ALTER DATABASE

ALTER DATABASE database_name
[ON [DEFAULT | database_device][=size]
[,database_device[=size]]...]
[FOR LOAD]

Пример

 ALTER DATABASE testing
     ON DEVICE1 = 8

ALTER TABLE

ALTER TABLE [database.[owner].]table_name
[WITH NOCHECK]
[ADD
 {col_name column_properties column_constraints]
 |[[,]table_constraint]}
    [,{next_col_name|next_table_constraint}]...]
| DROP [CONSTRAINT]
 constraint_name[,constraint_name]

Пример

1. Добавление PRIMARY KEY CONSTRAINT:

      ALTER TABLE authors
      ADD
      CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED (au_id)

2. Добавление FOREIGN KEY CONSTRAINT:

      ALTER TABLE titles
      ADD
      CONSTRAINT FK_pub_id FOREIGN KEY (pub_id) REFERENCES publishers(pub_id)

3. Добавление UNIQUE CONSTRAINT:

      ALTER TABLE titles
      ADD
      CONSTRAINT UNC_name_city UNIQUE NONCLUSTERED (stor_name,city)

4. Добавление DEFAULT CONSTRAINT:

    ALTER TABLE authors
    ADD
    DEFAULT 'UNKNOWN' FOR phone

   Будьте внимательны с default!
5. Добавление CHECK CONSTRAINT:

    ALTER TABLE authors
    ADD
    CONSTRAINT CK_zip CHECK (zip LIKE '[0-9][0-9][0-9][0-9][0-9]')

6. Добавление новой колонки

    ALTER TABLE publishers
    ADD
    country varchar(30)      NULL
            DEFAULT('USA')

Batches

 Пример

Несколько SELECT в одном batch'е

  SELECT COUNT(*) FROM titles
  SELECT COUNT(*) FROM authors

Выражения CASE

 CASE expression
 WHEN expression1 THEN exression1
 [[WHEN expression2 THEN expression2[..]]
 [ELSE expressionN]
 END

 CASE
  WHEN Boolean_expression1 THEN expression1
  [[WHEN Boolean_expression2 THEN expression2[..]]
  [ELSE expressionN]
  END

 COALESCE(expression1,expression2,...)
 NULLIF(expression1,expression2)
 ISNULL(expression1, expression)

 Примеры

 SELECT Category=
   CASE type
       WHEN 'popular_comp' THEN 'Popular Computing'
       WHEN 'mod_cook' THEN 'Modern Cooking'
       WHEN 'business' THEN 'Businness'
       WHEN 'psyhology' THEN 'Psyhology'
       WHEN 'trad_cook' THEN 'Traditional Cooking'
       ELSE 'Not yet categorized'
    END,
    'Shortended Tiitle' = CONVERT(varchar(30), title),
    Price = price
 FROM titles
 WHERE price IS NOT NULL
 ORDER BY type
 COMPUTE AVG(price) BY TYPE
 go

  Category               Shortedned Title               Price
  ---------------------- ------------------------------ -----------
  Business               Cooking with Computers: Surrep 11.95
  Business               Straight Talkk About Computers 19.99
  Business               The Busy Executive's Database  19.99
  Business               You Can Combat Computer Stress 2.99
                                                        

                                                        avg
                                                        ============
                                                        13.73

Оператор CHECKPOINT

 CHECKPOINT

Комментарии

/*Текст комментария*/

--Текст комментария

Поток управления

      BEGIN
       {sql_statement|statement_block}
      END
      

        :label
        GOTO label
       

       IF Boolean_expression
         {sql_statement|statement_block}
       [ELSE [Boolean_expression]
         {sql_statement|statement_block}
      

      RETURN ([integer_expression])
      

      WINTFOR {DELAY 'time'|TIME 'time'}
     

      WHILE Boolean_condition
        {sql_statement|statement_block}
        [BREAK]
         {sql_statement|statement_block}
        [CONTINUE]
     

Пример

 DECLARE @Name varchar(40)
 DECLARE CT CURSOR FOR SELECT Name from Peoples
 OPEN CT
 WHILE 1=1 BEGIN
  FETCH FROM CT INTO @Name
  /*Так, на мой взгляд, удобнее обходиться с курсорами*/
  IF @@fetch_status=-1 
      BREAK
  IF @@fetch_status=-2
      CONTINUE
  PRINT @Name
 END
 DEALLOCATE CT

CREATE DATABASE

 CREATE DATABASE database_name
 [ON [DEFAULT|database_device][= size]
       [,database_device][= size]]...]
 [LOG ON database_device[= size]
       [,database_device[= size]...]
 [FOR LOAD]

Пример

 CREATE DATABASE sales
 ON DEVICE3 = 125
 LOG ON DEVICE4 = 60

CREATE DEFAULT

 CREATE DEFAULT [owner.]default_name
 AS constant_expression

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

Пример

 CREATE DEFAULT  phonedflt AS 'unknown'
 sp_bindefault phonedflt,'authors.phone'

CREATE INDEX

 CREATE [UNIQUE][CLUSTERED|NONCLUSTERED]INDEX index_name
  ON [[database.]owner.]table_name(column_name[,column_name]...)
 [WITH]
  [FILLFACTOR = x]
  [[,] IGNORE_DUP_KEY]
  [[,] {SORTED_DATA | SORTED_DATA_REORG}]
  [[,] {IGNORE_DUP_ROW | ALLOW_DUP_ROW}]]
 [ON segment_name]

CLUSTERED - создавать кластеризованный индекс, т.е. такой индекс, при котором в листьях B-дерева, образующего индекс, находятся не ссылки на данные, а собственно страницы данных.

 FILLFACTOR - позволяет управлять заполнением страниц B-дерева индекса, задается в процентах, 100% - полное заполнение.

 Пример

 CREATE UNIQUE CLUSTERED INDEX au_id_ind
     ON authors (au_id)

Типы данных

Типы данных даты и времени

 При вводе данных используйте один из нескольких форматов, заключая значение в одиночные кавычки - " ' ". Если требуется получить секунды или миллисекунды - для этого применяется функция CONVERT. Существуют следующие типы даты и времени:

datetime

Этот тип данных имеет размер в 8 байт, т.е. два четырехбайтных целых - 4 байта на количество дней, прошедших или еще не наступивших с 1 января 1900, и 4 байта на число миллисекунд, прошедших с полуночи.
datetime может содержать даты с 1 января 1753 года и по 31 декабря 9999 года, с точностью в три тысячных секунды. По умолчанию datetime имеет значение 1 января 1900 года, полдень.

smalldatetime

Тип данных, во многом аналогичный datetime, но менее точный. Размер его - 4 байта, два байта на число дней, прошедших с 1 января 1900 года, и два байта на число минут с полуночи. Даты могут быть представлены в диапазоне с 1 января 1900 года и по 6 июня 2079 года, с точностью в минуту

Apr[il] [15][,] 1996 
Apr[il] 15[,] [19]96 
Apr[il] 1996 [15]
Apr[il] [19]96 15
[15] Apr[il][,] 1996 
15 Apr[il][,][19]96 
15 [19]96 apr[il]
[15] 1996 apr[il]
1996 APR[IL] [15]
[19]96 APR[IL] 15
1996 [15] APR[IL]

[0]4/15/[19]96 (mdy)
[0]4-15-[19]96 (mdy)
[0]4.15.[19]96 (mdy)
[04]/[19]96/15 (myd)

15/[0]4/[19]96 (dmy)
15/[19]96/[0]4 (dym)
[19]96/15/[0]4 (ydm)
[19]96/[04]/15 (ymd)

Денежные типы данных

money

Тип данных money в состоянии представлять числа в диапазоне от -922,337,203,685,477.5808 до +922,337,203,685,477.5807 с точностью в одну десятитысячную, имеет размер в 8 байт.

smallmoney

Тип данных smallmoney в состоянии представлять числа в диапазоне от -214,748.3648 до +214,748.3647, размер 4 байта.

Специальные типы данных

 Первый - bit - предназначен для представления целых чисел в диапазоне от 0 до 1, причем при вводе числа, отличного от 1, принимается значение, равное 1. Тип данных bit имеет размер в один байт, но при наличии нескольких полей типа bit в таблице они все будут упакованы вместе, например, если у нас есть 7 полей типа bit, то суммарное занимаемое ими пространство будет равно одному байту.

 timestamp имеет размер 8 байт и представлено как varbinary(8) 

Оператор DBCC

DBCC {
    CHECKALLOC [(database_name [, NOINDEX])] | 
    CHECKCATALOG [(database_name)] | 
    CHECKTABLE (table_name [, NOINDEX | index_id]) | 
    CHECKDB [(database_name [, NOINDEX])] | 
    CHECKIDENT [(table_name)] | 
    DBREPAIR (database_name, DROPDB [, NOINIT]) | 
    dllname (FREE) | 
    INPUTBUFFER (spid) | 
    MEMUSAGE | 
    NEWALLOC [(database_name [, NOINDEX])] | 
    OPENTRAN ({database_name} | {database_id}) 
        [WITH TABLERESULTS] | 
    OUTPUTBUFFER (spid) | 
    PERFMON | 
    PINTABLE (database_id, table_id) | 
    SHOW_STATISTICS (table_name, index_name) | 
    SHOWCONTIG (table_id, [index_id]) | 
    SHRINKDB (database_name [, new_size [, 'MASTEROVERRIDE']]) | 
    SQLPERF ({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} |
        {THREADS} | {LOGSPACE}) | 
    TEXTALL [({database_name | database_id}[, FULL | FAST])] | 
    TEXTALLOC [({table_name | table_id}[, FULL | FAST])] | 
    TRACEOFF (trace#) | 
    TRACEON (trace#) |
    TRACESTATUS (trace# [, trace#...]) | 
    UNPINTABLE (database_id, table_id) | 
    UPDATEUSAGE ({0 | database_name} [, table_name [, index_id]]) |
    USEROPTIONS} 
[WITH NO_INFOMSGS]

CHECKCATALOG [(database_name)]

Проверяет целостность системных таблиц и отношений между ними

CHECKTABLE (table_name [, NOINDEX | index_id])

Проверяет указанную таблицу, убеждаясь, что ее страницы и страницы указанного индекса корректно связаны. Если сегмент журнала расположен на отдельном устройстве, то запуск DBCC CHECKTABLE для таблицы syslogs позволит узнать размер использованного и свободного места в журнале.

CHECKDB[(table_name)]

Проводит проверку, аналогичную CHECKTABLE, но только для каждой таблицы и ее индексов в указанной базе данных, или в текущей, если никакая не указана.

CHECKIDENT[(table_name)]

Проверяет текущее значение IDENTITY для указанной таблицы и сравнивает его с маскимальным из фактически имеющихся, изменяя его в случае необходимости.

NOINDEX

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

DBREPAIR(database_name,DROPDB,NOINIT)

Удаляет указанную базу данных, обычно разрушенную, после чего уже невозможно получить к ней доступ. Если указана опция NOINIT, то тогда страницы данных, представляющих из себя базу данных, не модифицируются. DBREPAIR - это, собственно говоря, пережиток прошлого. Теперь для удаления разрушенной базы данных нормальные пацаны пользуются оператором DROP DATABASE. Если же и он не работает (во дают!), то следует воспользоваться хранимой процедурой sp_dbremove. Указаний, куда идти, если и это не помогает, Microsoft не дает, впрочем, большинство и так догадывается.

dllname(FREE)

Выгружает из памяти указанную библиотеку.

INPUTBUFFER(spid)

Возвращает первые 255 байт буфера SQL запроса для указанного spid.

MEMUASGE

Выдает данные об использовании памяти сервером.

NEWALLOC(database_name[,NOINDEX])

Проверяет корректность текущей или указанной бд, выдавая более подробный отчет, нежели чем CHECKALLOC, и не прерывая работы в случае нахождения ошибки.

!	Обратите внимание: CHECKALLOC или NEWALLOC могут сообщать об ошибке в том случае, если база данных испольщуется кем-то еще, так что старайтесь запускать проверку при минимальной загрузке или даже в однопользовательском режиме.

OPENTRAN({database_name|database_id}) WITH TABLERESULTS

Позволяет получить информацию об начатых, но незавершенных транзакциях, и о начатых, но не распространенных(distributed) транзакциях.

OUTPUTBUFFER(spid)

Позволяет просмотреть выходные данные для процесса spid

PERFMON

Позволяет получить статистику всех трех типов - IOSTATS, LRUSTATS и NETSTATS.

PINTABLE(database_id, table_id)

Помещает таблицу и связанные с ней индексы в оперативную память. Не следует считать, что PINTABLE прочитывает таблицу непосредственно в память; напротив, данные, к которым был доступ, помещаются в кеш, откуда уже не вытесняются до последующего вызова DBCC UNPINTABLE.

SHOW STATISTICS(table_name,index_name)

Позволяет получить статистику о распределении страниц в таблице

SHOWCONTIG(table_id,index_id)

Показывает фрагментированность таблицы

SHRINKDB (database_name [, new_size [, 'MASTEROVERRIDE']])

Позволяет сократить по возможности размер, занимаемый базой данных.

SQLPERF ({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} {THREADS} {LOGSPACE})

Позволяет просмотреть различного рода статистику

TEXTALL[({database_name|database_id})[,FULL|FAST])

Проверяет корректность размещения данных во всей базе данных в колонках типа text или image.

TEXTALLOC[({table_name|table_id},[FULL|FAST])]

Проверяет корректность размещения данных в колонках типа text или image.

TRACEOFF(trace#)

Выключает указанный флаг трассировки

TRACEON(trace#)

Включает указанный флаг трассировки

TRACESTATUS(trace#[,trace#...])

Показывает статус для указанных флагов трассировки

UNPINTABLE(database_id,table_id)

Объявляет указанную таблицу как нерезидентную в памяти сервера. Данные, однако, остаются в кеше до тех пор, пока они не будут сборошены на диск по тем или иным причинам.

UPDATEUASGE({0|database_name},table_name[,index_id])

Исправляет возможные неаккуратности в сообщениях о размере базы данных.

USEROPTIONS

Позволяет посмотреть список опций, установденных командой SET

WITH NO_INFOMSGS

Подавляет выдачу информационных сообщений (уровень с 1 по 10)

Пример

DBCC CHECKALLOC(pubs)
go
Checking pubs
Alloc page 0 (# of extent=32 used pages=58 ref pages=58)
Alloc page 256 (# of extent=26 used pages=37 ref pages=37)
Alloc page 512 (# of extent=14 used pages=40 ref pages=40)
Alloc page 768 (# of extent=1 used pages=8 ref pages=2)
Alloc page 1024 (# of extent=1 used pages=0 ref pages=0)
Alloc page 1280 (# of extent=1 used pages=0 ref pages=0)
Total (# of extent=75 used pages=143 ref pages=137) in this database
DBCC execution completed. If DBCC printed error messages, see your System Administrator.

CREATE PROCEDURE

CREATE PROCedure [owner.]procedure_name[;number]
    [(parameter1 [, parameter2]...[parameter255])]
[{FOR REPLICATION} | {WITH RECOMPILE}
    [{[WITH] | [,]} ENCRYPTION]]
AS sql_statements

parameter=
 @parameter_name datatype[=default][OUTPUT]

 Пользователь может создавать свои системные процедуры; они начинаются с символов sp_. При попытке выполнения такой процедуры она сначала ищется в текущей базе данных, в случае же неудачи - в базе данных master. Таблицы, используемые в системной процедуре, определямой пользователем, также сначала отыскиваются в текущей базе данных, и если это не удалось - в базе данных master.

Пример

CREATE PROCEDURE au_info_all
AS
SELECT au_lname, au_fname, title, pub_name
    FROM authors a, titles t, publishers p, titleauthor ta
        WHERE a.au_id = ta.au_id
        AND t.title_id = ta.title_id
        AND t.pub_id = p.pub_id

CREATE RULE

CREATE RULE [owner.]rule_name
AS condition_expression

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

Пример

CREATE RULE list_rule
AS 
@list IN ('1389', '0736', '0877')

CREATE TABLE

CREATE TABLE [database.[owner].]table_name 
(
    {col_name column_properties [constraint [constraint [...constraint]]]
    | [[,] constraint]}
        [[,] {next_col_name | next_constraint}...]
)
[ON segment_name]

column_properties =
datatype [NULL | NOT NULL | IDENTITY[(seed, increment)]]

datatype

Опрелеляет тип создаваемой колонки - как системный, так и определяемый пользователем.

IDENTITY[(seed, increment)]

Для колонки с таким свойством сервером автоматически генерируется возрастающая последовательность, начиная с seed и приращением increment.

!	Обратите внимание: Сервер не гарантирует непрерывность или уникальность значений - в реальных данных, лежащих в таблице могут появляться разрывы. Для избежания ошибок с уникальностью используйте ограничение PRIMARY KEY

 

constraint

Может включать в себя ограничение как для столбца, так и для всей таблицы. Всего на таблицу может быть не более одного PRIMARY KEY, не более чем 249 UNIQUE, не более чем 31 FOREIGN KEY (каждый из которых может ссылаться не более чем на 16 колонок), не более одного DEFAULT на колонку, и неограниченное число CHECK. Все эти ограничения могут находиться в одном операторе CREATE TABLE. Синтаксис этих ограничений таков:

PRIMARY KEY:
[CONSTRAINT constraint_name]
    PRIMARY KEY [CLUSTERED | NONCLUSTERED]
        (col_name [, col_name2 [..., col_name16]])
        [ON segment_name] 

UNIQUE:
[CONSTRAINT constraint_name]
    UNIQUE [CLUSTERED | NONCLUSTERED]
        (col_name [, col_name2 [..., col_name16]])
        [ON segment_name] 

FOREIGN KEY:
[CONSTRAINT constraint_name]
    [FOREIGN KEY (col_name [, col_name2 [..., col_name16]])]
        REFERENCES [owner.]ref_table [(ref_col [, ref_col2 
            [..., ref_col16]])]

DEFAULT:
[CONSTRAINT constraint_name]
    DEFAULT {constant_expression | niladic-function | NULL}
        [FOR col_name]

CHECK:
[CONSTRAINT constraint_name]
CHECK [NOT FOR REPLICATION] (expression)

PRIMARY KEY [CLUSTERED | NONCLUSTERED]

Определяет первичный ключ таблицы и тип индекса, который будет для него построен.

!	Обратите внимание: Все колонки, входящие в первичный ключ не могут быть объявлены как NULL. Если же этот параметр при созданни был опущен - для всех колонок принимается NOT NULL.

 

UNIQUE [CLUSTERED | NONCLUSTERED]

Определяет ограничение уникальности для колонки, и указывает тип индекса, создаваемого для этого. Хотя для UNIQUE и можно использовать поля, допускающие значения типа NULL, все же рекомендуется этого не делать.

 По умолчанию созжается некластеризованный индекс.

[FOREIGN KEY (col_name [, col_name2 [..., col_name16]])]
REFERENCES [owner.]ref_table [(ref_col
[, ref_col2 [..., ref_col16]])]

Создает ограничение FOREIGN KEY для таблицы. Число полей и их тип должны совпадать. FOREIGN KEY не может ссылаться на таблицу, находящуюся в другой базе данных.

!	Обратите внимание: Ограничение FOREIGN KEY, в отличие от PRIMARY KEY, НЕ СОЗДАЕТ ИНДЕКСА.

 

DEFAULT

Указывает значение, используемое по умолчанию. Это должно быть либо константное выражение, в котором допустимо использование т.н. niladic функций. Это

• USER
• CURRENT_USER
• SESSION_USER
• SYSTEM_USER
• CURRENT_TIMESTAMP

DEFAULT может использоваться для колонок любых типов, кроме timestamp или тех, для которых указано IDENTITY.

CHECK(expression)

Задает условие проверки

Указанное expression должно вычисляться в булево выражение, и если оно принимает значение FALSE - попытка добавления или изменения данных отвергается. При создании таблицы возможно указать только один CHECK, в дальнейшем же их число можно увеличить.

ON segment_name

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

!	Обратите внимание: Если в дальнейшем для этой таблицы будет создан CLUSTERED индекс без указания сегмента - все данные будут перенесены на DEFAULT сегмент.

 

NB	Обратите внимание: Вышеуказанным свойством можно пользоваться для перемещения таблицы по разным дискам и т.п.

 

Пример

CREATE TABLE employee 
(
    emp_id  empid
        CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
        CONSTRAINT CK_emp_id CHECK (emp_id LIKE 
            '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
            emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
        /* Each employee ID consists of three characters that 
        represent the employee's initials, followed by a five 
        digit number ranging from 10000 to 99999 and then the 
        employee's gender (M or F). A - (hyphen) is acceptable 
        for the middle initial. */
    fname   varchar(20)     NOT NULL,
    minit   char(1) NULL,
    lname   varchar(30)     NOT NULL,
    job_id  smallint        NOT NULL
        DEFAULT 1
        /* Entry job_id for new hires. */
        REFERENCES jobs(job_id),
    job_lvl tinyint
        DEFAULT 10,
        /* Entry job_lvl for new hires. */
    pub_id  char(4) NOT NULL
        DEFAULT ('9952')
        REFERENCES publishers(pub_id),
        /* By default the Parent Company Publisher is the company
        to whom each employee reports. */
    hire_date       datetime        NOT NULL
        DEFAULT (getdate())
        /* By default the current system date will be entered. */
)

CREATE TRIGGER

 CREATE TRIGGER [owner.]trigger_name
 ON [owner.]table_name
 FOR {INSERT, UPDATE, DELETE}
 [WITH ENCRYPTION]
 AS sql_statements 

 CREATE TRIGGER [owner.]trigger_name
 ON [owner.]table_name
 FOR {INSERT, UPDATE}
 [WITH ENCRYPTION]
 AS
 IF UPDATE (column_name)
 [{AND | OR} UPDATE (column_name)...] sql_statements

Таблицы INSERTED и DELETED

Пример

CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
    @max_lvl tinyint,
    @emp_lvl tinyint,
    @job_id smallint
SELECT @min_lvl = min_lvl, 
    @max_lvl = max_lvl, 
    @emp_lvl = i.job_lvl,
    @job_id = i.job_id
FROM employee e, jobs j, inserted i 
WHERE e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10) 
BEGIN
    RAISERROR ('Job id 1 expects the default level of 10.',16,-1)
    ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
    RAISERROR ('The level for job_id:%d should be between %d and %d.',
        16, -1, @job_id, @min_lvl, @max_lvl)
    ROLLBACK TRANSACTION
END

CREATE VIEW

CREATE VIEW [owner.]view_name
[(column_name [, column_name]...)]
[WITH ENCRYPTION]
AS select_statement [WITH CHECK OPTION]

Пример

CREATE VIEW titles_view
AS 
SELECT title, type, price, pubdate
    FROM titles

Курсоры

UPDATE table_name 
    SET column_name1 = 
            {expression1 | NULL | (select_statement)} 
        [, column_name2 = 
            {expression2 | NULL | (select_statement)}...] 
WHERE CURRENT OF cursor_name

DELETE FROM table_name 
WHERE CURRENT OF cursor_name

 DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR 
 FOR select_statement 
 [FOR {READ ONLY | UPDATE [OF column_list]}]

-m

В этом случае данные для курсора выгребаются асинхронно. Значение m представляет число уже выбранных записей.

n

Все данные получены, n - число записей

0

Курсор не открыт

FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n] FROM] cursor_name 
[INTO @variable_name1, @variable_name2, ...]

NEXT

Получает следующую запись

PRIOR

Получает предыдущую запись

FIRST

Получает первую запись

LAST

Получает последнюю запись

ABSOLUTE

Получает n-ю запись

RELATIVE

Получает n-ю запись относительно текущей

 Глобальная переменная @@FETCH_STATUS возвращает результат последнего fetch'а -

0

Все нормально, данные получены

-1

Курсор кончился

-2

Текущая строка не является более членом требуемого множества записей, заданного в операторе select при объявлении курсора.

 Оператор DEALLOCATE закрывает курсор и уничтожает все связанные с ним структуры данных.

Example

 DECLARE @Name varchar(40)
 DECLARE CT CURSOR FOR SELECT Name from Peoples
 OPEN CT
 WHILE 1=1 BEGIN
  FETCH FROM CT INTO @Name
  /*Так, на мой взгляд, удобнее обходиться с курсорами*/
  IF @@fetch_status=-1 
      BREAK
  IF @@fetch_status=-2
      CONTINUE
  PRINT @Name
 END
 DEALLOCATE CT

DECLARE

  DECLARE @variable_name datatype 
       [, @variable_name datatype...]

Пример

 DECLARE @I INT

DELETE

 DELETE [FROM] {table_name | view_name}
     [WHERE clause]

 В дополнение к стадартному синтаксису Transact-SQL имеет расширение:

 DELETE [FROM] {table_name | view_name}
     [FROM {table_name | view_name}
         [, {table_name | view_name}]...]
             [..., {table_name16 | view_name16}]] 
 [WHERE clause]

Пример

DELETE FROM titleauthor 
    FROM authors a, titles t
        WHERE a.au_id = titleauthor.au_id
        AND titleauthor.title_id = t.title_id
        AND t.title LIKE '%computers%'

DISK INIT

 DISK INIT 
     NAME = 'logical_name',
     PHYSNAME = 'physical_name',
     VDEVNO = virtual_device_number,
     SIZE = number_of_2K_blocks
 [, VSTART = virtual_address]

Пример

  
DISK INIT
    NAME = 'DEVICE2',
    PHYSNAME = 'd:\sqldata\device2.dat',
    VDEVNO = 2,
    SIZE = 8192

DISK REINIT

  DISK REINIT
   NAME = 'logical name',
   PHYSNAME = 'physical_name',
   VDEVNO = 'virtual_device_number',
   SIZE = number_of_2K_blocks
   [,VSTART = virtual_address]

DISK REFIT

 DISK REFIT

DROP DATABASE

DROP DATABASE database_name [, database_name...]

Пример

DROP DATABASE pubs, newpubs

DROP DEFAULT

DROP DEFAULT [owner.]default_name [, [owner.]default_name...]

Пример

sp_unbindefault 'authors.phone'
go
DROP DEFAULT phonedflt

DROP INDEX

DROP INDEX [owner.]table_name.index_name
[, [owner.]table_name.index_name...]

Пример

DROP INDEX authors.au_id_ind

DROP PROCEDURE

DROP PROCedure [owner.]procedure_name [, [owner.]procedure_name...]

 Группа процедур (т.е. процедуры с одинаковым именем, но с разным параметром ;number могут быть удалены одним оператором DROP

Пример

DROP PROCEDURE byroyalty

DROP RULE

DROP RULE [owner.]rule_name [, [owner.]rule_name...]

Пример

sp_unbindrule 'publishers.pub_id'
go
DROP RULE pub_id_rule

DROP TABLE

 DROP TABLE [[database.]owner.]table_name
 [, [[database.]owner.]table_name...]

Пример

DROP TABLE titles1

DROP TRIGGER

DROP TRIGGER [owner.]trigger_name [, [owner.]trigger_name...]

Пример

DROP TRIGGER employee_insupd

DROP VIEW

DROP VIEW [owner.]view_name [, [owner.]view_name...]

Пример

DROP VIEW titles_view

DUMP

DUMP DATABASE {dbname | @dbname_var}
        TO dump_device [, dump_device2 [..., dump_device32]]
    [WITH options 
        [[,] STATS [ = percentage]]]
  
Резеврное копирование журнала транзакций:
    DUMP TRANSACTION {dbname | @dbname_var} 
        [TO dump_device [, dump_device2 [..., dump_device32]]]
    [WITH {TRUNCATE_ONLY | NO_LOG | NO_TRUNCATE}
        {options}]

 Если же у вас переполнился лог до такой степени, что и TRUNCATE_ONLY не помогает - используйте опцию WITH NO_LOG

Пример

* The next day. */
DUMP TRANSACTION corporate
    TO DISK = '\\servername\sharename\directory\filename.ext'
    WITH NO_LOG
go

EXECUTE

EXEC[ute] 
{[@return_status =]
    {[[[server.]database.]owner.]procedure_name[;number] | 
        @procedure_name_var} 
    [[@parameter_name =] {value | @variable [OUTPUT]] 
        [, [@parameter_name =] {value | @variable [OUTPUT]}]...] 
    [WITH RECOMPILE]] 

EXEC[ute] ({@str_var | 'tsql_string'} [{@str_var | 'tsql_string'}...)}

Пример

Выражения

    constant | column_name | function | (subquery)}
    [{operator | AND | OR | NOT}
    {constant | column_name | function | (subquery)}...]

Функции

Аггрегативные функции

Аггрегативные функции возвращают суммарные значения. Вот список этих функций:

AVG	COUNT(*)	MIN
COUNT	MAX	SUM

Функции манипуляции датой и временем:

DATEADD	DATENAME	GETDATE
DATEDIFF	DATEPART

Математические функции

ABS	DEGREES	RAND
ACOS	EXP	ROUND
ASIN	FLOOR	SIGN
ATAN	LOG	SIN
ATN2	LOG10	SQRT
CEILING	PI	TAN
COS	POWER
COT	RADIANS

Niladic-функции

Эти функции возвращают различные системные значения.

CURRENT_TIMESTAMP	SYSTEM_USER
CURRENT_USER	USER
SESSION_USER

Функции для манипуляции со строками

LTRIM	SOUNDEX
ASCII	PATINDEX	SPACE
CHAR	REPLICATE	STR
CHARINDEX	REVERSE	STUFF
DIFFERENCE	RIGHT	SUBSTRING
LOWER	RTRIM	UPPER

Системные функции

COALESCE	HOST_NAME	OBJECT_NAME
COL_LENGTH	IDENT_INCR	STATS_DATE
COL_NAME	IDENT_SEED	SUSER_ID
DATALENGTH	INDEX_COL	SUSER_NAME
DB_ID	ISNULL	USER_ID
DB_NAME	NULLIF	USER_NAME
GETANSINULL	HOST_ID	OBJECT_ID

CONVERT

Функция CONVERT служит для преобразования различных типов данных.

GRANT

Полномочия на выполнение операторов:
 GRANT {ALL | statement_list} 
 TO {PUBLIC | name_list}
Полномочия на объекты:
GRANT {ALL | permission_list}
ON {table_name [(column_list)] | view_name [(column_list)] | 
stored_procedure_name | extended_stored_procedure_name}
TO {PUBLIC | name_list}

 По умолчанию полномочия даются системному администратору, владельцу базы данных, владельцу объекта или группе PUBLUC. Нектороые из них они могут передать другим пользователям. По умолчанию системный администратор имеет все полномочия, он может использовать хранимую процедуру sp_changedbowner для изменения владельца базы данных. Владелец базы данных или системный администратор могут передавать полномочия на сам оператор GRANT.

 Пользователи могут входит только в одну группу, исключая группу PUBLIC.

Для измения прав кроме оператора GRANT существует оператор REVOKE, имеющий следующий синтаксис:

Полномочия  на выполнение операторов:
REVOKE {ALL | statement_list} 
FROM {PUBLIC | name_list}
Полномочия на объекты:
REVOKE {ALL | permission_list} 
ON {table_name [(column_list)] | view_name [(column_list)] | 
stored_procedure_name | extended_stored_procedure_name} 
FROM {PUBLIC | name_list

1. Передается вместе с оператором CREATE DATABASE
2. Члены группы PUBLIC имеют полномочия создавать временные таблицы
3. По умолчанию относится к владельцу БД
4. Для определения прав на оператор DBCC см. Transact-SQL Reference, DBCC Statement
5. По умолчанию относится к владельцу хранимой процедуры
6. Передается вместе с полномочиями на SELECT
7. Передается вместе с полномочиями на UPDATE

GRANT SELECT
ON authors
TO public
go 
GRANT INSERT, UPDATE, DELETE
ON authors
TO Mary, John, Tom

Идентификаторы

• Все идентификаторы, начинающиеся с @, почитаются за локальные переменные.
• Все идентификаторы, начинающиеся с #, считаются именами временный объектов.
• В противном случае идентификаторы должны начинаться с символа латинского алфавита.
• По умолчанию в имени объектов не могут встречаться пробелы, но, используя режим "Quoted identifiers", это можно обойти. Для более подробного ознакомления с режимимом "Quoted identifiers" см. Transact-SQL Reference.

 Если вы указываете имя объекта не целиком, то сервер сначала пытается найти его среди объектов, которыми владеете вы, после этого производится попытка найти указанный объект как database.dbowner.name. Для определеня видимости хранимых процедур, начинающихся с символов sp_ см. раздел, посвященный оператору CREATE PROCEDURE

INSERT

INSERT [INTO]
    {table_name | view_name} [(column_list)]
{DEFAULT VALUES | values_list | select_statement}

• При указании значений конкретный полей вместо использования каких-либо значений можно использовать ключевое слово DEFAULT
• Вставка пустой строки приводит к добавлению пробела ' ', а не значения NULL

INSERT titles
    VALUES('BU2222', 'Faster!', 'business', '1389',
        NULL, NULL, NULL, NULL, 'ok', '06/17/87')

INSERT titles(title_id, title, type, pub_id, notes, pubdate)
    VALUES ('BU1237', 'Get Going!', 'business', '1389',
        'great', '06/18/86')

  
INSERT INTO newauthors
    SELECT *
        FROM authors
            WHERE city = 'San Francisco'

Ключевые слова

KILL

 KILL spid

 Пример

 KILL 15

LOAD

 Загрузка базы данных:
     LOAD DATABASE {dbname | @dbname_var}
         FROM dump_device [, dump_device2 [..., dump_device32]]
     [WITH options 
         [[,] STATS [ = percentage]]]
   
 Загрузка журнала транзакций:
     LOAD TRANSACTION {dbname | @dbname_var} 
         FROM dump_device [, dump_device2 [..., dump_device32]]
     [WITH options]
   
 Загрузка информации о резервной копии:
     LOAD HEADERONLY
         FROM dump_device

 Кроме восстановления после какого-либо сбоя и прочего несчаcтья (пожар, падение Тунгусского метеорита) можно также использовать LOAD DATABASE для переноса базы данных с различных устройств или серверов на другие.

Для подробного описания фрагментов DEVICES и работы с ними см. CREATE DATABASE Statement в Transact-SQL Reference.

 Для более подробного описания процедур резервного копирования см. соответсвующий раздел в Database Administration Companion.

 Пример

LOAD DATABASE corporate
    FROM tape_dev1
LOAD DATABASE corporate
    FROM TAPE = '\\.\tape0'

NULL

 Следует обратить внимание на то, что если вы создаете таблицу с колонками, допускающими NULL, то значение будут внутрисерверно преобразовываться в соответствии со следующей таблицей:

Операции

PRINT

PRINT {'any ASCII text' | @local_variable | @@global_variable}

 Пример

IF EXISTS (SELECT zip 
                FROM authors
                    WHERE zip = '94705')
PRINT 'Berkeley author'

RAISERROR

 RAISERROR ({msg_id | msg_str}, severity, state
 [, argument1 [, argument2]] )
 [WITH LOG]

 Пример

REATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
    @max_lvl tinyint,
    @emp_lvl tinyint,
    @job_id smallint
SELECT @min_lvl = min_lvl, 
    @max_lvl = max_lvl, 
    @emp_lvl = i.job_lvl,
    @job_id = i.job_id
FROM employee e, jobs j, inserted i 
WHERE e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10) 
BEGIN
    RAISERROR ('Job id 1 expects the default level of 10.',16,-1)
    ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
    RAISERROR ('The level for job_id:%d should be between %d and %d.',
        16, -1, @job_id, @min_lvl, @max_lvl)
    ROLLBACK TRANSACTION
END

REVOKE

Полномочия  на выполнение операторов:
 REVOKE {ALL | statement_list} 
 FROM {PUBLIC | name_list}
Полномочия  на объекты базы данных:
 REVOKE {ALL | permission_list} 
 ON {table_name [(column_list)] | view_name [(column_list)] | 
 stored_procedure_name | extended_stored_procedure_name} 
 FROM {PUBLIC | name_list}

 Пример

REVOKE CREATE TABLE, CREATE DEFAULT
FROM Mary, John

Условия поиска

{WHERE | HAVING} [NOT] Boolean_expression 
{WHERE | HAVING} [NOT] Boolean_expression {AND | OR}    Boolean_expression 
{WHERE | HAVING} [NOT] column_name IS [NOT] NULL
{WHERE | HAVING} [NOT] column_name join_operator column_name 
{WHERE | HAVING} [NOT] column_name [NOT] LIKE 'match_string' 
{WHERE | HAVING} [NOT] EXISTS (subquery) 
{WHERE | HAVING} [NOT] expression comparison_operator expression 
{WHERE | HAVING} [NOT] expression [NOT] BETWEEN expression AND  expression 
{WHERE | HAVING} [NOT] expression [NOT] IN (value_list | subquery) 
{WHERE | HAVING} [NOT] expression comparison_operator {ANY | ALL}   (subquery) 
{WHERE CURRENT OF cursor_name}

SELECT

SELECT [ALL | DISTINCT] select_list 
    [INTO [new_table_name]] 
[FROM {table_name | view_name}[(optimizer_hints)] 
    [[, {table_name2 | view_name2}[(optimizer_hints)] 
    [..., {table_name16 | view_name16}[(optimizer_hints)]]] 
[WHERE clause] 
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause] 
[FOR BROWSE]

INDEX = {index_name | index_id}

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

NOLOCK

Включает режим "грязного чтения",

HOLDLOCK

Указывает, что блокировки следует сохранять до конца транзакции.

UPDLOCK

Блокирует "блокировками обновления", а не разделяемыми блокировками, сохраняя их до конца транзакции.

TABLOCK

Блокирует таблицу в разделяющем режиме. Если используется вместе с HOLDLOCK, то блокировки сохраняюся до конца транзакции.

PAGLOCK

Блокирует разделяемой блокировкой на страничном уровне в тех случаях, когда блокировка была бы на всю таблицу.

TABLOCKX

Блокирует таблицу в исключающем режиме до конца BATCH или до конца транзакции.

FASTFIRSTROW

Указвает оптимизатору, что необходимо использовать некластеризованный индекс (конечно, если существует подходящий) для выборки данных в порядке, задаваемом ORDER BY даже в том случае, если оптимизатор, сравнив затраты на выборку с помощью индекса и произвольной выборкой и последующей сортировкой находит более выгодым использование сортировки.

SET

SET {
{{{ANSI_NULL_DFLT_OFF | ANSI_NULL_DFLT_ON}
| ARITHABORT 
| ARITHIGNORE 
| FMTONLY 
| FORCEPLAN 
| IDENTITY_INSERT [database.[owner.]]tablename 
| NOCOUNT 
| NOEXEC 
| OFFSETS {keyword_list} 
| PARSEONLY 
| PROCID 
| QUOTED_IDENTIFIER
| SHOWPLAN 
| STATISTICS IO 
| STATISTICS TIME} 
    {ON | OFF}} 
| DATEFIRST number 
| DATEFORMAT format 
| DEADLOCKPRIORITY {LOW | NORMAL}
| LANGUAGE language 
| ROWCOUNT number 
| TEXTSIZE number
| TRANSACTION ISOLATION LEVEL {READ COMMITTED | READ 
    UNCOMMITTED | REPEATABLE READ | SERIALIZABLE}}

ANSI_NULL_DFLT_OFF

Устанавливает режим обработки значений NULL, несовместимый с ANSI SQL. Колонки во вновь создаваемых таблицах создаются как NOT NULL.

ANSI_NULL_DFLT_ON

Отменяет режим, устанавливаемый ANSI_NULL_DFLT_OFF

ARITHABORT

Остававливает запрос, если во время его выполнения встретилась арифметическая ошибка, такая как деление на ноль или переполнение

ARITHIGNORE

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

FMTONLY

Возвращает только описание колонок, получающихся при выполнении запроса.

FORCEPLAN

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

IDENTITY_INSERT [database.[owner.]]tablename

Разрешает вставку данных в колонку со свойством IDENTITY в указанной таблице.

NOCOUNT

Подавляет выдачу сообщений о количестве строк таблицы, получающейся в качестве запроса. Глобальная переменная @@ROWCOUNT обновляется даже когда установлена опция NOCOUNT.

NOEXEC

Указывает только скомпилировать запрос, но не выполнять его.

OFFSETS

Возвращает смещение keyword_list. Используется только вместе с DB-Library

PARSEONLY

Отменяет компиляцию и выполнение запроса, только проверяет синтаксис.

PROCID

Возвращает ID хранимой процедуры клиентской части запроса. Используется вместе с DB-Library.

QUOTED_IDENTIFIER

Указывает, что в текущей сессии ' и " различны. Строки в двойных кавычках (") считаются ключевыми словами или именами объектов.

SHOWPLAN

Возвращает вместе с данными план выполнения запроса.

STATISTICS IO

Сообщает статистику по вводу-выводу.

STATISTICS TIME

Сообщает статистику по времени.

DATEFIRST number

Устанавливает первый день недели. По умолчанию стоит америкоидный - неделя начинается с воскресенья.

DATEFORMAT format

Устанавливает формат даты. Формат может быть mdy, dmy, ymd, ydm, myd, and dym. По умолчанию стоит америкоидный - mdy.
(все у них не как у людей!)

DEADLOCKPRIORITY {LOW | NORMAL}

Устанавливает режим убиваемости запросов в текущей сессии при взаимоблокировках. При LOW процесс считается наилучшей жертвой при их разрешении.

LANGUAGE language

Устанавливает язык сообщений об ошибках. По умолчанию, само собой, английский.

ROWCOUNT number

Ограничивает число строк таблиц, участвующих в запросе. Обратите внимание: - это относится ко всем таблицам, участвующим в запросе!

TEXTSIZE number

Устанавливает размер возвращаемый размер данных типа text. По умолчанию - 4 килобайта.

TRANSACTION ISOLATION LEVEL

Устанавливает уровень изолированности транзакций Допустимые значения:

• READ COMMITED
• READ UNCOMMITED
• REPEATABLE READ | SERIAZABLE

SETUSER

SETUSER ['username' [WITH NORESET]]

SETUSER 'mary'
go
GRANT SELECT ON computer_types TO joe
go
SETUSER

SHUTDOWN

SHUTDOWN [WITH NOWAIT]

 SHUTDOWN

Подзапросы

expression comparison_operator [ANY | ALL | SOME] (subquery)expression [NOT] IN (subquery)[NOT] EXISTS (subquery)
  

(SELECT [ALL | DISTINCT] subquery_select_list 
[FROM {table_name | view_name}[optimizer_hints] 
    [[, {table_name2 | view_name2}[optimizer_hints] 
    [..., {table_name16 | view_name16}[optimizer_hints]]] 
[WHERE clause] 
[GROUP BY clause]
[HAVING clause])

 declare @a varchar(80)
 select @a=(select first_name from employee ...)+
            ' получает '+
           (select convert(sum(pay)) from pays...)

TRUNCATE TABLE

TRUNCATE TABLE [[database.]owner.]table_name

TRUNCATE TABLE authors

UNION

SELECT select_list [INTO clause] 
    [FROM clause] 
    [WHERE clause] 
    [GROUP BY clause] 
    [HAVING clause] 
[UNION [ALL] 
SELECT select_list 
    [FROM clause] 
    [WHERE clause] 
    [GROUP BY clause] 
    [HAVING clause]...] 
[ORDER BY clause] 
[COMPUTE clause]

/* CORRECT */
SELECT cities = city FROM storeseast
UNION
SELECT city FROM stores
ORDER BY cities

UPDATE

UPDATE {table_name | view_name} 
SET [{table_name | view_name}] 
    {column_list 
    | variable_list
    | variable_and_column_list}
        [, {column_list2 
            | variable_list2
            | variable_and_column_list2}
            ... [, {column_listN 
                    | variable_listN
                    | variable_and_column_listN}]]
[WHERE clause]

• Изменяемые колонки не входят в кластеризованный индекс
• У таблицы не существует триггера на обновление
• Для обновления одной строки:
  1. Размер обновленной записи должен совпадать с размером необновленнной.
  2. Если на изменяемые колонки построен индекс, то он должен быть неуникальным некластеризованным и колонки имеют фиксированную длину.
  3. Обновляемые колонки могут находиться в некластеризованном уникальном индексе в том случае, если в условии WHERE было заданно точное соответствие и для поиска обновляемой строки был использован именно этот индекс.
  4. Следующая строка должна отличаться от обновляемой не более чем на 50% по размеру и иметь общее число discontiguous differing bytes меньшее или равное 24
  5. Обновляемая строка должна регулярно смазываться елеем, освященным епископом и не содержать богохульных или антисоветских данных.
• Для обновления многих строк они должны отвечать пунктам 1-3 и не иметь колонки типа timestamp

UPDATE {table_name | view_name}
SET [{table_name | view_name}] 
    {column_list 
    | variable_list
    | variable_and_column_list}
        [, {column_list2 
            | variable_list2
            | variable_and_column_list2}
            ... [, {column_listN 
                    | variable_listN
                    | variable_and_column_listN}]]
[FROM {table_name | view_name}
    [, {table_name | view_name}]...]
        [..., {table_name16 | view_name16}]] 
[WHERE clause]

 Ранее было невозможно одновременно получить старое значение колонки и присвоить ему новое, для этого приходилось использовать что-то вроде

BEGIN TRANSACTION
SELECT variable_name = column_name1
    FROM table_name
        WHERE column_name2 = expression
            HOLDLOCK
UPDATE table_name
    SET column_name1 = expression
        WHERE column_name2 = expression
COMMIT TRANSACTION

UPDATE table_name
    SET column_name1 = expression, variable_name = column_name1
        WHERE column_name2 = expression

UPDATE titles
    SET ytd_sales = 
        (select sum(qty)
            FROM sales
                WHERE sales.title_id = titles.title_id
                    AND sales.date IN (SELECT MAX(date) FROM sales))
    FROM titles, sales

UPDATE STATISTICS

UPDATE STATISTICS [[database.]owner.]table_name [index_name]

USE

 USE database_name

 Пример

 USE pubs