SQL Server Cheat Sheet

CREATE TABLE dbo.orders (
  id BIGINT IDENTITY(1,1) PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

EXEC sp_rename 'dbo.orders', 'customer_orders';

EXEC sp_rename 'dbo.orders.total', 'amount', 'COLUMN';

SELECT * INTO dbo.orders_backup FROM dbo.orders;

ALTER TABLE dbo.orders ADD status VARCHAR(20) NOT NULL DEFAULT 'new';

ALTER TABLE dbo.orders DROP COLUMN status;

ALTER TABLE dbo.orders ALTER COLUMN total DECIMAL(12,2) NOT NULL;

TRUNCATE TABLE dbo.orders;

DROP TABLE IF EXISTS dbo.orders_backup;

EXEC sp_help 'dbo.orders';

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'orders';

DBCC CHECKIDENT ('dbo.orders', RESEED, 0);

SELECT id, total INTO #recent FROM dbo.orders WHERE created_at > DATEADD(day, -7, SYSUTCDATETIME());

INSERT INTO dbo.orders (customer_id, total)
VALUES (1, 49.90), (2, 12.00);

INSERT INTO dbo.orders (customer_id, total)
OUTPUT INSERTED.id
VALUES (1, 49.90);

UPDATE dbo.orders SET status = 'shipped' WHERE id = 42;

UPDATE o
SET o.total = t.amount
FROM dbo.orders o
JOIN dbo.order_totals t ON t.order_id = o.id;

DELETE o
FROM dbo.orders o
JOIN dbo.customers c ON c.id = o.customer_id
WHERE c.is_test = 1;

DELETE TOP (5000) FROM dbo.orders
WHERE created_at < DATEADD(year, -2, SYSUTCDATETIME());

MERGE dbo.orders AS t
USING (VALUES (1, 49.90)) AS s (id, total) ON t.id = s.id
WHEN MATCHED THEN UPDATE SET t.total = s.total
WHEN NOT MATCHED THEN INSERT (id, total) VALUES (s.id, s.total);

SELECT email, COUNT(*)
FROM dbo.customers
GROUP BY email
HAVING COUNT(*) > 1;

WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM dbo.customers
)
DELETE FROM ranked WHERE rn > 1;

SELECT TOP (50) * FROM dbo.orders ORDER BY created_at DESC;

SELECT * FROM dbo.orders
ORDER BY created_at DESC
OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY;

CREATE OR ALTER VIEW dbo.recent_orders AS
SELECT * FROM dbo.orders WHERE created_at > DATEADD(day, -30, SYSUTCDATETIME());

SELECT CASE WHEN total > 100 THEN 'large' ELSE 'small' END AS bucket
FROM dbo.orders;

SELECT COALESCE(nickname, first_name, 'there') FROM dbo.customers;

SELECT GETDATE(), SYSUTCDATETIME(), CAST(GETDATE() AS date);

SELECT DATEADD(day, 7, created_at)                AS due,
       DATEDIFF(hour, created_at, delivered_at)  AS hours
FROM dbo.orders;

SELECT FORMAT(created_at, 'yyyy-MM-dd'),   -- readable, slow in bulk
       CONVERT(varchar(10), created_at, 23) -- fast
FROM dbo.orders;

SELECT DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1) AS month, COUNT(*)
FROM dbo.orders GROUP BY DATEFROMPARTS(YEAR(created_at), MONTH(created_at), 1);

SELECT CONCAT(first_name, ' ', last_name) AS name,   -- NULLs become ''
       CONCAT_WS(', ', city, country)     AS location -- 2017+
FROM dbo.customers;

SELECT customer_id, STRING_AGG(sku, ', ') WITHIN GROUP (ORDER BY sku)
FROM dbo.order_items GROUP BY customer_id;

SELECT created_at, SUM(total) OVER (ORDER BY created_at) AS running_total
FROM dbo.orders;

SELECT s.name AS [schema], t.name AS [table]
FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id
ORDER BY 1, 2;

CREATE NONCLUSTERED INDEX ix_orders_created_at ON dbo.orders (created_at);

CREATE NONCLUSTERED INDEX ix_orders_status
  ON dbo.orders (status) INCLUDE (total, created_at);

SELECT i.name, i.type_desc, i.is_unique
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.orders');

ALTER INDEX ix_orders_created_at ON dbo.orders REBUILD;

EXEC sp_spaceused 'dbo.orders';

SELECT session_id, status, wait_type, total_elapsed_time, text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)
WHERE session_id <> @@SPID;

KILL 63;

GRANT SELECT ON SCHEMA::dbo TO analyst;

SELECT @@VERSION, DB_NAME(), SUSER_SNAME();