Welcome to Julian Kuiters Sunday, May 27 2018 @ 01:38 PM AEST

Unsigned Integer Datatype in SQL Server 2005

  • Contributed by:
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
GO

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

/* 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'
GO

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.

or:

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