CODEWITHSUNDEEP
sql-serverunique-constraint
Lee Morgan
Lee Morgan

Reputation: 112

SQL SERVER: Unique Constraint Explanation

Could someone simplify the explanation of adding a UNIQUE CONSTRAINT to a column please. When creating the key index does SQL SERVER copy ALL of the information in the row and add it to the index or just the data in the column with the applied UNIQUE CONSTRAINT?

I hope I explained that properly.
Any help will be greatly appreciated.
Lee.

EDIT**

Ok i think i get it?

CREATE TABLE dbo.test 
    (
    Id int NOT NULL, 
    Name char(10) NOT NULL UNIQUE
    );

INSERT INTO dbo.test (id, name) VALUES (1, 'Lee')
INSERT INTO dbo.test (id, name) VALUES (2, 'Paul')
INSERT INTO dbo.test (id, name) VALUES (3, 'Adam')
INSERT INTO dbo.test (id, name) VALUES (4, 'Henry')

In a clustered index the whole table would be sorted like

3, Adam
4, Henry
1, Lee
2, Paul

So with each additional INSERT the server would have to re-sort the entire table based on the name column?

In a nonclustered index there is another "table" that stores the sort?

Upvotes: 0

Views: 748

Answers (2)

Prabhat G
Prabhat G

Reputation: 3029

UNIQUE CONSTRAINT will work just as UNIQUE INDEX. There are 2 ways:

  • With a clustered index the rows are stored physically on the disk in the same order as the index. (hence, only one clustered index is possible)

  • With a non clustered index there is a second list that has pointers to the physical rows. You can have many non clustered indexes, although each new index will increase the time it takes to write new records.

  • If you have both clustered and non clustered index, then non clustered index will point to the clustered index column.

THIS 'SO' answer will help you understand it a bit clear.

By default the unique constraint and Unique index will create a non clustered index if you don't specify any different (and the PK will by default be created as CLUSTERED if no conflicting clustered index exists) but you can explicitly specify CLUSTERED/NONCLUSTERED for any of them.

Upvotes: 0

sepupic
sepupic

Reputation: 8687

When creating the key index does SQL SERVER copy ALL of the information in the row and add it to the index or just the data in the column with the applied UNIQUE CONSTRAINT?

There is no such a term as "key index".

Indexes can be clustered or non-clustered.

When you declare UNIQUE CONSTRAINT it's logical entity, but it's physically supported by unique index creation

When you create your unique constraint declaring it as clustered, clustered index will be created. If you don't mention clustered in your constraint definition or use explicite nonclustered, non-clustered index will be created.

Non-clustered index is a separate data structure where every row contains key columns.

On the other hand, clustered index (or better call it clustered table) is data itself + searching B-tree above it. In this case no separate structure is created, it's table itself that now is organized not as a heap but as ordered index.

Upvotes: 1

Related Questions