Hello, I have read your replies and looked at the link so hopefully I have the cause of the SEEK / SCAN action clear???

When passing in a NVarchar to be compared to the NC Index of VarChar type one value has to be converted.

Based on the link you sent the Varchar is lower in order so this value must be converted. Because this value is in the index ALL values must be converted to compare, therefore a SCAN must be performed.

CONVERT_IMPLICIT(nvarchar(19),[clientname].[dbo].[CARD].[CARD_NUMBER],0)=N'123456789'

When a Varchar is compared to a Varchar no conversion is necessary therefore a SEEK in done.

If I convert the field to a BIGINT datatype a conversion is needed between a NVarchar and a BIGINT. But this time the BIGINT is higher up the list so the NVARCHAR get converted to BIGINT, therefore it only happens once so a SEEK can be performed.

Seek Keys[1]: Prefix: [clientname].[dbo].[CARD].CARD_NUMBER = Scalar Operator(CONVERT_IMPLICIT(bigint,[@1],0))

thanks