Loveyuki's BLOG

SQL Server 2008 带的 Management Studio 生成脚本功能已经支持编写数据的脚本了，但是 2008 R2 里面找了半天没找到编写数据脚本的选项，后来仔细一看，原来藏这里了。

相对于 2008 来说，更加了灵活了。想生成啥就生成啥。

前两天我发了一个使用 CTE 分割字符串的方法，效率虽然比旧的 CHARINDEX 高一些，但是比起 CLR 速度还是差了很多，今天我就发下使用 CLR 来分割字符串的方法

public class UserFunction 
{ 
    [SqlFunction(Name = "CLR_SplitStringToIntTable", TableDefinition = "Id INT", FillRowMethodName = "FillRow")] 
    public static IEnumerable SplitStringToIntTable(SqlString str) 
    { 
        return str.Value.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); 
    } 
 
    public static void FillRow(object row, out int id) 
    { 
        id = Convert.ToInt32((string)row); 
    } 
} 

把上面的 C# 代码编译后放到 SQL 服务器，然后到 SQL 管理器里面建立程序集，再建立一个新的函数

CREATE FUNCTION CLR_SplitStringToIntTable 
(    
    @Str NVARCHAR(MAX) 
) 
RETURNS TABLE (Id INT) 
AS EXTERNAL NAME SQLServerCLR.UserFunction.SplitStringToIntTable; 
GO 

效率对比我就不贴了，我这里提高的速度可不是一点。

记录一下，效率比旧的字符串分割效率高

ALTER FUNCTION [dbo].[UF_SpliteIntToTable] 
( 
    @String VARCHAR(8000) 
) 
RETURNS @Table TABLE (Id INT) 
AS 
BEGIN 
    DECLARE @SpliteChar AS CHAR(1) = ','; 
         
    WITH TempTable AS 
    ( 
        SELECT CAST(SUBSTRING(@String,1,CHARINDEX(@SpliteChar,@String,1) - 1) AS INT) AS item, 
        SUBSTRING(@String,CHARINDEX(@SpliteChar,@String,1) + 1, LEN(@String)) + @SpliteChar AS list 
 
        UNION ALL 
 
        SELECT CAST(SUBSTRING(list,1,CHARINDEX(@SpliteChar,list,1) - 1) AS INT) AS item, 
        SUBSTRING(list,CHARINDEX(@SpliteChar,list,1) + 1, LEN(list)) AS list 
        FROM TempTable 
        WHERE LEN(TempTable.list) > 0 
    ) 
 
    INSERT INTO @Table SELECT item FROM TempTable WHERE item > 0 
         
    RETURN 
END 

图片文件导入数据库用：

INSERT INTO tblFiles(blob) 
    SELECT * FROM OPENROWSET(BULK 'D:\BakFile\sfile\1.png', SINGLE_BLOB) 
    AS tmpImage 

数据库字段导出图片文件用：

SET @sql = 'bcp ' 
SET @sql = @sql + '"SELECT blob FROM SomeTestDB.dbo.tblFiles WHERE id= 1"' 
SET @sql = @sql + ' queryout ' 
SET @sql = @sql + '"D:\BakFile\tfile\1.png"' 
SET @sql = @sql + ' -T -f D:\BakFile\tfile\bcp.fmt -S' 
     
exec master..xp_cmdshell @sql 

bcp.fmt 文件可以自动生成后进行定制修改：

SET @sql ='bcp SomeTestDB.dbo.tblFiles format nul -T -N  -f D:\BakFile\tfile\bcp.fmt' 
exec master..xp_cmdshell @sql 

生成的文件如下：

10.0 
2 
1       SQLINT              0       4       ""   1     id           "" 
2       SQLIMAGE            4       0       ""   2     blob         "" 

因为我们只导出 blob 字段为图片文件，所以修改为：

10.0 
1 
1       SQLIMAGE            0       0       ""   1     blob         "" 

具体大家可以看 MSDN 文档

推荐每次备份数据库以后进行一次，要获得好的 DBCC 性能的一些建议

• 在系统使用率较低时运行 CHECKDB。
• 请确保未同时执行其它磁盘 I/O 操作，例如磁盘备份。
• 将 tempdb 放到单独的磁盘系统或快速磁盘子系统中。
• 允许 tempdb 在驱动器上有足够的扩展空间。使用带有 ESTIMATE ONLY 的 DBCC 估计 tempdb 将需要多少空间。
• 避免运行占用大量 CPU 的查询或批处理作业。
• 在 DBCC 命令运行时，减少活动事务。
• 使用 NO_INFOMSGS 选项显著减少处理和 tempdb 的使用。

USE master 
DECLARE @dbName VARCHAR(255) 
SET @dbName='需要修复的数据库实体的名称' 
EXEC SP_DBOPTION @dbName, N'single', N'true'   --单用户状态 
DBCC CHECKDB(@dbName,REPAIR_ALLOW_DATA_LOSS) 
DBCC CHECKDB(@dbName,REPAIR_REBUILD) 
EXEC SP_DBOPTION @dbName, N'single', N'false'  --多用户状态