نکته های مفید و حرفه ای در اس کیو ال سرور 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

کد:

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')-- اسکریپت آبجکتهایی به غیر جدولها

کد:

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

کد:

CREATE VIEW [dbo].[XtblGet_RAND]

AS

    SELECT RAND() AS MyRAND

GO

کد:

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

کد:

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

کد:

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

کد:

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

کد:

ALTER USER ali WITH LOGIN = ali

کد:

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

کد:

--- ایجاد

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

کد:

CREATE FUNCTION [dbo].[CalcdLen](@InputStr NVARCHAR(MAX))

RETURNS int

AS

BEGIN

    return LEN(@InputStr)

END

کد:

SELECT dbo.CalcdLen('MyNameIsAli')

کد:

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()

کد:

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