Wednesday, March 24, 2010

Interview Panel : Mostly Asked Interview Questions in Sql Server


Main difference between Stored procedure vs User Functions :

  • Procedure may return none or more values.Function must always return one value either a scalar value or a table.
  • Procedure have input,output parameters.Functions have only input parameters.
  • Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
  • Functions can be called from procedure.Procedures cannot be called from function.
  • Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
  • Transaction management possible in procedure but not in function.
Main difference between Cluster Index vs Non - Cluster Index :

Cluster Index

1 A cluster index is a form of tables which consist of column and rows.
2 Cluster index exists on the physical level
3 It sorts the data at physical level
4 It works for the complete table
5 There is a whole table in form of sorted data
6 A table can contain only one cluster index

Non Cluster Index

1 A non cluster index is in the form of a report about the tables.
2 They are not created on the physical level but at the logical level
3 It does not sort the data at physical level
4 A table has 255 non clustered indexes
5 A table has many non clustered indexes.
6 It work on the order of data

Main difference between Truncate vs Delete :

Truncate and Delete both are used to delete data from the table. These both command will only delete data of the specified table, they cannot remove the whole table data structure. Both statements delete the data from the table not the structure of the table.

  • TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.
  • You can use WHERE clause (conditions) with DELETE but you can't use WHERE clause with TRUNCATE.
  • You can’t rollback data in TRUNCATE but in DELETE you can rollback data. TRUNCATE removes (delete) the record permanently.
  • A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command.
  • If tables which are referenced by one or more FOREIGN KEY constraints then TRUNCATE will not work.
  • TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter.
  • Delete and Truncate both are logged operation. But DELETE is a logged operation on a per row basis and TRUNCATE logs the deallocation of the data pages in which the data exists.
  • TRUNCATE is faster than DELETE.
How to Select Duplicate Records in a table using single Query?

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

How to Delete duplicate records from a table :
declare @UserId int 
declare @Count int 
declare @Name varchar(50)
declare @temp table(id int,name varchar(50))

set @Count = 0 

declare cur cursor
       for select userid,count(userid) as UserIdRepeat ,name from test group by userid,name having count(userid) > 1 
open cur
       fetch next from cur into @UserId,@Count,@Name  

while (@@fetch_status = 0) 
begin 
       
       if(@Count > 1)
       begin   
                 insert into @temp (id,name) values (@UserId,@Name)
                 print @UserId
                 delete from test where userid = @UserId
       end
       fetch next from cur into @UserId,@Count,@Name
end
       select * from @temp
close cur
deallocate cur
       insert into test select id,name from @temp
       select * from test order by userid

Types of ADO.NET Command Object Methods :

1.ExecuteReader - Select statements
2.ExecuteNonQuery - DML statements(Insert Update and Delete)
3.ExecuteScalar - Returns a single value from Aggragate functions like SUM AVG COUNT etc

Types of User Defined functions in SQl server :
1.) Scalar function
2.) Inline table valued function
3.) Multi statement table valued function
Scalar function :
A scalar user defined function returns one of the scalar Data type .text, ntext, etc
Inline table Value :
An inline table value function return a table data type
Multi -statement table value function :
 A multi -statement table value function return a table and Is also an exceptional alternative to a view as the function
 Can support multiple t-sql statements to build the final result.

What is Sub Query?

Sub-query means a Query within a Query. 
This is the Example: 
Select Employee_Id, Employee_name From Employees Where Employee_Id IN (Select Mgr_Id from Manager)

Difference between Where and Having Clause :

Here both are filter conditions

Where clause is used when from is used in the select condition...

Having clause is used when group by function in the select condition....

e.g:

WHERE applies to rows HAVING applies to summarize rows (summarized with GROUP BY) if you wanted to find the average salary in each department GREATER than 333 you would code:

SELECT

DEPARTMENT

AVG (SALARY) FROM EMP

WHERE DEPARTMENT > 333

GROUP BY DEPARTMENT

IF you then wanted to filter the intermediate result to contain departments where the average salary was greater that 50 000 you would code:

SELECT

DEPARTMENT

AVG (SALARY) FROM EMP

WHERE DEPARTMENT > 333

GROUP BY DEPARTMENT

HAVING AVG (SALARY) > 50000.

Where executes first

GROUP BY next and

finally HAVING

What is Magic Table?

Magic tables nothing but inserted and deleted which are temporary objects created by server internally to hold recently inserted values in the case of insert, to hold recently deleted values in the case of delete, to hold before updating values or after updating values in the case of update.

Difference between Primary Key and Unique Key?

Primary key:

1) Primary key is nothing but it is uniqly identified each roe in Table.
2) Primary key does not Allows Duplicate values and Null values.
3) Primary key is default Clustered indexes.
4) one table can have only one Primary key.

Unique Key:

1) Unique Key is nothing but it is uniqly identified each roe in Table.

2) Unique Key does not Allows Duplicate values but allows only one Null value.

3) Unique Key is default Non- Clustered indexes.

2 comments: