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

Basic Logics · Alternatives · 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 · Real Scenario Logic's · Datetime functions

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

Real Scenario Logic's · Data Management · Data Migrations

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

Alternatives · Basic Logics · Real Scenario Logic's

To Drop Multiple Tables in a Single Query in SQL Server

Introduction In this Blog post, we will see how to Drop Multiple Tables in a Single Query in SQL Server. The below Script would work in both Azure SQL DB and On-Premises. Demo SQL Script --Creating Table CREATE TABLE TEST_1 ( ID INT NOT NULL, NAMES VARCHAR(30) NOT NULL); GO --Populating the Data in Table… Continue reading To Drop Multiple Tables in a Single Query in SQL Server

Alternatives · Basic Logics · Real Scenario Logic's

To Drop Multiple Constraints in a Single Query in SQL Server

Introduction In this Article, we will see how to Drop Multiple Constraints in a Single Query in SQL Server. The below Scripts would work well in both Azure SQL db and On-Premises. DEMO SQL SCRIPT CREATE TABLE DEMO_TABLE ( ID INT NOT NULL, NAMES VARCHAR(30) NOT NULL ); GO --Populating the Data in Table. INSERT… Continue reading To Drop Multiple Constraints in a Single Query in SQL Server

Alternatives · Basic Logics · Real Scenario Logic's

To Add Multiple Columns with Default Constraint Name and with Default Values in SQL Server

Introduction In this Article, we will see how to add Multiple Columns with Default Constraint name and with Default Values in SQL Server. The Below Scripts would help us to run in both Azure SQL db and On-Premises. Demo SQL Script --Creating the New Table CREATE TABLE DEMO_TABLE ( ID INT NOT NULL, NAMES VARCHAR(30)… Continue reading To Add Multiple Columns with Default Constraint Name and with Default Values in SQL Server

Alternatives · Basic Logics · Data Analysis · Data Management · Real Scenario Logic's · String Manipulation

SuperTrim Function in SQL Server

After Reading my Article - To Remove Multiple and Duplicate Spaces to Single Space in SQL Server, I received the email where one of my friend is asking for the below requirements- To Remove Invisible TAB, Carriage Return, Line Feed characters, White Spaces, Non-Breaking Spaces etc., Leading and Trailing spaces need to remove.Further, contiguous occurrences… Continue reading SuperTrim Function in SQL Server

Basic Logics · Real Scenario Logic's

Clone Temporary Table Structure to New Physical Table in SQL Server

Introduction In this Article, we will see how to Clone Temporary Table Structure to New Physical Table in SQL Server.This is applicable for both Azure SQL db and on-premises. Demo SQL Script IF OBJECT_ID('TempDB..#TempTable') IS NOT NULL DROP TABLE #TempTable; SELECT 1 AS ID,'Arul' AS Names INTO #TempTable; SELECT * FROM #TempTable; METHOD 1 -… Continue reading Clone Temporary Table Structure to New Physical Table in SQL Server

Azure Services · Real Scenario Logic's

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 in Microsoft Azure Portal step by step for Azure SQL DB. What is Azure Firewall? Azure Firewall Services are managed cloud-based network security services that protects our Azure Virtual Network resources.It's a supports high built-in availability and gives unrestricted… Continue reading ADD and EDIT Client IP Address in Azure SQL DB

Basic Logics · Real Scenario Logic's

Joining Non-Related Tables

Introduction In this Article, We are going to see how to join or relate or make connection between Non-related Tables In SQL Server. Demo Table – SQL Script IF OBJECT_ID('Tempdb..#T1') IS NOT NULL DROP TABLE #T1; CREATE TABLE #T1 (T1_Name VARCHAR(75)); INSERT INTO #T1 (T1_Name) VALUES ('Animal'),('Bat'),('Cat'),('Duet'); SELECT * FROM #T1; IF OBJECT_ID('Tempdb..#T2') IS NOT… Continue reading Joining Non-Related Tables

Basic Logics · Real Scenario Logic's

Implementing Serial Numbers Without Ordering Any of the Columns

Introduction In this Article, We are going to see How to Implement Serial Numbers Without Ordering Any of the Columns in SQL Server. Demo Table – SQL Script IF OBJECT_ID('Tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable; CREATE TABLE #TestTable (Names VARCHAR(75), Random_No INT); INSERT INTO #TestTable (Names,Random_No) VALUES ('Animal', 363) ,('Bat', 847) ,('Cat', 655) ,('Duet',… Continue reading Implementing Serial Numbers Without Ordering Any of the Columns

Basic Logics · Data Analysis · Real Scenario Logic's

Simple Query to find Nth Highest and Lowest Salary from the list of Non Unique Salary Records in SQL Server

Introduction In this Article, we will see how to find the Nth Highest and Lowest Salary from the list of Non Unique Salary records by using Simple SQL Query in SQL Server. Demo Table - SQL Script IF OBJECT_ID('Tempdb..#Employee') IS NOT NULL DROP TABLE #Employee; CREATE TABLE #Employee (EmployeeName VARCHAR(75), Salary MONEY); INSERT INTO #Employee… Continue reading Simple Query to find Nth Highest and Lowest Salary from the list of Non Unique Salary Records in SQL Server

Basic Logics · Data Analysis · Data Management · Data Migrations · Real Scenario Logic's

To Find Fill Rate in a Table on both AZURE and On-Premise – Key Metric in Data Analysis

Introduction Knowing Fill Rate is one of the essential tasks in Data Analysis/Management and Migration Processes. It helps us to take Business Decisions effectively. In this post, will see how to find the Fill rate in a table using SQL Queries that is Applicable for both AZURE and On-Premises... What is meant by Fill Rate?… Continue reading To Find Fill Rate in a Table on both AZURE and On-Premise – Key Metric in Data Analysis

Alternatives · Basic Logics · Real Scenario Logic's · String Functions · String Manipulation

To Remove Multiple and Duplicate Spaces to Single Space in SQL Server

In this blog post, we would see some of the easy methods to remove Multiple as well as Duplicate Spaces to Single Space using T-SQL in SQL Server like below. METHOD 1 - WHILE LOOP AND REPLACE By Using WHILE Loop, we can check the occurrences of double spaces, as Loop Condition.Till the occurrence (Loop)… Continue reading To Remove Multiple and Duplicate Spaces to Single Space in SQL Server

Alternatives · Basic Logics

Common Alternatives to IF ELSE Logic in SQL Server with Simple Example

Below are the Common Alternatives of IF ELSE Logic in SQL Server that was given with simple example. IF ELSE LOGIC SYNTAX IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ] --ELSE Block is OPTIONAL SQL QUERY WITH SIMPLE EXAMPLE --IF ELSE LOGIC IF 1>0 SELECT 'True' AS RESULT;… Continue reading Common Alternatives to IF ELSE Logic in SQL Server with Simple Example

Basic Logics · Keyboard Shortcuts · Real Scenario Logic's

Refresh, Enable, Disable IntelliSense Feature in SSMS (SQL Server Management Studio)

Introduction Microsoft IntelliSense is one of the feature that is available in the text editor of SSMS (SQL Server Management Studio).It would reduce our Typings and provide us quick access to syntax info. In this blogpost, we are going to know how to Refresh IntelliSense when it shows 'Error with red line' , and Enable… Continue reading Refresh, Enable, Disable IntelliSense Feature in SSMS (SQL Server Management Studio)

Basic Logics · Real Scenario Logic's · Uncategorized

To Remove IDENTITY property from a column in a table by handling Foreign key relationships in both AZURE and On-premises

If we want to remove the IDENTITY property from a column in a Table, but at the same time if we want to keep the table data as it is. Please follow the below steps with examples(images also attached for ref). Sample Table Creation and Data Population Script --PARENT TABLE CREATE TABLE dbo.[TestEmp] ( TestEmp_Key… Continue reading To Remove IDENTITY property from a column in a table by handling Foreign key relationships in both AZURE and On-premises

Real Scenario Logic's

TRIGGERS+DYNAMIC QUERY to avoid HUGE Existing code changes

Recently came across a below question. Requirement is to trim leading and trailing spaces for a particular column name when ever there is insert or update happens to the tables having that particular column name.The User would like to do this in real time when ever the data is inserted or updated to the table.… Continue reading TRIGGERS+DYNAMIC QUERY to avoid HUGE Existing code changes

Date Functions

AZURE Current DateTime

To know the Additional Details of AZURE's Current DateTime like Year, Month, Day, Hour, minute, Second, MilliSecond, NanoSecond, AM/PM, Month Name, WeekDay Name, Week, Day of Year and Quarter, Refer the below Simple Query- DECLARE @Current_DateTime DATETIME= SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time'; --To know the details of our TimeZone /* select [name],current_utc_offset from… Continue reading AZURE Current DateTime

String Functions

REPLACE Vs. STUFF

REPLACE To Replace all occurrences of the given pattern in a string. It Executes till it replaces all occurrences of the given pattern in a string.Syntax - REPLACE ( String_Expression , String_Pattern , String_Replacement ) Example - DECLARE @String_Expression VARCHAR(50)='Input:-)String:-)ForExample'; DECLARE @String_Pattern VARCHAR(10)=':-)'; DECLARE @String_Replacement VARCHAR(1)=''; SELECT @String_Expression AS INPUT_STRING, REPLACE ( @String_Expression, @String_Pattern, @String_Replacement… Continue reading REPLACE Vs. STUFF