MS SQL Server - Re: Alter Table - Add Colum

Author

Re: Alter Table - Add Colum - Ordinal Position??

David Portas

2006-02-09, 3:23 am

Sreejith G wrote:

> Hi Roger,
>
> ALTER TABLE tab ADD COLUMN newcol [ AFTER | BEFORE ] curcol, just like
> mysql does is there any option in Sql Server 2005.
>
> Thanks,
> Sree
>

No, there is no such option in SQL Server 2005.

As Roger explained, if you apply good practices like never using SELECT
* in your code then physical column order is relatively unimportant. As
far as end users are concerned, column order is determined by the
column list in a SELECT statement, not by the physical placement on
disc.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Tibor Karaszi

2006-02-10, 3:23 am

> This may be a silly question, what are the drawbacks to altering the
> column order (colorder) through the syscolumns table?

Not a silly question at all, but a simple answer: Corrupt table.

Syscolumns isn't only for the engine in what order to retrieve the columns. But also it defines in
what order the columns are physically stored on disk (in pages, row structures).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/
Blog: http:// solidqualitylearning
.com/blogs/tibor/

"Ben" <vanevery@gmail.com> wrote in message
news:1139501596.454222.136580@g47g2000cwa.googlegroups.com...
> Hi,
>
> This may be a silly question, what are the drawbacks to altering the
> column order (colorder) through the syscolumns table?
>
> - Ben
>

Sponsored Links