Creating and Managing SQL Server Indexes

SQL Server Indexes

One of the main routes to top performance in a SQL Server Database is the index. Indexes make the querying process quicker by giving speedy access to rows in data tables, in the same way that an index in a book quickly helps you find information within the book.

Database indexes are comprised of a look-up value, i.e:  a number identifier that corresponds to the row number in a table. There are 2 kinds of indexes in SQL Server - clustered and non-clustered. In clustered indexes it is required that the data in the table is sorted physically in the order of the index. Since the data in the table can only be physically sorted one way, there can only be one clustered index at most per table. With non-clustered indexes, it is not necessary for the data to be physically sorted, which means that there can be more than one non-clustered index per table. In fact the SQL Server allows for up to 249 non-clustered indexes per table. Due to the fact that the data is not physically sorted, the range searches with a non-clustered index do not tend to be very efficient.

You can use a specific command to create an index, or they can be created and managed using the Enterprise manager. You can create them using the Create Index Wizard from the Database Diagram or by modifying certain fields in the Table Designer.

There is a trade-off with indexes: whilst they immensely speed up the execution of queries, there is also associated overhead. They take up additional disk space and need more time to update themselves whenever data is updated or appended to a table.

When you are loading a large amount of data, it may be of benefit to drop the index before loading, then start the index afresh after the new records have been appended to the table. Indexes can be dropped using either the DROP INDEX command or the Table Designed.

Indexes can also become fragmented. To defragment an index, either drop then recreate the index or issue the command DBCC INDEXDEFRAG.

Picture courtesy of www.simple-talk.com

Recommended Posts | Network Management

Home Wireless Networks

How to Secure & Setup Home Wireless Networks

Do you use a wireless router for business? Is your router secure and password protected? Can anyone connect to your wireless router? Business needs security measures set up for wireless network. Many neighborhoods are scanned, by someone using a receiver, which picks up unsecured wireless signals ...
What is SDN?

What is SDN? Some Benefits Of Software Defined Networking

Currently we're facing serious challenges in IT, meeting current requirements is virtually impossible now with today's traditional network architectures and technologies. Faced with tight budgets, fast-evolving computing environment enterprise IT departments are seeking new way to address the issues ...
Network Management with SNMP

The Basics of Network Management with SNMP

SNMP is a popular protocol for the management of networks, used for gathering and configuring information from network devices, such as hubs, printers, servers, switches, and routers on an IP network, or any device which is network-capable. SNMP agents, managed devices and network management systems ...
Network Security Program Being Held Back

Why is Your Network Security Program Being Held Back?

Cyber security is the top concern of organizations and personal computer users. Professionals who work with cyber security often hear they have one of the most intellectually demanding jobs in the market today. Many think that cyber security challenges are never solved and that problems ...