SQL Server User-Defined Functions are good to use in most circumstances, but there just a few questions that rarely get asked on the forums. It is a shame, because the answers to them tend to clear up some ingrained misconceptions about functions that can lead to problems, particularly with locking and performanc… Read more
Sometimes, XML seems a bewildering convention that offers solutions to problems that the average database user doesn't have. To make things worse, XML experts generally lack the wherewithal to provide simple answers to even the simplest questions. Rob Sheldon, in contrast, can answer even those questions we somehow feel silly asking in public, and think twice about doing so.… Read more
Relational division is used in SQL to select rows that conform to a number of different criteria. It is a neglected but effective technique for a number of tasks. Although the SQL can appear daunting and complex, it can perform very well if you reduce the rows as much as possible before applying the final logic. Dwain Camps explains how, and shows the performance gains.… Read more
Views are a valuable tool for the SQL Server Developer, because they hide complexity and allow for a readable style of SQL expression. They aren't there for reasons of performance, and so indexed views are designed to remedy this shortcoming. They're great in certain circumstances but they represent a trade-off, and they come with considerable 'small print'. Jes Borland explains.… Read more
The SEQUENCE statement introduced in SQL Server 2012 brings the ANSI SQL 2003 standard method of generating IDs. This is a great relief to database professionals as it solves some problems what are awkward to solve with the IDENTITY property. Joe Celko explains the basics of using a SEQUENCE… Read more
Every database developer uses keys, but without always understanding all the ramifications. They come with few hard and fast rules, but if you get them right from the start with a database design, the whole process of database development is simpler, and the result is likely to perform better. We asked Phil for advice, little knowing that the explanation might take a while.… Read more
SQL Server isn't usually the best place to format dates or currency as strings. It can be a complex task to conform correctly with national and cultural conventions. Just occasionally, though, you need to do it. This is easy in SQL Server 2012, but if you aren't using that, what do you do?… Read more
Seldom has a SQL Server Service pack had such an effect on database development as when SQL Server 2012 SP1 introduced selective XML indexes. These transform the practicality of querying large amounts of XML data. Seth Delconte demonstrates how and why this feature makes all the difference.… Read more
SQL Server's Query Optimiser relies on statistics for providing a decent query plan. If the statistics are wrong, outdated or missing then you have little realistic hope of having queries that perform well. It is therefore important to appreciate how SQL Server maintains distribution statistics.… Read more
SQL Views are essential for the database developer. However, it is common to see them misused, or neglected. Joe Celko tackles an introduction to the subject, but there is something about the topic that makes it likely that even the experienced developer will find out something new from reading it.… Read more
A SQL expression may look elegant on the page but it is only valuable if its performance is good. Do window functions run quicker than their conventional equivalent code? Fabiano gives a definitive answer.… Read more
XML data can become a full participant in a SQL Server Database, and can be used in views, functions, check constraints, computed columns and defaults. Views and table-valued functions can be used to provide a tabular view of XML data that can be used in SQL Expressions. Robert Sheldon explains how. … Read more
The XML Data type has definite uses, but the way of interrogating, retrieving, and manipulating the values of properties and attributes within XML have been so foreign to the SQL language as to be somewhat of a barrier to their use. Fortunately, Robert Sheldon has once more managed to make the subject accessible to those of us who just need to get the job done.… Read more
Fabiano continues his introduction to SQL Server's implementation of the window functions by giving a history of what got implemented and when, and explaining the concept of the 'Frame' in a window function, with plenty of examples.… Read more
The XML data type, introduced in SQL Server 2005, is a powerful construct. When used wisely, it can provide useful extensions to SQL Server. Robert Sheldon, in the first part of a series, describes how create and index a typed XML column in a table, and discusses when you should consider using an XML Datatype.… Read more
When SQL Server introduced Window Functions in SQL Server 2005, it was done in a rather tentative way, with only a handful of functions being introduced. This was frustrating, as they remove the last excuse for cursor-based operations by providing aggregations over a partition of the result set, and imposing an ordered sequence over a partition. Now, with SQL Server 2012, we are soon to enjoy a full range of Window Functions. They are going to make for some much simpler SQL queries.… Read more
If you suffer from runaway queries, if you have several database applications with unpredictable fluctuation in workload, or if you need to ensure that workloads get the memory or CPU they need according to certain priorities, then you need Resource Governer, and you need Roy Ernest's clear explanation of the technology.… Read more
SQL is a powerful tool for querying data, and for aggregating it. However, you can't easily use it to draw inferences, to make predictions, or to tease out subtle correlations. To provide ever more sophisticated inferences to businesses, the race is on to combine the power of the relational model with advanced statistical packages. Both IBM and PostGres are ready with solutions. And SQL Server? Hmm...… Read more
As a DBA and hiring manager, Chris Shaw has been on those sides of the recruitement process. As an MVP and active member of the SQL Server community, he knows what resources are available to help DBAs hone their abilities. Who better to guide you through the many paths to developing your DBA skillset?… Read more
Subqueries and derived tables can add great versatility to SQL statements, cut down complexity, but can occasionally be a curse when their effect on performance is poorly understood. Surely everyone understands the various types of subqueries and how they are used? If you felt a twinge of doubt, here is Rob Sheldon's easy guide to the subject.
… Read more
