Post reply

unique index with multiple nulls

  • March 18, 2008 at 11:59 pm

    #204321

    hello everyone , i m using SQL Server 2005, here are my 2 tables:

    Table TVSRecords:

    TvsRecodID int (pk)

    TvsFormNo Int (unique index)

    CreateDate datetime

    FormImage image

    Comments varchar(100)

    AnotherTable is FormsQueue:

    FormQueueID int (pk)

    FormNo int

    FormImage image

    Now i moved the records from FormQueue table to TvsRecords, now the problem is while moving

    record from application, In 1st table TvsFormNo is unique and it allows only 1 null, so 1st

    user add forms in this table and its also check unique forms are inserted , but we have mutilple users

    used this table and add forms in TvsRecors (1st Table) so it gives error,

    Plz tell me how i implement unique index allow multiple nulls in TvsRecords table with Uniqueness

    in SQL SERVER 2005

    ? Plz tell me the solution.

    Thanx in Advance.

  • March 19, 2008 at 12:11 am

    #791290

    [font="Verdana"]A Unique index allows only one NULL value, so that values entered are unique (while indexing SQL Server compares NULL values as equal). It is by design.[/font]

  • March 19, 2008 at 12:20 am

    #791293

    i know this , by design it support 1 null only, but is there any other solution exists?

  • March 19, 2008 at 12:25 am

    #791294

    How about a trigger, to run on INSERT, UPDATE?

  • March 19, 2008 at 12:51 am

    #791300

    Well you cant have multiple columns with null values and a unique index on them. If this situation cant be changed you can take away the unique index and do you data validation ( for uniqueness for non-null values) in your stored procedure or your code.

    Or

    you can substitue your null values for some other values say negative values.

    "Keep Trying"

  • March 19, 2008 at 1:32 am

    #791307

    Try using an indexed view

    create view dbo.myview

    with schemabinding

    as

    select TvsFormNo

    from dbo.TvsRecords

    where TvsFormNo is not null

    go

    create unique clustered index uix_myview on dbo.myview (TvsFormNo)

    Also google for 'nullbuster' for another technique

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

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

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