需求：

MSSQL，列出服務實例中全部數據庫的例如以下信息： 數據庫ID、數據庫名、創建日期、數據文件類型、數據文件大小、數據庫總大小、文件所在路徑。

寫法(後面的百分比為所花時間占比)：

-- 连接子查询  (47%)

WITH cte_TotalSize AS

(

	SELECT database_id

	      ,CAST(SUM(size) AS FLOAT)/128 AS [TotalSize(MB)]

	FROM sys.master_files

	GROUP BY database_id

)

SELECT a.database_id AS [DB_ID]

      ,b.name AS [DB_Name]

      ,b.create_date

      ,CASE a.type WHEN 1 THEN 'Log' ELSE 'Data' END AS File_Type

      ,CAST(a.size AS FLOAT)/128 AS [Size(MB)]  -- Size以页（8 KB）为单位

      ,c.[TotalSize(MB)]

      ,a.physical_Name AS File_Path

FROM sys.master_files a

INNER JOIN sys.databases b ON a.database_id = b.database_id

INNER JOIN cte_TotalSize c ON a.database_id = c.database_id

-- 标量子查询  (34%)

SELECT a.database_id AS [DB_ID]

      ,b.name AS [DB_Name]

      ,b.create_date

      ,CASE a.type WHEN 1 THEN 'Log' ELSE 'Data' END AS File_Type

      ,CAST(a.size AS FLOAT)/128 AS [Size(MB)]  -- Size以页（8 KB）为单位

      ,(SELECT CAST(SUM(size) AS FLOAT)/128 FROM sys.master_files WHERE database_id = a.database_id) AS [TotalSize(MB)]

      ,a.physical_Name AS File_Path

FROM sys.master_files a

INNER JOIN sys.databases b ON a.database_id = b.database_id

-- 窗体函数  (19%)

SELECT a.database_id AS [DB_ID]

      ,b.name AS [DB_Name]

      ,b.create_date

      ,CASE a.type WHEN 1 THEN 'Log' ELSE 'Data' END AS File_Type

      ,CAST(a.size AS FLOAT)/128 AS [Size(MB)]  -- Size以页（8 KB）为单位

      ,CAST(SUM(size) OVER(PARTITION BY a.database_id) AS FLOAT)/128 AS [TotalSize(MB)]

      ,a.physical_Name AS File_Path

FROM sys.master_files a

INNER JOIN sys.databases b ON a.database_id = b.database_id

結果：

均得出正確結果的上面三種方法，代碼越來越少，性能卻越來越好。。

当中第三種是使用了窗体函數，相關文檔：http://msdn.microsoft.com/zh-cn/library/ms189461.aspx