Introduction
Primary Key (PK) of a table object is containing CLUSTERED INDEX by default. So the confusion arises between PK – CLUSTERED INDEX and UNIQUE CLUATERED INDEX.
In this article we are trying to solve this confusion.
So what is That?
UNIQUE Constraint means it takes the Unique Records on the columns and with this It Supports one NULL value.
UNIQUE Constraint = Unique Records in Columns + One NULL Value
CLUSTERED means, How data the is Arranged in the Disk.
Where have INDEX in different type
1. UNIQUE CLUSTERED Index
2. Non UNIQUE Clustered Index
3. UNIQUE NON CLUSTERED Index
4. NON UNIQUE NON CLUSTERED Index
If we think about the PRIMARY KEY (PK) by default it takes CLUSTERED Index. So the Primary Key is UNIQUE and NOT Support any NULL VALUES.
Primary Key (PK) = UNIQUE + NOT NULL + CLUSTERED Index (By Default)
Here we must remember that the CLUSTERED Index is not the feature of Primary key. The Clustered Index is created automatically when we create the Primary Key. We can create Primary Key without any CLUSTERED Index also (Not a good Practice).
So a primary key support only UNIQUE value and NO NULL is allowed here.
For UNIQUE CLUSTERED or NON CLUSTERED Index One NULL value is allowed.
A table object cannot have more than one Primary Key but have multiple Unique Indexes.
Hope you like it.
Posted By: MR. JOYDEEP DAS
Please provide an example of Primary Key NON CLUSTERED Index.
ReplyDeleteHello @ Atanu,
DeleteThanks to provide your valuable time in my Blog Post.
Here I am tryting to clearyfy your Query with Example.
To do so u have to Create the CLAUSTERED Index on any columns that do not have any PK first and than care the PK on other columns.
Example:
IF OBJECT_ID(N'dbo.tbl_EMPLOYEE', N'U') IS NOT NULL
BEGIN
DROP TABLE dbo.tbl_EMPLOYEE;
END
CREATE TABLE dbo.tbl_EMPLOYEE
(EMPID INT NOT NULL IDENTITY,
EMPCODE INT NOT NULL,
EMPNAME VARCHAR(50) NOT NULL);
GO
CREATE CLUSTERED INDEX IX_EMPCODE_tbl_EMPLOYEE
ON dbo.tbl_EMPLOYEE (EMPCODE);
GO
ALTER TABLE dbo.tbl_EMPLOYEE
ADD CONSTRAINT PK_EMPID_tbl_EMPLOYEE PRIMARY KEY(EMPCODE);
GO
--Output
sp_helpindex tbl_EMPLOYEE
Is there any way to restore SQL Server 2008 database backup in SQL Server 2005 without Generate Scripts technique (Note- DB size is large). Need help urgent.
ReplyDeleteHello Shuvojit,
DeleteU must ask me this question at another post or u can mail me. This query is not linked with this post.
Anyway as u are one of my good friend, i am going to answer your query.
Directly by MS SQL Server point of view it is not possible. By using script it is so dangerous to make the correct Order..
It is most convenient to use third party tools that can read backup and generate scripts in the correct order. Using ApexSQL Diff and Data Diff from ApexSQL.
You can also try
http://www.sql-server-backup.de/en/
Thanks a lot buddy :)
ReplyDeleteYou are always Welcome
Delete