Interview Questions

SQL Server Interview Questions Series – Part 1

1. What is SQL Temporary Table?

A temporary table is a table which developer creates at runtime only for the scope of a query or for the scope of the connection. You can do all CRUD operations on the table.

There are two kinds of temporary tables:

a. Local temporary table

b. Global temporary table

Local temporary table: This table is available only to the scope of a query. It automatically deletes when a query ends. For making a local temporary table you need to put single hash (#) before the table name.

CREATE TABLE #localTable
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name VARCHAR(15)
)

Global temporary table: This global table is available for the entire connection to the database. It automatically deletes when you close the connection. For making a global temporary table you need to put two hashes(##) before the table name.

CREATE TABLE ##globalTable
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name VARCHAR(15)
)

Both the local and global temporary table are stored in tempdb database.

2. What is SQL table variable?

A table variable is a variable in which you store data in rows and columns. You can also do all CRUD operations on table variable. Syntax for declaring table variable is written below:

DECLARE @tableVariable TABLE(ID INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(15))
INSERT INTO @tableVariable(Name)
VALUES ('First')

Table variable has only scope for current query. You cannot make a table variable for the SQL connection scope.

3. Difference between temporary table and table variable?

Temporary Table Table Variable
1. Temporary table are stored in tempDB database. Table variable is simply stored in memory as variable.
2. Temporary table is used for large data. Table variable is used for small data.
3. Stored procedures are recompiled every time if you have used temporary table inside. Don’t need the recompilation every time.
4. Temporary table are slower in performance compare than table variable. Table variables are fast as they need fewer resources.
5. You don’t need to pass temporary table to stored procedures and functions. You cannot pass table variable as input or output parameter to stored procedure and functions.

You cannot pass table variable as input or output parameter in stored procedure,

4. What is a View in SQL?

A View is a virtual table that you can use as a datasource in a query. A view has rows and columns just like a table. A view gets its data from the sql query written inside the block.

CREATE VIEW vwEmployeeJoinAfterMidYear
AS
	SELECT Name, JoinDate FROM Employee WHERE JoinDate >= '7/1/2013'
SELECT * FROM vwEmployeeJoinAfterMidYear

5. Can you use Order By clause in View?

No, you can’t use Order By clause in View. But if you use Top keyword in your query then you can use order by clause.

ALTER VIEW vwEmployeeJoinAfterMidYear
AS
	SELECT TOP 1 Name FROM Employee WHERE JoinDate >= '1/7/2013' Order By JoinDate

6. Can a View refer to temporary table?

No

7. Can a View contains select..into.. statement?

No

8. How can you protect the view so that if someone can’t see the view query?

We can put WITH ENCRYPTION keyword after the View name.

CREATE VIEW vwTestView WITH ENCRYPTION
AS
	SELECT * FROM Employee

9. How can you create the View Schema Bound?

By using Schema bound, a View is attached to base object. So that, you cannot change those fields of base object which you have used in the view.

CREATE VIEW vwTestView WITH SCHEMABINDING
AS
	SELECT ID, Name FROM dbo.Employee

Below written SQL statement is invalid as Name column is bound to vwTestView.

ALTER TABLE Employee
DROP COLUMN Name

10. What is User Defined Function (UDF) in SQL?

User defined function (UDF) is just like a method in the C# that contains the SQL statements. A user defined function can accept parameters, and return the data.

11. How many types of User Defined Function available in SQL?

There are three types of User Defined Function:

  1. Scalar function
  2. Inline table value function
  3. Multi-statement table value function

12. What is Scalar User Defined Function in SQL?

Scalar function is a user defined function that returns only a single value. Scalar function returns value by using the return command and return command should be the last statement of the function. A scalar function cann’t return text, ntext, cursor, image or timestamp data types.

CREATE FUNCTION dbo.fnScalarFunction1
(
    @NumberToAdd INT
)
RETURNS INT
AS
BEGIN
	RETURN (4 + @NumberToAdd)
END

Syntax for using Scalar Function:

SELECT dbo.fnScalarFunction1(4)

13. What is Inline table value User Defined Function in SQL?

Inline table function is a function that returns a table data type. Inline table value function is similar to view but it can also take parameters. 

CREATE FUNCTION dbo.fnInlineTableFunction
(
    @MinAge INT
)
RETURNS TABLE
AS
	RETURN (SELECT * FROM Employee WHERE Age > @MinAge)

Syntax for using Inline table value function:

SELECT * FROM dbo.fnInlineTableFunction(30)

14. What is Multi-statement table value User Defined Function in SQL?

 

Multi-statement table value function creates a table variable and then populate this table variable with the data.

CREATE FUNCTION dbo.fnMultistatementTableFunction
(
    @MinAge INT
)
RETURNS @myTable TABLE(ID INT, Name VARCHAR(15))
AS
BEGIN
	INSERT @myTable(ID, Name)
	SELECT ID, Name FROM Employee WHERE Age > @MinAge
	
	RETURN
END

Syntax for using Multi-statement table value function:

SELECT * FROM dbo.fnMultistatementTableFunction(40)

15. What is Stored Procedure in SQL?

Stored procedure is a group of T-SQL statements that are used to perform a particular task.

CREATE PROCEDURE spViewEmployees
AS
BEGIN
	SELECT * FROM Employee
END

Syntax of using stored procedure:

EXEC spViewEmployees

16. What are the advantages of using Stored Procedure in SQL?

  1. Stored procedures offer modularity and reusability.
  2. Stored procedures are compiled.
  3. Stored procedures offers security as it stop direct access to columns of table.
  4. All sql data processing is done at server instead of client machine.
  5. Only small text of sql sent to server so it reduces network traffic.

17. Where stored procedures text are stored?

Stored procedures are stored in the SysComments table in the same database in which they are created.

Syntax for finding stored procedure text:

SELECT * FROM SYSCOMMENTS WHERE ID = OBJECT_ID('spViewEmployees')

18. How can you encrypt theStored procedure?

You have to put “WITH ENCRYPTION” after the stored procedure name.

CREATE PROCEDURE spViewEmployees WITH ENCRYPTION
AS
BEGIN
	SELECT * FROM Employee
END

Then when you try to find the stored procedure text using the SysComments table text column is coming null.

SELECT * FROM SYSCOMMENTS WHERE ID = OBJECT_ID('spViewEmployees')

19. How many types of parameter types available in Stored Procedure?

There are two types of paramater types available:

  1. Input Parameter
  2. Output Parameter
CREATE PROCEDURE spViewEmployees
(
	@MinAge INT, --input parameter
	@TotalCount INT OUTPUT --output parameter
)
AS
BEGIN
	SELECT * FROM Employee WHERE Age > @MinAge
	SELECT @TotalCount = @@ROWCOUNT
END

Output:


DECLARE @TotalCount INT
EXEC spViewEmployees 30, @TotalCount OUTPUT
SELECT @TotalCount AS 'Total Employees'

20. From how many ways you can return data from Stored Procedure?

There are four ways to return data from stored procedure. 

  1. SELECT statement
  2. Output parameter
  3. RETURN command
  4. RAISERROR command

21. How can you view the Stored Procedure text in results?

We have to use the SP_HELPTEXT command.

SP_HELPTEXT spViewEmployees

 

You might also like

WPF Interview Questions for Beginners & Advanced – Part 1

SQL Server Interview Questions Series – Part 3

WPF Interview Questions for Beginners & Advanced – Part 3