The need to join tables in order to determine which records to delete is a common requirement. The syntax can be somewhat tricky because you need to use an alias for the table you want to delete from. First, here is a simple example: [cc lang=”sql”] DELETE o FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ [/cc] The following query will delete all the order records from the customers who have the name alexandria jones. This will only delete records from the orders table, however I strongly suggest you perform a SELECT statement prior to actually performing the delete using the same query as such: [cc lang=”sql”] –DELETE o SELECT o.* FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ [/cc] This will show you the exact records you are about to delete. Once you get the count, I actually suggest you take one additional safety measure to ensure you don’t delete more records by using the TOP clause: [cc lang=”sql”] –DELETE o SELECT count(o.*) FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ — if the above query returns 5 records, set 5 below DELETE TOP (5) o FROM #orders o JOIN #customers c ON c.customer_id = o.customer_id WHERE c.last_name = ‘jones’ AND c.first_name = ‘alexandria’ [/cc] Once again.. test, test, test before you delete!
Continue reading ...
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 ...
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 ...
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 ...
