Функции/команды SQL Server, о которых большинство разработчиков не знает [дубликат]

FileStream в SQL Server 2008 : функция FILESTREAM в SQL Server 2008 позволяет хранить данные больших двоичных объектов и осуществлять эффективный доступ к ним с использованием комбинации SQL Server 2008 и файловой системы NTFS.

Создание таблицы для хранения данных FILESTREAM

Если в базе данных есть файловая группа FILESTREAM, можно создавать таблицы, содержащие столбцы FILESTREAM. Как упоминалось ранее, столбец FILESTREAM определяется как столбец varbinary (max), имеющий атрибут FILESTREAM. Следующий код создает таблицу с одним столбцом FILESTREAM

USE Production;
GO
CREATE TABLE DocumentStore (
       DocumentID INT IDENTITY PRIMARY KEY,
       Document VARBINARY (MAX) FILESTREAM NULL,
       DocGUID UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL
              UNIQUE DEFAULT NEWID ())
FILESTREAM_ON FileStreamGroup1;
GO

PIVOT и UNPIVOT

«Схема_информации» дает мне множество представлений, которые я могу использовать для сбора информации о таблицах объектов SQL, процедурах, представлениях и т. Д.

Если вы используете Management Studio 2005, вы можете настроить автоматическое выполнение вашего запроса как транзакции. В новом окне запроса перейдите в Query-> Query Options. Затем щелкните вкладку ANSI (слева). Установите флажок SET IMPLICIT_TRANSACTIONS. Щелкните ОК. Теперь, если вы запустите любой запрос в этом текущем окне запроса, он будет запущен как транзакция, и вы должны вручную выполнить ОТКАТ или ЗАКУПИТЬ перед продолжением. Кроме того, это работает только для текущего окна запроса; в уже существующих / новых окнах запросов должна быть установлена ​​опция.

Лично я нашел это полезным. Однако это не для слабонервных. Вы должны не забыть ОТКАТИТЬ или ЗАВЕРШИТЬ свой запрос. Он НЕ сообщит вам, что у вас есть ожидающая транзакция, если вы переключитесь на другое окно запроса (или даже на новое). Однако,

Это классическая проблема. У вас есть много доступных решений:

1. Вероятно, самый простой способ - настроить ваш веб-сервер для серверных файлов CSS так, чтобы он никогда не кешировался / не истекал немедленно. Очевидно, вы не захотите этого в производственной среде. С IIS это очень легко сделать.
2. Добавьте случайное значение к имени файла, который вы включаете, например Site.css? V = 12. Это то, что SO делает для своих включений. Я делаю это самостоятельно, чтобы на машине разработки параметр менялся каждый раз (guid), когда файл обслуживается, но при развертывании он использует номер версии svn. Немного сложнее, но надежнее.
3. Многие,

COALESCE (), он принимает поля и значение для использования в случае, если поля нулевые. Например, если у вас есть таблица с городом, штатом, почтовым индексом, вы можете использовать COALESCE (), чтобы вернуть адреса в виде отдельных строк, IE:

City | Состояние | Почтовый индекс

Хьюстон | Техас | 77058

Бомонт | Техас | NULL

NULL | Огайо | NULL

, если вы запустите этот запрос к таблице:

select city + ‘  ‘ + COALESCE(State,’’)+ ‘  ‘+COALESCE(Zipcode, ‘’)

Вернет:

Хьюстон, Техас 77058

Бомонт, Техас

Огайо

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

DECLARE @addresses VARCHAR(MAX)
SELECT @addresses = select city + ‘  ‘ + COALESCE(State,’’)+ ‘  ‘
+COALESCE(Zipcode, ‘’)             + ‘,’ FROM tb_addresses
SELECT @addresses 

Вернется: Хьюстон Техас 77058, Бомонт Техас, Огайо

После создания #TempTable в процедуре, он доступен во всех хранимых процедурах, которые затем вызываются из исходной процедуры. Это хороший способ разделить набор данных между процедурами. см.: http://www.sommarskog.se/share_data.html

Удивительно, как много людей работают с SQL Server без защиты, поскольку они не знают о транзакциях!

BEGIN TRAN
...
COMMIT / ROLLBACK

• В SQL Server 2008 (и в Oracle 10g ): MERGE .

  Одна команда для ] INSERT / UPDATE / DELETE в таблицу из источника строки.

• Чтобы сгенерировать список чисел от 1 до 31 (скажем, для календаря):

   С Cal AS
   (
   ВЫБЕРИТЕ 1 КАК день
   СОЮЗ ВСЕ
   ВЫБЕРИТЕ день + 1
   ОТ кал
   ГДЕ день <= 30
   ) 
  

• Одностолбцовый индекс с предложением DESC в кластеризованной таблице может использоваться для сортировки по столбцу DESC, cluster_key ASC :

   CREATE INDEX ix_column_desc ON mytable (column DESC )
  
  ВЫБЕРИТЕ ТОП 10 *
  ИЗ mytable
  СОРТИРОВАТЬ ПО
   столбец DESC, pk
  - Использует индекс
  
  ВЫБЕРИТЕ ТОП 10 *
  ИЗ mytable
  СОРТИРОВАТЬ ПО
   столбец, pk
  - Не использует индекс
  

• CROSS APPLY и OUTER APPLY : позволяет объединять источники строк, которые зависят от значений объединяемых таблиц:

   SELECT *
  ИЗ mytable
  КРЕСТНОЕ ПРИМЕНЕНИЕ
   my_tvf (mytable.column1) tvf
  
  ВЫБРАТЬ *
  ИЗ mytable
  КРЕСТНОЕ ПРИМЕНЕНИЕ
   (
   ВЫБЕРИТЕ ТОП 5 *
   ИЗ другой таблицы
   ГДЕ othertable.column2 = mytable.column1
   ) q
  

• EXCEPT и INTERSECT операторы: позволяют выбирать условия, которые включают NULL s

   DECLARE @ var1 INT
  ОБЪЯВИТЬ @ var2 INT
  ОБЪЯВИТЬ @ var3 INT
  
  НАБОР @ var1 = 1
  НАБОР @ var2 = NULL
  НАБОР @ var2 = NULL
  
  ВЫБЕРИТЕ col1, col2, col3
  ИЗ mytable
  ПЕРЕСЕЧЕНИЕ 
  ВЫБРАТЬ @ val1, @ val2, @ val3
  
  - выбирает строки с `col1 = 1`,` col2 IS NULL` и `col3 IS NULL`
  
  ВЫБЕРИТЕ col1, col2, col3
  ИЗ mytable
  КРОМЕ 
  ВЫБРАТЬ @ val1, @ val2, @ val3
  
  - выбирает все остальные строки
  

• Предложение WITH ROLLUP : выбирает общую сумму для всех сгруппированных строк

   SELECT month, SUM (продажа)
  ИЗ mytable
  ГРУППА ПО 
   месяц С ROLLUP
  
  Месяц SUM (продажа)
   --- ---
   10,000 января
   20 000 февраля
   30,000 марта
  NULL 60 000 - всего из-за `WITH ROLLUP`
  

ДЛЯ XML

Есть несколько способов получить дату без временного отрезка; вот тот, который достаточно эффективен:

SELECT CAST(FLOOR(CAST(getdate() AS FLOAT))AS DATETIME) 

Действительно для SQL Server 2008:

SELECT CAST(getdate() AS DATE) AS TodaysDate

Когда я впервые начал работать программистом, я начал с использования SQL Server 2000. Меня учили теории БД на Oracle и MySQL, поэтому я мало что знал о SQL Server 2000.

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

С этой аннотацией:

Так что я хотел добавить это к list:

 select Convert(varchar, getdate(), 101) -- 08/06/2009
 select Convert(varchar, getdate(), 110) -- 08-06-2009

Эти два я использую чаще всего. Есть еще несколько: CAST и CONVERT в MSDN

Почему мне хочется сказать JOINS?

Производные таблицы - одни из моих любимых. Они работают намного лучше, чем коррелированные подзапросы, но люди могут продолжать использовать вместо них коррелированные подзапросы.

Пример производной таблицы:

select f.FailureFieldName, f.RejectedValue, f.RejectionDate,
         ft.FailureDescription, f.DataTableLocation, f.RecordIdentifierFieldName,
         f.RecordIdentifier , fs.StatusDescription 
    from dataFailures f
    join(select max (dataFlowinstanceid) as dataFlowinstanceid 
            from dataFailures 
            where dataflowid = 13)a 
    on f.dataFlowinstanceid = a.dataFlowinstanceid
    join FailureType ft on f.FailureTypeID = ft.FailureTypeID
    join FailureStatus fs on f.FailureStatusID = fs.FailureStatusID

Пространственные данные в SQL Server 2008 , т. Е. Сохранение данных широты и долготы в типе данных geography и возможность вычислять / запрашивать, используя функции, которые идут вместе с ним.

Он поддерживает как плоские, так и геодезические данные.

WITH (FORCESEEK) , который заставляет оптимизатор запросов использовать только операцию поиска по индексу в качестве пути доступа к данным в таблице.

используйте ctrl-0, чтобы вставить нулевое значение в ячейку

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

Является ли представление быстрее, чем простой запрос?

Это звучит глупо, но я просмотрел много запросов, где просто задавал себе вопрос человек просто не знает, что такое GROUP BY? Я не уверен, что большинство разработчиков не знают об этом, но этого достаточно, что иногда мне интересно.

Документация .

К сожалению, но я пришел к выводу, что самая скрытая функция, о которой разработчики не знают, - это документация на MSDN. Возьмем, к примеру, глагол Transact-SQL, такой как RESTORE. BOL будет охватывать не только синтаксис и аргументы ВОССТАНОВЛЕНИЯ. Но это только верхушка айсберга, когда дело касается документации. BOL охватывает:

• углубленные основы восстановления: Понимание того, как восстановление и восстановление из резервных копий работает в SQL Server .
• сквозные сценарии развертывания стратегии восстановления: Реализация сценариев восстановления для баз данных SQL Server .
• проблемы, связанные с системными базами данных: Рекомендации по резервному копированию и восстановлению системных баз данных .
• оптимизация процедур восстановления: Оптимизация производительности резервного копирования и восстановления в SQL Server .
• понимание , как восстановить. Резервное копирование и восстановление разделов с инструкциями (Transact-SQL) .
• больше угловых случаев и необычных сценариев, есть такие примеры, как Пример: частичное восстановление только некоторых файловых групп (модель полного восстановления) .

Список можно продолжать и продолжать, и это всего лишь одна тема (резервное копирование и восстановление). Все функции SQL Server охватываются одинаково. Считайте, что не все получат подробные данные о резервном копировании и восстановлении, но все задокументировано, и для каждой функции есть разделы «Как сделать».

Объем доступной информации просто смехотворен. Тем не менее, документация - один из самых малоиспользуемых ресурсов,

Большинство разработчиков SQL Server должны знать и использовать производные таблицы и общие табличные выражения (CTE).

BACKUP LOG <DB_NAME> WITH TRUNCATE_ONLY

DBCC_SHRINKDATABASE(<DB_LOG_NAME>, <DESIRED_SIZE>)

Когда я начинал чтобы управлять очень большими базами данных на MS SQL Server, а файл журнала имел более 300 ГБ, эти утверждения спасли мне жизнь. В большинстве случаев сжатие базы данных не действует.