Set - 8

Question 1 :

What does the SQL Writer service do?

Answer :

Handles Volume Shadow Service Copy functions.
The SQL Writer service was introduced to handle access to the data files in SQL Server. It allows backup programs, like VSS, to function while SQL Server is still running.


Question 2 :

Which key provides the strongest encryption?

Answer :

AES (256 bit)
The longer the key, the better the encryption, so choose longer keys for more encryption. However there is a larger performance penalty for longer keys. DES is a relatively old and weaker algorithm than AES.


Question 3 :

You are delegating permissions on your SQL Server 2005 server to other administrators. You have local, single server jobs on one server that you would like to allow another administer to start, stop, and view the history for, but not delete history. This administrator will own the jobs. Which role should you assign?

Answer :

SQLAgentUserRole
SQL Server 2005 provides 3 fixed roles for the agent service that limit privileges for administrators. The SQLAgentUserRole is designed for local jobs (not multiserver) that allows the member to work with their owned jobs (edit, start, stop, view history) without deleting the history of any job.


Question 4 :

What level of permissions does a user need to create an assembly with UNSAFE permissions?

Answer :

sysadmin
Only a sysadmin can create assemblies that are specified as unsafe.


Question 5 :

Which optional SQL Server component must you install to ensure that you can create and execute maintenance plans?

Answer :

SQL Server Integration Services
You must install Integration Services in order to properly create and execute maintance plans since these plans are a subset of SSIS packages.


Question 6 :

You have tightly secured access to your SQL Server 2005 server by placing it behind a firewall and limited ports 1433 to those clients that need to directly access the SQL Server. One of your developers complains about connecting to the Integration Services esrvice on your server. What could be the problem if you know the service is working properly?

Answer :

The firewall needs to allow port 135 through for Integration Services
Integration Services requires access to both DCOM and port 135. You need to open this port to allow the developer to communicate with SSIS.


Question 7 :

In SQL Server 2005, which network protocols use endpoints?

Answer :

All of the above
All communication points with SQL Server using network protocols use an endpoint. SQL Server creates defaults for each protocol that you enable.


Question 8 :

You have a SQL Server 2005 cluster and need to add anti-virus software as per your corporate standards. What should you exclude from scans?

Answer :

Log and data files for your databases as well as the Quorum drive and the backup folders.
Anti-virus programs can exist on the same server as SQL Server without an issue if you exclude certain items. The database data and log files, backup files and folders, the quorum drive for clusters, temporary replication files, the SQL Server log files, and Log Shipping folders/files. You do not need to exclude that SQL Server executables and probably do not want to so that they are protected.


Question 9 :

What port do you need to open on your server firewall to enable named pipes connections?

Answer :

Port 445
Named pipes communicates across TCP port 445.


Question 10 :

When the network service built-in account connects to a network resource, what is the context?

Answer :

The computer account of the Windows installation.
The network service account connects to network resources as the computer account for the Windows installation.


Question 11 :

A certificate is like which type of key in SQL Server 2005 encryption?

Answer :

An Asymmetric Key
A certificate is like an asymmetric key and can validate the identity of the issuer.


Question 12 :

How many connections are available in SQL Server MSDE 2000?

Answer :

Unlimited, no different from the other SQL Server 2000 editions.
The answer is actually an unlimited number of connections, though this in a practical sense is limited by memory on the machine (The actual limitation is 32k connecitons). Instead there is a governer that kicks in to limit things to 8 operations, of which 3 are system operations in some cases. But these are operations, not connections.


Question 13 :

What will be the result of the last SELECT statement if you execute the T-SQL script below as a sysadmin.
USE master
GO
IF DB_ID('test_schema') IS NOT NULL
DROP DATABASE test_schema
GO
CREATE DATABASE test_schema
GO
USE test_schema
GO
CREATE SCHEMA Schema1
CREATE TABLE Table1 (col1 int)
GO
CREATE SCHEMA Schema2
GO
CREATE TABLE Table1 (col1 int)
GO
SELECT SCHEMA_NAME(schema_id), name FROM sys.objects
WHERE name = 'Table1'

Answer :

Schema1 Table1
dbo Table1
The correct answer is A. You can combine the CREATE SCHEMA with a CREATE TABLE. You can even specify GRANTs or DENYs. Search Books online for CREATE SCHEMA.


Question 14 :

You are considering consolidating a number of your existing SQL Servers onto a new, large SQL Server 2005 server. You have 3 SQL Server 6.5 servers, 6 SQL Server 7.0 servers, and 16 SQL Server 2005 servers. You cannot make any application changes, but you can size a large SQL Server 2005 server to handle the disk, memory, and CPU needs of the existing servers as well as set compatibility modes. How many of these servers can you consolidate?

Answer :

25
In SQL Server 2005, you can handle a large number of transactions, which makes it a good choice for consolidating servers. It includes compatibility modes for SQL Server 6, 6.5, 7 and 2000. Therefore you can consolidate all the servers, 25 of them, onto the new server.


Question 15 :

To maintain the best performance of your SQL Server 2005 databases, how should you deploy encryption technologies?

Answer :

Encrypt data with a symmetric key and secure all keys with asymmetric keys.
For performance reasons, you should encrypt data using a symmetric key and secure that key with an asymmetric key.


Question 16 :

In SQL Server 2005, which network protocols use endpoints?

Answer :

All of the above
All communication points with SQL Server using network protocols use an endpoint. SQL Server creates defaults for each protocol that you enable.


Question 17 :

You have a SQL Server 2005 cluster and need to add anti-virus software as per your corporate standards. What should you exclude from scans?

Answer :

Log and data files for your databases as well as the Quorum drive and the backup folders.
Anti-virus programs can exist on the same server as SQL Server without an issue if you exclude certain items. The database data and log files, backup files and folders, the quorum drive for clusters, temporary replication files, the SQL Server log files, and Log Shipping folders/files. You do not need to exclude that SQL Server executables and probably do not want to so that they are protected.


Question 18 :

Can you give me an overview of some of the database objects available for use in SQL Server 2000?

Answer :

You are looking for objects such as: tables, views, user-defined functions, and stored procedures; it's even better if they mention additional objects such as triggers. It's not a good sign if an applicant cannot answer this basic question.


Question 19 :

What is an index? What types of indexes are available in SQL Server 2000?

Answer :

Any experienced database developer should be able to answer this question with ease. Some of the less-experienced developers will be able to answer it, but with a little less clarity.

Weekly SQL tips in your inbox
TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!
In its most simple terms, an index is a data structure used to provide quick access to data in a database table or view. In SQL Server, they come in two flavors: clustered and non-clustered. Clustered indexes store the data at the leaf level of the index. This means that whichever field(s) in your table are included in the clustered index, they will be stored in an orderly fashion in the table. Because of this sorting, you can only have one clustered index per table. Non-clustered indexes contain a row identifier at the leaf level of the index. This row identifier is a pointer to a location of the data on the disk. This allows you to have more than one non-clustered index per table.


Question 20 :

What does NULL mean?

Answer :

The value NULL is a very tricky subject in the database world, so don't be surprised if several applicants trip up on this question.
The value NULL means UNKNOWN; it does not mean '' (empty string). Assuming ANSI_NULLS are on in your SQL Server database, which they are by default, any comparison to the value NULL will yield the value NULL. You cannot compare any value with an UNKNOWN value and logically expect to get an answer. You must use the IS NULL operator instead.


Question 21 :

What is a primary key? What is a foreign key?

Answer :

A primary key is the field(s) in a table that uniquely defines the row in the table; the values in the primary key are always unique. A foreign key is a constraint that establishes a relationship between two tables. This relationship typically involves the primary key field(s) from one table with an adjoining set of field(s) in another table (although it could be the same table). The adjoining field(s) is the foreign key.


Question 22 :

What are triggers? What are the different types of triggers in SQL Server 2000?

Answer :

It's very beneficial for a potential database developer to know the types of triggers available, and how to implement them.

A trigger is a specialized type of stored procedure that is bound to a table or view in SQL Server 2000. In SQL Server 2000, there are INSTEAD-OF triggers and AFTER triggers. INSTEAD-OF triggers are procedures that execute in place of a Data Manipulation Language (DML) statement on a table. For example, if I have an INSTEAD-OF-UPDATE trigger on TableA, and I execute an update statement on that table, the code in the INSTEAD-OF-UPDATE trigger will execute instead of the update statement that I executed.

An AFTER trigger executes after a DML statement has taken place in the database. These types of triggers are very handy for auditing data changes that have occurred in your database tables.


Question 23 :

How can you ensure that a table named TableB with a field named Fld1 will only have those values in the Fld1 field that are also in the table named TableA with a field named Fld1?

Answer :

This relationship related question has two potential answers. The first answer (and the one that you want to hear) is the use of foreign key constraints. A foreign key constraint is used to maintain referential integrity. It is used to ensure that a field in a table will only hold values that are already defined in another field in a different (or the same) table. That field is the candidate key (usually a primary key of the other table).

The other option is the use of triggers. Triggers can be used to ensure the same effect of constraints in a roundabout way, but it is much more difficult to set up and maintain, and the performance is typically worse. Because of this, Microsoft recommends that developers use foreign key constraints instead of triggers for maintaining referential integrity.


Question 24 :

What is a performance consideration of having too many indexes on a production online transaction processing (OLTP) table?

Answer :

You are looking for the applicant to make some reference regarding data manipulations. The more indexes on a table, the more time it takes for the database engine to update, insert, or delete data, as the indexes all have to be maintained as the data manipulation occurs.


Question 25 :

What can be used to ensure that a field in a table only accepts a certain range of values?

Answer :

This question can be answered a couple of different ways, but only one answer is a "good" one. The answer you want to hear is a Check constraint, which is defined on a database table that limits the values entered into that column. These constraints are relatively easy to create, and they are the recommended type for enforcing domain integrity in SQL Server.

Triggers can also be used to restrict the values accepted in a field in a database table, but this solution requires the trigger to be defined on the table, which can hinder performance in certain situations. For this reason, Microsoft recommends Check constraints over all other methods for restricting domain integrity.


Question 26 :

What is the difference between a return parameter and an OUTPUT parameter?

Answer :

If the applicant is able to answer this question correctly, the odds are good that they have some experience working with stored procedures.
A return parameter is always returned by a stored procedure, and it is meant to indicate the success or failure of the stored procedure. The return parameter is always an INT data type.
An OUTPUT parameter is designated specifically by the developer, and it can return other types of data, such as characters and numeric values. (There are some limitations on the data types that can be used as output parameters.) You can use multiple OUTPUT parameters in a stored procedure, whereas you can only use one return parameter.


Question 27 :

What is a correlated sub-query? How can these queries be useful?

Answer :

The more seasoned developer will be able to accurately describe this type of query. A correlated sub-query is a special type of query containing a sub-query. The sub-query contained in the query actually requests values from the outside query, creating a situation similar to a loop. You can find a more detailed description as to how these special types of queries work in this article.