for example, in a query,
select * from abc where emp_no = N'1234567';
I get the same results if I use this:
select * from abc where emp_no = '1234567';
so what's the point of using the 'N' in front of the numeric value?Well, it doesn't seem to be a numeric value! The quotes specify it's a string variable and on top of that the N says it's to be (explicitly) converted to a unicode string variable.
Check what data type emp_no has. If it's INT (or another numeric type) then leave out the N and the quotes altogether.|||the original type from the source is character, that's why I need the quotes. so N means "unicode strong variable"? May I know under what circumstances would it be compulsory?|||The explicit casting of the string to unicode (which the N does) is needed when emp_no is of type NCHAR, NVARCHAR of NTEXT (which are unicode datatypes).
Even then it's not compulsory because SQL Server will implicitly cast the string to the right type. But it's recommended because of performance reasons. Implicit conversions are slower than explicit conversions (and consistency in your code).|||Suppose you have a table like this:
create table employee
(emp_no varchar(10) not null primary key,
name varchar(50)
other fields as necessary)
When you query the table Like this:
select * from abc where emp_no = N'1234567';
You will always get a table scan (or clustered index scan, which is the same thing). This is because SQL Server has to do an implicit conversion to match the data up. Since nvarchar values are not guaranteed to translate to varchar, all of the values in the table are converted to varchar. Depending on how many employees you have, this will take some time.
The short of it is, always query the underlying table with the same datatype as the column in the table.|||You will always get a table scan (or clustered index scan, which is the same thing).
Yeah, that too :D
No comments:
Post a Comment