Like I’ve mentioned before, unless you use SET TRANSACTION ISOLATION LEVEL, the server will default to READ COMMITTED. What this means is by default, your queries will only be able to interact with records that have been committed. If another query is affecting those records, you’re not able to see them in any way. I…
Category: Microsoft SQL
SQL 102 – Indexes With Included Columns
I’ve shown you how to create indexes, CLUSTERED versus NONCLUSTERED indexes, today I want to build on that. Let’s say you have a lookup query that takes names, and returns an ID for that name, you can actually build your query in a way where it would look up that data using just an index. …
70-433:OUTPUT and IDENTITY
Let’s look at a scenario you’ll need to be able to handle if you hope to pass the 70-433. This scenario requires you to understand the IDENTITY property for a column as well as how to return the identities created for rows inserted. Let’s start by creating a table to store widgets. We want an…
70-432: Unique Constraints
Let’s cover another scenario that uses knowledge you’ll need in order to pass the 70-432. This time we’re going to work with constraints. We all know that you can declare a unique constraint on a column you where you want to be sure you won’t get any duplicates. But do you know how to deal…
70-432: Revoking Permissions
Today we’re going to cover a scenario you might face at work, one that’s also a topic you’d need to know in order to pass the 70-432 Database Administrator’s exam. If you have any questions in your preparation for the 70-432, let me know. I’ll be happy to help in any way! Now for the…
SQL301-ETL With SSIS, Part 4
We’re back to our ETL with SSIS project, and today we’re going to pick up with transforming the data from our raw format to the final destination for our contact data. So far we’ve scanned a directory for files matching a certain pattern. After finding a file matching that pattern we set up some variables,…
SQL 102 – UNIQUE INDEX
Ok, you understand how to create an INDEX. You understand the differences in CLUSTERED and NONCLUSTERED indexes. Did you know you could index a view? You can. But before you start adding indexes, just like you would for a table, you’ll have to create an UNIQUE CLUSTERED INDEX on that index. The reason for that…
SQL 301-ETL With SSIS, Part 3
Ok, so in our SSIS package we currently have all our variables set up, we have a for each loop that will look in a certain folder for all files matching a certain file pattern. Inside that loop we have a script that will build our archive path and error paths. Next we need to…
Code to Write Code
I’ve read “When you start writing code that writes code for you, you’ve moved up from a beginner to a professional.” Today I’d like to walk you through an example of where learning to write this kind of code pays off. At my we have a table that defines a hierarchy. Each level on the…
Get Rid of the Duplicate Records
Time after time I’ll get a table that has duplicates in it and I have to clear out all the duplicate entries while leaving a distinct set of records. There’s a couple of ways to solve the problem. Today I want to take you through my standard fix. Script the source table out to a…