What is function in SQL Server 2005/2008 ?

What is function in SQL Server 2005/2008 ?

Function is a database object in Sql Server. It is a set of sql statements that accepts only input parameters, perform actions and return the result.

 Function can return only single value or a table. We can’t use function to Insert, Update, and Delete records in the database table.

Basically two types of function in SQL Server.

1. System Defined Function.

2. User Defined Function

System Defined Function derived in two types.

1. System Defined Function.

1. Scalar Function

SQL scalar functions return a single value, based on the input value.

• UCASE() - Converts a field to upper case
• LCASE() - Converts a field to lower case
• MID() - Extract characters from a text field
• LEN() - Returns the length of a text field
• ROUND() - Rounds a numeric field to the number of decimals specified
• NOW() - Returns the current system date and time
• FORMAT() - Formats how a field is to be displayed

2. Aggregate Function

SQL aggregate functions return a single value, calculated from values in a column.

• AVG() - Returns the average value
• COUNT() - Returns the number of rows
• FIRST() - Returns the first value
• LAST() - Returns the last value
• MAX() - Returns the largest value
• MIN() - Returns the smallest value
• SUM() - Returns the sum

2. User Defined Function

User Defined Function derived in three types.

1. Scalar Function

User defined scalar function also returns single value as a result of actions perform by function. We return any data type value from function.

sno	city
1	Delhi
2	Kanpur
3	Lucknow
4	Aligarh
5	Alur
6	Ambaliyasan
7	Anandnagaar

select * from City

select * from State

sno	city	state
1	Lucknow	UP
2	Kanpur	UP
3	Anandnagaar	Assam
4	Ambaliyasan	Gujarat
5	Ambaji	Gujarat
6	Aligarh	UP
7	Delhi	Delhi
8	Alandha	Karnataka
9	Achhnera	UP

create function [dbo].[functionStatecity]

(

@state nvarchar(50),@city nvarchar(50)

)

returns nvarchar(250)

As

Begin return (Select @state + ' _ '+ @city);

end

Calling function in query:

Select dbo.functionStatecity(state,city) as Name, sno from state

Name	sno
UP _ Lucknow	1
UP _ Kanpur	2
Assam _ Anandnagaar	3
Gujarat _ Ambaliyasan	4
Gujarat _ Ambaji	5
UP _ Aligarh	6
Delhi _ Delhi	7
Karnataka _ Alandha	8
UP _ Achhnera	9

2. Inline Table Value Function

User defined Inline Table Value function return a table variable as a result of actions performs by function.

create function [dbo].[functioncity]()

returns Table

As

return (Select * from city where sno='4')

Calling function in query:

Select * from functioncity()

sno	city
4	Aligarh

3. Multi-Statement Table Value Function

Multi-statement table-valued function returns a table variable as a result of actions perform by function. In this a table variable must be explicitly declared and defined whose value can be derived from a multiple sql stateme

What is DML, DDL, DCL and TCL ?

DML

DML is  Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

DDL

DDL is  Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL

DCL is Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

TCL

TCL is Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

What is Option Explicit ?

When Option Explicit appears in a module, you must explicitly declare all variables using the Dim, Private, Public, ReDim, or Static statements. If you attempt to use an undeclared variable name, an error occurs at compile time.

Difference between UNION and UNION ALL

UNION : UNION removes duplicate records in output. UNION performs a DISTINCT on the result set, eliminating any duplicate rows

UNION ALL : UNION ALL does not remove duplicates records in output

Select * from city

sno	city
1	Delhi
2	Kanpur
3	Lucknow
4	Aligarh
5	Alur
6	Ambaliyasan
7	Anandnagaar

select * from State

sno	city	state
1	Lucknow	UP
2	Kanpur	UP
3	Anandnagaar	Assam
4	Ambaliyasan	Gujarat
5	Ambaji	Gujarat
6	Aligarh	UP
7	Delhi	Delhi
8	Alandha	Karnataka
9	Achhnera	UP

select city from City

UNION

select city from State

city

Achhnera

Alandha

Aligarh

Alur

Ambaji

Ambaliyasan

Anandnagaar

Delhi

Kanpur

Lucknow

select city from City

UNION ALL

select city from State

city

Delhi

Kanpur

Lucknow

Aligarh

Alur

Ambaliyasan

Anandnagaar

Lucknow

Kanpur

Anandnagaar

Ambaliyasan

Ambaji

Aligarh

Delhi

Alandha

Achhnera

How to read a table without name in sql server

we can't create table without name or name as blank space, but after creating a table we can rename it with a blank space.

Sql Server doesn't support table name with two or more blank space, thus there is no resigns of conflict with name. 

now the queries for the table with name as blank space would be like this.

select * from []

Regards

Asit kumar