Post reply

Unsigned 32-bit integer datatype

  • 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

  • March 4, 2003 at 6:14 pm

    #450286

    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.

  • March 5, 2003 at 4:47 am

    #450301

    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.

  • March 6, 2003 at 7:15 am

    #450398

    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

  • July 18, 2007 at 11:05 pm

    #720609

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

  • July 19, 2007 at 3:59 am

    #720664

    Yes. TINYINT.

    All datatypes available are found in Books Online.

     

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

  • July 19, 2007 at 7:20 am

    #720744

    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

  • July 20, 2007 at 11:06 am

    #721222

    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]

  • July 20, 2007 at 11:31 am

    #721231

    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

  • July 20, 2007 at 12:00 pm

    #721236

    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]

  • July 20, 2007 at 12:21 pm

    #721245

    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.

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

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