Improving productivity with SQL snippets

SQL snippets can be a big boost to productivity when writing T-SQL. First, to use a SQL snippet in a script, there is no need to know the syntax, only the purpose of that SQL snippet (e.g. delete an object, create a table, etc.). Using snippets also reduces of the lines of code that have to be typed, and thus decreases the potential for errors that could occur from typing.

May 5, 2016

Retrieve unsaved SQL query scripts due to SQL Server Management Studio crash

Recovering work after SQL Server Management Studio crashes

Imagine a situation of working in SSMS, and suddenly the power goes out, the system gets crashed, SSMS gets crashed or the work session was abruptly terminated for any other reason, which causes the loss of unsaved work on various SQL scripts. In this situation SSMS provide prompt window for recovered data after restarting:

March 21, 2016

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:

The Code structure command

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 code faster with SQL Server code snippets

ApexSQL Complete is a SQL Server Management Studio and Visual Studio add-in designed to speed up the coding. It completes SQL statements automatically, fills in SQL fragments, generates outlines for the SQL script structure, and speeds up coding with T-SQL code snippets.

February 24, 2015