T-SQL vs P/L SQL: 6 Critical Differences

Most businesses today have started becoming data-oriented. This means that they rely on data-driven decision-making to plan future strategies pertaining to various departments such as Marketing, Growth, Business Development, Product, etc.

Since most of the essential business processes now rely on data-driven decision-making, organizations are actively looking for solutions that can help them manage their data better. Almost all businesses make use of SQL in some form to manage their data. There are, however, certain disadvantages associated with SQL. These disadvantages include SQL’s ability to process only a single statement at a time, or each SQL query making a separate call to the database resulting in high-performance overhead, etc. These disadvantages can be overcome by implementing certain extensions to SQL, such as T-SQL for Microsoft SQL Server or P/L SQL for Oracle.

This article will help you understand the various factors that drive the T-SQL vs P/L SQL decision.

Table of Contents

• Introduction to T-SQL
• Introduction to P/L SQL
• Factors that Drive T-SQL vs P/L SQL Decision
  • T-SQL vs P/L SQL: Internal Organisation and Database Objects
  • T-SQL vs P/L SQL: Transaction Control
  • T-SQL vs P/L SQL: Indexing
  • T-SQL vs P/L SQL: Cursors
  • T-SQL vs P/L SQL: Object-Oriented Programming (OOPS) Support
  • T-SQL vs P/L SQL:  Recursion
• Conclusion

Introduction to T-SQL

T-SQL (Transact-SQL) can be defined as a set of programming extensions from Microsoft and Sybase that add numerous features to the existing Structured Query Language (SQL). These features include exception and error handling, transaction control, row processing, and declared variables.

All applications that wish to communicate with Microsoft SQL Server can send T-SQL statements to the Server. T-SQL queries allow users to select columns, label output columns, restrict rows, and modify search conditions.

T-SQL identifiers are now used in all Servers, databases, and database objects in Microsoft SQL Server. These include tables, stored procedures, constraints, columns, views, and data types. T-SQL identifiers must each have a unique name assigned to them when created and used to identify an object.

More information on T-SQL can be found here.

Introduction to P/L SQL

P/L SQL stands for Procedural Language extensions to Structured Query Language. It is an extension to the popular Structured Query Language (SQL) language that combines the processing power of Procedural Language with the data manipulation power of SQL to create powerful SQL queries. P/L SQL ensures seamless processing of SQL statements by exponentially enhancing the robustness, security, and portability of the database.

 P/L SQL adds numerous procedural constructs to SQL to overcome some of its limitations. It also provides a more comprehensive programming language solution for building mission-critical applications on Oracle Databases.

More information on P/L SQL can be found here.

Factors that Drive T-SQL vs P/L SQL Decision

The various factors that drive the T-SQL vs P/L SQL decision are as follows:

• T-SQL vs P/L SQL: Internal Organisation and Database Objects
• T-SQL vs P/L SQL: Transaction Control
• T-SQL vs P/L SQL: Indexing
• T-SQL vs P/L SQL: Cursors
• T-SQL vs P/L SQL: Object-Oriented Programming (OOPS) Support
• T-SQL vs P/L SQL:  Recursion

1) T-SQL vs P/L SQL: Internal Organisation and Database Objects 

Oracle and hence P/L SQL, organizes all database objects as Schemas. A Schema is a group of logical data structures, like Tables, Views, Indexes, Clusters, Classes, Triggers, Objects, etc. There is no one-to-one relationship between a Schema and physical files on the disk. Users are given controlled access to certain Schemas and tables via roles and permissions.  

In Microsoft SQL Server and hence T-SQL, each database has a direct relationship with an unshared physical file on the disk. Database objects like Tables, Views, Indexes, Clusters, etc. are organized by database names. Users are granted access to the specific database and its objects.

2) T-SQL vs P/L SQL: Transaction Control 

Microsoft SQL Server commits each change individually and it is difficult to roll back changes unless it’s enclosed in a BEGIN TRANSACTION block. All commands which you think could lead to an error or could probably necessitate a rollback must be enclosed in a BEGIN TRANSACTION block. Hence, if an error occurs, one can easily roll back, and if everything goes smoothly, they can commit all changes to end the BEGIN TRANSACTION block. 

Oracle treats each database connection as a new transaction. By default, all changes via SQL statements are done in memory only, until an explicit COMMIT command is used. So one can easily roll back a lot of changes, as the underlying database on the disk is not changed. Issuing a ROLLBACK command will undo all changes done after the last COMMIT. A COMMIT command will permanently store the changes, and the first command after the new COMMIT initiates a new transaction, starting the process all over again. 

3) T-SQL vs P/L SQL: Indexing 

Oracle allows the creation of partitioned tables as well as indexes on those separate partitions. These are called Partitioned Indexes and they improve performance, manageability, and scalability. These indexes can be created as per table partitioning methods (Local Indexes) or created independently (Global Indexes).

Another kind of index supported in P/L SQL is Bitmap Indexes. Here, an Index Key has pointers to multiple rows, and Oracle stores a bitmap/bit array for each such Index Key. This is what creates a Bitmap Index. Oracle then uses a special mapping function to map each bit in the bitmap index, to row IDs of the related rows.

Microsoft SQL Server allows partitioned tables but follows a slightly different, simpler approach for indexes. Indexes for the partitions are also bifurcated as per the rows/data in the partitions. Hence, T-SQL can only support Index Partitioning but not Partitioned Indexes. Microsoft SQL Server does not support Bitmap Indexes.

4) T-SQL vs P/L SQL: Cursors 

Oracle creates a context area for a query before it tries to execute it. This context area holds all the information needed to execute SQL queries. Also, Oracle intrinsically creates a Cursor (pointer) to it. Hence, in Oracle, Cursors are powerful and capable, as they are also used by the underlying DBMS.

T-SQL, on the other hand, treats Cursors as just a reference to loop over query results or do some serial processing. Hence, Cursors have limited capabilities in T-SQL. 

5) T-SQL vs P/L SQL: Object-Oriented Programming (OOPS) Support

P/L SQL allows strong support for OOPS, including Inheritance and Function Overloading. The following sample code shows how Inheritence is implemented in P/L SQL:

CREATE OR REPLACE type employee_supertype
IS   object
  (obj_emp_id   VARCHAR2(30),
    obj_emp_name VARCHAR2(30),
    member FUNCTION func_calculate_salary RETURN NUMBER) NOT final;
.................. /. 
Then you can derive subclasses or subtypes from it. 

CREATE OR REPLACE type permanent_employee_subtype under employee_supertype (
obj_emp_job VARCHAR2(30),
obj_emp_sal NUMBER,
overriding member FUNCTION func_calculate_salary RETURN NUMBER); 
............./. 

CREATE OR REPLACE type contractual_employee_subtype under employee_supertype (
obj_emp_job VARCHAR2(30),
obj_emp_sal NUMBER,
overriding member FUNCTION func_calculate_salary RETURN NUMBER); 
............/. 

This adds power, functionality, and readability to P/L SQL. Moreover, P/L SQL can group functions and procedures into packages. These packages can be deployed on other machines and reused, enhancing portability.

In contrast, T-SQL does not have concepts of Inheritance or Function Overloading limiting its use as a full-fledged programming language. To achieve the same in T-SQL, one has to write 3 completely different programs whose relationship and interoperability are not supported by the language.

6) T-SQL vs P/L SQL:  Recursion 

Recursion is a powerful mechanism by which a program can call itself repeatedly, execute a given task until its complete. A simple example would be printing a family tree of the last 5 generations. A program would print all members of the first generation, then pick each one of them one by one, recall itself, and print all members that originate from the current member. This is conceptually easy to understand and program.

P/L SQL allows for full recursion i.e. a procedure/function can call itself in a cyclic fashion. Each recursive call creates new instances of all objects declared in the procedure, including parameters, variables, cursors, and exceptions. Also, new instances of SQL statements are created at each level in the recursive descent. This adds a lot of capability to P/L SQL, as a complete program can call itself recursively.

In contrast, T-SQL does not allow recursion. At best, T-SQL just allows a SQL query to call itself repeatedly until all results are obtained. It’s done via Common Table Expression (CTE), which can reference itself. By doing so, the CTE repeatedly executes, returns subsets of data, until it returns the complete result set.

The numerous factors that drive the T-SQL vs P/L SQL decision can be summarized in the following table:

T-SQL	P/L SQL
T-SQL was originally developed by Sybase and now owned by Microsoft. Hence, it works with Microsoft SQL Server only.	P/L SQL was developed by Oracle and works with Oracle only.
All database objects like Tables/Views/Procedures are internally organized by database names. Users are allowed access to a specific database and its objects.	All database objects are organized in Schemas. Users are allowed access to certain schemas via roles and permissions.
More focussed on Microsoft SQL Server and its functions only.	P/L SQL is more versatile and encompassing, E.g. you can send Emails and access Web Pages.
Allows B-Tree Indexes only.	Allows B-Tree, Bitmap, Domain, and Partitioned Indexes.
Provides a greater degree of control on how an application works in Microsoft SQL Server and is easy to learn.	Enhances the power of plain SQL, and is considered to be more powerful and holistic.
Limited and patchy support for use of Cursors.	Powerful support for use of Cursors, the underlying file/data organization supports it.
No direct support for Object-Oriented programming. Only supported though 3rd party  ORM tools.	Full support for Object-Oriented programming via Function Overloading, Data Encapsulation, etc.
Explicit error handling capabilities using Try-Catch blocks.	Provides error handling via checking for Exceptions only.
Provides bulk insert and data loading.	No explicit bulk inserts.
Allows reading data from an external sequential file and you can use the BULK statement to fine-tune the external reads.	Indirectly allows reading data from an external sequential file but no specific language constructs to ease the process.
No packages to ease re-use.	Allows grouping of Procedures into Packages, which can be reused.
No need for Subqueries and DELETE and UPDATE are improvised accordingly.	Needs Subqueries to read data from another table.
Provides WAITFOR construct to wait for a certain period of elapsed time.	No such direct construct in P/L SQL.
No support for Arrays.	Supports Arrays.

Conclusion

This article provided you with an in-depth comparison of the various factors that drive the T-SQL vs P/L SQL decision. Although it’s mostly peculiarities of your datasets that determine which database you should use, if your application is more about business logic and code, where the database would be simple but programming would be complex, the above discussion would be useful in selecting an appropriate database solution. 

Also, keep in mind that delegating database-intensive tasks to the database or Data Layer is faster, than doing them higher at your application level using a Database Driver. 

Visit our Website to Explore Hevo

Most businesses today use multiple platforms to carry out their day-to-day operations. As a result, all their data is spread across the databases of these platforms. If a business wishes to perform a common analysis of their data, they would first have to integrate the data from all these databases and store it in a centralized location. Building an in-house data integration solution would be a complex task that would require a high volume of resources. Businesses can instead use existing data integration platforms like Hevo.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.