• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6596
  • Last Modified:

nVarChar to VarChar

1.  Is there any script I can write to go through all of my tables to convert all of the nVarChar fields to VarChar?

2. I have found imcomplete info on this, can anyone find concrete: Do indexes on nVarchar perform slower than their VarChar siblings?

3.  When I change the type of the field from nVarChar to VarChar, do I need to recreate the index that existed on that field?

Thanks!
0
jagoodie
Asked:
jagoodie
1 Solution
 
obahatCommented:
1. Here is the script that would do it.
however - careful! The script keeps the number of chars defined for the NVARCHAR in the VARCHAR column.
Also - check out the comment in the script that asks to include additional comments (make sure you don't alter columns in tables that you don't wish to change). Also - make sure first, that you indeed don't have any unicode characters in the underlying tables.

2. Yes. True - if the # of chars is the same (since NVARCHAR stored twice as many bytes, which is more difficult to index).

3. Yes, since the values of the entry in the data pages is stored in the index as nvarchar, and those values must be altered to varchar.



SET NOCOUNT ON

DECLARE @CurrTableName SYSNAME
DECLARE @CurrColumnName SYSNAME
DECLARE @CurrDataType SYSNAME

IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
      DROP TABLE #tbl

SELECT a.TABLE_NAME AS TableName,
      a.COLUMN_NAME AS ColName,
      a.DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(64)) + ')' AS DataType
INTO #tbl
FROM INFORMATION_SCHEMA.COLUMNS a WITH (NOLOCK)
      INNER JOIN INFORMATION_SCHEMA.TABLES b WITH (NOLOCK)
      ON a.TABLE_NAME = b.TABLE_NAME
WHERE b.TABLE_TYPE = 'BASE TABLE'
      AND a.DATA_TYPE = 'NVARCHAR'
      -- Add additional filters!


SELECT TOP 1 @CurrTableName = TableName,
      @CurrColumnName = ColName,
      @CurrDataType = RIGHT(DataType, LEN(DataType) - 1)
FROM #tbl WITH (NOLOCK)

WHILE @CurrTableName IS NOT NULL
BEGIN
      EXEC('      ALTER TABLE ' + @CurrTableName + '
            ALTER COLUMN ' + @CurrColumnName + ' ' + @CurrDataType)

      DELETE FROM #tbl
      WHERE TableName = @CurrTableName
            AND ColName = @CurrColumnName

      SELECT TOP 1 @CurrTableName = TableName,
            @CurrColumnName = ColName,
            @CurrDataType = RIGHT(DataType, LEN(DataType) - 1)
      FROM #tbl WITH (NOLOCK)
END


0
 
rafranciscoCommented:
0
 
rafranciscoCommented:
You can also check this for #2:

http://www.aspfaq.com/show.asp?id=2522

"The key is that implicit conversion *can* cause a table scan instead of an index seek, and on larger tables this can really hurt. While it's important to understand why this happens and in which scenarios, my recommendation is to match your character-based datatypes as explicitly as possible. "
0
 
Scott PletcherSenior DBACommented:
>> 3.  When I change the type of the field from nVarChar to VarChar, do I need to recreate the index that existed on that field? <<

Altering the column should automatically change the index.  Performance-wise, you might be better off dropping the index prior to the ALTER and adding it back after the ALTER.  The script could be adjusted to do that as well.


0
 
jagoodieAuthor Commented:
Good stuff.  Thanks everyone!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now