Re-runnable Mounting and Unmounting between ADLS Gen 2 and Azure Databricks

Introduction In this blogpost, we will see the code of re-runnable mount and unmount point creations to access data files from Azure Data Lake Storage(ADLS) Gen 2 in Azure Databricks and for the scratch like things related to Resource Group, Key vault, ADLS Gen 2, Azure Databricks and App Registrations in Azure Active Directory and…

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…

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…

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…

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)…

Service Principal Creation for Accessing ADLS Gen 2 in Azure Databricks

Service Principal Creation in Azure to access ADLS Gen 2 in Azure Databricks

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…

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"…

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,…

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…

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…

PowerShell way to Create New Azure SQL Database

Programming is like any other sport. You might know the rules but you have to play to learn! - Zuckerberg In this blogpost, we would see how to create New SQL Database in Azure through PowerShell way. We can do this way, to get benefit of source control processes as well as simple infrastructure as…

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.…

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 -…

Azure CLI way to Create New Azure SQL Database

Where there is a dev, there is a code! Dev mostly loves coding way to do most of the things! In this blogpost, we would see how to create New SQL Database in Azure through Azure Command Line Interface(CLI) Way. We can do this way, to get benefit of source control processes as well as…

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…

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…

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/';…

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…

Google inside SSMS

Introduction While Others Searching about SSMS in Google. In this blog post, will see how can we bring Google inside SSMS and do Google Searches. SSMS inside Google SSMS inside Google Google inside SSMS Google inside SSMS How it is Possible? Yes, It is Possible! Check the below 2 Steps. STEP 1 Copy https://www.google.com/ and…

SQL Question and Answer #4

Question - What is Attribute, Tuple, Domain and Degree? Thinking that it will be useful to know some of the Basic Term definitions. Inspired from this Question. Attribute Basic, Rows are Records, Columns are Attributes of a Table. Tuple Tuple is a Single row of a Table. Domain Domain is the set of possible data…

CONCAT_NULL_YIELDS_NULL in SQL Server

Introduction In this blog post, we will see What is CONCAT_NULL_YIELDS_NULL and what's the syntax to use and what are default settings of it in both Azure SQL Db and On-Premises DB. Also we will see what are the important things to know about it and how to handle our codes irrespective of CONCAT_NULL_YIELDS_NULL setting…

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';…

SQL Question and Answer #3

Question Pic - Try yourself before checking Answer below! Question pic There can be 'n' number of answers for the above question. Among those, Below we have given one. Please comment your other methods of doing it. Because Sharing is Caring! From 'Thursday, March 05 2020' To '2020-03-05' SQL Script DECLARE @date VARCHAR(50) = 'Thursday,…

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…

Which Data Types has Non Nullable Default Values

I will be Happy to Convey that I have reached Half-Century in blog posts! Many more are yet to come...!

To Identify Available SQL Data Types

Introduction In this blog post, we are going to see what are the available datatypes in SQL and how to identify it. What are Available SQL Data Types Available SQL Datatypes To Identify Available SQL Data Types We can use sys.types ( System Catalog View) to identify both System and User-Defined Datatypes. SQL Script to…

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…

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…

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…

SQL Question and Answer #2

Everything we see is a perspective, not the truth. Question Pic Question SQL Script SELECT $42; SELECT $ 42; SELECT $ 42; SELECT $ 42; Q & A Will Line 1 Query Run without any error? If 'Yes', what's the Result? If 'No', Why it throws error? Keep Guess.... The Answer is Yes, Line 1…

Import CSV and TXT Files as Tables in SQL Server

Introduction In this blog post, we are going to look how can we import .csv and .txt files as tables in SQL Server and some of the possible errors to avoid in this process. Import .csv and .txt files as Tables in SQL Server .csv file ( Comma Delimited ) .csv file .txt file (…

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…

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…

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…

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));…

SQL Question and Answer #1

Question Question Here, the User wants us to add the column name through stored procedure and if the given column name already exists, then we have to show the alert message instead of adding it. Answer We are going to use CREATE TABLE and DROP TABLE, EXEC SP_HELP, CREATE OR ALTER PROC, System View -…

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'…

To know the Default Datatype of NULL and Empty in SQL Server

Introduction In this Blog post, we are going to see what is the default datatype of NULL and Empty and how to know it in SQL Server. How to know the Default Datatype To know the Default Datatype, one of the simple method we can use is, to add the alias name and move it…

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…

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) + '.' +…

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…

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)…

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…

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…

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…

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…

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…

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…

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…