70-229 test
Michael Duberstein
10.
You are a database developer for Proseware,Inc. You are creating a
database named Human Resources for the company.
This database will contain all employee records and demographics
information.
The company has 2,000 employees and
experiences a yearly turnover rate of about 2 percent.
When employees leave the company, all of their records must be retained for
auditing purposes.
Employee demographics information changes at a yearly rate of about 9
percent.
You do not need to maintain a history of demographics changes.
The scripts that will be used to create the indexes are shown below:
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [pk_Employee] PRIMARY KEY CLUSTERED
([EmployeeID])
WITH FILLFACTOR = 90
GO
ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD
CONSTRAINT [dbo].[pk_EmployeeDemographics] PRIMARY KEY CLUSTERED
([EmployeeID])
WITH FILLFACTOR = 90
GO
You want to conserve disk space and minimize the number of times that
expansion of the database files needs to occur. All varchar columns are 50
percent full.
Which two parameters should you specify for the CREATE DATABASE statement?
(Each correct answer presents part of the solution.)(Choose two)
A. SIZE = 1GB
B. SIZE = 1MB
C. SIZE= 2048KB
D. FILEGROWTH = 20
E. FILEGROWTH = 5%
F. FILEGROWTH = 0
Diffuculty: to choose between B and C: 1MB and 2048KB = 2MB.
Look at the calculation:
If 1MB then each record in the clustered indexes is 1MB : 2000 : 2 ~= 256B,
where 2 is the number of clustered indexes (tables).
Since the the ratio between clustered index size and table size is 1.2
in the average, the average record size is 256 : 1.2 = 213 B brutto
~= 205 B netto, since fillfunctor is 90% it leaves us
with 180 B per record in each of the two tables in the average.
Seems to be too little.
So 1MB is too little, 1GB to large, therefore 2048KB is the size.
So I choose C and E, please your comments!!!
19.
You are the database developer for a brokerage firm. The database
contains a tabled named Trades. The script that was used to create this
table is shown below:
CREATE TABLE Trades
( TradeID int IDENTITY(1,1)PRIMAEY KEY NONCLUSTERED NOT NULL,
TradeDate datetime NULL,
SettlementDate datetime NULL,
Units decimal(18, 4) NULL,
Symbol char(5) NULL,
ClientID int NULL,
BrokerID int NULL )
GO
CREATE CLUSTERED INDEX c_symbol ON Trades (Symbol)
The Trades table has frequent inserts and updates during the day. Reports
are run against the table each night.
You execute the following statement in
the SQL Query Analyzer:
DBCC SHOWCONTIG (Trades)
The output for the statement is shown below:
DBCC Statement Output
DBCC SHOWCONTIG scanning 'Trades' table. . . . . Table:
'Trades'(1621580815); index ID:1, database ID:12Table level scan performed.
-Pages Scanned-----------------------------------------:104
-Extents Scanned---------------------------------------:16
-Extent Switches----------------------------------------:24
-Avg. Pages per Extenbt-------------------------------:6.5
-Scan Density[Best Count:Actual Count]-----------:52.00%[13:25]
-Logical Scan Fragmentation-------------------------:7.69%
-Extent Scan Fragmentation---------------------------:43.75%
-Avg. Bytes Free per page-----------------------------:460.1
-Avg. Page Density (full)------------------------------:94.32%
DBCC execution completed. If DBCC printed error messages, contact your
system
You want to ensure optional performance for the insert and select operations
on the Trades table. What should you do?
A. Execute the DBCC DBREINDEX statement on the table.
B. Execute the UPDATE STATISTICS statement on the table.
C. Execute the DROP STATISTICS statement on the clustered index.
D. Execute the DBCC INDEXERRAG statement on the primary key index.
E. Execute the DROP INDEX and CREATE INDEX statements on the primary key
index.
Difficulty: to choose between A and D.
Assumption: INDEXERRAG is misprint for INDEXDEFRAG.
Considerations:
For A: seem to be a better solution(solves physical fragmentation as well,
and works for both indexes),
but is not online and reports are run each night.
For D:
1) The nonclsutered index can hardly be fragmented because it's IDENTITY.
2) The statistics in the question only indicates the fragmentation of the
leaf level of the
clustered index.
3)The operation should better be online.
I chose D. Is that correct?
45.
You're conducting a class to help some junior-level developers hone their
database-development skills. You enter a discussion with them about local
and global variables, and are trying to clarify the differences between the
two. Which of the following statements can you always make about these two
kinds of variables?
(Choose two.)
A. Global variables are really functions.
B. All values returned by them are specific to the object context.
C. They use different characters to denote themselves.
D. All values returned by them are specific to the connection context.
E. Both of them are available to all connections.
I am sure that A is correct, B and D are not.
I guess it's E, because it's true.
It's not C, because even local variables can start with @@, it's just not
recommended.
Is that correct?
72.
You are evaluating a junior-level developer design for a new database. This
database was to be designed with an emphasis on query performance. Replying
to this directive, the developer has sketched out several indexes for the
tables with the highest expected query load. As you review his design, you
notice that the new indexes will provide varying degrees of benefit to
queries. Which of his nonclustered indexes is likely to be the most
effective?
A. An index on gender for the local state 134,000 registered voters.
B. An index on the sales agent¡?s last initial for a table holding 25,000
orders.
C. An index for the StateCode (primary key) column in a US_States table.
D. An index for the State column in a PacificTime_ZIP_Codes table.
Since no information about types of query is given and therefore it's
impossible to determine the selectivity of potential queries,
the only criteria is the density of indexes.
For A: it's 134000 / 2 = 67000
For B: it's 25000 / 26 ~= 1000
For C: it's 1 - unique
For D: it's a guesswork but somewhere around 100000 / 50 = 2000,
where 100000 is the max amount of post offices in the US,
50 is the amount of states, while the distribution of the offices over the
territory is not known.
I would definitely choose C, but some users on forums choose B, because
1) primary key is clustered by default - seems irrelevant to me
2) The table in A is too small for massive index usage, anyway table scan
will be used in most cases
- again seems disputable to me.
What do you think?
89.
You have designed the database for a Web site that is used to purchase
concert tickets.
During a ticket purchase, a buyer view a list of available tickets,
decides whether to buy the tickets, and then attempts to purchase the
tickets.
This list of available tickets is retrieved in a cursor.
For popular concerts, thousands of buyers might attempt to purchase tickets
at the same time.
Because of the potentially high number of buyers at any one time,
you must allow the highest possible level of concurrent access to the data.
How should you design the cursor?
A. Create a cursor within an explicit transaction, and set the transaction
isolation level to REPEATABLE READ.
B. Create a cursor that uses optimistic concurrency and positioned updates.
In the cursor,
place the positioned UPDATE statements within an explicit transaction.
C. Create a cursor that uses optimistic concurrency. In the cursor, use
UPDATE statements
that specify the key value of the row to be updated in the WHERE clause,
and place the UPDATE statements
within an implicit transaction.
D. Create a cursor that uses positioned updates.
Include the SCROLL_LOCKS argument in the cursor definition to enforce
pessimistic concurrency.
In the cursor, place the positioned UPDATE statements within an implicit
transaction
Can't choose between B and C.
I guess it's B, because positioned update seems to be the only option for
this application.
I don't quite understand what the key value can be here for C?
How WHERE would look and how the uniqueness will be provided here?
Is an update that is not positioned feasible at all for this application?
If yes, what will provide better concurrency: B or C?
What will be the range of locks in both cases.
Explicit or implicit transaction doesn't seem to matter here.
Is that correct?
Please refer your comments to m_dube...@yahoo.com,
Please reply by June 11, 2002, 12GMT