sqlserver 数据库

sqlserver 数据相关

数据库索引碎片优化

--可以通过执行以下T-SQL脚本来REORGANIZE所有表索引碎片大于5%的索引：
--创建变量
DECLARE @tname NVARCHAR(256)
DECLARE @Iname NVARCHAR(256)
DECLARE @avgScrap FLOAT
DECLARE @str varchar(500)
-- 创建游标
DECLARE cur_tables CURSOR FOR
SELECT t.name AS TableName,i.name AS IndexName,s.avg_fragmentation_in_percent AS avgScrap
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.tables AS t ON i.object_id = t.object_id
WHERE s.avg_fragmentation_in_percent > 5 AND i.index_id <> 0
-- 打开游标循环遍历所有表索引
OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @tname, @Iname, @avgScrap
-- 当游标循环结束之后 @@FETCH_STATUS = -1，当还有下一行数据的时候，@@FETCH_STATUS = 0
WHILE @@FETCH_STATUS = 0
BEGIN
-- if @avgScrap>=30 --如果碎片大于30,重建索引
-- begin
-- -- set @str='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' rebuild'
-- end
-- else --如果碎片小于30,重新组织索引
begin
set @STR='alter index '+rtrim(@Iname)+' on dbo.'+quotename(rtrim(@tname))+' reorganize'
end
print @str
exec (@str) --执行
FETCH NEXT FROM cur_tables INTO @tname, @Iname, @avgScrap
END
-- 关闭游标
CLOSE cur_tables
-- 删除游标
DEALLOCATE cur_tables

sqlserver表

 创建表
if exists (select TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'testTable')
   drop table testTable
go

CREATE TABLE testTable (
  [id] int identity(1,1) not null PRIMARY KEY,
  [mer_no] nvarchar(40)   NULL
)  

添加主键
ALTER TABLE gw_trade_flag_anti_money ADD CONSTRAINT PK_Employees PRIMARY KEY (id);
添加表字段
alter table GW_Merchant add v_bin_white nvarchar(3) COLLATE Chinese_PRC_CI_AS DEFAULT ('N') NULL;
修改字段类型
ALTER TABLE gw_merchant_whiteList ALTER COLUMN card_no nvarchar(200);
添加字段注释   
execute sp_addextendedproperty 'MS_Description','字段备注信息','user','dbo','table','字段所属的表名','column','添加注释的字段名';  
修改字段注释   
execute sp_updateextendedproperty 'MS_Description','修改的注释内容','user','dbo','table','testTable','column','testRecord';  
删除字段注释  
execute sp_dropextendedproperty 'MS_Description','user','dbo','table','testTable','column','testRecord'; 
添加表注释  
execute sp_addextendedproperty 'MS_Description','注释-些表为测试表','user','dbo','table','testTable',null,null;  
修改表注释  
execute sp_updateextendedproperty 'MS_Description','修改注释-测试表','user','dbo','table','testTable',null,null; 
删除表注释  
execute sp_dropextendedproperty 'MS_Description','user','dbo','table','testTable',null,null; 
  
删除索引
if exists(select * from sysindexes where name='NON_INDEX')
drop index NON_INDEX on gw_Cardholder;
go

添加非聚合复合索引
CREATE NONCLUSTERED INDEX IDX_1 ON gw_merchant_whiteList_test 
(mer_no,card_no,refusals_number,trans_number, website_reg_time, pay_amount, approval_status);
创建表聚焦索引
CREATE UNIQUE CLUSTERED INDEX PK__gw_Group__3213E83E39D87308  ON gw_Group (id) WITH (DROP_EXISTING = ON);   
添加索引注释
EXEC sp_addextendedproperty
'MS_Description', N'去重符合索引','SCHEMA', N'dbo','TABLE', N'gw_merchant_whiteList_test','INDEX', N'IDX_1'
    
行转,拼接列
SELECT STUFF((SELECT ',' + tradeNo from gw_capture_recordInfo  r where r.captureBatchNo=b.captureBatchNo and exType in (1,2) FOR xml path('')),1,1,'')  

添加函数转in条件
--将字符串根据逗号分割成数组表用于in，例如字符串’1,2,3,4,5’
IF OBJECT_ID (N'dbo.StrToTable', N'FN') IS NOT NULL  
    DROP FUNCTION StrToTable;  
GO  
CREATE FUNCTION dbo.StrToTable(@str varchar(1000)) 
Returns @tableName Table 
( 
    str2table varchar(50) 
) 
As 
BEGIN  
    set @str = @str+ ',' 
    Declare @insertStr varchar(50) --截取后的第一个字符串 
    Declare @newstr varchar(1000) --截取第一个字符串后剩余的字符串 
    set @insertStr = left(@str,charindex(',',@str)-1) 
    set @newstr = stuff(@str,1,charindex(',',@str),'') 
    Insert @tableName Values(@insertStr) 
    while(len(@newstr)>0) 
    begin 
        set @insertStr = left(@newstr,charindex(',',@newstr)-1) 
        Insert @tableName Values(@insertStr) 
        set @newstr = stuff(@newstr,1,charindex(',',@newstr),'') 
    end 
    Return;  
END;    

数据库性能

查询数据库耗时超长的脚本
SELECT TOP 20
    total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
    qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
    last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
    SUBSTRING(qt.text,qs.statement_start_offset/2+1, 
        (CASE WHEN qs.statement_end_offset = -1 
        THEN DATALENGTH(qt.text) 
        ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) 
    AS [使用CPU的语法], qt.text [完整语法],
    dbname=db_name(qt.dbid),
    object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY  total_worker_time DESC

--查询表索引
SELECT 
    c.name AS PrimaryKeyColumn, i.name AS IndexName, i.type_desc AS IndexType, t.name AS TableName
FROM 
    sys.tables t
INNER JOIN 
    sys.indexes i ON t.object_id = i.object_id
INNER JOIN 
    sys.index_columns ic ON ic.object_id = t.object_id AND ic.index_id = i.index_id
INNER JOIN 
    sys.columns c ON c.object_id = t.object_id AND c.column_id = ic.column_id
WHERE 
    t.name = 'gw_refundrequest' -- 替换为你的表名
AND 
    i.is_primary_key = 1;      

循环数组在循环游标

 定义遍历的商户变量@merNo和数据表游标merNos
DECLARE @merNoList VARCHAR(1000)  --处理结果的变量
SET @merNoList='1030,1037,1050,1052,1053,1054,1062,1068,1077,1082,1084,1087,1139,1155,1170,1174,1179,1188,1192,1199,1224,1225,1226,1227,1228,1229,1230,1231,1232,1085,1100,1160,1206,1233,1104,1201,1219,1220,1234,1235,1237'
DECLARE @createTime DATETIME
set @createTime = GETDATE()
PRINT '当前时间：'+CONVERT(VARCHAR(100),@createTime,121)
DECLARE @next int
SET @next= 1
DECLARE @merNo VARCHAR(100)
WHILE @next <= dbo.Get_StrArrayLength(@merNoList ,',')
 BEGIN 
    SET @merNo  = dbo.Get_StrArrayStrOfIndex(@merNoList ,',',@next) -- 根据索引获取商户号
		--执行添加白名单
		update gw_vip_card set cardNo = LEFT(cardNo, 6) + '****' + RIGHT(cardNo, 4) where merNo =@merNo and  cardNo like '%***%'  AND cardNo not like '%****%' ;
		SET @next=@next+1 -- 	设置索引+1
		PRINT @merNo+'已执行完毕'
  END