Basic Questions

[BJMurphy]

Hi All,

I am used to other SQL engines, and have a few basic questions--

1)If I wanted to conditionally drop a table, does SQL Server have a
way to natively do this? Many SQL implementations will allow
something like:

CREATE OR REPLACE tablename AS
SELECT
x,y,z
FROM sourcetable
;

Does SQL Server have something like this? This syntax, both the
"create table as select" syntax and the "create or replace" syntax
seem to cause problems.

2) Some of our existing queries have a keyword, "GO" where I would
otherwise expect a semi-colon. Is there a functional difference
between the two? I seem to be able to replace the "GO" keywords with
semi-colons without any changes in how the script behaves, but I
thought I would check and see if anyone has advice about the
differences here.

Thanks in advance,
Ben

[Erland Sommarskog]

BJMurphy (mu********@gmail.com) writes:

I am used to other SQL engines, and have a few basic questions--

1)If I wanted to conditionally drop a table, does SQL Server have a
way to natively do this? Many SQL implementations will allow
something like:

CREATE OR REPLACE tablename AS
SELECT
x,y,z
FROM sourcetable
;

Does SQL Server have something like this? This syntax, both the
"create table as select" syntax and the "create or replace" syntax
seem to cause problems.

You can create a table from a query this way:

SELET x, y, z
INTO tablename
FROM sourcetable

There is nothing resemblent of CREATE OR REPLACE for anything in SQL
Server. You need to have things like:

IF object_id('sometable') IS NOT NULL
DROP TABLE sometable

2) Some of our existing queries have a keyword, "GO" where I would
otherwise expect a semi-colon. Is there a functional difference
between the two? I seem to be able to replace the "GO" keywords with
semi-colons without any changes in how the script behaves, but I
thought I would check and see if anyone has advice about the
differences here.

GO and ; are entirely unrelated. In T-SQL, GO is just another
identifier like MyTable, [Order Details] or whatever. GO is used by
many query tools as a batch separator, so that you can give the tool
something like:

CREATE something
go
-- Use this something
go
-- Drop this something

Each batch will be sent to SQL Server separately. A batch can consist
of many statements, separated or not by semicolons.

In application code you would typically never use GO, but each Execute
command is a batch.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[BJMurphy]

On Feb 13, 5:17 pm, Erland Sommarskog <[e-mail]:

BJMurphy ([e-mail]) writes:

I am used to other SQL engines, and have a few basic questions--

1)If I wanted to conditionally drop a table, does SQL Server have a
way to natively do this? Many SQL implementations will allow
something like:

CREATE OR REPLACE tablename AS
SELECT
x,y,z
FROM sourcetable
;

Does SQL Server have something like this? This syntax, both the
"create table as select" syntax and the "create or replace" syntax
seem to cause problems.

You can create a table from a query this way:

SELET x, y, z
INTO tablename
FROM sourcetable

There is nothing resemblent of CREATE OR REPLACE for anything in SQL
Server. You need to have things like:

IF object_id('sometable') IS NOT NULL
DROP TABLE sometable

2) Some of our existing queries have a keyword, "GO" where I would
otherwise expect a semi-colon. Is there a functional difference
between the two? I seem to be able to replace the "GO" keywords with
semi-colons without any changes in how the script behaves, but I
thought I would check and see if anyone has advice about the
differences here.

GO and ; are entirely unrelated. In T-SQL, GO is just another
identifier like MyTable, [Order Details] or whatever. GO is used by
many query tools as a batch separator, so that you can give the tool
something like:

CREATE something
go
-- Use this something
go
-- Drop this something

Each batch will be sent to SQL Server separately. A batch can consist
of many statements, separated or not by semicolons.

In application code you would typically never use GO, but each Execute
command is a batch.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

- Show quoted text -

Thanks, Erland, for your response.

To clarify further, if I use a semi-colon at the end of my query, and
put several queries in a row, SQL server will not execute them until
it reaches the end of the set of queries or a "GO" statement. Is that
correct?

Put another way, if I am creating table A in one query and then want
to use it in the next query, do I have any options other than using
"GO" after the first query to ensure that table A will be available in
the second query?

I understood the semi-colon to be analogous to "GO" in that both would
submit the query to the SQL server, acting as a separator, but it
seems that you are saying this is not the case. I apologize for my
confusion, I just want to make sure I have everything straight in my
head.

[jim_geissman]

I'm not the expert that Erland is, but I think I know.

GO and semicolon both serve to separate statements. But GO also
separates batches.

If you make 4 statements into one batch like this --

statement1; statement2; statement3; statement4 GO

....and statement4 fails with an error, the affected objects are
restored to the way they were before statement1.

If you make them 4 separate batches like this --

statement1 GO statement2 GO statement3 GO statement4 GO

.... and statement4 fails with an error, the objects are restored to
the way they were after statement 3.

Jim

[BJMurphy]

On Feb 14, 4:54 pm, [e-mail] wrote:

I'm not the expert that Erland is, but I think I know.

GO and semicolon both serve to separate statements. But GO also
separates batches.

If you make 4 statements into one batch like this --

statement1; statement2; statement3; statement4 GO

...and statement4 fails with an error, the affected objects are
restored to the way they were before statement1.

If you make them 4 separate batches like this --

statement1 GO statement2 GO statement3 GO statement4 GO

... and statement4 fails with an error, the objects are restored to
the way they were after statement 3.

Jim

OK. It seems as though the variables that I had setup were only valid
and were local to the specific batch in which they were created. If I
have ten statements, some of which are codependent, but I know they
will run successfully, it seems like I would want to use semi-colons
because it will allow me to keep the values of the local variables
throughout the entire batch, since there is only one batch. Would the
codependent queries still work?

For example:

create table foo;
select * from bar into foo;
select * from foo where (true);

Is then equivalent to:
create table foo
go
select * from bar into foo
go
select * from foo where (true);

Again, assuming the queries run succesfully.

Is there a way to create global variables?

Thanks for your response, Jim. I appreciate the help. Also, don't
worry, a book is on the way (the highly recommended one that comes in
two parts, I forget the title and author, sorry), so I won't be
posting too many questions, hopefullly :-)

[Erland Sommarskog]

BJMurphy (mu********@gmail.com) writes:

To clarify further, if I use a semi-colon at the end of my query, and
put several queries in a row, SQL server will not execute them until
it reaches the end of the set of queries or a "GO" statement. Is that
correct?

Not really. SQL Server never reaches any GO statement. GO is not a statement
at all. GO carries no particular meaning for SQL Server at all. GO is a
separator that is intercepted by interactive query tools. You would
never have any GO in a program.

Keep in mind that SQL Server is a client-server application. Query
Analyzer or SQL Server Management Studio are not SQL Server. They
are client tools. When you select a couple of statements in a
query window, they will send these to SQL Server. But if there are
GO on some lines, they will send the batches one by one. And one
batch will not be sent, until the previous has executed.

Once in SQL Server, SQL Server will execute the statements, sequentially.
(Unless there is some control-of-flow languages.)

The semi-colons are statement terminators, but in difference to
other semicolon languages, they are optional in SQL Server. (Except in
a few situations where they are needed to disambiguiate the grammar.)

Put another way, if I am creating table A in one query and then want
to use it in the next query, do I have any options other than using
"GO" after the first query to ensure that table A will be available in
the second query?

There are statements that must be alone in a batck, like CREATE PROCEDURE
and CREATE VIEW. So to refer to you newly created view, you need to
do this in a separate batch. There are also some commands that does not
take effect until the next batch. Some SET commands are like that.

However, CREATE TABLE does not have any of these quirks. You can say:

CREATE #mytable (a int NOT NULL
INSERT #mytable(a) VALUES (213)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

BJMurphy (mu********@gmail.com) writes:

For example:

create table foo;
select * from bar into foo;
select * from foo where (true);

Is then equivalent to:
create table foo
go
select * from bar into foo
go
select * from foo where (true);

Again, assuming the queries run succesfully.

Well, since two of them has syntax errors, they will not. So
the first batch will fail. But if you run the second set of bathces,
the second statement will create the table "foo".

Is there a way to create global variables?

No. The place to write data that needs to be persisted over batches
is in tables, typically temp tables.

(OK, there is a command SET CONTEXT_INFO which could be used for
this, but it's not anywhere close to practical for this purpose.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx