The database cannot know the number format used in the NUMERIC_STRING column so it does it the other way around: the database converts the strings to numbers-this is an unambiguous transformation. A single value can be written in many ways: 42 A number, formatted as text, can contain spaces, punctation, and leading zeros. You might wonder why the database does not do it this way automatically? It is because converting a string to a number always gives an unambiguous result. The solution is also the same as before: do not convert the table column, instead convert the search term. An index on NUMERIC_STRING cannot be used due to the function call. PostgreSQL) many databases just add an implicit type conversion. Although some database yield an error (e.g. If you compare it using a number, however, the database can no longer use this condition as an access predicate. Of course this statement can use an index on NUMERIC_STRING. Although it is a very bad practice, it does not automatically render an index useless if you consistently treat it as string: SELECT. * Disclaimer: I work for SQL Sentry.Numeric strings are numbers that are stored in text columns. SELECT FROM dbo.NumDec2 WHERE = CONVERT(NUMERIC(18,2), I prefer to use the term DECIMAL just because it's much more accurate and descriptive. SELECT FROM dbo.NumDec2 WHERE = CONVERT(NUMERIC(18,2), ALL SELECT FROM dbo.NumDec2 WHERE = CONVERT(DECIMAL(18,2), ALL 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 FROM dbo.NumDec2 WHERE = has no conversions (in fact the Expressions tab is empty):Įven these don't lead to any unexpected conversions. A variable is nothing but a name given to a storage location. Now run this query: DECLARE NUMERIC(18,2) = NUMERIC(18,2) = -1291334356.88 This lesson introduces Variables and Data Types in Sql Server. SELECT + 0.12, + 0.12ĬREATE INDEX ON dbo.NumDec2() Go ahead and try this test, too (data and indexes). Seems the optimizer is treating them as interchangeable, too. Now run these queries and capture the plan: DECLARE NUMERIC(18,0) ĬONVERT(DECIMAL(18,0), ), - conversionĬONVERT(NUMERIC(18,0), ) - conversionįROM dbo.NumDec WHERE = - no conversionĪs shown in SQL Sentry Plan Explorer*, the plan is not really interesting:Īs I commented above, we have explicit conversions where we asked for them, but no explicit conversions where we might have expected them. 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. 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.įor DECIMAL(18,2) and NUMERIC(18,2)? Assigning one to the other is technically a 'conversion'? Other than the type_id values, everything here is identical: SELECT * FROM sys.types WHERE name IN (N'numeric', N'decimal') These types are identical, they just have different names.

What I interpret from the wording in MSDN is actually: they are indistinguishable except in name, not one is an alias for the other). That is a slightly different meaning of synonym (e.g.

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.

Sql numeric