Basic trigger in PL/pgSQL, to flip a boolean field if another field has text

Your trigger function can be improved:

CREATE OR REPLACE FUNCTION trg_insup_bef_recipe()
  RETURNS TRIGGER AS
$func$
BEGIN
IF TG_TABLE_NAME = 'recipe_' THEN
   IF TG_OP IN ('INSERT', 'UPDATE') THEN
      NEW.is_baked_   := NEW.baking_instructions_ <> '';
      NEW.is_roasted_ := NEW.roasting_instructions_ <> '';
   END IF;
   RETURN NEW;
ELSE
    RAISE EXCEPTION 'This trigger is being used on the wrong table.';
END IF;
END
$func$ LANGUAGE plpgsql;

Major points

Do not use a separate code block without need. Removed the spurious BEGIN .. END.
NEW.baking_instructions_ <> '' is a 100% identical drop-in replacement for
length( NEW.baking_instructions_ ) > 0. Just shorter and faster.
The plpgsql assignment operator is :=, not =.
Removed multiple parentheses to demonstrate they are just noise.

Simplify

Given the trigger definition in your answer (which should be in your question), you can further simplify the trigger function.

If your trigger function is not intended to be generic but for a certain trigger on a certain table (which is the normal case) I suggest to reflect that in the function name instead of adding code and error messages to the function body. Much cheaper and cleaner (IMHO), but that's a matter of taste and style:

CREATE OR REPLACE FUNCTION trg_insup_bef_recipe()
  RETURNS TRIGGER AS
$func$
BEGIN
NEW.is_baked_   := NEW.baking_instructions_ <> '';
NEW.is_roasted_ := NEW.roasting_instructions_ <> '';
END
$func$ LANGUAGE plpgsql;

And since this trigger only has work to do when any of the involved columns have been changed, you only need to trigger it in those cases:

CREATE TRIGGER insup_bef_recipe
BEFORE INSERT
OR UPDATE OF is_baked_, baking_instructions_, is_roasted_, roasting_instructions_
ON recipe_
FOR EACH ROW EXECUTE PROCEDURE trg_insup_bef_recipe();

Simplify further

Do you really need those flags materialized as additional columns? Could be completely (and more reliably) replaced with simple expressions in your queries:

baking_instructions_ <> '' AS is_baked_

Then you don't need a trigger at all. You could create a view with that expression. Or even "generated field". Detailed instructions:
Store common query as column?
Computed / calculated columns in PostgreSQL


How to: Real Time deletion of relevant records from resulting mysql results?

Create for every table an AFTER DELETE TRIGGER, so you are sure you catch every deleted entry and can remove them from your result set.
The same goes of course for the inserts, then you can also use a TRIGGER to add rows to your result set.
Your query is probably slow and you hope that you don't need to run it again, when you simply add and remove all new entries since the query last ran.


how to Upload xml data to db Table by check primary key in oracle

You can try using third party tool such as Toad for Oracle. There is a function to import the .xml file into database and of course it will check the primary key before loading the data.


Oracle regexp_like square brackets inside character set

Why don`t  you dip into the manual  SQL Language Reference, Appendix D, Oracle Regular Expression Support:

[]
Bracket expression for specifying a matching list that should match any one of the expressions represented in the list. A non-matching list expression begins with a circumflex (^) and specifies a list that matches any character except for the expressions represented in the list.
To specify a right bracket (]) in the bracket expression, place it first in the list (after the initial circumflex (^), if any).
To specify a hyphen in the bracket expression, place it first in the list (after the initial circumflex (^), if any), last in the list, or as an ending range point in a range expression.


Why does Table Definitions Opened differ this much between cluster nodes?

You are writing to the 'one' node?  That is, you are treating it as the only Master?  Galera does "Row Based Replication", so it probably does not need to open the table definition on the 'Slaves'?

Do you have the same values for SHOW VARIABLES LIKE 'table%'; on all nodes?


InnoDB: Error: Table “mysql”.“innodb_table_stats” not found

The problem is actually very simple. Here is what happened

When you installed MySQL, the 5 InnoDB systems tables exist in two places

inside /var/lib/mysql/mysql as 5 .frm and 5 .ibd files
inside the data dictionary within ibdata1 (InnoDB System Tablespace)

At some point in your installation, you must have deleted ibdata1. This left the 10 InnoDB system table files inside /var/lib/mysql/mysql with no data dictionary entry.

SOLUTION

cd /var/lib/mysql/mysql
rm -f innodb_index_stats.frm
rm -f innodb_index_stats.ibd
rm -f innodb_table_stats.frm
rm -f innodb_table_stats.ibd
rm -f slave_master_info.frm
rm -f slave_master_info.ibd
rm -f slave_relay_log_info.frm
rm -f slave_relay_log_info.ibd
rm -f slave_worker_info.frm
rm -f slave_worker_info.ibd

Then, login to MySQL and run the steps from my post : InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6

GIVE IT A TRY !!!


The most important oracle database performance metrics

Your question is too broad and opinion based for this forum. There are too many permutations and combinations. Start with the Oracle docco. Don't forget OS metrics - sysstat for Linux or see here for ideas about Windows (you didn't even mention your OS).

Are you running an OLTP or an OLAP system? Different answers! Do you have a on-premise or cloud system? Different answers! What version of Oracle are you running? Different answers! How much are you prepared to pay (normally the crunch question!)? Different answers!

There are any number of both free and pay monitoring software systems/frameworks - Nagios (free) and Foglight (Quest) would probably be the best known.

Take the tour and check out this blog post - "Help us to help you". This site is a great resource and you'll get all the more out of it if you follow the guidelines. Give us a specific real-life scenario and then you should be able to get more assistance here. You could also check software recommendations.

[EDIT]

Maybe I missed the original reference to the Oracle Enterprise Manager (OEM) document. What you're really looking for is information on the Oracle Wait Interface (OWI), a complex but truly impressive piece of coding - not a system to be learnt over a weekend! You'll also want to delve into the mysteries of the Oracle Automated Work Repository (AWR) and the Automatic Database Diagnostic Monitor (ADDM) (may costo mucho bucks!).

The first thing that I would do if I were you is to get Christopher Lawson's "The Art and Science of Oracle Performance Tuning". It is a very (very! surprisingly so) readable introduction to Oracle performance tuning - he doesn't give you a "top 10" - he tells you how to look for one on your system. Then read, read and read some more (preferably till your eyes bleed! :-) ). Oracle is a huge system and, just as Rome wasn't built in a day, neither will a deep understanding of Oracle. But, if you persevere, you'll get here.


SQL Job is getting failed randomly while trying to send the alert email. need help

Pretty clearly, the mail server is sending a response to SQL Server:

  Exceeded storage allocation. The server response was: 5.3.4 Message size exceeds fixed maximum message size

The size of the email being sent by SQL Server is too large.


How to index WHERE (start_date >= '2013-12-15')

You just need to add a B-tree index to the some_date DB field. Partial indexes work, only if you know your query parameters and want to exclude / include particular ranges (http://www.postgresql.org/docs/current/static/indexes-partial.html).

Another alternative is to use table partitioning (http://www.postgresql.org/docs/current/static/ddl-partitioning.html) for the some_date DB field per day or month, so you may exclude huge number of rows easily.


changed password for postgres login in pgAdmin, now can't connect to server

I think there are two unrelated problems here:

1) failure to connect on Unix domain socket

This part of ps output:

  /opt/PostgreSQL/9.3/bin/postgres -D /home/dev/postgres_data

indicates that you're not running postgresql as packaged for Ubuntu. Ubuntu doesn't install its binaries inside /opt (it doesn't even create /opt) and your data directory /home/dev/postgres_data is non-standard too.

The usual suspect is a postgres server installed with the EntrepriseDB installer instead.

/var/run/postgresql is the default unix_socket_directory of Ubuntu's postgres, not Entreprisedb's or any other. Using the psql command-line tool shipping with Ubuntu against a non-Ubuntu leads to this error you mentioned:

  psql: could not connect to server: No such file or directory
      Is the server running locally and accepting
      connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

This mismatch is frequent on Mac OS X because there are quite a few incompatible PostgreSQL installers for it and Apple ships with a client-side postgresql that's not configured for any of them.

Short of getting rid of EntrepriseDB's postgres and installing Ubuntu's (good idea for the future but overkill for just that problem), you need to locate your unix_socket_directory, typically by consulting your postgresql.conf, which should be inside the data directory: /home/dev/postgres_data according to ps.

Then give it as the -h argument to psql, this will be tried instead of /var/run/postgresql. Per the manual page:

 -h hostname, --host=hostname
           Specifies the host name of the machine on which the server is
           running. If the value begins with a slash, it is used as the
           directory for the Unix-domain socket.

2) bad postgres password

If you successfully changed a db password with pgAdmin but can't use it, it may be that you're experiencing a known pgAdmin bug.

See PostgreSQL user can not connect to server after changing password


How can I represent a relationship of cardinality many to many?

You could have a Course table which would just contain the courses and descriptions.

As stated in the comments, you could then have a StudentCourse table which had either a surrogate PK, with foreign keys to Student and Course or a composite PK (StudentNumber and CourseNumber) with the Grade for the student held in it.

Similarly, you could have an Office table and link the Office back to the Advisor.  This would allow multiple advisors to have the same office without having to hold the office name multiple times (what if the office name changed for example?).

As also mentioned using the Advisor name as the primary key might not be best, their name could change; you might be better to use a Surrogate key (integer perhaps?) for that.


MariaDB slave replication stopped after slave upgrade to 10.1.41

Looks like this issue:
https://jira.mariadb.org/browse/MDEV-20247

Body must be 30 characters.


Oracle SQL Loader - load file where record is composed of "n" delimiters

So the linefeed before peas and dhdhjsk should be skipped but the linefeed before save is part of the data. Which linefeeds should be skipped and which are part of the data? I think such a format is very error prone and should be avoided. Perhaps you can do it the following way but I did not verify this

define "," as the physical record separator
join three physical records to on logical records
to skip a leading \n trim leading whitespace from fields  or use sql-functions

The following links to the manual can provide information:

Concatenating physical records to logical records

Input formats

Whitespace

Apply SQL functions

Specifying delimiters


Optimizing a slow query to better use indexing

Your query seems to be simply this:

SELECT time_stop, some_count
    FROM foo
    WHERE user_id = 1
    AND time_start >= '2016-07-27 00:00:00'
    AND time_stop <= '2016-07-27 23:59:59'
 ORDER BY some_count DESC LIMIT 1;

You need an index on (user_id,time_start) -- in that order -- and, assuming start is always earlier than stop, adding the seemingly-unnecessary AND time_start <= '2016-07-27 23:59:59' will allow the database to avoid even considering rows with time_start values later.

Also adding time_stop to the same index will not help you, for the same reason that the telephone directory won't help you find all people with first names beginning with the letter L.  Adding some_count to the index also won't help, for the same reason.  In a multi-column index, the values in column B are sorted within identical groups of column A (hence the telephone directory reference). The values in column C are sorted within identical groups of B, etc... so if A is an equality comparison, and B is a range, as in your query, those are easy to pinpoint... but the values of C appear random.  For C to be usable in an index, B needs also to be an equality comparison, which it is not, in your case.

However, if these are the only columns you're reading, then adding those columns to the index (to the right of user_id and time_start) could be useful for a different reason -- when a query uses an index that contains all the columns selected, it's a "covering index," and the entire result can be read directly from the index, instead of the main table, potentially giving a benefit that you have to weigh against the cost of maintaining larger indexes.


Check your Windows Services for a service named "SQL Server Integration Services 12.0".

If you find you need to add just run the SQL Installer and choose "Add features to existing instance" and mark the option "Integration Services"


Wich solutions is better for translate some data?

You should only store your data in one language and handle the translating of text on the application side, for a multitude of reasons:

Different languages use different characters which may affect the data type and collation of how you store the translated data. This can affect you from a flexibility standpoint and potentially a performance standpoint (depending on the size of your data) and what collations you're using elsewhere.

Different languages have different dialects which means there's more rules to how you store the translated words. For example, Spanish spoken in parts of Spain may vary from how it's spoken in Brazil or Mexico.

Per #2, there may be different words in each of those dialects for the corresponding English word, and you'll then need to store more translations than anticipated.

Even within the same dialect of a language, a word may translate differently depending on the context it's used it. Building out a relational model to manage all the contexts of a given word sounds like a nightmare to me.

There's already a multitude of APIs out there that can be consumed on the application side and take care of all the aforementioned issues, such as Google's Translate API. There are other free ones as well. This is probably the simplest solution to this kind of problem.


Would the error log for MySQL for the IBM i be in mysql.general_log?

According to SHOW VARIABLES general_log is OFF and log_error is blank. So the default configuration is to not log anything.


You have a few options,

You can just use CHECK conditions if it's on one table's jsonb column
CREATE TABLE with the possible values and set it as an fkey.
Reduce that jsonb to something more logical in the app, or in the db. Create a function such that f(jsonb)=>k and store k on the table, or possibly as an ENUM


You need to check that the collation of the servers and columns match (collation isn't only determined at the database level).

To check the servers:

SELECT SERVERPROPERTY('COLLATION');

To check the columns (a much, much, much more likely culprit for this symptom IMHO):

SELECT collation_name
  FROM [Temp].sys.columns AS c
   INNER JOIN [Temp].sys.objects AS o ON c.[object_id] = o.[object_id]
   INNER JOIN [Temp].sys.schemas AS s ON o.[schema_id] = s.[schema_id]
WHERE UPPER(c.name = N'TABLE_NAME')
  AND UPPER(o.name = N'CLASS')
  AND UPPER(s.name = N'TEAM');

If these don't match, then your choices are:

Make them match. E.g. on the server where the collation is case sensitive, you can make it not case sensitive using:

ALTER TABLE [Team].[CLASS]
  ALTER COLUMN [TABLE_NAME] VARCHAR(32) -- guessing
  COLLATE SQL_Latin1_General_CP1_CI_AS
  NOT NULL; -- also guessing

Use a case-insensitive query, e.g.

WHERE UPPER([TABLE_NAME]) LIKE '%LINK%';

-- or

WHERE [TABLE_NAME] COLLATE SQL_Latin1_General_CP1_CI_AS
  LIKE '%LINK%';

Also be sure to use an N prefix if the column is NVARCHAR.


SUGGESTION #1
Based on the WHERE clause, I highly recommend a compound index
ALTER TABLE mytable ADD INDEX (md5,created_at);

That way, all parts of the WHERE clause are answered by the index.
SUGGESTION #2
Since you are imposing a limit of 200, collect the 200 keys first, then join the keys
SELECT B.* FROM
(
    SELECT id FROM mytable
    WHERE md5 = ?
    AND created_at >= ?
    AND created_at <= ? LIMIT 200
) A INNER JOIN mytable B USING (id);

I have suggested this technique before in StackOverflow
GIVE IT A TRY !!!


Is it better to omit associative tables in displaying many to many relationship

Your proposed Student table is the same as the picture's associative table, only using the natural key (Student Name) instead of the surrogate key (Student ID) as the primary key. (As an aside, getting a natural key that uniquely identifies humans is one of the notoriously hard tasks in data.)
The downside of your proposed model is that the student can have no existence outside of their participation in a class. The student comes into existence (as far as this model is concerned) only when she enrolls in a class. Should she un-enroll she ceases to participate in this model in any way whatsoever. I know this is a made-up teaching example, but in real life we'd have students who are not currently enrolled in any class - say from the time they're accepted until they show up days or months later and enroll. Further each student will have many more attributes (phone number, next of kin, etc.) In your proposed solution these must be stored redundantly on every row. This breaks second normal form, risking data update anomalies.


You could insert the results of a flashback query or flashback versions query into a table and export that table or you could do your own versioning.  You could also look at the Change Data Capture feature which may meet your requirements.  The real answer to your specific question though is...

No, you can't do that.


Can you view the Oracle listener status from OEM?

Yes, we can view the listener status from OEM (Oracle Enterprise Manager).
I am uploading the two screen shot related to listener in Oracle 11g,OEM(Oracle Enterprise Manager) status.

and the current 'Up Status' of Listener.

Note: I have highlighted some part due to security reason.


How to keep the structure of the Tablix when there is no data to show

You can add a row outside of the outermost group right under the tablix header row.

Set the row visibility to Show or hide based on an expression.  The expression would like like:

=iif(CountRows("DataSet1") > 0, true, false)

When there is no data, the table will show the headers and empty row.

When there is data, the empty row will be hidden.


Why does MariaDB 10.1.16 vanilla installation comes with an almost empty config file?

Not sure if it answers your question, but in /etc/my.cnf:

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

One of the files in this directory is:

mariadb-server.cnf

This file and possibly other files as well does not exists in MySQL.


Log Shipping from Synchronous Automatic Failover AlwaysOn Availability Group AAG

Are you doing your backups to a neutral independent server like a file share? If you're doing them locally (like to a drive on SQL01A or SQL01B) then log shipping won't work since the file path would change whenever there's a failover.

Instead, on SQL01A and SQL01B, write your backups to a UNC path (like \\myfileserver\myfileshare) and then SQL02 will always be able to find them there.


Intermittent errors sending email from SQL Server Database Mail

This appears to be the result of a "bug" in Windows Server firewall. We disabled the firewall service, and after rebooting the production server (yuck) all appears to be working as expected. We have an open MS case for this, but I don't have great confidence that anything will come of it.... If we do come up with something conclusive I will post back here.


MySQL MONTH Clause: Want to get Month Name instead of number

SELECT MONTHNAME('2018-11-03');

... should give 'November'. So use MONTHNAME instead of MONTH.

Another option is the DATE_FORMAT function. This should give the same result as above:

SELECT DATE_FORMAT('2018-11-03', '%M');

Use %b instead of %M to get abbreviated month names (Jan, Feb etc).


Is it possible to set timezone for a schema - Postgresql

No.
You can either set the timezone at database level, or at session level (... or directly when accessing a date-like datatype, using the at time zone construct).
From the documentation:

The TimeZone configuration parameter can be set in the file postgresql.conf, or in any of the other standard ways described in Chapter 19. There are also some special ways to set it:

The SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax.

The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection.


Oracle convert multiple rows to column using pivot

You can have multiple aggregates within a PIVOT but you must alias them.

Doing this will add the alias of the aggregate as a SUFFIX for the resulting column name.  (Your requirement is to have them as a PREFIX)

The FOR x in ( ) entries can also have a column name.  These will be the PREFIX for the final column.

I've modified the requirement, but here is the result.

with data (ID,TYPE,LINE_1_ADDR,LINE_2_ADDR,LINE_3_ADDR
          ,CITY_NAM, STATE , COUNTRY , EFFEC_DT  , ROW_UPDATE_DT
          ,ADDR_ROLE_CD )
as (
SELECT
  10114753088
 ,'SBS CUSTOMER'
 ,'2095 CAPP HARLIN RD'
 ,'Apt #225'
 ,''
 ,'SIMPSONVILLE'
 ,'MN'
 ,'USA'
 ,TO_DATE( '27-Jun-04','rr-mon-dd' )
 ,TO_DATE( '20-Mar-15','rr-mon-dd' )
 ,'BILL'
FROM dual
UNION ALL
SELECT
  10114753088
 ,'SBS CUSTOMER'
 ,'304 S MAGNOLIA ST'
 ,''
 ,'P.NO:516'
 ,'Toronto'
 ,'ON'
 ,'CAN'
 ,TO_DATE( '27-Jun-04','rr-mon-dd' )
 ,TO_DATE( '21-Mar-15','rr-mon-dd' )
 ,'PHYSICAL'
FROM dual)
select *
from data
pivot (
   min(LINE_1_ADDR) as "LINE_1_ADDR"
  ,min(LINE_2_ADDR) AS "LINE_2_ADDR"
  ,min(LINE_3_ADDR) as "LINE_3_ADDR"
  ,min(CITY_NAM) as "CITY_NAM"
  ,min(STATE) as "STATE"
  ,min(COUNTRY) as "COUNTRY"
  ,min(EFFEC_DT) as "EFFEC_DT"
  ,min(ROW_UPDATE_DT) as "ROW_UPDATE_DT"
  for addr_role_cd in ( 'BILL' as "BILLING", 'PHYSICAL' as "PHYS" )
)


Copy over data from previous rows in oracle 11g table

All right, so we need some mechanism to keep track of the previous wid value regardless of the number of repeating values, so we can not use LAG directly, because we do not know how far back was the last different value.

Our data is:

with data as
(
  select 'P1' as person_id, date'2001-01-01' as eff_dt, 2001 as wid from dual union all
  select 'P1' as person_id, date'2001-01-10' as eff_dt, 2001 as wid from dual union all
  select 'P1' as person_id, date'2001-10-10' as eff_dt, 2002 as wid from dual union all
  select 'P1' as person_id, date'2002-01-01' as eff_dt, 2003 as wid from dual union all
  select 'P1' as person_id, date'2002-05-04' as eff_dt, 2003 as wid from dual union all
  select 'P1' as person_id, date'2002-08-06' as eff_dt, 2002 as wid from dual union all
  select 'P1' as person_id, date'2005-01-01' as eff_dt, 2001 as wid from dual union all
  select 'P1' as person_id, date'2006-01-10' as eff_dt, 2001 as wid from dual
)
select
 *
from
 data
order by
 eff_dt
;

PE EFF_DT            WID
-- ---------- ----------
P1 2001-01-01       2001
P1 2001-01-10       2001
P1 2001-10-10       2002
P1 2002-01-01       2003
P1 2002-05-04       2003
P1 2002-08-06       2002
P1 2005-01-01       2001
P1 2006-01-10       2001

I will not repeat the with section any more to save space. Let's calculate a new field, called prev_marker as:

If wid = previous wid, this field becomes NULL. If wid != previous wid, this field gets the value of the previous wid

select
  person_id,
  eff_dt,
  wid,
  case when lag(wid) over (order by eff_dt) != wid
    then lag(wid) over (order by eff_dt)
    else null
  end as prev_marker
from
  data
order by
  eff_dt
;

PE EFF_DT            WID PREV_MARKER
-- ---------- ---------- -----------
P1 2001-01-01       2001 (null)
P1 2001-01-10       2001 (null)
P1 2001-10-10       2002        2001
P1 2002-01-01       2003        2002
P1 2002-05-04       2003 (null)
P1 2002-08-06       2002        2003
P1 2005-01-01       2001        2002
P1 2006-01-10       2001 (null)

Using this coluumn, we can get the desired prv_wid value with the LAST_VALUE function if we inspect rows up to the current row. The trick is, that the LAST_VALUE function supports the IGNORE NULLS option, so when we encounter a NULL value, we can easily ignore all the rows with the same wid and fall back to the previous value:

select
  person_id,
  eff_dt,
  wid,
  last_value(prev_marker ignore nulls) over (order by eff_dt rows between unbounded preceding and current row) as prv_wid
from
(
select
  person_id,
  eff_dt,
  wid,
  case when lag(wid) over (order by eff_dt) != wid
    then lag(wid) over (order by eff_dt)
    else null
  end as prev_marker
from
  data
)
order by
  eff_dt
;

PE EFF_DT            WID    PRV_WID
-- ---------- ---------- ----------
P1 2001-01-01       2001 (null)
P1 2001-01-10       2001 (null)
P1 2001-10-10       2002       2001
P1 2002-01-01       2003       2002
P1 2002-05-04       2003       2002
P1 2002-08-06       2002       2003
P1 2005-01-01       2001       2002
P1 2006-01-10       2001       2002

Our last task is to replace the NULLs with 0s, that is just a simple NVL:

select
  person_id,
  eff_dt,
  wid,
  nvl(last_value(prev_marker ignore nulls) over (order by eff_dt rows between unbounded preceding and current row), 0) as prv_wid
from
(
select
  person_id,
  eff_dt,
  wid,
  case when lag(wid) over (order by eff_dt) != wid
    then lag(wid) over (order by eff_dt)
    else null
  end as prev_marker
from
  data
)
order by
  eff_dt
;

PE EFF_DT            WID    PRV_WID
-- ---------- ---------- ----------
P1 2001-01-01       2001          0
P1 2001-01-10       2001          0
P1 2001-10-10       2002       2001
P1 2002-01-01       2003       2002
P1 2002-05-04       2003       2002
P1 2002-08-06       2002       2003
P1 2005-01-01       2001       2002
P1 2006-01-10       2001       2002


First field from my return table is being eaten up

The short answer: use date_trunc('day', ...) to set the time part of a timestamp to 00:00:00

The long answer: the whole function is needlessly complicated. There is no need to create a table each time and then modify the columns or even update that table.

This can all be done with a single select statement.

If I understood everything correctly, the query you are looking for is something like this:

with adjusted as (
  select id,
         case
            when entrada < inicio then inicio
            else entrada
         end as entrada,
         case
           when saida > fim then fim
           else saida
         end as saida,
         erro
  from uptime u
  WHERE u.saida >= inicio
    and u.entrada <= fim
)
select id,
       date_trunc('day', entrada) as entrada
       date_trunc('day', saida) as saida
       erro
       saida - entrada as updatime
from adjusted
order by u.entrada;

Note that I "truncated" the columns entrada and saida in the final select which means that the calculation of the uptime does include the time part.

So the whole function can be simplified to:

create or replace function uptimes(inicio date, fim date)
    returns table (id integer, entrada timestamp with time zone,
                   saida timestamp with time zone, erro boolean, uptime interval)
as
$$
    with adjusted as (
      select id,
             case
                when entrada < inicio then inicio
                else entrada
             end as entrada,
             case
               when saida > fim then fim
               else saida
             end as saida,
             erro
      from uptime u
      WHERE u.saida >= inicio
        and u.entrada <= fim
    )
    select id,
           date_trunc('day', entrada) as entrada
           date_trunc('day', saida) as saida
           erro
           saida - entrada as updatime
    from adjusted
    order by u.entrada;
$$
language sql;

It is also a good idea to use some kind of prefix for the parameter names, so that they are easier distinguishable from the column names in the query. e.g. p_inicio instead of just inicio


If there is an OS you should leave some RAM for it.
EC2 is just a VM, so whatever you usually do for VM also applies for EC2.


PHP/MongoDB: Unset all fields in a document

To remove all fields (aside from _id), you can just update with an empty document:

<?php
    $collection->update(array('name' => 'Amy'), array());
?>

That will update the document leaving only the _id field.

Note that this won't free up any of the preallocated space for your capped collection.


expdp Hangs at DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM in oracle 12c

So in the end it's just a time issue you have. You would have to open up a ticket with Oracle if you want to speed things up. This is probably not an Oracle issue, but possibly an issue with your database design.

You have to understand that the Data Pump Export Utility has to determine all relevant constraints and relations before it can create a valid structured metadata export. This can take a substantial amount of time and depends on the structure of your database, the amount of schemas and other limiting constraints.

The more complex the metadata is, the longer it can take to export.

You might want to look at the object after the .../POST_SYSTEM_ACTIONS/PROCACT_SYSTEM as that could actually be the limiting factor.

You might want to have a look at the official documentation regarding Database Utilities | Data Pump Performance | Tuning Performance (Oracle 12c Documentation) for additional ideas on how to speed up things.


The datapump export has the complete DDL for every object you exported.  So while the default for the tablespace is to compress, this can (and will) be overridden by any explicit setting for a table.
Check the COMPRESSION column on xxx_TABLES for the schema(s) you are importing to see if you are truly getting any compression.
You will need to use "transform=segment_attributes" variants on import to ensure that you strip off the storage elements of your objects to that they pick up the default you set at tablespace level.


How to speed up mysql query against a 204 million record table

All columns on both tables are indexed.

Enough said.  That is not the right way to go about indexing.

  WHERE db2.tb2.c1 = db1.tb1.c1

db2.tb2 needs INDEX(c1) -- Keep in mind that a PRIMARY KEY is a UNIQUE key is an INDEX, so do not redundantly add INDEX if you already have PRIMARY KEY(c1).

  and c6='X' and c9 <='y' and c10>= 'Z' and c12>='N'

Needs a compound index; recommending adding these three, since there are no clues of which would be best:

INDEX(c6, c9)
INDEX(c6, c10)
INDEX(c6, c12)

At the same time, remove INDEX(c6) because it is now redundant.  For further discussion, Cookbook on making an INDEX, given a SELECT.

  tmp_table_size = 3G
  max_heap_table_size = 1G

Very dangerous.  Are you swapping?  Swapping is much worse on performance than lowering some of the settings.  Each SELECT that needs a tmp table will allocate 1GB; if you have lots of such queries running at the same time, you will rapidly chew up RAM.  Lower both of those to their defaults.

  key_buffer = 5G

That is for MyISAM indexes.  If you are not using MyISAM, set it to 20M.  InnoDB is the preferred engine; 26G (out of 36GB) is reasonable if all of your tables are InnoDB.

There may be more suggestions; see how far you get with these.

Edit

  DISTINCT(c1),c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12

May not mean what you think -- DISTINCT is not a function, and the parens are irrelevant.  That de-dups the entire set of 12 expressions.  Also, it is probably irrelevant, unless you actually have duplicate rows.

Is c1 your PRIMARY KEY?  Hence UNIQUE?  So the GROUP BY is also irrelevant.  Removing it may help speed.


I would seem to me, that you want leading matches first, then shortest strings first, and finally alphabetic sort order:
SELECT *
FROM   users
WHERE  username ILIKE '%del%'
ORDER  BY username NOT ILIKE 'del%'  -- leading match first
        , length(username)           -- shorter words first
        , username;                  -- finally alphabetical

db<>fiddle here
NOT ILIKE 'del%' because false sorts before true. See:

Sorting null values after all others, except special

Aside: Never use char(n). See:

Any downsides of using data type “text” for storing strings?


Not getting a result set in SSRS 2008r2 from an iSeries DB2 SQL stored procedure

One thing that you are missing in your stored procedure declaration is the DYNAMIC RESULT SETS option. The default value for that option is 0, may be that's what confuses the client software. Whether it (the client software) can even handle cursors returned by a stored procedure is another story.


Can this business logic be enforced by a conditional database constraint?

If CASE_RECOMMENDATION.CASE_STAGE_ID is unique, you can use referential integrity with a virtual column (11g+) to make it conditional:

alter table CASE_RECOMMENDATION ADD CONSTRAINT unique_case_stage unique (CASE_STAGE_ID);

-- virtual column only defined when stage_id=1646
alter table CASE_STAGE add
   (case_1646 as (case when stage_id=1646 then previous_case_stage_id end));

-- check that the virtual column is defined when stage_id=1646
alter table case_stage add
    constraint chk_1646 check ( stage_id!=1646 or previous_case_stage_id is not null);

-- referential integrity
alter table case_stage add
     constraint fk_1646 foreign key (case_1646)
     references case_recommendation (case_stage_id);

Let's check:

SQL> insert into  CASE_STAGE values (1, 1, 1, sysdate, null, null, 1, null, default);

1 row(s) inserted.

SQL> insert into CASE_RECOMMENDATION values (1, 1, 1, sysdate, 1);

1 row(s) inserted.

SQL> -- fails because previous_case_stage_id is null
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, null, 1, null, default);

ORA-02290: check constraint (VNZ_TEST3.CHK_1646) violated

SQL> -- fails because previous_case_stage_id doesn't exist in CASE_RECOMMENDATION
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 2, 1, null, default);

ORA-02291: integrity constraint (VNZ_TEST3.FK_1646) violated - parent key not found

SQL> -- succeeds !
SQL> insert into CASE_STAGE values (2, 1646, 1, sysdate, null, 1, 1, null, default);

1 row(s) inserted.


Can't connect to SQL Server through SSMS remotely

Windows Firewall is turned off so no issue there.

There will be some form of firewall operating between the machine and the outside world (unless there is something really wrong with your company's infrastructure setup!) and that is most likely blocking access to the relevant ports for external connections.

  How do I connect to an instance on this server using SSMS on my local machine?

It may be that you cannot access the SQL Server directly at all, in which case you'll need to use tools installed on the VM(s) rather than local copies. It may be that there is a VPN seetup that you can connect through. It may be that there is a route through the firewalls connecting to a specific address+port from your location. In any case you will need to talk to your infrastructure people, we know nothing of your network arrangement so are unlikely to be able to help beyond these generic pointers.


Multiple foreign keys with shared columns for weak entities

Both solutions for T or T-alternate are considered denormalizations. Denormalizations optimize a datamodel that is normally in third normal form into a structure that is more convenient for select queries. The correct data already exists in CC and CD. T or T-alternate are duplicating the data. With duplicate (denormalized) data you have to make sure that data cannot get out of synch with its parent.

I think the first option is the better one. The constraints are simple and straight-forward. There is no check constraint on multiple fields with nulls. You need to make sure that any insert into CC or CD also inserts into T. After-insert triggers on CC and CD would automatically insert A,B,C,null into T or A,B,null,D into T.

One thing to plan for is that a parent has two children, each with many rows per parent. Lets assume a row in P has 3 rows in CC, 0 in CD. If you write a query over P inner joining to CC and CD, it shows zero rows. An left outer join (parent on left) shows 3 rows.

Lets assume CC has 3 rows and CD has 4 rows. Inner join and outer join queries would show 3*4 = 12 rows.

Lets assume a 3rd set of data. CC is the main child table with 20 rows per P. CD is a descriptive text field with 3 rows per P. You could reduce the resulting row to 20 per P with an aggregation function like Oracle 11g's LISTAGG over CD. This combines the D descriptive field into a comma separated list "Planes, Trains, Automobiles".

Having CCA and CDA fields in T-alternative increases the chances for data to get out of synch, since CCA might not equal CDA when something goes wrong in the code. The T-alternative is not the standard way of approaching this problem. The first option does have some challenges, but it is simpler, more standard, and more likely to hold up under production systems.


The given statement smells like MS SQL-Server, not like DB2.

According to IBM Documentation I think SYSCAT.TABLES is the view you are looking for.


Documenting where stored procs are called from

SQL Server has the sys.sql_dependencies table.

In SSMS you can right-click an object and choose "dependencies". My subjective experience is that this can be incomplete.

References from application code may be amenable to a simple search, depending on how it's constructed.


Can a single PostgreSQL query use multiple cores?

No, for versions of PostgreSQL prior to v9.6. Please see the PostgreSQL FAQ: How does PostgreSQL use CPU resources?

The PostgreSQL server is process-based (not threaded). Each database session connects to a single PostgreSQL operating system (OS) process. Multiple sessions are automatically spread across all available CPUs by the OS. The OS also uses CPUs to handle disk I/O and run other non-database tasks. Client applications can use threads, each of which connects to a separate database process.
Since version 9.6, portions of some queries can be run in parallel, in separate OS processes, allowing use of multiple CPU cores. Parallel queries are enabled by default in version 10 (max_parallel_workers_per_gather), with additional parallelism expected in future releases.


How do I force windows to use a specific version of SQLCMD?

Have you tried updating the PATH variable?

For Windows 10, do the following:

In the File Explorer, right-click on This PC.
In the context menu, click Properties.
In the System window, click the Advanced system settings page on the left-hand side.
In the Advanced Tab, click on  Environment Variables.
In the System Variables window, highlight Path, and click Edit.
In the Edit System Variables window, insert the cursor at the end of the Variable value field.
If the last character is not a semi-colon (;), add one.
After the final semi-colon, type the full path to the file you want to find.

In your case: C:\Program Files\Microsoft SQL Server\110\Tools\Binn

Find the path with 100 in it, and then remove it.

Click OK in each open window.

Word of caution: After doing these steps, you will not be able to auto-find any other exe's that the system was able to find earlier in the older path you just removed.


As per MongoDB documentation Modify Chunk Size in a Sharded Cluster The default chunk size for a sharded cluster is 64 megabytes.

As MongoDB documentation here New in version 2.6: MongoDB provides the mergeChunks command to combine contiguous chunk ranges into a single chunk. See Merge Chunks in a Sharded Cluster for more information.

Note: Be careful when splitting data in a sharded collection to create new chunks. When you shard a collection that has existing data, MongoDB automatically creates chunks to evenly distribute the collection. To split data effectively in a sharded cluster you must consider the number of documents in a chunk and the average document size to create a uniform chunk size. When chunks have irregular sizes, shards may have an equal number of chunks but have very different data sizes. Avoid creating splits that lead to a collection with differently sized chunks.

For further your ref here


find rows with no child and if has child latest child

Is id guaranteed to be increasing along with created_at? In other words, can I just do a MAX(id) to always find the latest child value for the same parent_id? Or do I actually need to use the created_at date?

If I don't have to actually use the date, then you can do:

--rows that are not parent or child
SELECT id, parent_id
FROM mytable
WHERE parent_id = 0                              --not a child
AND id NOT IN (SELECT parent_id FROM mytable)    --not a parent

UNION ALL

--latest child of parent
SELECT MAX(id) as id, parent_id
FROM mytable
WHERE parent_id <> 0
GROUP BY parent_id

Gets kinda funny with the GROUP BY if you are wanting to include a bunch of other columns from the table.

But either way, a good general strategy is handling the two parts separately and use UNION ALL TO mash the results together. (UNION will check the two sets for duplicates, so a UNION ALL is preferred for performance reasons, since we know there won't be an overlap here.)


Allow access to enter data through forms, but not full database access

Use stored procedures, which can validate input before issuing the SQL commands. The user can be given rights to the stored procedure without any other access.


Minimizing and Handling Deadlocks Strategies

This is a "normalization" table?  That is, it is a mapping from name to a shorter id?

If so, simply remove it from the transaction.  You don't really need to rollback if there is a failure.  At worst, you might have a few extra rows in the table that are never used.  Outside the main transaction, have autocommit=ON.  And, there is probably no need for IODKU, so simply use INSERT IGNORE.

Change from

UNIQUE KEY `name` (`name`),
PRIMARY KEY (`id`),

to

PRIMARY KEY(name),
INDEX(id)   -- sufficient for auto_increment

Rationale:  Multiple unique indexes slows down INSERTs.

Caveat:  Under what conditions do you look at the "changing_date" columns?  If you are coming from id, then your indexes are better due to clustering.

Another thing to note:  All(?) forms of INSERT, including IODKU, "burn" ids.  That is, you will find lots of gaps in the id values.

Since you are inserting at a mere 1+/second, I don't think you need any kind okf batching.  But if they do come to you in clumps, then here is a discussion of how to do that efficiently: http://mysql.rjweb.org/doc.php/staging_table#normalization


MySQL 5.6 showing wrong "second_behind_master"

The Seconds_Behind_Master may be zero, but you should be looking at other parts of the SHOW SLAVE STATUS\G because it can tell you something about why it's zero. Let's discuss three(3) aspects that will shed some light on Replication's Seconds_Behind_Master.
ASPECT #1
There are occasions when a group of SQL commands are executed individually in the same one-second timeframe. Why does this happen on a Slave?
Keep in mind that the Master may have executed many INSERTs, UPDATEs, and DELETEs, in parallel. However, the binary logs records them as serialized events. An event will include the TIMESTAMP of the SQL command followed by the SQL Command.
If 30 DML commands each took 1 second to complete on a Master at 1399212601 (2014-05-04 10:10:01), they get recorded in the binary logs with the timestamp 1399212601.
On the Slave, when each command is read one-by-one, the timestamp will be 1399212601 for each command. Yet, real-time clock on the server will be steadily increasing. Seconds_Behind_Master is nothing more than UNIX_TIMESTAMP(NOW()) - 1399212601. This is the manifestation of Replication Lag.
ASPECT #2
There are occasions when a group of SQL commands are executed as series of row changes.
This could easily happen with row based replication (binlog_format is set to ROW instead of STATEMENT). Individual row changes bloat binary logs and result in same replication lag as stated in ASPECT #1
ASPECT #3
Here are three posts where I discuss how Replication handles LOAD DATA INFILE

Apr 18, 2013 : Loading data in mysql using LOAD DATA INFILE, replication safe?
Apr 02, 2013 : MySQL slave stuck in "Reading event from the relay log"?
Jan 22, 2012 : MySql shell command not replicated to slave

When you execute LOAD DATA INFILE on a Master, the Master will copy the entire CSV file as binlog events into the Binary Logs. The next binlog event recorded is the LOAD DATA INFILE command itself.
The Slave will see a binlog event in its relay logs that will alert the Slave to read the entire CSV file. Then, when it see the LOAD DATA INFILE, it executes it against the temp CSV file in created.
During the manifestation of the temp CSV file, the Seconds_Behind_Master will read 0. Then, when the entire CSV file is made and the LOAD DATA INFILE begins execution, the Seconds_Behind_Master will just increase by 1 or 2. It will do a punctuated jump to hundreds or even thousands of seconds.
SUMMARY
Binary Logs are are to handle group commits for better performance. Notwithstanding, you should looking at the following

Master_Log_File : Binary Log of the Last Binlog Event recorded on the Master read on the Slave
Read_Master_Log_Pos : Position of Last Binlog Event that was read from the Master and written to Relay Log on the Slave
Relay_Master_Log_File : Binary Log of the Last Binlog Event that was read from Master and was executed on Slave
Exec_Master_Log_Pos : Position of the Last Binlog Event that was read from Master and was executed on Slave
Relay_Log_Space

If the Relay_Log_Space is changing, then the IO Thread is reading just fine from the network. You can also see that by watching either Master_Log_File or Read_Master_Log_Pos  or both changing.
If the Relay_Master_Log_File and/or Exec_Master_Log_Pos is changing, then the command are begin executed on the Slave.
Therefore, once Seconds_Behind_Master is 0 for a long time, look for changes in these five fields in the SHOW SLAVE STATUS\G.


The biggest problem I see is in your joins. You're joining the "id" of Users, to the "id" of Posts and Comments. This basically says, take every record in Users and join it to every record in Posts, then join it to every record in Comments.

Presuming that the UserID exists as a foreign key in Posts and Comments, the code below should work. If not, you'll need to determine the correct foreign keys for the JOIN statements.

SELECT
    U.Age,
    U.CreationDate,
    U.DisplayName,
    P.AcceptedAnswerId,
    P.ClosedDate,
    P.Title,
    P.ViewCount,
    C.CreationDate,
    C.Score,
    C.UserId
FROM
    dbo.Users AS U
    LEFT OUTER JOIN dbo.Posts AS P
        ON U.UserID = P.UserID
    LEFT OUTER JOIN dbo.Comments AS C
        ON U.UserID = C.UserID
WHERE
    U.Location IN ('India','United Kingdom')
    AND U.LastAccessDate>= '04/02/2018'
    AND U.LastAccessDate< '06/02/2018'
    AND P.LastActivityDate>= '04/02/2018'
    AND P.LastActivityDate< '06/02/2018'
    AND U.Age>10

I replaced the location OR statement with an IN.

I removed the duplicated U.LastAccessDate conditions.

I removed the unnecessary U.Age IS NOT NULL condition.

If this query still runs slowly. My next best guess would be a missing index on Users.LastAccessDate and/or Posts.LastActivityDate.


Best database to store and retrieve Word and Excel documents

Personally since you don't have a relation beyond barcode --> document I don't think a relational database is the best fit.

If your requirements are really as simple as:

Find a document
Serve document to user

Then any Key/Value store should work.  This wikipedia article talks about some of the theory for Document Databases as well as some pro/cons vs Relational Databases.

You will need to write some sort of interface/application to translate the barcode reader output into a query for the document in the database you choose.  As such you might not even need to use a database at all.  You could just have the application layer find the documents in the network drive...

Since I was pressed for a specific recommendation.  I would look at MongoDB. It should be cheap to run, will fit you needs, and appears to have broad support.  Here is a link to a blog article discussing storing files/binaries in MongoDB.

Fair warning: I don't have any direct experience with MongoDB, and only basic familiarity with document databases in general.  As such my recommendation of MongoDB is based on hearsay, and general impressions.


How to remove duplicates and keep the one with the most additional information

Your question is a bit light on definitions. Assuming:

You define the "least amount of information" with how many of the relevant columns are NULL.
Primary key is adr_id.
Duplicates are marked with a column dupe_id to indicate groups of duplicates.

Since it's also vague what to do exactly, I create a separate table with the dupe ranking:

CRATE TABLE adr_dupe_rank AS
SELECT adr_id, dupe_id
     , rank() OVER (PARTITION BY dupe_id
                    ORDER BY (nr_of_beds  IS NULL)::int
                           + (nr_of_baths IS NULL)::int
                        -- + (...)::int  -- more terms
                   ) AS rnk
FROM   address;

false translate to 0, true translates to 1. So rows with the fewest empty columns are ranked first. Master rows end up with rnk = 1. Dupes get higher rnk numbers.

The window function rank() assigns 1 to all rows sharing the lowest score per dupe_id. Add enough columns or other terms to break ties and get one winner per dupe_id. Or deal with multiple winners separately.

You can then do as you please. To just delete dupes:

DELETE FROM address a
USING  adr_dupe_rank d
WHERE  a.adr_id = d.adr_id
AND    d.rnk > 1;

Alternatively you can use the above query as derived table in the DELETE directly.


Determining how a schema change occurred?

This gets logged to the default trace so, as long as it is enabled and hasn't rolled over in the meantime it should appear in the "Schema Changes History" report.

To access this in Management Studio right click the database then from the context menu choose Reports -> Standard Reports -> Schema Changes History

To retrieve the same information via TSQL you can use

SELECT StartTime
       ,LoginName
       --,f.*
FROM   sys.traces t
       CROSS APPLY fn_trace_gettable(REVERSE(SUBSTRING(REVERSE(t.path),
                                                       CHARINDEX('\', REVERSE(t.path)),
                                                       260)
                                             ) + N'log.trc', DEFAULT) f
WHERE  t.is_default = 1
       AND ObjectName = 'FOO'
       AND EventClass IN (46, /*Object:Created*/
                          47, /*Object:Dropped*/
                          164 /*Object:Altered*/ )


Using environment variables in data pump query

Of course, you can. Here is an example:

$ cat expdp.sh
#!/bin/bash

DIR_NAME=dummy
DIR_PATH=/oracle/base/export
QUERY='employees:"WHERE employee_id = 100"'
mkdir -p $DIR_PATH
sqlplus / as sysdba<<EOF
create directory $DIR_NAME as '$DIR_PATH';
exit
EOF
expdp \"/ as sysdba\" directory=$DIR_NAME tables=hr.employees query=$QUERY

Output:

$ ./expdp.sh

SQL*Plus: Release 12.1.0.1.0 Production on Sun Mar 30 20:06:26 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
Directory created.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Export: Release 12.1.0.1.0 - Production on Sun Mar 30 20:06:26 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=dummy tables=hr.employees query=employees:"WHERE employee_id = 100"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "HR"."EMPLOYEES"                            9.570 KB       1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /oracle/base/export/expdat.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Sun Mar 30 20:06:47 2014 elapsed 0 00:00:19


How do I know if an index has been successfully created in MySQL?

Welcome.

If you say that the script did not finish / exit...

One crude method to find if the indexing process is in the middle...

You shall try listing the folder contents of /var/lib/mysql/dbname/ and checking the date and time of update of the table names concerned. If the date/time does not change for a while you shall be sure that the process is not touching that table file for as much time. Hence cornering if there is a process snap due to timeout or other.


Why is mysql using the wrong index for order by query?

MySQL is using a relatively simple (simpler than other RDBMS) cost model for planning queries in which filtering your dataset has quite high priority. In your first query with the merge index it is estimated that scanning ~9000 rows is going to be necessary while the second with the index hint will require 18000. My bet would be that this weighs in the calculation enough to move the scale towards the merge. You can confirm this (or find other reasons) by turning optimizer_trace on, run your query and evaluate the results.

set global optimizer_trace='enabled=on';

-- run your query

SELECT SQL_NO_CACHE * FROM votes WHERE
    voter_id = 1099 AND
    rate = 1 AND
    subject_name = 'medium'
ORDER BY updated_at DESC
LIMIT 20 OFFSET 100;

select * from information_schema.`OPTIMIZER_TRACE`;

One remark on index_merge: in most cases you will find that it is quite expensive. Although very useful for OLAP type scenarios it might not be very well suited for OLTP because the operation can take significant time of your query and as you can see sometimes the suboptimal execution plan is actually faster.

Fortunately MySQL provides switches for optimizer so you can customize it as you wish.

For all the option you can run:

show global variables like 'optimizer_switch';

For changing one you don't have to copy paste the whole string. It works such as dict.update() in python.

 set global optimizer_switch='index_merge=off';

If possible I would also take a look at your table structure and improve. Having a ~100 byte primary key with many secondary keys is not really advised.

You have four secondary keys and some of them are superfluous for example (voter_id) index is a subset of (voter_id, updated_at)


Best way to perform SQL Server replication over multiple servers?

I had a long answer written on stackoverflow, but then you deleted the question about the same time I was ready to post it. I am not going to put the same effort in again, but in short:

Check out the stairway to replication: http://www.sqlservercentral.com/stairway/72401/
It does not cover bidirectional transactional replication, but it covers the permissions involved in setting up transactional replication. That should allow you to figure out the bidirectional setup as well.
You might want to check out merge replication instead. It was designed for multi-node bidirectional synchronization of data and offers nice conflict resolution handling. However it adds a guid column to each table.


Writing SQL statement in sqlcmd to hide the milliseconds for column type of time

If you just need to remove the nano-seconds for display purposes you could convert the time value to a string using the below which will give you the format you desire:

SELECT ID, CONVERT(NVARCHAR, EXPIRE_TIME, 108) AS EXPIRE_TIME FROM TESTTABLE;

However, if you do decide to do this and then decide you need to perform calculations on the information, you would need to cast it back to a time value; which would give you the nano-seconds again but they would be set to zeros.

Alternatively, you could handle the formatting of the data in the object that is consuming it; for instance a SSRS report (which has the Format() function) or in an ASP.NET Web Form (which has a multitude of ways to handle this). It really depends on what your ultimate goal is that you want to achieve.

I hope this helps you.

Further Reference: http://msdn.microsoft.com/en-us/library/ms187928.aspx


It's unclear whether you want the total count or the count in the past 24 hours. For the latter:

SELECT count(*), page
FROM   page_views
GROUP  BY 2
WHERE  created_at >= now() - interval '24 hours' -- guessing column
HAVING count(*) > 20
ORDER  BY 1 DESC;

The condition count(*) > 20 has to go into the HAVING clause.

You did not provide a table definition. Guessing you have a created_at to store the event time.

Total counts, but only for pages having a minimum count of 20 in the past 24 hours:

SELECT count(*), page
FROM  (
    SELECT page
    FROM   page_views
    GROUP  BY 1
    WHERE  created_at >= now() - interval '24 hours'
    HAVING count(*) > 20
    ) x
JOIN   page_views p USING (page)
GROUP  BY 2
ORDER  BY 1 DESC;


PLPGSQL function(multiple column output)--gives error

It seems you simply want to return the result of the query. To do that, the function needs to be declared as a set returning function. Your current function is a scalar function that returns a single character (char).

You also don't need dynamic SQL or even PL/pgSQL for that:

create or replace function help_constraint(p_schemaname text, p_tablename text)
  returns table(constraint_type text, constraint_name text, delete_action text, update_action text, status_enabled boolean, status_for_replication text, constraint_keys text)
as
$body$
  SELECT tc.constraint_type,
         tc.constraint_name,
         null::text as delete_action,
         null::text as update_action,
         null::boolean as status_enabled,
         null::text as status_for_replication,
         coalesce(cc.check_clause, ccu.column_name ) as constraint_keys
  from information_schema.table_constraints AS tc
    JOIN information_schema.constraint_column_usage ccu
           ON tc.constraint_catalog = ccu.constraint_catalog
          AND tc.constraint_schema = ccu.constraint_schema
          AND tc.constraint_name = ccu.constraint_name
    left JOIN information_schema.check_constraints cc
           ON tc.constraint_catalog = cc.constraint_catalog
          AND tc.constraint_schema = cc.constraint_schema
          AND tc.constraint_name = cc.constraint_name
  where tc.constraint_type IN ('FOREIGN KEY', 'PRIMARY KEY', 'CHECK')
    and ccu.table_schema = p_schemaname
    and ccu.table_name = p_tablename;
$body$
language sql;

Note that I change the first left join to an inner join as the where condition on ccu makes it an inner join  anyways.

To use that function use:

select *
from help_constraints('public', 'my_table');


Truncated 200GB table but disk space not released

If you are referencing the actual database file consumption on the volume, then SQL Server doesn't handle that automatically.  Just because you removed data from the database doesn't mean the database files will shrink to fit only the existing data.

What you'd be looking for, if you have to reclaim space on the volume, would be shrinking the particular file with DBCC SHRINKFILE.  It is worth noting a few best practices, as per that documentation:

  Best Practices

  Consider the following information when you plan to shrink a file:

  A shrink operation is most effective after an operation that creates    lots of unused space, such as a truncate table or a drop table operation.
  Most databases require some free space to be available for regular    day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
  A shrink operation does not preserve the fragmentation state of    indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
  Shrink multiple files in the same database sequentially instead of    concurrently. Contention on system tables can cause delays due to blocking.

Also of note:

  DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained.

There are surely a few things to consider when doing this, and I'd recommend you take a look at Paul Randal's blog post on what happens when you do this operation.

The first step would definitely be to verify how much space and free space you are actually able to replace, as well as the used space on the file(s):

use AdventureWorks2012;
go

;with db_file_cte as
(
    select
        name,
        type_desc,
        physical_name,
        size_mb =
            convert(decimal(11, 2), size * 8.0 / 1024),
        space_used_mb =
            convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
    from sys.database_files
)
select
    name,
    type_desc,
    physical_name,
    size_mb,
    space_used_mb,
    space_used_percent =
        case size_mb
            when 0 then 0
            else convert(decimal(5, 2), space_used_mb / size_mb * 100)
        end
from db_file_cte;


Can OPENROWSET parameters be built dynamically?

You could in theory do this, but it would require building the update statement with dynamic SQL. Basically, you declare a string variable, build it with metadata from the database, and then execute that string. Erland Sommarskog has an epic post on this: The Curse and Blessings of Dynamic SQL.

However, that's gonna require a lot of work and a lot of debugging.

Plus, for performance reasons, you don't really wanna fire a new OPENROWSET for every row of a table you're updating.

Instead, I'd suggest doing a single OPENROWSET call to pull all of the file contents into a temp table, and then doing your update statement from the temp table.


Cassandra in multiple data center - EC2MultiRegionSnitch

In regard to a standard implementation of Cassandra the answer is Yes. So everything in the cluster regardless of the data center would have access.

From the Apache Cassandra site:

  Cassandra's support for replicating across multiple datacenters is
  best-in-class, providing lower latency for your users and the peace of
  mind of knowing that you can survive regional outages.

The site you're referencing is talking about a service they perform called partitioning where they're actually distributing data in a different way based on their partitioning scheme, while still using Cassandra as the database. They do replication based on NetworkTopologyStrategy which depends on the network and the number of datacenters.

In regard to Amazon EC2
They're mixing lingo a little bit which is where the confusion is coming in.

  ... on Amazon EC2 where all nodes in the cluster are within a single
  region. The region is treated as the data center and the availability
  zones are treated as racks within the data center.

So on Amazon's network a region will contain multiple data centers, but they're distributing info like it were local to a data center (probably because their network has plenty of resources and nodes). This document here goes more into the availability zones concept for their network. Basically their Network Topology is a little larger than most networks.

The data should remain in what they're considering a data center, but whether this is the same physical data center or not would depend on the number of racks, the types of machines they're running and the backbones (OC-3,OC-12,OC-48, etc lines) going between them. If you're in a region where they can't have replication across multiple data centers as quickly they'll likely have more redundancy in the physical data centers themselves.


Is it okay to put dots in SQL Server database names?

You can, but I wouldn't. You would always have to wrap the DB name with square brackets such as [MyApp.Sales].

So to recap: if you value your sanity, don't do it.


Table with only a single constant value?

Having a flag on the Stations table that indicates which station is local would be exactly how I'd handle this, presuming you don't have a billion stations.  I'd likely call the column IsSourceStation or something, and make it a BIT value, that can accept NULL.  I would mark the local station row as 1, and leave all other rows as NULL, since that won't take any space (see my comments below regarding space).

I'd add a filtered index to the IsSourceStation column, filtered as WHERE IsSourceStation = 1.  This index will allow extremely fast lookups to determine the name of the local station, if that is required.

Looking for the Stations row that corresponds to our "home" station could be accomplished by:

SELECT *
FROM Stations
WHERE IsSourceStation = 1;

This will be very fast with the index I suggested, regardless of how many rows are in the Stations table.

Looking to confirm a station is not the home station?  Use this:

IF EXISTS (
    SELECT 1
    FROM Stations
    WHERE IsSourceStation IS NULL
        AND StationID = 1234
    )
BEGIN
    -- StationID 1234 is NOT the home station
END

The null bitmap used in SQL Server is a fantastic optimization designed for just this type of situation where very few rows in a nullable column actually contain a value.

My statement above, while technically correct in that the null bitmap is used to save space, in the case of a table with a single bit column, there is no appreciable difference between defining the column as nullable vs having it be not nullable, with a default value of 0.  I used the following test bed to determine this on SQL Server 2012:

USE tempdb;
IF EXISTS (SELECT 1 FROM sys.tables t WHERE t.name = 'TestBit')
DROP TABLE dbo.TestBit;
IF EXISTS (SELECT 1 FROM sys.tables t WHERE t.name = 'TestBitNotNull')
DROP TABLE dbo.TestBitNotNull;

CREATE TABLE dbo.TestBit
(
    TestBitID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , IsBit BIT NULL
);

INSERT INTO dbo.TestBit (IsBit)
SELECT TOP(1000000) NULL
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;

CREATE TABLE dbo.TestBitNotNull
(
    TestBitID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , IsBit BIT NOT NULL
        CONSTRAINT DF_TestBitNotNull
        DEFAULT ((0))
);

INSERT INTO dbo.TestBitNotNull(IsBit)
SELECT TOP(1000000) 0
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;

The above code creates two tables, each with a single INT column, and a single BIT column.  The first table allows the BIT column to be NULL; the second table does not.

I used the following to inspect the actual on-disk data pages for the first page of each table:

SELECT TOP(10) *
    , %%PHYSLOC%%
FROM dbo.TestBit
    CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%);

SELECT TOP(10) *
    , %%PHYSLOC%%
FROM dbo.TestBitNotNull
    CROSS APPLY fn_PhysLocCracker(%%PHYSLOC%%);

The DBCC PAGE command can be used with the last option set to "3" to see the actual column values stored on the page, along with quite a bit of detail about each row "slot".  In my two tables above, the first page of each table was 334 and 342 respectively.

DBCC PAGE (2, 1, 334, 3) WITH TABLERESULTS;

DBCC PAGE (2, 1, 342, 3) WITH TABLERESULTS;

The output for slot 0 from each of the DBCC PAGE commands above shows the following for the table with the nullable column:

And this, for the column that is not nullable:

The "memory dump" value in the 2nd column on the first row shows the actual data stored on disk in hex format; both variants are precisely the same.

Indeed, when looking at the on-disk size for both tables using this query:

SELECT o.name
    , i.name
    , p.partition_number
    , p.rows
    , UsedMB = au.used_pages / 8192E0
    , TotalMB = au.total_pages / 8192E0
    , AvgRowsPerPage = p.rows / CONVERT(DECIMAL(10,2), au.used_pages)
FROM sys.allocation_units au WITH (NOLOCK)
    INNER JOIN sys.partitions p WITH (NOLOCK) ON ((au.type = 1 OR au.type = 3) AND au.container_id = p.hobt_id) OR (au.type = 2 AND au.container_id = p.partition_id)
    INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
    INNER JOIN sys.objects o WITH (NOLOCK) ON p.object_id = o.object_id
    INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id
WHERE o.name = 'TestBit'
    OR o.name = 'TestBitNotNull'
ORDER BY o.name;

We see both tables are identically sized:

My conclusion in light of the above data is that it is probably easier just to use a non-nullable column with a default value of 0 since that eliminates the potentially problematic null handling required for nullable columns.

Where the null bitmap does help is when you have more than 8 nullable bit fields.  If you take my sample tables TestBit and TestBitNotNull and give them 16 bit fields each, you'll see the following table sizes for 1,000,000 rows:


The clustered index structure is the table in SQL Server.
In order to remove a filegroup, you first need to remove all data from the filegroup.  There are several ways of doing that, including using the GUI, like this:

Right-click the table, and choose "Design"
Right-click the "empty" area of the top design panel, and choose "Properties" like this:

In the "Properties" pane (typically shown to the right of the "Design" pane), you open the "Regular Data Space Specification" and change the name of the "Filegroup or Partition Scheme Name" to the name of the target filegroup:

Be aware, this method will result in SSMS generating code that actually drops-and-recreates the table.
An alternate method of doing that would be to rebuild the indexes for the table, specifying the target filegroup in the rebuild command.  I've written a blog-post showing the code I use to accomplish that over at SQLServerScience.com


cons of linked server providers not running in "allow in process"

That option limits the datatypes you can access through the linked server - you'll not be able to access text / ntext / image columns through an externally instantiated linked server, nor row spilling ( MAX ) columns.  Not a huge loss, generally.


You can use the function substring() with a regular expression. But you need to define your requirements exactly. Like markp commented, there is ambiguity with multiple matches.

SELECT substring($string FROM '{conn-to,(.*?)},')

The part between parentheses is returned. This returns the first match, and the non-greedy quantifier *? prefers the shortest match.


Not null has nothing to do with referential integrity. If you want to require an address ID here, that's separate from adding a foreign key link to the Address table.

alter table person_address modify column address_addressID int(11) not null;

That statement means you MUST have a value in this column. That's all it means; nothing more or less.

To link this table to the Address table (assuming that is the table's name), you need this:

ALTER TABLE person_address
ADD FOREIGN KEY (address_addressID)
REFERENCES address_tbl_name (addressID)
ON UPDATE CASCADE;

I've no idea what the primary key field is named or what that table looks like, so replace address_tbl_name and addressID in line 3 with appropriate field/table name.


How To Access My PostgreSQL Server From Another PC Securely?

How to connect should be trivial: fill the appropriate values into the pgAdmin dialog (host, port, user, database). If I remember correctly, pgAdmin calls the database "administrative database" or similar.

This is fairly secure, if you want to do better, here are some ideas:

Use the scram-sha-256 authentication method rather than md5. This requires that you change password_encryption to scram-sha-256 on the server and assign a new password to the user.
Limit the pg_hba.conf entry so that only one user can connect to one database from a single host, for example

host databasename username 12.34.56.78/32 scram-sha-256

For better security, don't allow remote connections as a superuser.
For transport security, you can enable SSL connections. Without that, the communication between database client and database server is unencrypted.


I would go with the view for a few reasons:

It is obvious how the sum is generated with a view and it would always be correct.
You won't be able to fully trust the data of a updated column. ie life happens.  (The importance of this depends on the consequences of  bad data.)
This is essentially a premature optimization in a relatively lightly used system.

I don't think there is a good reason given the circumstances you described to go with the column/trigger/stored proc at this time.


Different column for different conditions when joining

AFAIK the only other way is to run 2 queries and add the columns in the application.

Your solution can work with only one JOIN if you put some IF's in your GROUP_CONCAT's:

`GROUP_CONCAT(IF(a.key = 'format',a.value,NULL))` as formats,
`GROUP_CONCAT(IF(a.key = 'category',a.value,NULL))` as categories,
...

It is debatable if the single JOIN option is better...


what server role that needs to be configured for the user to enable sql server agent

from BOL :

  To configure a user to create or execute Microsoft SQL Server Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following SQL Server Agent fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole.


Repair Database using .FRM .IBD .OPT Files ( Hard copy mysql folder )

FINALLY
Thanx Dexter!
Answer link after about 6hours searching the internet:
https://stackoverflow.com/a/56641702/12873930
###Files require to restore
xampp(old folder)/mysql/data/
ibdata1
ib_logfile0
ib_logfile1
<databasename>/*.frm
<databasename>/*.ibd

###Step 1

After installed the same version of xampp.
Do not start the apache or myql

###Step 2

Go to the mysql/data folder and replace the ibdata1, ib_logfile0, and ib_logfile1
Now copy paste your database folder from your old xampp backup to the newly installed xampp folder c:/xampp/mysql/data/ that contain .frm and .ibd files, If you are not sure try with one database.

###Step 3

Go to c:/xampp/mysql/bin and look for my.cn.
Open the my.cn file and look for #skip-innodb and under that look for the line that says innodb_log_file_size=5M change it to 170M. innodb_log_file_size=170M. This is your log file size and if you are not sure just set it to 170

###Step 4

Now open the file mysql_start.bat (Windows Batch file) that is in the c:/xampp/ directory.

Add –innodb_force_recovery=6 after the ... --console
 ....

 mysql\bin\mysqld --defaults-file=mysql\bin\my.ini --standalone --console
 –innodb_force_recovery=6
 if errorlevel 1 goto error
 goto finish

###Step 5

Now Start your Apache and Mysql.
Go to your phpmyadmin and check for your database and its tables. if you do not get any errors you are on the right track.
Stop the Apache and Mysql and copy paste the rest of the databases.


MySQL - speed up execution of ALTER TABLE

create a new table like your source table.
alter that new table the way you want.
insert your data into the new table.
create indexes etc. as needed on the new table.
rename your old table to something like ..._old or whatever.
rename your new table to the former name of the old one.
copy any missing rows from the _old table to the new one.
done :)


Connecting 4 tables - finding some differences between them

you don't need to join all 4 tables, this will show the missing rows

select id, fk_id_chest, x, y from SOL_PUBLIC.COORD@RACDB
minus
select id, fk_id_chest, x, y from SIM_SOL.COORD


Splitting a csv bulk import to postgres into smaller blocks using psycopg2

I have a number of csv-files that I would like to import into my postgres-database. Since each file is quite large (200m-2bn records/file) I believe it would be better to add each file in smaller batches.

Probably not. It'll work just fine. You're probably better off running the CREATE TABLE and COPY in the same transaction though, that way you generate minimal WAL.


Join three tables that share a common column

There are (at least) two ways to write FULL joins between more than 2 tables. Using FULL JOIN multiple times, the expression in the ON condition gets a bit longer but it's pretty simple:

SELECT
    COALESCE(t1.Hostname, t2.Hostname, t3.HostName) AS Hostname,
    t1.OS,
    t1.Confidence,
    t2.Manufacturer,
    -- the rest, non common columns
FROM Table1 AS t1
  FULL OUTER JOIN Table2 AS t2
    ON t2.Hostname = t1.Hostname
  FULL OUTER JOIN Table3 AS t3
    ON t3.Hostname = COALESCE(t1.Hostname, t2.Hostname) ;

Using first a UNION of the common column, then LEFT joins:

SELECT
     u.Hostname,
    t1.OS,
    t1.Confidence,
    t2.Manufacturer,
    -- the rest, non common columns
FROM
    ( SELECT Hostname FROM Table1 UNION
      SELECT Hostname FROM Table2 UNION
      SELECT Hostname FROM Table3
    ) AS u
  LEFT OUTER JOIN Table1 AS t1
    ON t1.Hostname = u.Hostname
  LEFT OUTER JOIN Table2 AS t2
    ON t2.Hostname = u.Hostname
  LEFT OUTER JOIN Table3 AS t3
    ON t3.Hostname = u.Hostname ;


MySQL 8.0.22 Trigger won't run unless definer is logged in

Everything is fine, with  your triggers, as long as you don't have also a Trgger on updowncount

CREATE TABLE `status` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `systemname` char(40) DEFAULT NULL,
  `status` char(40) DEFAULT NULL,
  `lastchecked` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=444 DEFAULT CHARSET=utf8

CREATE TABLE `updowncount` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `upcount` int DEFAULT NULL,
  `downcount` int DEFAULT NULL,
  `systemname` text,
  `lastupdate` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

 CREATE TRIGGER `countsystemadd` BEFORE INSERT ON `status`
 FOR EACH ROW BEGIN
       SET @COUNT=(SELECT COUNT(*) FROM `updowncount` WHERE updowncount.`systemname`= NEW.`systemname`);
       IF @COUNT=0 THEN
        INSERT INTO `updowncount` (upcount, downcount, systemname, `lastupdate`) VALUES (0, 0, NEW.`systemname`, NOW());
       END IF;
END

 CREATE TRIGGER `countupdate` AFTER INSERT ON `status`
 FOR EACH ROW BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`);
       IF @COUNT > 0 THEN
        IF NEW.`status` = 'UP'
        THEN
        UPDATE `updowncount`
        SET `upcount` = `upcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        ELSE
        UPDATE `updowncount`
        SET `downcount` = `downcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        END IF;
    END IF;
 END

INSERT INTO status (`systemname`,`status`) VALUES  ('SystemA','UP')

SELECT * FROM updowncount

id | upcount | downcount | systemname | lastupdate
-: | ------: | --------: | :--------- | :------------------
18 |       1 |         0 | SystemA    | 2020-12-15 19:18:43

db<>fiddle here


Physical only checkdb is failing, but full one is completed successfully

Yes, this a defect in SQL Server 2017 up to CU14. There was a similar issue with nonclustered columnstore indexes in SQL Server 2016.

It's detailed in this video: CHECKDB Bug With SQL Server 2017 And ColumnStore Indexes

A more portable repro of the issue is this:

SELECT *, CONVERT(NVARCHAR(MAX), m.text + m.text + m.text + m.text + m.text) AS texty
INTO dbo.corrupt
FROM sys.messages AS m;

CREATE CLUSTERED COLUMNSTORE INDEX cx_corrupt ON dbo.corrupt;

DBCC CHECKDB WITH PHYSICAL_ONLY;

DROP TABLE dbo.corrupt;


innodb_thread_concurrency seems evil as it enlarge table size by big factor

I suspect I will get down-voted for this Answer, but I think these things need to be said.  (And, no, I don't know the specific reasons for the strange sizes.)

Two simplified replies:

InnoDB plays a lot of games in its attempts to trade off speed and space.
Benchmarks rarely reflect anything in the real world.

Some factors (including possible explanations):

When there are multiple inserts into one table, InnoDB takes effort to minimize the impact of one insert on the other -- this may involve premature block splits, duplication of rows awaiting commits, MVCC, etc.
AUTO_INCREMENT is a bit tricky.  Your benchmark is relatively simpleminded.  In real life, there might be "burned" ids, leading to gaps in the values.  This is (I assume) a deliberate efficiency measure -- gain speed, but waste ids.  Note that there are various AI modes (see innodb_autoinc_lock_mode).
BTrees are inherently sloppy.  When a block is "split", it becomes two half-full blocks.  In the single-threaded benchmark this is not a big issue since there is no contention for the 'last' block.  And InnoDB has some optimizations for such.
BTrees are even more sloppy in the presence of MVCC -- since multiple copies of a row live for some time.
BTree blocks (in InnoDB) are not necessarily recombined promptly.
Allocation in in pages, extents, segments.  See here .  This can lead to megabytes of un-recouped space.  Furthermore, there is no 'visibility' into how much space is wasted at page/extent/segment level.
Concurrency of 100 (especially with only 12 processors) is asking for a lot of thrashing of locks in InnoDB.  Oracle, even with 5.7, does not brag above about 64.  I find that 100 leads to InnoDB stumbling over itself, and not necessarily finishing the benchmark any sooner than with a smaller number.  That is, excessive currency can lead to terrible latency without necessarily helping throughput.
10K rows may lead to an excessive use of the undo buffer (etc), thereby actually be slower than committing sooner.
Your "project will have billions of records" -- but how fast will they be inserted, and from how many different connections, and will they be batched or single-row inserted, etc.?
My take on high speed ingestion .

More (after Update 2)

innodb_buffer_pool_instances = 16
innodb_doublewrite = off  -- this may be safe since the drive and the page_size are the same.
SSDs make 150 individual inserts/sec not a problem
innodb_page_size=4k -- be aware that all tables will have to live with this.  It limits rows to under 2KB.  BTrees will be a little deeper, but I don't see that as a problem.
With the "delete after a year", I strongly recommend using PARTITION BY RANGE(TO_DAYS(...)) with 14 (monthly) or 55 (weekly) partitions.  This will make the deletes have essentially no impact.  A DELETE, even daily, would probably cause hiccups.  More discussion here .
How fast did the mysqlslap run (inserts/sec)?  Probably a lot faster than 150.  If your 150 is reasonably steady, then you will not experience concurrency=100, possibly rarely =10.  I don't see a problem ingesting your data.
If you need to 'normalize' (ex: country, etc) the data as you load it, see my ingestion link.
What are the SELECTs like?  They will be competing with the INSERTs.  I suspect 200 selects/sec, if they are point queries, could coexist with the inserts.  If you expect 1000, we need to think further.
With partitioning, if the excessive size persists, here is a fix...  REORGANIZE or REBUILD, not OPTIMIZE, a weekly partition after it is fully built.  That way, the excessive disk usage can be freed.  Be sure to use innodb_file_per_table before creating the table.


Delete Old Archivelogs from missing database

We finally deleted the files manually and OEM is fine.

We ran this query to get the delete scripts:

SELECT 'alter diskgroup '
       || gname
       || ' drop file ''+'
       || gname
       || Sys_connect_by_path(aname, '/')
       || ''';' a
FROM   (SELECT b.name            gname,
               a.parent_index    pindex,
               a.name            aname,
               a.reference_index rindex,
               a.system_created,
               a.alias_directory
        FROM   v$asm_alias a,
               v$asm_diskgroup b
        WHERE  a.group_number = b.group_number)
WHERE  alias_directory = 'N'
       AND system_created = 'Y'
START WITH ( MOD(pindex, Power(2, 24)) ) = 0
           AND rindex IN (SELECT a.reference_index
                          FROM   v$asm_alias a,
                                 v$asm_diskgroup b
                          WHERE  a.group_number = b.group_number
                                 AND ( MOD(a.parent_index, Power(2, 24)) ) = 0
                                 AND a.name = '&DATABASENAME')
CONNECT BY PRIOR rindex = pindex;

Replace &DATABASENAME with your old database name and then run the scripts.

Important note: you would normally not do this. I'm sure my database has been deleted and that the remaining files are completely useless. If your intent is to clean obsolete archive logs, use RMAN instead. It's safe and really easy.


Where condition execution sequence in MySQL

There is no order, subject to standard operator precedence.

SQL is a declarative language. You tell the engine "what to get", "how to do it". So the engine does not evaluate left to right in code order, nor does it generally short circuit.

For example:

the engine attempts to match the predicates against a suitable index. The index key columns order may not match the WHERE clause order so it will ignore the WHERE order.
it may decide that the 2nd condition is cheaper to evaluate in order to limit rows before applying the 1st condition


SQL Visual Studio Find and Replace in Publish Profile

When writing cross-database code, I prefer to avoid three-part name references. The variability of the $(OtherDatabase) that you describe is the precise reason why I avoid three-part names. Someday, DatabaseA will be renamed to DatabaseC and have to update a bunch of references in code. In your case, you can just do a new build, but it's still a pain.

Instead, create synonyms in the project for each cross-database reference. In the synonym, you will use the SSDT project reference variable once per object:

CREATE SYNONYM dbo.PurchaseOrder FOR [$(OtherDatabase)].dbo.PurchaseOrder;

Then, every time you reference the cross-database object, you only need to use the two-part name. Developers should use neither the [$(OtherDatabase)] nor  a placeholder DatabaseA reference for find-and-replace.

FROM dbo.PurchaseOrder

This has the added benefit that all of the code will always be identical across different iterations of the database. The only object types that will not match will be synonyms. Rather than inspecting code, you can simply query the sys.synonyms DMV to determine where dbo.PurchaseOrder points.

In this query, name would return the two-part names (ex PurchaseOrder), and base_object_name would return the three-part names (ex DatabaseA.dbo.PurchaseOrder):

SELECT name, base_object_name FROM sys.synonyms;

If you ever have a database that needs to be repointed from DatabaseA to DatabaseB, you can simply re-point the synonyms via a series of DROP SYNONYM & CREATE SYNONYM statements, without having to perforem ALTER statements for procedures/views/functions/triggers.


Is there a way to examine SQL Jobs for references to linked servers?

To query for jobs, you just need to look in msdb's job tables:

SELECT j.name
  FROM msdb.dbo.sysjobs AS j
  INNER JOIN msdb.dbo.sysjobsteps AS s
  ON j.job_id = s.job_id
  WHERE s.command LIKE '%TheLinkedServer%';

Also syscomments is a backward compatibility view - it is not the best way to search for references in objects, particularly because any object > 4000 characters will be stored in multiple rows and could, potentially, have the linked server name straddle two rows. Much safer would be:

SELECT o.name FROM sys.objects AS o
  INNER JOIN sys.sql_modules AS m
  ON o.[object_id] = m.[object_id]
  WHERE m.definition LIKE '%TheLinkedServer%';

And finally, sp_msforeachdb is completely unreliable (see here and here for a better replacement).

Here is a handy search procedure I wrote last year that may be useful:

CREATE PROCEDURE dbo.FindString
   @search_string            NVARCHAR(4000),
   @database_list            NVARCHAR(MAX) = NULL,
   @case_sensitive           BIT = 0,
   @include_jobs             BIT = 0,
   @include_columns          BIT = 0,
   @include_parameters       BIT = 0,
   @include_system_objects   BIT = 0,
   @include_system_databases BIT = 0
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE
       @init_sql  NVARCHAR(MAX),
       @run_sql   NVARCHAR(MAX),
       @dbname    NVARCHAR(128),
       @all_text  NVARCHAR(10),
       @coll_text NVARCHAR(50);

   CREATE TABLE #t
   (
       [database]      SYSNAME,
       [schema]        SYSNAME,
       [object]        SYSNAME,
       [type]          SYSNAME,
       [create_date]   DATETIME,
       [modify_date]   DATETIME,
       [definition]    NVARCHAR(MAX)
   );

   CREATE TABLE #j
   (
       [job_name]      SYSNAME,
       [step_id]       INT,
       [step_name]     SYSNAME,
       [create_date]   DATETIME,
       [modify_date]   DATETIME,
       [definition]    NVARCHAR(MAX)
   );

   CREATE TABLE #cp
   (
       [database]      SYSNAME,
       [schema]        SYSNAME,
       [object]        SYSNAME,
       [type]          SYSNAME,
       [create_date]   DATETIME,
       [modify_date]   DATETIME,
       [param]         NVARCHAR(128),
       [column]        NVARCHAR(128)
   );

   SELECT
       @all_text = CASE @include_system_objects
           WHEN 1 THEN N'all_' ELSE N'' END,
       @coll_text = CASE @case_sensitive
       WHEN 1 THEN N'COLLATE Latin1_General_BIN' ELSE N'' END;

   SET @init_sql = N'SELECT
           [database] = ''$db$'',
           [schema]   = QUOTENAME(s.name),
           [object]   = QUOTENAME(o.name),
           [type]     = o.type_desc,
           o.create_date,
           o.modify_date,
           m.[definition]
       FROM
           $db$.sys.$all$sql_modules AS m
       INNER JOIN
           $db$.sys.$all$objects AS o
           ON m.[object_id] = o.[object_id]
       INNER JOIN
           $db$.sys.schemas AS s
           ON o.[schema_id] = s.[schema_id]
       WHERE
           m.definition $coll$
            LIKE N''%'' + @search_string + ''%'' $coll$;';

   SET @init_sql = REPLACE(REPLACE(@init_sql,
       '$all$', @all_text), '$coll$', @coll_text);

   SET @search_string = REPLACE(@search_string, '''', '''''');

   DECLARE c CURSOR
       LOCAL STATIC FORWARD_ONLY READ_ONLY
       FOR
           SELECT QUOTENAME(d.name)
               FROM
                   sys.databases AS d
               LEFT OUTER JOIN
                   dbo.SplitStrings_XML(@database_list, N',') AS s
                   ON 1 = 1
               WHERE
                   (
                     LOWER(d.name) = LOWER(s.Item)
                     OR NULLIF(RTRIM(@database_list), N'') IS NULL
                   )
                   AND d.database_id BETWEEN CASE @include_system_databases
                   WHEN 1 THEN 1 ELSE 5 END AND 32766
               ORDER BY d.name;
   OPEN c;

   FETCH NEXT FROM c INTO @dbname;

   WHILE @@FETCH_STATUS = 0
   BEGIN
       SET @run_sql = REPLACE(@init_sql, N'$db$', @dbname);

       INSERT #t
       EXEC sp_executesql
           @run_sql,
           N'@search_string NVARCHAR(4000)',
           @search_string;

       IF @include_columns = 1
       BEGIN
           SET @run_sql = N'SELECT
                [database] = ''$db$'',
                [schema]   = QUOTENAME(s.name),
                [object]   = QUOTENAME(o.name),
                [type]     = o.type_desc,
                o.create_date,
                o.modify_date,
                NULL,
                c.name
            FROM
                $db$.sys.$all$columns AS c
            INNER JOIN
                $db$.sys.$all$objects AS o
                ON c.[object_id] = o.[object_id]
            INNER JOIN
                $db$.sys.schemas AS s
                ON o.[schema_id] = s.[schema_id]
            WHERE
                c.name $coll$
                  LIKE N''%'' + @search_string + ''%'' $coll$;';

           SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql,
               '$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname);

           INSERT #cp
           EXEC sp_executesql
               @run_sql,
               N'@search_string NVARCHAR(4000)',
               @search_string;
       END

       IF @include_parameters = 1
       BEGIN
           SET @run_sql = N'SELECT
                [database] = ''$db$'',
                [schema]   = QUOTENAME(s.name),
                [object]   = QUOTENAME(o.name),
                [type]     = o.type_desc,
                o.create_date,
                o.modify_date,
                p.name,
                NULL
            FROM
                $db$.sys.$all$parameters AS p
            INNER JOIN
                $db$.sys.$all$objects AS o
                ON p.[object_id] = o.[object_id]
            INNER JOIN
                $db$.sys.schemas AS s
                ON o.[schema_id] = s.[schema_id]
            WHERE
                p.name $coll$
                  LIKE N''%'' + @search_string + ''%'' $coll$;';

           SET @run_sql = REPLACE(REPLACE(REPLACE(@run_sql,
               '$all$', @all_text), '$coll$', @coll_text), '$db$', @dbname);

           INSERT #cp
           EXEC sp_executesql
               @run_sql,
               N'@search_string NVARCHAR(4000)',
               @search_string;
       END

       FETCH NEXT FROM c INTO @dbname;
   END

   CLOSE c;
   DEALLOCATE c;

   SELECT 'Objects:';

   SELECT
       [database],
       [schema],
       [object],
       [type],
       [definition] = CONVERT(XML, '<?query --
           USE ' + [database] + ';'
           + CHAR(13) + CHAR(10) + 'GO'
           + CHAR(13) + CHAR(10) + [definition] + ' --?>'),
       first_line = (DATALENGTH(abbrev_def)
           -DATALENGTH(REPLACE(abbrev_def, CHAR(13), '')))/2 + 1,
       create_date,
       modify_date
   FROM
   (
       SELECT
           *,
           [count] = (DATALENGTH([definition])
               - DATALENGTH(REPLACE([definition], @search_string, '')))
               /DATALENGTH(@search_string),
           abbrev_def = SUBSTRING([definition], 1,
               CHARINDEX(@search_string, [definition]))
       FROM #t
   ) AS x
   ORDER BY [database], [schema], [object];

   IF @include_jobs = 1
   BEGIN
       SELECT 'Jobs:';

       SET @run_sql = N'SELECT
                job_name = j.name,
                s.step_id,
                s.step_name,
                j.date_created,
                j.date_modified,
                [definition] = s.command
            FROM msdb.dbo.sysjobs AS j
            INNER JOIN msdb.dbo.sysjobsteps AS s
            ON j.job_id = s.job_id
            WHERE s.command $coll$
              LIKE ''%'' + @search_string + ''%'' $coll$
            ORDER BY j.name, s.step_id;';

       SET @run_sql = REPLACE(@run_sql, '$coll$', @coll_text);

       INSERT #j EXEC sp_executesql
           @run_sql,
           N'@search_string NVARCHAR(4000)',
           @search_string;

       SELECT
           job_name,
           step_id,
           step_name,
           [command] = CONVERT(XML, '<?query --
               ' + [definition] + ' --?>'),
           create_date,
           modify_date
       FROM #j;
   END

   IF @include_columns = 1 OR @include_parameters = 1
   BEGIN
       SELECT 'Columns/parameters';

       SELECT
           [database],
           [schema],
           [object],
           [type],
           [param],
           [column],
           create_date,
           modify_date
       FROM #cp
       ORDER BY [database], [schema], [object], [param], [column];
   END

   DROP TABLE #t, #j, #cp;
END
GO

It relies on a split function like this one (or your own, if you already have one or, better yet, STRING_SPLIT() if you are on SQL Server 2016 or better and can deal with its limitations):

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(3)
)
RETURNS TABLE
AS
   RETURN
   (
       SELECT Item = CONVERT(NVARCHAR(128), Item)
       FROM
       (
           SELECT Item = x.i.value('(./text())[1]', 'NVARCHAR(128)')
           FROM
           (
               SELECT [XML] = CONVERT(XML, ''
                    + REPLACE(@List, @Delimiter, '')
                    + '').query('.')
           ) AS a
           CROSS APPLY
           [XML].nodes('i') AS x(i)
       ) AS y
       WHERE Item IS NOT NULL
   );