T-SQL or Transact-SQL is a proprietary extension to SQL (Sequential Query Language). The extended features includes: Improved performance, increased functionality and enhanced globalization support.
Transact-SQL is Microsoft SQL Server’s implementation of the language. It is largely SQL-92 compliant, so if you’re familiar with another vendor’s flavor of SQL, you’ll probably feel right at home with Transact-SQL. Transact-SQL uses stored procedures to retrieve data or to execute a query on to a database.
Stored procedures in Transact-SQL
Stored procedures are executable server-side routines. They give you great power and performance benefits if used wisely. Unlike user-defined functions (UDFs), stored procedures are allowed to have side effects. The reason for this is you cannot have transaction within function, whereas, in a procedure, they are allowed to change data in tables, and even alter object definitions. Stored procedures can be used as a security layer. You can control access to objects by granting execution permissions on stored procedures and not to underlying objects.
SQL Server 2008 supports different types of stored procedures: User-defined, system, and extended. You can develop user-defined stored procedures with T-SQL or with the CLR.
The following are some applications that can generate Transact-SQL:
- General office productivity applications.
- Applications that use a graphical user interface (GUI) to let users select the tables and columns from which they want to see data..
- Distributed database systems from which data from SQL Server is replicated to various databases, or distributed queries are executed.
- Data warehouses in which data is extracted from online transaction processing (OLTP) systems and summarized for decision-support analysis.
To explain more fully, programming in Transact-SQL is a blend of several distinct, yet integrated, components. Let us assume you want to build an application and you start with design and analysis of database as you wanted. And that you’ve built your development database using declarative SQL statements or perhaps a visual data modeling tool. You might later want to implement a set of complex business rules on your database tables through triggers coded in Transact-SQL. From there, you might create reports and business processing modules that support the user interface through stored procedures coded in Transact-SQL.
As mentioned earlier, SQL Server 2008 offers a wide variety of Transact-SQL features that enables developers to create new applications that can store and consume any type of data on any device, and enables all your users to make informed decisions with relevant insights. Here are the key features:
- Initializing variables
- Compound assignment operators
- Enhanced CONVERT function
- New date and time data types
- New date and time functions
- The MERGE statement
- Grouping sets
- Table valued parameters
- Large user-defined types
- Table value constructor support
- The HIERARCHYID data type
- DDL trigger enhancements
- Large CLR user-defined aggregates
- Sparse columns
- Filtered indexes
- Multi-input CLR user-defined aggregates
- The ORDER option for CLR table-valued functions
- Object dependencies
- Change data capture
- Collation alignment
SQL database is a popular subject these days. With the data becoming more complex and huge, technologies like Transact-SQL makes queries more quick and optimized. Understanding and studying these technologies that involve dealing with complex data warehouses and their mining issues is great in its terms. For beginners who have some knowledge about DBMS, these are some new concepts that may help them to craft their approach better.
If you looking to explore Transact-SQL completely, I recommend you to grab some books and use MSDN for solutions as well.