October 27, 2008 at 9:14 am
baumgaertner (10/27/2008)
I need a standard approch for "Change Ordinal Column Position" that works on every table independant from PK's, FK's.
Why? What are you trying to achieve here?
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
October 27, 2008 at 9:29 am
WILLIAM MITCHELL (10/25/2008)
I'm not in favor of changing the order of columns, but it is possible.It is very easy to change the order of the columns in SSMS if you go into the table design view and just select a column and move it up or down with the mouse.
I can think of one scenario where you might want to change the order of the columns - one best practice that I've seen is to have columns for the record creation date & user as the last two columns. If you need to add a new column to the table, it would be nice to keep those two columns at the end.
Actually, I think it better to have standard columns like these listed first in the tables as they shouldn't change. This way, as new columns are added, you don't have to move them to keep them at the end.
😎
October 27, 2008 at 1:26 pm
Lynn Pettis (10/27/2008)
Actually, I think it better to have standard columns like these listed first in the tables as they shouldn't change. This way, as new columns are added, you don't have to move them to keep them at the end.😎
Just curious - but can you define first for me? Are you using ORDER BY to define some order to the results that I am not aware of? 😀
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 27, 2008 at 6:40 pm
Garadin (10/27/2008)
What you're basically asking for with this change is a script to promote laziness and inefficient coding practices that can cause all sorts of issues down the line.
I've agreed with most everything on this thread, so far, but you're gonna have to explain how trying to shift the ordinal order of columns in a table promotes either "laziness or inefficient coding practices". 😉
--Jeff Moden
October 27, 2008 at 6:58 pm
Fair enough. It was a pretty bold statement that may not be 100% accurate. It was in response to this:
Yes, that's all right, but if you make a "SELECT * FROM tblXXX" on a table with 100 columns the columns should be logical sorted so that you find the information you need.
If you have 100 columns in your table and you go so far as to re-order them so that you can use select * from and see things easier, to me you're just promoting laziness. If they're doing that within stored procedures when they don't actually need all those columns, you could be promoting bad coding practices. I don't personally know what problems may come out of it, but I trust the opinions of the people saying there can be.
A script I use all the time on our tables with 150-250 columns (don't get me started on why we have tables with 150+ columns) for development purposes or finding out the exact column names is:
select * from syscolumns where id = (select id from sysobjects where name = 'customer') order by name
I've actually got it macro'd. If you need to see the data within the fields, you can do a quick select top from there. So it just seems this is completely unnecessary and the only true gains are cosmetic in nature.
October 27, 2008 at 7:00 pm
Jeffrey Williams (10/27/2008)
Lynn Pettis (10/27/2008)
Actually, I think it better to have standard columns like these listed first in the tables as they shouldn't change. This way, as new columns are added, you don't have to move them to keep them at the end.😎
Just curious - but can you define first for me? Are you using ORDER BY to define some order to the results that I am not aware of? 😀
Sure, when you are creating the table, the first columns I'd define are:
ChangedBy varchar(some value),
LastChangeDate datetime,
any other related columns that may be standard for the database,
followed by the actual columns for the table
This way, if a new column is defined, it goes at the end, and I don't have to "move" the standard database columns to "keep them" at the end.
October 27, 2008 at 7:40 pm
Lynn, I knew what you meant - but, I was kinda having a bit of fun with you:
Sure, when you are creating the table, the first columns I'd define are:
ChangedBy varchar(some value),
LastChangeDate datetime,
any other related columns that may be standard for the database,
followed by the actual columns for the table
This way, if a new column is defined, it goes at the end, and I don't have to "move" the standard database columns to "keep them" at the end.
When creating a table - these would be the first columns that would be put into the create statement. I agree with that...
However, would those columns show up first if I used one of the following? And define first here - ordinal position? Alphabetical? Other?
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'ProductInventory';
SELECT name FROM sys.columns WHERE object_name(object_id) = 'ProductInventory';
In fact, using the above - the order of the columns returned is different. Why?
And, as we both know - does it really matter? No...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 27, 2008 at 7:53 pm
True, but when you script the create statement or edit the table in the designer in SSMS, the columns are listed in the order you defined them in the initial create script.
If you are going to require every table to have common columns, such as a UpdateBy or LastChangeDate, I'd rather list this columns first in the create statement rather than at the end. The reason is that normally if you put them at the end, everyone wants them always at the end, requiring you to "move" them when new columns are defined.
If every table has them, you can also create templates that have them predefined so that every table always gets them as well.
Does the order matter, no.
😎
October 27, 2008 at 8:47 pm
Garadin (10/27/2008)
Fair enough. It was a pretty bold statement that may not be 100% accurate. It was in response to this:
Yes, that's all right, but if you make a "SELECT * FROM tblXXX" on a table with 100 columns the columns should be logical sorted so that you find the information you need.If you have 100 columns in your table and you go so far as to re-order them so that you can use select * from and see things easier, to me you're just promoting laziness. If they're doing that within stored procedures when they don't actually need all those columns, you could be promoting bad coding practices. I don't personally know what problems may come out of it, but I trust the opinions of the people saying there can be.
A script I use all the time on our tables with 150-250 columns (don't get me started on why we have tables with 150+ columns) for development purposes or finding out the exact column names is:
select * from syscolumns where id = (select id from sysobjects where name = 'customer') order by name
I've actually got it macro'd. If you need to see the data within the fields, you can do a quick select top from there. So it just seems this is completely unnecessary and the only true gains are cosmetic in nature.
Heh... looks who's promoting laziness... and with a "SELECT *" marco, no less. Well done!.. Seriously!! 🙂 And, I'm pretty sure the OP meant using SELECT * just from QA (or maybe even a macro :hehe:) and not in real code anywhere... or, at least I would hope that's the case.
No, I can see why some folks might want things all tidy when it comes to the ordinal order of column names. I think they spend too much time on it, but I don't believe it's necessarily wrong. There might even be some small performance advantage to having like columns adjacent to each other, although I'd certainly have to run a test on that little revelation before I claimed it's true. 😀
Me, I'm so lazy that I have code that writes my Insert/Select code for a table for me... I just pass in the table name and Viola... instant code, everything aliased and ready for a simple Search'n'Replace to update the aliases to what I need. Someday, I'll add the extra parameter... but, for now, I'm too lazy.:P
--Jeff Moden
October 27, 2008 at 8:49 pm
Lynn Pettis (10/27/2008)
True, but when you script the create statement or edit the table in the designer in SSMS, the columns are listed in the order you defined them in the initial create script.If you are going to require every table to have common columns, such as a UpdateBy or LastChangeDate, I'd rather list this columns first in the create statement rather than at the end. The reason is that normally if you put them at the end, everyone wants them always at the end, requiring you to "move" them when new columns are defined.
If every table has them, you can also create templates that have them predefined so that every table always gets them as well.
Does the order matter, no.
😎
Spot on...
--Jeff Moden
October 27, 2008 at 8:55 pm
Jeffrey Williams (10/27/2008)
In fact, using the above - the order of the columns returned is different. Why?
Heh... everybody say it with me... "Without Order By, the order of columns is not guaranteed". 🙂 (Just my turn to mess with somebody... take no offense, please).
But, like you said, shouldn't matter... well, unless you've made the fatal mistake of having a table with temporal column names... :hehe:
--Jeff Moden
October 27, 2008 at 10:44 pm
Heh... looks who's promoting laziness...
I never said *I* wasn't lazy =). But I do try to avoid it when everyone is telling me it could cause major issues down the road.
That said, I agree, this isn't really laziness, or necessarily bad coding practices. Both of those are pretty big leaps of logic that aren't necessarily true. I've seen "select * from" used as both... a lot... so that's where my mind automatically went when I read this.
October 28, 2008 at 9:40 pm
Heh... yeah, that was my first impression when I saw the original post. I wanted to scream "WHAT? ARE YOU NUTS??" Then I remembered the time when I had to do it either to make the boss happy or to make it easier on developers for the very reason posted and views were poo-poo'd from manager to mouse in the company. 🙂
Anyway, thanks for the feedback and for not taking it personal, Seth.
--Jeff Moden
October 29, 2008 at 9:20 am
Timely discussion.
I'm arguing with one of our VisualStudio/.NET developers about column order in tables. He's asking us to move columns around so they appear where he wants to see them with his VS and .NET views. I don't use, or know much about, VS/.NET so I don't really know if his reasons are valid.
It seems to me that a defining principal of a relation db is that table/row order should never matter. It would seem to me to be counterintuitive to then have order be important in a tool designed to work with a SQL database.
Does someone with VS/.NET experience think his point is valid? If so, why?
October 29, 2008 at 9:31 am
Tell him to use a SQL Server View. If he doesn't want to be aware that it is a view, try using the VIEW_METADATA option.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply