convert field type and performance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

convert field type and performance

Author

Topic

s2002
Starting Member

49 Posts

Posted - 2010-02-19 : 08:34:48

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 09:22:41

Apart from performance, working with nvarchar(max) is much easier compared to ntext as it supports all the functions of normal varchar fields. text,ntext etc on the other hand supports only limited string manipulation functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

s2002
Starting Member

49 Posts

Posted - 2010-02-19 : 10:51:00

Thanks for reply,
but don't you think text data with DATALENGTH greater than 300,000 should not be save in Nvarchar(max)?

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 10:54:21

"Does it lead to better performance"

Interesting question.

I think your biggest issue would be that 60% of your articles are > 20K. There will therefore be considerable network time to transfer this data from SQL Server to Application.

Make sure you do NOT use SELECT * anywhere, and in particular you do not use SELECT * in any statement where you do not specifically need the [body] column.

Moving the [body] column to a "parallel" table with a 1:1 relationship with articles might help performance - particularly if you have lots of queries that do NOT reference [body] column. But that's just a gut-feeling based on my experience.

The 1:1 table could even be placed in a separate NDF file

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 10:55:38

quote:
Originally posted by s2002

Thanks for reply,
but don't you think text data with DATALENGTH greater than 300,000 should not be save in Nvarchar(max)?

using nvarchar or varchar depends on whether you want to store unicode or nonunicode data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-02-19 : 13:39:43

hi,
I use this
SELECT id, DATALENGTH(body)
FROM dbo.articles
where DATALENGTH(body)>20000

Results shows that 60 percent of Total Records has DATALENGTH >20000
Currently "body" field is ntext field. I want to convert this field datatype to nvarchar(max).
After I convert, Does it lead to better performance.

Sincerely

I think you should use varchar(max) - less space, if body is not unicode, else using nvarchar(max).

With the query provided above, perfomance is the same for ntext and nvarchar(max). However, as someone mentiioned, you have more functions to use with nvarchar(max). So for some particular queries, using nvarchar(max) is a bit better.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-19 : 13:58:24

quote:
Originally posted by namman

hi,
I use this
SELECT id, DATALENGTH(body)
FROM dbo.articles
where DATALENGTH(body)>20000

Results shows that 60 percent of Total Records has DATALENGTH >20000
Currently "body" field is ntext field. I want to convert this field datatype to nvarchar(max).
After I convert, Does it lead to better performance.

Sincerely

I think you should use varchar(max) - less space, if body is not unicode, else using nvarchar(max).

With the query provided above, perfomance is the same for ntext and nvarchar(max). However, as someone mentiioned, you have more functions to use with nvarchar(max). So for some particular queries, using nvarchar(max) is a bit better.

All these points are already stated. See earlier posts

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

s2002
Starting Member

49 Posts

Posted - 2010-02-19 : 15:44:18

Special thanks to you for replies,

my Users insert unicode characters to this table. As I read "default setting for NVARCHAR(MAX) is to store its text value in the table structure, unless the text is over 8,000 bytes at which point it behaves like an NTEXT and stores the text value in the LOB , and stores a pointer to the text in the table."
Now base on this and the point that most of my table records has greater length than 8KB
Should I use "Ntext" or "Nvarchar(max).

Sincerely,

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-19 : 16:00:37

It's already been stated several times that NTEXT and NVARCHAR(MAX) are basically the same thing. The main difference being that NTEXT is depercated and NVARCHAR(MAX) as nore built in manipulation functions.

From BOL:

quote:
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 01:47:19

" Should I use "Ntext" or "Nvarchar(max)."

I don't think we are "getting" what your concern is. Perhaps describe that, and that may point at some issue that needs addressing / reassuring.

Personally I can't think of any instance where I would not want to change n/TEXT to n/VARCHAR(MAX) as mart of my SQL 2008 migration (well, maybe not on day one! but over time), and I have quite a few instances of VARCHAR(8000) [which is a limitation on the user, in order to avoid TEXT datatype in the server] when I am very keen to change to VARCHAR(MAX)

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 01:54:27

Actually I don't know what MS added VARCHAR(MAX) - isntead of just making TEXT behave as VARCAHR(MAX) now does (and provide a backward-compatibility-flag if necessary to disable use of RIGHT(MyText) etc.). VARCAHR(MAX) is a silly name anyway ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 02:26:51

<<
VARCAHR(MAX) is a silly name anyway ...
>>

Bu better than varchar(782653) ,varchar(9762344) ,varchar(2147483647),etc

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 02:56:55

Not better than TEXT ... or even TEXT2 ... or BigVarchar

TinyInt, SmallInt, Int, BigInt

SmallDatetime, Datetime, Datetime2

varchar, text, varchar(MAX)

the naming convention is a mess ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 03:00:10

quote:
Originally posted by Kristen

Not better than TEXT ... or even TEXT2 ... or BigVarchar

TinyInt, SmallInt, Int, BigInt

SmallDatetime, Datetime, Datetime2

varchar, text, varchar(MAX)

the naming convention is a mess ...

the best one for me was datetime2 . reminds me of time when we learnt programming, naming variables as temp,temp2,...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 03:06:59

Will confuse Oracle people migrating. They will want varchar2 too.

We only had single character variable names (plus an optional single digit) when I started. Spaces were optional (as the parser knew where a 1-character identifiers started and finished

) so we use variable names to generate code like:

FOR K = A TO M

which was also legally written as:

FORK=ATOM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 03:09:47

quote:
Originally posted by Kristen

Will confuse Oracle people migrating. They will want varchar2 too.

We only had single character variable names (plus an optional single digit) when I started. Spaces were optional (as the parser knew where a 1-character identifiers started and finished ) so we use variable names to generate code like:

FOR K = A TO M

which was also legally written as:

FORK=ATOM

thats a good one

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 04:09:41

quote:
Originally posted by Kristen

Will confuse Oracle people migrating. They will want varchar2 too.

We only had single character variable names (plus an optional single digit) when I started. Spaces were optional (as the parser knew where a 1-character identifiers started and finished ) so we use variable names to generate code like:

FOR K = A TO M

which was also legally written as:

FORK=ATOM

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources