SQLServer技术精华

SQLServer技术精华
1.SQL Server 2000拥有两类数据库
1..系统数据库 master model msdb tempdb
2..示例数据库 pubs northwind

2.SQL Server 数据库有三类文件
1..主要数据文件
2..次要数据文件
3..日志文件

3.DDL语句对数据库对象如表、列和视进行定义。它们并不对表中的行进行处理;
DDL语句并不处理数据库中实际的数据。DML则对表中的数据进行处理。

DDL: 数据定义语言，定义数据库中的表，视图和索引等。如 create drop alter语句
DML：数据操纵语言，保存，更新或删除数据   如 insert update delete
DQL：数据查询语言，查询数据库中的数据    如select语句
DCL：数据控制语言，用于设置数据库用户的权限如grant revoke语句

创建数据库
create database 库名
删除数据库
drop database 库名

创建主键约束的方法
1.create table student(studid int primary key,name varchar(20));
2.alter table student add constraint PK_studid primary key (studid) //字段名
如果要更改主键，必须要先删除已有的主键约束，方法如下：
drop table student drop PK_studid   //drop 约束名
创建唯一约束的方法
1.create table student(studid int,studentname varchar(10) unique);
创建自增长型的方法
create table reservation (pro int identity(1,1))//pro起始值为1，每次向表中添加记录时，该值为自动增加1
创建缺省约束以及检查约束的方法
create table student (studid char(10) default 'c001', //设置缺省值
  name varchar(10) check name like '[A-Z][A-Z]%'    //字段name需要进行检查
  )
示例如下：
create table carinfo (carID char(10),name char(10),type char(2),Drive_type char(15) );
create table carfactory (id char(10) not null,name char(10),postcade char(6),
  telphone char(15),address char(30) );
  //增加主键约束
  alter table carfactory add constraint pk_carfactory primary key (id);
  //外键约束
  alter table carInfo with nocheck
  add constraint FK_carID foreign key carID reference carfactory(id);

创建表以及外键约束的方法如下：
create table Class (ClassID int primary key,ClassName varchar(20))

建表时创建外健约束
create table Student(StudID int not null,ClassID int,StudentName varchar(20),birthday datetime,description varchar(200),
constraint foreign_emp foreign key(ClassID) references Class(ClassID) )

insert into Class values (1,'大一');
insert into Class values (2,'大二');
insert into Class values (3,'大三');
insert into Student values(1,1,'王小二','1998-10-2','this is my classmate')
insert into Student values(2,2,'王小三',getdate(),'this is my classmate2')
insert into Student values(3,3,'王小四','10/18/2006','this is my classmate3')

alter table Student
add constraint PK_key primary key(StudID) //增加学号为主键
alter table Student
add constraint CHECK_key check(studID not null) //对学号进行检查约束

constraint PK_primary primary key(studid)

1.添加列名与删除列名
alter table 表名 add 列名类型
如：alter table customer add name varchar(20)
alter table 表名 drop column 类型
如：alter table customer drop column name

2.添加约束和删除约束
alter table tableName add constraint constrainName type
如：alter table customer add constraint pk_constraint check(customerId!='test')
alter table tableName drop constraint constraintName
如：alter table tableName drop constraint pk_constraint

3.insert into 语句
语法：insert into 表名(字段名1,字段名2,.....) values(值1,值2,......)
例如，要将雇员John Smith的记录插入到本例的表中，可以使用如下语句：
　INSERT INTO EMPLOYEES VALUES (’Smith’,’John’,’1980-06-10’, ’Los Angles’,16,45000);
　通过这样的INSERT语句，系统将试着将这些值填入到相应的列中。这些列按照我们创建表时定义的顺序
排列。在本例中，第一个值“Smith”将填到第一个列LAST_NAME中；第二个值“John”将填到第二列FIRST_NAME
中……以此类推。
　　我们说过系统会“试着”将值填入，除了执行规则之外它还要进行类型检查。如果类型不符
（如将一个字符串填入到类型为数字的列中），系统将拒绝这一次操作并返回一个错误信息。
　　如果SQL拒绝了你所填入的一列值，语句中其他各列的值也不会填入。这是因为SQL提供对事务的支持。一次事务将数据库从一种一致性转移到另一种一致性。如果事务的某一部分失败，则整个事务都会失败，系统将会被恢复（或称之为回退）到此事务之前的状态。
　　注意事项：所有的整字都不需要用单引号引起来，而字符串和日期类型的值都要用单引号来区别。为了增加可读性而在数字间插入逗号将会引起错误。记住，在SQL中逗号是元素的分隔符。
　　同样要注意输入文字值时要使用单引号。双引号用来封装限界标识符。
　　对日期类型，我们必须使用SQL标准日期格式（yyyy-mm-dd），但是在系统中可以进行定义，以接受其他的格式。当然，2000年临近，请你最好还是使用四位来表示年份。
　　既然你已经理解了INSERT语句是怎样工作的了，让我们转到EMPLOYEES表中的其他部分：
　　INSERT INTO EMPLOYEES VALUES (’Bunyan’,’Paul’,’1970-07-04’, ’Boston’,12,70000);
　　INSERT INTO EMPLOYEES VALUES (’John’,’Adams’,’1992-01-21’, ’Boston’,20,100000);
　　INSERT INTO EMPLOYEES VALUES (’Smith’,’Pocahontas’,’1976-04-06’, ’Los Angles’,12,100000);
　　INSERT INTO EMPLOYEES VALUES (’Smith’,’Bessie’,’1940-05-02’, ’Boston’,5,200000);
　　INSERT INTO EMPLOYEES VALUES (’Jones’,’Davy’,’1970-10-10’, ’Boston’,8,45000);
　　INSERT INTO EMPLOYEES VALUES (’Jones’,’Indiana’,’1992-02-01’, ’Chicago’,NULL,NULL);

　　在最后一项中，我们不知道Jones先生的工薪级别和年薪，所以我们输入NULL（不要引号）。
NULL是SQL中的一种特殊情况，我们以后将进行详细的讨论。现在我们只需认为NULL表示一种未知的值。
我们可能希望对某一些而不是全部的列进行赋值。除了对要省略的列输入NULL外，还可以采用另外一种
INSERT语句，如下：
　　INSERT INTO EMPLOYEES( FIRST_NAME, LAST_NAME, HIRE_DATE, BRANCH_OFFICE)
　　VALUE( ’Indiana’,’Jones’, ’1992-02-01’,’Indianapolis’);

　　这样，我们先在表名之后列出一系列列名。未列出的列中将自动填入缺省值，如果没有设置缺省值则填入NULL。请注意我们改变了列的顺序，而值的顺序要对应新的列的顺序。如果该语句中省略了FIRST_NAME和LAST_NAME项（这两项规定不能为空），SQL操作将失败。
　　让我们来看一看上述INSERT语句的语法图：
　　INSERT INTO table [(column { ,column})] VALUES (columnvalue [{,columnvalue}]);

3.1 将一个表的数据添加到另一个表中
     insert into 表名 select 列名,列名,.....from 另一表名
  还可以将现有表中的数据有选择性地添加到另一个表中
  insert into 表名(列名,....) select 列名,列名,.....from 另一表名

4. select查询语句
复杂的语法如下：
SELECT [ ALL | DISTINCT ]
[ TOP n [PERCENT] [ WITH TIES] ]
<select_list>  所要查询的表的列的集合多个列之间用逗号分开
<select_list> ::=
{ *
| { table_name | view_name | table_alias }.*
| { column_name | expression | IDENTITYCOL | ROWGUIDCOL }
[ [AS] column_alias ]
| column_alias = expression

参数介绍：
ALL：指明查询结果中可以显示值相同的列。是系统默认的

DISTINCT：指明查询结果中如果有值相同的列则只显示其中的一列。对DISTINCT 选项来说Null值被认为是相同的值

top n [PERCENT]：指定返回查询结果的前n行数据。如果PERCENT 关键字指定的话则返回查询结果的前百分之n 行数据

<select_list>  所要查询的表的列的集合多个列之间用逗号分开

* 通配符返回所有对象的所有列
table_name | view_name | table_alias.*：限制通配符*的作用范围。凡是带*的项均返回其中所有的列

INTO子句：用于把查询结果存放到一个新建的表中SELECT...INTO 句式。不能与COMPUTE 子句一起使用
示例如下：select * from product into newtablename
newtablename 指定了新建的表的名称新表的列由SELECT 子句中指定的列构成.新表中的数据行是由WHERE 子句指定的

语法如下：select 字段名或者* from 表名
示例：显示地区来自于Ac的所有的作者名称
  select aname from authors where state ="AC"

示例：显示authors表中来自AC地区的作者，按作者名字排序
  select aname from authors where state='AC' order by aname;

  使用 as 关键字用于表示改变结果集中列的名称
  select aname as '姓名' from authors

  使用 top n 子句限制查询返回的行数
  select top 10 from author     //显示记录集的前10条记录
  select top 40 percent from author //显示前40%的记录

  使用模糊查询
   返回authors表中列aname以p开头的所有数据行
    select * from authors where aname like 'p%'
   查询时列a_code第1个字符为M，第2个字符为A或C，第3个字符为0，第4个字符为1或2
    select * from flight where a_code like 'M[AC]0[12]'
   like运算符：用于匹配字符串的一部分
   in运算符：用于返回给定的列是否在in之内
   between运算符：指定要匹配的值在范围之内
   示例：
   select title,title_id,type,price,ytd_sales from title
   where ytd_sales between 4094 and 498

5.update更新语句：UPDATE语句允许用户在已知的表中对现有的行进行修改。
　　例如，我们刚刚发现Indiana Jones的等级为16，工资为$40,000.00，我们可以通过下面的SQL语句对
数据库进行更新（并清除那些烦人的NULL）。
　　UPDATE EMPLOYEES SET GRADE = 16, SALARY = 40000 WHERE FIRST_NAME = ’Indiana’ AND LAST_NAME = ’Jones’;

　　上面的例子说明了一个单行更新，但是UPDATE语句可以对多行进行操作。满足WHERE条件的所有行都将被
更新。如果，你想让Boston办事处中的所有职员搬到New York，你可以使用如下语句：

　　UPDATE EMPLOYEES SET BRANCH_OFFICE = ’New York’ WHERE BRANCH_OFFICE = ’Boston’;

　　如果忽略WHERE子句,表中所有行中的部门值都将被更新为’New York’。
　　UPDATE语句的语法结构如下面所示：
　　UPDATE table SET column = value [{, column = value}]
[ WHERE predicate [ { logical-connector predicate}]];

UPDATE 示例
在ORACLE数据库中
表 A ( ID ,FIRSTNAME,LASTNAME )
表 B( ID,LASTNAME)

表 A 中原来ID,FIRSTNAME两个字段的数据是完整的
表 B中原来ID,LASTNAME两个字段的数据是完整的
现在要把表 B中的LASTNAME字段的相应的数据填入到A表中LASTNAME相应的位置。两个表中的ID字段是相互关联的。
语句如下：
update a set a.lastname=(select b.lastname from b where a.id=b.id)

6.删除记录
语法：delete from 表名 where 条件语句
设置级联删除：同时删除
  alter table orders add index IDx_customer (Customer_ID),
  add constraint FK_CUSTOMER foreign key(Customer_ID) refences customers(id)
  on delete cascade;
设置了级联删除选项后，当用户通过以下delete语句删除Customers表中ID为1时的记录时
delete from customers where id=1; 数据库系统会自动级联删除ORDERS表中所有Customer_ID为1的记录。


5.连接
示例表：
create table CUSTOMERS (
    ID bigint not null,
    NAME varchar(15) not null,
    AGE int,
    primary key (ID)
);
create table ORDERS (
    ID bigint not null,
    ORDER_NUMBER varchar(15) not null,
    PRICE double precision,
    CUSTOMER_ID bigint,
    primary key (ID)
);
alter table ORDERS add index FK_CUSTOMER (CUSTOMER_ID), add constraint FK_CUSTOMER foreign key (CUSTOMER_ID) references CUSTOMERS (ID);

insert into CUSTOMERS(ID,NAME,AGE) values(1,'Tom',21);
insert into CUSTOMERS(ID,NAME,AGE) values(2,'Mike',24);
insert into CUSTOMERS(ID,NAME,AGE) values(3,'Jack',30);
insert into CUSTOMERS(ID,NAME,AGE) values(4,'Linda',25);
insert into CUSTOMERS(ID,NAME,AGE) values(5,'Tom',null);

insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) values(1,'Tom_Order001',100,1);
insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) values(2,'Tom_Order002',200,1);
insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) values(3,'Tom_Order003',300,1);
insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) values(4,'Mike_Order001',100,2);
insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) values(5,'Jack_Order001',200,3);
insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) values(6,'Linda_Order001',100,4);
insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) values(7,'UnknownOrder',200,null);

5.1 交叉连接
   select * from customers,orders;   //返回两个表的所有的记录的笛卡儿积

5.2 内连接
  当来自两个表的公共列匹配时，才会组成相应的记录并添加到查询结果中。
  示例：update titles set price = price+10 from titles
     inner join titleauthor
     on titles.titleid = titleauthor.titleid
     where title.title='shanghai'
  显示内连接：使用inner join关键字，在on子句中设定连接条件：
  select a.*,b.* from customers a inner join orders b on a.id=b.customer_id;
  隐式内连接查询：在where子句中设定连接条件
  select a.*,b.* from customers a , orders b where a.id=b.customer_id;
5.3 外连接
   a.左外连接查询：使用left outer join关键字，在on子句中设定连接条件。当然也可以带其他条件，
   但是必须用where来表示。
   select a.*,b.* from customers a left outer join orders b on a.id=b.customer_id
得到的结果不仅包含条件a.id=b.customerid的所有记录，还包括左边表customers的其他数据行，不管它有没有满足条件
  示例：
select a.*,b.* from customers a left outer join orders b on a.id=b.customer_id where a.id>4 and c.id>2

  b.右外连接查询：使用right outer join关键字，在on子句中设定连接条件。当然也可以带其他条件，
   但是必须用where来表示。
   select a.*,b.* from customers a right outer join orders b on a.id=b.customer_id
得到的结果不仅包含条件a.id=b.customerid的所有记录，还包括右边表customers的其他数据行，不管它有没有满足条件
示例：
select a.*,b.* from customers a right outer join orders b on a.id=b.customer_id where a.id>4 and c.id>2

5.4 子查询
   又叫嵌套查询，是指在select子句或者where子句中又嵌套select查询语句
  示例：(1)查询具有三个以上订单的客户
    select * from customers c
    where (select count(*) from orders o where c.id=o.customer_id)
      >=3;

  示例：(2)查询名为mike的客户的所有订单
    select * form orders o where o.customer_id in(select id form customers where name ='mike');

5.5 联合查询
   是指能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。
   联合查询使用union关键字。
   示例：
    select * from customers where age<25 union select * from customers where age>=24;

6.内部命令
1.1.创建自定义的数据类型
exec sp_addtype 数据类型名称,类型的字符数,初始化值
示例如下：
exec sp_addtype city,'nvarchar(15)',null
create table ab ( id int,name city );
insert into ab values ( 1,'marry');
insert into ab values ( 2,'hurry');

1.2.删除自定义的数据类型
exec sp_droptype 类型名称
示例： exec sp_droptype city;

1.3.设置数据库为只读
exec sp_dboption '库名','read only',true
如：exec sp_dboption 'student','read only',true; //设置数据库student为只读的
解锁为不是只读的 exec sp_dboption 'student','read only',false;

1.4.允许多用户访问
  exec sp_dboption 'student','single user',true   //单用户
  exec sp_dboption 'student','single user', false //多用户

1.5.设置数据库文件收缩
  exec sp_dboption 'student',autoshrink,true;
  exec sp_dboption 'student',autoshrink,false;

函数部分
.1.AVG --求平均值
示例：求各部门员工表employee的平均年轻
select avg(e_wage) as dept_avgWage from employee group by dept_id;

.2.MAX --求最大值
示例：求工资最高的员工姓名用了嵌套的select语句
select e_name from employee where e_wage = ( select max(e_wage) from employee);

.3.STDEV()函数返回表达式中所有数据的标准差
STDEVP()函数返回总体标准差
VAR()函数返回表达式中所有值的统计变异数
VARP()函数返回总体变异数

.4.----算术函数----

/***三角函数***/
SIN(float_expression) --返回以弧度表示的角的正弦
COS(float_expression) --返回以弧度表示的角的余弦
TAN(float_expression) --返回以弧度表示的角的正切
COT(float_expression) --返回以弧度表示的角的余切

/***反三角函数***/
ASIN(float_expression) --返回正弦是FLOAT 值的以弧度表示的角
ACOS(float_expression) --返回余弦是FLOAT 值的以弧度表示的角
ATAN(float_expression) --返回正切是FLOAT 值的以弧度表示的角
ATAN2(float_expression1,float_expression2) --返回正切是float_expression1 /float_expres-sion2的以弧度表示的角
DEGREES(numeric_expression)--把弧度转换为角度返回与表达式相同的数据类型可为--INTEGER/MONEY/REAL/FLOAT 类型
RADIANS(numeric_expression) --把角度转换为弧度返回与表达式相同的数据类型可为--INTEGER/MONEY/REAL/FLOAT 类型
EXP(float_expression) --返回表达式的指数值
LOG(float_expression) --返回表达式的自然对数值
LOG10(float_expression)--返回表达式的以10 为底的对数值
SQRT(float_expression) --返回表达式的平方根

/***取近似值函数***/
CEILING(numeric_expression) --返回>=表达式的最小整数返回的数据类型与表达式相同可为--INTEGER/MONEY/REAL/FLOAT 类型
FLOOR(numeric_expression) --返回<=表达式的最小整数返回的数据类型与表达式相同可为--INTEGER/MONEY/REAL/FLOAT 类型
ROUND(numeric_expression) --返回以integer_expression 为精度的四舍五入值返回的数据--类型与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
ABS(numeric_expression) --返回表达式的绝对值返回的数据类型与表达式相同可为--INTEGER/MONEY/REAL/FLOAT 类型
SIGN(numeric_expression) --测试参数的正负号返回0 零值1 正数或-1 负数返回的数据类型--与表达式相同可为INTEGER/MONEY/REAL/FLOAT 类型
PI() --返回值为π 即3.1415926535897936
RAND([integer_expression]) --用任选的[integer_expression]做种子值得出0-1 间的随机浮点数

----字符串函数----
ASCII() --函数返回字符表达式最左端字符的ASCII 码值
CHAR() --函数用于将ASCII 码转换为字符
--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 值
LOWER() --函数把字符串全部转换为小写
UPPER() --函数把字符串全部转换为大写
STR() --函数把数值型数据转换为字符型数据
LTRIM() --函数把字符串头部的空格去掉
RTRIM() --函数把字符串尾部的空格去掉
LEFT(),RIGHT(),SUBSTRING() --函数返回部分字符串
CHARINDEX(),PATINDEX() --函数返回字符串中某个指定的子串出现的开始位置
SOUNDEX() --函数返回一个四位字符码
--SOUNDEX函数可用来查找声音相似的字符串但SOUNDEX函数对数字和汉字均只返回0 值
DIFFERENCE() --函数返回由SOUNDEX 函数返回的两个字符表达式的值的差异
  --0 两个SOUNDEX 函数返回值的第一个字符不同
  --1 两个SOUNDEX 函数返回值的第一个字符相同
  --2 两个SOUNDEX 函数返回值的第一二个字符相同
  --3 两个SOUNDEX 函数返回值的第一二三个字符相同
  --4 两个SOUNDEX 函数返回值完全相同

REPLACE() --函数返回被替换了指定子串的字符串
如：select replace('abc123g', '123', 'def')
SPACE() --函数返回一个有指定长度的空白字符串
STUFF() --函数用另一子串替换字符串指定位置长度的子串

----数据类型转换函数----
CAST() 函数语法如下
CAST() (<expression> AS <data_ type>[ length ])
CONVERT() 函数语法如下
CONVERT() (<data_ type>[ length ], <expression> [, style])
示例：
select cast(100+99 as char) convert(varchar(12), getdate())
结果：199 Jan 15 2000

----日期函数----
DAY() --函数返回date_expression 中的日期值
MONTH() --函数返回date_expression 中的月份值
YEAR() --函数返回date_expression 中的年份值
DATEADD(<datepart> ,<number> ,<date>)
--函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期

索引部分
1.在SQL Server 的数据库中按存储结构的不同将索引分为两类：
  簇索引Clustered Index ：簇索引对表的物理数据页中的数据按列进行排序然后再重新存储到磁盘上
  非簇索引Nonclustered Index：非簇索引具有与表的数据完全分离的结构使用非簇索引不用将物理数据页中的数据按列排序
2.语法如下：
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]INDEX index_name ON {table | view }
  column [ ASC | DESC ] [,...n])[with [PAD_INDEX]
  [ [, ] FILLFACTOR = fillfactor]
  [ [, ] IGNORE_DUP_KEY] 此选项控制了当往包含于一个惟一约束中的列中插入重复数据时SQL Server 所作的
反应
  [ [, ] DROP_EXISTING] 指定要删除并重新创建簇索引
  [ [, ] STATISTICS_NORECOMPUTE] 指定分布统计不自动更新
  [ [, ] SORT_IN_TEMPDB ]] 指定用于创建索引的分类排序结果将被存储到Tempdb 数据库
  [ON filegroup]  指定存放索引的文件组
  参数说明：
   UNIQUE：创建一个惟一索引即索引的键值不重复在列包含重复值时不能建惟一索引。如要使用此选项
       则应确定索引所包含的列均不允许NULL 值。否则在使用时会经常出错

   CLUSTERED：指明创建的索引为簇索引。如果此选项缺省则创建的索引为非簇索引

   NONCLUSTERED：指明创建的索引为非簇索引。其索引数据页中包含了指向数据库中实际的表数据页的指针

   index_name：指定所创建的索引的名称索引名称。在一个表中应是惟一的但在同一数据库或不同数据库中可以重复

   table：指定创建索引的表的名称。必要时还应指明数据库名称和所有者名称。

   view：指定创建索引的视图的名称。视图必须是使用SCHEMABINDING 选项定义过的其具体信息。

   ASC | DESC：指定特定的索引列的排序方式默认值是升序ASC

   column：指定被索引的列。如果使用两个或两个以上的列组成一个索引则称为复合索引。
       一个索引中最多可以指定16 个列，但列的数据类型的长度和不能超过900 个字节
  PAD_INDEX：指定填充索引的内部节点的行数。至少应大于等于两行PAD_INDEX 选项只有在
  FILLFACTOR 选项指定后才起作用因为PAD_INDEX 使用与FILLFACTOR 相同的百分比缺省时SQL Server
   确保每个索引页至少有能容纳一条最大索引行数据的空闲空间。如果FILLFACTOR 指定的百分比不够
   容纳一行数据SQL Server 会自动内部更改百分比
  FILLFACTOR = fillfactor：FILLFACTOR 称为填充因子它指定创建索引时每个索引页的数据占索引页
  大小的百分比fillfactor 的值为1 到100 。它其实同时指出了索引页保留的自由空间占索引页大小的
  百分比即100 - fillfactor 对于那些频繁进行大量数据插入或删除的表在建索引时应该为将来生成的
  索引数据预留较大的空间即将fillfactor 设得较小否则索引页会因数据的插入而很快填满并产生分页
  而分页会大大增加系统的开销但如果设得
过小又会浪费大量的磁盘空间降低查询性能因此对于此类表通常设一个大约为10
的fillfactor 而对于数据不更改的高并发的只读的表fillfactor 可以设到95 以上乃
至100
如果没有指定此选项SQL Server 默认其值为0 0 是个特殊值与其它小FILLFACTOR
示例：为表products 创建一个簇索引
create unique clustered index pk_p_id on products(p_id) with
pad_index,
fillfactor = 10,
ignore_dup_key,
drop_existing,
statistics_norecompute
on [primary]

示例：为表products 创建一个复合索引
create index pk_p_main on products(p_id, p_name, sumvalue) --其中sumvalue 是一个计算列表达式为price*quantity
with
pad_index,
fillfactor = 50
on [primary]

示例：创建一个视图并为它建一个索引
create view dbo.work_years with schemabinding
as
select top 100 percent emp_id,e_name, birthday, hire_date,
year(getdate())- year(hire_date) as work_years
from dbo.employee order by work_years desc
create unique clustered index emp_id_view on dbo.work_years (emp_id)

生成一个存储过程用来生成记录的脚本
------在SQLSERVER 创建生成记录为脚本的存储过程sp_sys_getInsertScript 结束--------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sys_getInsertScript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_sys_getInsertScript]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE Proc dbo.sp_sys_getInsertScript
@TblName varchar(100),
@BackTblName varchar(100)=NULL,
@whereClause varchar(300)=NULL,
@Collist varchar(300)='*',
@isOracle char(1)='F',
@Debug char(1)='F' -- F - False T - True A - F+T P - print insert_sql result
--with encryption
As
Declare @sql varchar(8000), @sql1 varchar(2000), @sql_PK varchar(1000)
Declare @colName varchar(100), @datatype varchar(50), @NullData varchar(50)

Select @sql='',@sql1='',@sql_PK='',
@BackTblName = Case when isnull(ltrim(@BackTblName),'')='' then NULL Else @BackTblName End ,
@whereClause = case when isnull(ltrim(@whereClause),'')='' then NULL Else @whereClause End ,
@Collist = Case when isnull(ltrim(@collist),'')='' then '*' Else Replace(@collist,' ','') End
----------------------------------------------------------------------------------------------
-- 0 - Schedule Module
----------------------------------------------------------------------------------------------
L_0:
if @BackTblName is null goto L_2
----------------------------------------------------------------------------------------------
-- 1 - Build Join Criteria if @backTblName is Not NULL
----------------------------------------------------------------------------------------------
L_1:
Declare Tmp_Cur Cursor For
Select Colname,DataType
From (select convert(varchar(100),sc.name) As colname,
   Case when rtrim(st.name) in ('char','nchar','ntext','nvarchar','sysname','text','varchar') then 'CH'
when rtrim(st.name) in ('binary','bit','decimal','float','int','money','numeric','real','tinyint','smallint','smallmoney','varbinary') then 'NUM'
when rtrim(st.name) in ('datetime','smalldatetime') then 'DT'
else 'OTH'
   End As Datatype
FROM sysindexes si
join sysindexkeys sik On si.id=sik.id And si.indid=sik.indid
Join syscolumns sc on sik.id=sc.id and sik.colid=sc.colid
join systypes st on sc.xtype=st.xusertype
where si.id=object_id(@tblName)
And ( si.status &(2048)> 0 or si.status &(4096)=4096 And not exists (select * from sysindexes si2 where si2.id=si.id and si2.status &(2048)> 0))
) As T
Open Tmp_Cur
Fetch Next From Tmp_Cur Into @Colname,@dataType
While @@Fetch_Status = 0
BEGIN
     select @sql_PK = @sql_PK + ' AND t1.'+@colname+'=t2.'+@colName
     Fetch Next From Tmp_Cur Into @Colname,@dataType
END
Close Tmp_Cur
Deallocate Tmp_Cur
if @sql_PK <> ''
select @sql_PK =stuff(@sql_PK,1,4,'')
--print '1-
--'+@sql_PK
----------------------------------------------------------------------------------------------
-- 2 - Build Table column list
----------------------------------------------------------------------------------------------
L_2:
Declare Tmp_Cur Cursor For
select colname,datatype,Case datatype when 'CH' then '''NULL''' when 'DT' then '''1900-1-1''' when 'NUM' then '0' Else '''''' End As NullData
From
(select convert(varchar(100),sc.name) As colname,
   Case when rtrim(st.name) in ('char','nchar','ntext','nvarchar','sysname','text','varchar') then 'CH'
when rtrim(st.name) in ('binary','bit','decimal','float','int','money','numeric','real','tinyint','smallint','smallmoney','varbinary') then 'NUM'
when rtrim(st.name) in ('datetime','smalldatetime') then 'DT'
else 'OTH'
   End As Datatype,
   sc.isnullable
--   Case sc.isnullable when 0 then 'Not NULL' Else '    NULL' end As NullAble,
from syscolumns sc join systypes st on sc.xtype=st.xusertype
where sc.id=object_id(@tblName)
And (CHARINDEX('*',isnull(@collist,'*'),1)>0 OR CHARINDEX(','+sc.name+',',','+@collist+',',1)>0)
And CHARINDEX('/'+sc.name+',',isnull(@collist+',',''),1)=0
) As T
Open Tmp_Cur
Fetch next From Tmp_Cur Into @colName,@datatype,@NullData
While @@fetch_Status = 0
Begin
     select @sql = @sql + '+'',''+isnull('
   +Case @dataType when 'NUM' then 'convert(varchar(30),t1.'+@colName+')'
     When 'DT' then CASE @isOracle WHEN 'T' THEN '''TO_DATE(' + replicate('''',3)+'+convert(varchar(20),t1.'+@colName+',20)+'+ replicate('''',3) + ',' + replicate('''',2) + 'yyyy-mm-dd HH24:mi:ss' + replicate('''',2) + ')'''
                                                                       ELSE replicate('''',4)+'+convert(varchar(20),t1.'+@colName+',20)+'+ replicate('''',4)
                                                        END
     Else replicate('''',4)+'+Replace(rtrim(convert(varchar(4000),t1.'+@colName+')),'+replicate('''',4)+','+replicate('''',6)+')+'+ replicate('''',4)
    End+','+'''NULL'''+')
',@sql1=@sql1+','+@colName
     Fetch next From Tmp_Cur Into @colName,@datatype,@NullData
End
close Tmp_Cur
Deallocate Tmp_Cur
----------------------------------------------------------------------------------------------
-- 3 - Build script and execute
----------------------------------------------------------------------------------------------
L_3:
if @sql<>''
   Select @sql='Select ''INSERT INTO '+@TblName+'('+stuff(@Sql1,1,1,'')+') Values( ''+ syntax+'');'' As Insert_syntax
From ( Select Replace('+stuff(@sql,1,5,'')+','''''''','''''''') As syntax
From '+@TblName+' t1
where ' +isnull('('+@WhereClause+') And ','')
+Case when @BackTblName is not null then 'not exists (select * from '+@BackTblName+' t2 where '+@sql_PK+')'
       Else '1=1'
End + '
) As T'
if @Debug in ('T','A') Print @sql
if @Debug in ('F','A') exec (@sql)
if @Debug = 'P'
   BEGIN
exec ('Declare Tmp_Cur Cursor For ' + @sql)
open Tmp_cur
Fetch Next From Tmp_cur Into @sql
While @@Fetch_status = 0
   BEGIN
  print @sql
  Fetch Next From Tmp_cur Into @sql
   END
Close Tmp_Cur
Deallocate Tmp_cur
   END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------创建生成记录的的过程语句结束--------
最后，用以下的语句来得到结果

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。