Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 making a new index

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 this
do 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 Shaw
SQL Server MVP
Go to Top of Page

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?
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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.
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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?
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-28 : 12:25:46
Why are you still looking in the object explorer for it? Run a query against it in a new query window as Gail said.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

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 clst
0 NULL clust
0 NULL _WA_Sys_00000004_00000005
0 NULL _WA_Sys_00000003_00000005
0 NULL _WA_Sys_00000005_00000005
0 NULL clust
0 NULL nc
0 NULL _WA_Sys_00000002_00000007
0 NULL NULL
0 NULL cl
0 NULL nc
0 NULL nc2
0 NULL cl
0 NULL cl
0 NULL clst
0 NULL _WA_Sys_00000006_00000018
0 NULL _WA_Sys_00000005_00000018
0 NULL _WA_Sys_00000001_00000018
0 NULL _WA_Sys_00000003_00000018
0 NULL clst
0 NULL _WA_Sys_00000002_00000019
0 NULL clst
0 NULL nc1
0 NULL nc2
0 NULL _WA_Sys_00000003_0000001B
0 NULL clust
0 NULL _WA_Sys_00000007_0000001D
0 NULL _WA_Sys_00000006_0000001D
0 NULL _WA_Sys_00000005_0000001D
0 NULL _WA_Sys_00000004_0000001D
0 NULL _WA_Sys_00000003_0000001D
0 NULL _WA_Sys_00000002_0000001D
0 NULL clst
0 NULL nc1
0 NULL nc2
0 NULL nc3
0 NULL _WA_Sys_00000008_00000022
0 NULL _WA_Sys_00000006_00000022
0 NULL _WA_Sys_00000005_00000022
0 NULL _WA_Sys_00000003_00000022
0 NULL _WA_Sys_00000009_00000022
0 NULL clst
0 NULL nc
0 NULL _WA_Sys_0000000D_00000029
0 NULL _WA_Sys_0000000B_00000029
0 NULL _WA_Sys_00000006_00000029
0 NULL _WA_Sys_00000005_00000029
0 NULL _WA_Sys_00000002_00000029
0 NULL _WA_Sys_00000003_00000029
0 NULL _WA_Sys_0000000F_00000029
0 NULL _WA_Sys_0000000E_00000029
0 NULL clst
0 NULL nc
0 NULL _WA_Sys_00000004_0000002C
0 NULL _WA_Sys_00000002_0000002C
0 NULL cl
0 NULL nc1
0 NULL nc2
0 NULL nc3
0 NULL clust
0 NULL _WA_Sys_00000004_00000031
0 NULL _WA_Sys_00000002_00000031
0 NULL _WA_Sys_00000003_00000031
0 NULL clst
0 NULL nc1
0 NULL nc2
0 NULL _WA_Sys_00000004_00000032
0 NULL _WA_Sys_0000000D_00000032
0 NULL _WA_Sys_0000000C_00000032
0 NULL clst
0 NULL nc
0 NULL _WA_Sys_00000005_00000033
0 NULL _WA_Sys_00000002_00000033
0 NULL _WA_Sys_00000006_00000033
0 NULL clst
0 NULL nc
0 NULL _WA_Sys_00000002_00000036
0 NULL _WA_Sys_00000004_00000036
0 NULL _WA_Sys_00000009_00000036
0 NULL _WA_Sys_00000005_00000036
0 NULL _WA_Sys_0000000A_00000036
0 NULL _WA_Sys_00000007_00000036
0 NULL _WA_Sys_0000000B_00000036
0 NULL clst
0 NULL nc1
0 NULL _WA_Sys_00000005_00000037
0 NULL _WA_Sys_00000004_00000037
0 NULL _WA_Sys_00000002_00000037
0 NULL _WA_Sys_00000007_00000037
0 NULL _WA_Sys_00000006_00000037
0 NULL clst
0 NULL nc1
0 NULL _WA_Sys_00000004_0000003A
0 NULL _WA_Sys_00000003_0000003A
0 NULL _WA_Sys_00000002_0000003A
0 NULL clust
0 NULL clst
0 NULL _WA_Sys_00000005_0000003C
0 NULL _WA_Sys_00000004_0000003C
0 NULL _WA_Sys_00000003_0000003C
0 NULL _WA_Sys_00000002_0000003C
0 NULL clst
0 NULL nc
0 NULL _WA_Sys_00000005_00000040
0 NULL _WA_Sys_00000002_00000040
0 NULL _WA_Sys_00000004_00000040
0 NULL clust
0 NULL clst
0 NULL nc1
0 NULL nc2
0 NULL clst
0 NULL clst
0 NULL nc1
0 NULL nc2
0 NULL clst
0 NULL cl
0 NULL cl
0 NULL clst
0 NULL nc1
0 NULL _WA_Sys_00000003_0000004A
0 NULL _WA_Sys_00000001_0000004A
0 NULL _WA_Sys_00000005_0000004A
0 NULL clst
0 NULL nc1
0 NULL _WA_Sys_00000005_0000004B
0 NULL _WA_Sys_00000003_0000004B
0 NULL _WA_Sys_00000001_0000004B
0 NULL _WA_Sys_00000006_0000004B
0 NULL cl
0 NULL nc
0 NULL clst
0 NULL clst
0 NULL clst
0 NULL nc1
0 NULL cl
0 NULL nc1
0 NULL cl
0 NULL cl
0 NULL nc1
0 NULL cl
0 NULL _WA_Sys_00000005_0000005E
0 NULL _WA_Sys_00000004_0000005E
0 NULL _WA_Sys_00000003_0000005E
0 NULL _WA_Sys_00000002_0000005E
0 NULL cl
0 NULL nc1
0 NULL nc3
0 NULL clst
0 NULL nc1
0 NULL nc2
0 NULL _WA_Sys_00000004_00000060
0 NULL clst
0 NULL nc1
0 NULL _WA_Sys_00000003_00000061
0 NULL _WA_Sys_00000002_00000061
0 NULL clst
0 NULL nc1
0 NULL _WA_Sys_00000009_00000062
0 NULL _WA_Sys_00000007_00000062
0 NULL _WA_Sys_00000006_00000062
0 NULL _WA_Sys_00000005_00000062
0 NULL _WA_Sys_00000003_00000062
0 NULL _WA_Sys_00000001_00000062
0 NULL PK_Departments
0 NULL UK_Departments_Customers
0 NULL _WA_Sys_00000002_023D5A04
0 NULL PK_Categories
0 NULL _WA_Sys_00000003_060DEAE8
0 NULL _WA_Sys_00000002_060DEAE8
0 NULL _WA_Sys_00000004_060DEAE8
0 NULL PK_Items
0 NULL _WA_Sys_00000002_08EA5793
0 NULL PK_ItemMappings
0 NULL _WA_Sys_00000002_0AD2A005
0 NULL _WA_Sys_00000003_0AD2A005
0 NULL UK_ItemMappings_DataSource
0 NULL _WA_Sys_00000005_0AD2A005
0 NULL PK_Imports
0 NULL _WA_Sys_00000002_0DAF0CB0
0 NULL _WA_Sys_00000006_0DAF0CB0
0 NULL _WA_Sys_00000005_0DAF0CB0
0 NULL _WA_Sys_00000004_0DAF0CB0
0 NULL _WA_Sys_00000003_0DAF0CB0
0 NULL PK_Transactions
0 NULL _WA_Sys_0000000A_0F975522
0 NULL _WA_Sys_00000002_0F975522
0 NULL _WA_Sys_00000009_0F975522
0 NULL _WA_Sys_00000004_0F975522
0 NULL _WA_Sys_0000000C_0F975522
0 NULL _WA_Sys_00000008_0F975522
0 NULL _WA_Sys_00000005_0F975522
0 NULL _WA_Sys_00000003_0F975522
0 NULL _WA_Sys_00000006_0F975522
0 NULL _WA_Sys_00000007_0F975522
0 NULL _WA_Sys_0000000E_0F975522
0 NULL IX_Transactions
0 NULL _WA_Sys_00000001_0F975522
0 NULL PK_TransactionRx
0 NULL _WA_Sys_0000000C_11158940
0 NULL _WA_Sys_00000005_11158940
0 NULL _WA_Sys_0000000B_11158940
0 NULL _WA_Sys_0000000D_11158940
0 NULL _WA_Sys_00000004_11158940
0 NULL PK_UserCustomerTypes
0 NULL _WA_Sys_00000003_15DB09B4
0 NULL _WA_Sys_00000002_15DB09B4
0 NULL NULL
0 NULL NULL
0 NULL _WA_Sys_00000006_182C9B23
0 NULL _WA_Sys_00000005_182C9B23
0 NULL _WA_Sys_00000003_182C9B23
0 NULL _WA_Sys_00000001_182C9B23
0 NULL _WA_Sys_00000004_182C9B23
0 NULL _WA_Sys_00000002_182C9B23
0 NULL _WA_Sys_00000007_182C9B23
0 NULL PK_Reports
0 NULL NULL
0 NULL _WA_Sys_00000002_1FCDBCEB
0 NULL _WA_Sys_00000001_1FCDBCEB
0 NULL PK_Users
0 NULL _WA_Sys_00000002_2062B9C8
0 NULL _WA_Sys_00000003_2062B9C8
0 NULL _WA_Sys_00000004_2062B9C8
0 NULL _WA_Sys_00000006_2062B9C8
0 NULL _WA_Sys_00000005_2062B9C8
0 NULL PK__Budget_S__D825195E23893F36
0 NULL _WA_Sys_00000002_21A0F6C4
0 NULL NULL
0 NULL _WA_Sys_00000003_24334AAC
0 NULL _WA_Sys_00000002_24334AAC
0 NULL _WA_Sys_00000001_24334AAC
0 NULL NULL
0 NULL _WA_Sys_00000003_25276EE5
0 NULL _WA_Sys_00000001_25276EE5
0 NULL _WA_Sys_00000002_25276EE5
0 NULL NULL
0 NULL _WA_Sys_00000002_261B931E
0 NULL _WA_Sys_00000001_261B931E
0 NULL NULL
0 NULL _WA_Sys_00000003_270FB757
0 NULL _WA_Sys_00000001_270FB757
0 NULL _WA_Sys_00000002_270FB757
0 NULL NULL
0 NULL _WA_Sys_00000002_34E8D562
0 NULL _WA_Sys_00000001_34E8D562
0 NULL NULL
0 NULL _WA_Sys_00000001_35DCF99B
0 NULL _WA_Sys_00000002_35DCF99B
0 NULL NULL
0 NULL NULL
0 NULL _WA_Sys_00000002_39E294A9
0 NULL _WA_Sys_00000001_39E294A9
0 NULL PK_DepartmentMappings
0 NULL _WA_Sys_00000003_3B36AB95
0 NULL _WA_Sys_00000004_3B36AB95
0 NULL _WA_Sys_00000002_3B36AB95
0 NULL PK_CustomerDatabases_1
0 NULL _WA_Sys_00000002_3CFEF876
0 NULL PK_App_Log
0 NULL PK_Residents
0 NULL UK_Residents_DataSourceResidentId
0 NULL _WA_Sys_00000005_46FD63FC
0 NULL _WA_Sys_00000004_46FD63FC
0 NULL _WA_Sys_00000009_46FD63FC
0 NULL _WA_Sys_00000002_46FD63FC
0 NULL _WA_Sys_0000000A_46FD63FC
0 NULL _WA_Sys_00000008_46FD63FC
0 NULL _WA_Sys_00000007_46FD63FC
0 NULL _WA_Sys_00000006_46FD63FC
0 NULL _WA_Sys_00000003_46FD63FC
0 NULL NULL
0 NULL _WA_Sys_00000003_4C0144E4
0 NULL _WA_Sys_00000002_4C0144E4
0 NULL _WA_Sys_00000001_4C0144E4
0 NULL NULL
0 NULL NULL
0 NULL _WA_Sys_00000003_4DE98D56
0 NULL _WA_Sys_00000001_4DE98D56
0 NULL PK_CustomerDocumentTypes
0 NULL _WA_Sys_00000003_4F48913A
0 NULL _WA_Sys_00000002_4F48913A
0 NULL NULL
0 NULL _WA_Sys_00000003_4FD1D5C8
0 NULL NULL
0 NULL NULL
0 NULL _WA_Sys_00000001_5130D9AC
0 NULL _WA_Sys_00000002_5130D9AC
0 NULL PK__DataSour__E70DA66960283922
0 NULL _WA_Sys_00000003_5E3FF0B0
0 NULL PK_Summary_Staging
0 NULL _WA_Sys_00000002_6166761E
0 NULL PK__Budget_H__3214EC2765370702
0 NULL _WA_Sys_00000004_634EBE90
0 NULL _WA_Sys_00000003_634EBE90
0 NULL PK__Detail_S__A8D751960EA330E9
0 NULL _WA_Sys_00000002_671F4F74
0 NULL _WA_Sys_00000005_671F4F74
0 NULL PK__Saved_Bu__3214EC2722AA2996
0 NULL _WA_Sys_00000005_69FBBC1F
0 NULL _WA_Sys_00000004_69FBBC1F
0 NULL _WA_Sys_00000003_69FBBC1F
0 NULL _WA_Sys_00000002_69FBBC1F
0 NULL queue_clustered_index
0 NULL queue_secondary_index
0 NULL queue_clustered_index
0 NULL queue_secondary_index
0 NULL NULL
0 NULL _WA_Sys_00000003_7988E3C9
0 NULL _WA_Sys_00000002_7988E3C9
0 NULL _WA_Sys_00000001_7988E3C9
0 NULL NULL
0 NULL _WA_Sys_00000002_7A7D0802
0 NULL _WA_Sys_00000001_7A7D0802
0 NULL queue_clustered_index
0 NULL queue_secondary_index
0 NULL FSTSClusIdx
0 NULL FSTSNCIdx
0 NULL ci_commit_ts
0 NULL si_xdes_id
0 NULL PK_Customers
0 NULL _WA_Sys_00000002_7D78A4E7
0 NULL _WA_Sys_00000003_7D78A4E7
0 NULL _WA_Sys_00000004_7D78A4E7
0 NULL PK_CustomerMappings
0 NULL _WA_Sys_00000002_7F60ED59
0 NULL _WA_Sys_00000003_7F60ED59
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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 2008

If you have already non-clustered index on a table and then you create a clustered index then all non-clustered index will be rebuild

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA
Go to Top of Page
   

- Advertisement -