T-SQL, Databases, Development

Если у вас в таблице есть автоинкрементное поле, к примеру, типа int , и следующее автоинкрементное значение превышает максимальное значение для типа этого поля, не отчаивайтесь — это не беда

Такое происходит с большими таблицами, откуда часто удаляют и вставляют новые значения. Таким образом в нумерации имеются провалы из-за удаления данных, а максимальное значение для типа данных счетчика быстро достигается. Ошибка будет примерно такой такой:  Server: Msg 8115, Level 16, State 1, Line 2 Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.

Варианты решения:

1. Расширьте тип данных. Если у вас был int, поставьте bigint. Помните, если у вас значение этого поля использовалось в процедурах и прочем коде, вам придется их исправить на использование нового типа!
2. Второй вариант ленивый. Если у Вас данные в таблице постоянно обновляются и есть большой кусок места до минимального значения счетчика( selectmin(auto_inc_field) fromyour_table) в этом поле, если смотреть от нуля, и если вы уверены, что если вы начнете нумерацию с нуля, то пока вы заполните этот промежуток, старые данные уже будут удалены, соответственно блок нумерации счетчика освободится, то этот вариант для Вас!   Вы можете элементарно начать нумерацию заново или с какого-то конкретного стартового номера следующей командой:

   DBCC CHECKIDENT ("your_table", RESEED, 1);
   GO

   Эта команда заменяет значение следующего автоинкрементного значение на 1. То есть, это есть замена текущего автоикремента.

    

3.  Вариант для тех у кого есть время и в случае если строк в таблице меньше чем вариантов значений счетчика. Вариант один из наиболее надёжных. Создаете копию таблицы (желательно без индексов) и порциями копируете данные из старой таблицы в новую. Таким образом вы получаете все ваши данные с индексацией с нуля плотно упакованные относительно значений счетчика.
   Конечно, если у вас есть связанные таблицы по этому полю, Вам конечно ещё придется обновить старые значения счетчиков на новые.

Если есть вопросы или другие способы решения проблемы, то милости прошу, в коменты!

Продолжаю тему восстановления  БД, начатую тут. Закончилось всё довольно таки успешно.

Итак…. Не помог мне

ALTER DATABASE ‘DatabaseName’SET SINGLE_USER
DBCC CHECKDB(‘DatabaseName’, REPAIR_REBUILD)
ALTER DATABASE ‘DatabaseName’ SET MULTI_USER

не помог и

DBCC CHECKDB(‘DatabaseName’, REPAIR_ALLOW_DATA_LOSS)

Так как удалось найти битую таблицу(в ней как раз наибольшее кол-во строк), принялся её реанимировать нестандартными способами. Попробовал сделать выборку данных- выкинуло в ошибку, но успело вернуть несколько миллионов записей. Попробовал сделать выборку, с ключом desc — таким образом вытащил ещё кусок данных с конца таблицы. Кстати, select стал выбирать больше записей после того как убил все индексы кроме Primary Key. Пошаманив ещё несколько часов и не получив желаемого результата, создал клон битой таблицы без данных и с помощью:

insert into tablename(fields) select top КОЛ-ВО_СТРОК1 *  from tableoriginal order by fieldnamePK asc

insert into tablename(fields) select top КОЛ-ВО_СТРОК2 *  from tableoriginal order by fieldnamePK desc

заполнил новую таблицу данными. Старую удалил, а эту переименовал так как нужно.

Должен сказать, что таким образом удалось восстановить почти все записи.

Для остальных БД выполнил DBCC CHECKDB() , откуда узнал какие из таблиц битые. Им помогла DBCC DBReidex(‘Tablename’) для битых таблиц.

На этом всё и закончилось.

Желаю вам надёжных хардов и меньше подобных проблем!

Случайно нашёл ссылочку http://developer.mimer.se/validator/parser200x/index.tml#parser. Тут можно проверить синтаксис SQL online. Очень простенькая штука с ограниченым кол-вом стандартов. Может быть полезной.

Ранее я писал об использовании Stored Procedures, теперь пришёл черёд триггеров. В отличие от хранимых процедур, триггера я принял сразу. Очень удобная и полезная штука. Если вы хотите отследить вставку, изменение или удаление записи, то триггеры именно для этих задач. Как вы знаете, триггеры можут быть 3 типов(DML): на delete, insert и update; то есть триггер сработает на указанное действие с данными в таблице БД delete, insert или update , в зависимости от его типа. Выполняться он может до(before) или после(after) того действия(delete, insert или update) которое его вызвало. Триггеры before полезны, например,  для того, чтобы вы могли предупредить какое либо недопустимое, или несогласованное с логикой бизнесс процесса действие с данными. Он выполняется до вызвавшего его действия, а значит с помощью него можно это действие отменить, проверив данные на валидность с помощью sql statements вашего тригера. Триггеры after, выполняютя после действия, которое его инициировало и полезны, например для логирования изменений данных.

Читать далее…

В одной из своих записей я уже рассказывал о способах ускорения insert. В этой записи я расскажу о простейших способах оптимизиции и ускорении удаления строк из таблиц. В принципе, способы удаления схожи со способами вставки во многом.

Итак:

1. Вместо построчного удаления делайте массовое(сразу все строки, которые нужно удалить), т.к. при массовом удалении индекс претерпит изменения лишь раз, вместо постоянного передёргивания при построчном удалении. Например:

вместо

delete from tablename where field1=2

delete from tablename where field1=3

delete from tablename where field1=4,

лучше записать так -

delete from tablename where field1=2 or field1=3 or field1=4

2. Если при удалении вы уверены, что никто не будет делать запрос к таблице, то делайте «LOCK TABLE».

3. Если есть возможность перефразировать запрос на удаление указывая в разделе WHERE условие по полю первичного ключа, то целесообразно будет сделать именно так. Этот запрос будет не только быстрее, но и не будет блокировать таблицу.

Функции Cast() и Convert() дают возможность преобразовывать выражения одного типа в другой.

Для MSSQL Server синтаксис:

CAST ( expression AS data_type [ (length ) ])

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Для MySQL:

CAST(expr AS type)

CAST() функция принимает данные одного типа и возвращает результат другого типа, как и CONVERT().

CONVERT(expr,type), CONVERT(expr USING transcoding_name)

Подробнее MS SQL http://msdn.microsoft.com/ru-ru/library/ms187928.aspx

и MySQL    http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

Продолжаю тему оптимизации запросов по индексам начатую тут. Кстати синтаксис create index можно посмотреть тут для MS SQL http://msdn.microsoft.com/ru-ru/library/ms188783.aspx, и тут для MySQL http://dev.mysql.com/doc/refman/5.0/en/create-index.html.

Продолжаю советы:

Читать далее…

Этой записью начинаю цикл статей по оптимизации БД с помощью индексов. Если вы хотите найти точный ответ на вопрос «какие индексы я должен создать чтобы производительность была оптимальной?», то не ищите его(ответ) ни тут, ни где-либо ещё. Нет чёткого правила где и какие индексы нужно создавать. Есть лишь советы рекомендательного характера, а вариантов построения индексов для одной базы данных очень много. Где и какие индексы строить зависит от назначения таблиц, частоты запросов на выборку и частоты изменения этой таблицы, от частоты участия в выборке оперделённой группы полей и т.д. и т.п.

Советы:

Читать далее…

Одной из часто встречаемых проблем при работе с данными, является скорость вставки большого количества строк в таблицы баз данных. Можно использовать построчную вставку в цикле, но например для MySQL достаточно быстро выполнится insert с множественным указанием значений values, например :

insert into tablename(field1,field2) values(val11,val12) ,(val21,val22),(val31,val32),…

Читать далее…

@@SPID — системная переменная типа smallint, в которой хранится SPID текущего процесса юзера.
Скрипт

SELECT @@SPID

вернёт вам SPID текущего юзера.
Посмотреть всю таблицу процессов можно с помощью процедур: sp_who или sp_who2.