Answer Posted / s.chandrahasan

A stored procedure is like a miniture program in SQL Server.
It can be as simple as a select statement, or as complex as
a long script that adds, deletes, updates, and/or reads data
from multiple tables in a database. (Stored procedures can
also implement loops and cursors which both allow you to
work with smaller results or row by row operations on data.)

The SQL Server functions are option for doing certain
operations in SQL Server. They can not be used to update,
delete, or add records to the database. They simply return a
single value or a table value. They can only be use to
select records. However, they can be called very easily from
within standard SQL, such as:

SELECT dbo.functionname('Parameter1')

OR

SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects

For simple reusable select operations, functions can simply
your code. Just be wary of using JOIN clauses in your
functions. If your function has a JOIN clause and you call
it from another select statement that returns multiple
results, that function call with JOIN those tables together
for EACH line returned in the result set. So though they can
be helpful in simpling some logic, they can also be a
performance bottleneck if they're not used properly.

Can you force a query to use a specific index?

943

---

Explain for xml explicit mode?

974

---

How many types of schemas are there?

1040

---

What is the difference between functions and scalar functions?

1208

---

What are the ways available in sql server to execute sql statements?

1134

---

Is the order of columns in the set clause important in ms sql server?

968

---

What are the advantages of user-defined functions over stored procedures in sql server?

1038

---

What are indexers?

1085

---

How many types of objects are there?

1006

---

How do I open port 1433?

930

---

What are the different types of backups avaialabe in sql server 2005?

1018

---

what is datawarehouse?

1110

---

Explain sub-query?

952

---

What are the different types of upgrades that can be performed in sql server?

1175

---

What are the types of dml?

978

---