SET vs SELECT SQL Server

Many times we wonder how set and select are different and mostly we think that select is used to return data set back to user and set is used to set the value of variables.

This is exactly right but sometimes people also use to set variable value using select statement.

Lets try some statements and learn some behaviors as well:

-- create couple temp tables
create table #Test (BusinessDate datetime)
create table #TestNoRows (BusinessDate datetime)

-- insert null row in #test table

insert into #test values (null)

-- declare variable
declare @businessDate as datetime

select @businessDate = businessdate from #Test
select @businessDate

You will get Null.  A row was returned from table with Null. To make it more clear, we will now set some default value to variable so that variable has some value.

-- Now set some default value.
set @businessDate = '01/01/1970'

select @businessDate =  isnull(businessdate, '01/01/2000') from #Test
select @businessDate

You will get '01/01/2000'. Again a row was returned and isnull function was able to work on this value and replaced the default value.

Now we will test how select behave with empty table.

-- declare variable
declare @businessDateNoRow as datetime

select @businessDateNoRow = businessdate from #TestNoRows
select @businessDateNoRow

No row returned. You will get Null as default value of variable.

-- Now set some default value.
set @businessDateNoRow = '01/01/1970'
select @businessDateNoRow =  isnull(businessdate, '01/01/2000') from #TestNoRows
select @businessDateNoRow

You will get '01/01/1970' because no rows returned that would be checked for null or not null. So same default value stored in variable is returned.

Now we will test how set/select works.

--using set

set @businessDateNoRow = (select  isnull(businessdate, '01/01/2000') from #TestNoRows )

select @businessDateNoRow

You will get null.

--using select

select @businessDateNoRow = (select  isnull(businessdate, '01/01/2000') from #TestNoRows )
select @businessDateNoRow

You will get null too.

When no rows are returned and if set is used, you would write subquery to set value. Nothing to do with set or select.