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


 
نوع داده جدید HIERARCHYID (متدهای مربوط)
ساعت ۱۱:٢٩ ‎ق.ظ روز ۱۳۸٧/٧/٢۸  کلمات کلیدی: t-sql ، hierarchyid ، getreparentedvalue ، binarywriter

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


 
نوع داده جدید HIERARCHYID (جستجو زدن روی سلسله مراتب)
ساعت ۱:٥٤ ‎ب.ظ روز ۱۳۸٧/٧/٢٤  کلمات کلیدی: hierarchyid ، t-sql ، isdescendantof ، getincestor

اگر شما روی 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;


 
نوع داده جدید HIERARCHYID (وارد کردن (Insert) گره های جدید )
ساعت ٤:٤٤ ‎ب.ظ روز ۱۳۸٧/٧/٢٠  کلمات کلیدی: hierarchyid ، t-sql ، sql server 2008 ، getdescwndant

برای وارد کردن یک گره در یک ساختار درختی ابتدا باید یک مقدار 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;


 
نوع داده جدید HIERARCHYID (استراتژی های Indexing)
ساعت ٤:٢٢ ‎ب.ظ روز ۱۳۸٧/٧/۱٦  کلمات کلیدی: t-sql ، hierarchyid ، breadth first ، depth first

این یک 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);

 


 
UDT های بزرگ
ساعت ۱٢:٢۳ ‎ب.ظ روز ۱۳۸٧/٧/۱٤  کلمات کلیدی: t-sql ، sql server 2008 ، udt

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

 


 
توابع جدید و ارتقا یافته
ساعت ٢:٢٩ ‎ب.ظ روز ۱۳۸٧/٧/٩  کلمات کلیدی: t-sql ، convert methods ، date ، time

با توجه به انواع جدید تاریخ و زمان توابع جدیدی تعریف شده است به شرح زیر : 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)


 
انواع داده ای Date و Time
ساعت ۱٢:٤٠ ‎ب.ظ روز ۱۳۸٧/٧/۳  کلمات کلیدی: sql server 2008 ، t-sql ، 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