SET vs SELECT
-
just curious if you guys now of any difference between the two...
-
I think they are both basically the same except you can't use set to get data from a table.
SET @Counter = @Counter + 1
SELECT @Counter = COUNT(ID) FROM SomeTable
-
You can
SET @Counter = (SELECT COUNT(ID) FROM SomeTable)
Far away is close at hand in the images of elsewhere.
Anon. -
A set does not return a result a select does.
-
Correct.
Another difference is the fact that you cannot use SET to assign a value to multiple variables in one go. (e.g. SET @var1 = 'value1', @var2='value2' won't work)
There are a number of threads around.
-
select sets @@ROWCOUNT as well.
-
SET is designed to set the value of a variable in one easy step with an clearly understandable keyword (primary thing).
SELECT variable does the same thing but typing
SELECT @var = 1
may cause so confusion to other programmers down the road as they may not clearly understand no need for a table reference (think about Oracle you must use at least FROM DUAL if needing to do one thing and a person coming from that world may not get it right away SQL doesn't).
Now SELECT however has far more flexibility. first you can multiple variables at one time instead of multiple SET statements. Secound the way the TDS works each record rolls into memory one after te other and you can concatinate with a single variable and SELECT statement.
Ex.
DECLARE @var VARCHAR(8000)
SELECT @var = @var + IsNull(colx + ', ', '') FROM tblX WHERE colz = 12
PRINT @var
Now another thing I do believe occurrs but cannot find the documentation on again is that SET does not cross the Query Manager the same for parsing as SELECT does and may have a slight less operation expense.
And just a correction you can use SET to get data from table like so.
SET @var = (SELECT TOP 1 colx FROM tblX)
but the output can only be one value.
-
Antares686 is right about the cost being less for set.
-
I must say that I really am glad I somehow stumbled onto this site and receive the regular e-mails. I have been working in Sybase for about eight years, did Oracle for about a year and have been working in SQL Server for a couple of years but was mostly utilizing my Sybase knowledge and not using its full power. Occasionally I hope I provide helpful input here but think I am gaining more just by the fact that I am playing around more. Nice to get the head up out of the code long enough to learn and try new things. The best new tip today was Antares686's
SELECT @var = @var + IsNull(colx + ', ', '') FROM tblX WHERE colz = 12
I wasn't aware that I could concatenate rows like this. It actually works in Sybase as well. Go figure. Goodbye to Excel for this task. Thanks Antares686.
-
Isn't that the only thing Excel is good for? 🙂
-
LMAO....
NO, of course not. Excel is also good for writing letters and text documents.....
-
Yes, I discovered that thanks to rmarda (on this site) and I haven't been back since.
-
One important point (imho), that Antares did mention but I just want to emphasize it, is that SET @Foo = (SELECT ...) will produce an error if multiple rows are returned by the SELECT-statement. On the other hand, SELECT @Foo = ... will run without error, and @Foo will contain the value from the last of these rows. Yes, it can be used to add the values from multiple rows into a single variable, but if you're just expecting a single variable you might receive unexpected results from this kind of query. In most situations you are probably better off with an error, at least if you handle it in a good way. Otherwise make sure that your select-query produces a single row if that's what you're expecting.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Edited by - chrhedga on 12/06/2002 01:15:41 AM
-
Just love to jump in here, I write so many stored procedures I just about go blind.
Select / Set are both great ways of moving data into variables, but Set does have the advantage of simplicity.
When writing a SELECT @Var = ... statement, I usually put a "Set NoCount On" and "Set NoCount Off" statement before and after. When running a statement within a loop you see many "1 row(s) affected" type comments. This requires communication back to the client which can drastically slow down the entire operation.
So for simple situations stick with the Set statement, or use Select and use your "Set NoCount Off" wisely!
-
Excellent input there with regards to SET NOCOUNT ON.
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply