آیا میدانید اس کیو الی !؟!؟

[hamed_Arfaee]

تاریخ فردا

کد:

CREATE FUNCTION [dbo].[TomorrowDate]
(
    @DateA VARCHAR(10)
)
RETURNS VARCHAR(10)
AS

BEGIN
    DECLARE @lnYear    INT
    DECLARE @lnMonth   INT
    DECLARE @lnDay     INT
    DECLARE @Temp      INT
    DECLARE @lnYearS   NVARCHAR(4)
    DECLARE @lnMonthS  NVARCHAR(2)
    DECLARE @lnDayS    NVARCHAR(2)
    
    SET @lnYear = CAST(SUBSTRING(@DateA, 1, 4) AS INT)
    SET @lnMonth = CAST(SUBSTRING(@DateA, 6, 2) AS INT)
    SET @lnDay = CAST(SUBSTRING(@DateA, 9, 2) AS INT) + 1
    
    IF (@lnYear % 4) = 3
    BEGIN
        SET @Temp = 1
    END
    ELSE
    BEGIN
        SET @Temp = 0
    END
    -------------------------------
    
    IF (@lnMonth = 12)
       AND (@lnDay > 29 + @Temp)
    BEGIN
        SET @lnDay = 1
        SET @lnMonth = 1
        SET @lnYear = @lnYear + 1
    END
    ELSE
    BEGIN
        IF ((@lnMonth >= 7 AND @lnMonth <= 11) AND (@lnDay > 30))
           OR ((1 <= @lnMonth AND @lnMonth <= 6) AND (@lnDay > 31))
        BEGIN
            SET @lnDay = 1
            SET @lnMonth = @lnMonth + 1
        END
    END
    
    SET @lnYearS = CAST(@lnYear AS NVARCHAR(4))
    IF (@lnMonth < 10)
        SET @lnMonthS = '0' + CAST(@lnMonth AS NVARCHAR(2))
    ELSE
        SET @lnMonthS = CAST(@lnMonth AS NVARCHAR(2))
    
    IF (@lnDay < 10)
        SET @lnDayS = '0' + CAST(@lnDay AS NVARCHAR(2))
    ELSE
        SET @lnDayS = CAST(@lnDay AS NVARCHAR(2))

    RETURN (@lnYearS + '/' + @lnMonthS + '/' + @lnDayS)
END

مثال :

کد:

SELECT dbo.TomorrowDate('1391/12/30')

نتیجه : 1392/01/01

[hamed_Arfaee]

جایگذاری ردیفی
تقریبا شبیه Replace هست ولی با این تفاوت که نمی دونیم مقدار جدید قراره جایگزین چه مقداری بشه .
این تابع 4 تا پارامتر داره

1. رشته ورودی (مبدا)
   
2. شماره ردیف کاراکتر شروع
   
3. شماره ردیف تعداد بعد از کاراکتر شروع
   
4. رشته جایگزین
   

کد:

STUFF ( character_expression , start , length , replaceWith_expression )

مثال

کد:

SELECT STUFF('hamed_Arfaee',6,1,'_IranVig_')

نتیجه : hamed_IranVig_Arfaee

نکته : عدد شروع باید بزرگتر از 0 و کوچکتر یا مساوی طول رشته ورودی باشه

[hamed_Arfaee]

ISNULL یا COALESCE
اول هر دوتا رو معرفی میکنم :

کد:

ISNULL ( check_expression , replacement_value )

دو تا پارامتر داره اگه اولی NULL باشه پارامتر دوم رو پس میده و در غیر این صورت همون پارامتر اول رو به خروجی میفرسته

و تقریبا میشه گفت ساختارش به این شکله :

کد:

DECLARE @Parameter1
DECLARE @Parameter2
IF (@Parameter1 IS NULL) SET @Parameter1=@Parameter2
RETURN @Parameter1

مثال :

کد:

DECLARE @var NVARCHAR(10)
SELECT ISNULL(@var,'its null')

نتیجه : its null

و

کد:

COALESCE ( expression [ ,...n ] )

این تابع n تا پارامتر داره از چپ به راست اولین که NULL نبود رو به خروجی میفرسته

و ساختاری به این شکل داره :

کد:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END

مثال :

کد:

DECLARE @var1 NVARCHAR(30)
DECLARE @var2 NVARCHAR(30)
DECLARE @var3 NVARCHAR(30)
DECLARE @var4 NVARCHAR(30)

SET @var3='var3 is my result'

SELECT COALESCE(@var1,@var2,@var3,@var4)

نتیجه : var3 is my result

و حالا اصل مطلب
کدوم رو بهتره که ازش استفاده کنیم؟

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

کد:

DECLARE @var1 NVARCHAR(6)
DECLARE @var2 NVARCHAR(30)
SET @var2='this is my result'

SELECT ISNULL(@var1,@var2)

نتیجه میشه : this i
که فقط 6 کاراکتر اول به خروجی ارسال میشه .

به نظر من Coalesce مطمعن تر از IsNull هست .

[hamed_Arfaee]

کاربرد کلید ALT در ویرایشگر متن SQL Server Management


وقتی کلید ALT رو نگه داریم میتونییم متنی رو که انتخاب میکنیم بصورت یک کادر مسطیلی باشه به این شکل :
   

[hamed_Arfaee]

تکرار عبارت و تولید فاصله

کد:

REPLICATE ( string_expression ,integer_expression )

کار این تابع تکرار یک عبارت به تعداد مشخص هست

مثال :

کد:

SELECT REPLICATE ( 'hamed,' ,5 )

خروجی : hamed,hamed,hamed,hamed,hamed,

و تابع دوم

کد:

SPACE ( integer_expression )

کار این تابع تولید کاراکتر فاصله به تعداد مشخص هست

مثال :

کد:

SELECT 'hamed'+space (5)+'arfaee'

خروجی :hamed-----arfaee
نکته : همونطور که میبینم میشه تابع تولید فاصله رو با تابع تکرار عبارت پیاده سازی کرد

[hamed_Arfaee]

UNION یا UNION ALL
وقتی میخواهییم نتیجه چند کوئری رو با هم ادغام کنیم از این کلید استفاده میکنیم
یعنی بجای اینکه به ازای هر سلکت یک جدول جدا داشته باشیم ، نتایج رو کلا تو یک جدول بدست میاریم
مثلا :

کد:

select 7
UNION
select 2
UNION
select 3

که خروجی میشه :
2
3
7
حالا تفاوت این دو اجتماع چی هست ؟!

وقتی از دستور UNION استفاده میکنیم همه نتایج در جدول موقت ثبت میشن و نتیجه ردیف های غیر تکراری و مرتب شده خواهد بود
مثال :

کد:

select 7,1
UNION
select 2,2
UNION
select 3,2
UNION
select 2,2

خروجی :
2------2
3------2
7------1

ولی دستور UNION ALL هیچ تغییری را اعمال نمیکند و جدول خروجی براساس جداول وروردی و بدون دستکاری خواهد بود
مثال :

کد:

select 7,3
UNION ALL
select 2,5
UNION ALL
select 3,3
UNION ALL
select 2,5

خروجی :
7------3
2------5
3------3
2------5

نکته :اگر حاصل هر یک بازجستها(سلکتها) منحصر به فرد از هم باشن (یعنی ردیف تکراری نداشته باشیم)نتایج این دو دستور تفاوتی چندانی نخواهند داشت (بجز مرتب شدن)

[hamed_Arfaee]

بررسی دستور Truncate Table و Delete

بررسی دستور Delete :
با اجرای دستور Delete به راحتی می‌توانیم تعدادی از (یا همه )رکوردهای یک جدول را حذف کنیم
مثال :

کد:

DELETE FROM table_name
WHERE some_column=some_value

یا

کد:

DELETE FROM Countries
WHERE Country=’UK’

اما نکته مهمی که دستور Delete دارد این است کار این دستور به شکل Transactional می‌باشد. یعنی یا کلیه رکوردهایی که Country آنها UK است حذف می‌شود و یا هیچکدام از آنها. پس اگر شما 200000 رکورد داشته باشید که در این شرط صدق کند اگر وسط کار Delete (البته اگر عملیات حذف طولانی باشد) منصرف شوید می‌توانید با Cancel کردن این دستور عملیات Rollback Transaction را به خودکار توسط SQL Server داشته باشید. در صورتیکه عملیات Cancel را انجام دهید SQL Server از Log File برای بازگرداندن مقادیر حذف شده استفاده خواهد کرد.

نکته دیگری که دستور Delete دارد این است که این دستور Log کلیه رکوردهایی را که قرار است حذف کند در Log File می‌نویسد. این Log شامل اصل رکورد، تاریخ و زمان حذف، نام کاربر و... می‌باشد. شاید الان متوجه شوید که دستور Delete چرا در برخی از مواقع که قرار است حجم زیادی از اطلاعات را حذف نماید به کٌندی این کار را انجام می‌دهد. (چون باید Log رکوردهای حذف شده در Log File نوشته شود.)

بررسی دستور Truncate Table:

Truncate در لغت به معنی بریدن و کوتاه کردن می‌باشد. با استفاده دستور Truncate Table می‌توانید محتوای کلیه رکوردهای موجود در یک جدول را در کسری از ثانیه حذف کنید.
نکته مهمی که باید درباره دستور Truncate Table بدانید این است که تاثیر استفاده از این دستور بر روی کلیه رکوردها بوده و به هیچ عنوان نمی‌توان برای این دستور شرط (Where Clause) اعمال نمود.
مثال

کد:

TRUNCATE TABLE  table_name

اما در مورد دستور Truncate Table و Delete باید به نکات زیر توجه کنید.
1- دستور Truncate Table فاقد قسمت شرط (Where Clause) می‌باشد در صورتیکه دستور Delete دارای قسمت شرط (Where Clause) است
2- دستور Truncate Table در Log File آدرس Page و مقدار فضای آزاد شده (کمترین میزان Log) را می‌نویسد اما در صورتیکه دستور Delete در Log هر رکوردی را که
قرار است حذف شود را در Log File ثبت می‌نماید.
3- دستور Truncate Table باعث می‌شود که Pageهای متعلق به جدول deallocate شوند. deallocate شدن Pageها این معنی را می‌دهد که رکوردهای موجود در جدول واقعاً حذف نشوند بلکه Extentهای مربوط به آن Pageها علامت Empty خورده تا دفعات بعد مورد استفاده قرار گیرند اما دستور Delete به طور فیزیکی محتوای Pageها مربوط به جدول را خالی می‌کند.
نکته : پس از Truncate شدن رکوردها امکان بازگشت آنها وجود ندارد.
4- در صورتیکه جدول شما دارای ایندکس باشد. دستور Truncate Table آزاد کردن فضای مربوط به ایندکس را در یک مرحله انجام می‌دهد(مطابق بند 3) همچنین Log مربوط به این حذف به شکل حداقل (مطابق بند 2) در Log File ثبت می‌شود. اما دستور Delete هر رکوردی را که از ایندکس حذف می‌کند در Log File ثبت می‌کند.
5- Trigger مربوط به دستور Delete به هیچ عنوان هنگام اجرای دستور Truncate Table فعال نمی‌شود. در صورتیکه با اجرای دستور Delete تریگر آن فعال خواهد شد.
6- در صورتیکه جدول شما دارایRelation) Reference) باشد امکان استفاده از دستور Truncate Table وجود ندارد. لازم به ذکر است حتی اگر Reference را غیر فعال کنید باز هم امکان استفاده از دستور Truncate Table وجود نخواهد داشت و تلاش برای اجرای دستور Truncate Table باعث نمایش خطای زیر خواهد شد.

کد:

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint

در صورتیکه در دستور Delete امکان حذف رکوردها به ازای جداولی که دارای Relation هستند وجود دارد. فقط باید به این نکته توجه کنید که ترتیب حذف رکوردها از جداول Master و Detail را رعایت کنید.

7- دستور Truncate Table مقدار Identity را Reset کرده و آن را به Seed (هسته/مقدار اولیه) بر می‌گرداند. در صورتیکه دستور Delete تاثیری بر روی مقدار Identity ندارد
8- دستور Truncate Table تنها توسط کاربرانی قابل اجرا است که نقش DB_Owner و یا SysAdmin را داشته باشند در صورتیکه دستور Delete توسط هر کاربری که مجوز Delete بر روی جدول را داشته باشد قابل اجرا می‌باشد.
9- پس از اجرای دستور Truncate Table تعداد رکوردهای حذف شده نمایش داده نمی‌شود. در صورتیکه هنگام اجرای دستور Delete تعداد رکوردهای حذف شده نمایش داده می‌شود.

منبع

dotnettips.info

[hamed_Arfaee]

بازسازی msdb تخریب شده

   

کد:

Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)

Msdb از نوع دیتابیس‌های سیستمی است و نمی‌شود مطابق روال متداول دیتابیس‌های SUSPECT شده آن‌را بازیابی کرد .
در ابتدای کار دیتابیس در حالت اورژانسی قرار می‌گیرد. بعد وضعیت و میزان تخریب نمایش داده شده، سپس تک کاربره می‌شود. در ادامه به اس کیوال سرور اجازه داده می‌شود که دیتابیس را با هر وضعی (حتی به قیمت از دست رفتن تعدادی رکورد) ترمیم کند و در آخر دیتابیس مجددا به حالت چند کاربره بازگشت داده می‌شود.
این روشی است که سال قبل با قطعی‌های مکرر برق زیاد کاربرد داشت.

اما دیتابیس سیستمی msdb را نمی‌شود در حالت اورژانسی قرار داد؛ بنابراین باید به دنبال راه چاره‌ی دیگری بود. پس از مدتی جستجو در وبلاگ‌های msdn ، راه حل زیر یافت شد و کاملا عملی است (تست شده!) :

روش زیر در مورد اس کیوال سرور 2008 ، 2005 و حتی 2000 نیز قابل استفاده است.
ابتدا خونسردی خودتان را حفظ کنید! الان فقط دیگر با management studio نمی‌توانید دیتابیس‌ها را مرور کنید و همچنین تمام job های تعریف شده شما نابود شده‌اند! اما سرور به کار عادی خودش می‌تواند ادامه دهد. سپس :
الف) تمام سرویس‌های مربوط به اس کیوال سرور را stop کنید. به کنسول سرویس‌ها مراجعه کرده و هر آنچه که در نام آن sql را مشاهده می‌کنید، stop کنید.
ب) با استفاده از خط فرمان، ابتدا به مسیر زیر وارد شوید:

کد:

cd "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\"

و سپس دستور زیر را اجرا نمائید:

کد:

start sqlservr.exe -c -m -T3608

به این ترتیب اس کیوال سرور در یک حالت حداقل که بتوان دیتابیس msdb تخریب شده را detach کرد راه اندازی می‌شود. (پرچم 3608 مجوز detach کردن این دیتابیس را می‌دهد)
ج) management studio را اجرا کنید. زمانیکه پنجره کانکت ظاهر می‌شود آن‌را کنسل کرده و در نوار ابزار بالای صفحه روی دکمه new query کیک کنید (چون حالت راه اندازی سرور در حالت تک کاربره است نمی‌خواهیم اتصال دیگری برقرار شود و در کار اخلال کند). با کلیک بر روی new query پنجره connect to server ظاهر می‌شود. در همین پنجره بر روی دکمه options کلیک کرده در برگه connection properties در قسمت connect to database نام master را وارد نمود و اکنون بر روی دکمه connect کلیک نمائید.
ج) سپس دستور زیر را وارد کنید تا دیتابیس msdb را بتوان detach کرد.

کد:

Use master;
sp_detach_db 'msdb'

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

کد:

Cannot detach an opened database when the server is in minimally configured mode

اگر به این خطا برخوردید، یکبار دیگر از صفر شروع کنید. تمام سرویس‌های مرتبط با sql را استاپ کنید (حتی در صورت نیاز کارت شبکه سرور را نیز غیرفعال کنید). و از مرحله الف مجددا شروع نمائید تا حتما حالت تک کاربره‌ی اتصال برقرار شود. (همچنین پنجره‌ی کوئری جدیدی را نیز باز نکنید چون در این حالت فقط و فقط یک اتصال مجاز است)

تا اینجا موفق شدیدم که دیتابیس msdb را detach کنیم. اکنون به پوشه دیتابیس‌ها مراجعه کرده و mdf و ldf این دیتابیس تخریب شده را rename کنید (به هر اسمی که مایل بودید).
د) اکنون نوبت بازسازی مجدد این دیتابیس است.
محتویات فایل instmsdb.sql را که در مسیر

کد:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\install

قرار دارد، در پنجره‌ی کوئری تک کاربره‌ای که در مرحله قبل بازکرده‌ایم، copy/paste کرده و دکمه F5 را فشار دهید. پس از مدتی دیتابیس msdb باز سازی شده و مشکل برطرف می‌شود.
ه) اکنون سرور را stop و start کنید یا کلا کامپیوتر سرور را restart‌ کنید تا تمامی سرویس‌های stop شده راه اندازی مجدد شوند.

منبع

dotnettips.info

[hamed_Arfaee]

برای تکرار عملیات داخل اس کیو ال سرور می تونیم از خود کلمه GO استفاده کنیم به این صورت :

کد:

select 'IranLED'

GO 20

اجرای دستور بالا باعث میشه کلمه ایران ال ای دی 20 بار تکرار بشه .

[hamed_Arfaee]

سلام
استفاده بیشتر از Group By
کلمات کلیدی که از نسخه 2008 به SQL Server اضافه شده کمک میکنن تا با کم کردن تعداد کوئری ها اطلاعات بیشتری بدست بیاریم .

نمونه ای از این کلید ها :Rollup, Cube هست .

فرض کنیم جدولی با اطلاعات زیر داریم :
ردیف------کالا---------جنس--------تعداد--

1--------میز------------فلزی---------2---
2-------میز-------------چوبی--------3---
3-------صندلی--------فلزی----------2---
4-------نیمکت---------چوبی--------4---

حالا اگر کوئری به این شکل بنویسیم :

کد:

select item,sum(cnt) from tbl1 group by item

حاصل میشه تعداد موجودی هر یک از کالا ها- حالا میخواهیم بدونیم تعداد کل موجودی چقدر هست .

پس اینطوری مینویسیم :

کد:

select item,sum(cnt) from tbl1 group by item with rollup

و حاصل این کوئری میشه :

کالا-------تعداد--

میز----------5---
صندلی-----2---
نیمکت------4---
نال--------11---

یعنی یک ردیف دیگه اضافه میشه حاصل تابع تجمیع گروه بندی براش مجموع کل هست و مقدار فیلد بدون تابع میشه NULL

حالا میخواهیم با دو تا ستون گروه بندی کنیم :

کد:

select item,kind,sum(cnt) from tbl1 group by item,kind with rollup

خروجی این کوئری میشه :

کالا---------جنس--------تعداد--

میز----------فلزی---------2-----
میز----------چوبی--------3-----
میز----------نال-----------5-----
صندلی--------فلزی------2-----
صندلی-------نال----------2-----
نیمکت---------چوبی------4----
نیمکت---------نال---------4----
نال-------------نال--------11----


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

حالا میخواهیم مجموع هر دو ستون رو داشته باشیم یعنی از هر کالا کلا چند تا و از هر نوع چند تا .

کد:

select item,kind,sum(cnt) from tbl1 group by item,kind with cube

که خروجی میشه :
کالا---------جنس--------تعداد--

میز----------فلزی---------2-----
میز----------چوبی--------3-----
میز----------نال-----------5-----
صندلی--------فلزی------2-----
صندلی-------نال----------2-----
نیمکت---------چوبی------4----
نیمکت---------نال---------4----
نال-------------نال--------11----
نال------------فلزی--------4---
نال------------چوبی-------7---

حالا از کجا بدونیم کدوم ردیف اطلاعات ما هست و کدوم ردیف مجموعی هست که اضافه شده ؟؟
برای این منظور از تابع Grouping استفاده میکنیم که خروجی این تابع 0و1 هست بامعنی هست و نیست

مثال :

کد:

select item,kind,sum(cnt),grouping(item),grouping(kind) from tbl1 group by item,kind with cube

خروجی میشه :
کالا---------جنس--------تعداد-------کالاگروه شده--------جنس گروه شده---

میز----------فلزی---------2------------------0---------------------0---------------
میز----------چوبی--------3------------------0---------------------0---------------
میز----------نال-----------5------------------0---------------------1----------------
صندلی--------فلزی------2------------------0---------------------0----------------
صندلی-------نال----------2------------------0---------------------1---------------
نیمکت---------چوبی------4-----------------0---------------------0----------------
نیمکت---------نال---------4-----------------0---------------------1----------------
نال-------------نال--------11-----------------1---------------------1----------------
نال------------فلزی--------4----------------1---------------------0-----------------
نال------------چوبی-------7----------------1---------------------0-----------------
اینجوری معلوم میشه که اون خروجی نال توی دیتا هست یا تولید شده .


یه مثال کلی :

کد:

select case when (grouping(item)=1)then 'all' else item end as item
,case when (grouping(kind)=1)then 'all' else kind end as kind,sum(cnt)
from tbl1 group by item,kind with cube

خروجی :

کالا---------جنس--------تعداد--------

میز----------فلزی---------2-----------
میز----------چوبی--------3-----------
میز----------مجموع-------5-----------
صندلی------فلزی--------2------------
صندلی-----مجموع-------2------------
نیمکت------چوبی---------4-----------
نیمکت------مجموع--------4-----------
مجموع------مجموع-------11----------
مجموع-------فلزی--------4-----------
مجموع-------چوبی-------7-----------

[hamed_Arfaee]

گرفتن خروجی XML از SQL Server
جدولی داریم با این ساختار و اطلاعات:

کد:

CREATE TABLE [dbo].[Names](
    [Id] [int] NULL,
    [fName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Names]([Id], [fName])
SELECT 1, N'Ali' UNION ALL
SELECT 2, N'Hamed' UNION ALL
SELECT 3, N'Naser' UNION ALL
SELECT 4, N'Zahra' UNION ALL
SELECT 5, N'Sara' UNION ALL
SELECT 6, N'Mobin'

واکشی:

کد:

SELECT  [Id] ,[fName]  FROM [Names] tblNames FOR XML AUTO  

حاصل:

کد:

<tblNames Id="1" fName="Ali" />
<tblNames Id="2" fName="Hamed" />
<tblNames Id="3" fName="Naser" />
<tblNames Id="4" fName="Zahra" />
<tblNames Id="5" fName="Sara" />
<tblNames Id="6" fName="Mobin" />

از نگارش 2005 به بعد، Select for XML علاوه بر خروجی متنی XML، توانایی تولید خروجی از نوع XML را نیز یافته است.
با چهار حالت:

کد:

RAW
AUTO
EXPLICIT
PATH

مثال کاربردی؛ ادغام چند ردیف در یک ردیف:

کد:

SELECT  ','+[fName]  FROM [Names] tblNames FOR XML PATH('')

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

کد:

,Ali,Hamed,Naser,Zahra,Sara,Mobin

و برای حذف ویرگول شروع میتوان از تابع STUFF استفاده کرد:

کد:

SELECT STUFF((SELECT ','+[fName]  FROM [Names] tblNames FOR XML PATH('')),1,1,'')

اطلاعات بیشتر

http://www.dotnettips.info/courses/topic...95f4d684c1
https://msdn.microsoft.com/en-us/library/ms178107.aspx