DevCon12 // msdevcon.ru #msdevcon мая, 2012 г. Microsoft

// DevCon12 SQL Server 2012 – новые возможности T-SQL- разработчика Алексей Шуленин Департамент стратегических технологий Microsoft

FileTable Прежде, чем говорить про FileTable, давайте вспомним, что такое FileStream Но прежде, чем вспоминать, что такое FileStream, давайте вспомним, что еще раньше был WinFS – Объектная файловая система или модель данных поверх NTFS? Каждый тип соответствует реляционной таблице с набором полей-свойств и атрибутом FILESTREAM, экземпляры - записи Entity Model для описания реляционной структуры в виде классов.NET – Каждый объект WinFS характеризуется набором свойств Item – единица хранения Relation – например, CreatedBy ScalarType NestedType – комбинация ScalarType и NestedType – Встроенные типы WinFS = File, Contact, Document, Picture, Audio, Video, Message, … – Можно создавать свои для личного или общего потребления – Экземпляры персистились в XSD или классы.NET

FileTable FileStream как осколок Атлантиды WinFS – Идейно под названием Object File System известна с начала 90-х – Проект Cairo не состоялся, но фрагментарно получил воплощение в широком спектре от Windows 95 до 2000 – Затем это называлось Storage+ в СОМ, затем Relational File System в SQL Server 2000,... – Под именем WinFS объявлена на PDC 2003, запланирована в составе Висты – В августе 2004 объявлена в качестве downloadable update где-нибудь на следующий год после Висты – Еще через год выпущена Beta 1, доступная подписчикам MSDN – Работала на XP, требовала.NET Framework (System.Storage) – В декабре 2005 обновление до.NET 2.0 – 23 июня 2006 г. в блоге WinFS Team было опубликовано ( что 2-й беты не будет и – WinFS не будет выпускаться в виде отдельного компонента, ее ключевые технологии войдут в SQL Server «Katmai» (2008) и ADO.NET в Visual Studio «Orcas»

varbinary(max), хранящийся не в БД, а в файловой системе Нет ограничения в 2 ГБ, только размером тома Стримовый доступ (за счет станд.интерфейсов Win32) SQLный буферный пул не исп-ся, NT system cache Доступ к файл.сист. на удаленной тачке – SMB Файл-группа базы должна иметь атрибут FILESTREAM Вместо файлов БД содержит каталоги файл.сист. (контейнеры) Контейнеры не могут быть влож. Учет.запись SQL Server должна иметь NTFS-права на доступ к контейнерам, раздача прав внутри SQL Server – обычным образом Пока БД открыта, с контейнерами нельзя работать извне SQL Server Интеграция SQL Serverного движка с NTFS позволяет делать по ним вставку, обновление, запросы, поиск, backup стандартными SQLными операторами При кластеризации – на общем диске Что такое FILESTREAM (напоминание)

FileTable Хранить файлы в SQL Server ничуть не хуже, чем в файловой системе На самом деле даже лучше, потому что при этом штатными средствами на раз обеспечивается – Транзакционность операций – Резервное копирование – Сортировка и поиск по атрибутам, полнотекстовый и семантический по содержанию – Безопасность, разделение доступа, администрирование на основе политик,... К сожалению, до последнего времени эти преимущества мало, что давали, потому что приложения привыкли работать с файлами при помощи Windows API – Чтобы открыть документ в Ворде, его придется выгрузить из SQL Server в файловую систему. Хотя бы на время. – Чтобы посмотреть фильм в плеере – аналогично и т.д. FileTable – это способ засветить файлстримовский контент, хранящийся в SQLной базе, в виде файловой шары – \\ \ \ \ \ – Для Windows-приложения FileTable выглядит как обычная папка общего доступа, и оно может читать оттуда файлы, копировать туда новые, создавать директории и т.д. – SQL Server перехватывает эти вызовы и интерпретирует их внутри себя в привычные реляционные операции – Также над FileTable доступны прямые запросы, обновления и пр.операции ср-вами T-SQL

FileTable - пример create database TestFileTable on primary (name = TestFileTableS_data, filename = c:\Temp\TestFileTable_data.mdf'), filegroup FG1 contains filestream (name = TestFileTable_media, filename = 'c:\Temp\TestFileTable_media') log on (name = TestFS_log, filename = 'c:\Temp\TestFileTable_log.ldf') При установке экземпляра SQL Server создается глобальная шара \\?\GLOBALROOT\Device\RsFx0200\ \, обеспечиваемая драйвером RsFx. См. net share. Сейчас в ней появилась папка TestDir (F5) alter database TestFileTable set filestream (directory_name = 'TestDir') alter database TestFileTable set filestream (non_transacted_access = full)

FileTable - пример use TestFileTable create table DocumentStore as FileTable with ( FileTable_Directory='Document', FileTable_Collate_Filename=database_default ) Сейчас в TestDir появилась папка Document Скопируем туда какой-нибудь документ, и он отразится в таблице DocumentStore В обратную сторону тоже работает exec xp_cmdshell 'dir \\w7x64_Denali\MSSQLSERVER\TestDir\Document' exec xp_cmdshell 'copy c:\Temp\FileTable.sql \\w7x64_Denali\MSSQLSERVER\TestDir\Document' select stream_id, file_stream, name, path_locator, parent_path_locator, file_type, cached_file_size, creation_time, last_write_time, last_access_time, is_directory, is_offline, is_hidden, is_readonly, is_archive, is_system, is_temporary, FileTableRootPath(), file_stream.GetFileNamespacePath() from DocumentStore

Полнотекстовый поиск SQL Server 2012 позволяет искать не только по содержанию документа, но и по его расширенным св-вам, если IFilter их вытаскивает – См., напр., в блоге Боба Бушмена «Using SQL Server Denali Full-Text Search With Extended Properties»Using SQL Server Denali Full-Text Search With Extended Properties Кастомизированный оператор NEAR позволяет задать порядок следования поисковых слов, а также макс.кол-во непоисковых, к-е разделяют пару поисковых – См. BOL к СТР1BOL Семантический поиск – Полнотекст просто ищет слова в содержании, семантический поиск позволяет оперировать по смыслу – Поиск ключевых фраз = Sample Document.docx' = DocumentID FROM Documents WHERE DocumentTitle AS Title, keyphrase, score FROM SEMANTICKEYPHRASETABLE(Documents, ORDER BY score DESC – Поиск близких по смыслу документов AS SourceTitle, DocumentTitle AS MatchedTitle, DocumentID, score FROM SEMANTICSIMILARITYTABLE(Documents, INNER JOIN Documents ON DocumentID = matched_document_key ORDER BY score DESC

Новое в GEOMETRY/GEOGRAPHY Геопространственные агрегаты – Aгрегатные функции CollectionAggregate, ConvexHullAggregate, EnvelopeAggregate и UnionAggregate – Pаботают, как обычные SUM(), COUNT(), AVG() и т.д., но применительно к типам Geometry и Geography – = geography::UnionAggregate(cell) from #cells –

Новое в GEOMETRY/GEOGRAPHY Поддержка дуг в соответствии со стандартом OGC – Дуга задается тремя точками Т.к. три точки однозначно определяют проходящую через них окружность что на плоскости, что на поверхности сферы – BufferWithCurves(distance) – честная окружность В отличие от STBuffer(distance) – аппроксимирует ломаной – Методы STCurveToLine() / CurveToLineWithTolerance(tolerance, relative) – Метод Reduce(tolerance) по алгоритму Дугласа-Пекера –

Новое в GEOMETRY/GEOGRAPHY Полный глобус –

Последовательности if exists (select 1 from sys.sequences where name = 'Seq1') drop sequence Seq1 create sequence dbo.Seq1 as int start with 1 increment by 1 maxvalue 1000 cycle Отдельный объект схемы Счетчик, прирастающий на значение INCREMENT, начиная со значения start в пределах [MINVALUE, MAXVALUE] Удобно по сравнению с identity, когда значение должно быть известно перед вставкой в автоинкрементную колонку В отличие от identity колонки с sequences не защищены от перезаписи. Уникальность должна обеспечиваться приложением. NO MINVALUE / NO MAXVALUE – будет взято минимальное / максимальное значение численного типа, определенного в AS CYCLE – начинать по кругу при достижении MINVALUE / MAXVALUE (в зависимости от знака INCREMENT) или выкидывать exception CACHE n – сколько след.значений будет храниться в памяти для повышения быстродействия. Последнее кэшированное значение записывается в сист.табл. и сохраняется на диск По достижении последнего значения следующие n значений записываются в память. Новое посл.зн-е сохр-ся в сист.табл. При штатном гашении сервиса в сист.табл. записывается след.зн-е от достигнутого текущего При нештатном – интервал от текущего до сохраненного пропадает ALTER SEQUENCE – менять можно все Метаданные - sp_sequence_get_range, sys.sequences

Паджинация int = int = 10 ;with cte as (select *, row_number() over (order by name) rowno from sys.objects) select * from cte where rowno + 1 select * from sys.objects order by name rows fetch rows only В сравнении с СТЕ:

Сумма нарастающим итогом Имеется таблица c полями CustomerID, CompanyName, Weight. Требуется получить правую колонку (Weight_RunningTotal) До 2012 существовало 3 способа 1. Курсор float = 0 = cursor local keyset for select Weight from Customer order by CustomerID for update of Weight_RunningTotal while 1 = 1 begin fetch next if 0 break update Customer set Weight_RunningTotal where current end select * from Customer

Сумма нарастающим итогом 2. Джойн таблицы самой на себя В колонке ID содержится текущая запись, а в ID_Preceding - все, ей предшествующие. Сумма нарастающим итогом получается как сумма по группам ID: select t2.CustomerID ID, t1.CustomerID ID_Preceding, t1.Weight from Customer t1 right join Customer t2 on t1.CustomerID < t2.CustomerID select t2.CustomerID ID, sum(isnull(t1.Weight, 0)) s from Customer t1 right join Customer t2 on t1.CustomerID < t2.CustomerID group by t2.CustomerID

3. Подзапрос Что слышится - "в n-ю запись вставляется сумма n-1 предыдущих" - то и пишется: Все три способа неидеальны Курсор, по определению, медленный Перемножение таблицы на себя - затраты растут как квадрат числа записей, Вложенный подзапрос – аналогично Идеальным был бы линейный способ, при котором таблица пробегается один раз а ля курсор, но без курсора Проблема в том, что мы не можем гарантировать порядок, как записи будут перебираться в ходе выполнения запроса, т.к. SQL оперирует с множествами, а классические множества неупорядочены Если оптимизатору покажется лучше по каким-нибудь соображениям изменить порядок сканирования, пред.скрипт возвратит чушь select CustomerID, CompanyName, Weight, (select SUM(Weight) from Customer t2 where t2.CustomerID < t1.CustomerID) from Customer t1 float = 0 update Customer set += Weight select * from Customer

Ушлый народ пытался обеспечить порядок хинтами оптимизатору WITH(INDEX(1),TABLOCKX), OPTION (MAXDOP 1) и другими уловками Однако все они были разобраны в журнале SQL Server Magazine и сделан вывод:журнале SQL Server Magazine in SQL Server 2008 and earlier, I havent yet found a pure set-based technique for running totals that optimizer handles very efficiently with large partition sizes Imagine how great it would be if one day wed be able to express a running total like this: … SUM(Weight) over (order by CustomerID) Мне приятно сообщить, что этот день настал Применимо ко всем агрегатным и аналитическим функциям, а также NEXT VALUE FOR для последовaтeльностейагрегатныманалитическим Позволяет гибко задавать размер окна select CustomerID, CompanyName, Weight, SUM(Weight) over (order by CustomerID) from Customer -- Нараст.итог не включая текущую запись: select CustomerID, CompanyName, Weight, SUM(Weight) over (order by CustomerID rows between unbounded preceding and 1 preceding) from Customer -- Скользящее среднее в окне из 3-х записей: select *, avg(delta * 1.0) over (partition by grouping_id order by dt rows between 1 preceding and 1 following) as x from #t

Сравнительная производительность на примере Sales.SalesOrderDetail из Adventure Works ( записей) Группировка по SalesOrderID (среднее число записей в группе = 17

Сравнительная производительность на примере Sales.SalesOrderDetail из Adventure Works ( записей) Без промежуточного группирования:

Сходные функции Обратная задача – из колонки нарастающим итогом получить в каждой записи дельты прироста Типичное решение – тоже джойн таблицы саму на себя, выстроив перед каждой записью все предшествующие внутри каждой группы, пронумеровать их в порядке убывания времени и выкинуть все с номером > 1, т.е. оставить самую ближайшую из прошлого with cte (id1, gr1, dt1, x1, id2, gr2, dt2, x2, n) as (select *, ROW_NUMBER() over (partition by t1.id order by t2.dt desc) from #t t1 left join #t t2 on t1.grouping_id = t2.grouping_id and t1.dt > t2.dt) select * from cte where n = 1 order by id1 Функция LAG(x, n) позволяет получить значение из колонки х, отступив n записей назад: select *, lag(x, 1) over (partition by grouping_id order by dt) from #t LEAD(x, n) – аналогично, вперед Функции first_value() и last_value() возвращают первое и последнее значение в группе По умолчанию - between unbounded preceding and current row По всему окну - over (partition by grouping_id order by dt rows between unbounded preceding and unbounded following)

Типовая ситуация применения: в появившейся в SQL Srv 2005 конструкции BEGIN TRY... END TRY BEGIN CATCH... END CATCH, когда ловим ошибку, обрабатываем какие-то случаи, а остальное перевыкидываем как есть в приложение Такое проблематично сделать при помощи RAISERROR varchar(10) = '0'--'0'; '0.001'; 'aaa' tinyint begin try = 1.0 / as decimal(10, 5)) end try begin catch if ERROR_NUMBER() = 8134 select 'Ошибка деления на ноль' else if ERROR_NUMBER() = 8115 select 'Арифметическое переполнение - в tinyint столько не влезает' else throw end catch THROW

EXEC WITH RESULT SETS См. также sys.dm_exec_describe_first_result_setsys.dm_exec_describe_first_result_set use [Adventure Works DW 2012] exec = N'select FirstName, MiddleName, LastName from DimCustomer where LastName + ''%''; select EnglishProductName, SafetyStockLevel from = = 'Y' with result sets ( ([Имя] nvarchar(5), [Отчество] nvarchar(3), [Фамилия] nvarchar(20)), ([Продукт] nvarchar(10), [Запас на складе] int) )

ColumnStore Index C1C2C3C5C6C4 В отличие от традиционной организации по строкам в колоночных индексах данные группируются и хранятся по одному столбцу за раз Преимущества: Большинство звездных запросов не трогают все столбцы таблицы. Т.о., многие поля никогда не будут передаваться в память. Это улучшает использование буферного пула, что уменьшает общее число операций ввода-вывода Должны быть прочитаны только необходимые столбцы => меньше данных читается из диска в память и затем перемещается из памяти в кэш процессора Столбцы сильно сжимаются. Это также уменьшает число байтов, которые необходимо прочесть и переместить. Не существует понятия ключевых столбцов => нет ограничения на 16 столбцов в ключе и длину ключа 900 байт Ограничения Не поддерживаются блобовские типы, binary, uniqueidentifier, sql_variant, CLR, XML На данный момент readonly

Дополнительные улучшения Ф-ция Format: select format(sysdatetime(), 'dd/MM/yyyy hh:mm:ss.ffff') -> 19/04/ :54: Ф-ция Parse: select parse('19/04/2012' as date using 'ru-RU') -> Ф-ция Try_Parse: select try_parse('19/04/2012' as date using 'en-US') -> NULL Аналогично, Try_Convert Ф-ция DateTimeFromParts: select datetimefromparts(2012, 4, 19, 14, 30, 50, 0) -> :30: Аналогично DateTime2FromParts, DateFromParts, TimeFromParts, … Ф-ция EOMonth: select EOMonth(SYSDATETIME()) -> ; select EOMonth(SYSDATETIME(), 3) -> Другие функции CONCAT ( string_value1, string_value2 [, string_valueN ] ) IIF ( boolean_expression, true_value, false_value ) CHOOSE ( index, val_1, val_2 [, val_n ] ),

Пожалуйста Оцените доклад и мастерство докладчика. Форма для оценки находится в вашем инфопакете.

© 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.