深入解析SQL Server 2008 笔记 </h1><p class="clear"></p><p class="postBody">

深入解析SQL Server 2008 笔记

目录

F

F

F

F

F

F

F

第一章 sqlserver2008架构和配置

版本

SELECT SERVERPROPERTY('Edition')--Developer Edition (64-bit)

SELECT SERVERPROPERTY(‘EngineEdition’)
–3

 

EngineEdition返回：2、3、4
可能是企业版，评估版，开发版
2 标准版 工作组版
3 开发版
4 express版 Windows embedded SQL

F

sql元数据

SELECT [name]FROM sys.[objects]WHERE [type_desc]='system_table'

 

兼容性视图，兼容sql2000

sql2005和sql2008的新功能不包含在兼容性视图里，例如表分区和资源调控器
兼容性视图只是为了向前兼容，在将来版本sqlserver将会删除所有兼容性视图

F

使用sql2008查询sysprocess中所有可用信息时，你必须访问3个DMV:
SYS.[dm_exec_connections]
SYS.[dm_exec_requests]
SYS.[dm_exec_sessions]
因为增加了MARS，所以sql2000的sysprocess不能标识出单个连接了

 

目录视图显示的列完全不同于兼容视图中的列

--目录视图SELECT * FROM sys.[databases]--兼容视图SELECT * FROM sys.[sysdatabases]

 

F

 

查看视图定义

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.[tables]'))SELECT * FROM sys.[system_sql_modules] WHERE [object_id]=OBJECT_ID('sys.[tables]')

 

sys.objects包含各种对象类型通用的所有属性列，sys.tables和sys.views包含有与sys.objects完全相同的列

F

信息架构视图

系统函数

sql7.0引入了系统函数，在sql2000中增强了这些函数

属性函数

 

F

如果只查看sue是否拥有某一个数据库访问权限，则无法使用系统提供的存储过程，使用目录视图这些查询将非常简单

SELECT [name]FROM sys.[databases]WHERE SUSER_NAME([owner_sid])='sue'

 

 

sql2008元数据层跟sql2005几乎一样

F

sqlserver数据库引擎主要组件

关系引擎（也称为查询处理器），存储引擎，SQLOS

DMV也可以管理数据仓库（sql2008的新功能）的性能报告提供更多有效数据

F

dm_exec_:执行有关的

规范化树

 

F

查询优化器使用修剪启发式算法

对于after触发器：触发器计划（生成触发器执行计划）在修改语句激活触发器之后和提交修改之前执行

 

触发器的特定步骤没有编译到执行计划中

查询执行器的详细介绍：http://sqlserverinternals.com/companion/

存储引擎主要包括3部分：访问方法，锁，事务服务，实用工具命令

 

访问方法实际不检索页面，他向缓存管理器发送请求，最后缓存管理器在其缓存中提供页面，

或者从磁盘读取页面到缓存，look-ahead预测行机制对页面上的行货索引条目进行鉴定

F

 

所有用户数据都存储在数据页或LOB页上

事务4个属性：原子性，一致性，隔离性，持久性

F

事务服务
事务管理组件

F

其他操作：存储引擎还包含用于控制实用工具的组件：例如大容量加载，dbcc命令，全文索引填充和管理，备份和还原

 

SQLOS

SQLOS的两个主要函数是计划和内存管理，同步化，内存broker，sqlserver异常处理，死锁检测，扩展事件，异步I/O

扩展事件：sql2008的资源调控器使用扩展事件管理资源使用率

numa架构

F

numa节点中的CPU数目取决于硬件生产商

内存节点是基于硬件numa而创建的，因此不受软件numa的影响

将scheduler翻译成计划程序，一头雾水

SQL2005之前的scheduler称为用户架构scheduler（UMS） user  Management scheduler
SQL2005和2008称scheduler为SOSscheduler，并进一步改进了UMS  system optimization  scheduler

多任务等待 等待类型
SOS_SCHEDU-LER_YIELD 223

http://www.golden-book.com/booksinfo/17/1754478.html

F

当更改关联掩码时，导致scheduler从online向offline切换时，已经分配给该scheduler的任何工作必须先完成，并且系统不再向该scheduler分配新工作

sqlserver工作线程 workers

SQLSERVER使用自己的计划程序sql agent而放弃Windows的计划程序scheduler，这样做可以获取更高的控制权和可伸缩性

F

spid优先分配给负载最低的scheduler

SELECT [load_factor] FROM sys.[dm_os_schedulers]

线程和纤程

每个numa节点都有自己的惰性写入器和IOCP io完成端口 iocp是一种网络侦听器

 

sql mail和sql xml需要线程模型

http://support2.microsoft.com/ph/2855

numa和scheduler

单个处理或SMP机器被看成是单个numa
scheduler和含有numa的cpu之间不存在硬映射

F

当非闲置线程没有让出scheduler，scheduler监视器将产生错误（17883），当应用程序（不是sqlserver）独占CPU时，将出现17883错误

scheduler监视器还负责向scheduler发送消息，帮助他们平衡工作负荷

使用trace flag 跟踪标志8002启动sqlserver，通过使用跟踪标志，将cpu映射到某个实例上

将scheduler绑定到cpu

scheduler和CPU不是一对一的关系，负载不大时，Windows可以在一个cpu上运行两个scheduler

F

parent_node_id:scheduler所属节点id，也称为父节点，代表numa节点

sys.[dm_os_schedulers]视图解释

F

F

DAC

SELECT *FROM SYS.[tcp_endpoints] AS E JOIN SYS.[dm_exec_sessions] AS SON E.[endpoint_id]=S.[endpoint_id]WHERE E.[name]='DEDICATED ADMIN CONNECTION'

name endpoint_id principal_id protocol protocol_desc type type_desc state state_desc is_admin_endpoint port is_dynamic_port ip_address
Dedicated Admin Connection 1 1 2 TCP 2 TSQL 0 STARTED 1 0 1 NULL
TSQL Default TCP 4 1 2 TCP 2 TSQL 0 STARTED 0 0 1 NULL
inven 65536 261 2 TCP 3 SERVICE_BROKER 0 STARTED 0 4030 0 NULL

 

默认情况下，DAC只能本地使用，但DBA也可以配置DAC允许远程连接

sp_configure 'remote admin connections', 1;GORECONFIGURE;GO

 

只能用sysadmin服务器角色成员连接

 

 

F

ALTER DATABASE [sss] SET CHANGE_TRACKING =ON

SELECT *
FROM sys.[all_columns]
WHERE object_id=OBJECT_ID(‘sys.syscommittab’)

ALTER TABLE [dbo].[nums]
ENABLE CHANGE_TRACKING

SELECT *
FROM sys.[all_objects]
WHERE [name] LIKE ‘%change_tracking%’
–change_tracking_1954106002

–使用DAC也没有办法查询
SELECT * FROM sys.change_tracking_1954106002

消息 208，级别 16，状态 1，第 1 行对象名 'sys.change_tracking_1954106002' 无效。

固定开销包括18字节的事务id，CSN和操作类型，以及来自目标表的主键大小

F

--使用DAC也没有办法查询SELECT * FROM sys.change_tracking_1954106002

INSERT INTO [dbo].[nums]
SELECT 1

从执行计划看到更改跟踪还会插入到内部更改表

 

删除的时候也会修改更改跟踪表

DELETE FROM [nums]

更改跟踪表更新 会插入旧值和新值到内部更改表

UPDATE [nums] SET [n]=2 DECLARE @context VARBINARY(128)=CONVERT(VARBINARY(128) ,SUSER_NAME());

WITH change_tracking_context(@context)
UPDATE

不仅内部更改表的插入操作在事务结束时同步发生，提交表的插入操作也在提交时发生，插入的行包含在内部表和目标表的隐藏列中使用的相同事务id，此时，还将为该事务分配一个CSN，因此可以将CSN看成是版本号，该版本号应用于事务修改的所有行

F

列跟踪

--打开TRACK_COLUMNS_UPDATED启用列跟踪ALTER TABLE [dbo].[nums]ENABLE CHANGE_TRACKINGWITH (TRACK_COLUMNS_UPDATED =ON)

 

F

changetable函数是系统表值函数

SELECT E.* ,[C].SYS_CHANGE_VERSION FROM [dbo].[nums] ECROSS APPLY CHANGETABLE(VERSION [sss].[dbo].[nums],( n),( [n])) C

 

F

事件会话

会话用于说明用户需要收集的事件，谓词用于解决应该筛选哪些事件，需要激活的操作和事件一起协同工作，目标在循环的结尾用于数据收集

 

和sql跟踪一样，大多数会话互相独立，连接多个会话的主要线程是中心位图，他用于指示是否启用或禁用指定事件，在许多会话中可以同步启用事件，但全局位图用于避免在运行时必须检测所有会话

 

USE [master]SELECT *FROM sys.[server_event_sessions] --XE会话信息中心元数据

SELECT *
FROM sys.[server_event_sessions_events] –每个XE会话绑定的事件

SELECT *
FROM sys.[server_event_sessions_actions] –每个XE会话绑定的操作

SELECT *
FROM sys.[server_event_sessions_targets] –每个XE会话绑定的目标

SELECT *
FROM sys.[server_event_sessions_fields] –每个XE会话自定义设置信息 例如用户可以将环形缓存区的内存使用量设置为指定数量，如果使用了目标，该视图将显示内存设置

 配置异步目标缓存区，这些设置将影响一个名为dispatcher的进程，该进程负责定期从缓冲区收集数据，并将收集的数据发送到绑定会话的每个异步目标中

 

F

数据库收缩会被快照隔离级别的事务阻挡，如果发生这种情况，DBCC SHRINKFILE和DBCC SHRINKDATABASE就会在

第一个小时内的每5分钟和之后的每个小时向错误日志写入具有信息性的消息，

sqlserver还通过SELECT * FROM sys.[dm_exec_requests]视图为shrink命令提供进程报告

 

F

 

F

 

文件流文件组 filestream文件组

F

 

CREATE DATABASE MyMovieReviews ON PRIMARY(NAME=Reviews_data,FILENAME='C:\DATA\Reviews_data.mdf'), FILEGROUPMovieReviewsFSGroup1 CONTAINS FILESTREAM(NAME=Reviews_FS,FILENAME='C:\DATA\Review_FS') LOG ON (NAME=Reviews_LOG,FILENAME='C:\DATA\Reviews_log.ldf')GO

在文件夹C:\DATA\Review_FS里会看到filestream.hdr文件和$fslog文件夹。filestream.hdr文件是filestream容器头文件，不应该修改或删除这个文件，对于现有数据库，可以用alter database添加文件流文件组

 

F

注意：不能吧文件从一个文件组移动到另一个文件组

 

F

F

F

IAM页面跟踪in-row data，row overflow data，lob data，每种页面都会有相应的IAM进行跟踪

SELECT * FROM sys.[system_internals_allocation_units]

 

F

数据库选项

F

F

ALTER DATABASE [sss] SET RESTRICTED_USER

–可以有多个连接，但是只能来自认为是合格的用户dbcreator或sysadmin服务器角色或数据库的db_owner角色的连接

 

F

从recovery_pending切换成emergency时，sqlserver会关闭数据库，然后用特殊标志重新启动他，这样会跳过恢复过程。

跳过恢复意味着可能会有逻辑上或物理上不一致的数据，丢失索引行，断开页面链接或错误的元数据指针，

将数据库设置为emergency模式就是承认数据可能不一致但是不管怎样还是想访问他

F

ALTER DATABASE [sss] SET RESTRICTED_USER WITH ROLLBACK AFTER ALTER DATABASE [sss] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATEALTER DATABASE [sss] SET RESTRICTED_USER WITH no_wait

 

游标选项

F

自动选项

SQL选项

F

数据库恢复选项

F

虽然sqlserver数据库页面是8KB，但是磁盘用512byte的扇区进行I/O操作，因此，每个页面上都有16个扇区

对sql2000更改了页面校验和之后，当修改页面的时候才会写入校验和，没有修改的页面不会无端端去计算校验和

F

数据库快照

不要将快照的稀疏文件和sql2008中的稀疏列相混淆

稀疏文件总是以64KB为增量增长的，所以磁盘上的稀疏文件大小总是以64KB的倍数

F

建立快照时会记录源数据库的事务日志中同步日志序列号LSN,LSN是确定数据库中特定点的方法

使用sys.[dm_io_virtual_file_stats]() DMV对快照文件使用的空间进行统计

F

如果源数据库上有任何快照，就不能还原源数据库

F

tempdb数据库

F

用户对象：用户创建的表，临时表，表变量
内部对象：工作表，工作文件，排序单元

hash union，hash aggression，hash join
联接，聚合，联合

当sqlserver处理使用哈希运算符的查询时，使用工作文件合并或聚合数据

F

tempdb的延迟删除

F

--使用sys.[dm_db_file_space_usage]视图来监视tempdb的空间 dbid=2--用户对象，内部对象，版本存储USE [tempdb]SELECT *FROM sys.[dm_db_file_space_usage]

SELECT *
FROM sys.[dm_db_task_space_usage]

–通常分配的空间值应该和释放的空间值相同，但是如果存在延迟删除，分配值比释放值大
SELECT *
FROM sys.[dm_db_session_space_usage]

 

F

F

数据库安全性

--所有的登录名，不管是Windows还是sqlserver身份验证，都可以在sys.[server_principals]目录视图中看到SELECT *FROM sys.[server_principals]

 

F

--下面查询显示数据库中用户映射到登录名，还显示了每个数据库用户的默认架构SELECT s.[name] AS loginname ,d.[name] AS username ,[default_schema_name] AS defaultschemaFROM sys.[server_principals] sJOIN sys.[database_principals] d ON d.[sid] = s.[sid]

数据和架构

主体和架构

CREATE USERCREATE SCHEMA --取代了之前的EXEC sys.[sp_adduser] @loginame = NULL, -- sysname@name_in_db = NULL, -- sysname@grpname = NULL -- sysnameEXEC sys.[sp_grantdbaccess] @loginame = NULL, -- sysname@name_in_db = NULL -- sysname

 

F

默认架构

在sql2008中，新建数据库时，其中包括了几个架构，他们包括[INFORMATION_SCHEMA]，dbo，guest

移动或复制数据库

F

drop database命令还会从实例中删除数据库的所有跟踪记录

F

sqlserver会编译一个新的日志文件，因为sp_detach_db创建的新日志文件会很小--小于1MB,所以这是收缩已经远远大于想要的日志文件的快速方法

备份，还原数据库

F

兼容性级别

F

除非将model数据库改变兼容性级别，否则sql2008中所有新建数据库的兼容级别都是100

 

--设置兼容级别ALTER DATABASE [sss] SET COMPATIBILITY_LEVEL 100

 

F

第四章 日志记录和恢复

缓冲区管理器保证在将更改写入数据库之前，先写入事务日志

 

F

崩溃恢复，针对sqlserver重启
还原恢复，针对用户发出的restore database语句

 

recovery会在建立数据库快照，数据库镜像运行recovery

 

F

构造脏页表DPT

F

F

sys.[fn_dblog]信息更详细

--读取事务日志DBCC LOG([sss])

USE [sss]
SELECT * FROM sys.[fn_dblog](NULL,NULL)

DBCC LOGINFO

无论有多少个物理日志文件，在收缩日志的时候，sqlserver不是单独考虑每个日志文件大小，而是根据整个日志确定可压缩的大小

vlf大小（31KB*8KB）

虚拟日志文件，一个0.5mb的日志文件含有两个vlf（一个vlf的最小大小是31*8kb =0.2mb）

一个vlf的最小大小是0.2MB

8KB怀疑就是一个数据页

namespace InternalsViewer.Internals.TransactionLog

Log Data containing fragment of a page at a particular offset

《sql2005 技术内幕 存储引擎》

宋沄剑的图片应该有错

F

行以物理顺序列出，FSeqNo才是真正的逻辑顺序

 

F

4400000085601161有4个VLF都是相同的证明是在同一次日志增长里的，并且loginfo里没有其他的createLSN说明

事务日志只增长过一次

F

F

自动截断日志
将恢复模式设置为simple
从未进行过完整备份

F

sqlserver根据每分钟内可恢复10MB事务日志的估算来确定恢复间隔

用户线程调用日志管理器，日志管理器都会检查日志大小

如果数据库在自动截断模式时日志已达到70%，那么日志管理器会唤醒checkpoint线程

F

--查看 sys.[database_recovery_status] 视图的[last_log_backup_lsn]列，如果该列为空，数据库就是处于自动截断模式

SELECT *
FROM sys.[database_recovery_status]
WHERE [database_id]=DB_ID(‘sss’)

进行数据库完整备份前

进行数据库完整备份之后

设置数据库为简单恢复模式之后，[last_log_backup_lsn]值变成null

ALTER DATABASE [sss] SET RECOVERY SIMPLE

F

压缩日志

auto_shrink选项每30分钟运行自动压缩进程，日志管理器累计自动压缩进程之间的30分钟间隔内所使用的最大日志空间量的统计信息，自动压缩进程将日志的压缩点标记为实际使用的最大日志空间的125%和日志的最小大小这两个数中较大的那个（最小大小是创建日志时的大小或已经被手动增大或压缩过的大小），如果有机会（备份并截断）的话，日志就会被压缩到这个大小

F

--统计各个数据库的事务日志文件已使用的百分比SELECT [instance_name] AS 'DATABASE' ,[cntr_value] AS 'LogFullPct'FROM sys.[dm_os_performance_counters]WHERE [counter_name] LIKE 'percent log used%'AND [instance_name] NOT IN ( '_total', 'mssqlsystemresource', 'MASTER','MODEL', 'TEMPDB', 'MSDB', 'ReportServer','ReportServerTempDB' )AND [cntr_value] > 0-- Author: <桦仔>-- Blog: <http://www.cnblogs.com/lyhabc/>-- Create date: <2014/4/18>-- Description: <统计各个数据库的总大小V2 不包含数据文件>-- =============================================SET NOCOUNT ON USE masterGO

DECLARE @DBNAME NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

–临时表保存数据
CREATE TABLE #DataBaseServerData
(
ID INT IDENTITY(1, 1) ,
DBNAME NVARCHAR(MAX) ,
Log_Total_MB DECIMAL(18, 1) NOT NULL ,
Log_FREE_SPACE_MB DECIMAL(18, 1) NOT NULL
)

–游标
DECLARE @itemCur CURSOR
SET
@itemCur = CURSOR FOR
SELECT name from SYS.[sysdatabases] WHERE [name] NOT IN (‘MASTER’,‘MODEL’,‘TEMPDB’,‘MSDB’,‘ReportServer’,‘ReportServerTempDB’)
AND [status]=65544

OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL=N’USE [’+@DBNAME+’];’+CHAR(10)
+
‘INSERT [#DataBaseServerData]
( [DBNAME] ,
[Log_Total_MB] ,
[Log_FREE_SPACE_MB ]
)
SELECT ‘’’+@DBNAME+’’’, str(sum(convert(dec(17,2),sysfiles.size)) / 128,10,2) AS Total_MB,
SUM(( database_files.size - FILEPROPERTY(database_files.name, ‘‘SpaceUsed’’) )) / 128.0 AS free_space_mb
FROM dbo.sysfiles as sysfiles INNER JOIN sys.database_files as database_files ON sysfiles.[fileid]=database_files.[file_id] WHERE sysfiles.[groupid] =0
AND database_files.[type] = 1;’
EXEC (@SQL)
FETCH NEXT FROM @itemCur INTO @DBNAME
END

CLOSE @itemCur
DEALLOCATE @itemCur

SELECT * FROM [#DataBaseServerData]
DROP TABLE [#DataBaseServerData]

备份和还原数据库

 

sql2008主要有4种备份类型：
完整备份
差异备份
日志备份
文件和文件组备份

F

备份线程只复制区

sql2008还支持一个日志标记的功能，他能让你在事务日志中放置参考点，如果数据库处于full模式，可以选择恢复到这些日志的标记之一

http://technet.microsoft.com/zh-cn/library/ms186865(SQL.105).aspx

http://msdn.microsoft.com/en-us/library/ms186858.aspx

F

最小日志记录只记录那些需要回滚事务的信息，但不支持时间点恢复
批量操作如下
select into
bulk insert
openrowset（bulk..）
insert into select
部分更新具有lob数据类型的列
update xx write
writetext,updatetext
create index
alter index rebuild 或 dbcc reindex
drop index

BCM BULK CHANGED MAP也称为最小记录映射ML MAP

最小化记录的缺点：sqlserver会强制在事务提交之前将数据页刷到磁盘，尤其这些页面的IO为随机IO，强制刷数据页面可能会非常昂贵，可以将他们与完整记录相比较，完整记录总是顺序IO，如果没有快速IO子系统的话，可能会很明显感觉最小记录比完整记录慢

由于在事务提交之前就要将数据刷到磁盘，那么就需要调用eager writer勤奋写入器不停刷盘

以insert select 为例，首先插入 insert select  top（10000）  是一个大事务

新页面以最小日志记录，数据不会不停刷盘，如果这时候宕机，我还没有提交事务，那么sqlserver只需要在下次启动的时候

将已经刷盘的页面释放掉就可以了，如果不是宕机是rollback tran也是同样的道理

纵观下面的大容量操作命令，你会发现都是插入操作和新建索引操作，这些操作都是同样的道理，预先刷盘，回滚的时候就释放页面

select into
bulk insert
openrowset（bulk..）
insert into select
部分更新具有lob数据类型的列
update xx write
writetext,updatetext
create index
alter index rebuild 或 dbcc reindex
drop index

 

truncate命令除外

 

 

大容量日志模式下的大批量操作，会把数据放进去日志备份文件bak里面，因为不像完整模式，每条记录都有前影像和后影像

所有只能把大批量操作期间的数据放进去日志备份文件，从而导致

大容量日志模式下的大批量操作备份出来的日志文件比在full模式下的大批量操作备份出来的日志文件大

F

F

F

F

sqlserver可能需要在恢复完成后对元数据做些调整，所以直到完成恢复的所有阶段，才允许访问数据库，换言之，restore没有“快速”恢复这个选项

F

页面还原 ，suspect page msdb

使用页面还原之后必须进行日志还原，不是所有的已损坏页都可以用数据库联机还原

企业版才支持联机页面还原

F

在单个文件中可以还原的最多页数是1000

SELECT [redo_target_lsn]

FROM sys.[master_files]
WHERE [database_id]=5

 

with standby还原

必须用 with recovery还原最后一个日志（不会保存备用文件standby文件），才能让数据库完全可用

F

F

第五章 表

在一个真实的关系数据库中，表中的每一行都是唯一的并具有一个被称为主键的唯一标识符（与ANSI SQL标准相一致，sqlserver不需要你是一行唯一或声明一个主键，但是由于这两个概念对关系模型非常关键，因此建议你总是这样做）

F

兼容性视图也可以通过dbo架构访问，因此sys.sysobjects和dbo.sysobjects是同一个对象，对于分类视图和动态关联对象来说，必须指定sys架构来访问对象

F

--name在任何情况下都合法CREATE TABLE [dbo].customer([name] CHAR(30))

 

F

--可以看一下会话是否有开启 [quoted_identifier]SELECT [quoted_identifier]FROM sys.[dm_exec_sessions]WHERE [session_id] = @@SPID

 

F

F

sql2005 sp2允许小数点之后的数据存放在可变空间中
sql2008默认为表除了master，model，tempdb，msdb之外的所有数据库启用了vardecimal storage format属性
虽然vardecimal存储格式可以降低数据的存储容量，但是却增加了额外的CPU开销为代价，一旦启用某个表的vardecimal属性，则表中所有decimal数据都存储为可变长度

vardecimal storage format属性是一种脱机操作，sqlserver独占锁定正在修改的表，直到所有decimal数据转换成新的格式。vardecimal存储格式已经不推荐使用了，因此我们不会介绍vardecimal数据的内部存储，对于新开发，建议使用sqlserver的压缩功能使可变字节的存储需求降到最低

F

日期和时间数据类型

F

date为3字节正数
所有包含时间信息的数据类型（time，datetime2，datetimeoffset）都允许在数据类型后添加一个1~7之间的数字来表示所保留字的方式来指定时间部分的精度，如果不指定精度，默认为7

SELECT CAST(CURRENT_TIMESTAMP AS TIME)SELECT CAST(CURRENT_TIMESTAMP AS TIME(2))SELECT CAST(CURRENT_TIMESTAMP AS TIME(7))--08:46:30.7200000--08:46:30.73--08:46:30.7300000

 

F

字符数据类型

SELECT [name],[max_length] FROM sys.[types]name max_lengthimage 16text 16uniqueidentifier 16date 3time 5datetime2 8datetimeoffset 10tinyint 1smallint 2int 4smalldatetime 4real 4money 8datetime 8float 8sql_variant 8016ntext 16bit 1decimal 17numeric 17smallmoney 4bigint 8hierarchyid 892geometry -1geography -1varbinary 8000varchar 8000binary 8000char 8000timestamp 8nvarchar 8000nchar 8000xml -1sysname 256

 

F

F

字符数据排序规则

sqlserver的系统表也遵守排序规则

resource数据库使用Latin1_General_CI_AI排序规则

F

SELECT * FROM sys.[fn_helpcollations]()--3885种排序规则，112种排序规则被废弃 SQL2012

由于排序规则决定数据在索引中出现的顺序，因此sqlserver不会接受由于你将数据库移动到不同的Windows版本上而造成的对排序规则变化的定义

Windows排序规则以族的形式出现，每族有18种排序规则，这18种主要是CI,BIN,AI,KS 假名，全半角，重音，二进制的不同

F

旧的排序规则是建立在Windows2000排序规则上的，而新的_100排序规则是建立在Windows2008中的排序规则基础上的 Latin1_General_100（sql2008）和Latin1_General（sql2000）是排序规则对

如果从sql2005用linked server链接sql2008，则应该避免使用新的_100排序规则，否则在访问时会遇到
非法的表格数据流排序规则错误TDS

F

--查看某种排序规则的代码页SELECT COLLATIONPROPERTY('Latin1_General_CS_AS','CODEPAGE') AS '排序规则代码页'--排序规则代码页--1252

不要使用仅支持unicode的排序规则作为服务器排序规则！！！

SELECT RANK=DENSE_RANK() OVER(ORDER BY colatedwork)

F

F

二进制排序规则，不存在二级权值，字符按照字符集中的代码点排序，因此使用前面实例中的Latin_General_BIN2

二进制排序，大写字母排在小写字母前面，这是ASCII标准

F

BIN是早期的二进制排序规则
BIN2是新的二进制排序规则，开发的时候尽量使用BIN2

转换称varbinary变成4100，这是因为PC架构是从小到大的，即最不重要的字节会存储在最前面

SQLSERVER排序规则有76种，其中1种被废弃

从sys.[fn_helpcollations]()看到SQL排序规则和哪种Windows排序规则相匹配

F

F

tertiary weights函数计算三级权重，然后重新排序

F

安装sqlserver时定义的排序规则，如果选择错了，必须重装sqlserver

在复选框里，binary提供BIN二进制排序，而binary-code point提供BIN2 二进制排序

F

F

使用sqlserver排序规则的陷阱
char，varchar数据类型优先级比nchar，nvarchar低，因此列转换成value类型，这种情况下可以使用索引

如果你使用SQL排序规则，不要混用varchar和nvarchar

如果列是nvarchar类型，则必须强制一种二进制排序规则，但是只有当用户能够接受一种区分大小写的查询时才可以

F

特殊数据类型

sql_variant数据类型可以保存除了text，ntext，image，xml，用户定义类型，max的可变长度，rowversion之外的任何数据类型

空间数据类型 sql2008提供了两种数据类型用于存储空间数据，geometry数据类型支持平面或欧几里德数据
geometry数据类型和sql标准版本1.1.0的开放地理信息联盟简单特性相一致，geography数据类型存储椭圆数据
例如全球卫星定位的经度和维度坐标，这些数据类型具有自己的访问和操作数据方法，也有自己特殊的扩展所有架构

F

SELECT ROWGUIDCOL FROM [dbo].[customer]

 

F

SELECT GETANSINULL('sss') 函数确定当前会话的默认可空性，当新列允许空值或该列或数据类型为空而且在表被创建或修改没有明确定义时，该函数返回1，强烈建议在建表时设置列为null或not null，这样可以消除二义性，同时可以保证你控制表的构建方式

F

sql2008引入了稀疏列的概念，当数据库允许很多列和很多行中存在null值，稀疏列通过利用更多的系统开销来检索not null值，从而降低null值的空间需求，因此当大部分数据都为null时，就可以体现出稀疏列的强大

用户定义数据类型

F

F

F

SELECT IDENTITYCOL FROM [dbo].[customer]

 

F

F

SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS]SELECT [ORDINAL_POSITION] FROM [INFORMATION_SCHEMA].[COLUMNS]

提供[ORDINAL_POSITION]值显示列的原始位置，因此column_id不一定是该列的顺序位置，如果删除了某一列或添加了某一列

在sql2008中兼容性视图sys.sysindexes包含很多相同的信息，但是由于sql2005中引入的存储结构变化，因此他是不完整的

F

indexid在2~250或256~1005之间（251~255的值保留）由于一张表可以有999个非聚集索引

SELECT * FROM sys.[xml_indexes] XML索引

SELECT [hobt_id],[partition_id] FROM sys.[partitions] 里的[hobt_id]和[partition_id] 之间有一对一关系

F

SELECT OBJECT_NAME(p.[object_id]) AS NAME ,p.[partition_id] ,p.[partition_number] AS pnum ,p.[rows] ,a.[allocation_unit_id] AS au_id ,a.[type_desc] AS page_type_desc ,a.[total_pages] AS pagesFROM sys.[partitions] pJOIN sys.[allocation_units] a ON p.[partition_id] = a.[container_id]WHERE [object_id] = OBJECT_ID('customer')

 

F

SELECT CONVERT(CHAR(8), OBJECT_NAME(i.object_id)) AS tablename ,*FROM sys.[indexes] AS iINNER JOIN sys.[partitions] AS p ON [p].[object_id] = [i].[object_id]AND [p].[index_id] = [i].[index_id]INNER JOIN [sys].[system_internals_allocation_units] AS a ON a.[container_id] = [p].[partition_id]ORDER BY [i].[object_id]

 

F

数据页面

行偏移数组：看上去是数组形式，实际上是按顺序排列，定义行边界 （数组里的每个元素占用2字节）

数据页面头解释

metadata：该页所在对象ID
metadata：该页所在分区ID
metadata：该页所在分配单元的ID ，交换分区时候需要修改这个分配单元ID
freedata：该页面中的第一个可用空间的字节偏移量
Pminlen：行的固定长度部分的字节数
tornBits：一共有16个位 2字节：每位保存每个扇区的一位
fagBits：包含关于页面其他信息的2字节位图

F

F

想一下内存中的存储桶页面头部的buffer和hash存储桶肯定存在某种关联，然后使用key-value来查找存储桶中的页面

状态B字段中只使用一位，表示该记录是一条备份转发记录

F

F

F

CREATE FUNCTION convert_page_nums(@page_num binary(6))RETURNS VARCHAR(11)AS BEGINRETURN(CONVERT(VARCHAR(2),(CONVERT(INT,SUBSTRING(@page_num,6,1))* POWER(2,8)) +(CONVERT(INT,SUBSTRING(@page_num,5,1)))) + ':' +CONVERT(VARCHAR(11),(CONVERT(INT,SUBSTRING(@page_num,4,1)) * POWER(2,24)) + (CONVERT(INT,SUBSTRING(@page_num,3,1)) * POWER(2,16)) +(CONVERT(INT,SUBSTRING(@page_num,2,1)) * POWER(2,8)) +(CONVERT(INT,SUBSTRING(@page_num,1,1)))))END

SELECT dbo.convert_page_nums(0x6E0000000100);
–SQL Server:将6字节的十六进制页面号转化成【文件号:页面号】格式函数

–http://www.cnblogs.com/xbf321/archive/2009/09/12/1565291.html

 

--使用未记录函数 sys.fn_PhysLocFormatter和一个未记录值%%physloc%%来返回结果行中的物理位置和表中的数据值SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS RID ,* FROM CUSTOMER--RID city region--(1:79:0) 1 2

%%physloc%%值不是关系引擎所能理解的，也就是在where子句中使用%%physloc%%，则sqlserver必须要检查哪些行位于%%physloc%%所指示的页面上，不能使用%%physloc%%来查找行，%%physloc%%作为sqlserver产品开发小组的一种调试功能而引入，不打算在产品应用程序中使用

F

F

SELECT minlen,maxlen FROM sysindexes minlen,maxlen只能在兼容性视图sysindexes里查询，所有目录视图也查不到indexproperty（）函数返回minlen值等于列长度（ sys.COLUMNS 的max_length）加上4字节的和，其中不包括用于存储列数目的2字节，也不包括null位图

SELECT minlen ,
maxlen
FROM sys.sysindexes
WHERE id=OBJECT_ID(‘customer’)

SELECT INDEXPROPERTY(OBJECT_ID(‘customer’), NULL, ‘minlen’) AS ‘最小列长度’

SELECT max_length+4 FROM sys.COLUMNS

 

F

syscolumn不再可靠

SELECT c.[name] AS columnname ,c.[column_id] ,pc.[max_inrow_length] ,[pc].[system_type_id] ,[pc].[leaf_offset]FROM sys.[system_internals_partition_columns] pcJOIN sys.[partitions] p ON p.[partition_id] = pc.[partition_id]JOIN sys.[columns] c ON [column_id] = [partition_column_id]AND c.[object_id] = p.[object_id]WHERE p.[object_id] = OBJECT_ID('aa')

F

F

第一可变长度列  第二可变长度列  第三可变长度列

可变长度列偏移的长度

201

F

 

01111111110
从右到左
col10  col9 。。。。。id
‘a’      null                1

11111111100
从右到左
1 0 0
col10。。。。。 col1    id

                       ‘b’      2

F

F

日期和时间数据的存储

对于datetime和smalldatetime数据类型来说，日期值均被存储为0

F

对于datetimeoffset数据的列用2个字节存储timezone偏移，之所以用2字节是因为偏移量被存储为协调世界时UTC的小时和分钟数每部分一个字节

F

各种日期和时间制的转换

sql_variant数据类型存储

F

F

F

保证实体完整性要有主键

F

F

F

重建一张表或一个分区以修改压缩设置或删除碎片

被修改的列不能是text，image，ntext，rowversion，timestamp列

F

添加一个新列 with values

F

F

启用或禁用触发器

删除某一列时不会回收磁盘空间，除非你在表上建立聚集索引或重建聚集索引的方式回收空间

添加允许null的新列时，sqlserver修改只能是元数据操作

F

USE [sss]CREATE TABLE change(col1 SMALLINT ,col2 CHAR(10) ,col3 CHAR(5))

SELECT c.[name] AS column_name ,
c.[column_id] ,
pc.[max_inrow_length] ,
pc.[system_type_id] ,
pc.[leaf_offset]
FROM sys.[system_internals_partition_columns] pc
JOIN sys.[partitions] p ON p.[partition_id] = [pc].[partition_id]
JOIN sys.[columns] c ON [column_id] = pc.[partition_column_id]
AND c.[object_id] = p.[object_id]
WHERE p.[object_id] = OBJECT_ID(‘change’)

–将smallint修改为int
ALTER TABLE change ALTER COLUMN col1 INT

–col1的位置值在行的更后面，偏移量4处没有任何行
SELECT c.[name] AS column_name ,
c.[column_id] ,
pc.[max_inrow_length] ,
pc.[system_type_id] ,
pc.[leaf_offset]
FROM sys.[system_internals_partition_columns] pc
JOIN sys.[partitions] p ON p.[partition_id] = [pc].[partition_id]
JOIN sys.[columns] c ON [column_id] = pc.[partition_column_id]
AND c.[object_id] = p.[object_id]
WHERE p.[object_id] <span style="c