SQL SERVER 2008

تجارب من در رابطه با SQL SERVER

پارامترهای Table Type و Table-Valued
ساعت ۱۱:۳۸ ‎ق.ظ روز ۱۳۸٧/٧/٢۸  کلمات کلیدی: t-sql ، sql server 2008 ، 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 تحت تاثیر قرار نمیگیرند.
•    آنها یک مدل برنامه نویسی ساده را تهیه کرده اند.