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.
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.
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
Let's do an example!
Create a new Visual Basic Windows Forms project and add two buttons on the form.
Add the appropriate SQL namespace to use:
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.
As you can see, creating indexes through code is not difficult at all. Until next time, good luck.