Congratulations to John Costantino, who won first prize of $100 for the
best solution to the April Reader Challenge, "Change the Status of
Table Items." Here's his solution to the April Reader
Challenge. (To read the full Challenge, go to
InstantDoc ID 95324.)
Solution:
Siva should first create an index on the
OrderStatus column of the Orders table.
Creating this index will help him quickly
locate orders of a particular status. Additionally, he should include a column such as
OrderDate as part of the index to answer
more useful questions about date and status
of the orders. Siva can create this index by
using the following CREATE INDEX
statement:
CREATE INDEX idx_Order_Status_By_Date ON
Orders("OrderStatus", "OrderDate")
Next, Siva should use the UPDATE statement in Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 95501) to change the
status of orders from Processing to Shipped
after all of the line items have been processed.
The UPDATE statement selects all orders in
the Orders table that are in the "Processing"
status and ensures that all of the line items
for every order are in the "Processed" status. The index on the OrderStatus and OrderDate columns of the Orders table improves
the UPDATE statement's performance. To
test the UPDATE statement action, Siva can
use the code in Web Listing 2.
MAY CHALLENGE:
Test your SQL Server savvy in this month's
Reader Challenge. Submit your solution
in an email message to challenge@sqlmag.com by May 10. Umachandar Jayachandran,
a SQL Server Magazine technical editor, will
evaluate the responses. We'll announce the
winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will
receive $100, and the second-place winner
will receive $50.
Problem:
Richard is a database architect for a company that uses SQL Server as its database.
The company's DBAs typically execute
database updates by using T-SQL scripts.
The front-end application that interacts
with SQL Server requires several ANSI settings to be enabled when the DBAs create database objects such as tables, functions,
stored procedures, and triggers. The application also uses features such as indexed views
and indexes on computed columns. For the
application to work correctly, it requires the
following ANSI SQL SET options:
- ANSI_PADDING should be enabled on
all character and binary columns in tables.
- ANSI_NULLS and QUOTED_IDENTIFIER options should be enabled when
stored procedures, functions, and triggers are
created.
Richard has recently encountered production problems that happen when DBAs
execute scripts from a session that don't have
the required ANSI SET options enabled.
The scripts create objects that have nondefault settings, resulting in application
errors and data integrity errors. To prevent
future problems, Richard wants to include checks in the application and in the scripts
that can identify objects that don't have
the necessary SET options enabled. Help
Richard do the following:
- Write code to list the columns that are
created with ANSI_PADDING OFF.
- Write code to list the stored procedures, functions, and triggers that were
created or modified with ANSI_NULLS or
QUOTED_IDENTIFIER OFF. For each
object, the setting that's disabled should be
shown.
- 3Create the checks in a T-SQL batch that
can be run at the end of the script or in the
application. The checks should also raise an
error if any objects were found that meet the
criteria.
You can use the script in Web Listing 3 to
create in the tempdb database objects that
have some of the SET options disabled.
End of Article
