Search

Advertisements

Showing posts with label INDEX. Show all posts
Showing posts with label INDEX. Show all posts

TSQL Script to Generate Create ColumnStore Index Script

 This article is about the TSQL script which can be used to generate create ColumnStore Index script from the database .

SET CONCAT_NULL_YIELDS_NULL OFF;

SQL Server - Unique index to allow multiple NULL

UNIQUE Constraint/Index :

Both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness.

Unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL,however only one null value is allowed per column. 

--Example for unique index to allow multiple NULL
 
CREATE TABLE Unique_Duplicate (Col1 INT,Col2 VARCHAR(100))
GO
CREATE UNIQUE NONCLUSTERED INDEX Unq_Nci_idx ON Unique_Duplicate(Col1) WHERE Col1 IS NOT NULL
GO
INSERT INTO Unique_Duplicate VALUES (NULL,'Unique index to allow multiple NULL')
INSERT INTO Unique_Duplicate VALUES (NULL,'Unique index to allow multiple NULL')
GO
SELECT * FROM Unique_Duplicate
GO

---------------------------------------------------------
--Example Unique index to allow one particular duplicate value

 
DROP INDEX Unq_Nci_idx ON Unique_Duplicate
GO
CREATE UNIQUE NONCLUSTERED INDEX Unq_Nci_idx ON Unique_Duplicate(Col1) WHERE Col1 <> 1
GO
INSERT INTO Unique_Duplicate VALUES (1,'Unique index to allow one particular duplicate value')
INSERT INTO Unique_Duplicate VALUES (1,'Unique index to allow one particular duplicate value')
GO
SELECT * FROM Unique_Duplicate
GO

--DROP TABLE Unique_Duplicate

 

 

Advertisements