الرئيسية
أحدث الصور
جستجو
ثبت نام
ورودsql server 2008
صفحه 1 از 1
sql server 2008
asie ahmadi الأربعاء نوفمبر 18, 2009 3:41 am
SQL SERVER 2008
خروجی یک فیلد به صورت comma separated
به کمک کوئری زیر میتوانید نتایج یک فیلد در پایگاه داده را به صورت comma separated دریافت کنید.
DECLARE @NameList nvarchar(Max)
SELECT @NameList = COALESCE(@NameList + ', ', '') +
ColumnName
FROM TableName
WHERE ID < 10
SELECT @NameList
چگونه عملیات صفحه بندی (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 از آن استفاده کنید باید ابتدا یک کوئری داشته باشید که تعداد کل سطرها را به شما بدهد و بر اساس این مقدار تعداد صفحات مورد نظر را به دست آورید. پس از آن با کلیک روی هر کدام از شماره صفحات آن را به عنوان مقدار به پارامتر مورد نظر بفرستید و از آن لذت ببرید.
تنظیم SQL Server برای Back Up گرفتن به صورت خودکار (تعریف 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 وارد کنید. همانطور که میدانید 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
مرحله اول – یک پایگاه داده تستی با دو 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
قسمت بندی (partitioning) یک فرآیند یا روش است که توسط آن جداول خیلی بزرگ و اندیسها(index) به چندین بخش کوچکتر و بخشهای قابل مدیریت تر تقسیم میشود. SQL Server نسخه ۲۰۰۵ به بعد این اجازه را میدهد که جداول را با استفاده از محدوده‌های تعریف شده قسمت بندی کرد و همچنین خصوصیات مدیریتی و ابزارهایی را برای نگه داشتن جداول قسمتبندی شده در سطح کارایی بهینه ارائه داده است.
جداول بر اساس ستونهایی که برای عملیات قسمت بندی استفاده میشوند و محدوده‌های مشخص شده برای هر قسمت، تقسیم بندی میشوند. نمونه‌ای از این ستون میتواند ستون شناسه صعودی باشد که میتواند در محدوده‌های مختلف قسمت بندی شود. محدوده های مختلف میتوانند در قسمت‌های مختلف باشند، قسمت‌های میتوانند در filegroup های مختلف باشند و قسمت‌های مختلف میتوانند روی هارد دیسکهای مختلف باشند تا سطح کارایی را بالا ببرند.
تقسیم‌بندی‌ها میتوانند به راحتی با استفاده از رویه ها و توابع راه اندازی شوند، به علاوه جداول غول‌آسا میتوانند به سادگی برای هر قسمت دارای اندیسهای مجزا باشند..این میتواند به بالا بردن سطح کارایی منجر شود. این تکنیک زمانی باعث بالا رفتن کارایی میشود که سرعت هارد دیسک گلوگاه کارایی سیستم باشد. اگر CPU یا RAM گلوگاه سیستم باشند، این تکنیک کمک بیشتری نمیکند.
جستجوی تمامی جداول یک پایگاه داده برای یافتن یک مقدار مورد نظر
:
شما میتوانید یک 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
رهگیری تغییرات داده ای در SQL Server 2008
مکانیزم جدیدی در SQL Server 2008 وجود دارد که به شما این اجازه را میدهد تا تغییرات داده ای در یک جدول را پیگیری کنید. تغییرات از لاگ مربوط به تراکنشها توسط پروسس capture خوانده شده و در جداول تغییر ذخیره میشود. جداول تغییر ستونهای جدول منبع و همچنین اطلاعات حاوی metadata را که میتواند برای دریافتن تغییراتی که رخ داده استفاده شود را mirror میکند. آن تغییرات میتوانند در یک فرمت ارتباطی مناسب در TVF ها مصرف شوند.
پروسس transform و load (ETL) در سرویسهای Integration در SQL Server که به روزرسانیهای نهایی را به انبار داده ها اعمال میکند تنها یک مثال از برنامه هایی است که از این خاصیت سود برده است.
در اینجا یک فرآیند ساده برای گرفتن تغییرات جدول Employee در پایگاه داده ای که testdb صدا زده میشود نشان خواهم داد.
قبل از اینکه بتوانید جداول را برای این خصوصیت فعال کنید، باید ابتدا این خاصیت را برای پایگاه داده توسط stored procedure با نام sys.sp_cdc_enable_db فعال کنید. این sp چندین آبجکت سیستمی مربوط به این کار را که شامل cdc schema و cdc user ، جداول، job ها، sp ها و توابع میشود را میسازد. برای اینکه چک کنید آیا این خصوصیت در پایگاه داده شما فعال است یا نه، query با نام is_cdc_enabled را روی sys.databases اجرا نمایید. کد زیر پایگاه داده testdb را ساخته و این خصوصیت را روی آن فعال میکند:
USE master;
IF DB_ID('testdb') IS NOT NULL DROP DATABASE testdb;
CREATE DATABASE testdb;
GO
USE testdb;
EXECUTE sys.sp_cdc_enable_db;
برای از کار انداختن رهگیری داده ای روی پایگاه داده، از sp با عنوان sys.sp_cdc_disable_db استفاده نمایید.
پردازش رهگیری به محض اینکه اولین جدول در پایگاه داده برای رهگیری توانا میشود، تغییرات را از log خوانده و آنها را در جداول تغییر ثبت میکند. برای راه انداختن رهگیری داده ای برای یک جدول از sp با نام sys.sp_cdc_enable_table استفاده کنید. توجه داشته باشید که برای جمع آوری تغییرات باید SQL Server Agent در حال اجرا باشد. کد زیر یک جدول با نامEmployees در testdb ساخته و یک سطر در آن وارد میشود :
CREATE TABLE dbo.Employees
(
empid INT NOT NULL,
name VARCHAR(30) NOT NULL,
salary MONEY NOT NULL
);
INSERT INTO dbo.Employees(empid, name, salary) VALUES(1, 'Emp1', 1000.00);
کد زیر به جدول این توانایی را میدهد که تغییرات داده ای را رهگیری کند :
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'Employees'
, @role_name = N'cdc_Admin';
چون این اولین جدولیست که در پایگاه داده برای این کار آماده شده است، این کد سبب میشود که پردازش مربوط به این کار شروع شود (دو job اجرا میشود : cdc.testdb_capture و cdc.testdb_cleanup). آرگومان @role_name شما را قادر میکند تا یک database role تعیین کنید که دارای دسترسی برای تغییر داده خواهد بود. اگر role معین شده وجود نداشته باشد، SQL Server خود آن را خواهد ساخت. به صورت پیش فرض، تغییرات برای تمام ستونهای جدول جمع آوری میشود. برای جمع آوری تغببرات فقط برایزیرمجموعه ای از ستونها میتوانید لیست ستونها را در آرگومان @captured_column_list مشخص نمایید. برای گرفتن اطلاعات metadata درباره ستونهایی که نمونه جمع آوری به حساب آمده اند، میتوانید از procedure با نام sys.sp_cdc_get_captured_columns استفاده نمایید.
اگر بعدا خواستید که رهگیری تغییرات داده را غیرفعال نمایید از sys.sp_cdc_disable_table استفاده نمایید. و اگر میخواهید ببینید که آیا یک جدول برای این کار فعال شده است، روی view با نام sys.tables ستون is_tracked_by_cdc را جستجو کنید.
برای بدست آوردن اطلاعات درباره پیکربندی رهگیری تغییر داده برای هر جدول فعال شده، sp با نام sys.sp_cdc_help_change_data_capture را مانند زیر استفاده نمایید :
EXECUTE sys.sp_cdc_help_change_data_capture;
استفاده ازاین تغییرات به صورت مستقیم از جداول تغییرمیسر نیست بلکه باید از توابع table-valued استفاده نمایید. داده برای تغییراتی که درون یک محدوده مشخص شده از شماره سریالهای log (LSNs) قرار گرفته باشند درخواست شده است. SQL SERVER 2008 همچنین توابع نگاشت sys.fn_cdc_map_time_to_lsn و sys.fn_cdc_map_sln_to_time را مهیا کرده که به شما کمک میکند تا یک محدوده date-time را به محدوده ای از LSNs و بالعکس تبدیل کنید.
توابع مجزا تمامی تغییرات داخل یک ورودی (cdc.fn_cdc_get_all_changes_<capture_instance>) و تغییرات شبکه که در مقابل سطرهای مجزا جای گرفته اند (cdc.fn_cdc_get_net_changes_<capture_instance>) را مهیا میکنند، البته اگر که توسط sys.sp_cdc_enable_table فعال شده باشند.
SQL Server 2008 همچنین یک sp با نام sys.sp_cdc_get_ddl_history دارد که به شما تاریخچه تغییرات DDL پیوسته با یک نمونه خاص رهگیری را میدهد.
SQL Server Reporting Service چیست؟
در سال ۲۰۰۳ یک add-on به SQL Server نسخه 2000 به عنوان یک سرویس گزارشگیری افزوده شد. این سرویس تا نسخه 2005 خیلی کاربردی نبود. از سال 2005 مایکروسافت سرمایه گذاری خوبی را در این زمینه که قبل از آن شرکتهایی در آن فعالیت میکردند آغاز کرد. اینگونه بود که از این مقطع زمانی به بعد مایکروسافت اقدامات خوبی را در این زمینه شروع کرد و آن را از حالت یک add-on خارج کرد.
نصب
زمانی که میخواهید نسخه های 2005 و 2008 از SQL Server را نصب کنید در یکی از مراحل باید Reporting Service را برای نصب انتخاب کنید. در ادامه باید تیک مربوط به Configure کردن خودکار Reporting Server را بزنید. اگر این گزینه را انتخاب نکنبد باید عملیات پیکربندی را به صورت دستی انجام دهید.
معماری SSRS
سه کامپوننت اصلی وجود دارد که با یکدیگر کار کرده و SSRS را به یک برنامه چندلایه واقعی تبدیل میکند. این سه عبارتند از :
1. Client
2. Report Server
3. SQL Server Report Databases
شکل زیر یک تفکیک مفهومی از این سه بخش و اجزا آن است.
در اینجا data source و SSRS databases ، ReportServer و ReportServerTempDB موجودیتهای مجزایی هستند. Data Source منبع و منشا اصلی داده ها برای پر کردن گزارشات است و Report Server Databases وظیفه ذخیره سازی اطلاعات مربوط به گزارشات را بر عهده دارند. هر دوی این دو میتوانند به صورت فیزیکی در یک SQL Server مشترک قرار گیرند، فزض کنید که data source یک پایگاه داده SQL Server است. Data source میتواند data provider های مختلف مثل SQL Server، Oracle، LDAP و یا Analysis Service را پشتیبانی نماید. این امر ممکن است که شما یک سرور واحد را هم برای SSRS Report Server Web Service و هم برای Report Server Database پیکربندی کنید. اگر چه این کار توصیه نمیشود مگر اینکه تعداد کاربر شما کم باشد.
نصب SSRS دو پایگاه داده برای شما میسازد :
ReportServer : این پایگاه داده اصلی است که تمامی اطلاعات را از فایل RDL منتشر شده (published) در حود ذخیره میکند. همچنین مواردی چون خصوصیات گزارش (مانند data sourceها)، پارامترها و فایلهای لاگ را در خود نگهداری میکند.
ReportServerTempDB : این پایگاه داده کپیهای cach شده گزارشات را برای بالا بردن کارایی به جهت استفاده همزمان کاربران در خود دارد. با این مکانیزم این اطمینان را داشته باشیم که حتی در صورت restart شدن سرور باز هم گزارشات در دسترس هستند.
SSRS Report Server
SSRS Report Server مهمترین نقش را در مدل SSRS ایفا میکند. در میانه کار کرده و برای هر درخواست از سمت client برای render کردن یک گزارش یا برای انجام یک درخواست مدیریتی مانند ساخت یک subscription مسیول است. شما متوانید یک report server را با توجه به عملیاتهای مختلف به چندین زیربخش تفکیک کنید :
· Programming interface
· Report processing
· Data processing
· Report rendering
· Report scheduling and delivery
SSRS Web Service Interface
رابط برنامه نویسی که به عنوان API های .NET Web Service و متدهای دسترسی URL ظاهر میشوند، تمامی درخواستهای ورودی از client ها را خواه درخواست گزارش باشد، خواه درخواست مدیریتی، اداره میکند.با توجه به نوع درخواست، رابط برنامه نویسی با دسترسی به پایگاه داده Report Server یا ارسال آن به کامپوننت دیگر برای پردازش اقدام میکند.
Report Processor
این کامپوننت مسیول تمامی درخواستهای گزارش است. مانند رابط برنامه نویسی، مستقیما به پایگاه داده ReportServer متصل میشود تا اطلاعات مربوط به تعریف گزارش را دریافت کند که این اطلاعات بعدا برای ترکیب با اطلاعات data source و ساخت گزارش به کار میرود.
Data Processing
SSRS چهار نوع الحاقیه برای اتصال به پایگاه داده حمایت میکند :
1. SQL Server
2. Oracle
3. OLE DB
4. ODBC
وقتی که این کامپوننت یک درخواست را Report Processor دریافت میکند، یک اتصال به data source میزند و آن را به query منبع ارسال میکند. داده بازگردانده شده و به Report Processor فرستاده میشود تا با تعریف گزارش ترکیب شود.
Report Rendering
این کامپوننت وظیفه render گزارش در فرمت مورد نظ را دارد. فرمتهای مورد حمایت عبارتند از :
· HTML
· PDF
· HTML با استفاده از کامپوننتهای وبی Office
· Excel
· CSV
· MHTML
· TIFF
برنامه های Client
SSRS شامل چندی برنامه است که از آن استفاده میکنند. این برنامه ها و ابزارها، مدیریت Report Server ، پیاده سازی امنیت و وظایف render کردن گزارش را برای مهیا میکنند. این ابزارها مانند زیرند :
• Report Manager
این یک برنامه browser base همراه با SSRS است که محیطی گرافیکی را برای کاربران مهیا میکند که توسط آن میتوانند گزارشات را ببینند یا چاپ کنند، یا اینکه گزارشات را برای بخشهای مختلف مدیریت کنند.
• SQL Server Business Intelligence Development Studio (BIDS)
اگر شما روی سیستم خود Visual Studio نداشته باشید و SSRS را نصب کنید، خود SQL Server برای شما Visual Studio IDE را برای شما نصب میکند تا شما بتوانید به راحتی به طراحی و تست گزارشات خود بپردازید. این IDE را با نام SQL Server Business Intelligence Development Studio نصب کرده و با انتخاب آن میتوانید به راحتی به آن دست یابید. البته باید بگویم برای ساخت گزارشات وجود این IDE الزامی نیست.
• ابزارهای Command-line
شما میتوانید از چندین ابزار Command-line مانند rs ، rsconfig و RSKeyMgmt برای پیکربندی و مدیریت محیط SSRS استفاده نمایید.
• Custom Clients
• Reporting Services Configuration Manager
ابزاری برای پیکربندی به صورت گرافیکیست که از نسخه ۲۰۰۵ به بعد به وجود آمد.
انواع گزارشات و ساختار آنها
به طور کلی دو نوع گزارش در RS موجود است:
1. Client Report
2. Server Report
در Visual Studio 2005 به بعد کنترل جدیدی به آن افزوده شد با نام MicrosoftReportViewer که مخصوص نمایش گزارشات بود. گزارشات فایلهایی با پسوند rdl برای گزارش remote و rdlc برای گزارش clientهستند که میتوانید آن را به پروژه خود بیفزایید. پس از افزودن آن چندین ابزار به شما برای ساخت گزارش داده خواهد شد. شما میتوانید با این ابزارها گزارش مورد نظر خود را بسازید.
RDL یک مدل بر مبنای XML است که هر عنصر گزارش مانند قالب بندی، اطلاعات dataset، grouping و sorting، پارامترها و فیلترها را تعریف میکند. همانطور که شما آیتمی را اضافه میکنید، این ساختار دچار تغییرات میشود.
در IDE این ساختار از شما مخفیست ولی در صورت نیاز به تغییر کلی در گزارشات ، میتوانید از find and replace در این ساختار به صورت مستقیم استفاده نمایید. در VS 2005 و بعد از آن با زدن دکمه F7 در زمان کار با گزارش میتوانید این ساختار را ببینید. به تکه ای از این ساختار در یک گزارش توجه کنید :
در نسخه ۲۰۰۵ نه ابزار برای ساخت گزارش وجود داشت که در نسخه ۲۰۰۸ کمی تغییر کردند. لیست این ابزار در تصویر زیر آمده است:
هر گزارش شامل سه بخش ۱- Header ۲- Body ۳- Footer است که در هرکدام از برخی از این ابزار میتوان استفاده کرد. شما میتوانید از امکانات زیر در گزارشات استفاده نمایید که من فقط آنها را نام میبرم :
• نوشتن expression برای اکثر اجزای گزارش مثلا امکان visibility و ...
• استفاده از توابع از قبل آماده در نوشتن expression
• نوشتن expression با زبان VB (مثلا مد تغییر تاریخ)
• استفاده از پارامترها برای نوشتن query پویا
• استفاده از فایلهای اسمبلی (dll) : مثلا نوشتن تابعی با زبان c# و استفاده ازآن در گزارش
• کنترل قوی روی اجزا گزارش
• امکان قرار دادن sorting و filter بر اساس عناصر محتلف گزارش
• امکان استفاده از گروه بندی های داده ای تودرتو
• امکان استفاده از stored procedure و text query به عنوان dataset
• امکان ساخت گزارشات تعاملی مثلا استفاده از زیرگزارشات
• استفاده از چندین dataset به عنوان تامین کننده داده
• امکان دادن به کاربر برای ساختن گزارشات دلخواه با استفاده از report model
تفاوتهای موجود بین دو نوع گزارش
ساختار گزارشات rdl و rdlc کاملا شبیه هم است ولی در نحوه اجرا و تامین داده با هم کم متفاوت هستند. تفاوتهای موجود را به ترتیب در زیر بیان میکنم :
۱- گزارشات server باید در یک پروژه از نوع report server project و در محیط BIDS افزوده شوند در صورتیکه گزارشات client در یک پروژه معمولی .NET اضافه میشوند.
۲- گزارشات server از یک query مستقیم که به صورت stored procedure یا text است به عنوان تامین کننده داده استفاده میکنند در حالی که گزارشات client باید در کد مقداردهی شوند. مثلا داده مربوط به آنها میتواند توسط یک dataset یا یک xml data source تامین شود. در ضمن برای انجام این کار احتیاج به نوشتن مقداری کد مثلا در c# است.
۳-برای نمایش داده شدن گزارشات server کنترل MicrosoftReportViewer صرفا وظیفه نمایش گزارش در انتهای عملیات را دارد و تمام عملیات برای تهیه گزارش و render آن در report server انجام میشود. این در حالیست که برای نمایش گزارش client کنترل MicrosoftReportViewer نقش اساسی را انجام میدهد و عملیات render شدن گزارش در این کنترل انجام میشود.
۴-گزارشات client به نصب SSRS نیازی ندارند در حالیکه گزارشات server بدون این کار قابل دسترسی نیستند.
۵- کنترل MicrosoftReportViewer در گزارشات client و در نسخه ۲۰۰۵ دارای دکمه چاپ نبودند ولی گزارشات server این مشکل را نداشتند. البته لازم به ذکر است این مشکل در نسخه ۲۰۰۸ حل شده است.
۶-گزارشات client نیازی به publish روی report server ندارند در حالیکه گزارشات server برای اجرا شدن حتما باید publish شوند.
نکته : من این نوشته را بر اساس نسخه ۲۰۰۵ نوشتم و باید بیان کنم که نسخه ۲۰۰۸ بهبودهای خوبی داشته است. البته اصول تغییری نکرده است. در نوشته های آینده سعی میکنم مثالهایی را بر اساس نسخه ۲۰۰۸ بنویسم.
وابستگی های اشیا در SQL Server 2008
SQL Server 2008 چندین آبجکت ارائه داده که اکتشاف وابستگیهای آبجکتها را به صورت قابل اطمینانی فراهم میکند، و جایگزین view قدیمی وغیرقابل اطمینان sys.sql_dependencies وهمچنین stored procedure قدیمی sp_depends شده است. آبجکتهای جدید اطلاعاتی درباره وابستگیها فراهم کرده که در یک کد ثابت ظاهر میشود، آنها شامل هر دوی آبجکتهای schema-bound و non-schema-bound و نیز cross-database و حتی وابستگیهای cross-server میشوند. آبجکتهای جدید وابستگیهایی را که در کد SQL یا کد CLR به وجود بیایند را پوشش نمیدهند.
برای اینکه ببینید چگونه اطلاعات مربوط به وابستگیها را جستجو کنید، ابتدا کد زیر را اجرا کنید تا چند آبجکت در پایگاه داده tempdb برای تست ساخته شود :
USE tempdb;
IF OBJECT_ID('dbo.Proc1', 'P') IS NOT NULL DROP PROC dbo.Proc1;
IF OBJECT_ID('dbo.V1', 'V') IS NOT NULL DROP VIEW dbo.V1;
IF OBJECT_ID('dbo.V2', 'V') IS NOT NULL DROP VIEW dbo.V2;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.T2', 'U') IS NOT NULL DROP TABLE dbo.T2;
GO
CREATE PROC dbo.Proc1
AS
SELECT * FROM dbo.T1;
EXEC('SELECT * FROM dbo.T2');
GO
CREATE TABLE dbo.T1(col1 INT);
CREATE TABLE dbo.T2(col2 INT);
GO
CREATE VIEW dbo.V1
AS
SELECT col1 FROM dbo.T1;
GO
CREATE VIEW dbo.V2
AS
SELECT col1 FROM dbo.T1;
GO
SQL Server 2008 سه آبجکت جدید را معرفی کرده که اطلاعاتی درباره وابستگی آبجکت به ما میدهد : catalog view با نام sys.sql_expression_dependencies ، و توابع مدیریت پویا (DMF) با نامهای sys.dm_sql_referenced_entities و sys.dm_sql_referencing_entities .
View اول وابستگیهای آبجکت را توسط نام فراهم میکند. این جایگزین view قدیمیتر با نام sys.sql_dependencies شده است. Query زیر در مقابل sys.sql_expression_dependencies تمام وابستگیها در پایگاه داده کنونی را نشان میدهد:
SELECT
OBJECT_SCHEMA_NAME(referencing_id) AS srcschema,
OBJECT_NAME(referencing_id) AS srcname,
referencing_minor_id AS srcminorid,
referenced_schema_name AS tgtschema,
referenced_entity_name AS tgtname,
referenced_minor_id AS tgtminorid
FROM sys.sql_expression_dependencies;
Query بالا خروجی زیر را تولید میکند :
srcschema srcname srcminorid tgtschema tgtname tgtminorid
--------- ------- ---------- --------- ------- ----------
dbo Proc1 0 dbo T1 0
dbo V1 0 dbo T1 0
dbo V2 0 dbo T1 0
توجه کنید که query تمام وابستگیها در کد ثابت را شناسایی میکند نه وابستگیها در کد پویا را (ارتباط از dbo.T2 به dbo.Proc1).
DMF با نام sys.dm_sql_referenced_entities تمام entity هایی که entity ورودی به آن مراجعه میکند را مهیا میکند – به عبارت دیگر، تمام entity هایی که entity ورودی به آن وابسته است. به عنوان مثال، کد زیر تمام entity هایی که dbo.Proc1 به آن وابسته است را برمیگرداند :
SELECT
referenced_schema_name AS objschema,
referenced_entity_name AS objname,
referenced_minor_name AS minorname,
referenced_class_desc AS class
FROM sys.dm_sql_referenced_entities('dbo.Proc1', 'OBJECT');
این کد خروجی زیر را برمیگرداند :
objschema objname minorname class
--------- --------- --------- ----------------
dbo T1 NULL OBJECT_OR_COLUMN
dbo T1 col1 OBJECT_OR_COLUMN
خروجی نشان میدهد که dbo.Proc1 به جدول dbo.T1 و ستون dbo.T1.col1 وابسته است. مجددا بیان میکنم که وابستگیهایی که در کد پویا نشان داده میشود، شناسایی نمیشود.
DMF با نام sys.dm_sql_referencing_entities تمام entity هایی که به entity ورودی ارجاع داده اند را فراهم میکند – به عبارت دیگر، تمام entity هایی که به entity ورودی وابسته هستند. برای مثال،کد زیر تمام entity هایی را که dbo.T1 وابسته هستند را بر میگرداند:
SELECT
referencing_schema_name AS objschema,
referencing_entity_name AS objname,
referencing_class_desc AS class
FROM sys.dm_sql_referencing_entities('dbo.T1', 'OBJECT');
این کد خروجی زیر را بازمیگرداند :
objschema objname class
--------- --------- ----------------
dbo Proc1 OBJECT_OR_COLUMN
dbo V1 OBJECT_OR_COLUMN
dbo V2 OBJECT_OR_COLUMN
خروجی نشان میدهد که dbo.Proc1 و dbo.V1 و dbo.V2 به dbo.T1 وابسته اند.
خروجی یک فیلد به صورت comma separated
به کمک کوئری زیر میتوانید نتایج یک فیلد در پایگاه داده را به صورت comma separated دریافت کنید.
DECLARE @NameList nvarchar(Max)
SELECT @NameList = COALESCE(@NameList + ', ', '') +
ColumnName
FROM TableName
WHERE ID < 10
SELECT @NameList
چگونه عملیات صفحه بندی (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 از آن استفاده کنید باید ابتدا یک کوئری داشته باشید که تعداد کل سطرها را به شما بدهد و بر اساس این مقدار تعداد صفحات مورد نظر را به دست آورید. پس از آن با کلیک روی هر کدام از شماره صفحات آن را به عنوان مقدار به پارامتر مورد نظر بفرستید و از آن لذت ببرید.
تنظیم SQL Server برای Back Up گرفتن به صورت خودکار (تعریف 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 وارد کنید. همانطور که میدانید 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
مرحله اول – یک پایگاه داده تستی با دو 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
قسمت بندی (partitioning) یک فرآیند یا روش است که توسط آن جداول خیلی بزرگ و اندیسها(index) به چندین بخش کوچکتر و بخشهای قابل مدیریت تر تقسیم میشود. SQL Server نسخه ۲۰۰۵ به بعد این اجازه را میدهد که جداول را با استفاده از محدوده‌های تعریف شده قسمت بندی کرد و همچنین خصوصیات مدیریتی و ابزارهایی را برای نگه داشتن جداول قسمتبندی شده در سطح کارایی بهینه ارائه داده است.
جداول بر اساس ستونهایی که برای عملیات قسمت بندی استفاده میشوند و محدوده‌های مشخص شده برای هر قسمت، تقسیم بندی میشوند. نمونه‌ای از این ستون میتواند ستون شناسه صعودی باشد که میتواند در محدوده‌های مختلف قسمت بندی شود. محدوده های مختلف میتوانند در قسمت‌های مختلف باشند، قسمت‌های میتوانند در filegroup های مختلف باشند و قسمت‌های مختلف میتوانند روی هارد دیسکهای مختلف باشند تا سطح کارایی را بالا ببرند.
تقسیم‌بندی‌ها میتوانند به راحتی با استفاده از رویه ها و توابع راه اندازی شوند، به علاوه جداول غول‌آسا میتوانند به سادگی برای هر قسمت دارای اندیسهای مجزا باشند..این میتواند به بالا بردن سطح کارایی منجر شود. این تکنیک زمانی باعث بالا رفتن کارایی میشود که سرعت هارد دیسک گلوگاه کارایی سیستم باشد. اگر CPU یا RAM گلوگاه سیستم باشند، این تکنیک کمک بیشتری نمیکند.
جستجوی تمامی جداول یک پایگاه داده برای یافتن یک مقدار مورد نظر
:
شما میتوانید یک 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
رهگیری تغییرات داده ای در SQL Server 2008
مکانیزم جدیدی در SQL Server 2008 وجود دارد که به شما این اجازه را میدهد تا تغییرات داده ای در یک جدول را پیگیری کنید. تغییرات از لاگ مربوط به تراکنشها توسط پروسس capture خوانده شده و در جداول تغییر ذخیره میشود. جداول تغییر ستونهای جدول منبع و همچنین اطلاعات حاوی metadata را که میتواند برای دریافتن تغییراتی که رخ داده استفاده شود را mirror میکند. آن تغییرات میتوانند در یک فرمت ارتباطی مناسب در TVF ها مصرف شوند.
پروسس transform و load (ETL) در سرویسهای Integration در SQL Server که به روزرسانیهای نهایی را به انبار داده ها اعمال میکند تنها یک مثال از برنامه هایی است که از این خاصیت سود برده است.
در اینجا یک فرآیند ساده برای گرفتن تغییرات جدول Employee در پایگاه داده ای که testdb صدا زده میشود نشان خواهم داد.
قبل از اینکه بتوانید جداول را برای این خصوصیت فعال کنید، باید ابتدا این خاصیت را برای پایگاه داده توسط stored procedure با نام sys.sp_cdc_enable_db فعال کنید. این sp چندین آبجکت سیستمی مربوط به این کار را که شامل cdc schema و cdc user ، جداول، job ها، sp ها و توابع میشود را میسازد. برای اینکه چک کنید آیا این خصوصیت در پایگاه داده شما فعال است یا نه، query با نام is_cdc_enabled را روی sys.databases اجرا نمایید. کد زیر پایگاه داده testdb را ساخته و این خصوصیت را روی آن فعال میکند:
USE master;
IF DB_ID('testdb') IS NOT NULL DROP DATABASE testdb;
CREATE DATABASE testdb;
GO
USE testdb;
EXECUTE sys.sp_cdc_enable_db;
برای از کار انداختن رهگیری داده ای روی پایگاه داده، از sp با عنوان sys.sp_cdc_disable_db استفاده نمایید.
پردازش رهگیری به محض اینکه اولین جدول در پایگاه داده برای رهگیری توانا میشود، تغییرات را از log خوانده و آنها را در جداول تغییر ثبت میکند. برای راه انداختن رهگیری داده ای برای یک جدول از sp با نام sys.sp_cdc_enable_table استفاده کنید. توجه داشته باشید که برای جمع آوری تغییرات باید SQL Server Agent در حال اجرا باشد. کد زیر یک جدول با نامEmployees در testdb ساخته و یک سطر در آن وارد میشود :
CREATE TABLE dbo.Employees
(
empid INT NOT NULL,
name VARCHAR(30) NOT NULL,
salary MONEY NOT NULL
);
INSERT INTO dbo.Employees(empid, name, salary) VALUES(1, 'Emp1', 1000.00);
کد زیر به جدول این توانایی را میدهد که تغییرات داده ای را رهگیری کند :
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'Employees'
, @role_name = N'cdc_Admin';
چون این اولین جدولیست که در پایگاه داده برای این کار آماده شده است، این کد سبب میشود که پردازش مربوط به این کار شروع شود (دو job اجرا میشود : cdc.testdb_capture و cdc.testdb_cleanup). آرگومان @role_name شما را قادر میکند تا یک database role تعیین کنید که دارای دسترسی برای تغییر داده خواهد بود. اگر role معین شده وجود نداشته باشد، SQL Server خود آن را خواهد ساخت. به صورت پیش فرض، تغییرات برای تمام ستونهای جدول جمع آوری میشود. برای جمع آوری تغببرات فقط برایزیرمجموعه ای از ستونها میتوانید لیست ستونها را در آرگومان @captured_column_list مشخص نمایید. برای گرفتن اطلاعات metadata درباره ستونهایی که نمونه جمع آوری به حساب آمده اند، میتوانید از procedure با نام sys.sp_cdc_get_captured_columns استفاده نمایید.
اگر بعدا خواستید که رهگیری تغییرات داده را غیرفعال نمایید از sys.sp_cdc_disable_table استفاده نمایید. و اگر میخواهید ببینید که آیا یک جدول برای این کار فعال شده است، روی view با نام sys.tables ستون is_tracked_by_cdc را جستجو کنید.
برای بدست آوردن اطلاعات درباره پیکربندی رهگیری تغییر داده برای هر جدول فعال شده، sp با نام sys.sp_cdc_help_change_data_capture را مانند زیر استفاده نمایید :
EXECUTE sys.sp_cdc_help_change_data_capture;
استفاده ازاین تغییرات به صورت مستقیم از جداول تغییرمیسر نیست بلکه باید از توابع table-valued استفاده نمایید. داده برای تغییراتی که درون یک محدوده مشخص شده از شماره سریالهای log (LSNs) قرار گرفته باشند درخواست شده است. SQL SERVER 2008 همچنین توابع نگاشت sys.fn_cdc_map_time_to_lsn و sys.fn_cdc_map_sln_to_time را مهیا کرده که به شما کمک میکند تا یک محدوده date-time را به محدوده ای از LSNs و بالعکس تبدیل کنید.
توابع مجزا تمامی تغییرات داخل یک ورودی (cdc.fn_cdc_get_all_changes_<capture_instance>) و تغییرات شبکه که در مقابل سطرهای مجزا جای گرفته اند (cdc.fn_cdc_get_net_changes_<capture_instance>) را مهیا میکنند، البته اگر که توسط sys.sp_cdc_enable_table فعال شده باشند.
SQL Server 2008 همچنین یک sp با نام sys.sp_cdc_get_ddl_history دارد که به شما تاریخچه تغییرات DDL پیوسته با یک نمونه خاص رهگیری را میدهد.
SQL Server Reporting Service چیست؟
در سال ۲۰۰۳ یک add-on به SQL Server نسخه 2000 به عنوان یک سرویس گزارشگیری افزوده شد. این سرویس تا نسخه 2005 خیلی کاربردی نبود. از سال 2005 مایکروسافت سرمایه گذاری خوبی را در این زمینه که قبل از آن شرکتهایی در آن فعالیت میکردند آغاز کرد. اینگونه بود که از این مقطع زمانی به بعد مایکروسافت اقدامات خوبی را در این زمینه شروع کرد و آن را از حالت یک add-on خارج کرد.
نصب
زمانی که میخواهید نسخه های 2005 و 2008 از SQL Server را نصب کنید در یکی از مراحل باید Reporting Service را برای نصب انتخاب کنید. در ادامه باید تیک مربوط به Configure کردن خودکار Reporting Server را بزنید. اگر این گزینه را انتخاب نکنبد باید عملیات پیکربندی را به صورت دستی انجام دهید.
معماری SSRS
سه کامپوننت اصلی وجود دارد که با یکدیگر کار کرده و SSRS را به یک برنامه چندلایه واقعی تبدیل میکند. این سه عبارتند از :
1. Client
2. Report Server
3. SQL Server Report Databases
شکل زیر یک تفکیک مفهومی از این سه بخش و اجزا آن است.
در اینجا data source و SSRS databases ، ReportServer و ReportServerTempDB موجودیتهای مجزایی هستند. Data Source منبع و منشا اصلی داده ها برای پر کردن گزارشات است و Report Server Databases وظیفه ذخیره سازی اطلاعات مربوط به گزارشات را بر عهده دارند. هر دوی این دو میتوانند به صورت فیزیکی در یک SQL Server مشترک قرار گیرند، فزض کنید که data source یک پایگاه داده SQL Server است. Data source میتواند data provider های مختلف مثل SQL Server، Oracle، LDAP و یا Analysis Service را پشتیبانی نماید. این امر ممکن است که شما یک سرور واحد را هم برای SSRS Report Server Web Service و هم برای Report Server Database پیکربندی کنید. اگر چه این کار توصیه نمیشود مگر اینکه تعداد کاربر شما کم باشد.
نصب SSRS دو پایگاه داده برای شما میسازد :
ReportServer : این پایگاه داده اصلی است که تمامی اطلاعات را از فایل RDL منتشر شده (published) در حود ذخیره میکند. همچنین مواردی چون خصوصیات گزارش (مانند data sourceها)، پارامترها و فایلهای لاگ را در خود نگهداری میکند.
ReportServerTempDB : این پایگاه داده کپیهای cach شده گزارشات را برای بالا بردن کارایی به جهت استفاده همزمان کاربران در خود دارد. با این مکانیزم این اطمینان را داشته باشیم که حتی در صورت restart شدن سرور باز هم گزارشات در دسترس هستند.
SSRS Report Server
SSRS Report Server مهمترین نقش را در مدل SSRS ایفا میکند. در میانه کار کرده و برای هر درخواست از سمت client برای render کردن یک گزارش یا برای انجام یک درخواست مدیریتی مانند ساخت یک subscription مسیول است. شما متوانید یک report server را با توجه به عملیاتهای مختلف به چندین زیربخش تفکیک کنید :
· Programming interface
· Report processing
· Data processing
· Report rendering
· Report scheduling and delivery
SSRS Web Service Interface
رابط برنامه نویسی که به عنوان API های .NET Web Service و متدهای دسترسی URL ظاهر میشوند، تمامی درخواستهای ورودی از client ها را خواه درخواست گزارش باشد، خواه درخواست مدیریتی، اداره میکند.با توجه به نوع درخواست، رابط برنامه نویسی با دسترسی به پایگاه داده Report Server یا ارسال آن به کامپوننت دیگر برای پردازش اقدام میکند.
Report Processor
این کامپوننت مسیول تمامی درخواستهای گزارش است. مانند رابط برنامه نویسی، مستقیما به پایگاه داده ReportServer متصل میشود تا اطلاعات مربوط به تعریف گزارش را دریافت کند که این اطلاعات بعدا برای ترکیب با اطلاعات data source و ساخت گزارش به کار میرود.
Data Processing
SSRS چهار نوع الحاقیه برای اتصال به پایگاه داده حمایت میکند :
1. SQL Server
2. Oracle
3. OLE DB
4. ODBC
وقتی که این کامپوننت یک درخواست را Report Processor دریافت میکند، یک اتصال به data source میزند و آن را به query منبع ارسال میکند. داده بازگردانده شده و به Report Processor فرستاده میشود تا با تعریف گزارش ترکیب شود.
Report Rendering
این کامپوننت وظیفه render گزارش در فرمت مورد نظ را دارد. فرمتهای مورد حمایت عبارتند از :
· HTML
· HTML با استفاده از کامپوننتهای وبی Office
· Excel
· CSV
· MHTML
· TIFF
برنامه های Client
SSRS شامل چندی برنامه است که از آن استفاده میکنند. این برنامه ها و ابزارها، مدیریت Report Server ، پیاده سازی امنیت و وظایف render کردن گزارش را برای مهیا میکنند. این ابزارها مانند زیرند :
• Report Manager
این یک برنامه browser base همراه با SSRS است که محیطی گرافیکی را برای کاربران مهیا میکند که توسط آن میتوانند گزارشات را ببینند یا چاپ کنند، یا اینکه گزارشات را برای بخشهای مختلف مدیریت کنند.
• SQL Server Business Intelligence Development Studio (BIDS)
اگر شما روی سیستم خود Visual Studio نداشته باشید و SSRS را نصب کنید، خود SQL Server برای شما Visual Studio IDE را برای شما نصب میکند تا شما بتوانید به راحتی به طراحی و تست گزارشات خود بپردازید. این IDE را با نام SQL Server Business Intelligence Development Studio نصب کرده و با انتخاب آن میتوانید به راحتی به آن دست یابید. البته باید بگویم برای ساخت گزارشات وجود این IDE الزامی نیست.
• ابزارهای Command-line
شما میتوانید از چندین ابزار Command-line مانند rs ، rsconfig و RSKeyMgmt برای پیکربندی و مدیریت محیط SSRS استفاده نمایید.
• Custom Clients
• Reporting Services Configuration Manager
ابزاری برای پیکربندی به صورت گرافیکیست که از نسخه ۲۰۰۵ به بعد به وجود آمد.
انواع گزارشات و ساختار آنها
به طور کلی دو نوع گزارش در RS موجود است:
1. Client Report
2. Server Report
در Visual Studio 2005 به بعد کنترل جدیدی به آن افزوده شد با نام MicrosoftReportViewer که مخصوص نمایش گزارشات بود. گزارشات فایلهایی با پسوند rdl برای گزارش remote و rdlc برای گزارش clientهستند که میتوانید آن را به پروژه خود بیفزایید. پس از افزودن آن چندین ابزار به شما برای ساخت گزارش داده خواهد شد. شما میتوانید با این ابزارها گزارش مورد نظر خود را بسازید.
RDL یک مدل بر مبنای XML است که هر عنصر گزارش مانند قالب بندی، اطلاعات dataset، grouping و sorting، پارامترها و فیلترها را تعریف میکند. همانطور که شما آیتمی را اضافه میکنید، این ساختار دچار تغییرات میشود.
در IDE این ساختار از شما مخفیست ولی در صورت نیاز به تغییر کلی در گزارشات ، میتوانید از find and replace در این ساختار به صورت مستقیم استفاده نمایید. در VS 2005 و بعد از آن با زدن دکمه F7 در زمان کار با گزارش میتوانید این ساختار را ببینید. به تکه ای از این ساختار در یک گزارش توجه کنید :
در نسخه ۲۰۰۵ نه ابزار برای ساخت گزارش وجود داشت که در نسخه ۲۰۰۸ کمی تغییر کردند. لیست این ابزار در تصویر زیر آمده است:
هر گزارش شامل سه بخش ۱- Header ۲- Body ۳- Footer است که در هرکدام از برخی از این ابزار میتوان استفاده کرد. شما میتوانید از امکانات زیر در گزارشات استفاده نمایید که من فقط آنها را نام میبرم :
• نوشتن expression برای اکثر اجزای گزارش مثلا امکان visibility و ...
• استفاده از توابع از قبل آماده در نوشتن expression
• نوشتن expression با زبان VB (مثلا مد تغییر تاریخ)
• استفاده از پارامترها برای نوشتن query پویا
• استفاده از فایلهای اسمبلی (dll) : مثلا نوشتن تابعی با زبان c# و استفاده ازآن در گزارش
• کنترل قوی روی اجزا گزارش
• امکان قرار دادن sorting و filter بر اساس عناصر محتلف گزارش
• امکان استفاده از گروه بندی های داده ای تودرتو
• امکان استفاده از stored procedure و text query به عنوان dataset
• امکان ساخت گزارشات تعاملی مثلا استفاده از زیرگزارشات
• استفاده از چندین dataset به عنوان تامین کننده داده
• امکان دادن به کاربر برای ساختن گزارشات دلخواه با استفاده از report model
تفاوتهای موجود بین دو نوع گزارش
ساختار گزارشات rdl و rdlc کاملا شبیه هم است ولی در نحوه اجرا و تامین داده با هم کم متفاوت هستند. تفاوتهای موجود را به ترتیب در زیر بیان میکنم :
۱- گزارشات server باید در یک پروژه از نوع report server project و در محیط BIDS افزوده شوند در صورتیکه گزارشات client در یک پروژه معمولی .NET اضافه میشوند.
۲- گزارشات server از یک query مستقیم که به صورت stored procedure یا text است به عنوان تامین کننده داده استفاده میکنند در حالی که گزارشات client باید در کد مقداردهی شوند. مثلا داده مربوط به آنها میتواند توسط یک dataset یا یک xml data source تامین شود. در ضمن برای انجام این کار احتیاج به نوشتن مقداری کد مثلا در c# است.
۳-برای نمایش داده شدن گزارشات server کنترل MicrosoftReportViewer صرفا وظیفه نمایش گزارش در انتهای عملیات را دارد و تمام عملیات برای تهیه گزارش و render آن در report server انجام میشود. این در حالیست که برای نمایش گزارش client کنترل MicrosoftReportViewer نقش اساسی را انجام میدهد و عملیات render شدن گزارش در این کنترل انجام میشود.
۴-گزارشات client به نصب SSRS نیازی ندارند در حالیکه گزارشات server بدون این کار قابل دسترسی نیستند.
۵- کنترل MicrosoftReportViewer در گزارشات client و در نسخه ۲۰۰۵ دارای دکمه چاپ نبودند ولی گزارشات server این مشکل را نداشتند. البته لازم به ذکر است این مشکل در نسخه ۲۰۰۸ حل شده است.
۶-گزارشات client نیازی به publish روی report server ندارند در حالیکه گزارشات server برای اجرا شدن حتما باید publish شوند.
نکته : من این نوشته را بر اساس نسخه ۲۰۰۵ نوشتم و باید بیان کنم که نسخه ۲۰۰۸ بهبودهای خوبی داشته است. البته اصول تغییری نکرده است. در نوشته های آینده سعی میکنم مثالهایی را بر اساس نسخه ۲۰۰۸ بنویسم.
وابستگی های اشیا در SQL Server 2008
SQL Server 2008 چندین آبجکت ارائه داده که اکتشاف وابستگیهای آبجکتها را به صورت قابل اطمینانی فراهم میکند، و جایگزین view قدیمی وغیرقابل اطمینان sys.sql_dependencies وهمچنین stored procedure قدیمی sp_depends شده است. آبجکتهای جدید اطلاعاتی درباره وابستگیها فراهم کرده که در یک کد ثابت ظاهر میشود، آنها شامل هر دوی آبجکتهای schema-bound و non-schema-bound و نیز cross-database و حتی وابستگیهای cross-server میشوند. آبجکتهای جدید وابستگیهایی را که در کد SQL یا کد CLR به وجود بیایند را پوشش نمیدهند.
برای اینکه ببینید چگونه اطلاعات مربوط به وابستگیها را جستجو کنید، ابتدا کد زیر را اجرا کنید تا چند آبجکت در پایگاه داده tempdb برای تست ساخته شود :
USE tempdb;
IF OBJECT_ID('dbo.Proc1', 'P') IS NOT NULL DROP PROC dbo.Proc1;
IF OBJECT_ID('dbo.V1', 'V') IS NOT NULL DROP VIEW dbo.V1;
IF OBJECT_ID('dbo.V2', 'V') IS NOT NULL DROP VIEW dbo.V2;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.T2', 'U') IS NOT NULL DROP TABLE dbo.T2;
GO
CREATE PROC dbo.Proc1
AS
SELECT * FROM dbo.T1;
EXEC('SELECT * FROM dbo.T2');
GO
CREATE TABLE dbo.T1(col1 INT);
CREATE TABLE dbo.T2(col2 INT);
GO
CREATE VIEW dbo.V1
AS
SELECT col1 FROM dbo.T1;
GO
CREATE VIEW dbo.V2
AS
SELECT col1 FROM dbo.T1;
GO
SQL Server 2008 سه آبجکت جدید را معرفی کرده که اطلاعاتی درباره وابستگی آبجکت به ما میدهد : catalog view با نام sys.sql_expression_dependencies ، و توابع مدیریت پویا (DMF) با نامهای sys.dm_sql_referenced_entities و sys.dm_sql_referencing_entities .
View اول وابستگیهای آبجکت را توسط نام فراهم میکند. این جایگزین view قدیمیتر با نام sys.sql_dependencies شده است. Query زیر در مقابل sys.sql_expression_dependencies تمام وابستگیها در پایگاه داده کنونی را نشان میدهد:
SELECT
OBJECT_SCHEMA_NAME(referencing_id) AS srcschema,
OBJECT_NAME(referencing_id) AS srcname,
referencing_minor_id AS srcminorid,
referenced_schema_name AS tgtschema,
referenced_entity_name AS tgtname,
referenced_minor_id AS tgtminorid
FROM sys.sql_expression_dependencies;
Query بالا خروجی زیر را تولید میکند :
srcschema srcname srcminorid tgtschema tgtname tgtminorid
--------- ------- ---------- --------- ------- ----------
dbo Proc1 0 dbo T1 0
dbo V1 0 dbo T1 0
dbo V2 0 dbo T1 0
توجه کنید که query تمام وابستگیها در کد ثابت را شناسایی میکند نه وابستگیها در کد پویا را (ارتباط از dbo.T2 به dbo.Proc1).
DMF با نام sys.dm_sql_referenced_entities تمام entity هایی که entity ورودی به آن مراجعه میکند را مهیا میکند – به عبارت دیگر، تمام entity هایی که entity ورودی به آن وابسته است. به عنوان مثال، کد زیر تمام entity هایی که dbo.Proc1 به آن وابسته است را برمیگرداند :
SELECT
referenced_schema_name AS objschema,
referenced_entity_name AS objname,
referenced_minor_name AS minorname,
referenced_class_desc AS class
FROM sys.dm_sql_referenced_entities('dbo.Proc1', 'OBJECT');
این کد خروجی زیر را برمیگرداند :
objschema objname minorname class
--------- --------- --------- ----------------
dbo T1 NULL OBJECT_OR_COLUMN
dbo T1 col1 OBJECT_OR_COLUMN
خروجی نشان میدهد که dbo.Proc1 به جدول dbo.T1 و ستون dbo.T1.col1 وابسته است. مجددا بیان میکنم که وابستگیهایی که در کد پویا نشان داده میشود، شناسایی نمیشود.
DMF با نام sys.dm_sql_referencing_entities تمام entity هایی که به entity ورودی ارجاع داده اند را فراهم میکند – به عبارت دیگر، تمام entity هایی که به entity ورودی وابسته هستند. برای مثال،کد زیر تمام entity هایی را که dbo.T1 وابسته هستند را بر میگرداند:
SELECT
referencing_schema_name AS objschema,
referencing_entity_name AS objname,
referencing_class_desc AS class
FROM sys.dm_sql_referencing_entities('dbo.T1', 'OBJECT');
این کد خروجی زیر را بازمیگرداند :
objschema objname class
--------- --------- ----------------
dbo Proc1 OBJECT_OR_COLUMN
dbo V1 OBJECT_OR_COLUMN
dbo V2 OBJECT_OR_COLUMN
خروجی نشان میدهد که dbo.Proc1 و dbo.V1 و dbo.V2 به dbo.T1 وابسته اند.
asie ahmadi- کاربر عادی
- تعداد پستها : 16
تاريخ التسجيل : 2009-11-08
صفحه 1 از 1
صلاحيات هذا المنتدى:
شما نمي توانيد در اين بخش به موضوعها پاسخ دهيد