In retrospect, it was probably the inclusion of the OUTPUT clause in the MERGE statement that gave SQL Server 2008 its most powerful SQL enhancement.. It isn't the easiest of features to explain, but Bob does it in his usual clear and careful way.… Read more
To make progress as a relational Database programmer, you have to think in terms of sets, rather than lists, arrays or sequential data. Until that point, you'll feel the need to pass lists, arrays and the like to functions and procedures. Joe suggests some unusual ways around the difficulty and broods on the power of the VALUES constructor.… Read more
If you ever feel uneasy when you set about handcrafting database maintenance jobs for SQL Server, it may be the voice of your conscience saying 'Ola, Ola!'. Follow your conscience. Ola Hallengren has already crafted a superb set of routines to do this for you. Why reinvent something that does it all, safely and well. Brad McGehee explains....… Read more
If you are not keen on repetitive typing, you can still rapidly produce production-quality documented code by planning ahead and using Extended properties, and system views. Phil Factor explains, with some Scary SQL… Read more
When you have to routinely collect data from Performance Monitor Counters, it soon becomes easier and more convenient to use PowerShell. SQL Server MVP Laerte Junior was inspired to create a script, and guides us through its useful functions.… Read more
The Row Count Spool operator is a simple component in an execution plan that can make an enormous difference to SQL query plans that would otherwise read a table on every row of the inner input to a nested loop. As this is a common type of query, it illustrates the power that a knowledge of your showplan operators can give you.… Read more
If you've ever wondered what happens to your queries if you're not conscientious about creating indexes, then Fabiano Amorim has yet another nugget of Execution Plan wisdom for you. The Query Optimizer's solution is to use the Nonclustered Index Spool, and we're about to learn why.… Read more
We soon learn, in SQL Server, that heaps are a bad thing, without necessarily understanding how or why. Jonathan Lewis is an Oracle expert who doesn't like to take such strictures for granted, especially when they don't apply to Oracle. Jonathan discovers much about how SQL Server places data, and concludes from his experiments that heaps perform badly in SQL Server because you cannot specify a fill factor for them.… Read more
A number of hierarchies and networks are most convenently modelled as binary trees. So what is the best way of representing them in SQL? Joe discards the Nested Set solution in favour of surprisingly efficient solution based on the Binary Heap.… Read more
You can create a database under the assumption that SQL looks after all the problems of concurrency. It will probably work fine under test conditions: Then, in the production environment, it starts losing data in subtle ways that defy repetition. It is every Database Developer's nightmare. In an excerpt from his acclaimed book, Alex explains why it happens, and how you can avoid such problems. … Read more
SQL Server's UPDATE statement is apparently simple, but complications such as the FROM clause can cause puzzlement. Bob Sheldon starts simply, and introduces the more complex forms painlessly. … Read more
The 14 chapters of "The Art of XSD", written by MVP Jacob Sebastian, will take the reader step-by-step all the way from the basics of XML Schema design all the way to advanced topics on SQL Server XML Schema Collections. Reviewer Hima Bindu Vejella gives it an 8/10 rating, and gives us an excellent distilled description of what the book has to offer.… Read more
It distils a great deal of practical experience; the writing of it was a considerable task; It packs in a great deal of information. Alex's book shows how to write robust database applications, and we can all learn from it. We took the book to a critic who never minces his words, and were relieved to find that Joe Celko liked it. … Read more
Continuing to illuminate the depths of SQL Server's Query Optimizer, Fabiano shines a light on the sixth major Showplan Operator on his list: the Lazy Spool. What does the Lazy Spool do that's so special, how does the Query Optimizer use it, and why is it so Lazy? Fabiano explains all...… Read more
Sometimes, a procedural database process cannot easily be reduced to a set-based algorithm in order to reduce the time it takes. Then, you have to find other ways to parallelise it. Other ways? Josef shows how to use SSIS to drastically reduce the time that such a process takes.… Read more
To learn PowerShell, Laerte suggests that you just start using it. To encourage you to start, he provides a series of tips on using PowerShell with SQL Server to solve various everyday problems. With a little patience, a good IDE, and a bit of help and advice, "the lion is dead".… Read more
A well-known Oracle expert records faithfully his struggles with the unfamiliar : SQL Server. He now sets himself the task of creating a table with a million rows of random data. As one might expect, it is the lack of familiarity with the workarounds and tricks of SQL Server that trips him up. His journey brings us fresh insights, and a glimpse at the alternative-universe of Oracle.… Read more
The SQL Speed Freak Challenge is a no-holds-barred competition to find the fastest way in SQL Server to perform a real-life database task. It is the programming equivalent of drag racing, but without the commentary box. Kathi has stepped in to explain what happened with the second challenge and why some SQL ran faster than others.… Read more
One of the most interesting additions to SQL Server syntax in SQL Server 2005 was the APPLY operator. It allows several queries that were previously impossible. It is surprisingly difficult to find a simple explanation of what APPLY actually does. Rob Sheldon is the specialist in simple explanations, so we asked him.… Read more
You know how to set up a SQL Server instance, but it is great to have a list of all the necessary checks and procedures: even better is to have the link to in-depth explanations for every stage by the renowned SQL Server Expert Buck Woody.… Read more
