Difference Between function v/s store procedure

Definition of Store Procedure

In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs.

Definition of Function

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 of that action as a value. The return value can either be a scalar (single) value or a table.   

Difference Between Union vls Union All

In this Artical here when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports). Describe Union and union All see above -

Union -

In Sql server union use merge two and more select query. But you can take care about those query have equal number of columns and sequence of columns must be same both queries you include in union. Data type also same when apply union in both query. UNION performs a DISTINCT result , Removing any duplicate rows. It is slower than UNION ALL.

SYNTEX

   SELECT COLUMN1,COLUMN2 FROM TABLE1
     UNION
   SELECT COLUMN1,COLUMN2 FROM TABLE2

Ex.

Create Table in Sql server

CREATE TABLE TABLE1(
   Id int,
   Name nvarchar(50)
)
CREATE TABLE TABLE2(
   Id int,
   Name nvarchar(50)
)
Insert data in table
Insert into TABLE1(Id,Name)values(1,'jeet')
Insert into TABLE1(Id,Name)values(2,'Ranu')
Insert into TABLE1(Id,Name)values(3,'jitendra')
Insert into TABLE2(Id,Name)values(1,'Pulkit')
Insert into TABLE2(Id,Name)values(2,'jeet')
Insert into TABLE2(Id,Name)values(3,'Vijay')
Select Both Query With select Command and apply union See in above example

Example

Select Name from Table1
UNION
Select Name from Table2

Output-

Union All-

In Sql server union use merge two and more select query. Query have equal number of column and same data type. UNION ALL does not remove duplicates. Union All is fasterbthan union

SYNTEX

   SELECT COLUMN1,COLUMN2 FROM TABLE1
     UNION
   SELECT COLUMN1,COLUMN2 FROM TABLE2

Ex.

Create table
CRAETE TABLE TABLE1(
   Id int,
   Name nvarchar(50)
)
CRAETE TABLE TABLE2(
   Id int,
    Name nvarchar(50)
)

Insert data in table

Insert into TABLE1(Id,Name)values(1,'jeet')

Insert into TABLE1(Id,Name)values(2,'Ranu')

Insert into TABLE1(Id,Name)values(3,'jitendra')

Insert into TABLE1(Id,Name)values(1,'Pulkit')

Insert into TABLE2(COLUMN1,COLUMN2)values(2,'jeet')

Insert into TABLE2(COLUMN1,COLUMN2)values(3,'Vijay')

Select Both Query with select Command and apply Union All see in above example

Example

Select Name from Table1
UNION ALL
Select Name from Table2

Output-

In this example you can understand easly difference between Union and Union all .Now Some points define differnces in union and union all See above-

UNION	UNION ALL
Union eleminate all duplicate rows	Union All not does not eleminate all duplicate rows. It is fatch all record with duplicay
Union use distinct sort	Union All does't Show distinct sort
Union is slower than Union All	Union all is faster than Union
It is not work on columns that have text data type	It is work on those column that have datatype text.