Stuff and Replace Function in SQL Server : Detailed Description

(This is a guest blog post by Daniel Jones (li | t | fb). Daniel is a SQL Server DBA and contributor at SQL Tech Tips. Having 2 + years of experience in SQL recovery and system infrastructure.)

In SQL Server, STUFF and REPLACE functions are used to replace the characters in a string. Both functions play an important role in Transact-SQL with performing the distinct functionality. In the following section, we are going to discuss about Stuff and Replace function in detail. Continue reading “Stuff and Replace Function in SQL Server : Detailed Description”

INDEX REORGANIZE vs REBUILD in SQL Server

This blog post is meant to help distinguish the differences between REORGANIZE and REBUILD when it comes to index fragmentation. If you are serious about learning more of the internals of how indexing works in SQL Server, watch Kimberly Tripp’s (blog | twitter) video called, “Index Internals” here (open link and scroll down to “Index Internals”). Continue reading “INDEX REORGANIZE vs REBUILD in SQL Server”

How to Find the Space Used in Your Data Files

I recently had to resize a data file on of my production SQL Server databases and needed to know an appropriate size to resize it to. So, I created this simple script that queries sys.database_files and brings back the File ID, File Location, File Name, Original Size, Space Used and Space Left.

SELECT sdb.file_id as [File ID],
sdb.physical_name [File Location],
sdb.name [File Name],
CONVERT(numeric(10,2),ROUND(sdb.size/128.,2)) AS [Original Size in MB],
CONVERT(numeric(10,2),ROUND(FILEPROPERTY(sdb.name, 'SpaceUsed')/128.,2)) AS [Space Used in MB], 
CONVERT(numeric(10,2),ROUND((sdb.size - FILEPROPERTY(sdb.name, 'SpaceUsed'))/128.,2)) AS [Space Left in MB]
FROM sys.database_files sdb
Continue reading “How to Find the Space Used in Your Data Files”