Implicit Data Conversion: The “Hidden” Bottleneck

Have you ever written a query that just seems to take forever? You have looked it over time and time again, and it seems to be perfect! It’s straightforward, has an appropriate index, and still takes 5 – 10 seconds to come back.

Well, there can be a number of causes. Today, we are going to discuss “Implicit Conversion.” I ran into this again today helping a colleague out. I have seen this literally bring a system to its knees by causing deadlocks during high load. In short, we can blame this for the fall of the Roman Empire.

Consider the following stored procedure:

Create Procedure WidgetInfoGet @WidgetID VarChar(20)
As
Select WidgetID, Descr, CurrentPrice, Qty
From WidgetMaster
Where WidgetID = @WidgetID

That looks innocent enough, right? However, a statement like this has the potential to make demon and children cry if that table has several hundred thousand rows in it.

Here’s the deal. Have you ever seen a system where in some tables an ID is stored as a Character string, but in others it’s stored as an Integer? Sure, we all have. Developers and even DBAs get confused and write a procedure with the wrong data type all the time in that scenario. Say for example that our table actually has WidgetID definted as an Int, but the developer was confused and wrote the procedure as if the WidgetID column was a VarChar(20).

Now, every time SQL Server has to look for a Widget, it has to convert @WidgetID from a VarChar to an Int. This is an implicit conversion of data type. Internally, SQL Server uses a convert function to do this. When this happens, SQL Server cannot use an index effectively because any time it has to hand something to a function, it cannot be certain of the result of the function. Therefore, it has to convert the value for each and every row. This results in SQL Server scanning the entire table looking for the value. This takes time and, under default locking modes, places a share lock on the entire table preventing other processes from updating records while the scan is taking place.

The fix? Simple! Strangle the person who started mixing data types in the system. Once they are dead, alter your procedure to convert the value first:

Create Procedure WidgetInfoGet @WidgetID VarChar(20)
As
Declare @intWidgetID Int
Set @intWidgetID = Convert (Int, @WidgetID)

Select WidgetID, Descr, CurrentPrice, Qty
From WidgetMaster
Where WidgetID = @intWidgetID

…and you will now retire a millionaire because you single-handedly rescued the world.

Building a Weekend Table - SQL Dates and Times Series

Hello and welcome (back) to the SQL Coach blog!

Tonight I am going to show you how to build a table populated with the dates of all weekend days within a certain date range.

In this project we will be covering:

• The SmallDateTime SQL Server data type
• How to create a Variable Temporary Table in SQL Server
• How to loop through a date range in SQL Server T-SQL
• How to use DatePart to determine if a date refers to a Saturday or Sunday

Open a new query window and copy and paste the following code into it...

-- Get rid of Row(s) Inserted tracking and messaging
SET NOCOUNT ON

-- Create a temporary variable table
DECLARE @WeekendDays TABLE
(
dDate SmallDateTime,
sType VarChar(3)
)

-- Define the date range we will be using
DECLARE @dDate SmallDateTime,
@dEndDate SmallDateTime

SET @dDate = '19900101'
SET @dEndDate = '20251231'

-- Populate the temporary table with dates
-- of all Saturdays and Sundays that fall
-- within the date range
WHILE @dDate < @dEndDate
BEGIN
If DATEPART(WEEKDAY, @dDate) = 1
INSERT INTO @WeekendDays Values(@dDate, 'SUN')

Else If DATEPART(WEEKDAY, @dDate) = 7
INSERT INTO @WeekendDays Values(@dDate, 'SAT')

SET @dDate = DATEADD(DAY, 1, @dDate)
END

This code will create and populate a temporary variable table holding the dates of all Saturdays and Sundays from January 1, 1990 through December 31, 2025.

For the remainder of this post I will refer to this code as the base code.

Now, let's have some fun and check out the data in this table! ;)

Suppose you want to know how many Saturdays are in December 2007.

You can find out this information by adding the following query below the base code and executing it (it being all code, the whole enchilada):

SELECT COUNT(*) AS NUM_WEEKEND_DAYS_IN_DEC_2007
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

Excellent! Now we know December 2007 has 10 weekend days.

Now, let's see the dates of these weekend days.

You can find the dates by adding the following query below the base code and executing it:

SELECT dDate
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

You should see 10 rows that begin with this data...

2007-12-01 00:00:00
2007-12-02 00:00:00

Now, let's get rid of the time data because we really do not need it here.

SELECT Convert(Char(8), dDate, 112)
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

Now, the data will look like this...

20071201
20071202

Now, let's Americanize this format.


SELECT Cast(Month(dDate) as VarChar)
+ '/' + Cast(Day(dDate) as VarChar)
+ '/' + Cast(Year(dDate) as VarChar)
FROM @WeekendDays
WHERE dDate BETWEEN '20071201' AND '20071231'

As you see, the data now looks like this...

12/1/2007
12/2/2007


I hope you now have a much better understanding of how to work with Dates in Sql Server. And I imagine you have some ideas you just can't wait to try out!

I apologize for the lack of indenting in the code. I actually wrote the code using indenting to make it easier to follow but once I published this post the formatting was removed. Ah well, these things happen. I think the code is clear enough you can easily follow it.

Remember to check back soon for more great SQL information. And please help us to spread the word about our blog. Tell your friends. Tell your co-workers. Heck, tell your family and even your pets!

Thanks!

Difference between DateTime and SmallDateTime - SQL Dates and Times Series

Greetings and welcome to the SQL Coach blog!

It seems that many people are a little confused about working with time and dates in SQL databases.

Are you one of the people who are wondering about working with time in SQL?

Do you just want to know more about the sql server date time types?

If you answered "YES!" to either question you will want to check this blog daily because I will be posting a thorough and easy-to-follow description of the many options available when working with dates / time in SQL Server.

Assuming all goes to plan I will post this information by Friday night!

In the meantime, here is some information to get you started...

What is the difference between SMALLDATETIME and DATETIME?

"What's that", you ask? You never heard of this SmallDateTime data type?

You are not alone! Based on the databases I have worked in while helping my clients I think it is safe to say many people do not know about the SmallDateTime data type.

That does not change the fact that it does indeed exist. And yes, even your SQL Server 2000 has it!

Here are the main differences you should know about these two datetime types:

1. Range of Dates

A DateTime can range from January 1, 1753 to December 31, 9999.
A SmallDateTime can range from January 1, 1900 to June 6, 2079.


2. Accuracy

DateTime is accurate to three-hundredths of a second.
SmallDateTime is accurate to one minute.


3. Size

DateTime takes up 8 bytes of storage space.
SmallDateTime takes up 4 bytes of storage space.


Armed with this knowledge, you may want to use SmallDateTime instead of DateTime if you only need to represent dates from January 1, 1900 to June 6, 2079 and you do not need accuracy below 1 minute. Why? Simple! Using SmallDateTime will reduce the amount of data your queries are pulling back. The size of each row will be a bit smaller.


That's it for now... be sure to check back soon for more information on using datetime in SQL Server.