I think i found the solution.

I created an indexed view on begindate and did the days duration count in the query, not in a computed column.

The query produces:

cnt ...

Gail,

You are correct, i should have mentioned the varchar(max).

Please see the attached query below. I am aware it needs work to protect from SQL Injection ... its on my todo...

Sean,

In my original post, i said:

"I have a dynamic SQL query"

I also gave datatypes:

"@date is an integer."

"begindate is datetime format."

I also asked:

"Would it be faster to add a...

"The data types varchar(max) and date are incompatible in the add operator."

Since it is within a dynamic sql query, i get the above error msg.

What would be the best datatype to cast to, in...

Grant Fritchey (5/15/2014)

---

Then...

"By the way, just we're clear, dbo.cb is an indexed view? What's the key value on?"

dbo.cb is a Table not am Indexed View.

The primary key is ID.

Thanks

http://johnsterrett.com/2013/08/07/workload-tuning-finding-top-offenders-in-sql-server-2012/

Is this a good resource for Extended Events?

I followed the video and produced the following results.

Any advice is appreciated!

"A file you attempted to upload is not a...

Thanks for the warning!

http://www.statisticsparser.com/

Is this good enough?

Grant,

The Dynamic SQL CTE gets the main page data based on various selected / passed filters.

The Indexed SQL Views queries, the last three, just provide a count, so the user...

Grant Fritchey (5/14/2014)

---

How are you measuring the time? From the SSMS client? That includes the time to transmit across the wires and isn't a good measure of server performance. If...

I am using SQL Indexed Views in this example.

I also know the dynamic sql has not been 'fixed' to protect from sql injection. Its on the todo list.

Thanks

Sean Lange (5/13/2014)

---

isuckatsql (5/13/2014)

---

lol ..... and so we go full circle.

I started with a dynamic SQL query, too slow.

Then i went with an SQL Indexed View, much faster, but the...

lol ..... and so we go full circle.

I started with a dynamic SQL query, too slow.

Then i went with an SQL Indexed View, much faster, but the more items i...