Featured Article(s)
Installing and Configuring Reporting Services (Part 3 of 3)
In this session, readers will learn the basics of installing Microsoft SQL Server Reporting Services. This session will cover common installation situations and will discuss some of the more common pitfalls that surround the installation. This session will also cover the basics of Reporting Services configuration options, including basic database configuration and security implications.
Matching Your Data Types
SQL Server query optimization best practices tells us that we get better performance when we do not modify the contents of a column while using it in a join or where clause. The reason this is true is that when SQL Server has to perform a conversion of the data in a column before doing a comparison, it is unable to use an index.
You see this problem a lot when using DATETIME and SMALLDATETIME data types because these data types always contain both a Date and Time value. If you specify only Date, the Time value is set to Midnight. If you only specify the Time, the Date defaults to the earliest data possible for each data type. With SQL Server 2008 you have new data types that store only DATE or TIME, but many systems were developed prior to these new data types were .
I have seen many queries that wish to return all the data for a specific day (mm/dd/yyyy) stored in a DATETIME datatype. Here are a couple examples of a where clause:
WHERE DATEPART(dd, DateColumn) = DATEPART(dd, @Date)
AND DATEPART(mm, DateColumn) = DATEPART(mm, @Date)
AND DATEPART(yy, DateColumn) = DATEPART(yy, @Date)
or
WHERE CONVERT(VARCHAR(10), DateColumn, 101) = CONVERT(VARCHAR(10), @Date, 101)
Both queries are accurate and return the desired results. But performance is reduced because it cannot use an index on DateColumn because of all the date conversion that has to occur prior to the comparison.
Similar problems occur when data type conversion has to be completed prior to comparison. Given the string "SSWUG ROCKS " it is stored differently for different character types.
| Data Type | String Representation |
CHAR |
"SSWUG ROCKS " |
VARCHAR |
"SSWUG ROCKS" |
NCHAR |
"S S W U G R O C K S " |
NVARCHAR |
"S S W U G R O C K S " |
In order to perform comparisons of this string everything would have to be converted to the same data type. Since the VAR versions truncate trailing spaces, if your data is stored as NVARCHAR or VARCHAR all comparisons will have to be converted to the same in order to match.
One client I worked with years ago had a problem directly relating to this issue. They imported data from many different data sources. In order to import data, they would create a temporary table for staging import data, and then execute SQL merge queries. The import phase was lightning fast, but the merge was beyond slow. It just couldn’t keep up with the load.
The cause was the import tables utilized default character data types in SQL Server, ie. NVARCHAR, but the permanent tables used VARCHAR. When performing a merge, the matching columns for every row in one table or the other had to be converted to a common data type, in this case VARCHAR, before the merge criteria could be performed.
The key is to store your data in a form that doesn’t require conversion for comparisons.
Email In Your Database – Response
A while back I wrote about putting Email in your SQL Server database. The key point is that the primary purpose of a database server is to server data. If you are using resources to manage Email then it is not managing data. However, sometimes the need for Email notification is not know until data is modified. Typically this is done in a trigger, so you want the request to send Email to be fast. Roger sends a response with one of the new features of SQL Server 2005 that allow SQL Server to handle this problem in a more streamlined fashion.
Roger Writes:
Another alternative would be to use DBMail that has been in SQL Server for the last six years or so. It works by putting mail messages in a Service Broker queue and then having an external process read the queue and send the messages over SMTP.
If you wish to weigh in on the discussion, send your comments to btaylor@sswug.org. Feel free to send comment for other issues or questions you may have as well.
Cheers,
Ben