Sql-Server
我應該在聚集索引中包含主鍵以使其唯一嗎？
我有下表：
IF OBJECT_ID('[dbo].[ShipTaxAddress]') IS NOT NULL DROP TABLE [dbo].[ShipTaxAddress] GO CREATE TABLE [dbo].[ShipTaxAddress] ( [TaxRegionAddressId] INT NOT NULL, [TaxRegionId] INT NOT NULL, [CountryCode] VARCHAR(2) NOT NULL, [AddressFormatId] INT NOT NULL, [MatchAddressLine1] NVARCHAR(50) NULL, [MatchAddressLine2] NVARCHAR(50) NULL, [MatchAddressLine3] NVARCHAR(50) NULL, [MatchAddressLine4] NVARCHAR(50) NULL, [MatchAddressLine5] NVARCHAR(50) NULL, [MatchAddressLine6] NVARCHAR(50) NULL, [MatchPostalCode] VARCHAR(20) NULL, CONSTRAINT [PK_ShipTaxAddress] PRIMARY KEY CLUSTERED ([TaxRegionAddressId] asc))具有作為聚集索引的標識。
但是，因為它主要根據以下兩列進行查詢：
[CountryCode] VARCHAR(2) NOT NULL, [AddressFormatId] INT NOT NULL,我考慮過在它們上創建一個聚集索引。
它們很小，但不是唯一的。我應該在集群索引中包含 id 列以便我可以使其唯一嗎？
是或否，為什麼？
這是我想更快的主要查詢：
ALTER PROCEDURE [dbo].[udpProductTaxRateGet] ( @itemNo varchar ( 20 ), @calculateDate datetime, @addressLine1 nvarchar( 50 ), @addressLine2 nvarchar( 50 ), @addressLine3 nvarchar( 50 ), @addressLine4 nvarchar( 50 ), @addressLine5 nvarchar( 50 ), @addressLine6 nvarchar( 50 ), @postalCode nvarchar( 20 ), @countryCode varchar( 2 ), @addressFormatID int ) WITH EXECUTE AS 'webUserWithRW' AS --see Bocss2.dbo.[fnGetProductTax] for equivalent logic and comments in Bocss DECLARE @Addresses TABLE (TaxRegionId int NOT NULL) INSERT INTO @Addresses(TaxRegionId) SELECT DISTINCT TaxRegionId FROM dbo.[ShipTaxAddress] WHERE [CountryCode] = @countryCode AND [AddressFormatID] = @addressFormatID AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine1]), ISNULL(@addressLine1, '')) = ISNULL(@addressLine1, '') AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine2]), ISNULL(@addressLine2, '')) = ISNULL(@addressLine2, '') AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine3]), ISNULL(@addressLine3, '')) = ISNULL(@addressLine3, '') AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine4]), ISNULL(@addressLine4, '')) = ISNULL(@addressLine4, '') AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine5]), ISNULL(@addressLine5, '')) = ISNULL(@addressLine5, '') AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine6]), ISNULL(@addressLine6, '')) = ISNULL(@addressLine6, '') AND @postalcode Like ISNULL ( CONVERT(nvarchar(20),[MatchPostalCode]), @postalcode) SELECT DISTINCT ISNULL(pst.TaxCode, '') as TaxCode , ISNULL(pst.TaxRate, 0) as TaxRate FROM dbo.[ProductShipTax] pst INNER JOIN @Addresses a ON pst.TaxRegionId = a.TaxRegionId WHERE pst.[ItemNo] = @itemNo AND @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo] GO這就是我改進此過程的方式：
我創建了以下索引：
CREATE INDEX IDX_ShipTaxAddress_ShipTaxAddress ON dbo.[ShipTaxAddress] (CountryCode, AddressFormatID, MatchPostalCode) INCLUDE (TaxRegionId, [MatchAddressLine1], [MatchAddressLine2], [MatchAddressLine3], [MatchAddressLine4], [MatchAddressLine5], [MatchAddressLine6]) GO CREATE NONCLUSTERED INDEX IX_ProductShipTax_ITemNo_DateFrom_DateTo ON [dbo].[ProductShipTax] ( [ItemNo] ASC , [DateFrom] ASC , [DateTo] ASC ) INCLUDE (TaxRegionId ,TaxCode,TaxRate) WITH (DROP_EXISTING=ON)我已將表中的相關列從 VARCHAR 更改為 NVARCHAR，以消除轉換的需要。表變成了這樣：
IF OBJECT_ID('[dbo].[ShipTaxAddress]') IS NOT NULL DROP TABLE [dbo].[ShipTaxAddress] GO CREATE TABLE [dbo].[ShipTaxAddress] ( [TaxRegionAddressId] INT NOT NULL, [TaxRegionId] INT NOT NULL, [CountryCode] VARCHAR(2) NOT NULL, [AddressFormatId] INT NOT NULL, [MatchAddressLine1] NVARCHAR(50) NULL, [MatchAddressLine2] NVARCHAR(50) NULL, [MatchAddressLine3] NVARCHAR(50) NULL, [MatchAddressLine4] NVARCHAR(50) NULL, [MatchAddressLine5] NVARCHAR(50) NULL, [MatchAddressLine6] NVARCHAR(50) NULL, [MatchPostalCode] NVARCHAR(20) NULL, CONSTRAINT [PK_ShipTaxAddress] PRIMARY KEY NONCLUSTERED ([TaxRegionAddressId] asc)) GO我改變了程序：
ALTER PROCEDURE [DenormV2].[udpProductTaxRateGet] ( @itemNo varchar ( 20 ), @calculateDate datetime, @addressLine1 nvarchar( 50 ), @addressLine2 nvarchar( 50 ), @addressLine3 nvarchar( 50 ), @addressLine4 nvarchar( 50 ), @addressLine5 nvarchar( 50 ), @addressLine6 nvarchar( 50 ), @postalCode nvarchar( 20 ), @countryCode varchar( 2 ), @addressFormatID int ) WITH EXECUTE AS 'webUserWithRW' AS --see Bocss2.dbo.[fnGetProductTax] for equivalent logic and comments in Bocss SELECT @postalcode = CASE WHEN @postalcode = N'' THEN NULL ELSE @postalcode END SELECT @addressLine1 = CASE WHEN @addressLine1 = N'' THEN NULL ELSE @addressLine1 END SELECT @addressLine2 = CASE WHEN @addressLine2 = N'' THEN NULL ELSE @addressLine2 END SELECT @addressLine3 = CASE WHEN @addressLine3 = N'' THEN NULL ELSE @addressLine3 END SELECT @addressLine4 = CASE WHEN @addressLine4 = N'' THEN NULL ELSE @addressLine4 END SELECT @addressLine5 = CASE WHEN @addressLine5 = N'' THEN NULL ELSE @addressLine5 END SELECT @addressLine6 = CASE WHEN @addressLine6 = N'' THEN NULL ELSE @addressLine6 END SELECT TOP 1 ISNULL(pst.TaxCode, '') as TaxCode , ISNULL(pst.TaxRate, 0) as TaxRate FROM dbo.[ProductShipTax] pst WHERE EXISTS ( SELECT TaxRegionId FROM dbo.[ShipTaxAddress] WHERE [CountryCode] = @countryCode AND [AddressFormatID] = @addressFormatID AND ([MatchAddressLine1] = @AddressLine1 OR ([MatchAddressLine1] IS NULL AND @AddressLine1 IS NULL) ) AND ([MatchAddressLine2] = @AddressLine2 OR ([MatchAddressLine2] IS NULL AND @AddressLine2 IS NULL) ) AND ([MatchAddressLine3] = @AddressLine3 OR ([MatchAddressLine3] IS NULL AND @AddressLine3 IS NULL) ) AND ([MatchAddressLine4] = @AddressLine4 OR ([MatchAddressLine4] IS NULL AND @AddressLine4 IS NULL) ) AND ([MatchAddressLine5] = @AddressLine5 OR ([MatchAddressLine5] IS NULL AND @AddressLine5 IS NULL) ) AND ([MatchAddressLine6] = @AddressLine6 OR ([MatchAddressLine6] IS NULL AND @AddressLine6 IS NULL) ) AND (@postalcode = [MatchPostalCode] OR ([MatchPostalCode] IS NULL AND @postalcode IS NULL) ) AND TaxRegionId = pst.TaxRegionId ) AND pst.[ItemNo] = @itemNo AND @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo] GO比較以下內容時：
USE US16HSMMProduct_ORIGINAL GO exec dbo.udpProductTaxRateGet @itemNo=N'31997299', @calculateDate='Aug 8 2016 1:01:46:760PM', @addressLine1=N'', @addressLine2=N'', @addressLine3=N'', @addressLine4=N'', @addressLine5=N'', @addressLine6=N'FL', @postalcode=N'', @countryCode=N'US', @addressFormatID=2 go USE US16HSMMProduct_AFTER_CHANGES GO exec DenormV2.udpProductTaxRateGet @itemNo=N'31997299', @calculateDate='Aug 8 2016 1:01:46:760PM', @addressLine1=N'', @addressLine2=N'', @addressLine3=N'', @addressLine4=N'', @addressLine5=N'', @addressLine6=N'FL', @postalcode=N'', @countryCode=N'US', @addressFormatID=2 go我們得到這個：
如果您要將這兩列用作聚集索引，首先必須將 PK 更改為非聚集，如 BlackStar 所述，它們不是唯一的，然後 SQL Server 將添加一個唯一性使其唯一。該唯一符為 4 個字節，僅在必要時使用。您可以改為將標識列添加到該索引的末尾。標識列定義為 int，即 4 個字節，因此兩個版本的索引之間不會有大小差異。
編輯現在已經提供了主要查詢。
如果您希望它更快，我會刪除 table 變數。在一個查詢中完成所有操作。您可以輕鬆地改用 CTE。
像這樣：
WITH a AS ( SELECT DISTINCT TaxRegionId FROM dbo.[ShipTaxAddress] WHERE [CountryCode] = @countryCode AND [AddressFormatID] = @addressFormatID AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine1]), ISNULL(@addressLine1, '')) = ISNULL(@addressLine1, '') AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine2]), ISNULL(@addressLine2, '')) = ISNULL(@addressLine2, '') AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine3]), ISNULL(@addressLine3, '')) = ISNULL(@addressLine3, '') AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine4]), ISNULL(@addressLine4, '')) = ISNULL(@addressLine4, '') AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine5]), ISNULL(@addressLine5, '')) = ISNULL(@addressLine5, '') AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine6]), ISNULL(@addressLine6, '')) = ISNULL(@addressLine6, '') AND @postalcode Like ISNULL ( CONVERT(nvarchar(20),[MatchPostalCode]), @postalcode)) SELECT DISTINCT ISNULL(pst.TaxCode, '') as TaxCode , ISNULL(pst.TaxRate, 0) as TaxRate FROM dbo.[ProductShipTax] pst INNER JOIN a ON pst.TaxRegionId = a.TaxRegionId WHERE pst.[ItemNo] = @itemNo AND @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo]