{Thousands of recompiles a day} vs {thousands of extra table scans a day} is an easy choice.  I'd have to see actual damage from the recompiles to recommend...

Of course that forces the entire table to be rewritten every time.  In a typical rebuild, often large parts of the table do not need rewritten because they...

I'll have to think about that method some more, don't have time right now.

• This reply was modified 4 years, 4 months ago by ScottPletcher.

{Thousands of recompiles a day} vs {thousands of extra table scans a day} is an easy choice.  I'd have to see actual damage from the recompiles to recommend differently.  Partly...

So OP does in their next post.

OK.  I might still stick with recompile on this.  It's returning only a single int column and the controlling columns (in the WHERE clause)...

Just one more thing... The example above spans 29 months.  Is there a good way to separate it out to place three columns for each month within the date...

So how do you know this will be run "thousands of times a day"?  I wouldn't expect it to called anywhere even close to that many times.

I did not add...

Which version of SQL Server?  If you're in a recent enough version, you can use function TRY_CAST to see if the value is a valid int or not.

exec sp_executesql @sql = @sql, N'@date datetime', @date

Are you sure you need a specific datetime and not a range value such as the current day?

Actually you could be causing a performance problem by getting SQL to use the same query plan for different values, i.e., by avoiding the "overhead" of...

As to licensing issues, talk to an expert on that.  Licensing has gotten really complex, you need someone who truly studies to know all the details on that correctly.

Actually you could be causing a performance problem by getting SQL to use the same query plan for different values, i.e., by avoiding the "overhead" of a recompile. ...

You can use CPU affinity to reduce the number of CPUs available to SQL Server.

196 cores?  Wowza that's a *lot*.  If you're only at 25% usage, you presumably would be...

Actually you could be causing a performance problem by getting SQL to use the same query plan for different values, i.e., by avoiding the "overhead" of a recompile.  The first...

Below is some code that will probably do what you want.  I strongly recommend you run it first with the EXEC() commented out.  If all of the output looks ok,...