November 22, 2011 at 9:35 am
Is is standard practice to cover your queries by adding non-clustered that is essentially your primary key.
Only difference is that you add included columns to make sure you cover your query?
I say this because in my result I receive from my dm_db_missing_index.. It is suggesting that I do just that.
Thank you
November 22, 2011 at 9:54 am
Includes in an index typical reference columns that cannot be part of the index itself such as bit columns. The includes also enable an index with more than the allowed number of columns.
This may be considered standard if your queries often make selections with criteria based on non-indexable columns.
November 22, 2011 at 9:58 am
jerry-621596 (11/22/2011)
Includes in an index typical reference columns that cannot be part of the index itself such as bit columns. The includes also enable an index with more than the allowed number of columns.This may be considered standard if your queries often make selections with criteria based on non-indexable columns.
Really?
BEGIN TRAN
CREATE TABLE #tst
(id int identity(1,1) PRIMARY KEY CLUSTERED
,bt BIT NOT NULL
)
INSERT INTO #tst (bt) VALUES (0)
INSERT INTO #tst (bt) VALUES (1)
CREATE NONCLUSTERED INDEX ix ON dbo.#tst (bt)
SELECT * FROM #tst WHERE bt = 1
--index seek
--idbt
--21
ROLLBACK
November 22, 2011 at 10:01 am
It comes down to what is best for your system.
Watch all of those for a much deeper understanding.
http://www.youtube.com/watch?v=RZ4YTg4S7HI&feature=plpp_video
http://www.youtube.com/watch?v=Kcnj5bQghjY&feature=plpp_video
http://www.youtube.com/watch?v=XmE_Utp8CY0&feature=plpp_video
November 22, 2011 at 10:50 am
Great videos.
The third one is exactly what I needed.
Thank you
November 22, 2011 at 10:51 am
All of then is exactly what you need... tone of great content in there ;-).
November 22, 2011 at 11:05 am
jwbart06 (11/22/2011)
Is is standard practice to cover your queries by adding non-clustered that is essentially your primary key.
Depends if your primary key is clustered or not. If it is, no, probably not, although there are cases where that is necessary. If the pk is nonclustered, probably not unless you're doing range queries because singleton seeks with lookups aren't expensive
I say this because in my result I receive from my dm_db_missing_index.. It is suggesting that I do just that.
Missing indexes is a guideline, not a directive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply