To ensure that future email messages you receive from SQL Server Magazine UPDATE aren't mistakenly blocked by antispam software, be sure to add [email protected] to your list of allowed senders and contacts.
This Issue Sponsored By
AppDev Training
http://lists.sqlmag.com/cgi-bin3/DM/y/eg310FgQMn0BRZ0BKTr0Am
Win a Harley at SQL Server Magazine Connections
http://lists.sqlmag.com/cgi-bin3/DM/y/eg310FgQMn0BRZ0ggP0At
1. SQL Server Perspectives
2. News and Views
3. Reader Challenge
4. Announcements
5. Resources
6. Events Central
7. New and Improved
Sponsor: AppDev Training
Free SQL Server 2000 Training! Become a more efficient, effective developer with AppDev's award-winning self-study SQL Server training! See AppDev quality for yourself--download 3 hours of training immediately or request a CD-ROM by mail (each a $95 value) FREE! Click the link below for your FREE SQL Server 2000 Training!
http://lists.sqlmag.com/cgi-bin3/DM/y/eg310FgQMn0BRZ0BKTr0Am
1. SQL Server Perspectives
by Brian Moran, [email protected]
I've been writing about SQL Server Profiler frequently over the past few weeks, so you're probably aware that I think Profiler is a great tool that's under-utilized by development and DBA teams. Several readers have sent me interesting ideas about integrating Profiler into the development environment, so this week I want to share two of those ideas with all of you.
The first suggestion comes from a reader who is a DBA for a busy eBusiness test-and-production environment in which granting sa privileges to developers isn't permitted because the organization's management team isn't willing to take the risk. The reader writes, "Granting developers sa privileges on their own server, as you suggested, is a valid solution, but it's not cost effective for us. So, I'd like to suggest a compromise that's worked for us. We run a Profiler trace all day, every day, during business hours. Profiler stores the data in a table that anyone can view. This lets everyone see the high CPU/read transactions that occur throughout the day. Of course, events, data columns, and filters for tracing should be decided upon within each environment. Our trace is run in both production and test environments. When a specific stored procedure requires further traces, a developer asks a DBA to start another trace that specifies that stored procedure--also storing the results in a table for easy querying."
In "SQL Server Profiler: For Developers, Too" ( http://www.winnetmag.com/article/articleid/43268/43268.html ), I argued that Profiler should become an integral part of the development and unit-testing process. I still hold that the risks involved with granting developers sa access are eclipsed by the benefits of giving developers access to trace data. However, this reader's solution is a reasonable compromise for shops that can't, or won't, grant sa access to developers.
Another interesting idea comes from a reader who writes, "In my company, we go out of our way to blur the lines between development, administration, and support. All our development leads have full administrative access to both our production and development environments, with the understanding that developers have as much responsibility for the well being of those systems as the administrators. Proper communication and coordination between everyone on the team brings many benefits to the table, including more robust applications, better testing scenarios, quicker problem resolution, improved relationships between development and administration, and a deeper resource pool to pull from when major support problems arise. We also require all developers to directly support any application they develop for 6 weeks after moving the application to production. When you have to clean up your own messes and interact with the users affected by those messes, accountability and responsibility improve. Creating silos of authority only leads to silos of communication, and I would rather risk a developer overstepping his bounds with a production system than consistently rolling out applications that under-perform--or don't perform at all--because a developer lacks knowledge about how users implement the applications they develop."
I love this idea! I completely agree that blurring the lines between traditional support, development, quality assurance, and administration roles is bound to create a better end product. And I suspect that developers become more vigilant when they know they're going to eat their own dog food in production for 6 weeks.
On a different note, in "In the Express Lane with SQL Server" (http://www.winnetmag.com/article/articleid/43234/43234.html ), I told you about SQL Server 2005 Express Technical Preview. Until a product manager at Microsoft pointed it out, I didn't realize that the Express Technical Preview came out 2 weeks before SQL Server 2005 Beta 2 was released to Microsoft Developer Network (MSDN) subscribers. Now, SQL Server 2005 Express Technical Preview is no longer available--there's only Express Beta 2. The bits of the two versions are different, but the Microsoft Web site doesn't spell that out clearly. If you have the SQL Server 2005 Express Technical Preview, you should uninstall, and download Express Beta 2. Microsoft doesn't support a direct upgrade from Technical Preview to Beta 2.
Win a Harley at SQL Server Magazine Connections
SQL Server Magazine Connections conference is coming to Las Vegas, Nevada, November 7 - 10. With more than 45 sessions covering data warehousing and business intelligence, database development, database administration, and more, this is the premier SQL Server event! Don't miss the insightful opening keynote "The Evolution of SQL Server" by Microsoft's Thomas Rizzo. Registration includes an exclusive day of presentations about SQL Server 2005 (formerly code-named Yukon) by the Microsoft SQL Server 2005 development team including sessions about T-SQL, Common Language Runtime (CLR) integration, security enhancements, management tools, best practices, and more. Attendees will receive the latest build of Visual Studio 2005, a beta copy of SQL Server 2005, and a chance to win a Harley Davidson motorcycle. Get the complete conference brochure online or call 800-438-6720 or 203-268-3204.
http://lists.sqlmag.com/cgi-bin3/DM/y/eg310FgQMn0BRZ0ggP0At
2. News and Views
http://www.sqlmag.com
3. Reader Challenge
contributed by Umachandar Jayachandran, [email protected]
Congratulations to Alejandro Mesa, a database programmer for Simplex Medical in Fort Myers, Florida, and Marcos Kirchner, student at the University of Blumenau (FURB) in Santa Catarina, Brazil. Alejandro won first prize of $100 for the best solution to the August Reader Challenge, "A Bulk-Copy Procedure." Marcos won second prize of $50. You can find a recap of the problem and the solution to the August Reader Challenge at
http://www.sqlmag.com/articles/index.cfm?articleid=43650
Now, test your SQL Server savvy in the September Reader Challenge, "Foreign Punctuation" (below). Submit your solution in an email message to [email protected] by August 19. 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: David is a database programmer for an advertising company that deals with international data. The database that holds data for each customer contains a table that stores names in Unicode format. The following script contains the table and sample data:
CREATE TABLE #Temp ( nc nvarchar(30) COLLATE Latin1_General_CI_AS )
CREATE CLUSTERED INDEX Idx_Temp_nc on #Temp( nc )
INSERT INTO #Temp VALUES( N'MRKT' + nchar(0x3000) + N'Name')
INSERT INTO #Temp VALUES( N'TEST String' )
INSERT INTO #Temp VALUES( N'MRKT' +
nchar(0x3000) + N'Name' + nchar(0x3000) )
In the sample data, the Unicode character 0x3000 represents a punctuation symbol in different languages, such as Chinese. When David writes a query using an equality search condition, he notices that the query doesn't work as expected. For example, the following query returns two rows instead of one:
SELECT * FROM #Temp WHERE nc = N'MRKT' +
nchar(0x3000) + N'Name' + nchar(0x3000)
What is incorrect in the query? And how can David modify the query to return the correct data without ignoring punctuation or white-space characters in any language?
4. Announcements
This year's competition is calling for a cutting-edge SQL Server solution that solves a real-world problem and/or improves performance. One grand-prize winner will win a free trip to the 2004 SQL Server Magazine Connections Conference in Las Vegas, Nevada. Fill out an entry form today to get the recognition you deserve! Deadline is September 1.
http://lists.sqlmag.com/cgi-bin3/DM/y/eg310FgQMn0BRZ02hN0Az
http://lists.sqlmag.com/cgi-bin3/DM/y/eg310FgQMn0BRZ0BKTs0An
5. Resources
Back doors are undocumented features of an application that let you do things the application wasn't intended to support. In his August T-SQL Black Belt column, "Back Doors with a View," Itzik Ben-Gan concludes his series on T-SQL back doors by discussing three back doors to SQL Server views: rolling your own information_schema views, creating sorted updateable views, and circumventing update limitations that views impose. He also tells readers which undocumented techniques he thinks could be useful for T-SQL programmers if SQL Server supported them. Read this article today at
http://www.sqlmag.com/articles/index.cfm?articleid=42971
http://www.winnetmag.com/sqlserver/forums/messageview.cfm?catid=1664&threadid=124302
by Brian Moran, [email protected]
Q. I need to store integers--account numbers and other IDs--that have 20 or more digits. Storage and retrieval of these integers needs to be accurate, with no rounding, so I can't use the float data type. I've seen recommendations to others in this situation to store the integers in a CHAR(20) field. But I don't want to store my number in a character field because I want them to remain numeric values. How can I store integers that have 20 or more digits as numeric values?
A. I suggest using the decimal or numeric data type to store the integers. In SQL Server, the numeric and decimal data types are equivalent. Both data types let you store integers that have up to 38 digits. The syntax for defining a decimal data type is:
DECIMAL\[(p\[, s\])\]
The first number after the DECIMAL keyword is the precision setting, which defines the total number of digits the integer can have. The second number is the scale setting, which defines the total number of digits to the right of the decimal place...
To read the complete answer to this question and download the code that shows how to store a 20-digit number as both a decimal data type and a character data type, go to
http://www.sqlmag.com/articles/index.cfm?articleid=43114
6. Events Central
For a complete guide to Web and live events, see
http://www.winnetmag.com/events
Meeting database-performance demands with larger amounts of data and more complex retrieval requirements is a constant challenge for SQL Server users. SQL Server Magazine invites you to attend a free, interactive Web seminar on September 16. Learn about the foundation of common issues and tactics for solving SQL Server performance dilemmas. Register today:
http://lists.sqlmag.com/cgi-bin3/DM/y/eg310FgQMn0BRZ0BKTt0Ao
7. New and Improved
by Dawn Cyr, [email protected]
ProClarity announced the availability of several new analytic tools for the ProClarity Analytic Application Development Platform, a customizable development environment that lets organizations create sophisticated analytic applications. The new tools are add-ins available through the ProClarity Community, a Web-based group of developers, customers, and partners. Chief among the new add-ins is the Bubble-up Exception key performance indicator (KPI) template, which lets users visually analyze a hierarchy of information and identify potentially important variances. The Quadrant Analysis tool serves as a visual template for grouping data according to predefined parameters. The Cache Warmer improves query performance by "pre-loading" data views. The Usage Monitor add-in lets you comprehensively monitor your analytic applications. And new parameterization customization features for the ProClarity Analytic Server let you supply a specific value as a data slicer to get more personalized views of information. For more information, you can attend the ProClarity Web seminar "ProClarity Accessories: Extending the Value of the ProClarity Platform" on Tuesday, August 24 at 10:00 a.m. Mountain time; or contact ProClarity at 208-344-1630.
http://lists.sqlmag.com/cgi-bin3/DM/y/eg310FgQMn0BRZ01eO0Aw
WhiteTown Software announced WhiteTown Wizards, applications that convert DBF files to Microsoft Access, SQL, XML, Microsoft Excel, HTML, or comma-separated value files. To convert outdated DBF files, you simply select the source file you want to convert, and the wizard interface guides you through the steps of converting the file to the format you need. The wizards keep the original database structure, tables, and headers whenever possible. You can use the wizards to Web-enable your databases by converting them to HTML. And a special application is available for converting older, outdated DBF files to new DBFs while fixing their structure. WhiteTown Wizards are available individually for download. Each converter costs $19.95 for a single license. For more information, contact WhiteTown software by voice mail at 38-067-9105301, by fax at 206-984-3919, or at [email protected].
http://lists.sqlmag.com/cgi-bin3/DM/y/eg310FgQMn0BRZ0BKTu0Ap
SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine 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
Contact Us:
- About SQL Server Perspectives — [email protected]
- About the newsletter — [email protected]
- About technical questions — http://www.sqlmag.com/forums
- About product news — [email protected]
- About your subscription — [email protected]
- About sponsoring SQL Server Magazine UPDATE — Kate Silvertooth([email protected])
Manage Your Account
You are subscribed as #EmailAddr#.
To unsubscribe from this email newsletter, send an email message to mailto:#Mailing:UnsubEmail#.
To make other changes to your email account such as changing your email address, updating your profile, and subscribing or unsubscribing to any of our email newsletters, simply log on to our Email Preference Center.
http://www.winnetmag.com/email
Copyright 2004, Penton Media, Inc.
