InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 14:26:46 GMT20http://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxBecause we can have only one Primary key in a tableTue, 10 Apr 2012 04:09:06 GMTManoj Sahoohttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxPlease note: 2-year old thread.Fri, 13 Aug 2010 04:19:34 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxFor Clustered Index,Data and index are stored in same place.ie., it stores logically with the table data.So that we have only one clustered index for a table.An index may compriese more than one column which is known as Composite Index.For Non-Clustered Index , Data and Index are stored in different place.Here we can have 249 Non Clustered (2005) and 999 Non clustered(2008)for a single tableFri, 13 Aug 2010 01:37:02 GMTvinu.kutty86http://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxI still don't get how there can be multiple clustered indexes on the same table in DB2. Once the data is physically sorted based on one column, the other indexes are simply going point to that location.Thu, 04 Feb 2010 22:18:29 GMTHemanth Rameshhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxI merely gave it as a support for my opinion that for a different order of data, SQL Server needs to store it in the order specified by the index definition........your concerns are true.....Thu, 17 Apr 2008 04:54:15 GMTChandrachurh Ghoshhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspx[quote][b]chandrachurhghosh (4/17/2008)[/b][hr]Well, to support my opinion about storage and clustered index, try out the following....create a view with schema binding......then try to create a non-clustered index on it......[/quote]Indexed views are a whole nother story. The requirements for creating an indexed view are that the first index is clustered. That is specific to indexed views and has no bearing on tables (heaps or otherwise).Very simple reason. A view without a clustered index does not have persisted data. It's just a saved select statement. Creating a clustered index on it means that SQL will persist the results of that view to disk, as if it were a table. Once its persisted, then NC indexes can be created on it.Thu, 17 Apr 2008 04:25:51 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspx[quote][b]chandrachurhghosh (4/17/2008)[/b][hr]well....logically or functionally you can have n number of indexes at place for your database, but physically it depends upon how many copies of data you want to keep....Isn't this valid?[/quote]Sure. In SQL Server, an index is a stores a seperate set of data for the columns in it. That's why too many indexes can have an effect on insert/update performance.The distinction is that a clustered index always has all the columns of the table in the leaaf pages (because it is the table) while a nonclustered index doesn't (but in SQL 2005, because of the include columns, it can)The other reason that a table cannot have 2 clustered indexes is because the clustering key is used as the row's 'address'In a heap, the RID identifies the location of the row and is composed of the fileID, the page ID and the slot index. This value is used by nonclustered idnees to locate the row, should a lookup be required.Once the table gets a clustered index, the RID falls away and the clustering key becomes the row identifier. The clustereing key is hence added to all nonclustered indexes in place of the RIDThu, 17 Apr 2008 04:19:09 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxWell, to support my opinion about storage and clustered index, try out the following....create a view with schema binding......then try to create a non-clustered index on it......Thu, 17 Apr 2008 04:02:45 GMTChandrachurh Ghoshhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxThe OP asked why we can only have one clustered index per table.A lot of people responded why this is the case for SQL Server. I posted about DB2 MDC indexes to show there are other ways to tackle this problem.I repeat that the explanation I gave for MDC is very simplified, and that it really does achive what appears to be impossible - a row is stored once only and physically clustered in multiple dimensions. If anyone has further questions about how this is done, please find a DB2 forum to ask these questions.Thu, 17 Apr 2008 03:56:12 GMTEdVassiehttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxI accept that this aspect was unknown to me, that a table need not have a Clustered index to have a NC index...thanks for that....but does this change my basic statement that -The point in question is that how many copies of data you want to keep...if it is one....you can store it in only one order physically.....the index illustrated by EdVassie is merely a clustered index with two dimensions...as Last Name, then First name in the case of an address book....wherein for his case it is type of object, then objectid or something.....these are basics friends.....well....logically or functionally you can have n number of indexes at place for your database, but physically it depends upon how many copies of data you want to keep....Isn't this valid?Thanks for the discussion.Thu, 17 Apr 2008 03:16:17 GMTChandrachurh Ghoshhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxThat is not true. The definition of a heap is a table that does not have a clustered index, not a table with no index at all.It is very possible to have a heap with a nonclustered index and a query that refereces some columns not in the nonclustered index tree do an index seek (with Bookmark/RID lookup) on a heap.All it requires is for the optimiser decides that the seek/lookup is cheaper than the scan. That requires that the query affects something less than 1% of the table.Let's see this example....[code]-- the PK is created as nonclustered. This is a heap with a single nonclustered index on it.CREATE TABLE [dbo].[IndexTest]( [ID] [int] identity, [CreationDate] [datetime]CONSTRAINT [pk_IndexTest] PRIMARY KEY NONCLUSTERED ([ID] ASC))-- Now, lots of data.. I do mean, lots. INSERT INTO IndexTest (CreationDate) SELECT DATEADD(dd,col1.column_id + col2.column_id,'2008/01/01') FROM sys.columns col1, sys.columns col2-- On my test DB, this inserted 484416 rows.-- Now, going to see if I can get an index seek, on a heap, even though I'm querying a column not in the NC index.SELECT * FROM IndexTest WHERE ID BETWEEN 2000 AND 3000 -- 1001 rows affected. Table scanSELECT * FROM IndexTest WHERE ID BETWEEN 200 AND 300 -- 101 rows affected. Index seek + Rid lookup[/code]The IAM is not an index. The IAM is a allocation mechanism within SQL that says, for each heap, cluster or NC index in the system, which extents are allocated to the object. It's a way for the storage engine to quickly tell which extents within the database file belong to which objects.Thu, 17 Apr 2008 02:45:52 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxNow drop the index and create the following index on the table -CREATE NONCLUSTERED INDEX [TEST1]ON [dbo].[test1] ( [test] ASC)INCLUDE ([id])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]Now check the query execution plan for the following query -SELECT * FROM test1WHERE [test]='11'I will tell you why.....it is no longer heap....a heap by definition is not indexed other than its reference in the IAM.....with the above non-clustered index you are saving another copy of the data in order as specified in the definition of the index.....Hence, even though you can create a non-clustered index on a heap...it is of no use, it will always result in a table scan unless the columns involved in the query are present in the index definition .....we already have a non-clustered index - the Index allocation Map (IAM)Thu, 17 Apr 2008 02:24:37 GMTChandrachurh Ghoshhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxBecause the index is not covering and the table scan is cheaperTo do an index seek in this case requires 3 reads. 2 of the index tree and one of the table to fetch the column ID which is not in the index. A table scan just requires 1 read. If the index had ID either in the key or as an include, or the query just returned test, you'd get an index seek.To continue on your example...[code]SELECT test FROM test1 WHERE test = '11' -- index seek on index TEST1CREATE NONCLUSTERED INDEX Test_Covering ON Test1 (Test) INCLUDE (ID)SELECT * FROM test1 WHERE [test]='11' -- Index seek on index Test_Covering[/code]Does that make sense?Thu, 17 Apr 2008 02:24:19 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxOk GilaMonster.....I am giving an example....can you please tell me why this happens?CREATE TABLE [dbo].[test1]( [id] [int] IDENTITY(1,1) NOT NULL, [test] [varchar](255) NULL)INSERT INTO test1([test])VALUES('1')INSERT INTO test1([test])VALUES('11')CREATE NONCLUSTERED INDEX [TEST1]ON [dbo].[test1] ( [test] 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]SELECT * FROM test1WHERE [test]='11'Check the query execution plan for the above query. It still does a table scan. GilaMonster, can you tell me why?Thu, 17 Apr 2008 02:15:33 GMTChandrachurh Ghoshhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxin 2005, you don't need an indexed view to get what amounts to a second clustered index on a table - you can get the same effect with included columns (new feature in 2005).create a non-clustered index that also includes every non-key column in the table. This will be a copy of the table that's physically stored in key order.not sure if anyone mentioned this about included columns already - didn't see it anyway.Wed, 16 Apr 2008 22:57:31 GMTjezeminehttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspx[quote][b]GilaMonster (4/16/2008)[/b][hr]Primary key never affects the row order in data pages. *Snip*Does that help?[/quote]A lot thanks I assumed that was what primary key implied.I understand the index page id doesn't need to be sequential ( on index defrag it will end up being though ? ) and it is better to link in a new page than shuffle all entries down their corresponding pages. I also understand that if the index pages are mostly cached not physically retrivied whether it is page id 301 or 62 doesn't have much effect on the performance retrieval using that index.I was confused about srienstr's post and the different B-Tree implmentation in DB2 and SQL server [quote]This site (link) states that DB2 only guarantees that a clustered index is initially clustered, clustering is not maintained. While this saves time on inserts (no page splits), I'm glad SQL Server allows index fragmentation instead.[/quote]Why wouldn't the index fragment in exactly the same manner over time for DB2 Type-1 and 2 indexes or is he talking specifically about MDC ?Probably worth drawing a distinction between the clustering of the index only pages inside a SQL server clustered or non-clustered index and the clustering of the data pages as being two different things.Wed, 16 Apr 2008 20:20:50 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspx[quote][b]chandrachurhghosh (4/16/2008)[/b][hr]we cannot have any non-clustered index if we don't have a clustered index on a table[/quote]That's not the case. There's no requirement in SQL server for a table to have a clustered index. Nonclustered indexes can be created on a heap just as they can on a cluster.Wed, 16 Apr 2008 05:37:28 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxwe cannot have any non-clustered index if we don't have a clustered index on a table....also a primary key is by default clustered indexed and by definition unique.....well...from your discussion I got that you need to retrieve data as fast as a clustered index but in different ways.....MS is clever enough to let you features to handle situations like this.......if all the columns required in a query is present in a non-clustered index the SQL server fetches the values from the NC index pool itself. It does not require to go to the main table. Also, we can include columns in NC indexes which also has the same effect. NC indexes are more versatile than indexed views.Hope this helps.....Wed, 16 Apr 2008 04:41:59 GMTChandrachurh Ghoshhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxThe point in question is that how many copies of data you want to keep...if it is one....you can store it in only one order physically.....now by definition a clustered index is associated with the physical order.....the index illustrated by EdVassie is merely a clustered index with two dimensions...as Last Name, then First name in the case of an address book....wherein for his case it is type of object, then objectid or something.....these are basics friends.....well....logically or functionally you can have n number of indexes at place for your database, but physically it depends upon how many copies of data you want to keep....For more complex ways of data storage and retrieval, check out cubes in SQL Server Analysis Services.It is good to argue, but it is useless if you don't accept.Wed, 16 Apr 2008 04:38:21 GMTChandrachurh Ghoshhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxThe approach to clustering is different between SQL Server and DB2.As most SQL people know, SQL Server will always physically insert a new row in the correct place in a cluster index. This is because the bottom-level leaf pages of a SQL Server cluster index are also the data pages. DB2 has 2 types of cluster index, both of which work differently to cluster indexes in SQL Server. The original DB2 cluster index (written in the 1970s) most closely compares to a SQL non-clustered index on a table with no cluster index. In other words, the bottom level of the cluster index just has RID pointers into the table. When a row is inserted DB2 will place it in the first available slot, with the index updated as normal. DB2 keeps a statistic called clustered% to show how close the actual physical order of rows is to the clustered index definition. Most DB2 DBAs would rebuild a cluster index when the cluster% drops below 95%. When a DB2 cluster index is rebuilt, the table rows are sorted into the sequence of the cluster index. Immediately after a DB2 cluster index is rebuilt, both the index and the data are in the same physical sequence. There are advantages and disadvantages of the DB2 approach compared to the SQL Server approach.DB2 Multiple Dimension Clustering (MDC) indexes work in a different way. See my previous post in this thread for an introduction to the MDC concept, which came into DB2 about 2001. When a new row is inserted in a table with a MDC index, it is stored in the correct extent for the key entry. In this way, a MDC index is self-maintaining, and a MDC index rebuild is only necessary when it is desired to reclaim space.Wed, 16 Apr 2008 03:37:22 GMTEdVassiehttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxPrimary key never affects the row order in data pages. It's the clustered index that does that. Now, by default the primary key is a clustered index, but that is not required.Why would you not want a primary key on a table with no clustered index?What SQL does when a page fills and needs to be split is to allocate a new page somewhere in the data file. Where is not important. It then adjusts the next and previous pointers of the original page so that the new one is linked in in the correct logical order. The physical order of the pages may well not correspond to the ogical order. That's fragmentationLet's say we have a table with 5 pages, with a clustered index (hence row order matters). (Under 8 pages, so mixed extents1st page - pageID 250, previous page pointer = null, next page pointer = 2512nd page - pageID 251, previous page pointer = 250, next page pointer = 2523rd page - pageID 252, previous page pointer = 251, next page pointer = 2644th page - pageID 264, previous page pointer = 252, next page pointer = 275 5th page - pageID 275, previous page pointer = 264, next page pointer = null.Now, page 3 is full and needs, for whatever reason, to be split. SQL allocates a new page. Say everything below page 300 is used, so the new page is 301. Now the table looks like this.1st page - pageID 250, previous page pointer = null, next page pointer = 2512nd page - pageID 251, previous page pointer = 250, next page pointer = 2523rd page - pageID 252, previous page pointer = 251, next page pointer = 3014th page - pageID 301 (new), previous page pointer = 252, next page pointer = 2645th page - pageID 264, previous page pointer = 301, next page pointer = 275 6th page - pageID 275, previous page pointer = 264, next page pointer = null.Only the pages before and after the new page have to change. It would take waaay too long to adjust all the pages in the table (imagine a table with a few hundred thousand pages)When you have a heap (no cluster) the order of rows has no meaning, and new rows are just added to the last page of the table.Does that help?Wed, 16 Apr 2008 03:33:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxIt would help to know what he thought multiple clustered indexes would help with ...I never would have guessed that the primary key did not effect the row order in data pages in a non clustered table. Up until this point I thought a index defrag on a non-clustered table reorged the data pages by primary key order. Stuck in the monotonically increasing primary key paradigm I guess. When would you want a primary key rather than a uniqueness constraint on a non clustered table ?I am curious as to the difference between DB2 as SQL servers index implementation.As the existing index pages fill and we get page splits are we saying DB2 and SQL server manage this differently ? I would have expected a new page to be allocated to the index linked between the two entries that previously bounded the new insert and the higher level index pages be updated recursively. Obviously I am missing something.Wed, 16 Apr 2008 03:07:40 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxThe suggestion from another person about using indexed views for the purpose is probably the best. It's a separate copy of the data (with extras thrown in if needed) that's maintained automatically for you. You should ensure that the appropriate SET options (such as ANSI defaults, arithabort, etc) are enabled on your server.You could also create some non-clustered covering indices on your table although the size of an index in SQL Server is limited, but in 2005 you can "incldue" columns outside of the index key.Tue, 15 Apr 2008 15:14:28 GMTIan Yateshttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspx[url='http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0007201.htm']This site ([u]link[/u])[/url] states that DB2 only guarantees that a clustered index is [i]initially[/i] clustered, clustering is not maintained. While this saves time on inserts (no page splits), I'm glad SQL Server allows index fragmentation instead.Additionally, the MDC is primarily useful for low cardinality indices, which are not considered good candidates for a normal clustered index.I agree that there are situations where this option would be useful. Do you know if there's a request ticket on Connect for this feature?Tue, 15 Apr 2008 09:45:57 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxThe description I gave about MDC was very simplified. If you want more details then you have to search the DB2 documentation.Believe me, MDC does what it says on the tin.Tue, 15 Apr 2008 09:21:34 GMTEdVassiehttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxThis sounds like unclustered indexes on a grouped heap. Granted, grouping the heap is an interesting idea, but I wouldn't call it multiple clustered indices.Tue, 15 Apr 2008 09:12:53 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspx[quote]I really don't see any way to have multiple clustered indices without storing multiple copies of the data, as it's not clustered unless it's physically stored in that order.[/quote]In DB2, the data is stored once, and physically clustered in multiple dimensions. All it needs is a bit of lateral thinking. Consider the following...You have a collection of objects you want to cluster by shape, by size, and by colour.Store all the small round red things in one database extent "a". Store all the small square blue things in extent "b". Store all the large triangular blue things in extent "c".Create a new type of index that only knows about extents. Call it a Multiple Dimension Clustering type of index.Define an index for size. This has 3 entries: large, "c"; small, "a"; small, "b".Define an index for shape. This has 3 entries: round,"a"; square,"b"; triangular,"c"Define an index for colour. This has 3 entries: blue, "b"; blue, "c"; red,"a"You want all the blue things, the database gets you extents "b" and "c".You want all the small things, the database gets you extents "a" and "b"You want the blue square things, the database gets you extent "b"In each extent that is returned, ALL the rows match your WHERE clause.So storing a data item once only and physically clustering it in multiple dimensions can be done. You just dedicate a whole extent to store the intersection of all your clustering indexes. In DB2, you can set the extent size for each filegroup (called tablespace in DB2) so you can tune this value to minimise unused space in the extent. The DB2 MDC index pointers are the same size as normal RID pointers, but only the extent level information is populated, allowing normal and MDC indexes to be used in the same query with standard index AND and OR logic. This is a cool feature in DB2 but with weaknesses as well as strengths. It would be nice if SQL Server also had this technology - I am sure IBM would licence another one of its database patents to Microsoft for a suitable fee.Tue, 15 Apr 2008 07:44:30 GMTEdVassiehttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspx[quote][b]EdVassie (4/15/2008)[/b][hr]A clustered index physically stores the data in the sequence given in the index definition. The way this is implemented in SQL Server means you can only have 1 clustered index per table.This is true in most other DBMSs, but not all of them. DB2 has supported multiple clustered indexes for some years (for *nix and Windows) and now also on the mainframe. You can define a large number (256?) cluster indexes on the same table. The data is stored only once, but is physically ordered by the sequence defined for each index.[/quote]It is [i]possible[/i] in SQL Server, you just need to do it by way of indexed views, which results in a second copy of the table. Given the nature of clustered indices, I really don't see any way to have multiple clustered indices without storing multiple copies of the data, as it's not clustered unless it's physically stored in that order.Tue, 15 Apr 2008 07:13:56 GMTsrienstrhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxA clustered index physically stores the data in the sequence given in the index definition. The way this is implemented in SQL Server means you can only have 1 clustered index per table.This is true in most other DBMSs, but not all of them. DB2 has supported multiple clustered indexes for some years (for *nix and Windows) and now also on the mainframe. You can define a large number (256?) cluster indexes on the same table. The data is stored only once, but is physically ordered by the sequence defined for each index.Tue, 15 Apr 2008 05:39:56 GMTEdVassiehttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxWhy would you want to ? The primary clustering is designed to limit the number of index pages that need to change each time a row updates onto a new data page. In this scenario only the primary clustered index would change. Adding a second clustering index would force the system to update both of the 'clustering indexes' to point to the correct data page.If you are not trying to limit the amount of pages touched for Update you could investigate non-clustered indexes on a table without a clustering index (or a heap). As in this case the non-clustered indexes refer to the data pages and not the primary clustering index values. (and in this scenario both would be maintained on each row update onto a new page)Tue, 15 Apr 2008 00:45:34 GMTI cant let you do that Davehttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxsandhyarao49,SQL Server 2005 Books Online (September 2007)CREATE INDEX (Transact-SQL)http://msdn2.microsoft.com/en-us/library/ms188783.aspxClustered IndexThe bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time. For more information, see Clustered Index Structures.For further reading and/or reference ... SQL Server 2005 Books Online (September 2007)Clustered Index Structureshttp://msdn2.microsoft.com/en-us/library/ms177443.aspxSQL Server 2005 Books Online (September 2007)Nonclustered Index Structureshttp://msdn2.microsoft.com/en-us/library/ms177484.aspxHappy T-SQLing,Mon, 14 Apr 2008 07:54:15 GMTKey DBAhttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxYou should read up on exactly what a clustered index is rather than a regular non-clustered index and the difference will make it obvious.Essentially a clustered index defines the physical ordering of the data in the table. Obviously the data in the table can only be ordered once - all other (non-clustered) indices store a subset of the columns in a certain order as well as storing the contents of each row as far as the clustered index is concerned. This allows the non-clustered index to do a "bookmark lookup" if need be to access the table/clustered index to retrieve other column values.Sun, 13 Apr 2008 05:15:32 GMTIan Yateshttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxbecause a clustered index physically stores the records in that order, therefore as you can only store the data in one order you can only have one clustered index.Non clustered indexes store pointers in a seperate file location within the data file.Sun, 13 Apr 2008 05:14:12 GMTAnimal Magichttp://www.sqlservercentral.com/Forums/Topic484150-146-1.aspxwhy can we have only one clustered index per table.Sun, 13 Apr 2008 04:07:32 GMTsandhyarao49