Отправляет email-рассылки с помощью сервиса Sendsay
  Все выпуски  

Новости сайта "Упражнения по SQL" (http://www.sql-ex.ru) 225


Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)"

Выпуск 225 от 17 января 2009 г.

Новым посетителям сайта

SQL Exercises Сайт посвящен изучению языка, с помощью которого осуществляется взаимодействие с реляционными (и не только) СУБД. Суть обучения состоит в выполнении заданий на написание запросов к учебным базам данных; при этом система контролирует правильность выполнения заданий. В настоящее время реализованы все операторы подъязыка манипуляции данными (DML), которые включают в себя оператор извлечения данных SELECT, а также операторы модификации данных - INSERT, DELETE и UPDATE.

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

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

Имеется возможность получить сертификат по SQL DML при выполнении определенного количества заданий.


Новости сайта

§ Изменения среди лидеров (решенные за неделю задачи третьего этапа):
5. GreyC (150)
9. Ozzy (147)
20. Umrikhina (143)

§ Новые лица в ТОР 100 и вернувшиеся туда:
100. Romul_T (задач 124, время 88.012)

§ Продвинулись в рейтинге:
66. Scorpion (131, 104.193)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
101. Pavel_yu (124, 215.975)
129. Vasilko (133, 8.619)
135. nadush (117, 190.449)
178. vitaliiS (111, 88.798)

§ На этой неделе сертифицированы:
Ibn M (A09042339) [BK] - г.Москва, Россия
eksodus (A09032269) [BK] - г.Новосибирск, Россия

§ Число подписчиков - 3844

Число участников рейтинга - 20827

Число участников второго этапа - 1680

Число участников третьего этапа - 45

Сертифицировано на сайте - 347

Лучшие результаты (ТОР 20)

No Person Number of
Sel_ex
Last_Sel Number of
DML_ex
Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Креславский О.М. (Arcan) 152 152 21 370 707 59.893 41.017 46 06 Jan 2009 16 Jan 2009
2 Карасёва Н.В. (vlksm) 152 152 21 370 975 79.787 50.723 46 05 Jan 2009 16 Jan 2009
3 Никотин В.М. (@Nikotin) 150 150 21 364 108 8.371 3.751 40 13 Dec 2008 16 Jan 2009
4 Сальников С.А. ($erges) 150 150 21 364 291 3.487 3.824 40 13 Dec 2008 16 Jan 2009
5 Сенкевич С.В. (GreyC) 150 150 21 364 342 52.025 21.268 40 10 Jan 2009 16 Jan 2009
6 Печатнов В.В. (pvv) 146 149 21 352 357 30.849 17.490 36 10 Oct 2008 16 Jan 2009
7 Селезнёв А.С. (Артём С.) 145 149 21 349 322 38.500 29.235 36 25 Sep 2008 16 Jan 2009
8 Муллаханов Р.Х. (rem) 149 152 21 359 483 14.427 20.056 35 05 Jan 2009 16 Jan 2009
9 Зотов П.Г. (Ozzy) 142 147 21 343 307 88.048 105.763 31 11 Jan 2009 16 Jan 2009
10 Шиндин А.В. (AlShin) 147 148 21 354 79 20.458 7.203 30 05 Jan 2009 15 Jan 2009
11 Мурашкин И.В. (lepton) 142 150 21 342 995 47.797 37.312 30 12 Dec 2008 18 Dec 2008
12 Держальцев В.А. (MadVet) 137 146 21 333 1257 60.783 28.482 28 24 Sep 2008 30 Dec 2008
13 Любченко В.А. (IAS56) 136 146 21 332 615 403.343 373.617 28 11 May 2008 01 Dec 2008
14 Голубин Р.С. (Roman S. Golubin) 140 145 21 335 1122 93.042 58.822 25 13 Sep 2008 06 Dec 2008
15 Дроздков А.Н. (anddros) 145 151 21 348 217 4.593 1.153 24 30 Dec 2008 16 Jan 2009
16 Nikolaenko A.V. (Shadow77) 142 147 21 339 436 77.451 14.010 23 22 Oct 2008 11 Dec 2008
17 Солдатенков Ю.С. (SolYUtor) 138 146 21 331 819 22.615 6.102 20 14 Aug 2008 23 Oct 2008
18 Белогурова К. (Katy_Ekb) 133 143 21 321 552 10.666 4.673 18 27 Nov 2008 09 Dec 2008
19 Егоров А.Б. (ABEgorov) 137 144 21 329 180 12.897 8.815 18 03 Aug 2008 12 Aug 2008
20 Умрихина Е.В. (Umrikhina) 143 143 21 342 265 11.700 14.058 18 14 Jan 2009 16 Jan 2009

Лучшие результаты за неделю

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Ичалов В. (a66at) 69 69 126 34 160 565 16 Jan 2009
2 >Rakovich A. (Lohmatyi) 48 66 97 34 131 648 16 Jan 2009
3 Yakovlev A. (Toike) 48 48 90 34 124 1074 13 Jan 2009
4 Romanov (johnrom) 48 48 90 34 124 1075 13 Jan 2009
5 Мусин Р.С. (Exodus) 46 46 87 34 121 1139 14 Jan 2009
6 Popov S.S. (Sereger) 43 43 81 34 115 1239 14 Jan 2009
7 Кадиев М.А. (МаксимК) 48 48 90 11 101 1613 13 Jan 2009
8 >Колосок А.В. (scythian) 32 58 67 32 99 879 16 Jan 2009
9 Козловцев В.В. (Selenit) 41 53 84 9 93 1471 14 Jan 2009
10 Рассказов Д.И. (Scorpio_Rising) 40 40 68 19 87 2053 13 Jan 2009
11 Korneev (Korneev_ant) 44 44 83 0 83 2212 15 Jan 2009
12 Дмитренко (fara89) 34 34 61 21 82 2236 13 Jan 2009
13 Липало Н.Н. (_nikich77) 25 39 59 19 78 1830 15 Jan 2009
14 Тарасов И.А. (Гарсон) 26 26 42 34 76 2549 13 Jan 2009
15 >Дорошенко И. (drnk) 40 40 76 0 76 2554 16 Jan 2009
16 Лисицына А.А. (Глинка) 26 26 42 32 74 2692 13 Jan 2009
17 васин С.С. (serega1202) 35 35 64 9 73 2751 13 Jan 2009
18 >Petrov I. (ivp) 32 47 71 0 71 1986 16 Jan 2009
19 Жданова Ю. (Julencia-mt) 33 33 62 9 71 2901 13 Jan 2009
20 Старков К.Э. (кирюша) 21 21 34 34 68 3107 12 Jan 2009
21 Пьянков А.О. (V1nnie) 31 31 54 13 67 3174 13 Jan 2009
22 Кузев В.А. (veZuk) 27 27 43 23 66 3249 14 Jan 2009
23 Смирнов А.А. (Makapoh) 24 24 42 21 63 3499 13 Jan 2009
24 Степанов (Spirans) 28 28 44 17 61 3678 14 Jan 2009
25 Анисимов И.И. (Brutalis) 26 26 48 13 61 3679 14 Jan 2009
26 Беседин Д. (Донер) 24 24 39 21 60 3751 14 Jan 2009

Изучаем SQL

Массивы и списки в SQL Server 2005 (начало в вып.217-224)

Erland Sommarskog (оригинал: Arrays and Lists in SQL Server 2005 )
Перевод: Моисеенко С.И.

Список строк

Ниже приведена аналогичная функция, но теперь она возвращает таблицу строк:

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

BEGIN
   DECLARE @endpos   int,
           @startpos 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 @startpos = 0
      SET @endpos = charindex(@delimiter COLLATE Slovenian_BIN2, @tmpstr)

      WHILE @endpos > 0
      BEGIN
         SET @tmpval = ltrim(rtrim(substring(@tmpstr, @startpos + 1,
                                             @endpos - @startpos - 1)))
         INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
         SET @startpos = @endpos
         SET @endpos = charindex(@delimiter COLLATE Slovenian_BIN2,
                                 @tmpstr, @startpos + 1)
      END

      SET @leftover = right(@tmpstr, datalength(@tmpstr) / 2 - @startpos)
   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_tbl(@customers, DEFAULT) s
         ON   C.CustomerID = s.nstr
    go
    EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'

Эта функция подобна iter_intlist_to_tbl. Я добавил параметр, чтобы задавать разделитель. Когда Вы вызываете функцию пользователя в SQL Server, Вы не можете не указывать параметр, даже если он имеет значение по умолчанию, но Вы можете указать ключевое слово DEFAULT, чтобы использовать значение по умолчанию. Отметьте также, что я отсекаю концевые пробелы, которые находятся рядом с разделителями, но пробелы внутри списка элементов остаются.

Я люблю обращать внимание на использование datalength. Есть две системных функции в T-SQL, которые возвращают длину строки: len возвращает число символов и не учитывает концевые пробелы. datalength возвращает число байтов и учитывает концевые пробелы. Здесь я использую datalength, поскольку нет причин игнорировать концевые пробелы в кусках - они вполне могут быть в середине элемента списка.

Использование CLR

Введение в CLR

В SQL 2005 появилась возможность создавать хранимые процедуры, функции и т.д на языках .Net, таких как C# и Visual Basic .Net, или любом другом языке, который поддерживает технологию Common Language Runtime (CLR).

Если Вы никогда раньше не работали с CLR, Вы можете решить, что этот метод выше вашего понимания и предпочесть использование методов чистого SQL. С другой стороны, если являетесь опытным программистом на C# или VB, то конечно оцените этот метод. Как и итерационный метод, он очень легко модифицируется, что дает возможность адаптировать его к специальным форматам на входе.

Есть много способов, когда Вы можете злоупотреблять CLR и использовать его, когда не следует, но функция список-в-строку - основной пример того, для чего хорош CLR: операции, которые не выполняют никакого доступа к данным, но реализуют довольно сложные манипуляции со строками или числами. Тому есть две причины: 1) CLR дает Вам намного более богатый набор функций, в первую очередь, регулярные выражения. 2) Языки CLR компилируются, в то время как T-SQL интерпретируется, что приводит к намного лучшей производительности при работе с CLR. В царстве табличнозначных функций есть другой фактор, который улучшает производительность: выход из табличнозначной CLR-функции не записывается ни в какое промежуточное хранилище, а строки передаются в остальную часть запроса по мере их получения. Поэтому в этом смысле они являются встроенными функциями, но в отличие от собственно встроенных функций T-SQL оптимизатор понятия не имеет, что они произведут, по этой причине я называю их непрозрачными встроенными функциями.

Отдельной причудой функций CLR является то, что они не могут возвратить данные типа varchar; Вы можете вернуть только nvarchar. Это означает, что когда Вы работаете со списком строк, следует держать в голове необходимость преобразовать выходные данные к типу varchar, когда Вы выполняете соединение по столбцам таблиц с типом varchar, что я обсуждал в разделе "varchar или nvarchar".

По умолчанию в поставке SQL 2005 CLR отключены. Вы можете включить их из Surface Area Configuration или выполнив скрипт

EXEC sp_configure 'CLR enabled', 1
RECONFIGURE

от окне запроса.

В последующем я попытаюсь дать ознакомительный курс того, как написать табличнозначную функцию в CLR. Опытные .Net программисты найдут неточности в деталях - я должен буду признать, что единственные табличнозначные функции CLR, которые я написал, - это те, которые используются для данной статьи. (Я бы мог отослать Вас к Books Online, но я нахожу статью, посвященную табличнозначным функциям CLR в Books Online слишком краткой.) Для краткости изложения я включаю лишь примеры на C#.

(Продолжение следует...)

Полезная информация

§ Приглашаем вас посетить новый проект - Интерактивный учебник по SQL.
   Ресурс позиционируется как "справочное обеспечение" для сайта SQL-EX.RU, но может использоваться и независимо от него.

§ Онлайновый выпуск рассылки можно почитать на сайте.

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

§ Хотите поддержать проект? Вот инструкция по применению. :-)

Контакты

По всем вопросам, связанным с функционированием сайта, проблемами при решении упражнений, идеями вы можете обращаться к Сергею И.Моисеенко msi77[@]yandex.ru. Вы также можете предложить свои задачи для публикации на сайте.

Подписка Subscribe.Ru
Новости сайта "Упражнения по SQL"

В избранное