【数据库】一篇文章搞掂：SQL Server数据库

问题：

1、同一段代码，在存储过程中运行比普通SQL执行速度慢几十倍

原理：

在SQL Server中有一个叫做 “Parameter sniffing”参数嗅探的特性。SQL Server在存储过程执行之前都会制定一个执行计划，导致速度较慢。

解决方式：

我在实际中只使用了方法1，即可解决问题

1、在存储过程中创建一个变量替换掉参数

CREATE PROC sp_yp_jxctj
    @varTemp1 VARCHAR(50)
AS
    BEGIN
--用变量替换掉参数，以防出现“Parameter sniffing”问题
        DECLARE @var1 VARCHAR(50) = @varTemp1 
--下面对参数的引用，都引用@var1，不要引用@varTemp1

    END; 

2、将受影响的sql语句隐藏起来，比如：

   a) 将受影响的sql语句放到某个子存储过程中，比如我们在@thedate设置成为今天后再调用一个字存储过程将@thedate作为参数传入就可以了。

   b) 使用sp_executesql来执行受影响的sql。执行计划不会被执行，除非sp_executesql语句执行完。

   c) 使用动态sql（”EXEC(@sql)”来执行受影响的sql。

2、数据库日志文件过大，备份后还原耗时长

解决办法：

1、分离数据库——>删除日志文件——>附加数据库

缺点：风险大，过程久，有时候分离不成功

2、在SQL2008中清除日志就必须在简单模式下进行，等清除动作完毕再调回到完整模式，一定必务要再改回完整模式，不然数据库就不支持时间点备份了。

• 选择数据库–属性—选项—恢复模式–选择简单。
• 收缩数据库后，再调回完整
• 使用命令

USE[master]
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE   --简单模式
GO
USE 要清理的数据库名称
GO
DBCC SHRINKFILE (N'要清理的数据库名称_log' , 2, TRUNCATEONLY)  --设置压缩后的日志大小为2M，可以自行指定
GO
USE[master]
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL  --还原为完全模式
GO

 实例

1、保留小数

Convert(decimal(18,2),@num) 

2、收缩数据库日志

USE [master]
GO
ALTER DATABASE DBName SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE DBName SET RECOVERY SIMPLE
GO
USE DBName
GO
DBCC SHRINKFILE (N'DBName_Log' , 11, TRUNCATEONLY) 
--GO 查找日志名称
--USE DBName
--SELECT file_id, name FROM sys.database_files;
GO
USE [master]
GO
ALTER DATABASE DBName SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE DBName SET RECOVERY FULL
GO

3、查找数据库中某字段在哪些表有出现

SELECT COLUMN_NAME,TABLE_NAME FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME='test_name'

4、查找数据并存放到临时表

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#临时表名') and type='U')
DROP TABLE #临时表名
select * insert into #临时表名 from A

5、创建临时表

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#物料汇总表') and type='U')
DROP TABLE #物料汇总表
create table #物料汇总表 
(
    [Recnum] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
    记录 [decimal](18, 0) ,
    需求类型 [nvarchar](200) NULL,
    库存总量 decimal(26, 8) NULL default 0
)

6、select行号

select IDENTITY(int, 1,1) as 行号 

7、select分组编号

select row_number()over(partition by 分组列 order by 排序列) From A

8、查找小写字母

SELECT * FROM @t WHERE v LIKE '%[a-z]%' COLLATE chinese_prc_bin

9、时间相关

declare @日期 datetime
set @日期 = getdate()
select @日期
select @日期 = CONVERT(varchar(100), @日期, 111)
select @日期
select @日期 = CONVERT(datetime,CONVERT(varchar(100), @日期, 111))
select @日期
select @日期 = DATEADD(DAY,1,@日期)
select @日期
select DATEDIFF(DAY,@日期,getdate())

10、自定义排序

select * from teble_exaple order by
(
    case class 
        when 'C' then 1,
        when 'A' then 2
    else '' end
)           

11、游标写法

    --申明一个游标
    DECLARE TotalCursor CURSOR    
        FOR select 编号,数量,内容 from  #Print_ProductQRCode记录表
    --打开一个游标    
    OPEN TotalCursor
    --循环一个游标
    DECLARE @编号 nvarchar(300),@数量 int,@明细内容 nvarchar(MAX)
        FETCH NEXT FROM TotalCursor INTO @编号,@数量,@明细内容
    WHILE @@FETCH_STATUS =0
        BEGIN
                
            while @数量>0
            begin
                insert into #Print_ProductQRCode打印内容
                select null,@编号,@明细内容
                set @数量 = @数量 - 1
            End
        
            FETCH NEXT FROM TotalCursor INTO @编号,@数量,@明细内容
        END    
    --关闭游标
    CLOSE TotalCursor
    --释放资源
    DEALLOCATE TotalCursor

12、分组链接字符串

Select ItemNo,stuff(
(select ','+ValueName from ItemAttributeSet   where ItemNo=A.ItemNo
for XML path('')),1,1,''
)
 from ItemAttributeSet A
group by ItemNo

 13、查看数据库ID

select database_id,name,physical_name AS CurrentLocation,state_desc,size from sys.master_files    
where database_id=db_id(N'数据库名');