T-SQL is a language used for working with databases on the
MS SQL Server platform. You can find a lot of articles and tutorials about T-SQL in this section. If you want to learn SQL or just read how to work with SQL function you don’t know yet, check out the SQL tutorials. I want every article to contain an example of usage and a code example too
Below, you will find a basic chronological outline that I would recommend following to learn SQL step by step. Use other resources as well to complement and expand your knowledge. If you thoroughly cover these topics, whether here on the website or elsewhere, it will give you a solid foundation for effective work with databases, automation, BI, reporting, applications, and more. You can lay the foundation for:
Before studying, I recommend installing the necessary tools (in this order) so that you can immediately practice the newly acquired knowledge:
After studying these articles, you will get acquainted with the basic syntax of the T-SQL language and build simple SQL queries:
After studying these articles, you will be able to independently write simple query scripts for one table:
In a relational database, we typically don’t join just one table but several (in the FROM clause). There are several types of table joins. After studying these articles, you will understand the differences between different types of joins and be able to write more complex scripts by joining multiple tables. In practice, understanding and correctly choosing joins in various situations is probably the biggest challenge when learning the SQL language (moving from a complete beginner to moderately advanced):
After understanding the basic structure of the language, you can see how we can work with data (similar to Excel) through functions. These functions are most commonly used in the SELECT clause. Below are a few examples of the most commonly used functions; you can find more in the relevant category on the website:
Until now, we’ve dealt with what’s called DQL (Data Query Language) – querying. After understanding how to query tables, we can move on to creating tables and commands that either modify or delete records in the table:
If we can prepare data in the database and efficiently query it, we can then pull the query results into tools like Excel or Power BI:
SQL CTE, or Common Table Expression, is essentially a temporary result set represented in the form of an expression. Once declared using the WITH clause, it can be referenced in SELECT, INSERT, DELETE, or UPDATE scripts. SQL CTE (common table expression) Syntax WITH Alias_Query AS ( SELECT Column1, Column2 FROM dbo.Table ) SELECT * FROM Alias_Query; We… Read More »
We can add a column to the table in two ways. First is using sql server management studio graphical interface. Second one is using SQL command ADD COLUMN. T-SQL ADD COLUMN Syntax: ALTER TABLE dbo.Table ADD <Column> <Data Type> NULL (or) NOT NULL Column will be created on the end of the table. If the… Read More »
We distinguish two types of commands with data deleting purpose – SQL commands DELETE and TRUNCATE. Each is suitable for different situations. It is good to know them both and be able to choose the right one in a specific situation. DELETE Syntax (First Command is Optional): DELETE FROM dbo.Table WHERE <Condition>; or DELETE dbo.Table WHERE… Read More »
Creating a table in the SQL Server database is a basic skill. While creating it using SQL command CREATE TABLE we define: Name of the table together with database definition and scheme List of the columns with appropriate data types, constraints and definition if the column can be empty (NULL vs. NOT NULL) Table can… Read More »
ISNULL and COALESCE are functions used for work with null values (NULL). Both functions return first value out of previously defined entry parameters. There are also some differences between both functions. ISNULL Syntax: SELECT ISNULL(<Column>, <Column Replacement – other column or logic>) FROM dbo.Table COALESCE Syntax: SELECT COALESCE(<Column>, <Column Replacement – other column or logic… Read More »
Views have definitely their place in databases and data warehouses. We are talking about objects which are good to use since they do not contain data (do not take up place in storage). They consist only of table query. More complex views can be more expensive for logical operations mostly with complicated queries with multiple… Read More »
UPDATE Statement is used in SQL to change existing records in a table. This way, we can change one record, multiple records or all of them. Condition based on which are records restricted is placed in WHERE clause. Syntaxe – 3 Ways of UPDATE Usage A) Simple form: Change of the values in certain column… Read More »
This mini-article is intended for beginners. We will take a closer look at how to write a simple T-SQL script, and I will also explain basic SQL commands. We will assemble the individual parts of the script step by step and explain their purpose as we proceed. Let’s dive into SQL basics in the following… Read More »
T-SQL TRUNCATE command deletes all records in the table. Apart from DELETE (more in article SQL DELETE) command, there is no possibility to filter which record to delete. It is all or nothing. Truncate is on the other hand much faster compared to DELETE since it writes less into transaction log. SQL TRUNCATE command Syntax… Read More »
SQL MERGE command is an advanced way of comparing data in two tables (Source and Destination). We compare records and based on match (or difference) we execute UPDATE, INSERT or DELETE according to the result of the table comparison. We will use this mainly with data sync between objects/systems or with ETL processes in environments… Read More »