Introduction:
Azure SQL Database is a completely controlled platform as a service (PaaS) database engine that can control most of the database management tasks such as improving, repairing, assisting, and observing with no user participation. Azure SQL database always runs on the latest version of the SQL server database. With the assistance of the Azure SQL database, we can generate a highly accessible and high-execution data storage layer for the applications and infusion in Azure.
Why Azure SQL database?
When data in a SQL database table changes, the Azure Functions SQL trigger will call an Azure Function, opening up new event-driven SQL database scenarios.
The Azure SQL bindings for Azure Functions integration allow for faster access to data in a SQL database using Azure Functions.
All of the languages that Azure Functions supports have Azure SQL bindings for Azure Functions that are in public preview, along with the Azure SQL trigger for Azure Functions for C#.
Writing, compiling, debugging, and testing code—typically on a local workstation or development environment before sharing it with someone else—represents the “inner loop” in software development workflows. The “outer loop,” in contrast, begins as soon as the code is checked into a source control system, from which CI/CD pipelines can control all necessary steps (such as testing and validation) to publish code artifacts and configuration to production systems.
The new Azure SQL Database emulator allows developers to publish and run their database projects locally on a containerized, cross-platform emulator that provides close fidelity with the Azure SQL Database public service while enabling a full offline experience for running and testing their databases.The local development experience for Azure SQL Database also includes a number of extensions for Visual Studio Code and Azure Data Studio.
Developers can create database projects, construct and validate schemas and other objects against Azure SQL Database syntax and capabilities with the help of the SQL Database Project extension. The support for publishing these projects to the local emulator for additional testing and validation as well as, after passing, to a new or existing logical server in the public service of Azure SQL Database.
A genuine “database as code” experience is provided for developing, implementing, and managing databases as part of the entire end-to-end solution when database projects are verified in source control systems, such as GitHub repose, alongside other code artifacts and projects. In order to publish a new database or a schema change to a test or production environment in Azure SQL Database’s public service, developers and Dev-ops teams can then take advantage of tools like GitHub Actions.
Creating an Azure SQL Database using Azure portal:
search for SQL database and then click on create.
Fill the details.
Create a new server or you can choose an existing server.
Select the pricing tier of your choice, then click apply.
click Review + create and create your SQL database.
Database is created, you can click on go to resource and configure additional settings.
You can configure firewall, add IP’s that could connect with the SQL server. You can get the connection string by clicking “connection string”.
Copy the connection string and paste in appsettings.json.
Now you can run your project which is connected with azure SQL database.
SQL Quires:~
- Commands to create table:
create table customers (
id int auto_increment primary key,
first_name varchar(30),
last_name varchar(30),
gender enum(‘M’,’F’),
phone_number varchar(11)
);
create table provider(
id int primary key,
product_id int,
customer_id int,
order_time datetime,
foreign key (product_id) references products(id),
foreign key (customer_id) references customers(id) );
- Commands to Deleting table:
drop table provider;
- Commands to show available table list in a database:
show tables;
- DQL statement selection:~
select * from orders;
- Commands to Add new column:
alter table provider
add column service_count int;
- Commands to Delete column:
alter table products
drop column order_time;
- Commands to Commands to (dropping the table and recreate newly)
truncate table products;
- Commands to add primary key:
alter table provider
add primary key (id);
- Commands to remove primary key:
alter table provider
drop primary key;
- Commands to add foreign key:
alter table orders
add constraint FK_customerID
foreign key (customer_id) references customers(id);
- Commands to remove foreign key:
alter table orders
drop foreign key FK_customerID;
- Commands to add unique constraint to a column:
alter table provider
add constraint u_serviceCount unique (coffee_seed_count);
- Commands to remove unique constraint:
alter table provider
drop constraint u_serviceCount;
- To Change column name:
alter table provider
change `customer_service_count` `service_count` int;
- Commands to modify column datatype:
alter table provider
modify service_count int;
Data Manipulation Language:~
- Commands to Insert data in row:
insert into provider(id)
values (1);
- Commands to Update data:
update provider
set id=10
where id = 1;
Note: by default primary key column only can be used for where clause
- To change that set sql safe updates to false
set sql_safe_updates=0;
- Commands to Delete data:
delete from provider
where id=10;
Data Query Language
- IN clause
select * from customers
where last_name in (‘Adams,’John’);
- NOT IN clause
select * from customers
where last_name not in (‘Adams’,’John’);
- Between clause
select customer_id, order_time from orders
where order_time between ‘2017-01-01’ and ‘2017-01-10’;
- Like clause
select * from customers
where last_name like “%e%”;
- order by clause
select * from customers
order by last_name asc; — asc – ascending | desc – descending
- distinct clause (hides duplicate values)
select distinct first_name from customers
where last_name like “%e%”;
-
- Limit
select * from customers
limit 5 offset 3;
- Alias
select coffee_origin as country from products;
Joins:
- inner join:
select p.name, o.order_time from orders o
join products p on o.product_id = p.id; — join or inner join
- left join
select * from orders o
left join products p on o.product_id = p.id;
- right join
select * from orders o
right join products p on o.product_id = p.id;
aggregate function:
- count
select count(first_name) from customers
where first_name is null;
- sum
select sum(id) from customers;
- max and min
select max(id) from customers;
select min(id) from customers;
- average
select avg(id) from customers;
- grouping
select first_name, count(id) from customers
group by first_name;
- having
select first_name, count(id) from customers
group by first_name
Advantages of Azure SQL database:
- High availability
- Automatic tuning is a performance tuning tool based on autonomous artificial intelligence that resolves any performance-related issues without user involvement.
- Business continuity with outstanding data backup and restoration capacities.
- You may store data backups for up to 10 years thanks to long-term backup retention.
- You can virtually generate readable secondary databases in several data center locations using Geo-replication.
- Businesses and customers have the ability to scale database resources up or down thanks to the scaling database resources functionality.
Conclusion:
A database as a service paradigm is provided by both managed instances and the Azure SQL database. By utilizing the most recent version of the SQL server that is stable, SQL-managed instances and Azure SQL databases share the same code. In azure SQL databases and managed instances, the majority of conventional SQL languages, data management, and query processing are identical.