When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback

The 4 Guys Present: ASPFAQs.com

Jump to a FAQ
Enter FAQ #:
..or see our 10 Most Viewed FAQs.

4GuysFromRolla.com : ASP FAQS : Databases, Queries


Question:

What is Transact-SQL (T-SQL)?


[Print this FAQ]

Answer: 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!

Most Viewed FAQs:

1.) How can I format numbers and date/times using ASP.NET? For example, I want to format a number as a currency. (761643 views)
2.) I am using Access and getting a 80004005 error (or a [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)' error) when trying to open a connection! How can I fix this problem? (207777 views)
3.) How can I convert a Recordset into an array? Also, how can I convert an array into a Recordset? (202549 views)
4.) How can I quickly sort a VBScript array? (196039 views)
5.) How can I find out if a record already exists in a database? If it doesn't, I want to add it. (156019 views)
6.) How do I display data on a web page using arrays instead of Do...While...MoveNext...???... (152331 views)
7.) When I get a list of all files in a directory via the FileSystemObject, they aren't ordered in any reasonable way. How can I sort the files by name? Or by size? Or by date created? Or... (140381 views)
8.) For session variables to work, must the Web visitor have cookies enabled? (110162 views)
9.) Can I send emails without using CDONTS? (107083 views)
10.) How can I take the result of a SELECT...MULTIPLE or a group of same-named checkboxes and turn it into a query? That is, if the user selects 3 answers, how can I construct a query that looks for all 3? (106308 views)
Last computed at 9/17/2007 3:22:00 AM


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article