brovish’s gists

brovish / StatementCachingAnaliz.sql

Last active March 21, 2021 21:17

some sp, dmv, notes from course SQL Server: Optimizing Ad Hoc Statement Performance Kimberly L. Tripp https://app.pluralsight.com/library/courses/sqlserver-optimizing-adhoc-statement-performance/table-of-contents

	-----------------------------------------------------------------
	-- Course: SQL Server: Optimizing Ad Hoc Statement Performance
	-- Module: Statement Caching
	-- Demo: Analyzing query_hash and query_plan_hash
	-----------------------------------------------------------------

	SELECT TOP(10) [type] AS [Memory Clerk Type],
	SUM(pages_kb)/1024 AS [Memory Usage (MB)]
	FROM sys.dm_os_memory_clerks WITH (NOLOCK)
	GROUP BY [type]

brovish / grep_example_regex_subsr.bash

Last active December 9, 2020 08:50

example to extract substrings with grep

	##example to extract substrings with grep
	grep -Poe '\[SWIFT BIC CODE\].?\[\|BIC\/SWIFT.?\[\|$BIC$\/SWIFT.?\[\|$BIC$ \/ SWIFT.?\[' list_full.csv \| wc -l
	##Grep everything before a specific character [duplicate]
	awk -F":" '{print $1}' < inputfile

brovish / rule_for_type.sql

Created August 22, 2020 12:46

Create rule for type

	EXEC sp_addtype complex, 'varchar(50)'
	CREATE rule complex_valid
	AS
	-- check if last character is 'i'
	RIGHT(RTRIM(@cx), 1) = 'i' AND

	-- check if the real part exists and is numeric
	IsNumeric(LEFT(LTRIM(@cx),
	PATINDEX('%_[-+]%', LTRIM(@cx)))) = 1 AND

brovish / example_get_every_5_row.sql

Created August 22, 2020 15:56

Every fifth order entitles your customer to a discount. The amount of the discount is based upon the total amount of the four orders leading up to the fifth order.

	-- Listing 12-16: Creating a Temporary Table of Order Totals for a Customer
	SELECT IDENTITY(INT, 1, 1) AS Sequence,
	O.OrderID,
	SUM(OD.Quantity * OD.UnitPrice) AS Total
	INTO #Totals
	FROM Orders AS O
	JOIN [Order Details] AS OD
	ON OD.Orderid = o.OrderID
	WHERE O.CustomerID = 'SAVEA'
	GROUP BY

brovish / Deleting_Duplicates.sql

Created August 22, 2020 17:58

Deleting Duplicates with temp table and join (add identity column)

	-- Listing 12-23: Removing the Duplicates
	DELETE d
	FROM Dupes AS D
	JOIN #Singles AS S
	ON S.ID = D.ID
	AND S.Txt = D.Txt

	-- Listing 12-24: Inserting the Former Duplicates
	INSERT Dupes
	SELECT *

brovish / How to use Pivot with RowNumber and date.sql

Last active June 16, 2021 20:19

Extra SQL cases

	select date, CurrentMeterSNID,
	[1] = case when rownum2 = 1
	then reading
	else lead(reading) over(partition by CurrentMeterSNID order by date)
	end,
	[2] = case when rownum2 = 2
	then reading
	else lead(reading) over(partition by CurrentMeterSNID order by date)
	end
	from INF_Facility_ElectricalRecord

brovish / hierarchies_example.sql

Last active August 30, 2020 11:56

1 table hierarchie example

	/************************************************************
	* Code formatted by SoftTree SQL Assistant © v11.0.35
	* Time: 8/30/2020 2:54:20 PM
	************************************************************/

	-- Listing 16-8: Schema Creation Script for the New Employees Table
	IF OBJECT_ID('Employees', 'U') IS NOT NULL
	DROP TABLE Employees;
	GO

brovish / multi_purpose_sp.sql

Created August 31, 2020 22:52

Building the ultimate multipurpose procedure

	CREATE PROC [dbo].[GetMemberInfoParam]
	(
	@member_no INT = NULL,
	@Lastname VARCHAR(15) = NULL,
	@Firstname VARCHAR(15) = NULL,
	@MiddleInitial letter = NULL,
	@EmailAddress VARCHAR(128) = NULL,
	@Region_no numeric_id = NULL,
	@Member_code status_code = NULL
	)

brovish / ProcedurePlanState.sql

Created September 2, 2020 13:37

ProcedurePlanState sp Plan cache attributes and execute state

	IF OBJECT_ID('[dbo].[ProcedurePlanState]') IS NOT NULL
	DROP PROCEDURE [dbo].[ProcedurePlanState];
	GO

	CREATE PROCEDURE [dbo].[ProcedurePlanState]
	(@SchemaName sysname,
	@ObjectName sysname)
	AS
	SELECT
	ISNULL(db_name([ps].[database_id]), 'resourcedb')

brovish / create_database_snapshot.sql

Created September 5, 2020 17:45 — forked from EitanBlumin/create_database_snapshot.sql

Easily create a database snapshot for a given database using T-SQL


	DECLARE @CurrDB SYSNAME = 'MyDBName'

	DECLARE @CMD NVARCHAR(MAX);

	SELECT @CMD = ISNULL(@CMD + N',
	', N'') + N'(NAME = ' + QUOTENAME(name) + N'
	, FILENAME = ' + QUOTENAME(LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1)
	+ DB_NAME(database_id) + '_' + REPLACE(NEWID(),'-','') + '.ss', N'''')
	+ N')'

Puyl brovish