How important are error-free SQL scripts and accurate database documentation to you? ApexSQL’s new SQL Parser 2008 is exponentially faster and more accurate than any 3rd party dependency tracking algorithm on the market, ensuring more reliable documentation and error free scripts than tools that rely on SMO
The Challenge
The original version of our SQL parser was far more accurate than
- SQL Servers’ own dependency tracking objects (i.e. sysdepends)
- SQL Server’s client components SMO
- Other proprietary algorithms written by competitors
… but we wanted to make ours even more accurate and also significantly improve the performance
The Solution
The result of nearly 3 man years of development and testing is a component that is simply the most accurate on the planet for analyzing inter-object dependencies in SQL Server, achieved with exponential gains in performance
The Impact
This component will improve the performance, accuracy of almost all of our applications along with reducing script errors related to dependency order (build scripts, synchronization scripts etc). It will also provide the foundation for the next generation of functionality like building scripts out of source control with 100% accuracy and 0% errors, formatting, refactoring and more
The Difference
This component will serve to provide compelling competitive differentiator vs other 3rd party tools that rely on SQL Server’s dependency tracking (either via direct database calls or SMO) or proprietary algorithms that still result in critical errors and inaccuracies
Improved Accuracy vs SQL Server (sp_msdependencies, sysdepends)
Many developers understand that Microsoft SQL Server does a poor job tracking its own dependency relationships. But what many don’t know is that this problem is simply promulgated by many 3rd party tools “value added” tools that rely on this flawed underlying technology (via calls to sp_msdependencies) to create synchronization scripts, dependency documentation trees etc. Any dependency inaccuracies in SQL Server will thus be manifest in the output of the 3rd party tool
The following is an example of where this problem can affect you. SQL Server incorrectly tracks dependency relationship between Owners and views (as well as other objects) in GRANT and DENY permissions constructions
What is the Problem? You will most likely get script execution error in build/sync scripts because of incorrect object order. Permissions will be incorrectly defined on your object even if the function or view is successfully created. There will be inaccuracies in your documentation (and other lists of dependencies) since the dependency on the owner will be missing
See Appendix A for a list of additional examples.
Improved Accuracy vs SQL Server (SMO)
Many 3rd party SQL tools simply interface to SQL Server’s client components – SMO to interrogate dependencies. In addition to being incredibly slow, SMO is often inaccurate when reporting dependency relationships
The following is an example of where this problem can affect you. Table and referenced object in appropriate construction
What is the Problem? This missed dependency will break the referential integrity of your database. The link between the tables (master and details) won’t be displayed in the documentation
See Appendix B for a list of additional examples.
Improved Accuracy vs Leading Competitor’s own Parser
We’ve demonstrated how ApexSQL’s dependency parser is superior to SQL Server’s own native capabilities, but how does it stack up against other companies who have written their own solutions? To determine this we selected a leading competitor, who has written their own parser, and ran our complete set of test cases against it. This competitor had managed to fix some of the aforementioned deficiencies of SQL Server itself but how did it compare against ours? The results indicated that not only had we improved upon SQL Server but had achieved a level of accuracy superior to this leading competitor as well as other competitors who have written even less accurate parsers
The competitor tool may not recognize dependencies on owners from tables in a case when an owner is altered via AUTHORIZATION section
What is the Problem? This missed dependency will result in the owner not displayed in documentation. However the major problem here is that it will break the defined ownership on a table and introduces script execution error because of incorrect object dependency order. Your table will still be created
See Appendix C for additional examples
Improved Performance vs previous versions
The new ApexSQL Parser has resulted in dramatic improvement in all applications where it has been implemented. Here are a few examples
Conclusion
We hope that this short article has demonstrated the power, capability and performance of the new version of this critical SQL parsing component, whose benefits significantly improve almost all ApexSQL applications. Newer variants of this component promise powerful new features such as refactoring, formatting, find and replace as well as additional performance breakthroughs
Notes
Direct database call (i.e. sp_msdependencies, sysdepends) tests were conducted vs SQL Server 2005 and SQL Server 2008 as of September 2008
SMO tests were conducted vs the latest version of a variety of 3rd party Documentation and Compare tools that use SMO as well as SMO itself as of October 2008
Tests were conducted vs a Red-Gate’s most recent version as of September 2008 (SQL Compare version 7.1)
Appendix
Appendix A – SQL Server’s Native Dependency inadequacies (sp_msdependencies, sysdepends)
The following is a partial list of additional examples of problems with SQL Server dependency tracking
SQL Server incorrectly tracks dependency relationship between …
A) Parent schemas and views
What is the Problem? Script execution errors in build/synchronization scripts due to wrong object order
Objects won’t be created if the schema doesn’t already exist
Missing dependencies since the parent schemas won’t be shown/p>
B) Owners and assemblies in the case when an owner altered with AUTHORIZATION section
What is the Problem? Script execution errors in build/synchronization scripts due to wrong object order
Missing dependencies since the parent schemas won’t be shown
C) Owners and Fulltext-Catalogs when the owner is defined via DEFAULT AUTHORIZATION section
What is the Problem? Script execution errors in build/synchronization scripts due to wrong object order
Missing dependencies since the owner won’t be shown
Your catalog may not be created at all
D) Assemblies and datatypes when an assembly is specified in the EXTERNAL NAME parameter
What is the Problem? This missed dependency may result in the script execution error on an attempt to create your type from the assembly because of incorrect object dependency order. Even if you manually create the assembly and after that the datatype, it won’t help with displaying the assembly as a dependent object in the documentation
E) Datatypes and functions in case the datatype pointed out as a returns parameter type
What is the Problem? This missed dependency may result in the script execution error on an attempt to create your function. Even if your function is successfully created the dependent datatype won’t be displayed in the documentation
p>Appendix B – SQL Server’s Native Dependency inadequacies (SMO)
The following is a partial list of additional examples of problems with SQL Server dependency tracking (SMO)
SMO incorrectly tracks dependency relationship between …
A) Parent schema and Types:
What is the Problem? Script execution errors in build/synchronization scripts due to wrong object order. Objects won’t be created if the parent schema doesn’t exist. The documentation won’t display the dependency to the parent schema as well.
B) Owners and full-text catalogs, when an owner altered with ALTER AUTHORIZATION section.
What is the Problem? Script execution errors in build/synchronization scripts due to wrong object order in case the owner is created after the full text catalog.
C) Owners and objects in GRANT or DENY statements permissions constructions
What is the Problem? You will most likely get script execution error in build/sync scripts because of incorrect object order. Permissions will be incorrectly defined on your object even if the object was successfully created. There will be inaccuracies in your documentation (and other lists of dependencies) since the dependency on the owner will be missing.
D) Stored Procedure and Credential , and Certificate in appropriate constructions:
What is the Problem? This missed dependency won’t introduce script execution error but will affect your documentation as the dependent objects won’t be displayed.
E) Parent schema and Rules
What is the Problem? Script execution errors in build/synchronization scripts due to wrong object order. Objects won’t be created if the schema doesn’t exist. The documentation won’t display dependency to the parent schema as well.
F) Table and referenced object in appropriate construction
What is the Problem? This missed dependency will break the referential integrity of your database. The link between the tables (master and details) won’t be displayed in the documentation.
G) Table and xml schema collection
What is the Problem? Objects won’t be created if the xml schema collection doesn’t exist. Your documentation will miss dependencies to the xml schema collection from tables.
H) Table and partition schemes inside the CREATE INDEX construction that are not detected at all.
What is the Problem? Even if your table is created your indexes will be lost and thus the mentioned partition scheme won’t be displayed in the documentation as a dependent object.
I) Table and function when a function is specified in the DEFAULT parameter
What is the Problem?
This missed dependency may result in the script execution error on an attempt to create your table. Even if your table is successfully created the dependent function won’t be displayed in the documentation as a dependency and the default column values will never be populated.
J) Table and function when a function is specified in the CHECK parameter:
What is the Problem? This missed dependency may result in the script execution error on an attempt to create your table. Even if your table is successfully created the dependent function won’t be displayed in the documentation as a dependency and the column values will never be checked.
K) Synonym and table:
What is the Problem? Objects won’t be created properly.
L) Parent schema and xml schema collection
What is the Problem? Script execution errors in build/synchronization scripts due to wrong object order. Objects won’t be created if the parent schema doesn’t exist. The documentation won’t display the dependency to the parent schema as well.
Appendix C – Dependency inadequacies of a Leading Competitor
The following is a partial list of additional examples of problems with a leading competitor’s dependency algorithm. We kept this list deliberately short to avoid giving this competitor additional test cases!
This leading competitor incorrectly tracks dependency relationship …
A. To the default schema for application role in the DEFAULT_SCHEMA parameter construction
What is the Problem?
This missed dependency will result in the default schema not displayed in the documentation.
B. To services from routes specified in the SERVICE_NAME parameter
What is the Problem? This missed dependency introduces script execution error because of incorrect object dependency order. Also the mentioned service won’t be displayed in the documentation as a dependent object.
About ApexSQL
January 21, 2009
