Category: DDL

UNIQUE constraints in SQL Server

UNIQUE constraints are enforcing no duplicates can exists for a column or combination of columns for a table. We can define constraints in 3 ways:

  1. while creating a table
  2. using ALTER TABLE statement
  3. using SQL Server Management Studio

Let us quickly see with a simple example as below.

Drop table if exists TestTable
Create Table TestTable ( ID int UNIQUE, FirstName varchar(50) )

sp_help 'TestTable'

sp_help system procedure can be used to understand the table properties. If we observe the result of sp_help, we can see two important information.

  1. constraint name – unique constraint created.
  2. index name – though we have not created any index separately, there is one created as the same name of constraint.

So, when we create a UNIQUE constraint, SQL Server automatically creates a nonclustered index on the table. And this index is responsible for enforcing the uniqueness on the column(s).

Can we drop the index created?

No, we will never be able to drop those indexes until we drop the constraints.

Can we insert NULL values to a UNIQUE column?

Yes, we can have ONLY one NULL value to a UNIQUE column. If we try to insert again, it will fail for the same reason of duplicate violation.

Can we define UNIQUE for more than one column?

Yes, we can define on combination of columns. Interestingly, by defining on composite, we can have NULL values for each individual columns once and one for combination as well.

Drop table if exists TestTable
Create Table TestTable ( ID int , FirstName varchar(50) , UNIQUE(ID,FirstName))

--Data inserts
Print 'First insert'
Insert into TestTable Values(1,NULL)
Print 'Second insert'
Insert into TestTable Values(NULL,NULL)
Print 'Third insert'
Insert into TestTable Values(NULL,'SQL')
Print 'Fourth insert'
Insert into TestTable Values(100,'some name')

If you enjoyed this blog post, feel free to share it with your friends!

NOT NULL Constraint in SQL Server

NOT NULL constraints are important constraints in SQL Server to ensure the column defined on never accepts NULL values. By default, column accepts NULL value in SQL Server. If we need to ensure the column should not accept NULL values, then we can define a NOT NULL constraints on the column. We can create NOT NULL constraint in 3 ways:

  • while creating a table
  • using alter table statement
  • using SQL Server management Studio

Let us see an example:

Create Table TestTable ( ID int NOT NULL , FirstName varchar(50) )

Insert into TestTable Values(1,'SQL'),(NULL, 'Zealot')

Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column ‘ID’, table ‘SQLZealot.dbo.TestTable’; column does not allow nulls. INSERT fails.

The statement has been terminated.

Now, let us insert some data with NULL values for FirstName column and define a NOT NULL constraint on First Name column as below.

Insert into TestTable Values(2,NULL)

ALTER TABLE TestTable ALTER COLUMN FirstName VARCHAR(50) NOT NULL

Msg 515, Level 16, State 2, Line 5
Cannot insert the value NULL into column ‘FirstName’, table ‘SQLZealot.dbo.TestTable’; column does not allow nulls. UPDATE fails.

The statement has been terminated.

This error has raised because we have already inserted a record that has NULL value for First Name column in the table. That means, when we create a constraint, it checks the existing data before it creates the constraints.

If you enjoyed this blog post, feel free to share it with your friends!

How to create a lock on a table that prevents other requests in SQL Server

Problem Statement:

Today, I got a very peculiar request from one of my colleague that he wants to put a lock on a table so that no other requestscan be served. The request was looking weird to me initially as most of us want to avoid locking/blocking scenarios in a system, but the request was looking for creating one. But Why? After asking many questions, I got his intention that he is looking for replicating a locking scenario in his local environment that ends up with a Timeout.

Solution:

The solution provided is simple but put a Schema modification lock(Sch-M) on his table. Schema modification lock will be acquired when a DDL statement is executed on the table. As Schema modification is not compatible with any other locks, it will prevent access to the locked object data. An example of the Sch-M lock is lock during index rebuild (Please note when used with ONLINE option, index rebuild will acquire Sch-M lock shortly at the end of the process).


Session 1:
--Create Table
create Table TestLock(Col1 int)
Insert into TestLock values ('100')

--Raise a Schema modification lock
begin Tran T1
ALTER TABLE TESTLOCK ALTER COLUMN COL1 VARCHAR(5000)

-----------------------------------------------------------------------
Session 2:
Select * From TestLOCK(NOLOCK)--NOLOCK has no impact on SCH-M locked object.

Thoughts:

Finally, I understood he is trying to put some code to handle the locking situation, but not the solution for the timeout. As he mentioned the issue happens ONLY when maintenance window is running, I believe this is something related to any maintenance operations like (rebuild index,update stats etc.). He can , later, better of find the actual issue and fix it like move the functionality out of the maintenance window!!!