JDBC - varchar or nvarchar fields?

[D.]

Hi, I'm starting a new application in java using JTDS jdbc driver
(http://jtds.sourceforge.net) and SQLServer 2005 Express.
I have to design the database from scratch and my doubt is if I have to use
varchar or nvarchar fields to store string data.

Any experience about performance issues using nvarchar instead of varchar
(considering that Java internally works in unicode too)?

Thanks in advance,
Davide.

[Jack Vamvas]

what is the nature of the data you are storing?
varchar sores variable length non-Unicode
nvarchar stores variable length Unicode
Essentially if your not storing multilingual data use varchar , as nvarchar
takes up twice as much storage space.
--
----
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"D." <[e-mail]> wrote in message news:e3**********@newsreader.mailgate.org...

Hi, I'm starting a new application in java using JTDS jdbc driver
(http://jtds.sourceforge.net) and SQLServer 2005 Express.
I have to design the database from scratch and my doubt is if I have to use varchar or nvarchar fields to store string data.

Any experience about performance issues using nvarchar instead of varchar
(considering that Java internally works in unicode too)?

Thanks in advance,
Davide.

[joeNOSPAM]

The Microsoft client-DBMS protocol sends string data in two possible
ways, and the DBMS will treat that data differently depending on
which way it was sent. If you have tables with indexes on varchar
columns, the DBMS *will not* use nvarchar data to search these
indexes, so you get ill-performing table scans. Similarly, if you have
indexes on nvarchar columns, the dbms will not use varchar data for
searches. This means your data and column types should match.
Java's 16-bit characters match the nvarchar form better. By default
most JDBC drivers for Microsoft will send string data as nvarchar so
they don't corrupt any potential 16-bit data. They also usually have
an optional property which will tell the driver to send strings as
varchar,
but they usually can't mix-and-match. Lastly, consider that you may
want to deal with internationalized strings. For these reasons, I
recommend that you standardize on NVARCHAR columns for your
string data.
Yes, nvarchar takes 16 bits per char where varchar takes 8, but
nowadays scrimping every last bit of disk space is not typically a
driving economic concern.

Joe Weinstein at BEA Systems

[D.]

The application is multilingual and I've set the collation of SQL Server to
latin1
I'm tempted to use nvarchar (because jtds driver seems to send strings as
unicode), but I don't know:
1) How this will impact on performances of the application.
2) If I have to use a different sintax on my queries (for example using
N'xxxxxx' instead of 'xxxxxxx' for strings)
3) Any other issues on using nvarchar

Thanks,
Davide.

[joeNOSPAM]

The N'xxxxx syntax would be redundant but not harmful. It
just means that the data in nvarchar, and if the driver is
sending strings as unicode (all MSSQL JDBC drivers do
this by default), it is unnecessary for prepared statement
parameter values. For constants it might be good.
The performance issue is as I described. The huge performance
problem comes by mismatching varchar with nvarchar. If you
stick to one, there is no problem. I believe the amount of
space taken up by nvarchar compared to varchar is a minimal
disk space issue only. Because your app. is multilingual, I
can't imagine a reason you'd continue to consider varchar.
Joe

Eg: PreparedStatement p = c.prepareStatement("select * from foo where
bar = \"N'myconstant\" and qwe = ?");
p.setString(1, "myvariabledoesnotneedacapitalN");

Joe Weinstein at BEA Systems