SQL SERVER – SQL Report Builder in dbForge Studio for SQL Server

Modern opportunities of electronic document management systems can significantly simplify the process of decision-making at an enterprise.

Along with the opportunities they provide, the traditional printed documentation still plays an important role by allowing to conveniently operate the required information by means of reports.

Creation of such reports, depending on the degree of complexity, can take a long time, therefore, to accelerate their creation, visual editors are traditionally used.

Some of these editors have only basic functionality. Others have extended reporting capabilities. The latter group of products includes the SQL report builder built-in to dbForge Studio for SQL Server.

The editor’s capabilities allow you to build a report according your SQL query, literally, within a few minutes. For this, it’s enough to call the Report Designer command from the main menu.

Select a report type in the opened dialog.

Select a connection and a data source.

Herewith, tables and views can be selected as a source.

In case if data, for example, from several tables should be returned by a query, a user query can be written.

After selecting a data source, specify the columns, by which the report will be built.

You should also specify how this data will be presented in the report.

At the final stage, it remains just to specify the name for the report and click the Finish button.

After the wizard is finished, the report editor opens, in which it is possible to fine-tune the appearance of the report.

To preview the report, there is the Preview button provided.

After the report is generated, it can be both printed and exported to one of the supported formats.

As you can see, creating and editing the report in SQL report builder does not take a lot of time, allowing to streamline the process and reduce development time.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

MySQL – Profiler : A Simple and Convenient Tool for Profiling SQL Queries

In terms of working with highly loaded databases, any badly-designed request can cause significant performance degradation. Depending on the used DBMS features, various methods are used for query optimization.

However, a general rule can be pointed out for all types of DBMS: the fewer server resources are expended when executing a query, the more effective will be the query itself.

To detect bottlenecks, MySQL provides the EXPLAIN command. It will help tell you what exactly the database server does, when you execute the query.

To obtain data on executing a query, it is enough to set EXPLAIN before each targeted query. However, putting EXPLAIN for a large number of constructs can become rather tedious.

To simplify search for a problem query let us use MySQL Profiler in dbForge Studio for MySQL. It provides such commands as a Query Profiling Mode and Generate Execution Plan.

To demonstrate work with execution plan, let’s consider a small sample. Suppose we need to find vendors of PCs, but not laptops, basing on the following data:

model_type VARCHAR(10) NOT NULL
CREATE TABLE products (
maker_id INT(11) NOT NULL,
model_id INT(11) NOT NULL,
PRIMARY KEY (maker_id, model_id),
CONSTRAINT FK_products_models_model_id
FOREIGN KEY (model_id)
REFERENCES models (model_id)
INSERT INTO models(model_id, model_type)
(1, 'PC'), (2, 'Laptop'), (3, 'Tablet'),
4, 'Phone'), (5, 'PC'), (6, 'Laptop');
INSERT INTO products(maker_id, model_id)
(1, 1), (1, 2), (1, 3), (2, 4),
4, 4), (2, 5), (3, 5), (3, 6);

This task can be solved quite trivial:

FROM products AS p
JOIN models m ON p.model_id = m.model_id
WHERE m.model_type = 'PC'
SELECT p2.maker_id
FROM products AS p2
JOIN models m2 ON p2.model_id = m2.model_id
WHERE m2.model_type = 'Laptop'
AND p2.maker_id = p.maker_id

When designing a query, one always  ought to pay attention to re-readings from the same tables, since they bear an additional load on the database server disk subsystem:

The query can be rewritten with no re-readings, using aggregating functions:

SELECT p.maker_id
FROM products AS p
JOIN models AS m ON p.model_id = m.model_id
WHERE m.model_type IN ('PC', 'Laptop')
GROUP BY p.maker_id
HAVING COUNT(CASE WHEN m.model_type = 'PC' THEN 1 END) > 0
AND COUNT(CASE WHEN m.model_type = 'Laptop' THEN 1 END) = 0;

However, having a large number of records, JOIN to the models table can still affect performance.

To speed up the query, denormalization of the scheme can be performed by duplicating data from the model_type column of the products table:

UPDATE products AS p
JOIN models AS m ON p.model_id = m.model_id
SET p.model_type = m.model_type;

Also, pay attention to the fact that the model_type column is filtered by, so it is recommended to index it.

At that,  selection of columns, that will be included to the primary key,  should be approached carefully. Since columns from the primary key are included to the nonclustered index, this can increase database size.

As a result, selection is significantly simplified:

SELECT maker_id
FROM products
GROUP BY maker_id
HAVING COUNT(CASE WHEN model_type = 'PC' THEN 1 END) > 0
AND COUNT(CASE WHEN model_type = 'Laptop' THEN 1 END) = 0;

At this, the plan becomes simple at all:

However, the root of performance problems should not always be searched for in queries – it is very important to correctly configure database server settings. For this, the Server Variables tab can be used.

In this case, all the settings, for convenience, can be grouped according to their target facilities:

There is no perfect advice how to optimize a query.  However, using the profiler embedded into dbForge Studio for MySQL, there is a probability to detect all the performance bottlenecks.

Optimal settings for MySQL server can also vary depending on the specificity of performed tasks. Therefore, detailed consideration of the server configuration process will be revealed in a separate article.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Writing SQL Queries Made Easy with dbForget SQL Complete

Down with shackles of convention! dbForge SQL Complete 4.5 came in this world.

What does SQL mean for each of us? For some – the only language for working with relational databases. Yet, for others – it is a way of life…

However, when working with SQL queries, both categories of people face the same difficulties.

Eventually, writing queries becomes routine, and all because of imperfection of the built-in Intellisense in SSMS.

The new dbForge SQL Complete 4.5 is called to release everyone from the shackles of convention in SSMS Intellisense and provide more opportunities when working with T-SQL code.

For this, SQL Complete has several key features in its arsenal:

1. Code auto-completion

Because of contextual object tooltips and syntactic elements of the T-SQL language, the productivity of work with SQL Complete is not just rising – it opens on to a qualitatively new level, providing greater opportunities.

Not just a trite hint of tables and columns is ensured. The ability to generate foreign key relationships, aliases, and many other things…

Object hints are provided in such a big number of structures, that an incomplete list of them can be learned only on a separate page.

2. SQL Formatter

Features of the SQL Formatter embedded in dbForge SQL Complete will allow to forget about manual query editing forever.

Using the SQL Formatter, your code will always be perfect due to a huge number of settings, that allow to customize formatting of all SQL constructs according to your personal preferences.

Moreover, there is a possibility to format not only the current document or selected code fragment, but groups of files on the disk as well.

3. Snippets

Code reuse through snippets is a perfect opportunity to accelerate the process of writing code.

As Devart developers said, their analysis of more than 5.000 topical posts from authoritative resources such as StackOverflow had shown that the list of basic snippets was significantly extended to prevent users from typing frequently used T-SQL statements.

Besides, owing to the Snippet Manager, users can easily extend the set of existing snippets.

When typing code, a required snippet can be selected by capital letters:

and by match with the snippet’s name:

4. Auto-refresh after update schema changes

On active schema changing, many encounter a situation when new or modified objects are not suggested in SSMS Intellisense. For this, you have to manually refresh the object list.

But all that is in the past, since in the version 4.5, there is a capability of smart refresh of hints for modified objects only at specified time intervals.

Within the dbForge SQL Complete 4.5 release, I would like to bring to your attention that memory consumption is reduced and objects describing is now much faster.

All these improvements are aimed to ensure that our users to be able to enjoy work and perform it without delays.

To support words with deeds, I recommend users to re-discover the wonderful world of SQL with dbForge SQL Complete 4.5.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Query Plan Analysis of Pivot and Unpivot with Profiler of dbForge Studio for SQL Server

Probably, everyone who writes SQL code has used INSERT statement, which alongside SELECT, UPDATE and DELETE, is a part of a basic operations’ set for data manipulation.

At the first sight syntax of the INSERT statement may seem very trivial, especially when inserting one record -  INSERT INTO VALUES … .

It’s not surprising, whereas in SQL Server 2005 basic syntax the VALUES keyword was applied only in the context of inserting records through using the INSERT statement. However, after the release of SQL Server 2008 the basic syntax of T-SQL was considerably expanded. Owing to this, usage of the multiline VALUES statement became possible not only for record insertion.

This article will focus on several possible cases of the VALUES statement usage. To present an unbiased assessment of the advantages of the VALUES statement, we will use built-in Profiler of dbForge Studio for SQL Server to look at the query plan for each particular example.

We have a table with the following structure, and we need to find the maximum amount of medals (disregarding their value) for each player:

IF OBJECT_ID ('dbo.Players') IS NOT NULL
DROP TABLE dbo.Players;
CREATE TABLE dbo.Players
PlayerID INT
, GoldBadge SMALLINT
, SilverBadge SMALLINT
, BronzeBadge SMALLINT
INSERT INTO dbo.Players (PlayerID, GoldBadge, SilverBadge, BronzeBadge)
(1, 5, 4, 0),
2, 0, 9, 1),
3, 2, 4, 10);

To accomplish the task we need to convert columns into rows, so that afterwards we are able to apply the MAX aggregation function.

At first, let’s look at the example that is very common among beginners:

Note, instead of reading data once, we are reading it three times from the table.

Starting from SQL Server 2005, the UNPIVOT operator is generally used to convert columns into rows:

Let’s look at the plan:

Now data from the table is read only once and that is what we’ve been trying to achieve. However, there is still one more bottleneck left in our query (the most resource-consuming operation) – it’s the Sort operation which allows a server to detect the element with maximal value.

Let’s try to avoid row sorting  using the VALUES statement.

As we can see, sorting is not applied here.

Let’s compare our queries and the results they return

Let’s examine the next sample, where we need to format row-wise data output in the grouped table.

We will get the following result:

We can solve this task by reading the data several times using UNION ALL with sorting:

We can also go back to the UNPIVOT statement, checking line numbers:

However, the VALUES statement allows creating a more sophisticated query:

Let’s take a look at the plan and compare the results:

The VALUES statement cannot be considered a full substitute to UNPIVOT statement as well as a savior in other cases. It all goes down to the query plan, and analyzing it with the help of Profiler, available in dbForge Studio for SQL Server, allows detecting potential bottlenecks in query productivity. Additionally, while we are on the subject of Database Diagram, it does makes sense to mention Database Diagram tool from dbForge. It allows us to visualize a database structure for further analysis as well help us to create and edit database objects on a diagram. You can get the Database Diagram Tool from here.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL Contest – USD 100 Gift Card and Attractive Discount from Devart

Quick Update: To take part in contest click here.

No matter how old we get we all want to Santa to give us gifts during the festival season. As the festival seasons has been approaching I was talking to a few of the Developer Tools vendors to see if they can come up with something cool for blog readers. If you are familiar with this blog, you must know about Devart. They are a fantastic team of people and make beautiful and easy to use tools.

Here are few of the blog posts, which I have written about them:

If you look back, I have used Devart tools for over 2 years now. I am a big fan of their DBForge Studio for SQL Server. It is like a one mega pack which contains multiple product which DBA and Developer will need for their daily needs. It is a complete set of database development tools for SQL Server united into a single IDE to help developers and administrators increase productivity and tackle routine database development tasks.

The main dbForge products for SQL Server are:

  • dbForge SQL Complete. It type T-SQL queries faster and format code automatically in Microsoft SQL Server Management Studio and Visual Studio to make your work more effective and productive with the help of this SQL formatter tool.
  • dbForge Schema Compare for SQL Server. It compare and synchronize schemas safely, analyze database structure differences, and propagate changes to the required SQL Server database with the help of this tool.
  • dbForge Data Compare for SQL Server. It compare and synchronize data safely and effectively, get a convenient way of managing data differences in a well-designed interface and ability to customize synchronization with the help of this tool.
  • dbForge Query Builder for SQL Server. It use this tool to create complex queries without having to type the entire code. Query Builder provides a wide range of options for visual query results management.
  • dbForge Fusion for SQL Server.  It is SQL GUI tools for Microsoft Visual Studio to advance all SQL database development and administration tasks in your favorite IDE.
  • And certainly – dbForge Studio for SQL Server –  which incorporates most of the functions of the tools mentioned above.

Christmas Offer

Team Devart has come up with fantastic Christmas offer which will celebrate Christmas everyday from today to January 15th, 2013. You can get anywhere between 20% to 35% discount on their product by taking part in the contest which they are running on G+. The contest is very simple and there will be one winner every day that means there is a great chance of winning for everybody everyday. Here is something I am going to do. Once you take part in Devart’s contest, come here and let me know in a comment that you have purchased the product and I will enter you in my contest where you can win USD 100 gift card from ThinkGeek. If you are familiar with ThinkGeek, you want those interesting gadgets but you do not want to spend your money on it. Well, now you have the opportunity to get them. I am going to give one of you USD 100 Gift Card for participating in the contest of DevArt. I will announce the winner on January 16th, 2013.

Click here to know about how to take part in the contest of the Devart Christmas Offer.

Reference: Pinal Dave (http://blog.sqlauthority.com)