October 29, 2008 at 6:37 pm
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
February 4, 2009 at 5:27 am
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
September 14, 2010 at 9:25 am
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. 😀
September 14, 2010 at 9:42 am
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
September 14, 2010 at 9:47 am
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
October 6, 2011 at 10:10 am
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
October 7, 2011 at 6:03 am
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
October 7, 2011 at 8:20 am
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/
October 7, 2011 at 9:34 am
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).
October 7, 2011 at 9:46 am
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/
October 7, 2011 at 7:04 pm
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
October 7, 2011 at 7:06 pm
Lynn Pettis (10/7/2011)
...(using a ref cursor in Oracle).
GAH!!! MY EYES! MY EYES!!! NO MORE, PLEASE!!!
😛
--Jeff Moden
October 7, 2011 at 9:06 pm
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.
October 10, 2011 at 7:23 am
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/
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply