Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Make sure a varchar column allows no duplicates in SQL 2005

Posted on 2012-08-23
8
Medium Priority
?
459 Views
Last Modified: 2012-08-30
Goood Morning Experts,

Is there a way that I can set a Varchar column so that it will allow Nulls, but not duplicate values that are not null?
0
Comment
Question by:ISBTECH
  • 3
  • 3
  • 2
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38325495
if you have sql 2008+, you can create a filtered (unique) index excluding null values.

otherwise, you need to create a normal index + a trigger that checks and eventually rejects if the insert/update would result in duplicate values
0
 

Author Comment

by:ISBTECH
ID: 38325740
I can't make it an index though because that would not allow nulls right?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38325943
a normal index does not prohibit nulls.
you cannot do a UNIQUE index without the filtering technique.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38327072
>> you cannot do a UNIQUE index without the filtering technique <<

Hmm, that's not really true.  I can think of easy "tricks" that would do what the requestor wants.
0
 

Author Comment

by:ISBTECH
ID: 38345307
I don't have SQL 2008, just 2005 for now, what tricks are you suggesting?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38345863
Sorry, was really pressed for time when I originally answered and then forgot about this q.

I'll assume you have an IDENTITY column (if not, it's easy enough to add an IDENTITY column; or just use any other unique column(s), as any column(s) that are unique will do).

Add a persisted computed column, matching the data type of the unique column -- an integer in this case, to match the identity data type -- to the end of the varchar index.  The computed column is set to a unique value if the varchar column is NULL, and 0 if it is not NULL.

That makes every row automatically unique for all NULL values, but won't allow the same non-NULL value more than once.

To demonstrate:


IF OBJECT_ID('tempdb.dbo.#test_unique_index_but_multiple_nulls') IS NOT NULL
    DROP TABLE #test_unique_index_but_multiple_nulls
GO
CREATE TABLE #test_unique_index_but_multiple_nulls (
    ident int IDENTITY(1, 1) NOT NULL,
    varchar_col varchar(100),
    -- CAST is just to be absolutely sure we get an int data type
    varchar_col_index_trick AS CAST(CASE WHEN varchar_col IS NULL THEN ident ELSE 0 END AS int) PERSISTED
    )
   
CREATE UNIQUE NONCLUSTERED INDEX varchar_col_index ON #test_unique_index_but_multiple_nulls ( varchar_col, varchar_col_index_trick )

GO

INSERT INTO #test_unique_index_but_multiple_nulls VALUES(NULL)
INSERT INTO #test_unique_index_but_multiple_nulls VALUES('a')
INSERT INTO #test_unique_index_but_multiple_nulls VALUES(NULL) --should work!
INSERT INTO #test_unique_index_but_multiple_nulls VALUES(NULL) --should work!
INSERT INTO #test_unique_index_but_multiple_nulls VALUES('b')
INSERT INTO #test_unique_index_but_multiple_nulls VALUES('c')
INSERT INTO #test_unique_index_but_multiple_nulls VALUES('a') --should fail!

GO

SELECT * FROM #test_unique_index_but_multiple_nulls ORDER BY ident
--verify both one more time
INSERT INTO #test_unique_index_but_multiple_nulls VALUES(NULL) --should work!
INSERT INTO #test_unique_index_but_multiple_nulls VALUES('b') --should fail!
GO
SELECT * FROM #test_unique_index_but_multiple_nulls ORDER BY ident
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38350426
well, it's really a trick to create/use another column and put the index on that one ...
:)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38350613
What else to call it?  Calling it a "technique" seems a bit overblown :-)


>> to create/use another use another column and put the index on that one <<

Not true.
The LEADING column in an index is THE key column, so the index is still primarily on the original column, not the new column.  The new column just aids in making the index unique, to satisfy the functional requirements.


Obviously you didn't think of anything like this, since you stated CATEGORICALLY, in TWO different ways:

"otherwise, you need to create a normal index + a trigger that checks and eventually rejects if the insert/update would result in duplicate values"

"you cannot do a UNIQUE index without the filtering technique"


YIKES -- there's HUGE overhead in a trigger as compared to the "trick" above.  

And presumably the requestor didn't want to give up on their business requirement.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question