bonskijr’s gists

bonskijr / test_clonedb_identity_retention.sql

Created June 10, 2020 05:49

This script will confirm that DBCC CLONEDATABASE, which uses database snapshot but without data, still retains the next sequence of identity and SEQUENCE value

	use master
	go

	create database OriginalDB
	go

	use OriginalDB
	go

	create table SeededTable(id int identity, name nvarchar(20))

bonskijr / steps.sh

Last active May 31, 2020 09:18

wsl2 setting up

	## update OS
	sudo apt-get update
	sudo apt-get upgrade

	## innstall docker
	## https://gist.github.com/AlexanderAllen/48436790b793f710c8c4e6edf3637891#file-install_docker_ubuntu_wsl-sh
	## https://medium.com/@callback.insanity/using-docker-with-windows-subsystem-for-linux-wsl-on-windows-10-d2deacad491f
	# Update the apt package list.
	sudo apt-get update -y

bonskijr / updatefrom_repro.sql

Last active April 29, 2020 01:24

reprod script to see if UPDATE FROM is updating on order

	USE cauldron
	go
	CREATE TABLE __header ( ID int, name nvarchar(10))
	GO

	CREATE TABLE __detail ( ID int, headerID int, detailName nvarchar(50), updated timestamp -- this will generate an incrementing timestamp on update)
	GO


	INSERT INTO dbo.__header

bonskijr / pathcompactpath.vb

Last active March 24, 2020 11:35

PathCompactPath: Adding Ellipses to a File Path

	' original http://vbnet.mvps.org/index.html?code/fileapi/pathcompactpath.htm
	Option Explicit
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Copyright ©1996-2011 VBnet/Randy Birch, All Rights Reserved.
	' Some pages may also contain other copyrights by the author.
	''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	' Distribution: You can freely use this code in your own
	' applications, but you may not reproduce
	' or publish this code on any web site,
	' online service, or distribute as source

bonskijr / sql_to_json.sql

Last active January 30, 2020 15:06

Sample T-SQL to json array

	CREATE TABLE #Person ( ID INT IDENTITY PRIMARY KEY, Name NVARCHAR(100))
	GO
	CREATE TABLE #Pets ( PetsID INT IDENTITY PRIMARY KEY, PetName NVARCHAR(100))
	GO
	CREATE TABLE #PersonPets ( PersonID INT , PetsID INT)
	GO

	INSERT INTO #Person
	(
	Name

bonskijr / sample_item.csv

Last active June 13, 2019 14:48

sample csv file for SSMS 18.1 Flat File Import Error

Item Code	Item Description	Old Location Code	New Location Code	Purpose
F031	MILK FULL CREAM EMBORG 1L/12BOX CASE	FOOD	FS01	No location yet
F003	SODA SPRITE 330ML/24CAN CASE	FS2	FS04	Transfer Location

bonskijr / randomise_quote.vb

Last active December 10, 2018 15:45

throwaway quote randomiser for PD group

	'--- this code is a direct translation of a C# to VB.NET (http://converter.telerik.com/) since I can no longer write a sane VB code ;)
	Public Partial Class Form1
	Inherits Form

	'cache our quotes
	Private _quotes As List(Of Quotes) = New List(Of Quotes)()
	'track the current quote id
	Private _currId As Integer = 0

	Public Sub New()

bonskijr / GB_check_stat

Created December 3, 2018 01:59

Glenn berry check statistics

	SELECT o.name, i.name AS [Index Name],
	STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date],
	s.auto_created, s.no_recompute, s.user_created
	FROM sys.objects AS o WITH (NOLOCK)
	INNER JOIN sys.indexes AS i WITH (NOLOCK)
	ON o.[object_id] = i.[object_id]
	INNER JOIN sys.stats AS s WITH (NOLOCK)
	ON i.[object_id] = s.[object_id]
	AND i.index_id = s.stats_id
	WHERE o.[type] = 'U'

bonskijr / 906144518575198209.sql

Last active September 8, 2017 13:53

sample temp table


	SELECT row_number() over (order by (select null)) as RowId,
	FileName,
	ItemId
	INTO #result
	FROM cm_steps

	DECLARE
	@i int, @max int,
	@parentId int,

bonskijr / Setup.Event.sql

Last active August 10, 2017 09:25

Trace Deadlocks and Blocked process

	-- configure server first
	EXEC sp_configure ‘show advanced options’, 1 ;
	GO
	RECONFIGURE ;
	GO
	/* Enabled the blocked process report */
	EXEC sp_configure 'blocked process threshold', '10';
	RECONFIGURE
	GO
	/* Start the Extended Events session */

bonskijr bonskijr