T-SQL int equivalents of decimal/numeric types

Monday, 17 January 2011

The DECIMAL type in T-SQL is for storing fixed precision numbers. You can define the total number of digits to store (the precision), as well as how many digits are to the right of the decimal point (the scale)

eg. to store a number in the range –9.9 to 9.9 (with 0.1 increments), you could use decimal(2, 1).

Sometimes database systems may store integer values using their equivalent of the decimal type (with a scale of zero), and when you use SSIS to import the data, it just defaults to creating a compatible schema.

There are some storage and potential performance benefits to using integer data types, so it may be worth checking whether the source data would actually fit inside a native int (or tinyint or bigint).

First, let's look at the storage requirements for decimal types. As you can see from Table 1, even a decimal(1,0) will still take up 5 bytes.

Table 1 - Bytes required to store decimal precision types
Decimal precision Storage bytes
1-9 5
10-19 9
20-28 13
29-38 17

Contrast this with the requirements for the integer types.

Table 2 - Bytes required to store integer types
Data type Range Storage bytes
tinyint 0 to 255 1
smallint -215 (-32,768) to 215-1 (32,767) 2
int -231 (-2,147,483,648) to 231-1 (2,147,483,647) 4
bigint -263 (-9,223,372,036,854,775,808) to 263-1 (9,223,372,036,854,775,807) 8

So for certain ranges of precision, the value can safely be stored in an equivalent integer type.

Table 3 - Compatible integer types
Decimal precision Integer equivalent Bytes saved
1-2 tinyint 4
3-4 smallint 3
5-9 int 1
10-18 bigint 1

If you do this, be very careful that you are not inviting overflow errors into your application.

Trial by Media Center

Monday, 10 January 2011

My Media Center has been misbehaving a bit lately, much to my (and the family's) annoyance.

First off, it the receiver started showing "tuner not available" errors. Restarting the machine would resolve the problem, but only temporarily. Installing the newer Hauppauge driver 7.9.1.28162 seems to have mostly fixed that (from the Hauppauge UK website). Along the way to resolving this, I also tried swapping the card into the other PCI Express slot, just in case that helped.

Then the Microsoft Remote stopped working. The display on the front of the Antec case said something like "bad command" for any button I pressed. Opening up the iMON software, I enabled sounds for invalid commands, and sure enough every button press resulted in the warning sound. Bizarre! The batteries in the controller were still fresh, so the only thing I could think of was to find the original IR receiver that came with the controller and plug that in. I'd never used it as the Antec case came with a receiver built in. In any case (ha ha), that seemed to resolve that one.

Then a few hours later, the sound spontaneously muted. Attempts to increase the volume worked briefly, then the volume would auotmatically wind back down to zero/mute. Alternatively, it rolled right up to 50 (100%). Rebooting and that one went away – not sure what's going on there.

So I'm keeping a close eye on things. Maybe the MCE is feeling jealous that the XBox 360 is getting some of it's screen time Smile

Internode Usage meter for Windows Phone 7

Tuesday, 28 December 2010

Screen grab of Internode Usage appMy first application for Windows Phone 7 has now been published to the marketplace!

It's a free usage meter for Internode's broadband customers. Want to check how your much quota you have left on your ADSL plan? This app will tell you.

The first version is pretty simple. It displays two sections. The first (shown above) displays your total quota, amount used so far, and the time the data was retrieved. The second section displays details for your account (see below).

To install it on your Windows Phone 7 device, go to http://social.zune.net/redirect?type=phoneApp&id=c9ebe665-de0d-e011-9264-00237de2db9e (Opens in Zune)


Overview

ScreenGrab-WelcomeWelcome screen

When you first run the app, a welcome screen is displayed and you are prompted to go to the settings page to enter your username and password. (In version 1.0, there's a bug where the big 'Settings' button doesn't do anything – you'll have to click on the settings icon Settings icon instead. This will be fixed in the next update)


ScreenGrab-Settings

Settings

Enter your Internode username (just the bit before the @ works for me)and password.

Click on the 'Save' icon to store these credentials.


ScreenGrab-DetailsDetails

Your account details, including full username, monthly quota, plan name and plan speed.

Coming next

Features I'm thinking of adding for the next update:

I'm also keen to hear of any other suggestions.

Acknowledgements

This application uses the Internode API, however for the canonical source for your usage, please always refer to My Internode.

In many ways, the app is inspired by Angus Johnson's Internode Monthly Usage Meter (MUM). I've been a happy MUM user for as long as I've had Internode ADSL.

Christmas 2010

Sunday, 26 December 2010

It's been a big Christmas season in the Gardiner family this year. Last week we dressed up as New Testament-era characters in Aberfoyle Uniting's re-creation of Bethlehem in "The Road to Christmas". Thursday evening Narelle and I joined some friends for carol singing at a local hospital, then Christmas Eve we attended church as a family. It was a good service, and included this thought-provoking modern-day take on what Mary and Joseph went through:
On Christmas day we were hosting immediate family at our place for lunch, and were then joined by some extra friends for tea. Narelle had a thought a few weeks back that to cater for this many people, a second fridge would be useful. After checking out some second-hand stores, I ended up bidding (and winning) one on eBay.
It has been a great fridge. It fitted all we needed to fit in it, and saved all those minutes of "angst" when trying to fit the leftovers in it after lunch and tea.
I must say that Narelle did an amazing job – the meat and vegies were perfectly cooked...and the pudding was out of this world.
(Narelle added the above two paragraphs while I was playing Kinect) But I agree, the fridge was a great idea.
The day started at not an unreasonable time. We'd been to Church the previous evening, so things weren't so rushed in the morning.
The XBox 360 + Kinect has proved a big success. Our family present this year, and all the family has been having a go - even our youngest (though sometimes to the frustration of other players!) – she can play some of the games, though some of the finer controls are too tricky for her yet.
We've had a lot of fun playing:
(Kinectimals and Kinect Joy Ride were presents, the other two titles came with the bundle).
So far I'm totally impressed. The only thing I could wish for would be some family-friendly titles to be published that can have more than two players at one time (I've heard Kinect can track up to 6 people, though I think you'd need lots of space for that).

Sesame Street: A Celebration of 40 Years of Life on the Street One of my other favourite presents had me up far later than I should have been Christmas night. I was only thinking the other day when we were watching "A Muppet Christmas Carol" that one of the best things to ever come out of the USA is Jim Henson's Muppets. That thought was reinforced by me getting a copy of Sesame Street: A Celebration of 40 Years of Life on the Street. A fascinating read so far.

VS2008 crashes when opening Data Flow Task

Thursday, 16 December 2010

I recently encountered a problem opening an existing SQL Server Integration Services project in Business Intelligence Development Studio (BIDS aka Visual Studio 2008). When ever I tried to view the Data Flow task for an integration package, Visual Studio would crash. These are the details recorded in the event log:

Faulting application name: devenv.exe, version: 9.0.30729.1, time stamp: 0x488f2b50
Faulting module name: msdds.dll, version: 10.0.30319.1, time stamp: 0x4ba1fee8
Exception code: 0xc0000005
Fault offset: 0x000117f8
Faulting process id: 0x2678
Faulting application start time: 0x01cb9c2181b22022
Faulting application path: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe
Faulting module path: C:\Program Files (x86)\Common Files\microsoft shared\MSDesigners8\msdds.dll
Report Id: cba37908-0814-11e0-9fdb-70f1a10159b9

One curious thing I noticed was that the version of the msdds.dll file above is 10.* not 9.*. The file properties also indicated that this was part of Visual Studio 2010.

Not being certain whether the issue was something SQL or VS related, I decided in the absence of more specific information, the most effective approach was most likely to uninstall all the installations of Visual Studio and SQL development components (I didn't uninstall the SQL instances).

Uninstalling Visual Studio 2010 and 2008, then re-installing BIDS from the SQL 2008 R2 setup replaced the msdds.dll with the original 2008 version.

I was then able to open the SSIS project without any crashing!

Re-installing VS 2010 and the dll was updated back to 10.*, but this time BIDS continues to work properly.

One thing that may be relevant is that for a short time I did have SQL Server codenamed 'Denali' installed, so it is possible that something left behind from that might have been the culprit.

This is also a good way to confirm that the LED for your hard disk works properly (and to dream about getting an SSD!)