SQL Server Performance

Warning while creating constraint!

Discussion in 'SQL Server 2005 General Developer Questions' started by DilliGrg, Mar 1, 2007.

  1. DilliGrg Member

    I was trying to create a unique constraint on these fields for this table, I am getting this warning. Will this give problems in the insert/update for this table? Also, I can't change the column name in the table. Any suggestions?



    ALTER TABLE wr_admin.wr_report
    ADD CONSTRAINT U_WR_REPORT UNIQUE NONCLUSTERED (wr_report_name, wr_report_location, wr_report_provider_id)


    Warning! The maximum key length is 900 bytes. The index 'U_WR_REPORT' has maximum length of 1054 bytes. For some combination of large values, the insert/update operation will fail.


    Thanks,

    Name
    ---------
    Dilli Grg

    (1 row(s) affected)

  2. DilliGrg Member

    Nover mind, I need to figure out the way to reduce one of the column's length on existing table.



    Thanks,

    Name
    ---------
    Dilli Grg

    (1 row(s) affected)

  3. FrankKalis Moderator

    Check this out: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/973b128d-5114-4d48-8eab-52497b47611e.htm

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  4. alzdba Member

    If you need the unique constraint, you're restricted to the 900 bytes keysize for an index ! So, reducing columnsizes is the way to go.

    With included columns, the aim is to reduce IO to index-only access.
    The included columns are not part of the key for the index.

  5. DilliGrg Member

    Thanks guys for your responses/link. Found the way to reduce coumn size at last.



    Thanks,

    Name
    ---------
    Dilli Grg

    (1 row(s) affected)

Share This Page