question

EuniceRH avatar image
EuniceRH asked

UNIQUE NONCLUSTERED INDEX with INCLUDE columns

Is a UNIQUE NONCLUSTERED INDEX ok with INCLUDE columns or will it cause performance problems? It possibly has caused performance issues. I do a SELECT statement without a WHERE clause on it. I have always done this SELECT without a WHERE, but now it is causing EVEN MORE performance problems, since I added the INCLUDE columns on the UNIQUE NONCLUSTERED index. I have these indexes both on the same table. The Unique column is not the PK. ALTER TABLE [dbo].[tblContracts] ADD CONSTRAINT [tblContracts$PrimaryKey] PRIMARY KEY CLUSTERED ( [CustomerID] ASC, [StartDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX [tblContracts$PublicIdEtc] ON [dbo].[tblContracts] ( [PublicId] ASC) INCLUDE ( [ContractID] ASC, [CustomerID] ASC, [StartDate] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO Another issue is I could change the CLUSTERED index to ContractID, and make the CustomerID/StartDate another NONCLUSTERED UNIQUE (or PK) index, but I tried that and had to go backwards (but maybe that would help too, assuming that I take out the INCLUDE columns on the above UNIQUE index.)
sql-server-2008-r2indexingunique
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
Adding or removing an index can change the way the query optimizer decides to handle the query. You can't say that adding a unique nonclustered index with included columns will cause performance issues, nor that it will improve performance - it depends! It would be better to look at the query execution plans and see how/if/where the index changes the plan - this will then give you the information you need to decide if the index is good or bad. Maybe you could post the table DDL and the query, and even execution plans, and we can see what to do from there
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

"SELECT "tblContracts"."CustomerID", "tblContracts"."StartDate" FROM "dbo"."tblContracts" "tblContracts" ORDER BY "tblContracts"."PublicId" DESC" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0745482" StatementEstRows="23710" StatementOptmLevel="TRIVIAL" QueryHash="0x29766633680962AC" QueryPlanHash="0x444833433A3A45E7">
0 Likes 0 ·
in my case modifying this index to remove the INCLUDE columns helped performance, the sql query execution plan no longer uses this index at all, uses the clustered index.
0 Likes 0 ·
Removing the covering index and reverting back to a clustered index seek/scan, together with a key lookup operation - possibly suggests that stats where out of date?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.