SQL/RDBMS is one of the most widely used databases to date, and therefore SQL skills are important in most work roles.
In this SQL interview questions article, I will share with you some of the most frequently asked questions on SQL (Structured Query Language). This article is the perfect guide for you to learn all concepts related to SQL, Oracle, MS SQL Server, and MySQL databases. In this topic, all the examples are related to SQL Server.
I am keeping 10 questions and answers in each part, So Please read all 10 parts of this SQL Interview Questions article and get ready to crack the SQL interview for beginners or experience level.
Contents
What is a stored procedure?
A Stored Procedure is a collection or a group of precompiled T-SQL statements is stored together in the database.
It will reduce the network traffic because of the precompilation.
Stored procedures are created to perform one or more DML operations on a database. It is nothing but a set of SQL statements that accepts some input as parameters and performs some function and may or may not receive value.
Why do you use a Stored Procedure ?
- If we use the stored procedure then we will get below advantage.
- It will reduce the network traffic.
- It will also reduce the execution time due to precompiled.
- If will not allow for SQL Injection.
- Code will be stored in the server only so indirectly We will achieve the security.
SQL Interview Questions & Answer – Best TOP 10 (Day – 1)
How can I write a new stored procedure with an appropriate example?
Please find below syntax to write a stored procedure in SQL server.
Syntax
CREATE/ALTER PROCEDURE SP_Name
AS
BEGIN
——
—-Your Code
——
END
As per the above syntax, We are using create for creating a new stored procedure and alter is used for modified the existing stored procedure.
CREATE PROCEDURE SP_EMPLOYEEDETAILS
AS
BEGIN
SELECT * FROM TBL_EMP
END
SQL Interview Questions & Answer – Best TOP 10 (Day – 2)
What are the advantages of using stored procedures in SQL servers?
- Better Performance –
The procedure calls are quick and efficient as stored procedures are compiled once and stored in executable form. Hence the response is quick. The executable code is automatically cached, hence lowers the memory requirements. - Higher Productivity –
Since the same piece of code is used again and again so, it results in higher productivity. - Ease of Use –
To create a stored procedure, one can use any Java Integrated Development Environment (IDE). Then, they can be deployed on any tier of network architecture. - Scalability –
Stored procedures increase scalability by isolating application processing on the server. - Maintainability –
Maintaining a procedure on a server is much easier than maintaining copies on various client machines, this is because scripts are in one location. - Security –
Access to the Oracle data can be restricted by allowing users to manipulate the data only through stored procedures that execute with their definer’s privileges.
What are the advantages of function over the stored procedure in SQL server?SQL Interview Questions
- Reuseble the code due to stored in database server.
- It is very easy to maintain a stored procedure
- It is preventing the SQL Injection Attacks becasue encrypted code.
- It is also reduce the execution time and network trafic due to precompiled.
How can I pass a parameter to the stored procedure in SQL Server?
Let us create a SQL Server stored procedure that accepts the input parameters and processes the records based on the input parameter. SQL Interview Questions
Following is the example of a stored procedure that accepts the parameter.
CREATE PROCEDURE SP_EMPDETAILS
@FIRSTNAME VARCHAR(50),
@LASTNAME VARCHAR(50)
AS
BEGIN
SELECT * FROM TBL_EMP
WHERE FNAME = @FIRSTNAME AND LNAME = @LASTNAME
END
How can I excute a stored procedure in SQL Server ?
Please look into the below code snippet. SQL Interview Questions
EXEC SP_EMPLOYEEDETAILS
-- Or we can write
EXECUTE SP_EMPLOYEEDETAILS
How can I pass a output parameter to stored procedure in SQL Server ?
There are two different methods to call stored procedures. Let us see them here.
Method -1
-- EXECUTE SP PROPER ORDER OF PARAMETERS
EXEC SP_EMPDETAILS 'FNAME', 'SNAME'
-- EXECUTE SP IMPROPER ORDER OF PARAMETERS
EXEC SP_EMPDETAILS 'SNAME', 'FNAME'
When you run the above script, it will show us the result where the order of the columns is changed as SP assigns in second execution of SP so the result is invalid. SQL Interview Questions
Method – 2 SQL Interview Questions
In this method when we execute the stored procedure, we also include the variable to which we want to assign parameter. This way is the certain way to assign value to parameter inside a SP.
EXEC SP_EMPDETAILS @FIRSTNAME = 'FNAME', @LASTNAME = 'SNAME'
EXEC SP_EMPDETAILS @LASTNAME = 'SNAME', @FIRSTNAME = 'FNAME'
How can I excute a stored procedure with output parameter in SQL Server ?
Please follow the below example. SQL Interview Questions
-- Create a stored procedure with output parameter
CREATE PROCEDURE SP_EMPDETAILS
@DEPTID INT,
@DEPTCOUNT INT OUT
AS
BEGIN
SELECT @DEPTCOUNT = COUNT(*) FROM TBL_EMP
WHERE DEPTID= @DEPTID
END
Calling SP
DECLARE @DEPTCOUNT int
EXECUTE SP_EMPDETAILS 23, @DEPTCOUNT
IF(@DEPTCOUNT is null)
PRINT '@@DEPTCOUNT is null'
ELSE
PRINT @DEPTCOUNT
SQL Interview Questions & Answer – Best TOP 10 (Day – 3)
What is a sp_depends ? / How can I able view the dependencies of the stored procedure ?
Syntax:-
sp_depends SP_Name
- The above syntax is used to view the dependencies of the stored procedure.
- It is a system stored procedure.
- It can able to view the dependencies of other objects like Tables, Views, etc.
SQL Interview Questions & Answer – Best TOP 10 (Day – 1)
What is disadvantages/Drawbacks of using stored procedure in sql server ?
- Testing –
Testing of a logic which is encapsulated inside a stored procedure is very difficult. Any data errors in handling stored procedures are not generated until runtime. - Debugging –
Depending on the database technology, debugging stored procedures will either be very difficult or not possible at all. Some relational databases such as SQL Server have some debugging capabilities. - Versioning –
Version control is not supported by the stored procedure. - Cost –
An extra developer in the form of DBA is required to access the SQL and write a better stored procedure. This will automatically incur added cost. - Portability –
Complex stored procedures will not always port to upgraded versions of the same database. This is specially true in case of moving from one database type(Oracle) to another database type(MS SQL Server).
