Thursday, September 11, 2008

T-SQL: Set Vs Select

First, let me just say that finally, in MS SQL 2008, you can declare and assign variables at the same time!
DECLARE @var1 [type] = [value1], @var2 [type] = [value2], @var3 [type] = [value3]
What's the difference between SET and SELECT?  SET sounds better and that's pretty much it.  With SET, you can only assign one variable at a time, with SELECT, you can do this:
SELECT @var1 = [value1], @var2 = [value2], @var3 = [value3]
Another great reason to use SELECT for setting variables is this:
SELECT @var1 = [column1], @var2 = [column2], @var3 = [column3
FROM [table]


  1. I did not understand one thing. So ill repeat what jacob said


  2. I don't think we're supposed to , Mike.

    This post is in reality some kind of secret message to the Russian Mob.

    Nyet Nyet!

  3. I believe I've said this to you at least once before, Moshe, but I'll say it again...
    Um, what?

  4. The title and tag say SQL, I thought that was kinda self explanatory, no?

  5. I'm gonna play a fun game of looking at 2 different pictures and finding the differences.

    So here goes, by SET you have to write "type" while by SELECT you don't have to.

    and then with select you can also create columns from variables in a table.

  6. but btw, SQL does sound familiar from somewhere...

    and btw, now that I'm using chrome it doesn't block the ads, so now I see ads on the right side, and a whole bunch of them offer to teach SQL, I guess because that's what the post is about.

  7. everything wrong...go learn SQL...

  8. i'll bet this is a just a post to get people who might not come hither to do so and click on the advs.

  9. Yes and no. A lot of people don't know obscure parts of programming. My previous posts get hits every day because a lot of people don't how.

  10. Moshe - is there a difference between T-SQL and regular SQL? I mean regular, the ones that are used by VB and AS/400?

  11. I think SQL is just a general name. MS uses T-SQL, MySQL uses MySQL and Oracle uses PL/SQL. Not sure what you use.

    One of the first difference I came across when I started doing MySQL is that in T-SQL you write SELECT TOP [rows] * FROM, while in MySQL, at the bottom of the query, you put LIMIT [starting row], [number of rows].
    Each also has mostly differently named functions.

  12. thanks. I haven't used SQL since 2001. Forgot most of it. Except last Friday saw this idiot literally hard code each record using SQL instead of putting date ranges. He billed full day for 5 people, for something that could be done by a single programmer in half an hour.

  13. That's messed up. Nobody bothered to check if he knows anything other than SELECT * FROM?

  14. Moshe - 1. I'm not sure if he knows or not, but it sure shows lack of analytical skill.

    2. This guy has 4-5 people working under him and not one of them mentioned an alternate way.

  15. Or maybe he's just clever enough to know how to make much more money by working slower. :)

  16. mlevin,

    isn't AS/400 like ancient? I was telling Moshe that even though still comparatively a youngling, I did program in Pascal within VAX/VMS environment (green screen monitors) as my first college programming course (yeah, that's as close as I came to mainframes). Is AS/400 still used extensively? We have one senior AS/400 guy in our shul -Oleg - maybe you know him.

  17. jacob da jew,

    when Russian guys talk programming to each other, it's indeed a crypted method of mafia talk. When a Russian guy talks to a Russian girl in programming, it signifies a language of looove, a hot chat.

  18. AS/400 Is still used and is considered a state-of-art computer as far as business models are concerned. As of a few years ago (I'm not sure about today) IBM was offering half a million for anyone who manages to hack into one. AS/400s are widely used, #1 business computer in Europe, but in US it's not as popular. It's mostly a perception problem (like you people think they are ancient). Another problem is they seem expensive when compared to Unix or other servers, but what buyers don't realize it that this price includes a lot more so that in the long run it ends up being cheaper.

    For example AS/400 comes with a compiler, database, source control and security among other things. On other computers each of these are sold separately, and users have to install and maintain each one separately as well as all the expirations and licensings.

  19. as/400 still used extensively by MOST corporations due to its stability.
    Such as ACME brick. BNSF. DrHorton. Orielly Auto. and many others just in DFW area of US.
    For instance JDE one world used by many fortune 100. You may even use it without knowing it since there are now many graphical UIs.