Масиви та Списки до SQL Server

1. Передмова


Часто в форумах можна бачити питання виду Як я можу використовувати масиви в SQL сервері? або Чому не працює запит SELECT * FROM tbl WHERE col IN (@list) ? Дана стаття описує кілька способів вирішення цих питань як хороших, так і поганих. Також представлені дані про продуктивність цих способів вирішення.


Це досить довга стаття, тому якщо Ви хочете просто дізнатися, як оперувати списком значень, розділених комами, то виберіть одну з 3-х посилань, залежно від версії SQL сервера, які ви використовуєте: SQL2000, SQL7, SQL6.5. Якщо запропоновані там рішення не підходять Вам, тоді поверніться назаад і почніть читати спочатку. 🙂


Якщо Ви впали в прострацію, побачивши розмір цієї статті, то Ви з полегшенням дізнаєтеся, що це одна з тих статей, які не обов'язково читати цілком і по порядку. Якщо Ви відносите себе до "чистих" SQL програмістам, який хоче знати відповідь на питання "Яким чином я можу ….?", то Ви можете знайти, що розділ, присвячений продуктивності, містить занадто багато подробиць. З іншого боку справжні "зубри" SQL, яким цікаві показники продуктивності, можуть знайти описи методів трохи втомливими і можуть віддати перевагу переглянути їх побіжно.


2. Постановка завдання.


Имееются кілька значень для ключового поля, що визначають деяку кількість записів таблиці. І необхідно вибрати ці записи. Якщо запит складається у клієнтському додатку, то програмний код може виглядати приблизно так:

SQL = "SELECT ProductID, ProductName FROM Northwind .. Products" & _
“WHERE ProductID IN (” & List & “)”
rs = cmd.Execute(SQL)


де List – це змінна, яка містить список string значень, розділених комами, наприклад, "9, 12, 27, 39".


Однак, при використанні збережених процедур ми бачимо, що немає очевидного методу для реалізації такого запиту. Деякі намагаються використати таку процедуру:

CREATE PROCEDURE get_product_names @ids varchar(50) AS
SELECT ProductID, ProductName
FROM Northwind..Products
WHERE ProductID IN (@ids)



яку потім намагаються викликати таким орбразом:


EXEC get_product_names “9, 12, 27, 37”


але отримують таке повідомлення про помилку:

Server: Msg 245, Level 16, State 1, Procedure get_product_names, Line 2
Syntax error converting the varchar value "9, 12, 27, 37" to a column of data type int.


Це відбувається тому, що в цьому випадку ми вже не складаємо SQL запит динамічно, і @ ids є тільки одне зі значень у реченні IN. IN пропозиція також може інтерпретуватися як:

… WHERE col IN (@a, @b, @c)


Інші, стикаючись з цією проблемою, кажуть собі: От якщо б у T-SQL були б масиви як в будь-якому іншому нормальною мовою, то не було б і проблеми. Але в T-SQL є масиви. Називаються вони таблицями і для вирішення всіх завдань вони є більш природним вибором, ніж масиви. Якщо все, що Вам потрібно, це простий масив з одиничними integer значеннями в кожному осередку, то такий масив легко емулюється за допомогою таблиці. Однак, Ви не можете передавати таблицю як параметр в збережену процедуру. Якщо в одній збереженій процедурі визиввется інша збережена процедура, то проблеми не існує, тому що можна для обміну даними використовувати загальну таблицю. Але такий спосіб неможливий, коли збережена процедура запускається з клієнтського застосування. АЛЕ: клієнтська програма може передати в якості вхідного параметра рядок, який в SQL Ви можете "розпакувати" в таблицю. Більшість методів, описаних у цій статті, використовують саме такий метод.


3. Огляд методів вирішення


Існує кілька можливих рішень задачі, які можуть бути застосовані в більшості випадків. У статті предствален тільки відомі мені методи рішення. Ось список цих методів, розділений на дві групи:


Хороші методи:



Методи, які краще не використовувати:



Ви також можете ознайомитися з коротким оглядом методів рішення від Anith Sen за адресою http://www.bizdatasolutions.com/tsql/sqlarrays.asp. Хоча його оцінки деяких методів не завжди збігаються з моїми.


4. Основні міркування по інтерфейсу


Перш ніж приступити до розгляду методів рішення, я хотів би висвітлити некотрое загальні питання. Деяким ці питання можуть здатися тривіальними, але я хотів би донести до Вас дуже важливе питання про продуктивності, так що потерпіть.


Більшість представлених у статті методів можуть бути оформлені у вигляді функцій, які повертають таблицю (table-valued functions) або у вигляді збережених процедур. Це дуже добре, тому що це дозволяє Вам вибрати метод рішення, написати одну-дві функції і потім використовувати їх в будь-якому місці. Ось, наприклад, таким чином:


CREATE PROCEDURE get_company_names_iter @ customers nvarchar (2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN charlist_to_table (@ customers) s ON C. CustomerID = s.str
go
EXEC get_company_names_iter “ALFKI, BONAP, CACTU, FRANK”


У SQL7 Ви правда повинні будете використовувати замість користувальницької функцііo збережену процедуру. Ви можете ознайомитися з подбробностямі такого використання тут

SQL7.


Далі, як би міг виглядати інтерфейс функції charlist_to_table? Вхідний параметер міг би бути безрозмірного типу, переважно text або ntext, щоб функція могла оперувати вхідними даними будь-якого розміру. Але деякі методи рішення використовують функції T-SQL, які не підтримують типи даних text / ntext. У цьому випадку Ви повинні використовувати типи даних varchar (8000) або nvarchar (4000). Ви також можете додати параметер, який буде визначати роздільник у переданої рядку відмінний від коми, або будь-які інші необхідні Вам параметри. Ви можете зустріти приклади використання таких додаткових параметрів в даній статті.


Як би могла виглядати таблиця з результатами роботи функції? Очевидно, що вона повинна включати поле, котре буде містити значення з переданого списку елементів (ми повернемося до цього трохи нижче). Іноді Вам також може знадобитися поле, яке буде містити порядковий номер елемента в списку. Таке поле легко можливо реалізувати в одних методах і дуже важко реалізувати в інших.


Отже, є вхідний список елементів, але якого типу даних ці елементи? Ви можете написати функцію, в якій враховувати всі можливі типи даних SQL Server. У даній же статті обговорюються тільки списки integer і string елементів, тому що за моїми припущеннями ці типи даних використовуються в 99% всіх випадків.


Якщо Ви використовуєте список integer елементів, то Ви повинні будете перетворити їх якимось чином з типу string в тип integer, тому що вхідні дані є текстовими. Як саме Ви напишіть функцію, повертає таблицю integer значень є справа Вашого смаку. Можливо Ви віддасте перевагу такий спосіб:


CREATE PROCEDURE get_product_names_tblnum @ ids varchar (50) AS
SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
JOIN charlist_to_table (@ ids) s ON P. ProductID = convert (int, i.str)
go
EXEC get_product_names_tblnum “9, 12, 27, 37”


Якщо Ваша функція повертає список string елементів, то Вам може здатися, що немає ніяких проблем з конвертацією типів. Але бережіться! У SQL Server є дві групи текстових типів днних: 8-бітові типи даних char / varchar / text і 16-бітові типи даних nchar / nvarchar / ntext для Unicode. То який з цих типів даних повинна повертати ваша функція? Вам може здатися, що використання типу даних nvarchar (4000) для поля таблиці є найкраще рішення, тому що Ви отримаєте правильні результати як для 8-бітових так і для 16-бітових типів даних. Але не поспішайте! Правильна відповідь така: якщо Ви пов'язуєте таблиці по полю типу char / varchar, то Ваша функція повинна також повертати тип даних varchar, а якщо по полю nchar / nvarchar, то, відповідно, функція повинна повертати nvarchar. І причина в наступному. У даному запиті:


SELECT *
FROM tbl
JOIN charlist_to_table (@ list) c ON tbl.varcharcol = c.nvarcharcol


полі tbl.varcharcol буде неявно перетворено до типу nvarchar і це перетворення запобігти використанню SQL Server-Му будь-якого індексу по полю tbl.varcharcol. А це в свою чергу може дуже сильно сказати на продуктивності.


Є два способи вирішити цю проблему. Перший – це написати 2 функції: одна буде повертати тип даних varchar (8000), інша – nvarchar (4000).
Другий спосіб – написати одну функцію, яка приймає вхідний параметр типу ntext і повертає таблицю з 2-х ролей, типу varchar і типу nvarchar відповідно. Для некотрих методів вирішення завдання такої підхід зменшить продуктивність (у межах 5-10% для способу з двома полями). Для інших методів такий підхід навпаки ніяк не позначиться на продуктивності. Ви можете побачити приклади використання обох способів в даній статті.


5. Метод ітерацій

Якщо все, що Вам потрібно, це готове рішення, щоб продовжити створення свого програмного коду, то даний метод якраз для Вас. Хоча є більш швидкі методи, ніж даний, але для практичного підтвердження цього вам знадобляться вхідні дані дійсно дуже великого розміру. Так що головний козир цього методу – його простота. Всього ділов-то – завантажуєте запропоновані приклади функцій і вперед і вище. А більш швидкі методи вимагають для роботи наявності допоміжної таблиці з правильними даними. Це звичайно не така щоб вже й велика проблема, але все ж вимагає наявності ще одного об'єкта. До всього іншого програмний код дуже легкий для розуміння, що важливо, якщо у Вас є досвід роботи на C або Visual Basic. Не останню роль відіграє і те, що Ви можете адаптувати запропонований код для використання з вхідною рядком довільного формату.


5.1. Список integer елементів


Ось приклад для користувача функції в in SQL 2000 для роботи зі списком integer елементів.


CREATE PROCEDURE get_product_names_iter @ ids varchar (50) AS
SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
JOIN iter_intlist_to_table (@ ids) i ON P. ProductID = i.number
go
EXEC get_product_names_iter “9 12 27 37”


Оба-на, що ми бачимо? Адже це не список значень розділених комою? Я помітив, що для списку integer значень кома як роздільник у дійсності не грає ніякої ролі. Тому при написанні функції я вибрав замість коми пробіл як роздільник. Ось код функції iter_intlist_to_table:


CREATE FUNCTION iter_intlist_to_table (@list ntext)
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)

SET @textpos = 1
SET @leftover = “”
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 – datalength(@leftover) / 2
SET @ tmpstr = ltrim (@ leftover + substring (@ list, @ textpos, @ chunklen))
SET @textpos = @textpos + @chunklen

SET @pos = charindex(” “, @tmpstr)
WHILE @pos > 0
BEGIN
SET @str = substring(@tmpstr, 1, @pos – 1)
INSERT @tbl (number) VALUES(convert(int, @str))
SET @ tmpstr = ltrim (substring (@ tmpstr, @ pos + 1, len (@ tmpstr)))
SET @pos = charindex(” “, @tmpstr)
END

SET @leftover = @tmpstr
END

IF ltrim(rtrim(@leftover)) <> “”
INSERT @tbl (number) VALUES(convert(int, @leftover))

RETURN
END


Повертається цією функцією таблиця складається з двох полів. number – це значення елемента списку, а listpos – це порядковий номер елемента в списку.


Оскільки функція оперує тільки числовими елементами, то Вас може здивувати те, що в ній використовуються символьні типи даних в кодуванні Unicode, тобто ntext і nvarchar. Однак я з'ясував, що при використанні даних такого типу можна збільшити продуктивність функції на 10% в порівнянні з використанням 8-бітних символьних типів даних. Я точно не знаю чому саме, але припускаю, що це пов'язано з тим, що функція charindex на внутрішньому рівні оперує даними в Unicode. Тому для конвертації 8-бітних сімволних типів даних потрібно дополнітелной час.


Власне метод ітерацій представляє з себе 2-хуровневий цикл. Це викликано тим, що вхідний парметер у нас типу ntext, а значить він безрозмірний. Але не всі функції T-SQL беруть text / ntext в якості парметр (а ті функції, які приймають, все одно не оперують даними з довжиною більшою ніж у varchar / nvarchar.). Тому я розбиваю вхідні рядок на порції по 4000 символів і потім переходжу до основного циклу, в якому використовується функція charindex для пошуку символу пробілу, який був обраний в якості роздільника. Якщо Ви віддаєте перевагу в якості роздільника бачити кому, то Ви легко можете змінити функцію. Коли внутрішній цикл завершується, то в поточній порції вхідного рядка можуть ще залишатися якісь символи, які потрібно приєднати до початку наступної порції. І в самому кінці, коли оброблена остання порція, вибирається останній елемент списку.


Ви могли помітити, що я використовую як функцію datalength так і len. Ці дві функції оманливе схожі один на одного, але використовувати їх треба обережно. Обидві вони повертають довжину рядка, але datalength враховує хвостові прогалини, а len немає. Одн більш важливо те, що тільки функція datalength підтримує типи даних text / ntext. І ще: функція datalength підраховує довжину рядка в байтах, а len – в символах. Тому я і ділю результат функції datalength на 2, а результат функції len немає.


Функція розрахована на те, що вхідний параметер @ list містить тільки number елементи. В іншому випадку Ви отримаєте повідомлення про помилку конвертації і виконання бетча перерветься. Якщо Ви формуєте список в клієнтському додатку, то Ви повинні переконатися втому, що передаєте правильні дані. Але така перевірка не є надто вже важкою задачей.Но що робити, якщо Ви хочете додати перевірку вхідних даних? Наприклад, якщо Ви як DBA не дуже то довіряєте розробникам клієнтського додатка? Ви можете додати функцію перевірку чи є @ str насправді числом. Але у випадку помилки Ви не зможете використовувати RAISERROR, тому що це заборонено в призначених для користувача функції. Найкраще, що Ви можете зробити, це пропустити помилковий запис зі списку. Або додати NULL як його значення (у цьому випадку не забудьте дозволити використання NULL в повертається таблиці). Якщо ж Ви хочете видавати повідомлення про помилку, то Вам доведеться замість користувача функції використовувати збережену процедуру. Ви можете знайти приклад такої процедури тут SQL7.


5.2. Список string елементів


Ось проста функція, яка повертає таблицю зі string елементами списку.


CREATE FUNCTION iter_charlist_to_table
(@list ntext,
@delimiter nchar(1) = N”,”)
RETURNS @ tbl TABLE (listpos int IDENTITY (1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS

BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)

SET @textpos = 1
SET @leftover = “”
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 – datalength(@leftover) / 2
SET @ tmpstr = @ leftover + substring (@ list, @ textpos, @ chunklen)
SET @textpos = @textpos + @chunklen

SET @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0
BEGIN
SET @ tmpval = ltrim (rtrim (left (@ tmpstr, charindex (@ delimiter, @ tmpstr) – 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @ tmpstr = substring (@ tmpstr, @ pos + 1, len (@ tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END

SET @leftover = @tmpstr
END

INSERT @ tbl (str, nstr) VALUES (ltrim (rtrim (@ leftover)), ltrim (rtrim (@ leftover)))
RETURN
END


А ось приклад використання цієї функції:


CREATE PROCEDURE get_company_names_iter @ customers nvarchar (2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN iter_charlist_to_table (@ customers, DEFAULT) s ON C. CustomerID = s.nstr
go
EXEC get_company_names_iter “ALFKI, BONAP, CACTU, FRANK”


Ця функція дуже схожа на функцію iter_intlist_to_table. Є тільки одна різниця в інтерфейсі – Ви можете задати роздільник елементів у списку. Для роздільника існує значення по-замовчуванню, але навіть якщо Вам як раз і потрібен такий роздільник, Ви все одно повинні використовувати DEFAULT при виклику функції. Тому що в T-SQL при виклику користувача функції повинні бути явно вказані всі параметри. У незалежності від того, який роздільник Ви використовуєте, в результатах функції ведучі та хвостові прогалини будуть видалені.


Повертається цією функцією таблиця складається з трьох полів: listpos, str і nstr. Два останніх поля містять значення одного і того ж елемента списку, перше у вигляді varchar, а друге – nvarchar. При об'єднанні з іншими таблицями Ви можете використовувати те поле, яке найкраще підходить Вам в даний момент. Наприклад, для об'єднання по полю Northwind .. Customers. CustomerID, тип якого nchar (10), у прикладі було вибрано поле nstr. Не використовуйте поле nstr для об'єднання з іншою таблицею по полю типу varchar, тому що тим самим ви дуже сильно знизити продуктивність запиту! (Це питання піднімався тут Основні міркування по інтерфейсу.) Ви можете заперечити, що сам по собі додатковий стовпець у результатах також впливає на продуктивність в бік її погіршення. Але при тестуванні проізводітелності я не зміг виявити такого факту.


Одне переваг даного методу полягає в можливості легко його розширити. Припустимо, що вхідні дані виглядають таким чином:


“Yes, I want to”, “Charlie”, “Give it to me”


тобто елементи списку у лапках і роздільник може також бути частиною елемента. Наведена вище функція не сприймає подібний формат, але можна легко переписати її і для роботи з таким форматом вхідних даних.


6. Метод з використанням допоміжної таблиці


Найшвидшим методом вилучення елементів з спсіка з роздільником є метод з використанням допоміжної таблиці з числами. Це дуже проста таблиця, що складається з одно поля типу integer column, яка містить значення 1, 2, 3, … і тд Ось найбільш легкий спосіб отримати таку таблицю:


SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3


Від перекладача


Оскільки база pubs є не на кожному сервері, то замість таблиці authors можна використовувати sysobjects


SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM sysobjects t1, sysobjects t2, sysobjects t3


[У початок]


6.1. Базова функція


У первинному варіанті функції для демонстрації алгоритму роботи базової функції обмежимо довжину вхідних даних 7998-ю символами. Я позаімстововал цю функцію з сайту Anith Sen-а.


CREATE FUNCTION inline_split_me (@ param varchar (7998)) RETURNS TABLE AS
RETURN(SELECT substring(“,” + @param + “,”, Number + 1,
charindex (",", "," + @ param + ",", Number + 1) – Number – 1)
AS Value
FROM Numbers
WHERE Number <= len(“,” + @param + “,”) – 1
AND substring ("," + @ param + ",", Number, 1) = ",")


На відміну від методу ітерацій, який виглядає більш громіздким, але й більш промолінейним, пропонована функція набагато компактніше, але не всі зможуть відразу ж схопити суть цього SQL запиту. (Я зіткнувся з такими ж труднощами.) Перше, що кидається в очі, це поява в тексті запиту вираження “,” + @param + “,” ні більше ні менше як 4 рази. Додаючи в початок і кінець вхідного рядка роздільник, отримуємо те, що перший і останній елементи списку нічим не відрізняються від решти елементів.


Далі, розглянемо умова WHERE нашого запиту. Вираз:


substring(“,” + @param + “,”, Number, 1) = “,”


буде мати значення TRUE для всіх позицій рядки, в яких міститься роздільник. А вираз:


Number <= len(“,” + @param + “,”) – 1


просто визначає максимальну використовується нами в запиті число.


Розглянемо тепер формується нашим запитом вираз Value. Ми витягуємо з вхідного рядка за допомогою функції substring підрядок, починаючи з наступного після роздільника символу. Довжину витягуваної підрядка ми визначаємо з результату функції charindex, за допомогою якої ми шукаємо позицію наступного роздільника. Третій параметер функції charindex ми використовуємо для вказівки позиції, з якою ми наічнаем пошук цього наступного роздільника. Якщо наступний роздільник знайдений, то ми віднімаємо з номера його позиції номер позиції поточного роздільника і ще 1 (тому що сам роздільник у результатах нам не потрібний) і отримуємо довжину поточного елемента списку.


Ось приклад використання цієї функції:


CREATE PROCEDURE get_company_names_inline @ customers nvarchar (2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inline_split_me (@ customers) s ON C. CustomerID = s.Value
go
EXEC get_company_names_inline “ALFKI,BONAP,CACTU,FRANK”


Зауважте, що оскільки дана функція не видаляє ведучі та хвостові прогалини елементів списку, то вхідні рядок не повинна і утримувати таких прогалин.


Ця функція є inline функцією на відміну від функції методу ітерацій. А inline функція по суті є макро, тому оптимізатор під час побудови плану виконання просто включає текст функції в сам запит, що робить можливим загальну оптимізацію. Я правда не вірю в те, що для такої специфічної функції можна домогтися якихось переваг, зробивши її inline функцією. Функція сама по собі достоточно складна, і я не зміг знайти будь-яких значних переваг її inline варіанту при

6.2. Вхідні дані необмеженого розміру


Функція inline_split_me обмежує довжину вхідних даних 7998-ю символами (або 3998-ю для nvarchar). У такому варіанті функції неможливо використовувати вхідні дані типу text або ntext, тому що неможливо використовувати параметер text / ntext у виразі “,” + @param + “,”. Або, що більш важливо, функція charindex не може оперувати даними text / ntext, довжина яких перевищує максимально можливу довжину для varchar / nvarchar.


Однак, це не є непереборне перешкоду. Адже у функції методу ітерацій ми змогли вирішити цю проблему розбиваючи вхідні дані на порції. Ось функція, в якій використаний такий же підхід:


CREATE FUNCTION duo_text_split_me(@list ntext,
@delim nchar(1) = N”,”)
RETURNS @t TABLE (str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
DECLARE @slice nvarchar(4000),
@textpos int,
@maxlen int,
@stoppos int

SELECT @textpos = 1, @maxlen = 4000 – 2
WHILE datalength (@ list) / 2 – (@ textpos – 1)> = @ maxlen
BEGIN
SELECT @slice = substring(@list, @textpos, @maxlen)
SELECT @ stoppos = @ maxlen – charindex (@ delim, reverse (@ slice))
INSERT @ slices (slice) VALUES (@ delim + left (@ slice, @ stoppos) + @ delim)
SELECT @ textpos = @ textpos – 1 + @ stoppos + 2 – On the other side of the comma.
END
INSERT @slices (slice)
VALUES (@ delim + substring (@ list, @ textpos, @ maxlen) + @ delim)

INSERT @t (str, nstr)
SELECT str, str
FROM (SELECT str = ltrim (rtrim (substring (s.slice, N. Number + 1,
charindex (@ delim, s.slice, N. Number + 1) – N. Number – 1)))
FROM Numbers N
JOIN @ slices s ON N. Number <= len (s.slice) – 1
AND substring (s.slice, N. Number, 1) = @ delim) AS x

RETURN
END


Спочатку ми разоб'ем вхідні дані на порції і помістимо їх в таблицю-змінну @ slices. У процесі такого розбиття ми повинні перевіряти, що в поточній порції роздільник є останнім символом. Для цього використовуємо маленький фокус: для пошуку роздільника передамо у функцію charindex результат функції reverse над поточною порцією. У підсумку ми будемо абсолютно впевнені в тому, що всі записи в таблиці @ Slices починаються і закінчуються раделітелем. Ви можете помітити, що якщо довжина вхідних даних не буде перевищувати межі для типу nvarchar, то цикл по розподіленню на порції не виконається жодного разу, тому що ми відразу ж додамо вхідні дані в таблицю @ slices.


Після того, як таблиця @ slices заповнена, ми можемо виконати основну перетворення. Зауважу, що для цього нам не потрібен цикл по записах таблиці @ slices, ми просто пов'язуємо її безпосередньо з таблицею Numbers. Використання derived таблиці дозволяє нам не повторювати складні обчислення функцій substring і charindex, а також дає можливість видалити ведучі та хвостові прогалини елементів списку. (Derived таблиця – це таблиця, створювана безпосередньо в ході виконання всього запиту. Такі таблиці дуже корисними у складних запитах. Псевдонім таблиці AS x не має ніякого особливого значення, але за правилами SQL синтаксису будь-яка derived таблиця повинна мати псевдонім.)


Також як і iter_charlist_to_table, дана функція повертає таблицю складається з 2-х стовпців – varchar і nvarchar.Однако в даному випадку тестування показало що при використанні тільки поля nvarchar продуктивність збільшується на 3-5%. Але тому час виконання вимірюється в мілісекундах, то така різниця виглядає не дуже суттєвою. Правда при порівнянні продуктивності методів я використовував варіант функції, що повертає тільки поле nvarchar.


У таблиці з результатами немає поля listpos з номером елемента в списку. У даному методі отримати такий номер досить таки складно. Один спосіб – це додати у таблтцу @ slices полі sliceno і поле IDENTITY в таблицю результатів. Тоді при додаванні в кінцеву таблицю ми можемо впорядкувати результ по sliceno і Number. Проте, немає повної Гарат того, що значення в полі IDENTITY будуть відповідати порядку, заданому в ORDER BY. Більш надійний спосіб – це зв'язати таблицю Numbers з собою ж, як показано в прикладі на сайті Anith Sen-а.


Тепер Ви напевно очікуєте побачити варіант функції для роботи зі списком number елементів, але такого варіанту в цій статті немає. Ви можете самі написати таку функцію, використовуючи convert в потрібному місці. Але можете поступити і таким чином:


CREATE PROCEDURE get_product_names_tblnum @ ids varchar (50) AS
SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
JOIN duo_text_split_me (@ ids, DEFAULT) i ON P. ProductID = convert (int, i.str)
go
EXEC get_product_names_tblnum “9, 12, 27, 37”


6.3. Список елементів як поле таблиці


До цих пір у статті малося на увазі, що вхідний параметер представляє собою передані з клієнтського застосування текстові дані. Але іноді такий список з роздільниками може міститися в полі (полях) таблиці. Наприклад в таблиці:


CREATE TABLE jackets (model varchar(30),
sizes varchar(200),
colours varchar(200))


поля sizes і colours можуть містити дані про розміри і квітах курток у вигляді списку елементів розділених комами. Така схема даних правда суперечить першій нормальній формі, що в більшості випадків є результатом помилок при проектуванні. Але, залишаючи цю тему в стороні, припустимо, що нам потрібно витягти ці списки в окремі таблиці. Ви, звичайно, можете використовувати кожну з розглянутих вже функцій, але в будь-якому випадку Ви повинні будете обробити кожен запис вихідної таблиці в циклі, тому що неможливо передати поле таблиці в призначену для користувача функцію в якості параметра. Можливо Ви вже знаєте, що прогресивна обробка таблиці може бути значно повільніше ніж обробка таблиці як набору. Тому ідея з'єднати вихідну таблицю безпосередньо з таблицею Numbers виглядає більш кращим рішенням. Я не наводжу приклад такої обробки безпосередньо тут, але Ви вже бачили такий спосіб у функції duo_text_split_me, де ми застосували його до таблиці @ slices.


7. Масив з елементами фіксованого розміру


Цей метод, запропонований Steve Kass-му (SQL Server MVP), заснований на ідеї, описаної Ken Henderson-го в його книзі The Guru "s Guide to Transact-SQL.


Основна ідея методу полягає в тому, що масив визначається як список елементів фіксованого розміру, а не як список елементів розділених комами. Перевага такого масиву полягає в тому, що роздільник відсутній у вхідних даних. Але головна перевага – це продуктивність. Даний метод є найшвидшим, з усіх представлених в даній статті (але подивіться наведені далі у статті огляд потенційних проблем). Ось приклад використання даного методу безпосередньо для таблиці:


CREATE PROCEDURE get_product_names_fix @ids varchar(8000),
@itemlen tinyint AS
SELECT P.ProductID, P.ProductName
FROM Northwind..Products P
JOIN Numbers n ON P.ProductID = convert(int,
substring (@ ids, @ itemlen * (n.Number – 1) + 1, @ itemlen))
AND n.Number <= len(@ids) / @itemlen
GO
EXEC get_product_names_fix ” 9 12 27 37″, 4


Кожен елемент "масиву" має одінаквую довжину, яка задається параметром @ itemlen. Для вилучення конкретного елемента ми використовуємо функцію substring. Використана в запиті таблиця Numbers аналогічна таблиці, яку ми використовували в методі з допоміжною таблицею.


Ось функція витягуються елементи з рядка.


CREATE FUNCTION fixstring_single (@ str text, @ itemlen tinyint)
RETURNS TABLE
AS
RETURN(SELECT listpos = n.Number,
str = substring (@ str, @ itemlen * (n.Number – 1) + 1, @ itemlen)
FROM Numbers n
WHERE n.Number <= datalength(@str) / @itemlen +
CASE datalength (@ str)% @ itemlen WHEN 0 THEN 0 ELSE 1 END)


Завдання вираження в останньому рядку – дозволити останнього елемента масиву бути меншого розміру, ніж інші елементи, у разі, коли хвостові прогалини були видалені. Ось приклад використання функції fixstring_single для списку string елементів:


CREATE PROCEDURE get_company_names_fix @ customers nvarchar (2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN fixstring_single (@ customers, 6) s ON C. CustomerID = s.str
go
EXEC get_company_names_fix “ALFKI BONAP CACTU FRANK”


Ця функція має вхідний параметер типу text і повертає таблицю з полем типу varchar. Таким чином для Unicode даних Вам потрібна функція-близнюк, яка буде мати вхідний параметер типу ntext і повертати таблицю з полем типу nvarchar. В інших методів, описаних у цій статті, я пропонував повертати з однієї функції одночасно як varchar поле так і nvarchar, але в даній функції такий підхід черевато великими перевантаженнями (бо він такий і швидкий). Знову попереджу Вас – не використовуйте Unicode функції при з'єднанні по полях типу char / varchar. Це може згубно позначитися на продуктивності. См. Основні міркування по інтерфейсу.


Таблиця результатів має також поле listpos, яке містить порядковий номер елемента в масиві. Дуже зручно те, що це поле є число з таблиці Numbers.


Функція fixstring_single може отримати з вхідного рядка стільки елементів, скільки чисел міститься в таблиці Numbers. У даній статті використовується таблиця з 8000 номерів, що здається цілком достатнім для більшості клієнтських додатків. Якщо ж ви хочете, щоб функція оперувала великим числом, то просто відвели кількість номерів в таблиці Numbers. Якщо ж Ви хочете, щоб функція оперувала вхідними даними будь-якої довжини, то Ви можете написати multi-step функцію, яка буде розбивати вхідні рядок на порції. Є правда інший "фінт", який використовує Steve Kass. Функція fixstring_multi використовує самосоедіненіе таблиці Numbers, перетворюючи таким чином 8000 записів у 64 млн:


CREATE FUNCTION fixstring_multi(@str text, @itemlen tinyint)
RETURNS TABLE AS
RETURN (SELECT listpos = n1.Number + m.maxnum * (n2.Number – 1),
str = substring(@str,
@ Itemlen * (n1.Number + m.maxnum * (n2.Number – 1) – 1) + 1,
@itemlen)
FROM Numbers n1
CROSS JOIN (SELECT maxnum = MAX (Number) FROM Numbers) AS m
JOIN Numbers n2 ON
@ Itemlen * (n1.Number + m.maxnum * (n2.Number – 1) – 1) + 1 <=
datalength(@str)
WHERE n2.Number <= datalength (@ str) / (m.maxnum * @ itemlen) + 1
AND n1.Number <= CASE WHEN datalength (@ str) / @ itemlen <= m.maxnum
THEN datalength (@ str) / @ itemlen +
CASE datalength (@ str)% @ itemlen
WHEN 0 THEN 0
ELSE 1
END
ELSE m.maxnum
END)


Ця функція більш складна, ніж fixstring_single. Залишаю читачеві в якості домашнього завдання можливість розібратися самому, що ж відбувається в цій функції. Зауважу тільки те, що CROSS JOIN позбавляє нас від тяжких праць щодо заповнення таблиці Numbers.


7.1. Масив елементів фіксованої довжини і Метод ітерацій


Оскільки для методу ітерацій в цьому випадку змінюється лише формат вхідних даних, а не сам алгоритм, то можна без проблем використовувати метод ітерацій. Якщо метод з допоміжною таблицею засталяло Вас нервувати, то тоді ось Вам вихід. (Наприклад, якщо ваша допоміжна таблиця не містить необхідної кількості записів, то Ви отримаєте неправильні результати.) Однак, виграш у продуктивності методу ітерацій при використанні вхідного рядка з елементами фіксованої довжини і при використанні роздільника не такий вже видатний. І він все одно гірше ніж у методу з допоміжною таблицею і вхідним списком з роздільником. Я не наводжу тут варіанти функції для методу ітерацій з використанням списку елементів фіксованої довжини, але в результатах тестування ви можете побачити її оцінку.


7.2. Можливі проблеми з продуктивністю і форматом даних


Якщо Ви погляньте на результати тестів, то помітите цей метод випередив усі інші. Але існує декілька потенційних проблем, про які Ви повинні завжди пам'ятати, особливо про саму останню.


Потенційна проблема номер 1: Рядок з елементами фіксованої довжини найбільш чутливий до формату. Один зайвий символ в довільному місці рядка може призвести до того, що частина рядки праворуч від цього символу буде "поламана". Якщо Ви формуєте рядок у клієнтському додаток, то ця проблема не виглядає серйозною, тому що вирішується один тільки раз. Але, проте, якщо дивитися ширше, то ми переходимо до


Потенційна проблема номер 2: Вартість програмування складання рядки з елементами фіксованої довжини більше ніж рядки з роздільником. Я не враховував цей фактор при тестуванні продуктивності, тому що це зробило б сам процес тестування більш складним. Однак, враховуйте і ці 2 фактори:




  1. Мова програмування клієнтської програми зазвичай оперує текстовими рядками краще ніж SQL і



  2. Виконання якоїсь частини обчислень у клієнтському додатку а не на сервері означає, що в середньому проізводітелность системи збільшується.


Таким чином, навіть якщо ми щось втрачаємо через додаткових дій на стороні клієнта, то ці втрати нівелюються виграшем на стороні сервера.

Потенційна проблема номер 3: Довжина переданої з клієнтського застосування рядки більше у разі використання списку з елементами фіксованої довжини. Тобто має місце більший мережевий трафік. Ось це дійсно може бути проблемою для низькошвидкісних з'єднань або перевантажених мереж. Сама по собі проблема проблема не виглядає аж такою серйозною, проте, розгляд обсягу переданої інформації приводить нас до


Четвертого і найважливіша потенційна проблема: Важливе значення має те, як Ви осуществялется виклик процедури. Я в своїх тестах використовував RPC (remote procedure call) і рекомендую Вам цей спосіб виклику процедур з клієнтського застосування. (Наприклад, якщо в ADO Ви використовуєте adCmdStoredProcedure для властивості CommandType об'єкта Command object, то Ви використовуєте RPC.) Інший спосіб викликати збережену процедуру – це пакетне виконання команд за допомогою EXEC, тобто так, як Ви делеает це в Query Analyzer. Я з'ясував, що почин з деякої довжини вхідних даних цей метод стає трохи повільніше ніж використання списку з роздільником і допоміжної таблицею (див. функцію duo_text_split_me). Фактично при виклику через RPC альтернативна версія функції fixstring_multi виявилася трохи швидше, ніж наведена трохи вище функція. Але при використанні пакетного виконання команд, час відгуку було кілька секунд, що дуже далеко від пріємлімоє. Чому так відбувається – я не знаю. Але розмір вхідного рядка виразно грає якусь роль. Я не перевіряв точно, при якій довжині вхідних даних ефект має місце бути, але за приблизними даними десь в районі 8000, тобто в районі максимально розміру типу даних varchar.


8. OPENXML


В останні роки на роль стандарту для обміну даними був висунутий XML. Також як і HTML, XML виник на основі SGML стандарту, тому чисто зовні XML і HTML дуже схожі. Але є дуже важливі відмінності. На відміну від HTML, XML чутливий у регістру і одна єдина помилка в XML документі робить його повністю неправильним. Головне призначення XML не у відображенні, але передачі структурованих даних.У Microsoft SQL Server 2000 була додана широка підтримка XML, з якої нас найбільше цікавить функція OPENXML, яка може витягати дані з XML рядки і повертати їх у вигляді таблиці.


Це дає нам можливість представити список значень у вигляді XML рядки і потім у T-SQL витягне ці значення з допомогою OPENXML. Але не будемо настільки прямолінійні – це абсолютно неправильний спосіб. І справа не продуктивності (по-справжньому повільні методи будуть показані нижче), вона якраз цілком типово для більшості випадків. Просто річ у тому, що XML дуже складний для вирішення поставленого завдання. Крім, звичайно, випадку якщо у Вас вже є готовий XML документ. Але будувати XML рядок тільки для моделювання масиву – це по-моєму самогубство.


Але якщо Вам потрібно передати на SQL Server масив структурованих даних (може бути навіть багаторівневий), щоб додати ці дані в одну або кілька таблиць, то XML в цьому випадку дійсно вихід.


Але давайте ж все-таки розглянемо випадок з простим списком. Ось приклад того, як могла б виглядати функція get_product_names із застосуванням XML:


CREATE PROCEDURE get_product_names_xml @ids ntext AS

DECLARE @idoc int,
@err int

EXEC @err = sp_xml_preparedocument @idoc OUTPUT, @ids
SELECT @err = @@error + coalesce(@err, 4711)
IF @err <> 0 RETURN @err

SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
JOIN OPENXML(@idoc, “/Root/Num”, 1)
WITH (num int) AS a ON P.ProductID = a.num

EXEC sp_xml_removedocument @idoc
go
EXEC get_product_names_xml N "<Root> <Num num="9"/> <Num num="12"/>
<Num Num="27"/> <Num num="37"/> </ Root>


Тут важливо наголосити на тому, що при виклику sp_xml_prepraredocument SQL Server створює внутрішнє подання переданої XML рядка. Змінна @ idoc є вказівник на це подання, який ми використовуємо при виконанні OPENXML, яка вдає із себе табличну функцію (хоча в Books Online використовується інше визначення – rowset provider). Перед виходом з нашої процедури ми викликаємо sp_xml_removedocument для того, щоб звільнити пам'ять, виділену для XML документа. Якщо цього не робити, то в результаті витоку пам'яті в один прекрасний момент запуск процедури sp_xml_preparedocument закінчиться помилкою. (Між іншим, тому що гарним тоном є перевірка повертається процедурою статусу завершення, то не намагайтеся перевіряти таким чином sp_xml_removedocument. Вона весь час повертає 1. Це просто баг цієї процедури. Перевіряйте краще @ @ error.)


При складанні XML рядка будьте уважні, тому що такі символи як <, & and "в XML є метасимволом, тобто Ви повинні кодувати їх приблизно також як і в HTML. Теж саме стосується і не-ASCII даних у випадку, якщо ви не використовуєте Unicode. Пам'ятайте також, що XML дуже вимогливий до наявності пари для кожного тега. Тому Вам для складання XML рядка краще скористатися якою-небудь бібліотечної функцією. Це в принципі не моя область, але здається MS XML пропонує якісь методи для цього, наприклад, клас XmlTextWriter ст. Net Framework.


Ось де XML дійсно показує все свою міць, так це у випадку коли Вам потрібно додати в SQL Server великий набір даних, наприклад, замовлення і їх зміст. До появи підтримки XML в SQL Server найкращим варіантом був запуск збереженої процедури для кожного запису. (Це завжди була операція bulk-copy, але все ж це було незручне рішення.) З XML ж Ви можете скласти XML документ, що містить всі дані, і написати збережену процедуру, що викликає один раз sp_xml_prepredocument і потім двічі OPENXML – перший раз для додавання замовлень, другий раз для додавання змісту замовлень. Заміна тисяч викликів через мережу збережених процедур на один – це дійсно великий виграш в продуктивності.


Унаслідок того, що стаття і так виходить досить довгою, я не наводжу тут приклад функції для такого роду завдання. Краще надам можливість вирішити цю задачу Вам. Якщо Ви абсолютно не знайомі з XML і тим більше з використанням XML в SQL Server, то Ви можете прочитати пару книжок на цю тему. Або пошукати де-не-яку корисну інформацію на сайті SQL Server MVP Bryant Likes http://www.sqlxml.org/.


Прімеченном: ті, хто знає XML, побачили, що в запропонованому вище прикладі використовувався attribute-centred XML. При складанні XML рядка можна також використовувати element-centred XML або обидва відразу. Я не наводжу приклад для element-centred XML, тому що різниця між ними лише в розборі елементів списку, але ніяк не в продуктивності.


8.1. Коли OPENXML НЕ потрібен


Мені попадався на очі спосіб вирішення нашої задачі, в якому передається в процедуру рядок з роздільником за допомогою функції replace перетворювалася в XML рядок для того, щоб далі використовуватися в OPENXML. По-моєму, це дуже погана ідея. І ось чому:




  1. Оскільки результат функції replace повинен бути типу varchar, то отримана XML рядок не може бути довшим 8000 символів. А значить, довжина вхідного рядка повинна бути ще менше.



  2. Для видалення всіх "неправильних" для XML символів функцію replace доведеться запускати кілька разів. Приклади, які я бачив, абсолютно не враховували цю проблему.



  3. І тому XML більш повільний метод, ніж інші, які до того ж і більш легкі у використанні, то такий підхід абсолютно даремний.


9. Динамічний SQL


Для списку number елементів цей метод із-за своєї простоти може здаватися оманливе гарним:


CREATE PROCEDURE get_product_names_exec @ ids varchar (255) AS

EXEC(“SELECT ProductName, ProductID
FROM Northwind..Products
WHERE ProductID IN (” + @ids + “)”)
go
EXEC get_product_names_exec “9, 12, 27, 37”


Цей приклад дуже схожий на той клієнтський код, котрий був даний на початку статті. Фактично, цей метод всього лише варіант формування SQL команди на стороні клієнта і тому він має такі ж недоліки, які ми зараз і розглянемо. Спочатку розглянемо використання даного методу для списку string елементів і побачимо, що в цьому випадку він вже не здається таким привабливим:


CREATE PROCEDURE get_company_names_exec @ customers nvarchar (2000) AS
EXEC(“SELECT CustomerID, CompanyName
FROM Northwind..Customers
WHERE CustomerID IN (” + @customers + “)”)
go
EXEC get_company_names_exec "" "ALFKI" "," "BONAP" "," "CACTU" "," "FRANK" ""


Вкладені лапки роблять виклик процедури на основі цього методу важкувато.


Ну так які ж недоліки цього методу?



Більш докладно про динамічний SQL можна прочитати у моїй статті The Curse and Blessings of Dynamic SQL.


Часто, коли люди у форумах питають про методи роботи зі списком з роздільником, то динамічний SQL пропонується в якості рішення. Проте особисто мене таке незграбне рішення дратує, особливо коли існують інші красиві і прості методи з перетворення списку в таблицю. Тому якщо хтось радить Вам використовувати цей метод, не йдіть цієї поради. А якщо Ви самі маєте звичку давати такі ради, то, будь ласка, припиніть це робити.


Є одне виключення: у версії SQL Server 6.5 всі інші методи можуть оперувати тільки списком типу varchar (255), тому динамічний SQL у цій версії є можливо єдине життєздатне рішення.


10. Фокус з використанням UNION


SQL Server MVP Steve Kass запропонував такий метод:


CREATE PROCEDURE unpack_with_union
@list varchar(8000),
@tbl varchar(30),
@delimiter char(1) = “,” AS
DECLARE @sql varchar(8000)
SET @sql = “INSERT INTO ” + @tbl + ” SELECT “”” +
REPLACE (@ list, @ delimiter, "" "UNION ALL SELECT" "") + "" ""
EXEC (@sql)


Ідея полягає у перетворенні списку до запиту SELECT за допомогою оператора UNION ALL. (UNION ALL використовується тому, що на відміну від UNION він не видаляє повторювані значення.) Потім ми використовуємо динамічний SQL для виконання цього запиту і додавання даних в тимчасову таблицю, ім'я якої задається вхідним параметром. Оскільки в динамічному SQL використовується тільки тимчасова таблиця, то проблеми з правами доступу користувача в даному випадку немає.


Ось приклад використання:


CREATE PROCEDURE get_company_names_union @ customers varchar (8000) AS

CREATE TABLE #temp (custid nchar(10) NOT NULL)
EXEC unpack_with_union @customers, “#temp”

SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN #temp t ON C.CustomerID = t.custid
go
EXEC get_company_names_union “ALFKI,BONAP,CACTU,FRANK”


Цей метод цілком би міг отримати нагороду за невибагливість, але розглянемо його більш уважно. Даний метод не може оперувати вхідними даними з довжиною більше, ніж ~ 3000. Ви, звичайно, можете написати версію, яка приймає ntext параметер і розбиває його на порції. Але я не переймався такою метою, тому що при тестуванні продуктивності цей метод виявився значно повільніше методу з OPENXML, який у свою чергу повільніше методу ітерацій та методу з допоміжною таблицею. Ніякими іншими специфічними достоїнствами даний метод також не володіє.


Anith Sen запропонував простий метод, який просто перетворює список в набір INSERT запитів statements:


CREATE PROCEDURE unpack_with_insert
@list varchar(8000),
@tbl varchar(30),
@delimiter char(1) = “,” AS
DECLARE @sql varchar(8000)
SET @sql = “INSERT ” + @tbl + ” VALUES (” +
REPLACE (@ list, ",", ") INSERT" + @ tbl + "VALUES (") + ")"
EXEC (@sql)


Цей метод має такі ж проблеми, як і варіант з UNION запитом. У тестах на продуктивність він вів себе навіть гірше останнього.


11. Дійсно повільні методи


У розділі Питання / Відповідь одного з SQL журналів пропонувалося наступне рішення:


CREATE PROCEDURE get_company_names_charindex @ customers nvarchar (2000) AS
SELECT CustomerID, CompanyName
FROM Northwind..Customers
WHERE charindex ("," + CustomerID + ",", "," + @ customers + ",")> 0
go
EXEC get_company_names_charindex “ALFKI,BONAP,CACTU,FRANK”


Можливо, воно нагадає Вам метод з використанням допоміжної таблиці. Додавання коми в початок і кінець вхідного рядка дозволяє нам використовувати функцію charindex для пошуку ", ALFKI," і т.д. (Зауважте, що в даному випадку вхідні рядок не повинна містити внутрішніх пробілів.)


Автор даного методу в своїй статті заявив, що метод великою продуктивністю не відрізнять, тому що використання поля таблиці у вираженні виключає використання індексу по цьому полю, приводячи до сканування таблиці. Але це тільки початок. Сканування таблиці при тестуванні займало менше 100 мілісекунд, якщо таблиця повністю перебувала в кеші. А цей метод відпрацьовував за 42 секунди, навіть для самого маленького тестового набору з 15 елементів загальною довжиною в 200 символів!


Варіанти вираження WHERE можуть ще такими:


WHERE patindex ("%," + CustomerID + ",%", "," + @ customers + ",")> 0
WHERE "," + @ customers + "," LIKE "%," + CustomerID + ",%"


Варіант з використанням patindex також виконується 42 секунди. Варіант з використанням LIKE на одній з тестових машин був фактично в 4 рази швидше, але точно також повільним як з використанням charindex і patindex на інших машинах. Як не сумно, але це все одно в 100 разів повільніше методу з динамічним SQL і в 200 разів повільніше найшвидшого методу.


12. SQL Server 7


Якщо Ви працюєте з SQL Server 7, то у Вас немає можливості використовувати для користувача функції або XML. Тому в процедурі Ви можете використовувати метод ітерацій або допоміжну таблицю, для списку з роздільником або списку елементів фіксованої довжини. Тут я наводжу приклад для методу ітерацій. Ви можете легко адаптірвать його до двох інших методів.


12.1. Процедура для роботи зі списком string елементів


Дана процедура дуже схожа на наведену вище функцію iter_charlist_to_table. Але замість повернення таблііци-змінної процедура заповнює тимчасову таблицю # strings:


CREATE PROCEDURE charlist_to_table_sp
@list ntext,
@delimiter char(1) = N”,” AS

DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000),
@sql nvarchar(4000)

SET NOCOUNT ON

SELECT @textpos = 1, @leftover = “”
WHILE @textpos <= datalength(@list) / 2
BEGIN
SELECT @chunklen = 4000 – datalength(@leftover) / 2
SELECT @ tmpstr = @ leftover + substring (@ list, @ textpos, @ chunklen)
SELECT @textpos = @textpos + @chunklen

SELECT @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0
BEGIN
SELECT @ tmpval = left (@ tmpstr, charindex (@ delimiter, @ tmpstr) – 1)
SELECT @tmpval = ltrim(rtrim(@tmpval))
INSERT #strings(str) VALUES (@tmpval)
SELECT @ tmpstr = substring (@ tmpstr, @ pos + 1, len (@ tmpstr))
SELECT @pos = charindex(@delimiter, @tmpstr)
END

SELECT @leftover = @tmpstr
END

INSERT #strings(str) VALUES(ltrim(rtrim(@leftover)))


Ось приклад використання цієї процедури:


CREATE PROCEDURE get_company_names_iterproc @ customerids nvarchar (2000) AS
CREATE TABLE #strings (str nchar(10) NOT NULL)
EXEC charlist_to_table_sp @customerids
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN #strings s ON C.CustomerID = s.str
go
EXEC get_company_names_iterproc “ALFKI, BONAP, CACTU, FRANK”


Функція, що розбирає список string елементів, повертала таблицю з двох полів – varchar і nvarchar. У випадку з процедурою немає вагомої причини робити теж саме. Користувач процедури сам визначає тип даних, з якими він працює. З тієї ж причини процедура не створює поле listpos. Якщо Ви хочете знати номер елемента в списку, то Ви можете додати поле IDENTITY в таблицю.


У попередніх версіях цієї статті я пропонував Вам передавати ім'я тимчасової таблиці як параметер і потім використовувати динамічний SQL для додавання даних в таблицю. Однак, це був поганий рада. При тестуванні я виявив, що витрати на виконання кожного динамічного запиту на INSERT настільки великі, що не можуть бути прийнятними.


Є ще одна потенційна проблема продуктивності у запропонованому вище методі. Все залежить від того, як Ви використовуєте його. Зазвичай при кожному виклику створюється нова тимчасова таблиця. У цьому випадку процедура charlist_to_table_sp буде перекомпілювати при кожному виклику. У більшості випадків така перекомпіляція не буде проблемою. Фактично, при тестуванні я з'ясував, що ця процедура трохи швидше відповідної функції з перетворення рядка в таблицю, незважаючи на перекомпіляцію. Однак, в завантаженою системі при масових виклики Ви можете зіткнутися з блокуваннями компіляції, як показано в KB article 263889.


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


CREATE TABLE stringarray (spid int NOT NULL,
listpos int NOT NULL,
str varchar(4000) NOT NULL,
nstr nvarchar(2000) NOT NULL,
CONSTRAINT pk_array PRIMARY KEY (spid, listpos))


Для заповнення поля spid Ви можете використовувати глобальну змінну @ @ spid – ідентифікатор з'єднання. Заповнює таку таблицю процедура може в одній з перших рядків містити запит:


   DELETE stringarray WHERE spid = @@spid


і користувач повинен пам'ятати про необхідність використовувати поле spid в запитах. Наприклад:


CREATE PROCEDURE get_company_names_spid @ customerids nvarchar (2000) AS
EXEC charlist_to_table_spid @customerids
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN stringarray s ON C.CustomerID = s.nstr
WHERE s.spid = @@spid
DELETE stringarray WHERE spid = @ @ spid – Власник.
go
EXEC get_company_names_spid “ALFKI, BONAP, CACTU, FRANK”


Ще однією альтернативою може бути створення тимчасової таблиці самої процедури. Користувач для отримання результату може в цьому випадку скористатися конструкцією INSERT EXEC. Більш докладно з такою методикою, а також її недоліками, можна ознайомитися в моїй статті How to share data between stored procedures.


12.2. Екстравагантна процедура для списку integer значень


Методику, запропоновану в попередньому розділі, можна з успіхом застосувати і для списку integer елементів. Але те, що запропоновано нижче, не є точною копією функції iter_intlist_to_table, тому що щоб уникнути помилок перетворення типів, тут робиться перевірка на те, чи є елементи списку в дійсності числами. І для більшої ваги процедура розрізняє і те, якого знака числа, наприклад, +98 або -83. Якщо список містить елементи не є числами, то процедура виводить попередження. Процедура заповнює тимчасову таблицю, в якій є поле listpos. Значення у цьому полі повідомить нам про пропущених елементах списку, не пройшли перевірку.


CREATE PROCEDURE intlist_to_table_sp @list ntext AS

DECLARE @pos int,
@textpos int,
@listpos int,
@chunklen smallint,
@str nvarchar(4000),
@tmpstr nvarchar(4000),
@leftover nvarchar(4000)

SET NOCOUNT ON

SELECT @textpos = 1, @listpos = 1, @leftover = “”
WHILE @textpos <= datalength(@list) / 2
BEGIN
SELECT @chunklen = 4000 – datalength(@leftover) / 2
SELECT @ tmpstr = ltrim (@ leftover + substring (@ list, @ textpos, @ chunklen))
SELECT @textpos = @textpos + @chunklen

SELECT @pos = charindex(” “, @tmpstr)
WHILE @pos > 0
BEGIN
SELECT @ str = rtrim (ltrim (substring (@ tmpstr, 1, @ pos – 1)))
EXEC insert_str_to_number @str, @listpos
SELECT @listpos = @listpos + 1
SELECT @ tmpstr = ltrim (substring (@ tmpstr, @ pos + 1, len (@ tmpstr)))
SELECT @pos = charindex(” “, @tmpstr)
END

SELECT @leftover = @tmpstr
END

IF ltrim(rtrim(@leftover)) <> “”
EXEC insert_str_to_number @leftover, @listpos
go

— This is a sub-procedure to intlist_to_table_sp
CREATE PROCEDURE insert_str_to_number @ str nvarchar (200),
@listpos int AS

DECLARE @number int,
@orgstr nvarchar(200),
@sign smallint,
@decimal decimal(10, 0)

SELECT @orgstr = @str

IF substring(@str, 1, 1) IN (“-“, “+”)
BEGIN
SELECT @sign = CASE substring(@str, 1, 1)
WHEN “-” THEN -1
WHEN “+” THEN 1
END
SELECT @str = substring(@str, 2, len(@str))
END
ELSE
SELECT @sign = 1

IF @str LIKE “%[0-9]%” AND @str NOT LIKE “%[^0-9]%”
BEGIN
IF len(@str) <= 9
SELECT @number = convert(int, @str)
ELSE IF len(@str) = 10
BEGIN
SELECT @decimal = convert(decimal(10, 0), @str)
IF @decimal <= convert(int, 0x7FFFFFFF)
SELECT @number = @decimal
END
END

IF @number IS NOT NULL
INSERT # numbers (listpos, number) VALUES (@ listpos, @ sign * @ number)
ELSE
RAISERROR ("Warning: at position% d, the string"% s "is not an legal integer",
10, -1, @listpos, @orgstr)
go


Ось приклад використання процедури:


CREATE PROCEDURE get_product_names_iterproc @ ids varchar (50) AS
CREATE TABLE #numbers (listpos int NOT NULL,
number int NOT NULL)
EXEC intlist_to_table_sp @ids
SELECT P.ProductID, P.ProductName
FROM Northwind..Products P
JOIN #numbers n ON P.ProductID = n.number
go
EXEC get_product_names_iterproc “9 12 27 37”


Перевірка елемента списку відбувається в подпроцедуре insert_str_to_number. У більшості випадків достатньо бидет перевіряти лише такі умови


  @str NOT LIKE “%[^0-9]%” AND len(@str) BETWEEN 1 AND 9


тобто перевіряти, що @ str містить тільки цифри і їх кількість не перевищує 9 (тому що 10-ти значні числа ми інтерпретуємо як числа зі знаком).


Ви можете здогадатися, що така екстравагантність впливає на продуктивність, і в дійсності ця процедура працює на 50% і більше повільніше, ніж відповідна функція. Тим не менше, для багатьох випадків, такий час виконання є прийнятним.


І ще одне зауваження про виведення попередження за допомогою RAISERROR: в ADO це попередження дуже важко або взагалі неможливо перехопити в клієнтському прил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*