SQL-Ex blog

Я имею в виду молчаливые изменения, которые происходят, когда меняют тип данных столбца с помощью оператора ALTER TABLE ... ALTER COLUMN .... Имеются два свойства, которые могут измениться, если а) текущие установки не являются установками по умолчанию, и б) они не указываются при изменении. Этими свойствами являются:

1. Допустимость NULL (NULL или NOT NULL).


2. Коллация.

Допустимость NULL

Если столбец специфицирован как NOT NULL, то если, изменяя его, вы не укажете NOT NULL, столбец будет допускать NULL-значения. Даже документация для ALTER TABLE говорит об этом:

Для ALTER COLUMN всегда используется по умолчанию ANSI_NULL; если не задать ограничение, столбец будет допускать NULL.

Давайте сами в этом убедимся. Создадим простую таблицу с двумя столбцами, которые определим как NOT NULL. Затем мы проверим это свойство для столбцов, используя представление системного каталога, и попытаемся вставить NULL.

USE [tempdb];

-- DROP TABLE dbo.AlterColumnInt;

CREATE TABLE dbo.AlterColumnInt

(

  [Col1] INT NOT NULL,

  [Col2] DATETIME NOT NULL

);

SELECT [name], system_type_id, max_length, [is_nullable], [collation_name]

FROM   sys.columns

WHERE  [object_id] = OBJECT_ID(N'dbo.AlterColumnInt');

/*

name   system_type_id   max_length   is_nullable   collation_name

Col1   56               4            0             NULL

Col2   61               8            0             NULL

*/

INSERT INTO dbo.AlterColumnInt ([Col1], [Col2])

  VALUES (NULL, '2019-01-01');

/*

Msg 515, Level 16, State 2, Line XXXXX

Cannot insert the value NULL into column 'Col1', table

  'tempdb.dbo.AlterColumnInt'; column does not allow nulls. INSERT fails.

The statement has been terminated.

(Нельзя вставить NULL в столбец 'Col1' таблицы 'tempdb.dbo.AlterColumnInt'; 

столбец не допускает NULL-значений. INSERT завершился неудачно.

Выполнение оператора прекращено.)

*/

Как видно в конце приведенного выше примера, мы получили ошибку при попытке вставить NULL. Это ожидалось. Теперь изменим тип столбца INT на BIGINT. Мы проверим, что тип данных столбца изменился, и сможем также увидеть, что изменилось и значение is_nullable. Наконец, мы попытаемся опять выполнить вставку.

ALTER TABLE dbo.AlterColumnInt

  ALTER COLUMN [Col1] BIGINT;

-- Команды выполнились успешно

-- (никаких предупреждений)

SELECT [name], system_type_id, max_length, [is_nullable], [collation_name]

FROM   sys.columns

WHERE  [object_id] = OBJECT_ID(N'dbo.AlterColumnInt');

/*

name   system_type_id   max_length   is_nullable   collation_name

Col1   127              8            1             NULL

Col2   61               8            0             NULL

*/

INSERT INTO dbo.AlterColumnInt ([Col1], [Col2])

  VALUES (NULL, '2019-01-02');

-- Получилось (упс!)

Как видно в конце примера выше, вставка NULL теперь прошла.

Отрицательное влияние
1. Потенциально неверные данные (NULL) будут допускаться.

Коллация

Если коллация столбца не является коллацией по умолчанию для базы данных, то неиспользование предложения COLLATE с текущей коллацией столбца приведет к тому, что коллация столбца изменится на коллацию по умолчанию для базы данных. Документация говорит:

Если предложение COLLATE не указано, изменение типа данных столбца приведет к изменению коллации на текущую коллацию базы данных.

Давайте проверим. Теперь нам нужно создать тестовую базу данных с тем, чтобы гарантировать, что её коллация по умолчанию и тестовая коллация столбца различаются. Я использую коллацию SQL_Latin1_General_CP1_CI_AS, поскольку она широко используется (конечно, в основном благодаря тому, что, к сожалению, она является значением по умолчанию при установке на системы с базовым английским языком, а также единственным вариантом для коллации уровня экземпляра в LocalDB и Azure SQL Database Managed Instances). Мы создадим другую простую таблицу с двумя столбцами типа VARCHAR, но теперь столбцы будут одинаковыми за исключением имен (это позволит нам увидеть разницу в поведении между изменением только размера столбца и изменением типа данных на NVARCHAR). Мы проверим свойства столбцов, а затем вставим строки: одну, содержащую иврит (кодовая страница 1255 - та, что использует коллации Hebrew_*), и другую, использующую стандартные символы ASCII (тогда мы сможем протестировать изменения в чувствительности к регистру, поскольку Hebrew не имеет букв ни в верхнем, ни в нижнем регистрах).

-- Коллация уровня экземпляра

SELECT SERVERPROPERTY('Collation');

-- Latin1_General_100_CI_AS_SC_UTF8

IF (DB_ID(N'AlterTest') IS NULL)

BEGIN

  CREATE DATABASE [AlterTest] COLLATE SQL_Latin1_General_CP1_CI_AS;

  ALTER DATABASE [AlterTest] SET RECOVERY SIMPLE;

END;

USE [AlterTest];

-- DROP TABLE dbo.AlterColumnVarchar;

CREATE TABLE dbo.[AlterColumnVarchar]

(

  [Col1] VARCHAR(10) COLLATE Hebrew_100_CS_AS_SC NOT NULL,

  [Col2] VARCHAR(10) COLLATE Hebrew_100_CS_AS_SC NOT NULL

);

SELECT [name], system_type_id, max_length, [is_nullable], [collation_name]

FROM   sys.columns

WHERE  [object_id] = OBJECT_ID(N'dbo.AlterColumnVarchar');

/*

name   system_type_id   max_length   is_nullable   collation_name

Col1   167              10           0             Hebrew_100_CS_AS_SC

Col2   167              10           0             Hebrew_100_CS_AS_SC

*/

INSERT INTO dbo.AlterColumnVarchar ([Col1], [Col2])

  VALUES (0xF9D1C8ECE5C9ED, 0xF9D1C8ECE5C9ED); --"peace" (shalom) in Hebrew

INSERT INTO dbo.AlterColumnVarchar ([Col1], [Col2])

  VALUES ('BOB', 'BOB');

Теперь мы можем выполнить несколько тестов. Сначала убедимся, что данные существуют в таблице, которые мы ожидаем, а затем проверим чувствительность к регистру посредством сравнения и, наконец, убедимся, что сравнение действительно чувствительно к регистру и не является нечувствительным.

SELECT * FROM dbo.AlterColumnVarchar;

/*

Col1     Col2

שָׁלוֹם     שָׁלוֹם

BOB      BOB

*/

SELECT * FROM dbo.AlterColumnVarchar WHERE [Col1] = 'BOB';

/*

Col1     Col2

BOB      BOB

*/

SELECT * FROM dbo.AlterColumnVarchar WHERE [Col1] = 'bob';

-- строки отсутствуют

Теперь мы изменим оба столбца. [Col1] только увеличит свой размер, а [Col2] поменяет тип на NVARCHAR.

ALTER TABLE dbo.[AlterColumnVarchar]

  ALTER COLUMN [Col1] VARCHAR(50);

ALTER TABLE dbo.[AlterColumnVarchar]

  ALTER COLUMN [Col2] NVARCHAR(50);

-- Команды выполнились успешно

-- (нет предупреждений)

SELECT [name], system_type_id, max_length, [is_nullable], [collation_name]

FROM   sys.columns

WHERE  [object_id] = OBJECT_ID(N'dbo.AlterColumnVarchar');

/*

name   system_type_id   max_length  nullable  collation_name

Col1   167              50          1         SQL_Latin1_General_CP1_CI_AS

Col2   231              100         1         SQL_Latin1_General_CP1_CI_AS

*/

Результаты, приведенные в конце кода примера, показывают следующее:

• [Col1]
  
  
  
  1. увеличил размер с 20 до 50 (ожидаемо);
  
  
  2. теперь допускает NULL;
  
  
  3. теперь имеет коллацию SQL_Latin1_General_CP1_CI_AS (которая является коллацией по умолчанию именно для базы данных, а не коллацией уровня экземпляра).
  
  
  
  


• [Col2]
  
  
  
  1. теперь NVARCHAR;
  
  
  2. размер увеличился с 20 до 100 (ожидаемо, т.к. это в байтах при том, что столбец теперь имеет тип NVARCHAR);
  
  
  3. теперь допускает NULL;
  
  
  4. коллация сменилась на SQL_Latin1_General_CP1_CI_AS (которая является коллацией по умолчанию именно для базы данных, а не коллацией уровня экземпляра).
  
  
  
  

Итак, какое влияние оказало изменение коллации?

SELECT * FROM dbo.AlterColumnVarchar;

/* (oops!)

Col1     Col2

???????  שָׁלוֹם

BOB      BOB

*/

SELECT * FROM dbo.AlterColumnVarchar WHERE [Col1] = 'BOB';

/* (expected)

Col1     Col2

BOB      BOB

*/

SELECT * FROM dbo.AlterColumnVarchar WHERE [Col1] = 'bob';

/* (oops!)

Col1     Col2

BOB      BOB

*/

Как видно из результатов, приведенных в коде выше:

• [Col1]
  
  
  
  1. поскольку он сохранил тип VARCHAR, но изменилась кодовая страница на ту, которая не поддерживает символы иврита (с 1255 на 1252), эти символы сменились на стандартный символ замещения: "?";
  
  
  2. отсутствует чувствительность к регистру, поскольку "bob" теперь соответствует "BOB".
  
  


• [Col2]
  
  
  
  1. поскольку тип изменился на NVARCHAR, который поддерживает все символы, потери данных не случилось;
  
  
  2. и хотя это не показано в рассмотренном примере, возможна незначительная разница в правилах сортировки и сравнения между оригинальной и новой коллациями, даже если обе коллации имеют одну и ту же чувствительность к регистру. Расхождение либо в версии (номер, или без номера, в имени коллации: 90 или 100 и т.п.), либо в локали (языки: French или Latin1_General или Hebrew и т.д.) также могу внести некоторые различия. Не следует считать, что отсутствие потери данных и аналогичная чувствительность к регистру означает одинаковое поведение во всех случаях!
  
  

Теперь, если вы должны вернуть столбец к предыдущим спецификациям, вам следует изменить его на:

1. NOT NULL, если не было вставлены NULL-значения;


2. использование Hebrew_100_CS_AS_SC, которая вернет чувствительность к регистру.

Однако вы не можете:

1. вернуть ограничение NOT NULL, если NULL были уже вставлены;


2. вернуть потерянные символы. Это верно: все, что было конвертировано в знак вопроса "?", таким и останется без наличия какой-либо информации о том, какими были оригинальные символы. Единственный способ вернуть исходные символы - это восстановление из бэкапа и обновление (UPDATE) текущей таблицы из резервной копии (после восстановления коллации в исходное состояние!).

Принимая во внимание сделанные замечания, помните, что следующие пункты потенциально могут привести к потере данных при изменении столбца VARCHAR:

1. Смена кодовых страницне всегда преобразует символы, не имеющие точного соответствия в новой кодовой странице, в "?". Иногда преобразуются в достаточно близкие символы, что известно под названием сопоставлением "наилучшего соответствия" ("best fit"). Это иногда лучше, чем преобразование в "?", но здесь по-прежнему нет указания на то, чем был оригинальный символ.


2. вы не потеряете никаких данных (т.е. преобразования символов в "?" или сопоставления "наилучшего соответствия"), когда:
   1) символы находятся в стандартном диапазоне ASCII 0- 127 (0х00 - 0х79);
   2) переключаетесь на NVARCHAR;
   3) оставляете VARCHAR, но меняете коллацию на ту, которая использует ту же кодовую страницу, даже в другой локали (например, имеется 38 локалей / групп коллации, которые используют кодовую страницу 1252: SQL_Latin1_General, Latin1_General, French, Finnish_Swedish, German_PhoneBook, и т.д.);


3. обратитесь к "Коллация (бонусный параграф)" ниже относительно специфики UTF-8.

Влияние:

1. Потенциальная потеря данных (как существующих, так и новых).


2. Потенциальное изменение поведения (если произошли изменения одного или более чувствительных параметров - регистр, акцент, Кана тип, ширина и/или селектор варианта - или в опциях _SC , _BIN , _BIN2 или _UTF8).


3. Как вторичная проблема, связанная с пунктом 2, потенциальная ошибка при пересоздании уникального индекса/ограничения (который был удален для того, чтобы изменить столбец), если чувствительность - регистра, акцента, Кана типа, ширины и/или селектора варианта - изменились с "чувствительности" на "нечувствительность", и имеются данные в столбце, которые перестали быть уникальными в "нечувствительной" коллации (например: "а" и "А" уникальны в чувствительной к регистру или бинарной коллации, но неуникальны в нечувствительной коллации).

Коллация (бонусный параграф: UTF-8)

Итак, как ведут себя коллации UTF-8 (новинка SQL Server 2019) в нашей истории? UTF-8 - это кодировка Unicode, которая означает, что поддерживаются все символы. Это также 8-битная кодировка, что означает работу с типом данных VARCHAR. И в этом кроются потенциальные проблемы. VARCHAR подразумевает 1 байт на символ (например, VARCHAR(13) позволяет сохранить до 13 байтов). Однако при UTF-8 только символы со значениями 0-127 (стандартный набор ASCII) используют 1 байт на символ. Следующие примерно 1900 символов используют 2 байта на символ (аналогично UTF-16/NVARCHAR), и остальные 63К символов - 3 байта на символ.

Нам потребуется изменить коллацию базы данных на коллацию UTF-8 с тем, чтобы она оказалась результатом применения ALTER TABLE, когда никакия коллация не специфицирована. Затем мы создадим другую таблицу, на этот раз использующую коллацию Thai, которая использует кодовую таблицу 874. Как и раньше, мы будем использовать те же два столбца только с другими именами, чтобы быть в состоянии увидеть разницу между VARCHAR и NVARCHAR.

ALTER DATABASE [AlterTest] COLLATE French_100_CI_AS_SC_UTF8;

-- DROP TABLE dbo.AlterColumnVarcharThai;

CREATE TABLE dbo.[AlterColumnVarcharThai]

(

  [Col1] VARCHAR(10) COLLATE Thai_100_CS_AS_SC NOT NULL,

  [Col2] VARCHAR(10) COLLATE Thai_100_CS_AS_SC NOT NULL

);

SELECT [name], system_type_id, max_length, [is_nullable], [collation_name]

FROM   sys.columns

WHERE  [object_id] = OBJECT_ID(N'dbo.AlterColumnVarcharThai');

/*

name   system_type_id   max_length   is_nullable   collation_name

Col1   167              10           0             Thai_100_CS_AS_SC

Col2   167              10           0             Thai_100_CS_AS_SC

*/

INSERT INTO dbo.AlterColumnVarcharThai ([Col1], [Col2])

  VALUES (0xA4C7D2C1CAA7BA, 0xA4C7D2C1CAA7BA);

  -- "peace" на Thai (Khwām s̄ngb)

  -- (согласно переводчику Google; я не знаю Thai)

SELECT * FROM dbo.AlterColumnVarcharThai;

/*

Col1      Col2

ความสงบ    ความสงบ

*/

Как видно из кода примера, мы вставили 7 байтов в оба столбца, и они были корректно записаны и вернули 7 символов Thai.

Давайте теперь увеличим максимальный размер на 1 байт для обоих столбцов, и изменим один столбец на NVARCHAR.

ALTER TABLE dbo.[AlterColumnVarcharThai]

  ALTER COLUMN [Col1] VARCHAR(11) NOT NULL;

ALTER TABLE dbo.[AlterColumnVarcharThai]

  ALTER COLUMN [Col2] NVARCHAR(11) NOT NULL;

-- команды выполнились успешно

-- (предупреждений нет)

SELECT [name], system_type_id, max_length, [is_nullable], [collation_name]

FROM   sys.columns

WHERE  [object_id] = OBJECT_ID(N'dbo.AlterColumnVarcharThai');

/*

name   system_type_id   max_length   is_nullable   collation_name

Col1   167              11           0             French_100_CI_AS_SC_UTF8

Col2   231              22           0             French_100_CI_AS_SC_UTF8

*/

Как можно увидеть в коде примера, эта коллация изменилась на коллацию по умолчанию для базы данных (как и ожидалось).

Но на этот раз мы пофиксили проблему NOT NULL, указав ограничение в операторе ALTER TABLE.

Теперь давайте посмотрим, что хранится в столбцах:

SELECT * FROM dbo.AlterColumnVarcharThai;

/* (упс!)

Col1     Col2

ควา      ความสงบ

*/

Однако, мы потеряли 4 из 7 тайских символа в столбце VARCHAR. Но почему?

Если мы посмотрим на то, как те же самые 7 символов Thai представляются в каждой из 3-х используемых здесь кодировок, ситуация прояснится:

DECLARE @Thai NVARCHAR(10) = 0x040E270E320E210E2A0E070E1A0E;

SELECT @Thai; -- ความสงบ

SELECT DATALENGTH(CONVERT(VARBINARY(50), CONVERT(VARCHAR(50), @Thai

                          COLLATE Thai_100_CS_AS_SC))) AS [CodePage874],

       DATALENGTH(CONVERT(VARBINARY(50), @Thai)) AS [UTF-16],

       DATALENGTH(CONVERT(VARBINARY(50), CONVERT(VARCHAR(50), @Thai

                          COLLATE Thai_100_CS_AS_SC_UTF8))) AS [UTF-8]

/*

CodePage874    UTF-16    UTF-8

7              14        21

*/

UTF-16 (которую использует NVARCHAR) требует 14 байтов, и имеется место для этого, поскольку NVARCHAR(11) дает нам до 22 байтов. Но UTF-8 требует 21 байта в силу того, что каждый символ занимает 3 байта, в то время как VARCHAR(11) дает только до 11 байтов. Байты, которые не помещаются в отведенное пространство тихо усекаются. Упс.

Дополнительные замечания о потенциальной потере данных, специфичных для коллаций UTF-8:

1. если исходной коллацией является коллация UTF-8 (впервые появившаяся в SQL Server 2019), а новая коллация не является коллацией UTF-8 (и столбец остается в VARCHAR), то это изменяет кодовую страницу, что может привести к потере данных, даже если локаль/язык остаются без изменения. Это, помимо прочего, приводит к переходу от Unicode (все символы) к не Unicode (не все символы).


2. если VARCHAR не меняется, но происходит переключение на коллацию UTF-8, тогда, хотя все символы поддерживаются, все же возможна потеря данных из-за усечения, если размер нового столбца не имеет достаточно места для символов, которые требуют 2 или 3 байта при кодировке UTF-8.


3. вы могли бы попасть в такую ситуацию непреднамеренно, преобразовав столбец VARCHAR в UTF-8. Поэтому будьте осторожны! (Хотя, вероятно, этого не случится при использовании UTF-8 для всего, кроме текста ASCII).

Что-нибудь еще?

Эти изменения специфичны для ALTER TABLE, и не случаются, когда изменяются другие аспекты столбца, например, изменением имени с помощью sp_rename.

Заключение

При изменении типа данных столбца очень важно учитывать текущие свойства столбца is_nullable и collation_name. Значения, не принимаемые по умолчанию и которые не должны меняться, необходимо установить явно (в их текущие значения) в операторе ALTER TABLE.