SQL SERVER 2008

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

مقایسه اجمالی نسخه‌های SSCE و Express برنامه SQL Server
ساعت ۳:٢٤ ‎ب.ظ روز ۱۳۸٩/٢/٤  کلمات کلیدی: sql server ، ssce ، express edition

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

 

خصوصیت

SQL Server Compact Edition

SQL Server Express Edition

خصوصیات قرارگیری و نصب

اندازه نصب

۱.۷ مگابایت حجم دانلود

۱.۸ مگابایت حجم روی دیسک سخت

۵۳.۸ مگابایت حجم دانلود

۱۹۷ مگابایت حجم روی دیسک سخت

همخوانی با ClickOnce

دارد

دارد

نصب خصوصی- ادغام شده با نرم‌افزار

دارد

ندارد

گزینه نصب غیر مدیریتی

دارد

ندارد

قابلیتاجرا روی ویندوز موبایل

دارد

ندارد

نصب مرکزی با استفاده از یک MSI

دارد

دارد

راه‌اندازی در هنگام کار با نرم‌افزار

دارد

ندارد

پشتیبانی از پردازنده ۶۴ بیتی

نسخه ۳.۱ ندارد اما نسخه‌های بعدی دارد

دارد

قابلیت اجرا به عنوان سرویس

ندارد

دارد

خصوصیات فایل داده

قالب فایل

فایل واحد

چندین فایل

انباره فایل داده در یک محل اشتراکی در شبکه

دارد

ندارد

پشتیبانی از پسوندهای فایلی دیگر

دارد

ندارد

حداکثر اندازه پایگاه داده

۴ گیگابایت

۴ گیگابایت

ذخیره XML

دارد (ذخیره در nText)

دارد

قالب فایلی بدون رمز و سند امن

دارد

ندارد

خصوصیات برنامه‌نویسی

خصوصیات رایج T-SQL

دارد

دارد

T-SQL رویه‌ای

ندارد

دارد

دسترسی داده‌ای از راه دور (RDA)

دارد

ندارد

ADO.NET Sync Framework

دارد

ندارد (برای نسخه‌های آینده)

مشترک برای merge replication

دارد

دارد

تراکنش‌های ساده

دارد

دارد

تراکنش‌های توزیع شده

ندارد

دارد

XML - XQuery  و QPath به صورت بومی

ندارد

دارد

Sp ها، view ها و تریگرها

ندارد

دارد

امنیت بر مبنای نقش

ندارد

دارد

تعداد ارتباطات همزمان

۲۵۶

نامحدود


 
نکاتی در رابطه با SQL Server Compact یا SSCE
ساعت ۱٢:٥۸ ‎ب.ظ روز ۱۳۸٩/۱/۱٥  کلمات کلیدی: sql server ، ssce ، sql server compact ، sql ce

SQL Server Compact یک پایگاه داده رابطه‌ای فشرده است که توسط شرکت مایکروسافت جهت استفاده به عنوان پایگاه داده در دستگاه‌های موبایل و کامپیوترهای رومیزی عرضه شده است.
قبل از اینکه این محصول برای سکوهای رومیزی عرضه شود با عنوان SQL Server برای Windows CE شناخته می‌شد. آخرین نسخه منتشر شده این محصول SQL Server Compact 3.5 SP1 است که علاوه بر Windows Mobile 2003, 5.0, 6.0, 6.5 از  .Net Framework 3.5 هم پشتیبانی می‌نماید.
نکاتی در رابطه با SSCE :
۱- روی نسخه‌های ۳۲ و ۶۴ بیتی پشتیبانی می‌شود.
۲- برای دانلود و توزیع مجدد رایگان است و میتوانید آن را از سایت مایکروسافت دانلود نمایید.
۳- یک API مشترک را با دیگر نسخه‌های SQL Server به اشتراک می‌گذارد.
۴- شامل  ADO.NET Provider برای دسترسی به داده‌ها با استفاده از API مربوط می‌باشد.
۵- دارای قابلیت‌های همزمان‌سازی توکار می‌باشد.
۶- از LINQ و EF پشتیبانی می‌کند.
۷- بر خلاف نسخه‌های دیگر در کنار نرم‌افزار میزبان و در محیطی با حافظه کمتر از ۲ مگابایت اجرا می‌شود.
۸- تراکنش‌ها، محدودیت‌های ارجاعی، قفل شدن و چندین اتصال همزمان به پایگاه داده را پشتیبانی می‌کند.
۹- تراکنش‌های تودرتو پشتیبانی نمی‌شود اما تراکنش‌های موازی روی جداول مختلف پشتیبانی می‌شود.
۱۰- نسخه کنونی Stored Procedure ها و نوع داده‌ای XML را پشتیبانی نمی‌کند.
۱۱- XQuery پشتیبانی نمی‌شود.
۱۲- Indexing را علاوه بر Replication پشتیبانی می‌کند.
۱۳- این نوع پایگاه داده علاوه بر SQL Server Management Studio در Microsoft Visual Studio نیز قابل ساختن و مدیریت شدن است.
۱۴- در یک فایل واحد با پسوند .sdf نگه‌داری می‌شود.
۱۵- فایل پایگاه داده می‌تواند حجمی تا ۴ گیگابایت را پشتیبانی کند.
۱۶- قابلیت رمزنگاری ۱۲۸ بیتی فایل داده وجود دارد.
۱۷- قابلیت دسترسی همزمان چندین کاربر به فایل داده وجود دارد.
۱۸- به راحتی می‌توان آن را در سیستم مقصد کپی کرد و از آن استفاده نمود.
۱۹- قابلیت استقرار و توسعه توسط  تکنولوژی OneClick وجود دارد.
۲۰- DataDirectories  را پشتیبانی می‌کند، یعنی نیازی نیست که کل آدرس برای دسترسی به آن موجود باشد. یعنی به این صورت می‌توان به آن دسترسی داشت : 

|DataDirectory|\<database_name>.sdf

که این آدرس در assembly manifest برنامه نگهداری می‌شود.
۲۱- نیازی نیست که نامگذاری فایل بر اساس استاندارد نامگذاری sdf باشد و هر پسوندی می‌تواند استفاده شود.
۲۲- تنظیم رمز عبور برای فایل داده اختیاریست.

در انتها می‌توان به این نتیجه رسید که بهتر است برای سیستم‌های کوچک یا برنامه‌هایی که می‌خواهیم مثلا روی یک رسانه فقط خواندنی مثل CD منتشر کنیم، بهتر است که از این پایگاه داده استفاده نماییم. در اینگونه برنامه‌ها استفاده از فایل‌های Access به عنوان پایگاه داده بسیار رایج است.


 
استفاده از عملگر PIVOT برای تبدیل مقادیر تک ستونی به چند ستونی با کمک توابع تجمع
ساعت ۱۱:۱۱ ‎ق.ظ روز ۱۳۸۸/۱٢/۱٢  کلمات کلیدی: sql server ، pivot ، aggragate

برخی موارد نیاز داریم که یک مقدار چند ستونی را به چند ستونی تبدیل کنیم. این مورد در ساخت گزارشات کاربرد زیادی دارد. به عنوان مثال فرض کنید یک کوئری آماده دارید که توسط آن لیستی از تمام شیفت‌های افراد به همراه نام قسمت‌های مربوطه در یک سازمان ارائه می‌شود. حال نیاز دارید بفهمید که هر کدام از قسمت‌های سازمان در کدام شیفت، چند نفر کارمند دارد. عملا شما به یک جدول متقاطع نیاز دارید که ستون‌های آن شامل نام قسمت‌های مختلف سازمان و سطرهای آن نام شیفت‌های مختلف می‌باشد.
ابتدا شما را با گرامر عبارت مورد نظر آشنا میکنم :

 

FROM table_source 

-- جدول یا کوئری منبع

PIVOT (

aggregate_function ( value_column ) 

-- تابع تجمعی مورد نظر به همراه ستون مورد نظر

FOR pivot_column 

-- ستونی که برای ساختن نام ستونها استفاده میشود

IN ( <column_list>) 

-- مقادیری که از ستون بالا برای عملیات انتخاب میشود

) table_alias

  -- نام مستعار جدول خروجی


حال این مثال را پیاده‌سازی می‌کنیم، لطفا به کوئری اولیه توجه بفرمایید :

 

SELECT s.Name ShiftName,

h.BusinessEntityID,

d.Name DepartmentName

FROM HumanResources.EmployeeDepartmentHistory h

INNER JOIN HumanResources.Department d ON

h.DepartmentID = d.DepartmentID

INNER JOIN HumanResources.Shift s ON

h.ShiftID = s.ShiftID

WHERE EndDate IS NULL AND

d.Name IN ('Production', 'Engineering', 'Marketing')

ORDER BY ShiftName

 

 

خروجی این جدول لیستی از نام شیفت، شناسه کارمندان و نام قسمت مربوط می‌باشد. به خروجی آن توجه کنید:

 

ShiftName   BusinessEntityID   DepartmentName
Day              3                            Engineering
Day              9                            Engineering
...
Day              2                            Marketing
Day              6                            Marketing
...
Evening        25                          Production
Evening        18                          Production
Night            14                          Production
Night            27                          Production
...
Night            252                        Production
(194 row(s) affected)

 

حال برای تغییر دادن این کوئری به خروجی گزارش مورد نظر آن را به صورت زیر تغییر دهید:

 

SELECT ShiftName,

Production,

Engineering,

Marketing

FROM

(SELECT s.Name ShiftName,

h.BusinessEntityID,

d.Name DepartmentName

FROM HumanResources.EmployeeDepartmentHistory h

INNER JOIN HumanResources.Department d ON

h.DepartmentID = d.DepartmentID

INNER JOIN HumanResources.Shift s ON

h.ShiftID = s.ShiftID

WHERE EndDate IS NULL AND

d.Name IN ('Production', 'Engineering', 'Marketing')) AS a

PIVOT

(

COUNT(BusinessEntityID)

FOR DepartmentName IN ([Production], [Engineering], [Marketing])

) AS b

ORDER BY ShiftName

 

 

خروجی این کوئری به صورت زیر خواهد بود :

 

ShiftName   Production   Engineering   Marketing
Day              79                 6                    9
Evening        54                 0                    0
Night           46                  0                    0

(3 row(s) affected)


 
رفع خطا در هنگام ساخت database diagram
ساعت ۸:۱٤ ‎ق.ظ روز ۱۳۸۸/۱۱/۱۳  کلمات کلیدی: database diagram ، error ، sql server ، owner

دوستان زیادی رو دیدم که گاهی اوقات نیاز به ساخت یک database diagram جدید پیدا میکنند. گاهی اوقات پس از اینکه روی نام پایگاه داده کلیک راست را می‌زنیم و گزینه New Database Diagram را انتخاب می‌کنیم با خطای زیر مواجه میشویم.


Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.


دلیل آن هم به خوبی درپیغام خطا نمایش داده شده است و آن هم این است که برای پایگاه داده مورد نظر شما یک owner به صورت مستقیم تعریف نشده است.


برای رفع این مشکل می‌توانید از اسکریپت زیر استفاده کرده و عمل مورد نیاز برای رفع مشکل را انجام دهید.


ALTER AUTHORIZATION ON DATABASE::database_name TO valid_login


درست مانند :


ALTER AUTHORIZATION ON DATABASE::RamezaniDB TO sa



 
Catalog View چیست؟
ساعت ۸:۳٢ ‎ق.ظ روز ۱۳۸۸/۱٠/٢٠  کلمات کلیدی: sysobjects ، catalog view ، sql server

Catalog view ها اطلاعاتی را در خود نگهداری می‌کنند که موتور پایگاه داده SQL Server از آنها استفاده می‌نماید. استفاده از این view ها به کسانی توصیه می‌شود که به اطلاعاتی در رابطه با ساختار پایگاه داده خود نیاز دارند. این view ها عمومی‌ترین واسط موجود برای به دست آوردن متادیتاهایی در رابطه با  پایگاه داده می‌باشند. شما می‌توانید توسط کوئری‌های ساده به اطلاعات ارزشمندی به دست آورید. به عنوان مثال می‌توانید ارتباط بین آبجکت‌های موجود در پایگاه داده را با یک کوئری به دست آورید. یا مثلا می‌توانید لیست نام تمام جداول موجود یا کلید‌های شناسه موجود در پایگاه داده را به سادگی به دست آورید.

مثلا تکه کد زیر لیست تمام stored procedure های تعریف شده را به شما در خروجی خواهد داد :

 

USE MyTable

SELECT name FROM sys.objects WHERE type = 'P'

 

یکی از نکاتی که باید به آن توجه کرد این است که این view ها اطلاعاتی در رابطه با replication ، پشتیبان و طرح نگهداری پایگاه داده (db maintenance plan) یا SQL Server Agent در خود نگه نمی‌دارند.

برخی از catalog view ها سطرهای خود را از سطرهای دیگر به ارث می‌برند. به عنوان مثال sys.tables که اطلاعات مربوط به جداول موجود در وایگاه داده را در خود نگه می‌دارد، داده‌های خود را از sys.objects به دست می‌آورد. Sys.objects برای موارد دیگر یک catalog view پایه‌ای به حساب می‌آید. زمانی که یک جدول جدید در پایگاه داده ساخته می‌شود، متادیتای مربوط ازهر دو view قابل بازگشت است. اگر چه هر دوی این‌ها اطلاعات در سطح مختلفی را در رابطه با جدول به کاربر باز می‌گرداننداما فقط یک قلم ازاین داده با یک نام و یک شناسه (object_id) وجود دارد. به عبارت دیگر می‌توان گفت:

  • View پایه شامل زیرمجموعه‌ای از ستون‌ها و یک ابرمجموعه از سطرهاست.
  • View مشتق شده شامل یک ابرمجموعه از ستون‌ها و یک زیرمجموعه از سطرهاست.


 
توجه : از آنجایی که ممکن است در نسخه‌های آینده، اطلاعات کامل‌تری در این view ها ذخیره شوند بهتر از برای استفاده از آنها کوئری به شکل زیر باشد:

SELECT * FROM sys.catalog_view_name

 

این view ها در SQL Server به دسته‌های زیر دسته‌بندی شده است:


1.       Change Tracking Catalog Views

2.       Messages (For Errors) Catalog Views

3.       CLR Assembly Catalog Views

4.       Object Catalog Views

5.       Databases and Files Catalog Views

6.       Partition Function Catalog Views

7.       Database Mail Views

8.       Policy-Based Management Views

9.       Database Mirroring Catalog Views

10.   Resource Governor Catalog Views

11.   Data Collector Views

12.   Scalar Types Catalog Views

13.   Data Spaces

14.   Schemas Catalog Views

15.   Endpoints Catalog Views

16.   Security Catalog Views

17.   Extended Events Catalog Views

18.   Service Broker Catalog Views

19.   Extended Properties Catalog Views

20.   Server-wide Configuration Catalog Views

21.   Full-Text Search Catalog Views

22.   XML Schemas (XML Type System) Catalog Views

23.   Linked Servers Catalog Views

 


 
خروجی یک فیلد به صورت comma separated
ساعت ٦:۱۳ ‎ب.ظ روز ۱۳۸۸/٤/۳٠  کلمات کلیدی: sql server ، comma separated ، coalesce

به کمک کوئری زیر میتوانید نتایج یک فیلد در پایگاه داده را به صورت comma separated دریافت کنید. گفتم شاید این مورد به درد دوستان هم بخورد.

 

لطفا به کد زیر دقت نمایید :


DECLARE @NameList nvarchar(Max)

SELECT @NameList = COALESCE(@NameList + ', ', '') +
    ColumnName
FROM TableName
WHERE ID < 10

SELECT @NameList


 
چگونه عملیات صفحه بندی (paging) را در sql server انجام دهیم؟
ساعت ۱٠:۳٢ ‎ق.ظ روز ۱۳۸۸/٤/۱٤  کلمات کلیدی: sql server ، paging ، صفحه بندی

در خیلی مواقع ملاحظه میشود که برای نمایش تعدادی از رکوردهای یک جدول در پایگاه داده کل مقادیر موجود درآن توسط یک دستور 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 از آن استفاده کنید باید ابتدا یک کوئری داشته باشید که تعداد کل سطرها را به شما بدهد و بر اساس این مقدار تعداد صفحات مورد نظر را به دست آورید. پس از آن با کلیک روی هر کدام از شماره صفحات آن را به عنوان مقدار به پارامتر مورد نظر بفرستید و از آن لذت ببرید.


 
تنظیم SQL Server برای Back Up گرفتن به صورت خودکار (تعریف job)
ساعت ۳:۳۱ ‎ب.ظ روز ۱۳۸۸/۳/٢٦  کلمات کلیدی: automatic backup ، sql server ، job ، پشتیبانگیری خودکار

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

 


۱.    با راست کلیک روی بانک اطلاعاتی مورد نظر، گزینه Tasks\Backup را انتخاب کنید.
۲.    نوع Backup را انتخاب کنید. Full حجم زیادی از هارد را مصرف خواهد کرد و به ازای هر backup گیری یک فایل ایحاد می‌کند. در حالی که Differntial استفاده بهینه‌تری از فضای هارد داشته و فقط یک فایل ایجاد می‌کند. (ترجیحا از نوع Differntial استفاده شود)
۳.    در قسمت Destination در پایین صفحه، مسیر مورد نظر برای پشتیبان گیری مشخص می‌شود.
۴.    در بالای همین صفحه روی فلش کنار دکمه  Script کلیک میکنیم و از منوی باز شده گزینه Script action to job را باز میکنیم.
۵.    در صفحه باز شده و در قسمت سمت چپ صفحه از بخش Select a page‌ گزینه Schedules‌ را انتخاب میکنیم.
۶.    در همین صفحه روی دکمه New‌ کلیک میکنیم.
۷.    صفحه جدیدی به نام New Job Schedule باز خواهد شد.
۸.    در این صفحه بعد از نام گذاری، تنظیمات لازم و دلخواه در رابطه با زمانبندی Back Up گیری را انجام میدهیم.
۹.    در اینجا با کلیک روی دکمه OK صفحه را بسته تا صفحه New Job مجددا نمایش داده شود.
۱۰.    با کلیک روی Notifications و تیک زدن چک باکس Write to … یک Notify تعریف کنید. نوع این Notify را When the job completes تعریف کنید.
۱۱.    دکمه Ok را کلیک کنید تا به صفحه تعریف Backup برگردید.
۱۲.    دکمه Ok این صفحه را هم کلیک کنید تا صفحه بسته شود.
۱۳.    پایان


 
وارد کردن داده های یک فایل CSV در SQL Server
ساعت ٤:٢٩ ‎ب.ظ روز ۱۳۸۸/۳/۱٩  کلمات کلیدی: sql server ، bulk insert ، csv ، import data

شاید بعضی اوقات نیاز پیدا کرده باشید که داده هایی را در جداول SQL Server وارد کنید. این مطلب را که قبلا در جایی خوانده بودم را برای شما مینویسم تا شاید به درد بعضی از دوستان بخورد.
همانطور که میدانید CSV فایلیست که در آن مطالب به کمک کاما از هم جدا شده‌اند و یک فرمت استاندارد و شناخته شده میباشد که خیلی از نرم‌افزارهای بزرگ مانند excel هم آن را تولید و هم از آن داده دریافت میکنند.


۱-ابتدا توسط اسکریپت زیر یک جدول تستی بسازید :


Create TestTable
USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO


۲-یک فایل با فرمت CSV در درایو C و در آدرس C:\csvtest.txt بسازید که دارای محتوای زیر باشد :


 1,Masoud,Ramezani,19830101
2,Name1,Family1,19790122
3, Name2,Family2,20071101
4, Name3,Family3,20040202


۳- در این مرحله اسکریپت زیر را اجرا کنید تاداده ها از روی فایل مورد نظر روی جدول بارگزاری شود. توجه داشته باشید که اگر خطایی در حین انجام این کار رخ دهد فقط همان سطر وارد نمیشود و کار ادامه میابد.


BULK INSERT CSVTest
FROM 'c:\csvTest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

GO


۴-توسط اسکریپت زیر محتوای جدول را چک کنید.


SELECT *
FROM CSVTest
GO


۵- در انتها هم میتوانید این جدول تستی را از پایگاه داده حذف نمایید.


Drop Table CSVTest
GO


 
آموزش گام به گام قسمت بندی(partitioning) در SQL Server
ساعت ٤:۳٠ ‎ب.ظ روز ۱۳۸۸/۳/۱٧  کلمات کلیدی: sql server ، partitioning ، filegroup ، partition function

در ادامه مطلب قبلی با عنوان معرفی تکنیک قسمت بندی (partitioning) در SQL Server میخواهم مثالی در این زمینه مطرح کنم که شاید به درد خیلی از افراد بخورد. در این مثال نحوه قسمت بندی یک جدول را بیان میکنم. لطفا ابتدا مطلی قبل را برای آشنایی بیشتر مطالعه بفرمایید.

مرحله اول – یک پایگاه داده تستی با دو filegroup مختلف بسازید.
این متن برای بیان مثال از درایو C کامپیوتر استفاده میکند. اگرچه باد بیان کنم که برای به دست آوردن بهترین سطح کارایی قسمت‌بندی بهتر است که filegroup ها در دیسک‌های سخت مجزا تعریف شوند. قبل از اجرای اسکریپت زیر، مطمئن شوید که درایو C کامپیوتر شما حاوی دو فولدر میباشد.فولدرهای Primary و Secondary در مثال زیر دو فولدری هستند که برای filegroup های مختلف تعریف شده‌اند. اسکرپت زیر را برای ساختن پایگاه داده تستی اجرا نمایید.


USE Master;
GO
--- Step 1 : Create New Test Database with two different filegroups.
IF EXISTS (
SELECT name
FROM sys.databases
WHERE name = N'TestDB')
DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB
ON PRIMARY
(NAME='TestDB_Part1',
FILENAME=
'C:\Data\Primary\TestDB_Part1.mdf',
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP TestDB_Part2
(NAME = 'TestDB_Part2',
FILENAME =
'C:\Data\Secondary\TestDB_Part2.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 );
GO

مرحله دوم – تابع محدوده قسمت‌بندی را بسازید.
تابع قسمت‌بندی محدوده مقادیری که در قسمت متفاوت ذخیره شده است را تعریف مینماید. برای مثال اجازه دهید که تصور کنیم ۱۰ رکورد اول در یک filegroup و مابقی در filegroup دیگر ذخیره شده است. تابع زیر، تابع قسمت‌بندی را با محدوده مشخص تعریف خواهد کرد.


USE TestDB;
GO
--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION TestDB_PartitionRange (INT)
AS RANGE LEFT FOR
VALUES (10);
GO

مرحله سوم – طرح قسمت‌بندی را به filegroup ها الحاق کنید.
تابع قسمت‌بندی باید به filegroup ها الحاق شود تا در قسمت‌بندی جدول استفاده شود. در مثال زیر قسمت‌بندی روی filegroup های primary و secondary ساخته شده است.


USE TestDB;
GO
--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME TestDB_PartitionScheme
AS PARTITION TestDB_PartitionRange
TO ([PRIMARY], TestDB_Part2);
GO


مرحله چهارم – ساختن جدول با کلید و طرح قسمت‌بندی
جدولی که باید قسمت‌بندی شود باید به صورتی ساخته شود که دارای ستونی با نامی باشد که با  طرح قسمت‌بندی برای قسمت‌بندی جداول در filegroup های مختلف استفاده شود. مثال زیر ستونهای شناسه را به عنوان کلید قسمت‌بندی نشان داده است.


USE TestDB;
GO
--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
(ID INT NOT NULL,
Date DATETIME)
ON TestDB_PartitionScheme (ID);
GO


مرحله پنجم – ساخت اندیس روی جدول قسمت‌بندی شده که بحثی اختیاری و توصیه شده است.
این مرحله اختیاریست اما به شدت توصیه شده است. مثال زیر نحوه ساخت اندیسهای ردیف شده جدول را نشان میدهد. در اینجا اندیس با استفاده از طرح قسمت‌بندی و کلید قسمت‌بندی به عنوان یک جدول قسمت‌بندی شده ساخته شده است.


USE TestDB;
GO
--- Step 5 : (Optional/Recommended) Create Index on Partitioned Table
CREATE UNIQUE CLUSTERED INDEX IX_TestTable
ON TestTable(ID)
ON TestDB_PartitionScheme (ID);
GO


مرحله ششم -  وارد کردن داده در جدول قسمت‌بندی شده
داده ها را در جدول قسمت‌بندی شده وارد کنید. در اینجا کل ۳ رکورد را وارد میکنیم. قبلا تصمیم گرفته بودیم که در قسمت‌بندی اول شناسه کلیدهای قسمت‌بندی یک تا ده را قرار دهیم و مابقی رکوردها را در قسمت دوم قرار دهیم. در مثال زیر رکورد با شناسه (ID) برابر ۱ وارد قسمت اول شده است و مابقی در قسمت دوم وارد خواهند شد.


USE TestDB;
GO
--- Step 6 : Insert Data in Partitioned Table
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
VALUES (1,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (11,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (12,GETDATE());
GO


مرحله هفتم – داده را از جدول تستی آزمایش کنید.
روی جدول TestTable جستجو بزنید و داده های وارد شده در آن را ببینید.


USE TestDB;
GO
--- Step 7 : Test Data from TestTable
SELECT *
FROM TestTable;
GO

مرحله هشتم – درستی یا نادرستی وارد شدن داده ها را بررسی نمایید.
میتوانیم روی view با نام sys.partitions کوئری بزنیم و بررسی کنیم که جدول TestTable شامل دو قسمت است و همانطور که در مرحله ۶ بیان شد، یک رکورد وارد قسمت ۱ و دو رکورد وارد قسمت ۲ شده‌اند.


USE TestDB;
GO
--- Step 8 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO

منبع مطلب

 


 
معرفی تکنیک قسمت بندی (partitioning) در SQL Server
ساعت ٦:۱٤ ‎ب.ظ روز ۱۳۸۸/۳/۱٢  کلمات کلیدی: performance ، سطح کارایی ، sql server ، partitioning

قسمت بندی (partitioning) یک فرآیند یا روش است که توسط آن جداول خیلی بزرگ و اندیسها(index) به چندین بخش کوچکتر و بخشهای قابل مدیریت تر تقسیم میشود. SQL Server نسخه ۲۰۰۵ به بعد این اجازه را میدهد که جداول را با استفاده از محدوده‌های تعریف شده قسمت بندی کرد و همچنین خصوصیات مدیریتی و ابزارهایی را برای نگه داشتن جداول قسمتبندی شده در سطح کارایی بهینه ارائه داده است.


جداول بر اساس ستونهایی که برای عملیات قسمت بندی استفاده میشوند و محدوده‌های مشخص شده برای هر قسمت، تقسیم بندی میشوند. نمونه‌ای از این ستون میتواند ستون شناسه صعودی باشد که میتواند در محدوده‌های مختلف قسمت بندی شود. محدوده ‌های مختلف میتوانند در قسمت‌های مختلف باشند، قسمت‌های میتوانند در filegroup های مختلف باشند و قسمت‌های مختلف میتوانند روی هارد دیسکهای مختلف باشند تا سطح کارایی را بالا ببرند.


تقسیم‌بندی‌ها میتوانند به راحتی با استفاده از رویه ها و توابع راه اندازی شوند، به علاوه جداول غول‌آسا میتوانند به سادگی برای هر قسمت دارای اندیسهای مجزا باشند..این میتواند به بالا بردن سطح کارایی منجر شود. این تکنیک زمانی باعث بالا رفتن کارایی میشود که سرعت هارد دیسک گلوگاه کارایی سیستم باشد. اگر CPU یا RAM گلوگاه سیستم باشند، این تکنیک کمک بیشتری نمیکند.


 
جستجوی تمامی جداول یک پایگاه داده برای یافتن یک مقدار مورد نظر
ساعت ٥:۱٤ ‎ب.ظ روز ۱۳۸۸/٢/٢٧  کلمات کلیدی: sql server ، stored procedure ، جستجو ، full text search

چند وقتی بود که به دنبال راهی برای جستجو کردن جداول یک database برای یافتن یک مقدار خاص میگشتم. یک راه به نظر خودم میرسید و آن این بود که واقعا می آمدم و یک foreach روی تمام جداول database میزدم و به دنبال مقدار مورد نظر خودم میشگتم. اما به نظرم آمد که شاید راه حل بهتری هم موجود باشد، به همین دلیل بود که روی اینترنت یک جستجوی مختصر کردم و به نتایج جالب زیر رسیدم :
شما میتوانید یک stored procedure بسازید که محتوای آن در ادامه بیان میشود. این ساختار یک رشته را به عنوان ورودی دریافت میکند و آن را به عنوان کلمه مورد جستجو در نظر میگیرد. سپس روی تمام ستونهای از نوع char، varchar، nchar و nvarchar موجود در جداول database جستجو میکند (البته روی جداول سیستمی این کار ار انجام نمیدهد) و دنبال نتایج مورد نظر میگردد. در ضمن میتوان این stored procedure را برای پشتیبانی از دیگر انواع داده ای گسترش داد.
خروجی این sp دارای دو ستون است :
-    نام جدول و نام ستونی که مقدار مورد نظر در آن پیدا شده
-    محتوای واقعی ستون (البته تا ۳۶۳۰ کاراکتر نمایش داده میشود).

برای استفاده از این sp باید کمی محتاط باشید، زیرا این عملیات روی ساختارهای کوچک بسیار سریع است اما برای ساختارهای بزرگ با تعداد جدول زیاد و حجم داده بالا روی آنها بسیار زمانگیر است. پس اگر میخواهید از آن استفاده کنید بهتر است که قبلا کامل فکرهایخود را انجام دهید. این بهتر که در چنین مواقعی از Full-Text Search استفاده نمایید که این کار هم مشکلات خود را دارد.
اسکریپت زیر روی پایگاه داده Pubs به دنبال کلمه Computer میگردد :


EXEC SearchAllTables 'Computer'
GO


و این هم کد کامل store procedure میباشد :


CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN


    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName =
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
   
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END   
    END

    SELECT ColumnName, ColumnValue FROM #Results
END

 

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

مرجع