Check available disk space in all the database files

Here is the script:

use [db_name]
go

select name, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') as int)/128.0 as AvailableSpaceInMB
from sys.database_files
go

Knowing this info is a good guide for file shrinking operations.

The link between bcp utility and the bulk insert statement

Just tried to find out the real difference between bcp utility and bulk insert statement and here is the article from Microsoft's official site.

Quoted too: "The BULK INSERT statement allows you to bulk copy data to an instance of SQL Server using the functionality of the bcp utility with a Transact-SQL statement, rather than from the command prompt."

Therefore, it seems the whole "bulk insert" statement is purely another way of "bcp in" command.

The use of object varaibles, resultset and foreach loop in SSIS

I've recently been involved in documenting an SSIS package made by our lead developer and there is a foreach loop that he used to loop through a resultest from a query. An object variable is used to hold the resultset temporarily and the foreach loops through this result to repeat some actions. Since this trick is pretty new to me, I hence document a very good tutorial here to keep a memo.

A very good article about the concept of filegroup

Here is the link.

Script to see index usage stats

SELECT object_name(s.object_id) AS ObjectName

, s.object_id

, i.name as IndexName

, i.index_id

, user_seeks

, user_scans

, user_lookups

, user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id ()

AND objectproperty(s.object_id,'IsUserTable') = 1

ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

-- Possible bad Indexes (writes > reads)

DECLARE @dbid int

SELECT @dbid = db_id()

SELECT 'Table Name' = object_name(s.object_id), 'Index Name' =i.name, i.index_id,

'Total Writes' = user_updates, 'Total Reads' = user_seeks + user_scans + user_lookups,

'Difference' = user_updates - (user_seeks + user_scans + user_lookups)

FROM sys.dm_db_index_usage_stats AS s

INNER JOIN sys.indexes AS i

ON s.object_id = i.object_id

AND i.index_id = s.index_id

WHERE objectproperty(s.object_id,'IsUserTable') = 1

AND s.database_id = @dbid

AND user_updates > (user_seeks + user_scans + user_lookups)

ORDER BY 'Difference' DESC, 'Total Writes' DESC, 'Total Reads' ASC

Case sensitive search query

To view the full list of all collocation ino, you can run the following query:

SELECT * FROM fn_helpcollations()
go

For the real technique of enable case-sensitive search query, carry on and read this.

bcp utility quick ref

I found the -c argument is interesting for dumping data out of database and then being open in Excel easily.

The original post is here.

DBCC checkdb primer

This is another very good article.

You can add statistics on columns that don't have statistics in order to boost query performance.

This trick is totally something new to me. Please read further here.