SQL Server tutorial part 4 - Index

-

Indexes are used in SQL server to quickly retrieve the information

Type of Indexes

In SQL Server indexes are one of the following two types:
  1. Clustered Index 
  2. Non-Clustered Index. 

  • Clustered Index

A clustered index is the index that will arrange the rows physically in the memory in sorted order. An advantage of a clustered index is that searching for a range of values will be fast.

  • Non-clustered Index 

A non-clustered index is an index that will not arrange the rows physically in the memory in sorted order.  An advantage of a non-clustered index is searching for the values that are in a range will be fast. You can create a maximum of 999 non-clustered indexes on a table

Sample Table

Create Table customer(
  CustomerId int not null Identity(1,1),
  CustomerName varchar(100),
  Primary Key(CustomerId)
)
 
Create Table CustomerDetails(
  DetailsId int not null identity(1,1),
  CustomerId int foreign key references Customer(CustomerId),
  sort int,
  refCode int
)
SampleTable.sql - Snippet hosted by Cacher
RAW

Create index

--non clustered and unique index
 
Create Unique Nonclustered Index U_refCode on CustomerDetails(
  refCode desc
)
CreateUniqueIndex.sql - Snippet hosted by Cacher
RAW
--non clustered and non unique index
 
Create Nonclustered Index NU_sort on CustomerDetails(
  sort desc,
  refCode asc
)
CreateIndex.sql - Snippet hosted by Cacher
RAW

Drop index

Drop Index U_refCode
DropIndex.sql - Snippet hosted by Cacher
RAW

Please donate and support at https://www.paypal.me/Rajcoomar



Comments

Post a Comment

Popular posts from this blog

Car Wash System vb.net

-
Image
This software consists of a database that save the registration number of every vehicle being wash along side with the date, type of wash made and price Screen Shot Source Code To view records in the database: Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\washRcd.accdb;Persist Security Info=False") Dim sql As String sql = " SELECT * FROM tblwash" conn.Open() Dim dt As New DataTable Dim cmd2 As New OleDb.OleDbDataAdapter(sql, conn) cmd2.Fill(dt) DataGridView1.DataSource = dt DataGridView1.Refresh() conn.Close() To insert new record in the database: Private Sub insert() Dim conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\washRcd.accdb;Persist Security Info=False") Dim updateSql As String = String.Format(...
Read more

Face recognition using EmguCV 3.0 and typing pattern recognition

-
Image
Introduction An MSc project with the title Student Examination System, where the objective is to put the students in an examination condition but instead of having an invigilator in an examination center, the system will cater for the proper ongoing of the exam. the system can be used as an online examination system The system is able to: Recognizing the face shape of a particular student Detect if there is more than one person in the examination room  Analyze the typing pattern of a student and detect if any one person is taking part in the exam voice recognition for the student and detect if there is more than one person speaking in the examination room Setup Download Emgu CV from  http://www.emgu.com/wiki/index.php/Main_Page Download Haarcascade from  https://github.com/opencv/opencv/tree/master/data/haarcascades Create an account at  https://www.keytrac.net/ Face recognition The snippet below illustrates how the Emgu CV is loaded whe...
Read more

Student Information System - AngularJS , ASP.NET API, C#

-
Image
Web based application the student information system is a small application that allows user to register and login to view information about a particular student and can perform several actions like Login and register to the application View students  Add new student Delete a particular student Update user information Screen Shot Project architecture routing.js, config.js and app.js allow the application to route from one partial view to another and config.js is used to save all the endpoint needed to access the API.   For separation of concerns, in the solution panel separate partial views, controller and services in different directories and reference it in index.html to enable angular to load all the files required Login process login.html LoginController.js Using $resource from AngularJS to make an API call and response  with a user details model UserViewModel and UserDetailsViewModel Using Unity fo...
Read more