An increasing number of software out there namely websites and web applications today offer or need to offer real-time data, they want to have up-to-date data delivered to any device and to any application over any network and any connection. Whether an application is based on a browser and a website or a web application, or whether that's just a WPF or Silverlight, or a Windows store application, it doesn't matter a lot. An increasing number of collaborative apps and collaboration solutions running in the web browser but also running on mobile devices as well. So to cater these needs a robust framework is needed.
In this article we are going to dive deep into clustering index and non-clustered index. But before that we need to have an informal introduction of indexing in database and it’s usage for non-database guys.
So the question is What is index in database? Why do we need indexing in database? Let us learn with some scenario along with an analogy. The analogy is the index of a book.
In previous article we had discussed internals of clustered indexing. Here we are going to discussed non clustered indexing in details and we will use the same example table as shown below.
CREATE TABLE STUDENT
StudentID int IDENTITY(1,1) NOT NULL,
FirstName char(50) NOT NULL,
LastName char(50) NOT NULL,
SSN char(11) NOT NULL
Above table may be a clustered table or may not be a clustered table. It’s not mandatory for a table to be clustered one to create a non-clustered index on it. We want to create a non-clustered index “IX_Student_SSN” on SSN column of Student table as shown below