Solved

column reordering

Posted on 2011-03-17
7
362 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
  • 4
  • 3
7 Comments
 
LVL 142

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 142

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 142

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now