Developing Microsoft SQL Server 2016 Databases

Introduction

• Welcome
• Using the exercise files
• What you should know
• Install the sample database

• Table design in SQL Server
• Data types
• Create and alter tables
• Implement table schemas
• Calculate table columns

• Partition large data tables
• Create temporary tables
• Use table variables
• Understand column collation
• Data compression fundamentals
• Compress data tables
• Go back in time with temporal tables

• What are views?
• Create a view of the data
• Enhance view performance

• Understand data integrity
• Implement domain integrity
• Enforce referential integrity
• Use DML triggers to verify data

• Define user roles and permissions
• Add a user to a role with T-SQL
• Create loginless and contained users
• Implement schema security

• Store spatial data
• Add GUID data to a table
• Implement the XML data type
• Store BLOB data in a table
• Implement FILESTREAM access
• Implicit and explicit data conversions

• Introduction to stored procedures
• Write a stored procedure with T-SQL
• Add parameters to a stored procedure
• Recompile a stored procedure

• Why use in-memory tables?
• Create a memory-optimized table
• Write a native compiled stored procedure

• Table index fundamentals
• Clustered indexes
• Non-clustered indexes
• Single column and composite indexes
• Design spatial and XML indexes

• Improve queries with covering indexes
• Obtain index statistics
• Filtered indexes
• Create columnstore indexes

• Introduction to sparse columns
• Implement sparse columns

• Defragment indexes
• Analyze execution plans and tune queries
• Leverage the SQL Tuning Advisor

• SQL Server function overview
• Return values with scalar functions
• Create a table-valued function

• Use Dynamic Management Views
• Record extended event sessions
• Find bottlenecks with Activity Monitor

• Next steps