February 2, 2010 at 6:08 am
Hi all,
I have registered a differ behavior between the SQL server 2000 and 2008.
The next sample below returns different results, the rows are differently numbered.
In the SQL server 2008:
id_test_table some_code
------------- ---------
0 A
1 B
2 C
3 D
4 E
in the SQL server 2000:
id_test_table some_code
------------- ---------
1 A
2 B
3 C
4 D
5 E
Does anybody know a reason, an explanation?
Thanks to all.
Jiri
create function dbo.TheSameInt(@pInt int)
returns int
as
begin
return @pInt
end
go
create table #test_table(
id_test_table INT,
some_code CHAR(1))
go
insert into #test_table values (0, 'A')
insert into #test_table values (0, 'B')
insert into #test_table values (0, 'C')
insert into #test_table values (0, 'D')
insert into #test_table values (0, 'E')
go
declare @v_counter int
set @v_counter = 0
update #test_table
set id_test_table = dbo.TheSameInt(@v_counter),
@v_counter = @v_counter + 1
select * from #test_table
drop table #test_table
go
February 4, 2010 at 3:30 am
I tried to Google this so that I could get an anser but this came up in a SQL Server 2008 for Oracle DBA Class I attended a couple of years ago.
I believe that they change the behaior so that tit defaults to 0 as opposed to 1 with previos versions.
Perhaps another Forumn member can add to this and dispute or confirm this.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 4, 2010 at 5:54 am
It's nothing to do with defaults. The variable is being set to 0.
The problem is that you're setting the variable to be itself + 1 in the same row as you're setting somethings value to it. SQL Server does not honour processing select columns in the order they are supplied, so the variable can be incremented before the update. This was true in 2000 as well, but clearly the optimizer picks a different order in this particular scenario between the two versions. The situation becomes trickier still when you factor in parallelism and the fact that the order is not guaranteed (this is actually an example of a quirky update I think)
This is a bizarre piece of SQL anyway - is this just a random example, or do you actually use this code to set a sequence?
If you do, use IDENTITY on the id column instead, it will massively increase performance:
create table #test_table(
id_test_table INT IDENTITY(1,1),
some_code CHAR(1))
go
insert into #test_table values ('A')
insert into #test_table values ('B')
insert into #test_table values ('C')
insert into #test_table values ('D')
insert into #test_table values ('E')
go
select * from #test_table
drop table #test_table
go
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply