Script to find the No of Reads / Writes on Database files

Use below script to find the No of Reads / Writes on Database files

SELECT DB_Name(MF.Database_ID) AS [DB Name],
       MF.Physical_Name AS [Physical File Name],
       Num_Of_Bytes_Read,
       Num_Of_Reads,
       IO_Stall_Read_MS,
       Num_Of_Bytes_Written,
       Num_Of_Writes,
       IO_Stall_Write_MS,
       IO_Stall,
       Size_On_Disk_Bytes
FROM sys.dm_IO_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS MF ON MF.Database_ID = divfs.Database_ID
AND MF.File_ID = divfs.File_ID
                                  

Script to track Database Growth

Use below script to track database growth over a period of time:

WITH C AS (
SELECT 
RN = ROW_NUMBER() OVER( ORDER BY Backup_Start_Date DESC),
BackupDate = CONVERT(VARCHAR(10), Backup_Start_Date, 111), 
SizeInMB = FLOOR(Backup_Size/1024000) 
FROM msdb.dbo.BackupSet 
WHERE Database_Name = DB_NAME() -- put your databaseName here
AND Type = 'd'
) SELECT C.* , Diff = SizeInMB - (SELECT SizeInMB FROM C C2 WHERE RN = C.RN + 1) FROM C ORDER BY 1 ASC

Query to return database files and free space

Use below query to get database files and free space:

WITH C AS (
SELECT G.Name AS [FileGroupName],
       S.Type_Desc,
       S.Physical_Name AS [FileName],
       S.Size * CONVERT(FLOAT, 8) AS [Size],
       CAST(CASE S.Type
               WHEN 2 THEN 0
               ELSE CAST(FILEPROPERTY(S.Name, 'SpaceUsed') AS FLOAT) * CONVERT(FLOAT, 8)
             END AS FLOAT) AS [UsedSpace]
FROM Sys.filegroups AS G
INNER JOIN Sys.Master_Files AS S ON (( S.Type = 2 OR S.Type = 0)
                                      AND S.database_ID = DB_ID()
                                      AND (S.drop_lsn IS NULL))
                                    AND (S.Data_Space_ID = G.Data_Space_ID)
)
SELECT *, [Size] - [UsedSpace] AS RemainingSpace FROM C

SQL Server Cannot resolve collation conflict for equal to operation

Few days ago I had copied some tables, Stored Procedures, Views etc  from one database to a new database. 

Now whenever I execute a Stored procedure, I got below error:

"Cannot resolve collation conflict for equal to operation."

Thre error clearly indicates that the collation types for the columns being joined in the sql statement is different.

I had overcome this error by telling the columns to use the databases default collation on either side of the join fixed the problem - e.g.

SELECT table1.*, table2.* FROM table1
INNER JOIN table2 ON table1.column1 COLLATE DATABASE_DEFAULT = table2.column1 COLLATE DATABASE_DEFAULT

Query to find Computed columns

Use below query to find all computed columns in a database:

SELECT O.NAME AS [Table], C.NAME AS [Column], T.TEXT AS [formula]
FROM SysColumns AS C, SysObjects O, SysComments T
WHERE C.ID = O.ID AND O.ID = T.ID AND C.ColID = T.Number AND Iscomputed = 1 AND O.type = 'U'