SQL Server
SQL Server is a relational database management system created by Microsoft and uses ANSI SQL as standard query language.SQL, or Structured Query Language. It uses SQL statements to store, manipulate, retrieve, and manage data in a database.
Types of SQL statements
In SQL Server, SQL Statements are divided into four categories,
- DML(Data Manipulation Language) - Select, Insert, Update, Delete
- DDL(Data Definition Language) - Create, Drop, Alter
- DCL(Data Control Language) - Grant, Revoke
- TCL(Transaction Control Language) - Begin, Tran, Commit, Rollback.
Introduction
In this article, we will learn how to use different SQL statements to create objects such as tables, views, stored procedures, and functions(system and user-defined) in a database and how to use the objects to store and manipulate data.
This step-by-step guide will explain how to use the SQL Server Management Studio tool to execute various SQL statements.
Step 1
Assuming you've SQL Server 2012 or higher version.
Open Sql Server Management Studio. After connecting, open a New Query Window.
![]()
Step 2
In the New Query Window, start writing and executing SQL queries. Before anything else, we must create a database. To create one, follow the following steps.
Database
A database is an organized collection of data and objects (tables, views, procedures, functions, etc.), generally stored and accessed electronically from a computer system.
Create a database,
Create database LearningBasicSQL
Tables in SQL Server
Tables are database objects that contain all the data in a database. In tables, data is logically organized in a row-and-column format similar to a spreadsheet. Each row represents a unique record, and each column represents a field in the record. Each row in a relational database is known as a tuple.
To find a detailed article about the Tables in SQL, please go through this: Tables in SQL
Create a table,
use LearningBasicSQL
create table tbl_Employee(
EmpID int primary key not null identity(1,1),
EmpName varchar(50),
City varchar(20)
)
Insert values in a table,
insert into tbl_Employee values('Shilpa','Kolkata'),('Sayantan','Howrah')
Get rows of a table,
select * from tbl_Employee
Insert values in a table using Insert Into Select statement,
insert into tbl_Employee (EmpName,City)
select 'Anuja','Siliguri'
Delete a row from a table,
delete from tbl_Employee where EmpID=4
Truncate a table,
truncate table tbl_Employee
Drop a table,
drop table tbl_Employee
Stored Procedure in SQL
A stored procedure is a subroutine available to applications that access a relational database management system. Such procedures are stored in the database data dictionary. To find a detailed article about the stored procedure, please go through this: Stored Procedure. The following queries will help you create, update, and delete store procedures.
Create a stored procedure for add/update,
create procedure sp_AddEditEmployee (
@EmpId int=0,
@EmpName varchar(50),
@City varchar(20),
@Mode int
)
as
begin
if @Mode=0
insert into tbl_Employee values(@EmpName,@City )
if @Mode=1
update tbl_Employee set EmpName=@EmpName,City=@City where EmpID=@EmpId
end
In the above example, I have used a variable @Mode to add and update a table.Considering @Mode=0 for Addition and @Mode=1 for the update. The advantage of the above procedure is that we do not have to create two separate procedures for add and update. We can manage both operations using a single procedure.
Exec Procedure For Add,
exec sp_AddEditEmployee 0,'Amit','Nadia',0
select * from tbl_Employee
Exec Procedure for Update,
exec sp_AddEditEmployee 1,'Shilpa','Darjeeling',1
select * from tbl_Employee
Drop a procedure,
drop procedure sp_AddEditEmployee
View in SQL Server
Create a virtual table in which the query defines contents (columns and rows). Use this statement to create a view of the data in one or more tables in the database. A view contains a set of rows and columns.
To find a detailed article about Views in SQL, please go through this: Views in SQL
Create a view,
create view vw_Employee
as
select EmpID,EmpName from tbl_Employee
Display a View,
select * from vw_Employee
Drop a View,
drop view vw_Employee
Functions in SQL Server
Creates a user-defined function in SQL Server and Azure SQL Database. A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result as a value. The return value can be a scalar (single) value or a table.
To find a detailed article about Functions in SQL, please go through this: Function in SQL
Functions can be broadly classified into two types,
- System Function in SQL Server
The functions provided by SQL Server and cannot be modified commonly used to solve complex calculations—for example, Mathematical functions, date and time functions, and cursor functions.
- UUser-DefinedFunctions in SQL Server
SQL Server provides the user the facility to create functions stored in the database and help solve calculations quickly. The UDF(user-defined functions)are further classified into Scalar and Table Valued functions.
- Scalar Functions in SQL Server
SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of a value. The return value can either be a single scalar value or a result set
.In the following example, we will learn how to create a scalar UDF. The function defined in the example helps a user identify whether a number is odd or even. The input provided is 2.
create function CheckOdd_EvenNo(@input int)
returns varchar(10)
as
begin
declare @output varchar(10)
if @input%2=0
set @output='Even'
else
set @output='Odd'
return @output
end
To execute the function, use the statement as shown below.
select [dbo].[CheckOdd_EvenNo](2)
Table-valued functions in SQL Server
A table-valued function returns a single rowset (unlike stored procedures, which can return multiple result shapes). Because the return type of a table-valued function is Table, you can use a table-valued function anywhere in SQL that you can use a table.
In the following example, we will learn how to create a table-valued function. I have created a UDF to help us calculate the first 'N' natural numbers.
create function First_N_Natural_No (@item int)
returns @table table(itemValue varchar(20))
as
begin
declare @count int=1
while @count<=@item
begin
insert into @table values(@count)
set @count=@count+1
end
return
end
Use the following query to get the first 10 natural numbers. Since the UDF returns a table, we must use a select statement to get the result. The input provided is 10.
select * from First_N_Natural_No(10)
Summary
This article taught us how to create, insert, and update values, drop a simple table, and view using SQL Queries. Also, we got a brief idea about Stored Procedures and functions. Now we can create procedures and functions and use these objects to easily add and update a table and solve complex calculations.
In the next article, we will learn when to use procedures and functions and the advanced topics.
More on SQL Queries