Database refactoring is a simple database transformation which improves its design without changing the behavioral semantics of the database. The process of database refactoring defines how to safely evolve a database schema in small steps
In this article the comparison of refactoring options offered in ApexSQL Refactor and RedGate® SQL Prompt will be shown.
RedGate® SQL Prompt – The Encapsulate as a new stored procedure option
A stored procedure is SQL code written only once and is maintained in a single instance that can be called from several different applications. A procedure compiles the first time it is executed and creates an execution plan that is reused for following executions. It takes less time to process the procedure since the query processor does not have to create a new plan.
In RedGate® SQL Prompt, Encapsulate as a new stored procedure is the only feature for encapsulating code.
To implement this feature, select the code in the query window.
From the SQL Prompt menu, choose the Encapsulate as a new store procedure option.
In the next dialog user can choose whether to replace the source SQL code.
If the chosen option is Do not replace, the generated CREATE PROCEDURE script will be opened in a new query window, and the source code will remain in the query window from which is selected.
The Replace with reference to new stored procedure option replaces the selected source code in the query window with the EXEC statement.
If the user chooses the Replace with reference to new stored procedure option the generated CREATE PROCEDURE script will be opened in a new query window, and the source code will be replaced with the EXEC statement in the query window from which the code was selected:
EXEC dbo.usp_City ;
The @City variable must be declared manually, since SQL Prompt doesn’t recognize it as an input parameter
ApexSQL Refactor – The Encapsulate code as option
Encapsulate as a stored procedure
ApexSQL Refactor also has the Encapsulate code as a stored procedure option. To use it, from the ApexSQL Refactor menu choose the Encapsulate code as option and the Stored Procedure option from the submenu
ApexSQL Refactor recognizes the input parameters of the procedure. The EXECUTE statement for the encapsulated procedure will be created with the input parameter:
Clicking the Open script button will open the code in the query editor:
To open only the EXECUTE statement, click the Encapsulate now button. In this case, the generated CREATE PROCEDURE script from the preview will be executed in the background, and only the reference to stored procedure will be opened in the new query window:
EXECUTE dbo.usp_City @City
In addition to the Encapsulate code as a store procedure option, ApexSQL Refactor offers more options for encapsulating code.
Encapsulate code as an inline scalar function
User defined functions (UDFs) give a great benefits in terms of encapsulation and code reusability. A query that embeds the original expression inline uses such would run faster than a query that invokes the function, because when invoking a scalar UDF as part of a query, and pass the function a column from the table as input, the function is invoked for each row.
Type the T-SQL code in the query editor and from the ApexSQL Refactor menu choose the Encapsulate code as option and the Scalar Inline Function option from the submenu:
Encapsulate code as an inline table function
Table-valued user defined function (UDF) allows encapsulating rich T-SQL logic that returns a result set, and using that result set anywhere that a real table is allowed. Table-valued UDF that returns table result set can be multi statement and inline. Multi statement table-valued UDF can add flexibility to T-SQL solutions, but using an inline UDF is almost always more efficient. Type the code in the query editor and from the ApexSQL Refactor menu, choose the Encapsulate code as option and the Table Inline Function option from the submenu:
Encapsulate code as a view
The Encapsulate code as a view option will encapsulate code from the query editor as a View. From the ApexSQL Refactor menu choose the Encapsulate code as option and the View option from the submenu:
In the next article some additional refactors, which are not supported by RedGate® SQL Prompt, will be described.
See also:
ApexSQL Refactor vs RedGate® SQL Prompt – Part 2 – Additional refactors
ApexSQL Refactor vs RedGate® SQL Prompt Formatting – Part 3 – Common formatting options compared
ApexSQL Refactor vs. RedGate® SQL Prompt – Part 4 – Additional formatting options
ApexSQL Refactor vs. RedGate® SQL Prompt – Part 5 – Additional formatting features
Disclaimer: All product and company names are trademarks™ or registered® trademarks of their respective holders. ApexSQL is not affiliated, associated, authorized, endorsed by, or in any way officially connected with them or any of its subsidiaries or its affiliates
October 25, 2013
