Applied SQL - Preparation of Manipulation Statements - SQL Server 2022: Part 3 of 3 - Understanding How and Why in SQL Server
暫譯: 應用 SQL - 操作語句的準備 - SQL Server 2022：第三部分 - 理解 SQL Server 中的運作原理與原因

Part 3 of the book contains articles for better understanding of joins and indexes.

Part 1 of the book is about writing correct and best performing SELECT statements.

The book contains the following topics.

• Understanding the definition of a database, tables and constraints.
• Brief, general overview of the mathematics that must be used when preparing a statement.
• The 8 Algebra Operation – how to use this for writing correct well performing and correct statements.
  • Selection
  • Projection
  • Join – detailed review of the different join types
  • UNION, INTERSECT and EXCEPT
  • Cartesian Product
  • Divide
• Examples of using the Algebra Operations but not least with a focus on Joining many tables, so that we get a correct and maintainable result.
• Grouping of data - GROUP BY, CUBE, ROLLUP, GROUP BY GROUPING SETS and Window Functions.
• Sub-Select.

Part 2 is about the Different Object types and about Index in SQL Server.

There are many different object types in SQL Server. These are reviewed in Part 2 of the book. In addition, there is a review of the Index types with focus on understanding how the index is structured and can be used.

The topics are Views, PIVOT, UNPIVOT, APPLY JOIN, CTE, INSERT, DELETE, UPDATE, MERGE and TRUNCATE.

Definition of Stored Procedure, Function and Trigger. Temporal Data and InMemory Optimized Tables are important possibilities to know about. Maybe Data Masking and Row Level Security are not used, but it is important to know about all possibilities in the product.

This book contains details about the different Index types as Clustered Index, NonClustered Index, Filtered Index, columns included in an index and Columns Store Index.

Part 3 is about understanding of Joins, Indexes and how data is physical stored.

In this part of the book there are chapters on rarely used options. This subjects should be known if the need arises.

Graph and XML.

This part of the book has chaptersabout which type of Index to Create, position of the ClusterKey in a NonClustered Index, Ascending or Descending Sort Order in Indexes, Column Order in a MultiColumn Index, Index on a Foreign Key Column but also a chapter just for Fun and better understanding of indexes.

Try to understand the Join Semantic helps creating and using the index in the best way. There is chapters about LOOP, HASH and MERGE JOIN. But also a chapter for learning more about InMemory tables and indexes for this tables.

It is important to have an understanding about how the tables are defined, so there is a chapter about how to implement a Logical Data Model in several ways as a Physical Data Model.