smallint instead of decimal

Question

smallint instead of decimal

scorupco

Mr or Mrs. 500

Points: 525
More actions
November 24, 2010 at 1:13 am

#243205

Hello,
I was wondering what is your opinion about storing decimal data as int i.e.decimal(3,1) as smallint
examples:
decimal = smallint
30.3 = 303
3.0 = 30
0.3 = 3
Biggest advantage is that instead of 5 bytes (decimal with precision up to 9)
we can have 2 bytes (smallint)
It means less data pages and better disk I/O
Marcin

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Brigadur SSCrazy Points: 2097 More actions · Answer 1

Hi,

My first thought is that it should work for storage, but can be tricky if you have to do operations (mathematical, aggregations, ...) on the data.

Cheers

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Yes, it will mean less space and better I/O.

As has been pointed out, this approach makes you responsible for remembering to divide by 10 forever. If you are going to be indexing on this column, you will also have to remember to multiply your search values by 10 before searching.

Finally, you open yourself up to unexpected values being stored unless you put a constraint on the column to limit the min/max values.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

99.99999% of the time, that is going to be a very bad idea.

Just store the data is a datatype that most closely fits the real type of the data.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

go

Declare @input int

Declare @input1 decimal(3,1)

Select @input ='303'

Select @input1 ='30.3'

Select @input ,@input1

go

Declare @input int

Declare @input1 decimal(3,1)

Select @input ='30'

Select @input1 ='3.0'

Select @input ,@input1

go

Declare @input int

Declare @input1 decimal(3,1)

Select @input ='3'

Select @input1 ='0.3'

Select @input ,@input1

go

If this is your case if you need to show the data in decimal then how will you do ???

You need use function to derive this.If you use function it degrade in performance in convertion

As Michael said it is bad idea of having smallint in place of decimal

Thanks

Parthi

Thanks
Parthi

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Assuming SQL 2K8 Enterprise or SQL 2K8 R2 Standard + , just turn on compression.

You'll get the same IO boost with a very small CPU hit and zero change of troubles with the data between SQL and all the people involved.

scorupco Mr or Mrs. 500 Points: 525 More actions · Answer 6

First of all thank you for your opinions

At the moment we are using sql server 2005.

I know that in sql server 2005 SP2 there is data compression for decimal/numeric but this type of compression is useless in case with precision up to 9 because it still will use 5 bytes.

The idea of storing decimal as int is quite tempting but I understand all cons and I know that in production it might be too dangerous.

Once again thanks

Marcin

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

marcinSkorupka (11/25/2010)
First of all thank you for your opinions
At the moment we are using sql server 2005.
I know that in sql server 2005 SP2 there is data compression for decimal/numeric but this type of compression is useless in case with precision up to 9 because it still will use 5 bytes.
The idea of storing decimal as int is quite tempting but I understand all cons and I know that in production it might be too dangerous.
Once again thanks
Marcin

I have not studied compression in depth but I just tried page compression on a 15 GB DB that almost exclusively uses decimal(38,20) and the compression ratio is over 50%.

So while I implicitely trust your souce of information, taking 30 minutes to install SP2 on a test server and activate compression on that table seems like a worthwile test in my mind.

scorupco Mr or Mrs. 500 Points: 525 More actions · Answer 8

http://technet.microsoft.com/en-us/library/cc917696.aspx

point 6 - vardecimal

Marcin

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 9

Michael Valentine Jones (11/24/2010)
99.99999% of the time, that is going to be a very bad idea.
Just store the data is a datatype that most closely fits the real type of the data.

You guys need to listen to what Michael wrote above. 😉 Unless you're writing out to a file that requires such a thing, it's a horrible idea.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 10

marcinSkorupka (11/25/2010)
http://technet.microsoft.com/en-us/library/cc917696.aspx
point 6 - vardecimal
Marcin

Ummm... IIRC correctly, vardecimal was deprecated almost as fast as it was born.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004366 More actions · Answer 11

marcinSkorupka (11/25/2010)
First of all thank you for your opinions
At the moment we are using sql server 2005.
I know that in sql server 2005 SP2 there is data compression for decimal/numeric but this type of compression is useless in case with precision up to 9 because it still will use 5 bytes.
The idea of storing decimal as int is quite tempting but I understand all cons and I know that in production it might be too dangerous.
Once again thanks
Marcin

Agh... I need to read all posts before responding. It's good to see that you understand the dangers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)