nvarchar max

Question

nvarchar max

newbie101

SSC-Addicted

Points: 468
More actions
May 30, 2009 at 7:28 pm

#75474

What is the max for nvarchar? My results are getting truncated with the following:
select comments from myTable
I've tried declaring @comments nvarchar(4000) and @comments nvarchar(max). When I copy the results from query analyzer and paste into MS Word I get the following results:
222 character count without spaces
255 character count with spaces
Thank you :crazy:

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John H Marsh SSChampion Points: 11671 More actions · Answer 1

Hello,

The truncation is probably being caused by a setting in Query Analyser. Try changing it via Tools, Options, Results, Max Characters Per Column.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

newbie101 SSC-Addicted Points: 468 More actions · Answer 2

I'm sorry. I don't understand. The value is correct in the database but it truncates when I run the select statement. What do you mean by changing it via Tools, Options, Results, Max Characters Per Column?

John H Marsh SSChampion Points: 11671 More actions · Answer 3

Hello again,

Apologies for not being clearer with my first post.

There is an option in Query Analyser that, by default, truncates the number of characters returned to the User Interface.

You can change the setting by choosing “Tools” from the Menu Bar, then “Options …”, then the “Results” tab, and you will see the property “Max Characters Per Column”. Increase the value, click “OK” and run your query again.

Hope that fixes it.

Regards,

John Marsh

www.sql.lu
SQL Server Luxembourg User Group

newbie101 SSC-Addicted Points: 468 More actions · Answer 4

I checked again and it only returns 500 characters. I increased it to 4000. :unsure:

Sergiy SSC Guru Points: 110208 More actions · Answer 5

newbie101, Query Analyzer is for analyzing queries, not for data exchange.

Try to use appropriate tools for your tasks.

And another suggestion: try to avoid using varchar(max) by all means.

I once increased my friend's overall database performance in 6 times just by replacing varchar(max) with nvarchar(1000) in several tables. No changes to the queries were done at all.

_____________
Code for TallyGenerator

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 6

Verify the data in the table that you have values > 500 characters by using LEN function. For e.g.

SELECT LEN( comments ) FROM myTable

--Ramesh

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 7

newbie101 (5/30/2009)
I checked again and it only returns 500 characters. I increased it to 4000. :unsure:

After you modify it, you have to open a new window in Query Analyzer and run the query in the new window.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

newbie101 SSC-Addicted Points: 468 More actions · Answer 8

I changed all the the nvarchar datatype to nvarchar(4000). I ran it again and it works. Thank you! 😀