BN.com Gift Guide

Microsoft SQL Server 7.0 Database Implementation Training Kit: Exam #70-029

Overview

The target audience of this title is SQL Server administrators who install, configure, and support SQL server in an enterprise network. Business owners, contractors, and database administrators will also find all they need to know about Microsoft SQL Server.


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 ...

See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (12) from $1.99   
  • New (3) from $45.00   
  • Used (9) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$45.00
Seller since 2014

Feedback rating:

(193)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
$45.00
Seller since 2014

Feedback rating:

(193)

Condition: New
Brand new.

Ships from: acton, MA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
$72.00
Seller since 2014

Feedback rating:

(310)

Condition: New
Brand New Item.

Ships from: Chatham, NJ

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview

The target audience of this title is SQL Server administrators who install, configure, and support SQL server in an enterprise network. Business owners, contractors, and database administrators will also find all they need to know about Microsoft SQL Server.


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.

Read More Show Less

Product Details

  • ISBN-13: 9781572318267
  • Publisher: Microsoft Press
  • Publication date: 4/14/1999
  • Series: Training Kit Series
  • Edition description: BK&CD ROM
  • Edition number: 1
  • Pages: 800
  • Product dimensions: 7.57 (w) x 9.45 (h) x 2.55 (d)

Meet the Author


Founded in 1975, Microsoft is the worldwide leader in software for personal computers. The company offers a wide range of products and services for business and personal use, each designed with the mission of making it easier and more enjoyable for people to take advantage of the full power of personal computing every day.
Read More Show Less

Read an Excerpt


Chapter 6: Planning and Creating Indexes

...Lesson 3: Creating Indexes

Now that you are familiar with the different index architectures, this lesson will discuss creating indexes and obtaining information on existing indexes.
After this lesson you will be able to:
  • 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.
Estimated lesson time: 75 minutes

Creating and Dropping Indexes

You create indexes using the CREATE INDEX statement and remove them using the DROP INDEX statement.

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:

  • SQL Server automatically creates indexes when a PRIMARY KEY or UNIQUE constraint is created on a table. Defining a PRIMARY KEY or UNIQUE constraint is preferred over creating standard indexes.
  • You must be the table owner to execute the CREATE INDEX statement.
  • Indexes cannot be created on views.
  • SQL Server stores index information in the sysindexes system table.
  • Before you create an index on a column, determine whether indexes already exist on that column.
  • Keep your indexes small by limiting the key values to one or two columns. Typically, smaller indexes are more efficient than indexes with larger key values.
  • Select columns on the basis of uniqueness so that each key value identifies a small number of rows.
Syntax

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,...n])
[WITH
[PAD_INDEX]
[[,]FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]

[[,] DROP-EXISTING]

[[,] STATISTICS_NORECOMPUTE]
]
[ON filegroup]

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.

Example

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:

  • SQL Server reclaims disk space that is occupied by the index when you execute the DROP INDEX statement.
  • You cannot use the DROP INDEX statement on indexes that are created by PRIMARY KEY or UNIQUE constraints. You must drop the constraint in order to drop these indexes.
  • When you drop a table, all indexes for that table are also dropped.
  • When you drop a clustered index, all nonclustered indexes on the table are rebuilt automatically.
  • You must make the database in which an index resides the current database in order to drop that index.
  • The DROP INDEX statement cannot be used on system tables.
Syntax

DROP INDEX table.index [,...n]

Example

This example drops the cl_lastname index from the member table.

USE library
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.

  1. Open SQL Server Query Analyzer and verify that you are using the library database.
  2. Open the c:\sqlimpl\exercise\ch06\creaindl.sql script file.
  3. Review the CREATE INDEX statement. This script creates a nonclustered index named loan_member_link on the member_no column in the loan table with a fillfactor value of 75.
  4. Execute the script file.
  5. Verify that the loan_member_link index was created by executing the following statement:

    EXEC sp_helpindex loan

  6. The results of the sp_helpindex system stored procedure show that the loan_member_link nonclustered index exists on the member_no column of the loan table.
Creating Unique Indexes

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:

  • If a table contains data, SQL Server checks for duplicate values when you create the index. If the data contains duplicates, the CREATE INDEX statement fails.
  • SQL Server checks for duplicate values each time you use the INSERT or UPDATE statement. If duplicate key values exist, SQL Server rolls back the entire update and returns an error message with the first duplicate.
  • Create unique indexes only on columns in which entity integrity can be enforced. For example, you would not create a unique index on the lastname column of the member table because some members may have the same last name.
Example

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.

USE library
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'
FROM tablename
GROUP BY index_col HAVING COUNT(index-col) > 1
ORDER BY index-col

Example

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'
FROM library..member
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 two or more columns are best searched as a key.
  • If queries reference only the columns in the index. This is called a covered query. When a query is covered, the data rows do not have to be retrieved as the column values are read directly from the index keys.
For example, a telephone directory is a good example of where a composite index would be useful. The directory is organized by last names. Within the last names, it is organized by first names because entries with the same last name often exist. Although the entries are indexed by last name and first name, you cannot find an entry in the telephone directory by first name only.

When you create a composite index, consider the following facts and guidelines:

  • You can combine as many as 16 columns into a single composite index. The sum of the lengths of the columns that make up the composite index cannot exceed 900 bytes.
  • All columns in a composite index must be from the same table.
  • Define the most unique column first. The first column defined in the CREATE INDEX statement is referred to as the highest order. In the telephone directory example, the last name is the high order column and the first name is the low order column.
  • An index on (columnl, column2) is not the same as an index on (column2, column 1) -- each has a distinct column order. Generally, the column that contains more selective data or that would return the lowest percentage of rows should be the higher order column.
  • If the WHERE clause of a query references a lower order column of the composite index, it must also reference all higher order columns in the index for the query optimizer to use the composite index. Like using the first name in a telephone directory, it is only useful if you have first found the last name.
  • Composite indexes are useful for tables with multiple column keys. For example, in a sales table the key may be made up of a column that specifies a sales region and another column that specifies an invoice number.
  • Used correctly, composite indexes can increase query performance and reduce the number of indexes that you create on a table.
  • Multiple indexes on the same columns are typically not useful.
Example

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.

USE library
CREATE INDEX loan_ident
ON loan (isbn, copy_no)

...

Read More Show Less

Table of Contents


About This Book ..... vii
Chapter 1: Overview of SQL Server ..... 1
Chapter 2: Overview of Transact-SQL ..... 31
Chapter 3: Creating Databases ..... 73
Chapter 4: Creating Database Objects ..... 117
Chapter 5: Implementing Data Integrity ..... 135
Chapter 6: Planning and Creating Indexes ..... 157
Chapter 7: Maintaining Indexes ..... 191
Chapter 8: Querying Multiple Tables ..... 231
Chapter 9: Advanced Query Techniques ..... 259
Chapter 10: Summarizing Data ..... 291
Chapter 11: Implementing Views ..... 329
Chapter 12: Managing Transactions and Locks ..... 357
Chapter 13: Working with Distributed Data ..... 381
Chapter 14: Implementing Stored Procedures ..... 413
Chapter 15: Implementing Triggers ..... 443
Appendix A: Questions and Answers ..... 469
Appendix B: Library Database Case Study ..... 493
Appendix C: Database Schemas ..... 501
Appendix D: Performing Basic Queries ..... 505
Glossary ..... 547
Index ..... 595
Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 2 Customer Reviews
  • Anonymous

    Posted July 19, 2001

    If you want to learn everything about the implementation of SQL server 7.0 read this book

    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  No   Report this review
  • Anonymous

    Posted February 25, 2000

    Very good kit for 70-029

    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 kit

    Was this review helpful? Yes  No   Report this review
Sort by: Showing all of 2 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)