Little question : What is the diference between ''select'' and ''set''
-
Hello partners , do you know if exists some diference by using a 'select' instead of 'set' ?
for example
set @name = 'Hector'
set @age = '27'
set @contry = 'Troy'
or...............
select @name = 'Hector',
@age = '27',
@contry = 'Troy'
what declare is the best?
??????
-
There is no difference for simple constant assignments.
Where there is a difference is in trying to capture local copies of global variables. For example, the variables @@ERROR and @@ROWCOUNT change after every statement. If you need to capture the contacts of *both* of these, you can't use SET - you need to use SELECT to copy them both in 1 statement.
-
Also see this very similar question: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=173078
-
For assignment of values to a single variable there really is no difference. If you are assigning values to multiple variables in succession, then you can pick up some minor performance gains by using a single select statement instead of multiple set statements. Note that if the code only runs once, the gains will be negligible, but if the same code runs multiple times within a loop/cursor, you may see a slight gain.
Using your example of 3 set statements vs. 1 select statement, if you have a loop/cursor that runs 10,000 times, SQL Server will process either 30,000 set statements or 10,000 select statements. In a situation similar to this, but more complicated, I was able to cut 2 seconds off a 45 second process by changing to single selects for multiple assignments. This is not a great performance gain, but every second can count, and by using the select, SQL Server will have more resources available for use on other processes.
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply