Nov 20, 2013

SQL server interview questions and answers with explanation



Frequently asked Sql server interview questions and answers for fresher with explanation
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
Answer
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?
Answer
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
Answer
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?
Answer
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?
Answer
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?
Answer
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.
Answer
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()
Answer
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?
Answer
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.
Answer
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?
Answer
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?
Answer
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?
Answer
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.
Answer
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)?
Answer
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)
Hide
15
What are the advantages and disadvantages of using index? 
Answer
Explanation:

Advantage of index

Large numbers of indexes can help the performance of queries that do not modify data (SELECT statements) because SQL Server has more indexes to choose from to determine the best way to access the data as fast as possible.

Disadvantage of index

1. Large numbers of indexes on a table affect the performance of INSERT, UPDATE, and DELETE statements because all indexes must be adjusted appropriately as data in the table changes.

2. More disk spaces.

3. If we update a record and change the value of an indexed column in a clustered index, the database might need to move the entire row into a new position to keep the rows in sorted order.
Hide
16
Why we cannot use column alias in where clause but we can use select clause in sql server?
Answer
Explanation:
Why we cannot use column alias in where clause but we can use select clause in sql server?
Hide
17
Why we cannot use column alias in where clause but we can use select clause in sql server?
Answer
Explanation:
Why we cannot use column alias in where clause but we can use select clause in sql server?
Hide
18
Why we cannot use column alias in where clause but we can use select clause in sql server?
Answer
Explanation:
Why we cannot use column alias in where clause but we can use select clause in sql server?
Hide
19
Why we cannot use column alias in where clause but we can use select clause in sql server?
Answer
Explanation:
Why we cannot use column alias in where clause but we can use select clause in sql server?
Hide
20
Why we cannot use column alias in where clause but we can use select clause in sql server?
Answer
Explanation:
Why we cannot use column alias in where clause but we can use select clause in sql server?
Hide

3 comments:

  1. Anonymous6/12/2014

    Hi Ritesh,

    you are doing a good job. no doubt in it.

    But still i would like to give you one suggestion.Please start managing the things as you can see above questions 16 to 20 is same and that looks odd sometimes.

    Otherwise you are doing well.

    I hope you will follow it.

    ReplyDelete
  2. Nice. you are doing great job.

    ReplyDelete
  3. Anonymous11/02/2015

    how its great job? one year gone! no-change!

    ReplyDelete