Page 76 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes 5.9.3 Permissions

To create a synonym in a given schema, a user must have CREATE SYNONYM permission and
either own the schema or have ALTER SCHEMA permission.
The CREATE SYNONYM permission is a grantable permission.

Notes You do not need permission on the base object to successfully compile the CREATE
SYNONYM statement, because all permission checking on the base object is deferred until
run time.
5.10 Programming Objects

There are a number of programming objects such as functions, triggers, procedures etc., used in
SQL server. In this topic, you learn how to create these programming objects in SQL Server.

5.10.1 Creating Procedures

When you create an application with Microsoft® SQL Server™ 2000, the Transact-SQL
programming language is the primary programming interface between your applications and
the SQL Server database. When you use Transact-SQL programs, two methods are available for
storing and executing the programs. You can store the programs locally and create applications
that send the commands to SQL Server and process the results, or you can store the programs as
stored procedures in SQL Server and create applications that execute the stored procedures and
process the results.

Stored procedures in SQL Server are similar to procedures in other programming languages in
that they can:

Accept input parameters and return multiple values in the form of output parameters to
the calling procedure or batch.
Contain programming statements that perform operations in the database, including
calling other procedures.
Return a status value to a calling procedure or batch to indicate success or failure (and the
reason for failure).
You can use the Transact-SQL EXECUTE statement to run a stored procedure. Stored procedures
are different from functions in that they do not return values in place of their names and they
cannot be used directly in an expression.
The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored
locally on client computers are:

They allow modular programming: You can create the procedure once, store it in the
database, and call it any number of times in your program. Stored procedures can be
created by a person who specializes in database programming, and they can be modified
independently of the program source code.

They allow faster execution: If the operation requires a large amount of Transact-SQL code
or is performed repetitively, stored procedures can be faster than batches of Transact-SQL
code. They are parsed and optimized when they are first executed, and a compiled version
of the stored procedure remains in memory cache for later use. This means the stored

70 LOVELY PROFESSIONAL UNIVERSITY