至于陷阱一，PARTITION BY 高基数列。排序开销爆炸

这是最容易踩的坑。

-- 在 8000万行的订单表上，按 OrderID 做窗口分区 SELECT OrderID,OrderDate。TotalAmount,SUM OVER ( PARTITION BY OrderID ) AS OrderTotal FROM Orders;

看起来人畜无害？问题在于这方面，OrderID 是主键。每个分区只有一行。

SQL Server 执行窗口函数的第一步先是 排序。对于 PARTITION BY OrderID它需要对 8000万行按 OrderID 做排序——而 OrderID 本来就是唯一的。排序毫无意义，但调整器不知道，它老老实实地排了。

You are probably seeing this symptom:

• The query runs from milliseconds to minutes.
• Your TempDB suddenly spikes.
• You’re left wondering *** a simple sum is so slow.

A correct approach:

• If you just need “order total” per row。use a join or subquery instead of a window function.
• If you must use a window function,make sure partition column has an index that supports sorting.

Use JOIN instead of window when possible

SELECT
o.OrderID,o.OrderDate,o.TotalAmount,od.OrderTotal
FROM Orders o
INNER JOIN (
SELECT OrderID,SUM AS OrderTotal
FROM Orders
GROUP BY OrderID
) od ON o.OrderID = od.OrderID;

Add an index if you really need window function

CREATE NONCLUSTERED INDEX IX_Orders_PartitionON Orders
INCLUDE;-- 这样 PARTITION BY CustomerID 可以利用索引顺序，避免显式排序 

Check your execution plan for:

• A Sort operator with a huge Estimated Number of Rows.
• A Sort Type of “OrderBy”.

陷阱二这方面。ORDER BY 在窗口中触发“逐行计算”

MANY PEOPLE add ORDER BY when using ROW_NUMBER without realizing how it changes performance.

SELECT
CustomerID,OrderDate,TotalAmount,SUM OVER (
PARTITION BY CustomerID
ORDER BY OrderDate
) AS RunningTotal
FROM Orders;

This query’s problem is that ORDER BY,transforms window from “entire partition” into a “frame that expands row‑by‑row.” For every row in each partition SQL Server must recalculate aggregate for all rows up to that point.

Your symptoms?

• The execution plan shows Window Spool + Sort.
• Your TempDB usage climbs sharply.
• You see running time increase dramatically as rows grow.

Explicit frame clause helps optimizer understand what you need:

SELECT
CustomerID。OrderDate,TotalAmount,SUM OVER (
PARTITION BY CustomerID
ORDER BY OrderDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS RunningTotal
FROM Orders;

Or even 娱乐ter – leverage indexes that match your ordering:

CREATE NONCLUSTERED INDEX IX_Orders_Customer_OrderDateON Orders
INCLUDE;

Tip这方面，

• If you’re on an older version,consider using CTEs or CROSS APPLY to avoid heavy Window Spool overhead on massive datasets.

陷阱三这方面。同一个 SELECT 中写多个窗口函数，重复排序

SELECT
CustomerID,OrderDate,TotalAmount,ROW_NUMBER OVER AS rn,RANK OVER AS rk,SUM OVER AS CustTotal,G OVER AS CustAvg,COUNT OVER AS CustCount
FROM Orders;

You may think each window runs independently—but SQL Server can perform multiple sorts on same set of rows if each function has its own definition.

• The plan may show several Sort operators or one Sort followed by several Window Spool nodes.
• This leads to unnecessary CPU and memory pressure.
• Your query might be slower than two separate queries combined!

Use WINDOW clause in newer versions of SQL Server:

SELECT
CustomerId,OrderDate,TotalAmount。ROW_NUMBER OVER w1 AS rn,RANK OVER w1 AS rk,SUMOVER w2 AS CustTotal,GOVER w2 AS CustAvg,COUNTOVER w2 As CustCount
FROM Orders
WINDOW
w1 as,w2 as;

If you’re on an older version – split calculations into CTEs or subqueries so you only sort once:

WITH BaseWindow AS (
SELECT
customerid。orderdate,totalamount,sum over as custtotal,count over as custcount
FROM orders )
SELECT
customerid,orderdate,totalamount,ROW_NUMBER over rn,RANK over rk,custtotal,cast custavg
FROM BaseWindow;

说到陷阱四，窗口函数 + WHERE过滤 =先算后滤 This is about execution ordering pitfalls. SQL logical execution sequence: FROM → WHERE → GROUP BY → HING → SELECT → ORDER BY Window functions run during SELECT stage – meaning y compute over entire result set before filtering.

**You’ve likely seen this pattern:** sql -- Want most recent order per client SELECT * FROM ( SELECT CUSTOMER_ID。ORDER_DATE,TOTAL_AMOUNT,ROW_NUMBER OVER( PARTITION BY CUSTOMER_ID ORDER BY ORDER_DATE DESC ) rn FROM ORDERS ) t WHERE t.rn =1;In this example Windows computed **over all ~80 M rows**,n only ~500 K rows survive after `WHERE`. All those unnecessary calculations waste CPU and memory.

How to avoid it

A. Use CROSS APPLY – only compute one row per group

sql

-- Each client sees only its latest record

SELECT c.CustomerId。o.OrderDate,o.TotalAmnt FROM Customers c CROSS APPLY ( SELECT TOP ORDERDATE,TOTALAMOUNT FROM ORDERS o WHERE o.CustomerID = c.CustomerID ORDER BY ORDER_DATE DESC -- seek directly via index ) o;

B. If you only need values from orders table itself – use FIRST_VALUE + indexed covering scan

-- With an index on

SELECT DISTINCT CUSTOMERID。FIRSTVALUE OVER( PARTITiON BY CUSTOMERID ORDER BY   ORDERDATE DESC ) LastOrderDt,FIRSTVALUE OVER( PARTITiON BY CUSTOMERID ORDER BY   ORD_Dt DESC ) LastAmt;

Which works best?

• Cross Apply typically gives best performance because it uses index seeks and never materializes more rows than necessary.
• FIRST_VALUE works well when you truly just need one column per group and your table already has suitable covering indexes.
• In both cases look at execution plans: y should show Index Seek rar than Table Scan plus Sort.

Bottom line: Never put heavy windows inside a derived table that is later filtered out—unless you really need every row processed first. Make sure your logic forces SQL Server to filter before computing windows whenever possible.