Nvarchar working with logical operator working?

Question

Just need your help here.

I have a table T

A (nvarchar)         B()
-------------------------- 
'abcd'
'xyzxcz'

B should output length of entries in A for which I did

UPDATE T
SET B = LEN(A)    -- I know LEN function returns int

But when I checked out the datatype of B using sp_help T, it showed column B as nvarchar.

What's going on ?

select A
from T 
where B > 100

also returned correct output?

Why is nvarchar working with logical operators ?

Please help.

Comparison operators do work with nvarchar values using dictionary order approach. Is B a defined column in your table? if yes, what type have you specified for that? — Amir Molaei, Mar 09 '19 at 08:48
SQL Server has no moral compunctions whatsoever about implicitly converting `INT` to `CHAR` types and back. Extra insidious is that the `CHAR` -> `INT` conversion has higher priority than the other way around, so `B > 100` is implemented as `CAST(B AS INT) > 100`, and will fail if `B` contains a string that's not convertible this way. Needless to say, you should always take good care that columns are given appropriate types -- SQL Server won't complain if you don't, but reliability and performance will take a hit. — Jeroen Mostert, Mar 09 '19 at 08:55
@sneh999 . . . The question simply does not make sense to me. `UPDATE` does not change the type of a column. The type is whatever the definition of the table is. Why would you think `UPDATE` *would* change the type? — Gordon Linoff, Mar 09 '19 at 13:02

score 3 · Accepted Answer · answered Mar 09 '19 at 08:56

Check https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-2017 where it is said that data types are converted explicitly or implicitly when you move, compare or store a variable. In your case, you are comparing column B with 100, forcing sql server to implicitly convert it to integer type (check the picture about conversions on the same page). As a prove, try to alter a row putting some text in column B and, after repeating your select query B>100, sql server will throw a conversione error trying to obtain an integer out of your text.

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

It works because of implicit conversion between types.

Data type precedence

When an operator combines expressions of different data types, the data type with the lower precedence is first converted to the data type with the higher precedence. If the conversion isn't a supported implicit conversion, an error is returned.

Types precedence:
16. int
...
25. nvarchar (including nvarchar(max) )

In you example:

select A
from T 
where B > 100
     --nvarchar and int (B is implicitly casted to INT)

JBJ · Answer 3 · 2019-03-09T09:02:21.867

when adding a column to a table in ssms, not adding a datatype a "default" datatype is chosen. for me on 2017 developer it's nchar(10). if you want it to be int define the column with datatype of int. in tsql it'd be

create table T (
     A nvarchar --for me the nvarchar without a size gives an nvarchar(2)
    ,B int
);

sp_help T

--to make a specific size, largest for nvarchar is 4000 or max...max is the replacement for ntext of old, as.
create table Tmax (
     A nvarchar(max)
    ,B int
);

--understanding nvarchar and varchar for len() and datalength()

select 
     datalength(N'wibble') datalength_nvarchar -- nvarchar is unicode and uses 2 bytes per char, so 12
    ,datalength('wibble') datalength_varchar -- varchar uses 1 byte per so 6
    ,len(N'wibble') len_nvarchar -- count of chars, so 6
    ,len('wibble') len_varchar -- count of char so still 6

nvarchar(max) and varchar(max)

hope this helps, the question is a bit discombobulated

Nvarchar working with logical operator working?

3 Answers3