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

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


Новости сайта "Упражнения по SQL (http://www.sql-ex.ru)" Выпуск 179 (01 марта 2008 г.)

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

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

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

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

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


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

§ Внес незначительные уточнения в формулировку задачи 52 в ответ на замечания SoftDeveloper.

§ Написал FAQ на 51 задачу, проблемы с решением которой, на мой взгляд, связаны с невнимательным прочтением формулировки.

§ Исправил баг, связанный с исчезновением плюсов (+) из кода при редактировании сообщения на форуме.
Пишите, если что-то еще будет не так.

§ Дописал в справку особенности реализации в MS SQL функции LEN(), которая не учитывает концевые пробелы. Незнание этой особенности вызывает проблемы при решении некоторых задач на сайте, в то время как справка должна давать информацию, необходимую для решения задач.
Буду стараться и в дальнейшем поддерживать справку в актуальном состоянии.

§ Изменения среди лидеров (решенные задачи третьего этапа):
22. Артём С. (139, 142)

§ Продвинулись в рейтинге:
29. AlexFJ (задач 137, время 160.759)
32. ABEgorov (136, 5.555
35. wasp (136, 123.191)
36. Bulldozer (136, 184.320)
43. ikhomeriki (134, 4.160)
45. modicus (135, 8.151)
59. Runaway (133, 15.230)
62. elka (132, 72.971)
78. serge77777 (127, 19.852)
79. Чумазик (127, 79.422)
85. lexaNRJ (126, 80.987)
89. paul (125, 5.958)
98. Lexus (123, 37.533)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
116. FanOfBeer (120, 79.254)
126. xuser (117, 1.176) - первое место на промежуточном этапе
127. mz (117, 25.885)
128. Gendalf (117, 91.130)
129. nadush (117, 139.311)
138. comrade (115, 142.084)
156. Cергей L (110, 20.856)

§ На этой неделе сертифицированы:
Лори (B08019312) [AR] - г. Новокузнецк, Россия

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

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

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

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

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

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

No Person Number of
Sel_ex
Last_Sel Number of
DML_ex
Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Печатнов В.В. (pvv) 146 11 21 357 127 19.165 6.326 28 23 Feb 2008 29 Feb 2008
2 Креславский О.М. (Arcan) 146 11 21 357 389 22.436 12.553 28 23 Feb 2008 29 Feb 2008
3 Карасёва Н.В. (vlksm) 145 11 21 354 659 53.277 26.801 25 24 Feb 2008 29 Feb 2008
4 Голубин Р.С. (Roman S. Golubin) 145 11 21 354 919 92.541 58.822 25 23 Feb 2008 29 Feb 2008
5 Любченко В.А. (IAS56) 144 134 21 350 542 296.922 130.012 21 28 Feb 2008 29 Feb 2008
6 Белогурова К. (Katy_Ekb) 142 143 21 346 267 10.733 4.673 18 16 Feb 2008 27 Feb 2008
7 Держальцев В.А. (MadVet) 143 11 21 347 1046 48.873 16.561 18 26 Feb 2008 29 Feb 2008
8 Войнов П.Е. (pаparome) 143 11 21 346 916 3.013 .213 17 26 Feb 2008 26 Feb 2008
9 Северюхин Ю.А. (Venser) 140 142 21 339 335 4.930 .655 14 01 Feb 2008 04 Feb 2008
10 Тарасов Д.Б. (Gavrila) 141 11 21 340 914 23.390 2.501 14 26 Feb 2008 29 Feb 2008
11 Солдатенков Ю.С. (SolYUtor) 139 142 21 338 490 17.852 2.695 14 20 Sep 2007 28 Feb 2008
12 Шептунов П.П. (Dzen) 139 142 21 338 279 8.130 3.499 14 02 Oct 2007 15 Nov 2007
13 Мурашкин И.В. (lepton) 142 11 21 343 705 15.853 5.539 14 26 Feb 2008 26 Feb 2008
14 Мальцев А.В. (Палкин) 139 142 21 338 422 48.788 7.690 14 13 Oct 2007 20 Jan 2008
15 Васьков Е.В. (Johan) 139 142 21 338 493 14.323 12.767 14 24 Nov 2007 11 Feb 2008
16 Бураков С.Г. (burakov58) 139 142 21 338 974 51.701 19.814 14 30 Sep 2007 09 Nov 2007
17 Валуев Д.И. (Fiolent) 142 11 21 343 1638 188.425 131.545 14 28 Feb 2008 29 Feb 2008
18 Агапов В. (KERBEROS) 132 141 20 322 89 6.140 1.262 11 20 Nov 2006 27 Jul 2007
19 Кувалкин К.С. (Cyrilus) 140 11 21 337 1190 12.779 2.519 11 27 Feb 2008 29 Feb 2008
20 Зверев Д.Л. (dimzv) 135 141 21 330 1503 9.352 4.938 11 16 Dec 2007 16 Dec 2007

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 >Сальников С.А. ($erges) 71 71 138 34 172 426 29 Feb 2008
2 >Поляков С.Н. (PolSer) 45 45 83 34 117 1184 29 Feb 2008
3 Кемаев С.В. (S-layer) 38 50 80 14 94 1198 29 Feb 2008
4 Силантьев А. (sav) 30 57 59 34 93 729 29 Feb 2008
5 Зубрицкий Ю.Г. (shock) 34 71 66 25 91 427 28 Feb 2008
6 Алексеев А.О. (Toxa Moskit) 43 71 85 0 85 804 29 Feb 2008
7 >Gawin (G@wcio) 43 43 79 0 79 2183 29 Feb 2008
8 >Саввичев Д.М. (dms) 31 31 59 17 76 2288 29 Feb 2008
9 >карпунин А.С. (llllllll) 38 38 73 0 73 2425 29 Feb 2008
10 >Рыжов И. (Goriz) 27 45 59 11 70 1217 29 Feb 2008
11 Морозов (_moi) 34 34 65 0 65 2837 27 Feb 2008
12 >Петюшко А.А. (AlexPet) 18 68 30 34 64 501 29 Feb 2008
13 >Bezwierzchny T. (TomGolab) 20 50 39 23 62 944 29 Feb 2008
14 Михайлов И.Е. (IgorMZ) 28 57 53 9 62 1166 29 Feb 2008
15 >Бирюкова А.В. (bav21) 34 34 59 3 62 3022 29 Feb 2008
16 >qSTOFF В. (qSTOFF) 30 54 59 1 60 1423 29 Feb 2008
17 >pkhako (pkhako1) 28 28 52 1 53 3713 29 Feb 2008
18 >казанцев К.В. (@@@@@@) 28 28 52 0 52 3817 29 Feb 2008
19 Попеску Н.Н. (niku) 14 42 23 25 48 1305 29 Feb 2008
20 >Забаровский М.А. (Tamper) 20 35 47 0 47 2800 29 Feb 2008
21 Осташенко А. (annaeo) 26 26 45 0 45 4577 28 Feb 2008
22 >Мухаметов Р. (Xuk) 7 64 10 34 44 639 28 Feb 2008
23 Михайлов Ф.И. (fox53) 17 41 33 11 44 1354 29 Feb 2008
24 >Сидоров В.А. (m00nru) 26 26 39 5 44 4619 29 Feb 2008
25 Башкирова Т. (gnomik) 21 59 41 0 41 1283 27 Feb 2008
26 >Марченко Л.В. (MarLa) 14 42 33 8 41 1732 29 Feb 2008
27 >Солонин М.С. (Smike) 6 55 10 30 40 823 29 Feb 2008

Изучаем SQL

А знаете ли вы? Вложенные транзакции

Kalen Delaney (оригинал: Did you Know? Nesting Transactions )
Перевод Моисеенко С.И.

Transact-SQL обеспечивает три различных метода управления транзакциями: автоматическая фиксация транзакций (autocommit transactions), явные транзакции (explicit transactions) и неявные транзакции (implicit transactions).

Автоматически фиксируемая транзакция - любая отдельная операция INSERT, UPDATE или DELETE, независимо от того, сколько строк было затронуто. Как только оператор выполнен, изменения фиксируются, что означает, что все соответствующие строки в журнале, включая запись в журнале COMMIT TRAN, записываются в журнал транзакций на диске (в ваш .ldf файл).

Если Вы хотите иметь возможность объединить несколько операторов в единую транзакцию или если Вы хотите откатывать транзакцию в зависимости от некоторого условия, Вы можете использовать явное управление транзакциями с помощью операторов управления BEGIN TRANSACTION (начало транзакции или BEGIN TRAN) и COMMIT TRANSACTION (фиксация транзакции или COMMIT TRAN). Например, чтобы обновить обе таблицы T1 и T2 или ни одну из них, Вы можете использовать следующий псевдокод:

 

BEGIN TRAN
    UPDATE T1 ...
    UPDATE T2 ...
COMMIT TRAN

 

Если в системе наступает сбой после обновления T1, транзакция не будет зафиксирована. Она будет фиксироваться только в том случае, если оба обновления прошли успешно.

Помимо этого, при использовании явных транзакций вы можете решить откатить транзакцию после проверки состояния ошибки или числа обработанных строк:

 

BEGIN TRAN
    UPDATE T1 ...
    IF @@error > 0 ROLLBACK TRAN
        ELSE COMMIT TRAN

 

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

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

 

BEGIN TRAN
UPDATE T1
BEGIN TRAN
UPDATE T2
BEGIN TRAN
UPDATE T3
COMMIT TRAN

 

"Что будет зафиксировано?" - спрашивается в задачнике.

Обычно я получаю несколько ответов, среди которых: все обновляется, ничего не обновляется, обновляется Т1, обновляется Т3 (никто при этом не предлагает вариант обновления Т2).

Оказывается, что ни один из приведенных ответов не является верным.

SQL Server поддерживает внутренний счетчик, подсчитывающий, сколько раз был выполнен BEGIN TRAN, и вам необходимо выполнить такое же число COMMIT TRAN, чтобы реальная фиксация имела место. Каждый BEGIN TRAN увеличивает значение счетчика, а каждый COMMIT TRAN уменьшает его. Только когда значение счетчика станет 0, записи журнала будут сброшены на диск, и транзакция будет завершена на самом деле. Вы можете посмотреть значение счетчика с помощью функции @@trancount. С помощью этой функции вы можете увидеть, что когда вы выполняете откат - ROLLBACK, счетчик сразу же сбрасывается в 0.

 

BEGIN TRAN
SELECT @@trancount
BEGIN TRAN
SELECT @@trancount
BEGIN TRAN
SELECT @@trancount

ROLLBACK TRAN
SELECT @@trancount

 

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

Название "неявные транзакции" может слегка запутать. Только BEGIN TRAN может быть неявным; COMMIT TRAN же всегда явный. Это означает, что ни одна транзакция не будет зафиксирована, пока не встретится COMMIT TRAN. Транзакции всегда буду начинаться с выполнения INSERT, UPDATE, DELETE или SELECT (а также с некоторых других операторов, например, CREATE и DROP, о чем вы можете почитать в Books Online), если нет уже открытой транзакции.

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

 

SET IMPLICIT_TRANSACTIONS ON;

 

или с помощью установки второго бита конфигурационной опции user_options в значение 1. Вы можете прочитать о sp_configure 'user options' в Books Online. Хотя я не рекомендую использование неявных транзакций, если вы собираетесь их использовать, я бы посоветовала, чтобы их использовали все сессии, то сделать это посредством установки конфигурационной опции. Если некоторые сессии будут использовать неявные транзакции и требовать COMMIT для каждого отдельного оператора INSERT, UPDATE и DELETE, а другие сессии не будут требовать закрытия, это означает напрашиваться на неприятности.

Итак, что случится, если вы используете BEGIN TRAN, когда находитесь в режиме неявных транзакций? Я всегда думала, что BEGIN TRAN должен игнорироваться, однако выяснилось, что я ошибаюсь. Подумайте о том, что должно произойти, если вы имеете нормальный неявный режим транзакций, в следующем случае:

 

UPDATE T1
    SET ...
COMMIT TRAN

 

А затем предположим, что кто-то выполняет пакет в пределах явной транзакции и оформляет ее с помощью BEGIN/COMMIT:

 

BEGIN TRAN
UPDATE T1
    SET ...
COMMIT TRAN
COMMIT TRAN

 

Если BEGIN TRAN игнорируется, первый COMMIT установил бы @@trancount в 0, а второй дал бы ошибку. Вы можете проверить это поведение, просто выполнив COMMIT TRAN отдельно. SQL Server генерирует следующее сообщение:

Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
(запрос COMMIT TRANSACTION не имеет соответствующего BEGIN TRANSACTION)

Итак, если имеете режим неявных транзакций, выполнение BEGIN TRAN (чего вы фактически не должны делать) установит @@trancount в значение 2. Это на самом деле удивило меня, когда я заметила это, но затем я осознала значение такого поведения. Когда вы выполняете любую операцию DML, уже находясь в пределах транзакции, @@trancount не будет увеличиваться. Когда вы выполняете COMMIT для вашего оператора DML, @@trancount будет уменьшен на 1. Когда же вы выполняете COMMIT TRAN, соответствующий BEGIN TRAN, @@trancount уменьшится до 0, и транзакция действительно будет зафиксирована. Ниже полный скрипт, иллюстрирующий поведение @@trancount с неявными транзакциями:

 

SET IMPLICIT_TRANSACTIONS OFF;
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE name = 'T1' AND type = 'U')
DROP TABLE T1;
GO
CREATE TABLE T1 (col1 int);
GO
INSERT INTO T1 SELECT 1;
GO
SET IMPLICIT_TRANSACTIONS ON;
GO
BEGIN TRAN;
SELECT @@trancount;
UPDATE T1
SET col1 = col1 + 1;
COMMIT TRAN;
SELECT @@trancount;
COMMIT TRAN;
SELECT @@trancount;
GO
SET IMPLICIT_TRANSACTIONS OFF;
GO

 

13-08-2007

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

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

§ Желающих поспособствовать популяризации сайта прошу проголосовать/поставить закладку в социальных сетях:
del.icio.us
dzone.com
Digg.com
Reddit.com
stumbleupon.com

Контакты

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

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

В избранное