EDUCBA

Home Data Science Data Science Tutorials SQL Tutorial T-SQL Commands

T-SQL Commands

Priya Pedamkar
Article byPriya Pedamkar

Updated July 6, 2023

T-SQL-Commands

 

 

Introduction to T-SQL Commands

Every available RDBMS database is basically supporting SQL (Structured query language) statements or commands for executing any query of the developer from the storing data in the corresponding database. Every database may support some specific SQL kind language. T-SQL command is nothing but one specific query language which based on SQL and called as Transact Structure Query Language. It is one of the current upgrading Microsoft product and mainly a key extension of SQL language. It supports MS SQL server mainly.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Basic T-SQL Commands

There is a lot of SQL commands for T-SQL especially for fetching, inserting, deleting or truncating kind of data manipulation level of the task. Some of the basic commands are mentioned below:

  1. Alter table: Basically use for altering one existing table column definition, or add one new column or delete one already added column. Alter table column can also be used in case of rename table as well.
  2. Create table: It is used for creating a new table with a specific definition, create table can use for some new table with default tablespace, for using some define tablespace on creating new table need to define the same with the create statement.
  3. Create view: Using this we can able to create one view. Suppose there has some specific query which actually taken a long time to execute, in that case, we can define that specific query as a temporary table and create one new view with this temporary table executed data. Now calling the view directly except this query execution it will give a result much faster than usual. Create a view command actually helps to create the same.
  4. Delete: Using for deleting table, specific column of the table, specific row of the table or entire data inside the table. There is normally three kind of delete available in T-SQL commands, delete by using some cursor commands, where deleting some specific key column data by mentioning in the cursor. Delete by some dynamic command, where someone can delete some key data with a specific dynamic condition, it can delete one specific or multiple rows as well based on the provided condition. Delete by using some language command, here also again some condition of specific language which helps to remove one or multiple rows at a time.
  5. Insert: By Using insert commands someone can able to insert one or multiple rows in the table. There is two kind of critical insert available in case of it. One is inserted rows in a table or view by using some dynamic condition. Here someone can insert data in the table based on some dynamic condition mentioned in the query. And another one is inserted into the table or view based on some language command. In this case, an insert can be done based on some language condition mentioned in the command.
  6. Update: Update command is mainly used for updating some of existing row in a table. Three kinds of update available in T-SQL, by using cursor one kind of positional update, by using a dynamic command, one kind of dynamic update based on the mentioned query, by using language command, it is also a dynamic update but based on the specific language.

Intermediate T-SQL Commands

There are several other popular SQL commands also been used by the developer which is not very basic commands but working with T SQL more, those SQL commands are very much required to execute. Some of those kinds of requiring intermediate SQL commands are mentioned below:

  1. Begin transaction: Begin transaction is for managing or mention starting point of the user created transaction management. Suppose someone willing to execute multiple data manipulation query but willing to commit the same query one time, in that case, begin transaction need to mention for identifying starting of those DML statements.
  2. Commit Transaction: It basically committing the entire transaction into the database. As mentioned earlier someone willing to commit entire DML statement one time without any individual commit. In that where they will mention committing transaction command, it will commit entire DML execution in between transaction boundary means from begin to commit.
  3. Create index: Create one specific index for the table sometimes on some specific useful column. An index has been created on some key column of the table where those columns are very much useful in terms of application logic.
  4. Truncate table: Truncating one table data, but it will never drop the table structure.
  5. Rollback transaction: It also does the same thing like commit, it normally rollback the entire transaction in between define transaction boundary.

Advanced Commands

Still, some of the critical tasks need to be done by the T-SQL command users frequently. Those tasks also have some advance kind of commands need to be executed, which mainly used by some of the managerial people for granting or revoking some access to a specific user, dropping some of the critical table structure, providing permission for accessing specific table in the database etc. Those advance kind of T-SQL commands are below:

  1. Drop index: Dropping the existing created index with structure from the database. It is some architectural decision to drop one index from a specific table can have multiple impacts. So dropping access normally given to some specific key people of one project.
  2. Drop Table: Dropping one existing define table with structure from the database. Dropping one table from the database means table will entirely remove.
  3. Grant: Granting entire table or database for some specific critical users.
  4. Revoke: Revoking some critical accesses for some specific users.
  5. Use: Providing access for one existing database.

Tips and tricks to Use

Some common users who very frequently use this command queries, they normally use some of the tips and tricks for utilizing t-SQL commands output in a proper way. That kind of tricks normally used for solving some user-specific queries and display execution output for understanding the same properly. Some of the very commonly used key tricks are:

  1. Restore database: Normal attitude of the common developer for using SQL server common backup utility for database restore. Whereas T-SQL have some key command for restoring the database, which is more faster and secure than SQL server restoring mechanism.
  2. Stored Procedure: Normally common stored procedure concept does not support by SQL server, but T-SQL have provided one store procedure sp_executeSQLFromFile, help developer for executing T-SQL commands in store procedure structure, which is more faster than usual.

Conclusion

T-SQL is one of the key SQL query languages which are critically used for SQL server for executing queries. As T-SQL is based on SQL, so almost maximum available features are same as SQL. But still, it is not that much popular yet.

Recommended Articles

This has been a guide to T-SQL Commands. Here we have discussed basic as well as advanced T-SQL Commands and some immediate T-SQL commands respectively. You may also look at the following article to learn more –

  1. Uses of HBase Commands
  2. How to use Sqoop Commands
  3. Importance of Tableau Commands
  4. Hive Commands How To Use