SQL Server MVP Jeff Moden shows us a new very high performance method to convert an "Adjacency List" to “Nested Sets” on a million node hierarchy in less than a minute and 100,000 nodes in just seconds. Not surprisingly, the "steroids" come in a bottle labeled "Tally Table".  Read more...

This article shows a clean non-looping method to parse comma separated values from a parameter passed to a stored procedure.  Read more...

A quick solution is a table function that will create a date range based on the parameters given. Then, you can just join to the results.  Read more...

Unless you are working on a reporting-only application you will probably need to update tables in your SQL Server database. To update rows in a table you use the UPDATE statement. In this level we will be discussing how to find and update records in your database, and discuss the pitfalls you might run into when using the UPDATE statement.   Read more...

Relational Datbases have tables as data structures, not arrays. This makes it tricky and slow to do matrix operations, but it doesn't mean it is impossible to do. Joe gives the Celko Slant on how to go about doing Matrix Math in SQL.  Read more...

In this article, we provide a simple way to get a comma delimited list from a table of entries without having to use a CURSOR or a WHILE loop to read through the table.  Read more...

You may have data in a database that was inserted into a table by mistake, or you may have data in your tables that is no longer of value. In either case, when you have unwanted data in a table you need a way to remove it. The DELETE statement can be used to eliminate data in a table that is no longer needed. In this article you will see the different ways to use the DELETE statement to identify and remove unwanted data from your SQL Server tables.  Read more...

Learn how an outer join works and how you can use it in your applications to find the results you need when matching data isn't in all your tables.  Read more...

This article from Todd Fifield shows a Way to avoid the dreaded LIKE operator in your queries and dramatically speed up keyword searches.  Read more...

Not all applications are limited to only retrieving data from a database. Your application might need to insert, update or delete data as well. In this article, I will be discussing various ways to insert data into a table using an INSERT statement.  Read more...

There are seven common antipatterns in T-SQL coding that make code perform badly, and three good habits which will generally ensure that your code runs fast. If you learn nothing else from this list of great advice from Grant, just keep in mind that you should 'write for the optimizer'.  Read more...

In this article I will be expanding on my discussion of the GROUP BY clause by exploring the ROLLUP, CUBE and GROUPING SETS operators. These additional GROUP BY operators make it is easy to have SQL Server create subtotals, grand totals, a superset of subtotals, as well as multiple aggregate groupings in a single SELECT statement.  Read more...

In a followup to his first article on Hierarchies, Gus Gwynn takes a look at the performance of a few different methods of querying a hierarchy. Learn how the HierarchyID stacks up.  Read more...

Replacing multiple spaces with a single space is an old problem that people use loops, functions, and/or Tally tables for. Here's a set based method from MVP Jeff Moden.  Read more...

Referential integrity is a very important thing to consider when designing a database. In my years as a DBA I've seen database designs that sit on both ends of the spectrum, none at all and cases where every table is linked to multiple tables. While the later certainly can be a little more difficult to work with it ensures the integrity of your data stays intact. The other end provides much more flexibility when it comes to updating and deleting data from your database, whether it's being done through the application or directly on the backend, but has the issue of possible orphan records if things are not done properly. This tip will look at the DELETE CASCADE option when creating foreign key constraints and how it helps keep the referential integrity of your database intact.  Read more...

It's a well known fact that Scalar UDFs are the stuff of performance nightmares in T-SQL. But are they really as bad as they say? SQL Server MVP Jeff Moden shows us that they might not really be as big a problem as you might think and what you can do when they actually are.  Read more...

So you want to group your data for charting in Excel? Here's how, the easy way!  Read more...

Recursive CTEs can be confusing and scary, so examining some non-standard examples may cast light upon these shadowy demons.  Read more...

Be sure to understand the consequences of modifying underlying tables and the affects they can have on Views  Read more...

This article will show some common integer data-type conversions using T-SQL.  Read more...