nvarchar

What size to pick for a (n)varchar column?

In a slightly heated discussion on TDWTF a question arose about the size of varchar columns in a DB. For example, take a field that contains the name of a person (just name, no surname). It's quite easy to see that it will not be very long. Most people have names with less than 10 characters, and few are those above 20. If you would mak...

Determine varchar content in nvarchar columns

I have a bunch of NVARCHAR columns which I suspect contain perfectly storable data in VARCHAR columns. However I can't just go and change the columns' type into VARCHAR and hope for the best, I need to do some sort of check. I want to do the conversion because the data is static (it won't change in the future) and the columns are indexe...

Mapping to varchar and nvarchar in hibernate

If there are 2 columns in database, eg. code varchar(3) name nvarchar(50) how to tell hibernate to pass varchar for searching by code? In the hibernate mappings string is mapped to nvarchar and it produces queries like: Select code, name From table where code=N'AAA' (instead of code='AAA') This is very bad as it causes index scan i...

For Nvarchar(Max) I am only getting 4000 characters in TSQL?

Hi, This is for SS 2005. Why I am i only getting 4000 characters and not 8000? It truncates the string @SQL1 at 4000. ALTER PROCEDURE sp_AlloctionReport( @where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200)) AS DECLARE @SQL1 NVARCHAR(Max) SET @SQL1 = 'SELECT DISTINCT VenueInfo.VenueID, Ve...

Nvarchar or varchar what is better use multiply of 2 or rounded full numbers??

Hello, My question is what is better to use in generating columns in SQL. Should the size of nvarchar (varchar) be multiply of 2 (32, 64, 128) or it's doesn't matter and we can use fully numbers example '100', '50' ? Thank You very much for answers with reasons Greeting's to all ...

SQL Server CE nvarchar foreign keys with trailing whitespace

In SQL Server CE, foreign key constraints on nvarchar fields are only enforced after dropping the trailing whitespace. This means that if the PK is "foo " I can insert "foo" into the FK. Why is this the case? It seems to badly undermine the data integrity the foreign key system is supposed to provide. Is there any way to enforce a forei...

TSQL: Any benefits for explicitly specifying NVARCHAR in a string?

When you add pass a new job_type to sys.sp_cdc_add_job @job_type, (which is of type nvarchar(20)) You can pass the argument as either N'cleanup' cleanup Are there any reasons or benefits to use the former syntax using N' to pass the argument to stored procedures? ...

Linq to SQL nvarchar problem

Hi, I have discovered a huge performance problem in Linq to SQL. When selecting from a table using strings, the parameters passed to sql server are always nvarchar, even when the sql table is a varchar. This results in table scans instead of seeks, a massive performance issue. var q = ( from a in tbl where a.index == "TEST" s...

.Net Compression and SQL Server 2005 NVARCHAR(MAX)

Can I safely store a .Net compressed memory stream (System.IO.Compression) in an SQLServer 2005 NVARCHAR(MAX) field? SQLServer 2008 is not an alternative. ...

Trim trailing space from table contents

I have a table in a SQL server 2000 database with a nvarchar(30) field "details". There are some 10,000 records in that with a trailing space. I need a query to trim the particular field content in all rows. How can I achieve this? Thanks. ...

SQL Server - Better Data type to store large string value

we have a database table which has around 200,000 records. which includes 3 ntext columns, which hold string data with length vary from 4000-70000. but a mere selection on the table takes more than 1 minute to return data. and even using where condition, and indexes to select 12000 records for a condition it takes 40 sec. so we decided...

nvarchar(max) vs NText

What are the advantages and disadvantages of using the nvarchar(max) vs. NText data types in SQL Server? I don't need backward compatibility, so it is fine that nvarchar(max) isn't supported in older SQL Server releases. Edit: Apparently the question also applies to TEXT and IMAGE vs. varchar(max) and varbinary(max), for those searching...

How can I recover Unicode data which displays in SQL Server as ??????

Hi there, I have a database in SQL Server containing a column which needs to contain Unicode data (it contains user's addresses from all over the world e.g. القاهرة‎ for Cairo) This column is an nvarchar column with a collation of database default (Latin1_General_CI_AS), but I've noticed data inserted into it via SQL statements contain...

Create a file in Java for loading into an nvarchar field in SQLServer 2005 using BCP and UTF-16

Hi All, I want to use BCP to load into a SQL Server 2005 table with an nvarchar field using a loader control file. As I understand it, SQL Server 2005 only supports UTF-16 (and I believe it is UTF-16 LE). The file is being output by a Java program. The way I have it currently set up is as follows: An XML format BCP loader file (cre...

SQL nvarchar considerations

What's your strategy for determining nvarchar column sizes say for a address field? Do you always use nvarchar(max) or a fixed maximum size? There is nothing worse than data integration breaking due to too small columns. What are possible implications of using nvarchar(max) vs. a fixed maximum varchar size? I understand there is somethi...

Are there disadvantages to using VARCHAR(MAX) in a table?

Here is my predicament. Basically, I need a column in a table to hold up an unknown length of characters. But I was curious if in Sql Server performance problems could arise using a VARCHAR(MAX) or NVARCHAR(MAX) in a column, such as: 'This time' I only need to store 3 characters and most of the time I only need to store 10 characters. B...

SQL Server, varchar data to nvarchar data

I've got a database with collation Danish_Norwegian_CS_AS and lots of varchar columns. I'd like to convert all this data to unicode, but haven't found a way to convert this data yet. If I've understood correctly, the encoding used is UCS-2 little endian. For example I've got a column containing 'PÃ¥l-Trygve' which is easily converted w...

What is the performance penalty of XML data type in SQL Server when compared to NVARCHAR(MAX)?

I have a DB that is going to keep log entries. One of the columns in the log table contains serialized (to XML) objects and a guy on my team proposed to go with XML data type rather than NVARCHAR(MAX). This table will have logs kept "forever" (archiving some very old entries may be considered in the future). I'm a little worried about th...

Converting nvarchar(4000) to nvarchar(max)

Hi! We have a few tables in our SQL Server (2005 & 2008) database with columns defined as nvarchar(4000). We sometimes need to be able to store more data than that and consider to use nvarchar(max) instead. Now to the questions. Are there any performance implications we should know of? Is it safe to use "alter table" to do the actual...

Nvarchar + PDF encoding problem in Reporting Server 2005

I have a Report running on SQL 2005, say there are 2 fields I got from the database to show on the report: (A) =Field!FullName.Value (defined as varchar(250) in database) (B) =Field!Description.Value (defined as nvarchar(max) in database) When I export it to PDF, it shows up properly in Acrobat Reader. "FullName" (A) can be copi...