Prev: Collation settings
Next: Sql Server Indexing With Two or More Columns
From: Jay on 6 Dec 2009 16:08
Now this is an instructive example, espically the comment about SQL Server
2000.

CREATE TABLE dbo.t3(a int) ;
INSERT INTO dbo.t3 VALUES (1) ;
INSERT INTO dbo.t3 VALUES (1,1) ;
INSERT INTO dbo.t3 VALUES (3) ;
GO

SELECT * FROM dbo.t3 ;

Unfortunatly, SET COMPABILITY_LEVEL 80 does not replicate this behavior.

"Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message
news:ubSkznqdKHA.4112(a)TK2MSFTNGP06.phx.gbl...
> Books Online should be your number one reference, whether you're in study
> mode or not. Just open the documentation that is installed with SQL
> Server and search for "Batches".
>
> Or you can look at the Books Online online, starting with
> http://msdn.microsoft.com/en-us/library/ms175502.aspx
>
> --
> HTH
> Kalen
> ----------------------------------------
> Kalen Delaney
> SQL Server MVP
> www.SQLServerInternals.com
>
> "Jay" <spam(a)nospam.org> wrote in message
> news:eeCISTqdKHA.4636(a)TK2MSFTNGP04.phx.gbl...
>> Got a reference Kalen? I'm in study mode and could use the refresher.
>>
>> "Kalen Delaney" <please_reply_on_newsgroups(a)domain.com> wrote in message
>> news:E878B949-453A-4564-8541-9AB92357ADA9(a)microsoft.com...
>>> As GO is the default batch separator, this seems to imply that your
>>> problem had to do with what statements can and cannot be included in the
>>> same batch. You might benefit by reading the documentation about batches
>>> in T-SQL.
>>>
>>> --
>>> HTH
>>> Kalen
>>> ----------------------------------------
>>> Kalen Delaney
>>> SQL Server MVP
>>> www.SQLServerInternals.com
>>>
>>> "morphius" <morphius(a)discussions.microsoft.com> wrote in message
>>> news:E07897E5-F4B2-45E0-89FB-122984E74B8E(a)microsoft.com...
>>>> Jay,
>>>> You are right. It was the ;'s and GO's. Thanks.
>>>>
>>>> "Jay" wrote:
>>>>
>>>>> Are you logically separating the statements with ;'s and placing GO at
>>>>> the
>>>>> end of logical blocks?
>>>>>
>>>>> Beyond that, you would have to build everything into a procedure and
>>>>> add
>>>>> error checking (see @@ERROR, TRY & CATCH).
>>>>>
>>>>>
>>>>> "morphius" <morphius(a)discussions.microsoft.com> wrote in message
>>>>> news:8490B9FC-4C70-4A24-95D4-EED5979B8727(a)microsoft.com...
>>>>> >I have a .sql file with about at least 70-80 different sql
>>>>> > statements in any order (insert, update, delete) . I ran it in ssms
>>>>> > successfully, but it
>>>>> > didnt run some of the statements. The statements that didnt run will
>>>>> > only
>>>>> > run
>>>>> > successfully if run by itself. How do I ensure every statement
>>>>> > successfull
>>>>> > fires?
>>>>> > I have read an article http://support.microsoft.com/kb/827575 about
>>>>> > the
>>>>> > output buffer.
>>>>> > 1. How is the output buffer increase?
>>>>> > 2. What is the size limit of the output buffer so that I can divide
>>>>> > my big
>>>>> > .sql file into several small ones?
>>>>>
>>>>>
>>>>> .
>>>>>
>>
>>


From: Jeroen Mostert on 6 Dec 2009 16:39
Jay wrote:
> Now this is an instructive example, espically the comment about SQL Server
> 2000.
>
> CREATE TABLE dbo.t3(a int) ;
> INSERT INTO dbo.t3 VALUES (1) ;
> INSERT INTO dbo.t3 VALUES (1,1) ;
> INSERT INTO dbo.t3 VALUES (3) ;
> GO
>
> SELECT * FROM dbo.t3 ;
>
> Unfortunatly, SET COMPABILITY_LEVEL 80 does not replicate this behavior.
>
The compatibility level generally does not replicate behavior that was
broken to begin with, and would be very unlikely to have been counted on to
work in a production environment. In this case, this sequence of statements
fails in all versions (as well it should), the difference is just in *how*
it fails.

For a full and quite instructive list of what the compatibility levels do
and do not do (which is required reading if you have an existing database
you want to raise the compat level for), see
http://msdn.microsoft.com/library/bb510680.

--
J.
From: Jay on 6 Dec 2009 17:25
Interesting list. However, that was a side point

This one, very simple example, clearly illustrates what a batch is and with
the 2000 bug (and an understanding of transactions) illustrates the
difference between a batch and a transaction.

I found it elegant in its simplicity and how much it said, in so little.

"Jeroen Mostert" <jmostert(a)xs4all.nl> wrote in message
news:4b1c248e$0$22937$e4fe514c(a)news.xs4all.nl...
> Jay wrote:
>> Now this is an instructive example, espically the comment about SQL
>> Server 2000.
>>
>> CREATE TABLE dbo.t3(a int) ;
>> INSERT INTO dbo.t3 VALUES (1) ;
>> INSERT INTO dbo.t3 VALUES (1,1) ;
>> INSERT INTO dbo.t3 VALUES (3) ;
>> GO
>>
>> SELECT * FROM dbo.t3 ;
>>
>> Unfortunatly, SET COMPABILITY_LEVEL 80 does not replicate this behavior.
>>
> The compatibility level generally does not replicate behavior that was
> broken to begin with, and would be very unlikely to have been counted on
> to work in a production environment. In this case, this sequence of
> statements fails in all versions (as well it should), the difference is
> just in *how* it fails.
>
> For a full and quite instructive list of what the compatibility levels do
> and do not do (which is required reading if you have an existing database
> you want to raise the compat level for), see
> http://msdn.microsoft.com/library/bb510680.
>
> --
> J.


From: Erland Sommarskog on 6 Dec 2009 18:01
Jay (spam(a)nospam.org) writes:
> Interesting list. However, that was a side point
>
> This one, very simple example, clearly illustrates what a batch is and
> with the 2000 bug (and an understanding of transactions) illustrates the
> difference between a batch and a transaction.

This is not a bug in SQL 2000. And it has nothing to do with transactions.

The reason is SQL 2005 and SQL 2000 behave differently is due to they do
recompilation differently. As you may know, SQL Server has this misfeature
known as deferred name resolution, so if a batch refers to a table that
does not exist, SQL Server will not tell you about it, instead it hopes
that the table will appear at run-time. And, indeed this time, it happens.

In SQL 2000, recompilation is on batch level. So when the first INSERT
statement is reached, SQL Server recompiles the entire batch, and discovers
the mismatch in the second statement.

But in SQL 2005, recompilation is on statement level, so the first INSERT
statement is reach, recompiled and executed. Then the second INSERT is
reached and the error is detected.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Jay on 6 Dec 2009 18:20
Thanks Erland, excellent description, I think I can make it my own.

I made the point on batch vs. transaction because I was helping someone fix
a routine a while back and their problem was that, because a batch groups
statements, like a transaction, they were treating a batch as if it was a
transaction. This was in 2000.

Since then, I found someone else that was making the same conceptual
mistake.

If I can find two people making this error, I must assume there are a lot
more and finding a SIMPLE example that illustrates the batch concept (and
differences in version) is white gold.

That's all.

And thank you for clarifying the 2000/2005 behavior differences and the
reference to "deferred name resolution".

"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9CDA58E12A3Yazorman(a)127.0.0.1...
> Jay (spam(a)nospam.org) writes:
>> Interesting list. However, that was a side point
>>
>> This one, very simple example, clearly illustrates what a batch is and
>> with the 2000 bug (and an understanding of transactions) illustrates the
>> difference between a batch and a transaction.
>
> This is not a bug in SQL 2000. And it has nothing to do with transactions.
>
> The reason is SQL 2005 and SQL 2000 behave differently is due to they do
> recompilation differently. As you may know, SQL Server has this misfeature
> known as deferred name resolution, so if a batch refers to a table that
> does not exist, SQL Server will not tell you about it, instead it hopes
> that the table will appear at run-time. And, indeed this time, it happens.
>
> In SQL 2000, recompilation is on batch level. So when the first INSERT
> statement is reached, SQL Server recompiles the entire batch, and
> discovers
> the mismatch in the second statement.
>
> But in SQL 2005, recompilation is on statement level, so the first INSERT
> statement is reach, recompiled and executed. Then the second INSERT is
> reached and the error is detected.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Collation settings
Next: Sql Server Indexing With Two or More Columns