Skip to content

Instantly share code, notes, and snippets.

Avatar
❤️

Brent Ozar BrentOzar

❤️
View GitHub Profile
View UsersMemberships.sql
USE StackOverflow;
GO
DROP TABLE IF EXISTS dbo.UsersMemberships;
CREATE TABLE dbo.UsersMemberships
(Id INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL,
StartDate DATETIME NOT NULL,
EndDate DATETIME NOT NULL,
CancelledEarlyDate DATETIME NULL);
View An Introduction to SQL Server Statistics with Playing Cards
/* SQL Server Statistics Explained with Playing Cards
v0.1 - 2020-08-14
https://BrentOzar.com/go/learnstats
This first RAISERROR is just to make sure you don't accidentally hit F5 and
run the entire script. You don't need to run this:
*/
RAISERROR(N'Oops! No, don''t just hit F5. Run these demos one at a time.', 20, 1) WITH LOG;
GO
View Fun With ASCII
CREATE DATABASE [٠০౦০٠];
GO
USE [٠০౦০٠];
GO
CREATE SCHEMA [٠০౦০٠];
GO
CREATE TABLE [٠০౦০٠].[٠০౦০٠]([٠০౦০٠] NVARCHAR(20), [۰০౦০٠] NVARCHAR(20), [٠০౦০۰] NVARCHAR(20), [۰০౦০۰] NVARCHAR(20));
GO
CREATE UNIQUE CLUSTERED INDEX [٠০౦০٠] ON [٠০౦০٠].[٠০౦০٠]([٠০౦০٠], [۰০౦০٠], [٠০౦০۰], [۰০౦০۰]);
GO
@BrentOzar
BrentOzar / PlanCacheByDateAndHour.sql
Created Jul 3, 2018
Report on SQL Server's plan cache history over time.
View PlanCacheByDateAndHour.sql
/*
SQL Server Plan Cache History by Date & Time
Brent Ozar, 2018/07/03 - https://BrentOzar.com/go/plansbydate
This is free and unencumbered software released into the public domain.
Anyone is free to copy, modify, publish, use, compile, sell, or
distribute this software, either in source code form or as a compiled
binary, for any purpose, commercial or non-commercial, and by any
means.
@BrentOzar
BrentOzar / TopQueries.sql
Last active Jul 4, 2021
Some of the top StackOverflow queries from http://data.stackexchange.com. Great for demoing wide SELECT workloads.
View TopQueries.sql
USE StackOverflow;
GO
IF OBJECT_ID('dbo.usp_Q7521') IS NULL
EXEC ('CREATE PROCEDURE dbo.usp_Q7521 AS RETURN 0;')
GO
ALTER PROC dbo.usp_Q7521 @UserId INT AS
BEGIN
/* Source: http://data.stackexchange.com/stackoverflow/query/7521/how-unsung-am-i */
View SQL Server 2019 inline function bug
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
CREATE OR ALTER FUNCTION dbo.Test()
RETURNS INT AS
BEGIN
DECLARE @i BIGINT;
SELECT TOP 1 @i = CHECKSUM(*)
FROM master.dbo.spt_values;
RETURN 1;
@BrentOzar
BrentOzar / sys.index_resumable_operations.sql
Created Feb 20, 2020
Showing the (in)accuracy of sys.index_resumable_operations.
View sys.index_resumable_operations.sql
DROP TABLE IF EXISTS dbo.DiningRoomTable;
GO
CREATE TABLE dbo.DiningRoomTable (Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, Stuffing CHAR(1000));
INSERT INTO dbo.DiningRoomTable (Stuffing)
SELECT 'Stuff'
FROM sys.messages;
GO
CREATE INDEX IX_Stuffing ON dbo.DiningRoomTable(Stuffing)
WITH (ONLINE = ON, RESUMABLE = ON);
@BrentOzar
BrentOzar / How to Make SELECT COUNT(*) Queries Crazy Fast.sql
Created Dec 22, 2019
How to Make SELECT COUNT(*) Queries Crazy Fast.sql
View How to Make SELECT COUNT(*) Queries Crazy Fast.sql
USE StackOverflow;
GO
DropIndexes;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO
/* Check the table's size: */
sp_BlitzIndex @TableName = 'Votes';
GO
View Branching proc.sql
CREATE PROCEDURE dbo.GetUnshippedOrders @IsShipped bit
AS
BEGIN
SET NOCOUNT ON;
IF @IsShipped = 1
EXEC dbo.GetUnshippedOrders_UsesFilteredIndex
ELSE
SELECT OrderID, OrderDate FROM dbo.Orders WHERE IsShipped = @IsShipped;
END
GO
@BrentOzar
BrentOzar / sp_Blitz_as_script.sql
Created Dec 2, 2019
Long query without the create proc
View sp_Blitz_as_script.sql
DECLARE
@Help TINYINT = 0 ,
@CheckUserDatabaseObjects TINYINT = 1 ,
@CheckProcedureCache TINYINT = 0 ,
@OutputType VARCHAR(20) = 'TABLE' ,
@OutputProcedureCache TINYINT = 0 ,
@CheckProcedureCacheFilter VARCHAR(10) = NULL ,
@CheckServerInfo TINYINT = 0 ,
@SkipChecksServer NVARCHAR(256) = NULL ,
@SkipChecksDatabase NVARCHAR(256) = NULL ,