关于表变量是什么(和表变量不是什么),以及和临时表的比较让很多人非常困惑。虽然网上已经有了很多关于它们的文章,但我并没有发现一篇比较全面的。在本篇文章中,我们将探索表变量和临时表是什么(以及不是什么),然后我们通过使用临时表和表变量对其解密。
表变量
表变量在SQL Server 2000中首次被引入,那么,什么是表变量呢?微软在BOL (Declare @local_variable)中定义其为一个类型为表的变量。它的具体定义包括列定义,列名,数据类型和约束。而在表变量中可以使用的约束包括主键约束,唯一约束,Null约束和Check约束(外键约束不能在表变量中使用).定义表变量的语句是和正常使用Create table定义表语句的子集。只是表变量通过DECLARE @local_variable 语句进行定义。
通过参考1可以知道:
1) 表变量拥有特定作用域(在当前批处理语句中,但不在任何当前批处理语句调用的存储过程和函数中),表变量在批处理结束后自动被清除。
2) 参考6中在"Recompilations Due to Certain Temporary Table Operations" 环节讨论了临时表在会导致存储过程强制被重复编译的各种原因,但这些原因并不适用于表变量。表变量和临时表比起来会产生更少的存储过程重编译。
3) 针对表变量的事务仅仅在更新数据时生效,所以锁和日志产生的数量会更少。
4) 由于表变量的作用域如此之小,而且不属于数据库的持久部分,所以事务回滚不会影响表变量。
表变量可以在其作用域内像正常的表一样使用。更确切的说,表变量可以被当成正常的表或者表表达式一样在select,delete,update,insert语句中使用。但是表变量不能在类似“SELECT select_list INTO table_variable” 这样的语句中使用。而在SQL Server 2000中,表变量也不能被用于“INSERT INTO table_variable EXEC stored_procedure”这样的语句中。
表变量不能做如下事情:
1.虽然表变量是一个变量,但是其不能赋值给另一个变量。
2.check约束,默认值,和计算列不能引用自定义函数。
3.不能为约束命名。
4.不能Truncate表变量
5.不能向标识列中插入显式值(也就是说表变量不支持SET IDENTITY_INSERT ON)
临时表
在深入临时表之前,我们首先需要讨论一下会话(Session),一个会话仅仅是一个客户端到数据引擎的连接。在SQL Server Management Studio(SSMS)中,每一个查询窗口都会和数据库引擎建立连接。一个应用程序可以和数据库建立一个或多个连接,除此之外,应用程序还可能建立连接后一直不释放直到应用程序结束,也可能使用完释放连接需要时建立连接。
那么,什么是临时表?在BOL (CREATE TABLE)中,我们可以知道临时表和以Create table语句创建的表有着相同的物理构成,但临时表与正常的表不同之处有:
1) 临时表的名字不能超过116个字符,这是由于数据库引擎为了辨别不同会话建立不同的临时表,所以会自动在临时表的名字后附加一串
2) 局部临时表(以“#”开头命名的)作用域仅仅在当前的连接内,从在存储过程中建立局部临时表的角度来看,局部临时表会在下列情况被Drop:
a.显式调用DROP Table语句
b.当局部临时表在存储过程内被创建时,存储过程结束也就意味着局部临时表被DROP
c.当前会话结束,在会话内创建的所有局部临时表都会被Drop
3) 全局临时表(以“##”开头命名的)在所有的会话内可见,所以在创建全局临时表之前首先检查其是否存在,否则如果已经存在,你将会得到重复创建对象的错误.
a.全局临时表会在创建其的会话结束后被DROP,其它会话将不能对全局临时表进行引用。
b.引用是在语句级别进行,比如说下面例子:
i.建立新的查询窗口,运行如下语句:
create table ##temp (RowID int)
ii.再次开启一个新的查询创建,使用如下语句每5秒中对全局临时表进行引用
while 1=1 begin
select * from ##temp
waitfor delay '00:00:05'
end
iii.回到第一个窗口,关闭窗口
iv.在下一个循环引用全局临时表时,将产生错误
4) 不能对临时表进行分区。
5) 不能对临时表加外键约束
6) 临时表内列的数据类型不能定义成没有在TempDb中没有定义自定义数据类型(自定义数据类型是数据库级别的对象,而临时表属于TempDb),由于TempDb在每次SQL Server重启后会被自动创建,所以你必须使用startup stored procedure来为TempDb创建自定义数据类型。你也可以通过修改Model数据库来达到这一目标。
7) XML列不能定义成XML集合的形式,除非这个集合已经在TempDb中定义
临时表既可以通过Create Table语句创建,也可以通过”SELECT <select_list> INTO #table”语句创建。你还可以针对临时表使用”INSERT INTO #table EXEC stored_procedure”这样的语句。
临时表可以拥有命名的约束和索引。但是,当两个用户在同一时间调用同一存储过程时,将会产生”There is already an object named ‘<objectname>’ in the database”这样的错误。所以最好的做法是不用为建立的对象进行命名,而使用系统分配的在TempDb中唯一的。6
参考6谈论了很多由于临时表而导致的存储过程重编译的原因以及避免的方法。
误区
误区1.表变量仅仅在内存中。
误区2.临时表仅仅存储在物理介质中
这两种观点都是明显的误区,在参考1的Q4节。表变量都是在TempDb数据库中创建,因为表变量存储的数据有可能超过物理内存。除此之外,我们发现只要内存足够,表变量和临时表都会在内存中创建和处理。它们也同样可以在任何时间被存入磁盘。
如何证明这点?请看下面代码(在SQL Server 2000到2008中都有效)
-- make a list of all of the user tables currently active in the
-- TempDB database
if object_id('tempdb..#tempTables') is not null drop table #tempTables
select name into #tempTables from tempdb..sysobjects where type ='U'
-- prove that even this new temporary table is in the list.
-- Note the suffix at the end of it to uniquely identify the table across sessions.
select * from #tempTables where name like '#tempTables%'
GO
-- create a table variable
declare @MyTableVariable table (RowID int)
-- show all of the new user tables in the TempDB database.
select name from tempdb..sysobjects
where type ='U' and name not in (select name from #tempTables)
还有一些“证明”临时表仅仅存在于内存中谬误,下面我来指出其中一个:
注意表变量的名字是系统分配的,表变量的第一个字符”@”并不是一个字母,所以它并不是一个有效的变量名。系统会在TempDb中为表变量创建一个系统分配的名称,所以任何在sysobjects或sys.tables查找表变量的方法都会失败。
正确的方法应该是我前面例子中的方法,我看到很多人使用如下查询查表变量:
select * from sysobjects where name like'#tempTables%'
上述代码看上去貌似很好用,但会产生多用户的问题。你建立两个连接,在第一个连接中创建临时表,在第二个窗口中运行上面的语句能看到第一个连接创建的临时表,如果你在第二个连接中尝试操作这个临时表,那么可能会产生错误,因为这个临时表不属于你的会话。
误区3.表变量不能拥有索引。
这个误区也同样错误。虽然一旦你创建一个表变量之后,就不能对其进行DDL语句了,这包括Create Index语句。然而你可以在表变量定义的时候为其创建索引)比如如下语句.
declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)
这个语句将会创建一个拥有聚集索引的表变量。由于主键有了对应的聚集索引,所以一个系统命名的索引将会被创建在RowID列上。
下面的例子演示你可以在一个表变量的列上创建唯一约束以及如何建立符合索引。
declare @temp TABLE (
RowID int NOT NULL,
ColA int NOT NULL,
ColB char(1)UNIQUE,
PRIMARY KEY CLUSTERED(RowID, ColA))
1) SQL 并不能为表变量建立统计信息,就像其能为临时表建立统计信息一样。这意味着对于表变量,执行引擎认为其只有1行,这也意味着针对表变量的执行计划并不是最优。虽然估计的执行计划对于表变量和临时表都为1,但是实际的执行计划对于临时表会根据每次存储过程的重编译而改变(看参考1,Q2部分).如果临时表不存在,在生成执行计划的时候会产生错误。
2) 前面提到,一定建立表变量后就无法对其进行DDL语句操作。因此如果需要为表建立索引或者加一列,你需要临时表。
3) 表变量不能使用select …into语句,而临时表可以
4) 在SQL Server 2008中,你可以将表变量作为参数传入存储过程。但是临时表不行。在SQL Server 2000和2005中表变量也不行。
5) 作用域:表变量仅仅在当前的批处理中有效,并且对任何在其中嵌套的存储过程等不可见。局部临时表只在当前会话中有效,这也包括嵌套的存储过程。但对父存储过程不可见。全局临时表可以在任何会话中可见,但是会随着创建其的会话终止而DROP,其它会话这时就不能再引用全局临时表。
6) 排序规则:表变量使用当前数据库的排序规则,临时表使用TempDb的排序规则。如果它们不兼容,你还需要在查询或者表定义中进行指定(参考7.Table Variables and Temporary Tables)
7) 你如果希望在动态SQL中使用表变量,你必须在动态SQL中定义表变量。而临时表可以提前定义,在动态SQL中进行引用。
说了这么多,那么,我该如何选择呢?
微软推荐使用表变量(看参考4),如果表中的行数非常小,则使用表变量。很多”网络专家”会告诉你100是一个分界线,因为这是统计信息创建查询计划效率高低的开始。但是我还是希望告诉你针对你的特定需求对临时表和表变量进行测试。很多人在自定义函数中使用表变量,如果你需要在表变量中使用主键和唯一索引,你会发现包含数千行的表变量也依然性能卓越。但如果你需要将表变量和其它表进行join,你会发现由于不精准的执行计划,性能往往会非常差。
为了证明这点,请看本文的附件。附件中代码创建了表变量和临时表.并装入了AdventureWorks数据库的Sales.SalesOrderDetail表。为了得到足够的测试数据,我将这个表中的数据插入了10遍。然后以ModifiedDate 列作为条件将临时表和表变量与原始的Sales.SalesOrderDetail表进行了Join操作,从统计信息来看IO差别显著。从时间来看表变量做join花了50多秒,而临时表仅仅花了8秒。
如果你需要在表建立后对表进行DLL操作,那么选择临时表吧。
临时表和表变量有很多类似的地方。所以有时候并没有具体的细则规定如何选择哪一个。对任何特定的情况,你都需要考虑其各自优缺点并做一些性能测试。下面的表格会让你比较其优略有了更详细的参考。
总结
特性 |
表变量 |
临时表 |
作用域 |
当前批处理 |
当前会话,嵌套存储过程,全局:所有会话 |
使用场景 |
自定义函数,存储过程,批处理 |
自定义函数,存储过程,批处理 |
创建方式 |
DECLARE statement only.只能通过DECLEARE语句创建 |
CREATE TABLE 语句
SELECT INTO 语句.
|
表名长度 |
最多128字节 |
最多116字节 |
列类型 |
可以使用自定义数据类型
可以使用XML集合
|
自定义数据类型和XML集合必须在TempDb内定义 |
Collation |
字符串排序规则继承自当前数据库 |
字符串排序规则继承自TempDb数据库 |
索引 |
索引必须在表定义时建立 |
索引可以在表创建后建立 |
约束 |
PRIMARY KEY, UNIQUE, NULL, CHECK约束可以使用,但必须在表建立时声明 |
PRIMARY KEY, UNIQUE, NULL, CHECK. 约束可以使用,可以在任何时后添加,但不能有外键约束 |
表建立后使用DDL (索引,列) |
不允许 |
允许. |
数据插入方式 |
INSERT 语句 (SQL 2000: 不能使用INSERT/EXEC). |
INSERT 语句, 包括 INSERT/EXEC.
SELECT INTO 语句.
|
Insert explicit values into identity columns (SET IDENTITY_INSERT). |
不支持SET IDENTITY_INSERT语句 |
支持SET IDENTITY_INSERT语句 |
Truncate table |
不允许 |
允许 |
析构方式 |
批处理结束后自动析构 |
显式调用 DROP TABLE 语句. 当前会话结束自动析构 (全局临时表: 还包括当其它会话语句不在引用表.) |
事务 |
只会在更新表的时候有事务,持续时间比临时表短 |
正常的事务长度,比表变量长 |
存储过程重编译 |
否 |
会导致重编译 |
回滚 |
不会被回滚影响 |
会被回滚影响 |
统计数据 |
不创建统计数据,所以所有的估计行数都为1,所以生成执行计划会不精准 |
创建统计数据,通过实际的行数生成执行计划。 |
作为参数传入存储过程 |
仅仅在SQL Server2008, 并且必须预定义 user-defined table type. |
不允许 |
显式命名对象 (索引, 约束). |
不允许 |
允许,但是要注意多用户的问题 |
动态SQL |
必须在动态SQL中定义表变量 |
可以在调用动态SQL之前定义临时表 |
参考:
1) INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
2) T-SQL BOL (SQL 2000), table data type
3) T-SQL BOL (SQL 2008), Declare @local_variable
4) T-SQL BOL (SQL 2008), CREATE TABLE
5) Table-Valued Parameters (Database Engine)
6) Troubleshooting stored procedure recompilation
7) Local Temporary Tables and Table Variables
8) Startup stored procedure
9) Data Definition Language (DDL)
其它值得阅读的文章:
1) Things You Didn’t Know About Temp Tables and Table Variables
-----------------------------------------------------------------------
原文链接:http://www.sqlservercentral.com/articles/Temporary+Tables/66720/
Translated by:CareySon
简介
SQL Server每个表中各列的数据类型的选择通常显得很简单,但是对于具体数据类型的选择的不同对性能的影响还是略有差别。本篇文章对SQL Server表列数据类型的选择进行一些探索。
一些数据存储的基础知识
在SQL Server中,数据的存储以页为单位。八个页为一个区。一页为8K,一个区为64K,这个意味着1M的空间可以容纳16个区。如图1所示:
![1 1](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206141654438932.png)
图1.SQL Server中的页和区
如图1(PS:发现用windows自带的画图程序画博客中的图片也不错
)可以看出,SQL Server中的分配单元分为三种,分别为存储行内数据的In_Row_Data,存储Lob对象的LOB_Data,存储溢出数据的Row_Overflow_data。下面我们通过一个更具体的例子来理解这三种分配单元。
我建立如图2所示的表。
![2 2](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206141654475458.png)
图2.测试表
图2的测试表不难看出,通过插入数据使得每一行的长度会超过每页所能容纳的最大长度8060字节。使得不仅产生了行溢出(Row_Overflow_Data),还需要存储LOB的页.测试的插入语句和通过DBCC IND看到的分配情况如图3所示。
![3 3](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206141655258374.png)
图3.超过8060字节的行所分配的页
除去IAM页,这1行数据所需要三个页来存储。首先是LOB页,这类是用于存储存在数据库的二进制文件所设计,当这个类型的列出现时,在原有的列会存储一个24字节的指针,而将具体的二进制数据存在LOB页中,除去Text之外,VarBinary(max)也是存在LOB页中的。然后是溢出行,在SQL Server 2000中,一行超过8060字节是不被允许的,在SQL Server 2005之后的版本对这个特性进行了改进,使用Varchar,nvarchar等数据类型时,当行的大小不超过8060字节时,全部存在行内In-row data,当varchar中存储的数据过多使得整行超过8060字节时,会将额外的部分存于Row-overflow data页中,如果update这列使得行大小减少到小于8060字节,则这行又会全部回到in-row data页。
数据类型的选择
在了解了一些基础知识之后。我们知道SQL Server读取数据是以页为单位,更少的页不仅仅意味着更少的IO,还有更少的内存和CPU资源消耗。所以对于数据选择的主旨是:
尽量使得每行的大小更小
这个听起来非常简单,但实际上还需要对SQL Server的数据类型有更多的了解。
比如存储INT类型的数据,按照业务规则,能用INT就不用BIGINT,能用SMALLINT就不用INT,能用TINYINT就不用SMALLINT。
所以为了使每行的数据更小,则使用占字节最小的数据类型。
1.比如不要使用DateTime类型,而根据业务使用更精确的类型,如下表:
类型 |
所占字节 |
Date(仅日期) |
3 |
Time(仅时间) |
5 |
DateTime2(时间和日期) |
8 |
DateTimeOffSet(外加时区) |
10 |
2.使用VarChar(Max),Nvarchar(Max),varbinary(Max)来代替text,ntext和image类型
根据前面的基础知识可以知道,对于text,ntext和image类型来说,每一列只要不为null,即使占用很小的数据,也需要额外分配一个LOB页,这无疑占用了更多的页。而对于Varchar(Max)等数据类型来说,当数据量很小的时候,存在In-row-data中就能满足要求,而不用额外的LOB页,只有当数据溢出时,才会额外分配LOB页,除此之外,Varchar(Max)等类型支持字符串操作函数比如:
- COL_LENGTH
- CHARINDEX
- PATINDEX
- LEN
- DATALENGTH
- SUBSTRING
3.对于仅仅存储数字的列,使用数字类型而不是Varchar等。
因为数字类型占用更小的存储空间。比如存储123456789使用INT类型只需要4个字节,而使用Varchar就需要9个字节(这还不包括Varchar还需要占用4个字节记录长度)。
4.如果没有必要,不要使用Nvarchar,Nchar等以“字”为单位存储的数据类型。这类数据类型相比varchar或是char需要更多的存储空间。
5.关于Char和VarChar的选择
这类比较其实有一些了。如果懒得记忆,大多数情况下使用Varchar都是正确的选择。我们知道Varchar所占用的存储空间由其存储的内容决定,而Char所占用的存储空间由定义其的长度决定。因此Char的长度无论存储多少数据,都会占用其定义的空间。所以如果列存储着像邮政编码这样的固定长度的数据,选择Char吧,否则选择Varchar会比较好。除此之外,Varchar相比Char要多占用几个字节存储其长度,下面我们来做个简单的实验。
首先我们建立表,这个表中只有两个列,一个INT类型的列,另一个类型定义为Char(5),向其中插入两条测试数据,然后通过DBCC PAGE来查看其页内结构,如图4所示。
图4.使用char(5)类型,每行所占的空间为16字节
下面我们再来看改为Varchar(5),此时的页信息,如图5所示。
![5 5](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206141655447290.png)
图5.Varchar(5),每行所占用的空间为20字节
因此可以看出,Varchar需要额外4个字节来记录其内容长度。因此,当实际列存储的内容长度小于5字节时,使用char而不是varchar会更节省空间。
关于Null的使用
关于Null的使用也是略有争议。有些人建议不要允许Null,全部设置成Not Null+Default。这样做是由于SQL Server比较时就不会使用三值逻辑(TRUE,FALSE,UNKNOWN),而使用二值逻辑(True,False),并且查询的时候也不再需要IsNull函数来替换Null值。
但这也引出了一些问题,比如聚合函数的时候,Null值是不参与运算的,而使用Not Null+Default这个值就需要做排除处理。
因此Null的使用还需要按照具体的业务来看。
考虑使用稀疏列(Sparse)
稀疏列是对 Null 值采用优化的存储方式的普通列。 稀疏列减少了 Null 值的空间需求,但代价是检索非 Null 值的开销增加。 当至少能够节省 20% 到 40% 的空间时,才应考虑使用稀疏列。
稀疏列在SSMS中的设置如图6所示。
![6 6](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206141655485244.png)
图6.稀疏列
更具体的稀疏列如何能节省空间,请参看MSDN。
对于主键的选择
对于主键的选择是表设计的重中之重,因为主键不仅关系到业务模型,更关系到对表数据操作的的效率(因为主键会处于B树的非叶子节点中,对树的高度的影响最多)。关于主键的选择,我之前已经有一篇文章关于这点:从性能的角度谈SQL Server聚集索引键的选择,这里就不再细说了。
总结
本篇文章对于设计表时,数据列的选择进行了一些探寻。好的表设计不仅仅是能满足业务需求,还能够满足对性能的优化。
简介
SQL Server中的复制(Replication)是SQL Server高可用性的核心功能之一,在我看来,复制指的并不仅仅是一项技术,而是一些列技术的集合,包括从存储转发数据到同步数据到维护数据一致性。使用复制功能不仅仅需要你对业务的熟悉,还需要对复制功能的整体有一个全面的了解,本系列文章旨在对SQL Server中的复制进行一个简单全面的探讨。(PS:在我的上篇文章中我发现某些文章的图片使用mspaint手绘更有感觉,但被很多人吐槽,因此在不考虑个人羞耻感的前提下,本系列文章中的一些图片继续使用mspaint
)。
复制是什么
复制,英文是Replication,这个词源自于拉丁文replicare,原意是重复。SQL Server中的复制也是这个意思,复制的核心功能是存储转发,意味着在一个在一个位置增删改了数据以后,重复这个动作到其他的数据源,概念如图1所示。
![1 1](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321386779.png)
图1.复制的基本概念
当然,上面的这个模型是复制最简单的模型,实际中的模型可能会复杂很多,但是大多数使用复制的原因可以分为如下几类:
1.负载均衡----通过将数据复制到其它数据库服务器来减少当前服务器的负载,比如说最典型的应用就是分发数据来分离OLTP和OLAP环境。
2.分区----将经常使用的数据和历史数据隔离,将历史数据复制到其它数据库中
3.授权----将一部分数据提供给需要使用数据的人,以供其使用
4.数据合并-每个区域都有其各自的数据,将其数据进行合并。比如一个大公司,每个地区都有其各自的销售数据,总部需要汇总这些数据。
5.故障转移----复制所有数据,以便故障时进行转移。
虽然需要使用复制的原因多种多样,但是在使用之前你首先要了解复制技术所需的组成元素。
复制的组成部分
复制的概念很像发行杂志的模型,从发行商那里出版后,需要通过报刊亭等地方分发到订阅杂志的人手里。对于SQL Server复制来说,这个概念也是如此。对于SQL Server复制来说,发行商,报刊亭,订阅者分别对应的是发布服务器,分发服务器,订阅服务器。概念如图2所示。
![2 2](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321395516.png)
图2.发布分发订阅的基本概念
发布服务器
图2中的发布服务器包含了需要被发布的数据库。也就是需要向其它数据源分发内容的源数据库。当然,被发布的数据首先需要被允许发布。关于这里的详细设置会在文章后面提到。
分发服务器
图2中的分发服务器包含了分发数据库,分发数据库的作用是存储转发发布服务器发过来的数据。一个分发服务器支持多个发布服务器,就像一个报刊亭可以出售多个出版社所出的杂志一样。同理,分发服务器也可以和发布服务器是同一个实例,这就像出版商不通过报刊亭,自己直接贩卖杂志一样。
订阅服务器
图2中的订阅服务器包含了发布服务器所发布的数据的副本。这个副本可以是一个数据库,或者一个表,甚至是一个表的子集。根据不同的设置,有些发布服务器发布的更新到订阅服务器就是只读的(比如说用于出报表的OLAP环境),或者是订阅服务器也可以进行更新来将这些改变提交到发布服务器。
发布和文章
发布指的是可以发布的文章的集合,这些文章包括表,存储过程,视图和用户自定义函数,如图3所示。
![3 3](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321409519.png)
图3.可以发布的内容
当我们发布表时,还可以根据限定条件只发布表的子集。
订阅
订阅是相对发布的一个概念,订阅定义了订阅服务器从哪个分发服务器接收发布。有两类订阅方式,推送订阅(Push)和请求订阅(Pull),根据名字就可以望文生义的知道,在推送订阅的情况下,当发布服务器产生更新时,分发服务器直接更新订阅的内容,而请求订阅需要订阅服务器定期查看分发服务器是否有可用更新,如果存在可用更新,则订阅服务器更新数据。
复制类型
SQL Server将复制方式分为三大类,每一个发布只能有一种复制类型,分别为:快照复制,事务复制和合并复制。
快照复制
快照复制将发布的所有表做成一个镜像,然后一次性复制到订阅服务器。中间的更新不会像其它复制类型那样自动传送到订阅服务器。由这个概念不难看出,快照复制的特点会是:
1.占用网络宽带,因为一次性传输整个镜像,所以快照复制的内容不应该太大。
2.适合那些更新不频繁,但每次更新都比较大的数据。比如企业员工信息表,每半年更新一次这类的业务场景。
3.适合订阅服务器是OLAP只读的环境。
来自MSDN的配图能很好的阐述快照复制,如图4所示。
![snap snap](https://images.cnblogs.com/cnblogs_com/CareySon/201206/20120620112602317.gif)
图4.快照复制
事务复制
事务复制就像其名字一样,复制事务。在第一次设置好事务复制后,发布的表、存储过程等将会被镜像,之后每次对于发布服务器所做的改动都会以日志的方式传送到订阅服务器。使得发布服务器和订阅服务器几乎可以保持同步。因此,可以看出事务复制的特点是:
1.发布服务器和订阅服务器内容基本可以同步
2.发布服务器,分发服务器,订阅服务器之间的网络连接要保持畅通。
3.订阅服务器也可以设置成请求订阅,使得订阅服务器也可以不用一直和分发服务器保持连接。
4.适用于要求实时性的环境。
来自MSDN的配图能很好的阐述事务复制,如图5所示
![grid.ai grid.ai](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201126068828.gif)
图5.事务复制
合并复制
合并复制即允许发布服务器更新数据库,也允许订阅服务器更新数据。定期将这些更新进行合并,使得发布的数据在所有的节点上保持一致。因此,有可能发布服务器和订阅服务器更新了同样的数据,当冲突产生时,并不是完全按照发布服务器优先来处理冲突,而是根据设置进行处理,这些会在后续文章中讲到。
来自MSDN的配图能很好的阐述合并复制,如图6所示。
![merge merge](https://images.cnblogs.com/cnblogs_com/CareySon/201206/20120620112613426.gif)
图6.合并复制
建立一个简单的事务复制
下面我进行一个简单的事务复制。首先,在本地安装两个SQL Server实例,我本机安装的两个实例分别为SQL Server 2008R2和SQL Server 2012,其中,SQL Server 2008R2作为发布和分发服务器,SQL Server 2012作为订阅服务器,如图7所示。
![7 7](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321416237.png)
图7.复制的两个实例
首先在SQL Server 2008R2上配置发布服务器和分发服务器,选择配置分发,如图8所示。
![8 8](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321426369.png)
图8.配置分发
将发布服务器和分发服务器选择为同1台,如图9所示。
![9 9](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321441584.png)
图9.设置发布服务器和分发服务器为同一台服务器
设置快照文件夹,由上面MSDN的图可知,快照代理是需要在分发服务器上暂存快照的,设置这个目录,如图10所示。
![10 10](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321468717.png)
图10.设置快照文件夹
这里值得注意的是,需要给这个目录对于Everyone设置读取权限,如图11所示。
![11 11](https://images.cnblogs.com/cnblogs_com/CareySon/201206/20120620132147801.png)
图11.设置读取权限
下一步配置分发向导就按照默认值来,如图12所示。
![12 12](https://images.cnblogs.com/cnblogs_com/CareySon/201206/20120620132149443.png)
图12.配置分发向导
剩下的步骤都保持默认值,最后成功在SQL Server 2008R2实例上配置发布服务器和分发服务器,如图13所示。
![13 13](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321501721.png)
图13.成功配置发布和分发服务器
下面就要建立一个发布了,选择新建发布,如图14所示。
![14 14](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321529901.png)
图14.新建发布
一路next,在选择发布类型时选择事务发布,如图15所示。
![15 15](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321544036.png)
图15.选择事务发布
发布用于测试的一个表,只有两个列,一个为自增的int型主键id,另一个为随便设置的列,如图16所示。
![16 16](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321565663.png)
图16.设置发布的表(文章)
下一个页面不过滤文章,直接保持默认值下一步。在下一个窗口中选择立即创建快照并初始化..如图17所示。
![17 17](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321583909.png)
图17.立即创造快照并初始化
安全设置保持和SQL Server Agent一样的账户,如图18所示。
![18 18](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201321598534.png)
图18.快照代理和日志读取代理设置和SQL Server Agent同一个账户
剩下的步骤一路下一步,设置好发布名称后,成功创建发布,如图19所示。
![19 19](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201322019845.png)
图19.成功创建发布
下面我们来在SQL Server 2012的实例上创建订阅,选择新建订阅,如图20所示。
![20 20](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201322035517.png)
图20.新建订阅
在欢迎界面选择下一步后,选择刚刚创建的发布,如图21所示。
![21 21](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201322045682.png)
图21.选择发布服务器
下一步选择推送订阅,以便发布服务器所做的改动能自动更改到订阅服务器,如图22所示。
![23 23](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201322066437.png)
图23.选择推送订阅
选择保持连接,下一步保持默认值,然后在分发代理安全性下选择模拟进程账户。如图24所示。
![24 24](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201322084442.png)
图24.选择模拟进程账户
保持默认值,一路下一步直到订阅创建完成,如图25所示。
![25 25](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201322101816.png)
图25.创建订阅成功
现在我们进行测试,向表中插入100条数据,监视状态,发现100个事务已经成功传到了订阅服务器,如图26所示。
![26 26](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201322116442.png)
图26.插入的100条数据已经成功传送到订阅服务器
现在我们再来看订阅服务器(SQL Server 2012),在发布服务器插入的100条数据已经成功存在于订阅服务器,如图27所示。
![27 27](https://images.cnblogs.com/cnblogs_com/CareySon/201206/201206201322122081.png)
图27.100条数据已经成功发布到了订阅服务器
总结
本文对SQL Server的复制进行了大致的讲解,并实现了一个简单的复制。复制的概念需要对SQL Server的各个方面都要有所涉猎,本系列文章的下一篇将会将复制应用的一些模式。
简介
在传统的操作系统中,进程拥有独立的内存地址空间和一个用于控制的线程。但是,现在的情况更多的情况下要求在同一地址空间下拥有多个线程并发执行。因此线程被引入操作系统。
为什么需要线程?
如果非要说是为什么需要线程,还不如说为什么需要进程中还有其它进程。这些进程中包含的其它迷你进程就是线程。
线程之所以说是迷你进程,是因为线程和进程有很多相似之处,比如线程和进程的状态都有运行,就绪,阻塞状态。这几种状态理解起来非常简单,当进程所需的资源没有到位时会是阻塞状态,当进程所需的资源到位时但CPU没有到位时是就绪状态,当进程既有所需的资源,又有CPU时,就为运行状态。
下面我们来看一个具体的例子:
就拿我写博客的LiveWriter来说,LiveWriter需要监听我打字输入的状态,还需要每隔5分钟对草稿进行自动保存。假设如果这个进程只有一个线程的话,那么当对草稿进行保存时,因为此时需要访问硬盘,而访问硬盘的时间线程是阻塞状态的,这时我的任何输入都会没有响应,这种用户体验是无法接受的,或许我们可以通过键盘或者鼠标的输入去中断保存草稿的过程,但这种方案也并不讨好。而使用多线程,每个线程仅仅需要处理自己那一部分应该完成的任务,而不用去关心和其它线程的冲突。因此简化了编程模型。如图1所示。
![1 1](https://images.cnblogs.com/cnblogs_com/CareySon/201205/201205040911583490.png)
图1.两条线程满足各自的功能
更具体的说,线程的好处如下:
1.在很多程序中,需要多个线程互相同步或互斥的并行完成工作,而将这些工作分解到不同的线程中去无疑简化了编程模型。
2.因为线程相比进程来说,更加的轻量,所以线程的创建和销毁的代价变得更小。
3.线程提高了性能,虽然线程宏观上是并行的,但微观上却是串行。从CPU角度线程并无法提升性能,但如果某些线程涉及到等待资源(比如IO,等待输入)时,多线程允许进程中的其它线程继续执行而不是整个进程被阻塞,因此提高了CPU的利用率,从这个角度会提升性能。
4.在多CPU或多核的情况下,使用线程不仅仅在宏观上并行,在微观上也是并行的。
这里值得注意的是,上面的两个线程如果改成两个进程,那么达不到所要的效果,因为进程有自己独立的内存地址空间,而线程共享进程的内存地址空间。
经典线程模型
另一个看进程和线程的角度是进程模型基于两类不同的概念:资源的组织和执行。在过去没有线程的操作系统中,资源的组织和执行都是由进程完成的。但区分这两者很多时候需要加以区分,这也是为什么需要引入线程。
进程是用于组织资源的单位,进程将相关的资源组织在一起,这些资源包括:内存地址空间,程序,数据等,将这些以进程的形式组织起来可以使得操作系统管理这些资源更为容易。
而线程,是每一个进程中执行的一个条线。线程虽然共享进程中的大多数资源,但线程也需要自己的一些资源,比如:用于标识下一条执行指令的程序计数器,一些容纳局部变量的寄存器,以及用于表示执行的历史的栈。
总而言之:进程是组织资源的最小单位,而线程是安排CPU执行的最小单位。
其实在一个进程中多个线程并行和在操作系统中多个进程并行非常类似,只是线程共享的是地址空间,而进程共享的是物理内存,打印机,键盘等资源……
每一个进程和线程所独自占有的资源如表1所示。
进程占有的资源 |
线程占有的资源 |
地址空间 全局变量 打开的文件 子进程 信号量 账户信息 |
栈 寄存器 状态 程序计数器 |
表1.进程和线程所独占的资源
其中,线程可以共享进程独占的资源。
我们常用的术语“多线程”一般指的是在同一个进程中多个线程的并发执行。如图2所示。
![2 2](https://images.cnblogs.com/cnblogs_com/CareySon/201205/201205040912008422.png)
图2.没有多线程的系统一个进程只能由一个线程
在多线程的进程中,每个线程轮流使用CPU,因此实际上线程并不是并行的,但从宏观上看,是并行的。
在多线程模型中,每一个进程初始创建时只有一个线程。这个线程可以通过调用系统的库函数去创建其它线程。线程创建的线程并必须要为其指定地址,因为新的线程自动在创建它的地址空间内工作。虽然一个线程可以创建另一个线程,但通常来讲,线程之间是并列的,并不存在层级关系。
当一个进程完成其工作后,可以通过调用系统库函数进行销毁。
操作系统实现线程的几种模式
在操作系统中,线程可以实现在用户模式下,也可以实现在内核模式下,也可以两者结合实现。
线程实现在用户空间下
当线程在用户空间下实现时,操作系统对线程的存在一无所知,操作系统只能看到进程,而不能看到线程。所有的线程都是在用户空间实现。在操作系统看来,每一个进程只有一个线程。过去的操作系统大部分是这种实现方式,这种方式的好处之一就是即使操作系统不支持线程,也可以通过库函数来支持线程。
在这种模式下,每一个进程中都维护着一个线程表来追踪本进程中的线程,这个表中包含表1中每个线程独占的资源,比如栈,寄存器,状态等,如图3所示。
![3 3](https://images.cnblogs.com/cnblogs_com/CareySon/201205/201205040912048229.png)
图3.在用户空间中实现线程
这种模式当一个线程完成了其工作或等待需要被阻塞时,其调用系统过程阻塞自身,然后将CPU交由其它线程。
这种的模式的好处,首先,是在用户空间下进行进程切换的速度要远快于在操作系统内核中实现。其次,在用户空间下实现线程使得程序员可以实现自己的线程调度算法。比如进程可以实现垃圾回收器来回收线程。还有,当线程数量过多时,由于在用户空间维护线程表,不会占用大量的操作系统空间。
有好处就有坏处,这种模式最致命的缺点也是由于操作系统不知道线程的存在,因此当一个进程中的某一个线程进行系统调用时,比如缺页中断而导致线程阻塞,此时操作系统会阻塞整个进程,即使这个进程中其它线程还在工作。还有一个问题是假如进程中一个线程长时间不释放CPU,因为用户空间并没有时钟中断机制,会导致此进程中的其它线程得不到CPU而持续等待。
线程实现在操作系统内核中
在这种模式下,操作系统知道线程的存在。此时线程表存在操作系统内核中,如图4所示。
![4 4](https://images.cnblogs.com/cnblogs_com/CareySon/201205/201205040912102760.png)
图4.线程在操作系统内核中实现
在这种模式下,所有可能阻塞线程的调用都以系统调用(System Call)的方式实现,相比在用户空间下实现线程造成阻塞的运行时调用(System runtime call)成本会高出很多。当一个线程阻塞时,操作系统可以选择将CPU交给同一进程中的其它线程,或是其它进程中的线程,而在用户空间下实现线程时,调度只能在本进程中执行,直到操作系统剥夺了当前进程的CPU。
因为在内核模式下实现进程的成本更高,一个比较好的做法是另线程回收利用,当一个线程需要被销毁时,仅仅是修改标记位,而不是直接销毁其内容,当一个新的线程需要被创建时,也同样修改被“销毁”的线程其标记位即可。
这种模式下同样还是有一些弊端,比如接收系统信号的单位是进程,而不是线程,那么由进程中的哪一个线程接收系统信号呢?如果使用了表来记录,那么多个线程注册则通过哪一个线程处理系统信号?
混合模式
还有一种实现方式是将上面两种模式进行混合,用户空间中进程管理自己的线程,操作系统内核中有一部分内核级别的线程,如图5所示。
![5 5](https://images.cnblogs.com/cnblogs_com/CareySon/201205/201205040912183652.png)
图5.混合模式
在这种模式下,操作系统只能看到内核线程。用户空间线程基于操作系统线程运行。因此,程序员可以决定使用多少用户空间线程以及操作系统线程,这无疑具有更大的灵活性。而用户空间线程的调度和前面所说的在用户空间下执行实现线程是一样的,同样可以自定义实现。