Data Types

XML in the database. What’s the big deal any way?

So, we’ve had the ability to use XML within the SQL Server databases since SQL 2000 came out.  What’s the big deal you ask?  How can I use it to make my life easier you also ask?

When I first started looking into this whole XML thing I was thinking the same thing.  How is this blob of data going to make my life easier?  Well when used correctly it most certenly can.  In SQL Server (up through SQL 2005 at least) we don’t have a way to pass more than one record into a stored procedure.  If you have an order entry system and the custom wants to order 3 items you have to run three commands on the SQL Server, doing three inserts one at a time.  If you instead passed those three items from the UI to the database as an XML document you could then process all three items in a single command thereby improving performance.

declare @XML XMLSET @XML = ‘<ROOT><Customer ID=”12″>
<Order ID=”7498″>
<Item ID=”415″ Qty=”1″ />
<Item ID=”87547″ Qty=”2″ />
<Item ID=”4125″ Qty=”1″ />
</Order>
</Customer>
</ROOT>’
DECLARE @hDoc INT
exec sp_xml_preparedocument @hDoc OUTPUT, @XML
SELECT *
FROM OPENXML (@hDoc, ‘//Customer/Order/Item’)
WITH (CustomerId INT ‘../../@ID’,
OrderId INT ‘../@ID’,
ItemId INT ‘@ID’,
Qty INT ‘@Qty’)
exec sp_xml_removedocument @hDoc
As you can see by the 0utput we have a table which we can process in bulk.  Instead of running a sample piece of code if we were to build this into a stored procedure we could do something like this.

Here is our LineItems table.

CREATE TABLE LineItems
(OrderId INT,
ItemId INT,
Qty INT,
ItemPrice NUMERIC(6,2))
And here is our procedure which adds the XML data to the LineItems table.  In a production system business logic would need to be added to ensure stock on hand, and to return estimated ship dates to the client.
CREATE PROCEDURE usp_AddLineItems
@Items XML
AS
DECLARE @hDoc INT


exec sp_xml_preparedocument @hDoc OUTPUT, @Items


INSERT INTO LineItems
SELECT OrderId, ItemId, Qty, ItemPrice
FROM OPENXML (@hDoc, ‘//Customer/Order/Item’)
WITH (CustomerId INT ‘../../@ID’,
OrderId INT ‘../@ID’,
ItemId INT ‘@ID’,
Qty INT ‘@Qty’,
ItemPrice NUMERIC(6,2)
‘@ItemPrice’) exec sp_xml_removedocument @hDocGO

Since we don’t want to create a web based order system for this little demo here we can run the procedure and verify the output.

declare @XML XMLSET @XML = ‘<ROOT><Customer ID=”12″>
<Order ID=”7498″>
<Item ID=”415″ Qty=”1″ ItemPrice=”12.95″/>
<Item ID=”87547″ Qty=”2″ ItemPrice=”16.25″/>
<Item ID=”4125″ Qty=”1″ ItemPrice=”8.25″/>
</Order>
</Customer>
</ROOT>’
EXEC usp_AddLineItems @XMLGO
SELECT *
FROM LineItemsGO

As we can see this is much more efficient than having to run three insert statements as they would require at least three rights to the disk, plus how ever many reads are needed to do the business logic. By using the XML we can do it all with a single write to the disk, and a single set of reads for the business logic. In this example we have reduced our disk activity by 33%. If we were processing 10 line items we would have reduced our disk activity 90%. And since disk performance is usually the bottleneck of any database reducing the disk IO any way we can is a good thing.

Denny

New Article: SQL Server encryption vs. hashing for data security

I’ve published a new tip over on SearchSQLServer.com entitled “SQL Server encryption vs. hashing for data security“.

(It was published a couple of days ago, I just didn’t get notice until today.)

Denny

So what’s the difference between null and a blank field

There is always a discussion going on somewhere over having NULL values in a database.  Some people (such as myself) are fine with NULL values.  Some people don’t like having NULL values in there database for some reason.  I’ve heard all sorts of reasons; “The screw up performance”, “They are hard to deal with”, “I like using a default value better”, “Default values are easier to work with”.

The performance one is just bunk.  NULL values are indexed just like any other value.

The rest of the reasons to not use NULL values ignore the fact that a NULL value is a perfectly legit value for some fields.  Not all fields should allow a NULL value to be placed in them.  If you are building a shopping cart and have a line items table, the Quantity and Price fields probably shouldn’t allow nulls as you should know the price and quantity when creating the invoice.  However in the same shopping card application the users phone number may need to allow NULL values.

Something which is key to remember about NULL values is that NULL doesn’t mean nothing.  It means unknown.  There may be a value, there may not, we simply do not know what that value is.  Putting in some default value will then put a value on the field when there shouldn’t be.  A great example which I read recently involved a hypothetical database at a blood bank.  A person comes in needing blood.  They are entered into the system but the recipients blood type isn’t known.  If you put in a default value of a blank value when you go to query the supply table, if there are pints of blood which have not been typed they will also have a blank value (as that is your standard default value).  You’ll get a match when you run the query and possibly be giving the recipient blood which is of the wrong type.  Now if you were using NULL values in the database the query would return no matching values as NULL <> NULL (under the default settings of Microsoft SQL Server).  Granted we should be able to trust that the person give the recipient the blood would check the typing, but would you rather trust a person or the computer.  People are overworked and usually underpaid, and nurses and doctors make simple mistakes just like the rest of us.  Personally I’d rather know that the system wouldn’t find a match rather than have to worry that I’d be getting the wrong type of blood.

If you really want to display that default value when the data is returned that’s what the ISNULL system function is for.  This way you don’t have to write long case statements around each field which allows NULL.

NULL values have there place, just like data does.  NULL values don’t mean that we don’t care about the data, it means that we simply don’t know the value of the data as of yet.  It shows that we care about the system and the people using it.  How annoying is it to look at a form which is full of default values and having to read through all of them instead of simply looking at the ones which are blank.

What’s up with the N in front of string values?

If you have looked at pretty much any Microsoft provided T/SQL code you have probably seen an N in front of a string value when setting a variable much like this.

SELECT *
FROM sys.databases
WHERE name = N‘master’

What this N does is tell the SQL Server that the data which is being passed in is uni-code and not character data.  When using only the Latin character set this is not really needed.  However if using characters which are not part of the basic Latin character set then the N is needed so that SQL knows that the data being given it is uni-code data.

Denny

Should I be using uni-code data types?

The path that Microsoft is going says yes.  They are moving all there samples and defaults to using uni-code.  In reality uni-code may not be needed in your environment.  If you are storing only Latin characters (the US alphabet characters) then you probably don’t need to store uni-code characters.  However if you are planning on moving your operations to support customers who do not speak English then you will want to setup your database and application to use the uni-code data types (nvarchar, nchar, ntext).

It doesn’t take much extra to use uni-code data types, however do keep in mind that it does require more disk space to use uni-code data types.  For each character saved two bytes of disk space are used instead of one byte.  While this may not seam like much space if you will have a large table with a large ntext field this can use a lot of space quickly.

Denny