May 27, 2013 at 10:53 pm
Comments posted to this topic are about the item Use of Key Column
May 27, 2013 at 11:10 pm
In this question, first unique constraint pull my attention, But suddenly i look at "max" keyword.
due to that i got it right.
Good question thanks.:-)
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
May 27, 2013 at 11:27 pm
Good question Mahmood 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 27, 2013 at 11:56 pm
Nice one.
Neeraj Prasad Sharma
Sql Server Tutorials
May 28, 2013 at 1:58 am
informative question 🙂
May 28, 2013 at 2:37 am
Good question +1 thanks 🙂
MCTS | MCITP | Microsoft SQL Server 2008 Administration & Development
MCSA | MCSE | Business Intelligence SQL Server 2012
May 28, 2013 at 3:52 am
This was removed by the editor as SPAM
May 28, 2013 at 6:17 am
CREATE TABLE #temp
(
ID int PRIMARY KEY,
Name varchar(900)
CONSTRAINT UN_Name UNIQUE(Name)
);
The #temp table created with 900 bytes, but
CREATE TABLE #temp
(
ID int PRIMARY KEY,
Name varchar(1000)
CONSTRAINT UN_Name UNIQUE(Name)
);
if i try to create table with 1000 bytes, it shows the below error... But the temp table created and 3 rows were inserted.... Then what is the maximum length can we use? Please any one give explanation for this....
Warning! The maximum key length is 900 bytes. The index 'UN_Name' has maximum length of 1000 bytes. For some combination of large values, the insert/update operation will fail.
nice question....
Manik
You cannot get to the top by sitting on your bottom.
May 28, 2013 at 6:49 am
Good question .. But explanation is slightly wrong.
Table is not created because varchar(MAX) is not allowed type for use as a key column in an index. This doesn't mean you cannot create index on column length is more than 900 bytes. You can create index on column even with length of varchar(8000). Just we will receive warning message.
Warning! The maximum key length is 900 bytes. The index 'UN_Name' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail.
IF you try to insert/update a value of more than 900 bytes then error will be raised and insert fails.
AS well as if you try to create a index on the column with CHAR(8000), this time you will recieve error on index creation itself because length of CHAR columns are fixed.
--
Dineshbabu
Desire to learn new things..
May 28, 2013 at 7:59 am
Nice Question..
Thanks..
May 28, 2013 at 9:34 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 28, 2013 at 12:49 pm
Definitely an interesting one.
The question is fine and the "correct" answer is correct, but the reference is not helpful because it explicitly states that an index where the maximum total key size including variable length columns that are part of the key can be created (but a warning message is generated too) as long as the minimum possible total key size including all those columns is not greater than 900, which certainly is the case here (the minimum size of a varchar(max) column is 0, which is certainly less than 900) provided there are no existing rows in the table where the key length exceeds the limit (which is also true here, as there are no rows in the table when the index is to be created). So that page is completely useless for this question.
There is indeed a rule that no column with type nvarchar(max), varchar(max) or varbinary(max) can be used in index keys. However, it isn't documented on that BOL page (or anywhere else that I can find, for that matter). Maybe the restriction was carrient forward from the text, ntext, and image types which were superseded by the three (max) types, and someone forgot to document it anywhere.
Tom
May 28, 2013 at 3:18 pm
L' Eomot Inversé (5/28/2013)
There is indeed a rule that no column with type nvarchar(max), varchar(max) or varbinary(max) can be used in index keys. However, it isn't documented on that BOL page (or anywhere else that I can find, for that matter).
This is documented in the page for CREATE INDEX: http://msdn.microsoft.com/en-us/library/ms188783.aspx:
"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".
The pages for CREATE TABLE and ALTER TABLE do specify that an index will be created for each UNIQUE or PRIMARY KEY constraint, but do not explicitly mention that the same limitations apply as for explicitly created indexes.
May 29, 2013 at 2:59 am
Thanks for the clarification
Dave Morris :alien:
"Measure twice, saw once"
May 29, 2013 at 3:01 am
Nice question thanks. The fact that type varchar(max) can not be used on indexed fields was new to me. A fact that this question has illuminated.
Dave Morris :alien:
"Measure twice, saw once"
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply