> Create Database Objects
– CREATE/ALTER/DROP TABLE/COLUMN
– Data Types (XML, DATETIME, SPATIAL, VARCHAR)
– Constraints (Primary Key, Foreign Key, Unique, Check, Default)
– Calculated Column
– Views
– Indexed Views (SCHEMABINDING, COUNT_BIG(*), CLUSTERED INDEX)
– Stored Procedures, [link].
– DML Triggers (INSERTED, UPDATED, UPDATE function) vs CHECK Constraint
– TRIGGER for VIEWS (INSTEAD OF)
– UDF (Functions), [link].
– SP vs UDF, [link].

> Work with Data
– New Functions in SQL Server 2012 (IFF, TRY_PARSE, CONCAT, FORMAT), [link].
– FETCH-OFFSET, [link].
– SEQUENCE, [link].
– Ranking and Window Functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), [link].
– JOINS (INNER, OUTER LEFT, OUTER RIGHT, CROSS), [link].
– APPLY Operators (CROSS APPLY vs OUTER APPLY), [link].
– CTE and Sub-Queries, [link].
– PIVOT, [link].
– ROLLUP, CUBE & GROUPING SETS, [link].
– Dynamic SQL
– ANY, SOME, ALL
– CASE vs ISNULL vs COALESCE, [link].
– FOR XML RAW/AUTO/PATH [ELEMENTS], [link].
– Implementing XML Schemas and Handling of XML data

> Modify Data
– Stored Procedure (with EXECUTE AS, RECOMPILE)
– MERGE Statement (TARGET, SOURCE, WHEN MATCHED, WHEN NOT MATCHED, OUTPUT), [link].
– EXCEPT vs INTERSECT
– UNION vs UNION ALL, [link].
– SCALAR vs TABLE Valued Functions.
– Use of APPLY with UDFs, [link].
– VARCHAR(MAX) and .WRITE(), [link].

> Troubleshoot & Optimize
– Using Statistics
– SQL Internal JOINS (NESTED – Small, MERGE – Large Sorter, HASH – Large Unsorted), [link].
– TRANSACTIONS (BEGIN, COMMIT, ROLLBACK, XACT_ABORT, TRANCOUNT), [link].
– ISOLATION Levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, SERIALIZABLE)
– TRY/CATCH, [link].
– RAISE vs THROW [link]
– CURSORS (Row-Based) vs SET Based Approach
– Table Hints (UPDLOCK, ROWLOCK, TABLOCK, …etc)
– Query Hints (OPTION (OPTIMIZED FOR … [UNKNOWN]))