45793 (665137), страница 2
Текст из файла (страница 2)
Однако следует учитывать еще ряд нюансов. Во-первых, очевидно, что никто не гарантирует отсутствия «дырок» при автоматической генерации значений в столбце. А, во вторых, генерация нового значения для автоинкремента выполняется в не явной автономной транзакции. Это означает, что если сама по себе операция добавления записи не увенчается успехом, или транзакция, в которой будет производиться добавление, закончится отменой, то сервер следующее автоинкрементное значение сгенерирует так, как будто бы предыдущее добавление новой записи произошло успешно. И таким образом, образуется разрыв в автоматической нумерации.
| -- начало транзакции со вставкой BEGIN TRAN INSERT INTO Ident_table (some_values) VALUES ('value 10') -- откат, новая запись не добавляется ROLLBACK -- А здесь вставка «по честному» INSERT INTO Ident_table (some_values) VALUES ('value 11') -- Смотрим что получилось SELECT * FROM Ident_table --- результат: ID some_values ----------- -------------------------------------------------- ... ... 10 value 8 11 value 9 13 value 11 (10 row(s) affected) |
И можно наблюдать разрыв в идентификации записей.
И еще один нюанс, даже если удалить все записи из таблицы, последующие идентификаторы, возвращаемые сервером, не обнулятся, а будут продолжать увеличиваться, как будто записи не удалялись. В некоторых случаях может понадобиться обнулить серверный генератор последовательностей или проинициализировать его каким-нибудь другим значением. Для этих целей существует системная функция DBCC CHECKIDENT. С ее помощью можно проверить счетчик «на правильность» и/или проинициализировать его новым значением. Если же необходимо сбросить счетчик в начальное значение при удалении всех записей, то можно воспользоваться не оператором DELETE, а оператором TRUNCATE. В этом случае счетчик автоматически сбросится в первоначальное значение, но следует помнить, что TRUNCATE нельзя пользоваться, если на таблицу ссылаются внешние ключи других таблиц.
Существуют так же специальные команды, с помощью которых можно узнать начальное значение генератора определенной таблицы и приращение, то есть те значения, которые были установлены при указании IDENTITY. Это IDENT_INCR и IDENT_SEED соответственно.
Таким образом, в свете всего вышесказанного, самый типичный сценарий применения автоинкремента выглядит примерно так:
| DECLARE @PrimaryKey int BEGIN TRAN INSERT INTO MasterTbl () VALUES () SET @PrimaryKey = SCOPE_IDENTITY() INSERT INTO DetailTbl (ForeignKey, ) VALUES (@PrimaryKey, ) COMMIT |
Глобальный идентификатор
Идентификатор в пределах таблицы – это конечно здорово, но отнюдь не предел мечтаний. В некоторых случаях вовсе не лишней была бы возможность получить запись, гарантировано уникальную в пределах базы данных, экземпляра сервера или даже в пределах всех серверов предприятия. Для уникальности в пределах БД тип данных int, может еще и сгодится, но вот если брать что-то более глобальное, то четырех миллиардов уникальных значений может и не хватить. Max(int) – это много, но не так много как хотелось бы, проблема в том, что назначая новое автоинкрементное поле, которое по идее должно быть гарантировано уникальным при любых обстоятельствах, приходится думать о других уникальных полях, чтобы ни коим образом диапазоны их идентификаторов не пересеклись, а отсюда и совершенно неестественные ограничения.
Для выхода из подобной ситуации Microsoft предлагает использовать тип данных uniqueidentifier - 16 байтное число, которое, будучи сгенеренным с помощью специальной функции, является гарантировано уникальным при любых обстоятельствах. Вся прелесть такого подхода заключается в том, что такой идентификатор, будучи полученным на одном сервере, заведомо не пересечется с другими подобными же идентификаторами, полученными на других серверах. Уникальный идентификатор получается с помощью функции NewID(). Типичный сценарий работы с таким идентификатором выглядит примерно так:
| DECLARE @PrimaryKey uniqueidentifier BEGIN TRAN SET @PrimaryKey = NewID() INSERT INTO MasterTbl (PrimaryKey, ) VALUES (@PrimaryKey, ) INSERT INTO DetailTbl (ForeignKey, ) VALUES (@PrimaryKey, ) 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, но область действия этого оператора распространяется и на подзапросы, что в данном случае не годится, поэтому и приходится использовать временную таблицу.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
