Архив рубрики ‘TSQL’

Полезные скрипты

Posted: Август 7, 2013 in TSQL
Метки:, ,

В SSMS есть замечательная вкладка Template Explorer. Там куча скриптов почти на все случаи жизни. Простейшие скрипты можно запускать сразу. Если в скрипте есть параметры, то заходим в меню Query и выбираем Specify Values for Template Parameters. После этого указываем параметры и нажимаем ОК. Теперь скрипт с подставленными значениями тоже можно запускать.
Но эти скрипты очень многословны. Например скрипт добавления колонки занимает двадцать три строки! Из них шесть отводится только на комментарии, одиннадцать на создание таблицы %), три на собственно добавление самой колонки. Плюс несколько пустых строк. Конечно, эти скрипты скорее всего призваны быть как бы полуфабрикатами. Если что-то не нравится, то можно тут же изменить, никто нам этого не запрещает.
В процессе работы появились несколько скриптов, которые не столь тривиальны как добавление столбца. Эти скрипты используются довольно часто в наших проектах и просты в понимании:

1. Detach и Attach базы данных выглядит так


ALTER DATABASE Northwind SET OFFLINE WITH ROLLBACK IMMEDIATE
go
ALTER DATABASE Northwind SET ONLINE
go

2. Бекап и восстановление


BACKUP DATABASE Northwind TO DISK = N'C:\backups\Northwind.bak' WITH NOFORMAT, INIT, NAME = N'Northwind-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
alter database Northwind2 set single_user with rollback immediate
go
RESTORE DATABASE Northwind2 FROM DISK = N'C:\backups\Northwind.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
go
alter database Northwind2 set multi_user
go

3. Шринк


dbcc shrinkdatabase ('Northwind')

4. Чтение из Excel файла


if object_id('dbo.SomeTable','u') is not null 
  drop table dbo.SomeTable
select 
  *
into 
 SomeTable
FROM 
 OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=c:\xls\Northwind.xlsx;Extended Properties=Excel 8.0')...Products$ s

5. Выборка из DBF. БД DBF должна быть предварительно прилинкована


select 
    *
FROM 
  DBF...Products

6. Курсор


use Northwind
go

declare @CategoryID int

DECLARE MY_CURSOR Cursor  
FOR
select 
 CategoryID
from 
 dbo.Products (nolock) 

open MY_CURSOR 

Fetch NEXT FROM MY_CURSOR INTO 
	@CategoryID 
WHILE @@FETCH_STATUS = 0
BEGIN

  begin try
    ...
      
    if /*условие*/
    begin
      declare @error nvarchar (max) = '...'
      raiserror(@error, 16, 1) -- уровень 16 значит сразу в catch переходит
    end

  end	try
  begin catch			
    print '************Ошибка! ' + ERROR_MESSAGE() ;
  end catch	 
  
  FETCH NEXT FROM MY_CURSOR INTO 
		  @CategoryID 
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Реклама

У Дениса Гладких есть хорошая статья о том как передать список/коллекцию/множество в хранимую процедуру. Автор приводит три способа:

Решение 1. Строка – список значений, разделенных запятой
Решение 2. BULK INSERT
Решение 3. Table-Valued Parameters (Database Engine)

У нас в проекте использует еще одно решение… Если у вас на форме есть немного чекбоксов с выбором, то можно это все представить как число в двоичном счислении и завернуть в одну цифры в десятичном.

Пример: пусть у нас есть 5 чекбоксов с какими-то выборами (ClosedCheck, NotWorkCheck, TooSmallCheck, OthersCheck, LargeTo5CheckBox). Представим это все как одно число

int indicatorFlag = (ClosedCheck.Checked ? 1 : 0) + (NotWorkCheck.Checked ? 2 : 0) + (TooSmallCheck.Checked ? 4 : 0) + (OthersCheck.Checked ? 8 : 0) + (LargeTo5CheckBox.Checked ? 16 : 0);
cmd.Parameters.AddWithValue("@Indicator_Flag", indicatorFlag);

Со стороны встроенной процедуры имеем

@Indicator_Flag tinyint = 15 -- по умолчанию все единицы

А обращаться к отдельным битам нужно вот так

@Indicator_Flag&1 != 0
@Indicator_Flag&16 != 0

Вот такой вот способ. Похоже на стиль из олимпиадного программирования:)

У нас в проекте очень часто используются встроенные процедуры. Очень утомительно получается создавать репозитории и по нескольку раз писать одно и то же

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "dbo.GetSomeData";
cmd.CommandType = CommandType.StoredProcedure;

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

Deals deal = new Deals { Deal_Num = agreementNum, DealType_Code = type };
IList<Deals> deals = deal.ExecuteOptionalFromProcMap<Deals>("pGetAllSubjectDeals").ToList<Deals>();
int dealId = deals.ElementAt(0).Deal_ID;

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

    class Program
    {
        static void Main(string[] args)
        {
            dynamic sql = new DynamicSql();
            dynamic results = sql.pGetAllSubjectDeals(DealType_Code: 2, Deal_Num: "90100");

            Console.WriteLine(results[0].Deal_ID);
            Console.WriteLine(results[0].Deal_Num);
            Console.ReadLine();
        }

    public class DynamicSql : DynamicObject
    {
        private dynamic _expando;

        private IDictionary<string, object> Dictionary
        {
            get
            {
                return _expando as IDictionary<string, object>;
            }
        }

        public object GetSQLParameterValue(SqlParameter param, CallInfo callInfo, object[] args)
        {
            for (int i = 0; i < callInfo.ArgumentCount; i++)
            {
                if (param.ParameterName.Equals("@" + callInfo.ArgumentNames[i]))
                {
                    return args[i] ?? DBNull.Value;
                }
            }

            return DBNull.Value;
        }

        public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result)
        {
            _expando = new ExpandoObject();

            var callInfo = binder.CallInfo;

            using (SqlCommand cmd = Command)
            {
                cmd.CommandText = "dbo." + binder.Name;

                if (cmd.Connection.State != ConnectionState.Open)
                {
                    cmd.Connection.Open();
                }

                SqlCommandBuilder.DeriveParameters(cmd);

                FixStructuredTypeName(cmd);

                cmd.Parameters
                    .Cast<SqlParameter>()
                    .ToList<SqlParameter>()
                    .ForEach(p => p.Value =
                        (p.ParameterName != "@RETURN_VALUE" ? GetSQLParameterValue(p, callInfo, args) : DBNull.Value));

                try
                {
                    SqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        RecordToExpando(reader);
                    }

                    reader.Close();

                    RecordToExpando(cmd);
                }
                catch (SqlException ex)
                {
                    Dictionary.Add("ErrorMessage", ex.ToString());
                    throw ex;
                }
                finally
                {
                    cmd.Connection.Close();
                }
            }

            result = _expando;

            return true;
        }

        /// <summary>
        /// производится проверка параметров процедуры на наличие input табличных параметров
        /// </summary>
        /// <param name="cmd"></param>
        public static void FixStructuredTypeName(SqlCommand cmd)
        {

            foreach (SqlParameter param in cmd.Parameters)
            {
                if (param.SqlDbType != SqlDbType.Structured)
                    continue;

                // param.TypeName will be database.schema.typename
                string typeName = param.TypeName;

                // Trim off the database name to get schema.typename
                typeName = typeName.Substring(typeName.IndexOf(".") + 1);

                // If Microsoft fix this in a future release and only return
                // schema.typename, we would end up with just the typename (no dot)
                // So only change the TypeName if we still have a dot in our text
                if (typeName.Contains("."))
                    param.TypeName = typeName;
            }
        }

        public void RecordToExpando(SqlCommand command)
        {
            for (int i = 0; i < command.Parameters.Count; i++)
            {
                if (command.Parameters[i].Direction == ParameterDirection.Output ||
                    command.Parameters[i].Direction==ParameterDirection.InputOutput||
                    command.Parameters[i].Direction==ParameterDirection.ReturnValue)
                {
                    Dictionary.Add(command.Parameters[i].ParameterName.Replace("@", String.Empty),
                       DBNull.Value.Equals(command.Parameters[i].Value) ? null : command.Parameters[i].Value);

                }
            }
        }

        public void RecordToExpando(IDataReader reader)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                Dictionary.Add(reader.GetName(i), DBNull.Value.Equals(reader[i]) ? null : reader[i]);
            }
        }

        public static SqlCommand Command
        {
            get
            {
                SqlCommand cmd = new SqlCommand("", Connection);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandTimeout = 356;
                return cmd;
            }
        }

        private static SqlConnection Connection
        {
            get
            {
                return new SqlConnection(ConfigurationManager.ConnectionStrings["BillingConnectionString"].ConnectionString);
            }
        }
    }
    }
    

Я сдал экзамен «70-451 PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008» со второго раза. Во время сдачи мне попался вопрос типа, если вы обмениваетесь с данными в формате XML с другой конторой, то как вы будете их обрабатывать. Правильным ответом было поместить содержимое такого файла в ячейку таблицу формата XML, и далее, используя функции типа query, value… обрабатывать данные. Я так и поступил. Положил содержимое в ячейку, далее с помощью XQuery и метода value сформировал «денормализованную» XML, а затем извлекал из нее данные методом value и ложил их в нужные таблицы. К сожалению, даже после оптимизации XQuery-запроса, обработка XML весом 87 Мб заняло на двухпроцессорном сервере 9.5 часов! Об этом я узнал на следующий день, потому что уже через час тормозов нужно было менять тактику.

Нужно было срочно что-то менять, т.к. XML приходили каждый день. Тогда с помощью утилиты xsd.exe я по схеме сформировал класс на C#. Далее нужно было десериализовать этот класс, используя ту XML весом 87 Мб, что на моей однопроцессорной машине заняло несколько минут. Там еще была проблема, что XML был неправильно сформирован, потому что контора, которая нам его выгружала, была по сути нашим конкурентом. И они всячески старались вставлять палки нам в колеса. Пришлось потратить какое-то время на правку xsd-файла. Но в конце концов класс десериализовался.

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

Вот так вот. Верь после этого во всякие сертификации. Можно было бы, конечно, еще понастраивать SQL Server для оптимизации работы с XML, но решение с десериализацией и многопоточной вставкой мне понравилось больше. Правда для такой вставки пришлось открыть около 130 подключений к серверу)

Очень часто, читая исходники процедур для Sql Server, натыкаюсь на подсказку для таблицы NOLOCK. Эта подсказка действует только с инструкцией SELECT и позволяет читать данные, заблокированные другими транзакциями. Эта подсказка также известна как READUNCOMMITTED. Ее «подвох» заключается в том, что она позволяет читать «грязные данные» (dirty readings).

SELECT * FROM Table1 WITH(NOLOCK)

Не так часто, но все же встречается еще одна подсказка — READPAST. Она также действует с инструкцией SELECT, но в отличие от своего грязного брата, читает только закомиченные данные (прошу прощения за американизм).

SELECT * FROM Table1 WITH(READPAST)

Ее плюс является и ее минусом: нельзя понять, вернула ли выборка все данные, или только незаблокированные. Для этого нужно будет добавить еще какую-то логику.
А еще есть такая интересная штука READ_COMMITTED_SNAPSHOT…
Отрывок из MSDN:
После включения изоляции моментального снимка обновленные версии строк для каждой транзакции содержатся в tempdb.Уникальный порядковый номер транзакции определяет каждую транзакцию, и эти уникальные номера записываются для каждой версии строки.Транзакция работает с последними версиями строк, имеющими порядковый номер, предшествующий порядковому номеру транзакции.Более новые версии строк, созданные после начала транзакции, не учитываются.
Это, конечно, хорошая альтернатива к предыдущим подсказкам, если на сервере есть много свободного места на диске и в оперативной памяти.

Хотелось бы поговорить про алгоритмы соединения данных. Когда вы смотрите план запроса в SQL Server, то видите, что при соединении таблиц могут использоваться несколько разных типов соединений. Дело в том, что в зависимости от соединяемых таблиц и наличия индексов оптимизатор может использовать разные алгоритмы. Это может быть Nested Loop, Merge, Hash. Давайте посмотрим на них поближе.

Nested loop join

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

Merge join

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

Hash join

Hash join является самым интересным из трех представленных. Это соединение будет использовано, если обе таблицы велики и нет индексов по join-полям. Это самая наихудшая ситуация.Hash join выполняется в два этапа: построение и проба. При построении берется первая таблица, вычисляются хэш значения join-полей, строится хэш таблица. Во второй фазе берется вторая таблица, и вычисляются хэш значения ее join-полей. Далее на основе хэша вычисляется соответствующий список хэш таблицы. И для каждой записи первой таблицы в соответствующем списке из хэш таблицы происходит сравнивание с записью второй таблицы. При совпадении выдается соединение соответствующих строк.

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

Иерархичные данные в MS SQL 2008

Posted: Октябрь 2, 2010 in TSQL

Как правило иерархичные данные реализуются через ID/ParentID. Для примера посмотрим на такую таблицу

CREATE TABLE dbo.Employees(
	ID int NOT NULL PRIMARY KEY CLUSTERED,
	FirstName nvarchar(50) NOT NULL,
	LastName nvarchar(50) NULL,
	ReportsTo int NULL
)

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

  • 1. Вася Иванов
    • 2. Иван Петров
      • 4. Петр Сергеевич
    • 3. Колян
insert into Employees(ID, FirstName, LastName, ReportsTo)
values(1, 'Вася', 'Иванов', null)

insert into Employees(ID, FirstName, LastName, ReportsTo)
values(2, 'Иван', 'Петров', 1);

insert into Employees(ID, FirstName, LastName, ReportsTo)
values(3, 'Колян', null, 1);

insert into Employees(ID, FirstName, LastName, ReportsTo)
values(4, 'Петр', 'Сергеевич', 2);

Теперь, если хотим просмотреть кто кем руководит, создадим простенький запрос

select
	p.ID,
	p.FirstName,
	p.LastName,
	m.FirstName + ' ' + m.LastName as ManagerName
from
	Employees p
	left join
		Employees m
	on
		p.ReportsTo = m.ID

Результатом которого будет

Если мы хотим увидеть иерархичность, то воспользуемся Common Table Expression для указания уровня иерархии

with EmployeeList(ID, FirstName, LastName, ManagerName, Level)
as
(
select
	ID,
	FirstName,
	LastName,
	CAST('' as nvarchar(50)) as ManagerName,
	0 as Level
from
	Employees
where
	ReportsTo is null

union all

select
	p.ID,
	p.FirstName,
	p.LastName,
	CAST(mng.FirstName + ' ' + mng.LastName as nvarchar(50)) as ManagerName,
	mng.Level + 1 as Level
from
	EmployeeList mng
	join
		Employees p
	on
		p.ReportsTo = mng.ID
)
select ID, FirstName, LastName, ManagerName, Level from EmployeeList

Здесь я привожу имя менеджера к varchar(50), потому что мой Management Studio ругался на несовместимость типов. В результате этого запроса получим

Так мы поступали до недавних пор. В 2008 году майкрософт выпустила новую версию своей флагманской СУБД — MS SQL 2008. В ней был реализован новый тип данных — hierarchyid. Давайте посмотрим на него поближе.
Для начала поменяем нашу таблицу добавив поле HID и удалим поле ReportsTo

CREATE TABLE dbo.Employees(
 ID int NOT NULL PRIMARY KEY CLUSTERED,
 HID hierarchyid NOT NULL,
 FirstName nvarchar(50) NOT NULL,
 LastName nvarchar(50) NULL
)

Далее добавим наших сотрудников

insert into Employees(ID, HID, FirstName, LastName)
values(1, hierarchyid::GetRoot(), 'Вася', 'Иванов')

declare @ID hierarchyid

select @ID = MAX(HID)
from Employees
where hid.GetAncestor(1) = hierarchyid::GetRoot()

insert into Employees(ID, HID, FirstName, LastName)
values(2, hierarchyid::GetRoot().GetDescendant(null, null), 'Иван', 'Петров');

insert into Employees(ID, HID, FirstName, LastName)
values(3, hierarchyid::GetRoot().GetDescendant(null, null), 'Колян', null);

declare @auxID hierarchyid
select @auxID = (select HID from Employees where ID = 2);

select @ID = MAX(HID)
from Employees
where hid.GetAncestor(1) = @auxID

insert into Employees(ID, HID, FirstName, LastName)
values(4, @auxID.GetDescendant(null, null), 'Петр', 'Сергеевич');

select HID.ToString(), HID.GetLevel(), * from Employees

Сначала мы добавили корень.
hid.GetAncestor(1) = hierarchyid::GetRoot() — выбирает все записи, прямым предком которых является корень
parent.GetDescendant (null, null) — возвращает одного потомка данного родителя
child.GetAncestor(n) — возвращает идентификатор hierarchyid, представляющий n-го предка данного элемента

Запросим данные

select HID.ToString(), HID.GetLevel(), * from Employees

В результате чего увидим

Как мы видим, запрос, по сравнению с Common Table Expression, сильно упростился. Но этому предшествовала кропотливая работе по вставке. При работе с данными hierarchyid есть куча методов. Нет смысла их приводить здесь. Это как раз тот случай, для которого создавался MSDN. Стоит отметить, что это довольно интересное решение, но уж больно громоздкое и непереносимое на другие СУБД. Так что стоит несколько раз подумать, прежде чем его использовать.