1. What is an Abstract Class?
Abstract class is a class that can not be instantiated. The derived classes must provide implementation for all abstract methods defined in Abstract class.
2. Can I mark Abstract class with sealed modifier?
NO
3. Is it mandatory to have at least one Abstract method in Abstract Class?
No, we can have Abstract class without abstract method.
4. Can I declare abstract members as protected or internal in abstract class?
Yes
5. Can I declare abstract members as private in abstract class?
No, Virtual or Abstract members can not be declared as private.
6. Can I declare variables in Abstract Class?
Yes, You can define data and member fields.
7. What is an Interface?
Interface is a contract it defines the signature of the functionality. If any class is implementing an interface that should provide implementation for all the methods defined in Interface.
8. Can a class implement multiple Interfaces?
Yes, Class can implement multiple interfaces.
9. Consider a scenario, I have a class which is implemented two interfaces Interface1 and Interface2, both of these
Abstract class is a class that can not be instantiated. The derived classes must provide implementation for all abstract methods defined in Abstract class.
2. Can I mark Abstract class with sealed modifier?
NO
3. Is it mandatory to have at least one Abstract method in Abstract Class?
No, we can have Abstract class without abstract method.
4. Can I declare abstract members as protected or internal in abstract class?
Yes
5. Can I declare abstract members as private in abstract class?
No, Virtual or Abstract members can not be declared as private.
6. Can I declare variables in Abstract Class?
Yes, You can define data and member fields.
7. What is an Interface?
Interface is a contract it defines the signature of the functionality. If any class is implementing an interface that should provide implementation for all the methods defined in Interface.
8. Can a class implement multiple Interfaces?
Yes, Class can implement multiple interfaces.
9. Consider a scenario, I have a class which is implemented two interfaces Interface1 and Interface2, both of these
interfaces has a method called Add()
Please answer the following questions.
Please answer the following questions.
Will it be an Issue? No
Do I need to implement two different Add () methods? If yes how can you implement in derived class?Yes, we need to provide the implementation for two Add() methods by prefixing Interface name to method name. The implementation would be like this: Interface1.Add() {——} and Interface2.Add() {—–}
How can you access a specific Add() method of a class?
Do I need to implement two different Add () methods? If yes how can you implement in derived class?Yes, we need to provide the implementation for two Add() methods by prefixing Interface name to method name. The implementation would be like this: Interface1.Add() {——} and Interface2.Add() {—–}
How can you access a specific Add() method of a class?
Interface1 obj1= new Derrivedclass();
Obj1.Add() //Invoke the Interface1.Add() Method.
Obj1.Add() //Invoke the Interface1.Add() Method.
Interface2 obj2= new Derrivedclass();
Obj2.Add() //Invoke the Interface2.Add() Method.
Obj2.Add() //Invoke the Interface2.Add() Method.
ADO.Net Interview Question on DataSet
1. What is DataSet?
DataSet is an in-memory cache of data retrieved from data source; it consists of collection of DataTable objects.
2. In which namespace DataSet is available?
System.Data
3. Can we add more than one table to DataSet?
Yes
4. Can you relate and enforce constraints on tables in DataSet?
Yes, we can relate tables in DataSet with DataRelation objects and we can enforce UniqueConstraint and ForeignKeyConstraint objects.
5. How can you clear data from all the tables in a DataSet?
DataSet.Clear() method clears the data from all tables in DataSet
6. Can I read xml document that includes both schema and data into DataSet?
Yes, ReadXml() is used to read xml document that includes both schema and data.
7. What is the difference between DataSet.Copy() and DataSet.Clone()?
DataSet.Clone() :- Copies the structure of the DataSet, i.e schema of all data tables, relations and constraints. It does not copy any data.
DataSet.Copy() :- Copies both the structure and data of the DataSet.
8. How can you save all edits/changes of data in DataSet to DataBase?
By using DataSet AcceptChanges() method, we can update database
9. What happens when you invoke Dataset’s AcceptChanges() method?
By invoking AcceptChanges on the DataSet causes AcceptChanges to be called on each table with in DataSet.
DataSet is an in-memory cache of data retrieved from data source; it consists of collection of DataTable objects.
2. In which namespace DataSet is available?
System.Data
3. Can we add more than one table to DataSet?
Yes
4. Can you relate and enforce constraints on tables in DataSet?
Yes, we can relate tables in DataSet with DataRelation objects and we can enforce UniqueConstraint and ForeignKeyConstraint objects.
5. How can you clear data from all the tables in a DataSet?
DataSet.Clear() method clears the data from all tables in DataSet
6. Can I read xml document that includes both schema and data into DataSet?
Yes, ReadXml() is used to read xml document that includes both schema and data.
7. What is the difference between DataSet.Copy() and DataSet.Clone()?
DataSet.Clone() :- Copies the structure of the DataSet, i.e schema of all data tables, relations and constraints. It does not copy any data.
DataSet.Copy() :- Copies both the structure and data of the DataSet.
8. How can you save all edits/changes of data in DataSet to DataBase?
By using DataSet AcceptChanges() method, we can update database
9. What happens when you invoke Dataset’s AcceptChanges() method?
By invoking AcceptChanges on the DataSet causes AcceptChanges to be called on each table with in DataSet.
Calling AcceptChanges at the DataTable level causes the AcceptChanges method for each DataRow to be called.
When you call AcceptChanges on the DataSet, any DataRow objects still in edit-mode successfully end their edits. The RowState property of each DataRow also changes; Added and Modified rows become Unchanged, and Deleted rows are removed.
10.How do you find a DataSet has any changes made to it, since it was last loaded?
DataSet.HasChanges() :- method returns true if the DataSet has changes; otherwise false.
11. When do you use ExecuteReader method?
Use ExecuteReader when the sql command or stored procedure that is being executed returns a set of rows.
12. When do you use ExecuteNonQuery method?
If the command or stored procedure performs INSERT, DELETE or UPDATE operations, then we use ExecuteNonQuery method. ExecuteNonQuery method returns an integer specifying the number of rows inserted, deleted or updated.
13. When do you use ExecuteScalar method?
10.How do you find a DataSet has any changes made to it, since it was last loaded?
DataSet.HasChanges() :- method returns true if the DataSet has changes; otherwise false.
11. When do you use ExecuteReader method?
Use ExecuteReader when the sql command or stored procedure that is being executed returns a set of rows.
12. When do you use ExecuteNonQuery method?
If the command or stored procedure performs INSERT, DELETE or UPDATE operations, then we use ExecuteNonQuery method. ExecuteNonQuery method returns an integer specifying the number of rows inserted, deleted or updated.
13. When do you use ExecuteScalar method?
Use ExecuteScalar, when a sql command or sp that is being executed return single value.
What is SQL Server?
SQL
Server is a DBMS system provided by Microsoft. SQL Server is
sometimes
mistakenly referred to as SQL.
Error severity 13 indicates what?
Transactional
deadlock error. This level of error severity indicates a
transaction
deadlock error.
In which order do you perform an
upgrade to SQL Server 2005 for replicated
databases?
Distributor,
Publisher and then Subscriber. You always perform an upgrade in
this
order: distributor, publisher, subscriber.
How many Service Packs will be
released for SQL Server 2005 in 2007?
Explanation:
The answer is up in the air and this is more of a poll than a real
QOD.
Based on the ways things are going, the staff here sees just 1, though our
hope
would be that 3 or 4 would be released.
You setup a linked server from a
SQL Server 2000 server to your new SQL
Server 2005 server (with
defaults), however you cannot execute procedures
on the 2005 server. Why not?
You
need to enable RPC. By default, RPC is disabled in SQL Server 2005. You
need to
set the "remote access option" in your server configuration to 1 to
allow the
execution
of stored procedures from a remote server.
What is the recommended way to
send mail from SQLAgent in SQL Server
2005?
Database
Mail. You can use either Database Mail or SQLMail with SQL Agent
in SQL
Server 2005. However since SQLMail will be removed, it is recommended that
you use
Database Mail.
When you create a new Message
Type in the SQL Server 2005 Service
Broker, what does the
Authorization parameter signify?
The
owner of the message type. This parameter determines the owner of the
message
type. This defaults to the current user.
What the heck does ATN2 do?
The
angle between the x-axis and a ray. This is a mathematical function that
returns
the angle between the positive x-axis and the ray that passes through the
two
coordinates passed in. The angle is in radians.
SQL
Server Interview Questions
http://marancollects.blogspot.com
2/35
How does a differential backup
know which extents have changed so that it
can be very quickly run?
The DCM
tracks changes. The differential backup reads the extents from this
structure.
A differential backup uses the Differential Change Map to determine which
extents
have changed and need to be include in the backup. This greatly speeds the
differential
backup process.
What does the Queue Reader Agent
do in SQL Server 2005 replication?
This
agent reads the subscriber logs and moves changes back to the
publisher.
This agent is used when the queued update model is chosen with
transactional
replication. It moves changes from the subscribers back to the
publishers.
What are the three possible
functions of the plus (+) operator in SQL Server
2005, the base installed T-SQL?
Add,
string concatenation, unary plus. The three functions are Add, String
Concatenation,
and Unary Plus.
The Sort component in SQL Server
2005 Integration Services is what type of
component?
Blocking
Transformation. The Sort component is a blocking transformation
meaning
that it needs all rows in order to perform its function.
If you received a
"Performance Critical" status in the SQL Server 2005
replication monitor, what does
this mean?
The
latency between transaction commits at the publisher and subscriber
exceeds
the warning level. This status can actually mean two different things. Either
the
latency between the commit of a transaction at the publisher and the same
commit
at the subscriber is exceeding some level in a transactional level or not
enough
rows are being processed in a merge replication scenario.
Which of the following modules
within SQL Server 2005 cannot be signed
with a digital signature?
DDL
triggers. DDL triggers cannot be signed, but all the other objects can.
What does this return?
declare @i int
select @i = -5
select +@i
-5
This
will return -5 as the result. The + operator functions as a unary plus
operator,
which means that it performs no operation on the value it preceeds.
You have installed a US English
SQL Server 2000 instance with the default
options, collation, and sorting.
What does this return?
create table MyTable ( Mychar
varchar(20))
go
insert Mytable select 'Apple'
insert Mytable select 'ant'
insert Mytable select 'Ball'
go
select * from MyTable where
Mychar like '[^a]%' Ball
This
should return "Ball" only since the ^ operator means not matching the
next
character. In this case, the first character should not be an "A".
What is the Service Broker
Identifier?
A GUID
that identifies the database on which Service Broker is running. Each
database
has a Service Broker identifier. This is a GUID in the service_broker_GUID
column
that identifies the databases on which Service Broker is running. It ensure
that
messages are delivered to the right database.
You are looking to import a large
amount of data from a remote OLEDB data
source that is not a text file.
Which of the following techniques can you use?
Use the
select * from OPENROWSET(BULK...) command. SQL Server 2005
includes
a new option with the OPENROWSET command for getting large amounts of
data
from an OLEDB data source. It is the BULK option and works similar to the
BULK
INSERT command.
How are modified extents tracked
in SQL Server 2005 (which internal
structures)?
Differential
Change Map and Bulk Change Map. There are two internal
structures
that track extents modified by bulk copy operations or that have changed
since
the last full backup. They are the Differential Changed Map (DCM) and the Bulk
Changed
Map (BCM).
What does this return? select
(1324 & 1024)
1024.
This performs a bitwise AND operation between the two integers and
sets
the result to this. Since 1024 is a single set bit in it's value, if the
corresponding
bit is
set to 1, then in the result the bit is set to 1. In this case, since no other
bits
would
generate two 1s, the result is equivalevt to the mask, or 1024.
What does the Log Reader agent in
SQL Server 2005 replication do?
This
agent reads the publisher log for transactions to send to the distributor.
This
agent is tasked with reading the transaction log in transactional replication
and
moving
those transactions that need to be replicated to the distributor.
You are performing an update of
your Scalable Shared Database and receive
note that two reports run at the
same time received different results. These
reports were both run during your
update. What type of update did you
perform?
A
rolling update. When performing a rolling update, doing the detach and
attach
on each server as opposed to detaching from all then attaching to all, it is
possible
that different reporting servers will display different results.
What does a @@fetch_status of -2
mean in SQL Server 2005?
The row
being fetched is missing. This means that the row that was being
fetched
from the cursor is missing.
You want to be sure that your
Scalable Shared Database is as available as
possible. Which of the following
is not needed for this?
Use
Database Mirroring to fail over between the old reporting database and
the new
one. The update process for a Scalable Shared database with minimal
downtime
involves putting out a new copy of the database, detaching the old
database
from each server, and then attaching the new database to each server.
What is the cost threshhold for
parallelism in SQL Server 2005?
This is
the number of seconds that a serialplan cannot exceed if it is to be
used. A
parallel plan is used if the estimate exceeds this value. This is the threshold
at
which SQL Server determines whether a serial or parallel plan is to be used.
When
SQL
Server calculates that a serial plan exceeds the threshold, it will elect to
use a
parallel
plan instead.
You have a Scalable Shared
Database setup for reporting purposes on SQL2.
You want to be able to keep a
point in time view of the reporting database
each month. What can you do?
Make a
new copy of the production database each month and then copy that
to the
SAN. Attach it as a new Scalable Shared Database each month to the
reporting
servers. A Scalable Shared Database does not support database snapshots,
so you
would have to manually create a new database each month with the data
view
you need and add this as a new Scalable Shared Database to the SAN and each
reporting
server.
You have an old database that
needs to run in compatibility mode 65 on
your SQL Server 2005 server.
Which framework would you use to manage
this database programmatically?
SQL-DMO.
SMO does not support compatibility modes 60 or 65, so you would
need to
use DMO instead.
You have two Service Broker
instances running. One is on SQL1 with the
default collation and the other
is on SQL2 setup for French collation. Which
collation is used for Service
Broker messages sent between the instances?
Service
Broker does not consider the collation, using byte-by-byte matching
for
names. Neither collation is used. Service Broker operates in a collation
independent
method that removes collation information from the messages.
What does the max full-text crawl
range option do?
Determines
the number of partitions used in an index crawl.
This
option helps optimize the full-text indexing process by specifying the number
of
partitions
the SQL Server uses during index crawls.
Which of the following is not an
allocation unit in SQL Server 2005?
TEXT_IN_ROW_DATA.
The three types of allocation units are: IN_ROW_DATA,
LOB_DATA,
and ROW_OVERFLOW_DATA. Each heap or index has IN_ROW_DATA
which
holds part of the data. LOB_DATA is used for large object data types and
ROW_OVERFLOW_DATA
is used for varible length data that causes a row to exceed
the
8060 byte limit.
Which of the following is the
best use for a Scalable Shared Database in SQL
Server 2005?
A
reporting database server. A scalable shared database is a feature that
allows
you to setup read-only database on a separate server for reporting purposes.
This
database provides an identicle view of your data from another server.
You are loading 100 rows of data
into a narrow table that is heavily used by
your production inventory
queries. It was recommended that you drop the
indexes on the table before the
load and then rebuild them after the load is
complete. Is this something you
would do?
This
does not make sense. For such a small number of rows, it is unlikely that
dropping
the indexes will improve the performance of your load. If this were 100,000
rows,
then it might make sense.
How can SQL Server Agent Mail
send messages in SQL Server 2005?
SQL
Mail through Extended MAPI or Database mail. SQL Server Agent Mail
can be
configured to use Database Mail or Extended MAPI.
What is the scale of measurement
for the cost threshold for parallelism
setting in SQL Server 2005?
Seconds.
This value measures the number of seconds for a plan where the
optimizer
chooses between serial and parallel plans.
Which of the following statements
best describes the filter capabilities of
Report Builder?
Users
can do equals, greater than, less than, etc, plus they can do logical
AND,
OR, NOT operations. Users can also group filters to allow more advanced
filters.
While it looks a little different than you may be used to, the filter builder
is
reasonably
rich, allowing most standard evaluation types and rich boolean
comparisons.
The only weak spot in the set is no support for LIKE, you have to make
do with
CONTAINS.
True or false, Report Builder supports
user defined run time parameters?
True.
Users can define any portion of a filter to be a run time prompt, letting
other
users easily change the filter as needed. Not only is it easy to set up, Report
Builder
automatically populates a list of all possible choices based on the column
being
filtered.
Using Report Builder that is
bundled with Reporting Services 2005, which of
the following would work as a way
to add a derived field to a report?
Add a
field to the model in Report Builder using the built in formula/function
support.
While Report Builder cannot be used to build or maintain models, it does
allow
you to add a virtual field that exists only within that report - to the end
user it
looks
like the model is being modified.
If you absolutely need a report
to look the same regardless of what OS or
viewing software is being used,
which of the following file formats would be
the best choice?
TIFF.
By rendering as an image there are no concerns about different fonts,
problems
with page breaks, etc. PDF's are almost as good and more commonly used
of
course, with the advantage that document maps are translated to bookmarks that
are
usable - something not possible with a raw image.
True or false, Report Builder
offers direct export to a Microsoft Access
database as one of its supported
export options?
False.
Supported formats include Excel, CSV, XML, TIFF, MHTML, and PDF.
XML or
CSV could be easily imported into Access, but there is way to add the data
directly
from Report Builder
When discussing image support in
Report Builder, which of the following is
the most accurate statement?
Users
can add one or more images, but they will appear in the header or
footer
of the report, they cannot be added to the detail row. Images that are stored
as row
data in the database can be rendered at the detail level.
Multiple
images can be added, but they appear in the header or footer depending on
where
placed on the report. The only way to get an image at the detail level (row
based)
is to have it be part of the database and included in the model.
Using Report Builder, which of
the following is the best statement about the
formatting options for Boolean
columns?
Booleans
are formatted as True/False and there are no other built in options,
but you
could build an expression using IIF that would let you do other formatting
From
the format dialog there are no extra formatting options for Booleans, Report
Builder
renders them as True/False. Writing an expression that you add to the model
view is
the easiest way to work around this limitation.
What is the easiest way to
capture the SQL statement for a Report Builder
report you're troubleshooting?
Run
Profiler. Profiler will work as long as you have permissions to profile the
server
and is the the best solution because it requires no change to the Report
Server
itself. There is a way to log all report SQL to a log file, but that option was
not
listed
here and is better used if you want to do analysis rather than troubleshooting.
Clicking File, Save in Report
Builder does which of the following?
Saves
the report to the report server. File|Save writes the report to the
Report
Server. Users have the option to also save the report to disk by using
File|Save
to File. Report Builder users cannot modify the model.
Which of the following choices
show the three report formats supported by
Report Builder?
Table,
Matrix, Chart. Report Builder can build a report formatted as a table,
chart,
or matrix (cross tab), but only ONE can be used in any given report.
Using Report Builder, which of
the following statements is correct about
formatting numbers?
Users
can pick from a small number of predefined formats and they have the
option
to specify a custom format. There are give built in formats; general, currency,
percentage,
two place decimal, and exponent. Users can also define a custom format
using a
.Net format string.
True or false, Report Builder
supports using the LIKE function inside filters?
False.
There is no LIKE support, the next best thing is the CONTAINS function
which
works as if you specified both a leading and trailing wild card.
Which RAID levels store parity
information?
RAID 5.
Only RAID 5 (of those listed) contains parity information.
You have a large table that you
wish to partition to improve performance.
The table contains many columns
of data about customers and you decide
that basic information about each
customer will remain in the current table.
Extended information, such as
shipping instructions, secretaries' names,
etc. will be moved to a new table
along with the PK. What type of
partitioning is this?
Vertical
partitioning. If you are moving some columns from one table to a new
table,
this is vertical partitioning.
On which platforms can you use
Instant File Initialization to improve
database growth performance in
SQL Server 2005?
Windows
2003 and XP Pro. Both Windows 2003 Server and later as well as
Windows
XP Professional support Instant File Initialization.
You have created a database
snapshot on SQL Server 2005 for the sales
database to capture the
end-of-month activity. The next day your server
fails and you need to recover to
a standby server using the previous night's
backups. How do you recover the
snapshot?
There
is nothing you can do. The snapshot is lost. Database snapshots cannot
be
backed up, so once the server failed, the database snapshot was lost.
Using Reporting Services 2005, it
is true or false that subreports execute a
query against the datasource once
for every detail row in the report?
True.
Subreports can be used for a master-detail relationship, or the
subreport
can be a separate item, but in either case RS will query to get the data for
the
report once for each detail row. If end users are going to only occasionally
look
at the
data you're displaying in the subreport or only view it for a few rows, a
better
option
is to create a link to the other report.
You have noticed in both your SQL
Server 2000 and 2005 instances that
when a database grows in SQL
Server, there is a delay in the database
response. Why is that?
Once
the file is grown, zeros are written to the new space, causing a delay.
When a
database file grows, unless instant file initialization is turned on, the
server
must
allocate disk space and then write 0s into all that space. This zero-ing out of
the
file creates the delay.
Which utility is used to
administer SQL Server 2005 Notification Services
instances?
nscontrol.exe.
The nscontrol application can be used with various parameters
to
administer a SQL Server 2005 Notification Services instance.
After you've completed a backup
for your Sales server to disk, you want to
be sure that this backup is
intact and able to be used for restores before
writing it to tape. What command
will help you here?
RESTORE
VERIFYONLY. After a backup file has been written to disk or tape,
its
integrity can be checked with the RESTORE VERIFYONLY command. This
command
will verufy the backup set is complete and that the files are readable.
Out of the box Report Builder
supports two report level fields that can be
shown on a report. Which option
below has those two options?
The
current filter and the number of rows that matched that filter
By
default, the current filter definition and the number of rows that matched the
filter
are added to the end of the report. They can be removed and added back as
needed.
Can a particular event in SQL
Server 2005, such as the CREATE USER
command, have more than one DDL
trigger assigned to it?
Yes. An
event can have multiple triggers assigned to it.
You accidently delete an
application from an instance of SQL Server 2005
Notification Services. However
you have not removed the database, nor the
application objects. Can you
re-associate the application with the same
SSNS instance?
No. You
cannot re-assocaite the application because when you add the
application,
SSNS recreates the objects. If they already exist, the create fails.
How many users can be added to an
application role in SQL Server 2005?
None.
This is a trick questions. No users are added to application roles.
Application
roles are invoked by a user.
What algorithm is used to encrypt
the Database Master Key when it is
created?
Triple
DES. When you create a Database Master Key, it is encrypted using the
password
you supply with the Triple DES algorithm.
Which protocols support Kerberos
authentication on SQL Server 2005?
TCP/IP.
Only those clients connecting with TCP/IP can use Kerberos
authentication.
You have a few new SQL Server
2005 server instances and you want to be
sure that SQL authenticated
logins must abide by the password policy. On
this platforms can you enforce
this?
Windows
2003 Server. You can only enforce password policy on the Windows
2003
Server platform and newer.
You are setting up a native XML
web service on your SQL Server 2005 to
respond to inventory requests.
How can you you be sure that a SOAP
queries that are looking for a
WSDL response will be provided?
Use the
WSDL=DEFAULT parameter in the create endpoint statement.
When
using the CREATE ENDPOINT command, you can specify the WSDL=DEFAULT
parameter
to generate a default WSDL response or use WSDL="spname" where
spname
is the name of a custom stored procedure to return WSDL responses.
Where can
you view the list of server-scoped DDL triggers?
The
Object Explorer for the server has a "Triggers" folder.
The
server-scoped DDL triggers will appear in Management Studio in the Object
Explorer
under the "Triggers" folder.
You want to be sure that your reporting
solutions using a database snapshot are
properly protected from disaster. How can
you back up your database snapshots?
You
cannot back up a database snapshot.
A
database snapshot cannot be backed up or restored.
When you install SQL Server 2005 and
create a new database, is a Database Master
Key created?
No
A
database master key is not created when a database is created. It must be
created
by an
administrator.
What does the DEALLOCATE statement do in
SQL Server 2005?
Remove
a reference to a cursor.
This
statement is used to remove cursor references. If this is the last reference
being
removed,
the cursor's resources are released.
Report Builder has no built in support
for source control. Which of the following
choices could be implemented if you
decided that those reports needed to be under
source control?
Have
users save the RDL files locally and check into source control using a source
control
utility, or write code to script them out each night and check them into
source
control
Users
can do faux source control by just saving copies of their RDL files locally
before
they
make a change. Beyond that, you either teach them how to use a source
control
utility or you write a utility you can run on a schedule.
Setting
the TRUSTWORTHY database property to ON allows you to execute the
following
from within the context of the database
Objects
from unsigned CLR assemblies with EXTERNAL_ACCESS or UNSAFE
permissions
In
order to execute objects from CLR assemblies with EXTERNAL_ACCESS or UNSAFE
permissions,
the assemby must either be signed with a key pair or the database
hosting
the assembly must have the TRUSTWORTHY database property set to ON.
Executing
the other answers is essentially controlled by permissions. Setting the
TRUSTWORTHY
database property to ON also allows you to use the EXECUTE AS
clause
to allow impersonation outside the scope of the database within the instance
but
this was not one of the answers.
What tool is available for ad hoc
reporting by non IT end users in Reporting Services
2005?
Report
Builder
Report
Builder is bundled as part of Reporting Services 2005 and is targeted at end
users
needing to build ad hoc reports. BI Design Studio could be used, but typically
requires
more time and experience to use than the average end user will be willing
to
invest.
You have just upgraded your SQL Server
2000 instance on Windows 2000 to SQL
Server 2005. You are looking to begin
implementing encryption capabilities and need
to choose an algorithm for your data.
Which of the following is not available to you?
128-bit
AES
While
all of these are valid encryption algorithms on SQL Server 2005, the AES
algorithms
(128-bit, 192-bit, and 256-bit) are not supported on Windows XP or
Windows
2000. To use these, you would need to upgrde to Windows 2003.
Which types of replication work with
Oracle publishers?
Snapshot
and transactional
Oracle
publishing with SQL Server works as both snapshot and transactional
publication
issues.
What does @@textsize return?
The
current value of the TEXTSIZE option for the SET command.
This
returns the current value of the TEXTSIZE option that can be changed with the
SET
command. The default is 4096 bytes.
In
configuring the thesaurus configuration file for SQL Server 2005 Full-Text
Search,
you set
the diacritics_sensitive value to 1. What does this mean?
It
means the sets in this file are accent sensitive.
The
diacritics_sensitive determines if the terms in the expansion and replacement
sets
are accent sensitive. A value of 0 means accent insensitive and a value of 1
means
accent sensitive.
Before
a user can build reports using Report Builder in Reporting Services 2005,
which one of the following steps must be
done first?
Build
and deploy a model
Report
Builder requires a model and that model cannot be defined using Report
Builder.
The advantage of using models is that users do not need to understand SQL
statements
to build a report (but they will still benefit from a basic understanding of
the
relationships between various bits of data)
What does the CREATE SERVICE statement do
in SQL Server 2005?
This
statement is used to setup new tasks for Service Broker queues.
This
statement is ue to create a new Service Broker service that is a set of tasks
that
are run
on messages.
You have a SQL Server 2005 sales database
experiencing performance problems
because of heavy I/O activity. You decide
to create a snapshot of this database every
hour, stored on a separate physical disk
array and use that for reporting queries.
However the queries still seem slow
immediately after snapshot creation. Why?
The
snapshot still queries the original database.
The
snapshot of the production database will only reduce I/O for changed pages. Any
data
that has not been changed will be read from the source database, which is in
this
case is experiencing heavy I/O itself.
In
Reporting Services 2005, how
is Report Builder typically deployed to end users?
One
click deployment launched from a menu on the Report Manager home page
One
click deployment is fast and easy, users click the menu on the Report Manager
page.
It is not a web application, but rather a .Net Winform application.
Native
Web Services require what
type of endpoint in SQL Server 2005?
HTTP
endpoints
Native
XML Web Services in SQL Server 2005 require HTTP endpoints to
communicate
with clients.
If you
have a replication scenario with one publisher, one distributor and one
subscriber
and the transaction isolation level is not set to serializable, what happens
when a transaction fails on the
publisher?
It is
still sent to the distributor and executed on the subscriber.
A
transaction that fails in the publication database is still sent to the distributor
and
subscriber.
YUou can trap the error, but you would need to trap it on the subscriber
as
well.
When
creating a full-text index on a BLOB column (image, binary or varbinary)
where a file is stored, what happens if
an incorrect file prefix is stored in the type
column? (For
instance: if a word document is stored in the image or a
varbinary(MAX)
column and the value of the type column is '.pdf'.
The row
is not full-text indexed and an error message is written to the SQL Server
Error
Log
The
answer is the row is not full-text indexed and an error message is written to
the
SQL
Server Error Log. No columns in the row are full-text indexed and an error
message
is written to the SQL Server Error Log.
In SQL
Server 2005 Full-Text Search, you set up a replacement set like this:
<replacement>
<pat>SS2K5
</pat>
<sub>SQL
Server 2005 </sub>
<sub>SQL
Server 2K5 </sub>
</replacement>
A
search for "SS2K5" doesn't return results with fields that you know
contain
"SS2K5".
What is wrong?
The
replacement set does not return results with the search terms, only the
specified
replacements.
In the SQL Server 2005 thesaurus XML
configuration file, what is the expansion set?
Expansion
sets are synonyms for the search term and returned as results if they
appear
along with the search term.
The
expansion set is the group of values that are synonyms and can be substituted
for the
search term. For example, an expansion set can be "SS2K5", "SQL
Server
2005",
"SQL2K5". In this case, fields with any of these 3 values would be
returned as
a
result for searches on "SQL Server 2005".
You are
initiating a new replication subscription for one of the remote offices, but
don't
want to transfer it across the network. The snapshot files are 725MB, too large
for
your CD burner and there is no tape drive at the remote office. What feature of
SQL
Server 2005 would help you get the snapshot transferred?
Compressed
Snapshots
In SQL
Server 2005, you can specify a snapshot to be compressed and it will be
compressed
using the CAB format. If it compresses enough, you should be able to
burn it
onto a CD.
The TRUSTWORTHY database property is by
default?
Off
The
correct answer is off – The TRUSTWORTHY database property indicates whether
the
installed instance of SQL Server trusts the database such that it can reach out
and
perform actions outside the database. By default it is OFF such that the
database
to
reduce certain threats that can result from attaching a database that contains
potentially
harmful code
Where are Notification Services event
messages logged in SQL Server 2005?
In the
Windows Application Log
Event
messages are logged in the Windows Application log.
What
would happen when you execute the code below in Query Analyzer (SQL
Server
2000)
USE
Northwind
GO
CREATE
PROCEDURE sp_who
AS
PRINT
'SURPRISE'
GO
EXECUTE
sp_who
Information
about current SQL Server users and processes is displayed.
The
explanation could be found in the Books Online under Creating a Stored
Procedure.
One of the sections, named System Stored Procedures which describes
how SQL
Server looks up the system stored procedure has this note: Important If
any
user-created stored procedure has the same name as a system stored
procedure,
the user-created stored procedure will never be executed.
How is Thesaurus data configured in SQL
Server 2005 Full-Text Search?
An XML
file in the file system named tsxxx.xml where xxx is a code.
The
thesaurus file for SQL Server 2005 is an XML file containing data and stored in
SQL_Server_install_pathMicrosoft
SQL ServerMSSQL.1MSSQLFTDATA directory.
There
is one for each language and it is named tsxxx_.xml, where xxx is the three
letter
language code.
What
does a report model provide in SQL Server 2005 Reporting Services?
A
report model provides familiar business names for database tables and fields
A
Report model provides business names and terms for database fields and tables.
It
allows
for predefined relationships between tables as well as grouping items together
logically.
What can tracer tokens measure in SQL
Server 2005 transactional replication?
1 and 3
Tracer
tokens measure two things. One is the amount of time elapsed between a
command
being committed on the publisher and being written to the distribution
database.
The other is from the writing to the distribution database and being
committed
to a subscriber. This allows you to determine the latencies for
transactions
moving through your replication topology.
You
want to script the execution of an Integration Services package from the
command
line for use from a Unix scheduler. What utility would you use?
dtexec.exe
The
dtexec.exe utility is used to configure and execute Integration Services
packages
from
the command line.
What does the sqlwb utility do?
Opens
SQL Server 2005 Management Studio.
sqlwb.exe
actually opens Management Studio and can be configured to optionally
open a
solution, project, or script file when it starts.
You
want to automate the installation of SQL Server 2005 using SMO for your
custom
application and ensure that the encryption features are available with a
service
master key. What method
would you call to create this key?
The
Regenerate method under the ServiceMasterKey object.
To
create a Service Master Key in SMO, you would get a handle to the
ServiceMasterKey
object under the Server object and then call the Regenerate
method.
How can
you determine which Service Broker ports are being used on your server?
Query
the system catalog view: sys.conversation_endpoints
The
system catalog view sys.conversation_endpoints will show you which Service
Broker
endpoints, and therefore ports, are open on your server.
In
Full-Text Search, what is
word breaking?
Determining
word boundaries.
Word
breaking involves finding the boundaries of a word. This is different for
different
languages and SQL Server 2005 includes word breakers for 23 languages.
Can you
call Notification Services APIs from unmanaged code?
Yes,
but only for simple subscriptions.
Notification
Services can be called from unmanaged code through a COM interface
for
simple subscriptions only. Condition based subscriptions are not supported
through
COM Interop.
Which of the following is true about the
Raw File Source in SQL Server 2005
Integration Services?
It does
not support an error output.
The raw
file source has only one output and does not support an error output. It also
reads
faster than other data sources because it has no parsing or translation and
does
not use a connection manager.
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 FilesMicrosoft SQL
Server90NotificationServicesn.n.nnnbin
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.
In SQL
Server 2005, you want to monitor your transactional replication and
determine
how long it takes for a transaction to move from the publisher to a
subscriber.
What would you use to help
you do this?
Tracer
Tokens.
You can
use tracer tokens in SQL Server 2005 replication to measure latencies.
These
are small amounts of data written into your transaction logs.
How
many credentials can one login be mapped to?
1
A
credential can be mapped to many logins, but a login can only be mapped to one
credential.
You are
looking to secure your publisher to prevent unauthorized logins from
accessing
the data through replication. Where
do you add the list of logins that have
access?
Add
them to the Publication Access List in the publication database.
All
access to publications is controlled with the Publication Access List (PAL),
which is
similar
to the Windows ACLs. A list is created for each publication that is set up.
What
does the ERROR_PROCEDURE() function return?
The
name of the stored procedure that caused an error.
The
ERROR_PROCEDURE() function is placed in the catch block of a TRY..CATCH
construct
and returns the name of the procedure that caused an error to be thrown.
Credentials in SQL Server 2005 are used
to access what?
Resources
outside the SQL Server
A
credential is used to access resources outside of SQL Server. It contains the
authentication
information needed for accessing these resources.
Which of the following is an invalid file
name for a database file?
None of
the above
Explanation
from BOL: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file
name
extensions, but these extensions help you identify the different kinds of files
and
their use.
What does @@options return?
The
current SET options for the connection.
The
@@options variable returns a list of the options set for the current
connection.
This is
returned as integer and each bit in the integer represents an option.
You
have setup forwarding for your SQL Server 2005 Service Broker queues to
another
server. You edit a route to move a queue to a new server. What do you need
to do now to ensure you can recover the
routes in the event of a disaster?
Back up
the msdb database.
The
routes for forwarding are stored in msdb.sys.routes. If you back up the msdb
database,
the routes will be captured.
You are
looking to setup a large Notification Services application and you are
planning
on a 40GB database. When installing Notification Services, the creation
fails.
What is the likely problem?
Notification
Services installation has a 10 minute timeout and the database creation
probably
took longer than that.
The
Notification Services timeout for database creation is 10 minutes. If the
database
takes longer to create, the installation of the instance fails. You need to
reduce
the size of the database for installation and then increase it when you are
done.
You
have enabled AWE for your SQL Server 2005 server and allocated 4GB or RAM
on one
of your servers. An ASP.NET application on the same server is feeling
memory
pressure and you want to release some memory and reduce SQL Server to
3GB or
RAM. How can you do this?
Change
the max amount of memory allocated and shut down and restart SQL Server
to have
it take affect.
Once
memory is allocated through AWE, it cannot be released unless the SQL Server
is
restarted.
You are
troubleshooting a Service Broker application and find that messages are
remaining
on the queue. You determine that there is no active queue monitor. What
should you do?
Use the
ALTER QUEUE statement to turn activation on.
One of
the troubleshooting steps if messages remain in the queue is to check for an
active
queue monitor. If one is not on, then it needs to be activated. The ALTER
QUEUE
statement is used to change this.
You are
implementing replication across the Internet for a large bank that wishes to
move up
to date pricing information to an analyst's laptop. They are concerned about
spoofing
of their main site by criminals. What can you do to secure the replication
solution with SQL Server 2005?
Set the
encryption level to verify the certificate is issued by a trusted authority.
Since
you are asked to use replication, a replication solution is to use encryption
for
the
connection and verify the certificate being used is from a trusted authority.
Setting
the encrpytion level to 2 will do this.
In SQL
Server 2005, you are looking to implement full-text search. One of the tables
you are
looking to index stores Mircrosoft Word documents in a varbinary(max)
column.
Can you use Full-text search to index this column?
Yes
You can
use full-text search for formatted data such as Word that contains text
stored
in a varbinary column.
To recover an instance of Notification
Services in 2005 on another server, what
information would you need?
A
backup of the database holding Notification Services and the XSD and XSLT files
as
well as
the name and password for the service account.
To
recover SSNS, you need the database backup as well as the operational files,
XSD
and
XSLT files, stored on the file system and the account information used for the
service
account. Since SSNS usually works with data outside of the SQL Server, a
domain
account is usually used to ensure proper permissions.
What type of connectivity does the
readpipe/makepipe utility test?
Named
Pipes
The
readpipe and makepipe utility combination will test named pipe connectivity.
In SQL
Server 2005, which of the following schema changes are supported for the
publication
objects of a replicated database?
All of
the above.
SQL
Server 2005 replicated databases support the following schema changes for
objects:
* ALTER
TABLE
* ALTER
VIEW
* ALTER
PROCEDURE
* ALTER
FUNCTION
* ALTER
TRIGGER (DML only)
You
want to disable the receipt of messages in one of your databases by the Service
Broken. How should you do this?
ALTER
DATABASE Sales SET DISABLE_BROKER
To
disable the receipt of messages, you can disable the Service Broker by
disabling it
with
the ALTER DATABASE command. The SET command is "DISABLE_BROKER".
You are
building a .NET assembly that will access the registry of the local machine
for a
factor used in a computer column.
What permission set should you assign it?
EXTERNAL_ACCESS
The
most restrictive permission set should always be used for .NET assemblies in
keeping
with a secure SQL Server environment. Only the EXTERNAL_ACCESS and
UNSAFE
permission sets will allow registry access and UNSAFE permissions are not
required.
In SQL
Server 2005, you want to monitor your transactional replication and
determine
how long it takes for a transaction to move from the publisher to a
subscriber.
What would you use to help
you do this?
Tracer
Tokens.
You can
use tracer tokens in SQL Server 2005 replication to measure latencies.
These
are small amounts of data written into your transaction logs.
How many credentials can one login be
mapped to?
1
A
credential can be mapped to many logins, but a login can only be mapped to one
credential.
You are
looking to secure your publisher to prevent unauthorized logins from
accessing
the data through replication. Where
do you add the list of logins that have
access?
Add
them to the Publication Access List in the publication database.
All
access to publications is controlled with the Publication Access List (PAL),
which is
similar
to the Windows ACLs. A list is created for each publication that is set up.
What does the ERROR_PROCEDURE() function
return?
The
name of the stored procedure that caused an error.
The
ERROR_PROCEDURE() function is placed in the catch block of a TRY..CATCH
construct
and returns the name of the procedure that caused an error to be thrown.
Credentials in SQL Server 2005 are used
to access what?
Resources
outside the SQL Server
A credential
is used to access resources outside of SQL Server. It contains the
authentication
information needed for accessing these resources.
Which of the following is an invalid file
name for a database file?
None of
the above
Explanation
from BOL: SQL Server 2005 does not enforce the .mdf, .ndf, and .ldf file
name
extensions, but these extensions help you identify the different kinds of files
and
their use.
What does @@options return?
The
current SET options for the connection.
The
@@options variable returns a list of the options set for the current
connection.
This is
returned as integer and each bit in the integer represents an option.
You
have setup forwarding for your SQL Server 2005 Service Broker queues to
another
server. You edit a route to move a queue to a new server. What do you need
to do now to ensure you can recover the
routes in the event of a disaster?
Back up
the msdb database.
The
routes for forwarding are stored in msdb.sys.routes. If you back up the msdb
database,
the routes will be captured.
You are
looking to setup a large Notification Services application and you are
planning
on a 40GB database. When
installing Notification Services, the creation
fails. What is the likely problem?
Notification
Services installation has a 10 minute timeout and the database creation
probably
took longer than that.
The
Notification Services timeout for database creation is 10 minutes. If the
database
takes longer to create, the installation of the instance fails. You need to
reduce
the size of the database for installation and then increase it when you are
done.
You
have enabled AWE for your SQL Server 2005 server and allocated 4GB or RAM
on one
of your servers. An ASP.NET application on the same server is feeling
memory
pressure and you want to release some memory and reduce SQL Server to
3GB or
RAM. How can you do this?
Change
the max amount of memory allocated and shut down and restart SQL Server
to have
it take affect.
Once
memory is allocated through AWE, it cannot be released unless the SQL Server
is
restarted.
You are
troubleshooting a Service Broker application and find that messages are
remaining
on the queue. You determine that there is no active queue monitor. What
should you do?
Use the
ALTER QUEUE statement to turn activation on.
One of
the troubleshooting steps if messages remain in the queue is to check for an
active
queue monitor. If one is not on, then it needs to be activated. The ALTER
QUEUE
statement is used to change this.
You are
implementing replication across the Internet for a large bank that wishes to
move up
to date pricing information to an analyst's laptop. They are concerned about
spoofing
of their main site by criminals. What can you do to secure the replication
solution with SQL Server 2005?
Set the
encryption level to verify the certificate is issued by a trusted authority.
Since
you are asked to use replication, a replication solution is to use encryption
for
the
connection and verify the certificate being used is from a trusted authority.
Setting
the encrpytion level to 2 will do this.
In SQL Server 2005, you are looking to
implement full-text search. One of the tables
you are looking to index stores
Mircrosoft Word documents in a varbinary(max)
column. Can you use Full-text search to
index this column?
Yes
You can
use full-text search for formatted data such as Word that contains text
stored
in a varbinary column.
To recover an instance of Notification
Services in 2005 on another server, what
information would you need?
A
backup of the database holding Notification Services and the XSD and XSLT files
as
well as
the name and password for the service account.
To
recover SSNS, you need the database backup as well as the operational files,
XSD
and
XSLT files, stored on the file system and the account information used for the
service
account. Since SSNS usually works with data outside of the SQL Server, a
domain
account is usually used to ensure proper permissions.
What type of connectivity does the
readpipe/makepipe utility test?
Named
Pipes
The
readpipe and makepipe utility combination will test named pipe connectivity.
In SQL
Server 2005, which of the following schema changes are supported for the
publication
objects of a replicated database?
All of
the above.
SQL
Server 2005 replicated databases support the following schema changes for
objects:
* ALTER
TABLE
* ALTER
VIEW
* ALTER
PROCEDURE
* ALTER
FUNCTION
* ALTER
TRIGGER (DML only)
You
want to disable the receipt of messages in one of your databases by the Service
Broken.
How should you do this?
ALTER
DATABASE Sales SET DISABLE_BROKER
To
disable the receipt of messages, you can disable the Service Broker by
disabling it
with
the ALTER DATABASE command. The SET command is "DISABLE_BROKER".
You are
building a .NET assembly that will access the registry of the local machine
for a
factor used in a computer column. What permission set should you assign it?
EXTERNAL_ACCESS
The
most restrictive permission set should always be used for .NET assemblies in
keeping
with a secure SQL Server environment. Only the EXTERNAL_ACCESS and
UNSAFE
permission sets will allow registry access and UNSAFE permissions are not
required.
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.
What does this return?
SELECT
USER_NAME() -- Returns Andy
EXECUTE
AS TechPreparation
GO
EXECUTE
AS Steve
GO
REVERT
GO
SELECT
USER_NAME()
TechPreparation
The
execution context switches can be nested, so changing to TechPreparation, then
Steve,
then issuing a Revert will return you to the context of TechPreparation. This
works
in the same manner as a stack.
In
building a PDF report in Reporting Services 2005, images that are originally
stored
in jpg
format are rendered in jpg format. What format are images rendered in if they
were stored originally in another format?
png
Images
stored originally in formats other than jpg are rendered in png format.
What does the tablediff utility do?
This
utility will report the differences in data between two tables.
This
utility will report the data differences between two tables. They must be alike
in
structure
and give back the T-SQL to bring the data in the tables to be the same for
most
datatypes.
Which
severity levels of errors can be corrected by a user (as opposed to those that
an
administrator or Microsoft must work to fix.)
11-16
Severity
levels 11-16 are correctable by users. Below 11, these are informational
warnings
and not raised as system errors. Errors above 16 must have an
administrator
or Microsoft correct.
What is the granularity in seconds of the
timestamp datatype?
The
timestamp datatype does not store time values.
The
answer is the timestamp datatype does not store time values. The better name
for
this datatype is rowversion, and it is actually a database-wide unique varbinary
value.
When you have a rowversion column in a table, the value of the rowversion
column
changes for each modification to each row. The value in the rowversion
column
is guaranteed to be unique across all tables in the datatype.
You are
setting up the backup scheme for your SQL Server 2005 server and want to
setup
nightly full backups and hourly log backups in the Maintenance Plans
subsystem. How many plans must you setup?
2
Since
you have two separate schedules for the maintenance operations, you will
need to
have two separate plans to handle this need. Each plan can only be executed
on one
schedule, so one is needed for a single daily execution, the nightly full
backups,
and another for the hourly log backups.
You
have installed one new assembly on your SQL Server 2005 server and are
wondering
if it is being used on the production server. How can you easily monitor to
see if this assembly is being used?
You can
monitor the Assembly Load event in a trace.
While
you could scan trace results for the names of functions and procedures using
the
assembly, it is possible that you may not know all the places the assembly is
being
called from. There is a CLR Load event class that will capture an event when a
CLR
class is executed.
You
wish to ensure you can recover your SQL Server 2005 database to a point in
time if
necessary. Which recovery
models can you use?
Full
Only
the Full recovery model supports point in time restoration of your database.
Does a root element in an XML document
necessarily contain all the content for a
well-formed document?
Yes
By
definition, the root element is required in a well-formed XML document and it
contains
all other content nested inside it.
Which of the following datatypes can be
represented in a SQL_VARIANT datatype?
None of
the above.
The SQL
Variant type can store all datatypes except varchar(max), varbinary(max),
xml,
text, ntext, rowversion/timestamp (thought the data of a rowversion can be
stored
in a binary(8), which can be stored in a sql_variant) and sql_variant (it may
seem
strange that you can’t store a variant in a variant, but all this is saying is
that
the
sql_variant data type doesn’t actually exist as such—SQL Server chooses the
best
type of storage to store the value you give to it).
What's the difference between a server
login and a database user?
A
server login connects an account to the server. A database user the link from
the
server
login to a defined database .
You
need to create a server login using CREATE LOGIN before you can then link it to
a
database using a database user with CREATE USER. The server login logs into the
server
and grants access to any server wide permissions. The server login is then
mapped
to a database user for access to database objects.
For
regulatory reasons, you must maintain an exact text copy of your XML
documents
in your SQL Server 2005 database. What datatype of storage should you
choose?
varchar(max)
The XML
data type changes the representation of your XML document into an
internal,
optimized version that has all the content, but may not maintain the original
text.
The varchar(max) or nvarchar(max) represenations should be used in this case.
What is a bookmark lookup?
An
operation where the row in the heap or clustered index is found from the
bookmark
in the index.
A
bookmark ID is stored in an index and points back to the actual row in the heap
or
clustered
index of the table. The lookup operation occurs when an index satisfies a
search
and the actual row is retrieved from the table.
Yesterday's
QOD How are comments denoted
in an XML document?
With
the <!-- and --> markers.
Comments
in an XML document are denoted just as they are in HTML with the
markers.
How secure are encrypted stored
procedures, triggers and views?
They
are not really secure, that are only obfusticated. There are tools that you can
use to
get at the code.
The
code itself is just obfusticated. However, you cannot edit the code, the stored
procedure
or view would have to be dropped and re-created. By combing this fact
with a
DDL trigger that executes on object creation you can tell when it has been
modified.
You have created a new assembly and want
to test it on SQL Server 2005. You
install the developer edition with
defaults on your workstation and create the
assembly as a sysadmin. You then write a
function to use this assembly, but it does
not work. What could be the problem?
The CLR
environment needs to be enabled.
By
default on SQL Server 2005, the CLR runtime environment, which is what
executes
the .NET assemblies, is not enabled. You need to execute the following
code to
enable it:
The
Service Broker operates on messages in which fashion?
Like a
queue, first in, first out.
Messages
transferred in Service Broker work in a queue fashion with the first
message
being sent being the first processed.
You
have a user that agrees to take over some of the database administration for
your SQL Server 2005. They will be in
charge of granting access to one particular
database used by the time card
application for server logins. What security role
should you assign them?
db_accessadmin
This
user will not add logins, but rather grant database level access for existing
logins,
therefore the minimum role they need is the db_accessadmin fixed database
role.
This allows them the ability to add access for Windows logins, Windows group
logins,
or SQL Server logins.
What does this method in SQL Server
2005's SMO equate to in T-SQL?
db.CheckTables(RepairType.None)
DBCC
CHECKDB WITH NO_INFOMSGS
This
SMO method executes a DBCC CHECKDB with the NO_INFOMSGS option set.
In SQL Server 2005, can you include a
user-defined role as a member of another
user-defined role?
Yes
Yes you
can.
In SQL Server 2005, what does instance
aware mean for a service?
The
service is installed once for each instances on the server.
An
instance aware service is one that is installed for each instance on the
server. An
instance-unaware
service is only installed once , no matter how many instances.
Integration
Services is instance-unaware whereas SQL Agent is instance aware.
If you
use the EXECUTE AS clause to impersonate a user at a database-scoped level.
Can you
execute a query through a linked server if the impersonated user has the
rights
to do so when normally logged in? No trust relationships exist between the
databases.
No
If you
change your execution context to a user, which is scoped at a database level,
then
any queries to linked servers or other databases will fail. There is a way
around
this
restriction if trust relationships are setup.
The
nesting level for stored procedures in SQL Server 2005, but how many
databases can you access inside one
stored procedure?
8
The
footnote for nested stored procedures limits the number at 8.
Is it possible to build an index on a
view?
Yes
Yes, an
index can be put on a view but the first index must be a unique clustered
index.
What does REVERT do in SQL Server 2005?
Restores
your previous execution context.
If you
have changed your execution context with EXECUTE AS, the REVERT
statement
will restore the last context prior to the EXECUTE AS.
What
does SEND do in SQL Server 2005?
Sends a
service broker message using a conversation.
SEND is
used to send a message on an existing conversation in the Service Broker
architecture.
In SQL Server 2005, most data for your
data types is stored in the pages for the
table. Binary objects, such as image or
text, are stored in LOB or large Object data
pages. Where is varchar(max) data stored?
In LOB
pages
Since
the size restrictions for varchar(max) are the same as for text data, this data
is
stored in LOB data pages.
What will occur with the following code
in SQL Server 2005 if executed by Alice?
execute
as user = 'Bob' with no revert
select
user_name()
go
revert
select
user_name()
go
You
will receive as results:
bob
An
error
The NO
REVERT option with EXECUTE AS prevents the return of execution context to
the
previous value. If you run the REVERT statement, you will receive the following
error:
You have to create some
T-SQL that produces an order of players in a golf
tournament. Players who
finish on the same number of strokes have to be given the
same finishing place
denoting that they will have an equal prize amount. What T-SQL
function can achieve this?
RANK
alongside the OVER function where the OVER function uses the players
number
of strokes taken
The
rank function will return the rank of the row in the result set. Ties will
receive
the
same rank. The OVER clause will separate out the ranking into partitions and
use
those
for calculating the proper placement of the golfers.
You want to disable the trigger dEmployee
on the HumanResources.Employee table
in the SQL Server 2005 AdventureWorks
sample database. Which of the following
statements will do the trick?
Answer
A and B
Explanation
Either
of the commands in A and B will disable the trigger.
In SQL Server 2005, if you want the keys
securing your data to expire over time,
what type of key encryption would you
choose?
Answer
Certificates
Explanation
Certificates
have an expiration data associated with them. This allows you to issue
them to
users and force a reissue in order to maintain their access to data.
Which is larger in size in SQL Server
2005 if you are using symmetric keys?
Answer
Ciphertext
Explanation
Ciphertext
is larger and can be calculated using the following formula:
Size =
( FLOOR (8 + D)/BLOCK) + 1) * (BLOCK + BLOCK + 16)
where D
is the data size in bytes, BLOCK is the block size (8 or 16, depending on
algorithm)
and Size is the new size in bytes of the ciphertext.
What result would you expect from this
statement?
Select * from dbo
.MyTable
All records from [dbo].[MyTable]
The
answer is all records from [dbo].[MyTable]. Qualified names can include white
space
before and after the dots. Restrictions apply only to the names themselves
that
may need quoted identifiers with embedded white space or invalid characters.
You are using SQL Server 2005 and you
wish to find a number of details about
stored procedures created. The details
include date and time, the server that created
the stored procedure, the login name and
the contents. How would you do this?
Create
a DDL trigger on CREATE_PROCEDURE database event
The
answer is Create a DDL trigger on CREATE_PROCEDURE database event You can
find
the answer on page 448 of Beginning SQL Server 2005 for Developers: From
Novice
to Professional
A .NET assembly running with UNSAFE
permissions can do which of the following?
All of
the above
CLR
code running with UNSAFE permissions can access virtually any resource inside
or
outside of SQL Server without restriction. This is one reason only a sysadmin
can
create
an assembly as UNSAFE.
What does the SQL Writer service do?
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.
Which key provides the strongest
encryption?
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.
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?
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.
What level of permissions does a user
need to create an assembly with UNSAFE
permissions?
sysadmin
Only a
sysadmin can create assemblies that are specified as unsafe.
Which optional SQL Server component must
you install to ensure that you can create
and execute maintenance plans?
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.
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?
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.
In SQL Server 2005, which network
protocols use endpoints?
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.
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?
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.
What port do you need to open on your
server firewall to enable named pipes
connections?
Port
445
Named
pipes communicates across TCP port 445.
When the network service built-in account
connects to a network resource, what is
the context?
The
computer account of the Windows installation.
The
network service account connects to network resources as the computer account
for the
Windows installation.
A certificate is like which type of key
in SQL Server 2005 encryption?
An
Asymmetric Key
A certificate
is like an asymmetric key and can validate the identity of the issuer.
How many connections are available in SQL
Server MSDE 2000?
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.
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'
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.
No comments:
Post a Comment