Execute Dynamic SQL commands in SQL Server

In some applications having hard-coded SQL statements is not appealing, because of the dynamic nature of the queries being issued against the database server. Because of this sometimes there is a need to dynamically create a SQL statement on the fly and then run that command. This can be done quite simply from the application perspective where the statement is built on the fly whether you are using ASP.NET , ColdFusion or any other programming language. But how do you do this from within a SQL Server stored procedure? SQL Server offers a few ways of running a dynamically built SQL statement. These ways are: Writing a query with parameters Using EXEC Using sp_executesql Writing a query with parameters This first approach is pretty straightforward if you only need to pass parameters into the WHERE clause of your SQL statement. Let’s say we need to find all records from the Customers table where City = ‘London’. This can be done easily as the following example shows.

Multiple NULL values in a Unique index in SQL



this project needed to support having multiple NULL values in the column and
still have a UNIQUE constraint. That is allowed by Oracle but not in SQL Server
and DB2 LUW. There is a way to make this work in SQL Server and DB2 LUW also but
that requires a work-around. Consider this table:

CREATE TABLE TEST_UQ (COL1 INT IDENTITY(1,1) PRIMARY KEY, COL2 NVARCHAR(10) NULL)
GO

In this table, COL1 has been declared as the primary key but we want a UNIQUE
constraint to be put on COL2 as well. Please note that COL2 is a nullable column
and that SQL Server does not allow multiple NULL values in a UNIQUE index and treats
them the same way. We can test it out prior to proceeding with the work-around:

Let tus create a unique index first:

CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL2)
GO

Now, let us try to insert these values:

insert into test_uq (col2) values (’abc’);
insert into test_uq (col2) values (’xyz’);
insert into test_uq (col2) values (Null);

All three will go in. After that, try to insert the NULL value again:

insert into test_uq (col2) values (Null);

and you will get the error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index
‘TEST_UQ_IND_1′.
The statement has been terminated.

The work-around is to have a computed column and define the unique constraint on it.
Here is how you can do that:

1) First, let’s drop the existing unique index:

drop index test_uq.TEST_UQ_IND_1

2) Next, let’s add the computed column:

ALTER TABLE TEST_UQ ADD COL3 AS (CASE WHEN COL2 IS NULL THEN CAST(COL1 AS NVARCHAR(10)) ELSE COL2 END);

In this command, we are stating that whenever the value for COL2 is null,
replace it with the primary key after casting it to the same data-type as that of COL2. By doing so, we will mae sure that COL3
is always NOT NULL and always have unique values. This approach will work well in this case as there should never be a clash of
the values between COL1 and COL2. For example, what-if you needed to do this on a column that was also an interger data-type column?
In
that case, chances of clashes of the data can arise. If you suspect a clash, you can have additional logic like:
(CASE WHEN COL2 IS NULL then -1 * COL1 ELSE COL2 END). That way, you can still maintain the logic and the uniqueness.

3) Now, create the unique index on this column:

CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL3)
GO

4) Next, let’s try to insert the NULL value again:

insert into test_uq (col2) values (Null);

This time it will go through. If we examine the contents of the table:

COL1 COL2 COL3
----------- ---------- ----------
1 abc abc
2 xyz xyz
3 NULL 3
5 NULL 5

As you can see, we have allowed multiple NULL values now for
COL2 and still maintained the uniqueness. We can next try to insert the value
“abc” again and see if that preserves our uniqueness criteria:

insert into test_uq (col2) values (’abc’);

This time, we will get an error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index ‘TEST_UQ_IND_1′.
The statement has been terminated.

So, using this work-around, one can preserve the same behavior as Oracle.
This might be useful to you as well in case you are working on a project
that requires conversion from Oracle to SQL Server or Oracle to DB2 LUW.

Comments

Unknown said…
It's cool i really need that. Thank's a lot
Source?

http://decipherinfosys.wordpress.com/2007/11/30/multiple-null-values-in-a-unique-index-in-sql-serverdb2-luw/
Unknown said…
This comment has been removed by the author.
Unknown said…
This comment has been removed by the author.
Unknown said…
One of The Simplest way to achieve this using SQL 2008 is:

CREATE UNIQUE NONCLUSTERED INDEX IndexName ON dbo.TableName(ColumnName)
WHERE ColumnName IS NOT NULL;
GO
Kevin said…
I would not use this workaround - An insert will fail if you try to insert a value for Col2 that equals the value in Col1 for another record where that record's value for Col2 is null.

For instance, given the example in the article, after you have run all the insert statements, then try this:

insert into test_uq (col2) values (’3’);

It will fail, even though there are no records where col2 ='3'.
enl8enmentnow said…
Nice solution to a common problem.

To deal with the concern that the other commenter had, use this as the computed column rather then what's in the article:

case when col is null then identity_col else -1 end

and have the unique index on

computed_col, original_col.

- enl8enmentnow
Unknown said…
ALTER TABLE TEST_UQ ADD COL3 AS (isnull([COL2], -[COL1]))
Jocie Pup said…
Thank-you, nice solution!!

Just in case it helps anyone else, I was getting an error about the type of my column being non-indexable, but it turned out that actually the index was over 900 bytes as I was using nvarchar(max)
Luveshen Pather said…
There is an easier way...

CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME]
ON [SCHEMA].[TABLE_NAME]([COLUMN_NAME] ASC) WHERE ([COLUMN_NAME] IS NOT NULL) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
ON [PRIMARY];
Luveshen Pather said…
There is an easier way...

CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME]
ON [SCHEMA].[TABLE_NAME]([COLUMN_NAME] ASC) WHERE ([COLUMN_NAME] IS NOT NULL) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
ON [PRIMARY];
Luveshen Pather said…
There is an easier way...

CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME]
ON [SCHEMA].[TABLE_NAME]([COLUMN_NAME] ASC) WHERE ([COLUMN_NAME] IS NOT NULL) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
ON [PRIMARY];
Luveshen Pather said…
There is an easier way...

CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME]
ON [SCHEMA].[TABLE_NAME]([COLUMN_NAME] ASC) WHERE ([COLUMN_NAME] IS NOT NULL) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, MAXDOP = 0)
ON [PRIMARY];
Great tip, I like it.

Alternatively, you could also use a GUID for uniqueness:
convert(varchar(36),newid())

Also, to avoid the 2 column approach, you could use a DEFAULT constraint:

ALTER TABLE TEST_UQ ADD CONSTRAINT DF_TEST_UQ_COL2 DEFAULT convert(varchar(36),newid()) FOR col2
GO

For those readers that aren't aware, a default is only applied when you omit the column from the insert statement. For example, col2 will get a GUID if col2 is not present in the insert columns, as in:

INSERT INTO TEST_UQ(another_column) VALUES('a value')

Unknown said…
Im new on programming but this saved my life thanks to all supporting the noobs good luck.
Unknown said…
Thanks for the help! This was perfect for what I needed.
jignesh kadvani said…
interviewer asked me whether we can insert null values multiple times? and i said no. but now i can say confidently that yes.

Popular posts from this blog

Check If Temporary Table Exists

Row To Column