Pivot Tables in MySQL

Total: 101 Average: 4.8
  1. Briefly about Pivot tables
  2. Pivoting data by means of tools (dbForge Studio for MySQL)
  3. Pivoting data by means of SQL
  4. Automating data pivoting, creating query dynamically

Briefly about Pivot tables

This article deals with the transformation of table data from rows to columns. Such transformation is called pivoting tables. Often, the result of the pivot is a summary table in which statistical data are presented in the form suitable or required for a report.
Besides, such data transformation can be useful if a database is not normalized and the information is stored therein in a non-optimal form. So, when reorganizing the database and transferring data to new tables or generating a required data representation, data pivot can be helpful, i.e. moving values from rows to resulting columns.
Below is an example of the old table of products – ProductsOld and the new one — ProductsNew. It is through the transformation from rows to columns that such a result can be easily achieved.

Read More

SQL Server In-Memory OLTP: The Basics

Total: 0 Average: 0

This is the first article in a series of articles about In-Memory OLTP. It helps you to understand how the new Hekaton engine works internally. We will focus on details of in-memory optimized tables and indexes. This is the entry-level article, which means you do not need to be an SQL Server expert, however, you do need to have some basic knowledge about the traditional SQL Server engine. Read More

Database Checkpoints in SQL Server

Total: 0 Average: 0

In this article, we will talk about SQL Server Checkpoints.

To enhance performance, SQL Server applies modifications to database pages in memory. Often, this memory is called the buffer cache or buffer pool. SQL Server does not flush these pages to disk after every change. Instead, the database engine does checkpoint operation on each database from time to time. The  CHECKPOINT operation writes the dirty pages (current in-memory modified pages) and also writes details about the transaction log. Read More

Retrieving SQL Server Metadata with Help of T-SQL

Total: 3 Average: 4
This article contains a list of scripts that mine the SQL Server metadata in the various system functions, stored procedures, tables, and catalog views. Metadata queries are really helpful in discovering information for a given database schema. You can copy all the T-SQL scripts that are listed in this article and use for your own purposes. However, please do some tests before apply to a production database.

Read More