SQL Snippets

String Contains

DECLARE @string VARCHAR(100) = 'This string contains the word blank.'
DECLARE @find VARCHAR(20) = 'blank'


/* Method 1 */
IF CHARINDEX(@find, @string) <> 0
	PRINT 'Contains ' + @find
ELSE
	PRINT 'does not contain ' + @find

/* Method 2 */
IF @string LIKE '%' + @find + '%'  
    PRINT 'Contains ' + @find
ELSE  
    PRINT 'does not contain ' + @find

Creating a Table of Minutes

DECLARE @startDate DATETIME = GETDATE()-1, @endDate   DATETIME = GETDATE()
    
DROP TABLE IF EXISTS #minutes
CREATE TABLE #minutes (theMinute SMALLDATETIME)

INSERT INTO #minutes ( theMinute )
VALUES ( @startDate )

WHILE (SELECT MAX(theMinute) FROM #minutes) < @endDate
BEGIN

	INSERT INTO #minutes ( theMinute )
	VALUES ( DATEADD(MINUTE, 1, (SELECT MAX(theMinute) FROM #minutes)))  

END

SELECT theMinute
FROM #minutes
ORDER BY theMinute

Using LAG For a Previous Rows Value

SELECT 
	  theDay
	, LAG(theDay,1) OVER (ORDER BY theDay) AS previousDay
FROM dbo.dates
WHERE theDay BETWEEN GETDATE()-7 AND GETDATE()
ORDER BY theDay 

Using Row_Number and NTILE (Percentile)

SELECT 
	  theMinute
	, SMALLDATETIMEFROMPARTS(YEAR(theMinute),MONTH(theMinute), DAY(theMinute), DATEPART(HOUR,theMinute), 0) AS theHour
	, ROW_NUMBER() OVER (ORDER BY theMinute)  AS minuteCount
	, DATEPART(MINUTE,theMinute) AS datePartMinute
	, ROW_NUMBER() OVER (PARTITION BY DATEPART(HOUR,theMinute) ORDER BY theMinute)  AS rowCountMinute
	, ROW_NUMBER() OVER (PARTITION BY SMALLDATETIMEFROMPARTS(YEAR(theMinute),MONTH(theMinute), DAY(theMinute), DATEPART(HOUR,theMinute), 0) ORDER BY theMinute)  AS rowCountMinuteFixed
	, NTILE( (SELECT COUNT(DISTINCT DATEPART(HOUR,theMinute)) FROM #minutes) ) OVER (ORDER BY theMinute) - 1 AS hoursFromNow
	, NTILE(10) OVER(ORDER BY theMinute) AS tenthPercentile
FROM #minutes
ORDER BY theMinute

While Loop with Output Messages and a Brief Pause

DECLARE @OutputMessage VARCHAR(1000)
DECLARE @timeChange DECIMAL(6,2)
DECLARE @lastTime DATETIME = CURRENT_TIMESTAMP

DECLARE @iterator INT = 10

WHILE @iterator > 0
BEGIN	
	SET @timeChange = (1.*DATEDIFF(MILLISECOND, @lastTime, CURRENT_TIMESTAMP)) / 1000.0
	SET @lastTime = CURRENT_TIMESTAMP

	SET @OutputMessage = '{' + CONVERT(VARCHAR(20), CURRENT_TIMESTAMP) + '}' + '} Loops remaining: '  + CAST(@iterator AS VARCHAR(2)) + '; Last Loop took ' + CAST(@timeChange AS VARCHAR(8)) + ' seconds';
	IF @timeChange = 0 SET @OutputMessage = '{' + CONVERT(VARCHAR(20), CURRENT_TIMESTAMP) + '} Loops remaining: '  + CAST(@iterator AS VARCHAR(2)) + '; Starting Loops'
	RAISERROR(@OutputMessage, 0, 1) WITH NOWAIT;
	
	SET @iterator = @iterator-1

	WAITFOR DELAY '0:00:00.25'  /* quarter second delay to avoid flooding AG replicas */
END

SET @timeChange = (1.*DATEDIFF(MILLISECOND, @lastTime, CURRENT_TIMESTAMP)) / 1000.0
SET @OutputMessage = '{' + CONVERT(VARCHAR(20), CURRENT_TIMESTAMP) + '}' + '; Last Loop took ' + CAST(@timeChange AS VARCHAR(8)) + ' seconds';
RAISERROR(@OutputMessage, 0, 1) WITH NOWAIT;

Adding a Column or Table

When creating and testing a SQL script, it’s nice to be able to run the script many times without having to worry about removing a table or column that you are creating. These methods check first if the column/table already exists before adding it.