SQL 102

After completing my SQL 101 series, I thought I’d take a shot at writing another series. This time I’ll be writing about Database Administration. While these topics will be covered in an introductory way, don’t be fooled into thinking this is simple material. These basics can be applied in a number of ways. The technique is simple, the application will take experience and practice. As always, if you have any questions about this material, let me know. I’m here to help!

Syllabus

I’ll probably add more lessons to this course as I receive more questions on Administration topics. After all, these lessons are here for you! Let me know what you want to learn, and I’ll do my best to teach you.

This set of articles will be about Database Administration. As I add articles to the series, they’ll appear below.

  • World Backup Day Posted on: 20120330
      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 [...]
  • Foreign Keys and Witchcraft Posted on: 20101207
      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.  [...]
  • SQL 102-SELECT * Overhead Posted on: 20100512
      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 [...]
  • Referential Integrity Posted on: 20090330
      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 [...]
  • SQL 102 - Indexes With Included Columns Posted on: 20090302
      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 [...]
  • SQL 102 - UNIQUE INDEX Posted on: 20090126
      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 [...]
  • SQL 102 - CLUSTERED vs. NONCLUSTERED Indexes Posted on: 20090109
      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” [...]
  • Nesting an Exception Posted on: 20081103
      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 [...]
  • DDL Triggers Posted on: 20081027
      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 [...]
  • INSTEAD OF Triggers Posted on: 20080929
      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. [...]
  • Triggers Overview Posted on: 20080825
      OK, What do you know about triggers? Did you know you can use them to run a bit of T-SQL on data changes? Those would be called Data Manipulation Language (DML) Triggers. You can set those up for tables so that a certain action occurs with each INSERT, UPDATE, or DELETE. You can build [...]
  • DELETE Triggers Posted on: 20080728
      This is the last of my three posts on triggers. We’ve already covered INSERT and UPDATE TRIGGERS. This time we’re doing DELETE triggers. All the same rules apply to DELETE triggers that apply to INSERT and UPDATE triggers. CREATE TRIGGER t_tablename_delete ON TableName AFTER|FOR|INSTEAD OF DELETE AS T-SQL code Just like for the INSERT [...]
  • UPDATE Triggers Posted on: 20080627
      Last time we discussed triggers, we covered insert triggers. This time we’re doing UPDATE triggers. All the same rules apply to UPDATE triggers that apply to INSERT triggers. CREATE TRIGGER t_tablename_update ON TableName AFTER|FOR|INSTEAD OF UPDATE AS T-SQL code Just like for the INSERT TRIGGER, we can either run the T-SQL code after the [...]
  • Insert Triggers Posted on: 20080526
      An insert trigger is a Data Manipulation Language (DML) trigger that acts when a new record is added to the table. CREATE TRIGGER t_tableName_insert ON tableName AFTER|FOR|INSTEAD OF INSERT AS Some bit of T-SQ OK, let’s walk through this. CREATE TRIGGER begins the command. The next part t_tableName_insert is the trigger’s name. I follow [...]
  • SQL 102 - REVOKE Posted on: 20080520
      Like I mentioned previously REVOKE is like an undo function for GRANT and DENY.  If you have a developer and you work with him for a while, you may find that he’s ready to be given a little more leeway in the database.  Once you’ve decided to make a change to the permissions, you [...]
  • SQL 102 - DENY Posted on: 20080512
      Alright, at this point you should know how to GRANT permission to a database object, but do you know how to DENY permission to an object?  DENY uses a syntax similar to GRANT. DENY { ALL [ PRIVILEGES ] } | permission [ (column(s) ) ] [ ON securable ] TO principal I want [...]
  • SQL 201 - Contstraints Posted on: 20071001
      Constraints… helpful in making sure you get the data you want out of your database! Constraints allow you to define rules that the data must follow in order to be inserted into a record. You might want to define a primary key or foreign key in order to define the relationship of two related [...]
  • Triggers Posted on: 20070903
      Triggers are one of the most misunderstood features in Microsoft SQL server. Most of that misunderstanding comes from people implementing triggers with little understanding to their impact. Even worse, they’ll implement them without testing. And the worst of all, not using them, when the functionality being requested is the very definition of what a [...]
  • Default Values Posted on: 20070801
      When you start creating tables and setting them so they can’t have NULL values, you’ll eventually need a way to define a default value for the column. A default value is one that a column would apply to its record if a value is not provided. To specify the default value in a SQL [...]
  • Identity Columns Posted on: 20070716
      An IDENTITY column is a column that automatically gets it’s value set by the database engine when a new record is added. This is one of the oldest ways Microsoft SQL has of making sure a record is unique. Even if a user were to insert the same record twice, the IDENTITY will always [...]
  • Computed Columns Posted on: 20070702
      What is a Computed Column A computed column is a column that users will not enter data for, but the SQL Server will compute and store a value in. The expression for a computed column may include the names of other columns in the table. These columns can also be combined with literal values [...]
  • Schema Posted on: 20070615
      In previous versions of SQL Server we had what we called “owners” of objects. Nearly every time you talked about an object “owner” you were talking about dbo. Think about the default name of any user defined functions you created. They always got “dbo.” added to the beginning of their name by the engine [...]
  • Rename a Table or Column Posted on: 20070514
      In a previous post, I showed you how to rename a database in Microsoft SQL. If you haven’t already been hit with a request to change the name of a table or column by now… it’s coming. In order to prepare you, I’m not giving you the master sword. It’s almost as helpful, right? [...]
  • Renaming a Database Posted on: 20070430
      A topic that you’ll need sooner or later is how to rename a database. You’ll get a request to build a database, and it’ll be documented fairly well. You’ll be coding along, then you get a ping through IM. -> Hey, you know that database you just created for me? <- Yeah. I’ve already [...]
  • SQL 102 -- DROP DATABASE Posted on: 20070413
      While deleting a database is a fairly rare occurrence, I’m teaching you now, since it’s a pretty easy task to learn. Before teaching you this, you do know rule number 2, right? I bring this up because, you never know when you’ll get a request to drop a database, only to get a call [...]
  • DROP and Truncate Posted on: 20070330
      It’s only a matter of time before you need to clean up after yourself in Microsoft SQL. You’ll have to get rid of an index, or a table, or maybe even a whole database. Indexes, tables, and databases can easily be removed using the DROP statement. The DROP INDEX Statement If you get into [...]
  • SQL 102 -- CREATE DATABASE Posted on: 20070314
      The most fundamental task you’ll have to learn to do as a Database Administrator is to create a database. Please remember it’s probably a good idea to make sure you’re connected to the master database on your server when creating databases. Don’t change your connection to your database until you’ve closed out your CREATE [...]
  • ALTER TABLE Posted on: 20070215
      After you’ve spent time designing the perfect table, someone is going to come along and ask you a question that will lead you to changing your table. It can be something as simple as, hey, we can only store 25 characters for a city name, we need to have 50. Or it can be [...]
  • SQL 102 – Foreign Keys Posted on: 20070101
      In the simplest terms a Foreign Key in one table refers to the Primary Key of another table.  Let’s go back to our demo tables products and productSales. --Products: productName price quantity color ----------- ----- -------- ----- pencil 0.25 100 yellow pen 0.99 73 blue paper 1.00 500 white Mountain Dew 1.25 8 green [...]
  • SQL 201 - Statistics Posted on: 20061201
      Microsoft SQL Servers collect statistical information on indexes and column data stored in each database. These statistics are used by the query optimizer to choose the most efficient way to execute your queries. Good statistics hep the optimizer to asses the cost of different query plans, then choose the “best” method to execute your [...]
  • SQL 102 - Jobs Posted on: 20061115
      Now that you’re familiar with stored procedures, it’s time to introduce you to jobs. Jobs are like stored procedures, in that they are a set of steps performed sequentially. Jobs can run T-SQL commands, Integration Services Packages, Analysis Services commands, replication tasks, and even command prompt applications. And what’s the best part? These steps [...]
  • SQL 201 - Indexes Posted on: 20061101
      Indexes in Microsoft SQL are just like indexes in a book. They help you jump to the information you want more quickly. In Microsoft SQL you can index one or more columns in a table or view. The indexes defined can help your select statements run much more quickly… but they do have a [...]
  • SQL 102 - GRANT Posted on: 20061015
      As an administrator, you can execute the SELECT from the Components table and the v_Components view, and execute the usp_GetBatmobileComponentsList procedure; however, your new user cannot. To grant your new user the necessary permissions, use the GRANT statement. Let’s assume for this lesson you’ve already created a SQL Server user [Lucius Fox]. Execute the [...]
  • SQL 102 - Users Posted on: 20061001
      Before you got started working with SQL Server, someone had to set up a user account for you. Now that you’re learning to become a database administrator you need to learn to allow others to have access to SQL Servers. Granting a user access to a database takes three steps. First, create a login, [...]
  • SQL 102 – Primary Keys Posted on: 20060915
      In SQL 101, I showed you how to create a table.  I want to build on that today by introducing the concept of a primary key.  A primary key is a column, or combination of columns that uniquely identify the row in the table.  By doing this you can have some assurance that the [...]
  • A Series for Database Administration Posted on: 20060715
      I’m at it again! I’m working on some notes about how to administer a Microsoft Database Server.  I’m planning on following the fundamentals you would need to cover the 70-431 (the Microsoft Database Administrator’s exam).  I’m doing this to share, but I’m also doing this to help me grow a bit stronger in my [...]
  • The DBA’s Rules — Rules 3 and 4 Posted on: 20060701
      In previous posts I shared with you my rules for who does and doesn’t have access to sa, and keeping your data safe by having tested backup and restore procedures.  Next I’m covering two related rules.  These are to protect me as much as they are to protect you.  If you’ve worked in a [...]
  • The DBA's Rules -- Rule #2 Posted on: 20060615
      Rule #2: Have a tested Backup and Recovery Plan I’m sure no one out there isn’t doing a backup on their database server at some interval compatible with the nature of their data.  If you’re updating your data weekly, weekly is fine; if you’re updating every minute of every day, you need to backup [...]
  • The DBA's Rules Posted on: 20060531
      I don’t know if you’re a fan of NCIS, but for those of you who aren’t let me give you a little back story. Gibbs is the leader of an investigative team and he has a set of rules he teaches his team members in order to help make them better at their jobs. [...]
  • Determine the Space Used by Tables Posted on: 20060515
      Eventually you’ll be faced with running out of space on a SQL server.  Usually, before you get more drive space, you’re asked to find out if you can get rid of any information you’re holding onto in the server.  You come up with a list of tables in databases that you feel you could [...]
  • SQL 201 – CREATE TABLE Posted on: 20060401
      CREATE TABLE I’ve showed you how to get data out of a table, put data in, change it, and delete it.  But I haven’t showed you how to create your own table….until now. CREATE TABLE tableName ( columnName <datatype> ) This statement is pretty straightforward, you have to choose the name of the table, [...]