Match identity columns after INSERT

One common requirement in SQL when inserting data between tables is to match the new identity column with the old identity column. The most common solution to this problem is to perform a cursor and do the inserts one at a time. While this may work, it is not very efficient because set based operations are the bread and butter of SQL. Anytime looping comes into play, excessive resource consumption also occurs. There are a couple solutions to this, both use the OUTPUT clause introduced in SQL 2005. The first solution uses an intermediary reference table to hold the old and new values. This is done by outputting the inserted identity columns while sorting the source identity columns. You then join back on the source identity columns to get both old and newly created identities. This solution was created by my good SQL developer friend Mike Newell. Method 1 [cc lang=”sql”] — Drop temp tables if they already exist IF OBJECT_ID(‘TempDB..#source’, ‘U’) IS NOT NULL DROP TABLE #source; IF OBJECT_ID(‘TempDB..#target’, ‘U’) IS NOT NULL DROP TABLE #target; IF OBJECT_ID(‘TempDB..#xref’, ‘U’) IS NOT NULL DROP TABLE #xref; GO — Create the temp tables CREATE TABLE #source (id INT PRIMARY KEY IDENTITY(1, 1), data INT); CREATE TABLE #target (id INT PRIMARY KEY IDENTITY(1, 1), data INT); CREATE TABLE #xref (row INT PRIMARY KEY IDENTITY(1, 1), source_id INT, target_id INT); GO — If xref table is being reused, make sure to clear data and reseed by truncating. TRUNCATE TABLE #xref; — Fill source […]

Continue reading ...

Using the OUTPUT Clause in SQL Server

The OUTPUT clause in SQL Server 2008 was probably one of the most functional T-SQL enhancements added. I personally don’t use it enough because I often forget about it, however I have used it to overcome some serious deadlock incidents. It basically works in conjunction with INSERT, UPDATE, or DELETE. In my opinion it will probably be most utilized in an update statement, however I’m sure there are many scenarios I may be forgetting. The way it works is while performing one of these statements, you have the option to output any data within the rows that are being INSERTED UPDATED or DELETED — into another table. This is important because it allows only a single pass through on the table. Whereas before when performing an update to a table, in order to get in-row data you would have to perform a separate select. This could especially become a problem if the update and select needed to be within a transaction. But the output clause is transactional by nature because it occurs within a single statement. Let’s look at an example: [cc lang=”sql”] DECLARE @customer_id int = 1234; DECLARE @customer table ( first_name varchar(50), last_name varchar(50), phone_number varchar(50), visit_count int; ); UPDATE c SET customer_visit_count += 1 OUTPUT DELETED.first_name, DELETED.last_name, DELETED.phone_number, INSERTED.customer_visit_count INTO @customer FROM [customer] c WHERE c.customer_id = @customer_id; SELECT first_name, last_name, phone_number, visit_count FROM @customer; [/cc] This allows a single query (and one transaction) to be used in order to update and select from a table. The […]

Continue reading ...


Schemas are a concept that was introduced in SQL Server 2005 that replaced object owners. Schemas are methods used to abstract objects into separate categories in order to simplify permissions and help categorization and organization. To create a schema, simply do the following: [cc lang=”sql”] CREATE SCHEMA app AUTHORIZATION dbo [/cc] The app represents the name of the schema and the dbo represents the owner of the schema. Users, groups, or roles can be specified as owners. I personally like to use schemas in order to automatically grant permissions to a group or user. All you have to do is assign specific permissions a user has to a schema then any object you create under that schema, will allow the user that particular permission. This is better than explicitly specifying permissions to every object created.

Continue reading ...

User Defined Functions and Performance

There is definitely a lack of awareness in the SQL world regarding the use of user defined functions and the potential performance hit they can have when using within your queries. Don’t get me wrong, I would love nothing more than to be able to centralize my commonly used code into functions for reuse. In a lot of cases this is possible, however there are specific cases where this can cause a huge performance impact. The Problem The one thing we need to be aware of with SQL is that its efficiency lies in the fact that it deals with data in SETS. Meaning that its power does not come in performing row-by-row operations, rather it wants to retrieve chunks of data and manipulate them as recordsets. Keeping this in mind, you can look out for scenarios where certain operations will cause more of a row-by-row operation and therefore impact performance. The most common no no, is the use of scalar functions within a set based operation. It seems (but I can’t prove) that SQL 2008 has actually made some great strides in being able to deal with these situations, however there will always be a negative impact. First, let’s look at a common scenario. The Test First, let’s deploy this scalar user defined function which calculates the End of month for a given date: [cc lang=”sql”] CREATE FUNCTION [dbo].[ufn_GetLastDayOfMonth] ( @pInputDate DATETIME ) RETURNS DATETIME BEGIN DECLARE @vOutputDate DATETIME SET @vOutputDate = CAST(YEAR(@pInputDate) AS VARCHAR(4)) + ‘/’ + CAST(MONTH(@pInputDate) […]

Continue reading ...

Increase Job History

The SQL Server Agent by default only keeps 1000 rows of history for all jobs. There is also a limitation for each job to keep only 100 rows each. These rows do not only include the a row for the entire job, but the rows for each step in the job also. These rows can quickly get filled leaving you in the dark when you need to troubleshoot what had happened in a job. To change these values, you need to have sysadmin access to the SQL Server Agent. Within the object explorer, right click on the SQL Server Agent node, and choose properties. A new pop-up window will appear displaying the SQL Server Agent properties. Select the History node and the history settings are displayed: Here you have the option to not limit the agent job history, by unchecking the corresponding box. You can also choose the maximum # of rows to store for all jobs, and an option to limit each job. The last option is to Remove history that’s older than a specified time period. This would be recommended if you do not limit the history log size, since you will not be pruning any records otherwise.

Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!