The INSERT statement in SQL Server is versatile. It now allows the insertion of multiple rows of literal values. It also provides the output clause that solves a number of common problems such as ascertaining the value of identity fields, and other calculated fields in tables, or for auditing the rows that are added to tables. Robert, once again, gives a clear introduction. … Read more
When the SQL MERGE statement was introduced in SQL Server 2008, it allowed database programmers to replace reams of messy code with something quick, simple and maintainable. The MERGE syntax just takes a bit of explaining, and Rob Sheldon is, as always, on hand to explain with plenty of examples.… Read more
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
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
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
The SQL Server common table expression was introduced into standard T-SQL to simplify queries for which a derived table just wasn't suitable. Robert Sheldon explains CTEs in this article.… Read more
When they use SQL Server, one the commoner questions that Ms Access programmers ask is 'Where's the TRANSFORM/PIVOT command? So how do you swap colums and rows in an aggregate table? Do you really need to use a CLR routine for this?… Read more
Even though there are many other ways to get data into a database, nothing works quite as fast as BCP, once it is set up with the right parameters and format file. Despite its usefulness, the art of using the command-line utility has always seemed more magic than method; but now along comes Robert Sheldon to shed light on the murky details.… Read more
Anith follows up his highly successful article Facts and Fallacies about First Normal Form with a fascinating discussion of five common database design errors which persist in spite of the fact that the unfortunate consequences of their use is so widely known. It is a needy reminder to anyone who has to design databases.… Read more
It is not always easy to spot "antipatterns" in your SQL, especially in more complex queries. In this article, Plamen demonstrates some of the most common SQL coding errors that he encounters, explains their root cause, and illustrates potential solutions.… Read more
Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational 'change tables' rather than in an esoteric chopped salad of XML. These change tables contain columns that reflect the column structure of the source table you have chosen to track, along with the metadata needed to understand the changes that have been made. Pinal Dave explains all, with plenty of examples in a simple introduction.… Read more
The FOR XML clause in SQL Server causes a lot of difficulty, mainly because it is rather poorly explained in Books-on-Line. We challenged Bob Sheldon to make it seem simple. Here is his sublime response. … Read more
Sometimes, in SQL, it is the routine operations that turn out to be the trickiest for a DBA or developer. The cleaning up, or de-duplication, of data is one of those. András runs through a whole range of methods and tricks, and ends with a a fascinating technique using CTE, ROW_NUMBER() and DELETE… Read more
Phil returns to the old subject of 'Getting text-based data in and out of SQL Server'. He shows various easy ways of getting a file listings of directories from the file system, shows how one can access the Shell automation Objects, and demonstrates several ways of reading or writing data between database and f… Read more
Microsoft has quietly been improving full-text indexing in SQL Server. It is time to take a good look at what it offers. Who better to give us that look than Robert Sheldon, in the first of a series.… Read more
Given the fundamental importance of indexes in databases, it always comes as a surprise how often the proper design of indexes is neglected. It often turns out that the programmer understands detail, but not the broad picture of what indexes do. Bob Sheldon comes to the rescue with a simple guide that serves either to remind or educate us all!… Read more
It is an interesting problem in Transact SQL, for which there are a number of solutions and considerable debate. How do you go about producing a summary result in which a distinguishing column from each row in each particular category is listed in a 'aggregate' column? A simple, and intuitive way of displaying data is surprisingly difficult to achieve. Anith Sen gives a summary of different ways, and offers words of caution over the one you choose.… Read more
When Nigel Rivett takes us on a tour of the apparently innocuous subject of Identity Columns in TSQL, even the seasoned programmer is due for one or two surprises.… Read more
SQL Server provides several "standard" techniques by which to read and write to files but, just occasionally, they aren't quite up to the task at hand - especially when dealing with large strings or relatively unstructured data. Phil Factor provides some T-SQL stored procedures, based on use of the FileSystem Object (FSO), that may just get you out of a tight corner...… Read more
For those times when you absolutely, positively got to perform a cross tab query in SQL, Keith Fletcher's T-SQL stored procedure will allow you to do it "on the fly". You can add it to your database and start cross tabbing immediately, without any further setup or changes to your SQL code. Check it out, and then take the cross tab challenge. If you can compile a cross tab report that displays the order value by customer, by quarter, using the stored procedure, you may win a much-coveted prize!… Read more
