Reputation
2,035
Top tag
Next privilege 2,500 Rep.
Create tag synonyms
Badges
7 21
Newest
 Yearling
Impact
~97k people reached

Mar
18
comment Joining two inline functions, slows considerably when filtering on second function
Your use of terminology is a little confusing. Contrasting table-valued functions with inline functions like that seems to imply that inline functions are not table-valued. They are table-valued too, and even if you didn't know that, the OP's syntax certainly suggests that they return a table. The correct terms to contrast the two kinds would be multi-statement TVF (yours) vs inline TVF (OP's).
Mar
17
comment Joining two inline functions, slows considerably when filtering on second function
Perhaps it would be easier to find the issue if there were actual execution plans to compare.
Mar
11
comment Getting all descendants of a parent
According to the docs, Teiid does support recursive queries, you just need to omit the keyword RECURSIVE. (And while we are at it, you probably also need to get more acquainted with the manual.) Anyway, tagging your question with the wrong database system and not tagging it with the actual one is why you get the wrong answer.
Mar
6
comment SQL Server: Recursive dynamic SQL puzzle
First of all, please stop using single quotes around column aliases (as in 'b' = ...) – that is just bad. Secondly, what does the 'b' = ... example have to do with your question anyway? Or are you also confusing cases like SELECT columnalias = ... and SELECT @variablename = ...? You should probably ask about those separately.
Mar
4
comment Query for orders that contain a product without using sub-query
Orders_without_a_product_View seems a misnomer if its purpose is to find orders with a specific product. Or maybe I'm missing something.
Mar
4
answered Query for orders that contain a product without using sub-query
Mar
2
comment How can I use 'system pwd' command with MySql CONCAT() function?
And why would you need to know the current working directory of the server anyway?
Feb
27
comment I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches
The support, even though limited, for OVER that they introduced to T-SQL in SS 2005 opened quite a realm of new possibilities. Adding support for analytic functions like LAG and extending the support for OVER in SS 2012 at first seemed to me somewhat bleak in comparison. It's solutions like this that can make the improvement look more impressive. I am impressed, thanks for sharing this.
Feb
24
comment How to speed up query which is using <@> operator?
I'm not familiar with this <@> operator but the expression basically boils down to table.somepoint - someconstantpoint <= someconstantdistance, correct? I guess in order to make it sargable, you'd need to invert it to an equivalent of table.somepoint <= someconstantpoint + someconstantdistance. Not sure but maybe the earth_box() function and <@ operator are what you need here, as in "User".position <@ earth_box(Point(45, 45), 621.371192).
Feb
24
awarded  Yearling
Feb
24
comment Query to return zero if nothing exist
OP wants the totals to be returned "vertically", i.e. in a single column. How do you think your query would return them? No, not horizontally, but diagonally.
Feb
20
comment Issues with Optimizing TSQL Script
Logically, the join condition in your last query is not equivalent to what you are checking in the multi-statement script. The former covers a case not covered by the latter: MD.ManagerID = CR.CustomIdentifier AND CR.CounterPartyID = '' AND MD.SecurityAccount = CR.AccountCode. But if that combination of values can never appear in your data, then your attempt seems fine, only you need to replace null as SecurityCode with cr.GenevaCode as SecurityCode, of course, as well as include cr.GenevaCode into the GROUP BY, I guess.
Feb
20
comment I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches
A SEDE link (SQL Fiddle's been sluggish lately).
Feb
20
comment I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches
Well, my take on this isn't much better. Same four sorts in the plan (one is for the final ORDER BY, so it must be a given, unless there's a covering index), it just uses one CASE fewer.
Feb
20
comment I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches
All right, will give it a try. In the meantime, your max int check actually causes a minor bug. If the first value in a partition is 80 or greater, its block number will be last rather than 1. (Just change the first 66 to e.g. 86 and you'll see.)
Feb
20
comment I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches
@spaghettidba: I agree that your solution would probably be more efficient, but that max int thing does seem redundant to me too. You've got Value >= 80 all over the place – what is the chance a row with Value >= 80 can get a bb of 0? Seems like safeguarding against something you haven't thought through properly.
Feb
20
comment How to create a SQL trigger to execute a stored procedure when a table is updated
THROW is another thing that wouldn't work in every version of SQL Server.
Feb
20
comment I would like to get all values greater than 80, rank, then group in order to get a count of threshold breaches
Why is it DENSE_RANK and not ROW_NUMBER? Is it because there can be multiple readings per (Source, PollTime) and you want to treat them as one reading?
Feb
19
comment SQL Server's aggregate functions ignore nulls. How do I correct this?
Your saying "how I can get it to work correctly" seems to imply that the documented behaviour is not correct. So, perhaps someone disagreed with that premise, considering such a question not useful.
Feb
19
comment SQL Server's aggregate functions ignore nulls. How do I correct this?
Seems a little overkill. You could just compare COUNT(col) and COUNT(*).