SQL Server Peformance Optimization

You have been asked to review the following Transact-SQL script: CREATE TABLE [dbo].[authors] (
[au_id] [int] NOT NULL ,
[au_lname] [varchar] (40) NOT NULL ,
[au_fname] [varchar] (20) NOT NULL ,
[phone] [char] (12) NOT NULL ,
[address] [varchar] (40) NULL ,
[city] [varchar] (20) NULL ,
[state] [char] (2) NULL ,
[zip] [char] (5) NULL ,
[contract] [bit] NOT NULL 
)
GO

ALTER TABLE [dbo].[authors] WITH NOCHECK ADD 
CONSTRAINT [UPKCL_auidind] PRIMARY KEY CLUSTERED 
(
[au_id]
)
GO

CREATE INDEX [aunmind] ON [dbo].[authors]([au_lname], [au_fname])
CREATE INDEX [au_id_authors] ON [dbo].[authors]([au_id])
GO

What, if any, recommendations do you have for this script in order to optimize the performance of this table?

You have been asked to evaluate the following SELECT statement in order to optimize its performance: SELECT first_name, last_name, address1, address2, city, state, zip
FROM mailinglist
WHERE state = 'mo'

As part of your review, you also take a look at the indexes on the mailinglist table. They include:

last_name_index: [last_name]
zip_index: [zip]
city_state_index: [city, state]

You also note that the are currently over 86,000 rows in this table.

Based on this information, is there anything that can be done to optimize the performance of this query?

You have been asked to recommend which columns in a table should have indexes. To help you make this decision, you have been given this information. First, here is the table structure:

CREATE TABLE [dbo].[members] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) NOT NULL ,
[au_fname] [varchar] (20) NOT NULL ,
[phone] [char] (12) NOT NULL ,
[address] [varchar] (40) NOT NULL ,
[city] [varchar] (20) NOT NULL ,
[state] [char] (2) NOT NULL ,
[zip] [char] (5) NOT NULL ,
[marital_status] [char] (1) NOT NULL 
)

Second, here are the three main queries that will be run against this table:

SELECT * FROM members WHERE [state] = '<any state abbreviation here>'

SELECT * FROM members WHERE [zip] = '<any zip code here>'

SELECT * FROM members WHERE [marital_status] = '<s, m, d>'

Third, this table will include about 98,000 rows after it is put into production. Of these, about 1/3 of the members will be single, 1/3 married, and 1/3 divorced. In addition, members will be distributed fairly equally in every state.

Based only on the information provided, on which columns would you suggest that indexes be added?

You have been asked to review the following table structure, looking for potential ways to optimize the database. Here's the table structure:
CREATE TABLE [dbo].[authors] (
[au_id] [nchar] (10) NOT NULL ,
[au_lname] [nchar] (25) NOT NULL ,
[au_fname] [nchar] (15) NOT NULL ,
[phone] [nchar] (10) NOT NULL ,
[address] [nchar] (40) NOT NULL ,
[city] [nchar] (20) NOT NULL ,
[state] [nchar] (2) NOT NULL ,
[zip] [nchar] (9) NOT NULL 
)

From talking to a developer, you discover that this database will be used to potential prospects from the United States, the United Kingdom, New Zealand, and Australia.

What, if anything, can be done to this table in order to optimize its performance when put into production?

You have been asked to recommend which column, if any, should have a clustered index on it. Here is the table's format:
CREATE TABLE [dbo].[students] (
[id] [int] NOT NULL ,
[lname] [varchar] (40) NOT NULL ,
[fname] [varchar] (20) NOT NULL ,
[phone] [char] (12) NOT NULL ,
[address] [varchar] (40) NOT NULL ,
[city] [varchar] (20) NOT NULL ,
[state] [char] (2) NOT NULL ,
[zip] [char] (5) NOT NULL ,
[age] [tinyint] NOT NULL 
)

Here are the most common queries run against this table:

SELECT * FROM students WHERE [lname] = '<any last name>' AND [fname] = '<any first name>'
SELECT * FROM students WHERE [city] = '<any city name>'
SELECT * FROM students WHERE [age] >= '<some age>" and [age] <= '<some age>"

You can assume that all of the columns are more than 90% selective.

Based on this information only, which column should have a clustered index?

You have been asked to review the following table query, looking for ways to optimize performance. Here is the table's structure:
CREATE TABLE [dbo].[Suppliers] (
[SupplierID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [varchar] (40) NOT NULL ,
[ContactName] [varchar] (30) NOT NULL ,
[ContactTitle] [varchar] (30) NOT NULL ,
[Address] [varchar] (60) NULL ,
[City] [varchar] (15) NULL ,
[Region] [varchar] (15) NULL ,
[PostalCode] [varchar] (10) NULL ,
[Country] [varchar] (15) NULL ,
[Phone] [varchar] (24) NULL ,
[Fax] [varchar] (24) NULL ,
[HomePage] [text] NULL ,
[Comments] [text] NULL 
)

Here's the query:

SELECT IDENTITYCOL, SupplierID, CompanyName, ContactName, 
ContactTitle, Address, City, Region, PostalCode, Country, 
Phone, Fax, HomePage, Comments
FROM Suppliers
ORDER BY postalcode

After some research, you find out that the query is used to print mailing labels, using this format:

ContactName
CompanyName
Address
City, Region, PostalCode

You also learn that all the mailing labels are printed each time, never a subset.

What, if anything, can you do to this query to optimize it?

You have been asked to see if there is anyway to boost the performance of the following query:
SELECT * FROM salesterritory1 WHERE salesamount > 100
UNION
SELECT * FROM salesterritory2 WHERE salesamount > 100
UNION
SELECT * FROM salesterritory3 WHERE salesamount > 100

After some research, you find all the tables above that have a primary key. You also find out that the sales records for each territory are mutually exclusive.

Given this information, what, if anything, can you do to optimize the performance of this query?

You have been asked to review the following table and query. Here is the table's structure: CREATE TABLE [dbo].[sales] (
[stor_id] [char] (4) NOT NULL ,
[ord_num] [varchar] (20) NOT NULL ,
[ord_date] [datetime] NOT NULL ,
[qty] [smallint] NULL ,
[payterms] [varchar] (12) NULL ,
[part_id] [tid] NULL 
)

And here is the query:

SELECT part_id FROM sales WHERE qty is null

The sales table contains 43,988,099 rows, and the [qty] column already has a non-clustered index.

This query is run daily and takes over 15 minutes to run. You have been asked if there is anything that can be done to reduce the amount of time it takes to run this very important query?

You have been asked to review the following two queries, which perform the same exact task, but have been written slightly differently. They are: QUERY 1
SELECT customer_number, customer_name
FROM customer
WHERE customer_number in (1000, 1001, 1002, 1003, 1004)

or

QUERY 2
SELECT customer_number, customer_name
FROM customer
WHERE customer_number BETWEEN 1000 and 1004

As you reviewed the queries, along with the customer table, you find that the [customer_number] column has a clustered index on it. You also find out that the customer_number table has 873,998 rows, and is growing at a rate of about 85,000 per month.

Given the available information, which, if any, of these two queries will provide the best performance?

You have been asked to help optimize the performance of the following trigger. CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
--Get the range of level for this job type from the jobs table.
declare @min_lvl tinyint,
@max_lvl tinyint,
@emp_lvl tinyint,
@job_id smallint
select @min_lvl = min_lvl,
@max_lvl = max_lvl,
@emp_lvl = i.job_lvl,
@job_id = i.job_id
from employee e, jobs j, inserted i
where e.emp_id = i.emp_id AND i.job_id = j.job_id
IF (@job_id = 1) and (@emp_lvl <> 10)
begin
raiserror ('Job id 1 expects the default level of 10.',16,1)
ROLLBACK TRANSACTION
end
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
begin
raiserror ('The level for job_id:%d should be between %d and %d.',
16, 1, @job_id, @min_lvl, @max_lvl)
ROLLBACK TRANSACTION
end

The table this trigger is based was created using this script:

CREATE TABLE [dbo].[employee] (
[emp_id] [int] NOT NULL ,
[fname] [varchar] (20) NOT NULL ,
[minit] [char] (1) NULL ,
[lname] [varchar] (30) NOT NULL ,
[job_id] [smallint] NOT NULL ,
[job_lvl] [tinyint] NOT NULL ,
[pub_id] [char] (4) NOT NULL ,
[hire_date] [datetime] NOT NULL 
)
GO

In the trigger itself, there is a reference to the jobs table, here is the script that was used to create it.

CREATE TABLE [dbo].[jobs] (
[job_id] [smallint] IDENTITY (1, 1) NOT NULL ,
[job_desc] [varchar] (50) NOT NULL ,
[min_lvl] [tinyint] NOT NULL ,
[max_lvl] [tinyint] NOT NULL 
) ON [PRIMARY]
GO

What, if anything, can be done to boost the performance of this trigger?