July 30, 2009 at 9:15 am
We've begun to have a discussion on a few of our tables that are either missing a primary key or have one on a identity key that just auto-increment.
I've done some reading and I've kind of come to the conclusion that we would benefit from having an Surrogate IDENTITY Column that's the Primary Key on every table. Feel free to chime in and offer opinions but please be more kind than Bob was in this great thread.
Anyway, I want to do this outside the GUI as much as possible.
I'm able to run the following script and accomplish my goal:
--altered to protect the guilty
USE [db]
ALTER TABLE dbo.RDCost
ADD RDCID INT IDENTITY
CONSTRAINT PK_RDC_RDCID PRIMARY KEY CLUSTERED (RDCID)
The only problem is that it puts it at the end of the table. In SSMS I can't drag it to the top in design view but I was able to do so in Enterprise Manager.
What's the best way to handle putting it as the first column through script?
What kind of lovely opinions can I get this fine morning?
EDIT: I'm aware of the idea that it has to drop and recreate the table so this could be difficult.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
July 30, 2009 at 9:22 am
Bad news. The only way I know to change physical column order is to drop & recreate the table.
"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
July 30, 2009 at 9:31 am
Other than asthetics, does it really matter where in the column list the surrogate column resides when looking at the list of columns for a table? Does SQL Server really care where the column is located in the list of columns when storing data in the database?
At this point, I would not worry about wether the column(s) you are adding are at the begin or end of the column list. I don't believe that it will have any real impact on performance.
July 30, 2009 at 9:31 am
As MladenPrajdic said on Twitter, the column order shouldn't matter.
So I guess there's no reason to worry about it.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
July 30, 2009 at 9:33 am
Lynn Pettis (7/30/2009)
Other than asthetics, does it really matter where in the column list the surrogate column resides when looking at the list of columns for a table? Does SQL Server really care where the column is located in the list of columns when storing data in the database?At this point, I would not worry about wether the column(s) you are adding are at the begin or end of the column list. I don't believe that it will have any real impact on performance.
It's nice to have it at the beginning obviously for visual reasons, but I'm not going to be concerned if that's the only reason at this point.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply