How many rows are returned by these two select s

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(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 Job Interview Questions

• When starting SQL Server 2005 from the command line, what does the -h switch do?
• In SQL Server 2005 Full-Text Search, you set up a replacement set like this...
• Using Report Builder, which of the following statements is correct about formatting numbers?
• Your SQL Server 2005 Express Edition locks up frequently and cannot be accessed. How can you enable the dedicated administrator connection?
• If you absolutely need a report to look the same regardless of what OS or viewing software is being used, which of the following file formats would be the best choice?
• Can you use Full-text search to index this column?
• How many rows are returned by these two select statements?
• Which key does the OPEN MASTER KEY statement open?
• What format are images rendered in if they were stored originally in another format?
• Where does Profiler store its temporary data in SQL Server 2005?
• More...