You can catch values for every inserted column in a row and save them into the table where you save other audit data.

There are third party tools, such as ApexSQL Audit, that create such triggers, captures data (for UPDATEs both old and new) and shows reports.

When a row is inserted into a table with the following columns:

All column values are shown as separate columns

You can find more useful info here: Auditing triggers in SQL Server databases

Disclaimer: I work for ApexSQL as a Support engineer


The simplest way to copy sql tables from one database to another?

This article lists out three simple ways to copy data from one table to another. To summarise, you can use the Import/Export Wizard in SSMS to copy data from one table to another. You can use the "Generate Scripts" option to generate a script of schema and data from the source then drop your target table and run the script against your target. Lastly, you can use INSERT INTO ... SELECT ... to transfer data from one table to another.

For your scenario, the easiest option is the Import/Export Wizard for a one-off transfer. If you want to repeat this transfer, a simple solution would be to create a linked server on your target SQL Server connecting to your source SQL Server and use the INSERT INTO SELECT option.


How to drop an in-memory temporal columnstore

It seems you have to drop the columnstore index first.  This works for me:

ALTER TABLE A DROP INDEX IX_A_CCS;
ALTER TABLE A SET (SYSTEM_VERSIONING = OFF);
DROP TABLE A;

Note that this leaves behind the temporal history table, so you might want to run DROP TABLE A_History; for good measure (which is the default naming when a history table name is not specified).


When do I need to backup the Service Master Key?

If you are talking about the SQL service master key, then there is a rare occurrence where you really need to restore it.

I'm thinking of a couple of scenarios where you need to restore the SMK...

Somehow it got corrupted.
You are rebuilding your SQL server and planning to restore every database including system databases from backup. Usually in this case also you might not need to restore the SMK if you are using the same SQL service account and password.

In TDE you don't need to restore the SMK. As everyone said, you just need the certificate and the private key. You don't need to have same database master key, also as when you create the certificate from backup it gets encrypted by the DMK of destination machine.


How to implement an association with restrictions

There are a couple of ways you can go about this, but the most simplest way to enforce this would probably be to create a dimension combination table. Such a table is pre-filled with all valid possible combinations (by id) and then that is the source of your application's controls (such as dropdowns, etc) because as you filter on one dimension in that table, only valid entries for the remaining dimensions will be available.
For example, your dimension combinations table would have a plant_category_id column and a tag_type_id and maybe a second tag_type_id2 column (or if you normalized your Tags table, the column naming would be a little better in the dimensions combinations table).

The other thing you can do is implement a complex check constraint (possibly with a function) but this is probably more work than it's worth, and will depend on which implementation of MySQL and version you're using.

The third way is handling the logic in stored procedures that manage data access via the logic you want to implement, but probably would also be a lot of work, especially to maintain over time. So the first suggestion that makes it table driven would be my first pick.


Not sure how much of help this will be for you. but I had to have an inventory database for a pub. I had similar problem with different measurements, in that at times they could sell a whole bottles and at times n number of liquid units from the barrel. it is not as complex as your problem, but it has similarities.

What I ended with, is defining a few rules:

Every product has volume
Every product can have a Base product.
Every product must either multiply or divide it's base.

Now, let's say they brought a barrel of 20 liters, and they want to sell mugs of 500ml, 330ml or whatever they have for that matter.

They'd first define a single ml of beer with a volume of 1. then they can define a whole barrel of 20kml and based that on the beer while saying that the barrel is multiplying the beer. then if they want to sell mugs of 500ml, they will define a new product based on the 20k ml barrel that has a volume of 500, but this time it will divide from the beer.

We can now buy different quantities from the supplier and distribute it in different quantities to the customers.

Your problem is slightly more complex. but perhaps this will give you a general direction.


How to obtain a good execution plan for (LIKE @p) when the pattern is of the form 'prefix%'

I would like SQL Server to discover at execution time the relevant
  range of the index to scan and only scan that range.

This is exactly the plan I get.

CREATE TABLE T(Columns VARCHAR(50),
               Col VARCHAR(50),
               INDEX IX(Col, Columns));

DECLARE @p0 VARCHAR(50) = 'foobar%'

SELECT Columns
FROM T
WHERE Col LIKE @p0 ESCAPE '~'

See Dynamic Seeks and Hidden Implicit Conversions for more about this.

Check that the index is indeed covering and that the datatype of the parameter is correct for the column definition (i.e. if the column is varchar and the parameter is nvarchar this can cause a scan).


The algorithms used to allocate space in different datafiles in a single tablespace are not documented except in the database administration guide
where it just says:

the database can allocate extents for a segment in any data file in
the tablespace

It can change between two releases but it is likely based on same round robin algorithm.
Here is a short demo with Oracle 19 which shows that some round robin algorithm is used:
SQL> select banner from v$version where rownum=1;

BANNER
------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL> create tablespace test;

Tablespace created.

SQL> alter tablespace test add datafile;

Tablespace altered.

SQL> alter tablespace test add datafile;

Tablespace altered.

SQL> select tablespace_name, file_id, file_name from dba_data_files where tablespace_name='TEST';

TABLESPACE_NAME     FILE_ID FILE_NAME
-------------------- ---------- ------------------------------------------------------------
TEST                120 /u02/oradata/CDB2/datafile/o1_mf_test_hhxspzt3_.dbf
TEST                121 /u02/oradata/CDB2/datafile/o1_mf_test_hhxsq112_.dbf
TEST                122 /u02/oradata/CDB2/datafile/o1_mf_test_hhxsq2kt_.dbf

SQL> --
SQL> create table t tablespace test as select * from dba_objects ;

Table created.

SQL> select distinct file_id
  2  from dba_extents
  3  where segment_name='T'
  4  and   segment_type='TABLE'
  5  and tablespace_name='TEST';

   FILE_ID
----------
       121
       120
       122


Does there exist a way to determine the exact file that contains an allocation unit in a filegroup of multiple files?

Try the following query. It first creates a local temporary table and then populates it with the AllocationUnitID-to-FileID associations found in sys.dm_db_database_page_allocations, an undocumented Dynamic Management Function (DMF) introduced in SQL Server 2012 (for versions prior to 2012, you can get this info from DBCC IND()). That local temp table is then JOINed into a modified version of the original query.

The data from that DMF is placed into a temporary table for performance since, depending on the size of the database, it could take more than a few seconds to get that data. The DISTINCT keyword is used because that DMF returns one row per data page, and there are multiple data pages per each allocation unit.

I left-JOINed that data into the original query since the original query returns allocation units that have 0 data pages (typically ROW_OVERFLOW_DATA and LOB_DATA types). I also added the total_pages field so that it would be easier to relate that data point to the rows that have NULLs for the Data Files. If you don't care about the Allocation Units that have 0 rows, then it would be fine to change that LEFT JOIN to be an INNER JOIN.

IF (OBJECT_ID(N'tempdb..#AllocationsToFiles') IS NULL)
BEGIN
    -- DROP TABLE #AllocationsToFiles;
    CREATE TABLE #AllocationsToFiles
    (
      ObjectID INT NOT NULL,
      IndexID INT NOT NULL,
      PartitionID INT NOT NULL,
      RowsetID BIGINT NOT NULL,
      AllocationUnitID BIGINT NOT NULL,
      AllocatedPageFileID SMALLINT NOT NULL
    );
END;

IF (NOT EXISTS(SELECT * FROM #AllocationsToFiles))
BEGIN
  --TRUNCATE TABLE #AllocationsToFiles;
  INSERT INTO #AllocationsToFiles (ObjectID, IndexID, PartitionID, RowsetID,
                                   AllocationUnitID, AllocatedPageFileID)
    SELECT DISTINCT alloc.[object_id], alloc.[index_id], alloc.[partition_id],
           alloc.[rowset_id], alloc.[allocation_unit_id], alloc.[allocated_page_file_id]
    FROM   sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL,
                                               'LIMITED') alloc
    WHERE  alloc.is_allocated = 1
    AND    alloc.is_iam_page = 0;
END;

SELECT
    SchemaName = sh.name,
    TableName = t.name,
    IndexName = i.name,
    PartitionNumber = p.partition_number,
    IndexID = i.index_id,
    IndexDataspaceID = i.data_space_id,
    AllocUnitDataspaceID = au.data_space_id,
    PartitionRows = p.[rows],
    TotalPages = au.total_pages,
    AllocationUnitType = au.type_desc,
    LogicalFileName = dbf.[name],
    PhysicalFileName = dbf.[physical_name]
    --,p.[object_id], p.[partition_id], au.allocation_unit_id
FROM sys.allocation_units au
INNER JOIN sys.partitions p
        ON au.container_id = IIF(au.[type] = 2, p.[partition_id], p.[hobt_id])
INNER JOIN sys.indexes i
        ON i.[object_id] = p.[object_id]
       AND i.index_id = p.index_id
INNER JOIN sys.tables t
        ON p.[object_id] = t.[object_id]
INNER JOIN sys.schemas sh
        ON t.[schema_id] = sh.[schema_id]
LEFT JOIN (#AllocationsToFiles alloc
       INNER JOIN sys.database_files dbf
               ON dbf.[file_id] = alloc.AllocatedPageFileID
          )
        ON alloc.ObjectID = p.[object_id]
       AND alloc.IndexID = p.index_id
       AND alloc.PartitionID = p.partition_number
       AND alloc.AllocationUnitID = au.allocation_unit_id
WHERE sh.name <> N'sys'
ORDER BY t.name, i.index_id, p.partition_number;


Mysql replication no error and not replicating

USE site99;
INSERT INTO site2.mytable ...;

will not be replicated.  This is because the USE controls replication, not the db.tbl syntax.

If this does not provide the answer, please provide an example of the code that fails, plus mysqldumpbinlog against both the Master's binlog and the Slave's relay_log.  This should show whether or not the query made it that far.


Disable SQL Server Log Shipping to nonexistent destination

You just need to run two stored procedures to remove the setup of log shipping from the primary database How to: Remove Log Shipping (Transact-SQL).  As the secondary doesn't exist you shouldn't need to perform step 2 & 4.


MySQL: which innodb parameters should I tune if disk I/O is slow

That depends on why your disk I/O is slow and how many safeties you are willing to disable. Some examples:

sync_binlog=0 # if you use binlogs - you could lose up to typically 5 seconds of transactions that were written to the binlog

innodb_doublewrite=0 # you will end up with corrupted data if a page written to the transaction log is torn due to a crash or power failure

innodb_flush_logs_at_trx_commit=0 # you will lose up to typically 5 seconds of transactions that were written to InnoDB.

innodb_io_capacity and innodb_io_capacity_max - make sure these are set appropriately for your disk speed. Defaults are 200 and 2000 respectively, which is probably too high for regular SATA HDDs and certainly too low for SSDs, especially high end NVMe SSDs.


What makes using an Oracle Wallet to connect to a data source more secure than using a username and password to do the same?

Wallets can be configured to only be valid from a specific client, so
access to the specific host is also required, whereas
username/password text can be easily ported elsewhere with
copy/paste.
Using wallets also allows most applications to be redeployed between
environments (like dev and prod) without requiring reconfiguration or
changes to credentials.
Placing the credentials in an external store allows password
management policies to be more easily enforced without changing
application code whenever user names or passwords change.


SQL Server has very extensive locking options. Most are automatic but you can force them if needed for specific cases. You can also implement non-default locking behaviour on various levels. What you are looking for (as far as I can tell) can best be handled by a table hint called rowlock. Depending on your isolation level, you might need additional hints to ensure its function. You can read more about it in the following link: https://msdn.microsoft.com/en-us/library/ms187373.aspx


a filtered index to support a query - is it possible to consider nulls?

The solution I found on this occasion, having a look at the data, constraints, applications etc, I could change the column notCountedInTotal to not null having a default zero, as you can see on the table definition below.

CREATE TABLE [dbo].[accountTransaction] (
[accountTransactionID]     INT              IDENTITY(1,1)   NOT NULL,
[accountCode]              VARCHAR(30)                          NULL,
[transactionType]          CHAR(3)                              NULL,
[transactionReason]        VARCHAR(100)                         NULL,
[applicationID]            INT                                  NULL,
[invoiceID]                INT                                  NULL,
[entityID]                 INT                                  NULL,
[entityType]               CHAR(1)                              NULL,
[transactionCurrencyCode]  CHAR(3)                              NULL,
[transactionAmount]        DECIMAL(10,2)                        NULL,
[accountCurrencyCode]      CHAR(3)                              NULL,
[accountAmount]            DECIMAL(10,2)                        NULL,
[chequeNo]                 VARCHAR(20)                          NULL,
[issuingBank]              VARCHAR(50)                          NULL,
[issuingCountryCode]       CHAR(2)                              NULL,
[STReference]              VARCHAR(30)                          NULL,
[paymentMethod]            VARCHAR(10)                          NULL,
[GPStatus]                 INT                                  NULL,
[transactionDate]          DATETIME                             NULL  CONSTRAINT [DF_accountTransaction_transactionDate] DEFAULT (getdate()),
[transactionStatus]        VARCHAR(10)                          NULL,
[userID]                   INT                                  NULL,
[parentID]                 INT                                  NULL,
[transactionComment]       VARCHAR(max)                         NULL,
[notCountedInTotal]        BIT                              NOT NULL  CONSTRAINT [DF_accountTransaction_notCountedInTotal] DEFAULT ((0)),
[enteredDate]              DATETIME                             NULL  CONSTRAINT [DF_accountTransaction_enteredDate] DEFAULT (getdate()),
[chequeDetails]            VARCHAR(1000)                    NOT NULL  CONSTRAINT [DF_accountTransaction_chequeDetails] DEFAULT (''),
CONSTRAINT   [PK_accountTransaction]  PRIMARY KEY CLUSTERED    ([accountTransactionID] asc) WITH FILLFACTOR = 100)

After that it was easy to add a filtered index because there is no more the either the value is null or the value is zero thing.

execution plan using the newly created index:


Create mysql user which is also a domain account

By default, MySQL server accounts, with the default authentication plugin, are completely unrelated to system or network users. That means that (again, stress on by default), whenever you run: CREATE USER you are creating a mysql-only account, no matter if it has the same name than other system (os) or authentication methods.

However, MySQL has available authentication plugins, which you can enable on a per-account basis, and link it, for example, to OS accounts or LDAP/domain network accounts.

I am personally not familiar with Microsoft Authentication methods (I am myself a Linux administrator, so I only familiar with plugins such as unix_socket and PAM) but a quick search tells me that such plugins indeed exist:

https://dev.mysql.com/doc/refman/5.5/en/windows-pluggable-authentication.html

I can even see MySQL installed on Linux servers using PAM to authenticate using windows servers:

https://runops.wordpress.com/2015/03/19/how-to-setup-active-directory-authentication-in-mysql-running-on-linux/

If you were unsuccessful with trying those, or don't want to pay for them, you could setup an external utility to sync domain users with MySQL local users.


MSSQL: Auto-numbering rows in commit order

How do I ensure, that events (DB rows) are numbered in increasing commit order?

You don't.  At least not with eliminating concurrent writers.  You simply don't use a high-water mark for your readers.  Delete the rows after reading, or update them to show that they were processed.

See eg Using tables as Queues


Tracking changes in stored procedures within a database

Don't be tempted to re-invent the wheel. This problem has been solved many times before you came across it.

Take a look at schema comparison tools, such as those available in SQL Server Data Tools (SSDT), and commercial options like Red-Gate SQL Compare.

Going forward you can also use DDL Triggers to log certain DDL events, helping you catch outlying changes that only happened in one place. If you want to do more reading, I have a few tips about this handy feature here, here and here.


It's happens because s specifier makes presence of sign mandatory and places it on specified position.  Even you can put sign as last symbol in string, look at number format documentation or just try:

select
  to_char(10,'S999')  "10 S999",
  to_char(-10,'S999') "-10 S999",
  to_char(0,'S999')   "0 S999",
  to_char(10,'999S')  "10 999S",
  to_char(-10,'999S') "-10 999S",
  to_char(0,'999S')   "0 999S"
from dual

SQLFiddle

According to documentation:

  Negative return values automatically contain a leading negative sign
  and positive values automatically contain a leading space unless the
  format model contains the MI, S, or PR format element.

you always get a "minus" char for negative values at start of a string, so there are no need for S format specifier at most of the cases.
But if you need for some purpose to always place a sign before or after digits, then you can use S format specifier.

If you ask "why +0 and not -0" - it's just "by design" :)

Update
And some words about "design":

A long time ago  (far far away ... :) ) Oracle decide to use special values of data types to indicate null values. For character data it's empty string (that's also source of two string types - varchar and varchar2) and for numeric types it's -0 value.

If you look at specification of computer number format you can find, that for a signed number one bit always reserved to store sign. Zero value of this bit treated as '+' and value 1 as -.
Two representations of zero -0 and +0 are same from the point of view of math and one of them can be eliminated without loosing any functionality. So, Oracle designers decide to always store zero value as +0 and treat -0 as null.
May be behavior changed in modern versions of Oracle, but for compatibility reasons zero value normalization remains unchanged till today and you always got +0 in string representation.


Fast reads without paying the price on writes

For SQL Server 2014 and later my recommendation is rather radical: switch to a clustered columnstore index. 1000 records/min is well within the range of columnstore bulk load capabilities, on even modest hardware. See Clustered Columnstore Index: Data Load Optimizations – Minimal Logging and SQL Server clustered columnstore Tuple Mover. The query performance of a clustered columnstore is quite staggering due to the inherent advantages of columnar storage and batch processing. But even more so on time series (which your data probably is), due to very likely segment elimination.

On SQL Server 2016 there are some specific improvements targeting your case, see Real-Time Operational Analytics Using In-Memory Technology and Speeding up Business Analytics Using In-Memory Technology.

For SQL Server 2012 and earlier my recommendation is to upgrade to 2014 or 2016.

In any case, I would shy away from Transactional Replication, for two reasons:

it forces full logged INSERT for bulk operations (=very dramatic write perf impact)
I wouldn't want to be the admin responsible for the replication agents on a 1000 writes/min env.

Also need to consider that log shipping or AlwaysOn readable secondaries can only offload the processing of querying, but not the schema requirements (ie. indexes). Any index required by querying on the replica would have to be created on the original DB, and the price is paid at write time.

Of course I assume you did due diligence and your writes are optimized now, ie. all the advice in The Data Loading Performance Guide is applied and your upload is bulk and minimally logged.


Getting "There is already an object named 'Mytable' in the database" error

Look at the source code for trigger tr_AuditTableChange.

If it's creating a permanent table, see if it can use a #temp table instead, or better yet simple normal variables.

If it must use a permanent table, then quit creating and/or dropping that table, and leave it in place permanently.  If you have multiple sessions possibly stepping on each other, add @@SPID, but this is a bad technique in the first place.

For an auditing trigger, the auditing should do at most the following things

Insert into your audit table (which should have INSERT only permissions for normal users - no SELECT, no UPDATE, no DELETE).
ROLLBACK unauthorized transactions (if absolutely required)
Notify administrators of unauthorized transactions (if absolutely required).


You will have to edit your pg_hba.conf file.  If you really want to reduce possible access to a single user (see the note below), you will need the following lines, and only these:

# TYPE  DATABASE  USER       ADDRESS   METHOD
host    all       dbowner    all       md5
local   all       dbowner              md5

This means that only dbowner will have access to any DB, logging in via the Unix socket or a TCP connection (from all addresses, including localhost).  You can replace 'all' with your DB name if you want to limit access to a single DB.

After having edited the file, you will have to reload the config - depending on your system, do a pg_ctl reload or kill -HUP <postmaster_pid>, or do this form inside the DB, by calling

SELECT pg_reload_conf();

Please note that in the future you might want to do certain things that need rights dbowner may not have (be it SUPERUSER, CREATEUSER or something else).  Depending on the policy you follow, you'll have to edit the file again to allow further access to a user (eg. postgres) that has the necessary privileges, or you have to assign these to dbowner before closing the door on other users.


Which databases can have row and column level security based upon Active Directory?

I can't speak for any of the others but as for as SQL Server goes there would be some setup involved but after that you can control security at some level from active directory.

What you would do is create a series of roles (server and database level) and grant them the permissions needed.  Then you would create AD groups and create logins and users based on them.  Then assign those logins and users to the various roles.  Once that is done you can control who has what access by adding and removing individuals from the various AD groups.

There are options for column level permissions and row level permissions can be handled by views.

Now if new objects are created in the database that require additional security then that will have to be managed within SQL server by granting those permissions to the appropriate roles.


What is the best way to check if a value in a table has changed when writing to a database?

If you need combination of (tag_value,tag_type) to be unique , the only guaranteed way to do it is to add unique constraint or unique index, e.g.
alter table  html_tag add constraint UQ_HTML_TAG_COMPOSITE UNIQUE(tag_value, tag_type) .  In addition you can modify INSERT statement so it will not insert a record if it already exists. Something like

INSERT INTO html_tag(tag_value,tag_type)
SELECT 'dba.stackexchange' as tag_value, 'link' as tag_type FROM DUAL
WHERE NOT EXISTS
(
  SELECT NULL FROM  html_tag a WHERE a.tag_value = 'dba.stackexchange'
  and a.tag_type = 'link'
)

will greatly reduce (not 100% though due to concurrency) chances of inserting duplicate records.  You  may also try using MERGE. However, no matter how you insert the record, the only way to completely avoid duplicates is to enforce it on DB level by adding unique constraint /index.


It actually suffixes the password with the username and then MD5 hash is made out of that, and that hash is then prefixed with 'md5'


Inheritance and its alternatives in PostgreSQL

It is hard to answer the question as written.  The simplest solution would probably be something like a context table with types listed as they are.  What I am not sure about is what you need in terms of linking to this table.  In other words, have all the common fields for context in the context table plus a type field that tells you what kind of context it is.  Other tables can then join that to provide more information about the types.

The problems you mention with the 1:1 design can be solved with composite foreign and primary keys.  It may seem like field duplication but keep in mind your primary key is multi-field so your foreign keys would need to be also.  This allows you to ensure that different dependent tables do not overlap where you do not want them to do so.

The second solution is to use inheritance.  In my view, inheritance is going to be a good solution if the following are true:

The context records have foreign keys that reference other tables depending on subtype, and
No tables reference context as such but rather the subtype directly.

My guess is that in your case the second condition will not be met and therefore I would not recommend table inheritance for you.


How does a database conciliate data to files on distributed filesystems?

In simple terms, the DBMS doesn't care (or in some cases even know) if the file system where its files are located is distributed or not. In a high availability scenario with shared (distributed) storage an active-passive setup is most commonly used, where only one instance of the DBMS at a time is running and has access to the database files. It's the responsibility of the cluster manager (software or human) to control DBMS instance startup and failover.

In a shared data cluster, such as Oracle RAC or Db2 pureScale, there are multiple instances of the DBMS running concurrently and accessing the same set of database files, and a special component of the cluster manages concurrent access to the files by the DBMS processes.

That "special component" is usually tightly integrated with the DBMS itself but the actual implementations differ wildly. Oracle Clusterware plays this role in an Oracle RAC; Cluster Cache Facility in Db2 pureScale; both are tightly integrated with their respective underlying shared file systems.

I'm not aware of any open source shared-data cluster databases; they all tend to choose shared-nothing architectures, which are somewhat simpler to implement and more tolerant towards low-end hardware.


Moved MySQL datadir Now Server Will Not Run!

I have done the same thing. Be absolutely sure that all rights and owners are intact. You can do this with cp -rp if I'm correct. Rsync the folder locally should also keep owners and rights intact. You don't have to change the config however. A symlink will do.

Rename the original datadir to datadir-old or something similar.
Then create a symlink to the new location.
If you want the symlink to have right owner, create a new folder (tmp), move the symlink inside that folder, then change the owner of the tmp folder and its contents, then move the symlink back.
Undo the changes to the config files.

On the commandline... (I haven't tried this so check if all is as it should be!)

sudo -i
cd /var/lib
mv mysql mysql-old
ln -s /home/mysql mysql
mkdir tempdir
mv mysql tempdir
chown -R mysql:mysql tempdir
mv tempdir/mysql .
rm -rf tempdir

In the directory listing I see the following on my system. Yours should be similar!

drwx------  7 mysql         mysql          4096 Feb  6 12:01 mysql

-rw-rw----  1 mysql mysql 1671430144 Feb  6 17:37 ibdata1
-rw-rw----  1 mysql mysql    5242880 Feb  6 17:37 ib_logfile0
-rw-rw----  1 mysql mysql    5242880 Jan 28 14:35 ib_logfile1
drwx------  2 mysql mysql      24576 Jan 28 14:34 testdatabase_local
drwx------  2 mysql mysql       4096 Jan 28 13:38 mysql
-rw-rw----  1 mysql mysql          6 Jan 28 13:38 mysql_upgrade_info
drwx------  2 mysql mysql       4096 Jan 28 13:38 performance_schema

Try this and see if that fixes your problem.


Can sql server logins stored in the master db/msdb be used for migration?

Simply answering your questions is not the solution to your questions.

Are all these stored in the master db?

Yes. (Well sort of)

If so, then when migrating to another server, if we backup this master db and restore to the new server, then will the logins get migrated to the new server?

No. It is recommended to transfer the SQL Server Logins using the procedure documented by Microsoft. See also the Q&A Transfer logins to another server which includes various links and where they discuss in the comments, that restoring the master database can break things.

Similarly SQL server jobs, schedules, etc are stored in the msdb? Can this be backup/restored to migrate the jobs to the new server?

I would also recommend to right-click the jobs and script them out to ensure they are created correctly on the new server.
Even though the msdb is slightly less relevant for a functioning SQL Server instance, the newer versioned msdb could still contain elements (columns, tables, ...) that are not available in older versions.

If not, then what is the purpose of doing a backup of these system dbs?

When your server system crashes and you want to restore the whole SQL Server instance, then you would require the master database to restore all the databases, logins, etc. and the msdb database to have all the jobs back.


New tables not showing up in MySQL Workbench Navigator

After rebooting my computer, the problem is gone and refreshing schemas works as expected. ¯\_(ツ)_/¯


You could have a third table which stores both. Something like this:

unique_ids
----------
  identifier (UNIQUE)
  used_where (name/id of table that the identifier is used in)

users
-----
  username (FK to unique_ids.identifier)

clients
-------
  clientname (FK to unique_ids.identifier)

This way, both tables share one common table that contains the values that need to be unique and uniquness contraints can be easily enforced in that shared table. You will need a simple on-insert trigger (on both tables) as well that will check the used_where field to ensure that the identifier is not already used by the other table (because if I understand your requirement, username must be unique in users but clientname does not need to be unique in clients).

If you don't want to introduce new structures and modify existing ones, you could try using an on-insert trigger on clients and users that checks to make sure a new record does not contain an existing username/clientname.


Is there a way to throw an error on transaction commit, if a specific stored procedure was not called as part of the transaction?

There are several ways to approach this.

The first way is to just use triggers -- which operate inside the same transaction. When the transaction commits, the changes across all tables will be visible to others.

If you really don't want to use triggers, then you could use LISTEN / NOTIFY. You need one process that runs all the time that executes a LISTEN.

Then inside your transaction (maybe from a trigger) you call NOTIFY. The listning party won't be notified until after the transaction commits. It can receive a payload (maybe the IDs of the changed records?) and if it queries for that data it will have been committed and therefore be visible. Yes, the syncing that you want to be done will be done asynchronously so there will be a slight delay before everything is in sync again -- how long this takes depends on the complexity of your logic.


How can you have more used_pages than total_pages when looking at sys.allocation_units?

I suspect it is related to the fact that this DMV is updated asynchronously behind the scenes.  Per the sys.allocation_units BOL page:

  ...the values returned by sys.allocation_units immediately after dropping or truncating a large object may not reflect the actual disk space available

While this implies only drops and/or truncates won't be tracked in real-time, I just ran the same query against one of my environments where nothing is ever dropped or deleted and am getting similar behavior to your results.  I suspect this view just is updated in the background and whatever it is that has to trigger to spawn the update hasn't happened yet.

Hopefully someone else has a better answer as I'd be interested to know for certain as well.


Your varchar columns have bad data in them, and you can't really control when SQL Server will try to evaluate them (before or after any filtering occurs). So you can add where clauses that prevent them from being included (by checking for only rows where the value is a date), but there is still no guarantee of the order in which the where clauses will be evaluated (meaning you may still get an error). So in addition to those filters (which can logically replace your NOT NULL filters), you can also ensure that the predicates are only evaluated when they are, in fact, valid dates.

SELECT COUNT(*)
FROM dbo.patient AS p
INNER JOIN dbo.study AS s
  ON s.patient_fk = p.pk
WHERE ISDATE(p.pat_birthdate) = 1
AND ISDATE(s.study_datetime) = 1
AND CASE WHEN ISDATE(s.study_datetime) = 1 THEN s.study_datetime END
  <= DATEADD(DAY, -2192, GETDATE())
AND CASE WHEN ISDATE(p.pat_birthdate) = 1 THEN p.pat_birthdate END
  <= DATEADD(DAY, -7670, GETDATE());

Next, please fix the table so that random junk can no longer find its way into these columns. Identify the bad data first using:

SELECT * FROM dbo.study
  WHERE ISDATE(study_datetime) = 0;

SELECT * FROM dbo.patient
  WHERE ISDATE(pat_birthdate) = 0;

Then correct accordingly (they may all be "dates" in the same pattern, or it really may be a bunch of random junk that you just need to set to NULL). Once there are no bad values in there, you can change the data type to DATE or DATETIME, as it should have been from the start.

Please see the following posts:

Bad habits to kick : Using shorthand with date/time operations
Bad habits to kick : mis-handling date / range queries
Bad habits to kick : choosing the wrong data type
Bad habits to kick : using old-style JOINs


What is the best SSIS Tasks for String Parsing database fields

There is no built-in address clean-up task in SSIS. However, there are plenty of Examples of regular expressions that could be implemented with a Script Task. They can be implemented in VB or C# and knowledge of such a transform is something that any ETL developer should be able to do, or learn to do. They don't need to memorize Regular Expressions or become an expert, but they should be able to create a basic implementation and test/verify the patterns created and shared by others.

This is a great professional development opportunity for your developer! I'd suggest giving them the time, support, and encouragement to expand their development skills with this... along with encouraging their own use of this site and search engines to navigate their way through a solution. Whatever you do and search for though, start by breaking the address down into component parts. You will find numerous US Postal Code validation regular expressions, that are relatively easy to comprehend and apply.

If you do not have the time or capability there are third party packages, like Task Factory, that have components that can address this need.

On the higher end of development skills (used to ensure a true address, not just standardizing text) is using a web service like Bing Maps to geocode/resolve addresses. Such a service should be able to not only re-fromat addresses to correct values, but correct or identify bad data issues that may exist in your data. This would be a much more advanced solution.


mysqldump -u username -ppassword --databases mydb >with.sql

This gives you dump with top most statement

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb`
    /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mydb`;

In this case client doesn't need to create a database prior to restoration and database will create with default properties .

So in this case you can't change the name of database at another end. Moreover, while using replication some filtering rules will not work.

mysqldump -u username -ppassword mydb >without.sql

In this case table will create in any database, so prior to restoration you need to select the database in which you want this dump to be restored.

This will facilitate you to restore the dump with database name of your choice.

If you will not execute

        use databasename;

prior to restoration, it will throw the error and dump not executed.

So I must say, difference lies on your requirement.


Differences between Latin1_General_CI_AS and Latin_General_BIN collations?

_BIN will order in the binary (ASCII order) of characters while CI_AS will use dictionary order with case insensitivity and accent sensitivity so BIN will treat "A" and "a" as different characters and order them accordingly and sort the alphabet in this order.

  ...XYZ[]^_`abc

That is the characters will be sorted by their ASCII code values

As a simple test

declare @Chartable table (character char (1))
insert into @Chartable (character)
Values ('a'),('A'),('z'),('Z'),('_'),('æ'),('Æ')
SELECT  character COLLATE Latin1_General_BIN
   from @Chartable order by character COLLATE Latin1_General_BIN
SELECT  character  COLLATE Latin1_General_CI_AS
   from @Chartable order by character COLLATE Latin1_General_CI_AS
SELECT distinct character COLLATE Latin1_General_BIN
   from @Chartable order by character COLLATE Latin1_General_BIN
SELECT distinct character  COLLATE Latin1_General_CI_AS
   from @Chartable order by character COLLATE Latin1_General_CI_AS


Check sp_whoisactive by Adam Machanic - http://whoisactive.com/
It has @find_block_leaders parameter.


What exactly is the 'online' in OLAP and OLTP?

It is simply a remnant of olden times, when it was used in contrast to batch processing. "Online" here means "interactive", that is, requests to the database are processed as they come and responses are given more or less immediately, or at least as soon as they are available. Batch processing would collect requests into, well, batches, and execute them on schedule; responses would be given after the entire batch execution (e.g. next morning).

Abbreviations OLAP and OLTP hint at another historical artifact:  "on-line" used to be the more common spelling until mid-1980s.


Replication Setup what's the value of MySQL auto_increment and offset in my.ini

I'd answer your confusion about auto-increment* variables.

Remember:

auto_increment_increment controls the interval between successive column values.
auto_increment_offset determines the starting point for the AUTO_INCREMENT column value.

So if you're setting 6 servers, you can set them as:
auto_increment_offset=1,2,3,4,5,6
auto_increment_increment=6

so on each servers insertion series will be like:
1,7,13...
2,8,14...
3,9,15...
4,10,16...
5,11,17...
6,12,18...

Idea here is just to generate unique auto-increment numbers.

BUT WAIT!!!!!

Before you begin, when you say 6 servers it will be pair of 3 master-master or are they slaves of original master? If they're slaves, are they going to accept writes (not good!)? Are you going to write on all nodes (in case of master-master?  The architecture isn't clear enough and hence your solution of auto-increments might not be right / required as well.

Anyways... I hope we fixed the confusion of auto-increment. Trust the documentation and don't get confused by too many articles.


How do I efficiently partition a large MySQL (Aurora) table with multiple search columns?

I would consider using a date range as the partitioning scheme, and splitting by week which you can do using a function against the timestamp field.
Using days would create too many partitions, and months would not help your queries much especially when the range spans two months.

Using the range partition, you can create mutliple partitions covering your active time period, and out into the future for a year, then nearer the end of that range you can add partitions to the schema as required.
If necessary, you can also drop the old partitions when they are no longer needed.

I can't prepare a sample partition schema for you, as I'm running short of time at the moment, but the MySQL docs have pretty good coverage for this type of partitioning.

Hope that helps,
Dave


Distinct result with row_id for limit query

Would seem like you do not need any CTE, subquery or even JOIN at all. Just an EXISTS semi-join and an additional OFFSET:

SELECT name
     , MIN(score) AS s
     , ROW_NUMBER() OVER (ORDER BY MIN(score)) AS n  -- still needed?
FROM   sample1 a
WHERE  EXISTS (SELECT FROM sample2 s2 WHERE s2.mode = a.mode)
GROUP  BY name
ORDER  BY s
OFFSET $1
LIMIT  $2;

A large OFFSET is always costly, as all the leading, discarded rows have to be considered in the ordering first.

If it's a read-only table, you could add a MATERIALIZED VIEW with an index on the row number and query with SELECT * FROM mv WHERE n > $1 AND n <= $1 + $2.
Would help most for big $1 and small $2.


Have you established what you are bottlenecked on in your import? If you are CPU bottlenecked, there is a very limited amount you can do other than put CPUs with faster single-thread performance into your private cloud instance.

A few things that will help:

innodb_doublewrite=0
innodb_flush_logs_on_trx_commit=0
innodb_io_capacity=<approximate amount of IOPS your storage can provide when using 16KB random writes>
innodb_log_file_size=1G
innodb_log_files_in_group=24 # you will want to change this later!
performance_schema=0
slow_query_log=0
general_log=0

Note: Make sure you remove all of these options after your import is complete, they are dangerous and not sensible for regular runtime operation! These are equivalent of making your race car go faster through weight saving that comes from removing the roll cage and seat belts.

Enabled disk write cache on your import machine's virtual disk, if the driver supports such a thing.

If you can get enough RAM during the import, once your ib_logfile* files are created, stop mysqld, put those files and ibdata1 on tmpfs, and symlink them in. When you are done, stop mysqld and move them back.

If ZFS is an option for you, put your /var/lib/mysql on ZFS on a volume with the following settings:

compression=lz4
logbias=throughput
recordsize=16k
sync=disabled
xattr=sa

This will approximately double your write throughput compared to other file systems, due to compression and sync=disabled functionality. Make sure you set sync=standard after the import is complete.

If you combine all of the above and you are disk I/O limited, you will see a performance improvement in the multiples. If you are CPU limited, the above will give you double-figure % points of improvement.


It's not difficult. Just need a set section in your LOAD DATA INFILE command.

mysql> create table tst ( datecol datetime );
Query OK, 0 rows affected (0.04 sec)

mysql>

File:

[root@node1 ~]# cat /tmp/tst.dat
01-JAN-03
21-MAR-09
28-FEB-11
[root@node1 ~]#

Test:

mysql> load data infile '/tmp/tst.dat'
    -> into table tst
    -> fields terminated by '|'
    -> lines terminated by '\n'
    -> (@datecol)
    -> set
    -> datecol = str_to_date(@datecol, '%d-%M-%y');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from tst;
+---------------------+
| datecol             |
+---------------------+
| 2003-01-01 00:00:00 |
| 2009-03-21 00:00:00 |
| 2011-02-28 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)

mysql>


MySQL : How to know if Trigger was fired

You can do it the hard way by enabling MySQL logging (all SQL queries will be logged). I'm doing it when I'm trying to debug & understand complex code base with big amount of SQL queries, with SQL procedures, events & triggers. It's ugly, but it works.

https://support.plesk.com/hc/en-us/articles/213374189-How-to-enable-MySQL-logging-

How to Log All mysql queries into log file?

Debian-based systems:
/etc/mysql/my.cnf

[mysqld]
# Set Slow Query Log
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
log_queries_not_using_indexes = 1

# Set General Log
general_log = on
general_log_file = /var/log/mysql/mysql-general.log


Cardinality Estimate for LIKE operator (Local Variables)

The guess for LIKE in your case is based on:

G: The standard 9% guess (sqllang!x_Selectivity_Like)
M: A factor of 6 (magic number)
D: Average data length in bytes (from statistics), rounded down to integer

Specifically, sqllang!CCardUtilSQL7::ProbLikeGuess uses:

  Selectivity (S) = G / M * LOG(D)

Notes:

The LOG(D) term is omitted if D is between 1 and 2.
If D is less than 1 (including for missing or NULL statistics):
D = FLOOR(0.5 * maximum column byte length)

This sort of quirkiness and complexity is quite typical of the original CE.

In the question example, the average length is 5 (5.6154 from DBCC SHOW_STATISTICS rounded down):

Estimate = 10,000 * (0.09 / 6 * LOG(5)) = 241.416

Other example values:

 D  = Estimate using formula for S
 15 = 406.208
 14 = 395.859
 13 = 384.742
 12 = 372.736
 11 = 359.684
 10 = 345.388
 09 = 329.584
 08 = 311.916
 07 = 291.887
 06 = 268.764
 05 = 241.416
 04 = 207.944
 03 = 164.792
 02 = 150.000 (LOG not used)
 01 = 150.000 (LOG not used)
 00 = 291.887 (LOG 7) /* FLOOR(0.5 * 15) [15 since lastname is varchar(15)] */

Test rig

DECLARE
    @CharLength integer = 5, -- Set length here
    @Counter integer = 1;

CREATE TABLE #T (c1 varchar(15) NULL);

-- Add 10,000 rows
SET NOCOUNT ON;
SET STATISTICS XML OFF;

BEGIN TRANSACTION;
WHILE @Counter <= 10000
BEGIN
    INSERT #T (c1) VALUES (REPLICATE('X', @CharLength));
    SET @Counter = @Counter + 1;
END;
COMMIT TRANSACTION;

SET NOCOUNT OFF;
SET STATISTICS XML ON;

-- Test query
DECLARE @Like varchar(15);
SELECT * FROM #T AS T
WHERE T.c1 LIKE @Like;

DROP TABLE #T;


Mysql 5.6.17 drop large table locks the server for minutes

RDBMS in general aren't best at dropping tables at runtime, tends to be done more at deployment time.  That aside, there are times when whatever process (thinking ETL) in facts need to create/drop tables.  How about, try to TRUNCATE the table first, and then DROP it.  See if that reproduces the locking behavior you are seeing.


Does MySQL InnoDB table perform faster when data is removed

InnoDB may not release the diskspace but your query on your table is actually hitting less data. The size of your actual table has been reduced, but InnoDB will keep the storage for later growth on that table (or any other table in that database).

Your InnoDB table may or may not perform better depending on what percentage of total rows you purged from your table. If you removed 5% of total rows, for example, you probably won't see noticable speedup (up to 5%). If you removed 90%, you'll see it speedup quite a bit.

Some other approaches to increasing performance is writing a better query or indexing the table such that lookups are faster.


Implementing a one to zero or one relationship in SQL

I want a serious piece of DB theory

Modern relational theory rejects nulls, which would seem to immediately invalidate your option 1. However, this strawman may be eliminated by replacing the default null with a default value e.g. a 'dummy' customer created solely to explicitly model the "is not a customer" correspondence.

I think your option 2 is the most theoretically sound because, unlike the modified option 1, the relations can be in sixth normal form (6NF), being a projection-join normal form and the highest normal form attainable.

I have also heard of a design rule of thumb that states that a relation should model EITHER an entity OR the relationship between entities but never both, which seems sensible to me. Again, this would favour option 2. However, I heard of this rule of thumb many years ago, don't recall where and can offer no serious theoretical basis (other than 6NF as mentioned above).


SQL Server Job History Disappears After 5 Days

That job has to do with policy-based management, not SQL Server Agent jobs in general. In Object Explorer right-click SQL Server Agent and select Properties, then move to the History tab. What does it say here:

You can adjust those settings so that it keeps more rows overall, more rows per job, and keeps history longer. I am guessing either you have a lot of jobs on this server or these settings have been changed.


Why does Oracle not support an autonumber data type?

Well it does now - Oracle 12c introduced IDENTITY columns, see:

Identity Columns in Oracle Database 12c Release 1 (12.1)

e.g.

CREATE TABLE identity_test_tab (
   id          NUMBER GENERATED ALWAYS AS IDENTITY,
   description VARCHAR2(30)
);


If all tables in your database uses InnoDB then fire Optimize table table_name ....make sure

"if your table size is 100MB then double of this size must be available in datadir drive.."

If you are using MyISAM you can check with MyISAMcheck utility to optimize myisam tables...

Which MySQL version you are using? If your MySQL DB version is 5.6 or 5.7 then ibtemp will be there in datadir. Normally MySQL not able purge all temporary tables which is created by user stored in ibtemp and it grows day by day. If you are affordable downtime then take restart of MySQL service. It free disk space used by ibtemp temporary Tablespace.

Read about innodb row format you will get idea..


pt-online-schema-change's triggers fail with "DELETE command denied" on insert

The issue turned out to be that the triggers that the pt-online-schema-change tool creates for insert and update use the REPLACE DDL command of MySQL, which requires the DELETE permission to execute.


You are getting soft faults and this is a non-issue. Soft faults are part of the normal page lifetime as the process working set gets continuously trimmed.

If you also experience application problems then you need to investigate the cause. Read How to analyse SQL Server performance for a starting point.


What's wrong in this SQL Query? I am trying to update a column value

You never set your variable @Watermarkvalue to anything, so the value of the variable is NULL.

So this line:

Set @tabName ='SELECT '+ @watermarkValue +'= MAX([LOAD_DATE]) FROM '+ @tableName

Will always be NULL.

You want to fill your variable, so think you need something like this: (change to correct datatype)

ALTER PROCEDURE [dbo].[usp_UpdateWatermark]
 @tableName nvarchar(500)

AS
BEGIN

 DECLARE
 /* ============= Variables ============= */
  @watermarkValue  nvarchar(MAX)

  DECLARE
 /* ============= Variables ============= */
  @tabName  nvarchar(MAX)

  DECLARE @ParmDefinition nvarchar(500);
  SET @ParmDefinition = N'@watermarkValue int OUTPUT';

  SELECT @tabName = N'SELECT @watermarkValue = MAX(ID) FROM ' + @tablename;

 Execute sp_executesql @tabName,@ParmDefinition,@watermarkValue=@watermarkValue OUTPUT;

 UPDATE dbo.WatermarkTable
 SET  WatermarkValue  = @watermarkValue
 WHERE TableName = @tableName
END


Can a Key Preserved Table have a Composite Primary Key, on Oracle db?

The problem is that we know that the join will produce only 1 row (and no more) in the view for every row in MY_CFG_TABLE but Oracle doesn't. As described in OraFAQ: Updatable Join Views (emphasis mine):

  Clearly the view cannot contain DISTINCT, GROUP BY, CONNECT BY, aggregate functions, UNION, INTERSECT, or MINUS clauses: simple joins are all that is allowed. There is one other restriction: the view must be key preserved. This means that the join must meet the following restrictions:

  The join criteria must be equals (=) joins.
  Tables other than the table that is to be updated must be joined on a primary or unique key.

  If these conditions are violated, then Oracle cannot guarantee that the view will return one row only for each row in the base table. If two or more rows in the secondary table mapped to each row in the base table, then the update would be ambiguous. An attempt to update a non-key-preserved view will result in an Oracle error.

I assume that either the joined table (which is actually a system view) does not have a UNIQUE or PRIMARY KEY constraint or one of the underlying tables (when the whole view is expanded) is not joined on its unique/primary key. This might be causing the errors when we try to update the view.

The problem may be more complicated as we don't know the exact definition of the underlying ALL_TAB_COLUMNS view. It might have some non-equality joins or non-inner joins or unions that make the new view non-updatable.


Depending on the version of the driver you're using, the timestamp of the data could be generated either on server, or on client (Java driver 3.x, for example). You need to check that all servers and client machines are synchronized via ntp.


Is using association tables for One-to-Many relationships common practice?

For the simple case you are right and there is no need to have the separate link table for a one<->many relationship.

Off the top of my head I can only think of a couple of examples where it might be a requirement to have a separate table for the relationship:

If you are tracking the history of the rows (in your business logic layer, via triggers, or "temporal tables" if Oracle supports them) and there are properties of the relationship that you don't want to track changes to in the equipment table itself. Perhaps those codes that you describe, or maybe the person currently responsible for maintaining the equipment in that location.
If you envisage needing to support a many<->many relationship later.
Though that seems rather unlikely in this case.
If you have to support some oddity in a legacy framework which requires link tables for all relationships (which is plausible as you already describe needing to work around other legacy oddities).
It is simple local practise to work this way (obviously this does not make it a good idea, but it might make it one you don't have authority to fight against!)


Please see this which consists of all the things you need to do before you add a DB to existing AOAG :

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


Changing tx_isolation and binlog format on master server - do i also need to adjust slave?

The answers we got back from one of the senior DBA's at our hosting provider are as follows. I'm sharing in case anyone else runs in to this.

  It is generally quite critical to have the same settings on the slave
  as the master. An easy reason recommend this is that you might promote
  that slave to master some day and do not want surprises. There are
  other technical reasons for this, so I suggest that the slave use the
  same settings (aside from server-id and maybe the names of log files)

  You can technically change the settings dynamically without a restart
  but this is pretty tricky to get right since existing connections will
  use the old settings and a restart is sometime faster than trying to
  update all sessions to use a new binary log format by forcefully
  disconnecting them, then repeating the process with tx isolation (this
  matters for long lived connections which may not exist in your
  environment)

  I would suggest you add the settings to the slave and master's my.cnf,
  then restart them in any order. You do not need to manually run "stop
  slave" or "start slave"

  The slave does not have binary logging on, nor log-slave-updates, so
  there is nothing specific about the order of operations to manage.

To recap:

1) When we put this change on our production master/slave setup, do we need to apply this to both the master and slave, or just the master?

Both, primarily to be able to promote a slave to master without surprises.

2) Is there any special sequence we need to do things in? or can we just adjust my.cnf and restart mariadb?

Not really. Just adjust settings and restart in any order.

3) Do we need to 'stop slave' while applying this change?

No.


Based on what you provided: generic form with drop downs. Have predefined spaces to fill in. A dropdown with the question that can be chosen and then a box for text. Then include a "Add another section". Which will simply add another section to the bottom. This allows the form to expand and contract based on what they did. It is endlessly expandable. You don't have to keep redesigning the whole each time. Not sure that will work for you but it is one possibility.


mysql launch fails after upgrade to 5.7.11

mysql.local.err  Thank you so much @Rick James. The log showed that some of the my.cnf values were failing, about 5 IIRC!  WTF? I expect there were old values from the previous install which are no longer valid.

After I commented them out, the server started up fine.


Return master and master details record(s) in a single row in postgresql

It's really hard to tell what exactly you want, but maybe something like this:

select jsonb_build_object('customer', to_jsonb(c),
                          'total_amount', pd.total_amount,
                          'payment_details', pd.payments)
from customer c
  join lateral (
     select jsonb_agg(jsonb_build_object('amount', amount, 'date', payment_date)) as payments,
            sum(amount) as total_amount
     from payment p
     where p.customer_id = c.customer_id
  ) pd on true


They changed the layout of the cluster manager screens (and some of the terminology) from Windows Server 2008 R2 -> 2012. It's not a meaningful difference; watch newer videos.


Going against the grain - backing up and truncating the tran log

I'm assuming you have these databases in FULL RECOVERY because you need to be able restore to a point in time.  If that's NOT the case, then use SIMPLE mode and your log would only grow to the size needed to contain a transactional unit of work (which could be index maintenance as well as application transaction).

Now, back to the FULL RECOVERY model.  Backing up the log on a periodic cycle (whether it's 1 minute or 15 minutes) is only going to help you if transactions are able to commit during that time frame.  Active long running transactions will prevent that log space from being flagged for reuse.

There shouldn't be a problem with running transaction log backups every minute (Back Up Transaction Logs Every Minute. Yes, Really).

Constantly shrinking the log will have a negative performance impact due to waiting for the log to grow again.  Logs do not honor Instant File Initialization.

The bottom line is:

The transaction log has to be large enough to hold an active unit of work and you need timely log backups to allow of log reuse.


trying to make the database as efficient as possible

There are at least two different types of efficiency that are really at play here:

space (disk and memory)
speed

Under certain conditions (as described in Outman's answer, which is a copy/paste of the "Recommended Uses / Guidance" section of my blog post, linked at the top of that answer) you can save space, but that is entirely dependent on the type and per-row quantity of characters.

However, at least in its current implementation, you are more likely than not to have a decrease in speed. This could be due to how they are handling the UTF-8 data internally. I know that when comparing UTF-8 data to non-UTF-8 VARCHAR data, both values are converted to UTF-16 LE (i.e. NVARCHAR). I wouldn't be surprised if other (perhaps even most) operations needed to convert the UTF-8 data into NVARCHAR given that is how Windows / SQL Server / .NET have always handled Unicode.

So, assuming that you have a scenario that could possibly benefit from using UTF-8, you need to choose which efficiency is more important.

Now, whether or not UTF-8 will benefit scenarios where the environment itself is naturally UTF-8 (e.g. Linux) remains to be seen. Typically the database driver (ODBC, SQL Native Client, etc) handles the translation between client and server. I suppose there could be a performance / efficiency gain here if doing this would result in the driver software skipping the additional steps (and CPU cycles) it takes to do those encoding translations. So far this is just a theory as I have not tested it.

Just keep in mind the following:

UTF-8 was designed to achieve ASCII compatibility for easier implementation. This allows systems that are Standard ASCII-based (values 0 - 127; values 128 - 255 are Extended ASCII and not covered by this) to enable Unicode without having to re-save anything in a new encoding.

For SQL Server, the goal was that existing apps that are currently using VARCHAR can start supporting Unicode without needing to do much re-coding (i.e. adding N prefixes to string literals) or updating datatypes from VARCHAR to NVARCHAR.

It was not designed to be a form of compression. If you have data that has a reduced footprint in UTF-8, then great. But when working with data that is not Standard ASCII, then either won't have any savings, or even worse, you might increase the data size by going to UTF-8 (given that 63k of the 65k BMP characters are 3 bytes in UTF-8, which is 1 byte more than the 2 bytes they require in UTF-16).

And, if UTF-8 gives a performance gain, or at least you don't see a drop in performance, then great. But, don't expect it. In fact, don't be surprised if you happen to see a decrease in performance.
If you decide to implement the UTF-8 collations in SQL Server, you need to be aware of some potential data "issues":

Data loss from mixing UTF-8 string literals and/or variables (due to the current database having a UTF-8 default collation) and non-UTF-8 VARCHAR columns. This is caused by collation precedence effectively downgrading the collation from UTF-16 to whatever code page the column is using.
Minor truncation from mixing non-UTF-8 string literals and/or variables with UTF-8 columns (and, in some cases, variables). This is caused by certain characters
requiring more bytes in UTF-8 than they did in their original encoding.
Invalid byte sequences in UTF-8 can throw an error instead of returning the default replacement character "�". This is a different approach than has been taken so far with invalid sequences in any other 8-bit encoding or in UTF-16.

For more details and examples, please see the "Things to Keep in Mind: Operational" section of my post: Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?


So, after Mark's question above, I did some more searching around with much more generic terms (essentially ignoring the loss of the users and just focusing on the fact that the copy job was failing), and came across this thread, this thread, and this thread all of which seem to pretty closely match what I'm experiencing.  This lead me to the observation that, while the views functioned in use, their dependencies were no longer marked, which was what caused the copy job to fail.

While I'm still a bit curious as to why a failed copy job would wipe out the users in the source database (and why it seems "stuck" in SMO mode), I think this should more or less answer my question.  Thanks everyone.


For clarification

RPO = Recovery Point Objective; the point in time that you will be restoring to in the case of data loss.
RTO = Recovery Time Objective; the duration it will take to restore to the time defined in RPO.

You might be able to answer your question yourself if I supply you with ample information and give you some ideas to ponder.

Recovery Point Objective (RPO)

Is the RPO defined by business really feasible? Will the business survive with an RPO of one day (or 24 hours)?

Recovery Time Objective (RTO)

Can you bring your database(s) back online to a point-in-time defined by the RPO? Please don't forget: The RPO is a duration. If your database crashed as 2pm in the afternoon then you have to be able to restore the data at least to 2pm in the afternoon the day before. (And not just to 6pm the night you took the backup).

Transaction Log Backup: Yes / No?

Seeing as you now know that RPO and RTO can be relevant for your backup concept, I can now answer your question about the database's Transaction Log. A differential and/or full backup of the database will not free the transaction log, if the database is in FULL recovery model.

Reference: Misconceptions around the log and log backups: how to convince yourself (SQLSkills.com, Paul Randall)

You must perform a BACKUP LOG .... to CHECKPOINT the Transaction Log and allow it to free up space should it be required.

Existing Backup Concept

If your RPO is for 24 hours data loss and the RTO is 4 hours (for one database system), how will you fair if you have to rebuild 10 systems or 20 systems? Will you be able to restore all 20 databases to a point-in-time with the given RPO of 24 hours and an RTO of 4 hours?

Possible solution

Perform a BACKUP DATABASE ... once a week (e.g. Sunday)
Perform a BACKUP DATABASE ... WITH DIFFERENTIAL... every other day, except e.g. Sunday
Perform a BACKUP LOG ... every other hour
Store data in a different location other that your main database(s), or even in an off-site location (geographical redundancy)

This can help ensure that you can restore to the RPO requested by your company's Business Continuity (BC) team and Information Technology Service Continuity (ITSC) team.

You will also have to somehow guarantee that the database backups (FULL, DIFF and TLOG) are stored in a location that will not be affected by a down-time/data-loss in your current location (e.g. separate data centre, off-site data centre).

When storing data off-site, ensure that you can still guarantee the RTO if you have to calculate in some additional time for data copy operations or restore over slower network connections.

References

Recovery point objective (Wikipedia)
Recovery time objective (Wikipedia)
The difference between RTO and RPO (Techadvisory.org)


Help with optimizing Oracle Query - nested with count

SELECT c.phoneid, COUNT( /* DISTINCT*/ s.StudentID)
FROM Students s, Cellphone c
WHERE s.phoneid = c.phoneid
GROUP BY c.phoneid
ORDER BY 2 DESC
LIMIT FIRST 1 ROW /* WITH TIES */

?


Database Full Backup Maintenance Task has large delay between databases

This is sometimes due to an MSDB.dbo.backupsethistory table that's never been purged. SQL Server inserts a row into that table, then goes back and updates it later during the course of doing backups.

If your MSDB is on really slow storage, and you can't cache it in memory, and it's got a lot of history, then accessing that table can be your biggest bottleneck. I wrote a blog post called Brent's Backup Bottleneck: MSDB about a really bad occurrence of it.

To check that, run sp_Blitz (disclaimer: free open source script I wrote) and one of the warnings is MSDB History Not Purged, which warns if you've got more than 60 days of history stored.

To fix it, add a History Cleanup Task to your maintenance plans. However, if you're in this spot - where updating this table is taking forever - then clearing out history is probably going to take forever, too. You may have to run sp_purge_jobhistory manually yourself, nibbling off a little history at a time, to keep downtime low.


CTE column caused an overflow - Order By only!

SQL Server does not guarantee the timing or number of evaluations for scalar expressions. This means that a query that might throw an error depending on the order of operations in an execution plan might (or might not) do so at runtime.

The script uses CROSS APPLY where CROSS JOIN was probably intended, but the point is that the potential number of rows over which the ROW_NUMBER is calculated depends on the size of the sys.objects cross product.

For a database with sufficient objects, an overflow for the DATEADD result is an expected risk. For example, this is reproducible using the AdventureWorks sample database, which has 637 entries in sys.objects. The size of the cross product is 637 * 637 = 405,769; the following throws an overflow error:

SELECT DATEADD(DAY, 1 - 405769, GETDATE());

One might argue that there is no need to materialize the result of an expression for rows that are not returned (and therefore not throw an error), but that is not the ways things work today.

Consider:

The highest ROW_NUMBER will give the lowest value for DateCode_FK in the DATEADD(DAY, 1 - ROW_NUMBER()... expression
The presentation ORDER BY is DateCode_FK DESC
Only the first 10 rows in presentation order are required

If the optimizer contained logic to reason that lower row numbers lead to higher DateCode_FK values, an explicit Sort would not be needed. Only ten rows would need to flow through the execution plan. The ten lowest row numbers are guaranteed to produce the ten highest DateCode_FK values.

Regardless, even where a Sort is present, the argument is that SQL Server should not throw an error because none of the ten rows actually returned are associated with an overflow error. As I said above, "that is not the ways things work today".

An alternative formulation that avoids the error (though it is still not guaranteed to do so - see my opening remark), makes the row numbering deterministic, and uses CROSS JOIN:

WITH cte_Date (DateCode_FK) AS
(
    SELECT TOP (10)
        DATEADD
        (
            DAY,
            1 - ROW_NUMBER() OVER (
                ORDER BY
                    so1.[object_id],
                    so2.[object_id]),
            GETDATE()
        )
    FROM sys.objects AS so1
    CROSS JOIN sys.objects AS so2
    ORDER BY
        ROW_NUMBER() OVER (
            ORDER BY
                so1.[object_id],
                so2.[object_id]) ASC
)
SELECT TOP (10) -- Redundant TOP
    d.DateCode_FK
FROM cte_Date AS d
ORDER BY
    d.DateCode_FK DESC;

Paste the Plan


Oracle 11g hash join vs nested loops question

Try to execute the query with gather_plan_statistics hint. Then use dbms_xplan to display exec plan. You will see E-rows(estimated) and A-rows(Actual). This is where I would start, to check whether the optimizer is wrong in it's assumptions or not.

Also check v$sql_plan of the running query and check whether TEMP space is really used or not. Especially on older Oracle versions hash joins were slower, because hash table was too big, it did not fit into PGA and had to be stored on disk - thus generating too mush disk I/O.


REPLMERG.exe cannot connect to distributor

REPLMERG.EXE . . . -DistributorSecurityMode 0  . . .

Per the command line parameters for replmerg.exe

-DistributorSecurityMode [ 0| 1]
Specifies the security mode of the Distributor. A value of 0 indicates SQL Server Authentication Mode
(default), and a value of 1 indicates Windows Authentication Mode.

But you did not include
-DistributorLogin distributor_login Is the Distributor login name.
-DistributorPassword distributor_password Is the Distributor password.


Empty Strings: Why or when is '' equal to ' '?

Trailing blanks explained:

  SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2,
  , General rules #3) on how to compare strings
  with spaces. The ANSI standard requires padding for the character
  strings used in comparisons so that their lengths match before
  comparing them. The padding directly affects the semantics of WHERE
  and HAVING clause predicates and other Transact-SQL string
  comparisons. For example, Transact-SQL considers the strings 'abc' and
  'abc ' to be equivalent for most comparison operations.

  The only exception to this rule is the LIKE predicate. When the right
  side of a LIKE predicate expression features a value with a trailing
  space, SQL Server does not pad the two values to the same length
  before the comparison occurs. Because the purpose of the LIKE
  predicate, by definition, is to facilitate pattern searches rather
  than simple string equality tests, this does not violate the section
  of the ANSI SQL-92 specification mentioned earlier.

Here's a well known example of all the cases mentioned above:

DECLARE @a VARCHAR(10)
DECLARE @b varchar(10)

SET @a = '1'
SET @b = '1 ' --with trailing blank

SELECT 1
WHERE
    @a = @b
AND @a NOT LIKE @b
AND @b LIKE @a

Here's some more detail about trailing blanks and the LIKE clause.


Performance problems with TOP and user defined functions in views

Why don't you call a stored procedure via NHibernate instead of asking it to query the view?  That would allow you to write your own query and tune as needed....

I don't know NHibernate very well but this blog post looks like a reasonable resource.  In case that link dies here are the basic steps:

Write your stored procedure to select the data in the most efficient manner.
Write a class that represents the data you're selecting (a DTO would be perfect).
Write a NHibernate mapping that tells it how to associate the fields
Specify the name of the stored procedure to use and any parameters when you query the database via NHibernate.

It might be a good idea to check out this article on StackOverflow related to tuning performance when using NHibernate.


Hash index vs. btree index performance on inserts and updates

A B-tree index will be the best when it comes to the speed of data modifications.

There is no way to significantly increase the speed of inserting a few hundred rows.

If you need the uniqueness guarantee, you cannot do without the index, so there is not much room for improvement.


The guidelines for different volumes were more of general best practices when you're trying to squeeze your performance/cost ratio.  Outside of that, putting them in different volumes not only helps segregate workload which could cause performance to be affected by SSD functions such as TRIM or how it deletes data for example, but also helps you with management.

Transaction logs segregation is also very important for ACID compliance.  If you don't have your Tlogs in a different volume and the data volume goes down, you just lost all of your transactions that were not backed up yet.  See Tail of the log backup.  This way you know you can still recover the transactions and restore to a point in time or right before the crash.

Partition alignment is done at the disk level by the OS so you'd still want to ensure you are partition aligned.  Some flash drives might not need that such as the FGPA powered ones which treat SSDs differently a bit but why not double check anyways?  It's easy and fast to.

The 64kb is done more so by SQL Server because that's how big an extent is.  Your vendor could make it 256kb but you should get clear documentation as to why they recommend that.  Do they really read that much data per operation?  If so, great, set it.  If not, stick to what SQL Server as the underlying DB engine needs.

Personally on my all flash storage arrays based on my performance testing and expected growth I tend to put Data/TempDB on the same volume, Transaction Logs on a different one, and backups on another one.  This way if for some reason a backup all of a sudden is huge, or some job for some horrible reason doesn't delete some old data we don't interfere with the data drives.  I test restore and check every backup nightly but I trust my scripts to delete the data which has worked fine.  However, if one day it doesn't, I will know as the backup will fail, there will be no restore, and I'll be alerted along with the dashboard.  These could be mitigating controls you could utilize if you wish.

Edit/update based on question:

Well TempDB will always create as much IO as it needs to depending on your queries, but from what I read you were doing almost all reads and not writes.  TempDB would then be doing the bulk of the writes in your use case.  Thus it would be easy to determine how much tempdb is writing and see if it gets closet to the IOPS limit you have available.  From what I read your old system didn't have any performance issues and the new SSD as opposed to magnetic disk system would be much faster unless you shrunk a massive raid array into 1 disk or something.

When you're factoring in if TempDB contention is going to be an issue or not note the higher IOPS you get with a bigger RAID array as well.  A RAID 5/6 array will still give you all the read performance boost without penalty so in your use case you might get more IOPS by combining your TempDB and data array together, than you would segregating them; again based on usage pattern.

Easy answer?  Check perfmon to see your throughput and if you know the queries are largely going to remain the same (reasonably) and it should show you have to much in IOPS that it doesn't matter which one you go with.  Pick the one you'd be more comfortable managing and scaling with. :)

-Tail Of The Log Backups:

Yes, if your transaction log drive(s) go down you will lose any transactions that have not been hardened to disk somewhere (a dirty page) and have not been yet backed up.  Careful monitoring and proper raid arrays are mitigating controls for that, but many people also replicate/mirror/AG the data elsewhere so there is a copy that is very closely up to date.  Some people like Brent Ozar prefer to run transaction log backup scripts continuously and restore them dynamically from queries if needed.


Output File Default Location using MultiServer Agent Jobs

I found the answer already asked about the new variable, releases in SQL Server 2014, "SQLLOGDIR", which makes perfect sense that my 3 instances of SQL Server 2008 R2 wouldn't know what to do with that.
Here's the link:SQLLOGDIR


Best Practice to Archive Old Data in MySQL

With InnoDB and PARTITIONs, you could set up daily PARTITION BY RANGE(TO_DAY(...)) and use "Transportable tablespaces" to disassociate a day from the table and move it separately.  This will be a lot faster than the queries it would take to do the INSERT...SELECT and DELETE.  Once detached, the partition (now a table in its own right) can be moved to another database or server without impacting the ingestion of more data.

One drawback:  InnoDB's disk footprint is a lot more than Archive's.

What kinds of queries do you apply to the logs?

Blogs:
partitioning
and
chunking lengthy deletes


EER Diagram - subclasses which are neither disjoint nor fully overlapping

Assumptions:

A service can be of either type A or B. Not both.
A request can be for either a service of type A or for two services (one of type A and one of type B).
A client can have many requests.

I think a clean option is to have a Service_Request entity which associates the entity named, say, Client and Service. It will have 2 attributes, service_a and service_b where the second is optional. In SQL:

CREATE TABLE service_request
( service_request_id INT NOT NULL
    PRIMARY KEY,
  client_id INT NOT NULL                    -- who ordered it
    REFERENCES client (client_id),
  request_ordered_at TIMESTAMP NOT NULL,    -- when it was ordered
  -- more details about the request
  -- (price, duration, etc.)

  service_a_id INT NOT NULL                 -- type A service
    REFERENCES service_a (service_id),      --   (mandatory)
  service_b_id INT NULL                     -- type B service
    REFERENCES service_b (service_id)       --   (optional)
) ;

The rest of the design, the entity Service and the two subtypes (Service_A and Service_B) should stay as they are in the question.


Why should a SET TRANSACTION command show a long Duration when the CPU is low?

For a statement like that one (and the others you mentioned), network I/O is not included in the statement_completed event.  Network I/O is included in the batch_completed event.  Your suggestion to look at ASYNC_NETWORK_IO waits seems like the best next step to me.

The statement_completed can include network I/O time if the statement being executed uses a "streaming"-style execution plan (a query that returns lots of rows via a scan of a single table or simple nested loops join).  This is discussed a bit at the end of my answer here: https://dba.stackexchange.com/a/244783/6141

Note that it might not actually be a problem with slow network traffic per se.  It could be that the application server is maxed out on CPU, or the application is (for one reason or another) unable to continue to receive the results from SQL Server for a second or two.  You might want to see if they can capture performance metrics on the app server during one of these problem times as well.

Also, consider suggesting that they stop using the READ UNCOMMITTED isolation level


How to store very small values (E-314) in SQL Server database?

None of the provided numeric types (float, decimal) are able to store so small numbers. The example you have, 2.466987305926227e-314, is smaller than the smallest normal double-precision float number.
I see two options, if you want to store such numbers:

Use a custom solution with two columns, one for the mantissa and another for the exponent. The disadvantage will be that you'll have to do all the math operators and functions with complex expressions (even for addition).

Use a custom CLR User-Defined Type. From that page:

Beginning with SQL Server 2005, you can use user-defined types (UDTs) to extend the scalar type system of the server, enabling storage of CLR objects in a SQL Server database. UDTs can contain multiple elements and can have behaviors, differentiating them from the traditional alias data types which consist of a single SQL Server system data type.
Because UDTs are accessed by the system as a whole, their use for complex data types may negatively impact performance. Complex data is generally best modeled using traditional rows and tables. UDTs in SQL Server are well suited to the following:
Date, time, currency, and extended numeric types
Geospatial applications
Encoded or encrypted data


Why is a COUNT query faster than a result set query?

Exactly why probably differs between the architecture of different DBMS, but scanning the primary key index is usually faster than scanning the table itself. I would say that the effect in the engine is similar to the effect in the network, the less data you shuffle between different areas in the engine, the faster it goes


SQL Server 2012 database backup successfully report but no backup file

The msdb database keeps history of where, when, who, how big etc. of backups. I have often needed to know where a backup went or who did it or when was the last backup.

This is set to return the last two days of backup history. You can put a specific database in the value, if you leave it blank it will return for all databases. This query works for 2005+.  Just tested in 2012.

/*
Find Where DB Backups Went Physical Location
For last two days.
backupset.type
D --> FULL
I --> DIff or incrimental
L --> Log backups
*/

DECLARE @dbname     sysname
SET @dbname = ''

SELECT
     @@servername [ServerName]
    ,master.sys.sysdatabases.name [DatabaseName]
    ,msdb.dbo.backupset.backup_start_date [Backup Date]
    ,msdb.dbo.backupset.user_name
    ,datediff(second, msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date) [Duration-seconds]
    ,msdb.dbo.backupmediafamily.physical_device_name [File Location]
    ,msdb.dbo.backupset.type
FROM
    msdb.dbo.backupmediafamily,
    master.sys.sysdatabases
    LEFT OUTER JOIN
    msdb.dbo.backupset
    ON master.sys.sysdatabases.name = msdb.dbo.backupset.database_name
WHERE
    msdb.dbo.backupset.type in( 'D', 'I', 'L')
AND msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
and msdb.dbo.backupset.backup_start_date > getdate() - 2
AND master.sys.sysdatabases.name not in ('pubs','northwind', 'tempdb','adventureworks')
AND master.sys.sysdatabases.name like '%' + @dbname + '%'
ORDER BY
    master.sys.sysdatabases.name
    ,msdb.dbo.backupset.backup_start_date
    ,msdb.dbo.backupset.backup_finish_date
    ,msdb.dbo.backupmediafamily.physical_device_name
    ,msdb.dbo.backupset.type


simple SELECT EXISTS takes about two hours to execute

I think the problem stems from the one and only nullable: PRODUCT_QUANTITY. Why ?

Look at your query

SELECT EXISTS (SELECT * FROM ORDERS
WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42'
AND   PLACE_ID='2'
AND   PRODUCT_ID='2bc7277f-c7c3-43bf-9598-9466c96a8f12');

The subquery has SELECT * and the ORDERS table has 4 columns

3 with NOT NULL
1 with DEFAULT NULL

Based on the MySQL Documentation on Subquery Optimization, the MySQL Query Optimizer performs subquery optimization by means of converting a subquery as follows:

  The MySQL query optimizer has different strategies available to
  evaluate subqueries. For IN (or =ANY) subqueries, the optimizer has
  these choices:

  Semi-join
  Materialization
  EXISTS strategy

  For NOT IN (or <>ALL) subqueries, the optimizer has these choices:

  Materialization
  EXISTS strategy

In your particular case, you explicitly wrote the SELECT EXISTS (SELECT * ...). So, you are more responsible to optimize the subquery. Consequently, you should expect the MySQL Query Optimizer to start messing with you since it did not participate in the construction of the EXISTS strategy.

From the MySQL Documentation on Optimizing Subqueries with EXISTS Strategy, this is how checks on done for your SELECT:

  Becomes:

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                      AND oe_1 = ie_1
                      AND ...
                      AND oe_N = ie_N) The following discussion assumes a single pair of outer and inner expression values for simplicity.

  The conversion just described has its limitations. It is valid only if
  we ignore possible NULL values. That is, the “pushdown” strategy works
  as long as both of these two conditions are true:

  outer_expr and inner_expr cannot be NULL.
  You do not need to distinguish NULL from FALSE subquery results. (If the subquery is a part of an OR or AND expression in the WHERE clause,
  MySQL assumes that you do not care.)

The bulletpoint that says outer_expr and inner_expr cannot be NULL might be the problem. The inner_expr could present possible rows with a NULL in one of the columns in spite of all the data being non-null. To me, such a check is unnecessary.

The same document says the following in the evaluation process:

  For proper evaluation, it is necessary to be able to check whether the SELECT has produced any rows at all, so outer_expr = inner_expr cannot be pushed down into the subquery. This is a problem, because many real world subqueries become very slow unless the equality can be pushed down.

To make a long story short, slowness is being imposed because your SELECT EXISTS (SELECT * ...) is not optimal and the MySQL Query Optimizer is still abiding by its preset rules. There are checks being done on possible null results from the subquery even though you know there will be a non-null result.

SUGGESTION

Rather that doing a deeper dive into what makes your query's execution slow, I would rewrite the query as follows:

SELECT COUNT(1) FROM ORDERS
WHERE USER_ID='c85bc9f7-704f-418e-98af-a1528622dd42'
AND   PLACE_ID='2'
AND   PRODUCT_ID='2bc7277f-c7c3-43bf-9598-9466c96a8f12');

What are the advantages of rewriting the query this way ?

Subquery evaluation is completely eliminated
Return value is always non-null, either 0 or 1
Checking for 0/1 should be just as good as checking forTRUE/FALSE

GIVE IT A TRY !!!


Giving all permissions to all users for a database in SQL Server

You want local Windows users to have the same permissions as the database owner of a specific database? How about:

Manually create logins for each Windows account:

-- repeat this whole block for each user account

USE [master];
GO
CREATE LOGIN [Domain\Account] FROM WINDOWS;
GO

  -- repeat just this portion for each database that account should belong to

  USE [your_database];
  GO
  CREATE USER [Domain\Account] FROM LOGIN [Domain\Account];
  GO

Add the users to the dbo role of each database to which you've added a user account above, using dynamic SQL (uncomment the EXEC line when you think it looks right):

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
  EXEC sp_addrolemember N''db_owner'', N''' + name + ''';'
FROM sys.database_principals
WHERE principal_id BETWEEN 5 AND 16383
AND [type] IN ('U', 'S');

PRINT @sql;
--EXEC sp_executesql @sql;


Clustered Index during data load in MS SQL Server

It's important to note that a clustered index isn't really just an index, it is also the physical structure/ordering of the pages that comprise the table, not just the B-Tree of the primary key.
This is one of the areas why Sybase/SQL Server sort of dropped the ball in naming, while other vendors (IBM/Oracle) did a better job referring to them as "Index-organized tables" which IMO illustrates the concept more clearly.
So if you drop the clustered index, the table turns into a heap.  When you re-enable it, the table has to be physically rebuilt, which requires sorting data and moving pages - both expensive operations.


In addition to Phil suggestion, I'll activate tracing on sql net.

Just add:

TRACE_LEVEL_SERVER=USER

to: $ORACLE_HOME/network/admin/sqlnet.ora (create it if don't exist)

As for the listener, you can increase the tracing level using ADMIN or SUPPORT options instead of USER.
When you found the client who is causing it, you can enable client sql net traces as well.

Fatal NI connect errors are hard to determinate. But in my experience, most of them was caused by a client who left a session opened in TOAD, and then the computer goes to sleep mode.

note* If you have MOS, you should check note [1116960.1] and [1121357.1]


IN ( SELECT ... ) is often an inefficient way to design a query.  There are two alternatives below.  Also, your IN was asking to fetch the entire array; this is probably causing terrible performance.  You only need to check for the "existence" of one customer.

Note that each formulation checks for one "customer" in Table B and does not fetch the entire table (as is done with your IN ( SELECT ... )).

Two approaches:

SELECT  Customer,
        IF(b.customer IS NOT NULL, 'Yes', 'No') AS "new customer"
    FROM  `Table A` AS a
    LEFT JOIN  `Table B` AS b USING(customer);

SELECT  Customer,
        IF(EXISTS (
                SELECT  1
                    FROM  `Table B`
                    WHERE  customer = a.customer ) , 'Yes', 'No'
          ) AS "new customer"
    FROM  `Table A` AS a;

LEFT implies that the row may be missing from Table B.  This is exactly what you want in order to produce "No".

EXISTS is a "semi-join".  It stops when it finds success; this can be handy if there could be multiple matching rows (which is not your case).


You're logged in as rds_user, but you may not actually be logged in as 'rds_user'@'%'.

Privileges are actually granted not just per-username, but per username/host combination.  When you log in, you may be logging in as rds_user associated with a specific host, and that user may have less privileges.

Log in and run SELECT CURRENT_USER(); to see which user you're actually logged in as.

Run SELECT * FROM mysql.user WHERE user = 'rds_user'; to see all the users you could be logged in as.


Are multiple column index used in single column where clause

The index can be SEEKed if you search on Col1 (first column).
It can possibly be scanned if you search for the other columns, but if it is depends on whether it covers the index, estimated selectivity and stuff like that. And even if it would be used, a scan is never as effective as a seek (rest being equal).


Why transaction blocking simple insert in MySQL?

For transaction blocking, the first line of defense is to speed up the query.  Though this does not completely eliminate the possibly of block/delay/deadlock, it will often make the situation more tolerable.
Re the LIMIT 1 -- Is there only 1?  If so, why not do a JOIN.  Or might there be more than one?  If so, which one?  That is, don't you need an ORDER BY?
servertoken needs INDEX(userToken, serverToken)
Would a single INSERT ... ON DUPLICATE KEY UPDATE ... suffice for your lengthy set of queries?  If so, it would probably be a lot faster.
I see an OR in the long query.  This is likely to prevent usage of an index.  (I don't understand the intent of the query well enough to suggest an alternative.)
"RootFolderPath LIKE CONCAT (..." -- This smells like something that should be redesigned.  Consider putting path parts in separate columns?  Or something else.
Is folderId the PRIMARY KEY of folderTable?
Plese provide SHOW CREATE TABLE (as text, not image) for each relevant table.


Why isn't the transaction log getting truncated when taking a full backup?

Being in the full recovery model means you want the option of restoring to any point in time. That means having a full set of transaction log backups after any database backup.

Imagine you have a db backup from midnight yesterday and log backups from 6, 12 and 18:00. Now you take another db backup. If the log is truncated, you can't restore to 11pm (which would require the previous db backup and all the log backups since).


I am pretty sure the reason is that customerid in the view is the result of:

COALESCE(b.customerid, s.customerid::bigint)

After LEFT JOIN, cast & COALESCE, the predicate cannot be pushed down, so both tables have to be read in full, before the filter customerid = 520001215 can be applied. The fact that customerid can be NULL in both tables is not helping either. Can at least one be set to NOT NULL?

Extremely inefficient.

Run this query, and compare performance:

SELECT r.receiptnumber
    ,  r.receiptposition
    ,  r.date
    ,  c.customerid
    ,  r.price
FROM   dashboard.cc_add_entry2 c
JOIN   dashboard.receipts r USING (receiptnumber)
WHERE  c.customerid = 520001215;

UNION ALL
SELECT r.receiptnumber
    ,  r.receiptposition
    ,  r.date
    ,  r.customerid::bigint
    ,  r.price
FROM   dashboard.receipts r
LEFT   JOIN dashboard.cc_add_entry2 c USING (receiptnumber)
WHERE  r.customerid = 520001215
AND    c.customerid IS NULL;

Should be much faster. If you can't wrap it into a view, either use this query instead or write a SQL or plpgsql function that takes customerid as parameter.

Also these multicolumn indices would help some more:

CREATE INDEX foo1 ON dashboard.cc_add_entry2 (customerid, receiptnumber);
CREATE INDEX foo2 ON dashboard.cc_add_entry2 (receiptnumber)
WHERE customerid IS NULL;
CREATE INDEX foo3 ON dashboard.receipts (customerid, receiptnumber);


Why do I get leading zeros when converting from decimal to char in a db2 query?

It appears by design. See behaviour changes for v9.7 for more.

I suggest you'd need VARCHAR inside LPAD with leading spaces


Why does the PG manual state to modify the "pg_hba.conf" file before starting the server the first time?

Yes, it is purely a security measure to prevent even momentary unauthorized access.  If a hostile party on the same machine knows you are in the process of setting up a new cluster, they could easily test in a loop for it to become connectable with trust authentication.  So even if you leave it open for just a few seconds, it could be exploited in that time.

If you start the server first and then edit pg_hba.conf to make it more secure but then forget to do a pg_ctl reload or equivalent, it will remain vulnerable.   If you do do a reload, existing exploit connections will remain live even though new ones can't be made.  If you do a pg_ctl restart or equivalent, existing exploit connections will be severed.  But it only takes a few milliseconds to do serious damage.  For example, they could create a new postgres superuser account with a password of their choosing.  Or they can perhaps leverage the existing default PostgreSQL superuser account to manipulate the home directory of the OS user running the PostgreSQL server in arbitrary ways.  Including things like adding an entry to ~/.ssh/authorized_keys.


Availability Group aware backup scripts should exist on all nodes that can possibly host the primary copy of each database.  This allows the active node to perform the backup, avoiding error reports from nodes where the database is in secondary or read-only mode.

There are several excellent Availability Group-aware backup solutions, such as the ones from Ola Hallengren or Midnight DBA's MinionWare Backup


DEFAULT 'EMPTY_CLOB()' is useless. It inserts the text 'EMPTY_CLOB()' to your table. String "EMPTY_CLOB()" has 12 characters as you see.

EMPTY_CLOB() and (EMPTY_CLOB()) should be the same, i.e. an empty CLOB.


Create local table and foreign table from the same SQL

I think I'd try to get a list of tables from the .sql file (or directly from the server to which you applied the .sql file), then feed that list of names to IMPORT FOREIGN SCHEMA ... LIMIT TO (...).  That seems less error prone than what you are envisioning.


How to create tables with geometry in Postgres?

The simple answer is:

create table points ( p POINT not null);
create table lines ( l LINE not null);

Then inserts look like this:

insert into points (p) values (POINT(1.2, 123.1));
insert into lines (l) values (LINE(POINT(1.2, 123.1), POINT(-5, -123)));

You probably want to build indexes if you want to have some lookups on them. Also a primary key may be beneficial.


Using system tables to identify the columns of a table index in Sybase

I came up with the following solution - thanks to the comment by Phil:
SELECT
     distinct i.name as index_name, index_col(o.name, i.indid, c.colid) as name
FROM
    sysobjects o
    INNER JOIN sysindexes i on i.id = o.id
    INNER JOIN syscolumns c on c.id = o.id
WHERE
    o.type = 'U'
    and o.name = 'XXXXXX'
      and i.indid > 0
      and i.status & 2 = 2 -- Gives only system indexes. Skip it, and get all
      and index_col(o.name, i.indid, c.colid) <> null
    order by 1, 2