data conversion -- varchar to nvarchar

Hello,
The following question applies to SQL Server 2000 SP3 and SQL Server 2005:
We are in the process of investigating international support for our
application and part of that will likely require changing all of the
char/varchar columns in our database to nchar/nvarchar.
From what I gather by reading these forums, altering the columns in place
via ALTER TABLE statements is an acceptable method for doing this.
The question that I have been unable to completely confirm, however, is what
happens to existing data in the tables? Does the data get converted to
Unicode as part of the ALTER TABLE statement? And, if so, is there any risk
that this conversion will produce unexpected or unpredictable results?
Preliminary testing seems to indicate that the data does get converted
successfully during this process but I am just trying to confirm that
suspicion.
Thanks in advance for any assistance,
JohnLx> From what I gather by reading these forums, altering the columns in place
> via ALTER TABLE statements is an acceptable method for doing this.
> The question that I have been unable to completely confirm, however, is
> what
> happens to existing data in the tables? Does the data get converted to
> Unicode as part of the ALTER TABLE statement?
Yes, but of course there can't already be any data in there that requires
Unicode. Other than disk space requirements for those columns doubling (and
don't forget indexes), you shouldn't see any noticeable difference, except
(see next comment).

> And, if so, is there any risk
> that this conversion will produce unexpected or unpredictable results?
Absolutely. For char/varchar in the smaller size ranges (up to 4000) you
shouldn't see any problems. However, if you have a varchar(8000) with at
least one tuple with 4001 or more characters, you will get the following
when you try to convert VARCHAR(8000) to NVARCHAR(4000) (the largest size
for varying double-wide):
Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.
The statement has been terminated.
In addition, you'll want to reindex and update statistics for any tables
that have indexes/statistics on the column(s) you're changing. You'll also
want to verify that you re-compile (and alter params and converts, where
necessary) any stored procedures or functions that reference the columns.
You might also want to issue sp_refreshview for any views that reference
those tables.
A|||John,
the data get's converted in place as long as they fit into the new
datatype. Inyour case there shouldn't be an problem. The only thing
you have to keep in mind is while you can store up 8000 char in a
varchar column, nvarchar uses twice the space and thus the limit is
4000 characters.
Markus|||Whenever you alter the structure of a table, it can cause index
fragmentation. You will want to use DBCC INDEXDEFRAG for each altered table.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"johnlx" <nomail@.discussions.microsoft.com> wrote in message
news:2288C9DC-40B8-48E0-A63B-143A622A7030@.microsoft.com...
> Hello,
> The following question applies to SQL Server 2000 SP3 and SQL Server 2005:
> We are in the process of investigating international support for our
> application and part of that will likely require changing all of the
> char/varchar columns in our database to nchar/nvarchar.
> From what I gather by reading these forums, altering the columns in place
> via ALTER TABLE statements is an acceptable method for doing this.
> The question that I have been unable to completely confirm, however, is
> what
> happens to existing data in the tables? Does the data get converted to
> Unicode as part of the ALTER TABLE statement? And, if so, is there any
> risk
> that this conversion will produce unexpected or unpredictable results?
> Preliminary testing seems to indicate that the data does get converted
> successfully during this process but I am just trying to confirm that
> suspicion.
> Thanks in advance for any assistance,
> JohnLx
>|||Thanks to everyone who responded. You confirmed what I thought with regards
to the data conversion.
Aaron, good point about the views. I hadn't thought to rebuild them but
will include that in the process.
Thanks,
--John Lennox|||On Mon, 19 Dec 2005 14:23:08 -0500, Aaron Bertrand [SQL Server MVP]
wrote:
(snip)
>In addition, you'll want to reindex and update statistics for any tables
>that have indexes/statistics on the column(s) you're changing. You'll also
>want to verify that you re-compile (and alter params and converts, where
>necessary) any stored procedures or functions that reference the columns.
>You might also want to issue sp_refreshview for any views that reference
>those tables.
Hi Aaron (and JohnLx),
And to avoid implicit conversions that might damage performance, the
next step would be to check all variable declarations, temp table
definitions and table variable definitions - they too should be changed
from varchar to nvarchar and from char to nchar.
And finally, put an N in front of all string constants in your code.
(I.e., use SET @.var = N'Text', not SET @.var = 'Text')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Data Conversion

Here is my table:
CustomerShortName varchar (50)
CustomerID varchar(8)
OrderMonth char(2)
OrderYear char(4)
OrderCount varchar(50)
RegionID varchar(50)
Then the report is based on this sproc:
CREATE PROCEDURE dbo.renMonthlyOrderReport
(
@.StartMonth AS INT,
@.StartYear AS INT,
@.RegionIDs varchar(25)
)
AS
DECLARE @.Month1 AS INT, @.Month2 AS INT, @.Month3 AS INT, @.Month4 AS INT, @.Month5 AS INT, @.Month6 AS INT
DECLARE @.Year1 AS INT, @.Year2 AS INT, @.Year3 AS INT, @.Year4 AS INT, @.Year5 AS INT, @.Year6 AS INT
SELECT @.Month1 = @.StartMonth
SELECT @.Month2 = @.StartMonth + 1
SELECT @.Month3 = @.StartMonth + 2
SELECT @.Month4 = @.StartMonth + 3
SELECT @.Month5 = @.StartMonth + 4
SELECT @.Month6 = @.StartMonth + 5
SELECT @.Year1 = @.StartYear
SELECT @.Year2 = @.StartYear
SELECT @.Year3 = @.StartYear
SELECT @.Year4 = @.StartYear
SELECT @.Year5 = @.StartYear
SELECT @.Year6 = @.StartYear
IF @.Month1 > 12 SELECT @.Year1 = @.Year1 + 1
IF @.Month2 > 12 SELECT @.Year2 = @.Year2 + 1
IF @.Month3 > 12 SELECT @.Year3 = @.Year3 + 1
IF @.Month4 > 12 SELECT @.Year4 = @.Year4 + 1
IF @.Month5 > 12 SELECT @.Year5 = @.Year5 + 1
IF @.Month6 > 12 SELECT @.Year6 = @.Year6 + 1
IF @.Month1 > 12 SELECT @.Month1 = @.Month1 - 12
IF @.Month2 > 12 SELECT @.Month2 = @.Month2 - 12
IF @.Month3 > 12 SELECT @.Month3 = @.Month3 - 12
IF @.Month4 > 12 SELECT @.Month4 = @.Month4 - 12
IF @.Month5 > 12 SELECT @.Month5 = @.Month5 - 12
IF @.Month6 > 12 SELECT @.Month6 = @.Month6 - 12
SELECT
DISTINCT(dbo.TD_SALES.CustomerShortName),
CAST([dbo].[TD_SALES].OrderMonth as int) as OrderMonth,
CAST([dbo].[TD_SALES].OrderYear as int) OrderYear,
[dbo].[TD_SALES].RegionID,
dbo.TD_SALES.OrderCount
FROM
[dbo].[TD_SALES]
WHERE
(
[dbo].[TD_SALES].OrderMonth = @.Month1 AND [dbo].[TD_SALES].OrderYear = @.Year1 OR
[dbo].[TD_SALES].OrderMonth = @.Month2 AND [dbo].[TD_SALES].OrderYear = @.Year2 OR
[dbo].[TD_SALES].OrderMonth = @.Month3 AND [dbo].[TD_SALES].OrderYear = @.Year3 OR
[dbo].[TD_SALES].OrderMonth = @.Month4 AND [dbo].[TD_SALES].OrderYear = @.Year4 OR
[dbo].[TD_SALES].OrderMonth = @.Month5 AND [dbo].[TD_SALES].OrderYear = @.Year5 OR
[dbo].[TD_SALES].OrderMonth = @.Month6 AND [dbo].[TD_SALES].OrderYear = @.Year6
)
AND
[dbo].[TD_SALES].RegionID IN (SELECT * FROM RegionsTable(@.RegionIDs))
ORDER BY
dbo.TD_SALES.RegionID,
CAST([dbo].[TD_SALES].OrderMonth as int) DESC,
CAST([dbo].[TD_SALES].OrderYear as int) DESC,
dbo.TD_SALES.OrderCount DESC,
dbo.TD_SALES.CustomerShortName
GO
The OrderCount field is varchar because the data can be actual count , * , or null. But right now in my report I have a matrix table. How would I go about displaying the * character, null or a actual number but still be able to use the matrix for subtotaling?
Thanks,
AlTry this as the expression in your data cell (this will treat * as 0 for
aggregation purposes).
=iif(InScope("matrix1_ColumnGroup") AND
InScope("matrix1_RowGroup"),Fields!OrderCount.Value,Sum(iif(Fields!OrderCoun
t.Value="*",0,Fields!OrderCount.Value)))
Change the group names to be the innermost row and column group names in
your matrix.
If you want any * to result in * for the aggregate, you'd do this instead:
=iif(InScope("matrix1_ColumnGroup") AND
InScope("matrix1_RowGroup"),Fields!OrderCount.Value,
iif(Sum(iif(Fields!OrderCount.Value="*",1,0))>0,"*",Sum(iif(Fields!OrderCoun
t.Value="*",0,Fields!OrderCount.Value)))
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Al" <Al@.discussions.microsoft.com> wrote in message
news:D3794A37-C147-492F-BE37-870BF036C541@.microsoft.com...
> Here is my table:
> CustomerShortName varchar (50)
> CustomerID varchar(8)
> OrderMonth char(2)
> OrderYear char(4)
> OrderCount varchar(50)
> RegionID varchar(50)
> Then the report is based on this sproc:
> CREATE PROCEDURE dbo.renMonthlyOrderReport
> (
> @.StartMonth AS INT,
> @.StartYear AS INT,
> @.RegionIDs varchar(25)
> )
> AS
> DECLARE @.Month1 AS INT, @.Month2 AS INT, @.Month3 AS INT, @.Month4 AS INT,
@.Month5 AS INT, @.Month6 AS INT
> DECLARE @.Year1 AS INT, @.Year2 AS INT, @.Year3 AS INT, @.Year4 AS INT, @.Year5
AS INT, @.Year6 AS INT
> SELECT @.Month1 = @.StartMonth
> SELECT @.Month2 = @.StartMonth + 1
> SELECT @.Month3 = @.StartMonth + 2
> SELECT @.Month4 = @.StartMonth + 3
> SELECT @.Month5 = @.StartMonth + 4
> SELECT @.Month6 = @.StartMonth + 5
> SELECT @.Year1 = @.StartYear
> SELECT @.Year2 = @.StartYear
> SELECT @.Year3 = @.StartYear
> SELECT @.Year4 = @.StartYear
> SELECT @.Year5 = @.StartYear
> SELECT @.Year6 = @.StartYear
> IF @.Month1 > 12 SELECT @.Year1 = @.Year1 + 1
> IF @.Month2 > 12 SELECT @.Year2 = @.Year2 + 1
> IF @.Month3 > 12 SELECT @.Year3 = @.Year3 + 1
> IF @.Month4 > 12 SELECT @.Year4 = @.Year4 + 1
> IF @.Month5 > 12 SELECT @.Year5 = @.Year5 + 1
> IF @.Month6 > 12 SELECT @.Year6 = @.Year6 + 1
> IF @.Month1 > 12 SELECT @.Month1 = @.Month1 - 12
> IF @.Month2 > 12 SELECT @.Month2 = @.Month2 - 12
> IF @.Month3 > 12 SELECT @.Month3 = @.Month3 - 12
> IF @.Month4 > 12 SELECT @.Month4 = @.Month4 - 12
> IF @.Month5 > 12 SELECT @.Month5 = @.Month5 - 12
> IF @.Month6 > 12 SELECT @.Month6 = @.Month6 - 12
>
> SELECT
> DISTINCT(dbo.TD_SALES.CustomerShortName),
> CAST([dbo].[TD_SALES].OrderMonth as int) as OrderMonth,
> CAST([dbo].[TD_SALES].OrderYear as int) OrderYear,
> [dbo].[TD_SALES].RegionID,
> dbo.TD_SALES.OrderCount
>
> FROM
> [dbo].[TD_SALES]
> WHERE
> (
> [dbo].[TD_SALES].OrderMonth = @.Month1 AND [dbo].[TD_SALES].OrderYear =@.Year1 OR
> [dbo].[TD_SALES].OrderMonth = @.Month2 AND [dbo].[TD_SALES].OrderYear =@.Year2 OR
> [dbo].[TD_SALES].OrderMonth = @.Month3 AND [dbo].[TD_SALES].OrderYear =@.Year3 OR
> [dbo].[TD_SALES].OrderMonth = @.Month4 AND [dbo].[TD_SALES].OrderYear =@.Year4 OR
> [dbo].[TD_SALES].OrderMonth = @.Month5 AND [dbo].[TD_SALES].OrderYear =@.Year5 OR
> [dbo].[TD_SALES].OrderMonth = @.Month6 AND [dbo].[TD_SALES].OrderYear =@.Year6
> )
> AND
> [dbo].[TD_SALES].RegionID IN (SELECT * FROM RegionsTable(@.RegionIDs))
> ORDER BY
> dbo.TD_SALES.RegionID,
> CAST([dbo].[TD_SALES].OrderMonth as int) DESC,
> CAST([dbo].[TD_SALES].OrderYear as int) DESC,
> dbo.TD_SALES.OrderCount DESC,
> dbo.TD_SALES.CustomerShortName
> GO
>
> The OrderCount field is varchar because the data can be actual count ,
* , or null. But right now in my report I have a matrix table. How would I
go about displaying the * character, null or a actual number but still be
able to use the matrix for subtotaling?
> Thanks,
> Al|||I forgot something. You need to cast as well. Try this:
=iif(InScope("matrix1_MonthGroup") AND
InScope("matrix1_LenderNameGroup"),Fields!OrderCount.Value,
Sum(iif(Fields!OrderCount.Value="*",0,CDbl(Fields!OrderCount.Value))))
Or use CInt instead if the numbers are all integers.
--
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Al" <Al@.discussions.microsoft.com> wrote in message
news:203A383D-AAF1-49BD-8BC3-05CE64858D23@.microsoft.com...
> I used your expression in the data cell
> =iif(InScope("matrix1_MonthGroup") AND
InScope("matrix1_LenderNameGroup"),Fields!OrderCount.Value,
Sum(iif(Fields!OrderCount.Value="*",0,Fields!OrderCount.Value)))
> I get the desired output for the * char but it still returns '#Error'
if the condition of Fields!OrderCount.Value = "*" is not true.
> "Chris Hays [MSFT]" wrote:
> > Try this as the expression in your data cell (this will treat * as 0 for
> > aggregation purposes).
> >
> > =iif(InScope("matrix1_ColumnGroup") AND
> >
InScope("matrix1_RowGroup"),Fields!OrderCount.Value,Sum(iif(Fields!OrderCoun
> > t.Value="*",0,Fields!OrderCount.Value)))
> >
> > Change the group names to be the innermost row and column group names in
> > your matrix.
> >
> > If you want any * to result in * for the aggregate, you'd do this
instead:
> > =iif(InScope("matrix1_ColumnGroup") AND
> > InScope("matrix1_RowGroup"),Fields!OrderCount.Value,
> >
iif(Sum(iif(Fields!OrderCount.Value="*",1,0))>0,"*",Sum(iif(Fields!OrderCoun
> > t.Value="*",0,Fields!OrderCount.Value)))
> >
> > --
> > My employer's lawyers require me to say:
> > "This posting is provided 'AS IS' with no warranties, and confers no
> > rights."
> >
> > "Al" <Al@.discussions.microsoft.com> wrote in message
> > news:D3794A37-C147-492F-BE37-870BF036C541@.microsoft.com...
> > > Here is my table:
> > > CustomerShortName varchar (50)
> > > CustomerID varchar(8)
> > > OrderMonth char(2)
> > > OrderYear char(4)
> > > OrderCount varchar(50)
> > > RegionID varchar(50)
> > >
> > > Then the report is based on this sproc:
> > > CREATE PROCEDURE dbo.renMonthlyOrderReport
> > > (
> > > @.StartMonth AS INT,
> > > @.StartYear AS INT,
> > > @.RegionIDs varchar(25)
> > > )
> > > AS
> > > DECLARE @.Month1 AS INT, @.Month2 AS INT, @.Month3 AS INT, @.Month4 AS
INT,
> > @.Month5 AS INT, @.Month6 AS INT
> > > DECLARE @.Year1 AS INT, @.Year2 AS INT, @.Year3 AS INT, @.Year4 AS INT,
@.Year5
> > AS INT, @.Year6 AS INT
> > >
> > > SELECT @.Month1 = @.StartMonth
> > > SELECT @.Month2 = @.StartMonth + 1
> > > SELECT @.Month3 = @.StartMonth + 2
> > > SELECT @.Month4 = @.StartMonth + 3
> > > SELECT @.Month5 = @.StartMonth + 4
> > > SELECT @.Month6 = @.StartMonth + 5
> > >
> > > SELECT @.Year1 = @.StartYear
> > > SELECT @.Year2 = @.StartYear
> > > SELECT @.Year3 = @.StartYear
> > > SELECT @.Year4 = @.StartYear
> > > SELECT @.Year5 = @.StartYear
> > > SELECT @.Year6 = @.StartYear
> > >
> > > IF @.Month1 > 12 SELECT @.Year1 = @.Year1 + 1
> > > IF @.Month2 > 12 SELECT @.Year2 = @.Year2 + 1
> > > IF @.Month3 > 12 SELECT @.Year3 = @.Year3 + 1
> > > IF @.Month4 > 12 SELECT @.Year4 = @.Year4 + 1
> > > IF @.Month5 > 12 SELECT @.Year5 = @.Year5 + 1
> > > IF @.Month6 > 12 SELECT @.Year6 = @.Year6 + 1
> > >
> > > IF @.Month1 > 12 SELECT @.Month1 = @.Month1 - 12
> > > IF @.Month2 > 12 SELECT @.Month2 = @.Month2 - 12
> > > IF @.Month3 > 12 SELECT @.Month3 = @.Month3 - 12
> > > IF @.Month4 > 12 SELECT @.Month4 = @.Month4 - 12
> > > IF @.Month5 > 12 SELECT @.Month5 = @.Month5 - 12
> > > IF @.Month6 > 12 SELECT @.Month6 = @.Month6 - 12
> > >
> > >
> > > SELECT
> > > DISTINCT(dbo.TD_SALES.CustomerShortName),
> > > CAST([dbo].[TD_SALES].OrderMonth as int) as OrderMonth,
> > > CAST([dbo].[TD_SALES].OrderYear as int) OrderYear,
> > > [dbo].[TD_SALES].RegionID,
> > > dbo.TD_SALES.OrderCount
> > >
> > >
> > > FROM
> > > [dbo].[TD_SALES]
> > >
> > > WHERE
> > > (
> > > [dbo].[TD_SALES].OrderMonth = @.Month1 AND [dbo].[TD_SALES].OrderYear => > @.Year1 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month2 AND [dbo].[TD_SALES].OrderYear => > @.Year2 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month3 AND [dbo].[TD_SALES].OrderYear => > @.Year3 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month4 AND [dbo].[TD_SALES].OrderYear => > @.Year4 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month5 AND [dbo].[TD_SALES].OrderYear => > @.Year5 OR
> > > [dbo].[TD_SALES].OrderMonth = @.Month6 AND [dbo].[TD_SALES].OrderYear => > @.Year6
> > > )
> > > AND
> > > [dbo].[TD_SALES].RegionID IN (SELECT * FROM RegionsTable(@.RegionIDs))
> > >
> > > ORDER BY
> > > dbo.TD_SALES.RegionID,
> > > CAST([dbo].[TD_SALES].OrderMonth as int) DESC,
> > > CAST([dbo].[TD_SALES].OrderYear as int) DESC,
> > > dbo.TD_SALES.OrderCount DESC,
> > > dbo.TD_SALES.CustomerShortName
> > > GO
> > >
> > >
> > > The OrderCount field is varchar because the data can be actual
count ,
> > * , or null. But right now in my report I have a matrix table. How
would I
> > go about displaying the * character, null or a actual number but still
be
> > able to use the matrix for subtotaling?
> > >
> > > Thanks,
> > > Al
> >
> >
> >