SQL Interview Questions for experienced professionals

Previous page                                                                                                                 Next page

Question 31: How to check identity information in SQL server table?
Answer -  To know about 'Identity information' run the following query -

DBCC CHECKIDENT ('<Your table>');

It will display current idenity value along with column value.

Question 32: How to reset the identity value in SQL server table?
Answer -  To reset the table identity we can check for the following -
1. If table does't not contain any relationship, then in this situation we can truncate the table.
2. If table contains relationship with other tables then first we need to delete the data then reset the idenity -
i - Delete the record
ii- Run the following query -DBCC CHECKIDENT ('dbo.Employee', RESEED, 0);

Question 33: How to check if expression is numeric in SQL server?
Answer -  We can check expression in SQL server whether it is numeric value or not by using ISNUMERIC('Expression'), It returns boolean value either o(for non numeric) or 1(for numeric).
Example - select ISNUMERIC('9') -- it will return 1
           select ISNUMERIC('abc') -- it will return 0

Question 34: How does 'In' clause affect the performace?
Answer -  When we write where clause in SQL server ex. - where Id in(value1, value2, value3,...). If values are less then optimizer will take each value individual and filter but if number of values are more than 63 then it will create an Internal table in the current login scope in SQL server to hold these values. so it will repeat same behaviour.

Question 35: What is cross and outer apply in SQL server?
Answer -  Through apply operator we can invoke a table value function. It behave same as join between two tables. There are two apply operator available in SQL server(from SQL server 2000 onwards)-
1. Cross apply
2. Outer apply

Cross apply work like as normal inner join, it will produce a result set which contains only matching rows.

Outer apply work like as left join, it will produce a result set which contains rows matching rows as well as null values in the columns produced from table valued function.

Example - Check here 

Question 36: What is the difference between IsNull and Coalesce function in SQL server?
Answer -  IsNull and Coalesce functions in SQL server has similar purpose but can be used in more effective way based on scenario -

1. IsNull function is use to evaluate for single value where as we can pass multiple values (more than 2)to evaluate into a Coalesce function.

2. Data type determination in IsNull function is always same as the passed column data type where as in Coalesce function return data type is based on values with the highest precedence.

Real time example

Question 37: Can clustered index create on multiple columns?
Answer -  Yes, it is possible.
In SQL server any table can have only clustered index but in a table we can create clustered index on multiple columns.
Example - Let's say there is a table named 'Test' in which we need to created clustered index -

CREATE CLUSTERED INDEX IX_Col1_Col2

    ON dbo.TestTable (Col1,Col2);

GO

Question 38: Can we implement Goto statement to reach a level like as C# programming in SQL server?
Answer -  Yes, We can do that.
In SQL server implementation of Goto state is same as programming lanugage -
Below example will reach to appropriate level based on satisfied condition.
Declare @Value int

select @Value= @@ERROR

if(@Value<>0)

Begin

Goto Solution

end

Else

Begin

Goto Problem

End

Solution:

Begin

Print 'This is solution'

Return

End

Problem:

Begin

Print 'This is Problem' 
Return

End
Note - Use return statement at last of each level definition otherwise next level definition will also be executed

Question 39: Can we place 'Go' inside a stored procedure body in SQL server?
Answer -  No, We can not do that.
Go in not a T-SQL command,It marks the end of a batch in Query Analyzer and therefore signals the end of a stored procedure definition in that batch, so it cannot be part of an SP.

Question 40: What is the max memory limit for data types in each page in SQL server?
Answer -  It is 8060 bytes.

Question 41: What is the maximum Maximum Capacity Specifications for SQL Server?
Answer -  Check here

Question 42: Describe different kind of keys in SQL Server?
Answer -  There are following type of keys available In SQL server –
1. Super key
2. Candidate key
3. Primary key
4. Alternate key
5. Composite/Compound key
6. Unique key
7. Foreign key

Check here for more detail.

Question 43: If we placed multiple "Go" after a SQL statement, will it work?
Answer - Yes, It will work.

Question 44: How to delete the records from a table by using inner join and without using "in" condition?
Answer - It is quite simple, Let's have two tables TableA and TableB -
Apply delete on join query

Delete TableA

from TableA inner join TableB on TableA.Id=TableB.Id

Go

Check here for detail.

Question 45: How to check the database compatibility in SQL Server?
Answer - 1. Right click on any system or user data base and select properties.
2. From the 'Select a page" pane click on Options and check the database compatibility level.
(Follow the below screen shot)