SQL Server Nuggets

  https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

  https://www.sqlservercentral.com/stairways/stairway-to-row-level-security

  https://sqlespresso.com/2020/09/09/resizing-tempdb-when-tempdb-wont-shrink/

https://sqlespresso.com/  

CREATE PROCEDURE [eform].[CLEAR_ExceptionPolicyCorrectionPlan_HISTORY] @P_RETAIN_HISTORY_DAYS INT = 7 AS BEGIN SET NOCOUNT ON; DECLARE @TODAY DATE = GETDATE() , @RETAIN_HISTORY_DATE DATE , @MAX_STAGE_ID INT; SET @RETAIN_HISTORY_DATE = DATEADD(day, (@P_RETAIN_HISTORY_DAYS * -1), @TODAY); SELECT @MAX_STAGE_ID = MAX(StageID) FROM [eform].[ExceptionPolicyCorrectionPlan_LOG] WHERE LoadDateTime < @RETAIN_HISTORY_DATE; IF @MAX_STAGE_ID IS NULL RETURN; DELETE <HISTORY-TABLE> WHERE StageID <= @MAX_STAGE_ID;  

Prepare for staging and refresh of target table. Staging table name is TARGET table name with _STAGING Appended. TRUNCATE the staging table. Get the MAX DATE LOADED from the TARGET table Workflow:  - PREP FOR STAGING - LOAD STAGING FROM SOURCE - MERGE TARGET FROM SOURCE /* DECLARE @MAX_DATE_LOADED DATETIME; EXEC [dbo].[PREP_FOR_STAGING] @P_TABLE_SCHEMA = 'dbo' , @P_TABLE_NAME = 'TABLE_NAME' , @P_MAX_DATE_LOADED_OUT = @MAX_DATE_LOADED OUTPUT; SELECT @MAX_DATE_LOADED; */ CREATE PROCEDURE [ETL].[PREP_FOR_STAGING] @P_TABLE_SCHEMA NVARCHAR(50) , @P_TABLE_NAME NVARCHAR(50) , @P_MAX_DATE_LOADED_OUT DATETIME OUTPUT AS BEGIN DECLARE @MAX_DATE_LOADED DATETIME = CONVERT(DATE, GETDATE())  , @SQL NVARCHAR(4000); -- truncate the staging table SET @SQL = CONCAT( N'TRUNCATE TABLE '     , @P_TABLE_SCHEMA     , N'.'     , @P_TABLE_NAME , N'_STAGING' ); EXEC sp_executesql @SQL; -- g...

 OUTPUT returns a result set of the rows inserted, updated or deleted. Delete rows and return the rows deleted. DELETE <schema>.<table_name> OUTPUT DELETED.* OPTIONAL FROM, JOIN, WHERE, etc. MERGE You can access the $action column in the OUTPUT. It is an NVARCHAR(10) that returns  'INSERT', 'UPDATE', or 'DELETE' for each row of the OUTPUT. You can use WITH (READPAST) on UPDATE and DELETE statements when multiple processes can perform a destructive read. INSERT Example INSERT <table_name>  (<column_list>)  OUTPUT INSERTED.<colname(s)> INTO <table_name> VALUES (<value_list>) UPDATE Example UPDATE <table_name> SET <colN> = <value> OUTPUT INSERTED.<colname>, DELETED.<colname> INTO <table_name> OPTIONAL FROM, WHERE, etc.

  https://datajoe.blog/2019/05/22/create-a-slowly-changing-dimension-type-2-with-t-sql/ This uses T-SQL MERGE. I think it's a complete implementation.

  https://sqlperformance.com/2014/07/t-sql-queries/isolation-levels

You have tables used in reporting that have CreatedDate and ModifiedDate columns. You want to report activity at certain times of the day; e.g. 10AM, 1PM, and 4PM. The report users are in different time zones. How can you handle this? Use DATETIMEOFFSET as the column type for the CreatedDate and ModifiedDate columns; e.g. CreatedTime DATETIMOFFSET(0), ModifiedTime DATETIMEOFFSET(0) When you set the values for these columns use <column name> = GETUTCDATE() You will see values like this: 2021-01-08 20:14:52 +00:00 When a user is running a report, they should see the CreatedDate or ModifiedDate in their time zone. You can do that easily by associating the user with a time zone; e.g. use the time zone of their office; e.g. SELECT ModifiedDate AT TIME ZONE 'Eastern Standard Time' and you'll see this: 2021-01-08 15:14:52 -05:00 To see the list of time zones: SELECT * FROM sys.time_zone_info

https://www.sqlshack.com/temporal-tables-in-sql-server/  

https://www.brentozar.com/archive/2020/08/3-ways-to-run-dbcc-checkdb-faster/  

  https://www.mssqltips.com/sqlservertip/3176/identify-completion-time-for-long-running-sql-server-processes-using-dynamic-management-objects/

  https://www.brentozar.com/archive/2017/11/move-tempdb-another-drive-folder/    

  https://www.brentozar.com/archive/2020/02/how-to-pass-a-list-of-values-into-a-stored-procedure/ INSERT results from STRING_SPLIT gives best performance from the alternatives discussed.