Azure Data lake Gen 2 · Azure Data Platform · Azure Logic Apps · Azure Services · Data Engineering · Real Scenario Logic's · SharePoint

PART 2 – File Movement from SharePoint to Azure Data Lake Storage Gen 2

Introduction This a Part-2 of the series File Movement from SharePoint to Azure Data Lake Storage Gen 2 In this blogpost, we will see the how to do movement of files at folder level in automatic way from SharePoint to ADLS Gen 2 via Azure Logic Apps. The use case we are going to see here is, moving… Continue reading PART 2 – File Movement from SharePoint to Azure Data Lake Storage Gen 2

Azure Data lake Gen 2 · Azure Data Platform · Azure Databricks · Azure Services · Data Engineering · Python

Checking ADLS Gen 2 accesses in Azure Databricks via Service principal

Introduction In this blogpost, we will see how easily we can test the Service Principal accesses like below for files and folders at ADLS gen 2 in dynamic way (within some secs) via re-runnable python script in notebook of Azure Databricks. We can also utilize the below code to test the working of Key Vaults… Continue reading Checking ADLS Gen 2 accesses in Azure Databricks via Service principal

Data Engineering · Data Management · Pandas · Python

Pandas Basic Commands

Introduction In this blogpost, we will see some of the Python's pandas library basic commands and its operations. For running below commands, here, I have used Azure Databricks Notebook with python language. Via magic commands(%python), we can use the same below commands under other language connected notebooks as well. Basic pandas commands 1. Importing pandas library… Continue reading Pandas Basic Commands

Azure Data Platform · Azure Logic Apps · Azure Services · Data Engineering · Real Scenario Logic's · SharePoint

File Movement from SharePoint to Azure Data Lake Storage Gen 2

Introduction In this blogpost, we will see the how to do file movements in automatic way from SharePoint to ADLS Gen 2 via Azure Logic Apps. The use case, we are going to see here is when a person/someone uploads file in SharePoint(Source), we need to automatically move those files in the required Storage Account-ADLS Gen 2(Sink/Target)… Continue reading File Movement from SharePoint to Azure Data Lake Storage Gen 2

Azure Data Platform · Azure Services · Data Engineering · Performance tune

T-SQL Queries Vs Spark SQL Queries

Introduction In this blog post, we will see some of the Transact SQL (T-SQL) Queries and its equivalents in Spark SQL with examples. some T-SQL vs Spark SQL Keyword Differences T-SQL Queries The below input as well as output taken by running T-SQL Queries in SSMS via Azure Synapse Analytics/Dedicated SQL Pool, the same we… Continue reading T-SQL Queries Vs Spark SQL Queries

Azure Data Platform · Azure Services · Data Engineering · Did you Know! · Q & A · Real Scenario Logic's

Question and Answer #5

Question – Out of below 2 servers, We need to find which one is 'Microsoft Azure Synapse Analytics'? Other might be 'SQL Database'.1️⃣ "s1.database.windows.net"2️⃣ "s2.database.windows.net" How to find? 💡 For "Dedicated SQL pool (formerly SQL DW)" , the endpoint would be same as SQL Database ".database.windows.net" [Still pricing tier is different: cDWU vs DTU/vCore]Under "Azure Synapse Workspace"… Continue reading Question and Answer #5

Azure Data Factory · Azure Data Platform · Azure Services · Data Engineering · Data Management · Data Migrations

Lift and Shift SSIS Package in ADF

In this blogpost, we are going to see how the SSIS package runs in Azure Data Factory at high level with an example. Here, we will create Sample SSIS Package first and then create ADF instance with Azure-SSIS Integration Runtime and using Execute SSIS package activity within Pipeline. Prerequisite Active Azure Subscription. If you don’t have,… Continue reading Lift and Shift SSIS Package in ADF

Azure Data Factory · Azure Data Platform · Azure Services · Data Engineering

Bringing Folder Structure via Azure Data Factory

In this blogpost, we would see how to create Folder Structure in dynamic way as the process via Azure Data Factory with some sample files those are having different file formats. Here, We are going to make those file formats as dynamic folders and within those, we would also going to make them as year-->month-->date folder… Continue reading Bringing Folder Structure via Azure Data Factory

Azure Data Platform · Azure Services

Azure Batch Data Process – Covid Dataset

In this blogpost, we would see how to create Batch data process through Azure Data Engineering with covid dataset as sample. This post will give you all, the high level idea regarding Batch data process in Azure, if you are the beginner. Prerequisite Active Azure Subscription. If you don’t have, create a free account.Subscription Level Contributor access or Owner access. we can… Continue reading Azure Batch Data Process – Covid Dataset

Basic Logics · Did you Know! · Performance tune · Query Performance · Real Scenario Logic's

Unique Filtered Index in SQL Server

Introduction In this blog post, we will see what is Unique Filtered Index and how to create it and what are the challenges while creating it and what are the advantages of using it in SQL Server. Unique Filtered Index - What it is? As we all know, the UNIQUE Index will allow only Unique values.… Continue reading Unique Filtered Index in SQL Server

Azure Data Platform · Azure Services

T-SQL way to ADD and EDIT Client IP Address in Azure SQL DB

Introduction In this Article, we will see how to Add, Edit (and Delete) Client IP Addresses/Firewall in Microsoft Azure through Transact SQL(T-SQL) in SQL Server Management(SSMS) for Azure SQL DB. To know what is Azure Firewall and how to Add, Edit (and Delete) Client IP Addresses in Microsoft Azure Portal step by step, check -… Continue reading T-SQL way to ADD and EDIT Client IP Address in Azure SQL DB

Azure Data Platform · Azure Services

Create New Azure SQL Database

In this blogpost, we would see how to create New SQL Database in Azure. Prerequisite Active Azure Subscription. If you don't have, create a free account.Subscription Level Contributor access or Owner access. we can check our Access in Subscriptions-->Access Control (IAM)-->View my Access. (like below) Type Subscriptions in Global Search Resources Text Box Check our… Continue reading Create New Azure SQL Database

Azure Data Platform · Azure Services

Azure Resource Lock – Simple 2 Steps

Resource's Locked, Step Away! What is Resource Lock? The Resource Lock in Azure is Super powerful Resource Manager that provides Azure Admins a way to lock-down required Azure Resources and prevent them from accidental deletions and modifying of the resources. It is powerful than Role Based Access Control (RBAC), puts restrictions on the azure resources… Continue reading Azure Resource Lock – Simple 2 Steps

Basic Logics · Data Management · Real Scenario Logic's

Hyperlink a value in SQL Server

Introduction In this blog post, we will see how to hyperlink a value in SQL Server. Additionally, we see how to interchange the value as normal link-text and hyperlinks. Hyperlink a value SQL Script IF OBJECT_ID('Tempdb..#Temp') IS NOT NULL DROP TABLE #Temp; GO CREATE TABLE #Temp (NORMAL_LINK VARCHAR(200),HYPERLINK XML); GO INSERT INTO #Temp SELECT 'https://arulmouzhi.wordpress.com/','https://arulmouzhi.wordpress.com/';… Continue reading Hyperlink a value in SQL Server

Basic Logics · Did you Know! · Real Scenario Logic's · String Functions

Methods to Show Rupee Symbol in SQL Server

In this blog post, we will see some of the methods to Show Rupee Symbol in SQL Server by using On-Premises DB and Azure SQL DB in SSMS ( SQL Server Management Studio ) and Azure Data Studio. Methods to Show Rupee Symbol Using FORMAT ( ) FunctionUsing NCHAR ( ) Function SQL Script DECLARE… Continue reading Methods to Show Rupee Symbol in SQL Server

Basic Logics · Date Functions · Datetime functions · Real Scenario Logic's

Date in Our Language using SQL

Introduction In this blog post, we will see how we can use Dates in Some of our Local Languages! Date in Our Language We can achieve this by using FORMAT Function of SQL Server. Syntax FORMAT ( value, format , culture ) SQL Script DECLARE @Current_DateTime DATETIME; SELECT @Current_DateTime=SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time';… Continue reading Date in Our Language using SQL

Alternatives · Basic Logics · Did you Know! · Keyboard Shortcuts · Play in SQL · Real Scenario Logic's

Little Trick to Save Time in SSMS

Little Drops of Ink make Millions to Think! Introduction In this blog post, we are going to see a little trick that saves our valuable time in SSMS. Actually, if we need to change , include , remove or select a pattern in our SQL code or SQL scripts, most of us follow some of… Continue reading Little Trick to Save Time in SSMS

Basic Logics · Data Analysis · Play in SQL · Real Scenario Logic's

How to Know the Datatype and Properties of a Variable and Columns of a Table

Introduction In this blog post, we will see How to find the Datatype and Properties of a Variable and Columns of a Table. The Easy method for this Question is, using SQL_VARIANT_PROPERTY Function. SQL_VARIANT_PROPERTY The SQL_VARIANT_PROPERTY Function returns the base datatype and other basic information like Precision, Scale, Total bytes, Collation and Maximum Length. SYNTAX… Continue reading How to Know the Datatype and Properties of a Variable and Columns of a Table

Alternatives · Basic Logics · Real Scenario Logic's

Easy Like Search to find Table, View, Stored Procedure and Function Names in SQL Server

Introduction In this blog post, will see how can we easily find or search Table, View and Stored Procedure, Function names in SQL Server. SQL Script for Sample Table, View, SP, Function Creation -- Sample Table Creation CREATE TABLE TestTable ( TestTable_key INT IDENTITY(1,1) NOT NULL, TestTable_Name VARCHAR(100) NOT NULL ); GO -- Sample View… Continue reading Easy Like Search to find Table, View, Stored Procedure and Function Names in SQL Server

Basic Logics · Keyboard Shortcuts · Real Scenario Logic's

ON and OFF IntelliSense within Session in SSMS

Introduction In this blog post, we will see how to Switch ON or OFF IntelliSense within Session Level in SSMS (SQL Server Management Studio). Refresh, Enable and Disable IntelliSense Feature in SSMS (SQL Server Management Studio) works commonly for all sessions. But here, we will see how to Enable or Disable IntelliSense within Session level in… Continue reading ON and OFF IntelliSense within Session in SSMS

Alternatives · Basic Logics · Real Scenario Logic's

Alternate to Interesting ISNUMERIC Function Results in SQL Server

Introduction In this blog post, we are going to see what is ISNUMERIC Function in SQL server and what are the interesting result sets that it gives for some inputs and what are the other alternatives that we can use instead of ISNUMERIC Function. ISNUMERIC Function in SQL Server The ISNUMERIC function in SQL Server… Continue reading Alternate to Interesting ISNUMERIC Function Results in SQL Server

Alternatives · Play in SQL

Single Digit Special Characters used between SELECT and FROM in SQL Server

Introduction In this blog post, we are going to see what are the single digit special characters we can use between SELECT and FROM in SQL Server. Yup, many of us would guess and say '*' (Asterisk) . Yes, of course it's right and it will be our first answer. But What if my question… Continue reading Single Digit Special Characters used between SELECT and FROM in SQL Server

Basic Logics · Datetime functions · Real Scenario Logic's

To Fetch Formatted Time from Date time in SQL Server

Introduction In this blog post, we will see How to format the time part from Date time in SQL Server. Here, we have mentioned some of the common and easy methods to achieve this. To Fetch Formatted Time from Date time Method 1 - CONVERT SQL Script SELECT GETDATE() AS INPUT, CONVERT(VARCHAR(5),GETDATE(),108) AS EXPECTED_OUTPUT; Image… Continue reading To Fetch Formatted Time from Date time in SQL Server

Alternatives · Basic Logics · Data Management · Real Scenario Logic's

Truncate Extra Decimal Places to Exact Scale in SQL Server

Introduction In this blog post, we are going to see How we can Truncate Extra Decimal Places to Exact Scale in SQL Server. We can also say this as, Scale Only Round Off and Non-value Round Off. Method 1 - Using ROUND() SQL Script --Method 1 - Using ROUND() DECLARE @Test_Table AS TABLE (Test_Column DECIMAL(20,5));… Continue reading Truncate Extra Decimal Places to Exact Scale in SQL Server

Data Management · Data Migrations · Real Scenario Logic's

To Copy and Sync Database Tables Across Different Server

Introduction In this blog post, we will see How to make Copy, Sync between database tables across different Server with simple steps. We can follow the below steps and can copy or sync our Database Tables across different server and same server with different databases. Scenario we need to sync one Database table named 'States'… Continue reading To Copy and Sync Database Tables Across Different Server

Alternatives · Basic Logics · Data Management · Real Scenario Logic's

UPDATE Optional and Mandatory columns with values using T-SQL – Issue with Solution

Introduction In this Blog post, we are going to see What are the issues while updating both Optional and Mandatory columns and What is the solution and How we need to Handle/UPDATE both Optional and Mandatory Column values in SQL Server using T-SQL. Scenario Let us assume, we have the TABLE named Student with COLUMNS… Continue reading UPDATE Optional and Mandatory columns with values using T-SQL – Issue with Solution

Dynamic Management View · Performance tune · Query Performance · Real Scenario Logic's

Top 20 Poor Performing Queries in SQL Server

Introduction In this Blog post, we are going to see How to find the Top 20 Worst Performing Queries in SQL Server. We can use the below query as Query Performance Insight/Monitor that is common for both On-premises SQL Database and Azure SQL Database. SQL Script SELECT TOP 20 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,DBID) + '.' +… Continue reading Top 20 Poor Performing Queries in SQL Server

Alternatives · Basic Logics · Real Scenario Logic's

TRIM Functionality for INT data typed values in SQL – Issue with Solution

Introduction In this blog, we will see what is the issue when we use TRIM function in SQL for INT (Integer) data typed values and what is the solution for it. Issue - 'Argument data type int is invalid for argument 1 of Trim function.' Sample SQL Script DECLARE @A VARCHAR(10),@B INT; SET @A=' 54… Continue reading TRIM Functionality for INT data typed values in SQL – Issue with Solution

Alternatives · Basic Logics · Real Scenario Logic's

Listing Comma Separated Columns instead of ‘*’ in SQL Server

Introduction In any scenarios, if we need to explicitly list out all the column names of a table instead of using '*', we can use like below Script- Sample SQL Script IF(OBJECT_ID('TestEmp') IS NOT NULL) DROP TABLE [TestEmp]; CREATE TABLE dbo.[TestEmp] ( TestEmp_Key INT IDENTITY(1,1) NOT NULL, EmpName VARCHAR(100) NOT NULL, Age INT NULL, [Address] VARCHAR(100)… Continue reading Listing Comma Separated Columns instead of ‘*’ in SQL Server

Basic Logics · Real Scenario Logic's · String Manipulation

Counting Number of Occurrences of a Particular Word inside the String Using T-SQL

Introduction In this blog post, we will see how can we easily find the number of occurrences of a Particular Word inside the String Using T-SQL. Check our previous post for Finding Number of Occurrences of a Particular Character in the String. Finding Number of Occurrences of a Particular Word inside the String LOGIC The… Continue reading Counting Number of Occurrences of a Particular Word inside the String Using T-SQL

Basic Logics · Real Scenario Logic's · String Manipulation

Finding Number of Occurrences of a Particular Character in the String Using T-SQL

Introduction In this blog post, we are going to see how can we easily find the number of occurrences of a Particular Character in the String Using T-SQL. Finding Number of Occurrences of a Particular Character in the String LOGIC The Difference between the Length of String and Length of String without considering given particular… Continue reading Finding Number of Occurrences of a Particular Character in the String Using T-SQL

Alternatives · Basic Logics · Keyboard Shortcuts · Real Scenario Logic's

Check Database Size in SQL Server for both Azure and On-Premises

Introduction In this blog post, we will see how to check the database size in SQL Server irrespective of Azure and On-Premises using common T-SQL. Here, we will see two easy methods that is Common for both On-Premises and Azure SQL DB. To Check Current Database Size Method 1 - Using 'sys.database_files' System View We… Continue reading Check Database Size in SQL Server for both Azure and On-Premises

Alternatives · Basic Logics · Real Scenario Logic's

Common Alternatives to IF ELSE Logic in SQL Server with Simple Example in SQL Server-[PART 2]

Introduction- This is a Part 2 of one of my previous post named -'Common Alternatives to IF ELSE Logic in SQL Server with Simple Example'. In Part 1, we have already seen what are the common alternatives of IF ELSE logic in SQL Server and what are the Definition,Syntax and Demo SQL Scripts for IF ELSE,CASE… Continue reading Common Alternatives to IF ELSE Logic in SQL Server with Simple Example in SQL Server-[PART 2]

Basic Logics · Real Scenario Logic's

Show and Hide Line Numbers in SQL Server Management Studio (SSMS)

Introduction Many of us would face some challenges as a developer while debugging the SQL Codes. SQL Server would show us some error message with the particular Line Number where the error has occurred. The main aim of this post is to see, how to Show/Hide Line Numbers in SSMS. Show/Hide Line Numbers in SSMS… Continue reading Show and Hide Line Numbers in SQL Server Management Studio (SSMS)

Basic Logics · Date Functions · Real Scenario Logic's

To Find Current Indian Financial Year Start and End Date easily in both Azure SQL and On-Premises

Introduction The main objective of this post is let the users to know, how we can find Indian Financial Year Start and End Date easily in both Azure SQL and On-Premises with the common simple script. The below suggested script would work for both Azure SQL and On-Premises. Financial Year A Financial Year is actually… Continue reading To Find Current Indian Financial Year Start and End Date easily in both Azure SQL and On-Premises

Basic Logics · Real Scenario Logic's

To View List of ASCII Characters in SQL Server

Introduction The goal of this blog post is to know, how we can view the list of ASCII (American Standard Code for Information Interchange ) Characters in SQL Server. To View ASCII Character's List SQL Script SELECT NO,CHAR(NO) AS Characters FROM ( SELECT TOP 256 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 AS NO FROM SYS.COLUMNS… Continue reading To View List of ASCII Characters in SQL Server