The fact #1: Table Variables can have constraints.
The fact #2: You can't name Table Variables' Constraints.
That leads to additional couple of differences between Table Variables and Temp Tables:
1. You can name constraints in Temporary tables, but can't do it in Table Variables.
2. You can create indexes in Temporary tables, but for Table Variables you can create ONLY Primary Key Constraint (clustered if necessary) and Unique Constraint.
Here is a demo:
1. Create a sample Table Variable:
DECLARE @Demo TABLE
(
Demo_ID INT IDENTITY(1,1) NOT NULL,
Demo_Number INT NOT NULL,
Demo_Type CHAR(1) NOT NULL,
Demo_Text VARCHAR(100) NOT NULL
)
GO
2. Add Unique Constraint:
DECLARE @Demo TABLE
(
Demo_ID INT IDENTITY(1,1) NOT NULL,
Demo_Number INT NOT NULL,
Demo_Type CHAR(1) NOT NULL,
Demo_Text VARCHAR(100) NOT NULL,
UNIQUE (Demo_Number)
)
INSERT INTO @Demo(Demo_Number, Demo_Type,
Demo_Text)
VALUES(1,'A','Test 1'),(1,'B','Test 2')
GO
Test it:
Msg 2627, Level 14, State 1, Line 9
Violation of UNIQUE KEY constraint 'UQ__#A9777AE__F66FA017F6632BD9'. Cannot insert duplicate key in object 'dbo.@Demo'. The duplicate key value is (1).
The statement has been terminated.
Here you also can see that Table Variable has been created in dbo schema. That means Table Variable is a TempDB object. (see step 7).
3. Modify Unique Constraint to be really Unique and add Primary key:
DECLARE @Demo TABLE
(
Demo_ID INT IDENTITY(1,1) NOT NULL,
Demo_Number INT NOT NULL,
Demo_Type CHAR(1) NOT NULL,
Demo_Text VARCHAR(100) NOT NULL,
UNIQUE (Demo_ID, Demo_Number),
PRIMARY KEY (Demo_Number)
)
INSERT INTO @Demo(Demo_Number, Demo_Type,
Demo_Text)
VALUES(1,'A','Test 1'),(1,'B','Test 2')
GO
Test it:
Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'PK__#AC8BACF__F66FA016FD40C90A'. Cannot insert duplicate key in object 'dbo.@Demo'. The duplicate key value is (1).
The statement has been terminated.
We have 2 indexes on Table Variable so far.
4. Lets make Primary key to be Clustered:
DECLARE @Demo TABLE
(
Demo_ID INT IDENTITY(1,1) NOT NULL,
Demo_Number INT NOT NULL,
Demo_Type CHAR(1) NOT NULL,
Demo_Text VARCHAR(100) NOT NULL,
UNIQUE (Demo_ID, Demo_Number),
PRIMARY KEY CLUSTERED
(Demo_ID, Demo_Number)
)
INSERT INTO @Demo(Demo_Number, Demo_Type,
Demo_Text)
VALUES(1,'A','Test 1'),(1,'B','Test 2')
GO
5. Add Check Constraint:
DECLARE @Demo TABLE
(
Demo_ID INT IDENTITY(1,1) NOT NULL,
Demo_Number INT NOT NULL,
Demo_Type CHAR(1) NOT NULL,
Demo_Text VARCHAR(100) NOT NULL,
UNIQUE (Demo_ID, Demo_Number),
PRIMARY KEY CLUSTERED
(Demo_ID, Demo_Number),
CHECK (Demo_Type IN ('A','B','C'))
)
INSERT INTO @Demo(Demo_Number, Demo_Type,
Demo_Text)
VALUES(1,'A','Test 1'),(1,'D','Test 2')
GO
Test it:
Msg 547, Level 16, State 0, Line 11
The INSERT statement conflicted with the CHECK constraint "CK__#BDB638FE__Demo___A092A5A9". The conflict occurred in database "tempdb", table "@Demo".
The statement has been terminated.
6. As a last step we add Default Constraint:
DECLARE @Demo TABLE
(
Demo_ID INT IDENTITY(1,1) NOT NULL,
Demo_Number INT NOT NULL,
Demo_Type CHAR(1) NOT NULL,
Demo_Text VARCHAR(100) NOT NULL DEFAULT
'N/A',
UNIQUE (Demo_ID, Demo_Number),
PRIMARY KEY CLUSTERED
(Demo_ID, Demo_Number),
CHECK (Demo_Type IN ('A','B','C'))
)
GO
7. Now take a look at all newly created objects:
DECLARE @Current_Dt datetime2 = GETDATE();
DECLARE @Demo TABLE
(
Demo_ID INT IDENTITY(1,1) NOT NULL,
Demo_Number INT NOT NULL,
Demo_Type CHAR(1) NOT NULL,
Demo_Text VARCHAR(100) NOT NULL DEFAULT
'N/A',
UNIQUE (Demo_ID, Demo_Number),
PRIMARY KEY CLUSTERED
(Demo_ID, Demo_Number),
CHECK (Demo_Type IN ('A','B','C'))
)
SELECT Name, Type, type_desc, Text
FROM tempdb.sys.objects as o
LEFT JOIN tempdb.sys.syscomments as c
ON o.object_id = c.id
WHERE Type not in ('S','IT','SQ')
and Create_Date >= DATEADD(MILLISECOND,-10, @Current_Dt)
GO
Test it:
As you can see, all new objects are created in TempDB with system weird names.
No comments:
Post a Comment