Internal SQL Functions

1. Functions Introduction
This section gives examples of all functions that are available in the Mckoi database software. Functions may be used within all expressions except aggregate functions that may only be used within the SELECT .... FROM clause.

2. Mathematical Functions
Follows are all the mathematical functions available in Mckoi Database. Note that some of these functions may lose precision because the number could be cast to a 'double'. You can assume that all mathematical functions have at worst the precision of a 'double'. ABS(number)
Returns the absolute value of a number.
Examples:
SELECT ABS(-0.94)
==> 0.94
SELECT ABS(9 - 200)
==> 191
SIGN(number)
Returns 1 if the number is positive, -1 if the number is negative and 0 if the number is zero.
Examples:
SELECT SIGN(40)
==> 1
SELECT SIGN(-40)
==> -1
SELECT SIGN(40 - 40)
==> 0
MOD(number1, number2)
Returns the modulo of number1 and number2 (equivalent to {number1 % number2} in Java).
Examples:
SELECT MOD(15, 5)
==> 0
SELECT MOD(33, 10)
==> 3
SELECT ROUND(552 / 10), MOD(552, 10)
==> 55, 2
ROUND(number, decimal_places)ROUND(number)
Rounds the number to 'n' decimal places. When no 'decimal_places' argument is provided the number is rounded to the nearest whole number.
This will round up if the fraction to the right is >= .5 otherwise it rounds down. This uses the {BigDecimal.setScale(decimal_places, BigDecimal.ROUND_HALF_UP)} method for rounding.
Examples:
SELECT ROUND((943 * 13) / 99, 3)
==> 123.828
SELECT ROUND((943 * 13) / 99, 2)
==> 123.83
SELECT ROUND((943 * 13) / 99)
==> 124
POW(number1, number2)
Raises number1 to the power of number2.
Examples:
SELECT POW(9, 6)
==> 531441
SELECT POW(2, 32)
==> 4294967296
SELECT POW(2, 64)
==> 18446744073709551616
SELECT POW(2, -3)
==> 0.125
SQRT(number)
Finds the square root of the number argument.
Examples:
SELECT SQRT(65536)
==> 256
SELECT SQRT(-1)
==> NULL
LEAST(val1, val2, ...)
This function accepts any number of arguments and returns the value that represents the least value of the set.
Examples:
SELECT LEAST(4)
==> 4
SELECT LEAST(90, 9.125, 3, 75)
==> 3
SELECT LEAST('H', 'Z', 'B')
==> B
SELECT LEAST(10 / 3, 10 * 3,
POW(10, 3), MOD(10, 3))
==> 1
GREATEST(val1, val2, ...)
This function accepts any number of arguments and returns the value that represents the greatest value of the set.
Examples:
SELECT GREATEST(4)
==> 4
SELECT GREATEST(90, 9.125, 3, 75)
==> 90
SELECT GREATEST('H', 'Z', 'B')
==> Z
SELECT GREATEST(10 / 3, 10 * 3,
POW(10, 3), MOD(10, 3))
==> 1000

3. String Functions

String functions in Mckoi Database mostly map to equivalent functions found within java.lang.String. LOWER(str)
Returns a lower case version of the string literal argument.
Examples:
SELECT LOWER('THis is sOME TEXT')
==> this is some text
UPPER(str)
Returns an upper case version of the string literal argument.
Examples:
SELECT UPPER('THis is sOME TEXT')
==> THIS IS SOME TEXT
CONCAT(str1, str2, ...)
Returns the concatenation of the string arguments. This function can take any number of arguments.
Examples:
SELECT CONCAT('This i', 's some text', '.')
==> This is some text.
SELECT CONCAT('-', 0.95)
==> -0.95
LENGTH(str)
Returns the number of characters in the string argument.
NOTE: This may additionally be used on BLOB data to return the count of bytes in the BLOB.
Examples:
SELECT LENGTH('This is some text')
==> 17
SELECT LENGTH(0.544)
==> 5
SELECT LENGTH(' Test')
==> 8
TRIM( [ [ LEADING TRAILING BOTH ] [ characters ] FROM ] str )LTRIM(str)RTRIM(str)
Trims characters from a string argument. The LTRIM and RTRIM form trim whitespace from the left and right of the string respectively.
Examples:
SELECT TRIM(TRAILING 'a' FROM 'aaabcdaaa')
==> aaabcd
SELECT TRIM(LEADING 'a' FROM 'aaabcdaaa')
==> bcdaaa
SELECT TRIM('ab' FROM 'ababzzzzab')
==> zzzz
SELECT TRIM(' a string message ')
==> a string message
SUBSTRING(str, start_index)SUBSTRING(str, start_index, length)
Returns a substring of a string. The SUBSTRING function complies with the SQL specification. The start_index parameter is a value between 1 and the length of the string where 1 includes the first character, 2 includes the second character, etc. The length parameter represents the size of the substring.
Examples:
SELECT SUBSTRING('Tobias Downer', 8)
==> Downer
SELECT SUBSTRING('abcd', 1, 2)
==> ab
SELECT SUBSTRING('abcd', 3, 4)
==> cd
SELECT SUBSTRING('abcd', 3, 5000)
==> cd
SELECT SUBSTRING('abcd', 0, 5000)
==> abcd
SELECT SUBSTRING('abcd', 1, 0)
==> (string of 0 length)

4. Aggregate Functions

Aggregate functions can only operate within a group of a SELECT statement. They are used to compute statistics over a set of records. COUNT(*)COUNT(DISTINCT expression_list)COUNT(column_name)COUNT(expression)
The * version of this function returns the total number of rows in the group. If a column name is specified it returns the number of non-null values in the group. The 'expression' form of this function evaluates the expression for each row in the group and counts it only if it evaluates to NULL. COUNT(DISTINCT ... ) counts all distinct values of the expression list over the group.
Examples:
SELECT COUNT(*)
FROM Orders
SELECT COUNT(*)
FROM Orders
GROUP BY division
SELECT COUNT(id)
FROM Orders
GROUP BY division
SELECT last_name, COUNT(DISTINCT last_name)
FROM Customers
GROUP BY age
SUM(column_name)SUM(expression)
Calculates the sum of all values in a column/expression over a group. The expression form of this function is evaluated for each row in the group.
Examples:
SELECT SUM(value) FROM Orders
SELECT SUM(quantity * value)
FROM Orders
SELECT SUM(quantity * value) * 0.75
FROM Orders
GROUP BY division
AVG(column_name)AVG(expression)
Calculates the average of the column/expression over the group. The expression form of this function is evaluated for each row in the group.
Examples:
SELECT AVG(value) FROM Orders
SELECT AVG(quantity * value)
FROM Orders
SELECT AVG(quantity * value) * 0.75
FROM Orders
GROUP BY division
MIN(column_name)MIN(expression)
Finds the minimum value of a column/expression over a group.
Examples:
SELECT MIN(value) FROM Orders
SELECT MIN(quantity * value)
FROM Orders
SELECT MIN(quantity * value) * 0.75
FROM Orders
GROUP BY division
MAX(column_name)MAX(expression)
Finds the maximum value of a column/expression over a group.
Examples:
SELECT MAX(value) FROM Orders
SELECT MAX(quantity * value)
FROM Orders
SELECT MAX(quantity * value) * 0.75
FROM Orders
GROUP BY division

5. Security Functions

Functions that provide security information about the session performing the query. USER()
Returns the current user. PRIVGROUPS()
Returns a comma deliminated list of priv groups the user belongs to. A user may belong to any number of groups which dictate the tables a user may access. 6. Branch Functions
IF(condition_expr, true_expr, false_expr)
If the first expression (condition_expr) evaluates to true this function returns the result of 'true_expr' otherwise returns the result of 'false_exp'.
Examples:
SELECT IF(true, 5, 8)
==> 5
SELECT IF(false, 5, 8)
==> 8
SELECT IF(NULL, 5, 8)
==> NULL
SELECT IF(true, IF(false, 1, 2), 3)
==> 2
SELECT IF(col1 = 0, 'N/A', col1) FROM MyTable
COALESCE(expr1, expr2, expr3, ....)
Returns the first non null value from the parameters or null if the entire list contains null values.
Examples:
SELECT COALESCE(NULL, 'a')
==> a
SELECT COALESCE(NULL, NULL, NULL)
==> NULL
SELECT COALESCE(col1, 'N/A') FROM MyTable

7. Date/Time Functions

DATEOB(date_string)
Parses a string to a Date object that can be used on queries against TIMESTAMP / DATE / TIME columns. DATEOB with no arguments returns the current time of the machine running the database.
Since version 0.92 this function has been deprecated. Use the standard DATE, TIME and TIMESTAMP literals specified in SQL-92 instead.
Examples:
SELECT DATEOB()
==> Wed Aug 09 11:49:31 EDT 2000
SELECT DATEOB('Aug 1, 2000')
==> Tue Aug 01 00:00:00 EDT 2000
SELECT number FROM Orders
WHERE date_made >= DATEOB('Jan 1, 2000')

8. Misc Functions

UNIQUEKEY(table_name)
Returns a unique key for the given table name. This is an atomic operation that is guaranteed to return a unique number each call. It should be used to generate unique identification numbers for records. It is similar to the AUTO_INCREMENT feature of other database systems.
Examples:
SELECT UNIQUEKEY('Orders')
INSERT INTO Orders
( id, number, division, date_made, quantity,
value )
VALUES
( UNIQUEKEY('Orders'), CONCAT('Order-', id),
'Bio Engineering', DATEOB(), 25, 1900.00 )
TONUMBER(expression)
Attempts to cast the expression to a number. If the expression is a boolean then this function will return 1 for true or 0 for false. If the expression is a String then it attempts to parse the string into a number. If the expression is a Date then it returns the date as the number of milliseconds since Jan 1st, 1970.
Examples:
SELECT TONUMBER(DATEOB('Aug 1, 2000'))
==> 965102400000

Enterprise Manager

Part ii

SQL SERVER 2000

ENTERPRISE MANAGER

Navigating Enterprise Manager

The Enterprise Manager is a graphical interface tool used to administer your SQL Servers. You can use the Enterprise Manager to configure SQL Server options, create/edit/view databases, perform maintenance and backups, and do quite a few more tasks that we will look at over the next few weeks.

Back a couple of articles we saw that the SQL Server 2000 setup creates a program group on the Start Menu named "Microsoft SQL Server." To launch the Enterprise Manager Click Start > Programs > Microsoft SQL Server > Enterprise Manager

If you have ever used the Microsoft Management Console (MMC) you will notice that the Enterprise Manager looks familiar. This is because the Enterprise Manager is a MMC snap-in like many of the tools provided with Windows 2000.

By expanding "Microsoft SQL Server" and then expanding "SQL Server Group" you can locate the SQL Server you have installed on your local system. Expanding the tree further allows you to manage databases, security, replication, etc.

Right clicking the SQL Servers icon in the tree view will also popup a menu with options that allows you to start/stop the server, view the server messages, access the server's properties, etc.

SQL Server Groups and Registrations

In many cases you will want to reorganize how your SQL Servers (or Instances) are listed in Enterprise Manager. To make organizing your SQL Servers simpler, Enterprise Manager allows you to create Server Groups. Creating a new group is not complicated; start by right clicking "Microsoft SQL Server" on the Enterprise Manager tree.

Select "New SQL Server Group" from the popup menu.

The Server Groups screen allows you to create new groups as Top level (under "Microsoft SQL Server") or as a Sub-group under an existing group.

Enter a name for the group in the "Name:" textbox and click OK.

You can continue to add Groups and Sub-groups until you have the ideal layout for your organization.

Modifying Server Registrations

To access an instance of SQL Server in Enterprise Manager you must first register the server. When you install SQL Server, setup automatically registers the instance in Enterprise Manager. If you need to move a SQL Server Registration to another group in Enterprise Manager, or if you need to update the authentication information, you can simply right click the server in the Enterprise Manager tree and select "Edit SQL Server Registration Properties..."

To change the group the server is listed under, select another group name from the "Server Group:" drop-down list.

The "Display SQL Server state in console" checkbox allows you to enable or disable the automatic display of status icons. If you disable this option the server's icon will appear without a status indicator when you open Enterprise Manager:

The "Show system databases and system objects" checkbox will allow you to hide system objects. System objects include system databases, tables, stored procedures, etc... (more on system databases later in this article)

Finally, the "Automatically start SQL Server when connecting" checkbox will automatically start the SQL Server service if the server is stopped when you try connecting.

Once you have made your changes click OK.

Adding SQL Server Registrations

If you need to register a new SQL Server right click any server group and select "New SQL Server Registration..."

Unless you have previously run the wizard and disabled it, you are greeted with the Register SQL Server Wizard welcome screen. Click Next to continue.

Next select the SQL Server(s) you would like to add from the left list-box and click "Add." If your server is not on the list, enter the name of the server in the text-box provided and then click "Add." If your server is already registered in Enterprise Manager the server will not appear in the list of servers. Once you have selected all the servers you would like to add, click Next.

Select the appropriate type of authentication for your server and click Next. Note if you select SQL Server Authentication you will then be prompted for the account and password Information. SQL Server Authentication also gives you the option to prompt you for authentication information each time you connect to the server with Enterprise Manager. Windows Authentication uses the credentials of the user that is currently logged on when you connect to a server using Enterprise Manager.

Next, select the group you would like to add your SQL Server under. You can also create a new top-level group if one does not currently exist. After you select the Server Group, click Next.

Click Finish to add the Server(s) to Enterprise Manager.

The Server(s) is (are) then registered and you are given a status of the registration process. If any registrations fail you can select the server in the status window and click Properties. From the properties screen you can then confirm that your login information is correct. When your servers have been registered, click Close. Your new server(s) now appear(s) in Enterprise Manager under the group name you selected in the wizard.

System Databases

When you install SQL Server several databases are created automatically. Two of the databases (Northwind and pubs) are sample user databases and can be used as examples, or you can safely delete them (but they don't take up a lot of hard disk space). The other four special system databases (master, model, msdb, and tempdb) are used by the system and should not be removed. To view the databases that are on your SQL Server, in Enterprise Manager expand the group your server is located in, then expand the server, and last expand the Databases folder. Remember, if you have the "Show system databases and system objects" checkbox unchecked in the SQL Server's registration properties, you will be unable to see the system databases in Enterprise Manager.

master Database -- Contains information about the server overall and all databases created. The information in this database includes: file allocations, disk space usage, system-wide settings, user accounts, and the existence of other databases and other SQL Servers.

model Database -- This database provides a template for all newly created databases. If you ever need to add a table or stored procedures to all the new databases you create, simply modify the model database.

msdb Database -- Used by the SQL Server Agent service.

tempdb Database -- Is a workspace used for holding temporary information.

Books Online

The last topic I want to cover in this article is the SQL Server Books Online (sometimes called BOL) resource. Books Online, located on the start menu under "Programs" > "Microsoft SQL Server", are an invaluable resource. If you ever have a question the first place I would go are the Books Online -- they provide information on just about every topic dealing with SQL Server. One thing you may notice is that the Books Online are also used as the online "help" inside Enterprise Manager.

Enterprise Manager - Courtesy:www.databasejournal.com

[That finishes it up for this article. I hope you are finding this series to be a useful one, and I'm looking forward to your feedback.]

Email:

sqlbymadhu@gmail.com

Lock

LOCK

Securing the resources from one user to other user

The software does not allow other users to perform operations on the resource that would adversely affect the depencies of the user owning the lock.

Share Lock

If a user locks a table in share mode, other users can only view the data. But cannot update or delete the records. If they try to do so there system will go hanging.

To release the lock give commit or rollback command, multiple users can place share lock on same table at the same time.

Exclusive Lock

Only one user can place exclusive lock on a table.

Share Update Lock

That will lock records that are to be updated.

Transactions

TRANSACTION

A database transaction is a larger unit that frames multiple SQL statements. A transaction ensures that the action of the framed statements is atomic with respect to recovery.

A SQL Modification Statement has limited effect. A given statement can only directly modify the contents of a single table (Referential Integrity effects may cause indirect modification of other tables.) The upshot is that operations which require modification of several tables must involve multiple modification statements. A classic example is a bank operation that transfers funds from one type of account to another, requiring updates to 2 tables. Transactions provide a way to group these multiple statements in one atomic unit.

In SQL92, there is no BEGIN TRANSACTION statement. A transaction begins with the execution of a SQL-Data statement when there is no current transaction. All subsequent SQL-Data statements until COMMIT or ROLLBACK become part of the transaction. Execution of a COMMIT Statement or ROLLBACK Statement completes the current transaction. A subsequent SQL-Data statement starts a new transaction.

In terms of direct effect on the database, it is the SQL Modification Statements that are the main consideration since they change data. The total set of changes to the database by the modification statements in a transaction are treated as an atomic unit through the actions of the transaction. The set of changes either:

• Is made fully persistent in the database through the action of the COMMIT Statement, or
• Has no persistent effect whatever on the database, through:
• the action of the ROLLBACK Statement,
• abnormal termination of the client requesting the transaction, or
• abnormal termination of the transaction by the DBMS. This may be an action by the system (deadlock resolution) or by an administrative agent, or it may be an abnormal termination of the DBMS itself. In the latter case, the DBMS must roll back any active transactions during recovery.

The DBMS must ensure that the effect of a transaction is not partial. All changes in a transaction must be made persistent, or no changes from the transaction must be made persistent.

Explicit Transaction

Commit

Saves Changes permanently to the Database in the Server

BEGIN TRANSACTION

COMMIT TRASACTION

Data Definition Language (DDL) are automatically committed. The importance of transactions in older versions of sql and oracle.

Rollback

This cancels the changes up to the previous commit or savepoint

BEGIN TRANSACTION

ROLLBACK TRANSACTION

Savepoint

This creates a marker in between transactions

BEGIN TRANSACTION

SAVE TRANSACTION S

[Note: Save point in Rollback Transaction

BEGIN TRANSACTION

ROLLBACK TRANSACTION S

S is the savepoint (restore point)]

Example

TABLE - EMPLOYEE

ENO	ENAME	SAL	Transactions
1	BIJU	45000
2	UNNI	42000
3	BALU	36000
			Committed
4	GOPI	62000
5	HARI	22000
			Savepoint S
6	UNNI	16000
7	GOVIND	39000
			Rollback to S

Implicit Transaction

It means all the transactions are saved automatically.

Distributed Transaction

Distributed transactions span two or more Servers known as resource manager.

Trigger

TRIGGER

DATABASE TRIGGER

This is can be defined as a name stored procedure. This is created on a table and is fired based on database events (INSERT, UPDATE, DELETE).

[A trigger is a database object that is Bind to a table. In many aspects it is similar to a stored procedure and are often referred to as a "special kind of stored procedure." SQL Server 2000 greatly enhances trigger functionality, extending the capabilities of the triggers you already know and love, and adding a whole new type of trigger, the "Instead Of" trigger.

When to Use Triggers

There are many reasons to use triggers. If you have a table which keeps a log of messages, you may want to have a copy of them mailed to you if they are urgent. If there were no triggers you would have some solutions, though they are not as elegant. You could modify the application(s) logging the messages. This means that you might be redundantly coding the same thing in every application that logs messages.

Tables can have multiple triggers. The CREATE TRIGGER statement can be defined with the FOR UPDATE, FOR INSERT, or FOR DELETE clauses to target a trigger to a specific class of data modification actions. When FOR UPDATE is specified, the IF UPDATE (column_name) clause can be used to target a trigger to updates affecting a particular column.

SQL Server 2000 greatly enhances trigger functionality, extending the capabilities of the triggers you already know and love, and adding a whole new type of trigger, the "Instead Of" trigger.]

DELETED AND INSERTED TABLES

Two special tables are used in trigger statements, the ‘deleted’ table and ‘inserted’ table. Microsoft SQL Saver 2000 automatically created and manages these tables. You can use these temporary memories, resident tables to test the effects of certain data modifications and to set conditions for trigger actions, however, you cannot alter the data in the table directly.

UPDATE TRIGGER

This fired when it encounters an update command with a particular condition.

Column level Update Trigger

CREATE TRIGGER T1 ON STUDENTS

FOR UPDATE

AS

IF (SELECT AGE FROM INSERTED)>10

BEGIN

PRINT ‘AGE SHOULD NOT BE LESS THAN 10’

ROLLBACK TRANSACTION

END

Checking the trigger method: UPDATE STUDENTS SET AGE=8 WHERE

ENAME='SANTHOSH'

Table Level Update Trigger

CREATE TRIGGER T2 ON STUDENTS

FOR UPDATE

AS IF UPDATE (AGE)

BEGIN

PRINT ‘AGE SHOULD NOT BE CHANGED’

ROLLBACK TRANSACTION

END

INSERT TRIGGER

This is fired when it encounters an insert command with a particular condition.

CREATE TRIGGER T3 ON EMPLOYEES

FOR INSERT

AS

IF (SELECT SAL FROM INSERTED)>2000

BEGIN

PRINT ‘SALARY SHOULD NOT BE LESS THAN 2000’

ROLLBACK TRANSACTION

END

DELETE TRIGGER

This is fired when it encounters an delete command with a particular condition

CREATE TRIGGER T4 ON EMPLOYEES

FOR DELETE

AS

IF (SELECT COUNT (*) FROM DELETED)>1

BEGIN

PRINT ‘MORE THAN ONE RECORD CAN NOT BE REMOVED AT A TIME’

ROLLBACK TRANSACTION

END

DROPPING A TRIGGER

DROP TRIGGER

Eg: DROP TRIGGER T1.

MUTATING A TRIGGER OR CASCADING TRIGGER

Created for one table affects another table

Eg: If we created a trigger in child table it affects Parent table also.

Cursor

CURSOR

This can be defined as a memory are in which result of a query is started. If you want to use a multiple raw select in a stored procedure you need to create a cursor.

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

Step 01

Declare the Cursor

When you declare the cursor, we tell the server to allocate memory for the query, which is to be executed.

Step 02

Open the Cursor

The Query actually gets executed a results set is created and the cursor points to the first Record.

Step 03

Fetch the Records one by one from the results set using a loop.

Step 04

Close the Cursor

Step 05(optional)

De-allocates the memory occupied by the Cursor.

Eg:1

DECLARE CUR1 CURSOR FOR SELECT * FROM EMPLOYEES WHERE DEPTNO=20

OPEN CUR1

FETCH NEXT FROM CUR1

WHILE @@FETCH_STATUS =0

BEGIN

FETCH NEXT FROM CUR1

END

CLOSE CUR1

DEALLOCATE CUR1

Eg:2

DECLARE @EN VARCHAR(20), @S INT

DECLARE CUR2 CURSOR FOR SELECT ENAME,SAL FROM EMPLOYEES WHERE DEPNO=20

OPEN CUR2

FETCH NEXT FROM CUR2 INTO @EN, @S

WHILE @@FETCH_STATUS=0

BEGIN

PRINT @EN

PRINT @S

FETCH NEXT FROM CUR2 INTO @EN, @S

END

CLOSE CUR2

DEALLOCATE CUR2

User Defined Function

User Defined FUNCTION

Function can be defined as a Sub Programs which Performs an action this can be called from other Programs. Function should return Value.

Eg.1

CREATE FUNCTION NETSAL (@BPAY INT, @HRA INT, @DA INT)

RETURNS INT

BEGIN

RETURN (@BPAY+@HRA+@DA)

END

Calling Program

DECLARE @NS INT

EXEC @NS = NETSAL 4000, 3000, 2000

SELECT @NS ‘NET SALARY FOR THE EMPLOYEE’

O/P – NET SALARY FOR THE EMPLOYEE 9000

Eg: 2

CREATE FUNCTION CALC (@N VARCHAR (20))

RETURNS INT

BEGIN

DECLARE

@SAL INT, @COMM INT

SELECT @SAL=SAL FROM EMPLOYEES WHERE ENAME = @N

SET @COMM = SAL * .1

RETURN @COMM

END

Calling Program

DECLARE @C INT

EXEC @C AS ‘Commission For The Employee’

Dropping a Function

DROP FUNCTION NETSAL

Stored Procedure

STORED PROCEDURE

Programmatic part of the SQL server is called Stored Procedure. This may consists of SQL and non SQL statements.

Eg:1

DECLARE @A INT

DECLARE @B INT

DECLARE @C INT

SET @A=100

SET @B=200

BEGIN

SET @C = @A+ @B

SELECT ‘Sum Is’, @C

END

O/P - Sum Is 300

Update a Table

Eg:2

DECLARE @N VARCHAR (20), @EN INT

SET @N=’UNNI’

SET @EN=1

BEGIN

SELECT * FROM EMPLOYEES WHRE ENAME = @N

UPDATE EMPLOYEES SET SAL =SAL+500 WHERE ENO=@EN

SELECT ‘SALARY UPDATED’

END

User Defined Procedure

Procedure can be defined as a Sub Program which performs an action, this can be called from other Programs. (JAVA, C#, VB, C++)

Procedure may or may not return Value.

Eg:3

CREATE PROCEDURE PR1 @N1 INT, @N2 INT

AS

DECLARE @R INT

SET @R = @N1+ @N2

SELECT ‘Sum Is’, @R

Calling Program

EXEC PR1 250,750

O/P - Sum Is 1000

Procedure that Returns the Value

Eg:4

CREATE A PROCEDURE PR2 @N1 INT, @N2 INT

AS

DECLARE @R INT

SET @R=@N1+@N2

SELECT ‘Sum Is’ ,@R

RETURN @R

Calling Program

DECLARE @ TOTAL INT

EXEC @TOTAL = PR2 250, 750

SELECT @TOTAL/2 AS ‘Averate of Two Numbers’

Eg 5

CREATE PROCEDURE PR3 @EN INT

AS

DECLARE @SL INT, @COMM INT

BEGIN

SELECT @SL=SAL FROM EMPLOYEES WHERE ENO=@EN

SET @COMM=@SL *.1

SELECT ‘Commission Is’,@COMM

END

Calling Program

EXEC PR3 5

Note: How it works

· In Calling Program, we input a value to procedure (This Value must be in the table)

· This Value stored in PR3 variable @EN

· Selects the @EN value from the table Employees (SELECT FROM EMPLOYEES WHERE ENO=@EN)

· It stores the variable @SL

· @SL multiplies to .1 & it stores to @COMM

· @COMM displays the output with ‘Commission is’

Dropping a Procedure

DROP PROCEDURE

EG: DROP PROCEDURE PR1

Index

INDEX

Index is a database object in which records are stored in ascending order. Index cannot be opened by the user. The advantage of index is faster accessing records (it is internal action, no need to specify the created index).

CREATE INDEX EMPX ON EMPLOYEES (ENO)

ENO is Key column (column to be indexed)

Composite Index

CREATE INDEX EMPX ON EMPLOYEES (ENO,ENAME)

Unique Index

Unique index cannot be created on a table which has duplicate records in the key column.

When you created a table with unique or primary key constraint, a unique index is automatically created for the table.

CREATE UNIQUE INDEX EMPX ON EMPLOYEES(ENO)

Clustered Indexes

A printed phone directory is a great example of a clustered index. Each entry in the directory represents one row of the table. A table can have only one clustered index. That is because a clustered index is the actual table sorted in order of the cluster key. At first glance, you might think that inserting a new row into the table will require all the rows after the inserted row to be moved on the disk. Luckily, this is not the case. The row will have to be inserted into the correct data page, and this might require a page split if there is not enough room on the page for the new row. A list of pointers maintains the order between the pages, so the rows in other pages will not have to actually move.

The primary key of the phone directory is the phone number. Usually the primary key is used as the clustering key as well, but this is not the case in our example. The cluster key in the phone directory is a combination of last name and first name. How would you find a friend's phone number if you knew the last and first name? Easy, open the book approximately to the section of the book that contains the entry. If your friend's last name starts with an "F", you will search near the beginning of the book, if an "S", you will search towards the back. You can use the names printed at the top of the page to quickly locate the page with the listing. You then drill down to the section of the correct page till you find the last name you are looking for. Now you can use the first name to choose the correct listing. The phone number is right there next to the name. It probably takes more time describe the process than to actually do it. Using the last name plus first name to find the number is called a clustered index seek.

Lets say you wanted to find all the people who have the last name of "Smith" for a family reunion. How quickly could you locate all the names? Of course, it would take a matter of seconds to find all of them grouped together, possibly over a few pages. What if you wanted to locate everyone with the first name of "Jeff" in the book? Could it be done? Of course it could, but you would have to look at every entry in the entire book because the first name is the second column in our cluster key. This is called a clustered index scan, a very expensive operation on a big table.

Non-Clustered Indexes

The index in the back of a book is an example of a non-clustered index. A non-clustered index has the indexed columns and a pointer or bookmark pointing to the actual row. In the case of our example it contains a page number. Another example could be a search done on Google or another of the search engines. The results on the page contain links to the original web pages. The thing to remember about non-clustered indexes is that you may have to retrieve part of the required information from the rows in the table. When using a book index, you will probably have to turn to the page of the book. When searching on Google, you will probably have to click the link to view the original page. If all of the information you need is included in the index, you have no need to visit the actual data.

In SQL Server 2000 the term "Bookmark Lookup" was used to describe the process of retrieving some of the columns from the actual row. In my experience, this is a very expensive operation when performed on large tables. Now "Clustered Index Seek" for tables with a clustered index, and "RID Lookup" for tables without a clustered index are the terms used. I find this very confusing since "Clustered Index Seek" is usually the preferred behavior. In Books Online, it states that when the keyword "LOOKUP" appears, it is actually a bookmark lookup. I haven't found the term displayed in the Graphical Execution plan. To see the keyword, you have to use the SHOWPLAN_TEXT option.

Dropping a Index

DROP INDEX EMPLOYEES.EMPX