sql server , set N prefix as unicode for all queries by default

Question

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:

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.

Answer 2

sqlcmd.Parameters.Add("@yourparam", SqlDbType.NVarChar, 10);
sqlcmd.Parameters["@yourparam"].Value = "حميد";