大家好,又见面了,我是全栈君,祝每个程序员都可以多学几门语言。
需求:
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
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/118350.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...