TSQL stands for Transact structured Query language which is a Microsoft tool and extension of SQL language. TSQL is mainly used for writing an entire program of block function procedure that defines how things need to be complete and creating an application in which each application sending transact query over SQL server and there is no interaction with the database. It executes as a whole block with the extension of SQL language.

One of the things that make SQL a popular choice is that it doesn't require any substantial knowledge about coding and writing programs. SQL has some basic keywords such as SELECT, INSERT INTO, UPDATE, etc that can carry out tasks. Syntactical rules are also simple and easy to follow.

SQL works at an efficient speed. Huge data can be processed in seconds! Querying, manipulating, and calculations on data with analytical queries in a relational database can be done in no time.

As already mentioned, SQL is a standardized structured query language approved by ISO and ANSI for relational databases. Its long, detailed, and established documentation overs years also stands as a strong plus point. 

It is highly portable as used in PCs, servers, independent laptops having any operating system such as Windows, Linux, Mac, etc. It can also be embedded with other applications.

As it has simple commands for all purposes, it becomes an interactive language for its users. It is easy to understand and the commands are also understandable to the non-programmers.

SQL has a poor interface as it makes look everything very complex even when it's not! Due to its difficult interfacing, users find it difficult to deal with the databases. 

SQL Server Standard costs around $1,418/year. The high cost makes it difficult for some programmers to use it.

SQL doesn't grant complete control over databases to its users. This is due to some hidden business rules. 

Regardless of the SQL version, databases in SQL is constantly under threat as it holds huge amounts of sensitive data.

Differences Between T-SQL and Standard SQL

The first difference between SQL and T-SQL is the TOP keyword, which is used mainly in a SELECT statement. It indicates how many rows should be returned by a query in a result set. TOP is put after SELECT along with the number of rows to return. For example, the following query returns only the first 12 results:

You don't find this clause in standard SQL—it is available only in MS SQL Server. You can learn more about how and when to use the TOP statement in MS SQL Server in the course "SQL Basics in MS SQL Server".

Another difference between SQL and T-SQL is in the syntax of the SUBSTRING function used to cut a substring from a given string input. In standard SQL the syntax of this function is:

In the MS SQL Server the syntax of the function is:

In these functions, str is a given string to search, a start is a start number of a character (the first is 1) in a string from which the function will begin searching, and len is the length of the string to return.

These are only two examples of the differences between standard SQL and T-SQL, but you can learn more in this "SQL Basics in MS SQL Server" course and this "SQL Basics" course.

SQL is a base programming language, while T-SQL is derived from SQL. The choice of which database to use will depend on your project requirements.

If you wish to learn more about SQL, then check out our SQL certification course. The course will equip you with all you need to work with SQL databases and use them in your applications. From structuring your database correctly to authoring efficient SQL statements and clauses, managing your SQL database for scalable growth, and getting excellent work-ready training on SQL, it has a multitude of applications at work.

In this blog, we'll know everything about T-SQL.

Overview of T-SQL

A structured Query language is a computer language for a relational database system. Relational database system like MySQL oracle Sybase Informix uses SQL as the standard database language for storing retrieving manipulating data and storing it in a relational database.

Here are some SQL commands that are used for communication with databases like storing retrieving manipulating data.

Data definition language used for creating, dropping, altering, truncate in database.

Data manipulation language is used to insert, update, delete data in the database.

Data control language used for Revoke and grant data.

Transaction control language used for commit, rollback data.

Data Query language used for select data.

Transact structured Query language (T-SQL):

TSQL stands for Transact structured Query language which is a Microsoft tool and extension of SQL language. TSQL is mainly used for writing an entire program of block function procedure that defines how things need to be complete and creating an application in which each application sends transact query over SQL server and there is no interaction with the database. It executes as a whole block with the extension of SQL language.

There different types of T-SQL function is used in T-SQL :

It stands for Structured Query language and it is developed by IDM.	

The structured Query language is used to look over data using queries.	

In Structured Query language, the age we insert SQL into T-SQL.	

In Structured Query language, we perform DML and Doperationsion.	

In SQL, there is an interaction wiDatabasease server.	

Transact Structured Query language (T-SQL)

It stands for Transact Structured Query language and it is developed by Microsoft.

Transact Structured Query language used for creating applications and adding business logic.

In Transact Structured Query we do not insert T-SQL into SQL.

In which we can see an extension of the server used by MS SServerver.

In Transact Structured Query, there are blood od codes that are used to write function and procedure.

In TSQL, there is no interaction with the database server.

It is mainly used for the creation ng applications.

In this blog, we discuss the difference between SQL and T-SQL

Difference between SQL and T-SQL

By using TRY-CATCH constructs, putting our SQL statements/scripts inside the TRY block and error handling in the CATCH block.

SQL Server supports mainly 3 types of UDFs:

CUBE & ROLLUP are the grouping sets used with GROUP BY clause and are very helpful in creating reports.

If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task. Here are some alternatives to using a cursor:

With DELETE we can provide conditional WHERE clause to remove/delete specific rows, which is not possible with TRUNCATE.

TRUNCATE is faster than DELETE as Delete keeps log of each row it deletes in transaction logs, but truncate keeps log of only de-allocated pages in transaction logs.

Both statements can be rolled backed if provided in a transaction (BEGIN TRANS). If not then none of them can be rollbacked.

DELETE is DML just like INSERT, UPDATE, but TRANCATE is DDL, just like CREATE, ALTER, DROP.

By using OSQL & SQLCMD we can execute an sql query from command prompt.

SQL Server 2000 includes several tools you may find useful when performance tuning your SQL Server applications. The include:

Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE)

Update tablename set ColumnName1 = (case ColumnName1 when 'Yes' then 'No' else 'Yes' end).

In TSQL OFFSET-FETCH filter is designed similar to TOP but with an extra element. It helps to define how many rows you want to skip before specifying how many rows you want to filter.

Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found.

SELECT Name FROM tblPlayer WHERE Name Like '%[''%'']'.

The limitations of the IDENTITY column is that column values cannot be updated once generated. Also, it may require to specify this column as a PRIMARY KEY, as such, there is a possibility of duplication of values within a table. Identity property is applicable for integer based column only.

Column(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table. The column(s) that make up the unique index can be nullable. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. That is one of the things to look out for when designing/developing/porting applications across RDBMS.

There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).

Also, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index. This is just the default behavior though and can be changed at creation time, if needed.

In TSQL, SET statement allows you to change the current session handling of specific information like: system language, dateformat, lock timeout, rowcount and so on.

To backup the entire database, use the following syntax;

Sp_pkeys is part of  Catalog Stored Procedures and returns primary key information for a single table in the current database.

In T-SQL OFFSET-FETCH filter is designed similar to TOP but with an extra element.  It helps to define how many rows you want to skip before specifying how many rows you want to filter.

In TSQL, the syntax used for partition is,

[ database_name. ] $PARTITION.partition_function_name(expression)

TOP limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server. When TOP is used in combination with the ORDERBY clause, the result set is limited to the first N number of ordered rows. Otherwise, it retrieves the first N number of rows in an undefined order.

The maximum number of rows that can be constructed by inserting rows directly in the VALUE list is 1000.

Rollup and cube are the grouping sets used along with GROUP BY clause to generate summarized aggregations. These are mainly used for Data Audits and Report Generation.

‘GO’ is not a Transact-SQL statement but a batch separator. It is a command identified by the sqlcmd and osql utilities and SQL Server Management Studio Code editor. SQL Server utilities read “GO” as a signal that they should send the current batch of TSQL statements to an instance of SQL Server.

Yes – There are Six commands available to import data directly into the T-SQL language. These commands include:

The difference between T-SQL and SQL is that SQL is a query language to operate on sets, while TSQL is a proprietary procedural language used by MS SQL Server. Also, T-SQL has a different implementation of DELETE and UPDATE than SQL.

BulkCopy is a tool used to copy huge amount of data from tables. BULK INSERT command helps to Imports a data file into a database table or view in a user-specified format.

You use a linked server to connect to a database on a different server.

You use a synonym to specify the object (e.g. table) you want to access in SQL, it is like an alias.

You can let point a synonym to an object of a linked server, but you still need that linked server.

To backup entire database, use following syntax;

When an error occurs in a transaction within a TRY block, and if the error is not serious it enters into a status open and Uncommittable. In an uncommittable state, the transactions cannot perform any action that would generate a write to the transaction log.

A window function is a function that's applied to a set of rows defined by a window descriptor and returns a single value for each row from the underlying query. The purpose of the window descriptor is to define the set of rows that the function should apply to. You provide the window specification using a clause called OVER.

The syntax for using SQL_Variant_Property is,

SQL_Variant_Property (expression, property)

T String functions available in T-SQL are,

These are the  operations that can be performed using SQL database:

Data Definition Language: DDL is that part of SQL which defines the data structure of the database in the initial stage when the database is about to be created. It is mainly used to create and restructure database objects. Commands in DDL are:Create table
Alter table
Drop table


Data Manipulation Language: DML is used to manipulate the already existing data in the database. That is, it helps users retrieve and manipulate the data. It is used to perform operations such as inserting data into the database through the insert command, updating the data with the update command, and deleting the data from the database through the delete command.

Data Control Language: DCL is used to control access to the data in the database. DCL commands are normally used to create objects related to user access and also to control the distribution of privileges among users. The commands that are used in DCL are Grant and Revoke.

Transaction Control Language: It is used to control the changes made by DML commands. It also authorizes the statements to assemble in conjunction into logical transactions. The commands that are used in TCL are Commit, Rollback, Savepoint, Begin, and Transaction.

The three ways that Dynamic SQL can be issued are:

A sub-query is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

A sub-query can be used with the statements like Update, select, delete and insert with the operators like =, >, <, >=,<=, etc.

The TSQL command IDENT_CURRENT returns the last identity value produced for a specified table or view.  The last identity value created can be for any session and any scope.

A local variable is defined using TSQL by using statement “DECLARE” and the name of the local variable should begin with “@” sign as the first character of its name. For example, integer CNT we will define local variable as,

“GO” is not a Transact-SQL statement but a batch separator. It is a command identified by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.  SQL Server utilities read “GO” as a signal that they should send the current batch of TSQL statements to an instance of SQL Server.

•LEFT(character_expression, integer_expression ) – It returns the left part of a character expression with the specified number of characters.
•CHARINDEX( findTextData, text data, [startingPosition] ) – It returns starting position of an expression in a character string, and starting position is optional.
•REPLACE( textData, findTextData, replaceWithTextData ) – It replaces a new value for occurrences of text found in the string.
•REVERSE( character_expression ) – It returns the reverse of a character expression.
•LEN( textData ) – It returns the length of the string, excluding trailing blanks.
•LOWER ( character_expression ) – After converting an uppercase character to lowercase, it will return a character expression.
•LTRIM( textData) – Leading blanks will be removed.
•PATINDEX( findTextData, textData ) – It returns the starting position integer value of text found in the string.
•REPLICATE(character_expression, integer_expression ) – It repeats a character expression for a specified number of times.
•RTRIM( textData) – Removes trailing blanks. SPACE( number of spaces ) – It repeats the space value specified a number of times.
•STUFF( textData, start, length, insert text data ) – It deletes a specified length of characters and inserts another set of characters at a specified starting point.
•SUBSTRING( textData, startPosition, length ) – It returns portion of the string.
•UPPER( character_expression ) – It returns a character expression with lowercase character to uppercase.

The difference between SQL and TSQL is that SQL is a query language to operate on sets, while TSQL is a proprietary procedural language used by MS SQL Server.  Also, T-SQL has a different implementation of DELETE and UPDATE than SQL.

SQL Server

T-SQL  stands for Transact Structured Query Language. It is an extension of SQL functionality supported by Microsoft SQL Server and Sybase ASE.

Transact-SQL is Microsoft's and Sybase's proprietary extension to the SQL used to interact with relational databases.

T-SQL

What is the general guidance on when you ought to use forged as opposed to convert? Is there any performance troubles related to deciding on one as opposed to the alternative? Is one toward ansi-sq.?

T-SQL Cast versus Convert

Are disabling and enabling foreign key constraints supported in sq. Server? Or is my only choice to drop and then re-create the constraints?

How can foreign key constraints be temporarily disabled using T-SQL?

Academia has it that table names should be the singular of the entity that they shop attributes of.

I dislike any t-sq. That calls for square brackets around names, however I have renamed a users table to the singular, forever sentencing the ones the usage of the desk to once in a while must use brackets.

My intestine sense is that it's far greater accurate to live with the singular, but my intestine feel is also that brackets imply undesirables like column names with spaces in them etc.

Table Naming Dilemma: Singular vs. Plural Names

I have installed VS code tool on windows and exploring the functionality. So far, i'd say it is an awesome tool which has some cool features than traditional SSMS. The difficulty I'm facing is, usage of debug in VS Code.

When I click the debug, it is giving me blank side windows next to activity bar like callstack,breakpoints etc., and it seems like standard vs code installer supports only 4 languages. I am not able to see SQL in the bar.

Can anyone guide me on HOWTO debug the SQL objects like stored procs, functions inside the console?

SQL debug in vs code

Query Store is unable to capture information about adaptive queries because adaptive plans change on every execution.

I couldn't find an event to capture adaptative query plan execution, is there any event for that?

I tried to create a session to capture query_post_execution_showplan and filter showplan_xml to capture only adaptative queries, but there isn't a pred_compare for xml fields, so it's impossible to filter the showplan_xml field, I would need to capture the showplan_xml for all the queries being executed and filter later, that's not a good solution.

So, the main question: How can I track adaptative joins in a way that allows me to identify how many executions are using nested join and how many are using hash joins?

How to track adaptive joins

I am trying to insert an image into a table in SQL Server using OPENROWSET and the bulk command.

However, I do not have permission to use the bulk load statement..

I have been using SqlLobEditor for a while now, but sadly my trial has run out and I cannot upload my blobs anymore. The system in itself is quite legacy, and I cannot get the permissions I need to be able to do this with T-SQL.

Are there any free alternatives to SqlLobEditor I can use that does the same? For some reason that worked, but using T-SQL doesn't, so I don't quite understand what SqlLobEditor does that's different.

I've also thought about writing some web client way of doing it, but as of now I'm not allowed to do that either...

Insert blob in table without bulk load permissions

I have a need to create a function the will return nth element of a delimited string.

For a data migration project, I am converting JSON audit records stored in a SQL Server database into a structured report using SQL script. Goal is to deliver a sql script and a sql function used by the script without any code.

(This is a short-term fix will be used while a new auditing feature is added the ASP.NET/MVC application)

There is no shortage of delimited string to table examples available. I've chosen a Common Table Expression example http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

Example: I want to return 67 from '1,222,2,67,888,1111'

Using T-SQL, return nth delimited element from a string

How can I implement name matching function in sql? I searched online, but I found them using T-Sql for name matching. I want to implement name matching in sql, so that I pass two strings to that function and get matching score in return. 

You must fix the names in the database. Databases are meant for exact matches, not "looks mostly like". The most simple fix is probably to export the table in a CSV format, load it in Excel (two columns: Primary key and city name), and then use a spell checker to fix the names.

Name matching in sql

How can I insert Arabic characters into a SQL Server database? I tried to insert Arabic data into a table and the Arabic characters in the insert script were inserted as '??????' in the table.

I tried to directly paste the data into the table through SQL Server Management Studio and the Arabic characters was successfully and accurately inserted.

I looked around for resolutions for this problems and some threads suggested changing the datatype to nvarchar instead of varchar. I tried this as well but without any luck.

How can we insert Arabic characters into SQL Server database?

How to insert Arabic characters into SQL database?

select pt.TrTp, sum(pt.TrTp)
from ProdTr pt
where TransSt & 16 <> 16  // this is the row that i don´t understand..
group by pt.TrTp

What does "&" means in this sql where clause?

I'm trying to use T-SQL regex on postcodes. I'm trying to get a condition where

If the first letter is a W then the second must be S

If the first letter is a B then the second must me a number

but it doesn't do conditions, this would also bring out postcodes with W1 for example, which I don't want.

T-SQL Regex on postcodes

Basically, I am looking for a way to pull the date that the SQL instance was created, using TSQL, ie a CreateDate for the server itself. I could find nothing in the docs or the site. I know I can go scrolling through the system to find a date in the file system, but thought that their might be a way to do it as easily as I can for sys.objects. I thought that maybe I could use a CreateDate off of a table in master or msdb, but that seems to be the date that MS created those. I am running 2019. Any suggestion would be helpful.  

SQL Server Create Date From TSQL

Is there any tool -or way- to extract control flow from a MSSQL script automatically? I want to simplify a large TSQL code, knowing which tables are used or joined and what are flow branches.

here is the same tool for MySQL: Tool for convert SQL code to diagram

is there any SQL code control-flow analysis tool?

I have a number like this 1628864262422 and I wish to to convert it to datetime in SQL Server. How do I do this? 

T-SQLWatch

Trending Blogposts on T-SQL

Overview of T-SQL

Difference between SQL and T-SQL

Blogpost

Discussion Threads

Interview Questions

Cheatsheets

Technologies

Questionaire

Popular Discussions on T-SQL

openT-SQL Cast versus Convert

1

0

1

openHow can foreign key constraints be temporarily disabled using T-SQL?

1

0

0

openTable Naming Dilemma: Singular vs. Plural Names

1

0

0

openSQL debug in vs code

1

0

0

openHow to track adaptive joins

1

0

0

openInsert blob in table without bulk load permissions

1

0

0

openUsing T-SQL, return nth delimited element from a string

0

0

0

openName matching in sql

2

1

0

openHow to insert Arabic characters into SQL database?

1

0

0

openWhat does "&" means in this sql where clause?

1

0

0

openT-SQL Regex on postcodes

1

0

0

openSQL Server Create Date From TSQL

1

0

0

openis there any SQL code control-flow analysis tool?

1

0

0

openHow to convert number to datetime in SQL

0

0

0

PRODUCTS

QUICK LINKS

LEGAL

T-SQLWatch

Trending Blogposts on T-SQL

Overview of T-SQL

Difference between SQL and T-SQL

Blogpost

Discussion Threads

Interview Questions

Cheatsheets

Technologies

Questionaire

T-SQL

T-SQL