October 5, 2007 at 8:18 am
At my workplace, all our DBs are "static". We rarely have to create a new one, and when it is created (in the past) it was created by a developer. All the databases are small, so it's never been much of a problem.
Now we're redesigning some systems and I get to finally put my DB design knowledge to use. So, here I am, changing datetime to smalldatetime, money to smallmoney, int to smallint or tinyint and being wowed by how much space I can save by using proper datatypes instead of the general "big" datatypes. Then I ran a test against my newly designed DB. The test populates the tables I just redesigned. And wouldn't you know it, in one of my procs I got an Overflow error on a TinyInt column.
Except... Well, all the tiny int columns were fine. I had no values over 255 and no values under zero. So what the heck was going on?
Then I realized one of my queries does some math involving both ints and tinyints. After testing, I realized the expression was trying to convert the int into a tinyint to do the math instead of the other way around. :blink:
Has anyone else noticed this? That SQL Server, when doing mathmatical expressions, tries to force things into the smallest datatype instead of upward converting the small stuff into the biggest available datatype?
Is this a bug or a deliberate feature? And why would anyone want to do this deliberately? I would think it only makes sense to implicitly convert "up" from tinyint to smallint or int rather than convert "down" from int to smallint or tinyint. Doesn't it?
October 5, 2007 at 9:06 am
Implicit conversions are sometimes funny. I'm sure someone knows what rules apply, but I've been caught by this before.
I think it's a design decision that you have to pick one to convert the other two for comparison purposes. Have you tried changing the ordering? Not sure if that matters.
October 5, 2007 at 9:44 am
I have so many mathmatical expressions in this one particular query, each of which use the tinyint, that I'm not sure which one set off the problem. I'm using the full gamit, +, -, * and divide. And in the more complicated ones, if you change the order, you change your results.
When I figured out what the problem was, I just did a CONVERT() on all of them rather than try and figure out which particular mathmatical expression was causing the problem. I know that decision will cost me some processing power, but after an initial testing, I didn't see an appreciable difference between the new execution time and the old one.
I'll run a few more tests to make sure I'm not killing myself with the Convert() and if not, I'll just leave it as is. If so, then I'll look further into finding the exact expression(s) that are causing me issues.
October 5, 2007 at 12:40 pm
Ha! I think I found it. Looks like a SUM( ) was probably the issue. And SQL Server didn't implictly convert it up to a smallint or int datatype.
October 5, 2007 at 12:53 pm
Just me, but the only "small" data type I wouldn't use is smalldatetime. Why introduce a "Y2K" type issue by limiting your dates to January 1, 1900, through June 6, 2079. Most of us won't be working in 2079, but you never know if your app might. Plus, if you are working in a financial arena like mortgages, you could run into a problem as early as June 6, 2039 (40 year amortization).
October 5, 2007 at 12:59 pm
Lynn Pettis (10/5/2007)
Just me, but the only "small" data type I wouldn't use is smalldatetime. Why introduce a "Y2K" type issue by limiting your dates to January 1, 1900, through June 6, 2079. Most of us won't be working in 2079, but you never know if your app might. Plus, if you are working in a financial arena like mortgages, you could run into a problem as early as June 6, 2039 (40 year amortization).
That is correct SmallDateTime is to be used to remove duplicate times not to be used for column definition because resolution is limited it rounds to one minute which can cause serious performance problems when SQL Server is truncating time inserted in the application layer.
Kind regards,
Gift Peddie
October 5, 2007 at 12:59 pm
Perhaps, but the biggest "term" we have is 7 years. Plus, we already have plans to get off the current system in the next five years anyway.
And since hard drive space for the current system is hard to come by and we have a power user who likes to consume it (106 GB worth of saved data from a database that's barely 17 GB), I need to save space in every way possible for the current stuff.
So, it's worth the potential Y2k issue. Especially since, if I have to, I can easily convert back up.
October 5, 2007 at 1:16 pm
Last company I worked for kept saying the same thing about the propriatary system that supported the business from the time I started to the time I left in 2005. I warned them about a serious date flaw that would bite them on May 17, 2007 over four years ago, and presented a plan to remedy the situation before it happened. My boss said we'd be off the system by then and shot down the project. Guess what, on May 18, 2007 many financial processes and reports died horrible deaths.
I hope you are correct that you will be getting off this particular app in the time frame you mentioned. By the way, the application i supported at my previous company is still running and is close to 30 years old, written in COBOL and uses ISAM databases.
😎
October 5, 2007 at 1:28 pm
We've actually already started the re-write and we've gotten complete buy in from upper management on the issue. Especially when we told them what we were going to be able to do with the new system that we can't do with the waaaaaaayy out of date current system.
And since we track our business by the day, not by the minute/millisecond, converting back up from smalldatetime to datetime won't corrupt or lose any important data. We're keeping the regular datetime (and using timestamp) for the instances where we need to track that stuff. Unfortunately, there are a lot of spots where we didn't need to track the small stuff and the bigger datatypes got stuck in there for "future expansion"...
... which of course never came.
I love the "I used an int because we're going to have more values that 0,1, and 2 in this column once we do Project X" line. Five years later, we're still using 0 & 1 (never 2) and no one will let me change it to a bit column because there's still that possibility that we'll use 2. :hehe:
October 5, 2007 at 1:32 pm
You know, of course, as soon as they let you change to a bit field, that would be when they would want to add the value "2" to the system. In this case, since it is an int, they should at least allow it to be changed to a smaller data type, like a tinyint.
😎
October 6, 2007 at 8:41 pm
I've heard a lot of folks gripe that using SMALLINT and SMALLDATETIME actually slowed things down because of the datatype byte mismatch with computer "word" size on PC's. So, I wrote a simple test... datatype doesn't seem to make much of a difference in performance if the datatype you're using is the same as what's in the table...
DROP TABLE JBMTest
GO
--===== Create and populate a 1,000,000 row test table.
-- Takes about 23 seconds to execute.
SELECT TOP 1000000
RowNum = IDENTITY(INT,1,1),
SomeInt = CAST(RAND(CAST(NEWID() AS VARBINARY))*32000+1 AS INT),
SomeSmallInt = CAST(RAND(CAST(NEWID() AS VARBINARY))*32000+1 AS SMALLINT),
SomeTinyInt = CAST(RAND(CAST(NEWID() AS VARBINARY))*254+1 AS SMALLINT),
SomeDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME),
SomeSmallDate = CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS SMALLDATETIME)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2 --Lack of join criteria makes this a CROSS-JOIN
--===== A table is not properly formed unless a Primary Key has been assigned
ALTER TABLE dbo.JBMTest
ADD PRIMARY KEY CLUSTERED (RowNum)
--===== Run the tests... takes about xx seconds to run
DECLARE @StartTime DATETIME
PRINT REPLICATE('-',78)
PRINT 'Testing update on INT...'
SET @StartTime = GETDATE()
UPDATE dbo.JBMTest
SET SomeInt = SomeInt + 1
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds'
PRINT REPLICATE('-',78)
PRINT 'Testing update on SMALLINT...'
SET @StartTime = GETDATE()
UPDATE dbo.JBMTest
SET SomeSmallInt = SomeSmallInt + 1
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds'
PRINT REPLICATE('-',78)
PRINT 'Testing update on TINYINT...'
SET @StartTime = GETDATE()
UPDATE dbo.JBMTest
SET SomeTinyInt = SomeTinyInt + 1
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds'
PRINT REPLICATE('-',78)
PRINT 'Testing update on DATETIME...'
SET @StartTime = GETDATE()
UPDATE dbo.JBMTest
SET SomeDate = SomeDate + 1.0
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds'
PRINT REPLICATE('-',78)
PRINT 'Testing update on SMALLDATETIME...'
SET @StartTime = GETDATE()
UPDATE dbo.JBMTest
SET SomeSmallDate = SomeSmallDate + 1.0
PRINT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds'
PRINT REPLICATE('-',78)
The only thing that I'd advise is to be care about converting to the BIT data type... if you ever want to do a "SUM", you're pretty much screwed without an explicit conversion.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2007 at 4:26 am
IMHO, If datatypes are used correctly, no one should ever have to SUM() on a bit datatype. After all, it's supposed to be indicative of a true/false status, right?
For example (not a real one, btw), column "Yellow" has a bit on it. 1 if "product IS yellow" and zero if "product IS NOT yellow". If we want the total of yellow products, rather than SUM() the Yellow column, we would simply do a Select Count(ProductID) from MyTable where Yellow = 1.
On the otherhand, my experimentation has shown that if you do a SUM() on a tinyint or smallint, you have the same problem as you would if you SUM() a bit. Tinyint & smallint definitely have to be explictly convert/cast so you don't run into arithmetic overflow.
And all I have to say is, "Darnit! Computers are supposed to do what you WANT them to do. Not what you TELL them to do!!!" 😀
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply