Skip to content

Instantly share code, notes, and snippets.

View peschkaj's full-sized avatar

Jeremiah Peschka peschkaj

79 followers · 24 following
View GitHub Profile
@peschkaj
peschkaj / convert-01.sql
Last active November 29, 2015 21:42
CREATE TABLE conversion (
from VARCHAR(50) NOT NULL ,
to VARCHAR(50) NOT NULL ,
conversion_factor DECIMAL (38, 10) NOT NULL
) ;
ALTER TABLE conversion
ADD CONSTRAINT pk_conversion
PRIMARY KEY (from, to) ;
@peschkaj
peschkaj / converting-measurements.sql
Created November 9, 2015 15:28
Converting units with TSQL
IF OBJECT_ID('conversions') IS NOT NULL
DROP TABLE conversions;
IF OBJECT_ID('measurements') IS NOT NULL
DROP TABLE measurements;
CREATE TABLE conversions
(
convert_from INT,
convert_to INT,
@peschkaj
peschkaj / sample-json.json
Last active October 11, 2015 17:45
Sample JSON from SQL Server 2016
{
"orders": [
{
"number": "SO43659",
"due_date": "2011-06-12T00:00:00",
"ship_date": "2011-06-07T00:00:00",
"po_number": "PO522145787",
"total_due": 23153.2339,
"freight": 616.0984,
"items": [
@peschkaj
peschkaj / fk.sql
Created May 22, 2015 16:04
Testing foreign key effects on insert performance
USE testkeys;
GO
CREATE TABLE parent (
id INT NOT NULL,
filler CHAR(96) DEFAULT (REPLICATE('A', 96))
);
ALTER TABLE dbo.parent
ADD CONSTRAINT ParentPK PRIMARY KEY CLUSTERED (id);
@peschkaj
peschkaj / fabulous-query.sql
Last active August 29, 2015 14:18
A faboulously awful query
IF OBJECT_ID('tempdb..#recent_votes') IS NOT NULL
TRUNCATE TABLE #recent_votes;
ELSE
CREATE TABLE #recent_votes (
PostId INT PRIMARY KEY,
UserId INT,
VoteWeight INT
);
IF OBJECT_ID('tempdb..#posts') IS NOT NULL
@peschkaj
peschkaj / zsh-platform-detect
Created March 25, 2015 03:22
if [[ `uname` == 'Linux' ]]
then
export LINUX=1
export GNU_USERLAND=1
else
export LINUX=
fi
if [[ `uname` == 'Darwin' ]]
then
@peschkaj
peschkaj / keybase.md
Created March 7, 2015 02:51

Keybase proof

I hereby claim:

  • I am peschkaj on github.
  • I am peschkaj (https://keybase.io/peschkaj) on keybase.
  • I have a public key whose fingerprint is FC22 6FC0 6E2F CBA7 2E59 1716 9BDB 3B7E 5BDC F2F8

To claim this, I am signing this object:

@peschkaj
peschkaj / bad_query_80374.sql
Last active August 29, 2015 14:07
WITH rando
AS ( SELECT 1 + ABS(CHECKSUM(NEWID())) % ( 4 ) AS RandomNumber
FROM master.dbo.spt_values sv
WHERE sv.type = 'P'
AND sv.number < 4
)
SELECT r.RandomNumber ,
d.number
FROM rando AS r
INNER JOIN master.dbo.spt_values d ON r.RandomNumber = d.number
@peschkaj
peschkaj / find-the-cost.sql
Last active August 29, 2015 14:06
Find the cost and memory grant of queries in the plan cache
SELECT qs.query_plan_hash ,
query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
sum(//p:StmtSimple[xs:hexBinary(substring(@QueryHash, 3)) = xs:hexBinary(sql:column("qs.query_hash"))]/@StatementSubTreeCost)',
'float') AS cost ,
query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
sum(//p:StmtSimple[xs:hexBinary(substring(@QueryHash, 3)) = xs:hexBinary(sql:column("qs.query_hash"))]/p:QueryPlan/p:MemoryGrantInfo/@SerialDesiredMemory)', 'float') AS serial_desired_memory,
query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004
/07/showplan";
sum(//p:StmtSimple[xs:hexBinary(substring(@QueryHash, 3)) = xs:hexBinary(sql:column("qs.query_hash"))]/p:QueryPlan/p:MemoryGrantInfo/@SerialRequiredMemory)', 'float') AS serial_required_memory,
*
@peschkaj
peschkaj / ohgodno.txt
Created May 26, 2014 22:49
Begin selectivity computation
Input tree:
LogOp_Select
CStCollBaseTable(ID=1, CARD=19614 TBL: Person.Address)
ScaOp_Comp x_cmpEq