Calculating Running Total with OVER Clause and PARTITION BY Clause in SQL Server

Calculating Running Total with OVER Clause and PARTITION BY Clause in SQL Server
4.4 (88.42%) 19 votes

You often come across scenarios where you have to calculate a running total of a quantity.

A running total refers to the sum of values in all cells of a column that precedes the next cell in that particular column.

Let’s take a look at an example to make this clearer.

Read More

SQL Server Table Partitioning

SQL Server Table Partitioning
4.2 (84%) 5 votes

Problem

In this article, we will focus on the demonstration of table partitioning. The simplest explanation of table partitioning can be called as dividing large tables into small ones. This topic provides scalability and manageability.

Why do we need table partitioning?

Assume that we have a table and it grows day by day. In this case, the table can cause some problems which need to be solved by the steps defined below:

  • Maintain this table. It will take a long time and consume more resources (CPU, IO etc.).
  • Back up.
  • Lock problems.

Read More

Microsoft SQL Operations Studio: Understanding and Installation

Microsoft SQL Operations Studio: Understanding and Installation
5 (100%) 2 votes

SQL Server Management Studio is considered as the default integrated graphical user interface tool that has been used for many years to configure, manage, monitor and administrate the SQL Server instances hosted on the local machines, on remote servers or in the cloud by all SQL Server administrators and developers. It provides us with editing, debugging and deploying environment for many languages including T-SQL, XML, MDX and DMX languages. Due to the fact that Microsoft SQL Server can be installed now on the Linux platform, and that the SQL Server Management Studio tool is not compatible with any operating system outside Microsoft Windows, the need for a new cross-platform graphical user interface appears.

Read More

Example of Improving Query Performance with Indexes

Example of Improving Query Performance with Indexes
4.3 (86.67%) 3 votes

In this article, we’ll look at how an index can improve the query performance.

Introduction 

Indexes in Oracle and other databases are objects that store references to data in other tables. They are used to improve the query performance, most often the SELECT statement.

They aren’t a “silver bullet” – they don’t always solve performance problems with SELECT statements. However, they can certainly help.

Let’s consider this on a particular example.

Read More

50 Shades of Oracle Database Certification Exam

50 Shades of Oracle Database Certification Exam
5 (100%) 2 votes

In this article, I would like to talk about one of the basic certifications from Oracle – Oracle Database SQL Certified Expert. Unfortunately, this certification has become unavailable recently, but still, this article may be useful for preparing for other certifications and exams from Oracle. I wish a good read to everyone who wants to know which questions and tricks may await them and wants to be ahead of the game. Read More

Query Optimization in PostgreSQL. EXPLAIN Basics – Part 3

Query Optimization in PostgreSQL. EXPLAIN Basics – Part 3
4.5 (90%) 2 votes

I continue a series of articles on the basics of EXPLAIN in PostgreSQL, which is a short review of Understanding EXPLAIN by Guillaume Lelarge.
To better understand the issue, I highly recommend reviewing the original “Understanding EXPLAIN” by Guillaume Lelarge and read my first and second articles.

Read More

Query Optimization in PostgreSQL. EXPLAIN Basics – Part 2

Query Optimization in PostgreSQL. EXPLAIN Basics – Part 2
5 (100%) 2 votes

In my previous article, we started to describe the basics of the EXPLAIN command and analyzed what happens in PostgreSQL when executing a query.

I am going to continue writing about the basics of EXPLAIN in PostgreSQL. The information is a short review of Understanding EXPLAIN by Guillaume Lelarge. I highly recommend reading the original since some information is missed out.

Read More

Query Optimization in PostgreSQL. EXPLAIN Basics – Part 1

Query Optimization in PostgreSQL. EXPLAIN Basics – Part 1
4 (80%) 2 votes

 

Why does it take so much time to execute a query? Why are there no indexes? Chances are you’ve heard about EXPLAIN in PostgreSQL. However, there are still many people who have no idea how to use it. I hope this article will help users tackle with this great tool.

This article is the author revision of Understanding EXPLAIN by Guillaume Lelarge. Since I have missed out some information, I highly recommend you get acquainted with the original.

Read More