T-SQL - Enforce Atomic Operations x Locks

There are two different isolation levels you can use within a session that will insure that the data read at the beginning of the transaction doesn't change until the transaction with one of the isolation levels mentionned below is committed

BOL article on Isolation Levels

Serializable

Statements cannot read data that has been modified but not yet committed by other transactions

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

REPEATABLE READ

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

The main difference between the two, is that the serializable isolation level prevents inserting new data anywhere within the range of rows.

USE MYDATABASE
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
BEGIN TRANSACTION;
sp_insert_operation
COMMIT TRANSACTION

There is also the option of setting snapshot isolation at the database level, but I don't believe that would give you the level of isolation you are looking for.

Here is the relevant documentation ALTER DATABASE look under snapshot isolation

Update on Serializable

http://blogs.msdn.com/
Serializable isolation level relies on pessimistic concurrency control.  It guarantees consistency by assuming that two transactions might try to update the same data and uses locks to ensure that they do not but at a cost of reduced concurrency - one transaction must wait for the other to complete


Granting Permission on Databases and Schemas

Your script is assigning the user to the db_owner role.

All other permissions being applied are therefore redundant as this implies ALL of those other permissions.


Let's start by looking at the results of sp_blitzindex:

Lock escalation attempts: 252,312; Actual Escalations: 6

What is lock escalation? The first sentence from that page explains it pretty well:

Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead while increasing the probability of concurrency contention.

Here are a few other important quotes:

The Database Engine does not escalate row or key-range locks to page locks, but escalates them directly to table locks. Similarly, page locks are always escalated to table locks.
When the Database Engine checks for possible escalations at every 1250 newly acquired locks, a lock escalation will occur if and only if a Transact-SQL statement has acquired at least 5000 locks on a single reference of a table.

You're deleting 2 million records so I'm not surprised that you're getting over 5000 locks during your deletes. Less than 0.003% of your lock escalation attempts were successful for this object so there appears to be a lot of concurrent access. It's important to think about what blocking represents for your workload. Blocking can be good if it prevents an end user from getting incomplete or inaccurate results. I'm going to break down the blocking behavior into two categories: it occurs during a maintenance window (end users not on the system) or it occurs outside of a maintenance window.
If it occurs during a maintenance window that's good news for you. You just need to adjust your code and won't need to make difficult decisions about the end user experience. If you have multiple processes all issuing deletes on many tables you have at least the following options:

Delete in small batches instead of doing one large delete. You can delete the top X rows until there aren't any more rows to delete. This performs very well if you are deleting based on the clustered key (you are in your example). As long as your batch size is small enough to avoid
Prevent more than one process from accessing the same table. This could be accomplished through sp_getapplock, by only running one process (may not be practical for your maintenance window), or through some other method.
Partition by user id. This is a bit of a nuclear option but you can use partition switching to load the new data in very quickly with minimal locking. Tinyint has a range up to 32767 and SQL Server only supports up to 15000 partitions per table so this may not work for you. Partitioning has a lot of other side effects so consider making this change very carefully.

If this blocking is occurring while end users are accessing the system and you cannot defer the updates to your data until your maintenance window then I recommend approaching the problem based on what your end users should experience. I get the impression that you're deleting data from many different tables for a user id. What should happen if an end user runs a query after the delete but before the insert has completed? Is it acceptable to show partial data?  What should happen if some of the tables in the query have been updated with the new data but not all? Is it better to make them wait until all of the data has been loaded or to show them the previous data?
I can't answer those questions so some combination of the techniques listed above may work for you. Another one worth considering is to defer doing the deletes. Instead of doing deletes you could do inserts instead. You could add a version column to all of your primary keys and load in new data as needed. You could also have a metadata table that lists the version of the data that should be used for each user id as well (that can be helpful if you want an end user to wait until all data has been loaded). During a maintenance window you can delete the old rows. Of course, this will require you to rewrite many, if not all, of your queries and to recreate the primary keys on all of your tables.
Hopefully that gets you on the right track. Resolving your issue may be as simple as deleting in smaller chunks to avoid escalating locks to the table level but I urge you to think carefully about what data end users will see as a result of doing that.


BETWEEN is inclusive. From the documentation:

  And the value of

expr1 BETWEEN expr2 AND expr3

  is the value of the boolean expression:

expr2 <= expr1 AND expr1 <= expr3

So your first clause will match exactly T1, exactly T2, and anything in between. Your second clause will match exactly T1, i.e. any rows where DATETIME is exactly T1.


pgAdmin on a fresh OS X Server installation

Postgresql, just as most databases, can be accessed locally (on the local machine) and on TCP/IP, from remote machines. There is a common phenomenon that a database is configured by default for local connections.

The local connections can happen

on unix sockets, these are very like TCP ports, but they have a filesystem path instead of a port number. In case of PostgreSQL, the commons settings are 5432 for the TCP port and /var/run/postgresql/.s.PGSQL.5432.
On TCP ports, which is by default 127.0.0.1:5432, and is often by default turned off.  To turn it on, specify port = '5432' in postgresql.conf.

Most clients - php postgresql module, or the command line psql command - connecting the local server on unix sockets by default.

pgAdmin is a special thing, it wants to connect only to TCP ports, even if it runs on a localhost. It doesn't mean anything on your system were bad, this tool was simply so written, that it can use only TCP even for localhost administering.

You had to enable the TCP port listening in postgresql config file pg_hba.conf. The following line will be probably okay:

host all all 127.0.0.0/8 trust

The official documentation for this pg_hba.conf can you find here, and a short tutorial to enable the localhost networking can you find here.

If it don't work or you want better answer, it could help a lot if you extended your question with your actual pg_hba.conf.

Good luck!


Error: File '' couldn't be removed (error 2: No such file or directory)

This bug was reported by Byaruhanga Franklin to the mailing lists,

  I also tried with pgsql and pg_restore didn't get any error. i then changed to a lower version of PGADMIN3 1.18 still didn't get any error. i then built the new version 1.22.0 Alpha 2 from the git repository. this also has no error. i guess the problem is limited to only pgAdmin III Version 1.20.0.

Upgrade to PgAdmin3 1.22, or to PgAdmin4.


failed logon trigger blocking access into SQL Server instance

And any suggestions how to fix this in order to be able to login again
  into the instance will be very appreciated.

You should disable your trigger:

disable trigger tr_recorder on all server;

You should be able to do it without problem if you log in as sysadmin because if your trigger is exactly as you posted, it will be executed without errors by sysadmin.

Or you should do it using DAC, i.e. you should connect to server using -A, in case your trigger is different and even sysadmin cannot login now.

Here you can find step to step instruction with screenshots:
SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem.

  I want to know why do logins fail (is my trigger doing something
  wrong?).

Your problem can be the following: your trigger tries to insert into a table in master database but usually users don't have any permission in master. Unless the login is sysadmin or is mapped to master explicitely, it is guest in master and has no permission on any user table in master.

To fix the issue you can use execute as clause in your logon trigger, this way the trigger will be executed with the permissions of the login that you put in execute as clause.

One osservation: if all you wanted to do is to record every successful login to server you can do it by changing login auditing:

This way you'll get all the successful logins in SQL Server errorlog.


The equivalent of TRY-CATCH error handling in PostgreSQL is a block of code in this way:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

Have a look at Postgres docs about Trapping errors

If you want to use it in your functions, keep in mind it can only be used inside PL/pgSQL functions.


So I'm answering this question almost 4 years late:

InnoDB file formats were conceived at a time when InnoDB was independent of the MySQL Server (for example: MySQL 5.1 could run two different versions of InnoDB).
The reason why you would not want to run Barracuda (in 2012) is that it could reduce flexibility in downgrading MySQL (i.e. after a failed upgrade, you want to move back to a version that can not read a newer format).  i.e. there should be no technical reasons why Antelope is better.
In MySQL 5.7 the innodb_file_format option was deprecated.  Since MySQL and InnoDB are no longer independent, and thus InnoDB can use the MySQL rules of upgrades and what backwards compatibility is required.  No confusing setting required!
In MySQL 5.7, the default switched to Barracuda/DYNAMIC.  Since all currently supported releases of MySQL can read this format, it is safe to move away from Antelope and still offer downgrade.
On a MySQL 5.7 server, Antelope tables will be upgraded to Barracuda/DYNAMIC on the next table rebuild (OPTIMIZE TABLE etc).  That is unless they were specifically created with ROW_FORMAT=oldrowformat.
In MySQL 8.0, the option innodb_file_format is removed.

MySQL 5.7 also introduces the option innodb_default_row_format, which defaults to DYNAMIC.  This addresses the point in your update.


The documentation of extract clearly states:

  For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval

If you want to know how many seconds passed since Jan. 1st 1970 in your time zone, use

extract(epoch FROM localtimestamp)

The result of AT TIME ZONE, when applied to a timestamp with time zone, is always a timestamp without time zone.  extract will interpret such a time stamp in your current time zone (it does not know about the second argument you passed to AT TIME ZONE).

Your comment suggests that you want to get the (absolute) epoch at the beginning of the current day in your time zone. That would be

extract(epoch FROM date_trunc('day', current_timestamp))


The solution I have found for my problem with the help of @jkavalik was to:

Move the DELETE statement into a DB Event that runs every minute.

CREATE
  DEFINER = 'admin'@'%'
EVENT backup.DeleteProcessedRecords
  ON SCHEDULE EVERY '1' MINUTE
  STARTS '2015-10-20 11:10:18'
  ON COMPLETION PRESERVE
  DO
BEGIN
  DELETE FROM backup.records WHERE readyToBeDeleted = 1 ORDER BY deviceId;
END

add the ORDER BY deviceId to the DELETE Statement.
change the Index IDX_records_readyToBeDeleted from (readyToBeDeleted) to (readyToBeDeleted,deviceId).

Now I am waiting to see if I still get as many deadlock as I had before. I will keep you posted @jkavalik. And once more thank you for your expertise.


It is possible to take incremental backup in mongoDB Shard cluster? without opsmanager

Consider using Percona tool if you are using MongoDB version above 3.6. Taking dump of individual shards and trying to stitch it together while restoring is not advisable.


Database design for actions on some entity

You want to track changes to the items at the item level so your item table would have a key that represents the 'item_id' and something else that distinguishes each state change for that item - you could use a timestamp or a counter or a flag. Let's generically call it 'version attribute'.

  Table Design

[LIST]

    LIST_ID
    LIST_NAME
    -- other attributes

[ITEM]

    ITEM_ID
    ITEM_NAME
    LIST_ID -- FK to LIST.LIST_ID
    ACTION_CODE
    -- version attribute (read below)
    -- other attributes

Your LIST to ITEMS relationship would be a one-to-many relationship.

  Sample Data [version attribute omitted]

LIST
1    List-1

ITEM
11   Item-1    1     A -- say added
12   Item-2    1     A
13   Item-3    1     A
11   Item-1    1     D -- say deleted
13   Item-3    1     C -- say changed

Now there are 2 ways you could track the latest state.

1) Maintain the order of timestamps or counters (if you choose this method) and pick the latest for each item of the list. [Not my preferred way]

2) Add a flag column to your items list say CURR_VER_FLAG and for a given item always pick the record that has CURR_VER_FLAG = 'Y'.

This flag is set to 'Y' (or true if boolean) on the addition of a new
item.
When there are any changes to that item you do

update ITEM set CURR_VER_FLAG = 'N' where ITEM_ID = ? and LIST_ID = ? and CURR_VER_FLAG = 'Y'

and then insert the new record with CURR_VER_FLAG = 'Y'

Your ITEM table would now be

ITEM
    11   Item-1    1     A   **N**
    12   Item-2    1     A   **Y**
    13   Item-3    1     A   **N**
    11   Item-1    1     D   **Y**
    13   Item-3    1     C   **Y**-- say changed

I haven't shown it but it should be clear that item_id can no longer be a primary key of this table. You would have to add another distinguishing column probably a surrogate key.

Now you can get

all actions performed on a list
state changes for any item of a list
final state of a list

Note
I haven't considered performance here but with right indexes that shouldn't be an issue.


(SQL) User tables are separated into two sections

That is just the paging in SQL*Plus. By default the paging value is 14, that includes a blank line at the start of each page, the line with column headers, the line with heading separators (hyphens) and the rest (11 rows) is for user data.

If you increase the pagesize, the result of your query will fit in to 1 page:

set pagesize 20

It has nothing to do with php/OCI.

SET PAGES[IZE] {14 | n}


PostgreSQL - cannot create RULE from stored procedure

Thank you everyone for suggestions in comments. I've found out two solutions.

Use SQL stored procedure instead of PL/pgSQL:

    CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
      RETURNS VOID AS $test_create_rule_from_stored_procedure$
    CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
    DO INSTEAD
      INSERT INTO person_y (
        firstname,
        lastname)
      VALUES (
        new.firstname,
        new.lastname
      );
    $test_create_rule_from_stored_procedure$ LANGUAGE SQL;

Stay with PL/pgSQL but specify query for creating rule as string literal:

    CREATE OR REPLACE FUNCTION test_create_rule_from_stored_procedure()
      RETURNS INTEGER AS $test_create_rule_from_stored_procedure$
    BEGIN
      EXECUTE 'CREATE OR REPLACE RULE person_ins AS ON INSERT TO person_x
      DO INSTEAD
        INSERT INTO person_y (
          firstname,
          lastname)
        VALUES (
          new.firstname,
          new.lastname
        );';

      RETURN 1;
    END;
    $test_create_rule_from_stored_procedure$ LANGUAGE plpgsql;


Error Querying MySQL server linked to SQL Server 2008

it looks like a permission issue with the authentication mode you're using against your mysql server. You can test the linked servers using the stored procedure "sp_testlinkedserver" - details here. See if you can take data from your linked server using simple select statement: "select top 10 * from server.database.schema.table".

The MS error is for the action "Script as" - when you want to script the schema of an object, not for simply selecting data.


Query tables with information_schema in pgsql

Sounds like you need dynamic SQL for this. First build your query using string concatenation with the desired values (e.g. schema name, table name), then execute it with EXECUTE.

Example:

    -- create example objects
    CREATE SCHEMA example_schema;
    CREATE TABLE example_schema.example_table1 (example_column integer);
    CREATE TABLE example_schema.example_table2 (example_column integer);
    CREATE TABLE example_schema.example_table3 (example_column integer);

    -- example anonymous block using dynamic queries
    DO
    $$
    DECLARE
      dynamic_query text;
      catalog_row record;
      counter integer = 0;
    BEGIN
      -- insert an integer, that's incremented for every iteration into the example tables
      FOR catalog_row IN SELECT table_schema || '.' || table_name qualified_table_name
                                FROM information_schema.tables
                                WHERE table_schema = 'example_schema'
                                      AND table_name LIKE 'example_table%' LOOP
        counter := counter + 1;
        -- building the query;
        dynamic_query := 'INSERT INTO ' || catalog_row.qualified_table_name || ' VALUES (' || counter || ')';
        -- executing the query;
        EXECUTE dynamic_query;
      END LOOP;
    END;
    $$
    LANGUAGE plpgsql;

    -- check if it has worked
    SELECT * FROM example_schema.example_table1
    UNION ALL
    SELECT * FROM example_schema.example_table2
    UNION ALL
    SELECT * FROM example_schema.example_table3;


The following setup is possible, without using TLS.
Datacenter 1 is the primary datacenter. All clients connect only to this one.
Datacenter 2 is only for realtime backups via replication.
On-Site is also only for realtime backups via replication.
This setup is NOT meant for failover, only for disaster recovery.
Datacenter 1:
MongoDB

mongod --bind_ip 127.0.0.1,172.17.0.1 --replSet test --port 2000
--rest --httpinterface --logpath data/replica/logs/log.txt --dbpath data/replica/wiredTiger --directoryperdb --storageEngine wiredTiger
--wiredTigerDirectoryForIndexes --fork

127.0.0.1 is for SSH access, 172.17.0.1 is for access from Docker containers.
SSH Tunnels (this server is responsible for creating the tunnels to Datacenter 2):

autossh -f -N -L 2001:localhost:2001 mongodb@dc-2.example.com
autossh -f -N -R 2000:localhost:2000 mongodb@dc-2.example.com

Datacenter 2:
MongoDB

mongod --bind_ip 127.0.0.1 --port 2001 --replSet test --rest
--httpinterface --logpath data/replica/logs/log.txt --dbpath data/replica/wiredTiger --directoryperdb --storageEngine wiredTiger
--wiredTigerDirectoryForIndexes --fork

On-Site:
MongoDB

mongod --bind_ip 127.0.0.1 --port 2002 --replSet test --rest
--httpinterface --logpath data/replica/logs/log.txt --dbpath data/replica/wiredTiger --directoryperdb --storageEngine wiredTiger
--wiredTigerDirectoryForIndexes --fork

SSH Tunnels (On-Site is responsible for creating the tunnels to Datacenter 1, no persistent connection exists between Datacenter 2 and On-Site)

autossh -f -N -R 2002:localhost:2002 mongodb@dc-1.example.com
autossh -f -N -L 2000:localhost:2000 mongodb@dc-1.example.com

The server in Datacenter 1 contains all the data which is to be replicated, Datacenter 2 and On-Site are empty databases.
On the machine in Datacenter 1 I enter mongo --port 2000, then I issue a
rs.initiate(
   {
      _id: "test",
      version: 1,
      members: [
         { _id: 0, host : "localhost:2000", priority: 1, votes: 1 },
         { _id: 1, host : "localhost:2001", priority: 0, votes: 0, hidden: true },
         { _id: 2, host : "localhost:2002", priority: 0, votes: 0, hidden: true }
      ]
   }
)

I guess priority: 0, votes: 0, hidden: true on the secondaries is optional here, it does what I want, just have the two secondaries be a part as silent, invisible data collectors for backup purposes. I'm not sure if changing this would have any side-effects as it could be possible that then a connection between both secondaries should be available, which it is not, in this case (they can't see each other).
I could use a slaveDelay on one of the hidden members in order to make it lag for let's say half a day, but since I will be making plenty of backups from both hidden databases via mongodump, it's not really neccessary.
In order to be able to connect to the secondaries via mongodb tools I needed to issue a rs.slaveOk()
Nothing had to be changed in the clients, they connect to 172.17.0.1:2000.
I tested a reboot of the machine in Datacenter 2 and during the reboot I inserted new items in the DB, after about 30 seconds the rebooted machine synced to Datacenter 1 as expected.
Datacenter 1 has a fast server with lots of RAM and CPU and only a little bit of SSD storage, Datacenter 2 has a little bit of RAM but lots of HDD, so this seems to be a good solution.


How can I make multiple joins on the same table?

I think this does what you need

 SELECT s.Id, eeav.Name, IFNULL(ee.Name, ee2.Name) AS 'Name' FROM signal s
 LEFT JOIN equipmententityactualvalue eeav ON (s.Eeav = eeav.Id)
 LEFT JOIN equipmententity ee ON (eeav.Ee = ee.Id)
 LEFT JOIN equipmententity ee2 ON (s.Ee = ee2.Id)


Migration of create statement from SQL Server to MySQL

This answer will apply to your question : Insert Value based on One Column based on Value on another Column

AFAIK, and based on the link posted, you cannot create a computed column in MySQL.  You should either:

Do the calculation outside of the database, when inserting OR
Create a trigger on the table to populate the value for you OR
Create a view to represent the data in the way you want.


SQL Server: How do you prevent data being modified through an exposed view?

This is handled through the view's and table's security. Grant only SELECT permission to the view and the underlying tables.

Without write access, INSERTs, UPDATEs and DELETEs through the view will fail.

If the users have access through a group, and you want the group to still have access but not these specific users, you could use the DENY permission since DENY has precedence over GRANT.


Remove duplicate comparisons in postgres?

You can use least() and greatest() for this:

select distinct least(s1.id, s2.id), greatest(s1.id, s2.id)
from scale_polygons_v4 s1
  join scale_polygons_v4 s2
    on s1.id <> s2.id
   and s1.kind = s2.kind
   and st_overlaps(s1.geom,s2.geom);

Another option is to do this in the join condition:

select distinct s1.id, s2.id
from scale_polygons_v4 s1
  join scale_polygons_v4 s2
    on s1.id > s2.id
   and s1.kind = s2.kind
   and st_overlaps(s1.geom,s2.geom);


How to pass application user names to the database server for audit purposes?

If I understand your question correctly, you are asking how to pass an application user name to MySQL to be recorded in an audit table.
You can use a user-defined variable, which is local to the session in which it is created. Your application code will set it to the name of the logged-in user once it is authenticated. Your audit code can then reference that variable and retrieve the user name.
dbfiddle


How to manipulate bytea data when using backup script as a template

Instead of using...

COPY example_table (id, ip_address, domain, configuration) FROM stdin;
1   0.0.0.0 invalid.domain.com  \x7b277265666572656e63655f746f5f69705f61646472657373273a27302e302e302e30272c277265666572656e63655f746f5f646f6d61696e273a27696e76616c69642e646f6d61696e2e636f6d277d
\.

... I think you should be able to change your pattern, and do something like ...

INSERT INTO
    example_table
    (id, ip_address, domain, configuration)
VALUES
    (1, '0.0.0.0', 'invalid.domain.com',
    $quote${"reference_to_ip_address":'0.0.0.0',
            "reference_to_domain":'invalid.domain.com'}$quote$::bytea) ;

... or, if you want to make things a little simpler, use a Common Table Expression, and you will have to change settings only in ONE place:

WITH reference_data(id, ip_address, domain) AS
(
    -- This is the only place that needs changing...
    VALUES (1, '0.0.0.0', 'invalid.domain.com')
)
INSERT INTO
    example_table
    (id, ip_address, domain, configuration)
SELECT
    id, ip_address, domain,
    ('{"reference_to_ip_address":''' || ip_address || ''',' ||
     '"reference_to_domain":''' || domain || '''}')::bytea
FROM
    reference_data ;

You can check the data

 SELECT
     id, ip_address, domain, convert_from(configuration, 'utf8')
 FROM
     example_table ;

 id | ip_address | domain             | convert_from
 -: | :--------- | :----------------- | :-------------------------------------------------------------------------------
  1 | 0.0.0.0    | invalid.domain.com | {"reference_to_ip_address":'0.0.0.0',"reference_to_domain":'invalid.domain.com'}
  1 | 0.0.0.0    | invalid.domain.com | {"reference_to_ip_address":'0.0.0.0',"reference_to_domain":'invalid.domain.com'}


SQL Server tempdb log file isn't autogrowing, instead getting error “The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'”

This is happening because the disk is full.

I was given bad information. The primary DBA was out when I had this problem and I asked one of our secondary DBAs to check the disk space on the drive where the tempdb log file lives because I don't actually have access to this server. The secondary DBA told me that there was 25GB free space on that drive. In actuality, there was less than 10MB free space.


Ascending Key Problem - Leading column branded "Stationary" - SQL Server

There doesn't seem to be much guidance on what results in a branding of Stationary, however I did find KB2952101 that says if less than 90% of inserts were greater than the old maximum value, it would be classed as Stationary. All our inserts are new submissions, and the leading column is a bigint IDENTITY column, so 100% of inserts should be greater than the maximum previous value.
So my question is why would the column be branded as Stationary, when it is obviously Ascending?

It'll be branded stationary if, like you've already stated, that 10% or more of the inserts aren't ascending. If 100% of your inserts were as you say... then you might not have this problem, until of course you delete but then it would go back to unknown.
Here is a repro of your issue:
use master;
go
-- create a database for this to test
create database AscendingKey;
go

use AscendingKey;
go
-- create a test table
create table dbo.AscendingKeyTableTest
(
    SomeData        char(100) default('A'),
    AscendingKey    bigint not null,
);
go

-- insert some dummy data
set nocount on
go

declare @i int = 1

while(@i <= 1000)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i);
    set @i += 1
end
go

-- create stats on the ascendingkey column
create statistics AscendingKeyStats on dbo.AscendingKeyTableTest(AscendingKey);
go

-- look at the stats
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
-- unknown

-- now insert a few more ascending
declare @i int;
declare @j int = 1;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

while(@j <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+@j);
    set @j += 1
end
go

-- check again
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
-- unknown

-- update the stats
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

-- now insert a few more ascending
declare @i int;
declare @j int = 1;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

while(@j <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+@j);
    set @j += 1
end
go

-- update the stats
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

-- check again
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);

-- now insert a few more ascending
declare @i int;
declare @j int = 1;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

while(@j <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+@j);
    set @j += 1
end
go

-- update the stats
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

-- check again
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
-- ascending!
-- we hit the 3x stats updates to have it 'learn'

-- what happens if we insert more than 10% that isn't ascending
declare @i int = 1;

while(@i <= 10)
begin
    insert into AscendingKeyTableTest(AscendingKey) VALUES (@i);
    set @i += 1
end
go

-- still says ascending... but...
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go
-- what if we update again?
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;
go
-- stationary
dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go
-- get it back to ascending
declare @i int;

SELECT @i = max(ascendingkey) from dbo.AscendingKeyTableTest;

insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+1);
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+2);
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;

insert into AscendingKeyTableTest(AscendingKey) VALUES (@i+3);
update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;
go

dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go

-- what about the deletes?
delete from AscendingKeyTableTest where AscendingKey % 3 = 0
go

update statistics ascendingkeytabletest(ascendingkeystats) with fullscan;
go

dbcc traceon(2388);
dbcc show_statistics('dbo.ascendingkeytabletest', ascendingkeystats);
dbcc traceoff(2388);
go
-- back to unknown

-- cleanup
use master
go

drop database AscendingKey
go


Solution on a complex sub select query SQL Server

For starters, you can remove multiple references of the same table:

Select A.w,
    oa.f as valueZA,
    oa.k as valueZB,
From DatabaseA.TblA A
    LEFT JOIN DatabaseA.TblB B A.x = B.x
    outer apply (
        Select Z.f, Z.k From DatabaseZ.TblZ Z where Z.w = A.w
    ) oa
where A.isActiveBit = '1';

You can replace outer with cross, depending on the logic you need.

Also, looking for index suggestions from SQL Server definitely wouldn't hurt. Either check execution plan suggestions, or even better, google for diagnostic scripts referring sys.dm_db_index_usage_stats() DMV.


How does a column-oriented DBMS filter on multiple columns?

There are a few factors which reduce the horror of a full scan of the shop column.

The values of each column can be stored in the same order: the first value of timestamp corresponds to the first value in shop and to the first in product; the second to the second to the second, and so on. So a fast lookup that gives the offsets for the start and end of the timestamp range also gives the offset of the corresponding values in shop. The search can jump straight to that offset in the list of shop values, if shop codes are fixed length or can be coerced into being fixed length. More on this soon.

For disk-oriented rather than fully in-memory systems, meta data can be held to show which disk files correspond to what offsets for each column. So IO is limited to only the files necessary.

IO is still block-oriented and all the shop codes are contiguous on disk. One (relatively fast) sequential read will return a lot of shop codes into memory.

Those codes can be stored contiguously in memory, which is very pre-fetch- and processor cache-friendly.

Compression. Even if the shop codes are quite long or of variable length, likely there will be relatively few unique values (Walmart has fewer than 12,000 stores). Dictionary compression can be applied, which maps each long string to a much shorter fixed length integer. The mapping table is held once and it is these integers which become the values held in column array. Run-length encoding can further reduce the size of the "shop" array, producing a virtuous feedback with file size, memory and CPU cache utilisation.

The RowID you speak about is a useful visualisation but need not be physically implemented. Since tuples' columns are stored in the same order, the offsets perform the function the RowID would. For multi-predicate queries it is unlikely an actual array of integers representing offsets would be built for each predicate and the intersection of these computed. Rather, each predicate would produce a bitmap and these bitmaps would be AND-ed (or OR-ed) to produce the final predicate. Each bit represents the offset of a tuple that satisfies the predicate.
One of the earliest column stores (C-Store) allowed for redundant storage of column sets, each of which could be sorted differently to facilitate fast predicate lookup. I know of no recent system which implements this, but it's an interesting idea.


One database or multiple referencing one?

I would lean toward multiple databases. Primarily because you can set them up for different recovery, SLA, maintenance, deployment, changes, etc. And also this allows you to move certain databases to faster or larger I/O on different drives without disrupting everyone.

For the master tables that are used by all the applications, no I don't think you should have multiple copies of these. Your applications can query different databases and/or have different connection strings for different tasks (best separated by a middle tier of some sort in most cases). If the app-specific code within a single database needs to have some kind of reference to them, you can use synonyms or three-part names. This is easy to extend if you later need to split out not just across databases but across multiple instances / servers (just add a linked server and make it a four-part name).

Only if the link between these servers is notoriously slow or otherwise unreliable would I consider making replicas of the master tables. I talked about how to do that here and here, even though I don't explicitly cover moving data across servers.


For the timebeing we have implemented the solution found here: https://stackoverflow.com/questions/11414976/unique-constraint-on-two-columns-regardless-of-order

More specifically we have:

Added a Constraint so that TypeB must be greater than TypeA
Added a Unique Key for TypeA, RefA and TypeB
Added a Unique Key for TypeB, RefB and TypeA

This is a workaround as it forces changes to the business logic to ensure the products are sorted correctly before choosing which columns to add the information to.

Leaving this answer unaccepted in the hopes of a flashier answer.


Can't connect to a Oracle db instance via SQLDeveloper

In the meantime I figured it out myself.

The connection couldn't be established from SQL Developer because I was logged into my company's VPN and so "localhost" was resolved somehow as an IPV6 address.

When I closed the VPN connection and tried to connect with the same connection information it worked, localhost was resolved as normal IPV4 address then.


SSRS report does not work with multiple parameters

You could achieve this by changing your dataset query to an expression and use the Join operator on the multi-valued parameter, for example, your expression would look like

= "Select * " &
  "from table t " &
  "where a11.PDPOID LIKE '%" & Join(Parameters!TestParm.Label, "%' OR a11.PDPOID LIKE
  '%") & "%'"

Replace the Select * from table t with your query.


How to determine database read/write ratio on MongoDB MMS?

There are two way.

opcounters

Build as sum of the opcounters which are write operations, build a sum of the opcounters which are read operations set them into relation. To do this reliably for a sharded cluster, you have to create a dashboard which groups the opcounters for the shards.

network in/out ratio

This is self explanatory. But please keep in mind that you have to divide the out ratio by the number of replica set members to get a rough idea. For a sharded cluster, you need this grouped by shards in a dashboard, too.

What really comes to my mind: you should actually know if you have a rather read or rather write heavy application. Your use case, your business model (if applicable), your implementation, your integration tests: all these should give you an idea.


postgresql function to automatically merge and add two jsonb values from different tables when one is updated and store it in a third

Check out this PostgreSQL function
CREATE OR REPLACE FUNCTION public.test1(in_r1 json, in_r2 json)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
DECLARE
 sum_R jsonb;
 R record;
 x text[];

BEGIN
 sum_R = '{}'::jsonb;
 /* FOR R IN SELECT * FROM json_each_text(in_R1) */
 FOR R IN SELECT * FROM json_each(in_R1)
 LOOP
   raise notice 'key %', R.key;
   raise notice 'value %', R.value;
   x := array_append(array[]::text[],R.key);
   raise notice 'x %', x;
   raise notice 'jsonb value  %', to_jsonb(R.value);
   sum_R := jsonb_insert(sum_R, x, to_jsonb(R.value));
   raise notice 'sum_R %', sum_R;
 END LOOP;
 FOR R IN SELECT * FROM json_each_text(in_R2)
 LOOP
   raise notice 'key %', R.key;
   x := array_append(array[]::text[],R.key);
   raise notice 'x %', x;
   raise notice 'jsonb value  %', to_jsonb(R.value);
   raise notice 'jsonb old value  %', sum_R->>R.key;
   if ((sum_R->>R.key = '') is not false) then
     sum_R := jsonb_insert(sum_R, x, to_jsonb(R.value));
   else
     raise notice 'sum %', (sum_R->>R.key)::int + R.value::int;
     sum_R := jsonb_set(sum_R, x,
                        to_jsonb((sum_R->>R.key)::int + R.value::int));
   end if;
   raise notice 'sum_R %', sum_R;
 END LOOP;
 RETURN to_json(sum_R);
END;
$function$

Call it like
select test1('{"A": 5, "B": 15, "C": 10 }','{"A": 1, "C": 5, "D": 1}');

to get
                test1
--------------------------------------
 {"A": 6, "B": 15, "C": 15, "D": "1"}
(1 row)

apart from a lot of comments.
Using HSTORE for intermediate results:
CREATE OR REPLACE FUNCTION public.test2(in_r1 json, in_r2 json)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
DECLARE
 sum_R hstore;
 R record;
 x integer;

BEGIN
 sum_R = ''::hstore;
 /* FOR R IN SELECT * FROM json_each_text(in_R1) */
 FOR R IN SELECT * FROM json_each(in_R1)
 LOOP
   sum_R := sum_R || hstore(R.key::text, R.value::text);
   raise notice 'sum_R %', sum_R;
 END LOOP;
 FOR R IN SELECT * FROM json_each_text(in_R2)
 LOOP
   x := sum_R->R.key;
   if (((x::text) = '') is not false) then
     sum_R := sum_R || hstore( R.key, R.value::text);
   else
     sum_R := sum_R || hstore( R.key, (x + (R.value::int))::text);
   end if;
   raise notice 'sum_R %', sum_R;
 END LOOP;
 RETURN hstore_to_json(sum_R);
END;
$function$


Difference between Archive destination & db_recovery_file_dest

Archive log destination define the place where to store archived redo log files(once online redo log file is filled it can be stored as archived log files). You can have multiple archive log destinations.

In other hand, DB_RECOVERY_FILE_DEST specifies the default location for the fast recovery area. The fast recovery area contains multiplexed copies of current control files and online redo logs, as well as archived redo logs, flashback logs, and RMAN backups.

You can use DB_RECOVERY_FILE_DEST(which is default if you enable archive log mode in your database without specifying archive log destination explicitly), which is fast recovery area, to store archived redo log files or you can store them in separate locations.

Documentation:

DB_RECOVERY_FILE_DEST

Managing Archived Redo Log Files


Use execute as self

create or alter trigger check_view
on database
with execute as self
for create_view
as . . .

or execute as 'dbo'

create or alter trigger check_view
on database
with execute as 'dbo'
for create_view
as . . .


Each tab in SSMS is either linked to a SQL Server instance or working offline (disconnected). Whenever you open a .sql file in SSMS, it will automatically try to connect to the selected Instance and Database on your Object Explorer. When executing a query while offline it will pop up the connection window.

You can see the current connected instance if you hover the mouse over a connected tab or in the status bar at the bottom (default), next to the username and SPID, if you didn't hide it.

Scripts can't store the initial instance's connection. So make sure to connect previously to the proper instance. You might use sqlcmd inside scripts to connect to different instances but I don't think that's your concern.

There are add-ins like SSMS Tools in which you can map instances to color bars that are displayed on the side, up or down so for example you can map your Dev instance to color red and your Prod environment to green.


MySQL: Optimize UNION with "ORDER BY" in inner queries

Just out of curiosity, can you try this version? It may trick the optimizer to use the same indices that the subqueries would use separately:

SELECT *
FROM
(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
 'hp' AS source FROM is_log AS l WHERE l.account_id = 730
 ORDER BY l.zeitpunkt DESC LIMIT 10)
    AS a

UNION ALL

SELECT *
FROM
(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
 'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730
 ORDER BY l.zeitpunkt DESC LIMIT 10)
    AS b

ORDER BY zeit DESC LIMIT 10;

I still think that the best index you could have is the compound (account_id, zeitpunkt). It would yield the 10 rows fast, and no tricks would ne needed.


SQL Server 2016 memory optimised table: Precreated files constantly growing

Question title says SQL 2014, but question text says SQL 2016. This issue is not necessarily a bug in either version.

Assuming db is in full recovery, in order for file merge to occur, transaction log backups and checkpoints must occur. If running simple, checkpoint alone will take care of file merge and clearing the log. Please see this blog post:

https://blogs.msdn.microsoft.com/psssql/2017/03/06/why-am-i-getting-so-many-checkpoint-files-when-i-have-in-memory-oltp-enabled/

But @Slowry is also correct, in that you must be fully patched.

There is no knob you can turn to affect file precreation, although the required storage footprint is less in SQL 2016. There is no way to control the size of checkpoint file pairs, containers, or the memory optimized file group (in any version of SQL Server that currently supports In-Memory OLTP).


Merge two tables on partly matching column

Use some pattern matching / string manipulation in the JOIN condition. I use left() in my example:

SELECT *
FROM   tbl_old o
JOIN   tbl_new n ON left(n.new_name, -1)
                  = left(o.old_name, -1)

This assumes that all names have exactly one pending character (digit or no, doesn't matter).

left(string, -1) (with a negative length parameter) effectively trims the last letter from string. Should be fastest. Requires Postgres 9.1.
In older version substitute with substring() or one of many other ways ...


Which factors impact the time for SQL Server Recovery to complete

Since you are not using SQL server 2019, ADR won't be applicable. There are few general guidelines, which should be considered in almost all the cases and they are as below:

Keeping Transactions as tiny as possible.
Control long running transactions liaising with application team or vendor as appropriate.
Take transaction log backup as frequently as possible to reduce Analysis phase.

There is an option of changing target recovery time however that has some down-side also. You should consider them before enabling them.
From MS-site, if you avoid below scenarios, you don't need ADR:
Who should consider accelerated database recovery
The following types of customers should consider enabling ADR:

Customers that have workloads with long running transactions.
Customers that have seen cases where active transactions are causing the transaction log to grow significantly.
Customers that have experienced long periods of database unavailability due to SQL Server long running recovery (such as unexpected SQL Server restart or manual transaction rollback).

From the comment and new addition to your question about using blob type in your database, yes having these data types can significantly delay any checkpoint by database. In stead, you should use CAS storage type as explained well by Mr. Brent Ozar:
https://www.brentozar.com/archive/2015/03/no-more-blobs/
Hope this helps you in taking right decision.


Is it safe to run optimze on mutliple tables in parallel in mysql (percona)

As KJavalik pointed out in the Comments, it it safe, but Disk IO will increase and may choke other operations. So in case the Server is not serving any traffic/queries, you may try, else be careful.

Also keep an eye on the free disk space as optimize command will copy the remaining data of the table being optimized to a new table, and if the slave is in replication, new data will also take up some space.

So in case you are optimizing 2 tables of 50Gb each, for example, and prior experience suggests that post optimization each table will be around 35 Gb, then its good to have at least 90Gb of free space (35 Gb + 35 Gb + 20 Gb of extra free space for other tables to grow in case the database is in replication)


While this won't necessarily get to the root of the problem, you may want to see if your job can be set up to retry on failure.

If the problem is a temporary issue, then trying to run the job (or step) 15 seconds or 3 minutes later might work fine.

Note that you need to consider how your job works. If it starts by clearing a work table, then loads the table from a file, then processes the file data, and it's actually failing after the data's loaded, you want to be sure that you still have your file around to reload the data, or you want to break the job into steps, and just rerun the processing step. Also, you need to be sure that shifting the runtime doesn't interfere with other processes (sometimes, if a job takes less than a minute to run normally, you'll have something that has to follow it scheduled to run 15 minutes later; if you schedule up to 10 retries, 3 minutes apart, and the ninth retry works, it's running after that other job has started...).

Sometimes, seeing the length of time the failures take, and having multiple attempts a few minutes apart, will provide more info about the situation, and help you diagnose the solution.

I know that the SQL Server job scheduler allows you to retry job steps on failure; you specify the number of retries, and the time to wait before each retry. Other scheduling mechanisms may or may not have this ability.


Partition pruning with multiple date columns

Oracle is unable to do partition pruning when a function is applied to the partitioned column. From the docs:

  There are several cases when the optimizer cannot perform pruning. One
  common reasons is when an operator is used on top of a partitioning
  column. This could be an explicit operator (for example, a function)
  or even an implicit operator introduced by Oracle as part of the
  necessary data type conversion for executing the statement.

Your view has to apply some form of function to start and end dates to figure out if they're the same year or not, so I believe you're out of luck with this approach.

Our solution to a similar problem was to create materialized views over the base table, specifying different partition keys on the materialized views.

We've tailored ours to match common base queries so that we get query rewrite benefits as well. You may need to get users to use the MVs directly to ensure you get the partition pruning working as you need, rather than relying on query rewrite.

(Updated to remove incorrect example and add info regarding applying functions to partition columns)


Mysql: Query which orders by default and rank (pin/hold up entries?)

You can add to the ORDER BY a IF ot CASE, depending on the complexity or the new order.
In you case you ned to add the max(id) into the mix

CREATE TABLE table1 (
  `id` INTEGER,
  `title` VARCHAR(18),
  `rank_index` VARCHAR(4)
);

INSERT INTO table1
  (`id`, `title`, `rank_index`)
VALUES
  ('1', 'An awesome product', NULL),
  ('2', 'Another product', '3'),
  ('3', 'Baby car', NULL),
  ('4', 'Green carpet', '1'),
  ('5', 'Toy', NULL);

    SELECT t1.id, t1.`title`,t1.`rank_index`
    FROM table1  t1 CROSS JOIN ( SELECT MAX(id)  maxid FROM table1) t2
    ORDER BY IF(rank_index IS NULL, maxid - id + 1 , rank_index - 0.5) ASC

id | title              | rank_index
-: | :----------------- | :---------
 4 | Green carpet       | 1
 5 | Toy                | null
 2 | Another product    | 3
 3 | Baby car           | null
 1 | An awesome product | null

db<>fiddle here


ETA: translate Oracle SQL queries to MySQL

Why are you migrating to MySQL?  Did you consider PostgreSQL?  I only ask because PostgreSQL is pretty much feature-compatible with Oracle, and would make a migration an order of magnitude simpler.

That said, translating 80 table definitions and 500 queries will probably take a month or so.  Simple CRUD statements will be limited to the expert's typing speed, but if you're doing any aggregation or other BI-type work it can take a day just to get one statement working.  You might be better off simply documenting every query then re-writing them from scratch using MySQL idioms rather than trying to translate them.


Does my table need a primary key and clustered index change?

To answer question 2 first

2

A primary Key is NOT always the clustered index, it can be the clustered index and in the majority of cases is the way things are done, but it isn't always the best for your data. The culstered index is the order in which your data is physically stored on the disk whereas the primary key (which can be composite) designates the field to be a unique field and is beneficial for not inserting duplicate values and for foreign key lookups if you wish to do joins
(In summary for 2, yes you can add a primary key without a clustered index)

NOTE: sometimes if you want an identifiable row you can add a new field to the table to simply act as the primary key (not always advisable, but can sometimes be the a solution to improve performance)

1

Adding a primary key can change performance, however the only true way to know if it's going to improve performance is to test it, if you have a pre-live environment consider adding it to there and running your queries across it.

If you have queries that run as joins to this table on ID,AID,BID all together (Sorry I'm not 100% sure how these are all coming together) the Potentially create a composite primary key across all three which  means when anything wishes to get data with this table comparing all three of those it can find that row with ease. (Hope this makes sense)

3

Adding a Clustered Index completely depends on your data, once again a pre-live environment would be an ideal situation for testing.

A few things to consider when creating a clustered index, what data are you retrieving and what are you inserting

(this is a general example)If you're inserting and retrieving data that is the most recent data then a clustered index on the date field sounds the best idea, however if there is a LOT of data going in and out you will have very high contention on the most recent pages in your table, an alternative would be to have the clustered index around a category that those dates are on, eg client, this would mean that the data is grouped by a client which is more likely to have data gathered by, and spreads the read write load across the disk / disks

If the data retrieve is very random then a clustered index is quite pointless, if the data you get back has no real order to it then a Heap is completely acceptable.

Ultimately there is no be all answer to should I add a clustered index or a primary key because every situation is slightly different and will react in different ways.

If you have a pre-live environment (even a cut down version) can help make your decisions. Personally we have tables with primary clustered composite keys, and some tables that are simply heaps.

Hope this helps (and makes sense, sometimes I find I ramble)


Can I install SQL Server 2012 on a machine with 2008 R2 already?

You can simply install a new, named instance, and the two instances can live side-by-side with no risk - just be sure that your connections strings etc. are clear and correct (many problems arise from connecting to LOCALHOST when you meant to connect to LOCALHOST\OtherInstance).

Depending on resources you may want to be sure to cap both servers using the max server memory configuration option, or perhaps even only running one instance at a time. I assume this is on your local development environment.

I'd suggest abandoning the 2008 R2 version of the client tools and using the 2012 version consistently. It has much better functionality and can manage both versions seamlessly.


Problem tracing Error: 8623 with extended events

Thanks to Ronen Ariely over at MSDN and Jonathan Kehayias article on SQL Performance I was able to solve the problem. The problem was that event size was to big so that it was dropped. You can check this by querying sys.dm_xe_sessions as e.g.
   SELECT
   s.name,
   s.total_regular_buffers,
   s.regular_buffer_size,
   s.total_large_buffers,
   s.large_buffer_size,
   s.dropped_event_count,
   s.dropped_buffer_count,
   s.largest_event_dropped_size
FROM sys.dm_xe_sessions AS s;

If any event has been dropped you will se the number of dropped events in column dropped_event_count and the max size of any dropped event in largest_event_dropped_size. If this value is larger that the regular_buffer_size then you need to increase the regular buffer size.
This was the case for me and when I did change the Max Memory size from the default to 64MB the regular buffer size was increased so much that I was able to catch the error!
As a side note the problematic query was a entity framework generated SELECT with an huge (> 38 000 values) IN clause! No wonder that the database engine got enough.


Query records that chain together based on date gaps

The following solution (SQL Fiddle) uses the SQL Server 2012LAST_VALUE function:

SELECT
    MaxDiff.ID,
    MaxDiff.Arrival,
    MaxDiff.Departure
FROM
(
    -- Maximum difference encountered so far
    SELECT *,
        MaxDiff = LAST_VALUE(Diff.Diff)  OVER (
            ORDER BY Diff.Departure DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM
    (
        -- Difference between departure and prior arrival
        SELECT *,
            Diff =
                ISNULL(DATEDIFF(DAY, r.Departure,
                        LAST_VALUE(r.Arrival) OVER (
                            ORDER BY r.Departure DESC
                            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)), 0)
        FROM dbo.Records AS r
    ) AS Diff
) AS MaxDiff
WHERE
    MaxDiff.MaxDiff <= 30
ORDER BY
    MaxDiff.Departure DESC;

Results:

╔════╦════════════╦════════════╗
║ ID ║  Arrival   ║ Departure  ║
╠════╬════════════╬════════════╣
║  4 ║ 2009-07-19 ║ 2009-07-24 ║
║  3 ║ 2009-06-11 ║ 2009-07-01 ║
║  2 ║ 2009-04-27 ║ 2009-05-21 ║
║  1 ║ 2009-04-19 ║ 2009-04-28 ║
╚════╩════════════╩════════════╝

Execution plan:


Is it possible to create an alias/shortcut/makro/function for a collection of keywords?

You can tell the tool, in this case SQL Developer, to do it for you.

I talk about how to set this up here
 https://www.thatjeffsmith.com/archive/2014/02/auto-replace-in-oracle-sql-developer/

But basically, you define a code template in the preferences with the code you want to be recognized as a template, and then the code you want inserted.

If there is only one match for your template, when you invoke it with ctrl+spacebar, it automatically inserts the code.

If there are multiple matches, it prompts you for what you want.

I built two, one for

auto_increment - long form
autoincrement - short form

You could of course submit an enhancement request for the database so this keyword would be honored, but that's a years long process, assuming it even gets accepted and implemented.


Updates since the previous commit point Vs updates prior to the commit point

The text must be talking of two different commits during the same database session.

Say, the last commit that happened in this session was at time A. Now if you make a second commit at B, all updates made since A (the previous commit) are committed. Prior to the commit at B, all such updates are only tentative.


MySQL import and export data without update on insert

A unique timestamp is a mistake waiting to happen.

If there is also an AUTO_INCREMENT id, you can do this:

PRIMARY KEY(timestamp, id),
INDEX(id)

Then dups are not possible.


When to add another lookup table for the same field

Although RecipeCategory and IngredientCategory have very similar names and attributes, they are in fact two different entity types, because each of them (a) carries a specific business domain meaning, (b) has distinct kinds of relationships and (c) entails a particular set of rules.
In this regard, if the intention is to implement a relational database (RDB), it is quite helpful to perform an analysis of the business domain of interest (in order to construct a conceptual model) in terms of entity types (i.e., types or prototypes of entity occurrences), their attributes and interrelationships before thinking in terms of tables, columns and constraints (points that correspond to the logical model). Proceeding in this fashion, it is much easier to capture the meaning of the business domain with accuracy and then reflect it in an actual RDB structure.
Business domain rules
Recipe and RecipeCategory
Let us start working with two entity types: Recipe and RecipeCategory. In the scenario in question, it appears reasonable to affirm that:

A Recipe is classified by zero-one-or-many RecipeCategories
A RecipeCategory classifies zero-one-or-many Recipes

Such situation indicates, yes, that Recipe and RecipeCategory are involved in a many-to-many (M:N) relationship, which implies the existence of an associative entity type, that I am going to call RecipeCategorization.
Ingredient and IngredientCategory
Then, let us deal with Ingredient and IngredientCategory. In this case, we can affirm that:

An Ingredient is grouped by zero-one-or-many IngredientCategories
An IngredientCategory groups zero-one-or-many Ingredients

This means that Recipe and RecipeCategory are connected in another M:N relationship, which entails the existence of another associative entity type, that I denominated IngredientCategorization.
RecipeCategory and IngredientCategory
As discussed above, one can observe that the concrete occurrences of RecipeCategory are meant to be (directly) associated with the specific instances of Recipe, and not with the occurrences of Ingredient. In the same manner, the concrete instances of IngredientCategory are meant to be (directly) connected with the specific occurrences of Ingredient, and not with the instances of Recipe. Therefore, RecipeCategory and IngredientCategory are distinct entity types, and demand their own respective individual considerations.
Recipe and Ingredient
Finally, we can assume that:

A Recipe includes one-to-many Ingredients
An Ingredient is included in zero-one-or-many Recipes

Thus, there is another M:N relationship, this time between Recipe and Ingredient, which reveals the existence of other associative entity type, that I am going to entitle RecipeListing.
Illustrative IDEF1X model
Then, from the aforementioned analysis and consequent formulations, I created the IDEF1X† model shown in Figure 1:

As demonstrated, each entity type is depicted in its corresponding individual box, and is displayed directly related to (i) its own attributes, contained in the respective box, and to (ii) the entity types that apply, by way of the relationship lines.
Of course, there are other indirect relationships that should be derived via the direct connections exposed here.
Logical and physical elements
Once we have analyzed and defined the pertinent types of the things of significance, it is time to determine how to manage them by means of mathematical relations (declared and visualized as tables, if created on a certain SQL database management system), which are composed of domains (portrayed as columns) and tuples (pictured as rows).
As relations are abstract resources, Dr. E. F. Codd —the originator of the relational paradigm— envisioned the utility of representing them in tabular form, so that, e.g., the users and implementers of a RDB can approach them in a more familiar way. In this respect, even though a relational table has a concrete shape, it is still a logical element of a given database, and its components, e.g., columns, rows and constraints are logical as well.
In this regard, it is very important and of vast pragmatical value to distinguish logical from physical elements. For instance, in file systems, a physical record can be made up of zero, one or more fields. In the case of a RDB, the logical elements can be served by one or more physical units (at a lower level of abstraction, then), e.g., indexes, records, pages, extents, etc.
Thus, in accordance with the points detailed above, a table —being a logical level component— does not have fields (which may well be part of the underlying concrete scaffoldings supporting a table declaration, but work at the physical level).
Expository logical SQL-DDL structure
That being said, and based on the IDEF1X model previously presented, both RecipeCategory and IngredientCategory (and the rest of the identified entity types too) require an individual base table that stands for each of them, as exemplified in the following DDL structure:
-- You have to determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient physical implementation settings; e.g.,
-- a good INDEXing strategy based on query tendencies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE RecipeCategory ( -- Plays a ‘look-up’ role.
    RecipeCategoryCode CHAR(2)  NOT NULL,  -- This column can retain the values: ‘O’ for ‘Omnivorous’; ‘VT’ for ‘Vegetarian’; ‘VG’ for ‘Vegan’; etc.
    Name               CHAR(30) NOT NULL,
    Description        CHAR(60) NOT NULL,
    Etcetera           CHAR(30) NOT NULL,
    CreatedDateTime    DATETIME NOT NULL,
    CONSTRAINT RecipeCategory_PK  PRIMARY KEY (RecipeCategoryCode),
    CONSTRAINT RecipeCategory_AK1 UNIQUE      (Name),       -- ALTERNATE KEY.
    CONSTRAINT RecipeCategory_AK2 UNIQUE      (Description) -- ALTERNATE KEY.
);

CREATE TABLE Recipe (
    RecipeNumber    INT      NOT NULL,
    Name            CHAR(30) NOT NULL,
    Description     CHAR(60) NOT NULL,
    Etcetera        CHAR(30) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT Recipe_PK  PRIMARY KEY (RecipeNumber),
    CONSTRAINT Recipe_AK1 UNIQUE      (Name),       -- ALTERNATE KEY.
    CONSTRAINT Recipe_AK2 UNIQUE      (Description) -- ALTERNATE KEY.
);

CREATE TABLE RecipeCategorization ( -- Represents an associative entity type.
    RecipeNumber       INT      NOT NULL,
    RecipeCategoryCode CHAR(2)  NOT NULL, -- Contains meaningful and readable values.
    Etcetera           CHAR(30) NOT NULL,
    ClassifiedDateTime DATETIME NOT NULL,
    CONSTRAINT RecipeCategorization_PK                   PRIMARY KEY (RecipeNumber, RecipeCategoryCode), -- Composite PK.
    CONSTRAINT RecipeCategorization_to_Recipe_FK         FOREIGN KEY (RecipeNumber)
        REFERENCES Recipe (RecipeNumber),
    CONSTRAINT RecipeCategorization_to_RecipeCategory_FK FOREIGN KEY (RecipeCategoryCode)
        REFERENCES RecipeCategory (RecipeCategoryCode)
);

CREATE TABLE IngredientCategory ( -- Plays a ‘look-up’ role.
    IngredientCategoryNumber INT      NOT NULL,
    Name                     CHAR(30) NOT NULL,
    Description              CHAR(60) NOT NULL,
    Etcetera                 CHAR(30) NOT NULL,
    CreatedDateTime          DATETIME NOT NULL,
    CONSTRAINT IngredientCategory_PK  PRIMARY KEY (IngredientCategoryNumber),
    CONSTRAINT IngredientCategory_AK1 UNIQUE      (Name),       -- ALTERNATE KEY.
    CONSTRAINT IngredientCategory_AK2 UNIQUE      (Description) -- ALTERNATE KEY.
);

CREATE TABLE Ingredient (
    IngredientNumber INT      NOT NULL,
    Name             CHAR(30) NOT NULL,
    Description      CHAR(60) NOT NULL,
    Etcetera         CHAR(30) NOT NULL,
    CreatedDateTime  DATETIME NOT NULL,
    CONSTRAINT Ingredient_PK  PRIMARY KEY (IngredientNumber),
    CONSTRAINT Ingredient_AK1 UNIQUE      (Name),       -- ALTERNATE KEY.
    CONSTRAINT Ingredient_AK2 UNIQUE      (Description) -- ALTERNATE KEY.
);

CREATE TABLE IngredientCategorization ( -- Stands for an ssociative entity type.
    IngredientNumber         INT      NOT NULL,
    IngredientCategoryNumber INT      NOT NULL,
    Etcetera                 CHAR(30) NOT NULL,
    GroupedDateTime          DATETIME NOT NULL,
    CONSTRAINT IngredientCategorization_PK                       PRIMARY KEY (IngredientNumber, IngredientCategoryNumber), -- Composite PK.
    CONSTRAINT IngredientCategorization_to_Ingredient_FK         FOREIGN KEY (IngredientNumber)
        REFERENCES Ingredient (IngredientNumber),
    CONSTRAINT IngredientCategorization_to_IngredientCategory_FK FOREIGN KEY (IngredientCategoryNumber)
        REFERENCES IngredientCategory (IngredientCategoryNumber)
);

CREATE TABLE IngredientListing ( -- Denotes an associative entity type
    RecipeNumber     INT      NOT NULL,
    IngredientNumber INT      NOT NULL,
    Etcetera         CHAR(30) NOT NULL,
    IncludedDateTime DATETIME NOT NULL,
    CONSTRAINT IngredientListing_PK               PRIMARY KEY (RecipeNumber, IngredientNumber), -- Composite PK.
    CONSTRAINT IngredientListing_to_Recipe_FK     FOREIGN KEY (RecipeNumber)
        REFERENCES Recipe (RecipeNumber),
    CONSTRAINT IngredientListing_to_Ingredient_FK FOREIGN KEY (IngredientNumber)
        REFERENCES Ingredient (IngredientNumber)
);

--
--

With such structure, you prevent ambiguities and all their logical and pragmatic repercussions. You avoid mixing up

the representation of multiple entity types in a single “shared” table, and
the meaning and intention of each of their attributes (in “shared” columns),

which permits restricting much more easily their corresponding domains of values and the subsequent references through FOREIGN KEY (FK) constraints. The queries and result sets become much more clear because each aspect is approached separately.
This structure, hence, aids by itself in reflecting the business context under consideration with precision, remaining consistent with the characteristics delimited in the conceptual analysis, and guaranteeing that the data (every assertion in the form of a row) complies with the business rules.
Practical considerations regarding the RecipeCategory table
Seing that the RecipeCategory table is supposed to (1) fulfill a look-up role and (2) would hold only a few rows, I consider that it would be very advantageous to declare it with a PRIMARY KEY (PK) constraint on a column that keeps values that are meaningful and, at the same time, physically light and  narrow, i.e., of type CHAR(2) or maybe CHAR(3). So it might comprise, e.g., the rows that follow:

+-——————————————————-+-——————————-+-—————————————————————————-+-————-+-————-+
| RecipeCategoryCode | Name       | Description               | Etc… | Cre… |
+-——————————————————-+-——————————-+-—————————————————————————-+-————-+-————-+
| O                  | Omnivorous | Category of recipes that… | …    | …    |
+--------------------+------------+---------------------------+------+------+
| VT                 | Vegetarian | Category of recipes that… | …    | …    |
+--------------------+------------+---------------------------+------+------+
| VG                 | Vegan      | Category of recipes that… | …    | …    |
+--------------------+------------+---------------------------+------+------+
| F                  | Foo        | Category of recipes that… | …    | …    |
+--------------------+------------+---------------------------+------+------+
| B                  | Bar        | Category of recipes that… | …    | …    |
+--------------------+------------+---------------------------+------+------+

In this manner, when such PK column “migrates” to the RecipeCategorization table as a column with a FK constraint, it will have stable values that maintain their meaning and intention, making the result sets much more readable than, say, an INT value, which definitely can assist in the interpretation of the information obtained through, e.g., derived tables (those fetched back by virtue of a SELECT statement or a VIEW definition).
All this remains in agreement with the spirit of the relational model†† published in 1970 by Dr. Codd, where he included the following relevant note:

Naturally, as with any data put into and retrieved from a computer system, the user will normally make far more effective use of the data if he is aware of its meaning.

Relational databases and application programs
The extract of your question cited below has to do, among other things, with the “link” between (a) the database under discussion and (b) the application programs (apps) that will work along with it:

I tested the ideas in the related Q & A Relational tables with same content, custom solution. It's easier to handle data on the frontend side and housekeeping, but data integrity could be an issue if the database grows.

It is opportune to point out that the data, in its essence and of its nature, is a highly valuable organizational asset; as a result, it must be administered as such. This fundamental resource tends to outlive apps, app development platforms and programming paradigms.
With that, a RDB ought to be an independent (self-protective, self-describing, etc.) software component that is capable of being shared by multiple apps, and it must not be “coupled” —using object oriented programming parlance— with the code of any of these apps.
Consequently, you should (a) manage the data by dint of the instruments provided by the relational theory, the modeling platforms and the SQL sytem of choice, and (b) shape and implement the relevant processes and the graphical user interface with the pertinent app development tools. In this way, all the software components will work harmonically; they will be independent but, at the same time, well interconnected.

Endnote and reference
† Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the early relational model works authored by Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
†† Codd, E. F. (June 1970). A Relational Model of Data for Large Shared Data Banks, Communications of the ACM, Volume 13 Issue 6 (pp. 377-387). New York, NY, USA.


SQL Express 2008 R2 on Amazon EC2 instance: tons of free memory, poor performance

Decided to uninstall SQL Express 2008 and install SQL Express 2005.  Restored the database (same one I had originally backed up for the migration), load time for the one page was down to ~6 seconds from ~13 seconds.

Reindexed the database for fun and profit and checked consistency.  Seemed fine (return code 0).


How to generate a 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... series in standard SQL or T-SQL?

Postgres
You can make it work with a single generate_series() and basic math (see mathematical functions).
Wrapped into a simple SQL function:
CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
  RETURNS SETOF int AS
$func$
SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END
FROM  (
   SELECT n2m, n2m % (n*2) AS n2
   FROM   generate_series(0, n*2*m - 1) n2m
   ) sub
ORDER  BY n2m
$func$  LANGUAGE sql IMMUTABLE;

Call:
SELECT * FROM generate_up_down_series(3, 4);

Generates the desired result. n and m can be any integer where n*2*m does not overflow int4.
How?
In the subquery:

Generate the desired total number of rows (n*2*m), with a simple ascending number. I name it n2m. 0 to N-1 (not 1 to N) to simplify the following modulo operation.

Take it % n*2 (% is the modulo operator) to get a series of n ascending numbers, m times. I name it n2.

In the outer query:

Add 1 to lower half (n2 < n).

For the upper half (n2 >= n) mirror of the lower half with n*2 - n2.

I added ORDER BY to guarantee the requested order. With current versions or Postgres it also works without ORDER BY for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not warranted by the SQL standard.

Unfortunately, generate_series() is Postgres specific and not standard SQL, as has been commented. But we can reuse the same logic:
Standard SQL
You can generate the serial numbers with a recursive CTE instead of generate_series(), or, more efficiently for repeated use, create a table with serial integer numbers once. Anyone can read, noone can write to it!
CREATE TABLE int_seq (i integer);

WITH RECURSIVE cte(i) AS (
   SELECT 0
   UNION ALL
   SELECT i+1 FROM cte
   WHERE  i < 20000  -- or as many you might need!
   )
INSERT INTO int_seq
SELECT i FROM cte;

Then, the above SELECT becomes even simpler:
SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END AS x
FROM  (
   SELECT i, i % (n*2) AS n2
   FROM   int_seq
   WHERE  i < n*2*m  -- remember: 0 to N-1
   ) sub
ORDER  BY i;


Identifying number of rows (COUNT) that fall between two timestamps for each minute in the hour

I'd like a query that could produce this sort of result

Look at

WITH RECURSIVE
cte1 AS ( SELECT CAST(DATE_FORMAT(MIN(StartTime), '%Y-%m-%d %H:%i:00') AS DATETIME) mintime,
                 CAST(DATE_FORMAT(MAX(EndTime), '%Y-%m-%d %H:%i:00') AS DATETIME) maxtime
                 FROM srctable ),
cte2 AS ( SELECT mintime timechunk,
                 mintime + INTERVAL 1 MINUTE nextchunk
          FROM cte1
        UNION ALL
          SELECT cte2.nextchunk,
                 cte2.nextchunk + INTERVAL 1 MINUTE
          FROM cte1, cte2
          WHERE cte2.timechunk < cte1.maxtime )
SELECT cte2.timechunk time_chunk,
       COUNT(*) calls_in_progress
FROM cte2
LEFT JOIN srctable ON cte2.timechunk < srctable.endtime
                  AND cte2.nextchunk >= srctable.starttime
GROUP BY cte2.timechunk
/* WITH ROLLUP
   ORDER BY cte2.timechunk IS NULL, cte2.timechunk */

fiddle


TRUNCATE TABLE statement sometimes hangs

The reason why you experience performance degradation or stall while executing TRUNCATE TABLE is a known issue with this statement. Please refer to Bug #68184:Truncate table causes innodb  stalls. There are other bug numbers opened for prior versions as well.

You can use:

CREATE TABLE log_table_new LIKE log_table;
RENAME TABLE log_table TO log_table_old, log_table_new TO log_table;
DROP TABLE log_table_old;

It gets tricky for tables with AUTO_INCREMENT values: new table is created with an AUTO_INCREMENT value which is immediately taken in the working table. If you do no want to  use same values, you can:

ALTER TABLE log_table_new AUTO_INCREMENT=some high enough value;


Get another function to return timestamp in PostgreSQL

Your function is insecure and vulnerable to SQL injection:
SELECT f_last_update1('sometable; DROP TABLE sometable; SELECT current_timestamp');

Use
CREATE OR REPLACE FUNCTION f_last_update1(
   IN  nomSchema text,
   IN  nomTable text,
   OUT updated timestamp)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format(
              'SELECT max(updated) FROM %I.%I',
              nomSchema,
              nomTable
           )
      INTO updated;
END
$func$;


RDS MySQL 8.0.16 has empty "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS" table

The new MySQL v8.0.16 image on RDS (as of writing this) is missing spatial references other than SRID 0.

As a workaround, you can create a new instance with v8.0.15 and then perform the minor upgrade (requires reboot).

select count(*) from INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS

...returns:

 5152


Get distinct count from row and display in sepearte column

The question as I understand it is two-fold:

How to count some value occuring in a separate column
How to autogenerate column list based on result of some query

While 1 is solvable easily, I am quite certain you won't be able to acheive 2.

The solution for 1 could be as following.

Given the test tables and data:

CREATE TABLE sections(
  id INT UNSIGNED PRIMARY KEY,
  course_id INT UNSIGNED,
  name VARCHAR(32));
INSERT INTO sections(id,course_id,name)
VALUES (1,1,'Quants'),
  (4,1,'Verbal');
CREATE TABLE levels(
  id INT UNSIGNED PRIMARY KEY,
  section_id INT UNSIGNED,
  section_type INT UNSIGNED,
  name VARCHAR(32));
INSERT INTO levels(id,section_id,section_type,name)
VALUES (1,1,1,'aaa'),
  (2,1,1,'bbb'),
  (3,1,2,'ccc'),
  (4,1,1,'ddd');

You can get desired output with a:

SELECT a.name, a.id,
  SUM( CASE b.section_type WHEN 1 THEN 1 ELSE 0 END ) as section_type_1,
  SUM( CASE b.section_type WHEN 2 THEN 1 ELSE 0 END ) as section_type_2
FROM sections a INNER JOIN levels b ON a.id = b.section_id
WHERE a.course_id =1
GROUP BY a.course_id;


Streaming replication in postgres question

This is not an inherent feature of PostgreSQL.  Whether your application will connect to the replica (or even knows that a replica exists in the first place) is up to your application, framework, library, connection pooler, or networking.  And their respective configurations.

If it does connect to the replica, that connection will be read only, unless you do something to promote the replica.  Autopromotion is not an inherent feature of PostgreSQL, you would have had to install and configure something to make it happen.


Changing authentication to md5 in pg_hba.conf won't help you if the password hash is already in the scram format.  You need to set password_encryption TO md5 and then change your password (possibly back to the same thing it already is) to get the password stored in the md5 format.

JDBC which doesn't seem to be maintained anymore

JDBC is maintained.  Perhaps you can't figure out how to upgrade it, but that is a different problem than it not being maintained.
SCRAM does work with JDBC starting in version 42.2.1 (current version is 42.2.18)


Issue with DECLARE @CutOffDate DATE = DATEADD(YEAR), @NumberOfYears, @StartDate);

I notice a case-sensitive difference between your declaration @CutoffDate, and @CutOffDate from the error message.

Check to see if you're not using a case-sensitive collation.


It looks like you're using SQL Server Management Studio (SSMS) to query your server. SSMS will open one session for your query tab, plus another session to populate the Object Explorer.

By default, SSMS also has IntelliSense enabled. IntelliSense will make a high volume of very quick database queries in order to validate your query against database schema metadata. Each of the queries will involve a login to the server. I suspect this is responsible for the majority of what you are seeing. One quick way to verify is to disable IntelliSense on your SSMS. Alternatively, you could run you test from another tool that is less "chatty" with the database (such as the SqlServer PowerShell module or sqlcmd CLI).


How can I monitor the progress of an import of a large .sql file?

If you're  just importing from a dump file from the CLI on *nix, e.g.

mysql -uxxx -pxxx dbname < /sqlfile.sql

then first install pipe viewer on your OS then try something like this:

pv sqlfile.sql | mysql -uxxx -pxxxx dbname

which will show a progress bar as the program runs.

It's very useful and you can also use it to get an estimate for mysqldump progress.

pv dumps the sqlfile.sql and passes them to mysql (because of the pipe operator). While it is dumping, it shows the progress. The cool thing is that mysql takes the data only as fast as it can progress it, so pv can show the progress of the import. I do not have any proof. But it seems so. I guess there is some buffer used, but at some point I think mysql does not read any more data when it is still busy processing.


The number of pools does not change the total amount of pages innodb would use. A page exists on one of the pools, not a copy of it in each pool. That's why your 6GB database doesn't take much more than 6GB, the extra probably being for indexes.

From the documentation:

http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances

  The number of regions that the InnoDB buffer pool is divided into. For
  systems with buffer pools in the multi-gigabyte range, dividing the
  buffer pool into separate instances can improve concurrency, by
  reducing contention as different threads read and write to cached
  pages. Each page that is stored in or read from the buffer pool is
  assigned to one of the buffer pool instances randomly, using a hashing
  function. Each buffer pool manages its own free lists, flush lists,
  LRUs, and all other data structures connected to a buffer pool, and is
  protected by its own buffer pool mutex.


Synchronize MySQL database from remote MySQL database

Seems like the migration wizard (available from menu Database) is a better choice for this scenario.


Can multi indexing speed up "AND" of comparisons?

A multi-column index cannot be used for both conditions. For example, an index on (a, b) could be used for the condition a > 30, but not for b < 20.

If one of the conditions alone is selective enough and the other does not reduce the result set considerably, just create an index for that condition.

If you need index support for both conditions because neither of them is selective enough on its own, create two indexes, one for each condition. Then PostgreSQL can use a bitmap index scan on both indexes and a “bitmap or” to combine the results.


Use InnoDB.  At some point, it will be the 'only' one supported.  Anyway, it is actually faster in many benchmarks.

You are talking about dozens or maybe hundreds of rows in all the the tables combined.  You will have trouble noticing sluggishness until you get millions of rows.  "Eschew premature optimization."

On the other hand, if you have a query that is demonstrably slow, let's see EXPLAIN SELECT ... and SHOW CREATE TABLE.  There could be some trivial fix for it.


Oracle does not care about the OS timestamp on data files.  When it opens files it looks at the scn in all of the file headers and compares them to what is recorded in the control file.  If all scn match, the database opens.  If there is a mismatch between the scn of a file and that in the control file, it will throw an appropriate error.  But none of this has to do with the OS time stamp on the file.


Depending on how many values you have for the 'foo' column, you may be able to accomplish this with a union:

select   foo, sum(position)/count(position) as average_position
from     (
    select   foo, position
    from     my_table
    where    foo = 'bar1'
    order by date desc limit 3
union all
    select   foo, position
    from     my_table
    where    foo = 'bar2'
    order by date desc limit 3
) as subquery
group by foo;


SQL Server 2012 - Know when a SEQUENCE has recycled or is nearing cycle phase

There is no metadata view that is going to clearly give you this information. Your best bet would be to have a scheduled process that runs every few minutes and looks at sys.sequences and evaluates if the current_value is less than the prior current_value, in which case you would know that it had cycled.

You can also compare the current_value to the maximum_value to see how close you are to hitting the cycle point.

A very basic view of this would be the following with a small sequence, you can see the initial values remaining, what it looks like after using a few sequences, and then how it gets reset as the sequence cycles.

CREATE SEQUENCE dbo.TestSeq
AS SMALLINT
MINVALUE 1
MAXVALUE 10
START WITH 1
INCREMENT BY 1
CYCLE;

SELECT  name ,
        current_value ,
        maximum_value ,
        CAST(maximum_value AS BIGINT) - CAST(current_value AS BIGINT) AS Values_Remaining
FROM    sys.sequences
WHERE   name = 'TestSeq';
GO

SELECT NEXT VALUE FOR dbo.TestSeq;
GO 5

SELECT  name ,
        current_value ,
        maximum_value ,
        CAST(maximum_value AS BIGINT) - CAST(current_value AS BIGINT) AS Values_Remaining
FROM    sys.sequences
WHERE   name = 'TestSeq';
GO

SELECT NEXT VALUE FOR dbo.TestSeq;
GO 6

SELECT  name ,
        current_value ,
        maximum_value ,
        CAST(maximum_value AS BIGINT) - CAST(current_value AS BIGINT) AS Values_Remaining
FROM    sys.sequences
WHERE   name = 'TestSeq';
GO


Longitude and Latitude order with ST_X, and ST_Y on SRID 4326

Which makes perfect sense as there are no 113 degrees of latitude - but when I swap the lat/lon parameters - everything works! However when I query the lat/lon the results are backwards.

That's because MySQL and Microsoft do the absolutely insane thing of swapping the order in geographic representations because that's what the EPSG says. Oracle and PostGIS do not. There is no X/Y in MySQL Geographic Representation. With MySQL v8, they tried to fix this while maintaining total and utter insanity by introducing

ST_Longitude()
ST_Latitude()

For more information check out Does MySQL store point datatypes as LAT LNG or LNG LAT?


SQL Server - creating a historical data backup

I've run out of space on my SQL Server 2008 Express DB.

Best is to script out the schema using SSMS. Then

Take a full backup of your database --or--
bcp out the data from the tables. (note: the script will bcp out in native format. Change it to bcp out data in human readable format - by removing -n)


SQLRemote: Implicit synch of connected objects

It appears that this can be done with subqueries inside publications.

From documentation:

CREATE PUBLICATION SalesRepData (
TABLE SalesReps
TABLE Customers
   SUBSCRIBE BY rep_key
TABLE Contacts
SUBSCRIBE BY (SELECT rep_key
   FROM Customers
   WHERE Contacts.cust_key = Customers.cust_key )
);

This would catch all customer contacts for sales rep's customer.

Quiet important seems to be, that this needs to be updated once the connection has been updated -- e.g. a sales rep is getting new customers. This needs to be done with an BEFORE trigger for UPDATE/INSERT/DELETE.


You are using the wrong quotes.  Use ` instead of '

CREATE TABLE `topics` (
  `topicid` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `creator` varchar(255) NOT NULL,
  `createtime` datetime NOT NULL,
  `content` text NOT NULL
) ENGINE=InnoDB;

I also would recommend specifying the engine.


What are the advantages and disadvantages of Firebase for a database?

You haven't really given us much info about what this data is going to be used for. I mean, you have said what data is going to be stored, but what are you going to do with it?

If your purpose is storing the data then reporting on it, then I think you're looking in the wrong place. A simple MySQL or SQL Database would do just fine and the reporting tools are readily available.

However if your going to be linking to something such as a web or mobile application where the data is constantly changing by multiple users (all accessing the same database stored in the cloud) then Firebase is the way to go.

So, your Pro's and Con's:

Pro's

If your app does run of a centralized DB, and is updated by a lot of
users - then it's more than capable of handling the Real-Time data
updates between devices.
Stored in the cloud so readily available everywhere.
Cross Platform API (If you are using this DB with an App)
They Host the data. -Meaning if you are storing a lot of data, you don't have to worry about hardware!

Con's:

Unless your app runs of one centralized database updated by a vast quantity of users, it's a major overkill.
Storage format is entirely different to that of SQL, (Firebase uses JSON) so you wouldn't be able to migrate that easily.
Reporting tools won't be anywhere near the ones of standard SQL.
Costs! -Limited to 100 Connections and 1GB of Storage! (see this for more details on limitations!)
You don't host the data, Firebase does. And depending on which server you get put on, viewing there up time there seems to be a lot of disruption lately.

Note: I included "Who hosts the data" in both the pro's and con's. That's because you never told us how much data you were storing and who was going to be accessing it.


PK as ROWGUIDCOL or use a separate rowguid column?

Basically, they say that the application should never bring into its domain something that is used by replication only (it's only "DBA stuff" for them).

I agree completely. But... the primary key isn't only used for replication (presumably the application uses it in some way). The argument makes no sense in this context.

In any event, as far as I'm aware, there are only 2 ways for this "DBA stuff" to cross the domain boundary:

If the application is using queries that reference the ROWGUIDCOL column like this:

DECLARE @a table (id uniqueidentifier ROWGUIDCOL);

SELECT ROWGUIDCOL FROM @a;

I'm assuming none of your columns have this property yet, so the application wouldn't be doing this. (By the way, ROWGUIDCOL is a completely separate concept from replication. It just so happens that merge replication uses it.)
The primary key column would no longer be updatable. If the application is doing this and changes aren't going to be made to use another algorithm, there's no choice but to add a new column to the table, and therefore no discussion is necessary.

Other than those behaviours, the ROWGUIDCOL property is completely transparent. You can add it, and the application would never know. Any type of data replication scenario should be as transparent as possible to applications.


Just what it means. Some commands cannot be invoked from within stored routines. Creation of a stored routine is one of them. As another example, you cannot LOAD DATA INFILE from a stored routine.

You may try and do it with a prepared-statement, only to find that CREATE PROCEDURE cannot be invoked via prepared statement.

Sorry - no way around this that I know, and I've done my share of search on this.


join and repeat value from a column of another table

Since the tables have no column in common, we'll have to use the rownum pseudocolumn which is asigned to every row in a resultset. Do not confuse with rowid which is hash-like and exists phisycally in the table. Since rowids go 1,2,3...n, they provide a way to join two unrelated tables.

select
    t1.c1,
    t2.c2
from
    (select rownum rn,c1 from a) t1 left join
    (select rownum rn,c2 from b) t2 on (t1.rn=t2.rn)

NOTE:

I assumed A had more rows than B so I made a left join. You can turn things around should B have more rows than A.


SQL server 2016 restore impact of mirroring configuration?

You cannot just delete or drop db when mirroring is configured. You have to remove mirroring alter database dbname set partner off.

Then drop database on primary.

Then restore from the desired backup.

Then reset up mirroring - take full and log backup and restore it to server B with NO_RECOVERY.

Remember : Mirroring is deprecated technology. So you should either use basic availability group or if you are on Enterprise edition then go with AlwaysON.


What is the main difference between '[[:space:]]' and '\s' in Oracle pl-sql regular expression?

The Oracle documentation confirms that the POSIX expression [[:space:]] is equivalent to the Perl-style \s. I personally prefer \s because it's shorter, and also supported by many text editors.
In your example you have [[:space:]] (one whitespace character) but \s+ (one or more whitespace characters). Maybe that should have been [[:space:]]+.


That warning is just checking to see if you've ever backed up your encryptor certificate using T-SQL.  This could put you in situation where the database backup would be unreadable if you were to lose that SQL Server instance for whatever reason.

No, you don't need a regular backup cycle for the cert. You wouldn't need to back it up after the initial one unless you've lost your copy.

That warning is just checking to see if the pvt_key_last_backup_date IS NULL.

    select pvt_key_last_backup_date, *
   from master.sys.certificates
   where name = 'BackupCertifcateName'

Perhaps that backup was taken with a different certificate than the one you've backed up?

You can check to see which Certificate was used with this query:

SELECT
    c.name as backupCertName,
    key_algorithm,
    encryptor_thumbprint,
    encryptor_type,
    backup_start_date,
    type as backupType
FROM msdb.dbo.backupset bks
JOIN master.sys.certificates c on c.thumbprint = bks.encryptor_thumbprint
WHERE database_name = 'DB_NAME'
AND Encryptor_type = 'CERTIFICATE'
AND type <> 'L'
ORDER BY backup_start_date  DESC


Index organized tables (IOTs) don't actually have a normal heap table-type segment as their main storage - that's pretty much the point of IOTs. The data is stored in a B-Tree structure, essentially like a normal Oracle index.

So you won't see any segments in dba_segments for the table itself. What you will see are (index) segments for the primary key, and possibly overflow segments (this is used when you want to store parts of the row outside the B-Tree structure).

In dba_tables, you'll see one entry for your table with an IOT_TYPE of IOT (and pretty much everything else null or defaulted), and, if you have an overflow segment, another entry with IOT_TYPE of IOT_OVERFLOW and IOT_NAME the name of your table. (The table name for the overflow segment is system-generated, something like SYS_IOT_OVER_XYZ.)

See also:

Using Index-Organized Tables for Performance
Managing Index-Organized Tables


mysql dump best practices for development

mysqldump --opt --where="1 limit 10" database

It's an SQL injection:
The --where option is basically appended to a query of the form SELECT * from table WHERE , so in this case you get SELECT * from table WHERE 1 limit 10. Without the 1, you would have an invalid query. Specifying 1 for a where clause (since 1 is always true) simply selects all records.

Source: Limiting the number of records from mysqldump?


10MB is ridiculous, IMHO. Is your I/O subsystem that slow where limiting growth to such a small increment is necessary? Ideally you should not be observing AutoGrow at all, by sizing the log large enough to handle your largest transactions anyway. And only use AutoGrow as a crutch to protect you from abnormal events, such as this one, in which case it should be rather large.

And no, I would not reduce your log file to 200MB, and I'm not sure how that is "more sensible." Growth events are expensive, particularly with the log file, since the expanded portion of the file has to be zeroed out. You want to size the log file in such a way that you will avoid any predictable growth events. If you will be performing another similar index rebuild in the future, I don't understand what you would gain by shrinking the log file to 200MB in the meantime. Are you going to lease the freed up space out to someone else, then kick them out when your log needs the space again? I would say leave the log at 32GB (shrinking it and expanding it one event, as the script you pointed out suggested) and monitor for AutoGrow events (these are captured in the default trace, so you don't have to set anything up, just look at it once in a while). (On more modern versions, should think about using Extended Events for tracking growth events instead.) This will tell you if you need to consider making it even bigger.

There is no prize for smallest log file - you want it to be large enough to accommodate all of your normal (and abnormal) log activity without having to grow.


pgAdmin 4 v4 does not prompt to save on page refresh

Version pgAdmin 4 v4.2

There is a setting: File -> Preferences -> Browser -> Display -> Confirm on close or refresh

It worked fine.

I've created an issue here which is rejected: https://redmine.postgresql.org/issues/3993#change-15078

In pgAdmin 4 v4.1:
File --> Preferences --> Query Tool --> Options --> Prompt to save unsaved query changes?
Didn't work for me so I've updated to 4.2


Why Wikipedia using blob type instead of text type?

The 'right' way to store text is to use TEXT with CHARACTER SET ut8fmb4.  However, that requires the you know what encoding exists in the client, so that it can be converted (by MySQL) as it is stored.

By storing into BLOB, the above issue is eliminated.  However, that leads to another issue -- how to display the text.

Another possible excuse for BLOB is that Wikipedia was probably started before utf8mb4 was added to MySQL.

(We can only guess.)