Dynamic SQL Execution in SQL Server

Total: 1 Average: 5

Dynamic SQL is a statement constructed and executed at runtime, usually containing dynamically generated SQL string parts, input parameters, or both.

Various methods are available to construct and run dynamically generated SQL commands. The current article is going to explore them, define their positive and negative aspects, and demonstrate practical approaches to optimize queries in some frequent scenarios.

We use two ways to execute dynamic SQL: EXEC command and sp_executesql stored procedure.

CodingSight - Dynamic SQL Execution in SQL Server
Read More

Pitfalls of Linked Server Usage

Total: 6 Average: 3.7

An interesting project related to the task queue processing come to the company I work for. It was previously developed by another team. We needed to detect and resolve issues that occurred at high load on the queue.

In short, the project consisted of several databases and applications located on different servers. A ‘Task’ in the given project is a stored procedure or a .NET application. Correspondingly, the ‘task’ must be performed on a certain database and on a certain server.

All queue-related data is stored on the dedicated server. As for the servers at which tasks must be performed, they store only metadata. That is, procedures, functions, and service data related to this server. All task-related data comes from a Linked Server. Read More

Dynamic T-SQL and Benefits of its Usage

Total: 3 Average: 4.7

In our projects, we have to cope with different tasks. To solve some of them, we use dynamic T-SQL.

Why do we need dynamic T-SQL? Well, it is up to you.

In one of the projects, we have solved the task of building dynamic reports, and in others — data migration. Dynamic T-SQL is essential when you need to create, modify, get data or objects, but values or names come as parameters. For sure, it may seem unreasonable. Still, such tasks are possible. Later in the article, we will see several examples.

Read More