Solved

column reordering

Posted on 2011-03-17
7
370 Views
Last Modified: 2012-05-11
in 2008, there is a setting that blocks from reordering the columns. what is the consequence of overriding this option? any concerns? or just a cosmetic like sp_configure 'show advanced options'

thanks
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35161256
the options is to avoid that the table (and all the dependent views, triggers, replication etc) would become invalid in the process.
this setting is very good for production databases, but can be easily changed for development / testing environments:

http://www.west-wind.com/weblog/posts/455560.aspx
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35165082
>>avoid that the table (and all the dependent views, triggers, replication etc) would become invalid

can you give a small example how reordering column can mess views, triggers etc.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35165330
see this example, and observe the different output for v1 and v2.
though the data is ok, the result of the view v2 changes also, which might have impact on application that rely on the column position of the query they run (via the view)

set nocount on
create table t1 ( col1 int, col2 int )
go
insert into t1 values ( 1,2) 
go
create view v1 as select col1, col2 from t1
go
create view v2 as select * from t1
go
select * from t1
select * from v1
select * from v2
go
drop table t1
go
create table t1 ( col2 int, col1 int )
go
insert into t1 values ( 2,1) 
go
select * from t1
select * from v1
select * from v2
go
drop table t1
go
drop view v1
go
drop view v2

Open in new window

results:  
col1	col2
1	2

col1	col2
1	2

col1	col2
1	2

col2	col1
2	1

col1	col2
1	2

col1	col2
2	1

Open in new window


I could not manage to get an issue with triggers, but replication needs to be re-setup when you drop a column (as you cannot just change the column "order" without recreating the table)

and in Oracle you could drop the table, recreate it with different column order, and the view will still return the column data "by index", so wrong data in the columns ...

you just have to be aware that basically, the column order shall really not matter
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 6

Author Comment

by:anushahanna
ID: 35165606
very good example. thanks a3.

so basically, in 2005 also we had the same issue.. now, sql server is just giving us a warning sign, right?

other than that, if you are caseful with your design, then nothing to worry in turning on this switch, correct?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35165608
i meant careful.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35166182
exactly. handle with care
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35169800
thanks a lot!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question