Работа уникальными значениями

← →
den_777 (2004-05-24 09:46) [0]

Пока мне приходилось работать только с INFORMIX и IB,FB. Поэтому приведу пример на этих базах.
Надо чтобы была возможность автоматической и ручной вставки значения в поле которое является PRIMARY KEY, т.е. для таблицы с полями ID и CNAME проходили оба SQL insert into tbl1(id,cname) values(0,"myname") и insert into tbl1(cname) values("myname")
Соответственно в INFORMIX я просто задаю этому полю тип SERIAL и все, больше ничего делать не надо. В первом случае INFORMIX просто проверяет не противоречит ли вставляемое мной значение уникальности и если нет то вставляет его во втором случае сам генерит следующее значение.
В IB,FB это решается с помощью генератора и триггера IF NEW.ID IS NULL THEN NEW.ID=GEN_ID(MY_GEN,1).
Вопрос как таких же действий добиться от MSSQL. И вопрос в догонку, если в других базах какой-либо функционал был заложен в триггер перед вставкой, то как этот же функционал реализовать в MSSQL.

← →
ega23 © (2004-05-24 09:50) [1]

И вопрос в догонку, если в других базах какой-либо функционал был заложен в триггер перед вставкой, то как этот же функционал реализовать в MSSQL.

Точно так же, как и в других СУБД.
CREATE TRIGGER trigger_name
ON table
[WITH ENCRYPTION]
{
{FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
sql_statement [...n]
}
|
{FOR { [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]
AS
{ IF UPDATE (column)
[{AND | OR} UPDATE (column)]
[...n]
| IF (COLUMNS_UPDATED() {bitwise_operator} updated_bitmask)
{ comparison_operator} column_bitmask [...n]
}
sql_statement [ ...n]
}
}

SQL insert into tbl1(id,cname) values(0,"myname") и insert into tbl1(cname) values("myname")

Боюсь, что здесь всё так просто не получится. Это через процедуру делать придётся, ИМХО по другому - никак.

← →
den_777 (2004-05-24 10:04) [2]

> Точно так же, как и в других СУБД.

А разве в MSSQL есть триггер перед вставкой. Мне кажется, что там только триггеры после вставки, обновления и удаления. А перед вставкой и после вставки - это не одно и тоже.

А по поводу ID, может есть другие варианты. Насчет процедуры - это слишком сильное орграничение себе делать.Все таки insert into намного проще, да и писать на каждую таблицу хранимую процедуру только для того чтобы провести обычную вставку это как-то не хорошо.

← →
ega23 © (2004-05-24 10:13) [3]

Мне кажется, что там только триггеры после вставки, обновления и удаления.

Неправильно тебе кажется. Триггер - последний рубеж перед операцией.

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

Только так и делается. Работать с таблицами напрямую - вредно. Только черех ХП. Более того, даже Select"ы через ХП.

← →
den_777 (2004-05-24 10:30) [4]

> Неправильно тебе кажется. Триггер - последний рубеж перед
> операцией

Все может быть, только уточни как реализовать в MSSQL аналог

IF NEW.ID IS NULL THEN NEW.ID=54677
Причем поле ID NOT NULL.

содержимое триггера смысловой нагрузки не несет, только пример.

> Только так и делается. Работать с таблицами напрямую - вредно.
> Только черех ХП. Более того, даже Select"ы через ХП.

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

← →
ega23 © (2004-05-24 10:39) [5]

IF NEW.ID IS NULL THEN NEW.ID=54677
Причем поле ID NOT NULL.

Не слышал я в MS SQL про такую конструкцию. Реализовать - можно, повторюсь, что с помощью ХП:

Print "S_PersEdit- модификация таблицы персонала"
go
if exists (select * from sysobjects where id = object_id(N"[S_PersEdit]") and OBJECTPROPERTY(id, N"IsProcedure") = 1)
drop procedure [S_PersEdit]
GO

CREATE PROCEDURE S_PersEdit
--режим работы процедуры
@mode int=-1,--1-add,2-edit,3-del,30 - проверка на удаление 4-New PinCod
--11 -Generatior ADD, 12 --generator -CHK, 13 --generator -DEL
--Сведения об персоне
@persId int=-1 output, -- output для Генератора
@PersNam1 varchar(32)="",
@PersNam2 varchar(32)="",
@PersNam3 varchar(32)="",

As

declare @result int,
@cod int

if @mode=1
begin
if @PersID=-1 Select @PersID=Max(PersID)+1 from Persons
Insert into Persons (PersID, PersNam1, Persnam2, ....)
Values (@PersID, @PersNam1, @Persnam2, ... )

....

-- Далее можно сразу же протоколирование произвести..

.....
Return(@Result)
end

← →
Johnmen © (2004-05-24 10:42) [6]

>den_777

А просто объявить поле Identity ?
:)

← →
ega23 © (2004-05-24 10:48) [7]

А просто объявить поле Identity ?

Он хочет иметь возможность сам в него значения вставлять. Вопрос стоит в том, как часто он сам значения будет вставлять. Если операция редкая, то делать столбец как Identity и потом Set Identity_Insert ON/OFF ставить. Если частая, то IDENTITY очень неудобно будет.

Хотя задачка сама по себе странновата...

← →
paul_k © (2004-05-24 10:54) [8]

1 -й вариант почитать про Identyty
2-й вариант - в написать свою процедуру - генерацию уникального ID, вызывать её в тригере
Exec ap_get_id @new_id out update inserted set id = @new_id
если вставка не по 1-й записи то делать это в курсоре по inserted

3-й вариант - построить самому функцию вычисляющую новый ID и в свойствах поля указать её, как значение по умолчанию.
4-й вариант - использовать для ID тип uniqueidentifier (GUID) и функцию NewId()
Если подумать и поковырятся в хелпе можно найти ещё N способов организовать уникальный ID.

← →
den_777 (2004-05-24 11:11) [9]

> ega23 © (24.05.04 10:39) [5]

спасибо, но мы отсекаем возможность вставки в таблицу без ХП, так как в случае insert into tbl1(cname) values("myname")
SERVER ругнется, что ID должно быть обязательно задано.

Ну а насчет Identity в

> ega23 © (24.05.04 10:48) [7]

все написано.

Делать Set Identity_Insert ON/OFF при многопользовательской работе я думаю небезопасно.Одна транзакция будет действительно вставлять необходимые данные в ID, а вот что произойдет с другими транзакциями которые ожидали автоматической подстановки ID остается только догадываться.

> Хотя задачка сама по себе странновата...

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

Вопрос скорее всего адресован к тем, кто достаточно долго поработал с IB,FB и потом переходил на MSSQL. Какие стандартные замены привычной работе с генераторами и триггерами BEFORE INSERT нашел для себя этот человек в MSSQL.

← →
den_777 (2004-05-24 11:18) [10]

> 1 -й вариант почитать про Identyty

надеюсь все что можно про Identyty я уже прочитал

> 2-й вариант - в написать свою процедуру - генерацию уникального
> ID, вызывать её в тригере
> Exec ap_get_id @new_id out
> update inserted set id = @new_id
> если вставка не по 1-й записи то делать это в курсоре по
> inserted

А как быть с тем, что в случае insert into tbl1(cname) values("myname") SERVER ругнется, что ID должно быть обязательно задано.

> 3-й вариант - построить самому функцию вычисляющую новый
> ID и в свойствах поля указать её, как значение по умолчанию.
>

Спасибо за вариант, стоит разобраться с этой возможностью

> 4-й вариант - использовать для ID тип uniqueidentifier (GUID)
> и функцию NewId()
> Если подумать и поковырятся в хелпе можно найти ещё N способов
> организовать уникальный ID.

Не подходит, нужен ID с типом INTEGER

← →
ega23 © (2004-05-24 11:21) [11]

MS SQL-то какой?

← →
den_777 (2004-05-24 11:31) [12]

> ega23 © (24.05.04 11:21) [11]
> MS SQL-то какой?

2000

← →
ega23 © (2004-05-24 11:35) [13]

Тогда 3-й вариант - построить самому функцию вычисляющую новый ID и в свойствах поля указать её, как значение по умолчанию. ИМХО, разумнее всего будет. Я-то с точки зрения 7.0 советовал...

← →
den_777 (2004-05-24 11:43) [14]

> ega23 © (24.05.04 11:35) [13]

Спасибо, я тоже думаю что это самый подходящий вариант.

Но вот вопрос про триггеры BEFORE INSERT остается открытым. Ведь в любой базе DEFAULT и CHECK constraints реализовываются с помощью системных триггеров перед вставкой и обновлением. И разработчику баз данных тоже предоставлено право пользоваться возможностью изменения данных в триггере до вставки и обновлений. Если в MSSQL такой возможности нет, то просто любопытно, а как же у них реализованы DEFAULT и CHECK constraints.

← →
paul_k © (2004-05-24 12:11) [15]

>
> А как быть с тем, что в случае insert into tbl1(cname) values("myname")
> SERVER ругнется, что ID должно быть обязательно задано.
>

напиши то, что я советовал, в триггере и проверь... работы на 5 минут
создать таблицу с 2-мя полями и в триггере update inserted написать :)

← →
den_777 (2004-05-24 12:25) [16]

> paul_k © (24.05.04 12:11) [15]

update inserted MSSQL в триггере писать не разрешает
"error286: The logical tables INSERTED and DELETED cannot be updated"

← →
paul_k © (2004-05-24 12:53) [17]

Виноват...
Если верить вумной книжке
"Если какой-либо оператор пытается выполнить операцию, которая нарушает какое -либо ограничение по таблице, или являеттся причиной какой-то другой ошибки, то связанный с ним триггер не будет активизирован"
то есть проверять непустые поля надо раньше.
следовательно - либо значение по умолчанию либо через ХП организовывать добавление.

← →
den_777 (2004-05-24 13:04) [18]

> следовательно - либо значение по умолчанию либо через ХП
> организовывать добавление.

Это вроде как уже понятно. А вот есть ли мнение по поводу

> den_777 (24.05.04 11:43) [14]

← →
Johnmen © (2004-05-24 13:05) [19]

>den_777

Всё-таки неясно, зачем самому вставлять ID ? Ведь это фактически суррогатный ключ, и в величине его значения никакого смысла нет.
Да и поведение его (Identity), естественно, "учитывает" многопользовательскую работу...

← →
paul_k © (2004-05-24 13:09) [20]

> Это вроде как уже понятно. А вот есть ли мнение по поводу
>
> > den_777 (24.05.04 11:43) [14]

перепечатывать лень
"Microsoft Sql Server 2000 Справочник администратора" стр. 519
или BOL

← →
den_777 (2004-05-24 13:45) [21]

> Всё-таки неясно, зачем самому вставлять ID ? Ведь это фактически
> суррогатный ключ, и в величине его значения никакого смысла
> нет.

Раз уж программисты написавшие INFORMIX (по моему мнению СУБД INFORMIX на порядок лучше чем MSSQL и по надежности, и по скорости работы, и по возможности настроек) сделали тип SERIAL с функционалом, который я уже описывал, значит это кому-нибудь нужно.

> "Microsoft Sql Server 2000 Справочник администратора" стр.
> 519

Жаль, но данная книга у меня сейчас дома, так что прочесть сейчас не смогу. Подожду до завтра. Но вроде как совсем недавно я ее всю прочитал и ответы на заданные мной здесь вопросы не нашел. Интересно, что-же там на 519стр.

← →
Sandman25+1 (2004-05-24 13:59) [22]

[21] den_777 (24.05.04 13:45)

Не надо так про MS SQL. Многие возможности MS SQL появились только в последних версиях Informix, точнее, в 9.4

← →
Johnmen © (2004-05-24 14:14) [23]

>Раз уж программисты написавшие INFORMIX (...

Так мы про что здесь говорим ? Про INFORMIX или MSSQL ?
Ты уж определись...:)
И имей в виду, что приёмы, хорошо и корректно "ложащиеся" под одним SQL сервером, таковыми под другим могут и не быть !

← →
den_777 (2004-05-24 14:18) [24]

> Sandman25+1 (24.05.04 13:59) [22]

Прости, если задел тебя за живое. Для меня всякие фичи(навороты,упрощения для разработчика и т.п.) не являются объектами при сравнении двух СУБД, а по перечисленным мной параметрам INFORMIX и на версии 7.2 даст большую фору MSSQL. Может я не участвовал в разработках баз, которые реализовывали бы все преимущества MSSQL по сравнению с другими СУБД, но вот наоборот случалось работать. Так что это мое незыблемое мнение в котором я и хотел бы может быть разубежден, но только конкретными аргументами в пользу MSSQL. Аргументов в пользу INFORMIX для людей проработавших на MSSQL я к сожалению привести не смогу, так как все слишком специфично, чтобы объяснить это на пальцах, а аналогий в MSSQL нет. Если же ты проработал на INFORMIX (под SOLARIS разумеется) хотя бы лет 5 и поподдерживал бы базы объемом в сотни ГГб и с таблицами в сотни милионов записей, то мне странно твое мнение.

А если ты сравнил по ТТХ(описанию возможностей), не имея опыта работы, то твое мнение не имеет никакой цены.(надеюсь это не так).

← →
den_777 (2004-05-24 14:22) [25]

> Так мы про что здесь говорим ? Про INFORMIX или MSSQL ?
> Ты уж определись...:)

Я и определился. В самом первом посте привел пример про INFORMIX,IB,FB и попросил найти аналогию в MSSQL. Вот и все.

← →
paul_k © (2004-05-24 14:24) [26]

> Интересно, что-же там на 519стр.

Глава 22 "Создание и использование тригерров

> Раз уж программисты написавшие INFORMIX (по моему мнению
> СУБД INFORMIX на порядок лучше чем MSSQL и по надежности,
> и по скорости работы, и по возможности настроек) сделали
> тип SERIAL с функционалом, который я уже описывал, значит
> это кому-нибудь нужно.

1. Если мне не изменяет память уши MsSql растут из Sybase - dполне достойной корпоративной СУБД, а что наворотили на это под убравлением Билли Гейца - это второй вопрос.
2. А функционал - есть он, только реализован по другому

CREATE FUNCTION dbo.t_test_New_Id() RETURNS numeric AS BEGIN DECLARE @id numeric SELECT @id = (select isnull( max(id),1)+1 from t_test) -- или как ты там желаешь ID заполнить по умолчанию RETURN @id END GO CREATE TABLE dbo.t_test ( id numeric(18, 0) NOT NULL, field_1 varchar(50) NULL ) ON [PRIMARY] GO ALTER TABLE dbo.t_test ADD CONSTRAINT DF_t_test_id DEFAULT dbo.t_test_New_Id() FOR id GO

← →
Sandman25+1 (2004-05-24 14:27) [27]

[24] den_777 (24.05.04 14:18)

Я проработал с Informix под SCO Unixware 7.1 около 6 лет :) но тем не менее я совершенно спокойно признаю, что до введения генераторов (sequence) и select * from table(select ...) у MS SQL было несколько важных преимуществ.

← →
den_777 (2004-05-24 14:34) [28]

> paul_k © (24.05.04 14:24) [26]

INFORMIX привел только ради примера существования такой возможности в ранее известных мне СУБД. Тема закрыта.

А вот по поводу функции по DEFAULT есть одно неприятное ограничение: имя таблицы указано прямо в теле функции соответственно придется писать подобные функции для каждой таблицы. Может быть есть способ узнать в теле функции кто ее вызвал.

>...попросил найти аналогию в MSSQL.

Ещё раз. ПРЯМАЯ аналогия может быть либо неосуществима, либо реализовываться идеологически некорректно.
Поэтому чётко скажи, что надо-то ? Не приводя примеров/аналогий из др.серверов...

> den_777 (24.05.04 14:34) [28]

F1 - CREATE FUNCTION
создать отдельную таблицу под все ID

← →
den_777 (2004-05-24 14:43) [31]

> что до введения генераторов (sequence) и select * from table(select
> ...) у

В том то и дело что для меня это не является преимуществами, я говорил про надежность,скорость,тонкие настройки работы на многопроцессорных машинах,настройки работы при большом количестве пользователей,исчерпывающий мониторинг и статистику(пусть и в текстовом режиме), настройки для OLTP и DSS и т.п. И еще я никогда не работал c INFOMRIXом на машинах с процессорами INTEL, возможно там у INFORMIXа нет тех преимуществ, которые у него есть на SPARC+SOLARIS. А INFORMIX под WINDOWS - это вообще отдельная тема.

← →
den_777 (2004-05-24 14:47) [32]

> создать отдельную таблицу под все ID

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

← →
Sandman25+1 (2004-05-24 15:04) [33]

[31] den_777 (24.05.04 14:43)

Ну тогда о чем говорить? Для Вас, как для администратора, более важным является не совсем то (точнее, совсем не то :-) , что является важным для меня, как для разработчика ХП и прочих атрибутов серверной части ПО.

← →
den_777 (2004-05-24 15:20) [34]

Для Вас, как для администратора
Да не был я никогда чисто администратором, всегда совмещал обязанности программиста и разработчика БД. Ну иногда по совместительству поддерживал работу этих БД. А как же иначе разрабатывать программы не учитывая всех возможностей по администрированию и настройке СУБД.

Ну хватит уже меряться :))
А то я могу сказать, что Firebird спокойно делает все информиксы и ораклы по соотношению цена/производительность :))
По сути, обычно при такой нужде именно таблицу под все ID и делают в MSSQL, http://rsdn.ru/article/db/midas_migration.xml

> den_777 (24.05.04 09:46)
INSTEAD OF + F1
P.S. В MSSQL легко делается, то, что вы описали, читайте хелп.

← →
Sandman25+1 (2004-05-24 15:30) [37]

[34] den_777 (24.05.04 15:20)

И все-таки я настаиваю, что мы смотрим с разных точек зрения. Если СУБД суперскоростная, но не поддерживает некоторых возможностей, то такая, как бы ее назвать... MySQL без вложенных SELECT или Paradox без триггеров... меня совсем не устраивает.
Спасибо за интересный разговор.

← →
den_777 (2004-05-24 16:15) [38]

> Romkin © (24.05.04 15:28) [35]

Наверное имелось ввиду вот это.
create procedure CLIENT_ID
@TableName varchar(30),
@ID int output
as
update Seeds
set ID = ID + 1,
@ID = ID + LowOffset
where TableName = @TableName
go

предположим ситуацию
в ID записано значение 5
Два пользователя вставляют данные в одну и туже таблицу, но у обоих пользователей большие транзакции с вставкой данных в несколько таблиц.Первая транзакция получает значение 6, вторая 7.
Первая транзакция после неудачной вставки в какие то другие таблицы откатывается. Что буде с ID. Мне кажатся что 5.В случае блокировки записи в seeds до окочания первой транзакции, вторая может вылетить по timeout или просто здорово затормозить работу.
Может я что-то не так понимаю, объясните пожалуйста.
В том то и дело что в FireBird generator стоит вне транзакции и никак от нее не зависит, а с помощью таблиц такого не получится.

> А то я могу сказать, что Firebird спокойно делает все информиксы
> и ораклы по соотношению цена/производительность :))

И я тоге же мнения

> INSTEAD OF + F1

Что же я там должен увидеть.Это же подмена вставки, а не полноценный триггер BEFORE INSERT

> Sandman25+1 (24.05.04 15:30) [37]
> Спасибо за интересный разговор.

И ВАМ спасибо. Я ни в коем случае не идеализирую INFORMIX(или какую-либо другую СУБД), просто мне кажется что в MSSQL нет некоторых основопологающих вещей.

← →
Sandman25+1 (2004-05-24 16:35) [39]

[38] den_777 (24.05.04 16:15)
просто мне кажется что в MSSQL нет некоторых основопологающих вещей.

Возможно. Человек ко всему привыкает и почти всегда есть несколько способов добиться желаемого. Если бы Вы наоборот переходили с MS SQL или Orcale на Informix, Вы бы точно так же могли упрекнуть последний в отсутствии некоторых механизмов.

"Что же я там должен увидеть.Это же подмена вставки, а не полноценный триггер BEFORE INSERT" - правильно, это более мощная конструкция, нежели "BEFORE INSERT", иначе говоря "BEFORE INSERT" - частный случай "INSTEAD OF INSERT". Думаю, вопрос решён.

P.S. "в MSSQL нет некоторых основопологающих вещей" - это ваше частное и в общем случе безграмотное мнение. Прежде чем так высказываться, дайте определение "основопологающих вещей" не забыв добавить IMHO.

Работа уникальными значениями Найти похожие ветки