« first day (2238 days earlier)      last day (666 days later) » 

12:00 AM
true
I have less than 6 years of experience
I probably don't know anything
 
12:11 AM
@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 Sontum 2 days ago
 
 
2 hours later…
1:48 AM
I don't have enough experience to dispute this either. Maybe he can prove it to us.
 
 
2 hours later…
4:01 AM
@JoeObbish @ypercubeᵀᴹ If there is an index, I would use a recursive CTE skip scan simulation. Otherwise, a CLR implementation. You could also try:
SET ROWCOUNT 5; -- TOP (n)

SELECT DISTINCT ID
FROM X_LARGE_TABLE_2
OPTION (FAST 1, HASH GROUP); -- For the flow distinct

SET ROWCOUNT 0;
Assuming multiple statements and hints are OK.
 
4:23 AM
@PaulWhite I thought that SET ROWCOUNT was deprecated
but apparently that's only true when interacting with DELETE, INSERT, and UPDATE
interesting
you're using it instead of TOP
I assume because ROWCOUNT has no affect on the query plan?
 
@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.
 
SET ROWCOUNT is only deprecated for DML
Sorry, maybe we can get a 4th person to confirm that too
 
Otherwise it'll just be anecdotal :)
 
@AaronBertrand I know just the guy. has 17 years of experience
@PaulWhite that's a good trick. I think that ypercube implemented something equivalent to the recursive CTE skip scan approach
 
I shouldn't really have starred that but ha ha ha
 
4:27 AM
what do you think of the LAG() and SUBSTRING approaches? I can quote them again (not sure if you read the entire exchange, it was pretty long)
 
@JoeObbish I did read the messages but didn't open/try all the examples.
They're all valid - only you can choose which you prefer of course.
LAG might do better as a Window Aggregate (requires batch mode).
 
I'll give that a shot
 
But for simplicity, I do find the ROWCOUNT/FAST 1 quite appealing for the rowstore heap case.
Partly because it looks like it should be dumb/deprecated, but is in fact rather subtle.
Ultimately, SQL Server ought to consider skip scan itself. On the backlog, perhaps.
 
@PaulWhite neither one of the tricks I know for getting batch mode worked, not sure why
both the empty CCI and the NCCI filtered index on the table
perhaps SQL server thought there weren't enough rows to make batch mode worth it
 
Hm.
 
4:33 AM
wild guess on my part
I do plan to try out the dreaded, infamous plan guide as part of my analysis
you mentioned you were a fan once
 
Yes I like plan guides. I get a Window Agg for LAG(1) but not LAG(0). Interesting.
 
@PaulWhite You're right. Batch mode helps quite a bit here
both a variable and optimize for don't seem to work here
could just filter out NULLs
by "work", I mean run a LAG(0) with window agg
 
Yes. It seems Window Agg LAG might be hard coded for literals >= 1. I'll try to confirm that.
Filtering out NULLs from LAG(1) seems a reasonable approach.
 
@PaulWhite Thanks for the help. I'll dig around a little more and post a question on Monday. You can show everyone up with your SET ROWCOUNT trick :)
 
I couldn't force a plan for LAG(1) Window Agg on LAG(0). The details are hidden inside ImplementSequenceProject.
Someone with more experience will likely be able to offer a better solution.
 
4:51 AM
@PaulWhite better solution for the general problem? or getting batch mode?
 
@JoeObbish Both/either. Just a dig at Mr. 17 years really.
 
@PaulWhite ah, missed that. and here I was thinking that there weren't any showplan operators left to try...
 
 
1 hour later…
6:21 AM
I will admit that plan guides are awesome for troubleshooting
 
 
2 hours later…
8:41 AM
@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 :)
 
 
5 hours later…
1:47 PM
HE IS BACK
0
Q: Which Non-Functional Requirements Are The Most Important?

Matthew SontumThe 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...

 
2:42 PM
He finds execution plans useless. That's cool. I find breast pumps useless for me but...
5
 
@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 Sontum 1 min ago
Considering his previous comments about CI and NCI indexes, I really doubt he knows what a covering index is.
 
3:02 PM
I'll post this one here for posterity.
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 Sontum 3 mins ago
Oh boy. this is better than the Batman LEGO I'm going to later.
 
3:15 PM
It gets better and better.
From the hacking threats we've gone to publishing threats. Awesome
 
 
1 hour later…
4:40 PM
0
Q: Is SUM(CASE) Ever Better Than CTE PIVOT?

Matthew SontumTo 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...

 
@AndriyM Sometimes I feel like I don't know anything either. You have plenty of good answers all over the site!
@McNets I suppose that's technically a valid question
 
A better question would have been: why does this demo show PIVOT running 10x faster than SUM(CASE....
 
well sure, it's not a question that I would personally ask
 
@JoeObbish I know, but is it necessary "To refresh everyone's memory"? If he wants to argue can open a chat not a question.
 
@McNets If you let people get under your skin then they win
 
4:54 PM
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
 
and besides, maybe someone new to the discussion reads the question and answer and learns something
 
My only issue is that he states one is faster than the other, almost as if it's understood as fact. That's not a good way to frame a question. iMHO
The only evidence he offered supporting that are his own observations and he is refusing to acknowledge that execution plans don't lie
 
hey guys
 
@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.
 
does anyone heard about PowerBI?
 
5:07 PM
@JoeObbish Thank you nonetheless!
 
do you think the product will last or will it die as some kind of hype product?
it is not related to database per se
but anyway, I'm asking, just in case
I cannot ask that on any SE's site as it is opinion based
The company I'm working with, wants to make investments on Qlik but we would like to know how worthy is the other solution we heard about, Power BI
 
And you want to make business decisions based on opinions in chat?
 
not only, based on if you have some insights
I'm doing my own due diligence
what we are saying here, I'll have a thorough look
a chat like a interweb link or any kind of documents need to be verified
but a feedback especially from here can be helpful
I'm not asking in a chat for jewellery's lover ;)
 
5:27 PM
1
A: Is SUM(CASE) Ever Better Than CTE PIVOT?

SqlZimRepeating 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.
 
I feel like if he's only seeing perf difference on one machine and unwilling to share more details it might perhaps be too localized.
 
It's impossible to know what he's seeing since there's no repro.
BTW Martin the pivot expansion rule is ExpandPivot.
 
@PaulWhite Thanks - yes always interested in stuff like that!
 
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.
Confirmation bias is a thing though.
 
5:50 PM
@McNets the question needs nuking
 
This question is valid: Can anyone construct an example where PIVOT is slower?
The rest is fluff really.
 
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.
 
@WilliamMariager Which RDBMS platform?
 
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 I can give you a few reasons for having multiple databases for non-Azure if that would be helpful
 
5:58 PM
That'd be cool. Like I said, I'm mostly just curious. :)
 
@WilliamMariager There are some settings that are scoped at the database level. So you might want to split your data between databases
For example, suppose that you have your production data in an Always On availability group
that requires a recovery model of full
recovery model is set at the database level
now let's say you do an ETL and have a lot of staging data that you write once per day
you could put your staging data in the same database, but it would be an unnecessary performance and storage hit
if you create a separate database just for the staging data you can give it a recovery model of simple
you would write less to the log files and wouldn't replicate that data to other databases
it's easy to write queries that look at data from multiple databases on the same server
I don't know how different things are for Azure
 
@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.
 
Multi-tenant is another reason. Customers often require more separation than a schema can provide. Common server and application, multiple databases.
 
@ypercubeᵀᴹ DBFiddle now has pg 9.4 as well, hint hint ;)
 
6:14 PM
@JoeObbish, I see. I don't know about Azure either. I have run into a few differences, but nothing too bad yet.
@PaulWhite, That makes sense.
 
Can anyone tell me if this post, about installing SQL Server on Debian, means "SQL Server 2016" by "Sql Server vNext"?
 
@JackDouglas No it means the next version of SQL Server, currently in preview.
 
ah
thanks!
 
@JackDouglas And it requires:
Red Hat Enterprise Linux 7.2
Ubuntu Linux 16.04
SUSE Linux Enterprise Server v12 SP2
In case that's why you were asking.
 
Yes the packages are for Ubuntu but I think they will install on Debian
So is the preview version of SQL Server the first released on Linux?
Or was 2016 released too?
 
6:29 PM
@JackDouglas Yes it's brand new.
2016 is only for Windows.
 
That's useful, thanks
 
0
Q: Is it possible to install Microsoft SQL Server v.Next on Debian?

Rafael Dias da SilvaMicrosoft 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...

 
Does anyone use any tool to import EDI files from SQL-Server?
 
@PaulWhite I think that is the post I linked :p
 
6:34 PM
@JackDouglas Oh! I assumed it was a q on main LOL
Ha ha ha ooops
 
7:02 PM
@McNets EDI is an acronym. There's many different formats of files for different industries etc. So, yes & no
 
@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.
UNB+UNOA:3+0941F200254740+RECEPTOR+170216:0858+20170216085837'UNH+1+DELFOR:
 
7:20 PM
@ypercubeᵀᴹ Why OUTER APPLY? I would use CROSS APPLY there. Scalar aggregates always return a value, even on an empty set. Small point.
The way I first learned to write a (dynamic) pivot was with a cursor.
 
@McNets get the spec off them. There's no single format, it's an acronym
 
@PaulWhite Just in case there are rows for one RateType but not for others.
 
@ypercubeᵀᴹ But the SUM will still return a NULL even on an empty set.
 
@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?
 
@Philᵀᴹ could be D91?
 
7:28 PM
@ypercubeᵀᴹ No, you'll get (correctly) a NULL.
Because the SUM of no values is NULL.
 
Really? So what's the difference with OUTER APPLY?
 
@ypercubeᵀᴹ None in that specific case
 
Let me check. I may be not explaining right what I'm thinking
 
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;
Output: <nothing>
 
3
Q: ERROR: requested character too large for encoding

Luan HuynhI'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)...

HALLO EVERYONE
 
7:54 PM
@PaulWhite You are right of course.
It's only needed if there is no GROUP BY in the apply subqueries
 
@ypercubeᵀᴹ That extra 'f' makes all the difference.
OUTER apply is needed if there is a GROUP BY inside the APPLY.
 
extra 'if'?
 
@AndriyM The original said "You are right off course".
 
@PaulWhite Aha, I see
 
Comment and edit explained at medium length.
 
7:58 PM
@PaulWhite did you mean "if there is no GROUP BY..."?
 
@ypercubeᵀᴹ Nope.
This is fun.
 
If there's no GROUP BY, you can use either OUTER APPLY or CROSS APPLY.
 
Yes.
If there is a GROUP BY you have to use OUTER APPLY.
 
...to guarantee that the row for which you are calculating the aggregate value is preserved (since the GROUP BY query may return an empty set).
 
Exactly so.
T-SQL Master.
 
8:07 PM
Hopefully I'll master it someday.
 
@PaulWhite And also if there is no GROUP BY and no aggregate. Right?
 
@ypercubeᵀᴹ Right.
Or just a GROUP BY and no aggregate :)
@ypercubeᵀᴹ There's no need to put any of that in your answer by the way. Just thought it was interesting to chat about.
 
@PaulWhite yeah, thnx.
I guess the behaviour may be related to the difference in results between aggregate without GROUP BY and GROUP BY ()
.. which we had discussed some years ago due ot a relevant question.
 
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)
@PaulWhite yippee
 
@JackDouglas Congrats!
 
8:20 PM
nice!
 
thanks, though there is no real reason why I can't just install it on Windows, and I'm going to have to do that for 2016 and before.
 
8:30 PM
@JackDouglas I thought the reason would be Windows licencing?
I assumed that was the reason you were using Debian for DBfiddle.
 
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
 
Ok cool.
 
8:52 PM
@JackDouglas can you run apache+php in one (Linux) VM and connect to either Windows (SQL Server) or Linux (the rest dbms)?
Or is that what you meant?
 
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.
Which is not that big of a deal
 
 
2 hours later…
10:39 PM
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
 
11:15 PM
An accepted answer, with -53 score, wow!
-53
A: Is there an idiom available, that is exactly opposite to "Cake walk" or "Child's play"?

Brad ThomasAdulting 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...

 
11:40 PM
SQL Server vNext on DBFiddle: dbfiddle.uk/?rdbms=sqlserver_next
6
 
@JackDouglas Shall we stress test it by pivoting a few million rows? ;)
 
pivots are quite stressful at the moment
I'm proud of dba.se today though — awesome expertise on the sql-server tag in particular
@ypercubeᵀᴹ if you play with it and find issues please let me know — I'm not sure if transactions are working the way they should be
 
@JackDouglas I will but probably not today.
 
cool, thanks
 

« first day (2238 days earlier)      last day (666 days later) »