Skip to main content

Understanding T-SQL

T-SQL is the term coined by Microsoft & Sybase collaboration. It's the proprietary extension of SQL of Microsoft & Sybase. T-SQL is the main RDBMS to manage & manipulate the data in sql server. Like SQL, you don't need to write strongly type T-SQL. SQL server will parse the T-SQL into sql then execute the parsed query on top of the relational database model.

Let's try to understand the T-SQL

1. SQL is a standard of ISO & ANSI. T-SQL is a dialect of SQL.
2. Every database vendors (oracle, sybase..) implement the dialects of SQL as the main language to handle data in their database. Therefore core language element is same across all the database vendors.
3. SQL is strongly typed checked. Every SQL statement should end with semicolon (" ; ")                 T-SQL: it's not mandatory to end the every statement with " ; " semicolon.                              e.g. "Let's say we've an employee table and we want to fetch all the records"                          Using SQL syntax ( semicolon is mandatory): following query will give compilation error "SELECT * FROM Employee"                                                                                                      Using T-SQL syntax ( semicolon is optional): same query will execute successfully "SELECT * FROM Employee "
4. SQL: to get the result set from underlying table or sub-query it's mandatory to have "SELECT", "FROM" & "" T-SQL: To get the result set only "SELECT" is the mandatory key-word. Let's say you want to see the current date. Using SQL: you've to take the help of other data source "SELECT FROM Employee WHERE 1 = 0; " Or some dummy table\view provided by your database vendors "SELECT FROM DUAL ; " Using T-SQL: "SELECT GetDate() "
5. As per relational model all the attributes should have some name. But using T-SQL we can get the result-set based on the expression or column levele sub-queries without any name "SELECT Id , FirstName + ' ' + MiddleName + ' ' + LastName FROM Employees "
6. According to relational model all attributes of result-set should have unique name. T-SQL allows the query to return result-set with duplicate column-names. T-SQL example: "SELECT e.Id , d.Id FROM Employees as e JOIN Department as d ON e.DepartmentId = d.Id "

Comments

Post a Comment

Popular posts from this blog

How to install Zen-coding plugin

As a web-developer, irrespective of the technologies (java, c-sharp, python, php e.t.c.…), we used to write CSS code to make our web-pages looks good if not at least we’re involved in write html codes. What if there is some tool to whom you give some instruction and that tool generates a good, well formatted html tags for you. These kind of coding is possible and known as “ Zen coding ” and there are lots of plug-in available from different vendors. We’re going to  discuss the steps to install this “zen-coding” plugin for “visual studio, eclipse, sublime-text & notepad++ in next few lines. Follow the below steps to install "zen-coding" plugin based on your editor.  Steps to install zen-coding plugin for (visual studio, eclipse, sublime-text,notepad++) -- -- For Visual Studio 1. Go to "Tools" -> "Extensions and Updates" 2. It'll open the "Extensions and Updates windows"    Select online form Left hand menu items ent...
Post a Comment
Read more

EFCore - Scaffold-DbContext how to use

EFCore versions, we don't have GUI to manage the DB-entities & dbContext like we used to have with EF6 (or EF5/4 version). With EFCore we've the Scaffold-DbContext command which helps us to create entities & DB context from our DB-objects.  Let's see how to use Scaffold-DbContext: Assume we're connecting with SQL Server so after creating a sample project you'll have to install EFCore related libraries as mentioned below.  Install entity-framework-core for SQL Server, at this time 3.1.9 is the latest stable version   Microsoft.EntityFrameworkcore   Microsoft.EntityFrameworkCore.SqlServer  To use Scaffold-DbContext, following two NuGet packages are required Microsoft.EntityFrameworkCore.Tools  Microsoft.EntityFrameworkCore.Design Let's say we've two DB tables (e.g. dbo.Employees & dbo.Users )  Now let's see how to use Scaffold-DbContext to create c-...
Post a Comment
Read more

Azure SQL - Re-build index and update stats

Useful script to rebuild table indexes and update stats You can update the queries based on your requirement. To update the table stats --Query to update the table stats SELECT N'UPDATE STATISTICS ' + t.TABLE_SCHEMA + '.' + t.TABLE_NAME + ' ;' FROM INFORMATION_SCHEMA.tables AS t WHERE t.TABLE_TYPE = N'BASE table'; GO To re-build table indexes: --To rebuild table index DECLARE @myDatabaseName NVARCHAR(255) = N'your-database-name'; DECLARE @runningTableName NVARCHAR(255); DECLARE @cmd NVARCHAR(1000); DECLARE @name NVARCHAR(100)= 'your-database-name'; -- Populate the database cursor. DECLARE myDatabaseLevelCursor CURSOR READ_ONLY FOR SELECT @name; OPEN myDatabaseLevelCursor; FETCH NEXT FROM myDatabaseLevelCursor INTO @myDatabaseName; WHILE @@FETCH_STATUS = 0 BEGIN -- In this command we're declaring table level cursor SET @cmd = 'DECLARE myTableCursor CURSOR READ_ONLY FOR SELECT '...
Post a Comment
Read more