sql 常用语句备份
新增字段,默认其他字段计算
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