Common Table Expressions - 1

Introduction to Common Table Expressions (CTEs)

In this session, the concept of common table expressions (CTEs) is discussed. A CTE is essentially a
temporary result set that can be referred to with a SELECT, INSERT, UPDATE, or DELETE statement.
CTEs help simplify queries and make it easier to understand the code by breaking it into simpler
parts.

Creating and Using Temporary Tables

To avoid impacting the main table during demonstrations, a temporary table is created as a replica of
the employees table. This is done so that changes can be made without affecting the original data.

sql Code Sample

select * from employees

sql Code Sample

select * into #temp1 from employees

sql Code Sample

select * from #temp1

Defining a Common Table Expression (CTE)

To define a CTE, use the WITH keyword followed by the CTE name, the AS keyword, and the query in
parentheses. The CTE can then be referenced immediately in the following statement.

sql Code Sample

with CTE as (

select * from #temp1

select * from CTE

Important Note on CTE Usage

Whenever using SELECT, INSERT, UPDATE, or DELETE statements that refer to a CTE, the statement
must be written immediately after the CTE definition. Both the CTE definition and the referencing
statement must be executed together, or an error will occur.

Example: CTE with Specific Columns and Conditions

A CTE can be named as desired. In this example, only the employee ID and first name columns are
selected for employee IDs 2 and 4.

sql Code Sample

with test_CTE as (

select employeeID, firstName from #temp1

where employeeID in (2, 4)

)

select * from test_CTE

Example: Inserting Data Using a CTE

A CTE can be used to insert data into a new temporary table. In this example, records with employee
IDs 1, 2, and 3 are inserted into a new table named #temp2.

sql Code Sample

with common_table_expression as (

select * from #temp1

where employeeID in (1, 2, 3)

select * into #temp2 from common_table_expression

sql Code Sample

select * from #temp2

Example: Updating Data Using a CTE

A CTE can be used to update records in a table. In this example, employee IDs 2, 4, and 6 in #temp1
are updated to 101.

sql Code Sample

with CTE_1 as (

select * from #temp1 where employeeID in (2, 4, 6)

update #temp1

set employeeID = 101

where employeeID in (select distinct employeeID from CTE_1)

sql Code Sample

select * from #temp1

Example: Deleting Data Using a CTE

A CTE can be used to delete records from a table. In this example, the record with employee ID 1 is
deleted from #temp1.

sql Code Sample

with CTE_2 as (

select * from #temp1 where employeeID = 1

)
delete from #temp1

where employeeID in (select distinct employeeID from CTE_2)

sql Code Sample

select * from #temp1

Example: Inserting Duplicate Records Using a CTE

A CTE can also be used to insert duplicate records. In this example, all records from #temp1 with
employee ID 101 are inserted again into #temp1, resulting in duplicate entries.

sql Code Sample

with CTE_3 as (

select * from #temp1 where employeeID = 101

insert into #temp1

select * from CTE_3

sql Code Sample

select * from #temp1

Conclusion

In this session, the use of common table expressions (CTEs) with SELECT, INSERT, UPDATE, and
DELETE statements was demonstrated. In the next session, multiple CTEs and recursive CTEs will be
discussed.

Key Takeaways

 Common Table Expressions (CTEs) are temporary result sets that can be referenced with
SELECT, INSERT, UPDATE, or DELETE statements.

 CTEs are defined using the WITH keyword and must be immediately followed by the
statement that references them.

 CTEs help simplify queries and make code easier to understand by breaking them into
simpler parts.
 CTEs can be used to select, insert, update, and delete data in SQL Server, and each operation
was demonstrated with practical examples.

Common Table Expressions - 2

Introduction to Multiple Common Table Expressions (CTEs)

In this session, we will explore how to define and use multiple Common Table Expressions (CTEs) in
SQL. We will see how to use a single WITH keyword to define several CTEs and how these can be
utilized with SELECT, INSERT, UPDATE, and DELETE statements.

Creating a Temporary Table for Demonstration

To avoid modifying the main table, we first create a replica of the employees table. This temporary
table will be used for all subsequent operations.

sql Code Sample

select * from employees

sql Code Sample

select * into #1 from dbo.employees

Defining Multiple CTEs with a Single WITH Keyword

We can define multiple CTEs by using the WITH keyword once and separating each CTE definition
with a comma.

sql Code Sample

with CTE1 as (

select * from #1 where employee_id in (1, 2)

),

CTE2 as (

select * from #1 where employee_id in (3, 4)

Using CTEs in SELECT Statements

After defining the CTEs, we can reference them in SELECT statements. Here, we use UNION ALL to
combine the results from both CTEs.

sql Code Sample

select * from CTE1

union all

select * from CTE2

Inserting Combined CTE Data into a New Temporary Table

We can insert the combined output of multiple CTEs into a new temporary table using SELECT INTO.

sql Code Sample

with CTE3 as (

select employee_id, first_name from #1 where employee_id = 1

),
CTE4 as (

select employee_id, first_name from #1 where employee_id = 3

select * into #2 from (

select * from CTE3

union all

select * from CTE4

)X

Inserting Data into an Existing Table

If the destination table already exists, we use the INSERT INTO statement instead of SELECT INTO.

sql Code Sample

insert into #2

select * from (

select * from CTE3

union all

select * from CTE4

)X

Deleting Records Using Multiple CTEs

We can use CTEs to identify records for deletion. For example, deleting from the temporary table
where employee IDs match those in the CTEs.

sql Code Sample

with CTE3 as (

select employee_id, first_name, salary from #1 where employee_id = 2

),

CTE4 as (

select employee_id, first_name, salary from #1 where employee_id = 4

delete from #1

where employee_id in (

select distinct employee_id from CTE3

union all
 select distinct employee_id from CTE4

Updating Records Using Multiple CTEs

We can also use CTEs to update records. For example, updating all employee IDs except those found
in the CTEs.

sql Code Sample

with CTE3 as (

select employee_id, first_name, salary from #1 where employee_id = 9

),

CTE4 as (

select employee_id, first_name, salary from #1 where employee_id = 10

update #1

set employee_id = 100

where employee_id not in (

select distinct employee_id from CTE3

union all

select distinct employee_id from CTE4

Conclusion

Multiple CTEs can be defined using a single WITH keyword and can be used with SELECT, INSERT,
UPDATE, and DELETE statements. Temporary tables help in demonstrating these operations without
affecting the main data. Using UNION ALL allows combining results from multiple CTEs for further
processing.

Key Takeaways

 Multiple Common Table Expressions (CTEs) can be defined using a single WITH keyword by
separating each CTE with a comma.

 CTEs can be used with SELECT, INSERT, UPDATE, and DELETE statements for flexible data
manipulation.

 Temporary tables are useful to avoid altering the main table during demonstrations of data
modification operations.

 Combining CTEs with UNION ALL allows merging results from multiple CTEs for further
operations.
Recursive Common Table Expressions

Introduction to Recursive Common Table Expressions

In this video, we will discuss the concept of recursive common table expressions. A recursive CTE is a
common table expression that refers to itself. We will use an example to understand this concept
better by finding the factorial of a given number using SQL code.

Structure of a Recursive CTE

A recursive common table expression has two parts:

 The anchor query

 The recursive query

These two parts are joined using the UNION ALL operator.

sql Code Sample

with [ourCTE] as (

select 1 as n

union all

select n + 1 from [ourCTE] where n <= 4

select * from [ourCTE];

Execution Flow of Recursive CTE

First, the anchor query is executed, and its output serves as input to the recursive query. For
example, the anchor query outputs n=1n=1. This value is then passed to the recursive query, which
checks if n≤4n≤4. If true, it computes n+1n+1 and continues recursively until the condition fails.

Step-by-Step Evaluation

 Anchor query output: n=1n=1

 Recursive query with n=1n=1: n+1=2n+1=2

 Recursive query with n=2n=2: n+1=3n+1=3

 Recursive query with n=3n=3: n+1=4n+1=4

 Recursive query with n=4n=4: n+1=5n+1=5

 Recursive query with n=5n=5: Condition fails (5>45>4), recursion stops

The final output is the sequence: 1, 2, 3, 4, 5.

Using Recursive CTE to Compute Factorial

To compute the factorial of a number, multiply the number by all positive integers less than it down
to 1. For example:

 3!=3×2×13!=3×2×1
  5!=5×4×3×2×15!=5×4×3×2×1

This can be achieved by generating the sequence using a recursive CTE and then multiplying all the
resulting numbers.

Mathematical Approach to Product Calculation

To multiply all numbers in a column, use the logarithm and exponent functions. The property used is:

log⁡a(m)+log⁡a(n)=log⁡a(mn)loga(m)+loga(n)=loga(mn)

Taking the exponent returns the product:

alog⁡a(mn)=mnaloga(mn)=mn

sql Code Sample

select exp(sum(log(n))) from [ourCTE];

Adjusting the Factorial Calculation

To find the factorial of a different number, adjust the condition in the recursive query. For example,
to compute 3!3!, set the condition to n≤2n≤2 (since the sequence will be 1, 2, 3).

sql Code Sample

with [ourCTE] as (

select 1 as n

union all

select n + 1 from [ourCTE] where n <= 2

select exp(sum(log(n))) from [ourCTE];

Reference Queries

You can use the following queries for reference. The first query gives the factorial of 5, and you can
adjust the number as needed by changing the condition in the recursive query.

sql Code Sample

with [ourCTE] as (

select 1 as n

union all

select n + 1 from [ourCTE] where n <= 4

select exp(sum(log(n))) from [ourCTE];

sql Code Sample

with [ourCTE] as (
 select 1 as n

union all

select n + 1 from [ourCTE] where n <= 4

select * from [ourCTE];

Conclusion

Recursive common table expressions allow for iterative computations in SQL, such as generating
sequences and calculating factorials. By adjusting the recursive condition, you can compute the
factorial of any number. The product of a sequence can be computed using the sum of logarithms
and the exponent function.

Key Takeaways

 Recursive Common Table Expressions (CTEs) refer to themselves and are useful for iterative
computations in SQL.

 A recursive CTE consists of an anchor query and a recursive query, joined using the UNION
ALL operator.

 Recursive CTEs can be used to generate sequences and compute factorials by leveraging
mathematical properties and SQL functions.

 The product of a sequence can be computed using the sum of logarithms and the exponent
function in SQL.

Stored Procedure in MS SQL Server

Introduction to Stored Procedures

A stored procedure is a group of Transact-SQL statements that can be reused. If there is a piece of
code that needs to be used repeatedly, stored procedures enhance reusability.

Creating a Database and Table

First, a database is created for the analysis. The following SQL command is used to create a database
named testDB.

sql Code Sample

CREATE DATABASE testDB

After creating the database, select it with the following command:

sql Code Sample

USE testDB
A table named employees is created and populated with data for analysis. The code for this is
provided in the resource section and can be copied to create the table.

Viewing Data with SELECT

To view the data in the employees table, use the following SQL statement:

sql Code Sample

SELECT * FROM employees

Creating a Stored Procedure

To achieve the same output using a stored procedure, wrap the query inside a stored procedure. The
syntax is as follows:

sql Code Sample

CREATE PROCEDURE SP_test

AS

BEGIN

SELECT * FROM employees

END

After executing the above code, the stored procedure SP_test is created.

Calling a Stored Procedure

To call the stored procedure, you can simply write its name or use the EXECUTE command.

sql Code Sample

SP_test

Alternatively, you can use the following commands to call the stored procedure:

sql Code Sample

EXECUTE SP_test

EXEC SP_test

Any of these methods will call the stored procedure and produce the same output.

Real-World Use of Stored Procedures

In real-world scenarios, stored procedures may consist of thousands of lines of code, containing
multiple business logics. Storing such code in a stored procedure allows for easy reuse by simply
calling its name.

Alternative Syntax for Creating Procedures

You can use either CREATE PROCEDURE or CREATE PROC to create a stored procedure. The following
example demonstrates creating a procedure that selects only specific columns.

sql Code Sample

CREATE PROC SP_test_1

AS

BEGIN

SELECT first_name, last_name FROM employees

END

After executing the above code, the stored procedure SP_test_1 is created. To call it, simply write its
name and execute.

sql Code Sample

SP_test_1

Altering a Stored Procedure

To make changes to an existing stored procedure, use the ALTER PROCEDURE statement. For
example, to add the employee_id column to the output of SP_test_1, use the following code:

sql Code Sample

ALTER PROCEDURE SP_test_1

AS

BEGIN

SELECT employee_id, first_name, last_name FROM employees

END

After executing the above code, calling SP_test_1 will now return the updated output with the new
column included.

Conclusion

This session covered the basics of creating and altering stored procedures in MS SQL Server. Stored
procedures with input and output parameters will be discussed in upcoming sessions.

Key Takeaways

 Stored procedures are reusable groups of Transact-SQL statements in MS SQL Server.

 You can create a stored procedure using either CREATE PROCEDURE or CREATE PROC.

 Stored procedures can be called by their name or with the EXECUTE command.

 Existing stored procedures can be modified using the ALTER PROCEDURE statement.

Views in MS SQL Server

Introduction to Views in MS SQL Server

In this session, we will discuss the concept of views in MS SQL Server. We will use the employees
table from the test DB, which was created in the previous session on stored procedures.

To begin, we can display the data in the employees table using a simple select statement.

sql Code Sample

select * from employees

Since we will also discuss updating views, let us take a backup of this table.

sql Code Sample

select * into m_backup from employees

After executing the above statement, five rows are impacted. Now, running a select statement on the
backup table displays its data.

sql Code Sample

select * from m_backup

Understanding Views

A view is a virtual table. It does not occupy any physical space and is created based on a table. A view
is essentially a stored SQL query.

There are certain advantages of using views:

 Views help in reducing the complexity of the code.

 Views help in implementing security.

Creating a View

Let us discuss the syntax for creating a view. We will create a view named view_1.

sql Code Sample

create view view_1 as

select * from m_backup

After executing the above code, the view named view_1 is created successfully. To see the contents
of view_1, we can run a select statement just as we would for a table.

sql Code Sample

select * from view_1

Implementing Security with Views

Currently, view_1 displays all details of the employees, including salary, department ID, last name,
first name, and more. Suppose there are employees in your organization to whom you do not want
to show the salary data, but you want to share other details for some purpose. You can implement
security by creating a view that excludes the salary column and granting access to that view. If users
have access to the base table, they can see all columns, but with a view, you can restrict access to
specific columns.
Similarly, you can implement row-level security. For example, if you want to share data only for
employees with IDs three, four, and five, you can create a view that includes only those records and
grant access to that view.

Creating a View with Selected Columns

Let us create a view that contains only specific columns: employee ID, first name, last name, email,
department ID, and hire date. We want the user to have access only to these six columns.

sql Code Sample

create view view_2 as

select employee_id, first_name, last_name, email, department_id, hire_date from m_backup

After executing the above code, view_2 is created successfully. Running a select statement on view_2
displays the data for all columns except salary, demonstrating column-level security.

sql Code Sample

select * from view_2

Reducing Code Complexity with Views

Views can also help reduce code complexity, especially when working with multiple tables and joins.
If there is a complex piece of code, you can write it inside a view. Then, to access the results, you
only need to run a simple select statement on the view.

This is helpful for team members who may not be proficient with complex queries or joins. You can
create the complex code, wrap it in a view, and grant them access to the view so they can see the
relevant data easily.

Updating Data Through a View

Let us discuss updating a view and its impact on the base table. When we created view_1, it was
based on the employee backup table. If we update data through the view, it can impact the base
table.

sql Code Sample

update view_1 set employee_id = 100

After executing the above update, all employee IDs in view_1 become 100. Running a select
statement on the employee backup table shows that the employee IDs are updated in the base table
as well. Thus, updating a view can impact the underlying table, as a view is a virtual table built on top
of a base table.

sql Code Sample

select * from m_backup

You should be careful when updating data through a view, as it can affect the base table.

Dropping a View

To drop a view, use the drop statement followed by the view name.

sql Code Sample

drop view view_2

After dropping view_2, attempting to select from it will result in an error, as the object no longer
exists.

Conclusion

This session covered creating, updating, and dropping views in MS SQL Server, as well as how views
help in reducing code complexity and implementing security.

Key Takeaways

 Views in MS SQL Server are virtual tables created from SQL queries and do not occupy
physical space.
 Views help reduce code complexity and implement security by restricting access to specific
columns or rows.

 Updating data through a view can impact the underlying base table, so caution is required.

 Views can be created, updated, and dropped using standard SQL statements similar to those
used for tables.

Indexes in MS SQL Server

Introduction to Indexes in Microsoft SQL Server

In this session, the concept of indexes in Microsoft SQL Server is discussed. An analogy is drawn
between searching for a chapter in a book with and without an index page, highlighting the
importance of indexes for efficient data retrieval.

The Role of Indexes

If a book of 500 pages does not have an index, one must manually search each page to find a
particular chapter. This process is time-consuming and reduces efficiency. However, if an index is
available, one can directly access the required page, saving time and enhancing efficiency. The same
principle applies to Microsoft SQL Server.

When SQL Server is instructed to fetch or retrieve data from a table, it performs a scan. Scanning the
entire table reduces performance and consumes more time. To address this, indexes are defined on
tables or views. With indexes, SQL Server can retrieve data without scanning the entire table,
resulting in faster data retrieval.

Defining Indexes

Indexes are defined on one or more columns of a table or view. When defining an index, the order in
which the column data is stored in the index can be specified. If the data is sorted in the index, SQL
Server can efficiently identify and retrieve the required records.

Creating an Index: Example

Let us consider an example of creating an index on a table. Suppose the testdb database contains a
table named employees. To view the data in this table, the following SQL statement is used:
sql Code Sample

SELECT * FROM dbo.employees

Suppose there is a need to create an index on the salary column of this table. The syntax for creating
an index is as follows:

sql Code Sample

CREATE INDEX ix_1 ON dbo.employees (salary DESC)

After executing this statement, the index will be created on the employees table. To verify the
creation of the index, one can navigate to the database, expand the tables, and then expand the
indexes node to see the newly created index named ix_1. The index is shown as non-unique and
non-clustered. The difference between clustered and non-clustered indexes will be discussed in
future sessions.

Creating an Index on Multiple Columns

It is possible to create an index on multiple columns. For example, to create an index on

the first_name and last_name columns, the following statement is used:

sql Code Sample

CREATE INDEX ix_2 ON dbo.employees (first_name, last_name)

After executing this statement, the index will be created. To view the new index, refresh the table
and expand the indexes node. The new index, ix_2, will be visible.

Dropping an Index

To drop an index, the following statement can be used. For example, to drop the index ix_1 from
the employees table:

sql Code Sample

DROP INDEX dbo.employees.ix_1

After executing this statement, the index will be dropped. Refresh the table and check the indexes
node to confirm that ix_1 is no longer present.

Conclusion
This session provided an understanding of what indexes are in Microsoft SQL Server, how to create
and drop them, and how they improve data retrieval performance. The differences between
clustered and non-clustered indexes will be discussed in upcoming sessions.

Key Takeaways

 Indexes in SQL Server improve data retrieval efficiency by allowing direct access to data,
avoiding full table scans.

 Indexes can be created on one or multiple columns, and the order (ascending or descending)
can be specified.

 The process of creating, viewing, and dropping indexes involves specific SQL statements.
  The difference between clustered and non-clustered indexes will be discussed in future
sessions.

Clustered Index

Introduction to Clustered Indexes

In this session, we will further discuss the concept of clustered indexes. First, we will create a
database named TestIndex. Then, we will create a table within this database and discuss indexes
specifically in this context.

I will execute the statement to create the database. Once the database is created, we will select it
using the USE TestIndex command.

Next, we will create a new table named students within this database. Before creating the table, I
will drop it if it already exists using DROP TABLE IF EXISTS students to avoid conflicts.

The students table will have the following columns: ID, name, age, and gender. I will execute the
create table statement now.

To insert records into the students table, I will execute an insert statement. The code for this will be
provided in the resource section for your reference. After execution, the records will be inserted
successfully.
Running a simple SELECT statement on the students table now shows the data in the order it was
inserted. The data is not sorted in ascending or descending order by any column at this point.

Creating a Clustered Index

What happens if we create a clustered index on this table using the ID column? I will execute the
statement:

CREATE CLUSTERED INDEX IX1_1 ON students(ID);

Here, IX1_1 is the name of the clustered index, students is the table name, and ID is the column used
for the index.

After creating the clustered index, if we run a SELECT statement on the students table, the data is
now sorted in ascending order by the ID column. This is because a clustered index determines the
physical order of the data in the table.

Since a clustered index determines the physical order of data, only one clustered index can exist on a
given table. Attempting to create another clustered index without dropping the existing one will
result in an error.

For example, trying to create a clustered index on the ID column in descending order without
dropping the first index will cause an error.

Dropping and Recreating Clustered Index

To create a new clustered index, we must first drop the existing one. I will drop the clustered index
named IX1_1 on the students table using:

DROP INDEX IX1_1 ON students;

After dropping, the command completes successfully.

Now, I will create a new clustered index named IX_2 on the students table using the ID column, but
this time sorting in descending order:

CREATE CLUSTERED INDEX IX_2 ON students(ID DESC);

This command executes successfully.

Running a SELECT * FROM students now shows the data sorted in descending order by
the ID column, as specified in the clustered index.

Composite Clustered Index

Clustered indexes can also be created using multiple columns, known as composite clustered
indexes. Although only one clustered index can exist per table, it can consist of multiple columns.

To create a composite clustered index, we must first drop the existing clustered index IX_2:

DROP INDEX IX_2 ON students;

After dropping, the command completes successfully.

Now, I will create a clustered index named YX3 on the students table using the
columns gender and age. The data will be sorted first by gender in descending order, then by age in
ascending order:

CREATE CLUSTERED INDEX YX3 ON students(gender DESC, age ASC);

This command executes successfully.

Running SELECT * FROM students now shows the data sorted first by gender in descending order,
and within each gender, sorted by age in ascending order. For example, all males appear first sorted
by age ascending, followed by females sorted similarly.

Viewing Clustered Index in Object Explorer

You can view the clustered index in the Object Explorer by expanding the database TestIndex, then
expanding Tables, selecting the students table, and expanding Indexes. Here, you will see the
clustered index named YX3 listed.

Conclusion

In the next session, we will discuss non-clustered indexes. I hope this session was helpful.

Key Takeaways

 A clustered index determines the physical order of data in a table, and only one clustered
index can exist per table.

 Clustered indexes can be created on one or multiple columns, known as composite clustered
indexes.

 Creating a clustered index sorts the data physically according to the specified column(s) and
order (ascending or descending).

 To create a new clustered index, the existing one must be dropped first, as only one clustered
index is allowed per table.
Non-Clustered Indexes: Concepts, Creation, and Differences

Introduction to Non-Clustered Indexes

In this session, the concept of non-clustered indexes is discussed. The clustered index concept has
already been covered previously. The session begins by examining the data available in
the students table.

sql Code Sample

SELECT * FROM Students;

At this point, there are no indexes on the table. The simple SELECT statement is executed as
discussed in the previous session.

Creating a Non-Clustered Index

To create a non-clustered index, the CREATE statement is used. This is similar to creating a clustered
index, but with the specification of NONCLUSTERED.

sql Code Sample

CREATE NONCLUSTERED INDEX Yx2 ON Students(ID);

The command completes successfully, and now there is one non-clustered index on the table.
Another non-clustered index can also be created using the gender and age columns.

sql Code Sample

CREATE NONCLUSTERED INDEX Yx3 ON Students(Gender, Age);

This demonstrates that more than one non-clustered index can exist on a table. In contrast, only one
clustered index can be created because it determines the physical order of the data in the table.
Non-clustered indexes are stored separately and require additional disk space.

Example: How Non-Clustered Indexes Work

Consider a table where a non-clustered index is created on the ID column. Using this ID, which is
sorted in ascending order, it is possible to refer back to the data in the table.

Performance Comparison: Clustered vs Non-Clustered Indexes

Clustered indexes are slightly faster than non-clustered indexes. If there are columns not present in
the non-clustered index, SQL Server must refer back to the table, requiring an additional lookup. This
makes non-clustered indexes slower in such cases.

sql Code Sample

SELECT Name, Age, Gender FROM Students WHERE ID = 2;

In this example, for ID = 2, an additional lookup is required because the columns Name, Age,
and Gender are not available in the index itself. Therefore, the non-clustered index requires more
time, and clustered indexes are generally faster.

Dropping a Non-Clustered Index

To drop a non-clustered index, the same syntax is used as for dropping a clustered index.
sql Code Sample

DROP INDEX Yx2 ON Students;

Executing this command will drop the non-clustered index from the table.

Summary of Differences

 More than one non-clustered index can exist on a table, but only one clustered index is
allowed.

 The clustered index determines the physical order of data in a table, while the non-clustered
index does not.

 Clustered indexes are faster because non-clustered indexes may require additional lookups
for columns not present in the index.

 Non-clustered indexes require additional disk storage space, whereas clustered indexes are
created on the same table.

This concludes the discussion on clustered and non-clustered indexes.

Key Takeaways

 Non-clustered indexes are created separately from the table and do not determine the
physical order of data.

 Multiple non-clustered indexes can exist on a single table, unlike clustered indexes.
 Non-clustered indexes require additional disk space and may be slower than clustered
indexes due to extra lookups.

 Dropping a non-clustered index uses the same syntax as dropping a clustered index.