January 15, 2014 at 9:45 am
A few days ago I asked whether or not lengthening a text field (really a VARCHAR field) would retain the data already stored there. (Please see Changing the length of a column using DDL, will the data remain?.) Now I'd like to ask a follow-up question.
What if I were to change a field's data type from VARCHAR to NVARCHAR? Will it retain the data already there? I'm concerned because I know that NVARCHAR allows for keeping for formatting information, like underline, bold and italic. I've not worked with data like that much. Whenever I have I've seen some strange characters in there which I take to be formatting commands that are properly rendered in applications using a rich text box. Does NVARCHAR require that special formatting? So even if changing a field's data type from VARCHAR to NVARCHAR does retain the data already stored in the table, I'm not sure that the data will properly render in an application or report's rich text box. So, I guess I've got 2 questions, really. (1) Will changing the data type of a field from VARCHAR to NVARCHAR retain the data already stored in a table? (2) Is the special formatting that's saved as a part of the rich text box for bolding, italicizing, underlining, etc. characters in the field required, even when the only thing in the data is plain text?
Kindest Regards, Rod Connect with me on LinkedIn.
January 15, 2014 at 9:56 am
I don't see any reason why the data would be affected.
CREATE TABLE dbo.test(data varchar(10));
INSERT INTO dbo.test
VALUES('test data');
ALTER TABLE dbo.test ALTER COLUMN data nvarchar(10);
SELECT *
FROM dbo.test;
DROP TABLE dbo.test;
if you were going the other way there is potential for data loss.
January 15, 2014 at 9:57 am
Rod at work (1/15/2014)
I'm concerned because I know that NVARCHAR allows for keeping for formatting information, like underline, bold and italic.
Wha????
It does not store formatting. It allows for storing of UNICODE characters.
http://msdn.microsoft.com/en-us/library/ms186939.aspx
You can safely store any values from the ASCII set (varchar) in a nvarchar column. Of course, that assumes that the length is remains the same or increases. In other words if you change from varchar(10) to nvarchar(8) you may have truncation issues and the engine will in fact throw a warning at you.
So why not just always use nvarchar? It takes twice as much disc space to store each character.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 15, 2014 at 12:52 pm
Hi Sean,
At this point I'm considering the possibility of using NVARCHAR. We didn't, back in the day when the database and tables were first created (~1997 or so). I'm not even sure that NVARCHAR was around, as a data type back then. And I confess that not using NVARCHAR is now more of a habit on my part, then anything else. That's probably not a good thing, but that's the way it is. Anyway, it's a habit I can learn to drop/change.
Kindest Regards, Rod Connect with me on LinkedIn.
January 15, 2014 at 12:54 pm
Hi Bob,
That's good to know that just changing a VARCHAR(10), let's say, to a NVARCHAR(10) might lead to concatenating some of the data. And it's also good to know that the database engine will likely warn me about it. Does it warn me, but then just proceed to make the change? I would assume that it does.
Kindest Regards, Rod Connect with me on LinkedIn.
January 15, 2014 at 1:13 pm
That's good to know that just changing a VARCHAR(10), let's say, to a NVARCHAR(10) might lead to concatenating some of the data. And it's also good to know that the database engine will likely warn me about it. Does it warn me, but then just proceed to make the change? I would assume that it does.
You would not get any concatenation if the data types are the same length. You would get an error if you changed the length to a value shorter than the longer value in the column. the alter would not complete successfully.
CREATE TABLE dbo.test(data varchar(20));
INSERT INTO dbo.test
VALUES('test data'),
('test data longer'),
('short');
ALTER TABLE dbo.test ALTER COLUMN data nvarchar(10);
SELECT *
FROM dbo.test;
GO
DROP TABLE dbo.test;
January 15, 2014 at 1:32 pm
Rod at work (1/15/2014)
Hi Sean,At this point I'm considering the possibility of using NVARCHAR. We didn't, back in the day when the database and tables were first created (~1997 or so). I'm not even sure that NVARCHAR was around, as a data type back then. And I confess that not using NVARCHAR is now more of a habit on my part, then anything else. That's probably not a good thing, but that's the way it is. Anyway, it's a habit I can learn to drop/change.
Why do you want to use nvarchar? You shouldn't use that datatype unless your data actually requires it. I VERY rarely use nvarchar at my current job. Unless you need to handle characters that are not supported by varchar don't switch anything. It takes up twice the storage because each character is 2 bytes instead of 1. IIRC the nchar and nvarchar datatypes were added with sql server 7.0.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 15, 2014 at 1:50 pm
Robert klimes (1/15/2014)
That's good to know that just changing a VARCHAR(10), let's say, to a NVARCHAR(10) might lead to concatenating some of the data. And it's also good to know that the database engine will likely warn me about it. Does it warn me, but then just proceed to make the change? I would assume that it does.
You would not get any concatenation if the data types are the same length. You would get an error if you changed the length to a value shorter than the longer value in the column. the alter would not complete successfully.
CREATE TABLE dbo.test(data varchar(20));INSERT INTO dbo.test
VALUES('test data'),
('test data longer'),
('short');
ALTER TABLE dbo.test ALTER COLUMN data nvarchar(10);
SELECT *
FROM dbo.test;
GO
DROP TABLE dbo.test;
Oh, OK, my misunderstanding. Thank you, Bob.
Kindest Regards, Rod Connect with me on LinkedIn.
January 15, 2014 at 1:51 pm
Sean Lange (1/15/2014)
Rod at work (1/15/2014)
Hi Sean,At this point I'm considering the possibility of using NVARCHAR. We didn't, back in the day when the database and tables were first created (~1997 or so). I'm not even sure that NVARCHAR was around, as a data type back then. And I confess that not using NVARCHAR is now more of a habit on my part, then anything else. That's probably not a good thing, but that's the way it is. Anyway, it's a habit I can learn to drop/change.
Why do you want to use nvarchar? You shouldn't use that datatype unless your data actually requires it. I VERY rarely use nvarchar at my current job. Unless you need to handle characters that are not supported by varchar don't switch anything. It takes up twice the storage because each character is 2 bytes instead of 1. IIRC the nchar and nvarchar datatypes were added with sql server 7.0.
Oh, I see. Just use NVARCHAR where it's needed; not everywhere one collects text data. Thank you for clearing that up, Sean!
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply