Sql Server Alignment problem: sequence

Is it possible to add a column to a table using the "alter table"
statement and specify where in the sequence of columns the new column
sits. If not is there any way to alter the order of columns using TSQL
rather than Enterprise Manager / Design Table.

TIA

Laurence BreezeNo and no.

The physical column order should only be significant when you use SELECT * -
and you shouldn't use SELECT * in production code. List the columns in your
SELECT statements in whatever order you want them. Alternatively, create a
view over the table with the columns in the required order.

Your other option is to create a new table, populate it from the original
table and then rename it. This is what Enterprise Manager does behind the
scenes.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> No and no.
> The physical column order should only be significant when you use SELECT
> * - and you shouldn't use SELECT * in production code. List the columns
> in your SELECT statements in whatever order you want them.

However, in support or debug situations, SELECT * is very convenient to
use. And in this case, I find it important that columns are in some
reasonable order. And historic order is rarely reasonable.

Furthermore, if you want to move data between databases, it is far
simpler if columns are in the same order in both databases, as this
makes bulk-copying easier.

One should also not ignore the documenation aspect of it. When you read
the documentation of a 50-column table, do you prefer to have the columns
in historic order, or do you prefer some logic order with the primary
key first, and related columns close to each other.

So, while there is no support to insert a column in the middle other
than creating a new table and move data over, it is certainly a valid
question. Column order does matter!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Hi All,

I am new to this group and this is my first doubt i am facing at
present.

I am doing data migration. In this sequence i need to alter few views.
Alter in the sense, inside the existing query of view i want to include
one more column.

I want to do it inside one single script. If i run the script all views
should get updated.

Any help on this will be greatful.

my mail id is siddu.roy@.gmail.com.

Thanks in advanceYou can include GO batch delimiter following each CREATE VIEW statement.
Tools like OSQL, SQLCMD, SSMS and Query Analyzer send the preceding batch of
SQL statements whenever a GO is encountered.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Siddu" <siddu.roy@.gmail.comwrote in message
news:1167892621.237538.321780@.31g2000cwt.googlegro ups.com...

Quote:

Originally Posted by

Hi All,
>
I am new to this group and this is my first doubt i am facing at
present.
>
>
I am doing data migration. In this sequence i need to alter few views.
Alter in the sense, inside the existing query of view i want to include
one more column.
>
I want to do it inside one single script. If i run the script all views
should get updated.
>
Any help on this will be greatful.
>
>
my mail id is siddu.roy@.gmail.com.
>
Thanks in advance
>

|||SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.

That also means you cannot end it with a semi-colon and have to have a
keyword GO instead. That is another weird keyword in SQL Server; it
says make a batch out of the preceding statements.|||--CELKO-- wrote:

Quote:

Originally Posted by

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>

Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

BEGIN TRANSACTION
go
CREATE VIEW aaa
AS
SELECT 1 n
go
SELECT n FROM aaa
/*
n
----
1

(1 row(s) affected)
*/
go
CREATE VIEW aab
AS
SELECT n FROM aaa
go
SELECT n FROM aab
/*
n
----
1

(1 row(s) affected)
*/
go
ROLLBACK
go
SELECT n FROM aaa
/*
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'aaa'.
*/
go
DROP VIEW aaa
DROP VIEW aab
/*
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'aaa', because it does not exist in the system
catalog.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the view 'aab', because it does not exist in the system
catalog.
*/

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Alex Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

--CELKO-- wrote:

Quote:

Originally Posted by

>SQL Server is weird on this, but each VIEW statement has to be in a
>batch by itself. The reason is that VIEWs can be built on VIEWs, so
>you need to commit the first VIEW to do this.
>>

>
Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

Joe may have a point, even if did not hit the nail perfectly. Up to
SQL 6.5, there wasn't any deferred name resolution, so something like:

CREATE VIEW innerview AS SELECT 12 AS gurka
CREATE VIEW outerview AS SELECT gurka FROM innerview

would fail at compilation. For tables there were some special plumbing
to permit you to create a table and refer to it in the same batch, but
I guess they never found that worthwhile for views.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||--CELKO-- wrote:

Quote:

Originally Posted by

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>
That also means you cannot end it with a semi-colon and have to have a
keyword GO instead. That is another weird keyword in SQL Server; it
says make a batch out of the preceding statements.

Hi Joe,

Since we're picking on your answer here, can I also point out that GO
is a keyword for query analyzer (by default) and for the command line
tools. It is *not* a keyword for SQL Server, and is never sent to the
server.

This becomes obvious if ever you try to comment out a batch of code
that includes GOs. Because Comments are intepreted by SQL Server, but
the GOs are interpreted by the tool, you'll get error messages galore
(unterminated comments, unexpected * found, etc), plus whatever is
batched within the GOs within the commented out block still get
executed.

Damien|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Alex Kuznetsov (AK_TIREDOFSPAM@.hotmail.COM) writes:

Quote:

Originally Posted by

--CELKO-- wrote:

Quote:

Originally Posted by

SQL Server is weird on this, but each VIEW statement has to be in a
batch by itself. The reason is that VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>

Incorrect. MS SQL Server does not commit DDL right away (Oracle does).

>
Joe may have a point, even if did not hit the nail perfectly. Up to
SQL 6.5, there wasn't any deferred name resolution, so something like:
>
CREATE VIEW innerview AS SELECT 12 AS gurka
CREATE VIEW outerview AS SELECT gurka FROM innerview
>
would fail at compilation. For tables there were some special plumbing
to permit you to create a table and refer to it in the same batch, but
I guess they never found that worthwhile for views.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Yeah, right, his post makes more sence if one replaces 'commit' with
'submit'.

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||
On Jan 4, 3:35 pm, "--CELKO--" <jcelko...@.earthlink.netwrote:

Quote:

Originally Posted by

VIEWs can be built on VIEWs, so
you need to commit the first VIEW to do this.
>
That also means you cannot end it with a semi-colon and have to have a
keyword GO instead.

FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
it must still be "the first statement in a query batch".

Jamie.

--|||onedaywhen (jamiecollins@.xsmail.com) writes:

Quote:

Originally Posted by

FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
it must still be "the first statement in a query batch".

And still be the only.

(And I would suggest that ; is not a statement terminator in T-SQL - It's
statement initiator.)

Quote:

Originally Posted by

>onedaywhen (jamiecollins@.xsmail.com) writes:

Quote:

Originally Posted by

>FWIW in SQL Server 2005 you can end a CREATE VIEW with a semi-colon but
>it must still be "the first statement in a query batch".

>
>And still be the only.
>
>(And I would suggest that ; is not a statement terminator in T-SQL - It's
>statement initiator.)

Hi Erland,

I would have to disagree with that suggestion. The ; is statement
terminator in ANSI, and has been the (optional) statement terminator in
T-SQL since at least SQL Server 2000 (but I think it was allowed in
earlier versions as well). The fact that *some* statements now require
the preceding statement to be terminated doesn't change it into a
statement initiator.

Check out the location of the ; in the syntax diagrams in Books Online.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Monday, March 19, 2012

Alter table and column order

Thursday, March 8, 2012

Alter more than one view

Sql Server Alignment problem

Blog Archive

About Me