Last time, we discussed three methods for inserting data into a destination table that has a recursive relationship. In this article we’re going to cover how to insert data into that table without vi0lating the foreign key constraint by using a WHILE EXISTS loop. If you want to follow along with the examples, you’re going…
Loading Hierarchical Data
Earlier this week the question came up on how can we insert data into a table that has a hierarchical data structure. If you’re not familiar with a hierarchical data structures, here’s a quick explanation. Consider the staging table to the right. This table is used to load new accounts into our system. Each account…
Improve Performance by Replacing Cursors
This is the last article for my upcoming design patterns session. Today we’re going to look into speeding up your queries by replacing cursors, a row-by-row operation with a table value function. Before you start composing that hate-mail on how I’m wrong for not understanding cursors, let me say this: there is a time and…
Upserts
Up to this point in our design patterns sessions we’ve concentrated on removing duplicates from our troubled table. Now, let’s look into preventing those duplicates from being inserted in the first place. If you haven’t heard the phrase upsert before, it’s the idea of doing an INSERT and UPDATE in the same statement, that way…
De-duping by CTE
Ok, today we wrap up our design patterns for removing duplicates from tables by turning to common table expressions (CTE’s). When I start talking CTE’s some people begin to cringe in fear. I’ll admit, I still have to refer to a reference when doing something I haven’t done before with a CTE, which is pretty…
De-duping by Self join
Let’s continue the series on getting rid of duplicate data. Today we’re going to use self joins to get rid of them. A self join is a join from a table back to itself. It might seem weird to do that, but by the end of this article you’ll see how it’s useful. I use…
De-duping by temp table
Last time, we learned that manually removing duplicate rows from a table could be a long and manual process. Especially when you have as many duplicates as I created in our troubled table. Today we’re going to look at using temp tables to remove the extra rows. I gave you the overview to this already,…
Manually Deleting Stinks!
Go ahead and set up your TroubledTable by running the Create Troubled Table T-SQl Script. Now take a look at your duplicate rows. SELECT email, COUNT(*) FROM troubledTable GROUP BY email HAVING COUNT(*) > 1 You’ve got 200 rows that have been duplicated over and over. Not a pretty sight. Imagine how long it you…
Design Patterns Session
On February 6, I’m going to tele-present a session on T-SQL design patterns to the Brazil SQL Server Users Group. During this session I’ll cover three common scenarios you face, that can be solved by using T-SQL: Removing duplicate rows from tables. Create Troubled Table T-SQl Script — This contains the table we’ll use for…
How Much Logging Goes on for a Clustered Index Drop?
A week back @fatherjack put out a question on #sqlhelp that immediately drew my curiousity. He wanted to know how much his log file would grow if he dropped his clustered index (primary key), and then created a new one. I was thinking that it wouldn’t write a lot of information to the log file,…