Lập Trình .NET

SELECT 
	DB_NAME(req.database_id) as db_name
	,sqltext.text
    ,req.session_id
    ,req.status
    ,req.command
	,req.start_time
    ,req.cpu_time
    ,req.total_elapsed_time
	,queryplan.query_plan
	,c.client_net_address
	,s.login_name
	,s.login_time
	,s.program_name
	,s.host_name
	,s.host_process_id
	,s.client_interface_name
	,s.client_version
	--,req.*
	--,c.*
	--,s.*
FROM sys.dm_exec_requests req
JOIN sys.dm_exec_connections c on req.connection_id = c.connection_id
JOIN sys.dm_exec_sessions s on req.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) as queryplan

declare @dateTime datetime2 = SYSDATETIME()
declare @dateTimeOffset datetimeoffset = SYSDATETIMEOFFSET()
 
select @dateTime as [CurrentDateTime],
@dateTimeOffset as [CurrentDateTimeOffset],
CAST(@dateTimeOffset as datetime2) as [DateTime1]


select CAST('2023-04-19 15:32:31.2721255 +07:00' as datetime2) -- Output: 2023-04-19 15:32:31.2721255
select CAST('2023-04-19 15:32:31.2721255 +08:00' as datetime2) -- Output: 2023-04-19 15:32:31.2721255

select SWITCHOFFSET('2023-04-19 15:32:31.2721255 +07:00', '+01:00') -- Output: 2023-04-19 09:32:31.2721255 +01:00
select SWITCHOFFSET('2023-04-19 15:32:31.2721255 +08:00', '+01:00') -- Output: 2023-04-19 08:32:31.2721255 +01:00

select CAST(SWITCHOFFSET('2023-04-19 15:32:31.2721255 +07:00', '+01:00') as datetime2) -- Output: 2023-04-19 09:32:31.2721255
select CAST(SWITCHOFFSET('2023-04-19 15:32:31.2721255 +08:00', '+01:00') as datetime2) -- Output: 2023-04-19 08:32:31.2721255

declare @dateTime datetime2 = SYSDATETIME()
declare @dateTimeOffset datetimeoffset = SYSDATETIMEOFFSET()

select @dateTime as [CurrentDateTime],
@dateTimeOffset as [CurrentDateTimeOffset],
CAST(@dateTime as datetimeoffset) as [DateTimeOffset1],
TODATETIMEOFFSET(@dateTime, '+07:00') as [DateTimeOffset2],
TODATETIMEOFFSET(@dateTime, RIGHT(SYSDATETIMEOFFSET(), 6)) as [DateTimeOffset3]

SELECT name, physical_name AS CurrentLocation, state_desc  
FROM sys.master_files  
WHERE database_id = DB_ID(N'tempdb');
GO

-- Stop SQL Server
-- Copy files to new location
-- Start SQL Server

USE master;
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\MSSQL\DATA\tempdb.mdf');
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'E:\MSSQL\DATA\templog.ldf');
GO

-- Restart SQL Server


-- https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-system-databases?view=sql-server-2017
-- https://www.mytechmantra.com/LearnSQLServer/How-to-Move-TempDB-to-New-Drive-in-SQL-Server/

SELECT OBJECT_NAME([ps].[object_id], [ps].[database_id]) 
            AS [ProcedureName]
 , [ps].[execution_count] AS [ProcedureExecutes]
 , [qs].[plan_generation_num] AS [VersionOfPlan]
 , [qs].[execution_count] AS [ExecutionsOfCurrentPlan]
 , SUBSTRING ([st].[text], 
  ([qs].[statement_start_offset] / 2) + 1, 
     ((CASE [statement_end_offset] 
   WHEN -1 THEN DATALENGTH ([st].[text]) 
      ELSE [qs].[statement_end_offset] END 
   - [qs].[statement_start_offset]) / 2) + 1) 
      AS [StatementText]
    , [qs].[statement_start_offset] AS [offset]
    , [qs].[statement_end_offset] AS [offset_end]
    , [qp].[query_plan] AS [Query Plan XML]
    , [qs].[query_hash] AS [Query Fingerprint]
    , [qs].[query_plan_hash] AS [Query Plan Fingerprint]
FROM [sys].[dm_exec_procedure_stats] AS [ps]
JOIN [sys].[dm_exec_query_stats] AS [qs] ON [ps].[plan_handle] = [qs].[plan_handle]
CROSS APPLY [sys].[dm_exec_query_plan] ([qs].[plan_handle]) AS [qp]
CROSS APPLY [sys].[dm_exec_sql_text] ([qs].[sql_handle]) AS [st]
WHERE [ps].[database_id] = DB_ID()
ORDER BY [ProcedureName], [qs].[statement_start_offset];

select tb.name as table_name, cl.name as column_name
, type.name as type_name
, cl.max_length
from sys.columns cl
join sys.tables tb on cl.object_id = tb.object_id
join sys.types type on cl.user_type_id = type.user_type_id
where type.name in ('date', 'datetime', 'datetime2')
order by tb.name, cl.name

-- batch size: 20,000
-- total records: 10,000,000

DELETE TOP (20000)
FROM TableName
WHERE Condition

GO 500

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo', 
    @object_name = 'TableName', 
    @index_id = NULL, 
    @partition_number = NULL, 
    @data_compression = 'ROW'
GO

EXEC sp_estimate_data_compression_savings 
    @schema_name = 'dbo', 
    @object_name = 'TableName', 
    @index_id = NULL, 
    @partition_number = NULL, 
    @data_compression = 'PAGE'
GO

SELECT [database_id]
      ,i.[object_id]
      ,i.[index_id]
      ,DB_NAME(i.database_id) as DatabaseName
      ,OBJECT_SCHEMA_NAME (i.object_id, i.database_id) + '.'
      + OBJECT_NAME(i.object_id, i.database_id) as ObjectName
      ,idx.name as IndexName
      ,[partition_number]
      ,[index_type_desc]
      ,[alloc_unit_type_desc]
      ,[index_depth]
      ,[index_level]
      ,[avg_fragmentation_in_percent]
      ,[fragment_count]
      ,[avg_fragment_size_in_pages]
      ,[page_count]
      ,[avg_page_space_used_in_percent]
      ,[record_count]
      ,[ghost_record_count]
      ,[version_ghost_record_count]
      ,[min_record_size_in_bytes]
      ,[max_record_size_in_bytes]
      ,[avg_record_size_in_bytes]
      ,[forwarded_record_count]
      ,[compressed_page_count]
INTO #dm_db_index_physical_stats_logs
FROM sys.dm_db_index_physical_stats  (DB_ID(), NULL, NULL, NULL , 'DETAILED') i
JOIN sys.indexes idx on idx.object_id = i.object_id and idx.index_id = i.index_id;  
GO 


SELECT * 
FROM #dm_db_index_physical_stats_logs
--WHERE object_id = 1477580302
ORDER BY DatabaseName, ObjectName, index_id, index_level
GO

SELECT 
 i.database_id,
 i.object_id,
 i.index_id,
 DB_NAME(i.database_id) as DatabaseName,
 OBJECT_SCHEMA_NAME (i.object_id, i.database_id) + '.' 
 + OBJECT_NAME(i.object_id, i.database_id) as ObjectName,
 idx.name as IndexName,
 i.user_seeks,
 i.user_scans,
 i.user_lookups,
 i.user_updates,
 (user_seeks + user_scans + user_lookups) as TotaledSeekScanLookUp,
 i.last_user_seek,
 i.last_user_scan,
 i.last_user_lookup,
 i.last_user_update,
 i.system_seeks,
 i.system_scans,
 i.system_lookups,
 i.system_updates,
 i.last_system_seek,
 i.last_system_scan,
 i.last_system_lookup,
 i.last_system_update
INTO #dm_db_index_usage_stats_logs
FROM sys.dm_db_index_usage_stats i
JOIN sys.indexes idx on idx.object_id = i.object_id and idx.index_id = i.index_id
GO

SELECT *
FROM #dm_db_index_usage_stats_logs
--WHERE database_id = 9 and object_id = 1477580302
--and user_updates > (user_seeks + user_scans + user_lookups)
ORDER BY DatabaseName, ObjectName, user_seeks
GO

IF OBJECT_ID(N'[dbo].[SpaceUsedLogs]', N'U') IS NULL
BEGIN
 CREATE TABLE SpaceUsedLogs (
  name VARCHAR(200)
  ,rows BIGINT
  ,reserved VARCHAR(100)
  ,data VARCHAR(100)
  ,index_size VARCHAR(100)
  ,unused VARCHAR(100)
  ,created_date DATETIME DEFAULT getdate()
  )
END
GO

DECLARE @spaceUsed TABLE (
 name VARCHAR(200)
 ,rows BIGINT
 ,reserved VARCHAR(100)
 ,data VARCHAR(100)
 ,index_size VARCHAR(100)
 ,unused VARCHAR(100)
 )

INSERT INTO @spaceUsed
EXEC sp_spaceused [Table Name 1]

INSERT INTO @spaceUsed
EXEC sp_spaceused [Table Name 2]

-- ...

INSERT INTO SpaceUsedLogs (name, rows, reserved, data, index_size, unused)
SELECT * FROM @spaceUsed

SELECT 
    XEvent.query('data[@name="xml_report"]/value/deadlock') AS deadlock_graph
FROM    (SELECT CAST([target_data] AS XML) AS TargetData
         FROM sys.dm_xe_session_targets AS st
         INNER JOIN sys.dm_xe_sessions AS s 
            ON [s].[address] = [st].[event_session_address]
         WHERE [s].[name] = N'system_health'
           AND [st].[target_name] = N'ring_buffer') AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent);

SELECT o.name, o.xtype, m.definition, m.uses_ansi_nulls, m.uses_quoted_identifier
FROM sys.sql_modules  m  
INNER JOIN sysobjects o ON m.object_id = o.id 

SET NOCOUNT ON;  
DECLARE @objectid int;  
DECLARE @indexid int;  
DECLARE @partitioncount bigint;  
DECLARE @schemaname nvarchar(130);   
DECLARE @objectname nvarchar(130);   
DECLARE @indexname nvarchar(130);   
DECLARE @partitionnum bigint;  
DECLARE @partitions bigint;  
DECLARE @frag float;  
DECLARE @command nvarchar(4000);   
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function   
-- and convert object and index IDs to names.  
SELECT  
    object_id AS objectid,  
    index_id AS indexid,  
    partition_number AS partitionnum,  
    avg_fragmentation_in_percent AS frag  
INTO #work_to_do  
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')  
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;  

-- Declare the cursor for the list of partitions to be processed.  
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;  

-- Open the cursor.  
OPEN partitions;  

-- Loop through the partitions.  
WHILE (1=1)  
    BEGIN;  
        FETCH NEXT  
           FROM partitions  
           INTO @objectid, @indexid, @partitionnum, @frag;  
        IF @@FETCH_STATUS < 0 BREAK;  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)  
        FROM sys.objects AS o  
        JOIN sys.schemas as s ON s.schema_id = o.schema_id  
        WHERE o.object_id = @objectid;  
        SELECT @indexname = QUOTENAME(name)  
        FROM sys.indexes  
        WHERE  object_id = @objectid AND index_id = @indexid;  
        SELECT @partitioncount = count (*)  
        FROM sys.partitions  
        WHERE object_id = @objectid AND index_id = @indexid;  

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.  
        IF @frag < 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';  
        IF @frag >= 30.0  
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';  
        IF @partitioncount > 1  
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
        PRINT N'Executing: ' + @command;  
        EXEC (@command);  
        PRINT N'Executed: ' + @command;  
    END;  

-- Close and deallocate the cursor.  
CLOSE partitions;  
DEALLOCATE partitions;  

-- Drop the temporary table.  
DROP TABLE #work_to_do;  
GO

SELECT a.index_id
,name
,avg_fragmentation_in_percent
,OBJECT_NAME(a.object_id) as TableName
FROM sys.dm_db_index_physical_stats (DB_ID(),null, NULL, NULL, NULL) AS a  
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where avg_fragmentation_in_percent > 30
order by avg_fragmentation_in_percent desc

CREATE FUNCTION dbo.IsNullOrEmpty(@text nvarchar(max)) returns bit as
BEGIN
 RETURN IIF( @text IS NULL OR LEN(@text) = 0, 1, 0)
END

select 'row.' + COLUMN_NAME + ' = ' 
+ IIF(DATA_TYPE = 'nvarchar','reader["'+COLUMN_NAME+'"] as string','')
+ IIF(DATA_TYPE = 'uniqueidentifier' and Is_nullable = 'NO','Guid.Parse(reader["'+COLUMN_NAME+'"].ToString())','')
+ IIF(DATA_TYPE = 'uniqueidentifier' and Is_nullable = 'YES','reader["'+COLUMN_NAME+'"].ToString() != string.Empty ? Guid.Parse(reader["'+COLUMN_NAME+'"].ToString()) : (Guid?)null','')
+ IIF(DATA_TYPE = 'bit' and Is_nullable = 'NO','bool.Parse(reader["'+COLUMN_NAME+'"].ToString())','')
+ IIF(DATA_TYPE = 'bit' and Is_nullable = 'YES','reader["'+COLUMN_NAME+'"].ToString() != string.Empty ? bool.Parse(reader["'+COLUMN_NAME+'"].ToString()) : (bool?)null','')
+ IIF(DATA_TYPE = 'int' and Is_nullable = 'NO','int.Parse(reader["'+COLUMN_NAME+'"].ToString())','')
+ IIF(DATA_TYPE = 'int' and Is_nullable = 'YES','reader["'+COLUMN_NAME+'"].ToString() != string.Empty ? int.Parse(reader["'+COLUMN_NAME+'"].ToString()) : (int?)null','')
+ IIF(DATA_TYPE = 'timestamp','reader["'+COLUMN_NAME+'"] as byte[]','')
-- another types add here
+';'
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = '@TABLE_NAME'