SQL-Server

前段时间公司的开发服务器从内部服务器转移到了一台公网的 Azure 服务器，SQL Server 也变成公网开放，因此其面临的安全性风险骤增。为了提升安全性，数据库用户现在没有 Sys Admin 权限，仅有权访问少数数据库。这带来一个问题就是数据库重启以后，TempDB 数据库被重建，非 Sys Admin 角色的数据库用户就失去了操作 TempDB 的权限，进而导致一些需要 TempDB 数据库操作的操作失败。

解决的问题有两种，一种就是将数据库用户添加到 Model 数据库，因为所有新建数据库都是复制自 Model 数据库，因此重建 TempDB 数据库就自带了需要的数据库用户。这种做法的缺点也很明显，任何新建数据库都默认自带那些数据库用户，这可能并不是我们期望的。第二种方法是我们设立一个自动任务，每次 TempDB 数据库重建，我们就自动给它添加上需要的数据库用户。这里我们介绍一下如何实现第二种方法。

我们假设为登录用户 abc 在 TempDB 数据库中创建对应用户并赋予它 TempDB 数据库的 db_owner 角色。代码如下：

1
2
3

use TempDB
create user abc from login abc
exec sp_addrolemember 'db_owner', abc

接下来，我们利用 SQL Server Agent 设立一个自动任务来运行上面的脚本，Schedule Type 选为”Start automatically when SQL Server Agent starts”即可。

最近在研究自动部署的问题。其中一个需求是能否对数据库进行快速地备份和还原操作。传统的完全备份和还原非常耗时，不太可取，我知道自从SQL Server 2005 以来，SQL Server 提供了快照功能，可以快速建立起一个只读快照，那么我们能否把这个快照作为备份并用来还原数据库呢？答案是：可以！不过这种备份、还原方式有若干限制，比如，需要还原的数据库必须只有一个快照，其它限制我也忘了，似乎对我不构成问题。下面来具体说明如何操作，假设我们打算对数据库 MyDb 创建一个快照 MyDBSnapshot，我们还假设数据库只有一个逻辑数据文件。

一个从字符串中提取数字的函数：

1
2
3
4
5
6
7
8
9
10

CREATE FUNCTION [dbo].[GET_NUMBER](@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
    WHILE PATINDEX('%[^0-9-.]%',@S) > 0
    BEGIN
        SET @S = STUFF(@S, PATINDEX('%[^0-9-.]%', @S), 1, '')
    END
    RETURN @S
END

用例：

1
2
3

SELECT dbo.GET_NUMBER('1q2W399E4987r')

-- 得到结果： 123994987

当然还是靠 DateDiff 函数，这样做：DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0), 我觉得可以创建一个函数来封装一下以方便使用。

这种方法的来自于 stackoverflow 上的同样问题的一个答案

有一个需求，需要查询SQL Server里一个表中某个字符串类型的字段中包含下划线的记录。很自然会想到这样的代码：

1

SELECT * FROM MyTable WHERE MyCol LIKE '%_%'

结果证明这种写法是错误的。原谅我SQL编程的功力已经极大的退化了。原来下划线 _ 在 LIKE 子句中代表“一个”任意字符，而我需要将它作为普通字符查询，怎么做呢？有2种方法：

中括号

万能的中括号 （[、]） 可以做到：

1

SELECT * FROM MyTable WHERE MyCol LIKE '%[_]%'

ESCAPE 语句

可以用 ESCAPE 语句指定一个转义字符，然后 LIKE 语句中这个转义符后面的一个字符将作为普通字符处理：

1
2
3
4

SELECT * FROM MyTable WHERE MyCol LIKE '%\_%' ESCAPE '\'
-- or
SELECT * FROM MyTable WHERE MyCol LIKE '%^_%' ESCAPE '^'
-- 可以试试其它字符当转义符，效果都一样

完整的例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

CREATE TABLE Test
(
    Col VARCHAR(100)
)

INSERT INTO Test
VALUES
('A'),
('B'),
('A_B'),
('X_Y_Z'),
('123')

SELECT *
FROM Test
WHERE Col LIKE '1_3'

SELECT *
FROM Test
WHERE Col LIKE '%[_]%'

SELECT *
FROM Test
WHERE Col LIKE '%\_%' ESCAPE '\'

DROP TABLE Test