Interview Questions

How many rows are returned by these two select statements?

Microsoft SQL Server FAQs

(Continued from previous question...)

How many rows are returned by these two select statements?

insert mytable select ''
insert mytable select ' '
select * from mytable where mychar = ''
select * from mytable where mychar = ' '

How many rows are returned by these two select statements?

Answer
2 and 2

Explanation
Each select statement actually returns 2 rows. You can use this script to check this: 

create table mytable
( id int identity(1,1)
  , mychar varchar(20)
)
go
insert mytable select null
insert mytable select ''
insert mytable select ' '
go
set ansi_nulls on
set ansi_null_dflt_on on
select * from mytable
 where mychar is not null
select * from mytable
 where mychar <> '' and mychar is not null
select * from mytable
 where mychar <> ' ' and mychar is not null
select * from mytable where mychar = ''
select * from mytable where mychar = ' '
set ansi_null_dflt_on off
set ansi_nulls off
go
drop table mytable

(Continued on next question...)

Other Interview Questions