TSQL (Transact Structured Query Language)
What is Transact SQL?
Transact SQL, also called T-SQL, is Microsoft's extension to the ANSI SQL language. It is the driving force of Microsoft's SQL Server and is a dynamic database programming language. There have been several extensions added to the ANSI SQL language that have become their own SQL language. Oracles PL/SQL is another. So if you were using an Oracle database, you would code your database in PL/SQL as opposed to T-SQL. And allow they are based on the same standard there are slight difference that mean your PL/SQL language won’t necessary execute and perform the same functions on a SQL Server database.
MSDE Manager has a graphical interface that allows you develop your own T-SQL code and execute it against databases on your server. This can range from ad hoc queries to writing store procedures and other SQL Server objects. You can even create additional databases using T-SQL!
How is T-SQL Used?
T-SQL can be used to write ad hoc queries store procedures, views and other SQL Objects. You can even create new databases using the CREATE DDATABASE statement. A stored procedure is a stored set of SQL commands that sit on the physical server. And remain on the server for future use. They are compiled after their first use and take heavy burden off the server.
-- ad hoc query
SELECT * FROM NewTable
--Procedure that updates column
CREATE PROC as NewProc
UPDATE newtable SET Column = ‘updated’
WHERE CurrentValue = ‘old’
Views are virtual tables and made of SELECT Statement and combine data from different tables using joins which can then be used to show data as coming from big table instead of several smaller ones. The advantage of views as it allows you to give user’s access to data without having to give them permissions on the underlying tables. Instead you grant permissions to execute the view.
--View that returns all columns that have been updated.
CREATE VIEW as NewView
SELECT * FROM NewTable
WHERE Column = ‘Updated’
Other SQL Server objects that can be written in T-SQL include Triggers and User defined functions.
Why T-SQL?
T-SQL gives the DBA or SQL Server programmer the ability to write their own code to get their database server working just the way they want to. They can also save these scripts for future use that can save time and effort when carrying out repetitive tasks. It also makes transferring code relatively simple between different instances of SQL Server. In essence T-SQL gives the SQL Server DBA or programmer complete control of their database server.
How to execute T-SQL Statements using MSDE Manager
Ensure you have MSDE Manager started and running. Expand your SQL Server instance and expand your database folder and select the database you want execute T-SQL statements against.
Select <Tools> <TSQL> from the menu bar this takes you to the T-SQL screen
Ad hoc Query
The TSQL tool will check the syntax as you type changing the colour of key words in the above statement the SELECT and FROM statements have changed to blue
Stored Procedure
This screen demonstrates what a stored procedure script will look like this:
The green writing is comments that you can insert into your code to describe its meaning. This will allow other people to more easily understand what you have coded.
Create Views
The above codes crates a view called NewView
Conclusion
T-SQL is the programming language of SQL Server. It is a powerful tool that enables programmers and DBA’s to have full control of the database server. It can be an extremely useful tool to be able to write in T-SQL. The MSDE Manager provides an excellent tool to allow you write develop and execute your T-SQL code. It provides Syntax checks and colour coordinated statement that allow you to quickly spot errors in the code. If you are writing T-SQL against your databases server then using MSDE Manager can be very useful too.