Using VB to Set Up a Database Index

Introduction

Database indexes are crucial to the speed and performance of your database queries or database views. Knowing how to set up proper indexes in your databases is a vital skill to have.

First, some background on SQL.

SQL

Structured Query Language is the programming language you use to build databases, extract information from databases, and store information into databases. In an earlier article, I spoke about the term database, so if you haven’t read it yet, have a read here. If this is your first time hearing the term SQL, have a look here.

To extract any information from any database, we need to write an SQL query. A query can be explained as a request for information which it then presents ultimately. Now, as I have mentioned, all database information gets stored inside tables. These tables contain all the information that has been stored. We must write a query to get this information and present it to the user.

Queries

The simplest form of a query would look like the following:

SELECT * FROM TableName

This will give you all the data stored in that particular table. Remember now, that some tables can have millions of records inside them, so, in this case, this simple query can give you all those millions of records. Usually, this is not really needed. Usually, there is some sort of condition involved when extracting information. A query with a condition will look like this:

SELECT * FROM TableName WHERE Field = Value

A query similar to the above now has the capability to return only certain information. Let me use a bank as an example. With any bank, they deal with millions of customers. Many of these customers may have the same last names, or even the exact same names. Any customer of the bank may have more than one type of account. Now, taking all of this into consideration, a query to return all the information about a specific person may be troublesome. If conditions did not exist, this would have caused you to stand in the banking queue for a very long time while the poor teller sifts through all the information just to find the correct person with the correct information.

We can take conditions further, and write a query like the following:

SELECT * FROM TableName
WHERE Field1 = Value1
AND Field2 > Value2
OR Field3 < Value3

It gets trickier now, because here we deal with more than one field’s value and more than one condition. The preceding query can be translated into layman’s terms to mean:

Select all the data from the table named TableName where Column1 is equal to Value1 AND Column2 is greater than Value2 OR Column3 is less than Value3. More information Query Conditions can be found here.

It can get more complicated….

SELECT * FROM Table1, Table2, Table3
WHERE Table1.Field1 = Value1
AND Table2.Field2 > Value2
OR Table3.Field3 < Value3

This SQL code extracts certain data from three different tables, based on the various conditions set.

In an earlier article, I explained how to create a database through Visual Basic. Please follow it here.

Here are a few database articles that might interest you:

• Exploring the Data Controls in Visual Basic.NET
• Doing Data Extraction with Visual Basic.NET
• Using Parameterized Queries and Reports in VB.NET Database Applications
• Working with Blobs and VB.NET
• Databases and Windows 8.1 and VB
• Connecting to a SQL Server Database with VB and JavaScript in Windows 8.1

Let’s do an example!

Create a new Visual Basic Windows Forms project and add two buttons on the form.

Code

Add the appropriate SQL namespace to use:

Imports System.Data.SqlClient

Add the following code behind the button labeled ‘Create Index‘:

   Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

      Dim strCreate As String

      Dim connCreate As SqlConnection = New SqlConnection("Server=HTG;" & _
                                                          "uid=sa;pwd=;")

      strCreate = "CREATE UNIQUE INDEX Students_Index ON StudentInfo (StudentID)"

      Dim commCreate As SqlCommand = New SqlCommand(strCreate, connCreate)

      Try
         connCreate.Open()
         commCreate.ExecuteNonQuery()
         MessageBox.Show("Database Index has been created successfully")
      Catch ex As Exception
         MessageBox.Show(ex.ToString())
      Finally
         If (connCreate.State = ConnectionState.Open) Then
            connCreate.Close()
         End If
      End Try

   End Sub

Quite simple. All I did was create a connection object and create a query string that will be executed as a command by the connection object.

Here is more information on the Create Index SQL statement.

Here is more information on the Alter Index SQL statement.

Conclusion

As you can see, creating indexes through code is not difficult at all. Until next time, good luck.