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.
Tags: ,