Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 1st, 2005, 01:31 AM
Registered User
 
Join Date: Feb 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Column position in table

Hi All, i just wants to move a column from its 15th position to 2nd position in table using SQL(query analyzer NOT Entr.Mangr).
Reason : COLUMNS_UPDATED() function will only check the updated status upto 8th column in a table(as per my information) and i have to check this column.

Please updated,
with thanks .....kasana:)

 
Old June 1st, 2005, 09:41 AM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can't use alter table for this.
You have to drop the table and recreate it.

In general, column position should not affect the your database design/programming.

You *might* be able to update the information in the syscolumns system table to change the column order. This is absolutely not recommended.

If you absolutely have to change column positions, either re-create the table structure and import the data, or create a view with the proper column positions, and use the view instead.
 
Old June 1st, 2005, 10:24 AM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You use Columns_Updated() in the trigger not for moving column position

 
Old June 1st, 2005, 01:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

kasanar,

If you are looking for help on moving column from its position to other, as per my understanding, it could be done using alter table command. Else you can run a select to have this column placed anywhere in position as you wished. But I dont see that would help your cause.

Cheers!

_________________________
- Vijay G
Strive for Perfection
 
Old June 1st, 2005, 01:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Why do you think COLUMNS_UPDATED() is limited to only 8 columns?

From BOL:

"The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost."

This means that column 1 in the table is the leftmost bit of the first byte, column 9 is the leftmost bit in the second byte, etc.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old June 1st, 2005, 03:35 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jeff,
Can we change column position using ALTER TABLE? Correct me If I am wrong.
 
Old June 1st, 2005, 05:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by shahchi1
 Jeff,
Can we change column position using ALTER TABLE? Correct me If I am wrong.
I don't think so. The only way I know to "renumber" columns is to create a new table with the columns in the new order and copy the data contents from the original. If you use EM to move columns around in a table, look at the script that it generates and you'll see that it does it this way.

Changing the column "number" means that the data must move around in the data pages. The data is stored sequentially in column number order within the data row (var datatypes notwithstanding), and I know no way to change that short of making a new table...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old June 2nd, 2005, 02:00 AM
Registered User
 
Join Date: Feb 2005
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Jeff Mason
 Why do you think COLUMNS_UPDATED() is limited to only 8 columns?

From BOL:

"The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost."

This means that column 1 in the table is the leftmost bit of the first byte, column 9 is the leftmost bit in the second byte, etc.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
Thanks Jeff, it is done as for 3rd column :
if (substring(columns_updated(),1,1) & 4 ) =4
Kasana






Similar Threads
Thread Thread Starter Forum Replies Last Post
I added a column to a table, but, I can't see it. furjaw SQL Language 1 July 3rd, 2007 09:24 PM
bindingSource position after table.AddRow() miroslavign .NET Framework 2.0 1 September 12th, 2006 02:15 PM
Generating a 3 Column Table garethdown44 XSLT 4 June 14th, 2005 01:22 PM
how to make column of table 1 = to column of table gilgalbiblewheel Classic ASP Databases 4 October 11th, 2004 11:57 PM
Position of a table gisvb Dreamweaver (all versions) 1 January 5th, 2004 05:46 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.