How To

Tidy SQL with dbForge SQL Complete

In this article, we will talk about how to keep SQL code clean and highly readable. We will also discuss how to stop suffering from messy and unformatted code. In addition, we will talk about how to force all team members to adhere generally accepted coding standards with no efforts. (more…)

How to combine data from several sources using SQL and VirtualQuery

The VirtualQuery component allows executing SQL queries to sources that are not a database, but a TDataSet or any its descendant. It also allows you to connect to several data sources at a time and work with them using SQL queries as with a single data source. Thus, work in heterogeneous environment is implemented, when execution results of queries to different sources can be retrieved in a single data set. (more…)

Getting Real Currency Exchange Rates with Data Generator for SQL Server

This article will demonstrate how to get live currency exchange rates with the help of Python and dbForge Data Generator for SQL Server.

Currency exchange rate is the reference information used in the translation of monetary values from one currency to another. The exchange rate expresses the value of one currency in terms of another. (more…)

Migrating Entity Framework 6 projects to Entity Framework Core 1 (EF7)

For the purpose of this tutorial, we will use a test project from the “Entity Framework 6 Support for Oracle, MySQL, PostgreSQL, SQLite, DB2, and Salesforce” blog article. We will update this project so that it could be used with Entity Framework Core 1 (Entity Framework 7). (more…)

SQL Server Execution Plans

One of the best methods to analyze the query performance is to explore the query execution plan. A query plan or query execution plan is an ordered set of steps utilized to access data in a SQL relational database management system. In this article, we will discuss the basics of the Query Execution Plan feature available in SQL Server. (more…)

Generating Related Data Elements with dbForge Data Generator for SQL Server

Recently we got an interesting question from our user:

The docs don’t address directly, but how can we generate three related data elements, like date (more…)

MySQL Case Sensitive Search in Entity Framework

A high level abstraction provided by Entity Framework sometimes challenges developers in accessing a full set of features available via native SQL of a particular database server. This article will help you to cope with usage of collation in MySQL to control case sensitivity settings in generated queries. (more…)

Backup and Restore Databases in SQL Server

In this article, we will talk about the benefits of backing up SQL Server databases, different backup types, and backup strategies.

The immediate purpose of creating database backups is the ability to recover a damaged database and minimize the risk of catastrophic data loss. Thus, a reliable use of backup and restore for recovery requires a backup and restore strategy. (more…)

Enabling AUTO_CLOSE is a bad idea?

From a personal perspective, allowing a production database to run with AUTO_CLOSE option is not the best practice. Let me explain why you should not enable AUTO_CLOSE and the consequences of using this option.

The other day, I had to look in Error Log on a test server. After a two-minute timeout, I saw a great number of messages stored in the log, and I decided to check the log size using xp_enumerrorlogs:

EXEC sys.xp_enumerrorlogs

SELECT lod_id, last_log, size_mb = size / 1048576.
lod_id   last_log              size_mb
-------- --------------------- ---------------
0        2016-01-05 08:46:00   567.05288505
1        2015-12-31 12:53:00   1370.39249420
2        2015-12-18 11:32:00   768.46394729
3        2015-12-02 13:54:00   220.20050621
4        2015-12-02 13:16:00   24.04152870
5        2015-11-16 13:37:00   80.07946205
6        2015-10-22 12:13:00   109.33527946


What is faster inside SUM & AVG: 0 or NULL?

There are three aggregate functions that are most often used in practice: COUNT, SUM and AVG. The first one has already been discussed previously, while the other two have interesting performance nuances. But first, some theory…

When using aggregate functions in the execution plan, there may be two operators: Stream Aggregate and Hash Match, depending on the input stream.

The first may require pre-sorted set of input values, while Stream Aggregate does not block the execution of subsequent operators.

In turn, Hash Match is a blocking operator (with rare exceptions) and does not require sorting of the input stream. Hash Match uses a hash table that is created in memory, and in the case of incorrect assessment of the expected number of rows, the operator can spill the results into tempdb.