Learn SQL and IT with tutorials

T-SQL

Reorganize a SQL Server index

How to reorganize a SQL Server index? This operation defragments the bottom level of the indexes by physically reordering the pages, from left to right. The reordering operation also compacts the pages of the index. To better understand this, imagine a recipe book whose pages are no longer in order. You must then reorganise the pages to quickly reach the desired page. Reorganize a

T-SQL

Create a SQL Server view with encryption

How to create a SQL Server view with the encryption option? The WITH ENCRYPTION option allows you to hide the view code. Here is an example of code in T-SQL language to create a view with the WITH ENCRYPTION option. Create a SQL Server view with the encryption option First, here is the original tutorial with the script for creating the sales view used

T-SQL

SQL Server partitioning

SQL Server table partitioning allows to physically split the data of one table on the disk. Partitioning means data division that allows to directly access one unique partition instead of all the data from the table. First, why all large tables should have partitions? When accessing hundreds of million lines, it becomes very tricky to get a large bunch of data in seconds. Partitions

IT Concepts

OLTP and database transactions

Definition of OLTP, that stands for On Line Transactional Processing and database transactions. The abbreviation OLTP refers to all databases designed to manage data processing in a transactional and reliable manner. This is for general operational management purposes. Examples include accounting data, supermarket sales data such as receipts and bank transactions. Definition of transactional databases or OLTP: On Line Transactional Processing First of all,

T-SQL

Create a SQL Server partition

How to create a table partition in SQL Server? Let’s consider a large table with Sales data. So, this table have a column that stores the year of the sale and the table stores millions of lines. Let’s consider also that some business reports read this data to display yearly totals and compares them to the previous year totals. It is called a year

T-SQL

Create a partitioned view in SQL Server to group tables

How do you create a partitioned view in SQL Server and group data from multiple tables? This script is a simple example of creating a view that points to three different tables. In this example, each Sales table would contain the sales of a month or a quarter, to optimize performance. First, what is a partitioned view ? A partitioned view joins horizontally the

SSMS

Check if object exists with SSMS

How to add or remove a script to check if an object exists in SQL creation scripts generated with SQL Server Management Studio (SSMS)? To add dynamically the existence check before generating the create statement for you SQL Objects like tables, views,  functions or stored procedures, it’s easy. Simply activate this SSMS option to check object existence in your SQL Server Management Studio software. For example,

MySQL

Create a table in MySQL

To create a table in MySQL, start by connecting to the MySQL server from PHPMyAdmin. Indeed phpMyAdmin (PMA) is the central interface to manage MySQL databases and tables. MySQL is an Open Source database, which means that it is free software. To start, connect to the MySQL server and database from a web browser like Google Chrome or Firefox for example. How to create