锦州师范高等专科学校计算机系,数据库原理与应用 锦州师范高等专科学校 计算机系...

7.1 7.2 7.3 7.1.1 7.1.2 7.2.1 7.2.27.3.17.3.27.3.3

SQL ServerSQL Server

7.1 ---- SQL Server8K ---- ----

WHEREORDER BY textimagecharvarcharbinary900B

UPDATESELECT 1 2(1) (2) + PRIMARY KEYPRIMARY KEY

(3) 7.1.1 SQL Server 2000

PK_ PK__********,* Primary KeyNONCLUSTEREDCLUSTERED

7.1.1 T-SQL SQL Server SQL Server P139 SQL Server SQL Server

CREATE INDEX1 CREATE [UNIQUE][CLUSTERED|NONCLUSTERED] INDEX ON{|} 1[ASC|DESC][ n] [WITH[ n]] [ON ] {PAD_INDEX| FILLFACTOR=| IGNORE_DUP_KEY| DROP_EXISTING| STATISTICS_NORECOMPUTE| SORT_IN_TEMPDB}

11UNIQUE INSERTUPDATE2CLUSTERED

CREATE INDEXCLUSTEREDNONCLUSTERED 1.2120%

SCHEMABINDING ASC|DESC ASC column table 16900

3NONCLUSTERED

249 ORDER BY

4FILLFACTOR SQL Server sysindexes SQL Server =40 SQL Server40% 6.37 0 100 0sp_configure

199SQL Server1%99% fillfactor 10fillfactorfillfactor50100 SQL Server

5PAD_INDEX PAD_INDEXFILLFACTORPAD_INDEX FILLFACTOR FILLFACTOR SQL Server FILLFACTOR SQL Server

6IGNORE_DUP_KEY INSERT

7DROP_EXISTING DROP_EXISTINGDROP INDEX CREATE INDEX P141

8STATISTICS_NORECOMPUTE NORECOMPUTEUPDATE STATISTICS SQL Server9SORT_IN_TEMPDBtempdb tempdb

2 17-1XS_KCIX_ P141 CREATE INDEX IX_ ON XS_KC 2 7-2 7-1P142 CREATE INDEX IX_ ON XS_KC WITH DROP_EXISTING

37-37-2 CREATE UNIQUE CLUSTERED INDEX IX_ ON XS_KC WITH DROP_EXSTING

47-47-3 CREATE UNIQUE CLUSTERED INDEX IX_ ON XS_KC WITH PAD_INDEX FILLFACTOR=50 DROP_EXISTING

57-5XSQKXS_KCv_XSQK_KCXS_KCXS_KC

--XSQKXS_KCCREATE VIEW v_XSQK_KCWITH SCHEMABINDINGASSELECT XSQK.XSQK.XS_KC. XS_KC.FROM dbo.XSQKdbo.WHERE XS_KC.=XSQK. AND XS_KC.>=60--v_XSQK_KCCREATE UNIQUE CLUSTERED INDEX IVX_ON v_XSQK_KC()WITH PAD_INDEXFILLFACTOR=50

SQL Server ----PAD_INDEXSQL Server 1 aXS_KC 2KCIX_ P144P145

3 4SQL Server 5 1SELECT 1[ n] FROM 1[ n] WITHINDEX WHERE

2SELECT 1[ n] FROM 1[ n] WITHINDEX= WHERE

6 0 = 1 2-251 sysindexes7-6P146 SELECT FROM XS_KC WITHINDEXIX_ WHERE >=60

7-7P146 SELECT FROM XS_KC WITHINDEX=1 WHERE >=607sysindexes name indid idsysobjects

SQL ServerSQL Server

1 1 2 3 4

2 1Microsoft SQL ServerSQL 2SQL Server WindowsW 3-1 index_student S index_student.trc 4 5a

6 a K

7 SQL ServerSQL Server index_student.trcindex_student.trc 8 xsqk 9 10 11

7.1.2 DROP INDEX12 DROP INDEX {.|.} [ n] DROP INDEXPRIMARY KEYUNIQUE PRIMARY KEYUNIQUE

P147CREATE INDEXDROP_EXISTING>>DROP INDEX

CREATE INDEX DBCC DBREINDEX

36-8XS_KCIX_v_XSQK_KCIVX_P147

DROP INDEX XS_KC.IX_ v_XSQK_KC. IVX_

SQL Server

a XS_KC /

SQL Server

a XS_KC

7.1.2 Microsoft SearchSQL Server

7.2 PRIMARY KEYP62P75 FOREIGN KEY UNIQUE CHECK NOT NULL DEFAULT CREATE TABLEALTER TABLE CREATE DEFAULT

7.2.1 1P156P60 1 2 2 1 2

7.2.2 1 1CREATE DEFALUT AS SQL Server 0x $

27-14P156 CREATE DEFAULT DFO_char AS GO CREATE DEFAULT DFO_date AS 2003-1-1 10:00:00 GO CREATE DEFAULT DFO_binary AS 0xFFFF GO CREATE DEFAULT DFO_money AS $100 GO CREATE DEFAULT DFO_int AS 100 GO 3P157

2 1sp_bindefault sp_bindefault[@defname= ] [@objname= ]object_name [,[@futureonly= ]futureonly_flag] object_name.P157 futureonly_flagP157

7-157-14DFO_charXSQK(P158) sp_bindefault DFO_char,XSQK.2 sp_unbindefault 7-16 7-15DFO_char(P158) sp_unbindefault XSQK. 3P158

3 1 7-177-14 P159 DROP DEFAULT DFO_char,DFO_date,DFO_binary, DFO_money,DFO_int 2

7.3 SQL Server7.3.11

2 7-18- ALTER TABLE XSQK ADD CONSTRAINT CH_ CHECK LIKE _ _ _ _-_ _ _ _ _ _ _ % 051712345678 3 P161=1 OR =0

1 1 CREATE RULE AS P161

7-19-P161 CREATE RULE RO_ AS @ LIKE____-______% 2

2 1sp_bindrule sp_bindrule[@rulename=] [@objname=]object_name [,[@futureonly=]futureonly_flag] P162 7-207-19RO_XSQKP162 sp_bindrule RO_XSQK. sp_unbindrule sp_unbindrule[@objname=]object_name [,[@futureonly=]futureonly_flag] P162

7-217-20RO_ P162 sp_unbindrule XSQK.2

3 1DROP RULE [ n] 7-227-19RO_P164 DROP RULE RO_

2

7.3.2 1IDENTITY 27.3.3 P165 SQL Server 1 2 3