Масиви і Списки в 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 = @@spidDELETE 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>

*

*