| DELETE TOP(10) FROM Orders ORDER BY Date DESC |
В предыдущих версиях SQL Server для использования переменной в качестве параметра оператора TOP приходилось прибегать к сложным конструкциям или динамическому созданию запроса.
| DECLARE @DynamicQuery varchar(100)SET @DynamicQuery = 'SELECT TOP ' + CAST (@N AS varchar) + ' * FROM Products'EXECUTE(@DynamicQuery) |
Разбиение данных на страницы с использованием оператора TOP
Наиболее простым способом использования оператора TOP для разбиения на страницы является использование смещения по колонке ID. Процедура принимает параметр – количество записей на странице и «последний» ID, полученный в предыдущем запросе. При этом, благодаря новым возможностям оператора TOP, можно варьировать количество записей на странице, используя такую простую процедуру.
| SELECT TOP (@N) * FROM ИмяТаблицы WHERE КолонкаID > @ID |
Более общий подход, не требующий сохранения «последнего» ID предыдущего запроса и формально не зависящий от значения ID выглядит так:
| SELECT * FROM (SELECT TOP (@N * (@PageNum + 1)) * FROM ИмяТаблицыORDER BY КолонкаID) AS PTable WHERE КолонкаID NOT IN (SELECT TOP (@N * @PageNum) КолонкаID FROM ИмяТаблицыORDER BY КолонкаID) |
Следует обратить внимание читателя, что запрос будет выполнять свою задачу лишь при условии того, что колонка КолонкаID содержит уникальные значения. Таким образом, для обеспечения разбиения данных с помощью такой процедуры нужно иметь в таблице колонку IDENTITY.
Для того, чтобы подобная процедура работала в более ранних версиях SQL Server, необходимо использовать динамическое создание запроса:
| DECLARE @Query nvarchar(200)SET @Query = 'SELECT * FROM (SELECT TOP ' + CAST(@N * (@PageNum + 1) AS nvarchar) + ' * FROM ИмяТаблицы ORDER BY КолонкаID) AS P WHERE КолонкаID NOT IN (SELECT TOP ' + CAST(@N * @PageNum AS nvarchar) + 'КолонкаID FROM ИмяТаблицы ORDER BY КолонкаID)'EXECUTE(@Query) |
В связи с интеграцией SQL Server с платформой .NET, языки которой поддерживают гибкий механизм обработки исключений, разработчики SQL Server включили в T-SQL давно желанную SQL-программистами возможность обработки исключений. Текущая реализация в SQL Server 2005 позволяет обрабатывать некритические ошибки с помощью похожего на ставший уже стандартным синтаксис TRY … CATCH.
| BEGIN TRY -- «Опасный» запросEND TRYBEGIN CATCH -- ОбработкаошибкиEND CATCH |
Механизм обработки ошибок в T-SQL, конечно, не такой гибкий, как в .NET языках, но, тем не менее, позволяет сделать достаточно много, анализируя код ошибки, возвращаемый функцией @@ERROR. Например, если таблица Products задана так, что не позволяет хранить отрицательное значение в колонке Quantity:
| CREATE TABLE [Products]( [ProductID] [int] IDENTITY(1,1) NOT NULL, [BrandID] [int] NOT NULL, [Model] [nvarchar](32) NOT NULL, [Configuration] [nvarchar](128) NOT NULL, [Price] [money] NOT NULL, [Quantity] [int] NOT NULL, CHECK ([Quantity] >= 0)) |
то можно использовать следующий код для изменения количества имеющихся на складе ноутбуков и создания заказа:
| BEGIN TRY BEGIN TRAN -– Созданиетранзакции INSERT INTO Orders([Date], ProductID, Quantity, [Year] ) VALUES(GETDATE(), @ProductID, @Quantity, @Year) SET @OrderID = @@IDENTITY UPDATE Products2 SET Quantity = Quantity - @Quantity WHERE ProductID = @ProductID UPDATE Orders SET Quantity = @Quantity WHERE OrderID = @OrderIDCOMMIT –- Если нет ошибок, то подтверждаем транзакциюEND TRYBEGIN CATCH DECLARE @Err int SET @Err = @@ERROR ROLLBACK –- Откаттранзакции IF @ERR = 547BEGIN SELECT 'Недостаточно ноутов на складе' AS ErrorEND ELSE BEGIN SELECT 'Неизвестнаяошибка' AS Error, @Err AS ErrorNumberENDEND CATCH |
Пусть новые возможности Transact-SQL в SQL Server 2005 не вносят ничего принципиально нового в устройство SQL Server, но позволяют значительно быстрее создавать более понятный и легко читаемый код, упрощая, таким образом, жизнь разработчика.
В заключение, автор настоятельно рекомендует читателю ознакомиться со статьями из списка литературы, поскольку в них описаны кардинальные изменения основной концепции SQL Server.
Список литературы
Иван Бодягин, Новые возможности MS SQL Server 2004 "Yukon", RSDN Magazine #6-2003
Антон Злагостев, MS SQL Server 9 “Yukon”. Интеграция с .NET, RSDN Magazine #6-2003
Иван Бодягин, Версионность в “Yukon”, RSDN Magazine #6-2003
Иван Бодягин, MS SQL 2005: оконные функции, RSDN Magazine #6-2004
Алексей Ширшов, Использование XML совместно с SQL, RSDN Magazine #2-2004