DEV Community: nisargupadhyay87

Step-by-Step Guide to Restoring SQL Server Databases Without Transaction Log Backup

nisargupadhyay87 — Fri, 12 Sep 2025 08:22:23 +0000

Restoring an SQL Server database is one of the most critical tasks for DBA. In ideal scenarios, we use the full, differential, and transaction log backups to bring databases back online. However, in some cases, log backups are not available due to incomplete backups, accidental deletions, or damaged transaction log files. We can restore databases without log backups, but it has some technical challenges and a certain risk of data loss.
In this article, we’ll understand the step-by-step process to restore any database without the transaction log backups. Also, we will go-through the technical challenges that we might encounter while restoring the database without transaction log backup. First, let us understand how unavailability of transaction log backups can impact your recovery strategy.

When Do You Need to Restore Without Log Backups?

You might be forced into this scenario in cases such as:

The database recovery model is set to SIMPLE, where log backups are useless.
The transaction log backup chain is broken due to missing or corrupted transaction log files.
The log backup is deleted by mistake. Sometimes, when we run out of storage, someone may delete the log backups without verifying the full and differential backups.
The database is already in FULL recovery mode, but the organization did not configure the log backups.

In these situations, you can only rely on full and differential backups for recovery. Here is the step-by-step process of restoring a database without transaction log backups.

Steps to restore the database

Let us understand the step-by-step process to restore the database without transaction log. For demonstration, we will be using the Stackoverflow2010 database.
First, we need to check the recovery model of the database. If the database is in SIMPLE recovery model, then the log backups will not be needed.

select database_id, name, create_date, recovery_model_desc from sys.databases where name='Stackoverflow2010'

Query output:

As you can see, the database is in FULL recovery model. Now let us confirm the last full, differential and transaction log backup. Here is the script.

USE msdb;
GO

;WITH BackupHistory AS
(
    SELECT 
        bs.database_name,
        bs.backup_start_date,
        bs.backup_finish_date,
        bs.type,
        bmf.physical_device_name,
        ROW_NUMBER() OVER (PARTITION BY bs.database_name, bs.type ORDER BY bs.backup_finish_date DESC) AS rn
    FROM dbo.backupset bs
    INNER JOIN dbo.backupmediafamily bmf 
        ON bs.media_set_id = bmf.media_set_id
)
SELECT 
    database_name,
    CASE type
        WHEN 'D' THEN 'Full Backup'
        WHEN 'I' THEN 'Differential Backup'
        WHEN 'L' THEN 'Transaction Log Backup'
    END AS backup_type,
    backup_start_date,
    backup_finish_date,
    physical_device_name
FROM BackupHistory
WHERE rn = 1
  AND type IN ('D','I','L') and database_name='Stackoverflow2010'
ORDER BY database_name, backup_type;

Query output:

As you can see, we do not have the transaction log available. Hence, we must rely on the full and differential backup. As per the process, we will restore a full backup with NORECOVERY option. The NORECOVERY option will allow us to apply differential and log backups. Here is the script to restore the full backups.

USE [master]
Go
RESTORE DATABASE [Stackoverflow2010] FROM  DISK = N'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_full_backup.bak' WITH  FILE = 1,  
MOVE N'TTIConfig' TO N'D:\MS_SQL\DATA\Stackoverflow2010.mdf',  MOVE N'TTIConfig_log' TO N'D:\MS_SQL\LOG\Stackoverflow2010_1.ldf',  NOUNLOAD,  STATS = 5
GO

Once the full backup is restored, we must restore the differential backup. As we do not have any transaction log backup, we will restore the differential backup using WITH RECOVERY option. The WITH RECOVERY option will complete the restoration process and bring database online. You cannot apply any differential or transaction log backups on it. Here is the script.

USE [master]
GO
RESTORE DATABASE [Stackoverflow2010] FROM  DISK = N'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_differential_backup.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO

Once the process is completed, check the status of the database.

select database_id, name, create_date, recovery_model_desc, state_desc from sys.databases where name='Stackoverflow2010'

Query Output

As you can see, the database is online. The bad news is that we could not restore the database till point of failure.

Challenges of Restoring Without Log Backups

Restoring without transaction log backups introduces multiple DBA-level challenges:

You cannot restore the database to a specific moment before the failure. You can only recover data till the last full or differential backup.
If your last backup is old, you may lose hours or even days of transactions / data. For example, you are taking weekly full and daily differential, then you can recover the data till the last successful differential backup.
If the database is damaged or the log file is missing, you cannot back up the tail of the log. It leads to permanent transaction loss.
If differential backups are missing or corrupted, you must use the last full backup. Restoring a full backup increases the downtime and major data loss.

Best Practices to Prevent This

Always use FULL recovery model for production databases and configure maintenance plans to generate the transaction log backups. If you are using SIMPLE recovery model, take frequent differential backup to minimize the data loss.
Always automate the backup process. You can use SQL Server maintenance plans, Windows task schedular, or SQL Server agent jobs.
Validate backups with RESTORE VERIFYONLY and periodic test restores. You can also configure the backup validation process in database maintenance plans. If you have additional server available, regularly test the restore process.
Store backup on separate and secure location. You can use cloud storage which is more reliable.
Use monitoring tools (SQL Agent jobs, custom scripts, or third-party solutions like Stellar, Redgate, or SQL Monitor) to detect missing backups. If you cannot invest money in monitoring tools, create custom scripts that send the alerts if the backup fails.

Conclusion

Restoring SQL Server databases without log backups is possible, but it comes at the cost of higher data loss, downtime, and broken SLAs. While the steps are straightforward (full → differential → recovery), the operational risks are significant. A solid backup strategy including frequent transaction log backups is the only way to ensure reliable point-in-time recovery and avoid these pitfalls in production environments. Now, if the database is corrupted and you do not have backups available, you can use professional tools like Stellar Data recovery software. It has many features that help to recover the SQL database. Along with database files, you can also repair the corrupted backup files.

How to Recover SQL Server Database After Backup Corruption

nisargupadhyay87 — Tue, 02 Sep 2025 10:11:13 +0000

Backups are the most important asset of any organization that uses software. The backups are essential to recover the critical business data that has been corrupted or deleted due to hardware failures, accidental deletions, or catastrophic events. However, sometimes backup files themselves become corrupted due to many reasons. It hinders the entire database restore strategy and might cause a lot of financial issues to any organization.
This article explains what backup corruption is, why it happens, and provides a step-by-step guide to recover SQL database after backup corruption. We will also learn the potential reason which we might encounter while restoring the backup.

Understanding Backup Corruption in SQL Server

In simple words, a corrupt backup means a corrupted *.bak file which we are unable to restore. The backup can be corrupted due to many reasons.

Hardware issues – The backup might be corrupted due to the hardware issues like the storage sub systems where the backups are being stored. The hardware can fail due to many factors which can lead to the database backup corruption.
Software problems – Sometimes the backup process get interrupted due to SQL Server instance crashed while backup is in process.
Incomplete backups – The backup can get corrupted when the backups process is not completed due to the network latency or insufficient space in backup storage.
Malware or viruses – The malwares or viruses can also corrupt the backup files. Sometimes due to weak security, the hackers might be able to access the backup files and use certain tools which corrupt the backups.

The backups are very important because in case of database corruption or failure, we will be able to recover the data from backups only. If the backups are corrupted and you do not have any alternate solution to recover the data, there will be an irreversible data loss.

Prerequisites for SQL Database Recovery

Before attempting to recover the database, we must check the reasons for database backup corruption, find the valid backup to begin the restoration process.

Identify the corruption source

We can use any of the methods to identify the corruption source.

The details of the failed or interrupted backups are available in windows event viewer or the SQL Server error logs. If you are using SQL Scripts to take the backup, you can incorporate the logging mechanism in script.
You can also schedule a SQL Server agent job or windows task scheduler job to verify the integrity of the backup file. You can check the integrity of the backup by using RESTORE HEADERONLY or RESTORE VERIFYONLY statement. Here is the syntax of both commands.

RESTORE HEADERONLY FROM DISK = N'location_of_backup_file';
GO

You can read more about RESTORE HEADERONLY command here and RESTORE VERIFYONLY here.

The next step is to prepare the environment to begin the database restoration.

Preparing for the environment

Once the backup files have been identified, we should start preparing the database server. Along with provisioning the database server, we should take care of following items:

The server on which you are restoring the database should have adequate space. Also, the storage which server is using must not have any disk-level corruption.
The correct version of SQL Server must be installed. Also, verify that the resources allocated to the server are adequate, so the restoration process is not interrupted due to lack of CPU and memory.
If the database is partially accessible, place the database in a safe state for recovery.

Here is the step-by-step guide to recover the database of backup corruption.

Step-by-Step Guide to Recover SQL Database After Backup Corruption

Before restoring the backup, we must validate whether the backup is valid or corrupt. For example, we want to restore the AdventureWorks2012 database. Before we begin the restoration process, verify the backup copy available with us is valid. We have full, differential and transaction log backups. To check that, execute following query.

DECLARE @BackupFile_FullBackup NVARCHAR(255) = 'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_full_backup.bak';
DECLARE @BackupFile_DiffBackup NVARCHAR(255) = 'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_differential_backup.bak';
DECLARE @BackupFile_LogBackup NVARCHAR(255) = 'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_log_backup.bak';

-- Step 1: Check the backup header (metadata about backup set)
RESTORE HEADERONLY FROM DISK = @BackupFile_FullBackup;
RESTORE HEADERONLY FROM DISK = @BackupFile_DiffBackup;
RESTORE HEADERONLY FROM DISK = @BackupFile_LogBackup;

-- Step 2: Check the backup file list (files included in the backup)
RESTORE FILELISTONLY FROM DISK = @BackupFile_FullBackup;
RESTORE FILELISTONLY FROM DISK = @BackupFile_DiffBackup;
RESTORE FILELISTONLY FROM DISK = @BackupFile_LogBackup;

-- Step 3: Verify that the backup is not corrupted and can be restored
RESTORE VERIFYONLY FROM DISK = @BackupFile_FullBackup;
RESTORE VERIFYONLY FROM DISK = @BackupFile_DiffBackup;
RESTORE VERIFYONLY FROM DISK = @BackupFile_LogBackup;

Once the backups are verified, we can start the restoration process. First, we must restore the full backup, then secondly, we must restore the differential backup and finally if the database is in full recovery model, we must restore all transaction log backups.

Here is the command to restore the full backup.

RESTORE DATABASE [Stackoverflow2010_Restore] FROM  DISK = N'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_full_backup.bak' WITH  FILE = 1,  MOVE N' Stackoverflow2010' TO N'D:\MS_SQL\DATA\Stackoverflow2010_Restore.mdf',  MOVE N' Stackoverflow2010_log' TO N'D:\MS_SQL\LOG\Stackoverflow2010_Restore_1.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5

Here is the command to restore the differential backup.

RESTORE DATABASE [Stackoverflow2010_Restore] FROM  DISK = N'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_differential_backup.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

Here is the command to restore the transaction log file.

RESTORE LOG [Stackoverflow2010_Restore] FROM  DISK = N'D:\MS_SQL\Backups\Stackoverflow2010\Stackoverflow2010_log_backup.trn' WITH RECOVERY  NOUNLOAD,  STATS = 10

Note that the transaction log file will be restored WITH RECOVERY option.
You can refer to this article which explains the restoration process in detail.

Addressing Common Errors During Recovery

While restoring the database, we might encounter certain errors. Here are the common errors and their root cause and potential fix. Note that these errors are generic. For detailed description of errors, refer to the Microsoft documentation.
Error: The backup set is corrupted.
Reason:

This error occurs when one or multiple backup files are corrupted.

Potential fix and Precautions

This is very critical error, and which directly indicates that backup set which we are trying to restore is not usable. Now, all we can do is to find a good working backup and restore it. In most cases, you will encounter a major data loss.
Always test your backups. Keep the backups on redundant storage. In simple words, keep the backup of backups.

Error: The log or differential backup is missing:
Reason: The error indicates that while restoring the sequence of backups, there is a differential or log backup is missing. The error indicates a recovery chain has been broken due to a full backup is taken between a full backup and differential backup. Read this article to learn more about it.
Potential fix and precautions:

This is very critical error and the only way to fix it to restore the last full backup and subsequent working differential and log backup.
If you want to take an ad hoc backup, always use the COPY_ONLY option. It will ensure that recovery chain does not get broken and restore completes without error.
Error: Database is in use
Reason: This error occurs when you are trying to overwrite the database with backup and the database which you are restoring is ONLINE and MULTIUSER state.
*Potential Fix: *
This is not a critical error. The issue can be resolved by changing the state of database from MULTI_USER to SINGLE_USER. Execute following command to do that.

ALTER DATABASE <Database_Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Once the database is in SINGLE_USER mode, you can rerun the database restoration process.

Leveraging Tools for Efficient SQL Database Recovery

When the manual recovery method that I have explained fails, you can use the third-party tools which can help to recover the data. I personally use Stellar Repair for MS SQL which is professional solution that can help to repair and restore the corrupt database. Here are some key benefits

It can repair MDF and LDF database files.
It can recover the data and the database objects like tables, procedures and triggers.
It can also help to restore the data from the corrupt backups.

Conclusion

Backup corruption is one of the most challenging obstacles in database recovery. To avoid data loss, we must always verify backup integrity and restorability. Always maintain a clear recovery plan, always test the existing backups, and always store backups in multiple secure locations. In cases where standard restore methods don’t work, Stellar Repair for MS SQL provides a powerful way to recover SQL database from corruption and ensure business continuity.

How to Patch SQL Server Safely and Handle Unexpected Database Issues Post-Patching?

nisargupadhyay87 — Tue, 12 Aug 2025 08:49:37 +0000

Microsoft regularly releases security packs, cumulative, and other updates for MS SQL Server to add new features, fix performance issues, and bugs in the server. Before SQL Server 2016, the company used to release service packs and cumulative updates regularly. However, it changed its servicing model with SQL Server 2017. It now releases only cumulate updates (CUs) and General Distribution Releases (GDRs) every 2 months.

Unpatched SQL servers are prone to SQL injection attacks, cyberattacks, compliance issues, and more. So, it is recommended to install service packs, cumulative packs, and general distribution releases as and when available. Installing such updates timely ensure a secure, stable, and high-performance SQL Server environment. In this article, we will discuss the stepwise process to patch the SQL Server safely. We will also discuss some common database issues that you may encounter after patching the server and explain how to handle them.

Stepwise Process to Patch the SQL Server Safely

Before patching or installing the updates, consider the following:

-Check the SQL Server version you are updating. To do this, you can run the below query:

SELECT @@VERSION version

Ensure that the version of SQL server that you are updating is compatible with the available service packs or cumulate updates.
Back up your SQL databases before installing the updates. This will help you restore the databases in case something goes wrong.
Running agent tasks can disrupt the patching process. So, temporarily disable the SQL agent tasks till the patching is complete.
Stop the SQL Server (MSSQLSERVER) service. To do this, open the SQL Server configuration Manager, click SQL Server Services, right-click on SQL Server (MSSQLSERVER), and click Stop.
Always perform the process in a test environment first.
You can now download and install the latest updates according to your SQL Server version. For this,
Go to the Microsoft page to know the latest updates and version history for SQL Server.
According to your SQL Server version, download the patches/updates. For example, download only the cumulative updates, if you’re using SQL Server 2022. Download both service packs and cumulative packs if you’re using SQL Server 2016 and earlier versions.
Read and accept the License terms.
Select the SQL Server instance you want to update. Confirm the installation path.
Check and confirm the installation options.
Start the installation process.
Wait till the installation process is complete. Then, restart the SQL Server (MSSQLSERVER) service.
Verify the update by executing the SELECT @@version query.

Common Issues You can Face After Patching the MS SQL Server

After updating the SQL Server, you may encounter various issues and errors, like error codes 3417 and 917. These errors are associated with database script update failure, corruption, and inconsistences in SQL database files.
After installing the updates if the upgrade script fails, you can check the error log for the related error messages and then troubleshoot the upgrade script failure. You can also use the trace flag 902 to start the MS SQL Server.
If you see the SQL Server error 3417 after patching the SQL Server, this indicates that there is corruption in the database files. In such a case, you can restore the master database from backup or try rebuilding the master database file. To rebuild the master database, you can use the following CMD command:
Note: Make sure to read the limitations and prerequisites of rebuilding the SQL master database.

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName ]

If the errors have occurred due to corruption in MDF files, then you can run the DBCC CHECKDB command to repair the files. Here’s the syntax:

DBCC CHECKDB    
    [ ( db_name | db_id | 0   
        [ , NOINDEX    
        | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]   
    ) ]   
    [ WITH    
        {   
            [ ALL_ERRORMSGS ]   
            [ , EXTENDED_LOGICAL_CHECKS ]    
            [ , NO_INFOMSGS ]   
            [ , TABLOCK ]   
            [ , ESTIMATEONLY ]   
            [ , { PHYSICAL_ONLY | DATA_PURITY } ]   
            [ , MAXDOP  = number_of_processors ]   
        }   
    ]   
]

Repairing the MDF files using the DBCC CHECKDB command may cause data loss. Alternatively, you can use a professional SQL repair tool, like Stellar Repair for MS SQL, to prevent data loss and quickly repair the SQL database. Its advanced algorithms can repair the MDF/NDF files easily and recover all the objects without any data loss. It can help resolve database corruption errors that may occur after updating the server. The tool supports all versions of SQL Server, including MS SQL Server 2022, MS SQL Server 2019, and MS SQL Server 2017.

Conclusion

You must always install the latest service packs or cumulative updates (when available) to protect the SQL Server from threats and malicious attacks. Above, we have discussed the stepwise procedure to patch the MS SQL Server. We have also mentioned some common issues that you may face after patching or installing the updates. However, if the database gets corrupted after patching the SQL Server, then you can restore the database from backup. If the backup isn't available, use Stellar Repair for MS SQL to repair the corrupted MDF/NDF files and recover all the data.

Data Types in SQL Server: In-depth Overview

nisargupadhyay87 — Fri, 02 Aug 2024 05:13:21 +0000

The data type in an SQL Server is an attribute that defines what type of data will be stored in a column of a table. When we create any table in a database, we must define columns with their respective data types. The SQL Server has various system data types that can store all data types, such as numbers, alphabets, special characters, etc.
The data types supported by SQL Server, can be categorized in the following categories.

Exact numeric data types like int, bigint.
Approximate numeric data types like real and float.
Date and Time data types like datetime, datetime2.
Character string data types like char, varchar.
Unicode character strings like nChar and nVarchar.
Binary string data types like varbinary.
Special data types like XML, SQL_Variant, rowversion, etc.

Note that you must choose the data type carefully because choosing the wrong one might adversely impact the application's performance. Also, keep the data type's length adequate so the application can store data properly. For example, if you want to store an employee's address in the address column, the data type length must be adequate; otherwise, the user might encounter errors.
This article will explain various data types, their range, usage, and simple examples. We will also see the data types that have been deprecated.

Exact Numeric Data Types

Exact numeric data types are used to store precise values. As the name suggests, exact numeric data types do not lose accuracy due to rounding and approximation. These data types can be used to store financial data like the cost of a product and sales amount, statistical data like the population of a country, demographic data like human age, and many more. SQL Server supports five types of exact numeric data types.

Example of exact numeric data types in SQL Server

Here is a simple example which illustrates what type of data can be stored in exact numeric data type

use BansalGroup_MainDB
go
CREATE TABLE ExactNumericDatatypes (
    ID INT PRIMARY KEY,
    BigIntColumn BIGINT,
    IntColumn INT,
    SmallIntColumn SMALLINT,
    TinyIntColumn TINYINT,
    DecimalColumn DECIMAL(10, 2),
    NumericColumn NUMERIC(8, 3)
);
INSERT INTO ExactNumericDatatypes (ID, BigIntColumn, IntColumn, SmallIntColumn, TinyIntColumn, DecimalColumn, NumericColumn)
VALUES (1, 123456789012345, 123456, 12345, 255, 123.45, 123.456);

Approximate Numeric Data Types

The approximate numeric data types are used to store the data that has floating values. In SQL Server, you can store the approximate numeric datatypes in float and REAL data types. Here are the characteristics of float and REAL data type.

Example of approximate numeric data types in SQL Server
Here is a simple example that illustrates what data type can be stored in approximate numeric data type.

CREATE TABLE ApproximateNumericExample (

    FloatDataType FLOAT,
    RealDataType REAL
); 
INSERT INTO ApproximateNumericExample (FloatDataType, RealDataType)
VALUES (123.456, 123.456);

Date and Time Data Types

The SQL Server has data types that are used to store the date and time. Here is a short description of each of them.
date
The data type is used to store only date. The data type has three part; month, year and day. The default format to store the value in data type is yyyy-MM-dd. The range of datatype is from 0001-01-01 to 9999-12-31.
time
In some applications, we might have to store the time values separately. In such use cases, can use time data type. The data type is used to store only time. By default, the time datatype has seven fraction precision. The range of datatype is from 00:00:00.0000000 to 23:59:59.9999999.
datetime
The datetime data type is used to store the date and time. The data type can store three millisecond fractions. By default, the datetime data type format is yyyy-MM-dd HH:mm:ss:fff. The range of datatype is from 1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997.
datetime2
The datetime2 data type was introduced in SQL Server 2008. It is an extension of the datetime2 data type for better precision.
Datetime2 has a larger date range, and the default precision is 7 digit which is higher than datetime data type. Also, you can specify the fraction precision. E.g. datetime(5).
There are some limitations of datetime2 data type

The basic mathematical operations with dates are not supported, like calculating the difference between two dates or adding days to the existing date value.
When we compare the datetime2 value with specific date values, SQL Server performs an implicit conversion which may impact performance. The range of datatype is from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999.

smalldatetime
As the name suggests, the smalldatetime data type is used to store date and time but the data type does not store seconds or a fraction of time. The range of datatype is from 0001-01-01 00:00 to 9999-12-31 23:59.
datetimeoffset
The datetimeoffset is an extension of datetime2 data type. The data type includes the time zone based on UTC / GMT. The range of datatype is from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 with offset (-14:00) to (14:00).

You can refer to the following table to understand the difference between all datetime data types supported by SQL Server.

Example of date and time data types in SQL Server:
Here is the simple example:

CREATE TABLE DateTimeDataTypes (

    DateTimeColumn DATETIME,
    SmallDateTimeColumn SMALLDATETIME,
    DateTime2Column DATETIME2,
    DateColumn DATE,
    TimeColumn TIME
);
INSERT INTO DateTimeDataTypes (DateTimeColumn, SmallDateTimeColumn, DateTime2Column, DateColumn, TimeColumn)
VALUES
(
'2024-03-18 12:30:00', -- Datetime
'2024-03-18 12:30:00', -- Small datetime
'2024-03-18 12:30:00.1234567', --Datetime2
'2024-03-18', -- Date
'12:30:00.1234567' --Time
);

Character Strings

The character string data types store the characters or strings in a database. In SQL Server, you can use char(n), varchar(n), varchar(max), and text data types to store the character strings. Here is a detailed description of all of them.

Example of character string data types in SQL Server
Here is a simple example

CREATE TABLE StringDataTypeExample (

    FixedCharColumn CHAR(10),
    VarCharColumn VARCHAR(255),
    TextColumn TEXT

);
INSERT INTO StringDataTypeExample
( FixedCharColumn, VarCharColumn, TextColumn)
VALUES ('Nisarg ', 'Nisarg Upadhyay', 'Nisarg Upadhyay is a scary DBA');

Unicode Character Strings

The Unicode character string data types are used to store the Unicode characters or strings in a database. In SQL Server, you can use nchar(n), nvarchar(n), and ntext data types to store the character strings. Here is the detailed description of all of them.

Here is the simple example:

CREATE TABLE StringDataTypeExample (

    NCharColumn NCHAR(10),
    NVarCharColumn NVARCHAR(255),
    NTextColumn NTEXT

);
INSERT INTO StringDataTypeExample
( NCharColumn, NVarCharColumn, NTextColumn)
VALUES ('निसर्ग ', 'निसर्ग उपाध्याय', 'निसर्ग उपाध्याय एक डरावना डीबीए है।');-- Hindi language Text

Note that, while inserting a Unicode string, you must specify N before the Unicode string.

Binary Strings

In SQL Server the binary data types are used to store the binary string. In SQL Server, there are three data types: binary, varbinary, and varbinary(max). The details of all of them are the following:

Here is a simple example. I am inserting “Nisarg Upadhyay” character string. To insert data, first we must convert the character string to varbinary. The code to create table and insert data is following:

CREATE TABLE BinaryDataTypeTable
(
    BinaryData VARBINARY(100)
);
INSERT INTO BinaryDataTypeTable (BinaryData)
VALUES (convert(varbinary,'Nisarg Upadhyay')); -- Convert to varbinary

To view data, run the SELECT query on BinaryDataTypeTable.

select BinaryData [Binary string], convert(varchar,BinaryData)[Original String] from BinaryDataTypeTable

Output

As you can see, the character string is converted into a a binary string.

Special and Miscellaneous Data Types

SQL Server also supports some special data types that are supported by SQL Server to handle specific data.
XML data type
The XML data type is used to store XML data. It can store XML documents up to 2GB in size. SQL Server has many XML functions that can be used to read, write, or update XML data. Here is a simple example that shows how to store data in XML data type.

CREATE TABLE XMLExample (
    ID INT PRIMARY KEY,
    XMLColumn XML
);
INSERT INTO XMLExample (ID, XMLColumn)
VALUES (1, '<bookstore>
  <book category="Database">
    <title lang="en">How to tune database</title>
    <author>Nisarg Upadhyay</author>
    <year>2005</year>
    <price>30.00</price>
  </book>
  <book category="IT Programming">
    <title lang="en">.Net Pro</title>
    <author>Pritesh Patel</author>
    <year>2005</year>
    <price>29.99</price>
  </book>
</bookstore>');

Geospatial and Spatial data types
SQL Server supports data types to store geospatial data like GEOMETRY and GEOGRAPHY.
Geometry
The GEOMETRY datatype represent data in flat (Euclidean) coordinates system. Here is a simple example. I have created a table named GeometryDataTypeExample which is used to store the geometry coordinates of the city. Following is the code to create table:

CREATE TABLE GeometryDataTypeExample (
    CityID INT PRIMARY KEY,
    CityName NVARCHAR(100),
    Coordinates GEOMETRY
);

Following is the code to insert geometry data.

INSERT INTO GeometryDataTypeExample (CityID, CityName, Coordinates)
VALUES
    (1, 'New York City', geometry::STGeomFromText('POINT(-74.0059 40.7128)', 4326)),
    (2, 'Los Angeles', geometry::STGeomFromText('POINT(-118.2437 34.0522)', 4326)),
    (3, 'London', geometry::STGeomFromText('POINT(-0.1276 51.5074)', 4326));

Geography
GEOGRAPHY datatype represents data in round-earth (ellipsoidal) co-ordination system like latitude and longitude of GPS system. The geography system is CLR data type in SQL Server. Here is a simple example. I have created a table named geographydatatypeexample which is used to store the geographical data of the city. Here is the code to create a table:

CREATE TABLE GeographyDataTypeExample (
    CityID INT PRIMARY KEY,
    CityName NVARCHAR(100),
    GeoCoordinates GEOGRAPHY
);

Following is the code to Insert data in table.

INSERT INTO GeographyDataTypeExample (CityID, CityName, GeoCoordinates)
VALUES
    (1, 'New York City', geography::STGeomFromText('POINT(-74.0059 40.7128)', 4326)),
    (2, 'Los Angeles', geography::STGeomFromText('POINT(-118.2437 34.0522)', 4326)),
    (3, 'London',  geography::STGeomFromText('POINT(-0.1276 51.5074)', 4326));

You can read this article to learn more about the GEOMETRY and GEOGRAPHY datatypes.
Rowversion (Timestamp)
The rowversion is a special datatype which is like a timestamp, but it is used internally to manage the concurrency of database. When any record is changed or updated, the SQL Server automatically generates a unique binary number which is used to track or detect a change in record.
Hierarchyid
The hierarchy datatype is a special datatype used to store and query the hierarchical data in SQL Server. The datatype is optimized for representing the tree-like structure like employee hierarchy in company or a family tree. You can read this article to learn more about the hierarchy datatype.
Rowversion (Timestamp)
The rowversion is a special data type that is like a timestamp, but it is used internally to manage the concurrency of database. When any record is changed or updated, the SQL Server automatically generates a unique binary number which is used to track or detect a change in record.
SQL_VARIANT
The SQL_VARIANT data type can store values of various SQL Server data types. This data type is useful when you want to store different types of data in a column. For example, you want to store int and binary values in the same column. Such requirements can be fulfilled by SQL_Variant data type. The maximum length of the SQL_Variant data type is 8016 bytes. You can read this article to learn more about the SQL_Variant data type. Here is a small example.

CREATE TABLE SQLVariantDataType(SQLVariantColumn sql_variant) 

INSERT INTO SQLVariantDataType values ( CAST(46279.1 as decimal(8,2)))
INSERT INTO SQLVariantDataType values(CAST('Nisarg Upadhyay' as varchar(5)))

As you can see in above example, The table SQLVariantDataType has a column named SQLVariantColumn with SQL_Variant data type. Now, I inserted two records in a table. The first record is decimal and second record is character string. Now, execute select statement to view the data.

SELECT  
             SQLVariantColumn,
             SQL_VARIANT_PROPERTY(SQLVariantColumn,'BaseType') AS 'Base Type', 
        SQL_VARIANT_PROPERTY(SQLVariantColumn,'Precision') AS 'Precision', 
        SQL_VARIANT_PROPERTY(SQLVariantColumn,'Scale') AS 'Scale' 

FROM      SQLVariantDataType

Output

As you can see, the column has decimal and varchar data stored in same column.

Deprecated Data Types

After every release of SQL Server, some old features, syntax and data types get deprecated. So far, SQL Server has deprecated three data types. The data types can be used but it won’t be supported in future versions of SQL Server. These data types will be replaced by other data types. Here is a list.

Timestamp is replaced by rowversion.
Text is replaced by Varchar(max).
NText is replaced by nvarchar(max).
Image is replaced by varbinary(max).

While designing a new application, avoid using the above data types. You should start changing the code with the new data type if you already use it in your application.

Conclusion

In any database management system, the data type selection is very important. When we design any database, we must select data types carefully to get optimum performance of the database and application.

In this article, I have explained about data types, their categories, and how to use them to create tables. I have used SQL Server Management Studio and dbForge Studio for SQL Server to write the script that is used to create table.

How to effectively use TRUNCATE TABLE in SQL Server

nisargupadhyay87 — Mon, 08 Apr 2024 16:55:03 +0000

Data manipulation in an SQL Server or any other database system includes various operations to manipulate existing data or add new data to a table. These operations contain various DML statements like INSERT, UPDATE, and DELETE. The INSERT statements are used to add new data to a table, the UPDATE statements are used to update the existing data in a table, and the DELETE statements are used to remove data from the table.
Along with the above statements, there are a couple of other statements, like TRUNCATE TABLE, that can be used to manipulate the data.

The TRUNCATE TABLE statement removes all records from any table or specific partition. The purpose of DELETE and TRUNCATE is the same, but both work differently. The DELETE statement is used to remove all records or specific records from the table, but the truncate table is used to remove everything from a table. I have explained the difference between both statements in a separate section of this article.
This article covers the basics of TRUNCATE TABLE with syntax and practical examples. Note that the article is based on the Microsoft SQL Server database. The syntax might be different for other database platforms.

Understanding TRUNCATE TABLE syntax

The syntax to truncate a table is following:

TRUNCATE TABLE [schema_name.table_name] WITH ( PARTITIONS ( partition_number))

In the syntax,

schema_name: Specify the schema name in which the table exists.
table_name: Specify the table name that you want to truncate.
partition_number: Specify the partition number from which you want to truncate the data.

Now, let us understand the syntax with various examples.

Step-by-Step guide to using TRUNCATE TABLE

But before that, we must create a same database that we can use to test our scenarios. I have created a database named BansalGroup_MainDB. The database is used to store the details of the employees and client of a company named Bansal Group. The database has three tables named BansalGroupClients, BansalGroupEmployees and BansalGroupProjects. The BansalGroupClients table holds the data of the clients, BansalGroupEmployees table holds the data of employees and BansalGroupProjects table holds the data of the projects that are being handled by the company.
The BansalGroupProjects table has a foreign key on client_id column which references the BansalGroupClients table.
Here is the T-SQL code to create the tables.

CREATE TABLE BansalGroupProjects ( project_id INT PRIMARY KEY, project_name VARCHAR(100), client_id INT, start_date DATE, completion_date DATE, budget DECIMAL(10, 2), FOREIGN KEY (client_id) REFERENCES Clients(client_id) ); CREATE TABLE [HR].BansalGroupEmployees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(100), job_title VARCHAR(100), email VARCHAR(100), phone VARCHAR(20) ); CREATE TABLE BansalGroupProjects ( project_id INT PRIMARY KEY, project_name VARCHAR(100), client_id INT, start_date DATE, completion_date DATE, budget DECIMAL(10, 2), FOREIGN KEY (client_id) REFERENCES Clients(client_id) );
Once tables are created, run following queries to insert dummy data in all tables.

INSERT INTO BansalGroupClients (client_id, client_name, contact_person, contact_email, contact_phone) VALUES (1, 'ABC Corporation', 'John Smith', 'john@example.com', '123-456-7890'), (2, 'XYZ Corp', 'Jane Doe', 'jane@example.com', '987-654-3210'), (3, 'LMN Ltd', 'Sam Brown', 'sam@example.com', '555-555-5555'); INSERT INTO [HR].BansalGroupEmployees (employee_id, employee_name, job_title, email, phone) VALUES (1, 'Alice Johnson', 'Architect', 'alice@example.com', '111-222-3333'), (2, 'Bob Williams', 'Project Manager', 'bob@example.com', '444-555-6666'), (3, 'Charlie Brown', 'Draftsman', 'charlie@example.com', '777-888-9999'); INSERT INTO BansalGroupProjects (project_id, project_name, client_id, start_date, completion_date, budget) VALUES (101, 'Office Building', 1, '2023-01-01', '2023-06-30', 500000.00), (102, 'Residential House', 2, '2023-02-15', '2023-09-30', 700000.00), (103, 'Shopping Complex', 3, '2023-03-20', '2024-01-31', 1200000.00);

Now, we are ready to test the various use cases but before that, let us understand the difference between the TRUNCATE Table and other data deletion methods.

TRUNCATE TABLE vs. other SQL Data Deletion methods

The TRUNCATE TABLE and DELETE statements are used to remove data from the table. But both methods work differently and there are several differences between them. Note that, this is one of the popular interview questions. The following are the differences:

Drop table statement:

•DROP TABLE statement deletes a table from database.
•If the table is not locked by any transaction, the drop table statement executes immediately.
•DROP TABLE statement is a DDL statement and they are auto-committed so if you drop any table, it cannot be rolled back.

Truncate Table Statement

•The Truncate table statement deletes all records from a table.
•The TRUNCATE TABLE statement works faster because when we execute TRUNCATE TABLE statement, the operation is not logged in transaction logs which reduced the additional IO which occurs while writing the transaction on log files.
•The TRUNCATE TABLE statement resets the identity.
•The TRUNCATE TABLE cannot be rolled back, hence before executing the statement, make sure you have required backups.
•The TRUNCATE TABLE do not fire a trigger created on a table.

DELETE Statement

•The DELETE statement deletes all records or specific records. You can specify the condition in WHERE clause. So, the records that are matches with the condition will be deleted.
•The DELETE statement is slower, specially when you are removing a lot of records from a table because the DELETE is a logged operations so the changes will be written to transaction log file which adds additional IO.
•The DELETE statement does not reset the identity.
•If DELETE statement is executed in a transaction, and if the changes are not committed, the transaction can be rolled back.
•The DELETE statement executes a trigger created on a table from which you are deleting the data.

Now, let us take a look at some additional recommendation and limitation of using TRUNCATE TABLE.

Special considerations and limitations of TRUNCATE TABLE

The Truncate table statement cannot be executed on:

A table which being referenced by a foreign key. I have explained that in above section.
A table which being used in indexed view.
A table which is being used in merge or transactional replication.
A system-versioned temporal tables.

Before executing a TRUNCATE TABLE statement on any table, you must understand following implications.

As I mentioned in earlier sections of the article, the TRUNCATE TABLE is a minimally logged operation. Hence the changes made in data are not logged in transaction log file. If you truncate a table or partition, the operation could not be rolled back. You must restore entire database.
If the truncate table statement is in a transaction, and you try to rollback the transaction, the DML statements will be rolled back automatically but the truncate table won’t be rolled back.
Always test the procedure or the application code that has TRUNCATE TABLE statement on development environment before executing them on production environment.
Make sure you have well-defined and well-tested backups in your database system. Backups must be validated and tested on regular intervals.

Practical examples in SQL Server Database

Now, let us take a look at some examples. I have created BansalGroup_Maindb on the demo environment. If you are working in a production environment, to keep your data safe, make a backup of the database from which you want to truncate data.

Example 1: Simple Truncate table statement

First, let us truncate BansalGroupEmployees table. To do that, execute following T-SQL query.
use BansalGroup_MainDB go truncate table [BansalGroupEmployees]

Once query executed, run SELECT query to view data.
use BansalGroup_MainDB go select * from [BansalGroupEmployees]

Query output

As you can see, the data has been deleted from the BansalGroupEmployees table. In next example, we will truncate table of specific schema.

Example 2: Truncate table from specific schema

In this example, we will truncate BansalGroupClients table. The table is created in Clients schema, so the command to truncate the BansalGroupClients table would be:

use BansalGroup_MainDB go truncate table [clients].[BansalGroupClients]
Once query executed, run SELECT query to view data.
use BansalGroup_MainDB go select * from [clients].[BansalGroupClients]
Query screenshot

As you can see, the data has been deleted from the BansalGroupClients table. In next example, we will truncate table that is referenced by foreign key.

Example 3: Truncate table that is referenced by foreign key

This example shows how to run truncate table statement on a table that is referenced by foreign key. In this example, we will truncate table named BansalGroupClients table. The client_id column of the table is being referenced by client_id column of BansalGroupProjects table. Let us execute truncate table statement.
use BansalGroup_MainDB go truncate table [clients].[BansalGroupClients]
Query output

As you can see, the truncate table statement is failed because it is being referenced by another table. To rectify this issue, first we must drop the foreign key on BansalGroupProjects table. Execute following query to drop the foreign key constraint.

alter table [dbo].[BansalGroupProjects] drop constraint [FK__BansalGro__clien__4CA06362]
Once constraint is dropped, re-run the TRUNCATE TABLE statement.
use BansalGroup_MainDB go truncate table [clients].[BansalGroupClients]

The query executed successfully. To verify, run SELECT statement on BansalGroupClients table.

use BansalGroup_MainDB go select * from [clients].[BansalGroupClients]

Query output

As you can see, the table has been truncated.

Partitions and permissions

Working with partitions

The TRUNCATE TABLE statement can be used to truncate data from a specific partition. I explained the syntax in an earlier section. For demonstration, I created three partitions on the BansalGroupProjects table. You can read the article to learn more about SQL Server table partitioning.
You can view the details of partitions created on a table by running the following query:

SELECT [tables].name AS [Table Name], [partitions].partition_number AS [Partition Number], [Filegroups].name AS [FileGroup Name], [partitions].rows AS [Records in partition] FROM sys.tables AS [tables] INNER JOIN sys.indexes AS [indexes] ON [tables].object_id = [indexes].object_id INNER JOIN sys.partitions AS [partitions] ON indexes.object_id = [partitions].object_id AND indexes.index_id = [partitions].index_id INNER JOIN sys.allocation_units AS [allocationUnits] ON [partitions].partition_id = [allocationUnits].container_id INNER JOIN sys.filegroups AS [Filegroups] ON [allocationUnits].data_space_id = [Filegroups].data_space_id WHERE [tables].name = 'BansalGroupProjects' ORDER BY [tables].name, [indexes].name, [partitions].partition_number;
Query output

As you can see, there are four partitions in a table. Let us truncate the data from partition number 3. To do that, execute the following SQL query.
use BansalGroup_MainDB go TRUNCATE TABLE BansalGroupProjects WITH (PARTITIONS (3));
Once truncate table completes successfully, execute below query to verify the changes.

The highlighted record in the above image shows that the partition's data has been truncated.

Permissions

You must assign an ALTER permission on the table to the user or sql login. If the user or sql login is already a member of the sysadmin, db_owner or db_ddladmin fixed role, it will automatically get the TRUNCATE TABLE permission on all tables created in a database.

Conclusion

This article taught us about the TRUNCATE TABLE statement in SQL Server and how to use it effectively in SQL Server. The TRUNCATE TABLE can be run using SQL Server Management Studio, SQLCMD utility, or [dbForge Studio for SQL Server] tools.

I have explained the data manipulation operations in any database system. We understand the syntax of the TRUNCATE TABLE statement and various examples. I have covered the difference between a TRUNCATE TABLE query and a DELETE query, which is the most common interview question. Also, we understand when we should use the truncate table and what needs to be taken care of before using it.

Creating a view in SQL Server

nisargupadhyay87 — Thu, 08 Feb 2024 15:21:01 +0000

In this article, we are going to learn about SQL Server views. I am covering the following topics in the article

Basics of view and its usage.
How to create, update, and drop the view using T-SQL query.
How to create the view using SQL Server Management Studio.

For a better explanation, I have created a sample database that contains the data of a hospital. The database contains four tables named [Appointments],[Patients],[MedicalHistory], and [Doctors]
Here is the code to create the tables.

use HospitalManagementSystem
go
CREATE TABLE  Appointments (
    AppointmentID   int  NOT NULL,
    PatientID   int  NULL,
    DoctorID   int  NULL,
    AppointmentDate   datetime  NULL,
    Notes   varchar (max) NULL
)
CREATE TABLE Patients (
     PatientID   int  NOT NULL,
     FirstName   varchar (50) NULL,
     LastName   varchar (50) NULL,
     DateOfBirth   date  NULL,
     Gender   char (1) NULL,
     PhoneNumber   varchar (15) NULL,
     Email   varchar (100) NULL,
     Address   varchar (255) NULL
)
GO
CREATE TABLE MedicalHistory (
     MedicalHistoryID   int  NOT NULL,
     PatientID   int  NULL,
     Diagnosis   varchar (255) NULL,
     Prescription   varchar (max) NULL,
     DateRecorded   datetime  NULL
)
GO
CREATE TABLE Doctors (
     DoctorID   int  NOT NULL,
     FirstName   varchar (50) NULL,
     LastName   varchar (50) NULL,
     Specialization   varchar (50) NULL,
     PhoneNumber   varchar (15) NULL,
     Email   varchar (100) NULL,
     Address   varchar (255) NULL)

I have inserted dummy records in the tables using dbForge Studio for SQL Server which has a feature called Data generator. It helps to create a real-time data set that helps to run various test cases for the application.
Now, let us understand the basics of SQL Server views.

Understanding the Basics of Views

A view is a virtual table that contains the output of the SELECT query. The views are stored as a database object and like a table, it contains the columns. Note that the view contains only those columns that are returned by a query which is used to create a view. For example, if the query which is used to create a view, returns five columns then a view can contain only those five columns.

Unlike tables, the views do not store actual data in a database. Whenever we execute SELECT statement on view, it executes the T-SQL query which is used to create the view and returns the data from underlying tables.

The SELECT query that is used to define a view can have various string and numeric functions. Also, the query has data from one or more than one table, which can exist on the same database or different databases.

The views can be considered as an added security layer because when a user executes a query against a view, it retrieves the specific subset of the data. Moreover, the underlying structure of a table remains hidden. For example, if your database contains the details of the credit card of a customer and you do not want anyone to access that column, you can create a view on top of the base table and grant permissions to access that view only.
There are mainly five types of views.

Simple views: The simple views contain a simple SELECT statement which populates data from one table. As simple view references to one table, we can perform INSERT, UPDATE , and DELETE operations.
Complex Views: The complex views contain a SELECT statement that joins multiple tables and may contain aggregate functions, string manipulation functions and other complex business logic. The complex views cannot be updated.
Updatable Views: The updatable views allows us to perform INSERT, UPDATE , and DELETE operations. When we perform any DML operation on UPDATABLE view, the chages will be applied on the underlying tables. Note that, the updatable view cannot be complex, meaning it cannot contain an aggregate function or group by clause. It must not contain a DISTINCT keyword. The view must reference one table and if the view references multiple tables, then all tables must be joined using INNER JOIN only.
Indexed views: The indexed views are materialized views. As the name suggests, we can add a unique index on it. The index views have performance benefits when we are using a query that has aggregate functions on various columns. However, if the data of the underlying tables of the materialized views is frequently updated, it is not a good idea to use an indexed view. You can read Create indexed views article to learn more about the indexed views.
Partitioned views: The partitioned views are like SQL Server table partitions but unlike table partitions, we do not need to create various partition functions or schema. However, the partition views are not as flexible as table partitions but if configured properly, they give good results in terms of performance, data manageability, and archival process. You can read SQL Server Partitioned Views article to learn more about partitioned views.
System Views: The system views are used to show the SQL Server metadata. When we query a system catalog view, it provides the details of the metadata information of the SQL Server database and its configurations. The simple example is sys.tables view which shows the details of the tables created in a database. You can read System Views article to learn more about the system catalog views.

Now, let us understand how to create and manage SQL Server views.

Creating a View in SQL Server

The syntax to create a view is following:

create view [view_name]
as
sql_query..

In the syntax,

view_name: Specify the name of the view.
sql_query: Specify the SELECT query to fetch the data from one or multiple tables.

For example, I want to create a view that populates the details of the patients admitted to a hospital. The details of registered patients are stored on a [Patient] table. Hence, we are going to execute the SELECT statement on the [Patient] table.
Here is the query to create a view.

create view vw_patientDetails
as
select patientID, firstname, lastname, dateofbirth,gender, phonenumber,email,address from [Patients]
where  (FirstName is not null and lastname is not null)
and  (FirstName <>'' and lastname <>'')

When you execute the above query, SQL Server will create a view named [vw_patientDetails]. Now, to fetch the data from the view, you can execute a select statement against the view.
Here is the query to view the data of a patient from view.

Select * from vw_patientDetails

Here is the output.

As I mentioned in an earlier section, you can query a view just like the way you run a query on a SQL Server physical table. You can apply aggregate and string manipulation and other SQL Server built-in functions. Now, let us understand how we can use a query with joins to create a view.

Creating a View with Multiple Tables

This section explains how we can use a query that joins multiple tables in a view. The syntax is same as creating view

create view [view_name]
as
sql_query..

In the syntax,

view_name: Specify the name of the view.
sql_query: Specify the SELECT query with Joins to fetch the data from multiple tables.

For example, we want to create a view that shows the list of patients and the name of the doctor who is treating them. For that, we are joining [Patients] and [MedicalHistory] tables.

The join query is the following:

USE HospitalManagementSystem
go
SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,mh.Diagnosis,mh.Prescription from [Patients] p INNER join
[MedicalHistory] mh ON p.PatientID=mh.PatientID
WHERE
  (FirstName is not null and lastname is not null)
and  (FirstName <>'' and lastname <>'')

Now, to create a view named vw_patients_dignosis, execute the following query.

create view vw_patients_dignosis
as
SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,mh.Diagnosis,mh.Prescription from [Patients] p INNER join
[MedicalHistory] mh ON p.PatientID=mh.PatientID
WHERE
  (FirstName is not null and lastname is not null)
and  (FirstName <>'' and lastname <>'')

Once a view is created, run the following query to get the data.

USE HospitalManagementSystem
go
select * from vw_patients_dignosis

Query output

Now, let us understand how to update a view.

Updating a View in SQL Server

If you want to modify the SELECT statement in a view meaning altering the view, we can use ALTER VIEW statement. The syntax is following:

ALTER VIEW [your_view_name]
as
updated_SQL_Query

In the syntax

your_view_name: Specify the name of the view that you want to modify.
updated_SQL_Query: Specify the modified T-SQL query.

For example, we want to alter the view named vw_patients_dignosis. The view shows the list of patients whose diagnosis is null. To do that, we will add another condition in the WHERE clause of the query:
Here is the updated SQL query.

SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,mh.Diagnosis,mh.Prescription from [Patients] p INNER join  
[MedicalHistory] mh ON p.PatientID=mh.PatientID 
WHERE  
  (FirstName is not null and lastname is not null) 
and  (FirstName <>'' and lastname <>'') 
and (Diagnosis is not null and Diagnosis<>'')

Execute the code below to ALTER the view.

ALTER view vw_patients_dignosis 
as 
SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,mh.Diagnosis,mh.Prescription from [Patients] p INNER join  
[MedicalHistory] mh ON p.PatientID=mh.PatientID 
WHERE  
  (FirstName is not null and lastname is not null) 
and  (FirstName <>'' and lastname <>'') 
and (Diagnosis is not null and Diagnosis<>'')

Verify the changes by running the SELECT statement against the vw_patients_dignosis view.

use HospitalManagementSystem
go
select * from vw_patients_dignosis

Query output

As you can see the query has returned a list of patients whose diagnosis is not null which shows that the view has been updated.

Creating a View with Aggregate Functions

This section shows how to create a view using the aggregate function. We can also use a query that has the aggregate functions to create a view. The syntax is the same that we used to create a regular view.

For example, we want to create a view that contains a list of patients and the total number of prescribed medications. For that, we will use count() and group BY() functions. You can read Aggregate Functions (Transact-SQL) - SQL Server article to learn more about the aggregate functions and their usage in SQL Server.
The query to view a list of patients with medication is following:

SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,Sum(mh.Prescription)TotalMedications from [Patients] p INNER join  
[MedicalHistory] mh ON p.PatientID=mh.PatientID 
WHERE  
  (FirstName is not null and lastname is not null) 
and  (FirstName <>'' and lastname <>'') 
and (Diagnosis is not null and Diagnosis<>'')
group by p.FirstName ,p.LastName,p.Gender,p.PhoneNumber

You can encapsulate this query in vw_patient_medication by running the following statement.

create view vw_patient_medication  
as 
SELECT p.FirstName ,p.LastName,p.Gender,p.PhoneNumber,count(mh.Prescription)TotalMedications from [Patients] p INNER join  
[MedicalHistory] mh ON p.PatientID=mh.PatientID 
WHERE  
  (FirstName is not null and lastname is not null) 
and  (FirstName <>'' and lastname <>'') 
and (Diagnosis is not null and Diagnosis<>'')
group by p.FirstName ,p.LastName,p.Gender,p.PhoneNumber

Run the following query to view the data.

use HospitalManagementSystem
go
select * from vw_patient_medication order by TotalMedications desc

Query output:

Now, let us see, how we can create a view using SQL Server Management Studio.

Creating a View Using SSMS

We can also create a view using SQL Server Management Studio. For example, we want to create view that shows list of patients with their appointment dates with the respective doctors. We will use patient, doctors, and appointment tables.

To create a view, connect to SQL Server instance. → Expand HospitalManagementSystem database → Right-click on Views → Select New View.

A query designer opens which shows the list of tables created in the database. Select the desired tables and click Add.

In a query designer, you can select the desired columns. As shown in below screenshot, the query designer automatically creates the query based on the selected columns.

You can also make changes in auto-generated queries. Once the desired query is ready, click on the Save button which is in the menu bar. The SQL Server management studio prompts to provide the name of the view. Specify vw_patient_appointment as a name of the view.

Once the view is created, Execute following T-SQL query to verify the data.

select * from vw_patient_appointments

Query output

Dropping a View in SQL Server

We can use the DROP VIEW statement to drop any view. When we drop a view from the database, make sure that the same view is not referenced by any other stored procedure or function. If it is being referenced by any other database object, the application code will break. Note that, the ALTER or CONTROL permission is needed on the view that you are dropping. The T-SQL query is following:

Drop view [view_name]

In the above syntax view_name represents the name of the view that you want to drop. If the view exists in a specific schema, you must specify the schema name. The naming will be [Schema_name].[view_name].

Suppose, we want to delete a view named vw_patient_medication from the database. The view exists on the dbo schema, hence we do not need to specify the schema name in DROP VIEW statement. To drop the view, execute following T-SQL query:

drop view vw_patient_medication

Conclusion

In this article, we learned about the basics of SQL Server views and different types of views. The article covers following topics with syntax and various examples.

Basics of view and its usage.
How to create, update, and drop the view using T-SQL query.
How to create the view using SQL Server Management Studio.

Creating a SQL Server view using SQL Server management studio is fairly simple for the developers who has limited knowledge. But if you are a seasoned developer and creating a view that has very complex SQL Queries, you might consider using the dbForge Studio for SQL Server to create views. The dbForge Studio has lot of features that helps programmers to develop the database code very easily. It has a powerful feature named auto-complete. It helps to write complex SQL queries that are used to create views.

Defragment Your Indexes for Better Database Performance

nisargupadhyay87 — Mon, 28 Nov 2022 18:57:58 +0000

Index fragmentation occurs when the physical order of the index pages does not match the logical ordering of the index. When we insert, update or delete any data from the SQL Server table, the SQL Server automatically updates the table's indexes.

For example, when inserting records in a table, a page split might occur to accommodate the data in 8K pages of index, and eventually, the data becomes scattered. Suppose we have a table with 1500 pages that are 100% full. But due to various delete and update operations, now the table has 2000 pages, which are 60% full.

Due to that, when we access data, instead of reading data from 1500 pages, the SQL has to read 2000 pages. Also, the pages are scattered, so SQL Server has to perform several random IO operations. Note that the performance of the storage subsystem is always better when a sequential IO is performed in the data retrieval process. Due to index fragmentation, random I/O is performed, which adds overhead to storage.

The index fragmentation can be fixed by using the following methods

Index Reorganize
Index Rebuild

What is Index Rebuild

The index rebuild operation drops and recreates an index. The index rebuild blocks access to the table whose index we are rebuilding; therefore, the table will be inaccessible, and the performance will be degraded. We can rebuild the index without blocking access using the ONLINE = ON option, which is only supported in the SQL Server enterprise edition. Index rebuild creates new statistics, and the changes will be recorded in the Transaction Log file; therefore, you must keep track of the transaction log’s growth while rebuilding a large index.

To gain the optimum performance, the index should be rebuilt when the index fragmentation is higher than 30% to gain optimum performance.

What is index reorganize

The index reorganize is a lightweight index maintenance operation that reorders the physical pages according to the logical ordering of the indexes. The reorganize operation does not block access to the table. The index reorganize operation never drops the index, so the statistics will not be updated.

To gain the optimum performance, the index reorganize should be performed when the index fragmentation is between 10% and 30%.

We can use the following methods to defrag the indexes of the SQL Server.

ALTER INDEX T-SQL statement.
Using SQL Server management studio.

Devart Index Manager

In this article, we will learn how to reduce the fragmentation of indexes using the Devart SQL Index manager. The Devart index manager is a part of the Devart dbForge SQL tool professional. The Devart Index manager provides the following benefits
It is a central place where you can see a database list of all fragmented indexes. You can connect to the different database servers using the connection manager and populate the list of fragmented indexes from all databases.
You can rebuild all or specific indexes using a user-friendly interface and easy to use. Complex scripting is not required.
You can generate a CSV report of fragmented indexes and use it for review.
You can generate a SQL script to rebuild or reorganize fragmented indexes. You can use those scripts to automate index maintenance.
It is a SQL Server management studio plugin, so you do not have to maintain the additional software.

How to use SQL Index Manager

Open SQL Server Management Studio → Connect to SQL Server database engine → Expand Databases *→ Right-click on **WideWorldImportors *→ Hover **Index Manager → Select Manage Index Fragmentation.

The Devart Index Manager opens in a new SQL Server Management Studio tab. It is divided into three ribbons. The first ribbon contains the following configuration parameters.

Search Textbox: If the database has a lot of indexes, you can enter the keyword of the tables or index name. The tool will populate the index's name containing the specified keyword.
Reanalyze: This button scans the entire database and populates the list of all indexes.
Options: Here, you can configure the following index rebuild options.

Reorganize threshold: Specify the index reorganize threshold. The index will be reorganized if the fragmentation is higher than the specified value.
Rebuild threshold: Specify the index rebuild threshold. The index will be rebuilt if the fragmentation exceeds the specified value.
Sort in tempdb: If you enable the Sort in tempdb option, the index rebuild operation will be performed in SQL Server temporary database (TempDB)
Online: When enabling this option, the index rebuild operation will be performed without locking the table. This feature is only supported in the SQL Server enterprise edition.

Database Name: The list of databases created in a server.
Server Name: The list of SQL Server instance names.

The third section is a grid view which contains the following details:

Index Name: The list of fragmented Indexes above the specified threshold.
Fix Type: The fix type is an action suggested by the Devart Index Manager. The fix type is Index Reorganize or Index Rebuild. The fix type is also determined based on the threshold specified in the option.
Owner Object: The list of tables and schema whose indexes are fragmented. A table contains multiple indexes, but you can only view those with fragmentation higher than the specified threshold.
Index Type: Type of Index. It can be clustered index or a non-clustered index.
Index Size (Pages): Size of fragmented indexes. The size is 8kb pages.
Index Size (MB): Size of fragmented indexes. The size is in MB.
Fragmentation: Percentage fragmentation in listed indexes.
Partition number: Partition number of the table.
Rows in Partition: Total records in a partition.
Reason: Reason for index maintenance (Index rebuild to index reorganize).

The following screenshot shows the list of fragmented indexes in the WideWorldImportors database.

In this demo, we want to reorganize the indexes of the WideworldImportors database when fragmentation is higher than 10%, rebuild the indexes when fragmentation is higher than 25%, and the maximum index size must be higher than 1000 Pages.
To do that, we will change the index rebuild, reorganize the threshold, and specify the minimum index size.

To do that, Click on the Options button.

A dialog box opens. Enter 10% in Reorganize threshold, 25% in the Rebuild threshold, and 1000 in the Minimum index size textbox. Enable the Sort in TempDB option. Click OK to save the changes.

Now, we will rescan all tables and populate the list of the fragmented index. Click on Analyze.

The Devart index manager will populate the list of fragmented indexes which matches the above criteria. Following is the list in a grid view.

Before we rebuild the indexes, you can see three options in a ribbon that will be enabled.

Fix: When we click on Fix, the Index Manager will automatically rebuild/reorganize the index.
Script Changes: When we click on Script Changes, the index manager will generate an ALTER INDEX REBUILD script. This option gives us the flexibility to change the index rebuild parameters.

See the following screenshot:

We will script the changes in this demo, so I selected all indexes and clicked the Script Changes button. See the following image:

A SQL Script to rebuild the indexes has been created.

We can execute the script immediately, or we can create a SQL job to execute it during off-business hours.

Summary

This article explains the SQL Server indexes, how they get fragmented, and how to defrag them using the Devart dbForge Index manager.

Different ways to export SQL Database using Devart Data Pump

nisargupadhyay87 — Thu, 23 Jun 2022 08:09:22 +0000

The Devart Data Pump is a SQL Server Management Studio add-in that helps us migrate data between SQL Server databases to various data formats. The Devart Data Pump supports import and export of Microsoft Excel, Microsoft Access, CSV, Text, JSON, PDF, HTML, ODBC, etc. The Devart Data Pump is highly customizable, so the data export process becomes much easier.

In this article, we will learn the step-by-step process of exporting data from the Azure SQL database to an on-premises SQL database server with the help of efficient SQL tools. We are using the ODBC data source to connect to the on-prem server.

Environment Setup

I have configured Azure SQL Server and configured a sample database named AdventureWorksLT database. The details of the Azure SQL Server details are as follows:

We will export the records of the customer table, which is created in the AdventureWorksLT database.
I have installed SQL Server 2019 on my workstation and created a database named DemoDatabase. I have scripted out the table definition of the customer table from the AdventureWorksLT database and re-created it on DemoDatabase.
The script to create the table is as follows:

CREATE TABLE  [Customer]
(
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [NameStyle] BIT NOT NULL,
    [Title] [nvarchar](8) NULL,
    [FirstName] VARCHAR(100) NOT NULL,
    [MiddleName] VARCHAR(100) NULL,
    [LastName] VARCHAR(100) NOT NULL,
    [Suffix] [nvarchar](10) NULL,
    [CompanyName] [nvarchar](128) NULL,
    [SalesPerson] [nvarchar](256) NULL,
    [EmailAddress] [nvarchar](50) NULL,
    [Phone] NVARCHAR(25) NULL,
    [PasswordHash] [varchar](128) NOT NULL,
    [PasswordSalt] [varchar](10) NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL) ON [PRIMARY]
GO

Now, to copy data to DemoDatabase, we must first create an ODBC data source.

Configure ODBC Driver for SQL Server.

First, let us configure an ODBC data source. Open ODBC Data Sources 🡪 Click Add on User DSN tab.

Create New Data Source on screen select SQL Server Native Client 11.0 🡪 Click Finish

The Create ODBC driver for SQL Server configuration wizard opens. On the first screen, specify the desired ODBC driver name. Specify the server hostname on which SQL Server is installed in the Server Name box and click Next.

On the next screen, specify the authentication method used to connect to the SQL Server. We are using Windows Authentication.

On the next screen, we can configure the following parameters.
Set the default database. In our demo, I set the default database to DemoDatabase.

Specify the SPN for mirroring server
Enable ANSI quoted identifier, ANSI nulls, paddings, and warnings.
Application Intent. It can be READWRITE or READONLY.
Enable multi-subnet failover.

In our demo, I have not made any changes. Click Next.

On the wizard's next screen, you can specify the following options.

Change the SQL Server system message language.
Enable strong encryption
Enable the regional settings for currency, number, date, and time.
Specify the location of the log of long-running queries and query timeout
Specify the ODBC driver statistics log file.
In our demo, I have not made any changes. Click Finish.

A popup will be displayed. In the popup, you can see all the configuration parameters and the value set by us.

Click OK to create the data source.

Once the data source has been created successfully, you can view it in the User DSN tab of the ODBC Data Source Administrator.

Now, let us configure the Devart Data Pump to export data from Azure SQL Database to On-prem SQL Server.

Configure Devart Data Pump

The Devart Data Pump SQL tool is an efficient SQL server import and export wizard. It can be launched from SQL Server Management Studio. I am exporting data from AdventureWorksLT (Azure SQL Database) to DemoDatabase (on-prem SQL Server database). Open SQL Server Management Studio 🡪 Connect to Azure SQL Server instance 🡪 Expand Databases 🡪 Right-click the AdventureWorksLT database 🡪 Hover on Data Pump 🡪 Select Export Data.

The Data Export wizard starts. On the first screen, you can see the various export formats supported by the Devart Data Pump. I am exporting the data in ODBC format, so I have selected ODBC.

We need to specify the source database and schema on the Source screen. We are exporting data from the customer table created in the AdventureWorksLT database. Select AdventureWorksLT from the Databases drop-down box and select dbo from the Schema drop-down box. The list of tables created in the dbo schema will be loaded in the grid view. We are exporting data from the customer table; therefore, select it from the grid and click Next.

We must set the specification of the ODBC data providers from the Options screen. In the data source specification section, we must specify the ODBC connection string or select the pre-configured ODBC system or user data source. We have already configured an ODBC data source named AdventureWorksDB, so select it from the Use system to the user data source name drop-down box. Click Next.

You can select the table where you want to export the data from the table. In our demo, we have created a customer table in DemoDatabase; therefore, choose demodatabase.dbo.customer from the list.

Suppose you want to export data from specific columns of a table. To do that, you can select those columns from the Data Format screen. This option is useful when exporting data from specific columns to Excel or CSV files. In this demo, we are exporting all columns. Click Next.

We can export all rows of tables or specific rows of tables from the Exported rows screen. In this demo, I am exporting all records from the customer table; therefore, I have selected the Export all rows option.

We can configure the error handling process and behavior on the Error Handling screen. In this demo, I am not changing any parameters. Click Export to begin the data export between the Azure SQL database to the In-Prem SQL Server database.

The data export process begins.

The data has been exported successfully.

Run the SELECT statement to verify that data has been copied successfully.
SQL Query

USE demodatabase
GO
SELECT c.CustomerID,c.Title,c.FirstName+' '+c.MiddleName +' '+c.LastName,c.CompanyName,c.SalesPerson,
c.EmailAddress,c.Phone,c.ModifiedDate FROM Customer c

Query output

As you can see, all records have been transferred.

Summary

In this article, we have explored several ways of using Devart Data Pump for exporting SQL databases. In my next article, we will learn how to transfer data from the Azure SQL database to Microsoft Excel files and other data formats using the Devart Data Pump.

An overview of dbForge Schema Compare

nisargupadhyay87 — Fri, 13 May 2022 17:10:30 +0000

The dbForge Schema compare tool is a SQL tool that is used to compare the schema of two databases that exists on one or multiple instances of the SQL Server.
Recently, one of my company's clients was facing an issue in their application that accessed the archived database. After troubleshooting the issue, we came to know that the archived database was out of sync with the production database. The schema was out of sync because the execution of a few DDL scripts failed, and the schema remained unchanged. We fixed the issue by writing a stored procedure that compares the schema between production and archived databases and provides the list of missing objects.
Later, we learned about the dbForge Schema Compare tool that can perform the comparison of two SQL databases very easily. It provides the following benefits

Compare the source and destination database and objects.
Generate the scripts that can be used to create an object missing in the source/target database.
Generate the report which provides the details of the missing objects.

We can compare the following types of objects in the dbForge Schema Compare tool.

SQL Server database: This option is used to compare the database objects of the source and destination database.
Backup of SQL Server database: This option is used to compare the backup of the SQL Server database.
Script Folder: This option compares the source and destination folders containing the script to create database objects.
Source control repository: This option compares two source control repositories that contain the T-SQL database scripts.

In this article, we will learn to compare the objects of two databases. To demonstrate, I have installed SQL Server 2019 and restored two databases named statckoverflow2010 and Stackoverflow2010_old. The source database is stackoverflow2010, and the target database is stackoverflow2010_old. We will find the list of objects that are changed/missing in the target database (Stackoverflow2010_old).
First, click on New schema compare located below the menu bar.

The New schema comparison wizard starts. You can configure the following options:

Source and target databases which you want to compare.
Set advance option to customize sql compare process. Schema and table mapping.

Configure the source and target database.

First, let us configure the source and destination database. In our case, the source database is Stackoverflow2010, and the target database is stackoverflow2010_old.
We compare the databases; therefore, select Database from the Type drop-down box. In the Connection drop-down box, you can select the existing connection or create a SQL Server connection on which the source and destination databases are created. We are creating a new connection; therefore, select Manage.

Another dialog box named opens. Here you can see the various parameters and configuration options to create a SQL Server database connection. Click on New to create a New connection.

In our case, the configuration parameters are specified as follows.

Server Name: Specify the server's hostname on which the SQL Server is installed. In our case, the hostname is Nisarg-PC.
Authentication: select the authentication type. You can use windows authentication or SQL Server authentication. In our demo, we are using Windows authentication.
Database: select the source database. In this demo, the source database is stackoverflow2010.
Connection name: Enter the desired connection name.

Click Connect.

Back to the connection manager dialog box, you can see the new connection has been created. Following is the screenshot of the Connection Manager dialog box.

Now, similarly, configure the Target connection. In this demo, the target database is Stackoverflow2010_old on the same SQL Server instance. Therefore, we will change the database name in the Target SQL Server connection Manager. The parameters in the dialog box named Database Connection Properties are following:

Server Name: Specify the hostname of the server on which the SQL Server is installed. In our case, the hostname is Nisarg-PC.
Authentication: select the authentication type. You can use windows authentication or SQL Server authentication. In our demo, we are using Windows authentication.
Database: select the source database. In this demo, the target database is stackoverflow2010_old.
Connection name: Enter the desired connection name.

Click Connect.

In the Connection Manager dialog box, you can see the new connection has been created.

The source and target screen look like the following image. Click Next to configure the options.

Schema Mapping
You can set the correspondence between the source and target schema owners in the schema mapping screen. Suppose you are comparing the two databases. The source database has two schemas that the target database doesn't have. The stackoverflow2010 database has two schemas in this demo, but the stackoverflow2010_old database does not have any schema. The wizard automatically sets in the target schema grid-view on the schema mapping screen.

Table Mapping
You can choose the list of the database objects you want to compare on the table mapping screen. Also, you can map columns to compare the data type. We will learn more about it in our next article. If you want to exclude any objects from the mapping list, right-click on the object name and click on Unmap.
In our demo, we are comparing all objects.

Begin schema compare process
Once mappings are completed, click on Compare to begin the database comparison process. If the Database is large and contains many objects, the process takes a while to complete.

Once the comparison completes, you can see the object difference shown in the following image.

The comparison project provides the following details

Objects that exist in the only source database.
Objects that exist in both databases but have different values of parameters.
Objects that exist in the target database.
The comparison project shows the T-SQL queries which can be used to synchronize/change the source and destination database.
In our demo, the details provided by the schema comparison is following:
Only in the Source database
The Stackoverflow2010 database contains two schemas named UserPosts and Users, which do not exist in the Stackoverflow2010_old database. The comparison project also generates the CREATE SCHEMA statement. See the following image:

Different in source and destination database
There are two differences found in the source and destination databases.

The database recovery model.
- Stackoverflow2010: SIMPLE recovery model.
- Stackoverflow2010_old: FULL recovery model.
An additional column named description is in the comment table.

See the following images for reference:
Image 1: Database recovery model

Image 2: Column differences in the comment table

Only in the Target database

The Stackoverflow2010_old database contains the tblArchivedPosts table that does not exist in the stackoverflow2010 Database. The comparison project also generates the CREATE TABLE statement that might be used to create a table in the source database.

As you can see, the schema compare tool has provided detailed information on the differences between the source and target database.

Summary

The above article explains the process of comparing the SQL databases using dbForge schema compare. In my upcoming articles, we will review the process to compare the backups of the SQL databases.