SQL server中 表中如何创建索引？

如何创建索引 :

使用T-SQL语句创建索引的语法：

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] 
    INDEX   index_name
     ON table_name (column_name…)
      [WITH FILLFACTOR=x]
       UNIQUE表示唯一索引，可选
       CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选
       FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比

在stuMarks表的writtenExam列创建索引：

USE stuDB
GO

IF EXISTS (SELECT name FROM sysindexes
          WHERE name = 'IX_writtenExam')
   DROP INDEX stuMarks.IX_writtenExam 
/*--笔试列创建非聚集索引：填充因子为30％--*/
CREATE NONCLUSTERED INDEX IX_writtenExam
     ON stuMarks(writtenExam)
          WITH FILLFACTOR= 30
GO
/*-----指定按索引 IX_writtenExam 查询----*/
SELECT * FROM stuMarks  (INDEX=IX_writtenExam)
    WHERE writtenExam BETWEEN 60 AND 90

虽然我们可以指定SQL Server按哪个索引进行数据查询，但一般不需要我们人工指定。SQL Server将会根据我们创建的索引，自动优化查询。

if exists(select *from sys.objects where naem = 'newindex')
drop index newindex
create index
--===================================
竟然没有悬赏...唉...
那算了吧
我还是都告诉你吧..

看个示例
自己琢磨去：
--==============================================
use master
go
if db_id(N'zhangxu')is not null
drop database zhangxu
go
create database zhangxu
sp_helpdb zhangxu
use zhangxu
go
IF EXISTS (SELECT *FROM SYS.OBJECTS WHERE NAME = N'WORKER')
DROP TABLE WORKER
GO
create table worker
(
w_id int identity (1000,1) not null,
w_name Nvarchar(10) unique,
w_age SMALLINT CONSTRAINT CK_W_AGE CHECK(w_age>20 and w_age<150),
w_pay money DEFAULT 0,
CONSTRAINT PK_W_ID PRIMARY KEY(W_ID)
)
SELECT *FROM WORKER--用查询技术查看表信息
sp_help worker--利用存储过程查看表信息
/*
创建简单的非聚集索引
*/
USE ZHANGXU
GO
if exists(select name from sys.indexes where name = N'IX_ID_NAME')
DROP INDEX IX_ID_NAME on worker
go--检查是否存在索引，有则删除索引
create index IX_ID_NAME--创建索引
on worker(w_id,w_name)--在ID NAME 两个字段上创建非聚集索引
drop index worker.IX_ID_NAME--删除索引
select *from sys.indexes where name = 'IX_ID_NAME'--查看索引
/*
创建唯一非聚集索引
*/
USE ZHANGXU
GO
IF EXISTS(SELECT NAME FROM SYS.INDEXES WHERE NAME = N'IX_W_NAME')
DROP INDEX IX_W_NAME ON WORKER
GO
CREATE UNIQUE INDEX IX_W_NAME--唯一非聚集索引
ON WORKER(W_NAME)
/*
查看索引T-SQL脚本
*/
--IX_W_NAME 唯一 非聚集索引
USE [zhangxu]
GO
/****** 对象: Index [IX_W_NAME] 脚本日期: 07/29/2007 16:54:53 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_W_NAME] ON [dbo].[worker]
(
[w_name] ASC
)
WITH
(
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF
)
ON [PRIMARY]

--PK_W_ID聚集索引
USE [zhangxu]
GO
/****** 对象: Index [PK_W_ID] 脚本日期: 07/29/2007 16:56:45 ******/
ALTER TABLE [dbo].[worker]
ADD CONSTRAINT [PK_W_ID] PRIMARY KEY CLUSTERED
(
[w_id] ASC
)
WITH
(
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF
) ON [PRIMARY]

--UQ_WORKER 唯一，非聚集索引
USE [zhangxu]
GO
/****** 对象: Index [UQ__worker__07020F21] 脚本日期: 07/29/2007 16:58:38 ******/
ALTER TABLE [dbo].[worker]
ADD UNIQUE NONCLUSTERED
(
[w_name] ASC
)
WITH
(SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
ONLINE = OFF
) ON [PRIMARY]
select *from worker
insert into worker(w_name,w_age,w_pay) values('王国龙',25,4500)

if
exists(select
*from
sys.objects
where
naem
=
'newindex')
drop
index
newindex
create
index
--===================================
竟然没有悬赏...唉...
那算了吧
我还是都告诉你吧..
看个示例
自己琢磨去:
--==============================================
use
master
go
if
db_id(N'zhangxu')is
not
null
drop
database
zhangxu
go
create
database
zhangxu
sp_helpdb
zhangxu
use
zhangxu
go
IF
EXISTS
(SELECT
*FROM
SYS.OBJECTS
WHERE
NAME
=
N'WORKER')
DROP
TABLE
WORKER
GO
create
table
worker
(
w_id
int
identity
(1000,1)
not
null,
w_name
Nvarchar(10)
unique,
w_age
SMALLINT
CONSTRAINT
CK_W_AGE
CHECK(w_age>20
and
w_age<150),
w_pay
money
DEFAULT
0,
CONSTRAINT
PK_W_ID
PRIMARY
KEY(W_ID)
)
SELECT
*FROM
WORKER--用查询技术查看表信息
sp_help
worker--利用存储过程查看表信息
/*
创建简单的非聚集索引
*/
USE
ZHANGXU
GO
if
exists(select
name
from
sys.indexes
where
name
=
N'IX_ID_NAME')
DROP
INDEX
IX_ID_NAME
on
worker
go--检查是否存在索引，有则删除索引
create
index
IX_ID_NAME--创建索引
on
worker(w_id,w_name)--在ID
NAME
两个字段上创建非聚集索引
drop
index
worker.IX_ID_NAME--删除索引
select
*from
sys.indexes
where
name
=
'IX_ID_NAME'--查看索引
/*
创建唯一非聚集索引
*/
USE
ZHANGXU
GO
IF
EXISTS(SELECT
NAME
FROM
SYS.INDEXES
WHERE
NAME
=
N'IX_W_NAME')
DROP
INDEX
IX_W_NAME
ON
WORKER
GO
CREATE
UNIQUE
INDEX
IX_W_NAME--唯一非聚集索引
ON
WORKER(W_NAME)
/*
查看索引T-SQL脚本
*/
--IX_W_NAME
唯一
非聚集索引
USE
[zhangxu]
GO
/******
对象:
Index
[IX_W_NAME]
脚本日期:
07/29/2007
16:54:53
******/
CREATE
UNIQUE
NONCLUSTERED
INDEX
[IX_W_NAME]
ON
[dbo].[worker]
(
[w_name]
ASC
)
WITH
(
SORT_IN_TEMPDB
=
OFF,
DROP_EXISTING
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ONLINE
=
OFF
)
ON
[PRIMARY]
--PK_W_ID聚集索引
USE
[zhangxu]
GO
/******
对象:
Index
[PK_W_ID]
脚本日期:
07/29/2007
16:56:45
******/
ALTER
TABLE
[dbo].[worker]
ADD
CONSTRAINT
[PK_W_ID]
PRIMARY
KEY
CLUSTERED
(
[w_id]
ASC
)
WITH
(
SORT_IN_TEMPDB
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ONLINE
=
OFF
)
ON
[PRIMARY]
--UQ_WORKER
唯一，非聚集索引
USE
[zhangxu]
GO
/******
对象:
Index
[UQ__worker__07020F21]
脚本日期:
07/29/2007
16:58:38
******/
ALTER
TABLE
[dbo].[worker]
ADD
UNIQUE
NONCLUSTERED
(
[w_name]
ASC
)
WITH
(SORT_IN_TEMPDB
=
OFF,
IGNORE_DUP_KEY
=
OFF,
ONLINE
=
OFF
)
ON
[PRIMARY]
select
*from
worker
insert
into
worker(w_name,w_age,w_pay)
values('王国龙',25,4500)

如何创建索引 :
使用T-SQL语句创建索引的语法：

1234567

CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name (column_name…) [WITH FILLFACTOR=x] UNIQUE表示唯一索引，可选 CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选 FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比

在stuMarks表的writtenExam列创建索引：

12

USE stuDBGO

1234567891011

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_writtenExam') DROP INDEX stuMarks.IX_writtenExam /*--笔试列创建非聚集索引：填充因子为30％--*/CREATE NONCLUSTERED INDEX IX_writtenExam ON stuMarks(writtenExam) WITH FILLFACTOR= 30GO/*-----指定按索引 IX_writtenExam 查询----*/SELECT * FROM stuMarks (INDEX=IX_writtenExam) WHERE writtenExam BETWEEN 60 AND 90

虽然我们可以指定SQL Server按哪个索引进行数据查询，但一般不需要我们人工指定。SQL Server将会根据我们创建的索引，自动优化查询。