Clustered and Non clustered Index - SQL Server

Database indexes are used to improve the speed of data retrievel from database table with a large number of records. Database indexes (both clustered indexes and non-clustered indexes) are quite similar to book indexes in their functionality. A book index allows you to go straight to the different topics discussed in the book. If you want to search for a specific topic, you just go to index, find the page number that contains the topic that you are looking for and then can go straight to that page. Without an index, you would have to search the whole book.

Database indexes work in the same way. Without indexes you would have to search the whole table in order to perform a specific database operation. With indexes, you do not have to scan through all the table records. The index points you directly to the record that you are searching for, significantly reducing your query execution time.

SQL Server indexes can be divided into two main types:  

1. Clustered Indexes
2. Non-Clustered Indexes

Let’s first start with a clustered index.

Clustered Index

A clustered index physically sorts the data in database table. there can be only one clustered index per table. By default a clustered index is created on a primary key column.

Let’s create a dummy table with primary key column to see the default clustered index. 

Execute the following script:

CREATE DATABASE Hospital

CREATE TABLE Patients

(

id INT PRIMARY KEY,

name VARCHAR(50) NOT NULL,

gender VARCHAR(50) NOT NULL,

age INT NOT NULL

)

The above script creates a dummy database Hospital. The database has 4 columns: id, name, gender, age. The id column is the primary key column. When the above script is executed, a clustered index is automatically created on the id column. To see all the indexes in a table, you can use the “sp_helpindex” stored procedure.

EXECUTE sp_helpindex Patients

You will see in output window, the index name, description and the column on which the index is created. 

If you add a new record to the Patients table, it will be stored in ascending order of the value in the id column. If the first record you insert in the table has an id of three, the record will be stored in the third row instead of the first row since clustered index maintains physical order.

Custom Clustered Indexes

You can create your own clustered indexes. However, before you can do that you have to create the existing clustered index. We have one clustered index due to primary key column. If we remove the primary key constraint, the default cluster will be removed. The following script removes the primary key constraint.

ALTER TABLE Patients

DROP CONSTRAINT PK__Patients__3213E83F3DFAFAAD

GO

The following script creates a custom index “IX_tblPatient_Age” on the age column of the Patients table. Owing to this index, all the records in the Patients table will be stored in ascending order of the age.

CREATE CLUSTERED INDEX IX_tblPatient_Age

ON Patients(age ASC)

Let’s now add a few dummy records in the Patients table to see if they are actually inserted in the ascending order of age:

USE Hospital

INSERT INTO Patients

VALUES

(1, 'Sara', 'Female', 34),

(2, 'Jon', 'Male', 20),

(3, 'Mike', 'Male', 54),

(4, 'Ana', 'Female', 10),

(5, 'Nick', 'Female', 29)

In the above script, we add 5 dummy records. Notice the values for the age column. They have random values and are not in any logical order. However, since we have created a clustered index, the records will be actually inserted in the ascending order of the value in the age column. You can verify this by selecting all the records from the Patients table.

SELECT * FROM Patients

You can see that records are ordered in the ascending order of the values in the age column.

• For the tables without a clustered index, data is stored in an unordered heap. Heaps do not have an ordered structure, they do not have a natural order. This can get problematic as the table grows in size and data gets fragmented over time.

Non-Clustered Indexes

A non-clustered index is also used to speed up search operations. Unlike a clustered index, a non-clustered index doesn’t physically sort data into a table. In fact, a non-clustered index is stored in a separate location from the data table. A non-clustered index is like a book index, which is located separately from the main contents of the book. Since non-clustered indexes are located in a separate location, there can be multiple non-clustered indexes per table.

To create a non-clustered index, you have to use the “CREATE NONCLUSTERED” statement. The rest of the syntax remains the same as the syntax for creating a clustered index. The following script creates a non-clustered index “IX_tblPatient_Name” which sorts the records in ascending order of the name.

use Hospital

CREATE NONCLUSTERED INDEX IX_tblPatient_Name

ON Patients(name ASC)

The above script will create an index which contains the names of the patients and the address of their corresponding records as shown below:

Name	Record Address
Sara	Record Address
Jon	Record Address
Mike	Record Address
Nicky	Record Address
Sara	Record Address

Here, the “Record address” in each row is the reference to the actual table records for the Patients with corresponding names.

For example, if you want to retrieve age and gender of the patient named “Mike”, the database will first search “Mick” in the non-clustered index “IX_tblPatient_Name” and from the non-clustered index it will fetch the actual record reference and will use that to return actual age and gender of the Patient named “Mike”

Since a database has to make two searches, first in the non-clustered index and then in the actual table, non-clustered indexes can be slower for search operations. However, for INSERT and UPDATE operations, non-clustered indexes are faster since the order of the records only needs to be updated in the index and not in the actual table.

When to Use Clustered or Non-Clustered Indexes

Now that you know the differences between a clustered and a non-clustered index, let’s see the different scenarios for using each of them.

1.   Number of Indexes

This is pretty obvious. If you need to create multiple indexes on your database, go for non-clustered index since there can be only one clustered index.

2.   SELECT Operations

If you want to select only the index value that is used to create and index, non-clustered indexes are faster. For example, if you have created an index on the “name” column and you want to select only the name, non-clustered indexes will quickly return the name.

However, if you want to select other column values such as age, gender using the name index, the SELECT operation will be slower since first the name will be searched from the index and then the reference to the actual table record will be used to search the age and gender.

On the other hand, with clustered indexes since all the records are already sorted, the SELECT operation is faster if the data is being selected from columns other than the column with clustered index.

3.   INSERT/UPDATE Operations

The INSERT and UPDATE operations are faster with non-clustered indexes since the actual records are not required to be sorted when an INSERT or UPDATE operation is performed. Rather only the non-clustered index needs updating.

4.   Disk Space

Since, non-clustered indexes are stored at a separate location than the original table, non-clustered indexes consume additional disk space. If disk space is a problem, use a clustered index.

5.   Final Verdict

As a rule of thumb, every table should have at least one clustered index preferably on the column that is used for SELECTING records and contains unique values. The primary key column is an ideal candidate for a clustered index.

On the other hand columns that are often involved in INSERT and UPDATE queries should have a non-clustered index assuming that disk space isn’t a concern.

Reference: https://www.sqlshack.com/, https://www.sqlservertutorial.net, https://www.spotlightcloud.io/

Continue Reading →

Angular Forms

Angular provides two different approaches to handling user input through forms: reactive and template-driven. Both capture user input events from the view, validate the user input, create a form model and data model to update, and provide a way to track changes.

Template driven: With the template driven approach you basically apply directives, such as ngModel, in your template. Based on these directives Angular will create formcontrol objects. This approach is good for building simple forms with basic validation (required, minlength, maxlength,...).

Reactive forms: With the reactive approach you basically need to create new instances of the formcontrols and formcontrolgroups in your component. Reactive forms are the best choice for building complex forms and are better in case you have the intention to implement unit testing for your forms. Click here for example

Choosing an approach

Reactive forms and template-driven forms process and manage form data differently. Each approach offers different advantages.

Reactive forms provide direct, explicit access to the underlying forms object model. Compared to template-driven forms, they are more robust: they're more scalable, reusable, and testable. If forms are a key part of your application, or you're already using reactive patterns for building your application, use reactive forms.

High-level Differences between Template-driven and Reactive Forms

Below are some of the high-level differences between the two types:

• Template-driven forms make use of the "FormsModule", while reactive forms are based on "ReactiveFormsModule".
• Template-driven forms are asynchronous in nature, whereas Reactive forms are mostly synchronous.
• In a template-driven approach, most of the logic is driven from the template, whereas in reactive-driven approach, the logic resides mainly in the component or typescript code. 

Template-driven forms rely on directives in the template to create and manipulate the underlying object model. They are useful for adding a simple form to an app, such as an email list signup form. They're easy to add to an app, but they don't scale as well as reactive forms. If you have very basic form requirements and logic that can be managed solely in the template, template-driven forms could be a good fit.

Reactive form can be used in the following situation

• Complex forms with more number of fields.
• Multiple complex validation are there. Custom validations are required
• Require JSON structure to be send with the values in the form.
• We can get entire form in a structured way by using "form.value"

If we have 4 fields First Name, Last Name, Email, Phone Number in reactive form.

HTML code will be

<form [formGroup]="form">

    First Name <input formControlName="firstName">

    Last Name <input formControlName="lastName">

    Email <input formControlName="email">

    Phone Number <input formControlName="phoneNumber">

</form>

We can get the values from the form like below

{
"firstName": "FName",
"lastName": "LName",
"email": "test@123.com",
"phoneNumber": "123"
}

by calling form.value, where form is FormGroup Variable that we created.

Template Driven Form : It can be used when using simple forms. Like login page. With the two way data binding. We can simply assign value to variable from ui and vice versa.

Simple example is if we are givng two way binding for the below input.

<input [(ngModel)]="username">

We can simply display the value that user is giving in the UI.

<p>Hello {{username}}!</p>

You can build forms by writing templates in the Angular template syntax with the form-specific directives and techniques described in this page.

You'll learn to build a template-driven form that looks like this:

1- Create a new project named EmployeeForm:

ng new EmployeeForm

2- Chnage the directory

cd EmployeeForm

3- Install the angular forms module in your project 

npm install Forms

4- Add the Employee Model class

ng generate class EmployeeModel

It will generate a employee-model.ts file insrc/app directory.

5- Add members in the class file.

export class EmployeeModel {

public employeename: string ;

public Email: string;

public Username:string;

public Password: string;

}

6- Add a new Component named empcomponent

ng generate component empcomponent

It will generate required files for component in app directory. see the below screenshot.

7- Add the Form Module and newly created component in AppModule class.

import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';
import { FormsModule, ReactiveFormsModule } from '@angular/forms';
import { AppComponent } from './app.component';
import { EmpComponentComponent } from './emp-component/emp-component.component';

@NgModule({
  declarations: [
    AppComponent,
    EmpComponentComponent
  ],
  imports: [
    BrowserModule, FormsModule
  ],
  providers: [],
  bootstrap: [EmpComponentComponent]
})
export class AppModule { }

8- Add the below html in emp-component.component.html file.

<div class = "container">
   <h1>Employee Form</h1>
   <form #form="ngForm" class="form-horizontal" (ngSubmit)="SaveData(form)">

  <div class="form-group">
    <label class="col-sm-2 control-label">Name</label>
    <div class="col-sm-10">
      <input type="text" name="employeename" class="form-control" #employeename="ngModel" [(ngModel)]="model.employeename" required />
      <div *ngIf="employeename.errors && (form.submitted || employeename.dirty)" class="text-danger">
        <span [hidden]="!employeename.errors.required">Please Enter Name</span>
      </div>
    </div>
  </div>
<div class="form-group">
    <label class="col-sm-2 control-label">Email</label>
    <div class="col-sm-10">
      <input type="text" name="Email" class="form-control" #Email="ngModel" [(ngModel)]="model.Email" required pattern="^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+$"/>
      <div *ngIf="Email.errors && (form.submitted || Email.dirty)" class="text-danger">
        <span [hidden]="!Email.errors.required">Please Enter Email</span>
        <span [hidden]="!Email.errors.pattern">Please Enter Correct Email</span>
      </div>
    </div>
  </div>
    <div class="form-group">
    <label class="col-sm-2 control-label">Username</label>
    <div class="col-sm-10">
      <input type="text" name="Username" class="form-control" #Username="ngModel" [(ngModel)]="model.Username" required minlength="3"/>
      <div *ngIf="Username.errors && (form.submitted || Username.dirty)" class="text-danger">
        <span [hidden]="!Username.errors.required">Please Enter Username</span>
        <span [hidden]="!Username.errors.minlength">Username must have atleast 3 chars</span>
      </div>
    </div>
  </div>
  <div class="form-group">
    <label class="col-sm-2 control-label">Password</label>
    <div class="col-sm-10">
      <input type="password" name="Password" class="form-control" #Password="ngModel" [(ngModel)]="model.Password" required />
      <div *ngIf="Password.errors && (form.submitted || Password.dirty)" class="text-danger">
        <span [hidden]="!Password.errors.required">Please Enter Password</span>
      </div>
    </div>
  </div>

 <div class="form-group">
    <div class="col-sm-10 col-sm-offset-2">
      <button type="submit" class="btn btn-primary">Register</button>
    </div>
  </div>
   </form>
</div>

9- Add the below required code in emp-component.component.ts file

import { Component, OnInit } from '@angular/core';
import { EmployeeModel } from '../employee-model';
import { NgForm } from "@angular/forms";

@Component({
  selector: 'app-emp-component',
  templateUrl: './emp-component.component.html',
  styleUrls: []
})
export class EmpComponentComponent implements OnInit {

  model: EmployeeModel;
  constructor() { 
     this.model = new EmployeeModel();
  }
  SaveData(form: NgForm) {
    if (form.valid) {
      console.log(this.model);
      alert('valid!');
    }
  }
  ngOnInit() {
  }
}

10- Now replace the app-root component with emp-component in index.html file.

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>EmployeeForm</title>
  <base href="/">

  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="icon" type="image/x-icon" href="favicon.ico">
</head>
<body>
  <!-- <app-root></app-root> -->
  <app-emp-component></app-emp-component>
</body>
</html>

11- Now all the task has been done. now run the application.

ng serve

12- You can see the validation error in the above form. with the correct data form will be submitted.

13- Fill the correct data and click on Register button. It will display the message from server side.

All Done!

Continue Reading →

Breeze.js- Basics

Breeze.js is a client side JavaScript library that manages rich data. It is used for data management on client side using javascript.
If you store data in a database, query and save those data as complex object graphs, and share these graphs across multiple screens of your JavaScript client, Breeze is for you.
A Breeze client can communicate to almost any server technology including Node.js running Express with a MongoDB database. See Breeze-MongoDB integration in action

Client side Requirements:

Standard Breeze requirements:

1. Breeze.debug.js or Breeze.min.js
2. Q.js
3. Ajax library - usually jQuery

Additional MongoDB requirement:

1. Breeze.dataservice.mongo.js - a Breeze dataservice adapter that handles all of the MongoDB -specific client-side work involved in communicating with the MongoDB–backed service. 

Server side requirements

1- breeze-mongodb from npm - a node package that handles all of the server-side details of communicating between a Breeze client and a MongoDB-backed service.

npm install breeze-mongodb

Basics

You will write Breeze specific code in your Node server application to handle the following types of operations.

• Querying
• Saving

Breeze/MongoDB - Server side processing

The examples assume you’ll launch in node a JavaScript file containing standard node/express boilerplate that looks something like this:

server.js

var express = require('express');
var app = express();
var routes = require('./routes');  //  refs a routes.js file where most of our code will be written.
app.use(express.bodyParser());
app.use(app.router);
app.use(errorHandler);

Almost all of the Breeze/MongoDB code shown in these examples is assumed to be part of a “routes.js” file. Below is the beginning of such a file that opens a MongoDB database called “MyNorthwindDatabase”.

Assume a MongoDB server is running with access to this database.

routes.js

var mongodb = require('mongodb');             // MongoDB support package   
var breezeMongo = require('breeze-mongodb');  // Breeze MongoDB support package
var fs = require('fs');                       // Access to the local file system.

// Connect to a database.
var host = 'localhost';
var port = 27017;
var dbName = 'MyNorthwindDatabase';
var dbServer = new mongodb.Server(host, port, { auto_reconnect: true});
var db = new mongodb.Db(dbName, dbServer, { strict:true, w: 1});
db.open(function () { });

// route definitions begin here …

Querying with MongoDB

Let’s start on the breeze client which makes a query request to the server. Then we’ll see how routes.js redirects that request to the proper method for query processing.

Client side

Tell Breeze that you’re using MongoDB and everything else is standard Breeze. Put the following line somewhere in your application bootstrapping logic:

breeze.config.initializeAdapterInstance("dataService", "mongo", true);

Querying a MongoDB database from a Breeze client involves nothing more than a standard Breeze EntityQuery such as this one:

var query = EntityQuery.from("Products").where("ProductName", "startsWith", "C"); 

The real point here is that, in general, you cannot tell by looking at the client side code what backend datastore is behind any Breeze query.

Server side

In order to provide the most basic support for Breeze the minimum necessary requirement is simply that you give Breeze an endpoint and then route Breeze to this endpoint.

The routing could look something like this:

app.get('/breeze/Northwind/Products', routes.getProducts);

Thus a Breeze EntityQuery with “Products” in the EntityQuery.from clause is directed to the getProducts method in the routes.js file.

getProducts illustrates the typical implementation of a query processing method:

exports.getProducts = function(req, res, next) {
    // convert a client OData-style query string in the request to a MongoDB query
    var query = new breezeMongo.MongoQuery(req.query);

    // add custom server-side filtering to the query object here...
       query.filter["isDiscontinued"] = false;
     

    // execute the MongoDB query with a callback
    query.execute(db, "Products", processResults(res, next));
}  

// Return the query callback function
// res is the HTTP response object
// next is the Express HTTP pipeline callback
function processResults(res, next) {
    // return a function to process the results of the query
    // here we simply compose a response with the query results as content
    return function(err, results) {
        if (err) {
            next(err);
        } else {
            res.setHeader("Content-Type:", "application/json");
            res.send(results);
        }
    }
}

This is the standard template for most queries. The processResults method can be reused by all of the query methods discussed in this document.

Inside the query method

getProducts composes a query object by parsing the OData-style parameters that the Breeze client has passed in the URL query string and turning them into an equivalent MongoDB query expression. These implementation details are handled automatically by the Breeze MongoQuery class that you imported when you called “require(‘breeze-mongodb’)”.

The Breeze MongoQuery.execute receives three parameters: the database object (db), the name of a MongoDB collection in “MyNorthwindDatabase”, and a callback to process the results returned by MongoDB.

You can further constrain or augment the client query by modifying the Breeze query object before executing it. Continuing with our Products query, we may wish to ensure that no ‘discontinued’ products are ever returned. We’d specify that constraint with the query.filter property.

Saving with MongoDB

We’ll start on the client and return quickly to the server.

Client side

Saving to a MongoDB database from a Breeze client involves nothing more than a standard Breeze EntityManager.saveChanges call such as this one:

return myEntityManager.saveChanges().then(...);

Again, as with queries, in general, you cannot tell by looking at the client side code what backend datastore is behind any Breeze saveChanges call.

Server side

As with queries, in order to support Breeze’s client side EntityManager.saveChanges call, you will need to provide an endpoint and a route to this endpoint. Something like this:

app.post('/breeze/Northwind/SaveChanges', routes.saveChanges);

Here is a simple implementation for routes.saveChanges.

exports.saveChanges = function(req, res, next) {
    var saveHandler = new breezeMongo.MongoSaveHandler(db, req.body, processResults(res, next));
    saveHandler.save();
};

Validation through save interception

You authorize and validate client save-data with save “interceptors”. You can even modify the save data with interceptors.

Breeze offers two interceptor methods: MongoSaveHandler.beforeSaveEntity and MongoSaveHandler.beforeSaveEntities.

These are methods that you write and breeze calls just before saving the data to the MongoDB database.

exports.saveChanges = function(req, res, next) {
    var saveHandler = new breezeMongo.MongoSaveHandler(db, req.body, processResults(res, next));
    // write one or both of the following
    // saveHandler.beforeSaveEntity = myCustomBeforeSaveEntity;
    // saveHandler.beforeSaveEntities = myCustomBeforeSaveEntities;
    saveHandler.save();
};

You can define one or both of these methods. Breeze first calls beforeSaveEntity for every entity in the save-set and then calls beforeSaveEntities.

Each method has a distinct purpose:

• beforeSaveEntity - review and possibly modify or reject each entity individually.
• beforeSaveEntities - review and possibly modify the entire collection of entities to be saved (the “save-set”). You can modify or remove any of them. You can add more entities-to-save, potentially of types not included in the original save-set.

beforeSaveEntity

Breeze doesn’t define this method; you do. Breeze calls your custom implementation of the beforeSaveEntity interceptor once for each entity in the save-set.

Save Example #1:

Ensure that every new Product we add to the database has at least a $.50 surcharge.

function myCustomBeforeSaveEntity(entity) {
    var entityAspect = entity.entityAspect;
    if (entityAspect.entityTypeName === "Product" && entityAspect.entityState === "Added") {
        if (entity.surcharge < .5) entity.surcharge = .5;
    }
    return true;
}

Save Example #2:

Prevent new products from being added to “revoked suppliers” by removing such products from the save-set.

function myCustomBeforeSaveEntity(entity) {
    var entityAspect = entity.entityAspect;
    if (entityAspect.entityTypeName === "Product" && entityAspect.entityState === "Added") {
        if (revokedSupplierNames.indexOf(entity.supplierName) >= 0) return false;
    }
    return true;
}

If the method returns false, breeze will not save this entity. Breeze will continue to evaluate the remaining entities and may save them.

You can throw an exception if you want to terminate the save immediately.

beforeSaveEntities

Your beforeSaveEntities method is granted access to the entire save-set through several public properties on the MongoSaveHandler instance. The MongoSaveHandler instance is the this object within your beforeSaveEntities function.

Save Example #3:

Add 5% to the freight cost on every order saved.

function myCustomBeforeSaveEntities(callback) {
    var orderTypeName = this.qualifyTypeName("Order");
    var orders = this.saveMap[orderTypeName] || [];
    
    orders.forEach(function(order) {
       order.freightCost = order.freightCost * 1.05;    
    });
    callback();
}

Reference: http://breeze.github.io/

Continue Reading →