Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Scott
Posting Yak Master
145 Posts |
 Posted - 2004-07-28 : 03:02:21
|
| I have a table that collects data with a number of interger fields. We then do a lot of analysis on this table, eg: calculating percentages etc.. (cast(IntField1 as float)/cast(IntField2 as float)*100)Design/Performance question:Is it better to always store the values as in and do the cast in the query or should I just change the initial datatype to a float and speed? up the query?ThanksScott |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-07-28 : 03:43:17
|
| Firstly, dont use float. Float is an approximate datatype and can give you funny results especially when you nest expressions. Best use numeric, it even lets you define the precision and scale you want.To answer you question: Its basically a speed versus space trade-off. Using a numeric will take (on an average) twice as much space as an int. But if you store it as an int, you will need to cast it at runtime, which is not so great for speed. Given the cost of storage these days, I'd say speed wins over space anytime. So you are probably better off storing the values using the data type: numeric(9,2) instead of int, and then you can avoid doing all those casts.OS |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-28 : 08:08:18
|
| Is there a possibility that any of those fields will need to hold decimal places? If so, they should be numeric. If not, I would stick to INT. I would also consider if the upper limits can ever go beyond the limits for smallint and tinyint.The only place you suffer a big speed difference in casting is if it's used in the where or from clauses. The cost of casting in the select is so minor that it's not really even a consideration to defining and holding the data as what it really is. Just another opinion for you.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
|
|
|
Store Int or float