Joe ObbishJoe Obbish
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:
Answer #2: For completeness, another way to approach this problem is to use [OUTER APPLY]. 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.
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.
Top Answer: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[vGetVisits]') AND type in (N'U'))
Answer #3: Since this *is* a SQL Server 2014 question I might as well add a natively compiled stored procedure version of a "cursor".
Top Answer: Cause
Tom VTom V
Top Answer: I can't test this theory at the moment, but based on the [most recent capture data posted to GitHub], 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.
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.
John EisbrenerJohn Eisbrener