Difference Between Truncate and Delete Command in SQL Server

I have seen a lot of times, people get confused to differentiate the Truncate and Delete commands in SQL Server. If you are a DB developer or a DBA, then this is a mandated requirement to know exactly what is being performed when Truncate on a table and delete on a table. Let’s check the difference between Truncate and Delete in SQL Server.

TRUNCATE 

TRUNCATE is a DDL command, which is used to delete all the rows of a table without being to use a WHERE clause. Most of them come up with an assumption that a truncate cannot be rolled back once the TRUNCATE command is executed. But, the assumption is very untrue! The TRUNCATE command can be rolled back with the lopping into a begin transaction and rollback commands. 

Further, in truncation, the pages are completely deallocated and commit to the transaction log as deallocation. TRUNCATE can be only executed if login is given with an ALTER permission for a database. Additionally, as the rows are completely removed from the table, there is less utilization in the transaction log, hence performance is high.

Before we proceed with the truncation of the table, let’s create a dummy table, insert some values on CusName and CusID, and test on it.

Create table dummy

(

CusID int not null,

CusName nvarchar(100) null

)

Insert into dummy values (1, 'Dale'), (2, 'James')

T-SQL command to truncate a table.

begin tran

Truncate table dummy

select * from dummy

rollback 

The above command will completely remove two rows with two unique records from the dummy table.

DELETE

DELETE is a DML command, which is used to either remove all the rows from a table or a specific row from a table using the WHERE clause. As the rows are deleted from the table one by one, every single delete from the table is maintained and recorded in the transaction log. Since such records are maintained, there is high utilization of transaction log, due to which the performances are low.

Further, there is a need to DELETE permission for login in SQL Server to carryout the DELETE command. When there is a rollback possibility for Truncate, there is also a similar way to rollback DELETE records of a table by looping to Begin Transaction.

T-SQL command to delete a table.

Let’s use the dummy table to delete the CusID record.

begin tran

DELETE from dummy where CusID=1

select * from dummy

The above delete command will just delete the row selected in the WHERE clause with CusID=1.

 rollback 

You may also need: 
"Advantages of Using UNION ALL Over UNION in SQL Server"
"How to resolve error “The local device name is already in use” in SQL server?"
"How to Add or Remove Identity Property on Column from a table in SQL server"

Understanding the Importance of Database Schema in SQL Server

A schema is a container for objects such as tables, views, stored procedures, functions, etc., to facilitate management and ownership of a database object. In Microsoft SQL Server, the default schema is dbo, wherein any object created will be referenced to a dbo schema. There can be more than one schema created for a database in SQL Server. When a database is created, by default we will get to see the schemas as shown in the screenshot.

Let’s take a look at the example for differentiating the default schema and the created schema.

CASE 1: Creation of default dbo schema

Say, if we are creating a database with the name SQLArena and create an object say SQLArena_Test, then the object will reference to a dbo schema by default.

Create database [SQLArena]

Go

Create table SQLArena_Test(id int null)

From the created table, the id is the value which will be referenced to the dbo schema by default

CASE 2: Create a new database schema (Schema_Name: Trash)

Say, if we are creating a new database schema in already created database – SQLArena, then we will have to create the table with the prefix as the <Schema_Name> along with the object name to reference to the respective schema created. We can create the same table SQLArena_Test with a new database schema as below.

Create table <Schema_Name>.SQLArena_Test(id int null)

Example:

Create table Trash.SQLArena_Test(id int null)

From the above two cases, don’t be confused while fetching the column of the same table. Now, while fetching for the ‘id’ column in SQLArena database, by default the ‘dbo’ schema will be fetched rather than the ‘Trash’ schema.

GUI to guide you through creating a new schema.

1. Expand the respective database from the object explorer.

2. Expand “Security” under the selected database.

3. Now, we will see an option to expand from “Schema”.

4. Here, all the default schemas will be intact. One such is the dbo.

5.  Right-click on the schema and select “New Schema…”.

Simple Way to Change Index Fill Factor Settings in SQL Server

The fill factor is basically used for a database only if there is an index created. While checking for any databases, the fill factor is set to zero by default. Depending the table usage, the fill factor is set. In simple terms, when there is an insert, update or delete for a table, there should be some interpretation of data and these data should be stored somewhere ideally. Now, these data store in the leaf-level page and the amount of data to be stored on each page is determined by the Fill Factor.

A smallest unit page is 8K. When a page of 8k size is filled with data of more than 8K, then the data will be stored in a new page, which is basically called as page split. Let’s check on a simple way to change index fill factor settings in SQL Server.

Steps to change index fill factor settings in SSMS

1.   Open SSMS and right click on the server name to click the “Properties”.

2.  A new window pops with more info about the server, Click on “Database Settings”

Take a look at the screenshot of the same below:

Here is the T-SQL script for the changes from the screenshot.

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'fill factor (%)', N'80'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE

GO

Fixing CTRL+R Show/Hide Control Pane in SQL Server

To be straight forward, I experienced the issue of CTRL+R not working all of a sudden. As per my personal experience, this happens when there are multiple instances opened with different servers connected in respective instances. Additionally, the same issue of results not getting vanished using CTRL+R could be due to computer formatting. Once the computer is formatted or if there is a windows patching update, the settings may get changed. Not to panic about it. Here is a simple fix for the show/hide control pane result tab using the CTRL+R option in SQL Server.

Steps for Fixing Show/Hide pane in SSMS

1. Go to option “Tools”..

2. Click on “Options” to get a new window popped with more options.

3. On the left side tab, click on the option “Keyboard” to populate more options.

4. Set the mapping scheme keyboard to default (Default)

5. Tap the “Reset” button for the fix.

Take a look at the screenshot:

You may also like reading:
"How to create a Differential Backup in SQL Server"
"How to create a Full Backup in SQL server"
"How to create a transaction log backup in SQL server"

SQL Server - How to Shrink Big Log File Size of a Database

I had previously written an article on how to shrink the logfile size of a database. But, while working intensely on databases that are pretty huge with respect to transactions, I felt it really challenging to shrink the log file. As I had previously mentioned, the log file size increases with an increase in transactions such as bulk data insertions, Index ReOrg, Update Statistics, or Full backups and Log backups running for quite a long time. There are two ways through which a big log file size can be shrunk. 

Method 1:

Say for example, if the log file size is utilized to 90 GB of 100 GB. Then, for sure there will be high difficulty in shrinking the log file. Here we can shrink the log file by giving the user databases to 10GB and shrink the log file.

Before to shrink the log file, check the log file size and log percentage with the below DBCC command: 

DBCC SQLPERF(logspace)

Now, let’s take a database for example – SQLArena_TEST

First, take the log backup of the respective database

BACKUP LOG <Database_Name> TO  DISK = N'<Drive_Path>.trn'

WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP

Example:

BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST.trn'

WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP

Shrink the file by giving the user database to 10 GB, as said the example here was taken to be as 90 GB utilized log. The user database needs to be reduced in 10 GB recursively to completely shink the log file. 

USE SQLArena_TEST

GO

DBCC SHRINKFILE ('SQLArena_TEST_Log', 80328) 

Note: Take the log backup twice or thrice with a different filename for the path consecutively to shink the database to 10 GB reduction successfully.

Method 2:

As per method 1, where the shrinking happens at 10 GB reduction for the user database, where we will be shrinking the log file size at a single shot. This method as far I have seen seems to be something pretty faster than any other shrinking method used in SQL Server. 

When the log file sizes are too high, we tend to make a mistake of giving just one path while taking the log backup and shrinking. Instead, let’s try to increase the drive path in the log backup script to finish the log backup and shrinking the data file of the user by giving zero and truncate. Take a look at the command mentioned:

– Given just one path for log backup and this fails in shrinking

BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST.trn'

WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP 

– Give 7-8 drive paths to shrink at a single shot, Take this log backup twice

BACKUP LOG SQLArena_TEST TO  DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path1.trn',

DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path2.trn',

DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path3.trn',

DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path4.trn',

DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path5.trn',

DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path6.trn',

DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path7.trn',

DISK = N'C:\MSSQL\SQLArena\SQLArena_TEST_Path8.trn'

WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10

Once the log backup with multiple path files are created, shrink the user data by zero with TRUNCATEONLY command to completely shrink the big log file size. 

USE SQLArena_TEST

GO

DBCC SHRINKFILE (N'SQLArena_TEST_Log' , 0, TRUNCATEONLY)

GO

Advantages of Using UNION ALL Over UNION in SQL Server

Most of them working in development or SQL environment would have come across UNION and UNION ALL. But, when we look into the cause of using UNION and UNION ALL, it’s pretty interesting not just in terms of results, but also in terms of performance.

Basic difference between UNION and UNION ALL is that, UNION ALL results in almost all the duplicates between the tables, whereas UNION omits from being resulting the duplicate values.

Secondary difference and advantage would be getting the results at faster rate. The results will be generated at a pretty faster rate for UNION ALL when compared to UNION. However, the UNION ALL generating the duplicate results can eventually lead to long time querying.

For example, let’s take count of a table name CUS from two databases, one without duplicates and the other with duplicate counts. Take a look at the script below:

FOR UNION:

SELECT '-----CUSName',0

UNION

      SELECT 'Customer1',COUNT(*) FROM CUS

UNION  

      SELECT 'Customer1',COUNT(*) FROM CUS

Another scenario is to use UNION ALL instead of UNION in the statements to see a different result in the output set. 

UNION ALL:

SELECT '-----CUSName',0

UNION ALL

      SELECT 'Customer1',COUNT(*) FROM CUS

UNION ALL

      SELECT 'Customer1',COUNT(*) FROM CUS

From the above outputs, it is clear that UNION ALL functions in selecting even the duplicate counts. Seeing the example, Customer2 consists of 10 duplicate records, which was put forward along as a redundancy.

You may also need: "10 New Features SQL Server 2016 Does Better than SQL Server 2014"

How to resolve error “The local device name is already in use” in SQL server?

This happened very recently to me when I set up a job to fetch resource from a local machine, which caused the error as “The local device name is already in use”. Now, looking at the error we may find it very straight-forward to know that that there is a resource which is using the local device path. Further, if we are setting up two jobs or running a command, to fetch the same resource at a time, then there are high chances to get the error message.

 A simple way to fix the error could be either to change the schedule in the job or run the session/process in odd timings. Here are the steps to schedule the job timings.

1. Right click on the job and select “Properties”.
2. Under properties select “Schedules” option.
3. Double click the step name under“Name” option to open the schedules.
4. Now setup the schedules accordingly.

Now, let’s see a sample schedule setup for a job in the screenshot below.

Rename Physical Database File names of an Existing Database in SQL Server

Usually, Physical database names are not changed after the database creation, unless there is a requirement for name change for the created database. Say, a database is created and it is renamed with a new name. The new database name will still point to the same physical paths of the originally created database and hence cause confusion to rectify the right database file names.

Unlike, the logical file names which can be altered using the ALTER command for the database, the physical database file names cannot be changed when the database is online. Very straightforward to know that these physical paths are already being used when the database is ONLINE. Hence, we will have to take the database offline and then proceed to alter the physical database file names. 

Before moving on to the methods of renaming the physical database files, let’s check the information of the database by using a simple stored proc.

EXEC sp_helpdb <Database_Name>

Example:

USE SQLArena_Test

EXEC sp_helpdb SQLArena_Test

From the above stored proc, if we have to gather the physical data filenames and logical filenames of the database, proceed with the query as:

select file_id, NAME as [LOGICAL_FILE_NAME], PHYSICAL_NAME

from sys.database_files

Take database offline and then rename the Physical Database Files

In order to change the physical filename from the screenshot, let’s first disconnect all the opened session in the SQL Server and take the database OFFLINE.

USE SQLARENA_TEST

GO

ALTER DATABASE SQLARENA_TEST SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

ALTER DATABASE SQLARENA_TEST SET OFFLINE

Check if the database is offline or online with the command as:

SELECT NAME, STATE_DESC FROM SYS.DATABASES WHERE NAME='SQLARENA_TEST'

Now, all the sessions and process connected to SQLArena_TEST database is disconnected and taken the database to offline mode. Let’s ALTER the physical filenames by "_CHANGED" :

ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_CHANGED.mdf')

GO

ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_1', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_1_CHANGED.ndf')

GO

ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_2', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_2_CHANGED.ndf')

GO

ALTER DATABASE SQLARENA_TEST MODIFY FILE (Name='SQLARENA_TEST_log', FILENAME='C:\MSSQLSERVER\MSSQL\DATA\SQLARENA_TEST_log_CHANGED.ldf')

GO

Set the database to online and now you can now check the database information and the status of the databases as:

EXEC sp_helpdb SQLArena_Test

GO

USE SQLARENA_TEST

GO

ALTER DATABASE SQLARENA_TEST SET SINGLE_USER

GO

ALTER DATABASE SQLARENA_TEST SET OFFLINE

While using these scripts in production should be with caution, since backups may fail if the logical filenames are incorrectly assigned. 

You may also need: "Rename Logical Database File names of an Existing Database in SQL Server"

TRIM Function to Remove Blank Spaces From a String in SQL Server

Often while entering data in the tables or during bulk insertions, there are high chances of accidently adding white space either in the trailing or leading end of the string. Such instances can cause very annoying for the users to see the results in a shabby format. To overcome the problem of blank space in the string, we can create the TRIM() function and pass the string as parameter to the function.

Before moving to create the trim function, let’s see the two types of Trim functions that are available in SQL Server

1.  LTRIM - This function helps in removing the blank spaces from the leading end of the string

2.  RTRIM - This function helps in removing the blank spaces from the trailing end of the string

Using the above function in SQL Server to trim the string of an existing table in a database. The initial step is to create the table and insert some values to it with blank spaces. For example, In the CusName column insert names with the blank spaces both front and back of the string.

USE SQLArena_Test

GO

Create table TestDB

(

EID int null,

CusID nvarchar(100) null,

CusName Varchar(100) null

)

From the above table created, in case the names in “CusName” consists of leading and trailing spaces, then use the below command:

UPDATE TestDB SET CusName= LTRIM(RTRIM(CusName))

In the update statement, both LTRIM() and RTRIM() functions are applied while passing the CusName as the parameter. Both the functions are needed to apply in SSMS or Oracle to remove unwanted white spaces. In case if we want to create a function named “Trim” then we can create the function as show:

use SQLArena_Test

GO

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))

RETURNS VARCHAR(MAX)

BEGIN

RETURN LTRIM(RTRIM(@string))

END

GO

SELECT dbo.TRIM('SQLArena')

GO

Additionally, using these function in JOINT and WHERE clause can reduce the performance drastically and using these in production should be into consideration as the query run-time can increase very high. 

How to Add or Remove Identity Property on Column from a table in SQL server

I got stuck at a point on removing the identity column from a table in a database, thinking much about T-SQL scripts to remove the identity property. However, there is no direct DDL scripts like ALTER or MODIFY tables to remove the identity property.

Steps to remove the Identity property through GUI in SSMS:

1. Before dropping the identity, please check the table from the database that you are referring to.

2. From the “Object Explorer”, expand the database referring to and right-click on the table where the identity property needs to be changed.

3.  Select the “Design” option from the list, which will open the design of the table as shown below.

4. Under the Result section, the design is popped with the details of the table with respect to structure.

5. Now, expand the “Identity Specification” and opt as “Yes/No” in the “Is Identity” option.

6. Click on the “Save” option to effect the changes made.

When you click on the "Design option", the below layout pops up:

Take a look at the result section to opt the "Is Identity" changes:

Additionally, there is an option to generate a T-SQL script by the action performed using the GUI in SSMS. Since there is no ready-made script for changing the identity property, a feature in SSMS could be used to generate the T-SQL script.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET ARITHABORT ON

SET NUMERIC_ROUNDABORT OFF

SET CONCAT_NULL_YIELDS_NULL ON

SET ANSI_NULLS ON

SET ANSI_PADDING ON

SET ANSI_WARNINGS ON

COMMIT

BEGIN TRANSACTION

GO

/*creating a DumpTable*/

CREATE TABLE dbo.Tmp_DumpTable

       (

[COUNT] int not null identity(1,1),

fruits nvarchar not null,

vegetables nvarchar not null

       )  ON UserData

       TEXTIMAGE_ON UserData

GO

ALTER TABLE dbo.Tmp_DumpTable SET (LOCK_ESCALATION = TABLE)

GO

/  /*Setting the Identity_Insert to ON*/

SET IDENTITY_INSERT dbo.Tmp_DumpTable ON

GO

IF EXISTS(SELECT * FROM dbo.DumpTable)

       EXEC('INSERT INTO dbo.Tmp_DumpTable (Count,Fruits,Vegetables)

              SELECT Count,Fruits,Vegetables FROM dbo.DumpTable WITH (HOLDLOCK TABLOCKX)')

GO

/  /*Setting the Identity_Insert to OFF*/

SET IDENTITY_INSERT dbo.Tmp_DumpTable OFF

GO

/*Dropping the created DumpTable*/

DROP TABLE dbo.DumpTable

GO

EXECUTE sp_rename N'dbo.Tmp_DumpTable', N'DumpTable', 'OBJECT'

GO

COMMIT

Related Post: "Reset Identity Column Values In SQL Server"