MS SQL Interview Questions and Answers
Question - 101 : - Can we hide the definition of a stored procedure from a user?
Answer - 101 : -
YES, while creating stored procedure we can use WITH ENCRYPTION which will convert the original text of the CREATE PROCEDURE statement to an encrypted format.
Question - 102 : - What have included columns when we talk about SQL Server indexing?
Answer - 102 : -
Indexed with included columns were developed in SQL Server 2005 that assists in covering queries. Indexes with Included Columns are non clustered indexes that
have the following benefits:
- Columns defined in the include statement, called non-key columns, are not counted in the
- a number of columns by the Database Engine.
- Columns that previously could not be used in queries, like nvarchar(max), can be included
- as a non-key column.
- A maximum of 1023 additional columns can be used as non-key columns.
Question - 103 : - What is an execution plan? How would you view the execution plan?
Answer - 103 : -
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query.
From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If this option is turned on it will display the query execution plan in a separate window when the query is run again.
Question - 104 : - Explain UNION, MINUS, UNION ALL, INTERSECT?
Answer - 104 : -
INTERSECT returns all distinct rows selected by both queries.
MINUS: returns all distinct rows selected by the first query but not by the second.
UNION: returns all distinct rows selected by either query
UNION ALL: returns all rows selected by either query, including all duplicates