SQL Server Interview Questions and Answers Part 6
37. What is sub-query? Explain properties of sub-query.
Answer:
A subquery is a SQL query within a query.
A subquery is a SELECT statement that is nested within another T-SQL statement, executed independently in which it is nested and returns a result set.
Sub-queries can return a single row as an atomic value or multiple rows with the IN keyword.
Properties of Sub-Query
A subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator.
A subquery cannot contain ORDER-BY clause.
A query can contain more than one sub-queries.
SQL Subquery Examples
List of all students with score greater than 80
SELECT StudentName FROM Student WHERE StudentId
IN (SELECT StudentId FROM Score WHERE Score > 80)
38. What is SQL Profiler?
Answer:
SQL Profiler is a graphical tool that captures and saves data about each event to a file or SQL Server table to analyze later.
Using SQL Profiler, we can monitor the concerned events.
We should restrict to monitor few events because monitoring too many events cause the trace file or trace table to grow very large and adds overheads to the server.
39. Techniques you can use to optimize query performance.
Answer:
Creating useful indexes
Indexes help you find data with fewer disk I/O operations, thus help to achieve better query performance.
Creating useful indexes are very important as they can also diminish the performance. For example, if an application performs many DML (INSERT, UPDATE, and DELETE) operations on a table, we should have fewer indexes on such table because indexes are maintained with each DML operation. Use of many indexes on a table with many DML queries slows down performance significantly. If your queries are mostly SELECT statements, more indexes can be helpful.
Indexing very small tables is also not adviceable.
Rewrite Subqueries to Use JOIN
Rewriting a subquery to use JOIN can achieve better performance.
Limit Using Outer JOINs
Use Parameterized Queries
Parameterized queries yield better performance by compiling the query only once and executing the compiled plan multiple times.
40. What are the authentication modes in SQL Server?
Answer:
SQL Server offers two ways for implementing how the system will authenticate users:
Windows authentication mode and mixed authentication mode
Windows authentication mode requires users to provide a valid Windows username and password to access the database server. If this mode is chosen, SQL Server disables the SQL Server-specific login functionality, and the user's identity is confirmed solely through his Windows account.
This mode is sometimes referred to as integrated security.
Mixed authentication mode allows the use of both Windows and SQL Server credentials to authenticate each time the users connect. SQL Server allows administrator to create and maintain accounts with username and password within SQL Server. After having authenticated through his windows account, the users must be re-authenticated through SQL Server account each time they connect.
41. What is SQL Server Agent?
Answer:
SQL Server Agent is a service responsible to execute scheduled administrative tasks. These scheduled tasks are generally called as Jobs in SQL Server.
SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand.
For example, you can automate and schedule the task to backup company's server on every Monday. If the backup encounters a problem, SQL Server Agent can record the event and notify you.
42. What is Log Shipping?
Answer:
Log shipping ensures automatic backup of database and transaction log files on a production server and restoring them onto a standby server. This way the standby server has same db and can be used as the disaster recovery plan.
43. What is the difference between a Local and a Global temporary table?
Answer:
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
