What Is Merge Statement?
MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.
What is HEAP table ?
A table with NO CLUSTERED INDEXES is called as HEAP table. The data rows of a heap table are not stored in any particular order or linked to the adjacent pages in the table. This unorganized structure of the heap table usually increases the overhead of accessing a large heap table, when compared to accessing a large nonheap table (a table with clustered index). So, prefer not to go with HEAP tables ..
What is ROW LOCATOR ?
If you define a NON CLUSTERED index on a table then the index row of a nonclustered index contains a pointer to the corresponding data row of the table. This pointer is called a row locator. The value of the row locator depends on whether the data pages are stored in a heap or are clustered. For a nonclustered index, the row locator is a pointer to the data row. For a table with a clustered index, the row locator is the clustered index key value.
What is Covering Index ?
A covering index is a nonclustered index built upon all the columns required to satisfy a SQL query without going to the base table. If a query encounters an index and does not need to refer to the underlying data table at all, then the index can be considered a covering index. For Example
Select col1,col2 from table
where col3 = Value
group by col4
order by col5
Now if you create a clustered index for all the columns used in Select statement then the SQL doesn’t need to go to base tables as everything required are available in index pages.
What is Indexed View ?
A database view in SQL Server is like a virtual table that represents the output of a SELECT statement. A view is created using the CREATE VIEW statement, and it can be queried exactly like a table. In general, a view doesn’t store any data—only the SELECT statement associated with it. Every time a view is queried, it further queries the underlying tables by executing its associated SELECT statement.
A database view can be materialized on the disk by creating a unique clustered index on the view. Such a view is referred to as an indexed view. After a unique clustered index is created on the view, the view’s result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing costly operations during query execution. After the view is materialized, multiple nonclustered indexes can be created on the indexed view.
What is Bookmark Lookup ?
When a SQL query requests a small number of rows, the optimizer can use the nonclustered index, if available, on the column(s) in the WHERE clause to retrieve the data. If the query refers to columns that are not part of the nonclustered index used to retrieve the data, then navigation is required from the index row to the corresponding data row in the table to access these columns.This operation is called a bookmark lookup.
What is the difference between Primary Key and Unique Key ?
Both the Primary Key(PK) and Unique Key(UK) are meant to provide Uniqueness to the Column on which they are defined. PFB the major differences between these two.
By default PK defines Clustered Index in the column where as UK defines Non Clustered Index.
PK doesn’t allow NULL Value where as UK allow ONLY ONE NULL.
You can have only one PK per table where as UK can be more than one per table.
PK can be used in Foreign Key relationships where as UK cannot be used.
What is the difference between Delete and Truncate ?
Both Delete and Truncate commands are meant to remove rows from a table. There are many differences between these two and pfb the same.
Truncate is Faster where as Delete is Slow process.
Truncate doesn’t log where as Delete logs an entry for every record deleted in Transaction Log.
We can rollback the Deleted data where as Truncated data cannot be rolled back.
Truncate resets the Identity column where as Delete doesn’t.
We can have WHERE Clause for delete where as for Truncate we cannot have WHERE Clause.
Delete Activates TRIGGER where as TRUNCATE Cannot.
Truncate is a DDL statement where as Delete is DML statement.
What are Indexes or Indices ?
An Index in SQL is similar to the Index in a book. Index of a book makes the reader to go to the desired page or topic easily and Index in SQL helps in retrieving the data faster from database. An Index is a seperate physical data structure that enables queries to pull the data fast. Indexes or Indices are used to improve the performance of a query.
What are Constraints or Define Constraints ?
Generally we use Data Types to limit the kind of Data in a Column. For example, if we declare any column with data type INT then ONLY Integer data can be inserted into the column. Constraint will help us to limit the Values we are passing into a column or a table. In simple Constraints are nothing but Rules or Conditions applied on columns or tables to restrict the data.
Different types of Constraints ?
There are THREE Types of Constraints.
Domain
Entity
Referential
Domain has the following constraints types –
Not Null
Check
Entity has the following constraint types –
Primary Key
Unique Key
Referential has the following constraint types –
Foreign Key
Types of Indices in SQL ?
There are TWO types of Indices in SQL server.
Clustered
Non Clustered
How many Clustered and Non Clustered Indexes can be defined for a table ?
Clustered – 1
Non Clustered – 999
What is Transaction in SQL Server ?
Transaction groups a set of T-Sql Statements into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, atransaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.by programmers to group together read and write operations. In Simple Either FULL or NULL i.e either all the statements executes successfully or all the execution will be rolled back.
Types of Transactions ?
There are TWO forms of Transactions.
Implicit – Specifies any Single Insert,Update or Delete statement as Transaction Unit. No need to specify Explicitly.
Explicit – A group of T-Sql statements with the beginning and ending marked with Begin Transaction,Commit and RollBack. PFB an Example for Explicit transactions.
BEGIN TRANSACTION
Update Employee Set Emp_ID = 54321 where Emp_ID = 12345
If(@@Error <>0)
ROLLBACK
Update LEave_Details Set Emp_ID = 54321 where Emp_ID = 12345
If(@@Error <>0)
ROLLBACK
COMMIT
In the above example we are trying to update an EMPLOYEE ID from 12345 to 54321 in both the master table “Employee” and Transaction table “Leave_Details”. In this case either BOTH the tables will be updated with new EMPID or NONE.
MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.
A table with NO CLUSTERED INDEXES is called as HEAP table. The data rows of a heap table are not stored in any particular order or linked to the adjacent pages in the table. This unorganized structure of the heap table usually increases the overhead of accessing a large heap table, when compared to accessing a large nonheap table (a table with clustered index). So, prefer not to go with HEAP tables ..
What is ROW LOCATOR ?
If you define a NON CLUSTERED index on a table then the index row of a nonclustered index contains a pointer to the corresponding data row of the table. This pointer is called a row locator. The value of the row locator depends on whether the data pages are stored in a heap or are clustered. For a nonclustered index, the row locator is a pointer to the data row. For a table with a clustered index, the row locator is the clustered index key value.
What is Covering Index ?
A covering index is a nonclustered index built upon all the columns required to satisfy a SQL query without going to the base table. If a query encounters an index and does not need to refer to the underlying data table at all, then the index can be considered a covering index. For Example
Select col1,col2 from table
where col3 = Value
group by col4
order by col5
Now if you create a clustered index for all the columns used in Select statement then the SQL doesn’t need to go to base tables as everything required are available in index pages.
 |
| Marketo Most Frequently Asked Latest SQL Server Interview Questions Answers |
What is Indexed View ?
A database view in SQL Server is like a virtual table that represents the output of a SELECT statement. A view is created using the CREATE VIEW statement, and it can be queried exactly like a table. In general, a view doesn’t store any data—only the SELECT statement associated with it. Every time a view is queried, it further queries the underlying tables by executing its associated SELECT statement.
A database view can be materialized on the disk by creating a unique clustered index on the view. Such a view is referred to as an indexed view. After a unique clustered index is created on the view, the view’s result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing costly operations during query execution. After the view is materialized, multiple nonclustered indexes can be created on the indexed view.
What is Bookmark Lookup ?
When a SQL query requests a small number of rows, the optimizer can use the nonclustered index, if available, on the column(s) in the WHERE clause to retrieve the data. If the query refers to columns that are not part of the nonclustered index used to retrieve the data, then navigation is required from the index row to the corresponding data row in the table to access these columns.This operation is called a bookmark lookup.
What is the difference between Primary Key and Unique Key ?
Both the Primary Key(PK) and Unique Key(UK) are meant to provide Uniqueness to the Column on which they are defined. PFB the major differences between these two.
By default PK defines Clustered Index in the column where as UK defines Non Clustered Index.
PK doesn’t allow NULL Value where as UK allow ONLY ONE NULL.
You can have only one PK per table where as UK can be more than one per table.
PK can be used in Foreign Key relationships where as UK cannot be used.
What is the difference between Delete and Truncate ?
Both Delete and Truncate commands are meant to remove rows from a table. There are many differences between these two and pfb the same.
Truncate is Faster where as Delete is Slow process.
Truncate doesn’t log where as Delete logs an entry for every record deleted in Transaction Log.
We can rollback the Deleted data where as Truncated data cannot be rolled back.
Truncate resets the Identity column where as Delete doesn’t.
We can have WHERE Clause for delete where as for Truncate we cannot have WHERE Clause.
Delete Activates TRIGGER where as TRUNCATE Cannot.
Truncate is a DDL statement where as Delete is DML statement.
What are Indexes or Indices ?
An Index in SQL is similar to the Index in a book. Index of a book makes the reader to go to the desired page or topic easily and Index in SQL helps in retrieving the data faster from database. An Index is a seperate physical data structure that enables queries to pull the data fast. Indexes or Indices are used to improve the performance of a query.
What are Constraints or Define Constraints ?
Generally we use Data Types to limit the kind of Data in a Column. For example, if we declare any column with data type INT then ONLY Integer data can be inserted into the column. Constraint will help us to limit the Values we are passing into a column or a table. In simple Constraints are nothing but Rules or Conditions applied on columns or tables to restrict the data.
Different types of Constraints ?
There are THREE Types of Constraints.
Domain
Entity
Referential
Domain has the following constraints types –
Not Null
Check
Entity has the following constraint types –
Primary Key
Unique Key
Referential has the following constraint types –
Foreign Key
There are TWO types of Indices in SQL server.
Clustered
Non Clustered
How many Clustered and Non Clustered Indexes can be defined for a table ?
Clustered – 1
Non Clustered – 999
What is Transaction in SQL Server ?
Transaction groups a set of T-Sql Statements into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, atransaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.by programmers to group together read and write operations. In Simple Either FULL or NULL i.e either all the statements executes successfully or all the execution will be rolled back.
Types of Transactions ?
There are TWO forms of Transactions.
Implicit – Specifies any Single Insert,Update or Delete statement as Transaction Unit. No need to specify Explicitly.
Explicit – A group of T-Sql statements with the beginning and ending marked with Begin Transaction,Commit and RollBack. PFB an Example for Explicit transactions.
BEGIN TRANSACTION
Update Employee Set Emp_ID = 54321 where Emp_ID = 12345
If(@@Error <>0)
ROLLBACK
Update LEave_Details Set Emp_ID = 54321 where Emp_ID = 12345
If(@@Error <>0)
ROLLBACK
COMMIT
In the above example we are trying to update an EMPLOYEE ID from 12345 to 54321 in both the master table “Employee” and Transaction table “Leave_Details”. In this case either BOTH the tables will be updated with new EMPID or NONE.