SQLTeam.com | Weblogs | Forums

Primary Key

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

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.

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)
);
1 Like