CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Ahmad Ragab
Ahmad Ragab

Reputation: 1117

Looking for Name of a Particular SQL Server Pattern

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?

Upvotes: 1

Views: 65

Answers (1)

Martin Smith
Martin Smith

Reputation: 453142

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.

Upvotes: 2

Related Questions