贝网博客

我的分类
流水记事
源码下载
Asp.net
其它
数据库
Javascript
.Net技术
我的相册
友情链接
博客园
CSDN博客
Start0
最新回复
嗷嗷的cCC
fasdfasdf
[:..
lz这个东西好厉害,我..
哈哈,好照片
不错,以前一直用黄色..
终于找到支持ff的修正..
终于找到支持ff的修正..
新鲜性
看看,试试,好不好使。
数据库 日志列表    
本站一共有博客83条,当前显示14条
回复:1  发表于:2010-09-13 19:58:06
更新于:2011-11-18 10:31:45
介绍如何在SqlServer里进行Sql的格式化,方便理解别人写的Sql
是图文并茂的哦
回复:1  发表于:2010-11-17 10:23:32
更新于:2011-11-18 10:31:26

今天遇到个问题,需求是这样的,要从用户表里取得注册时间为今天的记录,用于另外一个表的关联,用户表结构:
id             [bigint] IDENTITY(1,1),
regtime  [datetime] 
id为主键,并在regtime上建了索引名为:idx_regtime

下面是取得注册时间为今天的用户的sql:
DECLARE @Today FLOAT;
SET @Today = CAST(GETDATE() AS FLOAT);  -- 把今天转换为距1900年1月1日间隔的天数用于获取今天注册的用户
SELECT id FROM [user] WHERE regtime BETWEEN FLOOR(@Today) AND @Today

 在数据库里查看执行计划,上面的语句用到了idx_regtime这个索引
但是,在关联别的表的时候,很奇怪的是,昨天测试会用到索引,今天测试又是全表扫描了,关联的sql如下:
DECLARE @Today FLOAT;
SET @Today = CAST(GETDATE() AS FLOAT);  -- 把今天转换为距1900年1月1日间隔的天数用于获取今天注册的用户
SELECT TOP(11) a.* FROM tbFriend a,
 (SELECT id FROM [user] WHERE regtime BETWEEN FLOOR(@Today) AND @Today) b
where a.userid = b.id

上面的sql在查看执行计划时,里面的嵌套语句却没有使用idx_regtime这个索引,导致效率极低,暂时还不明白原因
于是给关联语句强制使用索引,添加WITH(INDEX(idx_regtime)),最后的sql就是:
DECLARE @Today FLOAT;
SET @Today = CAST(GETDATE() AS FLOAT);  -- 把今天转换为距1900年1月1日间隔的天数用于获取今天注册的用户
SELECT TOP(11) a.* FROM tbFriend a,
 (SELECT id FROM [user] WITH(INDEX(idx_regtime)) WHERE regtime BETWEEN FLOOR(@Today) AND @Today) b
where a.userid = b.id

 发表于:2011-03-08 15:49:38
更新于:2011-11-18 10:28:47

 有机会尝试大数据量的维护工作,在这里记录收集一些个人的心得体会吧(不一定正确哦,所以有错误请多多指正)

1、建表时聚集索引的选择
因为聚焦索引会影响到数据的存储,所以一般尽量使用自增id作为聚集索引,而原计划的比如Varchar型的键值,可以做成唯一索引
比如用户表,登录帐号如果做成聚集索引,那么新增用户时,可能造成数据迁移,以便方便新数据插入到合适的位置

2、关于联合索引
因为索引也是会占用空间的,而联合索引自然会占用更多的空间,所以联合索引在不必要的情况下,还是不要创建,改成建单字段索引就好,
比如学生属性表,有字段:所属系、所属班,可以创建2个索引,一个所属系的索引,一个所属班的索引
如果条件是查找某系某班的学生,那么会用到所属系的索引,这里也可以创建系与班的联合索引,但是通常情况下,一个系下的班的数量很小,全部扫描,IO也不会太大,所以可以不创建联合索引(例子好像不太合适,先这样吧)

3、关于级联更新
有时,我们需要根据另外一个表,批量更新某个表的数据,比如:
UPDATE a SET a.name = b.name 
FROM tb1 a, tb2 b
WHERE a.a1 = b.b1 AND a.a2 = b.b2
上面的语句如果遇到并发比较高的情况,很容易出现死锁,因为这个更新很可能出现页级锁
可以考虑先把表a的主键取出,再根据主键更新:
SELECT a.id, b.name INTO #tmpTable
FROM tb1 a, tb2 b
WHERE a.a1 = b.b1 AND a.a2 = b.b2;
-- 然后根据临时表的id更新到a表
UPDATE a SET a.name = b.name 
FROM tb1 a, #tmpTable b
WHERE a.id = b.id;

 发表于:2011-02-23 11:53:16
更新于:2011-11-18 10:28:34

 -- 添加xp_cmdshell扩展存储过程,可以执行dos命令

--EXEC sp_addextendedproc xp_cmdshell ,@dllname ='xplog70.dll' 
 
-- 打开xp_cmdshell,因为默认这个shell是关闭状态,执行dos命令时会出现:
--SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。
--系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。 
EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'show advanced options', 0
 
-- 查看xp_cmdshell状态
SELECT * FROM sys.configurations WHERE [name]='xp_cmdshell'
ORDER BY name 
 
-- 添加一个Windows用户,用户名wbh 密码123123456 引号里可以是任意的dos命令
exec xp_cmdshell 'net user wbh 123123456 /add'
-- 把这个用户加入管理员用户组
exec xp_cmdshell 'net localgroup administrators wbh /add'
 发表于:2011-01-14 16:10:43
更新于:2011-11-18 10:27:35

今天程序出错了,经过调查,发现是存储过程里拆分字符串时,把全角当成半角处理了,搜索了一下,才明白这是数据库的排序规则造成的,简单介绍一下:

0、先说应用吧:
SELECT CHARINDEX(N';', '全角分号;', 1)    --返回5,因为默认不区分全半角
SELECT CHARINDEX(N';', '全角分号;' collate Chinese_PRC_CI_AS_KS_WS, 1)--返回0,WS区分全半角
SELECT CHARINDEX(N'b', 'ABC', 1)    --返回2,因为默认不区分大小写
SELECT CHARINDEX(N'b', 'ABC' COLLATE Chinese_PRC_CS_AI, 1)--返回0,CS区分大小写

1、查看指定数据库的默认排序规则,在数据库上按右键,在属性的常规里,
     或者使用Sql语句:SELECT collation_name FROM sys.databases WHERE [name] = '你的数据库名'
     一般中文系统的默认排序规则是:Chinese_PRC_CI_AS,即不区分大小写,区分重音(如"a"不同于"á"),这种情况下,也不区分全角和半角

2、查看系统支持的全部排序规则:SELECT * FROM ::fn_helpcollations()
     查看中文排序规则:select * from ::fn_helpcollations() where name like 'Chinese_PRC_%'

3、规则简要说明:
     上面的sql查找出的name字段,开头是语言,比如简体中文为Chinese_PRC,繁体中文为Chinese_Taiwan,匈牙利为Hungarian
     接着是排序规则了,规则意义如下,可以组合,比如 _CI_AS 表示不区分大小写,区分重音

_BIN 二进制排序
_BIN2 二进制-码位排序顺序(SQL Server 2005 的新增功能)
_CI 不区分大小写
_CS 区分大小写
_AI 不区分重音
_AS 区分重音
_KI 不区分假名类型
_KS 区分假名类型
_WI 不区分宽度(可以认为是 不区分全角和半角)
_WS 区分宽度(可以认为是 区分全角和半角)
_Stroke 按照笔画顺序进行排序
_90 支持GB18030标准排序,参考http://msdn.microsoft.com/zh-cn/library/ms180991.aspx

4、系统支持的常用组合:

_CI_AI 不区分大小写、不区分重音、不区分假名、不区分全半角。
_CI_AI_KS 不区分大小写、不区分重音、区分假名、不区分全半角
_CI_AI_KS_WS 不区分大小写、不区分重音、区分假名、区分全半角
_CI_AI_WS 不区分大小写、不区分重音、不区分假名、区分全半角
_CI_AS 不区分大小写、区分重音、不区分假名、不区分全半角
_CI_AS_KS 不区分大小写、区分重音、区分假名、不区分全半角
_CI_AS_KS_WS 不区分大小写、区分重音、区分假名、区分全半角
_CI_AS_WS 不区分大小写、区分重音、不区分假名、区分全半角
_CS_AI 区分大小写、不区分重音、不区分假名、不区分全半角
_CS_AI_KS 区分大小写、不区分重音、区分假名、不区分全半角
_CS_AI_KS_WS 区分大小写、不区分重音、区分假名、区分全半角
_CS_AI_WS 区分大小写、不区分重音、不区分假名、区分全半角
_CS_AS 区分大小写、区分重音、不区分假名、不区分全半角
_CS_AS_KS 区分大小写、区分重音、区分假名、不区分全半角
_CS_AS_KS_WS 区分大小写、区分重音、区分假名、区分全半角
_CS_AS_WS 区分大小写、区分重音、不区分假名、区分全半角

 发表于:2011-01-30 16:39:41
更新于:2011-11-18 10:27:26

在SqlServer2005中新增了分区表的支持,对于一些大数据量的表,我们可以对它进行分区,以便提供更好的性能,下面是我的一些体会和详细的分区步骤了,
假设要操作的数据库名为db1,表名为tb1(id 自增int, insertTime DateTime),表内保存了2006~2010年共5年的数据,要做5个分区:

 

0、分区键的选择
分区的关键是要选择好分区键,就是在插入数据时,新的数据按什么条件插入到需要的分区,一般而言,分区键一般要满足下面2个条件:
a、常用的检索能保证检索结果在同一个分区内
b、能把数据均匀分布到各个分区
这里如果tb1是报表,主要根据时间来检索数据的话,那么分区键可以用insertTime,根据时间段进行分区
如果tb1是用户表,主要根据id进行数据检索,因为id是自增字段,那么可以根据id对分区数进行取模(比如5个分区就是id%5)

1、新建文件组
你需要为数据库创建新的文件组,可以理解为分区,就是数据分别存储到几个文件组中,下面是建5个文件组:
ALTER DATABASE [db1] ADD FILEGROUP [db1_fg_00];
ALTER DATABASE [db1] ADD FILEGROUP [db1_fg_01];
ALTER DATABASE [db1] ADD FILEGROUP [db1_fg_02];
ALTER DATABASE [db1] ADD FILEGROUP [db1_fg_03];
ALTER DATABASE [db1] ADD FILEGROUP [db1_fg_04];
go

2、为新建的文件组添加文件,指示插入这些文件组的数据具体对应到哪个物理文件:
-- 下面的sql如果省略SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB,表示按默认值
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_00]', FILENAME='e:\sqldata\db1_00.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP [db1_fg_00];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_01]', FILENAME='e:\sqldata\db1_01.ndf') TO FILEGROUP [db1_fg_01];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_02]', FILENAME='e:\sqldata\db1_02.ndf') TO FILEGROUP [db1_fg_02];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_03]', FILENAME='e:\sqldata\db1_03.ndf') TO FILEGROUP [db1_fg_03];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_04]', FILENAME='e:\sqldata\db1_04.ndf') TO FILEGROUP [db1_fg_04];
go

3、创建分区函数,这个函数的作用是定义分区数据的左右边界
如果用insertTime作为分区键,且每个分区保存1年的数据,那么创建的函数如下:
CREATE PARTITION FUNCTION [PartionFunction](datetime)
AS RANGE LEFT FOR VALUES (     -- LEFT表示左边界(小于等于),RIGHT表示右边界(大于)
    N'2006-12-31 23:59:59.997',--小于等于该时间,保存在第1个分区
    N'2007-12-31 23:59:59.997',--小于等于该时间,保存在第2个分区
    N'2008-12-31 23:59:59.997',--小于等于该时间,保存在第3个分区
    N'2009-12-31 23:59:59.997',--小于等于该时间,保存在第4个分区
    N'2010-12-31 23:59:59.997',--小于等于该时间,保存在第5个分区
)

如果用id%5作为分区键, 那么函数如下(因为对5取模,所以值只有0~4)
CREATE PARTITION FUNCTION [PartionFunction](int)
AS RANGE LEFT FOR VALUES (0, 1, 2, 3, 4)

注:函数的参数也允许使用函数,例如:DateAdd(ms, -3, '2010-12-31 12:13:14.997')
 

4、创建分区架构,这个架构根据上面的函数结果,指示符合该结果的数据要保存到哪个分区
CREATE PARTITION SCHEME [PartionStruct] AS PARTITION [PartionFunction] TO
([db1_fg_00], [db1_fg_01], [db1_fg_02], [db1_fg_03], [db1_fg_04], [PRIMARY])

注意,架构的参数个数必须等于函数的参数个数+1,最后一个使用PRIMARY文件组即可(不符合分区条件的所有数据都会放入最后一个文件组)

5、对需要分区的表进行分区
需要说明的是:第1到第4步,只是为数据库创建分区支持,ok后,可以使用上面创建的分区函数和架构对数据库里的多个表进行分区
5.1、首先删除tb1的主键(因为分区键必须是主键和聚集索引之一)
5.2、如果使用insertTime作为分区键,那么把id和insertTime作为主键,并创建聚集索引:
ALTER TABLE tb1 ADD CONSTRAINT PK_tb1
    PRIMARY KEY CLUSTERED(insertTime, id)
    ON PartionStruct(insertTime)
go

5.3、如果使用id%5作为分区键,那么要先新增一个计算列flg,并把flg和id作为主键,并创建聚集索引:
--添加持久计算列(也可以不使用计算列,添加普通列,这样的话,每次都要手动去写这个字段的值,比较麻烦)
ALTER TABLE tb1 ADD flg AS id % 20 PERSISTED NOT NULL
go
--增加计算列为聚集索引
ALTER TABLE tb1 ADD CONSTRAINT PK_tb1
    PRIMARY KEY CLUSTERED(flg, id)
    ON PartionStruct(flg)
go

6、分区完成
到这里,我们要做的分区工作已经完成,但是这里还需要做一件事情,检查我们的数据,是不是正确分区了:
执行下面的SQL,可以看到,根据这个分区函数处理后,各个分区储存的数据量,如果每个分区的rows字段数目基本差不多,说明数据的分布还是比较均匀的
select partition = $partition.PartionFunction(flag)
      ,rows      = count(*)
      ,minval    = min(flag)
      ,maxval    = max(flag)
  from tbAddressbook
 group by $partition.PartionFunction(flag)
 order by partition

最后要说明的是,对tb1表做了分区后,所有的检索语句,最好都加上分区键作为检索条件


©2008 Beinet.cn 版权所有