Good Question. I prefer XQuery. It works well when the data already resides in a table, and it gives great error messages with repect to syntax when that data is schema bound. Furthermore, I am able to use a Cross Apply is which has become increasing...

<p>As a developer and consultant for business applications, I avoid column nullability like the plague, i.e. 'nothing' is rarely a viable attribute value.  I much rather address the potential of 'nothing' via schema design and normalization.  ...

2-4 GB AWE not Enabled 64 GB AWE Enabled Good information at: [http://msdn.microsoft.com/en-us/library/ms187499.aspx][1] [1]: http://msdn.microsoft.com/en-us/library/ms187499.aspx...

RBAR is the (simple) answer. The query optimizer is forced to analyze every row. The following code returns then same result set, but maintains two very different execution plans. When a simple scalar function is used in the 'WHERE' clause, the would-be...

As a simple follow up to my last post, the below statements are written to actually throw an error. So you can see, the 'update' statement is not commited should there be an issue with the 'output' / 'into' clause, i.e. the next key (prior to the +1) i...

There was a question very similar to this earlier in the quiz. I would make the same recommendation: use a surrogate table and composable DML. Something similar to the below should work: use tempdb go set nocount on go ...

The performance gap is primarily in the (re)compilcation of the execution plan itself. Should the query have an exitsiting plan cached, performance will prevail. You can take a look any given cached plans with a query similar to the following: ...

The first query treats the misspelling as a table alias, i.e. the code can be rewritten as follows: SELECT UNOIN.Dates FROM @t UNOIN SELECT Dates FROM @t1 Since it is treating the misspelling as an alias, no union really exists and t...

Small tables utilize mixed extents; per MS at [http://msdn.microsoft.com/en-us/library/ms190969.aspx:][1] To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data. SQL Server has two type...