SQLTeam.com | Weblogs | Forums

Primary Key


#1

Does the sqlserver 2012 table allow to create the primary key for text datatype.


#2

What results did you get when you attempted to do this? Here are the results that I got:[code]create table dbo.Junk (
num int not null,
txt text not null,

constraint PK_Junk primary key (txt)
);[/code]Msg 1919, Level 16, State 1, Line 1
Column 'txt' in table 'Junk' is of a type that is invalid for use as a key column in an index.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.


#3

I hope not! :grinning:

Now, seriously:

You shouldn't be using "text" anymore anyway, but [n]varchar(max).

But, if you must use that, you will need to create a computed column and index on that, something like this:

create table dbo.Junk (
	num		int not null,
	txt		text not null,
	txt_pk as cast(left(cast(txt as varchar(max)), 900) as varchar(900)) persisted,
        constraint Junk__PK primary key (txt_pk)
);