When a column in a row contains a NULL what does this mean? So what is a NULL? Is a NULL value a zero, space, or something else? From a SQL Server perspective a NULL is not a value, it only means that a value was not provided when the row was created. Special considerations needs to be taken into account when dealing with nulls in SQL Server. This article will discuss some programming issues to be aware of when working with nulls, and some different techniques that can be used when dealing with nulls.
 read more ...

Have you ever wanted to measure how busy your SQL Server machine is at any given moment? By busy I mean what is the current CPU utilization.This tip will show you how to use T-SQL to calculate the percentage of CPU being used by SQL Server.
 read more ...

Have you every needed to get the information returned from a stored procedure (SP) into a SQL Server table? Or have you wondered how you might get the contents of an operating system (OS) file into a SQL Server table? How about placing the output of some Windows executable into a SQL Server table so you can manipulate it with T-SQL in some way? So what methods can you use to accomplish these different, but similar tasks? This article will show you how insert rows into a SQL Server table from various sources.
 read more ...

How many applications do you have that capture a status field for one reason or another? Do these status fields support only a single status value at a time? Or does the single status field support multiple different statuses being associated with a given record at the same time? This article will discuss how to use an integer field to support having multiple statuses assigned to a records at the same time.
 read more ...

SQL Server does not maintain a row number when it stores new rows in a table. Although sometimes you need to return a set of rows from SQL Server and present them with a record number associated with each row. So how can this be done? This tip will show you a couple of methods to accomplish assigning a row number to each row in a result set.
 read more ...

When you are monitoring the performance of your SQL Server machines what tools do you use? I’m guessing most DBA’s use the Windows Performance console to graphically display performance counters for their SQL Server machines. How do you determine the performance trends over time on your SQL Server machines? Where do you store all the performance data that you gathered when monitoring SQL Server machines? Do you save it in SQL Server database tables? This article will show you how to use the Performance console to set up a counter log to gather performance data for SQL Server 2000. I will also discuss how to load the performance data into a SQL Server database table. Another point I will cover will be how to use some Windows XP modules to automatically start your performance counter logs on a Windows 2000 server.
 read more ...

How many times have you had problems sending SQL MAIL or SQL Agent Mail from SQL Server because the Exchange server was taken down or not working? Have you ever tried to connect up SQL Mail and/or SQL Agent mail to Exchange through a firewall or across domains, only to find out that you can’t use your Exchange in these situations? Maybe you don’t have Exchange but you would still like to setup SQL Server to send email. If you have experienced these problems then you might want to consider using POP3 and SMTP services for supporting your SQL Mail and SQL Agent mail requirements. This article will discuss how to setup SQL Server to use POP3 and SMTP services to support SQL Mail and SQL Agent mail.
 read more ...

If you need to make a minor database schema change, and you know the change will affect a number of stored procedures and views, then how can you determine which objects are affected? What if you are changing the length, or format of a single column, how do you find all the views, rules, default, triggers, CHECK constraints, DEFAULT constraints, and/or stored procedures that reference this single column? This tip will show you one technique for finding the objects that might be affected by your database change.
 read more ...

Are you a Database Administrator that has a desire to work remotely but don’t have access to your databases while you are out of your office? Would you consider using a web interface to manage your SQL Server databases and Logins? If so, then you might want to consider checking out Microsoft’s tool that will allow you to perform some SQL Server administration functions via the web.
 read more ...

Have you ever wanted to monitor activity on your SQL Server machines on an on going basis, but have heard that running Profiler causes a lot of overhead? How about gather SQL Server usage statistics over time, so you can do some kind of trend reporting, or change back method, based on the resources that customers actually use? If so then you should consider running server-side traces. This article will discuss what is server-side tracing, why it is more efficient than Profiler and how to use it to gather SQL Server 2000 usage information into a physical file.
 read more ...

Have you ever had a need to run a query or a process as soon as SQL Server starts? Possibly you want to run a cleanup or copy routine each time SQL server is started.  Well if so then here are a couple of options you might considering using to accomplish just that.
 read more ...

One of the tasks a DBA must consider is developing a backup strategy for their databases. A key piece of this strategy is to ensure that a complete set of the most recent database backups are available should there be a hardware failure. This article will discuss a method of how to use ALERTs, a SQL Agent job and a stored procedure (SP) to create a copy of your critical database backups on another physical machine as soon as the database backups are created.
 read more ...

This is the second article in a series discussing benchmarking T-SQL query performance. The first article in this series discussed different methods of capturing the elapsed time of a T-SQL batch, pieces of a batch, or a single T-SQL statement (http://place URL for first article here). This article will discuss how to determine the amount of CPU and I/O used when a given T-SQL statement or a series of statements are executed.
 read more ...

If you have ever had the need to show detailed data of individual transactions and also keep a running total, subtotals, and grand total columns at the same time, but were not exactly sure how to tackle the problem then this article might help. In this article I will show you a few different techniques for calculating and summing information on multiple rows without using a cursor. The techniques I will show you will just use a basic SELECT statement. Of course, the calculations of the running total, subtotals and grand total will be done using other SQL clauses and functions like SUM and CASE.
 read more ...

How many times have you had more than one way to do something and wanted to determine which method was faster and/or which one uses more resources. I am going to guess that most developers have run across this dilemma more than once. So how do you decide with method is faster? Or which method requires more resources? In this two part series I will discuss some of the methods I use in Query Analyzer (QA) to help me decide which coding method might be faster and use less resources
 read more ...

The CASE function is a very useful T-SQL function. With this function you can  replace a column value with a different value based on the original column value. An example of where this function might come in handy is where you have a table that contains a column named SexCode, where 0 stands for female, 1 for male, etc., and you want to return the value “female” when the column value is 0, or “male” when the column value is 1, etc.. This article will discuss using the CASE function in a T-SQL SELECT statement. 
 read more ...

If you have a need to write code that will process a given command against each Microsoft SQL Server database or all tables in a given database then you might consider using a couple of undocumented stored procedures, sp_MSforeachdb and sp_MSforeachtable. Have you been building complicated cursor while loops to process through a list of databases, or a list of tables? If you have then maybe you have been writing to much code. Using the sp_MSforeachdb and sp_MSforeachtable stored procedures can simplify the code you need to process through all databases and/or all tables. Don’t be deceived by the sp_MSforeachtable stored procedure (SP) name, this SP can process through database objects other than just tables. This article will discuss how to use these two undocumented stored procedures.
 read more ...

Occasionally you need to build dynamic T-SQL that not only requires the T-SQL code to be dynamic, but requires the dynamic T-SQL to return values from the dynamic code to the calling T-SQL code. There are a number of different ways to write and execute dynamic code. For the purpose of this article I will be using the sp_executesql stored procedure (SP) for executing my dynamic code. In this article I will show you how to use the sp_executesql SP to allow a T-SQL coder to set variables in the calling T-SQL code when executing dynamic T-SQL.
 read more ...

T-SQL has a lot of functionality available out of the box from Microsoft. Although sometimes you might not be able to find the exact functionality you need using one of the stored procedures or extended stored procedures provided by Microsoft SQL Server. This is especially true when you want to find a T-SQL command or store procedure (SP) to perform a specific task against an operating system file or files. If you need this kind of functionality there are alternative. One of these alternatives is to extend what you can do with T-SQL by launching a Visual Basic script from within T-SQL code.
 read more ...

This short article discusses a method where you can submit a Stored Procedures asyncronously.  This examples uses OLE Automation and WScript to accomplish this.
 read more ...

At some point you will have some business logic that will require you to process sequentially through a set of records one record at a time. For example you may have a list of databases, and for each database you may want to build a command that will perform some process against each database. Or you might have a set of records where you want to process through each record one at a time, so you can select additional information from another table based on the information contained in each record. This article will discuss two different ways to process through a set of records one record at a time.
 read more ...

If you want to write some SQL Server code that will return the output of a stored procedure (SP) into a table, then this tip is for you. You may want to do this for a number of reasons. One of the reasons I commonly do this, is so I can process through the SP output in my code, one record at a time.
 read more ...

This is the second article in my T-SQL programming series. This article will discuss building a program loop using T-SQL. In addition to talking about building a loop, I will also discuss way of controlling the loop processing, and different methods to break out of a loop.
 read more ...

This article shows you different options for how to script objects from SQL Server. Here I show how to use Enterprise Manager GUI, SQLDMO Script Method, and scptxfr.exe.
 read more ...

This is the first of a series of articles discussing various aspects of T-SQL programming. Whether you are building a stored procedure or writing a small Query Analyzer script you will need to know the basics of T-SQL programming. This first article will discuss defining variables, and using the IF…ELSE logic.
 read more ...

Every now and then you need to take the current date and calculate some other date. For instance you might have an application that needs to determine what date is the first is the first day of the month, or need to know the last day of the month. Now most of you probably already know how to take the date a part into its piece (year, month, day, etc.) and use those pieces along with a number of functions to calculate a date that you might need. In this article I will be showing how to use just the DATEADD and DATEDIFF function to calculate a number of different dates you might need to use in your applications.
 read more ...

Ever want to be alert when SQL Server drive space falls below a specific threshold of available free space? Maybe you wanted to do this so you could be proactive regarding limited database disk space, before no disk space causes a major database problem. If so then you need to know about the undocumented xp_fixeddrive extended stored procedure.
 read more ...

Ever need to get the output from a SELECT statement into a file? If so, then here is a tip that will show you how to use the “osql” command to create a file that contains the output from a SELECT statement.
 read more ...

Microsoft SQL Server 2000 does not support a method of identifying the row numbers for records stored on disk, although there are a number of different techniques to associate a sequential number with a row. You might want to display a set of records might where each record is listed with a generated number that identifies the records position relative to the rest of the records in the set. The numbers might be sequential that start at 1 and are incremented by 1 for each following record, like 1,2,3,4, etc.. Or in other case you may want to sequentially number groupings of records where each specific set of records are numbered starting at 1 and incremented by 1 until the next set is reach where the sequence starts over. This article will show a number of different methods of assigning a record sequence number to records returned from a query.
 read more ...

As part of the normal work a Database Administrator (DBA) will be required to migrate databases between servers. One of the reasons for a migration might be caused, because you are moving an application from a quality assurance (QA) environment to a production environment. Another reason might be your current database server hardware has reached the replacement date and you need to migrate your databases from the current, out-dated server  to a new server. Yet another reason might be you are migrating your application from SQL Server 7.0 to SQL Server 2000. For what ever the reason you will more than likely have to deal with migrating not only the data, but the SQL Server logins that access that data as well....
 read more ...

Ever want to return operating system file information via a SQL Server stored procedure (SP), or Transact SQL (TSQL). If so, then let me tell you about the undocumented SQL Server xp_getfiledetails extended SP....
 read more ...

A common question I hear is how can I run some SQL Server transact SQL (TSQL) code to determine how much of the allocated DATA disk space is being used by my database. Well there is a way to do this by using the undocumented SQL Server “DBCC SHOWFILESTATS” command....
 read more ...