Листинг 1. Пример объявления с типом BIGINT.

declare @bi0 as bigint, @bi1 as bigint
set @bi0 = power(cast(-2 as bigint), 63)
set @bi1 = -(@bi0 + 1)
select @bi0 as ?Нижняя граница bigint?, @bi1 as ?Верхняя граница bigint?

Листинг 2. Пример использования BIGINT для типизации.

drop table #tblTypesTest
create table #tblTypesTest (ID bigint identity 
(9223372036854775806,   1) not null, [Название] 
nvarchar(100) null) 
GO
insert #tblTypesTest ([Название]) values(?ааа?) - @bi1 - 1
insert #tblTypesTest ([Название]) values(?bbb?) - @bi1
select * from #tblTypesTest

Листинг 3. Пример объявления переменной типа «таблица».

declare @t table (CustID nchar(5) NOT NULL primary key, 
		 Company nvarchar(40) NOT NULL unique, 
		 ContactName nvarchar(30) NULL, 
		 ContactTitle nvarchar(30) NULL, 
		 Country nvarchar(15) NULL, 
		 City nvarchar(15) NULL)

Листинг 4. Пример переноса записей из таблицы в переменную.

declare @CustID as nchar(5), @Company as nvarchar(40),
 @ContactName as nvarchar(30)
declare @ContactTitle as nchar(30), @Country as
 nvarchar(15), @City as nvarchar(15)
select top 1 @CustID = CustomerID, @Company =
 CompanyName,
	 @ContactName = ContactName,
 @ContactTitle = ContactTitle, 
	 @Country = Country, @City = City
 from Customers order by CustomerID
while rowcount_big() > 0 begin
 insert @t values (@CustID, @Company, @ContactName,
 @ContactTitle, 
	  @Country, @City)
 select top 1 @CustID = CustomerID, @Company =
 CompanyName,
	 @ContactName = ContactName,
 @ContactTitle = ContactTitle, 
	 @Country = Country, @City = City
 from Customers where CustomerID > @CustID order by
 CustomerID 
end

Листинг 5. Пример операций с переменной типа «таблица».

update @t set ContactName = ?Don Pedro? where CustID =
 ?ALFKI?
delete from @t where CustID = ?ANATR?
select * from @t

Листинг 6. Пример определения переменных типа SQL_VARIANT.

declare @i as bigint, @d as decimal(15,3), @f as float(25),
 @c as nvarchar(10), @dt as datetime, @m as money, @u
 as uniqueidentifier
select @i = 9223372036854775807, @d =
 1234567890.123, @f = 1.23456789E123, @c = ?abcde?,
 @dt = getdate(), @m = 12345.678, @u = newid()

Листинг 7. Пример использования переменных типа SQL_VARIANT.

create function fnShowVariantProps(@x as sql_variant)
returns table as
return (select @x as ?Переменная?, 
 sql_variant_property(@x, ?BaseType?) as ?Базовый тип?, 
 sql_variant_property(@x, ?Precision?) as ?Длина в цифрах?, 
 sql_variant_property(@x, ?Scale?) as ?После запятой?,
 sql_variant_property(@x, ?TotalBytes?) as ?Размер в байтах?,
 sql_variant_property(@x, ?Collation?) as ?Unicode Collation?,
 sql_variant_property(@x, ?MaxLength?) as ?Макс.длина строки?)

Листинг 8. Пример создания таблицы #MyObjects для хранения объектов класса.

create table #MyObjects (ObjectID uniqueidentifier default
 NewID() NOT NULL, Property1 int NULL, Property2 decimal(15, 3) NULL,
Property3 float NULL, Property4 nvarchar(10) NULL,
Property5 datetime NULL, Property6 smallmoney NULL)

Листинг 9. Пример работы с таблицей #MyObjects.

insert #MyObjects (Property1) values (5)
insert #MyObjects (Property5) values (?2000-06-07?)
insert #MyObjects (Property2, Property3, Property6) values (2.72, -1.2E-4, 1000)
insert #MyObjects (Property1, Property4) values (-10, ?abc?)
insert #MyObjects (Property2) values (3.14)
select * from #MyObjects

Листинг 10. Использование типа SQL_VARIANT для экономии пространства.

create table #MyObjects (ObjectID uniqueidentifier default NewID() NOT NULL,
 PropertyName varchar(10) NOT NULL,	
		 PropertyValue sql_variant NOT NULL)
declare @id as uniqueidentifier
insert #MyObjects (PropertyName, PropertyValue) values 
   (?Property1?, cast(5 as int))
insert #MyObjects (PropertyName, PropertyValue) values 
   (?Property5?, cast(?2000-06-07? as smalldatetime))
set @id = NewID()
insert #MyObjects (ObjectID, PropertyName, PropertyValue) values 
   (@id, ?Property2?, cast(2.72 as decimal(15, 3)))
insert #MyObjects (ObjectID, PropertyName, PropertyValue) values
   (@id, ?Property3?, cast(-1.2E-4 as float))
select ObjectID, PropertyName, PropertyValue, sql_variant_property
(PropertyValue, ?BaseType?) from #MyObjects

Листинг 11. Функция (скалярного типа) подсчета слов в строке и пример ее использования.

create function fnNumToken (@InputString as nvarchar(4000),
 @Delimiters as varchar(100)) returns int
as begin
 declare @i as int, @i_prev as int, @n as int
 set @Delimiters = @Delimiters + char(1)
 set @InputString = @InputString + right(@Delimiters, 1) 
 select @i = 1, @n = 0, @i_prev = 0 
 while @i <= len(@InputString) begin
 if charindex(substring(@InputString, @i, 1), @Delimiters)
<>  begin
 if @i > @i_prev + 1 set @n = @n + 1
 set @i_prev = @i
 end
 set @i = @i + 1
 end
 return @n
end 
select dbo.fnNumToken(?(095) 967-85-85?, ?() -?)
select CompanyName, ?Кол-во слов в поле? = dbo.fnNumToken(CompanyName,
 ? -?) from Northwind..Customers

Листинг 12. Табличная функция и пример ее использования.

create function fnTokens (@InputString as nvarchar(4000),
 @Delimiters as varchar(100))
returns @Tokens table (id int identity (1, 1) NOT NULL
 primary key clustered, Token nvarchar(4000) collate Cyrillic_General_CI_AS NULL)
as begin
 declare @c as nvarchar(1), @Token as nvarchar(4000)
 declare @i as int
 set @Delimiters = @Delimiters + char(1) 
 set @InputString = @InputString + right(@Delimiters, 1) 
 select @i = 1, @Token = ??
 while @i <= len(@InputString) begin
 set @c = substring(@InputString, @i, 1)
 if charindex(@c, @Delimiters) = 0
 set @Token = @Token + @c
 else
 if @Token > ?? begin 
 insert @Tokens (Token) values (@Token)
 set @Token = ??
 end
 set @i = @i + 1
 end 
 return
end
select * from fnTokens(?(095) 967-85-85?, ?() -?)
select CompanyName, ContactName, ContactTitle, City from Customers 
 inner join fnTokens(?ANTON, BERGS, DUMON, FOLKO, OCEAN?, ?, ?) as Tkn
 on Customers.CustomerID = Tkn.Token

Листинг 13. Подготовка отчета на основе данных из БД Northwind с помощью обычного представления.

if exists (select * from dbo.sysobjects where id = object_id(?vwSalesByProductCategory?) and OBJECTPROPERTY(id, ?IsView?) = 1)
 drop view vwSalesByProductCategory
GO
create view dbo.vwSalesByProductCategory as
select c.CategoryName, p.ProductName, sum((od.UnitPrice - od.Discount) * od.Quantity) as ?Продажи?
from [Order Details] as od 
 inner join Products as p on od.ProductID = p.ProductID
 inner join Categories as c on p.CategoryID = c.CategoryID
group by p.ProductName, c.CategoryName
having c.CategoryName like ?Grains%?

Листинг 14. Подготовка отчета на основе данных из БД Northwind с помощью функции in-line.

if exists (select * from dbo.sysobjects where id
 object_id(?fnSalesByProductCategory?) and xtype in (?FN?,?IF?,?TF?))
drop function [dbo].[fnSalesByProductCategory]
GO
create function fnSalesByProductCategory(@Category as varchar(30))
returns table as
return (select c.CategoryName, p.ProductName, 
  sum((od.UnitPrice - od.Discount) * od.Quantity) as ?Продажи?
 from [Order Details] as od 
  inner join Products as p on od.ProductID = p.ProductID
  inner join Categories as c on p.CategoryID = c.CategoryID
 group by p.ProductName, c.CategoryName
 having c.CategoryName like (@Category + ?%?))

Листинг 15. Пример использования операторов ON DELETE и ON UPDATE.

select CustomerID as CustID, CompanyName as Company,
 ContactName as Name, ContactTitle as Title, City, Country
 into Cust from Customers where (CustomerID < N?B?)
alter table Cust add constraint PK_Cust primary key (CustID)
select OrderID as OrdID, CustomerID as CustID, OrderDate
 as OrdDate into Ords from Orders where CustomerID in
 (select CustID from Cust)
alter table Ords add constraint PK_Ords primary key (OrdID),
 constraint FK_Ords foreign key (CustID) references Cust
 (CustID) on delete cascade on update cascade
select OrderID as OrdID, ProductID as ProdID, UnitPrice,
 Quantity into OrdDet from [Order Details] where OrderID
 in (select OrdID from Ords)
alter table OrdDet add constraint FK_OrdDet foreign key
 (OrdID) references Ords (OrdID) on delete cascade on
 update cascade

Листинг 16. Каскадное удаление.

select Cust.CustID, Ords.OrdDate, Ords.OrdID, OrdDet.ProdID, OrdDet.Quantity
from Ords inner join Cust on Ords.CustID = Cust.CustID 
  inner join OrdDet on Ords.OrdID = OrdDet.OrdID
order by Cust.CustID
delete from Cust where CustID = ?ALFKI?
select * from Ords order by CustID
select * from OrdDet where OrdID in (10643, 10692, 10702, 10835, 10952, 11011)

Листинг 17. Ошибочное каскадное удаление.

alter table OrdDet drop constraint FK_OrdDet 
alter table OrdDet add constraint FK_OrdDet foreign key
 (OrdID) references Ords (OrdID) on delete no action on update cascade
delete from Cust where CustID = ?ANATR?

Листинг 18. Новые параметры в OBJECTPROPERTY и два новых поля в INFORMATION._SCHEMA.REFERENTIAL_CONSTRAINTS.

select objectproperty(object_id(?FK_OrdDet?), ?CnstIsDeleteCascade?)
select objectproperty(object_id(?FK_OrdDet?), ?CnstIsUpdateCascade?)
select constraint_name, update_rule, delete_rule
 	from information_schema.referential_constraints

Листинг 19. Таблица с триггерами.

select top 5 CustomerID as CustID, CompanyName as Company,
 ContactName as Name, ContactTitle as Title, Country into Cust from Customers
create trigger CustTr1 on Cust after update as 
 if update(Company) select ?Поле Company было модифицировано?
go
create trigger CustTr2 on Cust after update as 
 if update(Name) select ?Поле Name было модифицировано?
go
create trigger CustTr3 on Cust after update as 
 if update(Title) select ?Поле Title было модифицировано?
go
create trigger CustTr4 on Cust after update as 
 if update(Country) select ?Поле Country было модифицировано?
go
update Cust set Company = ?Здравствуйте, я - ваша тетя?,
 Name = ?Donna Rosa?, Title = ?Your aunt?, Country = ?Brazil? where CustID = ?ANATR?

Листинг 20. Задание последовательность вызова триггеров.

exec sp_settriggerorder @triggername = ?CustTr4?, @order = ?first?, @stmttype = ?update?
exec sp_settriggerorder @triggername = ?CustTr1?, @order = ?last?, @stmttype = ?update?
select objectproperty(object_id(?CustTr4?), ?ExecIsFirstUpdateTrigger?) 
select objectproperty(object_id(?CustTr1?), ?ExecIsLastUpdateTrigger?)

Листинг 21. Создание таблицы и представления.

if exists (select * from information_schema.tables 
	 where table_type = ?BASE TABLE? and table_name = ?Empl?)
 drop table Empl
go
select * into Empl from Employees
create view vwEmpl as select EmployeeID as ID, FirstName
 + ? ? + LastName as Name, Title from Empl

Листинг 22. Создание триггеров INSTEAD OF.

create trigger trEmplIns on vwEmpl instead of insert as
 if @@rowcount = 0 return
 insert Empl (FirstName, LastName, Title)
 select left(Name, charindex(? ?, Name, 1) - 1), right(Name, len(Name)
 - charindex(? ?, Name, 1)), Title from inserted
go
create trigger trEmplUpd on vwEmpl instead of update as
 if not update(Name) return
 update Empl set FirstName = left(i.Name, charindex(? ?,
 i.Name, 1) - 1), LastName = right(i.Name, len(i.Name)
 - charindex(? ?, i.Name, 1)), Title = i.Title from inserted
 i inner join Empl on i.ID = Empl.EmployeeID

Листинг 23. Использование триггеров INSTEAD OF.

insert vwEmpl (ID, Name, Title) values(0, ?Donna Rosa?, ?Your aunt?) 
update vwEmpl set Name = ?Don Pedro?, Title = ?What a man he was? where ID = 1
select * from Empl order by EmployeeID
select * from vwEmpl order by ID

Листинг 24. Получение метаданных и определение типа триггера.

exec sp_help ?trEmplUpd? -> тип объекта
exec sp_helptext ?trEmplUpd? -> текст триггера (если не with encryption)
exec sp_depends ?trEmplUpd? -> на чем определен
select objectproperty(object_id(?CustTr4?), ?ExecIsAfterTrigger?)
select objectproperty(object_id(?trEmplUpd?), ?ExecIsInsteadOfTrigger?)

Листинг 25. Поиск триггеров для таблицы/представления.

exec sp_helptrigger ?vwEmpl? -> список триггеров на объект
select objectproperty(object_id(?vwEmpl?), ?HasInsteadOfTrigger?)
select objectproperty(object_id(?vwEmpl?), ?HasAfterTrigger?)
select objectproperty(object_id(?vwEmpl?), ?HasUpdateTrigger?)
select objectproperty(object_id(?vwEmpl?), ?HasInsertTrigger?)

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