What operations should be transferred to the database (functions, procedures)?

Question

SQL Server

What operations should be transferred to the database (functions, procedures) in order to unload the backend?
Can you give examples please

Answer 1

The question is provocative, and you will not get a definite answer.
Most (small?) projects try to avoid putting business logic in the database altogether to make it easier to maintain.
At the same time, most DBMSs have pretty good data processing capabilities, you can also calculate tricky statistics and hang a lot of interesting things on triggers, but ... to use such things, you need good expertise in the database, otherwise there is a risk of getting a terrible architecture.

Answer 2

What for? Usually the database is a bottleneck, so they try to unload the database, but you can buy backend servers without problems.

Answer 3

What operations should be transferred to the database (functions, procedures)

... representation. Everything, provided that you know the DBMS language better than the backend.

Adding elements to a list separated by commas without repetition

CREATE TRIGGER AlbumRoleInsert ON AlbumRole INSTEAD OF INSERT AS 
BEGIN
  SET NOCOUNT ON
  DECLARE @ALB INT, @PRF INT, @ROL VARCHAR(500), @mas INT, @pla BIT, @mem BIT, @gue BIT
  DECLARE @ID INT, @RLR VARCHAR(500)
  DECLARE @Role TABLE(Role VARCHAR(100))
  
  DECLARE LISTROLE CURSOR LOCAL FAST_FORWARD FOR
    SELECT Album, Master, Performer, Play, Member, Role, SpecialGuest
      FROM Inserted
  OPEN LISTROLE
  FETCH LISTROLE INTO @ALB, @mas, @PRF, @pla, @mem, @ROL, @gue
  WHILE @@FETCH_STATUS = 0 BEGIN
    -- Есть запись?
    IF EXISTS( SELECT * FROM AlbumRole WHERE (Album = @ALB) AND (Performer = @PRF)) BEGIN
      SELECT @ID = ID, @RLR = [Role] FROM AlbumRole WHERE (Album = @ALB) AND (Performer = @PRF)
      -- Есть роль?
      INSERT INTO @Role SELECT TRIM( value ) FROM STRING_SPLIT( @RLR, ',' )
      INSERT INTO @Role SELECT TRIM( value ) FROM STRING_SPLIT( @ROL, ',' ) 
                                           WHERE TRIM( value ) NOT IN ( SELECT [Role] FROM @Role )
      DELETE @Role WHERE (TRIM( [Role] ) = '')
      SELECT @RLR = STRING_AGG ( [Role], ', ' ) FROM @Role
      -- Обновление списка.
      UPDATE AlbumRole SET Role = @RLR WHERE ID = @ID
    END ELSE BEGIN
      -- Добавление: 
      INSERT INTO AlbumRole ( Album, Master, Performer, Play, Member, Role, SpecialGuest ) 
             VALUES( @ALB, @mas, @PRF, @pla, @mem, @ROL, @gue )
    END
    -- Следующая строка
    FETCH LISTROLE INTO @ALB, @mas, @PRF, @pla, @mem, @ROL, @gue
  END
  CLOSE LISTROLE
  DEALLOCATE LISTROLE
END