sql server 约束、索引和排查连接数及耗资源SQL

一.约束

1.主键约束（PRIMARY KEY）

ALTERTABLE数据表名ADDCONSTRAINTPK_ID--(主键名称)PRIMARYKEY(ID)--(列名)

2.外键约束（FOREIGN KEY）

ALTERTABLE从表名称ADDCONSTRAINTFK_SID--(命名一个外键名称)--添加外键约束FOREIGNKEY(StuID)REFERENCES主表名称(ID)

3.唯一约束（UNIQUE）

让字段数据唯一

ALTERTABLE表名ADDCONSTRAINTQU_Name--(约束名称)UNIQUE(列名)

4.非空约束（NOT NULL）

确定这个字段中的数据必须不能为空

ALTERTABLE表名ALTERCOLUMNIDINTNOTNULL

5.检查约束（CHECK）

设置这个字段中的数据特性

ALTERTABLE表名ADDCONSTRAINTCK_SEX--(命名一个检查约束的名称)CHECK(SEXIN('男','女'));

6.默认约束（DEFAULT ）

若在表中定义了默认值约束，用户在插入新的数据行时，如果该行没有指定数据，那么系统将默认值赋给该列，如果我们不设置默认值，系统默认为NULL。

1. 修改字段约束

ALTERTABLE表名ADDCONSTRAINTDF_XXXDEFAULT1FOR字段名

2. 添加字段并赋默认值

ALTER表名ADD字段名INTnotnullDEFAULT1

3. 删除约束

ALTERTABLE表名DROPCONSTRAINTCK_AGE

7.查看约束

SELECT*FROMsysobjectsWHEREOBJECT_NAME(parent_obj)='表名'

二.索引

1.创建非集群索引

CREATENONCLUSTEREDINDEXIX_表_列ON表名(列);

2.重新组织索引（用于减少索引碎片）

--碎片 > 30% → REBUILD--碎片 5%~30% → REORGANIZE--碎片 < 5% → 什么都不做ALTERINDEX索引名ON表名 REORGANIZE;

智能重建/重组脚本

DECLARE@TableNameNVARCHAR(255),@IndexNameNVARCHAR(255),@FragPercentFLOAT;DECLAREidx_cursorCURSORFORSELECTOBJECT_NAME(ips.object_id),i.name,ips.avg_fragmentation_in_percentFROMsys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED')ipsJOINsys.indexes iONi.object_id=ips.object_idANDi.index_id=ips.index_idWHEREips.avg_fragmentation_in_percent>5ANDips.page_count>500;OPENidx_cursor;FETCHNEXTFROMidx_cursorINTO@TableName,@IndexName,@FragPercent;WHILE@@FETCH_STATUS=0BEGINIF@FragPercent>30EXEC('ALTER INDEX ['+@IndexName+'] ON ['+@TableName+'] REBUILD');ELSEEXEC('ALTER INDEX ['+@IndexName+'] ON ['+@TableName+'] REORGANIZE');FETCHNEXTFROMidx_cursorINTO@TableName,@IndexName,@FragPercent;ENDCLOSEidx_cursor;DEALLOCATEidx_cursor;

3.删除索引

DROPINDEX表名.索引名;

三.排查连接数及耗资源SQL

USEmaster GO-- 数据库用户连接SELECT*FROMsys.[sysprocesses]WHERE[spid]>50ANDDB_NAME([dbid])='表名'orderbycpudescSELECTCOUNT(*)FROM[sys].[dm_exec_sessions]WHERE[session_id]>50--查看各项指标是否正常，是否有阻塞，这个语句选取了前N个最耗CPU时间的会话SELECTTOP50[session_id],[request_id],[start_time]AS'开始时间',[status]AS'状态',[command]AS'命令',dest.[text]AS'sql语句',DB_NAME([database_id])AS'数据库名',[blocking_session_id]AS'正在阻塞其他会话的会话ID',[wait_type]AS'等待资源类型',[wait_time]AS'等待时间',[wait_resource]AS'等待的资源',[reads]AS'物理读次数',[writes]AS'写次数',[logical_reads]AS'逻辑读次数',[row_count]AS'返回结果行数',[cpu_time]FROMsys.[dm_exec_requests]ASderCROSSAPPLYsys.[dm_exec_sql_text](der.[sql_handle])ASdestWHERE[session_id]>50ANDDB_NAME(der.[database_id])='表名'ORDERBY[cpu_time]DESC