T-SQL

Create a temporary table with SQL Server

How to create and manage temporary tables with SQL Server? What is a SQL Server temporary table? SQL Server temp tables are temporary tables that are used for storing data temporarily. They are created with the CREATE TABLE command in T-SQL. In MS SQL tutorial, we will learn about SQL Server temp tables and what they are used for. There are two types of

T-SQL

Drop an index in SQL Server with a script

Another IT and more specifically a database tutorial on how to drop an index from a table in SQL Server. An index is associated with an existing table in a database. It can be a relational or transactional database. For example, learn here why it is important to implement maintenance plans to improve performance and management of MS SQL resources. Drop an index in

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 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

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