Jimmy那些事儿

SQL Server_系统结构

发表 | 更新 | 分类 ， ， | 阅读次数

SQL Server 的特殊结构：表变量、临时表

表变量与临时表

表变量

  • what：微软在BOL (Declare @local_variable)中定义其为一个类型为表的变量；定义表变量的语句是和正常使用Create table定义表语句的子集。只是表变量通过DECLARE @local_variable 语句进行定义。
    • 它的具体定义包括列定义，列名，数据类型和约束。
    • 表变量中可以使用的约束：主键约束，唯一约束，Null约束和Check约束 (外键约束不能在表变量中使用)
  • why ：
  • how ：
    • 表变量只能在一个批处理中生成，超出了这个边界表变量就不存在了；
    • 以@或者@@为前缀
      • 一个@为前缀的表变量（@aa）是本地的，因此只有当前用户会话才可以访问
      • 两个@为前缀即@@的表变量（@@aa）是全局的，通常都是系统变量
    • 表变量只能在一个批处理中生成，超出了这个边界表变量就不存在了
    • 存放在内存中，因此用户访问表变量的时候SQL Server是不需要生成日志的。同时变量是不需要考虑其他会话访问的问题，因此也不需要考虑锁机制，对于非常繁忙的系统来说，避免锁的使用可以减少一部分系统负载。

临时表

  • what：以#或者##为前缀；
    • 一个 # 为前缀的临时表(#a)是本地的，只有当前用户会话才可以访问
    • ## 为前缀的临时表(##a)是全局的，所有用户会话都可以访问该临时表
  • why ：临时表以会话为边界，会话结束则临时表移除；如果需要提前销毁临时表，可以使用DROP TABLE命令

  • how ：因为存放在tempdb，所以可能造成物理IO，在修改时也需要生成日志来确保一致性，同时锁机制也是不可缺少的

    • ​
    • ​

表变量 vs. 临时表

  • 临时表是利用了硬盘(tempdb数据库) ，表名变量是占用内存，因此小数据量当然是内存中的表变量更快。当大数据量时，就不能用表变量了，太耗内存了。大数据量时适合用临时表。
    • 一般对于大的数据集推荐使用临时表，同时创建索引，或者通过SQL Server的统计数据自动创建和维护功能来提供访问SQL语句的优化；
    • 如果需要在多个用户会话间数据交换，临时表就是唯一的选择了。在SQL Server临时表的定义中，我们知道临时表是存放在tempdb中的，因此需要注意tempdb的调优
特性 表变量 临时表
作用域 当前批处理 当前会话，嵌套存储过程，全局：所有会话
析构方式 批处理结束后自动析构 显式调用 DROP TABLE 语句. 当前会话结束自动析构 (全局临时表: 还包括当其它会话语句不在引用表.)
使用场景 自定义函数，存储过程，批处理 自定义函数，存储过程，批处理
索引 不可创建（索引必须在表定义时建立） 可以创建索引
约束 PRIMARY KEY, UNIQUE, NULL, CHECK约束可以使用，但必须在表建立时声明 PRIMARY KEY, UNIQUE, NULL, CHECK. 约束可以使用，可以在任何时后添加，但不能有外键约束
统计数据 不创建统计数据，所以所有的估计行数都为1,所以生成执行计划会不精准 创建统计数据，通过实际的行数生成执行计划。
数据插入方式 INSERT 语句 (SQL 2000: 不能使用INSERT/EXEC). INSERT 语句, 包括 INSERT/EXEC. SELECT INTO 语句.
回滚 不会被回滚影响 会被回滚影响
事务 只会在更新表的时候有事务，持续时间比临时表短 正常的事务长度，比表变量长
创建方式 DECLARE statement only.只能通过DECLEARE语句创建 CREATE TABLE 语句 SELECT INTO 语句.
列类型 可以使用自定义数据类型；可以使用XML集合 自定义数据类型和XML集合必须在TempDb内定义
表名长度 最多128字节 最多116字节
Collation 字符串排序规则继承自当前数据库 字符串排序规则继承自TempDb数据库
表建立后使用DDL (索引，列) 不允许 允许.
Insert explicit values into identity columns (SET IDENTITY_INSERT). 不支持SET IDENTITY_INSERT语句 支持SET IDENTITY_INSERT语句
Truncate table 不允许 允许
存储过程重编译 否 会导致重编译
作为参数传入存储过程 仅仅在SQL Server2008, 并且必须预定义 user-defined table type. 不允许
显式命名对象 (索引, 约束). 不允许 允许，但是要注意多用户的问题
动态SQL 必须在动态SQL中定义表变量 可以在调用动态SQL之前定义临时表

CTE

  • what：通用表表达式
  • why ：在同一个语句中，一次定义，可以多次引用。

专业词汇及缩写

字段名 全程 含义
dbo Database Owner 每个数据库的默认用户
db database 数据库
dt datetime 数据库数据类型（日期）
etl Extract-Transform-Load 数据仓库技术; 抽取-转化-加载
ext Extended file system 延伸文件系统/ 扩展文件系统
DDL Database Definition Language 数据库定义语言
DML Database Manipulation Language 数据库操作语言
DCL 数据控制语言
TCL 事务控制语言

dbo： 每个数据库的默认用户，具有所有者权限，即DbOwner；Database Owner

通过用DBO作为所有者来定义对象，能够使数据库中的任何用户引用而不必提供所有者名称

比如：你以User1登录进去并建表Table，而未指定DBO，当用户User2登进去想访问Table时就得知道这个Table是你User1建立的，要写上User1.Table，如果他不知道是你建的，则访问会有问题。如果你建表时把所有者指给了Dbo，则别的用户进来时写上Dbo.Table就行了，不必知道User1。

DML

查询(Select)、插入(Insert)、修改(Update)、删除(Delete)

OLE DB

  • what ：对象链接嵌入数据库（Object Linking and Embedding Database），是微软为以统一方式访问不同类型的数据存储设计的一种应用程序接口。
    • 是一组用组件对象模型（COM）实现的接口，而与对象连接与嵌入（OLE）无关。
  • why ：它被设计成为ODBC的一种高级替代者和继承者，把它的功能扩展到支持更多种类的非关系型数据库，例如可能不支持SQL的对象数据库和电子表格（如Excel）。
  • how ：一组抽象概念（包括数据源、会话、命令和行集）将数据的存储从需要访问数据的应用中分离出来。
    • 这是因为不同的应用需要访问不同数据类型和数据源，但是并不需要了解具体如何使用特定技术的方法访问这些数据。

OLE DB 在概念上分为两种：

  1. 消费者 ：是那些需要访问数据的应用程序
  2. 提供者 ：是实现了那些接口并将数据提供给消费者的软件组件。

OLE DB是微软数据访问组件（MDAC）的一部分。

  • MDAC是一组微软技术，以框架的方式相互作用，为程序员开发访问几乎任何数据存储提供了一个统一并全面的方法。

OLE DB的提供者可以用于提供像文本文件和电子表格一样简单的数据存储的访问，也可以提供像Oracle、SQL Server和Sybase ASE一样复杂的数据库的访问。

OLE DB同样可以提供对层次类型的数据存储（如电子邮件系统）的访问。

组件对象模型

  • what ：微软的一套软件组件的二进制接口标准。（Component Object Model，COM）
  • why ：使得跨编程语言的进程间通信、动态对象创建成为可能。
  • how ：

数据库名、表明、字段名

1
2
3
4
5
6
7
8
9
10
# 获取数据库所有类型
select name from systypes 
-----------------------------------------------------
# 获取所有用户名
SELECT name FROM Sysusers where status='2' and islogin='1'
islogin='1'表示帐户
islogin='0'表示角色
status='2'表示用户帐户
status='0'表示糸统帐户
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 获取所有数据库名
Select Name FROM Master..SysDatabases order by Name；
-----------------------------------------------------
# 获取所有表名
Select Name FROM SysObjects Where XType='U' ORDER BY Name 
  XType='U':表示所有用户表; 
  XType='S':表示所有系统表;
SELECT name FROM sysobjects WHERE type = 'U' AND sysstat = '83'
注意：一般情况只需要type = 'U'，但有时候会有系统表混在其中（不知道什么原因），加上后面一句后就能删除这些系统表了
# 获取指定数据库名下的所有表名
select Name from 数据库名..sysobjects where xtype='u' and status>=0
1
2
3
4
5
6
7
8
9
10
# 获取所有字段名
Select Name FROM SysColumns Where id=Object_Id('TableName')；
或者
select name from syscolumns where id =(select id from sysobjects where type = 'u' and name= 'TableName')  
-----------------------------------------------------
# 获取主键字段
SELECT  name FROM SysColumns 
  WHERE id=Object_Id('表名') 
  and colid=(select top 1 keyno from sysindexkeys where id=Object_Id('表名'))；