Technology is ever changing and very few areas are stable. This makes information technology interesting, but challenging. T-SQL or Transact-SQL is one area that is stable. There is also a vast use of T-SQL and many career options.
T-SQL is the combination of standard SQL as well as the proprietary adds-ons for Microsoft that include functions, stored procedures and other elements of the language. SQL or the Structured Query Language is the programming language used to inquire, create, control and manipulate objects in a relational database. It is also used for administering the database. SQL is both an American National Standards Institute (ANSI) and International Standards Organization (ISO) standard.
SQL statements are used to perform tasks such as adding data, making data modifications, creating objects, performing maintenance tasks and retrieving data from a database. Most analysis and business decisions are made as a result of querying and understanding data in the databases.
Databases hold core information that allow businesses to function. Relational database management systems use SQL. These include platforms like Oracle, DB2, Sybase, Microsoft SQL Server, and Access as well as others. Typically, each database platform follows the ANSI / ISO standards and then also has features in the language that are proprietary. This is true of Microsoft SQL Server and the full language is called T-SQL.
The place to begin learning T-SQL depends on the task at hand. Here are some example roles and starting points to jump start your career in each role.
Report Writer or Analyst
- Focus on the Select statement. Report writers and analysts must know how to ask questions (i.e. query the database). Global Knowledge course Querying Microsoft SQL Server 2014 (M20461) or Querying Data with Transact-SQL (M20761) are good starting points.
- Learn how to turn your queries into stored procedures. For example, if you have a query “Select last, first from customer” you can turn it into a stored procedure of “Create procedure usp_GetCustInfo as Select last, first from customer”. Then, you only need to call the procedure from the report “Execute usp_GetCustInfo”. Stored procedures provide performance and security benefits when querying the database.
- Spend time learning SQL Server Reporting Services. This tool will allow you to graphically produce reports and utilize the Select statements and stored procedures you create in T-SQL. Global Knowledge course Implementing Data Models and Reports with Microsoft SQL Server 2014 (M20466) is an excellent course dealing with reporting services as well as analysis services.
- Learn these T-SQL statements: Insert, Update and Delete. These allow data entry and modification.
- Focus on the Select statement. Although the primary focus is data entry, querying the data supports data verification.
- Learn the interface that supports data entry. This could be a web page or a Microsoft Windows application. Each interface will have its own unique design.
- Focus on Create, Alter and Drop statements. These statements give definition to the database objects. Examples include creation of tables, stored procedures, views, functions and triggers. The Alter statement supports modification. Drop removes an object.
- Take a relational database design course or read a relational database design book. Global Knowledge courses Developing Microsoft SQL Server 2014 Databases (M20464), Introduction to SQL Databases (M10985), or Developing SQL Databases (M20762) are excellent options. “Database Design for Mere Mortals”, by Michael Hernandez is a fabulous first book.
- Learn to map business questions to objects that need to exist. Take this question: Who are the best ten customers in terms of revenue and loyalty? Tables need to exist for customers, orders, time and perhaps customer satisfaction. This single question represents many topics – customers, orders, loyalty (probably over time), and revenue.
- Start small. Two ways to do this are to: join a team as a junior database administrator (DBA) and learn from more senior people or volunteer at a nonprofit or small business to help with their database. Spend time learning from the staff about their business and database platform.
- Focus on T-SQL statements such as Grant, Revoke and Deny. These all deal with security which is a primary responsibility of a DBA. These statements control access to objects that have been created by the database designer. These statements control whether a person is allowed to see data, modify data, create tables, drop tables or any other privilege.
- Focus on performance and metadata. Learn dynamic management views, system stored procedures, and system functions that deal with metadata. Administering Microsoft SQL Server 2014 Databases (M20462) is a good starting point for those pursuing a database administration role.
Although each role has its specialty, every role needs the ability to query the database, or database metadata (i.e. the objects within the database). Every person needs an understanding of the Select statement. Many roles need an understanding of statements such as create, alter, drop, insert, update, delete, grant, revoke and deny. The place to begin in T-SQL is with the select statement, regardless of role.
Querying Microsoft SQL Server 2014 (M20461)
Administering Microsoft SQL Server 2014 Databases (M20462)
Developing Microsoft SQL Server 2014 Databases (M20464)
Implementing Data Models and Reports with Microsoft SQL Server 2014 (M20466)
Querying Data with Transact-SQL (M20761)
Developing SQL Databases (M20762)