SQL Server review notes 05

-- 11. Index -- create index create unique clustered index book1_in1 on book1 (No.)/* error: Multiple clustered indexes cannot be created for the table 'book1. Delete the existing clustered index 'pri _ number' before creating a clustered index '. Because, in a table, only one focused index can be created, and 249 non-clustered indexes can be created */create unique nonclustered index book1_in1 on book1 (number)-insert data now to see, does unique really use insert into book1 values ('20160301', '20160301', 'css ', 35, 'Sun Yat-sen University', '03-13-2008 ') -- full table structure insert/* message 2627, Level 14, status 1, and row 1st violate the primary key constraint 'pri _ number '. Duplicate keys cannot be inserted in the object 'dbo. book1. The duplicate key value is (1017 ). The statement has been terminated. */-- Delete the index drop index book1.book1 _ in1create unique nonclustered index book1_in2 on book1 (number) -- view the table index information exec sp_helpindex book1 -- rename the index exec sp_rename 'book1. bookdomainin2', 'book _ in3' -- Index Analysis and maintenance -- 1. showplan_all: analyze which indexes are used by the system. note: The preceding go cannot be omitted! Goset showplan_all ongoselect * From book1where No. = '000000' goset showplan_all off -- 2. statistics Io Displays disk Io information, And the goset statistics Io ongoselect * From book1where id = '20160301' goset statistics Io off -- (1 line is affected) -- table 'book1 '. Scan count 0, logical read 2, physical read 0, pre-read 0, lob logical read 0, lob physical read 0, lob pre-read 0. -- Maintenance -- 1. update statistics book1 book1_in3 -- 2. scan the table to view the fragmentation information. DBCC showcontig (book1, book1_in3)/* DBCC showcontig is scanning the 'book1' table... table: 'book1' (133575514); index ID: 6; Database ID: 17 scanned at leaf level. -Number of scanned pages ................................: 1-Number of scan zones ..............................: 1-Number of Area switches ..............................: 0-average page number of each partition ........................: 1.0-scan density [optimal count: actual count] ......: 100.00% []-logical scan fragmentation ..................: 0.00%-scan fragments ..................: 0.00%-average number of available bytes per page .....................: 7799.0-average page density (full ).....................: 3.64% DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. */