November 5, 2015 at 1:48 pm
Question: Looking at an execution plan the conversion of NVARCHAR(15) to BIGINT is a big yellow exclamation NO NO. However, the numbers in the NVARCHAR(15) have leading zeros.
Technically speak 0123456789 is not an INTEGER or BIGINT, but to help the performance of my Stored Procs is there any way to allow leading zeros in a BIGINT Field??? If anybody knows I know SQL Central GURUs would know. Thanks again.
November 5, 2015 at 2:22 pm
No! Leading zeros are a display property and display properties are handled by the client application.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 5, 2015 at 2:42 pm
To help the performance of your procs, you need to make sure no implicit conversions occur on table columns that are being compared against (WHERE column_name ...) or JOINed (INNER JOIN column_name = ...).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 5, 2015 at 4:01 pm
The reason you're seeing the implicit conversion (and the warning) is because you're treating it like a number in your query.
WHERE tb.SomeColumnName = 0123456789
or
JOIN dbo.TableB tb
ON ta.SomeNumericColumn = tb.SomeColumnName
or
CASE WHEN x.Value = y.Value THEN ta.SomeNumericColumn ELSE tb.SomeColumnName END AS ConflictedCaseExpression
Treat it like an VARCHAR and you won't have the issue.
November 5, 2015 at 7:58 pm
I will look at this tomorrow. Thanks everyone.
November 6, 2015 at 12:20 pm
Digging deeper there was a varchar(15) to nvarchar(50) causing the CONVERT_IMPLICIT, changed to match the varchar(15) and problem went away. 😀
Love inheriting CODE and DATABASES.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply