Refactoring database schemas

I want to talk about refactoring database schemas MS SQL Server.

Refactoring is a change in the internal structure of the software, aimed at facilitating the understanding of its work and simplifying the modification without affecting the observed behavior.

- Martin Fowler

About refactoring of a code speak for a long time. At the moment, a lot of literature has been written, a lot of tools have been created that help to perform code refactoring.
But about the refactoring of database schemas is not so much information. I decided to fill this gap a bit and share my experience.

')

How to understand that it is time to refactor?

By doing something for the first time, you just do it. Doing something similar the second time, you frown at the need to repeat, but still repeat the same thing. By doing something similar for the third time, you start refactoring.

- Don Roberts

Martin Fowler introduced the concept of “Code with a ghost”, denoting the code that needs to be refactored.
With a nice smell is the code in which:

duplication occurs
there are big methods
there are methods with a large number of parameters
there is a switch statement

By analogy with this, one can single out the general shortcomings of the database schema, which indicate the need for the application of refactoring. These disadvantages include the following.

Multipurpose columns (or useless columns). Suppose we have a table containing information on orders. There is an optional InvoiceId field of type int in the table. Imagine that the sales process in a company is built in such a way that this field is never filled. Starting from the new year, managers have become necessary to put a customer assessment on orders (from 1 to 10 on the results of telephone calls). There is no such field in the table and the managers start typing this data into the InvoiceId field (for example, because the IT people told them that it would take a month to add a new field). This will cause problems when the InvoiceId field is used for its intended purpose.
Multipurpose tables. An example would be the Customer table in which information about individuals and legal entities is stored. In such a case, columns with NULL values inevitably appear.
Redundant data. For example, the presence of the Customer Address field in the order table may lead to the case when several orders of the same customer have different addresses.
Tables with a large number of columns. The presence of a large number of columns can mean that the table stores the attributes of more than one entity. In this case, you probably need to apply the "Split the table" refactoring.
Multivalued columns. Multi-valued columns are those in which several different pieces of information are represented in different positions. For example, in the order table there is an OrderNumber field containing data of the form XXX20150908000125. Where XXX is the product code, 20150908 is the order date, 000125 is the order number. In practice, it is often found necessary to break the field into parts so that it is easier to process these fields as separate elements.

Some useful tips for applying refactoring

Rate the scale of the disaster .
Before changing anything, make sure that you do not break external applications that use your database. If you had to maintain a database that was inherited, most likely you do not know who (what) and how it is used. Make a list of applications that use your database. If possible, ask colleagues developing these applications to give you a list of the objects they use. After that, agree with them your changes, agree on joint testing.
Pay special attention to the database tables for which rights are issued. This is a potential source of problems.
Discuss with colleagues so that instead of spreadsheets they switch to using views (procedures).
When all references to the database will be implemented by means of procedures / views / functions, it will be much easier for you to refactor.
Do not make many changes at once .
The smaller the change, the easier it will be to find an error in the event of a failure.
Check changes with tests .
After each change, run tests to make sure nothing is broken.
Use sandboxes .
There is no need to refactor production, even if the change is negligible. Use for refactoring test sites. Then do a full regression test. And only after that, make the change in the productive database.

Practical examples

I will show the application of some methods of refactoring using the example of the Northwind database ( download link ).
As a tool, I will use SQL Server Management Studio (SSMS) with the SQL Refactor Studio plugin installed. This plugin adds refactoring functions to SSMS.

Source diagram

Testing

After each change, we will run a test to make sure everything is still working.
For example, I created the dbo.RunTests procedure, which selects data from all representations in the database (of course, this does not provide us with full test coverage).
If there were no errors during the procedure, the procedure returns OK, otherwise Failed.

CREATE PROCEDURE dbo.RunTests AS DECLARE @Script nvarchar(max) = '', @Failed bit = 0 DECLARE crs CURSOR FOR SELECT 'IF OBJECT_ID(''tempdb..#tmp'') IS NOT NULL DROP TABLE #tmp SELECT * INTO #tmp FROM [' + object_schema_name(o.object_id) + '].[' + o.name + ']' FROM sys.objects o WHERE o.type = 'V' OPEN crs FETCH NEXT FROM crs INTO @Script WHILE @@fetch_status = 0 BEGIN BEGIN TRY EXEC sp_executesql @Script END TRY BEGIN CATCH SET @Failed = 1 SELECT 'Failed' AS Status, ERROR_MESSAGE() AS Details, @Script AS [Script] END CATCH FETCH NEXT FROM crs INTO @Script END CLOSE crs DEALLOCATE crs IF @Failed = 0 SELECT 'OK' AS [Status] RETURN 0 /* EXEC dbo.RunTests */ GO

Refactoring "Rename Object"

Of course, I don’t know about you, but when I create the table I give it a singular name (dbo.Entry and not dbo.Entrie s ).
So let's try to rename the dbo.Customer s table to dbo.Customer . There is one unpleasant (and very routine) process. It is necessary to rename the table so that the code using it does not break. To do this, you need to find and make a correction to it. Taking advantage of the standard View Dependencies, we see that the table is used in one view and there are two tables referencing dbo.Customers .

In principle, making a correction to one view after renaming a table is a trifling matter.
Well, in battle! Rename the table and run the test (the dbo.Customer and Suppliers by City view should break).
However, instead of the expected one line test gave me as many as five.

Then I decided to check the dependencies of the table using the advanced View Dependencies included in the package of SQL Refactor Studio. He already counted five views (the test showed that it was five representations that broke) and found one procedure (not covered by tests).

Six objects - this is more serious. And imagine that you need to fix the code in 50+ objects. Do you still want to rename the table? :) Handles will be hard, so let's use automation.
We use the Rename function included in the SQL package Refactor Studio. We select a table in the Object Explorer (hereinafter OE), from the context menu, select the SQL Refactor Studio -> Rename item. Enter a new name (Customer) and press the button Genarate rename script . There is also an opportunity to see the dependencies and uncheck the objects in which you do not need to rename.

As a result, a new tab has opened with the generated script.

Generated script

 use northwind go set transaction isolation level serializable set xact_abort on go if object_id('tempdb..#err') is not null drop table #err go create table #err(flag bit) go begin transaction go exec sp_rename 'dbo.Customers', 'Customer', 'OBJECT' go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER view dbo.[Orders Qry] AS SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Customer.CompanyName, Customer.Address, Customer.City, Customer.Region, Customer.PostalCode, Customer.Country FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID go raiserror('update view <dbo.Orders Qry>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER view dbo.[Quarterly Orders] AS SELECT DISTINCT Customer.CustomerID, Customer.CompanyName, Customer.City, Customer.Country FROM Customer RIGHT JOIN Orders ON Customer.CustomerID = Orders.CustomerID WHERE Orders.OrderDate BETWEEN '19970101' And '19971231' go raiserror('update view <dbo.Quarterly Orders>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER view dbo.Invoices AS SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID, Customer.CompanyName AS CustomerName, Customer.Address, Customer.City, Customer.Region, Customer.PostalCode, Customer.Country, (FirstName + ' ' + LastName) AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, "Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount, (CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight FROM Shippers INNER JOIN (Products INNER JOIN ( (Employees INNER JOIN (Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) ON Products.ProductID = "Order Details".ProductID) ON Shippers.ShipperID = Orders.ShipVia go raiserror('update view <dbo.Invoices>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER PROCEDURE dbo.CustOrderHist @CustomerID nchar(5) AS SELECT ProductName, Total=SUM(Quantity) FROM Products P, [Order Details] OD, Orders O, Customer C WHERE C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID GROUP BY ProductName go raiserror('update storedprocedure <dbo.CustOrderHist>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER view dbo.[Customer and Suppliers by City] AS SELECT City, CompanyName, ContactName, 'Customers' AS Relationship FROM Customer UNION SELECT City, CompanyName, ContactName, 'Suppliers' FROM Suppliers --ORDER BY City, CompanyName go raiserror('update view <dbo.Customer and Suppliers by City>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go ALTER view dbo.[Sales Totals by Amount] AS SELECT "Order Subtotals".Subtotal AS SaleAmount, Orders.OrderID, Customer.CompanyName, Orders.ShippedDate FROM Customer INNER JOIN (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) ON Customer.CustomerID = Orders.CustomerID WHERE ("Order Subtotals".Subtotal >2500) AND (Orders.ShippedDate BETWEEN '19970101' And '19971231') go raiserror('update view <dbo.Sales Totals by Amount>...', 0, 1) with nowait go if (@@error <> 0) and (@@trancount > 0) rollback transaction go if (@@trancount = 0) begin insert into #err(flag) select cast(1 as bit) begin transaction end go if exists (select * from #err) begin print 'the database <northwind> update failed' rollback transaction end else begin print 'the database <northwind> update succeeded' commit transaction end go

Run the script for execution.
Run the test.
Voila! We renamed the table and did not break the existing code.
We will also deal with all tables whose names end with s . It's so easy, isn't it?

Refactoring "Adding a lookup table"

This operation allows you to create a lookup table for an existing column.
The need for this operation may be due to the following reasons:

Introduction of referential integrity to ensure data quality;
Provide detailed descriptions. For example, you may need to add a new attribute to the description of an entity. If an entity is not allocated to this table in this case, it will be necessary to add this attribute to the necessary tables, which will lead to the denormalization of the schema.

Let's look again at our dbo.Customer table. You are not confused by the presence of the fields City, Region and Country in one place? These are attributes of one entity.

In the dbo.Employees table the same trouble. Seemingly clear violation of the 3rd normal form.

Let's start rectifying the case as follows:
1. Create a directory dbo.City (CityId, CityName)
2. In the dbo.Customer table , add the CityId field.
3. Create a foreign key.

Again, to save time, use the Add Lookup Table function. In OE, select the City field of the dbo.Customer table and in the context menu call the SQL Refactor Studio -> Add Lookup Table option .

In the window that appears fill in the field. Click Next , then Finish and a script is formed in a new window.
The script creates the dbo.City table, fills it with data, creates the CityId field in the dbo.Customer table, creates a foreign key.

Generated script

 use northwind go -- Step 1. Create lookup table. CREATE TABLE dbo.City ( CityId INT NOT NULL identity(1, 1) ,CityName NVARCHAR(15) NULL ,CONSTRAINT PK_City PRIMARY KEY CLUSTERED (CityId) ,CONSTRAINT City_ixCityName UNIQUE (CityName) ) GO -- Step 2. Fill lookup table. INSERT dbo.City (CityName) SELECT DISTINCT City FROM dbo.Customer GO -- Step 3. Add column. ALTER TABLE dbo.Customer ADD CityId INT NULL GO -- Step 4. Update table dbo.Customer. UPDATE s SET s.CityId = t.CityId FROM dbo.Customer s INNER JOIN dbo.City t ON s.City = t.CityName GO -- Step 5. Create foreign key constraint. ALTER TABLE dbo.Customer ADD CONSTRAINT FK_Customer_City FOREIGN KEY (CityId) REFERENCES dbo.City (CityId) GO

We run the script and tests (although we didn’t seem to break anything here).
All is ready. We go further.

Refactoring "Moving Fields"

So, we dealt with the City field. It remains to deal with the fields Region and Country. These fields are attributes of the City entity. So let's move them from the dbo.Customer table to dbo.City .
Again, SQL Refactor Studio provides the Move Columns function. We will use it!
We select dbo.Customer table in OE, in the context menu, select the item “SQL Refactor Studio -> Move columns” . In the dialog that appears, in the drop-down list, select the dbo.City table. We transfer the Region and Country fields to dbo.City .
If you move a field, you will receive a message stating that the field on which the index is built cannot be moved - temporarily remove this index.

Click Next , then Finish . Get the script in a new window.
The script creates the Region and Country fields in the dbo.City table and fills them with data.
The script also has a commented code that removes fields and lists the objects in which you need to make changes.
Let's not delete the fields now, let's do it in the next step.

Generated script

 USE northwind GO -- STEP 1. Add new column(s) -- IF NOT EXISTS ( SELECT * FROM syscolumns s WHERE s.NAME = 'Region' AND s.id = object_id(N'dbo.City') ) BEGIN ALTER TABLE dbo.City ADD Region NVARCHAR(15) NULL END GO IF NOT EXISTS ( SELECT * FROM syscolumns s WHERE s.NAME = 'Country' AND s.id = object_id(N'dbo.City') ) BEGIN ALTER TABLE dbo.City ADD Country NVARCHAR(15) NULL END GO GO -- STEP 2. Copy data -- -- (You can modify this query if needed) SET IDENTITY_INSERT dbo.City ON INSERT INTO dbo.City WITH (TABLOCKX) (CityId) SELECT CityId FROM dbo.Customer src WHERE NOT EXISTS ( SELECT * FROM dbo.City dest WHERE src.CityId = dest.CityId ) SET IDENTITY_INSERT dbo.City OFF UPDATE dest WITH (TABLOCKX) SET dest.Region = src.Region ,dest.Country = src.Country FROM dbo.City dest INNER JOIN dbo.Customer src ON (src.CityId = dest.CityId) GO -- STEP 3. Check and modify this dependent objects -- /* northwind.dbo.[Orders Qry] /*View*/ northwind.dbo.[Quarterly Orders] /*View*/ northwind.dbo.Invoices /*View*/ northwind.dbo.CustOrderHist /*StoredProcedure*/ northwind.dbo.[Customer and Suppliers by City] /*View*/ northwind.dbo.[Sales Totals by Amount] /*View*/ */ -- STEP 4. Drop column(s) -- -- (Uncomment or run separately this query) /* alter table dbo.Customer drop column Region alter table dbo.Customer drop column Country */ GO

We execute a script and tests.
Go to the next step.

Refactoring "Delete Object"

Performing the previous refactorings, we left some garbage (the City, Region, Country fields in the dbo.Customer table).
Let's make a clean! But if we just remove the fields, everything will break again.
You can use the Encapsulate Table With View refactoring.
Create a dbo.CustomerV view and replace the use of the table with its representation in the entire database.

 CREATE VIEW dbo.CustomerV AS SELECT c.CustomerID, c.CompanyName, c.ContactName, c.ContactTitle, c.Address, ct.CityName City, ct.Region, c.PostalCode, ct.Country, c.Phone, c.Fax, c.CityId FROM dbo.Customer AS c LEFT JOIN dbo.City AS ct ON c.CityId = ct.CityId

Next, using View Dependencies, we look at the dependencies for the dbo.Customer table:

View each object. If in any object our fields are used, we script the object (the Script object button on the toolbar) and make changes.
As a result, I got this script:

Encapsulate Table With View

 ALTER view dbo.[Sales Totals by Amount] AS SELECT st.Subtotal AS SaleAmount, o.OrderID, c.CompanyName, o.ShippedDate FROM dbo.CustomerV c JOIN ( dbo.Orders o JOIN "Order Subtotals" st ON o.OrderID = st.OrderID ) ON c.CustomerID = o.CustomerID WHERE (st.Subtotal >2500) AND (o.ShippedDate BETWEEN '19970101' And '19971231') GO ALTER view dbo.[Quarterly Orders] AS SELECT DISTINCT c.CustomerID, c.CompanyName, c.City, c.Country FROM dbo.CustomerV c RIGHT JOIN dbo.Orders o ON c.CustomerID = o.CustomerID WHERE o.OrderDate BETWEEN '19970101' And '19971231' GO ALTER view dbo.[Orders Qry] AS SELECT o.OrderID, o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate, o.ShippedDate, o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, o.ShipCity, o.ShipRegion, o.ShipPostalCode, o.ShipCountry, c.CompanyName, c.Address, c.City, c.Region, c.PostalCode, c.Country FROM dbo.CustomerV c INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID GO ALTER view dbo.Invoices AS SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID, Customer.CompanyName AS CustomerName, Customer.Address, Customer.City, Customer.Region, Customer.PostalCode, Customer.Country, (FirstName + ' ' + LastName) AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName, "Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity, "Order Details".Discount, (CONVERT(money,("Order Details".UnitPrice * Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight FROM Shippers INNER JOIN (Products INNER JOIN ( (Employees INNER JOIN (dbo.CustomerV Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID) ON Products.ProductID = "Order Details".ProductID) ON Shippers.ShipperID = Orders.ShipVia GO ALTER view dbo.[Customer and Suppliers by City] AS SELECT City, CompanyName, ContactName, 'Customers' AS Relationship FROM dbo.CustomerV UNION SELECT City, CompanyName, ContactName, 'Suppliers' FROM Suppliers --ORDER BY City, CompanyName GO

Run the script, then delete the fields and run the tests.
If everything is OK - you are well done, all neatly done.

Refactoring “Adding CRUD Methods”

This refactoring involves the creation of stored procedures that provide access (SELECT, INSERT, UPDATE, DELETE) to database tables.
There are several reasons for using this refactoring:

Hide from external applications database structure. External applications will access the database only through stored procedures. This allows you to easily change the database structure without changing external applications.
Implementation in the procedures of additional checks (business logic, access rights, etc.);
Saving information about who requested the data and when;
Performance issues are more efficiently addressed when SQL is in the database.

So, let's create access methods for our dbo.Customer table. Let's use the Add CRUD Methods method from the SQL Refactor Studio package. We select a table in OE, then in the context menu we select the SQL Refactor Studio -> Add CRUD Methods item.

In the dialog that appears, choose which methods we need to create. If necessary, change the names of the methods. Click Next . If you wish, you can configure the rights to the procedures, for this you need to note the necessary roles. Click Finish and get the script with stored procedures. Execute the script.

Generated script

 IF (object_id(N'dbo.Customer_Create') IS NULL) BEGIN EXEC ('create procedure dbo.Customer_Create as return 0') END GO -- ============================================= -- -- dbo.Customer_Create -- -- Create method. -- -- Date: 07.09.2015, @HabraUser -- -- ============================================= ALTER PROCEDURE dbo.Customer_Create @CustomerID NCHAR(5) ,@CompanyName NVARCHAR(40) ,@ContactName NVARCHAR(30) = NULL ,@ContactTitle NVARCHAR(30) = NULL ,@Address NVARCHAR(60) = NULL ,@PostalCode NVARCHAR(10) = NULL ,@Phone NVARCHAR(24) = NULL ,@Fax NVARCHAR(24) = NULL ,@CityId INT = NULL AS BEGIN SET NOCOUNT ON INSERT INTO dbo.Customer ( CustomerID ,CompanyName ,ContactName ,ContactTitle ,Address ,PostalCode ,Phone ,Fax ,CityId ) VALUES ( @CustomerID ,@CompanyName ,@ContactName ,@ContactTitle ,@Address ,@PostalCode ,@Phone ,@Fax ,@CityId ) RETURN 0 END /* declare @CustomerID NChar(5), @CompanyName NVarChar(40), @ContactName NVarChar(30), @ContactTitle NVarChar(30), @Address NVarChar(60), @PostalCode NVarChar(10), @Phone NVarChar(24), @Fax NVarChar(24), @CityId Int select @CustomerID = 'CustomerID', @CompanyName = 'CompanyName', @ContactName = 'ContactName', @ContactTitle = 'ContactTitle', @Address = 'Address', @PostalCode = 'PostalCode', @Phone = 'Phone', @Fax = 'Fax', @CityId = null exec dbo.Customer_Create @CustomerID = @CustomerID, @CompanyName = @CompanyName, @ContactName = @ContactName, @ContactTitle = @ContactTitle, @Address = @Address, @PostalCode = @PostalCode, @Phone = @Phone, @Fax = @Fax, @CityId = @CityId */ GO IF (object_id(N'dbo.Customer_Get') IS NULL) BEGIN EXEC ('create procedure dbo.Customer_Get as return 0') END GO -- ============================================= -- -- dbo.Customer_Get -- -- Read method. -- -- Date: 07.09.2015, @HabraUser -- -- ============================================= ALTER PROCEDURE dbo.Customer_Get @CustomerID NCHAR(5) AS BEGIN SET NOCOUNT ON SELECT CustomerID ,CompanyName ,ContactName ,ContactTitle ,Address ,PostalCode ,Phone ,Fax ,CityId FROM dbo.Customer WHERE CustomerID = @CustomerID RETURN 0 END /* declare @CustomerID NChar(5) select @CustomerID = ? exec dbo.Customer_Get @CustomerID = @CustomerID */ GO IF (object_id(N'dbo.Customer_Save') IS NULL) BEGIN EXEC ('create procedure dbo.Customer_Save as return 0') END GO -- ============================================= -- -- dbo.Customer_Save -- -- Update method. -- -- Date: 07.09.2015, @HabraUser -- -- ============================================= ALTER PROCEDURE dbo.Customer_Save @CustomerID NCHAR(5) ,@CompanyName NVARCHAR(40) ,@ContactName NVARCHAR(30) = NULL ,@ContactTitle NVARCHAR(30) = NULL ,@Address NVARCHAR(60) = NULL ,@PostalCode NVARCHAR(10) = NULL ,@Phone NVARCHAR(24) = NULL ,@Fax NVARCHAR(24) = NULL ,@CityId INT = NULL AS BEGIN SET NOCOUNT ON UPDATE t SET t.CompanyName = @CompanyName ,t.ContactName = @ContactName ,t.ContactTitle = @ContactTitle ,t.Address = @Address ,t.PostalCode = @PostalCode ,t.Phone = @Phone ,t.Fax = @Fax ,t.CityId = @CityId FROM dbo.Customer AS t WHERE t.CustomerID = @CustomerID RETURN 0 END /* set nocount on set quoted_identifier, ansi_nulls, ansi_warnings, arithabort, concat_null_yields_null, ansi_padding on set numeric_roundabort off set transaction isolation level read uncommitted declare @CustomerID NChar(5), @CompanyName NVarChar(40), @ContactName NVarChar(30), @ContactTitle NVarChar(30), @Address NVarChar(60), @PostalCode NVarChar(10), @Phone NVarChar(24), @Fax NVarChar(24), @CityId Int select @CustomerID = 'CustomerID', @CompanyName = 'CompanyName', @ContactName = 'ContactName', @ContactTitle = 'ContactTitle', @Address = 'Address', @PostalCode = 'PostalCode', @Phone = 'Phone', @Fax = 'Fax', @CityId = null begin try begin tran exec dbo.Customer_Save @CustomerID = @CustomerID, @CompanyName = @CompanyName, @ContactName = @ContactName, @ContactTitle = @ContactTitle, @Address = @Address, @PostalCode = @PostalCode, @Phone = @Phone, @Fax = @Fax, @CityId = @CityId select t.* from dbo.Customer as t where t.CustomerID = @CustomerID if @@trancount > 0 rollback tran end try begin catch if @@trancount > 0 rollback tran declare @err nvarchar(2000) set @err = 'login: ' + suser_sname() + char(10) + 'ErrorNumber: ' + cast(isnull(error_number(), 0) as varchar) + char(10) + 'ErrorProcedure: ' + isnull(error_procedure(), '') + char(10) + 'ErrorLine: ' + cast(isnull(error_line(), 0) as varchar) + char(10) + 'ErrorMessage: ' + isnull(error_message(), '') + char(10) + 'Date: ' + cast(getdate() as varchar) + char(10) print @err raiserror(@err, 16, 1) end catch */ GO IF (object_id(N'dbo.Customer_Del') IS NULL) BEGIN EXEC ('create procedure dbo.Customer_Del as return 0') END GO -- ============================================= -- -- dbo.Customer_Del -- -- Delete method. -- -- Date: 07.09.2015, @HabraUser -- -- ============================================= ALTER PROCEDURE dbo.Customer_Del @CustomerID NCHAR(5) AS BEGIN SET NOCOUNT ON BEGIN TRY BEGIN TRAN /* uncomment if needed delete from dbo.CustomerCustomerDemo where CustomerID = ? delete from dbo.Orders where CustomerID = ? */ DELETE FROM dbo.Customer WHERE CustomerID = @CustomerID COMMIT TRAN END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRAN -- catch exception (add you code here) DECLARE @err NVARCHAR(2000) SET @err = ERROR_MESSAGE() RAISERROR (@err, 16, 1) END CATCH RETURN 0 END /* declare @CustomerID NChar(5) select @CustomerID = ? exec dbo.Customer_Del @CustomerID = @CustomerID */ GO IF (object_id(N'dbo.Customer_List') IS NULL) BEGIN EXEC ('create procedure dbo.Customer_List as return 0') END GO -- ============================================= -- -- dbo.Customer_List -- -- List method. -- -- Date: 07.09.2015, @HabraUser -- -- ============================================= ALTER PROCEDURE dbo.Customer_List AS BEGIN SET NOCOUNT ON SELECT CustomerID ,CompanyName ,ContactName ,ContactTitle ,Address ,PostalCode ,Phone ,Fax ,CityId FROM dbo.Customer /* uncomment if needed left join dbo.City as t1 on t1.CityId = t.CityId */ RETURN 0 END /* exec dbo.Customer_List */ GO

If you need to correct the body of the generated procedures, go to the settings.

Each procedure is a template T4. About T4 can be read here and here .

Refactoring "Introducing a trigger for accumulating historical data"

This operation allows you to enter a new trigger, designed to accumulate information about changes in the data in order to study the history of changes or audit.
The need to apply the operation “Introducing a trigger for the accumulation of historical data” is mainly due to the requirement to transfer the data tracking functions to the database itself. This approach ensures that if important data is modified in any external application, this change can be tracked and audited.
The only drawback, in my opinion, is the fact that the presence of a trigger will increase the execution time of a DML operation.
As an alternative to this method, you can consider Change Data Capture (it works asynchronously, thus it does not increase the operation time, but it has several features).

Let's apply this refactoring for the dbo.Customer table.
Select a table in OE, select the SQL Refactor Studio - Introduce Trigger for History item in the context menu. Select table fields to track changes.

Click Next . If necessary, we change the name of the table being created and the trigger.

Click Finish and get the script. The script creates a trigger and a table to store the change history.

Generated script

 CREATE TABLE [dbo].[CustomerHistory] ( [id] [bigint] IDENTITY NOT NULL ,[action_type] [char](1) NOT NULL ,[modified_date] [datetime] CONSTRAINT [DF_CustomerHistory_modified_date] DEFAULT getdate() ,[modified_login] [sysname] CONSTRAINT [DF_CustomerHistory_modified_login] DEFAULT suser_sname() ,[host_name] [nvarchar](128) CONSTRAINT [DF_CustomerHistory_host_name] DEFAULT host_name() ,[program_name] [nvarchar](128) CONSTRAINT [DF_CustomerHistory_program_name] DEFAULT program_name() ,[CompanyName_old] [nvarchar](40) ,[CompanyName_new] [nvarchar](40) ,[ContactName_old] [nvarchar](30) ,[ContactName_new] [nvarchar](30) ,[ContactTitle_old] [nvarchar](30) ,[ContactTitle_new] [nvarchar](30) ,[Address_old] [nvarchar](60) ,[Address_new] [nvarchar](60) ,[PostalCode_old] [nvarchar](10) ,[PostalCode_new] [nvarchar](10) ,[Phone_old] [nvarchar](24) ,[Phone_new] [nvarchar](24) ,[Fax_old] [nvarchar](24) ,[Fax_new] [nvarchar](24) ,[CityId_old] [int] ,[CityId_new] [int] ,[CustomerID_old] [nchar](5) ,[CustomerID_new] [nchar](5) ,CONSTRAINT [PK_CustomerHistory] PRIMARY KEY ([id]) ) GO CREATE TRIGGER [dbo].[trg_CustomerHistory] ON [dbo].[Customer] AFTER INSERT, DELETE, UPDATE AS SET NOCOUNT ON DECLARE @action_type CHAR(1) IF EXISTS (SELECT *FROM inserted) AND EXISTS (SELECT * FROM deleted) SET @action_type = 'U' ELSE IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) SET @action_type = 'I' ELSE IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) SET @action_type = 'D' INSERT INTO dbo.CustomerHistory ( action_type ,CompanyName_old ,CompanyName_new ,ContactName_old ,ContactName_new ,ContactTitle_old ,ContactTitle_new ,Address_old ,Address_new ,PostalCode_old ,PostalCode_new ,Phone_old ,Phone_new ,Fax_old ,Fax_new ,CityId_old ,CityId_new ,CustomerID_old ,CustomerID_new ) SELECT @action_type ,d.CompanyName ,i.CompanyName ,d.ContactName ,i.ContactName ,d.ContactTitle ,i.ContactTitle ,d.Address ,i.Address ,d.PostalCode ,i.PostalCode ,d.Phone ,i.Phone ,d.Fax ,i.Fax ,d.CityId ,i.CityId ,d.CustomerID ,i.CustomerID FROM inserted i FULL OUTER JOIN deleted d ON (i.CustomerID = d.CustomerID) GO

That's all for now. I hope the information will be useful to you and in your databases there will always be a complete order.
Good luck!

Useful resources

http://databaserefactoring.com
Scott Ambler, Pramodkumar J. Sadaladzh Refactoring databases. Evolutionary design
Martin Fowler Refactoring. Improving Existing Code
Plugin for SSMS SQL Refactor Studio

Source: https://habr.com/ru/post/258629/

All Articles

Refactoring database schemas

How to understand that it is time to refactor?

Some useful tips for applying refactoring

Practical examples

Source diagram

Testing

Refactoring "Rename Object"

Refactoring "Adding a lookup table"

Refactoring "Moving Fields"

Refactoring "Delete Object"

Refactoring “Adding CRUD Methods”

Refactoring "Introducing a trigger for accumulating historical data"

Useful resources

More articles: