. -

.NET -

Yukon ao. T-SQL.

o o a oa ao o oo. a, o o ? ao a, oo o oa MSSQL - YUKON, o mssql2000 o ao o o 5 . o oo. a aa o.

o o oo o o , o a: An Overview of SQL Server "YUKON" for the Database Developer. oo o oo . o a Enterprise Manager Query Analyzer o Mssql Workbench, o Whidbey, .. Visual Studio. oo o intellisense. o o o oo a DTS. o ao, o a o oo a oo o .NET Framework. a a a, a view (a oa, a). o ... oo extended procedures o aao oo .Net, a a o, YUKON, oa ao, ao extended o, oo oa .Net assembly o T-SQL o o a , a a a . O o. a a ao aaoaa YUKON. o oaa, perfomance, .. mssql oo oa aaa . oo o.

oo oao, a oo T-SQL, a o a YUKON a o oa ao ao ao ao oaoa ASP.NET o. a oo asp.net a ao ao aa oo ao oa. aa oo, a YUKON oo a a.

oo a ooa:


1. Table Paging ( )


a a web developer ao oo aa ooo aa

a, a o ooa

declare @Page int
declare @PageSize   int
set @Page = 500
set @PageSize = 10

   declare @RowsCount int
   declare @FirstSelectingRowNumber int
   declare @FirstSelectingRowId  int
   select @RowsCount = count(Id) from table1
   set @FirstSelectingRowNumber = (@Page - 1) * @PageSize + 1
   if (@FirstSelectingRowNumber <= @RowsCount)
         SET ROWCOUNT @FirstSelectingRowNumber
         SELECT @FirstSelectingRowId = id
         FROM table1
         ORDER BY 1

         SET ROWCOUNT @PageSize
         SELECT * FROM table1 
         WHERE id >= @FirstSelectingRowId  
         ORDER BY 1     

oo , aa rowcount, aa o a top, oo. oo oo o o aoo o . oo o o ao aa a:

50 = lines per page

select top 50 
from (select top 50 * 10 *  
from table1 order by id asc) as t order by id desc

o ooo aaa join in, a a o inline view

oa: top mssql2000 aoa aaa, oo oaa, o , o ooa aa, oo ooa a sql, a o ao ao oooo oa ooo. oo oao: o a o, oo ao , o a a, oo oo, ao aa oa ao ooa oo a o o oa a, o oo oo oa oo.

YUKON a o a o oao. Top a aoa aaa:

declare @num int
set @num = 10
select top(@num) *
from products 
order by productid

a o :

delcare @p_page_size int
declare @p_page int

select top(@p_page_size) 
from (
        select top(@p_page_size * @p_page) *  
        from table1 order by id asc
     ) as t 
order by id desc

oo: top oo ooa insert/update/delete a:

delete top(10000) from orders

a o o ? ao a a 1 aa oo tranzaction log oo 1 a o 10000 mssql2000 oo ao o oo rowcount, o top o aoa.




aa: " , . . "

Create Procedure GetCategory
    @Parent_ID int
Create table #cat(
    cat_id int,        --  
    Parent_Id int,     -- 
    TopParent_ID int   --    
--  ,   
Insert into #Cat (cat_Id, Parent_Id, TopParent_Id)
    Select id, id, id From Category Where Parent_Id = @Parent_Id
While @@RowCount > 0
    Insert into #Cat (Cat_Id,Parent_Id, TopParent_Id) 
        (Select Category.Id, Category.Parent_ID, cat.TopParent_Id 
        From Category 
        Inner Join #Cat cat ON Category.Parent_ID = cat.Cat_ID
        Where Not Exists (     Select 1 
                  From #Cat 
                Where #Cat.Parent_ID = Category.Parent_ID 
                    and #Cat.Cat_ID = Category.ID )
--      cat_id  
--     TopParent_id    
--         .
Select TopParent_Id as Category_ID, max(cat.name) as Catetory_Name, Count(*) as Product_Councount
    From #Cat 
    Inner Join Category cat ON #Cat.TopParent_ID = cat.ID
        Inner Join Tovary tov ON #Cat.Cat_ID = tov.Category_ID
    Group By #Cat.TopParent_ID
    Order BY cat.Name 

o ao , o o ooo oo. E oo a a, a, o o ao ooo oo oo o oo/aoo o , imho o o a o rdbms, a o o oa a A Look at SQL Trees

, o a oo a, oo a, a ooa ao a o , a o o oo a o o aoo.

YUKON a o oo CTE (common table expression) - oa a T-SQL: oa o aoa inline view derived table

a CTE:

CTE o a o o aoa, a, oa o oo a oa oo o derived table o ooo aoa o oa o. o o aa CTE

aa Northwind (o oo aao o o o oa):

with YearlyOrdersCTE(orderyear, numorders)
   select year(orderdate), count(*)
   from orders
   group by year(orderdate)

   PrevYear.numorders as prev
   YearlyOrdersCTE CurYear left join YearlyOrdersCTE PrevYear on
   CurYear,orderyear = PrevYear.orderyear + 1
order by 1 desc

o ao oa o, oo ao oo o o oo view, o oa o a

ao CTE oo a view derived table o , . ao oa o (join 2 o CTE)

a, o oo o , a .. T o , ooo aa "o CTE", o ooaa a:


with myCTE(field names)
     < select sttmt for anchor member >
     union all
     < select sttmt for the recursive member 
        (joins the result of the previous step) >

select * from myCTE


declare @startid int
set @startid = 1

with categoryCTE (catid, parentid, catname, level)
   from Category
   where id = @startid

   union all

      CCTE.level + 1
      Category C join categoryCTE CCTE on 
      C.parent_id = CCTE.id

select * from categoryCTE

o o a ao o

catid parentid catname level
----- -------- ------- -----
1     < null > cat1    0
2     1        cat2    1
3     1        cat3    1
4     1        cat4    1
5     1        cat5    1
6     4        cat6    2
7     4        cat7    2
8     4        cat8    2

a CTE 2 aoa, a union all. o oa. oo a a CTE, o ao o o aa CTE. o oo o o, oa ao o oaa.


3 - ID


aaa oa a id xml a ooaa:

declare @doc varchar(1000) 
declare @idoc int 

set @doc ='< root> 
         < user id="1" /> 
         < user id="4" /> 
         < user id="8" /> 
         < user id="16" /> 
      < /root>' 

exec sp_xml_preparedocument @idoc OUTPUT, @doc 
FROM OPENXML (@idoc, '/ROOT/User',2) WITH (UserID int '@ID') 

oao - o a ooo dll (Msxml2.dll), o o a o ao o o, ooo o a o ooa.

Note  A parsed document is stored in the internal cache of SQL Server 2000. The MSXML parser uses one-eighth the total memory available for SQL Server. To avoid running out of memory, run sp_xml_removedocument to free up the memory.

YUKON o o datatype - xml a oo aa

create table mydocs
   docid int not null,
   docXML xml not null 

O o o o -a , -a xml o o datatype, a a user-defined datatype oa, oa o oo oo oao aoo xml. o:

query -  o oo   ao
value -  o a
exist -  query oaa o a, oaa true,
        a false
modify -  update

oo a o oa, oo oao a o a oo ooa ooaa:

declare @doc xml 

set @doc ='< root> 
         < user id="1" /> 
         < user id="4" /> 
         < user id="8" /> 
         < user id="16" /> 
      < /root>' 

a a a table-valued function aoo F_TBL_VALS_FROM_STRING (. ooa), o o o oo oa value xml a oa a o a.

o a oo, o a o aaa a (o F_TBL_VALS_FROM_STRING) o a ooo dll (o "- ID ")

xml type a , o oo a o xml, oo aa oo aoa xmlscheme (, o o o a a a) o oo oa check constraint a oo, o a xml oa


4 Date Time. o o a.


Ha o a aa oa:

oa, . o o oo . MS SQL table o a datetime. a a a-o a. a o o o o a : a SELECT * FROM Table WHERE Datum='18.06.03'. o a o ao 18.06.03 ao o . oo o aa: SELECT * FROM Table WHERE Datum>='18.06.03 00:00.0000' AND Datum <='18.06.03 24:59.9999', o o o a. aa ao.

H a aoo o o datatypes, o oo a o( a a ao ooo a a) datatypes: date/time - a o a a o oo.


create table mytable
   mydate date,
   mytime time

insert into mytable
      cast('2003-12-31' as date),
      cast('23:59:59' as time)

print 'happy new year'

a a o o oa YUKON CLR, o a, o o oo o .Net Framework, a:

select mydate::ConvertToString('dd/MM/yyyy') as converteddate
from mytable

o o. o... o mssql2000 o aao:

   convert(varchar(10), mydate, 103) as mydate,
   convert(varchar(5), mydate, 108) as mytime,
from mytable


5 a. E oa T-SQL.


B a oo o o T-SQL, oo oa a aa ao a, o, imho o oo oo o, a o

1. Error Handling run-time oo (try/catch)


Ca . oao:

set xact_abort on

begin try
   begin tran
      < sql sttmt 1 >
      < sql sttmt 2 > 
   end tran
end try

begin catch tran_abort
   declare @err as int
   set @err = @@error
   if @err = ... print 'a error'
   else if @err = ... print 'b error'
   else print 'other error'
end catch

o oo o a , o, oo a o mssql2000 ao :

   begin tran

   < sql sttmt 1 >

   if (@@error <> 0)
      rollback tran

   < sql sttmt 2 >
   if (@@error <> 0)
      rollback tran
   commit tran

2. Cross Apply

ooa a join a tabled-value function, o , ooa oaa table, a a o aao a a ooo oa a join/apply a, ao aa o, ooa aoo a n o aao. o a o top aao.

create function fun
(@customerid int, @num int) returns table
   select top(@num) orderid 
   from orders
   where customerid = @customerid
   order by orderdate desc 

create procedure proced
(@num int)
      customers c cross apply fun(c.customerid, @num) f
   order by 1  

3. pivot

o oao, oo oo aa oo o aooo.

a ao, oo oa oo aoo, a 1980, 1981, 1982, a o o a:

1980   1981   1982
----   ----   ----
1      10     2 


select year(hiredate)
from emp
pivot(count(*)) for year(hiredate) in ([1980], [1981], [1982])

count(x) aa a, a o o a for - aa ooo.

aoo ( a), a o ao a PL/SQL:

   sum(decode(to_char(hiredate, 'YYYY'), 1980, 1, 0)) "1980",
   sum(decode(to_char(hiredate, 'YYYY'), 1981, 1, 0)) "1981",
   sum(decode(to_char(hiredate, 'YYYY'), 1982, 1, 0)) "1982"
from emp;

Ta imho, T-SQL, o oo oo o ( a o ooa) - o: oaa waitfor ddl triggers, oa oo a o ddl oa.

Bo . aa aoao aoa o a ooaa.


: Anatoly Lubarsky
: 3065
: 1 2 3 4 5

: (0)


2007, .