October 3, 2012 at 5:20 am
hello experts,
please find attachment for the question..
October 3, 2012 at 5:23 am
Is this homework, interview questions?
October 3, 2012 at 5:32 am
hehe yes, its a question i faced in a test, i got the answer that is third one,it'll use index available on deptid. but need an explanation why optimizer dint use other two indexes.
October 3, 2012 at 5:42 am
And how did you come up with that it would use the DeptID index and not one of the others?
October 3, 2012 at 6:13 am
October 3, 2012 at 7:24 am
what i did actually i created a same table as mentioned in attached image and created index on each of the column then ran count(*) and saw execution plan it used index on deptid
October 3, 2012 at 8:26 am
Zeal-DBA (10/3/2012)
what i did actually i created a same table as mentioned in attached image and created index on each of the column then ran count(*) and saw execution plan it used index on deptid
So did I.
Here's the script if anyone wants to try it...
if object_id('dbo.Employeesx') is not null drop table dbo.Employeesx;
create table dbo.Employeesx
(
EmployeeId int,
EmployeeName varchar(256),
Salary money,
DeptId smallint
);
create clustered index IC_EmployeeId on dbo.Employeesx(EmployeeId);
create nonclustered index IX_EmployeeName on dbo.Employeesx(EmployeeName);
create nonclustered index IX_DeptId on dbo.Employeesx(DeptId);
insert dbo.Employeesx
(EmployeeId, EmployeeName, Salary, DeptId)
select top 100000
row_number() over (order by (select null)),
'Fred',
100,
9
from sys.all_columns a
cross join sys.all_columns b;
select * from dbo.Employeesx;
-- What will be the likely query plan?
-- Which index will SQL Server use & why?
select count(*) from dbo.Employeesx;
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply