The Nature of SQL - T-SQL

Many people who work with T-SQL have had some experience with other languages. If you've never done any programming, please don't close the book at this point and give up. This is certainly not a prerequisite for writing SQL, but it is a reference point for many who have worked with computer systems in other capacities.

Comparing T-SQL to a procedural or object-oriented programming language (such as Java, C, C++, C#, or Visual Basic) is like comparing apples to pomegranates. It's not better than or worse than, but quite different than, a true programming language — even though you may see some similarities in the syntax and structure of certain statements. For different types of operations, T-SQL may be far superior or much worse than these languages, simply because of what it is designed to accomplish. One of the challenges in making broad statements about the capabilities of different languages is that as they continue to grow and evolve, version after version, additional capabilities are added. The problem with industry standards is that everyone is out to protect and enhance their own product. Over time, the capabilities of each technology (or language, in this case) begin to overlap, leaving us with a number of different options to perform the same tasks.

Is it possible to perform data access or data manipulation (to insert, modify, or delete values in a database) with a procedural programming language without using SQL? Yes, but it's cumbersome and usually inefficient. Can you perform complex mathematical operations, looping, string parsing, or multidimensional array management in T-SQL? Certainly, but it won't be a very good experience. Chapter(Introducing T-SQL and Data Management Systems) mentioned that SQL Server 2005 gives programmers the capability of writing stored procedures and user-defined functions entirely in object-oriented program code, rather than SQL. This doesn't make T-SQL any less capable as SQL Server's native query language. It simply gives programmers another option.

T-SQL is designed primarily to work with relational data. No big surprise here. Secondarily, T-SQL also has a number of useful capabilities for working with scalar (single value) data, logical operations, mathematics, decision structures, text string parsing, and looping mechanisms. However, compared with most programming languages, SQL is not as powerful or as capable as a true programming language.
If your needs call for advanced functionality that may be outside the realm of SQL's native capabilities, you may need to carefully consider using a different approach, such as a custom, extended, stored procedure, application programming interface (API), .NET assembly, or other programming solution. This is why SQL Server's Integration Services can utilize both programming code and T-SQL. With that settled, what can you do with T-SQL? Quite a lot. What should you do with T-SQL? That's an even better question. I hope to give you a good idea by the time you finish this chapter.

T-SQL is the language used to talk to SQL Server, and query expressions are essentially used to ask the server to do things. It's important to know what you can ask for — and what SQL Server can do. Query operations are divided into three different categories. I'll briefly describe them and then take some time to look at specific examples. Like everything else in the technical world, these categories are best known by three-letter abbreviations (that's TLA, for short.) Locally, these fall in the order I've listed here:

  • Data Definition Language (DDL) — DDL statements are used to create and manage the objects in a database. They can be used to create, modify, and drop databases, tables, indexes, views, stored procedures, and other objects.
    Examples include CREATE, ALTER, and DROP.

  • Data Control Language (DCL) — DCL statements control the security permissions for users and database objects. Some objects have different permission sets. You can grant or deny these permissions to a specific user or users who belong to a database role or Windows user group.
    Examples include GRANT, REVOKE, and DENY.

  • Data Manipulation Language (DML) — DML statements are used to work with data. This includes statements to retrieve data, insert rows into a table, modify values, and delete rows.
    Examples include SELECT, INSERT, UPDATE, and DELETE.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

T-SQL Topics