(kl int identity,

timestamp timestamp)

DECLARE latsl timestamp, iats2 timestamp

SELECT iatsl=@@DBTS, iats2-iatsl

SELECT CASE WHEN TSEQUAL(iatsl, iats2) THEN Equal ELSE Not Equal END INSERT #testts DEFAULT VALUES SET iats2=iaiaDBTS

SELECT CASE WHEN TSEQUAL(gtsl, iats2) THEN Equal ELSE Not Equal END GO

DROP TABLE #testts

Equal

Server; Msg 532, Level 16, State 2, Line 16

The timestamp (changed to 0x0000000000000093) shows that the row has been updated by another user,

@@MICROSOFTVERSION

Эта автоматическая перементшя возвращает внутренний номер версии сервера, используемый Microsoft.

SELECT laMICROSOFTVERSION

117441211

Недокументированные флаги трассировки

Флаги трассировки представляют собой специальные настройки сервера, которые задаются при по.мощи команды DBCC TRACEON() или через опцию ко.мандтюй строки -Т сервера. Некоторые опции и.меют смысл только для всего сервера, поэтому их лучше указать как опцию командной строки. Больша>1 часть флагов, однако, устанавливается через DBCC TRACEON(flagnum), где flagnum - флаг, который необходимо установить. Для того чтобы установить более одного флага сразу, разделите их запяты.ми.

Для просмотра статуса флага служит команда DBCC TRACESTATUS(flagnum). Если ей передать -1, будут перечислены все установленные флаги трассировки. Вот простой пример:

EXEC master, .xpjogevent 99999.CHECKPOINT before setting flag

3502.informational

CHECKPOINT

DBCC TRACEON(3604.3502) DBCC TRACESTATUS(-l)

EXEC master, .xpjogevent 99999.CHECKPOINT aften setting flag

3502.informational

CHECKPOINT

DBCC TRACEOFF(3604.3502) DBCC TRACESTATUS(-l)

Вот как выглядит журнал ошибок сервера после вьиюлнения этих команд (флаг 3502 приводит к выводу расширенной информации о событиях CHECKPOINT):

1999-07-27 19:57:20.06 spidll Error: 99999. Severity: 10, State: 1 1999-07-27 19:57:20,06 spidll CHECK,d01NT before setting flag 3502, 1999-07-27 19:57:20,06 spidll DBCC TRACEON 3604, server process ID (SPID) 11, 1999-07-27 19:57:20,06 spidll DBCC TRACEON 3502, server process ID (SPID) 11, 1999-07-27 19:57:20,07 spidll Error: 99999, Severity: 10, State: 1 1999-07-27 19:57:20,07 spidll ChCCKPOlNT after setting flag 3502, 1999-07-27 19:57:20,0/ spidll Ckpt dbid 4 started (100000) 1999-07-27 19:57:20,07 spidll Ckpt dPid 4 phase 1 ended (100000) 1999-07-27 19:57:20,07 spidll Ckpt dbid 4 complete

1999-07-27 19:57:20,0/ spidll OBCC TRACEOFF 3604, server process ID (SPID) 11, 1999-07-27 19:57:20,07 spidll DBCC TRACEOFF 3502, server process ID (SPID) 11,

В табл. 20.4 лодержатся некоторые из флагов трассировки (обратитесь к Books Online за списком документированных). Этот список неполон - существует множество недоку.меитированиых флагов, не указанных в нем.

Таблица 20.4. Некоторые из недокументированных флагов трассировки SQL Server Флаг Значение

1200 Отображает детальную информацию о блокировках

1206 Дополняет флаг 1204, отображая другие блокировки, налагаемые участниками мертвой блокировки

2509 /1спользуется совместно с DBCC CHECKTABLE для просмотра общего числа фантомных записей таблицы

3502 Протоколирует расширенную информацию при наступлении события checkpoint в системный журнал ошибок

3607 Пропускает автоматическое восстановление баз данных

3608 Пропускает автоматическое восстановление баз данных, за исключением базы master

3609 Пропускает создание базы tempdb при старте сервера

8687 Отключает параллельное выполнение запросов (для многопроцессорных машин)

Недокументированные процедуры

Сущее гвует очень большое количество недокументированных процедур. По мои.м нодсчета.м, их npn.viepno сотия, не считая связа1нть[х с репл1Н<ацией. Я привел большую их часть в табл. 20.5. По мпоги.м причинам приведены пе все. Во-первых, их слишком много, чтобы описать соответствующи.м образом. Поэтому я ие описал недокументированные процедуры, связанные с ренликациег!. Также часто нeдoкy.vIeггиpoвaиныe процедуры столь опасны и столь .мало полезны, что лучше их не рассматривать. Наконец, некоторые недокументированные проце-ду1)ы работают ошибочно или так силь[10 зависят от внешнего по отношению к серперу кода (например, в Enterprise Manager и SQL-DMO), что они либо бесполезны, либо вызывают сомнения в [10лез[1ости у разработчика Transact-SQL. Смысл здесь в том, чтобы представт[ть обзор без лт[иших подробностей.

Таблица 20.5. Недокументированные системные и расширенные хранимые процедуры

Процедура

Назначение

sp checl<names [@mode]

sp delete bacl<uphistory @oldest date

sp enumerrorlogs

sp enumoledbdatasources

sp fixindex @dbname, @tabname, @indid

sp gettypestring @tabid,@colid, @typestring output

sp MS marksystemobject @objname

.sp MSaddguidcol

@source owner,

@source table

sp MSaddguidindex

@source owner,

@source table

sp MSaddlogin implicit nt login ©loginame

sp MSadduserJmplicit ntl ogin @ntname

sp MScheck uid owns any thing @uid

Проверяет ключевые системные таблицы на наличие He-ANSI-имен

Очищает системную историю резервного копирования до указанной даты

Перечисляет системные журналы ошибок текущего сервера

Перечисляет провайдеров OLEDB, доступных на сервере

Позволяет создавать и удалять индексы на системных таблицах

Выводит текстовое описание типа данных столбца

Устанавливает системный бит (ОхСООООООО). Некоторые функции и команды DBCC работают неправильно, если вызваны не из системного объекта. Установка системного бита приводит к тому, что значение свойства IsMSShipped равно 1

Добавляет ROWGUIDCOL-столбец к таблице.Также помечает таблицу для репликации (для того чтобы отменить это, используйте EXEC sp MSunmarkreplinfo)

Создает индекс на столбце ROWGUIDCOL таблицы

Добавляет учетную запись SQL Server, соответствующую существующей учетной записи NT

Добавляет пользователя базы данных, соответавующето существующей учетной записи NT

Возвращает 1, если пользователь является владельцем объектов в текущей базе данных

sp checknames @mode=silent

msdb..sp delete backuphistory @oldest date datetime

master..sp enumerrorlogs

sp enumoledbdatasources

USE Northwind

EXEC sp dboption Northwind, single, true

EXEC sp fixindex Northwind, sysobjects, 2

EXEC sp dboption Northwind, single, false

declare ©tabid int @typestr

varchar(30)

@tabid=OBJECT ID(authors) EXEC sp gettypestring @tabid, 1, @typestr OUTPUT

sp Ms marksystemobject sp dir

sp MSaddguidcolumn dbo, testguid

sp MSaddguidindex dbo, testuid

sp MSaddlogin implicit ntlogin GoofyTingler

sp MSadduser implicit ntlogin GoofyTingler

DECLARE @res int, @uid int

SELECT @uid = SUSER ID() EXEC @res = sp MScheck uid owns anything @uid

Продолжение

Таблица 20.5. Продолжение

Процедура

Назначение

sp MSdbuseraccess @mode=permdb, @qualdb name mask

sp MSdbuserpriv @mode=permserv]verrole

sp MSdependencies @objname, @objtype, ©flags int @objlist

sp MSdrop object ;@object id] ,@object name] ,@object owner]

sp MSexists file @full path, @filename

sp MSforeachdb @commandl @replacechar = ? [,@command2]

,@command3]

,@precommand]

,@postcommand]

sp msforeachtable @commandl @replacechar = ? [,@command2]

,@command3]

,@whereand]

,@precommand]

,@postcommand]

sp MSget oledbinfo ©server [,@login

,@infotype] [,@password]

sp MSget qualified name @object id,

@quaHfied name OUTPUT

sp MSget type @tabid, (Scolid, @colname OUTPUT @type OUTPUT

adJLrstright@tabl body = sp MSguidtostr @guid, @mystr OUTPUT

Возвращает список доступных пользователю баз данных и битовую маску, описывающую доступ в каждой базе

Возвращает битовую маску привилегий пользователя

Показывает зависимости объекта

Удаляет таблицу, представление, триггер или процедуру

Проверяет, существует ли указанный файл

Выполняет до трех команд для каждой из баз данных. @replacechar будет заменен именем базы данных, @precommand и @postcommand могут быть использованы для направления команд в единый результирующий набор

Выполняет до трех команд для каждой таблицы каждой базы данных (опционально соответствующей выражению @whereand)

Возвращает информацию о провайдере OLEDB для связанного сервера

Переводит ObjectlD в полное имя объекта

Возвращает имя и тип столбца таблицы

Возвращает значение типа uniqueidentifier как строку

sp MSdbuseraccess @mode = db

sp MSdbuserpriv @mode = role

sp MSdependencies @objname = titleauthor

sp MSdrop object @object name = authors2

DECLARE @resint

EXEC @res = sp MSexists file

d:\readme.txt

EXEC sp MSforeachdbDBCC

CHECKDB(?)

EXEC sp MSforeachdb

@commandl=PRINT Listing ? ,

@command2=USE ?; EXEC sp dir

EXEC sp MSforeachtable @commandl=EXEC sp help [?] EXECsp MSforeachtable @commandl=PRINT Listing ?=, @command2=SELECT * FROM ?,@whereand= AND name like title%

<$ [i n te rf a с e ta bl>s p M S g e t ole dbinfo @server=pythia, @login=sa

DECLARE @oid int, @obname sysname

SET @oid=DBJECT ID(titles) EXEC sp MSget qualified name @oid, @obname OUTPUT

DECLARE @tabid int, @colname sysname, @type nvarchar(4000) SET

@tabid=OBJECT ID(authors) EXEC sp MSget type ©tabid, 1, @colname OUTPUT @type OUTPUT

DECLARE @guid uniqueidentifier,

@guidstr sysname

SET @guid=NEWID()

EXEC sp MSguidtostr @guid,

@guidstr OUTPUT

Процедура

Назначение

sp MShelpindex @tablename [,@indexname] [,@flags]

sp MShelptype @typename] ,flags=sdtTuddtNULL]

sp MSindexspace

@tablename

[,@index name]

sp MSis pl< col @source table, @colname, @indid

sp MSI<iUdb @dbname

sp MSloginmappings @loginname

sp MStable has uniqueJn dex @tabid

sp MStablel<eys tablename] [,@colname]

,@type] [,@l<eyname]

;,@ftags]

sp Mstablerefs @tablename, @type=Nactualtables, @direction = Nprimary, @reftable

sp MStablespace [@name]

s p M S u П c to d rive @unc path, @local server, @locaLpath OUTPUT

sp MSuniquecolname table name, @base colname, @unique colname OUTPUT

sp Msuniquename @seed, ©start

Выводит информацию из каталога индексов

Выводит информацию из каталога типов

Возвращает информацию о размере индекса

Проверяет, является ли столбец первичным ключом

Удалят базу при помощи DBCC DBREPAIR (даже если база не повреждена)

Выводит соответствие между учетной записью, пользователем базы данных и псевдонимом

Проверяет, есть ли у таблицы уникальный индекс

Перечисляет ключи таблицы

Выводит список объектов, на которые ссылается таблица или которые ссылаются на таблицу

Выводит информацию о дисковом пространаве, занятом таблицей

Преобразует UNC-путь в диско-

Генерирует уникальное имя столбца для указанной таблицы на основе заданного базового

Возвращает уникальное имя объекта для текущей базы на основе значения @seed и начального значения

sp MShelpindex titles

EXEC sp MShelptype id EXEC sp MShelptype int.sdt EXEC sp MShelptype

EXEC sp MSindexspace titles

DECLARE @res int

EXEC @res=sp MSis pk col

titles,titlejd,l

sp MSkilldb northwind2

sp MSloginmappings

DECLARE @obJTd int @res int SET @objid=OBJECT ID(titles) EXEC

@res=s p MStable has u niquej ndex @objid

sp MStablekeys titles

sp MStablerefs titleauthor

sp MStablespace titleauthor

DECLARE @path sysname EXEC sp MSunc to drive \\PYTHIA\C$\, PYTHIA, @path OUTPUT

DECLARE @uniquename sysname EXEC sp MSuniquecolname titles, titlejd, @uniquename OUTPUT

sp MSuniquename titles, 3

Продолжение

Таблица 20.5. Продолжение

Процедура

Назначение

sp Msuniqueobjectname @name in, @name out OUTPUT

sp Msuniquetempname @namejn, @name out OUTPUT

sp readerrorlog [@lognum]

sp remove tempdb file @?lenaine

sp set locaLtime [@server name] [,@adjustmentjn minutes ] (forWinQx)

sp tempdbspace

xp dirtree rootpath

xp dsninfo @systemdsn

xp enum oledb providers

xp enumdsn

xp enumerrorlogs

xp fileexist filename

xp fixeddrives

xp get MAPI default profile

xp get MAPI profiles

xp getfiledetails ?lename

xp getnetname

Возвращает уникальное имя объекта для текущей базы

Возвращает уникальное имя временного объекта на основе базового

Выводит системный журнал ошибок с указанным номером. Если номер не указан, выводит текущий журнал ошибок

Удаляет файл, на котором основана база tempdb

Синхронизирует локальное время с указанным сервером

Возвращает информацию об использовании дискового пространства базой tempdb

Выводит полное содержимое каталогов и подкаталогов указанного пути

Выводит информацию ODBC DSN для указанного системного источника данных

Перечисляет доступные на сервере гуэовайдеры OLEDB

Перечисляет системные источники данных ODBC на сервере

Выводит список журналов ошибок сервера

Проверяет, существует ли указанный файл

Выводит список жестких дисков сервера

Возвращает имя профиля MAPI по умолчанию

Возвращает список имен системных профилей MAPI

Возвращает детальную информацию об указанном файле

Возвращает сетевое имя сервера

DECLARE @outname sysname SET @outname = - не может быть NULL

EXEC sp MSuniqueobjectname titles, @outname OUTPUT

CREATE TABLE tempdb..test(cl int)

DECLARE @outname sysname EXEC sp MSuniquetempname test, @outname OUTPUT

sp readerrorlog

master..sp remove tempdb file tempdev02

msdb..sp set locaLtime

sp tempdbspace

master..xp dirtree c:\

master..xp dsninfo pubsdsn

master..xp enum oledb providers

master..xp enumdsn

master..xp enumerrorlogs

master..xp fileexist d:\mssql7\install\readme.txt

master..xp fixeddnves

Master..xp get MAPI default profile

master..xp get MAPI profiles

master..xp getfiledetails d:\mssql7\install\readme.txt

master..xp getnetname

Заключение

Процедура

Назначение

xp oledbinfo @providername, @datasource, ©location, @providerstring, ©catalog, @login, ©password, @infotype

xp readerrorlog [lognum]

xp regaddmultistr

xp regdeletekey

xp regdeletevalue

xp regenumvalues

xp regread

xp regremovemultistring xp regwrite

xp subdirs

xp test MAPI profile proiile

xp varbintohexstr

Возвращает детальную информацию OLEDB для указанного связанного сервера

Возвращает набор (с1 char(255) с2 int), содержащий указанный журнал ошибок

Позволяет добавлять, изменять, удалять ключи и значения реестра

Выводит список подкаталогов первого уровня указанного каталога

Проверяет указанный профиль MAPI (то, что он существует и с ним можно установить соединение)

Преобразует переменную типа varbinary в строковое представление шестнадцатеричиого числа

master..xp oledbinfoSQLOLEDB, PYTHIA,NULU NULL, NULL,sa, drkildare, NULL

master..xp readerrorlog

EXEC master..xp regenumvalues HKEY LOCAL MACHINE, SOFTWARE\Microsoft\MSSQLServer\ MSSOLServer

DECLARE @dfnvarchar(64) EXECUTE master.dbo.xp regread NHKEY CURRENT USER, NContj;ol Panel\International, NsShortDate, @df OUTPUT, Nno output

master..xp subdirs D:\MSSQL7

master..xp test MAPI proiile SQL

CREATE PROC sp hex @i int @hx

varchar(30)

OUT AS

DECLARE @vb varbinary(30) SET @vb=CAST(@i as varbinary) EXEC master..xp varbintohexstr @vb, @hx OUTPUT GO

DECLARE @hex varchar(30) EXEC sp hex 343, @hex OUTPUT

Заключение

в этой главе рассмотрено множество флагов трассировки SQL Server, ко.манд DBCC, функций, переменных и храни.мых процедур, недокументированных в Books Online. Если вы решите использовать их в своей работе, делать это следует осторожно. Помните о возможных изменениях в новых выпусках SQL Server. Не следует ожидать поддержки со стороны Microsoft, поскольку недоку.ментированные возможности не поддерживаются, и их можно произвольно изменить. Применение недокументированных функций, в то.м числе и в SQL Server, ие рекомендуется. Не делайте этого без крайней необходимости.

Попурри

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

Г. В. Кентоп

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

Скрытые функции

Каждый выпуск SQL Server привносит новые функции в язык Transact-SQL. Сейчас их более пятидесяти. Не удивительно, что при таком большом количестве функций неаккуратршш разработчик может не знать о каких-то из них. Хотя эта книга не призвана заменить Books Online, некоторые из этих функций заслуживают упоминания, поскольку могут избавить от лишней работы.

Функции статуса

Функции статуса сообщают о рабочем окружении. У SQL Server множество таких функций. Вероятно, вы знакомы с некоторыми из них, с другими - нет. Вот несколько функций, выделяющихся среди прочих в плане редкости и полезности.

GETANSINULLO позволяет определить опцию но умолчанию для значения NULL базы данных. Эта опция управляется при помощи процедуры sp dboption через опцию ANSI null default, так же как и через ко.манды сессии SET ANSI NULL DFLT ON/SET ANSI NULL DFLT OFF. GETANSINULLO принимает необязательный параметр - имя базы.

Вот код, использующий GETANSINULLO:

DECLARE @ansirull int

-- Сохранить, чтобы потом восстановить

SET (Заns 1 null-GETANS1 NULL(tempdb)

IE ((?ansinull=C)

SET ANSI NULL DELT ON ON

CREATE TABLE #nulltest (cl int) INSERT #nulltest (cl) VALUES (NULL) SELECT * EROM #nulltest

IE (@ansinul1=0) - Вернуть прежнюю настройку SET ANSI NULL DELT ON OEE

Этот код использует GETANSINULL() для определения статуса ANSI null default до изменения значения. Далее создается временная таблица, состоящая из одного столбца, значение NULL по у.молчаиию для которого не указано, и в нее вставляется запись со значением NULL. Потом восстанавливается первоначальное значение.

HOST NAME(), GETDATEO и USER NAME() - также удобные фупкции статуса окружения. Вы часто будете встречать их при указании значений по умолчанию для столбцов, хотя их можно указывать и в списках SELECT, как видно из следующего фрагмента кода:

SELECT HOSTJAMEO

PUCKJEET

Здесь HOST NAME() применяется для того, чтобы получить имя рабочей станции. Другой частый пример использования этих функций - значения по умолчанию для столбцов. Вот пример:

CREATE TABLE #REPORT LOG (RepontLogld int identity PRIMARY KEY.

RepontDate datetime DEEAULT GETDATEO.

RepontUsen varchanOO) DEEAULT USERJAMEO.

ReportMaciiine varchanOO) DEEAULT HOSTJAMEO.

ReportName varcharOO) DEEAULT UNKNOWN)

INSERT #REPORT LOG DEEAULT VALUES SELECT * EROM #REPORT LOG

(результаты сокращены)

ReportLogId ReportDate ReportUser ReportMachine

1 1999-06-17 02:10:03.617 dbo PUCKJEET

Обратите внимание на при.менение конструкции INSERT...DEFAULT VALUES для добавления записей в таблицу с использованием только значения по умолчанию. Столбцы, допускающие значение NULL, будут содержать NULL при отсутствии значения по у.молчаиию во время добавления записи; столбцы, не допускающие значение NULL, в такой ситуации вызовут ошибку.

Обратите вии.мание, что можно было бы задействовать ANSI SQL-92-функ-ции CURRENT TIMESTAMP и CURRENT USER вместо GETDATEO и USER NAME() соот-

петстпенно. USER и SESSION USER являются сшюпи.ма.ми для CURRENT USER. ANSI-92-функции также могут применяться в выражениях SELECT; SELECT CURRENTJIMESTAMP. CURRENTJSER

1999-06-17 02:32:13.600 dbo

Функции SUSER NAME() и SUSER SNAME() удобны, когда необходн.мо указать в качестве значения по умолчанию для столбца и.мя учетгюй защюи текущего пользователя, а не са.мо имя пользователя в текущей базе данных (ANSI SQL эквивалент SYSTEM USER). Если ириложение использует только учетную запись sa и не при.меняет имен пользователей базы данных, хранение и.меии текущего пользователя в таблице лишено смысла. Оно всегда будет равно dbo. Хранение имени учетной записи текущего пользователя позволит отслеживать активность без необходимости создания отдельных пользователей базы дан1Ш1х. Функция SUSER NAME() включена в последний выпуск SQL Server только по соображе-ния.м обратной сов.местимости, в.место нее следует задействовать функцию SUSER SNAME(). Функция SUSER NAME() больше не включена в модель безогшс-иости SQL Server, поэтому ее применение не вызывает снижения производительности.

Функции свойств

Эти функции возврап[ают информапто об объектах в базе данных. 06i>i4ho это метаданные - данные о данных. Было вре.мя, когда даже для получения базовых метаданных требовалось исследование системных таблиц. К счастью, было добавлено достаточное количество функций, так что теперь эта пробле.ма не существует. Далее приведены наиболее интересные из них.

COLUMNPROPERTYO возвращает полезную инфо1)мацию о столбцах таблицы и napa.vieTpax хранимой процедуры. Эта функция принимает три параметра: идентификатор объекта (таблицы или хранимой процедуры - .\южно использовать функции OBJECrr ID() для получения опого), и.мя столбца или параметра, строковое выражение, указывающее на тип получаемой инфор.мации. За более подробной информацией .\южно обратиться к Books Online, но одно из са.мых интересных применений функции COLUMNPROPERTYO проиллюстрировано ниже:

CREATE TABLE #testfunc

(kl int identity PRIMARY KEY. cl decimal(10.2), c3 AS kl*cl) USE tempdb

SELECT C0LUMNPR0PERTY(0BJECT 1D(tftestfunc).kl,Isldentity). COLUMNPROPERTY(OBJECT ID(tftestfunc).сГ ,Scale). COLUMNPROPERTY(OBJECT ID(#testfunc).c3.IsComputed). COLUMNPROPERTY(OBJECT ID(testfunc).kl.AllowsNul1)

12 10

Обратите внимание иа нермслючение контексга на базу tempdb непосредственно перед вызовами функции COLUMNPROPERTYO. Это необходн.мо, так как рассматриваемый объект находится в базе данных tempdb, а COLUMNPROPERTYO не работает со ссылками в разных базах даниых.