June 11, 2014 at 9:27 pm
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
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy