SQL Server has a different syntax for creating a function, but it works quite similarly to MySQL. The following query shows you how to create a function in SQL Server: 

CREATE FUNCTION hittinglevel
(@g_all SMALLINT) 
RETURNS VARCHAR(10) 
AS 
BEGIN 
     DECLARE @hitlevel varchar(10); 
     IF @g_all BETWEEN 0 and 10
     SET @hitlevel = 'barely any'
     IF @g_all BETWEEN 11 and 50
     SET @hitlevel = 'some'
     IF @g_all BETWEEN 51 and 100
     SET @hitlevel = 'many'
     IF @g_all > 100 
     SET @hitlevel = 'tons'
     RETURN @hitlevel; 
END; 

The function you just created in SQL Server will work the same way as in MySQL, and you can drop it the same way as well.