some skill in sql

sometimes ,if you need  to use in ,pls consider if we can create a procedure  as follows to instead the traditional mothod(select count(*) from tb where id in('10','16')).

create procedure usp_function_name

as

declare @cnt1 int

declare @cnt2 int

declare @cntrst int

select @cnt1=count(id)  from tb where id='10'

select @cnt2=count(id) from tb wehre id='16'

select @cntrst=@cnt1+@cnt2

return @cntrst 

this skill was used in my workflow prj for querying the pending workitems which grouped by modelalias.

 

variable and updatable column should be avoided to create clustered index.

the reason for this rule is when updated these columns , the index-structure   would be rebuilt.

we assume that the original indexes wer put on one page(8k), we updated one row, then the size of this column expanded over 8k and this row would be put on another page.random access IO  resulted in reducing the performance.