Changing data-types

[Danny]

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

[Greg D. Moore \(Strider\)]

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" <ad*********@gm ail.comwrote in message
news:11******** **************@ o80g2000hse.goo glegroups.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

[Gert-Jan Strik]

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

[Erland Sommarskog]

Danny (ad*********@gm ail.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, es****@sommarsk og.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

[Danny]

On Sep 5, 1:12 am, Erland Sommarskog <esq...@sommars kog.sewrote:

Danny (adler.da...@gm ail.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...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

Thanks you guys.

Danny