Tip of the Week

SQL Server Triggers

2010-03-01T01:44:00Z

Handling trigger is a very simple, and very misunderstood facet of SQL Server programming. Today I'll take a look some very common trigger scenarios, some mistakes made, and how you can program around them.

First, a little bit of background of the types of triggers available in SQL Server.

Trigger Types
There are two different types of SQL Server triggers to date: INSTEAD OF triggers and AFTER triggers.

INSTEAD OF triggers are placed on tables and views (most often views) and are invoked INSTEAD OF the action taken. For example, if I have defined an INSTEAD OF trigger on a view to execute in the event of an INSERT statement, the trigger would be invoked INSTEAD OF my INSERT statement and actions could be taken to handle the intended statement. This is ideal for views because it allows the developer to abstract triggers on the view code so that the view behaves like a table and without the overhead of the user knowing how to appropriately handle with the underlying tables. For example, I could define a view named vw_AllSalesView that abstracts sales data among 10 different tables. I could then define an INSTEAD OF trigger on this view which will insert data into the appropriate table(s) when an INSERT statement is ran against the view.

AFTER triggers are actions that are taken AFTER an action is taken on a table. This is really the most common type of trigger, and is ran against tables (not views). For example, you have a situation where you need want to capture any new records inserted into a table into a processing table for later work. You could define an AFTER trigger on the primary table to capture the INSERTED records AFTER the action is taken.

It is very important to note the similarities and differences between an INSTEAD OF and an AFTER trigger. Both trigger types execute in the context of the original transaction. The INSTEAD OF trigger overtakes the transaction and completes its work rather than the original DML statement. The scope of a transaction of an AFTER trigger ends when the original statement has completed AND after the work by the AFTER trigger. The major differences between the two trigger types is the scope of work that they accomplish. Remember that the INSTEAD OF replaces in full the work of the original intended DML statement. The AFTER trigger still allows the original statement to occur, and runs after it has completed.

Pseudo Tables
So, how does a trigger have access to the records that were inserted/modified/or deleted (or intended to be inserted/modified/or deleted in the case of an INSTEAD OF trigger)? Well, the records from the modifications are stored in two different internal SQL Server tables.

INSERTED
The INSERTED pseudo-table stores records that were newly inserted into a table in the case of an INSERT statement. This table also contains the new values for the records that were modified through the use of an UPDATE statement.

DELETED
The DELETED pseudo-table stores records that were deleted from the table through a DELETE statement. This table also contains the old values for the records that were modified through the use of an UPDATE statement.

Granularity
All SQL Server triggers are handled at a set-based granularity. This means that whatever records are updated by a certain statement will also be available in the INSERTED and DELETED tables. This is one of the primary problems I have seen when triggers are written. SQL Server does not have a "FOR EACH ROW" feature when handling trigger records. In the event (usually a RARE event) that you need to take special actions on each record in one of the pseudo tables, such as calling a procedure for each value, a loop construct is how you'll need to accomplish it. (More on this later.)

So, lets take a look at a few common trigger-based programming mistakes and what you can do to get around them. (Think SET-BASED) First, lets create a table and load some data into it.

IF OBJECT_ID('SalesHistory') IS NOT NULL
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(     
    [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,     
    [Product] [varchar](50) NULL,     
    [SaleDate] [datetime] NULL,     
    [SalePrice] [money] NULL
)
GO    

DECLARE @i SMALLINT
SET @i = 1

WHILE (@i <=100)
BEGIN                 
    INSERT INTO SalesHistory(Product, SaleDate, SalePrice)                     
    VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))          

    INSERT INTO SalesHistory(Product, SaleDate, SalePrice)     
    VALUES ('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))                      

    INSERT INTO SalesHistory(Product, SaleDate, SalePrice)            
    VALUES ('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))                      

    SET @i = @i + 1    

END

Now to the trigger. Below is the format that I sometimes see on forum posts, usually along with the complaint of "Why isn't my trigger working?" Lets take a look at why it doesn't really do what is intended.

CREATE TRIGGER tr_SalesHistory_Update
ON SalesHistory
AFTER UPDATE
AS
BEGIN
    DECLARE @Product VARCHAR(50), @SaleID INT
    SET @Product = (SELECT Product FROM INSERTED)
    SET @SaleID = (SELECT SaleID FROM INSERTED)
    SELECT @Product, @SaleID
END

Now I can run an UPDATE statement to see how well my fresh new trigger works.

UPDATE TOP(5) SalesHistory
SET SaleDate = SaleDate

My UPDATE statement causes an error. It seems that I have a sub-query that returns more than one value that I'm trying to assign to a variable. I am actually VERY lucky to receive this error. This tells me that something is very wrong with my code, and that I probably need to take a step back and reconsider how I'm handling it.

Msg 512, Level 16, State 1, Procedure tr_SalesHistory_Update, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

However, instead of rethinking how I'm writing my trigger, I'll do a little rearranging to resolve my error. This time I'll include the functionality of using the values from my INSERTED table to update an archive table. I'll also show some rows at the end of the trigger so I can verify what I am doing is what is intended.

ALTER TRIGGER tr_SalesHistory_Update
ON SalesHistory
AFTER UPDATE
AS
BEGIN
    DECLARE @Product VARCHAR(50), @SaleID INT

    SELECT @Product = Product, @SaleID = SaleID
    FROM INSERTED

    UPDATE sf
    SET Product = @Product
    FROM SalesHistoryArchive sf
    WHERE SaleID = @SaleID

    SELECT @Product, Product, @SaleID, SaleID
    FROM INSERTED

END

Now, lets run the same UPDATE statement again:

UPDATE TOP(5) SalesHistory
SET SaleDate = SaleDate

Below is the output from my trigger. The first and the third columns represent the values from my variables that I captured from the INSERTED table and the second and fourth columns represent the actual values from the table. As you can see, my variables only contain the values from the first (just happened to be the first record in this case) record from my batch. I updated the SalesHistoryArchive table with these values, rather than from all of the values from the INSERTED table.

Luckily, the change for this is easy to make. I can use a set-based approach and get around the use of variable entirely. I can join my INSERTED table to the SalesHistoryArchive table to achieve my desired result.

ALTER TRIGGER tr_SalesHistory_Update
ON SalesHistory
AFTER UPDATE
AS
BEGIN

    UPDATE sf
    SET Product = i.Product
    FROM SalesHistoryArchive sf
    JOIN INSERTED i ON sf.SaleID = i.SaleID

END

Handling Each Value
So, what can you do if you need to call a stored procedure for each value altered and captured in your trigger? There are two main ways to handle this situation; store the records in a table and handle them later, or iterate through them in the trigger and call the procedure. The first way is relatively straightforward (and by far the best option of the two). In this scenario, a queue-table of sorts is used to capture output from the INSERTED or DELETED tables in the trigger. Later, a process acts on the records in this table and cleans them up once its work is done. Iteration is still likely used in this scenario, but not at the expense of the original transaction as would be the case if a WHILE LOOP or CURSOR were used in a trigger.

With the cursor approach, the INSERTED or DELETED records are loaded into a cursor (or WHILE LOOP) and the data set is iterated. At each iteration, the values from one row in the pseduo table is passed to a stored procedure. The main drawback to this apporach is that the entire data set must be iterated through BEFORE the intial transaction has completed. If it was a large update, the scope of the transaction becomes much larger, causing the duration of the locks on the table to increase. This causes nasty blocking and potentially deadlocks. When faced with such a situation, I strongly encourage you to store the captured values in a table for later processing. It requires a little extra work up front, but pays off in the end due to less stress on the database.

Tim

What version of SQL Server am I running?

2009-11-01T15:03:00Z

Quite often I see the question in the SQL Server forums "What version of SQL Server am I running?" To be honest, this is a VERY easy question to answer. Microsoft provides a function with a set of properties you can query for identifying such information. The function provided by Microsoft is SERVERPROPERTY. However, there are specific parameters you need to pass to it to get the information you need. So, rather than having to worry about these specific parameter values, I've created a system stored procedure that you can use to find out any information you need to about your SQL Server instance without having to know specific parameter values. The code for the system stored procedure is below.

USE master
GO
CREATE PROCEDURE sp_ServerProperties
AS
 SELECT 
     BuildClrVersion = SERVERPROPERTY('BuildClrVersion'),
     Collation = SERVERPROPERTY('Collation'),
     CollationID = SERVERPROPERTY('CollationID'),
     ComparisonStyle = SERVERPROPERTY('ComparisonStyle'),
     ComputerNamePhysicalNetBIOS = SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
     Edition = SERVERPROPERTY('Edition'),
     EditionID = SERVERPROPERTY('EditionID'),
     EngineEdition = SERVERPROPERTY('EngineEdition'),
     InstanceName = SERVERPROPERTY('InstanceName'),
     IsClustered = SERVERPROPERTY('IsClustered'),
     IsFullTextInstalled = SERVERPROPERTY('IsFullTextInstalled'),
     IsIntegratedSecurityOnly = SERVERPROPERTY('IsIntegratedSecurityOnly'),
     IsSingleUser = SERVERPROPERTY('IsSingleUser'),
     LCID = SERVERPROPERTY('LCID'),
     LicenseType = SERVERPROPERTY('LicenseType'),
     MachineName = SERVERPROPERTY('MachineName'),
     NumLicenses = SERVERPROPERTY('NumLicenses'),
     ProcessID = SERVERPROPERTY('ProcessID'),
     ProductVersion = SERVERPROPERTY('ProductVersion'),
     ProductLevel = SERVERPROPERTY('ProductLevel'),
     ResourceLastUpdateDateTime = SERVERPROPERTY('ResourceLastUpdateDateTime'),
     ResourceVersion = SERVERPROPERTY('ResourceVersion'),
     ServerName = SERVERPROPERTY('ServerName'),
     SqlCharSet = SERVERPROPERTY('SqlCharSet'),
     SqlCharSet = SERVERPROPERTY('SqlCharSetName'),
     SqlSortOrder = SERVERPROPERTY('SqlSortOrder'),
     SqlSortOrderName = SERVERPROPERTY('SqlSortOrderName'),
     FilestreamShareName = SERVERPROPERTY('FilestreamShareName'),
     FilestreamConfiguredLevel = SERVERPROPERTY('FilestreamConfiguredLevel'),
     FilestreamEffectiveLevel = SERVERPROPERTY('FilestreamEffectiveLevel')
     
GO
use master
go
EXECUTE sp_ms_marksystemobject 'sp_ServerProperties'

Once you've created the procedure, you can execute it in the context of any database on your SQL Server instance to return all of the system properties for your SQL Server instance. Note that this procedure is customized for SQL Server 2008. The 2005 version will be absent a few of these properties...namely the FILESTREAM properties.

HTH,

Tim

Why is this query slow: 1

2009-10-24T13:41:00Z

Since this is the first part of this series, I'll give a little background as to what I want to accomplish. At some point, you're going to run into a situation where a query on your SQL Server system is "slow". Slowness is usually ambiguous, and almost always relates to a persons reference. However, when we hear that something is slow or broken we need to check it out and see what may be the cause. So, my goal is to introduce simple to increasingly complex queries and show you the steps I like to take to diagnose what may be causing them to be "slow".

Statement 1:

SELECT *
FROM TableA a
JOIN TableB b ON a.PrimaryKeyField = b.ForeignKeyField
WHERE b.LookupValue = 3

The first thing I like to look for are the simple things. 9 times out of 10 something simple is being missed and causing poor performance. So, in the query above I first want to look for any columns that may need to be indexed. My TableA table has a clustered index (default) on the Primary Key field (named PrimaryKeyField), so single record lookups should perform seek operations. Next I want to look at the ForeignKeyField in TableB. Since I am joining on this field back to the PrimaryKeyField in TableA I want to make sure this field has a nonclustered index on it to facilitate faster lookup operations. If there is indeed a foreign key constraint defined between these two tables based on this relationship, the nonclustered index on the ForeignKeyField in TableB becomes even more important. SQL Server has to perform a lookup check to make sure the constraint is satistified anytime a record is inserted (or the ForeignKeyField is updated) in the table, so if SQL Server can use an index to perform the seek it is all the better.

Next I want to check the criteria in the WHERE statement. Note: The criteria in the JOIN operations can be thought of as "criteria", so anytime you're considering criteria you should also be considering indexes to quickly satisfy the criteria. Because I am doing a lookup on the LookupValue field, I'll want to see if an index on this field makes sense. Some indexes make sense, some do not....more on that later.

I also want to remove the SELECT * portion of the query and replace it with a specified field list. Bringing back all of the fields involved almost always results in a slower query, and can end up breaking your data layer or interface code if you're not careful.

Lastly, I want to make sure my table references are prefixed with the schema that the table belongs to. I like to do this so that SQL Server doesn't have to do the determination for me.

I plan on posting more of these in the future. If you like them and/or have any suggestions that you would like for me to go over, please let me know!

Tim

Viewing Performance Counters in SQL Server 2005

2009-10-01T02:21:00Z

I like Performance Monitor (perfmon); its a good tool for diagnosing many different types of problems. Its great for determining from how well your memory is handling requests to how well your CPU usage is doing...its also great for checking out different SQL Server metrics. In SQL Server 2005, the sys.dm_os_performance_counters Dynamic Management View (DMV) is introduced to allow you to query SQL Server related performance counters directly through the database engine. This makes is very useful when you need to quickly determine your buffer cache hit ratio or how many lock deprecated features being ran in your SQL engine. This DMV is straightforward to use, which allows you to easily setup your own custom procedures to quickly review any performance metric that may be important to you.

Lets run a quick query against the sys.dm_os_performance_counters DMV to see what kind of data we are dealing with:

select * 
from sys.dm_os_performance_counters

Here is a simple output from my system:

Its all SQL Server related infromation, which may not be 100% what you might want to monitor...but it is a good start. It allows me to monitor just about any SQL Server related metric that I want. For instance, if I wanted to see how many temp tables were currently on my system, I could write a little script like the one below.

select * 
from sys.dm_os_performance_counters
where counter_name = 'Active Temp Tables'
GO
IF OBJECT_ID('tempdb..#NewTempTable') IS NOT NULL
DROP TABLE #NewTempTable
GO
CREATE TABLE #NewTempTable
(
    Field1 INT
)
GO
select * 
from sys.dm_os_performance_counters
where counter_name = 'Active Temp Tables'

And the output from my home system:

You can see from the output above that the DMV pulls accurate information. When I first ran the query there were no temp tables on my system. I created a temp table, and then reran the query again. The DMV returned that there was one active temp table. Neat stuff...and very handy.

While having the ability to see how many temp tables on my system might not be really useful right now (it very well could be depending on your environment), you can easily see how you could write your own custom routines to pull data from this table that is important to you. The trickiest part is determine which counter is what you want to look at. That is a whole other topic in and of itself.

Hope this helps!
Tim

Using APPLY to match a table valued function to a table

2009-09-22T02:46:00Z

In this article, I will explain how to use the APPLY operator to join the results of a table valued function to a table. The APPLY operator allows a table column to be supplied as an input parameter to a table valued function.

Microsoft describes the APPLY operator as "The new APPLY relational operator allows you to invoke a specified table-valued function once per each row of an outer table expression" (source). Basically what that means is that you can join a table valued function to another table. Except you don't use the JOIN operator. You have to use either the CROSS APPLY or the OUTER APPLY operators.

Under certain circumstances you can use the simple join syntax. Specifically, when the function has no parameters or the parameters are variables or literals. But when the input parameters for the table valued function come from a table, attempting to do so will generate an error.

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "xxxxxxxxx" could not be bound.

In order to demonstrate use of the APPLY syntax, we will create a sample table and load it with some sample data. We will use the fn_DelimitedToTable function as demonstrated in the article titled Converting a delimited string into a table.

create table [dbo].[test_data_1]
     (ident         int identity primary key clustered
     ,username      varchar(100) not null
     ,websites      varchar(100) null
     )
GO
insert into [dbo].[test_data_1](username,websites) 
  values('Galderisi,Brandon', 'SQLServerNation.com,experts-exchange.com')
insert into [dbo].[test_data_1](username,websites) 
  values('Chapman,Tim', 'SQLServerNation.com,experts-exchange.com')
insert into [dbo].[test_data_1](username,websites) 
  values('Wills,Mark', 'SQLServerNation.com,experts-exchange.com')
insert into [dbo].[test_data_1](username,websites) 
  values('Smith,Joe', NULL)
GO

There are two variations of the APPLY operator. CROSS and OUTER. The CROSS APPLY operates just like an INNER JOIN. It will only return values from the table that yield results from the function. The OUTER APPLY operator, as you probably guessed, is like an OUTER JOIN. That means that all records from the table will be returned regardless of whether or not function returns data.

Below is the syntax for each, and we will then review the results.

select t1.*,f1.theValue from [dbo].[test_data_1] t1
  cross apply [dbo].[fn_DelimitedToTable](t1.websites,',') f1

select t1.*,f1.theValue from [dbo].[test_data_1] t1
  outer apply [dbo].[fn_DelimitedToTable](t1.websites,',') f1

The results show that each record returned from the function, yields a new row. As stated previously the CROSS APPLY operator will operate like and INNER JOIN. Therefore, the record for Jim Smith is omitted from the first result set because the value used as the input parameter is NULL and the function we are using only returns data if data is input.

I hope that you have found this useful. As always, if you have any questions about how this can apply to your situation, stop by our forums. If you would prefer you may also email me at BrandonGalderisi {AT} our website dot com.

create table [dbo].[test_data_1]
     (ident         int identity primary key clustered
     ,delimited     varchar(100)
     )
GO
insert into [dbo].[test_data_1](delimited) values('Galderisi,Brandon')
insert into [dbo].[test_data_1](delimited) values('Chapman,Tim')
insert into [dbo].[test_data_1](delimited) values('Wills,Mark')
GO

Relating SQL Server profiler with Performance monitor

2009-07-11T16:00:00Z

A DBA would often experience the need to explore performance bottlenecks, to create benchmarking reports and to overview the SQL Server performance as part of its daily existence. Any of the above (including the daily existence) can easily become a daunting task if the "tools of the trade" are not known and mastered. Hence, in this short article we will concentrate our attention on a very rewarding aspect of the Windows Server and SQL Server performance monitoring tools.

In this article I will not go in detail about how to start perfmon and Profiler, nor how to save the output to file. I believe this is a subject of a completely different article. What we would see here is how to build a bridge over the river of information flowing between both tools.

Here is what you will need for the purpose:

A perfmon generated file, in either *.csv or *.blg format
A Profiler trace, saved to disk as a *.trc file.
Plenty of good intention and sophistication when it comes to the gathered set of perfmon counters and Profiler events. Keep in mind that you have to know what you are looking for and get to it in the most efficient and modest way possible.

Let's suppose that we already have saved both files on disk and that we are ready to tackle the problem we are after.

1. Start SQL Server Profiler, and open the saved *.trc file.

2. Click on "File > Import Performance Data... " and you will be presented with a file browsing window like this one.

3. Point to the perfmon file (csv or blg) and click Open. Of course, the trace file and the perfmon file must have something in common. I.e. a trace which was stopped last month will not correlate with a perfmon file started today.

4. After you point to the perfmon file and click "Open", you will be presented with the following "Performance Counters Limit Dialog". As you can see, here you can select the performance counters which you are interested in and profiler will import them in correlation with the currently open trace. In an ideal situation you will select a few counters, thus making it easy to read and analyze.

5. After selecting the desired counters by checking the appropriate checkboxes, click OK, and viola! You will be presented with a colorful graphic of the perfmon counters' ups and downs within the Profiler.

6. Let's submerge us in a bit more detail: in the profiler now you see 4 different layers of information: trace events on top, the color diagram, the perfmon counter list and the actual Profiler's TextData field displayed on the bottom.

7. You have several options for exploring the information.

a. You can click on any line of the trace event and see the corresponding values of the perfmon counters

You can click on the perfmon's graphics and you will be taken to the profiler event which happened at that time.

You can right-click on one of the counters in the list and you can select "Go to max value" or "Go to min value"

You can review the t-sql in the bottom window

8. Don't forget that the world IS what you make out of it! It is up to your imagination what set of counters you will gather as well as what events and columns. Just make sure you use a server side trace and keep in mind that there is a way to gather perfmon counters remotely as well. (This is a topic for another article.)Think about the performance of your own performance measurements. Do not bring the system down, just because you wanted to know what is already bringing the system down.

Introduction to SQL Server Profiler

2009-05-28T00:12:00Z

How often do you find yourself with the need to know what statements are occurring on your SQL Server? Well, if you're like me the answer is "ALL THE TIME". Constantly Im asked about processes running on the server if things are slow. Even more importantly, I like to capture data from the system to use for trending purposes and to proactively diagnose performance problems before someone comes and tells me about it. Luckily, SQL Server comes with tools to capture such data, and to save it for later use. The tool is SQL Server Profiler, and it is wonderful for setting up trace files to save data so that you can report on the happenings on the server.

To start a new SQL Server Profiler session, you can click on the Tools menu and select it from the downdown (so long as you're not using SQL Server Express edition - Profiler does not come with Express).

Selecting Profiler from the menu brings up the SQL Server Profiler application. At this point, you'll want to create a New Trace. This can be done from the File menu, or by pressing Control + N.

A Bit about Traces
A trace is a SQL Server object that captures information regarding certain events that occur on the database instance. The information traces gather can be as simple as catching stored procedure executions to as complex as catching deadlock graphs. Whats great about them is that you can select certain columns of data as well as filter for certain events. These traces are absolutely imperative for any SQL Server DBA to use, and are extremely handy for diagnosing problems on the server, such as performance problems. SQL Server Profiler is a graphical user interface (GUI) for creating a grouping of traces. It makes it super easy to define them, alter them, and save them. However, if you want to define your own custom trace files through TSQL, I show you how in this article.

To run a new trace, you'll first need to connect to an instance of SQL Server. If you're using 2005 or 2008 a screen similar to the one below should appear. You can type your instance name (your server name if it is the default instance) or select your instance from the dropdown box.

Once I am connected to my server, I can define the Events, Columns and Filters I want to use in my trace. SQL Profiler comes with a handy set of predefined trace templates that you can use to capture common data. For the most part, these are great if you have a limited set of needs for your trace file. However, if you want more specific information you're going to need to define your own trace or alter some of the predefined templates.

In the trace properties screen I have the option to save the trace data to a file or to a table, or both. If you want to save the data you capture, here is a good place to do it.

Clicking on the Events Selection tab I can set filters for my trace events. For example, in the screenshot below I am setting criteria to only capture events where the event is intitiated by the LoginName "thedude". More realistic filters may include looking for logical Reads > 1000 or a duration of a query longer than 2 seconds.

In the same screen I can decide which columns of data are important to me. If I am capturing information regarding statements running, stored proc execution, etc then I want to know such informaiton as the name of the procedure, statement running, duration, reads, writes, etc. I can use this information to find those procedures which may be resource hogs.

I can also easily organize the order in which the columns appear. I can also group them, which I appreciate doing after I've captured the trace as it allows me to group the durations and reads of statements to easily identify longer running statements.

Below is a sample output of what statements were captured on my system while I was running the trace file. Notice the amount of detail that is showed through the trace. I am easily able to capture the Reads, CPU, SQL Statement, and Duration of the statement just through the use of the trace file.

Once you have your trace defined the way you want it, you have a number of options as to how you want to save it. You can save the results from the trace to a File (flat or XML) or a Table. I like to use the Table option since it allows me to view the data in a format to which I am accustomed to using, and so I can easily query it how I want. What is also great about the Save option is that I can save the definition of the trace file so that I can use it later. From there I can view the TSQL which comprises the trace so that I can save it later. I use this method when I want to quickly define a trace, and then create a stored procedure that runs on system start up to start the trace. Doing so make sure I can automatically capture events without having to go through the manual interaction to do so.

Conclusion
Today I took a broad overview of what SQL Server Profiler is, what it does, and what you can use it for. I hope it is a great place to get started opening the window to the world on your SQL Server. However, care must be taken when using the tool. If you try to capture too many events then you're risking slowing down your server because there is overhead in capturing the events. In a later writing, I plan to dig a little more deeply into these issues, and how you can use Profiler to diagnose some common performance problems on your server.

When an outer join becomes an inner join

2009-05-21T01:00:00Z

Enclosed you will find how an improperly constructed OUTER JOIN query can turn into an INNER JOIN.

An OUTER JOIN is described as a join where you want all data from one table along with any matching data from another table. In SQL Server the default behavior of using JOIN creates an INNER JOIN. But sometimes even if you use LEFT JOIN or LEFT OUTER JOIN, the query that you build may end up being an INNER JOIN.

The first thing we will do is setup some test tables. Before you critique the tables, realize that this is simple test data and is not what we would build for an employee employer table. The data sample below will add three employees. Two of those employees will be associated with employers in the #Employers table. Both of those employers will have a simple address.

Temporary tables:

set nocount on
go
create table #Employees
     (EmployeeId    int primary key clustered
     ,EmployerId    int null
     ,FirstName     varchar(20) not null
     ,LastName      varchar(20) not null
     )
create table #Employers
     (EmployerId    int primary key clustered
     ,EmployerName  varchar(30) not null
     )
create table #EmployerAddress
     (EmployerId    int primary key clustered
     ,EmployerCity  varchar(30) not null
     ,EmployerState varchar(3) not null
     )
go

Test data:

insert into #Employees(EmployeeId,EmployerId,FirstName,LastName)values(1,1,'Brandon','Galderisi')
insert into #Employees(EmployeeId,EmployerId,FirstName,LastName)values(2,2,'Tim','Chapman')
insert into #Employees(EmployeeId,EmployerId,FirstName,LastName)values(3,null,'Mike','Jones')

insert into #Employers(EmployerId,EmployerName)values(1,'Company A')
insert into #Employers(EmployerId,EmployerName)values(2,'Company B')

insert into #EmployerAddress(EmployerId,EmployerCity,EmployerState)values(1,'Anytown','USA')
insert into #EmployerAddress(EmployerId,EmployerCity,EmployerState)values(2,'Washington','DC')
go

Now let's look at the first simple query. This query simply selects all #Employees with the #Employer records that are available.

select * from #Employees ee
  left outer join #Employers er
    on ee.EmployerId = er.EmployerId

Now let's add in the #Employer_Address table.

select * from #Employees ee
  left outer join #Employers er
    on ee.EmployerId = er.EmployerId
  left outer join #EmployerAddress era
    on er.EmployerId = era.EmployerId

So far so good. The LEFT OUTER JOINs are working correctly still displaying all of the employees regardless of them having a listed employer. Now let's change it. Suppose you want to show all employees, but the employer only if they are in Washington. So how would you do it? Your first instinct may be to add criteria EmployerCity = 'Washington'. Let's see what that does.

select * from #Employees ee
  left outer join #Employers er
    on ee.EmployerId = er.EmployerId
  left outer join #EmployerAddress era
    on er.EmployerId = era.EmployerId
where era.EmployerCity = 'Washington'

What you will see that not only did it remove the Employer information, but it filtered down to only employees who work for that employer.

One way to solve this is to create a DERIVED table and JOIN to it. Since we only want employers who are in Washington, the DERIVED table will have an inner join on #Employers and #EmployerAddress.

select * from #Employees ee
  left outer join 
     (select er.EmployerId,er.EmployerName,era.EmployerCity,era.EmployerState
      from #Employers er
       INNER join #EmployerAddress era
         on er.EmployerId = era.EmployerId
      where era.EmployerCity = 'Washington'
     ) er
    on ee.EmployerId = er.EmployerId

What you will see is that now we have all 3 employees. But we only list the employer information for those in Washington.

I hope that you found this post useful and that it brings light to the fact that improperly constructed JOIN statements can yield undesired results.

The Numbers Table (Round 2)

2009-04-28T01:29:00Z

Originally I wrote about The Numbers Table over at SQL Server Central. Over there I presented the structure and a couple options on how to populate it. I won't dwell on all of that again. I'm just going to keep it simple.

What is a numbers table?

A numbers table is a table containing sequential integer values. The size of your table will vary depending upon how you may choose to use it. Most practical applications that I have encountered have never called for more than 100,000 records but we will be creating ours with 1,000,000 records just to be safe. Remember that the larger you choose to create your numbers table, the more space it will take up and the longer it will take to generate.

Where to put the table?

An argument can be made that that this table belongs in your user database. But if you have 10 user databases on your server, do you really need to numbers tables? That would also mean that every full database backup will have the extra space in it. This could quickly add up to a lot of wasted space both in your databases and in your backups. That's why I suggest keeping a single utility database per server. This single database can be used to store all of your utility functions, tables and procedures.

The Structure

The structure of a numbers table is very simple. It must only contain one column and it is the clustered index. For performance reasons, the table is created, populated, then the clustered index is applied.

create table util_nums
     (n     int not null
     ,constraint pk_util_nums primary key clustered(n) with fillfactor = 100
     )
go
alter table util_nums add constraint 
   pk_util_nums primary key clustered(n)with fillfactor =100

For populating this table, again I'm not going to dwell on the different ways to do it. So I will use the nested CTE method. It gets the job done, and it's fast enough. This method uses a series of cross joins in common table expressions to generate rows. The rows are then numbered using the row_number() function. This is the same SQL used in the A View of Numbers article, except it inserts it into the util_nums table.

;with   cte0 as (select 1 as c union all select 1),
       cte1 as (select 1 as c from cte0 a, cte0 b),
       cte2 as (select 1 as c from cte1 a, cte1 b),
       cte3 as (select 1 as c from cte2 a, cte2 b),
       cte4 as (select 1 as c from cte3 a, cte3 b),
       cte5 as (select 1 as c from cte4 a, cte4 b),
       nums as (select row_number() over (order by c) as n from cte5)
 insert into util_nums(n)
       select n from nums
       where n <= 1000000

A numbers table can have so many functions. Both the fn_CharsToTable and the fn_HashBytesMAX function can be updated to use the util_nums table instead of the vw_nums view. And in the future, all functions that I post on here will assume that you have the numbers table, instead of the vw_nums view.

Fast searching of large (n)varchar values

2009-04-02T05:00:00Z

In this Tip of the Week post, we will discuss how you can index a large data field to allow fast equality searching. What does this mean? It means that you won't be able to do indexed LIKE searches, but you will be able to do indexed = searches.

Why is this necessary? This is necessary due to the size limitation of indexes in SQL Server. SQL Server does not allow you to create an index on any field, or combination of fields, whose length is greater than 900 bytes. For varchar fields, each character is a byte. For nvarchar fields, each character is two bytes. That means that you cannot create an index on a varchar field that is greater than 900 characters, or a nvarchar field that is greateer than 450 characters. This can prove to be quite limiting. And performing a search on these fields will result in a clustered index scan.

The below trick uses a function previously covered in the topic Hashing large data strings with a User Defined Function to generate a hash of the data. The hashed column is then indexed so that searches can be done via the hash.

Note: All of the SQL in this article may be downloaded with the link at the bottom of the page.
      You will notice that the names look strange.  Even though we always recommend running
      our tests in a secluded environment, these names are meant to prevent name conflicts should
      you need to run them in an existing database.

Now let's get started by setting up all of the items necessary for this article.

create table [util_Nums_361A370A-D881-4612-BE1C-916BD466E884]
     (n   int not null
     ,constraint [pk_util_Nums_361A370A-D881-4612-BE1C-916BD466E884] 
      primary key clustered (n)
     )
go
;with   cte0 as (select 1 as c union all select 1), 
       cte1 as (select 1 as c from cte0 a, cte0 b), 
       cte2 as (select 1 as c from cte1 a, cte1 b), 
       cte3 as (select 1 as c from cte2 a, cte2 b), 
       cte4 as (select 1 as c from cte3 a, cte3 b), 
       cte5 as (select 1 as c from cte4 a, cte4 b), 
       nums as (select top 1000000 row_number() 
                                   over (order by c) as n from cte5)
insert into [util_Nums_361A370A-D881-4612-BE1C-916BD466E884](n)
select n from nums 
go
create function dbo.[fn_SplitEveryX_361A370A-D881-4612-BE1C-916BD466E884]
    (@InputString nvarchar(max)
    ,@Every int
    )
returns @Data table 
     (ident    int identity
     ,theData  nvarchar(max)
     )
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 01-April-2009
*    Purpose:       Splits an @InputString every @Every 
                    characters returning a table.
*
*************************************************************/

begin
     insert into @Data (theData)
    select substring(@inputstring, n , @every)
    from    dbo.[util_Nums_361A370A-D881-4612-BE1C-916BD466E884]
    where n <= (datalength(@Inputstring)/2)
        and (n-1)%@every=0
return 
end
go

create function dbo.[fn_hashbytesMAX_361A370A-D881-4612-BE1C-916BD466E884]
     (@string nvarchar(max)
     ,@Algo    varchar(10)
    )
    returns binary(16)
--WITH ENCRYPTION
as
/************************************************************
*
*    Author:        Brandon Galderisi
*    Last modified: 01-April-2009
*    Purpose:       uses the system function hashbytes as well
*                   as sys.fn_varbintohexstr to split an 
*                   nvarchar(max) string and hash in 8000 byte 
*                   chunks hashing each 8000 byte chunk,,
*                   getting the 32 byte output, streaming each 
*                   32 byte output into a string then hashing 
*                   that string.
*
*************************************************************/
begin
     declare    @concat       nvarchar(max)
               ,@NumHash      int
               ,@HASH         binary(16)
     set @NumHash = ceiling((datalength(@string)/2)/(4000.0))
    /* HashBytes only supports 8000 bytes so split the string if it is larger */
    if @NumHash>1
    begin
          ;with factors as 
          (select top (@NumHash) n,(n*4000)+1 factor 
           from [util_Nums_361A370A-D881-4612-BE1C-916BD466E884]
          )
         select @concat = 
               cast(
               (select right(sys.fn_varbintohexstr
                               (
                                hashbytes(@Algo, 
                                          substring(@string, factor - 4000, 4000)
                                          )
                               )
                            ,32)+''
              from Factors
               for xml path('')
               ) as varchar(max))

         set @HASH = dbo.[fn_hashbytesMAX_361A370A-D881-4612-BE1C-916BD466E884]
                                         (@concat,@Algo)
    end
     else
     begin
          set @HASH = convert(binary(16), hashbytes(@Algo, @string))
     end

return @HASH
end
go

create table [IndexedData_361A370A-D881-4612-BE1C-916BD466E884]
     (id       int identity not null primary key clustered
     ,theData  varchar(1000) not null
     ,theHash  binary(16) not null
     )
go
declare @TestData nvarchar(max) 

select @TestData = cast((
select char(n%255) from 
(select top 1000000 n from [util_Nums_361A370A-D881-4612-BE1C-916BD466E884] ) nums
for xml path('')
) as nvarchar(max))
insert into [IndexedData_361A370A-D881-4612-BE1C-916BD466E884](theData,theHash)
select theData, dbo.[fn_hashbytesMAX_361A370A-D881-4612-BE1C-916BD466E884](theData,'MD5')
from dbo.[fn_SplitEveryX_361A370A-D881-4612-BE1C-916BD466E884](@TestData,901)

go
create index [idx_IndexedData_361A370A-D881-4612-BE1C-916BD466E884_theHASH]
  on [IndexedData_361A370A-D881-4612-BE1C-916BD466E884] (theHash)

The above code will load 1110 records into the [IndexedData_361A370A-D881-4612-BE1C-916BD466E884] table. Now we will choose a value to search for and compare the execution plans.

To show the Execution Plan in SSMS
Click: Query > Include Actual Execution Plan
Or Press <CTRL>+M

declare 
      @dataToSearchFor varchar(1000)
     ,@hashToSearchFor binary(16)
select top 1  @DataToSearchFor = theData
from [IndexedData_361A370A-D881-4612-BE1C-916BD466E884]
order by newid()

set @hashToSearchFor = dbo.[fn_hashbytesMAX_361A370A-D881-4612-BE1C-916BD466E884]
    (@DataToSearchFor,'MD5')

select * from [IndexedData_361A370A-D881-4612-BE1C-916BD466E884]
where theData = @DataToSearchFor 

select * from [IndexedData_361A370A-D881-4612-BE1C-916BD466E884]
where theHash = @hashToSearchFor

The first thing to note is that the two result sets returned are identical. This is how we know that the search function returns the same data. Now let's look at the execution plans. We will ignore the first two Queries from the execution plan because they are just used to pick a value from "theData" column and get the hash for it. Since the goal is that you will be searching for a particular string of data, this will not factor into your production code.

The Query Cost of the two queries below do not add up to 100%, that's because of the cost of choosing a value. But what you will notice is that the first query, that simply queries the table looking in the NON-INDEXED "theData" column has over 10 times the Query Cost as the second. That is because SQL Server needs to scan the clustered index and compare every value to the value being searched for. The cost of the second query can be further broken down into it's two components. The first thing it does is an index Seek at a cost of 35%. This is the Seek of the index on "theHash" which retrieves the values of the clustered index. The second part, the Clustered Index Seek, has a cost of 65%. This is only in there because we did a select * from the table. If we were to limit the data being selected to the primary key (as we will show you next) the execution plan will change drastically. It is worth noting now that the costs of 35% and 65% are the cost relative to Query 4 and not the overall execution of the statement.

Next let's change the select statement to only return the Id column. The primary key. This is a more practical usage and will show how selecting unnecessary columns effects the overall performance of a query.

declare 
      @dataToSearchFor varchar(1000)
     ,@hashToSearchFor binary(16)
select top 1  @DataToSearchFor = theData
from [IndexedData_361A370A-D881-4612-BE1C-916BD466E884]
order by newid()

set @hashToSearchFor = dbo.[fn_hashbytesMAX_361A370A-D881-4612-BE1C-916BD466E884]
    (@DataToSearchFor,'MD5')

select id from [IndexedData_361A370A-D881-4612-BE1C-916BD466E884]
where theData = @DataToSearchFor 

select id from [IndexedData_361A370A-D881-4612-BE1C-916BD466E884]
where theHash = @hashToSearchFor

The first thing to verify is that your records returned are identical.

Then what you will now notice is that the cost of the query has been significantly reduced for the index seek. This is because the index contains the Id value as the primary key and therefore the Clustered Index seek that was being done previously no longer occurs. The reason why changing the "select *" to "select id" has no positive impact on the first query is that the Clustered Index is still being scanned. And since when the clustered index is being scanned all values are available, there is no performance increase (other than reduced I/O and network bandwidth to transfer the results) by reducing the selected columns.

Last but not least, the cleanup scripts.

drop table [IndexedData_361A370A-D881-4612-BE1C-916BD466E884]
drop function dbo.[fn_hashbytesMAX_361A370A-D881-4612-BE1C-916BD466E884]
drop function dbo.[fn_SplitEveryX_361A370A-D881-4612-BE1C-916BD466E884]
drop table [util_Nums_361A370A-D881-4612-BE1C-916BD466E884]

I hope that you found this article useful. It truly shows the power of indexes and provides a good workaround for some of the limitations of SQL Server.

If you feel that you may be able to make use of this method for searching large data strings, but are not completely sure of how exactly this can be implemented in your system, contact me and I can see how SQL Server Nation may be able to further assist you.

To download the full SQL Script used in this article, click here.

Comparing a Sub-Query and a UDF

2009-03-04T14:40:00Z

There has always been a lot of confusion in terms of performance between correlated sub-queries vs user-defined functions. So, I've taken it upon myself to try to figure out which one is more effecient. The results I have found are by no means conclusive, but will hopefully give you an idea as to when to use which approach as well as a general methodology for how to compare queries for effeciency purposes.

First, I'll start by creating a scalar user defined function that accepts the EmployeeID and the RateChangeDate and returns the SUM of the Rate field from EmployeePayHistory table in the AdventureWorks database. This is a pretty common type of scalar valued user-defined function (UDF) used to return month-to-date and year-to-date style data.

CREATE FUNCTION dbo.udf_GetPayHistory
(
    @EmployeeID INT,
    @RateChangeDate DATETIME
)
RETURNS MONEY
AS
BEGIN
    DECLARE @PayHistory MONEY

    SET @PayHistory = 
    (
        SELECT    SUM(Rate)
        FROM HumanResources.EmployeePayHistory eh
        WHERE 
            eh.EmployeeID = @EmployeeID AND
            eh.RateChangeDate >= @RateChangeDate
    )
    RETURN(@PayHistory)
END

In the code snippet below I query the HumanResources.Employee table in the AdventureWorks database and pass the EmployeeID and a static date to the function I defined above.

SELECT 
    EmployeeID, 
    LoginID, 
    dbo.udf_GetPayHistory(e.EmployeeID,'1/1/1997')
FROM 
    HumanResources.Employee e
WHERE 
    EmployeeID BETWEEN 100 AND 200

The statement below uses a correlated sub-query to return the same information as the statement above which uses the UDF to return data. The sub-query is correlated in that the HumanResources.EmployeePayHistory table access HumanResources.Employee table, which is table being queried at the outermost level. So, although this statement operates as a batch statement, you can almost think of it as processing iteratively. The inner query passing data to the outer query until the outer query satisfies its WHERE condition.

DECLARE @RateChangeDate DATETIME
SET @RateChangeDate = '1/1/1997'

SELECT 
    EmployeeID, 
    LoginID,
    PayHistory = 
    (
        SELECT    SUM(Rate)
        FROM HumanResources.EmployeePayHistory eh
        WHERE 
            e.EmployeeID = eh.EmployeeID AND
            eh.RateChangeDate >= @RateChangeDate
    ) 
FROM HumanResources.Employee e
WHERE EmployeeID BETWEEN 100 AND 200

If I run the statements together and view the Actual Execution Plan for the two statements, it is the sub-query approach that takes up 75% of the processing time. If I were to solely go by this information, I would think that the UDF approach to this solution is actually a LOT faster than the sub-query approach. To confirm this assumption, I'll need to run a couple of other tests.

Below is a comparison of the STATISTICS IO statement between the two statements. Because the two statements access the same two tables, I expect STATISTICS IO to show me the same information for the two statements, but this is NOT the case. The statement that uses the UDF only includes reads for the Employee table, while the statement that uses the correlated sub-query involves both tables. Clearly the 2nd statement is slower because it is showing more reads occurring on the database, right?

Next, I want to look at the exact time it takes for each of these statements to run. You always have to be careful when comparing time between statements. As a comparison metric, time is always dependent upon other factors on your database system at the time of running, such as load, memory pressure, etc. The figure can change with each run. However, since I'll be running these two statements at approximately the same time on the same system, I am comfortable with their approximate results.

Below is the screenshot for the subquery approach. This query only takes 1ms. I had run this query several times, so the execution plan had already been generated, which does help in execution time. This query seems pretty fast.

Below is the STATISTICS TIME for the UDF approach. Notice that using the UDF requires just a fraction more time. Ultimately, the time differences is insignificant since there is such a slight variation. Even though I ran the statements several times and received the same numbers each time, I'm still not really sure which statement is more effecient.

So, my next step is to look at the statement results in SQL Server Profiler. I'm mainly interested in the Reads and Duration columns from Profiler. The results from the subquery are below. The number of reads from this approach match up very closely to the results from STATISTICS IO, which is what I expect.

However, when I view the results from using the user-defined function, I get results that I am not expecting. The number of reads do not match up at all with what was returned from STATISTICS IO. The results returned from Profiler shows the ENTIRE number of reads for the query statement. The part that is misleading is that Profiler is showing all reads that the UDF incurrs. So, even though it is being showed as being called once, there are more reads involved in the database through its use.

Results

Today I looked at a performance comparison between using a correlated sub-query and a user-defined function (UDF). When I use the tools I am accustomed to using in terms of determining statement effeciency, I was misled. I had to dig a bit depper and break out SQL Server Profiler to really determine which statement was faster; the correlated sub-query. While the sub-query is faster, it does not promote code re-use like the UDF does, so there is a trade off. If I were to use the logic over and over again, I would consider the performance trade off of using the UDF so I didn't have to rewrite the logic over and over again. It really depends on the situation you're faced with to determine which approach to use.

How to allow multiple NULL values in a UNIQUE column

2009-02-25T19:42:00Z

SQL Server can guarantee unique values in two ways, Primary keys and a unique indexes/constraints. The primary difference between the two is that fields bound to a unique index or constraint can be NULL while a primary key field can not. For the purpose of the unique indexes and constraints, NULL values are treated equal. That means that you cannot have two NULL values.

Fortunately, there is a workaround for that will allow you to store multiple NULL values while ensuring that non-NULL values are unique. We will utilize a schema bound view in order to facilitate this. We will also create an index on the startdate column to help with the performance of the indexed view.

First let's create a table to test with. In order to prevent conflicts with existing tables and databases, we suggest that you create these objects either in TempDB or in a database specific for testing. In case you do not have the privileges to create your own database, we have added a partial GUID to all of the object names.

CREATE TABLE EMPLOYEE_9F2E916313EB
  (employee_id int not null identity(1,1)
  ,employee_lastname nvarchar(32) not null
  ,employee_firstname nvarchar(32) not null
  ,employee_startdate datetime null
  ,constraint pk_EMPLOYEE_9F2E916313EB primary key clustered (employee_id)
  )
GO
create index idx_EMPLOYEE_9F2E916313EB_startdate on EMPLOYEE_9F2E916313EB (employee_startdate)

While this may not be a very practical example, let's pretend that we want no two employees to have the same start date. But, we may not know the start date of certain employees so the column has to allow null values.

Next let's create the view and the UNIQUE CLUSTERED index.

CREATE VIEW EMPLOYEE_9F2E916313EB_vw_startdate
with schemabinding
as
select employee_startdate
from dbo.EMPLOYEE_9F2E916313EB
where employee_startdate is not null
GO
create unique clustered index uidx_EMPLOYEE_9F2E916313EB_vw_startdate_startdate 
  on dbo.EMPLOYEE_9F2E916313EB_vw_startdate (employee_startdate)

An important thing to note is that you must specify the two part name for the object. For SQL Server 2000 you need to specify the object owner, for SQL Server 2005 you must specify the schema. The key to making a schema bound view is the option WITH SCHEMABINDING in the views declaration. Also, an indexed view MUST have a clustered index created on it.

Now let's see how the database allows for multiple NULL values.

insert into EMPLOYEE_9F2E916313EB(employee_lastname,employee_firstname,employee_startdate)
values ('Smith','John' ,NULL)
insert into EMPLOYEE_9F2E916313EB(employee_lastname,employee_firstname,employee_startdate)
values ('Smith','Mike',NULL)
GO
select * from dbo.EMPLOYEE_9F2E916313EB

The result should look like this:

Now we will add more data that contains non-NULL dates.

insert into EMPLOYEE_9F2E916313EB(employee_lastname,employee_firstname,employee_startdate)
values ('Smith','Tim','2009-02-19')
insert into EMPLOYEE_9F2E916313EB(employee_lastname,employee_firstname,employee_startdate)
values ('Smith','Bob','2009-02-20')
go
select * from dbo.EMPLOYEE_9F2E916313EB

The results should look like this:

Now let's try inserting another record for 20-Feb-2009.

insert into EMPLOYEE_9F2E916313EB(employee_lastname,employee_firstname,employee_startdate)
values('Smith','Ralph','2009-02-20')
go
select * from dbo.EMPLOYEE_9F2E916313EB

You will see that the same four records still exist and you would have received the following error.

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.EMPLOYEE_9F2E916313EB_vw_startdate' with unique index 'uidx_EMPLOYEE_9F2E916313EB_vw_startdate_startdate'.
The statement has been terminated.

The schema bound view has prevented us from having multiple values of 20-Feb-2009 while still allowing multiple NULL values.

You must be aware that the schema bound view will prevent some operations being performed on the table. You can add new columns and drop existing columns but before you would be able to change the column referenced in the view, the view must be dropped. Below, the first two statements will work, but the third one will fail with the error below.

alter table EMPLOYEE_9F2E916313EB ADD login_name nvarchar(32) null
go
alter table EMPLOYEE_9F2E916313EB drop column employee_lastname
go
alter table EMPLOYEE_9F2E916313EB alter column employee_startdate datetime null

Msg 5074, Level 16, State 1, Line 1
The object 'EMPLOYEE_9F2E916313EB_vw_startdate' is dependent on column 'employee_startdate'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN employee_startdate failed because one or more objects access this column.

Cleanup scripts.

drop view EMPLOYEE_9F2E916313EB_vw_startdate
drop table EMPLOYEE_9F2E916313EB

Viewing SQL Server Jobs and History

2009-02-25T19:05:00Z

In SQL Server 2000, it was cumbersome to allow those who were not members of the sysadmin group the ability to view SQL Agent Jobs and job history through Enterprise Manager. SQL Server 2005 has made this a lot easier through the use of specialized database roles in the msdb database for viewing such information. In the scripts below, I get around these SQL 2000 restrictions with a couple of custom objects that allow you to view SQL Agent Job status and history.

First, I need to create a function that formats the job date related data from the SQL Server system tables so that it is in a format that is a bit easier on the eyes. I am creating this function in the msdb database because this is where the system tables are located. If you aren't already created backups of your msdb database, now is a really good time to start.

USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_GetJobDate]
(
 @LastRunDate INT,
 @LastRunTime INT
)
RETURNS SMALLDATETIME
AS BEGIN
      DECLARE @Date SMALLDATETIME
      SET @Date = 
        CASE WHEN @LastRunTime > 0
        THEN CAST((SUBSTRING(CAST(@LastRunDate AS VARCHAR(10)),
                    5, 2) + '/'
                    + RIGHT(CAST(@LastRunDate AS VARCHAR(10)), 2)
                    + '/'
                    + LEFT(CAST(@LastRunDate AS VARCHAR(10)), 4)
                    + ' '
                    + CAST(CASE WHEN SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + 
                            CONVERT(VARCHAR(6), @LastRunTime), 6), 1, 2) > 12
                      THEN SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + 
                            CONVERT(VARCHAR(6), @LastRunTime), 6), 1, 2) - 12
                      ELSE SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000') + 
                            CONVERT(VARCHAR(6), @LastRunTime), 6), 1, 2)
                    END AS VARCHAR(2)) + ':'
                    + SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000')
                            + convert(varchar(6), @LastRunTime),
                            6), 3, 2) + ':'
                    + SUBSTRING(RIGHT(STUFF(' ', 1, 1, '000000')
                            + convert(varchar(6), @LastRunTime),
                            6), 5, 2)) AS SMALLDATETIME)
        ELSE NULL
        END

      RETURN (@Date)
   END

Now that I have my function created in msdb for formatting the dates, I can create my stored procedure that pulls the job status and history. This procedure is also created in msdb, and accepts one parameter, the Job Name. You can provide the name of a specific job and it will give you job-specific details. If you omit the value for the @JobName parameter, it will return information for all jobs for the SQL Agent.

USE msdb
GO

CREATE PROCEDURE usp_GetJobInformation
(
    @JobName VARCHAR(255) = NULL
)
AS
BEGIN
    IF OBJECT_ID('tempdb..#Results')>0
        DROP TABLE #Results
   
    CREATE TABLE #Results
    (
        job_id                    UNIQUEIDENTIFIER NOT NULL,
        last_run_date             INT              NOT NULL,
        last_run_time             INT              NOT NULL,
        next_run_date             INT              NOT NULL,
        next_run_time             INT              NOT NULL,
        next_run_schedule_id      INT              NOT NULL,
        requested_to_run          INT              NOT NULL,
        request_source            INT              NOT NULL,
        request_source_id         SYSNAME   COLLATE DATABASE_DEFAULT NULL,
        running                   INT              NOT NULL,
        current_step              INT              NOT NULL,
        current_retry_attempt     INT              NOT NULL,
        job_state                 INT              NOT NULL
    )
   
    DECLARE @JobID VARCHAR(100)
    SELECT TOP 1 @JobID = job_ID FROM msdb.dbo.sysjobs
   
    INSERT INTO #Results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @JobID
   
    SELECT
        s.Name,
        CASE WHEN s.enabled = 0 THEN 'No' ELSE 'Yes' END AS Enabled,
        CASE WHEN next_run_date > 0 THEN 'Yes' ELSE 'No' END AS Scheduled,
        sc.name AS Category,
        current_step AS CurrentExecutionStep,
        msdb.dbo.fn_GetJobDate(last_run_date,last_run_time) AS LastRunDate,
        msdb.dbo.fn_GetJobDate(next_run_date, next_run_time) AS NextRunDate,
        CASE WHEN xp.running = 0 THEN 'Not Running' ELSE 'Executing...' END AS Status,
        ISNULL((
            SELECT 
                CASE 
                    WHEN run_status = 1 THEN 'Succeeded' 
                    WHEN run_status = 3 THEN 'Cancelled' 
                    WHEN run_status = 0 THEN 'Failed' 
                    WHEN run_status IS NULL THEN 'Unknown' 
                END AS LastRunStatus
            FROM
                msdb..sysjobhistory sho
            WHERE
                sho.job_id = xp.job_id AND
                sho.instance_id =
                (
                    SELECT MAX(instance_id)
                    FROM msdb..sysjobhistory sj (NOLOCK)
                    WHERE sj.job_id = sho.job_id
                )
        ) ,'Unknown') AS LastRunStatus
    FROM     #Results xp    
        INNER JOIN msdb..sysjobs s on xp.job_id = s.job_id
        INNER JOIN msdb..syscategories sc on s.category_id = sc.category_id
    WHERE
        s.Name = ISNULL(@JobName, s.Name)
    ORDER BY s.Name

    BEGIN
    IF OBJECT_ID('tempdb..#JobHistory')>0
        DROP TABLE #JobHistory
   

        CREATE TABLE #JobHistory
        (
            JobName SYSNAME,
            StepID INT,
            StepName SYSNAME,
            Message NVARCHAR(1024),
            RunStatus INT,
            RunDate INT,
            RunTime INT ,
            RunDuration INT    ,
            operator_emailed NVARCHAR(20),
            operator_netsent NVARCHAR(20),
            operator_paged NVARCHAR(20)               
        )

        INSERT INTO #JobHistory
        SELECT
            sjj.Name,
            sjh.step_id,
            sjh.step_name,
            sjh.message,
            sjh.run_status,
            sjh.run_date,
            sjh.run_time,
            sjh.run_duration,
            operator_emailed = so1.name,
            operator_netsent = so2.name,
            operator_paged = so3.name
        FROM
            msdb.dbo.sysjobhistory                sjh
            JOIN msdb.dbo.sysjobs sjj ON sjh.job_id = sjj.job_id
            LEFT OUTER JOIN msdb.dbo.sysoperators so1  ON (sjh.operator_id_emailed = so1.id)
            LEFT OUTER JOIN msdb.dbo.sysoperators so2  ON (sjh.operator_id_netsent = so2.id)
            LEFT OUTER JOIN msdb.dbo.sysoperators so3  ON (sjh.operator_id_paged = so3.id),
            msdb.dbo.sysjobs                 sj
        WHERE
        (sj.job_id = sjh.job_id)

        SELECT
            JobName, StepID, StepName, 
            Message, 
            msdb.dbo.fn_GetJobDate(RunDate, RunTime) AS LastRunTime,
            CASE RunStatus
                WHEN 0 THEN 'Failed'
                WHEN 1 THEN 'Succeeded'
                WHEN 2 THEN 'Retry (step only)'
                WHEN 3 THEN 'Canceled'
                WHEN 4 THEN 'In-progress message'
                WHEN 5 THEN 'Unknown'
            END AS RunStatus
        FROM #JobHistory
        ORDER BY JobName, LastRunTime DESC, StepID ASC
    END

END


GO

In terms of security for the above stored procedure, you'll need to add any user or group for which you want to run the procedure as a User in the msdb database. You'll also need to give this user EXECUTE permissions on the stored procedure.

Server Role Members

2009-02-25T15:53:00Z

Below is a quick and easy way to query SQL Server 2005 system views to determine which users belong to which server roles on the instance.

SELECT
      rolename = rolep.name,
      membername = memp.name
FROM
      sys.server_role_members rm
      JOIN sys.server_principals rolep ON rm.role_principal_id = rolep.principal_id
      JOIN sys.server_principals memp ON rm.member_principal_id = memp.principal_id

To determine which users are members of your database level roles on your instance, see this article.

Database Role Members

2009-02-25T15:52:00Z

Below is a quick and easy way to query SQL Server 2005 system views to determine which users belong to which database roles on your SQL Server databases.

SELECT
      rolename = rolep.name,
      membername = memp.name
FROM
      sys.database_role_members rm
      JOIN sys.database_principals rolep ON rm.role_principal_id = rolep.principal_id
      JOIN sys.database_principals memp ON rm.member_principal_id = memp.principal_id

To determine which users are members of your server level roles on your instance, see this article.