/*
 Julian Kuiters 2006
 http://www.julian-kuiters.id.au

 Sample code from article: "Unsigned Integer Datatype 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

-- Create table using the data type
CREATE TABLE dbo.test_data_type
(
	ID int NOT NULL,
	UTinyInt	dbo.unsignedTinyInt NULL,
	USmallInt	dbo.unsignedSmallInt NULL,
	UInt		dbo.unsignedInt NULL,
	UBigInt		dbo.unsignedBigInt NULL
)
GO

-- do some testing inserts, with valid and invalid values
INSERT INTO dbo.test_data_type (ID,UTinyInt) VALUES (1,0)
INSERT INTO dbo.test_data_type (ID,UTinyInt) VALUES (2,1)
INSERT INTO dbo.test_data_type (ID,UTinyInt) VALUES (3,200)
INSERT INTO dbo.test_data_type (ID,UTinyInt) VALUES (4,-1)

INSERT INTO dbo.test_data_type (ID,USmallInt) VALUES (1,0)
INSERT INTO dbo.test_data_type (ID,USmallInt) VALUES (2,1)
INSERT INTO dbo.test_data_type (ID,USmallInt) VALUES (3,200)
INSERT INTO dbo.test_data_type (ID,USmallInt) VALUES (4,-1)

INSERT INTO dbo.test_data_type (ID,UInt) VALUES (1,0)
INSERT INTO dbo.test_data_type (ID,UInt) VALUES (2,1)
INSERT INTO dbo.test_data_type (ID,UInt) VALUES (3,200)
INSERT INTO dbo.test_data_type (ID,UInt) VALUES (4,-1)

INSERT INTO dbo.test_data_type (ID,UBigInt) VALUES (1,0)
INSERT INTO dbo.test_data_type (ID,UBigInt) VALUES (2,1)
INSERT INTO dbo.test_data_type (ID,UBigInt) VALUES (3,200)
INSERT INTO dbo.test_data_type (ID,UBigInt) VALUES (4,-1)