Renaming Indexes with sp_rename Procedure

Not so long ago, I was working on a project where we needed to change the data type of a table. The table had millions of rows, and we decided to create a separate table using the existing table definition and insert the rows into that table. After exporting data into the new table, we renamed the old table, constraint, and indexes using the sp_rename stored procedure.

The current article is dedicated to the ways of renaming indexes. You can also read how to rename table in SQL.

CodingSight - Renaming Indexes with sp_rename Procedure
Read More

4 Ways to Count Rows in SQL Server Table with Pros and Cons

Recently, I was working on a database performance improvement project. One stored procedure was causing issues. That query populated the count of the rows and stored the value in a local variable. It was scanning a large table. Due to that, resource utilization became significantly higher.

To fix the issue, we removed the faulty code and used the SQL Server catalog views to generate the count of rows of the table.

Read More

Basics of SQL Server ALTER TABLE Statement

An SQL Server database, once created, does not remain unchanged. We constantly need to change it – add or remove tables, change their contents, modify columns and rows, etc. Understanding how to alter table in SQL is fundamental for all database specialists.

The current article will focus on the ALTER TABLE SQL server command. It lets us add, delete, or modify SQL table, and also add or drop constraints.

CodingSight - Alter Table Statement
Read More

An Overview of the PRINT Statement in SQL Server

The SQL PRINT statement serves to display the user-defined message. For example, you are developing a script with T-SQL Loops, and you want to display some specific message on each iteration of a loop. Then you can use the PRINT statement. Or, you can use it when developing a script with conditional statements. In that case, you ensure that the condition evaluated by the statement performs the correct step or generates the correct output. The PRINT statement can also be used to track the process of a T-SQL query or a stored procedure or to return the customized message.

CodingSight - PRINT Statement
Read More

How to install MySQL Server on Ubuntu 18.04 in 2 Different Ways

This article will present the step-by-step installation process of the MySQL server on Ubuntu. For demonstration purposes, I created a virtual machine using Oracle VM virtual box and set up Ubuntu 18.04 on the virtual box. I’d recommend you referring to the article Creating a Virtual Machine with Oracle VM Virtual Box for a better understanding of the installing process and creating a virtual machine on Oracle VM virtual box. Also, you may turn to the article How to Install Ubuntu 18.04 for more information about installing Ubuntu 18.04 on the virtual machine.

CodingSight - A Quick Guide on Installing MySQL on Ubuntu
Read More

Backup and Restore MySQL Database Using mysqldump

As the practical experience states, many people face difficulties with backing up and restoring the data of MySQL (and other) databases. However, backups are crucial for data safety and consistency.

The current article focuses on applying the mysqldump utility to backup and restore MySQL databases. This utility is the most common tool for generating backups in several formats or restoring MySQL databases.

CodingSight - Backup and Restore MySQL Database Using mysqldump
Read More