PRIMARY KEY VS UNIQUE KEY

What are the differences between PRIMARY KEY and UNIQUE KEY [PRIMARY KEY VS UNIQUE KEY]?

	PRIMARY KEY	UNIQUE KEY
Use	PRIMARY KEY is used to identify a unique row (record) in a table.	UNIQUE KEY is used to prevent duplicate values in a column.
INDEX	By default, SQL-Engine creates CLUSTERED INDEX on PRIMARY KEY column if not exists.	By default, SQL-Engine creates NON-CLUSTERED INDEX on UNIQUE KEY column.
NULL?	PRIMARY KEY does not allow or include NULL value.	UNIQUE KEY allows NULL value at one time just like any other value.
Existence	A table can have at most one PRIMARY KEY.	A table can have multiple UNIQUE KEYs.
Reference	PRIMARY KEY can be made a FOREIGN KEY into another table.	UNIQUE KEY can be made FOREIGN KEY into another table.
Delete	Table or Table row can’t be deleted if PRIMARY KEY made a FOREIGN KEY into another table and if primary key column value is associated between tables.	Table or Table row can be deleted if UNIQUE KEY made a FOREIGN KEY into another table if unique key column value is associated between tables.
Examples	CREATE TABLE [dbo].[Employee_Unique](        [EmployeeID] [int] IDENTITY(1,1) NOT NULL,        [DepartmentID] [int] NOT NULL,        [EmployeeName] [varchar](50) NOT NULL,        [Gender] [char](1) NOT NULL,        [BirthDate] [date] NOT NULL,        [Salary] [decimal](10, 2) NULL,        [Detail] [varchar](max) NULL,        [IsActive] [bit] NOT NULL,        CONSTRAINT UC_Employee UNIQUE (EmployeeID)  ) CREATE TABLE [dbo].[DegreeEmployee_Unique](        [DegreeID] [int] NOT NULL,        [EmployeeID] [int] NOT NULL,        [Sequence] [tinyint] NOT NULL,  CONSTRAINT [PK_DegreeEmployee_DegreeID_EmployeeID] PRIMARY KEY CLUSTERED (        [DegreeID] ASC,        [EmployeeID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[DegreeEmployee_Unique]  WITH CHECK ADD  CONSTRAINT [FK_DegreeEmployee_Unique_EmployeeID] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].[Employee] ([EmployeeID]) GO

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF

UNION VS UNION ALL

What are the differences between UNION and UNION ALL [UNION VS UNION ALL]?

	UNION	UNION ALL
Use	UNION is used to combine multiple result sets into one result set and will remove any duplicates rows that exist after combining the result sets.	UNION ALL is used to combine multiple result sets into one result set, but it does not remove any duplicate rows.
Performance	UNION operation is slower than UNION ALL operation due to following course of outputs. UNION is performing a DISTINCT operation across all columns in the result set and this removes the duplicate rows. Union statement not only removes duplicate rows, but output is the sorted list.	UNION ALL operation is faster than UNION ALL operation due to following course of outputs. UNION ALL does not remove duplicate rows and does not sort the result.
Constraints	All the queries (result sets) which need to combine need to have the same number of columns; columns should be of the same data type/compatible data types. ORDER BY clauses can only be issued for the overall result set and not within each result set
Examples	CREATE TABLE dbo.Customers (      CustomerID int,CustomerName Varchar(50),        City Varchar(50)) GO INSERT INTO dbo.Customers   VALUES(1,'Govinda Raj','Coimbatore'),         (2,'Krishna Raj','Mangalore') GO CREATE TABLE dbo.Suppliers (      SupplierId int,SupplierName varchar(50),        City Varchar(50)) GO INSERT INTO dbo.Suppliers VALUES        (1,'Govinda Raj','Coimbatore'),        (2,'Durga Devi','Chennai'),         (3,'Krishna Raj','Bangalore'),         (4,'Rama Krishnan','Bangalore') GO EXAMPLE 1: SELECT * FROM dbo.Customers WITH(NOLOCK) UNION SELECT * FROM dbo.Suppliers WITH(NOLOCK) GO CustomerID CustomerName City 1 Govinda Raj Coimbatore 2 Durga Devi Chennai 2 Krishna Raj Mangalore 3 Krishna Raj Bangalore 4 Rama Krishnan Bangalore EXAMPLE 2: SELECT * FROM dbo.Customers WITH(NOLOCK) UNION ALL SELECT * FROM dbo.Suppliers WITH(NOLOCK) GO CustomerID CustomerName City 1 Govinda Raj Coimbatore 2 Krishna Raj Mangalore 1 Govinda Raj Coimbatore 2 Durga Devi Chennai 3 Krishna Raj Bangalore 4 Rama Krishnan Bangalore EXAMPLE 3:  SELECT * FROM ( SELECT CustomerName FROM dbo.Customers WITH(NOLOCK) UNION SELECT SupplierName FROM dbo.Suppliers WITH(NOLOCK) ) A ORDER BY CustomerName CustomerName Durga Devi Govinda Raj Krishna Raj Rama Krishnan EXAMPLE 4: SELECT * FROM ( SELECT CustomerName AS VendorName, City As VendorCity FROM dbo.Customers WITH(NOLOCK) UNION SELECT SupplierName, City FROM dbo.Suppliers WITH(NOLOCK) ) Vendor VendorName VendorCity Durga Devi Chennai Govinda Raj Coimbatore Krishna Raj Bangalore Krishna Raj Mangalore Rama Krishanan Bangalore EXAMPLE 5: SELECT * FROM ( SELECT CustomerName AS VendorName, City As VendorCity FROM dbo.Customers WITH(NOLOCK) UNION ALL SELECT SupplierName, City FROM dbo.Suppliers WITH(NOLOCK) ) Vendor VendorName VendorCity Govinda Raj Coimbatore Krishna Raj Mangalore Govinda Raj Coimbatore Durga Devi Chennai Krishna Raj Bangalore Rama Krishanan Bangalore

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF