CODEWITHSUNDEEP
sql-servercastingdatabase-performance
Срба
Срба

Reputation: 473

Why is SQL cast expensive?

I was wondering what is the background explanation for this? I see from testing examples that casting is very time consuming but I don't realize why since I don't know mechanics in behind, and I would like to figure it out in details.

I have found somewhere that this is because casting usage disables index usage, but in the example below we were using cast in the query result only, while all the joins where done without casting. The casting here was used only to enable us use distinct clause, since it didn't work for ntext columns. So, indexes shouldn't be impacted by this, but the execution time difference is so obvious comparing to the same query without distinct and cast:

select distinct 
    cast(Table1.NtextColumnName1 as NVARCHAR(MAX)), ...
from Table1 join Table2 on Table1.ColumnName2 = Table2.ColumnName3
    join ...

Thank you for the time.

Upvotes: 1

Views: 2291

Answers (1)

Daniel B
Daniel B

Reputation: 807

I don't think it is the CAST which time-consuming, it is probably the DISTINCT, since this requires the server to examine the contents of all Table1.NtextColumnName1 values, sort them and compare them with each other to find the unique values.

Upvotes: 2

Related Questions