pdanes (2/24/2016)

---

Here are the outputs from the SHOW_STATISTICS command:

IX_Podrobnosti_SubPhylum_5000Feb 23 2016 10:51PM7071513285100YESNULL70715

10SubPhylum_5000

NULL07071501

Sorry about the ugly output - if there is a tidy way to post table output, I don't know...

TheSQLGuru (2/24/2016)

---

You are NOT the first to do something with SQL Server without really understanding what you are doing or why. As a SQL Server consultant I make a lot...

pdanes (2/24/2016)

---

Jacob Wilkins (2/23/2016)

---

Ah, interesting. Estimating 1240 rows and getting an actual 25 million is certainly problematic 🙂

I must have misunderstood you earlier, though. I thought the stand-alone COUNT queries...

Ah, interesting. Estimating 1240 rows and getting an actual 25 million is certainly problematic 🙂

I must have misunderstood you earlier, though. I thought the stand-alone COUNT queries were performing well,...

I wouldn't have expected the EXISTS to perform any better, since that's exactly what the IF (SELECT COUNT...)>0 gets converted to by the optimizer, but it's always good to be...

IF (SELECT COUNT...)>0 actually gets converted to an EXISTS check by the optimizer. http://blogs.technet.com/b/wardpond/archive/2007/08/27/if-exists-select-vs-if-select-count-1-0.aspx

Like you, I'd still prefer the explicit EXISTS, for clarity if for no other reason.

I wrote up...

Alan.B (2/22/2016)

---

Jacob Wilkins (2/19/2016)

---

kkilsby (2/19/2016)

---

Doesn't the SQL DB engine first create a cross join for any query involving more than one table? So performance isn't an issue for a cross...

No. SQL...

Then that's the issue. Even on a 2012 or higher instance, the compatibility level of...

Are you sure the instance you're connected to is 2012+? I know you said you're using SSMS 2014, but you...

Sergiy (2/17/2016)

---

Since you're not returning anything from table t2 there is no point in using a JOIN.

Check for existence would do just fine:

select t1.[StockCode],t1.[SalesOrder],t1.[SalesOrderLine],t1.[LotNumber],t1.[Customer],

t2.[ShipDate][/i],

from table t1

WHERE EXISTS (

...

You don't have the correct scope for the UPPER.

At any rate, it's simpler to do it the other way around, since you already have the SUBSTRING working. Just take the...

Alan.B (2/17/2016)

---

Jacob Wilkins (2/17/2016)

---

Alan.B (2/17/2016)

---

Perhaps... To know for sure check the query plan (the...

Alan.B (2/17/2016)

---

Perhaps... To know for sure check the query plan (the actual query plan,...

Typically I would suggest doing formatting at the presentation layer, not the database layer, but if you need to do it in the query, then you can just make the...