Отправляет email-рассылки с помощью сервиса Sendsay

Вопросы и ответы по MS SQL Server

Подписаться Бесплатная новостная рассылка Подписчиков 17.253 RSS
←   Апрель 2003   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30


За последние 60 дней 1 выпусков (1 раз в 2 месяца)


Сайт рассылки: http://www.itnews.ru
Открыта: 04-03-2003

Автор
Сергей

Статистика

17.253 подписчиков
-3 за неделю
←   Все выпуски   →

Вопросы и ответы по MS SQL Server


Информационный Канал Subscribe.Ru

Вопросы и ответы по MS SQL Server

Выпуск No. 7 от 2003-04-03
Вопрос : Как создавать и использовать автоинкрементные поля?
Ответ:
Автоинкрементные поля - это поля (колонки), значение которых увеличивается автоматически при добавлении записей в таблицу.
В Microsoft SQL Server такие поля носят название IDENTITY. Само название говорит, что это не просто автоинкрементные поля, а поля, созданные для однозначной идентификации записей в таблицах.
В таблице можно создать только одно поле IDENTITY.

Указание того, что поле является автоинкрементным, осуществляется при создании таблицы или при добавлении поля в таблицу с помощью следующего свойства: 
IDENTITY [ (начальное_число , шаг ) ]

Если не указать начальное значение и шаг, то по умолчанию будут установлены значения (1,1).
Автоинкрементными могут быть только целочисленные поля (int, bigint, smallint, tinyint, decimal или numeric).
Такой тип полей хорошо подходит для создания первичных ключей таблиц, т.к. все индексы таблицы строятся на основе кластерного индекса (или RowID, если такового нет), а величина целочисленных полей достаточно мала. Создадим таблицу для дальнейшего использования: 
CREATE TABLE Master7 (

   ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,

   Name varchar(20) NOT NULL,

   Age smallint NULL,

)

GO

INSERT Master7 VALUES('Строка 1',15)

GO

INSERT Master7 (Name) VALUES('Строка 2')

GO

Обратите внимание на варианты вставки записей. Для автоинкрементного поля невозможно подставить значение в части VALUES. Поэтому наименование поля и его значение просто пропускаются.

При работе с таблицами, имеющими автоинкрементные поля можно вместо названия автоинкрементного поля использовать служебное слово IDENTITYCOL. 
SELECT IDENTITYCOL, Name, Age

FROM Master7

WHERE IDENTITYCOL<9

GO

Результат: 
ID          Name                 Age

----------- -------------------- ------

1           Строка 1             15

2           Строка 2             NULL



(2 row(s) affected)

Кроме свойства IDENTITY существует функция IDENTITY, которая используется только в операторе SELECT с пунктом INTO для вставки автоинкрементного поля в новую таблицу. Формат функции: 
IDENTITY ( тип_данных, [начальное_число , шаг] ) AS название_колонки

Создадим с помощью этой функции вторую таблицу: 
SELECT IDENTITY(int,1,1) AS ID, a.Name, a.ID*10+b.ID as Code

INTO Detail7

FROM Master7 a, Master7 b

GO

Содержание созданной таблицы: 
ID          Name                 Code

----------- -------------------- -----------

1           Строка 1             11

2           Строка 2             21

3           Строка 1             12

4           Строка 2             22



(4 row(s) affected)

Для проверки последнего значения колонки identity последней вставленной записи в MS SQL Server 2000 существует три функции:
1. Функция (точнее глобальная переменная) возвращает последнее вставленное identity значение в текущем соединении. 
@@IDENTITY

2. Функция возвращает последнее вставленное identity значение в текущем соединении. Но в отличие от предыдущей функции ограничена областью видимости. Функция есть в MS SQL Server начиная с версии 2000. 
SCOPE_IDENTITY()

3. Функция возвращает последнее вставленное identity значение в указанную таблицу и не ограничена областью видимости и сеансом соединения. 
IDENT_CURRENT('имя_таблицы')

Последняя функция используется намного реже, чем две предыдущие. Разницу первых двух функций выясним на следующем примере: 
CREATE TRIGGER TriggerMaster7 ON Master7

FOR INSERT

AS

BEGIN

  SET NOCOUNT ON

  INSERT Detail7 VALUES('Новая строка',0)

END

GO

INSERT Master7 VALUES ('Строка 3',30)

GO

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY], @@IDENTITY AS [@@IDENTITY]

GO

Результат: 
SCOPE_IDENTITY                           @@IDENTITY

---------------------------------------- ----------------------------------------

3                                        5



(1 row(s) affected)

Функция SCOPE_IDENTITY() возвращает более предсказуемый результат. Неизвестно сколько триггеров сработает при вставке записи и, написав дополнительный триггер, можно долго искать ошибку из-за специфичного поведения функции @@IDENTITY.

Если необходимо на время отключить нумерацию автоинкрементного поля и вставить в него строки с необходимым значением, можно воспользоваться следующей командой: 
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

Пример использования команды: 
SET IDENTITY_INSERT Master7 ON

GO

INSERT Master7 (ID, Name, Age) VALUES (10,'Строка 4',40)

GO

SET IDENTITY_INSERT Master7 OFF

GO

INSERT Master7 VALUES ('Строка 5',50)

GO

SELECT * FROM Master7

GO

Результат: 
ID          Name                 Age

----------- -------------------- ------

1           Строка 1             15

2           Строка 2             NULL

3           Строка 3             30

10          Строка 4             30

11          Строка 5             50



(5 row(s) affected)

Это одна из причин того, что использование свойства IDENTITY не обеспечивает уникальности значений поля. Для обеспечения уникальности необходимо строить уникальный или кластерный индекс.

Для определения начального значения и шага приращения автоинкрементного поля можно воспользоваться следующими функциями: 
IDENT_SEED ('таблица_или_представление')

IDENT_INCR ('таблица_или_представление')

Для проверки текущего значения identity и исправления, при необходимости, его текущего значения пользуйтесь командой DBCC CHECKIDENT. Пример использования: 
DELETE Master7

GO

DECLARE @ID int

SELECT @ID=ISNULL(MAX(ID),0) FROM Master7

DBCC CHECKIDENT (Master7, RESEED, @ID)

GO

INSERT Master7 VALUES('Строка',15)

GO

SELECT IDENTITYCOL FROM Master7

GO

Результат: 
(5 row(s) affected)



Checking identity information: current identity value '11', current column value '0'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.



(1 row(s) affected)



ID

-----------

1



(1 row(s) affected)

Показанный выше скрипт удобно использовать для сброса счетчика до минимального значения после очистки таблицы или множественного удаления данных, чтобы избежать больших разрывов в значениях колонки identity.
На этом закончим. Удалить созданные объекты можно с помощью следующего кода: 
DROP TABLE Master7

DROP TABLE Detail7

GO

АНОНС РАССЫЛКИ
Рассылка клуба профессиональных программистов "Весельчак У".

В наших выпусках вы найдете:
1. Подробное описание создания драйверов под Windows.
2. Особенности программирования систем реального времени.
3. Описание протоколов связи.
4. Написание графических программ под OpenGL и DirectX.

Информация не только из стандартных описаний, но и полученная на основании
личного опыта работы в западных и российских фирмах. Клуб - это форум,
поэтому мы не ограничиваем себя в рамках только текущего описания. Все
вопросы, которые не найдут решения в рамках форумных дискуссий, а потребуют
подробного рассмотрения, войдут в наши выпуски.
http://subscribe.ru/catalog/comp.soft.prog.compu
Надеюсь, Вы нашли эту информацию полезной. Вопросы, предложения и пожелания шлите на адрес sql@likor.ru
С уважением, Сергей Кошкин.
Адрес сайта Рассылки - http://sql.boom.ru/

Архив на Subscribe.Ru
Поиск по архиву рассылки
"Вопросы и ответы по MS SQL Server"

Рейтинг@Mail.ru Rambler's Top100
http://subscribe.ru/
E-mail: ask@subscribe.ru 		Отписаться
Убрать рекламу
В избранное