As database systems and their interactions and interfaces with systems become more complex, a large amount of complex data is exchanged through the system boundaries. Table Valued parameters are frequently used to exchange data. If the structure of the data being exchanged is known, often systems (which work with SQL 2008 and above) will use User defined table types (UDTTs).
Depending upon the domain, data being exchanged across system boundaries may need to be constrained to a number of constraints, especially check and unique constraints. My encounter with UDTTs was informal – I learnt about them along the way and never explored them fully. Recently, I was wondering if I could use constraints with UDTTs.
And so, I decided to conduct a small experiment. I created the following UDTT. As can be seen from the definition, I have created the UDTT with the following constraints.
- Primary Key constraint
- Unique constraint
- Default constraint
- Check constraint
USE tempdb;
GO
CREATE TYPE dbo.UDTTDDLTest AS TABLE
( RecordId INT NOT NULL,
RecordValue VARCHAR(50) NOT NULL UNIQUE,
RecordStatus TINYINT NOT NULL DEFAULT(2)
PRIMARY KEY CLUSTERED (RecordId),
CHECK (RecordStatus>= 1 AND RecordStatus <= 3)
);
GO
SQL Server allows creation of the constraint without any issues. I then tried to run the following code that attempts to insert various combinations of data into the user defined table type.
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @myVariable AS dbo.UDTTDDLTest;
--Normal insertion
INSERT INTO @myVariable (RecordId, RecordValue, RecordStatus)
VALUES (1,
'SQL Twins',
1
);
SELECT * FROM @myVariable;
--Checking effect of default constraints
INSERT INTO @myVariable (RecordId, RecordValue)
VALUES (2,
'SQL Twins with Default'
);
SELECT * FROM @myVariable;
--Checking effect of primary key constraint
INSERT INTO @myVariable (RecordId, RecordValue, RecordStatus)
VALUES (2,
'Oops...duplicate key',
1
);
SELECT * FROM @myVariable;
--Checking effect of check constraint
INSERT INTO @myVariable (RecordId, RecordValue, RecordStatus)
VALUES (3,
'Bad Status!',
4
);
SELECT * FROM @myVariable;
--Checking effect of unique constraint. The value "SQL Twins" is already in the UDTT,
--so if the constraint is in effect, we will get an error
INSERT INTO @myVariable (RecordId, RecordValue, RecordStatus)
VALUES (4,
'SQL Twins',
1
);
SELECT * FROM @myVariable;
GO
As can be seen from the output below, SQL Server successfully prevented me from entering data that violated any one of the constraints.
Msg 2627, Level 14, State 1, Line 38
Violation of PRIMARY KEY constraint ‘PK__#B40743E__FBDF78E9F9E4365B’. Cannot insert duplicate key in object ‘dbo.@myVariable’. The duplicate key value is (2).
The statement has been terminated.
Msg 547, Level 16, State 0, Line 47
The INSERT statement conflicted with the CHECK constraint “CK__#B40743ED__Recor__B5EF8C5F”. The conflict occurred in database “tempdb”, table “@myVariable”.
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 57
Violation of UNIQUE KEY constraint ‘UQ__#B40743E__4D9E41B64A9AE451’. Cannot insert duplicate key in object ‘dbo.@myVariable’. The duplicate key value is (SQL Twins).
The statement has been terminated.
Summary
Constraints (Primary Key, Default, Unique and Check constraints) are supported by User Defined Table Types (UDTTs) in Microsoft SQL Server and can be used to ensure the quality of data being exchanged via UDTTs.
Further Reading
- Passing Table valued parameters to a table valued function – Underappreciated features of Microsoft SQL Server [Link]
- SQL Server 2000-Alternate to Table Valued Parameters-Share Temporary Tables with a stored procedure [Link]
- User Defined Table Types [Link]
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 