|
Microsoft SQL Server Interview Questions and Answers
You wish to configure event logging for your SQL
Server 2005 Notification Services instance. Where would
you make this change?
Edit the nsservice.exe.config file in the C:\Program
Files\Microsoft SQL Server\90\NotificationServices\n.n.nnn\bin
folder.
Event logging and most Notification Services
configuration require editing an XML file. In this case,
the NSservice.exe.config file is edited to set the
appropriate editing level.
Which of the following is not true about the Raw File
Destintion connection in SQL Server 2005 Integration
Services?
It supports BLOB object data.
The Raw File Destination connection does not use a
connection manager, supports NULL data, and only has one
input. It also does not support BLOB data or have an
error output.
What message types exist in SQL Server 2005 Service
Broker?
These are defined for each contract.
Each application that sets up queues and contracts
inside Service Broker must define the message types that
are valid for the contract.
What does the CEILING() function do?
Returns the smallest integer greater than or equal to
the value passed in.
CEILING() returns the smallest integer that is great
than or equal to the value passed in.
What is a dialog conversation in the SQL Server 2005
Service Broker.
A dialog conversation is a conversation between
services.
A dialog conversation is a conversation between
services. A conversation includes messages being passed
back and forth as part of a contract.
What is row versioning in SQL Server 2005?
Row versioning keeps a copy of each row for use by
applications or transactions to prevent readers from
being blocked by writers.
Row versioning is a method whereby the database engine
keeps a copy of a row's data as it existed before the
start of a transaction for queries to read this data and
reduce locking contention if they are configured.
What does @@MAX_PRECISION return?
The maximum precision for numeric and decimal data.
This function returns the maximum precision for numeric
and decimal data as set on the server. The default for
SQL Server 2005 is 38.
Which of the following columns can be indexed with SQL
Server 2005 Full-Text Search?
char, varchar, nvarchar, and varbinary, text, ntext, and
image
All character columns, char, varchar and nvarchar
columns including max, text and ntext, and image columns
are valid for full-text searching.
When starting SQL Server 2005 from the command line,
what does the -h switch do?
This switch reserves memory space for Hot-Add memory
metadata, available with AWE enabled.
This switch is used with 32-bit SQL Server and AWE to
reserve memory to reserve memory space for Hot-Add
memory metadata.
Janice has two tables, Employees and Orders. She has
been asked to provide a report of the number of orders
processed by each employee in the month of June. If an
employee didn’t process any orders, the report should
reflect the employee’s name and a zero for the number of
orders. Which of the queries is the best one for Janice
to use to return the information she has been requested
to provide?
SELECT
E.LastName + ', ' + E.FirstName AS [Employee Name]
, ISNULL(O.[# of Orders], 0) [# of Orders]
FROM dbo.Employees E
LEFT JOIN (SELECT
EmployeeID
, COUNT(*) [# of Orders]
FROM dbo.Orders
WHERE OrderDate >= '20060601'
AND OrderDate < '20060701'
GROUP BY EmployeeID) O
ON E.EmployeeID = O.EmployeeID
ORDER BY [Employee Name]
While it would seem BETWEEN would save Janice a bit of
typing, there is a problem with it. BETWEEN corresponds,
based on these queries to the same as:
WHERE OrderDate >= ‘20060601’ AND OrderDat e<=
‘20060701’
It’s the latter one that causes the query to be
incorrect as it would potentially include orders placed
on July 1st at midnight (20060701 corresponds to July 1,
2006 at 00:00:00). While this would be unlikely in a
small retail environment, it is entirely possible in
larger operations, especially those which are
international in scope. Therefore, the use of BETWEEN in
this case is not appropriate.
With respect to the GROUP BY, it must appear in the
subquery on the Orders table, as given in the answer.
When it occurs after the LEFT JOIN, the rows where there
is an employee but no order for the month will be lost.
Since Janice must report on employees who had no sales,
this is unacceptable.
Page Numbers : 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
