Practical T-SQL tutorial to learn SQL Server programming online, by example and with progressive iterations.
Welcome to our T-SQL tutorial platform, dedicated to IT and SQL Server, with a focus on data management tools! We offer online this SQL tutorial to help our visitors master the SQL language, starting with the basics of T-SQL. Topics cover various operations such as inserting data into a table using INSERT or INSERT INTO, displaying data with a SELECT queries, and creating stored procedures and functions.
It’s important to note that in our examples, the SQL Server version isn’t specifically mentioned. Since SQL language is standardized, most of the functions don’t change between Microsoft DBMS versions. Our examples work on older SQL Server versions like 2008, 2008 R2, and 2012, as well as newer versions like 2016, 2017, 2019, and 2022. You can also apply these operations directly on Azure SQL databases. We’re excited to offer these articles and full tutorial to help you learn SQL Server. However, of course some newer features will only be available on latest versions, this will be mentioned in the post.
Learn SQL Server online with our practical T-SQL tutorial
A Database Management System (DBMS) is a software designed to manage, manipulate, and store data in the form of databases containing tables. It uses SQL code (Structured Query Language) to execute queries to manipulate or display data stored in the databases. T-SQL is the abbreviation for Transact-SQL and it is Microsoft’s SQL programming language tailored for their SQL Server platform.
Table of Contents
1. T-SQL Tutorial about the basics
First, simple examples of queries using the SELECT clause for example. Learn also to create some tables.
- Create Table example queries
- Select queries to display, filter and sort data
- SQL Server data types
- Create a table with auto incremental column ID
- Compare values using logical operators in T-SQL
- Use the IF THEN ELSE equivalent in SQL Server
- Use the CASE conditional statement in a query
- Variables in SQL Server Programming
2. Tutorial to manage tables and columns using Transact-SQL
This second section is about to manage tables in a SQL Server database, but also columns within tables. This section contains also articles about Temporary tables and Common Table Expressions (CTE).
- Create a SQL Server table with a primary key
- How to modify a SQL Server table with a script?
- Empty data from a SQL Server table
- Drop a SQL Server table with a script
- Check if a table exists
- Modify a SQL Server column with a script
- Manage SQL Server temporary tables using T-SQL code
- Call a SQL Server CTE multiple times
- The SQL Server JOIN operator
- LEFT JOIN examples
3. Insert and update data using T-SQL queries
Next, a section on inserting data with the INSERT clause. Learn data insertion queries with T-SQL. Explore a basic example of an INSERT query in SQL Server, understand the process of inserting data into a table using a SELECT INTO query, and gain proficiency in creating dynamic INSERT INTO queries using SQL Server variables. Learn about the practical implementation of Insert or Update methods, also called Upsert and discover how to update the same column in another line with SQL Server.
- Simple INSERT query example with SQL Server
- Insert data into a table with a SELECT INTO query
- Insert or Update methods with SQL Server (Upsert)
- Update the same column of another line with SQL Server
- Create a dynamic INSERT INTO query using SQL Server variables
- Update a table from a Select query
- Use the Group By to aggregate data
4. Manage SQL Server views with scripts
In this third section, learn how to effectively manage SQL Server views with scripts, learn to create a SQL Server view, manage SQL Server views using T-SQL. And also how to modify a SQL Server view, create SQL Server partitioned views, and drop a SQL Server view.
- Create a SQL Server view using a script
- Modify a SQL Server View
- Drop a SQL Server View
- Test if a view exists in SQL Server
- Create SQL Server partitioned views
- Query to create a View with Encryption
5. Work with SQL Server strings variables and columns
In SQL Server programming, it is important to learn practical aspects of working with strings and text types. From handling various data types to efficiently managing lengthy strings and converting XML to NVARCHAR, we’ll explore essential techniques that simplify database operations. Get ready to enhance your SQL Server skills with insights into string manipulation, variable storage, and the utility of the SUBSTRING function.
- How to manage line breaks in T-SQL ?
- Split string with delimiter into one column with SQL Server
- Script to split a string just after a specific character
- How to split a text into multiple columns ?
- Store a SQL Server column in a variable with delimiters
- Manage SQL Server string longer than 8000 characters
- Convert data from XML to NVARCHAR
- How to use the SQL Server SUBSTRING function ?
- Concatenate multiple strings
6. Manage SQL Server stored procedures
In this section, you’ll acquire the skills to create stored procedures, ranging from simple ones to those with input and output parameters. Stored procedures, as T-SQL code blocks saved on the SQL Server, offer reusability and can be called from any connecting application or program. This procedure section promotes efficient data processing sharing among users, because it is all about reusability. The content will guide you through the creation and management of stored procedures in SQL Server, emphasizing best practices for efficiency and error avoidance.
- How to create a SQL Server stored procedure ?
- More stored procedures examples
- How to modify a stored procedure ?
- T-SQL Tutorial to create a stored procedure with parameters
- Execute a stored procedure with input parameters
- Create a stored procedure that returns values
- Insert data within a SQL stored procedure using variables
- T-SQL script example to pass a list of values into a stored procedure
- Stored procedure using a table valued parameter
- Create and execute a stored procedure with hard coded variables
- Drop SQL Server stored procedures without errors
7. SQL Server Functions
The section number 7 discusses managing SQL Server functions, also called User-Defined Functions to distinguish them from the built-in system functions.
- Create an SQL Server function
- Call a SQL Server function from a T-SQL script
- Modify an SQL Server function
- Query to drop a SQL Server function
- Test the existence of a function before deletion
- How to use table valued function in SQL Server ?
8. SQL Server programming tutorial to manage dates
Explore the topic of T-SQL date and time functions as we delve into practical scripts to manage dates in database. Discover the common pitfalls to handle dates efficiently in T-SQL, like how to calculate the difference between two dates with precision.
- How to manage dates in T-SQL ?
- Calculate the difference between two dates with T-SQL
- Query to convert string to date type
- Display the month name from a date in SQL Server
9. Work with SQL Server objects metadata
The SQL Server system tables allows to list all the objects from a given database, it is very useful to check if objects exists. But also to list all object very quickly.
- List all tables in a database
- Display the last modification date of SQL Server tables
- Check the last time data was updated in table
- Display all SQL Server tables size and disk space
- Check if a column exist in a SQL Server database
- List all stored procedure in a SQL Server database
- Search text within a stored procedure
- List user defined table types (UDT) in a SQL Server database
- List all SQL Server indexes in a database with a query
10. SQL Server tutorial on managing partitions and indexes
10.1 Partitions tutorial
10.2 Manage indexes using T-SQL
- Create a SQL Server Index to optimize performance
- Disable a SQL Server index using T-SQL
- Drop Index SQL Server with a Script
- Reorganize a SQL Server Index
- SQL Server Clustered Index
- SQL Server Full-Text Index Script
11. T-SQL PIVOT and UNPIVOT operators
In the world of relational databases, it’s common to find data stored as rows. However, sometimes it’s more convenient to work with data organized as columns. SQL Server’s PIVOT and UNPIVOT operators are essential tools for performing this transformation. Using these operators, you can easily convert table data stored in a row layout to a column layout, or vice versa.
- How to use the PIVOT query in SQL Server ?
- Use SQL Server UNPIVOT operator to transform columns into rows
- Dynamic Pivot query that adapt to the number of values in the table
12. Manage XML and JSON data types in SQL Server
To continue with more advanced,
- XML Data in SQL Server
- Implicit Conversion from XML to NVARCHAR
- SQL Server XML Nodes Method
- XML Query Method in SQL Server
- XML Value Method in SQL Server
- About the JSON functions in Transact-SQL
13. More advanced T-SQL tutorial
- Work with SQL Server transactions
- Insert large amount of data using the Bulk Insert query
- What is SQL Server pagination ?
- Work with SQLCMD and the command line
- Use temporal tables (system versioned tables) to manage data history
- Create SQL Server triggers to automate based on events
- Format a query automatically
- Use SQL Server cursors for processing a table line by line
A final word about the T-SQL programming tutorial
In conclusion, our T-SQL programming tutorial provide a valuable opportunity for acquiring skills in database management and manipulation. Explore the basics of SQL and T-SQL language through our online courses, covering tasks such as creating tables, inserting data, querying, filtering, sorting, utilizing aggregation functions, grouping data, and crafting stored procedures and functions.
Our examples cater to various SQL Server versions, both older and newer, enabling you to progress at your own pace on Microsoft’s Database Management System. With our courses, you’ll gain practical expertise, particularly in confidently creating and managing stored procedures in SQL Server. We invite your feedback and suggestions for an enhanced learning experience.
