Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

column reordering

Posted on 2011-03-17
7
Medium Priority
?
378 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

916 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