Use of Key Column

  • Comments posted to this topic are about the item Use of Key Column

  • 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 !!!

  • Good question Mahmood ๐Ÿ™‚

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Nice one.

    Neeraj Prasad Sharma
    Sql Server Tutorials

  • informative question ๐Ÿ™‚

  • Good question +1 thanks ๐Ÿ™‚

    MCTS | MCITP | Microsoft SQL Server 2008 Administration & Development
    MCSA | MCSE | Business Intelligence SQL Server 2012

  • This was removed by the editor as SPAM

  • 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.

  • 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..

  • Nice Question..

    Thanks..

  • Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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

  • 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Thanks for the clarification

    Dave Morris :alien:

    "Measure twice, saw once"

  • 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