|
Microsoft SQL Server Interview Questions and Answers
In replication, what is each set of the source data
that is replicated from the source server called?
Article
Each set of source data that is replicated is called an
article.
In SSIS, what is the difference between output columns
and external columns?
External columns represent the meta data of external
data sources and output columns are used be data flow
source adapters.
Output columns are used by all data-flow source adapters
and transformations. They are not used by destination
adapters. Put simply, they represent the metadata of the
data flowing out of the component. External columns
represent the metadata of external data sources and
destinations. As such, only source & destination
adapters have external columns because these are the
only components whose buffered pipeline data interacts
with these external sources and destinations.
You want to be sure your database server if properly
secured. You have two instances, a default instance and
a named instance installed. Which ports do you need to
open?
1433 and a specific port after configuring the named
instance to use that specific port
Named instances must be configured to use a specific
port and that port then opened in the firewall.
You have a relatively new SQL Server 2005 and msdb is
corrupt. How can you fix this database without a
restore? (data loss is acceptable)
Stop the server and restart it with a trace flag to
allow system databases to be detached. Then copy a new
version from the /INSTALL folder.
By starting SQL Server with trace flag 3608 you can
detach the msdb database. You can copy a new initial
version from the installation CD.
In SQL Server 2005 Integration Services, if you want to
import a flat file very quickly that contains only
integer data, what type of parsing should you use?
Fast Parse
The fast parse mode that is set on columns for a flat
file source connection can import a limited set of data
types extremely quickly.
In a new default SQL Server 2005 installation, what is
the status of the dedicated administrator connection?
Enabled for local connections only.
The dedicated adminstrator connection is enabled for
local connections only by default.
In the REPEATABLE READ isolation level, what phenomena
is still possible?
Phantom rows
The answer is Phantoms rows. A phantom row refers to the
situation where you execute a DML statement that
retrieves data, and another process may add new rows the
result set. For example:
SET ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT * FROM table
We return the following rows:
ColumnName
-----------
row1
row2
You are guaranteed to get back at least these rows, and
no other user may delete these rows (the rows are locked
with a shared lock) This is what is referred to as a
repeatable read. However, a user might add another row:
SELECT * FROM table
ColumnName
-----------
row1
row2
row3
To prevent phantoms, use the SERIALIZABLE isolation
level.
You are designing a new server that will hold 2
instances of SQL Server 2005 in a consolidation project.
One of the existing servers has 4GB of RAM and the other
has 2GB. You do not want either instance to feel memory
pressure, so you specify a new server with 8GB of RAM.
What else should you configure on these instances?
Min and Max memory for each instance
If you are designing multiple instances, you will want
to specify the amount of RAM that each has access to. If
you do not do this, performance can vary dramatically as
the servers compete for memory.
You have specified that a SQL Server 2005 login must
respect the password policy on a Windows XP host. Which
of the following are valid passwords for the user
Kendall?
All of the above are valid
On a Windows XP host, the password policy is not
enforced because the appropriate API call is not
available on the operating system.
A certificate is which type of security mechanism?
Asymmetric Key
A certificate is an asymmetric key that provides
encryption in addition to authentication of an entity.
Which of the following services is instance-aware?
Analysis Services
Of these, only Analysis services is instance-aware.
What What does the term ACID refer to?
An acronym that describes concurrency support in a
database
The answer is an acronym that describes concurrency
support in a database. ACID is an acronym for the
following four terms: * Atomicity - Every operation
within a transaction is treated as a singular operation;
either all of its data modifications are performed, or
none of them is performed.
* Consistency - Once the transaction is completed, the
system must be left in a consistent state.
* Isolation - It means that the operations within a
transaction must be suitably isolated from other
transactions. In other words, no other transactions
should see data in the intermediate state, within the
transaction, until it is finalized. This is done by
using locks.
* Durability - Once a transaction is competed, all
changes must be persisted as requested. The
modifications should persist in total even in the event
of a system failure.
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
|
