SQL Server Performance

What is Included Column?

Discussion in 'SQL Server 2005 General Developer Questions' started by Sandy, Apr 5, 2009.

  1. Sandy New Member

    Hi All,
    I saw new concept of included column in sql 2005, but when I was trying to create index.
    I just wanted to know what is it and I have a small question for this also.
    I have a table which contains 20 columns. I need a unique index on 18 numbers of columns
    which throws me an error “maximum 16 columns can be possible for an index”.
    Then I tried with this included column concepts and its possible now...
    but when I am trying to insert data. Its not checking my uniqueness of data..
    Why So..??
    Thanks,
    Sandy.
  2. techbabu303 New Member

    Creating unique index irrespective of using INCLUDE COLUMNS it behaves in manner below
    BOOK_TABLE
    TITLE NAME PUBLISHER
    TT1 HELLO PUB1
    TT1 HELLO PUB2
    The second row is allowed since the it is unique as complete row, hope this makes things clearer for you.
    -Sat
  3. Adriaan New Member

    Included columns are not part of the index itself, so they do not contribute to the uniqueness.
    As far as understand it, you can INCLUDE columns with a key index in case you often filter/join on the combination of key and additional column.
    Wow - 16 columns to be unique. If you are sure you need that many, you could create an insert/update trigger to check for uniqueness. Easiest if you also have an identity column:
    CREATE TRIGGER trigCheckUnique_IU ON MyTable FOR INSERT, UPDATE
    AS
    IF EXISTS
    (SELECT i.key FROM inserted i
    WHERE EXISTS
    (SELECT t.key FROM MyTable t WHERE t.key <> i.key AND t.col1 = i.col1 AND .........etc))

    BEGIN

    END
  4. satya Moderator

Share This Page