Numeric and decimal data types

Question

Numeric and decimal data types

rphilips

Mr or Mrs. 500

Points: 507
More actions
November 4, 2004 at 10:27 am

#162789

According to BOL numeric and decimal are equivalent datatypes. It is recommended that decimal be used since numeric is being maintained only for backwards compatibility and may not be implemented in new versions of SQL. I have an old database with lots of columns defined as numeric. In the interest of moving forward I planned on making any new columns decimal with the plan to eventually convert all the old columns and application code to decimal. When I try to make a foreign key constraint with the new column I get a datatype conflict error message. I cannot make a constraint between columns with numeric and decimal datatypes even though they are both defined with the same precision and scale. Is there any way around this? Also, will support for numeric be continued in SQL 2005. I haven't been able to find any information on this issue.
Error message:
Server: Msg 1778, Level 16, State 1, Line 1
Column 'dbo.PHIMS_USER.USER_Id' is not the same data type as referencing column 'Phims_User_Auth.pua_user_id' in foreign key 'FK_Phims_User_Auth_PHIMS_USER'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
Rosalind

Viewing 8 posts - 1 through 7 (of 7 total)

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

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

Unfortunatly, SQL 7 or greater will not allow a constraint between two different data types or two identical datatypes with different sizes, even if they are "functionally identical".

So, what you are typing to do will not work unless BOTH columns are of the same datatype and dimension.

Change both columns to decimal and your query will work.

Now, here's a cursor I wrote a while ago to change every column that was a float or numeric data type to an integer. It may help get you started on your task

DECLARE @s-2 varchar(200)

DECLARE @sid int

DECLARE @tabname sysname

DECLARE @colname sysname

DECLARE tab_cur INSENSITIVE CURSOR FOR

SELECT name, id

FROM sysobjects

WHERE xtype = 'U'

AND name <> 'dtproperties'

ORDER BY name

OPEN tab_cur

FETCH NEXT FROM tab_cur INTO @tabname, @sid

WHILE @@FETCH_STATUS = 0 Begin

PRINT 'Table: ' + @tabname

DECLARE col_cur INSENSITIVE CURSOR FOR

SELECT c.name

FROM syscolumns c, sysobjects o

WHERE c.id = o.id

AND c.xtype IN

(SELECT xtype

FROM systypes

WHERE name IN ('float', 'numeric'))

AND o.name = @tabname

OPEN col_cur

FETCH NEXT FROM col_cur INTO @colname

WHILE @@FETCH_STATUS = 0 Begin

SET @s-2 = 'ALTER TABLE ' + @tabname + ' ALTER COLUMN ' + @colname + ' int NULL'

EXEC(@s)

FETCH NEXT FROM col_cur INTO @colname

End

CLOSE col_cur

DEALLOCATE col_cur

FETCH NEXT FROM tab_cur INTO @tabname, @sid

End

CLOSE tab_cur

DEALLOCATE tab_cur

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

rphilips Mr or Mrs. 500 Points: 507 More actions · Answer 2

Thanks Michael. Do you know if numeric will still be supported in SQL2005?

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

Unfortunatly, I do not.

Download the beta and try to create a table. Then you can let all of us know!

Actually, there is a forum for SQL server 2005 on this site. Post there.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

I might be wrong, but I think the NUMERIC data type is still included in the SQL 2003 Standard. So, I guess it will take some time, before one can really speak of backward compatibility.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Bimal Fernando SSCommitted Points: 1945 More actions · Answer 5

Bimal Fernando

SSCommitted

Points: 1945

November 4, 2004 at 10:53 pm

#529051

Numeric is supported on SQL 2005 Beta 2.

tymberwyld SSCertifiable Points: 7810 More actions · Answer 6

If you want to upgrade your existing apps and DB from Numeric to Decimal, there is hardly anything to do.

First, create your F-Key before changing any data type. Next, from either table with the F-Key, change the Data Type to be Decimal and it will automatically update the other Table's Data Type to be Decimal as well. One thing I can't understand is why you have a Decimal as a P-Key?

If your apps are programmed with VB or .NET, there is no need to change any code from Numeric to Decimal. It's the same thing as far as programing is concerned...both are Decimals.

rphilips Mr or Mrs. 500 Points: 507 More actions · Answer 7

Thanks Everyone for all your help. We can consider this thread done!

Rosalind