What is Stored Procedure

What is stored procedure

• They are logically grouped SQL or Pl/SQL commands that performs some task.
• When stored procedure is create or alter it compiled and saved in database
• Compiled code is execute when stored procedure is call
• Stored procedure contains input and output parameter
• Stored procedure can contain DML and DQL statement.
• Transaction can be handled in stored procedure

Syntax for create procedure

CREATE PROCEDURE [dbo].[CreateNewPost]
-- Add the parameters for the stored procedure here
<Parameter name >, <Data type>,
<Parameter name >, <Data type> {input, output}
AS
BEGIN
<Queries>
Exception
<exception part>
End;

Example of insert record with output parameter

CREATE PROCEDURE [dbo].[InsertData]
@Title nvarchar(400),
@UserId int,
@Output nvarchar(100) output
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Post
(
Title,
UserId,
)
VALUES
(
@Title,
@UserId,
)
set @Output = @@IDENTITY

END

what are the difference between temp table and table variable in sql server

what are the difference between temp table and table variable in sql server?

• Declaration 

• Temp table is create by # sign with create keyword
• example: create table #TMPTABLE(id int, FName varchar(50),LName varchar(50))
• Table variable is declare by @ sign with 
• example: declare @TableVar table (id int,name varchar(50))

• Scope of use

• Temp table can be use by multiple user
• Table variable can use by current user only

• Use of DDL command

• Temp table allow use of all DDL commands on it.
• Table variable do not allow DDL command on it

• Index

• Temp table allow to create alter and drop table index
• Table variable do not allow to create index but it only allow to create cluster index only.

• Transaction

• Temp table allows to begin rollback and commit transaction
• Any Transaction command is not possible with table variable.

• Storage

• Temp table is physically created in tempdb
• Table variable is create in memory

Example Temp table

create table #TMPTABLE(id int, FName varchar(50),LName varchar(50))

insert into #TMPTABLE values (1,'jayendra','jayendra')

select * from #TMPTABLE

Example Table variable

declare @TableVar table (id int,fname varchar(50),lname varchar(50))

insert into @TableVar values (2,'jayendra','jayendra')

select * from @TableVar

varchar VS nvarchar

What are the difference between VARCHAR AND NVARCHAR in sql server?

• Defination

• VARCHAR is non-unicode character type with variable length
• NVARCHAR is unicode character type with variable length

• Size

• VARCHAR occupies 1 byte per character.
• for example: DECLARE @NAME VARCHAR(10)='JAYENDRA'
• occupies 8 bytes
• NVARCHAR occupies 2 byte per character.
• for example: DECLARE @NAME NVARCHAR(10)='JAYENDRA'
• occupies 16 bytes

• Usage

• use VARCHAR type when your column have only non-unicode character data only like any character which has ascii value 0 to 255
• use NVARCHAR type when you table column contains unicode character data like any other language character ex. gujarati or hindi

• Storage size

• Occupies number of bytes equal to the number of Characters entered and 2 bytes extra for defining offset.
• Occupies number of bytes equal to the number of Characters entered * 2 (twice of character) and 2 bytes extra for defining offset.

Cursor

Cursor

• When user executes any query, block of memory will be provided in database.
• This block of memory is provided for its internal processing and its private to database engine.
• It is called cursor.
• Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type
• When user defines a block of memory to execute a query through programming it is said to be explicit cursor.
• To define explicit cursor, user have to follow below steps.
1. Declare cursor
2. Open cursor
3. Fatch cursor
4. Close Cursor
5. Deallocate Cursor

Example

DECLARE @fname varchar(50)
DECLARE cur_emp CURSOR
static FOR
SELECT fname from emp
OPEN cur_emp
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM cur_emp INTO @fname
WHILE @@Fetch_status = 0
BEGIN
PRINT ' Name : '+@fname
FETCH NEXT FROM cur_emp INTO @fname
END
END
CLOSE cur_emp
DEALLOCATE cur_emp

Types of Cursors

• Static Cursors - A static cursor populates the result set at the time of cursor creation and query result is cached for the lifetime of the cursor.
• Dynamic Cursors - A dynamic cursor allows you to see the data updation, deletion and insertion in the data source while the cursor is open. Hence a dynamic cursor is sensitive to any changes to the data source and supports update, delete operations.

sql join

Joins

To get output from multiple tables we can use joins.
Using join, we can manipulate on two tables using single query.
Join can be applied if both tables contains same data type and width for column.

There are three types of joins

1. Inner
2. Outer (left, right)
3. Cross

Inner join

It is also called equi join.
It compares two columns from two tables using = operator.

Syntax:
select <columnName1>,<columnName2> from
<tableName1>Inner join<tableName2> on
<tableName1>.<columnName1> = <tableName2.>.<columnName2>;

Example:
select member_master.Name, member_master.Age, employee_master.salary
from member_master
inner join employee_master on
member_master.ID = employee_master.ID;

Outer Join

Outer join can be better illustrated using its types.

1. Left outer join

left outer join will take all the rows from left side table.
If there will be more rows in right side table, it will be truncated and if there will be less rows, null will be added as data.

syntax:
select <columnName1>,<columnName2>
from <tableName1> left join <tableName2> on
<tableName1>.<columnName> = <tableName2>.<columnName>;

2. Right Outer Join

right outer join is like left join but will take all rows from right side table.

Syntax:
select <columnName1>,<columnName2>
from <tableName1>right join <tableName2> on
<tableName1>.<columnName1> = <tableName2>.<columnName2>;

Cross Join

When we apply cross join, it will take first row from left side table and put it with each right side rows.
For example, table1 having 5 rows and table2 having 3rows
then first row from table1 will be placed with 3 rows of table2
and then 2nd row will be placed with 3 rows again and so on.

SQL Server Views with Example

SQL Server Views

Once table is created and data is populated we may need to hide some data from some users.
This leads to data security concept.
For this, we can create views.
View is sub set of database table, it means it is type of table but containing required part of data only.
It is virtual table.

Creating view:

Syntax:
create view <viewName> as select <columnName1>,<columnName2> from <tableName>;
Example:
create view Members as select Name,Age,Gender,Address from Employee_Master;

To select data from view, one can write as,
select <columnName1>,<columnName2> from <viewName>;

To destroy a view, one can write as,
drop view <viewName>;

SQL Indexer

Indexer

Indexer is also same as index in  sql server table

Indexers are of two type

1. Simple
2. Composite

An indexer created on single column of table is simple indexer where as indexer created on more
than one column is called composite indexer.

Simple Indexer
Syntax:
create index <indexName> on <tableName>(<columnName>);
Example:
create index Emp_Id_idx on Employee_Master(EmpId);

Composite index
Syntax:
create index <indexName> on <tableName> (<columnName1>,<columnName2>);
Example:
create index index11 on Employee_Master(Age,Salary);