SQL Server Directory - YOUR Microsoft SQL Server directory for Microsoft SQL Server 6.5, 7.0, 2000 and 2005 (Yukon).
SQL Server Directory - YOUR Microsoft SQL Server directory for Microsoft SQL Server 6.5, 7.0, 2000 and 2005 (Yukon).
 
Home ] Up ] FAQ ] Links ] Privacy ] Search ]
SQL Server Directory - YOUR Microsoft SQL Server directory for Microsoft SQL Server 6.5, 7.0, 2000 and 2005 (Yukon).
Categories
SQL Books
Consultants
SQL Certification
SQL Downloads
SQL Education
SQL Jobs
Data Mining
Magazines
Product Reviews
Products
Reporting Services
SQL Server 2005
SQL Server 64 bit
SQL Server Katmai
Web Resources
Wise Scripts


Top MS SQL Ads
Transcender
MCDBA Directory
SQL Server Dir.
Transact SQL Dir.

Top JOB Ads
CareerBuilder
Monster
Chicago Jobs
Senior Jobs
Microsoft SQL Server 2000 Developer Edition - LESS than $50!
MSSQL 2000 less $50

SQL Server 2000 Best Practices Analyzer Tool 1.0 by Microsoft

reviewed on 9/10/04 by Mark Wehmhoefer

Bottom line

This free SQL Server 2000 database management tool from Microsoft should be run on a regular basis on your development and production databases to verify the implementation of common Best Practices (as defined by Microsoft). Nothing more embarrassing than having database problems reported by the clients and your internal staff evaluators.

10 minute product review

Setup and installation was as simple as running an exe and accepting the defaults.

I was impressed by the quick execution time to evaluate a database even when 400 plus tables were checked.

Very lengthy list of database recommendations can be generated.

Sample recommendations found on an older development database

Cursor Usage T-SQL Rule Information - One or more objects have inappropriate cursor updatability information. SQL Server can improve performance given the appropriate updatability information. A future release of SQL Server may have stricter requirements on this information.

Database Backups Backup and Recovery Rule Information - One or more databases were found without a recent backup.

Database SQL Options Database Administration Rule Information - Database SQL Options should be configured as recommended to remove deprecated behaviors, be ANSI compliant, and be able to leverage the full feature set.

Database File Placement Database Administration Rule Information - It is recommended that data and log files be kept on separate drives.

NULL comparisons T-SQL Rule Information - One or more objects has an expression involving equality or inequality comparison against NULL value! It is recommended to use use IS or NOT IS as per the ANSI standard when testing for NULL values.

String = Expression Aliasing Deprecation Rule Information - One or more objects is using a string value as alias for an expressions! It is recommended to use quoted identifiers instead. A future version of SQL Server will not support this type of aliasing.

INSERT Column List T-SQL Rule Information - One or more object contains an INSERT statement without explicit specification of target column list.! It is generally recommended to explicitly specify the column list target of the INSERT operation.

Non-Ansi Outer Joins Deprecation Rule Information - One or more object is specifying outer joins by using non-ansi syntax.! It is generally recommended to use ANSI style JOIN clause syntax. A future version of SQL Server will not support *= or =* syntax.

Tables without PKs or UQs Database Design Rule Information - One or more databases failed the scan. It is a recommended best practice that all tables have a Primary Key or at least a Unique Constraint on a column defined.

Deprecated Builtin Functions Deprecation Rule Information - One or more objects has calls to deprecated builtin functions! A future release of SQL Server will not support this builtin function.

Use of Schema Qualified Tables/Views T-SQL Rule Information - One or more objects are referencing tables/views without specifying a schema! Performance and predictability of the application may be improved by specifying schema names.

Set working set size disabled Configuration Options Rule Information - 'set working set size' configuration is enabled and SQL Server is configured for dynamic memory management. 'set working set size' option should not be enabled if SQL Server is allowed to use memory dynamically.

SET Options T-SQL Rule Information - One or more objects is setting SET options to values that are not recommended! Some of the non recommended behaviors will not be supported in a future version of SQL Server.

SELECT * T-SQL Rule Information - One or more objects is using SELECT * syntax! It is recommended to provide explicit column lists rather than relying on expansion of '*'. A future version of SQL Server will disallow usage of '*' in functions and views.

Temp Table Usage T-SQL Rule Information - One or more objects have temp table usage that may benefit from table variable replacement! Use of table variables instead of temp tables can reduce number of recompilations.

TOP without ORDER BY T-SQL Rule Information - One or more objects has statements using TOP without specifying an ORDER BY clause! It is generally recommended to use specify ORDER BY clause when using TOP. Otherwise, results will be plan dependent.

NOCOUNT Option in Triggers T-SQL Rule Information - One or more triggers is either setting NOCOUNT to OFF or missing NOCOUNT setting! It is generally recommended to explicitly set NOCOUNT option to ON at the beginning of a trigger.

Results in Triggers T-SQL Rule Information - One or more triggers is sending information back to the caller.! It is generally recommended that triggers not send information to the caller. SELECT (without INTO and assignment), FETCH (without assignment) and PRINT statements send results to the client.

User Objects in Master General Administration Rule Information - One or more user objects were found in 'master' database. 'master' database is reserved for SQL Server internal use and it is recommended that user objects not be created in it.

User Object Naming Database Design Rule Information - Though the practice is supported, to avoid name clashes with system objects it is recommended that user object not have 'sp_', 'xp_' and 'fn_' as name prefixes.

Improvements I would like to see

Report detail could list specific occurrences of the variances found during the evaluation.

Report detail could count the number of the variances found during the evaluation.

Free download

You can download a fully-functional, free copy from Microsoft's website.
----------------------------------------

Mark Wehmhoefer is a full-time Data Base Administrator with a Fortune 50 healthcare company. He is the webmaster for several websites including: SQL Server Directory, MCDBA Directory and Front Page Help.

Mark has earned these Microsoft certifications: MCDBA, MCSE and MCSE + I. Mark has worked with Microsoft SQL Server since version 4.2 and has worked in various capacities in Information Technologies since 1974. 

 


Home ] Up ] FAQ ] Links ] Privacy ] Search ]

Born: 10/20/02         Aged: 11/25/06

Business: B2B Referral, Industrial Supply Deals, Office Supply Deals

Career: Back To School Mall, Chicago Job Resource, Chicago Job Talk, Diversity Job Resource,
Executive Job Resource, First Job Resource, Freelance Job Resource, Seasonal Job Resource, Senior Job Resource

Hobby: Tradin Stuff, Wild Birding

Shopping: Health Care and Fitness, Just Best Gifts, Just Clearance Sales
Just Comparison Shopping, Just Outlets, Luxury Matters, Value Buyers

Technology: FrontPage Help, MCDBA Directory, SQL Server Directory
Transact SQL Directory, WinFS Directory