March 30, 2009 at 9:32 am
I currently have 2 indexes on a table that are identical, one is a Unique Clustered Index, and the other is an Index for the primary key nonclustered.
/****** Object: Index [SALES] Script Date: 03/30/2009 11:17:18 ******/
CREATE UNIQUE CLUSTERED INDEX [SALES] ON [dbo].[SALES]
(
[PERIOD_ID] ASC,
[DISTRIBUTOR_ID] ASC,
[CLINIC_ID] ASC,
[PRODUCT_ID] 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
, FILLFACTOR = 90
) ON [PRIMARY]
GO
/****** Object: Index [PK_SALES2] Script Date: 03/30/2009 11:17:06 ******/
ALTER TABLE [dbo].[SALES] ADD CONSTRAINT [PK_SALES2] PRIMARY KEY NONCLUSTERED
(
[PERIOD_ID] ASC,
[DISTRIBUTOR_ID] ASC,
[PRODUCT_ID] ASC,
[CLINIC_ID] ASC
)WITH(
PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF
, ONLINE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON
, FILLFACTOR = 90
) ON [PRIMARY]
I would really like 1 unique primary key clustered index on this table but I can't get the syntax of it correct. Server is SQL Standard, and I can drop the current ones to create a new one once as the table is rather large and index creation will take a while. Any help that could be provided would be great.
March 30, 2009 at 12:23 pm
I'd use the method of creating a new copy of the table with all the right structures, migrate the data over, then drop the other table and it's FK's, rename the new table to the old table name and then recreate the FK's on the new table.
I assume you're going to make the PK clustered in this case.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply