Transact SQL Homework Help, Assignment Help, Online Tutor, Tutoring

Submit Assignment

* *

Payment Online Assignment Help

Asp dot net Project Homework

Homework Help

sfsf

Transact SQL

SQL and the Introduction of Transact-SQL

SQL, on which Transact-SQL is based, started life in the mid-1970s as an IBM product called SEQUEL. SEQUEL stood for Structured English Query Language. After a permutation or two and some legal problems, IBM changed the name to SQL--the Structured Query Language. The language was designed to provide a standard method for accessing data in a relational database. Ironically, although IBM introduced SQL, Oracle was the first to bring a SQL-using product to market.

Today, many different relational database systems utilize SQL as the primary means for accessing and manipulating data. When the American National Standards Institute (ANSI) published a standard for the SQL language in 1989, they set a universal standard to which database vendors could adhere. Later, in 1992, ANSI released an update to the SQL standard, known as SQL-92. The standards helped formalize many of the behaviors and syntax structures of SQL. The ANSI standard covered lots of important details concerning the querying and manipulation of data. The syntax was formalized for many commands; some of these are SELECT, INSERT, UPDATE, DELETE, CREATE, and DROP.

Unfortunately, the standards didn't address every facet of programming for a relational database. To meet the needs of their own user communities, database vendors began to extend the SQL language with capabilities that enhanced the basic functionality of SQL. The Transact-SQL language was introduced by Sybase to answer user requirements for programming extensions to SQL--extensions enabling conditional processing, error handling, declared variables, row processing, and numerous other functions. Even some of the simplest operations, like creating an index or performing a conditional operation, are extensions to the SQL language.

Furthermore, many relational database products had been on the market for some time before a standard of any kind had been published. As a result, many developers began to implement their own extensions to the SQL language. In most cases, these extensions to SQL were incompatible from vendor to vendor. A program written in Oracle's dialect of SQL, for example, wouldn't run properly under Sybase or DB2 and vice versa unless it contained only the simplest ANSI-standard SQL statements.

The Relational Database Model

These days, relational database management systems (RDBMSs) like SQL Server and Sybase are the primary engines of information systems everywhere--particularly distributed client/server computing systems. Though RDBMSs are now common enough to trip over, it wasn't always that way. Not too long ago, you would probably trip over hierarchical database systems or network database systems or COBOL (heck, that still happens). Here's a quick-and-dirty definition for a relational database: a system whose users view data as a collection of tables related to one another through common data values.

Perhaps you are interested in more than a quick-and-dirty definition? Here goes. The whole basis for the relational model follows this train of thought: data is stored in tables, which are composed of rows and columns. Tables of independent data can be linked, or related, to one another if all have columns of data that represent the same data value, called keys. This concept is so common as to seem trivial; however, it was not so long ago that achieving and programming a system capable of sustaining the relational model was considered a long shot with limited usefulness.

Relational data theory was first proposed by E. F. Codd in his 1970 paper to the ACM entitled "A Relational Model of Data for Large Shared Data Banks." Soon after, Codd clarified his position in the 1974 paper to the Texas Conference on Computing Systems entitled "The Relational Approach to Data Base Management: An Overview." It was in this paper that Codd proposed the now legendary 12 Principles of Relational Databases. If a vendor's database product didn't meet Codd's 12-item litmus test, then it was not a member of the club. The good news is that rules do not apply to applications development; rather, these rules determine whether the database engine itself can be considered truly "relational." Nowadays, most RDBMSs--including both Microsoft and Sybase variants of SQL Server--pass the test.

Codd's Rules for a Truly Relational Database System

Are you curious about Codd's 12 Principles of Relational Databases? Don't be ashamed that you don't know them by heart; few on the SQL Server's development staff do, and no one on the marketing staff does. However, the few folks who do know these principles by heart treat them like religious doctrine and likely would be mortified by their "lightweight" treatment here:

1. Information is represented logically in tables.

2. Data must be logically accessible by table, primary key, and column.

3. Null values must be uniformly treated as "missing information," not as empty strings, blanks, or zeros.

4. Metadata (data about the database) must be stored in the database just as regular data is.

5. A single language must be able to define data, views, integrity constraints, authorization, transactions, and data manipulation.

6. Views must show the updates of their base tables and vice versa.

7. A single operation must be able to retrieve, insert, update, or delete data.

8. Batch and end user operations are logically separate from physical storage and access methods.

9. Batch and end user operations can change the database schema without having to re-create it or applications built upon it.

10. Integrity constraints must be available and stored in the metadata, not in an application program.

11. The data manipulation language of the relational system should not care where or how the physical data is distributed and should not require alteration if the physical data is centralized or distributed.

12. Any row processing done in the system must obey the same integrity rules and constraints that set-processing operations do.

Transact-SQL and the SQL Server database management system accomplish all of these functions.

There is some debate about why relational database systems won out over hierarchical and network database systems, but the following reasons seem self-evident:

• The relational high-level language interface is much simpler to learn and more intuitive than with nonrelational databases (you are, after all, reading this book as opposed to one on Xbase).

• Relational databases provide efficient and intuitive data structures that easily accommodate ad hoc queries. From phone books to hotel registries, relational databases (of a sort) are second nature to most people.

• Relational databases provide powerful integrity controls such as check constraints and referential integrity--thus providing higher-quality data.

• The RDBMS vendors combined slick marketing and adept hardware positioning to gain a market and mindshare advantage.

One of the key risks you face when developing relational databases is their simplicity. They're just so easy. It is no chore to slap together a set of tables and columns for a database. Even assigning relationships between tables is not a big deal. The whole process of database creation can be accomplished so easily that many developers entirely skip the distinct and separate process of database design. There are literally volumes of work written about proper relational database design, which is beyond the scope of this text. But there are a few key concepts you must understand fully if you're going to leverage the power of relational databases and, hence, Transact-SQL. Prominent among them are the concept of normalization and the drastic difference in the set-processing behavior preferred by SQL Server versus the row-processing behavior of popular rapid application.