User defined function in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

User defined function in SQL Server

Functions are basically a separate area where we write our code and according to the need we can call it.

SQL Server allows you to create functions, as we create functions in programming language. We can pass parameter to function and function can also return the output. These are fast as these are precompiled.

Consider the following table prod_rep as an example:-

Figure 1

Type of User defined function in SQL Server

According to its return type we categorized the user defined function into following two parts

Scalar function

In scalar function, we can return a single value from the function.

For example:-

Suppose I have to create function which will return the dept according to id.

Use of Top command in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Use of Top command in SQL Server

The top command is used to select the top value from the table. We have to give the number of row with the top command.

For Example:-

Consider the following table as example in which it shows the prod_year, dept and no_of_prod.

Figure 1

Suppose in this table if you want to select top 2 rows then you will use the top command as follows:-

select top 2 * from prod_rep;

Click Here to View Complete Artical

Range operator in SQL Server

Written By:- Isha Malhotra

Our Website:-Tech Altum

Range operator in SQL Server

Range operators are those operators which selects data according to particular range. There are two type of range operator and these are following:-

Between operator:-

Between operator is used to select the data between any range.

For example:-

select * from prod_rep where No_of_Prod between 1000 and 1200;

in this example it will select data between 1000 and 1200.

Note:-between operator include the range also. It means it will also select those data which contain 1000 and 1200.

Click Here to View Complete Tutorial

Group by in Sql Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Group by

Sometimes you need to show data in some groups with some aggregate function. In this situation you can use group by statement.

For example:-

Check the data of following table. Suppose you want to show the sum of all no of prod according to year. In that case you use group by statement.

Click Here to View Complete Artical

Compute and Compute By in Sql Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Compute and Compute By

Sometimes in your records you need to show all data of table with some aggregate.

For example:-

See the data of following table:-

Figure 1

Suppose I want to show the data of all columns and after that I want to show the count of gender.

In this situation we will use the compute in following way:-

Sorting of Data in SQL Server

Written By:- Isha Malhotra (Software Developer)
Email:-malhotra.isha3388@gmail.com.
Note: -if you find this article helpful then kindly leave your comment

Sorting of Data in SQL Server

If we want to sort the data on the particular column in that case we use order by command. We can sort data either in ascending order or in descending order. By default order by command sorts data in ascending order.

For example:-

Check the data of following table:-

Figure 1

Suppose I want to sort data according to the gender then I will use the following query:-

select * from Enqdata order by gender

Note:-by default it sorts data in ascending order.

Alter command in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Use of Alter in SQL Server

When we need to modify the definition of Table then we use alter command. Alter comes under DDL Commands.

To start with alter check the structure of following table:-

Figure 1

To Add New Column in Table

Suppose I want to add new column for dept. in this table then we use alter query in following manner:-

alter table emp

add dept varchar(100)

now run select * from emp

you will get following result:-

Figure 2

Use of Pivot in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

Use of Pivot in SQL Server

When we need to format the result according to user‘s need then we use pivot. Pivot provides us facilities to use the column data as column.

For example:-

Check the following data in table:-

Figure 1