Articles Cookbook
21 Jun 2016
Jeffrey J. Keller

Window Functions in SQL Server: Part One: The OVER() Clause

Window functions were first introduced in standard SQL 2003 and then extended in SQL 2008. Since SQL Server 2005, Microsoft has been steadily improving window functions in the database engine. These functions perform an aggregate operation against a user-defined range of rows (the window) and return a detail-level value for each row. Well-known aggregate functions include SUM, AVG, MIN, MAX, and many others. Recent versions of SQL Server have introduced ranking and analytic functions such as LAG, LEAD, RANK, CUME_DIST, and many others.
Read more
19 May 2016
Maria Alcaraz

Pivot Tables in PostgreSQL Using the Crosstab Function

Some years ago, when PostgreSQL version 8.3 was released, a new extension called tablefunc was introduced. This extension provides a really interesting set of functions. One of them is the crosstab function, which is used for pivot table creation. That's what we'll cover in this article. The simplest way to explain how this function works is using an example with a pivot table. First, we will explain our initial point from a practical perspective, then we'll define the pivot table we want.
Read more
5 May 2015
Adrian Więch

5 Tips to Optimize Your SQL Queries

The SQL Language seems easy to learn – the commands follow a simple syntax and don't describe the specific algorithms used to retrieve data. The simplicity might, however, be deceptive. Not all the database functions operate with the same efficiency. Two very similar queries can vary significantly in terms of the computation time. This article presents some of the best practices that can greatly boost your SQL queries. 1. Learn How to Create Indexes Properly Learning how to index properly is the best thing you can do to improve the performance of your SQL queries.
Read more
14 Jan 2015
Patrycja Dybka

Oracle Collations: Binary and Linguistic Collations

Oracle bases its language support on the values of parameters that begin with NLS. These parameters specify, for example, how to display currency or how the name of a day is spelled. The table below presents some of the NLS parameters. By using one of them, NLS_SORT, we can specify the sort method (binary or linguistic) for both SQL WHERE clause operations and NLSSORT function operations. Option Name Description NLS_LANG The current language, territory, and database character set, which are determined by session-wide globalization parameters.
Read more
5 Jan 2015
Patrycja Dybka

PostgreSQL Collations

List Collations Collations in PostgreSQL are available depending on operating system support. For example, in Ubuntu type the following to list the names of the available collations: locale -a The same locales are available in PostgreSQL in the pg_collation catalog (mappings from an SQL name to operating system locale categories). select * from pg_collation; collname | collnamespace | collowner | collencoding | collcollate | collctype -----------+---------------+-----------+--------------+-------------+------------ default | 11 | 10 | -1 | | C | 11 | 10 | -1 | C | C POSIX | 11 | 10 | -1 | POSIX | POSIX C.
Read more
31 Dec 2014
Agnieszka Kozubek-Krycuń

MySQL Collations

List Collations To list all collations available in MySQL, use SHOW COLLATION; +-------------------+----------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-------------------+----------+-----+---------+----------+---------+ | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | | big5_bin | big5 | 84 | | Yes | 1 | | dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 | | dec8_bin | dec8 | 69 | | Yes | 1 | | cp850_general_ci | cp850 | 4 | Yes | Yes | 1 | | cp850_bin | cp850 | 80 | | Yes | 1 | | hp8_english_ci | hp8 | 6 | Yes | Yes | 1 | | hp8_bin | hp8 | 72 | | Yes | 1 | | koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 | | koi8r_bin | koi8r | 74 | | Yes | 1 | | latin1_german1_ci | latin1 | 5 | | Yes | 1 | | latin1_swedish_ci | latin1 | 8 | Yes | Yes | 1 | | latin1_danish_ci | latin1 | 15 | | Yes | 1 | | latin1_german2_ci | latin1 | 31 | | Yes | 2 | | latin1_bin | latin1 | 47 | | Yes | 1 | | latin1_general_ci | latin1 | 48 | | Yes | 1 | .
Read more
22 Dec 2014
Agnieszka Kozubek-Krycuń

How Does a Database Sort Strings?

Different languages have different alphabets and different ways to order letters within those alphabets. For example, a Polish character Ł comes right after L and before M. In Swedish, a letter Å comes almost at the end, right after Z. In French diacritics marks have no impact on the alphabetical order, so the letters À, Á and  are treated as the letter A when sorting strings. A collation is a set of rules that defines how to compare and sort character strings.
Read more
29 Oct 2014
Patrycja Dybka

Basic Date and Time Functions in MS SQL Server

As a follow up to our article “The Most Useful Date and Time Functions in Oracle Database”, let’s review what date and time functions look like in MS SQL Server. Let’s start with functions that extract a year, month and day from a given date. declare @dt date = '2014-10-20' select year (@dt) as year, month (@dt) as month, day (@dt) as day SQL Server uses GETDATE() and SYSDATETIME() to get a current date and time.
Read more
21 Oct 2014
Patrycja Dybka

The Most Useful Date and Time Functions

Date and time functions and formats are quite different in various databases. In this article, let's review the most common functions that manipulates dates in an Oracle database. The function SYSDATE() returns a 7 byte binary data element whose bytes represents: century, year, month, day, hour, minute, second It's important to know that select sysdate from dual in SQL*Plus gives the same result as select to_char(sysdate) from dual because SQL*Plus binds everything into character strings so it can print it.
Read more
3 Sep 2014
Patrycja Dybka

S.Q.L or Sequel: How to Pronounce SQL?

Have you ever thought about how to pronounce SQL? It's something that confuses a lot of people, even though SQL has been a big part of the tech industry for decades. Should you spell it out as “S-Q-L” or just say “sequel”? Let's dig into this debate and see if we can figure it out. SQL has been around for decades and supports a many billion dollar market. However, many people still struggle with just how to pronounce the term SQL.
Read more