When you convert between an application variable and a SQL Server result set column, return code, parameter, or parameter marker, the supported data type conversions are defined by the database API. When data from a Transact-SQL result column, return code, or output parameter is moved into a program variable, the data must be converted from the SQL Server system data type to the data type of the variable.When data from one object is moved to, compared with, or combined with data from another object, the data may have to be converted from the data type of one object to the data type of the other.When it tries to convert the value 'Colgate' to a numeric(9,0) data type, you get the error.Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL Endpoint in Microsoft Fabric Warehouse in Microsoft Fabricĭata types can be converted in the following scenarios: Query 2 throws an error because both predicates from sales have implicit convertions and to optimizer chose to begin from sales.pid before filtering only the rows that keep numbers. The query optimizer decides the comparison of a column without a implicit convertion is less expensive, so it starts by executing the predicate sales.type = 'number' and it generates a subset composed of the rows which contains all the values of sales.type as numbers (and you can convert real numbers from a string cloumn with no problem). In this case sales.pid has an implicit convertion on both queries since it's being compared to products.idn which has a different data type. Query 1 worked because when you removed the N (which is used to convert a string to nvarchar) SQL Server didn't need to perform an implicit convertion of sales.type from varchar to nvarchar. Knowing that, ff you check the execution plan it will help you understand the reason you get the error with one query and not with the other given the sample data you provided: The query optimizer might change the order things are done to get your result the fastest way among the options it come up with (given the result of the query isn't altered). but it would be better to fix the data model such as to ensure only like or compatible data types are compared. This can be addressed with TRY_CAST, TRY_CONVERT, CASE expression, etc. ='number' AND CONVERT_IMPLICIT(numeric(9,0).,0)=(1.)Īlthough the non-Unicode literal (or parameter) may workaround the problem, the query will still be vulnerable to run time errors. The plan with the non-Unicode literal has the same shape except the predicate order in the scan operator is reversed. As to whether or not this actually happens depends on the order of evaluation in the execution plan.īelow is the sales table clustered index scan predicate from the Unicode literal execution plan, showing the conversion occurs before the 'number' condition is evaluated: CONVERT_IMPLICIT(numeric(9,0).,0)=(1.) AND CONVERT_IMPLICIT(nvarchar(10).,0)=N'number' If I modify the query slightly it works: SELECTĪ conversion error will occur at run time when an attempt is made to convert the sales.pid value 'Colgate' value to numeric(9) to evaluate the join criteria. I don't see why casting NVARCHAR to NUMERIC is an issue: SELECT CAST (N'1' as NUMERIC) INNER JOIN sales ON products.idn = sales.pid
Why does the following SELECT fail (For some reason beyond my control, the generated query has N literals for strings.)? SELECT The transaction filter takes care of JOINing correctly. Sales has mixed data i.e VARCHAR and NUMERIC. INSERT INTO sales (idn, pid, type) VALUES (2, 'Colgate', 'word') INSERT INTO sales (idn, pid, type) VALUES (1, 1, 'number') I have the following schema/data in MSSQL Server 2019 ( SQLFiddle): CREATE TABLE products(
0 kommentar(er)
