Accordingly to company policy I have to prevent creating SQL logins with CHECK_POLICY=OFF setting, so I've created a trigger:

CREATE TRIGGER [create_or_alter_login] ON ALL SERVER
FOR CREATE_LOGIN, ALTER_LOGIN
as
begin
    DECLARE @data xml
    DECLARE @text nvarchar(max);
    DECLARE @user nvarchar(max);
    DECLARE @object_name nvarchar(max);
    DECLARE @MESSAGE_LOG varchar(255);
    DECLARE @MESSAGE_PRINT varchar(255);
SET @data = EVENTDATA();
SET @text = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)');
SET @user = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)');
SET @object_name = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)');
IF(@text LIKE N'%CHECK_POLICY=OFF%')
BEGIN
    SET @MESSAGE_LOG = 'CHECK_POLICY=OFF is a forbidden setting. Rollback for a batch ' + @text 
    exec xp_logevent 60000, @MESSAGE_LOG , warning
    SET @MESSAGE_PRINT  = '
    ************************************************************
    ' + 'CHECK_POLICY=OFF is a forbidden setting.
    ' + 'Rollback SQL login change for '+ @object_name + '
    ************************************************************
    '
    Rollback
    RAISERROR (@MESSAGE_PRINT,10,1)
    
END
ELSE
BEGIN
    SET @MESSAGE_LOG = 'SQL Login has been created or changed: ' + @text
    exec xp_logevent 60000, @MESSAGE_LOG , informational
END

END

In case of CREATE LOGIN [test7] WITH PASSWORD=N'there is a password', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF trigger is functioning and blocks creating a login with CHECK_POLICY=OFF (script and GUI)

In case of empty password: CREATE LOGIN [test7] WITH PASSWORD=N'', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF login is created and it doesn't matter if I use t-sql or GUI.

Any idea what happens where and how I should modify a trigger to prevent creating a login with CHECK_POLICY=OFF and empty password?

Tested on SQL 2012, 2016, 2017 and 2019

Thank you!

Since you didn't include sufficient details in your question, I can only give you a basic idea how that should work:

select *
from yourtable x
where not exists (
  select 1
  from yourtable y
  where x.partnum = y.partnum  -- same part number
  and y.binnum = 'KARDEX'      -- but in the KARDEX bin
)

What should be the max_connection for 48 core PROCESSORS and 256 gb RAM ?

I have this query that finishes in less than a second on average:

SELECT
    a.trea_code,
    a.fin_year,
    a.bill_no AS tran_id,
    ((('Transfer FROM ' :: text || (a.ddo_code) :: text) || 'ON Bill No : ' :: text) || btrim(((a.bill_no) :: character(10)) :: text)) AS party_name,
    a.voucher_no,
    a.ddo_code,
    a.accounting_date AS final_date,
    fb.audit_batch_date AS prov_date,
    'D' :: text AS status,
    substr((b.rscheme_code) :: text, 1, 4) AS major_head,
    substr((b.rscheme_code) :: text, 5, 2) AS submajor_head,
    substr((b.rscheme_code) :: text, 7, 3) AS minor_head,
    substr((b.rscheme_code) :: text, 10, 2) AS sub_head,
    substr((b.rscheme_code) :: text, 12, 2) AS subsub_head,
    b.rscheme_code AS scheme_code,
    b.amount,
    'FTC' :: text AS ttype
FROM
    final_bill_master a
    LEFT JOIN final_bill_track fb ON
     a.fin_year = fb.fin_year
    AND a.trea_code = fb.trea_code
    AND a.bill_no = fb.bill_no
    LEFT JOIN final_bill_deduction b
     ON a.trea_code = b.trea_code
    AND a.fin_year = b.fin_year
    AND a.bill_no = b.bill_no
    AND (b.flag = 'N' :: bpchar)
WHERE
    a.ddo_code IN (
        select
        ddo_code
    from
        trea.field_ddo_map
    where
        field_dept_cd = :fieldDepartmentCode
    )

Here's it's explain statement result:

QUERY PLAN
Gather  (cost=1015.19..276482.46 rows=300306 width=315)
  Workers Planned: 2
  ->  Nested Loop Left Join  (cost=15.19..245451.86 rows=125128 width=315)
        ->  Nested Loop Left Join  (cost=14.64..157248.09 rows=125128 width=37)
              ->  Hash Join  (cost=14.21..82705.38 rows=125128 width=33)
                    Hash Cond: (a.ddo_code = field_ddo_map.ddo_code)
                    ->  Parallel Seq Scan on final_bill_master a  (cost=0.00..78451.21 rows=1084921 width=33)
                    ->  Hash  (cost=14.08..14.08 rows=10 width=8)
                          ->  HashAggregate  (cost=13.98..14.08 rows=10 width=8)
                                Group Key: field_ddo_map.ddo_code
                                ->  Bitmap Heap Scan on field_ddo_map  (cost=4.36..13.96 rows=10 width=8)
                                      Recheck Cond: (field_dept_cd = 'TAX'::bpchar)
                                      ->  Bitmap Index Scan on field_ddo_map_pk  (cost=0.00..4.35 rows=10 width=0)
                                            Index Cond: (field_dept_cd = 'TAX'::bpchar)
              ->  Index Scan using final_bill_track_pkey on final_bill_track fb  (cost=0.43..0.60 rows=1 width=21)
                    Index Cond: ((a.fin_year = fin_year) AND (a.trea_code = trea_code) AND (a.bill_no = bill_no))
        ->  Index Scan using "final_bill_deductionPK" on final_bill_deduction b  (cost=0.55..0.65 rows=1 width=39)
              Index Cond: ((a.fin_year = fin_year) AND (a.trea_code = trea_code) AND (a.bill_no = bill_no) AND (flag = 'N'::bpchar))

But If I add one more condition at the very end it becomes excruciatingly slow (upto 10 MINUTES at worst).

SELECT
    a.trea_code,
    a.fin_year,
    a.bill_no AS tran_id,
    ((('Transfer FROM ' :: text || (a.ddo_code) :: text) || 'ON Bill No : ' :: text) || btrim(((a.bill_no) :: character(10)) :: text)) AS party_name,
    a.voucher_no,
    a.ddo_code,
    a.accounting_date AS final_date,
    fb.audit_batch_date AS prov_date,
    'D' :: text AS status,
    substr((b.rscheme_code) :: text, 1, 4) AS major_head,
    substr((b.rscheme_code) :: text, 5, 2) AS submajor_head,
    substr((b.rscheme_code) :: text, 7, 3) AS minor_head,
    substr((b.rscheme_code) :: text, 10, 2) AS sub_head,
    substr((b.rscheme_code) :: text, 12, 2) AS subsub_head,
    b.rscheme_code AS scheme_code,
    b.amount,
    'FTC' :: text AS ttype
FROM
    final_bill_master a
    LEFT JOIN final_bill_track fb ON
     a.fin_year = fb.fin_year
    AND a.trea_code = fb.trea_code
    AND a.bill_no = fb.bill_no
    LEFT JOIN final_bill_deduction b
     ON a.trea_code = b.trea_code
    AND a.fin_year = b.fin_year
    AND a.bill_no = b.bill_no
    AND (b.flag = 'N' :: bpchar)
WHERE
    a.ddo_code IN (
        select
        ddo_code
    from
        trea.field_ddo_map
    where
        field_dept_cd = :fieldDepartmentCode
    )
    AND a.fin_year = :financialYear

Here's its explain statement result:

QUERY PLAN
Nested Loop Semi Join  (cost=5.77..39.25 rows=1 width=315)
  Join Filter: (a.ddo_code = field_ddo_map.ddo_code)
  ->  Nested Loop Left Join  (cost=1.41..25.12 rows=1 width=59)
        Join Filter: ((a.fin_year = b.fin_year) AND (a.trea_code = b.trea_code) AND (a.bill_no = b.bill_no))
        ->  Nested Loop Left Join  (cost=0.86..16.91 rows=1 width=37)
              Join Filter: ((a.fin_year = fb.fin_year) AND (a.trea_code = fb.trea_code) AND (a.bill_no = fb.bill_no))
              ->  Index Scan using final_bill_master_pkey on final_bill_master a  (cost=0.43..8.45 rows=1 width=33)
                    Index Cond: (fin_year = '2022-2023'::bpchar)
              ->  Index Scan using final_bill_track_pkey on final_bill_track fb  (cost=0.43..8.45 rows=1 width=21)
                    Index Cond: (fin_year = '2022-2023'::bpchar)
        ->  Index Scan using "final_bill_deductionPK" on final_bill_deduction b  (cost=0.55..8.19 rows=1 width=39)
              Index Cond: ((fin_year = '2022-2023'::bpchar) AND (flag = 'N'::bpchar))
  ->  Bitmap Heap Scan on field_ddo_map  (cost=4.36..13.96 rows=10 width=8)
        Recheck Cond: (field_dept_cd = 'TAX'::bpchar)
        ->  Bitmap Index Scan on field_ddo_map_pk  (cost=0.00..4.35 rows=10 width=0)
              Index Cond: (field_dept_cd = 'TAX'::bpchar)

Somebody please tell me what is causing this and how can I remedy it?

EDIT: Added EXPLAIN(ANALYZE, BUFFERS) statement results Without the additional condition:

QUERY PLAN
Gather  (cost=1015.19..276486.57 rows=300311 width=315) (actual time=2.242..667.881 rows=28992 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=231819 read=66389
  ->  Nested Loop Left Join  (cost=15.19..245455.47 rows=125130 width=315) (actual time=0.692..641.730 rows=9664 loops=3)
        Buffers: shared hit=231819 read=66389
        ->  Nested Loop Left Join  (cost=14.64..157250.43 rows=125130 width=37) (actual time=0.612..510.599 rows=9377 loops=3)
              Buffers: shared hit=113925 read=66389
              ->  Hash Join  (cost=14.21..82706.60 rows=125130 width=33) (actual time=0.545..377.093 rows=9377 loops=3)
                    Hash Cond: (a.ddo_code = field_ddo_map.ddo_code)
                    Buffers: shared hit=1269 read=66389
                    ->  Parallel Seq Scan on final_bill_master a  (cost=0.00..78452.37 rows=1084937 width=33) (actual time=0.050..200.565 rows=867000 loops=3)
                          Buffers: shared hit=1214 read=66389
                    ->  Hash  (cost=14.08..14.08 rows=10 width=8) (actual time=0.134..0.136 rows=6 loops=3)
                          Buckets: 1024  Batches: 1  Memory Usage: 9kB
                          Buffers: shared hit=23
                          ->  HashAggregate  (cost=13.98..14.08 rows=10 width=8) (actual time=0.125..0.128 rows=6 loops=3)
                                Group Key: field_ddo_map.ddo_code
                                Buffers: shared hit=23
                                ->  Bitmap Heap Scan on field_ddo_map  (cost=4.36..13.96 rows=10 width=8) (actual time=0.078..0.113 rows=10 loops=3)
                                      Recheck Cond: (field_dept_cd = 'TAX'::bpchar)
                                      Heap Blocks: exact=5
                                      Buffers: shared hit=23
                                      ->  Bitmap Index Scan on field_ddo_map_pk  (cost=0.00..4.35 rows=10 width=0) (actual time=0.057..0.057 rows=10 loops=3)
                                            Index Cond: (field_dept_cd = 'TAX'::bpchar)
                                            Buffers: shared hit=8
              ->  Index Scan using final_bill_track_pkey on final_bill_track fb  (cost=0.43..0.60 rows=1 width=21) (actual time=0.012..0.012 rows=1 loops=28131)
                    Index Cond: ((a.fin_year = fin_year) AND (a.trea_code = trea_code) AND (a.bill_no = bill_no))
                    Buffers: shared hit=112656
        ->  Index Scan using "final_bill_deductionPK" on final_bill_deduction b  (cost=0.55..0.65 rows=1 width=39) (actual time=0.011..0.011 rows=0 loops=28131)
              Index Cond: ((a.fin_year = fin_year) AND (a.trea_code = trea_code) AND (a.bill_no = bill_no) AND (flag = 'N'::bpchar))
              Buffers: shared hit=117894
Planning Time: 2.794 ms
Execution Time: 670.567 ms

With the additional condition:

QUERY PLAN
Nested Loop Semi Join  (cost=5.77..39.25 rows=1 width=315) (actual time=4411.875..167336.582 rows=178 loops=1)
  Join Filter: (a.ddo_code = field_ddo_map.ddo_code)
  Rows Removed by Join Filter: 191568
  Buffers: shared hit=153665416 read=59 dirtied=12
  ->  Nested Loop Left Join  (cost=1.41..25.12 rows=1 width=59) (actual time=15.189..167037.964 rows=19277 loops=1)
        Join Filter: ((a.fin_year = b.fin_year) AND (a.trea_code = b.trea_code) AND (a.bill_no = b.bill_no))
        Rows Removed by Join Filter: 150227132
        Buffers: shared hit=153531047 read=59 dirtied=12
        ->  Nested Loop Left Join  (cost=0.86..16.91 rows=1 width=37) (actual time=0.111..73048.167 rows=17117 loops=1)
              Join Filter: ((a.fin_year = fb.fin_year) AND (a.trea_code = fb.trea_code) AND (a.bill_no = fb.bill_no))
              Rows Removed by Join Filter: 146487286
              Buffers: shared hit=85028813 read=59 dirtied=12
              ->  Index Scan using final_bill_master_pkey on final_bill_master a  (cost=0.43..8.45 rows=1 width=33) (actual time=0.057..107.777 rows=17117 loops=1)
                    Index Cond: (fin_year = '2022-2023'::bpchar)
                    Buffers: shared hit=9404 read=36 dirtied=5
              ->  Index Scan using final_bill_track_pkey on final_bill_track fb  (cost=0.43..8.45 rows=1 width=21) (actual time=0.009..2.673 rows=8559 loops=17117)
                    Index Cond: (fin_year = '2022-2023'::bpchar)
                    Buffers: shared hit=85019392 read=23 dirtied=2
        ->  Index Scan using "final_bill_deductionPK" on final_bill_deduction b  (cost=0.55..8.19 rows=1 width=39) (actual time=0.013..3.973 rows=8777 loops=17117)
              Index Cond: ((fin_year = '2022-2023'::bpchar) AND (flag = 'N'::bpchar))
              Buffers: shared hit=68502234
  ->  Bitmap Heap Scan on field_ddo_map  (cost=4.36..13.96 rows=10 width=8) (actual time=0.008..0.011 rows=10 loops=19277)
        Recheck Cond: (field_dept_cd = 'TAX'::bpchar)
        Heap Blocks: exact=95815
        Buffers: shared hit=134369
        ->  Bitmap Index Scan on field_ddo_map_pk  (cost=0.00..4.35 rows=10 width=0) (actual time=0.005..0.005 rows=10 loops=19277)
              Index Cond: (field_dept_cd = 'TAX'::bpchar)
              Buffers: shared hit=38554
Planning Time: 2.582 ms
Execution Time: 167336.873 ms

Usually in a 1 to 1 releationship two tables are related. But today I saw a database in which one table is related to two other tables, something like this:

Example data:

media: 
id  | item_id | type  | filename

100 | 200     | video | video200.mp4

101 | 201     | video | video201.mp4

201 | 501     | image | awesome_photograph.png

202 | 502     | image | awesome_photoshoot.png

203 | 503     | image | awesome_icon.gif
video:
id  | duration

200 | 10.026666

201 | 32.00
image:
id  | bitdepth

500 | 24

501 | 24

502 | 24

503 | 8

Personally, I think this is a very strange database design because of this:

• the column type is holding 'duplicate' data for example 'image' is stored 3 times. Maybe it would be better to introduce a mediaType table, but that might have been an overkill.

However, I can see one advantage:

• the table media acts like a parent to both video and image

Questions:

Is it ok to design it like this or is this completely wrong?

Is it true that when the sql service account is changed via the config manager then the new account can decrypt the SMK.

However if the change was made via the services.msc, then the new account doesn't have access to the SMK?

And that in the later case - sensitive data that is encrypted by SMK still continues to work because the computer account is able to access the SMK always. However when such a master db is moved to another server then and assuming that server has the sql service account different to the account that can decrypt the SMK then the linked server, credentials will not work?

How can I test whether a service account can access the SMK or not?

When migrating SQL server (same version) to new hardware, what are both options feasible? (that is restore of master/msdb or scripting)?

As already stated on one of your questions, if the version and CU level remain "exactly" same there would be no problem in restoring system databases. I would prefer restore than scripting out.

MySQL has https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_slow_extra which includes some session status variables per https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html , however its not equivalent to an https://dev.mysql.com/doc/refman/8.0/en/explain.html .

If you are using MariaDB then https://mariadb.com/kb/en/explain-in-the-slow-query-log/ is possible with https://mariadb.com/kb/en/server-system-variables/#log_slow_verbosity

mysqld --help --verbose

At about line 13, it will say where the config file is. It is probably not spelled the way you spelled it. Mine says

...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
...

I can't help you finding a better way to query the details from the first version of the record. But if it this query is going to be used a lot, maybe you should index the history table to get better performance, because by default the history table comes with a clustered index on (SysEndTime, SysStartTime), so expect a lot of information being read to get the results.

Maybe this could help you:

create index IX_EmployeeId_SysStartTime on Employee_History (
    EmployeeId ASC,
    SysStartTime ASC
)
INCLUDE (
    EditUserId
)

I can think of other ways to get the date of the record's first row on history, but just the date, not the full row. If this is enough for you, try these to see how they behave over your data:

--
select e.*, created.minSysStartTime
from Employee e
left join (
    select EmployeeId, min(SysStartTime) as minSysStartTime
    from Employee_History 
    group by EmployeeId
) created on e.EmployeeId = created.EmployeeId
--
select *
from Employee e
outer apply (
    select min(SysStartTime) as minSysStartTime
    from Employee_History c
    where e.EmployeeId = c.EmployeeId
) created

And these two queries (in my tests) only make a different execution plan when using that index I've mentioned first, otherwise they behave the same.