3 cursors inside a transaction. phew.

Also, there is a select X where there is a select subquery Y inside select X which itself is a column in a larger...

SQLBill (4/29/2015)

---

I would also add validation to your procedure. For example, what if you run this:

EXEC MasterInsertUpdateDelete @firstname = 'John', @lastname = 'Smith', @city = 'Denver', @StatementType =...

Set first name=coalesce(@firstname,first name),last name =coalesce(@lastname,last name) etc.

A where clause in the select would also be a great idea.

In QA, run a trace, then do one step/submit on your application and save the trace.

You may find that the application is doing thousands of calls to the DB the...

Regarding how to get the optimizer to do what you want when it seems like the engine has got the row counts wrong,

http://sqlbits.com/Sessions/Event14/Query_Tuning_Mastery_Clash_of_the_Row_Goals

There are more convensional methods but...

Nested loop join on the slow plan between many to many and on the fast one it is nested loop 1 to many.

So the faster operation should be Many to...

Looking at the amount of left joins together with the top 1, it looks like the query is trying to end up with a pivot?

Have you explored using pivot to...

Firstly, results are never cached. It will be pages that are being read physically which get cached.

Use the query found here to see if your query is running out...

Going to take a stab in the dark.

I think you probably should do the ranking all by itself inside a windowed query and then do the case statement on the...

This is just an example.

Take note of the columns chosen as the indexing column, as well as partitioning column.

Be very clear that you know the difference between the partitioning and...

Was more looking for table definition.

What you can do is join sys.indexes to sys.partitions and filter on objectname of parentobjectid =yourtable And indindex_id <2.

This will show you your...

Looking at that scale, it may not be practical to change the clustered indexes to use another key so Gail's recommendation should be the least painful.

That being said, just be...

declare @Description varchar(50) = "textbox_search.Text.Trim()"

SELECT IT.tr_id AS [Tr Id], IT.tr_date AS [Tr Date], IT.tr_type AS [Tr Type]

, IT.tr_no AS [Tr No], IT.item_id AS [Item Id], IT.item_no AS [Item No]

, IT.item_des...

That's why I asked for objects to be scripted out completely, but it was not to be.

do an outer apply onto the subquery and then pass the columns from the outer apply into 2 different columns in the select.

ACtually, you could probably just do a straight...