使用 t(n) 和多个交叉连接进行查询

2021-09-10 00:00:00 tsql sql-server

我试图从 Stackoverflow 线程理解这个查询:

Am trying to understand this query from a Stackoverflow thread:

--create test table
CREATE TABLE dbo.TestTable(
      Col1 nchar(4000) NOT NULL
    , Col2 nvarchar(MAX) NOT NULL
    );

--load 10000 rows (about 2.8GB)
WITH 
    t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    ,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    ,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
FROM t16M
WHERE num <= 100000;
GO

--run query in loop (expect parallel execution plan with many read-ahead and LOB page reads)
SET NOCOUNT ON;
DECLARE @RowCount int, @Iteration int = 1;
WHILE @Iteration <= 100
BEGIN
    CHECKPOINT;
    DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
    SELECT @RowCount = COUNT(*) FROM dbo.TestTable WHERE Col2 LIKE 'X%';
    RAISERROR('Iteration %d completed',0,1,@Iteration) WITH NOWAIT; --display progress message
    SET @Iteration += 1;
END;
GO

  1. 我特别无法理解 t-sql 查询的这一部分:

  1. I especially couldn't understand this portionof the t-sql query :

--load 10000 rows (about 2.8GB)
WITH 
    t4 AS (SELECT n FROM (VALUES(0),(0),(0),(0)) t(n))
    ,t256 AS (SELECT 0 AS n FROM t4 AS a CROSS JOIN t4 AS b CROSS JOIN t4 AS c CROSS JOIN t4 AS d)
    ,t16M AS (SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num FROM t256 AS a CROSS JOIN t256 AS b CROSS JOIN t256 AS c)
INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
FROM t16M
WHERE num <= 100000;
GO

  • 为什么我们在--load 10000rows..."下面有一个With"；那个with"是做什么的?它是创建"语句的一部分吗?

  • Why do we have a "With" below "--load 10000rows..." ; what does that "with" do? is it part of the 'create' statement?

    关于这个插入语句:

    INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
FROM t16M
WHERE num <= 100000;

    不会只是

    INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
FROM t16M
WHERE num <= 100000;

    做什么?以上不会从 t16M 中取出前 10000 行并插入到 dbo.TestTable 中吗?我们为什么要进行选择..."?这意味着什么?我知道 REPLICATE(N'X', 4000) 会将 X 放入 Col1, REPLICATE(CAST('X' AS nvarchar(MAX)), 10000) 会将 X 放在 Col2 中 10000 次.如果我们这样做，那为什么要从 t16M 中选择?或者如果我们从 t16M 中选择，那为什么要复制这个东西?

    do? Wouldn't the above take the first 10000 rows from t16M and insert into dbo.TestTable? Why are we doing that "select..."? What is the implication of that? I understand that REPLICATE(N'X', 4000) would put X 4000 times in Col1, REPLICATE(CAST('X' AS nvarchar(MAX)), 10000) would put X 10000 times in Col2. If we are doing this, then why select from t16M? or if we are selecting from t16M, then why do this replicate thing?

    有什么作用@RowCount = COUNT(*)做?将表中的行数分配给名为RowCount"的变量?它与'@@RowCount' 相同吗?我认为不可能.

    what does @RowCount = COUNT(*) do? Assign num of rows in the table to a variable called 'RowCount'? Is it the same as '@@RowCount'? I don't think it can be.

    推荐答案

    我将重新格式化代码以便更好地理解:

    I''ll reformat the code for better understanding:

    WITH t4 AS(
    SELECT n FROM (VALUES(0),(0),(0),(0)) t(n)
)
,t256 AS(
    SELECT 0 AS n 
    FROM t4 AS a 
    CROSS JOIN t4 AS b 
    CROSS JOIN t4 AS c 
    CROSS JOIN t4 AS d
)
,t16M AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (a.n)) AS num 
    FROM t256 AS a 
    CROSS JOIN t256 AS b 
    CROSS JOIN t256 AS c
)
INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
FROM t16M
WHERE num <= 100000;

    3 个级联CTE组成一个Tally Table 或其他称为Numbers Table.这是由从 1 到某个数字的连续数字组成.

    The 3 cascading CTEmade up a Tally Table or others called Numbers Table. This is composed of sequential numbers from 1 up to some number.

    此生成 4 行，值为 0:

    This one generates 4 rows with 0 value:

    WITH t4 AS(
    SELECT n FROM (VALUES(0),(0),(0),(0)) t(n)
)

    然后它是CROSS JOIN 4次，从而生成4 * 4 * 4 * 4 or 4^4 or >256 行，因此别名 t256.再次 t256 被 CROSS JOIN 连接到自身 3 次，产生 16,777,216 行，因此别名 t16M.如果您执行 SELECT * FROM t16M，您可以验证它返回超过 16M 行.

    Then it is CROSS JOINed to itself 4 times thus generating 4 * 4 * 4 * 4 or 4^4 or 256 rows, thus the alias t256. Again t256 is CROSS JOINed to itself 3 times producing 16,777,216 rows thus the alias t16M. If you do a SELECT * FROM t16M, you can verify that it returns over 16M rows.

    这用于将 100000 行插入 TestTable，如 where 子句所示:

    This is used then to insert 100000 rows into the TestTable, as evidenced by the where clause:

    INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
FROM t16M
WHERE num <= 100000

    有些人可能会使用 WHILE 循环来尝试执行此操作，即将 100000 行插入表中.Tally Table 是一种以基于集合的方式执行此操作的好方法.有关详细信息，请阅读:http://www.sqlservercentral.com/articles/T-SQL/62867/

    Some may use a WHILE loop in attempt to do this, that is insert 100000 rows into a table. The Tally Table is a great way to do this in a set-based fashion. For more info, read this: http://www.sqlservercentral.com/articles/T-SQL/62867/

    问题 2CREATE 语句下方的 WITH 关键字标记了 公用表表达式.

    Question #2 The WITH keyword below the CREATE statement marks the declaration of a Common Table Expression.

    问题 #3我相信下面的查询会产生语法错误.

    Question #3 I believe the query below will produce a syntax error.

    INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
FROM t16M
WHERE num <= 100000;

    另一方面，这不会.

    INSERT INTO dbo.TestTable WITH(TABLOCKX) (Col1, Col2)
SELECT REPLICATE(N'X', 4000), REPLICATE(CAST('X' AS nvarchar(MAX)), 10000)
FROM t16M
WHERE num <= 100000;

    这个查询的作用是INSERT 100000 行，由 2 列组成，其值是连续的 'X' 字符串.请记住，t16M 是我们的 Tally Table，它由从 1 到 16M+ 的数字序列组成.我们不使用Tally Table 的值进行插入，我们只使用其行的存在来限制插入次数.

    What this query does is INSERT 100000 rows, composed of 2 columns, whose values are continuous strings of 'X's. Remember that t16M is our Tally Table which consists of sequence of numbers from 1 up to 16M+. We do not use the values of the Tally Table for the insert, we only use the presence of its rows to limit the number of inserts.

    问题 #4 当您说 @RowCount = COUNT(*) 将行数分配给变量时，您说得对.

    Question #4 You're right when you said @RowCount = COUNT(*) assigns the number of rows to the variable.

    SELECT @RowCount = COUNT(*) FROM dbo.TestTable WHERE Col2 LIKE 'X%';
SELECT @@ROWCOUNT

    然而，上述说法并不相同.@@ROWCOUNT 返回受最后一条语句影响的行数.如果我们将它放在 SELECT @RowCount 之后的 WHILE 循环中，它将返回 1，因为只有 1 行受到影响.但是，如果我们直接把它放在INSERT 语句之后，它会返回与SELECT COUNT(*) FROM dbo.TestTable 一样的.

    However, the above statements are not the same. @@ROWCOUNT returns the number of rows affected by the last statement. If we put it inside the WHILE loop right after the SELECT @RowCount, it will return 1, as only 1 row is affected. However, if we put it directly after the INSERT statement, it will return the same as SELECT COUNT(*) FROM dbo.TestTable.

    • 相关文章