T-SQL: A Simple Example Using a Cursor

In SQL Server the cursor is a tool that is used to iterate over a result set, or to loop through each row of a result set one row at a time. It may not be the best way to work with a set of data, but if you need to loop row by agonizing row (RBAR) in a T-SQL script then a cursor is one way of doing it.

Note: If you are new to SQL Server and come from an Oracle background, you should know that cursors on SQL Server are different from those on Oracle.

Before creating the cursor, we will just start with a simple query that will end up being used in the cursor.


USE AdventureWorks2008;
GO

SELECT BusinessEntityID, Name
 FROM Sales.Store;

Which looks something like this:

SimpleCursor1

Now to convert it to a cursor, instead of just a select statement.

Step 1: Declare variables to hold the output from the cursor.

</p>
DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);

Step 2: Declare the cursor object;


DECLARE @BusinessCursor as CURSOR;

Step 3: Assign the query to the cursor.


SET @BusinessCursor = CURSOR FOR
SELECT BusinessEntityID, Name
 FROM Sales.Store;

Step 4: Open the cursor.


OPEN @BusinessCursor;

Step 5: Fetch the first row.


FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;

Step 5: Loop until there are no more results.  In the loop print out the ID and the name from the result set and fetch the net row.


WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END

Step 6: Close the cursor.


CLOSE @BusinessCursor;

Step 7: Deallocate the cursor to free up any memory or open result sets.


DEALLOCATE @BusinessCursor;

Now putting it all together:


DECLARE @BusinessEntityID as INT;
DECLARE @BusinessName as NVARCHAR(50);

DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
SELECT BusinessEntityID, Name
 FROM Sales.Store;

OPEN @BusinessCursor;
FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;

WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT cast(@BusinessEntityID as VARCHAR (50)) + ' ' + @BusinessName;
 FETCH NEXT FROM @BusinessCursor INTO @BusinessEntityID, @BusinessName;
END

CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;

SimpleCursor2

This should give you a quick overview of how to quickly build and use a cursor on Microsoft SQL Server. The example shown was run on SQL Server 2008, and works the same on SQL Server 2005 or 2012.

Enjoy!

Installing Sample Databases for SQL Server 2008R2

As I work on testing the Database Health Reports project, I have configured several test servers running as virtual machines using Hyper-V. Part of setting these up for testing, I usually end up installing the sample databases for SQL Server. The sample databases give me something to test against, and what usually ends up happening is I end up polluting the sample database over time with lots of test tables, bad indexes, and all the stuff that you should keep in a test database, and never actually put in a production system. Occasionally I need to wipe the server clean and start over, so when I do this I usually end up re-installing the sample databases. I do this just often enough to forget exactly where to get the sample databases from. To solve that problem I have created this blog posting so that I have somewhere to look next time I need the sample databases.

Step 1. Download the sample files from Codeplex.

There are a couple different versions of sample files available. I usually use the AdventureWorks 2008 OLTP Script. The OLTP (OnLine Transaction Processing) sample doesn’t include the data warehouse samples, and it is just quick and easy to use.
Download from here:
http://msftdbprodsamples.codeplex.com/releases/view/89502
AdventureWorks is the name of the sample database, which replaces the old Pubs database from earlier versions of SQL Server, and the NorthWind sample database from SQL Server 2005.
Download the a zip file containing the scripts needed to install AdventureWorks. The filename should be AdventureWorks 2008 OLTP Script.zip.

Step 2: Run the scripts.

To run the scripts either unzip the file and save the contents somewhere.
AdventureWorks1

Then browse into the AdventureWorks 2008 OLTP Script directory.

AdventureWorks2

Open the instawdb.sql script with SQL Server Management Studio.

AdventureWorks3

You will want to enable Full Text Search before running the script.

SQLCMD mode will need to be enabled to run the script. If the script is run without SQL Command mode, the following errors will be generated. To Enable SQLCMD mode, choose the SQLCMD Mode option from the query menu in SSMS.
AdventureWorks4
If the script is run without setting up the directories correctly the following error will be displayed.

AdventureWorks5
A fatal scripting error occurred.
Variable SqlSamplesSourceDataPath is not defined.

To fix the error, just set up the directories edit the 2 lines at about lines 36 and 37, first uncomment them, then change the directories to match the directory of your SQL Server, and the directory that the samples were downloaded to as shown in the following directory.

AdventureWorks7 Once the directories have been edited, then just run the script. The script may run for a few minutes depending on your server speed. If the script runs successfully it will generate the following output.

AdventureWorks8

Then refresh the database tree in the object explorer and the AdventureWorks2008 database will show up. Go ahead and browse the tables and take a look around.

AdventureWorks9

Not exactly a straightforward process, but if you follow the steps you will get The AdventureWorks 2008 database running on SQL Server 2008R2.
Enjoy

Using a CTE to Split a String Into Rows with Line Numbers

Last year while working on my CTE presentation for a SQL Saturday I added a blog post called “Using a CTE to Split a String Into Rows“, and since that posting I have used it many times.   But as things go in development, eventually there is a need to do something more.

Doing some complex string building to create files lately I cam across the need to use a CTE to split strings into rows but to also include line numbers.  To accomplish that I first started with the same function that I used last year, but renamed it, and added a second output column called LineNumber, here is what it looked like:


CREATE FUNCTION dbo.SplitWithLineNumber (@sep char(1), @s varchar(max))
RETURNS table
AS
RETURN (
 WITH splitter_cte AS (
 SELECT CHARINDEX(@sep, @s) as pos,
        cast(0 as bigint) as lastPos,
        0 as LineNumber
 UNION ALL
 SELECT CHARINDEX(@sep, @s, pos + 1),
        cast(pos as bigint),
        LineNumber + 1 as LineNumber
 FROM splitter_cte
 WHERE pos > 0
 )
 SELECT LineNumber,
        SUBSTRING(@s,
                 lastPos + 1,
                 case when pos = 0 then 2147483647
                      else pos - lastPos -1 end) as chunk
 FROM splitter_cte
 );

The things that have changed since the last post “Using a CTE to Split a String Into Rows“, are the following:

  • I added another column to the output called Line Number.
  • Modified the input to be varchar(max).
  • Adjusted the case statement to accommodate varchar(max).
  • Cast the positions as bigint’s in order to accomodate varchar(max).

So now when you run this on the original input, you get the following:

SELECT *
 FROM dbo.SplitWithLineNumber (' ',
          'the quick brown dog jumped over the lazy fox')
OPTION(MAXRECURSION 0);

CTEtoSplitaStringIntoRowswithLineNumbers

Just a small change, but if you need to lave line numbers on the output, this will do it for you.


How BIG is too BIG for a BIGINT IDENTITY?

After writing a recent blog post on Are you wasting half the capacity of IDENTITY, I started thinking about how much is too big for an BIGINT IDENTITY.

If you don’t know about, you can get Are you wasting half the capacity of IDENTITY 9,223,372,036,854,775,807 IDENTITY values out of a BIGINT in SQL Server.  Which in easier terminology is 9 with roughly 18 digits after it, which works out to 9 quintillion (us) or 9 trillion for the rest of the world.  Translating again that is roughly 9 billion billion, which is a huge amount of IDENTITY values.

Now if you consier the article of Are you wasting half the capacity of IDENTITY you can get just about 18.5 quintillion rows out of an identity column.  I know as DBA’s and programmers we all like to plan big, but there are very few real world cases where you would need more than 18.5 quintillion rows out of an IDENTITY COLUMN.

So I ask the question, what are you doing where you need more than 18.5 quintillion different id’s in a table?  Perhaps its time to rethink the table design if it is getting that big.

SQL Server 2008 – Filtered Indexes

SQL Server 2008 introduces a new feature called Filtered Index. A filtered index is a way of adding a WHERE clause to the index definition to get faster access to a subset of the data. For instance if you have a huge table where you are typically only accessing a small part of the table, you can add a filtered index for that small part and save time by having a smaller (filtered index) to access the data that you are looking for.