Category Archives: SQL Tutorials

Datový skladT-SQL is a language used for working with databases on the MS SQL Server platform. You will find a lot of articles and tutorials about T-SQL in this section. If you want to learn SQL or just remind yourself how to work with SQL function, check out the SQL tutorial. I want every article to contain an example of usage and a code example too. List of Articles in category »

SQL CREATE TABLE- How to Create a Database Table (SQL Server)

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 »

SQL ISNULL and COALESCE Functions – What Are The Differences And Usage

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 »

SQL View – Create, Alter, Drop View? Do you know Indexed view?

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 »

SQL UPDATE Statement – Syntax and Example of Updating Records

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 »

SQL TRUNCATE TABLE Command – Don’t use DELETE when you don’t have to

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. TRUNCATE Syntax TRUNCATE TABLE dbo.Table;… Read More »

SQL MERGE Statement – INSERT, UPDATE, DELETE at once

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 table comparison. We will use this mainly with data sync between objects/systems or with ETL processes in environments like… Read More »

SQL String Functions – Overview of Text Functions in T-SQL

T-SQL is not a language designed primarily to do something big with text strings or manipulate with them. That is the reason why we will not find many text-related built-in functions. But we will definitely find use for those that are there. Let’s take a look at SQL String Functions. 1) Text Concatenation (Combining) –… Read More »

Top 10 Most Useful MS SQL System Stored Procedures

System stored procedures are useful administrative tools. Below, you may find 10 of them which I consider as the most useful: sp_help – info on objects in database, data types sp_spaceused – displays space on the disk allocated to database and storage for each database objects including number of rows in the table sp_who – returns… Read More »

SQL OVER() with PARTITION BY – Clause for Gourmets With Examples

OVER clause can belong to commands working with so-called Window functions in SQL. These types of functions enable us to look at the data not only in their aggregated form (using GROUP BY), but we can also look at functional operations via multiple other attributes. We, in fact, create windows of some sort and we… Read More »

SQL Ranking Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

SQL ranking functions enable us to rank records in the table based on values of a field or multiple fields. Clause OVER() is mandatory for the ranking functions. Ranking functions belongs to the group of Window functions. Aggregate functions and Offset functions (for paging) also belong to this group. List of Ranking Functions ROW_NUMBER ()… Read More »

SQL ORDER BY – Sorting Data in SQL Table in Descending or Ascending Order

ORDER BY clause in SQL enables us to sort results of the query by specific field (column) or more columns. There are 2 sorting options: Ascending order (ASC) – sorts records from lowest to highest in case of numbers and from A to Z in case of text strings Descending order (DESC) – sorts records… Read More »

SQL OFFSET Functions – LAG, LEAD, FIRST_VALUE, LAST_VALUE

OFFSET functions are relatively new to MS SQL Server. They are available since SQL Server 2012 version. These functions enable a user to “list” through rows of a table. To be precise, it makes you able to put hand on previous or next row while still at the current one. These functions belong to a… Read More »