Database implementation and development in SQL Server environment

The course is the second stage of the SQL developer training. The course is intended for developers, database administrators, database designers who will be responsible for creating and optimizing complex SQL Server 2016 databases and database solutions.

• SQL Server 2016 overview: architecture, building blocks, features, tools; configuring SQL Server services
• Designing and implementing tables: design principles, requirements; using schemas; creating and managing tables, using calculated columns
• Advanced table design solutions: the importance of the physical database; data partitioning; data compression; use of temporary tables
• Ensuring data integrity using contraints: basic concepts; implementing domain integrity; designing, implementing and testing contraints; concepts and use of entity and reference integrity
• Indexing basics: overview, purpose, operation of indexing; relationship between data types and indexes; index types, their characteristics (heaps, clustered, nonclustered, covered); use of simple column and composite indexes
• Design of indexing strategies: overview of indexing strategies; index management; heap structure concept and application; clustered index concept and application; design of efficient clustered indexes
• Use of performance optimisation tools (Database Engine Tuning Advisor, Query Store)
• Execution plans: basic concepts, objectives; elements of execution plans; application of execution plans
• Columnstore index overview, creation and use; columnstore index design considerations
• Designing and implementing views: introduction; designing, building and managing views; performance considerations when designing views
• Designing and implementing stored procedures: overview, basic concepts, function; working with stored procedures; creating and running stored procedures; creating and using parameterized stored procedures; controlling the execution environment of stored procedures
• Designing and implementing custom functions: overview of functions; design and use of scalar functions; design and use of table-value functions; performance considerations when using functions; function alternatives
• Use of triggers: basic concepts, function; DML trigger concepts and design; DML trigger implementation and testing; advanced trigger design/concepts
• Using in-memory tables: overview, how in-memory online transaction processing (OLTP) works; using in-memory tables and native compiled stored procedures to improve performance
• Using managed code: overview of the common runtime environment (CLR); the concept of SQL CLR integration; the concept, importing and configuring assemblies; implementing SQL CLR integration; implementing objects created within .NET assemblies
• Managing XML data: introduction to XML and XML Schemas; storing XML data and XML Schemas under SQL Server; implementing XML data types; using XML Schema collections; querying XML data in SQL Server: using the T-SQL FOR XML statement; building and using simple XQuery queries; creating stored procedures with XML returns; shredding XML data back into a relational database
• SQL Server 2016 Spatial Data: basic concepts; using the Spatial data type (GEOMETRY, GEOGRAPHY); using Spatial Data in applications, managing Spatial data
• Storage and retrieval of large (BLOB) data and text documents: management aspects of BLOB data; use of FILESTREAM; use of file tables. Full-text search implementation: creating and using a full-text index; writing and using full-text queries
• Concurrency management: overview of transactions and locks, basic concepts; managing transactions and locks; concept and use of transaction isolation levels, use of snapshot isolation; implementing partition-level locking
• Basics of performance monitoring and optimization: overview and use of Extended Events to collect and analyze data; use of Live Query Statistics; optimization of database file configurations; use of metrics definition; use of performance monitoring tools (Dynamic Management Views, Performance Monitor)

Windows 8/10 or Windows Server 2012/2016 operator basics, practice in using SQL queries. Completion or knowledge of SQL Query 20461 or 20761. Previous knowledge of programming basics an advantage. As the course materials are in English, basic English at document reading level is required. The presentation will be in Hungarian.