SQL Server - Clustered Indexes and Non Clustered Indexes

To understand the concept of Clustered and Non Clustered indexes, it is important to understand the definition and functionalities of indexes first. An Index is a structure related to a view or table that speeds up the processes by quick retrieval of integrated rows from view or table.

There are two types of indexes exists in view or table. These are:

1. Clustered Indexes
2. Non-Clustered Indexes

An Overview on Clustered Indexes:

The data inserted by users in the tables is physically rearranged by Clustered Indexes. In a table, the physical arrangement and the order of data is determined by the clustered table. On disk media, the clustered index is arranged through a proper storage paradigm.

In clustered indexes, data access is a complex task comparative to accessing data in data pages. When group of similar values is been entered in the database table, this clustering of items is termed as Clustered Index.

In the view or table, the data rows are sorted and stored on the basis of the key values by Clustered Indexes. Only one clustered index exists in a table because the data rows can only be sorted and rearranged in one order.

The only instance when rows are sorted and stored in a table is when the database table holds a clustered index. The instance when the database table contains clustered index is known as clustered table. On the contrary, when no clustered index exits in a table and the rows are saved in an unarranged format, this condition is termed as ‘Heap’.

Standard statements i.e., SELECT, INSERT, UPDATE, DELETE can be used for accessing data in a table with clustered index. The only requisite for running all these operations on the table is that the data in the SQL Server must be stored as per parameters set by clustered indexes.

Clustered Index can be used for:

1. Column containing large volume of different values.
2. Columns that are viewed in sequence.
3. Queries returning wide result set.

Clustered Index must not be used for queries that involve frequent changes as well as on the wide keys.

Associated Limitations & Restrictions:

While creating clustered indexes, it requires that the disk space associated to both source (old) and target (new) structures must be allocated within their respective file groups and files.

When clustered index is built along with the non clustered index, the non clustered indexes requires to be rebuilt so that they can hold clustered key value in place of row identifier (RID).

For more details about how to create clustered indexes visit

An Overview on Non-Clustered Indexes:

In non-clustered environment, the structure of the indexes varies from the structure of the data rows. Based on the non-structured keys, the data rows are not stored or sorted in an arranged manner. In this structure, the leaf layer is not consisted of the data pages and in fact the leaf node contains the index row.

The overall flow of processes seems to be quite puzzling in this environment. A non clustered key value exists in non clustered index and every key value points towards the data row that contains that particular key value.

In non-clustered index, pointer from index row towards the data row is referred to as the row locator. The formation of the row locator depends upon the storage of data pages that decides if it is been stored in heap or a clustered table.

In SQL Server environment, indexes are not unique and the same key can be shared by multiple rows. On the contrary, the clustered as well as the non clustered indexes can be unique and this further implies that two different rows cannot share the same index key values.

The non-clustered pages are considered for:

1. Column containing wide volume of different values.
2. Columns that involved in frequent search operation of a query and return accurate results.
3. Queries returning smaller result sets.

Conclusion:

Both clustered and non clustered indexes exist with indifferent approach and merits. In SQL Server environment, both of these two concepts are implemented under set parameters. Creating non clustered indexes on tables sometimes proved to be beneficial as they appear identical to that of clustered indexes. 

Read More

Introduction to SQL Server Functions With Example

SQL Server have functions which can be defined simply as database object or set of SQL statements which accept only input parameters, perform actions like calculations, and return value as a result. This result can be a single value and is scalar. Functions cannot be used to Update, Delete or Insert records to the SQL databases.

SQL Functions and Types

Majorly, Functions can be categorized as;

1. System Defined Function
2. User-defined Function

System-defined Function

These kinds of functions are defined by the SQL Server itself to be utilized in different instructions for different purpose. There are two types of system defined function;

1. Scalar Function

These SQL Scalar functions return as a result a single value and operate on a single value. Let us see some examples;

System Scalar Functions

Scalar Function		Description
abs(-3.14)		Absolute number will be returned i.e. 3.14.
rand(6)		Allows generation of random number with 6 characters.
round(54.73871,4)		Round-off figure will be returned to 4 places of decimal 54.7387
upper('abcd')		Uppercase of provided string i.e. 'ABCD'
lower('ABCD')		Lower case of given string i.e. 'abcd'
ltrim(' abcd')		This will trim out the spaces from left hand side of 'abcd' string.
convert(int, 6.14)		Given float value will be converted to integer i.e. 6.

      2. Aggregate Function

Aggregate functions work on collection of values returning single value as result. Let us see some of the examples of Aggregate functions;

Aggregate Function			Description
max()			Maximum value from a collection of values will be returned.
min()			Minimum value from a collection of values will be returned.
avg()			Average value will be returned from the values inside collection.
count()			No of counts available inside a collection will be returned.

User Defined Function:

User-defined functions are created by user in user-defined database or system database. Let us see about these types and their examples;

1. Scalar Function

Single value will be returned as a result by user-defined scalar function also. Any type of data type value can be returned from the function.

2. Inline Table-Valued Function

Result of user-defined inline table-valued function is a table variable which is returned after actions performed by the function. The value of table variable should be derived from a single SELECT statement.

       3. Multi-statement Table-Valued Function

Multi-statement scalar function has a function body inside BEGIN and END block with multiple statements. It returns a table-variable as a result followed by the operation of program. It is important to explicitly declare a table variable whose values will be returned from multiple SQL statements.

User-defined Function Benefits:

• Help in modular programming: Function can be created and stored in the database. Once it is stored it can be called number of times in the SQL program. User-defined functions can be modified without depending upon the program source code.
• Faster execution is done by catching plans and using them again and again for repeated executions.
• Network traffic is also reduced as data is filtered with some operations.

Noteworthy Points:

• Function will return only single value, accepts only input parameters, and function is not used to Insert, Delete or Update in database.
• Function can be nested up to 32 levels and does not support Exception handling.
• Function cannot return XML data type, and can call only Extended Stored Procedure.

Conclusion:

In the section discussed above, we discussed about different types of function in SQL server along with their examples. Functions main category has been also elaborated.

Read More