Skip to content

Instantly share code, notes, and snippets.

View nchammas's full-sized avatar

Nicholas Chammas nchammas

82 followers · 29 following
View GitHub Profile
@nchammas
nchammas / default_language.sql
Created August 30, 2011 17:48
Recursive T-SQL CTE to find root of arbitrarily deep hierarchy
-- schema
--DROP TABLE dbo.common_Text;
--DROP TABLE dbo.common_LanguageType;
--DROP TABLE dbo.common_Comment;
CREATE TABLE dbo.common_Comment (
CommentId BIGINT NOT NULL PRIMARY KEY
);
CREATE TABLE dbo.common_LanguageType (
@nchammas
nchammas / simple_bank.sql
Created September 9, 2011 22:27
Simple bank schema in T-SQL
-- required SET options for indexed view
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
SET ARITHABORT ON;
--
@nchammas
nchammas / checksum_collisions.sql
Created September 30, 2011 18:33
SQL Server CHECKSUM() Collisions
-- The gist of it:
-- Use HASHBYTES('SHA1', @input); instead of CHECKSUM(@input); if you are interested
-- in using hashes to detect code changes.
-- oops! I deleted too many records
DECLARE @old_proc_definition VARCHAR(MAX) = '
DELETE FROM dbo.transactions
WHERE txn_id < 10000000000000000;
';
@nchammas
nchammas / identity overflow check.sql
Created October 13, 2011 21:13
SQL Server IDENTITY overflow check
-- Author: Nicholas Chammas
-- Turbine / WB Games
-- 2011/10/13
-- Inspired by: http://vyaskn.tripod.com/sql_server_check_identity_columns.htm
-- Purpose: Find all IDENTITY columns on all databases on this instance
-- and show how much of their range they have exhausted.
--
-- CAVEATS
--
-- 1. Developed using SQL Server 2008 syntax.
@nchammas
nchammas / TRUNCATE and DROP are both minimally logged.sql
Created November 8, 2011 21:32
Demonstration that TRUNCATE and DROP are logged and just as fast as one another.
SET NOCOUNT ON;
USE [tempdb];
CREATE TABLE a_farting_farthing (
an_integer INT DEFAULT (1)
);
INSERT INTO a_farting_farthing
DEFAULT VALUES;
@nchammas
nchammas / child_parent_update_locking_demo.sql
Created March 23, 2012 15:52
Updating child row does not lock parent row in SQL Server
-- Tested on SQL Server 2008 R2
-- Isolation level is READ COMMITTED
--
-- Session 1
--
CREATE TABLE dbo.parent (
parent_id INT NOT NULL PRIMARY KEY
, value VARCHAR(50) NOT NULL
);
@nchammas
nchammas / foldable date functions.sql
Created May 29, 2012 05:24
Date functions like GETDATE() and SYSUTCDATETIME() are folded by the optimizer.
SET NOCOUNT ON;
USE [tempdb];
GO
-- Try substituting GETDATE() for SYSUTCDATETIME() everywhere in this script
-- and you will get the same results.
CREATE TABLE a_table (
an_integer INT DEFAULT (1)
, a_string CHAR(10) DEFAULT ('ohmigodwut')
@nchammas
nchammas / timer.sh
Last active August 29, 2015 14:08 — forked from esoupy/Timer.sh
A simple timer / stopwatch implemented as a bash function.
#!/bin/bash
# Display the duration between timer start and stop.
#
# Usage: timer {start|stop} [format]
#
# Example:
#
# timer start
# <script_commands>
@nchammas
nchammas / centos-spark-setup.sh
Created January 21, 2015 03:05
CentOS-based Spark development image
yum install -y java-1.7.0-openjdk-devel gcc gcc-c++ ant git
yum install -y wget tar unzip time
yum install -y python-devel
curl https://bitbucket.org/pypa/setuptools/raw/bootstrap/ez_setup.py | python -
curl https://raw.github.com/pypa/pip/master/contrib/get-pip.py | python -
easy_install pip
@nchammas
nchammas / February 2015 Spark Meetup Notes.md
Created February 24, 2015 05:06
Nick Chammas

Boston Spark February 2015 Meetup

Boston Spark Community Updates

  • This is Event #3 for this Meetup.
  • 450+ members; Meetup started in May 2014.
  • New talk format: Lightning Talks
    • 30 seconds to 5 minutes maximum.
    • Any topic relating to Spark.
    • Any skill or experience level--beginners welcome!
  • Speakers: We want you!