Solved

column reordering

Posted on 2011-03-17
7
365 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

813 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

10 Experts available now in Live!

Get 1:1 Help Now