Similarly as Watches, the Lookups feature allows the ApexSQL Trigger report to show a meaningful value from a related SQL Server database table, along with the value of the field that was audited. A more human-readable value can be shown in the audit reports, for example “111” (customer’s ID number) versus “John Smithson” (customer’s actual name).March 12, 2015
ApexSQL Trigger uses SQL Server database triggers for capturing and storing changes that occur on the audited data and schema. More specifically, the DML database triggers are used for capturing INSERT, UPDATE or DELETE statements, and DDL trigger is used for capturing CREATE, ALTER or DROP statements. Now, since there are various types of columns in each database table, reading the audit report is often not very user-friendly and this especially applies to foreign key values in different tables.March 12, 2015
Using the default settings and options when documenting a SQL database via ApexSQL Doc does not have to be the only option, especially if there are additional options available, like Styles. SQL databases documentation can be personalized to make it stand out and differ, adding to its professional look.March 9, 2015
If changes need to be made to a column it is necessary to perform some impact assessment in order to determine what objects will be affected, meaning that SQL table column dependencies within a SQL Server database need to be foundMarch 9, 2015
Finding parameters and SQL variables that are only defined in the existing SQL Server stored procedures and functions, but never actually used, is not a problem, but for maintaining complex code, with dozens of parameters and variables a tool like ApexSQL Refactor can be used, which is a free SQL Server Management Studio and Visual Studio add-in, and useful SQL query formatter.March 9, 2015
ApexSQL Script is a SQL Server database migration tool which can create SQL scripts by converting SQL database objects and data into a single or multiple deployment SQL scripts, .NET solutions, or executable installers. In many cases when moving a SQL database, there’s no need to migrate the entire SQL database, but only a part of it.March 5, 2015
In this article, improving of T-SQL code layout and presentation using various features and options in ApexSQL Complete will be described. Additionally, the Code structure feature will be examined, how ApexSQL Complete highlights identifiers with brackets, how it manages closing characters, aliases, and column list.March 4, 2015
In this article, we’ll introduce the Code structure feature in ApexSQL Complete. ApexSQL Complete is a productivity add-in for SSMS and Visual Studio, that features SQL auto-complete, SQL formatting, and more. The Code structure feature allows the user to easily view and find all important code blocks, and to review the SQL code structure of the query.
The Code structure provides a tree-like form of the SQL code from the query, and it’s presented in its own panel in the SSMS, on the left side of the query window.
The SQL Code structure feature can be enabled from the ApexSQL Complete menu, with a click on the Code structure command, or by initiating it with the shortcut Ctrl+Q Ctrl+W:
When the Code structure is enabled, it will open its own panel beside the query window. In the Code structure window, all important parts of the code can be seen, which are used in the query:
To navigate to a particular code block in the query, scroll up/down in the Code structure panel, find that block and double-click on it:
As it’s shown on the picture above, with a double-click on the particular code block, in this case Create view vbooks will find that part of the code in the query window, center it, and highlight the entire code block.
The Code structure feature also provides the ability to expand the code, and navigate to the part which is under the main code:
ApexSQL Complete’s Code structure feature is particularly helpful with the large scripts. By using this feature, the user can easily navigate through the most important parts of the code, instead of having to scroll down the entire query.
If the script has more than 10,000 lines of code, by enabling the SQL Code structure feature, the code can be browsed and only the significant parts can be looked, which have a direct impact on the behavior of the script. For example, if the code for the specific trigger needs to be seen, in this case Create trigger HumanResources.dEmployee, and when navigate to it, it can be also expanded, and the other related code, which defines the trigger behavior can be seen:
When the script is created, if some part of the code is missing it can be seen in the Code structure panel. When that missing part of the code is added, it will be automatically added to the Code structure panel, by default.
If the following code is inserted in the query, while the Code structure is enabled:
CREATE PROCEDURE AddEmp5(@mgrid hierarchyid, @EmpName nvarchar(50)) AS BEGIN DECLARE @last_child hierarchyid DECLARE @last_child2 hierarchyid SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION UPDATE Org_T5 SET @last_child = LastChild = EmployeeId.GetDescendant(LastChild,NULL), @last_child2 = LastChild2 = EmployeeId.GetDescendant(LastChild2,NULL) WHERE EmployeeId = @mgrid INSERT Org_T5(EmployeeId, EmployeeId2, EmployeeName) VALUES(@last_child, @last_child, @EmpName) COMMIT END ; GO
This will automatically add Create procedure AddEmp5 to the Code structure panel, and all related code can be expanded, as it’s shown on the picture below:
This option can be changed, so that the Code structure will be automatically refreshed, or manually refreshed.
Changing to automatically, or manually refresh, can be done with a click on two icons in the top left corner of the Code structure window.
March 4, 2015
SQL database scripting tool ApexSQL Script natively integrates with the Subversion, Microsoft Visual SourceSafe, Git, SourceGear Vault, and Team Foundation Server source control systems.
ApexSQL Script allows to export complete SQL database, or just a couple of its objects, to one of the mentioned source control systems.March 4, 2015
In this article, a feature in ApexSQL Complete that keeps track of executed queries will be introduced. ApexSQL Complete is a productivity add-in for SSMS and Visual Studio, that features SQL auto-complete, SQL formatting, and more.March 4, 2015
In this article, the snippets feature in ApexSQL Complete will be described along with examples of using snippets macro variables.March 4, 2015
ApexSQL Doc features a built-in graphical dependencies viewer that can assist you in visually conveying your database dependencies in the final document.March 4, 2015
ApexSQL Doc, a SQL Server documentation tool, displays descriptions for SQL database objects using the extended properties that are stored in the SQL Server database. The Extended property editor feature allows you to add or edit your database object descriptions using the extended properties that are stored in your SQL Server database. The extended property of an object can be fully customized from a specialized dialog, opened by clicking the Extended property editor button in the Tools group of the Home tab:March 4, 2015
ApexSQL Doc is a SQL documentation tool that allows documenting of SQL Server Integration Service (SSIS) packages, Analysis Service databases (SSAS), multiple SQL databases, Tablau sites and SharePoint farms easily and automatically. ApexSQL Doc allows you to quickly develop a summary of your SSIS Packages. It can visually display the control and data flow as well as properties and expressions used for each component.March 4, 2015
No matter how well managed your systems are, accidents may still occur, and potentially lead to disastrous consequences. In order to ensure that there is a disaster recovery solution available, it is always good to have a standby copy of a primary database on another SQL Server instance.
The first way to achieve this is to utilize the SQL Server Log shipping.March 4, 2015
Even a superficial reading of books and articles on SQL Server performance monitoring will tell you that SQL Server memory pressure is the first thing you should monitor. Not the only thing, far from it, and some CPU, I/O and internal SQL Server counters are critically important to get the complete picture but it’s the memory counters that often show us the first indication of suboptimal performanceMarch 3, 2015
A SQL Server has multiple instance-level settings and objects, depending on the version. These can have a significant effect on SQL Server’s behavior and performance, and it is important to keep track of their configuration or even document them, for that matter.March 2, 2015
ApexSQL Doc is a database development tool for documenting multiple databases, SQL Server Integration Service (SSIS) packages, Analysis Service (SSAS) cubes and Reporting Service (SSRS) items, Tableau sites and Sharepoint farms. It allows specifying the exact server objects, database objects, attributes, and even specific object instances that will be included in the documentation.March 2, 2015
ApexSQL Diff, a SQL Server comparison and synchronization tool includes the ability to work from within SQL Server Management Studio. In addition of comparing and synchronizing SQL databases directly from SSMS, it can also compare and synchronize backups, script folders, snapshots and versions from source control projects. This should improve productivity and speed up common tasks.March 2, 2015
The existence of a deadlock may be indicated by application slowness, error messages or cases when a SELECT statement takes a long time to finish or terminates prematurely with the message:
“Transaction (Process ID xx) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction”
All this indicates that SQL Server encountered a deadlock. A deadlock is a common issue with SQL Server. It is a circular blocking chain when two or more processes holding locks on data where each process wants to lock the data already locked by another process, and each process is waiting on the other process to complete before moving forwardMarch 2, 2015