SQL Server Blog

How To Use Index Hints in SQL Server 2005 and 2008

2010-03-29T18:17:00Z

Sometimes the SQL Server query optimizer does not pick the best index to use in a join operation. This can be caused by a multitude of factors (out of date statistics, wrong indexing strategy for a table, etc) ... for what ever reason it happens, there is a way to force SQL Server to pick the correct index at runtime. This is called an INDEX HINT; if you have ever used a table hint, this is similar to one of those. The syntax is fairly simple, immediately proceeding the table being named in the join, you type with(index(INDEX_NAME)) and this tells SQL Server that when it comes time to join this table in, the named index must be used.

Below is a sample, previously the execution plan for this query was using a compound query that had OrgId as the first named column. Use of this index was making SQL Server do an Index Scan and then a Hash Join. After adding the index hint it was using the right index and doing a Clustered Index Seek and Nested Inner Loop Join. Perfect.

alter view OrgProductsView as
select [OrgCatalogs].[OrgId], [Products].[ProductId], [Catalogs].[CatalogId], [Catalogs].[CatalogTypeId], [AdjustedPrice], [ProductTypeId]
from [OrgCatalogs]
inner join [Catalogs] on [OrgCatalogs].[CatalogId] = [Catalogs].[CatalogId]
inner join [CatalogProducts] on [Catalogs].[CatalogId] = [CatalogProducts].[CatalogId]
inner join [Products] with(index(IX_Products_ProductId)) on [CatalogProducts].[ProductId] = [Products].[ProductId]

How to select only the date part of a datetime field in SQL (SQL Server 2000/2005/2008)

2010-01-29T05:19:00Z

Have you ever needed to get only the date part out of a datetime field in SQL and wanted to drop the precision of the timepart? It's actually surprisingly easy, and with this method is pretty fast as well. I've had to do this many times but always forget the syntax in T-SQL (though this should also work on other database platforms as well) so I figured I would put it up here on the blog and hopefully it would come in handy for some of you guys as well!

SELECT (CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) AS CurrentDate

You can, of couse, replace the call to getdate() with a column from a table that you are selecting from and it will work just as well. The performance hit for using this method is neglible ... especially considering the alternative of casting the calling YEAR(), MONTH() and DAY() on the field and concatenating them back together as strings.

SQL Server 2000/2005/2008 UDF to return a csv list of values from a query

2009-06-11T18:22:00Z

Today I had to do a common task that I've done several times, but suddenly realized I should put up here, because its a fairly common issue. Say you have TableA has a one to many relationship to TableB, and you want all of the values in TableB.dbo.ColumnA to be in a csv list based on the FK. This is the perfect place for a simple UDF!

create function [dbo].[udf_ocrd_getProgList](@siteid nvarchar(17))
returns varchar(max)
with execute as caller
as
begin
      DECLARE @ProgTypes nvarchar(max)
      SELECT @ProgTypes = COALESCE(@ProgTypes + ', ', '') +
         CAST(np.progname AS nvarchar(65))
      from ocrd.progsite ps
      inner join ocrd.nprograms np on ps.programid = np.programid
      inner join ocrd.sites sites on sites.siteid = ps.siteidlnk
      where ps.siteidlnk = @siteid
      return (@ProgTypes)
end

and then to execute it...

select sitename, dbo.udf_getProgList(siteid)
from ocrd.sites
where siteid = '20030626152018383'

And it will return "Site Name Here", "Value 1, Value 2, Value 3"

How to find all tables with a column of a particular name in SQL Server 2005 and 2008

2009-02-13T07:37:00Z

This is a simple query for finding all instances of a column with a particular name along with the table that it belongs to. This will not work on SQL Server 2000.

select sys.objects.name as TableName, sys.columns.name as ColumnName
from sys.columns
inner join sys.objects on sys.objects.object_id = sys.columns.object_id
where sys.columns.name = 'countyid'

How to use the row_number function in SQL Server 2005 and 2008 for server side data paging in T-SQL

2009-01-29T18:59:00Z

This technique is fairly handy for server side data paging, it essentially executes the statement server side, then grabs a subsection of the rows (that you specify) and only returns those to the client. This will reduce network traffic pretty considerably, and subsequently speed up your applications response times if network chatter is a problem.

with employeeDataSlice as
(
select *, row_number() over(order by EmployeeID asc) as displayRowNum
from [HumanResources].[Employee]
)
select * from employeeDataSlice where displayRowNum between 1 and 5

How to create a table with a non clustered primary key in SQL Server 2000 2005 and 2008

2009-01-24T20:54:00Z

Unless otherwise specified when you create your tables primary key in SQL Server 2000, 2005 and 2008 will have a clustered index on it. This will be a good default choice if you are going to be joining other tables together on this value. If you are going to be joining or selecting based on another value in the table, then you should probably use a nonclustered primary key. Here is how you do it in T-SQL at the time of table creation:

create table DemonstrationTable
(
TableIdColumn int identity not null primary key nonclustered,
TableValueColumn nvarchar(255)
)
go

Changing a clustered primary key to non-clustered in SQL Server 2000/2005/2008

2009-01-19T03:25:00Z

By default, when you create a primary key column in SQL Server, it makes it a clustered index. Sometimes this will be a default advantage, sometimes a serious disadvantage for performance as clustered indexes are the cornerstone of performance tuning in SQL Server. In the event that a clustered primary key gets created and you need to change it to a non-clustered one, follow these steps:

Open the table in design view in SQL Server Management Studio (right click on the table in the object explorer, click 'Modify')
Right Click anywhere over the column definitions, from the context menu click 'Indexes/Keys'
Select your primary keys index by name in the right listbox
In the right pane, under the section titled 'Table Designer' find the item labeled 'Create as Clustered' and change the 'Yes' to 'No'.
Click close, then close the design view of the table, and choose the option to save.
If you have full text catalogs on this table you may need to repopulate them.

That should be it, now you are able to create a clustered index on another column or set of columns on your table.

What does schemabinding mean in SQL 2005 and 2008?

2008-12-02T16:06:00Z

In order for us to be able to create an indexed view, we need to create that view with SCHEMABINDING specified. What exactly does SCHEMABINDING mean? Here is the definition from MSDN:

SCHEMABINDING
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Microsoft SQL Server 2005 Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

SCHEMABINDING cannot be specified if the view contains alias data type columns.

What does this mean?

Well, to put it simply, once you create a view with schemabinding, you cannot change the underlying tables in a way that would break the view. Examples of this would be removing columns or dropping tables that are specified in the view.

Creating an Indexed View in SQL Server 2005 and 2008

2008-11-26T22:33:00Z

As you probably are already aware, a view in SQL Server is generated off of a pre-defined select statement, and is a flexible solution to use when ever you are doing database driven application development; particularly when you are using an ORM tool (like SubSonic or EntitySpaces as examples). However, for performance reasons there is an additional piece you should be aware of and this is what is known as an "indexed view".

On the surface, an indexed view is the same thing as a normal view, however the critical difference is that an indexed view actually allows you to create a clustered index on it, effectively working around the "one clustered index per table" limitation. The downside to this is that the indexed view is a full copy of the data from the underlying tables, so for space considerations you will need to take this into account when using them.

Let's look first at how to create a normal view, so that we have something to compare to as we create an indexed view

create view ContactsView as
(
      select [Users].[FirstName], [Users].[LastName], [Contacts].*
      from [Users]
      inner join [Contacts] on [Contacts].[UserID] = [Users].[UserId]
);

What this will do is create a pretty flexible view that will only pull the first and last name of the user from the users table, and all of the columns from the Contacts table and automatically pick up any new columns that are added to the Contacts table, with no further modifications. Indexed views require a more rigid definition of what they will return. For this reason, we cannot use a wildcard (*) in the underlying query. Each column must be individually called out; also, each table named in the query must be in 2 part dot notation, referencing both the schema of the table and the table name itself, and the view must be declared with schemabinding enabled for it.

Below is the modified query to create a view capable of being an indexed view:

create view [dbo].[OrganizationContactsView] with schemabinding as
(
      select [FirstName], [LastName], [Contacts].[UserID], [Contacts].[ContactDescription], [Contacts].[IsPublic]
      from [dbo].[Contacts]
      inner join [dbo].[Users] on [Contacts].[UserID] = [Users].[UserId]
);

Now, we need to create the clustered index on it. Clustered indexes on a view must be unique, otherwise you will not be able to create one; nonclustered indexes will work fine. For the present example, a UserId will only be in the contacts table once per Organization, so we can make a unique clustered index spanning the OrgId and UserId columns, like this:

create unique clustered index IX_OrganizationContactsView_OrgId on [OrganizationContactsView](OrgId,UserId)

And, there we are, a clustered index!

Writing Your First CLR Stored Procedure

2008-10-31T23:25:00Z

In SQL Server 2005 and 2008 the Common Language Runtmie (CLR) is tightly integrated with the database engine, allowing you to write stored procedures in any .NET language such as C# and VB.NET. CLR stored procedures have some distinct advantages over traditional stored procedures, and they also have their drawbacks. In the world of application developers who use languages like C# and the world of database developers who use T-SQL, there are different definitions of "maintainability", and ultimately it will come down to what camp most of your development staff are in.

If you have a comfort level or more staff for database maintenance, then traditional stored procedures will fit into your architecture more easily, and CLR stored procedures will augment where specifically needed. However, when used properly CLR stored procedures will be a powerful tool in your development arsenal. The drawbacks to them are that they are deployed very differently (i.e. you can't run a create script in Management Studio and be done with it; you need to either deploy the assembly through Visual Studio to a database, or physically copy the assembly to the database server, and then register it manually in Management Studio, also you do lose some tracing data that you would normally see inside of SQL Profiler. Some of the more appropriate times to use them however are during operations that require high speed text manipulation, cryptography, and remotely accessing resources outside of SQL Server (such as calling a webservice to geocode address data).

Before we begin with creating our first CLR Stored Procedure, we need to enable the CLR in the database that we want to deploy the procedure to; to do this you can reference my previous posting on How to Enable CLR Stored Procedures.

Step One: Createing the CLR Procedure Project

To create the initial project, click on File -> New-> Project; then select Visual C#-> Database-> SQL Server Project

Let's name the project CLRStoredProcedures, and click ok. During the project creation phase, you will be prompted to add a reference to a database for the current project, create one to the AdventureWorks database if you don't already have one, and select that.

Step Two: Add a Stored Procedure to the Project

Next we need to add stored procedure class to the project, where we can add our methods. To do this, right click in the Solution Explorer, select "Add", and then select "Stored Procedure..."

After the item is added to the project you will be looking at a blank stub method in a C# class file, where you can customize the code to do what you need.

Now, let's go ahead and change the code in this file, so we can do something productive with it!

First, we should change the method name to something useful, in this case we can use the name "GetCustomersByTerritoryID". Guessing based on the name, we will need to be passing in an argument, TerritoryID to our procedure, so also add an argument to the function called "TerritoryID" and of type int. Below, is the code for the procedure, you can copy and paste it, and I will explain line by line what is going on.

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetCustomersByTerritoryID(int TerritoryId)

{

SqlPipe pipe = SqlContext.Pipe;

        SqlCommand cmd = new SqlCommand("select * from [Sales].[Customer] where [TerritoryID] = @TerritoryID");
        cmd.Parameters.AddWithValue("@TerritoryID", TerritoryId);
        using (SqlConnection con = new SqlConnection("context connection=true"))
        {
            try
            {
                cmd.Connection = con;
                con.Open();
                pipe.ExecuteAndSend(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con != null)
                    con.Close();

            }
        }
    }

Ok, once we get inside the declaration of the procedure, the first line is SqlPipe pipe = SqlContext.Pipe; this is the mechanism that we have available to us to send any results back to the client. I am going to make some basic assumptions that you have experience working with ASP.NET or data access with C#, and can already see the similarities here. We start off with instantiating a SqlCommand object, giving it a query to execute that is properly parameterized, and opening a database connection. Now, notice here that it does have a very different connection string that we are used to. It does not specify a server, database, username or password... or for that matter anything we normally see in a connection string. That is because we are already connected to the database and are already authenticated, so none of that is necessary. We simply set the "Context Connection=true" flag, and we're set!

The next big difference you see is that we are calling the ExecuteAndSend() function on the pipe object, and passing in the SqlCommand object as an argument; doing this executes the command and sends any subsequent results back to the caller.

Step Three: Build, Deploy, and Execute!

Now that we have the code already laid down, the final step is upon us, we first build the class file by clicking "Build" on the top menu, then choose the option to build it, then "Build" again, and choose deploy. After this says "Deploy Successful" in the status pane, we jump over into Management Studio, open up a query window to the AdventureWorks database, and try executing it

And that's it! We are done with our first CLR Stored procedure, you can call this like you normally would any stored procedure, and better yet, you can take advantage of the .NET framework from inside of SQL Server 2005 and 2008 from languages and platforms other than ASP.NET! ColdFusion, Classic ASP, PHP, all of them can benefit from this integration with no requirements on the application side! Attached to this post is the actual C# class file used in this tutorial.

How to enable CLR Stored procedures on SQL Server 2005 and 2008

2008-10-27T01:53:00Z

In order to make CLR stored procedures run, you must first enable the CLR in SQL Server, other wise you will just get this error:

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Enabling the CLR is fairly simple, just run this SQL Statement in Query Analyzer, while connected to the database that needs this option enabled:

EXEC sp_configure 'show advanced options' , '1'; 
reconfigure; 

EXEC sp_configure 'clr enabled' , '1' ;
reconfigure; 

EXEC sp_configure 'show advanced options' , '0'; 
reconfigure;

Making the case for stored procedures

2008-10-26T07:23:00Z

On a few of the mailing lists I am on for developers (primarily ones related to web development), there is a recurring topic that always gets people into rather heated debates: to use stored procedures or prepared statements? Now, either one you choose to use, is largely dependent on two things: preference/comfort level, and the appropriate tool for the appropriate job. This will be my attempt at defining (my opinion of) the right tool for the right job, while allowing for maximum flexibility.

Now, for those who are not aware of the difference, a prepared statement is straight SQL sent to the db for execution, but with explicitly declared parameters so as to prevent SQL injection attacks. The techniques on how to do this vary from language to language, but in ColdFusion it is through the use of the <cfqueryparam> tag, and in .NET, you use the SqlCommand object, type of text, and where you want the parameter values, you place a “?”, then add the parameters in the proper order.

Now, prepared statements are not a bad thing at all; they offer some of the same benefits of stored procedures: explicit parameterization, elevated chances of execution plan reuse, saves overhead on the db side of having to do type checking/conversion, etc. One additional benefit of using prepared statements, it’s a heck of a lot easier to write secure dynamic SQL for complex query building.

However, stored procedures are a far better choice 99% of the time; complex dynamic SQL being the sole exception that I would consent to allowing in any database I was in charge of administering or code being checked in by a developer who worked for me. Stored procedures have several distinct advantages over prepared statements (or inline SQL in general) , and some of them become more apparent only after having built large, complex, enterprise level applications that have multiple developers and support staff working on them.

Guaranteed execution plan reuse
As long as you aren’t doing dynamic SQL within your stored proc and concatenating strings for execution, absolutely no chance of a SQL injection attack happening
Performance increases for saving on permissions checks – when you submit a prepared statement for execution the database server needs to check the permissions chain on every object that that query touches, EVERY time it executes. This is not the same as an execution plan. With a stored procedure you create the procedure and GRANT EXECUTE for that proc to the Id that your web application connects to the database as.
Carefully controlled access to data – sometimes your data has sensitive information (like Social Security Numbers, secret question/answer information, etc) and while you have to both store that information, and maintain a level of security around it so that not everyone in the IT department has access to it, you create a stored proc, and enforce access to the data through stored procedures. This method also gives you the ability to give partial access to data (such as a redacted SSN, instead of the full SSN)
Modularity of code – complex logic for doing multiple operations to data in the database is easily ported from one process to another. As an example, one application I routinely work on has 3 different methods for creating a user (new user auto-registration, administrative interface for creating them by uploading an excel file, and one that creates them via a scheduled batch job from excel files uploaded by clients) and there is some pretty complex logic for finding duplicate users across multiple clients and preventing new users from being created who exist elsewhere. Since this piece of code is in the stored procedure, adding new sources and tools to create users is dead simple… call the stored procedure and that is that. One argument that I heard to try and minimize the impact of this advantage “what if you had a single, well architected application written in a single language and the query was easily available from other sections of code within the application, why is there a modularity advantage for this? Well, if you have a single language in your application, and its easily accessible everywhere else, it does lower the points this one gives you, but that was kind of my point here, it lends flexibility even if you don’t anticipate needing it now.
Minimizing the amount of traffic sent over the wire. Sometimes as developers we think 10Mbps/100Mbps/1000Mbps Ethernet has so much bandwidth that sending this little tiny 20 line query over the wire has no impact and won’t cause any appreciable slowdown. Well, that is true for the most part, but wait till your application starts getting a ton of traffic… the more packets you send over the wire, the higher your collision rate on the network, and you’ll see reduced network efficiency, sad but true; also one of the most often overlooked reasons for using stored procedures.
Wise choices in location of your business logic will greatly reduce your round trips to the database when using stored procedures. If you are performing an operation that can be completed from a solid set of parameters from start to finish and the end result (success/failure) is the only thing you need to know, a stored procedure is what you need. Since this was just used as an example I’ll use it again: say you are creating a user you have first name, last name, email, address, list of positions, departments, and supervisors. If you have ALL of that logic in your middle tier and not in a stored procedure, you will have to keep going back and doing lookups for data, making more round trips to save, add, and then complete. All of these round trips add to network overhead, time spent encoding/decoding data to send across the wire, process... locate, return… lots of data, lots of network chatter. This would all be eliminated with a stored procedure, you would feed one set of data to the proc, and execute it, and everything will be handled server side and the result handed back to you.
Centralization of maintenance, changes, security, and stronger separation of tiers.
Ease in separation in application tiers for troubleshooting. If you write a stored proc, use it in your application, and suddenly start seeing weird results in the application, you can go, execute the proc, and eyeball the results… do they look weird? No? Then the chances are good the problem lies in your application layer, start looking there. Many years of supporting messy applications and code taught me this lesson all too well.

Getting a range of values from an arbitrary starting point in T-SQL

2008-08-07T17:40:00Z

Recently a friend of mine called with a question about how to do something in T-SQL that he wanted to do for the leaderboard for a game he wrote. The issue was that tens of thousands of people are playing his game (on the iPhone) and the screen real estate only allowed for about 10 players scores to be displayed. The present method only displayed the top 10 scores, and did not include the current players score, so they never really saw where people were in relation to them.

He wanted something a bit more relevant to the player. The solution was to display the closest 9 scores in relation to the player, 4 greater than, and 5 less than. This is the solution I presented to him on a conceptual basis, actual implementation may vary and you would probably want to create an indexed view to run this query against with a clustered index on the column containing the players score and the players UserId, adding other columns as necessary to display the data you want. This example will run as is without creating any tables or making any temp tables (uses only Table type variables).

declare @PlayerScores table (ScoreValue int)
declare @TempScores table(ScoreValue int)
declare @currentScore int 

set @currentScore = 0

while (@currentScore < 101)
      begin 
            set @currentScore = @currentScore + 1
            insert into @PlayerScores (
                  [ScoreValue]
            ) values ( 
                  /* ScoreValue - int */ @currentScore )  
      end 

set @currentScore = 50

insert into @TempScores (
      [ScoreValue]
) 
select top 5 [ScoreValue] 
  from @PlayerScores
 where [ScoreValue] > @currentScore
 order by [ScoreValue] asc
insert into @TempScores (
      [ScoreValue]
)  
select top 5 [ScoreValue] 
  from @PlayerScores
 where [ScoreValue] <= @currentScore
 order by [ScoreValue] desc


select * 
  from @TempScores
 order by [ScoreValue]

The Basics of Database Normalization - Explained

2008-07-30T22:05:00Z

Data Normalization, refers to creating a structure to store your data in that results in reduction/elimination of redundant data. In a pure normalized form, a piece of data exists only in one place. Anywhere that the data is displayed is only making a reference to that data and joining tables together to recreate a human readable data structure. You gain several things from having a well normalized database schema, and you lose some things. Things that you gain are forced integrity of your data, everything belongs to something, attribute values are enforced to be in a known set of choices, and things are generally more clean and structured. Things you lose.. simplicity. The process of normalization results in generation of more tables, and so querying against data stored in these becomes more complex as you need to start doing joins and knowing what data will be there, what data might not be there; you can sometimes start getting very odd results if you are not careful.

As with many other subjects, there is a set of vocabulary terms that are used to express complex concepts succintly; data normalization is no different. There are varying degrees of normalization, each one being progressively more strict about what conditions must be met to attain a given level; these are referred to as "Normal Forms".

First Normal Form - typically abbreviated as 1NF, this is the most basic form, stating simply that a table must enforce that each row be unique, and not allow nulls in any column. So, an example to illustrate this lets take a table that most of you will have worked with at one point or another: Addresses. We will define the structure of the table as such:

create table Addresses
(
	AddressId int identity(1,1) not null primary key,
	StreetAddress1 nvarchar(100) not null,
	StreetAddress2 nvarchar(100) not null,
	City nvarchar(100) not null,
	State nvarchar(100) not null,
	Zip nvarchar(100) not null,
	IsMailingAddress bit not null,
	IsBillingAddress bit not null,
	IsPrimaryAddress bit not null	
)
go

This table adheres to the basic definition of 1NF, it has an identity column that has a unique key on it, which by definition, enforces that the row will be unique, even if the data is duplicated in every other column. If we changed the table to allow the IsMailingAddress (or any other column for that matter to contain a null value, then this table would no longer be properly normalized. There is some degree of contention about 1NF though; some people argue that Dr. Edgar F. Codd (the man who worked for IBM and invented the idea of relational data models) stated that null values must be allowed in a relational database. If you go with this concept, you will never be able to achieve a fully normalized database. For the sake of this discussion though, we will avoid a detailed explanation of this so as to keep things in scope.

Second Normal Form - also referred to as 2NF, this states that, first, the table must meet the guidelines of 1NF to be considered 2NF compliant. Further, that all columns in the table must rely on the key to express their uniqueness as a row in the table. Lets think about it like this if you have a table [Employees] with the columns [EmployeeId],[EmployeeName], and [Skill], and that a row in the table relied on a combination of EmployeeId and Skill to express uniqueness as a row, then the table is not meet the guidelines for 2NF. Due to the fact that EmployeeName does not depend on Skill, it only depends on EmployeeId (other employees can have the same skill, but other employees cannot have the same EmployeeId)

Third Normal Form - 3NF is the more commonly used structure when designing a new data model. It states that first a table must meet the requirements of 2NF, and goes one step further to state that all transitive or otherwise extraneous attributes have to be eliminated, and split off into what are commonly called Type tables. Let's look at again at the table above; notice how we have 3 columns that are attributes of an address IsMailingAddress, IsBillingAddress, and IsPrimaryAddress. These 3 columns are transitive properties, and would need to be removed from this table. We would first create a table AddressType, and have the columns AddressTypeId, AddressTypeName, and perhaps an AddressTypeDescription column. Next, we would create a table AddressTypes (note, these naming conventions can be adapted to best suit you, this is simply a convention that I have become accustomed to and ultimately I prefer a verbose name that directly implies what type of data we are working with), this table would have only two columns AddressId, and AddressTypeId. The goal of this is to only explicitly state what types the address IS, not also specify what they are NOT. Since not every address is a billing address, its extraneous data... we just don't care.

In most cases, 3NF is the most you will ever need to know about. There are additional ones though, but their definitions are difficult to explain in concise terms to those new to database design, so I will address those later in detail. Briefly though, they are Boyce-Codd Normal Form (BCNF), Fourth Normal Form (4NF), Fifth Normal Form (5NF), and Sixth Normal Form (6NF).

Engineering Managers: Top Ten Things Your DBA Should Be Doing

2008-06-24T17:28:00Z

Recently I got a request for a top 10 list of things that a DBA should be doing on a daily basis from an engineering managers perspective. Well, here is what I came up with, and please note, that this list is dependant on the poor guy being a full-time DBA, not some programmer who also doubles as a DBA as many of these tasks can be fairly time intensive depending on the size of the SQL Server installation and number of servers/databases/jobs that the DBA has to maintain.

1.) Ensuring backups are running (and complete successfully)

Recently a round of SQL injection attacks put our backup/recovery strategy to the test and uncovered a few flaws that we hadn't really encountered before. Namely, applications that continuously try and maintain connections to the database (note: a database should be in single user mode to do a recovery properly as the restore process needs to have exclusive access)

2.) Ensuring storage space/RAM is not a concern

Here at my job, when we launch a new site it's usually (these days) a statewide deployment, with the states Governor, senators, lobbyists, and other political figures present, which means lots of news coverage, promotional material distribution, and the like. Which always results in a sizable spike in traffic for about a month afterwards, then it normalizes again but is on average higher than it was before. Starting late last year, this spike in traffic made the SQL Server logs grow exponentially and eventually took down the site due to a non-proactive system administrator and no full-time DBA. It's a good idea to keep this kind of thing as automated as possible. As a matter of fact, all new HP Servers come with Insight Manager which can do this for you; I'd suggest finding someone capable to configure it for you.

3.) Proactively monitoring database performance

This is somewhat of an open ended statement, and can mean a lot of different things to different people. Work with your DBA to determine a set of metrics that are important to you for knowing what's going on in the database. Starting with SQL Server 2005, there is a rich set of data from the DMVs (Dynamic Management Views) available for snapshots into the internals of SQL Server. If you cannot find anything to start with take a look on MSDN for information on whats available, from there you will get some ideas. Some suggestions to start are the Top 100 Most resource intensive queries on your system. Start with the queries that are utilizing the highest amount of IO, as disk access is the slowest part of a database usually.

4.) Ensuring that a usable disaster recovery plan is in place

This takes time, resources, planning, and people dedicated enough to the job to want to do it in off hours and on weekends, otherwise you could have some upset customers when your site goes down for "Testing". It is pretty important though, as who wants to be the one to tell the Boss that the site won't come back up cause you can't figure out why the database restore software gives a generic error message? I didn't want to, but I was that guy!

5.) Ensuring an adequate set of tools are available and are familiar with their functionality

Every profession has its set of tools and utilities that make life easier. Database professionals ... are... well... often left out. However, there are some good tools out there for them, you just have to look for them! Red-Gate is an excellent starting point, their one product, SQL-Prompt is a godsend for people who do a lot of T-SQL development and use SQL Server Management Studio. IntelliSense for T-SQL! YAAY! (note: SQL Server 2008 Management Studio has this built in when connected to a 2008 Server)

6.) Ensuring that all database changes submitted by developers are reviewed and signed off on

This is also somewhat open-ended, and is dependant on your organization having a structured change management process. Let's face it though, not all developers take the time to really understand how to properly program in SQL, and may not be making best use of existing indexes, so make sure your DBA looks over all the new SQL code before it goes to production. It can save time on hard-to-locate performance hits later.

7.) Identifying rapidly growing objects in the database (transaction logs, tables, etc)

This goes hand in hand with #2, however, this is more of a proactive approach, usually automation techniques will let you know when you're starting to approach a pre-determined threshold. Doing this proactively, you will know when that time is coming, and you can have a strategy in place to handle it.

8.) Ensuring scheduled tasks are running (and complete successfully)

Backups, data cleansing, ETL tasks, all of them. No one likes coming in in the morning and finding an application down because a nightly dataload did not complete properly or a partner did not upload it. Be ready to roll back any partial updates!

9.) Ensuring the disaster recovery plan actually works

I reiterate because I love you. No, really, I do. I mean it. Call me?

10.) Be familiar with what your platform can do

Sounds simple enough, but I can't tell you the number of times I've interviewed potential candidates, and they couldn't tell me the difference between an inner join and an outer join. But a DBA that can't tell me what Profiler is? Nah.. not acceptable. If nothing else, know the toolsets that came with the product like the back of your hand, sometimes they're too generic for a specific task, but I will lay money on them being useful 99% of the time!