CONVERT text to varchar

I am upgrading another Access database to SQL Server and have hit a small
obstacle. The database has a column named Remarks and is of the datatype
text. I used the following query to find the maximum size of the Remarks
column,
SELECT MAX(Datalength(Remarks))
FROM Admissions
This returned 939.
I decided to use varchar instead, so I inserted a new column, RemarksVar and
used the following query to convert Remarks to RemarksVar,
UPDATE Admissions SET
RemarksVar = CONVERT(varchar(1000),Remarks)
Now when I look at the data, it looks fine, but when I use the following
query,
SELECT Datalength(Remarks) AS OLDLEN, LEN(RemarksVar) AS NEWLEN
FROM Admissions
It returns the old lengths and new lengths. The issue is that the new
lengths are EXACTLY half of the old lengths. Can anyone explain to me why
this happened like this?
Thanks,
DrewI think either the old column is NTEXT or the new column is NVARCHAR. N
means Unicode data and it occupies twice the space. This is reflected in
DATALENGTH() (which measures the number of bytes) but not LEN() (which
measures the number of characters). See this simple example:
DECLARE @.foo NVARCHAR(32), @.bar VARCHAR(32)
SET @.foo = N'splunge'
SET @.bar = 'splunge'
SELECT LEN(@.foo), DATALENGTH(@.foo),
LEN(@.bar), DATALENGTH(@.bar)
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:us3D1dmoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>I am upgrading another Access database to SQL Server and have hit a small
>obstacle. The database has a column named Remarks and is of the datatype
>text. I used the following query to find the maximum size of the Remarks
>column,
> SELECT MAX(Datalength(Remarks))
> FROM Admissions
> This returned 939.
> I decided to use varchar instead, so I inserted a new column, RemarksVar
> and used the following query to convert Remarks to RemarksVar,
> UPDATE Admissions SET
> RemarksVar = CONVERT(varchar(1000),Remarks)
> Now when I look at the data, it looks fine, but when I use the following
> query,
> SELECT Datalength(Remarks) AS OLDLEN, LEN(RemarksVar) AS NEWLEN
> FROM Admissions
> It returns the old lengths and new lengths. The issue is that the new
> lengths are EXACTLY half of the old lengths. Can anyone explain to me why
> this happened like this?
> Thanks,
> Drew
>
>|||You are correct... it was ntext. Sorry I didn't specify, I guess I didn't
think there was much difference between text and ntext.
Whenever I upsize an mdb database to SQL server, it always converts the text
(in Access) fields to nvarchar. Should I change over all of these nvarchar
to varchar?
Thanks for your help, I just wanted to make sure that I wasn't missing data!
Drew
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%2382YYhmoFHA.3312@.tk2msftngp13.phx.gbl...
>I think either the old column is NTEXT or the new column is NVARCHAR. N
>means Unicode data and it occupies twice the space. This is reflected in
>DATALENGTH() (which measures the number of bytes) but not LEN() (which
>measures the number of characters). See this simple example:
> DECLARE @.foo NVARCHAR(32), @.bar VARCHAR(32)
> SET @.foo = N'splunge'
> SET @.bar = 'splunge'
> SELECT LEN(@.foo), DATALENGTH(@.foo),
> LEN(@.bar), DATALENGTH(@.bar)
>
>
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:us3D1dmoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>|||> Whenever I upsize an mdb database to SQL server, it always converts the
> text (in Access) fields to nvarchar. Should I change over all of these
> nvarchar to varchar?
If you don't need Unicode support, yes, however depending on the size of
your tables the conversion might not be worth the effort. If you're going
to keep any ntext/nchar/nvarchar then please read:
http://www.aspfaq.com/2354
http://www.aspfaq.com/2522|||Whether you need unicode support or not depends on your data. Do you
currently store any unicode characters? Can you expect unicode characters in
the future?
ML|||I don't foresee any reasons for including unicode in my database. I may
regret this statement down the road, but these databases are localized and
not used abroad.
Thanks,
Drew
"ML" <ML@.discussions.microsoft.com> wrote in message
news:A4A87FAB-09B4-4F4D-92BB-F0E1980F7802@.microsoft.com...
> Whether you need unicode support or not depends on your data. Do you
> currently store any unicode characters? Can you expect unicode characters
> in
> the future?
>
> ML|||Thanks for your reply! Since I am upgrading these databases from Access, I
have to do quite a bit to the database to get it upgraded. The programmer
before me didn't use foreign keys, so I add them in and there are some other
problems that I need to fix.
I think I will convert my n's to regulars to save space.
Thanks,
Drew
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ealzkwmoFHA.2156@.TK2MSFTNGP09.phx.gbl...
> If you don't need Unicode support, yes, however depending on the size of
> your tables the conversion might not be worth the effort. If you're going
> to keep any ntext/nchar/nvarchar then please read:
> http://www.aspfaq.com/2354
> http://www.aspfaq.com/2522
>