What's the maximum acceptable latency if using a remote MySQL DB server?

There is no single correct answer to this question.  The MySQL client/server protocol has no limitations on toleration of round trip time, but responsiveness of your application can be dramatically affected.

Fundamentally, though, you can't look at any number and know intuitively that it will work, without intimate familiarity with your application.

I recently had to migrate a legacy application piecemeal from on-premise to cloud -- database first, then the app tiers.  The round-trip time between the two sites was 23ms.  "Cake," I thought... "23 ms is no problem."

Unfortunately, it was a problem, because the original developers had succumbed to one of the fallacies of distributed computing: "The latency is zero."

It was a 3-tiered app, and investigation of multi-second page loads revealed that every method call to the application tier on each page on the site triggered a minimum of 13 round trips to the database.  GetUserAttributes, GetAccountBalance, GetProductListings... you get the idea... so here's a simple page making 39 largely unnecessary database requests and requiring ~750ms just in transit time, instead of just a few.  (Why so many? They historically used two different ORMs and were still setting up both for each method, redundantly calling USE database and SET autocommit, etc.)  This is an extreme example, but on a LAN, this is the kind of thing you get away with, but that causes your performance to be impacted exponentially as RTT increases.

Where you can achieve hundreds of round trips per second on a LAN, that number drops to about 40 with the 23 ms added in.  That could easily be a factor of 10 or more, even though by human reckoning, 23 ms seems very quick.  Even single-digit round-trips can have a counter-intuitively significant impact on performance.

Fortunately, there's a fairly easy way to simulate latency to a MySQL server in an absolutely meaningful way -- by not simulating it, but actually injecting physical latency: configure a transparent TCP proxy in a remote data center.  For example, HAProxy.  Simple sample config:

listen mysql
  mode tcp
  bind *:3306
  server mysql-server 203.0.113.1:3306
  timeout tunnel 28800s

This overlooks features you'd want to use in a permanent deployment, like health-checks and ACLs, but if you set this up on a test machine in the remote data center, connect to it on port 3306, it would hairpin the connection back to the MySQL server at 203.0.113.1.  HAProxy is payload agnostic in TCP mode, and it's just forwarding packet payload (using splice where available, to avoid much time in userspace) so it will contribute no meaningful latency itself (and can handle a surprising amount of traffic on tiny machines).  If your app and db are local and this proxy is remote, this will perfectly model what your application behavior will be like with double the round trip time between the two data centers (because app to db, db to app traverses the entire path twice, not once)... the simulation is a worse than worst case scenario.

If this performance proves acceptable, your proposed round trip time is no problem.  If it isn't, you found the problem before it was too late.


Constraining foreign keys to two parent tables

Here is the definitive answer for which I can't take credit. That belongs to ypercube who has given me an insight into the power of FOREIGN KEYs with multiple fields. I've taken the liberty of removing most of the previous discussion - it can be followed through the edits.

My first (primitive) answer would have worked, but the DDL below is so much more elegant - it's like comparing the Lascaux cave paintings with Van Gogh's masterpieces.

Definitive DDL/DML with test (using MySQL).

Power supply table.

CREATE TABLE power_supply
(
  part_no INT PRIMARY KEY,
  weight INT,
  voltage INT,
  UNIQUE(part_no, voltage)
);

Motor table.

CREATE TABLE motor
(
  part_no INT PRIMARY KEY,
  model VARCHAR(45),
  voltage INT,
  UNIQUE (part_no, voltage)
);

Robot table.

CREATE TABLE robot
(
  name VARCHAR(30) PRIMARY KEY,
  power_supply INT,
  motor INT,
  voltage INT,
  FOREIGN KEY (power_supply, voltage)
        REFERENCES power_supply (part_no, voltage),
  FOREIGN KEY (motor, voltage)
        REFERENCES motor (part_no, voltage)
);

DML.

INSERT INTO power_supply VALUES(1, 25, 10);
INSERT INTO power_supply VALUES(2, 25, 10);
INSERT INTO power_supply VALUES(3, 15, 15);
INSERT INTO power_supply VALUES(4, 25, 15);
INSERT INTO power_supply VALUES(5, 25, 15);
INSERT INTO power_supply VALUES(6, 25, 20);
INSERT INTO power_supply VALUES(7, 25, 20);
INSERT INTO power_supply VALUES(8, 25, 25);

INSERT INTO motor VALUES(1, 'Model Name', 10);
INSERT INTO motor VALUES(2, 'Model Name', 10);
INSERT INTO motor VALUES(3, 'Model Name', 15);
INSERT INTO motor VALUES(4, 'Model Name', 15);
INSERT INTO motor VALUES(5, 'Model Name', 20);
INSERT INTO motor VALUES(6, 'Model Name', 20);
INSERT INTO motor VALUES(7, 'Model Name', 25);

Test.

INSERT INTO robot VALUES('Robo Name', 2, 6, 15); -- Voltages don't match, update fails
INSERT INTO robot VALUES('Robo Name', 2, 2, 10); -- Voltages match, update succeeds


How can I cache queries in sqlalchemy just like I do it in mysql by using SQL_CACHE keyword?

You need use a prefix_with.
For example, this:

query = session.query(User.name).prefix_with('SQL_CACHE')

would render:

SELECT SQL_CACHE users.name AS users_name FROM users


Is there to get space used in files for SQL Server database without negatively affecting IO?

SELECT
 DB.name,
 MF.physical_name,
 SUM(MF.size * 8 / 1024.0 /1024.0) AS FileSizeGB
FROM
 sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.source_database_id is null -- exclude snapshots
GROUP BY DB.name, MF.file_id, MF.physical_name
ORDER BY FileSizeGB DESC;

or

SELECT
 DB_Name(vfs.DbId) DBname,
 MF.physical_name,
 vfs.DBid,vfs.FileID,
 vfs.BytesOnDisk
FROM ::fn_virtualfilestats(null,null) vfs
JOIN sys.master_files MF ON vfs.DBid = MF.database_id
 AND vfs.FileId = MF.file_id


Yes, you can (ab)use NULL for this. Unique indexes allow multiple rows where the unique field is NULL. You just have to make sure you make the field nullable.

Note that this only applies to unique keys, not primary keys - primary keys cannot be nullable.


Data Extract and submit across the network

If I understand correctly, I wouldn't use SSIS. This is invoked on demand or scheduled to shift data whereas I read this as "automatic shift" of data

So, I'd consider replication or database mirroring and the let the DB engine move data for you:

Replication: use this if you need a subset of the datas (not all column, rows or tables)
Mirroring: use this to copy the entire database (you'd need a snapshot on the mirror to get data out though)


MySQL How to calculate Partnership Scores from Cricket ball by ball database [Solved]

Your grouping is a player pair so your GROUP BY expression needs to reflect this. The striker / non_striker must combined form a unique value. Start with an IF statement to avoid duplicates. I've used a mathematical expression that won't cause duplicates.

So with your query use the following group by:

GROUP BY IF(striker > non_striker,
            striker * 30 + non_striker,
            non_striker * 30 + striker)

This works because assuming all player ids are < 30. If this isn't the case it can be replaced by a higher value.

Alternately you could use a string based mechanism to get 1-2 as unique identifier. Note a separator - is needed (and can be anything not in the result set), otherwise striker,non_striker value 1,234 would be grouped the same as 12,34

GROUP BY IF(striker > non_striker,
            CONCAT(striker, '-', non_striker),
            CONCAT(non_striker, '-', striker)


How to solve multiple minimal covers for functional dependencies?

Starting from the dependencies:

b->c, dg->ce, bc->dg, e->a, g->bd

and splitting the right hand side, one obtain:

b->c, dg->c, dg->e, bc->d, bc->g, e->a, g->b, g->d

(Note that you have written bd->d instead of bc->d).

Then you have correctly found that the attribute d is  extraneous both in dg->c and dg->e, while c is extraneous both in bc->d and in bc->g. So that the set of dependencies is now:

b->c, g->c, g->e, b->d, b->g, e->a, g->b, g->d

Then you say:

  For g->bd the attribute d is extraneous because if we have g->b then g+ = {gbcd} which contains d

but no dependency g->bd exists (it has already been split in the two dependencies g->d and g->b).

The last step is to find the superfluous dependencies in:

b->c, g->c, g->e, b->d, b->g, e->a, g->b, g->d

that are b->d and b->c. So at the end we have:

g->c, g->e, b->g, e->a, g->b, g->d

which is exactly the answer given by the tool.

Finally, consider that actually a set of functional dependencies could have more than one minimal cover: this depends on the order in which the attributes and the dependencies are considered for the elimination.


Why does dropping foreign keys take long?

Dropping a constraint requires a Sch-M (Schema Modification) lock that will block others to query the table during the modification. You are probably waiting to get that lock and has to wait until all currently running queries against that table are finished.
A running query has a Sch-S (Schema Stability) lock on the table and that lock is incompatible with a Sch-M lock.
From Lock Modes, Schema Locks

The Database Engine uses schema modification (Sch-M) locks during a
table data definition language (DDL) operation, such as adding a
column or dropping a table. During the time that it is held, the Sch-M
lock prevents concurrent access to the table. This means the Sch-M
lock blocks all outside operations until the lock is released.
Some data manipulation language (DML) operations, such as table truncation,
use Sch-M locks to prevent access to affected tables by concurrent
operations.
The Database Engine uses schema stability (Sch-S) locks
when compiling and executing queries. Sch-S locks do not block any
transactional locks, including exclusive (X) locks. Therefore, other
transactions, including those with X locks on a table, continue to run
while a query is being compiled. However, concurrent DDL operations,
and concurrent DML operations that acquire Sch-M locks, cannot be
performed on the table.


Is it "OK" to have a table that only contain foreign keys?

There is no problem having a table that consists of foreign keys only.  In your particular example it would be OK to remove the OrderID. Relational theory states that each table should have a candidate key so, ideally, the business rule you mention would be in place to make the foreign key only table a "good" table.  In practice, DBMS software will not care and will allow a table without a unique constraint.

There will not, generally, be any problem with inserts as long as you observe the uniqueness business rules you decide for your system. Conversely, without uniqueness, you may end up with duplicate rows from a SELECT which the application would have to deal with.

If you add further columns in the future there will be no problems due to the existing columns all being foreign keys.

This situation is very common when there is a many-to-many relationship between entity types. This is implemented as an intersection table containing the foreign keys.


Does the scope for 'Alter Any Database' operate similar to 'Create Any Database'?

Why is this so hard to test for yourself? As sa or similar:

CREATE LOGIN Jake WITH PASSWORD = N'x', CHECK_POLICY = OFF;
GO
GRANT ALTER ANY DATABASE TO Jake;
GO
CREATE DATABASE IBelongToAaron;
GO

Now log in as Jake:

CREATE DATABASE IBelongToJake;
GO
ALTER DATABASE IBelongToJake SET RECOVERY FULL; -- success!
GO
ALTER DATABASE IBelongToAaron SET RECOVERY FULL; -- success!

So no, ALTER ANY DATABASE works as advertised, which is probably why it's not called ALTER ANY DATABASE THAT I CREATED OR OWN.


Optimize a MySQL Database using MySQL Workbench

The maintenance feature is a bit hidden in MySQL Workbench. You can reach it by opening your connection as usual and right clicking on your schema to open the schema inspector:

You will get a new tab in the editor area with many pages for inspection/maintenance. Click on the Maintenance button at the bottom to open this page:

From there it should be clear what to do.


How to Remove Multiple Databases Marked with "Recovery Pending"

You need to get the meta-data off your SQL Server (as seen in sys.databases) and that is indeed the DROP database command (which is performed by SSMS when you right-click and Delete). Either do them one-by-one, as in

DROP DATABASE a DROP DATABASE b

Or more then one at once, as in

DROP DATABASE a, b

You can also generate those DROP commands, such as:

SELECT 'DROP DATABASE ' + QUOTENAME(name)
FROM sys.databases
WHERE state_desc = 'RECOVERY_PENDING'


Replace query result with case/when failing

You don't mention what DBMS, but you probably need IS NULL instead of = NULL. However,  you're creating a complex case when this is a texbook use of COALESCE ():

SELECT
    "OEM_Description" = COALESCE( IT.[Description], I.[Description] ),
    C.Name as Customer_Name

Alternate solution based on comment:

IS NULL is not a function but a replacement for = NULL in your original like this:

SELECT
    "OEM_Description" =
CASE
   WHEN  (C.[Language Code] = 'ENG' and IT.[Description] IS NULL)
   THEN I.[Description]
  ELSE IT.[Description]
END,
C.Name as Customer_Name


Several problems with your trigger:

You are assuming the trigger fires per row, whereas triggers fire per statement. This means you can't just assign a variable because this won't work if the update affects more than one row.
Your query to assign a variable doesn't pull from anywhere - you need to reference the inserted and/or deleted pseudo-tables.
Checking UPDATE() only tells you that the column was referenced, but it doesn't tell you whether the value has changed.

Here is how I would re-write your trigger:

-- this assumes Death is not nullable

ALTER TRIGGER [dbo].[lastdeathtrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS
IF (UPDATE(Death))
BEGIN
  SET NOCOUNT ON;

  INSERT RF_WORLD.dbo.lastdeath(serial)
    SELECT i.serial FROM inserted AS i
    INNER JOIN deleted AS d
    ON i.serial = d.serial
    AND i.Death <> d.Death;
END
GO


PL-SQL "LAST_VALUE" function does not give the desired result

Explaination
The ORDER BY clause (in an analytical function), implies a window clause of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
You need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
Since this will apply to each row, you'll need a DISTINCT clause.
Example
with data(col_id, amount, operation_date ) as (
    select  1,  5000,  to_date( '11/1/2020', 'mm/dd/yyyy' ) from dual union all
    select  2,  1000,  to_date( '11/1/2020', 'mm/dd/yyyy' ) from dual union all
    select  3,  3000,  to_date( '11/1/2020', 'mm/dd/yyyy' ) from dual union all
    select  1,  1000,  to_date( '11/14/2020', 'mm/dd/yyyy' ) from dual union all
    select  2,   500,  to_date( '11/14/2020', 'mm/dd/yyyy' ) from dual
)
select distinct col_id
    , last_value( amount ) over (partition by col_id order by operation_date
                                 rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) amt
from data;

Results:


Do varchar(max), nvarchar(max) and varbinary(max) columns affect select queries?

Examples

Consider your query with 8 filter predicates in your IN clause on a dataset of 10K records.

select UniqueToken
from Books
where UniqueToken in
(
    'Unique token 1',
    'Unique token 2',
    'Unique token 3',
    'Unique token 4',
    'Unique token 5',
    'Unique token 6',
    'Unique token 9999',
    'Unique token 5000'
    -- 10,000 items here
);

A clustered index scan is used, there are no other indexes present on this test table

With a data size of 216 Bytes.

You should also note how even with 8 records, the OR filters are stacking up.

The reads that happened on this table:

Credits to statisticsparser.

When you include the Text column in the select part of your query, the actual data size changes drastically:

select UniqueToken,Text
from Books
where UniqueToken in
(
    'Unique token 1',
    'Unique token 2',
    'Unique token 3',
    'Unique token 4',
    'Unique token 5',
    'Unique token 6',
    'Unique token 9999',
    'Unique token 5000'
    -- 10,000 items here
);

Again, the Clustered index Scan with a residual predicate:

But with a dataset of 32KB.

As there are almost 1000 lob logical reads:

Now, when we create the  two tables in question, and fill them up with the same 10k records

Executing the same select without Text. Remember that we had 99 Logical reads when using the Books Table.

select UniqueToken
from BookUniqueTokens
where UniqueToken in
(
    'Unique token 1',
    'Unique token 2',
    'Unique token 3',
    'Unique token 4',
    'Unique token 5',
    'Unique token 6',
    'Unique token 9999',
    'Unique token 5000'
    -- 10,000 items here
)

The reads on BookUniqueTokens  are lower, 67 instead of 99.

We can track that back to the pages in the original Books table and the pages in the new table without the Text.

Original Books table:

New BookUniqueTokens table

So, all the pages + (2 overhead pages?) are read from the clustered index.

Why is there a difference, and why is the difference not bigger? After all the datasize difference is huge (Lob data <> No Lob data)

Books Data space

BooksWithText Data space

The reason for this is ROW_OVERFLOW_DATA.

When data gets bigger than 8kb the data is stored as ROW_OVERFLOW_DATA on different pages.

Ok, if lob data is stored on different pages, why are the page sizes of these two clustered indexes not the same?

Due to the 24 byte pointer added to the Clustered index to track each of these pages.
After all, sql server needs to know where it can find the lob data.

Source

To answer your questions

  He claimed that [Text] column affects any select query on the
  UniqueToken column.

And

  Does the existence of Text column affect this query's performance? If
  so, how can we optimized it?

If the data stored is actually Lob Data, and the Query provided in the answer is used

It does bring some overhead due to the 24 byte pointers.

Depending on the executions / min not being crazy high, I would say that this is  negligible, even with 100K records.

Remember that this overhead only happens if an index that includes Text is used, such as the clustered index.

But, what if the clustered index scan is used, and the lob data does not exceed 8kb?

If the data does not exceed 8kb, and you have no index on UniqueToken,the overhead could be bigger . even when not selecting the Text column.

Logical reads on 10k records when Text is only 137 characters long (all records):

  Table 'Books2'. Scan count 1, logical reads 419

Due to all this extra data being on the clustered index pages.

Again, an index on UniqueToken (Without including the Text column) will resolve this issue.

As pointed out by @David Browne - Microsoft, you could also store the data off row, as to not add this overhead on the Clustered index when not selecting this Text Column.

  Also, if you do want the text stored off-row, you can force that
  without using a separate table. Just set the 'large value types out of
  row' option with sp_tableoption.
  docs.microsoft.com/en-us/sql/relational-databases

TL;DR

Based on the query given, indexing UniqueToken without including TEXT should resolve your troubles.
Additionally, I would use a temporary table or table type to do the filtering instead of the IN statement.

EDIT:

  yes there is a nonclustered index on UniqueToken

Your example query is not touching the Text column, and based on the query this should be a covering index.

If we test this on the three tables we previously used (UniqueToken + Lob data, Solely UniqueToken, UniqueToken + 137 Char data in nvarchar(max) column)

CREATE INDEX [IX_Books_UniqueToken] ON Books(UniqueToken);
CREATE INDEX [IX_BookUniqueTokens_UniqueToken]  ON BookUniqueTokens(UniqueToken);
CREATE INDEX [IX_Books2_UniqueToken] ON Books2(UniqueToken);

The reads remain the same for these three tables, because the nonclustered index is used.

Table 'Books'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'BookUniqueTokens'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Books2'. Scan count 8, logical reads 16, physical reads 5, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Additional details

by @David Browne - Microsoft

  Also, if you do want the text stored off-row, you can force that
  without using a separate table. Just set the 'large value types out of
  row' option with sp_tableoption.
  docs.microsoft.com/en-us/sql/relational-databases/

Remember that you have to rebuild your indexes for this to take effect on already populated data.

By @Erik Darling

On

MAX Data Types Do WHAT?

Filtering on Lob data sucks.

Memory Grants and Data Size

Your memory grants might go through the roof when using bigger datatypes, impacting performance.


IF EXISTS taking longer than embedded select statement

Any idea why the IF EXISTS would make it run so much longer and do so many more reads? I also changed the select statement to do SELECT TOP 1 [dlc].[id] and I killed it after 2 minutes.

As I explained in my answer to this related question:
How (and why) does TOP impact an execution plan?
Using EXISTS introduces a row goal, where the optimizer produces an execution plan aimed at locating the first row quickly. In doing this, it assumes that the data is uniformly distributed. For example, if statistics show there are 100 expected matches in 100,000 rows, it will assume it will have to read only 1,000 rows to find the first match.
This will result in longer than expected execution times if this assumption turns out to be faulty. For example, if SQL Server chooses an access method (e.g. unordered scan) that happens to locate the first matching value very late on in the search, it could result in an almost complete scan. On the other hand, if a matching row happens to be found amongst the first few rows, performance will be very good. This is the fundamental risk with row goals - inconsistent performance.

As a temporary fix I have changed it to do a count( * ) and assign that value to a variable

It is usually possible to reformulate the query such that a row goal is not assigned. Without the row goal, the query can still terminate when the first matching row is encountered (if written correctly), but the execution plan strategy is likely to be different (and hopefully, more effective). Obviously, count(*) will require reading all rows, so it is not a perfect alternative.
If you are running SQL Server 2008 R2 or later, you can also generally use documented and supported trace flag 4138 to get an execution plan without a row goal. This flag can also be specified using the supported hint OPTION (QUERYTRACEON 4138), though be aware it requires runtime sysadmin permission, unless used with a plan guide.
Unfortunately
None of the above is functional with an IF EXISTS conditional statement. It only applies to regular DML. It will work with the alternate SELECT TOP (1) formulation you tried. That may well be better than using COUNT(*), which has to count all qualified rows, as previously mentioned.
That said, there are any number of ways to express this requirement that will allow you to avoid or control the row goal, while terminating the search early. One last example:
DECLARE @Exists bit;

SELECT @Exists =
    CASE
        WHEN EXISTS
        (
            SELECT [dlc].[ID]
            FROM TableDLC [dlc]
            JOIN TableD [d]
            ON [d].[ID] = [dlc].[ID]
            JOIN TableC [c]
            ON [c].[ID] = [d].[ID2]
            WHERE [c].[Name] <> [dlc].[Name]
        )
        THEN CONVERT(bit, 1)
        ELSE CONVERT(bit, 0)
    END
OPTION (QUERYTRACEON 4138);

IF @Exists = 1
BEGIN
    ...
END;


Timestamp and substring with regular expression

Ok, because the function is called substring, I think it only works for the string data type but not for the timestamp data type

To solve this I use the regular expression on the timestamp by converting the timestamp into a text first using the to_char function and then I use the substring function as follows:

select substring(to_char(time,'YYYY-MM-DD HH:MM:SS') from '.*') from test;


how to dump via mysqldump hugh table into chunks

You can use primary key to specify to dump only id in those range

mysqldump -u root -p db_name table_name --where='id  < 10000' > test1.sql
mysqldump -u root -p db_name table_name --where='id between 10000 and 20000' > test2.sql
.....
 mysqldump -u root -p db_name table_name --where='id between 900000 and 1000000' > test11.sql


SSAS Tabular Cube partitioning - number of partitions

In SSAS 2014 Tabular partitions inside a table process serially. In SSAS 2016 Tabular partitions inside a table can process in parallel. So in your version partitioning won't speed up processing if you process the whole table.

However partitioning is great for use in incremental processing. If you partition by year and only 2017 rows changed then you can just process the 2017 partition. Also to archive off data older than 10 years old you can drop the oldest partition.

I don't know where the 2 partition rule you mentioned came from but don't believe it makes sense. If you have a reference for that assertion with some context then feel free to share.


What events still occur within a transaction that gets rolled back?

For statements that are allowed to execute within an explicit transaction (i.e. BEGIN TRAN, or heaven forbid IMPLICIT_TRANSACTIONS is ON), I am only aware of the following two that will not be affected by a ROLLBACK (though technically both are somewhat "cheating" in a sense):

DML statements against table variables (variable, even table variables, do not participate in transactions)

statements executed via a linked server and OPENQUERY (even if using a loop back server definition to connect to the current instance) if you have the linked server property of "remote proc transaction promotion" disabled (so that it doesn't attempt to enlist in the current transaction).
OPENQUERY (with that one option disabled) works because it makes a separate connection.  There are other, similar methods of making a separate connection within T-SQL. David Browne, in a comment, mentioned extended stored procedures. We can also add SQLCLR methods (no matter what T-SQL object type they are exposed as) to that list, but only if making a regular / external connection, and specifying "enlist=false;" in the connection string. We can probably also add the OLE Automation stored procedures (i.e. sp_OA*), but I am not 100% sure of these.

There might be one or two statements that can be executed within an explicit transaction but cannot be rolled back, and I think I maybe ran into that many years ago, but I don't recall what it is and could have been user error (some other user, of course ;-), so you would have to consult the documentation for specifics of each statement / operation and/or test as many as you can (sometimes the documentation is incorrect ... it happens).

Not sure if this counts or not as it isn't an explicit change, but is not something that goes back to what it was before the transaction started (because it doesn't even make sense that it could): Tibor Karaszi noted in a comment that "consuming an identity/sequence value" could qualify. I have tested and confirmed that sequences do not give back the requested values.
Similarly, another value that is database state, and not specifically user data, that is not affected by a rollback is the current / last-used timestamp value (this is per-database and is returned by the system variable @@DBTS; also confirmed via test)

Not sure if these count or not as they are neither an explicit change nor even the state of the database, but their values are not affected by a rollback: in a comment, David Browne mentioned SESSION_CONTEXT, which is a property of each session. Along those same lines, we can include the related / similar CONTEXT_INFO

if a cursor that just executed a stored procedure 100 times that updated a table on each iteration, would all those updates get rolled back?

Yes, this is the purpose of explicit transactions: to group statements together into an atomic unit of work.

Would a DDL statement get rolled back?...such as DROP TABLE or CREATE VIEW? What about DROP DATABASE?

Some (maybe most) yes. Some no. You probably should test. For example, here is some DDL that does roll-back:
USE [tempdb];

IF (OBJECT_ID(N'dbo.DropMe') IS NOT NULL)
BEGIN
  DROP TABLE dbo.DropMe;
END;

CREATE TABLE dbo.DropMe (col1 INT);

BEGIN TRAN;

DROP TABLE dbo.DropMe;

ROLLBACK TRAN;

SELECT * FROM dbo.[DropMe];

But, conversely, here is some DDL that cannot be executed within an explicit (or implicit) transaction:
CREATE DATABASE [DropMe];
BEGIN TRAN;
DROP DATABASE [DropMe];
/*
Msg 574, Level 16, State 0, Line XXXXX
DROP DATABASE statement cannot be used inside a user transaction.
*/
ROLLBACK;

So, just take the two examples above and rework them for other statements that you are either curious about or need a definitive answer on.


Count between dates and operate between rows - SQL

WHERE timestamp >= '2021-04-02'
  AND timestamp  < '2021-04-02' + INTERVAL 1 MONTH
  AND status = "done"

(If you need to discuss further, please provide SHOW CREATE TABLE and more details about the desired query.)
And this index may help with performance:
INDEX(status, timestamp)


Is there a way stop the "." (period) acting as a word breaker in FullText Indexes?

Having followed the link from the comment by Doug Deden:

  It looks like a multi-step process, but there are some promising ideas here: Customize the Behavior of Word Breakers with a Custom Dictionary and Custom WordBreaker for SQL Server Full-text on Stack Overflow.

I've seen that I will need to create a custom dictionary and add the entry 'ASP.Net'. This will ensure normal word breaks aren't applied to this phrase when crawling and/or searching on the full-text index.

Create a custom dictionary (SharePoint Server 2010)


Postgres: error message "does not exist" when dropping existing trigger

The behaviour you've described is correct and documented : http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

If the trigger have been created with double quote and special char, it must be used with the same syntax.


sp_helpdb -- returns nothing, because db is restoring (log shipping target) SQL Server

If you want to see where the database files are you can use the sys.master_files dmv.


Airport Database Design guidance required

You mention every airport has a manager so the manager table should have AirportID as an FK. The Office table should have AirportId and AirlineID as FKs


Multiple IPs are not getting bind in mongodb conf file

had the same issue and the thing is everywhere it's written that you have to bind them like this

bindIp : 127.0.0.1,192.168.0.50

but that doesn't work.

The MongoDB version 3.2.x have the following configuration and its working for me.

    bindIp : [127.0.0.1,192.168.0.50]

    example :

# network interfaces
net:
      port: 27017
      bindIp : [127.0.0.1,10.12.2.1,10.12.65.1]


Extract data for all groups in a single SELECT statement

This works, provided the combination groupid - startdate is unique:

SELECT s1.groupid, s1.id FROM schedule s1, (
  SELECT groupid, MAX(startdate) startdate
  FROM schedule
  WHERE @date >= startdate
  GROUP BY groupid
) s2
WHERE s1.groupid = s2.groupid
AND s1.startdate = s2.startdate


mysql gtid_executed and gtid_purged variables

They are not part of any particular storage engine. GTID variables can be seen by running:

SHOW GLOBAL VARIABLES LIKE 'gtid%';

or

SELECT * FROM information_schema.global_variables
WHERE variable_name LIKE 'gtid%';

When you run RESET MASTER on the DB Server, these values are blanked out.

MySQL Documentation says about gtid_executed

  Issuing RESET MASTER causes the global value (but not the session value) of this variable to be reset to an empty string. GTIDs are not otherwise removed from this set other than when the set is cleared due to RESET MASTER. The set is also cleared if the server is shut down and all binary logs are removed.

MySQL Documentation says about gtid_purged

  Issuing RESET MASTER causes the value of this variable to be reset to an empty string.

When SQL statements are executed, gtid_executed is updated with the GTID Set value.

MySQL Documentation says about gtid_executed

  When the server starts, @@global.gtid_executed is initialized to the union of the following two sets:

  The GTIDs listed in the Previous_gtids_log_event of the newest binary log
  The GTIDs found in every Gtid_log_event in the newest binary log.

  Thereafter, GTIDs are added to the set as transactions are executed.

gtid_purged can only be set when it is blank. Usually, you will set this variable set during the reload of a mysqldump whose source had GTID enabled.


Is there any tool to convert Oracle commands to MySQL?

If you're looking for translating create table statements, there's SQL Fairy


Improving distinct values estimates in Postgres

First, a remark: your query could be written simpler as

SELECT count(DISTINCT assembly_id) FROM assembly_prods;

Also, your statistics query is wrong, because n_distict can also be negative. You should query:

SELECT CASE WHEN s.n_distinct < 0
            THEN - s.n_distinct * t.reltuples
            ELSE s.n_distinct
       END AS n_distinct
FROM pg_class t
   JOIN pg_namespace n ON n.oid = t.relnamespace
   JOIN pg_stats s ON t.relname = s.tablename
                      AND n.nspname = s.schemaname
WHERE s.schemaname = 'public'
  AND s.tablename = 'assembly_prods'
  AND s.attname = 'assembly_id';

For a simple query like that, the statistics should contain a good estimate.

If the estimates are off, try to ANALYZE the table. That will also fix the results for a newly TRUNCATEd table. TRUNCATE does not cause PostgreSQL to autoanalyze the table (there may be room for improvement here).

If that improves the results, see that the table is analyzed more often by configuring

ALTER TABLE assembly_prods SET (autovacuum_analyze_scale_factor = 0.05);

It is also possible to set autovacuum_analyze_scale_factor to 0 and raise autovacuum_analyze_threshold to the daily change rate for the table.

If ANALYZE alone does not improve the estimate, increase the size of the sample:

ALTER TABLE assembly_prods ALTER assembly_id SET STATISTICS 1000;

A new ANALYZE should now produce better estimates.

Getting good n_distinct estimates for more complicated queries becomes increasingly more difficult. Sometimes extended statistics will improve the estimate considerably.

As far as I know, PostgreSQL v12 does not bring any improvements in this area.


This should be easy with a NOT EXISTS subquery. Assuming the tables are as you show them:

selact a.*
from a
where "Current Stock" = 0
  and not exists
      ( select *
        from b
        where b.id = a.id
          and "Current Stock" > 0
      ) ;


Is SET INTEGRITY of any use after IMPORT statements, or just after LOAD statements?

Short answer: You are right in that you cannot specify an exception table for the IMPORT command. Also, there is no need to run the SET INTEGRITY statement if you use IMPORT.

Here is why:

The biggest reason for SET INTEGRITY is based on how LOAD and IMPORT work. IMPORT uses normal INSERT statements to get the data into the tables. Doing this engages the database transaction logs, triggers, and referential integrity. If a row is rejected by referential integrity, you will know about it.

LOAD bypasses referential integrity, triggers, and database transaction logs, and puts the data directly into the table (though it cannot bypass unique constraints and identity constraints). The main reason why you want an exception table is to catch the data that doesn't load into the table (perhaps it was the wrong data type etc.)

Because you bypass referential integrity, you MUST do a SET INTEGRITY afterwards to make the table aware of the data it now contains to see if anything does violate referential integrity (otherwise tables are put into a CHECK PENDING state until you do so). (It is also a good idea to run a RUNSTATS, REORG, FLUSH PACKAGE CACHE DYNAMIC, and REBIND afterwards too to make sure that indexes and the optimizer know about the new data. Actually, it is a good idea to do that anyway if you do a mass IMPORT...but that aside...).

Note that you can use the MESSAGES option with both LOAD and IMPORT to capture errors that happen so you know how to deal with them (usually after the fact though). Note that if you use both MESSAGES for a message file and FOR EXCEPTION <table name> for an exception table, any rows in error that get put in the exception table don't get listed in the message file.

Here is a good comparison of LOAD vs IMPORT straight from IBM DB2 documentation (version 9.7)


How to remove xmlns from child elements with FOR XML

Vote on this connect item for a change of behavior.

You can use for xml explicit to build your XML instead.

Something like this:

;with DummyTable(id) as
(
  select 1 union all
  select 2
)

select 1 as tag,
       0 as parent,
       'http://schemas.xmlsoap.org/soap/envelope/' as [soapenv:Envelope!1!xmlns:soapenv],
       'http://foobar' as [soapenv:Envelope!1!xmlns:pas],
        null as [pas:Child!2!pas:ID]
union all
select 2,
       1,
       null,
       null,
       id
from DummyTable
for xml explicit

Result:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pas="http://foobar">
  <pas:Child pas:ID="1" />
  <pas:Child pas:ID="2" />
</soapenv:Envelope>


Looking for a query that eliminates near-repeats

My sense is that you're doing something wrong and need better tooling like PostGIS and PgRouting just from the problem you're describing but perhaps something like this will work,

SELECT DISTINCT
  greatest(name1, name2),
  least(name1, name2),
  greatest(x1,x2),
  least(x1,x2)
FROM tbl;

If you're using PostGIS then you can use spatial equality,

SELECT max(coalesce(t1.name,t2.name))
FROM table AS t1
JOIN table AS t2
  ON ST_Equals(t1.line,t2.line)
GROUP BY ST_Equals(t1.line,t2.line);

If you're using PgRouting none of this matters because the edges become the same anyway. Edges are bidirectional.


Programmatically Check Dynamic File Name For Invalid Characters

Just run it through a series of REPLACE statements to get rid of any invalid characters:

SET @filename = REPLACE(@filename, '\','');
SET @filename = REPLACE(@filename, '/','');
SET @filename = REPLACE(@filename, ':','');
SET @filename = REPLACE(@filename, '"','');
SET @filename = REPLACE(@filename, '*','');
SET @filename = REPLACE(@filename, '?','');
SET @filename = REPLACE(@filename, '<','');
SET @filename = REPLACE(@filename, '>','');
SET @filename = REPLACE(@filename, '|','');

If it doesn't contain the character, it'll leave it unchanged. If it finds it, it'll remove it.


Migrated data with Special Characters Counted as 2 Characters in DB2 Linux

Every character needs 1 byte in a single-byte database, but it may need from 1 to 4 bytes in a UTF-8 database.
When you specify VARCHAR(N) for a column, N is defined in string units.
OCTETS means bytes, and CODEUNITS32 means characters.
In your case N probably is OCTETS, and since Ñ character requires 2 bytes (OCTETS), then whole string length in the UTF-8 database is 7.
Character strings


getting the total of amount of money of each user between two dates even if no record is found

Because you want to see every single user, table guest_t should be the main table. You can then LEFT JOIN shopping_t to it for existing shopping records.

This query use the period from 01-01-2016 to 01-04-2016. It seems to be what has been used for your desired output. I remove the calendar query because it does not seem required in your desired output.

Query:

SELECT gt.name, gt.id, coalesce(sum(st.money), 0)
FROM guest_t AS gt
LEFT JOIN shopping_t AS st
    ON gt.id = st.guest_id
        AND st.shopping_date >= '2016-01-01'
        AND st.shopping_date < '2016-04-01'
GROUP By gt.id, gt.name;

Coalesce replace NULL by 0.

Output is similar to your desired output:

   name | id | coalesce(sum(st.money), 0)
 george |  0 |                     220000
   Lisa |  1 |                          0
   Anne |  2 |                      10000
   Mike |  3 |                          0

If you want data for the past 3 months starting NOW(), the calendar subquery is not needed. You only need to check the boundaries (NOW and NOW - 3 months) in the ON clause:

SELECT gt.name, gt.id, coalesce(sum(st.money), 0)
FROM guest_t AS gt
LEFT JOIN shopping_t AS st
    ON gt.id = st.guest_id
        AND st.shopping_date >= (CURDATE () - INTERVAL 3 MONTH)
        AND st.shopping_date < CURDATE ()
GROUP By gt.id, gt.name;

This query gives the same output.

Both query are in this SQL Fiddle.

Your query with dates in the WHERE clause didn't work because a LEFT JOIN returns NULL for rows with no match in the shopping table. NULL cannot be compared to the dates and the result of the condition is false.  Therefore the whole row (guest+shooping) is discarded by the WHERE clause:

| id |   name |       city | address |       tel | point | etc | guest_id |  money |              shopping_date
|  0 | george | Summerwind |         | 042968084 |  -100 |     |        0 | 150000 |    March, 02 2016 00:00:00
|  0 | george | Summerwind |         | 042968084 |  -100 |     |        0 |  50000 |  January, 03 2016 00:00:00
|  0 | george | Summerwind |         | 042968084 |  -100 |     |        0 |  20000 | February, 16 2016 00:00:00
|  2 |   Anne |   Windhill |         | 042968084 |  -100 |     |        2 |   9000 |  January, 02 2016 00:00:00
|  2 |   Anne |   Windhill |         | 042968084 |  -100 |     |        2 |   1000 |  January, 03 2016 00:00:00
|  1 |   Lisa |    Laplace |         | 042968084 |     0 |     |   (null) | (null) |                     (null)
|  3 |   Mike |   Fairview |         | 042168084 |     0 |     |   (null) | (null) |                     (null)

From this output, the last 2 row are discarded.

When the date condition is move to the ON clause, shopping_t is checked before the LEFT JOIN. If the condition is not true, they are not used in the JOIN and replaced by NULL. However because a LEFT JOIN guest_t data stay in place whether shopping_t returns nothing (NULL).

If you want to see each days whether there is data or not, you have to CROSS JOIN the calendar subquery with guest_t and then use a LEFT JOIN:

select guest_id, t.days
    coalesce(sum(money), 0)
from (
    SELECT ADDDATE...
) as t
CROSS JOIN guest_t gt
left join shopping_t
    on gt.id = st.guest_id
        AND shopping_date = t.days
group by guest_id, t.days;

I remove the final WHERE clause with is similar to ON clause.


Redefine Transaction log size restricion fails

If you look at Maximum Capacity Specifications for SQL Server, you will see that the maximum size for transaction log is 2 TB:

SQL Server Database | Maximum sizes/numbers    | Maximum sizes/numbers
Engine object       | SQL Server (32-bit)      | SQL Server (64-bit)
 ...                | ...                      | ...
File size (log)     | 2 terabytes              | 2 terabytes

Then once converted to MB:

2 TB <=> 2.048 GB <=> 2.097.152 MB

According to ALTER DATABASE File and Filegroup Options, Unlimited means that the value is set to the maximum file size:

  MAXSIZE { max_size| UNLIMITED }
  ...
  UNLIMITED
  Specifies that the file grows until the disk is full. In SQL Server, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.

Either of these query should set it to unlimited:

USE [master]
GO
ALTER DATABASE [mydb] MODIFY FILE ( NAME = N'mydb_log', MAXSIZE = 2097152MB)
GO
ALTER DATABASE [mydb] MODIFY FILE ( NAME = N'mydb_log', MAXSIZE = UNLIMITED)

Using this query once set to UNLIMITED:

SELECT max_size, max_size/1024*8 FROM sys.database_files

Output for row 2 (LOG) should be:

maxsize     | maxsize in MB
268435456   | 2097152

268435456  is the number of 8 KB pages
2097152 is the same number converted to MB (<=> 2 TB)

Although maximum size cannot be set to a negative value, sys.database_files may show -1 for maximum size:

  Databases that are upgraded with an unlimited log file size will report -1 for the maximum size of the log file.

The maximum value for MAXSIZE using ALTER DATABASE ... MAXSIZE ... is 2147483647 pages (<=> 16777208 MB => 16 TB) but with a LOG file and a size above 2097152 MB, it fails with this error:

  Msg 5150, Level 16, State 2, Line 1
  The size of a single log file must not be greater than 2 TB.

Besides 2 TB is also probably an old limitation due to MBR:

  Master boot record
  ...
  Since block addresses and sizes are stored in the partition table of an MBR using 32 bits, the maximum size as well as the highest start address of a partition using drives that have 512-byte sectors (actual or emulated) cannot exceed 2 TiB−512 bytes (2,199,023,255,040 bytes or 4,294,967,295 (232−1) sectors × 512 (29) bytes per sector). Alleviating this capacity limitation was one of the prime motivations for the development of the GPT.
  Source: Wikipedia


Foreign keys can indeed speed up queries if they are trusted. If the engine knows that every value in ParentID in the Child table refers to a valid PK in Parents then it won't even look at that table to check.

However, relational constraints are a good idea in general regardless. One philosophy behind constraints in the database is to use them as a last-resort stop for bad data. Foreign keys are a good example of this.

If you depend entirely on your ORM for relation management, what happens if you change to a different framework? You still need to keep your existing data. What happens if for some reason you need to make changes to your relational model in the ORM and orphaned child records get put in with no parent?

Short answer - yes, you should still use keys and constraints in the database.


Compare the same column on different dates

First cut query, using a self full outer join:

SELECT
  coalesce(product_today.product_id, product_yesterday.product_id) AS product_id,
  sum(CASE WHEN product_yesterday.keyword_id IS NULL THEN 1 END) AS new_today,
  sum(CASE WHEN product_today.keyword_id IS NULL THEN 1 END) AS deleted_today,
  sum(CASE WHEN product_yesterday.level > product_today.level THEN 1 END) AS decreased,
  sum(CASE WHEN product_yesterday.level < product_today.level THEN 1 END) AS increased,
  sum(CASE WHEN product_yesterday.level = product_today.level THEN 1 END) AS not_changed
FROM (
  SELECT *
  FROM product_report
  WHERE created_at = current_date)
AS product_today
FULL OUTER JOIN (
  SELECT *
  FROM product_report
  WHERE created_at = current_date - INTERVAL '1' DAY
) AS product_yesterday
ON (product_today.project_id = product_yesterday.project_id
    AND product_today.keyword_id = product_yesterday.keyword_id)
GROUP BY project_id;

This query will handle products that're deleted or newly added. If you don't care about that you can simplify it to an inner join, so it only shows products that were neither deleted nor newly added:

SELECT
  product_today.product_id AS product_id,
  sum(CASE WHEN product_yesterday.level > product_today.level THEN 1 END) AS decreased,
  sum(CASE WHEN product_yesterday.level < product_today.level THEN 1 END) AS increased,
  sum(CASE WHEN product_yesterday.level = product_today.level THEN 1 END) AS not_changed
FROM product_report product_today
INNER JOIN product_report product_yesterday
ON (product_today.project_id = product_yesterday.project_id
    AND product_today.keyword_id = product_yesterday.keyword_id)
WHERE product_today.created_at = current_date
  AND product_yesterday.created_at = current_date - INTERVAL '1' DAY
GROUP BY project_id;

If you don't care about "yesterday", but rather "the previous entry", you can probably use a window function in a subquery, but it'll be fiddly to write without sample data and schema.


Row level security with a single DB user and connection pooling

I have a similar setup on a new project.

The issue here is that you have a connection pool that uses a single login. The application that holds that connection pool will need to set the user correctly to get the required row level permissions. If your application is incorrectly configured / coded, then the permissions can be subverted.

It seems to me that row level permissions are only really useful when you have different users/roles with different logins reading from tables directly. In that case there is no where user = xxxx style clause that can be subverted. In this situation they make different connections.


MSSQL - GRANT EXECUTE permission inside stored procedure

Try GRANT SELECT on the view and then GRANT EXECUTE on the procedure to the user.
see this

https://msdn.microsoft.com/en-us/library/ms188371.aspx


Is there a way to set bind_address from command line in mysql 5.5 without restarting?

In a word, no. Why?

MySQL Documentation on bind_address says right in the beginning

Command-Line Format --bind-address=addr
System Variable (>= 5.6.1)  Name    bind_address
Variable Scope  Global
Dynamic Variable    No
Permitted Values (<= 5.6.5) Type    string
Default 0.0.0.0
Permitted Values (>= 5.6.6) Type    string
Default *

It's a global variable in MySQL 5.6, but it is not dynamic.

Since you are using MySQL 5.5, it is only a startup option.

Therefore, you must bite the bullet and restart mysqld.


Safe way to truncate SQL Server Error Log

You can cycle the error log by calling sp_cycle_errorlog and then that will close the current error log and cycle the log extensions.  Basically, it'll create a new error log file that SQL Server will be hitting.  Then the archived error log(s) can be treated accordingly (delete/move with caution).  This will not technically "truncate" the log, it'll just roll it over and you can handle the old logs as you so please, like any other file system file.

When you do this, you should see a new log file with an entry that resembles the following:

  The error log has been reinitialized. See the previous log for older entries.

BOL reference on sp_cycle_errorlog


MYSQL: Stored function to replace category ids with names

Does this work? I wrote this up in a hurry on MariaDB so may not be 100% correct, but hoping it should work on MySQL 5.5 as well, or at least only require minor tweaks. It seems to work on your test data.

DELIMITER //
CREATE FUNCTION decode_path (
    `path_input` MEDIUMTEXT
)
RETURNS mediumtext CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
  BEGIN
    DECLARE cnt INT UNSIGNED DEFAULT 2;
    DECLARE cat_id INT UNSIGNED;
    DECLARE cat_name VARCHAR(200) DEFAULT '';
    DECLARE decoded_path MEDIUMTEXT;
    SET decoded_path = '';

    WHILE cnt < LENGTH(path_input) - LENGTH(REPLACE(path_input, '/', '')) + 2 DO
      SET cat_id = SUBSTRING_INDEX(SUBSTRING_INDEX(path_input,'/',cnt), '/', -1);
      SELECT `name` INTO cat_name FROM category WHERE id = cat_id;
      SET decoded_path := CONCAT(decoded_path, '/', cat_name);
      SET cnt := cnt + 1;
    END WHILE;
    RETURN decoded_path;
  END
//
DELIMITER ;

Using this, I get:

SELECT path, `name`, decode_path(path) FROM category;
+--------------+-------------------+---------------------------------------------+
| path         | name              | decode_path(path)                           |
+--------------+-------------------+---------------------------------------------+
| /1           | A                 | /A                                          |
| /1/2         | B                 | /A/B                                        |
| /1/2/3       | C                 | /A/B/C                                      |
| /4           | D                 | /D                                          |
| /4/5         | E                 | /D/E                                        |
| /4/5/6       | F                 | /D/E/F                                      |
| /7           | G                 | /G                                          |
| /7/8         | H                 | /G/H                                        |
| /7/8/9       | I                 | /G/H/I                                      |
| /426         | Computers         | /Computers                                  |
| /426/427     | Other accessories | /Computers/Other accessories                |
| /426/427/428 | Laser printers    | /Computers/Other accessories/Laser printers |
+--------------+-------------------+---------------------------------------------+

So to store the result string from the function into a new column named path_long:

UPDATE category SET path_long = decode_path(path);

Tested with MySQL 5.5 on db-fiddle.com here.


How to know if backup file is available before trying to restore

I've found an answer on SO that could help you:

Check for file exists or not in sql server?

Using this function:

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

You could check if the backup file exists before to restore it.


Does postgres single-user mode use postgresql.conf settings?

Yes, postgresql.conf and postgresql.auto.conf will be read and used in single-user mode as usual.

Parameters you specify with -c options will override the settings from the configuration files.

You are doing the right thing.


You can use Extended Events™ to accomplish this. The error_reported event allows you to log a row to a file every time a certain error occurs. You can add additional information to what is logged such as username and client_hostname. Here is some example T-SQL to get you started that filters on only error code 262:

CREATE EVENT SESSION [error_262_to_file] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.username)
    WHERE ([error_number]=(262)))
ADD TARGET package0.event_file(SET filename=N'C:\XE\error_262_to_file.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Suppose one of my most troublesome users logs in with his sean_gallardy login. I don't trust this user so I gave him very limited permissions. If he tries to view a query plan he gets the following error message in SSMS:

  Msg 262, Level 14, State 4, Line 1

  SHOWPLAN permission denied in database 'master'.

Here is what is logged to the extended event file in response:


Reset the sequence of a serial PK with dynamic SQL

General advice

You mentioned it yourself, you just started using Postgres. Yet, you're tackling extremely advanced tasks right away, juggling system catalogs and operating with advanced dynamic SQL to automate things.

While your objectives seem reasonable, you still need to start at the basics. There is just too much to explain here. Start with (relevant parts of) the excellent manual. I provided a couple of deep links further down.

Answer

To get name(s) (and data type(s)) of columns involved in the primary key, rather use this simpler query:

SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM   pg_index i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
                     AND a.attnum = ANY(i.indkey)
WHERE  i.indrelid = 'tbl'::regclass
AND    i.indisprimary;

I updated the Postgres Wiki page where your original query seems to originate from.

However, this can return multiple rows, while you only assign a single value. Assuming you have established that we are dealing with a serial type (single column) primary key. Else use similar techniques as laid out in my previous answer to make sure.

Then use the dedicated system information function pg_get_serial_sequence() to determine the name of the used sequence, like demonstrated in my previous answer.
Per documentation:

  get name of the sequence that a serial, smallserial or bigserial column uses

DO
$do$
BEGIN
   EXECUTE (
   SELECT format($$SELECT setval('%s'::regclass, max(%I)) FROM %s$$
                 , pg_get_serial_sequence(a.attrelid::regclass::text, a.attname)
                 , a.attname
                 , a.attrelid::regclass
                )
   FROM   pg_index i
   JOIN   pg_attribute a ON a.attrelid = i.indrelid
                        AND a.attnum = i.indkey[0]
   WHERE  i.indrelid = 'tbl'::regclass
   AND    i.indisprimary
   );
END
$do$ LANGUAGE plpgsql;

This builds and executes a query of the form:

SELECT setval('tbl_tbl_id_seq'::regclass, max(tbl_id)) FROM tbl;

Explain / Advise

This is advanced stuff and not really suitable for beginners. Messing with system catalogs can go south quickly if you don't know exactly what you are doing.
Stick to legal all-lower case identifiers in Postgres and plpgsql to make your life easier. But never rely on it in dynamic SQL, where you also need to defend against SQL injection at all times.
Making heavy use of format() to build the query string conveniently & safely.
Inside a plpgsql function you cannot call SELECT without assigning the result. You would use PERFORM instead. Details in the manual.
I removed that completely, since I reduced everything to a single EXECUTE.
Since the whole operation only makes sense for a single-column  primary key, I simplified the JOIN condition to a.attnum = i.indkey[0]
Note that pg_index.indkey has the special (internal) type int2vector. Unlike Postgres arrays its index starts with 0, not 1.
Make it a habbit to use dollar-quotes with a token around plpgsql code (including DO statements). This allows to nest simple dollar-quotes like I do in my example. Details:

Insert varchar with single quotes in PostgreSQL

You only need a single SELECT here:

SELECT setval('tbl_tbl_id_seq'::regclass, max(tbl_id)) FROM tbl;

instead of:

SELECT setval('tbl_tbl_id_seq'::regclass, (SELECT max(tbl_id) FROM tbl));

SQL Fiddle demonstrating a few things.


Transfer ERD between Databases on SQL Server

It sounds as if you are using the Database Diagrams feature from within SSMS. I found this tool out at CodeProject: http://www.codeproject.com/Articles/15080/Script-SQL-Server-2005-diagrams-to-a-file

I cannot speak to how well it would work for you. I would suggest you use a different tool, such as perhaps Visio, in order to create your ERDs so that they are more portable.


Cannot access any database in SQL Server 2008 R2 Express after moving workstation from domain to workgroup

I have been in this situation before. If you do not have the sa password and you are not a member of the sysadmin group then you only have one option remaining to you which is to start SQL Server in maintenance mode. This will allow you to reset the sa password and to add yourself to the sysadmin server role.

Here is a link that I found useful when I had to do this as it describes setp-by-step what you need to do. Good luck!

http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx


Backup of database X on SQL Server 2008 on server A - restore to SQL Server 2012 database Y on server B

Don't create the database on server B before restoring. Just restore it. It doesn't have to exist first in order to do that. This should work whether the database already exists or not (so perhaps you should consider using a better name than 'test'), just open a new query window:

USE master;
GO
RESTORE DATABASE test
  FROM DISK = 'E:\backup path\backup.bak'
  WITH REPLACE, RECOVERY,
    MOVE 'data_file_name' TO 'D:\data file path\test.mdf',
    MOVE 'log_file_name'  TO 'L:\log file path\test.ldf';

But I have no idea where you are getting a message about "busy" - are you restoring using the UI? Stop doing that - the UI is notoriously bad for assisting with tasks like backup and restore. And make sure you don't have any query windows etc. with their context set to the existing test database. To kick people out so you can restore over it, you can use:

USE master;
GO
ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

To get the file names to place in the MOVE arguments, run this:

RESTORE FILELISTONLY FROM DISK = 'E:\backup path\backup.bak';


How to backup to local drive and network drive simultaneously?

As the documentation shows, MIRROR TO is only available in the Enterprise editions of SQL Server 2005+.

If your end goal is only to have a redundant backup for safekeeping in a different location from the local disk, and they don't have to be the same backup, then you could just do them in sequence in a single batch.

BACKUP DATABASE [MGEC_STAGE] TO DISK = N'XXXX.bak' WITH INIT;
BACKUP DATABASE [MGEC_STAGE] TO DISK = N'Network path \XXX.bak' WITH INIT;

If the backups have to be identical, then you will just have to perform the backup to the local drive and copy it, using any of (among other options)

xp_cmdshell and the xcopy/copy command
Windows Task Scheduler sqlcmd command line and xcopy/copy, assuming you (i) are performing the backup using a schedule (e.g. SQL Agent) and (ii) know how long it will take, timing the copy to occur soon after
SSIS package, along the lines of the Windows Task Scheduler


alter table alter column causes huge log and data file growth

minimum storage for a VARCHAR is 4 bytes, I think, so you did change the size of the column from 1 byte to at least 4 bytes, causing (as you have observed) large amounts of data movement as the column grows and it shuffles other things around it.

I would recommend Aaron's approach as a faster method to achieve the same end.  The downside to it is that column order is not preserved....  but since we should always (lol) use named columns for our selects and inserts, the order of storage shouldn't matter.   (may need to refresh frameworks....)

You are correct.  DDL is a logged transaction and thus the behavior you observed in your log file / AG health is to be expected.


Whats the term in Oracle, when I'm speaking about SCHEMAS on SQL Server

You can't map everything on a one to one relationship when comparing Oracle and SQL Server. There are some similarities between both products and then there are concepts that could pass as two concepts within the other product. Let's start out with the basic stuff (Columns, Tables, ...) and continue up until we reach the instance.

   Oracle Database | SQL Server
  -----------------+-----------------
   Column          | Column
   Table           | Table
*) Schema          | Schema
*) User            | Schema
*) User            | Database User
*) User            | SQL Login
   Data Dictionary | INFORMATION_SCHEMA
   Tablespace      | Filegroup
   Oracle Database | SQL Server Database
   Oracle Instance | SQL Server Instance

Reference: [Microsoft SQL Server 2008 - Practical SQL Server 2008 for Oracle Professionals][] ()

Database and Instances

An Oracle instance will normally contain one database.

The exception being when an Oracle instance has been configured to run a container database (CDB), which will contain multiple pluggable databases (PDBs).

Reference: 17 Introduction to the Multitenant Architecture (Oracle | Docs | Database Concepts)

A SQL Server instance normally contains multiple databases.

A newly created SQL Server instance will start off with the basic four system databases (master, model, msdb, tempdb).

Schemas

The database at the Oracle level is the container for multiple schemas. The same is valid for the SQL Server. However, there is a difference *) between the Oracle schema and the SQL Server schema.

... in SQL Server

In SQL Server you can create a schema like this:

USE [StackExchange]
GO

/****** Object:  Schema [DBT]    Script Date: 12.12.2018 09:42:26 ******/
CREATE SCHEMA [DBT] AUTHORIZATION TheOwner
GO

Reference: CREATE SCHEMA (Transact-SQL) (Microsoft | Docs | SQL)

Any newly created object (table, view) can be linked to a specific schema, otherwise it is linked/stored inside the default schema dbo, unless the database user creating the new object has a differente default schema.

  they told me that they're going to use something like DBO.REPORT_XXX to name tables

In this case dbo. is the default schema for any object created in SQL Server. The dbo schema doesn't have to be explicitly created as it exists as soon as you create a database. You could create your own schemas and create tables within that schema. Here an example using the previously crated DBT schema:

USE [StackExchange]
GO
----------- schema.tablename -------
CREATE TABLE [DBT].[Application](
    [ID] [int] NOT NULL,
    [APPL_NAME] [nchar](20) NOT NULL
) ON [PRIMARY]

... in Oracle Database

In Oracle the schema is not a stand-alone concept/object. An Oracle schema is linked to an Oracle user. When you create an Oracle user a schema with the same name is created for that user. The CREATE statement for an Oracle user doesn't give a hint that the schema will be created:

CREATE USER ORACLEUSER IDENTIFIED BY YourPasswordHere1084
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP
 PROFILE "DEFAULT"
 QUOTA UNLIMITED ON USERS;

When you create objects (tables, views) you create them inside a specific schema. There is no default schema.

CREATE TABLE ORACLEUSER.ORACLUSERTABLE
(
  Identity  INTEGER
);

Summary

So in summary the Oracle user has a schema that has the same name as the user. The user is synonymous with the schema. Hence the *) pretty weird comparison in the table at the top.

The Oracle user (password/permissions) can be compared to the combination of SQL Server login (password) and the SQL Server database user (permissions).
The Oracle schema can be compared to the SQL Server schema.
The Oracle schema cannot exist without an Oracle user.
The SQL Server schema can exist without a corresponding user, but requires to be linked to a database user.
A SQL Server table can be created in the default schema dbo. (e.g. dbo.UserTable)
An Oracle table has to be created in a specified schema (e.g. ORACLEUSER.ORACLUESRTABLE)

Reference: Difference between database vs user vs schema (DBA Stack Exchange)

Answering Your Question

  but, can I achieve something like SQL Server schemas on Oracle?

Yes, you can. You create an Oracle user with password and a schema is automatically created. And yes, you could create a dbo user/schema in Oracle.


Is it possible to add index to mysql with multiple column to optimize order by performance?

You need an index on (c2, c1) for this query, not an index on (c1, c2).

Of course it would be best if there was an index (c2 DESC, c1 DESC) for the first and an index (c2 DESC, c1 ASC) for the second query. Unfortunately MySQL cannot create such indexes. The ASC and DESC are ignored.

Which means your two indexes:

CREATE INDEX index_1 ON t1 (c1 DESC, c2 DESC);
CREATE INDEX index_2 ON t1 (c1 ASC, c2 DESC);

are actually identical and same as if you had defined them as:

 CREATE INDEX c1_c2_index ON t1 (c1 ASC, c2 ASC);

If the performance - with the (c2, c1) index - is not good enough, you have a few options:

Since the columns are of integer type, add two more columns and store the negative values. You'll need triggers to polulate those columns.

.

CREATE TABLE
( c1 INT
, c2 INT
, c1_neg INT
, c2_neg INT
);

Then you can an indexes on (c2_neg, c1) and (c2_neg, c1_neg)

If you can move to MariaDB, you can use VIRTUAL PERSISENT columns and appropriate indexes for a similar solution (a bit better as you won't have to add triggers, the columns will be populated automatically).


What could cause problematic queries to disappear from QueryStore?

QueryStore has a variety of settings for configuration but you need to be aware that there is a cleanup job that removes things that are too old or exceed one of the other configuration thresholds - read more about them here:-
https://www.sqlskills.com/blogs/erin/query-store-settings/


When bloom was introduced in 9.6, parallel query had just been introduced and is was turned off by default.  Bloom appeared to be better than a non-parallel sequential scan in the example given.  But when you can do a parallel seq scan, it appears better than using a bloom index does.  It is not actually better as can be verified by turning off parallel query with set  max_parallel_workers_per_gather TO 0 and looking at the actual executions speeds, but the planner thinks the parallel seq scan will be better.  It looks like maybe the cost estimation part of bloom could use some work.

The example code was not updated for when parallel query was turned on by default, in v10, so it no longer works as advertised.

Note that your example never achieved any index usage at all, so you can't really draw any conclusions about which index is better for that scenario.


Immediate refresh materialized views (MQT) with OUTER JOIN under DB2

Unfortunately, as the error description for SQL20058N reason code 10 states, you can't use an outer join with a refresh immediate MQT:

Explanation

...
10
The REFRESH IMMEDIATE option was specified and the FROM clause referenced more than one table.

User Response

10
Create the materialized query table as REFRESH DEFERRED, or use an inner join without the explicit INNER JOIN syntax.

However, if the MQT definition you supply is really this simple (i.e., pre-computing the join), I wonder why you want to create an MQT in the first place?  As you said, performance of the query is good, so why wouldn't you just retain the query as written in the application?
Alternately, you could encapsulate the join logic in a view.

Although I would generally advise against this, if there is some serious performance issue with the join that can't be resolved with proper indexing and RUNSTATS, you could replicate this immediate update functionality through triggers on the PHONEUSER and PHONECALL tables that insert records into your CALLLIST table:

create trigger trg_PhoneUser
    after insert on PHONEUSER
    referencing new as n
    for each row
        insert into PHONECALL (userid) values (n.userid);

create trigger trg_PhoneCall
    after insert on PHONECALL
    referencing new as n
    for each row
    begin
        delete from PHONECALL where userid = n.userid and hour is null;--
        insert into PHONECALL values (n.userid, n.callid, n.hour);--
    end;

The first trigger is necessary to handle the outer join (i.e. where a user hasn't made any calls yet).  The second trigger inserts the calls, and also deletes the records inserted by the first trigger (as they are no longer necessary once a record in PHONECALL exists).  You would need to decide how you want to handle deletes in either table (either through triggers or foreign keys with cascading deletes).

Again, I want to stress that this is a possible solution, but I would only use it as a last resort.  Databases are designed to perform joins efficiently, but this might require some proper design and tuning.  The table structures above should absolutely not require this kind of solution, but I'm working on the assumption that this is not your actual schema.  I am only describing this solution because I can imagine a few edge cases where something like this solution might be necessary.


I would say that some or all of your application data is likely stored in the USERS tablespace. Don't do anything rash.

IIRC databases created by the dbca utility have USERS set as the default tablespace. This means that any new users created will store their tables etc. in USERS by default.

select * from dba_segments where tablespace_name = 'USERS' will give you a starting-point for seeing what is in the tablespace. (It could be a long list.)

select * from dba_users where default_tablespace = 'USERS' will show you the schema owners which will store their data in USERS by default.

I suggest that you don't see it as a problem to be solved, but accept that USERS is where the data is stored, and plan for controlled future growth of the tablespace. I have seen a couple of applications which had been installed like this.

HTH


PostgreSQL keeps asking for user password with "trust" authentication enabled

By checking the logs, it got apparent that PostgreSQL was matching the ::1/128 IPv6 address, which had scram-sha-256 as its authentication method. adding a line like
host    all             john            ::1/128            trust

fixed the problem.
Also, there is no need for restarting the DBMS after changes to pg_hba.conf.


How to SELECT cells from mysql for a HTML table?

MySql has no XML data type so it's hardly can help ( more help then app layer code) to generate HTML markup.
It can help to create ordered stream of cells. Kind of

select r.row_number, h.column_number, c.content
from table_cells c
join table_rows r on c.row_id = r.row_d
join table_cols h on c.column_id = h.column_id
order by r.row_number, h.column_number

Or may be to convet sparse array of cells to regular one as directed by table_cols and table_rows, if that is the case.


Memory usage grows on SQL Server Analysis Services 2012 until cubes fail to process

From a comment by Robin Blondon:

I tested with with SP1 through SP4 and it looks like SP4 is the only one that has this issue. I submitted a bug report On Azure Feedback.

The suggested advice would be to upgrade to a newer version of SQL Server Analysis Services, since SQL Server 2012 is out of mainstream support:


can I do a restore controlfile from autobackup out FAST RECOVERY AREA?

Why not?

From Database Backup and Recovery Reference 12c:

  The default location for the autobackup on disk is the fast recovery
  area (if configured) or a platform-specific location (if not
  configured). RMAN automatically backs up the current control file
  using the default format of %F. You can change the location and file
  name format with the CONFIGURE CONTROLFILE AUTOBACKUP FORMAT and SET
  CONTROLFILE AUTOBACKUP FORMAT commands.


Okcupid.com (can find matches without accessing a database!?)

They didn't explicitly mention this, so I am only guessing here, but they are using some search index such as Lucene to perform the actual searches.  They'll have a persistent database of some sort but their search index is periodically built from that data set.


Execution plan index suggestion - difference between similar queries

Some context

The first things to observe is that your clustered index does not help with the lookup of the tid column, because t is the leading column in the index.

If you flipped the order of t and tid in the key, I would expect the index hint to go away and the query to run faster without adding any new indexes.

Specific Answer

The most likely reason that the second query plan does not suggest an index is that the new filter on tid picks more than around 30% of all the values in the table. When that is the case, SQL Server will generally prefer to table scan instead of seeking the index (because this is the better strategy). Hence, suggesting an index is no longer the right thing to do. Selecting two values instead of one may be exactly the tipping point of this 30% constant in the optimiser

To explore this effect, you can force the index hint to return by doing this:

SELECT *
FROM [dbo].[Values] WITH (FORCESEEK)
WHERE  [tid] = 1 or [tid] = 2

.. But please don't do this in production, only to explore the effects.

Why the include?

The include is there because if it wasn't, the execution would have to do this:

Find the value you are looking for in the newly created index on
tid
Go to the main index (the primary key) to pick up the values
of column v and t

The INCLUDE makes sure all the columns are contained in the index so the second step is not needed. Incidentally, this is one of the reasons why you should eschew SELECT *.


need to join Vertical row as per values in Horizontal rows

If I get it right

SELECT t1.`userId`, j.`job`
FROM `table2` j
JOIN `table1` t1 ON t1.`state` IN (j.`to`, j.`from`)
GROUP BY t1.`userId`, j.`job`
HAVING count(t1.'state') = 2


Short version: no.

Active Data Guard is an optional feature that allows you to open the standby database in read-only mode while applying logs from the primary. If you need the standby database only in case of disaster, then you don't need to buy this option.


Retrieve list of matched words in PostgreSQL

What about using the regexp_split_to_table function?

SELECT regexp_split_to_table(ts_headline('the big red ball'::text , 'big & ball'),' ');
 regexp_split_to_table
-----------------------
 the
 big
 red
 ball
(4 rows)


Google search console data - find the newly appeared keyword for the month

I figured it out after a brief study, here it is,

SELECT * FROM sconsole WHERE sconsole.query IN
                (SELECT  tbl.query FROM
                  (  SELECT  s1.* FROM sconsole s1 WHERE s1.month = '{$params['m1']}' UNION ALL SELECT  s2.* FROM sconsole s2 WHERE s2.month = '{$params['m2']}'
                  ) AS tbl  GROUP BY  tbl.query HAVING COUNT(tbl.query) = 1)

works like a charm.


Backups script completes, but doesn't back up all dbs

The problem may actually be that in using your cursor, you're not encountering errors, but databases are simply getting skipped.  I have had situations in the past where a default cursor will skip records.  I've never been able to determine the cause, but I have found some ways to handle it better in the code.

One is to put some better definition around your cursor declaration.  Since this kind of cursor will always iterate forward, you should declare it as such:

DECLARE db_cursor CURSOR STATIC FAST_FORWARD FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'
    AND name != 'tempdb'
ORDER name

I have found using the FAST_FORWARD option is much more reliable for iterating each item in a data set.  Also, it can have other benefits.

Another approach is to build a WHILE... loop structure to handle this.  This does not rely on the cursor engine in SQL Server to handle your logic and can give you better reliability for getting all your databases(this is incomplete code, you will need to add backup/error handling logic to make it work):

DECLARE @work table
(dbid int,
 backupdate datetime,
 errormsg varchar(max));

DECLARE @currdb int

INSERT INTO @work (dbid)
SELECT
    database_id
FROM
    sys.databases a
WHERE
    state_desc = 'ONLINE'
    AND name != 'tempdb'

WHILE EXISTS (SELECT 1 FROM @work WHERE backupdate IS NULL AND errormsg IS NULL)
BEGIN
    SELECT top 1 @currdb = dbid
    FROM @work
    WHERE backupdate IS NULL AND errormsg IS NULL;

    BEGIN TRY
        --Execute Backup Code, then update @work table
        UPDATE @work
        SET backupdate = GETDATE()
        WHERE dbid = @currdb
    END TRY
    BEGIN CATCH
        --Catch error, put message in @work table
        update @work
        set errormsg=ERROR_MESSAGE()
        where dbid = @currdb;
    END CATCH

END

These are two approaches if you decide to move forward with writing your own scripts for backups.  Overall, I would strongly recommend you check out Ola Hallengren's backup scripts.  They are highly recommended by members of the community, well documented, and thoroughly tested.


This syntax for ALTER TABLE SWITCH requires you to specify a single partition.

ALTER TABLE [ database_name . [schema_name ] . | schema_name. ] source_table_name
{
    ALTER COLUMN column_name
        {
            type_name [ ( precision [ , scale ] ) ]
            [ COLLATE Windows_collation_name ]
            [ NULL | NOT NULL ]
        }
    | ADD { <column_definition> | <column_constraint> FOR column_name} [ ,...n ]
    | DROP { COLUMN column_name | [CONSTRAINT] constraint_name } [ ,...n ]
    | REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_option> ] ]
      }
    | { SPLIT | MERGE } RANGE (boundary_value)
    | SWITCH [ PARTITION source_partition_number
        TO target_table_name [ PARTITION target_partition_number ]
}
[;]

I found several articles that show different ways to automate the switching of all partitions - this is just one example

DECLARE @Partitions TABLE (PartitionId int PRIMARY KEY CLUSTERED);
DECLARE @PartitionId INT;

INSERT @Partitions(PartitionId)
    SELECT
        prv.boundary_id PartitionId
    FROM sys.partition_functions AS pf
    INNER JOIN sys.partition_range_values prv ON prv.function_id=pf.function_id
    WHERE (pf.name=N'PartitionFunctionName');

WHILE EXISTS (SELECT NULL FROM @Partitions)
    BEGIN

        SELECT TOP 1 @PartitionId = PartitionId FROM @Partitions;

        ALTER TABLE MS_PROD SWITCH PARTITION @PartitionId TO MS_Stage PARTITION @PartitionId;

        RAISERROR('Switched PartitionId %d to Stage',0,1,@PartitionId) WITH NOWAIT;

        DELETE @Partitions WHERE PartitionId = @PartitionId;

    END


Estimated hardware requirement for a planned "mostly-write" database

How many connections are there going to be pumping in the data?  Is every insert naturally its own transaction or are they naturally bundled up somehow?

  From what I already know, new messages do not need to be written to the DB instantly, it is ok to collect them in intervals of 1 to 2 seconds and bulk put them into the database

Where are they going to be stored for 1 to 2 seconds? What happens if the system crashes/restarts and those 1 to 2 seconds get forgotten about, is that acceptable?  If a small amount of data loss is acceptable on rare occasions, then simply setting synchronous_commit to off is probably a better solution then coming up with some complicated way to re-arrange small transactions so they are bundled into larger transactions.

All the above is mostly to answer the question of how fast does the fdatasync on your storage need to be.

  the data need to be filtered by time ranges and some additional fields

How wide will the queries time ranges be?  If they are narrow, then you can have an index just on the timestamp.  Then read every row in the time range and apply the additional field filters to each row.  This would make index maintenance pretty efficient, as the rows should be inserted roughly in timestamp order, meaning the index leaf pages that need to be updated will have just recently been used and so still in memory.  If you need more indexes than this, then index maintenance will likely become your bottleneck, especially once the total size of your indexes becomes a substantial fraction of your RAM.

Also, will the time range in those queries be mostly for recent times, or for any time in the last 5 years?

  The data need to be accessible for at least 5 years before it is acceptable to export and remove them from the DB

Do you envision this working by removing data from 5 years + 1 hour ago, every hour?  Or just archive and drop an entire year at a time once a year?  If the latter, then partitioning by year would be a good idea.  But at 3 billion records per year, you might want to partition by month instead.

I think it is hard to do much capacity planning without knowing which indexes will need to exist.

I don't think a large number of CPUs will help you.  Spend the money on fast storage, or maybe RAM, instead.


MySQL Workbench EER Diagram and ENUM data type

ENUM datatype was not supported in MySQL Workbench v5.x. I had the same problem with MWB v5.2. I just downloaded the latest 6.x (v6.1.4) and was able to create an ENUM which now appears in the Datatype dropdown menu of the table editor.

One word of caution regarding ENUMs, as noted in the MWB docs:

  Caution:
  Care must be taken when entering a default value for ENUM columns because a non-numeric default will not be automatically quoted. You must manually add single quote characters for the default value. Note that MySQL Workbench will not prevent you from entering the default value without the single quotation marks. If a non-numeric default value is entered without quotation marks, this will lead to errors. For example, if the model is reverse engineered, the script will contain unquoted default values for ENUM columns and will fail if an attempt is made to run the script on MySQL Server.


Can't understand results of pg_stat_statements view

I quote the manual (at the page you are linking to, just for the appropriate  version 9.1)

  total_time ... Total time spent in the statement, in seconds

67347 calls totaling 0.852 seconds. That's 0.012 ms per call.
Not uncommon for a simple SELECT.

If that cannot explain it, consider this quote from the manual:

  Note that statements are considered the same if they have the same
  text, regardless of the values of any out-of-line parameters used in
  the statement. Using out-of-line parameters will help to group
  statements together and may make the statistics more useful.

We'd have to study your exact statement(s) to assess that.

There is also the function pg_stat_statements_reset() to reset statistics (superusers only). Are you sure it wasn't called?

And there are a number of configuration parameters to regulate what is tracked. Any fancy settings there?


Stored procedure to set value to a column must accept returned values from functions

If it's possible to add an additional parameter to your stored procedure (PropertyValueIsFunction bit), you could test for that condition and use REPLACE to strip out the quotes.  Here's an example:

CREATE PROCEDURE [dbo].[sp_set_etl_status]
    @propertyName VARCHAR(100)
   ,@propertyValue VARCHAR(MAX)
   ,@propertyValueIsFunction bit
AS

DECLARE @query AS VARCHAR(MAX)

SET @query = '
BEGIN
    IF NOT EXISTS ( SELECT * FROM [internal].[etl_status]
                        WHERE [property_name] =''' + @propertyName + ''')
    BEGIN
        INSERT INTO [internal].[etl_status]
        VALUES (''' + @propertyName + ''',  NULL)
    END
END

UPDATE [internal].[etl_status]
SET [property_value] = ''' + @propertyValue+ '''
WHERE [property_name] = ''' + @propertyName + '''
;'
if @propertyValueIsFunction = 1
    set @query = replace(@query,'''' + @propertyValue + '''',@propertyValue)
print @Query
--EXEC(@query)

Assuming (notice the extra parameter value of 1 to indicate function):

exec [dbo].[sp_set_etl_status] 'admission_last_import_start_dttm', 'GETDATE()',1

the resulting query generated would look like this:

BEGIN
    IF NOT EXISTS ( SELECT * FROM [internal].[etl_status]
                        WHERE [property_name] ='admission_last_import_start_dttm')
    BEGIN
        INSERT INTO [internal].[etl_status]
        VALUES ('admission_last_import_start_dttm',  NULL)
    END
END

UPDATE [internal].[etl_status]
SET [property_value] = GETDATE()
WHERE [property_name] = 'admission_last_import_start_dttm'
;

When you print the @query text, you'll see that the quotes are stripped from the function being passed in.

You may have to play around with different scenarios, but this was my first thought to solve the original problem.


Is polling the only way for updating app's data from a database?

In Oracle you can use the built in DBMS_ALERT package to facilitate this.

  DBMS_ALERT supports asynchronous
  notification of database events
  (alerts). By appropriate use of this
  package and database triggers, an
  application can notify itself whenever
  values of interest in the database are
  changed.

  Suppose a graphics tool is
  displaying a graph of some data from a
  database table. The graphics tool can,
  after reading and graphing the data,
  wait on a database alert (WAITONE)
  covering the data just read. The tool
  automatically wakes up when the data
  is changed by any other user. All that
  is required is that a trigger be
  placed on the database table, which
  performs a signal (SIGNAL) whenever
  the trigger is fired.


Trim whitespace (spaces, tabs, newlines)

For anyone using SQL Server 2017 or newer

you can use the TRIM built-in function. For example:

DECLARE @Test NVARCHAR(4000);
SET @Test = N'
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~'
        + TRIM(NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A) FROM @Test)
        + N'~';

Please note that the default behavior of TRIM is to remove only spaces, so in order to also remove the tabs and newlines (CR + LFs), you need to specify the characters FROM clause.

Also, I used NCHAR(0x09) for the tab characters in the @Test variable so that the example code can be copied-and-pasted and retain the correct characters. Otherwise, tabs get converted into spaces when this page is rendered.

For anyone using SQL Server 2016 or older

You can create a function, either as a SQLCLR Scalar UDF or a T-SQL Inline TVF (iTVF). The T-SQL Inline TVF would be as follows:

CREATE
--ALTER
FUNCTION dbo.TrimChars(@OriginalString NVARCHAR(4000), @CharsToTrim NVARCHAR(50))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH cte AS
(
  SELECT PATINDEX(N'%[^' + @CharsToTrim + N']%', @OriginalString) AS [FirstChar],
         PATINDEX(N'%[^' + @CharsToTrim + N']%', REVERSE(@OriginalString)) AS [LastChar],
        LEN(@OriginalString + N'~') - 1 AS [ActualLength]
)
SELECT cte.[ActualLength],
       [FirstChar],
       ((cte.[ActualLength] - [LastChar]) + 1) AS [LastChar],
       SUBSTRING(@OriginalString, [FirstChar],
                 ((cte.[ActualLength] - [LastChar]) - [FirstChar] + 2)) AS [FixedString]
FROM   cte;
GO

And running it as follows:

DECLARE @Test NVARCHAR(4000);
SET @Test = N'
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~' + tc.[FixedString] + N'~' AS [proof]
FROM   dbo.TrimChars(@Test, NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A)) tc;

Returns:

proof
----
~this
              content~

And you can use that in an UPDATE using CROSS APPLY:

UPDATE tbl
SET    tbl.[Column] = itvf.[FixedString]
FROM   SchemaName.TableName tbl
CROSS APPLY  dbo.TrimChars(tbl.[Column],
                           NCHAR(0x09) + NCHAR(0x20) + NCHAR(0x0D) + NCHAR(0x0A)) itvf

As mentioned at the beginning, this is also really easy via SQLCLR since .NET includes a Trim() method that does exactly the operation you are wanting. You can either code your own to call SqlString.Value.Trim(), or you can just install the Free version of the SQL# library (which I created, but this function is in the Free version) and use either String_Trim (which does just white space) or String_TrimChars where you pass in the characters to trim from both sides (just like the iTVF shown above).

DECLARE @Test NVARCHAR(4000);
SET @Test = N'
    ' + NCHAR(0x09) + N'  ' + NCHAR(0x09) + N' this
 ' + NCHAR(0x09) + NCHAR(0x09) + N'  content' + NCHAR(0x09) + NCHAR(0x09) + N'
' + NCHAR(0x09) + N' ' + NCHAR(0x09) + NCHAR(0x09) + N'     ';

SELECT N'~' + SQL#.String_Trim(@Test) + N'~' AS [proof];

And it returns the exact same string as shown above in the iTVF example output. But being a scalar UDF, you would use it as follows in an UPDATE:

UPDATE tbl
SET    tbl.[Column] = SQL#.String_Trim(itvf.[Column])
FROM   SchemaName.TableName tbl

Either one of the above should be efficient for using across millions of rows. Inline TVFs are optimizible unlike Multi-statement TVFs and T-SQL scalar UDFs. And, SQLCLR Scalar UDFs have the potential to be used in parallel plans, as long as they are marked as IsDeterministic=true and do not set either type of DataAccess to Read (the default for both User and System data access is None), and both of those conditions are true for both of the SQLCLR functions noted above.


When issuing CREATE USER GuardDog_02... the exact user name created is
guardgog_02 because of SQL case folding. You may check that with \du in psql.

But according to that error message:

  psql: FATAL:  password authentication failed for user "GuardDog_02"

the username you're trying to log in with is GuardDog_02 (G,D in capital letters), which does not exist with this capitalization

To create that exact name, the identifier must be enclosed in double quotes, as in:

CREATE USER "GuardDog_02" WITH PASSWORD 'apple100' CREATEDB CREATEUSER;

Or, alternatively, if you don't want to recreate the user, log in with psql -U guarddog_02.


First issue that I have noticed in the provided plan is --> There are so many implicit conversions as listed below:

<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0002'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0008'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0017'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0019'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0004'"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(50),[fe].[subscriber_number],0)=CONVERT_IMPLICIT(nchar(11),[f].[subscriber_number],0)"/>
              <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(4),[fe].[plan_variation_subdivision_code],0)=N'0020'"/>

Due to this issue you have issue in cardinality estimate and also in Seek Plan.

Please read more on this from below links:

https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

https://hackernoon.com/are-implicit-conversions-killing-your-sql-query-performance-70961e547f11

https://www.sqlservercentral.com/forums/topic/type-conversion-may-affect-cardinalityestimate

https://www.sqlshack.com/implicit-conversion-in-sql-server/

Whenever you see yellow mark on the Select box in the query plan, please hover that and see what is the warning. Most of the time, it would give you very meaningful feedback.

Another issue I see is with the table CDB_MEMBER, its going for clustered index scan, meaning full table scan and if you would have noticed there is minor difference in estimates and actuals suggesting stats are a bit out of sync. Important aspect here is missing NC index which should have columns CDB_MBR_COV_REC_ID and SBSCR_ID

I think above two should help you a bit in fine-tuning the execution and reduce duration.

Please let us know if this helped.


Creating a foreign server in Postgres only if not exists

Try a DO statement:

DO
$$BEGIN
   CREATE SERVER ...;
EXCEPTION
   WHEN duplicate_object THEN
      NULL;  -- ignore
END;$$;


If value is null then take the next higher value

Currently is a data_rate is passed that is not in the list is will not return an annual_rental value. I would like to update this so if the value for data_rate is 30 it will take the next higher value e.g. 100 so annual_rental of £185.00 would be returned.

Maybe you need
SELECT
    annual_rental
FROM
    odwh_system.ead_credit_charge
WHERE
    connection_type = connection_type$
AND data_rate >= data_rate$
AND start_datetime$::date BETWEEN COALESCE(valid_from,start_datetime$::date) AND COALESCE(valid_to,start_datetime$::date)
ORDER BY data_rate LIMIT 1;

?


Is it possible to quickly create/restore database snapshots with PostgreSQL?

You could use file-system level snapshots, but that is often pretty cumbersome, needs special file systems, and is not always available, especially on aging laptops. ;-)

How about you create your base state as a database, and then create a new database from it for your test run, using the CREATE DATABASE ... TEMPLATE functionality.  After the test, you throw that database away.  Then your speed constraint is essentially only the time to cp -R the database directory.  That's about as fast as you're going to get without file system snapshot magic.