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

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 SQL Server partitioned views to group tables having partitions

How to Create SQL Server partitioned views to group data from multiple tables? Find examples of scripts to create views that points to four or more different tables. In this example, each Sales table would contain the sales of a year, a quarter, or a month, to optimize performance. Create SQL Server partitioned views to group multiple tables First, what is a partitioned view?

T-SQL

SQL Server table size and disk space

Listing SQL Server table size and disk space can be very convenient specially to analyse the disk usage for each table. Three different ways, but similar are presented here to display SQL Server tables and disk space used by each table. The third solution also shows the number of lines for every table. Get all SQL Server table names with table size and disk

T-SQL

List user defined table types in SQL Server (UDT)

How to list user defined table types in a SQL Server database? First of all, users declare User Defined Table Type as a particular table structure in the database. This UDT is reusable in database, it is not a system table. It’s a good practice to ensure users can’t insert data with different types or length for example. Or even from different data structure.

days of calendar
T-SQL

Last time table was updated in SQL Server

How to check the last date a table was updated on a SQL Server database? Check the last access and update with a simple SQL query? Thanks to the SQL Server relational database management system tables, a query allows you to display the last changes done. We can apply it to any SQL Server table or view, like last scans, or last updates for

T-SQL

SQL Server ALTER TABLE

How to modify a SQL Server table using the Alter Table command? Simply connect to the server hosting the instance, the database and the table and run an ALTER TABLE SQL Server query. The goal is to update an already existing table to adapt its structure for example. Or the constraints associated to the table. The example script below modify the Sales table previously

T-SQL

SQL Server primary key

How to create a table with a primary key in SQL Server ? A database primary key is designed to allow one unique value to identify a line in a table. First, what is the purpose of a primary key? And moreover, what is the definition of a primary key? A primary key ensures that only one unique line as a specific ID. Script