February 28, 2011

SQL 2005 - Examples of Various Select Queries

1. Introduction

In this Article, we will see how we can query the data from the database using the "Select statement" in SQL. In this article, I will show some basics of it. Before you start working on this, download the Script file attached and run it on the SQL Server to create the database Northwnd. If you already have it, skip the download.

2. Row and column filtering

2.1 Selecting all the columns

Consider the below example:

Example 1:
Select * from Employees

This will select all the columns from the table Employees. Before you execute it, make sure the NorthWnd database is selected as shown in the below Picture.

In the above Query, * represents all the columns and Employees specifies the table name.

2.2 Filtering the Specific Column

Example 2:
Select EmployeeID, FirstName, LastName, City from Employees

The above query does not select all the columns from the table employees because we replaced the * with specific column names. So, the * in the previous query states all the columns. Here, in the above query we ask for four specific columns. Usually, people will not go for all the columns by using the * as it generates network traffic. Keep in mind that it is always good to specify the column you want in place going for all the columns. Execute above query and see the results.

2.3 Concatenating the Columns

Example 3:
Select EmployeeID,
TitleOfCourtesy + ' ' + FirstName + ', ' + LastName As [Full Name] 
from Employees;

In the above example, first, note that the query is wrapped into three lines. SQL Server understands where the query starts and where it ends. The second columns are the concatenated column formed by joining the columns TitleOfCourtesy, FirstName and LastName. To have a valid column name we used "As [Full Name]". The Full Name is given in the Square brackets because it has the Space between FullName. You no need to use Square brackets when the column name does not have space. Also, note that the concatenation is done by the operator plus by joining the table columns with a constant string like space and comma wrapped in single quotes. Execute the above query to see the results. Here, the column Full Name is called the "Column Alias".

Alternate Query for the Column Alias:

Example 4:

Select EmployeeID,
[Full Name] = TitleOfCourtesy + ' ' + FirstName + ', ' + LastName 
from Employees;

2.4 Usage of distinct in SQL2005

Select city from Employees

The above example will select City from the table employees. When you look at the output, the city names Seattle and London is repeated more than once. How do we see all the different city names in the column City? Below is the query for that:

Example 5:

Select distinct city from Employees

The "distinct" keyword in front of the column name tells the SQL server that does not repeat the column value once it is already displayed. The output of the above query will show only the different city names.

2.5 Where clause in SQL2005

So far we had seen filtering the column names from the table. How about filtering only the required row that we need? We should use the "where clause" for this purpose. Where clause will operate based on the Boolean expression formed on the column values. Consider below specified example:

Example 6: 
Select * from Employees where employeeid = 5;

In the above query, we are selecting all the columns from the table employees. But, we are selecting the row which has employeeid = 5. How does SQL look the above SQL statement:

  1. I need to Select all the columns because * is specified in place of column names. 
  2. For each row, I need to compare the value in the employeeid with constant 5. When the expression returns true, select that row.

So, the expression you specify in the where clause or where condition will be evaluated for each row. And, the row will be displayed only when the condition returns true.

Example 7:

Select * from Employees where employeeid > 5;

3. Operators in SQL Server

In the previous section we saw the basics of select statements, how do we filter the columns and how do we filter rows. We finished at the usage of the where clause usage. In this section, we will continue exploring the where clause with some useful operators.

3.1 Usage of Like operator

You can use the Boolean operator = to compare the string also. But, it will try to match the exact strings. How about matching the string with some patterns? The answer is; use the "Like operator". Consider the below simple example:

Example 8

Select * from employees where Title like 'Sales%';

In the above query, we are matching the title column with the string that follows a specific pattern Sales%. The Sales% denotes any string that starts with Sales. Execute the query to see the results. There symbols other than "%" that can be used with Like operator. Those operators are shown below:

Play with the above patterns with the like operator with your own queries. 

3.2 Between Operator

This operator is used to check whether the value falls in between an upper and lower range. Please consider the below example:

Example 9

Select * from employees where employeeid between 4 and 7

Here, we specified that we need all columns from the employee table and their Ids should be in the range of 4 to 7. Also not that you can use this "between operator" for String and Date ranges also. Execute the above query in the NortWnd database to see the results.

3.3 IS NULL operator

The "IS NULL" operator is useful to check the empty columns. Note, here empty stands for the column that never received any values through the SQL statement. If a column does not have any value at all then what do we compare to bring that column? That is where the IS NULL operator comes into the picture.

Example 10
Select * from employees where Region IS NULL

The above query will bring all the records which have no region specified.

3.4 And, OR, NOT in SQL2005

These are all "Boolean Operators" and used in the same way we programmers uses them in the conditional statements. You can combine two or three conditions together in the where clause using these operators.

Example 11: 
Select * from employees where title = 'Sales Representative' AND City = 'London'

In the above query "AND Operator" is used to get the combination of Two conditions. Above SQL will retrieve all the employees who has the job title as Sales Representative as well as he belongs to city London.

Example 12
Select * from employees where title = 'Sales Representative' OR City = 'London'

The Condition is joint through the "OR Operator" which states the employee should be a Sales Rep Or else he should be from the London city.

Example 13
Select * from employees where title = 'Sales Representative' AND NOT City = 'London'

The above query uses both And operator and "NOT operator". As per the Operator precedence, the Not operator is applied first to skip all the records which have City as London. So the above query will return all the Sales Rep employees who do not belong to the City London.

3.5 In Operator in SQL2005

The In operator allows you filter a particular column by comparing it with a given list of values. You can use "IN operator" with sub-Queries also. First, we will look at the Simple query that uses this operator. Then we look how it is used in the sub-queries. Look at the simple example below:

Example 14

Select * from employees where FirstName IN ('Janet', 'Margaret', 'Steven', 'Michael', 'Robert')

The above query will select all employees who have the first name provided in the comma separated list. Now look at the example below:

Example 15

Select * from employees where FirstName IN (
    Select FirstName from employees where TitleOfCourtesy = 'Ms.')

The above query will first execute the sub-query to get all the first name with title Ms. Then the main query performs the filter based on the Subquery returned, "First Name". Also, note that subquery need not be on the same table. It can be a query for a different table also. The list for the IN operator is formed using the Sub-Query in the above example.

Note: The above query examples are created using the NorthWnd database. If you don’t have the DB download the attached script and execute in your SQL server. 

NorthWnd Database : Download 

.Net Remoting - Singleton Server Activated

1. Introduction

In the previous two articles, we saw the server activated, single call remote objects. Also remember, each call to the server will create a new remote object in the "Single call" technique. In this article, we will see how the Singleton remote objects work. Then we will walk through an example.

The example here does not require a big explanation, as you are already familiar with the remoting by the previous article on the remoting topic.

2. Server activated, Singleton remote object

In this technique also, the object is created by the server and will be kept in the server’s remote object pool. Unlike the single call technique, here only one object is created and maintained in the memory pool. All client applications access the same remote objects. That is why we call this remote object as singleton remote object.

To better understand the usage of Singleton remote object, a simple application is created.

3. The application

As I already told, there is no much explanation is required on the coding side. You just need to say that you want to register the object as singleton remote object. That is all. Everything else is taken care by the server application. Below is the change that you should know:


Deploy the Server on one machine. Run the client application from the different machine to see how it works. 

Below is the Screenshot of a server and two clients: 

Fig 1. Example - Singleton Server Activated

In this example, all the clients access the remote object RegVotes. This object is registered as singleton object so the server maintains the single copy of the object. All the clients share that single object. You can select one the voting option and click the Vote now button. The server returns the average rating after client’s vote is registered.

4. Search Tags

Once you open the solution (Solution file you can find inside the SingletonServer folder) use the following tags to search the projects:


You will get the entire step, which is exactly equal to the previous applications. And differs only in the place when registering the objects using RegisterWellKnownServiceType.

5. Previous Articles

This article may be confusing for you if you are reading it without reading the previous parts. The links below get you to the previous two parts:

Look at the "remoting" label for other articles on remoting.

Note: The application created in VS2005 IDE. If you have the latest version say yes to the conversion dialog/Wizard.

Source Code : Download

C# - Debugging C-sharp Applications - Part 1

1. Introduction to Debugging

Debugging is the process of finding the logical errors in the program by examining the statements during the execution. The execution of a program pauses when the breakpoint is hit. Here, I will walk you through the debugging the sample application supplied with this article.

The screen shot of the application is given below:

The first textbox will accept a number below 36000. The Get Prime Sum button will calculate sum of all the prime numbers between 1 and number given in the input. The prime numbers are displayed on the multi-line enabled text box. And, the summation of the prime number is displayed in the label control below the multi-line text box.

I am not going to explain the code that runs behind this form. However, you can understand it. What I do is, explain you some debugging techniques that you can use in your program to get away from the logical troubles.

2. Inserting a Break point

As already told, breakpoint temporarily pauses the program to examine some piece of code. Let us first put a breakpoint on the event handler for the Get Prime Sum button. Follow the Steps:

1) Open the attached solution
2) Locate the source code for the Get Prime Sum Handler
3) In the margin left click as shown below:

4) Notice the color of the statement is changed and there is a red ball in the margin area. We call this as BreakPoint. When we run the program and when the execution reaches the breakpoint, the program pauses on the statement.

5) Run the application. Type 16 in the small text box; click the Get Prime Sum button. Notice how the program pauses the execution at break point. You will get a pointing arrow showing the current statement that is under examination. Below Picture shows this:

6) Press F5 and close the running application.

3. Toolbar buttons: Step into, Step Over, Step Out, Continue, and Stop Debugging

Stepping through the statements one by one allows us to examine the state of each variable and effect of the statement execution. We will see how these debugging commands work in this section.
First, have a look at the screenshot below:

Don’t have this toolbar?

Attempt 1:
Right click next to any of the toolbar buttons already displayed. From the displayed menu select debug:

Still, do not have the all the above-specified toolbar buttons? Follow the Attempt 2.

Attempt 2:
1) Click on the "tools|Customize…" menu option
2) From the displayed dialog under the "Command-Tab", select the debug item in the category listbox.
3) Drag&Drop the commands as shown in the red box below to the debugging toolbar or even to any popup menu item

4. Step Over Debug Option

"Step Over" tells go ahead and execute the next immediate statement. Each time you do step over the next statement will get executed. Now run the application again and type 16 in the input text box and I hope you don’t remove the breakpoint still. The breakpoint will hit at button’s click event handler. Now click on the "Step Over Button". The statement txtOutput.text = “” is executed now. Click the step over button until you reach the last statement as shown below:

Place the cursor over txtInput.Text exactly over the ".Text" portion of it. The displayed pop-up shows the current value in the text box txtInput. The below screen shows this:

Like this, you can examine each variable, before executing a statement and after executing a statement. As we asked for Step Over, the execution moves statement by statement. Press F5 and Close the running application.

5. Step Into and Step Out

I once again expect that the breakpoint not yet removed. If  you already removed it, place it back on the same statement and run the sample application with 12 in the input box. Once execution resumes at second statement inside the button click event handler, click the "Step Over Button". Now we are at the “if” conditional statement.

The return value of the function IsNumberValid is part of the condition. In the previous section when we do step over the execution gone to the next statement. It means the function is executed before moving to the next statement.  When the "Step Into" command is used the execution passed inside the function. Now click the Step Into button. The execution now reaches the first statement in the function now as shown below:

Now step over until you reach the conditional statement. OK. The step into will enters inside the function that is part of the current statement. The step out is a reverse of it. Say for example, in our case once you reached the conditional statement you decided it is not worth to explore further inside this function. So you can go for "Step Out" option, which will execute the remaining statements in the function and the execution pauses on the statement, which called this function. Now click the step out function to go back to the statement that called the IsNumberValid function.

6. Breakpoint window

With a breakpoint window, you can easily manage multiple break points. A sample screen shot of the breakpoint window is shown below:

1) When you un-check the item in the window, the breakpoint will be disabled. You can see a disabled breakpoint in the above screen shot’s code area. You can right click the breakpoint bubble in the code area margin and disable it using the context menu as an alternate method.
2) The breakpoints icon shown in the toolbar in the above picture is used to bring the breakpoint window. If you cannot able to see the toolbar icon bring it from the customize option as explained in section 3 of this article.

Play around this window yourself. It is a kind of easy go.

7. Conditional Breakpoint

Once started using the breakpoints for some period of time on your big projects, you will really come to a situation “Hey, I need to break the execution of my loop when the loop is running for 160th time” or “I need to break my execution when a particular variable’s value changed”.  Of course, it is not a big deal as we can go ahead and place some extra piece of code to have a break point. But, this requires a re-compilation. And, in some corporate project, this re-compilation and re-launch consume considerable amount of time. The "Conditional Breakpoints" help you do this without the need for re-compilation as it does not involve any source code change. Let us explore the conditional breakpoints with the downloaded application.

First let us put a conditional breakpoint, which evaluates a Boolean expression and breaks when the expression returns true. Follow the steps below to do that:
1) Locate the function SumIfPrime.
2) Once you located the function put a breakpoint on a statement shown in the below picture

3) Right click the breakpoint and select Condition… from the context menu
4) From the displayed dialog type the condition as shown in the below picture, make sure the "Is true Radio Button" is in checked state. I will talk about "Has Changed Radio Button" later. Click OK.

Now, the breakpoint will be hit, only when the expression is evaluated to true. The expression will be true when the value of the variable i is between 20 to 30. Also, note the placement of the break point that pauses the execution based on the condition supplied. When the execution reaches the breakpoint, the runtime engines evaluates the expression supplied and pauses the execution only when the expression returns true. In our case, the conditional breakpoint stops the execution for all the prime numbers that comes in between 20 and 30. Below is the screenshot that shows how the break point looks:

To test this conditional breakpoint, launch the application. Then, type 137 in the input box. Click the Get Prime Sum button. How many time the breakpoint hit happened? This is a simple example, but you can find more suitable and useful one when you are debugging big solutions for the corporate company you are working for.

The “has changed” radio option tells the runtime that when the variable typed or return value of the expression changes, hit the break point. Need an Example? OK.

1) Right the click the conditional breakpoint
2) In the condition text box type PrimeSum
3) Check the radio button has changed

Now test the application. Whenever the value in the PrimeSum changed, the breakpoint hit happens. Hope you like this first part of debugging. Rest in Next. I mean part 2 of this article. Bye.

Note: The attached download sample is in VS2005. If you have the latest version, say yes to the conversion UI

Source Code : Download

ASP.Net - Understanding and Using ImageMap Control

1. Introduction

ImageMapButton is a server-side control, which allows you to display an image take an action when the particular part of the image is clicked. Think about that you are working on a Car sales website for a reputed car manufacturing company. And, your client asks to display a nice picture of their car model on one page and when the user clicks the parts of it (the car), the details about the part should be displayed on the next page. ImageButtonControl will fulfil this need.

In this article, I will show you the usage of the ImageButtonControl with the use Map of India. You can try the car model stuff yourself, as I believe this walk-through article will help you do that.

Let me first tell you, what we are going to do?

In the First Page which is the default page, we will display the Map of India. When you click on the AndhraPradesh state, we will navigate to a separate page that talks about Andhra. Similarly, when we click on the TamilNadu state, we will navigate to a separate page that talks about TamilNadu. Each of these pages has a normal image that highlights the state you clicked. Also, each page has the Go Back link to navigate to the Main Map page.

OK. Let us start our example.

2. Start a website

  1. First, start a File System based website with Visual C# as the Language. [Actually, we are not going to write any code here. But you can extend your learning if you need]
  2. Right click your website in the solution explorer, select Add New Item.
  3. From the Displayed dialog select WebForm and Name the form Andhra.aspx
  4. Add one more Web Form and Name it TamilNadu.aspx
  5. Go to Windows explorer and Browse to the Web Site Folder and create a directory called Images.
  6. Open the Attached solution, copy the content (Images! What else? ) Of the Images folder to your newly created Images folder.

The copied Images are Shown Below:

The Solution Explorer looks like below now [Refresh the Explorer, if Images folder is not displayed]:

(Forget about web.config if it is not there)

3. Image Map Button on Default page

In the default page, we are going to place the ImageMap control. So, when the website is accessed the initial page called default.aspx will display this Imagemap control.  The outline of the India will be loaded into this Imagemap control. Let us see how we can do it.

1) First, open the Default.aspx page and go to the design view.
2) Drag and drop the ImageMap control on the Page.

3) Once it is done, set the Image URL Property. Click on the small button at the end of the property value. From the displayed dialog, select Image Wmap.Jpg as shown in the below picture:

4) Click OK.

Now the ImageMap control displays the Outline of the India Map.

4. Set the Images for Other Pages

Now we will set the Images for other two pages. This time, we will use Normal Image controls. When we click the particular state; that is TamilNadu or Andhra in the ImageMap control on the Default.aspx page, we will move to the corresponding pages.

  1. Open the design view of the Andhra.aspx page.
  2. Drag and Drop the Image control on the design surface
  3. Set the Image URL property to the image WMapAP.JPG as you already did it for the ImageMap control
  4. Add a label control below the Image and set the following Text property: Andhra Pradesh. The language used here is Telugu
  5. Add a Hyperlink control below the Image. Set the Navigate property to Default.aspx. Now, when the link is clicked you will move to default.aspx page.

Open the TamilNadu.aspx page, Place an Image control and set the URL image to WMapTN.JPG. Add a label as set its text property to The State of TamilNadu. The language used is Tamil. Add a Hyperlink control to navigate back to the Default.aspx as you did for the Andhra.aspx

5. Set the HotSpots for ImageMap control

Now it is time to set the HotSpot for the ImageMap control. A HotSpot is a portion of the image, which will produce some action when it is clicked. There are three different ways to define the hotspot for an Image. The hotspot can be defined as Circle or Rectangle or Polygon. In our case even though the polygon is best suited, I am going to define rectangle hot spots for the State of Andhra and Polygon hotspot for the State of TamilNadu.

For the state of Andhra, two rectangles are specified as a hotspot. You can use the paintbrush to do this. Take a copy of the Image and Follow the instructions below:

  1. When you cursor is at the top left of the first rectangle, the X co-ordinate specified LEFT and Y co-ordinate specifies the TOP. Now drag your mouse to get the first rectangle as shown above. When you are at the Bottom right hand side corner, X co-ordinate specifies RIGHT and Y co-ordinate specifies the BOTTOM. Note Down these top, left, bottom, right co-ordinate values from the paintbrush. Do the same for the second rectangle.
  2. Now let us go to the Tamil Nadu state. Place your cursor as shown above and note down each pair of X&Y co-ordinates. These points defines our polygon which more suitable for irregular shapes.

Now you have the set of Points. The first two sets define the Rectangle and the other big pairs of X&Y define the polygon. Let us set the information collected on the paintbrush to the HotSpot property of the ImageMap control.

1) Open the Default.aspx page
2) Click the ImageMap control
3) Set the HotSpot Mode property to PostBack. This will help you to handle the events in the server immediately when a hot spot is clicked. But, in the example, we are not writing any code. You can experiment that.
4) Now click on the button […] next to the HotSpot property.
5) From the Displayed dialog, click on Add down arrow, and select RectangleHotSpot. Do it one more time, as we need to Rectangle hot spot for the State of Andhra.

6) Set the Top, Left, Bottom, Right property for both the Rectangle hot spots. Remember, you already noted down those points for both the rectangle using the paintbrush.
7) Set alternate text property for both the rectangle hotspot to Andhra Pradesh. This text is displayed as a tool tip when the mouse is on the hot spot.
8) Set the Hotspot mode for both the rectangle hot spots as Navigate. And, set Navigate URL property (Again for both rectangle hot spots) to the page Andhra.aspx. Use the dialog, which easily sets the property in the relative path notation.
9) Now add the Polygon Hotspot the same way you added the rectangle hot spots. One Polygon spot is enough.
10) Set Hotspot mode to Navigate, Set Navigate URL to TamilNadu.aspx and set the Alternate text to TamilNadu.
11) Set the Coordinates property for the point you noted down  for the polygon using the paintbrush. Put all the points as X1,Y1,X2,Y2….Xn,Yn

6. Run And Test

1) Press F5 and Run the site
2) Hover the mouse inside the state of Andhra. Once an Icon changed to Hand, stop moving the mouse and You can see the alternate text.
3) Left click when the hand icon is displayed
4) You are moved to Andhra.aspx page
5) Click the Go Back link to move to the Default page
6) Now click inside the TamilNadu, and you will be moved to Tamilnadu.aspx page.

7. How do I use the Attached Sample?

1) Download the attached zip file
2) Extract it to a folder
3) Open visual studio 2005
4) Click the menu item File|Open|Web site
5) Select the Extracted Folder

Note: The Attached solution is created in visual studio 2005

Source Code : Download

February 6, 2011

SQL 2005 - Clustered and NonClustered index in sql server

1. Introduction

We all know that data entered in the tables persisted in the physical drive in the form of database files. Think about a table say Customer (For any leading bank India) that has around 16 Million records. When we try to retrieve records for two or three customers based on his customer id, all 16 million records or taken and a comparison is made to get a match on the supplied customer ids. Think about how much time will that take, if it is a web application and there are 25 to 30 customers wants to access their data through the internet. Does the database server 16 Million x 30 searches? The answer is no because all modern database uses the concept of "Index".

2. What is a Database Index?

An index is a database object, which can be created on one or more columns (16 Max column combination). While creating the index, SQL Server will read the column(s) and forms a relevant data structure to minimise the number of data comparisons on the column(s) in which it is created. The index will improve the performance of data retrieval and adds some overhead on data modification such create, delete and Modify.  So it depends on how much data retrieval can be performed on the table versus how much of DML (Insert, Delete and Update) Operations.

In this article, we will see creating the Index. The below two sections are taken from my previous article as it is required here. If your database already has changed for next two sections, you can directly go to the section 5.

3. First, Create two tables

To explain these constraints we need two tables. First, let us create these tables. Run the below scripts to create the tables. Copy paste the code on the new Query Editor window then execute it.

CREATE TABLE Student(StudId smallint, StudName varchar(50), Class tinyint);
CREATE TABLE TotalMarks(StudentId smallint, TotalMarks smallint);

Note that there are no constraints at present on these tables. We will add the constraints one by one.

4. Primary Key Constraint

A table column with this constraint is called as the "key column" for the table. This constraint helps the table to make sure that the column value is not repeated and also no null entries. We will mark the StudId column of the Student table as a primary key. To do so, follow these steps:

  1. Right click the student table and click on the modify button
  2. From the displayed layout select the StudId row by clicking the Small Square like button on the left side of the row.
  3. Click on the "Set Primary Key" toolbar button to set the StudId column as a primary key column.

Primary Key Index Creation
Fig 1. Creating Primary key from UI

Now this column does not allow null values and duplicate values. You can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. But, multiple columns can participate on the primary key. In such case, the uniqueness is considered among all the participant columns by combining their values. The primary key with combined columns is known as "Composite Primary Key".

5. Clustered Index

The primary key created for the StudId column will create a clustered index for the "Studid" column. A table can have only one clustered index on it.

When creating the clustered index, SQL server 2005 reads the "Studid" column and forms a Binary tree like structure on it. This binary tree information is then stored separately in the disc. From Management studio, expand the table Student and then expand the Indexes. You will see the following index created for you when the primary key is created:

Fig 2. Clustered Index

With the use of the binary tree, now the search for the student based on the "studid" decreases the number of comparisons to a large amount. Let us assume that you had entered the following data in the table student:

Table with Clustered Index
Fig 3. Table with Clustered Index

The index will form the below specified binary tree (Or a tree similar to this). Note that for a given parent, there is only one or two Child. The left side will always have a lesser value and right side will always have a greater value when compared to the parent. The tree can be constructed in a reverse way also. That is, left side higher and right side lower.

Tree Structure of Clustered Index
Fig 4. Tree Structure of Clustered-Index

Now let us assume that we had written a query like below:

Select * from student where studid = 103;
Select * from student where studid = 107;

  • Execution without index will return value for the first query after the third comparison.
  • Execution without index will return value for the second query at eighths comparison.

Execution of the first query with index will return a value at the first comparison.
Execution of the Second query with index will return the value at the third comparison. 

Look below:
1) Compare 107 vs 103 : Move to right node
2) Compare 107 vs 106 : Move to right node
3) Compare 107 vs 107 : Matched, return the record

If numbers of records are less, you cannot see a different. Now apply this technique with a Yahoo email user accounts stored in a table called say YahooLogin. Let us assume there 33 millions of users around the world have yahoo email id and that is stored in the YahooLogin. When a user logs in by giving the username and password, the comparison required is minimum 1 to maximum 25, with the binary tree that is clustered index. Look at the above picture and guess yourself how fast you will reach the level 25. Without Clustered index, the comparison required is 1 to 33 million(s).

Got the usage of Clustered index? Let us move to a Non-Clustered index.

6. Non-Clustered Index

A "non-clustered Index" is useful for columns that have some repeated values. Say fox example AccountType column of a bank database may have 10 million rows. But, the distinct values of account type may be 10-15. A clustered index is automatically created when we create the primary key for the table. We need to take care of the creation of the non-clustered index.

Follow the steps below to create a Non-clustered index on our table Student based on the column class.

1) After expanding the Student table, right click on the Indexes. And click on the New Index.

Index DB Objects
Fig 5. Indexes as Database Objects

2) From the displayed dialog type the index name as shown below and then click on the Add button to select the column(s) that participate in the index. Make sure the Index type is Non-Clustered.

Non-Clustered Index creation Dialog
Fig 6. Creating Non-Clustered Index

3) In the "select column dialog", place a check mark for the column class. This tells that we need a non-clustered index for the column Student.Class. You can also combine more than one column to create the Index. Once the column is selected click on the OK button. You will return the dialog shown above with the selected column marked in blue. Our index has only one column. If you selected more than one column, using the MoveUp and MoveDown button you can change the order of the indexed columns. When you are using the combination of columns, Always use the highly repeated data column first and comparatively unique data columns down in the list. For example, let use assume the correct order for creating the Non-clustered index is:  Class, DateOfBirth, PlaceOfBirth

Pick column for the index
Fig 7. Selecting table column for Non-Clustered Index

4) Click on the Index folder on the right side and you will notice the non-clustered index created for you and the index created for the data column class.

Summary of the indexes
Fig 8. Index summary dialog

7. How does a Non-Clustered Index work?

A table can have more than one Non-Clustered index. But, it should have only one clustered index that works based on the Binary tree concept. Non-Clustered column always depends on the Clustered column in the database.

This can easily be explained with the concept a book and their index page at the end. Let us assume that you are going to a bookshop and found a big 1500 pages of C# book that says "all about C#".  When you glanced the book, it has all beautiful colour pages and shiny papers. But, that is not only the eligibility for a good book right? Once you impressed with the book, you want to see your favourite topic Regular Expression and how it is explained in the book. What will you do? I just peeped at you from the behind and recorded what you did as below:

  1. You went to the Index page (It has total 25 pages). It is already sorted and hence you easily picked up Regular Expression that comes on the page Number 17.
  2. Next, you noted down the number displayed next to it which is 407, 816, 1200-1220
  3. Your first target is Page 407. You opened a page in the middle, the page is greater than 500.
  4. Then you moved to a somewhat lower page. But it still reads 310.
  5. Then you moved to a higher page. You are very lucky you exactly got page 407. [Yes man you got it. Otherwise, I need to write more. OK?]
  6. That’s all, you started exploring what is written about Regular expression on that page, keeping in mind that you need to find page 816 and the Range 1200-1220 also.

In the above Scenario, the Index page is Non-Clustered index and the page numbers are clustered index arranged in a binary tree. See how you came to the page 407 very quickly. Your mind actually traversed the binary tree way left and right to reach the page 407 quickly.

Here, the class column with distinct values 1,2,3..12 will store the clustered index columns value along with it. Say for example; Let us take the only class value of 1. The Index goes like this:

1: 100, 104, 105

So here, you can easily get all the records that have value for class = 1. Map this with the Book index example now. See you all in the next articles.

Like this site? Tell it to your Friend :)