نکته های مفید و حرفه ای در اس کیو ال سرور SQL Server

[ali]

از Query یا پرس و جوی زیر برای بدست آوردن تعداد CPU های فیزیکی و مجازی موجود در Server (یا همان کامپیوتری که SQL Server در آن نصب شده) استفاده می شود:

کد:

SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS Hyperthread_Ratio,
cpu_count/hyperthread_ratio AS Physical_CPU_Count,
physical_memory_in_bytes/1048576 AS Physical_Memory_in_MB,
sqlserver_start_time, affinity_type_desc
FROM sys.dm_os_sys_info

البته اگر از * استفاده بشود یک سری اطلاعات بیشتر نیز در اختیار شما گذارده می شود.

[ali]

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

کد:

select  'create table [' + so.name + '] (' + o.list + ')' +
    CASE WHEN tc.Constraint_Name IS NULL THEN ''
    ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')'
END
from    sysobjects so
cross apply
    (SELECT
        '  ['+column_name+'] ' +
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
            else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
        case when exists (
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1
        ) then
        'IDENTITY(' +
        cast(ident_seed(so.name) as varchar) + ',' +
        cast(ident_incr(so.name) as varchar) + ')'
        else ''
        end + ' ' +
         (case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
          case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '

     from information_schema.columns where table_name = so.name
     order by ordinal_position
    FOR XML PATH('')) o (list)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')

و ضمنا این هم اکسریپت اس کیو ال برای استخراج اسکریپت سایر آبجکتهای موجود در پایگاه داده. البته نمی دونم اینکریپت شده ها رو هم نمایش میده یا نه. هنوز تست نکردم.

کد:

select object_definition(object_id)
from sys.objects
where type_desc in ('SQL_SCALAR_FUNCTION',
'SQL_STORED_PROCEDURE',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_TRIGGER',
'VIEW')-- اسکریپت آبجکتهایی به غیر جدولها

[ali]

برای ایجاد عدد تصادفی از فانکشن RAND() در SQL Server استفاده می شود. ولی از این فانکشن به تنهایی برای تولید عدد تصادفی بین دو عدد صحیح نمی توان استفاده نمود. ضمنا از تابع RAND() نمتوان در داخل یک فانکشن دیگر استفاده کرد. در صورت استفاده با خطای زیر مواجه می شوید:

کد:

Invalid use of a side-effecting operator 'rand' within a function.

برای جلوگیری از این خطا در ابتدا یک Query یا همان View در SQL Server ایجاد می نماییم:

کد:

CREATE VIEW [dbo].[XtblGet_RAND]
AS
    SELECT RAND() AS MyRAND
GO

سپس با استفاده از این View یک فانکشن می نویسیم که عدد تصادفی بین دو عدد صحیح را ایجاد نماید:

کد:

CREATE FUNCTION [dbo].[UDF_GetRandomNumberInt](@LowerNumber INT , @UpperNumber INT)
RETURNS INT
AS
BEGIN
    RETURN ROUND(((@UpperNumber - @LowerNumber -1) * (SELECT MyRAND FROM XtblGet_RAND) + @LowerNumber), 0)
END

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

کد:

SELECT dbo.UDF_GetRandomNumberInt(10 , 1500) AS RndNumber

[ali]

کد:

DECLARE @BookRowID int
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FAST_FORWARD
FOR
SELECT BookRowID FROM BooksEnd --where BookRowID > 8779
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @BookRowID
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE BooksEnd SET BookNotes = N'سایر توضیحات'  WHERE BookRowID = @BookRowID
    FETCH NEXT FROM @MyCursor INTO @BookRowID
END
CLOSE @MyCursor
DEALLOCATE @MyCursor

[ali]

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

کد:

Format#   Query (current date: 12/30/2006)    Sample
======================================
1    select convert(varchar, getdate(), 1)    12/30/06
2    select convert(varchar, getdate(), 2)    06.12.30
3    select convert(varchar, getdate(), 3)    30/12/06
4    select convert(varchar, getdate(), 4)    30.12.06
5    select convert(varchar, getdate(), 5)    30-12-06
6    select convert(varchar, getdate(), 6)    30 Dec 06
7    select convert(varchar, getdate(), 7)    Dec 30, 06
10    select convert(varchar, getdate(), 10)    12-30-06
11    select convert(varchar, getdate(), 11)    06/12/30
101    select convert(varchar, getdate(), 101)    12/30/2006
102    select convert(varchar, getdate(), 102)    2006.12.30
103    select convert(varchar, getdate(), 103)    30/12/2006
104    select convert(varchar, getdate(), 104)    30.12.2006
105    select convert(varchar, getdate(), 105)    30-12-2006
106    select convert(varchar, getdate(), 106)    30 Dec 2006
107    select convert(varchar, getdate(), 107)    Dec 30, 2006
110    select convert(varchar, getdate(), 110)    12-30-2006
111    select convert(varchar, getdate(), 111)    2006/12/30
======================================

تبدیلات مربوط به زمان:

کد:

8 or 108    select convert(varchar, getdate(), 8)    00:38:54
9 or 109    select convert(varchar, getdate(), 9)    Dec 30 2006 12:38:54:840AM
14 or 114    select convert(varchar, getdate(), 14)    00:38:54:840

[ali]

رفع Error اس کیو ال سرور user group or role already exists in the current database
این پیغام خطا معمولا زمانی رخ می دهد که در بخش کاربران SQL کاربری را ایجاد بنمایید که در یک دیتابیس قبلا موجود می باشد. برای رفع ای ERROR بایستی از کد زیر استفاده نمایید:

کد:

ALTER USER [<username>] WITH LOGIN = [<username>]

که به جای عبارت username همان نام کاربری را ثبت نمایید. مثال:

کد:

ALTER USER ali WITH LOGIN = ali

[ali]

کد:

(SELECT ABS(CHECKSUM(NewId())) % 14)

[ali]

برای یجاد جدول TEMP در SQL Server بایستی به شکل زیر جدول و فیلدهای آن را ایجاد نمود. در مثال زیر ابتدا جدول ایجاد شده ، سپس داخل آن مقادیری وارد شده و در مرحله بعدی با استفاده از دستور SELECT مقدار داخل آن نمایش داده شده و در نهایت مجددا جدول مجازی پاک شده است.

کد:

--- ایجاد
CREATE TABLE #MyTempTable
(
    MyRowID INT NOT NULL ,
    MyRowDescription NVARCHAR(50)
)
--- درج مقدار
INSERT INTO #MyTempTable
        ( MyRowID, MyRowDescription )
VALUES  ( 1,
          N'MyFirstName'
          )  
--- نمایش مقادر داخل جدول          
SELECT * FROM #MyTempTable
--- حذف مجدد جدول
DROP TABLE #MyTempTable

[ali]

ابتدا یک تابع که مقداری عددی را برمیگرداند و دارای پارامتر ورودی از نوع رشته است را ارائه می دهیم:

کد:

CREATE FUNCTION [dbo].[CalcdLen](@InputStr NVARCHAR(MAX))
RETURNS int
AS
BEGIN
    return LEN(@InputStr)
END

نحوه استفاده: (خروجی عدد 11 خواهد بود)

کد:

SELECT dbo.CalcdLen('MyNameIsAli')

تابع دوم یک Function است که مقدار جدول برمی گرداند:

کد:

CREATE FUNCTION GetAllProducts( )
RETURNS TABLE
AS
RETURN
(SELECT * FROM dbo.tblLibUsers)
GO

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

کد:

SELECT * from GetAllProducts()

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

کد:

CREATE FUNCTION GetAllProducts3( )
RETURNS @MyTable TABLE
(
MyRiwID INT NOT NULL, MyRoName NVARCHAR(50) NOT NULL
)
AS
BEGIN
INSERT INTO @MyTable
        ( MyRiwID, MyRoName )
VALUES  ( 1,
          N'aa'
          )
RETURN
END
GO

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

کد:

SELECT * FROM GetAllProducts3()

[ali]

با استفاده از نمونه تریگر زیر می توانید یک Database Trigger تعریف کرده و حاصل عملیات آنرا بر روی یک یا چند جدول خاص اعمال نمایید. (How to make DDL Trigger for a specific table)

کد:

CREATE TRIGGER NoEventOnSecificTable ON DATABASE  
FOR
DROP_TABLE , ALTER_TABLE
AS
DECLARE @eventData XML,
        @uname NVARCHAR(50),
        @oname NVARCHAR(100),
        @otext VARCHAR(MAX),
        @etype NVARCHAR(100),
        @edate DATETIME
SET @eventData = eventdata()
SELECT
        @edate=GETDATE(),
        @uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
        @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
        @otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
                'VARCHAR(MAX)'),
        @etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')
IF @oname IN ('Table_1654456545','tblUsers')
  BEGIN
    DECLARE @err varchar(100)
    SET @err = 'Table ' + @oname  + ' is super duper protected and cannot be dropped.'
    RAISERROR (@err, 16, 1) ;
    ROLLBACK;
END
GO