Unsigned Integer Datatype in SQL Server 2005

SQL Server 2005

There's been some interesting questions by users on the SQL Server Engine Tips blog. While almost all of them seem to be off topic, there are some interesting ideas. One of the fun ones was from Dave asking "Can we have some unsigned int data types?".

As a basic background for those of us who spend most of their time in T-SQL; an unsigned integer does not hold negative values. The value range is from 0 to the maximum of the datatype.

So assuming Dave is wanting unsigned integers for this reason, here is how you would create unsigned integer user datatypes in SQL Server 2005:

/* Create the unsigned integer data types */
CREATE TYPE dbo.unsignedTinyint FROM tinyint NULL
CREATE TYPE dbo.unsignedSmallint FROM smallint NULL
CREATE TYPE dbo.unsignedInt FROM int NULL
CREATE TYPE dbo.unsignedBigint FROM bigint NULL

/* unsigned integer (positive values only) rule */
CREATE RULE dbo.unsignedSmallIntRule AS @value >= 0;
CREATE RULE dbo.unsignedIntRule AS @value >= 0;
CREATE RULE dbo.unsignedBigintRule AS @value >= 0;

/* Bind the unsigned integer rules to their datatypes */
EXEC sp_bindrule 'dbo.unsignedSmallIntRule', 'dbo.unsignedSmallint'
EXEC sp_bindrule 'dbo.unsignedIntRule', 'dbo.unsignedInt'
EXEC sp_bindrule 'dbo.unsignedBigintRule', 'dbo.unsignedBigint'

You can download the sample script here which includes an example table with the new unsigned integer data types.

If you attempt to insert an negative value into an unsigned column, you will receive an error message like:

Msg 612, Level 16, State 0, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated.


Msg 220, Level 16, State 2, Line 1
Aritmetic overflow error for data type tinyint, value =-1.

  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz

Unsigned Integer Datatype in SQL Server 2005 | 1 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.
Unsigned Integer Datatype in SQL Server 2005
Authored by: Anonymous on Saturday, June 16 2007 @ 12:24 AM AEST

Your solution does not appear to emulate an true unsigned 32-bit int. Rather, it merely restricts the range of allowed values of the signed data type to exclude negative of numbers. An actual unsigned 32-bit int would have a range of 0 to 4294967295, not 0 to 2147483647.

Of course, using unsigned types only double the allowed number of positive values, so switching to bigint is probably a better long term solution if you've already hit the limit of int.

[ # ]

Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 Copyright © 2018 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.