Динамическая сортировка данных в MS SQL Server

Часто бывает нужно предоставить выборку данных в MS SQL Server в отсортированном виде, при этом сортировка должна быть настраиваемая на клиенте (направление сортировки и поле сортировки). Пример такого сценария: таблица на клиенте, в которой выводятся данные о каких-либо объектах. 

В большинстве случаев, такую задачу решают при помощи Dynamic SQL.

Альтернативным вариантом является построение выражения в инструкции ORDER BY на основе входных данных.

Собственно пример реализации такого варианта решения:

declare @order nvarchar(max)
set @order = 'Field1'
-- ...
declare @direction nvarchar(4)
set @direction = 'asc'
-- ...
select * 
from MyTable1
order by 
  case
    when @direction = 'asc' then 
      case
        when @order = 'Field1' then Field1
        when @order = 'Field2' then Field2
        when @order = 'Field3' then Field3
      end 
    else null
  end asc,
  case
    when @direction = 'desc' then 
      case
        when @order = 'Field1' then Field1
        when @order = 'Field2' then Field2
        when @order = 'Field3' then Field3
      end 
    else null
  end desc

Получение данных родительского XML-узла в T-SQL

Недавно мне пришлось решать одну задачу, в рамках которой возникла необходимость выбора родительского узла в XML дереве и все это в рамках SQL-запроса. Как оказалось, сделать это не так трудно - в T-SQL есть "магическое" XPATH-выражение для получения родительского узла: 'parent::*'.

В результате получается следующее:

declare @x xml
-- ...
select @x.query('/Document/A/parent::*')

Или можно чуть посложнее - для всех узлов B выбрать узел C, входящий в родительский узел от родительского узла для узла B (прародитель):

select T.b.value('parent::*/parent::*/C', 'nvarchar(50)') from @x.nodes('//Document/A/B') T(b)

Вот и все. Надеюсь кому-то это пригодится.

Формирование таблицы временных интервалов заданной длины

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

Каждый решает эту задачу по своему, самый простой вариант - это WHILE цикл со вставкой строки с новым интервалом во временную таблицу. Но начиная с 2005 MS SQL Server'а, благодаря Common Table Expression (CTE), теперь это можно сделать по-другому. Собственно пример (формирование интервалов продолжительностью в 2 дня за конкретный месяц):

set dateformat ymd
--
declare @StartDate datetime
declare @EndDate datetime
declare @Step int
--
set @StartDate = N'2011-09-01'
set @EndDate = N'2011-10-01'
set @Step = 2
--
declare @date datetime
set @date = @StartDate
--
declare @interval table (

int_start datetime primary key

,int_end datetime

)
--
;
with cte as (

select

@date as date1

,dateadd(day, @Step, @date) as date2

--
union all
--
select

C.date2

,dateadd(day, @Step, C.date2)

from cte C
where C.date2 < @EndDate

)
insert into @interval (

int_start

,int_end

)
select

date1

,date2

from cte
--
select * from @interval
GO

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

З.Ы. Кому интересно - пользуйтесь на здоровье :)

Сложение строковых значений столбца

Иногда возникает задача, в которой необходимо сложить все строковые значение какого-либо столбца в одну строку через разделитель, например через ";". Сделать это можно следующими способами.

Способ 1: Сложение в переменную

declare @result nvarchar(max)
set @result = N''

select
 @result = @result + Name + N';'
from Table

Способ 2: Сложение в строку (если нужно использовать как подзапрос)

select Name + N';'
from Table
for xml path('')

З.Ы. Удачной борьбы со скриптами ;)

Incorrect syntax near 'GO'

Недавно столкнулся со следующей проблемой: в моей программе нужно выполнять набор sql-скриптов, которые содержатся в файлах *.sql, на базе данных MS SQL Server 2005. Каждый такой скрипт в отдельности содержит инструкции по созданию таблиц, хранимых процедур, обновления данных и т.п., и т.д.

В этом случае использование привычных объектов SqlCommand и SqlDataAdapter приводит к генерации исключения времени выполнения со следующей ошибкой "Incorrect syntax near 'GO'". Это происходит из-за того, что в T-SQL на самом деле нет такой иструкции, но Query Analyzer разбирает запросы и интерпретирует комманду 'GO' как комманду отправки пакета комманд на сервер.

Один из вариантов решения проблемы состоит в использовании утилиты, идущей в поставке MS SQL Server, osql.exe. К сожалению, такой вариант не всегда является приемлемым.

Далее я привожу более простой вариант решения проблемы с использованием сборок MS SQL Server:

using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

namespace MyProject.Sql
{
public class SampleSql
{
public static void ExcuteSql(string command, string connectionString)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();

var server = new Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(command);
server.ConnectionContext.Disconnect();
}
}
}
}

Для выполнения данного кода необходимо подключить к проекту следующие сборки:

• Microsoft.SqlServer.ConnectionInfo
• Microsoft.Sqlserver.Smo

Вот собственно и все. Удачного вам программирования :)

SQL Server T-SQL Coding Conventions, Best Practices, and Programming Guidelines

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

Для лучшей работы приложения, использующего базы данных, необходимы специалисты, такие как администраторы баз данных и программисты баз данных. В своем приложении вы можете использовать Microsoft SQL Server, Oracle, Sybase, DB2 или любую другую СУБД. Если во время разработки приложения вы не привлекаете к работе специалистов по базам данных, то достаточно часто «узким местом» в проекте становится именно база данных. В этой статье предложены методы написания SQL-скриптов так, чтобы администраторам баз данных и разработчикам было проще разобраться в проекте и на более высоком уровне поддерживать и сопровождать базу данных.

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

• Примите соглашение об именовании объектов базы данных, стандартизируйте его в своей компании и следуйте этому соглашению. Это позволит повысить читаемость вашего кода и сделает его более понятным. Здесь можно найти пример именования объектов.
• Старайтесь приводить свои данные, по крайней мере, к 3-ей нормальной форме. Но не стоит следовать этому утверждению как догме, т.к. в некоторых случаях небольшие отступления от этого правила могут ускорить выполнение запросов.
• Старайтесь писать комментарии к нетривиальному коду в хранимых процедурах, триггерах, а так же в пакетных SQL командах, чтобы другим разработчикам было проще разобраться в вашем коде. Не стоит беспокоиться о длине комментариев, так они никак не влияют на производительность запросов.
• Не используйте SELECT *. Всегда указывайте имена требуемых полей в выражении SELECT. Например: SELECT CustomerId, CustomerName. Таким образом, вы уменьшите количество операций ввода-вывода диска, что улучшит производительность.
• Старайтесь избегать курсоров всегда когда это возможно. Всегда придерживайтесь подхода «основанного на наборах» вместо «процедурного» для доступа к данным и манипулирования ими. Достаточно часто курсоров можно избежать, заменив их оператором SELECT. Если, все же, вам не удается избежать курсора заменой оператором SELECT, используйте цикл вместо курсора. Цикл почти всегда быстрее, чем курсор. Но для замены курсора на цикл должен присутствовать столбец (первичный ключ или уникальный ключ), чтобы однозначно идентифицировать каждую строку. Здесь можно посмотреть примеры использования WHILE циклов.
• Избегайте создания временных таблиц, стараясь обрабатывать данные на столько, на сколько это возможно, поскольку создание временной таблицы влечет за собой больше операций ввода-вывода диска. Также лучше использовать дополнительные SQL выражения, просмотры (представления), переменные типа TABLE вместо временных таблиц.
• Старайтесь не использовать символ обобщения (%) в начале слова при поиске с использованием LIKE, поскольку это приводит к сканированию (пересмотру) индекса, что проигрывает индексному поиску. В следующем примере первое выражение приводит к пересмотру индекса, в то время как второе к поиску по индексу:
  
  
  

  SELECT CustomerId FROM Customers WHERE CustomerName LIKE '%soft'

  SELECT CustomerId FROM Customers WHERE CustomerName LIKE 'M%t'
  Также избегайте поиска с использованием операторов неравенств (<> и NOT), которые приводят к сканированию таблицы и пересмотру индекса.
• При необходимости используйте «производные таблицы», т.к. они работают быстрее. Например, следующий запрос ищет вторую по величине зарплату среди сотрудников по таблице Employees:
  
  
  

  SELECT MIN(Salary)
  FROM Employess
  WHERE EmpID in (
  SELECT TOP 2 EmpID
  FROM Employees
  ORDER BY Salary DESC
  )
  Этот запрос может быть переписан с использованием производной таблицы, как показано ниже, в результате чего запрос выполняется в 2 раза быстрее первого:
  
  

  SELECT MIN(Salary)
  FROM (
  SELECT TOP 2 Salary
  FROM Employees
  ORDER BY Salary DESC
  ) AS A
  Это просто пример, и результат, полученный вами, может сильно отличаться от представленного в зависимости от свойств базы данных, индексов, объема данных и т.п.
• Во время разработки базы данных не забывайте уделять время быстродействию. Позже, во время промышленной эксплуатации базы данных, будет намного сложнее настраивать производительность базы данных, т.к. для этого скорее всего вам потребуется пересобрать таблицы и индексы, переписать запросы и т.п. Для анализирования работы запросов можно пользоваться командами SHOWPLAN_TEXT и SHOWPLAN_ALL, а также визуальным представлением плана выполнения запроса (execution plan) в Query Analyzer или SQL Management Studio. Запросы следует писать так, что бы они использовали «поиск по индексу» (Index Seek), а не «сканирование индексов» (Index Scan), и тем более избегать «сканирование таблиц» (Table Scan). Сканирование индексов или таблиц достаточно дорогостоящая операция в плане производительности, поэтому лучше их избегать. И, конечно же, используйте «правильные» индексы на «правильных» колонках.
• Используйте команду SET NOCOUNT ON в начале пакетных команд SQL, хранимых процедур и триггеров в рабочей версии скриптов, чтобы предотвратить вывод сообщений таких как (1 row(s) affected) после выполнения команд INSERT, UPDATE, DELETE и SELECT. Это позволит повысить производительность хранимых процедур за счет уменьшения сетевого трафика.
• Для наглядности и повышения читаемости кода используйте JOIN операторы, определенные стандартом ANSI SQL, вместо классического соединения таблиц. С ANSI соединениями (JOIN) команда WHERE используется только для задания условия отбора (фильтрации) данных, тогда как в классическом соединения команда WHERE используется еще и для задания условий соединения таблиц. Далее приведены два примера, первый из которых демонтирует использование классического соединения таблиц, а второй - использование ANSI JOIN для соединения таблиц.
  
  
  

  SELECT a.au_id, t.title
  FROM titles t, authors a, titleauthor ta
  WHERE a.au_id = ta.au_id
  AND ta.title_id = t.title_id
  AND t.title LIKE '%Computer%'
  
  SELECT a.au_id, t.title
  FROM authors a
  INNER JOIN titleauthor ta ON a.au_id = ta.au_id
  INNER JOIN titles t ON ta.title_id = t.title_id
  WHERE t.title LIKE '%Computer%'
• Не используйте префикс "sp_" в именах своих хранимых процедур. Префикс sp_ зарезервирован для системных процедур, поставляемых с SQL Server. Каждый раз когда SQL Server'у необходимо выполнить хранимую процедуру, имеющую префикс sp_, первое что он делает - ищет хранимую процедуру в базе данных master, затем просматривает по всем спецификаторам (база данных, владелец), затем как владелец dbo. Таким образом можно сократить время поиска хранимой процедуры, избегая префикс sp_.
• Представления (View) обычно используются для отображения различной информации пользователям в зависимости от их требований. Представления также используются для ограничения доступа к таблицам, на основе которых построены представления, предоставляя права только на представления. И самое главное, представления позволяют вам упрощать ваши запросы. Объединяйте все необходимые данные, соединения (join) и вычисления в представления, в результате вам не придется дублировать их при написании запросов. Вместо это просто сделайте SELECT из вашего представления.
• Используйте пользовательские типы данных (User Define Datatypes), если специфические поля повторяется во многих ваших таблицах. Таким образом вы получите непротиворечивые поля, гарантировано имеющие одинаковые типы данных.
• Не предоставляйте возможности вашим приложениям выбирать\манипулировать данными, используя SELECT или выражения INSERT\UPDATE\DELETE. Вместо этого создайте хранимые процедуры и предоставьте приложениям к ним доступ. В результате чего вы получите "чистый" и последовательный доступ к данным из любых модулей ваших приложений, что позволит в тоже время заключить бизнес-логику приложений в базе данных.
• Старайтесь не использовать типы данных TEXT и NTEXT для хранения текста больших объемов. Существует ряд проблем связанных с использованием типа данных TEXT. Например, вы не можете напрямую записывать или обновлять текстовые данные используя выражения INSERT или UPDATE. Вместо этого вы будете вынуждены использовать специальные выражения READTEXT, WRITETEXT и UPDATETEXT. Также существует множество ошибок связанных с репликацией таблиц, содержащих текстовые поля. Таким образом, если нет необходимости хранить текст большего размера, чем 8KB, лучше использовать CHAR(8000) или VARCHAR(8000).
• Если есть возможность, то старайтесь не хранить бинарные файлы и файлы образов (Binary Lange Object или BLOB'ы) в базе данных. Вместо этого храните путь к бинарному файлу в базе данных и используйте его как указатель на сам файл, который может храниться где угодно на сервере. В результате чего, вы получите более высокую производительность, работая с большими бинарными файлами вне базы данных. И в добавок ко всему, база данных не предназначена для хранения файлов.

Browser Security Handbook

Перешедший чуть более года назад в Google Михал Залевски (Michal Zalewski), ранее известный по ряду уязвимостей, обнаруженных им в различных браузерах, опубликовал Browser Security Handbook - обзор подходов к обеспечению безопасности в браузерах вместе со сводными таблицами, описывающими эффективность их реализации (равно как и наличие таковой) в девяти версиях современных браузеров - от IE6 до Chrome. Крайне познавательный и емкий материал. Источник: Browser Security Handbook

Отключение кэширования паролей в Windows, IE и Outlook

Для повышения безопасности рекомендуется отключить кэширование паролей. Сделать это можно следующими способами:

1. Отключение кэширования пароля в Internet Explorer
   • Открыть раздел реестра HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings
   • Создать параметр DisablePasswordCaching (тип "REG_DWORD", представление "Шестнадцатеричное", значение "0x00000001")
2. Отключение кэширования пароля в Outlook
   • Открыть раздел реестра HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\10.0\Outlook\Security или HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Outlook\Security
   • Создать параметр EnableRememberPwd (тип "REG_DWORD", представление "Шестнадцатеричное", значение "0x00000001")
3. Отключение кэширования пароля в Windows
   • Открыть раздел реестра HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Network(для пользователя) или HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Policies\Network (для системы)
   • Создать параметр DisablePasswordCaching (тип "REG_DWORD", представление "Шестнадцатеричное", значение "0x00000001")

Быть программистом

Ссылка на интересную статью про бытность программистов, в которой автор приводит свои рекомендации как надо писать код.

Первое сообщение

Сегодня я зарегистрировал свой первый блог.

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