Advertisements

Create and Connect Azure SQL database in the Azure portal


Introduction

This article explains how to create and connect Azure SQL database in the Azure portal.

SQL database

First, we need to log into the Azure Portal with our Azure credentials. Then we need to create an Azure SQL database in the Azure portal.

Click on “Create a resource” on the left side menu and it will open an “Azure Marketplace”. There, we can see the list of services. Click “Databases” then click on the “SQL Database”.

SQL database

SQL database

Create a SQL database

After clicking the “SQL Database”, it will open another section. There, we need to provide the basic information about our database like Database name, Storage Space, Server name, etc.

Database Creation

Database Creation

Database name : The valid name of our SQL Database ( We have given the Database name as “AzureSQLDB” ).

Subscription : We can select our Azure subscription for SQL Database creation.

Resource group : We can create a new resource group or choose from an existing one ( We have selected our existing resource group as “WebAppAzureSql” ).

Select source : We select Blank database ( This will create a blank database ). The following Select source categories are available in the SQL Database.

  1. Blank database – This will create a blank database.
  2. AdventureWorksLT This will generate an AdventureWorksLT sample schema.
  3. Backup : Create a new database from an existing backup.

Server

Under the server, we need to configure required settings.

  1. Server name : Any globally unique name we can give which will store our database information.
  2. Server admin login : Create our server admin name for future access.
  3. Password & Confirm Password : Create our server admin password for future access.
  4. Location : Choose an available location that will be more suitable for our requirement.

Once we enter all the “Server” details then Click on “Select”.

Pricing tier : Select an appropriate pricing tier for our requirement.

Collation : Create a name for the Collation (Collation defines the rules that sort and compare the data and cannot be changed after the database creation ).

Click on “Create” to provision the database.

Database provisioned successfully!! Go to the Dashboard and Click on the Sql Database ( “AzureSqlDB” ).

DashBoard

DashBoard

Now open the details page of SQL database ( “Overview” ). Here, we can see the Server name and other created details. We can access the SQL Database through this Server name in Azure Portal & Local MSSQL.

Create a server-level firewall rule

We need to setup a firewall rule for accessing our Azure Sql database in Azure Portal. So the SQL Database service creates a firewall at the server-level that prevents external applications and tools from connecting to the server. If we not set up any firewall rule in Azure SQL database portal, then we will get the following error.

Setup Firewall Rule

Click on the “Set server Firewall” in the Overview Section.

The “Client IP address” automatically fetched into the Firewall Setting. We just copy the IP Address and Add the START IP & END IP Section (This will add based on more than one IP address ). Set a “RULE NAME” of our Firewall. Once it’s all done then the information saves automatically into the firewall rule section.

SQL Database Login in Azure Portal

Click on the “Query editor ( preview)” and Click on the “Login” and it will display a Login portal for accessing our Azure SQL Database in Azure. So give or appropriate database credential into it. Once it’s done Click on “OK” button.

Output 1

It will open our Azure Sql Database Query Editor window with Database details.

Output 2

Accessing through our Local “Microsoft SQL Server Management Studio”.

Reference

See Also

You can download other ASP.NET Core & Azure source codes from MSDN Code, using the link, mentioned below.

Summary

In this article, we are going to create and connect Azure SQL database to the Azure portal. I hope this article is useful for all Azure beginners & experts.

Advertisements

Ranking Function in Microsoft SQL Server


Introduction

In this article, I will explain the use of the Ranking function in Microsoft SQL Server. In common use, Rank shows the position and courage of a person in a real life scenario. So here, I will also give some examples related to all Ranking functions in Microsoft SQL Server.

Table In Microsoft SQL Server

My table contains bulk amount of data and it’s related to student marks list. So, I am going to apply Microsoft SQL Server Ranking function in the following Table “Table_MarkList”.

Ranking Function

Ranking Function Main Table

Sql Query

USE [Test]
GO
/****** Object:  Table [dbo].[Table_MarkList]    Script Date: 08/03/2016 12:06:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Table_MarkList](
	[Id] [int] IDENTITY(1000,1) NOT NULL,
	[Name] [varchar](50) NULL,
	[Marks] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Table_MarkList] ON
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1000, N'Vimal', 85)
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1001, N'Nikhil', 85)
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1002, N'Prasanth', 84)
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1003, N'Manaf', 83)
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1004, N'Naveen', 82)
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1005, N'Midhun', 80)
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1006, N'Vijeth', 95)
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1007, N'Dileep', 97)
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1008, N'Ashiq', 92)
INSERT [dbo].[Table_MarkList] ([Id], [Name], [Marks]) VALUES (1009, N'Rajeesh', 75)
SET IDENTITY_INSERT [dbo].[Table_MarkList] OFF

Ranking Function in Microsoft SQL Server

The following are the Ranking functions in Microsoft SQL Server.

  • RANK()
  • DENSE_RANK()
  • Row_Number()
  • NTILE()

RANK()

One of the important Ranking functions in Microsoft SQL Server is RANK(). Rank() Ranking functions are non deterministic and return a ranking value for each row in a partition. Some rows return same rank as other rows, depending on the same values in columns.

The following result will be displayed after the RANK() function is applied in “Table_MarkList”. So, the RANK() function is sorting the mark’s column values in descending order, in the following table. The Rank() function will apply the same rank for common values in the table. The following table contains the same marks for “Vimal” & “Nikhil” and after applying Rank() function, it shows both of them with the same rank as “4”. So, the next rank will show as “6” because internally it counts one 4 as 5.  

Rank Function

RANK() Function Sql Query

select Name,Marks, RANK() over(order by Marks desc) As 'Rank' From dbo.Table_MarkList

DENSE_RANK()

Another important Ranking function in Microsoft SQL Server is DENSE_RANK(). It differs from RANK() function and it also returns the same rank as other rows, depending on the same values in columns. If the same rank happens, then the next rank will be +1 added to the last rank.

The DENSE_RANK() function sorts the Marks column values in descending order in the following table “Table_MarkList”. The DENSE_RANK() function will apply the same rank for common values in the table “Table_MarkList”. So, the following table contains same marks for “Vimal” & “Nikhil”, after applying DENSE_RANK() function, it has assigned these both the same rank as “4”. So, the next rank will start from “5”. It will maintain the rank order of the RANK() function.

Dense Rank In MSSQL

Dense Rank In MSSQL

DENSE_RANK() Function Sql Query

 select Name,Marks, DENSE_RANK() over(order by Marks desc) As 'Dense Rank' From dbo.Table_MarkList 

Row_Number()

One of the important Ranking functions in Microsoft SQL Server is Row_Number(). We can use this Ranking function for pagination purposes.

The Row_Number() function is sorting the Marks column values in descending order in the following table “Table_MarkList”. The Row_Number() function will apply the same rank for common values in the table “Table_MarkList”. The following table contains the same marks for “Vimal” & “Nikhil”. After applying Row_Number() function, it is showing them both the rank as “4 & 5”. So, it gives the result as a sequence, one by one,  and it never minds the common values in the “Marks” Column. Now the results are showing it accurately maintaining the Row Number in “Table_MarkList” table.

Row Number In MSSQL

Row Number In MSSQL

Row_Number() Function Sql Query

select Name,Marks, Row_Number() over(order by Marks desc) As 'Row Number' From dbo.Table_MarkList

NTILE()

The important Ranking function in Microsoft SQL Server is NTILE(). We can use this Ranking function for grouping purposes.

NTILE(3) makes 3 groups and splits the number of row values in  3 equal groups. For example – If there are 9 row values in a table, it will split them into 3 equal row values in a table.

Simple! But what will happen on 10 row values in NTILE(3) ?. It’s so simple. NTILE() will split them too in 3 groups but the first group will have 1 extra row value than the other row values in a table.

Quartile Function In MSSQL

Ntile Function In MSSQL

NTILE() Function Sql Query

select Name,Marks, NTILE(3) over(order by Marks desc) As 'Quartile' From dbo.Table_MarkList

Reference

Ranking Functions (Transact-SQL)

Summary

We learned the types of Ranking functions in Microsoft SQL Server and I hope you liked this article. Please share your valuable suggestions and feedback.

How to resolve “Saving changes is not permitted” Error In Sql


Introduction

In this article you will learn how to resolve “Saving changes is not permitted” in SQL. The following table  “Table_1” contain the student mark information. I want to change that column name “Subject” to “Mark”.

Step 1 : I have one table “Table_1” and it contain the student mark information.

1

Step 2 : Design View of “Table_1”.

2

Step 3 : Now I changed the “Subject” name as “Mark” and “varchar(50)” as “int”. It is throwing the exception “Saving changes is not permitted”.

3

Step 4 : Go to Tools, click Options… , Expand Designers , then click Table and Database Designers,

4

Step 5 : Remove checked in “Prevent saving changes that require table re-creation.” Click Ok.

5

Step 6 : Click Ok.

6

Step 7 : Out Put.

7

Summary

We learned how to resolve “Saving changes is not permitted” in SQL. I hope this article will be useful for beginners.

 

Copy Or Generate SQL Database Content To Multiple One


Introduction

In this article you will learn how to copy or generate SQL database content to multiple one.

Step 1: I have two database “Test1” & “Test2”. Test2 database is empty,  so we want to copy Test1 database to Test2 based on your requirement like MS SQL Server 2008, 2012, etc.

1

Step 2: Generate the “Test1” database script based on the following steps:

  • Right click “Test1” database.
  • Click Tasks, then Generate Scripts.

2

Step 3 : Click Next..

3

Step 4 : Select “Select specific database objects” and check all the tables present in the database.

4

Step 5 : Click “Advanced“, Open New Pop Up Window, General, Script for Server Version, click SQL Server2008 R2 ( Select based on your requirement ).

5

Step 6 : Click “Advanced“, Open New Pop Up Window, General, Types of data to script, then select Schema and data ( Select based on your requirement ).

6

Step 7 : Select the save location for generated script.

7

Step 8 : Click Next.

8

Step 9 : Click Finish.

9

Step 10 : Change database name and update the generated scripts.

10

Step 11 : Now copy “Test1” into “Test2”.

11

Summary

We learned how to copy or generate SQL database content to multiple one. I hope this article will be useful for beginners.

 

 

Replace Function in Microsoft Sql Server


Introduction

Mostly everyone is familiar with C# replace function. In this article I will explain the use of replace function in Microsoft SQL Server. A simple function to replace bulk amount of data in the database. Here I will also give some examples.

Table

My table contains bulk amount of data and some of the values are wrongly entered  into the database. So I want to change those particular values into the table using a single query.

The given table contains incorrect address in the “Address1” column like httpp://www.c-sharpcorner.com/, so http is appended with an extra p (“httpp“). So we can update those values in a single query. How ? Microsoft SQL Server provides Replace function to replace particular data.

1

Sql Query :

The following code will replace that particular data.

update dbo.Tbl_Replace set Address1 = Replace(Address1,'httpp','http')

We can directly use the update method in the above query. Why we are using replace function in SQL?

Because when you are trying to update bulk amount of data you can use the replace function. It will consume the time of update, since you don’t have to update each and every address separately.

update dbo.Tbl_Replace set Address1 = 'http://www.c-sharpcorner.com/' where Id=1  

The above update code only changes the particular Id value.

Example 1

Replace Function

The Sql Replace function replacing “httpp” to “http”.

update dbo.Tbl_Replace set Address1 = Replace(Address1,'httpp','http')  

Output

The Replace function replaced the appended value using a single query.

2

Example 2

Table

How to put space between “BlogContent” like “Blog Content” without affecting whole content in the database.

4

Replace Function

You can use a single space.Like ‘Content’ to ‘ Content’

update dbo.Tbl_Replace set Details = Replace(Details,'Content',' Content') 

Output

5

Summary

We learned how to use a replace function in Microsoft Sql.I hope this article is useful for all .NET programmers.

%d bloggers like this: