Hi I have a table Sales. Usually I will be adding to this table all data for a month. So:
Now sometimes I just want to only add Sales for a certain product. So two questions:
You could add @Product as an optional parameter, but would have to be careful of parameter sniffing causing bad plans.
That would look like (assuming product is an int)
Make sure you check out the varied discussions on optional parameters.
answered Feb 19, 2010 at 02:36 PM
Kev Riley ♦♦
+1 to kev - but I would write
Instead of using an OR clause.
However it's not clear to me what you mean - because if you mean inserting all sales for a product in any month, then I woild split that out into a separate procedure...
answered Feb 19, 2010 at 03:19 PM
Matt Whitfield ♦♦
With such a generic question, it's hard to be specific, but in the interest of being contrarian...
No, don't use the same procedure with an optional parameter (which is possible, as Kev & Matt showed). Instead create a new procedure. The reason being, either Kev's or Matt's approach could lead to scans in the Product table and from a purely performance centered point of view, having a specified query that didn't include the ISNULL or the OR function would result in better index use and therefor more consistent and usually better performance.
answered Feb 19, 2010 at 03:47 PM
Grant Fritchey ♦♦
I agree with Kev and Matt, but just for the sake of options remember you can do this:
answered Feb 19, 2010 at 08:00 PM
I just had to check this out. With SQL Server 2005, doing a COUNT(*) on a table with some eight million rows, non clustered PK being an int identity column.
I don't know how well it shows on this picture, but three queries, delivering the same result creates different plans, where the BETWEEN-approach "costs" 6% and the other two "costs" 46% and 47%.
I saw this question now, very, very long after it has been answered, when Diarmuid got the Popular Question badge.
I just wanted to add something that might or might not be valid anymore. When I was at a training course with MVP Tibor Karaszi, he told me that using BETWEEN is more efficient than using WHERE (productid=@productid OR @productid is null). That was when we started using SQL Server 2000 at work, having used SQL Server 7, so I don't know if this is still valid.
Does anyone know if this is still valid?