TopAnswers Databases
or
Why does my SELECT DISTINCT TOP N query scan the entire table?
Joe ObbishJoe Obbish
sql-server-2016
Top Answer: There look to be three different optimizer rules that can perform the `DISTINCT` operation in the above query. The following query throws an error which suggests that the list is exhaustive:
Joe Obbish
Answer #2: For completeness, another way to approach this problem is to use [OUTER APPLY][1]. We can add an `OUTER APPLY` operator for each distinct value that we need to find. This is similar in concept to ypercube's recursive approach, but effectively has the recursion written out by hand. One advantage is that we're able to use `TOP` in the derived tables instead of the `ROW_NUMBER()` workaround. One big disadvantage is the query text gets longer as `N` increases.
Joe Obbish
Answer #3: Here is an attempt to emulate a repeated partial scan (similar to but not the same as a skip scan) using a recursive CTE. The aim - since we have no index on `(id)` - is to avoid sorts and multiple scans on the table.
ypercubeᵀᴹ
Answer #4: You've already answered your own questions correctly.
Paul White
Answer #5: I think you have answer on why
paparazzo
GROUP BY with MAX versus just MAX
OfirisOfiris
sql-server
Top Answer: I created the table big_table according to your schema
Craig Efrein
Answer #2: >*The inserts to this table are in chunks of 50,000 rows with the same date. So I thought grouping by might ease the MAX calculation.*
Paul White
How to recursively find gaps where 90 days passed, between rows
IndependentIndependent
sql-server-2014
Top Answer: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vGetVisits]') AND type in (N'U'))
pavel nefyodov
Answer #2: As I read the question, the basic recursive algorithm required is:
Paul White
Answer #3: Since this *is* a SQL Server 2014 question I might as well add a natively compiled stored procedure version of a "cursor".
mikael eriksson
Answer #4: A solution that uses a cursor.
ypercubeᵀᴹ
Joining on same table without duplicating data in a field or using c#
CarllDevCarllDev
sql-server
Top Answer: There are a couple of ways to approach this, the following is a recursive solution.
Paul White
Get streak count and streak type from win-loss-tie data
jamaussjamauss
sql-server-2012
Top Answer: Another way to get the result is by a recursive CTE
Serpiton
Answer #2: One intuitive approach to solve this problem is:
Paul White
Answer #3: Since you are on SQL Server 2012 you can use a couple of the new windowing functions.
mikael eriksson
Is Page Life Expectancy a decent surrogate for measuring I/O churn?
MaxMax
sql-server
After upgrading to SQL Server 2019, function throws “insufficient memory” error
dsungarodsungaro
sql-server-2019
Top Answer: Cause
Paul White
Empty blocking process in blocked process report
Tom VTom V
sql-server-2012
Top Answer: I can't test this theory at the moment, but based on the [most recent capture data posted to GitHub][1], I would say that the reason that thee `<process>` node is empty is that it requires a currently running request (many of the attributes are found in `sys.dm_exec_requests` and not in `sys.dm_exec_sessions`) and without a currently running request, it can't report any details, similar to how doing an `INNER JOIN` between `sys.dm_exec_requests` and `sys.dm_exec_sessions` will exclude rows where a Session is active but is idle due to no current request.
solomon rutzky
Answer #2: Blocked transactions can occur because of lock escalations.
John aka hot2use
Trace Flag 4199 - Enable globally?
FilamentUnitiesFilamentUnities
sql-server
Top Answer: Personally, whenever I build a new server for a new project I always enable TF4199 globally. The same applies when I upgrade existing instances to newer versions.
spaghettidba
Answer #2: I wanted to share my experience with trace flag 4199.
Paul Williams
Answer #3: My search on the topic brought me here, so I'd just like to share my recent experience on the topic.
Greg
Using a Table as a Queue without sp_getapplock/sp_releaseapplock
John EisbrenerJohn Eisbrener
sql-server
Top Answer: For full disclosure, I'm lazy and leave writing safe locking code under concurrency to the professionals. So perhaps this is a Non-Answer but I'm writing it up anyway because you're a good guy and I want to help you.
Joe Obbish
“The Heap”
transcript
Max 
ignore me.  I'm going to put that down to moron user syndrome.
Max 
oh that's timing I suppose, I must've went to click the reply button on Sean's message, but then the display automatically scrolled to bring Erik's message into view, at the exact time I pressed the reply button.  hah.
Jack Douglas 
you also can't remove pings (only add more) for the same reason
Jack Douglas 
because the notification has already gone out to the first person you replied too
Jack Douglas replying to Max 
yes you can't change a reply to be a reply to someone else
Max 
I edited a message, intending to link it to Sean's message above.  When saving the edit, I got the popup.
Max 
@Jack I encountered a bug.  not sure if it's been seen before.  
![Capture.PNG](/image?hash=55e98c240f83b2139a13e832764cf3dc83e2f2f4b9f70955402c4262c06a4649)
Josh Darnell 
M0rN!nG
ypercubeᵀᴹ 
morning
Jack Douglas 
Morning
Paul White 
Morning
Andriy M 
Morning
Jack Douglas 
still it's been really helpful for identifying some low hanging fruit
Jack Douglas replying to Paul White 
that looks like it's got a few extras I'd like in pg_stat_statements
Paul White 
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
Paul White replying to Jack Douglas 
More like Query Store then?
ypercubeᵀᴹ 
morning all
GeorgePalacios 
morning
Jack Douglas replying to Tom V 
Yes it does look a lot like that, thanks @Tom. A bit more persistent perhaps.
Paul White replying to Tom V 
Thanks
Andriy M 
Morning
Tom V replying to Paul White 
Looks like sys.dm_exec_query_stats minus the plan
Paul White 
What does that do?
Jack Douglas 
pg_stat_statements is coming in handy
Lamak 
morning
Josh Darnell 
Morning
Jack Douglas 
Morning
Andriy M 
Morning
Paul White 
Morning
Paul White 
https://support.microsoft.com/en-nz/help/4536648/cumulative-update-12-for-sql-server-2016-sp2
Tom V 
Morning
GeorgePalacios replying to Paul White 
That's very wise. Can tell you've been doing this modding business a while
Paul White 
In the end, the only thing that gets rid of the worst troublemakers is persistence (deleting their content) and time (they lose interest).
Paul White 
But the best anti-spam tool on the SE network was built and is operated by the community.
Paul White 
It's fairly effective against spammers.
Paul White 
IP blocks are a blunt instrument anyway, since dynamic addressing is so widespread.
Paul White 
email and IP address are the only identifiers really and neither is unchangeable.
GeorgePalacios replying to Paul White 
Never thought of it like that. Even IP bans aren't permanent really.
Paul White 
They only work on SO/SE sites because people can't be bothered with the work arounds in general.
Paul White 
And none of them are particularly difficult to work around.
Paul White 
By the time you get there, things are almost always irretrievable.
Paul White 
Things like suspensions, account deletion etc. in the end are only discouragements.
GeorgePalacios 
As in, a stern talking to?
Paul White 
And discouragement is the only tool available.
Paul White 
Don't get me wrong: There are genuine problem users out there, and we'll need a way to discourage them at some stage.
GeorgePalacios replying to Paul White 
We shall see I guess :)
Paul White 
Right. The whole friendliness thing is overstated. It's a short-term phenomenon.
GeorgePalacios replying to Paul White 
Yeah. Signal - noise is likely going to be a much bigger problem than friendliness
Paul White 
Like 3-6 months after the last activity or something.
Paul White 
But not necessarily rapid.
Paul White 
I do think automatic deletion of unpopular questions here should be fairly aggressive.
Paul White 
Our recent friend on dba was only a problem when the question was closed and then people engaged past the point of being constructive in chat. Here, we could have had a bit of discussion in the built-in chat room, a summary answer if necessary, and that would be that.
Paul White 
Far too much time and energy gets spent on them
Paul White 
It's a pretty small minority in the end
Paul White 
Once someone has proven themselves to be a complete waste of time of course
Paul White 
As and when issues do arise, I'm quite fond of ignoring people until they go away as a general solution.
GeorgePalacios 
(jk)
GeorgePalacios 
I wonder if you could build that into some kind of rule, in anticipation of these events
GeorgePalacios 
SE version 2.0
Paul White 
That seems like a great way to pass the time.
Paul White 
Otherwise people will just endlessly debate how they can pre-emptively solve every problem they ever encountered elsewhere.
Paul White 
Exactly.
GeorgePalacios 
Considered is probably a better word
GeorgePalacios 
I do think being reactive rather than proactive has certain benefits here
Paul White 
One has to wonder which problems will arise without close votes and down votes.
GeorgePalacios 
heh. Very true
Paul White 
Yeah but SE are inept
GeorgePalacios 
That's where SE have a huge edge
GeorgePalacios 
And I'm sat there thinking, you don't have the data to support those models
GeorgePalacios 
Like in that thread you have people suggesting AI
Paul White replying to GeorgePalacios 
Well hopefully we'll find out.
GeorgePalacios 
I'm not stipulating on a solution by the way, just interested in the parallels between codidact and SE
GeorgePalacios 
but does the individual evaluation model scale
GeorgePalacios 
Very true
Paul White 
The more words and rules you have, the more scope for rules-lawyering.
GeorgePalacios replying to Paul White 
Possibly significantly easier whilst the community is small
Paul White 
And realistically this community has shown it can evaluate each case on its merits if it ever arises.
GeorgePalacios 
Yeah. I wonder if there's a critical mass when that won't be sufficient for people
Paul White 
We have https://topanswers.xyz/meta?q=8
Paul White 
Agonizing over exactly the right words to use.
GeorgePalacios replying to Paul White 
Yeah. It's almost change for the sake of change
Paul White 
Probably too much.
Paul White 
There's a lot of self-analysis there.
GeorgePalacios 
It's interesting that the exact same arguments are popping up in that new community
GeorgePalacios 
It seems to cause a lot of friction in communities
GeorgePalacios replying to Paul White 
Just the whole "Are we being mean" thing that has hit SE
Paul White 
I did read this:  
>Some rude comments are part of the reason why people consider Stack Overflow to be unwelcoming. Another part, perhaps even the biggest part, is the flood of downvotes and close votes questions get  
  
and thought that none of those would be a problem here :)
Paul White replying to GeorgePalacios 
That's very, very long. I couldn't get all the way to the end. What do you mean by "that" in "when that will hit us"?
GeorgePalacios 
I wonder when that will hit us
GeorgePalacios 
Very interesting thread
GeorgePalacios 
https://forum.codidact.org/t/are-we-trying-to-make-a-more-friendly-welcoming-site/762/11
Jack Douglas 
Morning
GeorgePalacios 
Morning
Andriy M 
Morning
Paul White 
Morning
Tom V 
Morning
GeorgePalacios 
#EasiestBugReport2020
Jack Douglas replying to GeorgePalacios 
thanks @GeorgePalacios
Jack Douglas replying to Paul White 
oh yes that isn't nice
Paul White 
Yeah that seems like a bug