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

View

VIEW

View can be defined as a virtual table which is created on a table whatever changes (SELECT, UPDATE, INSERT, DELETE). You make against a view will be reflected on the associated table as well. View does not occupies any memory space.

[A view is quite easy to define, but a lot more difficult to create, use and manage. It’s not anymore than a named SELECT statement, or a virtual table. You can select data, alter data, remove data and all other things you can do with a table (with some limitations). But there is a difference between a view and a table. The data accessible through a view is not stored in the database as its own object. It’s stored in the underlying tables that make up the view. The only thing that is stored in the database is the SELECT statement that forms the virtual table ]

Advantages of using views

As you could see in the previous section, a view is very useful. Here are some other scenarios when a view can be very useful.

• Restrict data access and/or simplify data access
  A view can be used to limit the user to only use a few columns in a table. For example if we do not want a user to be able to access all columns because of security. But it could also be because not all columns are interesting for the user. It is also possible to limit access to rows, using a WHERE clause. If we use USER_ID(), we can even find out who is using the view, and return only data that is relevant for this user. Furthermore, a view can join several tables, in several databases on several servers, but all the user use is the view’s name. Simple, but powerful!
• Simplify data manipulation
  We can also use a view to manipulate data. Often with a relational database design, this means that many tables must be joined together. Using a view can simplify this, and the users do not need to know all tables involved.
• Import and export data
  A view can also be used to export and/or import data to/from another application. Both the bcp utility and BULK INSERT works with a view.
• Merge data
  A so called Partition View can be used to merge data from multiple tables in multiple databases, so it appears as one table only, but it is in fact several tables. This can be accomplished by using the UNION operator. For example if we had customers in Europe and United States, we could have one server for Europe, and one for United States, with tables that are identical, and then merge all data in a partitioned view. More on this later.

Syntax

CREATE VIEW AS QUERY

CREATE VIEW EMPV AS SELECT * FROM EMPLOYEES

CREATE VIEW EMPV AS SELECT ENAME, DGN, SAL FROM EMPLOYEES WHERE DEPTNO=20

Changing Columns names in a view

CREATE VIEW EMPV (EEMPNAME, DEISGNATION, SALARY) AS SELECT ENAME, DGN, SAL FROM EMPLOYEES WHERE DEPTNO=20

Dropping a View

DROP VIEW EMPV

Sub Query

SUB QUERY

Query inside a Query. (Select statement in select statement) The advantage of sub query is that reduces Network Traffic.

Table Employees

ENO	ENAME	DESIGNATION	SAL	DEPTNO
1	MANU	MANAGER	44000	10
2	VIVEK	ACCOUNTANT	14000	20
3	ANU	CLEARK	4000	20
4	BIJU	PROGRAMMER	24000	30
5	RENU	MANAGER	30000	20
6	JOY	CLEARK	3500	10
7	UNNI	MANAGER	40000	30
8	VANI	GENERAL MANAGER	40000	20

SELECT * FROM EMPLOYEES WHERE DGN= (SELECT DGN FROM EMPLOYEES WHERE ENAME=JOY)

Note: SELECT * FROM EMPLOYEES WHERE DGN= is Main Query or Outer Query

(SELECT DGN FROM EMPLOYEES WHERE ENAME = ANU) is Sub query or Inner Query.

Multiple Sub Query

SELECT * FROM EMPLOYEES WHERE DGN IN (SELECT DGN FROM EMPLOYEES WHERE ENAME=’MANU’) AND SAL= (SELECT SAL FROM EMPLOYEES WHERE ENAME = ‘VANI’)

Multi Level Sub Query- Sub Query inside a sub query is multilevel sub query.

Correlated Sub Query

Is a type of sub Query which is executed once for each row processed by the main Query. The execution of the Sub Query co-related to the candidate row of the main Query.

SELECT * FROM EMPLOYEES AS E WHERE E.SAL>(SELECT AVG(SAL) FROM EMPLOYEES WHERE E.DEPTNO=DEPTNO)

Check the Average

SELECT DEPTNO, AVG(SAL) FROM EMPLOYEES GROUP BY DEPTNO.