Q: Query with t(n) and multiple cross joins 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



*

*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


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

*regarding this insert 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;

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

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?

*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.

A: 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;

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.
This one generates 4 rows with 0 value:
WITH t4 AS(
    SELECT n FROM (VALUES(0),(0),(0),(0)) t(n)
)

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.
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

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/

Question #2
The WITH keyword below the CREATE statement marks the declaration of a Common Table Expression. 
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;

This, on the other hand, will not.
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;

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.
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

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.
