In my last post on SSIS, I started you into developing an ETL solution with SSIS. I walked you through setting up your solution, renaming the package, and setting up the variables we would use during the rest of these lessons. Today, we’ll pick up with a Foreash Loop Container. In most case, you’re going…
Category: Microsoft SQL
SQL 102 – CLUSTERED vs. NONCLUSTERED Indexes
Previously, I covered the basics of creating an index using CREATE INDEX. Today, I’m going to dig a little deeper. I’d like to teach you the difference in an CLUSTERED versus a NONCLUSTERED index. If you learn nothing else, know that a CLUSTERED INDEX physically orders the table, a NONCLUSTERED INDEX creates another “table” and…
70-433:Good, Better, Best Index Selection
With SQL Server 2008, you’ve got some additional tricks you can pull off with indexes. With 2005 you could INCLUDE columns in your index, now with 2008 you can also create indexes with a WHERE clause. This means you can limit the number of rows in your NONCLUSTERED indexes. By limiting the number of rows…
SQL 301 – ETL With SSIS, Part 1
I’ve discussed SSIS on this site before, but I’ve heard the feedback, you want real world examples. So, I decided to use the business case I address most often using SSIS, and that’s Exchange Transformation and Load. Let’s dive right in to this problem. You receive a file via FTP from a vendor every day. …
SQL 301 – Views
At this point I’m sure you know how to CREATE and ALTER a VIEW. But today I’d like to go into a little more detail on views. I’d like to cover updatable views, and some additional options you can add to your CREATE or ALTER VIEW statements. Updatable Views You can write insert s, updates,…
Find Tables Containing a Certain Column
Today I had to find all the tables that contained a certain column. I was going to rename the column, so it made more sense when you see it in my tables. In order to make the change, I had to know all the tables that had the column. Then, I get to use sp_rename!…
sp_executesql
I’m pretty sure this is the first time I’ve brought up dynamically generated SQL statements. But it’s time you learn about them. There are times where you’ll need to run the same statement against multiple tables. Or agains multiple databases. Sometimes, you may not know how many columns you’ll need to create or select during…
Nesting an Exception
Like just about every other technique in Microsoft SQL you can nest your exception handling. The basic template is: BEGIN TRY BEGIN TRY — Nested try block END TRY BEGIN CATCH — Nested catch block END CATCH END TRY BEGIN CATCH –catch block END CATCH You can do this as many levels deep as you…
DDL Triggers
Introduction OK, we all know that when we create objects in a database, we’re using Data Definition Language (DDL) commands. Every time we create an object, we’re firing an event. Since we’re firing an event, we can also do some action… That means we can create a TRIGGER to handle this action. A DDL trigger…
INSTEAD OF Triggers
OK, so we’re all familiar with triggers now, right? You have seen that using a trigger you can log a change to a table in a special log file, or we can create a related record in another table. But all of these events happen after the action that triggers the trigger has completed. What…