Applied SQL - Preparation of Manipulation Statements - SQL Server 2022: Part 1 of 3 How to Write SELECT in SQL Server
暫譯: 應用 SQL - 操作語句的準備 - SQL Server 2022：第 1 部分 如何在 SQL Server 中撰寫 SELECT

Part 1 of the book has focus on writing a SELECT statement. When preparing manipulation statements, it should be planned how the statement should be structured before the statement is written. First we need to know exactly how the tables are created. We need to know all the constraints specified for the individual tables and columns in terms of data type, NULL/NOT NULL, Primary Key, Foreign Keys, Check Constraints and Triggers.

Relational databases are based on mathematics. Therefore, mathematics can be used to advantage when planning a statement.

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.