none
Index scan and table scan RRS feed

  • Question

  • Hi,

    I am using sql server 2000. Please try the below scenario.

    CREATE TABLE [dbo].[test_table](
        [id] [int] IDENTITY(1,1) NOT NULL primary key,
        [val] [varchar](100) NULL,
        [FirstName] [varchar](100) NULL,
        [LastName] [varchar](100) NULL,
        [EmailAddress] [varchar](100) NULL,
        [migrationstatus] [int] NULL,
     )
     
     create index ix_FirstName on test_table(FirstName)
     
    declare @i int
    set @i = 1
    while @i<=10000
    begin
        insert into test_table(FirstName,val,LastName,EmailAddress) values('aaa','test1','test2','test3')
    set @i = @i+1
    end


    If you have a primary key for a table (id in my example), the optimizer is choosing index scan for the below query

    select * from test_table where FirstName = 'aaa'

    But if you do not have a primary key for the table then the optimizer is choosing table scan for the below query

    select * from test_table where FirstName = 'aaa'

    Does that mean all the tables needs to have a clustered index mandatory for index scan or am I missing something?

    Thanks,
    Rakesh


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Thursday, June 7, 2012 10:22 AM

Answers

  • Yes. you are right. You need to built a clustered index to use any non-clustered index.


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    no this is not true. you can use nonclustered index without clustered index.

    nonclustered index can be built with include to cover everything a select query needs without going back to the main table either clustered or heap

    Thursday, June 7, 2012 11:48 AM
  • Thanks, You are right. If we create a cover index including all columns. In above sample non clustered index created only on first name.

    Here is code that use index seek without using clustered index the results.

    --drop table test_table
    CREATE TABLE [dbo].[test_table](
        [id] [int] IDENTITY(1,1) NOT NULL ,
        [val] [varchar](100) NULL,
        [FirstName] [varchar](100) NULL,
        [LastName] [varchar](100) NULL,
        [EmailAddress] [varchar](100) NULL   
     )
     
     create index ix_FirstName on test_table(FirstName) include (id,val,LastName,EmailAddress)
     
    declare @i int
    set @i = 1
    while @i<=1000
    begin
        insert into test_table(FirstName,val,LastName,EmailAddress) values('aaa','test1','test2','test3')
    set @i = @i+1
    end
    
    select * from test_table where firstname  ='aaa'

    and result


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Thursday, June 7, 2012 12:02 PM

All replies

  • Table Scan and Clustered Index Scan are essentially the same thing. Remember that the leaf level of a clustered index is the data pages. You will only see "Table Scan" if the table does not have a clustered index.

    There are situations, though, where the optimizer may scan only a range of a clustered index. A table scan is always of the entire table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, June 7, 2012 10:41 AM
  • Yes. you are right. You need to built a clustered index to use any non-clustered index.

    Non clustered Indexes built on Clustered Index as we see in XML Primary and Secondary Indexes.

    Am i right?


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Thursday, June 7, 2012 11:42 AM
  • Yes. you are right. You need to built a clustered index to use any non-clustered index.


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    no this is not true. you can use nonclustered index without clustered index.

    nonclustered index can be built with include to cover everything a select query needs without going back to the main table either clustered or heap

    Thursday, June 7, 2012 11:48 AM
  • Thanks, You are right. If we create a cover index including all columns. In above sample non clustered index created only on first name.

    Here is code that use index seek without using clustered index the results.

    --drop table test_table
    CREATE TABLE [dbo].[test_table](
        [id] [int] IDENTITY(1,1) NOT NULL ,
        [val] [varchar](100) NULL,
        [FirstName] [varchar](100) NULL,
        [LastName] [varchar](100) NULL,
        [EmailAddress] [varchar](100) NULL   
     )
     
     create index ix_FirstName on test_table(FirstName) include (id,val,LastName,EmailAddress)
     
    declare @i int
    set @i = 1
    while @i<=1000
    begin
        insert into test_table(FirstName,val,LastName,EmailAddress) values('aaa','test1','test2','test3')
    set @i = @i+1
    end
    
    select * from test_table where firstname  ='aaa'

    and result


    Shamas Saeed (if Post helpful please mark as Answer) http://shamas-saeed.blogspot.com

    Thursday, June 7, 2012 12:02 PM
  • Hi,

    No that's not correct, You can have a covering index as Nonclustered index.

    for instance just change your query to 
    select firstname from test_table where firstname='aaa' 

    and see the plan, you will get a Nonclustred index scan or rather Index Seek

    Regards
    satheesh


    Thursday, June 7, 2012 1:44 PM