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).
create table employee
(emp_no varchar(10) not null primary key,
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.