SimplyGold

Today marks the end of my not-so brief sabbatical from my blog; thanks to everyone who stuck along. I can promise some free templates not to mention some nice EE themes within the coming weeks.

Anyway, yesterday I was working on a stored procedure that required me to select a field from the table into a variable. I figured it had something to do with the SET keyword, but it took me a while to figure out the exact syntax:

DECLARE @VariableName INT

SET @VariableName = (SELECT COUNT(*) FROM Customers WHERE State = 'MN')

You know the one I'm talking about:

SqlError: The backup set holds a backup of a database other than the existing 'Pro_SSRS' database. (Microsoft.SqlServer.Smo)

For some reason, if you tell SQL Server to overwrite the existing database (look below), the restore works like a charm. The database that I was restoring to had the same name as the backup file, and I'm not so experienced with SQL Server as to know what other (if any) mistakes I was making.

This is, of course, ridiculously easy to do in almost any programming language on the market these days. But what if you want to do this in SQL Server? SQL Server doesn't provide a function for this, so we'll have to create our own. The most intuitive way to do it is to simply split the given phrase by whitespace and compare each word to the word that you want to search. But this is, of course, slow, especially as the phrase gets big.

I found a nice little function in the book Beginning ASP .NET 2.0 E-Commerce in C# 2005 by Christian Darie and Karli Watson. What they do is replace each instance of the word with a word that is one character longer. Then, the length of the new sentence - the length of the old sentence will give you the number of times the word appeared in the sentence.

An example:

• The word: "word"
• The sentence: "The word we're looking for is the word 'word.'"
• The replaced sentence: "The wordx we're looking for is the wordx 'wordx.'"
• Difference in lengths of the two sentences: 3, which is the number of times the word appears.

Pretty neat. ;-)

The reason to use the REPLACE function instead of doing it the normal way, of course, is because REPLACE is faster. The code, then, which I'm copying almost verbatim from page 174 of the book:

CREATE FUNCTION dbo.WordCount(

 @Word VARCHAR(20),
 @Phrase VARCHAR(1000))

 RETURNS SMALLINT

/* @BiggerWord is a string one character longer than @Word */
DECLARE @BiggerWord VARCHAR(21)
SELECT @BiggerWord = @Word + 'x'

/* Replace @Word with @BiggerWord in @Phrase */
DECLARE @BiggerPhrase VARCHAR(2000)
SELECT @BiggerPhrase = REPLACE (@Phrase, @Word, @BiggerWord)

/* The length difference between @BiggerPhrase & @Phrase is the number we're looking for */
RETURN LEN(@BiggerPhrase) - LEN(@Phrase)

COALESCE is a cool little function whose name/purpose I keep forgetting/confusing with other cool functions.

Anyway: COALESCE returns the first non-null value in a list:

• PRINT COALESCE(900, NULL, 10, NULL, 40)

The result: 900.

• PRINT COALESCE(NULL, NULL, 10, NULL, 40)

The result: 10.

Note that at least one value must be non-null. For example:

• PRINT COALESCE(NULL, NULL, NULL)

will result in an error — "At least one of the arguments to COALESCE must be a typed NULL."

The Problem

You want to insert 5000 records into the database. You normally do this by creating a separate insert statement for each record, and this is fine with your boss, but you’re a glutton for punishment and want to optimize this scenario because it occurs often enough in your application. On the other hand, you’re also lazy, have ten thousand interesting things to do, and would love to not have to spend a lot of time tackling the problem. Oh, and you’ll be using stored procedures because, as everyone knows, they’re better than dynamic SQL. So? What do you do?

Enter OPENXML

OPENXML is a nice little function that will allow us to solve the above-mentioned problem without feeling like we’re hacking our way through it. The need for a hack wouldn’t arise if we can just send any number of parameters that we want to the stored procedure; i.e., in .NET, we have the params[] array, and in Javascript, we have the args array, but in stored procedures, we don’t have any such luxury.

The code, then, of the stored procedure that bulk-inserts data into a table using OPENXML:

1. CREATE procedure [dbo].[openxml_test]
2.  
3. @xmlDocument text
4.  
5. as
6.  
7. declare @handle int
8.  
9. exec sp_xml_preparedocument @handle output, @xmlDocument
10.  
11. begin
12.  
13. set nocount on;
14.  
15. insert into openxml_test_db(ID, random_data)
16.  
17.      select tid, tdata from openxml(@handle, N‘ROOT/Thread’, 2)
18.  
19.      with (tid uniqueidentifier, tdata varchar(100))
20.  
21. exec sp_xml_removedocument @handle
22.  
23. end
24.  
25.  
26.  

The XML structure is going to be of the form:

1. <ROOT>
2.  
3. <Thread>
4.  
5. <TGuid>cf69e831-4b82-43bc-bc0c-00013af696d3</TGuid>
6.  
7. <TData>An awful lot of data</TData>
8.  
9. </Thread>
10.  
11. </ROOT>

What We’re Doing

Line 3: Sending the variable as text is less effecient than sending it as varchar(8000). If you’re reasonably sure that your xml (including the tags, data, and all) that you send to the stored procedure isn’t going to exceed 8000 characters in length, I suggest you use that instead.

Line 9: sp_xml_preparedocument is a system stored procedure that, as its name implies, prepares the xml document. This stored procedure needs to be called before a call to openxml.

Line 17: We’re getting the data from the xml document and inserting it into the table. The @handle is the handle returned by sp_xml_preparedocument, the‘ROOT/Thread’ is the XPATH that tells OPENXML which rows to process, and the 2 describes the mapping between OPENXML’s rowset and the XML Document. This last parameter is optional. The default mapping is attribute-centric mapping, which we would use if our XML structure was like the following:

1. …
2. <TGuid value=”The guid goes here” />
3. <TData value=”An awful lot of data” />
4. …

Line 19: We’re basically telling how to format the data we get from OPENXML.

Line 21: The XML Document that SQL Server created is stored in memory. To avoid memory leaks, we close the handle.

So, What Kind of Savings Can We Expect?

Two things to notice:

1. The savings become more and more significant as the number of records we’re inserting becomes larger.
2. Even when we’re inserting only 50 records, the difference is noticeable (about .34 seconds).

Transactions allow you to batch a set of SQL so that all of them either succeed or fail together.

In .NET, it's especially easy to create transactions — using SqlTransaction.Suppose you have a monkey object and a fingers object. When you create a monkey, you would of course want to create the monkey’s fingers, as well. And so, you first create the monkey object, get the ID of that object, and then go create the fingers.

The code might go something like this:

public void CreateMonkey() { Monkey monkey = new Monkey();

monkey.Id = Guid.NewGuid();

//Set other monkey data here

bool result = Insert(monkey);

if (result) { Fingers fingers = new Fingers(10);

monkey.Hand.Fingers = fingers;

result = Insert(monkey.Hand.Fingers);

if (result) Console.WriteLine("Success! Good job!");

else Console.WriteLine("Failure! Dismal, just dismal!"); } }

You’ll run into problems when you use this kind of code, as might be obvious to you already. Suppose you successfully created the monkey, but for some odd reason, creating the fingers failed. What now? You have a monkey without fingers running around, which is sad for the monkey and creepy for us humans.

Yes, this is exactly where transactions come in. Transactions let you batch SQL statements together so that either all of them succeed or all of them fail. So, if you were using transactions, you’d either get “no monkey and no fingers” or “a monkey with fingers.”

.NET 2.0 makes it especially easy to use transactions. ;-) I recently had to work with them, and I was impressed with how intuitive they were.

When you want to use a transaction, you basically need to follow the following steps:

1. Create a SqlTransaction object
2. Create a SqlTransaction object
3. Link the transaction to the SqlConnection before running the first SQL statement of the batch.
4. Link the transaction to the SqlCommand object of each of the SQL statement of the batch.
5. If everything succeeds, commit the transaction. If there’s an error, rollback the transaction.

So, the modified code:

public void CreateMonkey() { //Need to initialize it to null because otherwise .NET will // complain that we're using an uninitialized object. SqlTransaction tr = null;

SqlConnection cn = new SqlConnection ("ConnectionString");

try { cn.Open();

tr = cn.BeginTransaction();

string sqlInsertMonkey = "Insert Monkey into database";

SqlCommand cmd = new SqlCommand(sqlInsertMonkey, cn, tr);

if (cmd.ExecuteNonQuery() == 0) throw new Exception("Failed to insert monkey");

string sqlInsertFingers = "Insert Monkey's fingers into database";

cmd = new SqlCommand(sqlInsertFingers, cn, tr);

if (cmd.ExecuteNonQuery() == 0) throw new Exception("Failed to insert fingres");

cmd.ExecuteNonQuery();

//Everything executed successfully, so commit the //transaction tr.Commit();

//Close the SqlConnection cn.Close(); }

catch (Exception ex) { //We need to check this, because the exception might //not be SQL related at all; i.e., the transaction //could have completed successfully and we could //be here because of some other problem. if (tr != null) { tr.Rollback(); cn.Close(); }

//Show the appropriate error message here } }

It’s that easy! :-)