APEXSQL BLOG LABELS
PRODUCT SPECIFIC
LINKS
MOST RECENT
|
ApexSQL News Product announcements, promotions, discussions, and other ApexSQL Tools news Why simply backing up your database is not enough How do you augment your current backup strategy to ensure faster, easier, and more successful recoveries? Click here to learn how. Labels: Articles ...Refactor Roadmap Here is a list of refactors that we're planning to implement in the next few months, compiled into a Refactor Roadmap. Labels: Articles ...Huge Performance Gains in ApexSQL 2008 Versions SQL 2008 support, powerful new functionality, more accurate output all delivered with tremendous performance improvements? Seeing is believing ApexSQL’s new line of 2008 versions is exponentially faster that anything we’ve released before. Customers will immediately notice the performance gains in most operations, some even more than the average benchmarks we’ve published as the quotes below provide testimonial to. "I installed the upgrade and ran the software. I was able to document our database in about 30 minutes where before it took about 8 hours. So I experienced a much better speed improvement over the six times faster time you are advertising. The process was less resource intensive also. Good job." James Rickaway Omnicell, Inc. Gabriele Marchetti MCSA, MCSE MCDBA, MCTS and MCITP Database Administrator Associate Director Projects and Reporting Bank Julius Baer & Co. Ltd. "I downloaded the Beta version of ApexSql Doc 2008 and I was really surprised of the performance. It's really much faster than the last version." Michele Notari IT Project Leader Tupperware TEAM MBS Performance Results – 2005 vs 2008 versions The following is a collection of graphs demonstrating performance improvements in several of our new 2008 versions. Based on continued improvements in the technology of our underlying components, we expect further increases in performance over the coming months.      Labels: Articles ...ApexSQL's Support for SQL Server 2008 New Features Overview Below is an overview of what SQL Server 2008 new features are supported by ApexSQL’s 2008 line of tools. New Data Types: Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support: - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Enhancements in indexes: - Spatial Indexes – allowing you to index a spatial column of geography or geometry data types - Relational (filtered) Indexes – optimized non-clustered indexes specially developed to allow working with queries from a well-defined subset of data using a filter predicate Enhancements in column properties: - Sparse columns - ordinary columns that have an optimized storage for null values - XML COLUMN_SET - a column of this type is only materialized when selected, and will return you all the non-NULL sparse columns in a row as an XML BLOB - FILESTREAM varbinary (max) – the new way of storing BLOB data allowing you to store the data in the file system. Unlike columns defined with FILESTREAM attribute the ordinary varbinary(max) columns store their data in the tables. Enhancements in procedure parameters and user-defined types: - User defined table type – allows you to define special types having table definition. Variables of such types can be used to store a result set for processing at a later time and passed as procedure parameters or used within the procedure bodies directly - Read-only parameters – indicates that the parameter cannot be updated or modified within the body of the procedure Data compression in tables, indexed views, indexes and constraints: - Data (Row and Page) Compression – allows you to compress whole tables, indexed views, indexes, primary key and unique constraints; can be configured for a whole table, indexed view and non-clustered index and for each partition in case of partitioned indexes or tables. ROW compression means storing fixed length data in variable length storage format and PAGE compression means minimizing the data redundancy Change Tracking and Change Data capture: - Change Tracking – built-in server’s ability that allows you to answer the following questions: “What rows have changed for a user table?” and “Has a row changed?” - Change Data capture – this new SQL Server feature provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system Support for SQL Server 2008 New Features in ApexSQL Doc New Data Types: objects using these types are fully supported for documenting; this also includes support of user-defined data types based on these types Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support: - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Enhancements in indexes: all the new index properties show up in the documentation and TOC seamlessly to ordinary indexes - Spatial Indexes – allowing you to index a spatial column of geography or geometry data types. The index documentation template now contains a new index property indicating if the index is spatial - Relational (filtered) Indexes – optimized non-clustered indexes specially developed to allow working with queries from a well-defined subset of data using a filter predicate. In additional to the new spatial property the index property table now displays the filter condition of a relational index and indicates if an index has filter Enhancements in column properties: tables with such columns are fully supported for documenting and new column attributes show up in the DDL section of the documentation without limitations - Sparse columns - ordinary columns that have an optimized storage for null values - XML COLUMN_SET - a column of this type is only materialized when selected, and will return you all the non-NULL sparse columns in a row as an XML BLOB - FILESTREAM varbinary (max) – the new way of storing BLOB data allowing you to store the data in the file system. Unlike columns defined with FILESTREAM attribute the ordinary varbinary(max) columns store their data in the tables. The table and index documentation templates were specially redesigned to indicate FILESTREAM file group and partition scheme in the table or index properties Note: The only limitation is that FILESTREAM filegroups aren’t documented and thus tables with FILESTREAM columns aren’t grouped in the TOC Enhancements in procedure parameters and user-defined types: all new enhancements are fully supported - User defined table type – allows you to define special types having table definition. Variables of such types can be used to store a result set for processing at a later time and passed as procedure parameters or used within the procedure bodies directly. The user-defined type documentation template was specially updated to indicate if UDT is a table type; procedures with parameters of such types and variables of table types inside their bodies are documented seamlessly to the ordinary ones - Readonly parameters – indicates that the parameter cannot be updated or modified within the body of the procedure. The procedure documentation template was redesigned to display this attribute right next to the procedure parameters indicating if a parameter is READONLY. This attribute also shows up in the DDL section of a procedure Data compression in tables, indexed views, indexes and constraints: the new object attributes are fully supported for documenting and show up in the DDL section of the respective objects - Data (Row and Page) Compression – allows you to compress whole tables, indexed views, indexes, primary key and unique constraints; can be configured for a whole table, indexed view and non-clustered index and for each partition in case of partitioned indexes or tables. ROW compression means storing fixed length data in variable length storage format and PAGE compression means minimizing the data redundancy Change Tracking and Change Data capture: both features are fully supported by the application - Change Tracking – built-in server’s ability that allows you to answer the following questions: “What rows have changed for a user table?” and “Has a row changed?” The table documentation template was redesigned to indicate if Change Tracking feature is enabled for a table; enabling or disabling of this feature shows up in the DDL section of your tables - Change Data capture – this new SQL Server feature provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system. All system procedures of this feature can be documented along with SQL Server Agent jobs for capturing and cleaning up data in a database SSIS 2008: the application fully supports documenting of packages of SQL Server Integration Services 2008; they are documented seamlessly to SQL Server 2005 ones without limitations Support for SQL Server 2008 New Features in ApexSQL Log New Data Types: fully supported for auditing, undoing and redoing of DML operations and exporting DDL feature Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support: - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Support for SQL Server 2008 New Features in ApexSQL Recover New Data Types: fully supported in all operations for lost data recovery such as recovery from delete, truncate and drop operations and so forth… Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support: - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Support for SQL Server 2008 New Features in ApexSQL Edit New Data Types: fully supported for loading, processing and scripting with the respective parent objects as a part of their DDL. User-defined types based on these types are also fully supported and loaded into the Schema Explorer Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support: - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Enhancements in indexes: indexes with the new attributes are supported seamlessly to the ordinary indexes; they are displayed in the Schema Explorer and can be scripted without limitations as a part of DDL script of the parent objects or separately via the Schema Explorer - Spatial Indexes – allowing you to index a spatial column of geography or geometry data types - Relational (filtered) Indexes – optimized non-clustered indexes specially developed to allow working with queries from a well-defined subset of data using a filter predicate Enhancements in column properties: all new column properties are loaded from your database and can be fully scripted into the Query Editor as a part of the DDL of their parent objects - Sparse columns - ordinary columns that have an optimized storage for null values - XML COLUMN_SET - a column of this type is only materialized when selected, and will return you all the non-NULL sparse columns in a row as an XML BLOB - FILESTREAM varbinary (max) – the new way of storing BLOB data allowing you to store the data in the file system. Unlike columns defined with FILESTREAM attribute the ordinary varbinary(max) columns store their data in the tables Enhancements in procedure parameters and user-defined types: user-defined table types have limited support in the application - User defined table type – allows you to define special types having table definition. Variables of such types can be used to store a result set for processing at a later time and passed as procedure parameters or used within the procedure bodies directly. You can create such types via the Query Editor or use procedure variables of table types in your queries if you know they exist in your database, Note: Such user-defined types aren’t displayed in the Schema Explorer and thus could not be scripted directly - Readonly parameters – indicates that the parameter cannot be updated or modified within the body of the procedure. Such parameters can be scripted into the Query Editor without limitations along with procedure DDL’s Data compression in tables, indexed views, indexes and constraints: all new data compression attributes are loaded from your database and can be scripted as a part of DDL of the parent objects - Data (Row and Page) Compression – allows you to compress whole tables, indexed views, indexes, primary key and unique constraints; can be configured for a whole table, indexed view and non-clustered index and for each partition in case of partitioned indexes or tables. ROW compression means storing fixed length data in variable length storage format and PAGE compression means minimizing the data redundancy Change Tracking and Change Data capture: this features has limited support; for example, the CHANGE_TRACKING attribute doesn’t get scripted along with a parent table definition, but you can still manage both features directly in the Query Editor that allows you to enable or disable CHANGE_TRACKING on a table or use Change Data Capture procedures calls without limitations - Change Tracking – built-in server’s ability that allows you to answer the following questions: “What rows have changed for a user table?” and “Has a row changed?” - Change Data capture – this new SQL Server feature provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system Support for SQL Server 2008 New Features in ApexSQL Enforce New Data Types: objects using these types are fully supported for processing (creating both condition and fix script parts); this also includes support of user-defined data types based on these types Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support: - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Enhancements in indexes: all the new index properties can be validated in your rules - Spatial Indexes – allowing you to index a spatial column of geography or geometry data types. The Object Model was extended with the index property SpatialIndexType that allows you to define the type of the index (geography or geometry) - Relational (filtered) Indexes – optimized non-clustered indexes specially developed to allow working with queries from a well-defined subset of data using a filter predicate. Enforce Object Model now has new index properties for indicating if an index has filter condition (HasFilter) and the filter condition property itself (FilterDefinition) for the further validation in a rule Enhancements in column properties: all enhancements were ported to Enforce Object Model and are available in the rule condition - Sparse columns - ordinary columns that have an optimized storage for null values. Column object now has IsSparse property indicating if a column is SPARSE - XML COLUMN_SET - a column of this type is only materialized when selected, and will return you all the non-NULL sparse columns in a row as an XML BLOB. Column object was also extended with IsColumnSet property indicating if a column is XML COLUMN_SET - FILESTREAM varbinary (max) – the new way of storing BLOB data allowing you to store the data in the file system. Unlike columns defined with FILESTREAM attribute the ordinary varbinary(max) columns store their data in the tables. Table object now has special properties returning FILESTREAM file group (FileStreamFileGroup) and FILESTREAM partition scheme (FileStreamPartitionScheme) for the further validation Enhancements in procedure parameters and user-defined types: READONLY parameters has limited support - User defined table type – allows you to define special types having table definition. Variables of such types can be used to store a result set for processing at a later time and passed as procedure parameters or used within the procedure bodies directly. The Rulebase Explorer was extended to display User-Defined Table Type node having its own context menu. Object model was enhanced with the new object type (UserDefineTableType) treating it differently from ordinary user-defined types and porting in there all properties and attributes of table types for validation in your rules. Such rules can be executed separately from the other user-defined type rules - Readonly parameters – indicates that the parameter cannot be updated or modified within the body of the procedure. Note: Such parameters will be treated as an ordinary ones without possibility to validate if a parameter is READONLY Data compression in tables, indexed views, indexes and constraints: data compression has limited by the Object Model; you are allowed validating only one property of a respective object HasCompressedPartitions indicating whether an object has compressed partitions without possibility to determine ROW or PAGE compression type - Data (Row and Page) Compression – allows you to compress whole tables, indexed views, indexes, primary key and unique constraints; can be configured for a whole table, indexed view and non-clustered index and for each partition in case of partitioned indexes or tables. ROW compression means storing fixed length data in variable length storage format and PAGE compression means minimizing the data redundancy Change Tracking and Change Data capture: Change Tracking feature is fully supported by the Object Model; Change Data Capture feature can be analyzed without limitations from the code of validation rules (it can also be used in the FixSQL string), however it has no explicit support in the Object Model - Change Tracking – built-in server’s ability that allows you to answer the following questions: “What rows have changed for a user table?” and “Has a row changed?” Enforce Object Model was enhanced with the ChangeTrackingEnabled property of Table object indicating if CHANGE_TRACKING feature is enabled for a table - Change Data capture – this new SQL Server feature provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system Support for SQL Server 2008 New Features in ApexSQL Code New Data Types: objects with new datatypes are fully supported and can be processed within the templates without limitations; Datatype Mapping page of the Options dialog was extended reflecting new data types. HierarchyId and Spatial data types have limited support Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support: - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Note: Columns of such a type are treated as columns of user-defined types and thus IsUserDefinedDatatype property for such columns is True Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Note: Columns of such types are treated as columns of user-defined types and thus IsUserDefinedDatatype property for such columns is True Change Tracking and Change Data capture: both features doesn’t have corresponding properties in the Code Object Model but they can be processed in the templates without limitations - Change Tracking – built-in server’s ability that allows you to answer the following questions: “What rows have changed for a user table?” and “Has a row changed?” Enforce Object Model was enhanced with the ChangeTrackingEnabled property of Table object indicating if CHANGE_TRACKING feature is enabled for a table - Change Data capture – this new SQL Server feature provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system Support for SQL Server 2008 New Features in ApexSQL Clean New Data Types: all new data types are fully supported and references to columns or parameters of these types as well as user-defined types based on them are tracked without limitations Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support: - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Enhancements in indexes: objects having such indexes are fully supported, can be loaded and analyzed for dependencies - Spatial Indexes – allowing you to index a spatial column of geography or geometry data types - Relational (filtered) Indexes – optimized non-clustered indexes specially developed to allow working with queries from a well-defined subset of data using a filter predicate Enhancements in column properties: objects having or referencing such columns are supported and processed by the application without problems. All dependencies to such columns are tracked seamlessly to ordinary columns - Sparse columns - ordinary columns that have an optimized storage for null values - XML COLUMN_SET - a column of this type is only materialized when selected, and will return you all the non-NULL sparse columns in a row as an XML BLOB - FILESTREAM varbinary (max) – the new way of storing BLOB data allowing you to store the data in the file system. Unlike columns defined with FILESTREAM attribute the ordinary varbinary(max) columns store their data in the tables. Enhancements in procedure parameters and user-defined types: user-defined table types are treated seamlessly to ordinary user-defined types; they are loaded and analyzed by the application and participate in all dependency reports - User defined table type – allows you to define special types having table definition. Variables of such types can be used to store a result set for processing at a later time and passed as procedure parameters or used within the procedure bodies directly Data compression in tables, indexed views, indexes and constraints: the product works with compressed objects as well as uncompressed once; there will be no any differences in their analysis - Data (Row and Page) Compression – allows you to compress whole tables, indexed views, indexes, primary key and unique constraints; can be configured for a whole table, indexed view and non-clustered index and for each partition in case of partitioned indexes or tables. ROW compression means storing fixed length data in variable length storage format and PAGE compression means minimizing the data redundancy Change Tracking and Change Data capture: the application easily tracks dependencies to objects referenced via these features: for example, if you enable or disable CHANGE_TRACKING on a table from within a procedure or seamlessly use Change Data Capture system procedures calls from within your procedures. All such dependencies will be analyzed and taken into account by the product - Change Tracking – built-in server’s ability that allows you to answer the following questions: “What rows have changed for a user table?” and “Has a row changed?” - Change Data capture – this new SQL Server feature provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system Support for SQL Server 2008 New Features in ApexSQL Diff New Data Types: all new datatypes are fully supported and objects using the ones can be compared and synchronized without limitations Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support. The application can track and synchronize differences in the datatypes scale where it’s applicable, for example, time(5) against time(7): - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Enhancements in indexes: all new properties of the indexes are fully comparable and can be synchronized without limitations as parts of their parent objects - Spatial Indexes – allowing you to index a spatial column of geography or geometry data types - Relational (filtered) Indexes – optimized non-clustered indexes specially developed to allow working with queries from a well-defined subset of data using a filter predicate Enhancements in column properties: the application can track differences in all new column attributes, for example, SPARSE NULL against NULL; all of them are fully supported and can be synchronized in either direction along with parent objects - Sparse columns - ordinary columns that have an optimized storage for null values - XML COLUMN_SET - a column of this type is only materialized when selected, and will return you all the non-NULL sparse columns in a row as an XML BLOB - FILESTREAM varbinary (max) – the new way of storing BLOB data allowing you to store the data in the file system. Unlike columns defined with FILESTREAM attribute the ordinary varbinary(max) columns store their data in the tables. Enhancements in procedure parameters and user-defined types: all the enhancements are fully supported and processed by the product - User defined table type – allows you to define special types having table definition. Variables of such types can be used to store a result set for processing at a later time and passed as procedure parameters or used within the procedure bodies directly. The application detects differences in table definitions of these types, tracks dependencies on them to any object (for example, a procedure) and is able to synchronize such types and achieve their equality - Readonly parameters – indicates that the parameter cannot be updated or modified within the body of the procedure. The application can detect and synchronize differences in READONLY attribute seamlessly to other differences detected in the parameters Data compression in tables, indexed views, indexes and constraints: differences in data compression can be tracked for any respective object and synchronized without limitations - Data (Row and Page) Compression – allows you to compress whole tables, indexed views, indexes, primary key and unique constraints; can be configured for a whole table, indexed view and non-clustered index and for each partition in case of partitioned indexes or tables. ROW compression means storing fixed length data in variable length storage format and PAGE compression means minimizing the data redundancy Change Tracking and Change Data capture: both features are fully supported; the application determines whether CHANGE_TRACKING is enabled or disabled for a table and can synchronize the detected changes making parent tables equal, also any calls to Change Data Capture procedures from within your objects are also be tracked, differences can be reported and synchronized and dependencies to a captured object can be taken into account on synchronizing with the dependencies - Change Tracking – built-in server’s ability that allows you to answer the following questions: “What rows have changed for a user table?” and “Has a row changed?” - Change Data capture – this new SQL Server feature provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system Support for SQL Server 2008 New Features in ApexSQL Data Diff New Data Types: all new datatypes are fully supported; data in tables and indexed views exposing these types can be compared and synchronized without limitations Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support: - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Enhancements in column properties: the data from columns having these attributes is compared seamlessly to any other column - Sparse columns - ordinary columns that have an optimized storage for null values - XML COLUMN_SET - a column of this type is only materialized when selected, and will return you all the non-NULL sparse columns in a row as an XML BLOB. COLUMN_SET column is treated as a calculated column and as any of them can be compared but cannot be synchronized - FILESTREAM varbinary (max) – the new way of storing BLOB data allowing you to store the data in the file system. Unlike columns defined with FILESTREAM attribute the ordinary varbinary(max) columns store their data in the tables. Such columns are treated as ordinary BLOB’s and the data from them get compared and synchronized the same way Data compression in tables and indexed views: differences in data of the compressed objects are detected seamlessly to uncompressed ones and also fully supported for synchronization - Data (Row and Page) Compression – allows you to compress whole tables; can be configured for a whole table and/or indexed view for each partition in case of tables. ROW compression means storing fixed length data in variable length storage format and PAGE compression means minimizing the data redundancy Support for SQL Server 2008 New Features in ApexSQL Audit New Data Types: all new datatypes are fully supported; data in tables exposing these types can be audited and undone without limitations Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support: - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Enhancements in column properties: the data from columns having these attributes is audited seamlessly to any other column - Sparse columns - ordinary columns that have an optimized storage for null values - FILESTREAM varbinary (max) – the new way of storing BLOB data allowing you to store the data in the file system. Unlike columns defined with FILESTREAM attribute the ordinary varbinary(max) columns store their data in the tables. Such columns are treated as ordinary BLOB’s and the data from them get audited and undone the same way Data compression in tables: auditing of data of the compressed objects is done seamlessly to uncompressed ones; such data is also fully supported for undoing - Data (Row and Page) Compression – allows you to compress whole tables; can be configured for a whole table and/or indexed view for each partition in case of tables. ROW compression means storing fixed length data in variable length storage format and PAGE compression means minimizing the data redundancy Support for SQL Server 2008 New Features in ApexSQL Script New Data Types: all new datatypes are fully supported and objects using the ones can be scripted without limitations Date and Time – simplifying your work with date and time data, providing increased date range, fractional seconds precision, and time zone support. The application can track and synchronize differences in the datatypes scale where it’s applicable, for example, time(5) against time(7): - Date – defines a date within the accuracy of one day - Time – defines a time of a day based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetimeoffset – defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock within the accuracy of 100 nanoseconds - Datetime2 – defines a date that is combined with a time of day that is based on 24-hour clock within the accuracy of 100 nanoseconds: can be considered as an extension of the existing datetime type Hierarchy – allowing you to construct relationships among data elements within a table - Hierarchyid – represents position in a hierarchy and sets the desired relationship between rows Spatial – predefined CLR data types in SQL Server allowing you to represent information about the physical location and shape of geometric objects - Geography – represents data in a round-earth coordinate system and stores ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates - Geometry – represents data in a Euclidean (flat) coordinate system Enhancements in indexes: all new properties of the indexes are fully scripted now without limitations as parts of their parent objects - Spatial Indexes – allowing you to index a spatial column of geography or geometry data types - Relational (filtered) Indexes – optimized non-clustered indexes specially developed to allow working with queries from a well-defined subset of data using a filter predicate Enhancements in column properties: the application can script all new column attributes, all of them are fully supported - Sparse columns - ordinary columns that have an optimized storage for null values - XML COLUMN_SET - a column of this type is only materialized when selected, and will return you all the non-NULL sparse columns in a row as an XML BLOB - FILESTREAM varbinary (max) – the new way of storing BLOB data allowing you to store the data in the file system. Unlike columns defined with FILESTREAM attribute the ordinary varbinary(max) columns store their data in the tables. Data compression in tables, indexed views, indexes and constraints: data compression options now can be processed for any respective object without limitations - Data (Row and Page) Compression – allows you to compress whole tables, indexed views, indexes, primary key and unique constraints; can be configured for a whole table, indexed view and non-clustered index and for each partition in case of partitioned indexes or tables. ROW compression means storing fixed length data in variable length storage format and PAGE compression means minimizing the data redundancy Enhancements in procedure parameters and user-defined types: all the enhancements are fully supported and processed by the product - User defined table type – allows you to define special types having table definition. Variables of such types can be used to store a result set for processing at a later time and passed as procedure parameters or used within the procedure bodies directly. The application detects differences in table definitions of these types, tracks dependencies on them to any object (for example, a procedure) and is able to synchronize such types and achieve their equality - Readonly parameters – indicates that the parameter cannot be updated or modified within the body of the procedure. The application can detect and synchronize differences in READONLY attribute seamlessly to other differences detected in the parameters Change Tracking and Change Data capture: both features are fully supported by the application - Change Tracking – built-in server’s ability that allows you to answer the following questions: “What rows have changed for a user table?” and “Has a row changed?” The table documentation template was redesigned to indicate if Change Tracking feature is enabled for a table; enabling or disabling of this feature shows up in the DDL section of your tables - Change Data capture – this new SQL Server feature provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system. All system procedures of this feature can be documented along with SQL Server Agent jobs for capturing and cleaning up data in a database This list represents SQL 2008 support as of April 23, 2009. As new SQL 2008 features are added to our software and released, the list might no longer fully reflect the current level of SQL Server 2008 support at dates after Dec 17th, 2009. If there is a particular feature not listed on this page you would like to inquire about please email support@apexsql.com directly. Labels: Articles ...The Power of the Parser 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 versionsThe 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 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. 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. Labels: Articles ... |
© 2010 ApexSQL Tools All Rights Reserved | 1.919.968.8444 | Contact Us | Terms of Use | Privacy Policy