2

I am running a stored procedure in SQL Server, something like:

INSERT INTO #TempTable
    SELECT CustomerID
    FROM Customer
    WHERE DateCreated > @TimeStamp

This is ignoring the index on the date column. I run the select query it is fine with a parameter, I change to this it is fine:

INSERT INTO #TempTable
    SELECT CustomerID
    FROM Customer
    WHERE DateCreated > '20150122'

I changed the whole query to dynamic SQL and now it runs fine too.

What is happening here?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Charles Bryant
  • 995
  • 2
  • 18
  • 30
  • 1
    question 1 - are you sure that `DateCreated` column is the same type as `@TimeStamp` variable – Roman Pekar Jan 22 '16 at 11:43
  • 3
    Also please read about parameter sniffing – Devart Jan 22 '16 at 11:49
  • 1
    As mentioned, sounds like parameter sniffing, I have a done a fairly relevant example with dates in [this question](http://stackoverflow.com/a/14469603/1048425), or could be [stale statistics](http://www.benjaminnevarez.com/2013/02/statistics-on-ascending-keys/) which is particularly prevalent with ascending keys such as created date, or identity. – GarethD Jan 22 '16 at 12:16
  • @GarethD would this still be an issue if the date parameter is read from a table and put into the parameter? – Charles Bryant Jan 22 '16 at 12:23
  • 1
    @CharlesBryant NO , please read Kimberly Tripp says : http://www.sqlskills.com/blogs/kimberly/stored-procedure-execution-with-parameters-variables-and-literals/ PAUL WHITE SAYS: http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options – Neeraj Prasad Sharma Jan 22 '16 at 12:29
  • Read [this answer](http://stackoverflow.com/a/34855620/243373) I gave recently on a very similar question, regarding Parameter Sniffing and how to circumvent it. – TT. Jan 22 '16 at 13:00
  • Thanks for the responses, @NeerajPrasadSharma those articles were really informative, essentially sql server does not know what the parameters are until it runs the stored procedure which is after it has chosen a plan, so it does not know the type of a variable. – Charles Bryant Jan 22 '16 at 13:07

0 Answers0