SQL Server Functions

The below is a list of all of the articles on SQL Server Functions that have been written on this blog.

 

SQL Server – Parsing, Casting and Conversion

Sometimes the data stores in the table in a different format in comparison to the real data type it belongs to. The date might be stored as an integer value or a string value, numerical values might be stored as a character string, the currency might be stored in number format. The reason or explanation could

Continue reading

Convert Delimited Data Into Columns In SQL Server

This article addresses the conversion of the delimited data into columns in SQL Server. There are two functions called “STRING_SPLIT” and “PARSENAME” that helps us convert the delimited data into a single and multiple columns.  Consider the below data set for practice. Data1456, ConvertedData1, SomeOtherData11466, ConvertedData2, SomeOtherData21476, ConvertedData3, SomeOtherData4 Let’s create the table and load

Continue reading

SQL Server – CASE Function- Examples

This article talks about an equivalent of Oracle SQL’s DECODE function in SQL Server. There is no such function in SQL Server but through CASE function we can construct the same expression. (Update: IIF can be used as an alternative since SQL Server 2012 onwards) CASE function evaluates a list of conditions and returns one

Continue reading

SQL Server – Replicate Function

SQL Server “Replicate” Function repeats a character expression to a specified number of times. It is equivalent to or alternative of Oracle’s RPAD function. Let’s’ check how this will be useful to the developers. SELECT LEN(REPLICATE(2, ’12’)) The above query will return 12 as a result due to 2 is called 12 times (222222222222) hence

Continue reading

Aggregate Functions in Analytic Context

The functions SUM, AVG, COUNT, MIN, and MAX are well-known aggregate functions that we use every day. They are to compute/summarize the multiple rows by grouping them and provide a single summary value. However, when it comes to analysis, the aggregate functions carry out twofold responsibility: The same aggregate function which computes on each row

Continue reading

Apache Hive Aggregate Functions – Part-1

In both traditional relational systems and big data technologies such as Apache Hive and Apache Impala, most of the functions specified in this series are common. An aggregate function or aggregation function in database management is a function where the values of multiple rows are grouped together to form a single summary value. The dataset

Continue reading

Apache Hive Aggregate Functions – Part-2

This is continuation part of “Apache Hive Aggregate Functions” Function: VARIANCE & VAR_POP Returns the population variance of the total number of records present in the specified column. Syntax: variance(col), var_pop(col) Example: SELECT VARIANCE(amount) FROM tbSalesData; The result is 14618.639 As per the calculation, the variance supposed to be 15836.858 however in Apache Hive 1.1.0,

Continue reading

An alternative to ISNULL() and NVL() functions in Hive

The NVL() function enables you to substitute null for a more relevant alternative in the query results. This function accepts two arguments. If the first argument is null, then it returns the second argument. If the first argument is not null, it returns the first one and will ignore the second argument. This function is

Continue reading

TOP, LIMIT, ROWNUM vs DENSE_RANK

What would you do if you were asked to identify top-ten products based on their prices? In SQL Server, using a TOP clause with a specified number of records with descending order of price? In MySQL and Impala, using a LIMIT clause with a specified number of records with descending order of price? Basically, TOP

Continue reading

Best Usage of NTILE function in SQL Server and other RDBMSs

SQL Server NTILE() is a window function that distributes rows of an ordered partition right into a unique quantity of approximately same partition or group or buckets. The use of the function is described with a real time problem scenario.  Problem Scenario: An agency is doing unique sort of promotional events on 1st of each month. The administration wanted to peer, which promotional event has benefited the organization in each region.  USE dbTestGO CREATE TABLE

Continue reading

Hijri Date in SQL Server – with Islamic Month Name

This article focuses on converting the Gregorian date into an Islamic date along with Islamic month name in textual format. In SQL Server there are two methods to convert the Gregorian date to Islamic date, however, for Islamic month name we need to depend on our own code.   Either you can write a function

Continue reading

SQL Server 2016, 2017 and 2019 New Functions

There are several string and analytical functions introduced with the newer versions of SQL Server and are listed below. STRING_SPLIT is a table-valued function introduced in SQL Server 2016 (13.x) that splits a string into rows of sub-strings, based on a specified separator character. Discussed about its functionality and usage in my previous blog. Please

Continue reading

Approximate Count of Unique Values in SQL Server

In my previous blog, I discussed the approximate count of unique values for which a predefined function is available in Impala (NDV), Oracle 12c (APPROX_COUNT_DISTINCT) and in MongoDB (estimatedDocumentCount()).  However, I didn’t mention about SQL Server since there is no such function available in the versions I am using. However, the function has been introduced

Continue reading

DISTINCT vs APPROXIMATE DISTINCT

As all (or most) of us know, SELECT DISTINCT eliminates duplicate records from the results and returns only unique values. But it’s very expensive in terms of resources consumption as it requires to sort or hash all rows over all columns in the table. The use of DISTINCT might be pleasant until the column is

Continue reading

Calculating “Approximate Median” in Cloudera Impala, Apache Hive, SQL Server, Oracle and MySQL

APPROX_MEDIAN is an approximate inverse distribution function that accept a nonstop/continuous dispersion model. It takes a numeric or datetime value and returns an estimated middle value or an approximate interpolated value that would be the middle value once the values are sorted. Nulls are ignored in the calculation. In short, median is the middle value

Continue reading

Word Count in SQL Server – String_Split Usage

STRING_SPLIT is a table-valued function that splits the words from the input text and returns into rows. This function has been introduced in SQL Server 2016. String_split will help in many ways. 1) You can get the word count from the given text. 2) You can pass the string values to a query. Word Count

Continue reading

Computing Running & Cumulative Average

This article helps you in understanding how to calculate running average and cumulative average on a given data-set.   As discussed earlier, windowing functions are the same in SQL Server, Oracle & Hive and perform computations across a set of table rows that are related to the current row either by the department, area, category,

Continue reading

Json in SQL Server – Load and convert Json data

Data is the backbone of the business, and it can be in any sort of format. It can be lying in your relational databases or it can be a tweet in the twitter or a post in the Facebook or a message in the Instagram. Worldwide data is expected to hit 175 zettabytes by 2025

Continue reading

Handling “Json” and “Unstructured” Data in SQL

The below is to understand how we can handle JSON data in prior versions of SQL Server 2016. Sample JSON data is:   {“accountNumber”: 2020112, “pin”: 2525},  {“accountNumber”: 2567899, “pin”: 1462}  {“accountNumber”: 6789925, “pin”: 2614}  {“accountNumber”: 9925678, “pin”: 6142} This can be extracted into columns easily in SQL Server 2016 with the help of following

Continue reading

Analytical & Window Functions

Please refer to my previous post in which schema and data for EMP and DEPT tables available. In this article we are about to discuss about SQL Server Analytical and Window functions. As stated in Microsoft docs, analytic functions calculate an aggregate value based on a group of rows. Unlike aggregate functions, however, analytic functions

Continue reading

SQL Server – Date Time Functions

Here are a few examples of functions that can help you manipulate DATE in different manners. These will comprise most of your daily job routines. The below set of Date/Time functions are used consistently by SQL Users as they are most oftenly required to retrieve specific data from the database. ——————————————————————————— Please note that this

Continue reading

SQL Server – Monthly Report – Group by Month

The following example guide you to retrieve a monthly report. As discussed earlier, If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Here is an example to generate a monthly report by GROUPING DATE column. CREATE TABLE Test1( AccountID INT,

Continue reading

SQL Server – Daily Report – Group by Date

Like hourly report, the following example guide you to retrieve a daily report. As discussed earlier, If the date column is SMALLDATETIME or DATETIME, we can split it into year, month, day, hour, minute and seconds separately using CONVERT function. Here is an example to generate a daily report by GROUPING DATE column. CREATE TABLE

Continue reading

SQL Server – Hourly Report – Group by Hour

Often it is important to know how many inserts occur per hour in a transaction table OR how many payments are made per hour OR to retrieve the hourly report, whatever term it is, the necessity is to produce a report from a datetime column based on hours. If the date column is SMALLDATETIME or

Continue reading

SQL Server – CHARINDEX – Oracle INSTR

There is a ‘CharIndex’ function in SQL Server which is similar to the Oracle ‘Instr’ function. In Oracle, the syntax of the INSTR function is :instr( string1, string2 [, start_position [, nth_appearance ] ] ) ‘String1’ in the above syntax represents the string that you need to search in, whereas String2 is the substring that

Continue reading

SQL Server – GETDATE(), CURRENT_TIMESTAMP in DEFAULT statements

Most of the transactional tables generally do require to have a column with DEFAULT value as current date time to identify when the transaction had took place. To accomplish this, in CREATE TABLE statement you can use GETDATE() function or CURRENT_TIMESTAMP (ANSI SQL equivalent to GETDATE function) as DEFAULT. However there is no difference in

Continue reading

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s