In this topic, let us try to understand the intrinsic details of what database normalization is and also, at the same time, we will try to understand the concepts of T-SQL in Microsoft SQL Server. Though the first concept is more relevant to almost all the relational databases available in the market today, the second concept that we would be discussing here would be very much specific to Microsoft’s SQL Server database alone. Let us go through these two concepts in detail and understand these to the core.
To understand the term normalization in a generic sense, it means to bring something to a normal condition or state. But this term has a very specific meaning for it in the areas of Databases. Hence, let us try to understand the concept of Normalization in the context of databases. Database normalization is the process of reorganizing data in a relational database in accordance with the series of so-called normal forms in order to reduce the amount of redundant data and also, at the same time, to improve upon the data integrity. The two principles that govern the process of Database Normalization are:
Database normalization was proposed and invented by Edgar F Codd and is an integral part of his relational model. He is also considered the Father of the relational data model. Almost all the relational databases engines that exist today still use the rules laid by him. As per the rules laid by him, he had extracted the first 3 Normal Forms (acronym as NF) – namely 1NF, 2NF and 3NF. He had proposed the theory of Normalization with the introduction of First NF and then continued / extended the theory with the Second and Third NFs. This theory had then been extended with the help of Raymond F Boyce to form the BCNF. We will discuss these in further detail in the following sections for better understanding.
With the understanding that we have gained through the sections that are covered till now, let us expand our horizon of understanding by going through each and every one of these normal forms. Let us now go through each and every one of these Normal Forms and also understand what it brings to the table when it is applied.
First Normal Form (1NF):
These definitions would help in understanding the concepts of a table, database and etc. Please take a look at these if you feel are required:
Second Normal Form (2NF):
Third Normal Form (3NF):
Boyce-Codd Normal Form (BCNF):
Fourth Normal Form (4NF):
Fifth Normal Form (5NF):
Domain/Key Normal Form(DKNF):
Sixth Normal Form(6NF):
T-SQL, often abbreviated as Transact SQL, is a proprietary extension to SQL owned by Sybase and Microsoft SQL Server. T-SQL expands the horizons on the SQL standard by including more features than what SQL standards provide. T-SQL is much more centralized to SQL Server as such and most of the operations that are performed in SQL Server are done via T-SQL. This is very much true in case of GUI tools such as SSMS or DBeaver. From now, remember that any operation that you will and wish to do on Microsoft SQL Server on any given GUI tool, it is all T-SQL that runs in the background. T-SQL is proprietary in the case of Microsoft SQL Server and also Microsoft Azure SQL database.
Transact SQL (T-SQL) is not the only extension to the already set SQL standard, there are many as such. T-SQL is an extension owned by Sybase & Microsoft SQL Server. PL/SQL is an extension owned by Oracle whereas PL/pgSQL is an extension that is owned by PostgreSQL folks. Although there are various advantages that you gain while using it, it makes the process of moving from one database system to another extremely difficult.
With the understanding that we have on T-SQL, let us deep dive into the features that it provides to us. Some of the features that T-SQL provides (which are rather not provided by SQL) are as follows:
We have already discussed in the earlier sections that T-SQL is a proprietary version of SQL standards for Microsoft SQL Server just the same way as PL/SQL is for Oracle database. An individual can create the required T-SQL units such as any other SQL scripts, Triggers, Functions or Stored procedures etc. to cater to specific requirements. Hence, to cater specific requirements on SQL Server, there can be specific code written such as a Trigger to handle the audit on a specific table or something.
Such requirements can be handled on the SQL Server instance itself using some SQL code, since SQL Server has its own notion of its syntax in the form of T-SQL – the database artifacts thus created would be called T-SQL pieces of code rather than SQL code. Mentioning it T-SQL, you’re drawing a definitive line of difference that it works only with SQL Server and may / may not work with the other database vendors – as there can be changes to the syntaxes, functionalities that are not made available for usage and the like.
Let us try to understand the differences between SQL & T-SQL in detail in this section here. Let us go through each point to understand the strengths of each of these that we are discussing here:
|SQL (Structured Query Language) can be defined as a language to operate over sets of data.||T-SQL is a proprietary procedural language designed specifically for Microsoft SQL Server, and can also be said that T-SQL is an extension to SQL|
|SQL is an open format, which has been followed by various database providers (eg. Oracle, Sybase, PostgreSQL, MySQL, MS SQL Server)||T-SQL is proprietary to Microsoft SQL Server|
|SQL is not Turing complete and it is very limited in the scope of what can be done with it.||T-SQL is Turing complete|
|SQL doesn’t have any procedural programming on its own||T-SQL on the other hand contains procedural programming, local variables.|
|SQL has its own implementation of DELETE and UPDATE||T-SQL has its own implementation of DELETE and UPDATE than that of SQL.|
There are numerous advantages that are available for us to discuss here and we will just do that. Let us take a closer look at the advantages that Normalization provides us:
Let us now take a closer look at the advantages that T-SQL provides us:
In this article, we have tried to understand the concepts of Normalization and T-SQL (pertaining to MS SQL Server). We have also gone through the various available forms of normalization in a RDBMS system. We have tried to focus on the features that T-SQL has to offer and also have gone through the advantages that it brings to the table with its usage. On the whole, these two concepts put to use in an efficient manner will offer a greater performance to your application.
We have taken utmost care to provide you best of the details, but we would still encourage you to go through the official documentation for the latest updates as things are getting changed on a quicker pace. Hope that you have got all the details at once and hope all the details provided were of good use as well.
|SSAS||SQL Server DBA|
|Team Foundation Server||BizTalk Server Administrator|
Free Demo for Corporate & Online Trainings.