FATAL: database does not exist in postgresql for jdbc

As a_horse_with_no_name mentioned, the database name (and possibly other parameter values) in the JDBC URL are case-sensitive. But any unquoted identifier like preopusDB in your CREATE DATABASE statement will be folded to lower case.
Use
jdbc:postgresql://localhost/preopusdb?user=...


Best approach to have a Live copy of a table in the same database

20,000/60 = 333 records per minute is not that rate where you worry about inserts, even if they are burst. Of course it depends on your hardware, but since your table is rather huge, you need something big. So I would defenitely create 1 or 2 indexes (you can omit clustered) to speed up your queries.

At a higher insert rate you probably go with partitioning and split PAGELATCH contention between partitions. Or you could use a variation of GUID clustered primary key. There is a lot of arguing here and you may want to check Tom Kejser blog post here.

Also there is no shame if you go below 3NF. People create highly denormalized data shops just for reporting purposes. Since you have enterprise edition you could use CDC (Change Data Capture) technique and create SSIS package to update reporting table, at a time you need fresh data.


Configuring pg_admin 9.6 while there are users using it on Windows Server 20XX?

I don't think that there is anything like that for PostgreSQL.

pg_ctl reload does check the syntax of the config file, but it only works with a running server.  If there is a syntax error it does report it, but it reports it to the running server's log file, not to the command line.

And if there is no syntax error, then it applies those changes which it can apply to the running server, which doesn't seem like what you want.

Some settings cannot be changed with a reload, but only a full restart.  Changings to those setting will be noted in the log file and then ignored.  Other things are called errors, but not syntax errors.  Those things will also get reported to the log file and ignored.  But these conditions won't block other changes from getting applied to the running server.

If you change "shared_buffer=1TB", that is valid syntax and valid semantics, so you wouldn't get a warning on that front if you tried to reload it.  But it also wouldn't apply the change, as shared_buffers can't be changed without a full restart.  Then if you tried to do the restart, your server would stop and then fail to start again, because your server (probably) doesn't have 1TB memory available to it.

Your comment asks specifically about adding users. That is a bit of a different topic than I had in mind.  In PostgreSQL, you can add users via SQL alone as long as they match to a pattern already existing in the pg_hba.conf.  If you do need to change the pg_hba.conf (to add hosts, or to tie specific hosts to specific users), that does require a reload, which can also be done in SQL via SELECT pg_reload_conf();.  The pg_hba.conf file is quite a bit simpler than the postgresql.conf file.  But there is still no "lint" like tool for it, that I know of. It never needs a restart to take effect (unless you change it to invoke new modules like ssl that themselves are not set up yet and need a restart to set up).  It only needs a reload.  If you introduce syntax errors to pg_hba.conf, then the reload will leave things alone, and complain to the log file.  If you were not paying attention to those complaints and issued a restart, you server would stop, and then refuse to start again due to the syntax errors in pg_hba.conf.  So good rules of thumb are to never issue a restart when a reload would suffice, and look in the log file after a reload.  Also, try to use wildcards to the extent feasible in the pg_hba.conf so that you can manager users via SQL rather than pg_hba.conf


How to select multiple id from same table

You can "join the same table multiple times" by aliasing the table in the FROM statement e.g. SELECT xyz FROM names AS n1 (where n1 is the alias for the table [names]). You can refer to the same table as many times as you want by using a different alias for the same table and it will be treated as a 'different' set of data.

You will then need to join between the various aliases to get the conditions you want such as "surnames that matches record surname".

As stated by a commenter above, the structure isn't very clear but you would end up with something like:

SELECT name, s1.surname, s2.surname from names AS n1
JOIN surnames AS s1 ON n1.mothersname = s1.surname
JOIN surnames AS s2 ON n1.fathersname = s2.surname

I'm sure the join isn't exactly this as you seem to have "intermediate" tables for name and surname (and that may be a design issue in itself) but the above should illustrate how to use the same table multiple times in a query.


Find rows containing a key in a JSONB array of records

Postgres 12 or later: with SQL/JSON path expression
SELECT *
FROM   tbl
WHERE  jdata->'array' @? '$ ? (exists (@."attr"))';

You can look for keys or values, only in the outer nesting level or recurse, abstract arrays away (unnest them) in lax mode or not (strict mode). I added query variations in the fiddle to demonstrate:
db<>fiddle here
@? is jsonpath existence operator
jsonpath value '$ ? (exists (@."attr"))' explained:
$ ... look at each value found in left operand (in default "lax" mode)
? ... run the following test
(exists (@."attr")) ... Does a key with name 'attr' exist?
Can use an index, either with default jsonb_ops:
CREATE INDEX tbl_jdata_array_idx ON tbl USING GIN ((jdata->'array'));

Or with jsonb_path_ops:
CREATE INDEX tbl_jdata_array_path_ops_idx ON tbl USING GIN ((jdata->'array') jsonb_path_ops);

See:

https://www.postgresql.org/docs/current/gin-builtin-opclasses.html
https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

Related:

How to sum the values of a json column filtered with regex?

Postgres 11 or older (no SQL/JSON, yet)
That particular use case is not covered by plain indexes for built-in operators.
Simple query without index support
SELECT *
FROM   tbl
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements(jdata->'array') elem
   WHERE  elem ? 'attr'
   );

EXISTS because we want each qualifying row once, even if multiple array elements can contain the key. And it's faster.
But this query cannot use an index.
Expression index
You can generate a text array of unique keys in the given jsonb array of records - and wrap the expression into a simple IMMUTABLE function:
CREATE OR REPLACE FUNCTION jsonb_arr_record_keys(jsonb)
  RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY (
   SELECT DISTINCT k
   FROM   jsonb_array_elements($1) elem, jsonb_object_keys(elem) k
   )';

COMMENT ON FUNCTION jsonb_arr_record_keys(jsonb) IS '
   Generates text array of unique keys in jsonb array of records.
   Fails if any array element is not a record!';

Then create a GIN expression index based on this function:
CREATE INDEX tbl_special_idx ON tbl USING gin (jsonb_arr_record_keys(jdata->'array'));

Query like this, using the generic array contains operator @>:
SELECT *
FROM   tbl
WHERE  jsonb_arr_record_keys(jdata->'array') @> '{attr}';

Now the index can be used efficiently.
Provide the key name nested in an array ('{attr}'). (You can conveniently check for multiple keys this way ('{attr1, attr2}') or similar ... )
dbfiddle here
Related:

Index for finding an element in a JSON array
How to get particular object from jsonb array in PostgreSQL?
Conditionally replace single value per row in jsonb column


Does index rebuild time depend on the fragmentation level?

For everyone interested, I have created a chart showing the index REBUILD duration of about 2500 index rebuilds within couple of weeks in relation to the fragmentation of the index and it's size in pages.

This data is based on 10 SQL Servers, hundreads of tables and on Ola Hallengren's optimizing procedures. The general threshold for rebuilding is set to 5% fragmentation.

I have cut off some of the the largest tables (10 Mi + Pages) in this statistics to make it more readable.

The chart shows the required time (duration) as size of the bubbles. The biggest bubble's values are about 220 seconds.
It shows that the required time to rebuild an index is not really related to the fragmentation. Instead it seems to be more depending on the number of pages the index has. Also it indicates that low-level fragmentation is more time-consuming than higher fragmentaion.

The second chart is just zoomed into the area <= 200 K Pages. It shows the same, it takes longer for larger indexes, not for more fragmentation.


Is tlog backup on replica clearing VLFs on the primary?

Thomas Stringer has answered a very similar question at:

Transaction Log Maintanance While Using AlwaysOn Availability Group

The answer is that the Availability Group replicas are aware of each other and when either the primary or secondary does a backup, both logs are able to free log space.

The article you referenced also offers the explanation that Log Blocks, not the VLFs, are what get hardened between the Primary and Secondary media. A VLF can contain several log blocks and this will influence when the space can be recovered.


How to get a column A which has the smaller column B grouping by column C?

Oracle solution to both problems:

SELECT ID, Category FROM (
   SELECT FIRST_VALUE(ID) OVER
      (PARTITION BY Category ORDER BY Ord) IDOfSmallestOrdForCategory
      , Category, ID FROM DATAS
   )
   WHERE ID = IDOfSmallestOrdForCategory;

Generic solution to both problems:

SELECT MIN(a.ID), a.Category FROM DATAS a
JOIN (SELECT CATEGORY, COALESCE(MIN(ORD),0) MINORD FROM DATAS GROUP BY CATEGORY) b
ON COALESCE(a.ORD,0) = b.MINORD AND a.Category = b.Category
GROUP BY a.Category;

Oracle DDL/DML:

CREATE TABLE DATAS (
    ID       Integer,
    CATEGORY Integer,
    ORD      Integer
);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 2);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (6, 3, NULL);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (7, 3, NULL);


Is there a way to synchronize mysql databases that are missing a few days worth of data

This may be your best bet:
https://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync.html

However, if you have inserted the same unique key (or AUTO_INCREMENT value) on both masters, there will be a mess.


Replicate encrypted data from Sybase ASE 15.5 to Sybase ASE 15.5

Assumptions:

you're referring to ASE's column-level encryption which requires a user/role to have decrypt permissions to see the actual data (as opposed to database-level encryption which merely encrypts the data out on disk)

ASE encrypted data is replicated as varbinary; this means that at no time is the data decrypted and/or re-encrypted; of course, it also means you need a copy of the encryption key in the RDS.

NOTE: If using a MSA or Warm Standby setup you can replicate the encryption key(s) to the RDB.

NOTE: If you have the wrong encryption key in the RDS, that varbinary(X) data will not be decryptable ("Duh, Mark!" ?).

If you're creating explicit repdefs for the table then you'll need to designate the encrypted column's datatype as varbinary(X), where X is the value you'll find in the associated (PDB) syscolumns.encrlen column.

The default function string class is already configured with a function/function-string that supports replication of encrypted data; this basically consists of issuing set ciphertext on when the DSI connects to the RDB, thus allowing that varbinary(X) data to be written directly into an encrypted column.

Permissions to write the varbinary(X) data into the RDB table should be the same as for any write operation into the table.

As for replication of encrypted data in a heterogenous environment ... I'm about 98% sure this isn't possible if simply because each RDBMS product, if it supports some sort of column-level encryption, uses different encryption methods and keys. [I'd have to peruse the latest SRS 16.x manuals if this has changed with recent versions of the product.]


Percona-Server: slow TokuDB queries after upgrading from 5.6 to 5.7. ANALYZE TABLE doesn't resolve the problem

Add this composite index:

INDEX(letter_id, user_id)  -- in either order

Do you deliberately not have a PRIMARY KEY?


Performance of large transactions and concurrency?

Are rows being updated during the transaction able to be read after the transaction starts and before it finishes?

Yes, in Postgres reads do not block writes and writes do not block reads. The Postgres documentation states that:

  Internally, data consistency is maintained
  by using a multiversion model (Multiversion Concurrency Control,
  MVCC). This means that while querying a database each transaction sees
  a snapshot of data (a database version) as it was some time ago,
  regardless of the current state of the underlying data. […] The main
  advantage of using the MVCC model of concurrency control rather than
  locking is that in MVCC locks acquired for querying (reading) data do not
  conflict with locks acquired for writing data, and so reading never
  blocks writing and writing never blocks reading.

Are rows not being updated during the transaction able to be read after the transaction starts and before it finishes?

Yes.

If another transaction starts trying to change rows that are being changed by a previously unfinished transaction, will that transaction fail at start or after it tries to commit (assuming conflict)?

This depends on the Transaction Isolation Level and if you are issuing a write that depends on a read or a blind write. Using the default level Read Committed the second transaction has to wait until the first transaction is done writing. In higher transaction levels one of the transactions might get aborted with a serialization error.

You can actually try this yourself by running two psql sessions:

Session 1:

-- first set up a table
CREATE TABLE tools (id SERIAL PRIMARY KEY, description STRING);
INSERT INTO tools(description) VALUES('scredriver');
INSERT INTO tools(description) VALUES('hammer');

-- now type the following into two psql sessions
          SESSION 1             |          SESSION 2
                                |
BEGIN TRANSACTION;              |
UPDATE tools                    |
   SET description = 'anvil'    |
 WHERE id = 1;                  |
                                | BEGIN TRANSACTION;
                                |UPDATE tools
                                |   SET description = 'wrench'
                                | WHERE id = 1;
                                |-- this transaction is blocked
                                |-- until the other transaction
COMMIT TRANSACTION;             |-- commits

As you will see, session 2 will get blocked by session 1. Only if session 1 commits, session 2 will be able to continue.

The postgres documentation also contains performance suggestions on how to avoid blocking and serialization failures when using higher transaction levels.


SQL Server 2016 with high tempdb usage and low RAM usage

My understanding is that tempdb is only used when the server is under memory pressure

That's not exactly true.  There are lots of things that can cause tempdb usage:

the most obvious would be explicitly writing data to local or global temporary tables (#tempTableName or ##tempTableName).  I imagine this is not your problem though
version store activity (for instance because of queries using snapshot isolation or RCSI)
there are a few execution plan operators that can "spill" to tempdb

It's likely that your problem is spills.  For some good general information, and links to specific troubleshooting steps, check out this answer from Paul White to the question What frequency of hash/sort spills into tempdb is concerning?

You could also run sp_BlitzCache @SortOrder = 'spills' (from the First Responder Kit) to find some of the most problematic queries if they are still in your plan cache.

As far as dealing with the spills, you could post separate questions about the bad queries and probably get help on resolving whatever is causing the spills (bad cardinality estimates, small memory grants, etc).

Memory pressure can lead to low memory grants, which can lead to tempdb spills - which is why I said your initial assertion about memory pressure is not exactly true - because it is true sometimes!

The execution plan you provided is an estimated execution plan, so it doesn't indicate if any operators spilled to tempdb.  But it does have multiple memory-consuming operators (sorts in particular), so it has the potential to generate tempdb activity.


>Product    Value    Date
>---------- -------- --------
>A          123      Jan-99
>A          123      Feb-99
>A          123      Mar-99
>A          123      Apr-99
>A          123      May-99
>A          456      Jun-99
>E          456      Jan-99
>E          456      Feb-99
>E          654      Apr-99
>E          987      Apr-99
>E          873      Apr-99
>E          987      May-99
>E          456      Jun-99
>Z          852      Jan-99
>Z          852      Feb-99
>Z          852      Mar-99
>Z          963      Apr-99
>Z          852      Apr-99
>Z          852      Jun-99

For this data set I would try and use a function like ROW_NUMBER() to partition the data into blocks that I would like to capture and then filter.

For changing values I would try something like

SELECT *
  FROM(
    SELECT * ,
           ROW_NUMBER( )OVER( PARTITION BY t.Product ,
                                           t.[Value] ORDER BY t.Product )AS  RowNumber
           FROM dbo.Table_1 t ) AS Result
  WHERE RowNumber = 1;

 Product    Value   Date    RowNumber
 A          123     Jan-99  1
 A          456     Jun-99  1
 E          456     Jan-99  1
 E          654     Apr-99  1
 E          873     Apr-99  1
 E          987     Apr-99  1
 Z          852     Apr-99  1
 Z          963     Apr-99  1

This should return unique values for each product, and then you should be able to quickly see where changes have occurred.

edit: LAG would be another great way to do this as mentioned by Colin.


That's the Hit Rate since Uptime (Last MySQL Startup)

There are two things you can do to get the Last 10 Minutes

METHOD #1

Flush all Status Values, Sleep 10 min, Run Query

FLUSH STATUS;
SELECT SLEEP(600) INTO @x;
SELECT round ((P2.variable_value / P1.variable_value),4),
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

METHOD #2

Capture innodb_buffer_pool_read_requests, innodb_buffer_pool_reads, Sleep 10 minutes, Run Query with Differences in innodb_buffer_pool_read_requests and innodb_buffer_pool_reads

SELECT
    P1.variable_value,P2.variable_value
INTO
    @rqs,@rds
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_read_requests'
AND P2.variable_name = 'innodb_buffer_pool_reads';
SELECT SLEEP(600) INTO @x;
SELECT round (((P2.variable_value - @rds) / (P1.variable_value - @rqs)),4),
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1.variable_name = 'innodb_buffer_pool_read_requests'
AND P2.variable_name = 'innodb_buffer_pool_reads';

Give it a Try !!!


Case When over 2 columns return results into 1 output column

Something like this (untested) should work for you:

select
  *,
  case q20
    when '8' then 1
    when '9' then 1
    else case q46
      when '8' then 1
      when '9' then 1
      else 0
    end
  end as promoter
from ##csat_resp;

You could also write the same logic as:

case when q20 in ('8','9') or q46 in ('8','9') then 1 else 0 end

or in SQL Server 2012 and later:

IIF(Q20 IN (8, 9) OR T.Q46 IN (8, 9), 1, 0)


PostgreSQL: Running SELECT DISTINCT ON ~700m rows - how to optimize?

Thanks to ypercube's tip in the comments above, I was able to keep the time per chunk of the query constant, and so good enough for my one-off purposes. I'm now running:

SELECT DISTINCT ON (id) id, data
FROM table
WHERE id > @last_max_id
ORDER BY id
LIMIT 250000

Takes about 1 minute per query. My whole process will take about 48 hours, but that's good enough.

Could definitely optimize further with multithreading if better performance were needed.

I also noticed that using a cursor would have probably worked similarly well:

BEGIN TRANSACTION;
DECLARE my_cursor CURSOR
FOR SELECT DISTINCT ON (id)
      id, raw_data
FROM table
ORDER BY id;

FETCH 100000 FROM my_cursor;
FETCH 100000 FROM my_cursor;
FETCH 100000 FROM my_cursor;

The FETCH's ran for about 100s, 110s, and 90s respectively so I imagine that scales linearly as well.


The only way I know how to do this is to remove either the left-hand or right-hand border from the cells where it is double. A very laborious work-around and something that should be addressed as a bug.


Does the order of keys in compound Primary key make any difference?

The order of keys can make a difference depending on your access pattern.

Example...

You want to find MAX(idPackageVariant) for a singleidHolidayPackage

With idHolidayPackage as the second column, then the index will be scanned. With idHolidayPackage as the first, it can be seeked which is more efficient.

This is a specifiy MySQL problem of course. IIRCl you'll need to do this. InnoDB uses the first unique key as the clustered index which may or may not be the primary key. However, this may not be the case

CREATE  TABLE IF NOT EXISTS `wah_schema`.`HolidayPackageVariant` (
  `idHolidayPackage` INT UNSIGNED NOT NULL ,
  `idPackageVariant` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `packageType` VARCHAR(45) NOT NULL ,

  UNIQUE INDEX `idHolidayPackage_UNIQUE` (`idHolidayPackage` ASC, `idPackageVariant` ASC, `packageType` ASC) ,

  PRIMARY KEY (`idHolidayPackage`, `idPackageVariant`) ,

  -- not needed INDEX `fk_HolidayPackageVariant_HolidayPackage` (`idHolidayPackage` ASC) ,

  CONSTRAINT `fk_HolidayPackageVariant_HolidayPackage`
    FOREIGN KEY (`idHolidayPackage` )
    REFERENCES `wah_schema`.`HolidayPackage` (`idHolidayPackage` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Alternatively, you can just have idPackageVariant as the primary and clustered key for efficiency.


For only 2,000 records, one INSERT will be most performant. Inserting batches of records is generally the best way to do it (with batch sizes up to as much as roughly 100,000 typically). It shouldn't matter if you use VALUE syntax or SELECT query syntax, as long as they're in the same batch.
This article does a good job going in depth on additional INSERT optimization techniques.


Rotating a table with multiple value columns

The (2nd form of the) crosstab() function expects these columns as input:

1 row_name column
(0-n) extra columns
1 category column
1 value column

See:

Pivot on Multiple Columns using Tablefunc

Your specific difficulty is that you are trying to process 3 value columns at once (param1, param2, param3). Your input table is already "half pivoted". There are various ways to solve this. Joining three crosstab queries is probably cleanest. Demonstrating for 5 weeks:

SELECT *
FROM   crosstab(
     'SELECT id, week, param1
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct1 (id int, w1p1 int, w1p2 int, w1p3 int, w1p4 int, w1p5 int)
JOIN   crosstab(
     'SELECT id, week, param2
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct2 (id int, w2p1 int, w2p2 int, w2p3 int, w2p4 int, w2p5 int) USING (id)
JOIN   crosstab(
     'SELECT id, week, param3
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct3 (id int, w3p1 int, w3p2 int, w3p3 int, w3p4 int, w3p5 int) USING (id)

dbfiddle here

[INNER] JOIN is safe, since all instances are guaranteed to return the same week ids. Else we'd use FULL JOIN.

With over 50 weeks, you get over 150 columns. Is that really what you want?


Select distinct id with max date according to department

12c+

Oracle 12c introduces the MATCH_RECOGNIZE syntax.

It may appear cumbersome at first, but I find that it becomes drastically easier to enhance and maintain as you add information that isn't from last row only.

Code:

with data(revno,revcontent,PIC,created) as (
select '00',     'Testing Purpose',  'Smith',    date '2008-01-11' from dual union all
select '01',     'Testing Purpose',  'Windsor',  date '2008-02-01' from dual union all
select '02',     'Test',             'Thorn',    date '2008-01-05' from dual union all
select '02',     'Testing',          'Baker',    date '2008-03-01' from dual union all
select '03',     'Testing only',     'Sykes',    date '2008-01-20' from dual
)
select *
from data
  MATCH_RECOGNIZE (
    partition by revno
    order by created
    MEASURES
        last(anyrow.revcontent) as revcontent,
        last(anyrow.pic) as pic,
        last(anyrow.created) as created,
        first(anyrow.revcontent) as first_revcontent,
        first(anyrow.pic) as first_pic,
        first(anyrow.created) as first_created
    one row per match
    PATTERN (ANYROW*)
    define
      ANYROW AS 1=1
  )

Table Results

REVNO REVCONTENT      PIC     CREATED   FIRST_REVCONTENT FIRST_PIC FIRST_CREATED
----- --------------- ------- --------- ---------------- --------- -------------
00    Testing Purpose Smith   11-JAN-08 Testing Purpose  Smith     11-JAN-08
01    Testing Purpose Windsor 01-FEB-08 Testing Purpose  Windsor   01-FEB-08
02    Testing         Baker   01-MAR-08 Test             Thorn     05-JAN-08
03    Testing only    Sykes   20-JAN-08 Testing only     Sykes     20-JAN-08


Database mail in Azure VM with SQL Server installed on it

Check out this link - basically, you need to use an authenticated relay (like sendgrid) when sending from an Azure VM.

There are some exceptions:

Enterprise Agreement subscriptions don't have this restriction
Pay-As-You-Go subscriptions can submit an exemption request and if approved you will not have to use an authenticated relay. Note, this only applies to VMs. PaaS services cannot be exempted.

All other subscriptions do not have the exemption option - you must use an authenticated relay.


2 databases in sql server 2005 "Production Server"

Having a test database on a production instance sounds like a very bad idea to me, even without taking performance into account.

However, a second database on the instance does not consume resources per-se. It's the user or application activity that drives load to the instance. Obviously, you will need storage to accomodate the data and log files and that could degrade the NTFS performance if the free space on the volumes goes lower than 20%.

Other than that, buffer consumption and CPU/disk/network time depend on activity, not on the database existance alone.


What happens when InnoDB hits its tablespace autoextend max?

IMHO It is dangerous to place a max on an autoextend. A transaction's worth of data could be lost simply because there is no room to post any new data. I have seen this happen recently with a monitoring system whose ibdata2 has a max of 1024G imposed on it. All I did to fix it was bump up the max to 16384G (That was a band-aid until my employer will let me clean up its InnoDB layout [politics with outside vendors getting in the way])

My advice to you is to use the default path for the system tablespace file (a.k.a. ibdata1). The reason? This is the default innodb path

innodb_data_file_path = ibdata1:10M:autoextend

What does ibdata1 contain? Four(4) classes of info

Table Data Pages
Table Index Pages
Table MetaData (List of TablespaceIDs + other related info)
MVCC (to support Transaction Isolation and ACID compliance)

With innodb_file_per_table disabled by default, everything InnoDB and its grandmother lands in ibdata1. You can segregate the Table Data and Indexes Pages from ibdata1 by enabling innodb_file_per_table in /etc/my.cnf

[mysqld]
innodb_file_per_table

There is a way to shrink ibdata1 by means of reloading all MySQL Data. I have written a posts about this:

How do you remove fragmentation from InnoDB tables?
My original post in StackOverflow

Once you have performed this InnoDB Infrastructure Cleanup, you should never change the innodb_data_file_path. There is no need to create ibdata2, ibdata3, and so forth, when all Table Data and Index Pages live outside of ibdata1.


Yes it will work.
It will have some difference in performance but will it be significant or not depends on the indexes usage. Scan operation will become more expensive because an index row is wider on the leaf level. On the other hand you will have one index in your buffer pool instead of three.

I would start from investigating the indexes current usage to make a reasonable decision.

Check user_seeks, user_scans, user_lookups columns:

    SELECT i.name, s.*,si.*
    FROM sys.dm_db_index_usage_stats s
        JOIN sys.indexes i ON i.object_id = s.object_id
            AND i.index_id = s.index_id
        JOIN sys.sysindexes si ON si.id = i.object_id
            AND si.indid = i.index_id
    WHERE s.object_id = OBJECT_ID('YourTableName')
        AND s.database_id = DB_ID()
    ORDER BY i.name

Check query plans for a particular index:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @IndexName AS NVARCHAR(128) = 'IX1';

SET @IndexName = QUOTENAME(@IndexName);

-- Dig into the plan cache and find all plans using this index
;WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
DB_NAME(x.dbid),
x.ObjectName,
stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text,
obj.value('(@Database)[1]', 'varchar(128)') AS DatabaseName,
obj.value('(@Schema)[1]', 'varchar(128)') AS SchemaName,
obj.value('(@Table)[1]', 'varchar(128)') AS TableName,
obj.value('(@Index)[1]', 'varchar(128)') AS IndexName,
obj.value('(@IndexKind)[1]', 'varchar(128)') AS IndexKind,
x.plan_handle,
x.query_plan
FROM (
    SELECT try_CONVERT(XML, qp.query_plan) AS query_plan, cp.plan_handle, OBJECT_NAME(qp.objectid, qp.dbid) AS ObjectName, qp.objectid, qp.dbid
    FROM sys.dm_exec_cached_plans AS cp
        JOIN sys.dm_exec_query_stats s ON s.plan_handle = cp.plan_handle
        cross apply sys.dm_exec_text_query_plan (s.plan_handle, s.statement_start_offset, s.statement_end_offset) AS qp
) x
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements') AS batch(stmt)
CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]') AS idx(obj)
OPTION(MAXDOP 1, RECOMPILE);


Is a two column primary key the most performant way to go if (a,b) should be unique but I will only be querying against a?

If you define the primary key as (a,b), then the index will be used when handling queries using a OR a AND b, but not b alone.

If you define the primary key as (b,a), then the index would be used when handling b, b AND a, but not a alone.


SQL Server refuses to connect from time to time

There are several possible issues, check one by one:

Do you have permission to access the database using the credentials that you are supplying? Maybe user don't have the necessary permissions to access the server
User credentials are ok, but user doesn't have the right to connect to the given database, you should grant him all rights needed
And last but not least, maybe there isn't a database which correspond to the information you specified, check that the database you are trying to connect exists

If everything fails, check server error logs and client event log too to get some more information.


What is x?  The missing alias on the subquery?

Get rid of the subquery:

SELECT *
    FROM ...
    JOIN ...  ON ...
    JOIN ...  ON ...
    WHERE ...
    ORDER BY ...   -- Belongs after WHERE and before LIMIT
    LIMIT ...;     -- Useless without ORDER BY

Problem:  Since you are specifying a single value for id_CAT, it does not make sense to ORDER BY id_CAT.  Perhaps you want to ORDER BY id_CAT, something_else?  Or simply  ORDER BY something_else?


Installing SQL Server Express in parallel of Standard

You are on  a totally wrong path here. Seriously.

  So i suspect either SQL SErver is corrupted or windows is corrupted (other ideas?).

Why? To be THAT corrupt (2 seconds to 45 minutes) would basically mean not running. Event log full of errors?

Check performance counters. I would say totally overloaded disc subsystem or too little memory - a real normal debug situation, nothing else. Installing Express won't solve anything.

To answer your question.

Yes, it is possible. It is another instance with a predefined name (SQLExpress).

But you are much better on to sit down and do beginner baseline analysis - performance counters etc. SQL Server pretty much can not be THAT currupt as you indicate and still work. I think that is a purely performance issue and should be dealt with it - logically, not with blind actionism.


How to backup and restore a database to a different location and name via command line only

The WITH MOVE 'logical_file_name_in_backup' refers to the internal name of that file, not the database name.

Run this to see what I mean. You're looking at the name column

SELECT * FROM sys.database_files

Edit:

Need WITH REPLACE now... see first link above and look for

  The database name is different from the database name recorded in the backup set.


Parsing out date_entered field to capture TIME 'hh:mm:ss' based on time range

It sounds like you want

WHERE date_entered BETWEEN sysdate - interval '2' hour
                       AND sysdate

Assuming date_entered never contains future dates

WHERE date_entered >= sysdate - interval '2' hour

Depending on exactly what you need (and why you're running the query), you may want to truncate the date to the hour or minute so that you're always comparing against data from the top of the hour

WHERE date_entered >= trunc(sysdate - interval '2' hour, 'HH24')

Depending on why you are doing this, however, keep in mind that you may miss data if you're running every 2 hours.  If I insert a row at, say 3:59:59 pm, your job starts runs at 4:00:00 pm (looking for data between 2 and 4) and at 6 pm (looking for data between 4 and 6), and I commit at 4:00:01 pm, then neither execution of the job will return this row.


SQL Error (126): Incorrect key file for table '.\mysql\user.MYI'; try to repair it

When using MyISAM, messages like that usually occur after a power failure (or other crash of mysqld).

USE mysql;
CHECK TABLE user;
REPAIR TABLE user;


How do I get the some of the values from 2 foreign keys in a table?

This is a good start:

select S.FIRST_NAME,    C.CLASS_NAME,    C.DURATION_OF_CLASS
FROM STUDENTS S
INNER JOIN PAYMENTS_PER_STUDENT A ON S.STUDENT_ID=PAYMENTS_PER_STU_ID
INNER JOIN CLASSES_R C ON A .CLASSES_CLASS_ID = C.CLASS_ID;

I found it + here.


Method or Counter to track dropped connections in SQL Server?

To give information on the performance counter side...

The only performance counters I know of that might help would be the ODBC counters for disconnects. SQL Server has a counter for killed connections but those are connections killed by SQL Server, not the client.

You would have to capture these at the client which you could use typeperf command to do this remotely. SimpleTalk has a good article about using this command.

The comment suggestion would be the more human way of doing things.


Auto increment char varying column prefixed with some characters

PostgreSQL does not have "auto-increment" fields in the sense of MySQL's AUTO_INCREMENT, but I'm guessing you mean SERIAL.

If so, yes, what you describe is possible, but please, please don't do this.

A SERIAL is just shorthand for a CREATE SEQUENCE and a default value. e.g.

CREATE TABLE blah(
   id serial primary key
);

is actually shorthand for:

CREATE SEQUENCE blah_id_seq;

CREATE TABLE blah (
    id integer primary key default nextval('blah_id_seq'::regclass)
);

ALTER SEQUENCE blah_id_seq OWNED BY blah.id;

You can use this knowledge to cast the nextval return to a character type, e.g.

CREATE TABLE thisiswrong (
    id text primary key default CAST(nextval('thisiswrong_id_seq'::regclass) AS text)
);

but again, I beg of you, do not do this. It's horrible. It's wrong. You will regret it. Whatever you are trying to do, there is a better way to do it than this.

I have intentionally not shown how to generate a formatted field like PAT000001, but you can use any expression in a DEFAULT, not just a CAST. So look at the to_char function or the format function for how to do this if you insist.

The correct way to do what you want to do is not to do it. Get the application to display codes like PAT000001 when it sees a primary key value for patient_id like 1. The user never needs to know that you're just storing integers.

If you want other prefixes like PAT, DOC, etc, use a composite primary key, e.g.

CREATE TABLE saner_ish (
    categorycode varchar(3) CHECK (length(categorycode) = 3),
    patient_id integer default nextval('saner_ish_patient_id'::regclass),
    PRIMARY KEY (categorycode, patient_id)
);

(possibly with an enum type, an IN list, or whatever for validation).

Then in the application turn PAT00001 into ('PAT', 1) for queries, and reverse it for display to the user.


Security and Performance Implications of SQL Tuning Advisor

As long as the developers only have that role in the development database instance, I see no problem; far better to tune now than later.  Now, if your development machine has limited resources, there may be a reason to limit access.  But in general, usually tuning tasks run from a few minutes to the default maximum of 30 minutes; during that time, Oracle may try parallel queries (if enabled), so that could be an issue, but you can play with the instance parameters parallel_adaptive_multi_user and parallel_degree_level to minimize the impact.

There are no changes between Oracle 11g and 12c that would affect your decision.


How long does it take for powershell to restore a SQL Backup?

You are setting the restore to not recover your database.

$res.NoRecovery = $TRUE

Remove this line or set this value to $FALSE and the database will restore as you expect.

This setting is useful when restoring log backups. This includes when you restore the tail of the log. Books Online has more info here

As for the duration of a RESTORE it depends on your storage and the size of your database. With that said, 80MB shouldn't take very long at all.


Execute a T-SQL job with a different connection string

I don't think there's a built-in way to do this, but here are a few alternatives:

Use OPENDATASOURCE like this:

EXEC OPENDATASOURCE('SQLNCLI', 'Data Source=MyServer;Integrated Security=SSPI;Initial Catalog=master;Application Name="test app"')
    .master.sys.sp_configure;

Note that using OPENDATASOURCE requires you to turn on the Ad hoc Distributed Queries sp_configure setting (as noted in the Remarks section of the documentation) even though you won't be writing code that accesses another server (SQL Server doesn't know that).
Create a wrapper SSIS package that specifies the application name in a Connection Manager, and then uses an Execute SQL task to run the stored procedure.
Create a wrapper command-line app that takes a connection string and a command as parameters; execute it using an Operating System (cmdexec) job step.


How to control blocking sessions while an index maintenance job runs?

Not really controlling solution but you may try new index rebuild options :

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql?view=sql-server-2017

::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION =  [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

this way you may control behavior of maintenance operation if it is blocking other processes.


Replication doesn't care about or interact with clustering.

Replication is set up from SQL instance to SQL instance, the physical host that the instance happens to be running on at that moment isn't relevant.

If a failover occurs, a momentary interruption in replication will be experienced as the instance goes offline, but will resume once SQL comes up on the other host.

  If yes then is it mandatory to have a 3 separate Instances for Distributor, Publisher and lastly Subscriber?

What? No.

You can have a single SQL instance be the distributor, publisher, AND subscriber if you want to. (Not common, but possible to push changes from one database to another on the same host.)

A more typical configuration is to have two SQL instances (a publisher/distributor and a subscriber).

But all of those business decisions are completely independent of clustering.


EDIT for new data type revelation...

SELECT sip_RECno,
    user1 = MAX(CASE WHEN ONAY_ID = 1 THEN CONVERT(TINYINT, ONAY) END),
    user2 = MAX(CASE WHEN ONAY_ID = 2 THEN CONVERT(TINYINT, ONAY) END),
    user3 = MAX(CASE WHEN ONAY_ID = 3 THEN CONVERT(TINYINT, ONAY) END),
    user4 = MAX(CASE WHEN ONAY_ID = 4 THEN CONVERT(TINYINT, ONAY) END)
FROM dbo.tablename
WHERE sup_RECno = 1
GROUP BY sip_RECno;


This is my best shot without seeing any table structures

SELECT
    user_runs.userID,
    user_runs.runID,
    `createdBy`,
    `username`,
    DATE_FORMAT(`dateCreated`,'%d-%m-%Y') `dateCreated`,
    COUNT(DISTINCT runFavouriteID) AS favourite,
    DATE_FORMAT(MAX(`runDate`),'%d-%m-%Y') AS `lastRan`,
    MIN(`runTime`) AS `fastestTime`
FROM
    (SELECT * FROM `user_runs` WHERE userID=4) user_runs
    INNER JOIN `runs` ON user_runs.runID = runs.runID
    INNER JOIN `users` ON users.userID = runs.createdBy
    LEFT OUTER JOIN `user_run_times` ON user_run_times.runID = user_runs.runID
    LEFT OUTER JOIN `run_favourites` ON run_favourites.runID = user_runs.runID
GROUP BY
    user_runs.runID,`createdBy`,`username`,`dateCreated`;

You will need these indexes

ALTER TABLE user_runs ADD INDEX userID_runID_ndx (userID,runID);
ALTER TABLE runs      ADD INDEX runID_createdBy_ndx (runID,createdBy);
ALTER TABLE runs      ADD INDEX createdBy_runID_ndx (createdBy,runID);


How much space is needed to reclaim space from a table in MyISAM

As far as I can tell, the documentation is not very clear on the subject.

From my experience, I'd say it always rebuilds fresh table files for data and index.

Intuitively, from what I know about MyISAM storage engine, I'd say it should be possible to run a full data file in place optimisation if and only if the rows have constant length. That is if you don't use columns with dynamic lengths (VARCHAR, TEXT, etc.).

Regarding index file, rebalancing the trees might be possible in place, but at a higher cost (duration).


Moving data from an old table into a newly created table with different field names / number of fields

It depends of your volumetry but you can use a simple INSERT SELECT statement.

You old table:

mysql> desc old_table;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| fname | varchar(255) | YES  |     | NULL    |       |
| lname | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Imagine you want to replace fname by firstname, lname by lstname and add a birthdate field.

Create your new structure:

CREATE TABLE new_table (
    id int(11),
    firstname varchar(255),
    lastname varchar(255),
    birthdate DATETIME DEFAULT NULL
);

Now, transfert the old data in the new table, note that the new fields (here Birthdate) will be empty:

INSERT INTO new_table (id, firstname, lastname) SELECT id, fname, lname FROM old_table;

It's just a generic example, if you want for specific one, please add your tables structures in your question.

Max.

Edit for question "At what point would it be a good idea to do something else? ":
The INSERT SELECT statement locks (write lock) your table to enforce data consitency during operation. I can't answered to the question "at what point" it depends of your server, configs... But imagine your have a table with 1 Million rows, the operation could take some seconds, we'll say 4 seconds for example, so for 4 seconds, your users will not write in your table.


If both documents are in the same collection, then yes you can - using the $or operator.

db.collection.find({
  $or: [
    {doc_type:'item', item_id : 1001},
    {doc_type:'company', name: "Acer"}
  ]
});

That will return both documents:

{ doc_type : 'company',
  name : 'Acer',
  type : 'Laptops',
  helpline : '1800-200-000'
}
{ doc_type : "item",
  item_id : 1001,
  price : 2000,
  discount : 20}
}

However, if you have two different types of record then it is probably best to store them in different collections.


Although the question  Why Does the Transaction Log Keep Growing or Run Out of Space? provides lots of information on transaction log management, it does not mention the main performance problem of Virtual Log Files, which I will address below.

Since your question is about managing size and performance of the transaction log, I'll write about each subject individually.

The transaction log records all transactions and database modifications made by each transaction, and several maintenance actions have an affect on the space requirements of the transaction log. When managing the size of the transaction log, you will need to schedule all maintenance activities with an understanding of how they will affect growth and how they won't. Maintenance activities include transaction log backups, updating statistics, rebuilding indexes and other regularly scheduled activities. Your transaction log contains active virtual log files and inactive virtual log files in on single physical file. Active log files contain transactions that have not been backed up and inactive log files are available for reuse and are mainly the free space in the log file. Generally, when pages in the database change, those changes are recorded in the transaction log. During an operation like rebuilding all indexes, pages will change at a rapid pace and could prompt the database engine to autogrow the log file. Autogrowths actions are recorded in the default trace. I've experienced rapid growth due to index rebuilding, but I had the space to accommodate so I didn't make any changes. You could increase the frequency of the transaction log backups during index rebuilds, assuming you use full recovery model, and distribute the index rebuilds over time so that the growth is mitigated.

I've experienced poor performance in log files to the point where some of the applications developed ineternally were slowing noticibly and timing out. It was due to excessive number of small virtual log files. The size and number of virtual log files contined in one physical file is directly related to the autogrowth settings. To check if you have excessive VLFs, run DBCC Loginfo. If the resulting number of records is in the hundreds or thousands, then perform the following procedure:

Change the autogrowth settings to something reasonable. It's difficult to make a recommendation in your case because an 800GB log file is highly unusual for a 96GB database. It needs to be a value that is large enough so that the database engine only creates a minimal amount of VLFs. I would try 500MB to 1GB.
Shrink the file as small as possible
Reinitialize the file. Usually this step is to reinitialize the file back to it's original size. If you have no idea what size to reinitialize it, try 50GB.

Allowing it to grow without reinitializing it will allow the VLF issue to occur again and contributes to higher IO because each autogrowth of the log file needs to be completely written out with zeros before it can be used. This is why shrinking the log file causes poor performance.


Grant User permission to view XE results via sys.fn_xe_file_target_read_file

I have managed to do this now thanks to this article the missing piece of the puzzle was to copy the certificate to the master database

DECLARE @Cert NVARCHAR(4000) =
         CONVERT(NVARCHAR(4000),
                 CERTENCODED(CERT_ID(N'CodeSigningCertificate')),
                 1);

EXEC (N'USE [master];
CREATE CERTIFICATE [CodeSigningCertificate]
FROM BINARY = ' + @Cert);


Schannell: Certificate received from the remote server was issued by an untrusted certificate authority

You're getting those errors because something is trying to connect to SQL Server via SSL, and you don't have a real (signed) certificate trusted by all parties involved.

By setting "Force Encryption" to Yes, you won't make the errors go away - actually, the opposite. You'll make the errors happen everywhere, because you're not fixing the root problem. Right now, only a few things are trying to connect via SSL, but you're about to make EVERYTHING connect via SSL, which means everything will throw those errors. Bad idea.

Instead, track down what's trying to connect via SSL. If it's happening at the same date/time every time, you can set up a trace, or figure out what servers are running batch jobs. Then, THAT server is the one that needs a real cert (as well as the SQL Server) - or just change that application's settings so that it's not trying to connect with SSL.


The password is not supposed to be pg_md5 encoded in the .pcppass file, only in the pcp.conf file.


Changing char(10) to char(3) grew table even after cleantable and shrinkfile

This has most likely to do with forwarded records, the new char(3) value is copied to a new allocation unit (page) leaving a forwarding pointer in its place. You can check if they exist like this.

SELECT
    OBJECT_NAME(ps.object_id) as TableName,
    i.name as IndexName,
    ps.index_type_desc,
    ps.page_count,
    ps.avg_fragmentation_in_percent,
    ps.forwarded_record_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
INNER JOIN sys.indexes AS i
    ON ps.OBJECT_ID = i.OBJECT_ID
    AND ps.index_id = i.index_id
-- WHERE forwarded_record_count > 0
   WHERE ps.object_id = object_id(<TABLENAME>)

The only way to fix this since you are on SQL Server 2005 would be to create a clustered index on the table and then to drop it. SQL 2008 would allows you to do ALTER TABLE <TABLE> REBUILD.

DBCC CLEANTABLE only cleans up variable length columns typed as varchar and nvarchar, see: DBCC CleanTable by Steve Stedman.


Seeting up a PostgreSQL database on another hard drive than my system one on OS X?

I very strongly recommend that you do not put a tablespace on an external removable drive. If the tablespaces disappears (ie: drive unplugged) you risk severe database corruption that will be difficult to recover from. You'll likely find permissions management a challenge, too.

You should generally initdb a new database cluster on the drive, then start it with pg_ctl. If you like you can change your launchd properties to auto-start the database (this will require messing with file permissions) or you can just manually start it with pg_ctl when you want to use it.

It may be easier to move the existing data directory to the new location and either create a symlink from the old location to the new one or edit your launchd configuration to tell the PostgreSQL launcher to look in the new location. If your postgresql.conf is outside the data directory you can just change the data_directory parameter in postgresql.conf instead.

See the PostgreSQL user manual for details on initdb and pg_ctl if you need to use them.

For how to set PostgreSQL up to start your new database on boot-up you'll want to do some learning about file permisisons and launchd properties. I don't use OS X much so I can't be of much help with this part; if you're stumped and you've done some reading to try to figure it out, consider asking for help on the OSX stack exchange site. Just remember to explain what you've tried and what you've done already. You won't need to do this if you just symlink it from the old location.


How to connect to a remote Oracle server from Windows machine using PL/SQL Developer?

You need to have SQL*Net installed in order to connect PL/SQL Dev to Oracle. SQL*Net is installed by default with most Oracle DB products (Oracle client for example). Since PL/SQL dev is a Windows app, you can see what Oracle products you have installed by looking into the registry (HKEY_LOCAL_MACHINE\SOFTWARE\Oracle). Also in PL/SQL dev settings (Tools/Preferences/Connection) you will have a list of all Oracle Homes (which allows you to specify which one you want to use if you have multiple homes).

If you already have an Oracle Home, the TNSNAMES.ora file is by default in the directory %ORACLE_HOME%\network\admin. You can set the registry key TNS_ADMIN if you want to specify another directory.


Preserve CASE WHEN output as new column in MySQL

ALTER TABLE dogs
ADD COLUMN Size VARCHAR(6) AS (CASE WHEN weight < 15 THEN 'Small'
                                    WHEN weight < 40 THEN 'Medium'
                                    ELSE 'Big' END) VIRTUAL

MySQL 8.0 Reference Manual  /  ...  /  CREATE TABLE and Generated Columns


Why does a wait event that occurred 5 times accounts for 1.1% of your database wait time concern you as a potential threat?  Is there some additional information that leads you to believe that this is a threat?  Obviously, something has to be in your top 5 wait events.  Is this event consistently one of the top wait events?  Or did it just appear on one AWR?

The particular wait generally occurs when one session is trying to pin a cursor while another session is parsing it.  Potentially, some of the 95% of the CPU related wait events could be the result of parsing (though you'd want to look at the number of hard parses elsewhere in the AWR).  If you have an OLTP system, you should really only be doing any quantity of hard parses immediately after a reboot-- everything else should be using prepared statements and, at most, doing a soft parse.  If you've got dynamic SQL somewhere in the system, removing that may help though it may also be a rather drastic step to save a few seconds.


SQL Server 2016 Install - R Server and R Services Install Failed

Add the SQLRUsergroup to the LogOnLocally user right assignment. You might have to create the SQLRUsergroup if it does not already exist. Ensure the logon used to execute setup is a local admin on the box.


Moving data in multiple tables from old database to one table in new database

You can use SSIS (from sql 2005 and up)to transfer data from one database.Table to anotherDatabase.Table or all tables as well.

Note: SQL Server Import and Export Wizard will give you option to save the package at the end so that you can reuse the package for subsequent data transfer.

You need to be careful with constraints on the destination database. You can temporarily disable them and then reenable then once your data load is done. Script by Aaron can be found at Temporarily disable all foreign key constraints

If the tables from source and destination have exactly same schema, then you can use bcp as well. for script refer to my answer here.


You have to understand that the underlying protocol that you're using to send dta(http://en.wikipedia.org/wiki/Transmission_Control_Protocol). The TC-Protocol is designed to deal with network breaks. Ultimately if you send a transmission to the remote server saying, for instance "insert into table (id, name) values (1, 'test');" then the TC-Protocol will handle not only delivering it, but also making sure it arrives in tact. In fact, it will even retry... but if there's a persistent network break it'll tell your application that the connection to the remote server was lost.

Thus, the answer is simply you do not need to do anything about this. TCP will make sure you don't get garbage data. TCP is the reason that you do not see garbage web-pages when you are on a shoddy connection. It also makes sure that you don't deliver garbage to your DB's.

We run about 500 million transactions a month into the DB and not one are garbage - because of TCP.


Identify unattached File Size in SQL Server

Basically if you manage the contents of the files you select from and include the file size, it should not be too hard to do.  (But there are issues.)  Here is a greatly edited script that may help you.

    -- Gather the full names of Database Files
SET NOCOUNT ON
create table #os_files(fullfilename varchar(2000));

insert into #os_files exec xp_cmdshell 'DIR D:\DATA\*.mdf /B /S'
insert into #os_files exec xp_cmdshell 'DIR D:\DATA\*.ndf /B /S'
insert into #os_files exec xp_cmdshell 'DIR F:\LOGS\*.ldf /B /S'

create table #os_files_dir(dirstring varchar(2000));

-- Gather Filename (without path) and Filesize without commas (/-C).
-- Exclude system files (/A-S) and directories (/A-D)
insert into #os_files_dir exec xp_cmdshell 'DIR D:\DATA\*.mdf /-C /TC /A-S /A-D /S';
insert into #os_files_dir exec xp_cmdshell 'DIR D:\DATA\*.ndf /-C /TC /A-S /A-D /S';
insert into #os_files_dir exec xp_cmdshell 'DIR F:\LOGS\*.ldf /-C /TC /A-S /A-D /S';

create table #os_files_dirclean(cleanstring varchar(2000));
-- Clean out non-file rows: Exclude NULL columns,
--  all the information rows that begin with a ' '
--  and handle a 'File Not Found' row, if it appears.
insert into #os_files_dirclean
select * from #os_files_dir
WHERE SUBSTRING(dirstring,1,1) LIKE '[0-9]';

-- Select the database files that need to be cleaned up.
SELECT os.fullfilename AS PHYSICAL_FILENAME,
       CAST(SUBSTRING(fd.cleanstring,21,18) AS BIGINT) AS SIZE
FROM #os_files os JOIN #os_files_dirclean fd
       ON os.fullfilename like '%' + SUBSTRING(fd.cleanstring,40,2000)
    LEFT OUTER JOIN master.sys.master_files db
       ON RTRIM(db.physical_name) LIKE '%'+ RTRIM(os.fullfilename)
WHERE db.database_id IS NULL

-- Clean up the temp tables
drop table #os_files;
drop table #os_files_dir;
drop table #os_files_dirclean;

Issues that make this a fragile solution:

In order to get the full path name, used the `DIR ... /B /S'
In order to get the file name and size, used the `DIR ... /-C /TC /A-S /A-D /S'
Since in step 2 the first column of a row with a file is the date and time of the file. Used this to filter out all rows that do not start with a numeric.
Since step 1 and step 2 return different name formants, this results in needing to join a full path name with a file name only.
A file name can appear more than once on a server, if it is in a different location. So there may be some ambiguity in the join if that case arises on one of your servers.

If you need other file types, you will need to include them also in loading the table. One SQL Server example is ".ndf" used when adding files to the database beyond the primary .mdf. I included this in the dataset.

For a server I tested with I got the following.  Note that RLFJUNK is not really a database file, it is just named like one:

PHYSICAL_NAME                       SIZE
D:\DATA\Administration_Data.MDF     524288000
D:\DATA\EventHorizon_Data.MDF       629145600
D:\DATA\RLFJUNK.mdf                 19
F:\LOGS\Administration_log.LDF      107216896
F:\LOGS\EventHorizon_Log.LDF        2202009600

Future changes to the OS or the format of the DIR command could easily disrupt the script.  Still, hope it helps you for the time being.

This is more like something that you might want to find or develop a bit of CLR code to get the directory information without so much maneuvering.


Mysql: 1000 select queries vs 1 query ( select .. in )

to get the same result, you need to UNION the result and you can't union 10000 selects. the limit is 63 table references per query
Further IN clause has also a limit of elements you can use.

The number of values in the IN() list is only limited by the max_allowed_packet value.

see manual
As you have many ids, you should try
select t1.*
from topic t1
      INNER JOIN
     (SELECT  user_id FROM topic WHERE user_id <= 1000) t2 ON t1.user_id = t2.user_id

On big numbers for the IN you will get faster results.


Subtracting from multiple CASE statements

So you want the result of the second SUM to be subtracted from the first SUM. One option would be to do just that:

SELECT TOP (100) PERCENT
  IM.IM_PROD_CODE,
  IV.IV_CUKEY,
  IM.IM_KEY,
  IM.IM_DESCR,
  Sales    = SUM(CASE WHEN IV.IV_TYPE = '1' THEN SH.SH_QTY * SH.SH_PRICE END),
  Credits  = SUM(CASE WHEN IV.IV_TYPE = '8' THEN SH.SH_QTY * PK1.SH.SH_PRICE END),
  NetSales = SUM(CASE WHEN IV.IV_TYPE = '1' THEN SH.SH_QTY * SH.SH_PRICE END)
           - SUM(CASE WHEN IV.IV_TYPE = '8' THEN SH.SH_QTY * PK1.SH.SH_PRICE END)
FROM
  … /* the rest of your query */

Yes, you are repeating the SUM expressions, but that is fine, there are only two of them. The DRY principle applicable to many other languages is less relevant in SQL, where duplication of code can be a perfectly normal way to achieve better performance.

In this case, however, the duplication can be avoided by using a derived table:

SELECT
  IM_PROD_CODE,
  IV_CUKEY,
  IM_KEY,
  IM_DESCR,
  Sales,
  Credits,
  NetSales = Sales - Credits
FROM
  (
    … /* the entirety of your current query */
  ) AS derived;

I would also like to suggest, if I may, that you be consistent in how you specify your constant literals. What I mean is sometimes in your query you are providing the matched values for IV.IV_TYPE as strings (IV.IV_TYPE = '1') and other times as numbers (IV.IV_TYPE = 1). You should really choose one way and, of course, it should be the one matching the column's actual type.

Also, the TOP (100) PERCENT in your query makes little sense. It looks as though it may be a remnant of an old technique for intermediate materialisation, which, however, may no longer be working in your version of SQL Server.

There is also this HAVING filter in your query (HAVING (IM.IM_KEY = 'A-05.000.007.01_LM')), which would work more efficiently if you moved the condition to the WHERE clause.

One last note concerns your second CASE expression (the Type 8 one). One of the columns it is referencing is qualified with an alias that is not found in your FROM clause: PK1.SH.SH_PRICE. I am assuming that is some kind of a copy-paste error posting your script here but I thought I would let you know so that you could correct it.


There was a proposal for an extension that would do part of this, but it never got finalized.  Perhaps another push could get it done.


Merging multiple databases as separate schemas in PostgreSQL

I would assume that the other databases are Postgres as well? And all of them have only one schema? If you only have to do it once, then the easiest way is to use pg_dump -Fc to export the database and then edit the resulting SQL file by some regexp script.

In detail, you will have a

SET search_path = public;

row near the top of the file.  Rewrite 'public' to the desired schema name.  Other places to check for are where a stored procedure returns a user-defined type, view or table rowtype, here you will see something similar to

CREATE OR REPLACE FUNCTION f()
RETURNS public.my_view AS
...

Rewrite 'public' in such places as well.


How do I copy the structure and contents of some tables from one database to another?

(I would have assumed there was already a question on this, but I couldn't find a general one for SQL Server, just narrowly tailored ones, or ones for other platforms.)

The method I chose would probably depend on how big the tables are, and also whether there are any other objects associated with the tables (like indexes, triggers, constraints, etc.). But here are a few methods that might work:

Use the "Import/Export Wizard".

This is probably the simplest. Right click the source database and go to Tasks > Export Data... I'm not going to walk through all the steps of the wizard here, but you can select the source and destination databases, then choose which tables you want to copy. Here's a page that walks through a simple example, although they are using it for importing data from Excel.

After copying the data, add any necessary indexes/constraints/etc.

Do a SELECT INTO

With very small tables, I'll just do a very quick:

SELECT *
INTO myDestinationDB.dbo.tablename
FROM mySourceDB.dbo.tablename

Replacing with your database names and table names, of course.

This creates the destination tables on the fly. You'll need to add indexes/constraints yourself afterwards. Also be aware that this won't always work for some limited situations like identity columns.

Script out the table definition, then do an INSERT INTO

Right-click the table in the source database, and choose "Script Table as > Create To > New Query Editor Window"

Then change the USE databasename at the top to your destination database, and run the CREATE statement to create the table. Now is your chance to also add indexes or constraints, if necessary. Then to copy the data, do something like:

INSERT INTO myDestinationDB.dbo.tablename
SELECT * FROM mySourceDB.dbo.tablename

If these tables have millions and millions of rows, definitely use the 1st method above, or use an advanced technique like a custom SSIS package (not covered in this answer), otherwise you might fill up your transaction log.

Hope this leads you in the right direction. Good luck.


Merging two 'tables' (function outputs) 1 to 1

Just assign a unique index in both tables, then use that for the join:

SELECT order_details, product_id, quantity, order_item_id
FROM (SELECT order_item_id, product_id, quantity,
             row_number() OVER (PARTITION BY product_id, quantity) AS indx
      FROM order_items) AS items
JOIN (SELECT order_details, product_id, quantity,
             row_number() OVER (PARTITION BY product_id, quantity) AS indx
      FROM order_details) AS details
USING (product_id, quantity, indx);


What is the difference between select and fetch time?

The benchmark you cited is using the term "select time" to mean the amount of time that elapses between when the time when the query is sent to the server for execution and the time when the first row is available for retrieval.  The "fetch time" is the difference between when the first row is available to the client and the last row has been retrieved by the client.

"Select time" then means the amount of time the server spends identifying the rows that will be returned, and "Fetch time" means the amount of time required for the server to actually deliver all of the data to the client.

In the simplest query, SELECT * FROM t1, where the server has to do almost no work in order to identify which rows should be returned (all rows would be returned, with no WHERE clause), we could expect that the select time would be almost instantaneous, while the fetch time would depend on how fast the server can read the rows from its backing store (or cache) as well as the available network bandwidth and latency between the server and the client.  In a more complicated query, where the server has to do more work to identify the rows, the select time could be much higher and the fetch time could be lower by comparison.

In summary, "Select time" is an estimate, based on observation from the client's perspective, of how fast the server can identify the rows to be returned, while "Fetch time" is how fast the server can actually deliver them once they have been identified.

The two times added together are the actual time required for your query to be fully answered.  In light of that, the only thing that really matters is the total, since that's how long you're going to have to wait until you can use the entire result set.


Cannot perform SELECT COUNT(*), rows over 1 billion on a table

Here is a script to delete in chunks. It deletes in chunks of 4500 records to avoid lock escalation. It performs 100 loops to delete a total of 450,000 rows.
If your database is using the FULL recovery model, you may want to backup the transaction log after each run to keep if from growing too large.

SET NOCOUNT ON;
DECLARE @continue INT
DECLARE @rowcount INT
DECLARE @loopCount INT
DECLARE @MaxLoops INT
DECLARE @TotalRows BIGINT
DECLARE @PurgeBeginDate DATETIME
DECLARE @PurgeEndDate DATETIME

SET @PurgeBeginDate = '2014-01-01 00:00:00'
SET @PurgeEndDate = '2014-03-31 23:59:59'

SET @MaxLoops = 100
SET @continue = 1
SET @loopCount = 0

SELECT @TotalRows = (SELECT COUNT(*) FROM audit_tb (NOLOCK) WHERE datetime between @PurgeBeginDate and @PurgeEndDate)
PRINT 'Total Rows = ' + CAST(@TotalRows AS VARCHAR(20))
PRINT ''

WHILE @continue = 1
BEGIN
    SET @loopCount = @loopCount + 1
    PRINT 'Loop # ' + CAST(@loopCount AS VARCHAR(10))
    PRINT CONVERT(VARCHAR(20), GETDATE(), 120)

    BEGIN TRANSACTION
        DELETE TOP (4500) audit_tb WHERE datetime between @PurgeBeginDate and @PurgeEndDate
        SET @rowcount = @@rowcount
    COMMIT

    PRINT 'Rows Deleted: ' + CAST(@rowcount AS VARCHAR(10))
    PRINT CONVERT(VARCHAR(20), GETDATE(), 120)
    PRINT ''

    IF @rowcount = 0 OR @loopCount >= @MaxLoops
    BEGIN
        SET @continue = 0
    END
END

SELECT @TotalRows = (SELECT COUNT(*) FROM audit_tb (NOLOCK) WHERE datetime between @PurgeBeginDate and @PurgeEndDate)
PRINT 'Total Rows Remaining = ' + CAST(@TotalRows AS VARCHAR(20))
PRINT ''

GO

I have used this in a SQL Agent job to run repeatedly until the required number of rows were removed. I just schedule the job to run every 15 minutes so my tran log backup job runs between runs of this job.
I hope this helps


How to subtract the sum of columns from different tables?

As an alternative to a full join, you could use UNION ALL to combine the two aggregated sets together, only the withdrawals set would be included with the negated results. You would then use the combined set as a derived table and aggregate the amounts per account_id once more:

SELECT
  account_id,
  SUM(amount) AS balance
FROM
  (
    SELECT
      account_id,
      SUM(amount) AS amount
    FROM
      prizes
    GROUP BY
      account_id

    UNION ALL

    SELECT
      account_id,
      -SUM(amount) AS amount
    FROM
      withdrawals
    GROUP BY
      account_id
  ) AS derived
GROUP BY
  account_id
;

I am not sure how this query would compare to the full join solution but it looks simple enough to me to qualify as a good starting point.


I'll assume LUW and a recent one. There you can use the LISTAGG aggregate function:

with Sample(region, openservices, dfc) as (
    values ('Karaci','14','4')
         , ('Lahore','13','3')
         , ('Islamabad','10','4')
), Second (region, openservices, dfc) as (
    values ('Karaci','14','4')
         , ('Lahore','10','3')
         , ('Islamabad','10','4')
)
select region, substr(listagg(openservices,','),1,10), dfc
from (
    select region, openservices, dfc
    from sample
    union
    select region, openservices, dfc
    from second
)
group by region, dfc

The union will eliminate duplicate rows and the we can use listagg to concatenate openservices for the group region, dfc. Here I assumed that dfc is functionally dependent of region (according to you sample data). Since the return type of LISTAGG is a rather long string, I trimmed it down using substr.

Result:

REGION    2          DFC
--------- ---------- ---
Islamabad 10         4
Karaci    14         4
Lahore    10,13      3


How I develop Union Report Views for Multiple Single Tenant Databases?

Code provided below:
CustomerTemplateDb contains all the tables for single tenant database.

It will list other single tenant databases around it, and create one consolidated view in reporting database.

(Feel free to code review, or make modifications as needed. Works on my end)

declare @DatabaseTemplate nvarchar(max) = 'CustomerTemplate'
declare @DatabaseDestination nvarchar(max) = 'ReportingDb'
declare @TableSchema nvarchar(max)
declare @TableName nvarchar(max)

declare @DatabaseCurrent nvarchar(max)
declare @DatabaseCounter int = 1

declare @TableListGenerateCode nvarchar(max) = ''
declare @TableCounter int = 1

declare @DropView nvarchar(255) = ''

declare @TableSelectFromCurrentDatabaseCode nvarchar(max) = ''
declare @TableSelectFromAllDatabaseCode nvarchar(max) = ''

declare @ColumnSelectFromCurrentTableCode nvarchar(max) = ''

IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL drop table #DatabaseList
create table #DatabaseList (DatabaseId int identity(1,1), DatabaseName varchar(255))

IF OBJECT_ID('tempdb..#TableList') IS NOT NULL drop table #TableList
create table #TableList (TableId int identity(1,1), TableSchema varchar(255), TableName varchar(255))

IF OBJECT_ID('tempdb..#ColumnList') IS NOT NULL drop table #ColumnList
create table #ColumnList (ColumnId int identity(1,1), ColumnName varchar(255))

insert into #DatabaseList (databasename)
select db.name
from sys.databases db
where
database_id > 4
and db_Name(database_id) <> @DatabaseTemplate and db_Name(database_id) <> 'reportingdb' and db_Name(database_id) <> 'electronics' and db_Name(database_id) <> 'mytestdb'
and state = 0;

set @TableListGenerateCode=
'
Insert into #TableList (TableSchema, TableName)
SELECT TABLE_SCHEMA, TABLE_NAME
FROM ' + @DatabaseTemplate + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' AND table_catalog = ' + '''' + @DatabaseTemplate + ''''

exec (@TableListGenerateCode)

while @TableCounter <= (select count(*) from #TableList)
begin

    set @TableSchema = (select TableSchema from #TableList where Tableid = @TableCounter)
    set @TableName = (select TableName from #TableList where Tableid = @TableCounter)
    set @DatabaseCounter = 1

    while @DatabaseCounter <= (select count(*) from #DatabaseList)
    begin

        set @DatabaseCurrent = (select DatabaseName from #DatabaseList where DatabaseId = @DatabaseCounter)

        truncate table #ColumnList
        set @ColumnSelectFromCurrentTableCode = 'insert into #ColumnList   select STUFF((
            SELECT '',
        '' + QUOTENAME(c.name)
            FROM ' + @DatabaseCurrent + '.sys.columns c where c.object_id = object_id(''' +  @DatabaseCurrent + '.' + @TableSchema + '.' + @Tablename +''')
            FOR XML PATH(''''), TYPE).value(''.'',''nvarchar(max)''),1,2,'''')'

        exec (@ColumnSelectFromCurrentTableCode)

        set @TableSelectFromCurrentDatabaseCode =
    '
    UNION ALL

    SELECT ' + (select top 1 ColumnName from #ColumnList) +
            '
    FROM ' + @DatabaseCurrent + '.' + @TableSchema + '.' +  @Tablename + CHAR(13)+CHAR(10) + '
            '
        set @TableSelectFromAllDatabaseCode = @TableSelectFromCurrentDatabaseCode + @TableSelectFromAllDatabaseCode

        set @DatabaseCounter = @DatabaseCounter + 1

    end

        set @TableSelectFromAllDatabaseCode = N' EXEC ' + @DatabaseDestination + '.sys.sp_executesql ' + 'N''Create View ' + @Tablename + 'Vw as
        ' + RIGHT(@TableSelectFromAllDatabaseCode, LEN(@TableSelectFromAllDatabaseCode) - 12)+''''

        PRINT @TableSelectFromAllDatabaseCode

        --PRINT ('Create View ' + @Tablename + 'Vw as
        --' + RIGHT(@TableSelectFromAllDatabaseCode, LEN(@TableSelectFromAllDatabaseCode) - 13)+'''');

        set @DropView = N' EXEC ' + @DatabaseDestination + '.sys.sp_executesql ' + 'N''IF OBJECT_ID(''''' + @TableName + 'Vw'''', ''''V'''') IS NOT NULL   DROP VIEW ' + @Tablename + 'Vw' + ''''

        exec (@DropView)
        exec (@TableSelectFromAllDatabaseCode)
        set @TableCounter = @TableCounter + 1

        set @DropView = ''
        set @TableSelectFromCurrentDatabaseCode = ''
        set @TableSelectFromAllDatabaseCode = ''

end


Mysql REPLACE INTO query for Multiple rows insertion

You should avoid using triggers in conjunction with REPLACE INTO on multiple rows. Why ?

REPLACE INTO is nothing more than a mechanical DELETE and INSERT. It can incite mysqld to address deadlocks (See my answer to How I prevent deadlock occurrence in my application?)

Here are two comments that shows the LIFO approach to processing triggers

  Posted by Atif Ghaffar on September 23 2007 9:12pm    [Delete] [Edit]
  PLEASE Note that the REPLACE does a DELETE operation.

  We did not realize this and had the triggers that should be triggered
  on DELETE triggered.

  After checking all the code, we just found a script that does a
  replace to refresh the values of some fields.

  We should have had used "insert into ... on duplicate update" syntax
  instead.

  Posted by J Mike on May 4 2009 11:06pm    [Delete] [Edit] If you are
  using REPLACE INTO... triggers are fired in this order (if delete of
  duplcate key is used):

  before insert
  before delete
  after delete
  after insert

Your problem ? An UPDATE trigger will never fire off if this has to be done multiple times in a single operation. At least, I don't see that happening.

SUGGESTIONS

You may want to use INSERT ... ON DUPLICATE KEY UPDATE (See my answer to Update one table from another table while sorting that table based on one column)
Stick to doing REPLACE on one row at a time


Unable to create an user using mysql_embedded

As you are using the docker mariadb image the best way is to set the environment variables when running the image:

MYSQL_DATABASE
MYSQL_USER
MYSQL_PASSWORD

ref: https://hub.docker.com/_/mariadb/

These will case the database and user to be created with grants (ref)


Maintenance Plan folder missing in SQL Managed Instance (via VM)

Please refer to this other thread on DBA.StackExchange about how to schedule maintenance of indexes and statistics. As you can read on that thread you can use Ola Hallengren's maintenance scripts, and schedule them using SQL Agent on Azure Managed Instance.
You can use also Azure Automation to schedule any maintenance task as described on this article.


Shall I use JSONB for integers based array?

Unless you have a very good reason to do otherwise, the recommended storage is always normalised.

Store the integers in a separate table of (other_row_id, the_integer) and join on them.

If you must use arrays, use PostgreSQL's native arrays ARRAY[1,2,3].

Storing them as jsonb is a spectacularly inefficient and clumsy way to do it with essentially no advantages.


Pausing AlwaysOn Availability Group replication for failover

I think you are absolutely right on here:

  The installer creates the distributor on the publisher, which I assume
  will break replication after a failover and require generation of new
  snapshots and reinitializion of subscriptions.

a good way to relate the replication to the availability group is:

The primary is SP1 and the secondary replica is SS2.
The distributor is on another server SD3. The replication is working fine.
We redirected the Original Publisher to the AG Listener Name using this script:

USE distribution;
GO
EXEC sys.sp_redirect_publisher
    @original_publisher = 'MyPublisher',
    @publisher_db = 'MyPublishedDB',
    @redirected_publisher = 'MyAGListenerName';

You can find more details on this link:

Replication log reader not updated to new primary after availability group failover

In an AlwaysOn availability group a secondary database cannot be a publisher.

The failover of a distributor on an availability database is not supported.

I would suggest you use another server as a distributor, as you are probably already doing.

I normally use another server as a distributor and several distributor databases depending on the size and how busy your publications are.

I am not entirely sure about the push subscriptions but for the other stuff
you could have a look at this link:

Setting up Replication on a database that is part of an AlwaysOn Availability Group

  SRV1: Original Publisher

  SRV2: Publisher Replica

  SRV3: Publisher Replica

  SRV4: Distributor and Subscriber (You can choose a completely new
  server to be the distributor as well, however do not have a
  distributor on any of the publishers in this case as the failover of a
  distributor is not supported in this case).

maybe this will not apply on your case, but just for reference:

Configure Replication for Always On Availability Groups (SQL Server)

  Am I on the right track in saying the most reliable method of
  "pausing" replication would be to use sp_MSstopdistribution_agent on
  the publisher (for PUSH) and on subscriber (for PULL) before failover
  to the (non-distributor) node, then do a sp_MSstartdistribution_agent
  after we fail back again?

I would say in most cases yes, I used to use those procedures as you can see (scripts included) on this link:

How to restart the distributor agent of transactional replication?

Now I prefer to use the following method:

-- on the publisher
-- this gives you the distributor server name and distributor database name

sp_helpdistributor

-- now connect to the distributor server and database
-- get the job name

-- get the distributor job name for the publication you want
-- in this example my publication is called 'DEOrder'
use distribution
go

my database and publication are called ATOrder in the example below.

-- the snapshot agent
select  job_name=name, publisher_db, publication from  distribution.dbo.mssnapshot_agents
where publisher_db = N'ATOrder'

-- the distributor jon transactional replication
select job_name=name, publisher_db, publication from  distribution.dbo.MSlogreader_agents
where publisher_db = N'ATOrder'

-- if it is merge replication
select  job_name=name, publisher_db, publication  from MSmerge_agents
where publisher_db = N'ATOrder'

-- who are my publishers and who are my subscribers
select * from MSsubscriber_info

-- just checking dbs
select * from MSpublisher_databases

-- get the distributor job (log reader) for a specific publication
select job_name=name, publisher_db, publication from  distribution.dbo.MSlogreader_agents
where publisher_db = N'ATOrder'

-- take note of the job name
--my_publication_server_name_and_instance2-ATOrder-25

-- check that the job is running at the moment - it must be!!
sp_runningjobs 'my_publication_server_name_and_instance2-ATOrder-25'

--check I am targeting the right job
exec msdb.dbo.sp_help_job @job_name = 'my_publication_server_name_and_instance2-ATOrder-25'

--stop the job
exec msdb.dbo.sp_stop_job @job_name = 'my_publication_server_name_and_instance2-ATOrder-25'

--start the job
exec msdb.dbo.sp_start_job @job_name = 'my_publication_server_name_and_instance2-ATOrder-25'

for more information, as to see if everything is going on alright, or who and what is getting a bit slow, you can check this link:

Replication Monitor Information using T-SQL

  We are using SQL 2014 on both publisher and subscriber.

Lucky you


I believe the below will work for you.  I made the final result return ID's rather than names so that you could see how to grab additional attributes from the base tables if necessary.
I also just realized that this only handles one level of group to parent, would need to slide in a recursive CTE after CTE_RawData to handle that scenario.  Is that something your query needs to do?  Or is one level of parent group enough?
/** The Setup - table variables, sample data **/

DECLARE @LeafChilds TABLE
    (
    ChildID INT NOT NULL PRIMARY KEY
    , ChildName NVARCHAR(100) NOT NULL
    )

INSERT INTO @LeafChilds
    (ChildID, ChildName)
VALUES (1, 'Child 1')
    , (2, 'Child 2')

DECLARE @Group TABLE
    (
    GroupID INT NOT NULL PRIMARY KEY
    , GroupName NVARCHAR(100) NOT NULL
    )

INSERT INTO @Group
    (GroupID, GroupName)
VALUES (1, 'Group 1')
    , (2, 'Group 2')
    , (3, 'ALL')

DECLARE @GroupChildMapping TABLE
    (
    GroupID INT NOT NULL    --FK to @Group (GroupID)
    , ChildID INT NOT NULL  --FK to @LeafChilds (ChildID)
    )

INSERT INTO @GroupChildMapping
    (GroupID, ChildID)
VALUES (1, 1)
    , (2, 1)
    , (2, 2)

DECLARE @GroupGroupMapping TABLE
    (
    GroupID INT NOT NULL            --FK to @Group (GroupID)
    , ParentGroupID INT NOT NULL    --FK to @Group (GroupID)
    )

INSERT INTO @GroupGroupMapping
    (GroupID, ParentGroupID)
VALUES (1, 3)
    --, (1, 3)  --DUPLICATE PAIR??

/** Get the Data
    CTE_RawData - Gets the raw data
    CTE_UnPivot - unpivots the data and formats it as desired.
    **/
;WITH CTE_RawData AS
    (
    SELECT C.ChildID
        , C.ChildName
        , GM.GroupID AS DirectGroupID
        , GI.GroupName AS DirectGroupName
        , GMP.ParentGroupID AS IndirectGroupID
        , GP.GroupName AS IndirectGroupName
    FROM @LeafChilds AS C
        LEFT OUTER JOIN @GroupChildMapping AS CM ON CM.ChildID = C.ChildID
        LEFT OUTER JOIN @GroupGroupMapping AS GM ON GM.GroupID = CM.GroupID
        LEFT OUTER JOIN @Group AS GI ON GI.GroupID = GM.GroupID
        LEFT OUTER JOIN @GroupGroupMapping AS GMP ON GMP.GroupID = CM.GroupID
        LEFT OUTER JOIN @Group AS GP ON GP.GroupID = GMP.ParentGroupID
    WHERE GM.GroupID IS NOT NULL
    )
, CTE_UnPivot AS
    (
    SELECT ChildID
        , GroupInheritanceType
        , GroupID
    FROM (SELECT ChildID, DirectGroupID, IndirectGroupID FROM CTE_RawData AS R) AS C
        UNPIVOT (GroupID FOR GroupInheritanceType IN (DirectGroupID, IndirectGroupID)) AS unpvt
    )
SELECT P.ChildID
    , L.ChildName
    , P.GroupInheritanceType
    , P.GroupID
    , G.GroupName
FROM CTE_UnPivot AS P
    LEFT OUTER JOIN @LeafChilds AS L ON L.ChildID = P.ChildID
    LEFT OUTER JOIN @Group AS G ON G.GroupID = P.GroupID


Common entities with differing attributes across multiple databases

Yes, you absolutely should not store the data across more than one system if it can be avoided.  But welcome to the nightmare many organizations have created for themselves.
Without knowing the exact circumstances of how the applications are set up, it'll be hard to give you a definitive answer, but hopefully we can get you in the right direction.
Recommendations:

This will be incredibly stressful experience, so make sure you have an exercise regimen or therapist lined up.
Now is a great time to make sure your new system for storing customer data complies with GDPR and other data privacy standards!
Store the customer data in a common database, but force the other systems to use the primary keys of that system.  You will either need to update each system's data with the new key (hard at first) or create a mapping from the source system's key to the new key (easy at first, harder as time goes on). It isn't possible to maintain strict PK-FK relations between different databases, so you'll need to ensure procedures exist to keep things in sync if records are merged, deleted, etc.
For attributes not stored by both systems (but not required to be stored in either system), you can create a table in 1:0-1 relation with the master table and store the optional column(s) there.  Depending on the exact circumstances you may need to create subtypes and handle the optional columns to ensure the proper columns are stored for each subtype.

Trying to maintain anything other than the proper keys across each system will lead to far more headaches than you have today.
Other things:

Know your industry's regulatory/reporting requirements and ensure any changes you make won't interfere.
If you don't already standardize information like addresses, you'll want to do so now to make matching easier.
People are really bad at typing consistently, so get familiar with string comparison functions (Levenshtein Distance and Regex).
If you are in a strict environment, or don't have too many records that are close matches, may make sense to have a human review any possible matches before merging records.


You have multiple choices here.

writing a crontab that will check every minutes your data (that's what you propose).
using NOTIFY/LISTEN with trigger like this :

CREATE OR REPLACE FUNCTION f_check_data_value()
  RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT pg_notify('data', NEW.value);
END;
$$;

CREATE TRIGGER check_data_values_trigger
AFTER INSERT OR UPDATE
  ON mytable
FOR EACH ROW EXECUTE PROCEDURE f_check_data_value();

You'll then have to write a listener (in PHP if it's what you are using). I haven't used php inn a while but the php.net documentation is a good place to start I think : http://php.net/manual/fr/function.pg-get-notify.php


MySQL / MariaDB : Many indexes vs. One multi-column index

There is no perfect answer.  A possibly good answer is...

First, are the columns compared with = constant?  Or a "range", such as > constant?  For now, I will assume only =.

How selective are the values?  True/false values are not; let's ignore them.

Assuming the 'worst' case (always =, all are selective), let's take the 6 permutations of two columns:

INDEX(a, b) -- for (a), (a,b), (a,b,c), (a,b,d), (a,b,c,d)
INDEX(a, c)
INDEX(a, d)
INDEX(b, c)
INDEX(b, d)
INDEX(c, d)

This list will handle all the one- and two-column tests efficiently, and at least help when you use 3 or 4 columns.

If some column is always used in a range, then never put anything after it in the INDEX.  If, for example, b is always tested via a range, change the 4th and 5th to (c,b) and (d,b)

Other tips:

You say INT.  That allows 4 billion values and takes 4 bytes.  Use a smaller datatype.  40M distinct values needs INT UNSIGNED, but if you have something with only a million distinct values, consider MEDIUMINT UNSIGNED (3 bytes, 0..16M).  Etc.  The rationale is that making the table and indexes smaller helps decrease I/O, hence improves speed.
Do you need id?  Or is some combo of the columns unique and could become the PK, there by elimination id?
If there are common patterns, extend some of the indexes.  For example, if usually when users test a and c, they also test b, then change (a, c) to (a, c, b).
Use InnoDB.
If any column is "low cardinality", then no index will be used when the WHERE clause mentions only that column; live with it.  That column, when combined with others, works fine.
Do not over-normalize.  I hope that a,b,c,d are not normalizations of floats or datetimes.  Note how this hooks into my comments about "ranges".

If you would like to discuss further, please provide some extra clues about the data and queries involved.  (Note that I had to do hand-waving about flags and ranges.)


When dropping Constraints, are their indexes dropped as well

Yes. And No. Constraints that are implemented by an index (eg. Primary key constraint and Unique constraint) will drop the index that implements them. In fact, the only way to drop the index that implements a constraint is to drop the constraint. Other constraints do not have an index to speak of, so there is nothing to drop.

You give an example of a foreign key constraint and ask if dropping the constraint will drop the index. a foreign key constraint is not implemented by an index therefore it has no index to drop. What you're tangentially hinting at is that to efficiently check a foreign key on update/on delete options, an index is required on the referencing key. It is up to you to create, drop and alter this index as appropriate to keep it aligned with the foreign key. The system will not update the index definition for you, since is unaware that the index is in any way related to your foreign key.

  Is that enough and will it work?

We really can't answer it. Will creating an index on IndustryId help the FK_CourseIndustry foreign key constraint? Yes. Will your upgrade/migration script always work, be idempotent and handle all cases? Unlikely. it depends on your current schema, on how you run your migration, etc. For instance, is not at all clear if you drop the Industry_Id column or not by the time you run the upgrade in the example.


Oracle does NOT support Ubuntu. From the documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/operating-system-checklist-for-oracle-database-installation-on-linux.html#GUID-E5C0A90E-7750-45D9-A8BC-C7319ED934F0
The following Linux x86-64 kernels are supported:

Oracle Linux 8.1 with the Unbreakable Enterprise Kernel 6:
5.4.17-2011.0.7.el8uek.x86_64 or later Oracle Linux 8 with the Red Hat Compatible kernel: 4.18.0-80.el8.x86_64 or later

Oracle Linux 7.4 with the Unbreakable Enterprise Kernel 4:
4.1.12-124.19.2.el7uek.x86_64 or later Oracle Linux 7.4 with the Unbreakable
Enterprise Kernel 5: 4.14.35-1818.1.6.el7uek.x86_64 or
later Oracle Linux 7.7 with the Unbreakable Enterprise Kernel 6:
5.4.17-2011.4.4.el7uek.x86_64 or later Oracle Linux 7.5 with the Red Hat Compatible Kernel: 3.10.0-862.11.6.el7.x86_64 or later

Red Hat Enterprise Linux 8: 4.18.0-80.el8.x86_64 or later

Red Hat Enterprise Linux 7.5: 3.10.0-862.11.6.el7.x86_64 or later

SUSE Linux Enterprise Server 15: 4.12.14-23-default or later

SUSE Linux Enterprise Server 12 SP3: 4.4.162-94.72-default or later

Ubuntu is an entirely different branch of Linux, and no matter how many people claim they have hacked it to work, it is NOT supported and results are NOT guaranteed.


Covering Index Not Being Used Effectively

Using index means that an index was "covering" and was used.  Or, at least, when the EXPLAIN was run, it decided that.
I have looked at thousands of EXPLAINs and slowlog entries.  In theory, they always match.  However, there is nothing that forces the EXPLAIN to actually match what happens and is recorded in the slowlog.
I have seen as many as 6 different EXPLAINs for a given query "digest".  But that is easily explained by different cardinality, etc.
Could you provide the full, exact, text of the EXPLAIN and the slowlog entry.  I would like to see if I can spot something.  Also, if available, please provide EXPLAIN FORMAT=JSON SELECT ...
Actually, SHOW CREATE TABLE would probably have the clue.
WHERE `hash` = 34605915

If hash is VARCHAR, then this cannot use the index.  Add quotes to the string "34605915".
Or, if hash is always numeric, then make it an integer datatype, perhaps INT UNSIGNED (32-bit number; 4-bytes).
When mixing char and int, MySQL will convert the varchar to int before comparing.  So, the one case of "varchar_col = integer-constant" cannot use the index effectively.


Dates, Times and Timezones - How are they incorporated in Interval?

Yes, if not it would cause all kinds of errors.  when doing math on a date for example 1 month ago, it does take the date to be subtract from and figures out what to do from there,  it does not just subtract 30 days.

links will answer your question
wiki interval
another site

also consider this simple query

select '03-01-2019'::date - interval '30 day',
'03-01-2019'::date -interval '1 month'


The usual rule of thumb for MySQL Query Optimizer is this:

If the EXPLAIN plan has to read more than 5% of the table rows via the index, the index is dismissed (ruled out) and a full table scan is performed.

In the case of the Slave, 156769 out of 32212292 rows are to be read. That's 0.4867%. Hence, the index is used.

With regard to the Master, it is very disturbing that the MySQL Query Optimizer ruled out indexes. As @ypercube commented, perhaps there is no index.

You should make sure by running SHOW CREATE TABLE ox_data_summary_ad_hourly\G on both Master and Slave. If they are different, please post both outputs in the question.

Also, please run SELECT COUNT(1) FROM ox_data_summary_ad_hourly; on both Master and Slave and post these counts


Postgres: Filtering large jsonb arrays coming from a sub-query

You're doing as well as can be managed with your data structure. The GIN indexes serve the same purpose as any other index - finding rows given a condition. They don't help once the row is located. If you want this to be improved, you'd need to unroll the array elements into separate rows.


Well it took a while but finaly found it myself. Besides the service account it also requires full access for the user of SSMS. Sounds obvious probably and indeed I thought about it earlier but since the account was already in the list of administrators and this group already had full access to the shared volume I thought it would have been enough. So what was required was just adding the SSMS user/group and service account to the advanced sharing of the SYNC folder.


What is the foreign key between two children in the same hierarchy?

The id of a manager that you store in the supervisor column of employee.


What is the difference between Oracle's VARCHAR and VARCHAR2 datatypes?

It would seem that Oracle at one time had plans to give a different definition to VARCHAR than to VARCHAR2.  It has told customers this and recommends against using VARCHAR.  Whatever their plans were, as of 11.2.0.2 VARCHAR is identical to VARCHAR2.  Here is what the SQL Language Reference 11g Release 2 says:

  Do not use the VARCHAR data type. Use
  the VARCHAR2 data type instead.
  Although the VARCHAR data type is
  currently synonymous with VARCHAR2,
  the VARCHAR data type is scheduled to
  be redefined as a separate data type
  used for variable-length character
  strings compared with different
  comparison semantics.

The PL/SQL User's Guide and Reference 10g Release 2 puts it this way:

  Currently, VARCHAR is synonymous with
  VARCHAR2. However, in future releases
  of PL/SQL, to accommodate emerging SQL
  standards, VARCHAR might become a
  separate datatype with different
  comparison semantics. It is a good
  idea to use VARCHAR2 rather than
  VARCHAR.

The Database Concepts 10g Release 2 document says the same thing in stronger terms:

  The VARCHAR datatype is synonymous
  with the VARCHAR2 datatype. To avoid
  possible changes in behavior, always
  use the VARCHAR2 datatype to store
  variable-length character strings.

The Oracle 9.2 and 8.1.7 documentation say essentially the same thing, so even though Oracle continually discourages the use of VARCHAR, so far they haven't done anything to change it's parity with VARCHAR2.


Oracle 11g production database copy to USB drive

To get a consistent single-file snapshot of a running Oracle instance DataPump Export can be used.

You need to have access to the server's filesystem (or a shared filesystem) in order to copy the resulting dump file.

The process would be to

run expdp (for one or multiple schemas)
copy the dump file to the USB stick
run impdp to import the dump into the development database


While I think Postgresql has had arrays since before SQL-99, the SQL-99 standard specifies 1-indexed arrays, and it's consistent with other areas in SQL where the first element is 1 instead of 0.

PostgreSQL actually has support for custom array start indexes, but I strongly advise you not to use it.