DataType NUMERIC PARAMS - 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

Transact-SQL (2005)

DataType NUMERIC PARAMS

Author

Topic

TallOne
Starting Member

49 Posts

Posted - 2009-04-03 : 12:07:48

Somewhere along the line, I picked up the habit of declaring numerics as NUMERIC(18,0) VS NUMERIC I know these are precision params(18 digits 0 to the right) but I'm not sure about the length. I'd like to know how many bytes each uses to determine if there's a benefit of specifying NUMERIC(18,0) VS NUMERIC.

Also, is there any benefit of declaring using the AS keyword?
DECLARE @ID AS NUMERIC
VS
DECLARE @ID NUMERIC

Thanks in advance everyone!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-03 : 12:28:53

It's a good habit to always specify precision and scale. IIRC correctly, a future version of SQL Server will require it for numeric and decimal, and also require the size for char/varchar/nvarchar.

There's a page in Books Online that has the actual byte counts for the various levels of precision of decimal/numeric. I believe the minimum is 5 bytes, up to 7 places I believe. Since 2005 has vardecimal and 2008 allows row compression, there's an additional list showing the compressed sizes as well. Search for "vardecimal" and you should find it.

TallOne
Starting Member

49 Posts

Posted - 2009-04-03 : 14:26:57

Thank you so much for your response! I guess I'm on track then.

"a future version of SQL Server will require it for numeric and decimal, and also require the size for char/varchar/nvarchar"

Do you know where I can find a link documenting the above?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-03 : 14:51:42

I don't remember where I saw that, I may have heard it from someone at Microsoft. Check Books Online, they're pretty good about indicating things like that.

TallOne
Starting Member

49 Posts

Posted - 2009-04-07 : 17:43:36

If anyone is interested....

Declare @MyVar Numeric(18,0)
Set @MyVar = 1

SELECT SQL_VARIANT_PROPERTY(@MyVar, 'BaseType') AS [Base Type],
SQL_VARIANT_PROPERTY(@MyVar, 'Precision') AS [PRECISION],
SQL_VARIANT_PROPERTY(@MyVar, 'Scale') AS [Scale],
SQL_VARIANT_PROPERTY(@MyVar, 'TotalBytes') As TotalBytes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources