Oct 11, 2016

Interview Questions and answers - SQL Server Day 7

Question: What are ACID properties in SQL Server?

Answer: The ACID rules of transaction in any database ensure the reliability of data in all transactions in the database.

Atomicity shows the ability of database to assure that either all tasks of a transaction are performed or none of them.

Consistency assure that database remain consistent state before the transaction and after the transaction is over.

Isolation assures that other operation can't be performed in an intermediate state during a transaction. As a result, transactions that run concurrently appear to be serialized.

Durability is responsible to notify the transaction is succeed; the transaction will persist, not undone. It will survive system failure and the database system has checked the integrity constraints and no need to abort the transaction.
For example click here 

Question: What is TRANSACTION in SQL Server?

Answer: Transaction is the collection of T-SQL statements which executes in single unit. Transaction begins with a specific T-SQL statement and ends when all T-SQL statements execute successfully. If any statements fail then transaction fails. Transaction has only two types of results success or failed.
For more details click here

Question: What are COMMIT and ROLLBACK in SQL Server?

Answer: COMMIT statement use to committed transaction to save changes in database successfully.
ROLLBACK statement use to stop current transaction and undone the changes made by transaction.
For more details click here 

Question: What is a WITH(NOLOCK) in SQL Server?

Answer: WITH(NOLOCK) used in SELECT statement to give the priority to select statement. WITH(NOLOCK) unlock the table for SELECT statement to stop another process.

Question: GO command in SQL server?

Answer: GO command indicates the end the SQL statements it is used when there are multiple statements to be executed but sent as a batch.

Question: What is difference between UNION and UNION ALL in SQL Server?

Answer: UNION operator is use to combine multiple result set into one result set but remove any duplicate rows. Basically UNION use to performing a DISTINCT operation across all columns in the result set. UNION operator has extra overhead of removing duplicate rows and sorting result.

UNION ALL operator use to combine multiple results set into one result set but it does not remove any duplicate result. Actually this is not remove duplicate rows so it is faster than UNION operator, if you want to combine multiple results and without duplicate records then use UNION. For more details click here 

Oct 10, 2016

Interview Questions and answers - SQL Server Day 6

Question: When we used UPDATE_STATISTICS commands in SQL server?

Answer: When bulk changes done in your data base like INSERT, UPDATE and DELETE because bulk changes may increase your index view fragmentation. UPDATE_STATISTICS Updates query optimization statistics on a table or indexed view.

Question: What is view in SQL Server?

Answer: View is a virtual table which contains one or more than one fields of more than one physical table. View is used for de-normalization purpose. This is used to join multiple tables and get the data.

Question: What is the property of relational table in SQL Server?
  1. Values are atomic.
  2. Column values are of the same kind.
  3. Each row is unique.
  4. The sequence of columns is insignificant.
  5. The sequence of rows is insignificant.
  6. Each column must have a unique name.

Question: Explain different type of lock in SQL Server?

Answer: Following locks are available in SQL Server.

Shared (S)
Used for read operations those do not change or update data, such as a SELECT statement.

Update (U)
Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking and potentially updating resources later.

Exclusive (X)
Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
Bulk Update (BU)
Used when bulk copying data into a table and the TABLOCK hint is specified.

Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Question: What is DBCC command in SQL Server?

Answer: DBCC stands for Database Consistency Checker.These commands are used to check the consistency of the database like validation tasks, maintenance and status checks.

For example –

DBCC CHECKALLOC – Responsible to check that all pages are correctly allocated in database.

DBCC CHECKDB – Responsible to check the integrity & allocation of specific objects in a database.

DBCC SQLPERF- Responsible to show transaction log statistics.

DBCC SQLFILEGROUP – Responsible to check all the tables file group for any design.

Oct 8, 2016

Interview Questions and answers - SQL Server Day 5

Question: What is difference between Truncate and Delete in SQL Server?

Answer: If you wish to removes rows basis on condition then we have to use DELETE command while truncate will remove all the rows from a table.

  • Truncate is DDL command.
  • Truncate command removes all data from the table.
  • Truncate does not keep the logs so it fast then DELETE.
  • Truncate statement is not conditional mean we cannot use WHERE clause in truncate statement.
  • Rollback is not possible.
  • Truncate reset the table to its empty state.

  • Delete is a DML command
  • We can use WHERE clause in delete statement.
  • Delete statement delete specific rows if where condition exist.
  • Delete command lock the row when executed, each row in table is locked for deletion.
  • Delete operation are logged individually so delete activates the trigger.
  • Rollback is possible in delete.
  • Delete keeps log so delete is slower then truncate.

Question: What are DML, DDL, DCL and TCL in SQL- Server?

DML is stand for Data Manipulation Language. DML is responsible to retrieve, store, modify, delete, insert and update data in database. Examples: SELECT, UPDATE, and INSERT statements.

DDL is stand for Data Definition Language. DDL is responsible to create and modify the structure of database objects in database. Examples: CREATE, ALTER, and DROP statements.

DCL is stand for Data Control Language. DCL is responsible to roles, permissions, and referential integrity and also use to control access to database by securing it. Examples: GRANT, REVOKE statements

TCL is stand for Transactional Control Language. TCL is responsible to manage different transactions occurring within a database. Examples: COMMIT, ROLLBACK statements.

Question: Write query SELECT, INSERT, DELETE and UPDATE Statements in SQL Server?

Answer: Suppose we have a table Employee with column 'FNAME', 'LNAME', 'ADDRESS' and CREATEDDATE

SELECT Statement: SELECT * FROM Employee

INSERT Statement:


DELETE Statement: 


Note: this query will delete all records of table.

Delete records with condition, where clause use for condition

UPDATE Statement:

Note: It will update ADDRESS column with 'GORKHPUR' of all table row.

Update Statement with condition

Question: What is difference between a HAVING clause and a WHERE clause in SQL Server?

Answer: Only with the SELECT statement we can be use HAVING Clause.  Basically HAVING Clause is used with GROUP BY Clause. Without used of GROUP BY clause, HAVING behaves like a WHERE clause.

Where Clause Example: SELECT empName,empDept,empSalary WHERE empSalary=20000

HAVING Clause Example: SELECT empName,AVG(empSalary) GROUP BY empName,empSalary HAVING AVG(empSalary)>20000
For more click here 

Question: What is Difference between Primary key and Unique Key in SQL Server?

  1. Primary key doesn’t have null value while unique key can hold null value.
  2. A table can have only one primary key while a table can have more than one unique key
  3. By default primary key have clustered index while by default unique key have non-clustered index

Question: What are difference between @@IDENTITY, SELECT SCOPE_IDENTITY(), SELECT IDENT_CURRENT in SQL Server?

SELECT @@IDENTITY: it’s responsible to returns the last identity value generated for any table in the current session, across all scopes (i.e. global scope).

SELECT SCOPE_IDENTITY(): It’s responsible to returns the last identity value generated for any table in the current session and the current scope(i.e. local scope).

SELECT IDENT_CURRENT(‘table_name’): It’s responsible to returns the last identity value generated for a specific table in any session and any scope (i.e. global scope). For more with example click here 

Question: How to get first & last day of the previous Week in SQL Server?

Answer:  Following query may help you
This query will return first day of the previous Week

This query wil return last day of the previous Week


Oct 7, 2016

Interview Questions and answers - SQL Server Day 4

Question: What is Primary and foreign key in SQL server?

Answer: Primary key ensures the row have unique identity. Primary key have unique values and but can’t be null. A table can have only one primary key.
Foreign key ensures data integrity of the relationship tables.

Question: What is an identity in SQL Server?

Answer: If we want to set the unique value of a column then we have to use Identity. Identity automatically generates numeric value; we can set start and increment value.

Question: Can you please tell me default port of TCP/IP of SQL Server? Can we changed it if yes then how?

Answer: Default port of SQL Server is 1433, yes it can be changed. Network Utility TCP/IP propertiesà Port Number.

Question: What is difference between clustered and non-clustered index in SQL Server?

Answer: Best answer by Microsoft MSDN..
  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index. When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.

  • Non-clustered indexes have a structure separate from the data rows. A non-clustered index contains the non-clustered index key values and each key value entry has a pointer to the data row that contains the key value.
  • The pointer from an index row in a non-clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key.
  • You can add non-key columns to the leaf level of the non-clustered index to by-pass existing index key limits, 900 bytes and 16 key columns, and execute fully covered, indexed, queries. For more info https://msdn.microsoft.com/en-IN/library/ms190457.aspx

Question: How many clustered index we can create in a table- SQL Server?

Answer: We can create only one clustered index in a table.

Question: How many non-clustered index we can create in a table- SQL Server?

Answer: We can more then on clustered index in a table. Max number of non-clustered index in SQL Server 5 is 249 and max number of non-clustered index in SQL Server 8 is 999.

Question: Any disadvantage of index in SQL Server?

Answer: Frequently changes of table may increase the fragmentation of Index it may cause of slow query performance, so time to time index should be rebuilt.

Question: What is fill factor in SQL Server?

Answer: Fill Factor works in performance tuning area, for the index the most important property is Fill Factor. Fill Factor responsible to determine the percentage of space on each leaf-level page to be filled with data. As we know page is smallest unit of SQL server which size is 8k. Every page cans one or more than one row which is depending on size of row.

Oct 6, 2016

Interview Questions and answers - SQL Server Day 3

Question:  What is difference between Function and Stored Procedure in SQL Server?

Answer: Some basic difference between stored procedure and function is described below.

The main difference between stored procedure and function is Function must returns a value while stored procedure can or not this is optional. Function can have input parameter only while stored procedure can have both input/output parameters. Function can be called from stored procedures but stored procedures can’t call from function. 

Stored Procedure
Function must returns a value
In stored procedure it is optional 
Function can have input parameter only
Stored procedure can have both input and output parameter s
Function can be called from procedure
Procedure can‘t be called from function
Function allows only select statement
Procedure allows DML statement(SELECT, INSERT, UPDATE, DELETE)
Function can use in SQL statement like WHERE/HAVING/SELECT
Procedure can’t be use in SQL statement like WHERE/HAVING/SELECT
Function can return table and can be treated as another row set. This can be use in joins with other tables.
Transaction can’t be implement
Transaction can be implement in stored procedure

Question: What is subquery in SQL server?

Answer: A SELECT statement query nested in another t-SQL  statement is called subquery. SELECT subquery always run independently and return result set to t-SQL statement. Subquery doesn't depend on statement in which it is nested.

Question: What are different types of joins in SQL Server?

Answer: Types of joins in SQL Server is given bellow.
1- Cross join
2- Inner join
3- Outer join
    a)Left Outer join
    b)Right Outer join
    c)Full Outer join
4- Self join

Question: What is Cross Join in SQL Server?

Answer: Joins between tables without conditions produces the Cartesian  product called cross join. The size of Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.


SELECT p.bID, t.Name AS Territory
FROM Sales.SalesPerson p
CROSS JOIN Sales.SalesTerritory t

Question: What is inner join in SQL Server?

Answer: Inner join display only the rows of tables which matched in both joined table on a particular column. This is the default type of join in the query.

Question: What is Outer join and it type in SQL Server?

Answer: Outer join includes related and rows and also not related rows. there are three type of outer joins in SQL Server.
a) Left Outer Join: the Left Outer join display all the rows of first table and all matched rows of second table but not appears unmatched rows of second table.
b) Right Outer join: Right Outer joined display all the row of second table and matched rows of first table but not appears unmatched rows if first table.
c) Full Outer Join: Full Outer join display all the rows of joined tables whether they matched or not.

Question: What is Self join in SQL Server?

Answer: Join between a table and it's alias is called self join. In this join table join itself but same name may create confusion so we used alias of table as second table.

Oct 5, 2016

Interview Questions and answers - SQL Server Day 2

Question: What is an index in SQL Server?

Answer: Index is responsible to arrange data physically to speed up the query performance. Index is the physical structure containing to the pointers of the data. We can create index one or more columns of the table. Creating a relational index on table is called rowstore index it is either clustered or non-clustered index. There are two types of index

1-  Clustered index
2-  Non-clustered index
3-  A new index introduced in SQL Server 2014. “Columnstore index”

Question: What is curser in SQL Server?

Answer: Curser is responsible to manipulate data in a set on row by row basis. Actually curser is the database object used by application.

Question: Types of index in SQL Server.

Answer: Types of index is given below.
  1. Clustered index
  2. Non-clustered index
  3. Columnstore index

Question: What are constraints in SQL Server?

Answer: Constraints are the rules enforced on data columns on table. This ensures the accuracy and reliability of the data in the database. Constraints define some conditions that restricts the column to remain true while inserting or updating or deleting data in column.

Question: Types of constraints in SQL Server?

Answer: Types of constraints is given below.
  1. NOT NULL- This constraint is responsible for a column to confirm that a column cannot have a null value.
  2. DEFAULT – This constraint provides a default value when specified non for this column.
  3. UNIQUE- This constraint insure that each row for the column has different value.
  4. PRIMARY KEY- Primary key constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. This constraint ensures that the specific column for a table have a unique identity.
  5. FOREIGN KEY- This constraints responsible to uniquely identified a rows/records in any another database table.
  6. CHECK- The CHECK responsible to enables a condition to check the value being entered into a record

Oct 4, 2016

Interview Question and answer - SQL Server Day 1

Question: What is Stored Procedure?

Answer: Stored Procedure is the collection of Structured Query Language (SQL) statements with an assigned name which stored in database in compiled form. Store Procedure can be used over the network by several clients using different input data because stored procedure can accept input parameters. Stored procedure improved performance reducing network traffic.

Question: What is Trigger in SQL Server?

Answer: Trigger is same like procedure which is executed on an action when event (INSERT, DELETE, UPDATE etc) occurs. DBMS manage and store to trigger. Triggers are commonly used to perform auditing action, to maintain the table integrity in place of native constraints such as foreign ken and check constraints, and perform other DML processing. When trigger is at work, process can’t be completed until trigger completion because trigger operate under scope of transaction.

Question: Type of trigger?

Answer: There are two types of trigger DML and DDL.
1-  DML Trigger: DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. There are two types of DML trigger.

A)  Instead of Trigger: Instead of trigger override the standards actions of the triggering, means fire in the place of the triggering action such as insert, delete or update. For example:  when the value being updated in an hourly wage column in a payroll table exceeds a specific value.

B)  After Trigger: This trigger fire after execution of action query. After trigger fire for both DML and DDL statements.

2-  DDL Trigger: This type of trigger fired when action occurs like Drop, Create, and Alter Table DDL triggers are always after trigger.