Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is there any option in ms sql server which use N prefix for my database. There are many query in my application which non of them use N prefix for nvarchar fields , and now I have to set N for all of them and it takes long time to do. My collatation is Persian_100 and it need unicode (N prefix) so any advise is appreciated

select * from table1 where field1=N'حمید' ;

I want skip N prefix in my query , but it acts as unicode for all type of queries:

share|improve this question
5  
No, there's no way of doing this. A string literal in TSQL without the N prefix is always treated as a varchar in the code page of the default collation. –  Martin Smith Jun 5 '12 at 10:41
    
So that is un professional acts, when we set type of field as nvarchar so we decide to save or select value from that field as unicode. so N prefix is boring. –  Hamid Jun 5 '12 at 10:57
2  
A character you need to type to guarantee correct results is "boring"? The SELECT keyword is boring too, but you still have to type it, unless you don't want results. –  Aaron Bertrand Jun 5 '12 at 12:33
    
No there is no way to do this automatically. When you do your programming in C# and you declare the variables as a Unicode data type it will handle this for you automatically. –  mrdenny Jun 5 '12 at 13:01
3  
When you are querying the SQL Server are you parameterizing your SQL queries or are you just going string concatination? –  mrdenny Jun 5 '12 at 15:13

1 Answer 1

No, there's no way to do this.

Any option that did what you want would be extremely confusing because there would need to be a way to turn it off within the string literal syntax. There are other considerations (like storage size) to be aware of when choosing data types, so a setting like this could be very dangerous to put in play.


With regards to .NET applications that send parameterized T-SQL statements to SQL Server, the framework takes care of this for you because a .NET String inherently supports Unicode and things were built using that assumption. This applies to ORM-type libraries as well if they're programmed well.

Consider the following code snippet:

IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = @"SELECT * FROM sys.server_principals WHERE name = @name";

IDbDataParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = "@name";
param.Value = "dbo";
cmd.Parameters.Add(param);

IDataReader dr = cmd.ExecuteReader();

What actually gets sent to SQL Server is this (line break added for clarity):

exec sp_executesql N'SELECT * FROM sys.server_principals WHERE name = @name',
    N'@name nvarchar(3)',@name=N'dbo'

You can see that the framework automatically used a Unicode type for the parameter (the length of 3 is a separate discussion entirely...).

The places where you need to modify code are in situations where SQL queries are not parameterized (or are unparameterizable, i.e., DDL) -- in other words, queries that have parameters concatenated directly into the string, or contain string constants. See my answer here for an example of how to avoid SQL injection issues when using dynamic SQL.

share|improve this answer
2  
+1 but is worth calling out that the .Net way of supporting string has a dark side, namely forcing table scans when WHERE varchar = @value is used naively with Parameters.AddWithValue. See How Data Access Code Affects Database Performance –  Remus Rusanu Jul 8 '12 at 19:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.