数据表定义

数据表(或称表)是数据库最重要的组成部分之一,数据库中以表为组织单位存储数据,数据库只是一个框架,数据表才是其实质内容。数据库管理工具中可以显示数据库中的所有数据表,数据表是数据库中一个非常重要的对象,是其他对象的基础。

你是不是也在苦苦寻求优化自己的SQL
Server数据库的方法?如果你的数据库里有不少非常大的表格,分区功能能够帮到你很大的忙,因为它可以把这些大表格分割成独立文件组。这个技术可以让你把数据分布在不同的物理磁盘中,并通过调节它们的并行性能来最优化你的查询性能。

关于Ansi_Padding的用法

MERGE 目标表
USING 源表
ON 匹配条件
WHEN MATCHED THEN
语句
WHEN NOT MATCHED THEN
语句;

Merge关键字是一个神奇的DML关键字。它在SQL Server
2008被引入,它能将Insert,Update,Delete简单的并为一句。MSDN对于Merge的解释非常的短小精悍:”根据与源表联接的结果,对目标表执行插入、更新或删除操作。

创建数据表

方式一:打开数据库管理工具-》登录-》选择数据库-》选择表-》右键点击,选择新建表-》在右边视图中输入列名、数据类型、是否可空、在列属性中设置需要的属性-》点击保存(或ctrl+s)-》保存成功,刷新即可显示

图片 1

图片 2

方式二:使用T-SQL脚本新建表

 1 --数据库声明
 2 use testss
 3 --建表语法声明
 4 create table test1
 5 (
 6 --字段声明
 7 id int identity(1,1) not null,
 8 name nvarchar(50) null,
 9 sex nvarchar(50) null,
10 age nvarchar(50) null,
11 classid int,
12 primary key clustered(id asc) with(ignore_dup_key=off) on [primary]    --主键索引声明
13 )on [primary]
14 
15 --字段注释声明
16 exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'id主键' , @level0type=N'SCHEMA',
17 @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'id';
18 
19 exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'姓名' , @level0type=N'SCHEMA',
20 @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'name';
21 
22 exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'性别' , @level0type=N'SCHEMA',
23 @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'sex';
24 
25 exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年龄' , @level0type=N'SCHEMA',
26 @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'age';
27 
28 exec sys.sp_addextendedproperty @name=N'MS_Description', @value=N'班级id' , @level0type=N'SCHEMA',
29 @level0name=N'dbo', @level1type=N'TABLE',@level1name=N'test1', @level2type=N'COLUMN',@level2name=N'classid';
30 
31 go

 示例结果如下:图片 3

对SQL Server数据表进行分区的过程分为三个步骤:

  1.当设置为ON时,不剪裁字符值中插入到varchar列的尾随空格和二进制值中插入到varbinary列的尾随零。不将值按列的长度进行填充。

其中最后语句分号不可以省略,且源表既可以是一个表也可以是一个子查询语句

WHEN MATCHED THEN

删除数据表

方式一:打开数据库管理工具-》登录-》选择数据库-》选择表-》右键点击,选择删除-》点击弹出框中的确定删除-》刷新数据库

图片 4图片 5

方式二:使用T-SQL脚本删除:drop table
test2;

图片 6

1)建立分区函数

     
2.当设置为OFF时,剪裁varchar列的尾随空格和varbinary列的尾随零。该设置只影响新列的定义。

WHEN NOT MATCHED BY TARGET
表示目标表不匹配,BY TARGET是默认的,所以上面我们直接使用WHEN NOT
MATCHED THEN
WHEN NOT MATCHED BY SOURCE
表示源表不匹配,即目标表中存在,源表中不存在的情况。

WHEN NOT MATCHED THEN

修改数据表

方式一:打开数据库管理工具-》登录-》选择数据库-》选择表-》右键点击,选择重命名-》重新输入表名-》按enter键确定-》刷新可查看新表名

 图片 7

方式二:使用T-SQL脚本修改表名:exec
sp_rename ‘test1′,’test2’;

图片 8

2)建立分区方案

 

主要用法:
merge无法多次更新同一行,也无法更新和删除同一行
当源表和目标表不匹配时:
若数据是源表有目标表没有,则进行插入操作
若数据是源表没有而目标表有,则进行更新或者删除数据操作
当源表和目标表匹配时:
进行更新操作或者删除操作

其中最后语句分号不可以省略,且源表既可以是一个表也可以是一个子查询语句

查看数据表

–查询数据库下所有的表
–xtype=’U’查询用户表
select name from sysobjects where
xtype=’U’
— xtype=’S’查询系统表
select name from sysobjects where
xtype=’S’

–查看test1表的所有信息
exec sp_help test1;

–查询test1表中所有的列
select * from syscolumns where
id=Object_Id(‘test1’)

–查看test1表中所有列数据
select * from test1;

3)对表格进行分区

  1.SETANSI_PADDING为ON时,将允许空值的Char(n)和binary(n)列填充到列长;

when matched 这个子句可以有两个,当有两个时,第一个子句必须是when
matched and condition且两个matched子句只会执行一个,且两个子句必须
是一个update和一个delete操作
when not matched by source和上面类似

WHEN NOT MATCHED BY TARGET

分区函数定义how,即你想要SQL
Server如何对数据进行分区。这里就不以某一个表格作为例子,而是总体概括分割数据的技术。

     
2.而当SETANSI_PADDING为OFF时,将剪裁尾随空格和零,始终将不允许空值的Char(n)和binary(n)列填充到列长。

merge icr_codemap_bak as a
using icr_codemap as b
on a.COLNAME = b.COLNAME and a.ctcode = b.ctcode
when matched and b.pbcode <> a.pbcode
then update set a.pbcode = b.pbcode
when not matched
then insert values(b.colname,b.ctcode,b.pbcode,b.note)
;
可以比对字段不一致进行更新

这个是MSDN的网址

表示目标表不匹配,BY TARGET是默认的,所以上面我们直接使用WHEN NOT
MATCHED THEN

分区是通过指定每个分区的分割界线实现的。例如,假定我们有一个Customers表格,里面包含了企业所有的客户的信息,客户信息以唯一的客户号进行辨识,客户号从1到1000000。我们可以运用以下的分区函数(这里称之为customer_Partfunc)把这个表格平均分为四个分区:

 默认为 ON

在 Merge Matched 操作中,只能允许执行 UPDATE 或者 DELETE 语句。
在 Merge Not Matched 操作中,只允许执行 INSERT 语句。
一个 Merge 语句中出现的 Matched 操作,只能出现一次 UPDATE 或者 DELETE
语句,否则就会出现下面的错误 – An action of type ‘WHEN MATCHED’ cannot
appear more than once in a ‘UPDATE’ clause of a MERGE statement.
Merge 语句最后必须包含分号,以 ; 结束。

WHEN NOT MATCHED BY SOURCE

CREATE  PARTITION  FUNCTION  customer_partfunc(int)

图片 9

 

表示源表不匹配,即目标表中存在,源表中不存在的情况。

AS  RANGE  RIGHT

主要用法:

FOR   VALUES(250000,500000,750000)

merge无法多次更新同一行,也无法更新和删除同一行

这些分割界线指定了四个分区。第一个分区包含所有值小于250000的记录。第二个分区包含所有值在250000和499999之间的记录。而第三个分区包含所有值在500000和749999之间的记录。其他所有大于或等于750000的记录都包含在第四个分区里。

当源表和目标表不匹配时:若数据是源表有目标表没有,则进行插入操作若数据是源表没有而目标表有,则进行更新或者删除数据操作当源表和目标表匹配时:进行更新操作或者删除操作when
matched 这个子句可以有两个,当有两个时,第一个子句必须是when matched and
condition且两个matched子句只会执行一个,且两个子句必须是一个update和一个delete操作when
not matched by source和上面类似

注意这个例子中使用了“RANGE
RIGHT”从句。这说明分界值是在分区的右边。同样,如果使用的是“RANGE
LEFT”从句,那么第一个分区就会包含所有值小于或等于250000的记录;第二个分区就会包含所有值在250001和500000之间的记录,如此类推。

merge icr_codemap_bak as ausing icr_codemap as bon a.COLNAME =
b.COLNAME and a.ctcode = b.ctcodewhen matched and b.pbcode <>
a.pbcodethen update set a.pbcode = b.pbcodewhen not matchedthen insert
values(b.colname,b.ctcode,b.pbcode,b.note);

一旦建立完定义如何对数据进行分区的分区函数之后,下一步就是建立一个分区方案,定义where,即你想在哪里对数据进行分区。这是一个很直接明了的过程,例如,如果我有四个文件组,名称分别从“fg1”到“fg4”,那么就可以使用以下分区方案:

可以比对字段不一致进行更新

CREATE PARTITION   SCHEME  customer_partscheme

这个是MSDN的网址

AS  PARTITION  customer_partfunc

在 Merge Matched 操作中,只能允许执行 UPDATE 或者 DELETE 语句。在 Merge
Not Matched 操作中,只允许执行 INSERT 语句。一个 Merge 语句中出现的
Matched 操作,只能出现一次 UPDATE 或者 DELETE
语句,否则就会出现下面的错误 – An action of type ‘WHEN MATCHED’ cannot
appear more than once in a ‘UPDATE’ clause of a MERGE statement.Merge
语句最后必须包含分号,以 ; 结束。

TO(fg1,fg2,fg3,fg4)

ps:SQL Server中Merge-using的用法

注意我们现在把一个分区函数连接到了分区方案,但是我们还没有把分区方案连接到任何具体的数据库表格。这就是重复使用功能发挥功能的时候。我们可以通过这个功能把分区方案(或者只是分区函数)用于数据库表格的任何数据上。

merge into UserInfo uusing chartinfo c on u.UserId=c.UserIdwhen matched and u.UserName=c.UserName then update set u.lastUpdate=c.LastUpdatewhen not matched --为not matched时 不能为update then insert values;

建立好分区方案之后,就可以开始对表格进行分区了。这是最简单的一个步骤,只需要在表格创建语句中添加“ON”从句,指定表格分区方案和要应用该分区方案的表列。你不需要指定分区函数,因为分区方案已经定义了分区函数。

Merge和using搭配用于特别是BI上数据统计和分析上 比如
要求子表中没有的数据那么父表中就要删除对应的数据
保证子表和父表的数据对应 如果按照常规的做法是 跑个作业
然后通过游标/表值函数/临时表等等循环的获取数据然后更新父表
这样是很浪费效率的 这时Merge派上用场了。

举个例子,假设你想要用上述的分区方案来创建一个客户表格,你需要使用以下Transact-SQL语句:

CREATE  TABLE 
customers(FirstNamenvarchar(40),LastNamenvarchar(40),CustomerNumberint)

ON  customer_partscheme(CustomerNumber)

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图