Display both null and not null under same id but not both null or not null like XOR logic

Select NullTest.ID, NullTest.Col1
    From NullTest
    Join (Select ID From NullTest Where Col1 Is Not Null Group By ID)
         NotNulls
         On NotNulls.ID = NullTest.ID
    Join (Select ID From NullTest Where Col1 Is Null Group By ID)
         Nulls
         On Nulls.ID = NullTest.ID ;


Tracking Down Application Timeout Errors in SQL Server

Aaron Bertrand put me on the right track with his comment

  And I believe you should be able to filter on duration and error <> 0.

Created a server side trace using the tsql_duration profile template

Added the User Error Message Event
Added the following filters

error <> 0

error <> 1

severity <> 10

This avoided capturing the USE DATABASE commands

The error message captured by profiler was 2 - Abort and the Event Class was 10 RPC:Completed.


allcaller_allcaller_logs starts with start_time but in your query start_time is checked by BETWEEN - thats range scan, not direct ref, so the query cannot simply use remaining columns (I suppose you use MySQL 5.5, in 5.6 there is Index Condition Pushdown Optimization which would be able to utilize that index better). Your index seems to "cover" your query on that table, so optimizer decision is still a bit weird, but there may be other reasons (bad cardinality/statistics) which together override it.

Generally if you make special index for your query, you need to organize all direct ref ("=") columns first, then either those with range (BETWEEN or <,>...) or those used in order by if you have it.

As you have A.end_time != ... - such condition cannot be generally satisfied by index, so even if you need covering index, move such columns to the end of your index.

"which is not even matching the order of conditions in where clause" - this is misunderstanding of column order - WHERE clause evaluates all conditions as boolean expression and is free to reorder then any way it sees will be best, so order in columns in WHERE and order in index do not have to correspond in any way. For index it is better to order them by cardinality and actual usage.


Filter not working on Showplan XML in SQL Server trace

SHOWPLAN XML event does not have a column with cpu duration so you cannot filter by duration. You can only filter by columns mentioned in this article.

https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/showplan-xml-event-class

Few alternatives:

use filter by other allowed columns.
capture everything for as minimum time as practical, load in table and filter. Be careful about load generated by this process, especially on production server.
Trace for long running queries. Use that list to filter SHOWPLAN XML  event.


Try to put 'on paper' what you 'fields' that you need. Once you have that then you must group them based on what fields have a same identifier. In you case you might have 3 entities:

User with an ID and a name
Group with an ID and a name (with values like like pen and paper)
Material with an ID, the GroupID and a name

For the ToDo you can try yourself to figure it out.

Google on database normalisation to find some more information. You do not need to go down all the way but try to figure out the first 3 forms.


Using listener_name in 4 part naming convention gives sys.servers error

There isn't one place where I found everything written properly, which maybe is because not many people use cross AG dist txs,  so this is what needs to be done:

Have app make code change to use 4 part naming convention using the appropriate listener_name.
Create linked servers for all listeners on all nodes:

Script is from  https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-clustered-dtc-for-an-always-on-availability-group?view=sql-server-ver15 step 12:

IF NOT EXISTS (SELECT * FROM sys.servers where name = N'Listener1')
BEGIN
    EXEC master.dbo.sp_addlinkedserver @server = N'Listener1';
END

Do the same for the other listeners. above should suffice - you can do a distributed query test now (its given in the same step 12 of above link) , but as I am on SQL Server 2016 SP2+, I did this also:

ALTER AVAILABILITY GROUP AG_Name

   SET (

      DTC_SUPPORT = PER_DB

      );

Did the same for other AG's per  https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-availability-group-for-distributed-transactions?view=sql-server-2017#manage-unresolved-transactions


How to store likes to posts in Parse.com database?

For those purpose, I only use relations in the USER table with users ids stored as pointers inside.

Using a cloud code function, I provide a  parameter to add , del or whatever instructions to apply to a single or an array of users. Cloud gives the opportunity to create collateral events such as "a user like this"

As a reading step, a query is applied to the relation to retrieve parts or all users.

The column is named with the purpose such as I will add 2 columns to mention  respectively "like" , "dontLike"

However, if one need more than a single information , a second table needs to be created and linked also with a relation.
For example, a new table with a first column which is a regular pointer to users, other  columns to stored the additional attributes.
Which means that this new table must be first saved, to be able to stored its id in the relation.


RDS MySQL instance is unavailable, probably during binlog rotation

Your graph shows a binary log purge launched at approximatively 4am the 23th.

You can search for a query like:

PURGE BINARY LOGS BEFORE '2014-04-23 04:00:00';
-- or
PURGE MASTER LOGS BEFORE '2014-04-23 04:00:00';

If you are sure that your MySQL is a standalone instance, there is no know issues with the PURGE BINARY LOGS statement and I see that your binary log is 1.5Mb so i'm sure your system can manage that purge without pain. In a Master/Slave infrastructure, you can purge binary logs before they been "played" on slaves, the result is the slaves replication goes down (with impact on the apps).

Max.


Swapping TDE Cert Slow, any way to improve performance?

Is there a faster way to change TDE Certificates that anyone knows of?

If you're actually needing to change the certificate that is doing the encryption (it protects the data) then the only faster way is to get a faster disk subsystem, new motherboard/cpus that support AESNI, and upgrade to SQL Server 2016.

If, however, you don't need to rotate the actual encrypting key but one of the protecting keys (Server Cert in this case, not the DEK) it's a very short mostly but not all metadata operation that can be accomplished via ALTER DATABASE ENCRYPTION KEY [Blah] ENCRYPTION BY SERVER CERTIFICATE [Blah2]. That will change the server certificate used to protect the DEK which will be extremely fast as the physical data does not need to be decrypted and encrypted again using the new DEK.


First you should check the error logs & post for clarification.
This can happen doue to-

Your slave database is differ to its master when replication start.
So before replication sync both the server.

If your replication is statement type & replicate some specific database. ex-

use not_replica;
alter table replica.test ....
use replica;
insert into replica.test ...

here alter command for test would not replicate. because you are using not_replica database. after that insert statement would fail & replication stopped.


Offset vs Where performance for pagination with index

Yes, there is a performance difference, especially with big OFFSETs. With OFFSET you ORDER BY your indexed column, and then limit the result set by removing the first x rows indicated by OFFSET x. All the rows still have to be read from disk.

With WHERE you ORDER BY your indexed column, but then only read the range of rows specified by your indexed WHERE condition from disk.

For more details, see: We need tool support for keyset pagination (On the Use The Index, Luke blog)


What is preventing DROP TABLE from removing a Postgres table?

Community Wiki answer, originally a comment on the question by a_horse_with_no_name

You need to end every SQL statement with ;. You didn't do that and thus your statement was not executed at all. Run DROP TABLE pnt; instead. See e.g. here: Understanding postgres prompts


It is not possible, because you cannot create a backup of an offline database.

From MSDN:

  Without the NO_TRUNCATE option, the database must be in the ONLINE
  state. If the database is in the SUSPENDED state, you might be able to
  create a backup by specifying NO_TRUNCATE. But if the database is in
  the OFFLINE or EMERGENCY state, BACKUP is not allowed even with
  NO_TRUNCATE


How to recursively update a same string?

WITH RECURSIVE cte AS (
    SELECT employee, 1 id
    FROM t1
    UNION ALL
    SELECT REPLACE(employee, first_name, ''), id+1
    FROM cte
    JOIN t2 USING (id)
)
SELECT REGEXP_REPLACE(employee, ' +', ' ') employee
FROM cte
WHERE id > ( SELECT MAX(id)
             FROM t2 )

fiddle

If t2.id values do not start with 1 or have omissions, you must re-enumerate t2 records in CTE using ROW_NUMBER().

UPDATE.

Possible problem - some first_name is a substring of some employee (John and Johnson, for example). If so use spaces as additional wrappers:

WITH RECURSIVE cte AS (
SELECT employee || ' ' employee, 1 id
FROM t1
UNION ALL
SELECT REPLACE(employee, ' ' || first_name || ' ', ' '), id+1
FROM cte
JOIN t2 USING (id)
)
SELECT REGEXP_REPLACE(employee, ' +', ' ') employee
FROM cte
WHERE id > ( SELECT MAX(id)
             FROM t2 )

fiddle

PS. Trim excess trailing space if needed.


How to restore a database if full database is corrupt?

Database corruption can exist in the database, FULL backups and DIFF backups. This is because they contain data pages. LOG backups are different and don't contain database corruption.

All of your backups are susceptible to file corruption, but that's different. We're talking database corruption here.

If you need to do a restore due to database corruption, do one last LOG backup so that you can restore without data loss, called the tail.

Now time to start the restores. Start with the FULL backup that is before the last successful DBCC CHECKDB. If you don't have a last successful DBCC CHECKDB, you'll need to run it after each restore to see what's good and what's not.

Then apply a DIFF backup if you have one that is relevant to the FULL you just restored.

Lastly, restore the entire LOG chain since that DIFF (or FULL if you didn't use a DIFF) backup and then apply that final LOG backup (the tail).

You didn't ask this question, but I'm including it anyway. It makes sense to have your FULL backup job occur before the DBCC CHECKDB job so that you know which backups are good if you ever encounter database corruption that requires you to do a restore.


The documentation states that you should not enable snapshot isolation on SSRS database.

Check here:

Enabling Snapshot Isolation on the Report Server Database.
You should follow the instructions there to turn it off.

Excerpt from the docs:
"...You cannot enable snapshot isolation on the report server database. If snapshot isolation is turned on, you will encounter the following error: "The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available."

As for the cause of the blocking, and ways to fix them, here are some links to help you:

SSRS Blocking Processes
SSRS issues locks to provide a multi-threading mechanism. Once all threads running the report that you wrote, end, the lock is removed. So if you see these blocks regularly, it is usually due to a slow query.
Advice on setting up a Reporting server
(advice on how to set up a second reporting instance)


If you do not have access to the code from the applications accessing your database, using the Profiler over a reasonable period of time, as commented by @nelson-casanova, is your best option.

If you do have access to the application code, check there.  Also check the definition of any stored procedures, functions, or other views.

I presume you need the column removed for security reasons?  If so, perhaps you can modify your view to use IS_SRVROLEMEMBER and return those columns only when authorized? Copy your view and name it 'original_view', copy your view and edit your columns then and name it 'modified_view.  Now edit your view and call the new views:

IF IS_SRVROLEMEMBER ('role_view_all') = 1
   select * from original_view;
ELSE IF IS_SRVROLEMEMBER ('role_view_all') = 0
   select * from modified_view;

If there is an application that you missed in your Profiler run, you can temporarily add that application's user(s) to the 'role_view_all' role, while developers sort out a workaround or fix their queries.


Simulate effective LEAD(1) or LEAD(2) in MySQL

Use MySQL 8, which supports Window Functions
Hack around it..

Generate a sequence of IDS. This whole project makes no sense without them anyway because the result set of a SQL query isn't guaranteed without an ORDER BY.
LEFT-OUTER JOIN the table with itself self join on id+1 = id

MySQL specific, emulate lag with user-defined variables

That looks something like this in MySQL,

SELECT t1.digit, t2.digit
FROM (
    SELECT digit, @rownum1 := @rownum1 + 1 AS id
    FROM
     alfabet  , (SELECT @rownum1 := 0) r
    ORDER BY
     alfabet.digit ASC
) AS t1
LEFT OUTER JOIN (
    SELECT digit, @rownum2 := @rownum2 + 1 AS id
    FROM
     alfabet  , (SELECT @rownum2 := 0) r
    ORDER BY
     alfabet.digit ASC
) AS t2
  ON t1.id +1 = t2.id;

You can even make that simpler with

SELECT digit, previous
FROM (
    SELECT digit, @prev previous, @prev := digit AS prev
    FROM
     alfabet  , (SELECT @prev := '') r
    ORDER BY
     alfabet.digit ASC
) AS t1;


Multiple to_json(array_agg), separate joins

The basic problem is this:

Two SQL LEFT JOINS produce incorrect result

Here is one way to avoid this problem and improve some other details while being at it:

SELECT m.*, u.users, r.replies
FROM   messages m
LEFT   JOIN LATERAL (
   SELECT json_agg(u) AS users
   FROM   (
      SELECT id, fullname, email, company
      FROM   users
      WHERE  id IN (m.user_id, m.to)
      -- no order by?
      ) u
   ) u ON TRUE
LEFT   JOIN LATERAL (
   SELECT json_agg(r) AS replies
   FROM   (
      SELECT *
      FROM   messages
      WHERE  message_id = m.id
      ORDER  BY created_at
      ) r
   ) r ON TRUE
WHERE  :to IN (m.user_id, m.to)
AND    m.to IS NOT NULL;

Use json_agg() instead of to_json(array_agg()). Simpler, faster.
Aggregate the subquery on users right away in a LATERAL join, thus avoiding the main problem. Related:

Create smaller row from alias and preserve column names

This:

WHERE  m.to = :to OR
      (m.to IS NOT NULL AND m.user_id = :to)

can be expressed simpler:

WHERE  :to IN (m.user_id, m.to)
AND    m.to IS NOT NULL

This:

SELECT json_agg(r) AS replies
FROM   (
   SELECT *
   FROM   messages
   WHERE  message_id = m.id
   ORDER  BY created_at
   ) r

could be shortened to:

SELECT json_agg(r ORDER BY created_at) AS replies
FROM   messages r
WHERE  message_id = m.id

But a single ORDER BY in a subquery is typically faster than per-aggregate ORDER BY.


Oracle 9.2 - Bring standby up-to-date using RMAN

I do not have access to a 9.2 database currently, but this is the method for doing this:

Using RMAN Incremental Backups to Roll Forward a Physical Standby Database

Basically, you can issue a BACKUP INCREMENTAL FROM SCN ... to create the desired backup. However I suspect this feature is not available in versions before 10.2.0.1, as there is no reference of it in the 9.2 Data Guard documentation nor the 9.2 BACKUP reference, and the related MOS notes also specify the lowest version as 10.2.0.1.

Try the above syntax, but if it does not work, you will need to rebuild the standby.


Memory Settings for SQL Server, SSAS, and SSRS

we are not using the mulitdimensional model, not tabular

Do you mean NOT multi-dimensional ONLY TABULAR?

If so:
It's not best practice to install SSAS and SQL Server together on one server as the SSAS (Tabular) VertiPaq Engine works totally different than SQL Engine and it's (VertiPaq) is more memory intensive unless the models configured as DirectQuery mode. You could may have different VM for each of them if the license covered physical server cores

If not so:
I don't think that is practical as you must select either option TABULAR/MULTI-DIMENSIONAL during SSAS Installation. if you mean, there is no use of either SSAS options, you could simply uninstall it from the server

Yes, it's recommended to configure maximum server memory setting when there are multiple instances on the same server (other applications on database server not recommended), depending on the instance workload you could set max server memory setting, leaving minimum ~4GB to OS. I have seen ~16GB memory (for SSRS dedicated server) sufficient to handle around 150 users requests

Consider following settings are same as maximum server memory in SQL Server

In SSRS: WorkingSetMaximum
In SSAS: TotalMemoryLimit


Database design suggestions for a data scraping/warehouse application?

These are general recommendations, as you do not show the full extent of your queries to be performed (which kind of analytics you plan to do).

Assuming you do not need real time results, you should just denormalize your data at the end of the period, precalculate once your aggregated results on all necessary timeframes -by day, by week, by month-, and work only with summary tables. Depending on the queries you intend to do, you may not even need the original data.

If durability is not a problem (you can always recalculate statistics as raw data is elsewhere), you can use a caching mechanism (external, or MySQL 5.6 includes memcache), which works great for writing and reading key-value data on memory.

Use partitioning (can also be done manually), as with these kind of applications, usually the most frequently accessed rows are also the most recent. Delete or archive old rows to other tables to use our memory efficiently.

Use Innodb if you want durability, high concurrent writes and your most frequent accessed data is going to fit into memory. There is also TokuDB- it may not be raw faster, but it scales better when dealing with insertions on huge, tall tables and allows for compression on disk. There are also analytic-focused engines like Infobright.

Edit:

23 insertions/second is feasible in any storage with a bad disk but:

You do not want to use MyISAM- it cannot do concurrent writes (except on very specific conditions) and you do not want to have huge tables that become corrupted and lose data
InnoDB is fully durable by default, for better performance you may want to reduce the durability or have a good backend (disk caches). InnoDB tends to get slower on insertion with huge tables. The definition of huge is "the upper parts of the Primary key/other unique indexes must fit into the buffer pool" to check for uniqness. That can vary depending on the memory available. If you want scalability beyond that you have to partition (as I suggested above)/shard or use any of the alternative engines I mentioned before (TokuDB).

SUM() statistics do not scale on normal MySQL engines. An index increases performance, again, because most of the operations can be done on-memory, but one entry for each row has to still be read, in a single thread. I mentioned design alternatives (summary tables, caching) and alternative engines (column-based) as a solution to that. But if you do not need real-time result, but report-like queries, you shouldn't worry too much about that.

I suggest you to do a quick load test with fake data. I've had many clients doing analytics on MySQL of social networks without problems (well, at least, after I helped them :-) ), but you decision may depend on your actual non-functional requisites.


Providing an ad-hoc report facility for business using in MySQL and PHP?

I suppose you could wire up some gnarly PHP code that queries INFORMATION_SCHEMA for database objects and then presents them to your end-users using some glamorous jquery and HTML5 elements. Then you'd have to generate SQL from the user's input.

Or, you could go through a bit of effort to implement/deploy BIRT to your user base.

However, my personal preference for doing ad-hoc querying would probably be Pentaho. There's still the overhead of deploying this to your users, but I find their documentation and implementation to be straightforward and easy-to-follow. Here's a link to using Ad-Hoc reporting in the Pentaho suite.


Get all (non repeated) distribuidores with their paises given a producto_solicitud

2nd query:
Of course you get duplicate rows. 1 row per producto each fabricante is connected to - multiplied with the number of rows in pais each combination is connected to.

1st query:
An explicit JOIN binds before (groups of) comma-separated items in the FROM list. This is why you cannot reference fabricante in the JOIN condition between pais and distribuidor_producto_solicitud - that happens before the result of this operation is joined to fabricante. Per documentation:

  In the absence of parentheses, JOINs nest left-to-right. In any case
  JOIN binds more tightly than the commas separating FROM-list items.

Bold emphasis mine.

The unconditional CROSS JOIN (comma-notation) between fabricante and the rest is a mistake to begin with. You probably want:

SELECT DISTINCT f.*, p.*
FROM   negocio.distribuidor_producto_solicitud d
JOIN   nomencladores.fabricante_distribuidor   f ON f.id = d.fabricante_distribuidor_id
LEFT   JOIN nomencladores.pais_distribuidor_producto_solicitud pd
                                                 ON pd.distribuidor_producto_solicitud_id = d.id
JOIN   nomencladores.pais                      p ON p.id = pd.pais.id
WHERE  d.producto_solicitud_id = 1;

Gives you one row for every combination of (fabricante, pais). The LEFT JOIN is there to include fabricantes that have no pais attached.

You need the DISTINCT, because there can be any numbers of rows in distributor for each fabricante.

With your verbose naming convention I suggest to use table aliases to make the query more readable.


How to setup Oracle SSL using self-signed certificate between client and DB?

I found the answer.

In my post I've described how to create trusted and user certificates. As I guess trusted certificate is public key and user certificate is a private key(correct me if I am wrong).

Now I write how I have done this job.

--on DB server

Run owm(Oracle Wallet Manager) and:

Create new wallet.
Create certification request.
Export certification request.
using ssl.ca-0.1(Downloaded from http://www.openssl.org/contrib/) created self-signed root certificate using new-root-ca.sh and imported as a trusted certificate on DB server.
Created self-signed server certificate by running the sign-server-cert.sh and imported as an user certificate on DB server.

--On client server
Copied root certificate(generated previously).

Create new wallet.
Create certification request.
Import trusted certificate by choosing copied root certificate.

Edit tnsnames.ora file on client server to use TNS entry using protocol TCPS and port, for example, 2484.

Edit listener.ora file on DB server to listen port 2484 and protocol TCPS.

Open Net Manager and configure SSL on both sides.

--DB side

Indicate wallet directory.

I've unchecked "Require Client Authentication"
and other options what you want.

--Client side

Indicate wallet directory..

and other options what you want.


WITH RECOMPILE

This is a bit of a sledgehammer. It recompiles every statement in the module. Using OPTION (RECOMPILE) on the statements that are parameter-sensitive is a more targeted solution.

  Reassign parameter values to local variables and use those instead of the parameters

This has the same effect as OPTIMIZE FOR UNKNOWN i.e. a plan is generated based on average values. The plan does not recompile on each execution.

For more information please see my article Parameter Sniffing, Embedding, and the RECOMPILE Options.


This is a table hint that can be used in various statements (SELECT, INSERT, DELETE, UPDATE, MERGE). See the related page at msdn: Table hints and the linked Introduction to Memory-Optimized tables.
For further information about isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Syntax
WITH  ( <table_hint> [ [, ]...n ] )

<table_hint> ::=
[ NOEXPAND ] {
    INDEX  ( index_value [ ,...n ] )
  | INDEX =  ( index_value )
  | FORCESEEK [( index_value ( index_column_name  [ ,... ] ) ) ]
  | FORCESCAN
  ...
  | SNAPSHOT
  ...

...
SNAPSHOT
Applies to: SQL Server 2014 through SQL Server 2016.
The memory-optimized table is accessed under SNAPSHOT isolation.
SNAPSHOT can only be used with memory-optimized tables (not with disk-based tables).
For more information, see Introduction to Memory-Optimized Tables.


It would be much better if you used a proper type for dates, i.e. DATE and not VARCHAR, as that would give you several advantages:

integrity: prevents nonsense data to be inserted in the column, like `20181301', '20190133', '2018ABCd', 'today', 'Xmas19', etc.
storage and index size: DATE is 3 bytes vs 9 of VARCHAR(8).
usability: let you use the ready made conversions and format functions (not for the specific query but possibly for other ones).

If you can't change the type, it's not so bad though as your format is at least an order preserving one, so you can use conditions that can utilize indexes:

WHERE PostingDate >= '20180101'
  AND PostingDate  < '20190101'

which by the way can still be used when (and if) you convert the column type to DATE. Nothing would need to change.


The mongostat tool provides you with this information.

Example output of mongostat:

insert query update delete getmore command dirty used flushes vsize   res qrw arw net_in net_out conn     set repl                time
*0     2     *0     *0       0     2|0  0.0% 0.0%       0 3.18G 16.0M 0|0 1|0   556b   57.2k    2 replset  PRI May 22 09:17:02.431

From the example above, the query field has the value of 2. This means that at that moment in time, the system processes two queries per second.

For full description of the output fields of mongostat, please see the Fields section.


MySQL query 'going away' on executing INSERT ON DUPLICATE UPDATE statement with a 12524 character blob

This sounds like you have to increase the size of your MySQL Packets

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Since MySQL Packets can hold rows of data, larger items in the packet can cause a lot of packets to filter in-and-out to prevent whole chunks of related data from splitting during processing. This can be a silent killer of DB connections for no apparent reason. If fact, I wrote a post about how this can affect certain mysqldumps.

Try increasing the max_allowed_packet (256M) using the following command:

SET max_allowed_packet = 1024 * 1024 * 256;


Does implicit casting negatively affect index usage?

Such conversion often will occur, and they can often impact cardinality estimation (and hence can affect plan quality). It will depend though on data types, existing indexes, cardinality etc. For example this query might not be able to seek in any case, as SQL Server might be using a scan regardless.

You should be able to determine if this is an issue in your case by inspecting the execution plan - do you get a scan on eventCount if you first cast eventFactor to a smallint? What if you use a local smallint variable that would result in the same number of rows that would match the filter? We can't tell from here...


Is it possible to run an .exe file from a CLR function?

You can use xp_cmdshell for doing this.

As a little example from documentation:

CALL xp_cmdshell( 'dir > c:\\temp.txt' );

But take care: It's not possible to get e.g. STDOUT back into your database without output redirection.


Do id columns help (in speed) select statements?

Since you mentioned your typical queries involve grouping by string fields, I assume you have many duplicate values in these columns (otherwise grouping makes no sense).

You can definitely improve performance by creating lookup table[s] for values stored in someData1, someData2, someData3 with integer primary key and changing original someData1 to someData1_id . You still need index on someData1_id, but taking into account number of rows , the new index will be much smaller than index on string field, and as a result , much faster...


sp_publication_validation procedure doesn't seem to work properly

My explanation is that sp_publication_validation validates a Publication: set of Articles sent by Publisher and received by Subscriber. Since there were no data corruption in transit this SP does not report any errors.

To detect differences in replicated tables use SP_TABLE_VALIDATION.

  Either returns rowcount or checksum information on a table or indexed
  view, or compares the provided rowcount or checksum information with
  the specified table or indexed view. This stored procedure is executed
  at the Publisher on the publication database and at the Subscriber on
  the subscription database.

SP_TABLE_VALIDATION on MSDN


Prevent Primary Key increment when an INSERT statement fails in PostgreSQL

this is exactly what you are facing:

test=# CREATE SEQUENCE seq_a;
CREATE SEQUENCE
test=# SELECT nextval('seq_a');
 nextval
---------
       1
(1 row)

test=# BEGIN;
BEGIN
test=# SELECT nextval('seq_a');
 nextval
---------
       2
(1 row)

test=# ROLLBACK;
ROLLBACK
test=# SELECT nextval('seq_a');
 nextval
---------
       3
(1 row)

a sequence makes sure that values are ascending BUT it does not make sure that it does not contain gaps. it is also important to notice that a sequence cannot be rollbacked. you cannot have strictly ascending and gap-free at the same time as it would not work with a mix of long and short transactions.
therefore a sequence should never be used for an invoice-id and so on.


Materialized view with FAST refresh on remote table: How to include a GEOMETRY column?

Regarding the SDO_GEOMETRY/fast refresh/remote table scenario:
The issue doesn't seem to happen when the MV selects from a local table. The issue only seems to happen when we select from a remote table/using a dblink. It seems to be normal Oracle behavior.

Using remote table/dblink (fails):
The steps were adapted from the answer in this post: Create fast-refresh MV over dblink on table without PK?
SQL> create table t5 (c1 number not null unique, c2 number);

Table created.
SQL> create materialized view log on t5 with primary key;

Materialized view log created.

SQL> create materialized view mv5 refresh fast on demand as
  2  select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape
  3  from t5@self;
select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape

             *
ERROR at line 2:
ORA-12015: cannot create a fast refresh materialized view from a complex query

Using a local table (succeeds):
SQL> create table t5 (c1 number not null unique, c2 number);

Table created.

SQL> create materialized view log on t5 with primary key;

Materialized view log created.

SQL> create materialized view mv5 refresh fast on demand as
  2  select t5.*, sdo_geometry(2001, 26917, sdo_point_type(c1,c2, null), null, null) as shape
  3  from t5;

Materialized view created.

SQL> insert into t5 values (1, 1);

1 row created.

SQL> insert into t5 values (2, 2);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('MV5', 'f');

PL/SQL procedure successfully completed.

SQL> select refresh_method from user_mviews where mview_name = 'MV5';

REFRESH_METHOD
--------
FAST

Of course, that doesn't answer your question about how to get a GEOMETRY column working.
But I think that test will at least be help demonstrate that the combination of 1) SDO_GEOMETRY, 2) fast refresh, and 3) using a remote table is what's causing the issue. If we remove any of those three things, then the MV seems to work fine.


How do I get Azure SQL query history/statistics/performance?

Depending on how long the system has been up on the current underlying server, you may be able to get some of the info you're looking for from these DMVs (though many are limited to a maximum of 14 or 30 days of data retention).

sys.database_connection_stats - while it doesn't provide any of the specific info you're after, it can give you some clue about how busy your server has been, cycle fluctuations, etc.
sys.dm_db_index_usage_stats - again, depending on how far back this data goes, this can show you number of read/write operations against tables over the entire period (no breakdown by hour or day, you'll have to average). Also note that an update that affects one row and another that affects 2 billion rows are each treated equivalently as a single write operation.
sys.resource_stats - shows CPU usage, storage, and I/O metrics in 5-minute intervals over the past 14 days (or less). Not sure how valuable this info is for your needs - not sure how you will translate avg_data_io_percent to something meaningful for your new hosting provider. You can get more granular data from sys.dm_db_resource_stats (every 15 seconds), but this only goes back an hour (and requires elevated privileges).
sys.bandwidth_usage - on a v11 server you may be able to see some details about in/out bandwidth. On v12, however, this view has been deprecated and returns an empty result. I don't know of a direct replacement; you should be able to get some info on this in "Resource Costs" in the Azure portal.

You can get a rough idea about how far back your DMV data goes, you can use this as a ballpark:

SELECT MIN(start_time) FROM sys.event_log WITH (NOLOCK);

That DMV may have some useful info too, but there are warnings all over the place about the impact it can have on performance - you're migrating away, but I'm putting this here for future readers.


I don't know why, but I have as server Postgres 9.3 and psql, pg_restore and pg_dump belong to version 9.2.

/usr/bin/pg_restore is version 9.2, but /usr/pgsql-9.3/bin/pg_dump is version 9.3.

This is the cause of the problem.

postgresql.x86_64                                     9.2.18-1.el7                                  @base
postgresql-devel.x86_64                               9.2.18-1.el7                                  @base
postgresql-libs.x86_64                                9.2.18-1.el7                                  @base
postgresql-odbc.x86_64                                09.03.0100-2.el7                              @base
postgresql93.x86_64                                   9.3.17-1PGDG.rhel7                            @pgdg93
postgresql93-contrib.x86_64                           9.3.17-1PGDG.rhel7                            @pgdg93
postgresql93-libs.x86_64                              9.3.17-1PGDG.rhel7                            @pgdg93
postgresql93-server.x86_64                            9.3.17-1PGDG.rhel7                            @pgdg93

The solution is simply:

yum remove postgresql


how to turn off delimiters in generated SQL scripts for data types in SQL Server Management Studio

I don't know of any setting which will do this in SSMS.  Microsoft probably does it so that they don't need to check the data type name for special characters when generating the script (user defined data type names could have anything in them) so they just box everything.

You could submit that as a feature request to the tools team via connect.microsoft.com or to Mladen for including in his SSMS Tools Pack.


ApexSQL Search - http://www.apexsql.com/sql_tools_search.aspx

free tools, search database object (for this tasks) - search code for columns name


Updating a view on multiple joined tables

General view updatability
The key part of the CREATE VIEW (Transact-SQL) documentation is:

Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table.

Note that even if the view is technically updatable, it may not be actually updatable in practice, due to limitations of the query processor's reasoning. That is the subtlety behind the phrase, "...the Database Engine must be able to..."
The easiest way to be sure that a view is actually updatable is to request a pre-execution ("estimated") plan for the update query. If you get an error, either the view is not logically updatable, or the query processor can't tell that it is.
Requesting a "estimated" plan does not involve executing the query, naturally. The plan shown will also show you how much of the view definition the query optimizer was able to remove (because it is redundant). Typically, it does a good job with this, so the update view plan may look very similar to a plan for a simple update to the single affected base table.
Specific example

Can I use the ClaimStatusName column of the linked table dimClaimStatus to update the main table that is referenced via foreign-key? [...] I want to change fiClaimStatus in tabData.

Not using the query you posted:
update claims
set status='Awaiting Auth.'
where status = 'Approved'

This changes the base table column associated with the view's exposed column name status. From the view definition, that is the alias for column  ClaimStatusName in table dimClaimStatus.
The execution plan shows that dimClaimStatus is the table updated through the view:

If you want to update fiClaimStatus, that is the column you need to specify in the update statement. If that involves a lookup, chances are you can't use the view directly, as you originally wanted, but you could write something like:
update claims
set fiClaimStatus =
(
    select CS.idClaimStatus
    from dbo.dimClaimStatus AS CS
    where CS.ClaimStatusName = 'Awaiting auth.'
)
where status = 'Approved';`


Why can't SQL Server optimizer use foreign key

I can't reproduce your problem, which means there's likely something missing in your description (or I've missed something in your description!).  If you want more detailed help with this issue, please suggest changes to this repro code that allow it to more closely match your situation.

Here's an attempt and recreating the tables and data based on your description:

  There is an index on ToBeTransferredTemp.type
  and unique index on CODES.idstring.
  There is a foreign key constraint on ToBeTransferredTemp.type -> codes.idstring

In addition to those three pieces of information, I noticed that there is a clustered index on each of the two tables.

USE [master];
GO
DROP DATABASE IF EXISTS [245776];
CREATE DATABASE [245776];
GO

USE [245776];
GO

CREATE TABLE dbo.CODES
(
    IDSTRING varchar(100),
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_CODES PRIMARY KEY (IDSTRING)
);

CREATE UNIQUE INDEX UQ_Codes_IDSTRING ON dbo.Codes (IDSTRING);

CREATE TABLE dbo.ToBeTransferredTemp
(
    ID varchar(100),
    [type] varchar(100) NOT NULL,
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_ToBeTransferredTemp PRIMARY KEY (ID),
    CONSTRAINT FK_ToBeTransferredTemp_type FOREIGN KEY ([type])
        REFERENCES dbo.CODES (IDSTRING)
);

CREATE NONCLUSTERED INDEX IX_type ON dbo.ToBeTransferredTemp ([type]);

Here are the two tables, with clustered primary keys, foreign key constraint, an NC index, and a unique index.

I have included some filler columns in order for the original key lookup plan shape to be possible.

INSERT INTO dbo.CODES
SELECT TOP (8500)
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    v1.number
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

INSERT INTO dbo.ToBeTransferredTemp
SELECT
    CAST(NEWID() AS varchar(100)),
    c.IDSTRING,
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    NULL
FROM dbo.CODES c;

This loads exactly 8500 rows into each table.

The query as written in the OP produces a merge join plan for me:

SELECT *
FROM ToBeTransferredTemp a
    INNER JOIN codes c
        ON a.[type] = c.idstring;

In order to get the plan shape from the OP, I have to hint the join type (INNER LOOP JOIN) and hint that the CODES table should be accessed by the unique index.

SELECT *
FROM ToBeTransferredTemp a
    INNER LOOP JOIN codes c WITH (INDEX (UQ_Codes_IDSTRING))
        ON a.[type] = c.idstring;

This appears to be the right plan shape.  However, the estimates are exactly right in this plan.


Database Compatibility Requirement for Transactional Replication

Does the subscriber "within two version requirement" for transactional
  replication from this MSDN article apply to the SQL Server Instance,
  Database Compatibility Level, or Both?

Yes. No. No.

The database compatibility level is irrelevant, and you would never set the database compatibility on the distribution database, as it's a system database.

A SQL Server 2014 publisher/distributor can have a SQL Server 2017 subscriber.

SQL Server 2017 supports compatibility levels back to 100, so you can set the subscriber database to 110 compatibility level if you want to.  And you might need to if you publish views, stored procedures, stored procedure executions, or have triggers on subscriber tables that aren't NOT FOR REPLICATION.


how to user table name as variable in mysql stored procedure

ALTER TABLE var1 AUTO_INCREMENT = 1;

we can replace above line in below code fragment. Then it will solve this problem.

SET @SQL = CONCAT('ALTER TABLE `',var1,'` AUTO_INCREMENT = 1');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Complete code :

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`()
BEGIN
DECLARE var1 CHAR(50);
DECLARE done TINYINT DEFAULT FALSE;
DECLARE cursor1
 CURSOR FOR
 SELECT table_name FROM information_schema.tables where table_schema='maliban';
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 OPEN cursor1;
 my_loop:
 LOOP
 FETCH cursor1 INTO var1;
  IF done THEN
   LEAVE my_loop;
  ELSE
   SET @SQL = CONCAT('ALTER TABLE `',var1,'` AUTO_INCREMENT = 1');
   PREPARE stmt FROM @SQL;
   EXECUTE stmt;
   DEALLOCATE PREPARE stmt;
  END IF;
 END LOOP;
END


can i backup multiple db2 databases at the same time?

Yes, you can back up multiple databases at the same time.

Whether you want to do so or not depends on how much impact to database performance you are willing to accept and how long your backups run. You can control this somewhat by throttling your backup(s).


docker and postgres: using password_file leads to no pg_hba.conf entry for host

It was a permission issue.

database_1   | /usr/local/bin/docker-entrypoint.sh: line 19: /run/secrets/db_pass: Permission denied

My password file had -rw------- permissions. Since the docker-entrypoint.sh script sets the -e option, it was exiting early, right after this error, preventing to append the host all all all md5 line to pg_hba.conf.

I added r for group and others, as well as r-x for group and others on its parent directory. That fixed the issue.


Why can't "NEXT VALUE FOR" be used in a case statement

To fully answer your question one needs to know intimate details of the SQL Server SQL engine implementation, and most (all?) people who have that knowledge likely have signed all sorts of NDAs and confidentiality agreements. What follows is my speculation.

Typically the scope of NEXT VALUE is the entire SQL statement where it is referenced. This probably means that the sequence value is generated and made available independently of other features of that SQL statement. This has a semantic conflict with the conditional use of NEXT VALUE in the situations described by the error message. In other words, the user doesn't know if the next sequence value has or has not been consumed, depending on some nondeterministic condition, but at the same time it has already been generated independently.

So my guess is that the restriction aims to prevent this semantic ambiguity. What you can do instead is (in pseudocode)

vNext = NEXT VALUE FOR mySeq;
...
CASE
  WHEN Inserted.KeyId IS NOT NULL THEN Inserted.KeyId
  ELSE vNext
END

which doesn't have that ambiguity -- it is explicitly said that one sequence value is consumed every time, though it may or may not be actually used.


Sharing Databases and Tables between users in MySQL

[https://mariadb.com/kb/en/mariadb/roles/](MariaDB 10.0.5 has Roles); see if they provide what you desire.

(Yes, MariaDB is essentially compatible with MySQL; Roles is an extra feature.)


Regardless of platform, the following remarks apply.

(-) Nested views:

are harder to understand and debug

e.g. What table column does this view column refer to? Lemme dig through 4 levels of view definitions...
make it harder for the query optimizer to come up with the most efficient query plan

See this, this, this, and this for anecdotal evidence. Compare to this, which shows that the optimizer is often smart enough to correctly unpack nested views and select an optimal plan, but not without a compilation cost.

You can measure the performance cost by comparing the view query to an equivalent one written against the base tables.

(+) On the other hand, nested views let you:

centralize and reuse aggregations or business rules
abstract away your underlying structure (say, from other database developers)

I've found that they are rarely necessary.

In your example you are using nested views to centralize and reuse certain business definitions (e.g. "What is an eligible student?"). This is a valid use for nested views. If you are maintaining or tuning this database, weigh the cost of keeping them against that of removing them.

Keep: By keeping the nested views you incur the advantages and disadvantages enumerated above.
Remove: To remove the nested views:

You need to replace all occurrences of the views with their base queries.
You must remember to update all relevant queries if your definition of eligible student/teacher/school changes, as opposed to just updating the relevant view definition.


Creating tables and triggers dinamically

The Relational Model of Data advocates storing data in columns of rows in tables. That is, it suggests that storing data--such as year and month--in a table's name might be a Bad Thing. In my experience, it's a Very Bad Thing.
Instead, store the year and month in a column of a row in the table. Use suitable CHECK constraints. (Don't allow storing month 13 or year 32, for example.) Or store a date, from which year and month can easily be derived; columns of type "date" won't allow invalid months, although you might still want to use a CHECK constraint to limit the range of valid dates. Valid dates are application-dependent.
If you're concerned about the number of rows--you probably shouldn't be--read up on indexing, indexing expressions, and partitioning.


If the client computer isn't on the same domain as the SQL Server, and if there's no trusts set up between the client computer's domain and the SQL Server's domain, you can't use integrated authentication.


IBM i Access ODBC driver returning only first letter of column names

I believe this is a bug with the specific version of IBM's driver I was using. I was using the ODBC driver from IBM i Access Client Solutions Linux AP v07.03.00.

I replaced that driver with the ODBC driver from IBM i Access for Linux v7.1.0-1.0 and now everything works as expected.


Yes and no. You cannot configure it in such a way that each server hosts a copy of the database. They both share the same storage, so that is logical. You have a SQL Server cluster OR you configure AlwaysOn between the 2. However, you can host an AlwaysOn copy on a SQL Server failover cluster instance (FCI). In that case, you need one or more other SQL Servers to host the synchronous or a-synchronous copies. The picture below might illustrate this a bit (no sure where I got it from, it is from my own library):

Mind the difference between the two: a SQL Server failover cluster hosts an instance of SQL Server using 2 servers in an active/passive configuration. An AlwaysOn node hosts a database or group of databases on a single instance.

Here are some possible usefull links:

https://blog.papercut.com/the-ultimate-guide-to-high-availability-methods-for-microsoft-sql-server/

https://www.brentozar.com/archive/2012/02/introduction-sql-server-clusters/

https://techcommunity.microsoft.com/t5/Premier-Field-Engineering/An-overview-of-High-Availability-and-Disaster-Recovery-solutions/ba-p/370479

Is this the configuration you currently have?

If so, then you can create a setup like the one below. You can see that there is an extra SQL Server node with 1 instance that hosts an a-synchronous copy for each database of the availability group (where a-synchronous would be common for a DR setup). You can use synchronous copies technically, but you would not use that for a DR scenario. Synchronous copies rely heavily on latency and is therefore suitable for an High Availability scenario. For your understanding; look at the AlwaysOn layer as a seperate thing. There is no difference in configuring the AlwaysOn group on single server instances or a Failover Cluster Instance. AlwaysOn is configured at the database level.

Be aware that you need to buy an extra license in this configuration. However, recently there was a change in licensing for SA customers, which would make it free:

https://cloudblogs.microsoft.com/sqlserver/2019/10/30/new-high-availability-and-disaster-recovery-benefits-for-sql-server/

Also; don't forget to look how you would setup your maintenance scripts and that instance level configuration is not copied to the other server. If you require things to be in sync, then you need to script that or update it manually every time you change something.

I found this easy setup guide for you to configure the AlwaysOn group:

https://www.sqlservercentral.com/blogs/alwayson-availability-groups-step-by-step-setup

Also be aware that mixing 2 seperate technologies (Failover Cluster Instances and AlwaysOn) make things more complex. It is more complex to troubleshoot then when you have only 1 technology. You could setup a similar HA/ DR setup with AlwaysOn only like below. Just be aware that you need double the storage space (which you don't need for a cluster setup) and you would have some more latency (because you need to wait for the commit of the synchronous copy):

There are a lot of things you need to make decisions on, because all the technologies have their strong and weak points. The only option to learn them all is to read about it, but I think you get a fairly good jumpstart with all this information.

Oh and you might want to delete your Answer earlier. I saw you also set it in the comments, so it is double.


How to implement a dynamic string into a (prepared) sql statement?

The bad news: Prepared statements do not work like that. Parameters can only be data, not code or identifiers.

The good news: you do not need parameters for a ALTER TABLE statement at all.

final String sqlps = "ALTER TABLE filen ADD COLUMN gn_id integer";

...
    ps = con.prepareStatement(sqlps);
    ps.execute();

Your column needs a data type. I added integer on suspicion. Possibly add a NOT NULL constraint after that.
Friendly advice: do not use quoted upper-case column names in Postgres if you can avoid it. Use legal, lower case, unquoted names. Makes your life easier.


deadlock in mariadb: with non-index & fk table

The lack of a PK is a serious problem.  But there are other problems with the many:many table.  See here for 7 tips on an efficient mapping table.  In particular, you do need a composite PRIMARY KEY in order to prevent inserting duplicates.  At that point, INSERT IGNORE (or other techniques) can be efficiently used.


Limit number of simultaneous queries by user

This is not possible now. You have two classes of configuration options

Connections and Authentication, includes things like max_connections
Session/Client Configuration, includes things like statement_timeout, lock_timeout, idle_in_transaction_session_timeout
Role-based settings includes CONNECTION LIMIT (per user).

That's all that the server provides. In order to track what you're talking about the server would have to check pg_stat_activity. You can however get the information you're looking for manually..

SELECT usename AS username, count(*) AS concurrent_statements
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY usename;

You can run a function periodically that checks the above and calls pg_cancel_backend(pid int), or pg_terminate_backend(pid int). See the docs here for more information I don't particularly think that's a good idea. I would work with the default options before I tried to roll my own system based on this.


Is it possible to use MAX with a condition?

(I'm a bit rusty with PostgreSQL, but I believe the query below is ISO-compliant and so should work):

SELECT
    MAX( date_executed) as last_executed,
    MAX( CASE WHEN user_id = $userId THEN date_executed END ) AS last_executed_by_user
FROM
    jobs;
GROUP BY
    some_other_field

$userId is a parameter you'll specify in your PostgreSQL client library.
A CASE WHEN x THEN y END expression without an ELSE case will evaluate to NULL when x is false or Unknown.
The MAX aggregate function only considers non-NULL values, therefore it only considers values in jobs.date_executed when jobs.user_id = $userId.

You can also use the FILTER clause to get the same result (thanks for the tip, ypercubeᵀᴹ!):

SELECT
    MAX( date_executed) AS last_executed,
    MAX( date_executed ) FILTER ( WHERE user_id = $userId ) AS last_executed_by_user
FROM
    jobs;
GROUP BY
    some_other_field

Both queries will return last_executed_by_user = NULL if there are no rows where user_id = $userId.


SQL Server gets its time Zone information from the operating system on which it is installed. So if SELECT GETDATE() reveals the right information, then SQL Server is most likely not the problem.

You can query the registry on your SQL Server, if you have the appropriate permissions, and see what the server's time zone is set to:

    DECLARE @TimeZone NVARCHAR(255)

EXEC master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',

N'StandardName',

@TimeZone OUTPUT

SELECT @TimeZone

That should tell you what time zone your SQL Server thinks it is in.

I would suggest that the issue is either the client on which you are running the SharePoint application session - or more likely a setting in SharePoint itself or the server time zone on the SharePoint server itself.

You can check the regional settings for a site and see what it reports. This link should help get you started there.


MongoDB: Can I perform a second query if the first doesn't match

No, you can't do conditional returns within a single query. You seem to understand that, based on the wording of the question. Maybe if you can explain the exact use case more we might be able to think of another idea.

With the general description you give, I would just make two round trips to the server, if you'd usually find something the first try. Or do an $or, as you mention, if it's more often that you'd return the 2nd option.


For website search you should use an actual search tool like Solr.  Full text search works in-theory, but performance slows when your data set starts to get big.  Search tools like Solr, Endeca, and Elastic Search are designed to solve problems like this, and will perform much better than full text search.

Feel free to experiment with full text search if you want, but I don't think it is a valid long-term solution for a website.


Offline index rebuild - does it also create a whole new copy of the index?

Consider what Paul Randal says in his post on Search Engine Q&A #19: Misconceptions around index rebuilds (allocation, BULK_LOGGED mode, locking):

  Index rebuild (whether online or offline, and at least as far back as 7.0) will create a new copy of the index before dropping the old copy.

He reiterates that point in SQLskills SQL101: REBUILD vs. REORGANIZE:

  Rebuilding an index requires building a new index before dropping the old index...

Finally, the documentation also discusses this on the page Reorganize and rebuild indexes:

  Rebuilding an index drops and re-creates the index.

So the answer to your question is yes, a new index is created and the old one is dropped when rebuilding an index offline (although it will generally require less disk space than the online version of the operation).


It performs a scan because it has to find all values not in the one listed.
Imagine looking up in a phone book and finding all last name values that aren't 'Smith'.
Then you'll have to scan the entire book. If you instead had to find only those with the last name value 'Smith', then you could just seek to that section.

As for how to optimize, it would unfortunately be to rework the logic to avoid NOT IN if the scan is too costly to accept.
So if you somehow can change your NOT IN to an IN or = it would be better for the database engine.
Of course this can be impossible at time, and then you'll have to weigh the cost of the query vs. the cost of reworking the logic.


Accelerated Database Recovery - Should I turn it on for system databases

Nevermind. Tried it on the model database and got this answer:
Msg 12122, Level 16, State 1, Line 25
Accelerated Database Recovery cannot be enabled/disabled on system databases.


MariaDB LOAD DATA INFO can't import single column file to single column table?

To quote the manual:

  If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

and

  Interpret characters preceded by the escape character \ as escape sequences. For example, \t, \n, and \ signify tab, newline, and backslash, respectively. See the discussion of FIELDS ESCAPED BY later for the full list of escape sequences.

and

  If the FIELDS ESCAPED BY character is empty, escape-sequence interpretation does not occur.

and

  ... empty ('')

-- https://dev.mysql.com/doc/refman/8.0/en/load-data.html

That is, if you have tabs or backslashes, the data will be mangled as it is read.  This may explain the problem you are having.

I guess this may be the fix:

FIELDS ESCAPED BY ''


How to prevent misuse of rights in monitoring application?

Easiest is to not grant the table at all, not to end users. Create a stored procedure that does the dml and grant the procedure to the monitoring users, not to the regular users. That way no misuse is possible.


Amazon RDS MySQL Troubleshooting High Sessions

In addition to what was already stated, I took a look at the amount of freeable RAM on the instance. My RAM useage was also very good, with plenty of usable RAM.

The waits shown in the images are log file related -- the only thing that really should affect those is your disk IO. So begun the hunt: my CloudWatch metrics were showing a high number of writes (about 230 per second) and a very low number of reads (1 to 2 per second). That was somewhat of a surprise, but it turned out there was some index rebuilding that was being done on a table with over 5 million rows in it.

From this point, I started wondering what my limit on IOPS was for my RDS instance. As it turned out, I was using general storage, not provisioned IOPS, and in AWS General Storage IOPS baseline is about 3 IOPS per GB. Since my database was large but not enormous, I had only selected 50GB of General SSD storage. That means that my disk operations were limited to only about 150 IOPS for baseline. There are burst IOPS, up to 3000 IOPS for about 30-35 minutes, but I had already depleted this and wasn't aware that I needed to monitor these credits in CloudWatch to know when I might start getting throttled.

In the end, I increased the size of my RDS Storage and the problem immediately subsided.


figured this out, here is a solution in case someone needs to do something similar...

using double quotes "" around characters will display them as they are...

select to_char(sysdate,'"Today is the" Ddspth "of" fmMONTH,Yyyysp') as Today from dual

will display:

"Today is the" Eighteenth "of" MARCH, Two Thousand Fourteen


Execution plans behavior after a restart in SQL server

This is what happens when you restart SQL Server. Correct term will SQL Server has to create new execution plans, choose the 'reasonably good one' and save it in the cache to reuse.

  or instead, SQL will need to create new execution plans library in
  order to keep on improving the executions?

You have some misconception about execution plans in your question.

Plans are not updated after each execution. SQL Server keep tracks of how many time a plan was used, last used, and few other metrics. If you have memory pressure the usage metrics is used to decide which plans need to be removed to free up memory.

  Actually, after each execution, those plans are updated to let the SQL
  learn itself about new improvements.

You can manually remove plans from cache, details here.

There are improvements announced for SQL vnext (2017) to adjust/modify query plan on the fly called Adaptive Query Processing . You can watch a video by Microsoft Product Manager Joe Sack.here and read here.


create a batch script on windows that periodically starts a sql function

Use psql with a command to execute the function, eg:

postgres=> create function f() returns void language plpgsql as $$begin null; end;$$;
CREATE FUNCTION
postgres=> \q

root@maidenhead:~# psql --command="select f()" postgres postgres
 f
---

(1 row)

...which is easily set up to run via Task Scheduler, no batch file required (thanks @Craig)


Retrieving database dump after copying from remote server

The first command does not store the created file anywhere, so the answer is: no, you can't retrieve that file because there is none.

If you need such a file, you can either re-run the first statement and provide a filename using the -f switch.

Or, if f you have not yet changed your local database,  then you can simply dump that.


Target database memory exceed available shared memory

MEMORY_TARGET needs /dev/shm filesystem with required size. By default /dev/shm size is half of the system memory. So you have to change line in /etc/fstab from:

tmpfs                   /dev/shm                tmpfs   defaults          0 0

to something like:

tmpfs                   /dev/shm                tmpfs   defaults,size=49G 0 0

df -h /dev/shm will show you tmpfs size. To avoid reboot after editing /etc/fstab you can do:

umount tmpfs
mount -a

But with such amount of memory I would go with ASMM (SGA_TARGET) and huge pages. Especially if you will have many clients connected. Overhead to manage 48GB of 4kB pages will be quite big.


Find all users who rated all the movies rated by a specific user

This will not suffer from duplicate votes

SELECT user_id
FROM ratings
WHERE movie_id IN (SELECT movie_id FROM ratings WHERE user_id = '1234') AND user_id <> '1234'
GROUP BY user_id
HAVING count(DISTINCT movie_id) = (SELECT count(DISTINCT movie_id) FROM ratings WHERE user_id = '1234')


Try to run psql -h 127.0.0.1 (...)

When you use the -h that will tell psql to use a TCP/IP connection
('h' is from host), and the parameter after -h is the name of the host.

In that case 127.0.0.1 is a default address, meaning localhost.

If you don't use the -h (with host next to this parameters) it will
use a "local way" of connection called unix domain sockets.


Instead of having SQL Server monitor one of its subprocesses, why don't you have a script at the operating system level - WMI, PowerShell, what have you - periodically poll the services to make sure Agent is running? This seems a lot more foolproof than a startup procedure (which only runs when the SQL Server service starts, and potentially will finish before Agent starts, so it's not going to catch many scenarios - like when Agent suddenly fails or is forcibly shut down without touching the SQL Server service).

A job is absolutely not the way to do this. How can a job tell you that SQL Server Agent is not running? Catch-22. That's like your car driving itself to go get gas when it is out of gas.

Also, creating a watchdog for the Agent service seems way too reactive to me. Have you looked into why the service isn't starting or is getting shut down? The Windows event logs should have information about this.


You are only printing the last row, not every row:

You need to move the dbms_output inside the loop:

create or replace procedure Display
is
  cursor ABC is
    select empno, ename, sal
    from emp
    where deptno=10;

  emp_rec ABC%rowtype;

Begin
  Open ABC;
  Loop
    fetch ABC into emp_rec;
    exit when ABC%notfound;

    --- the output needs to be here
    dbms_output.put_line(emp_rec.empno||'   '||emp_rec.ename||'  '||emp_rec.sal);
  end Loop;
end;
/


How to design database table to store process status?

You'll be averaging about a million records a year, which shouldn't be a problem given the table is fairly narrow. I don't see anything wrong with your design (other than a NULLable primary key?). You should be able to easily track when and how long each process takes by comparing timestamps. This is accomplished using a self join.


Does Azure SQL DW support partitioned views?

Update Jul 2019 - as at July 2019, Azure SQL Data Warehouse now supports materialized views.

The engine is implemented somewhat differently in SQL Data Warehouse so it does not support the feature Partitioned Views as SQL Server does.  For example CHECK constraints are required to enable these to work correctly, but CHECK constraints are not supported in Azure SQL Data Warehouse.

You could spoof up something like this, ie basically a view over two tables each distributed as you described but they would have to be copies of the original tables in order to change the distribution.  This would add extra complexity to your process.  You could create the copies using CTAS which is powerful in SQL DW, but it's impossible to say if it would improve your processing times.

Make sure you have created all relevant statistics.  From experience I find int\bigint columns work faster with columnstore rather than varchar.

Perhaps you can give an example of your tables, DDL, distributions, query and sample data and I can have a look at it.


Getting "Conversion failed when converting the nvarchar value to data type int."

Write your query this way.  It includes a better test for integers and dumps ISNUMERIC that returns 1 for '-.', for example.

SELECT
    zip,
    coordinates
FROM (
    SELECT
        CASE WHEN ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%' THEN
            CONVERT(int, [ZIP5]) END zip,
        CONVERT(varchar(max), geom) AS coordinates
    FROM
        [SpatialData].[dbo].[zip5]
    WHERE
        ZIP5>'' AND NOT ZIP5 LIKE '%[^0-9]%'
) AS t1
WHERE
    zip >= 85000 AND
    zip < 86000

See this Connect item

SQL Server is free to evaluate the WHERE/SELECT clause in the order that it decides is optimized. A view or derived table is not materialized can easily be expanded into, from the outer query.

What SQL Server is compiling is really a query that looks like this

SELECT
    CONVERT(int, [ZIP5]) AS zip,
    CONVERT(varchar(max), geom) AS coordinates
FROM
    [SpatialData].[dbo].[zip5]
WHERE
    ISNUMERIC([ZIP5]) = 1
    AND CONVERT(int, [ZIP5]) >= 85000
    AND CONVERT(int, [ZIP5]) < 86000

You can inspect the query plan of your original, but my guess from looking at the structure is that the WHERE clause uses the expression CONVERT(int, [ZIP5]) twice, so it makes sense to streamline the resolution (calculation to a result) of the expression in the process of retrieving data from the table.  This puts the processing of the SELECT clause before the WHERE, so your ISNUMERIC() = 1 never got a chance to filter the bad eggs.

It is by-design.


pgsql - Delete data from huge table where not in

For large tables used for test of existence NOT EXISTS frequently works faster then NOT IN. So try

DELETE FROM LargeTable
WHERE NOT EXISTS (
  SELECT *
  FROM EvenLargerTable
  WHERE EvenLargerTable.foreign_id = LargeTable.id);

It is hard to explain why it's happening without having actual data. But with simple playground we can see that NOT IN case does not used indexes to perform the operation:

Playground:

-- drop table if exists a;
-- drop table if exists b;
create table a as select (random()*1000)::int as x from generate_series(1,10000);
create index idx_a on a(x);

create table b as select (random()*1000)::int*10 as x from generate_series(1,1000000);
create index idx_b on b(x);

analyse a;
analyse b;

Tests:

nd@postgres=# explain (verbose) delete from a where a.x not in (select b.x from b);
╔══════════════════════════════════════════════════════════════════════════════════╗
║                                    QUERY PLAN                                    ║
╠══════════════════════════════════════════════════════════════════════════════════╣
║ Delete on nd.a  (cost=0.00..129160170.00 rows=5000 width=6)                      ║
║   ->  Seq Scan on nd.a  (cost=0.00..129160170.00 rows=5000 width=6)              ║
║         Output: a.ctid                                                           ║
║         Filter: (NOT (SubPlan 1))                                                ║
║         SubPlan 1                                                                ║
║           ->  Materialize  (cost=0.00..23332.00 rows=1000000 width=4)            ║
║                 Output: b.x                                                      ║
║                 ->  Seq Scan on nd.b  (cost=0.00..14425.00 rows=1000000 width=4) ║
║                       Output: b.x                                                ║
╚══════════════════════════════════════════════════════════════════════════════════╝

nd@postgres=# explain (verbose) delete from a where not exists (select * from b where a.x=b.x);
╔══════════════════════════════════════════════════════════════════════════════════╗
║                                    QUERY PLAN                                    ║
╠══════════════════════════════════════════════════════════════════════════════════╣
║ Delete on nd.a  (cost=0.42..5005.91 rows=1 width=12)                             ║
║   ->  Nested Loop Anti Join  (cost=0.42..5005.91 rows=1 width=12)                ║
║         Output: a.ctid, b.ctid                                                   ║
║         ->  Seq Scan on nd.a  (cost=0.00..145.00 rows=10000 width=10)            ║
║               Output: a.ctid, a.x                                                ║
║         ->  Index Scan using idx_b on nd.b  (cost=0.42..20.78 rows=999 width=10) ║
║               Output: b.ctid, b.x                                                ║
║               Index Cond: (a.x = b.x)                                            ║
╚══════════════════════════════════════════════════════════════════════════════════╝


Concept to keep track of changes in all tables

The answer depends on what you intend to do with the data. Are you going to be doing frequent queries against it or do you only need it for the occasional lookup of what happened when, to rollback bad changes, or for regulatory reasons?

In the first case above, use the second process you descibed although I would add a column to denote the active record. I would also add a record created date. I wouild also create a view of just the active records and use that for allof my code that needs to see the current data.

In the second case above, I would have audit tables that are populated through triggers (the offective way to create audit records for any change to the db) and that also include the data of the change and the user or application that made the change as well as the old and new values.


When you write your INSERT query:

INSERT (log, POSITION(' edited' IN log), 1, 'NOW()')

In this way the DBMS thinks you try to put in your date field a string named "NOW()" instead of function to retrieve the current timestamp

You must remove the ' ' around NOW() function. In this way:

INSERT (log, POSITION(' edited' IN log), 1, NOW())


I am able to get identical results between python and T-SQL code with the MD5 algorithm. For example, the NO COLLUSION string hashes to 0x5CA1A58C070F24EF1D4D2900E5727F37 on both platforms.
Example T-SQL code:
SELECT HASHBYTES('MD5', 'NO COLLUSION');

Example Python code:
import hashlib
result = hashlib.md5(b'NO COLLUSION')
print(result.hexdigest())

I'm not an encryption expert, but it's not clear to me what you mean by "SQL's seed". The MD5 algorithm doesn't appear to call for a random number to be added by the implementer. Such an algorithm would be quite inconvenient because it would result in the same problem that you're running into right now: different implementations of the same hashing algorithm would give different results for the same input.


sp_reinitsubscription states that the default for the @publication parameters is "all," so you can execute without that parameter to reinitialize all of the publications.

exec sp_reinitsubscription @subscriber = 'all'


pgbouncer 1.7 with TLS/SSL client and server connections

I figured it out... I was (partially?) guilty of trying to use too many new features in pgbouncer 1.7.

There are the TLS/SSL settings & then there is the HBA access controls. (TLS/SSL doesn't need HBA access controls & vice-versa to work). Also, since pgbouncer & the database are on the same box, there is no need for the extra overhead of TLS/SSL between pgbouncer & the database.

Simplifying to just use more commonly used user authentication settings proved to be the fix.

First, postgresql.conf & pg_hba.conf were left untouched as show above.

pgbouncer.ini, however, is this:

;
; pgbouncer configuration
;
[databases]
mydatabase = host=localhost port=5432 dbname=mydatabase
;
[pgbouncer]
listen_port = 6543
listen_addr = *
admin_users = lalligood, postgres
auth_type = cert
auth_file = pgbouncer/users.txt
logfile = /var/lib/pgsql/pgbouncer.log
pidfile = /var/lib/pgsql/pgbouncer.pid
ignore_startup_parameters = application_name
server_reset_query = DISCARD ALL;
pool_mode = session
max_client_conn = 1000
default_pool_size = 300
log_pooler_errors = 0
; Improve compatibility with Java/JDBC connections
ignore_startup_parameters = extra_float_digits
; TLS settings
client_tls_sslmode = verify-full
client_tls_key_file = server.key
client_tls_cert_file = server.crt
client_tls_ca_file = root.crt

So the specific changes are auth_type = cert & auth_file = pgbouncer/users.txt (changing/removing the HBA references) & stripping out the 4 server_tls_... lines at the end.

The users authenticate to both pgbouncer and the postgres database using the SSL cert.

This also means that I have to put together a list of users that will be going through pgbouncer in ./pgbouncer/users.txt. The format should be just like this (for each user):

"lalligood" ""

since pgbouncer will not be verifying any connections based on a password.

So what all this means is that TLS/SSL authentication/connectivity through pgbouncer works. But it also leaves me with the feeling that auth_type = hba / auth_hba_file = pg_hba.conf is suspect at best; not working properly at worst.


Different execution plans during INSERT at @table variable and #temporary table

The first thing that I noticed is that the query plan compilation time was over 3 seconds for each query. Wow, this is a really complex query!

Because the solution space of potential execution plans is so large (it grows exponentially with the number of the number of objects involved in the query), SQL Server is only going to be able to explore a tiny fraction of the potential query plans when coming up with a plan for these queries.  Remember that SQL Server's job isn't to create the best query plan possible, but instead to create a query plan that is good enough and to do so as quickly as possible.

I have often found that small changes in the way a query is formulated, even if they don't impact the logic of the query, can have a significant impact on the query plan.  Anecdotally, this grows more and more likely as the query grows more and more complex.  One possible reason that this could happen is that a tweak to the query might cause SQL Server to begin cost-based optimization with a different initial plan.  As cost-based optimization proceeds, this different starting point could yield a different exploration of the space of potential query plans--kind of like a different random seed impacts random number generation.  Note that the query plans you provided are significantly different (compare images of the plan shape below!) and SQL Server actually does estimate that the @table variable plan is slightly cheaper.

In terms of why the table variable vs. temp table would have such an impact on cost-based optimization, I'll hazard an only-partially-educated guess: inserting into a table variable forces a serial plan (see the NonParallelPlanReason of CouldNotGenerateValidParallelPlan that appears in the table variable plan, but not the temp table plan), and this may impact the code path that the query optimizer takes either generating an initial plan or in some phase of plan optimization.

If possible, the next step that I would try is to simplify the query so that fewer tables are used and/or the query is split into multiple queries (with intermediate #temp tables) so that each query is simpler and has better statistics available.  If that's not possible, you could also try more hacky options such as using query hints (e.g., force MAXDOP 1 on the temp table query, and see if the plan comes out more like the table variable query).

Query plan with #temp table:

Query plan with @table variable:

And finally, if you are interested in going a little bit deeper into how the query optimizer works, I have found Paul White's blog to be a great resource!


SQL Server White Papers vs Books Online - What's the difference and where are the white papers?

How are white papers different from books online? Are white papers
  just a deeper dive into a technical issue?

Books Online is the documentation, while white papers are often more technical discussions of a topic, some times with an lean toward marketing.

  Does Microsoft have an organized place for white papers? If so, where
  please?

Microsoft has an gallery of SQL white papers up through SQL Server 2014 here: https://social.technet.microsoft.com/wiki/contents/articles/13146.white-paper-gallery-for-sql-server.aspx

White papers for SQL 2017 and 2019 are here: https://info.microsoft.com/sql-server-2017-technical-white-paper-registration.html and https://info.microsoft.com/ww-landing-SQLDB-Microsoft-SQL-Server-WhitePaper.html

An SQL 2019 white paper focusing on big data is here: https://info.microsoft.com/ww-landing-SQL-Server-2019-Big-Data-WhitePaper.html

More can be found here: https://www.microsoft.com/en-us/search?q=white+paper+sql


MySQL large number of opened tables -- we're noticing performance hits

Opened_tables (etc) is useless without dividing by Uptime.  I recommend no more than 2/sec.  Perhaps 1/sec for Opened_table_definitions/Uptime.  Those are under the control of these:

mysql> SHOW VARIABLES LIKE 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_definition_cache     | 1500  |
| table_open_cache           | 1555  |
| table_open_cache_instances | 4     |
+----------------------------+-------+

And rethink the wisdom of having so many databases and so many tables.

open_files_limit and the OS limit are also involved.

Table_open_cache_hits and Table_open_cache_misses and Table_open_cache_overflows are useful to watch.  Up to 1 miss or overflow/sec might be a good goal.

When increasing cache(s), be sure not to use up so much RAM that swapping occurs.  If swapping does occur, the quick fix is to decrease innodb_buffer_pool_size a little.

If you would like more analysis, please provide the info requested here .

Tuning Analysis

Observations:

Version: 5.7.20-0ubuntu0.16.04.1-log
16 GB of RAM
Uptime = 21d 01:09:23
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Really bad:  query_cache_size should not be more than about 50M.  2GB leads to a lot of overhead in purging entries.  Consider turning off the QC.

Since MyISAM is not used much, decrease key_buffer_size to 50M.

open_file_limit is only 1024.  This is probably because Ubuntu is restricting
it to 1024 -- see ulimit, and raise it.  After that, increase table_open_cache to
at least 5K, and a few others.

innodb_buffer_pool_size (10G) is probably at a good size.  I deduce that you have less than tha much data.

innodb_log_file_size is quite high for the amount of activity you have.
But it is not important to change it.

Why is SHOW CREATE TABLE (and similar things) being run so many times?

Why so many ALTER TABLEs?

It seems that a lot of SELECTs are inefficient.  See this for what to do next.

LOCK TABLES and UNLOCK TABLES should rarely, if ever, be used with InnoDB.  What is going on?

Details and other observations:

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (512M - 1.2 * 32 * 1024) / 16384M = 3.1% -- Percent of RAM wasted in key_buffer.
-- Decrease key_buffer_size.

( Key_blocks_used * 1024 / key_buffer_size ) = 32 * 1024 / 512M = 0.01% -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size to avoid unnecessary memory usage.

( Opened_tables ) = 10,163,367 / 1818563 = 5.6 /sec -- Frequency of opening Tables
-- increase table_open_cache

( Opened_table_definitions ) = 2,479,464 / 1818563 = 1.4 /sec -- Frequency of opening .frm files
-- Increase table_definition_cache and/or table_open_cache.

( open_files_limit ) = 1,024 -- ulimit -n
-- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)

( Table_open_cache_overflows ) = 10,109,150 / 1818563 = 5.6 /sec
-- May need to increase table_open_cache

( Table_open_cache_misses ) = 10,163,360 / 1818563 = 5.6 /sec
-- May need to increase table_open_cache

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 132,895 / 655280 = 20.3% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size to bigger than necessary?

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 10,751,949,312 / (1818563 / 3600) / 2 / 256M = 0.0396 -- Ratio
-- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 1,818,563 / 60 * 256M / 10751949312 = 756 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( innodb_print_all_deadlocks ) = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( (Com_show_create_table + Com_show_fields) / Questions ) = (1137006 + 1592424) / 249138897 = 1.1% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.

( local_infile ) = ON
-- local_infile = ON is a potential security issue

( query_cache_size ) = 2048M -- Size of QC
-- Too small = not of much use. Too large = too much overhead. Recommend either 0 or no more than 50M.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (2048M - 1143770840) / 560588 / 8192 = 0.219 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size

( Created_tmp_disk_tables ) = 2,468,770 / 1818563 = 1.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 2,468,770 / (2468770 + 4016418) = 38.1% -- Percent of temp tables that spilled to disk
-- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.

( Com_rollback / Com_commit ) = 2,444 / 8577 = 28.5% -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic

( Select_scan ) = 5,519,376 / 1818563 = 3 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 5,519,376 / 21974063 = 25.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( Com_alter_table ) = 85,985 / 1818563 = 0.047 /sec
-- Why so many ALTERs?

( back_log / max_connections ) = 92 / 214 = 43.0%

( Connections ) = 10,172,085 / 1818563 = 5.6 /sec -- Connections
-- Increase wait_timeout; use pooling?

( thread_cache_size ) = 100 -- How many extra processes to keep around (Not relevant when using thread pooling) (Autosized as of 5.6.8; based on max_connections)
-- 0 is good for Windows
0 is inefficient for non-Windows; 10 is probably fine

( thread_cache_size / max_connections ) = 100 / 214 = 46.7%

Abnormally large:

Com_create_db = 0.35 /HR
Com_create_table = 85 /HR
Com_create_trigger = 19 /HR
Com_create_user = 0.34 /HR
Com_drop_table = 85 /HR
Com_drop_user = 0.014 /HR
Com_grant = 0.34 /HR
Com_release_savepoint = 3.6 /HR
Com_rollback_to_savepoint = 0.25 /sec
Com_savepoint = 3.6 /HR
Com_show_create_db = 3.6 /HR
Com_show_create_func = 0.96 /HR
Com_show_create_table = 0.63 /sec
Com_show_create_trigger = 0.054 /sec
Com_show_events = 3.6 /HR
Com_show_fields = 0.88 /sec
Com_show_function_status = 3.6 /HR
Com_show_privileges = 0.002 /HR
Com_show_procedure_status = 3.6 /HR
Com_show_table_status = 0.25 /sec
Com_show_triggers = 0.25 /sec
Com_unlock_tables = 89 /HR
Handler_savepoint = 3.6 /HR
Handler_savepoint_rollback = 0.25 /sec
Performance_schema_digest_lost = 2.56e+7
Qcache_free_blocks = 342,300
Qcache_free_memory = 1.14e+9
Qcache_total_blocks = 1.49e+6
Select_full_range_join = 55,471
Threads_cached = 93
eq_range_index_dive_limit = 200
group_concat_max_len = 1.0e+6
innodb_purge_threads = 4
performance_schema_max_file_classes = 80
performance_schema_max_mutex_classes = 210
performance_schema_max_statement_classes = 193
query_cache_limit = 1.05e+8

Abnormal string values:

innodb_force_load_corrupted = OFF
innodb_undo_directory = ./
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN


Conversion failed when converting the varchar value 'TerminatedByCompany' to data type int

I am not entirely sure what you are trying to achieve by joining on t.Name and u.EmploymentStatusId as they are different data types, varchar and int. When this join is being completed it is trying to implicitly convert t.Name into an int and it contains data that cannot be converted as the error message shows.

If you are trying to get the employment status of the user with CustomerId of 214 I would do something similar to:

 SELECT
 t.Name AS Status

 FROM
 dbo.Paychecks AS p INNER JOIN dbo.Users AS u ON u.Id=p.UserId
 INNER JOIN dbo.TerminationReasons AS t ON t.Id = u.EmploymentStatusId
 WHERE u.CustomerId=214 order by u.FirstName;

If I have missed the point please clarify exactly what you are trying to achieve.


What is happening with locks in my database?

When I run sp_lock, I realized that two locks in my table: PAG and KEY.
Searching more about KEY LOCK, I created an index for the column on the where clause, and this solve my problem.

EDIT:
When I removed the where clause (without index) , it worked fine too.


database design storing historical information

Instead of a bit for IsCurrentVersion, I'd suggest setting up a version table and then use queries that let you pick the max version. I have several examples of the necessary code in this article. This method performs extremely well and has scaled well over time. However, there are alternatives that also work well. Between these two methods, you should be able to do exactly what you're looking for.


SQL Server 2017 installation unauthorized error

I'm using my admin account which has permissions (I’m in Administrations group ...

Let's get the obvious one out of the way ...
Did you run the installer program "as Administrator" ?
Just being in the "Administrators" Group stopped being enough to "break" your machine somewhere around Windows Vista!
To get into an "elevated" security context, wherein you can make changes to your machine, you must pass through the User Account Control (U.A.C.) "Challenge" by running the installer program "as Administrator".


Frequent Delete & Insert, impact on performance

Using a DELETE + INSERT pattern instead of also involving UPDATEs can be highly problematic. IMO, it's an anti-pattern. In 99.9% of cases, it's better to use UPDATES when possible.

Usually the DELETE + INSERT pattern is implemented such that the subset of rows is blindly deleted in the target, which can create a tremendous amount of unnecessary write activity because the data is always re-written even if nothing changed.

Not only does this put load on the I/O subsystem, but it can also kill data access concurrency unless snapshot isolation is used (which will multiply the wasted disk write workload). If the default isolation level is used and the data is concurrently read, this pattern can produce deadlocks very readily and in large quantity.

Having said that, one needs to implement UPDATEs carefully because they still have the potential to rewrite rows that haven't changed. Thankfully that's solved easily with a bit of extra code. All in all, it's well worth the effort to use UPDATEs despite the slight added code complexity.

Depending on what you're doing, it may be easier and more reliable to send the entire desired state as a whole to the database using something like a table-valued parameter and sorting things out there instead of on the client side.


What is the best database design for multi-language data?

The primary disadvantage of your first option is that you must create/maintain a similar table structure for n different languages and your application must switch between them.  When a new language is added you will have to add table and modify your application.
Your second option is preferable of the two.  I will address some of your concerns.

If your security relies on the separation of data into different tables, then your security needs work.  Sure, separating them would provide some degree of additional security, but if you invest the time you save by not maintaining multiple tables into verifying/increasing your other security measures you will have far greater benefit.
When you query the data you can sort by language.
A WHERE clause on the country would retrieve data only for a particular country.

I recommend you store the primary details including the English names in simpler tables and then each table that has other language equivalents can have a language specific table containing every other language.  Something like this:

Languages
   ID
   Name

Drugs
   ID
   Price
   Name
   Interactions
   Mechanism
   Uses

DrugLanguages
   DrugID
   LanguageID
   Name
   Interactions
   Mechanism
   Uses

Companies
   ID
   Name
   Address
   Activity

CompanyLanguages
   CompanyID
   LanguageID
   Name
   Address
   Activity

You should know that I have never done multilingual design, so my approach may have limitations that I do not fathom.


Is it possible to delegate responsibility for specific indexes for a collection to specific shards in a mongoDB database?

As Antonios wrote, you cannot distribute different indexes to different shards. To distribute load, you should select good shard key and this case key what is used most (best situation is if all queries would always use "some" key) it should be "hashed". ( {"some":"hashed"} )

Check hased sharding from here!


Login failed errors while DB refresh in progress

One option might be to change the login for this user to have a different default database:

ALTER LOGIN [xxxx]
WITH DEFAULT_DATABASE = [tempdb];

This way at least the connections will succeed and the logging of errors will stop (the apps will likely have mixed results, mostly failures, with whatever they are trying to do of course).

And then you can change it back to the usual one after the refresh is done.


It depends on what you want to enforce. If a transaction can take place between a customer and a company that is not in company_has_customer the individual foreign keys are preferable. If a transaction only can take place between a customer and a company that is in company_has_customer it is better to have a foreign key to that table.


According to this example in the Oracle Docs:

TABLES=XY_RESPONSE:SYS_P25129

should be sufficient.

Unless you have a very strange collation in your database, no quoting is required for any of these arguments.


Where is `pg_temp` documented in the PG manual?

It is documented at https://www.postgresql.org/docs/current/static/runtime-config-client.html

  Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in the path by using the alias pg_temp. If it is not listed in the path then it is searched first (even before pg_catalog). However, the temporary schema is only searched for relation (table, view, sequence, etc) and data type names. It is never searched for function or operator names.


How to run SQL services on NT SERVICE\MSSQLSERVER account if it is running earlier on LocalSystem

You have to change the service account in SQL Server Configuration Manager in order to have all required permissions applied to the service account.

Open Configuration Manager, select SQL Server Services in the treeview on the left, then open the properties window of the desired SQL Server instance.

Set the service account as follows:

Then open file explorer, navigate to the SQL Server data root and change the permissions on the properties window. Click the Advanced button:

Click the "Change permissions" button:

Then add the service account using the add button. If your computer is in a domain, make sure you select the local computer and enter NT SERVICE\MSSQLSERVER:

Make sure you check the "replace permissions on child objects" checkbox.


Unique constraint to enforce max one null per item

A partial unique index should do this:

create unique index max_one_null
   on item (type_id)
where manufactured_until is null;

  For bonus points, is there a reasonably complex way to guard that the intervals do not overlap for one item type

Look into range types and exclusion constraints. They were specifically designed for this problem.

Something like this (untested):

create table item
(
  id int,
  type_id int,
  manufactured_during tsrange
);

The inserts will look a bit different as there is only a single column now:

insert into item
  (id, type_id, manufactured_during)
values
  (1, 101, '[2000-01-01,2012-12-31]'),
  (1, 101, '[2013-01-01,)');

[2000-01-01,2012-12-31] defines a closed interval that includes both dates. [2013-01-01,) defines an open interval without an end (maps to manufactured_until is null with your current table design)

Then add an exclusion constraint to guard the range:

alter table item
  add constraint check_manufactured_range
  exclude using gist (type_id with =, manufactured_during with &&);

You don't need a unique index any more because the constraint will make sure that nothing will overlap.

More information in the manual: http://www.postgresql.org/docs/current/static/rangetypes.html

If you search the internet for "Postgres exclusion constraints" you will find several presentations and blog posts regarding this topic.

P.S.: it seems that you actually want a daterange (instead of a "timestamp range") because you probably don't want to include the time of day with the intervals that define when something was manufactured.