236 reputation
15
bio website
location
age
visits member for 3 years, 3 months
seen Dec 3 at 23:05

Feb
13
awarded  Nice Answer
Feb
1
comment What is the correct result for this query?
@ErwinSmout: Of course not. However this falls within fair use under US copyright law. Relatively small portions, quoted in the context of analysis (i.e. criticism) of the work, for educational purposes, with negligible impact on the work's ability to be sold.
Jan
31
awarded  Yearling
Jan
31
awarded  Editor
Jan
31
comment What is the correct result for this query?
@ypercube actually the phrase that mentions cardinality is for non-grouped rows. You want 1.b.ii which says "where M is the number of groups in T." I've trimmed down 1.b.i to hide irrelevant detail.
Jan
31
revised What is the correct result for this query?
Removed details of irrelevant case
Jan
30
comment What is the correct result for this query?
@ypercube: +1 from me too. I decided to take the extra time to pull from the spec to prove that there was no weasel words hidden someplace that would make your answer wrong. But once I did that, I might as well post it as a full answer. So I did.
Jan
30
awarded  Supporter
Jan
30
awarded  Commentator
Jan
30
comment What is the correct result for this query?
Part 11, and Part 14. Parts 3,9,10,and 13 Were not updated in 2011, and thus their previous versions apply. There is no part 12. Similarly there are no parts 5-8. See the Wikipedia page for Sql:2011 or Part 1 itself for an explanation of what each part contains.
Jan
30
comment What is the correct result for this query?
Technically I used the Final Draft International Standard, rather than the standard itself. Per ISO/IEC rules only editorial (non-technical) changes are permitted between FDIS and the final standard. The standard is spit up into multiple parts. Part 1, Part 2, Part 4 ...
Jan
29
awarded  Teacher
Jan
29
answered What is the correct result for this query?
Jan
18
comment Closed loops in database model, does it affect the performance?
One possible case where one could have a legitimate circular dependency would be if modeling some form of heirarcy where the each level alternates between two types. Examples are scarce, but one would be an "Alternating decision tree" (a machine learning data structure). The dependency could be removed by using a self-referencing tables with a single or multi-table "inheritance" design, but that would require additional constraints to enforce the alternating aspect, while the circular reference style inheirently enforces it.
Apr
23
comment As a DBA, how would I go about transitioning from Oracle to SQL Server?
Also note that stored procedures whose name begins with "sp_" are treated specially. Don't name your stored procudes like that, unless you are backporting a system procedure from a newer version of SQL server. You can technically utilize this to create database wide stored procedures, but that is not recomended, because future versions may create a new system stored procedure with the same name.
Mar
23
comment Where should you define foreign keys?
SQL Server exclusivly locks and marks the parent row as deleted (LOP_DELETE_ROWS/LCX_MARK_AS_GHOST). At this point any updates/insets that would reference the deleted parent will stall in the FK check stage (and would be rolled back if the delete transaction succeeds). It then does the equivelent of Delete from children where parentid=____. Since no new rows referencing the parent row can be added, it does not need to lock any rows other than those it is deleting. Deleteing them bottom up would be no better, unless you used multiple top level transactions.
Mar
23
comment Where should you define foreign keys?
@Adam: I'm just trying to point out that Oracle is not a good database on which to base generalizations about most SQL databases. Orcale's database is excellent as a data store, especially considering its scalability features, but its relational engine leaves a lot to be desired, and thus some very useful features are best avoided in Oracle. That does not make it a bad database if its advantages outweigh the disadvantages for your specific application.
Mar
22
comment Where should you define foreign keys?
@Adam: Oracle is a rather crappy SQL Database in many respects. This is apparently one of them. Most other SQL databases will convert a cascading delete into the equivlent of one delete statement per impacted table (assuming no cycles, and that the cascades do not need to delete from the same table twice for different forign key constraints). I just verified that on Microsoft Sql Server.
Mar
6
comment Common Table Expression (CTE) benefits?
Just for reporting queries? The systems I work on every day have transaction queries that are that complicated. Oddly our reporting queries are often some of our simplier ones. (Exlcuding trivial joinless CRUD queries of course).
Sep
26
comment Why shouldn't we allow NULLs?
@Aaron, your points are one of the main reasons few people do use that design. SQL syntax for joins is not nearly as convient as it could be, unless of course you name your columns such that "normal" joins can always be accomplished via natural joins. (Of course, unusual cases may always require explicit joins.) The other main reason people don't often do this is obviously performance in the many join case.