| Author |
Topic |
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
 Posted - 2011-03-28 : 09:24:23
|
| CREATE CLUSTERED INDEX idx_1 ON Transactions (CustomerId, DateCreated)if i do thisdo i have to remove the existing index?> or this overrides?we have determined that this index works 3 times faster |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-28 : 10:02:48
|
| If there's an existing clustered index, you need to remove that first. A table can only have one clustered index. That could take a lot of time if the table is large or has several nonclustered indexes.Just be careful if the current cluster is enforcing the primary key. You'll have to drop all foreign keys, drop the primary key, create the new clustered index, recreate the primary key, recreate all foreign keys.--Gail ShawSQL Server MVP |
 |
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2011-03-28 : 10:33:41
|
| thank you GM, one question, how do i determine if the current cluster is in fact enforcing the primary key? also this table has a non clustered non unique index as well, we can just ignore this one? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-28 : 10:59:38
|
| Query sys.indexes. Check the is_primary_key column for that index.--Gail ShawSQL Server MVP |
|
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2011-03-28 : 11:11:44
|
| where is this sys.indexes located? UNder system tables all i have is the system diagrams which doesn't have this. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-28 : 11:47:23
|
| Why are you looking in object explorer? Open a new query window and type the query. Much faster and you can join tables (and you'll need to here)--Gail ShawSQL Server MVP |
|
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2011-03-28 : 12:17:29
|
| ok i located the master under the system tables. but, this particular table in question is not in the group of tables.Should it be? WHy was it not included? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2011-03-28 : 13:09:49
|
| i ran this :SELECT * FROM sysindexes(i did not see transactions table)0 NULL clst0 NULL clust0 NULL _WA_Sys_00000004_000000050 NULL _WA_Sys_00000003_000000050 NULL _WA_Sys_00000005_000000050 NULL clust0 NULL nc0 NULL _WA_Sys_00000002_000000070 NULL NULL0 NULL cl0 NULL nc0 NULL nc20 NULL cl0 NULL cl0 NULL clst0 NULL _WA_Sys_00000006_000000180 NULL _WA_Sys_00000005_000000180 NULL _WA_Sys_00000001_000000180 NULL _WA_Sys_00000003_000000180 NULL clst0 NULL _WA_Sys_00000002_000000190 NULL clst0 NULL nc10 NULL nc20 NULL _WA_Sys_00000003_0000001B0 NULL clust0 NULL _WA_Sys_00000007_0000001D0 NULL _WA_Sys_00000006_0000001D0 NULL _WA_Sys_00000005_0000001D0 NULL _WA_Sys_00000004_0000001D0 NULL _WA_Sys_00000003_0000001D0 NULL _WA_Sys_00000002_0000001D0 NULL clst0 NULL nc10 NULL nc20 NULL nc30 NULL _WA_Sys_00000008_000000220 NULL _WA_Sys_00000006_000000220 NULL _WA_Sys_00000005_000000220 NULL _WA_Sys_00000003_000000220 NULL _WA_Sys_00000009_000000220 NULL clst0 NULL nc0 NULL _WA_Sys_0000000D_000000290 NULL _WA_Sys_0000000B_000000290 NULL _WA_Sys_00000006_000000290 NULL _WA_Sys_00000005_000000290 NULL _WA_Sys_00000002_000000290 NULL _WA_Sys_00000003_000000290 NULL _WA_Sys_0000000F_000000290 NULL _WA_Sys_0000000E_000000290 NULL clst0 NULL nc0 NULL _WA_Sys_00000004_0000002C0 NULL _WA_Sys_00000002_0000002C0 NULL cl0 NULL nc10 NULL nc20 NULL nc30 NULL clust0 NULL _WA_Sys_00000004_000000310 NULL _WA_Sys_00000002_000000310 NULL _WA_Sys_00000003_000000310 NULL clst0 NULL nc10 NULL nc20 NULL _WA_Sys_00000004_000000320 NULL _WA_Sys_0000000D_000000320 NULL _WA_Sys_0000000C_000000320 NULL clst0 NULL nc0 NULL _WA_Sys_00000005_000000330 NULL _WA_Sys_00000002_000000330 NULL _WA_Sys_00000006_000000330 NULL clst0 NULL nc0 NULL _WA_Sys_00000002_000000360 NULL _WA_Sys_00000004_000000360 NULL _WA_Sys_00000009_000000360 NULL _WA_Sys_00000005_000000360 NULL _WA_Sys_0000000A_000000360 NULL _WA_Sys_00000007_000000360 NULL _WA_Sys_0000000B_000000360 NULL clst0 NULL nc10 NULL _WA_Sys_00000005_000000370 NULL _WA_Sys_00000004_000000370 NULL _WA_Sys_00000002_000000370 NULL _WA_Sys_00000007_000000370 NULL _WA_Sys_00000006_000000370 NULL clst0 NULL nc10 NULL _WA_Sys_00000004_0000003A0 NULL _WA_Sys_00000003_0000003A0 NULL _WA_Sys_00000002_0000003A0 NULL clust0 NULL clst0 NULL _WA_Sys_00000005_0000003C0 NULL _WA_Sys_00000004_0000003C0 NULL _WA_Sys_00000003_0000003C0 NULL _WA_Sys_00000002_0000003C0 NULL clst0 NULL nc0 NULL _WA_Sys_00000005_000000400 NULL _WA_Sys_00000002_000000400 NULL _WA_Sys_00000004_000000400 NULL clust0 NULL clst0 NULL nc10 NULL nc20 NULL clst0 NULL clst0 NULL nc10 NULL nc20 NULL clst0 NULL cl0 NULL cl0 NULL clst0 NULL nc10 NULL _WA_Sys_00000003_0000004A0 NULL _WA_Sys_00000001_0000004A0 NULL _WA_Sys_00000005_0000004A0 NULL clst0 NULL nc10 NULL _WA_Sys_00000005_0000004B0 NULL _WA_Sys_00000003_0000004B0 NULL _WA_Sys_00000001_0000004B0 NULL _WA_Sys_00000006_0000004B0 NULL cl0 NULL nc0 NULL clst0 NULL clst0 NULL clst0 NULL nc10 NULL cl0 NULL nc10 NULL cl0 NULL cl0 NULL nc10 NULL cl0 NULL _WA_Sys_00000005_0000005E0 NULL _WA_Sys_00000004_0000005E0 NULL _WA_Sys_00000003_0000005E0 NULL _WA_Sys_00000002_0000005E0 NULL cl0 NULL nc10 NULL nc30 NULL clst0 NULL nc10 NULL nc20 NULL _WA_Sys_00000004_000000600 NULL clst0 NULL nc10 NULL _WA_Sys_00000003_000000610 NULL _WA_Sys_00000002_000000610 NULL clst0 NULL nc10 NULL _WA_Sys_00000009_000000620 NULL _WA_Sys_00000007_000000620 NULL _WA_Sys_00000006_000000620 NULL _WA_Sys_00000005_000000620 NULL _WA_Sys_00000003_000000620 NULL _WA_Sys_00000001_000000620 NULL PK_Departments0 NULL UK_Departments_Customers0 NULL _WA_Sys_00000002_023D5A040 NULL PK_Categories0 NULL _WA_Sys_00000003_060DEAE80 NULL _WA_Sys_00000002_060DEAE80 NULL _WA_Sys_00000004_060DEAE80 NULL PK_Items0 NULL _WA_Sys_00000002_08EA57930 NULL PK_ItemMappings0 NULL _WA_Sys_00000002_0AD2A0050 NULL _WA_Sys_00000003_0AD2A0050 NULL UK_ItemMappings_DataSource0 NULL _WA_Sys_00000005_0AD2A0050 NULL PK_Imports0 NULL _WA_Sys_00000002_0DAF0CB00 NULL _WA_Sys_00000006_0DAF0CB00 NULL _WA_Sys_00000005_0DAF0CB00 NULL _WA_Sys_00000004_0DAF0CB00 NULL _WA_Sys_00000003_0DAF0CB00 NULL PK_Transactions0 NULL _WA_Sys_0000000A_0F9755220 NULL _WA_Sys_00000002_0F9755220 NULL _WA_Sys_00000009_0F9755220 NULL _WA_Sys_00000004_0F9755220 NULL _WA_Sys_0000000C_0F9755220 NULL _WA_Sys_00000008_0F9755220 NULL _WA_Sys_00000005_0F9755220 NULL _WA_Sys_00000003_0F9755220 NULL _WA_Sys_00000006_0F9755220 NULL _WA_Sys_00000007_0F9755220 NULL _WA_Sys_0000000E_0F9755220 NULL IX_Transactions0 NULL _WA_Sys_00000001_0F9755220 NULL PK_TransactionRx0 NULL _WA_Sys_0000000C_111589400 NULL _WA_Sys_00000005_111589400 NULL _WA_Sys_0000000B_111589400 NULL _WA_Sys_0000000D_111589400 NULL _WA_Sys_00000004_111589400 NULL PK_UserCustomerTypes0 NULL _WA_Sys_00000003_15DB09B40 NULL _WA_Sys_00000002_15DB09B40 NULL NULL0 NULL NULL0 NULL _WA_Sys_00000006_182C9B230 NULL _WA_Sys_00000005_182C9B230 NULL _WA_Sys_00000003_182C9B230 NULL _WA_Sys_00000001_182C9B230 NULL _WA_Sys_00000004_182C9B230 NULL _WA_Sys_00000002_182C9B230 NULL _WA_Sys_00000007_182C9B230 NULL PK_Reports0 NULL NULL0 NULL _WA_Sys_00000002_1FCDBCEB0 NULL _WA_Sys_00000001_1FCDBCEB0 NULL PK_Users0 NULL _WA_Sys_00000002_2062B9C80 NULL _WA_Sys_00000003_2062B9C80 NULL _WA_Sys_00000004_2062B9C80 NULL _WA_Sys_00000006_2062B9C80 NULL _WA_Sys_00000005_2062B9C80 NULL PK__Budget_S__D825195E23893F360 NULL _WA_Sys_00000002_21A0F6C40 NULL NULL0 NULL _WA_Sys_00000003_24334AAC0 NULL _WA_Sys_00000002_24334AAC0 NULL _WA_Sys_00000001_24334AAC0 NULL NULL0 NULL _WA_Sys_00000003_25276EE50 NULL _WA_Sys_00000001_25276EE50 NULL _WA_Sys_00000002_25276EE50 NULL NULL0 NULL _WA_Sys_00000002_261B931E0 NULL _WA_Sys_00000001_261B931E0 NULL NULL0 NULL _WA_Sys_00000003_270FB7570 NULL _WA_Sys_00000001_270FB7570 NULL _WA_Sys_00000002_270FB7570 NULL NULL0 NULL _WA_Sys_00000002_34E8D5620 NULL _WA_Sys_00000001_34E8D5620 NULL NULL0 NULL _WA_Sys_00000001_35DCF99B0 NULL _WA_Sys_00000002_35DCF99B0 NULL NULL0 NULL NULL0 NULL _WA_Sys_00000002_39E294A90 NULL _WA_Sys_00000001_39E294A90 NULL PK_DepartmentMappings0 NULL _WA_Sys_00000003_3B36AB950 NULL _WA_Sys_00000004_3B36AB950 NULL _WA_Sys_00000002_3B36AB950 NULL PK_CustomerDatabases_10 NULL _WA_Sys_00000002_3CFEF8760 NULL PK_App_Log0 NULL PK_Residents0 NULL UK_Residents_DataSourceResidentId0 NULL _WA_Sys_00000005_46FD63FC0 NULL _WA_Sys_00000004_46FD63FC0 NULL _WA_Sys_00000009_46FD63FC0 NULL _WA_Sys_00000002_46FD63FC0 NULL _WA_Sys_0000000A_46FD63FC0 NULL _WA_Sys_00000008_46FD63FC0 NULL _WA_Sys_00000007_46FD63FC0 NULL _WA_Sys_00000006_46FD63FC0 NULL _WA_Sys_00000003_46FD63FC0 NULL NULL0 NULL _WA_Sys_00000003_4C0144E40 NULL _WA_Sys_00000002_4C0144E40 NULL _WA_Sys_00000001_4C0144E40 NULL NULL0 NULL NULL0 NULL _WA_Sys_00000003_4DE98D560 NULL _WA_Sys_00000001_4DE98D560 NULL PK_CustomerDocumentTypes0 NULL _WA_Sys_00000003_4F48913A0 NULL _WA_Sys_00000002_4F48913A0 NULL NULL0 NULL _WA_Sys_00000003_4FD1D5C80 NULL NULL0 NULL NULL0 NULL _WA_Sys_00000001_5130D9AC0 NULL _WA_Sys_00000002_5130D9AC0 NULL PK__DataSour__E70DA669602839220 NULL _WA_Sys_00000003_5E3FF0B00 NULL PK_Summary_Staging0 NULL _WA_Sys_00000002_6166761E0 NULL PK__Budget_H__3214EC27653707020 NULL _WA_Sys_00000004_634EBE900 NULL _WA_Sys_00000003_634EBE900 NULL PK__Detail_S__A8D751960EA330E90 NULL _WA_Sys_00000002_671F4F740 NULL _WA_Sys_00000005_671F4F740 NULL PK__Saved_Bu__3214EC2722AA29960 NULL _WA_Sys_00000005_69FBBC1F0 NULL _WA_Sys_00000004_69FBBC1F0 NULL _WA_Sys_00000003_69FBBC1F0 NULL _WA_Sys_00000002_69FBBC1F0 NULL queue_clustered_index0 NULL queue_secondary_index0 NULL queue_clustered_index0 NULL queue_secondary_index0 NULL NULL0 NULL _WA_Sys_00000003_7988E3C90 NULL _WA_Sys_00000002_7988E3C90 NULL _WA_Sys_00000001_7988E3C90 NULL NULL0 NULL _WA_Sys_00000002_7A7D08020 NULL _WA_Sys_00000001_7A7D08020 NULL queue_clustered_index0 NULL queue_secondary_index0 NULL FSTSClusIdx0 NULL FSTSNCIdx0 NULL ci_commit_ts0 NULL si_xdes_id0 NULL PK_Customers0 NULL _WA_Sys_00000002_7D78A4E70 NULL _WA_Sys_00000003_7D78A4E70 NULL _WA_Sys_00000004_7D78A4E70 NULL PK_CustomerMappings0 NULL _WA_Sys_00000002_7F60ED590 NULL _WA_Sys_00000003_7F60ED59 |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-28 : 13:24:33
|
| May I suggest some time with Books Online. May help you understand what you're seeing. I don't mind helping, but...Oh, and I said sys.indexes, not sysindexes.--Gail ShawSQL Server MVP |
|
|
|
Jahanzaib
Posting Yak Master
115 Posts |
Posted - 2011-04-15 : 15:45:50
|
| Each table can have 1 clustered index only and 999 non-clustered in SQL Server 2008If you have already non-clustered index on a table and then you create a clustered index then all non-clustered index will be rebuildRegards,Syed Jahanzaib Bin HassanMCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA |
|
|
|
|
making a new index