use northwind

go
 
--删除主键
if exists (select [name] from sysobjects where [name] = 'pk_order_details')
alter table [order details] drop CONSTRAINT pk_order_details
 
--删除索引
if exists (select [name] from sysindexes where [name] = 'orderid')
drop index [order details].orderid
 
if exists (select [name] from sysindexes where [name] = 'ordersorder_details')
drop index [order details].ordersorder_details
 
if exists (select [name] from sysindexes where [name] = 'productid')
drop index [order details].productid
 
if exists (select [name] from sysindexes where [name] = 'productsorder_details')
drop index [order details].productsorder_details
 
 
--创建聚集索引
create clustered index orderid on [order details](orderid)
 
--创建非聚集索引
create index order_details on [order details](orderid)
 
create index productid on [order details](productid)
 
create index productsorder_details on [order details](productid)
 
--创建唯一索引
create unique index productid on [products](productid)
 
--创建复合索引
create index fullname on [employees](lastname, firstname)
 
--索引重建及填充
dbcc dbreindex('northwind.dbo.customers', '', 65)
 
/********************************************************************************/
 
/*
 *启用全文检索
 *enable, disable
 */
exec sp_fulltext_database 'enable'
 
/*
 *创建全文索引
 *sp_fulltext_catalog '全文索引名'， '动作' [, '全文索引保存路径']
 *全文索引保存路径只在create时有效
 *动作:create, drop, start_full, start_incremental, stop, rebuild
 */
exec sp_fulltext_catalog 'nw', 'create'
 
/*
 *为全文索引启用表
 *sp_fulltext_table '表名', '动作', '全文索引名', '主键名'
 */
exec sp_fulltext_table 'customers', 'create', 'nw', 'PK_Customers'
 
/*
 *将列加入全文索引
 *sp_fulltext_column '表名', '列名', '动作', 数据库语言
 *数据库语言:0x0409 美式英语, 0x0804 间体中文, 0x0404 繁体中文, 0 中性
 */
exec sp_fulltext_column 'customers', 'companyname', 'add', 0x0409
 
/*
 *为全文索引启动完全或增量填充
 */
exec sp_fulltext_table 'customers', 'start_full'
 
exec sp_fulltext_table 'customers', 'start_incremental'
 
--使用全文索引
select * from customers
where contains(companyname, 'food')
 
--使用模糊查询
select * from customers 
where companyname like '%food%'
 
 
/*
 *视图
 */
use northwind
go
 
--创建视图
create view test_view 
as
select lastname,firstname, reportsto 
from employees
 
--修改视图
alter view test_view
with encryption --加密
as
select lastname, firstname, reportsto
from employees
 
--修改视图
alter view test_view
as
select * from products
where unitprice > 20
with check option --输入检查
 
--删除视图
drop view test_view
 
--查看视图定义
select [text] from syscomments where [id] = (
select [id] from sysobjects where [name] = 'test_view')
 
exec sp_helptext 'test_view'