What are the maximum number of tables that can be joined together in a query?
A) 16
B) 32
C) 256
D) 1024
Question 2
When using BCP to copy data into a table, which of the following statements apply? Choose 2.
A) Database users will not be able to access the table because BCP will lock it.
B) Database users will see the rows inserted by BCP after each batch is complete.
C) You must have INSERT permissions on the table.
D) Existing rows are replaced by BCP.
Question 3
An UPDATE statement may modify how many tables?
A) One or more tables, provided the UPDATE statement is not operating on a view.
B) Only one, though triggers may cause additional side-effects.
C) Only one.
D) Up to sixteen
Question 4
Data and log files expand by what increment?
A) 8KB
B) 64KB
C) 512KB
D) 1MB
Question 5
Jamie is having a problem tracking down an issue users are having with a stored procedure.
Users will occasionally have problems with the stored procedure running for an excessive
amount of time, perhaps hours on end. There are no messages in the SQL Server error log that
correspond to the times that users are having problems. Which of the following is the most
likely cause and resolution?
A) The server is crashing and the users aren't able to access their data. Replace the hardware.
B) The server is processing a database backup, and which is causing an excessive
amount of locking. Stop the automated backup jobs.
C) There is a problem with one or more user connections causing lock contention.
Use sp_who and DBCC INPUTBUFFER to track down the likely culprit.
D) There is a problem with one or more user connections causing lock contention.
Use sp_lock to track down the
Question 6
Which of the following commands are parts of the Data Definition Language (DDL)?
A) INSERT
B) CREATE TABLE
C) UPDATE
D) CREATE VIEW
Question 7
Consider the following table and trigger definitions: Table1: UserID int IDENTITY FirstName
char(50) LastName char(50) DepartmentID int BillingID int CREATE TRIGGER Table1_InsertUpdate ON
Table1 FOR INSERT, UPDATE AS BEGIN IF UPDATE(DepartmentID) IF (Select count(*) from Table1
inner join Deleted on Table1.DepartmentID = deleted.departmentid) = 0 BEGIN RAISERROR
('Cannot remove the last member from a department.', 16, 1) ROLLBACK END END What does the
ROLLBACK statement in the trigger do?
A) It causes the transaction to roll back, aborting the INSERT or UPDATE that cause the trigger to run,
but not aborting the transaction that the offending statement was part of.
B) It causes the transaction containing the offending statement to roll back.
C) Nothing; the keyword EXIT should have been used to abort the transaction.
D) It causes the transaction log to fill up.
Question 8
In order for her new application to process a sales record, Kim has to make sure that the
application has collected enough information. For sales over $500, she needs to have a valid
account manager's ID number. For sales over $1000, she needs to have a valid zip code for
delivery to determine who gets commission. For sales over $10,000, she needs to make sure that
enough stock is available. Which of the following would be a good way to check the data prior
to inserting it?
A) Table constraints
B) Triggers
C) An inline table-valued function
D) A multi-statement table-valued function
Question 9
What is the maximum number of rows that a table can store in SQL Server 2000?
A) 255
B) 1,048,516
C) 2,147,483,647
D) No limit besides physical storage size
Question 10
If table_a has an insert trigger that performs an update on table_b, in which case will the
update trigger on table_b be fired?
A) If the 'nested triggers' database option has a value of '1'.
B) If the 'nested triggers' server option is set to '1'.
C) If the 'nestable' option is set for the trigger on table_a.
D) If the 'nestable' option is set for the trigger on table_b.
Question 11
You are finding unusually high memory utilization on a SQL Server that is primarily used to
process XML data. The server will periodically freeze up and refuse to process any more XML
statements. The problem can only be fixed by restarting the SQL Server service, at which point
it work fine for a while, but the problem always returns. What is the most likely cause for
the problem?
A) Your developers are probably not deallocating their cursors after processing the XML data.
B) The system is routinely encountering malformed XML and the accumulated errors are crashing the system.
C) The XML strings are not being removed after being prepared and used.
D) A hardware problem is the root of your trouble.
Question 12
What are two advantages of triggers over rules and constraints? Choose two.
A) The ability to define boundaries within which the data must reside.
B) The ability to use looping structures.
C) The ability to reference data in other databases.
D) The ability to check data before it is logged.
Question 13
Which of the following is a good reason to use transactional replication?
A) It provides an up-to-the-second backup in case one of the sites in the replication model goes down.
B) It provides a method for using a lot of resources on several servers in order to provide
up-to-the-second reporting capabilities.
C) It provides the lowest-overhead replication of any other type.
D) It is veryQuestion tolerant of network outages.
Question 14
When creating a table that contains a birth date for a person,
you want to ensure that a valid date is always entered. Which of the following is the best way to accomplish this?
A) Adding a CHECK constraint to the column
B) Adding a trigger to the table that will validate the column
C) Using a DateTime data type for the column.
D) Using a NOT NULL constraint on the column
Question 15
Which of the following EXEC statements are valid? Choose all that apply.
A) exec ReturnValue = MyProc 5, 4, 'hello', @A OUTPUT
B) exec @ReturnValue = MyProc @length = 5, 4, 'Hello', @A OUTPUT
C) exec @ReturnValue = MyProc 5, 4, 'Hello', @A OUTPUT
D) exec @ReturnValue = MyProc @length = 5, @width = 4, @message = 'Hello', @A = @A OUTPUT
Question 16
What tool included with SQL Server is primarily used for writing and executing ad-hoc queries?
A) SQL Server Enterprise Manager
B) SQL Server Profiler
C) SQL Server Query Analyzer
D) SQL Server Index Wizard
Question 17
What are the three join operations SQL Server supports? Choose all that apply.
A) Nested loops
B) Merge join
C) Index spool
D) Hash match
Question 18
The graphical execution plan presented by SQL Server Query Analyzer uses different icons to
represent which of the following?
A) Physical operators
B) Logical Operators
C) T-SQL commands
D) Process flow
Question 19
Phil has created a new database that takes advantage of multiple physical disks on his server.
He has created his primary data file on one disk and placed a secondary data file on a separate
physical disk. The transaction log exists on the same physical disk as the primary file.
Phil’s plan is to put all user objects on the secondary data file and leave the system tables
on the primary data file. After creating all his objects, however, he quickly discovers that
they ended up on the primary file group. What might he have done wrong?
A) All objects are created on the primary data file until there is no more room.
Only after the primary data file is full will objects be created on the secondary data file.
B) He probably forgot to create the secondary data file as part of a separate file group.
Objects are created on file groups rather than on data files.
C) He probably forgot to declare the secondary data file as the default data file.
Objects are created on the default data file unless another data file is specified.
D) Nothing is actually wrong. SQL Server automatically assigns the secondary data file as the
primary file group so that all user defined objects will be created separately from the
primary data file.
Question 20
Which of the following best describes a situation in which the statistics for a table would
be misleading?
A) Random rows in the table have been deleted.
B) Deleting all of the records of a specific gender.
C) Dropping Foreign Key constraints on the table
D) Dropping indexes on a table
Question 21
Which of the following are results of specifying schema binding on a view?
Choose all that apply.
A) The view created with schema binding cannot be dropped.
B) The CREATE statement for the view must contain a two-part name for all objects referenced.
C) When created with schema binding, the CREATE statement is stored in an encrypted manner and cannot be scripted out.
D) Objects referenced by the view cannot be dropped or changed while the view is schema bound.
Question 22
George needs to write a view that can return one or more customers from a table given a last
name or part of a last name. Which of the following strategies will allow him to do that?
A) Write a view for every single last name that can be entered.
B) Write an application that automatically creates a new view for each last name in the database.
C) Pass a lastname parameter into the view.
D) Write a SELECT statement that references the view. Use the WHERE clause in the SELECT statement to filter the data.
Question 23
Which of the following are parts of a SELECT statement? Choose all that apply.
A) FROM
B) SORT BY
C) WHERE
D) SELECT
Question 24
Which of the following features are new to SQL Server 2000? Choose all that apply.
A) Full Text Searching
B) Indexed views
C) Cascading Referential Integrity Constraints
D) Remote administration of servers
Question 25
When is it important to use the WITH RECOMPILE option when creating a stored procedure?
A) Always use WITH RECOMPILE.
B) On servers that aren't busy, WITH RECOMPILE should be used all the time.
C) Only if the query plans used by the stored procedure change frequently.
D) When any tables that are going to be used by the stored procedure are routinely dropped and re-created.
Question 26
Given the following partial function definition, which SET statement shown below would correctly accept the returned value? create function Area (l1 int, l2 int) returns int
A) set @A = dbo.area(5, 4)
B) set @A = area(5,4)
C) exec area(5, 4, @A OUTPUT)
D) set @A = dbo.@Area(5, 4)
Question 27
Consider the following table and trigger definitions:
Table1: UserID int IDENTITY FirstName char(50) LastName char(50) DepartmentID int BillingID int
CREATE TRIGGER Table1_InsertUpdate ON Table1 FOR INSERT, UPDATE AS BEGIN IF
UPDATE(DepartmentID) IF (Select count(*) from Table1 inner join Deleted on
Table1.DepartmentID = deleted.departmentid) = 0 BEGIN RAISERROR
('Cannot remove the last member from a department.', 16, 1) ROLLBACK END END
What does the RAISERROR statement in the trigger do?
A) It provides the connection with an error message.
B) It provides the connection and the SQL Server error log with an error message.
C) It provides the connection, the SQL Server error log file, and the Windows NT application event log with an error message.
D) It causes a server-wise severity one message, stopping SQL Server.
Question 28
Diane needs to enforce referential integrity on one of her tables. The data in one of the
columns should contain key values from a table residing in a database on the corporate server,
as opposed to her departmental server where her application database resides. She decides to
use constraints to enforce data integrity. How would you rate this solution?
A) This is an optimal solution. Using constraints to validate data entry is the best way to verify referential integrity.
B) This is a fair solution. Although using constraints will work, it would be better to use triggers to accomplish this goal.
C) This is a poor solution. It won't perform well, but it will work.
D) This solution won't work.
Question 29
Choose the statement that best describes a composite index.
A) It physically sorts the rows in a table.
B) It contains multiple keys.
C) It logically sorts the rows in a table in both ascending and descending order.
D) It contains multiple columns in its key.
Question 30
You need to install SQL Server Developer Edition on a spare machine to deliver to a new developer in your company so he can study for his certification exam. Of the following machines that can be spared,
what is the least powerful machine you can use for this purpose?
A) Pentium 266MHz, 32MB memory
B) Pentium 266MHz, 64MB memory
C) Pentium 166MHz, 64MB memory
D) Pentium 90MHz, 32MB Memory
Question 31
Which of the following types of columns would be good candidates for an index? Choose all that apply.
A) Columns that make up the Primary Key
B) Columns that contain Foreign Keys
C) Columns that contain data such as gender for a large population.
D) Columns that contain data such as a tax ID or address for a large population
Question 32
What is the maximum number of rows that a table can store in SQL Server 2000?
A) 255
B) 1,048,516
C) 2,147,483,647
D) No limit besides physical storage size
Question 33
An UPDATE trigger typically accesses which of the following?
A) The updated table
B) The inserted table only
C) The INSERTED and DELETED tables
D) The DELETED table only
Question 34
Which of the following commands can best assist in diagnosing locking problems?
A) sp_lock
B) sp_who
C) DBCC SHOWLOCKS
D) SET SHOWLOCKS
Question 35
Which of the following commands can be used to interrupt processing of a While loop?
A) BREAK
B) HALT
C) EXIT
D) CONTINUE
Question 36
Choose all the statements that you could use in the WHERE clause to find only the rows
where the first name is Bobby or Bobbi. Choose all that apply.
A) WHERE name = ‘Bobby’ or name = ‘Bobbi’
B) WHERE name LIKE ‘Bobb_’
C) WHERE name LIKE ‘Bobb%’
D) WHERE name LIKE ‘Bobb[iy]’
Question 37
What are the space requirements for views?
A) Views require as much space as all the tables they are created from.
B) Views require as much space as would a table that presented the same data.
C) Views do not store data and therefore use no space.
D) Views require the space used to store a few records in various system tables.
Question 38
You have written a stored procedure that takes several parameters and uses them to issue
appropriate queries to the database. Most of the parameters are optional, and if all are passed
the resulting query you have to run can be quite complex. There is a great deal of conditional
logic in the procedure based on the parameters passed. When testing this procedure you found
that when you first created it, it worked great even though you started with a rather complicated
set of parameters. Since then performance has been disappointing. Every time you re-create it,
it works fast once, but rarely shows such speed on later calls. What is the most likely cause
of the problem?
A) You have specified the With Encryption option, which is causing additional overhead because the procedure has to be decrypted each time it is run.
B) You have specified the With Recompile option, which is causing additional overhead because the procedure has to recompile a new query plan every time.
C) You have forgotten to specify the With Recompile option, which is causing an inappropriate query plan to be used with subsequent runs.
D) Your stored procedure cannot be helped. Stored procedures weren’t intended to handle complex conditional logic.
Question 39
How does a TRUNCATE TABLE statement differ from an unrestricted DELETE? Select all that apply.
A) A TRUNCATE TABLE cannot be rolled back, even if it occurs within a transaction.
B) An unrestricted DELETE resets an Identity column to its original seed, whereas the TRUNCATE TABLE does not.
C) If the table is referenced by a FOREIGN KEY constraint, only the DELETE may be used.
D) The TRUNCATE TABLE causes a DELETE trigger to be fired, whereas the DELETE does not.
Question 40
What is a valid GROUP BY clause for the following SELECT list? SELECT Pub_ID, Type, Max(Price)
A) GROUP BY Pub_ID, Type, Max(Price)
B) GROUP BY Pub_ID, Type, Price
C) GROUP BY Pub_ID, Type
D) GROUP BY max(Price)
Question 41
Jesse's company has a central office and three remote locations. Each remote location has
three to five employees that run an order-taking application; they don't generate any reports
or need any additional data beyond their order-taking application. Each site has a modest but
reliable network connection to the central office. Which of the following is the best way for
Jesse to provide these users with access to the database?
A) Set up snapshot replication to each remote site.
B) Use transactional replication to replicate data to the remote site.
C) Use merge replication to replicate data to the remote site.
D) Don't use replication; just connect the users directly to the remote database.
Question 42
Eric needs to build a stored procedure to count the number of snack food vendors within his
zip code. Which of the following strategies would be the easiest to implement for passing and
returning values?
A) Pass the zip code in as a parameter; pass the number of vendors out as a return value.
B) Pass the zip code in as a parameter; pass the number of vendors out as an output parameter.
C) Use the stored procedure to call a scalar-valued user-defined function.
D) Pass the zip code in as a parameter; pass the output back in a temporary table.
Question 43
Carol wants to distribute the Personnel table, but needs to leave out a couple of columns
from the replication, such as the AnnualSalary column. What's the best way to do this?
A) Implement horizontal partitioning.
B) Use SQL Server security to keep people out of that column.
C) Use vertical partitioning to turn off replication for that article.
D) Use union partitioning.
Question 44
Which of the following are good uses for the I-SQL utility? Choose 2.
A) Execution of Data Definition Language statements
B) Monitor queries issued by one or more users
C) Examine a graphical query plan
D) Schedule execution of a T-SQL script
Question 45
What role do CHECK constraints play in partitioned views?
A) CHECK constraints are required to ensure that an inserted row goes to the correct table.
B) CHECK constraints are not required but can increase performance for inserts to the partitioned view.
C) CHECK constraints serve no purpose for partitioned views.
D) CHECK constraints could conflict with the definition of the partitioned view and therefore
cannot exist on the tables that make up a partitioned view.
Question 46
Which of the following options will create a composite unique index on the CustomerID and ProjectID fields in the CP table?
A) create unique composite index CPidx on CP(CustomerID, ProjectID)
B) create unique index CPidx on CP.CustomerID, CP.ProjectID
C) create unique index CPidx on CP(CustomerID, ProjectID)
D) create clustered index CPidx on CP(CustomerID, ProjectID)
Question 47
Sam needs to set up her stored procedure so that when invalid data values are passed into the
stored procedure, the stored procedure will cause the application to see an error and have
SQL Server log an event into the error log. Which of the following will allow this to happen?
A) ON ERROR RESUME NEXT
B) RAISERROR 'Invalid data', 50000, 1, LOG
C) RAISERROR ('Invalid Data', 50000, 1) WITH LOG
D) RAISERROR('Invalid Data', 50000, 1)
Question 48
Roger wants to create an index on a read-only table. What is the best option
he should use when creating the index?
A) FILLFACTOR = 100
B) FILLFACTOR = 0
C) PAD_INDEX, FILLFACTOR = 100
D) PAD_INDEX, FILLFACTOR = 0
Question 49
When using BCP to transfer data out of a table, which of the following statements apply? Choose 3.
A) Database users will not be able to access the table because BCP will lock it.
B) You must have SELECT permissions on the table.
C) Data changes made by other users during the BCP will not be reflected in the data file.
D) The contents of the data file are replaced if the data file already exists.
Question 50
Insensitive cursors provider what functionality?
A) They enable users to see data inserted since the time the cursor was opened.
B) They allow faster updates because the data for the cursor is stored in tempdb.
C) They cannot be scrolled backward because the data is discarded from the temporary storage after it is fetched.
D) They ensure an unchanging view of the data as it was when the cursor was opened.
Question 51
What is the lifetime of a user-defined variable?
A) Duration of the script
B) Duration of the statement block
C) Duration of the batch
D) Duration of the current SQL Server connection
Question 52
To use full-text searching and find all rows containing forms of the verb "swim,"
you should use which CONTAINS predicate?
A) CONTAINS(columname, FORMSOF(INFLECTIONAL, 'Swim'))
B) CONTAINS(columnname, 'swim', 'swims')
C) CONTAINS(columnname, FINDALL("Swims"))
D) CONTAINS(columnname, 'swim*')
Question 53
Which of the following will help reduce deadlocks in your database?
A) Keeping the transaction isolation level as high as possible without hurting user concurrency
B) Always accessing tables in the same order in all programs
C) Never completing transactions until a user can confirm that the changes are correct
D) Always checking the state of @@TRANCOUNT before issuing a ROLLBACK command
Question 54
Sarah needs to figure out how to replicate data from all of her warehouses back to the
corporate office. She wants to store all the inventory information in one table, and all of
the information needs to be accessible to all of the warehouses and the corporate office, and
the information should be updateable at any location. The warehouses do not have reliable
network connections back to the corporate office. Which type of replication should Sarah use?
A) Snapshot replication
B) Merge replication
C) Transactional replication
D) Snapshot replication with Updating Subscribers
Question 55
When creating a database with the CREATE DATABASE command, in what units can the file size be
specified? Choose all that apply.
A) MB (Megabytes)
B) Number of pages
C) Number of extents
D) KB (Kilobytes)
Question 56
Eddie needs to have a database that is replicated to other locations. They have highly
redundant network connectivity to all of the other locations, and a very large central server.
Changes to the database need to be made at every site, and need to be done instantaneously at
every site, with no delay. Which type of replication would work best for Eddie?
A) Merge replication
B) Transactional replication
C) Snapshot replication
D) Snapshot replication with updates
Question 57
Which of the following are aggregate functions? Choose 3.
A) Sum
B) ABS
C) Max
D) Count
Question 58
Barney wrote this stored procedure to manage inserts to his Sales table: CREATE PROCEDURE
InsertSale @SalesPersonID int, @ProductID int, @Quantity int, @CustomerID int AS
If @Quantity > 0 Insert Sales (SalesPersonID, ProductID, Quantity, CustomerID) VALUES
(@SalesPersonID, @ProductID, @Quantity, @CustomerID) else RAISERROR
('Unable to enter negative or 0 quantity into Sales table.', 16, 1) GO
If there are no constraints on the table, how can Barney make sure that the value of
@SalesPersonID is valid?
A) Check the Sales table and see whether there are any other rows in that table for that SalesPersonID.
B) Check the table in which SalesPersonID is a Primary Key to ensure that the value is valid.
C) Attempt the insert and check to see whether the foreign key constraint causes an error.
D) It's not possible to access more than one table in a stored procedure.
Question 59
Which queries will return all the names in the Authors table sorted by first name, then last? Choose 2.
A) SELECT au_fname, au_lname FROM Authors SORT BY 1, 2
B) SELECT au_fname, au_lname FROM Authors ORDER BY au_fname, au_lname
C) SELECT au_fname, au_lname FROM Authors SORT BY au_fname, au_lname
D) SELECT au_fname, au_lname FROM Authors ORDER BY 1, 2
Question 60
When using an INSERT statement, data to be inserted may come from which of the following?
Select all that apply.
A) The VALUES clause
B) A stored procedure
C) Another database
D) A SELECT statement
Question 61
Which of the following are reasons to choose SQL Server 2000? Choose all that apply.
A) Internet integration
B) Scalability and availability
C) Enterprise-Level Database features
D) Data warehousing
Question 62
You were recently hired to oversee the installation of SQL Server to support a new accounting
package for a small business. The company is cost-conscious, so you need to make a conservative
recommendation about where to install SQL Server in the company's existing structure. Which of
the following makes the best choice to support SQL Server?
A) Dual-processor machine with 512MB memory currently acting as an Exchange server
B) A single-processor 800MHz Pentium with 256MB memory currently acting as a Primary Domain Controller.
C) A spare Pentium 90MHz laptop with 128MB of memory
D) Pentium 1GHz machine with 512MB memory recently purchased to replace the president's workstation
Question 63
How is a NOT NULL column added to an existing table?
A) By using the ALTER TABLE statement.
B) Dropping and recreating the table.
C) By using ALTER TABLE to add a nullable column, and then using sp_changenull to change the column's NULL option.
D) By using the sp_addnullablecolumn system stored procedure.
Question 64
Omitting the WHERE clause of an UPDATE or DELETE statement has what effect?
A) All rows in the specified table are affected.
B) The query returns an error because the WHERE clause is required.
C) Only the current row is affected.
D) Only the rows with the correct data type are affected.
Question 65
What statement could you use in the WHERE clause to select all the rows in a table where no
price is defined?
A) WHERE price IS NOT NULL
B) WHERE price IS < 0
C) WHERE price != 0
D) WHERE price IS NULL
Question 66
Consider the following table and trigger definitions: Table1: UserID int IDENTITY FirstName char(50)
LastName char(50) DepartmentID int BillingID int CREATE TRIGGER Table1_InsertUpdate ON Table1
FOR INSERT, UPDATE AS BEGIN IF UPDATE(DepartmentID) IF (Select count(*) from Table1 inner join
Deleted on Table1.DepartmentID = deleted.departmentid) = 0 BEGIN RAISERROR
('Cannot remove the last member from a department.', 16, 1) ROLLBACK END END Assuming that this
is the only trigger bound to the table, is it possible for the last member of a department to
be deleted?
A) No; the trigger prevents that from happening.
B) Yes; the trigger has not been activated yet.
C) Yes, if a new record is inserted on top of the existing one.
D) Yes, by using the DELETE command.
Question 67
Referential integrity refers to what?
A) The enforced uniqueness of a row in a table
B) The enforced uniqueness of a column in a table
C) Ensuring that a Foreign Key attribute cannot be NULL
D) The enforced synchronization of Primary Key and Foreign Key values
Question 68
Barney wrote this stored procedure to manage inserts to his Sales table:
CREATE PROCEDURE InsertSale @SalesPersonID int, @ProductID int, @Quantity int, @CustomerID int
AS If @Quantity > 0 Insert Sales (SalesPersonID, ProductID, Quantity, CustomerID) VALUES
(@SalesPersonID, @ProductID, @Quantity, @CustomerID) else RAISERROR
('Unable to enter negative or 0 quantity into Sales table.', 16, 1) GO What does the
RAISERROR statement do?
A) The RAISERROR will cause the row to not be inserted into the database.
B) It provides feedback to the calling application so it can diagnose why the insert failed.
C) It will cause SQL Server to shut down because a severity 1 error occurred.
D) It will do nothing.
Question 69
SQL Sever maintains what types of information about a column in the table definition?
A) Name, NULL option, and constraints
B) Name, data type, and NULL option
C) Name, data type, NULL option, and bytes of overhead.
D) Name, data type, NULL option, and constraint information
Question 70
DTS Package Designer tasks can be set to run in what ways? Choose all that apply.
A) On completion
B) On failure
C) On error
D) On success
No comments:
Post a Comment