Блог

В рамках запроса на создание уникального идентификатора с добавлением нуля я создал то, что, как я думал, будет простым утверждением

 right('00000000'   convert(char(6),ID),6)
  

Однако это вообще не получилось как заполненные нулем символы.
Дальнейшее расследование показывает, что все не так, как я ожидал.
См.:

 drop table #test 
go
select --top 30
    right('00000000'   convert(varchar(6),ID),6) varcharPadRight, --results in varchar(6) in tempdb
    right('00000000'   convert(char(6),ID),6) charPadRight, --results in varchar(6) in tempdb
    right('00000000'   convert(char(6),ID),20) charPadRight20, --results in varchar(14) in tempdb
    right('00000000'   convert(varchar(6),ID),20) vcharPadRight20 --results in varchar(14) in tempdb
into #test
from requestidentifier aTableWithAnIntIdentityColumn
where aTableWithAnIntIdentityColumn.ID in (1,100,1000)
go
select * from #test

select left(so.name, 5) name,sc.name, sc.xtype, sc.length from tempdb..sysobjects so inner join tempdb..syscolumns sc on so.id = sc.id where so.name like '%test%'
  

Результатом этого являются:

 varcharPadRight charPadRight charPadRight20 vcharPadRight20
--------------- ------------ -------------- ---------------
000100          100          00000000100    00000000100
001000          1000         000000001000   000000001000
000001          1            000000001      000000001
  

и

 tableName colName        xtype length
--------- -------------- ----- ------
#test     varcharPadRigh 167   6
#test     charPadRight   167   6
#test     charPadRight20 167   14
#test     vcharPadRight2 167   14
  

Где xtype 167 — это varchar .

Есть ли кто-нибудь, кто может объяснить порядок операций, которые могут привести к этим (для меня) неожиданным результатам?

(Это поведение соответствует SQL Server 2005 и 2008)

Простое объяснение заключается в том, что тип данных char имеет пробелы справа, чтобы сделать его длиной переменной. Итак, если у вас есть символ (6) и установите для него значение ‘3’, значение в переменной фактически будет равно 3-пробел-пробел-пробел-пробел. Это 3, за которым следует 5 пробелов, чтобы общая длина = 6 символов.

Когда вы добавляете 6 нулей слева от строки, SQL выполняет некоторые преобразования типов данных. Жесткое кодирование строки приведет к varchar, поэтому ‘000000’ будет иметь тип данных varchar(6). Когда вы добавляете char и varchar, результатом является varchar с объединенными длинами.

 '000000'   Convert(Char(6), int)
VarChar(6)   Char(6)
varchar(12)
  

В части Char(6) справа от данных по-прежнему будут заполнены пробелы, поэтому, когда вы берете 6 самых правых символов, вы получаете пробелы.

varchar не заполняется пробелами в конце, поэтому он работает точно так, как вы ожидаете.

ДОКАЗАТЕЛЬСТВО:

 Declare @ID Int
Set @Id = 3

-- Data type after converting to char (results in char(6))
SELECT SQL_VARIANT_PROPERTY(Convert(Char(6), @id), 'BaseType') As DatType, 
       SQL_VARIANT_PROPERTY(Convert(Char(6), @id), 'MaxLength') As Length,
       Convert(Char(6), @id)

-- data type of hard coded string (Results in varchar(6))
SELECT SQL_VARIANT_PROPERTY('000000', 'BaseType') As DatType, 
       SQL_VARIANT_PROPERTY('000000', 'MaxLength') As Length,
       '000000'

-- data type of varchar concatenate char (Results in varchar(12))
SELECT SQL_VARIANT_PROPERTY('000000'   Convert(Char(6), @id), 'BaseType') As DataType,
       SQL_VARIANT_PROPERTY('000000'   Convert(Char(6), @id), 'MaxLength') As Length,
       '000000'   Convert(Char(6), @id)

-- data type of the result (results in varchar(6))
SELECT SQL_VARIANT_PROPERTY(Right('000000'   Convert(Char(6), @id), 6), 'BaseType') As DataType,
       SQL_VARIANT_PROPERTY(Right('000000'   Convert(Char(6), @id), 6), 'MaxLength') As Length,
       Right('000000'   Convert(Char(6), @id), 6)
  

Преобразование int в char(6) выравнивается по левому краю, поэтому

    int -> char(6) -> '000'   char(6) -> right('000'   char(6))
    1  -> 1______ -> '0001_____'     -> '1_____'
    10 -> 10_____ -> '00010____'     -> '10____'
    etc
  

Таким образом, rtrim в коде даст ожидаемые результаты
, например

 right('00000000'   rtrim(convert(char(6),ID)),6)