SQL server decimal vs numeric:
There is a small difference between NUMERIC(p,s) and DECIMAL(p,s) SQL numeric data type. NUMERIC determines the exact precision and scale. DECIMAL specifies only the exact scale; the precision is equal or greater than what is specified by the coder. (In Oracle, both are the NUMBER type). They are actually equivalent, but they are independent types, and not technically synonyms, like ROWVERSION and TIMESTAMP - though they may have been referred to as synonyms in the documentation at one time. That is a slightly different meaning of synonym (e.g. they are indistinguishable except in name, not one is an alias for the other). Ironic, right?
- What I interpret from the wording in MSDN is actually:
- These types are identical, they just have different names.
- Other than the type_id values, everything here is identical:
SELECT * FROM sys.types WHERE name IN (N'numeric', N'decimal');
I have absolutely no knowledge of any behavioral differences between the two, and going back to SQL Server 6.5, have always treated them as 100% interchangeable.
for DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a "conversion"?
Only if you do so explicitly. You can prove this easily by creating a table and then inspecting the query plan for queries that perform explicit or - you might expect - implicit conversions. Here's a simple table:
CREATE TABLE [dbo].[NumDec] ( [num] [numeric](18, 0) NULL, [dec] [decimal](18, 0) NULL );
Now run these queries and capture the plan:
DECLARE @num NUMERIC(18,0); DECLARE @dec DECIMAL(18,0); SELECT CONVERT(DECIMAL(18,0), [num]), -- conversion CONVERT(NUMERIC(18,0), [dec]) -- conversion FROM dbo.NumDec UNION ALL SELECT [num],[dec] FROM dbo.NumDec WHERE [num] = @dec -- no conversion UNION ALL SELECT [num],[dec] FROM dbo.NumDec WHERE [dec] = @num; -- no conversion
As shown in SQL Sentry Plan Explorer*, the plan is not really interesting:
But the Expressions tab sure is:
As I commented above, we have explicit conversions where we asked for them, but no explicit conversions where we might have expected them. Seems the optimizer is treating them as interchangeable, too. Go ahead and try this test, too (data and indexes).
CREATE TABLE [dbo].[NumDec2] ( [num] [numeric](18, 2) NULL, [dec] [decimal](18, 2) NULL ); INSERT dbo.NumDec2([num],[dec]) SELECT [object_id] + 0.12, [object_id] + 0.12 FROM sys.all_columns; CREATE INDEX [ix_num] ON dbo.NumDec2([num]); CREATE INDEX [ix_dec] ON dbo.NumDec2([dec]);
Now run this query:
DECLARE @num NUMERIC(18,2) = -1291334356.88, @dec NUMERIC(18,2) = -1291334356.88; SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = @num UNION ALL SELECT [num] FROM dbo.NumDec2 WHERE [num] = @dec;
Plan has no conversions (in fact the Expressions tab is empty):
Even these don't lead to any unexpected conversions. Of course you see it on the RHS in the predicate, but in no case did any conversion have to occur against the column data to facilitate the seek (much less force a scan).
SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(DECIMAL(18,2), @num) UNION ALL SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(DECIMAL(18,2), @dec) UNION ALL SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(NUMERIC(18,2), @num) UNION ALL SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(NUMERIC(18,2), @dec) UNION ALL SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(DECIMAL(18,2), @num) UNION ALL SELECT [num] FROM dbo.NumDec2 WHERE [num] = CONVERT(DECIMAL(18,2), @dec) UNION ALL SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(NUMERIC(18,2), @num) UNION ALL SELECT [dec] FROM dbo.NumDec2 WHERE [dec] = CONVERT(NUMERIC(18,2), @dec);
Personally, I prefer to use the term DECIMAL just because it's much more accurate and descriptive. BIT is "numeric" too.
* Disclaimer: I work for SQL Sentry.
