Здравствуй, человек-читатель блогов на Community.

Хочу рассказать о своем недавнем опыте оптимизации курсора в SQL Server.
Первое, что нужно знать, курсор – это не хорошо, а плохо . Где есть возможность заменить курсор на INSERT SELECT или на использование временной таблицы, там нужно это сделать (за редким исключением). Курсор – это почти всегда дополнительные ресурсы сервера и резкое падение производительности по сравнению с другими решениями.
Второе, иногда без курсора не обойтись – там где не обойтись без построчного прохода по результату выборки. Вот в таких случаях очень важно правильно создать нужный тип курсора – тот, который соответствует решаемой задаче. Общий синтаксис объявления курсора имеет вид:

DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,... n ] ] ] [ ;]

Остановлюсь на первых трех строчках ключевых параметров.
LOCAL или GLOBAL: если хотим, чтобы курсор был доступен другим процедурам, функциям, пакетам в рамках нашей сессии, то GLOBAL – в этом случае за удалением курсора следим сами (команда DEALLOCATE). Во всех остальных случаях (т.е. в подавляющем своем большинстве) – LOCAL . Внимание, по умолчанию создается именно GLOBAL курсор!
FORWARD_ONLY или SCROLL: если хотим ходить по курсору, как ненормальные, туда-сюда, то SCROLL, иначе – FORWARD_ONLY . Внимание, по умолчанию создается SCROLL курсор!
STATIC или KEYSET, DYNAMIC, FAST_FORWARD: если хотим, чтобы при проходе по курсору отображалась актуальная информация из таблицы (т.е., если после открытия курсора, мы поменяли информацию в одном из полей таблицы и хотим, чтобы при проходе по курсору в нужной строчке курсора была уже обновленная информация), то используем или KEYSET (если КАЖДАЯ таблица, участвующая в выборке, имеет уникальный индекс) или DYNAMIC (самый медленный тип). Если же нам нужен снимок результата выборки после открытия курсора – STATIC (самый быстрый тип – копия результата выборки копируется в базу tempdb и работаем уже с ней). FAST_FORWARD = KEYSET+FORWARD_ONLY+READ_ONLY – пацаны из инета пишут, что STATIC дольше открывается (т.к. создается копия в tempdb), но быстрее работает, а FAST_FORWARD – наоборот. Так что если количество записей велико (насколько большое показывает практика), то применяем STATIC, иначе – FAST_FORWARD. Внимание, по умолчанию создается DYNAMIC курсор.

Таким образом, для большого кол-ва записей в большинстве случаев мой выбор:
DECLARE cursor_name CURSOR LOCAL FORWARD_ONLY STATIC FOR
select_statemen t

для небольшого кол-ва записей:
DECLARE cursor_name CURSOR LOCAL FAST_FORWARD FOR
select_statement

Теперь перейдем к практике (что собственно и подтолкнуло меня к писанине сего).
Испокон веков при объявлении курсора я применял конструкцию DECLARE ... CURSOR LOCAL FOR...
При разработке интеграции с одной очень нехорошей базой, в которой нет ни одного индекса и не одного ключа, я применил тот же подход при объявлении курсоров, что и всегда. Выборка одного курсора содержала 225 000 записей. В результате процесс импорта данных из такой базы занял 15 часов 14 минут !!! И хотя импорт и был первичный (т.е. одноразовый), но даже для нормального тестирования такого импорта потребовалось бы несколько суток! После замены вышеназванной конструкции при объявлении курсора на DECLARE .. CURSOR LOCAL FORWARD_ONLY STATIC FOR.. весь процесс импорта занял... внимание... 10 минут 5 секунд !!! Так что игра точно стоит свеч.
Хочу повториться, что идеальный вариант - это все же не использовать курсоры вообще - для СУБД MS SQL намного роднее реляционный, а не навигационный подход.

Ребека М. Риордан "Курсоры в Transact-SQL"

Интернет-Университет Информационных Технологий

http://www.INTUIT.ru

Учебный курс: «Программирование в Microsoft SQL Server 2000»

Курсор – это особый временный объект SQL, предназначенный для использования в программах и хранимых процедурах. С его помощью можно в цикле пройти по результирующему набору строк запроса, по отдельности считывая и обрабатывая каждую его строку. В хранимых процедурах с помощью курсоров можно выполнять сложные вычисления, которые трудно выразить с помощью синтаксиса инструкции SELECT. Большой теоретический материал урока дополнен очень хорошими примерами. В частности, рассматривается применение функции CURSOR_STATUS, описание переменных @@CURSOR_ROWS и @@FETCH_STATUS, и многое другое.

Вы научитесь:

• объявлять курсор;
• открывать курсор;
• закрывать курсор;
• освобождать курсор;
• использовать простую команду FETCH;
• осуществлять выборку строки в переменные;
• осуществлять выборку строки по ее абсолютной позиции;
• осуществлять выборку строки по ее относительной позиции;
• выполнять позиционную модификацию;
• выполнять позиционное удаление;
• использовать глобальную переменную @@CURSOR_ROWS для определения количества строк во множестве курсора;
• использовать глобальную переменную @@FETCH_STATUS для определения результатов выполнения команды FETCH;
• использовать функцию CURSOR_STATUS для запроса статуса курсора.

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

Например, поскольку нет возможности указать на определенную строку во множестве, представление пользователю строк по одной за раз может вызвать затруднения. Даже несмотря на то, что предоставляемые Transact-SQL расширения к стандартному языку SQL позволяют реализовать гораздо большие возможности для программирования, тем не менее остаются операции, которые затруднительно, трудоемко или даже вообще невозможно выполнить на основе принципов работы с множествами.

Чтобы справиться с подобными ситуациями, в SQL предусмотрены курсоры. Курсор представляет собой объект, который указывает на определенную строку во множестве. В зависимости от сути созданного вами курсора, вы можете перемещать курсор внутри множества и модифицировать или удалять данные.

Понятие о курсорах

Microsoft SQL Server реально поддерживает два различных типа курсоров: курсоры Transact-SQL и курсоры API (курсоры программного интерфейса приложений). Курсоры API создаются внутри приложения, использующего объекты Microsoft ActiveX Data Objects (ADO), OLE DB, ODBC или DB-Library. Каждое из этих API поддерживает несколько отличающиеся функциональные возможности и использует различный синтаксис. Здесь мы не будем подробно обсуждать курсоры API; если вы планируете использовать их, обратитесь к соответствующей документации на API и языку программирования, который вы собираетесь применить.

Курсоры Transact-SQL создаются с помощью команды DECLARE CURSOR. Как объект курсора, так и множество строк, на которое он указывает, должны существовать на сервере. Подобные курсоры называются серверными курсорами. Если вы используете серверный курсор из приложения, соединенного с SQL Server через сеть, каждая операция с курсором требует двустороннего сетевого взаимодействия. Библиотеки API-курсоров, поддерживающие серверные курсоры, поддерживают также клиентский курсор, который существует в клиентской системе и кэширует строки, которые он обрабатывает на клиенте.

Множество строк, на которое указывает курсор, определяется с помощью команды SELECT. При создании курсора Transact-SQL на команду SELECT накладываются несколько ограничений:

команда SELECT не может возвращать несколько результирующих множеств;

команда SELECT не может содержать фразу INTO для создания новой таблицы;

команда SELECT не может содержать фразу COMPUTE или COMPUTE BY, используемые для агрегирования результатов. (Однако, она может содержать функции агрегирования, например, AVG.)

Характеристики курсоров

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

Отражение изменений

Способность курсора отражать изменения в данных называется чувствительностью курсора. Предположим, что вы создали курсор для оператора:

SELECT * FROM Oils WHERE Left(OilName, 1) = "B" База данных Aromatherapy вернет четыре строки, как показано на рис.1. Если в процессе использования вами курсора кто-либо добавит значение Description для элемента Bergamot, либо добавит строку для элемента Bayberry, что произойдет с множеством строк, на которое указывает ваш курсор?

Рис. 1. База данных Aromatherapy содержит четыре строки, начинающиеся с буквы В.

При создании вашего курсора могут быть независимо определены два вида чувствительности: изменения каких строк включаются во множество (членство множества) и отражение изменений в исходных строках.

Прокрутка

Второй характеристикой курсора является способность осуществления прокрутки как вперед, так и назад, либо только вперед. Здесь имеет место извечная для программирования дилемма: скорость против гибкости. Последовательные курсоры (forward-only) работают значительно быстрее, но имеют меньшую гибкость.

Обновление

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

Типы курсоров

Transact-SQL поддерживает четыре различных типа курсоров: статические, ключевые, динамические и курсоры быстрого доступа, или "пожарные" (firehose). Каждый тип курсора хранит различные данные относительно строк, на которые он указывает, кроме того, каждому типу курсора свойственны различные сочетания характеристик, рассмотренных в предыдущем разделе.

Статические курсоры

Статический курсор делает как бы моментальный снимок данных, задаваемых оператором SELECT, и хранит их в базе данных tempdb. Он "не чувствует" изменений в структуре или в значениях данных, а поскольку любые модификации будут отражены только в копии, этот курсор всегда открывается в режиме "только чтение". Статические курсоры, однако, могут быть объявлены как последовательные или как прокручиваемые.

Ключевые курсоры

Ключевой курсор копирует в базу tempdb только те столбцы, которые уникально идентифицируют каждую строку. Чтобы иметь возможность объявить ключевой курсор, каждая таблица, входящая в определение оператора SELECT, должна иметь уникальный индекс, который задает копируемый набор – ключ.

Ключевые курсоры могут быть как модифицируемыми, так и иметь режим "только для чтения". Они также могут быть прокручиваемыми или последовательными.

Членство в ключевом курсоре фиксируется на момент объявления курсора. Если в процессе открытого состояния курсора добавляется строка, удовлетворяющая условию отбора, она не будет добавлена во множество. В нашем предыдущем примере, где в качестве условия отбора использовалось LEFT(OilName, 1) = "B", новая строка со значением поля OilName "Bayberry" не будет добавлена к строкам, относящимся к области действия курсора.

Аналогично, если изменение вносится в строку, которая после этого не будет удовлетворять условию членства во множестве, например, замена "Basil" на "Kumquat", строка все же останется членом множества. Даже если строка удаляется, она по-прежнему остается членом множества, но SQL Server возвращает NULL для всех значений столбцов.

Хотя членство во множестве курсора после открытия курсора остается фиксированным, тем не менее, изменения значений данных, вносимые в исходные таблицы, находят отражение. Например, изменение значения поля Description для строки Bergamot будет возвращено курсором. Однако изменения значений множества ключей отражаются в курсорах только в том случае, если они осуществляются внутри курсора. В продолжение предыдущего примера, если значение поля OilName было изменено с "Basil" на "Kumquat" внутри курсора, курсор вернет "Kumquat". Если же изменение было внесено другим пользователем, курсор по-прежнему будет возвращать "Basil".

Совет. Как мы увидим в следующем разделе, создание курсора и открытие курсора являются разными операциями. Чтобы обновить содержимое ключевого курсора, можно закрыть и повторно открыть его.

Динамические курсоры

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

Для динамических курсоров действует одно ограничение: используемый для определения курсора оператор SELECT может содержать фразу ORDER BY только в том случае, если имеется индекс, включающий в себя столбцы, используемые в фразе ORDER BY. Если вы объявляете ключевой курсор с использованием фразы ORDER BY, не оперирующей индексом, SQL Server преобразует курсор в ключевой.

Курсоры быстрого доступа

SQL Server поддерживает специальную оптимизированную форму не прокручиваемого курсора, допускающего только чтение. Этот вид курсора объявляется с использованием ключевого слова FAST_FORWARD, и чаще всего его называют "пожарным" курсором (firehose).

"Пожарные" курсоры очень эффективны, но при их использовании имеются два важных ограничения. Во-первых, если в операторе определения SELECT курсора вы использовали столбцы с типом данных text, ntext или image, а также фразу TOP, SQL Server преобразует курсор в ключевой.

Во-вторых, если оператор SELECT, который вы использовали для определения курсора, содержит таблицы, имеющие триггеры, и таблицы, не имеющие триггеров, курсор преобразуется в статический. Триггеры представляют собой сценарии Transact-SQL, которые автоматически исполняются сервером при выполнении для таблицы операторов Data Manipulation Language (DML). Подробнее мы рассмотрим триггеры в уроке 29, сейчас же обратим внимание на следующий момент: если кто-либо добавляет триггер к одной из таблиц, используемых курсором, ваше приложение может внезапно остановить выполнение, поскольку SQL Server преобразует более быстрый курсор в менее быстрый.

Использование курсоров

Использование курсоров подобно использованию локальных переменных – вы объявляете их, устанавливаете значение, а затем используете. Однако, в отличие от локальных переменных, которые автоматически уничтожаются при выходе за пределы области действия, вы должны явным образом освобождать используемые курсором строки, а затем уничтожать курсор.

Создание курсоров

Первым шагом в использовании курсора является его создание. Курсоры Transact-SQL создаются с помощью оператора DECLARE CURSOR.

Внимание! SQL Server поддерживает два различных метода создания курсоров: с использованием синтаксиса SQL-92 и с использованием синтаксиса Transact-SQL. Синтаксис SQL-92 соответствует стандарту ANSI, но имеет меньшие функциональные возможности, чем синтаксис Transact-SQL, который и рассматривается здесь.

Оператор DECLARE CURSOR имеет следующий синтаксис:

DECLARE имя_курсора CURSOR

[видимость]

[прокрутка]

[блокировка]

FOR оператор_выборки

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

Видимость курсора определяется с помощью ключевых слов LOCAL или GLOBAL, которые имеют тот же эффект, что и ключевые слова @local_table или @@global_table при объявлении временных таблиц.

Совет. SQL Server будет закрывать и освобождать локальный курсор при выходе за пределы его области действия (видимости), но лучше всегда делать это явно.

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

Параметр тип определяет тип создаваемого курсора. Здесь допустимы ключевые слова STATIC, KEYSET, DYNAMIC и FAST_FORWARD. Параметр типа FAST_FORWARD и параметр прокрутки FORWARD_ONLY являются взаимно исключающими.

Параметр блокировка определяет, могут ли строки модифицироваться курсором, и если да, то могут ли их модифицировать другие пользователи. Если используется ключевое слово READ_ONLY, курсор не может вносить никаких изменений в исходные данные. Тем не менее, другие пользователи могут модифицировать данные, либо это можете делать вы сами с помощью оператора UPDATE. Если в качестве параметра блокировка задано SCROLL_LOCKS, обновления могут быть выполнены только курсором. Все другие операторы UPDATE, как внутри этого же пакета, так и предоставляемые другими пользователями, выполняться не будут.

Последняя опция блокировки, OPTIMISTIC, позволяет осуществлять обновления строк как внутри курсора, так и вне его. Это наиболее гибкая опция, но при этом всегда существует вероятность, что модификация, выполненная курсором, окончится неудачей, если строка была изменена после ее чтения курсором.

Параметр TYPE_WARNING предписывает SQL Server отправлять предупреждающее сообщение клиенту, если тип курсора преобразуется от заданного к другому типу. Это возможно, если вы объявляете курсор, который не поддерживает заданный оператор SELECT.

Параметр оператор_выборки, указываемый в фразе FOR, является обязательным. Он задает строки, которые будут включены во множество курсора.

Фраза FOR UPDATE является необязательной. По умолчанию курсоры являются модифицируемыми, если не задан параметр READ_ONLY, однако и в этом случае лучше все-таки использовать эту фразу, чтобы быть уверенным в полученном результате. Вы можете использовать раздел OF имена_столбцов, чтобы указать определенные строки, для которых вы допускаете модификацию. Если вы опускаете раздел OF имена_столбцов, модификация может быть выполнена для всех столбцов, указанных в операторе SELECT.

Курсорные переменные

Transact-SQL позволяет объявлять переменные типа CURSOR. В этом случае стандартный синтаксис DECLARE не создает курсор; вы должны явно установить переменную для курсора с помощью ключевого слова SET.

DECLARE myCursor CURSOR

FOR SELECT OilName FROM Oils

DECLARE @myCursorVariable CURSOR

SET @myCursorVariable = myCursor

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

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

DECLARE @myCursorVariable CURSOR SET @myCursorVariable = CURSOR LOCAL FAST_FORWARD FOR SELECT OilName FROM Oils

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

Открытие курсора

Объявление курсора создает объект курсора, но не создает набор записей, которыми курсор будет манипулировать (множество курсора). Множество курсора не создается, пока вы не откроете курсор. После достаточно сложного синтаксиса оператора DECLARE CURSOR, синтаксис оператора кажется вполне прозрачным:

OPEN курсор_или_переменная

Ключевое слово GLOBAL помогает избежать конфликтов: если курсор, объявленный с ключевым словом LOCAL, и курсор, объявленный с ключевым словом GLOBAL, имеют одинаковый идентификатор, ссылки на курсор будут по умолчанию отнесены к локальному курсору, если вы не использовали ключевое слово GLOBAL. Как и в других подобных случаях, лучше явно указывать ключевое слово, если вы открываете глобальный курсор.

Закрытие курсора

Закончив использование курсора, вы должны его закрыть. Оператор CLOSE освобождает ресурсы, используемые для обслуживания множества курсора, а также освобождает все блокировки, наложенные на строки, если вы использовали параметр SCROLLOCKS в операторе DECLARE. Синтаксис команды CLOSE почти идентичен синтаксису оператора OPEN – меняется только ключевое слово:

CLOSE курсор_или_переменная

Освобождение курсора

Последним оператором в последовательности действий, связанных с созданием курсора, является оператор DEALLOCATE. Синтаксис его также прост:

DEALLOCATE курсор_или_переменная

Однако здесь есть одна тонкость: оператор DEALLOCATE удаляет идентификатор или курсорную переменную, но он не обязательно удаляет сам курсор. Сам курсор не удаляется до тех пор, пока все ссылающиеся на него идентификаторы будут либо освобождены, либо перестанут действовать (при выходе за пределы области действия). Рассмотрим следующие примеры:

Создание курсора DECLARE myCursor CURSOR KEYSET READ_ONLY FOR SELECT * FROM Oils -- Создание курсорной переменной DECLARE @cursorVariable CURSOR -- Создание множества записей курсора OPEN myCursor -- Назначение переменной курсору SET @cursorVariable = myCursor -- Освобождение курсора DEALLOCATE myCursor

После освобождения курсора идентификатор myCursor больше не ассоциируется с множеством курсора, но поскольку на множество курсора еще ссылается переменная @cursorVariable, курсор и множество курсора не освобождаются. Если вы явно не освободите также и курсорную переменную, курсор и множество курсора будут существовать, пока переменная не утратит свое действие.

Манипулирование строками с помощью курсора

Курсоры сами по себе не вызвали бы никакого интереса, если бы вы не могли осуществлять с их помощью определенные действия. Transact-SQL поддерживает три различные команды для работы с курсорами: FETCH, UPDATE и DELETE.

Команда FETCH извлекает указанную строку из множества строк курсора. В своем простейшем варианте команда FETCH имеет следующий синтаксис:

FETCH курсор_или_переменная

В этом формате записи возвращается строка в позиции курсора (текущая строка).

Используйте простую команду FETCH

1. Перейдите к папке SQL 2000 Step by Step в корневой директории, выделите сценарий с именем SimpleCursor и нажмите кнопку Open (Открыть).
2. Query Analyzer загрузит сценарий в окно Query (Запрос).

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

Команда FETCH может не только возвращать строку непосредственно, но и позволяет сохранять значения из возвращенного столбца в переменных. Чтобы сохранить результаты выполнения команды FETCH в переменной, используйте следующий синтаксис:

FETCH курсор_или_переменная INTO список_переменных

Список_переменных есть перечень разделяемых запятыми идентификаторов переменных. Перед выполнением команды FETCH вы должны объявить переменные. Список_переменных должен содержать переменную для каждого столбца, фигурирующего в операторе SELECT, который определяет курсор. Тип данных переменной должен либо совпадать, либо быть совместимым с типом данных столбца.

Выберите строки с записью ее в переменные

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

Эти ключевые слова дают вам возможность задавать абсолютную позицию во множестве курсора. Ключевые слова FIRST и LAST возвращают первую и последнюю строки соответственно, в то время как ABSOLUTE n задает строку, отстоящую на n строк от начала (если n положительно) или от конца (если n отрицательно) множества записей курсора. Значение n может быть выражено в виде константы (3) или в виде переменной (@theRow).

Выберите строки по их абсолютной позиции

1. Выделите сценарий с именем FetchAbsolute и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

Помимо ключевых слов, дающих возможность извлекать строки по их абсолютной позиции, оператор FETCH предусматривает три ключевых слова, позволяющих извлекать строки по их позиции относительно текущей строки. Оператор FETCH NEXT возвращает следующую строку, оператор FETCH PRIOR возвращает предыдущую строку, а оператор FETCH RELATIVE n возвращает строку, отстоящую на n строк от текущей. Подобно оператору FETCH ABSOLUTE n, оператор FETCH RELATIVE n может задавать строки, предшествующие текущей, если n отрицательно, и строки, следующие за текущей, если n положительно.

Выберите строки по их относительной позиции

1. Выделите сценарий с именем FetchRelative и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

Если курсор имеет тип FORWARD_ONLY или PAST_FORWARD, для указания позиции может быть использовано только ключевое слово NEXT. В действительности, если курсор относится к одному из этих типов, ключевое слово NEXT не нужно. SQL Server предполагает, что каждый оператор FETCH фактически представляет собой оператор FETCH NEXT.

Используйте оператор FETCH NEXT для быстродействующего курсора

1. Выделите сценарий с именем FetchFirehose и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

Нажмите кнопку Run Query (Выполнить запрос) в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит запрос.

Модификация и удаление строк через курсоры

Если ваш курсор является модифицируемым. Изменение исходных значений во множестве курсора выполняется достаточно просто. Предусмотрена специальная форма фразы WHERE, которая поддерживает модификацию через курсор:

UPDATE таблица_или_представление SET список_для_модификации WHERE CURRENT OF курсор_или_переменная

Это называется позиционным обновлением. Transact-SQL также поддерживает позиционное удаление, которое имеет следующую форму записи:

DELETE таблица_или_представление WHERE CURRENT OF курсор_или_переменная

Выполните позиционное обновление

1. Выделите сценарий с именем PositionedUpdate и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

Нажмите кнопку Execute Query (Выполнить запрос) в панели инструментов анализатора запросов Query Analyzer. Query Analyzer выполнит запрос. Обратите внимание, что отображаются две панели сетки. Первая создается оператором FETCH и содержит начальное содержимое столбцов. Вторая является результатом выполнения оператора SELECT и содержит значение поля Description после модификации.

Мониторинг курсоров Transact-SQL

Transact-SQL предоставляет две глобальные переменные и функцию, которые помогают вам контролировать работу и состояние вашего курсора. Переменная @@CURSOR_ROWS возвращает количество строк во множестве последнего курсора, открытого в соединении. Значения, возвращаемые @@CURSOR_ROWS, представлены в таблице 1.

Переменная @@FETCH_STATUS возвращает информацию о выполнении последней команды FETCH. В таблице 2 представлены значения, возвращаемые переменной @@FETCH_STATUS.

Наконец, Transact-SQL предоставляет функцию CURSOR_STATUS. Эта функция имеет следующий синтаксис:

CURSOR_STATUS(тип, курсор_или_переменная) Тип может иметь значения "local", "global" или "variable", а курсор_или_переменная – это идентификатор курсора или курсорной переменной, информацию о котором требуется получить. Результаты, возвращаемые функцией CURSOR_STATUS, представлены в таблице 3.

Используйте функции мониторинга курсором

1. Выделите сценарий StatusFunctions и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).

Версия для печати

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

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

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

Жизненный цикл курсора:

Создание курсора: DECLARE <имя курсора> [ INSENSITIVE ] [ SCROLL ] CURSOR FOR < SELECT -оператор> FOR { READ ONLY | UPDATE }

Здесь ключевое слово INSENSITIVE означает, что курсор будет статическим (слепок с данных), в то время как по умолчанию курсор создаётся динамическим (выборка осуществляется каждый раз при обращении к строке). Ключевое слово SCROLL означает, что курсор можно прокручивать в любом направлении, иначе курсор создаётся «последовательным».

Открытие курсора: OPEN [ GLOBAL ] <имя курсора>. Курсор, указанный как GLOBAL , не удаляется автома-тически при завершении работы той процедуры или пакета, из которых он был вызван.

Считывание данных : FETCH [[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n ] FROM ] [ GLOBAL ] <имя курсора> [ INTO @ variable _ name , …]. SQL Server 2000 позволяет считывать из курсора всего одну строку. Ключевое слово FIRST – возвратить первую строку курсора; LAST – последнюю строку курсора; NEXT – следующую строку за текущей, возвращённая строка становится текущей; PRIOR – предыдущую перед текущей; ABSOLUTE n – возвращает строку по её абсолютному порядковому номеру в курсоре; RELATIVE – через n строк после текущей. Данные столбцов будут сохраняться в каждую из указанных переменных в порядке их перечисления.

Изменение данных: выполняет команда UPDATE с синтаксисом, предназначенным для работы с курсорами.

Удаление данных: выполняет команда DELETE с синтаксисом, предназначенным для работы с курсорами.

Закрытие курсора: CLOSE [ GLOBAL ] <имя курсора>

Освобождение курсора: DEALLOCATE [ GLOBAL ] <имя курсора>

Пример использования курсора:

DECLARE fo_curs CURSOR STATIC FOR

SELECT name_rus from fo ORDER BY name_rus

DECLARE @name varchar(50)

FETCH FIRST FROM fo_curs INTO @name

WHILE @@FETCH_STATUS=0

FETCH NEXT FROM fo_curs INTO @name

DEALLOCATE fo_curs

2.7. Обеспечение безопасности и сохранности данных в Microsoft SQL Server. Управление базами данных. Роли. Назначение прав пользователям (GRANT, DENY, REVOKE). Методы и технологии защиты данных в SQL Server.

Система безопасности и администрирование SQL Server . .

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

Управление базами данных

Для создания базы данных средствами TSQL используется команда CREATE DATABASE, однако обычно для этой цели используются возможности SQL Server Management Studio. В SQL сервер определено достаточно много операций с базами данных: увеличение (уменьшение) размеров файлов, изменение конфигурации (команда ALTER), присоединение и отсоединение, передача прав владения, изменение имени, просмотр свойств и, наконец, удаление (DROP DATABASE).

Как и в большинстве серверов баз данных, в SQL Server существует пользователь, наделенный всеми административными полномочиями - это System Administrator или ‘sa" . После начальной установки сервера пароль sa пуст. Пользователь, создающий новую базу данных, автоматически становится её владельцем (‘dbo" – Data Base Owner). В момент создания базы определяется и пользователь "guest". Если учётная запись пользователя явно не отображается в пользователя конкретной базы данных, пользователю предоставляется неявный доступ с использованием гостевого имени guest. Обычно guest запрещают.

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

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

Db_owner. Имеет все права в базе данных

Db_accessadmin. Может добавлять или удалять пользователей

Db_securityadmin. Управляет всеми разрешениями, объектами, ролями и пользователями

Db_ddladmin. Может выполнять все команды DDL, кроме GRANT, DENY, REVOKE

Db_backupoperator. Может выполнять команды архивир. данных

Db_datareader. Может просматр. любые данные в любой таблице

Db_datawriter. Может модифиц. любые данные в любой таблице

Db_denydatareader. Запрещ. просматр. люб. данные в люб. таблиц

Db_denydatawriter. Запрещ модифицир люб данные в люб таблиц

Назначение прав пользователям. Основой системы безопасности SQL Server являются (1) учётные записи (accounts); (2) пользователи (users); (3) роли (roles); (4) группы (groups).

Когда пользователь подключается к SQL Server , действия, которые он может выполнять, определяются правами, выданными ему как пользователю и члену роли. Права выдаются администратором СУБД, владельцем базы данных или владельцем конкретного объекта БД. Права в БД можно разделить на три категории: (1) права на доступ к объектам баз данных; (2) права на выполнение команд TSQL ; (3) неявные права. Сервер позволяет передавать права владения от одного пользователя другому.

Для управления разрешениями пользователя на доступ к объектам базы данных используются следующие команды:

GRANT { ALL | < вид действия >,…}

{ ON {<имя таблицы или представления>} [(<имя столбца>,…)]

| ON {< имя хранимой процедуры >}

| ON {< имя пользовательской функции >}

TO { PUBLIC | <имя объекта системы безопасности>,…}

[ AS <имя группы> | <имя роли>]

– назначение прав пользователям , где

ALL – пользователю предоставляются все возможные разрешения, иначе указать

<вид действия> – права на доступные для пользователя действия, а именно:

SELECT – на просмотр, для столбца таблицы и для таблицы (представления)

INSERT – на добавление, для таблицы (представления) в целом

UPDATE – на изменение, для столбца таблицы и для таблицы (представления)

DELETE – на удаление, для таблицы (представления) в целом

EXECUTE – на выполнение хранимых процедур

REFERENCES – возможность ссылаться на указанный объект (вводить в состав внешнего ключа).

<имя объекта системы безопасности> – учётные записи SQL Server , пользователи домена Windows; PUBLIC – для всех пользователей.

WITH GRANT OPTION – позволяет пользователю, которому сейчас предоставляются права, самому назначать права на доступ к объекту другим пользователям.

AS <имя группы> | <имя роли> – участие пользователя в роли, которой предоставлена возможность предоставлять права другим пользователям.

GRANT SELECT ON authors TO public

GRANT INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom

GRANT SELECT ON Plan_Data TO Accounting WITH GRANT OPTION

GRANT SELECT ON Plan_Data TO Jack AS Accounting

Джек не входит в роль Accounting , но кто-нибудь из этой роли может предоставить право

DENY { ALL | < вид действия >,…}

{ ON {<имя таблицы или представления>} [(<имя столбца>,…)]

| ON {<имя хранимой процедуры>}

| ON {<имя пользовательской функции>}

TO { PUBLIC | <имя объекта системы безопасности>,…}

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

Пример (на запрещение выполнения команды TSQL):

DENY CREATE TABLE TO Jack CASCADE

Команда REVOKE используется для неявного отклонения доступ к объектам базы данных. Синтаксис аналогичен команде DENY. Неявное отклонение подобно запрещению доступа с тем отличием, что оно действует только на том уровне, на котором определено. Пример: пользователю Jack , который является участником роли GoodUsers , предоставлены права на доступ к таблице XFiles . Если при помощи REVOKE для роли GoodUsers отклоняются права на доступ к этой таблице, пользователь Jack всё равно может обращаться к этой таблице, поскольку права для него определены явно. Если же применить REVOKE персонально для него, он потеряет право на доступ к XFiles.

Разрешения, предоставленные роли, наследуются их членами. Если пользователю предоставлен доступ к объекту через членство в одной роли, но запрещён в другой, то конфликт доступа всегда решается в пользу запрещения.

Технологии защиты данных в MS SQL Server

1.Механизм checkpoints – контрольных точек, которые генерируются через ~60 с для записи обновлённых страниц на диск (контрольная точка может быть поставлена принудительно командой CHECKPOINT).

2.Встроенные и внешние механизмы проверки целостности базы данных (запускаются автоматически или, как утилита DBCC – Database Consistency Checker – вручную).

3.Физическое дублирование (если оно разрешено) файлов баз данных средствами операционной системы (включая механизм зеркальных жёстких дисков).

4.Резервирование баз данных и журналов транзакций – путём записи дампа базы данных на устройство резервирования (магнитную ленту или жёсткий диск).

5.Репликация – возможность дублирования информации путём её периодической (в некоторых случаях – синхронной) передачи с одного SQL сервера на другой.

6.Шифрование трафика между клиентом и сервером, а также шифрование кодов, использованных для работы с объектами БД (хранимых процедур, триггеров и др.)

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

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

Что такое курсор?

Курсор не может использоваться в MySQL сам по себе. Он является важным компонентом хранимых процедур. Я бы сравнил курсор с «указателем » в C / C + + или итератором в PHP-операторе foreach .

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

Такая операция по обработке записи может быть также исполнена на PHP-уровне, что значительно уменьшает объем передаваемых на PHP-уровень данных, так как мы можем просто вернуть обработанный сводный / статистический результат обратно (тем самым устраняя процесс обработки select – foreach на стороне клиента).

Поскольку курсор реализуется в хранимой процедуре, он имеет все преимущества (и недостатки), присущие ХП (контроль доступа, пре-компиляция, трудность отладки и т.д.)

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

Пример практического применения

На моем персональном сайте есть страница с результатами игр моей любимой команды НБА: Лейкерс .

Структура таблицы этой страницы довольно проста:

Рис 1. Структура таблицы результатов игр Лейкерс

Я заполняю эту таблицу с 2008 года. Некоторые из последних записей с результатами игр Лейкерс в сезоне 2013-14 приведены ниже:

Рис. 2. Данные таблицы результатов игр Лейкерс (частичные) в сезоне 2013-2014

(Я использую MySQL Workbench в качестве GUI-инструмента для управления базой данных MySQL. Вы можете использовать другой инструмент по своему выбору).

Что ж, должен признать, что баскетболисты Лейкерс в последнее время играют не очень здорово. 6 поражений подряд по состоянию на 15 января. Я определил эти «6 поражений подряд », посчитав вручную, сколько матчей подряд, начиная с текущей даты (и вниз к более ранним играм) имеют в колонке winlose значение «L » (поражение).

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

Можем ли мы сделать то же самое с помощью одного оператора SQL? Я не являюсь экспертом SQL, потому не смог придумать, как достичь нужного результата («6 поражений подряд ») через один оператор SQL. Мнения гуру будут для меня очень ценными — оставьте их в комментариях ниже.

Можем ли мы сделать это через PHP? Да, конечно. Мы можем получить данные по играм (конкретно, столбец winlos ) этого сезона и перебрать записи для вычисления длительности текущей серии побед / поражений подряд.

Но чтобы сделать это, нам придется охватить все данные за этот год и большая часть данных будет для нас бесполезна (не слишком вероятно, что какая-то команда будет иметь серию длиннее, чем 20+ игр подряд в регулярном сезоне, который состоит из 82 матчей).

Тем не менее, мы не знаем наверняка, сколько записей должно быть извлечено в PHP для определения серии. Так что нам не обойтись без напрасного извлечения ненужных данных. И, наконец, если текущее количество выигрышей /поражений подряд это единственное, что мы хотим узнать из этой таблицы, зачем нам тогда извлекать все строки данных?

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

Добавление каждой новой записи будет автоматически обновлять и эту таблицу. Но это слишком громоздкий и чреватый ошибками способ.

Так как же можно сделать это лучше?

Использование курсора в хранимой процедуре

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

Давайте создадим в MySQL Workbench первую ХП:

DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `streak`(in cur_year int, out longeststreak int, out status char(1)) BEGIN declare current_win char(1); declare current_streak int; declare current_status char (1); declare cur cursor for select winlose from lakers where year=cur_year and winlose<>"" order by id desc; set current_streak=0; open cur; fetch cur into current_win; set current_streak = current_streak +1; start_loop: loop fetch cur into current_status; if current_status <> current_win then leave start_loop; else set current_streak=current_streak+1; end if; end loop; close cur; select current_streak into longeststreak; select current_win into `status`; END

В этой ХП у нас есть один входящий параметр и два исходящих. Это определяет подпись ХП.

В теле ХП мы также объявили несколько локальных переменных для серии результатов (выигрышей или проигрышей, current_win ), текущей серии и текущего статуса выигрыш /проигрыш конкретного матча:

Эта строка является объявлением курсора. Мы объявили курсор с именем cur и набор данных, связанных с этим курсором, который является статусом победа /поражение для тех матчей (значение столбца winlose может быть либо «W », либо «L », но не пустое) в конкретном году, которые упорядочены по идентификатору id (последние сыгранные игры будут иметь более высокий ID) в порядке убывания.

Хотя это не видно наглядно, но мы можем себе представить, что этот набор данных будет содержать последовательность значений «L » и «W ». На основании данных, приведенных на рисунке 2, она должна быть следующей: «LLLLLLWLL… » (6 значений «L », 1 «W » и т.д.)

Для расчета количества побед / поражений подряд мы начинаем с последнего (и первого в приведенном наборе данных) матча. Когда курсор открыт, он всегда начинается с первой записи в соответствующем наборе данных.

После того, как первые данные загружены, курсор перемещается к следующей записи. Таким образом, поведение курсора похоже на очередь, перебирающую набор данных по системе FIFO (First In First Out). Это именно то, что нам нужно.

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

Если статус следующей записи такой же, как у текущего последовательного набора побед / поражений, это означает, что серия продолжается, тогда мы увеличиваем количество последовательных побед (или поражений) еще на 1 и продолжаем перебирать данные.

Если статус отличается, это означает, что серия прервана, и мы можем остановить цикл. Наконец, мы закрываем курсор и оставляем исходные данные. После этого выводится результат.

Чтобы проверить работу этой ХП, мы можем написать короткий PHP-скрипт:

exec("call streak(2013, @longeststreak, @status)"); $res=$cn->query("select @longeststreak, @status")->fetchAll(); var_dump($res); //Dump the output here to get a raw view of the output $win=$res["@status"]="L"?"Loss":"Win"; $streak=$res["@longeststreak"]; echo "Lakers is now $streak consecutive $win.n";

Результат обработки должен выглядеть приблизительно так, как показано на следующем рисунке:

Вывод набора данных из хранимой процедуры

Несколько раз по ходу этой статьи разговор касался того, как вывести набор данных из ХП, которая составляет набор данных из результатов обработки нескольких последовательных вызовов другой ХП.

Пользователь может захотеть получить с помощью ранее созданной нами ХП больше информации, чем просто непрерывная серия побед / поражений за год; например мы можем сформировать таблицу, в которой будут выводиться серии побед /поражений за разные годы:

YEAR	Win/Lose	Streak
2013	L	6
2012	L	4
2011	L	2

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

Хранимые процедуры MySQL могут возвращать только скалярные значения (целое число, строку, и т.д.), в отличие от операторов select … from … (результаты преобразуются в набор данных). Проблема в том, что таблица, в которой мы хотим получить результаты, в существующей структуре базы данных не существует, она составляется из результатов обработки хранимой процедуры.

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

Сначала мы создадим вторую ХП, код которой показан ниже:

DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `yearly_streak`() begin declare cur_year, max_year, min_year int; select max(year), min(year) from lakers into max_year, min_year; DROP TEMPORARY TABLE IF EXISTS yearly_streak; CREATE TEMPORARY TABLE yearly_streak (season int, streak int, win char(1)); set cur_year=max_year; year_loop: loop if cur_year

Несколько существенных замечаний к приведенному выше коду:

1. Мы определяем самый ранний и самый поздний года для выборки из таблицы lakers ;
2. Мы создаем временную таблицу для хранения исходящих данных с необходимой структурой (season, streak, win );
3. В цикле мы сначала выполняем ранее созданную ХП с необходимыми параметрами (call streak(cur_year, @l, @s );), затем захватываем возвращаемые данные и вставляем их во временную таблицу (insert into yearly_streak values (cur_year, @l, @s); );
4. Наконец, мы выбираем из временной таблицы и возвращаем набор данных, после чего делаем некоторую настройку (DROP TEMPORARY TABLE IF EXISTS yearly_streak; ).

Чтобы получить результаты, мы создаем еще один небольшой PHP-скрипт, код которого показан ниже:

query("call yearly_streak")->fetchAll(); foreach ($res as $r) { echo sprintf("In year %d, the longest W/L streaks is %d %sn", $r["season"], $r["streak"], $r["win"]); }

Выведенные на экран результаты будут выглядеть приблизительно следующим образом.

In my T-SQL code I always use set based operations. I have been told these types of operations are what SQL Server is designed to process and it should be quicker than serial processing. I know cursors exist but I am not sure how to use them. Can you provide some cursor examples? Can you give any guidance on when to use cursors? I assume Microsoft included them in SQL Server for a reason so they must have a place where they can be used in an efficient manner.

Solution

In some circle"s cursors are never used, in others they are a last resort and in other groups they are used regularly. In each of these camps they have different reasons for their stand on cursor usage. Regardless of your stand on cursors they probably have a place in particular circumstances and not in others. So, it boils down to your understanding of the coding technique then your understanding of the problem at hand to make a decision on whether or not cursor-based processing is appropriate or not. To get started let"s do the following:

• Look at an example cursor
• Break down the components of the cursor
• Provide additional cursor examples
• Analyze the pros and cons of cursor usage

How to Create a SQL Server Cursor

Creating a SQL Server cursor is a consistent process, so once you learn the steps you are easily able to duplicate them with various sets of logic to loop through data. Let"s walk through the steps:

1. First, you declare your variables that you need in the logic.
2. Second you declare cursor with a specific name that you will use throughout the logic. This is immediately followed by opening the cursor.
3. Third, you fetch a record from cursor to begin the data processing.
4. Fourth, is the data process that is unique to each set of logic. This could be inserting, updating, deleting, etc. for each row of data that was fetched. This is the most important set of logic during this process that is performed on each row.
5. Fifth, you fetch the next record from cursor as you did in step 3 and then step 4 is repeated again by processing the selected data.
6. Sixth, once all of the data has been processed, then you close cursor.
7. As a final and important step, you need to deallocate the cursor to release all of the internal resources SQL Server is holding.

From here, check out the examples below to get started on knowing when to use SQL Server cursors and how to do so.

Example SQL Server Cursor

Here is an example cursor from tip Simple script to backup all SQL Server databases where backups are issued in a serial manner:

DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = "C:\Backup\" SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM MASTER.dbo.sysdatabases WHERE name NOT IN ("master","model","msdb","tempdb") OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + "_" + @fileDate + ".BAK" BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor

SQL Server Cursor Components

Based on the example above, cursors include these components:

• DECLARE statements - Declare variables used in the code block
• SET\SELECT statements - Initialize the variables to a specific value
• DECLARE CURSOR statement - Populate the cursor with values that will be evaluated
  • NOTE - There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement. This could be 1 or many variables and associated columns.
• OPEN statement - Open the cursor to begin data processing
• FETCH NEXT statements - Assign the specific values from the cursor to the variables
  • NOTE - This logic is used for the initial population before the WHILE statement and then again during each loop in the process as a portion of the WHILE statement
• WHILE statement - Condition to begin and continue data processing
• BEGIN...END statement - Start and end of the code block
  • NOTE - Based on the data processing multiple BEGIN...END statements can be used
• Data processing - In this example, this logic is to backup a database to a specific path and file name, but this could be just about any DML or administrative logic
• CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
• DEALLOCATE statement - Destroys the cursor

Recommended Reading

Learn more about SQL Server Cursors and alternatives:

Additional SQL Server Cursor Examples

In the example above backups are issued via a cursor, check out these other tips that leverage cursor-based logic:

• Script to create commands to disable, enable, drop and recreate Foreign Key constraints in SQL Server

SQL Server Cursor Analysis

The analysis below is intended to serve as insight into various scenarios where cursor-based logic may or may not be beneficial:

• Online Transaction Processing (OLTP) - In most OLTP environments, SET based logic makes the most sense for short transactions. Our team has run into a third-party application that uses cursors for all of its processing, which has caused issues, but this has been a rare occurrence. Typically, SET based logic is more than feasible and cursors are rarely needed.
• Reporting - Based on the design of the reports and the underlying design, cursors are typically not needed. However, our team has run into reporting requirements where referential integrity does not exist on the underlying database and it is necessary to use a cursor to correctly calculate the reporting values. We have had the same experience when needing to aggregate data for downstream processes, a cursor-based approach was quick to develop and performed in an acceptable manner to meet the need.
• Serialized processing - If you have a need to complete a process in serialized manner, cursors are a viable option.
• Administrative tasks - Many administrative tasks need to be executed in a serial manner, which fits nicely into cursor-based logic, but other system-based objects exist to fulfill the need. In some of those circumstances, cursors are used to complete the process.
• Large data sets - With large data sets you could run into any one or more of the following:
  • Cursor based logic may not scale to meet the processing needs.
  • With large set-based operations on servers with a minimal amount of memory, the data may be paged or monopolize the SQL Server which is time consuming can cause contention and memory issues. As such, a cursor-based approach may meet the need.
  • Some tools inherently cache the data to a file under the covers, so processing the data in memory may or may not actually be the case.
  • If the data can be processed in a staging SQL Server database the impacts to the production environment are only when the final data is processed. All of the resources on the staging server can be used for the ETL processes then the final data can be imported.
  • SSIS supports batching sets of data which may resolve the overall need to break-up a large data set into more manageable sizes and perform better than a row by row approach with a cursor.
  • Depending on how the cursor or SSIS logic is coded, it may be possible to restart at the point of failure based on a
  • Repeat a batch with the GO command

  Next Steps

  • When you are faced with a data processing decision determine where you stand with SQL Server cursor usage. They may or may not have a place in your application or operational processes. There are many ways to complete a task, so using a cursor could be a reasonable alternative or not. You be the judge.
  • If you run into issues with another coding technique and need to get something done quickly, using a cursor may be a viable alternative. It may take longer to process the data, but the coding time might be much less. If you have a one-time process or nightly processing, this could do the trick.
  • If cursors are shunned in your environment, be sure to select another viable alternative. Just be sure the process will not cause other issues. As an example, if a cursor is used and millions of rows are processed will this potentially flush all of the data from cache and cause further contention? Or with a large data set will the data be paged to disk or written to a temporary directory?
  • As you evaluate a cursor-based approach versus other alternatives make a fair comparison of the techniques in terms of time, contention and resources needed. Hopefully these factors will drive you to the proper technique.