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

[ali]

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

کد:

DECLARE @SchemaName VARCHAR(256)DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @TSQLDropIndex VARCHAR(MAX)

DECLARE CursorIndexes CURSOR FOR
SELECT schema_name(t.schema_id), t.name,  i.name
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id= i.object_id
WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
and (is_primary_key=0 and is_unique_constraint=0)

OPEN CursorIndexes
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName

WHILE @@fetch_status = 0
BEGIN
SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + '.' +QUOTENAME(@IndexName)
PRINT @TSQLDropIndex
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
END

CLOSE CursorIndexes
DEDEALLOCATE CursorIndexes

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

کد:

declare @SchemaName varchar(100)declare @TableName varchar(256)
declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int
declare @IsIncludedColumn int
declare @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)

declare CursorIndex cursor for
select schema_name(t.schema_id) [schema_name], t.name, ix.name,
case when ix.is_unique = 1 then 'UNIQUE ' else '' END
, ix.type_desc,
case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end
+ case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end
+ case when ix.allow_row_locks=1 then  'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end
+ case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end
+ case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end
+ 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions
, ix.is_disabled , FILEGROUP_NAME(ix.data_space_id) FileGroupName
from sys.tables t
inner join sys.indexes ix on t.object_id=ix.object_id
where ix.type>0 and ix.is_primary_key=0 and ix.is_unique_constraint=0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
order by schema_name(t.schema_id), t.name, ix.name

open CursorIndex
fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName

while (@@fetch_status=0)
begin
declare @IndexColumns varchar(max)
declare @IncludedColumns varchar(max)

set @IndexColumns=''
set @IncludedColumns=''

declare CursorIndexColumn cursor for
  select col.name, ixc.is_descending_key, ixc.is_included_column
  from sys.tables tb
  inner join sys.indexes ix on tb.object_id=ix.object_id
  inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
  inner join sys.columns col on ixc.object_id =col.object_id  and ixc.column_id=col.column_id
  where ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0)
  and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
  order by ixc.index_column_id

open CursorIndexColumn
fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn

while (@@fetch_status=0)
begin
  if @IsIncludedColumn=0
   set @IndexColumns=@IndexColumns + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
  else
   set @IncludedColumns=@IncludedColumns  + @ColumnName  +', '

  fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
end

close CursorIndexColumn
deallocate CursorIndexColumn

set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end
--  print @IndexColumns
--  print @IncludedColumns

set @TSQLScripCreationIndex =''
set @TSQLScripDisableIndex =''
set @TSQLScripCreationIndex='CREATE '+ @is_unique  +@IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ '('+@IndexColumns+') '+
  case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'  

if @is_disabled=1
  set  @TSQLScripDisableIndex=  CHAR(13) +'ALTER INDEX ' +QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13)

print @TSQLScripCreationIndex
print @TSQLScripDisableIndex

fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName

end
close CursorIndex
deallocate CursorIndex

ضمنا می توانید مقدار FILLFACTOR را به دلخواه تنظیم نمایید. از عدد 1 تا 100 قابل تنظیم می باشد. مقدار عددی وارد شده هم بستگی به یک سری آیتم دارد که می توانید در اینترنت جستجو نمایید و بر اساس آن این عدد را تنظیم نمایید.

[ali]

Adding Row Number to SQL SELECT result
گاهی اوقات لازم است که به نتیجه یک SELECT انجام شده یک ردیف شمارنده در SQL Server اضافه کنیم.

کد:

SELECT EmployeeId, EmployeeName, Salary    
        FROM Employees

EmployeeId   EmployeeName     Salary
-------------------------------------
1002         Alden            4000
2343         Lawson           4500
2004         Barbra           4800
1105         Marsden          4500
3116         Mac              5000

برای این منظور از کد زیر استفاده می نماییم:

کد:

SELECT ROW_NUMBER()
        OVER (ORDER BY EmployeeName) AS Row,
    EmployeeId, EmployeeName, Salary
FROM Employees

Row   EmployeeId   EmployeeName     Salary
-------------------------------------------
1     1002         Alden            4000
2     2343         Lawson           4500
3     2004         Barbra           4800
4     1105         Marsden          4500
5     3116         Mac              5000

[ali]

نحوه تغییر نام فیلد در SQL Server
rename column in SQL server

کد:

EXEC sp_RENAME 'table_name.old_name', 'new_name', 'COLUMN'

[ali]

کد:

CREATE FUNCTION [dbo].[ShamsiToMiladi](@DateStr varchar(10))
RETURNS DATETIME
AS
BEGIN
   declare @YYear int
   declare @MMonth int
   declare @DDay int
   declare @epbase int
   declare @epyear int
   declare @mdays int
   declare @persian_jdn int
   declare @i int
   declare @j int
   declare @l int
   declare @n int
   declare @TMPRESULT varchar(10)
   declare @IsValideDate int
   declare @TempStr varchar(20)
   DECLARE @TmpDateStr varchar(10)
  
   SET @i=charindex('/',@DateStr)
  
   IF LEN(@DateStr) - CHARINDEX('/', @DateStr,CHARINDEX('/', @DateStr,1)+1) = 4
   BEGIN
     SET @TmpDateStr = dbo.ReversDate(@DateStr)
     IF ( ISDATE(@TmpDateStr) =1 )  
       RETURN @TmpDateStr
     ELSE
        RETURN NULL
   END
   ELSE
     SET @TmpDateStr = @DateStr
  
   IF ((@i<>0) and
       (dbo.SubStrCount('/', @TmpDateStr)=2) and
        (ISNUMERIC(REPLACE(@TmpDateStr,'/',''))=1) and
        (charindex('.',@TmpDateStr)=0)
       )
   BEGIN
       SET @YYear=CAST(SUBSTRING(@TmpDateStr,1,@i-1) AS INT)
                IF ( @YYear< 1300 )
                      SET @YYear =@YYear + 1300
                IF @YYear > 9999
                  RETURN NULL
  
       SET @TempStr= SUBSTRING(@TmpDateStr,@i+1,Len(@TmpDateStr))
  
       SET @i=charindex('/',@TempStr)
       SET @MMonth=CAST(SUBSTRING(@TempStr,1,@i-1) AS INT)
       SET @MMonth=@MMonth-- -1
  
       SET @TempStr= SUBSTRING(@TempStr,@i+1,Len(@TempStr))  
  
       SET @DDay=CAST(@TempStr AS INT)
       SET @DDay=@DDay-- - 1
  
                 IF ( @YYear >= 0 )
                     SET @epbase = @YYear - 474
                 Else
                     SET @epbase = @YYear - 473
                 SET @epyear = 474 + (@epbase % 2820)
  
        IF (@MMonth <= 7 )
                       SET @mdays = ((@MMonth) - 1) * 31
        Else
            SET @mdays = ((@MMonth) - 1) * 30 + 6
  
        SET @persian_jdn =(@DDay)  + @mdays + CAST((((@epyear * 682) - 110) / 2816) as int)  + (@epyear - 1) * 365  +  CAST((@epbase / 2820)  as int ) * 1029983  + (1948321 - 1)
  
  
  
        IF (@persian_jdn > 2299160)
                 BEGIN
            SET @l = @persian_jdn + 68569
            SET @n = CAST(((4 * @l) / 146097) as int)
            SET @l = @l -  CAST(((146097 * @n + 3) / 4) as int)
            SET @i =  CAST(((4000 * (@l + 1)) / 1461001) as int)
            SET @l = @l - CAST( ((1461 * @i) / 4) as int) + 31
            SET @j =  CAST(((80 * @l) / 2447) as int)
            SET @DDay = @l - CAST( ((2447 * @j) / 80) as int)
             SET @l =  CAST((@j / 11) as int)
            SET @MMonth = @j + 2 - 12 * @l
            SET @YYear = 100 * (@n - 49) + @i + @l
                 END
  
           SET @TMPRESULT=Cast(@MMonth as varchar(2))+'/'+CAST(@DDay as Varchar(2))+'/'+CAST(@YYear as varchar(4))  
           RETURN Cast(@TMPRESULT as Datetime)
  
    END
    RETURN NULL    
  
END
go
CREATE  FUNCTION [dbo].[SubStrCount](@SubStr varchar(8000), @MainText Text)  
RETURNS  int
AS
BEGIN
  DECLARE @StrCount int
  DECLARE @StrPos int
  
  SET @StrCount = 0
  SET @StrPos = 0
  SET @StrPos = CHARINDEX( @SubStr, @MainText, @StrPos)
  
  WHILE @StrPos > 0
  BEGIN
    SET @StrCount = @StrCount + 1
    SET @StrPos = CHARINDEX( @SubStr, @MainText, @StrPos+1)
  END
  
  RETURN  @StrCount  
END

go
CREATE FUNCTION [dbo].[ReversDate] (@DateStr varchar(10))
RETURNS varchar(10)
  
AS
  
BEGIN
  DECLARE @TempStr varchar(10)
  DECLARE @StartIndex int
  DECLARE @SubStrLen int
  DECLARE @i int
  
  SET @TempStr = ''
  SET @StartIndex = LEN(@DateStr) + 2
  SET @i = LEN(@DateStr)
  WHILE @i > 0
  BEGIN
    IF SUBSTRING(@DateStr,@i,1) IN ('/', '-')
    BEGIN
      SET @SubStrLen = @StartIndex - (@i + 2)
      SET @StartIndex = @i + 1
      SET @TempStr = @TempStr + SUBSTRING(@DateStr,@StartIndex,@SubStrLen) + SUBSTRING(@DateStr,@i,1)
    END
    SET @i = @i - 1
  END
  IF @TempStr <> ''
  BEGIN
    SET @SubStrLen = @StartIndex - 2
    SET @TempStr = @TempStr + SUBSTRING(@DateStr,1,@SubStrLen)
  END
  ELSE
    SET @TempStr = @DateStr
  RETURN @TempStr
END

[ali]

کد:

-- Server and instance name
Select @@SERVERNAME as [Server\Instance];
-- SQL Server Version
Select @@VERSION as SQLServerVersion;
-- SQL Server Instance
Select @@ServiceName AS ServiceInstance;

-- Current Database
Select DB_NAME() AS CurrentDB_Name;

[ali]

کد:

-- Note the tempdb system database is recreated every time the server restarts
-- Thus this is one method to tell when the database server was last restarted
SELECT  @@Servername AS ServerName ,
        create_date AS ServerStarted ,
        DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning ,
        DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig
FROM    sys.databases
WHERE   name = 'tempdb';
GO

[ali]

کد:

EXEC sp_helpdb;
--OR
EXEC sp_Databases;
--OR
SELECT  @@SERVERNAME AS Server ,
        name AS DBName ,
        recovery_model_Desc AS RecoveryModel ,
        Compatibility_level AS CompatiblityLevel ,
        create_date ,
        state_desc
FROM    sys.databases
ORDER BY Name;
--OR
SELECT  @@SERVERNAME AS Server ,
        d.name AS DBName ,
        create_date ,
        compatibility_level ,
        m.physical_name AS FileName
FROM    sys.databases d
        JOIN sys.master_files m ON d.database_id = m.database_id
WHERE   m.[type] = 0 -- data files only
ORDER BY d.name;
GO

[ali]

برای بدست آوردن Session ID های فعال در SQL Server از Query زیر استفاده می شود:

کد:

SELECT *
FROM
   sys.dm_exec_sessions s
   LEFT  JOIN sys.dm_exec_connections c
        ON  s.session_id = c.session_id
   LEFT JOIN sys.dm_db_task_space_usage tsu
        ON  tsu.session_id = s.session_id
   LEFT JOIN sys.dm_os_tasks t
        ON  t.session_id = tsu.session_id
        AND t.request_id = tsu.request_id
   LEFT JOIN sys.dm_exec_requests r
        ON  r.session_id = tsu.session_id
        AND r.request_id = tsu.request_id
   OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) TSQL

[ali]

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

کد:

CREATE FUNCTION [dbo].[UDF_Persian_To_Julian](@iYear int,@iMonth int,@iDay int)
RETURNS bigint
AS
Begin

Declare @PERSIAN_EPOCH  as int
Declare @epbase as bigint
Declare @epyear as bigint
Declare @mdays as bigint
Declare @Jofst  as Numeric(18,2)
Declare @jdn bigint

Set @PERSIAN_EPOCH=1948321
Set @Jofst=2415020.5

If @iYear>=0
    Begin
        Set @epbase=@iyear-474
    End
Else
    Begin
        Set @epbase = @iYear - 473
    End
    set @epyear=474 + (@epbase%2820)
If @iMonth<=7
    Begin
        Set @mdays=(Convert(bigint,(@iMonth) - 1) * 31)
    End
Else
    Begin
        Set @mdays=(Convert(bigint,(@iMonth) - 1) * 30+6)
    End
    Set @jdn =Convert(int,@iday) + @mdays+ Cast(((@epyear * 682) - 110) / 2816 as int)  + (@epyear - 1) * 365 + Cast(@epbase / 2820 as int) * 1029983 + (@PERSIAN_EPOCH - 1)
    RETURN @jdn
End
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
CREATE FUNCTION [dbo].[UDF_Gregorian_To_Persian] (@date datetime)
Returns nvarchar(50)
AS
Begin
    Declare @depoch as bigint
    Declare @cycle  as bigint
    Declare @cyear  as bigint
    Declare @ycycle as bigint
    Declare @aux1 as bigint
    Declare @aux2 as bigint
    Declare @yday as bigint
    Declare @Jofst  as Numeric(18,2)
    Declare @jdn bigint

    Declare @iYear   As Integer
    Declare @iMonth  As Integer
    Declare @iDay    As Integer

    Set @Jofst=2415020.5
    Set @jdn=Round(Cast(@date as int)+ @Jofst,0)

    Set @depoch = @jdn - [dbo].[UDF_Persian_To_Julian](475, 1, 1)
    Set @cycle = Cast(@depoch / 1029983 as int)
    Set @cyear = @depoch%1029983

    If @cyear = 1029982
       Begin
         Set @ycycle = 2820
       End
    Else
       Begin
        Set @aux1 = Cast(@cyear / 366 as int)
        Set @aux2 = @cyear%366
        Set @ycycle = Cast(((2134 * @aux1) + (2816 * @aux2) + 2815) / 1028522 as int) + @aux1 + 1
      End

    Set @iYear = @ycycle + (2820 * @cycle) + 474

    If @iYear <= 0
      Begin
        Set @iYear = @iYear - 1
      End
    Set @yday = (@jdn - [dbo].[UDF_Persian_To_Julian](@iYear, 1, 1)) + 1
    If @yday <= 186
       Begin
         Set @iMonth = CEILING(Convert(Numeric(18,4),@yday) / 31)
       End
    Else
       Begin
          Set @iMonth = CEILING((Convert(Numeric(18,4),@yday) - 6) / 30)  
       End
       Set @iDay = (@jdn - [dbo].[UDF_Persian_To_Julian](@iYear, @iMonth, 1)) + 1

      Return Convert(nvarchar(50),@iDay) + '-' +   Convert(nvarchar(50),@iMonth) +'-' + Convert(nvarchar(50),@iYear)
End
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
CREATE FUNCTION [dbo].[UDF_Julian_To_Gregorian] (@jdn bigint)
Returns nvarchar(11)
WITH ENCRYPTION
AS
Begin
    Declare @Jofst  as Numeric(18,2)
    Set @Jofst=2415020.5
    Return Convert(nvarchar(11),Convert(datetime,(@jdn- @Jofst),113),110)
End
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
CREATE FUNCTION [dbo].[UDFMake1numTo2Num](@StrMyNum NVARCHAR(2))
    RETURNS NVARCHAR(2)
AS
BEGIN
    DECLARE @MyNunInStr NVARCHAR(10)
    SET @MyNunInStr = @StrMyNum
    IF LEN(@MyNunInStr) < 2
    BEGIN
     SET @MyNunInStr = '0' + @MyNunInStr
    END
RETURN @MyNunInStr
END
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
-- متد برای تصحیح قالب تاریخ شمسی
CREATE FUNCTION [dbo].[UDF_ReverseShamsiDate](@StrDateShamsi NVARCHAR(10), @Seperator CHAR(1))
RETURNS NVARCHAR(10)
AS
BEGIN
    DECLARE @StrDayOfMotn NVARCHAR(10)
    DECLARE @StrMothOfYear NVARCHAR(10)
    DECLARE @StrYearOfYear NVARCHAR(10)
    
        SET @StrDayOfMotn = dbo.UDFMake1numTo2Num(REPLACE(SUBSTRING(@StrDateShamsi , 1 , ((SELECT CHARINDEX('-' , @StrDateShamsi , 0)))), '-' , ''))
        SET  @StrMothOfYear = dbo.UDFMake1numTo2Num(REPLACE(SUBSTRING(@StrDateShamsi , ((CHARINDEX('-' , @StrDateShamsi , 0)  )) , 3) , '-' , ''))
        SET @StrYearOfYear = RIGHT(@StrDateShamsi , 4)

    return (@StrYearOfYear + @Seperator + @StrMothOfYear + @Seperator + @StrDayOfMotn)
END
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
CREATE FUNCTION [dbo].[MakeDateShamsiToMiladi](@InputShamsiDateString nvarchar(10))
RETURNS datetime
AS
BEGIN
    declare @InputShamsiDateString1 nvarchar(10)
    declare @yearm int
    declare @monthm int
    declare @daym int
    set @yearm = CONVERT(int , SUBSTRING(@InputShamsiDateString , 1 , 4))
    set @monthm = CONVERT(int , SUBSTRING(@InputShamsiDateString , 6 , 2))
    set @daym = CONVERT(int , SUBSTRING(@InputShamsiDateString , 9 , 2))
    return (select dbo.[UDF_Julian_To_Gregorian](dbo.[UDF_Persian_To_Julian](@yearm,@monthm ,@daym )))
END
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
GO
-- فانکشن نهایی برای تاریخ صحیحی شمسی
CREATE FUNCTION [dbo].[MakeCompleteShmsiDate](@InputMiladiDate DateTime , @MySeperatorChar char(1))
RETURNS NVARCHAR(10)
AS
BEGIN
    return (select dbo.UDF_ReverseShamsiDate(dbo.UDF_Gregorian_To_Persian(@InputMiladiDate), @MySeperatorChar) AS ShamsiDateOfLog)
END
GO

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

کد:

select [dbo].[MakeCompleteShmsiDate](GETDATE() , '/')

خروجی:

کد:

1395/04/07

[ali]

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

کد:

Msg 15434, Level 16, State 1, Line 1
Could not drop login 'ode' as the user is currently logged in.

برای رفع این مشکل ابتدا باید Session کاربر را یافته و سپس آن را KILL کرد و در نهاید کاربر را حذف نمود.

کد:

SELECT session_id
FROM sys.dm_exec_sessions
WHERE login_name = 'ode'

کد:

KILL 52 -- عدد 52 را با عدد بدست آمده خودتان جایگزین نمایید

و در نهایت می توانید کاربر را DROP یا حذف نمایید.

کد:

DROP LOGIN ode

به جای ode نام کاربر مورد نظر خود را قرار دهید.