BrentOzar

/*
Watch Brent Tune Queries: Misleading Stored Proc Edition
Brent Ozar - v1.0 - 2024-11-08

https://BrentOzar.com/go/watch

This demo requires:
* Any version of SQL Server, but I use SQL Server 2022
	running in 2022 compat level. If you want to use
	an earlier version, your plans will vary.

BrentOzar

/*
Watch Brent Tune Queries: Top Posts From Top Locations
v1.2 - 2024-09-07
https://www.BrentOzar.com/go/tunequeries
 
 
This demo requires:
* Any supported version of SQL Server
* A large Stack Overflow database: https://www.BrentOzar.com/go/querystack
 

BrentOzar

DECLARE @NumberOfLayers INT = 1000;

SELECT 0 AS Ordered, 'DECLARE @t TABLE (Id INT PRIMARY KEY CLUSTERED);'
UNION
SELECT 1 AS Ordered, 'WITH CTE1 AS (SELECT * FROM @t t1)'
UNION
SELECT value AS Ordered, ', CTE' + CAST(value AS VARCHAR(10)) + ' AS (SELECT cA.* ' +
  ' FROM CTE' + CAST(value - 1 AS VARCHAR(10)) + ' cA INNER JOIN ' +
  ' CTE' + CAST(value - 1 AS VARCHAR(10)) + ' cB ON cA.Id = cB.Id) '
FROM GENERATE_SERIES(2,@NumberOfLayers)

BrentOzar

/* What sent to ChatGPT 4: */

You are a T-SQL database developer working with Microsoft SQL Server 2019. Given these two tables:

CREATE TABLE [dbo].[Comments](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CreationDate] [datetime] NOT NULL,
	[PostId] [int] NOT NULL,
	[Score] [int] NULL,
	[Text] [nvarchar](700) NOT NULL,

BrentOzar

USE [StackOverflow]
GO
/****** Object:  Table [dbo].[Badges]    Script Date: 10/21/2022 12:48:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Badges]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Badges](

BrentOzar

/* 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

BrentOzar

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

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

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);

BrentOzar

USE StackOverflow;
GO
DropIndexes;
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140;
GO

/* Check the table's size: */
sp_BlitzIndex @TableName = 'Votes';
GO