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

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

Подписаться Бесплатная «Серебряная» новостная рассылка . Подписчиков 2.594 RSS
←   Август 2008   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


За последние 60 дней ни разу не выходила


Сайт рассылки: http://www.sql-ex.ru
Открыта: 15-09-2004

Автор
Serge I

Статистика

2.594 подписчиков
0 за неделю
←   Все выпуски   →

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


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

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

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

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

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

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


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

§ Уточнили формулировки 52 и 89 (английский вариант - сообщения sulo), 72 (обучающий этап - сообщение s.pereira).

§ Автор - $erges - добавил проверочные данные для задачи 15 (Select).

§ Вняв пожеланию shimanskievgeni, улучшили юзабилити страниц с упражнениями. Теперь отправлять запрос на проверку можно клавиатурно (Ctrl+Enter). Табуляцию можно осуществить с помощью Ctrl+Shift+t. Последнее улучшит форматирование запроса при публикации на форуме, обновление которого тоже на подходе.

§ С результатом (задач 138, время 27.904) к третьему этапу подошел Ozzy.
Одна задача до третьего этапа осталась TomGolab (задач 137, время 54.028).

§ Изменения среди лидеров (решенные за неделю задачи третьего этапа):
8. Shadow77 (141)
21. ABEgorov (144)

§ Новые лица в ТОР 100 и вернувшиеся туда:
78. Vendigo (128, 28.170)
87. HandKot_(126, 197.514)
91. shadon (125, 23.105)
99. rage (124, 1.532) - сохраняет вторую позицию на промежуточном этапе
100. Umrikhina (124, 2.965)

§ Продвинулись в рейтинге:
37. Gendalf (137, 134.353)
43. anddros (136, 2.613)
50. Fencer (134, 202.774)
55. Fomichev (133, 14.539)
57. Botch (131, 19.888)

§ Продвижение ближайших претендентов на попадание в ТОР 100:
103. -=Онуфрий=- (123, 133.905)
129. mz (120, 30.237)
164. Leshich (111, 99.020)

§ На этой неделе сертифицированы:
anddros (B08033795) [AR]) - г.Москва, Россия
lambda (A08032840) [BK] - г.Санкт-Петербург, Россия
Rash ST (B08027977) [AR]) - г.Тюмень, Россия
shadon (B08028458) [AR]) - г.Угледар, Украина
Goerik (A08032554) [BK] - г.Астрахань, Россия

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

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

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

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

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

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

No Person Number of
Sel_ex		 Last_Sel Number of
DML_ex		 Scores Days Days_2 Days_3 S_3 LastSolved LastVisit
1 Сальников С.А. ($erges) 147 147 21 357 118 2.842 3.359 31 23 Jun 2008 01 Aug 2008
2 Креславский О.М. (Arcan) 147 147 21 357 517 37.671 27.498 31 30 Jun 2008 01 Aug 2008
3 Карасёва Н.В. (vlksm) 147 147 21 357 778 73.340 46.383 31 22 Jun 2008 01 Aug 2008
4 Печатнов В.В. (pvv) 146 146 21 354 257 19.426 6.326 28 02 Jul 2008 01 Aug 2008
5 Держальцев В.А. (MadVet) 142 146 21 347 1128 60.815 28.482 28 18 May 2008 23 Jun 2008
6 Любченко В.А. (IAS56) 142 146 21 347 615 403.439 373.617 28 11 May 2008 08 Jun 2008
7 Голубин Р.С. (Roman S. Golubin) 144 145 21 348 1044 92.981 58.822 25 27 Jun 2008 24 Jul 2008
8 Nikolaenko A.V. (Shadow77) 145 147 21 349 349 37.317 14.010 23 27 Jul 2008 01 Aug 2008
9 Мурашкин И.В. (lepton) 144 146 21 347 839 37.289 26.815 21 09 Jul 2008 22 Jul 2008
10 Белогурова К. (Katy_Ekb) 138 143 21 335 361 10.714 4.673 18 20 May 2008 31 Jul 2008
11 Войнов П.Е. (pаparome) 143 146 21 343 1034 3.103 .213 17 23 Jun 2008 28 Jul 2008
12 Северюхин Ю.А. (Venser) 134 142 21 326 335 4.925 .655 14 01 Feb 2008 04 Feb 2008
13 Борисенков Д.В. (xuser) 137 142 21 331 92 3.217 .926 14 02 May 2008 30 May 2008
14 Мишин С.А. (CepbIu) 140 142 21 335 69 9.772 2.148 14 26 Jun 2008 01 Aug 2008
15 Тарасов Д.Б. (Gavrila) 141 142 21 337 1069 24.230 2.501 14 30 Jul 2008 01 Aug 2008
16 Солдатенков Ю.С. (SolYUtor) 135 142 21 327 703 17.844 2.695 14 20 Apr 2008 01 Aug 2008
17 Кувалкин К.С. (Cyrilus) 135 142 21 327 1207 13.037 2.782 14 15 Mar 2008 31 Jul 2008
18 Шептунов П.П. (Dzen) 133 142 21 325 279 8.120 3.499 14 02 Oct 2007 15 Nov 2007
19 Селезнёв А.С. (Артём С.) 140 142 21 335 257 15.649 4.279 14 22 Jul 2008 22 Jul 2008
20 iglbeat (iglbeat) 142 142 21 340 336 28.490 6.641 14 15 Jul 2008 01 Aug 2008

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

No surname n_sel sel_all sel_scores dml_scores scores rating last_visit
1 Балуева Е.Ю. (beu) 35 47 77 34 111 1046 31 Jul 2008
2 >Васильев (alex_v81) 43 43 81 17 98 1637 01 Aug 2008
3 >Kiselev I. (darkman666) 44 55 90 0 90 1532 01 Aug 2008
4 >Бураго Д.К. (ящик с гвоздями) 30 52 60 29 89 968 01 Aug 2008
5 >Kuzmichev K. (God Like) 26 55 52 25 77 845 01 Aug 2008
6 Решетников Е.О. (Силуэт) 40 40 76 0 76 2516 31 Jul 2008
7 >Петров И.В. (PSmith) 36 36 66 3 69 2886 01 Aug 2008
8 >Мишкин С.В. (sergeymishkin) 37 37 68 0 68 2950 01 Aug 2008
9 >Казаров С.В. (ALion) 28 41 62 3 65 2376 01 Aug 2008
10 Кощий А.В. (Toshichka) 34 34 62 0 62 3352 01 Aug 2008
11 Кабанов Е.Н. (light_ret) 26 27 49 9 58 3621 31 Jul 2008
12 >Пыхалов А. (alp) 31 31 57 0 57 3722 01 Aug 2008
13 Пятина Д. (mordaha) 31 31 57 0 57 3725 29 Jul 2008
14 Зотов Е. (Тихий) 25 25 44 11 55 3907 01 Aug 2008
15 Мирзаянова (sunclear) 27 35 54 0 54 3152 01 Aug 2008
16 >Вахромеева Т.А. (Tenyva) 29 29 49 5 54 3997 01 Aug 2008
17 Арсентьев В. (HPValeriy) 24 24 43 9 52 4194 01 Aug 2008
18 SLYUSAREV V.A. (vslyusarev) 17 49 38 12 50 1193 01 Aug 2008
19 Заживихин А. (azazh) 28 28 49 0 49 4530 31 Jul 2008
20 >Чедакин С. (PowWow) 9 55 14 34 48 846 01 Aug 2008
21 Авербух А.Б. (aka ronin) 25 25 45 0 45 4946 30 Jul 2008
22 >marat (lMn) 20 78 44 0 44 616 01 Aug 2008
23 >Колечкин С.А. (Fin da mix) 18 34 43 0 43 3241 01 Aug 2008
24 >Ivanenko I. (monax) 23 23 39 3 42 5316 01 Aug 2008

Изучаем SQL

Столбцы Identity (окончание, начало в вып.200)

Nigel Rivett (оригинал: Identity Columns )
Перевод Моисеенко С.И.

Изменение текущего значения seed

Мы видели, что текущее значение seed может быть изменено вставкой, но только в направлении шага. Лучший способ сделать это - использовать команду DBCC checkident. Чтобы установить значение seed, используется ключевое слово reseed. 

CREATE TABLE #d (i INT IDENTITY (5,2), j INT)
INSERT #d (j) SELECT 1
INSERT #d (j) SELECT 1
SELECT * FROM #d
i           j
----------- -----------
5           1
7           1

DBCC checkident(#d)
Checking identity information: current identity value '7', current column value '7'.
(Проверка информации identity : текущая значение identity  '7', текущее значение столбца '7'.)

DBCC checkident(#d, reseed, 2)

Checking identity information: current identity value '7', current column value '2'.
(Проверка информации identity : текущая значение identity  '7', текущее значение столбца '2'.)

Отметьте для себя, что впервые текущее значение seed отличается от последнего установленного значения. 

INSERT #d (j) SELECT 2
SELECT * FROM #d
i           j
----------- -----------
5           1
7           1
4           2

Мы можем также сбросить текущее значение seed к его исходному значению посредством "усечения" (команда Truncate) таблицы.

Заметим, что простое удаление (DELETE) не делает этого.

TRUNCATE TABLE #d
DBCC checkident(#d)
Checking identity information: current identity value 'NULL', current column value 'NULL'.
(Проверка информации identity : текущая значение identity  'NULL', текущее значение столбца 'NULL'.)


INSERT #d (j) SELECT 1
SELECT * FROM #d
i           j
----------- -----------
5           1

DBCC checkident(#d)
Checking identity information: current identity value '5', current column value '5'.
(Проверка информации identity : текущая значение identity  '5', текущее значение столбца '5'.)

Как узнать значение Identity

Типичная задача - как узнать значение identity для вставленной строки. Есть несколько операторов, связанных с этим

  • scope_identity
  • ident_current
  • @@identity

    scope_identity ()

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

    @@identity

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

    ident_current ('таблица')

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

    Как утверждалось ранее scope_identity () - вероятно единственная из всех этих функций, которую Вам придется использовать.

    Использование Scope_Identity ()

    Как утверждалось ранее scope_identity () возвращает последнее вставленное значение identity . 

    CREATE TABLE #t1 (i INT IDENTITY(5,1), j INT)
INSERT #t1 (j) SELECT 1
SELECT SCOPE_IDENTITY(), MAX(i) FROM #t1

--------------------------------------- -----------
5                                       5

    scope_identity также возвращает значение после отката 

    BEGIN TRAN
INSERT #t1 (j) SELECT 1
ROLLBACK TRAN
SELECT SCOPE_IDENTITY(), MAX(i) FROM #t1


--------------------------------------- -----------
6                                       5

    но значение не обновляется при сбое в результате нарушения ограничений индекса, хотя значение устанавливается 

    CREATE UNIQUE INDEX ix ON #t1 (j)
INSERT #t1 (j) SELECT 1
SELECT SCOPE_IDENTITY(), MAX(i) FROM #t1

--------------------------------------- -----------
6                                       5
INSERT #t1 (j) SELECT 2
SELECT SCOPE_IDENTITY(), MAX(i) FROM #t1


--------------------------------------- -----------
8                                       8

    Добавление столбца Identity в таблицу

    Столбец identity может быть добавлен в таблицу с помощью оператора alter table. Значения будут присваиваться столбцу согласно seed и step.

    В этом случае SCOPE_IDENTITY () не будет возвращать установленное значение. 

    CREATE TABLE #t2 (j INT)
INSERT #t2 (j) SELECT 1
INSERT #t2 (j) SELECT 1
INSERT #t2 (j) SELECT 1
INSERT #t2 (j) SELECT 1
SELECT * FROM #t2

j
-----------
1
1
1
1
ALTER TABLE #t2 ADD i INT IDENTITY (5,2)
SELECT * FROM #t2

j           i
----------- -----------
1           5
1           7
1           9
1           11

    Это может оказаться полезным для таблиц с дубликатами строк.

    Замечание. Существующий столбец не может быть преобразован в столбец identity. В этом случае Вы должны удалить существующий столбец и добавить новый.

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

    Select Into

    Столбец identity может быть включен в таблицу, создаваемую с помощью оператора select into посредством функции identity 

    SELECT *, IDENTITY(INT,1,1) AS id INTO #tbl FROM sysobjects

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

    Типы данных Identity

    Identity должен содержать только целочисленные значения, однако столбец может быть любого числового типа данных (bigint, int, tinyint, numeric, decimal). Это может оказаться полезным, когда значения превышают границу, допустимую для типа bigint.

    Поиск столбцов Identity и их свойств

    Наличие столбца identity в таблице может быть проверено следующим образом:

    SELECT OBJECTPROPERTY(OBJECT_ID(''),'TableHasIdentity')

    Оператор вернет 1, если таблица содержит столбец identity.

    Аналогично...

    SELECT COLUMNPROPERTY(OBJECT_ID(''),'','IsIdentity')

    ... покажет, имеет ли столбец свойство identity .

    Более полезный способ получить эту информацию состоит в использовании каталожного представления sys.identity_columns, которое возвращает строку для каждого столбца в базе данных, имеющего свойство identity . 

    SELECT TableName = OBJECT_NAME(OBJECT_ID) ,
       ColumnName = name ,
       OriginalSeed = seed_value ,
       Step = increment_value ,
       LastValue = last_value ,
       IsNotForReplication = is_not_for_replication
FROM sys.identity_columns

    Символьные значения в столбце Identity

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

    a1
a2
a3
b1
b2

    Такое невозможно и, вероятно, даже не желательно. Заметьте, что этот столбец фактически содержит два значения - для чего бы это понадобилось?

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

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

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

    Bulk Insert

    Если таблица имеет столбец identity, то оператор bulk insert (массовая вставка) будет постоянно сбоить, если значения identity не будут содержаться в текстовом файле. Самый легкий путь решить проблему состоит в том, чтобы создать представление на таблице, которое бы не содержало столбца identity, и выполнять массовую вставку в представление. Другой вариант состоит в том, чтобы создать форматный файл, чтобы использовать его с bulk insert. Я по возможности избегал бы этого варианта, поскольку это добавляет внешний объект и делает поддержку более сложной.

    Этим предполагается, что значения identity будут располагаться в порядке строк текстового файла, но дело обстоит не так. Часто проблема возникает с неструктурированными данными, подобными XML. В этом случае блок XML не может быть разобран, используя значения identity. Это зачастую будет работать, но чего нельзя гарантировать - особенно, если будут порождаться многочисленные потоки, - поэтому лучше не полагаться на это.

    Для работы с такими данными импортируйте их в текстовый столбец (или varchar (max) в v2005 +), чтобы затем разобрать данные. Такой вариант может оказаться весьма медленным. Вы не сможете определить разделитель столбцов или строк, и имеется интересная "особенность" в некоторых версиях sql сервера, которую следует знать: Если длина текстового файла делится на 4, bulk insert потерпит неудачу, не выдавая ошибки. Проверьте вашу версию, чтобы убедиться в наличии этой проблемы, и, если это так, Вы можете проверить длину файла и добавить фиктивный символ, если длина файла оказывается делимой на 4.

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

    Это вопрос, который часто вызывает страсти почти религиозного фанатизма, и поиск даст вам массу источников по этому поводу.

    Мне приходилось слышать, как люди утверждали, что каждая таблица в базе данных должна иметь столбец identity, и что только они должны использоваться в соединениях. Другие люди настаивают на том, что это не имеет никакого отношения к реляционной базе данных и никогда не должно использоваться. Я не подписался бы ни под одним из этих мнений, но буду использовать identity там, где это кажется разумным. Импортируя данные в промежуточные таблицы, identity может оказаться полезной для идентификации строк, которые могут содержать дубликаты. В той же самой ситуации это может быть полезным для группирования строк в соответствии с памятью, доступной для обработки. Поисковая таблица (lookup table) нуждается в ID, так почему бы не сделать его identity, если он не создается скриптом. Это может быть полезным для создания ID, например, идентификатора клиента, но следует быть осторожным в случае различных систем, использующих один и тот же ID.

    Восстановление после сбоя

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

    Резюме

  • Столбец identity имеет название, начальное значение и шаг.
  • Столбец identity не гарантирует ни уникальности, ни последовательности
  • Мы можем найти текущее значение seed и изменить его при помощи dbcc checkident
  • Следующее получаемое значение - шаг, добавленный к текущему значению.
  • Сбой при вставке может изменить текущее значение seed.
  • Явное значение может быть вставлено посредством set identity_insert и включением значения в список столбцов.
  • Явно вставляемое значение, которое превышает текущее значение seed в направлении шага, обновит текущее значение seed.
  • Команда truncate table (но не delete) обновит текущее значение seed к первоначальному значению.
  • Scope_identity () может использоваться, чтобы найти последнее размещенное значение identity.
  • Столбец identity может быть добавлен к таблице, но свойство identity в существующем столбце не может быть изменено.
  • Функция identity может использоваться для создания столбца identity на таблице, создаваемой с помощью команды select into.
  • Столбцы identity и их свойства могут быть получены с помощью sys.identity_columns.
  • Bulk insert не гарантирует формирование значений identity в порядке следования строк текстового файла.
  • Иногда легче использовать представление для операции bulk insert в таблицу, имеющую столбец identity.

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

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

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

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

    Контакты

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

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