Скрытые возможности SQL Server
Каковы некоторые скрытые возможности SQL Server?
Например, недокументированные системные хранимые процедуры, уловки для выполнения вещей, которые очень полезны, но недостаточно документированы?
ответы
Спасибо всем за отличные ответы!
Хранимые процедуры
- sp_msforeachtable: запускает команду с '?' заменяется на каждое имя таблицы (v6.5 и выше)
- sp_msforeachdb: запускает команду с '?' заменяется на каждое имя базы данных (v7 и выше)
- sp_who2: точно так же, как sp_who, но с гораздо большей информацией для блоков устранения неполадок (v7 и выше)
- sp_helptext: если вам нужен код хранимой процедуры, просмотрите & UDF
- sp_tables: возвращает список всех таблиц и представлений базы данных в области видимости.
- sp_stored_procedures: вернуть список всех хранимых процедур
- xp_sscanf: считывает данные из строки в местоположения аргумента, указанные каждым аргументом формата.
- xp_fixeddrives:: найдите фиксированный диск с наибольшим свободным пространством
- sp_help: Если вы хотите знать структуру таблицы, индексы и ограничения таблицы. Также просмотров и UDFs. Сочетание клавиш Alt+F1
обрывки
- Возвращение строк в случайном порядке
- Все объекты базы данных пользователя по дате последнего изменения
- Только дата возвращения
- Найти записи, дата которых находится где-то внутри текущей недели.
- Найти записи, дата которых произошла на прошлой неделе.
- Возвращает дату начала текущей недели.
- Возвращает дату начала прошлой недели.
- Смотрите текст процедуры, которая была развернута на сервере
- Отбросьте все соединения с базой данных
- Контрольная сумма таблицы
- Контрольная сумма строки
- Удалите все процедуры в базе данных
- Правильно сопоставьте идентификаторы входа после восстановления
- Вызов хранимых процедур из оператора INSERT
- Найти процедуры по ключевому слову
- Удалите все процедуры в базе данных
- Запросите журнал транзакций для базы данных программно.
функции
- HashBytes ()
- EncryptByKey
- Команда PIVOT
Разное
- Экстра Строка Соединения
- TableDiff.exe
- Триггеры для событий входа в систему (Новое в пакете обновления 2)
- Повышение производительности с помощью сохраняемых вычисляемых столбцов (шт.).
- Настройка DEFAULT_SCHEMA в sys.database_principles
- Принудительная параметризация
- Формат хранения Vardecimal
- Выяснение самых популярных запросов в считанные секунды
- Масштабируемые общие базы данных
- Функция фильтрации таблиц / хранимых процедур в SQL Management Studio
- Флаги трассировки
- Номер после
GOповторяет партию - Безопасность с использованием схем
- Шифрование с использованием встроенных функций шифрования, представлений и базовых таблиц с триггерами
84 ответа
В Management Studio вы можете поместить число после маркера окончания партии GO, чтобы партия повторялась столько раз:
PRINT 'X'
GO 10
Напечатает 'X' 10 раз. Это может спасти вас от утомительного копирования / вставки при выполнении повторяющихся вещей.
Многие разработчики 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 представляет новые значения (после ОБНОВЛЕНИЯ), в то время как deleted ссылается на старые значения до ОБНОВЛЕНИЯ:
UPDATE (table)
SET field1 = value1, field2 = value2
OUTPUT inserted.ID, deleted.field1, inserted.field1
WHERE (condition)
Если будет возвращено много информации, вывод OUTPUT также может быть перенаправлен во временную таблицу или табличную переменную (OUTPUT INTO @myInfoTable).
Чрезвычайно полезно - и очень мало известно!
Марк
sp_msforeachtable: Запускает команду с '?' заменяется на каждое имя таблицы. например
exec sp_msforeachtable "dbcc dbreindex('?')"
Вы можете выполнить до 3 команд для каждой таблицы
exec sp_msforeachtable
@Command1 = 'print ''reindexing table ?''',
@Command2 = 'dbcc dbreindex(''?'')',
@Command3 = 'select count (*) [?] from ?'
Также, sp_MSforeachdb
Дополнительные параметры строки подключения:
MultipleActiveResultSets = истина;
Это позволяет ADO.Net 2.0 и выше считывать несколько наборов результатов только для пересылки и только для чтения на одном подключении к базе данных, что может повысить производительность, если вы много читаете. Вы можете включить его, даже если вы делаете смесь типов запросов.
Имя приложения =MyProgramName
Теперь, когда вы хотите просмотреть список активных соединений, выполнив запрос к таблице sysprocesses, имя вашей программы появится в столбце имя_программы вместо ".Net SqlClient Data Provider"
TableDiff.exe
- Инструмент Различия таблиц позволяет обнаруживать и согласовывать различия между исходной и целевой таблицей или представлением. Утилита Tablediff может сообщать о различиях в схеме и данных. Самая популярная особенность tablediff - это то, что он может генерировать скрипт, который вы можете запустить в месте назначения, чтобы согласовать различия между таблицами.
Менее известный метод TSQL для возврата строк в случайном порядке:
-- Return rows in a random order
SELECT
SomeColumn
FROM
SomeTable
ORDER BY
CHECKSUM(NEWID())
В Management Studio вы можете быстро получить разделенный запятыми список столбцов для таблицы:
- В обозревателе объектов разверните узлы под заданной таблицей (чтобы вы увидели папки для столбцов, ключей, ограничений, триггеров и т. Д.)
- Наведите указатель мыши на папку "Столбцы" и перетащите ее в запрос.
Это удобно, когда вы не хотите использовать отвратительный формат, возвращаемый путем щелчка правой кнопкой мыши по таблице и выбора "Сценарий таблицы как...", а затем "Вставить в...". Этот прием работает с другими папками, поскольку он дает вам разделенный запятыми список имен, содержащихся в папке.
Конструкторы строк
Вы можете вставить несколько строк данных с помощью одного оператора вставки.
INSERT INTO Colors (id, Color)
VALUES (1, 'Red'),
(2, 'Blue'),
(3, 'Green'),
(4, 'Yellow')
HashBytes() для возврата хеша своего ввода MD2, MD4, MD5, SHA или SHA1.
Если вы хотите знать структуру таблицы, индексы и ограничения:
sp_help 'TableName'
Выяснение самых популярных запросов
- С помощью sys.dm_exec_query_stats вы можете определить множество комбинаций анализа запросов по одному запросу.
Связь с комнадом
select * from sys.dm_exec_query_stats
order by execution_count desc
Я знаю, что это не совсем скрыто, но не так много людей знают о команде PIVOT. Мне удалось изменить хранимую процедуру, в которой использовались курсоры, и потребовалось 2 минуты, чтобы запустить быстрый 6-секундный фрагмент кода, который был в десять раз меньше числа строк!
Полезно при восстановлении базы данных для целей тестирования или чего-либо еще. Корректно сопоставляет идентификаторы входа в систему:
EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36'
Вместо написания сложных объединений и подзапросов эти два ключевых слова являются гораздо более элегантным сокращением и удобочитаемым способом выражения намерения вашего запроса при сравнении двух результатов запроса. Начиная с SQL Server 2005, они сильно дополняют UNION, который существует уже несколько лет в языке TSQL.
Концепции EXCEPT, INTERSECT и UNION являются фундаментальными в теории множеств, которая служит основой и основой реляционного моделирования, используемого всеми современными СУБД. Теперь результаты типа диаграммы Венна могут быть более интуитивно понятными и легко генерируемыми с помощью TSQL.
Я не уверен, является ли это скрытой функцией или нет, но я наткнулся на это и обнаружил, что это полезно во многих случаях. Вы можете объединить набор полей в одном операторе выбора, а не использовать курсор и циклически проходить через оператор выбора.
Пример:
DECLARE @nvcConcatonated nvarchar(max)
SET @nvcConcatonated = ''
SELECT @nvcConcatonated = @nvcConcatonated + C.CompanyName + ', '
FROM tblCompany C
WHERE C.CompanyID IN (1,2,3)
SELECT @nvcConcatonated
Результаты:
Acme, Microsoft, Apple,
Отбросьте все соединения с базой данных:
Use Master
Go
Declare @dbname sysname
Set @dbname = 'name of database you want to drop connections from'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End
Контрольная сумма таблицы
Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK)
Контрольная сумма строки
Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK) Where Column = Value
Если вам нужен код хранимой процедуры, вы можете:
sp_helptext 'ProcedureName'
(не уверен, что это скрытая функция, но я использую ее все время)
Хитрость хранимых процедур заключается в том, что вы можете вызывать их из оператора INSERT. Я нашел это очень полезным, когда я работал над базой данных SQL Server.
CREATE TABLE #toto (v1 int, v2 int, v3 char(4), status char(6))
INSERT #toto (v1, v2, v3, status) EXEC dbo.sp_fulubulu(sp_param1)
SELECT * FROM #toto
DROP TABLE #toto
В SQL Server 2005/2008 для отображения номеров строк в результате запроса SELECT:
SELECT ( ROW_NUMBER() OVER (ORDER BY OrderId) ) AS RowNumber,
GrandTotal, CustomerId, PurchaseDate
FROM Orders
ORDER BY является обязательным условием. Предложение OVER() указывает SQL Engine сортировать данные по указанному столбцу (в данном случае OrderId) и назначать числа в соответствии с результатами сортировки.
Полезно для анализа аргументов хранимой процедуры: xp_sscanf
Считывает данные из строки в местоположения аргумента, указанные каждым аргументом формата.
В следующем примере xp_sscanf используется для извлечения двух значений из исходной строки на основе их позиций в формате исходной строки.
DECLARE @filename varchar (20), @message varchar (20)
EXEC xp_sscanf 'sync -b -fproducts10.tmp -rrandom', 'sync -b -f%s -r%s',
@filename OUTPUT, @message OUTPUT
SELECT @filename, @message
Вот набор результатов.
-------------------- --------------------
products10.tmp random
dm_db_index_usage_stats
Это позволяет узнать, были ли недавно обновлены данные в таблице, даже если в таблице нет столбца DateUpdated.
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'MyDatabase')
AND OBJECT_ID=OBJECT_ID('MyTable')
Код от: http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/
Информация, на которую ссылаются: SQL Server - Какова дата / время последней вставленной строки таблицы?
Доступный в SQL 2005 и позже
Только дата возвращения
Select Cast(Floor(Cast(Getdate() As Float))As Datetime)
или же
Select DateAdd(Day, 0, DateDiff(Day, 0, Getdate()))
Бывают случаи, когда нет подходящего столбца для сортировки, или вам просто нужен порядок сортировки по умолчанию для таблицы, и вы хотите перечислить каждую строку. Для этого вы можете поместить "(выберите 1)" в предложение "упорядочить по", и вы получите то, что хотите. Аккуратно, а?
select row_number() over (order by (select 1)), * from dbo.Table as t
Найти записи, дата которых находится где-то внутри текущей недели.
where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ), 0 )
Найти записи, дата которых произошла на прошлой неделе.
where dateadd( week, datediff( week, 0, TransDate ), 0 ) =
dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )
Возвращает дату начала текущей недели.
select dateadd( week, datediff( week, 0, getdate() ), 0 )
Возвращает дату начала прошлой недели.
select dateadd( week, datediff( week, 0, getdate() ) - 1, 0 )
Сохранялся вычисленным-столбцы
- Вычисляемые столбцы могут помочь вам перенести стоимость вычислений во время выполнения на этап модификации данных. Вычисляемый столбец сохраняется с остальной частью строки и прозрачно используется, когда выражение в вычисляемых столбцах и запрос совпадают. Вы также можете создавать индексы на PCC для ускорения фильтрации и сканирования диапазона выражения.
Не столько скрытая функция, сколько настройка сопоставления клавиш в Management Studio в меню Инструменты \ Параметры \ Клавиатура: по умолчанию для Alt+F1 используется sp_help "выделенный текст", но я не могу жить без добавления Ctrl+F1 для sp_helptext "выделенный текст"
Вот некоторые функции, которые я считаю полезными, но многие люди не знают о них:
sp_tables
Возвращает список объектов, которые могут быть запрошены в текущей среде. Это означает любой объект, который может появиться в предложении FROM, кроме объектов синонимов.
sp_stored_procedures
Возвращает список хранимых процедур в текущей среде.
Вот простой, но полезный:
Когда вы редактируете содержимое таблицы вручную, вы можете вставить NULL в столбец, набрав Control-0.