dateKey in DimDate - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

dateKey in DimDate

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-01-03 : 08:07:49

what is the best choise the type of the column:
Decimal(8,0) or int ?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-03 : 11:18:58

Int is likely better given it's range of values is
-2,147,483,648 to 2,147,483,647

decimal you risk overflow..would depend on your estimated populated range of values.

Poor planning on your part does not constitute an emergency on my part.

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-01-03 : 14:07:04

i define it Decimal(8,0),(limit it on 8 character),so why i have overflow?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-01-03 : 14:26:20

Int uses only 4 bytes of storage vs. 5 bytes for decimal(8,0). You can add a check constraint on the column to limit the values that can be stored.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-03 : 14:31:31

quote:
Originally posted by inbs

i define it Decimal(8,0),(limit it on 8 character),so why i have overflow?

It depends on the data you plan to populate it with.Given your subject line..8 digits should be enough. No dates (for quite some time) will overflow the column.


Create table #foo (number decimal(8,0) not null)

INSERT #foo VALUES (100000000) <=overflows the decimal column

drop Table #foo
GO
Create table #foo (number int not null) 

INSERT #foo VALUES (100000000) <=does not overflow the int column




drop Table #foo

Poor planning on your part does not constitute an emergency on my part.

inbs
Aged Yak Warrior

860 Posts

Posted - 2011-01-04 : 01:33:23

i will never get number more than 8 characters
in your example you wrtire number with 9 characters (100000000)

still i do not understand why i preffer in ?
(if i use decimal(8,0) ,it prevent to insert more than 8 characters)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-04 : 07:33:58

You have answered your own question. You asked our opinion without any clarity as to the purpose of the field. Int takes less space as per Rob's reply. I only showed you the 9 digit number because you asked "why overflow?", so I showed you a sample of one. As you have noted, the data won't exceed 8 digits, so you can pick either int or decimal (8,0).

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources