anydragon - 50 중 15 번째 페이지 - mac, linux, aws, c, c++, mysql, mssql, redis, csharp, nodejs, rust, golang

MSSQL History

SELECT
	db_name(st.dbid) DBName
	, object_schema_name(objectid, st.dbid) SchemaName
	, object_name(objectid, st.dbid) SPName
	, qs.total_elapsed_time
	, creation_time
	, last_execution_time
	, text
FROM
	sys.dm_exec_query_stats qs
CROSS APPLY
	sys.dm_exec_sql_text(qs.plan_handle)st
JOIN
	sys.dm_exec_cached_plans cp
ON
	qs.plan_handle = cp.plan_handle

통계용 쿼리 샘플

select year as '년', month as '월', count(*) as count
from (
	select
		YEAR(us.fd_dtcreate) as 'year',
		MONTH(us.fd_dtcreate)  as 'month'
	from tb_ykfile as us
	where us.fd_dtCreate > '2020-08-01' and us.fd_dtCreate <= '2022-01-01'
) tb
group by year, month
order by year, month
;

SELECT    CONVERT(varCHAR(10),Dateadd(d, a.number,'2020-08-01 00:00:00'),120) AS dt
          ,Isnull(b.count, 0)                            AS count
FROM      master..spt_values a 
LEFT JOIN 
          ( 
                   SELECT   count(*)                    AS count, 
                            CONVERT(CHAR(10), b1.fd_dtcreate,120) AS fd_dtcreate
                   FROM     tb_ykfile b1 WITH(nolock) 
                   GROUP BY CONVERT(CHAR(10), b1.fd_dtcreate, 120)
		 ) b 
ON        CONVERT(CHAR(10), Dateadd(d, a.number,'2020-08-01 00:00:00'), 120) = b.fd_dtcreate 
WHERE TYPE = 'P' AND CONVERT(CHAR(10), DATEADD(D, NUMBER, '2020-08-01'), 120) <= '2022-01-01'
order by dt
;

MSSQL Paging

          SELECT row_number() OVER(ORDER BY fd_nIdService DESC) AS PagedNumber, *
          FROM [tb_Service] AS [t]
          WHERE 1=1
          ORDER BY [fd_nIdService] DESC
          OFFSET ((2 - 1) * 10) ROWS FETCH NEXT 10 ROWS ONLY