SQL Server 2000 重温笔记

一、页面（Page）和盘区（Extent）：
Sql Server2000中所有的信息都存储在页面上，除了事务日志（事务日志是由一系列记录组成的）页面是数据库中使用的最小数据单元，每个页面存储8K的信息。Sql Server2000中有以下几种类型的页面：

• 数据页面：用于存储数据库数据，但不包括text,ntext,image类型的数据
• 索引页面：用于存储索引数据
• 文本/图像页面：用于存储text,ntext,image类型的数据
• 全局分配页面：用于存储盘区分配的信息
• 页面剩余空间页面：用于存储页面剩余空间的信息
• 索引分配页面：用于存储页面被表或索引使用的盘区的信息。

盘区是由8个连续页面组成的数据结构，它是Sql 2000中为表和索引分配空间的最小单位，也就是说当创建一个表时，Sql会自动地以盘区为单位给它分配空间。Sql 2000中设计两种盘区：

• 单一盘区：只被单一的数据库对象使用，所有8各页面都用于这个对象。
• 混合盘区：被最多8个对象共同使用，一个新建的表或索引首先从混合盘区分配页面，当表或索引中的数据量增大到8个页面时，再将它转化为单一盘区。

二、特殊的数据库类型：
1、文本和图像数据，在SQL Server中用text, ntext和image数据类型表示，因为它们的数据量往往较大，所以不像表中其他类型的数据那样一行一行地存放在数据页中，而是被存储在专门的页中，在数据行的相应位置只存储指向这些数据实际存储位置的指针。但在SQL 2000中可以指定将text, ntext和image数据类型的数据在行中存储，但要满足2各条件：

• 文本和图像数据的大小不超过7000字节。
• 数据行有足够的空间存放这些数据。

具体设置：sp_tableoption [table],'text in row','ON'或者'OFF'或者是上限值如'1000'
2、用户自定义类型，是数据库的一种对象，就像table, view。。。。。。它一般是基于基础类型的，比如定义了一个email类型，添加默认值，规则检查'@'，当创建表email字段就可以用这个自定义类型。
添加自定义类型：exec sp_addtype enail,'varchar(10)','not null'

三、强制数据完整性机制：
1、主键（PRIMARY KEY）约束，主健可以强制实体完整性。
2、外键（FOREIGN KEY）约束，SQL 2000中增加了级联修改和级联删除的功能，作为外键的列的值可以是空值。
3、惟一性（UNIQUE）约束，和主键约束的区别：一个表上只能定义一个主键约束，但可以定义多个惟一性约束；定义了惟一性约束的列上的数据库可为空。都可以被外键约束引用。
4、检查（CHECK）约束：检查约束可以用来限制列上可以接受的数据值，使用用逻辑表达式来限制列上可以接受的数据。可以在一列上使用多个检查约束，也可以在表上建立一个可以在多个列上使用的检查约束。
5、默认值（DEFAULT）:默认值也是一种数据库对象。可以在创建表时设置，也可以在数据库中创建一个默认值对象，绑定到指定的列。
Create default goodDesp as 'common goods'
exec sp_bindefault 'goodDesp','Goods.Description'
6、空值（NULL）

以上约束实施的完整性称为声明型数据完整性，他们是作为表和列定义的一部分在语法中实现的，还有一种数据完整性的实施方法是过程型数据完整性，它的实现是独立于表存在的，需要与表绑定，如默认值，规则，触发器等数据库对象，这些数据完整性不是一个表独有的，可以应用于数据库中不同的表。

四、在表的操作中应用声明型数据完整性：
1、命名约束：用Constraint  约束名 约束类别，如果是命名约束就可以做更改或删除等操作。如：
Create table Goods
(
　　name varchar(10)
　　Constraint Pk_GoodName Primary Key,
　　...
　　status tinyint default(0)
)

2、非命名约束：
Create table Goods
(
　　name varchar(10) Primary Key,
　　...
　　status tinyint default(0)
)

3、创建外键约束：

• References table(field) on delete no action(sql 7.0 没有级联删除)/cascade(sql 2000级联删除) ,没有Foreign Keny 子句
• Foreign Key (field) References table(field) on delete no action(sql 7.0 没有级联删除)/cascade(sql 2000级联删除)

Create tabel orders
(
　　orderId int Indentity(1,1) constraint PK_OrderID primary key,
　　goodName varchar(20) not null references Goods(name) on delete cascade，
　　customerID int not null foreign key(custimerID) references Customers(customerid) on delete no action,
　　quantity int not null constraint CK_Quantity check(quantity > 0)
)

4、修改，添加，删除列
Alter table customers alter column name varchar(40) not null
Alter table customers add email varchar(50) null constraint CK_EA check(email like '%@%')
Alter table customers drop column email

5、修改约束：'with check or nocheck'表示加约束时，检查还是不检查表中已有的数据。'nocheck'使约束无效，'check'使约束重新有效。
Alter table Goods with nocheck add/drop constraint pk_Name Primary Key(name)
Alter table Goods nocheck/check constraint pk_Name

6、where和having的区别：

• where针对单行，having针对一组行
• 在Group by子句分组前去掉不满足where条件的行，而having子句中的条件在分组之后被应用。
• having子句可在条件中包含聚合函数，而where不行

select name, sum(Quantity) as sumQuty from orders
group by name having sum(Quantity)>500

7、computer 返回所有行的集合，computer ... by 子句返回一个分组后记录的集合
select name Quantity from orders
computer sum(Quantity) [by name]
返回两个结果

8、可以用with encryption 给视图，存储过程，触发器加密，加密后任何权限的人都看不到具体的内容。

五、规则：是数据库对象之一，它的作用与check约束的部分功能相同，在向表的某列插入或更新数据时，用它来限制输入值得取值范围。规则和约束的不同之处在于：
1、check约束是建表时指定的，规则需要作为单独的数据库对象来实现
2、在一列上只能使用一个规则，但可以使用多个check约束
3、规则可以应用于多个列，还可以用于自定义类型，而check只能用于定义它的列
应用规则需要首先定义规则，在绑定：
Create rule rule_name as @value >0
sp_bindrul/sp_unbindrule rule_name field
eg: exec sp_bindrule 'range_rule','orders.Quantity'

六、客户程序访问数据库过程：
1、查询语句被发送到服务器。如果是存储过程，则发送的sql很少，减少带宽压力。用户看不到表，所以存储过程可以作为安全机制。
2、服务器编译T_SQL语句
3、优化产生查询计划。 2，3这两步都在存储过程中已经做了，所以存储过程速度比较快。
4、数据库引擎执行查询
5、执行结果发回客户程序

存储过程最好不要用sp_作为其名称的前缀，因为sql server是以下面的顺序来查找以sp_开头的存储过程：
1、在master数据库查找
2、如果存储过程指定了数据库和所有者，则查找基于指定数据库和所有者的存储过程
3、如果没有制定存储过程的数据库和所有者，则以DBO作为所有者来查找
4、若用户定义的存储过程和系统存储过程同名，用户定义的存储过程永远不会被执行

七、触发器：触发器是一种特殊的存储过程。触发器和引起触发器执行的T_SQL语句被当作一次事务处理，因此可以在触发器中回滚这个事务。如果发现引起触发器执行的T_SQL语句执行了一个非法操作，则可以通过回滚事务使语句不能执行，回滚后SQL Server会自动返回到此事务执行前的状态。
1、Inserted表和Deleted表在触发器执行过程中的作用?
SQL Server为每个触发器都创建了Inserted和Deleted表，这是两个逻辑表，由系统来维护，用户不能对它们进行修改。它们存放在内存而不是数据库，它们的表结构与被该触发器作用的表结构相同。触发器执行完成后，这两个表也会被删除。

• Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。也就是说在执行Delete或Update时，被删除的行从激活触发器的表中被移到Deleted表，这两个表不会有共同的行。
• Inserted表存放由于执行Insert或Update语句而要象表中插入的所有行。在Insert或Update事务中，新的行同时添加到激活触发器的表和Inserted表中，Inserted表的内容是激活触发器的表中新行的拷贝。

一个Update事务是首先执行一个Delete操作，在执行一个Insert操作，旧行首先被移到Deleted表，然后新行同时插入激活触发器的表和Inserted表。
Create trigger goodsDelete on Goods after delete as
delete from orders where goodsName in (select name from deleted)
Create trigger OrderInsert on Orders after insert as
if(select status from Goods,inserted where Goods.name = inserted.goodsName)=1
begin
　　print 'The goods is being processed, the order cannot be committed'
　　rollback transaction
end

八、索引：索引是一种物理结构，它能够提供一种以一列或多列的值为基础迅速查找表中行的能力。索引中记录了表中的关键值，提供了指向表中行的指针。索引使得数据库应用程序能够不扫瞄全表而找到想要数据。
1、无索引和有索引的查询过程：
如果某个字段没有索引，要按此字段查询时，就要按照存储顺序一行一行的查询所有记录，看对应字段是否符合条件。如果在对应列上创建了索引，那么SQL Server就首先搜索这个索引，在索引中找到符合条件的值，再按照索引中记录的相应行在表中的位置找到表中的行。由于索引进行了分类，且索引包含的行和列比全表少的多，所以索引搜索是很快的。
2、索引越多越好吗？
不是，一是因为创建索引要花费时间和占用存储空间，二是因为索引虽然加快了搜索速度，却减慢了数据修改和插入的速度。因为每当执行一次数据修改（插入，删除，更新）时，就要进行索引的维护，对建立了索引的列执行操作要比未建立索引的列执行修改操作所花的时间长，修改的数据越多，涉及维护索引的开销就越大。
3、索引的分类：
按索引的结构分聚集索引和非聚集索引，按索引实现的功能分惟一性索引和非惟一性索引。惟一性索引能保证在创建索引的列或多列组合上不包括重复的数据，聚集和非聚集索引都可以是惟一性索引。在创建主键约束和惟一性约束的列上会自动创建惟一性索引。

• 聚集索引
• 非聚集索引