SQL NULL functions are used to perform operations on NULL values that are stored in the database tables.

A NULL value serves as a placeholder in the database when data is absent or the required information is unavailable. It is a flexible value not associated to any specific data type and can be used in columns of various data types, including string, int, varchar, and more.

Following are the various features of a NULL value −

• The NULL value is different from a zero value or a field containing a space. A record with a NULL value is one that has been left empty or unspecified during record creation.

• The NULL value assists us in removing ambiguity from data. Thus, maintaining the uniform datatype across the column.

SQL NULL Functions

To handle these NULL values in a database table, SQL provides various NULL functions. They are listed as follows −

• ISNULL()
• COALESCE()
• NULLIF()
• IFNULL()

The ISNULL() Function

The SQL ISNULL() function returns 0 and 1 depending on whether the expression is null or not. If the expression is null, then this function returns 1; otherwise, it returns 0.

Syntax

Following is the syntax for the ISNULL() function −

ISNULL(column_name)

Example

First of all let us create a table named CUSTOMERS, containing the personal details of customers including their name, age, address and salary etc., using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert records into this table using the INSERT INTO statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', NULL ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', NULL ),
(7, ''Indore'', 24, ''Indore'', 10000.00 );

The table will be created as −

Following is the query to check whether SALARY is NULL or not −

SELECT SALARY, ISNULL(SALARY) AS Null_value FROM CUSTOMERS;

Output

On execution of the above query, we get the column “SALARY” and Null_value. If the SALARY is NULL, then their null value is 1; otherwise, it is 0. −

The COALESCE() Function

The SQL COALESCE() function returns the first occurred NON-NULL expression among its arguments. If all the expressions are NULL, then the COALESCE() function will return NULL.

An integer is evaluated first in the COALESCE() function, and an integer followed by a character expression always produces an integer as the output.

Syntax

Following is the syntax for the COALESCE() function −

COALESCE(expression_1, expression_2, expression_n);

Example

In the following query, we are returning the first occurred NON-NULL value −

SELECT COALESCE (NULL, ''welcome'', ''tutorialspoint'') AS Result;

Output

On executing the above query, we get “welcome” as a result, because it is the first NON-NULL value −

Example

In the following query, we are using the COALESCE() function on the SALARY and AGE columns of CUSTOMERS table. The first NON-NULL values evaluated from these two columns are displayed in another column named “Result”.

SELECT NAME, SALARY, AGE, COALESCE(SALARY, AGE) AS Result FROM CUSTOMERS;

Output

When you execute the above query, we get the following table as a result −

The NULLIF() Function

The SQL NULLIF() function compares two expressions. If both expressions are the same, it returns NULL. Otherwise, it returns the first expression. This function can be used directly with clauses like SELECT, WHERE, and GROUP BY.

Syntax

Following is the syntax of NULLIF() function −

NULLIF(expression_1, expression_2);

Example

The following SQL query uses NULLIF() function to compare values in NAME and ADDRESS columns of the CUSTOMERS table. If the NAME value matches the ADDRESS value, the result is NULL; otherwise, it returns the NAME value. The result values are stored in another column called “Result”.

SELECT NAME, ADDRESS, NULLIF(NAME, ADDRESS) AS Result FROM CUSTOMERS;

Output

When you execute the above query, we get the following table as a result −

The IFNULL() Function

The IFNULL() function replaces the NULL values in a database table with a specific value. This function accepts two arguments. If the first argument is a NULL value, it is replaced with the second argument. Otherwise, the first argument is returned as it is.

This function does not work in the SQL Server database.

If both the arguments are NULL, the result of this function is also NULL.

Syntax

Following is the syntax for IFNULL() function −

IFNULL(column_name, value_to_replace);

Example

The following query evaluates the values in SALARY column of the CUSTOMERS table. Using the IFNULL() function, we are replacing the NULL values in this column (if any) with the value 5500 −

SELECT NAME, SALARY, IFNULL(SALARY, 5500) AS Result FROM CUSTOMERS;

Output

Following is the output of the above query −

SQL Hosting

SQL Hosting is nothing but a means to manage any RDBMS linked to your website using SQL. If the website has an access to a RDBMS, any data from the website you created will be stored and retrieved from this database.

There are various SQL hosting plans available if your web server is hosted by an Internet Service Provider (ISP).

Following are the most common SQL hosting databases −

• MS SQL Server
• Oracle
• MySQL
• MS Access

MS SQL Server

MS SQL Server is created and developed by Microsoft. It is compatible with both virtual and cloud servers. It is very efficient to use with database-driven websites having high traffic.

MS SQL Server”s features include −

• Maximum scalability and security
• Integrated reporting capabilities
• Easy to use
• Powerful
• Robust
• Offers more diverse features while hosting

Oracle

Oracle is a popular database which is suitable to use with high-traffic websites. This database offers various features like,

• Cost-effective product
• High-performance
• Converged, multi-model database management system
• In-memory MySQL databases

Oracle is also a very powerful, robust and full featured SQL database system.

MySQL

MySQL is one of the most popular RDBMS in the world used to store and manage data. It is compatible with any type of server, say cloud, virtual or dedicated. Features of MySQL are as follows −

• Easy to use
• High performance
• Excellent security
• Improved speed
• Cost effective

MS Access

Microsoft Access is a very simple database which can be used for simple websites. MS Access is neither as powerful as MySQL, MS SQL Server or Oracle. Thus, it is not effective for websites with higher traffic.

The MIN() and MAX() functions in SQL are aggregate functions. They are used to compare values in a set and, retrieve the maximum and minimum values respectively.

An aggregate function is a mathematical computation that takes a range of values as input and yields a single value expression, representing the significance of the provided data.

MAX() and MIN() aggregate functions are generally used in two ways:

• As functions, they are used with the GROUP BY clause of the SELECT statement.

• As expressions, they are used with a subquery and HAVING clause of SELECT statement.

The SQL MAX() Function

The MAX() function compares the values in a column and returns the largest value among them.

Syntax

Following is the syntax of SQL MAX() function −

MAX(column_name);

Example

In the following example, we are running a query for MAX() function on a table named CUSTOMERS. The objective is to retrieve the maximum salary value from this table. First of all, let us create the CUSTOMERS table using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The CUSTOMERS table will be created as −

Here, we are comparing the salaries of CUSTOMERS and retrieving the maximum salary using the following query −

SELECT MAX(SALARY) FROM CUSTOMERS;

When the above query is executed, the result is displayed as −

HAVING with MAX() Function

In the following query, we are fetching the ID, NAME, and SALARY of the CUSTOMERS using the MAX() function along with HAVING clause.

SELECT ID, NAME, SALARY
FROM CUSTOMERS
GROUP BY NAME, ID
HAVING MAX(SALARY) < 8000;

When the above query is executed, we get the details of the employees whose maximum salary is less than 8000 −

MAX() Function in Subqueries

In the following example, we are using the MAX() function in a subquery to retrieve the record with maximum salary, from the CUSTOMERS table.

SELECT * FROM CUSTOMERS
WHERE SALARY = (SELECT MAX(SALARY) FROM CUSTOMERS);

When we execute the above query, we will get the following result −

MAX() Function with Strings

This query retrieves the maximum value (alphabetically) among the names of customers in the CUSTOMERS table using the MAX() function −

SELECT MAX(NAME) AS max_name FROM CUSTOMERS;

Following is the result of the above query −

Aliases with MAX() Function

In the following example, we use the MAX() function to retrieve the record containing maximum age from the CUSTOMERS table. We are displaying the results as a new column with the alias “max_age”.

SELECT MAX(age) AS ''max_age'' FROM CUSTOMERS;

Following is the output of the above query −

The SQL MIN() Function

The MIN() function compares values in a column and returns the smallest value among them.

Syntax

Following is the syntax of SQL MIN() function −

MIN(column_name);

Example

In this example, we are comparing values in the SALARY column of CUSTOMERS table and displaying the minimum salary using the following query −

SELECT MIN(SALARY) FROM CUSTOMERS;

When the above query is executed, the result is displayed as −

HAVING with MIN() Function

In the following query, we are fetching the ID, NAME, and SALARY of the CUSTOMERS using the MIN() function along with HAVING clause.

SELECT ID, NAME, SALARY
FROM CUSTOMERS
GROUP BY NAME, ID
HAVING MIN(SALARY) > 5000;

When the above query is executed, we get the details of the maximum salary for employees whose minimum salary is more than 5000, as we can see in the table that follows −

MIN() Function in Subqueries

In the following example, we are using the MIN() function in a subquery to retrieve the record with minimum salary, from the CUSTOMERS table.

SELECT * FROM CUSTOMERS
WHERE SALARY = (SELECT MIN(SALARY) FROM CUSTOMERS);

When we execute the above query, we will get the following result −

MIN() Function with Strings

Following is the query to retrieve the minimum value (alphabetically) among the names of customers in the CUSTOMERS table using the MIN() function −

SELECT MIN(NAME) AS min_first_name FROM CUSTOMERS;

Following is the result of the above query −

Aliases with MIN() Function

Following is the SQL query that will fetch the minimum age from the CUSTOMERS table using the MIN() function −

SELECT MIN(age) AS ''min_age'' FROM CUSTOMERS;

When we execute the above query, the minimum value in the age field is displayed as shown below.

SQL Wildcards

SQL Wildcards are special characters used as substitutes for one or more characters in a string. They are used with the LIKE operator in SQL, to search for specific patterns in character strings or compare various strings.

The LIKE operator in SQL is case-sensitive, so it will only match strings that have the exact same case as the specified pattern.

Following are the most commonly used wildcards in SQL −

The percent sign (%) represents zero, one, or multiple characters within a string. The underscore (_) represents a single character or number. These symbols can also be used in combination to perform complex pattern searching and matching in SQL queries.

Syntax

Following is the basic syntax to use wildcard characters −

SELECT * FROM table_name
WHERE column_name LIKE [wildcard_pattern];

We can combine N number of conditions using the AND or the OR operators. Here, the [wildcard_pattern] can represent any numeric or string value.

The following table demonstrates various ways of using wildcards in conjunction with the LIKE operator within a WHERE clause:

Example

Firstly, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following INSERT query adds records into the CUSTOMERS table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table will be created as −

Here, we are displaying all the records from the CUSTOMERS table where the SALARY starts with 200.

SELECT * FROM CUSTOMERS WHERE SALARY LIKE ''200%

Output

This would produce the following result.

A comment is a piece of text that is used in programming languages to provide additional information. These comments are ignored by the compiler and do not affect the program”s functionality. They are not visible in the output after the execution of code. Their purpose is to make the source code easier for human to understand more clearly.

SQL Comments

In SQL, comments can be used to explain a particular section of a query; or to skip the execution of statement. So, whenever a line of code is marked as a comment in a program, it is not executed.

There are two types of comments used in MySQL database, they are as follows −

• Single-line comments
• Multi-line comments

Single Line Comments

The SQL single line comments starts with two consecutive hyphens (i.e. –) and extends to the end of the line. The text after the hyphens will not be executed.

Syntax

Following is the syntax of SQL single line comment −

-- This is a single-line comment

Example

In the following query, we are using a single line comment to write a text −

-- Will fetch all the table records
SELECT * from table;

Example

Here, we have a SELECT statement that retrieves data from a table named CUSTOMERS. Though we have an ORDER BY clause in this statement, since we have commented that part, this query just retrieves the records in the CUSTOMERS table without sorting the result −

SELECT * FROM CUSTOMERS -- ORDER BY NAME ASC;

Example

Now, we are using the single line comment to ignore the last statement −

SELECT * FROM CUSTOMERS;
SELECT * FROM EMPLOYEES;
-- SELECT * FROM ORDERS WHERE ID = 6;

Multi-Line Comments

The SQL multi line comments are used to comment out multiple lines or a block of SQL code. It starts with /* and ends with */. Entire text between these delimiters (/*…*/) will be ignored and considered as a comment.

Syntax

Following is the syntax of SQL multi line comments −

/* This is a
   multi-line
   comment */

Example

The following example uses multi-line comment as an explanation of the query −

/*following query
will fetch all the
table records./*
SELECT * from CUSTOMERS;

Example

Here, we are using the multi-line comments (/*….*/) to ignore a part of the query, making it as a comment −

SELECT ID /*AGE, SALARY*/
FROM CUSTOMERS WHERE SALARY = 1500.00;

Example

In the following query, we are ignoring multiple statements using a multi-line comment −

/*SELECT * FROM CUSTOMERS;
SELECT * FROM EMPLOYEE;*/
SELECT * FROM ORDERS WHERE ID = 6;

If you take a user input through a webpage and insert it into an SQL database, there is a chance that you have left yourself wide open for a security issue known as the SQL Injection. This chapter will teach you how to help prevent this from happening and help you secure your scripts and SQL statements in your server side scripts such as a PERL Script.

SQL Injection

SQL Injection is a type of security attack that exploits a vulnerability in a database by executing malicious queries. This will allow attackers to access sensitive data, tamper it and also delete it permanently.

Injection usually occurs when you ask a user for input, like their name and instead of a name they give you a SQL statement that you will unknowingly run on your database. Never trust user provided data, process this data only after validation; as a rule, this is done by Pattern Matching.

Example

In the example below, the name is restricted to the alphanumerical characters plus underscore and to a length between 8 and 20 characters (you can modify these rules as needed).

if (preg_match("/^w{8,20}$/", $_GET[''username''], $matches)) {
   $result = mysqli_query("SELECT * FROM CUSTOMERS
      WHERE name = $matches[0]");
} else {
   echo "user name not accepted";
}

To demonstrate the problem, consider this excerpt −

// supposed input
$name = "Qadir DELETE FROM CUSTOMERS;";
mysqli_query("SELECT * FROM CUSTOMSRS WHERE name=''{$name}''");

The function call is supposed to retrieve a record from the CUSTOMERS table where the name column matches the name specified by the user. Under normal circumstances, $name would only contain alphanumeric characters and perhaps spaces. But here, by appending an entirely new query to $name, the call to the database turns into disaster; the injected DELETE query removes all records from the CUSTOMERS table.

Fortunately, if you use MySQL, the mysqli_query() function does not permit query stacking or executing multiple SQL queries in a single function call. If you try to stack queries, the call fails.

However, other PHP database extensions, such as SQLite and PostgreSQL happily perform stacked queries, executing all the queries provided in one string and creating a serious security problem.

Preventing SQL Injection

You can handle all escape characters smartly in scripting languages like PERL and PHP. The MySQL extension for PHP provides the function mysql_real_escape_string() to escape input characters that are special to MySQL.

if (get_magic_quotes_gpc()) {
   $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysqli_query("SELECT * FROM CUSTOMERS WHERE name=''{$name}''");

The LIKE Quandary

To address the LIKE quandary, a custom escaping mechanism must convert user-supplied ”%” and ”_” characters to literals. Use addcslashes(), a function that lets you specify a character range to escape.

$sub = addcslashes(mysql_real_escape_string("%str"), "%_");
// $sub == %str_
mysqli_query("SELECT * FROM messages
   WHERE subject LIKE ''{$sub}%''");

SQL Non-Clustered Indexes

The SQL Non-Clustered index is similar to the Clustered index. When defined on a column, it creates a special table which contains the copy of indexed columns along with a pointer that refers to the location of the actual data in the table. However, unlike Clustered indexes, a Non-Clustered index cannot physically sort the indexed columns.

Following are some of the key points of the Non-clustered index in SQL −

• The non-clustered indexes are a type of index used in databases to speed up the execution time of database queries.
• These indexes require less storage space than clustered indexes because they do not store the actual data rows.
• We can create multiple non-clustered indexes on a single table.

MySQL does not have the concept of Non-Clustered indexes. The PRIMARY KEY (if exists) and the first NOT NULL UNIQUE KEY(if PRIMARY KEY does not exist) are considered clustered indexes in MySQL; all the other indexes are called Secondary Indexes and are implicitly defined.

To get a better understanding, look at the following figure illustrating the working of non-clustered indexes −

Assume we have a sample database table with two columns named ID and NAME. If we create a non-clustered index on a column named ID in the above table, it will store a copy of the ID column with a pointer that points to the specific location of the actual data in the table.

Syntax

Following is the syntax to create a non-clustered index in SQL Server −

CREATE NONCLUSTERED INDEX index_name
ON table_name (column_name)

Here,

• index_name: holds the name of non-clustered index.
• table_name: holds the name of the table where you want to create the non-clustered index.
• column_name: holds the name of the column that you want to define the non-clustered index on.

Example

Let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE  INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (20, 2),
);

Let us insert some values into the above-created table using the following query −

INSERT INTO CUSTOMERS VALUES
(7, ''Muffy'', ''24'', ''Indore'', 5500),
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(6, ''Komal'', ''22'', ''Hyderabad'', 9000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(4, ''Chaitali'', ''25'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(3, ''Kaushik'', ''23'', ''Kota'', 2000);

The table is successfully created in the SQL database.

Now, let us create a non-clustered index on a single column named ID using the following query −

CREATE NONCLUSTERED INDEX NON_CLU_ID
ON customers (ID ASC);

Output

On executing the above query, the output is displayed as follows −

Commands Completed Successfully.

Verification

Let us retrieve all the indexes that are created on the CUSTOMERS table using the following query −

EXEC sys.sp_helpindex @objname = N''CUSTOMERS

As we observe, we can find the column named ID in the list of indexes.

Now, retrieve the CUSTOMERS table again using the following query to check whether the table is sorted or not −

SELECT * FROM CUSTOMERS;

As we observe, the non-clustered index does not sort the rows physically instead, it creates a separate key-value structure from the table data.

Creating Non-Clustered Index on Multiple Columns

Instead of creating a new table, let us consider the previously created CUSTOMERS table. Now, try to create a non-clustered index on multiple columns of the table such as ID, AGE and SALARY using the following query −

CREATE NONCLUSTERED INDEX NON_CLUSTERED_ID
ON CUSTOMERS (ID, AGE, SALARY);

Output

The below query will create three separate non-clustered indexes for ID, AGE, and SALARY.

Commands Completed Successfully.

Verification

Let us retrieve all the indexes that are created on the CUSTOMERS table using the following query −

EXEC sys.sp_helpindex @objname = N''CUSTOMERS

As we observe, we can find the column names ID, AGE and SALARY columns in the list of indexes.

SQL Unique Indexes

The SQL Unique Index ensures that no two rows in the indexed columns of a table have the same values (no duplicate values allowed).

A unique index can be created on one or more columns of a table using the CREATE UNIQUE INDEX statement in SQL.

Following are the points to be noted before creating a Unique Index on a table −

• If the unique index is only created on a single column, the rows in that column will be unique.
• If a single column contains NULL in multiple rows, we cannot create a unique index on that column.
• If the unique index is created on multiple columns, the combination of rows in these columns will be unique.
• We cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row.

Syntax

Following is the syntax for creating a UNIQUE INDEX in SQL −

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ..., columnN);

Here,

• index_name is the name of the index that you want to create.
• table_name is the name of the table on which you want to create the index.
• (column1, column2, …., columnN) are the names of one or more columns on which the unique index is being created.

Example

First of all, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 4),
   PRIMARY KEY(ID)
);

Insert some values into the above-created table using the following query −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''kaushik'', ''23'', ''Kota'', 2000),
(4, ''Chaitali'', ''26'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''Hyderabad'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

Once the table is created, let us create a unique index for the column named SALARY in the CUSTOMERS table using the following query −

CREATE UNIQUE INDEX UNIQUE_ID ON CUSTOMERS (SALARY);

But, when we execute the above query, the output is obtained as follows −

ERROR 1062 (23000): Duplicate entry ''2000.00'' for key ''customers.UNIQUE_ID''

Since a unique index could not be created on SALARY column (due to duplicate values), let us create Unique Index on the NAME column of the same table, using the following query −

CREATE UNIQUE INDEX UNIQUE_ID ON CUSTOMERS (NAME);

Output

When we execute the above query, the output is obtained as follows −

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Let”s verify whether the unique index for the column NAME is created or not using the following query −

SHOW INDEX FROM CUSTOMERS;

As you observe the output below, you can find the column NAME along with the ID (PRIMARY KEY) in the list of indexes.

Updating with Duplicate Values

If we try to update the columns that have unique index with duplicate values, the database engine generates an error.

Example

Assume the previously created CUSTOMERS table and create a unique index on the column named ADDRESS using the following query −

CREATE UNIQUE INDEX ADD_UNIQUE_INDEX ON CUSTOMERS(ADDRESS);

Now, let us update the value in the column named ADDRESS with a duplicate (already existing data) value using the following query −

UPDATE CUSTOMERS SET ADDRESS = ''Mumbai'' WHERE ADDRESS = ''Delhi

Output

On executing the above query, the output is displayed as follows −

ERROR 1062 (23000): Duplicate entry ''Mumbai'' for key ''customers.ADD_UNIQUE_INDEX''

Creating a unique index on Multiple Fields

We can also create a unique index on multiple fields or columns of a table using the CREATE UNIQUE INDEX statement. To do so, you just need to pass the name of the columns (you need to create the index on) to the query.

Example

Instead of creating a new table, let us consider the previously created CUSTOMERS table. We will create a unique index on the columns NAME and AGE using the following query −

CREATE UNIQUE INDEX MUL_UNIQUE_INDEX ON CUSTOMERS(NAME, AGE);

Output

When we execute the above query, the output is obtained as follows −

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0.

Verification

Now, let us list all the indexes that are created on the CUSTOMERS table using the following query −

SHOW INDEX FROM CUSTOMERS;

As you observe you can find the column names NAME, and AGE along with the ID (PRIMARY KEY) in the list of indexes.

An index in a database is a data structure that helps to improve the speed of retrieving specific data from tables and views.

Data in a table is stored in the form of an unordered data structure called a “Heap”, where rows are placed without any specific order. Thus, when retrieving data from a table, the query optimizer must scan the entire table to locate the requested rows. This process can be time-consuming, especially when we are dealing with large tables. To speed up the data retrieval, SQL provides a data object called index that stores and organizes table data in a specific way, allowing faster data access.

SQL Clustered Indexes

A clustered index in SQL is a type of index that determines the physical order in which the data values will be stored in a table.

When a clustered index is defined on a specific column, during the creation of a new table, the data is inserted into that column in a sorted order. This helps in faster retrieval of data since it is stored in a specific order.

• It is recommended to have only one clustered index on a table. If we create multiple clustered indexes on the same table, the table will have to store the same data in multiple orders which is not possible.
• When we try to create a primary key constraint on a table, a unique clustered index is automatically created on the table. However, the clustered index is not the same as a primary key. A primary key is a constraint that imposes uniqueness on a column or set of columns, while a clustered index determines the physical order of the data in the table.

MySQL database does not have a separate provisions for Clustered and Non-Clustered indexes. Clustered indexes are automatically created when PRIMARY KEY is defined on a table. And when the PRIMARY KEY is not defined, the first UNIQUE NOT NULL key is treated as a Clustered index.

Syntax

Following is the syntax to create a clustered index with SQL Server −

CREATE INDEX index_name ON table_name(column_name [asc|desc])

Where,

• index_name: specifies the name you want to give to the index being created.
• column_name: specifies the column(s) that will be indexed in the order specified.
• asc|desc: specifies the order (asc – ascending, desc – descending) in which the data should be sorted. The default sorting order is ascending order.

Example

In this example, let us create a clustered index on a table in SQL Server. For that, we need to first create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE  INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (20, 2)
);

Now, insert some values into the CUSTOMERS table using the following query −

INSERT INTO CUSTOMERS VALUES
(7, ''Muffy'', ''24'', ''Indore'', 5500),
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(6, ''Komal'', ''22'', ''Hyderabad'', 9000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(4, ''Chaitali'', ''25'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(3, ''Kaushik'', ''23'', ''Kota'', 2000);

The table is successfully created in the SQL Server database.

Now, let us create a clustered index on the column named ID using the following query −

CREATE CLUSTERED INDEX CLU_ID ON CUSTOMERS(ID ASC);

Output

On executing the above query, the output is displayed as follows −

Commands Completed Successfully.

Verification

To verify if the clustered index is defined on ID column, check whether the records of CUSTOMERS table are sorted by retrieving them using the following query −

SELECT * FROM CUSTOMERS;

The records of the table are sorted in ascending order based on values in the column named ID.

Creating Clustered Index on Multiple Columns

With the following example, let us understand how clustered index works when it is created on multiple columns of a table.

Instead of creating a new table, consider the previously created CUSTOMERS table and define a clustered index on multiple columns of this table, such as AGE and SALARY, using the following query −

CREATE CLUSTERED INDEX MUL_CLUS_ID
ON CUSTOMERS (AGE, SALARY ASC);

Output

When we execute the above query, the output is obtained as follows −

Commands Completed Successfully.

Verification

Now, let us verify whether the values in the columns AGE and SALARY is sorted or not −

SELECT * FROM CUSTOMERS;

As we can observe in the table below, the records are sorted only based on the values in AGE column and not with the values in SALARY column. So, it is recommended to have only one clustered index on a table.

The SQL Show Index Statement

The SHOW INDEX is the basic SQL statement to retrieve the information about the indexes that have been defined on a table. However, the SHOW INDEX statement only works on MySQL RDBMS and is not a valid statement in the SQL Server.

To list the indexes created on a table in SQL Server, a system stored procedure sp_helpindex is used.

The result-set obtained from querying the SHOW INDEX statement on a MySQL table contains the index information.

Syntax

Following is the syntax of the SHOW INDEX statement in MySQL −

SHOW INDEX FROM table_name;

Example

Following example demonstrates the working of SHOW INDEX statement in MySQL. First, create a table with the name CUSTOMERS in the MySQL database using the CREATE query below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (20, 2),
   PRIMARY KEY (ID)
 );

Let us now insert some values into the above created table using the following query −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''Kaushik'', ''23'', ''Kota'', 2000),
(4, ''Chaitali'', ''25'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''Hyderabad'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

Once the data is inserted, create an index for the column NAME in the CUSTOMERS table using the following query −

CREATE INDEX INDEX_NAME ON CUSTOMERS(NAME);

Now, you can list all the indexes that are defined on the CUSTOMERS table using the following query −

SHOW INDEX FROM CUSTOMERS;

Output

On executing the above query, the output is displayed as follows −

Showing Indexes in SQL Server

In SQL server, the system stored procedure sp_helpindex is used to retrieve the information about the indexes that have been defined on a table. It returns the result as a table that contains detailed information about each index, including the name, type, and columns.

Syntax

Following is the basic syntax to list indexes defined on a table in SQL Server −

sp_helpindex [ @objname = ] ''name''

Here, [ @objname = ] ”name” specifies the name of the table for which the index information is being retrieved. The index information includes −

• index_name is the names of the columns that are included in index.
• index_description is the brief description of the index such as the type of index (like clustered or non-clustered).
• index_keys is the keys that are included in the index.

Example

CREATE INDEX INDEX_NAME on CUSTOMERS(NAME);

Now, let us list all the indexes that are created on the CUSTOMERS table using the system stored procedure sp_helpindex as shown below −

EXEC sys.sp_helpindex @objname = N''CUSTOMERS

Output

On executing the above query, the output is displayed as follows −