InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 09:26:19 GMT20http://www.sqlservercentral.com/Forums/Topic1063682-2904-1.aspxComparison of clustered index table with unindexed heap in this article is not fair, IMHO. Clustered index means we have both data and index. HEAP means we only have data and no index. Author should add an index to the heap to make fair comparison.Author should also show examples of operations where heap beats clustered index table in performance.Sun, 27 Jan 2013 06:22:32 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1063682-2904-1.aspx[quote][b]Steven Willis (6/22/2011)[/b][hr]Now a second question...I personally avoid using UNIQUEIDENTIFIER cols as part of a primary key. But sometimes I have no choice when I've inherited a schema from someone else. I've not found a way to avoid index scans on these cols. Any advice? [/quote]Yes. Determine if the schema requires the primary key to be the clustered index. The two are not the same thing.Check out [url]http://ask.sqlservercentral.com/questions/12/should-my-primary-key-be-clustered[/url] for starters.Wed, 22 Jun 2011 10:43:14 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1063682-2904-1.aspxI know it's trivial but the table names used in some of your SQL statements do not match the the table names reflected in the results. For instance in step3:SELECT *FROM [b]SalesOrderDetail[/b]WHERE SalesOrderID = 43671AND SalesOrderDetailID = 120Heap (1 row(s) affected)Table [b]'SalesOrderDetail_noindex'[/b]. Scan count 1, logical reads 1495.This is true so far in steps 2 and 3.Wed, 22 Jun 2011 10:41:37 GMTtmulherinhttp://www.sqlservercentral.com/Forums/Topic1063682-2904-1.aspx[quote][b]Steven Willis (6/22/2011)[/b][hr]Here's two sample queries that will run against the default DotNetNuke schema. Note the only difference is in the second query in which I've added to the where clause a requirement to make the value of the primary key greater than zero. Adding this line to the second query seems to force an index seek on the table. Is this really doing what it seems to be doing? If so, it's a very valuable technique for forcing seeks instead of scans.[/quote]Yes and no.*Note in the query plan that each query has exactly the same cost, and each element of the two queries has the same cost as its counterpart (even the scan/seek). This plus the actual number of rows and row sizes indicates that the data you're querying against is simple enough that there's not much difference in performance between a seek and a scan. Here we get into the "black box" nature of the query optimizer. It does appear that adding a requirement on the primary key forced an index seek, but that is not guaranteed to happen whenever you do so. Sometimes a scan is more efficient than a seek, and in my experience the optimizer is usually, but not always, better than I am at figuring that out.* (see my previous post on the usefulness of this answer)Wed, 22 Jun 2011 10:31:56 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1063682-2904-1.aspxNow a second question...I personally avoid using UNIQUEIDENTIFIER cols as part of a primary key. But sometimes I have no choice when I've inherited a schema from someone else. I've not found a way to avoid index scans on these cols. Any advice? Wed, 22 Jun 2011 10:10:25 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1063682-2904-1.aspxHere's two sample queries that will run against the default DotNetNuke schema. Note the only difference is in the second query in which I've added to the where clause a requirement to make the value of the primary key greater than zero. Adding this line to the second query seems to force an index seek on the table. Is this really doing what it seems to be doing? If so, it's a very valuable technique for forcing seeks instead of scans.[code="sql"]SELECT up.PropertyValue ,ppd.PortalIDFROM dbo.UserProfile AS upINNER JOIN dbo.ProfilePropertyDefinition AS ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionIDWHERE up.UserID = 2345 -- Put a real UserID here SELECT up.PropertyValue ,ppd.PortalIDFROM dbo.UserProfile AS upINNER JOIN dbo.ProfilePropertyDefinition AS ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionIDWHERE [b][size="3"]ppd.PropertyDefinitionID &gt; 0[/size][/b] AND up.UserID = 2345 -- Put a real UserID here [/code][For some reason the image is not displaying, but you can click on the link and download it to view in SSMS.][img]http://www.sqlservercentral.com/Forums/Attachment9120.aspx[/img]Wed, 22 Jun 2011 09:59:48 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1063682-2904-1.aspxVery cool. Thanks!Wed, 22 Jun 2011 09:43:38 GMTamenjonathanhttp://www.sqlservercentral.com/Forums/Topic1063682-2904-1.aspx[quote][b]amenjonathan (6/22/2011)[/b][hr]I have heard from more than one person that all columns in the clustered index also show up in each non-clustered index on that same table, which is why you should keep your clustered index columns to the bare minimum you can get away with.Is this true?[/quote]Yes and no*. The clustered index (plus a uniqueifier if necessary) is the row pointer for non-clustered indexes (See http://msdn.microsoft.com/en-us/library/ms177484.aspx) So a smaller clustered index will mean smaller non-clustered indexes, all else being equal.BUTif you have few non-clustered indexes, and you have a column that is almost always included in criteria for queries, you can gain performance if that's part of the clustered index at a small cost of space in the non-clustered indexes (indeed, you'd likely be moving that column from its own non-clustered index to the clustered index, so you could actually save space.)Note: When I speak about changing the clustered index, I'm speaking of conceptually changing it preferably before any data is in the table, and definitely before moving it to production. Changing a clustered index in production can be difficult, to say the least. This is one of those areas where planning is key.* This is ALWAYS the answer to any Yes or No question. Always.Wed, 22 Jun 2011 09:33:15 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1063682-2904-1.aspxI have heard from more than one person that all columns in the clustered index also show up in each non-clustered index on that same table, which is why you should keep your clustered index columns to the bare minimum you can get away with.Is this true?Wed, 22 Jun 2011 09:08:53 GMTamenjonathanhttp://www.sqlservercentral.com/Forums/Topic1063682-2904-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Stairway+Series/72351/">Stairway to SQL Server Indexes: Step 3, Clustered Indexes</A>[/B]Mon, 14 Feb 2011 10:37:07 GMTDavid Durant