What are the difference between Stored procedure and function in sql server

Difference between Stored procedure and function

• Return Value

• Function must return any value
• Procedure can return single value or n number of rows or null

• Parameter Type

• Function has input parameter only
• Stored procedure can have input or output or both parameter

• Call

• Function can call from stored procedure
• Stored procedure can not call from function

• Use of DML

• Function can not use any DML statement
• Stored procedure can use all DML statement

• Exception handling

• Exception handling is not possible with function
• Stored procedure allow to use Exception handling

• Use of Transaction

• Function can not use any command of transaction control like begin, commit, rollback
• Transaction can be begin, commit and rollback in procedure.

• Compile

• Function compiles every time when its call
• Stored procedure compiles only once when it created

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.