SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com
THIS ISSUE SPONSORED BY
Need to Configure a Server for Microsoft SQL Server?
http://activeanswers.compaq.com/ActiveAnswers/Render/1,1027,536-6-100-225-1,00.htm
FREE WP: The Challenge of Managed Recoverability
http://ad.doubleclick.net/clk;4356300;7273592;h?http://www.bmc.com/offers/sqlbacktrack_oracle/whitepaper/
(below NEWS AND VIEWS)
SPONSOR: NEED TO CONFIGURE A SERVER FOR MICROSOFT SQL SERVER?
Check out the new online ProLiant Transaction Processing Sizer for Microsoft SQL Server 2000 from the new HP. This automated tool will help you determine an optimum hardware configuration for your database server - based on YOUR requirements. Through an interview process, a set of hardware configurations is developed using patented system sizing and configuration technology. Specific configuration information and performance recommendations are provided for each configuration. Developed by HP in our software integration lab, this tool supports the ProLiant server family and appropriate options.
http://activeanswers.compaq.com/ActiveAnswers/Render/1,1027,536-6-100-225-1,00.htm
June 20, 2002—In this issue:
1. COMMENTARY
- Update Your Data in Smaller Bites
2. SQL SERVER NEWS AND VIEWS
- Multiple Vulnerabilities in SQLXML for SQL Server 2000
- Results of Previous Instant Poll: Spida Worm
- New Instant Poll: Enforcing Data Integrity
3. ANNOUNCEMENTS
- Get Kudos & a Free Trip to SQL Server Magazine LIVE! in Orlando!
- Immediate Access to T-SQL Solutions!
4. HOT RELEASES (ADVERTISEMENTS)
- Protect Your Database
- FREE SQL Server 2000 Administration or Developer Training
5. RESOURCES
- What's New in SQL Server Magazine: The Shape of T-SQL
- Hot Thread: Creating XML Schemas
- Tip: Changes in Enterprise Manager and Query Analyzer
6. NEW AND IMPROVED
- Test and Edit T-SQL Views and Stored Procedures
- View SQL Server Databases and Tables
7. CONTACT US
- See this section for a list of ways to contact us.
1. COMMENTARY
(contributed by Brian Moran, news editor, [email protected])
This week, I'd like to offer advice about the common need to "munge a lot of data." To help set the stage, here's a question a colleague recently asked:
"I'm having trouble updating a large number of rows in a table that contains 35 million rows. For example, say that the table, MyTable, has a column called UserId. A typical value for UserId looks like '374771&something,' and I need to trim everything to the right of the & symbol. The UPDATE query I'm running looks like this:
UPDATE MyTable
SET UserID = SUBSTRING(UserID, 1, charindex('&', UserID)-1)
WHERE
UserID LIKE '%&%'
However, the query has been running for several hours, the transaction log is currently 10GB and growing, and I wonder if there's a more efficient way to tackle this problem."
The need to update large amounts of data is common in the real world, but many SQL Server professionals don't know how to tackle the problem efficiently. Fully addressing all the scenarios and solutions that this question raises could fill an issue of SQL Server Magazine, and I don't have that much space. However, the basic rule of thumb I follow in data-modification cases is similar to a rule of thumb you should follow when eating a nice juicy steak: You might choke and die if you try to swallow the entire steak in one bite, so don't do it. Eating the steak in multiple, smaller bites is much easier, safer, and tastier. The same advice holds true when modifying massive amounts of data.
My colleague was trying to update 35 million rows. Conceptually, you can update the row through a single UPDATE statement, or you can do it in multiple UPDATE statements, updating smaller "bites" of data with each statement. Chunking the data-for example, issuing 70 statements that each update 500,000 rows instead of one statement that updates all 35 million-is beneficial for many reasons, including the following (I'm assuming you don't have a business requirement to perform the update in a single all-or-nothing transaction):
- You can manage the size of the transaction log.
- You won't experience a horrible roll-back situation if the UPDATE fails 99 percent of the way through updating 35 million rows.
- The server won't be as sluggish for other people working with the database at the same time.
- You probably won't lock the entire table, so you'll greatly improve concurrency, which allows other actions against the database during the update process.
- You'll have a much more predictable way to gauge how long the update process will take.
I could go on and on. In fact, I will when I address the same question in my SQL Server Savvy Q&A column in an upcoming issue of SQL Server Magazine. Trust me, your mom was right when she told you to take smaller bites. Taking smaller bites when updating your data might just prevent a great big SQL tummyache.
2. SQL SERVER NEWS AND VIEWS
MULTIPLE VULNERABILITIES IN SQLXML FOR SQL SERVER 2000
(contributed by Ken Pfeil, [email protected])
Matt Moore discovered two vulnerabilities in XML for SQL Server 2000 (SQLXML). The first problem is a buffer overrun that lets an attacker execute arbitrary code on the affected system, and the second problem is in a function specifying an XML tag that lets an attacker run scripts on the user's computer in a higher privilege zone, such as "Intranet" instead of "Internet." Microsoft has released Security Bulletin MS02-030 (Unchecked Buffer in SQLXML Could Lead to
Code Execution) to address this vulnerability and recommends that
affected users download and apply the appropriate patch mentioned in
the bulletin.
http://www.secadministrator.com/articles/index.cfm?articleid=25589
The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Have you protected your SQL Server machines against the Spida worm?" Here are the results (+/- 1 percent) from the 266 votes:
- 70% Yes
- 18% No, but I plan to soon
- 2% Doesn't apply
The next Instant Poll question is, "Where do you usually enforce data integrity?" Go to the SQL Server Magazine Web site and submit your vote for 1) At the client, 2) At the server, 3) At the client and the server, 4) Varies according to efficiency, or 5) I don't give much thought to data integrity.
http://www.sqlmag.com
THIS WEEK'S INSTANT POLL SPONSOR: BMC SOFTWARE
SQL-Backtrack — a faster, smarter data recovery solution.
Click for BMC Software's complimentary white paper.
http://ad.doubleclick.net/clk;4325705;7273592;m?http://www.bmc.com/offers/sqlbacktrack_oracle/whitepaper/
SPONSOR: FREE WP: THE CHALLENGE OF MANAGED RECOVERABILITY
Don't wait for a disaster to realize the importance of protecting your data. Read more about the increasing importance of efficient 100% recovery. And find out how to increase efficiencies within IT operations and ensure your business continuity with SQL Backtrack from BMC Software. SQL Backtrack determines the cause of your outages and automatically brings your database back online. Ensure that your mission-critical data will be available. Click here for your FREE white paper:
http://ad.doubleclick.net/clk;4356300;7273592;h?http://www.bmc.com/offers/sqlbacktrack_oracle/whitepaper/
3. ANNOUNCEMENTS
(brought to you by SQL Server Magazine and its partners)
Get the recognition you deserve for your cutting-edge SQL Server solution and take home the SQL Server Innovator's Cup. If you work with SQL Server and have created a technical solution to a problem or enhanced a program or system feature to improve performance or return on investment, you qualify to enter this awards program sponsored by Microsoft. Enter today at
http://www.sqlmag.com/awards
Exclusive in-depth articles, tips, tricks, and code samples all at your fingertips. Content you can't get anywhere else—brought to you by the SQL Server experts you trust such as Kalen Delaney, Itzik Ben-Gan, and others. Increase your productivity today! Go to the following URL.
http://www.tsqlsolutions.com
4. HOT RELEASES (ADVERTISEMENTS)
SQL-UP! ensures uptime and disaster-protection of SQL Server databases. The software clusters databases over LAN or WAN without a shared storage device. Clustered databases are automatically synchronized in realtime. For more information:
http://www.incepto.com
FREE SQL Server 2000 Administration or Developer training CD-ROM for the first 50 respondents who check out this offer at:
http://www.learnkey.com/sqlupdate
5. RESOURCES
T-SQL can be useful for graphical and geometrical puzzles, which require a different way of thinking from the usual business-scenario problems. In "The Shape of T-SQL," SQL Server Magazine contributing editor Itzik Ben-Gan shows you how to use T-SQL to solve a geometric problem involving mapping motel locations and how to think "outside the box" to implement similar ideas in other business situations you face. The article appears in the June 2002 issue of SQL Server Magazine and is available online at the following URL:
http://www.sqlmag.com/articles/index.cfm?articleid=24932
Bharilal is trying to create an XML schema from two SQL Server tables. Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
http://www.sqlmag.com/forums/messageview.cfm?catid=9&threadid=6802
(contributed by the Microsoft SQL Server development team)
Q. What are the changes in Enterprise Manager and Query Analyzer that I should know about when I upgrade from SQL Server 7.0 to SQL Server 2000?
A. When you're upgrading from SQL Server 7.0 to 2000, you need to be aware of several changes in Enterprise Manager and Query Analyzer. For example, the connection defaults have changed for the tools and for Data Transformation Services (DTS) packages. SQL Server 2000 Books Online (BOL) documents these changes. Look for the sections that describe installing SQL Server, upgrading, and backward compatibility. Here's an example of a Query Analyzer change. In SQL Server 7.0 and earlier, the SET QUOTED_IDENTIFIER default setting for Query Analyzer is OFF. In SQL Server 2000, this option's default setting for Query Analyzer—as well as for ODBC and OLE DB—is ON. Moreover, several of SQL Server 2000's new features, such as indexed views and indexes on computed columns, require this option to be ON. If you use double quotes for strings when QUOTED_IDENTIFIER is ON, as you do in SQL Server 7.0, you'll receive a syntax error, which might derail scripts that you wrote for SQL Server 7.0's Query Analyzer. In SQL Server 2000, you should use single quotes around these strings by default.
Send your technical questions to [email protected].
6. NEW AND IMPROVED
(contributed by Carolyn Mader, [email protected])
Eddie Merkel announced T-SQLEditPro, a T-SQL code editor with a SQL Sense feature that lets you access database objects. The software also lets you view, edit, and test views and stored procedures from one central location. The software features a graphical query builder that lets you generate complex queries. After you've written your code, T-SQLEditPro lets you store snippets of code and even entire procedures in a local database file that you can share with other developers on the network. T-SQLEditPro costs $79 for a single-user license. Contact Eddie Merkel at [email protected].
http://www.t-sql.com
Deepak Kumar announced Easy Access to SQL, software for beginning SQL Server users that lets you view all the data in your SQL Server database. You don't need to use any command to view the data, and the software requires only a login ID and password. After the software validates you, it shows all SQL Server databases in one combo box. When you choose a database from the combo box, the software shows all the tables that belong to that database in another combo box. The query box can execute all SQL commands and show you results. For more information, contact Deepak Kumar at [email protected] or [email protected].
7. CONTACT US
Here's how to reach us with your comments and questions:
- ABOUT THE COMMENTARY — [email protected]
- ABOUT THE NEWSLETTER IN GENERAL — [email protected]
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — [email protected]
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — [email protected]
- WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
[email protected]
More than 102,000 people read SQL Server Magazine UPDATE every week. Shouldn't they read your marketing message, too? To advertise in SQL Server Magazine UPDATE, contact Beatrice Stonebanks at [email protected] or 800-719-8718.
SQL Server Magazine UPDATE is brought to you by SQL Server Magazine,
the only magazine completely devoted to helping developers and DBAs
master new and emerging SQL Server technologies and issues. Subscribe
today.
http://www.sqlmag.com/sub.cfm?code=ssei211x1y
Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.
http://www.winnetmag.net/email
