Function in SQL -part 8

 In this blog we are going to understand  Sql function.

 FUNCTION:

• A function is a database object in SQL Server.
• It accepts only input parameters, perform an action and return the result.

There are two types of function in SQL Server

• System Functions
• User-Defined Functions

1.SYSTEM FUNCTIONS:

• All the built-in functions supported by the server called System functions.
• Built-In functions are used in SQL SELECT expressions to calculate values and manipulate data like Mathematical Functions, Ranking Functions, String Functions, etc.

Example:

First, we create a STUD table and insert dummy data.

                                                     

Here we retrieve the Student name and birth year using the Datename function. By using Date name function we will retrieve only the birth-year of a student.

SYSTEM FUNCTION

If you expand Datename function you will see two-parameter Date part and Expression.

                                                BUILT-IN FUNCTIONS                                                                        

2.USER DEFINED FUNCTIONS:

•  The user-defined function accepts the parameter and returns a result.
•   User-defined functions are create by a user.

Here we create a USER_DEFINE function and by using the SELECT statement retrieve the values in the STUD table.

USER DEFINED FUNCTION

DOB is passed as a parameter to the USER_DEFINE function which returns the formatted date.

There are two types of User-Defined Functions

• Scalar Function
• Table-valued Function

1.SCALAR FUNCTION:

•  It is a function that takes one or more values but returns a single value.

Here we create SCALAR_FUN and retrieve first name and surname as a name in one column also retrieve the Dob column.

SCALAR FUNCTION

Now see a newly created function in the database.

2.TABLE-VALUED FUNCTION:

• A table-valued function is a user-defined function that returns data of a table type.

TABLE-VALUED FUNCTION

There are two types of Table value Functions

1.INLINE TABLE VALUED FUNCTION:

• This function returns a table data type based on a single SELECT Statement.

We create INLINE_FUN and retrieve the data of the STUD table.

INLINE TABLE-VALUED FUNCTION

Now see a newly created function in the database.

2.MULTI-STATEMENT TABLE VALUED FUNCTION:

• Multi-statement table-valued function returns a table variable as a result of actions performed by the function.
• This function also returns the tabular result set.
• It is unlike the inline table-valued function, It contains only one statement or more than one statement.                      

            MULTI STATEMENT TABLE-VALUED FUNCTION

                                               

Now see a newly created function in the database.

     

                                                               

For more understanding watch below video :

https://youtu.be/uGlfP9o7kmY

      

Thank You!!!

Index, Clustered index and Non-clustered index in SQL-part 6

  In this blog we are going to understand  Index, Clustered index and Non-clustered index in SQL,

Index:

• An index improves the performance of search.
• An index makes a search faster by using a Balanced Tree (B-Tree) Structure. In B-Tree Structure data divided into root node, Non- leaf nodes and Leaf nodes.
• Index can make your insert, update and delete can become slove.

two types of index:

1.Clustered Index

2.Non-clustered Index

1.Clustered Index:

• A Clustered Index stores the actual data at the leaf node.
• The Clustered index will be automatically created when the primary key is defined.

2.Non-clustered Index:

• The Non-Clustered Index does not contain actual data. It uses a clustered index for fetching the data.
• Non-Clustered Index used to increase the speed of queries.

Example: If data from 1 to 100 and we want to search 80.The B-Tree divided data into two sections that are 1 to 50 and 51 to 100. Data 80 is greater than 50 therefore, the first section 1 to 50 is skipped only 51 to 100 is searched and gets the data.

how to create clustered index:

By creating index goto the design of the table. Then right-click on column and click on indexes/keys.

After that new pop-up window is open. In the Table designer, select the clustered index.

                                                                

How to create non-clustered index:

    

      

• When we execute the query and then click on Display Estimated Execution Plan it shows the execution plan.
• It uses the Clustered Index Seek. Clustered Index Seek scanning a particular range of rows from a clustered index.

     

For more understanding watch below video :

https://youtu.be/uGlfP9o7kmY

      

Thank You!!!

Trigger and Stored procedure SQL -part 7

 In this blog we are going to understand  Sql trigger and stored procedure.

Trigger:

• Trigger are  nothing but logic, which execute before insert. update and delete.
• Triggers are a special kind of stored procedure.
• It executes implicitly.
• When an event occur trigger is automatically executed.
• types of Trigger:

1.Instead of Trigger:

• Instead of  trigger  will be fired  instead insert,update and delete operations on a table.

2.After Trigger:

• After trigger fired after insert,update and deleter operations on a table.

Syntax of Creating Trigger:-

CREATE TRIGGER trigger_name
ON table_name
AFTER {[INSERT],[UPDATE],[DELETE]}
AS
BEGIN
{sql_statements}
END

How to create the Trigger:

                                                  

                                                  

Example:

                                                             

    CREATE TRIGGER

See the trigger are created:                       

       

            

 RESULT OF TRIGGER

 

STORED PROCEDURE:

• A stored procedure is  nothing but it is  block of code which we  can code reusable.
• The stored procedure  increase performance because they are pre-compiler, there plan are precompile and there syntax are checked at front.
• Syntax:

CREATE PROCEDURE proc_name
AS
Begin
{sql_statement}
End

How to create the stored procedure:

right click on programmability-->stored procedure.

Example:

                                                    

See the stored procedure are created:

                                            

To execute a stored procedure, we use the EXECUTE or EXEC the statement followed by the name of the stored procedure:

                                   

For more understanding watch below video :

https://youtu.be/uGlfP9o7kmY

      

Thank You!!!

SQL backup,restore and script in SQL- part 5

  In this blog we are going to understand SQL backup , restore and scripts.

SQL  Backup :

• A copy of sql server data that can be used to restore and recover the data after a failure.
• backup of a database can also be used to restore a copy the database to a new location.
• The backup copies the data or log records from database to device such as disc.

There are three main types of backup:

1.Full backup:

• Full backup stores a full copy of a specific database at the point in time that the backup completed.
• It contains all the data in a specific database or set of filegroups. 
• This backup copy of all data to a storage device, such as a disk or tape.

2.Differential backup :

• a copy of all data changes from the last full backup to the current point in time

3.Log backup :

• It is the backup of transaction logs that have all log records that are not backed up in previous log backup.

Steps for back up database :

1) Connect to database and expand database, select the database which we want to backup :

         

2) Select the backup type, By default, it is Full -:

                        

3) You can add new destination and add .bak extension :

                          

4) Click on "Ok" and the database backup completed :

               

5) You can check whether the backup is created successfully or not by going to destination folder :

            

SQL  Restore :

• The restore is a process of restoring or recovering data from backup.

Steps for restoring database :

1) Click on database => Restore database :

   

2) Click on device and select backup device : 

                         

3) Give name for database  :

     

4) Now database is restored successfully :

                      

5) And refresh database to see restored database :

  

SQL  Scripts :

• We can create or generate scripts for transferring database between instances.
• SQL script is a set of SQL commands which are stored as a file in SQL scripts.

Steps for generating scripts :

1)  Click on database => Task => Generate scripts :

                  

2) After clicking next, choose entire database or specific object :

                 

3)  Click on save as script file and destination :

                     

4)  We can see the scripts are saved, now click on finish :

 

5)  Open the destination folder and verify the script is generated or not :

 

6) Click on script file, we can see the set of commands :

 

For more understanding watch below video :

https://youtu.be/uGlfP9o7kmY

      

Thank You!!!

Select Top records in SQL- part 4

  In this blog we are going to find out the highest salary in customer table.

Select Top:

• The SELECT TOP clause is used to specify the number of records to return. 
• The SELECT TOP clause is useful on large tables with thousands of records, returning a large number of records can impact performance.

create a new table for solving the following query:

Customer Table:

                 

1.Finding the First highest sales in customer table?

                  

                       

2.Finding the Second highest sales in customer table?

                  

3.Finding the third highest sales in customer table?

                       

               

4.Finding the fourth highest sales in customer table?

                       

For more understanding watch below video :

https://youtu.be/uGlfP9o7kmY

Thank you!!!!