sql - Creating non clustered Index for multi table joins -
i have written query joining multiple tables , multiple column condition. have large amount of data need create index on these columns. query should use index better performance. let me know how create below scenario ?
select a.name, b.sal, c.date1 inner join b on a.id = b.id inner join c on b.id1 = c.id1 a.status1 = '0' , a.totalamt <> 0 , a.flag = 'n' , b.sal > 100 , c.date1 not in('2008-08-08', '2009-09-09')
i tying same thing @ozren has written.but verify condition have written .
primary key
table a=id table b=id1 table c=id1 verify uncessary condition i) when sal>100 totalamt >0 (is so?) ii) a.status1 , a.flag ?(like when flag='n' status always='0' can put such validation while insert , avoid condition.
non ci
table (status1, totalamt, flag) table b (sal) table c (date1)
- analyze proc check amonth thee columns use in condition.
- if example
flag='n'
use can use filtered index on column - also ensure proper data type
flag
char(1) , not varchar etc.
Comments
Post a Comment