SQL SERVER 2008

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

بهبودهای SQL و CLR
ساعت ٥:٤٤ ‎ب.ظ روز ۱۳۸٧/۱٢/۱٢  کلمات کلیدی: tvf ، clr ، sql server 2008 ، table-valued

بهبود در توابع Table-Valued


در حال حاضر CLR TVF ها یک عبارت ORDER جدید را به عنوان بخشی از عبارت CREATE FUNCTION DDL پشتیبانی میکنند. شما میتوانید از این عبارت برای مشخص کردن نام ستونها در جدول خروجی زمانیکه میدانید که سطرها همیشه با آن ترتیب برگردانده خواهند شد، استفاده نمایید.  این میتواند بهینه ساز را زمانیکه روی تابع جدول query میزنید و به ستونهای آن برای مرتب سازی (Ordering)  استناد میکنید کمک نماید.( مانند زمانیکه برای ORDER BY و GROUP BY  و DISTINCT استفاده میشود.)
به عنوان مثال، کد C# زیر یک تابع با نام fn_split  که لیستی مجزا از مقادیر و یک Seprator را میپذیرد و سپس رشته ورودی را به عناصر مجزا میشکند. تابع یک جدول با دو ستون (pos و element) با یک ردیف برای هر عنصر به عنوان نتیجه باز میگرداند. Pos موقعیت عنصر در لیست را نشان میدهد و ستون element خود عنصر را نمایش میدهد :


using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
 
public partial class TVF
{
    // Struct used in string split functions
    struct row_item
    {
        public string item;
        public int pos;
    }
 
    // Split array of strings and return a table
    // FillRowMethodName = "ArrSplitFillRow"
    [SqlFunction(FillRowMethodName = "ArrSplitFillRow",
     DataAccess = DataAccessKind.None,
     TableDefinition = "pos INT, element NVARCHAR(MAX)")]
    public static IEnumerable fn_split(SqlString inpStr,
        SqlString charSeparator)
    {
        string locStr;
        string[] splitStr;
        char[] locSeparator = new char[1];
        locSeparator[0] = (char)charSeparator.Value[0];
        if (inpStr.IsNull)
            locStr = "";
        else
            locStr = inpStr.Value;
        splitStr = locStr.Split(locSeparator,
              StringSplitOptions.RemoveEmptyEntries);
        //locStr.Split(charSeparator.ToString()[0]);
        List<row_item> SplitString = new List<row_item>();
        int i = 1;
        foreach (string s in splitStr)
        {
            row_item r = new row_item();
            r.item = s;
            r.pos = i;
            SplitString.Add(r);
            ++i;
        }
        return SplitString;
    }
 
    public static void ArrSplitFillRow(
      Object obj, out int pos, out string item)
    {
        pos = ((row_item)obj).pos;
        item = ((row_item)obj).item;
    }
}

تابع اغلب سطرها را مرتب شده بر اساس pos برمیگرداند. اگرچه شما نمیتوانید به این ترتیب تا زمانیکه در query خارجی و در ORDER BY ترتیب را مشخص نکرده باشید، اعتماد کنید.
فرض کنید که مسیر فایل dll برابر باشد با : C:\TVF\TVF\bin\Debug\TVF.dll . کد زیر اسمبلی مربوطه را در tempdb  برای تست میسازد:


USE tempdb;
CREATE ASSEMBLY TVF FROM 'C:\TVF\TVF\bin\Debug\TVF.dll';


کد زیر دو تابع بر اساس تابع fn_split CLR  ثبت میکند : تابع  fn_split_no_order عبارت ORDER ندارد و fn_split_order_by_pos عبارت ORDER را برای مرتب سازی بر اساس pos مشخص کرده است :


CREATE FUNCTION dbo.fn_split_no_order
  (@string AS NVARCHAR(MAX), @separator AS NCHAR(1))
RETURNS TABLE(pos INT, element NVARCHAR(4000))
EXTERNAL NAME TVF.TVF.fn_split;
GO
CREATE FUNCTION dbo.fn_split_order_by_pos
  (@string AS NVARCHAR(MAX), @separator AS NCHAR(1))
RETURNS TABLE(pos INT, element NVARCHAR(4000))
ORDER (pos)
EXTERNAL NAME TVF.TVF.fn_split;
GO


حال به query های زیر توجه نمایید:


SELECT *
FROM dbo.fn_split_no_order(
  N'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',
  N',') AS T
ORDER BY pos;

SELECT *
FROM dbo.fn_split_order_by_pos(
  N'a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z',
  N',') AS T
ORDER BY pos;


اولی تابع جدولی را که بدون عبارت ORDER ثبت شده را جستجو میکند و دومی این کار را روی تابع جدولی که با ORDER ثبت شده (pos) را جستجو میکند.هر دو داده ها را مرتب شده بر اساس pos درخواست میکنند.اگر شما هر دو را برای آزمایش اجرا کنید خواهید دید که اولی شامل اطلاعات sort شده است اما دومی اینطور نیست. همچنین هزینه برای ارزیابی کردن اولی حدود 10 برابر بیشتر از دومی است.