Alter Table Drop Column

Dropping a column is a fairly straightforward operation as long as no dependencies depend on the column. The following code tested on SQL 2008 returns back almost instantly even when tested against tables with over 1 million rows. It seems the drop column statement performs similar to a truncate statement in that it simply moves a pointer and does not actually delete the data from the pages. The syntax is simple, we can pass in a comma delimited list of columns to drop from the table. [cc lang=”sql”] ALTER TABLE ##my_tables DROP COLUMN first_name, last_name [/cc] For the first example, we’ll create a table and then drop one of it’s columns. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( first_name VARCHAR(50), last_name VARCHAR(50), ssn VARCHAR(9) CONSTRAINT ssn_unique UNIQUE ) ALTER TABLE ##meme DROP COLUMN first_name [/cc] That was easy, it simply dropped the column. It won’t always happen that way however. If we have a constraint, index, or key that depends on the column, we need to drop or disable that first. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( first_name VARCHAR(50), last_name VARCHAR(50), ssn VARCHAR(9) CONSTRAINT ssn_unique UNIQUE ) ALTER TABLE ##meme DROP COLUMN first_name, ssn [/cc] Here we get the error: So we need to drop the constraint in this case prior to dropping the column. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( […] Continue reading ...

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 […]

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 ...

CREATE SCHEMA Syntax

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.

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 ...

Featured Articles

 Site Author

  • Thanks for visiting!