Transact SQL, also called T-SQL, is Microsoft's extension to the ANSI SQL language. It is the driving force of Microsoft's SQL Server and is a dynamic database programming language. There have been several extensions added to the ANSI SQL language which have become their own SQL language. Oracles PL/SQL is another. So if you were using an Oracle database, you would do database programming in PL/SQL. Just like you use T-SQL with SQL server.
How is T-SQL Used? T-SQL is written inside of a stored procedure. A stored procedure is a stored set of SQL commands that sit on the physical server. In this case the SQL server. They are compiled after their first use and take heavy burden off the server. Often with ASP development you run in to situations where interaction between the database and the application are rapidly in succession. Like this.
A new user comes in. Lets put him in the users table. Return the identity. Now lets update the member count for his company in the Comapany table. Add him to the company members table with his new ID. He was sponsored by another company member so lets track all that as well.
In a normal ASP application we would be doing ALL of the above from the application. We would execute 1 SQL statement, come back and do the next, come back and do the next, etc.... With no valid reason for doing so. The above scenario could all be done dynamically with T-SQL in 1 stored procudure call. Thus several SQL statements execute with only 1 trip to the database as opposed to several.
Why T-SQL? Dynamic Static SQL, like you write in your ASP pages, has several draw backs. The biggest being that it is static. With dynamic SQL you can dynamically build your queries and get a high amount of reuse out your objects. Much like you would use IF statements and Select CASE statements in ASP program, you can do the same with T-SQL. The following is an example of a TSQL statement that selects a different field in the SQL Server Database based on the parameters passed to the Stored Procedure (Stored Proc).
CREATE PROCEDURE SP_Products @cat int, @Price nvarchar(10) AS
Select CODE,TITLE,Version,Status,
Case @Price When 'Price' THEN Price When 'PriceA' Then PriceA When 'PriceB' Then PriceB When 'PriceC' Then PriceC End, Lots, LotsOf, Description, Pic From Products Where Category = @cat ORDER BY CODE
In the above example I am selecting a different Price field based on the user level of the buyer.
Extended Functionality Without the ability to dynamically change your SQL statements in your Active Server Pages, you have no functionality. Look at the example above. Sure I could have used ASP to generate the same SQL query but at an expensive of extra data processing, combining technologies, and complexity to update.
Maintainability Stored Procedures are essentially functions. Just like you build a function for maintenance, a stored procedure works the same way. It is an object oriented approach to database programming at the database level. Not the application level (which is not an option regardless).
Security Stored Procedure calls say nothing important to the observer. If you are concerned about who knows what about your database, you hide practically everything with stored procedures.
The Editor Itself Editing stored procedures on the SQL server is also far more practical and user friendly than opening ASP files and re-writing static SQL. The SQL server also checks your syntax and will not let you write an invalid query. You do not have to keep hitting the refresh button in the browser until everything checks out.
So that's an overview of T-SQL. Any serious database programmer should be learning how to use it. Especially for large complex applications. Look for specific T-SQL examples soon. This is also a great site for SQL information - http://www.sqlteam.com
FAQ posted by Rob Taylor at
11/30/2000 4:12:36 AM to the
Databases, Queries category.
This FAQ has been viewed 65,401 times.
Do you have a FAQ you'd like to suggest?
Suggestions? Comments? If so, send it in!
Also, if you'd like to be a FAQ Admin (creating/editing FAQs),
let me know! If you are looking for other FAQs, be
sure to check out the 4Guys
FAQ and Commonly Asked Messageboard Questions!
