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

BrentOzar

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

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 ,

BrentOzar

SELECT msm.*, hbp.*, pmem_in_use_kb = pm.physical_memory_in_use_kb, large_pages_kb = pm.large_page_allocations_kb,
        locked_pages_kb = pm.locked_page_allocations_kb, commit_limit_kb = available_commit_limit_kb,
        tot_mem_kb = sm.total_physical_memory_kb, [version] = @@VERSION,
        sm.available_physical_memory_kb
FROM sys.dm_os_process_memory pm
CROSS JOIN sys.dm_os_sys_memory sm
CROSS JOIN (SELECT max_server_mem = value_in_use FROM sys.configurations
            WHERE NAME = 'max server memory (MB)') msm
CROSS JOIN (SELECT hybrid_buffer_pool = value_in_use FROM sys.configurations
            WHERE NAME = 'hybrid_buffer_pool') hbp