TSQL - Cheat sheet

-- Show databases and info
    SELECT * FROM sys.DATABASES
    sp_configure;
    sp_help 'tablename';
    sp_helpfile;
    sp_helpIndex 'tablename';

-- Help
    DBCC HELP('checkdb');
    DBCC HELP ('?');

    -- Managing blocked processes
    sdf -- Considered replacement for sys.sysprocesses
    SELECT * FROM sys_sysprocesses WHERE blocked > 0;
    SELECT * FROM sys.dm_tran_locks; -- Look for request_session_id
    DBCC INPUTBUFFER(56); -- 56 is spid
    --BOL is books online

-- Killing processes
    KILL 1234; -- 1234 is spid from select * from sysprocesses or

-- Index usage stats
    SELECT object_name(object_id) AS TableName, * FROM sys.dm_db_index_usage_stats
    WHERE db_name(database_id) = 'databasebane'

    -- Fragmentation percent
    SELECT object_name(object_id) AS TableName, * FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, NULL);
    -- Params are dbid (db), objectid (table), indexid (index), mode
    -- e.g.
    SELECT object_name(object_id) AS TableName, * FROM sys.dm_db_index_physical_stats(DB_ID('dbname'), NULL, NULL, NULL, NULL);


-- Fixing fragmentation (Rebuild requires table lock, reorg usespage locks)
    ALTER INDEX ALL ON dbo.TableName
    REBUILD -- Completely rebuilds index from scratch
    --REORG rearranges pages
    WITH(ONLINE = ON) --

    -- Fillfactor is amount of pages full

--- Create non cluster index (non for non clustered cl for clustered)
    -- Note you can write click on 'Missing Index' when "Execution Plan" is enabled

    CREATE NONCLUSTERED INDEX nonColumnName ON dbo.dbname(columnname);
    CREATE CLUSTERED UNDEX clColumnName ON dbo.dbname(columnname);

    -- Destroy index
    DROP INDEX tablename.nonColumnName;

-- OS
    SELECT * FROM sys.dm_os_performance_counters;
    SELECT * FROM sys.dm_os_sys_info;
    SELECT * FROM sys.dm_os_windows_info;

    -- Get process
    USE master
    GO
        SELECT * FROM sysprocesses (nolock) WHERE blocked = 0 AND spid IN (
        SELECT blocked FROM sysprocesses (nolock) WHERE blocked <> 0
    )
    GO

-- Files and Extensions
    --MDF -  Master Data File
    --NDF - Non master data file
    --LDF - Log data file
    CREATE DATABASE DB!
    ON PRIMARY
    (
        NAME = DB1Data1,
        FILENAME = 'C:\MSSQLDB\DB1Data1.mdf',
        SIZE = 10MB
    ),
    FILEGROUP fgCurrent
    (
        NAME = DB1Data2,
        FILEGROUP = 'D:\MSSQLDB\DB1Data2.ndf',
        SIZE = 10MB
    ),
    (
        NAME = DB1Data3,
        FILENAME = 'D:\MSSQLDB\DBData3.ndf'
        SIZE = 10MB
    )
    LOG ON
    (
        NAME = DB1Log1,
        FILENAME = 'E:\MSSQLDB\DB1Log1.ldf',
        SIZE = 10MB
    )

-- Filegroups
    ALTER DATABASE dbname ADD FILEGROUP fgname

    -- Add file
    ALTER DATABSE dbname ADD FILE (NAME = N'newfile', SIZE 10MB, FILENAME = N'D:\MSSQLDB\FG3.ndf');

    --  Create table on filegrtoup
    CREATE TABLE T1 (Col1 INT) ON fgCurrent;

    SELECT * FROM sys.filegroups;

    SELECT * FROM sys.database_files;
    SELECT * FROM sys.data_spaces;
    --(data_space_id)

    --Note ds and df are aliased from above
    SELECT df.file_id, df.name, ds.name AS Filegroup
    FROM sys.data_spaces df
    INNER JOIN sys.data_spaces ds
    ON df.data_spaces_id = ds.data_spaces_id

    --All files on server
    SELECT * FROM sys.master_files

    --

-- SERVER SIDE TRACE (SST)
    SELECT * FROM sys.traces;

    --File->Export->Trace Definition ()
    --(export: sp_completed and statement_completed) Remember to add filename

-- Auditing SQL Server
    -- Right click audit -> select 'New Audit'

-- Backup
    -- RPO Recovery Point Objective (How much data can you afford to lose)
    -- RTO Recovery Time Objective  (How long can you afford to be down)

    -- Full Backup Including options
    BACKUP DATABASE dbname
    TO DISK = 'c:\backup\dbname.bak',
    WITH INIT, -- optional prevents concatenating backups to single file and ovverides existing backup
    FORMAT, -- optional forces backup even if existing file has corrupted header (reformatted)
    STATS = 10; -- optional show stats every 10 perent

    -- Log backups
    BACKUP LOG dbname
    TO DISK = 'c:\backup\dbnamelog1.trn';

    -- Differential Backups (Requires full backup)
    BACKUP DATABASE dbname
    TO DISK = 'c:\backup\dbname.bak',
    WITH INIT,
    FORMAT,
    STATS = 10,
    DIFFERENTIAL;

    -- Filegroup backups (Can add multiple file groups)
    BACKUP DATABASE dbname
    FILEGROUP = 'FG1'
    TO DISK = 'c:\backup\dbname.bak',
    WITH INIT,
    FORMAT,
    STATS = 10;

    -- System backup (model can be pulled from another db)
    -- Can backup master, msdb, model, tempdb with same syntax as above
    -- Same with log master, log msdb, log model, logtempdb

-- Basic system restore
    RESTORE DATABASE databasename
    FROM DISK = 'c:\backup\dbname.bak'
    --WITH REPLACE, -- Use when db already exists
    --STATS = 10

    -- Restore with move
    RESTORE DATABASE databasename
    FROM DISK = 'c:\backup\dbname.bak'
    WITH
    MOVE 'dbname' TO 'c:\newbackup\dbname.bak'

    -- Restore log backup
    RESTORE LOG databasename
    FROM DISK = 'c:\backup\dbnamelog.trn'
    WITH stats = 10, norecovery


-- INDEXES
    -- Clustered: Table is physically ordered in index order

    -- Non clustered: Value in index points to row of individual pages

    -- Index fragmentation:
        -- Internal: Fragmentation within page itself
        -- External: Pages are not located next to eachother



--TRIGGER (auto execute on trigger. Do not use)
    CREATE TRIGGER||PROCEDURE triggername
        ON dbo.TABLE
        AFTER INSERT,UPDATE,DELETE
        AS BEGIN
            PRINT 'ok!'
        END

    -- Trigger types
    -- DML: Data Manipulation Language
    -- DDL: Data Definition Language (ON DATABASE)
    -- Logon

--Stored Procedures (SP):
    CREATE  PROCEDURE GetstudentnameInOutputVariable
    (
        @studentid INT,                   --Input parameter ,  Studentid of the student
        @studentname VARCHAR (200) OUT,    -- Output parameter to collect the student name
        @StudentEmail VARCHAR (200)OUT     -- Output Parameter to collect the student email
    )
    AS
    BEGIN
        SELECT @studentname= Firstname+' '+Lastname,
        @StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
    END

    -- To execute:
    GetstudentnameInOutputVariable
    -- or EXECUTE GetstudentnameInOutputVariable;

--Synonym
    SELECT * FROM Food.dbo.snack
    --=
    CREATE SYNONYM Snack FOR Food.dbo.snack

--Drop vs Detach
    --Detach does not delete files
    --Attach:
    CREATE DATABASE DBName
    ON (FILENAME = 'C:\MyDBs\CD')
    FOR ATTACH;

--Scale Out I/O (Spread across database/server)
    --SEPERATE Data and Log Files!

--Move filegroup
ALTER DATABASE [databasename]
MODIFY FILE
(
    NAME = DB1Data2,
    FILENAME = 'C:\Newfolder\Newfile.ndf'
)

-- Recover Models
    --Simple: No log backups. Full only
    --Bulk Logged: Logs and differentials but not PiT (Point in Time)
    --Full: Adds PiT (Point in Time)

-- Options
    --Autoclose: deallocates all objects from memory
    --Autoshrink: Automatically shrinks db every 30 minutes (FRAGMENTS DB Don't USE!)
    --recovery_model_desc: See above

--Stats
    --Synchronous forces queries to wait when stats are out of date
    --Asynchronous allows queries to continie with old stats and builds new stats in bg (better methinks)
    SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on FROM sys.DATABASES

    ALTER DATABASE dbname SET auto_update_statistics_async ON

-- Restricted Access
    -- Single User: allows only one user
    -- Restricted User: Allows only db_owner, sysadmin or dbcreatot
    -- Multi User: Allows all users
    ALTER DATABASE dbname SET single_user
    ALTER DATABASE dbname SET restricted_user
    ALTER DATABASE dbname SET multi_user

-- Compression
    -- Row Level
        -- Simplest, Cheapest, Doesn't Store 0 or NULL, Changes fixed-length vars to variable-length vars
    -- Page Level
        -- More resources, adds prefix and dictionary compression
    -- Prefix Compression
        -- Looks for and replaces repeating patterns at begining of column
    -- Dictionary Compression
        -- Looks for and replaces repeating patterns anywhere on page
        sp_estimate_data_compression_savings 'dbo', 'table', NULL, NULL, 'ROW'; --OR Page

-- Buffers
    -- Clean: Unchanged buffer
    -- Dirty: Modified buffer
    dbcc dropcleanbuffers -- drops clean buffers (Don't run live)

--ShrinkDatabase and ShrinkFile (Dangerous 1: takes forever, 2: Fragments Indexes)
    DBCC SHRINKDATABASE(N'databasename', 10); --10 is target percentage

    -- Shrink file:
    DBCC SHRINKFILE('logiles', 5); -- 5 is target file size in MB

--FreeProcCache

-- CheckDB and CheckTable (Check for consitancy errors)
    DBCC CHECKDB(databasename); --(databasename) is optional uses current db by default (Look for minimum repair level)
    -- Put db in single user mode to fix
    DBCC CHECKDB(datanasename, repair_allow_data_loss); -- repair_allow_data_loss is minimum repair level

-- SQLPerf
    DBCC SQLPERF(logspace); -- Gives logspace usage for all databases --GET LOG SPACE USAGE

-- InputBuffer Shows last query spid ran spid is in () in tab eg. SqlQuery.sql (57) 57 is spid
    DBCC inputbuffer(57) -- WHERE spid is 57

-- Table Partitioning (Every table is single partitioned)
    SELECT * FROM sys.partitions WHERE OBJECT_NAME([object_id]) = 'tablename';

    CREATE PARTITION FUNCTION RangePF1(INT)
        AS RANGE LEFT FOR VALUES(1, 500, 1000); -- ranges fo into filegroups (e.g. FG1, FG2, FG3)
        GO
        CREATE PARTITION RangePF1
        TO (FG1, FG2, FG3, FG4) -- FG4 is used to hold all data above 1000

    CREATE CLUSTERED INDEX clustID ON dbo.tablename(ID) -- ID is column
    ON RangePF1(ID)

    SELECT * FROM sys.partitions
    WHERE OBJECT_NAME([object_id]) = 'tablename';

    CREATE CLUSTERED INDEX ClustID ON db.tablename(ID);
    ALTER TABLE dbo.tablename
    SWITCH PARTITION 4
    TO db.tablename PARTITION 1

-- Bookmark Lookups

-- Covering Index
    -- Add multiple columns to nonclustered index
    CREATE CLUSTERED INDEX ClustID ON db.tablename(col1, col2);
    ALTER TABLE dbo.tablename
    WITH(drop_existing = ON);

-- Set statistics IO
    SET STATISTICS IO ON; -- Look for scan count and logical reads (ignore physical reads)


-- SERVER LEVEL SECURITY
    -- Create login
    CREATE LOGIN [computer\USER] FROM WINDOWS; -- Create from windows

    CREATE LOGIN [computer\GROUP] FROM WINDOWS; -- Create by group

    CREATE LOGIN [username] WITH PASSWORD=N'1234' MUST_CHANGE,
        DEFAULT_DATABASE = [master],
        CHECK_EXPIRATION = ON, -- enforces password expiration restrictions in AD (Active Directory)
        CHECK_POLICY = ON; -- Forces AD password policy

        -- Roles
        ALTER SERVER ROLE
        ADD MEMBER [username]
        GO;

        sp_srvrolepermission 'diskadmin';

        -- Granting permissions
            --(Can right click on server and click on permissions)
            GRANT VIEW SERVER STATE, CONTROL SERVER TO [username];

            -- Permissions granted immediately no need to flush privileges

-- Database Level Security
    -- execute as user
    EXECUTE_AS_USER = 'username';

    -- Roles (Expand DB->Security->Roles->Database Roles in GUI)
    CREATE ROLE [Rolename]

    -- Grant role same as grant user
    GRANT EXECUTE, VIEW definition TO [Rolename];

    -- Add uesr to role
    ALTER ROLE [Rownane] ADD MEMBER [username];

-- Schema
    CREATE SCHEMA [Schemaname] AUTHORIZATION [username];

    -- Schema security
    --ALTER AUTHORIZATION ON dbo.table TO [dbo]; is
    ALTER AUTHORIZATION ON schema::Schemaname TO dbo;

    -- Grant at schema level
    GRANT EXECUTE ON schema::Schemaname TO [username];

-- Transaction Logs (Tracks db changes, Allows for rollback, changes written to log first)

-- Patch Management
    -- RTM: - Release to manufacturer (Market)
    -- CU: Cumilitive update (hotfixes)
    -- SP: Servuce Pack

-- Concurrency Tools and Blocking Conditions (xlock locks until finished)

    -- Hold exclusive rows until transaction is committed
    BEGIN TRAN
        SELECT * FROM dbo.tablename(xlock)
        WHERE zip BETWEEN 75401 AND 7800
    COMMIT TRAN

-- Troubleshooting Data
    -- SQL Profiler Tools -> Profiler
    -- Trace name (arbitrary)
    -- Rollover creat

    -- Dynamic Management Views(Funtions) (DMVs)

-- Temporary table
    SELECT COLUMNS INTO #temptable FROM TABLE WHERE id = 1;

-- POWERSHELL
    get-service --ps
    get-process --ps -auxgext.txt -- command > /text.txt
    get-member --(gm)
    get-service | ?{$_.Name -eq "SQL"} --Get service by name e.g. SQL
    get-service | ?{$_.Name -eq "SQL"} | %{$_.Stop()} -- Same with start -- ? = where -- % = foreach
    pscd drive -- List drives Can access registry as logical drive


-- Variables
    DECLARE @theVar VARCHAR(32);
    SET @theVar = 'Your Mom!';

-- Procedure with variables
    CREATE PROCEDURE count_by_lname(@lname VARCHAR(64))
    AS
    BEGIN
        SELECT COUNT(*) FROM [CASE] WHERE LName LIKE @lname;
    END

    count_by_lname @lname = 'Smailey';

-- If Statement
    IF (SELECT COUNT(*) FROM [CASE] WHERE LName LIKE 'Smailey') > 100
        PRINT 'OK'
    ELSE
        PRINT 'Nope'


-- Second If Statement
    IF (SELECT COUNT(*) FROM [CASE] WHERE LName LIKE 'Smailey') > 100
        BEGIN
            PRINT 'OK'
        END
    ELSE
        BEGIN
            PRINT 'Nope'
        END

-- Create and execute function
    CREATE FUNCTION functionName (@LName AS VARCHAR(64)) RETURNS VARCHAR(MAX)
    AS
    BEGIN
        RETURN('You typed:' + @LNAME)
    END

    SELECT dbo.functionName('Test');

-- Create table values function
    CREATE FUNCTION smaileys() RETURNS TABLE
    AS
    RETURN
        SELECT CaseID, FName, LName FROM [CASE] WHERE LName LIKE 'SMAILEY';

    SELECT * FROM dbo.smaileys();

-- Table variables (array)
    DECLARE @dood TABLE
    (
        firstname VARCHAR(MAX),
        lastname VARCHAR(MAX)
    );
    INSERT INTO @dood (firstname, lastname) VALUES ('Matthew', 'Smailey');
    SELECT * FROM @dood;

    -- Common table expressions
    WITH filmcounts AS (
            SELECT filmCountryID, COUNT(*) AS numberOfFilms FROM films GROUP BY filmCountryID
    ) SELECT FROM filmcounts AVG(numberOfFilms);

--Cursor 'FETCH NEXT', 'FETCH FIRST', 'FETCH LAST', 'FETCH PRIOR'
    DECLARE user_cursor CURSOR
        FOR SELECT CaseID, FName LName FROM [CASE] WHERE LNAME LIKE 'Smailey';

    OPEN user_cursor
        FETCH NEXT FROM user_cursor

        WHILE @@FETCH_STATUS = 0
            FETCH NEXT FROM user_cursor

    Close user_cursor
    DEALLOCATE user_cursor

-- Transactions
    BEGIN TRANSACTION --Begin transaction (TRAN for short)
        INSERT INTO TABLE (id) VALUES (1);
    COMMIT -- Transaction gets committed

    -- Rollback transaction (undo)
    ROLLBACK TRANSACTION;

    --Naming transaction
    BEGIN TRAN transaction_name;
    COMMIT TRAN transaction_name;