Важность Правильного Выбора ANSI_PADDING
Эта статья является авторским переводом моей статьи SET ANSI_PADDING Setting and Its Importance.
СОДЕРЖАНИЕ:
Описание проблемы
Не так давно мне пришлось столкнуться со следующей проблемой у клиента. Наша программа, написанная на VFP, выдавала такую ошибку: "Violation of PRIMARY KEY constraint 'rep_crit_operator_report'. Cannot insert duplicate key in object 'dbo.rep_crit' The duplicate key value is (ADMIN, REPORT_PERIOD_SALES).", что в переводе означает, что была произведена попытка добавить значение, которое уже присутствовало в таблице rep_crit.
Изучение проблемы
Я начала с проверки кода формы, в которой появлялась эта ошибка. Сразу обнаружилось, что код достаточно небрежный, в частности, там было TABLEUPDATE() без проверки возвращаемого значения.
Я проверила работу этой формы на своей базе данных, и не получила никакой ошибки, все отработало правильно.
После этого я созвoнилась с клиентом и, используя TeamViewer, пронаблюдала эту ошибку в действии. Затем я запустила SQL Server Profiler и обнаружила, что вместо UPDATE, который должен был выполняться в этом месте программы, выполнялся INSERT и, конечно, вываливался с ошибкой. В этот момент я подумала, что, наверно, придется менять код, а это достаточно сложный процес.
Надо сказать, что у меня было подозрение, почему мы столкнулись с ошибкой. Клиент недавно установил новую версию нашей програмы. В новой версии я изменила код, чтобы правильно сохранять VarChar колонки. До этого они сохранялись с пробелами на конце до полной длины. В VFP это контролируется установкой cursorsetprop("MapVarchar", .t., 0). До моего изменения мы использовали установку по умолчанию и, таким образом, сохраняли данные как character.
В VFP коде, который я проверила перед звонком клиенту, мы добавляли пробелы сами. Колонка report была определена как VARCHAR(20), но мы использовали PADR(report,20) в коде. То есть, код сам по себе бессмысленный - если нам надо сохранять значение с пробелами на конце, то почему бы не опpеделить эту колонку как CHAR(20), и тогда никакой проблемы не будет? Поскольку этот код существовал задолго до моего прихода в компанию, нужно было оставить все как есть.
В этот момент я решила проверить длину данных в этой колонке и запустила такой код:
SELECT *, DATALENGTH(Report) as Report_Length FROM dbo.rep_crit
К моему удивлению, я увидела много значений меньше 20. Я проверила этот код на своей локальной базе данных и получила ожидаемый ответ - все значения были 20.
Я решила попробовать исправить проблему таким кодом:
UPDATE dbo.rep_crit SET report = LEFT(RTRIM(report) + SPACE(20),20)
Я опять сначала протестировала его на своей базе данных, где все работало. После этого я запустилa код на клиенте, после этого еще раз проверила длину и получила результат, как и в первый раз - длина была меньше 20 символов.
Решение
Конечно, многие читатели этой статьи уже сообразили, в чем было дело. К моему стыду, я по-прежнему была в неведении. Я написала емайл своим колегам по работе, а также задала вопрос на форуме Weird problem with the client. Колега сразу же вспомнил, что у него была эта же проблема с другим клиентом, и отослал меня к описанию в FootPrints (эту программу мы используем как Bug Tracking Software). Latheesh NK также правильно определил проблему как неверную установку SET ANSI_PADDING.
Таким образом, было установлено, что некоторые таблицы использовали неправильную установку ANSI_PADDING OFF для колонок, что вызывало описанную проблему при правильной установке сессии, поскольку установки для колонок имеют больший приоритет, чем установки сессии.
Для того, чтобы исправить эту проблему, нужно было запустить ALTER TABLE команду, чтобы изменить длину колонки до исходной длины, но при правильной установке SET ANSI_PADDING ON. Это и было решением проблемы для данной таблицы.
Мы можем проверить эти установки для колонки, если нажмем правую клавишу мыши и посмотрим свойства колонки:
Эта установка находится внизу с��ойств колонки.
После того, как мы поняли в чем проблема, нужно проверить всю базу данных и, по возможности, исправить неверную установку.
Код для исправления неверной установки ANSI_PADDING во всей базе данных
Я написала следующий код для исправления во всей базе данных:
;WITH cte
AS (
SELECT c.is_nullable
,c.object_id AS table_id
,OBJECT_NAME(c.object_id) AS TableName
,c.max_length
,c.NAME column_name
,CASE c.is_ansi_padded
WHEN 1
THEN 'On'
ELSE 'Off'
END AS [ANSI_PADDING]
,T.NAME AS ColType
FROM sys.columns c
INNER JOIN sys.types T ON c.system_type_id = T.system_type_id
WHERE T.NAME IN ('varbinary', 'varchar')
)
SELECT 'ALTER TABLE dbo.' + quotename(cte.TableName) + ' ALTER COLUMN ' + QUOTENAME(cte.column_name) + ' ' + cte.ColType + '(' + CASE
WHEN cte.max_length = - 1
THEN 'max'
ELSE CAST(cte.max_length AS VARCHAR(30))
END + ')' + CASE
WHEN cte.is_nullable = 1
THEN ' NULL '
ELSE ' NOT NULL'
END
FROM cte
WHERE cte.ANSI_PADDING = 'Off'
Этот код нужно запустить в SSMS, предварительно выбрав Query/Results To Text из меню. После этого сгенерированный код надо скопировать в новое окно и запустить - таким образом можно исправить все таблицы базы.
Установки базы данных по умолчанию
Я обсудила эту проблему еще в одной теме SET ANSI_PADDING setting в форуме. Это обсуждение добавляет новую интересную информацию.
Логично ожидать, что когда мы создаем новую базу данных, все установки по умолчанию правильные для ANSI_PADDING или ANSI_NULL. Однако, как оказывается, это не так даже для SQL Server 2012. Если мы оставим все установки по умолчанию, многие будут неправильными. Смотрите сами:
Тaким образом, если мы хотим иметь правильные установки, лучше поменять их на этапе создания базы данных. Впрочем, если это не сделано, беда не велика, так как все равно установки сессии перекрывают установки базы данных, а по умолчанию в 99% случаев эти установки правильные для сессии (хотя, конечно, не помешает перепроверить).
В комментариях к английскому оригиналу этой статьи указывается еще одно интересное обсуждение похожей проблемы, связанной с усечением varbinary колонки.
Английский оригинал этой статьи участвовал в соревновании TechNet Guru contributions за июнь и выиграл серебряную медаль.