SQLSERVER相关修改记录

1.创建索引执行语句：
CREATE NONCLUSTERED INDEX idx_iot_senddate ON Tabel1(表名) (IOT_TerminalNo,SendDate);

2.同步修改字段值

UPDATE t1
SET t1.Wps_ID = t2.Wps_ID
FROM Project_Technology t1
INNER JOIN Project_Technology_Welding t2 ON t1.MGuID = t2.MGuID
WHERE t1.Status =0 and t1.MGUID = t2.MGUID;

3.sql表新增字段
alter table File_Project_Test add [Synch] int DEFAULT ((1)) NULL;

4.查看数据sql查询cpu占比较高的语句：

SELECT TOP 10 s.session_id,
r.status,
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid))
+ N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

5.查询当天每分钟的时间值sql
SELECT DATEADD(MI,number,'2024-06-26') MinuteTime from master..spt_values WITH(NOLOCK) where type = 'p' and DATEADD(MI,number,'2024-06-26') < '2024-06-27'

 
6.查询当天的每个小时值显示sql
SELECT datepart(HOUR,DATEADD(HOUR,NUMBER,'2024-06-26')) Hour FROM master..spt_values WITH(NOLOCK) where type = 'p' AND NUMBER < DateDiff(HOUR, '2024-06-26', '2024-06-27');