• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

column reordering

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
anushahanna
Asked:
anushahanna
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
anushahannaAuthor Commented:
>>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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
anushahannaAuthor Commented:
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
 
anushahannaAuthor Commented:
i meant careful.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
exactly. handle with care
0
 
anushahannaAuthor Commented:
thanks a lot!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now