UNIQUE INDEX and INCLUDE

  • Basic situation: Poorly designed DB that I'm in the process of fixing. I have to take baby steps, though. Key is to do this with minimal impact.

    So, this particular issue is that I have about 5 unique indexes that exceed the storage limitations due to the usage of nvarchar columns. No big deal, I say, BOL says I can define these using INCLUDE to get past the storage limitations. Alas, tests show that while I can define them with INCLUDE columns, the uniqueness constraint does NOT check the included columns.

    My little test:

    CREATE TABLE UniqueTest (

    PROFILE_ID NUMERIC(19) NOT NULL,

    THING_NAME NVARCHAR(255) NOT NULL,

    ATTRIBUTE_NAME NVARCHAR(255) NOT NULL,

    VIEWABLE NCHAR(1) NOT NULL

    CONSTRAINT UniqueTest_CK1 CHECK (VIEWABLE IN ('Y', 'N')),

    );

    CREATE UNIQUE INDEX UniqueTest_UQ

    ON UniqueTest (PROFILE_ID)

    INCLUDE

    (THING_NAME,

    ATTRIBUTE_NAME) ;

    INSERT INTO UniqueTest (

    PROFILE_ID,

    THING_NAME ,

    ATTRIBUTE_NAME,

    VIEWABLE

    )

    VALUES

    (

    3,

    REPLICATE('FGHIJ', 20),

    REPLICATE('MNOPQ', 20),

    'Y')

    --OK, try again

    INSERT INTO UniqueTest (

    PROFILE_ID,

    THING_NAME ,

    ATTRIBUTE_NAME,

    VIEWABLE

    )

    VALUES

    (

    3,

    REPLICATE('FGHIJ', 20),

    REPLICATE('MNOPQ', 20),

    'Y')

    --Fails as expected

    INSERT INTO UniqueTest (

    PROFILE_ID,

    THING_NAME ,

    ATTRIBUTE_NAME,

    VIEWABLE

    )

    VALUES

    (

    4,

    REPLICATE('FGHIJ', 20),

    REPLICATE('MNOPQ', 20),

    'Y')

    --Does not fail - expected

    INSERT INTO UniqueTest (

    PROFILE_ID,

    THING_NAME ,

    ATTRIBUTE_NAME,

    VIEWABLE

    )

    VALUES

    (

    3,

    REPLICATE('ABDCE', 20),

    REPLICATE('12345', 20),

    'Y')

    --Fails, not good

    So other than browbeating the original developers, any (DB-based, non-trigger) options other than modifying the columns?

  • Not really. The reason indexes can't enforce (or even contain) too many bytes of data is structural, and critical to database performance.

    You can make either triggers (probably a bad idea) or the insert procs (better idea if possible) enforce it. That's going to be your best option, so far as I know.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Included fields are only a gain for data access. They will not be used for data correctness checs within UNIQUE INDEXES.

    I confirm GSquared to use a procedure which enforces the uniqueness of the data.

    Greets

    Flo

  • A side comment from what these fine gentlemen have said .. do you need NVARCHAR? I find some developers don't know the difference between VARCHAR and NVARCHAR and just choose the one at random. If there is no uni-code data in your field then I would look into changing that to varchar type and your are below the 900 byte limit.

    Thanks.

    Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • Yes, we need unicode. We have international/multilingual customers so it's a must.

  • aa nuts :P. Aaa well thought I should mention it, I have fixed lots of developers over the past year for this small mistake. :hehe:

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply