Table Variable (bug)? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table Variable (bug)?

Author

Topic

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-15 : 17:37:21

I ran into this while testing some stuff and was wonding if it is some known behavior or not?

All that I'm doing is create a table variable, loading it with some data and then updating a random number of rows to a value. In this example I'm trying to update 3 rows and setting the Code columm to 'A'.

You will notice that there are 3 declarations of the table variable at the top of the script. I get why the first one may not work, but I would expect the second one to wokr correctly, but only the thirsd one works correctly. By "correctly" I mean updates 3 and only 3 rows.
(EDIT: I should probably note that I just hit "refresh" a bunch of times to notice that the first 2 table declarations updated a variable number of rows, for waht it's worth).

Maybe I can;t see the forest for the tree or somethig, but here is the script:

DECLARE @Foo TABLE (ID INT IDENTITY, Bar INT, Code CHAR(1))
--DECLARE @Foo TABLE (ID INT IDENTITY(1, 1), Bar INT, Code CHAR(1))
--DECLARE @Foo TABLE (ID INT IDENTITY(1, 1) PRIMARY KEY, Bar INT, Code CHAR(1))
DECLARE @Count INT


INSERT @Foo
SELECT 1, NULL
UNION ALL SELECT 2, NULL
UNION ALL SELECT 3, NULL
UNION ALL SELECT 4, NULL
UNION ALL SELECT 5, NULL
UNION ALL SELECT 6, NULL
UNION ALL SELECT 7, NULL

SET @Count = 3

SELECT *
FROM @Foo

UPDATE Foo
SET Code = 'A'
FROM @Foo AS Foo
INNER JOIN
	(
		SELECT
			ID,
			ROW_NUMBER() OVER(ORDER BY NEWID()) AS RowNum
		FROM 
			@Foo
	) AS Temp
	ON Foo.ID = Temp.ID
WHERE
	Temp.RowNum <= @Count

SELECT *
FROM @Foo

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-15 : 17:44:05

So what exactly is the question? You are creating a table variable and creating a new RowNum and joining it with the table variable where rownum < @count (which is 3). Since the rownum is random, the three rows will be different each time you run the query.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-15 : 17:47:06

The number of rows updated is also random when I use the first two declarations of the table variable. Only the third declaration updates three rows on every execution of the update.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-01-15 : 18:45:24

The filter is Rownum <3. The rownum value of 3 could be assigned to any ID value.. so depending on which one gets assigned the number of rows < Rownum value of 3 changes each time.

But I am not sure why the number of rows gets fixed when you force the PK on the ID column..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-14 : 16:26:19

I just wanted to follow up on this since I received a confirmation from Microsoft that this is expected behavior. I've never run into this issue before, but maybe this might affect how other use the NEWID() function.

From Microsoft:

Not a bug. The customer expected the NEWID function to be executed once per row of table variable @Foo. However, SQL Server makes no such guarantee. The SQL language's declarative nature leaves the semantics of nondeterministic scalar functions like NEWID largely up to the vendor. It is not recommended that users rely on them being executed a predictable number of times.

With hash join, or with loop join if we were to spool the inner side, NEWID would be called 7 times -- once per row of the inner side. With loop join (without spool), NEWID is called 49 times -- also once per row of the inner side. This is consistent with our efforts, in Yukon and beyond, to execute most nondeterministic functions once per row of the branch of the query they occur on. We call these nondeterministic functions "once-per-row" functions.

One way to protect against this is to store the NEWID guids as a column of @Foo, rather than generate them in the query. That way the JOIN and the NEWID don't happen in the same query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-14 : 16:46:15

[code]DECLARE @Foo TABLE (Bar INT, Code CHAR(1))

INSERT @Foo
SELECT 1, NULL UNION ALL
SELECT 2, NULL UNION ALL
SELECT 3, NULL UNION ALL
SELECT 4, NULL UNION ALL
SELECT 5, NULL UNION ALL
SELECT 6, NULL UNION ALL
SELECT 7, NULL

-- Peso 1
UPDATE f
SET f.Code = 'A'
FROM (
SELECT TOP 3 Code
FROM @Foo
ORDER BY NEWID()
) AS f

SELECT *
FROM @Foo

-- Peso 2
UPDATE TOP (3) f
SET f.Code = 'B'
FROM @Foo AS f

SELECT *
FROM @Foo[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources