January 25, 2010 at 5:09 am
create table #emp
(
id int ,
e_name varchar (200)
)
create table #dept
(
id int ,
dept_name varchar (200)
)
insert into #emp
select 101 , 'aaa' union
select 102 , 'ddd' union
select 103 , 'ggg' union
select 104 , 'jjj' union
select 105 , 'bbb' union
select 106 , 'sss'
insert into #dept
select 101 , 'Soft' union
select 102 , 'HR' union
select 103 , 'Admin' union
select 104 , 'Staff' union
select 105 , 'Soft' union
select 112 , 'HR' union
select 113 , 'Admin' union
select 114 , 'Staff' union
select 115 , 'Soft' union
select 106 , 'HR'
select
dept_name ,
count(dept_name)
from #dept
where dept_name <> 'Admin'
group by dept_name
order by dept_name
select
e.e_name,
d.dept_name
from #emp e
inner join #dept d
on e.id = d.id
where e.e_name <> 'sss'
order by d.dept_name
-----------------------------------------------------------------
In above script , how SqL optimizer deals with different operators\functions , what is the sequnce
like
whether where clause will filter the data first , then selection of data with "SELECT" and art last ORDER by
please tell this kind of seqeunce in above 2 queries
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 25, 2010 at 6:04 am
Run the query with the 'include actual execution plan' option on and you'll be able to see exactly what order the operations are done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 25, 2010 at 7:06 am
create nonclustered index idx on #emp (e_name)
create nonclustered index idx on #dept (dept_name)
select
e.e_name,
d.dept_name
from #emp e
inner join #dept d
on e.id = d.id
where e.e_name <> 'sss'
order by d.dept_name
i think , this query should have RID lookup also for #emp table , as the releated index doesnt have id in it . other table showing RID lookup. please tell me why ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 25, 2010 at 7:14 am
Because you have a table scan on the #emp table. SQL's not using either index on that table.
A table scan reads the entire table (hence it's name). All columns, all rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply