1

I was messing around in SQL and I stumbled onto this code mostly by accident and for reasons I can't explain works at least in SQL Server 2008.

I wouldn't normally ask this on SO but I don't what I should be searching for, and I was hoping to find out.

  DECLARE @Number INT = 1

  SELECT @Number = @Number + N
  FROM (SELECT * FROM (VALUES (2), (3), (4)) vals(N)) vals

  PRINT @Number

This adds 1 + 2 +3 + 4 and produces 10.

Why does this work and does this pattern have a name? Is it an anti-pattern?

Ahmad Ragab
  • 1,067
  • 1
  • 12
  • 24

1 Answers1

2

Yes it's an anti pattern.

It isn't guaranteed to work (see recent Connect item) and will inhibit parallelism. Just use SUM.

  DECLARE @Number INT = 1

  SELECT @Number+= SUM(N)
  FROM (SELECT * FROM (VALUES (2), (3), (4)) vals(N)) vals

  PRINT @Number

It is similar to another old technique for concatenating strings that is also not guaranteed.

Why does this work

When it does work correctly the variable is incremented by N for each row in the source. However this is execution plan dependant as the linked Connect item shows.

Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Oh, dear. Thanks so much for the links. This is what I was looking for, because I was planning to use it for formatting currencies. I will need to reconsider. – Ahmad Ragab Dec 13 '13 at 22:44