sql 常用语句备份

更新表统计，提高执行计划准确性，降低逻辑读取、IO，降低CPU占用

exec sp_updatestats

 

新增字段，默认其他字段计算

ALTER TABLE 表名 add 字段名 as 字段名1+字段名2

 

SQL查看变量的数据类型

DECLARE @Sum int
--SET @Sum = 0
SELECT @Sum, CAST(SQL_VARIANT_PROPERTY(@Sum, 'BaseType') AS VARCHAR(50))

 

查询临时表是否存在

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#CC') and type='U')

查询某个字段是否存在

IF COL_LENGTH('TBHotelPolicyControl','Flag') IS NULL

 

查询某个字段在哪张表

select sys.objects.name,* from sys.columns
 inner join sys.objects on sys.columns.object_id = sys.objects.object_id
where sys.columns.name = '字段名'

 

排序规则，可以在查询，建表时制

SELECT TBCITY.code collate Chinese_PRC_BIN FROM TBCITY

collate Chinese_PRC_BIN，指定按某种方式排序

 

关闭自动增长

SET IDENTITY_INSERT TableName OFF

 

游标

create table #A
(
Number varchar(50)
,Name varchar(50)
)
insert into #A
select '123456','Name1'
union
select '123456','Name2'
create table #B
(
Number varchar(50)
,Name varchar(50)
)

declare @Number varchar(50)
declare @Name varchar(50)
DECLARE myCursor CURSOR
FOR (SELECT * FROM #A)
OPEN myCursor
FETCH NEXT FROM myCursor into @Number,@Name
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #B values(@Number,@Name)
FETCH NEXT FROM myCursor into @Number,@Name
END
CLOSE myCursor
DEALLOCATE myCursor

select * from #B

 

--索引

CREATE NONCLUSTERED INDEX IX_HR_EXP_CLAIM_HEAD_tx_date  --创建一个非聚集索引 ON HR_EXP_CLAIM_HEAD(tx_date)       --为TEST表的TNAME字段创建索引 WITH FILLFACTOR = 50         --填充因子为50% GO

DBCC DBREINDEX (HR_EXP_CLAIM_HEAD,IX_HR_EXP_CLAIM_HEAD_tx_date) UPDATE STATISTICS HR_EXP_CLAIM_HEAD

-- 删除相同留一条

select comp_code,region,nature,exp_code from TBEXPENSES_ALLOCATION
 where comp_code='CNABB'
 group by comp_code,region,nature,exp_code having count(*) > 1

 

 

================= 清理Log===================

USE [CSFramework3.Normal.jhzls]

GO

SELECT file_id, name FROM sys.database_files;

GO

--简单模式
USE [master]
GO
ALTER DATABASE [CSFramework3.Normal.jhzls] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [CSFramework3.Normal.jhzls] SET RECOVERY SIMPLE
GO
USE [CSFramework3.Normal.jhzls]
GO
DBCC SHRINKFILE (N'CSFramework3_Normal_Log' , 11, TRUNCATEONLY)

GO

 

--还原为完全模式
USE [master]
GO
ALTER DATABASE [CSFramework3.Normal.jhzls] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [CSFramework3.Normal.jhzls] SET RECOVERY FULL
GO

 

 

-- 察看占用进程

exec sp_who

kill 123 

-- 查询所有表记录数

select object_name(i.id) TableName

        ,rows as RowCount

  from sysindexes i

 inner join sysobjects o on (o.id=i.id and xtype='U')

 order by RowCount desc

 

-- 查询索引碎片

 

use DATABASE_NAME;
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and dbindexes.[name] not like 'pk_%'
ORDER BY indexstats.avg_fragmentation_in_percent * page_count desc

 

-- 重建索引

-- FILLFACTOR 填充因子，只读的表可以设置为100。读写各半80-90之间。高更改的表50到70之间

use DATABASE_NAME;

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+name+']' AS TableName
FROM sys.tables w
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (ONLINE=off,FILLFACTOR=80);' --' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
exec (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

 

-- 函数 sys.dm_db_index_physical_stats

SELECT * FROM  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

 

-- 数据清理日志

alter database ItemaData set recovery simple;
go
dbcc shrinkfile('ItemaData_log',10,truncateonly);
go
alter database ItemaData set recovery full with no_wait;
go
alter database ItemaData set recovery full;
go