Free ebook: Introducing Microsoft SQL Server 2012

Introducing Microsoft SQL Server 2012 содержит 10 частей и доступна для свободной загрузки как PDF файл (288 страниц; 10.8 MB).

PART I DATABASE ADMINISTRATION (by Ross Mistry)

1. SQL Server 2012 Editions and Engine Enhancements
2. High-Availability and Disaster-Recovery Enhancements
3. Performance and Scalability
4. Security Enhancements
5. Programmability and Beyond-Relational Enhancements

PART II BUSINESS INTELLIGENCE DEVELOPMENT (by Stacia Misner)

6. Integration Services
7. Data Quality Services
8. Master Data Services
9. Analysis Services and PowerPivot
10. Reporting Services

Источник

SQL Server 2005/2008 System Views Map

Microsoft SQL Server System Views Map отображает основные системные представления и отношения между ними. Следующие версии доступны в Microsoft Download Center для свободного скачивания как PDF файлы:

• SQL Server 2008 System Views Map
• SQL Server 2005 System Views Map

Применение OUTPUT в инструкциях DML

Анализируя базы данных, я часто вижу, что возможности предложения OUTPUT в инструкциях DML не используются, а реализуются с помощью дополнительных запросов и встроенных функций SQL Server'а. Такая реализация всегда стоит дополнительных ресурсов  и отрицательно влияет на производительность приложения в целом.

OUTPUT предоставляет возможность вывода вставленных/измененных/удаленных данных в инструкциях INSERT, UPDATE, MERGE, DELETE. Строку, обрабатываемую такими инструкциями, можно целиком или частично вставить в табличную переменную (Пример 1), другую таблицу (Пример 2), просто вернуть клиентскому приложению или параллельно выполнить и то, и другое (Пример 3).

Ниже приведены несколько примеров использования предложения OUTPUT в инструкциях INSERT и DELETE.

use tempdb
go

--
-- Тестовая таблица
--
create table t1
(
    id int identity,
    col1 nvarchar(55) null
)
go

--
-- Пример 1: Возвращает идентификатор вставленной строки
--
insert into t1(col1)
output inserted.id
values('testdata 1')
go

--
-- Пример 2: Вставляет данные в таблицу t2
--
create table t2
(
    dml_time datetime not null,
    dml_statement sysname not null,
    id int null,
    col1 nvarchar(55) null
)
go

insert into t1(col1)
output getdate(),'insert',inserted.*
into t2
values('testdata 2')

select * from t2
go

--
-- Пример 3: Вставляет данные в табличную переменную
--
declare @t table(col1 nvarchar(55))

insert into t1(col1)
output inserted.col1 into @t
output inserted.id as NewRowID
values('testdata 3')

select * from @t
go

--
-- Пример 4: Протоколирует удаляемые строки в таблицу t2
--
delete t1
output getdate(),'delete',deleted.*
into t2
where id = 1

select * from t2
go

--
-- Пример 5: Определение последнего значения идентификатора
--
insert into t1(col1)
values('testdata 5')

declare @id int = scope_identity()

select @id as id
go

--
-- Удаляет тестовые таблицы
--
drop table t1, t2
go

26: Где бы поискать бесплатных иконок для моей утилиты?

У нас в отделе есть огромный icons-архив, который мы используем для коммерческих приложений. Но для личных программ, его использование запрещено, а мне хотелось бы обновить иконки в ExPEditor'е. Сегодня разговаривал с коллегами о поиске бесплатных иконок для собственных приложений и мне посоветовали эти два сайта http://www.iconfinder.com/ и http://www.iconarchive.com.

Если кто-то знает другие бесплатные архивы с иконками, поделитесь ссылками! ☺

23: Как определить имя приложения, вызывающего хранимую процедуру?

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

Тут необходимо отметить, что в SQL Server вовсе не требуется применение тяжелой артиллерии для определения имени приложения, выполняющего тот или иной SQL запрос. Достаточно лишь вызвать функцию APP_NAME (Transact-SQL). При этом следует помнить, что ограничение доступа по имени приложения не является безопасным методом, так как имя приложения можно переопределить в ConnectionString с помощью ключевых слов App или Application Name.

Развертывание SQL шаблонов

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

SQL шаблоны не требуют дальнейших затрат ресурсов на их "обслуживание", пока они разрабатываются и применяются на одном компьютере. Но как только требуется добавить их в систему управления версиями, а затем развернуть на компьютерах коллег разработчиков и/или администраторов, то сразу встает вопрос об автоматизации этого процесса.

Известно, что SQL шаблоны являются текстовыми файлами с расширением .sql, тогда остается  только выяснить местоположение шаблонов, откуда их загружает SSMS при старте и написать скрипт для копирования файлов в нужную папку.

Различные версии SSMS используют различные папки для хранение шаблонов, к тому же эти папки могут отличаться в зависимости от версии операционной системы. С помощью поиска, я выяснил местоположение шаблонов на моем компьютере с Windows 7 и написал простой скрипт для развертывания шаблонов. Теперь когда у нас в проекте появляется новый разработчик, то ему достаточно выполнить пакетный файл и можно сразу приступить к программированию. Для копирования файлов я использовал xcopy.

Создайте текстовый файл в папке с пользовательскими шаблонами, скопируйте в него следующий скрипт и после сохранения файла, измените его расширение на .cmd. Выполните созданный пакетный файл и перезапустите SSMS для загрузки Ваших шаблонов в Templates Explorer.

ECHO OFF
COLOR 0C
CLS

SET MY_TEMPLATES=A1_MyTemplates_%DATE%

REM Templates Folder for Windows 7

REM SQL Server 2005
SET TARGET_FOLDER_90=C:\Users\%USERNAME%\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql\%MY_TEMPLATES%\

REM SQL Server 2008 or 2008 R2
SET TARGET_FOLDER_100=C:\Users\%USERNAME%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql\%MY_TEMPLATES%\

REM SQL Server 2012
SET TARGET_FOLDER_110=C:\Users\%USERNAME%\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates\Sql\%MY_TEMPLATES%\

ECHO SQL Server Version:
ECHO   1 - SQL Server 2005
ECHO   2 - SQL Server 2008 or 2008 R2
ECHO   3 - SQL Server 2012

SET /P MENU=Select a menu number:

IF %MENU%==1 SET TEMPLATES_LOCATION=%TARGET_FOLDER_90%
IF %MENU%==2 SET TEMPLATES_LOCATION=%TARGET_FOLDER_100%
IF %MENU%==3 SET TEMPLATES_LOCATION=%TARGET_FOLDER_110%

ECHO COPY TO %TEMPLATES_LOCATION%
XCOPY *.* "%TEMPLATES_LOCATION%" /Y /E

PAUSE

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

-- =============================================
-- Author: <Author,,>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================

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

-- =============================================
-- Author: <Author,,Вася Пупкин>
-- Create date: <Create Date,,2012-02-22>
-- Description: <Description,,>
-- =============================================

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

Если кто-то напишет подобную программу для обновления шаблонов - сообщите мне! ☺

Применение SQL шаблонов в SSMS

Применение шаблонов в любой отрасли обосновано стандартизацией и сокращением времени, затрачиваемым на производство продукта. Что в свою очередь повышает конкурентоспособность производства или услуг. Шаблоны также применяются при разработке программного обеспечения. Но часто при общении со специалистами, моими коллегами и сотрудниками других фирм, я наблюдаю, что  многие не знают о возможности применения шаблонов в SQL Server Management Studio.

В SSMS существует множество подготовленных шаблонов для разработки и администрирования баз данных SQL Server'а, шаблоны для Analysis Services и шаблоны для SQL Server Compact Edition. Все эти шаблоны доступны в Template Explorer, для отображения которого необходимо выбрать пункт меню View>Template Explorer.

Шаблоны организованы в виде дерева и сгруппированы по типам объектов в папки. В панели инструментов Template Explorer'а можно выбрать тип шаблонов, а в нижней его части отображается список последних использованных шаблонов, что также облегчает работу, если постоянно используются только пара-тройка шаблонов. Для увеличения области Recently Used Templates, просто переместите сепаратор вверх.

Template Explorer для шаблонов SQL Server

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

<ИмяПараметра, ТипДанных, Значение>

Данные, заключенные в угловые скобки и разделенные запятыми, будут использованы в диалоге спецификации значений параметров шаблона (Specify Values for Template Parameters), для вызова которого, выберите пункт меню Query>Specify Values for Template Parameters... или нажмите сочетание клавиш Ctrl+Shift+M.

Тип данных параметра носит чисто информативный характер, который подсказывает например, что значение параметра типа nvarchar(5) должно быть заключено в одинарные кавычки и не должно превышать длинну в пять символов. Но этот тип не ограничивает ввод пользователем, значений превышающих указанную длину и не принуждает ввод одинарных кавычек.
Например диалог Specify Values for Template Parameters для шаблона создания базы выглядит как изображено ниже.

create database <database_name, sysname, database_name>
go

Диалог спецификации значений параметров шаблона

Результат замены параметров шаблона пользовательскими значениями представляет готовый к выполнению скрипт:

create database TestDatabase
go

Вывод: Использование шаблонов позволяет значительно сократить время затраченное на программирование однотипных скриптов и гарантирует соответствие этих скриптов руководствам  кодирования и именования объектов, принятым в компании.

Extended Properties в SQL Azure

В одном из обсуждений ExPEditor'a с коллегами, был задан вопрос: "Поддерживаются ли расширенные свойства в SQL Azure?"

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

См. также: Рекомендации и ограничения (база данных SQL Azure)

20120212 Права доступа к расширенным свойствам

Расширенные свойства можно добавлять практически ко всем объектам базы SQL Server, за небольшим исключением некоторых типов объектов. Обычным применением расширенных свойств является:

• переопределение имен колонок таблиц, отображаемых в графических интерфейсах клиентских приложений
• описание объектов базы данных, которое сохраняется в свойствах с предопределенным именем.

Так SQL Server Management Studio использует в качестве стандартного имени для описания колонок таблиц - MS_Description.

В процессе проектирования и разработки базы данных разработчик, как правило, имеет полный доступ к объектам базы и таким образом - полный доступ к расширенным свойствам объектов. Но ситуация меняется, когда требуется ограничить доступ к данным и объектам базы.

Для просмотра расширенных свойств объектов требуется разрешение VIEW DEFINITION, а для их добавления/редактирования/удаления разрешение ALTER. Такие разрешения удобнее всего ограничивать с помощью ролей базы. Роли группируют разрешения и ограничения доступа к объектам/данным базы и позволяют сократить время, затрачиваемое на администрирование.

В следующем примере создается роль [my_db_role], члены которой могут изменять свойства объектов только в схеме [my_schema], но они могут просматривать метаданные всей базы и соответственно расширенные свойства всех объектов базы.

create role [my_db_role]
go
grant view definition to [my_db_role]
go
grant alter on schema::[my_schema] to [my_db_role]
go

Описанные права доступа следует учитывать при использовании утилиты ExPEditor для документирования баз SQL Server.

Параметр QUOTED_IDENTIFIER в sqlcmd по умолчанию выключен

SET QUOTED_IDENTIFIER { ON | OFF } регулирует возможность использования ключевых слов  или запрещенных символов в именах объектов базы данных. Например следующий скрип будет успешно выполнен в SQL Server Management Studio, так как в SSMS параметр QUOTED_IDENTIFIER включен по умолчанию:

create table "create" ("insert" int)
go
drop table "create"
go 

Этот же скрипт выполнится с ошибкой синтаксиса, если параметр QUOTED_IDENTIFIER устнановить в значение OFF.

Также QUOTED_IDENTIFIER должен быть включен при создании/изменении отфильтрованных индексов, индексов для вычисляемых столбцов и индексированных представлений. Это следует учесть если база, имеющая подобные объекты, создается скриптами, выполняемыми с помощью утилиты SQLCMD. В SQLCMD параметр QUOTED_IDENTIFIER по умолчанию выключен, для переключения это параметра в значение ON используется аргумент -I.

Например:

sqlcmd -i test.sql -I

UpgradeSettings: Импорт значений параметров предыдущей версии приложения

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

Для реализации автоматического обновления значений всех совпадающих параметров приложения:

• откройте редактор параметров (settings) и добавьте новый логический параметр, например UpgradeSettings, со значением по умолчанию True
• перед кодом загрузки параметров, вызовите следующий фрагмент кода:

if (Properties.Settings.Default.UpgradeSettings)
{
    Properties.Settings.Default.Upgrade();
    Properties.Settings.Default.UpgradeSettings = false;
    Properties.Settings.Default.Save();
}

Базовый класс ApplicationSettingsBase имеет также другие методы, которы могут оказаться полезными при работе с параметрами приложения:

• Reload - загружает последние сохраненные значения параметров
• Reset - восстанавливает значения по умолчанию всех параметров
• GetPreviousVersion - возвращает значение определенного параметра, предыдущей версии приложения.

Рефакторинг settings и диалога login

Спасибо Konst_One за найденную ошибку в Build 016 в диалоге Task >> Generate DDL >> Save File As SQL Skript (.sql)(*.sql). Ошибка уже устранена и в следующей версии прототипа она не будет "резать глаза".

Обсуждение Документирование баз SQL Server на SQL.RU приносит свои плоды. Все ваши советы и замечания не пропадают в пустоту, а пополняют список features и improvements.
Сейчас я занялся рефакторингом главной формы, после которого появится возможность добавления новых свойств к объектам базы данных. В текущей версии можно редактировать только одно свойство - descripton, добавление новых свойств я деактивировал, так как валидатор имен свойств работает абсолютно нестабильно.

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

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

Welkom

Приветствую Вас!

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

ExPEditor - это программа для документирования баз SQL Server. С помощью этой утилиты можно редактировать расширенные свойства объектов базы, просматривать уже созданные свойства и содержимое таблиц. Кроме того есть возможность генерирования скрипта для переноса свойств на другой экземпляр базы, при необходимости можно сгенерировать документацию базы в формате Word.

ExPEditor можно скачать с моего сайта.

Тема на SQL.RU

В теме Документирование баз SQL Server  на SQL.RU обсуждается документирование баз данных, где ExPEditor был впервые представлен в русскоязычном Интернете.