SQL-Server 2005: Change Ordinal Column Position

Question

SQL-Server 2005: Change Ordinal Column Position

Viewing 14 posts - 31 through 43 (of 43 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

RML51 (10/29/2008)
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?

You're correct... the only thing that it matters to is the human trying to write against it. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jchandramouli SSCommitted Points: 1772 More actions · Answer 2

Hi,

you can drop and create table the tblTest with backup of records.

1. Generate create script of the tblTest with latest modifications.

2. create a temp table (should be the old structure of tblTest) and insert the tblTest records into it.

3. drop the existing table tblTest and run the create script of the tblTest

4. Insert records from temp table to the new table

5. drop the temp table.

Hope you have an idea out of this for your requirement.

"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

7 Authority SSC Rookie Points: 48 More actions · Answer 3

Yes, although it shouldn't matter what the order of rows, there's a very important reason why I want the columns sorted.

When you're working on large systems, with a lot of tables, filled with a lot of fields, there's no way someone can remember a field name. It's very helpful to have the column list sorted so I don't have to scan the entire table.

Believe me, it makes things much easier. 😀

scott.pletcher SSCertifiable Points: 5958 More actions · Answer 4

I'd be nervous about meeting this request for a specific order of columns in a table, just because of the large number of tables in any significant db. That could turn into a huge workload overall for extremely little benefit.

Tables also tend to get larger (more rows) over time. Sure, not a really big deal to rebuild a table w/ 10K or even 100K rows. But what happens at 5M? 10M? 100M? Then it becomes serious wasted time.

Developers do need to have an easy-to-use list of columns ... but that can be created separately from the physical tables themselves. All the meta-data you need for the columns is easily available to query.

On those annoying [usu. improperly designed] tables that have gazillion columns, I have a query that lists them in alpha order. And one that lists them by type. And so on.

But don't try to ALTER the physical sequence of the columns themselves, unless the table is known to be small and will remain that way (e.g., a table of state names or area codes).

Scott Pletcher, SQL Server MVP 2008-2010

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

Please note: 2 year old thread.

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

paul.t.silvey SSC Enthusiast Points: 175 More actions · Answer 6

Yes, COLUMN ORDER DOES MATTER in some data layer tier applications. They look to the first column being the primary key and if an ID column was added at the end which is now the primary key, the applications cannot tell the difference.

Examples of such applications:

Hiebernate type apps, JRF (java related), etc...

In my present environment we deal with tables that contain almost a billion rows. We need rapid deployment for such an environment, but unfortionatley, Microsoft SQL server is aging in its methods. An update to syscolumns was always an easy way to fix this, but not now. I have yet to find a solution to this problem that does not require down time.

The old method was:

sp_configure 'allow updates', 1

go

reconfigure with override

go

UPDATE syscolumns tables...

SQL Silvey

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

paul.t.silvey (10/6/2011)
Yes, COLUMN ORDER DOES MATTER in some data layer tier applications. They look to the first column being the primary key and if an ID column was added at the end which is now the primary key, the applications cannot tell the difference.
Examples of such applications:
Hiebernate type apps, JRF (java related), etc...
In my present environment we deal with tables that contain almost a billion rows. We need rapid deployment for such an environment, but unfortionatley, Microsoft SQL server is aging in its methods. An update to syscolumns was always an easy way to fix this, but not now. I have yet to find a solution to this problem that does not require down time.
The old method was:
sp_configure 'allow updates', 1
go
reconfigure with override
go
UPDATE syscolumns tables...

The "fix" is simple, really... rename the table to something else and create a "pass-through view" with the correct columnar order and name it the same as the original name of the table. SQL Server takes about 65 milliseconds to accomplish this if you have a script to do it and the application shouldn't even know what you did. If it does, perhaps it's time time revisit the application.

I have to admit, though... I have a problem with any system that requires positional notation for columns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

paul.t.silvey (10/6/2011)
Yes, COLUMN ORDER DOES MATTER in some data layer tier applications. They look to the first column being the primary key and if an ID column was added at the end which is now the primary key, the applications cannot tell the difference.
Examples of such applications:
Hiebernate type apps, JRF (java related), etc...
In my present environment we deal with tables that contain almost a billion rows. We need rapid deployment for such an environment, but unfortionatley, Microsoft SQL server is aging in its methods. An update to syscolumns was always an easy way to fix this, but not now. I have yet to find a solution to this problem that does not require down time.
The old method was:
sp_configure 'allow updates', 1
go
reconfigure with override
go
UPDATE syscolumns tables...

How do these types of application deal with things like composite keys then? Strange that something hibernate still is so limited. Seems to me that is not so much "Microsoft SQL server is aging in its methods" as it is still using hack 3rd party data layers that don't support real data structures.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Sean Lange (10/7/2011)
paul.t.silvey (10/6/2011)
Yes, COLUMN ORDER DOES MATTER in some data layer tier applications. They look to the first column being the primary key and if an ID column was added at the end which is now the primary key, the applications cannot tell the difference.
Examples of such applications:
Hiebernate type apps, JRF (java related), etc...
In my present environment we deal with tables that contain almost a billion rows. We need rapid deployment for such an environment, but unfortionatley, Microsoft SQL server is aging in its methods. An update to syscolumns was always an easy way to fix this, but not now. I have yet to find a solution to this problem that does not require down time.
The old method was:
sp_configure 'allow updates', 1
go
reconfigure with override
go
UPDATE syscolumns tables...
How do these types of application deal with things like composite keys then? Strange that something hibernate still is so limited. Seems to me that is not so much "Microsoft SQL server is aging in its methods" as it is still using hack 3rd party data layers that don't support real data structures.

It isn't just MS SQL Server when it comes to Hibernate or nHibernate. Same thing happens with Oracle. As for concatenated keys, at my previous position we actually had to concatenate the keys into a one column key in the returned result set (using a ref cursor in Oracle).

Sean Lange SSC Guru Points: 286590 More actions · Answer 10

Lynn Pettis (10/7/2011)
Sean Lange (10/7/2011)
paul.t.silvey (10/6/2011)
Yes, COLUMN ORDER DOES MATTER in some data layer tier applications. They look to the first column being the primary key and if an ID column was added at the end which is now the primary key, the applications cannot tell the difference.
Examples of such applications:
Hiebernate type apps, JRF (java related), etc...
In my present environment we deal with tables that contain almost a billion rows. We need rapid deployment for such an environment, but unfortionatley, Microsoft SQL server is aging in its methods. An update to syscolumns was always an easy way to fix this, but not now. I have yet to find a solution to this problem that does not require down time.
The old method was:
sp_configure 'allow updates', 1
go
reconfigure with override
go
UPDATE syscolumns tables...
How do these types of application deal with things like composite keys then? Strange that something hibernate still is so limited. Seems to me that is not so much "Microsoft SQL server is aging in its methods" as it is still using hack 3rd party data layers that don't support real data structures.
It isn't just MS SQL Server when it comes to Hibernate or nHibernate. Same thing happens with Oracle. As for concatenated keys, at my previous position we actually had to concatenate the keys into a one column key in the returned result set (using a ref cursor in Oracle).

That may be one of the reasons it is your previous position. 😉 Man it is hard to believe there still 3rd party things that are that short sighted. Sad actually.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

Sean Lange (10/7/2011)
Man it is hard to believe there still 3rd party things that are that short sighted. Sad actually.

It's not a fault... it's a feature! 😀 Keeps people who know what they're really doing fully employed. 😉 It's hard to look bad when most 3rd parties are involved.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Lynn Pettis (10/7/2011)
...(using a ref cursor in Oracle).

GAH!!! MY EYES! MY EYES!!! NO MORE, PLEASE!!!

😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Jeff Moden (10/7/2011)
Lynn Pettis (10/7/2011)
...(using a ref cursor in Oracle).
GAH!!! MY EYES! MY EYES!!! NO MORE, PLEASE!!!
😛

Wait, Wait, it gets even better when interfacing nHibernate with Oracle!

The ref cursor (output parameter) had to be the first parameter in the stored procedure and there could only be one output parameter. All the other parameter passed to the stored procedure had to be input parameters.

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

Jeff Moden (10/7/2011) It's hard to look bad when most 3rd parties are involved.

VERY true there!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/