USE Northwind
GO

PRINT 'This script attempts to reorder a column by updating the system tables themselves. '
CREATE TABLE crazy_test(
	identcol INT IDENTITY(1,1), 
	col1 INT, 
	col2 INT, 
	col3 INT, 
	CONSTRAINT pk_crazy_test PRIMARY KEY NONCLUSTERED (identcol ASC))

CREATE CLUSTERED INDEX idx_crazy_test ON crazy_test(col2 DESC)

INSERT crazy_test(col1, col2, col3)
	SELECT 1,8,9 UNION ALL
	SELECT 2,7,11 UNION ALL
	SELECT 3,6,10 UNION ALL
	SELECT 4,5,12

PRINT 'Order works as expected'
SELECT * FROM crazy_test

PRINT 'Notice the colorder, which you would think "orders" the table columns.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'

--ONLY RUN THE SCRIPT TO HERE.  TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--Look in enterprise manager to see rows are ordered as expected.  Make sure you right-click on the Tables and refresh first.


--Now set the server to allow updates directly on the system tables and update the column order.
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE sc
SET sc.colorder = 
	CASE 
		WHEN sc.colorder = 3 THEN 2
		WHEN sc.colorder = 2 THEN 3
		ELSE sc.colorder
	END
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO

PRINT 'Notice the new colorder.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO

DBCC FREEPROCCACHE
PRINT 'Order works the same in Query Analyzer though.'
SELECT * FROM crazy_test

PRINT 'The indexes are still also on the right columns.'
EXEC sp_helpindex 'crazy_test'

--ONLY RUN THE SCRIPT TO HERE.  TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--If you check the Enterprise Manager table views and the diagrams, the order also has not changed.
----Again, make sure you hit the refresh first in Enterprise Manager before checking.
--The indexes also have not been changed.

--*****Now, we will change the colid like we did the column order.  We'll rerun the test from scratch so it's a fair test.
--DO NOT RUN THIS ON A PRODUCTION SYSTEM.  THIS IS JUST FOR TESTING.

GO
DROP TABLE crazy_test
GO	
EXEC sp_configure 'allow updates',0
GO
RECONFIGURE
GO

CREATE TABLE crazy_test(
	identcol INT IDENTITY(1,1), 
	col1 INT, 
	col2 INT, 
	col3 INT, 
	CONSTRAINT pk_crazy_test PRIMARY KEY NONCLUSTERED (identcol ASC))

CREATE CLUSTERED INDEX idx_crazy_test ON crazy_test(col2 DESC)

INSERT crazy_test(col1, col2, col3)
	SELECT 1,8,9 UNION ALL
	SELECT 2,7,11 UNION ALL
	SELECT 3,6,10 UNION ALL
	SELECT 4,5,12

PRINT 'Order works as expected'
SELECT * FROM crazy_test

PRINT 'Notice the colid.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'

--ONLY RUN THE SCRIPT TO HERE.  TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--Look in enterprise manager to see rows are ordered as expected.  Make sure you right-click on the Tables and refresh first.

--Now set the server to allow updates directly on the system tables and update the column id.
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE sc
SET sc.colid = 
	CASE 
		WHEN sc.colid = 3 THEN 2
		WHEN sc.colid = 2 THEN 3
		ELSE sc.colid
	END
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO

PRINT 'Notice the new colid.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO

DBCC FREEPROCCACHE
PRINT 'Order is now different in Query Analyzer.'
SELECT * FROM crazy_test

PRINT 'We messed up the indexes though.  Notice, it now shows col1 as the indexed column.'
PRINT 'THIS IS WHY THIS SHOULD NEVER BE RAN ON A PRODUCTION SYSTEM.  JUST RECREATE THE TABLE IF YOU'
PRINT 'NEED A NEW ORDER.  ORDER SHOULD NOT BE IMPORTANT IN THE RDMS THOUGH, SO YOU PROBABLY HAVE BAD DESIGN'
EXEC sp_helpindex 'crazy_test'

--ONLY RUN THE SCRIPT TO HERE.  TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--If you check the Enterprise Manager table views and the diagrams, the order has been changed both in table design and the diagram tool.

--Erase the test tables and reset the system to not allow system table updates.

GO
DROP TABLE crazy_test
GO	
EXEC sp_configure 'allow updates',0
GO
RECONFIGURE
GO

quote:

---

Originally posted by Kristen

You would do well to start removing all code that depends on column position Right Now! Most especially if it is based on SELECT * rather than SELECT Col1, Col2, ColA, ColB, ...

Kristen

---