Уникальные значения в MSSQL 2000

← →
Mitrofan (2004-05-13 09:22) [0]

Скажите, имеется ли в MSSQL 2000 механизм автоинкремента или генераторы ? Как в данной СУБД для поля организовать уникальность значений ? Спасибо.

← →
Nikolay M. © (2004-05-13 09:24) [1]

IDENTITY

← →
Mitrofan (2004-05-13 09:28) [2]

А как оно работает ? Значение присваивается при вставке записи ?
Могу ли я это значение получить на клиентской машине еще до вставки записи ?

← →
31512 © (2004-05-13 09:33) [3]

Из справки по Transact-SQL

@@IDENTITY
Returns the last-inserted identity value.

Syntax
@@IDENTITY

Return Types
numeric

Remarks
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

Examples
This example inserts a row into a table with an identity column and uses @@IDENTITY to display the identity value used in the new row.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ("Accountant",12,125)
SELECT @@IDENTITY AS "Identity"

← →
Jee © (2004-05-13 09:40) [4]

> 31512 © (13.05.04 09:33) [3]

И какая мораль следует из цитирования хэлпа?
@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT - вещи хорошие. Однако если ты сам работаешь с таблицей и если добавляются строки с нескольких клиентов, ситуации немножко разные, не так ли?

← →
Курдль © (2004-05-13 09:49) [5]

У меня есть на такие случаи общее пожелание.
1. Потратьте немного времени на ознакомление (пусть даже не углубленное изучение) CASE - технологий.
2. Установите себе какое-нибудь средство CASE хотя самый минимальный набор для работы с БД. Это может быть от тяжеловесного монстра Rational Rose до компактного PD.
3. Потренируйтесь немного в проектировании БД - от концептуальной модели - к физической и до генерации скрипта создания БД.
4. Внимательно посмотрите скрипт и вы обязательно удивитесь, как грамотно все вышло!
Т.е. если у Вас в концептуальной модели стояло поле "ID serial", то простой заменой целевой СУБД в скрипте можно получить:
- для IB - CREATE GENERATOR
- для Oracle - CREATE SEQUENCE
и т.д.
Таблицы автоматически будут снабжены всем необходимым - ключами, индексами, констрэйнтами, а ошибки будут отловлены еще до генерации самой БД.

← →
bushmen © (2004-05-13 13:24) [6]

Я бы для начала поссоветовал поглубже изучить основы MS SQL Server"a, а также основы клиент-серверной технологии

← →
31512 © (2004-05-14 09:51) [7]

> Jee ©

> И какая мораль следует из цитирования хэлпа?

Мораль сей басни такова:
Посмотрим хелп сначала,
И не найдя ответа,
Возми и умну книжку почитай
Или сходи до интернета.
И лишь потом вопросы
В форум посылай ...
31512 ©
P.S.

В [1] про

> если добавляются строки с нескольких клиентов

ничего не сказано.

← →
Jee © (2004-05-14 10:18) [8]

> 31512 © (14.05.04 09:51) [7]

Мораль конечно замечательная. Таким образом можно ответить на 99% вопросов в форуме, при этом можно даже не знать ответа.
В [1] ответили достаточно для того, чтобы человек нашел сам в хэлпе.

> > если добавляются строки с нескольких клиентов
>
> ничего не сказано.

Ничего не сказано и про то, что с MSSQL 2000 в данном случае будет работать исключительно один клиент. Имхо, если используется такого класса СУБД, нужно рассчитывать на то, что это будет не так. Человек начнет использовать значение IDENTITY в могопользовательском режиме и отгребет кучу ошибок.

← →
Jee © (2004-05-14 10:22) [9]

> Mitrofan (13.05.04 09:28) [2]
> А как оно работает ? Значение присваивается при вставке
> записи ?
> Могу ли я это значение получить на клиентской машине еще
> до вставки записи ?

Можно конечно и с IDENTITY сделать корректно, но придется изворачиваться, чтобы не возникло конфликтов при вставках записей с разных клиентов.
Если тебе действительно необходимо на клиентской машине перед вставкой записи получать уникальное значение ключа, можно использовать тип uniqueidentifier у поля. А занчение получать функцией NEWID(). Но за это придется поплатиться объемом данных.

← →
Nikolay M. © (2004-05-14 10:54) [10]

> начнет использовать значение IDENTITY в могопользовательском
> режиме и отгребет кучу ошибок.

SCOPE_IDENTITY() в большинстве случаев вполне достаточно.

← →
31512 © (2004-05-14 10:54) [11]

> Jee © (14.05.04 10:18) [8]

Таки Игорь прав ...

http://ln.com.ua/~openxs/articles/smart-questions-ru.html

← →
Danilka © (2004-05-14 11:10) [12]

[9] Jee © (14.05.04 10:22)
Только этот ID будет мало похож на автоинкремент, мягко говоря. :)
Получить автоинкрементированое значение перед вставкой записи может понадобиться, например, для автонумерации документов, а теперь представь что будет, если в номере договора напишется это самое. :))

← →
Skyle © (2004-05-14 11:19) [13]

> Но за это придется поплатиться объемом данных.

И ещё, если я не ошибаюсь, без приведения типов по полю типа uniqueidentifier нельзя сортировать.

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

← →
Jee © (2004-05-14 11:32) [14]

> 31512 © (14.05.04 10:54) [11]

Спасибо за ссылку, весьма интересная статья.

> Таки Игорь прав ...

Это кто?

> Skyle © (14.05.04 11:19) [13]

uniqueidentifier удобен, например, в случае, когда есть несколько баз, находящихся в разных филиалах. И есть центральная база в офисе. Данные из филиалов собираются в центральную базу. При добавлении строк не возникнет конфликта с уникальностью первичного ключа.
А по поводу использования ПК для сортировки, номера документа и т.п. - он не для этого предназначен. Корректнее использовать специальные поля.

← →
Danilka © (2004-05-14 11:37) [15]

[14] Jee © (14.05.04 11:32)
А ПК, как правило, и не требуется знать до вставки записи.
Конечно, пусть автор ветки скажет для чего это ему нужно, но если мой мелафон в порядке, то тогда uniqueidentifier ему не подойдет. :))

← →
Skyle © (2004-05-14 11:40) [16]

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

С этим я спорить не буду, но в данном случае никакой нагрузки кроме обеспечения глобальной уникальности при синхронизации он не несёт.

> При добавлении строк не возникнет конфликта с уникальностью
> первичного ключа.

А на кой ему быть первичным ключом, если он используется только между базами? Пусть первичным ключом будет ID INT IDENTITY(1, 1), а UID - где-нибудь там же, но сбоку. Выскажу предположение, что индекс по ID будет веселее строиться, чем по UID.

> А по поводу использования ПК для сортировки, номера документа
> и т.п. - он не для этого предназначен. Корректнее использовать
> специальные поля.

А я как сказал? :-)

← →
-=VaaL=- © (2004-05-14 11:46) [17]

ндааа... а теперь еще раз прочитаем цитирование хелпа 31512 © (13.05.04 09:33) [3]

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session.

← →
Skyle © (2004-05-14 12:07) [18]

> -=VaaL=- © (14.05.04 11:46) [17]
> ндааа... а теперь еще раз прочитаем цитирование хелпа 31512
> © (13.05.04 09:33) [3]
>
> @@IDENTITY and SCOPE_IDENTITY will return the last identity
> value generated in any table in the current session.

И что? Судя по

> Mitrofan (13.05.04 09:28) [2]
> А как оно работает ? Значение присваивается при вставке
> записи ?
> Могу ли я это значение получить на клиентской машине еще
> до вставки записи ?

IDENTITY тут никак не катит и о нём вообще речи нет.
Автор к сожалению никак не конкретизировал задачу.
Или я чего-то не понял?

← →
paul_k © (2004-05-14 13:41) [19]

выкрутится можно следующим путем
CREATE TABLE t_all_ids ( id numeric(18, 0) IDENTITY (1, 1) NOT NULL, user_login numeric(18, 0) IDENTITY (1, 1) NOT NULL, )
go create procedure GetId New_Id numeric output as begin tran insert into t_all_ids (user_login) select suser_sname() set New_Id = @@identity Commit tran goи когда нужно получить новый ID - исполнять подобную процедуру.
Вопрос в том, а всегда ли нужен такой танец с бубном или можно обходится без него

← →
Курдль © (2004-05-14 13:53) [20]

А такая фишка в MSSQL-е не проходит:
select GET_IDENTITY("имя таблицы") as NEW_ID from DUMMY?

← →
paul_k © (2004-05-14 13:59) [21]

Если верить хелпу GET_IDENTITY в МсСкул не известна

← →
Курдль © (2004-05-14 14:00) [22]

А есть на чем проверить? Просто по аналогии с Sybase. Вдруг MS SQL это от нее тоже уеаследовал?

← →
Skyle © (2004-05-14 14:08) [23]

> А есть на чем проверить? Просто по аналогии с Sybase. Вдруг
> MS SQL это от нее тоже уеаследовал?

Нет, такого нет.
А какую задачу решаем? Если хочется просто найти следующее_значение_на_момент_запроса, то это можно сделать и выборкой.
Но в случае многопользовательской работы это нифига не спасёт.

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

← →
Курдль © (2004-05-14 14:18) [24]

Чаще всего такие вопросы возникают при необходимости получить значение нового идентификатора заранее. Например для того, чтобы сформировать пакет записей для связанных по этому идентификатору таблиц. Не знаю, как в MSSQL, но для 3х СУБД это так:

Sybase: select GET_IDENTITY("имя таблицы") as NEW_ID from DUMMY
Oracle: select имя_последовательности.NEXTVAL NEW_ID from DUAL
InterBase: select GEN_ID("имя таблицы", 1) from rdb$database

← →
paul_k © (2004-05-14 14:20) [25]

кстати о Sybase
Sybase ASE 12
"GET_IDENTITY" is not a recognized built-in function name.

← →
Курдль © (2004-05-14 14:23) [26]

Да? На ASA 8.1 уже есть!
Adaptive Server Anywhere SQL Reference Manual 3. SQL Functions Alphabetical list of functions GET_IDENTITY function [Miscellaneous] -------------------------------------------------------------------------------- Function Allocates values to an autoincrement column. This is an alternative to using autoincrement to generate numbers. Syntax GET_IDENTITY ( [ owner.] table-name [, num_to_alloc ],... )

← →
Skyle © (2004-05-14 14:25) [27]

Я не знаю задачи, для которой нужно заранее знать значение первичного ключа.

> Например для того, чтобы сформировать пакет записей для
> связанных по этому идентификатору таблиц.

Это можно сделать иначе и совсем несложно...

← →
paul_k © (2004-05-14 14:29) [28]

2Курдль
Да догадался потом я что Вы на ASA работаете..
Только я Вот вижу Sybase и сразу почемуто в любимый ASE (Adaptive Server Enterprise) проверять лезу. А там - обшибка синтаксиса..

← →
Курдль © (2004-05-14 14:30) [29]

> Это можно сделать иначе и совсем несложно...

Можно конкретнее?
Если у меня есть форма с 4-мя ДатаСэтами, ну например, "субъекты" и я ввожу нового физ.лица:
1. Заполняю ФИО (таблица "Субъекты")
2. Ввожу адрес (таблица "Адреса")
3. Ввожу телефон (таблица "Телефоны")
4. Ввожу счет в банке (таблица "Счета")
Причем все записи во всех таблицах новые, а таблицы 2-4 связаны с 1 по первичному ключу?

← →
Курдль © (2004-05-14 14:33) [30]

> Только я Вот вижу Sybase и сразу почемуто в любимый ASE
> (Adaptive Server Enterprise) проверять лезу. А там - обшибка
> синтаксиса..

ASA тоже очень быстро прогрессировала, посмотрите и Вы апгрэйды.
В 8.0 еще этого не было, а в 8.1 не было UUID., появилось только в 8.2 (и все за год-два).
Но я не вижу смысла в ASE, не лучше ли сразу на Оракл садиться?

← →
bushmen © (2004-05-14 14:34) [31]

Если Вы работаете на MS SQL 2000, то проще всего загнать все данные в хранимую процедуру, а там через SCOPE_IDENTITY() получить номер identity-поля.

← →
Курдль © (2004-05-14 14:39) [32]

> Если Вы работаете на MS SQL 2000, то проще всего загнать
> все данные в хранимую процедуру, а там через SCOPE_IDENTITY()
> получить номер identity-поля.

Возможно я поэтому на нем и не работаю. Для задачи [29] я просто беру значение первичного (для 1) /внешнего (для 2-4) ключа способом [24] (поправить ошибку в конце - не имя_таблицы, а имя_генератора) и прописываю, куда надо.

← →
Skyle © (2004-05-14 14:41) [33]

> Можно конкретнее?
> Если у меня есть форма с 4-мя ДатаСэтами, ну например, "субъекты"
> и я ввожу нового физ.лица

А если вот так?
Отправляешь запрос такого вида:
BEGIN TRAN
DECLARE @Id INT

INSERT FirstTable(....)
VALUES(.....)

SELECT @Id = @@IDENTITY

--А дальше вставка всего остального с использованием @Id.
-- Это замечательно работает в случае динамического формирования запроса.

← →
paul_k © (2004-05-14 14:41) [34]

Курдль © (14.05.04 14:33) [30]
не будем спорить на что, ни за что садится. Все от задачи пляшет.

Курдль © (14.05.04 14:30) [29]
а зачем заранее? достаточно добавить все в правильной последовательности и взять @@identity после каждого инсерта.
тогда и танцы с бубном отплясывать вокруг таблиц не надо.
Тем более когда нужно всего лишь расставить связи и дальше пользовать эти поля для сортировки не предвидится, то мое мнение - uniqueidentifier с NewId() в качестве defaultValue вполне подходит

← →
bushmen © (2004-05-14 14:42) [35]

>Возможно я поэтому на нем и не работаю

Мало ли что кто делает вообше - вопрос был задан конкретно про MS SQL 2000. А о Ваших личных предпочтениях я осведомился в Вашем вчерашнем посте по поводу транзакций.

← →
Курдль © (2004-05-14 14:45) [36]

> SELECT @Id = @@IDENTITY

А это к какой таблице(полю/генератору/последовательности)относится?

← →
Skyle © (2004-05-14 14:47) [37]

After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
К последней, которую трогали.

← →
bushmen © (2004-05-14 14:49) [38]

>А это к какой таблице(полю/генератору/последовательности)относится?

Если Вам это интересно, то на www.sql.ru обсуждался этот вопрос.

← →
Курдль © (2004-05-14 14:49) [39]

Понял, не дурак! :)))
Но это значит, что надо насильственно открывать транзакцию + все вытекающие...

← →
Курдль © (2004-05-14 14:54) [40]

> Если Вам это интересно, то на www.sql.ru обсуждался этот
> вопрос.

Да я так - чисто умозрительно! А мало ли где этот вопрос обсуждался?! Я Вас уверяю, что даже на этом форуме все вопросы где-нибудь да обсуждались!
Так что мы имеем? Иначе, чем специально обученной процедурой, идентити из MSSQL не вытянуть?

> + все вытекающие

Давайте не будем возвращаться ко вчерашнему обсуждению - никаких проюлем с этим у меня никогда не было, да и судя по форумам - у других тоже. Если Вы хотите что-то конкретно обсудить, то надо говорить на реальных фактах и примерах, а если нет у Вас опыта работы с MS SQL, то сначала преобретите, а потом спорьте.

> Но это значит, что надо насильственно открывать транзакцию
> + все вытекающие...

Выполнение батча в транзакции - имхо самое правильное поведение. И явно здесь открывать ничего не обязательно.

> если нет у Вас опыта работы с MS SQL, то сначала преобретите,
> а потом спорьте.

Вот я его и обретаю!

> Выполнение батча в транзакции - имхо самое правильное поведение.
> И явно здесь открывать ничего не обязательно.

BEGIN TRAN [33] - это явно.
Но ладно. Возможность есть - и хорошо. Плохо, что ноа затрудняет "облегченное программирование" средствами, например, компонентов типа UpdateObject, имеющими в дизайн-тайме возможности автогенерации скриптов...

> BEGIN TRAN [33] - это явно.

...указывает на то, что

> Выполнение батча в транзакции - имхо самое правильное поведение

> > Выполнение батча в транзакции - имхо самое правильное
> поведение

Но это вовсе не значит, что открытие транзакции внутри ХП (скрипта) - хороший стиль.

> Но это вовсе не значит, что открытие транзакции внутри ХП
> (скрипта) - хороший стиль

Мне кажется, что стилем это можно назвать с большой натяжкой. Честно. Господина Дейта почитываем? Он там много про это рассуждает.

Я надеюсь, не стоит объяснять необходимость транзакции как таковой, особенно в примере
> Курдль © (14.05.04 14:30) [29]
?

> Вот я его и обретаю!

Странным образом Вы его преобретаете. Сначала говорите, что в MS SQL все организовано не правильно, а только потом изучаете :))

>Но это вовсе не значит, что открытие транзакции внутри ХП
>(скрипта) - хороший стиль

По-моему, опять пошла пустая болтовня. Только вот интересно каким образом в хранимой процедуре Вы обойдетесь без открытия транзакции? Тут нет Вашего любимого ApplyUpdate

Может мы об одном и том же.

В примере [29] я показал самое типичное окно из всех проектов.
Редко бывает такое счастье, что форма ввода работает только с одной таблицей. Но программирование такой формы проходит "на автомате" - методичным заполнением (а чаще - автозаполнением) запросов. Транзакции, естественно, отрабатываются но неявно - по ApplyUpdates.

> По-моему, опять пошла пустая болтовня. Только вот интересно
> каким образом в хранимой процедуре Вы обойдетесь без открытия
> транзакции? Тут нет Вашего любимого ApplyUpdate

А зачем ее открывать в ХП? В крайнем случае можно обойтись savepoint-ом.

> bushmen © (14.05.04 15:12) [48]

> Тут нет Вашего любимого ApplyUpdate

> Курдль © (14.05.04 15:18) [49]

> по ApplyUpdates.

:-)))

Я же говорил, что совсем не обязательно это делать ручками.
Плюс к тому, есть альтернативные архитектуры, где можно, лучше всего и самое удобное делать так, а не через ApplyUpdates.

Ладно, думаю пока заканчивать. Всё равно автор вопроса так и не появился...

← →
Zz_ (2004-05-14 15:24) [52]

>>"облегченное программирование"

Спорить с такими - совершенно бесполезно

Все эти компоненты разом дохнут, если, например,
есть жесткое требование - никаких прав ни на один объект
БД, окромя SP

> Все эти компоненты разом дохнут, если, например,
> есть жесткое требование - никаких прав ни на один объект
> БД, окромя SP

Ну и что из того? Если это к вопросу об открытии транзакции внутри SP, то все равно не оправдывается! Для компонента типа TStopedProc, или обычного TQuery, содержащего код вызова SP, транзакция открывается, закрывается и откатывается автоматически по методу Execute (ExecSQL, ExecProc).

Судя по всему нехватает генератора для полного счастья автора.
Ну дык можно самому чтонить замутить самостоятельно... на примере 2-ух таблиц

Вот для этой таблицы нам нужно создать генератор для первичного ключа
create table tab (t_id bigint primary key, t_value varchar(100))

А вот это таблица генератора:
create table gen
(g_id bigint identity(1,1) primary key,
g_guid uniqueidentifier)

припустим охота получить для себя 2 первичных ключа для tab не всталяя туда самих записей. Делаем так:

declare @g1 uniqueidentifier, @g2 uniqueidentifier
declare @p1 bigint, @p2 bigint

set @g1 = newid()
set @g2 = newid()
insert into gen values(@g1)
insert into gen values(@g2)
set @p1 = (select g_id from gen where g_guid = @g1)
set @p2 = (select g_id from gen where g_guid = @g2)
-- в @p1 и @p2 наши первичные ключи
delete from gen where g_guid = @g1
delete from gen where g_guid = @g2

-- вставка в таблицу проводится банально:

insert into tab values (@p1, "value1")
insert into tab values (@p2, "value1")

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

Весь этот код оформить в процедуру для получения первичного ключа и пусть все берут это значение только из нее вот и все сказка.

>Для компонента типа TStopedProc, или обычного TQuery, >содержащего код вызова SP, транзакция открывается, закрывается >и откатывается автоматически по методу Execute (ExecSQL, >ExecProc).

Вы сами это проверяли? По-моему, нет!

> Вы сами это проверяли? По-моему, нет!

Да ясен пень, что 1000 раз проверял! Даже может 100 000 000! Столько раз, сколько раз данные успешно попадали в БД!

← →
Zz_ (2004-05-14 16:24) [57]

Как вы думаете скока будет count(sp_xxx), для которых

sp_xxx cannot be used inside a user-defined transaction

> Как вы думаете скока будет count(sp_xxx), для которых
>
> sp_xxx cannot be used inside a user-defined transaction

Понятия не имею! А зачем ЭТО?

Уникальные значения в MSSQL 2000 Найти похожие ветки