|
- BACKUP and RESTORE
- Keys
- Skinny = Fast
- Primary Key
- Natural
- Surrogate
- Composite
- Foreign Key
- Referential Integrity
- Indexes vs. Constraints
- Clustered vs. Nonclustered Indexes
- Data Types
- date, smalldatetime, datetime, datetime2
- int, smallint, tinyint
- bit
- numeric, decimal, float
- varchar, nvarchar, char, nchar
- text, image
- Conditional Operators
- =, >, <, >=, <=, <>, !=, BETWEEN, IN, NOT IN, LIKE, NOT LIKE, IS NULL, IS NOT NULL, EXISTS, NOT EXISTS
- Anatomy of a SQL SELECT
- SELECT column list (why not *?)
- TOP
- Table and Column Aliases
- Joins
- INNER JOIN
- LEFT (or right) JOIN
- WHERE
- ORDER BY
- Useful Functions
- COALESCE, ISNULL
- CASE, both flavors
- Strings: SUBSTRING, CHARINDEX, LEN, LEFT, RIGHT
- Dates: DATEADD, DATEDIFF, FORMAT, GETDATE, GETUTCDATE, DATEPART/YEAR/MONTH/DAY, DATENAME
- CAST, CONVERT
- Aggregate Queries
- GROUP BY
- SUM, COUNT, MIN, MAX, AVG, STDEV
- HAVING
- Correlated Subqueries
- Less Frequently Used SELECT Variants
- Derived tables
- UNION and UNION ALL (point out column definition/type and order by placement)
- FULL OUTER JOIN
- CROSS JOIN
- ...and even more if you're interested
- Abusing a left join to ensure lack of existence
- Compound ON clause or entries in the WHERE clause?
- 8K pages, BTREEs and how this black magic really works
- Join multiple times to the same table in a single query
- COMPUTED PERSISTED columns
- CTE
- Recursive CTE
- CROSS APPLY
- OUTER APPLY
- PIVOT and UNPIVOT
- Windowing Functions with OVER()
- OUTPUT clauses on insert, update and delete
- .NET dlls in the database: CLR assembly definition and usage
- Administration Features (Complex backup and restore scenarios, SQL Agent, Perfmon counters, Replication scenarios)
- SSAS
- SSRS
- Performance Analysis (Indexes, objective/measurable improvement, general considerations, etc.)
- EXERCISE: Query practice with Sakila
|
