使用CHECKSUM的哈希索引

 Microsoft SQL Server 的最大index键值限定为900 bytes，要创建一个超过900 bytes的index，可以使用CHECKSUM来创建hash index. 使用CHECKSUM创建的hash index可以节省存储空间。

SET NOCOUNT ON;CREATE TABLE PostalCode        (PostalCode NVARCHAR(6),        PostalArea NVARCHAR(20),        PostalCity NVARCHAR(28),        PostalState NVARCHAR(2),        PRIMARY KEY CLUSTERED (PostalCode, PostalArea));GOINSERT INTO PostalCode        (PostalCode, PostalArea, PostalCity, PostalState)VALUES ('382001','Sector 01','Gandhinagar','GJ'),       ('382002','Sector 02','Gandhinagar','GJ'),       ('382002','Koba','Gandhinagar','GJ'),       ('382003','Sector 03','Gandhinagar','GJ'),       ('382004','Sector 04','Gandhinagar','GJ'),       ('382005','Sector 05','Gandhinagar','GJ'),       ('382006','Sector 06','Gandhinagar','GJ'),       ('382007','Sector 07','Gandhinagar','GJ'),       ('382008','Sector 08','Gandhinagar','GJ'),       ('382009','Sector 09','Gandhinagar','GJ'),       ('382010','Sector 10','Gandhinagar','GJ'),       ('382011','Sector 11','Gandhinagar','GJ'),       ('382012','Sector 12','Gandhinagar','GJ'),       ('382013','Sector 13','Gandhinagar','GJ'),       ('382014','Sector 14','Gandhinagar','GJ'),       ('382015','Sector 15','Gandhinagar','GJ'),       ('382016','Sector 16','Gandhinagar','GJ'),       ('382017','Sector 17','Gandhinagar','GJ'),       ('382018','Sector 18','Gandhinagar','GJ'),       ('382019','Sector 19','Gandhinagar','GJ'),       ('382020','Sector 20','Gandhinagar','GJ'),       ('382021','Sector 21','Gandhinagar','GJ'),       ('382022','Sector 22','Gandhinagar','GJ'),       ('382026','Sector 26','Gandhinagar','GJ'),       ('382027','Sector 27','Gandhinagar','GJ'),       ('382028','Sector 28','Gandhinagar','GJ'),       ('382029','Sector 29','Gandhinagar','GJ'),       ('382030','Sector 30','Gandhinagar','GJ'),       ('360001','Race Course','Rajkot','GJ');GOCREATE NONCLUSTERED INDEX idx_PostalAreaSearchNC ON PostalCode (PostalArea, PostalCity, PostalState) INCLUDE (PostalCode);GO

查看执行计划：

 

  --Create a CHECKSUM column on the tableALTER TABLE PostalCodeADD PostalCheckSum AS CHECKSUM(PostalArea, PostalCity, PostalState);--Create another non-clustered index--Use this checksum value, with the PostalCode as an included columnCREATE NONCLUSTERED INDEX idx_PostalChecksumSearchNC ON PostalCode (PostalCheckSum) INCLUDE (PostalCode);GO--Search on the Area, City & StateDECLARE @PostalArea NVARCHAR(20) = 'Sector 28'DECLARE @PostalCity NVARCHAR(28) = 'Gandhinagar'DECLARE @PostalState NVARCHAR(2) = 'GJ'SELECT * FROM dbo.PostalCode WHERE PostalCheckSum = CHECKSUM(@PostalArea, @PostalCity, @PostalState);--Check the relative size of the regualr non-clustered index and the hash indexSELECT OBJECT_NAME(ips.object_id) AS TableName,       si.name AS IndedName,       ips.index_id AS IndexId,       ips.index_type_desc AS IndexType,       ips.avg_record_size_in_bytes AS AverageRecordSize,       ips.min_record_size_in_bytes AS MinimumRecordSize,       ips.max_record_size_in_bytes AS MaximumRecordSizeFROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('PostalCode'),NULL,NULL,'DETAILED') AS ipsINNER JOIN sys.indexes AS si ON ips.index_id = si.index_idWHERE si.name = 'idx_PostalAreaSearchNC' OR si.name = 'idx_PostalChecksumSearchNC';

使用CHECKSUM创建的hash index可以节省存储空间：