SQL Server with Mr. Denny:

T/SQL


December 15, 2008  9:00 AM

Back To Basics: Getting Data from an XML Document



Posted by: Denny Cherry
Back To Basics, T/SQL, XML

One of the most popular ways to get data multiple pieces of data in a single parameter from one stored procedure to another, or from a client application to the database is to use XML. This can be done in SQL Server 2000 by using the NTEXT (or TEXT) datatype, and in SQL 2005 using the XML...

December 8, 2008  1:06 PM

Just how awesome are table parameters in SQL Server 2008?



Posted by: Denny Cherry
CREATE PROCEDURE, SQL Server 2008, T/SQL, Table Variables

I would have to say, that one of the coolest new features of SQL Server 2008 is the ability to pass a table as a single parameter to a stored procedure. While we have been able to do this in the past, by using XML to pass more than one value in, then break it apart. But this is just such a...


November 27, 2008  12:15 PM

Back To Basics: Creating Indexes



Posted by: Denny Cherry
Back To Basics, CREATE INDEX, SELECT statement

After you have created your tables, and stored procedures you will have a basic idea of what queries are going to be running against your database.  If you haven't already done so, its now time to start adding indexes to your database.  The catch with index is that there is such a thing as to...


November 3, 2008  11:00 AM

Improving SQL Service Broker Performance



Posted by: Denny Cherry
END CONVERSATION, RECEIVE, Remus Rusanu, SEND, Service Broker, SQL Server 2005, SQL Server 2008, T/SQL

A while back I was talking to a Microsoft Support Engineer and he had mentioned that in a high load Service Broker environment such as ours there can be some impressive performance improvement can be achieved by reusing the service broker sessions. The cost of creating and closing a new...


October 1, 2008  11:00 AM

T/SQL Code to remove SQL Injection Values from your tables



Posted by: Denny Cherry
SQL Injection, T/SQL

With SQL Injection Attacks being all the rage these days, I've been asked a couple of times for T/SQL code to clean up the database. So I threw this code together to clean up the data. This code will clean all the character and uni-code columns in all the user defined tables in the system. ...


July 21, 2008  11:00 AM

A better way to index text data



Posted by: Denny Cherry
Index Performance, Query tuning, SELECT statement, T/SQL, Tables, Uni-code

Indexing text data (varchar, nvarchar, char, etc) is a good way to make it faster to find the data you are looking for.  However these indexes can end up being very hard on the disks behind the index, as well as the memory of the server.  This is because of the large amount of data being put in...


July 14, 2008  11:00 AM

Using batching to do large operations without filling the transaction log



Posted by: Denny Cherry
DELETE statement, Query tuning, SQL, Tables

Deleting large amounts of data from a table is usually an important task, but if you don't have a maintenance window to work within then you can end up causing locking and blocking against the other processes which are accessing the database.  Not to mention you will cause a very large amount of...


July 10, 2008  11:00 AM

My Experience with putting a CRL Procedure into SQL



Posted by: Denny Cherry
C#, CREATE ASSEMBLY, CREATE PROCEDURE, Encryption, Microsoft.SqlServer.Server, SQL CLR Procedure, SQL Server 2005, SQL Server stored procedures, T/SQL

A little while ago I came upon a situation where I needed to put a CRL procedure into SQL Server 2005.  Now I've been using SQL Server 2005 since the day it went RTM, and this is the first occasion that I've needed to put a CLR procedure into SQL.  Could I have done what I needed to without a CRL...


July 7, 2008  11:00 AM

Back To Basics: The RECEIVE command (and END CONVERSATION)



Posted by: Denny Cherry
Back To Basics, RECEIVE, Service Broker, SQL, T/SQL

The RECEIVE command is the last of the Service Broker items to cover in the Back To Basics series.  The RECEIVE command is what is used to actually get the message out of the queue so that you can process the data within the message_body column.  The RECEIVE command works just like the


June 26, 2008  11:00 AM

Back To Basics: The SEND command (and the BEGIN DIALOG command)



Posted by: Denny Cherry
Back To Basics, BEGIN DIALOG, SEND, Service Broker, SQL, T/SQL

The SEND command was introduced to SQL Server when SQL Server 2005 was released.  It's kind of like the INSERT command except that it is used only with the SQL Server Service Broker.  With the service broker you put data into a queue instead of a table.  Insert of inserting a command into the...


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: