【数据库】一篇文章搞掂：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](, ) IDENTITY(,) NOT NULL,

    记录 [decimal](, ) ,

    需求类型 [nvarchar]() 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'数据库名');

巴特西