If I may I'd like to add a little side-note about another difference that a lot of people seem to run into unwillingly. When using just SELECT and the query returns nothing - e.g. when the table is empty or when the WHERE excludes all records - the @variable remains unchanged. It is NOT set to NULL as most people seem to expect.

Example :

declare @id int
select @id = -1 -- init
select @id = id from #t where names = 'no such a thing present'
select @id a

However, when combining this with the SET statement it WILL change the variable into NULL even though nothing got returned.

declare @id int
select @id = -1 -- init
set @id=(select id from #t where names='no such a thing present')
select @id as id

A border-case I know and quite logical if you think about it IMHO, but I've seen many people burn their hands on when they use this in a loop and forget to re-init the @variable first. The SELECT returns nothing and they end up using the value from the previous iteration.

Good point Roby Van Hoye. SET will set the variable a value NULL when there is no value coming out of the SELECT statement and old value will be retained when there is error. For SELECT the value will never be reset to NULL when select statement resturns nothing.

Nice share Madhivanan Sir and nice and important side note Roby Van Hoye.

Thanks for sharing

@Roby, @Madhivanan:

I can't reproduce this behavior (no value coming from a SELECT, @variable retains last value).

This will reliably print out the names of the databases until there are no more databases. The exit criteria is the variable being set to NULL by a SELECT assignment of the variable.

DECLARE @current_id [int] = NULL
DECLARE @current_name [sysname]

SELECT @current_id = MIN([database_id]) FROM [master].[sys].[databases]

WHILE @current_id IS NOT NULL
    BEGIN
    	SELECT @current_name = [name] FROM [master].[sys].[databases] WHERE [database_id] = @current_id

    	PRINT @current_name

    	SELECT @current_id = MIN([database_id]) FROM [master].[sys].[databases] WHERE [database_id] > @current_id
    END

Marc Jellinek , It is becuase you have used aggregate function MIN for valiable assignment. Aggreates will always show you NULL when there is no data. Refer this post and look at point 13 http://beyondrelational.com/modules/2/blogs/70/posts/14865/null-null-null-and-nothing-but-null.aspx

So better to use always "set", except in one case. i.e, assigning multiple values to multiple variables.

@Madhivanan: That's my point. A SELECT statement that doesn't return rows will NOT always use the previous value. Have to be careful with those absolute statements.

Marc Jellinek , If you re-read my post you can see that old value will be retained when there is error and SET option is used. If there is no error, it is not true.

ramkoti, yes follow it

@Madhivanan: Where is the error? There is no error raised when a SELECT statement doesn't return records. It's simply a no-op.

DECLARE @db_name [sysname] = 'foo'

SELECT
    @db_name = [name]
FROM
    [master].[sys].[databases]
WHERE [database_id] = 12340987145

SELECT @db_name

No error, but the variable retains it's previous value

Marc Jellinek , When I said "re-read my post" I meant the original blog post. When you look at the second example, the select statement returns multiple ids and SET throws an error and will not assign any value to the variable and the old value will be retained. In your latest example, the select statement does not return a name and SELECT will not assign any value to variable so that it retains the old value if any. I hope this clears this confusion.

@Marc Jellinek

The case where you try to fetch the database-id that comes after the last database actually DOES return a row. That's maybe not what you'd expect, but , Like madhivanan explained, a side-effect of using Aggregation functions.

As a simple example you can look at the different result of these two queries :

 SELECT Min([database_id]) FROM sys.databases WHERE 1 = 2 -- feel free to replace 1 = 2 with [database_id] > 999999, effect is identical unless you have (had) MANY db's =)
 SELECT TOP 1 [database_id] FROM sys.databases WHERE 1 = 2 ORDER BY [database_id]

on first glance they should always return the same value. In practice they don't as the first version will return NULL (rows affected = 1), yet the second version will return nothing (rows affected = 0). Assuming you assign the output to a variable like this :

 SELECT @id = Min([database_id]) FROM sys.databases WHERE 1 = 2
 SELECT TOP 1 @id = [database_id] FROM sys.databases WHERE 1 = 2 ORDER BY [database_id]

The first one will always put NULL in @id, the second will will not touch whatever is stored in @id.

In this situation, the statement

A SELECT statement that doesn't return rows will always use the previous value.

is an absolute one.

ANSI Standard SQL has a row constructor assignment: SET () = ();

Hopeully, MS will catch up with the rest of the SQL world.