Как сделать индекс неуникальным 1с sql. Попытка вставки неуникального значения в уникальный индекс. Что такое индекс

Вам встретилось сообщение, содержащее строки:
Microsoft OLE DB Provider for SQL Server: CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID
или
Cannot I_nsert duplicate key row in object
или
Попытка вставки неуникального значения в уникальный индекс.

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

1. В SQL Server managment studio физически уничтожаем сбойный индекс (в моем случае это был индекс по таблице итогов регистра бухгалтерии). В 1С распроводим сбойные документы. В режиме тестирования и исправления ставим галки реиндексация таблиц + пересчет итогов. 1С воссоздает индекс уже без ошибки. Проводим ранее сбоившие документы.

2. 1) С помощью Management Studio 2005 сгенерировала create-скрипт на создание индекса, который глючил, и сохранила в файлик.
2) Вручную убила косячный индекс из таблицы _AccumRgTn19455
3) Запустила запрос вида
Код SQL S_elect count(*), поля_индекса
FROM AccumRgTn19455
GROUP BY поля_индекса
HAVING count(*)>1
После того, как индекс был убит, у меня отобразилось 15 дублирующихся записей, хотя до выполнения п.2 запрос ничего не возвращал.
4) Просмотрела все записи и вручную почистила дубликаты. На самом деле, я ещё пользовалась обработкой "Структура отчёта", чтобы понять, с чем вообще имею дело. Оказалось, что в таблице _AccumRgTn19455 хранится регистр накопления "Выпуск продукции (налоговый учёт)". Я ещё поковырялась sql-запросами, выявила 15 неуникальных документов и после окончания всех действ проверила в 1С, что эти документы проводятся нормально, без ошибок. Просто так чистить таблицы наобум, конечно, не стоит: важно понимать, что чистится и чем это может обернуться.
5) Запустила запрос на создание индекса, который был сохранён в файле.
6) Перевела базу в однопользовательский режим и запустила dbcc checkdb - на этот раз ни одной ошибки не выдалось.
7) Перевела базу обратно в однопользовательский режим.
Всё... проблема побеждена. Ну ещё в 1С запустила "Тестирование и исправление", там тоже всё прошло нормально, перестало ругаться на неуникальный индекс.

3. Если неуникальность заключается в датах с нулевыми значениями , то проблема решается созданием базы с параметром смещения равным 2000.

1. Если проблема загрузкой базы данных, то:
1.1. Если Вы делаете загрузку (используйете dt-файл) в базу MS SQL Server, то при создании базы перед загрузкой укажите смещение дат - 2000.
Если уже база создана со смещением 0, то создайте новую с 2000.

1.2. Если есть возможность в файловом варианте работать с базой, то выполните Тестирование и Исправление, а также Конфигурация - Проверка конфигурации - Проверка логической целостности конфигурации + Поиск некорректных ссылок.

1.3. Если нет файлового варианта, попробуйте загрузить из DT в клиент-серверный вариант с DB2 (который менее требователен к уникальности), и затем выполнить Тестирование и Исправление, а также Конфигурация - Проверка конфигурации - Проверка логической целостности конфигурации + Поиск некорректных ссылок.

1.4. Для локализации проблемы можно определить данные объекта, загрузка которого не удалась. Для этого надо включить во время загрузки трассировку в утилите Profiler или включите запись в технологический журнал событий DBMSSQL и EXCP.

2. Если проблема неуникальности проявляется во время работы пользователей:

2.1. Найти с помощью метода пункта 1.4 проблемный запрос.

2.1.2. Иногда ошибка возникает во время исполнения запросов, например:

Данная ошибка возникает из-за того что в модуле регистра накопления "Рабочее время работников организаций" в процедуре "ЗарегистрироватьПерерасчеты" в запросе не стоит служебное слово "РАЗЛИЧНЫЕ".
Код 1C v 8.х Т.е. должно быть:
Запрос = Новый Запрос(
"ВЫБРАТЬ РАЗЛИЧНЫЕ
| Основные.ФизЛицо,
. . . . .
В последних выпущенных релизах ЗУП и УПП ошибка не возникает, т.к. там стоит "РАЗЛИЧНЫЕ".

2.2. После нахождения проблемного индекса из предыдущего пункта, необходимо найти неуникальную запись.
2.2.1. «Рыба» скрипта для определения неуникальных записей с помощью SQL:
Код SQL S_elect COUNT(*) Counter, <перечисление всех полей соответствующего индекса> from <имя таблицы>
GROUP BY <перечисление всех полей соответствующего индекса>
HAVING Counter > 1

2.2.2 Пример. Индекс в ошибке называется "_Document140_VT1385_IntKeyIndNG".
Перечень полей таблицы:
_Document140_IDRRef, _KeyField, _LineNo1386, _Fld1387, _Fld1388, _Fld1389, _Fld1390, _Fld1391RRef, _Fld1392RRef, _Fld1393_TYPE, _Fld1393_RTRef, _Fld1393_RRRef, _Fld1394,_Fld1395, _Fld1396RRef, _Fld1397, _Fld1398, _Fld1399RRef, _Fld22260_TYPE, _Fld22260_RTRef, _Fld22260_RRRef, _Fld22261_TYPE, _Fld22261_RTRef, _Fld22261_RRRef
Перед выполнением приведенной ниже процедуры сделайте резервную копию базы данных.
Выполните в MS SQL Server Query Analizer:
Код SQL S_elect count(*), _Document140_IDRRef, _KeyField
from _Document140_VT1385
group by _Document140_IDRRef, _KeyField
having count(*) > 1
С его помощью узнайте значения колонок _Document140_IDRRef, _KeyField, дублирующихся записей (id, key).

При помощи запроса:
Код SQL S_elect *
from _Document140_VT1385
or _Document140_IDRRef = id2 and _KeyField = key2 or ...
посмотрите на значения других колонок дублирующихся записей.
Если обе записи имеют осмысленные значения и эти значения разные, то исправьте значение _KeyField на уникальное. Для этого определите максимальное занятое значение _KeyField (keymax):
Код SQL S_elect max(_KeyField)
from _Document140_VT1385
where _Document140_IDRRef = id1
Замените значение _KeyField в одной из повторяющихся записей на правильное:
Код SQL update _Document140_VT1385
set _KeyField = keymax + 1
Здесь _LineNo1386 = - дополнительное условие, которое позволяет выбрать одну из двух повторяющихся записей.

Если одна (или обе) из повторяющихся записей имеет очевидно неправильное значение, то ее нужно удалить:
Код SQL delete from _Document140_VT1385
where _Document140_IDRRef = id1 and _LineNo1386 = lineno1
Если повторяющиеся записи имеют одинаковые значения во всех колонках, то из них нужно оставить одну:
Код SQL S_elect distinct *
into #tmp1
from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1

Delete from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1

I_nsert into _Document140_VT1385
S_elect #tmp1

D_rop table #tmp1

Описанную процедуру необходимо выполнить для каждой пары повторяющихся записей.

2.2.3. Второй пример:
Код SQL S_elect COUNT(*) AS Expr2, _IDRRef AS Expr1, _Description
FROM _Reference8_
GROUP BY _IDRRef, _Description
HAVING (COUNT(*) > 1)

2.3.4 Пример определения неуникальных записей с помощью запроса 1С:Предприятие:
Код 1C v 8.х ВЫБРАТЬ Справочник.Ссылка
ИЗ Справочник.Справочник КАК Справочник
СГРУППИРОВАТЬ ПО Справочник.Ссылка
ИМЕЮЩИЕ КОЛИЧЕСТВО(*) > 1

Ошибка возникает, если в базе есть у каких-то объектов, реквизитов, субконто - значение NULL, а у них такого значения быть не может. И появляется такая ошибка только в SQL базах. Т.е. если такую базу выгрузить в файловую, то там уже этой ошибки не будет. Т.к. у файловой базы свои таблицы (всего 4 шт.), а у SQL свои. И SQL база критично реагирует на такие значения в своих таблицах.

Эта проблема не решается никакими тестированиями (ни внешним, ни внутренним) ни в каких вариантах баз (SQL или файловая) и даже Процедурой _1sp_DBReindex в менеджере SQL, которая вроде как должна проводить реструктуризацию таблиц в SQL.

Разберём решение проблемы на примере перехода с Бухгалтерии 3.0 ПРОФ на КОРП. После перехода у счёта 68.01 появляется новое субконто РегистрацияВНалоговомОргане. И тогда, в базах на SQL, все создание в ПРОФ версии документы которые используют этот счёт, не будут перепроводится. Будет выходить выше показанная ошибка. Т.к. это новое субконто у старых документов, в проводках, запишется со значением NULL (хотя должно быть Пустое значение, ну или как-нибудь налоговый орган).

Чтобы устранить эту ошибку, нужно убрать значения NULL там, где их не должно быть. В данном случае в документах, где используется субконто РегистрацияВНалоговомОргане. Сделать это можно, написав обработку, которая заменит NULL на Пустое значение (готовую обработку можно скачать из этой статьи). Делать обработкой, т.к. попытка изменить значение этого субконто в проводках документа вручную, приводит к всё той же ошибке.

Обработку для замены NULL"ов во всех субконтах РегистрацияВНалоговомОргане можно скачать из этой статьи, внизу.

НО так заменить NULL в SQL базе не получится, во время выполнения обработки будет выдана та же самая ошибка. Поэтому необходимо сделать так:

1. Выгрузить уже рабочую, переведённую на КОРП версию, SQL базу в dt’шник (в конфигураторе Администрирование – Выгрузить базу – выберите куда выгрузить базу в виде файла *.dt)

2. Загрузить dt’шник в файловую базу (в ненужной или заранее подготовленной, чистой, файловой базе, в конфигураторе Администрирование – Загрузить базу – выбрать выгруженный ранее dt’шник)

3. Выполнить обработку в файловой базе (там ошибки не будет и все NULL’ы корректно заменяться) (как выполнить обработку описано ниже)

5. Теперь наоборот выгрузить dt’шник из файловой базы и загрузить его в SQL базу. Теперь при проведении обработанных документов ошибки выходить не будет.

Обработка из этой статьи находит все документы, за указанный период, у которых в проводках фигурирует субконтно РегистрацияВНалоговомОргане (которое появляется в КОРП версии), у которого значение NULL. И заменяет это значение на Пустое значение.

В обработке необходимо указать период, за который необходимо обработать документы (можно за весь период в котором ведётся учёт в базе) и нажать «Заполнить табличную часть». После чего можно галками пометить какие документы обработать (можно выбрать все) и нажать кнопку «Выполнить обработку».

Соответственно если у кого-то такая же ошибка, но НЕ после перехода на КОРП, а на пример после обмена, загрузки каких-то данных, выполнения каких-то обработок и т.д. То необходимо выявить, где присвоилось значение NULL в конкретном документе/справочнике и подобным способом убрать этот NULL но уже своей обработкой, но в том порядке, как описано выше. Помните, что NULL может быть, как в проводках документа, в т.ч. не только бухгалтерских, так и где-нибудь на форме документа/справочника, в каком-нибудь реквизите, но в таком случае он наверно даже не откроется.

Также если это ошибка появилась у вас при проведении документа, после того как вы перевели файловую базу Бух КОРП на SQL (а база когда-то изначально была ПРОФ), то значит у тех документов что были созданы в ПРОФ версии, сейчас также в субконто РегистрацияВНалоговомОргане значение NULL и SQL база такое не приемлет. И при загрузке базы в SQL будет выходить такая ошибка. Тут на самом деле в файловой базе значений NULL по факту не будет, но SQL в свои таблицы загрузит именно такие значения. Поэтому тут надо заставить базу SQL создать эти NULL"ы и потом в файловой базе их исправить. Но как это сделать, уже не подскажу.

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

2. Если проблема неуникальности проявляется во время работы пользователей:

2.1. Найти с помощью метода пункта 1.4 проблемный запрос.

2.1.2. Иногда ошибка возникает во время исполнения запросов, например:

Delete from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1

I_nsert into _Document140_VT1385
S_elect #tmp1

D_rop table #tmp1

Описанную процедуру необходимо выполнить для каждой пары повторяющихся записей.

2.2.3. Второй пример:
Код SQL S_elect COUNT(*) AS Expr2, _IDRRef AS Expr1, _Description
FROM _Reference8_
GROUP BY _IDRRef, _Description
HAVING (COUNT(*) > 1)

В этой статье будет описано, что делать, если при работе с 1С:Предприятие 8.1 Вам встретилось сообщение, содержащее строки:

Cannot insert duplicate key row in object

Попытка вставки неуникального значения в уникальный индекс.

Что такое индекс?

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

Хотя индекс и связан с конкретным столбцом (или столбцами) таблицы, все же он является самостоятельным объектом базы данных.

Индексы таблиц в базе данных 1С:Предприятие создаются неявным образом при создании объектов конфигурации, а также при тех или иных настройках объектов конфигурации.

Физическая сущность индексов в MS SQL Server 2005.

Физически данные хранятся на 8Кб страницах . Сразу после создания, пока таблица не имеет индексов, таблица выглядит как куча (heap) данных. Записи не имеют определенного порядка хранения.
Когда вы хотите получить доступ к данным, SQL Server будет производить сканирование таблицы (table scan). SQL Server сканирует всю таблицу, что бы найти искомые записи.
Отсюда становятся понятными базовые функции индексов:
— увеличение скорости доступа к данным,
— поддержка уникальности данных.

Несмотря на достоинства, индексы так же имеют и ряд недостатков. Первый из них – индексы занимают дополнительное место на диске и в оперативной памяти. Каждый раз когда вы создаете индекс, вы сохраняете ключи в порядке убывания или возрастания, которые могут иметь многоуровневую структуру. И чем больше/длиннее ключ, тем больше размер индекса. Второй недостаток – замедляются операции вставки, обновления и удаления записей.
В среде MS SQL Server 2005 реализовано несколько типов индексов:

некластерные индексы;
кластерные (или кластеризованные) индексы;
уникальные индексы;
индексы с включенными столбцами
индексированные представления
полнотекстовый

Уникальный индекс

Уникальность значений в индексируемом столбце гарантируют уникальные индексы . При их наличии сервер не разрешит вставить новое или изменить существующее значение таким образом, чтобы в результате этой операции в столбце появились два одинаковых значения.
Уникальный индекс является своеобразной надстройкой и может быть реализован как для кластерного, так и для некластерного индекса. В одной таблице может существовать один уникальный кластерный и множество уникальных некластерных индексов.
Уникальные индексы следует определять только тогда, когда это действительно необходимо. Для обеспечения целостности данных в столбце можно определить ограничение целостности UNIQUE или PRIMARY KEY, а не прибегать к уникальным индексам. Их использование только для обеспечения целостности данных является неоправданной тратой пространства в базе данных. Кроме того, на их поддержание тратится и процессорное время.

1С:Предприятие 8.1 начиная с версии 8.1 активно использует кластерные уникальные индексы. Это означает, что при конвертации с 8.0 или переходе с 8.1.7 можно получить ошибку неуникального индекса.

Если неуникальность заключается в датах с нулевыми значениями, то проблема решается созданием базы с параметром смещения равным 2000.

Что делать?

1. Если проблема загрузкой базы данных, то:

1.1. Если Вы делаете загрузку (используйете dt-файл) в базу MS SQL Server, то при создании базы перед загрузкой укажите смещение дат — 2000.

Если уже база создана со смещением 0, то создайте новую с 2000.

1.2. Если есть возможность в файловом варианте работать с базой, то выполните Тестирование и Исправление, а также Конфигурация — Проверка конфигурации — Проверка логической целостности конфигурации + Поиск некорректных ссылок.

1.3. Если нет файлового варианта, попробуйте загрузить из DT в клиент-серверный вариант с DB2 (который менее требователен к уникальности), и затем выполнить Тестирование и Исправление, а также Конфигурация — Проверка конфигурации — Проверка логической целостности конфигурации + Поиск некорректных ссылок.

1.4. Для локализации проблемы можно определить данные объекта, загрузка которого не удалась. Для этого надо включить во время загрузки трассировку в утилите Profiler или включите запись втехнологический журнал событий DBMSSQL и EXCP.

1.5. Если доступна узел (планы обменов), то выполнить обмен. Можно также дополнительно перед обменом выполнить пункт 2.3.5

2. Если проблема неуникальности проявляется во время работы пользователей:

2.1. Найти с помощью метода пункта 1.4 проблемный запрос.

2.1.2. Иногда ошибка возникает во время исполнения запросов, например:

Данная ошибка возникает из-за того что в модуле регистра накопления «Рабочее время работников организаций» в процедуре «ЗарегистрироватьПерерасчеты» в запросе не стоит служебное слово «РАЗЛИЧНЫЕ».

Т.е. должно быть:

Запрос = Новый Запрос(
«ВЫБРАТЬ РАЗЛИЧНЫЕ
| Основные.ФизЛицо,

В последних выпущенных релизах ЗУП и УПП ошибка не возникает, т.к. там стоит «РАЗЛИЧНЫЕ».

2.2. После нахождения проблемного индекса из предыдущего пункта, необходимо найти неуникальную запись.

2.2.1. «Рыба» скрипта для определения неуникальных записей с помощью SQL:
SELECT COUNT(*) Counter, <перечисление всех полей соответствующего индекса> from <имя таблицы>
GROUP BY <перечисление всех полей соответствующего индекса>
HAVING Counter > 1

2.2.2 Пример. Индекс в ошибке называется «_Document140_VT1385_IntKeyIndNG».

Перечень полей таблицы:

Document140_IDRRef, _KeyField, _LineNo1386, _Fld1387, _Fld1388, _Fld1389, _Fld1390, _Fld1391RRef, _Fld1392RRef, _Fld1393_TYPE, _Fld1393_RTRef, _Fld1393_RRRef, _Fld1394,

Fld1395, _Fld1396RRef, _Fld1397, _Fld1398, _Fld1399RRef, _Fld22260_TYPE, _Fld22260_RTRef, _Fld22260_RRRef, _Fld22261_TYPE, _Fld22261_RTRef, _Fld22261_RRRef

Перед выполнением приведенной ниже процедуры сделайте резервную копию базы данных.
Выполните в MS SQL Server Query Analizer:

select count(*), _Document140_IDRRef, _KeyField
from _Document140_VT1385
group by _Document140_IDRRef, _KeyField
having count(*) > 1

С его помощью узнайте значения колонок _Document140_IDRRef, _KeyField, дублирующихся записей (id, key).

При помощи запроса:

select *
from _Document140_VT1385
or _Document140_IDRRef = id2 and _KeyField = key2 or …

посмотрите на значения других колонок дублирующихся записей.

Если обе записи имеют осмысленные значения и эти значения разные, то исправьте значение _KeyField на уникальное. Для этого определите максимальное занятое значение _KeyField (keymax):

select max(_KeyField)
from _Document140_VT1385
where _Document140_IDRRef = id1

Замените значение _KeyField в одной из повторяющихся записей на правильное:

update _Document140_VT1385
set _KeyField = keymax + 1

Здесь _LineNo1386 = — дополнительное условие, которое позволяет выбрать одну из двух повторяющихся записей.

Если одна (или обе) из повторяющихся записей имеет очевидно неправильное значение, то ее нужно удалить:

where _Document140_IDRRef = id1 and _LineNo1386 = lineno1

Если повторяющиеся записи имеют одинаковые значения во всех колонках, то из них нужно оставить одну:

select distinct *
into #tmp1
from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1

delete from _Document140_VT1385
where _Document140_IDRRef = id1 and _KeyField = key1

insert into _Document140_VT1385
select #tmp1

drop table #tmp1

Описанную процедуру необходимо выполнить для каждой пары повторяющихся записей.

2.2.3. Второй пример:

SELECT COUNT(*) AS Expr2, _IDRRef AS Expr1, _Description
FROM _Reference8_
GROUP BY _IDRRef, _Description
HAVING (COUNT(*) > 1)

2.3.4 Пример определения неуникальных записей с помощью запроса 1С:Предприятие:

или для бухгалтерии

ВЫБРАТЬ
Подзапрос.Период,
Подзапрос.Регистратор,
<измерения>,
СУММА(Подзапрос.КоличествоЗаписей) КАК КоличествоЗаписей
ИЗ
(ВЫБРАТЬ
Хозрасчетный.Период КАК Период,
Хозрасчетный.Регистратор КАК Регистратор,
<измерения>,
1 КАК КоличествоЗаписей
ИЗ
РегистрБухгалтерии.Хозрасчетный КАК Хозрасчетный) КАК Подзапрос

СГРУППИРОВАТЬ ПО
Подзапрос.Период,
Подзапрос.Регистратор,
<измерения>

ИМЕЮЩИЕ
СУММА(Подзапрос.КоличествоЗаписей) > 1

2.3.5 Сделать индекс субд не уникальным. Заксриптовать индекс с помощью Management Studio.

2.3.6 Частный случай при обмене в РБД. Ошибка приходится на «вспомогательные» таблицы, связанные с расчетом итогов или аналитики. Например:

Ошибка при вызове метода контекста (Записать): Попытка вставки неуникального значения в уникальный индекс:
Microsoft OLE DB Provider for SQL Server: Cannot insert duplicate key row in object ‘dbo._AccntRegED10319’ with unique index ‘_Accnt10319_ByPeriod_TRNRN’.
HRESULT=80040E2F, SQLSrvr: Error state=1, Severity=E, native=2601, line=1

В этом случаи перед загрузкой выключить использование итогов, загрузить сообщение, включить использование итогов и пересчитать.