SQL *

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

Постановка задачи

Дана база данных, в ней 3 таблицы следующего вида:




table1: user_id (INT(5), PRIMARY KEY), username (VARCHAR(50), INDEX)
table2: phone_id (INT(5), PRIMARY KEY), user_id (INT(5), INDEX), phone_number (INT(10), INDEX)
table3: room_id (INT(5), PRIMARY KEY), phone_id (INT(5), INDEX), room_number(INT(4) INDEX)

Необходимо выбрать номер комнаты в которой сидит пользователь с ником qux…

Действуют они везде – хоть MySQL, хоть Oracle хоть самописная БД. Чем умнее БД – тем больше она старается оптимизировать сама, но лучше ей помочь

1. Разделяй и властвуй, а попросту кластеризация БД – все данные одного типа можно еще разбить на кластеры – отдельные таблицы, в каждую таблицу попадают записи, которые удовлетворяют какому-то простейшему правилу, например в таблицу с индексом I попадают данные у которых ID%N==I, где N – кол-во кластеров. Таким образом очень просто и эффективно делим те данные, которые не надо считывать последовательно – например разбиваем все слова на 100-200-миллион блоков, в каждом блоке только слова у которых ID%N==I. В качестве примера в большой системе, типа социальной сети, можно поделить все данные по признаку принадлежности одному пользователю — например все фото разместить в N таблиц, информация о фото помещается в таблицу K=USER_ID%N

2. Условно — работа с диском. Всегда пиши (вставляй) последовательно, кэшируй и буферизуй запись, читать старайся подряд от начала до конца. Ускорение записи может быть просто фантастическое – много порядков, просто от того что Вы правильно используете запись зная как работает Ваш (или производителя) алгоритм записи на диск. Данные почти всегда можно отсортировать до записи – в памяти ли, разные файлы ли с кускам текста – всегда можно построить индекс или простейший массив, который отсортирован по ID данных и читать-писать их в порядке как в индексе. Как один из вариантов – всегда можно придумать более оптимальную структуру хранения данных. К примеру когда надо вставить кусок таблицы в другую таблицу делать это лучше последовательно от меньшего ID к большему, заодно отключив механизм индексации. И включив его после вставки.

Одна из наиболее интересных и захватывающих разработок от Майкрософт в технологическом плане была представлена 8 ноября 2010 года. В этот день состоялся релиз CTP 1 SQL Server 2011 (Codename Denali). CTP доступна как в х86, так и в х64. Как и ожидалось, новый сервер принес много вкусненького для всех поклонников MS SQL будь то разработчик, администратор или бизнес аналитик.

За последние несколько лет Майкрософт внедрила много интересных технологий, которые были приняты разработчиками на вооружение. Самые значительные изменения были сделаны в 2005 SQL сервере и получили дополнительное развитие в 2008 выпуске. В этой статье (заключительной) будут рассмотрены изменения и новые возможности которые произошли в новой версии SQL Server.

Если у вас возникнут проблемы при установке сервера, то рекомендую обратиться к этой статье.
Далее пойдет речь о новшествах в SQL Server Management Studio (SSMS).

В оригинальном звучании и в жизни эта возможность звучит как With Result Set. Эта штука позволяет менять имена и типы данных в возвращаемом хранимой процедурой наборе данных.

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

Для демонстрации работы будем использовать в качестве примера таблицу tbl_Test состоящую из 3 колонок.

Новое полезное дополнение для SQL Server 2011 (Denali) ­– выражение Throw. Разработчики на .Net уже догадались наверно, где и как оно будет использоваться.

Это слово может использоваться в сочетании с управляющей конструкцией Try…Catch и позволяет послать уведомление о возникновении ошибки времени исполнения. Когда возникает исключение, программа ищет ближайший по иерархии вверх блок Catch который может обработать исключение. Используя это выражение внутри блока Catch можно изменить вывод ошибки. Более того, теперь вызывать исключение можно произвольно в любом месте скрипта.

Далее рассмотрим различные способы поимки исключении, которые предоставляет SQL Server начиная с версии 2000 и до версии 2011, с указанием плюсов и минусов.

В новом SQL Server 2011 (Denali) расширяются возможности команды Order By с помощью двух долгожданных дополнительных команд:

• Offset (смещение)
• Fetch First или Fetch Next (взять первые… или взять следующие…)

Offset

Использование данной команды позволяет пропустить указанное количество строк перед тем как выводить результаты запроса. Что под этим подразумевается: Допустим, у нас есть 100 записей в таблице и нужно пропустить первые 10 строк и вывести строки с 11 по 100. Теперь это легко решается следующим запросом:

Select *
From  <SomeTable>
Order by  <SomeColumn>
Offset 10 Rows

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

Возможность, которой не удивишь нынче пользователей Oracle, DB2, PostgreSQL и множества других реляционных баз данных, наконец-то появилась и в MS SQL Server. На арене Sequence!

Sequence – генерирует последовательность чисел так же как и identity. Однако основным плюсом sequence является то, что последовательность не зависит от какой-либо конкретной таблицы и является объектом базы данных.

Рассмотрим пример скрипта написанного на SQL Server 2008. Создание простой таблицы с двумя колонками, одна из которых будет автоинкрементной.

Create Table WithOutSequence1
(
	 EmpId int identity not null primary key
	,EmpName varchar(50) not null
)

Insert into WithOutSequence1
	Select 'Violet' Union All
	Select 'Tape'

Select * from WithOutSequence1

Похожим образом создадим еще одну таблицу.

Create Table WithOutSequence2
(
	 EmpId int identity not null primary key
	,EmpName varchar(50) not null
)

Insert into WithOutSequence2
	Select 'Violet' Union All
	Select 'Tape'

Select * from WithOutSequence2

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

Общий синтаксис для команды выглядит так:

CREATE SEQUENCE [schema_name . ] sequence_name
[ AS { built_in_integer_type | user-defined_integer_type } ]
| START WITH <constant>
| INCREMENT BY <constant>
| { MINVALUE <constant> | NO MINVALUE }
| { MAXVALUE <constant> | NO MAXVALUE }
| { CYCLE | NO CYCLE }
| { CACHE [<constant> ] | NO CACHE }

Прочитал интересную и полезную статью (1) — и захотел поделиться собственным опытом. В нашей фирме за 12 лет работы с одной (своей, Oracle-ориентированной) программой у сотен клиентов накоплен богатейший материал на тему апгрейда структуры БД.

Первоначально мы предполагали, что достаточно хранить в каждой БД номер версии последнего апгрейда и накатывать скрипты инкрементально, поднимая версию до нужной. Такая методика успешно использовалась в предыдущей версии нашей программы, работавшей с СУБД Paradox. Но с СУБД Oracle все пошло не так, у каждого клиента было собственное видение, какой должна быть его БД, и рассинхронизация версий стала неизбежным злом. Привычная методика апгрейдов по версиям стала постоянно приводить к ошибкам, на которые уже никто и не обращал внимание, и рассогласование структур продолжалось несколько лет. В итоге у каждого клиента оказалась собственная, не идентичная никакой другой, структура БД, а клиентская часть программы как-то должна была с этим бороться.

В какой-то момент руководство поручило вплотную заняться проблемой апгрейдов БД ведущему программисту фирмы (мне) — человеку творческому, предпочитающему потратить больше времени, но избежать рутинной работы. И тогда был разработан инструмент, позволивший практически свести на нет различия в структурах БД у всех клиентов. Об этой технологии я и хочу рассказать мировому сообществу.

Проблемы контроля версий баз данных и миграций между версиями уже не раз поднимались как на Хабре (1, 2, 3 и др.), так и в Интернете (преимущественно, англоязычном).

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

В последнее время реляционные СУБД немного потеснены системами с альтернативными моделями данных. Отчасти это вызвано задачами повышения производительности за счет упрощения структур хранения. С другой стороны, идут поиски путей расширения выразительных средств, в том числе за счет перехода к более богатым информационным моделям. Ведь многие поняли, что повышения уровня абстракции предметной области на один порядок дает расширение сферы применения продукта в десятки раз и возможность занять многие смежные ниши, порой увеличивая количество клиентов в сотни и тысячи раз.

Первая часть: http://habrahabr.ru/blogs/sql/119317/

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

Недавно, реализуя некоторый код доступа к данным, я столкнулся с задачей выбора последних N записей для каждой сущности. Пользователь kuda78 подсказал вместо многоэтажной выборки использовать метод SelectMany.
Исследуя, какой SQL код создает LinqToSQL, я натолкнулся на интересный SQL оператор APPLY.

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

Обеспечить безопасность приложения в данном случае поможет GreenSQL — файрволл или прокси для SQL-серверов. Сервис GreenSQL размещается между вашим приложением и SQL-сервером, принимает sql-запросы от приложения и в зависимости от степени его благонадежности либо перенаправляет их на целевой SQL-сервер, либо отвергает.

 

Марги Росс (Margy Ross) — Президент Kimball Group.

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

Студенты, посещающие лекции Kimball Group по многомерному моделированию, попросили у меня список «заповедей Kimball» для многомерного моделирования. Воздержимся от использования религиозной терминологии. Поэтому, нижеследующее, добытое методом проб и ошибок, назовём не слишком строгими рекомендациями и правилами «как-ничего-не-сломать».

Столкнулся с интересной ошибкой на загруженном Project Server 2007. Причем проявляется она при активном использовании (читай, в продакте). При разработке, отладке и тестировании данная ошибка не проявилась ни разу. Еще хуже то, что при возникновении ошибки SQL «переклинивает», и без постороннего вмешательства ошибка не исчезает. Ну и под конец — из логов крайне сложно понять причину ее возникновения. Гугл, к сожалению, не помог, что, собственно, и побудило написать об этой проблеме.
Если у вас на странице сведений о проекте «Возникла неожиданная ошибка», а в логе ошибка 7888 (SQL Timeout) — добро пожаловать под кат. Если вы интересуетесь внутренним «эго» SQL Server — тоже стоит почитать.

В свете всех последних новостей, праздников, а также наличия более актуального MS SQL Server 2008, про «малыша» 2005 многие забыли.

Днями Microsoft начала распространение Service Pack 4 для MS SQL Server 2005 посредством Windows Update.

Пакет можно было скачать ещё в середине декабря 2010, однако в свете 2008-й версии, выход 4-го пакета обновления для 2005-й версии громко не афишировался.

Пакет обновления 4 (SP4) для SQL Server 2005 можно также загрузить со страницы загрузки пакета обновления 4 (SP4) для SQL Server 2005. Для каждой платформы, поддерживаемой SQL Server 2005, доступен отдельный загружаемый пакет установки:

• Для владельцев корпоративных версий, это можно сделать на соответствующей странице Download Center.

• Для владельцев Express или Express Edition with Advanced Services, следует скачивать обновление с отдельной страницы Download Center.

Середина 2000-х годов ознаменовалась бурным ростом числа колоночных СУБД. Vertica, ParAccel, Kognito, Infobright, SAND и другие пополнили клуб колоночных СУБД и разбавили гордое одиночество Sybase IQ, основавшей его в 90х годах. В этой статье я расскажу о причинах популярности идеи по-колоночного хранения данных, принцип действия и область использования колоночных СУБД.

Начнем с того, что популярные в наше время реляционные СУБД — Oracle, SQL Server, MySQL, DB2, Postgre и др. базируются на архитектуре, отсчитывающей свою историю еще c 1970-х годов, когда радиоприемники были транзисторными, бакенбарды длинными, брюки расклешенными, а в мире СУБД преобладали иерархические и сетевые системы управления данными. Главная задача баз данных тогда заключалась в том, чтобы поддержать начавшийся в 1960-х годах массовый переход от бумажного учета хозяйственной деятельности к компьютерному. Огромное количество информации из бумажных документов переносилось в БД учетных систем, которые должны были надежно хранить все входящие сведения и, при необходимости, быстро находить их. Такие требования обусловили архитектурные особенности реляционных СУБД, оставшиеся до настоящего времени практически неизменными: построчное хранение данных, индексирование записей и журналирование операций.

Для такого популярного программного продукта, как 1С: Предприятие, разработчикам приходится обеспечивать кроссплатформенность и совместимость с большим числом приложений. В частности, с каждой новой версией добавляется поддержка всё новых СУБД.

Многие предприятия (преимущественно малые и средние) по-прежнему не торопятся переходить от версии 7.7 к 8.х. А в этом случае единственная предусмотренная разработчиками совместимая СУБД – Microsoft SQL. Вариант весьма затратный для небольшой компании: помимо лицензий на рабочие станции и MS SQL Server, требуется приобрести еще и MS Server.

Альтернативным решением может стать технология SELTA@Etersoft, разработанная питерской компанией Etersoft. SELTA используется в связке со свободной СУБД Postgre SQL и позволяет 1С 7.7 работать с SQL-базами. Продукт является коммерческим и позиционируется разработчиком как доступная (стоимость решения составляет от 15000 р.) альтернатива MS SQL Server.

Здравствуйте. Будучи администратором БД (Microsoft Dynamics NAV), возникла задача выдавать/проверять наличие роли SQL db_datareader для некоторых работников. Но в таблице список пользователей хранился в виде SID windows: S-1-5-21-3879… и записи постоянно добавлялись. Другими словами необходимо было конвертировать из S-1-5-21-38… → aapetrov.

Серверов было около 70-ти и хотелось все сделать стандартными средствами (не использовать, например psgetsid.exe).

Как и положено начал с гугла — ничего полезного не нашел. Пришлось обратиться за помощью на многоуважаемый форум. Было предложено множество вариантов, за что огромное спасибо добрым людям, но увы, решение так и не было найдено.
«Если даже ребята с sql.ru не смогли подсказать — подумал я — значит все, это конец».