I got an answer of 4 for the above, was expecting that it would be 1.

Question

I got an answer of 4 for the above, was expecting that it would be 1.

VM-723206

SSCrazy

Points: 2964
More actions
June 18, 2009 at 3:53 am

#135364

declare @table table (id int, [name] varchar(20))
insert into @table values(1,'SQL Server 2005')
insert into @table values(2,'SQL Server 2005')
insert into @table values(3,'SQL Server 2005')
insert into @table values(4,'SQL Server 2005')
declare @id int
select @id = id from @table where [name] = 'SQL Server 2005'
select @id
I got an answer of 4 for the above, was expecting that it would be 1.
Any ideas?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

Why would you expect 1?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Pyay Nyein Mr or Mrs. 500 Points: 575 More actions · Answer 2

there is no gurantee of the order of the return result unless you exclusively use the 'order by' statement.

Pyay Nyein Mr or Mrs. 500 Points: 575 More actions · Answer 3

if you are expecting 1, try this

select @id = min(id) from @table where [name] = 'SQL Server 2005'

and why are you expecting 1?

VM-723206 SSCrazy Points: 2964 More actions · Answer 4

No specific reason why I would expect 1; was expecting this since this is the first occurence in the table which satisfies the name match.

VM-723206 SSCrazy Points: 2964 More actions · Answer 5

I was just wondering why it should be 4 and not 1, 2 or 3?

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

the variable be assigned the value of every row, so the last row return is what the variable will be.

Because a table has no Order and you not using an Order By there is no way of knowing what the last record will be ...

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

VM-723206 SSCrazy Points: 2964 More actions · Answer 7

Christopher Stobbs (6/18/2009)
the variable be assigned the value of every row, so the last row return is what the variable will be.
Because a table has no Order and you not using an Order By there is no way of knowing what the last record will be ...

Ahh.. Great! Thank you Chris!

Pyay Nyein Mr or Mrs. 500 Points: 575 More actions · Answer 8

SQL Server returns the resultset which it can return fastest. So it would be that 4 is fastest to return for this statment.

If ORDER BY clause is not explicitly specified then even the clustered index will not return the resultset in order of how clustered index is built.

VM-723206 SSCrazy Points: 2964 More actions · Answer 9

Pyay Nyein (6/18/2009)
SQL Server returns the resultset which it can return fastest. So it would be that 4 is fastest to return for this statment.

What do you mean by 'fastest'? Could you please explain the internal mechanism of how this works?

WayneS SSC Guru Points: 95460 More actions · Answer 10

VM (6/18/2009)
declare @table table (id int, [name] varchar(20))
insert into @table values(1,'SQL Server 2005')
insert into @table values(2,'SQL Server 2005')
insert into @table values(3,'SQL Server 2005')
insert into @table values(4,'SQL Server 2005')
declare @id int
select @id = id from @table where [name] = 'SQL Server 2005'
select @id
I got an answer of 4 for the above, was expecting that it would be 1.
Any ideas?

Since your select does not have a where clause that restricts what you returned to just one row, the query will populate @id with the value of id for every row in the table that does match the where clause. The variable being 4 just means that the row with ID=4 happened to be the last row.

If your query had an order by of id descending, then you would get 1. Without an order by, sql will return the rows in whatever order that it is quickest for it to do so. So if you had put in values from 1 to 1,000,000, your variable could end up being any value. Repeated runs of the query could even give you different values for @id.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Pyay Nyein Mr or Mrs. 500 Points: 575 More actions · Answer 11

VM (6/18/2009)
Pyay Nyein (6/18/2009)
SQL Server returns the resultset which it can return fastest. So it would be that 4 is fastest to return for this statment.
What do you mean by 'fastest'? Could you please explain the internal mechanism of how this works?

It's depend on the size of the table and how SQL server store the data internally. In this case, the size of the table is small and SQL server will do the table scan and will bring back all the records which match the criteria. Christopher is right, it will be bring back all the 1,2,3,4 since all of these match the criteria, not only 4.

But there is no gurantee in order...try this

declare @table table (id int, [name] varchar(20))

insert into @table values(1,'SQL Server 2005')

insert into @table values(3,'SQL Server 2005')

insert into @table values(4,'SQL Server 2005')

insert into @table values(2,'SQL Server 2005')

declare @id int

select @id = id from @table where [name] = 'SQL Server 2005'

select @id

u will see the result 2, SQL server scan the table and return the result as it is stored internally rather than order it, because it is the fastest way atm. but if there are cluster index/indexes on other columns, you will get different result, since these factors will affect how SQL Server scan the table in fastest way. Hope that would help..

VM-723206 SSCrazy Points: 2964 More actions · Answer 12

VM-723206

SSCrazy

Points: 2964

June 18, 2009 at 7:35 am

#1011743

Yes, it definitely helped. Thanks a lot guys!