MS SQL Interview Questions and Answers
Question - 91 : - What is the difference between UNION and UNION ALL?
Answer - 91 : -
Union will remove the duplicate rows from the result set while Union all doesn't.
Question - 92 : - Which operator do you use to return all of the rows from one query except rows are returned in a second query?
Answer - 92 : -
You use the EXCEPT operator to return all rows from one query except where duplicate rows are found in a second query. The UNION operator returns all rows from both queries minus duplicates. The UNION ALL operator returns all rows from both queries including duplicates. The INTERSECT operator returns only those rows that exist in both queries.
Question - 93 : - How will you create a column alias?
Answer - 93 : -
The AS keyword is optional when specifying a column alias.
Question - 94 : - In what sequence SQL statements are processed?
Answer - 94 : -
The clauses of the subselect are processed in the following sequence (DB2):
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- SELECT clause
- ORDER BY clause
- FETCH FIRST clause
Question - 95 : - What is lock escalation?
Answer - 95 : -
A query first takes the lowest level lock possible with the smallest footprint (row-level). When too many rows are locked (requiring too much RAM) the lock is escalated to a range or page lock. If too many pages are locked, it may escalate to a table lock.
Question - 96 : - What are the main differences between #temp tables and @table variables and which one is preferred?
Answer - 96 : -
- SQL Server can create column statistics on #temp tables
- Indexes can be created on #temp tables
- @table variables are stored in memory up to a certain threshold.
Question - 97 : - What are Checkpoint In SQL Server?
Answer - 97 : -
When we did the operation on SQL SERVER that is not committed directly to the database. All operations must be logged in to Transaction Log files after that they should be done on to the main database.CheckPoint is the point that alerts SQL Server to save all the data to the main database if no checkpoint is there then log files get full we can use the Checkpoint command to commit all data in the SQL SERVER. When we stop the SQL Server it will take a long time because Checkpoint is also fired.
Question - 98 : - Why we use the OPEN XML clause?
Answer - 98 : -
OPENXML parses the XML data in SQL Server in an efficient manner. Its primary ability is to insert XML data into the DB.
Question - 99 : - Can we store PDF files inside the SQL Server table?
Answer - 99 : -
YES, we can store this sort of data using a blob datatype.
Question - 100 : - Can we store Videos inside the SQL Server table?
Answer - 100 : -
YES, we can store Videos inside SQL Server by using FILESTREAM data type, which was introduced in SQL Server 2008.