Início > Não categorizado > T-SQL/SELECT PitFall

T-SQL/SELECT PitFall

Today I worked in a very interesting T-SQL problem…

Look at the following queries:

DECLARE @i Int, @x Int, @y VarChar(250)

SET @i = 2

SELECT @i = 1,

       @x = @i + 1, — @i(1) + 1

       @y = CASE

WHEN @i = 1 THEN ‘Return 1’

WHEN @i = 2 THEN ‘Return 2’

END

SELECT @x x, @y y

GO

DECLARE @i Int, @x Int, @y VarChar(250)

SET @i = 2

SELECT @i = 1,

       @x = (SELECT @i + 1), — @i(2) + 1

       @y = (SELECT (CASE

WHEN @i = 1 THEN ‘Return 1’

WHEN @i = 2 THEN ‘Return 2’

END))

SELECT @x x, @y y

They are very similar, the difference is that in the second batch I’m using a SubQuery to compute the value of the variables @x and @y.

As you can see, the variable @i is being used in the same SELECT instruction three times, first it receives the value “1”, then the variable @x use the variable @i and the variable @y is also using the @i.

The main point here is, this is not a good practice but, this work fine, let´s look at the results to understand the problem.

Results at SQL Server 2005 SP3, 2008 SP1 and 2008R2:

11.png

The second batch returns a different value because the SubQuery was executed first, when the value of @i was 2.

Things start to get confuse when we run it in SQL Server 2000.

Look and guess what is the results in SQL Server 2000 SP4.

12.png

Pay attention, It should be included into our migration check list, because it was changed from SQL 2000 to 2005. So, be very carefully in write/analyze these kind of queries.

Anúncios
Categorias:Não categorizado
  1. 2 de setembro de 2011 às 1:51

    Interesting post. Thank you!

  1. No trackbacks yet.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: