Devising a View

Congratulations to Emmanuel Nanchen, analyst/programmer at Manpower Switzerland, and Vishal Gupta, a data architect and DBA at Nexgenix in Irvine, California. Emmanuel won first prize of $100 for the best solution to the July Reader Challenge, "Devising a View." Vishal won second prize of $50. Here’s a recap of the problem and the solution to the July Reader Challenge.

Problem

Ian is the database developer for several SQL Server 2000 and 7.0 installations. As part of his job managing the company’s sales database, he controls security by enforcing Sales and Manager database roles. The database holds two tables: a sales force login table called SalesUser and a sales assignments table called Items that contains a separate list for each salesperson. First, Ian created the login table with relevant columns as follows:

The view lets users who are listed in the SalesUser table access only their own assignments.

To prevent users from adding activities that don’t apply to them, Ian appends CHECK OPTION at the end of the view. The CHECK OPTION clause prevents users from entering data that they can’t see or query through the view. In addition, he adds the T-SQL is_member() function to the WHERE clause as a check mechanism. Ian uses is_member() to confirm that the current database user is a member of the specified database role or Windows NT group. That way, he feels comfortable granting any member of the manager role permission to view and modify all assignments. Here’s the modified view:

August Challenge

Now, test your SQL Server savvy in the August Reader Challenge, "Speeding Up the Query" (below). Submit your solution in an email message to challenge@sqlmag.com by July 18. SQL Server MVP 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.

Here’s the challenge: While troubleshooting a slow distributed query in his application code, Donald finds that SQL Server is choosing an inefficient execution plan. The distributed query performs a join between a large table residing on remote server Prod1 and a small table residing on local server Prod2 and retrieves most columns from the remote table. However, SQL Server retrieves all the remote table’s rows and performs the join on the local server.

To debug the query, Donald uses Northwind’s Orders and Order Details tables to create a test scenario. On the remote server, he creates the following test table:

From the Blogs

The quest for the Golden Record to achieve a single, accurate and complete version of a customer record is worth the pursuit to attain survivorship. Record matching and consolidation are only the beginning. Melissa Data takes a new approach. Learn how to apply intelligent rules based on reference data to make smarter and better decisions for data cleansing....More

On SQL Servers where Availability Groups (or Mirroring) isn’t in play, I typically recommend keeping a combination of on-box backups along with copying said backups off-box as well. Obviously, keeping databases AND backups on the SAME server is the metaphorical equivalent of putting all of your eggs in one basket – and therefore something you should avoid like the plague....More

One of the biggest strengths of AlwaysOn Availability Groups is that they allow DBAs to address both high availability and disaster recovery concerns from a single set of tooling or interfaces. But, this doesn’t mean that you won’t still need backups....More