問題描述
我試圖從 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
我特別無法理解 t-sql 查詢的這一部分:
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 CTE
made 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 JOIN
ed to itself 4 times thus generating 4 * 4 * 4 * 4
or 4^4
or 256
rows, thus the alias t256
. Again t256
is CROSS JOIN
ed 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
.
這篇關于使用 t(n) 和多個交叉連接進行查詢的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!