@AaronBertrand Some even claim that GROUP BY is extremely inefficient compared to PIVOT ;)
I'm talking about our our suspended and revived recently friend
This is not the right methodology. There is no reason to use SUM(CASE) logic on versions of SQL Server that support PIVOT. SUM(CASE) is what you used to have to do before PIVOT existed. It is extremely inefficient. — Matthew Sontum2 days ago
@JoeObbish It avoids setting a row goal, which TOP does.
It's interesting, because we want a row goal to get the Flow Distinct, but we don't want a row goal to affect the expected number of duplicates. Separating the ROWCOUNT and the FAST achieves both.
@JoeObbish Yes. It is widely thought that SET ROWCOUNT is deprecated for SELECT as well. It's not, as you say.
@JoeObbish I was reading your discussion with ypercubeᵀᴹ last night until I went to bed, and this morning I've read the rest of it as well as your discussion with Paul. So now I'd like to ask you to forget what I told you yesterday about my specialising in T-SQL. It's quite obvious to me now that I know next to nothing about it. Thank you :)
The majority of the questions I see on this site are functional in nature "How Do I Do X?" "Why Did Y Happen?" And the answers are functional in nature as well. "SELECT SUM(CASE)" "The connection did not close when the user logged out of their computer"
This is fine for hobbyists, but in the sof...
@AaronBertrand What can we say, "PIVOT is always better"
Fine. I recently converted a SELECT UNION SELECT GROUP to use CTE PIVOT at my current work. That conversion made it run 160x faster. But I never tested the intermediate SUM(CASE). I have a laptop/VPN so I just tested it. In my test case the CTE PIVOT took 10 seconds to return, the CASE(SUM) took 14. I retested a couple of times and the results are consistent. PIVOT is always better. Worst PIVOT trial was 11 seconds, best SUM(CASE) was 13 seconds. — Matthew Sontum1 min ago
Considering his previous comments about CI and NCI indexes, I really doubt he knows what a covering index is.
But, on a more personal note, have you considered a different profession? You say that you have 2 years of experience on me, but you are roughly where I was 10 years ago (7 years experience) I too thought a lot of cases were 'it depends' back then. Now I've worked long enough to see with a singular clarity, and everything is simple/easy. You might still get there, but is you indeed have 19 years and aren't there yet, you may never be, — Matthew Sontum3 mins ago
Oh boy. this is better than the Batman LEGO I'm going to later.
To refresh everyone's memory, there are two types of ways to perform a PIVOT, before SQL 2005, when PIVOT was introduced, most people did this:
SELECT RateID
SUM(CASE WHEN RateItemTypeID = 1 THEN UnitPrice ELSE 0 END),
SUM(CASE WHEN RateItemTypeID = 2 THEN UnitPrice E...
Right, I still doubt that those examples perform any differently, so the assertion that PIVOT is faster even in that case is dubious. Can't answer now though, on the way to a kid's birthday party
@JoeObbish When it comes to query tuning, I still rely more on what makes sense outwardly, which is not always enough. I believe it would be fair to say that my understanding of physical operators is lacking.
Repeating the tests from Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - By Jeff Moden, 2010/08/06 (first published: 2008/08/19) on rextester
Normal
rextester: http://rextester.com/UVZE87903
create table #timer (what varchar(64), ended datetime);
insert into #timer values ('Start'...
@MartinSmith In case (ha!) you're interested, the difference in expression positioning is due to project normalization. This happens for CASE and not for PIVOT because the latter is only expanded to the CASE form during cost-based optimization. Project normalization happens before CBO. Disable with TF 9259.
It's a shame he wasn't able to provide DDL or plans. The devil is in the detail. Often it's an implicit conversion or a subtlety of execution. Ah well.
The boring answer is the one we already know: use the syntax you prefer, and only explore alternatives if performance is not acceptable. In most cases PIVOT and a proper implementation of Agg(CASE... will perform practically identically.
Is there a common reason to have multiple databases for the same application? I'm guessing I can't have any relationship between databases, so as far as I can think, you'll almost always have a single database?
I don't have anything particular in mind, I'm just curious.
SQL Server is what I was looking at. Currently changing a few things in Azure, and I noticed I could add more databases to my SQL Server, but couldn't come up with a reason to do so. :)
@WilliamMariager In my case, our ERP uses a SQL-Server database, and we have admin rights, but we prefer to add a second one just to avoid issues with proper releases and upgrades.
Microsoft has released the relational DB engine part of the SQL Server for Linux, including Ubuntu 16.01.
Is it possible to use this to install it on Debian (Jessie, for example)? If so, What different requirements would I have to consider to get it working?
I know this is pretty new, but I'd a...
@Philᵀᴹ By now my customer was sending me DELFOR messages, formatted as XML, now he has changed to SAP and he has sent to me a sample of the new messages. I think it's EDIFACT.
@PaulWhite Say a RateID has only RowType=1 values and not for 2 or 3. Then the 1st cross apply would be joined with the main table but the 2nd and 3rd join would remove that RateID. Right?
If the APPLY could return an empty set, there would be a difference. It can't. Scalar aggregates never return an empty set.
@ypercubeᵀᴹ Sure. Try an example.
Trivially:
DECLARE @T table (c1 integer);
INSERT @T VALUES (1);
SELECT * FROM @T AS T CROSS APPLY (SELECT s = SUM(T2.c1) FROM @T AS T2 WHERE 0 = 1) AS CA;
Output: 1, NULL
Compare with the vector aggregate:
DECLARE @T table (c1 integer);
INSERT @T VALUES (1);
SELECT * FROM @T AS T CROSS APPLY (SELECT s = SUM(T2.c1) FROM @T AS T2 WHERE 0 = 1 GROUP BY ()) AS CA;
I'm using PostgreSQL 9.6 on Linux.
I got an error when I do a test on chr() function.
postgres=# select chr(1199111);
ERROR: requested character too large for encoding: 1199111
postgres=# select chr(55296);
ERROR: requested character not valid for encoding: 55296
postgres=# select chr(100000)...
1> select @@version
2> go
Microsoft SQL Server vNext (CTP1.3) - 14.0.304.138 (X64)
Feb 13 2017 16:49:12
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
on Linux (Debian GNU/Linux 8 (jessie))
(1 row affected)
No it just fits together neater with what I'm used to doing — with Windows VMs, I'll either need to have a separate webserver VM or figure out how to run apache+PHP on Windows, or (least likely) figure out how to use IIS+whatever on Windows
I've got Windows Datacentre on the host already so the cost of adding Windows VMs isn't easy to quantify — it's not zero but it is more related to how much resources they use
The model is to keep the DB VMs as simple as possible so each has it's own webserver and a small amount of code — the queries are passed in, and results returned with json. Every DB VM uses an identical API, so the central webserver doesn't need to know anything about the DB it is talking to. With Linux it is 1 VM (apache+php+rdbms) per DB VM, and with Windows it'll be 2 with that model.
Kudos. I, for one, would love to see any real, reproducible example where PIVOT is faster. I know my skills are clearly somewhat lacking, but I still believe this claim is either fabricated or due to other, undisclosed variables that lead to the two queries not really being equivalent after all. — Aaron Bertrand ♦5 mins ago
Adulting
Adulting (v): to do grown up things and hold responsibilities such as, a 9-5 job, a mortgage/rent, a car payment, or anything else that makes one think of grown ups.
Used in a sentence: Jane is adulting quite well today as she is on time for work promptly at 8am and appears well...