Wither ‘Go’
Wow, it’s been another time and a bit since my last post. Still here, still banging my head against the proverbial wall…
Came across this Oddness today.
I had the following script segment:
Select 1
From sys.tables
Where Object_Id = Object_Id('Security.Dictionary')
)
Drop Table [Security].Dictionary;
Create Table [Security].Dictionary
(
Id Int Identity(1, 1) Not Null
,HashValue Int Not Null
,ItemType TinyInt Null
,TextData NVarChar(MAX)
);
Alter Table [Security].Dictionary With NoCheck
Add Constraint PK_Dictionary Primary Key NonClustered
(
HashValue
)
With (
FillFactor = 50
,Online = On
,Pad_Index = On
,Allow_Row_Locks = On
,Allow_Page_Locks = Off
);
I needed to Add ItemType to the clustered key, so I dutifully made it a Not Null and tried again.
Select 1
From sys.tables
Where Object_Id = Object_Id('Security.Dictionary')
)
Drop Table [Security].Dictionary;
Create Table [Security].Dictionary
(
Id Int Identity(1, 1) Not Null
,HashValue Int Not Null
,ItemType TinyInt Not Null
,TextData NVarChar(MAX)
);
Alter Table [Security].Dictionary With NoCheck
Add Constraint PK_Dictionary Primary Key NonClustered
(
HashValue
,ItemType
)
With (
FillFactor = 50
,Online = On
,Pad_Index = On
,Allow_Row_Locks = On
,Allow_Page_Locks = Off
);
Imagine my puzzlement when I received the following error:
Msg 8111, Level 16, State 1, Line 12
Cannot define PRIMARY KEY constraint on nullable column in table ‘Dictionary’.
Msg 1750, Level 16, State 0, Line 12
Could not create constraint. See previous errors.
This threw me for a loop since ItemType was quite clearly Not Null (Line 9). So I ran each segment in turn using Select and F5 and was even more non-plussed when each segment ran fine. I then ran the script again and once more, it ran without failure. With my trusty imaginary ‘WTF?’ Red Fez hat firmly planted on my noggin, I attempted to recreate the situation and low and behold I found that adding a Go batch separator thus separating the Create Table And the Alter Table made all the difference for the first run through:
Select 1
From sys.tables
Where Object_Id = Object_Id('Security.Dictionary')
)
Drop Table [Security].Dictionary;
Create Table [Security].Dictionary
(
Id Int Identity(1, 1) Not Null
,HashValue Int Not Null
,ItemType TinyInt Not Null
,TextData NVarChar(MAX)
);
Go
Alter Table [Security].Dictionary With NoCheck
Add Constraint PK_Dictionary Primary Key NonClustered
(
HashValue
,ItemType
)
With (
FillFactor = 50
,Online = On
,Pad_Index = On
,Allow_Row_Locks = On
,Allow_Page_Locks = Off
);
If I were to guess what just happend here I’d say that I think the Alter Table sans the Go seperator was parsed based off the previous table specification, however embedding the Go seperator forced the parser to re-evaluate the new layout. Or something…
Sql Server 2008, SP1
