ScottPletcher (6/14/2016)

---

I'd make a slight adjustment, since I prefer to check conditions as near to the source as possible, and it might even be slightly more efficient:

SELECT X1,

...

You could do something like this:

SELECT X1,

AllVar

FROM your_table

CROSS APPLY

...

Compatibility level 90 is not supported in SQL Server 2014. Compatibility levels 100 and up are, however.

https://msdn.microsoft.com/en-us/library/bb510680.aspx

Cheers!

Lynn Pettis (6/10/2016)

---

Jacob Wilkins (6/10/2016)

---

Well, the attached plans aren't for acceptably-performing query with UDF and unacceptably performing query with UDF.

They're for query with UDF and query with inline equivalent.

It's...

Well, the attached plans aren't for acceptably-performing query with UDF and unacceptably performing query with UDF.

They're for query with UDF and query with inline equivalent.

It's expected that on large...

My best guess for the change would be a change in the query plan, specifically where in the query plan the function was being applied. Something like previously the function...

So there are two separate problems.

The first is that there are TransTypes other than 4 and 1, so ordering by TransType DESC means that the RN=1 could well be...

marcossuriel (6/7/2016)

---

Hello Guys,

I generated a sample from the actual live database. Please note that the TransType on the database is stored as follows:

TransType: 4 = Shipment

TransType: 1 = Receipt

I...

drew.allen (6/6/2016)

---

Jacob Wilkins (6/6/2016)

---

I didn't say it will return only rows with RN=1 and transtype='Shipment'.

I said it will return all such rows.

The "period" was to indicate that the...

drew.allen (6/6/2016)

---

Jacob Wilkins (6/6/2016)

---

It sounds like the problem is that you're returning rows where current quantity is 0, which violates one of your requirements.

If that's the case, then at least...

It sounds like the problem is that you're returning rows where current quantity is 0, which violates one of your requirements.

If that's the case, then at least that immediate problem...

There are two ways SQL Server can implement grouped aggregations (stream and hash).

They have different performance characteristics and each work well in certain situations, but the relevant difference here...

Grant Fritchey (6/1/2016)

---

Jacob Wilkins (6/1/2016)

---

There's an "Execute SQL" option in the Query Designer that you can use to run the query straight from the designer (available by right-clicking the SQL...

tom.w.brannon (6/1/2016)

---

I don't know anything about XML but the following works just fine as well:

CREATE TABLE #Mytest2 (id INT, x XML)

INSERT #mytest2

VALUES

(11,...

There's an "Execute SQL" option in the Query Designer that you can use to run the query straight from the designer (available by right-clicking the SQL pane).

Starting a while ago...