Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2006
    Posts
    115

    Question Unanswered: Is there a need to put an 'N' infront of an numeric value?

    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?

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    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.

  3. #3
    Join Date
    Oct 2006
    Posts
    115
    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?

  4. #4
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    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).

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Suppose you have a table like this:
    Code:
    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:
    Code:
    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.

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Quote Originally Posted by MCrowley
    You will always get a table scan (or clustered index scan, which is the same thing).
    Yeah, that too

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •