PrepAway - Latest Free Exam Questions & Answers

Which WHILE statement should you use at line 18?

###BeginCaseStudy###
Case Study: 1
Scenario 1
Application Information
Your company receives invoices in XML format from customers. Currently, the invoices are
stored as files and processed by a desktop application. The application has several
performance and security issues. The application is being migrated to a SQL Server-based
solution. A schema named InvoiceSchema has been created for the invoices xml.
The data in the invoices is sometimes incomplete. The incomplete data must be stored and
processed as-is. Users cannot filter the data provided through views.
You are designing a SQL Server database named DB1 that will be used to receive, process,
and securely store the invoice data. A third-party Microsoft .NET Framework component will
be purchased to perform tax calculations. The third-party tax component will be provided as a
DLL file named Treytax.dll and a source code file named Amortize.cs. The component will
expose a class named TreyResearch and a method named Amortize(). The files are located in
c:\temp\.
The following graphic shows the planned tables:

You have a sequence named Accounting.InvoiceID_Seq.
You plan to create two certificates named CERT1 and CERT2. You will create CERT1 in
master. You will create CERT2 in DB1.
You have a legacy application that requires the ability to generate dynamic T-SQL statements
against DB1. A sample of the queries generated by the legacy application appears in
Legacy.sql.
Application Requirements
The planned database has the following requirements:
• All stored procedures must be signed.
• The original XML invoices must be stored in the database.

• An XML schema must be used to validate the invoice data.
• Dynamic T-SQL statements must be converted to stored procedures.
• Access to the .NET Framework tax components must be available to T-SQL objects.
• Columns must be defined by using data types that minimize the amount of space used
by each table.
• Invoices stored in the InvoiceStatus table must refer to an invoice by the same
identifier used by the Invoice table.
• To protect against the theft of backup disks, invoice data must be protected by using
the highest level of encryption.
• The solution must provide a table-valued function that provides users with the ability
to filter invoices by customer.
• Indexes must be optimized periodically based on their fragmentation by using the
minimum amount of administrative effort.
Usp_InsertInvoices.sql

Invoices.xml
All customer IDs are 11 digits. The first three digits of a customer ID represent the customer’s
country. The remaining eight digits are the customer’s account number.
The following is a sample of a customer invoice in XML format:

InvoicesByCustomer.sql

Legacy.sql

CountryFromID.sql

IndexManagement.sql

###EndCaseStudy###

You execute IndexManagement.sql and you receive the following error message:
“Msg 512, Level 16, State 1, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =,! =,
<, <= ,>, > = or when the subquery is used as an expression.”
You need to ensure that IndexManagement.sql executes properly.
Which WHILE statement should you use at line 18?

PrepAway - Latest Free Exam Questions & Answers

A.
WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable)

B.
WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)

C.
WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)

D.
WHILE @counter < (SELECT SUM(RowNumber) FROM 6indextabie)

One Comment on “Which WHILE statement should you use at line 18?”

  1. MickBig says:

    Definitely B. A and C do not work, syntactically. D has a typo in the syntax but even after fixing it, it’s doing a SUM on the ROW_NUMBERs generated in the previous table var, i.e., 1 + 2 + 3 until whatever the max ROW_NUMBER is. B just does a COUNT, which is the correct approach.

    Try it here on your db:

    DECLARE @IndexTable TABLE (
    TableName varchar(100), IndexName varchar(100), Fragmentation int, RowNumber int );

    DECLARE @TableName sysname, @IndexName sysname, @Fragmentation int, @RowNumber int;

    INSERT INTO @IndexTable (TableName, IndexName, Fragmentation, RowNumber)
    SELECT OBJECT_NAME(i.Object_id),
    i.name AS IndexName,
    indexstats.avg_fragmentation_in_percent,
    ROW_NUMBER() OVER(ORDER BY i.name DESC) AS ‘RowNumber’
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ‘DETAILED’)
    AS indexstats INNER JOIN sys.indexes AS i
    ON i.Object_id = indexstats.Object_id AND i.index_id = indexstats.index_id;

    DECLARE @counter int = 0;

    –A.
    –WHILE SUM(@RowNumber) < (SELECT @counter FROM @indextable)

    –B.
    –WHILE @counter < (SELECT COUNT(RowNumber) FROM @indextable)

    –C.
    –WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)

    –D.
    –WHILE @counter < (SELECT SUM(RowNumber) FROM @indextable)
    BEGIN
    SET @counter = @counter + 1;
    PRINT 'Test'
    END




    0



    0

Leave a Reply