Did you know that you can define an XML schema for every XML column or variable that you want? Defining XML schemas is not only good practice, because it validates the data as soon as you store it in the column or variable, but it also improves XML query performance.
Top 10 T-SQL worst practices
As part of an improvement process (any process, not just database development), you can start by asking yourself “How could I make this process as bad as possible”. In fact, when you think of how to royally screw something up, all you have to do is flip the answer around, and there you have a good starting point for improving your process, work, whatever.
Here are a few T-SQL “worst practices” to get you started on improving your database design and development practices. I’ve personally seen all of these in the field, and I think you might recognize some of them, too.
Parsing XML data
You’ll be hard-pressed not to stumble over XML data in your daily work. Good thing then, SQL Server contains built-in XML parsing logic – there’s a native xml datatype, built-in XPath support and all the tools you need to store and transform data from or to XML data. However, this is an extensive topic, so this article will cover the basics of parsing XML data into a recordset, just to wet your apetite.
Turning semicolon delimited data into a table
Here’s a handy conversion tool that I built for myself in order to convert semi-colon delimited data (very common in files, etc) into a table. It comes in the form of a scalar function, that converts a varchar(max) to an XML value.
Creating XML data from a recordset
SQL Server has a wide range of XML features natively built into the platform, and this includes methods to create XML from tabular data, working with XML data and, of course, turning XML data back into a recordset. This primer will go through some common methods of creating XML from a regular SELECT query.
A short introduction to application roles
Application roles provide a practical way to assign application-specific permissions in your database and to make sure that your applications always use a defined login. Not to be confused with actual roles, application roles are more like users in the database.
Collations and case sensitive wildcards
The other day, I ran across the following issue: I was looking to split a value using upper case and lower case characters. Here’s an example query:
SELECT PATINDEX('%[a-z][A-Z]%',
'testValue' COLLATE Finnish_Swedish_CS_AS) AS offset
I expected this query to return the value 4, because at that offset in the string, there is a lower case character (t) followed directly by an upper case character (V). However, in practice, this query returns the value 1.
Using Service Broker to send messages
Until now, I’ve never really looked any closer at Service Broker and message queues in SQL Server, but it turns out it’s a really useful feature if you need asynchronous processing or any other kind of queued messaging logic. Messages can be sent within a database, a server, or even between servers.
Date formats and conversions
Have you noticed how date conversions can seem a bit arbitrary at times? How a string value is translated to a date depends on a number of factors, including how you perform the conversion and what language settings you have set for your connection. But there are ways to limit your conversion headaches.
A URL encoding function in T-SQL
A while ago, I needed to create a URL encoding function in T-SQL. URL encoding is what happens when you translate special characters (basically anything that isn’t an alphanumerical) so they’ll fit in a URL.