Hi All,

I have a varchar(255) column for storing text in english and in
hebrew.
It was a stupid mistake to set the data type to be varchar, because
now I need to store text in german and francais too.

Question: Is it safe to change the data type from varchar to nvarchar,
without damaging the data that's already present?
(I have more than 1000000 records stored...)

Thanks,
Danny

This should work, but note it'll take awhile and your DB may be unavailable
during that time.



--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

"Danny" <adler.danny@gmail.com> wrote in message
news:1188918798.881371.235040@o80g2000hse.googlegr oups.com...
> Hi All,
>
> I have a varchar(255) column for storing text in english and in
> hebrew.
> It was a stupid mistake to set the data type to be varchar, because
> now I need to store text in german and francais too.
>
> Question: Is it safe to change the data type from varchar to nvarchar,
> without damaging the data that's already present?
> (I have more than 1000000 records stored...)
>
> Thanks,
> Danny
>

Yes, it is safe, even though it will seriously impact server performance
during the conversion, cost a lot of log space (temporarily). Also, you
should expect serious growth of the required space for the table, expect
the current table size * 2.

BTW, the simple test below proves the change is safe.

CREATE TABLE Test (a varchar(255))
INSERT INTO Test VALUES ('a')
INSERT INTO Test VALUES (REPLICATE('b',255))
INSERT INTO Test DEFAULT VALUES
SELECT * FROM Test
go
ALTER TABLE Test ALTER COLUMN a nvarchar(255)
go
SELECT * FROM Test
DROP TABLE Test

--
Gert-Jan


Danny wrote:
>
> Hi All,
>
> I have a varchar(255) column for storing text in english and in
> hebrew.
> It was a stupid mistake to set the data type to be varchar, because
> now I need to store text in german and francais too.
>
> Question: Is it safe to change the data type from varchar to nvarchar,
> without damaging the data that's already present?
> (I have more than 1000000 records stored...)
>
> Thanks,
> Danny

Danny (adler.danny@gmail.com) writes:
> I have a varchar(255) column for storing text in english and in
> hebrew.
> It was a stupid mistake to set the data type to be varchar, because
> now I need to store text in german and francais too.
>
> Question: Is it safe to change the data type from varchar to nvarchar,
> without damaging the data that's already present?
> (I have more than 1000000 records stored...)

Provided that the collation of the column is a Hebrew collation, yes.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Sep 5, 1:12 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Danny (adler.da...@gmail.com) writes:
> > I have a varchar(255) column for storing text in english and in
> > hebrew.
> > It was a stupid mistake to set the data type to be varchar, because
> > now I need to store text in german and francais too.
>
> > Question: Is it safe to change the data type from varchar to nvarchar,
> > without damaging the data that's already present?
> > (I have more than 1000000 records stored...)
>
> Provided that the collation of the column is a Hebrew collation, yes.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thanks you guys.

Danny