bertwagner’s gists

bertwagner / SQLInjectionExecute1.sql

Created August 25, 2017 21:38

EXEC dbo.sp_GetFullName 'TFly37'

bertwagner / dbo.sp_GetFullName.sql

Last active August 25, 2017 21:32

	CREATE PROCEDURE dbo.sp_GetFullName
	@ParmUserName varchar(100)
	AS
	BEGIN
	DECLARE @FullQuery varchar(1000)
	SET @FullQuery = 'SELECT FullName FROM dbo.RegisteredUser WHERE UserName = ''' + @ParmUserName + ''''

	EXEC(@FullQuery);
	END

bertwagner / SQL Injection data.sql

Last active September 8, 2017 00:40

	USE Sandbox;
	GO
	DROP TABLE IF EXISTS dbo.RegisteredUser
	CREATE TABLE dbo.RegisteredUser
	(
	Id INT IDENTITY(1,1),
	FullName varchar(100),
	UserName varchar(40),
	HashedPassword varchar(66),
	JoinDate datetime2

bertwagner / CoffeeData for SARGable queries.sql

Last active August 19, 2017 13:21

	-- Test data taken from my favorite green coffee seller's website, Sweet Maria's https://www.sweetmarias.com/category/green-coffee
	--DROP TABLE IF EXISTS dbo.CoffeeInventory

	DROP TABLE dbo.CoffeeInventory
	CREATE TABLE dbo.CoffeeInventory
	(
	ID int IDENTITY(1,1),
	Name VARCHAR(100),
	Price DECIMAL(4,2),
	Description VARCHAR(500),

bertwagner / Non-SARGable queries.sql

Created August 19, 2017 13:04

	-- From https://github.com/bertwagner/SQLServer/blob/master/Non-SARGable%20Execution%20Plans.sql
	-- This script will check the execution plan cache for any queries that are non-SARGable.
	-- It does this by finding table and index scans that contain a scalar operators

	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

	DECLARE @dbname SYSNAME
	SET @dbname = QUOTENAME(DB_NAME());

	WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

bertwagner / SkewedCoffeeData.sql

Last active August 5, 2017 11:49

Coffee data

	USE Sandbox
	GO
	-- Test data taken from my favorite green coffee seller's website, Sweet Maria's https://www.sweetmarias.com/category/green-coffee
	DROP TABLE IF EXISTS dbo.CoffeeInventory
	CREATE TABLE dbo.CoffeeInventory
	(
	ID int IDENTITY(1,1),
	Name VARCHAR(100),
	Price DECIMAL(4,2),
	Description VARCHAR(500),

bertwagner / CoffeeData.sql

Created July 27, 2017 21:00

Coffee Data taken from Sweet Maria's homepage

	-- Test data taken from my favorite green coffee seller's website, Sweet Maria's https://www.sweetmarias.com/category/green-coffee
	DROP TABLE IF EXISTS dbo.CoffeeInventory
	CREATE TABLE dbo.CoffeeInventory
	(
	ID int IDENTITY(1,1),
	Name VARCHAR(100),
	Price VARCHAR(5),--DECIMAL(4,2),
	Description VARCHAR(500),
	CreateDate DATETIME2 DEFAULT GETDATE()
	)

bertwagner / Create Car Inventory Data.sql

Created June 17, 2017 11:50

Dataset for demos of how to solve 5 business problems with temporal tables https://medium.com/@bertwagner/5-business-problems-you-can-solve-with-temporal-tables-363255ed3a8c

	-- Drop and create our temporal and historical tables
	IF OBJECT_ID('dbo.CarInventory', 'U') IS NOT NULL
	BEGIN
	-- When deleting a temporal table, we need to first turn versioning off
	ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = OFF )
	DROP TABLE dbo.CarInventory
	DROP TABLE dbo.CarInventoryHistory
	END;
	CREATE TABLE CarInventory
	(

bertwagner / JSON performance test redux dotnet.cs

Created June 3, 2017 21:20

This file has been truncated, but you can view the full file.

	using Newtonsoft.Json;
	using System;
	using System.Collections.Generic;
	using System.Data.SqlClient;
	using System.Diagnostics;
	using System.IO;
	using System.Linq;
	using System.Text;
	using System.Threading.Tasks;
	using System.Xml.Serialization;

bertwagner / JSON Performance Tests redux.sql

Created June 3, 2017 20:55

This file has been truncated, but you can view the full file.

	-- Create our test data: A table of a car dealer's inventory of cars
	-- car data source: https://github.com/arthurkao/vehicle-make-model-data
	DROP TABLE IF EXISTS dbo.AllCarsInOneRow
	CREATE TABLE dbo.AllCarsInOneRow
	(
	Id INT IDENTITY(1,1),
	CarDetails NVARCHAR(MAX)
	);
	DROP TABLE IF EXISTS dbo.XmlVsJson
	CREATE TABLE dbo.XmlVsJson

Bert Wagner bertwagner