How to express this constraint in a database schema?

In a nutshell, introduce d into the third table to enable vanilla foreign key constraints e.g. Transitional SQL-92 syntax:

CREATE TABLE T1
(
 a INTEGER NOT NULL,
 d INTEGER NOT NULL,
 UNIQUE (a, d)
);

CREATE TABLE T2
(
 b INTEGER NOT NULL,
 d INTEGER NOT NULL,
 UNIQUE (b, d)
);

CREATE TABLE T3
(
 a INTEGER NOT NULL,
 b INTEGER NOT NULL,
 c INTEGER NOT NULL,
 d INTEGER NOT NULL,
 UNIQUE (a, b, c),
 FOREIGN KEY (a, d) REFERENCES T1 (a, d),
 FOREIGN KEY (b, d) REFERENCES T2 (b, d)
);


In addition to Scott's answer, you can just move the concat to the where clause as well.

SELECT CONCAT(FirstName, ' ', LastName) as FullName
FROM Customer
WHERE CHARINDEX('Martin', CONCAT(FirstName, ' ', LastName), 1) <> 0

Or, as a derived table.

SELECT FullName
FROM(
SELECT CONCAT(FirstName, ' ', LastName) as FullName
FROM Customer) x
WHERE CHARINDEX('Martin', FullName, 1) <> 0

As for why you can't use the alias in the WHERE clause, it's because queries don't start with SELECT. Your WHERE is evaluated before the column list is selected.

Julia Evans: SQL Queries don't start with select
Similar Question on Stackoverflow


Getting a "type" column from each table in query

Perhaps this is what you are looking for:

(SELECT id, name, 'Book' AS media_type
FROM books
LIMIT 0,3)

UNION ALL

(SELECT id, name, 'Video' AS media_type
FROM videos
LIMIT 0,3)

UNION ALL

(SELECT id, name, 'Album' AS media_type
FROM albums
LIMIT 0,3);

I don't know if you need any particular ordering or not.  According to the docs, UNION produces an unordered set of rows.  You should be able to add an ORDER BY after the last SELECT, outside the parentheses.


SQL Mirroring Locking/Blocking Contention

1) If we utilize snapshot for reporting queries, and high volume OLTP
  transactions are sent from Server A to Server B, Will Report queries
  create locking/blocking contention, which will prevent Server B Mirror
  from receiving updates?

It will depend. If you are reading a page that has been changed since snapshot was created you are reading the page from sparse file. In that case there is no contention. But if you are reading from a page that did not change since snapshot was created you are reading from source database file. Which may cause contention at the file level since both the source database and the snapshot will be accessing the same MDF.

These are two good articles you might find helpful.

Database Snapshots by  Basit Aalishan Masood-Al-Farooq
SQL Server 2005 Snapshots by Andrew Calvett.

  2) If so, can I place the Server B Mirror database in Read-Committed
  Snapshot Isolation (RCSI), to prevent locking/blocking (while ServerA
  is in Read committed) ? Or does Mirror Database always have to be in
  same transaction isolation level as Principal ?

You cannot do that. As Mirror Database is in restoring state, you cannot make any change. Allowing different configuration goes against the principal or database mirroring where it guaranteed to have same configuration and data so roles of Principal and Mirror can be switched at any time.

Not related to your question but you might find this useful especially the comment in fact, you can’t execute this on the mirror, because the database is in the restoring state.

How to Enable RCSI for a Database with Database Mirroring by Sanjay Mishra


Unpacking JSON into 14 columns without crosstab() and tablefunc?

Unpack your JSON to separate rows using
SELECT json_unpacking.foo,
       values.value,
       ROW_NUMBER() OVER (PARTITION BY foo) value_number
FROM json_unpacking
CROSS JOIN json_each(json_unpacking.bar) values

Then combine it back into needed row structure using grouping and conditional aggregation.


How to name table-variable function unique constraint?

Table Variables, whether created from a DECLARE or from a RETURNS in a CREATE FUNCTION statement, as well as User-Defined Table Types (UDTTs), do not allow for constraints to be named. According to the MSDN page for CREATE FUNCTION:

  Arguments
  ...
          < column_constraint >::= and < table_constraint>::=
          ... Named constraints are not allowed.

A "stated" exception to this rule is found on the MSDN page for DECLARE @local_variable which states (under the explanation of DEFAULT):

  To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.

However, it appears that this is an error with the documentation as I am unable to assign a name to a DEFAULT Constraint, there is no description of the syntax to accomplish this, and there are no examples of doing this in the documentation (I looked as far back as SQL Server 2000).

When it comes to Temporary Tables the issue is a bit more straight forward: if you explicitly name any constraints, that would disallow the code from running in two different sessions at the same time. Constraints are objects (unlike Indexes) and need to have unique names within the same schema. Hence, if some code creates a local Temporary Table that has constraints that are given names, then if two sessions run that code at the same time, the second session would get an error when it went to create its Temporary Table due to the name conflict (which occurs in tempdb, where Temporary Tables exist). For example, run the following in one query tab in SSMS:

CREATE TABLE #Session1 (Col1 INT NOT NULL CONSTRAINT [UQ_DifferentSession] UNIQUE);

And in another query tab, run the following:

CREATE TABLE #Session2 (Col1 INT NOT NULL CONSTRAINT [UQ_DifferentSession] UNIQUE);

Even with the Temporary Tables having different names in addition to being local Temporary Tables created in different sessions, you will still receive the following error:

  Msg 2714, Level 16, State 5, Line 1
  There is already an object named 'UQ_DifferentSession' in the database.
  Msg 1750, Level 16, State 0, Line 1
  Could not create constraint. See previous errors.

Similar to Temporary Tables, Table Variables also do not have any definition until they are "declared" and then that definition only resides in tempdb. But we can't test for name collisions on Table Variables since you cannot name their constraints.

Returned tables in Multi-statement TVFs, and User-Defined Table Types, on the other hand, exist a little differently than Temporary Tables in that they store their definition in the database where they exist. But the meta-data they store in their local database is never actually used to store data; their meta-data is only used as a template for what will get created (when needed) in tempdb. And when that meta-data "template" is used, the named objects in tempdb (Primary Keys, Unique Constraints, Default Constraints, and Check Constraints) get dynamically generated names in the form of UQ__#BC4FB52__A259EE56FAB8BB6F.

Regarding the desire to name the constraints in order to be in line with naming conventions (as stated in a comment on the Question):

While it wouldn't hurt anything to use sp_rename (as pointed out in @Peter's answer), it would just be a superficial change. One of the main reasons to have a naming convention is for maintenance, so that if you need to ALTER or DROP the object later, you won't need to write queries to discover what is there and then concatenate that into Dynamic SQL. But that concern isn't relevant here since there is no way to ALTER or DROP them anyway.

Also, any name change done via sp_rename would be short-lived since any ALTER FUNCTION will drop and recreate the Constraints, and hence give them newly generated object names. This might could be handled programmatically with a DDL Trigger that captures CREATE FUNCTION, ALTER FUNCTION, and CREATE TYPE statements, but the end-result of that effort won't be that your life is any easier ;-). It would just be that selecting from sys.objects, sys.indexes, and sys.key_constraints looks "cleaner".


Specifying the failover partner when using Availability Groups

There's two parts to this question:

First, can you use the Failover Partner connection string tip with AlwaysOn Availability Groups?  No.  AlwaysOn AG's "Listener" technology is the replacement.  Have your connection strings point to the listener name and they'll always get the primary replica.  (For the next part of this answer, I'm assuming you're using the listener name - if you're not, start, heh.)

Second, why do some queries fail to connect to the listener?  This has to do with the number of DNS entries for the listener.  All possible subnets for the listener will be in DNS at all times.  If you've got a listener in 192.168.1.X and another in 192.168.100.x, both listeners will always be in DNS.  By default, your clients will try connecting to each of the DNS entries serially, and not always in numeric order.  If you've got a 30-second connection timeout, it's possible that your app will only try one of the IPs and then fail before it has the time to try the second one.

If you want to try connecting to all possible IPs simultaneously, check out the MultiSubnetFailover = True options for the SQL Server client as described here: http://msdn.microsoft.com/en-us/library/hh205662.aspx

Otherwise, you'll need to increase your connection timeout to account for the multiple IPs.

Update Feb 27: the question added, "I was therefore trying to come up with a way to avoid manual intervention for these (replicated) DBs should a failure of the replica occur and the other databases which are part of an AG failover."

Ooo, unfortunately, no, if the databases aren't part of the Availability Group, you're going to be doing manual work in order to fail over. One popular option is to use a DNS record, and just repoint the DNS record at whatever server is currently hosting the primary copy of the databases.


This is probably due to the fact that the text has Carriage Returns in it and that you are using the default row delimiter which is the Carriage Return.

This will be tough to fix since you can't know when a Carriage Return is part of a column or actually a new line. the file needs to be fixed manually.

Unless you know where the file came from...  If there's a possibility  for you to reprocess the file, then I would reprocess it, replacing all Carriage Returns found in columns by blanks ('').

I hope this helps.


Sum multiple columns, based on distinct values in different Columns

For the one row you posted expected results for, this will return what you want.
In the future, please post your table as an insert script so that it's easier to work with.
SELECT
    x.*
INTO #x
FROM
(
    VALUES
        (1510142123,-589.53,0,0,'CC',5831879,NULL,NULL),
        (1510142123,-589.53,0,0,'CC',5831882,NULL,NULL),
        (1510142123,-155.06,0,0,'CC',5898896,NULL,NULL),
        (157771145,-126.42,0,0,'CC',5885900,NULL,NULL),
        (157771145,-58.73,0,0,'CC',5885903,NULL,NULL),
        (158088837,-55.14,0,-3.45,'CC',5897306,NULL,5897303),
        (158088837,-5.75,0,-3.45,'CC',5897309,NULL,5897303),
        (158464166,-161,0,-3.45,'CC',5910551,NULL,5910548),
        (158464166,-24.15,0,-3.45,'CC',5910554,NULL,5910548),
        (1591970734,-111.61,0,0,'Bank',5939648,NULL,NULL),
        (1591970734,-0.01,0,0,'Cash',5939711,NULL,NULL),
        (1591970734,-0.01,0,0,'Cash',5939714,NULL,NULL),
        (159297565,-708.93,20,0,'CC',5943728,5910848,NULL),
        (159297565,-0.02,20,0,'Cash',5948207,5910848,NULL)
)AS x (id,payment, credit, debit, [source], paymentid, creditid, debitid);

SELECT
    x.id,
    SUM(DISTINCT y.payment) AS payment,
    SUM(DISTINCT y.credit) AS credit,
    SUM(DISTINCT y.debit) AS debit,
    MAX(x.source) AS source,
    MIN(x.paymentid) AS min_paymentid,
    MAX(x.debitid) AS max_debitid
FROM #x AS x
CROSS APPLY
(
    SELECT
        SUM(x2.payment) AS payment,
        SUM(x2.credit) AS credit,
        SUM(x2.debit) AS debit
    FROM #x AS x2
    WHERE x.id = x2.id
    AND   x.paymentid = x2.paymentid
    GROUP BY x2.id,
             x2.creditid,
             x2.debitid
) AS y
GROUP BY x.id
ORDER BY x.id;


executing single query in loop increase time every time

What percentage of the 12M rows are you transferring?

This gets slower and slower as you increase the OFFSET:

limit 24000,3000

Instead, "remember where you left off".  More:  http://mysql.rjweb.org/doc.php/pagination and http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

Is there an index on callstart?

The INDEX cdrs_data is a terribly long index.  The only way it might be useful is if it is "covering".

The TABLE cdrs_data -- does it have any indexes?  Of so, disabling the indexes before starting the inserts is likely to speed things up.  Reenable afterward.

Another option is to use mysqldump with a --where clause. for the dump side; then mysql for loading in the new place.


Subtracting dates give me error in Postgresql

If those columns are timestamp (or date) columns, just use

age(MySecondDateField, MyFirstDateField)

The timestamp or date prefix is only needed for date/timestamp constants.

If those columns are varchar then you should change them to a proper timestamp/date datatype.

Until you do that, you can use the to_date() or to_timestamp() function to convert a string to a date, e.g:

to_timestamp(MySecondDateField, 'yyyy-mm-dd hh24:mi:ss')

For more details on those functions, please see the manual: http://www.postgresql.org/docs/current/static/functions-formatting.html

You should never store dates (or numbers) as varchar/text columns. To fix your table, you can use a conversion function when you run the alter table:

alter table wrong_design
   alter MyFirstDateField type timestamp
   using to_timestamp(some_col, 'yyyy-mm-dd hh24:mi:ss.us')


In the root container you can switch the current session to a specific PDB with

ALTER SESSION SET container=orcl;

If you want to actually connect to a specific container (because the user only exists in that container or you have an application which should not know about containers) then you have to connect to the actual net service for the specific PDB. You can use lsnrctl service or check the default tnsnames.ora. There is a good article about this.

This requires some preparation, so check out the Oracle documentation for the concepts as well, but in essence you would have to use a TNS alias or connection description specifying SERVICE_NAME=PDB1 (in your case probably orcl, although that’s a uncommon name for a PDB) instead of a SID or the CDB net service name.


How to maintain unique sequence across table columns?

Plan A:  Have a 3rd table that contains an AUTO_INCREMENT plus any common columns between the two existing tables.

Plan B:  Build a "sequence" table which has 2 columns:  an AUTO_INCREMENT and another column to key into the table.  Probably REPLACE is the command to get a new id without growing the table.

REPLACE is performed in 2 steps:

DELETE any row(s) that have match on any UNIQUE key.
INSERT a new row.

Here's the table and its initialization:

    CREATE TABLE sequence (
        seq INT UNSIGNED AUTO_INCREMENT NOT NULL,
        code CHAR(1) CHARACTER SET ascii NOT NULL,
        PRIMARY KEY(seq),
        UNIQUE(code) ) ENGINE = InnoDB;
    INSERT INTO sequence (seq, code) VALUES (1, 'a');

To get the new 'next' sequence number, either use REPLACE:

    REPLACE INTO sequence (code) VALUES ('a');
    SELECT seq FROM sequence WHERE code = 'a';

Or, and this is probably faster,

    INSERT INTO sequence (seq, code)
        VALUES (NULL, 'a')
        ON DUPLICATE KEY UPDATE
            seq = LAST_INSERT_ID(seq + 1);
    SELECT LAST_INSERT_ID();


pg_indexes.tablename only contains the table name, not the schema name. The schema name is available in the column schemaname.

So you need to use

select *
from pg_indexes
where tablename ='asignacion'
  and schemaname = 'distribucion';


SQL server Audit- Does it cause overhead

If I enable Audit on production databases that have over 100 m rows and have constant DML statements going (24 hours), is that going to cause any overhead on SQL server performance or tempdb?

To directly answer your question, yes. Auditing always has overhead. If you have to write down what you've done and call extra code that wouldn't normally be called then by definition it'll have overhead.

Whether that overhead is appreciable enough to cause you an issue we can't say. This is completely dependent upon:

How the auditing is implemented
What you are auditing
Where you are holding your audit information

There will be a different in performance using some 3rd party solution utilizing detours/iat/eat hijacking/whatever versus a trace vs security (extended events). Your mileage will vary with all of these (not all supported) choices.

  Does that increase the .ldf log size of the database its monitoring?

If, for some reason, you keep the audit records in a table in that database then yes... otherwise, no.


Recover parameters for stored procedure from system logs

There is no built-in logging mechanism that runs by default out-of-the-box to capture stored procedure parameter values.

Depending on the version of SQL Server you're using, the best way to capture that detail is via Extended Events (actual execution plans and looking at parameter runtime values. Pretty expensive though, and a bear to parse out via XML).

I suppose "best" is very subjective.  I don't think there is really a great, low-impact, way to get the actual parameter values for every execution of a stored proc through some SQL Server built-in mechanism.  It's probably easier to log that through the client, or via logging built into the stored proc.


How to tell how long autovacuum took to vacuum a postgres table?

Autovacuum durations are reported in the logs when log_autovacuum_min_duration is set to 0 or a duration threshold.
It produces this kind of output:

2021-05-17 03:59:40.530 CEST [25526] LOG:  automatic vacuum of table "db.public.tablename": index scans: 1
    pages: 0 removed, 1001527 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 1346603 removed, 15288094 remain, 224598 are dead but not yet removable, oldest xmin: 736
    buffer usage: 1104110 hits, 2210831 misses, 1716564 dirtied
    avg read rate: 2.329 MB/s, avg write rate: 1.808 MB/s
    system usage: CPU: user: 46.90 s, system: 60.02 s, elapsed: 7417.28 s

As can be seen in this example, it can be very long on large tables, but it does not consume so much resources due to throttling.
When the operation is still in progress, it can be monitored through
the system views pg_stat_progress_vacuum  and  pg_stat_activity (to be joined with the pid column).


The last two lines are an abbreviated way of solving the problem without recurring to the complete algorithm to check for dependency preservation. In particular the teacher noted that combining the dependencies that you can obtain from R1 and R3 you cannot obtain C, which is essential to get A in the dependency  BC → A. This dependency can never be derived by the projection of F over the decomposed relations.

Here follows the complete explanation.

To check if a decomposition preserves the dependencies, one can use the Ullman algorithm that checks, for each dependency X → Y in F, if Y is contained in the closure of X with respect to the projection of F over the decomposition (formally, calling G the union of the projection of the dependencies over the decomposed relations Ri, that is G = ∪πRi(F), the algorithm checks if X → Y ∈ G+, the closure of G, by checking that Y ⊆ X+G).

So, we must calculate X+G for each dependency of F, to check if Y is contained in it.

Such a closure is computed with the following algorithm.

Initialize X+G with X,
at each step, for each relation Ri, add to X+G the attributes obtained by calculating the closure of the attributes of X+G ∩ Ri with respect to F, and projecting the result again on Ri. In symbol: X+G := X+G ∪ ((X+G ∩ Ri)+F ∩ Ri)
repeat 2 while X+G changes.

Let’s try it for the dependency BC → A.

Initially:

BC+G = BC,

using R1(AB), BC ∩ AB = B, B+F = BD, so intersecting it with AB gives B, which is already in BC+G;

using R2(BC), BC ∩ BC = BC, BC+F = ABCDEG, so intersecting it with BC gives BC which is already in BC+G;

using R3(ABDE), BC ∩ ABDE = B, B+F = BD, so intersecting it with ABDE we obtain BD and we can add D to BC+G, which now becomes BCD;

BC+G is changed, so we must restart checking for the new value BCD. The first two relations do not give any contribute, since D is not present in them. The third one must be considered again:

using R3(ABDE), BCD ∩ ABDE = BD, BD+F = BD, we can not add anything;

using R4(EG), BCD ∩ EG = ∅,  nothing to add again.

So we have terminated, beacause no new attribute can be added to the closure, and since BC+G = BCD does not contain A, we can conclude that at least this dependency is not preserved, and so that the decomposition does not preserve the dependencies.

And since you asked how to decompose a relation without losing dependencies, you can look at the synthesis algorithm to decompose a relation in Third Normal Form (3NF). You can find it on any good book on databases, and it guarantees to preserve both the dependencies as well as the data (lossless decomposition), while reducing redundancies and anomalies.

In this case a decomposition in 3NF is the following:

R1 < (ADE) ,
{ AD → E } >

R2 < (BD) ,
{ B → D } >

R3 < (ABC) ,
{ BC → A,
AC → B,
AB → C } >

R4 < (EG) ,
{ E → G } >


SQL Server 2008 upgrade to SQL Server 2012 on a cluster

The Setup.exe will do the dirty work for you and take the passive node (currently upgrading node) away from being a possible owner of the SQL FCI's virtual network name (VNN).  But if you want to ensure that this is happening (which I recommend, it's what I always do), then you can manually take the passive node you're about to upgrade off of the possible owners list for the FCI's VNN.

You could use the GUI (failover cluster admin) or PowerShell to accomplish this task.  Below is an example of the latter:

Get-Cluster -Name "your cluster name" |
    Get-ClusterGroup -Name "SQL Server (your instance name)" |
    Get-ClusterResource -Name "SQL Network Name (your FCI's VNN)" |
    Set-ClusterOwnerNode -Owners "current active node"

All of the parameter strings there need to be specific to your environment.  Note, calling Set-ClusterOwnerNode will set the list of possible owners for that cluster resource.  Depending on how many nodes are in your cluster, that could contain more than one string (i.e. Set-ClusterOwnerNode -Owners "ACTIVENODE", "SOMEOTHERNODE", "ETC.").

Something to be knowledgeable about, there is a KB for a fix for a failover cluster instance upgrade from SQL Server 2008 [R2] to SQL Server 2012.  I recommend you review it before the operation.

As for other tips and tricks, I would recommend you read this BOL reference on upgrading a SQL Server Failover Cluster Instance.  There is a lot of great information in that article that should be considered.


What is the reason to "normalize your databases"?

Your issue is that you are getting two different pieces of advice conflated into one and the justifications for each piece of advice are not being presented clearly.
Recomendation 1: Normalize your database
In any transactional database this is generally considered a best practice.  There are lots of reasons why you might back away from this and there are applications, like BI data warehouses, where this is not necessarily what you want.  However, for a transactional database you normalize to begin with and denormalize when necessary as a rule of thumb.
Where there seems to be some confusion is around why to normalize.  You are not alone in this confusion. A lot of people have a lot of misconceptions about the purpose of normalizing your database.
Normalization is NOT (primarily) about:

Increasing performance
Saving memory
Saving disk space
Reducing duplication (it is about reducing redundancy, but that is not exactly the same thing as duplication - more below)

Normalization IS about:

Data Quality (first and foremost)

Normalizing your database design means that you don't store a piece of data in multiple places so that if you need to update or delete it for any reason you might get yourself in trouble because the data will need to change in more than one place.  When you don't normalize you end up with the very real possibility of your data becoming inconsistent over time as changes are made inconsistently.  This is a little bit of an over simplification because there are other benefits of normalization, such as simplified query logic. However, data quality is by far the most important benefit and, ironically, it is the one which programmers without formal database design training most often fail to understand clearly.
Recommendation 2: Use Integer Surrogate Keys
This falls much more under the rubric of common custom than anything else.  Lots of people like to use a meaningless integer primary key in all their relational database tables. This in itself is actually two pieces of advice: (i) use surrogate keys in every table and (ii) use integers for surrogate keys.
Different people will give you different reasons for why they consider these best practices and all of these reasons can be argued over on a case by case basis.  The best argument I can think of for using surrogate keys is that natural keys are more likely to be changed.  Changing any primary key is a giant pain so it's best to avoid if you can.  The best argument I can think of for using integers for your surrogate key is that it's a nice simple data type which is compact and efficient.  Again, this is highly situational so people will make an argument for or against this in different cases.
What I would say about Recommendation 2 overall is this: pick a lane and stick to it so that your code is relatively consistent and diverge from this only when you have a really compelling case of critical performance or critical efficiency and you can clearly demonstrate that diverging from your usual approach has significant benefits.

In general, I avoid using natural keys, but there are times when you can get away with it, and even times when it makes better sense to use them. The question you need to ask yourself with a natural key is "Might this change in the future?"
My rule of thumb is "if a user can see it, they're going to want to change it someday". In your specific case though, language codes are set by an international standards body, so the chance that they might change are pretty slim — it would be too big of a pain.  I wouldn't hesitate in your case to use "en-us" as a key value.


https://dev.mysql.com/worklog/task/?id=3698

Currently, all datetime values specified in CREATE EVENT are converted to UTC
time zone internally.


The merge process could not connect to the Publisher ' '. Check to ensure that the server is running. (

The error message is stated plainly:

  Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

Try logging in to your Publisher as the replication agent account running on the subscriber (connecting from the subscriber through SSMS). There could have been changes to domain trusts, changes to domain membership, broken SPNs etc that could be causing this.

Validate your domains have a trust between them, your SPNs are configured correctly and that your replication accounts can actually log in to each instance. Also, double check that the accounts themselves are not disabled and\or have expired passwords.


select datepart(minute, '04:15')

Utilize the DATEPART() function, with minute as the datepart param.

declare @current_time time

select @current_time = getdate()

select datepart(minute, @current_time) as current_minute

BOL Reference on DATEPART


Error ORA-12505 when trying to access a newly installed instance of oracle-11g express

you should create at least one database user that you will use to create database objects. A database user is a type of database object: a user is associated with a database schema, you connect to the database as a database user, and the database user is the owner of any database objects (tables and so on) that you create in the schema associated with the user.

For example, to create a database user named chris(For Example). Follow these steps, using the command line:

Display the SQL command prompt window. For example, on Windows, click Start, then Programs (or All Programs), then Oracle Database 11g Express Edition, and then Run SQL Command Line.

Connect as the SYSTEM user:

Type: connect
Enter user-name: system
Enter password:
Create the user. For example, enter a statement in the following form:

SQL> create user chris identified by <password-for-chris>;

Grant the user the necessary privileges. For example:

SQL> grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
  CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
  CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, -
  CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
  to chris;

Optionally, exit SQL*Plus (which also closes the command window):

SQL> exit

SQL Developer as an Alternative for Creating Database Users:

If you have experience with SQL Developer, you can use it instead of the command line to create a database user, as follows:

Create a database connection for the SYSTEM user.

Open that database connection for the SYSTEM user.

Right-click the Other Users node in the Connections navigator under that connection.

Select Create User, and specify the necessary information. (Under System Privileges, grant ALTER SESSION, CREATE SESSION, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, and UNLIMITED TABLESPACE.)

I am also attaching the screen shot with example , which is given by Oracle BOL for SQL Developer connection.

For your ref Here And Here.


This seems to do what you want:

select jsonb_build_object('uid', b.uid, 'nested', jsonb_agg(to_jsonb(m)))
from base b
  join lateral (
    select n.uid, n.message
    from nested n
      join jsonb_array_elements_text(b.nested) as x(bid) on x.bid = n.uid
  ) m on true
group by b.uid
order by b.uid;

Online example: https://rextester.com/ASA37564


The repmgr doc you linked to says to use wal_level = 'hot_standby' with PG versions 9.5 or older, and wal_level = 'replica' for PG versions 9.6 or newer.

# Ensure WAL files contain enough information to enable read-only queries
# on the standby.
#
#  PostgreSQL 9.5 and earlier: one of 'hot_standby' or 'logical'
#  PostgreSQL 9.6 and later: one of 'replica' or 'logical'
#    ('hot_standby' will still be accepted as an alias for 'replica')
#
# See: https://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-WAL-LEVEL

You're using PostgreSQL 10, so replica or logical should be used for this option.


Replication/log shipping to a standby site

1. Is there any data type or primary key dependency exists if I use log shipping?

Log shipping works on database log backups. Any DML operation is captured in logs, so it is applied to secondary. No question of datatype or primary key dependency for logshipping to work.

  2. Why I have to use network folders/directories. Why can I not use local folders to capture log files?

Logshipping uses copy, restore jobs. Copy job copies log backups from primary to secondary and restore job restores them. So, in this case, assuming log shipping is between different instances of SQL, how can one job have visibility if it is local path?

  3. What about the password between the two databases i.e primary and standby as primary will connect to standby?

Copy job of primary should have write access and restore job should have read access.

  4. Is there any method whereby I can open my standby database in read only mode and logs are applied without interrupting or disconnecting my users?

No, it's not possible in log shipping. AlwaysOn does this by providing read only replicas. Further quote from MSDN on two options for log shipping:

From Using Secondary Servers for Query Processing

    There are two options for configuration when you place the secondary database in standby mode:

    You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. Disconnection will happen on the schedule you set for the restore job.

    You can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database.

  5. I have a very large database i.e of 45 TB. So for disaster recovery solution what method do you suggest?

First you should have valid backups that are tested routinely. Then you need to ask yourself:

How much downtime can I afford?
Budget my business can spend?
Do I need secondary databases for reporting?

Based on above questions,you can see what are the total HA solutions available and what is best for me.

  6. Please also mention the difference between "Log shipping" & "Always ON".

There are lot of differences you can you see below.

What is better to use - database mirroring or log shipping for SQL Server?


How to get prior data in a missing dates in sql table

You should really have a Calendar table for this sort of thing. Using a recursive cte is going to be one of the worst performing options as the size of your date range gets larger.

For only 152kb in memory, you can have 30 years of dates in a table with this:

/* dates table */
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo
               cross join n as tenK cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date
  on dbo.Dates([Date]);

Without taking the actual step of creating a table, you can use it inside a common table expression with just this:

declare @fromdate date = '20161229';
declare @thrudate date = '20170103';
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1)
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
select [Date]
from dates;

Use either with outer apply() like so:

select
    d.[Date]
  , r.rateValue
from dates d
  outer apply (
    select top 1 rateValue
    from rates i
    where i.rateDate <= d.Date
    order by i.rateDate desc
    ) r;

rextester demo: http://rextester.com/NVY8460

returns:

+------------+-----------+
|    Date    | rateValue |
+------------+-----------+
| 2016-12-29 | 1.2266    |
| 2016-12-30 | 1.2345    |
| 2016-12-31 | 1.2345    |
| 2017-01-01 | 1.2345    |
| 2017-01-02 | 1.2345    |
| 2017-01-03 | 1.2240    |
+------------+-----------+

Number and Calendar table reference:

Generate a set or sequence without loops - 1 - Aaron Bertrand
Generate a set or sequence without loops - 2 - Aaron Bertrand
Generate a set or sequence without loops - 3 - Aaron Bertrand
The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden
Creating a Date Table/Dimension in sql Server 2008 - David Stein
Calendar Tables - Why You Need One - David Stein
Creating a date dimension or calendar table in sql Server - Aaron Bertrand
tsql Function to Determine Holidays in sql Server - Aaron Bertrand
F_table_date - Michael Valentine Jones


Does PostgreSQL consider how evenly data is distributed on disk when choosing a seq scan?

In short, No.

One of the statistics which PostgreSQL collects is the correlation between the values of a column (actually the ranks of those values) versus the location of that row in the table.  You can find that result in pg_stats.correlation for your specific tablename and colname.

However, the particular query you give is not one of the situations where this information is used.  The primary use is to estimate how much of the IO done for the table-lookup step of a large index range scan (e.g. "WHERE state='Ca'", or "WHERE age is between 50 and 65") will be sequential-like IO (either really sequential, or close enough to sequential so that read-ahead still triggers, or falling into the same table-block as the previous row so requiring no IO at all) rather than random IO.

Also, since your relation is not monotonic but rather more like a parabola, the estimated correlation will undervalue the strength of the correlation in your case, as it only looks at linear correlations.


How can I create a dblink from Postgresql 8.3 to SQL Server (2000 and 2008)?

First off, you should be aware that PostgreSQL 8.3 will soon be going EOL.  You should probably use a more current version.

In fact, if you did use a more current version (9.1 or later), you could use Foreign Data Wrappers (FDW).  Someone has even posted source code for a TDS FDW, which should work for older SQL Server.

If you're somehow stuck with PostgreSQL 8.3, user DBI-Link as Craig Suggests.


MySQL Workbench: Custom fields for Relationships

Are you using the Place a relationship using existing columns option? Using one of the other options will create a new column in the referencing table, which is mostly useful when you're creating new structure.

You can create relationships by editing the table, going to Foreign Keys, and adding it there manually, as well.


To put it simply, indexes allow fast access to small proportions of a table. This is because they access data that is scattered throughout many block in the data segment, so unless the rows you are looking for are clustered into a small number of blocks the total cost of accessing all of those single blocks will soon become greater than just scanning a table.

At absolute best you might get benefits from an index if you are accessing 20% of a table's rows, but it's more likely that 1-5% is an effective limit.

If you want to access a greater proportion effectively, say 10% of the rows, then if you can use a partitioning scheme to isolate "queryable" groups into partitions then they can be queried very quickly. Even if you are accessing 1% of a table's rows then if the query can retrieve them from a partition that holds just those rows with a full partition scan then that will be quicker than accessing them through an index -- around 1/100th of the time to perform a full table scan (ignoring parallel query).

So if your queries very often include a predicate on the client id then partition on that -- list partitioning, I would suggest. If you also query on ranges of dates, then consider range partitioning on the time column.

So you could composite partition by range-list, or by list-range. If you want to drop old data easily then range-list could be better, but I'm not sure that there's a lot to choose between them.

The token sounds like it could be a good candidate for an index. Indexes are self-maintaining, and modifying data will not invalidate them. Obviously there's an overhead to maintaining them, but data is generally queried many time more often than it is modified so on balance I tend to "index first, ask questions later".


postgresql 9.3: two instances on same port and two different ip adresses don't work

You must also set separate unix_socket_directories.


MS Access: How to automatically fill in ID field based on value of another field

Assuming that you know how to edit and run a SQL query in Access, I think you need to execute a SQL Query similar to the following:

    UPDATE FoodPurchases
    INNER JOIN Vendors ON FoodPurchases.VendorName = Vendors.VendorName
    SET FoodPurchases.VendorId  = Vendors.VendorId

I have not verified the above, so let me know if it does not work.


Always On adding a database outside of group

That should not cause any issues at all. However, when connecting to a database outside of the AG, you will need to reference the server name instead of the AG Listener since the listener will always redirect you to the primary replica and your non-AG database will not always be on the primary replica.

Edit: Also keep in mind that actively using a secondary replica will warrant a need to license it.


What caused the database [.mdf file] to grow suddenly

This will find all of the autogrow activities in all of the errorlog files still in active sequence, from my answer here:

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   DatabaseName,
   HostName,
   ApplicationName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
-- AND DatabaseName = N'AdventureWorks'
ORDER BY StartTime DESC;

Now, once you've identified a suspect autogrowth event, you can see information like the application name and host name that caused the event. It's possible that you might capture other activity by the same SPID, but you can't rely on this. Just take a look for things that started or ended within an arbitrary window - this looks at 5 minutes before and 5 minutes after, and hard-codes the SPID observed above:

SELECT * FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE StartTime >= DATEADD(MINUTE, -5, '2018-03-19 11:41:16.970')
    AND EndTime   <  DATEADD(MINUTE,  5, '2018-03-19 11:41:16.970')
    -- AND TextData IS NOT NULL
    AND SPID = 63;

If you are rolling through 20 errorlog files per day, something is not configured correctly or you are performing way too much of something that is filling those log files with noise. IMHO.


Simplest way to log in as Oracle user different from schema user and still see all tables,... of schema?

ALTER SESSION SET CURRENT_SCHEMA

will work, but you still need to do all of the grants.

–  Phil Oct 29 at 23:33


Your query is standard SQL. Unfortunately it hasn't been implemented yet in SQL Server. You can express your support for this feature to be implemented: Microsoft Azure Feedback: Add support for ANSI standard row value constructors.

You can use the common method of transforming IN to an EXISTS subquery. Works with just one or more columns:

SELECT *
FROM tableA AS a
WHERE EXISTS
      ( SELECT 1
        FROM tableB AS b
        WHERE a.[C1] = b.[D1]
          AND a.[C2] = b.[D2]
          ---
          AND a.[Cn] = b.[Dn]
      ) ;


Can not give NT SERVICE\MSSQLSERVER permissions on network drive

NT SERVICE\MSSQLSERVER is a local service account - therefore it does not have access to network resources (like file shares)
Change to either a Network service account or a domain account using SQL Server Configuration Manager
The different service account types are described here :-
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15


How to sync two or more collocation database server?

Suggest you have multiple instances in the central server.  It could be a single installation of mysql, but separate data directories and my.cnf and Port (or use VMs).

After doing that, you can make lots of pairs of "Dual Master" setups where either Master can be written to.  And they can get into serious trouble with auto_increment keys, unique keys, and other things that plague M-M.

No, I really think, you should start over on your 'requirements'.  Currently you are asking for a maintenance nightmare.


PostgreSQL query slows due to ORDER BY even with an index

If you don't have an ORDER BY clause the database takes any 10 records that meet your criteria and returns them. With ORDER BY it first finds all rows that meet criteria and then orders them.

You could try enabling btree-gist and then creating index on the spatial column and the area

CREATE INDEX offices_st_area_idx
ON public.offices
USING gist (area, st_area(area));

But otherwise then that it's a normal question regarding performance and depends heavily on your data and server settings.


Oracle: Save PDB state for all instances in RAC environment

Depends on where you use the command.

It works on all instances from the root container:

srvctl start database -db wagon

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         2          3 PDB1       ENABLED  MOUNTED
         1          3 PDB1       ENABLED  MOUNTED

SQL> alter pluggable database pdb1 open instances=all;

Pluggable database altered.

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         1          3 PDB1       ENABLED  READ WRITE
         2          3 PDB1       ENABLED  READ WRITE

SQL> alter pluggable database pdb1 save state instances=all;

Pluggable database altered.

srvctl stop database -db wagon; srvctl start database -db wagon

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         1          3 PDB1       ENABLED  READ WRITE
         2          3 PDB1       ENABLED  READ WRITE

But it does not work on all instances when issued from the PDB itself:

SQL> alter session set container=pdb1;

Session altered.

SQL> alter pluggable database pdb1 close instances=all;

Pluggable database altered.

SQL> alter pluggable database pdb1 save state instances=all;

Pluggable database altered.

srvctl stop database -db wagon; srvctl start database -db wagon

SQL> select inst_id, con_id, name, recovery_status, open_mode from gv$pdbs where name = 'PDB1';

   INST_ID     CON_ID NAME       RECOVERY OPEN_MODE
---------- ---------- ---------- -------- ----------
         2          3 PDB1       ENABLED  READ WRITE
         1          3 PDB1       ENABLED  MOUNTED


MySql - filter bad words in multiple tables using triggers

The slangs table might have a few hundred entries. Whereas the blog_posts and image table might have a lot more data(in the range of millions depending on the traffic). So if an insert or update triggers an update in the other two tables, it might create an unwanted spike in database activity. A better approach would be to plan a Scheduler to do this task.


SQL Server in a state of confusion: does the database exist or doesn't it?

Right.. I figured it out.. some clown here (who won't own up to it) renamed the db so that its name in SSMS is different to the mdf and ldf file names.. and not just a bit different... completely different. LOL. Thanks all for your helpful suggestions anyway.


How to edit publication's generation_leveling_threshold configuration value

I think what you may be looking for is sp_changemergepublication.

This stored procedure will allow you to modify generation_leveling_threshold value.


Unable to convert a table to hypertable due to unique index error

TimescaleDB requires that unique and primary keys include the time dimension column. While only one column can be defined as the time dimension. You can read about limitations in create_hypertable doc.

Since Flask SQL Alchemy requires a primary key, you can define a compound primary key on the time dimension column, which is start in your case, and id. Based on this doc I believe it will be:

class Price(db.Model):
    __tablename__ = "prices"
    id = db.Column(db.Integer, primary_key=True)
    country_code = db.Column(db.String(2), nullable=False)
    value = db.Column(db.Float(precision=2), nullable=False)
    start = db.Column(db.DateTime(timezone=True), primary_key=True)
    end = db.Column(db.DateTime(timezone=True), nullable=False)
    price_type = db.Column(db.String(32), nullable=False)

Then you should be able to execute create_hypertable statement successfully. It should include only column start as the time dimension, i.e.:

SELECT create_hypertable('prices', 'start', chunk_time_interval=>INTERVAL '1 day')

Note that create_hypertable will also create the default index on start. Depending on your application queries you might not need this index and the primary key might be enough. In such case you can drop the index on start either by setting option create_default_indexes to false during the call to create hypertable or by executing DROP INDEX statement afterwards. The best practice section contains more information.


Subquery vs Join and where - which one is faster?

You aggregation doesn't make much sense (and most databases, and even latest versions of MySQL will reject it - See ONLY_FULL_GROUP_BY)... you probably want to GROUP BY o.user_id, (and maybe, also orders_id, but then add it also on the SELECT clause) and you can have something a bit quicker if done this way:

SELECT
    u.*, c.order_count, c.products_count
FROM
    (SELECT
         o.user_id,
         COUNT(DISTINCT o.id) AS order_count,
         COUNT(po.order_id)   AS products_count
    FROM
        orders o
        LEFT JOIN products_orders po ON po.order_id = o.id
    WHERE
        o.date >= (now() - INTERVAL 1 MONTH)
    GROUP BY
        o.user_id
    ) AS c
    JOIN users u ON u.id = c.user_id ;

The execution plan for this query is different, and uses a derived table (which MySQL or MariaDB aren't always very good at), but it will seek your users only once.

You can check out all the details at dbfiddle here


How to design a database for query builder project?

One approach you could take is to design a table that implements a stack into which can be placed the components of the logical expression in reverse-polish (postfix) notation.  In such a design, the components of the predicate are each inserted into the table with an order number to specify the reverse evaluation order.  To evaluate them a query is written to read them out by order number, apply the operators when encounter, and store the intermediate result to be applied the next.  The use of the reverse-polish notation eliminates the need for parenthesis and can support nesting of expressions to any level.

Here is an article specific to SQL Server that shows such a solution. Now in this article the author is developing a more complex example of implementing business rules to determine whether an employee is authorized to execute a particular operation.  It can easily be adapted to your use case as in both cases what is being implemented is a logical expression storage and evaluation engine.

I hope this can point you in a direction that will enable you to more easily handle the complexity that parsing nested logical expressions bring!


Is it possible to have MySQL slave outside firewall

Rather than exposing your database's ports to the public network at all, I would recommend arranging for the services to be able to see each other by more secure means.

The SSH tunnel option already mentioned will work, though for reliability you'd want to look into making sure that the tunnel remained active after a network blip or other event (look into autossh, or hooking your SSH tunnel into init/systemd).

I would instead recommend a "proper" VNP solution. OpenVPN is common (available in just about all Linux distributions' standard repositories) and easy enough to setup (IMO), though there are many other options. If you setup a point-to-point VPN between the two hosts (or a more general arrangement if more machines need to take part) then the database instances will be able to connect to each other securely over that without needing to be exposed to the outside world.


InnoDB, What would cause: "db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL"

You have 6 indexes

  PRIMARY KEY (`UID`),
  KEY `FK_miii_data_miif_mapping` (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`),
  KEY `FK_miii_data_miif` (`MODEL_INTEGRATION_IMPORT_FIELD_UID`),
  KEY `FK_MIIIData_MIIInstance` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`IMPORT_PAGE_NUMBER`),
  KEY `i_ticker` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`TICKER_CODE`),
  KEY `TICKER_CODE_INDEX` (`TICKER_CODE`),

The PRIMARY KEY is in the gen_clust_index (aka Clustered Index). All secondary index entries include a corresponding PRIMARY KEY entry.

I would mysqldump that table and reload it into a test DB server

Next, I would run CHECKSUM TABLE db1.tableABC; or mysqlchk against db1.tableABC in production and the test DB.

If the checksum values match, you should be OK.

If they do not match or you are not sure, run this on the production server

ALTER TABLE db1.tableABC ENGINE=InnoDB;

This will rebuild the table and its indexes.

If that error ever materializes after this, there may be a data dictionary problem inside ibdata1. Your final solution would be to dump all databases, shutdown mysql, delete ibdata1, ib_logfile0, ib_logfile1, start mysql, reload all data.

I posted this InnoDB Cleanup Process in StackOverflow back on Oct 29, 2010


Multi master replication with different database

I assume you have M1 -> M2 -> M3 -> M1  ?

On M1, have the tables in yahoo be ENGINE=BLACKHOLE.


Redshift DISTSTYLE ALL table takes less disk space?

Well, after lots of experimentation, we have found that, yes, if you have a fat table with not enough rows to fill much of a block at the slice level using DISTSTYLE KEY, it is much more efficient, space-wise, to use DISTSTYLE ALL, even though this replicates more data.

For example, our fat tables have, say, 600000 rows and 1000 columns. Then, on a 5-node 32-slice setup, they were using up 1Mb per column per node per slice, or 160 Gb, and we were only filling less than 1% of that space.

DISTSTYLE ALL appears to not use all the slices.

So using DISTSTYLE ALL, we multiplied the data by 5, but this still takes up less than 1/20th of the space that the DISTSTYLE KEY tables did.

It slows down our queries a little (due to the need for IPC between the slices, and because the DISTSTYLE KEY means we have only the data on the node that matches our other data) but, since these tables are loaded once and never change after, we don't suffer from the other problems of DISTSTYLE ALL.


Connect analysis services to oracle database

How can I make SSAS use the tnsnames.ora file that I need?

The best answer is don't bother.  Use the Easy Connect naming method, and bypass the TNSNAMES.ORA file entirely.

Alternatively you can set the TNS_ADMIN environment variable to the folder containing your TSNNAMES.ORA file and reboot your server for the SSAS service to see get the changed environment.


RETURN isn't a valid command in a rule definition; you can only use SELECT, INSERT, UPDATE, DELETE, and NOTIFY.

You can create an ALSO rule to return the value of the new row, something like this:

> CREATE RULE return_data AS ON INSERT TO FOO DO ALSO SELECT NEW.*;
CREATE RULE

> INSERT INTO foo VALUES (1, 'test');
 a |  b
---+------
 1 | test
(1 row)

INSERT 0 1

Note that you can do something identical by just adding the RETURNING clause to the INSERT statement:

> INSERT INTO foo VALUES (2, 'test 123') RETURNING *;
 a |    b
---+----------
 2 | test 123
(1 row)

INSERT 0 1


Can anyone explain TokuDBs seemingly large amount of data_free for an append only table?

I've seen tokutek guys explain elsewhere that table status figures such as data_length etc are basically rough estimates by the tokudb engine, and they are sometimes off by large amounts, so I wouldn't read too much into it.


How do I use "OLD" or "NEW" in a dynamic SQL statement in an on-update trigger?

After a lot of trial and error I've finally figured it out. The trick was the notation for composite data types (and more importantly their usage) which eventually led me to this:

CREATE OR REPLACE FUNCTION versioned_update()
RETURNS TRIGGER AS $$
DECLARE
     sql TEXT;
BEGIN
    sql := 'INSERT INTO backup_table VALUES $1.*';
    EXECUTE sql USING OLD;
END;
$$ language 'plpgsql';


Add aggregated value filtered by a condition to existing query

Sounds like you want the aggregate FILTER clause:
SELECT time_bucket('1 day'::interval, c.dataora) AS dataora
     , c.codicestazione
     , c.idcomune
     , count(c.id) AS totale_conferimenti
     , sum(c.peso) AS totale_peso_conferimenti
     , sum(c.peso) FILTER (WHERE tiporifiuto = 0) AS totale_peso_conferimento_tipo
FROM   conferimenti c
GROUP  BY 1, c.codicestazione, c.idcomune;
See:

Aggregate columns with additional (distinct) filters
How to count multiple values of a column as one group?


Use jsonb array inside where IN or ANY in PostgreSQL

You can do it like this:

SELECT *
FROM products
WHERE EXISTS (
         SELECT 1 FROM shipment
         WHERE products.data @>
               jsonb_build_object('shipments', ARRAY[shipment.id])
      );

This query is still complicated and not very efficient.

The reason for that is the terrible data model. JSON in the database can be useful, but just stuffing all your data into a JSON column is a certain recipe for complicated and slow queries.

You should implement this with a junction table that implements the m-to-n relationship between the tables.

My rule of thumb is that you should use JSON in the database only for columns that are not used a lot inside the database.


What is the different between compression on the PK vs. on the Table?

It's not a matter of putting compression on a primary key, but instead a matter of putting the compression on the clustered index. For SQL Server, a clustered index is organizing the table's physical structure on that index. Or, in shorter form, the clustered index is the table. This means that compressing the clustered index and compressing the table are functionally equivalent. If you were to create your primary key as a non-clustered index and keep the base table as a heap, these two structures would be different and compressed separately.


High number of undistributed commands after a "An existing connection was forcibly closed by the remote host" event

It's almost certainly a network/connectivity issue, even if the jobs don't fail at the same time.

Error 14152 is logged when "Agent shuts down after unsuccessfully retrying an operation (agent encounters an error such as server not available, deadlock, connection failure, or time-out failure).", and if the last error was "existing connection was forcibly closed" that suggests there were also earlier tries with the same result even if not logged.

There are many reasons this could be happening intermittently. One specific reason can be multiple NICs in the distributor having the same IP assigned. It could also be the network connection is simply overloaded at times.


What causes disk space growth from Informatica load onto SQL Server?

Check some of the following:

-Free space of DB in prod before backup.  You might just have lots of free space in prod pre-reserved.  It used to be a best practice before IFI was introduced.  You'll still want to pre-allocate.

-Transaction Log size post recovery but pre-shrink by running DBCC SQLPERF (LOGSPACE) both times.  Maybe the transaction log doesn't have open transactions post restore and you are now checkpoint/shrinking it.  This would mean you might not be taking prod tlog backups and your recovery model is FULL.

-List the sizes for all of your tables in order of size post recovery and then against after shrink, then compare.


To shrink an .ibd file it's enough to run ALTER TABLE t1 ENGINE INNODB. It will rebuild the tablespace and the new one will be the most compact. I think OPTIMIZE TABLE does exactly the same internally. ALTER TABLE however is preferred because you can use pt-online-schema-change to avoid blocking the table.

To get better understanding how data use the tablespace I would recommend InnoDB tools https://github.com/jeremycole/innodb_ruby

For example, to check how many pages are user by PRIMARY secondary indexes and how many free pages:

# /usr/local/bin/innodb_space -f actor.ibd space-extents-illustrate

  Start Page ╭────────────────────────────────────────────────────────────────╮
           0 │███▄▂░░                                                         │
             ╰────────────────────────────────────────────────────────────────╯

Legend (█ = 1 page):
  Page Type                                                         Pages    Ratio
  █ System                                                              3   42.86%
  █ Index 15                                                            1   14.29%
  █ Index 16                                                            1   14.29%
  ░ Free space                                                          2   28.57%


In MariaDB (which is a better MySQL) there is a sequence storage engine that can be used to generate sequences:

CREATE TABLE foo
AS
SELECT seq AS a, seq%2*seq AS b
FROM seq_1_to_3000000;


Postgres not using index when expression in WHERE condition

The plan is determined before the query is executed, and such a choice cannot be made at execution time in PostgreSQL.

I see two ways:

execute two queries and use the result of the first in the second
add a second "cut off" condition like

AND t.timestamp > '2020-01-01'

that won't change the result, but let the optimizer know that an index scan will be the right choice


A node can only be in one cluster at a time.  If Node3 is in CLUSTER-1, you have to remove it in order to add it to CLUSTER-2.

If you want to use Node3 with availability groups in both projects, then all five servers will need to be in the same cluster.

There are other options that would allow you to use Node3 for DR purposes without having all 5 nodes in one cluster.  For instance, you could perhaps use log shipping to replicate the databases on CLUSTER-2 to Node3.  More detail on the requirements would be needed for someone to be able to suggest what would be best.


ERROR: check constraint is violated by some row

You have NULLs that violate the CHECK CONSTRAINT.

To verify that this is indeed the problem, run this SQL.

SELECT * FROM integrations.accounts WHERE qb_settings IS NOT NULL OR
xero_settings IS NOT NULL OR... (fill in the fields that correspond to
those in the CONSTRAINT).

This will give you all the records that have fields which have a NULL value which violate the CONSTRAINT.

From your SQL, you have 59 such records - that's why you're getting the error.

What you need to do to rectify this is to run SQL similar to the following:

UPDATE My_Table SET My_Field = 0 WHERE My_Field IS NULL.

Obviously it is up to you to ensure that the values that you set correspond to your system's business logic and other system requirements.


How to grant the USAGE privilege on all types?

We can do this with dynamic sql and psql, Sub out user1 and user2 with the name of the user. Feel free to delete either. You may also want only DOMAIN or USER-DEFINED TYPE adapting this should be pretty simple.

SELECT format(
  $$GRANT USAGE ON TYPE %s TO %s;$$,
  format('%I.%I.%I', object_catalog, object_schema, object_name ),
  grantUser
)
FROM information_schema.element_types
CROSS JOIN (VALUES ('user1'),('user2')) AS t(grantUser)
WHERE object_type IN ('USER-DEFINED TYPE', 'DOMAIN');

Then run

\gexec

You can also run this in a do block with PERFORM


how to change the publication settings via T-SQL?

From my local ssms, I connect to the server where the publications are and run the following command to change the publication and set the snapshot folder to a different location:

the script below also marks that subscription for reinitialization and starts the snapshot agent.

use [my_database_publisher]
EXEC sp_changepublication
@publication = 'My_Publication_Pub', --Enter your publication name
@property = 'alt_snapshot_folder' ,
@value = 'G:\Backup\ReplData',
@force_invalidate_snapshot=1
GO
--Cannot make the change because a snapshot is already generated.
--Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

--================================================================================================================================================
-- Marks the subscription for reinitialization. This stored procedure is executed at the Publisher for push subscriptions.
-- https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-reinitsubscription-transact-sql?view=sql-server-2017
-- Execute at the Publisher to reinitialize the push subscription.
--================================================================================================================================================
EXEC sp_reinitsubscription
     @publication = N'my_publication_Pub',
     @subscriber = N'my_subscriber',
     @destination_db = N'ORCA_Repl_Sub', --the destination database on the subscriber server
     @article ='one_or_all_of_the_articles' --you can specify the name of one article or put `all` for all articles
GO

--========================================================================
-- Start the Snapshot Agent job.
--========================================================================
use [ORCASTG_CA18_Repl]
EXEC sp_startpublication_snapshot
     @publication = N'my_publication_Pub',
go


Hashed indexes were added in MongoDB 2.4 (March 2013) specifically to support hashed shard keys. I'm not aware of any use case outside of sharding.

When choosing a shard key you can generally get the best outcome (appropriate balance of read/write locality) by defining your own compound index. However, an effective shard key requires an understanding of the attributes of your chosen field(s) (eg. cardinality, divisibility, randomness) as well as your application use case (common update/read queries). The field(s) included in your shard key index also need to be present in every document and cannot be changed after insertion.

Where there is no natural choice of shard key based on your data, a hashed shard key can be used to achieve a more uniform distribution of values which will help distribute writes across multiple shards. The field being hashed still needs to have good cardinality (i.e. large number of different values), so ObjectId values or timestamps work well. The downside of a hashed shard key is that it supports equality queries, but cannot be used for range queries since the values in the index are effectively distributed.

For example, the default _id (ObjectId) includes a leading timestamp component. While the _id field is unique, immutable, and present in every document .. an ObjectId is not a suitable choice for a shard key because the values are monotonically increasing. If you shard on an ever-increasing value like an ObjectId, all of the new inserts will target a single "hot" shard that currently has the highest shard key value. Documents will then have to be re-balanced to your other shards, which renders your sharding ineffective for scaling -- a poorly sharded collection will be limited by the write throughput of a single shard plus the overhead of frequent re-balancing of newly inserted data between shards.

For more information, see: Shard a Collection Using a Hashed Shard Key.


How to make a unique column with default value of UUID

CREATE TABLE TblUID
(
    ID INT NOT NULL,
    UID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
)


What are the performance differences of inserting a bunch of data with one INSERT versus a bunch of INSERTs in a transaction?

Using a transaction eliminates the most expensive operation, the transaction log flush, but other per-statement operations can still make the multi-row insert more efficient than single-row inserts.

In SQL Server a multi-row insert may modify indexes more efficiently, and insert rows more efficiently, as the locking and latching necessary for the operation taken on a statement-by-statement, rather than a row-by-row basis.  And there is some overhead for running each statement, as TSQL is (by default) an interpreted language.

Also referential integrity constraints are checked and triggers fired after each statement, not at the end of a transaction.  So there could even be a scenario where inserting multiple rows in a single statement behaves differently than using multiple statements.  EG

use tempdb
go

drop table if exists t
create table t(id int primary key, tid int not null)
insert into t(id,tid) values (1,2),(2,1)


How can I pass parameter to stored procedure through a query?

You can pass third parameter value as

CALL test(@vote_value, @score, (select u.id from Users u), 'WEEK');

Please put appropriate WHERE clause in above inline query.


How do I find the FamilyGUID of an exsting database

You can use the catalog view - sys.database_recovery_status

SELECT DB_NAME(database_id) as DatabaseName, database_guid, family_guid
FROM master.sys.database_recovery_status

You can make your restore process more sophisticated by comparing the  family_guid of the backup file and the database to be restored.

Just dump the info of RESTORE headeronly into a temp table and use above query to compare. If they are different then there is a problem.


Problem getting zips in radius via MySQL

From Indexing and Querying Spatial Data in Oracle in the Oracle® Spatial Developer's Guide 11g Release 2 (11.2):

  Querying Spatial Data

  Spatial uses a two-tier query model with primary and secondary filter operations to resolve spatial queries and spatial joins. The term two-tier indicates that two distinct operations are performed to resolve queries. If both operations are performed, the exact result set is returned.

  You cannot append a database link (dblink) name to the name of a spatial table in a query if a spatial index is defined on that table.

  Spatial Query

  In a spatial R-tree index, each geometry is represented by its minimum bounding rectangle (MBR). Consider the following layer containing several objects in Figure1. Each object is labeled with its geometry name (geom_1 for the line string, geom_2 for the four-sided polygon, geom_3 for the triangular polygon, and geom_4 for the ellipse), and the MBR around each object is represented by a dashed line.

  Description of "Figure1 Geometries with MBRs"

  A typical spatial query is to request all objects that lie within a query window, that is, a defined fence or window. A dynamic query window refers to a rectangular area that is not defined in the database, but that must be defined before it is used. Figure2 shows the same geometries as in Figure1, but adds a query window represented by the heavy dotted-line box.

  Description of "Figure2 Layer with a Query Window"

  In Figure 2, the query window covers parts of geometries geom_1 and geom_2, as well as part of the MBR for geom_3 but none of the actual geom_3 geometry. The query window does not cover any part of the geom_4 geometry or its MBR.

  Primary Filter Operator

  The SDO_FILTER operator, implements the primary filter portion of the two-step process involved in the Oracle Spatial query processing model. The primary filter uses the index data to determine only if a set of candidate object pairs may interact. Specifically, the primary filter checks to see if the MBRs of the candidate objects interact, not whether the objects themselves interact. The SDO_FILTER operator syntax is as follows:

SDO_FILTER(geometry1 SDO_GEOMETRY, geometry2 SDO_GEOMETRY, param VARCHAR2)

  In the preceding syntax:

  geometry1 is a column of type SDO_GEOMETRY in a table. This column must be spatially indexed.
  geometry2 is an object of type SDO_GEOMETRY. This object may or may not come from a table. If it comes from a table, it may or may not be spatially indexed.
  param is an optional string of type VARCHAR2. It can specify either or both of the min_resolution and max_resolution keywords.

  The following examples perform a primary filter operation only (with no secondary filter operation). They will return all the geometries shown in Figure2 that have an MBR that interacts with the query window. The result of the following examples are geometries geom_1, geom_2, and geom_3.

  Example1 performs a primary filter operation without inserting the query window into a table. The window will be indexed in memory and performance will be very good.

  Example1 Primary Filter with a Temporary Query Window

SELECT A.Feature_ID FROM TARGET A  WHERE sdo_filter(A.shape, SDO_geometry(2003,NULL,NULL,
                                       SDO_elem_info_array(1,1003,3),
                                       SDO_ordinate_array(x1,y1, x2,y2))
                           ) = 'TRUE';

  In Example1, (x1,y1) and (x2,y2) are the lower-left and upper-right corners of the query window.


Update can't see records just committed by another connection

Without knowing more (like the query execution plan, number of rows affected, indexing, etc.) it will be hard to provide a concise answer. But REPEATABLE_READ raises a flag, at least for me.

Transaction isolation levels are a matter of give-and-take. None of them are "the best"; rather, you choose one that's a "best fit". Each one comes with a cost. If you want high consistency, you get less concurrency.

I like this post:
https://www.percona.com/blog/2012/08/28/differences-between-read-committed-and-repeatable-read-transaction-isolation-levels/

This excerpt from the post sums it up nicely:

  In REPEATABLE READ every lock acquired during a transaction is held
  for the duration of the transaction.

  In READ COMMITTED the locks that did not match the scan are released
  after the STATEMENT completes.


Reduce query time for higher offset in sql server

Answer based on comments originally left by Dan Guzman:

OFFSET isn't magic; time will be progressively slower with higher offsets. Also, you should have a separate index for each column to be ordered but SQL Server can read each either forward or backward so you don't need additional permutations for descending order.

Instead of row-number pagination, you could use key pagination instead, passing the last retrieved values of the ordered column and primary key. The query could then specify `SELECT TOP(n) WHERE '. That would allow users to scroll forward (and backwards with similar logic). If you must do row number pagination, do that in a caching layer.

See Optimising Server-Side Paging - Part I and T-SQL Querying: TOP and OFFSET-FETCH (sample chapter) by Itzik Ben-Gan.

An anchor filter is required to avoid a progressively larger scan, which is costly to return a page far into the result with a large paginated result like yours. I've used that method with multi-billion row tables with sub-second response time, albeit that was back in the SQL 2000 days and I don't think users ever scrolled to the end.


Postgres - window function rank and count

Your issue appears to be that you are applying the same WINDOW (named w) for both your COUNT(*) and your rank().

When you use a WINDOW which contains an ORDER BY clause, and you then apply certain aggregations such as SUM or COUNT, it applies the aggregation continuously across the ordering, which is why your COUNT and rank() are identical.

If you modify your query have multiple windows as

SELECT compUserId, rank, totalUsers
FROM (
    SELECT cu.competition_user_id as compUserId, cu.user_id as userId,
    count(*) OVER (PARTITION BY cu.competition_id) as totalUsers,
    rank() OVER (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC) as rank
    FROM competition_users cu
    LEFT JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id
    LEFT JOIN competition_user_sessions cus ON cus.competition_user_session_id = ccs.competition_user_session_id
    WHERE cu.left_competition = false
    AND cu.competition_id in (:compIds)
) as sub
WHERE compUserId in (:compUserIds);

so that you are only applying the PARTITION BY to your COUNT(*) window, and have both PARTITION BY and ORDER BY clauses for your rank(), I believe you'll get the results you want.

Refer to this SQL FIDDLE as a reference, where I have a generic id field, a com_num field to represent the competition id, and a com_time field to represent a competitors time.


How to set up a transactional replication to an Always On Availability Group (AG) with two replicas through connecting to the AG Listener

I want to describe the whole process of setting up the replication that worked for me. I hope it is useful for you too :)

If you create a Publication using GUI of SQL Server Management Studio, you don't need to run scripts mentioned in the steps 1-5 and can quickly jump to step 6. However, you can use the following scripts, too.

Introduce the Distributor. The following query is run on the     Distributor that can be the same as the Publisher. The Distributor
must be a SQL instance that doesn't currently belong to the
Availability Group and won't be part of the Availability Group in
the future.

USE master;
GO
EXEC sys.sp_adddistributor
@distributor = 'WIN-ABCDE123F', -- IP address doesn't work
@password = 'pa$$w0rd'; -- an arbitrary password

Create the distribution database at the Distributor. Run the
following commands on the Distributor. The @security_mode parameter
is used to determine how the publisher validation stored procedure
that is run from the replication agents, connects to the SQL
Listener. If set to 1 Windows authentication is used to connect to
the SQL Listener. If set to 0, SQL Server authentication is used. If so,
@login and @password parameters must be provided.

USE master;
GO
EXEC sys.sp_adddistributiondb
@database = 'distribution',
@security_mode = 1;

Add the Publisher to the Distributor. Most of the times, the Publisher is the same as the Distributor. Run the following commands on the Distributor.

USE master;
GO
EXEC sys.sp_adddistpublisher
@publisher = 'WIN-ABCDE123F',
@distribution_db = 'distribution',
@working_directory = '\\WIN-ABCDE123F\WorkingDir',
@security_mode = 1;

@working_directory is a UNC path. For more information about UNC click here. Its default value is ReplData folder for the instance of SQL Server, for example C:\Program Files\Microsoft SQL Server\MSSQL\MSSQ.1\ReplData.
Add the Distributor to the Publisher. If the Publisher was different from the Distributor, you must have run the following commands on the Publisher. If not, it is the same as step 1 command and there is no need to run it again.

USE master;
GO
exec sys.sp_adddistributor
@distributor = 'WIN-ABCDE123F',
@password = 'pa$$w0rd';

Create a publication on the Publisher. You can do it using GUI or some scripts.
Set snapshot security account. Create a Windows user account for snapshot security agent, say repl_snapshot. Grant required access to it using SQL Server Management Studio GUI. Then open Replication\Local Publications folder. Right click on the publication and select Properties. From “Select page” panel, select Agent Security, click on Security Settings, select “Run under the following Windows account” and in "Process Account" box type: 'WIN-ABCDE123F\repl_snapshot'. Enter its password and confirm it. You can also use the Administrator account but it is not recommended regarding security issues.
Run the following script on the Publisher to create a subscription and add
subscription agent to the publisher.

use [publication_db]
EXEC sp_addsubscription @publication = N'PUBLICATION_NAME',
   @subscriber = N'SQL_Listener_Name', -- IP address doesn't work
   @destination_db = N'db_on_subscriber',
   @subscription_type = N'Push',
   @sync_type = N'automatic', @article = N'all',
   @update_mode = N'read only', @subscriber_type = 0;
GO

EXEC sp_addpushsubscription_agent @publication = N'PUBLICATION_NAME',
   @subscriber = N'SQL_Listener_Name', -- IP address doesn't work
   @subscriber_db = N'db_on_subscriber',
   @subscriber_login='SQL_LOGIN_SQL_LISTENER',
   @subscriber_password='PASSWORD', @subscriber_security_mode = 0;
GO

You can see the subscription has been created just on the SQL Listener, not nodes (replicas).
Before running the replication, on the publisher, go to Replication/Local Publications. Select 'Validate Subscriptions...', select the subscription and confirm it. Again go to Replication/Local Publications. Click on View Snapshot Agent Status. On the dialog opened, click on Start and wait until the process finishes. If all things went well, you could start the replication. Go to Replication/Local Publications. Right click on the subscription and select View Synchronization Status. Click Start and wait until the process finishes.


IN SQL Server 2012 Enterprise edition, you can use AlwaysON Availability groups to achieve what you want.

If you have 2 servers set up, then you can configure AlwaysON.

Primary server will be your main and secondary (Replica) - when configured as "Readable" will allow you to redirect all "Read Intent" requests to secondary replica as well as the secondary replica is a read-only replica.

Note that, your clients are connected to the Availability Group using Listener, which allows seamless failover for your application.

In case of disaster you have all your concerns covered

  A. Not have boxes sitting around doing nothing waiting for a failure.

The secondary will be used for read intent requests and will server as a standby server in case the primary fails.

  B. Automatic fail over. We don't want to be running like headless chickens flipping switches and configuration settings when a database / box fails

When you use synchronous mode, it will allow you for automatic failover. In my testing, asynchronous mode (without automatic failover - manual failover)  has proven more benefits in terms of performance. SO depending on the data loss allowed by your company and the downtime, you can choose between them.

AlwaysON allows you to Configure Flexible Failover Policy to Control Conditions for Automatic Failover

  C. Sensible fail-over times (e.g. a five minute fail over is not sustainable)

AlwaysON will provide failover in terms of seconds, but it depends on your n/w.

EDIT :

If you implement AlwaysON (which fits in your situation), below is my recommendation:

Assuming (from your post) DB1 is the main database and DB2 is for reporting.

Configure DB1 as primary in AlwaysON. You get a warm standby database on the secondary server (say DB1_Replica).

Configure DB1_Replica as readable and have asynchronous-Commit mode configured.

  Under asynchronous-commit mode, the secondary replica never becomes synchronized with the primary replica. Though a given secondary database might catch up to the corresponding primary database, any secondary database could lag behind at any point. Asynchronous-commit mode can be useful in a disaster-recovery scenario in which the primary replica and the secondary replica are separated by a significant distance and where you do not want small errors to impact the primary replica or in or situations where performance is more important than synchronized data protection. Furthermore, since the primary replica does not wait for acknowledgements from the secondary replica, problems on the secondary replica never impact the primary replica.

The listener is going to help you with seamless failover.

Refer to below links to get you started :

Failover and Failover Modes (AlwaysOn Availability Groups)
Before You Fail Over a SQL Server AlwaysOn Availability Group - By Brent Ozar
WhitePaper : Offloading Read-Only Workloads to Secondary Replicas
Configure SQL Server 2012 AlwaysOn Availability Groups Read-Only Routing using T-SQL


Creating multiple windows login on a single databae

Test your Windows logins with SQL Server Management Studio

At a very basic level Let's test your logins with SQL Server Management Studio. You need to first log into the Windows machine with the login you created, and then login to SQL Server Management Studio with that same Windows login.

Once you've logged in through Windows, open SQL Server Management Studio and register SQL Server via the connect button Shown in Figure A. Here you register your SQL Server specifically for that login. Next, in Figure B you can connect specifically to SQL Server with the Windows login. The Connect To Server dialog box will automatically have the Windows login information provided that you've selected Windows authentication--but you may need to type the SQL Server name correctly.

Figure A: Register SQL Server for your Windows login

Figure B: Test Windows authentication on SQL Server with your Windows login

p.s. With SQL logins, you're saved the hassle of logging in and out of Windows to test those logins. But I guess you could take a shortcut, hold down the shift key and right-click the SQL Server Studio and select Run as different user--to shortcut the Windows login/logout process.

I've noticed that Run as different user selectively shows up in some places and not in others. For example, in Windows Server 2012's search menu, shift-right clicking SQL Server Studio will not reveal this command, but if I pin Studio to my task bar and shift-right click it's there.

Your question is kind of general (as there are numerous ways to login to SQL Server with Windows authentication)--I hope this explanation is a value to you and is what you needed.


How to mimic a full outer join using mysql views?

Simulation of FULL OUTER JOIN:

( SELECT ... FROM a LEFT JOIN b ON ... ) -- Intersection, plus rest of b
UNION ALL  -- There will be no overlap; ALL is faster
( SELECT ... FROM b LEFT JOIN a ON ... WHERE a.id IS NULL ) -- Just rest of a

(The link given is less efficient because it gets the Intersection twice, then dedups by doing UNION DISTINCT.)

If you need a FULL OUTER JOIN between more than 2 tables, then it gets messier.  (But you did not ask for that.)


Create SQL job to send email in Excel format

tl;dr I don't think what you want is an option with the tools you have available.

I do not believe it is possible with SQL 2005, to actually have your data export as .xls or .xlsx format.

Over the years I have tried with several different tools to do just this.  I did not have the option to select different tools.

In anycase, for most window devices opening a .csv file will open it in Excel.  Normally the .csv file is physically smaller then it would be as .xls or .xlsx, so there are some benefits to keeping it .csv.

Once you have the .csv file you can open it with Excel and save it as .xls or .xlsx.  I think that is your only option.

Update This is outside the parameters of the question but worthy of mention: There is a PowerShell module dfinke/ImportExcel that will allow you to convert table data to Excel even if you don't have Excel on the machine, and put in a folder.  I saw this in a demo of Dbatools and it looks really interesting.


As jkavalik suggested in his comment, I tried UNION and it worked great. The performance improvement was astronomical.

SELECT
  COUNT(a.nid) AS cnt, a.nid, a.title
FROM
  node a /* Airport */
JOIN
  (
    (SELECT
      dep.nid
    FROM
      departure_field dep
    JOIN
      node f
    ON
      f.nid = dep.flight_node_id
    WHERE
      f.type = 'flight' AND f.uid = 100
    )
  UNION ALL
    (SELECT
      arr.nid
    FROM
      arrival_field are
    JOIN
      node f
    ON
      f.nid = arr.arrival_node_id
    WHERE
      f.type = 'flight' AND f.uid = 100
    )
  ) airport
ON
  airport.id = a.nid
GROUP BY
  a.nid
ORDER BY
  a.cnt DESC

Note that in my case UNION ALL was the key, because it includes duplicates from both arrivals and departures. And this was important because I wanted to count how much times each airport was visited. Without ALL each airport would show up only once as simple UNION removes duplicates.


My favourite way of achieving this is the OUTPUT clause. Here is an example:

SET NOCOUNT ON;

-- example setup for the queue table
DECLARE @queue TABLE (
    transaction_id int PRIMARY KEY,
    processed bit
);

-- some sample data
INSERT INTO @queue
VALUES
    (1,0),
    (2,0),
    (3,0),
    (4,0),
    (5,0);

Now the processing thread:

-- processing thread
WHILE 1 = 1
BEGIN
    DECLARE @transaction_id int;

    DECLARE @row_to_process TABLE (
        transaction_id int
    );

    BEGIN TRAN;

    BEGIN TRY

        DELETE FROM @row_to_process;

        UPDATE Q
        SET processed = 1
        OUTPUT inserted.transaction_id
            INTO @row_to_process
        FROM (
            SELECT TOP(1) transaction_id, processed
            FROM @queue
            WHERE processed = 0
            ORDER BY transaction_id
        ) AS Q;

        IF @@ROWCOUNT = 0
        BEGIN
            COMMIT;
            -- sleep for 5 seconds then restart the loop
            WAITFOR DELAY '00:00:05';
            CONTINUE;
        END

        SELECT @transaction_id = transaction_id
        FROM @row_to_process;

        IF @transaction_id IS NOT NULL
        BEGIN
            RAISERROR(N'processing transaction N. %d.',1,1, @transaction_id) WITH NOWAIT;
            EXEC whatever_processes_the_row @transaction_id;
        END

        COMMIT;

    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH
END

If you're marking the row as processed by deleting it, it works in the same way: the only thing you have to do is change the UPDATE statement into a DELETE statement and pull the current @transaction_id from the DELETED logical table.


cannot insert multiple commands into a prepared statement

You can do that in a single query using a writeable CTE:

with updated as (
   UPDATE bankdetails
       SET bank_details = 'mysore',
           "PAN"= 123,
            bank_acc = 456,
            "UAN" = 789,
            tax = 'myy'
  WHERE bankdetails.user_id = 79
  returning *
)
INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
SELECT 79,'mysore',123,456,789,'myy'
WHERE NOT EXISTS (SELECT * FROM updated);

The insert will only be executed if the update did not change a row.

You can even avoid writing the values twice:

with data (user_id, bank_details, pan, bank_acc, uan, tax) as (
  values (79, 'mysore', 123, 456, 789, 'myy')
), updated as (
   UPDATE bankdetails
       SET bank_details = d.bank_details,
           "PAN"= d.pan,
            bank_acc = d.bank_acc,
            "UAN" = d.uan,
            tax = d.tax
  FROM data d
  WHERE user_id = d.user_id
  returning *
)
INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
SELECT user_id, bank_details, pan, bank_acc, uan, tax
FROM data
WHERE NOT EXISTS (SELECT * FROM updated);

I don't know which programming language you are using, but for e.g. a JDBC PreparedStatement you can replace the data in the values(..) part with parameter placeholder:

with data (user_id, bank_details, pan, bank_acc, uan, tax) as (
  values (?, ?, ?, ?, ?, ?)
)
...


Can I restore a MySQL table from a file (non-sql) backup?

Your best solution is going to be to install MySQL, stop the service, then completely replace the entire "datadir" with the one from the backup, then cross your fingers before restarting the MySQL service.

The datadir is probably the folder you found in xamppfiles/var/mysql; that sounds right but I don't use XAMPP to know for sure. You can open up the command line client and issue the command SHOW VARIABLES LIKE "%datadir%"; to confirm the proper path.

You can't simply restore the parts of what are missing alongside the existing structure, because you also need the information about which users have access to the tables and other metadata that isn't stored directly in the individual database folder. Restore the entire folder so you get all the correct metadata, database and table structure and data, and permissions.

This is a bit beyond the scope of what MySQL is meant to do, so success is not guaranteed (proper backups usually involve making a .sql file export, which you can do from phpMyAdmin or the command line; I use the 'mysqldump' command line client).


Yes. You can see the output of external sorts when doing EXPLAIN ANALYZE (and adding in BUFFERS and others according to the EXPLAIN documentation, so you don't really have to estimate if you can run these. Using your examples:

postgres@[local]:5432:postgres:=# create table t1
postgres-# as
postgres-# select
postgres-#   c as key
postgres-# , cast('value' as char(5)) as val
postgres-# from generate_series(1, 100000000) c;
SELECT 100000000
Time: 162609.861 ms
postgres@[local]:5432:postgres:=# SET work_mem="128MB";
SET
Time: 0.317 ms
postgres@[local]:5432:postgres:=# EXPLAIN (ANALYZE, BUFFERS) create table t_128 as select * from t1 order by key;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=13476557.48..13672503.59 rows=78378445 width=28) (actual time=38358.696..66282.785 rows=100000000 loops=1)
   Sort Key: key
   Sort Method: external merge  Disk: 1954200kB
   Buffers: shared hit=2083 read=538461 dirtied=508619, temp read=244397 written=244397
   ->  Seq Scan on t1  (cost=0.00..1324325.45 rows=78378445 width=28) (actual time=0.108..17185.459 rows=100000000 loops=1)
         Buffers: shared hit=2080 read=538461 dirtied=508619
 Planning time: 74.660 ms
 Execution time: 206961.514 ms
(8 rows)

Time: 207239.156 ms
postgres@[local]:5432:postgres:=# SET WORK_MEM = '1024MB';
SET
Time: 0.192 ms
postgres@[local]:5432:postgres:=# EXPLAIN (ANALYZE, BUFFERS) create table t_1024 as select * from t1 order by key;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=16537253.59..16787253.81 rows=100000088 width=10) (actual time=30379.785..51937.967 rows=100000000 loops=1)
   Sort Key: key
   Sort Method: external merge  Disk: 1954944kB
   Buffers: shared hit=540541, temp read=244382 written=244382
   ->  Seq Scan on t1  (cost=0.00..1540541.88 rows=100000088 width=10) (actual time=0.013..7570.784 rows=100000000 loops=1)
         Buffers: shared hit=540541
 Planning time: 0.121 ms
 Execution time: 172406.698 ms
(8 rows)

Time: 172527.526 ms
postgres@[local]:5432:postgres:=#

We can see that the sorts going on are external (on-disk), and they take up roughly 2GB. Now disk is much, much slower than memory, so there might be more of a speedup if we get it doing the whole sort in memory.

Now, if we go a really ludicrous route, we can change the sort characteristics, like this:

postgres@[local]:5432:postgres:=# SET work_mem='20GB';
SET
Time: 0.188 ms
postgres@[local]:5432:postgres:=# EXPLAIN (ANALYZE, BUFFERS) create table t_20g as select * from t1 order by key;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=14828266.59..15078266.81 rows=100000088 width=10) (actual time=20532.705..31367.756 rows=100000000 loops=1)
   Sort Key: key
   Sort Method: quicksort  Memory: 7833229kB
   Buffers: shared hit=540541
   ->  Seq Scan on t1  (cost=0.00..1540541.88 rows=100000088 width=10) (actual time=0.013..7758.434 rows=100000000 loops=1)
         Buffers: shared hit=540541
 Planning time: 0.044 ms
 Execution time: 139461.981 ms
(8 rows)

Time: 139503.860 ms
postgres@[local]:5432:postgres:=#

Which shaves 67499.533ms (or about 67.5 seconds) off the total update from the 128MB version of the sort mem, for a difference of about 32% or so. This changed from the external merge (intermediate results sorted on disk), to quicksort in memory.

The IO that is being done to execute this query is shown by adding the  BUFFERS parameter to EXPLAIN, which gives you hit (read from shared block cache), read (read from disk), dirtied (changed), and written (written to disk).

The optimal work_mem depends on a lot of factors. It's easiest to log_temp_files and use that as a starting point for adjusting the work_mem per query, and set it to something like 2-4x the size of your largest temp file. Though the setting is applied per each query (to sorts and hash tables as well), so don't make it too large unless you have to. Setting it per query on something really large like this is probably more appropriate.

tuplesort.c does actually go into detail about how it's sorted, and if you want to delve deeper than what is shown in EXPLAIN ANALYZE, then you can look at things with dtrace/systemtap/eBPF or other dynamic tracing facilities that would show you more in-depth how PostgreSQL is sorting. Or you could add a bunch of ereport lines in a custom compiled version depending on what you're interested in.

You can also get a lot more detail on work_mem effects in this post Understanding postgresql.conf : work_mem, if you're interested in additional tests and their results.


Mixing 2 and 3 node AG on a single WSFC?

None.  The settings and behavior of each availability group (AG) are completely independent of the other AGs.  When an AG is created, a separate role is created in the cluster to contain that AG's objects.  Each role acts independently and doesn't share, care about, or even know about the settings of other AGs that are on the cluster.


Table design - parent with always exactly 1 or 2 children

The second option seems a bit odd. Why do you need the GroupDetails table instead of having a regular link table with 1 or 2 rows.

Groups
----------------------------
Id INT NOT NULL PRIMARY KEY
TableAId INT NOT NULL
TableBId INT NOT NULL
Sort TINYINT NOT NULL

In any case if you don't need to support more than two links, I would choose the first option. There's nothing wrong with having a null value in a column, so that's not an issue. You also get rid of the cumbersome sort column, which for 2 values is pretty much overkill anyway.