- Shopping Bag ( 0 items )
Candidates for the MCP exams can learn all they need to know about Microsoft SQLServer with the help of this training kit. This book is also targeted at SQL Server implementers, system engineers, and developers ...
Candidates for the MCP exams can learn all they need to know about Microsoft SQLServer with the help of this training kit. This book is also targeted at SQL Server implementers, system engineers, and developers who are responsible for planning and deploying Microsoft SQL Server and writing Transact-SQL code.
After this lesson you will be able to:
Estimated lesson time: 75 minutes
- Create clustered and nonclustered indexes with unique or composite characteristics.
- Use the CREATE INDEX options to expedite index creation and improve index performance.
- Apply the appropriate fillfactor value to accommodate the future growth of tables.
Using the CREATE INDEX Statement
Use the CREATE INDEX statement to create indexes. You also can use the Create Index wizard in SQL Server Enterprise Manager. When you create an index on one or more columns in a table, consider the following facts and guidelines:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,...n])
[[,]FILLFACTOR = fillfactor]
If you do not specify UNIQUE in the CREATE INDEX statement, the index will allow duplicates. If you do not specify CLUSTERED in the CREATE INDEX statement, a nonclustered index will be created.
This example creates a clustered index on the lastname column in the member table.
CREATE CLUSTERED INDEX cl_lastname
ON library..member (lastname)
Using the DROP INDEX Statement
Use the DROP INDEX statement to remove an index from a table. When you drop an index, consider the following facts:
DROP INDEX table.index [,...n]
This example drops the cl_lastname index from the member table.
DROP INDEX member.cl_lastname
To create an index on the loan table
In this exercise, you will open a script file that creates an index, review the contents of the script, execute the script, and then verify that the index was created.
A unique index ensures that all data in an indexed column does not contain duplicate values. If the table has a PRIMARY KEY or UNIQUE constraint, SQL Server automatically creates a unique index when you execute the CREATE TABLE or ALTER TABLE statements.
Create a unique index if you want an index that is independent of constraints. However, in most cases, create PRIMARY KEY or UNIQUE constraints rather than creating unique indexes. When you create a unique index, consider the following facts and guidelines:
Figure 6.12 shows the creation of a unique, nonclustered index named title_ident on the title table. The index is built on the title_no column. The value in the title_no column must be a unique value for each row of the table.
CREATE UNIQUE INDEX title_ident
ON title (title_no)
Finding All Duplicate Values in a Column
If duplicate key values exist when you create a unique index, the CREATE INDEX statement fails. SQL Server returns an error message with the first duplicate, but other duplicate values may exist as well. Use the following sample script on any table to find all duplicate values in a column. Replace the italicized text with information specific to your query.
SELECT index_col, COUNT(index_col) AS '# of Duplicates'
GROUP BY index_col HAVING COUNT(index-col) > 1
ORDER BY index-col
This example determines whether duplicate member numbers exist in the member_no column in the member table. If so, SQL Server returns the member number and number of duplicate entries for each duplicate member number in the table.
SELECT member_no, COUNT(member_no) AS '# of Duplicates'
GROUP BY member_no HAVING COUNT(member_no) > 1
ORDER BY member_no
Creating Composite Indexes
An index that is created on more than one column in a table is called a composite index. You can create composite indexes:
When you create a composite index, consider the following facts and guidelines:
Figure 6.13 shows the creation of a nonclustered, composite index on the loan table. The isbn and the copy_no columns are the composite key values. Notice that the isbn column is listed first because it is more selective than the copy_no column.
CREATE INDEX loan_ident
ON loan (isbn, copy_no)
Posted July 19, 2001
This is a great book for those who want to start enterprise databasing with sql server. If you want to learn about administration of sql you need the other MS press book which is about administration. After finishing this book you'll become a great theorist but you need to work a lot to put your knowledge in practice.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Posted February 25, 2000
This book is a very concise, and prepares you adequately for the exam. Though it misses on Query optimizer, physical design, and depth of DBCC SHOWCONIG, it is definitely better than the junk other books offer. I have bought three books, and was forced tor return the other two. Hey I passed the exam with flying colors, ofcourse Transcenders were backing me up. Also I wish MS adds a sample test to this kitWas this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.