Azure Synapse

Azure Synapse INSERT with VALUES Limitations and Alternative

Azure Synapse Analytics data warehouse is based on TSQL. But, not all TSQL features are available in Azure Synapse dedicated SQL pool. The feature such as using an expression or UDF in VALUES clause of INSERT INTO table statement is not supported. In this article, we will check Azure Synapse INSERT with VALUES limitations and alternative methods. Azure Synapse INSERT with VALUES Limitations The VALUES clause Introduces the list or lists of data values to be inserted. There must be one data value for each column in column_list, if specified, or…

Continue ReadingAzure Synapse INSERT with VALUES Limitations and Alternative
Comments Off on Azure Synapse INSERT with VALUES Limitations and Alternative
March 3, 2021
Azure Synapse

Azure Synapse Analytics Cursor Alternative

SQL Cursor is a database object to retrieve data from a result set one row at a time. Database such as Oracle, Teradata, Microsoft SQL Server support cursors. The cursor are useful in many scenarios such as retrieving records in a loop. SQL Cursor always returns one row at a time, you can perform your calculation on returned values. In this article, we will check what is Azure Synapse Analytics Cursor Alternative with an example. Does Azure Synapse Analytics support SQL Cursor Variable? The Microsoft SQL Server, which is based…

Continue ReadingAzure Synapse Analytics Cursor Alternative
Comments Off on Azure Synapse Analytics Cursor Alternative
February 26, 2021
Azure Synapse

Azure Synapse DROP TABLE IF EXISTS Alternatives

Many relational databases such as Netezza supports DROP TABLE IF EXISTS syntax to safely drop table if it is already present in the database. The Microsoft SQL Server 2016 or higher version supports drop table if exists. However, same command won't work on Azure Synapse dedicated SQL pool server. In this article, we will check what are the Azure Synapse analytics DROP TABLE IF EXISTS alternatives with some examples. Azure Synapse DROP TABLE IF EXISTS Alternatives The DROP TABLE IF EXISTS statement checks the existence of the table in the schema, and…

Continue ReadingAzure Synapse DROP TABLE IF EXISTS Alternatives
Comments Off on Azure Synapse DROP TABLE IF EXISTS Alternatives
February 25, 2021
Azure Synapse

Create External Tables in Azure Synapse Analytics

There are many situations in which you need to access the data without loading it to Azure Synapse analytics. The data may be stored in an external data source such as flat files. Azure Synapse Analytics dedicated SQL pool allows you to query external data available on Azure the data lake or Azure Blob storage. In this article, we will check how to create external tables in Azure Synapse Analytics. Create External Tables in Azure Synapse Analytics Create MASTER KEYCreating a Database Scoped CredentialCreating External Data SourceCreate an External File…

Continue ReadingCreate External Tables in Azure Synapse Analytics
Comments Off on Create External Tables in Azure Synapse Analytics
February 24, 2021
Redshift

Redshift NULL Handling Functions-Usage and Examples

A NULL value in a relational database is a special marker used in SQL to indicate that a data value does not exist in the database. In other words, it is just a placeholder to denote values that are missing or that we do not know. Almost all relational databases support functions to handle nulls. In this article, we will check Redshift NULL handling functions, usage and some examples Redshift NULL Handling Functions A NULL functions are used to handle NULL values that you may receive as a part of the data. For example, you can use…

Continue ReadingRedshift NULL Handling Functions-Usage and Examples
Comments Off on Redshift NULL Handling Functions-Usage and Examples
February 23, 2021
Azure Synapse

SQL GROUP BY with CUBE Function Alternative in Synapse

Many relational databases such as Oracle, Teradata, etc. support GROUP BY with CUBE function to group the result rows. However, Azure Synapse does not support GROUP BY with CUBE function. In this article, we will check the SQL GROUP BY CUBE function alternative in the Azure Synapse data warehouse. SQL GROUP BY with CUBE Function Alternative in Synapse Similar to the ROLLUP function, CUBE is an extension of the GROUP BY clause. The CUBE allows you to generate subtotals like the ROLLUP extension. In addition, the CUBE extension will generate subtotals for all combinations of grouping columns specified in the GROUP BY clause.…

Continue ReadingSQL GROUP BY with CUBE Function Alternative in Synapse
Comments Off on SQL GROUP BY with CUBE Function Alternative in Synapse
February 22, 2021
Azure Synapse

QUALIFY Clause in Synapse and TSQL- Alternative and Examples

The Azure Synapse data warehouse is based on the Microsoft TSQL. If you are migrating from Teradata to Azure Synapse, you will notice that many features are missing in the Synapse date warehouse. The feature such as QUALIFY clause is widely used in Teradata. But, the same feature is not available in Synapse. In this article, we will check what is QUALIFY Clause alternative in Azure Synapse and TSQL. QUALIFY Clause in Azure Synapse and TSQL The relational databases such as Teradata uses QUALIFY clause to filter the result of…

Continue ReadingQUALIFY Clause in Synapse and TSQL- Alternative and Examples
Comments Off on QUALIFY Clause in Synapse and TSQL- Alternative and Examples
February 17, 2021
Redshift

Redshift Comparison Operators – ALL, SOME, ANY Alternative

The relational database such as Teradata support SQL comparison operators, including ALL, ANY and SOME. Some databases refer ALL, ANY and SOME as a SQL logical operator. A logical operators is an operator applied to the result of a predicate to determine the result of a search condition. Amazon Redshift does not support ALL, ANY and SOME logical operators. In this article, we will check what are ALL, SOME, ANY comparison operators alternatives in Redshift. SQL Logical Operators ALL, SOME and ANY Before jumping into details about alternative approach, let…

Continue ReadingRedshift Comparison Operators – ALL, SOME, ANY Alternative
Comments Off on Redshift Comparison Operators – ALL, SOME, ANY Alternative
February 17, 2021
Redshift

UnderStanding QUALIFY Clause in Redshift

If you’re familiar with Teradata, you might have used the QUALIFY clause to filter the results of ordered analytical functions. Amazon Redshift also started supporting QUALIFY clause. In this blog post, we’ll explore what the QUALIFY clause does, why it’s useful, and how you can achieve similar functionality in Redshift. Page Content Introduction What is the QUALIFY Clause? How QUALIFY Clause is used in Redshift? Alternatives to the QUALIFY Clause Conclusion Introduction The QUALIFY is one of the important clause as it helps filter results using window functions, similar to how the HAVING clause…

Continue ReadingUnderStanding QUALIFY Clause in Redshift
Comments Off on UnderStanding QUALIFY Clause in Redshift
February 11, 2021
Redshift

What are INSTR Alternative Functions in Redshift?

The INSTR function in relational databases such as Oracle, Teradata, etc. is used to search a string for a substring and find the location of the substring in the string. The Amazon Redshift does not support the INSTR function. However, there are few string functions that you can use. In this article, we will check what are INSTR alternative functions in Redshift. SQL INSTR String Function The SQL INSTR function returns the location of a substring in a string. Optionally, you can provide the starting position and occurrence of the…

Continue ReadingWhat are INSTR Alternative Functions in Redshift?
Comments Off on What are INSTR Alternative Functions in Redshift?
February 11, 2021