August 25, 2011 at 10:54 am
Okay,
Practicing for my Database Developement test and I have come across this question.
Can you create a non-clustered index on an nvarchar(max) column.
According to the Microsoft exam prep kit , you can.
I thought there was a limitation on all indexes of 900 bytes and 16 columns.
I am vexed on this one. Because I would create non-clustered index on the id which is probably an int and then include the nvarchar(max).
This quote from the white pages does not explain it well enough.
"Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement. "
Am I crazy or am I just missing something?
Thank you :hehe:
August 25, 2011 at 11:14 am
You can CREATE it.
You'll get a warning which says that if you have more than 900 bytes in your data, the insert / update will fail.
Quite easy to test if you have 2 minutes...
August 25, 2011 at 11:28 am
Cool, I will do that.
Weird thing is the column name is lastName as an nvarchar(max).
So I could just assume the lastname will never be 2gb long, but it could possibly be over 900bytes long.
Thank you,
I am not crazy, just the people writing these exams!
August 25, 2011 at 11:35 am
jwbart06 (8/25/2011)
Cool, I will do that.Weird thing is the column name is lastName as an nvarchar(max).
So I could just assume the lastname will never be 2gb long, but it could possibly be over 900bytes long.
Thank you,
I am not crazy, just the people writing these exams!
Be careful, some of them are helpers on this forum!
August 25, 2011 at 11:39 am
You can't create a nonclustered index with a varchar(max) as the key column. You can put a varchar(max) as an include column, but if you try to create an index with a varchar(max) as a key column (and it's the key which is to 900 bytes), you'll get an error.
Easy to test.
CREATE TABLE SillyName (
FirstName VARCHAR(max),
LastName VARCHAR(MAX)
)
CREATE NONCLUSTERED INDEX idx_WontWork ON SillyName (LastName)
Msg 1919, Level 16, State 1, Line 6
Column 'LastName' in table 'SillyName' is of a type that is invalid for use as a key column in an index.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2011 at 11:47 am
I get the same message when I try to create my non-clustered huge index.
I don't know but I think the exam is wrong!
August 25, 2011 at 11:52 am
Not my intention.
Be careful, some of them are helpers on this forum!
Sorry, but the exams are frustrating, that is why they are exams. 😎
I am here to learn.
August 25, 2011 at 11:59 am
jwbart06 (8/25/2011)
Not my intention.Be careful, some of them are helpers on this forum!
Sorry, but the exams are frustrating, that is why they are exams. 😎
I am here to learn.
Hey I learned something here too.
Don't trust the MS papers. I had never tried it (makes no sense in a correct design) and I assumed MS was right :sick:
That's twice this month!!!
Good thing Gails knows everything-ish!
August 25, 2011 at 12:09 pm
jwbart06 (8/25/2011)
I get the same message when I try to create my non-clustered huge index.I don't know but I think the exam is wrong!
The exam likely isn't, but the exam prep kit is (and this isn't the only example)
The prep kit is not written by the same people that design the exams, and it likely doesn't get checked as carefully.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply