How do I estimate database growth?

Unfortunately, SQL Server does not come with any built-in tools to estimate database size.
If you don’t have a third-party estimating tool, you have to do it the hard way, which is by calculating the space taken up by each table, and then adding the total space needed for each table to get a grand total for the database.

Here are some guidelines for estimating the space needed for a database.

1. For each table, find out how many bytes each row will use up on average. It is easy to calculate the size of fixed length columns, but calculating the space used by variable length fields is much more difficult. About the only way to do this is to get some of the actual data that will be stored in each variable length column, and then based on the data you have, estimate the average byte length of each variable length column. Once you know the typical sizes of each column, you can then calculate the size of each typical row in your table.
2. The above step is a good start, but it usually significantly underestimates the amount of space you need. Besides the actual data, you must also estimate the type and number of indexes you will use for each table. Indexes can use a huge amount of space, and you must estimate how much space you think they will take. This is a function of the type of index (clustered or non-clustered, the number of indexes, and the width of the indexes).
3. Besides estimating the size of the indexes, you also must take into consideration the Fillfactor and Pad Index used when the indexes are created. Both of these affect how much empty space is left in an index, and this empty space must be included in your estimate.
4. And one more factor affecting how much space it takes to store data in a table is how many rows can be fitted onto one SQL Server 8K data page. Depending on the size of each row, it is likely that not all of the space in each data page is fully used. This must also be accounted for when estimating page size.
5. While tables, and their associated indexes take up most of the physical space in most databases, keep in mind that every object in SQL Server takes up space, and must be accounted for.

As you can see, without a tool to help out, manually estimating database size is not a fun task, and it is, at best, only a rough estimate.

Another option you might consider, assuming that you already have an existing database with data, is to extrapolate the current size of your database on a table by table basis. For example, if you know that a particular table has 100,000 rows, and it is 1MB in size, then assuming that neither indexing or the fillfactor changes, than when the table gets to 200,000 rows, that it should be about 2MB in size. If you do this for every table, then you can get a fairly good idea on how much disk space you will need in the future. To find out how much space a particular table uses, use this command:

sp_spaceused ' '

What happens when my integer IDENTITY runs out of scope?

Before we actually look at the answer, let’s recall some basics of the IDENTITY property and SQL Server’s numerical data types.

You can define the IDENTITY property on columns of the INT data type and on DECIMAL with scale 0. This gives you a range of:

TINYINT	0 – 255
SMALLINT	-32.768 – 32.767
INT	-2.147.483.648 – 2.147.483.647
BIGINT	-2^63 – 2^63-1

When you decide to use the DECIMAL datatype you have a potential range from -10^38 to 10^38-1.

So, keeping this in mind, we’re now ready to answer the original question here. What happens when an INTEGER IDENTITY value is about to run out of scope?

CREATE TABLE id_overflow ( col1 INT IDENTITY(2147483647,1) )

GO

INSERT INTO id_overflow DEFAULT VALUES INSERT INTO id_overflow DEFAULT VALUES SELECT * FROM id_overflow

DROP TABLE id_overflow

(1 row(s) affected) Server: Msg 8115, Level 16, State 1, Line 2 Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.

This script creates a simple table with just one column of type INT. We have also created the IDENTITY property for this column. But instead of now adding more than 2 billion rows to the table, we rather set the seed value to the positive maximum value for an INTEGER. The first row inserted is assigned that value. Nothing unusual happens. The second insert, however, fails with the above error. Apparently SQL Server does not start all over again or tries to fill the maybe existing gaps in the sequence. Actually, SQL Server does nothing automatically here. You have to do this by yourself. But what can you do in such a case?

Probably the easiest solution is to alter the data type of the column to BIGINT, or maybe right on to DECIMAL(38,0) like so:

CREATE TABLE id_overflow ( col1 INT IDENTITY(2147483647,1) )

GO

INSERT INTO id_overflow DEFAULT VALUES ALTER TABLE id_overflow ALTER COLUMN col1 BIGINT INSERT INTO id_overflow DEFAULT VALUES

SELECT * FROM id_overflow

DROP TABLE id_overflow

col1

——————–

2147483647 2147483648

(2 row(s) affected)

If you know in advance that your table needs to keep that many rows, you can do:

CREATE TABLE bigint_t ( col1 BIGINT IDENTITY(-9223372036854775808, 1) )

GO

INSERT INTO bigint_t DEFAULT VALUES

SELECT * FROM bigint_t

DROP TABLE bigint_t

col1

——————–

-9223372036854775808

(1 row(s) affected)

Or the DECIMAL(38,0) variation:

CREATE TABLE decimal_t ( col1 DECIMAL(38,0) IDENTITY(-99999999999999999999999999999999999999, 1) )

GO

INSERT INTO decimal_t DEFAULT VALUES

SELECT * FROM decimal_t

DROP TABLE decimal_t

col1

—————————————-

-99999999999999999999999999999999999999

(1 row(s) affected)

One might be distressed in one’s aesthetical taste by those negative numbers, but it’s a fact, that one now shouldn’t have to worry about running out of scope for quite some time.

DELETE and TRUNCATE explained.

DELETE logs the data for each row affected by the statement in the transaction log and physically removes the row from the file, one row at a time. The recording of each affected row can cause your transaction log grow massively if you are deleting huge numbers of rows. However, when you run your databases in full recovery mode, detailed logging is necessary for SQL Server to be able to recover the database to the most recent state, should a problem arise. The fact that each row is logged explains why DELETE statements can be slow.

TRUNCATE is faster than DELETE due to the way TRUNCATE “removes” rows. Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast. It is a common mistake to think that TRUNCATE is not logged. This is wrong. The deallocation of the data pages is recorded in the log file. Therefore, BOL refers to TRUNCATE operations as “minimally logged” operations. You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state.

Some limitations do exist for using TRUNCATE.

· You need to be db_owner, ddl_admin, or owner of the table to be able to fire a TRUNCATE statement.

· TRUNCATE will not work on tables, which are referenced by one or more FOREIGN KEY constraints.

So if TRUNCATE is so much faster than DELETE, should one use DELETE at all? Well, TRUNCATE is an all-or-nothing approach. You can’t specify just to truncate those rows that match a certain criteria. It’s either all rows or none.

You can, however, use a workaround here. Suppose you want to delete more rows from a table than will remain. In this case you can export the rows that you want to keep to a temporary table, run the TRUNCATE statement, and finally reimport the remaining rows from the temporary table. If your table contains a column with the IDENTITY property defined on it, and you want to keep the original IDENTITY values, be sure to enabled IDENTITY_INSERT on the table before you reimport from the temporary table. Chances are good that this workaround is still faster than a DELETE operation.

You can also set the recovery mode to “Simple” before you start this workaround, and then back to “Full” one it is done. However, keep in mind that is this case, you might only be able to recover to the last full backup.

Understanding NULLS

Some SQL programmers and developers tend to think NULL as zero or blank. In fact, NULL is neither of these. NULL literally means that the value is unknown or indeterminate.

One side effect of the indeterminate nature of NULL value is it cannot be used in a calculation or a comparision.

Listed below are a few important rules to remember about the behaviour of NULL values.

• A NULL value cannot be inserted into a column defined as NOT NULL.
• NULL values are not equal to each other. It is a frequent mistake to compare two columns that contain NULL and expect the NULL values to match. (A NULL column can be identified in a WHERE clause or in a boolean expression using phrases such as ‘value is NULL’ and ‘value is NOT NULL’.)
• A column containing a NULL value is ignored in the calculation of aggregate values such as AVG, SUM or MAX.
• When columns that contain NULL values in a GROUP BY clause of a query are listed, the query output contains rows for those NULL values.
• JOINS between tables, in which one join condition contains values and the other contains NULL, are governed by the rules for “outer joins”.

Difference between UNIQUE constraint and PRIMARY key

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

How do you find the Second highest Salary?

This is the most common question asked in Interviews.

EMPLOYEE table has fields EMP_ID and SALARY how do you find the second highest salary?

Answer:

We can write a sub-query to achieve the result

SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)

The first sub-query in the WHERE clause will return the MAX SALARY in the table, the main query SELECT’s the MAX SALARY from the results which doesn’t have the highest SALARY.

How many rows this query will return

Tables EMP and MANAGER has 5 records each.

How many rows will the following query return?

SELECT * FROM EMP, MANAGER

Answer:

25 rows. Each rows in EMP will have five rows of MANAGER which makes 5×5 = 25.

What is a deadlock?

Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other’s piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated.
SQL Server detects deadlocks and terminates one user’s process.

A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.
SQL Server detects the situation after four denials and refuses further shared locks.
A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

What is the maximum size of a row?

This table specifies the maximum sizes and numbers of various objects defined in Microsoft SQL Server databases, or referenced in Transact-SQL statements. The table does not include Microsoft SQL Server 2000 Windows® CE Edition.

¹ Network Packet Size is the size of the tabular data scheme (TDS) packets used to communicate between applications and the relational database engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.
² The maximum number of bytes in any key cannot exceed 900 in SQL Server 2000. You can define a key using variable-length columns whose maximum sizes add up to more than 900, provided no row is ever inserted with more than 900 bytes of data in those columns. For more information, see Maximum Size of Index Keys.
³ The data portion of a database cannot exceed 2 GB in size when using the SQL Server 2000 Desktop Engine (MSDE 2000) or the Microsoft Data Engine (MSDE) 1.0. The total size of the database, including log files, can exceed 2 GB provided the sum of the sizes of the data files remains 2 GB or lower.
⁴ Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.

Indexes in SQL Server

Indexes are used by relational databases like SQL Server to find data quickly when a query is processed. One doesn’t need to modify the application’s code while manipulating indexes. Indexes operate “behind the scenes” in support of the database engine. However, creating the proper index can drastically increase the performance of an application.

The SQL Server engine uses an index in much the same way a reader uses a book index. For example, one way to find all references to INSERT statements in a SQL book would be to begin on page one and scan each page of the book. We could mark each time we find the word INSERT until we reach the end of the book. This approach is pretty time consuming and laborious. Alternately, we can also use the index in the back of the book to find a page number for each occurrence of the INSERT statements. This approach produces the same results as above, but with tremendous savings in time.

When a SQL Server has no index to use for searching, the result is similar to the reader who looks at every page in a book to find a word: the SQL engine needs to visit every row in a table. In database terminology we call this behavior a table scan, or just scan.

A table scan is not always a problem, and is sometimes unavoidable. However, as a table grows to thousands of rows and then millions of rows and beyond, scans become correspondingly slower and more expensive.

Create an Index
We can create the index using the following SQL. The command specifies the name of the index (IDX_UnitPrice), the table name (Products), and the column to index (UnitPrice).

Let’s create an Index on the Products table of the Northwind database.

CREATE INDEX [IDX_UnitPrice] ON Products (UnitPrice)

To verify that the index is created, use the following stored procedure to see a list of all indexes on the Products table:

EXEC sp_helpindex Customers

How It Works

The database takes the columns specified in a CREATE INDEX command and sorts the values into a special data structure known as a B-tree. A B-tree structure supports fast searches with a minimum amount of disk reads, allowing the database engine to quickly find the starting and stopping points for the query we are using.

Each index entry contains the index key (UnitPrice). Each entry also includes a reference (which points) to the table rows which share that particular value and from which we can retrieve the required information.

Much like the index in the back of a book helps us to find keywords quickly, so the database is able to quickly narrow the number of records it must examine to a minimum by using the sorted list of UnitPrice values stored in the index. We have avoided a table scan to fetch the query results. Given this sketch of how indexes work, lets examine some of the scenarios where indexes offer a benefit.

Taking Advantage of Indexes

The database engine can use indexes to boost performance in a number of different queries. Sometimes these performance improvements are dramatic. An important feature of SQL Server 2000 is a component known as the query optimizer. The query optimizer’s job is to find the fastest and least resource intensive means of executing incoming queries. An important part of this job is selecting the best index or indexes to perform the task. In the following sections we will examine the types of queries with the best chance of benefiting from an index.

Searching For Records

The most obvious use for an index is in finding a record or set of records matching a WHERE clause. Indexes can aid queries looking for values inside of a range (as we demonstrated earlier), as well as queries looking for a specific value. By way of example, the following queries can all benefit from an index on UnitPrice:

DELETE FROM Products WHERE UnitPrice = 1

UPDATE Products SET Discontinued = 1 WHERE UnitPrice > 15

SELECT * FROM PRODUCTS WHERE UnitPrice BETWEEN 14 AND 16

Indexes work just as well when searching for a record in DELETE and UPDATE commands as they do for SELECT statements.
Sorting Records

When we ask for a sorted dataset, the database will try to find an index and avoid sorting the results during execution of the query. We control sorting of a dataset by specifying a field, or fields, in an ORDER BY clause, with the sort order as ASC (ascending) or DESC (descending). For example, the following query returns all products sorted by price:

SELECT * FROM Products ORDER BY UnitPrice ASC

With no index, the database will scan the Products table and sort the rows to process the query. However, the index we created on UnitPrice (IDX_UnitPrice) earlier provides the database with a presorted list of prices. The database can simply scan the index from the first entry to the last entry and retrieve the rows in sorted order.

The same index works equally well with the following query, simply by scanning the index in reverse.

SELECT * FROM Products ORDER BY UnitPrice DESC

Grouping Records

We can use a GROUP BY clause to group records and aggregate values, for example, counting the number of orders placed by a customer. To process a query with a GROUP BY clause, the database will often sort the results on the columns included in the GROUP BY. The following query counts the number of products at each price by grouping together records with the same UnitPrice value.

SELECT Count(*), UnitPrice FROM Products GROUP BY UnitPrice

The database can use the IDX_UnitPrice index to retrieve the prices in order. Since matching prices appear in consecutive index entries, the database is able count the number of products at each price quickly. Indexing a field used in a GROUP BY clause can often speed up a query.

Maintaining a Unique Column

Columns requiring unique values (such as primary key columns) must have a unique index applied. There are several methods available to create a unique index. Marking a column as a primary key will automatically create a unique index on the column. We can also create a unique index by checking the Create UNIQUE checkbox in the dialog shown earlier. The screen shot of the dialog displayed the index used to enforce the primary key of the Products table. In this case, the Create UNIQUE checkbox is disabled, since an index to enforce a primary key must be a unique index. However, creating new indexes not used to enforce primary keys will allow us to select the Create UNIQUE checkbox. We can also create a unique index using SQL with the following command:

CREATE UNIQUE INDEX IDX_ProductName On Products (ProductName)

The above SQL command will not allow any duplicate values in the ProductName column, and an index is the best tool for the database to use to enforce this rule. Each time an application adds or modifies a row in the table, the database needs to search all existing records to ensure none of values in the new data duplicate existing values. Indexes, as we should know by now, will improve this search time.

Index Drawbacks

There are trade-offs to almost any feature in computer programming, and indexes are no exception. While indexes provide a substantial performance benefit to searches, there is also a downside to indexing. Let’s talk about some of those drawbacks now.

Indexes and Disk Space

Indexes are stored on the disk, and the amount of space required will depend on the size of the table, and the number and types of columns used in the index. Disk space is generally cheap enough to trade for application performance, particularly when a database serves a large number of users. To see the space required for a table, use the sp_spaceused system stored procedure in a query window.

EXEC sp_spaceused Orders

Given a table name (Orders), the procedure will return the amount of space used by the data and all indexes associated with the table, like so:

Name rows reserved data index_size unused
——- ——– ———– —— ———- ——-
Orders 830 504 KB 160 KB 320 KB 24 KB

According to the output above, the table data uses 160 kilobytes, while the table indexes use twice as much, or 320 kilobytes. The ratio of index size to table size can vary greatly, depending on the columns, data types, and number of indexes on a table.
Indexes and Data Modification

Another downside to using an index is the performance implication on data modification statements. Any time a query modifies the data in a table (INSERT, UPDATE, or DELETE), the database needs to update all of the indexes where data has changed. As we discussed earlier, indexing can help the database during data modification statements by allowing the database to quickly locate the records to modify, however, we now caveat the discussion with the understanding that providing too many indexes to update can actually hurt the performance of data modifications. This leads to a delicate balancing act when tuning the database for performance.

In decision support systems and data warehouses, where information is stored for reporting purposes, data remains relatively static and report generating queries outnumber data modification queries. In these types of environments, heavy indexing is commonplace in order to optimize the reports generated. In contrast, a database used for transaction processing will see many records added and updated. These types of databases will use fewer indexes to allow for higher throughput on inserts and updates.

Every application is unique, and finding the best indexes to use for a specific application usually requires some help from the optimization tools offered by many database vendors. SQL Server 2000 and Access include the Profiler and Index Tuning Wizard tools to help tweak performance.

Now we have enough information to understand why indexes are useful and where indexes are best applied. It is time now to look at the different options available when creating an index and then address some common rules of thumb to use when planning the indexes for your database.

Types of Indexes

Clustered Indexes

Earlier in the article we made an analogy between a database index and the index of a book. A book index stores words in order with a reference to the page numbers where the word is located. This type of index for a database is a non-clustered index; only the index key and a reference are stored. In contrast, a common analogy for a clustered index is a phone book. A phone book still sorts entries into alphabetical order. The difference is, once we find a name in a phone book, we have immediate access to the rest of the data for the name, such as the phone number and address.

For a clustered index, the database will sort the table’s records according to the column (or columns) specified by the index. A clustered index contains all of the data for a table in the index, sorted by the index key, just like a phone book is sorted by name and contains all of the information for the person inline. The non-clustered indexes created earlier in the chapter contain only the index key and a reference to find the data, which is more like a book index. You can only create one clustered index on each table.

A clustered index is the most important index you can apply to a table. If the database engine can use a clustered index during a query, the database does not need to follow references back to the rest of the data, as happens with a non-clustered index. The result is less work for the database, and consequently, better performance for a query using a clustered index.

To create a clustered index, simply select the Create As CLUSTERED checkbox in the dialog box we used at the beginning of the chapter. The SQL syntax for a clustered index simply adds a new keyword to the CREATE INDEX command, as shown below:

CREATE CLUSTERED INDEX IDX_SupplierID ON Products(SupplierID)

Most of the tables in the Northwind database already have a clustered index defined on a table. Since we can only have one clustered index per table, and the Products table already has a clustered index (PK_Products) on the primary key (ProductId), the above command should generate the following error:

Cannot create more than one clustered index on table ‘Products’.
Drop the existing clustered index ‘PK_Products’ before creating another.

As a general rule of thumb, every table should have a clustered index. If you create only one index for a table, use a clustered index. Not only is a clustered index more efficient than other indexes for retrieval operations, a clustered index also helps the database efficiently manage the space required to store the table. In SQL Server, creating a primary key constraint will automatically create a clustered index (if none exists) using the primary key column as the index key.

Sometimes it is better to use a unique non-clustered index on the primary key column, and place the clustered index on a column used by more queries. For example, if the majority of searches are for the price of a product instead of the primary key of a product, the clustered index could be more effective if used on the price field. A clustered index can also be a UNIQUE index.

A Disadvantage to Clustered Indexes

If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order. This behavior essentially turns an update query into a DELETE followed by an INSERT, with an obvious decrease in performance. A table’s clustered index can often be found on the primary key or a foreign key column, because key values generally do not change once a record is inserted into the database.

Composite Indexes

A composite index is an index on two or more columns. Both clustered and non-clustered indexes can be composite indexes. Composite indexes are especially useful in two different circumstances. First, you can use a composite index to cover a query. Secondly, you can use a composite index to help match the search criteria of specific queries. We will go onto more detail and give examples of these two areas in the following sections.

Covering Queries with an Index

Earlier in the article we discussed how an index, specifically a non-clustered index, contains only the key values and a reference to find the associated row of data. However, if the key value contains all of the information needed to process a query, the database never has to follow the reference and find the row; it can simply retrieve the information from the index and save processing time. This is always a benefit for clustered indexes.

As an example, consider the index we created on the Products table for UnitPrice. The database copied the values from the UnitPrice column and sorted them into an index. If we execute the following query, the database can retrieve all of the information for the query from the index itself.

SELECT UnitPrice FROM Products ORDER BY UnitPrice

We call these types of queries covered queries, because all of the columns requested in the output are contained in the index itself. A clustered index, if selected for use by the query optimizer, always covers a query, since it contains all of the data in a table.

For the following query, there are no covering indexes on the Products table.

SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice

This is because although the database will use the index on UnitPrice to avoid sorting records, it will need to follow the reference in each index entry to find the associated row and retrieve the product name. By creating a composite index on two columns (ProductName and UnitPrice), we can cover this query with the new index.

Matching Complex Search Criteria

For another way to use composite indexes, let’s take a look at the OrderDetails table of Northwind. There are two key values in the table (OrderID and ProductID); these are foreign keys, referencing the Orders and Products tables respectively. There is no column dedicated for use as a primary key; instead, the primary key is the combination of the columns OrderID and ProductID.

The primary key constraint on these columns will generate a composite index, which is unique of course. The command the database would use to create the index looks something like the following:

CREATE UNIQUE CLUSTERED INDEX PK_Order_Details ON [Order Details] (OrderID, ProductID)

The order in which columns appear in a CREATE INDEX statement is significant. The primary sort order for this index is OrderID. When the OrderID is the same for two or more records, the database will sort this subset of records on ProductID.

The order of columns determines how useful the index is for a query. Consider the phone book sorted by last name then first name. The phone book makes it easy to find all of the listings with a last name of Smith, or all of the listings with a last name of Jones and a first name of Lisa, but it is difficult to find all listings with a first name of Gary without scanning the book page by page.

Likewise, the composite index on Order Details is useful in the following two queries:

SELECT * FROM [Order Details] WHERE OrderID = 11077

SELECT * FROM [Order Details] WHERE OrderID = 11077 AND ProductID = 13

However, the following query cannot take advantage of the index we created since ProductID is the second part of the index key, just like the first name field in a phone book.

SELECT * FROM [Order Details] WHERE ProductID = 13

In this case, ProductID is a primary key, however, so an index does exist on the ProductID column for the database to use for this query.

Suppose the following query is the most popular query executed by our application, and we decided we needed to tune the database to support it.

SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice

We could create the following index to cover the query. Notice we have specified two columns for the index: UnitPrice and ProductName (making the index a composite index):

CREATE INDEX IX_UnitPrice_ProductName ON Products(UnitPrice, ProductName)

While covered queries can provide a performance benefit, remember there is a price to pay for each index we add to a table, and we can also never cover every query in a non-trivial application.

Additional Index Guidelines

Choosing the correct columns and types for an index is another important step in creating an effective index. In this section, we will talk about two main points, namely short index keys and selective indexes (we’ll explain what selective indexes are in just a moment).

Keep Index Keys Short

The larger an index key is, the harder a database has to work to use the index. For instance, an integer key is smaller in size then a character field for holding 100 characters. In particular, keep clustered indexes as short as possible.

There are several approaches to keeping an index key short. First, try to limit the index to as few columns as possible. While composite indexes are useful and can sometimes optimize a query, they are also larger and cause more disk reads for the database. Secondly, try to choose a compact data type for an index column, based on the number of bytes required for each data type. Integer keys are small and easy for the database to compare. In contrast, strings require a character-by-character comparison.

As a rule of thumb, try to avoid using character columns in an index, particularly primary key indexes. Integer columns will always have an advantage over character fields in ability to boost the performance of a query.

Distinct Index Keys

The most effective indexes are the indexes with a small percentage of duplicated values. Think of having a phone book for a city where 75% of the population has the last name of Smith. A phone book in this area might be easier to use if the entries were sorted by the resident’s first names instead. A good index will allow the database to disregard as many records as possible during a search.

An index with a high percentage of unique values is a selective index. Obviously, a unique index is the most selective index of all, because there are no duplicate values. SQL Server will track statistics for indexes and will know how selective each index is. The query optimizer utilizes these statistics when selecting the best index to use for a query.

Maintaining Indexes

In addition to creating an index, we’ll need to view existing indexes, and sometimes delete or rename them. This is part of the ongoing maintenance cycle of a database as the schema changes, or even naming conventions change.

View Existing Indexes

A list of all indexes on a table is available in the dialog box we used to create an index. Click on the Selected index drop down control and scroll through the available indexes.

There is also a stored procedure named sp_helpindex. This stored procedure gives all of the indexes for a table, along with all of the relevant attributes. The only input parameter to the procedure is the name of the table, as shown below.

EXEC sp_helpindex Customers

Rename an Index

We can also rename any user created object with the sp_rename stored procedure, including indexes. The sp_rename procedure takes, at a minimum, the current name of the object and the new name for the object. For indexes, the current name must include the name of the table, a dot separator, and the name of the index, as shown below:

EXEC sp_rename 'Products.IX_UnitPrice', 'IX_Price'

This will change the name of the IX_UnitPrice index to IX_Price.

Delete an Index

It is a good idea to remove an index from the database if the index is not providing any benefit. For instance, if we know the queries in an application are no longer searching for records on a particular column, we can remove the index. Unneeded indexes only take up storage space and diminish the performance of modifications. You can remove most indexes with the Delete button on the index dialog box, which we saw earlier. The equivalent SQL command is shown below.

DROP Index Products.IX_Price

Again, we need to use the name of the table and the name of the index, with a dot separator. Some indexes are not so easy to drop, namely any index supporting a unique or primary key constraint. For example, the following command tries to drop the PK_Products index of the Products table.

DROP INDEX Products.PK_Products

Since the database uses PK_Products to enforce a primary key constraint on the Products table, the above command should produce the following error.

An explicit DROP INDEX is not allowed on index ‘Products.PK_Products’.
It is being used for PRIMARY KEY constraint enforcement.

Removing a primary key constraint from a table is a redesign of the table, and requires careful thought. It makes sense to know the only way to achieve this task is to either drop the table and use a CREATE TABLE command to recreate the table without the index, or to use the ALTER TABLE command.