This past Saturday was World Backup Day, It’s not an actual government recognized holiday, but as a DBA you should start celebrating it. Maybe celebrate isn’t the right word for what you might want to do on WBD. If you have a copy of the SQL PASS Summit 2011 DVDs, check out Grant Fritchey’s Lightning…
Tag: SQL102
Foreign Keys and Witchcraft
I’ve worked with hundreds databases in my career. I could probably design a handful of them from memory if asked. I have notes to remind me of lessons learned from most of them. When I start looking through that list one of the things I most often reference is the importance of Foreign keys. Why…
SQL 102-SELECT * Overhead
How many of you have heard me rant against using “SELECT * FROM tableName”? If you have I’m sure you’ve heard me mention the following reasons why SELECT * is evil. the more data you ask for, the longer it takes to return that data extra paging table locking hinders future attempts to create a…
Referential Integrity
OK, I’ve talked about Primary Keys and Foreign Keys. Now let’s talk about Referential Integrity. Basically this just means that any Foreign Key value in one table has to have that same value defined in the table where that Foreign Key is the Primary Key. Let’s look at two tables, and try to explain this…
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. …
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 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…
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…