Learn to use Union, Intersect, and Except Clauses

In this article, we will understand Union, Intersect, and Except clause in SQL Server. Also, we will see the difference between Intersect and Union in SQL Server. Also, the difference between Join with Intersect by Sagar Jaybhay

The intersect operator retrieves the common records between the left and right queries of the intersect operator.

  1. This is introduced in SQL Server 2005.
  2. The number of columns and order of columns should be the same.
  3. The data types must be the same or least compatible.
  4. It filters duplicate records and selects only distinct records that are common in the left and right queries. But if you use inner join then it will not filter distinct records.
  5. To use inner join behave like intersect you need to use distinct records

select * from Employee where EmpID between 1 and 100
intersect
select * from Employee where EmpID between 1 and 10;
Intersect Operator In SQL Server

In this we also get the result using inner join and below is a result

select * from Employee as e 
inner join (select * from Employee where EmpID between 1 and 10) as a on e.EmpID=a.EmpID
Join Result

In an inner join, it treats 2 Null values are different and if you join 2 tables on that column on which column has null values then inner join will not include that null value records where intersect treat null as same and return matching records.

What is the difference between Union, Intersect and Except operator in SQL Server?

Union:

The Union operator will return all unique rows from the left query and right query and union all operator will include duplicate also.

Intersect :

The intersect operator will retrieve all unique rows from the left and right queries.

Except:

The Except operator will retrieve all the unique rows from the left query that are not present in the right query result set.

Cross Apply and Outer Apply In SQL Server

The Apply Operator is introduced in SQL Server 2005 and it is basically used to join a table and table-valued function.

The table-valued function on the right-hand side gets called for each row in the left-hand side table.

The cross apply will return only matching rows and it is similar to an inner join.

Outer apply will return matching rows as well as non-matching rows and is similar to left join and the columns which are unmatched of table-valued function is returned as null.

https://sagarjaybhay.net

How Angular Routing Works and What is Routing?

In this article, we will understand How Angular Routing Works and What is the Meaning Of routing In Angular By Sagar Jaybhay.`

What is the Meaning Of routing In Angular

Routing basically means navigating between pages. You have seen many sites with links that direct you to a new page.

The Angular Router enables you to show different components and data to the user based on where the user is in the application. The router enables navigation from one view to the next as users perform tasks.

Angular Routing

Routing Collection is a place where we specify the URLs and components where loaded.

If you create an angular project using cli command then it will automatically add your_app_name-routing.module.ts file where we can define routes.

import  NgModule  from '@angular/core';
import  Routes, RouterModule  from '@angular/router';
import HomeComponent   from "./home/home.component";

import  LeftmenuComponent  from "./leftmenu/leftmenu.component";
import   SupplierComponent from "./supplier/supplier.component";
import  CustomerComponent  from './Customer.component';

const routes: Routes = [
path:'Home',component:HomeComponent,
path:'Customer',component:CustomerComponent,
path:'Supplier',component:SupplierComponent,
path:'',component:HomeComponent
];

@NgModule(
  imports: [RouterModule.forRoot(routes)],
  exports: [RouterModule]
)
export class AppRoutingModule  
angular routing file

This file needs to add in our main module file by default it is added but if it is not added by default you can go add this in this location.

Routing Module added

But for this path, we need to modify some of the Html code like below.

<p>home works!</p>
<a [routerLink]="['Home']">Home</a> <br/>
<a [routerLink]="['Customer']">Customer</a> <br/>
<a [routerLink]="['Supplier']">Supplier</a> <br/>
<hr>
<br/>
<br/>
<br/>
<div>
    <router-outlet></router-outlet>
</div>

In the above code, we use 2 different directives routerLink and router-outlet.

base href

Most routing applications should add an <base> element to the index.html as the first child in the <head> tag to tell the router how to compose navigation URLs.

If the app folder is the application root, as it is for the sample application, set the href value exactly as shown here.

src/index.html (base-href)

content_copy<base href="/">

routerLink

This is used to generate a router link. In our case  http://localhost:4200/Home you can pass a parameter to the route by using the following way.

a [routerLink]="['/user/bob']" [queryParams]="debug: true" fragment="education">
    link to the user component
  </a>

If you want to preserve state in the browser you can use state variable.

<a [routerLink]="['/user/bob']" [state]="tracingId: 123"> link to the user component
</a>	

router-outlet

It is used to placed dynamically added content in our case it is a component that is associated with view. Each outlet can have a unique name, determined by the optional name attribute. The name cannot be set or changed dynamically. If not set, the default value is “primary”.

<router-outlet></router-outlet>
<router-outlet name='left'></router-outlet>
<router-outlet name='right'></router-outlet>

A router outlet emits an activate event when a new component is instantiated, and a deactivate event when a component is destroyed.

<router-outlet
  (activate)='onActivate($event)'
  (deactivate)='onDeactivate($event)'></router-outlet>

GitHub Project Link: – https://github.com/Sagar-Jaybhay/angular9

Angular Binding how it works and the Significance of package.lock.json

In this article, Sagar Jaybhay explains different ways of angular binding in angular and Package.lock.json file significance in angular application.

Angular Binding

Their are 3 types of binding which are listed below.

  1. One-Way binding( UI – – -> Component): when you want to send data from UI to Component you can use this. () angular bracket this syntax is used for one way binding from UI to Component.
  2. One-Way binding [Component – – -> UI]: when you want to send data from Component to UI you can use Square brackets.
  3. Two-Waybindig: when you want to send data from UI to Component and Component to UI you can use [()] this syntax.
Customer ID : <input [(ngModel)]="CustomerModel.CustomerID" type="text">
<br/>
Customer Name : <input [(ngModel)]="CustomerModel.CustomerName" type="text">
<br/>
Customer Amount : <input [(ngModel)]="CustomerModel.CustomerAmount" type="text">

<br/>
<br/>
<hr>

<table>
    <tr><td>Customer ID</td><td>CustomerModel.CustomerID</td></tr>
    <tr><td>Customer Name </td><td>CustomerModel.CustomerName</td></tr>
    <tr><td>Customer Amount</td><td>CustomerModel.CustomerAmount</td></tr>
</table>

But above code is not worked it will throw an error

ngModel Angular Error: Can’t bind to ‘ngModel’ since it isn’t a known property of ‘input’

why because angular have the modular approach and to work binding, it needs formModule for that purpose we need to import this in our customer.module.ts file now our code becomes.

import  BrowserModule  from '@angular/platform-browser';
import  NgModule  from '@angular/core';

import FormsModule  from '@angular/forms'

import  AppRoutingModule  from './Customer-routing.module';
import  CustomerComponent  from './Customer.component';

@NgModule(
  declarations: [
    CustomerComponent
  ],
  imports: [
    BrowserModule,
    AppRoutingModule,
    FormsModule
  ],
  providers: [],
  bootstrap: [CustomerComponent]
)
export class AppModule  

angular formModule

Package.Lock.json:

This package.lock.json tells us how npm versioning works.

In the above figure, 5.2.1 is our version in this 5 represents a major version, 2 is a minor version and 1 is revision. So by using the above image, you will understand version number is divided into 3 parts major, minor and revision.

  1. Major version:– it means developer added new features remove the old one and major version incremented when there are breaking changes.
  2. Minor Version: It is incremented when there are new features added but no breaking changes present.
  3. Revision: it is incremented when patches or bug fixes.

The below image is for package.lock.json.

package.lock.json

So if you check the above image if no symbol present in-front of version number then it is not updated automatically. Npm does not increment the major version automatically. But it gives provision for upgrading minor version and provision by using below symbols in front of version numbers.

^:- this for latest minor and revision version

~:- this is for the only install the latest revision

This is the file created after npm resolution and it created automatically.

GitHub Project Link: https://github.com/Sagar-Jaybhay/angular9

How to Start a YouTube Channel Free – Complete Guide

Now a days YouTube is very popular, you many not find single person on earth who don’t know what YouTube is? so let’s understand answer of below three questions

  1. What is a YouTube channel?  
  2. What is the purpose of the YouTube channel?
  3. How to create a YouTube channel?

Contents

What is a YouTube channel?

Purpose of You tube channel

Ø      To Show case your talent to the world.

Ø      Showcase your art.

Ø      Sharing skill or Hobby.

Ø      show information to the world and Sharing knowledge.

Ø      Promoting your Business.

Ø      earn money from youtube.

Ø      Increase your social presence on world of internet.

How to create YouTube channel?.

Step 1 :  Create your Google account (Gmail account).

1.      Open Google signup account https://accounts.google.com/signup.

2.      filled and information and click on NEXT button.

3.      provide mobile number and click on NEXT button.

4.      enter OTP number sent on  phone number and click on NEXT button.

5.      Provide the Recovery mail and personal information and click on NEXT button.

6.      Provide you  additional number (Optional ) and click on “skip ” or “yes I am in ” button.

7.      Read Privacy and Terms  and click on  “I agree” button.

8.      Your account is ready.

Step 2 :  Open URL  https://www.youtube.com/channel_switcher.

Step 3 : click on “Create a new channel”  and provide your channel name.

Step 4 : Your channel is created , start uploading YouTube video.

Step 5 :  click on “Upload Button”  and select file by clicking “Select File” Button.

Step 6 : Provide the File name and provide description about YouTube video and links and/or details about video  

Step 7 : Click on “Next” button and provide YouTube video elements (optional).

Step 8 : Provide “Visibility” option.

Step 9 : click on “SAVE” button , your YouTube video is published.

Step 10 :  to See the YouTube Dashboard of your YouTube channel, open YouTube Studio https://studio.youtube.com/.

Some more info.

What is a YouTube channel?

YouTube channel is way of organize your YouTube video content for your audience.

you can add videos, links, and info about yourself or your channel for visitors to explore.

In YouTube every video is associated with channel, Channel is way logically grouping of all YouTube video?  Without channel you will not have any present in YouTube also you cannot upload any video on YouTube without YouTube Channel.

Purpose of You tube channel

There are lot of benefits of YouTube channel, below are purpose of youtub

Ø  To Show case your talent to the world

Ø  Showcase your art

Ø  Sharing skill or Hobby

Ø  show information to the world and Sharing knowledge

Ø  Promoting your Business

Ø  earn money from youtube

You can see details of how to earn money from youtube .

Ø  Increase your social presence on world of internet

  • Release music on YouTube or Upload YouTube music videos
  • youtube movies

How to create YouTube channel?

Step 1 :  Create your Google account (Gmail account)

YouTube is product of google company, so if you have google account or Gmail account then you can move to step 2.  

Below are steps to create google account or Gmail account, and youtube sign in, if you have google account or Gmail account then move to next steps

1.      Open Google signup account https://accounts.google.com/signup

2.      filled and information and click on NEXT button

YouTube Account Creation

3. provide mobile number and click on NEXT button

You-Tube-Verify-Mobile-Numbe

4.  Enter OTP number sent on  phone number and click on NEXT button

verify-number

5. Provide the Recovery mail and personal information and click on NEXT button

Personal-Info-Filling

6     Provide you an additional number (Optional ) and click on “skip ” or “yes I am in ” button

7. Read Privacy and Terms  and click on  “I agree” button

You-Tube-Privacy-Terms

8.  Your account is ready

Step 2:  Open URL  https://www.youtube.com/channel_switcher

Log in into you’re your google account and open URL https://www.youtube.com/channel_switcher

YouTube channel swticher

Step 3 : click on “Create a new channel”  and provide your channel name

You can provide the Channel name or Brand name here, name should be unique in nature

You-Tube-Brand-account

Step 4 : Your channel is created , start uploading YouTube video

Step 5 :  click on “Upload Button”  and select file by clicking “Select File” Button

If you click on “SELECT FILE” button, it will start browsing video present on hard disk . provide the absolute path to load file

Here you can youtube mp4 and youtube mp3 video as well

upload-youtunbe-video

Step 6 : Provide the File name and provide description about YouTube video and links and/or details about video

In this Step , you will have three option

  1. Details: in Details, you need to provide the Title of video and Description of video.  The description should be in detail and prescribe. Title and Description should explain . in Details you need to provide  youtube tags so it will be helpful for searing video
  2. Thumbnail: in Thumbnail you need to upload an image , this image will be shown in your video at the start
  3. Playlist: If you want to youtube upload video in a playlist, please select Playlists. this is optional.
    If you want you can create new playlist as well by selecting dropdown “Playlist”
fill-details-of-youtube-video

Step 7 : Click on “Next” button and provide YouTube video elements (optional)

additional-info-of-youtube-videos

Step 8 : Provide “Visibility” option

In Visibility option, you can select which target audience can see your YouTube video , public/unlisted/private.

In Visibility there is option “Schedule”, using this we can schedule the publishing video date and time.

schedule-info-of-youtube

Before publishing you need to check whether children appear in this video and you need to see video content.

content-videos-violation

Step 9 : click on “SAVE” button , your YouTube video is published

Video save from youtube , so it is publish to worlds , any one from world can see your video

Step 10 :  to See the YouTube Dashboard of your YouTube channel, open YouTube Studio https://studio.youtube.com/

YouTube Studio is youtube dashboard to see below detail

  • analytics of your YouTube video such as Real-time Impression, Typical performance, Notification Metrics
  • News
  • to Know issue
  • to Know how your youtube video is performing
  • Insights see what is working and what is not working
  • Comment filter: find a comment that are most important to you 
  • New upload flow
You-Tue-dashboard

Some more info

By clicking on home youtube , you can go to your Home page of Channel

Please note that you can do all steps in your mobile phone on youtube app

Below  are list of extrac activity you can do

  • relaxing music/sleeping music
  • youtube live
  • youtube premium
  • youtube kids
  • youtube app download for pc
  • youtube music free
  • release youtube new song
  • youtube film
  • download video using  google’s youtube go application
  • youtube online
  • youtube news
  • open youtube
  • my youtube
  • youtube on
  • youtube creator studio

How Angular know which module is the startup module?

In this article, we will learn about How Angular 9 knows which module is the startup module and which is not? Also, we will understand Which conventions Angular team Uses by Sagar Jaybhay.

Which One is the startup Module?

This is mentioned in the main.ts file and below is coed for this file.

import  enableProdMode  from '@angular/core';
import  platformBrowserDynamic  from '@angular/platform-browser-dynamic';

import  AppModule  from './app/app.module';
import  environment  from './environments/environment';

if (environment.production) 
  enableProdMode();


platformBrowserDynamic().bootstrapModule(AppModule)
  .catch(err => console.error(err));

platformBrowserDynamic is used to a bootstrap module. This used to set the first module. But another question is …..

How main.ts file is invoked in an angular application?

The main.ts file is invoked by index.html file. But if you see the index.html file code we didn’t find any kind of script invocation code because these all things are done in bundling and minification and when you see dist folder you can see some js files.

Before bundling index.html code.

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Customerapplication</title>
  <base href="/">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="icon" type="image/x-icon" href="favicon.ico">
</head>
<body>
  <app-root></app-root>
</body>
</html>

After bundling index.html file in the dist folder. This bundling is done by a webpack.

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Customerapplication</title>
  <base href="/">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="icon" type="image/x-icon" href="favicon.ico">
</head>
<body>
  <app-root></app-root>
http://runtime-es2015.js
http://runtime-es5.js
http://polyfills-es5.js
http://polyfills-es2015.js
http://styles-es2015.js
http://styles-es5.js
http://vendor-es2015.js
http://vendor-es5.js
http://main-es2015.js
http://main-es5.js</body>
</html>

In the above code, you will see the script files which angular refer.

  1. Runtime.js:- this file contains code for webpack runtime.
  2. Polyfill.js:- used to new code run in old browser
  3. Vendor.js:- this is actually our code or custom coded files where our component, module, and models have resided.
  4. Main.js:- this is the point where the first module in our program is called.
  5. Style.css:- it contains all CSS code

Naming Convention Used By Angular Team

Whatever the angular team followed for file naming convention by angular is Angular Style Guide. For more information about this, you can visit this link https://angular.io/guide/styleguide

default angular naming conventiones

In the above image, the app is the root of the application. Names are given us

  1. root_folder_name.component.ts
  2. root_folder_name.component.css
  3. root_folder_name.module.ts
  4. root_folder_name.component.html

In angular team thinking app comprises of Html, CSS, model.  This is given by the angular team style guide. But if you want to use your thought process, company guidelines in which you worked you can use.

Now onwards we create customer applications in that we create customer models and whatever the name in the app we renamed it, the customer.

Below is our customer model

export class Customer
    CustomerName:"";
    CustomerID:number;
    CustomerAmount:number;

To bind these properties with the component we need to use directives which are used for binding or data flow means from view to component or from component to view. This data flow is in one way or two way. It also used to manipulate the dom elements. We learn this in the upcoming chapters.


GitHub Project Link: https://github.com/Sagar-Jaybhay/angular9


How to Find Blocking Queries in Transaction In SQL server?

In this article, we will learn Blocking Queries In Transaction and Except Operator and difference between Except and Not In Operator in SQL Server By Sagar Jaybhay.

Blocking Queries

Blocking Query happens because there is an open transaction.

DBCC OpenTran is a command for checking the open transaction but there is a problem it only shows the oldest active transaction. It is not going to show you an open transaction.

dbcc opentran

We write a transaction and execute that and run dbcc opentran command see below image

dbcc opentran 2

Below is a query which gives you all open transaction this query I found one of the blogs whose link is this.

https://stackoverflow.com/questions/4449719/is-there-a-way-to-list-open-transactions-on-sql-server-2000-database

SELECT
trans.session_id AS [SESSION ID],
ESes.host_name AS [HOST NAME],login_name AS [Login NAME],
trans.transaction_id AS [TRANSACTION ID],
tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION 
BEGIN TIME],
tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS ESes
ON trans.session_id = ESes.session_id
WHERE ESes.session_id IS NOT NULL

Except Operator in SQL server:

The Except Operator returns unique rows from the left query which are not present as a result of the right query.

  1. It is introduced in SQL server 2005
  2. The number and order of columns needs to be same
  3. The data types need to be same
select * from Employee where EmpID between 1 and 20
except 
select * from Employee where EmpID between 10 and 100;

In the above query, we have 1 to 9 empid is not present in the second query so it only returns 1 to 9 empid result.

What is the difference between Except and Not In operator in SQL Server?

Except operator returns unique rows from left result set which are not present in right result set and Not In operator also does the same.

select * from Employee where EmpID between 1 and 20
except 
select * from Employee where EmpID between 10 and 100;

The above query is for except operator and below is a query for not in operator which does the same.

select * from Employee where (EmpID >=1 and EmpID <=20) and EmpID
not in(
select EmpID from Employee where EmpID between 10 and 100);

So what is the difference between them?

  1. Except filters duplicate and returns only distinct rows from the left query that not in the right query but Not in Operator not filter the duplicates.
  2. Except operator columns need to be same and also datatype but Not in operator works with a single column from outer query to a single column in Inner query.

Profile Link of GitLab Account is:- https://gitlab.com/Sagar_Jaybhay

T SQL Advanced Tutorial By Sagar Jaybhay 2020

In this article we will understand T SQL Advanced Tutorial means Transaction In SQL and Common Concurrency Problem and SQL server transaction Isolation level by Sagar Jaybhay

What is the Transaction?

A transaction is a group of commands that changed the data stored in a database. A transaction is treated as a single unit.

The transaction ensures that either all commands will succeed or none of them. Means anyone fails then all commands are rolled back and data that might change is reverted back to the original state. A transaction maintains the integrity of data in a database.

begin try
begin transaction
	update dbo.account set amount = amount-100 where id=1
	update dbo.account set amount=amount+100 where id=2
commit transaction
print 'transaction committed'
end try
begin catch
rollback transaction
print 'transaction rolled-back'
end catch
Concurrent Transaction

In the above example either both statements executed or none of them because it goes in catch block where we rolled-back transactions.

begin try
begin transaction
	update dbo.account set amount = amount-100 where id=1
	update dbo.account set amount=amount+100 where id='A'
commit transaction
print 'transaction commited'
end try
begin catch
rollback transaction
print 'tranaction rolledback'
end catch
Transaction-Fail-and-Roll-Back

Common Concurrency Problem

  1. Dirty reads
  2. Lost update
  3. Nonrepetable reads
  4. Phantom reads

SQL server transaction Isolation level

  1. Read Uncommitted
  2. Read committed
  3. Repeatable read
  4. Snapshot
  5. Serializable

How to overcome the concurrency issues?

One way to overcome this issue is to allow only one user at the time allowed for the transaction.

Dirty Read Concurrency Problem:

A dirty read happens when one transaction permitted to read data that modified by another transaction but that yet not committed. Most of the time it will not cause any problem because if any case transaction fails then the first transaction rolled back its data and the second transaction not have dirty data that also not exist anymore.

To do 2 transactions on one machine open 2 query editor that is your 2 transaction machine and you do an operation like below

Multiple-Transaction

For the first transaction, we update the amount in the account table and then given a delay for 1 min 30 seconds and after this, we rollback the transaction. And in the second window, we select data from a table where we can see uncommitted data and after transaction rollback, we see committed data.

We have default isolation level read committed to set different for reading uncommitted data you can use below command.

set transaction isolation level read uncommitted;


-- the First transaction

begin transaction

update account set amount=amount+1000000 where id=1;

waitfor delay '00:01:30'
rollback transaction

-- Second Transaction

set transaction isolation level read uncommitted;
select * from account;
Success-Ful-Multiple-Transaction.png

Lost Update

It means that 2 transactions read and update the same data. When one transaction silently overrides the data of another transaction modified this is called a lost update.

Both read committed and read uncommitted have lost update side effects.

Repeatable reads, snapshots, and serialization do not have these side effects.

Repeatable read has an additional locking mechanism that Is applied on a row that read by current transactions and prevents them from updated or deleted from another transaction.

-- first transaction

begin transaction
declare @amt float
select @amt=amount from account where id =1;

waitfor delay '00:01:20'
set @amt=@amt-1000
update account set amount=@amt where id=1;
print @amt
commit transaction
-- first tarnsaction
-- second transaction
begin transaction
declare @amt float
select @amt=amount from account where id =1;

waitfor delay '00:00:20'
set @amt=@amt-2000
update account set amount=@amt where id=1;
print @amt
commit transaction
Lost Update

Non-Repeatable read

It was when the first transaction reads the data twice and the second transaction updates the data in between the first and second transactions.

Phantom read

It happens when one transaction executes a query twice and it gets a different number of rows in the result set each time. This happens when a second transaction inserts a new record that matches where the clause of executed by the first query.

To fix phantom read problem we can use serializable and snapshot isolation levels. When we use the serializable isolation level it would apply the range lock. Means whatever range you have given in first transaction lock is applied to that range by doing so second transaction not able to insert data between this range.

Snapshot isolation level

Like a serializable isolation level snapshot also does not have any concurrency side effects.

What is the difference between serializable and Snapshot isolation level?

Serialization isolation level acquires it means during the transaction resources in our case tables acquires a lock for that current transaction. So acquiring the lock it reduces concurrency reduction.

Snapshot doesn’t acquire a lock it maintains versioning in TempDB. Since snapshot does not acquire lock resources it significantly increases the number of concurrent transactions while providing the same level of data consistency as serializable isolation does.

See below the image in that we use a serializable isolation level that acquires a lock so that we are able to see the execution of a query in progress.

Snap Shot Isolation Level

Now in the below example, we set a database for allowing snapshot isolation. For that, we need to execute the below command.

alter database temp
set allow_snapshot_isolation on

Doing so our database tempdb is allowed for snapshot transaction than on one window we use serialization isolation level and on the second we use snapshot isolation level. When we run both transactions we are able to see the snapshot isolation level transaction completed while serialization is in progress and after completing both transactions we see one window has updated data and others will have previous data. First

Isolatio level

Now after completing both transactions

  1. snapshot isolation never blocks the transaction.
  2. It will display that data which is before another transaction processing
  3. It means that snapshot isolation never locks resources and other transaction able read the data
  4. But here one transaction is updating the data another is reading that data so it’s ok
  5. When both transactions updating same data then transaction blocks and this blocks until the first transaction complete and then transaction 2 throws error lost update why because preventing overwriting the data and it fails and error is transaction is aborted you can’t use snapshot isolation level update, delete insert that had been deleted or modified by another transaction.
  6. If you want to complete the second transaction you need to rerun that transaction and data is modified successfully.

Read Committed Snapshot Isolation Level

It is not a different isolation level. It is an only different way of implementing Read committed isolation level. one problem in that if anyone transaction is updating the record while reading the same data by another transaction is blocked.

Difference between Snapshot isolation level and Read Committed Snapshot isolation level.

Snapshot IsolationRead Committed Snapshot isolation level
It is vulnerable to update conflictsNo update conflicts here
Can not use with a distributed transactionIt can work with a distributed transaction
Provides transaction-level read consistencyIt provides statement-level read consistency

My Other Site: https://sagarjaybhay.net

Cursor In RDBMS By Sagar Jaybhay 2020

In this article we will understand cursor in rdbms in our case we show example on SQL Server By Sagar Jaybhay. Also we will understand Merge statement in SQL Server and rerunnable SQL scripts and How to create a stored procedure with an optional parameter?

Cursors In RDBMS

In a relational database management system takes into consideration then it would process the data in sets inefficient manner.

But when you have a need to process the data row by row basis then the cursor is the choice. The cursor is very bad at performance and it should be avoided and also you can replace the cursor with join.

Different Types of Cursors In RDBMS

Their are four types of cursors in rdbms which are listed below

  1. Forward only
  2. Static
  3. Keyset
  4. Dynamic

The cursor is loop through each record one by one so that’s why it’s performance is not good.

declare @empid int
declare @deptid int
declare @fullname varchar(200)

declare empcurose cursor for 
select EmpID,full_name,DepartmentID from Employee

open empcurose

fetch next from empcurose into @empid,@fullname,@deptid

while(@@FETCH_STATUS=0)
begin

print 'EmpID '+cast(@empid as varchar(10))+ ' Name '+cast(@fullname as varchar(100)) + ' deptid '+cast(@deptid as varchar(100))
fetch next from empcurose into @empid,@fullname,@deptid
end
close empcurose
deallocate empcurose
cursor in rdbms
deallocate empcurose

This line is used to deallocate all resources which are allocated for that cursor.

What is rerunnable SQL scripts?

A re-runnable SQL script is a script that runs multiple times on the machine will not throw any kind of error.

For example, if you use create table statement to create a table then use if not exist in create a statement so it will not throw an error.

How to create a stored procedure with an optional parameter?

create procedure searchemployee
@name varchar(10)=null,
@deptid int=null,
@gender varchar(10)=null
as
begin

if(@name is not null)
print 'i am in name '+cast(@name as varchar(20))
select * from tblEmp where [name]=@name;
return;

if(@deptid is not null)
print 'i am in deptid '+cast(@deptid as varchar(20))
select * from tblEmp where deptid=@deptid;
return;

if(@gender is not null)
print 'i am in gender '+cast(@gender as varchar(20))
select * from tblEmp where geneder=@gender;
return;
print 'i m here '+cast(@gender as varchar(20))+' '+cast(@deptid as varchar(20)) +' '+cast(@name as varchar(20))
select * from tblEmp

end

execute searchemployee @deptid=2

Simply pass default values to stored procedure variables.

Merge statement In SQL server

Merge statement is introduced in SQL server 2008 it allows to insert, update, deletes in one statement. It means there is no need to use multiple statements for insert update and delete.

In this, if you want to use merge statement you need to 2 tables

  1. Source table– it contains the changes that need to apply to the target table.
  2. Target table– this is the table that requires changes insert, update, delete.

Merge statement joins the target table to source table by using a common column in both tables based on how you match up we perform insert, update and delete.


Transaction Link: https://www.codementor.io/@sagarjaybhay18091988/transaction-in-sql-server-155l4qr7f4


Subqueries & Correlated Subqueries SQL Server 2020

In this article Sagar Jaybhay explain how to write Subqueries and Correlated Subqueries in SQL Server and What to choose for Performance Subquery or Join?

Subqueries In SQL Server:

Subqueries are enclosed in parenthesis. Subquery also called an inner query and the query which enclosed that inner query is called an outer query. Many times subqueries can be replaced with joins.

select * from Employee where DepartmentID not in (select distinct DepartmentID  from Department)

Another example

select Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID) from Department as d;

The above query is an example of use subquery in the select list. The above result can be achieved using join also see below query

select d.Department_Name,COUNT(e.empid) as empcount from Department d
join Employee e on e.DepartmentID=d.DepartmentID
group by d.Department_Name
order by empcount;

According to MSDN, you can nested up to 32 levels.

Columns present in subqueries can not be used in the outer select list of a query.

Correlated Subqueries:

If our subquery depends on the outer query for its value then it is called Correlated subqueries. It means subquery depends on outer subquery/ Correlated subqueries are executed for every single row executed by outer subqueries.

A correlated subquery can be executed independently

select distinct Department_Name,(select count(*) from Employee where DepartmentID=d.DepartmentID group by DepartmentID) as empcount from Department as d  order by empcount;

What to choose for Performance Subquery or Join?

According to MSDN, there is no big difference between queries that use sub-queries and joins.

But in some cases, we need to check the performance and Join produces better performance because the nested query is must be processed for each result of the outer query. In such cases, JOIN will perform better.

In general, JOIN works faster as compared to subqueries but in reality, it will depend on the execution plan generated by SQL Server. If the SQL server generates the same execution plan then you will get the same result.

Transaction Link: https://www.codementor.io/@sagarjaybhay18091988/transaction-in-sql-server-155l4qr7f4

You Should Know Use Of This Terms Angular 9

In this article you will understand importance of mainly used terms in Angular 9 by Sagar Jaybhay. You will understand Components, Modules, How angular 9 works?

Angular 9

Angular is binding frameworks.  It helps us to bind view and model. In angular, you can have multiple apps inside the src folder.

Binding Framework Angular 9

Components

In angular, the binding code which binds the UI means our Html and Model is component. Here Component is the part of angular, which receives data from the UI and Sends data back to UI.

COmponent In Angular 9

In an enterprise application, you can have a lot of views, lots of components and lots of models.

Modules

If you collect different component and group them into one repo is called modules and these component are generally belong to the same modules

In by default angular generated boilerplate code which doesn’t contain model but as per requirement we want that and we created in our application. The naming convention for this is app.model.ts.

Component In Details In Angular 9:

Below is the code for a component which is generated by default by using angular cli.

import  Component  from '@angular/core';

@Component(
  selector: 'app-root',
  templateUrl: './app.component.html',
  styleUrls: ['./app.component.css']
)
export class AppComponent 
  title = 'customerapplication';

In angular, we need to create a class first and after that, we use @component which is decorater and other programming languages are termed as an attribute, data annotation.

Now take each term in @Component.

  1. selector: Placed Html in that location
  2. templateUrl: it means whatever data or fields are present in that component is binded with this Html file which is given in this property.
  3. styleUrls: this means for above Html use this style sheet which is present in this location.

In our component class which is AppComponent in our case, we have given export keyword before this. If we didn’t give the export keyword then it is not accessible or visible outside of that .ts file.

Points to remember:

The connection between view and model is done by component.

Module In Angular 9:

The module is used to group related components under the hood. Below is code for a module which is auto-generated by angular cli.

import  BrowserModule  from '@angular/platform-browser';
import  NgModule  from '@angular/core';

import  AppRoutingModule  from './app-routing.module';
import  AppComponent  from './app.component';

@NgModule(
  declarations: [
    AppComponent
  ],
  imports: [
    BrowserModule,
    AppRoutingModule
  ],
  providers: [],
  bootstrap: [AppComponent]
)
export class AppModule  

So if you see above code AppModule is a class and it is decorated by @NgModule decorator. So we learn every property in this decorator.

  1. Declarations: this is used to declare the component which is used under this module. If you have comp1, comp2 like component then these are declared inside these declarations. When you use this component inside the declaration first you need to import them.
  2. Imports: it is used to import mainly supporting modules
  3. Providers: A provider is an instruction to the Dependency Injection system on how to obtain a value for a dependency.
  4. Bootstrap: this is used to give a starting component or root component where the application starts.

If you consider an enterprise application is having lots of modules and for a startup, we need only one so


GitHub Project Link: https://github.com/Sagar-Jaybhay/angular9