Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!feeder.erje.net!eu.feeder.erje.net!eternal-september.org!feeder.eternal-september.org!mx05.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Is a column redundant in a nonclustered index if it's in the clustered index? Date: Tue, 11 Jun 2013 20:53:24 +0200 Organization: Erland Sommarskog Lines: 42 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx05.eternal-september.org; posting-host="78e2ae7e6764121e217a035f19bdb315"; logging-data="10557"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/bn8wsNF0A9YMAge5dTkeH" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:TdNw2Zc7AwET0Do/+LFrmiB143E= Xref: csiph.com comp.databases.ms-sqlserver:1519 (rja.carnegie@gmail.com) writes: > 1. Given that [costcentre] is the first term in the primary key > and the clustered index, is it redundant, not useful, to tack on > [costcentre] at the end of the non-unique key in the > nonclustered index? Depending on the queries it is useful or useless. That is, one cannot say anything a priori. Say that you have a query like: SELECT * FROM tbl WHERE SeemisSubjectID = @somesubject AND costcentre LIKE 'AB%' With only SeemisSubjectID in the index, all umpteen rows with matching SeemisSubjectID has to be scanned for matching costcentre. With costcentre as part of the index key, the optimizer can seek all the way. > 2. If instead I am considering the following nonclustered index, > is [costcentre] redundant? > > CREATE NONCLUSTERED INDEX IX13_Ali_SeemisSubjectID ON AliasSubject > (SeemisSubjectID) INCLUDE (costcentre) > ; Yes, it is redudant, but it is not malign. Since costcentre is part of the clustered key it is included in the index anyway. However, you may decide to change the clustered index or make the table a heap. By explicitly including costcentre in the index, you ensure that the index will remain covering for the query you had in mind. > 3. Are the answers different if [SeemisSubjectID] is a unique key > but I also want to use [costcentre] in a JOIN condition? Yes, if SeemisSubjectID is unique, then adding costcentre as in index key is redudant and you save some space on the upper levels of the index by leaving it out, or only having it as an included column. -- Erland Sommarskog, Stockholm, esquel@sommarskog.se