RANDBETWEEN(m,n) in Sql Server?

Today I have to find random number between two numbers lots of the time in the stored procedures So I tried to create a function which return random numbers between two boundary numbers m and n. I tried to look for the solution and got below logic to implement the function.

Let m = 5 and n = 500 and method call would be RANDBETWEEN(5, 500). Then logic to find the random number would be as below:

• Use RAND() (which returns a value between 0 and 1 (exclusive).
• multiply by 298 (since you want a dynamic range of [300-3] = 297 + 1)
• add 3 to Offset
• and cast to INT?

Usage:

SELECT CAST(RAND() * 298 + 3 AS INT)

A Stored Procedure can be written like this if it supposed to be reuse in code more often:

CREATE PROCEDURE [dbo].[RANDBETWEEN]
@LowerBound int = 0 ,
@UpperBound int = 1 , 
@ret int OUT
AS
    BEGIN
    SET NOCOUNT ON;
    SELECT @ret = (CAST((RAND() * (@UpperBound - @LowerBound)) + @LowerBound AS INT));
RETURN ;
END;

Call can be made like this:

DECLARE @tmp INT;
EXECUTE [dbo].[RANDBETWEEN] 0,10, @ret=@tmp OUT ;
SELECT @tmp

To create a function I found that I have to create a View that make random method call and then function will do remaining operation to get the random number.

CREATE VIEW Get_RAND
AS
SELECT RAND() AS RANDNumber
GO

Then you can create a function like this (accessing the view with the SELECT RandomNumber... ) :

CREATE FUNCTION RANDBETWEEN(@LowerBound INT, @UpperBound INT)
RETURNS INT
AS
BEGIN
    DECLARE @TMP FLOAT;
    SELECT @TMP = (SELECT RandomNumber FROM Get_RAND);
    RETURN CAST(@TMP* (@UpperBound - @LowerBound) + @LowerBound AS INT);
END
GO

Then this function can be called as below:

SELECT [dbo].[RANDBETWEEN](1,10)

CAST Data-Type Conversions in SQL

 

May be you have  covered the data types that SQL recognizes and supports. Ideally, each column in a
database table has a perfect choice of data type. In this non-ideal world, however, exactly what that
perfect choice may be isn't always clear. In defining a database table, suppose you assign a data type
to a column that works perfectly for your current application. Later, you want to expand your
application's scope or write an entirely new application that uses the data differently. This new use
could require a data type different from the one you originally chose.

You may want to compare a column of one type in one table with a column of a different type in a
different table. For example, you could have dates stored columns contain the same things (dates, for
example), the fact that the types are different may prevent you from making the comparison. In SQL-86
and SQL-89, type incompatibility posed a big problem. SQL-92, however, introduced an easy-to-use
solution in the CAST expression. The CAST expression converts table data or host variables of one type
to another type. After you make the conversion, you can proceed with the operation or analysis that you
originally envisioned.

Naturally, you face some restrictions when using the CAST expression. You can't just indiscriminately

convert data of any type into any other type. The data that you're converting must be compatible with
the new data type. You can, for example, use CAST to convert the CHAR(10) character string '1998-04-26'
to the DATE type. But you can't use CAST to convert the CHAR(10) character string 'rhinoceros' to the
DATE type. You can't convert an INTEGER to the SMALLINT type if the former exceeds the maximum

size of a SMALLINT.

You can convert an item of any character type to any other type (such as numeric or date) provided that
the item's value has the form of a literal of the new type. Conversely, you can convert an item of any
type to any of the character types, provided that the value of the item has the form of a literal of the

original type.

The following list describes some additional conversions you can make:

� Any numeric type to any other numeric type. If converting to a type of less fractional precision, the
    system rounds or truncates the result.

� Any exact numeric type to a single component interval, such as INTERVAL DAY or INTERVAL SECOND.

� Any DATE to a TIMESTAMP. The time part of the TIMESTAMP fills in with zeros.

� Any TIME to a TIME with a different fractional-seconds precision or a TIMESTAMP. The date part of the
    TIMESTAMP fills in with the current date.

� Any TIMESTAMP to a DATE, a TIME, or a TIMESTAMP with a different fractional-seconds precision.

� Any year-month INTERVAL to an exact numeric type or another yearmonth INTERVAL with different
    leading-field precision.

� Any day-time INTERVAL to an exact numeric type or another day-time INTERVAL with different leading-
    field precision.

Using CAST within SQL

Suppose that you work for a sales company that keeps track of prospective employees as well as
employees whom you've actually hired. You list the prospective employees in a table named PROSPECT,
and you distinguish them by their Social Security numbers, which you store as a CHAR(9) type. You list

the employees in a table named EMPLOYEE, and you distinguish them by their Social Security numbers,
which are of the INTEGER type. You now want to generate a list of all people who appear in both tables.
You can use CAST to perform the task, as follows:

SELECT * FROM EMPLOYEE

WHERE EMPLOYEE.SSN =

CAST(PROSPECT.SSN AS INTEGER) ;

Using CAST between SQL and the host language
The key use of CAST is to deal with data types that are in SQL but not in the host language that you use.
The following list offers some examples of these data types:

� SQL has DECIMAL and NUMERIC, but FORTRAN and Pascal don't.

� SQL has FLOAT and REAL, but standard COBOL doesn't.

� SQL has DATETIME, which no other language has.

Suppose that you want to use FORTRAN or Pascal to access tables with DECIMAL(5,3) columns, and
you don't want the inaccuracies that result from converting those values to the REAL data type of
FORTRAN and Pascal. You can perform this task by CASTing the data to and from characterstring

host variables. You retrieve a numeric salary of 198.37 as a CHAR(10) value of '0000198.37'. Then if
you want to update that salary to 203.74, you can place that value in a CHAR(10) as '0000203.74'.
First, you use CAST to change the SQL DECIMAL(5,3) data type to the CHAR(10) type for the employee
whose ID number you're storing in the host variable :emp_id_var, as follows:

SELECT CAST(Salary AS CHAR(10)) INTO :salary_var

FROM EMP

WHERE EmpID = :emp_id_var ;

Then the application examines the resulting character string value in :salary_var, possibly sets the string
to a new value of '000203.74', and then updates the database by using the following SQL code:

UPDATE EMP

SET Salary = CAST(:salary_var AS DECIMAL(5,3))

WHERE EmpID = :emp_id_var ;

Dealing with character-string values like '000198.37' is awkward in FORTRAN or Pascal, but you can write
a set of subroutines to do the necessary manipulations. You can then retrieve and update any SQL data
from any host language and get and set exact values.

The general idea is that CAST is most valuable for converting between host types and the database rather
than for converting within the database.

 

an Simple example of conversion is as:

use NIR

declare @as int;

select @as= dbo.emp.Salary from dbo.emp where dbo.emp.Id=1;

 

declare @ds varchar(22);

set @ds= CAST(@as as varchar(22));

print @ds;

 

Triggers in SQL Server

DDL Triggers

DDL triggers respond to an event associated with a Data Definition Language

(DDL) statement. These DDL statements are

� CREATE

� ALTER

� DROP

You use DDL triggers for the following purposes:

� To prevent changes being made to a schema

� To log who makes changes to a schema

� To respond in some desired way to changes made in a schema

 

Preventing undesired changes

The following example shows you how to prevent undesired changes being made to the tables of the
TriggerImpl database.

1. Use SQL Server Management Studio to connect to the desired SQL Server 2005 instance.

2. Click the New Database Query button.

3. Ensure you're using the TriggerImpl database:

USE TriggerImpl

4. Create a trigger to prevent changes being made to the TriggerImpl

database:

CREATE TRIGGER PreventChanges

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE

AS
PRINT 'Making alterations to the TriggerImpl database

is not permitted.'

PRINT 'To make changes you must disable this DDL

trigger.'

ROLLBACK

The first line provides a name for the trigger. The second line specifies that the trigger apply the database
changes. A trigger is bound to a database object; in this case, the current database, TriggerImpl . The third

line specifies that the trigger executes for DROP TABLE, ALTER TABLE, and CREATE TABLE statements. The
FOR keyword indicates that the trigger runs before the DDL statement executes.

5. Attempt to create a new table called DDLTriggerTest:

CREATE TABLE DDLTriggerTest

(ID int PRIMARY KEY,

SomeColumn varchar(30))

An error message displays.

6. Attempt to drop the dbo.ConstraintTest table that you created earlier in this chapter:

DROP TABLE dbo.ConstraintTest

The attempted change is prevented with a message similar to the message shown in previous step.

7. Drop the trigger:

DROP TRIGGER PreventChanges

ON DATABASE

8. Retry creating the DDLTriggerTest table, which failed in Step 5:

CREATE TABLE DDLTriggerTest

(ID int PRIMARY KEY,

SomeColumn varchar(30))

Because you dropped the trigger in Step 7, you can now successfully create the DDLTriggerTest table.

Auditing changes

Another use of a DDL trigger is to log how and when changes are made indat abase or table structure.

In the following example, I show you how to create a DDL trigger for the ALTER TABLE statement.
Follow these steps:

1. Ensure you are using the Chapter14 database:
    USE nir

2. Create a table called AuditedTable. Later you monitor this table for

    changes in its structure made by using the ALTER TABLE statement.

    CREATE TABLE AuditedTable

    (MessageID int PRIMARY KEY,

    Message varchar(100))

3. Insert a sample row into the AuditedTable table.

    INSERT INTO AuditedTable

    VALUES (1, 'Hello World!')

4. Confirm that the row has been inserted .

    SELECT *

    FROM AuditedTable

5. Create a table DDLAudit to contain the information used for auditing.

    Using a TIMESTAMP column allows easy monitoring of the sequence of alterations made:

    CREATE TABLE DDLAudit

    (

    Changed TIMESTAMP,

    DateChanged DateTime,

    TableName char(30),

    UserName varchar(50)

    )

6. Confirm that the DDLAudit table has been created and is empty.

    SELECT *

    FROM DDLAudit

7. Insert a sample row manually into the DDLAudit table.

    INSERT INTO DDLAudit (DateChanged, UserName)

    VALUES (GetDate(), 'John Smith')

8. Confirm that the sample row has been inserted.

    SELECT *

    FROM DDLAudit

9. Create a trigger named AuditDDL, which responds to an ALTER TABLE statement.

Notice that in the FOR clause, you write ALTER_TABLE with an underscore.

Notice too that the GetDate() function is used to retrieve the date and time when the row is inserted
into the DDLAudit table. The suser_sname() function is used to retrieve the system name of the

user making the change in the table schema.

CREATE TRIGGER AuditDDL

ON DATABASE

FOR ALTER_TABLE

AS

INSERT INTO dbo.DDLAudit(DateChanged,

TableName, UserName)

SELECT GetDate(), 'AuditedTable', suser_sname()

-- End of Trigger

 

The trigger now responds to any attempt to use the ALTER TABLE statement to alter the structure of the
AuditedTable table.

10. Use the following code to attempt to add an additional column to the AuditedTable table.

ALTER TABLE AuditedTable

ADD Comment varchar(30)

11. Inspect the content of the DDLAudit table.

SELECT *

FROM DDLAudit

DML Triggers

A DML trigger is executed in response to an event associated with a Data Modification Language (DML)
statement. A DML trigger is associated with one of the following statements:

� INSERT

� UPDATE

� DELETE

You can use DML triggers either to replace a DML statement or to execute after a DML statement. A
trigger that replaces a DML statement is called an INSTEAD OF trigger. A trigger that executes after
a DML statement is called an AFTER trigger.

The inserted and deleted tables

SQL Server automatically manages the deleted and inserted tables. If you delete rows from a table, the
deleted table contains a row that matches the rows deleted from the other table. Similarly, if you update
a row, the deleted table contains a row with the old values. When you execute an UPDATE, values are
inserted into both the inserted and deleted tables.

If you insert data into a table, a copy of that row or those rows is contained in the inserted table.

You can use the inserted and deleted tables to determine what kind of change has been made to the
data, as I show you in the next section.

Triggers for auditing DML

A common use for DML triggers is to record, for audit purposes, changes made to data. The following
steps show you how to create a DML trigger to store information about who changed data:

1. Open a new database engine query in SQL Server Management Studio.

2. Ensure you are working in the NIR database.

    USE NIR

3. Create a table to store messages called DMLAuditedTable:

   CREATE TABLE DMLAuditedTable

    (MessageID int PRIMARY KEY,

    Message varchar(100))

    This is the table you want to audit.

4. Enter a sample value in the DMLAuditedTable table:

    INSERT INTO DMLAuditedTable

    VALUES (1, 'Hello World!')

5. Confirm the successful INSERT operation:

    SELECT *

    FROM DMLAuditedTable

6. Create a table, DMLAudit, to store the audit information:

    CREATE TABLE DMLAudit

    (

    Changed TIMESTAMP,

    DateChanged DateTime,

    TableName char(30),

    UserName varchar(50),

    Operation char(6)

    )   

The changed column is of type TIMESTAMP to store information about the sequence in changes made
to the DMLAuditedTable table. In the Operation column, you store information about whether the DML

change was an INSERT or an UPDATE operation.

7. Enter a sample row manually into the DMLAudit table:

INSERT INTO DMLAudit (DateChanged, UserName)

VALUES (GetDate(), 'John Smith')

8. Confirm the successful INSERT operation into the DMLAudit table:

    SELECT *

    FROM DMLAudit

9. Create a DML trigger called AuditDML:

    CREATE TRIGGER AuditDML

    ON dbo.DMLAuditedTable

    AFTER INSERT, UPDATE

-- NOT FOR REPLICATION

AS

DECLARE @Operation char(6)

IF EXISTS(SELECT * FROM deleted)

SET @Operation = 'Update'

ELSE

SET @Operation = 'Insert'

INSERT INTO dbo.DMLAudit(DateChanged,

TableName, UserName, Operation)

SELECT GetDate(), 'DMLAuditedTable', suser_sname(),

@Operation

-- End of Trigger

Notice the IF clause that uses information from the deleted table to determine whether the operation is
an UPDATE or an INSERT. That information is stored in the @Operation variable. The GetDate() function

retrieves the data and time of the operation and the suser_sname() function retrieves the username.
The Operation column stores the value in the @Operation variable.

10. Test whether the DML trigger responds to an INSERT operation on the

    DMLAuditedTable table by using the following code:

    INSERT INTO DMLAuditedTable

    VALUES (2, 'To be or not to be, that is the

    question.')

11. Execute a SELECT statement on the DMLAudit table to confirm that

    the INSERT operation has been executed:

    SELECT *

    FROM dbo.DMLAudit

12. Execute an UPDATE statement against the DMLAuditedTable table:

    UPDATE DMLAuditedTable

    SET Message = 'Goodbye World!'

    WHERE MessageID = 1

13. Test whether the AuditDML trigger has added a row to the DMLAudit table by using the following code:

    SELECT *

    FROM DMLAudit

output window shows that the UPDATE operation also caused a row to be added to the DMLAudit table.
Notice that the value in the Operation column is Update.

The information you store in an audit table can be much more extensive than shown in this example.
The scope is limited only by your knowledge of T-SQL and your business setting.

Maintaining Data Integrity with Constraints and Triggers

Maintaining the integrity of the data in a SQL Server 2005 instance is crucially important to the reliable
operation of your business that uses SQL Server data. SQL Server uses several mechanisms, including
constraints and triggers, to help ensure data integrity. In this chapter, I tell you about constraints and
triggers that are tools to help maintain data integrity in SQL Server 2005.

A constraint is a rule that is enforced by SQL Server 2005. Microsoft suggests that, in SQL Server 2005,

constraints are the preferred way to enforce business rules.

A trigger is a special kind of stored procedure that executes in response to an event inside SQL Server.
A common use of triggers is to create an audit trail. For example, suppose you want to keep an audit
trail of who makes changes to prices in your online store. Each time someone modifies a row in the
relevant table, a trigger executes, which could store information such as the timeof the change, who
made the change, what the original price was, and what the new price is. Such information allows your

business to monitor trends in prices and also to find out who made any possibly wrong changes in price.

Constraints, Defaults, Rules, and Triggers

In this section, I describe constraints, defaults, rules, and triggers, which provide a range of ways to
enforce business rules inside SQL Server databases. In this article, I create a simple database by using

the following code.

Examples later in this aritcle use the ConstriggerDB database.

CREATE DATABASE ConstriggerDB

Constraints

Constraints (rules enforced by SQL Server 2005) provide a key way to ensure several aspects of data
integrity. Microsoft recommends that you use constraints rather than triggers in SQL Server 2005 to
ensure data integrity. The constraints supported in SQL Server 2005 are

� Primary key: Provides a way to uniquely identify each row in a table. A primary key constraint is a
    specialized form of a unique constraint.

� Unique constraint: Specifies that each value in a column is unique. One difference between a
    unique constraint and a primary key constraint is that a column with a unique constraint can contain
    NULL values, which are not permitted in a column that has a primary key constraint. If a column is a
    primary key or part of a primary key, you cannot also set up a unique constraint for that column.

� Check constraint: Specifies rules that values in a column must obey. A check constraint uses an
    expression to define the permitted values in a column. Later in this chapter, I show you how to define
    check constraints on a column.

Defaults

A default is a database object that you define and bind to a column. If during an insert operation, you
don't supply a value for a column to which the default is bound, then the default is inserted into that
column.

The following example creates a default of Unknown for values inserted into a table that records student
grades.

First, specify that you use the ConstriggerDB database:

USE ConstriggerDB

Then create the default called StudentGradeUnknown, specifying that it is the string Unknown:

CREATE DEFAULT StudentGradeUnknown AS 'Unknown'

Then create a simple table to store student grades:

CREATE TABLE StudentGrades

(StudentID int PRIMARY KEY,

Examination varchar(10),

Grade varchar(7))

At this stage, the StudentGradeUnknown default exists in the ConstriggerDB database. You need to bind
it to the Grade column in the StudentGrades table. Use this code, which makes use of the sp_bindefault
system stored procedure:

sp_bindefault 'StudentGradeUnknown', 'StudentGrades.Grade'

GO

To confirm that the default operates, use the following INSERT statement. Notice that no value is supplied
for the Grade column:

INSERT INTO StudentGrades(StudentID, Examination)

VALUES(1, 'XML101')

Also, insert a row where you supply a value in the Grade column:

INSERT INTO StudentGrades

VALUES(2, 'SVG101', 'A')

You can confirm the values in the StudentGrades table by using the following

code:

SELECT * FROM StudentGrades

In the first row shown in output, the value Unknown in the Grade column was supplied by the default bound
to that column. In the second row, you supplied a value for the Grade column so the default was not used.

Rules

Rules are included in SQL Server 2005 for backwards compatibility with SQL Server 2000. Check constraints
in SQL Server 2005 provide similar functionality.

Microsoft recommends that you use check constraints rather than rules in new code.

The following example that demonstrates how to create and use a rule uses a test table called TestTable in
the ConstriggerDB database, which I created with this code:

USE ConstriggerDB

CREATE TABLE TestTable

(ID int PRIMARY KEY,

Data char(1))

To create a rule in the ConstriggerDB database, use the following code:

CREATE RULE aThroughcOnly

AS

@ruleval >= 'a' AND @ruleval <= 'c'

The preceding rule specifies that the value must be lowercase, between lowercase a and lowercase c. You
also need to bind the rule you have created to a column. The following code binds it to the Data column in
the TestTable table:

sp_bindrule aThroughcOnly, 'TestTable.[Data]'

 

To insert a row with an allowed value, use the following code:

INSERT INTO dbo.TestTable

VALUES (1, 'b')

You should be prevented from entering a value that doesn't correspond to the rule you created. The following
attempts to insert a disallowed character in the Data column.

INSERT INTO dbo.TestTable

VALUES (2, 'd')

After you create a rule, you're likely to leave it in place unless you want to convert it to a constraint as
described in the preceding section. To unbind a rule from a specified column, use the following code:

sp_unbindrule 'dbo.TestTable.Data'

Triggers

Triggers are used to help maintain data integrity and enforce business rules. (Remember that a trigger is a
special kind of stored procedure that executes in response to an event inside SQL Server.) They complement
the protection of data integrity that constraints, defaults, and rules can provide. A trigger is a specialized
stored procedure. Unlike regular stored procedures, you cannot use an input parameter with a trigger, nor

can a trigger return a value. A trigger is associated with a particular table. When a specified event occurs,
the trigger executes.

Triggers are broadly divided into two groups:

� DDL triggers: Data Definition Language triggers

� DML triggers: Data Modification Language triggers

Triggers are classified as follows:

� INSTEAD OF triggers: These execute instead of the statement to which

    they are related.

� AFTER triggers: These execute after the statement to which they are

    related.

 

I describe and demonstrate several of these types of triggers later in another article soon.

 

 

 

How to Maintain Integrity using Transactions

Many business activities depend on an action being accompanied by a corresponding action.
For example, if you are a customer and pay for goods but don't receive them, something is wrong.
Similarly, if you take goods and don't pay for them, again, something is wrong. The expected typical

scenario is a transaction where you pay for goods and you receive the goods. More than one action
is required to make up a transaction.

Another common example of more than one operation making up a transaction is when you transfer
money from one bank account to another. Suppose you're transferring a regular payment to a company.

Your bank takes the money out of your account and puts it into the account of the company or person
that you're paying. You would be annoyed if the money was taken out of your account and didn't reach
the account where it was supposed to go. If the money was never transferred to the company's account
you, as a customer of a bank, would not be happy whether the money was put in the wrong account or
just disappeared. The different parts of that transaction must be kept together. There are two possible
scenarios:
No money is taken from your account and nothing is transferred to the

    other account (possibly because of insufficient funds in your account or

    a network problem is preventing the transfer).

The right amount of money is taken from your account and is placed in

    the other account.

In a SQL Server transaction, either all the component parts of a transaction are carried out or none of
them are. This concept is called atomicity.

Transactions

In SQL Server 2005, there are several levels of transaction. In the preceding paragraphs, I mention
business level transactions. These are the subject of this chapter. Behind the scenes in SQL Server 2005,
other transactions take place routinely. For example, if you add data to a table that has an index, both
the table and the index need to be updated or neither is updated. If that coordination of operations

doesn't happen, then the index and table are inconsistent with each other, which is unacceptable.

ACID

ACID describes four essential characteristics of a transaction:

Atomicity: Atomicity means that the transaction cannot be divided and still make sense. With the
    transfer between bank accounts, either both parts of the transaction take place successfully or neither
     happens.

Consistency: Consistency means that the database is in a consistent state before the transaction
    takes place and remains in a consistent state after the transaction. For example, if you add a row to
    a table, then the index must also be updated.

Isolation: This is the idea that a transaction should be able to proceed as if it were completely
    isolated from any other transaction. For multi-userdatabases, it is increasingly important that the
    product supports this.

Durability: This is the concept that a transaction survives even if there is a hardware failure. It
    should be possible to re-create the data up to thelast completed transaction that completed a split
    second before the hardware failure.

The transaction log
Each SQL Server 2005 database has an associated transaction log. The transaction log contains
information about recent changes that have been made in the database that have not yet been
committed to disk. When SQL Server is restarted, any transactions not yet committed to disk are
committed to disk during startup. This facility supports durability of the ACID acronym that I discuss
in the preceding section.

Coding Transactions

When a transaction involves, for example, removing money from one account and transferring it to
another account, then both accounts are updated. First,look at how SQL Server carries out a simple update.

A simple update

Imagine that you have a database called Departments that has columns, which include DepartmentName
and DepartmentManager. When the manager of the IT department is replaced, you need to update the
information inthe DepartmentManager column. To do this, you use code like the following:

UPDATE Departments

SET DepartmentManager = 'John Smith'

WHERE Department = 'IT'

The WHERE clause works much as it does in a SELECT statement. It selectsthe rows where the
Department column contains the IT value and the SETclause causes the value in the
DepartmentManager column to update to the John Smith value.

A simple transaction

To demonstrate a simple transaction, I create a database called TransactionDemo. In that database,
I create a couple of tables called PersonalAccount and CompanyAccount:

CREATE DATABASE TransactionDemo

USE TransactionDemo

CREATE TABLE PersonalAccount (AccountID INT PRIMARY KEY,

Name VARCHAR(30), BALANCE MONEY)

I create two accounts, one for John Smith and one for Jane Doe, with each individual having a balance
of $100.00:

INSERT INTO PersonalAccount

VALUES (1, 'John Smith', 100.00)

INSERT INTO PersonalAccount

VALUES (2, 'Jane Doe', 100.00)

Similarly, I create a CompanyAccount table:

CREATE TABLE CompanyAccount (AccountID INT PRIMARY KEY,

Name VARCHAR(30), BALANCE MONEY)

Then I create two rows in it, with each company having a balance of $10,000:

INSERT INTO CompanyAccount

VALUES (1, 'Acme Company', 10000.00)

INSERT INTO CompanyAccount

VALUES (2, 'XMML.com', 10000.00)

To confirm that the two tables have been created with appropriate values in

each column, use the following code:

SELECT * FROM PersonalAccount

SELECT * FROM CompanyAccount

The following code transfers $50.00 from John Smith's personal account to

XMML.com's company account by using a transaction:

BEGIN TRANSACTION

UPDATE PersonalAccount

SET BALANCE = 50.00

WHERE Name = 'John Smith'

UPDATE CompanyAccount

SET BALANCE = 10050.00

WHERE Name = 'XMML.com'

COMMIT TRANSACTION

GO

Confirm that the balance in John Smith's personal account and XMML.com's company account have
changed appropriately by using the following code:

SELECT * FROM PersonalAccount

SELECT * FROM CompanyAccount

Often, a transaction has some error checking included in the code. To include error checking when
making a transfer from Jane Doe to Acme Company, you can use @@ERROR:

BEGIN TRANSACTION

UPDATE PersonalAccount

SET BALANCE = 0.00

WHERE Name = 'Jane Doe'

IF @@ERROR <> 0

PRINT N'Could not set balance in PersonalAccount.'

UPDATE CompanyAccount

SET BALANCE = 10100.00

WHERE Name = 'Acme Company'

IF @@ERROR <> 0

PRINT N'Could not set balance in CompanyAccount.'

COMMIT TRANSACTION

GO

To confirm that you have changed the row for Jane Doe in the Personal Account table and the row for Acme
Company in the CompanyAccount table, use the following code:

SELECT * FROM PersonalAccount

SELECT * FROM CompanyAccount

The BEGIN TRANSACTION statement marks the beginning of the T-SQL code to be treated as a transaction.
If the T-SQL code in the transaction executes successfully, the COMMIT TRANSACTION statement is
reached and the transaction is committed.

If an error occurs during processing, the ROLLBACK TRANSACTION statement executes. After the COMMIT
TRANSACTION statement executes, you cannot use a ROLLBACK TRANSACTION statement to roll back the
transaction.

Implicit transactions

The preceding examples showed explicit transactions. T-SQL also supports implicit transactions.

To start implicit transactions, you use the SET IMPLICIT_TRANSACTIONS ON statement. Each statement after that statement until a transaction is committed is considered to be part of that transaction. After you do that,you must explicitly commit the statements that make up each transaction by using the COMMIT

TRANSACTION statement. The statements after the COMMIT TRANSACTION statement are considered to be the first statement of the next transaction. Again, that transaction must be explicitly committed.

To turn implicit transactions off, you use the SET IMPLICIT_TRANSACTIONS OFF statement. The default behavior, if you do not SET IMPLICIT TRANSACTIONS ON, is that each individual T-SQL statement is treated as a transaction rather than as a group of T-SQL statements.

You can't combine Data Definition Language (DDL) statements in a single transaction.

Making Availability of Data and Preventing Data Loss in SQL Server

To keep database secure and reliable then here is some expect that make sql server to most

power database management system in microsoft technologies:

• Keeping your hardware secure
• Taking advantage of database mirroring
• Creating checkpoints
• Keeping your database running with clustering
• Producing database snapshots
• Backing up and restoring data

In a connected world, your colleagues and customers need almost continuous

access to data. This means that you need to avoid users temporarily

losing access to data. Or, if you can't completely avoid such temporary problems,

make sure that you can recover from them quickly. More important,

you must take careful steps to ensure that the chance of users permanently

losing access to data is as close to zero as possible.

SQL Server 2005 supports many features that improve the chances of keeping

your SQL Server databases available to users. For example, database mirroring

is a new feature that allows almost instant switching to a backup SQL

Server if a primary server goes down.

As well as achieving high availability of data, it is crucially important that you

avoid the permanent loss of any business data that is needed for the running

of your business. Losing important business data can be fatal for your continued

employment and can, in some cases, also be fatal for the business.

Taking appropriate steps to back up data and ensure that you can restore it is

of enormous importance.

Reducing Downtime with Database Mirroring

Database mirroring is an option to improve the availability of a SQL Server

database or databases. Database mirroring is new in SQL Server 2005. You

choose to mirror the databases on a SQL Server instance on a database-bydatabase

basis.

Note:

Database mirroring was intended to be available in the November 2005

release of SQL Server 2005. Microsoft has delayed support of the database

mirroring feature in a production environment, although you can enable it in

the November 2005 release for evaluation purposes by using trace flag 1400.

Microsoft recommends that you do not use database mirroring in the original

release in a production environment.

Database mirroring overview

You have three server machines in a common setup for database mirroring.

One machine (the principal) has the copy of a database that applications read

and write to. Another machine (the mirror) has a copy of the principal database.

The mirror database is kept almost instantaneously in synch with the

principal database via a network connection. All transactions that are applied

to the principal database are also applied to the mirror database.

You might wonder how, with two copies of the data, applications know which

copy of the database to read and write to. The third machine is a witness and

has the "casting vote" as to which of the other two machines is running the

principal database.

Database mirroring gives very fast switching if the principal database

becomes unavailable. Typically, it takes less than three seconds to be up and

running again, using the mirror database. Many users don't notice an interruption

of response; at the most, perhaps just a slightly slower response than

normal.

Microsoft claims zero data loss for database mirroring. Transactions are sent

to the mirror database's log at the same time as they are written to the principal

database's log. The chances of any transaction being lost on the mirror

are extremely low.

Note: You cannot mirror the master, msdb, tempdb, or model databases.

You can switch control to the mirror database either manually or automatically.

Given that one of the advantages of database mirroring is the really

rapid switching that can occur automatically, I envisage automatic switching

being the typical scenario.

Another useful feature of database mirroring is that any changes that are

made on the new principal database (the former mirror database) are automatically

synchronized with the former principal database when the former

principal server is available again.

You can use database mirroring together with replication. For example, if

you're replicating the data from a headquarters SQL Server instance to

branch offices, all or any of the headquarters or branch office instances to

which replication takes place can be a database mirroring configuration.

While replication and database mirroring are separate processes, you can, in

appropriate circumstances, usefully combine them.

 

Transparent client redirect

Database mirroring depends on a companion new technology on the client

side that is called transparent client redirect. Essentially the client knows

about both the principal database and the mirror database. While the principal

database is working correctly, the client only connects to it. When the

principal database fails and the former mirror database becomes the new

principal database, the client automatically connects to the new principal

database.

Database views

You can use another new feature, called database views, with database mirroring.

Database views allow you to make read-only use of the mirror database.

The mirror database is only minimally out of synchronization with the

principal database, because the transaction log of the principal database is

immediately sent to and applied to the mirror database. For any data

retrieval that doesn't require absolutely up-to-date, real-time information, the

mirror database is satisfactory. Any database access that involves writing to

the database must use the principal database.

One important potential use of database views is as the data source for

Reporting Services. Because reporting requires only read access to the database,

you can retrieve any data you need while taking some load off the principal

database.

 

Differences from failover clustering

I list here some key differences between database mirroring and failover clustering:

 

� Database mirroring allows failover at the database level. Failover clustering

   allows failover at the server level or SQL Server instance level.

� Database mirroring works with standard computers, standard storage,

    and standard networks. Failover clustering requires specific, certified

    hardware.

� Database mirroring has no shared storage components. Failover clustering

    uses shared hard drives.

� Database mirroring allows Reporting Services to run against the

    mirror database. Reporting Services cannot be run against a currently

    inactive node in a failover cluster.

� Database mirroring has two (or more) copies of a database. Failover

    clustering works with a single copy of databases, which are stored on

    shared hard drives.

� Database mirroring is much faster than failover clustering. Typical figures

    might be 3 seconds versus 60 seconds, although exact figures

    depend on various factors specific to your setup.

 

Similarities to failover clustering

The following points apply to both database mirroring and failover clustering:

� Both support automatic detection and failover.

� Each has a manual failover option.

� Each supports transparent client connection to the backup database or

    server.

� Each achieves "zero" work loss.

� Database views minimize the effects of DBA or application errors.

 

 

Dr. Codd's 12 Rules for a Relational Database Model;

The most popular data storage model is the relational database, which grew from the

seminal paper "A Relational Model of Data for Large Shared Data Banks," written by

Dr. E. F. Codd in 1970. SQL evolved to service the concepts of the relational database

model. Dr. Codd defined 13 rules, oddly enough referred to as Codd's 12 Rules, for the

relational model:

0. A relational DBMS must be able to manage databases entirely through its

relational capabilities.

1. Information rule-- All information in a relational database (including table

and column names) is represented explicitly as values in tables.

2. Guaranteed access--Every value in a relational database is guaranteed to be

accessible by using a combination of the table name, primary key value, and

column name.

3. Systematic null value support--The DBMS provides systematic support for the

treatment of null values (unknown or inapplicable data), distinct from default

values, and independent of any domain.

4. Active, online relational catalog--The description of the database and its

contents is represented at the logical level as tables and can therefore be

queried using the database language.

5. Comprehensive data sublanguage--At least one supported language must have a

well-defined syntax and be comprehensive. It must support data definition,

manipulation, integrity rules, authorization, and transactions.

6. View updating rule--All views that are theoretically updatable can be updated

through the system.

7. Set-level insertion, update, and deletion--The DBMS supports not only setlevel

retrievals but also set-level inserts, updates, and deletes.

8. Physical data independence--Application programs and ad hoc programs are

logically unaffected when physical access methods or storage structures are

altered.

9. Logical data independence--Application programs and ad hoc programs are

logically unaffected, to the extent possible, when changes are made to the table

structures.

10. Integrity independence--The database language must be capable of defining

integrity rules. They must be stored in the online catalog, and they cannot be

bypassed.

11. Distribution independence--Application programs and ad hoc requests are

logically unaffected when data is first distributed or when it is redistributed.

12. Nonsubversion--It must not be possible to bypass the integrity rules defined

through the database language by using lower-level languages.