Creating User-Defined Table Types in SQL Server

Here is a script that will create a user-defined table type in SQL Server. I was using 2008 R2.

In my type I am adding a primary key (Sku) column and a CHECK constraint on the Quantity column to state that the value must be greater than zero.

CREATE TYPE [dbo].[SkuListWithQuantity] AS TABLE(
[Sku] [CHAR](64) NOT NULL,
[Quantity] [INT] NOT NULL CHECK (Quantity > 0),
PRIMARY KEY CLUSTERED ([Sku] ASC)
);

You can test it works for valid values using the following SQL:

DECLARE @Skus SkuListWithQuantity
INSERT INTO @Skus (Sku, Quantity) VALUES ('FBIUN140095', 21), ('KLIJJ146314', 10)
SELECT * FROM @Skus


You can test it with an invalid Quantity value using the following SQL:

DECLARE @Skus SkuListWithQuantity
INSERT INTO @Skus (Sku, Quantity) VALUES ('FBIUN140095', 0), ('FBIUN140095', 10)
SELECT * FROM @Skus

You will receive an error like:

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint “CK__#5165187F__Quant__52593CB8”. The conflict occurred in database “tempdb”, table “@Skus”.
The statement has been terminated.

You can test it with a duplicate Primary Key value using the following SQL:

DECLARE @Skus SkuListWithQuantity
INSERT INTO @Skus (Sku, Quantity) VALUES ('FBIUN140095', 21), ('FBIUN140095', 10)
SELECT * FROM @Skus

You will receive an error like:

Msg 2627, Level 14, State 1, Line 4
Violation of PRIMARY KEY constraint ‘PK__#5CD6CB2__CA1FD3C45EBF139D’. Cannot insert duplicate key in object ‘dbo.@Skus’. The duplicate key value is (FBIUN140095 ).
The statement has been terminated.