Interview Question and Answer

1

Why we cannot use column alias in where clause but we can use it in order by clause of select statement in sql server?

For example, it is incorrect to write:

SELECT Roll_No AS Id From Student WHERE Id > 1

While it correct:

SELECT Roll_No AS Id From Student ORDER BY Id

Explanation:

In sql server order of execution of different clauses of a select statement is following order:

Clause of select statement	Execution order
FROM	1
ON	2
JOIN	3
WHERE	4
GROUP BY	5
WITH CUBE or WITH ROLLUP	6
HAVING	7
SELECT	8
DISTINCT	9
ORDER BY	10
TOP	11

It is clear the WHERE clause executes before the SELECT clause so WHERE clause has no knowledge about column alias of SELECT clause while ORDER BY clause executes after the SELECT clause so it know about column alias of SELECT clause.

Hide

2

What are the differences between stored procedure and function in sql server?

Explanation:

1.

Function:

Functions can be part of SELECT statement or DML queries. For example:

Suppose there is a scalar function IsInteger. It is correct to write:

SELECT IsInteger(Roll_No) AS Id From Student

Stored procedure:

Stored procedures cannot be part of SELECT statement or any other DML queries.

2.

Function:

Function can retuen any type of data which sql server supports. For example scalar function can return INTEGER, MONEY, NVARCHAR, TEXT etc. and table valued function can return table variable. For example:

CREATE FUNCTION GetString()

RETURNS VARCHAR(100)

AS

BEGIN

    RETURN 'Exact Help'

END

--To execute function

SELECT dbo.GetString()

Stored procedure:

Return type of a stored procedure can return only integer type data. For example:

CREATE PROCEDURE LoadStudent

AS

BEGIN

    IF @@ERROR = 0

         RETURN 1

    ELSE

         RETURN 0

END

--To execute stored procedure

DECLARE @ReturnValue INTEGER

EXECUTE @ReturnValue = LoadStudent

SELECT @ReturnValue

3.

Function:

A function cannot return any other result set except its return type. For example, it is incorrect to create function like:

CREATE FUNCTION GetString()

RETURNS INTEGER

AS

BEGIN

    SELECT 'Exact Help'

   

    RETURN 1

END

Stored procedure:

A stored procedure can return as many result set as we want apart from its return type. For example:

CREATE PROCEDURE GetData

AS

BEGIN

   

    SELECT 'Exact help' --Result set 1

    SELECT * FROM Student  --Result set 2

   

    RETURN 1

END

4.

Function:

A function cannot have any insert, update, delete, merge statements, DDL queries, try catch block etc. It can have select statement but it cannot return result set. For example:

CREATE FUNCTION GetInfo()

RETURNS INTEGER

AS

BEGIN

    DECLARE @Roll AS INTEGER

    SELECT TOP(1) @Roll = Roll_No  FROM Student

    --It is incorrect to write

    /*

   

         SELECT TOP(1) Roll_No  FROM Student

         INSERT INTO Student DEFAUL VALUE

         DELETE FROM Student

         CREATE TABLE Emp(

             Id AS INT,

             Name AS VARCHAR(100)

         )

   

    */

   

    RETURN @Roll

END

Stored procedure:

A stored procedure can have all DDL and DML statements.

Hide

3

To delete the records of a table which one is better?

a. Using DELETE statement

b. Using TRUNCATE statement

Explanation:

It will depend upon the requirements:

1. TRUNCATE statement has not any WHERE clause. So if we have to delete a part of records from table we will have to use DELETE statement.

2. TURNCATE statement is faster than DELETE statement since it doesn’t keep the logs for each record. So we have not to keep the logs then we should use TRUNCATE statement otherwise we have to use DELETE statement.

3. TURNCATE statement reset the identity value of table so if we want to reset then we should use TURNCATE otherwise we should use DELETE statement.

Hide

4

What is a global temporary table in sql server?

Explanation:

In sql there are two types of temporary tables:

1. Local temporary table

2. Global temporary table

Local temporary table name has prefix # while global table has ##. For example:

Creating local temporary table

CREATE TABLE #ExactHelp(

    ID INT,

    Author VARCHAR(100)

)

Creating local temporary table

CREATE TABLE ##ExactHelp(

    ID INT,

    Author VARCHAR(100)

)

Scope of local temporary table is session and global temporary table is login.

Global temporary table is mostly used in dynamic sql for example:

EXECUTE('SELECT * INTO #TempTable FROM Customer')

EXECUTE('SELECT * INTO ##TempTable FROM Customer')

Now if we execute following query:

SELECT * FROM #TempTable

We will get error message: Invalid object name '#TempTable'.

If we execute following query it will work fine.

SELECT * FROM ##TempTable

Hide

5

Why a table can have only one clustered index?

Explanation:

Clustered index in sql server is stored as B tree. Physical organization of data of table with clustered index is same as clustered index. Any table can have only one physical organization so it can have only one clustered index.

Hide

6

What are the differences between table variable and temporary table in sql server?

Explanation:

1. Scope of temporary variable is session. For example if we will execute following query in same tab of sql server it will execute fine.

CREATE TABLE #temp(

    ID INT,

    Name VARCHAR(50)

)

Go

INSERT INTO #temp VALUES(1,'Davis')

SELECT * FROM #temp

But if we will execute following query in other tab

SELECT * FROM #temp

We will get error message like: Invalid object name '#temp'.

Note: When we open new query page or tab in sql server management studio it create a new session.

Scope of table variable is batch. For example we will execute following sql queries

DECLARE @table TABLE(

    ID INT,

    Name VARCHAR(50)

)

Go

INSERT INTO @table VALUES(1,'Davis')

SELECT * FROM @table

We get error like: Must declare the table variable "@table"

Note: Go keyword in sql server always creates a new batch.

2. Temporary table is same as normal table with different scope. We can create non – clustered index on temporary table. For example:

CREATE TABLE #temp(

    ID INT,

    Name VARCHAR(50)

)

CREATE NONCLUSTERED INDEX temp_table_index

    ON #temp(ID , Name)

We cannot create a non-clustered index on table variable. For example:

DECLARE @table TABLE(

    ID INT,

    Name VARCHAR(50)

)

CREATE NONCLUSTERED INDEX temp_table_index

    ON @table(ID , Name)

We will get some error message.

Note: If we have large amount of data and performing quires on it then temporary table may be better option than table variable since we can increase the performance of a query by creating indexes on it.  

3. There is not any table lock concept in the table variables. For example it is correct to write:

SELECT * FROM #temp WITH(NOLOCK)

While it is incorrect to write:

SELECT * FROM @table WITH(NOLOCK)

3. Unlike to the temporary table, table variables also not support transaction and logging. For example:

CREATE TABLE #temp(

 ID INT,

 Name VARCHAR(50)

)

BEGIN TRANSACTION

    INSERT INTO #temp VALUES(1,'Davis')

    ROLLBACK

    SELECT * FROM #temp

Select query will not return any rows since it has been rollback while if will perform same operation on table variable for example:

DECLARE @table AS TABLE(

 ID INT,

 Name VARCHAR(50)

)

BEGIN TRANSACTION

    INSERT INTO @table VALUES(1,'Davis')

    ROLLBACK

    SELECT * FROM @table

In this case select query will return one row like

ID	Name
1	Davis

Since table variables doesn’t support transaction and rows has not been roll backed.

4. Tables variables in the stored procedures are pre- compiled while temporary tables are not.

5. We can pass the table variables as a parameter of stored procedures or functions. For example:

 CREATE TYPE InfoType AS TABLE(

    ID INT,

    Name VARCHAR(50)

)

GO

CREATE PROCEDURE GetInfo(

    @table AS InfoType READONLY

)

AS

BEGIN

    SELECT * FROM @table

END

Now we will execute the above stored procedure by passing table variable.

DEClARE @info AS InfoType

INSERT INTO @info VALUES(1,'Davis')

EXECUTE GetInfo @info

Output:

ID	Name
1	Davis

While a temporary table cannot be passed. For example:

CREATE TABLE #temp(

    ID INT,

    Name VARCHAR(50)

)

INSERT INTO #temp VALUES(1,'Davis')

EXECUTE GetInfo #temp

We will get the error message like: nvarchar is incompatible with InfoType

6. A table value function can return table variable but it cannot return temporary table.

7. With help of SELECT INTO we can directly create the temporary table while it is not possible for temporary variables. For example:

SELECT * INTO #temp

FROM Student

It is not possible in case of table variables.

SELECT * INTO @table

FROM Student

If we will execute above query we will get error message like: Incorrect syntax near '@table'

Hide

7

Write a sql query to get all records of table in XML format.

Explanation:

To get the result set of SELECT statement in the XML format we have to use FOR clause. It has forms

1. AUTO

2. RAW

3. EXPLICIT

For example suppose we have KeyValue table like this:

Id	Key	Value
1	Name	Scott
2	Age	42
3	DOJ	12/05/2011
4	IsActive	1

Sql query to get the data of KeyValue table in XML format:

SELECT  * FROM KeyValue FOR XML AUTO

Output:

<KeyValue Id="1" Key="Name" Value="Scott" />

<KeyValue Id="2" Key="Age" Value="42" />

<KeyValue Id="3" Key="DOJ" Value="12/05/2011" />

<KeyValue Id="4" Key="IsActive" Value="1" />

Hide

8

What is difference between @@IDENTITY  and SCOPE_IDENTITY()

Explanation:

SCOPE_IDENTITY() function returns the values of identity column of last insered record in the current scope while @@IDENTITY has not such restriction.

Hide

9

Can a function return result set like table in sql server?

Explanation:

In sql server function can return only value of any data type. This may be table data type. Such function is called table valued function. For example:

CREATE FUNCTION GetData()

RETURNS TABLE

AS

RETURN (

    SELECT * FROM Student

)

--To execute this function

SELECT * FROM dbo.GetData()

Hide

10

Write a sql query to get all the tables of given database.

Explanation:

In sql server all the objects are stored in the system defined view sys.Objects. There is type column to identify the different type of object. For example:

U: User defined table

P: Stored procedure

FN: scalar function

S: System defined table 

Sql query to get the all user defined table:

SELECT * FROM sys.objects WHERE type = 'U'

Hide

11

Write a sql query to check data of a column is type bit or not in sql server?

Explanation:

Sql server doesn’t support a function like IsBit.  We can test a given data or column is type of BIT by using the function SQL_VARIANT_PROPERTY. For example:

DECLARE @Var AS BIT = 1

SELECT SQL_VARIANT_PROPERTY(@Var,'BaseType')

Output: bit

Creating IsBit function in sql server

CREATE FUNCTION IsBit(

    @Data AS SQL_VARIANT

)

RETURNS BIT

AS

BEGIN

    DECLARE @IsBit AS BIT

   

    IF SQL_VARIANT_PROPERTY(@Data,'BaseType') = 'bit'

         SET  @IsBit = 1

    ELSE

         SET  @IsBit = 0

        

    RETURN @IsBit

   

END

Examples:

DECLARE @Var1 BIT = 0

DECLARE @Var2 BIT = 'True'

DECLARE @Var3 INTEGER = 1

DECLARE @Var4 VARCHAR = 'True'

SELECT

    dbo.IsBit(@Var1),

    dbo.IsBit(@Var2),

    dbo.IsBit(@Var3),

    dbo.IsBit(@Var4)

Output:

1   1   0   0

Note: In sql server 0 and 1 are integer constants they are not a bit type constants. In the same way 'True' and 'False' are varchar constants not a bit constant. For example:

SELECT

    dbo.IsBit(1),

    dbo.IsBit(0),

    dbo.IsBit('True'),

    dbo.IsBit('False')

Output:

0   0   0   0

Hide

12

Can you define stored procedure in sql sever?

Explanation:

Stored procedure is batch of sql queries which is compiled into a single execution plan. It can have any number of parameters and one returns code (or return type) which can be only integer type. For example:

CREATE PROCEDURE AppProc(

    @Id AS INT,

    @Name AS VARCHAR(50) OUTPUT

)

AS

BEGIN

    SELECT @Name FROM Student WHERE Roll_No = @Id

    SELECT 'Success'

   

    RETURN 1

END

--To execute the procedure

DECLARE @RrturnCode AS INT

DECLARE @Name AS VARCHAR(50)

EXECUTE @RrturnCode = AppProc 5, @Name OUTPUT

SELECT @Name

Hide

13

Do you know any generic data type in sql server which stores almost all the other types of data?

Explanation:

SQL_VARIANT is special data type in sql server support the data of other types with few exceptions.

Hide

14

Write a sql query which delete the all records of Student table and insert the deleted records into the Student_Archive tables at the same time.

Explanation:

Sql server has introduce an output clause in DELETE statement by which we can return a result set of deleted records or insert into the other table. Sql query for this is:

DELETE Student

OUTPUT DELETED.* INTO Student_Archive  

Hide

15

Can you write a sql query to get the records of a table in the CSV (comma separated value)?

Explanation:

Suppose we have KeyValue like this:

Id	Key	Value
1	Name	Scott
2	Age	42
3	DOJ	12/05/2011
4	IsActive	1

We want to write a sql query which will return data of column key delimited by comma or any other character like this:

CSV

Name,Age,DOJ,IsActive

Sql server query:

DECLARE @SqlQuery AS VARCHAR(MAX) = ''

SELECT @SqlQuery = @SqlQuery + [KEY] + ',' FROM KeyValue

SELECT LEFT(@SqlQuery,LEN(@SqlQuery)-1)