К вопросу об идентификаторах

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

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

Таким образом, в свете всего вышесказанного, самый типичный сценарий применения автоинкремента выглядит примерно так:

DECLARE @PrimaryKey int

BEGIN TRAN

INSERT INTO MasterTbl (<... some fields ...>) VALUES (<... some values ...>)

SET @PrimaryKey = SCOPE_IDENTITY()

INSERT INTO DetailTbl (ForeignKey, <... some other fields ...>) VALUES (@PrimaryKey, <...some other values ...>)

COMMIT

Глобальный идентификатор

Идентификатор в пределах таблицы – это конечно здорово, но отнюдь не предел мечтаний. В некоторых случаях вовсе не лишней была бы возможность получить запись, гарантировано уникальную в пределах базы данных, экземпляра сервера или даже в пределах всех серверов предприятия. Для уникальности в пределах БД тип данных int, может еще и сгодится, но вот если брать что-то более глобальное, то четырех миллиардов уникальных значений может и не хватить. Max(int) – это много, но не так много как хотелось бы, проблема в том, что назначая новое автоинкрементное поле, которое по идее должно быть гарантировано уникальным при любых обстоятельствах, приходится думать о других уникальных полях, чтобы ни коим образом диапазоны их идентификаторов не пересеклись, а отсюда и совершенно неестественные ограничения.

Для выхода из подобной ситуации Microsoft предлагает использовать тип данных uniqueidentifier - 16 байтное число, которое, будучи сгенеренным с помощью специальной функции, является гарантировано уникальным при любых обстоятельствах. Вся прелесть такого подхода заключается в том, что такой идентификатор, будучи полученным на одном сервере, заведомо не пересечется с другими подобными же идентификаторами, полученными на других серверах. Уникальный идентификатор получается с помощью функции NewID(). Типичный сценарий работы с таким идентификатором выглядит примерно так:

DECLARE @PrimaryKey uniqueidentifier

BEGIN TRAN

SET @PrimaryKey = NewID()

INSERT INTO MasterTbl (PrimaryKey, <... some fields ...>) VALUES (@PrimaryKey, <... some values ...>)

INSERT INTO DetailTbl (ForeignKey, <... some other fields ...>) VALUES (@PrimaryKey, <...some other values ...>)

COMMIT

Для разработчиков под ADO.Net есть еще один удобный повод использовать подобный идентификатор. Поскольку, на самом деле, это обычный GUID, то при добавлении записей в отсоединенный набор данных (Dataset), можно совершенно спокойно получить этот идентификатор на клиенте и быть уверенным, что на сервере такого нет.

Таким образом, использовать обычный автоинкремент удобно только в пределах таблицы, а GUID, во всех остальных случаях. Если же, хотя бы в отдаленной перспективе, предвидится какое-нибудь подобие репликации или синхронизации данных между различными базами или серверами, то лучше в качестве идентификатора сразу использовать GUID. Это серьезно уменьшит количество головной боли.

Производительность

Довольно часто возникает вопрос о производительности этих двух способов. Естественно, что в общем случае работа с identity быстрее, чем с GUID. Но заметно это становится только на больших объемах данных, порядка десятков миллионов записей на таблицу. На таких объемах выигрыш от использования identity составляет от 5 до 20% в зависимости от сложности запросов, но эти данные очень приблизительные и всерьез на них ориентироваться не стоит. Если стоит проблема выбора, то лучше померить на конкретных данных и структуре базы.

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

Timestamp (rowvesion)

Строго говоря, существует еще один тип данных, предназначенный для идентификации, но идентификации несколько другого рода. Тип данных rowversion предназначен для идентификации версии строки в пределах базы данных.

ПРИМЕЧАНИЕ

Вообще можно указывать как timestamp (старое название), так и rowversion, но Microsoft рекомендует использовать rowversion, поскольку, во-первых, это более точно отражает суть этого типа данных, а во вторых, ключевое слово timestamp зарезервировано в стандарте для другого типа.

Если в таблице имеется поле типа rowversion (оно, как и identity, может быть только одно на таблицу), то значение в этом поле будет автоматически меняться, при изменении любого другого поля в записи. Таким образом, запомнив предыдущее значение, можно определить - менялась запись, или нет, не проверяя всех полей. Для каждой базы SQL сервер ведет отдельный счетчик rowversion. При внесении изменения в любую таблицу со столбцом такого типа, счетчик увеличивается на единицу, после чего новое значение сохраняется в измененной строке. Текущее значение счетчика в базе данных хранится в переменной @@DBTS. Для хранения данных rowversion используется 8 байт, посему этот тип вполне может быть представлен, как varbinary(8) или binary(8).

Номер строки

Просьба подсказать способ вывести номер строки является лидером хит-парада. Этот вопрос по сути своей не корректен и, тем не менее, встречается наиболее часто. Но и вопрошающих тоже можно понять, ведь попросить вывести номер строки вполне естественно. Но дело в том, что в реляционной теории вообще нет такого понятия как «номер строки», в этом смысле записи в таблице абсолютно равноценны. Более того, если задуматься, то становится ясно, что в принципе нет таких задач, которые требовали бы нумерации строк на сервере перед отправкой их клиенту. Нет смысла просить номер у хранилища данных, поскольку эта информация не постоянная и зависит от порядка отображения, а, следовательно, не находится в компетенции хранилища. Клиентскому же приложению, занимающемуся отображением полученной информации, не составляет ни какого труда при получении строк пронумеровать их в требуемом порядке.

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

ANSI SQL

Этот способ пронумеровать выводимые записи на сервере по идее должен работать на любых СУБД, минимально удовлетворяющих требованиям стандарта ANSI SQL.

SELECT

(SELECT count(*) FROM NumberingTable WHERE OrderedValue <= X.OrderedValue), X.*

FROM NumberingTable X ORDER BY OrderedValue

Способ этот всем хорош, кроме двух вещей: во-первых, необходимо, чтобы столбец, по которому надо сортировать (OrderedValue) был уникальным, а во вторых, работает эта конструкция ужасно медленно, причем скорость падает с каждой новой записью.

Microsoft SQL Server

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

SET NOCOUNT ON

DECLARE @tmp TABLE(ID int IDENTITY, OuterID int)

INSERT INTO @tmp (OuterID)

SELECT [ID] FROM sysobjects ORDER BY [Name]

SELECT T.[ID], SO.* FROM sysobjects SO INNER JOIN @tmp T ON SO.[ID] = T.OuterID

ORDER BY T.[ID]

Oracle

Здесь можно отделаться более простым запросом, но тоже не совсем тривиальным. Эта СУБД дает некоторый доступ к своей внутренней информации, и внутри у нее записи пронумерованы. Но проблема в том, что сервер нумерует строки для своих нужд до сортировки, поэтому приходится делать вложенный запрос с сортировкой.

SELECT RowNum, U.* FROM

(SELECT * FROM user_tables ORDER BY tablespace_name) U

Но еще раз хочу напомнить, что если есть возможность избавить сервер от нумерации, значит надо сервер от нее избавлять. В приложении должно быть что-то сильно не так, если требуется подобная функциональность.

Постраничный вывод

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

Microsoft SQL Server

Здесь можно придумать довольно много решений и все они будут по-своему хороши.

Во-первых, в данном случае, на удивление эффективным может оказаться применение динамических запросов. При удачном стечении обстоятельств (нужные индексы в нужных местах, и достаточно простые выборки пусть и на большом объеме данных) этот способ является самым быстрым.

DECLARE @Page int, @PageSize int, @MaxRecord varchar(10), @Count varchar(10)

-- номер страницы

SET @Page = 10

-- размер страницы

SET @PageSize = 20

SET @MaxRecord = cast((@Page * @PageSize + @PageSize) as varchar(10))

SET @Count = cast(@PageSize as varchar(10))

EXECUTE ('SELECT * FROM

    (SELECT TOP ' + @Count + ' * FROM

      (SELECT TOP ' + @MaxRecord + ' * FROM sysobjects

       ORDER BY name ASC) SO1

     ORDER BY name DESC) SO2

   ORDER BY name')

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

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

SET NOCOUNT ON

DECLARE @Page int, @PageSize int, @MaxRecord int

-- номер страницы

SET @Page = 10

-- размер страницы

SET @PageSize = 20

-- создание временного хранилища

DECLARE @pg TABLE(RowNum int IDENTITY, OuterID int)

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

-- из исходной таблицы

SET @MaxRecord = @Page*@PageSize + @PageSize

-- установка количества записей обрабатываемых запросом

SET ROWCOUNT @MaxRecord

-- запись отсортированных данных в переменную

INSERT INTO @pg (OuterID)

SELECT ID FROM OriginalTable ORDER BY SortValue ASC

-- теперь нужны записи для одной страницы

SET ROWCOUNT @PageSize

-- вот эти данные уходят на клиента

SELECT O.* FROM OriginalTable O INNER JOIN @pg P

ON O.ID = P.OuterID

WHERE RowNum > @MaxRecords - @PageSize

ORDER BY P.RowNum

-- снятие ограничений на количество записей

-- обрабатываемое одним запросом

SET ROWCOUNT 0

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

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

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

SET NOCOUNT ON

DECLARE @Page int, @PageSize int, @MinRecord int, @MaxRecord int

-- номер страницы

SET @Page = 10

-- размер страницы

SET @PageSize = 20

SET @MinRecord = @Page*@PageSize

SET @MAXRecord = @Page*@PageSize+@PageSize

SET ROWCOUNT @MaxRecord

-- создание курсора

DECLARE @Cursor CURSOR

SET @Cursor = CURSOR SCROLL KEYSET READ_ONLY FOR

  SELECT * FROM OriginalTable

  ORDER BY SortValue

OPEN @Cursor

-- смещение к нужной записи

FETCH ABSOLUTE @MinRecord FROM @Cursor

DECLARE @i int

SET @i = 0

-- выор в цикле нужного количества

WHILE @i < @PageSize

BEGIN

  FETCH NEXT FROM @Cursor

  SET @i = @i + 1

END

CLOSE @Cursor

DEALLOCATE @Cursor

SET ROWCOUNT 0

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

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

Если Вам нужна помощь с академической работой (курсовая, контрольная, диплом, реферат и т.д.), обратитесь к нашим специалистам. Более 90000 специалистов готовы Вам помочь.
Бесплатные корректировки и доработки. Бесплатная оценка стоимости работы.

Поможем написать работу на аналогичную тему

Получить выполненную работу или консультацию специалиста по вашему учебному проекту
Нужна помощь в написании работы?
Мы - биржа профессиональных авторов (преподавателей и доцентов вузов). Пишем статьи РИНЦ, ВАК, Scopus. Помогаем в публикации. Правки вносим бесплатно.

Похожие рефераты: