Category >
MSSQLSERVER
|| Published on :
Monday, May 11, 2015 || Views:
3109
||
Top SQL And SQL SERVER Interview Questions Part 2 SQL SERVER Interview Questions Interview Questions
Top SQL And SQL SERVER Interview Questions Part 1
Question 1: What is BCP? When does it used?
Ans: BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the
structures same as source to destination.
Question 2: How do you load large data to the SQL server database?
Ans: Question 9: BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to
Imports a data file into a database table or view in a user-specified format.
Question 3: Can we rewrite subqueries into simple select statements or with joins?
Ans: Subqueries can often be re-written to use a standard outer join, resulting in faster performance. As we
may know, an outer join uses the plus sign (+) operator to tell the database to return all non-matching
rows with NULL values. Hence we combine the outer join with a NULL test in the WHERE clause to
reproduce the result set without using a sub-query.
Question 4: What are types of sub-queries?
Ans: Single-row subquery, where the subquery returns only one row.
Multiple-row subquery, where the subquery returns multiple rows,.and
Multiple column subquery, where the subquery returns multiple columns.
Question 5: What is SQL Profiler?
Ans: SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of
Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to
analyze later. For example, you can monitor a production environment to see which stored procedures
are hampering performance by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too
large, you can filter them based on the information you want, so that only a subset of the event data is
collected. Monitoring too many events adds overhead to the server and the monitoring process and can
cause the trace file or trace table to grow very large, especially when the monitoring process takes
place over a long period of time.
Question 6: What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it
used for?
Ans: Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by
pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table
and than do bulk of inserts and to restore those indexes after that.
Question 7: Can SQL Servers linked to other servers like Oracle?
Ans: SQL Server can be lined to any server provided it has OLE-DB provider from Microsoft to allow a link.
E.g. Oracle has a OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL
Server group.
Question 8: What is sub-query? Explain properties of sub-query.
Ans: Sub-queries are often referred to as sub-selects, as they allow a SELECT statement to be executed
arbitrarily within the body of another SQL statement. A sub-query is executed by enclosing it in a set of
parentheses. Sub-queries are generally used to return a single row as an atomic value, though they
may be used to compare values against multiple rows with the IN keyword.
A subquery is a SELECT statement that is nested within another T-SQL statement. A subquery SELECT
statement if executed independently of the T-SQL statement, in which it is nested, will return a result
set. Meaning a subquery SELECT statement can standalone and is not depended on the statement in
which it is nested. A subquery SELECT statement can return any number of values, and can be found
in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a
T-SQL statement. A Subquery can also be used as a parameter to a function call. Basically a subquery
can be used anywhere an expression can be used.
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, and
- A subquery cannot contain a ORDER-BY clause.
- A query can contain more than one sub-queries.
Question 9: What is De-normalization?
Ans: De-normalization is the process of attempting to optimize the performance of a database by adding
redundant data. It is sometimes necessary because current DBMSs implement the relational model
poorly. A true relational DBMS would allow for a fully normalized database at the logical level, while
providing physical storage of data that is tuned for high performance. De-normalization is a technique
to move from higher to lower normal forms of database modeling in order to speed up database access.
Ans: Question 10: What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and
increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers,
the value of this cannot be controled. Identity/GUID columns do not need to be indexed.