Reputation
2,534
Top tag
Next privilege 3,000 Rep.
Cast close & reopen votes
Badges
9 28
Newest
 Enlightened
Impact
~106k people reached

Jul
10
revised Optimize a SQL query
added a reference
Jul
10
comment No advantage of using Cross Apply or CTE over inline sub-query
In a more general case, your inline queries will give you only one result per row, while joining to a CTE (which doesn't have to be a CTE, it could be a normal subselect) or CROSS APPLYing a row set can give you access to more than one column.
Jul
10
revised How to break down RELATIONAL data into HIERARCHY data structure?
added 217 characters in body
Jul
10
answered How to break down RELATIONAL data into HIERARCHY data structure?
Jul
10
comment Why should a key be made explicit?
They are not needed for your database to work but they are needed for your data to "work" i.e. be consistent, because that is exactly how you are telling your database server to keep the information consistent.
Jul
9
comment how to set “result to text” with a tsql statement in sp?
This would be a rough equivalent of Results to File rather than Results to Text, though.
Jul
9
awarded  Custodian
Jul
9
comment joining tables with full outer join and… COALESCE?
This is how I would probably do it too. No need to nest COALESCE like that, however – just specify all the arguments in a single call: coalesce(a.c1, a.c2, a.c3, a.c4, a.c5) is not null would work just as well. Using a subquery in general would make sense but in this case seems unnecessary. This is because the c1, c2 etc. aliases are assigned to columns rather than expressions, and so you could apply COALESCE to the original column names and thus avoid the subselect.
Jul
9
reviewed No Action Needed joining tables with full outer join and… COALESCE?
Jul
8
comment Grouping Data Using Joined Tables with Certain Distinct Values
There seems to be either a typo or a missing word or phrase (can decipher what it should be in either case) in the second bullet point: "...so that a proposed (?) can be checked."
Jul
8
answered Grouping Data Using Joined Tables with Certain Distinct Values
Jul
8
comment Grouping Data Using Joined Tables with Certain Distinct Values
You have StoreID in orderLines, rather than in orders, as if the same order could be shared by several stores. But if an order does indeed have various StoreID associated with its order lines, how can you select the same order total and the same payment total for each store? Or how would you divide the totals among the stores that come from the same order? Something is missing.
Jul
7
revised Optimize a SQL query
a small correction
Jul
7
revised Unexpected SELECT permissions
removed redundant empty lines from the code
Jul
7
comment SET ANSI_NULLS at the database level versus setting it at the table level
And you haven't addressed the Why bit (why i got the ANSI_NULLs set to off at the database level).
Jul
7
comment SET ANSI_NULLS at the database level versus setting it at the table level
This part may be confusing for readers: When you dont specify ANSI_NULL_DEFAULT in CREATE TABLE or ALTER TABLE statements… You probably meant to say something like When you don't specify the nullability of a column in CREATE TABLE or ALTER TABLE statements…
Jul
7
comment Need some assistance on MySQL Sub query
You could also correlate the subquery with the main query by changing its condition to WHERE call_type=d.call_type. Not sure if that would have any implications on performance, but at least you would avoid the possible mistake of changing the argument ('SALES') in one place and forgetting to do that in another.
Jul
7
revised Need some assistance on MySQL Sub query
added line breaks to a code snippet for readability
Jul
6
answered Optimize a SQL query
Jul
6
comment Are junction tables a good practice?
@Namphibian: You mean even if I'm retrieving only the indexed columns from the table, MySQL will still have to touch the table? Is that how non-clustered indices work in MySQL?