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
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
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
Answer: All information releated to SQL login belongs to Master database. So achieve required result will use the following query -
Answer: All information releated to databases belongs to Master database. So achieve required result will use the following query -
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 -
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.
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 -
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.
Create Procedure [dbo].[SampleSP]
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
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