SSIS will use as much memory as it needs. If there is not enough physical DRAM on the box, it will take it from the page file (which generally results in a big slowdown)

The memory for running SSIS does not come out of the SQL Server memory space, it comes out of the memory that is left on the box after SQL Server has taken what it wants. In your case, this would be 2GB. Remember that out of those 2GB, you will also need space for the OS and another other applications running on the box.

In other words, think of SSIS just like any other application - not as part of SQL Server (even though it installs with it).


How to retrieve specific characters within rows of database column?

Your question's not brilliantly clear, but if you want the 2nd, 3rd and 4th characters in the column city, in the table zip, then use SUBSTR:

SELECT SUBSTR(city,2,3)
FROM student.zip;

REGEXP_INSTR returns the location of a match, not a string, so you don't want to use that. REGEXP_MATCH would be the one to use, but SUBSTR is simpler.


MySQL query to get same column data in two rows

SELECT 1 Start, t1.time `Start Time`, 0 Stop, MIN(t2.time) `Stop Time`
FROM src_table t1
JOIN src_table t2 ON t2.time > t1.time AND t2.ign = 0
GROUP BY t1.time
WHERE t1.ign = 1


What is the new JDBC 4.3 Connection Request feature with `beginRequest` & `endRequest` methods?

These new JDBC4.3 APIs beginRequest and endRequest have to do with connection management. It's really a hint provided by the connection pool to the driver that a connection has been checked out of the pool (beginRequest) or checked back into the pool (endRequest). Without these APIs the driver has really no clue about request boundaries. The driver can then use these request demarcations to do dirty laundry like pinging the database, replacing the connection without another one for load balancing, clearing out dirty states, etc.

These APIs were defined as Oracle specific APIs in the Oracle JDBC thin driver before JDBC4.3. Oracle's UCP (Universal Connection Pool) already calls these APIs. Now that they are part of the standard, other connection pools can start using them (and other drivers will implement them).


Correct way to re-use or recover databases from "PostgreSQL 9.3" to "PostgreSQL 9.5"

Nice news, finally, I arrived to a Solution.

Just Install PostgreSQL 9.3 in the same folder, now I open pg_admin and all the databases are back.

Now I should to make a backup before I uninstall PostgreSQL again.

If someone have another idea, I can be happy to use it.


Does the || operator in Postgres result in a blank result if one of the columns is blank?

Depends what "blank" means, NULL or the empty string.

If it means NULL, then yes, that's normal. SELECT 'ab' || NULL ; will return null.
If it means an empty string, then no. SELECT 'ab' || '' ; will return 'ab'.

To avoid the issue, you can convert the nulls to empty string with COALESCE():

SELECT
    COALESCE(str1, '') || COALESCE(str2, '') || ... || COALESCE(strN, '')

or use CONCAT() or CONCAT_WS() functions - which ignore nulls:

SELECT
    CONCAT(str1, str2, ..., strN)

SELECT
    CONCAT_WS('', str1, str2, ..., strN)


Is it possible to do this math in a view?

Something along these lines maybe? You can do running totals with the OVER clause for SUM.

CREATE TABLE Expenses (
    expense_id int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    amount decimal(19,5)
)

INSERT INTO Expenses (amount) VALUES (50), (300), (75), (40)
GO

WITH running_total AS (
    SELECT
        expense_id,
        amount,
        SUM(amount) OVER (ORDER BY expense_id ROWS UNBOUNDED PRECEDING) AS total
    FROM Expenses
)
SELECT
    expense_id,
    amount,
    total,
    CASE WHEN total > 400 THEN total - 400 ELSE 0 END AS out_of_pocket_total
FROM running_total
ORDER BY expense_id


Should I run multiple DBCC Shrinkfile if files are in different disk arrays?

Your task is to reduce the size of mdf file, right?

Shrinking a database by dbcc shrinkdatabase is extremely slow. It is single threaded.

There is a faster way to reduce DB size:

Create new filegroup and allocate space for that filegroup as needed (determine how much space is actually used in the old files)
Move all the data to that filegroup and drop the old one

This solution can be multi threaded so it is going to be much faster


SQL Server 2017: OR and LIKE Interaction

Even though the asker didn't provide actual execution plans for comparison, I found the question interesting and dug up a little. I am pretty confident that the difference is due to implicit conversion introduced through the LIKE keyword. I made small script showing the effects of it:
CREATE TABLE T1 (A INT NOT NULL)

;WITH CTE AS ( SELECT X.A FROM (VALUES (101), (102), (103), (104), (105), (155), (107), (108)) X(A))
INSERT INTO T1
SELECT CTE1.A FROM CTE AS CTE1
CROSS JOIN CTE AS CTE2
CROSS JOIN CTE AS CTE3
CROSS JOIN CTE AS CTE4
CROSS JOIN CTE AS CTE5
CROSS JOIN CTE AS CTE6

INSERT INTO T1
VALUES (505), (555)

SELECT COUNT(1) FROM T1 WHERE A IN ('101', '605', '655')

SELECT COUNT(1) FROM T1 WHERE A = '101' OR A LIKE  '6[05]5'

If you check the execution plans of the last two selects you see it clearly:

The second has wrong estimates due to implicit conversion. In this case I scewed the data on purpose, so it got overestimated.
In this simplistic query it doesn't matter, but the real query probably has overall too low estimates, which leads to tempdb spills during hash joins. Changing IN to LIKE inflates the expected number of rows (like in my example) and increases memory grant. This in turn reduces/prevents tempdb spilling, which speeds up the query.


Issues with uninstalling and re-installing SQL Server 2008 R2

Log in with your Windows account and enable the SA login again.  You'll probably need to configure the SQL Server for mixed mode authentication as well.

If you decide that you do want to reinstall SQL, after uninstalling it you'll need to remove the database files then you can reinstall.  SQL leaves the databases alone when you uninstall so that you don't loose any accounts or data when reinstalling.


If you want to find the date/time difference between the first and the last timestamp of each ticket, there are several functions that can help you:

select
    ticket_id,
    timediff(max(timestamp), min(timestamp))              as diff,
    timestampdiff(hour, min(timestamp), max(timestamp))   as diff_in_hours,
    timestampdiff(minute, min(timestamp), max(timestamp)) as diff_in_minutes,
    timestampdiff(second, min(timestamp), max(timestamp)) as diff_in_seconds
from tickets
group by ticket_id ;

Test in dbfiddle.uk:

select ticket_id,
       timediff(max(timestamp), min(timestamp)) as diff,
       timestampdiff(hour, min(timestamp), max(timestamp)) as diff_in_hours,
       timestampdiff(minute, min(timestamp), max(timestamp)) as diff_in_minutes,
       timestampdiff(second, min(timestamp), max(timestamp)) as diff_in_seconds
from tickets
group by ticket_id ;

 ticket_id | diff      | diff_in_hours | diff_in_minutes | diff_in_seconds
---------: | :-------- | ------------: | --------------: | --------------:
1466089815 | 263:01:00 |           263 |           15781 |          946860
1466089816 | 263:36:00 |           263 |           15816 |          948960


Why is != not working? how to compare with null values?

You can use != rather than the SQL standard operator <>, but I recommend using the latter. That has no influence on your problem though.

Your table must contain some NULL values in sold_dealer_id.

Now NULL = 36 is not true, but NULL <> 36 is also not true, so such lines are excluded from both query results.

You can use sold_dealer_id IS DISTINCT FROM 36 to get the opposite of sold_dealer_id = 36. That operator will treat NULL values as if they were normal values.


What does a CONSTRAINT have to do with my unique index?

The first one is a unique constraint. It can be added to an existing table with:

ALTER TABLE ADD CONSTRAINT ...

Details in the manual here.
It is implemented using a unique index. Per documentation:

  Adding a unique constraint will automatically create a unique btree
  index on the column or group of columns used in the constraint. A
  uniqueness constraint on only some rows can be enforced by creating a partial index.

In addition it allows foreign key references to it.

The second one in a unique index.
It couldn't be a unique constraint because those only allow columns, not expressions. More details:

How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
Discussion on pgsql-general about the difference between constraint and index.


Migrate from SQL Server 2014 Failover Cluster to 2019 Availability Group, rollback plan

2 Massive questions
You haven't said anything about what is connecting to your databases.
For swapping over the databases log shipping will give you a much shorter cutover time than backup & restore.
If you can add a AOAG Listener with the name of the old server you will simplify updating all of the client connection strings (means the old server has to be powered down or renamed).
For rollback one generally has as thorough acceptance testing as possible - if that fails just power down the new server and power up the old one. Once clients are connecting and adding new data you have gone past the point of no return & know you have to fix any problems that occur on the new server.


System Health Extented Event - Security_Ring_Buffer_Error_Recorded

Based on my understanding, this is just a noise that you can ignore.

This event is a part of sqlserver.security_error_ring_buffer_recorded event. It is detailed logging telling you what it is doing with the security errors (e.g. failed logins) to make sure they don't get lost.

The API ImpersonateSecurityContext (provided by SSPI) allows a server to impersonate the client's security context - meaning : the server can authenticate the client and use the client's security context for local access checks directly or by passing the context to another server on the same machine.

I would safely ignore those.

BTW, I am able see the same output on SQL Server 2012 + RTM + (Build11.0.2100.60)


Refresh materalized view incrementally in PostgreSQL

You can always implement your own table serving as "materialized view". That's how we did it before MATERIALIZED VIEW was implemented in Postgres 9.3.

You can create a plain VIEW:

CREATE VIEW graph_avg_view AS
SELECT xaxis, AVG(value) AS avg_val
FROM   graph
GROUP  BY xaxis;

And materialize the result once or whenever you need to start over:

CREATE TABLE graph_avg AS
SELECT * FROM graph_avg_view;

(Or use the SELECT statement directly, without creating a VIEW.)
Then, depending on undisclosed details of your use case, you can DELETE / UPDATE / INSERT changes manually.

A basic DML statement with data-modifying CTEs for your table as is:

Assuming nobody else tries to write to graph_avg concurrently (reading is no problem):

WITH del AS (
   DELETE FROM graph_avg t
   WHERE  NOT EXISTS (SELECT FROM graph_avg_view WHERE xaxis = t.xaxis)
   )
, upd AS (
   UPDATE graph_avg t
   SET    avg_val = v.avg_val
   FROM   graph_avg_view v
   WHERE  t.xaxis = v.xaxis
   AND    t.avg_val <> v.avg_val
-- AND    t.avg_val IS DISTINCT FROM v.avg_val  -- alt if avg_val can be NULL
   )
INSERT INTO graph_avg t  -- no target list, whole row
SELECT v.*
FROM   graph_avg_view v
WHERE  NOT EXISTS (SELECT FROM graph_avg WHERE xaxis = v.xaxis);

Basic recipe

Add a timestamp column with default now() to your base table. Let's call it ts.

If you have updates, add a trigger to set the current timestamp with every update that changes either xaxis or value.

Create a tiny table to remember the timestamp of your latest snapshot. Let's call it mv:

CREATE TABLE mv (
   tbl text PRIMARY KEY
 , ts timestamp NOT NULL DEFAULT '-infinity'
); -- possibly more details

Create this partial, multicolumn index:

CREATE INDEX graph_mv_latest ON graph (xaxis, value)
WHERE  ts >= '-infinity';

Use the timestamp of the last snapshot as predicate in your queries to refresh the snapshot with perfect index usage.
At the end of the transaction, drop the index and recreate it with the transaction timestamp replacing the timestamp in the index predicate (initially  '-infinity'), which you also save to your table. Everything in one transaction.
Note that the partial index is great to cover INSERT and UPDATE operations, but not DELETE. To cover that, you need to consider the entire table. It all depends on exact requirements.


Coping MySQL DB between two servers: which server shoud initiate the operation?

I think the performance difference will be minor.  Do what is more convenient.

If one machine is more powerful, run it on that machine -- Note that you will have 3 processes (mysqldump, mysql, mysqld) on that machine and 1 (mysqld) on the other.


General Database Optimizations - correct data types - why to avoid NULL?

While I do use NULL columns, there is overhead.  The Oracle documentation you retrieved this short list should explain if you read further.

There are case where NULL indicates issues with data types and/or just having the column.

Consider PHONE_NUMBER NUMBER(15):  This will likely have a formatted phone number column, and may be null for numbers like 1-800-CALL-NUM.
Other columns may be NULL because you are unable to gather the data require to fill them.  Consider dropping the columns if is unlikely you will be gather the data.
NULL columns may belong to a sub-type.  It may be appropriate to have a related table containing type related data. This would have an optional 1 to 1 (1 to (0,1)) relationship.
NULL columns are problematic for indexes.
NULL columns may be state related, such as DATE_SHIPPED on a order.  It would be appropriate for this to be NULL before the order is shipped.  However, it would be inappropriate for a has_shipped indicator column to be NULL.

It is always possible to normalize NULL columns out of the database.  It may not always be appropriate to do so.

I do try to put NULL columns last. I've posted on column order previously.


Security considerations of allowing logical replication subscribers on PostgreSQL

Note that the GRANT SELECT is only needed for the subscriber in order to do a bulk copy of the initial data in the table.  If they do not have that GRANT, they can still start a subscription, and they would be able to see any newly inserted rows or the new version of any updated rows.  The fact that the GRANT SELECT is needed for full functionality seems like wart in the design, as declining to GRANT the SELECT access does not deny them access to all the data, merely the subset that already existed and was never updated.  This is confusing in a way that could lead to security mistakes.

Also, there is no fine-grain control to which users can subscribe to which publications in a database (at least, not that I can find).  Any user WITH REPLICATION who can connect to a given database can initiate subscriptions to all publications in that database.  Combined with the previously discussed limitation in which denying SELECT on a table doesn't deny all visibility to the contents of the table, this is really limiting. Although maybe not in your use case, if you only have one outside party, or if all outside parties need access to the same thing.

Cascading logical replication is possible. So you can gain some fine-grained control by creating one "sterile" database for each publication you want separate control over.  In that sterile database, you create a subscription to the publication in the main database (connecting to it with some internal role if course, not the external user's role), and create a publication of those same tables.  Then you grant the external user's role CONNECT (or using pg_hba.conf settings) to only the sterile database and not the main database.  You could do this either with a separate database server, or a separate database within the same database server.  It does annoyingly increase the storage needs, and the CPU overhead of republishing the data internally.

In order to let the outside party connect in order to do the replication, you will have to poke a hole in your firewall to let them in.  Doing that could open you up to various miscreants trying to hack into your database. So make sure you have strong passwords (or some better form of authentication, like client certs) on all roles, not just "some_client".  Or poke the firewall hole only for the client's domain or IP address range.  Or make sure all your roles in pg_hba other than "some_client" are allowed only from internal IP addresses.  Or all of the above.

Finally, you open yourself up to accidental (or even intentional) denial of service attacks from this client.  If they fail to keep their database up and running and following along with your publication, it would cause your database to start retain pg_wal files, perhaps to the point where it fills up your storage system and causes downtime. So you should have some way of monitoring pg_wal size and be prepared to forcibly drop the replication slot if it is not keeping up.


SUM of column by GROUP BY which is WHithin a GROUP BY

SELECT `drcr`,SUM(`amount`)
`amount`,`name`,`type`,`narration`,`main_reference`,`date`
FROM (
    SELECT gl.`drcr`,gl.`amount`,gl.`name`,gl.`type`,gl.`narration`,gl.`main_reference`,gl
  .`date` "
                              . "FROM `general_ledger` AS gl "
                              . "INNER JOIN ( "
                              . " SELECT SUM(amount) AS total FROM `general_ledger` GROUP BY `type` ) AS tot "
                              . "WHERE gl.`date` BETWEEN '$start_date' AND '$end_date' "
                              . "GROUP BY gl.`main_reference`,gl.`date`,gl.`name`,gl.`type`,gl.`amount`,gl.`drcr`,gl.`narration`
) Interimresults
GROUP BY `drcr`, `name`,`type`,`narration`,`main_reference`,`date`


MySQL DBs taking up way too much disk space

I solved this issue by setting innodb_file_per_table = 0 in my MySQL config (/etc/mysql/mysql.conf.d/mysqld.cnf) and re-importing the mysqldump after dropping all databases. The ibdata1 file that contains all DB data is just about 2.4 GB.

root@ip-x-x-x-x:/var/lib/mysql# du -sm * | sort -nr | head -15
2445    ibdata1
48  ib_logfile1
48  ib_logfile0
12  ibtmp1
11  dbvwgcayak57wx
11  dbmuj7j8scnzt6
11  dbmmqtf5sss9pk
11  dbf9ynyscjzw83
11  dbdr5hk7kvsh73
11  db3mj8b7b5ezuw
11  db22z4jcfbf9yn
4   db7b5ezuwb8ca4
1   phpmyadmin
1   mysql
1   ib_buffer_pool

Seems like having a *.ibd file for every single table can actually be disadvantageous if your DB contains a lot of small tables.


Should a log table get an id field or primary key?

Should I remove the id field?

I would recommend keeping it.

You may not need the field now, but in the future, it can really help you out -- what if you need to store details of the files for each log entry?

I don't know how large this table will get and how quickly, but adding a column to a large table is typically an expensive operation. If the table is relatively small, then it's not a big deal to keep in terms of storage space. IMO, keep the column and save a potential headache later.

  Should I have a primary key on either messageId or exportedDateTime or both?

It doesn't sound like messageId alone would be unique in this table (though I could be wrong), and creating uniqueness on a date/time column alone can potentially create duplicates (and hence errors). The only option left is a 2-column key, which is not particularly appealing given the scenario I set out above.

Essentially, my point of this answer is that keeping the column is not a big deal (I'm assuming), but needing it later may be a big deal and/or require extra work to put it back.


Why is the transaction log not truly circular?

In the face of log file growth events and / or large, long-running transactions, the order of the output of DBCC LOGINFO can look at a little wacky - temporarily, at least.

The important thing to remember is that the FSeqNo is the logical order of the VLFs.

Paul Randal talks about this in his blog post Inside the Storage Engine: More on the circular nature of the log (emphasis mine):

  Once the active transaction I created commits or rolls back, VLFs 3, 4, 1, and 2 can clear and then the ‘normal’ sequencing of VLFs in the log will resume.

  Every VLF has a small header which contains the sequence number of the VLF within the transaction log, so the log can kind of do contortions to work around active VLFs in the middle of the log.

These irregularities should self-correct over time as VLFs clear normally.


ORA-04020: deadlock detected while trying to lock object - Oracle Package

Look at this webpage https://docs.oracle.com/cloud/latest/db112/LNPLS/nameresolution.htm#LNPLS017, especially the INNER CAPTURE section.
Does the package exist on both sides of the link or only one?
What does the ORA-60 deadlock trace file show?
What does DBMS_UTILITY.NAME_RESOLVE show for "SCHEMA2.PKG_NAMEHERE@SCHEMA2.LINKNAME.INT@SCHEMA1"?
Do the DBLINKS loopback?


Attaching database to an existing connection

all rdms support
Db.table

SQL SERVER
[db].[dbo].[Table]

So you can make a query
SELECT a.col1,b.cl2 FROM db1.table1 a INNER JOIN db1.table1 b ON a.id= b.id

That would get you columns from 2 databases, on one connection.
2 connections make the thing more complicted
MYsql can establish federated tables http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html so that you have scenario 1 again. (that is possible for almost every rdms)
For Postgreslq exists something similar see https://www.postgresql.org/docs/9.3/postgres-fdw.html
Else you can only get the data from connection/Server  1 and compare it to the data received by server 2, by means of a variables
(the syntax differs somewaht from rdms to rdms)


Running an SSIS package without the SSIS service installed

The SSIS service doesn't actually do much and you can run packages without it or with it disabled. It's used by SSMS amd SQL Agent for example.

See the KB article "Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service"

What you need are the base SQL Server binaries which are installed with the DB engine. So you need a SQL Server Instance of some flavour to have dtexec running. Whether it's the DB Engine or the SSIS service installed but disabled.

Running packages in BIDS is a special case. Run the package on the same PC as BIDS via dtexec and it will fail unless you have installed server components (= a SQL Server Instance)


Do I use INSERT w/ ON DUPLICATE KEY or INSERT/UPDATE separately?

Your question mixes two things.

On the one hand, you have two parts to the UI, wherein the user decides, perhaps incorrectly, whether the action is just an insert or just a delete.

On the the other hand, you could have a single button, thereby simplifying the UI, and the button does IODKU under the covers.

But...  Maybe you want the user to be reprimanded when he asks to INSERT something that is already in the table?  (Etc)

The real goal should be to make the UI simple, concise, obvious, etc, to the user.  The amount of work to achieve the requested task is so insignificant that you should not be considered in the equation.  It may take the user 10 seconds to read, then press, the appropriate button, but but only 10 milliseconds to perform the action.  That's 1000:1 !

Anyway, back to your question...  IODKU has to check all UNIQUE (including PRIMARY) keys mentioned, then decide to INSERT or what row(s) to UPDATE.  (Yes, multiple mentioned unique keys can lead to multiple rows updated.  By why mention more than one unique key?)

An INSERT or a UPDATE is inherently faster than IODKU.  But it is probably sub-millisecond.  On the other hand, it will take seconds to report an error to the user and wait for him to deal with it -- if he presses INSERT when he should press UPDATE.

So, I go back to the important principle -- design the UI right.


MySql Event to Delete Data from 2 Tables Older Than 2 Months

When you have multiple statements you need not only a BEGIN and END
But also a DELIMITER
One error is also dates have to be in quotes as you can see in the code
DELIMITER //
CREATE EVENT
IF NOT EXISTS daily_delete
ON SCHEDULE EVERY 1 DAY STARTS '020-09-13 04:00:00'
DO
BEGIN
    DELETE
    FROM bariq_DB.invoice
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 2;
    delete from bariq_DB.order_details
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3;
END;//
DELIMITER ;


Postgres query not using indexes with conditions from multiple tables

The order_number = 'bcfong28' OR cards.name ~* 'bcfong28' is the cause of the table scan. The planner has no available shortcut that an index would provide, so it has no choice to check all rows to see whether one contraint or the other holds.

Indexing invoice_tcgplayer_details.order_number will cut out out the sequential scan on invoice_tcgplayer_details, and this may be enough for the planner to bring the trgm index to bear on the other and combine the two through a bitmap-or hash join.

That said, the UNION approach will be pretty good, since one of the selects will be nothing more than

SELECT inventory_uuid
FROM invoice_tcgplayer_details
WHERE order_number = 'bcfong28'


Why does nested loops join only support left joins?

What I don't like in the linked article is the statement that "nested loop join algorithm does not support the logical join operator of right join".

While there is a limitation, the wording at this point is a bit confusing. I hope the following explains things better:

The nested lop join algorithm involves two tables (whether base tables or result sets of previous operations is irrelevant) which are named outer and inner table and they are treated in a different way by the algorithm (the "outer" table is traversed at the outer loop and the "inner" table at the inner loops).

So, lets say we have have a join:

A (some_type) JOIN B

The algorithm can be executed as either:

outer-loop-A  nested-loop  inner-loop-B

or:

outer-loop-B  nested-loop  inner-loop-A

Now, if (some_type) is INNER or CROSS join, then there is no limitation, the planner can choose between either of the two ways (with different performance characteristics, depending on the size of the sets, distribution of values of the joined columns, indexes, etc. Usually the smallest table will be chosen to be the "outer" table in the algorithm).

But when some_type is LEFT join, it can only use:

outer-loop-A  nested-loop  inner-loop-B

and not

outer-loop-B  nested-loop  inner-loop-A

And since a RIGHT can always be rewritten as a LEFT join, it has the same limitation, in reverse. For A RIGHT JOIN B (which can be rewritten a B LEFT JOIN A) it can only use:

outer-loop-B  nested-loop  inner-loop-A

and not the other way around*.

The same limitation applies for left-semijoin, left-anti-semijoin, right-semijoin and right-anti-semijoin.

The FULL join on the other hand cannot be directly handled with a nested loop join algorithm. The article explains very well (it's near the end) how a full join can be rewritten (and is by the optimizer) to a union of a left join and a left anti-semijoin which then might be planned as two nested loops (and a union).

* As Dudu Markovitz explains in his answer, the reverse way would be able to be used but only if we modified the nested-loop join algorithm to have an extra structure and an extra step in the end.


Oracle 11g R2 on CentOS 6.3, and Net Configuration Assistant

Community Wiki answer containing the solution originally added to the question by the author.

Finally I found the solution: I didn't change permission to be owned by the oracle user and oinstall group for the database dir in which the Oracle software is. I think there are some scripts or programs related to listener and the oracle user could not run it.


SQL 2012 Availability Group issue - The core cluster resources do not move when we initiate AG fail-over

There is no reason to move the cluster core resources. They are NOT part of the availability group and function entirely on their own. There is no need to even have these on the same server as any of your AG resources.

The core cluster group provides the administrative point for the cluster which is completely independent from any other resources on the cluster. Leave it migrate on its' own.


What settings control the maximum number of connections per user in Oracle 12c?

You might want to check the SESSIONS parameter, which controls the maximum number of sessions for the system:

SELECT name, value
FROM v$parameter
WHERE name = 'sessions';

https://docs.oracle.com/database/121/REFRN/GUID-52804B5A-164F-44F3-8980-F2593B58D807.htm#REFRN10197

You can also verify that there isn't a limit configured via user profile. Query the DBA_PROFILES view to check if a value is assigned for the SESSIONS_PER_USER parameter associated with your user:

select profile, resource_name, limit
from dba_profiles
where resource_name = 'SESSIONS_PER_USER';

Info regarding profiles:
https://docs.oracle.com/database/121/SQLRF/statements_6012.htm#SQLRF01310


In the context of another principal what permissions hinge on the identity of the database owner?

I had meant to write a blog post where I explain this in detail (more than is appropriate here), with example scripts, etc, but just haven't had the time. But, rather than have this remain unanswered until I have time to write that post (test scripts are done but need to be cleaned up), I will post the basic info here and come back later to adjust once that post is published.

There are two related scenarios that I know of that are dependent on the permissions of the database owner:

Trying to access instance-level resources, or another database, while using either impersonation or an App Role
Trying to obtain either EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission (instance-level permissions) while using unsigned SQLCLR assemblies (and using either SQL Server 2005 - 2016 or SQL Server >= 2017 and not using the horrible "trusted assemblies" hack)

Both scenarios are related by being times when enabling TRUSTWORTHY is required. And, both are also scenarios that can avoid the evil TRUSTWORTHY ON by implementing the far better approach of Module Signing.

Scenario #1

is very similar to, but still not the same as, actually accessing instance-level resources or another database under impersonation. There is a lot of overlap as they are two parts of the same functionality. I would guess that most of the time impersonation is being used to become the dbo user via the EXECUTE AS clause of a CREATE {module} statement, specifying either OWNER or N'dbo'. In that case it's very difficult to separate the indirect requirement, but impersonating a non-dbo user makes it clear.

When impersonating a user (we're assuming a non-dbo user), in order to access instance-level resources or another database, not only does the current database need to be marked as TRUSTWORTHY, but the login associated with the dbo user of the current database (i.e. same SID between the login and dbo) needs to have the AUTHENTICATE SERVER permission. This is not well known, which is likely why so many people simply resort to having sa own the database: it has all permissions. Many people, I'm sure, who already have their DBs owned by sa might never even know that this is a potential problem because the condition was already satisfied when they enabled TRUSTWORTHY in order to get the impersonation working.

EXAMPLE:

Setup:

There is a database, [TestDB], that has TRUSTWORTHY set to ON.
Database [TestDB] is owned by login O (this means that the dbo user in [TestDB] has the same SID as login O). O is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
The application connects as login A that has an associated user A in database [TestDB]. Login A is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
There is also a login, P, that has an associated user P in database [TestDB]. Login P is not a member of sysadmin nor has CONTROL SERVER, but does have the VIEW SERVER STATE permission.
There is a stored procedure in [TestDB] named dbo.ViewAllSessionInfo that selects from sys.dm_exec_sessions and sys.dm_exec_requests, and is not filtered to only display the row for the current login or SPID / session_id.

Behavior:

Login A (the app login) can execute dbo.ViewAllSessionInfo, but only gets 1 row returned, the row for the current session.
The stored procedure is altered to contain the EXECUTE AS N'P' clause (P has the required permission).
Login A executes dbo.ViewAllSessionInfo, but still only gets 1 row returned, the row for the current session (even though the stored procedure is now executing as P, and P does have VIEW SERVER STATE permission, and the database has TRUSTWORTHY enabled).
Login O is then granted the AUTHENTICATE SERVER permission.
Now, when login A executes dbo.ViewAllSessionInfo, rows for all sessions are returned (even though the only new permission added was granted to O, and not to either A or even to P).

Scenario #2

occurs when creating or altering an assembly, or even trying to use any code (stored procedure, trigger, functions, or type) from within an assembly that has been marked as either EXTERNAL_ACCESS or UNSAFE. Additionally, starting in SQL Server 2017, if you keep the "security" setting of "CLR strict security" enabled (the default), then even assemblies marked as SAFE need to meet this requirement (again, we are assuming that neither module signing nor "trusted assemblies" is being used). In this case, assuming that a non-dbo account is attempting one of those actions, the login associated with the dbo user of the current database (i.e. same SID between the login and dbo) needs to have either the EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission. This is more widely known than Scenario #1 (due to an error message that provides this information), yet many people simply resort to having sa own the database (again, everything "magically" works). Many people, I'm sure, who already have their DBs owned by sa might never even know that this is a potential problem because the condition was already satisfied when they enabled TRUSTWORTHY in order to get the operation working.

EXAMPLE:

Setup:

There is a database, [TestDB], that has TRUSTWORTHY set to OFF (different than test case for Scenario #1).
Database [TestDB] is owned by login O (this means that the dbo user in [TestDB] has the same SID as login O). O is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
The application connects as login A that has an associated user A in database [TestDB]. Login A is not a member of sysadmin nor has CONTROL SERVER permission, or any high-level permission.
There is also a SQLCLR assembly, C, in database [TestDB]. Assembly C has a PERMISSION_SET of UNSAFE.
There is a stored procedure in [TestDB] named dbo.DoUnsafeStuff that is really a .NET method in assembly C.

Behavior:

Login A (the app login) can execute dbo.DoUnsafeStuff, but gets a security error.
Database [TestDB] is altered to set TRUSTWORTHY to ON.
Login A can execute dbo.DoUnsafeStuff, but still gets a security error.
Login O is then granted the UNSAFE ASSEMBLY permission.
Now, when login A executes dbo.DoUnsafeStuff, the stored procedure operates as expected, without the security error (even though the only new permission added was granted to O, and not to A).

In both scenarios,

the required permission is not required for the principal performing the action. It's more of a gate-keeper to determine if the requested action is allowed to be performed by any principal within that database.


First of all be aware though that when you are querying on tables that large you have to expect it to take some time.

That being said assuming you are using SQL 2008 or later you could try a filtered index on CLLab, however that makes several assumptions.

The number of rows where the Dept = "Lab" and Observe_Id = "Reviewed" is a relatively small percentage of the total number of rows.
This is an important enough query to warrant the extra write time to CLLAB for an additional index just for it.

If the answers to both of those are Yes (and of course SQL 2008 or higher) then try this.

CREATE NONCLUSTERED INDEX "IX_CLLAB_REVIEWED_LABS"
ON CLLAB (SSNO, COMPANY, DATEBACK)
WHERE DEPT = "LAB"
  AND OBSERVE_ID = "REVIEWED";

Really I would rather get rid of the subquery all together if possible but that would require more knowledge of the data and what you are trying to get out of the query.

Last but not least if you comment out the small subquery in the field list does it run significantly faster?  If so that part of the query could be incorporated into the main query as a LEFT OUTER JOIN and might save you some time.

It would look like this.

-- In the field list
FACILITY = CASE WHEN B.FACILITY = '' THEN D.FACILITY ELSE B.FACILITY END

-- The FROM & WHERE Clauses
FROM CLLAB A
JOIN CLORDER B
    ON A.COMPANY = B.COMPANY
    AND A.ORDER_ID = B.ID
JOIN CLMASTER C
    ON B.ACCOUNT = C.ACCOUNT
LEFT OUTER JOIN CLDoctor D
    ON  B.PROVCODE = D.DOCTOR
    AND B.FACILITY = ''          -- optionally. May be more efficient
                                 -- with different indexing
WHERE B.DEPT = 'LAB'
  AND A.DATEBACK NOT IN (.....)


In that case, go to McAfee audit plugin as it's compatible with MySQL from version 5.1 to latest GA. This plugin outputs in json nicely for you and you can filter nicely on the events you do and do not want.


I just ran into this same issue. By default the first statement in a job step is to SET QUOTED_IDENTIFIER OFF - you can see this in a Profiler trace. The solution is to add a line at the beginning of your code to SET QUOTED_IDENTIFIER ON.

As to your script not containing quotes - I believe when creating or rebuilding certain types of indexes that setting is required is to be on.

Here's a couple links:

https://stackoverflow.com/questions/15218893/first-statement-issued-by-sql-server-agent-sets-quoted-identifier-off


They are your data. You probably don't want to remove them ;)

"nonremovable row versions" includes the current, active row versions containing the data you care about. It also includes row versions made obsolete by a delete or update, but still visible to the snapshot of one or more running transactions.

You should only be concerned if there are lots of nonremovable dead row versions. That is not the case here. To learn more, read the manual on routine vacuuming, the chapter on concurrency and MVCC, and read about table bloat.


a) Use an OS that is certified for installing and running Oracle Database (Ubuntu is not amongst them). Native or virtual machine, your choice.

or

b) here is an unofficial guide for installing Oracle 11.2 on Ubuntu Linux
https://forums.oracle.com/thread/1117155

I have never tried myself, but several people succeeded installing Oracle 11.2 on Ubuntu with the help of this.


insert row if table is empty, else update row (mysql)

got it working but there might be better ways

CREATE PROCEDURE simple6(IN name VARCHAR(20))
BEGIN

SET @sql_text = concat('SELECT endtime INTO @endtime1 FROM ',name,'big order by endtime DESC LIMIT 1');
PREPARE stmt FROM @sql_text;
EXECUTE stmt;

if @endtime1 IS NULL
then

SET @sql_text2=concat('INSERT INTO ',name,'big (starttime,endtime) SELECT starttime,endtime FROM ',name,' ORDER BY endtime DESC LIMIT 1');
PREPARE stmt2 FROM @sql_text2;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;

else
SET @sql_text3= concat('set @cat:= (IF(TIMESTAMPDIFF(SECOND,(SELECT endtime from ',name,'big order by endtime DESC limit 1),(SELECT starttime from ',name,' order by starttime DESC limit 1)) < 5,1,0))');
PREPARE stmt3 FROM @sql_text3;
EXECUTE stmt3;
DEALLOCATE PREPARE stmt3;

if @cat !=0
THEN
SET @sql_text4=concat('UPDATE ',name,'big SET endtime = (SELECT endtime FROM ',name,' ORDER BY endtime DESC LIMIT 1) ORDER BY ID DESC LIMIT 1');
PREPARE stmt4 FROM @sql_text4;
EXECUTE stmt4;
DEALLOCATE PREPARE stmt4;

else
SET @sql_text5=concat('INSERT INTO ',name,'big (starttime,endtime) SELECT starttime,endtime FROM ',name,' ORDER BY endtime DESC LIMIT 1');
PREPARE stmt5 FROM @sql_text5;
EXECUTE stmt5;
DEALLOCATE PREPARE stmt5;

end if;
end if;
DEALLOCATE PREPARE stmt;

END\\


No, you cannot load a 32-bit ODBC driver into the 64-bit SQL Server address space (reference). From your flurry of recent questions, it seems you are having continuing problems accessing a Pervasive database using their 64-bit driver. Have you considered contacting Pervasive support for assistance or an updated driver? You could also ask a question on their forum.


H2 SELECT NULLABLE or other equivalent to check a column has NOT NULL constraint or not

If you have a definition like this:

create table foo
(
   id integer not null
)

The following query will show "NO" in the is_nullable column:

select table_name,
       column_name,
       is_nullable
from information_schema.columns

If the column has a check constraint, like this:

create table foo
(
  id integer,
  constraint id_not_null check (id is not null)
);

then this will be shown as "nullable" with the first query. You would need to check information_schema.constraints and analyze the content of the CHECK_EXPRESSION column to detect this.


Memory limitations on db2 express-c version 10.1 seems stuck at 2 gig, why?

Found the answer in the doc (I should have RTM). You check the setting using:

db2 get dbm cfg

And looking for INSTANCE_MEMORY, then update with:

db2 update dbm cfg using INSTANCE_MEMORY <memoryDividedBy4KB>


Why does importing a MySQL dump cause so many I/O?

Restoring a dump file, fundamentally, involves inserting all of the rows and building all of the indexes for all of the data.  In that light, it's not particularly relevant how the dump file is generated, whether you use one or several files, or which of the available options you select (though some of them, like extended-insert could possibly make it worse of not selected).

When you're restoring a file, you're also:

writing the data to the transaction log
writing the data to the double-write buffer
writing the data to the tablespace files
writing the indexes to the double-write buffer
writing the indexes to the tablepace files
writing the data to the binary log if that's enabled ... and if you have the default binlog_format setting of "statement," then you're literally writing almost every byte in the dump file plus overhead... but if your binlog_format is set to "row," you're writing a more compact version to the binlogs... and if your binlog_format is set to "mixed," then which format is actually used in the binlog depends on your default transaction isolation level.

Setting innodb_flush_log_at_trx_commit to the slightly less safe value of 2 or significantly less safe value of 0 from the very expensive but ACID-compliant default value of 1 will speed up your insert for sure, though it isn't likely to reduce actual I/O because that value doesn't change what's written to the transaction log, it merely changes how often InnoDB insists on confirmation from the operating system that the contents of the log have been persisted to disk.  I use "safe" in the sense of safety against the loss of recent transactions if a crash occurs during the time the value is set to 2 or 0; 1 protects against data loss if either MySQL or the system crashes; 2 protects against data loss if MySQL crashes but not the system, and 0 protects against neither.  Once you set it back, it doesn't have any after-effects.

Quick side note, some of the comment-looking things shown are not comments.  The /*!mnnrr format is a MySQL backwards-compatibility extension that tells the server "If you're MySQL version m.nn.rr or higher, execute this statement, otherwise disregard."

/*!40000 ALTER TABLE `table_name` DISABLE KEYS */;
/*!40000 ALTER TABLE `table_name` ENABLE KEYS */;

Each table is surrounded by these in a dump file.  These were more useful with MyISAM than with InnoDB, because DISABLE KEYS directed the storage engine not to update any non-unique indexes until ENABLE KEYS was issued, allowing all of the row data to be written, and then indexed in a batch.  With InnoDB, the indexes are built as the inserts are processed... so there's a lot of I/O potential there as the index trees are being built and shuffled around.

The size of the InnoDB buffer pool is going to play a role in the amount of disk I/O -- possibly a significant one if it's relatively small -- because whatever can't stick around in memory will have to be promptly flushed to disk, only to be read back in when it's needed again, and this will be particularly true with indexes, less so with the actual rows, because in InnoDB, rows are physically stored in primary key order, and mysqldump writes them to the file in primary key order... so they're inserted in the order in which they'll be stored... but the secondary indexes will have to be shuttled back and forth to disk as index pages are updated as the insert operations on a given table progresses.

So there's a baseline of "extra" activity that every insert that happens, because of all the different logging, safety, and ACID mechanisms... and indexes seem like a likely wildcard candidate for potentially creating a substantial amount of additional I/O.


How to use timestamp column for filtering records in where clause in mysql

AND has higher precedence than OR. Your query, without parentheses, is equivalent to this:

SELECT event_time, user_host, argument
FROM mysql.general_log
WHERE (event_time > '2016-08-04 10:50:27' AND user_host LIKE 'vareity_1_1%')
   OR user_host LIKE 'db_tvm%' ;

Try this instead:

SELECT event_time, user_host, argument
FROM mysql.general_log
WHERE event_time > '2016-08-04 10:50:27'
 AND (user_host LIKE 'vareity_1_1%' OR user_host LIKE 'db_tvm%') ;


Struggling with MySQL character set, collation

Don't use latin1, use utf8.  You have "Mojibake" because the bytes in the client are utf8-encoded, yet you said they were latin1.

More on Mojibake and what to do about it:  https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored


How to assign GUID to batches of data based on row counts and tunable batch size

you have to play with row_numbers
 declare @part int = 3

 ;with baseRowNums as
  (
    SELECT ID, VALUE1, VALUE2,
       Row_Number() Over (ORDER BY ID) AS rn
    FROM BASE_TABLE
  )
 ,batchNums as
 (
 SELECT *,
    Sum(CASE WHEN rn % @part = 1 THEN 1 ELSE 0 END)
    Over (ORDER BY ID, rn
          ROWS Unbounded Preceding) AS part
 FROM baseRowNums
 )
 ,GUIDs as
 (select part, MAX(newid()) as GUID
  from batchNums
  group by part
 )
 select ID, VALUE1, VALUE2, GUIDs.GUID
 from batchNums
 join GUIDs on batchNums.part = GUIDs.part

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7c26d9bab095ce9d8ca1abf1f4c1a898


SQL Server Merge Replication Change Partition Definition

I found a solution!

Go to the merge agent that handles the subscriber that you would like to change.
Right click -> Properties
Go to the Steps page and edit the Run agent step
Add or Alter the HostName parameter like so:  -HostName [<Your Value Here>]
Stop & Start the agent job
Reinitialize the subscription.

That should do it!


DBCC CHECKDB fails after restore to SQL Server 2017

You can find out what object(s) those pages belong to by running:

SELECT s.name, o.name, a.index_id
FROM sys.schemas AS s
INNER JOIN sys.objects AS o
ON s.[schema_id] = o.[schema_id]
CROSS APPLY sys.dm_db_database_page_allocations
  (DB_ID(N'DBNAME'),o.[object_id],NULL,NULL,N'LIMITED') AS a
WHERE allocated_page_page_id BETWEEN 1737 AND 1743;

You can try rebuilding that index (or table if index_id is 0 or null) to see if that fixes the allocation problem. If it remains, you could try dropping the index and re-creating it or, again, if it is a table or heap, selecting everything into a new table, creating the new indexes there, dropping the old table, and renaming the new one.

If that doesn't help, you can inspect the contents of those pages, and determine if it is acceptable to lose the data as a part of your upgrade (or, if not, to not upgrade).

DBCC TRACEON(3604,-1);
DBCC PAGE(N'DBNAME', 1, 1737, 2);
DBCC PAGE(N'DBNAME', 1, 1738, 2);
DBCC PAGE(N'DBNAME', 1, 1739, 2);
...

If it is an acceptable loss then you can run the repair with allow data loss as suggested (it might be smart to keep a copy of the backup before repairing in case you ever need to pull the data from those pages in some way).


MySQL: How index speed up query execution

Here you have a video on the basic inner structure and inner workings of indexes. I recommend you to watch it all.

Basically, indexes are ordered structures on disk (although they can be cached, and they normally will for better performance) that will allow certain operations to be done faster. In particular, in MySQL, B-tree/B+tree (the most common ones) will allow you:

Perform filtering faster, with conditions like a=3, where a is an indexed column. This is possible because you can locate values that are 3 with less read operations
Obtain ordered results (ORDER by a): as values in an index are ordered, in certain cases, there will be no need to reorder them with a sorting algorithm, as we are already reading them in the right order
Reading values without accessing the whole row (covering index): SELECT a ... WHERE a = 3; as the index is smaller, it is less amount of bytes read, reduced operations and usually cached, improving read performance
Speeding up certain operations, like max(a) or min(a): the optimizer just needs to read the first or the last value on the index structure

In the case of B-trees, (look at this slide), if it is looking for the value 1, it only needs to read the root node. As 1 is before 3, it knows it is on the left node. In one step it discarded 50% of the reads. Imagine the advantage with millions of nodes, it is exponentially (or actually, logarithmically) better. If all rows have to be read, then reading may not be faster, in fact, it may be even slower. That is why when the selectivity is low MySQL refuses to use an index and performs a FULL TABLE SCAN. However, even in some cases, performing a full index scan may be an advantage (if less data has to be read overall), but it won't be as effective as if we perform a filtering with high selectivity.

In the case of hash indexes, the advantage comes from the use of a hash table. This can be even faster in some cases, but it cannot be used in all cases (for example, for > or < comparisons). The major engines on MySQL (InnoDB, MyISAM) do not support it, but they use internally in some structures hash maps.

MySQL will not select the index type automatically, it is selected on creation time (ADD INDEX my_index (col_a, col_b) USING BTREE), but defaults to BTREE.

MySQL and other DBRMSs support other type of indexes, too, like R-Trees, Fulltext, Spatial, etc.


Unable to Watch Live Data on Extended Events Session

I found the problem.  My login had a different default database than the master database.  When I changed my default database to master, the error went away, and I was able to Watch Live Data on the extended event sessions.

To change the default database, in SSMS, I expanded the server, Security, Logins.  I right-clicked on my user credentials. On the Login Properties page for my user, I changed "Default database" to master.  The error went away when I closed and reopened the connection in the SSMS Object Explorer.

EDIT 2018-12-11

On the feedback link for this issue, Karl Fasick posted a likely solution:

  Object Explorer isn't connected to the master database...
  To fix - disconnect Object Explorer - Reconnect, but go to Connection
  Properties and explicitly choose master.


Cluster communication - SQL Server Failover Cluster

The issue is not the version of SQL but the version of Windows you're working with. Win 2008 changed things up quite a bit, private heartbeat network is no longer required.

Pre Win 2008 cluster reqs.


SQL Server WHERE clause on CLR method (spatial) peformance

The first thing to look at is your indexing strategy. Bad execution plans are often caused by insufficient indexes or stale statistics. Your statistics warning might hint at that.

If that does not resolve your problem there are a few hacks that you can try:

A top operation requires SQL Server to separate query sections in the execution plan.

If you know you are always dealing with less then 2 billion rows you could write your query like this:

SELECT * FROM(
  SELECT TOP(2000000000) *
    FROM <complex join>
    WHERE ro.reportguid = '64c0a4af-ee4d-4e83-a194-2a14e8a6ab0e'
  )X
WHERE l.geom.STGeometryType() = 'Point'

An alternative is to write a scalar valued function that takes in the l.geom column and a few other columns from the other tables and returns the STGeometryType() value while ignoring all the other values. Because SQL Server does not consider the function logic at optimization time, it is forced to execute the function after the join. That does not guarantee that the other filter is executed first but often it works out that way.

The third option is to play around with join hints and join order. They often lead to a change in where filters are applied.

All three options I would consider a last resort because they make the code ugly and you run the risk that someone removes that ugliness later trying to make the code better.


Query to update the JSON that does not contain a specific key

I had fun with this one :) This leaves personalUse as-is when present, otherwise sets it to true, and should preserve all other key/values in in the toplevel object besides myData and all keys other than personalUse in the innermost objects. What it is not - and I'm pretty sure cannot be - is remotely efficient, but that can't really be helped without using jsonb, adding indexes, and otherwise doing a lot of high-touch things. This should also work back to at least PG9.5, although I only tested as far back as 10.

testdb=# CREATE TABLE details (id bigserial primary key, detail json not null);

INSERT INTO details (detail)
VALUES
  ('{"myCompany":[{"email":"company@company.com", "inProfit":true } ],
     "myData":[{"email":"example@example.com", "personalUse":true },
               {"email":"test@test.com", "personalUse":false },
               {"email":"test@example.com"} ] }');
CREATE TABLE
INSERT 0 1
testdb=# with
explode_fields as (
select id, key as details_key, value as details_value from details, json_each(detail)
),
only_mydata as (
select * from explode_fields where details_key='myData'
),
explode_mydata as (
select only_mydata.*, row_number() over (partition by id) as item_idx, item as mydata_item
FROM only_mydata, json_array_elements(details_value) as item
),
explode_mydata_fields as (
select explode_mydata.*, key as mydata_item_key, value as mydata_item_value
from explode_mydata, json_each(mydata_item)
),
only_mydata_personaluses as (
select * from explode_mydata_fields where mydata_item_key = 'personalUse'
),
/* Need a true for every inner item to use as the 2nd argument to coalesce() */
personaluse_all_trues as (
select id, details_key, details_value, item_idx, mydata_item, 'personalUse' as mydata_item_key, 'true'::json as mydata_item_value
from explode_mydata
),
merged_personaluses as (
select t0.id, t0.details_key, t0.details_value, t0.item_idx, t0.mydata_item, 'personalUse' as mydata_item_key, coalesce(t1.mydata_item_value, t0.mydata_item_value) mydata_item_value
FROM personaluse_all_trues t0
left join only_mydata_personaluses t1
on t0.id=t1.id and t0.details_key=t1.details_key and t0.details_value::text=t1.details_value::text and t0.item_idx::text=t1.item_idx::text and t0.mydata_item::text=t1.mydata_item::text
),
include_other_item_fields as (
select id, details_key, details_value, item_idx, mydata_item, mydata_item_key, mydata_item_value from merged_personaluses
union all
select id, details_key, details_value, item_idx, mydata_item, mydata_item_key, mydata_item_value from explode_mydata_fields where mydata_item_key <> 'personalUse'
),
agg_mydata_fields as (
select id, details_key, details_value::text, item_idx, json_object_agg(mydata_item_key, mydata_item_value) as item
from include_other_item_fields
group by 1, 2, 3, 4
),
agg_mydatas as (
select id, details_key, json_agg(item) as new_details
from agg_mydata_fields group by 1, 2
),
include_other_fields as (
select id, details_key, new_details as details_value
from agg_mydatas
union all
select * from explode_fields where details_key <> 'myData'
),
agg_records as (
select id, json_object_agg(details_key, details_value) as detail from include_other_fields
group by id
)
update details set detail=agg_records.detail
from agg_records
where details.id=agg_records.id;
UPDATE 1
testdb=# select id, jsonb_pretty(detail::jsonb) from details;
 id |                jsonb_pretty
----+---------------------------------------------
  1 | {                                          +
    |     "myData": [                            +
    |         {                                  +
    |             "email": "example@example.com",+
    |             "personalUse": true            +
    |         },                                 +
    |         {                                  +
    |             "email": "test@test.com",      +
    |             "personalUse": false           +
    |         },                                 +
    |         {                                  +
    |             "email": "test@example.com",   +
    |             "personalUse": true            +
    |         }                                  +
    |     ],                                     +
    |     "myCompany": [                         +
    |         {                                  +
    |             "email": "company@company.com",+
    |             "inProfit": true               +
    |         }                                  +
    |     ]                                      +
    | }
(1 row)


DB2 will not INSERT into Created Temp Table that I created

By default the global temporary tables are created with the option ON COMMIT DELETE ROWS. Whatever tool you are using to run your statements must have the autocommit option turned on, so as soon as you issue the INSERT statement it is committed, thus deleting rows in the table.

You should either create the table using the ON COMMIT PRESERVE ROWS option, or disable autocommit while running your commands and issue an explicit COMMIT when you are done. Which option you choose depends on your business logic.


There is nothing special about configuring Service Broker to work with mirroring.  Configure Service Broker, then enable mirroring.

Having said that, you'll need to test failover to ensure any clients accessing the Service Broker infrastructure, such as queues, are able to failover gracefully from primary to secondary.

I'd recommend building a test-network using Virtual Machines including a primary, a secondary, and a witness VM.  Restore a copy of the target database into the primary, and configure mirroring to see how it works.


I would always create two tables and I would also split the prices tables into many tables as they tend to get really big.

I would do something like this:

Tickers/Companies table:

CREATE TABLE `tickers` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ticker` VARCHAR(10) NOT NULL DEFAULT '0',
    `name` VARCHAR(80) NOT NULL DEFAULT '0',
    `sector` VARCHAR(80) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `ticker` (`ticker`)
)
ENGINE=MyISAM;

Depending on amount of data you expect and the way you query for data, I would then split prices data into tables based on datetime. Let's assume you get lots of data for each ticker per day and that you would mostly query this daily data. I would then form tables likes this:
price_YEAR_MONTH_DAY

Example for two tables:

CREATE TABLE `price_2014_03_23` (
    `ticker_id` INT(10) UNSIGNED NOT NULL,
    `datetime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `open` DOUBLE NOT NULL,
    `high` DOUBLE NOT NULL,
    `low` DOUBLE NOT NULL,
    `close` DOUBLE NOT NULL,
    PRIMARY KEY (`ticker_id`, `datetime`)
)
ENGINE=MyISAM;

CREATE TABLE `price_2014_03_24` LIKE `price_2014_03_23`

So a single day select would look like this:

select p.* from price_2014_03_23 as p
left join
  tickers as t on t.id = p.ticker_id
where
  t.id = 1 and
  p.datetime = '2014-03-23 09:00:00'

If you'd want data for more days, you can do it like this:

select
  p.*
from
  (select * from `price_2014_03_23` WHERE `ticker_id` = 1 union all
   select * from `price_2014_03_24` WHERE `ticker_id` = 1) as p

If you do not have that many data in prices tables, you can simply use only one and query that one.

I usualy split data tables based on queries I perform on it. So, I want to minimize the UNION ALL and make sure all indexes are in place.
If you can provide some more insights on data being gethered (amount also) and the information you want to get from it, I can provide more specific solutions.


How can I generate object DDL, like the CLI "show" command does?

For a SHOW TABLE the parser actually creates the CREATE TABLE based on the current definition.

But for SHOW VIEW, etc. it's retrieving the CREATE from dbc.TablesV.RequestText. If the source code is larger than 12.5 KB RequestTxtOverFlow is set and the remainder is found in one or more 32 KB chunks in dbc.TextTbl.

For SHOW PROCEDURE it's different, this returns the internally stored sorce code (unless the SP is created with the NOSPL option), there's no way to access this info using a Select.


I believe you're referring to a product that uses a Microsoft SQL Server database (but if that's incorrect please add tags to your question with the appropriate database).
In Microsoft SQL Server there a multitude of ways you can move and sync data between databases and servers. It sounds like a solution you'd want would be dependent on one of the options Microsoft offers.
One option that's been around a while is called Replication. Here's the Microsoft docs on it: SQL Server Replication
There's different kinds of replication depending on your needs, but Transactional Replication is pretty popular because of its ability to be near real time in sync. It's also possible to do two-way aka bi-directional replication (so when you make changes at the target server, it re-syncs them back to the source server).
I'm assuming you're an engineer since you mentioned PLM and PCB (I used to be a software developer for an electronics manufacturer in a past life :). You'll likely want help from your IT / Software Dev team setting this up.
Other technologies for moving data in Microsoft SQL Server are:

AlwaysOn Availability Groups
SSIS
Log Shipping

But my personal recommendation from experience and based on your use case would be Transactional Replication.


Select Multiple Values from Same Column; one sql statment

Just use GROUP_CONCAT with a WHERE condition

SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id)
FROM t
WHERE meta_key  IN('first_name','last_name','street_add','city','state')
GROUP BY user_id


Rhetorical Question: Do you really need to rank all 12,000,000 records?

At my employer's web hosting company, we have a gaming client that ranks its top 10,000 players per game platform. They use the same construct you do. You should just limit it to the top 10,000 or whatever number is reasonable

SET @r=0;
UPDATE records SET rank= @r:= (@r+1) where type = 2 ORDER BY seconds DESC LIMIT 10000;

I would run your original query at midnight to get them all

If you must try to rank all, maybe you can try something like this:

DROP TABLE IF EXISTS ranktable;
CREATE TABLE ranktable
(
    rank INT NOT NULL AUTO_INCREMENT,
    rec_id INT NOT NULL,
    PRIMARY KEY (rank)
);
INSERT INTO ranktable (rec_id)
SELECT rec_id FROM records WHERE type=2 ORDER BY seconds DESC;
UPDATE records A INNER JOIN ranktable B USING (rec_id) SET A.rank = B.rank;

What may further help is to index your records table like this:

ALTER TABLE records ADD INDEX type_seconds_ndx (type,seconds);


Does it make sense to order condition in where query?

It makes no difference: the optimiser evaluates the query and will work out predicate order for itself, matching a suitable index and all the other good stuff it does.

This is because SQL is "declarative" not "procedural": you say what you want, not how to do it.

It's nicer to read though...


What's the smartest way to find the scale of an unrestricted numeric type?

is there a more elegant way to do that

Yes, use the scale() function which was introduced in 9.6:

SELECT my_numeric,
       scale(my_numeric)
FROM (
 VALUES
    (0.1::numeric),
    (0.12),(0.123),
    (0.1234),
    (0.12345),
    (0.123456),
    (0.000001),
    (0.100000)
) foo (my_numeric)

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


Changing the locking to table locks will just make the deletes run even slower as the delete won't be able to run until the lock can be taken on the table which means that all other threads need to be finished or blocked.  If you have foreign keys with delete cascade enabled that will probably take a lot of the time.

You might want to change it to a SQL Agent job so instead of running your app which connects and disconnects, you just run a loop deleting data until you are done.

SELECT NULL --Makes the WHILE loop work.
WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP (1000) FROM YourTable
    WHERE Column < getdate()-31
END

If this doesn't work you could look into table partitioning which would allow you to switch the data to another table very quickly, then truncate the data from the new table.  This however does require Enterprise Edition.


Mongo db and sharding / replica sets concept questions

1 - You can have more than 1 mongos instance and connect to whichever you want (the client driver should have that option). Nevertheless, a mongos is just a router, meaning it will only route the requests to the correct shard(s).

2 - Yes, a config server can be in the same machine as a primary/secondary, just don't put config instances together (you are required to have 3 because of redundancy).

3 - A replica set is a group consisting of 1 primary and N secondaries. If you are sharding,  each group will be a shard. That means that the single primary and all secondaries of the same group will have the same data (replicated). Also, having more secondaries won't help you increase the write performance as only the primary is able to do perform inserts/updates/deletes. Considering this, the only way to increase the write performance is by having more shards (horizontal scaling) and, of course, choosing a good sharding key that will balance data across all shards (you don't want all your data to be in 1 shard and all others remain empty). For further info check this official doc explaining the concept of replica set and its members http://docs.mongodb.org/manual/core/replica-set-members/

Also, instances of a replica set shard (the primary and N secondaries) are not required to be in the same machine. You might want to put them in separated machines to increase redundancy and perhaps have a better distribution of the load.


Will a SQL Server 2008 R2 MDF file work on SQL Server 2016 with Windows Server 2016?

The article you reference merely states what version of the SQL Server engine is supported on the various operating systems.

You should not encounter any problems attaching (you don't restore) your mdf file to the SQL Server 2016 instance running on Windows 2016.

Note: During the attach, the database will be upgraded to SQL Server 2016 and can never be restored back to a SQL Server version that is prior to 2016.


Visio Professional(prior to 2013) has an option to reverse engineer a database schema.  I have used this in the past for Access as well as other databases.  However, it does mean you have to purchase the professional version.

You could also migrate the database schema into SQL Server and leverage the database diagrams found there.  You should be able to use SQL Server Express for this.


What is Postgres lock behaviour when UPDATE WHERE clause does a table scan?

For the discussion, let's assume your execution plan looks like
        QUERY PLAN
--------------------------
 Update on mytab
   ->  Seq Scan on mytab
         Filter: (id = 1)

I also assume that you are using the default READ COMMITTED isolation level.
Then PostgreSQL will sequentially read to the table.
Whenever it finds a row that matches the filter, that row will be locked and updated.
If locking a row is blocked by a concurrent query, PostgreSQL waits until the lock goes away. Then it re-evaluates the filter condition and either moves on (if the condition no longer applies on account of a concurrent modification) or it locks and updates the modified row.
See the documentation:

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row. The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.

In particular, it is possible that two UPDATE statements that each modify several rows deadlock with each other, since they acquire locks as the proceed and locks are always held until the end of the transaction.


In addition to specifying the corresponding command line argument, you can also add an option to my.cnf:

Edit my.cnf and add skip-log-bin in the [mysqld] section.
Restart mysqld
Enjoy your lack of binlogs

Note that this will remain in effect until you remove the option from the configuration file.


Support ticket system - when/how to move old tickets?

What is the problem with the table becoming large?  Generally, any sort of OLTP query will access the table using an appropriate index in which case the size of the table is more or less irrelevant.  The cost of using an index will grow at an O(log(n)) rate-- practically, a b*-tree index will only add one or two levels for any realistically sized table.  And you can potentially limit that further by using function-based indexes to limit the size of the index by doing things like only indexing the active rows.

The only queries that should care about the size of the table are queries where you want to do a full scan on the table in order to do things like produce metrics about how many tickets have been opened since the beginning of time (or, at least, over a significant fraction of history).  If you are concerned about those sorts of reporting queries, you can do things like use materialized views to pre-aggregate the data.

Normally, I would suggest keeping a single table and ensuring that appropriate indexes and/or materialized views exist to support the queries about whose performance you are concerned.


Best practices for committing a transaction in SQL Server where TRY CATCH is used

Option A is the correct choice. It is possible for all statements in a transaction to work and then the actual COMMIT to fail, so you keep the COMMIT inside your TRY block so that any failure of the COMMIT will be caught and you can gracefully handle this error and rollback.

See this answer on SE. In your example code, if we assume other users are running this procedure concurrently it might be possible for the TRY block to complete successfully and then fail on the COMMIT due to changes made by other sessions.


PS: sorry for my comments, I was developing a complex code and I have a small error that seems a stupid PostgreSQL restriction on a "returing table" part... I was stupid, instead of concentrating and solving it, I used the internet (search engine put me here). Now, this wiki-answer is to help other readers, called by  search engine and attracted by the title of the question.
Thanks to @dezso (was a correct answer) and, please all readers, you can edit this question to be more didactic, it is a Wiki.

Since PostgreSQL-v8 we can do it! We can RETURNS EXISTING_TABLE_NAME
In its Guide, in all PostgreSQL versions, from since pg v8 to current version, all have a section named  "SQL Functions as Table Sources". Let's reproduce the Guide's example with some simplifications:
CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe'), (1, 2, 'Ed'), (2, 1, 'Mary');

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1);

It is running as expected, it is perfect!
 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed

The question "How to use RETURNS TABLE with an existing table in PostgreSQL?" have a good answer since pg v8... This is the way we do it in the last 15 years, the syntax is:  RETURNS SETOF <EXISTING_TABLE_NAME>.
Use clause TABLE as instantaneous CREATE TABLE for returning
The @tinlyx's confusion, explainded on his question, is about the use of the clause TABLE instead of SETOF... To think using the "PostgreSQL syntax logic", we must  first remember that RETURN <EXISTING_TABLE_NAME> is also valid, and it has the same behavior   that RETURN <EXISTING_TYPE_NAME>. Is natural to return only one row.
Next step, remember that we declare a tuple with the CREATE TABLE clause (<tuple_description>), therefore, a good syntax to express an "instant-define-tuple table" is RETURN TABLE (<tuple_description>), and it makes sense to return TABLE -type, which is like an array type, will return several instances (TABLE is a set of tuples).
Next step, remember that we declare a tuple with the CREATE TABLE (<tuple_description>) clause, therefore, a good syntax to express an "instantaneous table-definition" is RETURN TABLE (<tuple_description>); and it makes sense to return Table-type, that is like Array-type, they return multiple instances (TABLE is a set of tuples).
The "modern thing" in PostgreSQL (!) is what @ZiggyCrueltyfreeZeitgeister showed, the RETURNS TABLE (LIKE <table_name>) syntax.
CREATE FUNCTION getfoo2(int) RETURNS TABLE (LIKE foo) AS $$ -- working fine!
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo2(1); -- same result as getfoo(1)

Many ways to do the same, a summary:
Expliciting the table name (two ways) or type name:

RETURNS TABLE (LIKE <table_name>) (modern and good)
RETURNS SETOF <table_name> (old but good)
RETURNS SETOF <type_name> (after  CREATE TYPE <type_name> (<tuple_description>))

Implicit/generic ways, by anonymous types:

RETURNS SETOF  RECORD (generic but somethimes a problem)
(exist something as?) RETURNS SETOF  ROW?

Instantaneous table-definition:

RETURNS TABLE (<tuple_description>)
(no RETURNS) using OUT in the parameter list.

For the last case, using our example to illustrate: CREATE FUNCTION getfoo(int, OUT fooid int, OUT foosubid int, OUT fooname text)
For dynamic and/or polymophic input you must check this explanation.
Best practice?
There are many ways to do the same, so, there are a "best one"?
As syntax I prefer the use of RETURNS TABLE (LIKE <table_name>), that is explicit: no confusion with "implicit RECORD", no fear of incompatibilities...
Important for library management, DROP TABLE foo CASCADE will drop also the function: in any syntax (returns table or returns setof) PostgreSQL will do a good job.
drop table foo cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function getfoo(integer)
drop cascades to function getfoo2(integer)


SQL DBA data type is changed after upgrade

You should perform the following tasks after updating :

Upgrade Database Compatibility Level

The database compatibility level needs to be manually changed on the 2014 instance

Run DBCC CHECKDB WITH DATA_PURITY

Checks for invalid data values based on column data type

Run DBCC UPDATEUSAGE

Corrects page count inaccuracies to reflect accurate information when using sp_spaceused

Run sp_refreshview

Ensures that views are up to date

Update Statistics

Brings database statistics are up to date to allow the query optimizer to choose the best query plan

Take a Full Backup of the Database
Make sure you can recover the database with all of the migration efforts
Monitor Performance between Cardinality Estimators

It’s important to note: SQL Server 2014 received a major overhaul of the cardinality estimator. For the vast majority of queries, performance will improve due to better row estimations. However, in some scenarios estimations could be worse which may result in performance degradation. It’s important to monitor most commonly used queries to see if any performance degradation is resulting from poor estimations. It is possible to utilize the old cardinality estimator by leveraging a database compatibility level of 110 or lower or by leveraging trace flag 9481.


What's the best way to pass a variable/valuable between steps in a SQL Server job

The only way to pass state between SQL Server Agent T-SQL Job Steps is via a real, i.e. non-#temp, table, perhaps one that exists only in the tempdb database.  Even a global temp table (##temp) won't suffice since even though it would be accessible to multiple sessions, each T-SQL jobstep runs in it's own session that gets created at the start of the step and ends at the end of the step.  The first session to run would create the ##temp table, but when that step ends, the ##temp table would be destroyed prior to the 2nd step starting.
I'd suggest creating a table in tempdb, in the first step of the job, as in:
USE tempdb;
IF OBJECT_ID(N'dbo.JobStepState', N'U') IS NOT NULL
DROP TABLE dbo.JobStepState;
CREATE TABLE dbo.JobStepState
(
    VariableName varchar(25) NOT NULL
        CONSTRAINT JobStepState_pk
        PRIMARY KEY CLUSTERED
    , VariableValue varchar(25) NOT NULL
);

Then, in a subsequent step you can insert a variable into the table with:
INSERT INTO tempdb.dbo.JobStepState (VariableName, VariableValue)
VALUES ('SomeVariableName', 'SomeValue');

And in later job steps, you can refer to the value using:
DECLARE @val varchar(25);
SET @val = (
    SELECT jss.VariableValue
    FROM tempdb.dbo.JobStepState jss
    WHERE jss.VariableName = 'SomeVariableName'
    );
IF @val = 'SomeValue'
    PRINT N'Yes'
ELSE
    PRINT N'No';

DROP TABLE dbo.JobStepState;


I think it has to do with your security requirements / permissions on the backend.  With the single table approach, DB permissions will be for the table, whereas you can permission the multiple tables per user.

You can partition your single inbox table by user. This will allow you to manage it in much the same way as you would the multiple tables for performance.

If you decide on separate inbox tables per user, I would recommend putting them all in their own dynamically created filegroup.

What will happen to the tables or rows when the user leaves?  In two scenarios, 1 where they are dropped and 2 where there are archived, both would be more easily and cleanly accomplished by the table scenario IMHO.


SQl Server Password Change after installation

Enterprise Manager - Object Explorer - Server - Properties - Security - Server Authentification - Sql Server and Windows Authentification mode.

To change password:  Security - Logins - sa - Password

Don't forget to restart te service


If i issue a rollback when recovery mode is bulk logged, will my bulk operations be rolled back?

I decided asking here was just being ultra-lazy so tested it myself.

create table tmp_insert_in_txn ( something int not null primary key )
begin transaction
    select 1 as something into tmp_created_in_txn
    -- show the value was inserted
    select something as in_txn from tmp_created_in_txn

    insert into tmp_insert_in_txn  with ( tablock )
    (   something   )
    select something from tmp_created_in_txn

    -- show the value was inserted
    select something as in_txn from tmp_insert_in_txn
rollback
-- no rows after rollback:
select * from tmp_insert_in_txn
-- no table after rollback:
select * from tmp_created_in_txn

This shows that the rollback does remove the table tmp_test and the row from tmp_test2, so the answer is that minimally logged operations are still rolled back.

Hmm... so how come using them prevents the transaction log from getting really big?


GRANT command not working in MySQL Server

Two points to add:

It is recommended to use another user other than root to connect to your DB in your application. Try to use root account for administration only. This can be good only on testing machines.
Don't forget to flush privileges for the changes to take effect. This is better than restarting mysql.


How can I get a local variable that's a list in a stored procedure?

What I'm totally stopped on is I don't see any equivalent of a List is SQL. Is there a way to build all this up in a list?

So a list in SQL Server is basically a table. You can use table-valued parameters to pass in your complete list from C#. You basically just create a table type to define that list you are passing in, see the example in the MSDN link.

You would just need to take each requirement you list in your question or that the C# is performing, and write the query to pull the correct values from your tabled-values list and create your INSERT, DELETE, and UPDATE statements based on those values.

So for

  Any domain in the passed in list that is not in CompanyDomains is added to CompanyDomains.

Something like:

INSERT INTO CompanyDomains (<column list>
SELECT <column list>
FROM @TableParameter t
WHERE NOT EXISTS ( SELECT domain FROM CompanyDomains cd WHERE t.domain = cd.domain );


Best practice try/catch throw and XACT_ABORT sql server

To implement effective try catch with xact_state kindly follow this msdn link

SET XACT_ABORT on

SET NOCOUNT ON

BEGIN TRY

'DO STUFF'
End Try
BEGIN CATCH

SET @return_msg = 'procedure name FAILED'

  + '. Step ' + CONVERT(VARCHAR,ISNULL(@v_step,0))

  + '. Error Num: ' + CONVERT(VARCHAR,ISNULL(ERROR_NUMBER(),0))

  + '. Error Msg: ' + ERROR_MESSAGE()

  SELECT @return_msg

-- Test XACT_STATE for 0, 1, or -1.
    -- If 1, the transaction is committable.
    -- If -1, the transaction is uncommittable and should
    --     be rolled back.
    -- XACT_STATE = 0 means there is no transaction and
    --     a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT 'The transaction is in an uncommittable state.' +
              ' Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT 'The transaction is committable.' +
              ' Committing transaction.'
        COMMIT TRANSACTION;
    END;

END CATCH

  RETURN 0


What you're describing looks like a many-to-many relationship between table1 and table3 through table2.
Direct many-to-many relations between two tables are illegal and require an intermediate table.
Typically, many-to-many relations are usually verbs in natural language with T1 and T3 being nouns, and T2 being a verb - e.g. Customer (T1) Buys (T2) Books (T3). T2 should have both the PK of T1 and T3 as FKs in its own table to ensure the data matches across the three tables.
The design you have is problematic as strings are not unique, and therefore you can end up joining MJones in T1 to MJones in T3 with no assurance that the two individuals are the same person.
This type of error is acceptable in say a marketing database where some errors may be non-critical, and typically this is a good use in a non-relational database e.g. Big Data. However, in a database where accuracy is paramount e.g. a database recording medicines administered to patients this type of error would have disastrous consequences.
If you use a unique key in your design you will always ensure data consistency - strings are potentially problematic.


By default the DEFINER is the only user that can read stored programs. If DEFINER is left out then the default user that is created the function is used. This is a security feature.

Definer and invoker security contexts differ as follows:

A stored program or view that executes in definer security context executes with the privileges of the account named by its DEFINER attribute. These privileges may be entirely different from those of the invoking user. The invoker must have appropriate privileges to reference the object (for example, EXECUTE to call a stored procedure or SELECT to select from a view), but when the object executes, the invoker's privileges are ignored and only the DEFINER account privileges matter. If this account has few privileges, the object is correspondingly limited in the operations it can perform. If the DEFINER account is highly privileged (such as a root account), the object can perform powerful operations no matter who invokes it.

A stored routine or view that executes in invoker security context can perform only operations for which the invoker has privileges. The DEFINER attribute can be specified but has no effect for objects that execute in invoker context.

https://dev.mysql.com/doc/refman/5.5/en/stored-programs-security.html


What does the Perfmon counter "Suboptimal plans/sec" actually measure?

There are two items this counter tracks:

Timeout
Memory Limit Exceeded

The values can be found in the XML plan as Max has proposed in the comments. I can confirm it has been this way since at least SQL Server 2008.

Kudos to @Max Vernon and @Joe Obbish.

The MSDN BOL Technet Docs description isn't super helpful.


Missing or lost transaction and no errors to show what went wrong

You can use ApexSQL Log to read the transaction log (online, backup, detached). Even in the trial, it shows in GUI all transactions it finds in logs. That can help you troubleshoot the issue.


Count instances of value in multiple tables

One way would be to pivot

WITH CTE AS
(
SELECT [Document No_], 'SL' AS Source
FROM [Sales Line]
UNION ALL
SELECT  [Document No_], 'SLA' AS Source
FROM [Sales Line Archive]
)
SELECT P.[Document No_],
       P.SL,
       P.SLA
FROM CTE
PIVOT (COUNT(Source) FOR Source IN ([SL], [SLA])) P
WHERE P.[Document No_] IN (1,2,3);

I'd hope that the predicate on [Document No_] gets pushed up into the selects from the source tables. Check the execution plans to be sure.


Adding a new value into a unique index locks out (until commit) any other transaction from inserting the same value. When doing that with multiple values in the same transaction, these locks accumulate.

Then when several parallel transactions do that concurrently in no particular order, that creates the opportunity for transactions to inter-lock each other, which causes the error you're reporting.

There's no easy technical solution, because it's more of a conceptual problem.

At the conceptual level, there's a contradiction between making sure that different transactions can't insert an identical value into a unique index (that part being enforced by the database engine), and running these in parallel without having them to care about what the others are doing.

At a higher level (inside the function it's too late), the transactions have to be serialized, or the work partitioned so that parallel transactions can not hit the same part of the unique index at the same time.


OLE DB provider "MSOLEDBSQL" with SQL Server not supported?

You seem to have done everything right. However the link you posted has a comment on the page which states:

This page is no longer maintained. Please read the details below.

If you open the details tab by clicking the + sign you will be greeted with the following information:

This page is no longer maintained. To download the Microsoft OLE DB Driver 18 for SQL Server, please go to the documentation page at https://aka.ms/downloadmsoledbsql.

This will take you to https://docs.microsoft.com/en-gb/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15 (Just in case the short link becomes obsolete.)
I would grab the newest OLE DB drivers from there.
Microsoft OLE DB Driver for SQL Server
Take your time and read the information about the Different generations of OLE DB Drivers on the web page. In the sub section titled 3. Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) there is this small note that states:

The new OLE DB provider is called the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL). The new provider will be updated with the most recent server features going forward.

Based on what you wrote in your question, you must be using the correct OLE DB driver. Good....

Create Linked Server from SQL Server 2019 to SQL Server 2016
I have multiple instances on my laptop and created a linked server from my 2019 instance to a SQL Server 2016 instance using the following stored procedures:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server = N'MSOLEDBDSQL',
    @srvproduct=N'SERVER\sql2016',
    @provider=N'MSOLEDBSQL',
    @datasrc=N'SERVER\sql2016',
    @catalog=N'master'

GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MSOLEDBDSQL', @locallogin = NULL , @useself = N'True'
GO

I then queried the linked server with the following statement:
SELECT name, create_date FROM MSOLEDBDSQL.master.sys.databases

Which promptly returned:

+----------------------+-------------------------+
|         name         |       create_date       |
+----------------------+-------------------------+
| master               | 2003-04-08 09:13:36.390 |
| tempdb               | 2020-11-03 16:29:26.787 |
| model                | 2003-04-08 09:13:36.390 |
| msdb                 | 2016-04-30 00:46:38.773 |
| SSODB                | 2020-01-03 14:35:34.143 |
| BizTalkMgmtDb        | 2020-01-03 14:35:39.570 |
| BizTalkDTADb         | 2020-01-03 14:35:43.370 |
| BizTalkMsgBoxDb      | 2020-01-03 14:35:45.137 |
| BizTalkRuleEngineDb  | 2020-01-03 14:36:21.603 |
| BAMPrimaryImport     | 2020-01-03 14:36:34.713 |
| BAMArchive           | 2020-01-03 14:36:35.333 |
| DemoDB               | 2020-01-15 12:04:41.427 |
| BAMAlertsApplication | 2020-01-28 14:40:20.767 |
+----------------------+-------------------------+

Seems to work just fine.
Create Linked Server from SQL Server 2016 (CI) instance to SQL Server 2016 instance
I then created a linked server from my 2016 Case-insensitive instance to the same SQL Server 2016 instance using the following stored procedures:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server = N'MSOLEDBDSQL',
    @srvproduct=N'SERVER\sql2016',
    @provider=N'MSOLEDBSQL',
    @datasrc=N'SERVER\sql2016',
    @catalog=N'master'

GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MSOLEDBDSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MSOLEDBDSQL', @locallogin = NULL , @useself = N'True'
GO

I then queried the linked server in my CI instance with the following statement:
SELECT name, create_date FROM MSOLEDBDSQL.master.sys.databases

Which promptly returned:

Msg 7430, Level 16, State 3, Line 1
Out-of-process use of OLE DB provider "MSOLEDBSQL" with SQL Server is not supported.

Completion time: 2020-11-05T13:41:43.0333451+01:00

This seems to correlate with what you are observing..... Hmmm. Let's think this over.
Possible Causes

Collation has to be the same: CI or CS
Drivers aren't fully 2016 compatible when connecting from a 2016 to a 2016 instance.
Something is different on SQL Server 2019 instance compared to SQL Server 2016 instance.

Let's go and have a look at the ...
Ojbect Explorer | Server Objects | Linked Servers | Providers | MSOLEDBSQL Properties
We'll open up the properties of the driver on the SQL Server 2019 instance I used first:

...and the properties of the driver on the SQL Server 2016 CI I used in my second test:

And there we have it. The property Allow inprocess on the SQL Server 2016 CI configured instance isn't ticked. The SQL Server 2019 instance has this setting ticked. Let's change it for our SQL Server 2016 instance and run the query. It works!
Solution
Ensure the option Allow inprocess is ticked for the Microsoft OLE DB Driver for SQL Server in its properties in the Linked Server | Providers.


Redundant / partially duplicated data in a DB - how to structure?

You might get better luck on stackoverflow as you've not asked a sys admin question.

The recipe examples don't really give any useful pointers on what you're actually trying to do. If you're after info about removing duplication from a database you should look up normalization and go from there. It's a well known process.

If you're this unsure of how to use mysql with your data you need to reassess what you're doing and make sure you're picking the right tool for the job before you end up going off in the wrong direction, only to regret it later.


You may have placed the log-error option under the wrong header.

Put it here:

[mysqld_safe]
log-error=/var/log/mysql/err.log

Then, service mysql restart

I put it there because the mysql service in /etc/init.d calls mysqld_safe to call mysqld. In addition, /etc/init.d/mysql can pass paramaters to mysqld_safe as shown above.

Just to be safe, run this also

mkdir /var/log/mysql
chown -R mysql:mysql /var/log/mysql

Give it a Try !!!


a query that listing all rows and sizes of tables in a Database


How to automate Oracle DB installation (like a 'hands-free' install)?

Two solutions:

Install Oracle manually the first time. Then zip all ORACLE_HOME and for future installation simply:

Unzip oracle_home into the new home
Run $ORACLE_HOME/bin/relink all
Run ./runInstaller -silent -attachHome ORACLE_HOME="<Oracle_Home_Location>" ORACLE_HOME_NAME="<Oracle_Home_Name>"

SID is a property of the database/instance and is separate from installation of the software.

Other solution (not my preferred but probably the preferred for mass deployment) is to create a response file. The sample is available in the installation dir:

<11g Disk>/database/response/enterprise.rsp

Copy this file, edit it and replace default values with your ones (mainly the path, install type, etc). Then run:

./runInstaller -silent -responseFile myresponsefile.rsp

You can also install the DB manually the first time and record the response file witht he command:

./runInstaller -record -destinationFile myresponsefile.rsp

The first one is my preferred because you can easily install and patch the database with the last PSU available, then you have a consolidated installation can be moved around. Of course all systems must be already prepared with needed packages.


I'd definitely create at least 3 maybe 4 tables/models here.

One table contains all contents with the their details.
Second table with all the users/players.
Third table where you store the n:m relation between user and contest.
(Optional) Fourth table for the different types of sport.

But to be honest I'm not sure how to do this in django. Especially when it comes to the third table.


How do I get a list of "large data users" from my table?

select  username, round(sum(length(payload))/1024) AS metric
    from  wbo
    GROUP BY  username      -- to get one row per user
    ORDER BY  metric DESC   -- list biggest first
    LIMIT  50;              -- but show only 50