Auditing, conflict resolution, tamper & concurrency protection are some of the most common requirements for any enterprise system – this 2-part series presents an in-depth look at the various change detection mechanisms available within SQL Server.  Read more...

This article describes a T-SQL procedure to insert data using the SSIS Data Pump  Read more...

There are certain circumstances where SQL Server will silently truncate data, without providing an error or warning, before it is inserted into a table. In this tip we cover some examples of when this occurs.  Read more...

Despite NTEXT and TEXT being deprecated in SQL Server for some time they are still both used in production systems. In this article, I will briefly demonstrate the difference between a VARCHAR (MAX), NVARCHAR (MAX) and the NTEXT data types, and the impact on performance from using NTEXT/TEXT.  Read more...

The final level of this stairway looks at the MERGE statement in detail, focusing on how to perform insert, update and delete logic using the MERGE statement.  Read more...

Following on from the previous TSQL performance tuning article in this series, we now focus on the important topic of T-SQL “Not IN” And “Not Exists “  Read more...

This article shows how you can use a TSQL semaphore in SSIS.  Read more...

A UDF is very convenient for centralising business logic as we can specify a set of business logic in one UDF which references multiple stored procedures and ad-hoc queries. However, they can lead to significant performance degradation due to their demands on the CPU  Read more...

I saw your recent tip on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my application. My users and me have a difference of opinion on some of the calculations in our reporting applications. All of the code is in T-SQL, but I think the reporting issues are related to data types and rounding down or rounding up rules. Do you have any insight into these issues? I would like to see some examples with a variety of coding options.  Read more...

One of the common problems is dealing with apostrophes in T-SQL. This article examines the challenges of single quotation marks and ends with a short quiz.  Read more...

This article will show you how to automatically generate insert, update, and delete procedures for tables in your database  Read more...

The Tally Table has proven to be a simple and elegant method for avoiding many varieties of RBAR. Unfortunately, one of its more common uses, that of a CSV splitter, has a well-known and serious performance problem. MVP Jeff Moden shows us what that problem is and how to correct it. (UPDATED with additional info and attachments on 5/12/2011).  Read more...

Here's a great method for managing ETL for data marts and small data warehouses with just T-SQL.  Read more...

A suggested design for creating flight schedules makes querying easy. Includes basic airport and airlines data. Get started now with this tricky query problem.  Read more...

Learn how yo can use a SQL-CLR procedure to handle all your PIVOT queries.  Read more...

How to reset an identity field to the next contiguous value, after deletions.  Read more...

Splitting strings based on patterns supported by LIKE and PATINDEX can be an interesting way to address a wide variety of problems.  Read more...

One of the most common T-SQL questions is on returning a ranked result set for each member of a group. Dave Ballentyne brings us a short tutorial on how you can do this in T-SQL.  Read more...

This article explains how to use the GROUPING clause to generate subtotals for rows in a very easy fashion.  Read more...

In this sequel to his first "Hierarchies on Steroids" article, SQL Server MVP Jeff Moden shows us how to build a pre-aggregated table that will answer most of the questions that you could ask of a typical hierarchy. Any bets on whether Santa is packin’ a Tally Table in his bag or not?  Read more...