I finished this book in 20 pages. I originally thought that the theme of this book was the explanation of t-sql in sql server 2005, but after reading it, I found that it was not. There is no systematic explanation of t-sql in the book. The first three chapters talk about the principles of query, and the next six chapters classify common SQL problems into one category for each chapter: Chapter 4 subqueries, table expressions and ranking functions, Chapter 5 joins and set operations, Chapter 6 aggregation and rotation of data , Chapter 7 TOP and ALLPY, Chapter 8 Data Modification, Chapter 9 Graphs, Trees, Hierarchies and Recursive Query.
The author said in the preface that if you read this book carefully and spend some time doing the exercises in the book, you can master the experience accumulated by the author for more than ten years in a short period of time. I have read this book and basically read the code in the book. I feel that I have learned a lot from the author's experience, and the author is very experienced. Many solutions not only show the author's skills in SQL, but the algorithms are also worth learning. In addition, many questions give solutions under sql server 2000 and solutions under sql server 2005. Here are two examples:
1. Quickly generate a data table with only one column. The value of this column ranges from 1 to 8000. The sql only needs to be executed 14 times (the first sql inserts data 1, and the next 12 times inserts data within 2 to the power of 12 4096, and the remaining The following 8000-4096 numbers are generated at once) instead of 8000 times:
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 8000;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO
2. For tables with recursive structures, the author introduces the common design method (recursion) and also introduces the nested collection model proposed by Joe Celko. And gave his own faster implementation method. This model sets two values at each node of the number: lvalue and rvalue. The left value is less than the left value of all subordinate nodes, and the right value is greater than the right value of all subordinate nodes. The final calculation of the recursive relationship is very fast. Of course, the change of a node in this model will cause an average of half of the nodes to recalculate the left and right values, so it is not suitable for data tables that require frequent real-time changes. The specific SQL is relatively long. If you are interested, you can read the book or download the source code of this book.