T-SQL.RU

Если у вас возникнет потребность внести массовые изменения во все объекты БД (заменить одно значение на другое), то следующий скрипт сможет облегчить вам эту задачу. Кроме того, что он заменять указанный текст на новый, он ещё сохраняет на диск старую и новую версию файла, а это поможет вам в последствии легко заменить файлы в системе контроля версий, например, в TFS.
Скрипт написан на PowerShell и в текущей реализации работает с процедурами, функциями, триггерами и представлениями, но может быть легко дополнен. Для выгрузки скриптов в файлы используются два параметра: $backupFolder и $changeFolder. Кроме того, если вам не нужно заменять объекты на сервере, а только выгрузить файлы для последующей замены, то достаточно поменять параметр $alter с $true на $false.

$ServerName=Read-Host "Укажите имя сервера БД"
# Определяем имя БД
$DatabaseName=Read-Host "Введите имя БД"
# Определяем текст для поиска
$OldText=Read-Host "Введите текст для поиска" 
# Определяем текст для замены
$NewText=Read-Host "Введите текст для замены" 

$backupFolder = "c:\temp\old\";  
$changeFolder = "c:\temp\new\";
$alter = $true;  

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$server=new-object("Microsoft.SqlServer.Management.Smo.Server") $ServerName
 
#Имя БД, с которой работаем
$db = $server.Databases[$DatabaseName]

 
# Создаём соединение
$SQLConnection = New-Obj...

читать дальше...

Ошибка 5161 - An unexpected file id was encountered. File id %d was expected but %d was read from "%.*ls". Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.
может возникнуть, когда вы пытаетесь подменить файлы БД.

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

• Создать новую БД с аналогичной структурой
• С помощью операции ALTER DATABASE ... MODIFY FILE указать расположение файлов, которые нам нужно "подцепить"
• Перезапуск SQL Server

После этого, вместо того, чтобы получить доступ к данным, ваша БД переходит в статус Recovery Pending.
Если посмотреть журнал ошибок SQL Server, то там могут присутствовать ошибки:

Причина этой ошибки в том, что файлы вашей исходной БД имели другие идентификаторы, например:



А когда вы создаёте пустую БД, то файлы идут по порядку:

CREATE DATABASE [MyDB]
 ON  PRIMARY 
( NAME = N'db', FILENAME = N'c:\temp\db\db.MDF'  ),
( NAME = N'FG_01', FILENAME = N'c:\temp\db\01.ndf'  ),
( NAME = N'FG_02', FILENAME = N'c:\temp\db\02.ndf'  ),
( NAME = N'FG_03', FILENAME = N'c:\temp\db\03.ndf'  ),
( NAME = N'FG_04', FILENAME = N'c:\temp\db\04.ndf'  ),
( NAME = N'FG_05', FILENAME = N'c:\temp\db\05.ndf'  ),
( NAME = N'FG_06', FILENAME = N'c:\temp\db\06.ndf'  )
 LOG ON
( NAME = N'db_log', FILENAME = N'c:\temp\db\db_Log...

читать дальше...

16 - 17 марта 2016 года прошла 5-ая конференция https://www.youtube.com/user/russianvc.

Мой доклад SQL Server 2016. Temporal Tables:

Temporal Tables - это один из новых функционалов, который появится в SQL Server 2016.
В докладе мы подробно рассмотрим эту технологию, поговорим о преимуществах от её использования на примере реальных задач. Сравним с другими способами отслеживания изменений, такими, как логирование с помощью DML-триггеров, Change Tracking (CT) и Change Data Capture (CDC).

[youtube:-6tr7Mo5C3c]
Презентация и архив с примерами.

Temporal_Tables.pptx (2,90 mb)

Demo.zip (6,44 kb)

читать дальше...

SQL Server позволяет повысить производительность ряда операций за счёт минимального протоколирование изменений в журнал транзакций.
Минимальное протоколирование — это протоколирование только информации, необходимой для восстановления транзакции без поддержки восстановления на момент времени.
Список таких операций впечатляет (https://msdn.microsoft.com/ru-ru/library/ms190925.aspx):

• Операции массового импорта (bcp, BULK INSERT и INSERT... SELECT)
• Операции SELECT INTO
• Инструкции WRITETEXT и UPDATETEXT при вставке или добавлении новых данных в столбцы с типом данных text, ntext и image
• Частичные обновления типов данных с большими значениями с помощью предложений. WRITE инструкции UPDATE при вставке или добавлении новых данных
• Операции с индексами (CREATE INDEX [включая индексированные представления], ALTER INDEX REBUILD или DBCC DBREINDEX, DROP INDEX), тесты над индексами я проводил ранее - http://t-sql.ru/post/index_minimally_logged.aspx

За счёт минимального протоколирования можно в разы сократить время выполнения привычных запросов, но это возможно, только если у вас используется модель восстановления – простая (SIMPLE) или модель восстановления с неполным протоколированием (BULK LOGGED).
Как же быть, если мы используем полную модель восстановления (FULL)? В BOL по этому поводу написано:
В модели полного восстановления все массовые операции полностью протоколируются. Однако для набора массовых операций можно использовать минимальное протоколирование, в...

читать дальше...

Рассмотрим ещё одну новую возможность, которая появилась в SQL Server 2016 CTP 3.1. Это встроенная поддержка сжатия GZIP: COMPRESS и DECOMPRESS.

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

Для начала убедитесь, что вы используете SQL Server 2016 CTP 3.1 и выше. Я использую Microsoft SQL Server 2016 (CTP3.2) - 13.0.900.73 (X64)   Dec 10 2015 18:49:31   Copyright (c) Microsoft Corporation  Enterprise Evaluation Edition (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 10586: ).

Создадим 2 таблицы:

--Простая таблица с текстовым полем
create table SimpleTable ( val varchar(max) );
go

--Таблица с GZIP
create table SimpleTableGZIP ( val varbinary(max) );
go

 Наполним данными:

--SIMPLE
insert into SimpleTable
select replicate('A', 10000);
go 1000

--GZIP
insert into SimpleTableGZIP
select compress ( replicate('A', 10000) );
go 1000

Теперь сравним их объём:

select object_name( object_id ) table_name
     , reserved_page_count
     , ( reserved_page_count ) * 8. sizeKb
     , row_count
  from sys.dm_db_partition_stats
  where object_id in ( object_id( 'SimpleTable' )
                     , object_id( 'SimpleTableGZIP' )
                     );

А теперь небольшой тест на скорость вставки:

set statistics time on;
go
insert into SimpleTable
select t1.na...

читать дальше...

Одна из интересных новинок в SQL Server 2016 - это Row-Level Security (RLS). Технология RLS (row-level security или безопасность на уровне строк) предоставляет возможность создания политик безопасности, которые ограничивают доступ пользователям к информации в БД.

Данная технология поддерживается во многих современных СУБД, но, к сожалению, в предыдущих версиях SQL Server реализовать RLS было не самой простой задачей. На сайте Microsoft есть целый документ, как это реализовать - http://download.microsoft.com/download/8/8/0/880F282A-AA4E-4351-83C0-DFFA3B56A19E/SQL_Server_RLS-CLS_White_paper.docx

Теперь рассмотрим, как это реализовано в SQL Server 2016

Создадим тестовую таблицу и наполним её данными:

if object_id ( N'dbo.TestRLS', N'U' ) is not null
drop table dbo.TestRLS;
go
create table dbo.TestRLS ( id int identity
                         , UserName sysname
                         , Val int
                         );
go
insert dbo.TestRLS 
values  ( 'User1', 11 ) 
      , ( 'User1', 12 )
      , ( 'User1', 13 )
      , ( 'User2', 21 )
      , ( 'User2', 22 )
      ;
go
select * from dbo.TestRLS;
go

Технология RLS состоит из 3-х компонентов:
Predicate function - Пользовательская функция описывает логику безопасности
Security predicate - Применяет предикат к конкретной таблице. Два типа: filter predicates and blocking predicates
Security policy - Коллекция предикатов для нескольких таблиц

Теперь создадим 2-х пользователей и дадим им права на чт...

читать дальше...

В родном клиенте SQL Server Management Studio 2016 появилась возможность в режиме реального времени просматривать, как выполняется запрос. Отслеживать построение плана и оперативно оптимизировать его.

Функционал поддерживается, как для новой версии SQL Server 2016, так и для SQL Server 2014 SP1.

 

В графическом виде можно видеть, как выполняется запрос:

Просмотреть live query statistics можно несколькими способами:

Кроме того, можно включить настройки SET STATISTICS XML ON или SET STATISTICS PROFILE ON, прежде чем запустить выполнение запроса. А в другой сессии выполнить запрос к системному представлению sys.dm_exec_query_profiles:

select st.text
, eqp.physical_operator_name
, eqp.row_count
, eqp.estimate_row_count
, 100 * eqp.row_count /eqp.estimate_row_count as [PercentComplete]
  from sys.dm_exec_query_profiles as eqp
    cross apply sys.dm_exec_sql_text (eqp.sql_handle) as st

Либо использовать расширенное событие (Extended Events) - query_post_execution_showplan

Ссылки по теме:

• https://msdn.microsoft.com/en-us/library/dn831878.aspx
• http://www.brentozar.com/archive/2015/05/announcing-live-query-execution-plans/
• http://www.alexvolok.com/2015/07/3-good-things-about-live-query-statistics/

читать дальше...

С выходом SQL Server 2016 CTP3 нас ожидает приятное дополнение в синтаксисе.

Изменения касаются DDL операций DROP и ALTER TABLE.

Теперь любую операцию по удалению объекта (таблицы, процедуры, функции, триггеры, индексы и т.д.), или столбца таблицы можно выполнить проще с помощью инструкции DROP IF EXISTS.

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

--1)
if object_id ( N'dbo.TestTable', N'U' ) is not null
drop table dbo.TestTable;
go

create table dbo.TestTable ( id int identity );
go

--2)
if exists ( select * from sys.tables t
              inner join sys.schemas s
                on t.schema_id = s.schema_id
              where t.name = N'TestTable' 
                and s.name = N'dbo'
          )
drop table dbo.TestTable;
go

create table dbo.TestTable ( id int identity );
go

Теперь есть ещё один способ (только начиная с CTP3 SQL Server 2016):

--3)
drop table if exists dbo.TestTable;
go

create table dbo.TestTable ( id int identity );
go

Синтаксис поддерживается для следующих объектов:

 

Кроме объектов, подобную конструкцию можно использовать и при удалении столбцов и ограничений:

читать дальше...

С выходом каждой новой версии SQL Server появляется всё больше операций, которые могут быть проведены ON-Line, т.е. без длительных блокировок и ожиданий. В SQL Server 2014, например, мы увидели ON-Line операции над отдельными секциями секционированных объектов.

Новая версия SQL Server 2016 так же добавит несколько операций ON-Line.

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

truncate table dbo.test_table with ( partitions ( 1, 3 to 4 ) ); 
go

А вторая операция - это возможность выполнять изменение колонки в таблице в режиме ON-Line

alter table dbo.test_table 
alter column val varchar(50) not null 
with (online = on );
go

Для демонстрации TRUNCATE над отдельными секция создадим секционированную таблицу и наполним её данными:

create partition function pf_dt ( datetime )
as range right for values ( '20150801', '20150802', '20150803', '20150804', '20150805' );
go
 
create partition scheme ps_dt
as partition pf_dt all to ( [primary] );
go
 
create table dbo.test_table ( dt datetime, val varchar(50) ) on ps_dt (dt);
go
 
insert into dbo.test_table
select '20150801', replic...

читать дальше...

Вопросы, которые периодически задают многие:

"Как узнать статус служб SQL сервер?"

"Как узнать под кем стартует сервис?"

"Как всё это сделать с помощью запроса T-SQL?"

 

Параметры служб можно легко получить через стандартную оснаску Windows "Службы (Services)" - services.msc

Либо через стандартную утилиту SQL Server Configuration Manager - SQLServerManagerXX.msc, где XX - номер версии SQL Server.

Но получить эту информацию на удалённом сервере можно и через запрос на T-SQL. Вот несколько способов:

1) Недокументированная процедура master..xp_servicecontrol у которой 2 входных параметра - команда (start/stop/pause/continue/querystate) и имя службы. Эта процедура позволяет перезапускать службы и просматривать текущий статус.

exec master.. xp_servicecontrol @Action = 'querystate', @ServiceName = N'MSSQLSERVER';

2) Используем недокументированную процедуру master..xp_regread, которая позволяет считывать настройки из реестра.

 

declare @ServiceName sysname;
exec master..xp_regread
      'HKEY_LOCAL_MACHINE'
    , 'SYSTEM\CurrentControlSet\services\MSSQLSERVER'
    , 'ObjectName' 
    , @ServiceName OUTPUT;
select @ServiceName as 'Log On As';

3) Через WMI (Windows Management Instrumentation)

exec master..xp_cmdshell 'wmic service where "Name like ''%sql%''" get Caption, StartName, Started';

 4) Через утилиту командной строки SC.exe

exec master..xp_cmdshell 'SC query MSSQLSERVER';

5) С помощью запроса на PoSh (PowerShell):

... читать дальше...