SQL Server database trigger auditing – How to customize data tracking and reporting – Part 1

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

How to customize SQL database documentation

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

How to clean up SQL database code by removing unused parameters

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

How to improve SQL code layout and presentation

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

How to preview SQL Server code structure and control flow

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

How to track executed SQL Server queries

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

How to document a SQL Server database with extended properties

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

Documenting SSIS packages

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

Synchronizing databases using the transaction log – SQL Server Log shipping

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

Top SQL Server Memory Pressure Counters

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 performance

March 3, 2015

Document SQL Server instance settings/objects

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

How to compare SQL databases in SSMS

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

SQL Server deadlock notifications

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 forward

March 2, 2015