Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Indexing

Indexing is something like adding page numbers to book in real world example. As a publisher if you are not adding page number to book it’s very hard for reader or user to read book and find information from specific page.

Indexes directly affects application performance, good indexing improves application
performance and bad indexing reduce application performance. 

Definition:

An index is an on-disk structure associated with a table or views that speeds retrieval of rows from the table or view.

Basic Types:

Clustered:

A table can have only one clustered index. That is because a clustered index is the actual table sorted in order of the cluster key.
If table has no clustered index data are stored in an unordered structure called heap.

Real word Example for clustered index:

Phone directory book, each directory of phone book represents one row. Primary key on table is by default clustered index for table.  

Nonclustered:

A table can have one or many non clustered indexes (i.e. max 999 for SQL SERVER 2008 and 249 for SQL server 2005).
A clustered index uses clustered key to sorts and stores the data rows of the table or view.The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.

Example for Clustered Index:

In Web-shop billing or shipping Address has multiple column data we can add User Name+MobileNumber Or telephone as Non Clustered index.
It is recommended to use unique key for non clustered indexes.

Special purpose indexes:

Hash

With a hash index, data is accessed through an in-memory hash table.

Memory-optimized nonclustered indexes

For memory-optimized nonclustered indexes, memory consumption is a function of the row count and the size of the index key columns

Unique

Uniqueness can be a property of both clustered and nonclustered indexes.This ensures that index kay contain no duplicate values.

XML

A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.

Spatial

A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type.

Filtered

An optimized nonclustered index. A proper designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

Index with included columns

A nonclustered index that is extended to include nonkey columns in addition to the key columns.

Index on computed columns

An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.

Insert into select statement

Insert into select statement:

With SQL you can copy data from one table and insert into another table using SQL Insert into select statement.

Syntax:

If using different table with same columns

INSERT INTO FirstTable
SELECT * FROM SecondTable;

Different table with some same columns only

INSERT INTO FirstTable
(column_name(s))
SELECT column_name(s)
FROM SecondTable;

You can use same table also

INSERT INTO FirstTable
SELECT * FROM FirstTable;
OR
INSERT INTO FirstTable
(column_name(s))
SELECT column_name(s)
FROM FirstTable;


Example:

How to Insert duplicate records from same table ?

Below example explain how I can insert duplicate records from same table,my requirment is I want to duplicate product with different countryid same discount price for country india.

INSERT INTO ProductDiscounts ([ProductId],
             [CountryId] ,
            [DiscountPrice],
            [TaxRate])
             
            SELECT [ProductId],
             2,
            [DiscountPrice],
            [TaxRate] FROM ProductDiscounts WHERE ProductId=(SELECT TOP 1 id FROM products WHERE
            productcode='MX-IND-001') AND
            countryId=(SELECT TOP 1 id from Country WHERE CountryCode='IN')

 

Example with different tables:

How to Insert records from one table to another table?

INSERT INTO Products ([ProductCode],
            [Type] ,
            [Description],
            [isActive])
             
            SELECT [ProductCode],
            [ProductType],
            [Description],
            [IsAvailable] FROM SellableProducts WHERE
            countryCode='IN'
 
 
 

Unique constraint

Unique constraint:

Unique constraints are rules that enforce uniqueness or prevent duplication on column data.

Points to remember:

1. UNIQUE constraints allow null values, only one value is allowed per unique column.
2.  A UNIQUE constraint can be referenced by a FOREIGN KEY constraint.
3.  If you are trying to add unique key for existing column which has duplicate data, the Database Engine returns an error and does not add the constraint.
4.  For unique constraint column duplicates values are not allowed to add.
5.  The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint

Create a unique constraint using SQL Server management studio:

1.                  In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design.


2.                  On the Table Designer menu, click Indexes/Keys.


3.                  In the Indexes/Keys dialog box, click Add.


4.                  In the grid under General, click Type and choose Unique Key from the drop-down list box to the right of the property.

 


                                                                                                                                                  Figure 4

 

5.                  On the File menu, click Save table name,Check created unique constraint.


 

To modify:

1.   In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design, then click on Indexes/Keys from table designer menu.
2.  In the Indexes/Keys dialog box, under Selected Primary/Unique Key or Index, select the constraint you want.
3.  Complete an action from the following:


Change the columns that the constraint is associated with:
In the grid, click Columns and then click the ellipses (…) to the right of the property.

Rename the constraint:

Type a new name in the Name box. Make sure that your new name does not duplicate a name in the Selected Primary/Unique Key or Index list.

Set the clustered option:
In the grid select the Create As Clustered and from the dropdown choose Yes to create a clustered index and No to create a non-clustered one. Only one clustered index can exist per table. If a clustered index already exists, you must first clear this setting on that index.

Define a fill factor: Expand the Fill Specification category and type an integer from 0 to 100 in the Fill factor box.

 

To Delete:

1.  In Object Explorer, right-click the table to which you want to add a unique constraint, and click Design, then click on Indexes/Keys from table designer menu
2.  In the Indexes/Keys dialog box, select the unique key in the Selected Primary/Unique Key and Index list.
3.  Click Delete.

Unique constraint using T-SQL:

How to Add UNIQUE Constraint on CREATE TABLE?

Column Level:

USE [WebShop]
GO
CREATE TABLE [dbo].[User](
     [ID] [int] IDENTITY(1,1) NOT NULL Primary Key,
     [ManagerId] [int] NULL,
     [UserType] [tinyint] NULL,
     [LoginName] [nvarchar](50) NULL Unique,
     [Password] [nvarchar](8) NULL,
     [CreatedDate] [datetime] NULL,
     [UpdatedDate] [datetime] NULL,
     [DeletedDate] [datetime] NULL
     )
 
GO

Table Level:

USE [WebShop]
GO
CREATE TABLE [dbo].[User](
     [ID] [int] IDENTITY(1,1) NOT NULL Primary Key,
     [ManagerId] [int] NULL,
     [UserType] [tinyint] NULL,
     [LoginName] [nvarchar](50) NULL,
     [Password] [nvarchar](8) NULL,
     [CreatedDate] [datetime] NULL,
     [UpdatedDate] [datetime] NULL,
     [DeletedDate] [datetime] NULL,
     CONSTRAINT UK_LoginName UNIQUE ([LoginName]),
     CONSTRAINT UK_NameManager UNIQUE ([ManagerId],[LoginName])
     )
 
GO

 

How to add a UNIQUE Constraint on ALTER TABLE?

Single Unique Key:

ALTER TABLE [User]
ADD CONSTRAINT UK_LoginName UNIQUE(LoginName)

Multiple uniqe key:

ALTER TABLE [User]
ADD CONSTRAINT UK_NameManager UNIQUE(ManagerId,LoginName)

 

How to DROP a UNIQUE Constraint?

ALTER TABLE [User]
DROP CONSTRAINT UK_LoginName