Creating Cluster index on adding column

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

SQL Server 2005 Forums

Transact-SQL (2005)

Creating Cluster index on adding column

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2010-01-21 : 04:03:27

Hi, i have table with 3 columns. I want to add new column along with clustered index to that table. Please give me the syntax for the same. I tried up to some level as below but getting errors.

Alter Mytable Add Mycolumn varchar(50) Clustered Index

Please give me the correct command to meet the above criteria

developer :)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-21 : 04:12:34

Try this,

ALTER TABLE Table_name ADD my_column varchar(50) NULL
GO
CREATE NONCLUSTERED INDEX IX_Test ON Table_name(my_column)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2010-01-21 : 04:21:16

I want to create a clustered index but not a non-clustered one.
I have no indexes on table and no primary keys on table. I just have 3 columns of varchar datatype. Now i am adding new column with varchar datatype. On this new column i want to add clustered index while adding the column to table.

quote:
Originally posted by senthil_nagore

Try this,

ALTER TABLE Table_name ADD my_column varchar(50) NULL
GO
CREATE NONCLUSTERED INDEX IX_Test ON Table_name(my_column)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

developer :)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-21 : 04:30:42

Primary key will be clustered index

Set the columns as Primary key.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-21 : 04:32:09

Here the Script

ALTER TABLE table_name ADD mycol varchar(50) NOT NULL
GO
ALTER TABLE table_name ADD CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (mycol)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2010-01-21 : 04:34:26

I dont want to create primary key on a column. With out creating PK, i want clustered index while adding column.

quote:
Originally posted by senthil_nagore

Here the Script

ALTER TABLE table_name ADD mycol varchar(50) NOT NULL
GO
ALTER TABLE table_name ADD CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (mycol)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

developer :)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-21 : 04:38:23

Not Possible, Primary key acts as a clustered index;

Go through about Clustered index in BOL

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2010-01-21 : 04:46:32

We can create a clustered index on a column which doesn't have a primary key(If there is no other clustered index in the table). I am sure about this. Now i am in same situation. I can create a clustered index on column through Sqlserver management studio once the column is added to table. But the thing i want is when creating a column their itslef i want to create a clustered index on it.

quote:
Originally posted by senthil_nagore

Not Possible, Primary key acts as a clustered index;

Go through about Clustered index in BOL

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

developer :)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-21 : 04:48:04

Then Create a script on Management Studio!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-21 : 04:51:00

Try this

ALTER TABLE table_name ADD mycol varchar(50) NULL
GO
CREATE CLUSTERED INDEX IX_Test ON table_name (mycol)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2010-01-21 : 05:08:34

Thank you :)

quote:
Originally posted by senthil_nagore

Try this

ALTER TABLE table_name ADD mycol varchar(50) NULL
GO
CREATE CLUSTERED INDEX IX_Test ON table_name (mycol)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

developer :)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-21 : 05:10:40

quote:
Originally posted by satish.gorijala

Thank you :)

quote:
Originally posted by senthil_nagore

Try this

ALTER TABLE table_name ADD mycol varchar(50) NULL
GO
CREATE CLUSTERED INDEX IX_Test ON table_name (mycol)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

developer :)

Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 08:27:13

quote:
Originally posted by senthil_nagore

Not Possible, Primary key acts as a clustered index;

Go through about Clustered index in BOL

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

This is NOT true.

It is possible to make the primary key without a clustered index but you have to specify this when you create the key.

This is rarely important as generally, if your tables are designed well then you want your primary key to be the clustered index (as it is by default) but if your natural key isn't a good candidate for the clustered index (maybe you have to use a surrogate primary key) then you can declare the primary key NON CLUSTERED

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources