Access与SqlServer数据类型比较,timestamp时间戳详解

一、Access与SqlServer数据类型比较

Microsoft Access 数据类型	SQL Server 数据类型
是/否，（0和-1） 1 位，不允许有 Null 值。	bit(1、0 或 NULL)
数字（字节）（0~255） 1个字节，无小数位	tinyint（0~255） 大小为 1 字节
数字（整型）（-32768~32767） 2 个字节, 无小数位	smallint（-32768~32767） 大小为 2 个字节
数字（长整型）（-2,147,483,648~2,147,483,647） 默认值，4个字节，无小数位	int （-2^31 (-2,147,483,648) ~ 2^31 - 1 (2,147,483,647)） 存储大小为 4 个字节。int 的 SQL-92 同义字为 integer
数字（单精度浮点型） 负值:3.402823E38 ~ -1.401298E-45 正值:1.401298E-45 到 3.402823E38 4 个字节,7位小数	real （3.40E + 38 ~ 3.40E + 38，浮点数字数据） 存储大小为 4 字节，同义词为 float(24)
（无等价的数据类型）	bigint（-2^63 (-9,223,372,036,854,775,808) ~ 2^63-1 (9,223,372,036,854,775,807)） 存储大小为 8 个字节
数字（双精度浮点型） 负值: –1.79769313486231E308 ~–4.94065645841247E–324  正值: 4.94065645841247E–324~1.79769313486231E308 8 个字节, 15位小数位	float (n)（1.79E + 308 ~ 1.79E + 308）， n 必须为从 1 到 53 之间的值
货币 8 个字节 货币值或用于数学计算的数值数据，这里的数学计算的对象是带有 1 到 4 位小数的数据。精确到小数点左边 15 位和小数点右边 4 位。	(1)、money（-2^63 (-922,337,203,685,477.5808) ~ 2^63 - 1 (+922,337,203,685,477.5807)），8 个字节； (2)、smallmoney （-214,748.3648 ~+214.748,3647），4 个字节。 精确到货币单位的千分之十
小数/数值（-10^28 -1 ~ 10^28 -1） 28位小数位，12 个字节 可以指定数值范围（最大总位数）和精度	decimal / numeric（-10^38 -1 ~ 10^38 -1，可指定数值范围(最大总位数)和精度(小数点右边的最大位数)）。 decimal[(p[, s])]： p（精度）小数点左边和右边可以存储的十进制数字的最大个数，精度必须是从 1 到最大精度之间的值。最大精度为 38。 s（小数位数）指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 p 之间的值。默认小数位数是 0，因而 0 <= s <= p。最大存储大小基于精度而变化。 精度 / 存储字节数 1-9 / 5 10-19 / 9 20-28 / 13 29-38 / 17 eg：decimal(18,4)，可存99万亿保留4位小数的数字
同步复制ID（GUID） 16 个字节	uniqueidentifier  16个字节
日期/时间 从 100 到 9999 年的日期与时间值。 8 个字节	datetime （1753-1-1~9999-12-31，精确度为三百分之一秒即3.33毫秒） smalldatetime （1900-1-1~2079-6-6，精确度为1分钟）
自动编号 可以产生三种编号：顺序号、随机号和GUID。 AutoNumber（增量）	int（定义了 Identity 标识列属性，设置标识种子和标识增量）
文本 （默认值）文本或文本和数字的组合，以及不需要计算的数字，例如电话号码。 最多为 255 个字符或长度小于 FieldSize 属性的设置值。Microsoft Access 不会为文本字段中未使用的部分保留空间。	varchar(n) （最大长度为8000个ANSI字符） nvarchar(n) （最多可含4000个Unicode字符）
备注(Memo) 长文本或文本和数字的组合。 最多为 65,535 个字符	text （最多可存储2^31 -1 (2,147,483,647) 个字符，默认长度为 16） （ntext、text 和 image，外部存储）
OLE 对象 最多为1G字节，受可用磁盘空间限制	image （2^31 -1 (2,147,483,647) 字节的二进制数据，用来存储 BLOB（二进制大对象），如图片、文档、声音和已编译代码。） （ntext、text 和 image，外部存储）
超链接 文本或文本和以文本形式存储的数字的组合，显示的文本，地址，子地址，屏幕提示： displaytext#address#subaddress#	char （固定长度，最多可含8000个ANSI 字符） nchar （固定长度，最多可含4000个Unicode字符） varchar，nvarchar
（无等价的数据类型）	varbinary （可变长最多可存储8000字节的二进制数据）
（无等价的数据类型）	smallint （-2^15 (-32,768) ~ 2^15 - 1 (32,767)），2个字节。
（无等价的数据类型）	timestamp （自动生成的二进制数，确保这些数在数据库中是唯一的，一般用作给表行加版本戳的机制，8 字节。timestamp 列中的值不是 datetime 数据，而是 binary(8) 或 varbinary(8)，标明了数据修改的顺序）
（无等价的数据类型）	char nchar
（无等价的数据类型）	sql_variant （一种存储 SQL Server 支持的各种数据类型（text、ntext、image、timestamp 和 sql_variant 除外）值的数据类型）
（无等价的数据类型）	用户定义

 

二、timestamp（时间戳）详解

timestamp 详解

每一个数据库都有一个计数器，这个计数器记录着数据行的插入、更新行为。如果我们为一个表中增加 timestamp 列，那么，该列将记录每一个数据行的计数器值。假如数据库中当前的计数器是 20（实际中不是数字类型，为了方便解释，这里用数字类型）：那么在表 A 中插入一条记录后，插入行的 timestamp 为 21；然后更新表 B 的一条记录后，更新行的 timestamp 为 22；现在再更新刚才插入的行，此时其 timestamp 为 23。可以看出，每个行都有一个 timestamp，并且是在数据库的所有表中唯一的，当更新行时，被更新的行的 timestamp 会加 1。注意删除不影响计数器。

timestamp 作用：timestamp 就是设计来避免更新冲突的，当然还有其它一些作用，比如 SQL Server 全文索引时用以增量填充。

timestamp 应用：我们只需要添加一个为 timestamp 类型的列就可以了，它会自动记下当前计数器的值。一个表只能有一个 timestamp 列，并且不能将该列作为主键。不可为空的 timestamp 列在语义上等价于 binary(8) 列；可为空的 timestamp 列在语义上等价于 varbinary(8) 列。

timestamp 会重复吗？可能会，在使用 select into 时。

如何查看数据库中当前的 timestamp？请使用 @@DBTS，比如在查询分析器中用：print @@DBTS。

增、改没有 timestamp 列的表数据时，@@DBTS 会变吗？不会。

利用 timestamp 避免更新冲突

SQL Server 里有数据类型 timestamp，这里我们将利用 timestamp 来避免更新冲突。

比如一个内容管理系统，A 正在编辑某一篇文章，B 也正在编辑该文章，A 和 B 都不知道对方在编辑这篇文章，双方保存后，都以为已经成功保存，实际上，数据库中只保存了最后提交的记录（先提交的记录被覆盖），这就造成了数据库中的数据与操作者所认为的不一样。

要避免这种更新冲突，一种方法可以采用模拟锁的形式，一个人在编辑另一个人就不允许编辑，但比较麻烦，由于这种更新冲突并非常见，投入较大精力在这上面，不值。这里使用 timestamp 轻松实现避免更新冲突。

1、表中增加一个字段 tmstmp，类型为 timestamp，参见SQL Server中timestamp（时间戳）。

2、编辑文章时。增加一个隐藏域 <input type="hidden" name="timestamp" value="<%=rs("tmstmp")%>">，参见如何显示timestamp的值。

3、更新文章时。
sql = "update ... where id=" & id & " and tmstmp=" & tmstmp
conn.Execute sql, effectLines
if effectLines <= 0 then
    '更新失败，可能是原记录不存在，或者更新冲突
end if

SQL Server中timestamp（时间戳）

比如，字段的标识列属性（数据类型为数值型）：在插入新行时自动添加；

而，字段的数据类型为时间戳类型：每行更新时数据库自动更新此行的此字段的值；

我们知道数字类型有个“标识”属性，设置好“标识种子”、“标识递增量”，每增加一条记录时，这个字段的值就会在最近的一个标识值基础上自动增加，这样我们可以知道哪些记录是先添加的，哪些记录是后添加的，但是我们无法知道哪些记录修改过。timestamp这个类型的字段呢，每增加一条记录时，它会在最近的一个时间戳基础上自动增加，当修改某条记录时，它也会在最近的一个时间戳基础上自动增加，所以我们就知道哪些记录修改过了。

如何显示timestamp的值

有朋友反应response.Write(rs("[timestamp]"))，是一片空白，可是在查询分析器中看到的是一串数字。这种情况下，在写SQL语句时这样写，select (字段名+0) as 别名，输出时就会有显示了。(0x000000000000FACC)