T-SQL 2008

برای دیدن مطالب جدید به آدرس زیر مراجعه کنید :

http://sqlserver2008.persianblog.ir

شما محدود نشده اید که تنها از یک زیرعبارت در عبارت GROUP BY استفاده کنید. شما میتوانید چندین زیرعبارت را با استفاده از کاما مشخص کنید. کاما به عنوان اپراتور حاصلضرب استفاده میشود، بدین معنی که شما حاصلضرب دکارتی مجموعه ها را خواهید داشت. به عنوان مثال، کد زیر جایگزین حاصلضرب دکارتی بین دو مجموعه از زیرعبارات میشود:

GROUPING SETS ( (a, b), (c, d) ), GROUPING SETS ( (w, x), (y, z) )

این کد به صورت منطقی معادل است با :

GROUPING SETS ( (a, b, w, x), (a, b, y, z), (c, d, w, x), (c, d, y, z) )

شما میتوانید همچنین از زیرعبارات CUBE و ROLLUP به عنوان قسمتی از ضرب دکارتی استفاده نمایید.

این نسخه از SQL Server چندین پسوند برای Group By معرفی کرده که به شما این امکان را میدهد که به صورت همزمان چندین عملیات Grouping روی یک query داشته باشید. این پسوندها عبارتند از Grouping Sets و CUBE و ROLLUP که زیرعبارتهایی از عبارت Group By و توابع GROUPING_ID هستند. پسوندهای جدید استاندارد هستند و نباید با option های غیراستاندارد و قدیمی CUBE و ROLLUP اشتباه گرفته شوند.

زیر عبارات GROUPING SETS  و CUBE و ROLLUP
برای نمایش و معرفی این عبارات اجازه دهید به چند تکه کد اشاره کنیم:

USE tempdb;
IF OBJECT_ID('dbo.Orders', 'U') IS NOT NULL DROP TABLE dbo.Orders;
CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  orderdate DATETIME   NOT NULL,
  empid     INT        NOT NULL,
  custid    VARCHAR(5) NOT NULL,
  qty       INT        NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);
INSERT INTO dbo.Orders (orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20060802', 3, 'A', 10), (10001, '20061224', 1, 'A', 12),
  (10005, '20061224', 1, 'B', 20), (40001, '20070109', 4, 'A', 40),
  (10006, '20070118', 1, 'C', 14), (20001, '20070212', 2, 'B', 12),
  (40005, '20080212', 4, 'A', 10), (20002, '20080216', 2, 'C', 20),
  (30003, '20080418', 3, 'B', 15), (30004, '20060418', 3, 'C', 22),
  (30007, '20060907', 3, 'D', 30);

بدون پسوندها یک query به صورت نرمال فقط یک Grouping Set در عبارت Group By تعریف میکند. اگر شما بخواهید یک aggregate را روی چندین مجموعه از گروهها اعمال کنید شما به چندینquery نیاز خواهید داشت. و برای اینکه نتیجه را به صورت یکجا داشته باشید مجبور هستید که از UNION ALL استفاده نمایید.
با زیرعبارت جدید Grouping Sets شما به سادگی میتوانید تمام گروههایی را که میخواهید لیست کنید. به صورت منطقی شما دارید همان کار قدیم را انجام میدهید و نتیجه چندین query را یکی میکنید. البته شما با این روش دسترسی به داده و میزان محاسبات را بهینه میکنید. این بدین دلیل است که SQL SERVER به ازای هر مجموعه نیاز ندارد که داده ها را پیمایش کند، به علاوه در برخی موارد آن یک aggregate سطح بالاتر را بر اساس یک aggregate سطح پایینتر محاسبه میکند به جای اینکه دوباره داده های پایه را محاسبه کند.
به عنوان مثال، query زیر روی چهار مجموعه عملیات aggregate را انجام میدهد :

SELECT custid, empid, YEAR(orderdate) AS orderyear, SUM(qty) AS qty
FROM dbo.Orders
GROUP BY GROUPING SETS (
  ( custid, empid, YEAR(orderdate) ),
  ( custid, YEAR(orderdate)        ),
  ( empid, YEAR(orderdate)         ),
  () );

چهار سطر آخر مجموعه های تعریف شده هستند. آخرین آنها یعنی () یا مجموعه خالی جایگزین ALL یعنی همه میشود. این مانند یک aggregate query بدون عبارت GROUP BY است که شما با تمام جدول مانند یک گروه واحد رفتار کردید.
دو زیر عبارت جدید دیگر باید به عنوان مخفف در زیر عبارت Grouping Sets به کار روند. زیرعبارت CUBE یک مجموعه قدرتمند از مجموعه المانهای لیست شده در پرانتزها تولید میکند. به عبارت دیگر، این تمام مجموعه گروههای ممکن که میتوان با عناصر لیست شده در پرانتزها ساخته شود را تولید میکند که شما مجموعه خالی نیز میشود. به عنوان مثال استفاده زیر از CUBE :

CUBE(a,b,c)

منطقا معادل است با :

GROUPING SETS((a),(b),(c),(a, b),(a, c),(b, c),(a, b, c),())

برای n عنصر CUBE به ادازه 2 به توان n مجموعه گروه میسازد.

خارج از عناصر لیست شده در پرانتزها، زیرعبارت ROLLUP تنها مجموعه گروههایی را تولید میکند که دارای business value باشند، وراثت و سلسله مراتب را بین عناصر فرض کنید. به عنوان مثال استفاده زیر از ROLLUP :

ROLLUP( country, region, city )

منطقا معادل است با :

GROUPING SETS((country, region, city),(country, region),(country),())

دقت کنید مواردی که دارای business value نیستند، سلسله مراتب را بین عناصر فرض کنید- مانند (city) تولید نشده اند.ممکن است چندین شهر با یک نام در دنیا موجود باشد حتی ممکن است در یک کشور شهر همنام موجود باشد بنابراین هیچ business value برای اینکه آنها را مجتمع کنیم موجود نیست.

این عبارت جدید یک عبارت استاندارد است که سه عمل INSERT و UPDATE و DELETE را ترکیب کرده و بر اساس یک منطق شرطی یک عملیات تجزیه ناپذیر را انجام میدهد. استفاده از این عملیات تجزیه ناپذیر بهینه تر از استفاده همزمان از سه عملیات بالا به صورت مجزاست.
عبارت به دو جدول اشاره میکند: یک جدول هدف که در عبارت MERGE INTO مشخص میشود و جدول دیگر که جدول منبع است و در عبارت USING استفاده میشود. جدول هدف، هدفیست برای تغییرات و اصلاحات، و جدول منبع میتواند برای اصلاح هدف مورد استفاده قرار بگیرد.
سیمنتیک عبارت merge شبیه یک outer join است. شما با استفاده عبارت ON مشخص میکنید که کدام سطر از جدول هدف با کدام جدول منبع مطابقت دارد و کدام مطابقت ندارد. شما یک عبارت برای هر مورد دارید که تعیین میکند کدام عملیات انجام شود :

WHEN MATCHED THEN
 WHEN NOT MATCHED [BY TARGET] THEN
WHEN NOT MATCHED BY SOURCE THEN

دقت کنید که شما احتیاج ندارید که هر سه عبارت را مشخص کنید، بلکه فقط یک مورد نیاز است.
به همان اندازه که دیگر عبارات تغییر، عبارت MERGE نیز عبارت OUTPUT را حمایت میکند،‌که به شما این امکان را میدهد که مقادیری را از سطرهای تغییر یافته باز گرداند. به عنوان قسمتی از عبارت OUTPUT شما میتوانید متد $action را فراخوانی کنید تا عملیاتی که باعث تغییر سطر شده را مشخص کنید.( 'INSERT', 'UPDATE', 'DELETE')
به عنوان مثال کد زیر طریقه استفاده از عبارت MERGE را مشخص میکند. این کد دو جدول Customers و CustomersStage را در tempdb ساخته و آنها را با داده هایی پر میکند:

USE tempdb;
IF OBJECT_ID('dbo.Customers', 'U') IS NOT NULL DROP TABLE dbo.Customers;
CREATE TABLE dbo.Customers
(
  custid      INT         NOT NULL,
  companyname VARCHAR(25) NOT NULL,
  phone       VARCHAR(20) NOT NULL,
  address     VARCHAR(50) NOT NULL,
  CONSTRAINT PK_Customers PRIMARY KEY(custid)
);
INSERT INTO dbo.Customers(custid, companyname, phone, address)
  VALUES
  (1, 'cust 1', '(111) 111-1111', 'address 1'),
  (2, 'cust 2', '(222) 222-2222', 'address 2'),
  (3, 'cust 3', '(333) 333-3333', 'address 3'),
  (4, 'cust 4', '(444) 444-4444', 'address 4'),
  (5, 'cust 5', '(555) 555-5555', 'address 5');
 
IF OBJECT_ID('dbo.CustomersStage', 'U') IS NOT NULL
  DROP TABLE dbo.CustomersStage;
CREATE TABLE dbo.CustomersStage
(
  custid      INT         NOT NULL,
  companyname VARCHAR(25) NOT NULL,
  phone       VARCHAR(20) NOT NULL,
  address     VARCHAR(50) NOT NULL,
  CONSTRAINT PK_CustomersStage PRIMARY KEY(custid)
);
INSERT INTO dbo.CustomersStage(custid, companyname, phone, address)
  VALUES
  (2, 'AAAAA', '(222) 222-2222', 'address 2'),
  (3, 'cust 3', '(333) 333-3333', 'address 3'),
  (5, 'BBBBB', 'CCCCC', 'DDDDD'),
  (6, 'cust 6 (new)', '(666) 666-6666', 'address 6'),
  (7, 'cust 7 (new)', '(777) 777-7777', 'address 7');

عبارت MERGE زیر از جدول Customers به عنوان هدف برای تغییرات و از CustomerState به عنوان منبع استفاده میکند. شرط MERGE مشخصه custid  را در دو جدول بر هم منطبق میکند. وقتی یک تطابق در هدف یافت شد مشخصه های هدف Customer با مشخصه های منبع Customer، overwrite میشود. وقتی یک تطابق در هدف یافت نشود، یک سطر جدید در جدول هدف با استفاده از مشخصه جدول منبع insert میشود. وقتیکه یک تطابق در در جدول منبع یافت نشود، سطر customer هدف delete میشود.

MERGE INTO dbo.Customers AS TGT
USING dbo.CustomersStage AS SRC
  ON TGT.custid = SRC.custid
WHEN MATCHED THEN
  UPDATE SET
    TGT.companyname = SRC.companyname,
    TGT.phone = SRC.phone,
    TGT.address = SRC.address
WHEN NOT MATCHED THEN
  INSERT (custid, companyname, phone, address)
  VALUES (SRC.custid, SRC.companyname, SRC.phone, SRC.address)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
OUTPUT
  $action, deleted.custid AS del_custid, inserted.custid AS ins_custid;

این عبارت سطرهای 2 و 3 و 5 را update کرده، سطرهای 6 و 7 را insert و سطرهای 1 و 4 را delete میکند.

نسخه جدید SQL Server با معرفی این دو نوع پارامتر کدها را مختصر کرده و کارایی آن را نیز بالا میبرد. Table Type ها اجازه استفاده مجدد آسان تعریف جدول را با متغیر جداول مهیا میسازد و Table Valued اجازه پاس کردن یک table را با استفاده از پارامترها به stored procedure ها و function ها را به ما میدهد.

1- Table Type ها
این نوع اجازه میدهد که شما تعریف table را در پایگاه داده ذخیره نمایید و بعدا از آن برای تعریف متغیرهای table و پارامترها به stored procedure ها و function ها استفاده نمایید.به این دلیل که این نوع جدید به شما این امکان را میدهد که از تعریف table دوباره استفاده کنید، آنها پایداری (consistency) و کم کردن احتمال خطا را تامین میکنند.
شما باید از عبارت CREATE TYPE برای ساخت این نوع جدید استفاده کنید. به عنوان مثال کد زیر یک Table Type جدید بهپایگاه داده AdventureWork می افزاید :

USE AdventureWorks;
GO
CREATE TYPE dbo.OrderIDs AS TABLE
( pos INT NOT NULL PRIMARY KEY,
  orderid INT NOT NULL UNIQUE );
و در کد زیر از آن پس از تعریف استفاده شده است :
DECLARE @T AS dbo.OrderIDs;
INSERT INTO @T(pos, orderid) VALUES(1, 51480),(2, 51973),(3, 51819);
SELECT pos, orderid FROM @T ORDER BY pos;

برای دیدن اطلاعات metadata مربوط به table typeها روی view با نام sys.table_types یک Query بزنید.

2- پارامترهای Table-Valued
شما اکنون دیگر میتوانید از table type ها به عنوان یک type  برای پارامترهای ورودی Stored procedure ها و function ها استفاده نمایید. در حال حاضر پارامترهای table-valued به صورت read only هستند و شما باید در هنگام تعریف آنها را با کلمه کلیدی READONLY مشخص کنید.
سناریویی که در آن این پارامترها بسیار مفید هستند پاس کردن یک آرایه از کلیدها به stored procedure است. قبل از این نسخه راه حلی که برای این کار موجود بود استفاده از متد split بود که یک رشته را میتوانست تکه تکه کند. البته تکنیکهای دیگری نیز مثل استفاده از xml بود. استفاده از Dynamic SQL ریسک حملات SQL Injection را بالا میبرد. استفاده از split و xml هم بسیار پیچیده بود.

در این نسخه جدید با این نوع پارامترها کار بسیار ساده شده است. دیگر احتمال SQL Injection وجود ندارد و اجازه استفاده مجدد و مفید را از طرحهای اجرا را به ما میدهد. به عنوان مثال stored procedure زیر یک پارامتر از نوع مورد نظر را گرفته و خروجیهای مناسب را تولید میکند :

CREATE PROC dbo.usp_getorders(@T AS dbo.OrderIDs READONLY)
AS
SELECT O.SalesOrderID, O.OrderDate, O.CustomerID, O.TotalDue
FROM Sales.SalesOrderHeader AS O
  JOIN @T AS T
    ON O.SalesOrderID = T.orderid
ORDER BY T.pos;
GO
تکه کد زیر نیز این stored procedure را صدا میزند:
DECLARE @MyOrderIDs AS dbo.OrderIDs;
INSERT INTO @MyOrderIDs(pos, orderid)
  VALUES(1, 51480),(2, 51973),(3, 51819);
EXEC dbo.usp_getorders @T = @MyOrderIDs;

توجه کنید که وقتی شما مقداری را به عنوان پارامتر پاس نکنید به صورت پیش فرض یک جدول خالی به عنوان ورودی ارسال میشود. همچنین دقت داشته باشید که شما نمیتوانید متغیرها و پارامترهای table type را به صورت NULL پر کنید.
SQL Server 2008 همچنین Client API ها را برای تعریف و پر کردن پارامترهای table valued بهبود بخشیده است. پارامترهای table valued به صورت داخلی مانند متغیرهای table کار میکنند. این نوع پارامترها در بعضی موارد از جداول موقت (temporary table) و راه حلهای دیگر بهتر است :

•    آنها strong type هستند.
•    SQL Server برای اینها آماری از distribution نگه نمیدارد. به همین دلیل باعث recompilation نمیشود.
•    اینها با transaction rollback تحت تاثیر قرار نمیگیرند.
•    آنها یک مدل برنامه نویسی ساده را تهیه کرده اند.

شما میتوانید از چندین متد دیگر برای دستکاری در نوع داده HIERARCHYID استفاده کنید. از جمله Parse، GetReparentedValue،Read و Write.
با استفاده از متد HIERARCHYID::Parse میتوانید یک رشته با فرمت درست را به یک HIERARCHYID تبدیل کنید درست مثل تابع CAST.
با استفاده از متد GetReparentedValue میتواند گره والد یک گره تغییر داد. این متد دو آرگومان ورودی دارد، یکی @old_root و دیگری @new_root. فرض کنید مقدار منطقی و مسیر گره فعلی به صورت روبرو باشد /1/1/2/3/2/ و مقدار old_root /1/1/ باشد و مقدار new_root برابر /2/1/4/ باشد، در این صورت این متد مقدار /2/1/4/2/3/2/ را باز خواهد گرداند. دقت داشته باشید که این متد نیز تضمین نکرده که مقدار برگشتی یکه باشد.
متدهای Read و Write نیز فقط در CLR قابل استفاده است. از آنها برای خواندن از  BinaryReader و  نوشتن روی BinaryWriter استفاده کرد. شما میتوانید با استفاده از CAST حتی مقادیر رشته ای معادل HIERARCHYID را به آن تبدیل میکند و بالعکس.

اگر شما روی hid جستجو کنید خروجی به صورت binary است و اطلاعات با معنایی برای شما ندارد. برای اینکه به شکل منطقی تری دست پیدا کنید از متد ToString استفاده کنید. اگر این کار را کنید مسیری از سطوح را به شما میدهد که با اسلش از هم جدا شده است. به عنوان مثال کد زیر هر دو نوع binary و معادل منطقی آن را به شما میدهد :

SELECT hid, hid.ToString() AS path, lvl, empid, empname, salary
FROM dbo.Employees
ORDER BY hid;

فراخوانی HIERARCHYID یک مرتب سازی topological را میسازد و از متد GetLevel برای بدست آوردن سطح میتوانید استفاده کنید. با استفاده از این میتوانید یک ترسیم گرافیکی از ساختار سلسله مراتبی بدست آورید. به راحتی بر اساس hid مرتب سازی کنید و مانند زیر بر اساس lvl مقدار تورفتگی را بدست آورید:

SELECT
  REPLICATE(' | ', lvl) + empname AS emp,
  hid.ToString() AS path
FROM dbo.Employees
ORDER BY hid;

برای گرفتن یک زیر درخت از employee، شما میتوانید از متد IsDescendantOf کمک بگیرید. این متد اگریک گره به عنوان نواده گره مورد نظر باشد مقدار یک (1) را بر میگرداند. به مثال زیر دقت کنید:

SELECT C.empid, C.empname, C.lvl
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON P.empid = 3
    AND C.hid.IsDescendantOf(P.hid) = 1;

از کد زیر برای بدست آوردن تمامی مدیران یک employee (افراد بالای سری) استفاده میشود:

SELECT P.empid, P.empname, P.lvl
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON C.empid = 14
    AND C.hid.IsDescendantOf(P.hid) = 1;

از متد GetIncestor برای بدست آوردن والد یک گره استفاده کنید. این متد یک عدد را  که آن را n میخواند به عنوان ورودی میگیرد و یک HIERARCHYID که مربوط به گره والد با n سطح بالاتر آن است را میدهد. به عنوان مثال کد زیر یک زیر درخت با یک سطح پایینتر از employee با شماره 9 را میدهد:

SELECT C.empid, C.empname
FROM dbo.Employees AS P
  JOIN dbo.Employees AS C
    ON P.empid = 9
    AND C.hid.GetAncestor(1) = P.hid;