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

Ответ 1

FileStream в SQL Server 2008: функция FILESTREAM SQL Server 2008 позволяет хранить и эффективно осуществлять доступ к данным BLOB с использованием комбинации 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

Ответ 2

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

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

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

  WITH   cal AS
          (
          SELECT  1 AS day
          UNION ALL
          SELECT  day + 1
          FROM    cal
          WHERE   day <= 30
          ) 
  

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

  CREATE INDEX ix_column_desc ON mytable (column DESC)
  
  SELECT  TOP 10 *
  FROM    mytable
  ORDER BY
          column DESC, pk
  -- Uses the index
  
  SELECT  TOP 10 *
  FROM    mytable
  ORDER BY
          column, pk
  -- Doesn't use the index
  

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

  SELECT  *
  FROM    mytable
  CROSS APPLY
          my_tvf(mytable.column1) tvf
  
  SELECT  *
  FROM    mytable
  CROSS APPLY
          (
          SELECT  TOP 5 *
          FROM    othertable
          WHERE   othertable.column2 = mytable.column1
          ) q
  

  Операторы

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

  DECLARE @var1 INT
  DECLARE @var2 INT
  DECLARE @var3 INT
  
  SET @var1 = 1
  SET @var2 = NULL
  SET @var2 = NULL
  
  SELECT  col1, col2, col3
  FROM    mytable
  INTERSECT 
  SELECT  @val1, @val2, @val3
  
  -- selects rows with `col1 = 1`, `col2 IS NULL` and `col3 IS NULL`
  
  SELECT  col1, col2, col3
  FROM    mytable
  EXCEPT 
  SELECT  @val1, @val2, @val3
  
  -- selects all other rows
  

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

  SELECT  month, SUM(sale)
  FROM    mytable
  GROUP BY 
          month WITH ROLLUP
  
  Month   SUM(sale)
    ---        ---
    Jan        10,000
    Feb        20,000
    Mar        30,000
   NULL        60,000  -- a total due to `WITH ROLLUP`
  

Ответ 3

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

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

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

SELECT CAST(getdate() AS DATE) AS TodaysDate

Ответ 4

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

BEGIN TRAN
...
COMMIT / ROLLBACK

Ответ 5

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

Ответ 6

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

Город | Государство | Zipcode

Хьюстон | Техас | 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, Бомонт Техас, Огайо

Ответ 7

Многие разработчики SQL Server все еще не знают о разделе OUTPUT (SQL Server 2005 и новее) в инструкции DELETE, INSERT и UPDATE.

Очень полезно знать, какие строки были INSERTED, UPDATEd или DELETEd, а предложение OUTPUT позволяет сделать это очень легко - он позволяет получить доступ к "виртуальным" таблицам с именем inserted и deleted ( как в триггерах):

DELETE FROM (table)
OUTPUT deleted.ID, deleted.Description
WHERE (condition)

Если вы вставляете значения в таблицу с полем первичного ключа INT IDENTITY, с предложением OUTPUT вы можете сразу получить вставленный новый идентификатор:

INSERT INTO MyTable(Field1, Field2)
OUTPUT inserted.ID
VALUES (Value1, Value2)

И если вы обновляете, может быть чрезвычайно полезно узнать, что изменилось - в этом случае inserted представляет новые значения (после UPDATE), а deleted ссылается на старые значения перед UPDATE:

UPDATE (table)
SET field1 = value1, field2 = value2
OUTPUT inserted.ID, deleted.field1, inserted.field1
WHERE (condition)

Если много информации будет возвращено, вывод OUTPUT также может быть перенаправлен на временную таблицу или переменную таблицы (OUTPUT INTO @myInfoTable).

Очень полезно - и очень мало известно!

Марк

Ответ 8

"Information_Schema" дает мне много мнений, которые я могу использовать для сбора информации о таблицах, процедурах, представлениях, и др.

Ответ 9

Если вы используете Management Studio 2005, вы можете автоматически выполнить запрос в качестве транзакции. В новом окне запроса перейдите в Query- > Query Options. Затем щелкните вкладку ANSI (слева). Проверьте SET IMPLICIT_TRANSACTIONS. Нажмите "ОК". Теперь, если вы запустите любой запрос в этом текущем окне запроса, он будет запущен как транзакция, и вы должны вручную выполнить ROLLBACK или COMMIT, прежде чем продолжить. Кроме того, это работает только для текущего окна запросов; предварительно существующие/новые окна запросов должны быть установлены.

Я лично нашел это полезным. Однако это не для слабонервных. Вы должны помнить ROLLBACK или COMMIT ваш запрос. Он будет НЕ сказать, что у вас есть ожидающая транзакция, если вы переключитесь на другое окно запроса (или даже новое). Однако он скажет вам, если вы попытаетесь закрыть окно запроса.

Ответ 10

PIVOT и UNPIVOT

Ответ 11

FOR XML

Ответ 12

BACKUP LOG <DB_NAME> WITH TRUNCATE_ONLY

DBCC_SHRINKDATABASE(<DB_LOG_NAME>, <DESIRED_SIZE>)

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

Прежде чем запускать их, обязательно сделайте полную резервную копию LOG и после выполнения их для полной резервной копии БД (последовательность восстановления больше не действительна).

Ответ 13

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

Ответ 14

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

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

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

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

Количество доступной информации просто нелепо. Тем не менее, документация является одним из самых недоиспользуемых ресурсов, поэтому я проголосовал за то, что это скрытая функция.

Ответ 15

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

Является ли просмотр быстрее простого запроса?

Ответ 16

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

Ответ 17

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

Ответ 18

Почему я соблазнился сказать 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

Ответ 19

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

Ответ 20

Spacial Data в SQL Server 2008, т.е. сохранение данных Lat/Long в типе географии и возможность вычисления/запроса с использованием функций, которые соглашайтесь с ним.

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

Ответ 21

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

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

С этим аннотатом:

Итак, я хотел добавить это в список:

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

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