Post reply

Suitable Way Of Nonclustered Index way for a table.

  • June 11, 2014 at 9:27 pm

    #148653

    I create a Table Like :

    CREATE TABLE Table1

    (

    Product_id int NOT NULL CONSTRAINT PK_table1_productid Primary KEY NONCLUSTERED,

    Product_name varchar(40) NOT NULL CONSTRAINT UK_table1_productname UNIQUE CLUSTERED,

    Product_description varchar(250) NOT NULL CONSTRAINT DF_table1_productdescription DEFAULT '',

    Store_id int NOT NULL,

    Location_Id int NOT NULL,

    )

    My Question is , in this table i have around 1 lacks records

    and i wants to filter records of a particular store_id or a location_id from table

    so which way is suitable of Non-clustered index way 1 or way 2 for fast serach.

    Kindly give difference if possible b/w 1 and 2?

    Way 1:

    Create nonclustered index IN_table1_storeid

    ON table1(store_id)

    Create nonclustered index IN_table1_locationid

    ON table1(location_id)

    Way 2:

    Create nonclustered index IN_table1_storelocationid

    on Table1(store_id,location_id)

  • June 12, 2014 at 3:22 am

    #1720963

    http://www.sqlservercentral.com/articles/Indexing/68636/ (also see the other two in the series)

    http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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