Extract and combine multiple values from a jsonb column

jsonb_path_query() is a set-returning function. When putting more than one of those in a SELECT list, this is the expected behavior. See:

What is the expected behavior for multiple set-returning functions in SELECT clause?

You seem to be looking for a CROSS JOIN instead:

SELECT *
FROM       (SELECT jsonb_path_query(test, '$.title'::jsonpath) AS title FROM jsonb_test) a
CROSS JOIN (SELECT jsonb_path_query(test, '$.tags.tag'::jsonpath) AS tag FROM jsonb_test) b

Alternatively, and probably more elegantly, use two LATERAL joins:

SELECT a.title, b.tag
FROM   jsonb_test j
     , jsonb_path_query(j.test, '$.title'::jsonpath) a(title)
     , jsonb_path_query(j.test, '$.tags.tag'::jsonpath) b(tag);

db<>fiddle here

What is the difference between LATERAL and a subquery in PostgreSQL?

Effectively, a "proxy cross join". See:

Understanding multiple table join with aggregation


How to Determine Chunk Distribution (data and number of docs) in a Sharded MongoDB Cluster?

There is currently no built-in way to do this, so a small function is needed.  For the purposes of this answer I have created a 2 shard cluster with ~1 million documents as per these instructions.  Next up I used this function to examine those documents:

AllChunkInfo = function(ns, est){
    var chunks = db.getSiblingDB("config").chunks.find({"ns" : ns}).sort({min:1}); //this will return all chunks for the ns ordered by min
    //some counters for overall stats at the end
    var totalChunks = 0;
    var totalSize = 0;
    var totalEmpty = 0;
    print("ChunkID,Shard,ChunkSize,ObjectsInChunk"); // header row
    // iterate over all the chunks, print out info for each
    chunks.forEach(
        function printChunkInfo(chunk) {

        var db1 = db.getSiblingDB(chunk.ns.split(".")[0]); // get the database we will be running the command against later
        var key = db.getSiblingDB("config").collections.findOne({_id:chunk.ns}).key; // will need this for the dataSize call
        // dataSize returns the info we need on the data, but using the estimate option to use counts is less intensive
        var dataSizeResult = db1.runCommand({datasize:chunk.ns, keyPattern:key, min:chunk.min, max:chunk.max, estimate:est});
        // printjson(dataSizeResult); // uncomment to see how long it takes to run and status
        print(chunk._id+","+chunk.shard+","+dataSizeResult.size+","+dataSizeResult.numObjects);
        totalSize += dataSizeResult.size;
        totalChunks++;
        if (dataSizeResult.size == 0) { totalEmpty++ }; //count empty chunks for summary
        }
    )
    print("***********Summary Chunk Information***********");
    print("Total Chunks: "+totalChunks);
    print("Average Chunk Size (bytes): "+(totalSize/totalChunks));
    print("Empty Chunks: "+totalEmpty);
    print("Average Chunk Size (non-empty): "+(totalSize/(totalChunks-totalEmpty)));
}

It's pretty basic at the moment, but it does the job.  I have also added it on github and may expand it further there.  For now though, it will do what is needed.  On the test data set described at the start, the output looks like this (some data removed for brevity):

mongos> AllChunkInfo("chunkTest.foo", true);
ChunkID,Shard,ChunkSize,ObjectsInChunk
chunkTest.foo-_id_MinKey,shard0000,0,0
chunkTest.foo-_id_0.0,shard0000,599592,10707
chunkTest.foo-_id_10707.0,shard0000,1147832,20497
chunkTest.foo-_id_31204.0,shard0000,771568,13778
chunkTest.foo-_id_44982.0,shard0000,771624,13779
// omitted some data for brevity
chunkTest.foo-_id_940816.0,shard0000,1134224,20254
chunkTest.foo-_id_961070.0,shard0000,1145032,20447
chunkTest.foo-_id_981517.0,shard0000,1035104,18484
***********Summary Chunk Information***********
Total Chunks: 41
Average Chunk Size (bytes): 1365855.024390244
Empty Chunks: 1
Average Chunk Size (non-empty): 1400001.4

To explain the arguments passed to the function:

The first argument is the namespace to examine (a string), and the second (a boolean) is whether or not to use the estimate option or not.  For any production environment it is recommended to use estimate:true - if it is not used, the all the data will need to be examined, and that means pulling it into memory, which will be expensive.

While the estimate:true version is not free (it uses counts and average object sizes), it is at least reasonable to run even on a large data set.  The estimate version can also be a little off if object size is skewed on some shards and hence the average size is not representative (this is generally pretty rare).


Multiple PostgreSQL instances for one PGDATA folder

No, that is impossible. PostgreSQL protects against that as good as it can, but if you manage to start a second postmaster, instant data corruption will ensue.

This question makes me wonder why you want that:

It cannot be scalability, because you have I/O as a bottleneck.
It cannot be high availability, because the shared storage is a single point of failure.

Such architectures like Oracle RAC are ill-conceived and mostly useless; nobody would design such a system these days.

The way to go is a shared-nothing cluster like patroni.


Postgresql database backup (and restore) from two servers to third storage place

I would run the backup jobs on A and B at specified times using the local native pg_dump, then push them to C for storage.

sudo su -c 'pg_dump -f ~/backupfile.sql -C "MyDB"' myuserid
scp ~/backupfile.sql myuserid@cmachine.mydomain.com:

Restoring from the backup file can be done with:

psql -d <database> -f backup-file.sql


Postgres: How is SET NOT NULL "more efficient" than CHECK constraint

My wild guess: "more efficient" means "less time is required to perform the check" (time advantage). It may also mean "less memory is required to perform the check" (space advantage). It might also mean "has less side effects" (such as not locking something or locking it for shorter periods of time)... but I don't have a way to know or check that "extra advantage".

I cannot think of an easy way to check for a possible space advantage (which, I guess, is not that important when memory nowadays is cheap). On the other hand, it's not that difficult to check for the possible time advantage: just create two tables which are the same, with the only exception of the constraint. Insert a sufficiently large number of rows, repeat a few times, and check the timings.

This is the table setup:

CREATE TABLE t1
(
   id serial PRIMARY KEY,
   value integer NOT NULL
) ;

CREATE TABLE t2
(
  id serial PRIMARY KEY,
  value integer
) ;

ALTER TABLE t2
  ADD CONSTRAINT explicit_check_not_null
  CHECK (value IS NOT NULL);

This is an extra table, used for storing timings:

CREATE TABLE timings
(
   test_number integer,
   table_tested integer /* 1 or 2 */,
   start_time timestamp without time zone,
   end_time timestamp without time zone,
   PRIMARY KEY(test_number, table_tested)
) ;

And this is the test performed, using pgAdmin III, and the pgScript feature.

declare @trial_number;
set @trial_number = 0;

BEGIN TRANSACTION;
while @trial_number <= 100
begin
    -- TEST FOR TABLE t1
    -- Insert start time
    INSERT INTO timings(test_number, table_tested, start_time)
    VALUES (@trial_number, 1, clock_timestamp());

    -- Do the trial
    INSERT INTO t1(value)
    SELECT 1.0
      FROM generate_series(1, 200000) ;

    -- Insert end time
    UPDATE timings
       SET end_time=clock_timestamp()
     WHERE test_number=@trial_number and table_tested = 1;

    -- TEST FOR TABLE t2
    -- Insert start time
    INSERT INTO timings(test_number, table_tested, start_time)
    VALUES (@trial_number, 2, clock_timestamp());

        -- Do the trial
    INSERT INTO t2(value)
    SELECT 1.0
    FROM generate_series(1, 200000) ;

    -- Insert end time
    UPDATE timings
       SET end_time=clock_timestamp()
     WHERE test_number=@trial_number and table_tested = 2;

    -- Increase loop counter
    set @trial_number = @trial_number + 1;
end
COMMIT TRANSACTION;

The result is summarised in the following query:

SELECT
    table_tested,
    sum(delta_time),
    avg(delta_time),
    min(delta_time),
    max(delta_time),
    stddev_pop(delta_time)
FROM
    (
    SELECT
        table_tested, extract(epoch from (end_time - start_time)) AS delta_time
    FROM
        timings
    ) AS delta_times
GROUP BY
    table_tested
ORDER BY
    table_tested ;

With the following results:

table_tested | sum     | min   | max   | avg   | stddev_pop
-------------+---------+-------+-------+-------+-----------
           1 | 176.740 | 1.592 | 2.280 | 1.767 | 0.08913
           2 | 177.548 | 1.593 | 2.289 | 1.775 | 0.09159

A graph of the values shows an important variability:

So, in practice, the CHECK(column IS NOT NULL) is very slightly slower (by a 0.5%). However, this small difference can be due to any random reason, provided that the variability of the timings is far larger than that. So, it's not statistically significant.

From a practical point of view, I would very much ignore the "more efficient" NOT NULL, because I don't really see it's significant; whereas I think that the absence of an AccessExclusiveLock is an advantage.


If the table data means that the datetimes of the trips are consistent with the id order, and trips always start at 35 and end at 38 and vice versa, then you could use the LAG() function, assuming that you are in SQL Server 2012 or later version:

with ins_and_outs as
  ( select zoneid,
           previous_zoneid = lag(zoneid) over (order by id)
    from tablex
  )
select whole_trips = count(*) - 1
from ins_and_outs
where zoneid in (35, 38)
  and (previous_zoneid <> zoneid or previous_zoneid is null) ;

This checks the "previous" zoneid, based on id ordering to see if the zone has changed. You may have additional columns (with date/time data) that would be more accurate to base the order than the (order by id) I used.

Perhaps this variation might be more close to the description of a "trip" (from 35 to 38 and from 38 to 35):

with ins_and_outs as
  ( select zoneid,
           previous_zoneid = lag(zoneid) over (order by id)
    where zoneid in (35, 38)
    from tablex
  )
select whole_trips = count(*) - 1
from ins_and_outs
where (previous_zoneid <> zoneid or previous_zoneid is null) ;

This will give different results, if there are cases, "trips" that start from zone 35, go to other zones and then return to 35 without going through zone 38. Query 1 will count such a trip. Query 2 will not count it.


This what you are looking for?:

SELECT  @rank := @rank + 1 AS 'Rank', province, city, population
    FROM  canada
    JOIN  ( SELECT  @rank := 0 ) AS init
    WHERE  population > 0
    ORDER BY  province, city, population DESC;

+------+----------+---------------+------------+
| Rank | province | city          | population |
+------+----------+---------------+------------+
|    1 | Alberta  | Airdrie       |      24673 |
|    2 | Alberta  | Athabasca     |       2539 |
|    3 | Alberta  | Banff         |       7502 |
|    4 | Alberta  | Barrhead      |       4430 |
|    5 | Alberta  | Bassano       |       1388 |
|    6 | Alberta  | Beaverlodge   |       2219 |
|    7 | Alberta  | Black Diamond |       3663 |
|    8 | Alberta  | Blackfalds    |       3198 |
|    9 | Alberta  | Blairmore     |       2095 |
|   10 | Alberta  | Bon Accord    |       1611 |
|   11 | Alberta  | Bonnyville    |       6003 |
|   12 | Alberta  | Bow Island    |       1792 |
|   13 | Alberta  | Brooks        |      12745 |
|   14 | Alberta  | Calgary       |     968475 |
|   15 | Alberta  | Calmar        |       2000 |
|   16 | Alberta  | Camrose       |      15687 |
...
|  708 | Saskatchewan              | Wilkie                    |       1198 |
|  709 | Saskatchewan              | Wynyard                   |       1793 |
|  710 | Saskatchewan              | Yorkton                   |      15172 |
|  711 | Yukon                     | Haines Junction           |       1148 |
|  712 | Yukon                     | Mayo                      |        540 |
|  713 | Yukon                     | Whitehorse                |      19616 |
+------+---------------------------+---------------------------+------------+

Or you can upgrade to MySQL 8.0 or MariaDB 10.2 to get CTEs and Windowing functions.


Would SQL Server Profile show triggers or other code which run as a side-effect of the query I'm profiling?

That should capture any ad hoc SQL or stored procedures any triggers associated with the table will fire, depending on the filters of your trace (I would be more concerned that it captures too much for you to sift through).

But there is a much cleaner and less invasive way to know if a table has a trigger associated with it:

SELECT [Trigger] = tr.name
FROM sys.triggers AS tr
INNER JOIN sys.tables AS t
ON tr.parent_id = t.[object_id]
WHERE t.name = N'TableName';

There are multiple other reasons the profiler trace is unreliable - a trigger could be currently disabled, for example, or the trigger might only run further SQL statements if certain conditions are true.


How to access NEW or OLD field given only the field's name?

Actually, since NEW is a well defined composite type, you can just access any column with plain and simple attribute notation. SQL itself does not allow dynamic identifiers (table or column names etc.). But you can use dynamic SQL with EXECUTE in a PL/pgSQL function.
Demo
CREATE OR REPLACE FUNCTION trg_demo1()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col_value text;
   _col_name  text := quote_ident(TG_ARGV[0]);  -- escape identifier
BEGIN
   EXECUTE format('SELECT ($1).%s::text', _col_name)
   USING NEW
   INTO  _col_value;

   -- do something with _col_value ...

   RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', _col_name, _col_value;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

The cast to text is optional. Using it, because it works universally. If you know the type, you can work without casting ...
Using format() with %s, because the identifier is already escaped at that point.
Else, use format() with %I to safeguard against SQL injection.
Alternatively, in Postgres 9.3 or later, you can convert NEW to JSON with to_json() and access columns as keys:
CREATE OR REPLACE FUNCTION trg_demo2()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col_value text := to_json(NEW) ->> TG_ARGV[0];  -- no need to escape identifier
BEGIN
   RAISE NOTICE 'It works. The value of NEW.% is >>%<<.', TG_ARGV[0], _col_value;
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Since the column name is not concatenated into an SQL string, SQL injection is not possible, and the name does not need to be escaped.
db<>fiddle here (with EXCEPTION instead of NOTICE to make the effect visible).
Related:

How to set value of composite variable field using dynamic SQL


Autorise computer accounts through AD group

Found it! The issue occured because  we have set the group membership too early (before the computer login once to Active Directory). If we set group membership after a first login it is working as expected


Need to configure Availability Groups between three different SQL failover instance

One of the requirements for AGs is that all instances are participating nodes in the same WSFC.  I believe, based on what youa re telling us, is that the 3 instances are, in fact, in 3 separate clusters.

I don't believe that what you are trying to accomplish is possible.


There should be no problem to start the database. There are however some things to consider:

The databases have both the same name, unique name, and DBID. This can cause problems if you want to back them both up by RMAN.
The configuration of sqlnet will have the name server A. Therefore the database might not be accessible from another server.

So change the DBID and unique name (Check the documentation on these subjects) and check the configuration files of sqlnet (normally in the $ORACLE_HOME/network/admin directory) for references to server A.


SQL server agent unable to establish connection

I have re-installed the sql server 2014 and it fixed this issue. I have realized the sql server native client was not properly installed. Thanks your time


SQL Server - Select last value given a date

You can do this for all databases (even MySQL - which doesn't
have Window functions or CTEs - i.e. WITH clauses).

To solve this, I did the following:

Created a table

CREATE TABLE table1 (Investor_ID int, my_date DATE, type VARCHAR(20), amount int);

Note, never have a column named "date" (or any other SQL keyword).

Populated the table:

INSERT INTO table1 VALUES (1, '2015-03-31', 'CONTRIBUTION'   ,  3);
INSERT INTO table1 VALUES (1, '2015-06-30', 'Balance Update' , 20);
INSERT INTO table1 VALUES (1, '2015-09-30', 'CONTRIBUTION' ,    5);
INSERT INTO table1 VALUES (1, '2015-12-31', 'Balance Update' , 10);
INSERT INTO table1 VALUES (2, '2015-03-31', 'CONTRIBUTION'   ,  8);
INSERT INTO table1 VALUES (2, '2015-06-30', 'Balance Update' , 38);
INSERT INTO table1 VALUES (2, '2015-09-30', 'CONTRIBUTION' ,    1);
INSERT INTO table1 VALUES (2, '2015-12-31', 'Balance Update' , 50);

And then ran the following query:

SELECT t1.my_date, t1.Investor_ID, t1.type, t1.amount
FROM table1 t1
WHERE my_date = (SELECT MAX(my_date) FROM table1 t2 WHERE  t1.Investor_ID = t2.Investor_ID)

And the result was as desired:

my_date   |investor_id|               type|amount
--------------------------------
2015-12-31|1               |Balance Update    |10
2015-12-31|2               |Balance Update    |50

If you want to use Window functions, use this query:

SELECT main.* FROM
(
  SELECT Investor_Id
  ,my_date
  ,type
  ,amount
 ,Row_Number() OVER (PARTITION BY Investor_ID ORDER BY my_date DESC) AS RowNum
 FROM table1
) main
WHERE main.type = 'Balance Update'
AND rownum = 1;


Query goes in Copying to tmp table stauts in MySQL and never returns

(I can't explain why one call was 'fast' and the other was 'slow'.)

The "copying to tmp table" provides virtually no useful info.  It may be saying that it is busy shoveling around more than 50 (re: LIMIT) rows collected from the 4 tables.  My fix, below, mitigates that.

Try the following rewrite as an attempt to speed things up.

First -- Find the ids for "Taylor Swift" songs.  (But would that be in the title??)
Second -- Get the rest of the data and do the JOINs.

The rationale -- Get the complex, slow, parts of the query over with before rummaging around in multiple tables.

SELECT ...
    FROM ( SELECT id FROM tbl_songs
        WHERE ( MATCH (song_title) AGAINST ('taylor swift*' IN BOOLEAN MODE))
          AND (CONCAT(song_title) LIKE '%taylor swift%')
          AND song_status = 1
        ORDER BY s.song_title = 'taylor swift' desc
        LIMIT 50 ) AS s1
    JOIN tbl_songs AS s2  ON s1.id = s2.id
    JOIN ... the-other-tables
        ON ...
    WHERE  ...  the-filtering
    ORDER BY s.song_title = 'taylor swift' desc
    LIMIT 50

Yes, the ORDER BY and LIMIT are needed twice.

The hope is that MATCH is performed first, leading to much fewer rows to apply the LIKE to.  Please provide EXPLAIN SELECT ....

The 1=1 is a common technique for building the WHERE clause.  It is harmless, but it would be better to gather an array of AND clauses and put them together at the end, not building a string as you go.

Check innodb_buffer_pool_size -- is it about 70% of available RAM?


Was SSRS installed in SharePoint Integrated Mode?

Did you try looking in the Reporting Services Configuration on the server in question?

https://social.msdn.microsoft.com/Forums/en-US/a856488b-fbcd-479e-9167-34ddf7ba3e83/how-do-i-find-out-if-ssrs-is-intregrated-or-native?forum=sqlreportingservices


Intermittent Disconnection to DB on SAN. Event ID 17053 and 9001

You need to either have a SAN admin look at the system of bring a consultant in to look at it.  (Yes I'm a SAN consultant, no that's not why I'm saying this.)


requested recovery stop point is before consistent recovery point

You need to start with an earlier base backup than the one you are currently starting from.  PostgreSQL can only recover forward, not backward.


How to check the last time any table in database modified in ms sql server

This should give you the details about when was the last read/write performed on a specific database.
USE server_name;
GO

SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO

WITH agg AS
   (
       SELECT
       last_user_seek,
       last_user_scan,
       last_user_lookup,
       last_user_update
FROM
    sys.dm_db_index_usage_stats
WHERE
    database_id = DB_ID()
        )
           SELECT
             last_read = MAX(last_read),
             last_write = MAX(last_write)
          FROM
      (
SELECT last_user_seek, NULL FROM agg
UNION ALL
SELECT last_user_scan, NULL FROM agg
UNION ALL
SELECT last_user_lookup, NULL FROM agg
UNION ALL
SELECT NULL, last_user_update FROM agg
      ) AS x (last_read, last_write);

Be aware sys.dm_db_index_usage_stats does not return information about memory-optimized indexes.  For information about memory-optimized index use, see sys.dm_db_xtp_index_stats.


Using Oracle XE, how do I create a CASE statement To return a specific date

The following will generate a list showing the current date, and the warning and due dates for the current date.

CREATE TABLE myCal (CAL_Date date,
Bus_Working_Day int)

INSERT INTO myCal
SELECT DATE '2017-06-27', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-28', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-29', 1 FROM DUAL UNION ALL
SELECT DATE '2017-06-30', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-01', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-02', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-03', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-04', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-05', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-06', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-07', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-08', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-09', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-10', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-11', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-12', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-13', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-14', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-15', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-16', 0 FROM DUAL UNION ALL
SELECT DATE '2017-07-17', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-18', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-19', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-20', 1 FROM DUAL UNION ALL
SELECT DATE '2017-07-21', 1 FROM DUAL
;

SELECT cal.CAL_Date
      ,warn.Warning_Date
      ,due.Due_Date
  FROM myCal cal
         LEFT  JOIN (SELECT mn.CAL_Date
                           ,ROW_NUMBER() OVER (PARTITION BY mn.CAL_Date ORDER BY alt.CAL_DATE) as rn
                           ,alt.CAL_Date as Warning_Date
                       FROM myCal mn
                              INNER JOIN myCal alt ON (alt.CAL_Date > mn.CAL_Date)
                      WHERE alt.Bus_Working_Day = 1
                    ) warn ON (cal.CAL_Date = warn.CAL_Date AND warn.rn = 7)
         LEFT  JOIN (SELECT mn.CAL_Date
                           ,ROW_NUMBER() OVER (PARTITION BY mn.CAL_Date ORDER BY alt.CAL_DATE) as rn
                           ,alt.CAL_Date as Due_Date
                       FROM myCal mn
                              INNER JOIN myCal alt ON (alt.CAL_Date > mn.CAL_Date)
                      WHERE alt.Bus_Working_Day = 1
                    ) due ON (cal.CAL_Date = due.CAL_Date AND due.rn = 10)
 ORDER BY CAL_Date
;

dbfiddle here

It requires more than a CASE statement, because you need to evaluate whether each following day is a business day or not to find the days you need.

Here's how it works:
 - join the table against itself (with mn as the first instance and alt as the second);
 - ignore all alt rows where the alt date is less than or equal to the mn date (because the warning and due date will always come after the current date, of course), and where the alt date is not a business working day;
 - for each mn date, number the alt dates in ascending order;
 - take the calendar table, and outer join it to the results of the above twice (joining to the mn date) as warn and due;
 - for the join to warn, take the 7th alt date where the calendar date matches the mn date, for due, the 10th alt date.

You may want to consider (if possible) adding warning_date and due_date to the existing calendar table - you could modify the code above to populate the values, once the base information on the dates is in place. Saves you from joining the calendar table to itself five times to get what you want. (OK, if you have a specific date, then you can hit the warn and due subqueries directly, and don't really have to join back to the calendar table - I did that for demonstration purposes. So, only joining it to itself 4 times).

NOTE: In newer versions of Oracle, where CROSS APPLY and OUTER APPLY are available, you may be able to change these to a more direct (and possibly better performing) query - however, without knowing what version you've got available (and needing to use dbfiddle for my testing, where the version is 11g), I went with this solution.

You should be able to modify this to meet your needs.

UPDATE: You asked what changes would need to be made to use the query in a trigger.

Actually, now that I think about it, I'd be inclined not to use a trigger directly. You see, even if one row is added, all the rows (or, at least, all the rows whose date is, say, within 20 days before any changed date) will need to be updated - which would mean we'd have a trigger that was (technically, at least) recursive.

To avoid that, it might be better to do something somewhat more complicated (of course!).

Create a new table, let's call it FLAG_JOB_TO_RUN, and give it one column, JOB_NAME.
In your trigger, INSERT the value Update_Warn_Due_Dates into FLAG_JOB_TO_RUN.JOB_NAME (if it doesn't already exist).
Schedule a script (or, even better, create a stored procedure and schedule that) to do the following:

Check if there is an 'Update_Warn_Due_Dates' entry in FLAG_JOB_TO_RUN
If there is:

Run the UPDATE version of the SELECT query above to set Warning_Date and Due_Date for all rows in myCal, and
DELETE that row from FLAG_JOB_TO_RUN

Run this job as often as you think is necessary.  It might suffice to run it once a night, and to manually run it right after you know there's been a major change; or, you might need to run it every 5 minutes.


Plaintiff and defendant have the same attributes

As you are managing cases, you may want to ensure you don't double book people.  A person could be a Plaintiff on one case and Defendant on another.  I would have a persons table (legal persons like corporations can be problematic), and use a join table to associate persons with cases.  The join table would have a flag to indicate the role in the case.

This model could be extended to cover judges, lawyers, clerks etc.


Are file locations stored in PostreSQL catalog?

The config file and data directory location cannot be stored in the system catalogs, because the database cannot read the system catalogs until it knows where they are.

It's a bit like putting the map to your buried treasure into your treasure chest.

(Note that pg_settings is not technically a system catalog. It is a view that shows GUCs as configured by postgresql.conf / ALTER SYSTEM ... SET, by ALTER USER ... SET, by ALTER DATABASE ... SET, etc.)


Why index is not used after replacing "WHERE field=x" to "field in (x,y)"?

First, since the table is tiny (only 1 row?), you cannot trust EXPLAIN to give you information that will apply in a 'real' situation.
Indexing a flag is rarely useful.  The Optimizer looks at the situation as says (roughly):  "If I need to scan more than 20% of the table, I won't bother using the index."  This is a reasonable decision because using an index requires bouncing between the index's BTree and the data's BTree.  Whereas, scanning the table sequentially is likely to be more efficient, in spite of having to check and throw out rows that are not needed.
OK, id is not a flag.  But, to the optimizer it may as well be -- since you have only 1 row in your table?


Combining 2 MySQL queries with IN clause does not work

You have basically two problems here,

Your sub-query is returning row-based results and you need comma-separated results.
You could fix this issue by using the GROUP_CONCAT(value SEPARATOR ',') like this:
...r.reactable_id IN
(
select  GROUP_CONCAT(id SEPARATOR ',')
from    (select * from comments...

But you won't see any result due to the next problem...

Different types (reactable_id is an INT value and the GROUP_CONCAT result is a STRING value. So your final solution is to rewrite your query using JOINS instead of sub-queries.
You can see this post to guide your steps:
https://stackoverflow.com/questions/6135376/mysql-select-where-field-in-subquery-extremely-slow-why


SQL Server, connecting without windows authentication?

Windows Authentication is always available, SQL Server Authentication is only available if Mixed Mode is enabled.

  During setup, you must select an authentication mode for the Database Engine. There are two possible modes: Windows Authentication mode and mixed mode.

So:

For an instance using Windows Authentication mode, connections can only be established using Windows Authentication.

For an instance using Mixed Mode, connections may be made using either Windows Authentication or SQL Server Authentication.

In other words, Windows Authentication being always available does not mean it is always used. A company may choose to use only SQL Server Authentication, even if Windows Authentication cannot be disabled (it will remain enabled but unused).


Should a meta_key or other key-value table columns use a HASH or BTREE index?

That's the wrong question.  The indexes that WP provides on the meta tables are inefficient.  (And the general EAV pattern sucks.)
This explains how to significantly improve the indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
Back to your question:  BTree is about as fast as Hash (were it to exist).  And Hash is not available for InnoDB.  (InnoDB is the only 'engine' you should use for WP.)
I believe that the developer of InnoDB saw that "one side does fit all" when he implemented only BTree.


MySQL Slave lag in SHOW SLAVE STATUS does not match SHOW PROCESSLIST

SHOW PROCESSLIST, shows the slave lag under the system_user's time
  column (the one that belongs to the replication sql thread)

No. It's the time that the Slave sql thread has been in its current state.


A better design would be to have a single table to hold all the parties to a payment.  If necessary, sub-type this table so companies, proprietors and customers have their own, unique sets of columns (or a separate, related table).

The payments table will then have two foreign keys to Parties, let's call them PaymentFromPartyID and PaymentToPartyID, along with amount, date etc.  This way all permutations of money transfer can be tracked.

To find all the monies a person has received use

select
    sum(p.Amount)
from Payments as p
inner join Parties as y
    on y.PartyID = p.PaymentToPartyID
where y.Name = 'The person/company/proprietor of interest';

Additional Detail

A party is anyone or anything who has in interest in a payment.  This would be a proprietor, customer or company, distinguished by a type:

Parties(
    PartyID
    PartyName
    PartyType     -- customer or company or proprietor
    <customer-fields>
    <proprietor-fields>
    <company-fields>)

A payment relates a payer (of any type) to a payee (of any type):

Payments(
    PaymentFromPartyID  -- FK to Parties.PartyID
    PaymentToPartyID    -- FK to Parties.PartyID
    Amount
    Date
    etc.)

The IDs used in From and To says in which direction the money flows.  There is no need to have any information about a party in the Payments table, such as a type, other than the two PartyIDs.

To get all the payments made to customers (for example) add Parties.PartyType = 'Customer' to the query given above.

The Payments_Track table you list has all the attributes I list here (bar a few surrogate IDs) but is normalised.


Backing up and restoring all databases in MySQL on linux

The exact syntax used varies depending on what version of MySQL you are using, but the basic version of mysqldump (without any other options) you are using will run:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /;

DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (. . .

You can check this if you are concerned, before running the second statement, simply look in the file dump.sql (it is just a text file) and you can see clearly what queries it will run.

So to answer your questions:

  will the second command overwrite an existing table with the same name as on the first computer ?

Yes it will

  will the second command delete all tables which are NOT there on the first computer?

No it won't

You can get a full list of the options available from: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-option-summary


I guess you want to either insert a new row with the counter status_likes = 1 or incresase it by 1 when a row with status_id already exists. If there is a UNIQUE constraint on member_status_like_count (status_id), you can use INSERT ... ON DUPLICATE KEY UPDATE:

CREATE TRIGGER member_status_likes__AFTER_INSERT__UPSERT__like_count
AFTER INSERT ON member_status_likes
FOR EACH ROW
  INSERT INTO member_status_like_count
      (status_id, status_likes)
  VALUES
      (NEW.status_id, 1)
  ON DUPLICATE KEY UPDATE
      status_likes = status_likes + 1 ;


difference of left join and left outer join

There is no difference, they are absolutely 100% identical in function - the OUTER keyword is optional in the syntax. Personally I prefer LEFT OUTER JOIN as it's a bit more explicit.

You can see more details here:

MSDN : Using Outer Joins

and here:

MSDN : FROM (Transact-SQL)

The relevant bit in the latter document:

LEFT [ OUTER ]

  Specifies that all rows from the left table not meeting the join
  condition are included in the result set, and output columns from the
  other table are set to NULL in addition to all rows returned by the
  inner join

The same description applies whether you use the OUTER keyword or not.


Can I recover a SQL database from Standby/Read-Only without a standby file?

Turns out it can be done if you use CONTINUE_AFTER_ERROR

RESTORE DATABASE foo WITH RECOVERY, CONTINUE_AFTER_ERROR

I did still get a warning when I tried it but then did a CHECKDB and received no errors.

RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 0 pages in 4.180 seconds (0.000 MB/sec).
Msg 3441, Level 17, State 1, Line 13
During startup of warm standby database 'foo' (database ID 46), its standby file ('C:\MSSQL\Backup\foo_standby') was inaccessible to the RESTORE statement. The operating system error was '2(The system cannot find the file specified.)'. Diagnose the operating system error, correct the problem, and retry startup.

Given the warning I'm not 100% certain if I would try it on a production database where I didn't have a good backup.  Given that it's a temp restore it might be worth giving it a shot.


Point PostgreSQL on Ubuntu to a new datadir

You can't just copy base from the old cluster to the new one. It doesn't work like that. You'll be missing the transaction logs, transaction commit statuses, global catalogs, and all the other components that make those databases make any sense.

Instead, pg_createcluster in the new location, then replace the entire data directory with the one from your old system. That's the directory with base, pg_xlog, global, etc in it.


A manual vacuumdb should only be needed for special circumstances, like after running pg_upgrade or after doing some kind of bulk load or major maintenance.  Otherwise, just let autovacuum do its thing.

If your database is mostly idle at night, you can still do vacuumdb and it will concentrate the maintenance into the off hours so that autovacuum is less likely to kick in during the day.  But if you do that, you probably still shouldn't turn off autovacuum.

The vacuums to prevent wrap around are normal for any system that processes hundreds of millions of transactions over its lifetime.  They are nothing to worry about.  It is sometimes nice to know when they are occurring, because anti-wrap around autovacuums will block some kinds of work, such as index builds, whereas regular autovacuums will not.


How to call a windows program in mysql prompt in windows

You cannot run OS commands in a MySQL CLI nor in MySQL Workbench. Why would you do that (except for trying to hack a machine via an SQL connection)? If you need to run an OS command, open a normal shell window (that still stands).

Update:
The system command only works on Unix like OSes (http://dev.mysql.com/doc/refman/5.6/en/mysql-commands.html), e.g. Linux + Mac.

MySQL Workbench doesn't support most of these CLI commands however (use and delimiter are 2 that are supported).


How to remove a listener IP from a SQL Availability Group Listener by T-SQL

Based on my experience with SQL Server 2014 and 2016, you can simply remove the IP address resource in Failover Cluster Administrator.  We have done this on several non-production clusters and on a production cluster in order to move from one subnet to the other.  I stopped the SQL Server service on all nodes to ensure that there would be no connections and less chance of some sort of conflict since it's a bit out of the ordinary.

The other option is to simply take a few minutes of downtime to remove the listener and rebuild it.  If you scripted all of the operations it should only take seconds.


Begin with the question, what is your requirement? based on your question, you need to develope a relational database for managing movies data.

Before starting to build a database, you have to ask a lot of questions to understand about how the movies business work within the requirement scope. Lets start with the question,

Who is important to your movies business?

Based on the question, ACTOR, DIRECTOR.

What is important to movies business?

GENRES, MOVIE.

Now, the next question will be what is the definition of ACTOR, DIRECTOR, GENRES, MOVIES?

For example, An ACTOR is a human being, who acts in atleast one movie. Note that you should have a clear definition of entity (business concept) or else it will lead to multiple interpretation. (see here).

After understanding the business concepts, let us go for business rules. Some simple questions (based on your question).

Can an Actor act in more than one Film?
Can a Movie coontains more than one Actor?
Can a Movie directed by more than one Director?
Can a Movie categorized as more than one Genre?

Example business rule from your question.

Each Actor can act in one or many Actor Movie.
Each Actor Movie must be acted by one Actor.
Each Movie can contain one or many Actor Movie.
Each Actor Movie must belongs to one Movie.
Each Directos can direct one or many Director Movie.
Each Director Movie must be directed by one Director.
Each Movie can contain one or many Director Movie.
Each Director Movie must belongs to one Movie.
Each Genres can contain one or many Genres Movie.
Each Genres Movie must belongs to one Genres.
Each Movie can contain one or many Genre Movie.
Each Genre Movie must belongs to one Movie.

By applying these business rules, our new model may look like as follows:

This model is not normalized, after applying normalization (see here) process, you will have clear and detailed data model for your application.

I hope this model will help you to begin your assignment. Thank you.


Ignore that wait type CLR_semaphore - its a benign wait type to be filtered out as per Wait statistics, or please tell me where it hurts


How do I create databases / logins / users with minimal privileges when installing an application?

If the DBA doesn't want to grant sysadmin or securityadmin role memberships (needed to create logins), have the DBA create those logins prior to installation and add MyAdmin to the dbcreator fixed server role.  The dbcreator role membership will provide the permissions to create, alter, and drop any database. No server-level permissions need be granted to MyUser before installation since the login will have CONNECT by default.

The MyAdmin account will own any databases created or restored under that security context. As the database owner, the account will be mapped to the dbo user and have full database permissions for administrative functions, including creating users and modifying schema. This account should be used only by the installer.  Routine database access should be done as MyUser with only the needed permissions granted.

--A sysadmin or securityadmin role member creates logins
--before instalation and adds MyAdmin login to dbcreator role.
CREATE LOGIN MyAdmin WITH PASSWORD='$&Sl~dg$30=asz';
CREATE LOGIN MyUser WITH PASSWORD='MM20&%93.f04}y';
ALTER SERVER ROLE dbcreator
    ADD MEMBER MyAdmin;
GO

--Installer connects with MyAdmin login.
--Database will be owned by login running RESTORE (MyAdmin)
RESTORE DATABASE ApplicationDatabase
FROM DISK='D:\SqlBackups\ApplicationDatabase.bak'
WITH
      MOVE 'ApplicationDatabase' TO 'D:\SqlDataFiles\ApplicationDatabase.mdf'
    , MOVE 'ApplicationDatabase_log' TO 'L:\SqlLogFiles\ApplicationDatabase.ldf'
    , STATS=5, REPLACE;
GO
--Database owner will access resotred database as dbo user.
USE ApplicationDatabase;
GO
--dbo user can create user mapped to SQL login MyUser.
CREATE USER MyUser;
GO


Your 2 queries (and tables?) don't exactly match.

The sql server 2008 compatibility level query has 143 columns returned while
the sql server 2012 compatibility level query  has 146 columns returned.

Please validate any other differences in these tables.

  Why Index Scan qualify 1.1 Million rows in SQL 2008 vs 8.7 Million
  rows in SQL 2012?

Your index scan is returning rows, ordered by Object2.Column2
to then push their bookmark

(bookmark=column144 / Column147 in your anonomized plans)

to the RID lookup and filter on Object2.Column1.

Initial guess: Ties?

This leads me to believe that Object2.Column2 has ties, resulting in different rows to be read first.

Both are executing ordered prefetching

  <NestedLoops Optimized="0" WithOrderedPrefetch="1">

  ... An ordered prefetch preserves key order on the inner side of the
  nested loops join,  so SQL Server ensures that asynchronous I/Os are
  processed in the correct order. ... Our test query features an ORDER BY
  clause that the optimizer can avoid sorting for by using ordered
  prefetch...

Source

I might be wrong, but what I take from this is that, as long as the ordering is preserved, different rows could be fed to the RID lookup operator, supporting the ties claim.

You could try running the query with a unique order and adding these query plans to the question.

  While both compatibility level is prior to SQL Server 2014 version why
  EstimatedRowsWithoutRowGoal (I understand you cannot see it in 2008)
  is different?

We would need detailed statistic information here. The statistics show different estimates between the tables, resulting in a difference in EstimatedRowsWithoutRowGoal

You could try running a full scan update before running both queries while selecting the same amount of columns.

Testing

When doing tests between sql server instances (2008) & (2017 with compat 2012)

I keep on getting the same amount of rows returned:

2008 (compatibility):

Query plan

2012 (compatibility):

Query Plan

Even when changing Column2 to hold many duplicates, the estimates & actuals remain the same.

UPDATE dbo.Object1
SET Object1.Column2
= Object1.Column2 % 400;

As expected, estimates remain the same when adding the extra columns to the table in the compat 2012 db

ALTER TABLE dbo.Object1
ADD ExtraColumn1 int default(1) with values;
ALTER TABLE dbo.Object1
ADD ExtraColumn2 int default(2) with values;
ALTER TABLE dbo.Object1
ADD ExtraColumn3 int default(3) with values;

Same story with enabling the traceflags

Conclusion

This kind of rules out my previous statement about ties in your data, but also shows that I cannot replicate your problem with my dataset (much smaller).

Even if I set the entire column2 to the same value, there is 0 difference in the amount of rows returned. Data differences seem like what should cause this.

Dataset used

CREATE DATABASE TEST
GO
USE [master]
GO
ALTER DATABASE [TEST] MODIFY FILE ( NAME = N'TEST', FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [TEST] MODIFY FILE ( NAME = N'TEST_log', FILEGROWTH = 1048576KB )
GO
alter database test set recovery simple
GO
backup database test to disk = 'NUL'

GO

use TEST
go

CREATE TABLE dbo.Object1(
Column1 int,
Column2 int,
Column3 int,
Column4 int,
Column5 int,
Column6 int,
Column7 int,
Column8 int,
Column9 int,
Column10 int,
Column11 int,
Column12 int,
Column13 int,
Column14 int,
Column15 int,
Column16 int,
Column17 int,
Column18 int,
Column19 int,
Column20 int,
column1_varchar varchar(20));

CREATE INDEX Index1
on dbo.Object1(column15,Column16,Column17)
 CREATE INDEX Index2
on dbo.Object1(Column2,Column10,Column19)

SELECT top(1000000)  --1M
row_number() over(order by (select null)) as rownum
INTO #temp
FROM master..spt_values spt1
cross apply master..spt_values spt2
cross apply master..spt_values spt3

INSERT INTO dbo.Object1
WITH(TABLOCK)
SELECT
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
rownum,
'ABC'+ CAST(rownum as varchar(20))
FROM #temp
GO 3


Can Entity Framework access In Memory OLTP Tables?

I created a small test application to see if there were any issues accessing IMOLTP tables from Entity Framework.  My test application uses the latest stable version of Entity Framework (EF6).

With the default settings, I got this error initially trying to insert a row into a memory-optimized table:

  SqlException: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. Provide a supported isolation level for the memory optimized table using a table hint, such as WITH (SNAPSHOT).

This is because Entity Framework creates an explicit transaction automatically when using the "SaveChanges()" method (the most common approach for inserting rows):

  In all versions of Entity Framework, whenever you execute SaveChanges() to insert, update or delete on the database the framework will wrap that operation in a transaction.

The simplest way to work around this in the case of EF is to turn on the database-level option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, which essentially adds the needed WITH (SNAPSHOT) hint automatically:

ALTER DATABASE [YourDatabaseName] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;

Other than that, there seems to be no issues with accessing the in-memory tables.  This isn't all that surprising, since under the covers Entity Framework is using the .NET Framework Data Provider for SQL Server (SqlClient), which has no documented limitations when it comes to in-memory tables.


To drop a table, do I have to drop all foreign key constraints or can I just NOCHECK them?

SQL Server prevents you from dropping the table if a foreign key exists, nocheck or not:

create table t1
(id int not null, id2 int null)

create table t2
(id int primary key)

insert into t2 values (1)

insert into t1 values (1,1)

alter table t1 add constraint fk1 foreign key (id2) references t2(id)

alter table t1 nocheck constraint all

-- add a value to confirm nocheck working as expected
insert into t1 values (2,2)

-- lets clear out data from t1
truncate table t1

--alter table t1 drop constraint fk1

drop table t2

Gives you: Could not drop object 't2' because it is referenced by a FOREIGN KEY constraint.

So it's a moot point, really, you'll have to drop the FK first, either way.


How do I create a table with count of instances of an id before an event date that is individual to that id?

Would something like this do?

with only_returned as
  (select cust_id,
          min(order_date) min_order_date
   from your_table
   where returned = 1
   group by cust_id
  )
select r.cust_id,
       count(b.order_id) returned_before,
       count(a.order_id) returned_after
from only_returned r
  join your_table b on b.cust_id = r.cust_id
                   and b.order_date < r.min_order_date
  join your_table a on a.cust_id = r.cust_id
                   and a.order_date > r.min_order_date;

ONLY_RETURNED CTE contains customers whose orders were returned; it also selects the first return order date
then, join only_returned with your_table, twice:

once for orders placed before minimum return order date (the b alias)
once for orders placed after minimum return order date (the a alias)


A natural key (AKA business key or domain key) is a key that is used to identify information in the business domain.

A surrogate is a key that is "unseen", internal to the database, meaningless and not used in the business domain.

The distinction that really matters is whether the attribute in question is exposed to users / consumers and therefore serves some function and acquires meaning as part of a real world process.

It seems that there is already too much loose terminology and folklore associated with keys. I don't think it would be helpful to have another term for the kind of key you are referring to here.


Distribute data into x groups of at-most n records each

From the example code it seems that you want to transfer all outstanding rows, or none. This can be done in a couple of statements, though there are good reasons not to. More on that later.

If there's a foreign key in the detail table referencing the header table that must be honoured or worked around. Either the header table must be populated first, then the detail table, or the foreign key must be disabled, then re-enabled once the data is in place. Either way some data will be read twice.

I'll not use the temporary table (#TotalCID) either, saving the read and write this consumes. Instead I'll create a view on the source table (ConsumerTable). Even though there are a lot more rows in the base table compared to the temporary table (twenty million versus one hundred thousand) you have to read the base table at some point to find the rows to transfer. I believe a filtered index will be useful here. If there are concurrent writes to the base table they can be avoided by using a "high water mark" if there's an identity column or a suitable isolation level. It's OK to retain #TotalCID if you're more comfortable with that approach.

First I will show all the code, then I'll explain my thinking.

drop table if exists ConsumerTable;
drop table if exists ETLDetail;
drop table if exists ETLHeader;
go

create table ConsumerTable
(
    ConsumerID      varchar(16) primary key,    -- Note A
    SeedingStatus   int         default (0)     -- Note B
);

go
-- Note C
create index ToTransfer on ConsumerTable (ConsumerID)
where SeedingStatus in (0,2);
go

insert ConsumerTable(ConsumerID)
values ('a'), ('b'), ('c'), ('d'), ('e'),
       ('f'), ('g'), ('h'), ('i'), ('j');
go

create table.ETLHeader
(
    HeaderId        int     primary key,
    Batch_Date      date    not null,
    Total_Rec_count int     not null
);

create table ETLDetail
(
    Batch_ID    int not null,
    ConsumerID  int not null
);

go
create or alter view ConsumersToTransfer as
with DataRows as
(
    select
        ConsumerID,
        RowId       = ROW_NUMBER() over (order by ConsumerID)   -- Note D, E
    from ConsumerTable
    where SeedingStatus in (0, 2)                               -- Note F
)
select
    ConsumerID,
    RowId,
    BatchId = (RowId - 1) / (select BatchSize = 4)              -- Note G
from DataRows;
go

Notes

A For testing I'll make this the PK. It doesn't have to be for this method to work.

B The default is a convenience for my test data. It is not required for this solution.

C I think this filtered index will be useful, but you must have code to find these rows efficiently already. Continue to use that.

D To pre-calculate BatchID I will use integer division. From the docs

  If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

For that I'll need a unique, gap-less sequence of integers, one for each source row. I'll use ROW_NUMBER() to build this. If your table has an identity column which provides the same conditions it can be used instead. Note that identities can leave gaps on occasion, or rows may have been deleted. If so the batch sizes will not be uniform.

E The OVER() clause is requried. We don't care which rows are together in a batch, however. So use the clustering index's leading column to avoid a sort. If you have a need to keep some rows near others you can change this clause, though there'll likely be a performance hit.

F The predicate matches that of the filtered index defined above.

G I'll use a batch size of 4 for testing. Change this to your production value (25,000) for your tests. I subtract 1 from RowId because ROW_NUMBER() is 1-based and I want the Nth row to end a batch rather than start a new batch.

Here is the code to transfer rows:

begin transaction;

    declare @NextBatchStart int
              = 1 + (select ISNULL(MAX(HeaderId), 0) from ETLHeader);       -- Note H

    insert ETLHeader                    -- Note I
    (
        HeaderId,
        Batch_Date,
        Total_Rec_count
    )
    select
        BatchId + @NextBatchStart,
        Batch_Date = GETDATE(),
        COUNT(*)
    from ConsumersToTransfer
    group by BatchId;

    insert ETLDetail                    -- Note J
    (
        Batch_ID,
        ConsumerID
    )
    select
        BatchId + @NextBatchStart,
        ConsumerId
    from ConsumersToTransfer;

    update ct
    set SeedingStatus = 1
    from ConsumerTable as ct
    inner join ETLDetail as d                   -- Note K
        on d.ConsumerId = ct.ConsumerId
        and d.Batch_ID >= @NextBatchStart       -- Note L
    where ct.SeedingStatus in (0, 2);           -- Note M

commit;

Notes

H This plays the role of your SCOPE_IDENTITY() ensuring new header rows follow on from existing rows.

I Each INSERT will read all source rows. Since this is will be in clustered key sequence (because of the OVER() clause) it should be fast.

J If there are concurrent writes to ConsumerTable these two statements could return different results and, hence, different batches. Specifically Total_Rec_count will be off for the last (non-full) batch.

K An inner join is logically equivalent to your .. IN (select ..).

L Only need to consider batches created in the current run.

M Predicate matches the filtered index.

Wrap this in the error-catching and rollback logic to handle your desired all-or-nothing outcome.

I think part of the reason your current approach takes so long is because the system does not use IX_ConsumerID because the leading column (IsPickedUp) is not selective enough. I touch on this for another question here.

I understand you want all-or-nothing outcomes. It is obvious your hardware can handle this because you have written code that runs. However it takes a toll on the system. All those rows written have to be held in the transaction logs. The logs must grow to accommodate the rows and cannot be truncated until the transaction completes. The system must hold locks, preventing other work from continuing.

A better way may be to process each batch in a transaction. Fewer resources will be needed for each batch compared to the whole. Locks will be released sooner. Restart logic will ensure no row is processed twice or missed.


Database mirroring stuck in suspended mode

First thing to try:
ALTER DATABASE Gandalf SET PARTNER RESUME;

Might work!  If you prefer you can do it with the GUI - Tasks / Mirror / Resume
Check logs for an Error 3624.
I get this occasionally - reading up on this, it looks very much like "an error has occurred."
My solution is to stop mirroring and recreate.
I would be interested if anyone has better information.


When does SSIS SQL Server Configuration write to the configuration table?

The only actions that will cause the values in your configuration table to be updated within SSIS will be when you are in the Configuration editor menu. Clicking OK there will cause the values to be written to the table.

Now, if you have an Execute SQL Task that has UPDATE statement in it, that would obviously change the values in the table but those would only become effective for the next run. Once a package is actually running, the configuration table could be dropped as it doesn't periodically poll the table to investigate values.

If you modify the value of a Connection Manager in your package, assuming that CM is also under Configuration, the next time the package attempts to validate itself at the package level, it'll reach out to the configuration resource (table, XML, etc) and overlay with the configured values. I think I remember that if you are just right clicking and executing a task within a package, it doesn't make the hop but don't quote me on that.


That's a bit insane to capture all running queries no? Queries from a specific user or including the ones from sproc, ETL, etc? If they are coming from an unauthorized location, that tells me that your DB is not locked down enough. If one instance has 20 DBs, each of the service accounts, domain users or even SQL login should only been given access to the database pertain to them. The people who are the owner of these accounts (who knows the password) are usually gone trusted and have gone through background checks.

Otherwise, they are 3rd party SQl tools that like idera dm , SQL Sentry and even pefromance dashboard that comes with SSMS 2016 that keep track of queries applied to the database.


mongoDB: querying for a document that has an object reference

To query two collections with related data the common approach (as at MongoDB 3.0) is to find a list of matching _id values from the first collection which can used with a $in query on the second.

Here's an example using the mongo shell:

// Find _ids for matching locations based on some criteria (eg. "abbrev")
var matches = db.locations.find({ "abbrev": "CAN"}, { _id: 1 })

// Transform matching _id values into an array
var locs = [];
matches.forEach(function(match) { locs.push(match._id) });

// Look for widgets matching the location criteria
db.widgets.find({"loc": {$in : locs} } )

Depending on the driver and/or framework you are using, there may be helper functions to make it easier to populate related data. These helper functions will ultimately result in multiple queries as there is currently no server-side support for joins.

Some examples of frameworks with helpers (not intended to be a comprehensive list):

MongooseJS: query population: Node.js Object-Document Model (ODM) framework including declarative schema & query population for relationships
Mongoid: relations: Ruby ODM including declarative schema & query population for relationships
mongo-views: experimental add-in for the mongo interactive shell adding support for queryable views / virtual collections

You may note that I've said there is currently no server-side support for populating data. There is actually a new $lookup operator being added to the Aggregation Framework in the MongoDB 3.1.x development cycle (which will culminate in the eventual production release series for MongoDB 3.2). For more information see: SERVER-19095 in the MongoDB Jira issue tracker.


Identity Column in Concurrency, Multithreading, Parallel Processing

Would the identity primary key column fail, with a primary key violation in any way, example: processors are trying to input the same identity number?

This is covered in the SQL Server product documentation:

  CREATE TABLE (Transact-SQL) IDENTITY (Property)

  Identity columns can be used for generating key values. The identity property on a column guarantees the following:

  Each new value is generated based on the current seed & increment.
  Each new value for a particular transaction is different from other concurrent transactions on the table.

The second point is the guarantee you need.

It is valid no matter how many user processes or threads access SQL Server concurrently.

To state it explicitly: yes, identity allocation is thread-safe.

A call to CMEDSeqGen::GenerateNewValue allocates the next value to a caller, and is protected by a latch (a lightweight synchronization object). It is not affected by user transactions, which means you can end up with gaps in case of rollbacks. Concurrent transactions can generate identity (and sequence) values without having to wait on a lock.

For more details, see my answer to a related question.


MySQL Workbench - how to search all columns in every table

Open a new Query tab - from the File menu option choose the New Query item (second one down).

Then, it's exactly the same as in the MySQL tool.

use information_schema;
show tables;
SELECT * FROM columns;

will give the result:

Tables_in_information_schema
-----------------------------
CHARACTER_SETS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS                                <<--
COLUMN_PRIVILEGES
..
<more columns snipped>
..

For some reason, the information_schema database doesn't appear in the
databases list in the GUI, but if you explicitly 'use' it, it will work.

Et voilà


Global Temporary Table used in Stored Procedure executed concurrently across sessions

Based on your 2nd code snippet in the question, if the table already exists by the time you get to the sp_executesql, then you didn't need a global temporary table (##table) to begin with: a local temporary table (#table) would work just fine. Local temporary tables are available to sub-processes, and any changes made to them (data and/or schema) are available to all levels, up to the top level where it was created.

If that code snippet is actually inaccurate, and we interpret the following:

  I use dynamic SQL in a stored procedure and must use a global temp table so the temp table and its data is available outside the instance when sp_executesql happens.

to mean that the temporary table is created within Dynamic SQL at some point AND needs to be available to other Dynamic SQL later on, then you still don't need a global temporary table: just create the local temporary table in the main process scope, prior to any Dynamic SQL, and just use the Dynamic SQL to insert into it.

The following is the code posted in the question, modified slightly to be working code, AND working with a local (not global) temporary table:

IF (OBJECT_ID(N'tempdb..#BOM') IS NOT NULL)
BEGIN
  DROP TABLE #BOM;
END;

CREATE TABLE #BOM(ObjectID INT, ObjectName sysname, ParamField VARCHAR(3));
DECLARE @TBL AS TABLE (ObjectName sysname NOT NULL, ColumnName sysname NULL);
DECLARE @SqlQuery NVARCHAR(MAX),
        @Param NVARCHAR(100);

--SET @Param = 'v';

INSERT INTO #BOM (ObjectID, ObjectName, ParamField)
  SELECT [object_id], [name], [type]
  FROM   [msdb].[sys].[objects] so

SET @SqlQuery = 'SELECT tmp.ObjectName, sc.[name]
                 FROM #BOM tmp
                 LEFT JOIN msdb.sys.columns sc
                        ON sc.[object_id] = tmp.ObjectID
                 WHERE 1=1 '; --build sql query

IF (@Param IS NOT NULL)
BEGIN
  SET @SqlQuery = @SqlQuery + ' AND tmp.ParamField = @InnerParam'; --add to sql query
END;

INSERT INTO @TBL (ObjectName, ColumnName) --insert records into table
  EXECUTE sp_Executesql @SqlQuery,
                        N'@InnerParam NVARCHAR(100)',
                        @InnerParam = @Param; --from final sql query

SELECT * FROM @TBL;

In either case, you don't need to create a real table that enforces process separation via @@SPID or a GUID. That is just extra junk that needs to be cleaned up whereas the local temporary table is inherently "thread safe" and is automatically cleaned up for you.


Does the term "page" have the same meaning in DBMS and OS jargons?

Does the term page have the same meaning in DBMS and in OS?

Generically, yes. The term PAGE is a generic for the smallest allocate-able unit of space. In SQL Server, for example, pages are a fixed size and have historically been 4k and 8k, depending on version (not counting Large Pages which are virtual memory and not database structures) and in Windows pages can be a multitude of sizes depending on options and underlying hardware.

  Assume page has the same meaning in DBMS and OS, why do we specify the NSM and PAX only in DBMS? Can this page format be portable to OS?

I didn't read the entire paper because it also overloads the same term. Note that the hardware and underlying OS may change what a PAGE size is but doesn't change the generic meaning. Its use is quite overloaded and TBH you can make whatever you want in your own OS.

This is a little too generic and high level to really "answer" per se, and the "answers" are going to be hardware and software dependent and specific. You might want to narrow your focus a bit and ask a much more pointed question as this is extremely wide IMHO.

Edit:

You also mention SSD's, which also have a unit of PAGE which depends on (sarcasm) shockingly enough the storage controller and firmware. Some SSD's use pages of 1MB or more, some smaller.

Normally when we're talking about "Low Level Cache" it's the data or code caches which are generally referred to as lines or segments and not pages.

  Maybe an additional question. Where and how can I find the documentation or implementation of page size or page format from a SQL server? Any particular DBMS or SQL server will make me happy.

SQL Server documents the architecture here. This is fixed and cannot be changed.

Windows page size (not to be confused with a paging file) can be found by calling GetSystemInfo (Windows API) which will populate a _SYSTEM_INFO structure which has a member called dwPageSize that will tell you the current page size for the system - this should all still be 4k at the time of this writing.


What happens if there are too many inserts in MongoDB? How to ensure all data is stored?

You have answered some of your own questions here, specifically you have a decent idea about the write lock aspect of the equation - 12,000 insert/sec gets you to ~60% write lock.  That's a reasonable level to get consistent performance - you will be getting some contention, and some ops will be a little slower, but you really want to start worrying at about 80% - like a lot of things, when you start exceeding 80% available capacity you will start hitting issues a lot more often.

In terms of other bottlenecks, and specifically how quickly you can write to disk - this can cause problems, but to look at the relevant stats over time I would recommend getting MMS installed with the munin-node plugin to give you hardware and IO stats in addition to the MongoDB stats.

When you have that, the metrics you will want to keep an eye on are:

The Average Flush time (this is how long MongoDB's periodic sync to disk is taking)
The IOStats in the hardware tab (IOWait in particular)
Page Faults (if your disk is busy with writes and you need to read data, they are going to be competing for a scarce resource)

It's a bit complicated then, but here's a basic idea:

When average flush time starts to increase, be worried
If it gets into the multiple second range, you are probably on the limit (though this depends on the volume of data written and the disk speed)
If it approaches 60 seconds you will see performance degrade severely (the flush happens every 60 seconds, so they would essentially be queuing up)
High IOWait is going to hinder performance too, especially if you have to read from disk at any point
Hence looking at page fault levels will also be important

The other piece of this puzzle, which we have not mentioned yet, is the journal.  That will be persisting data to disk as well (by default every 100ms) and so it will be adding to the disk's load if it is on the same volume.  Hence if you are seeing high disk utilization, then moving the journal off to another disk would be a good idea.

There are no real "magic numbers" to stay under, in most cases it's all relative, so get a good baseline for your normal traffic, check to see if things are trending up and maybe load test to see what your limits are and when things start to degrade and you will be in good shape.

After all that pre-amble, on to some of your questions:

  What happens if there are more inserts per second than mongod is able
  to save to the hard disk? Will there be any warning or will it simply
  fail silently?

If you start to stress the disk to the levels described above, eventually everything is going to slow down and at some point (and this will depend on time outs, how beefy your hardware is, how you handle exceptions) your writes will fail - if you are using a recent version of pymongo then you will be using safe writes by default and those will then fail.  If you wish to be a little more paranoid, you can occasionally do a write concern of j:true which will wait to return OK until the write has made it to the journal (i.e. on disk).  This will, of course, be slower than a normal safe write, but it will be an immediate indication of disk capacity related issues, and you could use it to block/queue other operations and essentially act as a throttle to prevent your database from being overwhelmed.

  I am thinking about a simple replication setup using one master and
  one slave. Does the initial sync or a resync process lock the
  databases?

I think I covered locking overall at the start, but to answer this piece specifically: First, make sure you are using a replica set, not master/slave.  The master/slave implementation is deprecated and not recommended for use in general.  As for the initial sync will add some load to the primary in terms of reads, but not in terms of writes, so you should be fine in terms of locking.

  What happens to my data if the write queue increases on long term?

As you can probably tell from the explanation above, the answer is very much dependent on how you write your application, how you choose to have your writes acknowledged and how much capacity you have available.  You can, essentially, be as safe as you wish when it comes to writing to disk on MongoDB, but there is a performance trade off, as mentioned with the j:true discussion above.

Generally, you want to figure out your limiting factor - be it locking, disk speed etc. and then track the levels over time and scale out (sharding) or up (better hardware) before you hit a hard limit and see performance problems.

One last thing, db.serverStatus().writeBacksQueued is actually a metric that will only ever be non-zero in a sharded environment, and it has to do with making sure that writes to a chunk during a migration are dealt with appropriately (handled by the writeback listener).  Hence it essentially is a red herring here - nothing to do with general write volume.


Mysql locking - How to acquire a lock in the DB for each userId

GET_LOCK('rick');
...
RELEASE_LOCK('rick');

During ..., any other thread trying to do GET_LOCK('rick') would be blocked.

Will that work for your application?

Be aware of the caveats:  A disconnect (eg, network glitch) will release the lock.  Multiple locks need some care.  Etc.


Yes, the subselect is slowing you down.  This is because it has to execute that query for every single row of the outer query.  The bigger the outer table, the more times you have to run that same query again.

By moving it out to a join you will only have to execute it once.

SELECT DISTINCT
    `table1`.`date` AS `date`,
    `table1`.`col1` AS `col1`,
    `usage_count`
FROM `table1` LEFT JOIN
(
    SELECT COUNT(`col2`) AS `usage_count`, `col1`, `date`
    FROM `tbl1`
    GROUP BY `col1`, `date`
) AS tbl1 ON `tbl1`.`col1` = `table1`.`col1`
    AND `tbl1`.`date` = `table1`.`date`


SQL Cluster, AlwaysOn AG, or Something Else?

My Network Admin. wants an "Active/Active" with shared storage

There is no such thing as an "active/active" instance.  Only one node of the cluster can be hosting the SQL instance for a failover cluster instance.  If by "active/active" you mean having two different SQL Server failover cluster instances, that is possible.

Why shared storage?  That's an odd "requirement", and usually comes as a need from a requirement, not typically a requirement itself.  The failover cluster instance would exercise shared storage, but different replicas in an availability group would have their own storage.

  load balancing

There is no load balancing with a failover cluster instance.  For an availability group, you can offload certain operations to secondary replicas (reporting/readonly workloads, backups, etc.), but there is no fully capable (data modifying) load that can be offloaded to a secondary.

  redundancy

This can be achieved with either a failover cluster instance and/or an availability group.

  auto-fail over

This can be achieved with either a failover cluster instance or an availability group.

One of the main questions you need to ask yourself is what level of high availability are you looking for?  A failover cluster instance will give you HA for the whole instance, whereas an availability group will give you high availability of a grouping of databases as a unit of failover.


(Edited to include earlier comments for clarity of the answer.)

A better plan for database maintenance is to implement Ola Hallengren's Maintenance Plan. This is free and can be downloaded from https://ola.hallengren.com

You can configure the settings to suit your needs. Brent Ozar suggests some minor parameter tweaks at:  http://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/

Because you are using SQL Server Express, which has no SQL Server Agent, you will need to use an alternate approach to running those jobs.

If you go to Ola Hallengren's site, https://ola.hallengren.com/frequently-asked-questions.html, you can look at his answer:

Therefore, the execution of the stored procedures must be scheduled by using cmd files and Windows Scheduled Tasks. Follow these steps.

Download MaintenanceSolution.sql.
Execute MaintenanceSolution.sql. This script creates the stored procedures that you need.
Create cmd files to execute the stored procedures; for example a script:

sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES', @Directory = N'C:\Backup',
@BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt
In Windows Scheduled Tasks, create tasks to call the cmd files.
Schedule the tasks.
Start the tasks and verify that they are completing successfully.

Mladen Prajdić also wrote a solution some years ago using Service Broker, which is available in SQL Server Express.  See the links at:

http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express
http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express-2

If you like that solution then you should be able to keep all the configuration inside the SQL Server.


How to change the schema of stored procedure without recreating it

You can use ALTER SCHEMA (Transact-SQL) if you are on SQL Server 2005 or later.

ALTER SCHEMA Sales TRANSFER SalesTraining.MainSP;


Need to figure out what FKs point to a specific table

This looks like it may get you what you need:

http://www.sqlskills.com/BLOGS/PAUL/post/Programmatically-picking-apart-foreign-key-constraints.aspx


Error on data node startup in MySQL-cluster

You can try and set

[NDBD DEFAULT]
...

NoOfFragmentLogParts=12
...

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbmtd-nooffragmentlogparts

Then restart all nodes with --initial

Best regards

Johan Andersson

Severalnines AB


mongodb compact does nothing - no entry in mongodb.log and nothing in db.currentOp()

Since you are using the MMAP storage engine there are several factors to be aware of:

The compact command only defragments data files & indexes in MMAP; it does not release unused space to the operating system. Running compact can still be useful to reduce fragmentation and encourage free space reuse, but will not help if your disk space is critically low.
The compact command in MMAP requires additional disk space (up to 2GB) during the compact operation.
As at MongoDB 3.2, compact is a blocking command (database-level lock for the collection being compacted).

The approach to reclaiming preallocated space in MMAPv1 is rebuilding the database either through a resync (recommended for replica sets) or a repair (the only option for standalone deployments).

  repairDatabase doesn't work because no scratch space

You can use the command line option --repairpath or the config file option storage.repairPath to provide extra storage (i.e. a second volume) for working space during the repair process:

mongod --repair --repairpath /path/to/extraspace --dbpath ...

After the repair process completes, the rebuilt data files will reside in the dbpath and the repairpath should be empty.

WiredTiger Storage Engine

If you have the option of upgrading to the WiredTiger storage engine in future, it has many improvements over MMAP:

Data files and indexes are compressed by default (with configurable compression options that can be set globally or per collection).
The compact command can release storage to the O/S (MongoDB 3.2.3+).
Minimal additional space should be required in order to run the compact command.


Usually, you don't want to do this. Have you given second thoughts to this?

Else, pg_crypto is your friend. But as the above-mentioned slides suggest, it isn't necessarily a panacea.


What can I do about high CXPACKET waits?

Install SQL Server 2016 SP2

You mentioned you're on 13.0.1745.2.  This is an RTM version of SQL Server, and is actually out of support as of 1/9/2018.

Plus, if you install SP2 you'll get the new CXCONSUMER wait type (see here for lots of info about that).  This splits out "harmless" parallelism waits from ones that you can actually do something about.  This can help you a lot in determining whether or not CXPACKET is really a problem for your server!

Query Tuning

Check out this article from Paul Randal: Knee-Jerk Wait Statistics : CXPACKET

In it, he talks about how CXPACKET can be a normal wait type - it's always going to occur if queries are going parallel (especially since you don't have the CXCONSUMER split yet).  And he also discusses how query tuning can be a more effective solution (getting at the root cause) over adjusting MAXDOP and Cost Threshold:

  One of the common cases of unexpected parallelism is when a table scan happens where you’re expecting a smaller index seek or scan.

He goes on to say that indexing, statistics updates, etc can be used to eliminate the scan, and thus the query will be less likely to go parallel.

Change MAXDOP

If you really do need to reduce the amount of CXPACKET waits, you can reduce MAXDOP to 2 (from the current setting of 4) - but this might introduce other problems.  Of course, if you have already determined that 4 is a good setting for your system through performance testing, ignore that suggestion.

Increase Cost Threshold for Parallelism

Another option is to increase this value, to prevent some queries from going parallel entirely.  I've seen a lot of advice suggesting that 50 is a good place to start, rather than the default of 5 (example).


Monitor executed SQL statements from external application

The best i can think of will be to use SP_whoisactive from Adam Machanic which is very quick to generate a table to collect the data you need, then run the procedure in a loop with a wait statement to regularly collect the data you want over an interval.

You can gather the data as explained here to log the activities on the system with help of SP_whoisactive.

However , you may always go with live monitoring using SQL profiler and gather the required data accordingly.


To resume the situation for people who don’t want to follow the link to the original question on SO: the OP is querying a Mysql DB via an unspecified interface which (sensibly) refuses to show the values stored in VARBINARY fields with encrypted data, and (rather dumb-mindedly) still shows a BLOB label for the value returned by calling AES_DECRYPT on them. The answer given on SO was to cast those results as CHAR(50), which from a DB perspective makes little sense, but in this way the interface shows the results as text and everybody is happy.

The question here is: if I have to use CHAR(50), how can I handle longer values?

My answer is: you don’t say which interface you are using (your screenshots are not enough for me to recognize it), but I’d bet that if you cast as CHAR instead of CHAR(50) it still works and you have no size limits that you should care of. So:

SELECT 'test' AS original,
        AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))) AS encrypted,
        CAST(AES_DECRYPT(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512))),
                                             UNHEX(SHA2('This Is Cool',512)))
             AS CHAR) AS decrypted;

However, this is an interface question, not a DB engine question, and it is rather strange. I suspect the interface settings have been changed from the ones used when asking the first question, because now the strings which the interface thinks are binary are automatically displayed as HEX. Giving the first query to a commandline interface does not produce the results shown here. To get them from a commandline interface I have to write the second field as HEX(AES_ENCRYPT('test', UNHEX(SHA2('This Is Cool',512)))); similarly, the supposedly wrong result of the second query is simply HEX('test') instead of 'test' because the interface does not know it is actually readable text. Casting it to CHAR should work.

Please be aware that all those conversions the interface is forcing you to perform are not needed when data is exchanged between different parts of your application.


sqlite - very slow performance of WHERE EXISTS without index on EXISTS

The unique index on tbl2.col2 isn't adding any new information that a full table scan wouldn't give it

That is true; the question is, how many table scans do you think are there?

SQLite documentation will tell you that its optimizer is capable of only nested-loop joins, so regardless of whether it is smart enough to rewrite the EXISTS subquery into a join1 or will naively evaluate the predicate for each row of the driving table, you will still end up doing 4.5 million table scans, each retrieving on average 50K rows.

In the presence of a unique index on tbl2.col2 this turns into 4.5 million index seeks using the B-tree, each requiring only 2-3 logical I/O operations and retrieving exactly one row, which is 11 orders of magnitude less I/O.

1 - Which would not make any difference anyway, since it would then rewrite that join into a combination of simple predicates against the two tables.


Composite index of three columns in MySQL and WHERE by 1st and 3rd column. Index usage

There are few factors that affects MySQL decision whether to use an index or no.

This composite index can be used in some cases, like in the fiddle you provided; and in another cases, it won't be used.

Add the following to your fiddle to see the different possibilities:

explain select * from `test` where `a` = 'fgsdfgsdfg' and `c` = 'dfsadfsdf'
delete from test where a='dsfasdfsdfasd';
explain select * from `test` where `a` = 'fgsdfgsdfg' and `c` = 'dfsadfsdf';
explain select * from `test` where `a` = 'ABC' and `c` = 'dfsadfsdf';

Notice that in the first scenario, the index was chosen because it will avoid doing a full table scan.
In the second scenario, since any way a full table scan will be done, it is more expensive to use the index, so the optimizer decided not to use the "possible" key.

In first scenario, field a's cardinality is 2. i.e. there are two different values of this field in the whole table, and they are 50% each. This means that using the index, only 50% of the table will be scanned, so it is better to use the index.

In the second, the cardinality of field a is 1. i.e. only one distinct value exists in the table, and this is the value I am asking for in the where condition. So, all rows will be scanned. The optimizer decides that is it cheaper to scan that table than to scan the index and the table.

Extra piece of info: In the last statement, I am checking a against a value that doesn't exist. The index will be used in this case.

UPDATE

Composite index on (c1, c2, c3) would sometimes be used if you query based on (c1, c3). It will use the first part (c1) for the speed (to seek the first entry). (c3) part will not increase the speed of seeking, but will make fetching data faster, as it will be fetched from the index in this case.

HTH


You can use this
Schema (MySQL v8.0)
CREATE TABLE Table1
    (`salesTime` varchar(7))
;

INSERT INTO Table1
    (`salesTime`)
VALUES
    ('0:05:58'),
    ('0:00:00'),
    ('0:31:54'),
    ('0:23:26')
;

Query #1
SELECT SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(`salesTime`, "%H:%i:%s"))))  FROM Table1;

| SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(`salesTime`, "%H:%i:%s")))) |
| --------------------------------------------------------------------- |
| 01:01:18                                                              |

View on DB Fiddle
But TIME can only have the maximum of 838:59:59, if your SUM get bigger, you need to convert the seconds also into dd.hh:mm:ss


Split databases into groups of roughly equal datasize

The very crude method is to pair up largest database with smallest, then 2nd largest with 2nd smallest and so on. This should work assuming that your database sizes are evenly distributed.
Implementation has been hacked toghether in 5 min, so can probably be improved a lot:

;WITH DBSizes AS
(
SELECT
    db.name,
    DbSize.TotalDataSize * 8 / 1024 as size_mb,
    ROW_NUMBER() OVER( ORDER BY DbSize.TotalDataSize ) AS RankedSizeInc,
    ROW_NUMBER() OVER( ORDER BY DbSize.TotalDataSize DESC ) AS RankedSizeDes
FROM sys.databases AS [db]
CROSS APPLY
    (
        SELECT SUM(mf.size) AS TotalDataSize
        FROM sys.master_files AS [mf]
        WHERE mf.database_id = db.database_id
        AND mf.type_desc = 'ROWS'
    ) AS DbSize
)
SELECT *, NTILE(7) OVER(ORDER BY RankedSizeInc DESC) AS Quartile
FROM
    -- Pair-up largest and smallest DBs toghether, then 2nd largest and 2nd smallest and so on
    -- TOP 50 PERCENT removes duplicates produced by UNION ALL
    ( SELECT TOP 50 PERCENT *
    FROM(
        -- Return DBs in order of increasing size
        SELECT name, size_mb, RankedSizeInc
        FROM DBSizes AS a
        UNION ALL
        -- Return DBs in order of decreasing size
        SELECT name, size_mb, RankedSizeDes
        FROM DBSizes AS b
    ) AS c
    ORDER BY RankedSizeInc
) AS d


Writing a view that caches to another table

CREATE OR REPLACE FUNCTION trade_volume (_market_id  int
                                       , _start_time timestamp
                                       , _end_time   timestamp)
  RETURNS SETOF numeric(16,8) AS
$func$
BEGIN

RETURN QUERY
SELECT sum                          -- dubious identifier
FROM   market_trades_sum_cache
WHERE  market_id  = _market_id
AND    start_time = _start_time
AND    end_time   = _end_time;

IF NOT FOUND THEN
   RETURN QUERY
   INSERT INTO market_trades_sum_cache(market_id, start_time, end_time, sum)
   SELECT market_id
        , _start_time
        , _end_time
        , sum(amount)
   FROM   market_trades
   WHERE  market_id = _market_id
   AND    timestamp BETWEEN _start_time AND _end_time
   GROUP  BY 1
   RETURNING sum;
END IF;

END
$func$ LANGUAGE plpgsql VOLATILE;

A couple of warnings

It's a bad idea to use names of basic functions (sum) or data types (timestamp) as column names.
BETWEEN includes lower and upper bound. Often, you would rather include the lower, but exclude the upper bound.
There is a tiny chance for a race condition in the above function. To be prepared for heavy concurrent load, consider this related answer:

Is SELECT or INSERT in a function prone to race conditions?

Alternative with tsrange

...
AND    ts_range = tsrange(_start_time, _end_time, '[]')
...
   INSERT INTO market_trades_sum_cache(market_id, ts_range, sum)
   SELECT market_id
        , tsrange(_start_time, _end_time, '[]')  -- incl. bounds
        , sum(amount)
   FROM   market_trades
   WHERE  market_id = _market_id
   AND    timestamp BETWEEN _start_time AND _end_time
   GROUP  BY 1
   RETURNING sum;
...

I would consider a multicolumn GIN or GiST index for the range type. Though, if you only check for equality, a btree index would do the job, too. But the latter isn't much use for anything else. Details in the manual.

To include market_id in the index (like I'd suggest), you also need the additional module btree_gin or btree_gist. Then create the index:

CREATE INDEX sum_mult_idx ON market_trades_sum_cache USING GIN (market_id, ts_range, sum);

The last column sum is optional and only makes sense if you get index-only scans out of it. Else don't include it in the index.

Or, if you want to disallow overlapping time ranges, use an exclusion constraint, based on a GiST index (GIN is not currently possibly). Details:

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
Optimizing queries on a range of timestamps (two columns)
2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

The last one also provides a clean solution for enforcing '[)' boundaries.


Is it possible to track a function's dependencies in Postgres?

Since functions are stored as strings in PostgreSQL, and there is no limit to the languages a function could be written in, PostgreSQL cannot analyze the code to add dependencies.

The best option you have is to manually figure out the dependencies.

If you store that information in a database table, you could make sure that all REFRESH MATERIALIZED VIEW statements are executed in the correct order.


Change of index key columns order affects performance

Because you have an equality predicate on where statusID=2.

With the reversed order it is able to seek into exactly the rows matching the status and these can then feed into a stream aggregate to do the grouping.

Your original index supports the group by requestID, statusID but not the WHERE - meaning the whole index will need to be scanned and only rows matching the statusID=2 retained.


Auto update "TIMESTAMP" column only when a manual phpmyadmin edit occurs?

The defaults for TIMESTAMP are quite limited.  Instead of depending on it, take charge.  You should supply values when you need them:

UPDATE tbl  SET
        ...,
        ts = NOW()   -- Include this when you need it; else leave it out.
    WHERE ...


Postgres/PostGIS is returning data from a table that doesn't exist (I'm a newbie -- show me why I am wrong)

FROM ...
     JOIN stations AS subways
    ...

This is called an alias. You aliased stations as subways. So for the rest of the query (ON, SELECT part) the table is called subways and you can only reference it as subways.


How to find if SQL server backup is encrypted with TDE without restoring the backup

I up-voted Brent's answer, as his scenario could definitely muddy the water on whether the backup contained TDE data.

However, if you've had TDE enabled for a while, it seems that RESTORE FILELISTONLY (Transact-SQL) might provide the information you're after.  There is a column on the result set called TDEThumbprint which "Shows the thumbprint of the Database Encryption Key. The encryptor thumbprint is a SHA-1 hash of the certificate with which the key is encrypted."

I looked at some of my backups which were both TDE encrypted and not TDE encrypted.

The backups of my TDE databases had the certificate thumbprint in that column and the backups that did not have TDE databases had null.


Database events that will cause deletion of index

No. To my knowledge, changing a column that is included in an index is not possible (you'll get an error message to that effect). You would have to drop the index before changing the column, and then re-apply the index again. The only exception to this that I am aware of is that you can change a column from NOT NULL to NULL without having to drop the index (but not the other way around).

However, if you use SSMS to change a table, SSMS may drop and re-create the index for you, but it shouldn't drop the index either way.

On a side note, if you have an indexed view, modifying the view till drop any indexes on the view.


Passport numbers should definitely be encrypted in the database.  You should investigate the legality of collecting and storing this information before proceeding.


Temboard: when monitoring a server modifying postgresql.conf on the server is not effective?

Temboard presumably uses ALTER SYSTEM, which on the server side, writes into postgresql.auto.conf.

The contents of postgresql.auto.conf takes precedence over the contents of postgresql.conf, so if a setting is specified with both methods, the value in postgresql.conf is going to be ignored, no matter how you restart.

To get back to the situation where the setting in postgresql.conf is not ignored, consider doing

 ALTER SYSTEM RESET setting_name;

Then the next reload or restart should pick up the value from postgresql.conf.


SQL Server Maintenance Plan multiple database backups in a single folder

Yes it is possible
Use the Maintenance plan wizard its pretty easy to use.
Set your schedule type to recurring and frequency to daily on the "new job schedule form"
when you get to the "Define Back Up Database Task" form select the databases you wanna backup and select "create a backup file for every database " then set path of the folder you want to save to.

hope that helps


How to select rows which have max and min of count?

This is an alternative formulation of Travis's answer which avoids the need to sort the COUNT in both directions.

WITH s
     AS (SELECT MyGroup,
                Count(MyGroup)              AS [Count],
                MAX(Count(MyGroup)) OVER () AS [MaxMyGroup],
                MIN(Count(MyGroup)) OVER () AS [MinMyGroup]
         FROM   MyTable
         GROUP  BY MyGroup)
SELECT Agg,
       MyGroup,
       V.[Count]
FROM   s
CROSS APPLY (VALUES ( 'Max', CASE WHEN [Count] = [MaxMyGroup] THEN [Count] END),
                     ('Min', CASE WHEN [Count] = [MinMyGroup] THEN [Count] END))
             V(Agg, [Count])
WHERE V.[Count] IS NOT NULL


Source - ODBC is not compatible with this version of the DataFlow

Deploying the package with SQL Server Management Studio 2016 resolved the issue.


What is the `TABLE example` syntax called?

It's documented with the SELECT statement and it's called "TABLE Command" there.

In the SQL standard it's called an "explicit table":

  The <explicit table>

     TABLE <table or query name>

  is equivalent to the

     ( SELECT * FROM <table or query name> )

This seems to be part of the standard at least since SQL 1992


Does Oracle run queries to completion after SQLdeveloper disconnects?

Most likely, it will finish a DDL command behind the scenes, but I wouldn't bet my job on it.  What if your CTAS action runs out of tablespace?  You would never see the error.  If you have an unrealiable Internet connection, then the best solution is probably

Simple/basic solution

Use VNC or RDP to "jump" to a desktop or server that is in your datacenter. Run SQL Developer or whatever client on that desktop OS.  If you get disconnected, you just reconnect VNC and you're where you left off.  Your DB client never breaks its connection to the Oracle server.

Way cooler Oracle Scheduler "nohup" solution.

But if you can't do that, or just want to do something cooler with Oracle, you could fire off your DDL inside a Scheduler Job in Oracle.  This is basically like using NOHUP in Unix if you're familiar.  You invoke DBMS_SCHEDULER.CREATE_JOB and then a job slave process will proceed on the Oracle server running as your same user to complete the work.  When you submit the job, it will return immediately with no error or confirmation other than "Anonymous block completed.", which just means that you've queued up an asynchronous job.  If you get disconnected, it won't matter.  You can log back in and check the status of the job via the USER_SCHEDULER_JOB_RUN_DETAILS built-in view.

I just did this in SQL Developer (note, user needs to have CREATE JOB privilege, and obviously CREATE TABLE, too):

-- submit this as a background job
BEGIN
  dbms_scheduler.create_job (
      job_name => 'MY_BACKGROUND_JOB'
    , job_type => 'PLSQL_BLOCK'
    , job_action => 'BEGIN EXECUTE IMMEDIATE ''CREATE TABLE t AS SELECT * FROM all_objects''; END;'
    , enabled => TRUE
    , auto_drop => TRUE
  );
END;

...

-- Waited here a few seconds/minutes here for the job to complete.

...

-- Couple of checks to make sure the table got created
SELECT created FROM user_objects WHERE object_name='T';

CREATED
-------------------
06/09/2014 18:06:42

SELECT count(*) FROM t;

  COUNT(*)
----------
     59907

Here's the status check.  Note the status check says SUCCESS and error 0, so all good.

-- Check on last run of job with this name
SELECT * FROM
( SELECT job_name, status, error#, actual_start_date, log_date
  FROM user_scheduler_job_run_details
  WHERE job_name='MY_BACKGROUND_JOB'
  ORDER BY log_date DESC )
WHERE rownum=1;

I ran the job again just for kicks and I got a FAILED with error 955 which is expected since the table t already exists.


Update row based on match to previous row

UPDATE m
  SET FirstUniqueDetailEntry = CASE
    WHEN m2.OrderNo = m.OrderNo
    AND m2.DetailID = m.DetailID THEN 0 ELSE 1 END
  FROM @m_TestTable AS m
  INNER JOIN @m_TestTable AS m2
  ON m.RowID = m2.RowID + 1;

SELECT * FROM @m_TestTable ORDER BY RowID;

Results:

RowID   OrderNo   DetailID   FirstUniqueDetailEntry
-----   -------   --------   ----------------------
1       Order1    1          1
2       Order1    1          0
3       Order2    10         1
4       Order2    10         0
5       Order2    15         1
6       Order3    20         1
7       Order3    20         0
8       Order3    30         1


Relation between multiple hierarchical taxonomies linked to a single entity

Taxonomy -> TaxonomyTerm
TaxonomyTerm -> Subject
Subject -> Topic
Topic -> Question

Each of these lines is a 1 to many relationship, e.g. A single Taxonomy can have 1 or more TaxonomyTerms, and these terms can be related to multiple Taxonomies. I think you mostly have it but I'm not seeing a need to link one TaxonomyTerm directly with another.


Grouping results and combining duplicates as array

I believe SQLServer 2017 introduced STRING_AGG:
select code, string_agg(name, '') as data
from T
group by code