Developing Microsoft SQL Server 2016 Databases

Learn how to develop a SQL Server 2016 database with more advanced features, like temporary tables, stored procedures, indexes, and secure user accounts.

Introduction

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

1. Data Table Fundamentals

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

2. Implement Advanced Table Features

• 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

3. Work with Database Views

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

4. Ensure Data Integrity through Constraints

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

5. Create Secure User Accounts

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

6. Implement Data Types

• 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

7. Create Stored Procedures

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

8. Create In-Memory Tables

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

9. Introduction to Indexing

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

10. Index Optimization

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

11. Work with Sparse Columns

• Introduction to sparse columns
• Implement sparse columns

12. Optimize Query Performance

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

13. Design and Implement User-Defined Functions (UDFs)

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

14. Monitor Database Performance

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

Conclusion

• Next steps