The Nature of SQL

Most folks who work with Transact-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 is a reference point for many who have worked with computer systems in other capacities.

Comparing Transact-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, Transact-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 Transact-SQL? Probably, but it likely won't be a very good experience. Chapter 1 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 Transact-SQL any less capable as SQL Server's native query language. It simply gives programmers another option.

Transact-SQL is designed primarily to work with relational data. No big surprise here. Secondarily, Transact-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 Data Transformation Services (Data Integration Services in SQL Server 2005) can utilize both programming code and Transact-SQL. With that settled, what can you do with Transact-SQL? Quite a lot.

Transact-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) — Commands are used to create and manage the objects in a database. DDL statements can be used to create, modify, and drop databases, tables, indexes, views, stored procedures, and other objects.

• Data Control Language (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.

• Data Manipulation Language (DML) — Contains the statements used to work with data. This includes statements to retrieve data, insert rows into a table, modify values, and delete rows.