Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Tuesday, March 6, 2018 4:31 PM
We want to remove white space from a SQL database using a query.
Our database is also very large and we want do a copy it to another instance using copy wizard. Will removing the white space reduce the size of the database or do we also have to compact it??
Are there any other simple and effective sql queries that are better than the below? Or is what I have below sufficient?
what is the difference between the line below between the single quotes:
SET @String1 = ' String '
versus
SET @String1 = ' String '
XXXXXXXXXXXXXXXXXXXXXXXXXXXX
entire script for removing white spaces from a SQL database below:
DECLARE @String1 NVARCHAR(MAX)
SET @String1 = ' String '
SELECT @String1 OriginalString, RTRIM(LTRIM(@String1)) TrimmedValue
GO
versus
DECLARE @String1 NVARCHAR(MAX)
SET @String1 = ' String '
SELECT @String1 OriginalString, RTRIM(LTRIM(@String1)) TrimmedValue
GO
XXXXXXXXXXXXXXXXXXXXXXx
dsk
Tuesday, March 6, 2018 4:38 PM
Hi,
The difference is that ' String ' has more spaces in both left and right side.
And ' String ' has just one space.
By using LTRIM, RTRIM, you will remove all spaces in the right and left side.
Ousama EL HOR
[If a post helps to resolve your issue, please click the "Mark as Answer" of that post or click 
"Vote as helpful" button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]
[User Page] [MSDN Page] [Blog] [Linkedin]
Tuesday, March 6, 2018 4:45 PM
so you are saying ' String ' will remove more white space than ' String ' ?
dsk
Tuesday, March 6, 2018 4:45 PM
You can use TRIM if you are on SQL Server 2017. It is much simpler than using both LTRIM and RTRIM.
Tuesday, March 6, 2018 4:49 PM
I am on sql 2014. So are you saying that I cannot use TRIM?
dsk
Tuesday, March 6, 2018 4:54 PM
Is the below query more effective than LTRIM and RTRIM for removing white space?
DECLARE @TestString AS VARCHAR(20)
SET @TestString = ‘Test String’
To remove the extra spaces we need to use SET @TestString= REPLACE(REPLACE(REPLACE(@TestString,’ ‘,’ %’),’% ‘,”),’%’,”)
dsk
Tuesday, March 6, 2018 4:58 PM
Do we need to compact the database after removing the white space? We are going to copy the database to another server hence we want to reduce the time without sacrificing performance. Do you recommend not compacting the database for performance reasons?
dsk
Tuesday, March 6, 2018 5:19 PM
There are many variables. What you are talking about will likely have no impact on performance and little on physical size of the database, unless you have billions of records.
What are the data types in the database?
Tuesday, March 6, 2018 6:46 PM
What is the data type on your column? Is it varchar or char?
If it is varchar then your removal of white space will only have any impact on white space to the left of the characters.
If it is char then you will have to change the size of the column to have any impact. For example if it is defined as char(10) then this will ALWAYS use 10 bytes of storage no matter whether the column contains 'x' or 'xxxxxxxxxx'.
A better idea to reduce the overall size is to look at compression.
Martin Cairney SQL Server MVP
Tuesday, March 6, 2018 10:31 PM
How do we determine the types of data in the database?
dsk
Tuesday, March 6, 2018 10:34 PM
how do we determine the type of data in our column?
If shrinking a viable option is there a difference between shrinking using a maintenance task versus using using a query? What query do you recommend to shrinking the database?
So you are saying for the following:
Varchar - we can use a query such as LTRIM to remove white space.
Char - need to change column size. And you are recommending compression?
dsk
Tuesday, March 6, 2018 11:29 PM
I don't recommend shrinking a database as this will introduce fragmentation in the tables and indexes and have a negative impact on performance.
Look at the structure of your table - in SSMS expand the database and then the table and look at the columns - they will show what the datatype is.
The datatype is fundamental to a database table - it defines what you are going to put in each column.
Martin Cairney SQL Server MVP
Friday, March 9, 2018 1:33 PM
Is it not okay to shrink the database as long as you rebuild the indexes (this fixes the fragmentation issues)?
dsk
Friday, March 9, 2018 2:01 PM
The bigger question is why are you shrinking? Do you have a disk space issue?
I would only do this if you know for certain the database will not grow again due to adding data.
If there is any chance the database will grow again, the better solution is to add more disk space.
The reason is auto-grow operations are expensive and can impact performance at unexpected times, especially if the system is resource constrained or heavily loaded. The other thing that can happen if there are frequent grow/shrink operations, this can lead to on-disk file fragmentation which degrades I/O performance.
Another option to consider if you are wanting to make the database smaller is to implement Data Compression. This can sometimes help with performance also.
HTH,
Phil Streiff, MCDBA, MCITP, MCSA
Friday, March 9, 2018 2:10 PM
The DB is huge- 215 GB and we want to remove white space/shrink/rebuild indexes before we use the copy wizard. We believe the smaller the database the quicker it will copy. Data compression is not an option.
dsk
Friday, March 9, 2018 2:28 PM
If you shrink the database and then defragment (rebuild/reorganize) indexes afterwards, it likely will just regrow again. The index rebuild/reorganize operation takes space.
The preferred method of moving databases is to take a backup and copy the backup file across network to destination server. The end result is exactly the same as using the copy wizard. Plus, you have the option of backing up with compression, which makes the backup file smaller, hence, should take less time to copy across the network, theoretically.
HTH,
Phil Streiff, MCDBA, MCITP, MCSA
Friday, March 9, 2018 2:33 PM
The "copy wizard" copies the data. The physical size of the database files, has no effect on the copy process.
Friday, March 9, 2018 3:23 PM
It does if you use the detach/attach method. So it all boils down to what method is selected in the copy database wizard.