should I include the primary key in the clustered index in order to make it unique?

Question

I have the following table:

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))

that has an identity as a clustered index.

however, as it is queried mostly according to these 2 columns:

[CountryCode]         VARCHAR(2)                       NOT NULL,
[AddressFormatId]     INT                              NOT NULL,

I thought about creating a clustered index on them.

they are pretty small, but they are not unique. should I include the id column in the cluster index so that I can make it unique?

Yes or No and Why?

This is the main query I would like to go faster:

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

This is how I improved this procedure:

I have created the following indexes:

    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)

I have changed the relevant columns in the table, from VARCHAR to NVARCHAR, in order to eliminate the need of conversion. the Table became like this:

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

I have changed the procedure:

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

when comparing the following:

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

we get this:

here is the execution plan of the old procedure

here is the execution plan of the new procedure

Id say test it out in your environment as a unique index can help the engine make much better choices. Test create the unique one and run your queries against it after dumping your plan cache, then compare plans. You might notice much better joins and faster results from the unique one. Do note the constraint will need to be unique checked every write which will have a very tiny impact on writes, but if you're doing thousands of write operations a second.... — Ali Razeghi - AWS, Aug 9, 2016 at 16:32
@Paparazzi I am not sure about that, on the table these fields are stored as VARCHAR. I might have to alter the columns in the table from varchar to nvarchar, since that seems easier than changing the application — Marcello Miorelli, Aug 9, 2016 at 17:13
But the posted table definition is nvarchar. Have the @ variables match the actual column type. — paparazzo, Aug 9, 2016 at 17:18

Tara Kizer · Accepted Answer · 2016-08-09 18:27:07Z

If you were to use those two columns as the clustered index, first have to change the PK to non-clustered as BlackStar mentioned, and they are not unique, then SQL Server will add a uniquifier to make it unique. That uniquifier is 4 bytes and used only when necessary. You could instead add the identity column to the end of that index. The identity column is defined as int which is 4 bytes, so there wouldn't be a size difference between the two versions of the index.

EDIT now that the main query has been provided.

I would remove the table variable if you want it to go faster. Do it all in one query. You can easily use a CTE instead.

Like this:

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]

I think the uniquifier is variable length so it adds two more bytes per row. Have a look at chat.stackexchange.com/transcript/179/2013/8/15/6-7 and follow the link to the video for more info. — Mikael Eriksson, Aug 9, 2016 at 16:24
@MikaelEriksson, that's interesting as I'd never come across that it was variable. I guess the size is 4 bytes, but that there is also overhead. Found this as I was too lazy to watch video: aboutsqlserver.com/2011/11/24/…. It's tl;dr, so answer is at the bottom. — Tara Kizer, Aug 9, 2016 at 16:36
@MikaelEriksson from what I've read it's variable in the sense that it's 4 or 0 depending on uniqueness. However that does mean that if your CI is mostly unique it will definitely be smaller than adding the int. (most cases won't need the uniquifier so it will be smaller) — Kenneth Fisher, Aug 9, 2016 at 17:29

BlackStar · Accepted Answer · 2016-08-09 15:53:42Z

Actually, the primary reason for making indexes is for speeding up query's. So if you are trying to speed up your query then, as long as the index have the necessary and only necessary columns the best the index would be.

However, you already have a CLUSTERED PRIMARY KEY that insides resides a CLUSTERED INDEX so you can't have both at the CLUSTERED INDEX at the same time.

Rather you choose to make your PRIMARY KEY not CLUSTERED and then your INDEX could be CLUSTERED. Otherwise, you won't be able to make another CLUSTERED INDEX. You have to evaluate what is the most important and cost time and performance.

Also, you INDEXES are not made to be UNIQUE by purpose, if you strongly believe you usually search by [CountryCode] and [AddressFormatId] more often than any other column, then the advise would be to create the PRIMARY KEY as NOT CLUSTERED and create the CLUSTERED INDEX containing just the pair of two columns that you use for searching and joining.

NOTE: the CLUSTERED INDEX would work best just by looking up for both columns at the time, if you believe those columns are the most searchable ones but not both at the same time, then the INDEX would drawback the performance.

paparazzo · Accepted Answer · 2016-08-10 06:01:18Z

I would leave the PK clustered index alone

create two non clustered indexes on

[CountryCode]
[AddressFormatId]

as for query that was added late

there is no ItemNo in the table definition

since you are joining on TaxRegionId

index on TaxRegionId

SELECT DISTINCT ISNULL(pst.TaxCode, '') as TaxCode
     , ISNULL(pst.TaxRate, 0) as TaxRate
FROM    dbo.[ProductShipTax] pst
INNER JOIN dbo.[ShipTaxAddress] a 
      ON pst.TaxRegionId = a.TaxRegionId
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)

and     pst.[ItemNo] = @itemNo
AND     @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo]

I have added the main query I am tuning, related to this table. — Marcello Miorelli, Aug 9, 2016 at 17:00
@Paparazzi, if both columns are used in most queries, then it wouldn't make sense to have two indexes with one column each. A composite index would perform better, meaning both columns are in one index. I would test this composite index as non-clustered and then test again as clustered. The test should include several different queries. — Tara Kizer, Aug 9, 2016 at 18:16
@Tara "as it is queried mostly according to these 2 columns" If you take that as the query is always both columns then yes. — paparazzo, Aug 9, 2016 at 18:18

Stack Exchange Network

should I include the primary key in the clustered index in order to make it unique?

3 Answers 3

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-server
index
sql-server-2005
performance
primary-key
query-performance
or ask your own question.

Hot Network Questions

should I include the primary key in the clustered index in order to make it unique?

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-serverindexsql-server-2005performanceprimary-keyquery-performance or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-server
index
sql-server-2005
performance
primary-key
query-performance
or ask your own question.