SQL 201

It’s that time again! Time to start up a new class and cover some more advanced topics in SQL Development. Before beginning these lessons, You need to be comfortable with all the material in my SQL101 series. Specifically you’re going to need to know SELECT, INSERT, UPDATE, and DELETE.

With those skills in hand, I’ll help you take the next steps in your education. Here are the topics I plan to cover in this class:

There’s a LOT more to this class than 101, but when you’re done with this, you’ll be ready for any junior level SQL job you can find. Much of this I didn’t learn until I was well into my mid level positions.

As always, if you have any problems with the class, please let me know… I’m here to help!

And as I add more articles to this class, they’ll appear below.

  • SQL201 - Stored Procedure Results Posted on: 20110926
      I’m working my way through the last of the 201 lessons. Today we’re going to dive back into stored procedures. This time I want to cover the three ways you can get data back out. You can simply use a SELECT statement to return a record set, or you can define one or more [...]
  • SQL201-On Handling Errors Posted on: 20110922
        Let’s get back into SQL 200 by learning to deal with errors. Churchill had a great quote that applies to development (database or not) “He who fails to plan is planning to fail.” No matter how well you think you’ve planned your code, it’s going tofail.   The question is, will it fail [...]
  • SQL 201-Non-Correlated Sub Queries Posted on: 20110606
      It’s been a while since I’ve covered any material for my SQL 201 series, so I thought I’d jump back into it with non-correlated sub queries.  It’s a mouthful.  But it’s a really simple concept, you can join two queries that have absolutely nothing to do with each other into one statement. I’d like [...]
  • SQL Homework—UNION, INTERSECT, EXCEPT—Solutions Posted on: 20110323
      Here are the solutions to the homework problems I sent out.  Compare my queries with yours.  If they’re different, and you’d like some explanation on why they’re different.  Let me know.  I’ll be happy to explain any differences with you. 1. Give me a list of all the first and last names in both [...]
  • SQL Homework–UNION, INTERSECT, EXCEPT Posted on: 20110321
      This homework will run from my training server, shaioshin.hopto.org.  If you do not currently have access to this server, please let me know and I’ll set up credentials so you can complete the following homework assignment. In the AdventureWorks database on my server I have two tables: example.person and person.contact.  Take a look at [...]
  • SQL201-The Syllabus Posted on: 20110314
      It’s that time again! Time to start up a new class and cover some more advanced topics in SQL Development. Before beginning these lessons, You need to be comfortable with all the material in my SQL101 series. Specifically you’re going to need to know SELECT, INSERT, UPDATE, and DELETE. With those skills in hand, [...]
  • Converting VARCHAR to DATETIME Posted on: 20100328
      Recently a post was made on LinkedIn asking how one would convert a date stored in a VARCHAR field could be converted to a DATETIME so date math could be performed.  Unfortunately, this problem is more common than it should be.  If users are given a free-form field to enter data, they’ll often enter [...]
  • UNION, INTERSECT, and EXCEPT Posted on: 20100319
      Earlier today I was discussing methods you can use to differences in data sets between two tables or views.  My colleague was discussing the usual method SELECT columName(s) FROM table1 t1 (READCOMMITTED) LEFT JOIN table2 (READCOMMITTED) ON t1.sharedKey = t2.sharedKey WHERE t2.sharedKey IS NULL While this is a perfectly cromulent way of finding out [...]
  • SQL 201-Formatting Dates in Microsoft SQL Posted on: 20100315
      One of the more frequent questions I get for SQL Server is how to format a DATETIME into a specific date format. Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function. Following the standard date formats are some extended date formats that are [...]
  • Find Stored Procedures Referencing… Posted on: 20090717
      Time and time again, I find myself trying to find all the stored procedures that reference a specific database object.  It usually comes around when I’m working on changes to a table’s structure, or I’m replacing a table with a view.  I’ll usually reach for the following query (or something like it. 1: SELECT [...]
  • SET IDENTITY_INSERT Posted on: 20090420
      Let’s say you have the following table on two servers, one in development and one in production. CREATE TABLE person ( personID INT IDENTITY(1,1) , firstName VARCHAR(50) , lastName VARCHAR(50) ) You’re about to implement a new query, but you need to test it out in development before you move it to production.  The [...]
  • Code to Write Code Posted on: 20090116
      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 [...]
  • Get Rid of the Duplicate Records Posted on: 20090114
      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 [...]
  • Variables And Parameters Posted on: 20080922
      Transact SQL (T-SQL) has two main wait of passing data, variables, and parameters. A variable is an object in T-SQL batches and scripts that can old a value. After you define a variable, you can SET the variable or get the value from a variable at any point after the declaration. Useless trivia: You [...]
  • Flow Control Posted on: 20080915
      When you want to build more intelligent or more flexible T-SQL scripts, you’re going to need to know how to control the flow of the program. You’ll need the code to be able to make simple decisions on what to do next. Here are the commands you’re going to learn: IF…ELSE BEGIN…END BREAK CONTINUE [...]
  • The Error Severity Posted on: 20080901
      Errors can have different levels of severity. Based on this severity, you may wish to handle the error in different ways (see my articles on TRY..CATCH). In order to determine the severity of an error, Microsoft SQL has provided the ERROR_SEVERITY() function. This function takes no argument and returns an integer. Check out this [...]
  • Sub Queries With Multiple Levels of Nesting Posted on: 20080721
      Let’s go back to sub queries for a bit. You can nest a sub query within a sub query. There really is no limit. But in doing so, you really need to make sure there isn’t a better way, like a join, or Common Table Expression. The following query finds the names of employees [...]
  • Sub Query Fundamentals Posted on: 20080714
      Here we are with another sub query post. A sub query is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another sub query. A sub query can be used anywhere an expression is allowed. Here’s an example of a sub query in the SELECT clause of a [...]
  • The Error Message Posted on: 20080707
      When dealing with errors, it’s often necessary to log, or pass on errors to your users. In either case, you’ve already seen how to trap the ERROR_NUMBER. This time I’ll show you how to get the ERROR_MESSAGE. The ERROR_MESSAGE function takes no argument and it returns a string. BEGIN TRY DECLARE @Number TINYINT, @Result [...]
  • Correlated Subqueries in a HAVING Clause Posted on: 20080630
      Using sub queries in the HAVING clause is little different than using a sub query in any other part of the clause. The one thing that may through you is the WHERE clause inside the sub query… it acts as a JOIN criteria, so you might not get the placement right, until you’ve done [...]
  • Correlated Subqueries with Comparison Operators Posted on: 20080620
      If you haven’t used a sub query yet, I’m surprised. They can be incredibly useful. They can also be a crutch…but like I say time and again, each tool has it’s use. I’d like to go into a little detail on sub queries today by talking about correlated sub queries. Let’s say you wanted [...]
  • Correlated Sub Queries with Aliases Posted on: 20080613
      I know I’ve covered sub queries before, but I wanted to make sure I made a specific point. When you create a correlated sub query, you can reference columns from your subquery anywhere in your outer query. Usually you’ll either use it in your SELECT clause or your WHERE clause, but you can use [...]
  • COUNT(*) vs COUNT(columnName) Posted on: 20080610
      Let’s look at the following code using your copy of AdventureWorks. USE AdventureWorks GO SELECT COUNT(*) AS countStar , COUNT(ALL EmployeeID) AS CountAllEmployeeID , COUNT(DISTINCT EmployeeID) as CountDistinctEmployeeID FROM HumanResources.JobCandidate You should get three counts countStar    CountAllEmployeeID    CountDistinctEmployeeID 13 2 2 You need to understand the differences in these counts. COUNT(*)  returns the number [...]
  • Correlated Subqueries Posted on: 20080609
      The fundamental idea behind sub queries is you execute the sub query once, then take the value(s) from that sub query and substitute them in place of the sub query for the outer query. That’s quite a mouthful. Just think of it like this, the database engine will run the inner query, then replace [...]
  • Error Number Posted on: 20080602
      Every error generated in Microsoft SQL has a specific number, this number should uniquely identify the error that has occurred. This can be useful in building logic on what to do when a certain error is raised since creating a logic test for a number is far quicker than generating one for a string. [...]
  • sql server 2000 + How to display date time Posted on: 20080124
      Over on Experts Exchange I saw a question that I get all the time, “How do I format this DATETIME like x?”  If they’re asking to learn, I point them over to the CAST and CONVERT article on msdn.  If you wish to change a Microsoft SQL DATETIME column from the standard format 2008-01-28 [...]
  • 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 [...]
  • Exception Handling Posted on: 20070815
      Learning to handle errors better can be one of the skills that will really set you apart from other SQL Developers. You can do constant unit testing after each change, but odds are you’ll never hit ever scenario your code will face. There is a better way. If you have programmed in any language [...]
  • 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 [...]
  • 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 [...]
  • 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 201-SELECT INTO Posted on: 20070202
      I’ve already shown you how to create a table, and how to INSERT data into that table.  But did you know you can do both in one statement?  The SELECT INTO statement selects data from one table and inserts it into a different table.  I use the SELECT INTO statement is to create backup [...]
  • SQL 201 - Locking Hints Posted on: 20070115
      If you’ve spent any time looking at another programmers code, I’m sure you’ve see something like this: SELECT columnName FROM tableName (NOLOCK) I’m sure you asked yourself what the (NOLOCK) was all about, right?  Well, it’s time I explained.  The keywords in the parenthesis are referred to as table or locking hints.  A lock [...]
  • Optimizing Queries Posted on: 20061215
      This is the first article in a never ending series of articles on how to optimize your queries. Honestly, this is a topic I’m still learning volumes about. I learned pretty early on the number one rule to getting fast results from a SELECT query. Limit the results to the minimum you need If [...]
  • 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 [...]
  • SELECT CASE Posted on: 20061120
      A useful skill to learn in SQL is how to translate a value into a description.  You’ll use it all the time.  Whether you’re turning M|F into Male or Female, or turning T|F into True or False, you’ll have to be able to make the translation as quickly as possible.  The easiest way to [...]
  • 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 201 - Views Posted on: 20060816
      Previously I showed you how to create tables.  The normal goal for designing a table is to store data in such a way that each table contains one group of facts that are highly related to each other.  For example, if you had built a contact database, you’d have one table for people, one [...]
  • SQL 201 — User Defined Functions Posted on: 20060801
      Back in SQL 101, I introduced you to some functions, GETDATE(), ISNULL(string1, string2), etc.  All of these functions come defined in Microsoft SQL before you even install it on your machine.  But that’s just the beginning.  You can create your own functions to do tasks too!  There are two types of user defined functions, [...]
  • TOP PERCENT Posted on: 20060630
      We have a multi-threaded process that grabs it’s fair share records and then processes them. It basically looks at the total number of records to process, and takes the top 10% of the records and marks them so that no other process will grab those records. If you haven’t done a lot with TOP, [...]
  • Inter-Database Queries Posted on: 20060501
      I’ve had this question come up a few times now.  “How do I copy data from one database to another?”  It’s after getting a question like that, I explain fully qualified database object names.  That’s a mouthful!  Basically it’s a fancy way of saying the “full name” of a database object, like a table [...]
  • SQL 201 — CREATE PROCEDURE Posted on: 20060415
      Now that you have the basics of SQL, I think it’s time to learn something a bit more involved.  If you need to accomplish a task within SQL you will run a series of T-SQL statements.  If you want to run those same steps over and over again, you could save them to a [...]
  • 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, [...]
  • Moving Data Between Tables Posted on: 20051223
      Very early into my SQL career I had to get comfortable moving records around between tables and databases. There are several ways to do it, you just have to get used to some of the requirements in moving the records. Moving records into a temporary table The first time you move records, you’ll probably [...]
  • SQL 201 - Combining Query Results Posted on: 20051209
      Overview There comes a point in your SQL career where you will need to combine the results of several queries into one data set, and present it in one view.  When you get to this point, you’ll need to be familiar with the UNION command.  With Microsoft SQL 2005, there are two new functions, [...]
  • SQL 201 - Sub Queries Posted on: 20051115
      Sub queries or nested queries are complete queries wrapped inside of another complete query. Some times this technique is used as a replacement for a JOIN statement. Some times it’s the only way to define exactly what you you want to get from a database. The important thing to learn about sub queries is [...]
  • SQL 201 - String Manipulation Functions Posted on: 20050919
      In a previous post I introduced you to calculated fields, and mentioned there were many other functions you could use to compute values or columns.  It’s time to introduce you to the eight most used functions when it comes to manipulating strings in SQL LEFT and RIGHT If you ever need the first 3 [...]
  • String Manipulation -- CAST and CONVERT Posted on: 20050402
      So far I’ve shown you just a few of the many string manipulation functions available in Microsoft SQL.  Today we’re adding two more of these tools to your tool belt.  CAST and CONVERT.  These two become useful when you’ve stored a value as one data type, and later find you need it used as [...]
  • String Manipulation -- REPLACE, PATINDEX and Regular Expressions Posted on: 20050310
      In the previous SQL posts, I showed you SUBSTRING, then I showed you REPLACE. Now, with REPLACE, you had to know what characters you wanted to replace.  What if you only knew what characters you wanted to keep?  Well, let me introduce you to PATINDEX.  This is an advanced command that is easy to [...]
  • String Manipulation -- REPLACE Posted on: 20050202
      In the last SQL post, I showed you SUBSTRING, and how you could use it to locate the area code in an un-formatted phone number.  The problem was the field we were working from was full of malformed numbers.  Today, we’ll start on cleaning up the numbers. But first, I need to include my [...]