Post reply

Temp table - table scan

  • September 13, 2012 at 6:24 am

    #266565

    create table #emp

    (

    @empno int,

    @firstname varchar(128),

    @middlename varchar(128),

    @lastname varchar(128)

    )

    i have the above table having no index in that. i cant make empno as primary. is it good to create an autoincreament column in this table though it wont be used in the query of retrieval.

    or can i create non clustered index??...i would like to avoid table scan for this table..in queries @empno is used in where condition and rest of columns is used in select columns...

    any help to finetune this one???/

    i am using sqlserver2008

  • September 13, 2012 at 6:50 am

    #1537166

    Yes you can create a clustered index on a #Tmp table.

    The syntax is exactly the same as for a normal table.

    CREATE [UNIQUE] CLUSTERED INDEX CIX_Tmp_EmpNo ON #emp(empno)

    Since empno is used in filtering, this would be preferable to autoincrement

    However your create table statement would not work, as column names can't start with '@'

  • September 13, 2012 at 9:51 am

    #1537306

    Thanks Brown!!!.. but i cant make column empno as autoincreament as per requirement....but i can additionally add one more column and make it auto increment. will that avoid table scan?.. because in where clause i have to use empno only....

    Thanks a lot

  • September 13, 2012 at 9:56 am

    #1537308

    BeginnerBug (9/13/2012)

    Thanks Brown!!!.. but i cant make column empno as autoincreament as per requirement....but i can additionally add one more column and make it auto increment. will that avoid table scan?.. because in where clause i have to use empno only....

    Thanks a lot

    How is this #temp table populated?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 13, 2012 at 9:59 am

    #1537312

    There is not a "requirement" for clustered indexes to auto increment. This is suggested for actual tables since having an incrementing key (such as an identity or datetime) reduces fragmentation and page splits.

    In this case adding an identity column to your temp table sounds like a waste of time.

  • September 13, 2012 at 10:01 am

    #1537314

    BeginnerBug (9/13/2012)

    Thanks Brown!!!.. but i cant make column empno as autoincreament as per requirement....but i can additionally add one more column and make it auto increment. will that avoid table scan?.. because in where clause i have to use empno only....

    Thanks a lot

    if you use only empno in the where clause to filter, and create the index as shown, that will avoid a table scan - so you don;t need the autoincrement column.

    If you don't have an index on empno, it will table scan if you have an additional autoincrement column or not.

  • September 13, 2012 at 10:06 am

    #1537318

    Thanks to All...

    create table #emp

    (

    empno int,

    firstname varchar(128),

    middlename varchar(128),

    lastname varchar(128)

    )

    Inside a Sp i m creating this temp table. is it possible to create a clustered index on the column that has duplication? .. because here empno column will have duplication....

  • September 13, 2012 at 10:15 am

    #1537324

    UNIQUE is optional, so omit that if empno has duplicates.

    But if empno has duplication, then isn't it a complete duplicate row (same firstname/middlename/lastname too)

  • September 13, 2012 at 10:21 am

    #1537328

    Yes.. But First name or MiddleNamee or Lastname would differ... I simulated my actual table into this one.. Thanks brown.. i will try to create clustered index on empno column....

    Thanks all for helping me in this ....i will let you know the performance gain

  • September 14, 2012 at 1:45 am

    #1537659

    BeginnerBug (9/13/2012)

    Yes.. But First name or MiddleNamee or Lastname would differ... I simulated my actual table into this one.. Thanks brown.. i will try to create clustered index on empno column....

    Thanks all for helping me in this ....i will let you know the performance gain

    It would be very helpful if you could post the query you intend to use to populate this temp table.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply