| Author |
Topic |
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
 Posted - 2010-01-27 : 08:16:48
|
| I have few questions as follows:1. How can I find if a table is heap or cluster organized in SQL Server 2005?2. What is the organization of a table if a create a table without any primary key?3. What will happen to the organization of the table if a table without primary key is heap organized and later I add a primary key with clustered option?PS:I am basically an Oracle DBA but now I am learning SQL Server to meet business requirements. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-27 : 08:30:49
|
| 1) check if the table has a clustered index. if it has a clustered index then it is not a heap. Script the table out is probably the easiest way or look under INDEXES in the management studio object explorer view for the tabke2) By default primary keys are implemented with a clustered index. It is possible to create a table without a primary key but with an explicit clustered index. It is also possible to create a primary key which doesn't use a clustered index. Therefore:If you create a table without a primary key, it will be a heap UNLESS you create a CLUSTERED INDEX on one or more columns of the table3) The data in the table will be physically reordered into a B-TREE that follows the CLUSTERED INDEX. This can take a long time when you have a lot of data.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-27 : 08:40:21
|
This will show all tables and whether they are clustered or notSELECT object_name(i.object_id) [table] , i.type_descFROM sys.indexes iJOIN sys.tables tOn t.object_id = i.object_idWHERE i.index_id < 2ORDER BY [table]; |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 08:41:10
|
Good answer T.C.  |
|
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2010-01-27 : 10:49:30
|
| That was so simple and well framed. Thanks!"It is possible to create a table without a primary key but with an explicit clustered index."But that will require rebuild of all present clustered indexes. |
|
|
|
mahajanakhil1985
Yak Posting Veteran
71 Posts |
Posted - 2010-01-27 : 10:50:24
|
| Oops sorry. It will require rebuild of all non-clustered indexes. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-27 : 11:29:46
|
| when adding a clustered index to a heap that has nonclustered indexes, i'll drop all non-clustered 1st, then create clustered, finally recreate nonclustered |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 11:43:13
|
| When a Clustered Index is added to a table that has data, and did not already have a clustered index, then all non-clustered indexes will be rebuilt.best to create the clustered index before adding any data (and if inserting data in bulk best to pre-order it by the clustered index, and for MS SQL Bulk Import tools also provide the Hint that the source data is pre-sorted) |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-27 : 11:47:27
|
| Good call on the non-clustered indices that might exist for question (3). I didn't consider them in my answer.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-01-27 : 11:52:01
|
quote:
Originally posted by Kristen
Good answer T.C.
Cheers.I do know what I'm talking about..... sometimes! Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 12:00:18
|
| Just wanted to let you know I was checking ... |
|
|
|
|
heap or clustered?