SQL index

In SQL index are used to speed up the performance of queries. It reduce the number of database data pages scanned. In SQL Server, a clustered index determines the physical order of data in a table. Remember There can be only one clustered index per table. An index is nothing but a copy of select columns from a table that can be searched very efficiently that also includes a low-level disk block address

Please Note that updating a table’s data with indexes takes more time than updating a table without because the indexes also need an update. So you should only create indexes on columns and tables that will be frequently searched.

We have two type of index in SQL

 1.  Clustered Index

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

When creating the clustered index, SQL server 2005 reads the Emp_id column and forms a Binary tree on it. This binary tree information is then stored separately in the disc. Expand the table Emp_Details and then expand the Indexes. You will see the following index created for you when the primary key is created:

SELECT * FROM Emp_Details WHERE Emp_id=662

Execution of the query will be

11. It will check the root node as 662 is less than 2000: move to left node

  2.   On 1st level it will check as 662 will in between 501-1000: move to right

It will get the records and send back the results  

2. Non Clustered Index

A non-clustered index is useful for columns that have some repeated values. Say for example, Emp_Id column of a bank database may have 15 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.

Sql Constraint

Sql Constraint

Constraints are the rules apply on data columns on table. These are used to ensure the accuracy and reliability of the data in the database.

There are two type of Constraints

·         Column level : Column level constraints are applied only to one column

·         Table level: table level constraints are applied to the whole table.

Following are commonly used constraints available in SQL.

·        NOT NULL: Ensures that a column cannot have NULL value.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL,

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200)

)

·        DEFAULT: Provides a default value for a column when none is specified.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL,

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200),

JoiningDate Datetime DEFAULT GETDATE()

)

·        UNIQUE: Ensures that all values in a column are different.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL ,

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200),

CONSTRAINT uc_EmpID UNIQUE (Emp_Id)

)

·        PRIMARY: Uniquely identified each rows/records in a database table.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL PRIMARY KEY,

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200),

)

·        FOREIGN Key: Uniquely identified a rows/records in any another database table.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL PRIMARY KEY,

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200),

PRIMARY KEY (Emp_Id) FOREIGN KEY REFERENCES Emp_Master(Emp_Id)

)

·        CHECK: The CHECK constraint ensures that all values in a column satisfy certain conditions.

CREATE TABLE Emp_Details

(

Emp_Id int NOT NULL CHECK (P_Id>0),

Emp_Name varchar(200) NOT NULL,

Address varchar(200),

City varchar(200),

PRIMARY KEY (Emp_Id)

)

·        INDEX: Use to create and retrieve data from the database very quickly.

User can apply the Constraints either at the time of creation of the table with the CREATE TABLE statement or by using ALTER TABLE statement to create constraints on existing table.

Dropping Constraints:

Constraint can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.

e.g., to drop the primary key constraint in the EMP_Details table, you can use the following command:

ALTER TABLE EMP_Details DROP CONSTRAINT EMP_Id;

Integrity Constraints:

Integrity constraints are used to ensure accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity.

Delete duplicate records from a SQL table without a primary key

If we need to Delete duplicate records from a SQL table without a primary key then we can do with many ways. please try below statements 

1st:

DELETE SUB FROM

(SELECT ROW_NUMBER() OVER (PARTITION BY EmpId, EmpName, EmpSSN ORDER BY EmpId) cnt

 FROM Employee) SUB

WHERE SUB.cnt > 1

2nd:

With duplicates

As

(Select *, ROW_NUMBER() Over (PARTITION by EmpID,EmpSSN Order by EmpID,EmpSSN) as Duplicate From Employee)

delete From duplicates

Where Duplicate > 1 ;

These Statements will update Table and remove all duplicates from the Table!

Delete duplicate records from a table

Delete duplicate records from a table (SQL) having a unique id (Primary key) or auto incremented id.

DELETE FROM Emp_Details WHERE EmpID in(SELECT EmpID FROM Emp_Details GROUP BY EmpId HAVING  COUNT(*) >1)

Where Emp_Details is a table and EmpID is A auto incremented  Column 

Self join in SQL

Self join

Self join is a join in which a table is joined with itself , specially when the table has a FOREIGN KEY which references its own PRIMARY KEY. To join a table itself means that each row of the table is combined with itself and with every other row of the table.The self join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.

Please Check the example Below.