SQL Server - Common Interview Questions and Answers


Question 1: Why a table in SQL server can contain only one clustered index?
Answer: When clustered index is created on column/columns in SQL server, then table records will be in sorted order based on primary key column. So as per genuine concept available records can be sorted only by one key. So each table can contain only one clustered index.

Question 2: Why null value is not allow on primary key column in SQL server?
Answer: When primary key is created on column or columns so column value will act as a unique identifier for respective row. So as per genuine concept unique identifier of any row in SQL table can not contain null value. So null value doesn't allow on primary key column.

Question 3: How to delete top 10 rows in SQL server?
Answer: There are following efficient way to delete top N rows in SQL server -

Way 1 :  WITH  Temp AS
                  (
                   SELECT TOP 10 * FROM MyTable
                   Order by <column> Asc
                  )
                then
                  DELETE FROM Temp

Way 2 : DELETE TOP (10) FROM   MyTable

Question 4: In which database of SQL server, SQL job execute history exist?How to find last job history which was run successfully? 
Answer: In MSDB database SQL job history exist . In order to get records regarding last successful run will refer the below query - 
Select top 1 *from msdb.dbo.sysjobhistory
where run_status=1
order by run_date desc

Question 5: How to find list of all SQL server Jobs along with respective owners?
Answer: All information releated to SQL job belongs to MSDB database and login information belongs to Master database. So achieve required result will use the following query - 
select A.name as [JobName],B.name as [Owner] from msdb.dbo.sysjobs A
left join master.sys.syslogins B on A.owner_sid=B.sid

Question 6: How to find list of all 'Logins' in SQL server?
Answer: All information releated to SQL login belongs to Master database. So achieve required result will use the following query - 
Select * from master.sys.syslogins

Question 7: How to find list of all 'Databases' in SQL server?
Answer: All information releated to databases belongs to Master database. So achieve required result will use the following query - 
SELECT name FROM master..sysdatabases

Question 8: How to find list of all SQL jobs along with corresponding job steps, databases and schedules?
Answer: All information releated to databases belongs to MSDB database. So achieve required result will use the following query - 
Select SJ.job_id,SJ.name as[Job Name],SJS.step_name,sjs.database_name,
sjss.next_run_date,SJSS.next_run_time
from msdb.dbo.sysjobs SJ
Inner join msdb.dbo.sysjobsteps SJS on sj.job_id=sjs.job_id
Inner join msdb.dbo.sysjobschedules SJSS on SJ.job_id=SJSS.job_id

Question 9: What are the main differences between 'Truncate' and 'Delete'?
Answer: There are following main difference between 'Truncate' and 'Delete' in SQL server -
1. Truncate is a DDL(Data Definition Language) command while Delete is a DML command.
2. Truncate command always lock the page and table but not any row during execution while Delete command lock the row during execution.
3. Truncate command doesn't activate any trigger as there is no log regarding row deletion while Delete command activate a trigger as there is log maintained for each row deletion.
4. Truncate command have better performance than Delete command as there is no log behind the Truncate command.
5. Truncate command doesn't take rollback once committed while  Delete command can rollback with the help of log table.
6. Truncate command always remove whole data, no where condition is possible, while in Delete command we can apply where clause.

Question 10: What are the main differences between 'Functions' and 'Stored Procedures'?
Answer: There are following main difference between 'Function' and 'StoredProcedure' in SQL server -
1. Functions always have to return at least one value while in Stored procedures it can be based on choice, stored procedure may or may not return a value.
2. Functions can have only input type parameters while Stored procedure can have input as well as output type parameters.
3. Function doesn't support transaction management while stored procedures support transaction management.
4. Function doesn't support exception handling while in Stored procedure try-catch can be used for exception handling.
5. Function can be called using a stored procedure while Stored procedure cannot call using a function.

Question 11: What are the main types of 'Stored procedures' in SQL server?
Answer: There are following main types of stored procedures available in SQL server database -
1. System defined are already defined stored procedures in SQL server. They are available with sys. schema under Master database. These stored procedures starts with prefix 'sp_'.
Example - sp_Rename, sp_Help.. etc
2. Extended stored procedures are stored in Master database and starts with prefix 'xp_'.
These procedures used to perform various maintenance activities.
3. User defined procedures are created by users to perform custom activities. In order to create user defined stored procedures, user should be aware about the syntax knowledge.

Question 12: What are the main types of 'Functions' in SQL server?
Answer: There are following main types of functions available in SQL server database -
1. System defined are already defined functions in SQL server for different purpose. There are two types of system defined functions in SQL server -
      1.1 Scalar function perform on single value and also return a single value.
             Example - ltrim('vipul '), this function return 'vipul'
      1.2 Aggregate function perform on a set of values and return a single value.
             Example - select max(salary) from dbo.employee, this 'max' function perform on a set of values but return a single value which will be max salary.

2. User defined are defined by users for other custom purpose. There are three types of user defined functions available in SQL server -
     2.1 Scalar function returns any single data type value. 
             Example - returns int, it will return int type single value      
     2.2 In-line table valued function returns table type variable, Value of table type variable should be derived from single select statement.
     2.3 Multi-Statement table valued function returns a table type variables, whose value can be derived from muliple SQL statements. It is useful when table is required based on derivation from multiple set of SQL stateements.

Question 13: What is output parameters in stored procedure?
Answer: In Microsoft SQL server a stored procedure with output parameters can be called for callback values to the calling application.
Example -
Create Procedure [dbo].[SampleSP]
@EmpId int,
@ManagerId int output,
@EmployeeId int output
as
Begin
Select @ManagerId=ManagerID,@EmployeeId =EmployeeID From dbo.MyEmployees
where EmployeeID=@EmpId

Execute and get back the values -
Declare @ManagerId int
Declare @EmployeeId int
exec [dbo].[SampleSP] 1,@ManagerId output,@EmployeeId output
select @ManagerId as [ManagerId],@EmployeeId as [EmployeeId]

It will Give manager's Id as well as Employee's Id.

Question 14: How to get value of an 'Identity' column which is created during insertion of a particular record ?
Answer: Suppose in a SQL table, there is an 'Identity' column and in this table Insert is happening from multiple locations, So as per scenario, corresponding created 'Identity column' value is required after insertion to respective location.
Example - There are 2 locations as 'L1', 'L2'. Each location is inserted One record which created 2 rows on Identity column 'I1', 'I2' respectively. Now 'I1' should go to 'L1', 'I2' should go to 'L2'. In order to implement same, follow the below code of SQL - 
Step 1- Create one table
Create table SampleData
(
Id int Identity(1,1),
Name varchar(20)
)
Step 2- Create one stored procedure for insert -
Create procedure InsertintoSampleData
@Name Varchar(20)
as
Begin
Insert into SampleData
select @Name
Select scope_identity()--(It will return respective identity:Current scope and current Login)
or
Select @@IDENTITY--(It will return respective identity:current Login)
or
Select IDENT_CURRENT('SampleData')--(It will return respective identity:No limitation)
End

Question 15: What is the difference between 'Scope_Identity()', '@@Identity' and 'Ident_Current('<table name>')'?
Answer: It is very common scenario when it is required to get last inserted record. In order to achieve this we have three options available in SQL server -
      1. Select SCOPE_IDENTITY
      2. Select @@IDENTITY
      3. Select IDENT_CURRENT('SampleData')

SCOPE_IDENTITY will return the last inserted identity column value, but it's scope is limited to inserted table and current login.

@@IDENTITY will return the last inserted identity column value, but it's scope is limited to inserted table and current login.

IDENT_CURRENT('<TABLE NAME>') will return the last inserted identity column value for any scope any login for a specific table.


No comments:

Post a Comment