Unique if not null

From http://www.webservertalk.com/archive132-2004-3-145797.html :

If you are running SQL 2000, another technique is to create an indexed view
to enforce your unique-if-not-null constraint. For example:

CREATE TABLE MyTable (  MyPK INT NOT NULL,  ColumnX int NULL ) GO CREATE VIEW uv_MyTable WITH SCHEMABINDING AS  SELECT ColumnX  FROM dbo.MyTable  WHERE ColumnX IS NOT NULL GO CREATE UNIQUE CLUSTERED INDEX uv_MyTable_cdx ON uv_MyTable(ColumnX) GO INSERT INTO MyTable VALUES(1, NULL) INSERT INTO MyTable VALUES(2, NULL) INSERT INTO MyTable VALUES(3, 1) INSERT INTO MyTable VALUES(4, 1) --fails GO

Note that you’ll need to have SET ARITHABORT ON in order to maintain the
view index. This can be done from the client with an explicit SET
ARITHABORT ON, at the database level with ALTER DATABASE or at the server
level with sp_configure ‘user options’. See the Books Online for more
information.–
Hope this helps.Dan Guzman
SQL Server MVP


Error: [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE failed because the following SET options have incorrect settings: ‘ARITHABORT’.
Solution: http://www.thescripts.com/forum/thread83799.html

print cast(databasepropertyex('npr','IsArithmeticAbortEnabled' ) as int) -- 0 ALTER DATABASE npr SET ARITHABORT ON print cast(databasepropertyex('npr','IsArithmeticAbortEnabled' ) as int) -- 1

-- Two columns joined to be unique CREATE TABLE MyTable ( MyPK INT NOT NULL, ColumnX int NULL, ColumnY int NULL ) GO CREATE VIEW uv_MyTable WITH SCHEMABINDING AS SELECT ColumnX, ColumnY FROM dbo.MyTable WHERE (ColumnX IS NOT NULL) and (ColumnY IS NOT NULL) GO CREATE UNIQUE CLUSTERED INDEX uv_MyTable_cdx ON uv_MyTable(ColumnX, ColumnY) GO INSERT INTO MyTable VALUES(1, NULL, 1) INSERT INTO MyTable VALUES(2, NULL, 1) INSERT INTO MyTable VALUES(3, 1, 1) INSERT INTO MyTable VALUES(4, 1, 1) -- fails - another row exists INSERT INTO MyTable VALUES(5, 2, 1) INSERT INTO MyTable VALUES(6, 2, 2) INSERT INTO MyTable VALUES(7, 1, null) INSERT INTO MyTable VALUES(8, 1, null) update MyTable set ColumnX = 1 where MyPK = 1 -- fails - another row exists update MyTable set ColumnX = 3 where MyPK in (1,2) -- fails - two rows get same select * from MyTable GO drop view uv_MyTable drop TABLE MyTable GO

Look out for the index error: “It contains one or more disallowed constructs.”. It could be due to the view contains any of the following:- TOP clause
– text, ntext, image columns
– DISTINCT
– MIN, MAX, COUNT(*), COUNT(<expression>), STDEV, VARIANCE, AVG
– SUM on nullable expression
– derived table
– ROWSET function
– Another view
– UNION
– Subqueries, OUTER joins, self-joins
– CONTAINS or FREETEXT predicates
– COMPUTE, COMPUTE BY
– ORDER BY None of those are allowed in indexed views(according to “Inside SQL Server 2000”)Ref: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=8030

Another index error is “Cannot index the view XXXX. It references another view or function“.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: