NDepend is a static code analyzing tool which will help us to improve the source code quality by reducing the issues in our code. Patrick Smacchia introduced NDepend in 2007. This tool will likely find hundreds or even thousands of issues affecting our code base. Stopping all work to try and fix all issues for weeks would be quite unproductive. This is the only tool that proposes to focus on progress from a baseline. The tool estimates the technical debt evolution since the baseline.

Recent code smells that should be fixed before committing any code to source control are highlighted in the code just edited. NDepend is the only .NET tool that can tell the developer that over the past hour, the code just written has introduced debt that would cost for example about 30 minutes should it have to be repaid later. Knowing this, the developer can fix the code before even committing it to the source control.

In this post, we can see all the steps to download and install NDepend in our Windows machine and analyze our source code.  

Please refer to NDepend official site for more information.  

This tool can be useful for developers, architects, and DevOps people. This is integrated with all Visual Studio versions from 2010 to 2022. All features are available side-by-side with Visual Studio Code and Rider thanks to the standalone app VisualNDepend.exe

We can use this tool to analyze .NET 6.0, .NET 5.0, (and soon .NET 7), .NET Core and other versions, .NET Fx 4.x, ASP.NET Core, Blazor, Xamarin, Unity and UWP applications.
Download and Install NDepend on Windows machine.

NDepend is giving a 14-day fully functional trial version. We can use this trial version to explore all the features and once we are satisfied, we can buy the professional version.

We can extract the Zip file in any folder except '%ProgramFiles%\NDepend'. This might provoke problems because of Windows protection.

The Zip file includes four main executable files. 
NDepend.Console.exe is a console application which is used for registering and unregistering the license of NDepend.  It can also be used from your CI/CD to generate HTML+js reports about the health of your code base (see some sample reports here)

NDepend.PowerTools.exe is another console application which will help us to perform various operations via command line. Notice that power-tools are Open-Source and relies on the NDepend.API.

Currently, this tool has 18 various usages. We can choose any of these choices and hit enter key to continue.

I have chosen ‘r’.  This power tool analyzed the .NET 5.0 and 6.0 public APIs.  

NDepend.VisualStudioExtension.Installer.exe is an installer file. This file will install the NDepend extension in our Visual Studio version.

I am using Visual Studio 2022 on my machine. We can choose our respective versions.

You must agree to the license terms. It may take some time to complete the entire extension installation.
After successful installation, we can open Visual Studio. 

We can see the new extension under the Extensions menu. We can choose the New Project option from the menu.

We can give a valid Project Name. We are going to analyze one .NET 6.0 project. I have already written one project for JWT Authentication. We can use this project to analyze with NDepend.

We can click Add VS Solution or Project button and add our existing .NET 6.0 project. 

We can choose our existing project by clicking on the Browse button. We can also select multiple projects by clicking on the Check All button. It will select all the compatible projects that exist in our system. 

We can click the play button on the left top corner to start the analysis.  
After analysis (it just takes a few seconds), we can see new menus in the NDepend toolbar. 

We can click Dashboard option. 

We can see the elaborated analysis in the dashboard page. Total 355 lines in our project. Among the 355 lines, 24 lines are comments. We got an exceptionally good A rating. Still there are 5 rules violation and 15 issues in the code. 

We can click on 5 violated rules and see the details below.

Same way, we can click on the single issue with priority High and see more details of the issue.
Dependency Graph is another option in the tool. We can choose this option from the menu. See a 6 minutes intro video about the graph on youtube here.

Dependency Matrix is another good option in this tool. See a 5 minutes intro video about the matrix on youtube here.

Code Metrics View is also one of the good features in this tool. See a 4 minutes intro video about this metric view on youtube here.

VisualNDepend.exe is a standalone tool which can be used to analyze the .NET projects without Visual Studio.

We can create a new project or open an existing NDepend project and analyze using this Visual tool.

All the features like Dashboard, Dependency Graph, Dependency Matrix, and Code Matrics View are available in this tool as well.

Once we have bought the professional license, we can update the license using  NDepend Console tool.  

Conclusion
In this post, we have seen the introduction of NDepend tool which is a static code analysis tool that can be used for improving the code quality of our .NET or .NET Core source code. We have installed the NDepend extension in Visual Studio 2022 and analyzed one existing .NET 6.0 project using this tool. We have seen various features like Dashboard, Dependency Graph, Dependency Matrix, and Code Matrics View in this tool. I have just given an introduction about NDepend tool in this post. We can see more information about this tool in an upcoming post later. Please feel free to give your valuable feedback about this article.

HostForLIFEASP.NET SQL Server 2019 Hosting

We get a procedure list with the help of DMV like " Sys.objects & sys.parameters"

USE [SqlBank]

SELECT s.NAME StoreProcedure, p.NAME Parameter FROM SYS.PARAMETERS p
JOIN SYS.OBJECTS s on p.object_id=s.object_id
WHERE TYPE='P'
ORDER BY p.NAME

From the above query, we get Procedure name with parameters

Output

Thank you for reading the blog.

HostForLIFEASP.NET SQL Server 2019 Hosting

To compare SQL DB objects we use Dynamic views Like  "SYS.SYSOBJECTS, "SYS.SYSCOMMENTS", "INFORMATION_SCHEMA.COLUMNS"

STEP 1
Declare local varible
DECLARE @SourceDbName SYSNAME = 'SqlBank'; -- Source Database Name
DECLARE @TargetDbName SYSNAME = 'SqlBankCore';-- Target Databse Name

DECLARE @SYSobjectCommonQuery NVARCHAR(4000);
DECLARE @SYSCommentsQuery NVARCHAR(4000);

DECLARE @SourceSYSObjectsExecute NVARCHAR(4000);
DECLARE @SourceSYSCommentExecute NVARCHAR(4000);

DECLARE @TargetSYSObjectsExecute NVARCHAR(4000);
DECLARE @TargetSYSCommentExecute NVARCHAR(4000);

DECLARE @InformationCommonQuery NVARCHAR(4000);
DECLARE @ExecuteSourceInformationColumn NVARCHAR(4000);

STEP 2
Declare Variable table for Objects xtypes
DECLARE @Tbl_Xtypes TABLE(xtype CHAR(2),xtypeDefination VARCHAR(1000))

INSERT INTO @Tbl_Xtypes Values
('AF','Aggregate function (CLR)'),('C ','CHECK Constraint'),
('D','Default or DEFAULT Constraint'),('F','FOREIGN KEY Constraint'),
('FN','Scalar Function'),('FS','Assembly (CLR) Scalar-Function'),
('FT','Assembly (CLR) Table-Valued Function'),('IF','In-lined Table Function'),
('IT','Internal Table'),('L','Log'),('P','Stored Procedure'),
('PC','Assembly (CLR) Stored Procedure'),
('PK','PRIMARY KEY Constraint (Type is K)'),
('RF','Replication Filter Stored Procedure'),('S','System Table'),
('SN','Synonym'),('SQ','Service Queue'),
('TA','Assembly (CLR) DML Trigger'),('TF','Table Function'),
('TR','SQL DML Trigger'),('TT','Table Type'),
('U','User Table'),('UQ','UNIQUE Constraint'),
('V','View') ,('X','Extended Stored Procedure')

STEP 3
Assign Common Query for local object
SET @SYSobjectCommonQuery=N'
SELECT NAME,ID,XTYPE,UID,INFO,STATUS,BASE_SCHEMA_VER,REPLINFO,PARENT_OBJ
,CRDATE,FTCATID,SCHEMA_VER,STATS_SCHEMA_VER,TYPE,USERSTAT,SYSSTAT,INDEXDEL,
REFDATE,VERSION,DELTRIG,INSTRIG,UPDTRIG,SELTRIG,CATEGORY,CACHE
FROM SYS.SYSOBJECTS WHERE XTYPE NOT IN (''S'') ORDER BY NAME ASC';

SET @SYSCommentsQuery =N'SELECT id,number,colid,status,ctext,texttype,
language,encrypted,compressed,text FROM sys.syscomments';

STEP 4
SET Source & Target Database Common query to get DB objects

SET @SourceSYSObjectsExecute=N'USE ['+@SourceDbName+'] '+@SYSobjectCommonQuery+'';
SET @SourceSYSCommentExecute=N'USE ['+@SourceDbName+'] '+@SYSCommentsQuery+'';
SET @TargetSYSObjectsExecute=N'USE ['+@TargetDbName+'] '+@SYSobjectCommonQuery+'';
SET @TargetSYSCommentExecute=N'USE ['+@TargetDbName+'] '+@SYSCommentsQuery+'';

STEP 5
Create Temp Table to insert SYS.SYSObjects Objects to Temp Table

IF(OBJECT_ID('tempdb..#Tbl_SourceObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceObjects
END

CREATE TABLE #Tbl_SourceObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_SourceObjects
EXEC sp_executesql @SourceSYSObjectsExecute

STEP 6
Create Temp Table to insert SYS.SYSComments Objects to Temp Table

IF(OBJECT_ID('tempdb..#Tbl_SourceComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceComments
END

CREATE TABLE #Tbl_SourceComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

INSERT INTO #Tbl_SourceComments
EXEC sp_executesql @SourceSYSCommentExecute

STEP 7
IF(OBJECT_ID('tempdb..#Tbl_TargetObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetObjects
END

CREATE TABLE #Tbl_TargetObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_TargetObjects
EXEC sp_executesql @TargetSYSObjectsExecute

IF(OBJECT_ID('tempdb..#Tbl_TargetComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetComments
END

CREATE TABLE #Tbl_TargetComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

--print @SourceSYSCommentExecute
INSERT INTO #Tbl_TargetComments
EXEC sp_executesql @TargetSYSCommentExecute

--======================================================================

Select @SourceDbName [Source DataBase Name], Main.name [Source Object Name],
@TargetDbName[Target DataBase Name],Main.[Object Defination],SubMain.name [Target Object Name]
,Main.text[Source Object text],SubMain.text [Target Object text],
CASE WHEN Main.text=SubMain.text Then
 Concat(Main.[Object Defination], '  Object Match with Source Databse')
 else
 Concat(Main.[Object Defination], ' Object Mismatch Or Not Found with Source Database')
 end [Global Message]
from
(
SELECT o.name, cs.id,cs.number,cs.colid,cs.status,cs.ctext,cs.texttype,
cs.language,cs.encrypted,cs.compressed,ISNULL(cs.text,'') text,
CASE WHEN o.xtype in (SELECT x.xtype from @Tbl_Xtypes x)
THEN (Select xx.xtypeDefination from @Tbl_Xtypes xx where xx.xtype=o.xtype)
else '' end [Object Defination]
FROM #Tbl_SourceComments cs join
#Tbl_SourceObjects o on o.id=cs.id
) Main
left join
(
select css.id,oo.name,ISNULL(css.text,'') text,oo.xtype FROM
#Tbl_TargetComments css  left join
#Tbl_TargetObjects oo on css.id=oo.id

)SubMain  on Main.name=SubMain.name

SET @InformationCommonQuery = N'
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION
,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX
,NUMERIC_SCALE,DATETIME_PRECISION,ISNULL(CHARACTER_SET_CATALOG,'''')
CHARACTER_SET_CATALOG
,ISNULL(CHARACTER_SET_SCHEMA,'''') CHARACTER_SET_SCHEMA,
ISNULL(CHARACTER_SET_NAME,'''')
CHARACTER_SET_NAME,
ISNULL(COLLATION_CATALOG,'''') COLLATION_CATALOG
,ISNULL(COLLATION_SCHEMA,'''') COLLATION_SCHEMA,ISNULL(COLLATION_NAME,'''')
COLLATION_NAME,ISNULL(DOMAIN_CATALOG,'''') DOMAIN_CATALOG,ISNULL(DOMAIN_SCHEMA,'''')
DOMAIN_SCHEMA
,ISNULL(DOMAIN_NAME,'''') DOMAIN_NAME from INFORMATION_SCHEMA.COLUMNS';

SET @ExecuteSourceInformationColumn = N'USE ['+@SourceDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_SourceInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_SourceInfoTable
END

CREATE TABLE #Tbl_SourceInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_SourceInfoTable
exec sp_executesql @ExecuteSourceInformationColumn

DECLARE @ExecuteTargetInformationColumn NVARCHAR(4000);

SET @ExecuteTargetInformationColumn = N'USE ['+@TargetDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_TargetInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_TargetInfoTable
END

CREATE TABLE #Tbl_TargetInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_TargetInfoTable
EXEC sp_executesql @ExecuteTargetInformationColumn

SELECT s.TABLE_NAME [Source Table Name],s.COLUMN_NAME [Source Col Name],
s.DATA_TYPE [Source Col Data Type],
IC.TABLE_NAME [Target Table Name],
IC.COLUMN_NAME [Target Col Name],IC.DATA_TYPE [Target Col Data Type] ,
CASE WHEN s.COLUMN_NAME= IC.COLUMN_NAME
THEN 'Match'
ELse 'Column Mismatch Or Not Found'
end [Global Message Info]
FROM #Tbl_SourceInfoTable s LEFT JOIN
     #Tbl_TargetInfoTable IC
              ON s.COLUMN_NAME=IC.COLUMN_NAME
              AND s.TABLE_NAME=IC.TABLE_NAME

Combine Script
DECLARE @SourceDbName SYSNAME = 'SqlBank';
DECLARE @TargetDbName SYSNAME = 'SqlBankCore';

DECLARE @SYSobjectCommonQuery NVARCHAR(4000);
DECLARE @SYSCommentsQuery NVARCHAR(4000);

DECLARE @SourceSYSObjectsExecute NVARCHAR(4000);
DECLARE @SourceSYSCommentExecute NVARCHAR(4000);

DECLARE @TargetSYSObjectsExecute NVARCHAR(4000);
DECLARE @TargetSYSCommentExecute NVARCHAR(4000);

DECLARE @InformationCommonQuery NVARCHAR(4000);
DECLARE @ExecuteSourceInformationColumn NVARCHAR(4000);

--=======Below variable table for xtype & xtype defination list--=====
DECLARE @Tbl_Xtypes TABLE(xtype CHAR(2),xtypeDefination VARCHAR(1000))

INSERT INTO @Tbl_Xtypes Values
('AF','Aggregate function (CLR)'),('C ','CHECK Constraint'),
('D','Default or DEFAULT Constraint'),('F','FOREIGN KEY Constraint'),
('FN','Scalar Function'),('FS','Assembly (CLR) Scalar-Function'),
('FT','Assembly (CLR) Table-Valued Function'),('IF','In-lined Table Function'),
('IT','Internal Table'),('L','Log'),('P','Stored Procedure'),
('PC','Assembly (CLR) Stored Procedure'),
('PK','PRIMARY KEY Constraint (Type is K)'),
('RF','Replication Filter Stored Procedure'),('S','System Table'),
('SN','Synonym'),('SQ','Service Queue'),
('TA','Assembly (CLR) DML Trigger'),('TF','Table Function'),
('TR','SQL DML Trigger'),('TT','Table Type'),
('U','User Table'),('UQ','UNIQUE Constraint'),
('V','View') ,('X','Extended Stored Procedure')

SET @SYSobjectCommonQuery=N'
SELECT NAME,ID,XTYPE,UID,INFO,STATUS,BASE_SCHEMA_VER,REPLINFO,PARENT_OBJ
,CRDATE,FTCATID,SCHEMA_VER,STATS_SCHEMA_VER,TYPE,USERSTAT,SYSSTAT,INDEXDEL,
REFDATE,VERSION,DELTRIG,INSTRIG,UPDTRIG,SELTRIG,CATEGORY,CACHE
FROM SYS.SYSOBJECTS WHERE XTYPE NOT IN (''S'') ORDER BY NAME ASC';

SET @SYSCommentsQuery =N'SELECT id,number,colid,status,ctext,texttype,
language,encrypted,compressed,text FROM sys.syscomments';

--=============Common_Query_For_Both_Database--=====================
SET @SourceSYSObjectsExecute=N'USE ['+@SourceDbName+'] '+@SYSobjectCommonQuery+'';
SET @SourceSYSCommentExecute=N'USE ['+@SourceDbName+'] '+@SYSCommentsQuery+'';
SET @TargetSYSObjectsExecute=N'USE ['+@TargetDbName+'] '+@SYSobjectCommonQuery+'';
SET @TargetSYSCommentExecute=N'USE ['+@TargetDbName+'] '+@SYSCommentsQuery+'';

--PRINT @TargetSYSCommentExecute

IF(OBJECT_ID('tempdb..#Tbl_SourceObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceObjects
END

CREATE TABLE #Tbl_SourceObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_SourceObjects
EXEC sp_executesql @SourceSYSObjectsExecute

IF(OBJECT_ID('tempdb..#Tbl_SourceComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceComments
END

CREATE TABLE #Tbl_SourceComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

--print @SourceSYSCommentExecute
INSERT INTO #Tbl_SourceComments
EXEC sp_executesql @SourceSYSCommentExecute

--======================================================================

IF(OBJECT_ID('tempdb..#Tbl_TargetObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetObjects
END

CREATE TABLE #Tbl_TargetObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_TargetObjects
EXEC sp_executesql @TargetSYSObjectsExecute

IF(OBJECT_ID('tempdb..#Tbl_TargetComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetComments
END

CREATE TABLE #Tbl_TargetComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

--print @SourceSYSCommentExecute
INSERT INTO #Tbl_TargetComments
EXEC sp_executesql @TargetSYSCommentExecute

--======================================================================

Select @SourceDbName [Source DataBase Name], Main.name [Source Object Name],
@TargetDbName[Target DataBase Name],Main.[Object Defination],SubMain.name [Target Object Name]
,Main.text[Source Object text],SubMain.text [Target Object text],
CASE WHEN Main.text=SubMain.text Then
 Concat(Main.[Object Defination], '  Object Match with Source Databse')
 else
 Concat(Main.[Object Defination], ' Object Mismatch Or Not Found with Source Database')
 end [Global Message]
from
(
SELECT o.name, cs.id,cs.number,cs.colid,cs.status,cs.ctext,cs.texttype,
cs.language,cs.encrypted,cs.compressed,ISNULL(cs.text,'') text,
CASE WHEN o.xtype in (SELECT x.xtype from @Tbl_Xtypes x)
THEN (Select xx.xtypeDefination from @Tbl_Xtypes xx where xx.xtype=o.xtype)
else '' end [Object Defination]
FROM #Tbl_SourceComments cs join
#Tbl_SourceObjects o on o.id=cs.id
) Main
left join
(
select css.id,oo.name,ISNULL(css.text,'') text,oo.xtype FROM
#Tbl_TargetComments css  left join
#Tbl_TargetObjects oo on css.id=oo.id

)SubMain  on Main.name=SubMain.name

--===========================Compair Tables Only--=================

SET @InformationCommonQuery = N'
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION
,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX
,NUMERIC_SCALE,DATETIME_PRECISION,ISNULL(CHARACTER_SET_CATALOG,'''')
CHARACTER_SET_CATALOG
,ISNULL(CHARACTER_SET_SCHEMA,'''') CHARACTER_SET_SCHEMA,
ISNULL(CHARACTER_SET_NAME,'''')
CHARACTER_SET_NAME,
ISNULL(COLLATION_CATALOG,'''') COLLATION_CATALOG
,ISNULL(COLLATION_SCHEMA,'''') COLLATION_SCHEMA,ISNULL(COLLATION_NAME,'''')
COLLATION_NAME,ISNULL(DOMAIN_CATALOG,'''') DOMAIN_CATALOG,ISNULL(DOMAIN_SCHEMA,'''')
DOMAIN_SCHEMA
,ISNULL(DOMAIN_NAME,'''') DOMAIN_NAME from INFORMATION_SCHEMA.COLUMNS';

SET @ExecuteSourceInformationColumn = N'USE ['+@SourceDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_SourceInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_SourceInfoTable
END

CREATE TABLE #Tbl_SourceInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_SourceInfoTable
exec sp_executesql @ExecuteSourceInformationColumn

DECLARE @ExecuteTargetInformationColumn NVARCHAR(4000);

SET @ExecuteTargetInformationColumn = N'USE ['+@TargetDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_TargetInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_TargetInfoTable
END

CREATE TABLE #Tbl_TargetInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_TargetInfoTable
EXEC sp_executesql @ExecuteTargetInformationColumn

SELECT s.TABLE_NAME [Source Table Name],s.COLUMN_NAME [Source Col Name],
s.DATA_TYPE [Source Col Data Type],
IC.TABLE_NAME [Target Table Name],
IC.COLUMN_NAME [Target Col Name],IC.DATA_TYPE [Target Col Data Type] ,
CASE WHEN s.COLUMN_NAME= IC.COLUMN_NAME
THEN 'Match'
ELse 'Column Mismatch Or Not Found'
end [Global Message Info]
FROM #Tbl_SourceInfoTable s LEFT JOIN
     #Tbl_TargetInfoTable IC
              ON s.COLUMN_NAME=IC.COLUMN_NAME
              AND s.TABLE_NAME=IC.TABLE_NAME

HostForLIFEASP.NET SQL Server 2019 Hosting

When SELECTing information from the table(s), we mostly ask for grouped data. Grouping allows us to retrieve statistical data by some columns. Grouping is very important, but sometimes it is not just enough to “generate” final information.

Sometimes we need to rotate one of the columns to be as a row without changing the result of grouped data. Long story short, transforming data from a state of rows to a state of columns is called Pivoting.

PS: All examples use Adventurework2019.

Simple example without pivot: (classical grouping)
SELECT
  SalesOrderID
, SpecialOfferID
, SUM(UnitPrice) as TotalSum
  FROM Sales.SalesOrderDetail AS SOD
  GROUP BY SalesOrderID, SpecialOfferID

In the above example, we get data grouped by SalesOrderId and SpecialOfferId. Say your manager asked for the same data but he wants to see SpeciallOfferId in the columns. So from the above example, you will have columns like [1], and [2]. The main focus is SpecialOfferId and we need to somehow rotate the column to be not a column but a row.

T-SQL provides a special table operator to implement it: PIVOT.

Before writing PIVOT, let’s see what you need to know about the internal implementation of PIVOT. You should identify 3 elements:

    What do you want to see on rows?
    What do you want to see on columns?
    What type of information should be rendered in the intersection of these columns and rows ( mostly it is your aggregate column)

Here is the syntax for implementing it,

As we learned from the article, Common Table Expression’s (CTE) use cases wider than Derived Tables and now we will implement PIVOT exactly with CTE.

WITH CTE
AS
(SELECT SOd.SalesOrderID, Sod.SpecialOfferID, UnitPrice
  FROM Sales.SalesOrderDetail AS SOD)
  SELECT SalesOrderId, [1],[2] FROM CTE
  PIVOT(SUm(UnitPrice) FOR SpecialOfferId IN ([1],[2])) AS PVT

As you can see, PIVOT is a special table expression and as a complex combination, it defines the aggregate column for the spread column. Before switching to UNPIVOT, we need to understand some limitations of PIVOT:

    We can use only one aggregate with PIVOT
    The IN clause of the PIVOT operator accepts a static list of spreading values. You need to type them manually without any magic technique.
    No way to use COUNT(*), use classical COUNT(<column name>)

Unpivot is a reverse operation for you PIVOT. If you have any table generated by u PIVOT, you can reverse this table to the “original one” with UNPIVOT.

Let’s use our query with some modifications to create a table that will store all PIVOTted data.
WITH CTE
AS
(SELECT SOd.SalesOrderID, Sod.SpecialOfferID, UnitPrice
  FROM Sales.SalesOrderDetail AS SOD)
  SELECT * INTO MyPivottedTable FROM CTE
  PIVOT(SUm(UnitPrice) FOR SpecialOfferId IN ([1],[2])) AS PVT

The above query will store all information inside new created MyPivottedTable table.

Now it is time for our magic UNPIVOT to ”restore” information to its original.
SELECT SalesOrderID,SpecialOfferID, UnitPrice
 FROM MyPivottedTable
 UNPIVOT(UnitPrice FOR SpecialOfferId IN ([1],[2])) AS UNPVT

Here is the response:

HostForLIFEASP.NET SQL Server 2019 Hosting

These two operators EXCEPT and INTERSECT have been introduced since SQL Server 2005 and these two returns distinct or unique values by comparing the results of two queries.

Moreover, these operators do have rules for combining result sets just like other operators like UNION and we’ll also discuss it.

That’s why in this article we’ll be showing how we can use these two operators and understand their differences.

What is INTERSECT Operator?
Fundamentally, INTERSECT operator combines two or more queries and returns unique rows or records that are the outcomes of both left and right queries.

Then it removes duplicate records as the final result set.

INTERSECT Syntax
--INTERSECT Syntax
query_expression
INTERSECT
query_expression

It is similar to the UNION operator, that’s why both queries must have the same number and order of columns, and the column data type should be the same.

Furthermore, if these rules aren’t followed an error will occur like failing conversion of data and an equal number of expressions error.

INTERSECT Operator Example
Before we can see examples, let’s try to create a table first with some data in it.
DECLARE @TBLDOGS TABLE (Id int, DogBreed nvarchar(50))
DECLARE @TBLDOG_OWNERS TABLE (Id int, OwnersName nvarchar(50),
DogBreed nvarchar(50))

INSERT INTO @TBLDOGS (Id, DogBreed)
VALUES
(1, 'German Shepherd'),
(2, 'Labrador Retriever'),
(3, 'Golden Retriever'),
(4, 'Poodle'),
(5, 'French Bulldog')

INSERT INTO @TBLDOG_OWNERS (Id, OwnersName, DogBreed)
VALUES
(1, 'Bruce Wayne', 'German Shepherd'),
(2, 'Lex Luthor', 'Golden Retriever'),
(3, 'Vandal Savage', 'Dobermann'),
(4, 'Maxwell Lord', 'Dachshund'),
(5, 'Simon Stagg ', 'Poodle'),
(6, 'Elon Musk', 'German Shepherd')

SELECT * FROM @TBLDOGS
SELECT * FROM @TBLDOG_OWNERS

Output

OK, so everything has been set up. Let’s show how we can use the INTERSECT operator.
SELECT DogBreed FROM @TBLDOGS
INTERSECT
SELECT DogBreed FROM @TBLDOG_OWNERS

Output

As you can see, in our example we have two tables @TBLDOGS and @TBLDOG_OWNERS. Going to the data of the two tables, @TBLDOGS have German Shepherd, Labrador Retriever, Golden Retriever, Poodle, and French Bulldog.

While @TBLDOG_OWNERS have German Shepherd, Golden Retriever, Dobermann, Dachshund, Poddle, and German Shepherd.

The intersection of the two tables resulted in a German Shepherd, Golden Retriever, and Poodle.

What is EXCEPT Operator?
Fundamentally, EXCEPT operator subtracts a query result set from another query result set.

In other words, it returns unique rows or records from the left query that doesn’t appear or are not found on the right query.

EXCEPT Syntax
--EXCEPT Syntax
query_expression
EXCEPT
query_expression

Moreover, both queries must have the same number and order of columns, and the column data type should be the same.

Furthermore, if these rules aren’t followed an error will occur like failing conversion of data and an equal number of expressions error.

EXCEPT Operator Example

Data about dogs from the previous example, we’re still going to use here.

To lessen confusion I’ll be posting again the data sample of dogs.
DECLARE @TBLDOGS TABLE (Id int, DogBreed nvarchar(50))
DECLARE @TBLDOG_OWNERS TABLE (Id int, OwnersName nvarchar(50),
DogBreed nvarchar(50))

INSERT INTO @TBLDOGS (Id, DogBreed)
VALUES
(1, 'German Shepherd'),
(2, 'Labrador Retriever'),
(3, 'Golden Retriever'),
(4, 'Poodle'),
(5, 'French Bulldog')

INSERT INTO @TBLDOG_OWNERS (Id, OwnersName, DogBreed)
VALUES
(1, 'Bruce Wayne', 'German Shepherd'),
(2, 'Lex Luthor', 'Golden Retriever'),
(3, 'Vandal Savage', 'Dobermann'),
(4, 'Maxwell Lord', 'Dachshund'),
(5, 'Simon Stagg ', 'Poodle'),
(6, 'Elon Musk', 'German Shepherd')

SELECT * FROM @TBLDOGS
SELECT * FROM @TBLDOG_OWNERS

Output

OK, so everything has been set up. Let’s show how we can use the EXCEPT operator.

SELECT DogBreed FROM @TBLDOGS
EXCEPT
SELECT DogBreed FROM @TBLDOG_OWNERS

SELECT DogBreed FROM @TBLDOG_OWNERS
EXCEPT
SELECT DogBreed FROM @TBLDOGS

Output

Going to the data of the two tables, the EXCEPT operator resulted differently and it makes a significant difference because of what is on the left table.
Moreover, as we change the left table we have different results.
Moreover, from the example above the first result, the left query is the @TBLDOGS while the right query is the @TBLDOG_OWNERS.
While the second result, the left query is the @TBLDOG_OWNERS while the right query is the @TBLDOGS.
Let’s see the difference between EXCEPT and INTERSECT operators in the next section.

Difference Between INTERSECT and EXCEPT Operators

Remember that the Intersect operator uses an intersection that takes rows from both result sets that are common in both.

While the Except operator takes rows from the left result set but excludes the right result set that isn’t found.

Conclusion
In this article, we have learned how to use the SQL Server’s INTERSECT and EXCEPT operators and have seen some examples to understand these operators.
Moreover, we didn’t forget to show the difference between the two.
I hope you have enjoyed this article, as I enjoyed it while writing.
Till next time, Happy programming! Cheers! And Thank you!

HostForLIFEASP.NET SQL Server 2019 Hosting

Enable configuration in SQL
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Sample API link
-- Sample API Link
http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile

Sample JSON Request
@Mobile varchar(max),
@Flag varchar(max)

{
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '

Content Type
application/json

Create Store Procedure for Calling Web API.
--//========================
    -- if you learn more please visit my blog

  -- https://saipathrikar.blogspot.com/
--//========================
--================ execute this 1st for 1st time use only

Create Proc Proc_CallApiFromSQL
(
@Mobile varchar(max),
@Flag varchar(max)
)
as
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
 declare @json table (Json_Table nvarchar(max))
 declare @body varchar(max)
 declare @Apilink varchar(max)
 set @Apilink='http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile';
 set @body='

 {
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'post',@Apilink,'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
Exec sp_OAMethod @Object, 'send', null, @body
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

  INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
--select * from @json
-- Parse the JSON string
SELECT * FROM OPENJSON((select * from @json))
WITH (
CID bigint,CName varchar(max),AC_Balance varchar(max),AC_No varchar(max),AC_ID bigint,CDOB datetime,
CEmail varchar(max),ReciverEmail varchar(max),CMObile varchar(max),CGender varchar(max),CPan varchar(max),
CAdhaar varchar(max),CNationality varchar(max),CAddress varchar(max),City varchar(max),State varchar(max),
Country  varchar(max),PinCode varchar(max),Cisdelete bit,CreatedBy varchar(max),CreatedDate datetime,ModifiedBy varchar(max),
ModifiedDate datetime,UID bigint,CustImgPath varchar(max),CustAdaarPath varchar(max),CustPanPath varchar(max),
Flag varchar(max),OpMsg varchar(max),Pass varchar(max)
)
return

Execute Stored Procedure
EXEC  Proc_CallApiFromSQL '8541254874','SE'

Output

HostForLIFEASP.NET SQL Server 2019 Hosting

Create Sequence in SQL
CREATE SEQUENCE Seq_AccountNumber As BigInt
Start with 100001
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999999999999999
CYCLE
CACHE;

Use Sequence in Procedure for generating numbers Like "Sbank_10001".
CREATE Proc Proc_GenerateAccountNumber
as
Declare @BnkName varchar(100)='Sbank_';
Declare @AccountNumber varchar(max);

SELECT @AccountNumber =CONCAT(@BnkName ,
 next value for dbo.Seq_AccountNumber
)
SELECT @AccountNumber as AccountNumber
Return

Now execute procedure "Proc_GenerateAccountNumber" & check output

If you want to restart Sequence Alter Sequence,
alter sequence dbo.Seq_AccountNumber Restart with 10001

HostForLIFEASP.NET SQL Server 2019 Hosting

Today we will look at a very common debate that occurs while developing a data access layer. This debate is to decide if we want to use stored procedures and keep our logic in the database, or if we want to use an ORM like Entity Framework and keep all logic in our middle tier C# code, using the database only as a data store. I will not be detailing the different types of ORMs and samples on using them as this information is widely available on the web. I will also not detail how to write and use stored procedures, as this information is also widely available for almost all types of databases. I will simply try to explain the pros and cons of using each and which and why I think it is a better solution.

What are Stored Procedures?
Let us begin by identifying what stored procedures are. Stored procedures are commonly used artifacts of relational databases, like SQL Server, Oracle, etc. These can include code to manipulate the data and are efficient in selecting, processing, and sending back data to the front-end application. These are written using SQL language, which is easy to understand and learn. Some of the major advantages of using stored procedures are that these are compiled, optimized, and run by the database engine and so can perform very quickly. For this reason, I have seen some architects strongly push the use of stored procedures.

What is an ORM?
An ORM means object relational mapping. This method allows us to create classes to represent our database tables, and then use a context or communication class to join these classes to our database tables. Then, we can use the full power of the middle-tier programming language, like C#, to write queries to select and update the data in our tables. Of course, an ORM does not end there, and we can also create other artifacts for stored procedures, etc. We can also merge classes from multiple tables and break a single table into multiple classes. However, the main idea is to use the database mainly as a store and keep all processing logic in the middle-tier code.

What to use today? Stored Procedures vs ORM
These days we have a much-advanced landscape for both stored procedures in various databases, and also great ORMs. For example, Entity Framework Core gives us many options and is easy to understand and implement. So, what should we use? The answer is that it depends upon the particular scenario. However, I would prefer to use an ORM, as in this way we can better abstract all business and processing logic into proper classes inside our middle-tier, and use the power of all libraries, NuGet packages, etc. available at this level. This also saves us from being tied to a particular database, and in the future, we can move to another data store quite easily. However, if we need to do some heavy programming at a database level and want to fully utilize the power of the database engine, using stored procedures would be the way to go. However, in this case, we might need to commit to a particular database for a longer period, especially if we are using a SQL dialect for that particular database to write our stored procedures.

Summary
In this article we looked at what to choose to build, our data access or data process layer. The answer depends on the coder's needs, and different people will have different opinions on it. However, from my personal point of view, I prefer to keep all logic in the middle-tier. This way the logic is better abstracted, and we can use the vast number of libraries available to process the data. Also, we can retrieve data in chunks and process it using parallel programming techniques and utilize the extremely powerful hardware we have these days. This also does not tie us to any particular database.

HostForLIFEASP.NET SQL Server 2019 Hosting

T-SQL supports different types of table expressions,
    Views
    Functions
    Derived Tables (DT)
    Common Table Expressions (CTE)

We will have separate articles on functions and views. But today’s post is related to Derived Table and Common Table Expression and their differences. By the way, what is table expression?

Table expression - is a special type of named query which allows us to build relational-oriented queries. You create a special query, rename it, and can use it in multiple types ( at least as a view and a function)

You can wrap your query into views and functions, store them as an object in the database and call them as a user-defined table which can have an argument (function) or without it.

But unfortunately, the above sentence is not correct for DT and CTE. T-SQL provides your to wrap your query into Derived table and Common Table Expressions but storing them directly as a separate object is not possible.
Why do we need to use Derived Table and Common Table Expression?

When working with SELECT, we usually apply special searching/filtering to our queries. But in a few cases, it is not possible to do it.
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD

Let’s try to filter numbers that are less than 200.
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD
WHERE rownum < 200

As we know from this article, every statement has its logical execution number and Where executes before SELECT and defined alias in SELECT can’t be detected in WHERE. How about using expression directly:
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD
WHERE Row_number() over(order by SalesOrderDetailId) < 200

It is also not possible because Windowed functions can only appear in the SELECT and ORDER clauses. (read error message)

So, how we can filter numbers?

Except for using views and functions there are 2 more options,
    Derived Tables
    Common Table Expressions

2 types of writing Derived tables,
SELECT * FROM
(
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD) AS DT
WHERE DT.rownum < 200

--second version of writing derived tables

SELECT * FROM
(
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD) AS DT(RowNumber, SalesOrderID, CTrackingNumber)
WHERE DT.RowNumber < 200

Now, it is possible to wrap and filters our query. When comparing with Subqueries, Derived tables allow us to wrap the entire query but in subqueries, the inner query “lived” in the WHERE clause.

There are 2 “but”s when using derived tables:

It is really hard to refer from one derived table to another when building complex queries:
SELECT ...
FROM (SELECT
    FROM (SELECT ...
        FROM T1
            WHERE ...) AS MyDT1
         WHERE ....) AS MyDT2
    WHERE ....

If you want to combine multiple instances of the same derived table, it will not be possible.
SELECT * FROM
 (
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD)
AS DT(RowNumber, SalesOrderID, CTrackingNumber)
INNER JOIN DT as DT2
ON DT.RowNumber = DT2.RowNumber

The problem here is related to the same-time execution of expression in the T-SQL lifetime.

To write it correctly, you need to type DT twice.
SELECT * FROM
 (
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD)
AS DT(RowNumber, SalesOrderID, CTrackingNumber)
INNER JOIN (SELECT
Row_number() over(order by SalesOrderDetailId) as rownum,
SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail AS SOD)
AS DT2(RowNumber, SalesOrderID, CTrackingNumber)
ON DT.RowNumber = DT2.RowNumber

rom the usage point of view, CTE does the same thing DT does. We can wrap and filter, join, and order our queries like in DT. But you can think about it as version 2.0 of Derived Tables.
WITH CTE
AS(
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD)
SELECT * FROM CTE
WHERE rownum < 200;

What are the advantages of using Common Table Expressions?

It is really easy to refer from one CTE to another,
WITH CTE
AS(
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD)
,
CTE2 AS --from CTE2 we referred to CTE
(SELECT * FROM CTE)
SELECT * FROM CTE2;

If you want to combine multiple instances of the same expressions, it is also easy to do it with CTE.
WITH CTE
AS(
SELECT
Row_number() over(order by SalesOrderDetailId) as rownum, *
FROM Sales.SalesOrderDetail AS SOD)
SELECT * FROM CTE
INNER JOIN CTE as CTE2
ON CTE.rownum = CTE2.rownum

Writing recursive queries with CTE:
CREATE TABLE [dbo].[RecursiveTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Level] [int] NULL,
    [SubLevel] [int] NULL,
    [Name] [nvarchar](30) NOT NULL,
    [Position] [varchar](30) NULL
) ON [PRIMARY]
GO

WITH CTE
AS
(SELECT Id, Level, SubLevel, Name, Position
FROM Rekursiya WHERE Id = 1
UNION ALL
SELECT R.Id, R.Level, R.SubLevel, R.Name, R.Position
FROM CTE
INNER JOIN RecursiveTable AS R
ON R.SubLevel = CTE.Level)
SELECT * FROM CTE;

Working with PIVOT and Unpivot ( I’ll write a separate article about it )

HostForLIFEASP.NET SQL Server 2019 Hosting

We always have chance to use connection strings, such as in an application. When we need it, the first idea is to acquire it from SQL Server Managerment Studio. However, it seems we cannot.

This article will introduce several ways that we can get connection strings easily. The article will cover the following:
    Introduction
    A - Get Connection string by SSMS (SQL Server Management Studio) --- no way
    B - Get Connection string from online reference or generator
    C - Get Connection String by Visual Studio, Server Explorer
    D - Get Conenction String by Microsoft Universal Data Link file (xxx.udl)

A - Get Connection String by SSMS
Although the first expection is to get Connection String from SSMS, but actually, we cannot. Open SSMS, right click a Database Connection => Properties.

Open the Server Properties Window. This is most possible place where I might get the database Connection String. However, we have all parameters available, but we cannot see the whole connection string.

Click the link "View Connection Properties" on the left panel of this Window:

As before, I can get all parameters, but not the connection string.
B - Get Connection String by online Reference or Generator

online Reference:
From the Web, we can check and get the connection string we need, such as in:

ConnectionStrings References - ConnectionStrings.com --- in general
    SQL Server connection strings - ConnectionStrings.com --- Specific for SQL Server

We can get connection string like this:

However, we do not have chance to test the connection string.

Online Generator:

We can use an online Connection String generator, such as:

    SQL Server Connection String Generator | Aireforge®

To generate a Connection String by your parameters:

You can get a real and ready connection string using your specifc parameters, but stil cannot make a test.

C - Get Connection String by Visual Studio's Server Explorer
In Visual Studio IDE, Click View => Server Explorer:

In the Server Explorer, click the Connect to Database icon:

To add a connection:

In this way, we can test if the connection is working.

Finally, click the Advanced Button. Then we can get the connection string at the bottom of the Advanced Properties Window.

Otherwise, after creating the connection, right click => Properties

In Properties Window, we will see the ConnectionString there too:

D - Get Connection String by Microsoft Universal Data Link file (xxx.udl)
We can create a Data.udl file in the computer:

Double click the Data.udl file, and set up the connection:

Click the Test Connection button:

Close the created connection, and open the Data.udl file by another editor, such as VS Code:

We will see the connection string:

HostForLIFEASP.NET SQL Server 2019 Hosting