Join Gerry O'Brien for an in-depth discussion in this video A history of SQL and T-SQL, part of Querying Microsoft SQL Server 2012.
In this lesson, we'll explore the history of the structured query language and outline its progression to modern day. We'll also take a look at the Microsoft implementation of SQL, known as Transact SQL or just T-SQL. In 1970, Dr. E.F. Codd published a paper called A Relational Model of Data for Large, Shared Databanks. This has become the standard model for relational database management systems. Shortly thereafter, IBM developed the structured query language, originally called SEQUEL, then later shortened to S-Q-L, although the pronunciation has remained the same.
In 1986, the American National Standards Institute adopted SQL as a standard, and it became known as SQL 86. It was also adopted by the International Organization for Standardization, also known as ISO, in 1987. Over the years the standards have been updated and the standard names reflect that with the most recent being SQL 2011. T-SQL is the dialect that Microsoft has implemented for the SQL server product.
It implements the ANSI standard SQL, but it also adds extensions to the language. Like other vendors, Microsoft provides extensions to the language, which are typically used to provide additional functionality in their database product to support specific requirements or maybe to implement aspects of the language that are not yet adopted or implemented by the standard. Other companies also provide their own implementation of the language in the standard, such as Oracle who refer to their language as PL/SQL and IBM who uses SQL PL.
In terms of transact sequel, T-SQL is known as a set based and declarative language. This is in contrast to programming languages that are considered to be procedural. In other words, transact sequel describes what you want, not how to get it. The database engine handles the necessary processing on the back end to determine where to pull the data from that you ask for specifically. T-SQL uses discreet groups of commands. They are known as DML, DDL and DCL.
The DML components are known as the Data Manipulation Language. And they're used for querying and modifying data. We'll be using DML statements throughout this course for querying the data in SQL Server. DDL, or Data Definition Language, are the components that are used for creating and modifying database objects, such as tables and views and other components. And finally, the DCL, or the Data Control Language, is used to implement the security on the database, such as adding users, granting and revoking permissions, etcetera.
So in summary, the T-SQL language that you will use in this course is derived from and extends ANSI and iSO SQL standard. You'll gain an understanding of the usage of the DML aspects of T-SQL as we progress through the course. And you will become familiar with the use of T-SQL for querying a database.
- Writing SELECT queries
- Querying multiple tables
- Filtering text and duplicates
- Sorting and grouping query results
- Using SQL Server's built-in functions
- Writing subqueries
- Using common table expressions
- Programming with T-SQL
- Interpreting query performance data