SQL Window Function

SQL Window Function

Requirements:

MSSQL is used to list the following information about all data volumes in a server instance: data upload ID, data upload name, creation date, data file type, data file size, total data size, and file path.

Percentage method (the percentage is the percentage of time spent ):

-- Connect the subquery (47%) WITH cte_TotalSize AS (SELECT database_id, CAST (SUM (size) as float)/128 AS [TotalSize (MB)] FROM sys. master_filesGROUP BY database_id) SELECT. database_id AS [DB_ID], B. name AS [DB_Name], B. create_date, CASE. type WHEN 1 THEN 'log' ELSE 'data' end as File_Type, CAST (. size as float)/128 AS [Size (MB)] -- Size is in the unit of page (8 KB), c. [TotalSize (MB)],. physical_Name AS File_PathFROM sys. master_files aINNER JOIN sys. databases B ON. database_id = B. database_idINNER JOIN cte_TotalSize c ON. database_id = c. database_id -- scalar quantum query (34%) SELECT. database_id AS [DB_ID], B. name AS [DB_Name], B. create_date, CASE. type WHEN 1 THEN 'log' ELSE 'data' end as File_Type, CAST (. size as float)/128 AS [Size (MB)] -- Size is in the unit of page (8 KB), (select cast (SUM (size) as float)/128 FROM sys. master_files WHERE database_id =. database_id) AS [TotalSize (MB)],. physical_Name AS File_Path FROM sys. master_files aINNER JOIN sys. databases B ON. database_id = B. database_id -- Window Function (19%) SELECT. database_id AS [DB_ID], B. name AS [DB_Name], B. create_date, CASE. type WHEN 1 THEN 'log' ELSE 'data' end as File_Type, CAST (. size as float)/128 AS [Size (MB)] -- Size is in the unit of page (8 KB), CAST (SUM (size) OVER (partition by. database_id) as float)/128 AS [TotalSize (MB)],. physical_Name AS File_Path FROM sys. master_files aINNER JOIN sys. databases B ON. database_id = B. database_id

Result:

The above three methods show positive results. The less generation, the better the performance ..

Among them, the third is the use of the window function, the relationship between: http://msdn.microsoft.com/zh-cn/library/ms189461.aspx

In SQL, how does one insert a column in a known table and the value of the newly inserted column is obtained by the function?

Insert into Table2 (field1, field2,...) select value1, value2,... from Table1
Then sum can be used in the select statement.

Insert into (Department name, Department Head, total number of students) select (@ Department name, @ Department Head, sum (number of students) form-Major a where. system name = @ system name)

Problem hi me

The SQL function concatenates a column into a string.

Custom Functions

CREATE function ABC (@ tablename varchar (100 ))
Returns varchar (2000)
As

Select name from tbl_Person
Concatenate name into a string in a loop
Returns string

The loop does not write a cursor.