Unsigned 32-bit integer datatype

Question

Unsigned 32-bit integer datatype

tomiz

SSCommitted

Points: 1842
More actions
March 4, 2003 at 5:12 pm

#81706

Hi Gurus,
In C# there's datatype called uint which can be 0 to 4,294,967,295 in value. What datatype should I use in SQL to represent uint?
Thanks
/Tomi

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 1

I think you need to use the BIGINT datatype. To enforce >0 and <4,294,967,295 rule you need to put a constraint on the column. Otherwise just let it accept any whole value. INT would not be enough for you and UNSIGNED INT does not exist in SQL Server.

Antares686 SSC Guru Points: 125444 More actions · Answer 2

BIGINT is only valid in SQL 2000, if using SQL 7 then your best bet is decimal or numeric with a precision of 10 and a scale of 0. Then you can use a CONSTRAINT to ensure all values between 0 and 4,294,967,295.

If you are building for an app you may be seling then I would go NUMERIC(10,0) for both SQL 7 and 2000 so you don't have to think anymore about it. It will only use 1 more byte per row.

tomiz SSCommitted Points: 1842 More actions · Answer 3

Thanks,

I'll settle for bigint. It's a shame that there aren't unsigned integer data types in sql. They would be really handy.

/Tomi

Fernan Lee SSC Rookie Points: 35 More actions · Answer 4

hi i would like to ask anybody else knows whats the equivalent or is there any unsigned integer data types in sql 2005?

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

Yes. TINYINT.

All datatypes available are found in Books Online.

N 56°04'39.16"
E 12°55'05.25"

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 6

Also, in SQL Server 2005, you can create your own datatype, a user-defined type:

CREATE TYPE dbo.uInt FROM bigint NULL
--OR CREATE TYPE dbo.uInt FROM decimal(10,0) NULL
GO
CREATE RULE dbo.uIntRule AS @value BETWEEN 0 AND 4294967295 ;
GO
EXEC sp_bindrule 'dbo.uIntRule', 'dbo.uInt'
GO

Mark

Wayne West SSC-Insane Points: 22586 More actions · Answer 7

And for us SQL Server 2000 folk:

create rule [uIntRule] as @value BETWEEN 0 AND 4294967295

GO

EXEC sp_addtype N'uInt', N'bigint', N'not null'

GO

EXEC sp_bindrule N'[dbo].[uIntRule]', N'[uInt]'

GO

I wanted to experiment, I've never created UDTs before. I really need to spend more time in 2K5 on my Mac (Parallels/XP Pro).

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 8

Way to go Wayne. I knew there was a way in SQL2k, but could not remember it. When I tried "Create Type" in SQL2k and it failed, I thought I must have must have imagined it and didn't press further. Thanks for the help.

Mark

Wayne West SSC-Insane Points: 22586 More actions · Answer 9

Yeah, I noticed the code was 2K5-only when I tried to run it in Query Analyzer and it didn't work. So I cheated. I created the UDT through Enterprise Manager, likewise for the rule, then bound the rule. Then I generated a script for the UDT and trimmed it slightly.

Having not worked with UDTs before, I was curious if the UDT was available in the pull-down list in Enterprise Manager if I created or modified a table, and it was.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

You can create UDT's in SQL Server 2000, sp_addtype in BOL for SQL 2000 shows how. Used this a few times at a previous employer.