SQL Most Frequently asked Interview Questions

SQL is a standard language for accessing and manipulating databases. From data storage to data science, SQL is playing the vital role in managing and maintaining the important data.

1. What is the difference between “Stored Procedure” and "Function"?

A procedure can have both input and output parameters, but a function can only
have input parameters.

Inside a procedure we can use DML (INSERT/UPDATE/DELETE) statements. But
inside a function we can’t use DML statements.

We can't utilize a Stored Procedure in a Select statement. But we can use a function
ina Select statement.

We can use a Try-Catch Block in a Stored Procedure but inside a function we can't
use a Try-Catch block.

We can use transaction management in a procedure but we can't in a function.
We can't join a Stored Procedure but we can join functions,

Stored Procedures cannot be used in the SQL statements anywhere in the
WHERE/HAVING/SELECT section. But we can use a function anywhere.

A procedure can return 0 or n values (max 1024). Buta function can return only 1
value that is mandatory.

A procedure can't be called from a function but we can call a function froma
procedure.

2.What is difference between "Clustered Index" and “Non Clustered Index”?

A Clustered Index physically stores the data of the table in the order of the keys
values and the data is resorted every time whenever a new value is inserted or a
value is updated in the column on which it is defined, whereas a non-clustered index
creates a separate list of key values (or creates a table of pointers) that points
towards the location of the data in the data pages.

A Clustered Index requires no separate storage than the table storage. It forces the
rows to be stored sorted on the index key whereas a non-clustered index requires
separate storage than the table storage to store the index information.

A table with a Clustered Index is called a Clustered Table. Its rows are stored in a B-
Tree structure sorted whereas a table without any clustered indexes is called a non-
clustered table. Its rows are stored in a heap structure unsorted.

The default index is created as part of the primary key column as a Clustered Index.
In a Clustered Index, the leaf node contains the actual data whereas in a non-
clustered index, the leaf node contains the pointer to the data rows of the table.

A Clustered Index always has an Index Id of 1 whereas non-clustered indexes have
Index Ids > 1,

A Table can have only 1 Clustered Index whereas prior to SQL Server 2008 only 249
non-clustered indexes can be created. With SQL Server 2008 and above 999 non-
clustered indexes can be created,

A Primary Key constraint creates a Clustered Index by default whereas A Unique
Key constraint creates a non-clustered index by default.