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.
| Author |
Topic |
|
tech_1
Posting Yak Master
129 Posts |
 Posted - 2011-11-01 : 10:03:35
|
I have a table which has a PK on a GUID field.What I want to do is to drop the clustered index that it applies by default.what is the syntax of this? my current SQL Script:quote:
CREATE TABLE [dbo].[DevConfig]( [Id] [uniqueidentifier] NOT NULL, [LookupType] [varchar](20) NOT NULL, [LookupKey] [varchar](20) NOT NULL, [LookupVal] [varchar](50) NOT NULLCONSTRAINT [PK_DevConfig] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-11-01 : 10:13:04
|
[code]alter table DevConfig drop constraint PK_DevConfig[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-11-01 : 10:34:52
|
| Do note that all foreign keys that reference this table must be dropped first.Where are you going to move the cluster to? A uniqueidentifier is generally a very bad clustered index, but you should have one on all tables.--Gail ShawSQL Server MVP |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-11-01 : 10:46:01
|
| That will drop the PK and the index that supports it.You will probably need to add the PK back but as non-clusteredalter table DevConfig CONSTRAINT [PK_DevConfig] PRIMARY KEY nonclustered (id)also the clustered index ix included in all other indexes - after this those indees will not include id by default and you might find they are no longer covering or queries might be slower.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Drop clustered index