How to find rows that need to be nvarchar

Question

Post reply

How to find rows that need to be nvarchar

Gabe Weiny

SSC Enthusiast

Points: 193
More actions
February 8, 2007 at 4:06 am

#176764

I want to convert a field from nvarchar to varchar to increase performance. But before I do, I want to confirm what data will be stripped away when I do so.
Is there any way to run a query to select rows that have characters not supported by varchar?
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

Try casting the column to varchar(n) and then back to nvarchar(n) and seeing if it is the same.

SELECT YourNVarCharColumn

FROM YourTable

WHERE YourNVarCharColumn <>

CAST(CAST(YourNVarCharColumn as varchar(<collength&gt) as nvarchar(<collength&gt)

jezemine SSCrazy Eights Points: 8059 More actions · Answer 2

what sort of perf improvement do you expect from this change? The only difference is the n-types take twice as much space.

The only perf improvement I can see is you could fit a few more rows on a single page (depending on what fraction of the row size is made up by char data), so you might have less page reads/writes.

If most of the row is not char data, i don't see much opportunity for perf improvement.

---------------------------------------
elsasoft.org

Ken McKelvey SSCoach Points: 18856 More actions · Answer 3

>> what sort of perf improvement do you expect from this change?

One thing I have noticed is that, if a SQL collation sequence is used, comparison operations, especially LIKEs, are considerably quicker with varchar as opposed to nvarchar.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 4

This works quite well:

SELECT YourNVarCharColumn

FROM YourTable

Where binary_checksum(YourNVarCharColumn) <> binary_checksum(Convert(varchar(n), YourNVarCharColumn))

This will give you the queries to run:

Select

'Select ''' + Table_Name + ''' As TableName, ''' + Column_Name + ''' As ColumnName, [' + Column_Name + ']' + char(10) +

'From [' + Table_Name + '] with(nolock)' + char(10) +

'Where binary_checksum([' + Column_Name + ']) <> binary_checksum(Convert(varchar(' + Case Character_Maximum_Length When -1 Then 'Max' Else Str(Character_Maximum_Length) End + '), [' + Column_Name + ']))'

From

information_Schema.Columns

Where

data_type = 'nvarchar'

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]