آموزش T-SQL 2008

[masoud.ramezani]

قصد دارم مطالب مفید و به روزی را درباره SQL Server و به خصوص نسخه تازه به بازار آمده آن یعنی 2008 بیان کنم. امیدوارم بتوانم تجربیات خودم را برای دوستان محترم و علاقه مند بیان کنم تا شاید بشود که کمکی به پیشبرد صنعت IT در ایران داشته باشم.
لطفا با نظرات خود مرا یاری کنید.

[masoud.ramezani]

عملگرهای ترکیبی


عملگرهای ترکیبی مثل += و -= و *= و ... در این نسخه به امکانات زبان افزوده شده است.

DECLARE @price AS MONEY = 10.00;

SET @price += 2.00;

SELECT @price;

[masoud.ramezani]

سازنده Table Value

سازنده Table Value که در عبارت VALUES استفاده میشود. این بدین معنیست که میتوان فقط از یک عبارت Values برای ساخت چندین سطر استفاده کرد.

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');

توجه داشته باشید که اگر یکی از سطرها نتواند وارد شود کل عملیات fail میشود.
یک سازنده table value میتواند برای تعریف عباراتی مثل جداول مشتق شده استفاده شود مثل :

SELECT *

FROM

  (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')

  ) AS C(custid, companyname, phone, address);

مثال بالا از query داخلی استفاده کرده و به عنوان 5 سطر در جدول C استفاده میکند.

[masoud.ramezani]

توابع تبدیل ارتقا یافته


توابع تبدیل نوعهای مختلف به یکدیگر ارتقا یافته است. شما میتوانید به عنوان سومین آرگومان سبک و شیوه تبدیل را تعیین کنید. شیوه 0 (صفر) همان چیزیست که در نسخه های قبل انجام میشد. این شیوه رشته ها را به کد اسکی باینری معادل ترجمه میکند و همچنین بالعکس.
شیوه های 1 و 2 انواع جدیدی هستند. این شیوه ها رشته های در مبنای 16 را به معادل مقدار باینری که معادل ارقام در مبنای 16 است تبدیل میکند و بالعکس. وقتی شما از شیوه 1 استفاده کنید رشته ورودی باید دارای پیشوند 0x باشد و اگر شیوه 2 را استفاده کنید نباید اینگونه باشد. این اتفاق در هنگامی که یک مقدار باینری را به کاراکتر تبدیل میکنید هم اتفاق می افتد. به مثالهای زیر دقت نمایید:

SELECT

  CONVERT(VARCHAR(12) , 0x49747A696B  , 1) AS [Bin to Char 1],

  CONVERT(VARBINARY(5), '0x49747A696B', 1) AS [Char to Bin 1],

  CONVERT(VARCHAR(12) , 0x49747A696B  , 2) AS [Bin to Char 2],

  CONVERT(VARBINARY(5), '49747A696B'  , 2) AS [Char to Bin 2];

این کد خروجی زیر را خواهد داشت:

Bin to Char 1 Char to Bin 1 Bin to Char 2 Char to Bin 2

------------- ------------- ------------- -------------

0x49747A696B  0x49747A696B  49747A696B    0x49747A696B

[masoud.ramezani]

انواع داده ای Date و Time


یکی از مواردی که خیلی از طرف توسعه دهندگان مورد نیاز بود این بود که نوع DateTime در نسخه های قبل به صورت دو نوع مجزا در نسخه جدید آورده شودکه اینگونه هم شد. در نسخه جدید 4 نوع جدید برای این کار در نظر گرفته شده است که عبارتند از Date ، Time، DateTime2 و DATETIMEOFFSET
در جدول زیر مشخصات این انواع جدید آورده شده است:

      نوع داده

   ذخیره سازی (bytes)

   دامنه تاریخ

   دقت

   فرمت پیشنهادی تارخ و مثال

     DATE

   3

   January   1, 0001, through December 31, 9999 (Gregorian calendar)

   1   day

   'YYYY-MM-DD'

 '2009-02-12'

     TIME

   3   to 5

    

   100   nanoseconds

   'hh:mm:ss.nnnnnnn'

 '12:30:15.1234567'

     DATETIME2

   6   to 8

   January   1, 0001, through December 31, 9999

   100   nanoseconds

   'YYYY-MM-DD   hh:mm:ss.nnnnnnn'

 '2009-02-12 12:30:15.1234567'

     DATETIMEOFFSET

   8   to 10

   January   1, 0001, through December 31, 9999

   100   nanoseconds

   'YYYY-MM-DD   hh:mm:ss.nnnnnnn [+|-]hh:mm'

 '2009-02-12 12:30:15.1234567 +02:00'

به مثال زیر نیز توجه کنید :

DECLARE

  @d   AS DATE           = '2009-02-12',

  @t   AS TIME           = '12:30:15.1234567',

  @dt2 AS DATETIME2      = '2009-02-12 12:30:15.1234567',

  @dto AS DATETIMEOFFSET = '2009-02-12 12:30:15.1234567 +02:00';

 

SELECT @d AS [@d], @t AS [@t], @dt2 AS [@dt2], @dto AS [@dto];

این انواع جدید در ODBC و ADO.NET و OLE DB هم حمایت شده است جدول زیر معادل هر نوع را در Visual Studio 2008 نمایش میدهد :

SQL

   ODBC

   OLE DB

   ADO.NET

     DATE

   SQL_TYPE_DATE/

 SQL_DATE

   DBTYPE_DBDATE

   DateTime

     TIME

   SQL_TIME/

 SQL_SS_TIME2

   DBTYPE_DBTIME/

 DBTYPE_DBTIME2

   TimeSpan

     DATETIMEOFFSET

   SQL_SS_TIMESTAMPOFFSET

   DBTYPE_DBTIMESTAMPOFFSET

   DateTimeOffset

     DATETIME2

   SQL_TYPE_TIMESTAMP

 SQL_TIMESTAMP

   DBTYPE_DBTIMESTAMP

   DateTime

[masoud.ramezani]

توابع جدید و ارتقا یافته مربوط به زمان



با توجه به انواع جدید تاریخ و زمان توابع جدیدی تعریف شده است به شرح زیر : SYSDATETIME ، SYSUTCDATETIME ،SYSDATETIMEOFFSET، SWITCHOFFSETوTODATETIMEOFFSET این توابع تاریخ کنونی را در فرمتهای مختلف به عنوان خروجی میدهد. برای گرفتن زمان و یا تاریخ کنونی میتوانید از توابع زیر استفاده کنید :

SELECT

  CAST(SYSDATETIME() AS DATE) AS [current_date],

  CAST(SYSDATETIME() AS TIME) AS [current_time];

با استفاده از کد زیر میتوانید time zone مربوط را عوض کنید مثلا مثال زیر time zone را به GMT +05:00 خواهد برد :

 SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-05:00');

یک سری از توابع به طوری تغییر یافته اند که میتوان ازآنها روی انواع مربوط به زمان و تاریخ استفاده کرد.

Type conversion functions (CAST and CONVERT)

Set and aggregate functions (such as MAX, MIN)

Metadata functions (such as TYPEPROPERTY, COLUMNPROPERTY)

System functions (such as DATALENGTH, IS_DATE)

[masoud.ramezani]

UDT های بزرگ

در نسخه های قبلی انواع تعریف شده توسط کاربر (user defined type) دارای محدودیت از لحاظ طول بود یعنی CLR های با طول بیش از 8000 بایت نمیشد که ساخته شود ولی در نسخه جدید این محدودیت حذف شده است و این محدودیت تا 2 گیگابایت افزایش یافته است. دو نوع جدید GEOMETRY و GEOGRAPHY از این نوع هستند که در SQL Server 2008 به صورت built in افزوده شده اند. این دو برای نگهداری موقعیت جغرافیایی مناسبند. مشخصه ای برای تعیین طول موجود است که عبارت است از : SqlUserDefinedTypeAttribute.MaxByteSize اگر مقدار آن روی 1- تنظیم شده باشد یعنی تا 2 گیگا بایت میتواند افزایش حجم یابد. کلاینتهای قدیمی 2000 و 2005 این UDT های با حجم زیاد را با نوع VARBINARY(MAX) و IMAGEجایگزین میکند.

[masoud.ramezani]

نوع داده جدید HIERARCHYID (استراتژی های Indexing)


این یک CLR UDT است که برای نگهداری و دستکاری سلسله مراتب و وراثت استفاده میشود. این نوع به صورت داخلی در مقدار VARBINARY ذخیره میشود که اطلاعات گره کنونی را در سلسله مراتب (به صورت پدر و فرزند و همچنین به صورت همنیا) نگهداری میکند. برای درک این مطلب باید استراتژی Indexing و Insert گره های جدید را متوجه شویم.

این مورد شامل چند بخش است :

١- استراتژی های Indexing :
کد زیر نحوه استفاده از نوع جدید HIERARCHYID را نمایش میدهد به ستون hid دقت کنید :

USE tempdb;

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees

(

  empid   INT NOT NULL,

  hid     HIERARCHYID NOT NULL,

  lvl AS hid.GetLevel() PERSISTED,

  empname VARCHAR(25) NOT NULL,

  salary  MONEY       NOT NULL,

  CONSTRAINT PK_Emploees PRIMARY KEY NONCLUSTERED(empid)

);

به استفاده از متد GetLevel() توجه کنید، دراین ستون یعنی lvl شماره سطح این گره در سلسله مراتب به دست آمده و نگهداری میشود. این تضمین میکند که تمام گره های فرزند پایینتر از گره های والد است. به این دلیل اگر از اندیس استفاده کنیم به صورت خودکار با افزایش صعودی اندیس، این سلسله مراتب هم به صورت خودکار افزایش میابد درست مثل درختی که شاخ و برگهای آن افزایش میابد. دو نوع استراتژی موجود است 1- عمقی اول (depth first) 2- ردیفی اول (breadth first)
در استراتژی دوم گره های هم سطح دارای اندیس نزدیک به هم هستند. در کد های زیر هر دو نوع اندیس گذاری معرفی شده :

CREATE UNIQUE CLUSTERED INDEX idx_depth_first ON dbo.Employees(hid);

CREATE UNIQUE INDEX idx_breadth_first ON dbo.Employees(lvl, hid);

[masoud.ramezani]

نوع داده جدید HIERARCHYID (وارد کردن (Insert) گره های جدید )

برای وارد کردن یک گره در یک ساختار درختی ابتدا باید یک مقدار HIERARCHYID برای آن ساخته شود. از متد HIERARCHYID::GetRoot() برای تولید یک مقدار برای گره root استفاده کنید. از متد GetDescwndant برای تولید مقدار زیر یک گره داده شده استفاده کنید. این متد دو مقدار ورودی HIERARCHYID را به صورت اختیاری دریافت کرده که این دو ورودی جایی را بین خودشان برای گره جدید مشخص میکنند.
توجه داشته باشید که متد بالا تضمین نمیکند که مقدار تولید شده برای HIERARCHYID یکه باشد. برای اجبار کردن تولید یکه باید حتما روی ستون مربوطه یک primary key ، unique constraint و یا unique index تعریف کنید.
به عنوان مثال کد زیر یک stored procedure برای اضافه کردن یک گره به ساختار درختی میسازد :

IF OBJECT_ID('dbo.usp_AddEmp', 'P') IS NOT NULL DROP PROC dbo.usp_AddEmp;

GO

CREATE PROC dbo.usp_AddEmp

  @empid   AS INT,

  @mgrid   AS INT = NULL,

  @empname AS VARCHAR(25),

  @salary  AS MONEY

AS

 

DECLARE

  @hid            AS HIERARCHYID,

  @mgr_hid        AS HIERARCHYID,

  @last_child_hid AS HIERARCHYID;

 

IF @mgrid IS NULL

  SET @hid = HIERARCHYID::GetRoot();

ELSE

BEGIN

  SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);

  SET @last_child_hid =

    (SELECT MAX(hid) FROM dbo.Employees

     WHERE hid.GetAncestor(1) = @mgr_hid);

  SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);

END

 

INSERT INTO dbo.Employees(empid, hid, empname, salary)

  VALUES(@empid, @hid, @empname, @salary);

GO

کد بالابه اندازه کافی گویا هست که احتیاج به توضیح نداشته باشد. با استفاده از کد پایین میتوانید چندین گره به جدول اضافه کنید :

EXEC dbo.usp_AddEmp @empid =  1, @mgrid = NULL, @empname = 'David'  , @salary = $10000.00;

EXEC dbo.usp_AddEmp @empid =  2, @mgrid =    1, @empname = 'Eitan'  , @salary = $7000.00;

EXEC dbo.usp_AddEmp @empid =  3, @mgrid =    1, @empname = 'Ina'    , @salary = $7500.00;

EXEC dbo.usp_AddEmp @empid =  4, @mgrid =    2, @empname = 'Seraph' , @salary = $5000.00;

EXEC dbo.usp_AddEmp @empid =  5, @mgrid =    2, @empname = 'Jiru'   , @salary = $5500.00;

EXEC dbo.usp_AddEmp @empid =  6, @mgrid =    2, @empname = 'Steve'  , @salary = $4500.00;

EXEC dbo.usp_AddEmp @empid =  7, @mgrid =    3, @empname = 'Aaron'  , @salary = $5000.00;

EXEC dbo.usp_AddEmp @empid =  8, @mgrid =    5, @empname = 'Lilach' , @salary = $3500.00;

EXEC dbo.usp_AddEmp @empid =  9, @mgrid =    7, @empname = 'Rita'   , @salary = $3000.00;

EXEC dbo.usp_AddEmp @empid = 10, @mgrid =    5, @empname = 'Sean'   , @salary = $3000.00;

EXEC dbo.usp_AddEmp @empid = 11, @mgrid =    7, @empname = 'Gabriel', @salary = $3000.00;

EXEC dbo.usp_AddEmp @empid = 12, @mgrid =    9, @empname = 'Emilia' , @salary = $2000.00;

EXEC dbo.usp_AddEmp @empid = 13, @mgrid =    9, @empname = 'Michael', @salary = $2000.00;

EXEC dbo.usp_AddEmp @empid = 14, @mgrid =    9, @empname = 'Didi'   , @salary = $1500.00;

[masoud.ramezani]

نوع داده جدید 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;

[masoud.ramezani]

نوع داده جدید HIERARCHYID (متدهای مربوط)

شما میتوانید از چندین متد دیگر برای دستکاری در نوع داده 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 را به آن تبدیل میکند و بالعکس.

[masoud.ramezani]

پارامترهای Table Type و Table-Valued

نسخه جدید 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 تحت تاثیر قرار نمیگیرند.
• آنها یک مدل برنامه نویسی ساده را تهیه کرده اند.

[masoud.ramezani]

عبارت Merge در SQL Server 2008

این عبارت جدید یک عبارت استاندارد است که سه عمل 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 میکند.

[masoud.ramezani]

Grouping Sets در sql server 2008



این نسخه از 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 برای اینکه آنها را مجتمع کنیم موجود نیست.

[masoud.ramezani]

جبر مجموعه های گروه بندی در SQL Server 2008



شما محدود نشده اید که تنها از یک زیرعبارت در عبارت 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 به عنوان قسمتی از ضرب دکارتی استفاده نمایید.

[masoud.ramezani]

تابع GROUPING_ID


این تابع به شما اجازه میدهد که مجموعه هایی را مشخص کنید که هر کدام از سطور نتایج متعلق به آنهاست. به عنوان ورودی شما تمام مشخصه هایی را که در هر مجموعه دسته بندی شرکت دارند مهیا میکند. تابع یک نتیجه integer که یک bitmap (نقشه بیتی) است را تولید میکند، که در آن هر بیت جایگزین یک مشخصه متفاوت میشود. در این راه تابع یک عدد صحیح یکه برای هر مجموعه دسته بندی تولید میکند.
Query زیر یک مثال از استفاده این تابع را نمایش میدهد:

SELECT 
 
  GROUPING_ID(
 
    custid, empid,
 
    YEAR(orderdate), MONTH(orderdate), DAY(orderdate) ) AS grp_id,
 
  custid, empid,
 
  YEAR(orderdate) AS orderyear,
 
  MONTH(orderdate) AS ordermonth,
 
  DAY(orderdate) AS orderday,
 
  SUM(qty) AS qty
 
FROM dbo.Orders
 
GROUP BY
 
  CUBE(custid, empid),
 
  ROLLUP(YEAR(orderdate), MONTH(orderdate), DAY(orderdate));

توجه کنید که در خروجی، که در اینجا به صورت مختصر نمایش داده شده، هر مجموعه با یک عدد صحیح یکه جایگزین شده است:

grp_id  custid empid       orderyear   ordermonth  orderday    qty
 
------- ------ ----------- ----------- ----------- ----------- -----------
 
0       C      3           2006        4           18          22
 
16      NULL   3           2006        4           18          22
 
24      NULL   NULL        2006        4           18          22
 
25      NULL   NULL        2006        4           NULL        22
 
0       A      3           2006        8           2           10
 
16      NULL   3           2006        8           2           10
 
24      NULL   NULL        2006        8           2           10
 
25      NULL   NULL        2006        8           NULL        10
 
0       D      3           2006        9           7           30
 
16      NULL   3           2006        9           7           30
 ...

به عنوان مثال، عدد 25 جایگزین مجموعه دسته بندی (orderyear, ordermonth) شده است. بیتهای جایگزین عناصری که قسمتی از مجموعه گروه بندی در آن غیر فعال است (ordermonth – 2 و orderyear – 4)، و بیتهایی جایگزین عناصری که قسمتی از مجموعه دسته بندی که فعال هستند، نیستند (orderday – 1, empid – 8, custid – 16) میشوند. عدد 25 از جمع مقادیر جایگزین بیتهایی که فعال هستند به دست می آید یعنی 1+8+16 = 25. این ویژگی مخصوصا وقتی مفید است که شما نیاز دارید که به aggregate ها صورت خارجی بخشید و سپس فقط روی مجموعه های خاص query بزنید. شما میتوانید جدول را با مشخصه grp_id دسته بندی (cluster) کنید که این به SQL Server این امکان را میدهد که به صورت موثر و مفید یک درخواست برای یک مجموعه دسته بندی ویژه را انجام دهد.

[masoud.ramezani]

ساختن template برای گزارشات در Reporting Service


شاید برای خیلی ها که با SQL Server Reporting Server کار میکنند این سوال پیش آمده باشد که چگونه میتوان برای گزارشات یک قالب از پیش تعیین شده ساخت؟ این دقیقا شبیه Master Page برای صفحات ASP .NET است. پاسخ این سوال به شرح زیر است:

۱- گزارش مورد نظر خود را بسازید. این گزارش باید شامل قسمتهایی باشد که در تمام گزارشات شما تکرار میشود. مثلا اگر در تمام گزارشات شما Header و Footer مورد نیاز است، میتوانید این کار را در قالب خود طراحی کنید.
۲- فایل rdl ساخته شده را با نام مورد نظر در آدرس زیر کپی کنید:

D:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\Rep ortProject

۳- Visual Studio را باز کنید و یک پروژه Report بسازید.
۴- پس از ساخته شدن پروژه روی پوشه Reports راست کلیک کنید و گزینه Add و سپس New Item را انتخاب کنید. در دیالوگ باز شده باید یک قالب با نام موردنظر خود ببینید. آن راانتخاب کنید.
در گزارش جدید باید تغییراتی که از قبل داده اید را ببینید.

نکته - از این روش شما برای ساختن قالب برای Data Source ها هم میتوانید استفاده کنید.

[abbaseftekhari]

سلام دوست عزیز
از مطالب آموزند ه ات بسیار ممنون .
ببخشید من یک سوال داشتم اگه داده ها داخل یک جدول را بصورت صفحه به صفحه بخوهیم انتخاب کنیم باید چه کنیم منظور من عمل پیجینگ است در سمت sql server لطفا یک نمونه کد برای من قرار دهید . باتشکر

[masoud.ramezani]

چگونه عملیات صفحه بندی (paging) را در sql server انجام دهیم؟

در خیلی مواقع ملاحظه میشود که برای نمایش تعدادی از رکوردهای یک جدول در پایگاه داده کل مقادیر موجود درآن توسط یک دستور select به دست می‌آید و صفحه‌بندی خروجی به کنترلهای موجود سپرده میشود. اگر پایگاه داده ما دارای تعداد زیادی رکورد باشد آن موقع است که ما دچار مشکل میشویم. فرض کنید به طور همزمان ۵ نفر (که تعداد زیادی نیستند) از برنامه ما که شامل ۱۰۰۰۰۰ سطر داده میباشد استفاده کنند و در هر صفحه ما ۱۰ رکورد نمایش داده شود و صفحه‌بندی ما از نوع معقولی نباشد در این صورت به جای اینکه ما با ۵×۱۰ رکورد داده را بارگزاری کنیم، ۵×۱۰۰۰۰۰ رکورد یعنی ۵۰۰۰۰۰ رکورد را برای به دست آوردن ۵۰ رکورد بارگزاری میکنیم. در زیر روشی شرح داده میشود که سربار اضافه از روی برنامه و سرورهای مربوطه حذف شود. به stored procedure و توضیحات مربوط به آن توجه فرمایید :

CREATE PROCEDURE sp_PagedItems

    (

     @Page int,

     @RecsPerPage int

    )

AS



-- We don't want to return the # of rows inserted

-- into our temporary table, so turn NOCOUNT ON

SET NOCOUNT ON





--Create a temporary table

CREATE TABLE #TempItems

(

    ID int IDENTITY,

    Name varchar(50),

    Price currency

)





-- Insert the rows from tblItems into the temp. table

INSERT INTO #TempItems (Name, Price)

SELECT Name,Price FROM tblItem ORDER BY Price



-- Find out the first and last record we want

DECLARE @FirstRec int, @LastRec int

SELECT @FirstRec = (@Page - 1) * @RecsPerPage

SELECT @LastRec = (@Page * @RecsPerPage + 1)



-- Now, return the set of paged records, plus, an indiciation of we

-- have more records or not!

SELECT *,

       MoreRecords = 

    (

     SELECT COUNT(*) 

     FROM #TempItems TI

     WHERE TI.ID >= @LastRec

    ) 

FROM #TempItems

WHERE ID > @FirstRec AND ID < @LastRec





-- Turn NOCOUNT back OFF

SET NOCOUNT OFF

در این کد دو پارامتر از نوع integer تعریف میکنیم. اول پارامتر @Page که مربوط به شماره صفحه‌ای میباشد که قصد دارید آن را بارگزاری نمایید. دومین پارامتر با نام @RecsPerPage تعداد رکوردهایی است که هر بار میخواهید بارگزاری شوند مثلا اگر میخواهید هر بار ۱۵ عدد از رکوردها را نمایش دهید این مقدار را باید برابر ۱۵ قرار دهیم.در مرحله بعد یک جدول موقت با نام #TempItems ساخته شده است که به طور موقت مقادیری را در حافظه نگه میدارد و هیچ فضای فیزیکی روی هارد دیسک اشغال نمیکند. نکته کلیدی که جلوتر از آن استفاده شده ستون با نام ID است که از نوع auto-increment است و روی جدول موقت تعریف شده است. این ستون شناسه هر سطر را در خود نگه میدارد که به صورت اتوماتیک بالا میرود و جزء لاینفکی از این نوع paging میباشد. پس از آن جدول موقت را توسط رکوردهای جدول واقعی با نام tblItem توسط دستور select پر میکنیم.
در مرحله بعد شماره اولین و آخرین سطر مورد نظر را بر اساس پارامترهای ورودی محاسبه کرده و در متغیرهای @FirstRec و @LastRec بریزیم.
برای استفاده از این کد فقط کافیست که پارامترهای ورودی را مقداردهی نمایید. مثلا اگر میخواهید در یک کنترل Grid از آن استفاده کنید باید ابتدا یک کوئری داشته باشید که تعداد کل سطرها را به شما بدهد و بر اساس این مقدار تعداد صفحات مورد نظر را به دست آورید. پس از آن با کلیک روی هر کدام از شماره صفحات آن را به عنوان مقدار به پارامتر مورد نظر بفرستید و از آن لذت ببرید.

[masoud.ramezani]

توسعه و بهبود DDL برای Trigger



در sql server 2008، تریگرها طوری تغییر کرده اند که از آنها به عنوان DDL میشود استفاده کرد مثلا یک table جدید در آن ساخته شود. با این تغییر انعطاف بسیار بالایی به trigger ها داده شده است. شمای XML برای رخدادها به عنوان بخشی از database engine در مسیر

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2006\11\ev ents\events.xsd

نصب شده و همچنین از آدرس زیر قابل دریافت است.

http://schemas.microsoft.com/sqlserver

خیلی از stored procedure ها اعمال تعریف داده(DDL) انجام میدهند. قبل از sql server 2008 ، شما نمیتوانستید با یک trigger ساختار داده ای جدیدی تعریف کنید. اکنون شما میتوانید رویدادهایی را راه اندازی کنید که درآنها کارهایی شبیه PROCEDURE انجام دهید. به عنوان مثال، stored procedure با نام sp_rename یک trigger را که در رویداد RENAME جدید ساخته شده است راه اندازی و اجرا میکند. برای نمایش این نوع از trigger، کد زیر یک database با نام testdb و در آن یک trigger روی رویداد RENAME برای چاپ منبع و جزییات هدف را جهت تست میسازد.

 
USE master;
 
GO
 
IF DB_ID('testdb') IS NOT NULL DROP DATABASE testdb;
 
CREATE DATABASE testdb;
 
GO
 
USE testdb;
 
GO
 
CREATE TRIGGER trg_testdb_rename ON DATABASE FOR RENAME
 
AS
  
DECLARE
 
  @SchemaName       AS SYSNAME =
 
    EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
 
  @TargetObjectName AS SYSNAME =
 
    EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname'),
 
  @ObjectName       AS SYSNAME =
 
    EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
 
  @NewObjectName    AS SYSNAME =
 
    EVENTDATA().value('(/EVENT_INSTANCE/NewObjectName)[1]', 'sysname');
  
DECLARE
 
  @msg AS NVARCHAR(1000) = 
 
N'RENAME event occurred.
 
SchemaName: ' + @SchemaName + N'
 
TargetObjectName: ' + @TargetObjectName + N'
 
ObjectName: ' + @ObjectName + N'
 
NewObjectName: ' + @NewObjectName;
  
PRINT @msg;
 
GO

برای تستtrigger کد زیر یک table با نام dbo.T1 با یک ستون که col1 نامیده میشود میسازد و sp_rename را برای تغییر نام ستون به col2 اجرا میکند :

CREATE TABLE dbo.T1(col1 INT);
 
EXEC sp_rename 'dbo.T1.col1', 'col2', 'COLUMN';

Trigger روی رویداد RENAME فراخوانی شده و پیام زیر را چاپ میکند :

RENAME event occurred.
 
SchemaName: dbo
 
TargetObjectName: T1
 
ObjectName: col1
 
NewObjectName: col2

[masoud.ramezani]

ستونهای پراکنده، ستونهایی هستند که برای ذخیره سازی NULL ها بهینه شده اند. برای تعریف یک ستون به عنوان Sparse ، مشخصه های آن را به عنوان بخشی از تعریف ستون مشخص کنید. Sparse ها هیچ فضایی را برای NULL مصرف نمیکنند، مطابق با انواع با طول ثابت; اگرچه وقتی یک ستون به عنوان sparse مشخص میشود، ذخیره مقادیر غیر Null از همیشه پر هزینه تر میشود. بنابراین شما فقط باید زمانی یک ستون را از نوع Sparse تعریف کنید که درصد زیادی از اطلاعات آن Null باشد. کتابهای online مربوط به SQL Server پیشنهاداتی در این زمینه دارد که چه مقدار از داده ها از هر نوع باید null باشد تا استفاده از sparse برای آن ستون مفید باشد.

Query زدن و پر کردن ستونهای sparse مانند ستونهای معمولیست با یک تفاوت که در جلوتر بیان خواهد شد. به عنوان مثال کد زیر یک جدول با نام T1 در tempdb (برای تست) میسازد، سه تا از ستونها با مشخصه sparse علامت گذاری میشوند، یک زوج از سطرها در آن insert میشوند و روی جدول query زده میشود :

USE tempdb;

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;

CREATE TABLE dbo.T1

(

  keycol INT                   NOT NULL PRIMARY KEY,

  col1   VARCHAR(20)           NOT NULL,

  col2   INT            SPARSE     NULL,

  col3   CHAR(10)       SPARSE     NULL,

  col4   NUMERIC(12, 2) SPARSE     NULL

);

INSERT INTO dbo.T1(keycol, col1, col2) VALUES(1, 'a', 10);

INSERT INTO dbo.T1(keycol, col1, col4) VALUES(2, 'b', 20.00);

 

SELECT keycol, col1, col2, col3, col4

FROM dbo.T1;

محدودیتهای زیادی برای استفاده از ستونهای Sparse موجود است که بحث در رابطه با آنها در اینجا نمیگنجد.
SQL Server 2008 به شما این اجازه را میدهد که مجموعه ای از ستونها که تمامی ستونهای sparse یک جدول را در یک ستون XML واحد ترکیب میکند تعریف کنید. شما ممکن است که بخواهید به این گزینه، وقتیکه تعداد زیادی از ستونهای sparse را در جدولتان دارید (بیشتر از ۱۰۲۴) فکر کنید و کار کردن روی آنها به صورت شخصی ممکن است طاقت فرسا باشد.

برای تعریف یک مجموعه از ستونها(column set)، عبارت زیر را به عنوان بخشی از عبارت CREATE TABLE مشخص کنید:

<column_set_name> XML column_set FOR ALL_SPARSE_COLUMNS

به عنوان مثال، کد زیر جدول T1 را مجددا با یک مجموعه ستون با نام cs میسازد:

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;

CREATE TABLE dbo.T1

(

  keycol INT                   NOT NULL PRIMARY KEY,

  col1   VARCHAR(20)           NOT NULL,

  col2   INT            SPARSE     NULL,

  col3   CHAR(10)       SPARSE     NULL,

  col4   NUMERIC(12, 2) SPARSE     NULL,

  cs     XML column_set FOR ALL_SPARSE_COLUMNS

);

شما همچنین میتوانیداز کد مشابهی که قبلتر استفاده شده برای insert سطرها در جدول و query زدن روی آن استفاده کنید :

INSERT INTO dbo.T1(keycol, col1, col2) VALUES(1, 'a', 10);

INSERT INTO dbo.T1(keycol, col1, col4) VALUES(2, 'b', 20.00);



SELECT keycol, col1, col2, col3, col4

FROM dbo.T1;

اما شما میتوانید روی column set با استفاده از اعمال XML به جای اعمال رابطه ای کار کنید. به عنوان مثال، کد زیر یک سطر با استفاده از column set در جدول درج میکند :

INSERT dbo.T1(keycol, col1, cs)

  VALUES(3, 'c', '<col3>CCCCCCCCCC</col3><col4>30.00</col4>');

NULL برای ستونی که در مقدار XML نیامده است در نظر گرفته میشود که در این مثال col2 است.
دقت کنید که اگر شما یک column set در جدول تعریف کنید، SELECT * مقادیر مشابه SELECT که در آن تمام ستونها به صورت واضح نام برده شده اند را بر نمیگرداند. SELECT * تمام ستونهای sparse را به عنوان یک مقدار XML در column set برمیگرداند. برای نمایش این، کد زیر را اجرا کنید :

SELECT * FROM dbo.T1;

این کد خروجی زیر را برمیگرداند:

keycol      col1       cs

----------- ---------- ------------------------------------------

1           a          <col2>10</col2>

2           b          <col4>20.00</col4>

3           c          <col3>CCCCCCCCCC</col3><col4>30.00</col4>

اگر شما به صورت صریح ستونها را در عبارت SELECT لیست کنید، تمام ستونهای نتیجه به عنوان مقادیر رابطه ای باز گردانده میشوند.
خصیصه جدید دیگری که میتوانند در مقارنه با ستونهای sparse استفاده شوند اندیسهای فیلتر شده (filtered index) هستند. این خصیصه در بخش بعدی توضیح داده خواهد شد.

[mostafarastin239]

سلام مهندس
دست گلت درد نکنه. می تونم خواهش کنم که ی دی اف این آموزش ها را قرار بدی تا بتونیم دانلود کرده و داشته باشیم.
با تشکر