Category Archives: Data Access

>T-SQL: Backup database

>CREATE PROCEDURE [dbo].[sp_BackupDatabase]@databaseName sysname, @backupType CHAR(1)ASBEGINSET NOCOUNT ON; DECLARE @sqlCommand NVARCHAR(1000)DECLARE @dateTime NVARCHAR(20) SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),’/’,”) +REPLACE(CONVERT(VARCHAR, GETDATE(),108),’:’,”) IF @backupType = ‘F’SET @sqlCommand = ‘BACKUP DATABASE ‘ + @databaseName +‘ TO DISK = ”D:\Backup\’ + @databaseName + ‘_Full_’ … Continue reading

Posted in Data Access | Leave a comment

>T-SQL: Top N Rows Per Group

>The following will return the top 10 players who hit the most home runs per year since 1990. The key is to calculate the “Home Run Rank” of each player for each year. selectHRRanks.*from(Selectb.yearID, b.PlayerID, sum(b.Hr) asTotalHR,rank() over (partition by … Continue reading

Posted in Data Access, programming | Leave a comment