God bless Dynamic SQL

The phrase: “Repetition is the mother of learning” is well known. It may sound banal, but in the second year of work, I was able to fully experience the meaning of this phrase.

On the one hand, when a person discovers something new for himself, the repetition of what has been traversed, within reasonable limits, allows him to better consolidate the material. However, in my situation, every day I had to solve functionally similar tasks. The natural result is a smooth decrease in the motivation to do it manually.

Find a way out of this situation, I was helped by dynamic SQL , which allowed to automate the most routine operations and increase productivity.
')
The following are a few life examples that are solved through the use of dynamic SQL .

1. Automatic index maintenance

What worked satisfactorily at the design stage, over time, can cause a significant drop in performance when working with a database.

There may be many reasons for this, therefore, in order to minimize the likelihood of the most obvious of them occurring, they form the so-called maintenance plan, which includes tasks for restructuring (defragmentation) of indices.

For one-time maintenance, you can rebuild indexes manually, for example, via the context menu item in SSMS - Rebuild Index .

Also, you can use one of the specialized tools - at one time, I rather actively used the free SQL Index Manager tool (it’s a pity that at the time of this writing, RedGate had already made it paid).

However, this fact should not sadden us, because the main functionality of this application is easy to implement through the use of dynamic SQL .

First of all, you need to get a list of fragmented indexes, while eliminating the tables without a cluster key (heap):

SELECT [object_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name , [object_type] = o.type_desc , index_name = i.name , index_type = i.type_desc , s.avg_fragmentation_in_percent , s.page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') s JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id JOIN sys.objects o ON o.[object_id] = s.[object_id] WHERE s.index_id > 0 AND avg_fragmentation_in_percent > 0 

After that, we will form a dynamic query, which, depending on the degree of fragmentation, will rebuild or reorganize the indices:

 DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = ( SELECT 'ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' + CASE WHEN s.avg_fragmentation_in_percent > 50 THEN 'REBUILD WITH (SORT_IN_TEMPDB = ON' + CASE WHEN SERVERPROPERTY('Edition') IN ('Enterprise Edition', 'Developer Edition') THEN ', ONLINE = ON' ELSE '' END + ')' ELSE 'REORGANIZE' END + '; RAISERROR(''Processing ' + i.name + '...'', 0, 1) WITH NOWAIT;' FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') s JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id JOIN sys.objects o ON o.[object_id] = s.[object_id] WHERE s.index_id > 0 AND page_count > 100 AND avg_fragmentation_in_percent > 10 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') EXEC sys.sp_executesql @SQL 

As practice has shown, defragmenting indexes with a low degree of fragmentation or with a small amount of data does not bring any noticeable improvements to improve performance when working with them - for this reason, the filtering condition in the final script has changed.

Index defragmentation is a very resource-intensive operation that can take a long time for tables containing large amounts of data.

In order not to block the work of users, it is best to perform defragmentation of indexes at night, when the base is the minimum load. But not everyone has the desire to work at night, so it is reasonable to take advantage of the possibilities of the SQL Agent .

A Job was added via SQL Agent , which executed the script daily.

2. Automatically add column to selected tables.

At the implementation stage, the customer asked to implement the ability to log changes on all existing tables. As a result, it was necessary to add 2 columns for more than 300 tables:

 CreatedDate DATETIME ModifiedDate DATETIME 

It is difficult to estimate the time when performing this task manually. Using dynamic SQL , the task was completed within half an hour.

As a result, we received a list of all tables that did not have the specified columns:

 SELECT SCHEMA_NAME(o.[schema_id]) + '.' + o.name FROM sys.objects o LEFT JOIN ( SELECT * FROM ( SELECT c.[object_id], c.name FROM sys.columns c WHERE c.name IN ('ModifiedDate', 'CreatedDate') ) c PIVOT (MAX(name) FOR name IN (ModifiedDate, CreatedDate)) p ) c ON o.[object_id] = c.[object_id] WHERE o.[type] = 'U' AND (ModifiedDate IS NULL OR CreatedDate IS NULL) 

A dynamic change request was created for these tables:

 DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = ( SELECT ' ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ADD ' + CASE WHEN ModifiedDate IS NULL THEN '[ModifiedDate] DATETIME' ELSE '' END + CASE WHEN CreatedDate IS NULL THEN CASE WHEN ModifiedDate IS NULL THEN ', ' ELSE '' END + '[CreatedDate] DATETIME' ELSE '' END + ';' FROM sys.objects o LEFT JOIN ( SELECT * FROM ( SELECT c.[object_id], c.name FROM sys.columns c WHERE c.name IN ('ModifiedDate', 'CreatedDate') ) c PIVOT (MAX(name) FOR name IN (ModifiedDate, CreatedDate)) p ) c ON o.[object_id] = c.[object_id] WHERE o.[type] = 'U' AND (ModifiedDate IS NULL OR CreatedDate IS NULL) FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') EXEC sys.sp_executesql @SQL 

3. Creating consolidated tables

Tasks for creating summary reports in enterprises are very common. There were a lot of problems. One of them - it took a lot of time to implement a specific report.

To partially optimize this process, it was decided to generate some reports dynamically.

In some scenarios, the consolidated tables were more efficient than the constant use of PIVOT queries.

Similar tables can be created through the table editor.

However, this option is not optimal, especially when it is possible to use dynamic SQL . All that is required from the user is to specify the number of columns that will be in the table, the column prefix, its type.

Next, execute the following query:

 IF OBJECT_ID ('dbo.temp', 'U') IS NOT NULL DROP TABLE dbo.temp GO DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = 'CREATE TABLE dbo.temp (EmployeeID INT IDENTITY(1,1) PRIMARY KEY' + ( SELECT ', Day' + RIGHT('0' + CAST(sv.number AS VARCHAR(2)), 2) + ' INT' FROM [master].dbo.spt_values sv WHERE sv.[type] = 'p' AND sv.number BETWEEN 1 AND 31 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') + ')' PRINT @SQL EXEC sys.sp_executesql @SQL 

Voila! After execution, we will get a table with the following structure:

 CREATE TABLE dbo.temp ( EmployeeID INT IDENTITY (1, 1) PRIMARY KEY , Day01 INT , Day02 INT , Day03 INT , Day04 INT , Day05 INT , ... , Day30 INT , Day31 INT ) 

It should be noted that the use of dynamic SQL is not limited to the examples described above. I hope this article will help you look at your daily tasks from the other side.