Add A Column And Create A Non Clustered Index on a Table

Question

Add A Column And Create A Non Clustered Index on a Table

Stylez

SSCrazy

Points: 2195
More actions
February 13, 2017 at 11:48 am

#335281

Good Morning Guys,
I would like to ask if it is possible to Add A New Column on an Existing Table and Make it a unique non Clustered index at the same time. i am currently using this code to achieve this
IF NOT EXISTS(
SELECT *
FROM sys.columns
WHERE Name  = N'EDB_INCOME_TYPE_ID'
  AND Object_ID = Object_ID(N'DIM_INCOME_TYPE_TEST'))
BEGIN
    ALTER TABLE DIM_INCOME_TYPE_TEST
    ADD EDB_INCOME_TYPE_ID INTEGER NOT NULL CONSTRAINT DIM_INCOME_TYPE_NI_K06 DEFAULT 0
END
but this does not make it a Unique non Clusetered Index. any help from you Guys/Gals is very much appreciated
Best Regards,
Noel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Are you sure you want a default value of 0, while having a UNIQUE index on that field? One insert of a default value and you don't get to repeat that action ever again. EDIT: at least, not until you delete that inserted value or update it to something else...

And you are correct that your code isn't going to create a unique non-clustered index. Why not look up the syntax in Books OnLine (aka BOL)?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Unless the table is empty, adding a column and creating a unique index at the same time will fail, as the newly added column is not unique.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sgmunson SSC Guru Points: 110640 More actions · Answer 3

GilaMonster - Tuesday, February 14, 2017 2:31 AM
Unless the table is empty, adding a column and creating a unique index at the same time will fail, as the newly added column is not unique.

Good catch... I hadn't quite thought that far down the road.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Stylez SSCrazy Points: 2195 More actions · Answer 4

Many Thanks Guys. Thanks for the help! i was able to resolve it by not making it a Unique Constraint

Best Regards,

Noel