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.

Multiple valid OR conditions in select statement SQL Server

If you have a query that has OR condition,  you will not get multiple rows just because there are multiple conditions satisfied the result.
However many rows exist in table that many rows will be returned.

Also all the operators are evaluated before the query result is returned.
This can result in multiple rows to be returned.

Lets work with an example:

Create table #person (name varchar(50), age int)

-- insert one row

insert into #person values ('tom', 40)

-- select person whose name is either 'tom' or age is 40.

select * from #person 

where name = 'tom' or age = 40

One row will be returned.

-- insert second row 

insert into #person values ('monica', 41)

-- Now select with multiple conditions 

select * from #person 

where (name = 'tom' or age = 40 or age > 40)

2 rows will be returned which tells all the ORs are evaluated and based on filters, rows will be returned.

HTH

Using star * in SQL Server Views

Learned a lesson about using star * in views.

If you add new cols in the table that is referenced in the view as * (along with other cols using join), the view will only show same number of columns that it is originally compiled with. 
View definition doesn’t get update right if * is not used in the end.

It keeps the same number of columns and moves the data to wrong columns while trying to return same number of cols.

Never use *.

HTH

Return no result set with Min / Max

Hi,

Just in case if you wondering how not to return NULL rows when aggregate functions couldnt find value, this is how you can do it!

If Object_Id('tempdb..#myTable') Is Not Null 

Drop Table #myTable;

Go

Create Table #myTable(col1 Integer Null, col2 Integer Null)

Insert #myTable(col1) values (1)

;With t_max(tmax, tmin) As

       (Select Max(col2), Min(col2)

       From #myTable)

Select tmax, tmin

From t_max

Where tmax Is Not Null And tmin Is Not Null

HTH