Posts

Function In SQL Server Part VIII

Image
Function IN SQL Server Function : A function is a set of SQL statements that perform a specific task.  Functions foster code reusability. There are Two Type of Function : A) System Defined Function : System functions are built-in database functions.  There are two types of system-defined functions in SQL Server : 1) System Scalar Functions : Scalar are on single value and returns single value. 2) System Aggregate Functions : Aggregates the values and return a single value. B) User-Defined Function : User-defined functions are create by a user. There are three types of user-defined functions in SQL Server : Table : tblCustomer   1) Scalar Function : It is a function that takes one or more values but returns a single value. Steps : 1) Select Function then Right click on Function and select new Scalar-valued Function in Programmability :  2) Example - Create Scalar-valued Function : 3) Scalar-valued Function Created Successfully
Post a Comment
Read more

Trigger & Stored Procedure In SQL Server Part VII

Image
Trigger & Stored Procedure In SQL Server Trigger : Triggers are nothing but they are logic’s like stored procedures that can be executed automatically before the Insert, Update or Delete, Or after the Insert, Update, or Delete. There are Two Types of Triggers : 1) Instead of Triggers : The Instead of trigger fires before the operations such as Insert, Update or Delete,  this trigger is going to be fired. 2) After Triggers : The After Triggers fires after the operations such as Insert, Update or Delete,  this trigger is going to be fired. Note : The  Insert, Update and Delete  statements are also known as Triggering SQL statements as these statements are responsible for the trigger to fire. Creating Trigger in SQL Server :  1) Select Trigger then Right click on Trigger and select new Trigger in tables :  Example : 2) Create Trigger : 3) Triggers are Created successfully : 4) Output of Trigger : St
Post a Comment
Read more

Index In SQL Server Part VI

Image
Index In SQL Server Index : Indexes are used to improve the performance of  searching, Because they are in sequential format it helps the searching operation. An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. A table or view can contain the following two types of indexes : 1) Clustered Index : Clustered Index arranges the indexes in the form of the Balanced Tree Structure(B-Tree). In SQL server clustered index is created automatically, when a primary key is created. There can be only one clustered index per table, because the data rows themselves can be stored in only one order.   Creating Clustered Index in SQL Server :  1) Go to Design of a table, then Right click on column and sel
Post a Comment
Read more

SQL Backup, Restore & Script Part V

Image
SQL Backup, Restore & Script SQL  Backup : A copy of SQL server data that can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Backup of a database can also be used to restore a copy the database to a new location. 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.  2) Differential Backup : Full Backup stores a full copy of a specific database at the point in time that the backup completed. 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 databases folder Right-click the databases and select Tasks and then select Back Up : 2) Select Full as t
Post a Comment
Read more