CODEWITHSUNDEEP
sqlsql-servert-sql
nightElf91
nightElf91

Reputation: 657

How to avoid Implicit Type Conversion

I am trying to use STRING_AGG function in a INT column. And getting a implicit type conversion warning. I think its affecting my query performance. How can I avoid this?

Here's the query:

SELECT
     STRING_AGG(ES.SomeIntID, '#') AS Something
     FROM SomeTable 
     GROUP BY OtherID

I have tried to convert/cast the SomeIntID value explicitly to INT and NVARCHAR(12) but no improvement.

The warning:

convert_issue: Cardinality Estimate
expression: CONVERT(nvarchar(12), SomeIntID, 0)

It's significant as this function is being used multiple times in the query. I think this conversion is increasing my WorkFile/WorkTable logical read too.

Attempted query to convert:

STRING_AGG(CAST(ES.SomeIntID as nvarchar(12)), N'#') AS Something

AND

STRING_AGG(CAST(ES.SomeIntID as varchar(12)), '#') AS Something

Upvotes: 4

Views: 3478

Answers (1)

Jesús López
Jesús López

Reputation: 9221

I think you can safely ignore that warning on the query plan:

Type conversion in expression (CONVERT_IMPLICIT(nvarchar(12),[t].[task_id],0)) may affect "CardinalityEstimate" in query plan choice

Please look at this query plan

If cardinality estimate was affected by the conversion, then estimated number of rows would be significantly different than actual number of rows, but they are equals in this case.

Please, look at estimated and actual number of rows differences to find out if cardinality estimate might be affected.

Also replace STRING_AGG(ES.SomeIntID, '#') AS Somethingwith SUM(ES.SomeIntId) AS AnythingElse to see if query plans are different and there are different cardinality estimates.

Upvotes: 3

Related Questions