Changing data-types

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

Re: Changing data-types

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" wrote in message
news:1188918798.881371.235040@o80g2000hse.googlegroups.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
>

Re: Changing data-types

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

Re: Changing data-types

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/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Changing data-types

On Sep 5, 1:12 am, Erland Sommarskog 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/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Thanks you guys.

Danny