0

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?

Lee Morgan
  • 112
  • 9
  • Unique index and unique constraint are moreover same with basic difference that index can be disabled, unique constraint cannot. I am not quite clear what you're trying to ask. – Prabhat G Nov 02 '17 at 10:37
  • @prabhat. Apologies I will try and explain a little better. Say I create a table with 5 columns col1, col2, col3, col4, col5 and add unique to col5, does SQL Server copy the data for each column into the index or just from col5? – Lee Morgan Nov 02 '17 at 10:45
  • you should read this as well : http://www.programmerinterview.com/index.php/database-sql/clustered-vs-non-clustered-index/ – Prabhat G Nov 02 '17 at 13:47

2 Answers2

1

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.

sepupic
  • 8,409
  • 1
  • 9
  • 20
0

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.

Prabhat G
  • 2,974
  • 1
  • 22
  • 31