As far as SQL Server config: Since you're not using SQL Server clustering with shared storage, I believe you don't need to do anything but configure all your instances with Tempdb on T drive (or whatever standard path you choose). See Example A at https://msdn.microsoft.com/en-gb/library/ms345408(v=sql.110).aspx.

As for how to ensure that T drive is available as local storage in all your VMs for when SQL Server starts up, that someone else will have to help with (perhaps stackoverflow with virtualisation tags).

And be sure to test your whole failover process, don't take anyone's word for it.


I would convert this into a single INSERT statement using on conflict do deal with the "insert or update" part:
insert into test1(id, name)
select id, name
from test
on conflict (id) do update
  set name = excluded.name;

This assumes that there is a unique index (or constraint) on test1.id


Tail-log backup options when restoring to a new database

I'm restoring to a new database and don't want to mess with or interrupt the source database at all.

  So should I leave the options under Tail-log backup (Take tail-log
  backup before restore and Leave source database in the restoring
  state) unchecked or checked?

Unchecked.  Just enter the name of the new database you want to restore into and pick the point on the timeline.


How to make CREATE INDEX in InnoDB table faster

RHEL5 ships with MySQL 5.0, unless you installed something else.  So I would focus on increasing these three variables.  They are not specific to either InnoDB or MyISAM.

read_buffer_size        = #of Mb
sort_buffer_size        = #of Mb
tmp_table_size          = #of Mb

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_read_buffer_size

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_sort_buffer_size

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_tmp_table_size

example :

read_buffer_size        = 16M
sort_buffer_size        = 64M
tmp_table_size          = 256M

Increasing the buffer_pool won't help you to speed up index creation. It is used for caching.

http://dev.mysql.com/doc/refman/5.0/en/innodb-buffer-pool.html

Also, if it were possible, I would also try to put the tmpdir on a faster disk.


Deleting a Column from a Table in Production

Without knowing all of the structure of your tables I am limited in my advice. However, no, your database will not plot your demise if your remove a column under the following circumstances (by no means exhaustive):

You still use a database key to map your dimensions.
Your new indices on this new Dimension table are properly covering indices when they should be.
You manage this number of indices so as not to overburden Insert/Updates

Your new design has Two Dimension Tables and a Fact table

This is why it went from m:1 to m:n with a "cross reference" table. We call this another dimension.

Design actually implemented Normalization to achieve this

By removing a dependency, your team will be better equipped to retrieve Facts that can transform how your data is processed in a more meaningful way.

Note on Dimensions and Facts

Dimensions for Descriptive Context

Dimensions provide the “who, what, where, when, why, and how” context surrounding a business process event. Dimension tables contain the descriptive attributes used by BI applications for filtering and grouping the facts. With the grain of a fact table firmly in mind, all the possible dimensions can be identified.
Whenever possible, a dimension should be single valued when associated with a given fact row. Dimension tables are sometimes called the “soul” of the data warehouse because they contain the entry points and descriptive labels that enable the DW/BI system to be leveraged for business analysis. A disproportionate amount of effort is put into the data governance and development of dimension tables because they are the drivers of the user’s BI experience.

Facts for Measurements

Facts are the measurements that result from a business process event and are almost always numeric. A single fact table row has a one-to-one relationship to a measurement event as described by the fact table’s grain. Thus a fact table corresponds to a physical observable event, and not to the demands of a particular report. Within a fact table, only facts consistent with the declared grain are allowed. For example, in a retail sales transaction, the quantity of a product sold and its extended price are good facts, whereas the store manager’s salary is disallowed.
Kimball Dimensional Modeling Techniques

My suggestion is the design team should know that enforcing rules in the database is best, unless it hurts performance. I do not know the size or quantification of your DDL statements to fully answer this, though.
But rest assured this should be a positive change to your system as now SQL Server will not have to go through all that extra data to retrieve what actually mattered.


You can use a data modifying CTE to chain the inserts and pass the generated ID on to the next statement:

CREATE OR REPLACE PROCEDURE record_tenant_transaction(UUID,VARCHAR,NUMERIC,UUID,UUID)
LANGUAGE plpgsql
AS $$
BEGIN

  with new_info as (
    INSERT INTO transaction_info(tenant_id)
    VALUES ($1)
    returning id  --<< adjust here for the correct column name
  ), new_entry as (
    INSERT INTO entry(transaction_info_id)
    select id --<< adjust here for the correct column name
    from new_info
    returning id  --<< adjust here for the correct column name
  )
  INSERT INTO entry_detail(description,amount,credit,debit,entry_id)
  VALUES ($2,$3,$4,$5, (select id from new_entry) );
  COMMIT;

END;
$$

You have to adjust the column name in the line I marked, as I don't know what the generated column in the table transaction_info is.

A more procedural way (which I wouldn't recommend) is to store the generated IDs in variables:

CREATE OR REPLACE PROCEDURE record_tenant_transaction(UUID,VARCHAR,NUMERIC,UUID,UUID)
LANGUAGE plpgsql
AS $$
DECLARE
  l_info_id uuid;
  l_entry_id uuid;
BEGIN
  INSERT INTO transaction_info(tenant_id)
  VALUES ($1)
  returning id --<< adjust here for the correct column name
  into l_info_id;

  INSERT INTO entry(transaction_info_id)
  values (l_info_id)
  returning id --<< adjust here for the correct column name
  into l_entry_id;

  INSERT INTO entry_detail(description,amount,credit,debit,entry_id)
  VALUES ($2,$3,$4,$5, l_entry_id);

  COMMIT;
END;
$$

You should also give your parameters proper names, rather then referencing them through $1, $2 and so on.


Is it better to have large tables or many tables (MySQL)

In my instance, I have groups that I am keeping data separately for. Is it better to create a new table for every group or keep all the information for the groups in one table (that will get very, very large) and have a column that identifies which group it belongs to?

Define "very, very large".
Millions of rows?  That's large[-ish].
Anything less than that really isn't.

If you went with multiple tables, how would you identify them?
You don't want to use Group Names as Table Names, because someone will create a Group containing a character that can't be used as such and break everything.
So you'd wind up with another table that links each table name to its Group name using some other, arbitrary identifier.

Assuming you did this; how would you then write any sort of query that goes across all of those tables, which you might need to do for Administrative purposes.
Let's assume that the database is powerful enough to be able to join or union that many tables in one query?  (MySQL, for example, used to be limited to 61 such tables).

Basically, the table-per-thing model is generally a Bad Idea and should be avoided.

Create a single Groups table, with a unique identifier for each Group and index it sensibly for the queries that you intend to run against it.


Putting SQL Server data files in a different partition

It won't matter for performance but it could matter for other reasons.

To get better performance, you need to have it split across different physical hardware; splitting files across logical partitions does not help or hurt performance. But I wouldn't worry about this unless you start seeing disk activity as a bottleneck.

There are benefits from this approach:

You can back up the D: drive without having to do a system image backup.
You can restore the D: drive without restoring the entire system.
If you get to a point where you need more space, it is easier to move the D: drive to another physical disk than to have to move the C: drive or relocate the database files.


Load data local infile changing special character

I fixed it by using CHARACTER SET UTF8 option in my query.

$loadmeth = "'".'"'."'";
LOAD DATA LOCAL INFILE   '$current_date/dummycsv.csv'
       INTO TABLE tablename
       CHARACTER SET UTF8
       FIELDS TERMINATED BY ','
        ENCLOSED BY $loadmeth
       LINES TERMINATED BY '\r\n'
       IGNORE 1 LINES;


Fast way to load a large amount of test data

Here are a few guiding principles to load not small amounts of test data:

1) When possible use minimal logging.

If you can avoid writing unnecessary data to the transaction log, great, why not do that? This will require your test database to use a recovery model of simple. You also need to be careful to follow the rules which allow you to get minimal logging. If you need to insert into a table with a clustered index that already contains data trace flag 610 can help.

2) Avoid table and column DDL choices which contribute to unnecessary overhead.

As you discovered it is sometimes more efficient to create a clustered index after the data is loaded instead of before it, although it can go either way depending on the data. Sometimes creating the clustered index after can be faster simply because SQL Server can insert data in parallel into an existing heap (as of SQL Server 2016) and it can create the clustered index in parallel. This won't be a minimally logged operation and it will require all of the table's data to be sorted so this might not work if your development system isn't sized large enough.

As you also discovered, definitely create nonclustered indexes after you have loaded the data instead of before.

I believe that there is some overhead to using the VARCHAR(MAX) datatype so I would avoid that if possible.

3) Avoid row by row operations when possible. SQL Server is generally more efficient with set based solutions.

There are a few row by row operations in your code. You are doing a WHILE loop and just processing one row at a time. You're also creating a transaction for every row that you're inserting. There must be some overhead to creating and committing a transaction, right? Why pay that for every row? If your system is big enough you can often just insert your test data with a single query, especially if you are able to use minimal logging.

4) Use parallelism when possible.

The code that you have runs one row at a time and cannot take advantage of multiple cores on the server.

I will give you one way to approach problems like this, but this query may not meet your needs exactly.

WITH
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3),
L5 AS (SELECT 1 AS c FROM L4 A CROSS JOIN L4),
NUMS AS (SELECT 1 AS NUM FROM L5)
SELECT TOP 1000000
  CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS INT) [ID]
, CAST(t.RAND_VALUE AS INT) [OTHERKEY]
, CAST(t.RAND_VALUE AS VARCHAR(100)) [DESCRIPTION]
, CAST(GETDATE() AS DATETIME) [TIME]
INTO [X_JRO_TEST_RAND_2]
FROM NUMS CROSS JOIN (SELECT ROUND(1000 * RAND(CHECKSUM(NEWID())), 0) RAND_VALUE) t;

I will break the query down for you using the guiding principles that I listed at the top of the post.

1) SELECT INTO creates a HEAP table as part of the insert. If the database has a recovery model of simple then this operation will be minimally logged. I can save work by not writing unnecessary data to the transaction log.

2) The table does not have any indexes before loading data. I'm also using VARCHAR(100) instead of VARCHAR(MAX).

3) To generate numbers efficiently I'm using a technique popularized (probably created) by Itzik Ben-Gan. All of the CTEs are there to generate 1000000 rows. I would check out the article for more details on how the approach works. To make RAND() return a different value for each row I used one of the techniques documented in this stack overflow post.

4) As of SQL Server 2014 the query optimizer can insert data into a heap in parallel when using SELECT INTO syntax. As of SQL Server 2016 the query optimizer can insert data into a heap in parallel even without SELECT INTO syntax. On my test system the query runs in parallel.

On a test system here your code took 10:30 for a million rows but the query above took only 23 seconds. I only measured the data loading step and didn't bother with the indexes. It's worth pointing out that there are probably even more efficient ways to generate data in the form that you wanted, and that my code does not have different values for the TIME column.

It's also worth pointing out that if your solution runs fast enough for your purposes, great, go ahead and keep it. Sometimes it isn't necessary to get every last bit of performance out of code, especially code run behind the scenes for development purposes.


PostgreSQL, finding elements by value in numeric JSON arrays

You're using the wrong operator, you want @> (added in 9.4). ?& only operates over JSON objects.

SELECT
  j,
  j @> '"dog"'::jsonb AS hasDog,
  j @> '["dog","waffle"]' AS hasDogAndWaffle,
  j @> '5'   AS has5,
  j @> '42'  AS has42
FROM ( VALUES
  ('[5,2,3]'::jsonb),
  ('["dog","cat","elephant","waffle"]'::jsonb)
)
  AS t(j);

                  j                   | hasdog | hasdogandwaffle | has5 | has42
--------------------------------------+--------+-----------------+------+-------
 [5, 2, 3]                            | f      | f               | t    | f
 ["dog", "cat", "elephant", "waffle"] | t      | t               | f    | f
(2 rows)

If all you're storing is numbers, you should consider using intarray and not jsonb. It should be a lot faster.


How to programmatically remove a push subscription?

Since its the publisher that pushes the data, removing the subscription is done at the publisher.  Here's a script I use (Taken from Wrox).  I first find the subscription I want to remove in part 1, and then use that information to drop the subscription.  This script isn't meant to be run all at once.

**/* Part I on the publisher/distribution server */**
use distribution
go

/* Declare a table variable */
declare @subscription_push table
(
publisher_id smallint,
publisher_db sysname,
subscriber_db sysname,
subscription_int int,
sync_type tinyint,
status tinyint
);

insert into @subscription_push
select publisher_id, publisher_db, subscriber_db, subscription_type, sync_type, status
from distribution..MSsubscriptions
where subscription_type = 1 and status = 2

/* check the data */
select * from @subscription_push

/* Declare table variable that will store the publisher, the publication database, the type of publication and the name of the publication using
sp_helpsubscription */

declare @subscriberinfo table
(publisher sysname,
subscriber sysname);

insert into @subscriberinfo
select publisher, subscriber from distribution..MSsubscriber_info

/* check the data */
select * from @subscriberinfo

**/* Part II */**

/* Then remove the subscription from the publisher based on the information in the table @subscriberinfo*/
exec sp_dropsubscription @publication='publication_name', @article='article or all for all articles', @subscriber='subscribername', @destination_db='TEST'

go

Hope this helps.  I have used in a test environment several times.  Perhaps someone has a better way to remove push subscriptions?


What did I miss in EXPLAIN that hinted at server crippling loads?

EXPLAIN ANALYZE suggested the cost of the whole query with lateral joins would be about a quarter of the original.

EXPLAIN (estimating the cost) suggests 40,089.36 vs. 189,883.92 unicorn points.
But EXPLAIN ANALYZE (measuring actual execution times) disagrees and shows 2,502.031 ms vs. 1,835.193 ms, so around 1/3 slower. There can be many reasons why the estimation is off target. Most prominently cost settings and statistics. See:

Keep PostgreSQL from sometimes choosing a bad query plan

That said, the query can probably be much faster (by orders of magnitude). We only got limited information, but I suppose you want something like this:
SELECT a.*
     , rs.created_at AS latestsnapshottime  -- maybe use COALESCE?
     , rp.created_at AS latestresponsetime
FROM   appointments a
LEFT   JOIN LATERAL (
   SELECT rs.created_at
   FROM   reportsnapshots rs
   WHERE  rs.appointmentid = a.id
   ORDER  BY rs.created_at DESC  -- NULLS LAST ?
   LIMIT  1
   ) rs ON true
LEFT   JOIN LATERAL (
   SELECT rp.created_at
   FROM   responses rp
   WHERE  rp.appointmentid = a.id
   ORDER  BY rp.created_at DESC  -- NULLS LAST ?
   LIMIT  1
   ) rp ON true
WHERE  a.organizationid = 16
AND    a.locationid = '51'
AND    a.cancelled = FALSE
AND    a.filteredin = TRUE
AND    a.start BETWEEN '2021-05-04T00:00:00-06:00' AND '2021-05-04T23:59:59-06:00'
AND    a.locationid IN (61,60,140,53,138,130,133,131,55,51,100)
-- GROUP  BY a.id, rs.created_at, rp.created_at  -- not needed, I guess
ORDER  BY a.start, a.id
LIMIT  100;

DISTINCT ON is a great tool, but for different situations. See:

Select first row in each GROUP BY group?

Retrieving a single row from each LATERAL subquery like I suggest can  use an index and is super fast. Ideally, you have theses indexes:
reportsnapshots (appointmentid, created_at DESC NULLS LAST)
responses       (appointmentid, created_at DESC NULLS LAST)

Further reading:

Optimize GROUP BY query to retrieve latest row per user

If created_at is defined NOT NULL, a simpler index on (appointmentid, created_at) is just as good (and preferable). See:

Sort by column ASC, but NULL values first?
Why does ORDER BY NULLS LAST affect the query plan on a primary key?

Plus an index on the outer table. The one in use now (appointments_organizationId_status_start_idx) doesn't seem too bad. But I suspect more potential, depending on undisclosed information.
You probably don't have to aggregate in the outer query, since both subqueries return a single row each (even your original).
Alternatively, use plain correlated subqueries with max() for your simple case. Probably even faster, yet:
SELECT a.*
     , (SELECT max(rs.created_at)
        FROM   reportsnapshots rs
        WHERE  rs.appointmentid = a.id) AS latestsnapshottime
     , (SELECT max(rp.created_at)
        FROM   responses rp
        WHERE  rp.appointmentid = a.id) AS latestresponsetime
FROM   appointments a
WHERE  ...
LIMIT  100;

See:

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

Aside 1: BETWEEN is typically no good for timestamps. See:

https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_BETWEEN_.28especially_with_timestamps.29
How to add a day/night indicator to a timestamp column?

Aside 2: In your first query plan I see Sort Method: external merge Disk: 3,856kB and Sort Method: external merge Disk: 17,752kB, which indicates  a lack of work_mem. The same problem does not surface in the 2nd plan, nor will it for my queries. But look into your server configuration. Related:

Optimize simple query using ORDER BY date and text


You will need to encrypt the data within the application and store the encrypted values within the database.  Things like TDE and storage level encryption will not meet your requirements.


Aren't two writes required to update a clustered index record

I think the problem here is a difference in terminology.

The "number of writes" that's usually referred to is the number of object accesses, rather than the number of pages that get touched by the physical operation.

The reason why that's usually used as a metric in discussion is because it's a more "stable" and meaningful number to talk about. As we're getting into here, the number of pages touched by an INSERT statement for even a single row depends on many factors, so it's not a very useful quantity outside your own environment and situation.

The one thing I would pick at from the article quote is this (emphasis mine):

  One write for inserting the row, and one write for updating the non-clustered index.

This may be confusing. Inserting a row into the base table would involve an insert to the base table, and also an insert into each nonclustered index (ignoring special index features), not an update.

  So if a record has to be updated, say the value 1 has to be updated to 7, won't the update need to be applied to both the key in the clustered index top node (this may, in cases, cause a re-structuring of the entire structure) and the corresponding value in the record in the leaf-page?

Yes, assuming the column that was updated is in the index key. However, this is still a single object access, and hence a "single write."


MySQL query to evaluate ranges in date and time elements of a datetime column separately

This actually turned out to be fairly painful. :) The details of this logic ran though my head most of last night... but in my current sleep-deprived state, I think I have devised a solution where every case is handled, and which does what we need in a reasonably-optimal fashion.

It returns the correct values for all of the test cases.

You need an index on (EventTime) so that the optimizer can turn the date-level tests into constants and use that index to find the matching rows by date range... then the server can filter the date-matched rows based on their time match in the CASE statement of the where clause.

In the WHERE clause, the CASE expression matches a condition in our data, and will yield a boolean value based on the truthiness of the expressions in the THEN.  This value will include a row if TRUE and exclude the row if FALSE.

I've used variables, but that's mostly for clarity... assuming there's any clarity to be found in what I've written, below, and to prevent errors since the literals are needed in so many different places.

SET @TargetDateTime = '2013-09-26 00:00:00';
SET @DateRange = 7;
SET @TimeRange = 15;

SELECT E.*
 FROM EventTable E
      -- must be at or after midnight the morning of the first valid day
WHERE EventTime >= DATE_SUB(DATE(@TargetDateTime), INTERVAL @DateRange DAY)
      -- must be before midnight on the morning after the first valid day
  AND EventTime <  DATE_ADD(DATE(@TargetDateTime), INTERVAL (@DateRange + 1) DAY)
      -- now, hold my beer and watch this...
  AND CASE
  -- easy first case, the time portions of both values are equal
  WHEN TIME(EventTime) = TIME(@TargetDateTime)
  -- in each case if the expression in the "THEN" evaluates to something true,
  -- then the row will be returned if the other conditions in WHERE are also met
  THEN TRUE
  -- next case, if the event appears to be earlier than the target,
  -- we match the row IF either...
  WHEN TIME(EventTime) < TIME(@TargetDateTime)
  -- ...the simple case, difference in seconds is within range
  THEN TIME_TO_SEC(@TargetDateTime) - TIME_TO_SEC(EventTime)
       BETWEEN 0 AND (@TimeRange * 60)
       OR
  -- ...the complicated case, when we span midnight
       86400 + TIME_TO_SEC(EventTime) - TIME_TO_SEC(@TargetDateTime) <= (@TimeRange * 60)
  -- next case, event appears later than target, we match if either...
  WHEN TIME(EventTime) > TIME(@TargetDateTime)
  -- ... simple subtraction returns a value in range
  THEN TIME_TO_SEC(EventTime) - TIME_TO_SEC(@TargetDateTime)
       BETWEEN 0 AND (@TimeRange * 60)
       OR
  -- ... we wrap around midnight
      86400 + TIME_TO_SEC(@TargetDateTime) - TIME_TO_SEC(EventTime) <= (@TimeRange * 60)
  -- ELSE isn't encountered unless we run into null values, in which case, no match
  ELSE FALSE END; /* END of CASE expression */

A working demo of the final version is here: http://sqlfiddle.com/#!2/7b8e5/2

It is usually bad form in a WHERE clause to use columns as arguments to functions, because this prevents the optimizer from using an available index, since it has to evaluate the data in every row of the table in order to evaluate the function.  There's no harm, here, though, since an index can't help us match the times.  This is because DATETIME values are on the left hand side of the field, as stored, and the time values are on the right, and an index stops helping as soon as we encounter irrelevant information from left to right.  (That's usually discussed in the context of multi-column indexes, but it also holds true, here.)

It's also often bad form in a WHERE clause to use a CASE expression because that too may prevent the optimizer from choosing the optimal path because the CASE can prevent the optimizer from forming a proper understanding of the logic, but this is acceptable here because we have nothing but AND in the WHERE clause and two other conditions that are easily satisfied by an index.

Update: This code and the answer went through several iterations before arriving at its current (working) form, as you can see in the comments.

Essentially, what we are doing is considering the three cases than can be identified when trying to compare wall-clock time of day: the times are equal, A is earlier than B, or B is earlier than A.  It seems like "absolute value" might come into play, but the problem we encounter is that when a time value has no date component, earlier and later aren't meaningful concepts when the times might span midnight.  Without a date component, 00:05:00 and 23:55:00 are 23 hours and 50 minutes apart.  With a date component, they could also be "n days and 10 minutes" apart.  When the date component is missing but uninteresting (or already handled, in this case), we first check the simple case, subtracting the "earlier" from the "later" to see if they're within range, but when they're not, we add 86400 seconds to the "earlier" component, essentially making it "the same time, the following day"... then the subtraction can be done to see if the times are close enough together when they're calculated as having spanned midnight.


Is order by necessary after a group by clause?

My understanding is group by clause seems to already have a sorting
  functionality.

There are two basic approaches that SQL Server can use. A stream aggregate requires the data to be sorted by the group by keys. This can be either supplied by an index or might need an explicit sort. A stream aggregate is order preserving in that the rows output from that operator are in the same order as the input. This does not imply any guarantee about the eventual output from the query as a whole however. You only get that if you add an ORDER BY.

For a GROUP BY col1, col2 the input stream can be ordered by either col1, col2 or col2, col1 to be acceptable for the stream aggregate in this case. The addition of an index might change the decision of the optimiser as to which one to use.

The other basic approach is that of a hash aggregate. In which the grouping keys are hashed. This is not at all order preserving and will likely output rows in seemingly random orders.

Additionally for parallel plans there might be hybrid approaches, e.g. Each thread could have a local stream aggregate with the thread results then aggregated at global level with a hash aggregate.

Adding ORDER BY means that SQL Server will ensure that the rows are delivered in the desired order. If you are currently observing an output order of col1, col2 then likely you are getting a stream aggregate ordered by those two columns. The addition of an explicit order by won't change the execution plan to add any additional sort as SQL Server will recognize that the output of this aggregate is already in the desired order.


I've faced similar issue - the thing is sql doesn't "know" that cola and colb is range and cola of next row will always be bigger than current row colb, so between won't help too much: when it will find first cola that matches between, it will continue checking others also.
I would suggest query that uses cross apply to find max(table2.cola) <= table1.cola and then to verify add where table2.colb >= table1.cola
Something like this

SELECT      t1.cola AS [InputValue]
           ,t2.cola
           ,t2.colb
           ,t2.colc
           ,t2.cold
           ,t2.code
FROM        table1 t1
CROSS APPLY (   SELECT   TOP 1
                         t2.cola
                        ,t2.colb
                        ,t2.colc
                        ,t2.cold
                        ,t2.code
                FROM     table2 t2
                WHERE    t1.cola >= t2.cola
                         AND t1.cola <= t2.colb
                ORDER BY t2.cola DESC ) t2;


Almost all of your requirements can be provided by merge-replication. There is some information about it in my StairWay series here: http://www.sqlservercentral.com/stairway/72401/

Those articles don't cover replication using port 80 but that is possible.

However, the topology is always a tree. You have one root and the data is distributed through there. A real "distributed agents" topology, where each note can synch with each other node is not possible with the build in tools.


How should I store time series in mongodb

I suggest a single time series entry per document. There are some problems with storing multiple entries per document:

a single document is limited to a certain size (currently 16 MB); this limits how many entries can be stored in a single document
as more entries are added to a document, the entire document (and time series) will needlessly be deleted and reallocated to a larger piece of memory
queries on sub-documents are limited compared to queries on regular documents
documents with very flat structures (like one sub-document for each second) are not performant
the built-in map-reduce does not work as well on sub-documents

Also note a timestamp is built-in to the default MongoDB ObjectId. You can use this if the time series precision is less than one second.

Here is an example BSON document from an event logging library that uses MongoDB:

Example format of generated bson document:
{
    'thread': -1216977216,
    'level': 'ERROR',
    'timestamp': Timestamp(1290895671, 63),
    'message': 'test message',
    'fileName': '/var/projects/python/log4mongo-python/tests/test_mongo_handler.py',
    'lineNumber': 38,
    'method': 'test_emit_exception',
    'loggerName':  'testLogger',
    'exception': {
        'stackTrace': 'Traceback (most recent call last):
                       File "/var/projects/python/log4mongo-python/tests/test_mongo_handler.py", line 36, in test_emit_exception
                       raise Exception(\'exc1\')
                       Exception: exc1',
        'message': 'exc1',
        'code': 0
    }
}

Since an event log is similar to a time series, it may be worth studying the rest of the code. There are versions in Java, C#, PHP, and Python.

Here is another similar open source project: Zarkov

[update] In response to @RockScience's comment, I've adding some more references:

Very verbose case study of how MongoDB was used to store time series. Interesting parts:

Slide 25: how much data? 20 4-byte data points @ 1 minute intervals for 10 years (not apparent if these are actual statistics or just projected estimates)
Slide 66: shows sample MongoDB document used to store time series data

Best practices for storing log data, straight from the creators of MongoDB


To configure SQL Server on Ubuntu, run the following commands in a terminal to install the mssql-server package.

Import the public repository GPG keys:

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo
apt-key add -

Register the Microsoft SQL Server Ubuntu repository:

sudo add-apt-repository "$(wget -qO-
https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"

Run the following commands to install SQL Server:

sudo apt-get update sudo
apt-get install -y mssql-server

After the package installation finishes, run mssql-conf setup and follow the prompts to set the SA password and choose your edition.

sudo /opt/mssql/bin/mssql-conf setup

Make sure to specify a strong password for the SA account (Minimum length 8 characters, including uppercase and lowercase letters, base 10 digits and/or non-alphanumeric symbols).

Once the configuration is done, verify that the service is running:

systemctl status mssql-server

If you plan to connect remotely, you might also need to open the SQL Server TCP port (default 1433) on your firewall.

At this point, SQL Server is running on your Ubuntu machine and is ready to use!
You can read Microsoft Documentation Here


"Truncate" or "Delete/Vacuum Full" for deleting some of the table rows

If you don't have concurrent transactions that would prohibit you from getting an exclusive lock on the table, I would:

Select the (relatively few) surviving rows into a temporary table.
Make sure you have enough RAM available for the temporary tables (for this session only). Read about temp_buffers in this related answer:
Optimizing bulk update performance in PostgreSQL
Truncate the table.
Re-insert the survivors.

This way, views stay intact. They would prohibit you from dropping involved tables.
You also do not need any vacuuming, no dead rows at all this way. While being at it, you may want to ORDER BY your rows for the INSERT for optimized SELECT performance.

Here and here are closely related cases with more details on SO.


Upgrade SQL Server 2008 Express to SQL Server Workgroup 2008 Volume License

You can use the copy database wizard to move databases from one database instance to another.

Right-click on an existing database, choose TASKS and then at very bottom, is 'Copy Database'.

I would recommend the detach / re-attach database option, I don't like the other method, it's not reliable enough.

You'll probably want to test a couple of database detach / attach operations.

Detach takes the database offline, and makes it portable so you can change the volume, before re-attaching the database in a new location.


Managing static (or reference) data between different environments?

The way I do this is with SSDT projects and a post deployment script that MERGE-s from a VALUES list to the target table.

If it is necessary to store different values in different environments you can use sqlcmd variables defined in the project properties and store a publish profile for each different environment.

It does require a particular development approach though. Developers should know that the contents of these tables is part of the project and that they should be editing the post deployment script and re-publishing rather than editing the data in these tables directly.

You could potentially add triggers to these tables that roll back changes and raise an error as a reminder if you feel that this may be a problem. The triggers could check a CONTEXT_INFO value and allow the modification to succeed if some specific value to allow the post deploy script to succeed.


Postgres to Postgres foreign data wrapper, for tables based on queries

To do what you need to do, you should be able to create a view on your secure server and reference that as the table in your less secure server, like this:

On the secure server:

CREATE OR REPLACE VIEW redacted_view AS SELECT col1, col2 FROM sensitive_table;

On the less secure server:

CREATE FOREIGN TABLE sensitive_table (col1 int, col2 int)
     SERVER foreign_server
     OPTIONS (table_name, 'redacted_view');

It's not your ideal scenario, but from what you're describing it should work. Hope that helps. =)


What happens during "big" inserts on a table with a clustered composite key?

Holy cow, you've got a lot of questions in here. Let's break this down.

Q: Will SQL "move" the existing rows to maintain the clustering, or will it let the table become "fragmented"?

Think of a database as a collection of pages - literal pieces of paper laid out on your desk. Think about the dictionary for now. If you wanted to add more words to the dictionary, you could add them in place if the pages had empty space.

When you first start out with an empty dictionary, this is relatively easy. But think about a mature dictionary with thousands of paper pages in it, all full.

When you want to add more words to that mature dictionary, odds are there isn't going to be any space left on the page. SQL Server will "tear" a page - it will take a brand new page somewhere else, and move some of the words over onto that new page. The new page would be at the end of the dictionary. The good news is that immediately after that action, there's now a half-empty page at the end of your dictionary, and also at the middle, both with space to add words.

If you happen to be adding them in that order, that is. (This is why the way you load data becomes increasingly important.)

Could this cause a big performance hit if the import is done one row at a time?

Forget the index for a second - adding data one row at a time is just plain inefficient regardless of the indexing structure. SQL Server is a set-based system - whenever you can work in sets, you probably should.

What happens when I query the data?

You didn't ask this, but I'm asking it for you, hahaha.

Think back about the aftermath of our inserts. Now we've got a dictionary that's mostly ordered, but when you get to a few points of the dictionary, you'll have to jump to the back to read from a few other pages. If these pages are all cached in your memory (RAM, buffer pool, etc) then the overhead just isn't going to be that large. Most memory access is random anyway - it's not like SQL Server stores your dictionary in memory in order.

On the other hand, if you need to fetch the data from conventional magnetic hard drives (spinning rust), then you can end up getting a bit of a performance benefit if that data is stored in order. The real design goal here, though, is to get the data from RAM instead of getting it from drives. The difference between defragmented data on disk versus fragmented data on disk is nowhere near as significant as the difference between getting it from disk versus getting it from RAM.

Should I rather just not bother with the ordering of the rows and just add an identity column as the primary key and an index on the Date column to help with my queries?

Bingo: this is the difference between physical database design and logical database design. Programmers have to worry a lot about physical database design initially, but as long as your database is under, say, 100GB in size, you can fix logical design in post, so to speak. Put an identity field on there for starters, cluster on it, and then after being live for a few months, revisit the index design to maximize performance.

Now, having said that, once you're experienced with this type of decisionmaking, then you'll be better equipped to guesstimate indexes right from the start. Even so, I don't even usually put much thought into index design initially. Users never seem to query the data the way I would have expected.


InnoDB : Deadlock from one reader and one writer

Due to how MySQL handles replication of INSERT INTO ... SELECT, it will actually do some locking on the rows of the tables involved in the SELECT portion of your query. Here is an older article detailing the issue.

The solution is to use row-based replication and set the transaction level to READ-COMMITTED.

To clarify, the locking of INSERT INTO .. SELECT will happen regardless of if you are using replication at all, it is just part of the internals.


You can use this methodology to determine the first day of 3 months ago, and the last day of the previous month:

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)   --First day of 3 months ago
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) --Last Day of previous month

Then, just use it on your where clause.

declare @start date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
declare @end date = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

select *
from table
where dateField between @start and @end

If your dateField is datetime then you need to add the seconds, or just add a day and use a < operand so you don't miss the last day

declare @start date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
declare @end date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)        --First day of current month

select *
from table
where dateField >=@start and dateField < @end


Boolean logic with BITs and Booleans in SQL Server

You may need to revise your code as follows:

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
INSERT INTO @Data VALUES ((@Flag1 | case when charindex('qwerty', @string ) > 0 then 1 else 0 end) & @Flag2)
SELECT * FROM @Data

Your original code has syntax error because LIKE is not an operator.

Another way is to use IIF as following

DECLARE @String varchar(2000) = 'asdf'
DECLARE @Flag1 bit = 1
DECLARE @Flag2 bit = 0

DECLARE @Data Table
(
    Value bit
)

INSERT INTO @Data VALUES (@Flag1 | @Flag2)
INSERT INTO @Data VALUES ((@Flag1 | iif( @String LIKE '%qwerty%', 1, 0)) & @Flag2)
SELECT * FROM @Data


postgres: "alter default privileges" issue

Either you've forgot to add a permission to SELECT in the GRANT below, quoted from the question:

ALTER DEFAULT PRIVILEGES FOR USER access1
   IN SCHEMA access1 GRANT INSERT, UPDATE, DELETE ON TABLES TO access1_rw;

... or, if you really meant that having the role access1_rw doesn't let  read the tables even tough it lets write to them, then you must grant the role access1_ro to user1 (in addition to granting the role access1_rw).

Not having either of those has the consequence that user1 was never granted the right to SELECT from tables in the mentioned schema.


What is the easiest way to move data from Oracle to SQL Server?

I actually used the "Microsoft SQL Server Migration Assistant (SSMA)" from MS once for this and it actually did what it promised to do:

SQL Server Migration Assistant for Oracle (documentation)
Microsoft SQL Server Migration Assistant v6.0 for Oracle (download)
SQL Server Migration Assistant (SSMA) Team's Blog

However in my case it was not as fast as I would have expected for a 80 GB Oracle-DB (4 hours or something) and I had to do some manual steps afterwards, but the application was developed in hell anyway (one table had 90+ columns and 100+ indices).


If you use log shipping, then the archive_command on the master and the restore_command on the replica are responsible for copying the logs. The contents of those commands is up to you.  You can use scp, rsync, tar, ssh, or just plain cp command to and from a shared network folder; or any number of other things.  It is quite easy to come up with commands that generally work but fail in some edge cases, so you might want to use canned solutions that do it for you, like pg_barman or pgbackrest, as they are more likely to get the edge cases correct.

I don't know what your second question means.

For the 3rd, your archive directory generally shouldn't be on the same storage system as your database directory is, otherwise you have a single point of failure, which rather defeats the purpose.  So your rsync command should go inside archive_command, so that each file is sent to where ever it is you want it to go as soon as it is ready for archival. If you do want your archive_command to copy the data to a local archive, than your hand-rolled rsync should definitely be rsyncing from that local archive directory, not pg_xlog.

It is possible to combine both streaming and log shipping.  This is really useful if you also want to be able to do PITR (point-in-time-recovery), as then you need the WAL log archive anyway.  If you are not interested in PITR, then you should probably just use streaming replication, with replication slots to cover long outages.


No. rman does not delete directories that it did not create by itself.


Does a trigger in a transaction only fire when the transaction is committed?

I don't know anything about Entity Framework, but in the following basic test using Sql Server Management Studio against a local instance, it appears that the trigger fires upon the completion of the insert to table dbo.Test1 and I can immediately see rows in dbo.Test2 BEFORE issuing the COMMIT

USE [Test]
GO
IF OBJECT_ID('dbo.Test1', 'U') IS NOT NULL
    DROP TABLE dbo.Test1
GO
IF OBJECT_ID('dbo.Test2', 'U') IS NOT NULL
    DROP TABLE dbo.Test2
GO
CREATE TABLE [dbo].[test1](
    [col1] [char](3) NULL
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[test2](
    [col1] [char](3) NULL
) ON [PRIMARY]

GO
CREATE TRIGGER dbo.Test1Trigger
   ON  dbo.Test1
   AFTER INSERT,DELETE,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.Test2 select * from inserted

END
GO
begin transaction
insert into dbo.test1 (col1) values('abc')
select * from dbo.test2     --The row comes out of the select without issuing a commit
--insert into dbo.test2 (col1) values('xyz')
--commit
--select * from dbo.test2
--rollback


Loop Block with execution doesn't persist

IMHO just a single UPDATE can solve your issue. Something like this:

UPDATE "Vendas"
   SET "EhConsumoProprio" = TRUE
  FROM "ItensVendas"
 WHERE "ItensVendas"."VendaId" = "Vendas"."Id"
   AND "Vendas"."Especie" = 'NFE'
   AND "Vendas"."ClienteId" IS NOT DISTINCT FROM 0
   AND "ItensVendas"."CodCFOP" IN (5949, 5927);


How do I connect to mysql without having to type an empty password?

Turns out the answer is actually to use the -p -- the long version allows you to set it to empty, like so:

mysql --port=3307 --host=127.0.0.1 -uroot --password=


Auto created Statistics vs Manually created Statistics

The only major difference is that SQL Server will only ever create single column statistics. You can create multi-column and filtered statistics. If any of the statistics you created were those types, they may be important. If not, SQL Server is likely to have created system statistics on those single columns, when deemed useful to a query.

Unfortunately you have to do a bit of digging to figure out which statistics your queries are using.

See here for checking the plan cache.

See here for checking queries as you execute them.  Note in the comments that on 2014+ you require a different trace flag.


Can PosgreSQL databases be restored from physical files?

As dezso said, its easy to change the data directory.

But I did a small PoC for this. So its possible to use existing physical files to another Postgresql.

Limitations

The new PostgreSQL should be the same version.
New PostgreSQL data directory and custom tablespace directory should be same.

My Postgresql config:

OS: Ubuntu 16.04
Postgresql 9.4

Install and config a sample database

apt-get install postgresql-9.4
mkdir /opt/pgdata
chown -R postgres:postgres /opt/pgdata

--in psql
create tablespace db LOCATION  '/opt/pgdata/';
create database bhuvi with tablespace db;
\c bhuvi
create table test (id int);
insert into test values (1),(2),(3),(4),(5);
bhuvi=# select * from test;
 id
----
  1
  2
  3
  4
  5
(5 rows)

Remove the PostgreSQL

--take a copy of Data direcory and table space directory
cp -R /var/lib/postgresql/9.4/main/ /opt
cp -R /opt/pgdata /opt/pgdata_bak

--clean up postgresql
apt-get remove --purge postgresql-9.4
apt-get autoclean
apt-get autoremove
rm -rf /etc/postgresql
rm -rf /var/lib/postgresql/
rm -rf /var/log/postgresql/
rm -rf /opt/pgdata

Fresh install and Replace old data and Tablespace directories

-- Reinstall
apt-get install postgresql-9.4
service postgresql stop
rm -rf /var/log/postgresql/9.4/main/
cp -R /opt/main /var/log/postgresql/9.4/
cp -R /opt/pgdata_bak /opt/pgdata
chown -R postgres:postgres /var/log/postgresql/9.4/main/
chown -R postgres:postgres /opt/pgdata
service postgresql start

Now check the new Postgresql

--in psql
\l+
   Name    |  Owner   | Encoding |
-----------+----------+----------+
 bhuvi     | postgres | UTF8     |
 postgres  | postgres | UTF8     |
 template0 | postgres | UTF8     |
           |          |          |
 template1 | postgres | UTF8     |
           |          |          |
bhuvi=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)

bhuvi=# select * from test ;
 id
----
  1
  2
  3
  4
  5
(5 rows)


Do I have to filter the percona.checksums table in replication for use with pt-table-checksum?

You're incorrect. It only runs the checksums on the master, not on the slaves.

Example query run:

REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'db', 'table', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `db`.`table` /*checksum table*/;

This works because it requires statement based replication, so the query is run fresh on every slave against their data, not the data the master found. This is documented on pt-table-checksums docs under LIMITATIONS

pt-table-checksum requires statement-based replication, and it sets
binlog_format=STATEMENT on the master, but due to a MySQL limitation
replicas do not honor this change. Therefore, checksums will not replicate
past any replicas using row-based replication that are masters for further replicas.

If you modify your data after the checksum query has replicated, of course it won't show that the data was changed, as it was correct when the checksum was calculated.

If you re-run the checksum after changing the data on the slave, then it should pick it up, assuming you're not using a n>2 tier replication setup.

re: Another hypothesis, it doesn't matter, the connection to the slave only matters for 'live' detection of mismatches, not for the checksums to actually calculate correctly. You shouldn't have false negatives, even with network interruptions.

Makes sense?


SQL Server Query Optimizer Time Spent and Numbers

Yes, SQL Server can report how long it took to do any of those actions (though you may have to run it to get some additional details such as actual row counts returned)

Statistics Time

SET STATISTICS TIME { ON | OFF }

Displays the number of milliseconds required to parse, compile, and execute each statement.
https://msdn.microsoft.com/en-us/library/ms190287.aspx

Execution Plans

To pull the execution plan you have many options:

Query the plan cache: (https://www.mssqltips.com/sqlservertip/1661/analyzing-the-sql-server-plan-cache/)
Trace/extended events the xml: https://www.mssqltips.com/sqlservertip/1264/capturing-graphical-query-plans-with-sql-server-profiler/)
Gather it in SSMS: via the "Include Actual Execution Plan" button in the toolbar https://msdn.microsoft.com/en-us/library/ms189562.aspx

On the SQL Server Management Studio toolbar, click Database Engine
Query. You can also open an existing query and display the estimated
execution plan by clicking the Open File toolbar button and locating
the existing query.
Enter the query for which you would like to display the actual
execution plan.
On the Query menu, click Include Actual Execution Plan or click the
Include Actual Execution Plan toolbar button
Execute the query by clicking the Execute toolbar button. The plan
used by the query optimizer is displayed on the Execution Plan tab in
the results pane. Pause the mouse over the logical and physical
operators to view the description and properties of the operators in
the displayed ToolTip.
Alternatively, you can view operator properties in the Properties
window. If Properties is not visible, right-click an operator and
select Properties. Select an operator to view its properties.
You can alter the display of the execution plan by right-clicking the
execution plan and selecting Zoom In, Zoom Out, Custom Zoom, or Zoom
to Fit. Zoom In and Zoom Out allow you to zoom in or out on the
execution plan, while Custom Zoom allows you to define your own zoom,
such as zooming at 80 percent. Zoom to Fit magnifies the execution
plan to fit the result pane.


Postgres LIKE query using a GiST index is just as slow as a full scan

In my opinion if you do not share how you did the test, it's very hard to give you an answer. Let see an example of what I mean. Sorry for I used a postgres 11 but the conclusions are the same:

This is a new db, there is nothing running against the instance:

test=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
test=# create table test_trgmidx (col1 varchar(30), col2 varchar(50));
CREATE TABLE
test=# CREATE INDEX trgm_idx_test_col2 ON test_trgmidx USING gist (col2 gist_trgm_ops);
CREATE INDEX

Inserting 500000 rows using a very simple loop.

test=# \i loop_long.sql
DO
test=# select count(1) from test_trgmidx;
 count
--------
 500000
(1 row)

test=# select * from test_trgmidx limit 20;
col1        |    col2
------------+------------
 ABCD1EFGH  | abcd1efgh
 ABCD2EFGH  | abcd2efgh
 ABCD3EFGH  | abcd3efgh
 ABCD4EFGH  | abcd4efgh
 ABCD5EFGH  | abcd5efgh
 ABCD6EFGH  | abcd6efgh
 ABCD7EFGH  | abcd7efgh
 ABCD8EFGH  | abcd8efgh
 ABCD9EFGH  | abcd9efgh
 ABCD10EFGH | abcd10efgh
 ABCD11EFGH | abcd11efgh
 ABCD12EFGH | abcd12efgh
 ABCD13EFGH | abcd13efgh
 ABCD14EFGH | abcd14efgh
 ABCD15EFGH | abcd15efgh
 ABCD16EFGH | abcd16efgh
 ABCD17EFGH | abcd17efgh
 ABCD18EFGH | abcd18efgh
 ABCD19EFGH | abcd19efgh
 ABCD20EFGH | abcd20efgh
(20 rows)

Now I restart the instance to have a clean buffer cache, then I run the explain for the first select twice, to see how cache is "disturbing" our results:

test=# explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like 'abcd345678efgh';
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using trgm_idx_test_col2 on public.test_trgmidx  (cost=0.29..8.30   rows=1 width=28) (actual time=4.586..4.912 rows=1 loops=1)
  Output: col1, col2
  Index Cond: ((test_trgmidx.col2)::text ~~ 'abcd345678efgh'::text)
  Buffers: shared hit=19 read=237
 Planning Time: 0.303 ms
 Execution Time: 4.934 ms
(6 rows)

test=# explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like 'abcd345678efgh';
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using trgm_idx_test_col2 on public.test_trgmidx  (cost=0.29..8.30 rows=1 width=28) (actual time=2.096..2.298 rows=1 loops=1)
 Output: col1, col2
 Index Cond: ((test_trgmidx.col2)::text ~~ 'abcd345678efgh'::text)
Buffers: shared hit=232
Planning Time: 0.072 ms
Execution Time: 2.317 ms
(6 rows)

It's clear that the first run need to retrive rows from disk (read=237) while the second one need only to access buffer cache (shared hit=232, NO reads).
Now let's do the same for the second select, restart the instance and run the explain twice:

test=# explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like '%d2%';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.test_trgmidx  (cost=0.00..9926.00 rows=106061 width=28) (actual time=0.039..89.906 rows=111111 loops=1)
   Output: col1, col2
   Filter: ((test_trgmidx.col2)::text ~~ '%d2%'::text)
   Rows Removed by Filter: 388889
   Buffers: shared read=3676
 Planning Time: 0.719 ms
 Execution Time: 94.942 ms
(7 rows)

test=# explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like '%d2%';
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.test_trgmidx  (cost=0.00..9926.00 rows=106061 width=28) (actual time=0.015..61.741 rows=111111 loops=1)
   Output: col1, col2
   Filter: ((test_trgmidx.col2)::text ~~ '%d2%'::text)
   Rows Removed by Filter: 388889
   Buffers: shared hit=3676
 Planning Time: 0.081 ms
 Execution Time: 65.878 ms
(7 rows)

As you can see, now the reads come from disk the first run and from buffer the second one. Things are even more complicated because of OS buffers. Is is possible to clean OS cache and re-run everithing having back different results:

# free
              total        used        free      shared  buff/cache   available
Mem:        7914604      929920     4105056       93960     2879628     6748994
Swap:       4063228           0     4063228

# echo 3 > /proc/sys/vm/drop_caches

# free
              total        used        free      shared  buff/cache   available
Mem:        7914604      802204     6846392       93960      266008     6951156
Swap:       4063228           0     4063228
#

Look at column buff/cache as it drop down from 2879628 to 266008. Now run the explain  again (twice for any select):

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=#  explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like 'abcd345678efgh';
                                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using trgm_idx_test_col2 on public.test_trgmidx  (cost=0.29..8.30 rows=1 width=28) (actual time=130.858..140.403 rows=1 loops=1)
   Output: col1, col2
   Index Cond: ((test_trgmidx.col2)::text ~~ 'abcd345678efgh'::text)
   Buffers: shared hit=19 read=237
 Planning Time: 38.448 ms
 Execution Time: 140.466 ms
(6 rows)

test=#  explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like 'abcd345678efgh';
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using trgm_idx_test_col2 on public.test_trgmidx  (cost=0.29..8.30 rows=1 width=28) (actual time=4.386..4.759 rows=1 loops=1)
   Output: col1, col2
   Index Cond: ((test_trgmidx.col2)::text ~~ 'abcd345678efgh'::text)
   Buffers: shared hit=232
 Planning Time: 0.115 ms
 Execution Time: 4.787 ms
   (6 rows)

test=# explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like '%d2%';
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.test_trgmidx  (cost=0.00..9926.00 rows=106061 width=28) (actual time=9.214..161.243 rows=111111 loops=1)
   Output: col1, col2
   Filter: ((test_trgmidx.col2)::text ~~ '%d2%'::text)
   Rows Removed by Filter: 388889
   Buffers: shared hit=1 read=3675
 Planning Time: 0.090 ms
 Execution Time: 165.354 ms
(7 rows)

test=#  explain (analyze, buffers, verbose) select * from test_trgmidx where col2 like '%d2%';
                                                          QUERY PLAN
       ----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.test_trgmidx  (cost=0.00..9926.00 rows=106061 width=28) (actual time=0.013..62.043 rows=111111 loops=1)
   Output: col1, col2
   Filter: ((test_trgmidx.col2)::text ~~ '%d2%'::text)
   Rows Removed by Filter: 388889
   Buffers: shared hit=3676
 Planning Time: 0.078 ms
 Execution Time: 66.101 ms
   (7 rows)

You can check how different are now the statistics, first run read form disk, second one from buffer cache. All these lines only to say that in my opinion and for my experience, it is almost impossible to have a clear understanding about what  is happening in you environment without all the information around how you did any single explain. And even having all the informations, it is sometimes very difficult to find the answer because of all the variables playing  a role.
My 2 cents


Trouble Shooting "Unable to complete login process"

Troubleshooting this wasn't the simplest of processes, as it turned out to be a third party application killing the user. However there were clear indications in the SQL log that there were problems.

There were around 20 memory errors a day in the SQL Server log along the lines of "A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 7254 seconds. Working set (KB): 12756, committed (KB): 61520, memory utilization: 20%."

Using a product called ExtraHop (You can use your favorite network packet capturing tool) we were able to trace all the traffic going into and out of the server during the time frame of the 17052 error. This showed us that our monitoring software was connecting to the server and that a simple SELECT 1 was taking two seconds to complete.  A Change request was raised to increase the memory and then implemented and the errors went away.

I believe the monitoring software terminates its connection attempt to an SQL server; as the termination was coming from outside of SQL it would indeed be an unknown error.


How define select, if bridge pair may have 3 players?

This is the MySQL solution (very similar to Leigh's Oracle solution):

SELECT Pair
 , GROUP_CONCAT(name SEPARATOR ' - ') Names
FROM Members m
JOIN Players p ON m.player_id = p.id
GROUP BY Pair;

Obligatory sqlfiddle which was modified from Leigh's illustration as well.*

* I give credit when I plagiarize!


Order of operation with LTRIM/RTRIM/ISNULL

Your tests are redundant. First both LTRIM and RTRIM return NULL when given NULL input:

declare @Test1 varchar(16) = null;

select
    lft = LTRIM(@test1),
    rgt = RTRIM(@Test1);

lft              rgt
---------------- ----------------
NULL             NULL

Second, standard SQL ignores trailing spaces when comparing strings:

select
    A =case
        when '' = '   ' then 'equal'
    end,
    B = case
        when '  ' = '     ' then 'equal'
    end;

A     B
----- -----
equal equal

SQL Server provides the NULLIF function. This takes two parameters. If they differ the first will be returned. If they are equal it will return NULL. I think this will address your requirement.

declare @Test1 varchar(16) = '    ';

select
    first = nullif(@Test1, '');

set @Test1 = NULL;

select
    second = nullif(@Test1, '');

set @Test1 = 'some value';

select
    third = nullif(@Test1, '');

first
----------------
NULL

second
----------------
NULL

third
----------------
some value

You may still need the LTRIM, depending on your input validation.


powershell command enable-alwayson not work for SQL Server Named instance

Check out this documentation and tweak your command slightly.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/enable-and-disable-always-on-availability-groups-sql-server?view=sql-server-2017

The example shows this as the proper command for an instance

Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\Computer\Instance


Why write ahead logs in PostgreSQL are generated every second

Start with the manual page on Write Ahead Log

  wal_writer_delay (integer)
  Specifies the delay between activity rounds for the WAL writer. In each round the writer will flush WAL to disk. It then sleeps for wal_writer_delay milliseconds, and repeats. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of sleep delays is 10 milliseconds; setting wal_writer_delay to a value that is not a multiple of 10 might have the same results as setting it to the next higher multiple of 10. This parameter can only be set in the postgresql.conf file or on the server command line.

Other options on that page can also have an effect. Also consider that many small writes can easily be maintained but one big write can cause delays and increase the data lost if the power goes out.


Simple recovery mode with mirroring - is that possible?

Mirroring uses the transaction log to redo everything at the mirror site.  In simple recovery model, the transaction log is effectively transitory and only enough information is kept in the log for undo/redo to keep the database consistent during crash recovery. So Database mirroring is not possible in Simple Recovery model as it is not supported.


Add a New Column with ALIASES on 'date' by automation based on search results

Update status:-

As got advice and helpful from @ypercube

so far I got sorted with Fix static Pivot table but not dynamic Pivot table,

Here is a Static pivot table

    SELECT
    ls.sku                                AS list_sku,
    COALESCE( MIN(suo.sku), 'Not Sold' )  AS sold_sku,
    SUM(CASE suo.date WHEN '2018-04-30' THEN units_ordered ELSE 0 END) AS Monday,
    SUM(CASE suo.date WHEN '2018-05-01' THEN units_ordered ELSE 0 END) AS Tuesday,
    SUM(CASE suo.date WHEN '2018-05-02' THEN units_ordered ELSE 0 END) AS Wednesday,
    SUM(CASE suo.date WHEN '2018-05-03' THEN units_ordered ELSE 0 END) AS Thursday,
    COALESCE( SUM(suo.units_ordered), 0 ) AS total_sold
 FROM   tbl_list_sku AS ls
       LEFT JOIN tbl_sku_units_order AS suo
              ON suo.sku = ls.sku
GROUP  BY ls.sku
ORDER  BY total_sold DESC ;

Here is output results

So far I will update  this answers when I got a fix with Dynamic table

Update: Got fix with Dynamic Pivot Table and please with it

    SET
   @sql = NULL;
SELECT
   GROUP_CONCAT(DISTINCT CONCAT( 'MAX(IF(suo.date = ''', date, ''', suo.units_ordered, 0)) AS ', CONCAT("`", date, "`") ) ) INTO @sql
FROM
   tbl_sku_units_order;
SET
   @sql = CONCAT('SELECT ls.sku AS "List SKU",
 COALESCE( MIN(suo.sku), "Not Sold" ) AS "sold_sku", ', @sql, ' , COALESCE( SUM(suo.units_ordered), 0 ) AS total_sold
FROM
   tbl_list_sku AS ls
   LEFT JOIN
      tbl_sku_units_order AS suo
      ON suo.sku = ls.sku
GROUP BY
   ls.sku
ORDER BY
   total_sold DESC');
 PREPARE stmt
FROM
   @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is a result

I know I'm not great code in the world, but delight with results and thank for your help @ypercube.


No. Galera doesn't work with shared storage, as far as I know.

The way initial cluster members join is by basically blowing away the MySQL datadir, and rsyncing it over from another peer. That would not work out well with shared storage.

Plus, I don't think MySQL itself is really built for shared storage...


MERGE has more flexible OUTPUT. OUTPUT can refer to the merge source which is handy if you want the client to be able to match what it sent to what was actually inserted (e.g. IDENTITY values). INSERT can't do that (for no fundamental reasons; seems to be not implemented).

I can't think of any performance difference. The plans certainly look so similar that I can't make out a difference right now. If there was a major difference SQL Server could optimize one form to the other.


can I exclude or delete .pem files in backup data myqdl?

They are needed  for the mysql to generate nbew ssl keys

So to answer your question

  You have to decide if you want ssl or not, so if you use ssl and have user created some  client certificates with it you need it, if not you don't need them.

When you active ssl in my.ini

like

ssl=1
ssl-ca="\sslfolder\ca-cert.pem"
ssl-cert="\sslfolder\server-cert.pem"
ssl-key="\sslfolder\server-key.pem"

you can  allow a user only to access via ssl

First you generate a new ssl certificate for a user exampleuser

and add like this the use of ssl

CREATE USER 'exampleuser'@'localhost' IDENTIFIED BY 'secretsparrworf' REQUIRE X509;
GRANT ALL PRIVILEGES ON *.* TO 'exampleuser'@'localhost';

fionaööy you can access mysql via ssl like this

mysql -u exampleuser-p --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem


How to log which executed SQL statement is deleting records from SQL Server table?

You can use the help of SQL Profiler ..

Inside the profiler you can choose the events against your server..


You must install the PostGIS functions, data types, etc. before you can use PostGIS. This can be found in these three SQL-files. Set the correct path to the files and execute them in PostgreSQL by using psql.


Update a record whilst creating a link to a parent record

Change your statement to

UPDATE b
   SET b.ID = a.ID
  FROM tbl_Users        AS a
 INNER JOIN tbl_Absence AS b
    ON a.ID = b.ID
 WHERE b.ID      = 9999
   AND b.deleted = '2019-11-14 10:00:00'
   AND a.ID      = 9999;

(just the first line is changed - use the alias b (for tbl_absence) direct after the UPDATE

PS: the AND a.ID = 9999 is obsolete too, since is implied by the JOIN condition


How can I dump all tables to CSV for a PostgreSQL schema?

Here's a shell script that can do what you want:

SCHEMA="myschema"
DB="mydb"

psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\
  while read TBL; do
    psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV" $DB > $TBL.csv
  done

Make sure you set the DB and SCHEMA variables to your particular database and schema.

The wrapping psql command uses the A and t flags to make a list of tables from the string passed to the c command.


phpmyadmin not working after mysql update

Of the three, I prefer repair!

Looks like your phpMyAdmin was installed by the package manager, so the first thing to try is reconfiguring it. sudo dpkg-reconfigure phpmyadmin should accomplish that. Hopefully that will fix the error message you got that "phpmyadmin is broken or not fully installed" -- or if not, at least provide you with a more helpful message.

You may have other broken packages or missing dependencies, which you can try to fix with sudo aptitude -f. That will attempt to fix any broken packages.

I'm not really sure about your terminal error message. It appears that the /bin/ directory (which contains many of your system commands like ls, kill, mkdir, and many more), isn't in the list of directories used when looking for a command (the "path"). I'm hopeful that the aptitude -f will fix this too, because otherwise something really strange (and unrelated) happened to your bash configuration file.

Note that, while these are reasonably safe commands, I'm not responsible if your system explodes.


Answered in a comment by Rick James:
phpmyadmin does not have permission to see that database.  Change it to use root.


MongoDB Write Concern when Journaling is off

The answer to your question: Writing data change to disk.

When journaling is on, the first change is written to the journal, then it is written to data blocks.


ORDER BY on aggregate field on a closure table giving unexpected results

Your sortpath isn't technically being "changed" by the ORDER BY.  The correct values are all there, just not coming up in the order you anticipated.  In SQL, if you don't explicitly sort a set of data, the order in which it comes back to you is undefined... and the fact that it originally appeared to be in the anticipated order was more along the lines of coincidental.

I tweaked your fiddle (the "sorted" query):

GROUP_CONCAT(s.SortOrder ORDER BY a.PathLength DESC separator '') AS SortPath

Now, it looks like the results are being explicitly sorted in the order you expect, which is the order in which they were implicitly sorted before.

So why did that order change?  If you do an EXPLAIN SELECT on the "sorted" query with and without the ORDER BY clause, you'll notice that the ordering of the tables changes in the output of EXPLAIN depending on whether you specify ORDER BY on the entire query.  This is because the query optimizer re-orders the join-order of the tables to what it thinks is the most cost-effective sequence.

With the ORDER BY, the tables are joined from left to right, n-d-a-s.  Without the ORDER BY, the tables are joined from left to right, d-n-a-s.  Both are logically valid, but this causes the rows to be read from the tables in a different order, and your unsorted GROUP CONCAT's output order changes as a result of the change to the query plan.


What are the support windows for SQL Server 2012 SP3 and SP4 respectively?

SP4 for 2012 was announced recently and is due out next month.

See Microsoft's Support Lifecycle Policy documentation for end-of-support dates.  According to that document, SQL Server 2012 Service Pack 3 "mainstream support" ends on July 11, 2017.  Extended support for SQL Server 2012, including service pack 4, ends July 12, 2022.


Is it possible for MySQL v5.7.6 to to implement mesh replication with multi-source replication

It's called Galera.

A write to any node is automatically replicated to all other nodes.

Galera is usually deployed in 3-node configuration, but 4 is possible.

It is available from Percona (PXC), MariaDB, or an addon to MySQL.

Please explain why you want that configuration.

And, no, Galera won't get into an infinite loop.  Nor will any other multi-master setup -- as long as you set server-id to different values on each Master.


Postgres Windows post-install "The database cluster initialization failed"

There are two Windows binary packages available for download from the PostgreSQL site: one by BigSQL and one by EDB.

You don't tell us which one you tried, but my advice is to give up on the one that you are using and try the other one.


MYSQL: update field with random fixed char

solved with this:

update table1 set field1 = case floor(1 + rand()*2) when 1 then 'Y' else 'N' end;


In MySQL how do I grant a user access to a read replica only?

Master and slave instances can have different users and access permissions -replicated queries are executed by the SUPER user-, just create and/or modify the separate users with SET SESSION sql_log_bin = 0; every time (so you do not create conflicts on replication). Avoid any other process writing directly to the mysql.user (and other mysql.*) tables directly and you will be fine.

You probably want to set your slave as read_only and not provide SUPER privileges on anyone else there (as it will become out of sync if someone writes there and not to the master).


Convert YYYY-MM-DD date (without time zone) to unix timestamp in Postgres

Alright... after a change in my approach, I decided to try to "extract" data instead of convert. Turned out there's the oh-so convenient function extract in Postgres, which does exactly what I want.

extract(EPOCH from my_date_column) as date_timestamp

Here is a more detailed explanation.


SQL Server Indexing Performance in Unique/Duplicate column

The more unique the key is, the more you filter the data.
For example if you had an index on a field with only 2 unique values such as gender, the index would only split the search space in half or the number of records / 2.

Choosing to index a field with a greater amount of unique records (cardinality) will divide the search space further until you reach a unique key where every key maps to one record, which is the most efficient index.


Prevent SysAdmin from doing a Database Restore

First, you can't prevent a sysadmin from doing anything. :-) Any login that is a member of the sysadmin fixed server role operates outside the permissions system - they can do anything.

My first thought was to use a DDL trigger to stop the restore. Unfortunately, restoring a database is an operation that does not cause any triggers to fire (see this Connect item).

My next idea is to either have an alert on the restore event or have some job periodically looking at the msdb.dbo.restorehistory table so you can at least catch the restores quickly and work to fix them. Not ideal, I know.


At a guess - STUFF, QUOTENAME, and FOR XML PATH are being used to build an aggregated string out of the inner query - specifically, to build a column list for use in a later dynamic query. MySQL uses backticks (`) to "quote" DB object names, and there's a GROUP_CONCAT aggregate function built-in.

The CROSS APPLY isn't actually using anything from the query to determine its values - it appears to be a hard-coded sub-query, in which case you should be able to just put it in as a CROSS JOIN instead.

You can search this site or use your favorite search engine to find out how to do row numbers in MySQL. In fact, here's an answer I provided a while ago that shows you how to generate a row number that would've been done with a PARTITION BY in MS SQL.

Basically, you set up a variable to hold the row number. For each row, the variable's starting value will be the number of the previous row. You also store the current value of each partition column. If any of the partition columns change, set the row number variable to 1, otherwise, add one to it. Note that the query (or subquery) you generate the row number in must be sorted first by all the partition columns, then by the columns that would be in the OVER clause's ORDER BY using the MS SQL functions.

The one thing I can't see a way around easily is your use of a global temporary table (##NewGeneric). Technically that's not really a part of this code (it assumes it already exists and just uses it), but it's something you should at least be aware of. In MS SQL Server, a global temp table is created in one session, goes away when that session either drops it or closes (mostly), but allows other sessions to access it (and, if another session is using it when it should go away, it holds off until that session's current statement (transaction?) is over).

If you are really having other sessions access it, you'll have to use a regular table. The biggest differences should be that you have to be sure to drop it yourself, and you won't be able to drop it immediately if it's involved in a current transaction; your session will have to wait around until it's free to drop it.


Sql Server : How to query list of subscriptions and Articles (for a given publication) from Publisher

To get subscription list from a publisher database, you can run below tsql :

use publisherDB;
select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid

Incase if someone needs for merge replication :

use publisherDB
go
select distinct
    sa.name as articlename,
    sp.publisher as publisherserver,
    sp.publisher_db as publisherdb,
    sp.name as publicationname
    ,ss.subscriber_server as subscriberservername
from dbo.sysmergearticles sa
join dbo.sysmergepublications sp on sa.pubid = sp.pubid
join dbo.sysmergesubscriptions ss on ss.pubid = sa.pubid
order by subscriberservername


Insert multiple rows into a table with only an IDENTITY column

As of writing this, there is no way to insert multiple rows into just an IDENTITY column using an INSERT statement. The DEFAULT VALUES placeholder stands for just one row. And the INSERT ... SELECT syntax has no extension to support the same functionality as the DEFAULT VALUES clause.

Instead, you can use a MERGE statement to achieve the goal:

MERGE INTO
  dbo.Groups AS tgt
USING
  dbo.MySource AS src  -- << use your source row set here
ON
  1 = 0
WHEN NOT MATCHED THEN
  INSERT DEFAULT VALUES
OUTPUT
  INTO @output (ID)
;

The ON 1 = 0 clause basically turns the MERGE into a pure INSERT, because the explicitly false condition causes all the source rows not to be matched and thus trigger the WHEN NOT MATCHED THEN branch of the statement. Now, the WHEN NOT MATCHED THEN branch expects a single-row insert definition, where the familiar DEFAULT VALUES is perfectly valid. As the result, you effectively get an INSERT ... SELECT statement with the functionality of INSERT ... DEFAULT VALUES for an arbitrary number of rows.


Log Sequence Numbers in Full BackUp and Log Backup

LastLSN + 1 of the Full database backup will fall in between the FirstLSN and LastLSN of its subsequent transaction log backup.
(http://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/)


String manipulation seems to way to go here:

UPDATE myTable
  SET myDate = SUBSTR(myDate, 7, 4)
     || '-' || SUBSTR(myDate, 4, 2)
     || '-' || SUBSTR(myDate, 1, 2)


Attempting to tune a Sharepoint site using SQL Server Waits and Queues

As this is SharePoint your hands are tied.  You can't create indexes or statistics without loosing support from Microsoft.

CXPACKET just means that one thread of a parallized query is waiting for another thread to finish waiting for something else.  SOS_SCHEDULER_YIELD can mean a few things, typically it means that you need to fix your indexes are you have queries that aren't tuned correctly so the SQL Server is pausing those queries (yielding them) so that it can work on other stuff (as this is SharePoint you can't really do anything about this).  OLEDB means that the SQL Server is waiting on the OLEDB driver so probably there are linked server queries or something along those lines that are calling out to another database server.  There's nothing you can do about that either.

From what it sounds like there's about nothing you can do without throwing more hardware at the problem.  Use Profiler, Server Side Tracing or Extended Events and find the queries which are taking to long to run and check their execution plans and see how bad that are.  Odds are those search queries are doing large scan operations.

How big are the content databases?  Microsoft recommends keeping the content databases under 100 Gigs so that scanning is kept to a minimum.


SQL Server online maintenance - False publicity?

A lot of questions here. I have a feeling the answers won't make you happy, though.
DBCC CheckDB

To replicate the issues, I've been testing on a big database the dbcc checkdb, reorganize and rebuild of indexes and the update of statistics while I bombard the server with transactions on a database that is almost 1TB.

I don't know that I'd lumped DBCC CHECKDB with "online maintenance", but it certainly isn't an offline activity by nature. That being said, it can still be resource intensive which could manifest as an application "not working" although the database is technically still online and available.

I received a timeout from my application which has a the timeout set to 5 minutes...So how can one of my small transactions be blocked if it doesn't lock tables?

That doesn't indicate any blocking, especially since DBCC CHECKDB doesn't cause blocking unless you specify it should. You should take a closer look at monitoring the queries to see what the cause may be for the timeout - it could be a lot of things honestly.
Online Index Builds

My second test was to reorganize all indexes (which is also supposed to be online) while processing 124000 transactions, This time I got a deadlock...

Was the deadlock with the index reorganize statement, or another application statement? I can't recall a reorganize causing this issue ever for me, but deadlocks are a fact of life and the application should be able to gracefully retry when it encounters them. The operation being online in no way guarantees you won't get deadlocks.
Most people schedule index maintenance overnights/weekends to help avoid related issues around locking, blocking, and resource contention. Testing against a normal business hour workload is bound to give you problems like you're seeing.
Update Statistics

My third test was to update all statistics with maxdop=1 while running 124000 transactions. The error that was received in this case is: Could not continue scan with no lock due to data movement

This one's on you (or rather, the application): don't use NOLOCK and you won't get NOLOCK related errors. Again, not sure I'd recommend updating all stats on a busy system during high volume, so this seems like a test destined to have issues as well.
Technical Definitions

I've read multiple articles inclusing Paul Randal's explanation of online vs offline of the rebuild of indexes and understand the difference... but as I said, I'm being challenged to explain why it isn't really online although Microsoft claims it's online.

If you'd read this then you know that online doesn't mean no locks are taken. The definition of the word is highly contextual. You can point whoever you need to to the documentation on online rebuilds:

Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. In these rare cases, the SQL Server Database Engine will select the user or application activity as a deadlock victim.

Conclusion
All this is to say that regardless of maintenance operations being able to be "online", most people don't test them during full workloads nor expect them to work well during normal transactional volume.
Most of the time you really won't need to rebuild/reorg all indexes or update all stats at the same time, even during maintenance windows. If you have a VLDB, split out that DBCC CHECKDB into different windows (like Paul has covered so well).
I'd focus more on how/when these things will actually run. Use something like Ola's maintenance solution so you're not making SQL Server do more work than it has to.
And have your customer scale back their expectations!
See also

Why does create index with online block access to the table over a period of minutes?
How to reproduce "Could not continue scan with nolock due to data movement"
Bad Habits: Putting NOLOCK Everywhere
CHECKDB From Every Angle: Consistency Checking Options for a VLDB


Audit succesfull logons , loging every 2 seconds

I feel like this is normalish behavior but can't find any information on why. The most I see is that Microsoft's docs state that specification also logs connection reuse from the connection pool:
Events in this class are raised by new connections or by connections that are reused from a connection pool.

It does seem to be a common issue for people trying to audit logins in the same sense as you are, e.g. this Reddit post.
You may want to try Tony Hinkle's accepted answer on this StackOverflow as a workaround and see if you stop getting redundant logs in your audit. (The drawback with this solution is you have to specify each Login name you want to audit, but maybe this is a plus depending on your end goal.)


The following solution allows for storing/retrieving blobs externally, avoiding any duplications, stored in a tree of folders and files named after the blobs' own md5 hashes, in such a way the can be queried and manipulated as if they were simple database bytea columns. All files are stored in the folder $PGDATA/extstore.

Documents are saved to $PGDATA/extstore/db/zzz/yy/xxxxxx, where db is the current database name, zzz is a specific separate folder (optional), yy are the first X digits of the blob's md5 hash, and xxxxxx is the blob's md5 hash. The higher the value of X, the smaller the number of files per folder, i.e. for X=1, all files are distributed in 16 folders (named 0 to f), for X=2, all files are distributed in 256 folders (named 00 to ff), and so on.

There is a chance that two different files will produce the same MD5 hash (and therefore one would overwrite the other), however the chance is indeed very small, 1 in 2^128: not impossible, but incredibly unlikely.

I've created all of the following functions in a separate schema, called extstore:

CREATE SCHEMA IF NOT EXISTS extstore;

The following functions save a file inside the "extstore" repository:

SET ROLE postgres;
CREATE OR REPLACE FUNCTION extstore.f_save_file_pl(text, int, text, bytea) RETURNS void AS $$
  use strict;

  my($folder, $level, $md5, $contents) = @_;

  my $rv = spi_exec_query('SELECT current_catalog AS db');
  my $db = $rv->{rows}[0]->{'db'};

  $md5 = lc $md5;
  $md5 =~ '^[0-9a-f]{32}$' or die 'Malformed MD5';
  $level >= 0 && $level < 4 or die 'Invalid level';

  my $err = 'Cannot create folder ';

  my $dir = 'extstore';
  if (! -d $dir) { mkdir $dir or die "$err$dir"; }

  $dir .= "/$db";
  if (! -d $dir) { mkdir $dir or die "$err$dir"; }

  if ($folder) {
    $folder =~ s/\//-/g;
    $dir .= "/$folder";
    if (! -d $dir) { mkdir $dir or die "$err$dir"; }
  }

  if ($level) {
    my $subf = substr $md5, 0, $level;
    $dir .= "/$subf";
    if (! -d $dir) { mkdir $dir or die "$err$dir"; }
  }

  my $file = "$dir/$md5";
  if (! -f $file) {
    open(my $out, '>:raw', $file) or die "Unable to create file $file: $!";
    print $out decode_bytea($contents);
    close($out);
  }

  return;
$$ LANGUAGE plperlu SECURITY DEFINER;

CREATE OR REPLACE FUNCTION extstore.f_save_file(p_folder text, p_level int, p_data bytea) RETURNS text AS $$
SELECT md5
FROM (
  SELECT md5, extstore.f_save_file_pl($1, $2, md5, $3) AS none
  FROM (
    SELECT md5($3) AS md5
  ) a
) a;
$$ LANGUAGE SQL SECURITY DEFINER;

The following functions read a file from the "extstore" repository:

SET ROLE postgres;
CREATE OR REPLACE FUNCTION extstore.f_read_file_pl(text, int, text) RETURNS bytea AS $$
  use strict;

  my($folder, $level, $md5) = @_;

  my $rv = spi_exec_query('SELECT current_catalog AS db');
  my $db = $rv->{rows}[0]->{'db'};

  $md5 = lc $md5;
  $md5 =~ '^[0-9a-f]{32}$' or die 'Malformed MD5';
  $level >= 0 && $level < 4 or die 'Invalid level';

  my $dir = 'extstore';
  if (! -d $dir) { return undef; }

  $dir .= "/$db";
  -d $dir or return $dir;

  if ($folder) {
    $folder =~ s/\//-/g;
    $dir .= "/$folder";
    -d $dir or return undef;
  }

  if ($level) {
    my $subf = substr $md5, 0, $level;
    $dir .= "/$subf";
    -d $dir or return undef;
  }

  my $file = "$dir/$md5";
  -f $file or return undef;

  open(my $out, '<:raw', $file) or die "Unable to open file $file: $!";
  local $/ = undef;
  my $contents = encode_bytea(<$out>);
  close($out);

  return $contents;
$$ LANGUAGE plperlu SECURITY DEFINER;

CREATE OR REPLACE FUNCTION extstore.f_read_file(p_folder text, p_level int, p_md5 text) RETURNS bytea AS $$
SELECT extstore.f_read_file_pl($1, $2, $3);
$$ LANGUAGE SQL SECURITY DEFINER;

The following functions obtain information of files stored in extstore:

CREATE OR REPLACE FUNCTION extstore.f_get_file_size(text, int, text) RETURNS int AS $$
  use strict;

  my($folder, $level, $md5) = @_;

  my $rv = spi_exec_query('SELECT current_catalog AS db');
  my $db = $rv->{rows}[0]->{'db'};

  $md5 = lc $md5;
  $md5 =~ '^[0-9a-f]{32}$' or die 'Malformed MD5';
  $level >= 0 && $level < 4 or die 'Invalid level';

  my $dir = 'extstore';
  if (! -d $dir) { return undef; }

  $dir .= "/$db";
  -d $dir or return $dir;

  if ($folder) {
    $folder =~ s/\//-/g;
    $dir .= "/$folder";
    -d $dir or return undef;
  }

  if ($level) {
    my $subf = substr $md5, 0, $level;
    $dir .= "/$subf";
    -d $dir or return undef;
  }

  my $file = "$dir/$md5";
  -f $file or return undef;

  return -s $file;
$$ LANGUAGE plperlu SECURITY DEFINER;

CREATE OR REPLACE FUNCTION extstore.f_get_files(text) RETURNS SETOF text AS $$
  use strict;

  my($folder) = @_;

  my $rv = spi_exec_query('SELECT current_catalog AS db');
  my $db = $rv->{rows}[0]->{'db'};

  my $dir = 'extstore';
  if (! -d $dir) { return undef; }

  $dir .= "/$db";
  -d $dir or return $dir;

  if ($folder) {
    $folder =~ s/\//-/g;
    $dir .= "/$folder";
    -d $dir or return undef;
  }

  open(my $out, '-|', "/bin/find '$dir' -type f -printf '%f\\n'") or die "Unable to list folder $dir: $!";
  while(<$out>) {
    chomp;
    return_next($_);
  }
  close($out);

  return undef;
$$ LANGUAGE plperlu SECURITY DEFINER;

For example, the following small text document

abcd1234
ASDF

has an md5 hash of '8cfc4f81e28149a4d4c79dae83d7ceb3', and the following function call from the database template1

SELECT extstore.f_save_file('scans', 2, 'abcd1234\015\012ASDF'::bytea);

saves it in $PGDATA/template1/scans/8c/8cfc4f81e28149a4d4c79dae83d7ceb3.

The file can then be read via:

SELECT extstore.f_read_file('scans', 2, '8cfc4f81e28149a4d4c79dae83d7ceb3');

The size of the blob can be queried without having to read the file to memory and calculate the size of the blob, by simply getting the file size from the filesystem:

SELECT extstore.f_get_file_size('scans', 2, '8cfc4f81e28149a4d4c79dae83d7ceb3');

Finally, the following function will produce a list of all file names (i.e. the blobs' MD5 hashes) within a folder (in this case $PGDATA/extstore/template1/scans):

SELECT * FROM extstore.f_get_files('scans');


ibdata1 increases when firing update queries

Lets try to understand whats stored inside ibdata1:
When you have innodb_file_per_table enabled, the tables are stored in their own tablespace but the shared tablespace is still used to store other InnoDB’s internal data:
* data dictionary
* change buffer
* doublewrite buffer
* undo logs

since there is trancations, rollbacks that support repeatable read that will make ibdata1 files grow even when you update any innodb table.

reference:
http://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

ibdata1 grows exponentially when innodb_file_per_table is configured

https://serverfault.com/questions/272146/why-ibdata1-is-still-growing-when-i-have-used-innodb-file-per-table-in-mysql-5-1


Confirmation of SQL statement disappeared

If the feedback on SQL*Plus is set to off then you don't get the confirmation messages.

SQL> set feedback off
SQL> create user c##u1 identified by user1;

set feedback on.

SQL> set feedback on
SQL> drop user c##u1;

User dropped.

SQL> create user c##u1 identified by user1;

User created.

Documentation: SET FEEDBACK


Pagination while sorting a non unique text column

One solution that performs well would be being smart with indexes.

First, create an index on two columns, one being your non unique text column and a second one being your id column (it can be any column that can be differentiated between non unique text column rows):

CREATE INDEX user_name_uuid_idx
    ON public.user USING btree
    (name COLLATE pg_catalog."default" ASC NULLS LAST, uuid ASC NULLS LAST)
    TABLESPACE pg_default;

NOTE: the order of columns does matter. Use the same order that is used in the select query

Then use this query to get x page.

SELECT * FROM user WHERE (name, uuid) > ('Misty Wyman', '070d905d-2155-492f-b811-2c9d30015299') ORDER BY name, uuid LIMIT 20;


Whats the difference between information_schema.user_privileges and mysql.user?

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

Source - http://dev.mysql.com/doc/refman/5.5/en/information-schema.html

You should be taking at the proper commands for modifying accounts and flush PRIVILEGES to reload the PRIVILEGES.

http://dev.mysql.com/doc/refman/5.5/en/account-management-sql.html
http://dev.mysql.com/doc/refman/5.5/en/flush.html


Oracle 19c installation stuck at creating and starting oracle instance

Two of the most common reasons for the Oracle DB installation to get stuck at this point are:

inability to write to %TEMP% directory (usually as a result of AD domain policies or a full disk)
virus check software (other than Windows Defender) interfering with the installation process

If you need to cancel the installation and try again, be sure to confirm that the %TEMP% directory on your Windows box does not have any leftover directories prefixed with deinstall, OraInstall or CVU, as these may be reused by future installations, creating a different set of issues.


Which is quicker: Select of existing row vs Update where no row exists?

Can you not use the MERGE statement added with SQL Server 2008 to "UPSERT" in one atomic operation?

DECLARE @FilterValue INT

;MERGE
INTO ChildFilterTable AS CFT
USING  (your filter, source thing here)
                  ON (CFT.ChildID = ...)
WHEN MATCHED
    THEN update stuff
WHEN NOT MATCHED BY TARGET
    THEN insert stuff;