SQL Server 2005 XQuery and XML-DML - Part 3

"Improving XML Update Performance with SQL Server 2005"

This is the third and final article in a series of three that look at how the latest version of Microsoft’s enterprise-level database, SQL Server 2005, now offers great support and close integration with XML as a data persistence format. This includes new ways to validate, store and query XML documents that are stored within the database. SQL Server 2005 provides native support for XML that can vastly improve application performance, while supporting robust and safe multi-user access to the data contained within the XML documents.

The topics we covered in the first article were:

A brief overview of the way that SQL Server 2005 stores XML documents and schemas
How SQL Server 2005 provides support for querying and manipulating XML documents
A simple test application that allows you to experiment with XQuery

In the second article of the series, we looked at:

Extracting data from xml columns
Comparing traditional XML data access approaches with XQuery
Combining XQuery and XSL-T

In this final article, we'll be exploring:

Updating the contents of xml columns
Comparing traditional XML update techniques with XQuery
Using XQuery in a managed code stored procedure

About the Example Applications

As well as the XQuery Tester application described in the first article, you can also use several other pages provided in the downloadable samples to see XQuery in action. The full list of these examples is:

Reading XML Values the Traditional Way
Using a Simple XQuery with an 'xml' Column
Comparing the Traditional Way and XQuery
Using Parameters with an XQuery
Combining XQuery and XSL-T
Modifying an 'xml' Column with XQuery
Comparing Traditional and XQuery Updates
Using a Managed Code Stored Procedure in SQL Server 2005

We won't be listing all of the code in this article, because much of it uses standard ASP.NET and ADO.NET techniques to create the pages and connect to the database. What we will concentrate on is how the examples exploit the new XML data management features in SQL Server 2005. Many of the examples use an un-typed xml column so that the queries are easier to assimilate (no namespace declaration or prefixes are required). However, some processes (such as the modify method when replacing values in the XML) require a typed column, and so the script we provide creates two tables - one named Store with an un-typed xml column and one named StoreTyped with a typed xml column. You'll see both used in the examples.

Traditional XML Update Techniques versus XQuery

In previous articles, we've only looked at how you can use XQuery to select and extract data from an xml column in SQL Server 2005. The next area of investigation is to see how we can use XQuery to update values in an xml column. This example, "Modifying an 'xml' Column with XQuery", provides three different SQL statements that use XQuery to update values in the Demographics column of both the typed and un-typed xml columns in the sample database.

Figure 1 shows the example page, and you can see these three statements - we'll look at them in detail shortly. There is also a list of customers generated by a GridView and a SqlDataSource control, allowing you to select a customer to see the update applied. If you select the third statement, a text box is displayed where you can enter a new value for the bank name.

Figure 1 - Updating an XML document in SQL Server 2005 using XQuery

Each query in this example extracts the XML document from the Demographics column of the row that was just updated, and the code in the page then displays this document so that you can see the result. We'll show you these results as we look at each of the update statements in turn. The code to execute each query is shown in the next listing. It extracts the selected query statement from the ASP.NET RadioButtonList control, creates a command, and adds to it a parameter for the customer ID - taken from the selected row in the GridView control.

If the user selected the third statement, which updates the BankName element within the XML document, the new value for this element is extracted from the TextBox control in the page and used to set another parameter on the command. Then the query is executed, and the updated XML document returned from the Demographics column is displayed in a Label control on the page. To make it easier to see the results, the code inserts line breaks between each element in the XML so that it wraps in the page:

Using con As New SqlConnection(ConfigurationManager.ConnectionStrings _

("XMLTestConnectionString").ConnectionString)

Dim cmd As New SqlCommand(optQType.SelectedValue, con)

cmd.Parameters.Add(New SqlParameter("@custid", Int32.Parse(grid1.SelectedDataKey.Value)))

If optQType.SelectedIndex = 2 Then

cmd.Parameters.Add(New SqlParameter("@newname", txtNewName.Text))

End If

Try

con.Open()

Dim sResult As String = Server.HtmlEncode(cmd.ExecuteScalar())

lblMessage.Text = sResult.Replace("><", "><br /><")

con.Close()

Catch ex As Exception

lblMessage.Text = ex.Message

End Try

End Using

The Query that Inserts a New Element

The first query we provide inserts a new element into the XML document in the Demographics column. It executes the XML-DML insert statement within a call to the modify method of the Demographics column, specifying that the XML fragment <newnode>Some value</newnode> should be inserted as the first child of the StoreSurvey root element:

UPDATE Store SET Demographics.modify('

insert <newnode>Some value</newnode> as first into /StoreSurvey[1]

WHERE CustomerID = @custid

Figure 2 shows the result. You can see the new element in the XML document at the top of the page:

Figure 2 - Inserting a new element into an XML document in SQL Server 2005 using XQuery

This statement - and the delete statement shown next - access an un-typed xml column in the Store table. If we were accessing a typed xml column, we would have to declare the namespace of the elements in the document and add prefixes to the XPath. The update statement in this case would be:

UPDATE Store SET Demographics.modify('

declare namespace x="http://testschemas/StoreSurvey";

insert <x:newnode>Some value</x:newnode> as first into /x:StoreSurvey[1]

WHERE CustomerID = @custid

However, this will only work if the schema for the column allows this node to be part of a valid document. In the StoreTyped table, the schema does not include this element, so the update would fail with a validation error.

The Query that Deletes an Existing Element

The second statement removes all nodes named newnode from the XML document in the Demographics column. It executes the XML-DML delete statement within a call to the modify method of the Demographics column, specifying the XPath "//newnode". Alternatively, we could have specified the full XPath "/StoreSurvey/newnode ":

UPDATE Store SET Demographics.modify('delete //newnode') WHERE CustomerID = @custid

The result, when the same customer row is selected in the GridView, shows that the newnode element has been removed from the document:

Figure 3 - Deleting an existing element from an XML document in SQL Server 2005 using XQuery

The Query that Updates an Existing Element

The previous two statements operate on an un-typed xml column. However, the XML-DML replace statement used in this XQuery statement can only be used on a typed column. This means that we have to declare the namespace as referenced in the schema for this column, and add the corresponding prefixes to the query. This is the third update statement:

UPDATE StoreTyped SET Demographics.modify('

declare namespace x="http://testschemas/StoreSurvey";

replace value of (//x:BankName)[1] with xs:string(sql:variable("@newname"))

WHERE CustomerID = @custid

Notice how this takes an extra parameter named @newname, which contains the new value for the BankName element, and we access this in the XQuery using the sql:variable function. To ensure that the value is of a type compatible with the schema for the column, we cast it to a string type. Figure 4 shows the results of executing this query, and you can see the updated value for the bank name in the XML document that is extracted from the Demographics column after the query has been executed.

Figure 4 - Updating the contents of an element in an XML document in SQL Server 2005 using XQuery

Comparing XQuery Update Performance with the Traditional Approach

In the previous article, we showed an example that compares the performance of XQuery with the traditional approach to extracting values from XML documents stored in a database. This next example, "Comparing Traditional and XQuery Updates", does the same kind of thing, but compares performance when updating values in the XML documents stored an xml column.

The aim is to update the value of one element within the XML document in the Demographics column for all 700+ rows in the StoreTyped table. The example page offers three options:

Update the value of the Internet element using the traditional DataSet approach
Update the value of the Internet element using an individual XQuery on each row
Update the value of the Internet element using an XQuery that process all rows in one go

The queries we use for each of these options, and the code to execute them, are discussed next. To save space and make the relevant parts easier to see, we've omitted some code that opens the database connection, records the time taken for the process, displays the results, and handles any errors. You can see all the code in the samples available for download from http://www.daveandal.net/articles/sql2005-xquery/.

Updating the Document Using the Traditional Approach

The first option in the example page uses the two SQL statements, shown in the next listing. A normal SELECT statement fetches a rowset containing the customer ID and the contents of the Demographics column from every row in the table. An UPDATE statement is used to replace the content of the Demographics column with a new XML document for the row specified by the @custid parameter. Notice how we convert the string value that will be provided by the @newxml parameter into an xml type before applying it to the row:

SELECT CustomerID, Demographics FROM StoreTyped

UPDATE StoreTyped SET Demographics = CONVERT(xml, @newxml) WHERE CustomerID = @custid

The code in the page goes through three distinct stages when you execute this query. First it uses the SELECT statement to fetch the rowset containing all the rows from the StoreTyped table into a DataTable:

' fill a DataTable with the rows from the StoreTyped table

da = New SqlDataAdapter(sql_trad_select, con)

da.Fill(dt)

...

Then it iterates through the DataTable, updating the value of the node named Internet in the XML document stored in each row by loading it into an instance of the XmlDocument class. Because this is a typed column, and the XML document contains a namespace declaration, the code has to generate an XmlNamespaceManager instance for this document and add the namespace to it. This allows the SelectSingleNode method to locate the required element, and the value can be updated. Finally, the updated XML document is extracted from the XmlDocument instance as a String using the OuterXml property, and stored back in the DataTable row:

...

' iterate through the rows in the DataTable

Dim xd As New XmlDocument()

Dim xn As XmlNode

Dim xmlns As XmlNamespaceManager

Dim nav As XPathNavigator

For Each dr As DataRow In dt.Rows

' update the XML document in the Demographics column

xd.LoadXml(dr("Demographics").ToString())

xmlns = New XmlNamespaceManager(xd.NameTable)

xmlns.AddNamespace("x", "http://testschemas/StoreSurvey")

xn = xd.SelectSingleNode("//x:Internet", xmlns)

nav = xn.CreateNavigator()

nav.SetValue("DSL")

dr("Demographics") = xd.OuterXml

...

Now the code can generate the UpdateCommand required by the DataAdapter, and add the two parameters required to it. These parameters reference the two columns in the DataTable where the values will come from for each row as the update takes place. Finally, a call to the Update method of the DataAdapter pushes the changes back into the database table:

...

' push the changes back into the database

Dim cmd As New SqlCommand(sql_trad_update, con)

cmd.Parameters.Add(New SqlParameter("@newxml", SqlDbType.VarChar, 1000, "Demographics"))

cmd.Parameters.Add(New SqlParameter("@custid", SqlDbType.Int, 4, "CustomerID"))

da.UpdateCommand = cmd

iRows = da.Update(dt)

Figure 5 shows the result. You can see at the bottom of the page that the code records the time taken for the complete process (extracting and updating the rows), and displays this along with a count of the number of rows that were updated. An average time for the 700+ rows using our hardware setup was around 650 milliseconds.

Figure 5 - Updating the XML documents in the StoreTyped table using the traditional approach

Updating the Document Using an XQuery for Each Row

The second option in this example uses an XQuery to perform the updates on each row in the StoreTyped table. The two queries it uses are shown in the next listing. The first simply returns the CustomerID from every row in the table. There is no need to extract the contents of the Demographics column this time because we don't need to update it on the client. Instead the update will be carried out by the second SQL statement, which contains an XQuery that uses an XML-DML statement within the modify method of the Demographics column:

SELECT CustomerID FROM StoreTyped

UPDATE StoreTyped SET Demographics.modify('

declare namespace x="http://testschemas/StoreSurvey";

replace value of (//x:Internet)[1] with x:InternetType("T1")

WHERE CustomerID = @custid

To perform the updates, the code first fills a DataTable with the rowset containing the Customer ID values:

' fill a DataTable with the rows from the StoreTyped table

da = New SqlDataAdapter(sql_xquery_select, con)

da.Fill(dt)

...

Then it iterates through the DataTable executing a command that applies the UPDATE statement to each row in turn. Notice that the code opens the connection before iterating the rows, and then closes it after all the updates are complete, to minimize processing time:

...

' iterate through the rows in the DataTable

Dim cmd As New SqlCommand(sql_xquery_update, con)

cmd.Parameters.Add(New SqlParameter("@custid", SqlDbType.Int))

con.Open()

For Each dr As DataRow In dt.Rows

' iterate through the rows updating the 'xml' column in the

' database with an XQuery modify statement

cmd.Parameters("@custid").Value = dr("CustomerID")

iRows += cmd.ExecuteNonQuery()

con.Close()

The result of selecting this option is shown in Figure 6. The average time taken with out hardware setup was around 175 milliseconds, giving a saving of over 70%. This is due both to the reduction in client-side processing that is required when using an XQuery, as well as the saving in processing and bandwidth required to transport the XML documents from the database to the client for updating, and then back to the database. The setup that produced these results had the Web server and SQL Server 2005 running on the same machine. When accessing a remote SQL Server, you should see even greater improvements.

Figure 6 - Updating the XML documents in the StoreTyped table using an XQuery on each row

Updating the Document Using a Single XQuery for All the Rows

Both of the previous options have performed the updates on one row at a time. In the case of the traditional approach, this is the only way it can be done because the code replaces the complete XML document in the Demographics column with an updated one. So the original document must be extracted first, updated, and then pushed back into the table. In the second option, there was no need to extract the XML document from the database because the XQuery only updates one value in the document and so the rest of the document is unchanged.

However, if you are applying the same update to every row (as in this example), you might be able to create an XQuery that can be executed against all the rows, automatically calculating the new value for the element or attribute you want to update. In our simple example, we update the Internet element to the same value in every row. Depending on what update you what to carry out, you may be able to use the built-in functions of XPath, or an XQuery that contains a custom function, to calculate the new value.

The example shown here uses the following SQL statement to update all the rows in the StoreTyped table in one operation. Basically, it is the same as the previous option, but does not contain a WHERE clause and so the operation will be applied to all the rows in the table:

UPDATE StoreTyped SET Demographics.modify('

declare namespace x="http://testschemas/StoreSurvey";

replace value of (//x:Internet)[1] with x:InternetType("56kb")

This means that it can be executed using the following simple code:

' execute the single XQuery statement that updates all the rows

Dim cmd As New SqlCommand(sql_xquery_update_all, con)

con.Open()

iRows += cmd.ExecuteNonQuery()

con.Close()

However, the result (shown in Figure 7) doesn’t appear to offer much advantage over the previous option where we updated each row in turn. This is because, in effect, the process that SQL Server 2005 must carry out is the same - except that it processes all the rows in response to one command, rather than having to update each row for separate individual commands. Of course, the results here are slewed by the fact that the database and client (the Web server) are on the same machine. When a remote database is used, network bandwidth will come into play and - depending on the loading of the database - you should see a more significant improvement.

Figure 7 - Updating the XML documents in the StoreTyped table using one XQuery on all the rows

Using XQuery in a Managed Code Stored Procedure

The final topic in this series of articles is to investigate how we can use the new feature of SQL Server 2005 that allows stored procedures to be written in managed code, when we are working with XML data and XQuery. Included in the samples you can download from this article is a Visual Studio 2005 project that implements a managed code stored procedure named UpdateContactDetails.

Figure 8 shows this stored procedure as installed in SQL Server 2005 - the lock on the icon indicates that it is a managed code procedure implemented as an assembly (a DLL) stored within the database. And, as you can see from Figure 8, the stored procedure takes a single parameter that is a customer ID for one of the rows in the StoreTyped table.

Figure 8 - The UpdateContactDetails managed code stored procedure in SQL Server 2005

You can open the sample project named ManagedCodeProc in Visual Studio 2005 and change the connection to point to your own SQL Server 2005 database using the Database page of the Properties window (see Figure 9). Then compile and deploy the stored procedure to the database using the commands on the Build menu in Visual Studio.

Figure 9 - Changing the connection to point to your own SQL Server 2005 database in Visual Studio 2005

After deploying the stored procedure, you must give the account(s) that will access it EXECUTE permission - just as you have to with any stored procedure. The project we provide in the samples includes a Test.sql script that executes the following T-SQL statement. You can change the account name as required:

GRANT EXECUTE ON UpdateContactDetails TO [public]

It's actually quite hard to think of many "real world" examples where a managed code procedure is necessary. SQL Server's set-based processing efficiency, combined with the wide range of functions available in Transact-SQL, mean that almost any complex task can be accomplished in a standard database stored procedure. As you saw in the previous article, in the example "Using Parameters with an XQuery", you can execute a SQL statement that uses XQuery and XML-DML methods within a normal stored procedure.

The UpdateContactDetails Stored Procedure

The code to implement the UpdateContactDetails stored procedure is a normal .NET Class file. It starts by importing the namespaces that are required to access the database, and the namespace that contains the SqlProcedure() attribute (Microsoft.SqlServer.Server). This is followed by the routine that actually implements the stored procedure, decorated with the SqlProcedure() attribute. You can see from the code that the parameter visible in the SQL Server Management Studio window (Figure 8) is declared as a parameter to the main routine:

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

<SqlProcedure()> _

Public Shared Sub UpdateContactDetails(ByVal CustomerID As Int32)

...

As the name suggests, this stored procedure is designed to update a row in the Demographics column of the StoreTyped table. The schema for the Demographics column allows the XML documents stored in this column to include a ContactDetails element, however none of the rows contain this element by default. This means that the code must discover if the XML document already contains this element, and update it with the new value, or insert the complete element and value if it is not already present. So the procedure declares three SQL statements.

The XQuery Statements to Update the XML Documents

The first of the SQL statements for this example uses the query method of the Demographics column to extract the values of existing elements, which we'll use to decide what the new contact name will be:

SELECT CustomerID,

ContactName = Demographics.query('

declare namespace x="http://testschemas/StoreSurvey"; data(//x:ContactName)

'),

BusinessType = Demographics.query('

declare namespace x="http://testschemas/StoreSurvey"; data(//x:BusinessType)

'),

AnnualSales = Demographics.query('

declare namespace x="http://testschemas/StoreSurvey"; data(//x:AnnualSales)

FROM StoreTyped WHERE CustomerID = @custid

The second is an UPDATE statement that will replace the value of an existing ContactName element with the new value, passed to it via the parameter named @newname. As in previous examples, we have to specify a single node for the target in the replace statement, and we cast the value of the parameter to a string type:

UPDATE StoreTyped SET Demographics.modify('

declare namespace x="http://testschemas/StoreSurvey";

replace value of (//x:ContactName)[1] with xs:string(sql:variable("@newname"))

WHERE CustomerID = @custid

The third statement will be executed where the XML document in the Demographics column does not currently contain a ContactName element. It uses an XML-DML insert statement to insert the new element, taking the value from the parameter named @newname - again casting it to a string type first. To comply with the schema for this column, the ContactName element must be the first child element in the document and so we specify as first into and provide the root element as the target:

UPDATE StoreTyped SET Demographics.modify('

declare namespace x="http://testschemas/StoreSurvey";

insert <x:ContactName>{ xs:string(sql:variable("@newname")) }</x:ContactName>

as first into (//x:StoreSurvey)[1]

WHERE CustomerID = @custid

Executing the XQuery Statements within the Stored Procedure

The main body of the stored procedure code uses these three statements to update the ContactName element in the row specified by the CustomerID value that is passed into the stored procedure when it is executed. The first step is to create a connection to the database, and for this the special value "context connection=true" is used for the connection string with a standard SqlConnection class instance. Then the code creates a command from this connection, and adds the @custid parameter to it.

Next, as with ordinary client-side code, it opens the connection, executes the query to get a SqlDataReader instance over the results, and calls the Read method to access the first (and only) row. The existing values from the XML document, returned by the first of the SQL statements we looked at earlier, are stored in local variables and the SqlDataReader can be closed:

' create inproc connection using SqlClient SqlConnection

Using con As New SqlConnection("context connection=true")

' create a Command and get a DataReader for the row

Dim cmd As SqlCommand = con.CreateCommand()

cmd.CommandText = sql_select

cmd.Parameters.Add(New SqlParameter("@custid", CustomerID))

con.Open()

Dim dr As SqlDataReader = cmd.ExecuteReader()

If dr.HasRows Then

dr.Read()

' get values from reader

Dim contactname As String = dr.GetString(1)

Dim businesstype As String = dr.GetString(2)

Dim annualsales As Int32 = Int32.Parse(dr.GetString(3))

dr.Close()

...

Now we decide what the new contact name will be. This obviously depends on the business logic you need to implement, and here we're taking some fairly arbitrary decisions based on the values in the elements we extracted from the XML document. Notice that we also set a flag to indicate if the row currently contains a ContactName value. We'll assume for the purposes of this example that the absence of a value means that there is no ContactName element, though to be sure you would probably need to execute an exist method call to check that an empty element is not present in the document:

...

' decide what new contact name will be

Dim bExists As Boolean = (contactname <> String.Empty)

Select Case businesstype

Case "BM"

If annualsales > 150000 Then

contactname = "Mike Sharp"

Else

contactname = "Maria McCauly"

End If

Case "OS"

If annualsales > 10000 Then

contactname = "Arthur Atkinson"

Else

contactname = "Christina Wells"

End If

Case "SGS"

If annualsales < 150000 Then

contactname = "Mike Sharp"

Else

contactname = "Kim Wong"

End If

Case Else

contactname = "Jonathan Kosygin"

End Select

...

The final section of code updates the XML document in the Demographics column by executing one of the other two SQL statements we declared earlier. If the ContactName element already exists, it executes the query that uses an XML-DML replace statement, while the query that uses an XML-DML insert statement is executed if the ContactName element does not already exist:

...

' now ready to update the database table row

' set Command to update SQL statement and add parameter

' containing new value to pass to the XQuery

If bExists Then

cmd.CommandText = sql_update

Else

cmd.CommandText = sql_insert

End If

cmd.Parameters.Add(New SqlParameter("@newname", contactname))

cmd.ExecuteNonQuery()

End If

con.Close()

End Using

End Sub

End Class

Executing the UpdateContactDetails Stored Procedure

The example page that uses the UpdateContactDetails stored procedure first declares two queries. These consist of the name of new stored procedure, and a SQL statement that is used simply to extract the XML document from the Demographics column so that you can see the results:

Dim sql_execute As String = "UpdateContactDetails"

Dim sql_select As String = "SELECT Demographics FROM StoreTyped WHERE CustomerID = @CustomerID"

The page also contains an ASP.NET GridView and SqlDataSource, which together retrieve and display a list of the customers in the StoreTyped table. Clicking on the Select button in one of the rows in the GridView executes the routine named SelectCustomer in the page. This routine starts by creating a connection and command, specifying the new managed code stored procedure, and adds a parameter to the command that will pass the selected customer ID value into the stored procedure. Then it opens the connection to the database, and executes the stored procedure:

Sub SelectCustomer(ByVal sender As Object, ByVal e As EventArgs)

Using con As New SqlConnection(ConfigurationManager.ConnectionStrings _

("XMLTestConnectionString").ConnectionString)

Try

' create command and execute the stored procedure

Dim cmd As New SqlCommand(sql_execute, con)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@CustomerID", Int32.Parse(grid1.SelectedDataKey.Value)))

con.Open()

cmd.ExecuteNonQuery()

...

Once the stored procedure completes, the code continues by using the SELECT statement declared earlier to retrieve the XML document from the Demographics column in this row, and displays it in the page. An HTML line break is inserted between each element to make the results easier to see:

...

' now extract the XML document from the Demographics

' column in the same row and display it in the page

cmd.CommandText = sql_select

cmd.CommandType = CommandType.Text

Dim sResult As String = Server.HtmlEncode(cmd.ExecuteScalar())

con.Close()

lblMessage.Text &= sResult.Replace("><", "><br /><")

Catch ex As Exception

Try

con.Close()

Catch

End Try

lblMessage.Text = ex.Message

End Try

End Using

End Sub

Figure 10 shows the results. You can see that the stored procedure inserted the ContactName element into the XML document, with (in this case) the value "Mike Sharp". If you select other rows, you'll see that different names are inserted into the new ContactName element.

Figure 10 - Executing the UpdateContactDetails managed code stored procedure

Summary

In the previous two articles, we discussed the great new features in SQL Server 2005 that make it easier to work with XML documents, and also allows you to write more efficient data access code when you need to persist, access and update XML. SQL Server 2005 is not the first database to provide these features, but the combination of these new features and integration with ADO.NET make it easy to take advantage of them, while vastly reducing the amount of code you have to write. We also explored how XQuery provides great performance and simplifies data access code requirements when extracting data from XML documents stored in SQL Server 2005, and using it in a variety of ways.

In this article, we delved deeper into XQuery and SQL Server 2005 XML features to see how you can improve performance when updating XML documents stored in the database, and how you can use XQuery within managed code stored procedures. You saw how you can execute XQuery statements that update values in an XML document stored in both a typed and an un-typed xml column using the XML-DML insert, delete and replace statements. You also saw the dramatic improvement in performance that results from using XQuery when compared to traditional methods that extract the complete XML document, update it, and then push it back into the database.

Finally, we looked at how you can create, compile and deploy managed code stored procedures that use XQuery in a SQL Server 2005 database; and how you can then execute these procedures. It is possible to write and compile a managed code stored procedure without using Visual Studio 2005 (by using one of the command-line .NET compilers), and deploy it using the T-SQL statements that install an assembly in SQL Server 2005. However, Visual Studio makes the whole process much easier, and allows you to set break-points and debug the code while it is executing if required.