How to select specific value from a table that has multiple values for a record in primary key table in PostgreSQL?

Single pass using DISTINCT ON

We can use DISTINCT ON in subselect to get the users highest priority rights. Then we need only count the rights by those groups.

It's important to note, we're joining to rights here. We needn't do that. You could adopt the method of simplicity used in @JackDouglas's answer to compare on ints. It saves a lot of time by not joining but because we only do one table scan we're still twice as fast. In production, you may want to skip the join on rights.

SELECT
  organization,
  count(rightgrp) FILTER (WHERE rightgrp = 1) AS "Reviewer",
  count(rightgrp) FILTER (WHERE rightgrp = 2) AS "Data Entry User",
  count(rightgrp) FILTER (WHERE rightgrp = 3) AS "Read Only User"
FROM (
  SELECT DISTINCT ON (organization, uid)
    organization,
    uid,
    CASE
      WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
      WHEN rights.name IN ('Data Entry') THEN 2
      ELSE 3
    END AS rightgrp
  FROM rights
  JOIN user_rights_rel
    ON rid = rights.id
  JOIN users
    ON uid = users.id
  ORDER BY 1, 2, 3
) AS t
GROUP BY organization
ORDER BY organization;

 organization | Reviewer | Data Entry User | Read Only User
--------------+----------+-----------------+----------------
 A            |        2 |               2 |              1
 B            |        1 |               1 |              3
(2 rows)

Using the tablefunc extension and crosstab

I wouldn't use this method, it's too sledge-hammery for what you need.

Here is a method that users crosstab for display, though it doesn't have to,

SELECT *
FROM crosstab($$
  SELECT organization, rightgrp, count(*)
  FROM (
    SELECT DISTINCT ON (organization, uid)
      organization,
      uid,
      CASE
        WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
        WHEN rights.name IN ('Data Entry') THEN 2
        ELSE 3
      END AS rightgrp
    FROM rights
    JOIN user_rights_rel
      ON rid = rights.id
    JOIN users
      ON uid = users.id
    ORDER BY 1, 2, 3
  ) AS t
  GROUP BY organization, rightgrp
  ORDER BY 1,2
$$, $$
  SELECT * FROM (VALUES (1),(2),(3)) AS t(cat)
$$
) AS ct(organization text, "Reviewer" int, "Data Entry User" int, "Read Only User" int);

First, we optimize the table read to a single pass which we select only the highest-privilege rights for the (organization,user).

SELECT DISTINCT ON (organization, uid)
    organization,
    uid,
    CASE
      WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
      WHEN rights.name IN ('Data Entry') THEN 2
      ELSE 3
    END AS rightgrp
  FROM rights
  JOIN user_rights_rel
    ON rid = rights.id
  JOIN users
    ON uid = users.id
  ORDER BY 1, 2, 3;

 organization | uid | rightgrp
--------------+-----+----------
 A            |   1 |        1
 A            |   2 |        1
 A            |   3 |        2
 A            |   4 |        2
 A            |   5 |        3
 B            |   6 |        3
 B            |   7 |        3
 B            |   8 |        2
 B            |   9 |        1
 B            |  10 |        3
(10 rows)

Then we simply GROUP BY organization, rightgrp and select the count, like this

SELECT DISTINCT ON (organization, uid)
  organization,
  uid,
  CASE
    WHEN rights.name IN ('Reviewer', 'High Reviewer') THEN 1
    WHEN rights.name IN ('Data Entry') THEN 2
    ELSE 3
  END AS rightgrp
FROM rights
JOIN user_rights_rel
  ON rid = rights.id
JOIN users
  ON uid = users.id
ORDER BY 1, 2, 3

 organization | rightgrp | count
--------------+----------+-------
 A            |        1 |     2
 A            |        2 |     2
 A            |        3 |     1
 B            |        1 |     1
 B            |        2 |     1
 B            |        3 |     3
(6 rows)

From there we can feed this to pivot using PostgreSQL's tablefunc extension which provides crosstab. We can generate the pivot manually though using crafty SELECT/FILTER. Keep in mind right #1 is the highest priority

 organization | Reviewer | Data Entry User | Read Only User
--------------+----------+-----------------+----------------
 A            |        2 |               2 |              1
 B            |        1 |               1 |              3

If you want to generate it without crosstab..


I doubt there are many applications that are completely written in stored procedures. Doing a (sophisticated) user interface based on a procedural language in a DBMS is pretty hard to do (depending on the DBMS maybe even impossible).

Oracle's APEX (APlication EXpress) is geared toward that, but the UI is still HTML - although it is generated generated through stored procedures if I'm not mistaken.

One application I know of that makes heavy use of stored procedures as part of the business logic is LedgerSMB: http://ledgersmb.org/


Rebuilding index has impact on mdf/ldf files SQL Server 2008

When you rebuild an index the database engine creates what is called an "intermediate sort result." These intermediate results are like chunks that are combined to become your index and creating these chunks requires space. If it can the database engine will actually sort the index in memory; but if not it will store the intermediate results in the destination file group.

Using the SORT_IN_TEMPDB option asks SQL Server to store these intermediate results in TempDb instead of rebuilding them in the source database. When the TempDb resides on a different disk to the source database this can help the performance of the rebuild task and can reduce the performance impact on your database.

A requirement of rebuilding indexes is that you must have sufficient space (wherever you decide to build the index) to hold the largest index in your database: this in your example is 25GB. Which, if you use TempDb you should quite easily have.

As to whether your source database will grow, well I wouldn't rule it out but it won't grow anywhere near to what it would if you rebuilt the indexes within it. If you are concerned about this fact then take a copy of the database and put it in a test environment and try it out - it is the only way to really know for sure.

SORT_IN_TEMPDB:

http://msdn.microsoft.com/en-us/library/ms188281.aspx

I hope this helps you.


Mongodb Not Restarting with Collections, WiredTiger.wt may be corrupt

There's now an official repair option that worked for me after a sudden computer shutdown https://docs.mongodb.com/manual/tutorial/recover-data-following-unexpected-shutdown/

Create a backup copy of the data files in the --dbpath
Start mongod with --repair

mongod --dbpath /data/db --repair
Upon completion, the dbpath should contain the repaired data files and
an empty mongod.lock

The collections were repaired without data loss.


Moving Databases between Instances of different Versions of SQL Server

The best way will be to use backup-restore - using start-dbamigration .. This will take care of migrating entire instance including all databases, logins, jobs, etc.

Make sure you use -BackupRestore switch with appropriate network location

e.g.

  Start-DbaMigration -Source sql2016 -Destination sql2017 -BackupRestore -NetworkShare \nas\sql\migration


Where is MSOLEDBSQL provider driver? "The 'MSOLEDBSQL' provider is not registered on the local machine."

The new OLE DB driver, MSOLEDBSQL, was released today. This new driver includes the support for the latest TLS 1.2 standards and is backwards compatible with SQL Server Native Client 11 (SQLNCLI11). See the Microsoft SQLNCLi team blog announcement.


SSISDB master key in an Availability Group

We had similar case where the DMK being used within the DB to protect some columns in the database, and DMK protected by Server Master Key (SMK), this is good as always the DMK is opened as long as the SQL Service running with same SMK, there is no need of password in which case the application need to explicitly OPEN and CLOSE DMK using password.
I agree with:

You should make all the nodes in your Always On Group have the same service master key, then failover would be seamless.  https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-service-master-key-transact-sql

Having same SMK across the replicas in availability group would be seamless. It may considered at some-point as less secure, well we still maintain the boundary of AG. i.e. the database cannot be functional when it is restored into a instance other than AG replicas, and we need to extend that boundary (one instance to multiple) as we need to compliance to the High Availability.
Consider it like maintaining same SID of logins across Availability Replicas. To make SMK same in all replicas, you can do following (if there are no other things protected already by SMK on secondary):
---- On Primary replica
BACKUP
SERVICE MASTER KEY TO FILE = '\\Sharedfolder\SMK-202005121328.key'
ENCRYPTION BY PASSWORD = '*************';

---- On Secondary replica
RESTORE
SERVICE MASTER KEY FROM FILE = '\\Sharedfolder\SMK-202005121328.key'
DECRYPTION BY PASSWORD = '*************';


Why not just create luns on the new storage, with same size as on old storage and add them to the existing disk groups. Next drop the old disks from the disk group and when the re-balance is over, your system is migrated to the new storage, without any downtime.

Works like a charm. ASM is way smarter than a filesystem.


Plan 1

If tables COUNTRY, CITY, and STREET were in identifying relationship, then ER diagram would be like below:

With identifying relationship, table STREET already has column country_id, so it has no difficulty to directly join with table COUNTRY.

Plan 2

But if (and as I think is the case) the tables have non-identifying relationship, then ER diagram will be like below:

With this, we cannot join table STREET with table COUNTRY directly because there is no key to connect. So we have to use table CITY in order to join STREET with COUNTRY.

Plan 3

Sometimes we want to avoid this (usually performance issues), so add an additional relationship from table STREET to table COUNTRY. This is called Denormalization. We often do this for performance perspective. Surely, Joining with two tables has better performance than with three tables. But denormalization always bring about many difficulties about maintaining consistency.

In short, we assume that you now have plan2 and you are considering to move to plan3.

Don't. The STREET -> COUNTRY foreign key constraint is redundant.

You can though choose plan1 if you want. You still have only two foreign key constraints (no redundancy) and you can join COUNTRY and STREET directly.

If you are not fond of the compound (multi-column) primary keys or you are afraid of this causing performance issues (in comparison with simple IDENTITY primary keys), you could use a variation on plan1:

Plan 1b

Keep plan1 design (primary and foreign keys) but define the (plan2's) single-column keys as the clustered indexes.


In MySQL, what's the most efficient way to query an entire table in chunks?

OFFSET is very bad.  Instead of processing the data in Order(N) time, it will take Order(N*N).  This is because of the effort to skip past the "offset" rows.

Instead "remember where you left off".  I go into details about such in these two blogs:

Pagination via OFFSET is bad:  http://mysql.rjweb.org/doc.php/pagination (It gives details on "left off" as a good alternative)
How to delete lots of rows via chunking:  http://mysql.rjweb.org/doc.php/deletebig . You need to adapt it from DELETE to what you are doing.  It also gives tips on how to walk through the PRIMARY KEY to efficiently discover the next 'chunk'.

These tips apply to all versions of MySQL.


Checking if user owns child records (MySQL)

You need a Select with 2 joins:

SELECT c.id
FROM c
  INNER JOIN b ON b.id = c.parent_id
  INNER JOIN a ON a.id = b.parent_id
WHERE c.id in (1, 2, 3, 18, 329)
  AND a.user_id = 20 ;


SQL Server order of execution and query plan

The order of execution you have quoted is a version of the Logical Processing Order of the SELECT statement - as the documentation states. The actual physical execution of the statement is determined by the query processor and the order may vary from this list. Also the SELECT node in the plan isn't an execution plan operator at all. This root node just labels the type of statement.

I.e., the DBMS is free to do it any way it wants, as long at it returns the same result as if it had used the logical execution order.


Is it possible to change the default style used by CAST/CONVERT when converting DATETIME?

CAST simply doesn't support the ability to do anything other than a generic conversion, without any flexibility other than inherent ones (e.g. language or dateformat settings, which have to do with how a date string is interpreted, not how much precision it has). Generic conversions of datetime -> string loses precision, regardless of the method. None of these will have seconds:

PRINT GETDATE();
SELECT CAST(GETDATE() AS VARCHAR(23));
SELECT CONVERT(VARCHAR(23), GETDATE());

The answer is to use CONVERT with an appropriate length and a style number (and there is no reason to use VARCHAR for this conversion in isolation).

SELECT CONVERT(CHAR(23), GETDATE(), 126);

Sorry, but there is no server-, database-, or schema-level setting that will make this happen automatically (or change the way CAST works).

Regarding the specific problem you think you have (you can't use CONVERT because of errors with SQL_VARIANT arguments), you can cast as DATETIME within a CASE expression to avoid getting errors on direct conversion of non-convertible values. Either of these forms will work:

DECLARE @x TABLE(y SQL_VARIANT);
INSERT @x(y) VALUES(GETDATE());
INSERT @x(y) VALUES('foo');

SELECT CASE WHEN ISDATE(CONVERT(CHAR(23), y)) = 1
  THEN CONVERT(CHAR(23), CONVERT(DATETIME, y), 126)
  ELSE y END
  FROM @x;

SELECT CONVERT(VARCHAR(4000),
  CASE WHEN ISDATE(CONVERT(CHAR(23), y)) = 1
  THEN CONVERT(DATETIME, y) ELSE y END, 126)
  FROM @x;

And before you say that must be inefficient, you're using SQL_VARIANT and scalar user-defined functions. These extra converts of singleton values will be a mere drop in the bucket.

A disclaimer: you could still end up with errors using this approach, since the ISDATE() test is only checking the first 23 characters of what could be a valid datetime string concatenated with garbage. For example:

INSERT @x(y) VALUES('2000-01-01T00:00:00.000 garbage');

Using the same code as above will lead to:

  Msg 241, Level 16, State 1Conversion failed when converting date and/or time from character string.

So a slightly safer approach would be that suggested by Paul below, using the metadata that SQL_VARIANT stores to ensure you only try to convert data with a date/time base type. This allows you to ignore string lengths etc. as well (except for on output).

DECLARE @x TABLE(y SQL_VARIANT);
INSERT @x(y) VALUES(GETDATE());
INSERT @x(y) VALUES('foo');
INSERT @x(y) VALUES('2000-01-01T00:00:00.000 garbage');

SELECT CASE
  WHEN CONVERT(SYSNAME, SQL_VARIANT_PROPERTY(y, 'BaseType')) LIKE N'%date%'
  THEN CONVERT(CHAR(23), CONVERT(DATETIME, y), 126)
  ELSE y END
  FROM @x;

In the future, rather than just vaguely saying "I am unable to use CONVERT," show us what you're actually doing and why you actually think you can't do something. Some pretty smart folks here that can grok and analyze without having to pull teeth or make assumptions.


How should I back-up an application database during a release?

Use the expdp to dump the schema. When needed you can later import the schema back with impdp.


Get unique ids based on order by another column using group by with row_number

Try this:

select reqID FROM(
select req.reqId, req.RN from (
select
        (ROW_NUMBER() OVER (ORDER BY A.received desc)) as RN,
        A.reqId as reqId
        FROM RequisitionToPatient B
        INNER JOIN Requisition A ON A.reqId = B.reqId
        INNER JOIN Orders C on C.reqId = A.reqId
) req
group by req.reqId, req.RN
order by req.RN) AS a
  GROUP BY reqID

Output:

reqID
------
12
14
11
10
9


CREATE DATABASE Permission denied in database 'master' error

If you can run the code you posted successfully in the context of your domain account then it's likely the tool is connecting to your database as a different user.  Are you able to inspect the connection string it uses?  If so, check to see if it's using Integrated Authentication or is using a User ID/Password combination and modifying things accordingly there.  Either change the connection string to use Integrated Authentication or the SQL login that you created as a test.


use fulltext formsof inflectional to generate terms

You can find the words that will be used in the match with the sys.dm_fts_parser function in SQL Server.

The Syntax

sys.dm_fts_parser('query_string', lcid, stoplist_id, accent_sensitivity)

I won't go in details for the parameters values, but the "lcid" is important.  In the example below I'm using 1033 which is for English - US.  I've tried with 1036, French - Standard, and for sure I didn't get any results beside the word itself, since the word doesn't exist in the French dictionary.

Example

SELECT display_term
FROM SYS.DM_FTS_PARSER('FORMSOF(INFLECTIONAL, welcome)', 1033, 0, 0)

Result

welcome's
welcomed
welcomes
welcomes'
welcoming
welcome


ms-access concat a string with control source

As the users in the comments above pointed me in the right direction, the issue was a circular reference error between the name of the created field (TextBox) and the Control Source. These can be viewed in the Property Sheet of a form or report under All tab. Obviously you don't want to rename the Control Source between this references the table or query the data's being pulled from, so just change the name of the text box. For whatever reason MS Access defaults with the names being the same so this is a common issue. In the image, originally the fields Name and Control Source both contained Insert.


I did a google search for the phrase "sql developer code formatting", and the first hit was this:

http://www.thatjeffsmith.com/archive/2014/03/how-to-make-your-code-look-like-steven-feuersteins-in-oracle-sql-developer/

Have a read through that blog post from Jeff Smith, product manager for SQL Developer.  That should explain what you need to do.

Edited to add picture of the relevant options.


Can I alter an HSTORE column to JSON datatype?

You can simple use hstore_to_json function to get a json out of a hstore. To change the table you can use the ALTER TABLE command.

Example:

ALTER TABLE table_name
    ALTER your_column_name TYPE json USING hstore_to_json(your_column_name);

There is hstore_to_jsonb too if you want jsonb instead of json.


Is the speed of a PostgreSQL SELECT adversely affected by too many indexes on the table?

No, the query performance will not be affected, or not very much.  Indexes are updated on DML statements (and TRUNCATE) while they may or may not be used when executing a query.

The decision whether they will be used are made by the planner.  With very many indexes present I can imagine that the planner spends more time on choosing the usable ones, but I would expect the difference to be small.  (Normally, planning is faster that retrieving rows.)

Note that usually a few indexes will speed up a lot of different queries.  This may not be the case if you filter on many of your columns.

Also note that in a lot of cases (and without knowing your table definition one cannot decide whether your case falls into 'most' or not) having so many columns reflects having a less-that-optimal schema design.


Should I split timestamp parts into separate columns?

Keep the timestamp and don't add columns for the parts.

If you need to search for part of a timestamp, you can always create indexes on extract expressions.

Having individual columns wastes space and adds undesirable redundancy for no benefit I can envision.


Commit or rollback SQLServer-SQLTransaction when no data changed

My advice
Think about it the other way around.
Only issue a ROLLBACK if you have a reason to undo everything since the start of the transaction--such as an exception, a bad state, or an explicit desire to undo everything. If everything is going as planned, then you should always COMMIT.
Why?
Issuing a ROLLBACK will result in SQL Server undoing any work that was performed inside the transaction.
Even if you "know" that SQL Server hasn't changed data, there's really no benefit in issuing a ROLLBACK unless you want SQL Server to undo changes because you're worried about inadvertent changes. If you are 100% sure that nothing has changed, then I can be 100% confident saying you should just issue a COMMIT instead.
If your code has made changes that are technically updates, but are logically no change, then a ROLLBACK would be extra work.
Here's an example:
First, let us create a new database that is fresh, clean, and pristine. It's as clean & fresh as new bed sheets:
CREATE DATABASE RollMeBack;

ALTER DATABASE RollMeBack SET RECOVERY SIMPLE;

USE RollMeBack
GO
--Create a table
CREATE TABLE dbo.WidgetQueue (
    QueueID bigint identity(1,1),
    QueueStatus char(1),
    SomeOtherStuff varchar(500)
    CONSTRAINT PK_WidgetQueue PRIMARY KEY CLUSTERED (QueueID)
);
GO
--put some stuff in it
INSERT INTO dbo.WidgetQueue (QueueStatus, SomeOtherStuff)
SELECT  QueueStatus    = CASE c2. column_id%3
                           WHEN 0 THEN 'Q'
                           WHEN 1 THEN 'S'
                           WHEN 2 THEN 'L'
                         END,
        SomeOtherStuff = c1.name + c2.name
FROM sys.columns AS c1
CROSS JOIN sys.columns AS c2;
GO 5

CHECKPOINT;

BACKUP DATABASE RollMeBack TO DISK = 'NUL;';

That'll make a nice big table. It probably doesn't need to be that big. Now, let's take a fresh CHECKPOINT and look at the transaction log. I get 3 rows returned. Doesn't matter what they say, that's not important right now.
USE RollMeBack
GO
CHECKPOINT;

SELECT *
FROM sys.fn_dblog(NULL,NULL);

Now, we're going to do an update, in a transaction, that logically is making no changes, but it's actually going to update a bunch of rows. For me, this runs in 2 seconds, and updates 1892100 rows setting the status to exactly the same value as is currently there.
BEGIN TRANSACTION
    UPDATE q
    SET QueueStatus = 'S'
    FROM dbo.WidgetQueue AS q
    WHERE QueueStatus = 'S';

Now, lets look at the transaction log again:
SELECT *
FROM sys.fn_dblog(NULL,NULL);

 For me, that returns 2916 rows--these are 2916 log records that record the transaction in the transaction log. Your mileage may vary. These log records are basically a measure of what SQL Server did during your transaction. All these log records tell us that SQL Server did a whole bunch of work inside that transaction. Logically, it had a net zero result, and the data will look exactly the same as at the start....except SQL Server actually updated those rows.
If I issue a ROLLBACK now, SQL Server is going to undo those updates. Logically, it's the same outcome as having left them alone, but SQL Server is just doing what it was told, and it has to go through those 2916 log records to untangle the word it did, and make sure it leaves everything exactly as it was at the start of the transaction.
If I issue a COMMIT instead, SQL Server just says "OK. DONE!" and logs the COMMIT without having to untangle work done.
And before we go, lets drop that database we created to test.
USE master
GO
DROP DATABASE RollMeBack;

Other Considerations
What if there was a trigger on my dbo.QueueStatus table? Then my update could have fired off a bunch of other work that I didn't realize, but is still important.
You're also putting the onus on the developer to know, understand, and maintain the fact that there was zero work done inside that transaction. Future changes to the application might introduce a code path that results in a change that should be persisted--now the application logic needs to be updated to know not to rollback.
What about logging? Even if it is just temporary logging during the development cycle to throw some stuff into a log table to try to troubleshoot a problem, that logging would be rolled back, too.


Optimising BCP performance for BLOB data

This is not a direct answer to your question, but there are some articles that you would benefit from reading them (in case you didn't find them first :-) ). They are about loading lots of data using bcp/bulk copy. I've read them all and I didn't find anything detailed regarding KILOBYTES_PER_BATCH, they're all using ROWS_PER_BATCH, but I'm sure you will find other useful information.

Load 1TB in less than 1 hour (from SQL CAT team) - list of advices from here (quote):

Run as many load processes as you have available CPUs.  If you have 32 CPUs, run 32 parallel loads.  If you have 8 CPUs, run 8 parallel loads.

If you have control over the creation of your input files, make them of a size that is evenly divisible by the number of load threads you want to run in parallel.  Also make sure all records belong to one partition if you want to use the switch partition strategy.

Use BULK insert instead of BCP if you are running the process on the SQL Server machine.

Use table partitioning to gain another 8-10%, but only if your input files are GUARANTEED to match your partitioning function, meaning that all records in one file must be in the same partition.

Use TABLOCK to avoid row at a time locking.

Use ROWS PER BATCH = 2500, or something near this if you are importing multiple streams into one table.

Top 10 Best Practices for Building a Large Scale Relational Data Warehouse (from SQL CAT team) - advices (quote):

Use SIMPLE or BULK LOGGED recovery model during the initial data load.

Create the partitioned fact table with the Clustered index.

Create non-indexed staging tables for each partition, and separate source data files for populating each partition.

Populate the staging tables in parallel (Use multiple BULK INSERT, BCP or SSIS tasks)

Build a clustered index on each staging table, then create appropriate CHECK constraints.

SWITCH all partitions into the partitioned table.

Build non-clustered indexes on the partitioned table.

The Data Loading Performance Guide (from SQL CAT team)

Loading Bulk Data into a Partitioned Table - SQL Server Best Practices Article  (Technet article)

SQL Server 2000 Incremental Bulk Load Case Study (Technet article)

Lessons Learned and Findings from a Large Fast-Track POC (from SQL CAT team)

Performance Tuning Tips for SQL Server BCP (By Brad McGehee)

Performance Impact: Finding the Most Optimal Batch Size  (By Linchi Shea)

and the obvious MSDN references:

Managing Batches for Bulk Import
Managing Bulk Copy Batch Sizes
Bulk Copy Performance Considerations
Guidelines for Optimizing Bulk Import
Optimizing Bulk Import Performance

In my personal experience, I succeeded to make a fast data load using parallel load and testing with several batch sizes. I guess that only personal testing will suit you.
Hopefully you will find some good advices in the references.


Are composite primary keys bad practice?

To say that the use of "Composite keys as PRIMARY KEY is bad practice" is utter nonsense!
Composite PRIMARY KEYs are often a very "good thing" and the only way to model natural situations that occur in everyday life! Having said that, there would also be many situations where using a composite PK would be cumbersome and unwieldy and therefore not an optimal choice.
Your question is: "if composite primary keys are bad practice... (answered) and if not, in which scenarios is their use beneficial?".
Below is an example of where composite keys represent a rational/beneficial choice as the PK (indeed the only rational one as I see it - in the fiddle here, there's an extra example of having grades also!
On the plus side for composite keys, think of the classic Databases-101 teaching example of students and courses and the many courses taken by many students!
Create tables course and student:
CREATE TABLE course
(
  course_id SERIAL,
  course_year SMALLINT NOT NULL,
  course_name VARCHAR (100) NOT NULL,
  CONSTRAINT course_pk PRIMARY KEY (course_id)
);

CREATE TABLE student
(
  student_id SERIAL,
  student_name VARCHAR (50),
  CONSTRAINT student_pk PRIMARY KEY (student_id)
);

I'll give you the example in the PostgreSQL dialect (and MySQL) - should work for any server with a bit of tweaking.
Now, you obviously want to keep track of which student is taking which course - so you have what's called a joining table (also called linking, bridging, many-to-many or m-to-n tables). They are also known as associative entities in more technical jargon!
1 course can have many students.
1 student can take many courses.
So, you create a joining table
CREATE TABLE registration
(
  cs_course_id INTEGER NOT NULL,
  cs_student_id INTEGER NOT NULL,

  -- now for FK constraints - have to ensure that the student
  -- actually exists, ditto for the course.

  CREATE CONSTRAINT cs_course_fk  FOREIGN KEY (cs_course_id)
    REFERENCES course  (course_id),
  CREATE CONSTRAINT cs_student_fk FOREIGN KEY (cs_student_id)
    REFERENCES student (student_id)
);

Now, the only way to sensibly give the registration table a PRIMARY KEY is to make that KEY a combination of course and student. That way, you can't get:

a duplicate of student and course combination

a course can only have the same student enrolled once, and

a student can only enroll in the same course one time only

you also have a ready made search KEY on course per student - AKA a covering index,

it is trivial to find courses without students and students who are taking no courses!
-- The db-fiddle example has the PK constraint folded into the CREATE TABLE
-- It can be done either way. I prefer to have everything in the CREATE TABLE statement.

ALTER TABLE registration
ADD CONSTRAINT registration_pk
PRIMARY KEY (cs_course_id, cs_student_id);

Now, you could, if you were finding that searches for student by course were slow, use a UNIQUE INDEX on (sc_student_id, sc_course_id).
ALTER TABLE registration
ADD CONSTRAINT course_student_sc_uq
UNIQUE (cs_student_id, cs_course_id);

There is no silver bullet for adding indexes - they will make INSERTs and UPDATEs slower, but at the great benefit of greatly decreasing SELECT times! It's up to the developer to decide to index given their knowledge and experience, but to say that composite PRIMARY KEYs are always bad is just plain wrong.
In the case of joining tables, they are usually the only PRIMARY KEY that make sense! Joining tables are also very frequently the only way of modelling what happens in business or nature or in virtually every sphere I can think of!
This PK is also of use as a covering index which can help speed up searches. In this case, it would be particularly useful if one were searching regularly on (course_id, student_id) which, one would imagine, can often be the case!
This is just a small example of where a composite PRIMARY KEY can be a very good idea, and the only sane way to model reality! Off the top of my head, I can think of many many more.
An example from my own work!
Consider a flight table containing a flight_id, a list of departure and arrival airports and the relevant times and then also a cabin_crew table with crew members!
The only sane way this can be modelled is to have a flight_crew table with the flight_id and the crew_id as attibutes and the only sane PRIMARY KEY is to use the composite key of the two fields!


Hyper V replication - vs - sql replication solutions

I think the answer depends on the SQL skill level of the people who will ultimately be responsible (lose their job) if the data can't be recovered in a DR scenario.

Most of the time when DBAs are chit chatting you'll hear horror stories of vendor solutions that didn't work, or stopped working, or weren't monitored. And when the data is lost it's lost. I've seen all 3. It's all good to say some other team will be monitoring it with a fancy tool - until a few months later when they've learned to ignore the alarms, and simply don't have time to respond to your repeated emails that it's broken.

That's why DBAs like me are a bit anal and insist on ownership and responsibility wherever there's accountability. I keep backups in SQL because then I can verify they're working and monitor them as well. I keep it all as simple as possible because simple works.

On the other hand if someone hands you a server and says "this is a copy, confirm its a proper copy and working", there's not a whole lot you can do without SQL Data Compare and an outage window.

If a place doesn't have a DBA, or there are licensing restrictions or software installed on the database server, then you make do with what you can. And vendors and consultants will often play along as well because when the data is lost they've already been paid and moved on (again they're making do with what they've been told or provided).

But for me? For best practice and not even a difficult one at that? No vendors in my backups.

PS: The exception being underlying snapshots of the backup media like network drives, for long term or redundant storage, as long as it's clear whose responsible for validating that's working. However again it's a matter of buyer beware - when it runs out of snapshot space someone will inevitably delete all your old backups without consultation; I've seen it happen time and again. But in this case as long as it's clear X people are responsible for long term file storage there's not much you can do.


How to migrate postgreSQL on EC2 instance to AWS RDS?

You can't do it with minimal downtime, or not easily anyway.

RDS doesn't support importing base backups and doing streaming replication to/from outside servers.

See Getting WAL from AWS RDS and Moving a standalone DB to Amazon RDS.

You'll have to dump and reload.

If you're keen, you could instead set up Londiste or a similar tool to stream changes from the standalone server into RDS, with the Londiste pgq daemon and the subscriber daemon both running on AWS EC2. The subscriber would connect remotely to RDS over the PostgreSQL protocol. This lets you use trigger based replication to keep the DBs in sync, giving you a shorter outage window. You still have to disable writes on the old DB and make sure Londiste is fully caught up before switching over, though.


Migrate from innodb_file_per_table to off in MySQL ALTER TABLE removing ibd files

Found a solution that works

MariaDB [(none)]>  SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH LIKE '%5377%';
+---------+---------------------------------+
| SPACE   | PATH                            |
+---------+---------------------------------+
| 2432544 | ./db5377/menu_router.ibd |
+---------+---------------------------------+
1 row in set (0.06 sec)

MariaDB [(none)]> SET SESSION old_alter_table=1
-> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> ALTER TABLE db5377.menu_router FORCE;
Query OK, 2331 rows affected, 1 warning (0.10 sec)
Records: 2331  Duplicates: 0  Warnings: 1

MariaDB [(none)]>  SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES  WHERE PATH LIKE '%5377%';
Empty set (0.05 sec)

To convert all the separate table spaces into the shared space.
Credit to: Migrate from innodb_file_per_table to off in MySQL

echo "SET SESSION old_alter_table=1;" > ConvertInnoDBToInnoDB.sql
MYSQL_CONN="-uroot -p"
SQL="SELECT CONCAT('ALTER TABLE ',REPLACE(REPLACE(REPLACE(PATH,'./',''),'.ibd',''),'/','.'),' FORCE;')"
SQL="${SQL} FROM  INFORMATION_SCHEMA.INNODB_SYS_DATAFILES WHERE PATH NOT LIKE '%mysql%'"
mysql ${MYSQL_CONN} -AN -e"${SQL}" >> ConvertInnoDBToInnoDB.sql

mysql> source /root/ConvertInnoDBToInnoDB.sql


Is using the alpha version of Percona 5.6 safe?

Big Percona fan here.  I'm really excited about a lot of 5.6 features (although many seem to be things Percona already provided).  At the same time there's no way i'm rolling out an alpha into production and no way I'm going back to stock and loosing thing I get from percona 5.5  Largely I suppose those "things" are related to information schema (which is just for my DBA satisfaction; not performance implications) as well as knowing I have a trusted xtrabackup situation going on w/ percona builds.

I'm personally waiting for Percona's 5.6 GA before even deploying to QA for evaluation.

As for the fulltext feature you're wanting I'm of the opinion search like that is not the role of a relational database and should be delegated to a lucene solution such as an in house elastic search install, or searchify for a cloud based solution if you don't have the resources to manage your own search service.

My opinion on your implementation isn't really an answer though, so to get to that more directly: Do not deploy alphas to production.  Alpha is the disclaimer saying "play around if you like but we're not sure we trust what we've built yet ourselves"


setting statistics time on/off giving different results

If there are user defined functions in your code, there can be significant overhead to setting STATISTICS TIME ON. See here.

More importantly, be REALLY careful when using scalar UDFs. They will inhibit parallelism for your query, and have a nasty habit of running once per row returned by your query.

The impact they have on STATISTICS TIME should be the least of your concerns.


MSDTC provides transaction management services for applications. It can act as a regular transaction manager or an XA-compliant resource manager. SQL Server is the well known app that uses DTC but it isn't really a SQL Server feature or even component. MSDTC is really intended to provide transaction management for two or more distinct resources to ensure transactional consistency. Transaction management  is built into SQL Server for a single resource/database but right now it doesn't natively handle multiple resources. See this old MSDTC blog for a more detailed description. The content is really old but the concepts are still accurate.

As for your questions,

There are many other transaction managers out there but SQL Server is integrated with DTC by default (e.g. starting your query with BEGIN DISTRIBUTED TRANSACTION will automatically enlist DTC as a transaction manager). If you use JDBC, you can also configure SQL Server to support XA by installing the XA component from the JDBC package. There's a driver and some install script to do that (all within the package). That sets up some XPs to provide XA transaction support. There may be other TMs out there for SQL Server but I've not encountered them.
Yes it can, depends on what you're doing. DQ can enlist DTC to manage transactions (it'll automatically promote to a DT) if needed. E.g. READ only operations will not but WRITE will.
Transactional replication without updateable subscriptions do not require MSDTC. It is log based and regardless of push or pull, there is no concept of distributed transactions.


What's the fastest way to execute a MySQL Dump of a large Database?

Set up the OS with LVM Snapshots.  Preferably have MySQL in a separate partition or drive that is to be snapshotted.  Then to take a dump:

Stop mysqld
take snapshot (a minute or so, regardless of dataset size)
start mysqld

Copy (suggest rsync) the snapshot to the other machine (this takes time) into an identical snapshot area.

start mysqld

(Caveat:  I have never tried this, so there could be some rough edges.)


No, logging and recovery don't work at that level. What I recommend is, instead of trying to make one database serve two different recovery objectives, store this logging information in its own database (or a different, existing database) with alternate recovery objectives.

If that is not an option for some reason, the only thing you can really do to minimize the impact on the log is to:

Issue truncate instead of delete - which is still logged, but less so.
Instead of allowing for lots of small inserts, queue those up into batches and do what you can to make the eventual insert eligible for minimally logged transactions.


Single row to multiple rows with column combination

Given this test data:

DECLARE @T AS TABLE
(
    ZIP sql_variant NOT NULL,
    STATE sql_variant NOT NULL,
    F sql_variant NULL,
    C sql_variant NULL,
    F1 sql_variant NULL,
    C1 sql_variant NULL,
    F2 sql_variant NULL,
    C2 sql_variant NULL,
    F3 sql_variant NULL,
    C3 sql_variant NULL
);

INSERT @T
    (ZIP, STATE, F, C, F1, C1, F2, C2)
VALUES
    ('A', 'AS', 'AF', 'AC', 'AF1', 'AC1', 'AF2', 'AC2');

INSERT @T
    (ZIP, STATE, F, C, F1, C1)
VALUES
    ('B', 'BS', 'BF', 'BC', 'BF1', 'BC1');

One way to produce the desired output is:

SELECT
    T.ZIP,
    T.STATE,
    CA.F,
    CA.C
FROM @T AS T
CROSS APPLY
(
    VALUES
        (F, C),
        (F1, C1),
        (F2, C2),
        (F3, C3)
) AS CA (F, C)
WHERE
    CA.F IS NOT NULL
    OR CA.C IS NOT NULL;


Does mysql horizontal partitioning create new ibd files for each partition?

Short answer: Yes; 4 .ibd files of about 1/4 the total size.

Long answer:

There are multiple things going on...

Different engines have PARTITIONs implemented.  In all cases from 5.1 (when Partitioning was introduced) through at least 5.7, a partition is effectively a separate table.
innodb_file_per_table controls whether InnoDB puts new tables (and its partitions) in the ibdata1 file, or in individual .ibd file(s).
"Native partitions" may be coming soon (5.8?); that will change much of this answer.
Depending on the data distrubution and the details of the PARTITION clause in your CREATE TABLE, the partitions are not necessarily similar size.

In my experience, PARTITION BY RANGE is the only useful one, and there are only 4 use cases where it is of any use.  Details.


What does the Service Master Key protect?

Look at the encryption hierarchy found  in this TechNet article.  This document from MS shows the entire hierarchy.  You can see that the Master Key is created from the Windows DPAPI service and is used for:

-DB Master Key

-Certificates

-Symmetric Keys

-Asymmetric Keys

-TDE

-Transact SQL Encryption Functions

-Passwords (I'm not 100% on this, but it is in the technet article and it was mentioned in the comments so I added it)

Was this for a school project or a cert exam? Do you need any more background info?


Is COUNT(DISTINCT PRIMARY_KEY) faster than COUNT(*)?


The RESTORE DATABASE command will create the database for you.

Don't create it first because then it will have to be deleted by the RESTORE command and a type if safety check in there might not allow the restore unless you specify the REPLACE option.

Also, is the database in that backup was read only when the backup was produced, then it will be read only when restore. Just turn off that database option after you done the restore.


An UPDATE will read with U locks, releasing each U lock immediately if it is not going to X lock and change the row.  Profiler will show you this with the Lock:Acquired and Lock:Released events.

EG an update on a heap will IX lock the object, then for each page IU lock the page, then for each row U lock the row, and either release the U lock or convert to IX lock on the page, convert to X lock on the row and update it.  The page lock will be released when it's read the last row on the page.

use tempdb

select *
into test
from sys.objects

go

begin tran
update test set name = 'foo2'
where object_id = 9

Has a lock sequence like this:

Lock:Acquired                               59      8 - IX  5 - OBJECT
Lock:Acquired   5:64                            59      7 - IU  6 - PAGE
Lock:Acquired   5:64:0                          59      4 - U   9 - RID
Lock:Released   5:64:0                          59      4 - U   9 - RID
Lock:Acquired   5:64:1                          59      4 - U   9 - RID
Lock:Released   5:64:1                          59      4 - U   9 - RID
Lock:Acquired   5:64:2                          59      4 - U   9 - RID
Lock:Released   5:64:2                          59      4 - U   9 - RID
Lock:Acquired   5:64:3                          59      4 - U   9 - RID
Lock:Released   5:64:3                          59      4 - U   9 - RID
Lock:Acquired   5:64:4                          59      4 - U   9 - RID
Lock:Released   5:64:4                          59      4 - U   9 - RID
Lock:Acquired   5:64:5                          59      4 - U   9 - RID
Lock:Acquired   5:64                            59      8 - IX  6 - PAGE
Lock:Acquired   5:64:5                          59      5 - X   9 - RID
Lock:Released   5:64:5                          59      0 - NULL    9 - RID
Lock:Acquired   5:64:6                          59      4 - U   9 - RID
. . . continue reading rows


The problem you face is known as "Normal forms" of databases, especially the first normal form. https://en.wikipedia.org/wiki/First_normal_form.

Your databse with the concatenated user IDs (first version) is not in first normal form.

See https://en.wikipedia.org/wiki/Database_normalization for why and how normalisation is generally considered good.

In your first example, the query for "user 4 does not like the post anymore" becomes complicated. It will have to do string operations, which will have to consider side effects and corner cases (user is the only "liking" user, user is the last liking user, user is in the middle of the liking user string). I would find this bad. Don't do it. Use a normalized design.

re: database gets heavy

If you have a post that has 4 million likes, in database design 1 you would have one row with a "likes" column that is at least 4 million characters wide (because you'll need the comma as seperator chars). You will then have to perform string operations on four million digit wide strings. This is very unperformant and slow.

On the other hand, databases are designed to handle millions of rows. We have databases with several hundred million rows, and count()-operations are fast. Extremely fast. So no, this will not be a performance bottleneck.

The next issue would be readability and maintainability.

For example, tell me what these 2 statements do:

select count(*)
from posts
inner join likes on posts.postid = likes.postid
where postid = 7

select len(likes) - len(replace(likes, ',', ''))
from posts
where postid = 7


Alas, no. You cannot parameterize views like that, you need a function to do it.

I rather hope someone proves me wrong, as I much prefer views to set-returning functions.  But I doubt they will.


Multiple Transaction Log files and performance impact

Transaction log writes are sequential. Only one of the log files will ever be written to at any one time, so having multiple files - in and of itself - can't possibly change your I/O patterns for that database.

Unless you are getting lucky. For example, you've added a second log file to an SSD or otherwise faster or less busy disk, or split the log files across multiple disks and have done so for multiple databases, and you are observing better I/O now because the log has switched to that file on the faster disk, or is more isolated from your other data/log files. In other words, I believe any observed I/O difference is due entirely to other factors and is merely a coincidence, not due to the fact that you added log files alone. SQL Server is explicitly designed to only use one log file at a time - so how could multiple log files possibly improve log write performance, unless the current log file is on faster / more isolated disk? I think you need to provide better empirical evidence (and in doing so you may discover for yourself the true cause of the improved performance).

Please read these posts in full - they were written by a pretty smart guy who worked on the SQL Server storage team for quite some time, so I don't think he's making any of this stuff up for fun:

Multiple log files and why they’re bad
Importance of proper transaction log size management

Also Kimberly Tripp touches on this in a worthwhile article:

8 Steps to better Transaction Log throughput

Note that none of the 8 steps involves adding a transaction log file. In fact she recommends against it.

There are other perils to having multiple log files, particularly if they are large (think RTO) - and there really is nothing to gain.


Run an SSIS package located on a linked server from a stored procedure

I would recommend creating a SQL Agent job on server 2 then running sp_start_job over the linked server to execute it. That stored proc is documented here.

If your SSIS package is deployed to SSISDB then you could execute it directly via stored proc if you prefer. See the answer on this question.


Query system specs (cpu, memory, data disks, free space) using T-SQL

Yes, you bet. Let's start with the open source First Responder Kit (disclaimer: I'm one of the script authors) - install sp_Blitz, and then:

sp_Blitz @CheckServerInfo = 1

And at the bottom, you get a bunch of information about your system:

I've taken the liberty of rearranging the columns a little in that screenshot because I want to show you the finding, details, and the CheckID.

When you see a CheckID that has information you want to use, open up the sp_Blitz script and do a search for that CheckID. For example, if you want to know how we're determining that you have 4 logical processors and 31GB RAM, do a control-F for 84 (the CheckID), and you'll see the dynamic management views (DMVs) where we're gathering that data from.

Check 84 is a great example because it requires different logic based on what version of SQL Server you're trying to check, too.

Rather than giving you the specific answers for each of the 4 questions you asked, I'm just teaching you this process because I bet if you need those 4 things, you probably need a lot more, too - and you'll find a lot of 'em in sp_Blitz. Hope that helps!


What does "psql: /usr/pgsql-11/lib/libpq.so.5: no version information available (required by psql)" actually mean?

You have installed a mixture of packets: some from the Fedora distribution, some from the PostgreSQL Global Development group.
These don't work well together. In particular, you have psql from the Fedora packages in the PATH, but libpq from the PGDG packages is on the library path.
That explains your question #2.
Ad question #1: that warning is printed if the library version of the installed library is lower than the one the software was linked with.
Remove the Fedora packages to get rid of the problem:
dnf remove postgresql postgresql-server

I hope that puts the correct psql on your PATH; if not, reinstall the postgresql11 package.


Which columns should be indexed when all may be used in different search queries?

Composite Primary Key

I would define the primary key as a composite key of (CinemaID, MovieID, Showtime).

These 3 columns uniquely identify each row, and so having a separate ID column is not necessary.

Composite (Secondary) Index

With this PK, the only additional index you will need for your queries is (CinemaID, Showtime).

Why these indexes?

A good way to think about indexes are used is to think of them as the order of columns in a spreadsheet.

Imagine a spreadsheed with (CinemaID, MovieID, Showtime) that is sorted by each column successively.

All your queries have CinemaID present, which means you can quickly find the "section" of the spreadsheet for that CinemaID. Then, for your query that searches by MovieID, you can easily find the "subsection" in the 2nd column, where MovieID matches the searched for value.

As the 3rd column of Showtime is also sorted, you can imagine how quickly and easily it is to find all those show times for that movie, in that cinema. The DBMS does things in a similar way and can retrieve those results extremely quickly.

As for your other queries, they all start with CinemaID and then use Showtime in some manner. They also need the MovieID in their results.

So, the (CinemaID, Showtime) index has you covered there. Again, the CinemaID easily finds the "section" of the spreadsheet (in the analogy), and all possible showtimes (and there are going to be duplicates, assuming there is more than one screen) will be listed in order and are easily searched and/or sorted by those values.

Even better, since your primary key includes MovieID, that column is included for all secondary indexes after the defined columns (at least for MySQL InnoDB - others engines too, but not necessarily all.)

Think of that is a 3rd column in the "spreadsheet" of our secondary index. The reason the column exists is to have all parts of the primary key available to do a lookup to the main table (aka the clustered index, in InnoDB), if necessary. In this simple case, no lookup is needed, so it is even more efficient as it doesn't require that double lookup.

Using just this primary key and a single secondary index, you should obtain excellent performance on any of the queries you listed.

Afterthought

My assumption that this uniquely identifies each row may NOT be correct if you have a movie showing on multiple screens at the same time. If you want to be able to separately identify those screens, then my solution is not best (I can provide another solution for that situation, please just let me know.)


Mirror log empty on primary but fine on the mirror

Well, its took months but i finally found the problem!

The sys.dm_os_performance_counters wasnt being populated at all on the primary server.

Turns out that the counters where not installed, i was able to do this manually as follows:

C:\Windows\system32>lodctr "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSS
QLSERVER\MSSQL\Binn\perf-MSSQLSERVERsqlctr.ini"

Restarted SQL, and boom, the table is now being populated - including my mirroring statistics.


Retrieving a fixed range of rows from Postgresql

The proper way to do this is using LIMIT and OFFSET.

Note that for this to work properly, your query will need an ORDER BY clause so that the rows are fetched in the same order each time.

Your first query would be:

SELECT qid,ansid,ans
FROM table1
WHERE askerid='something'
LIMIT 50 OFFSET 0

The second query would have an OFFSET of 50, as you want to skip the first 50 rows:

SELECT qid,ansid,ans
FROM table1
WHERE askerid='something'
LIMIT 50 OFFSET 50

The ORDER BY clause wants adding between the WHERE and LIMIT clauses.

The Postgres documentation on this is here, and mentions that this may not be performant for large OFFSET values. It will be ok for small-ish datasets if the ORDER BY column is indexed.


You should not have the quorum disk as a resource inside a "Service or Application".  It needs to be configured separately, and should be visible only under the "Disk Witness in Quorum" section in the "Storage" configuration pane.

When the cluster is configured with either "No Majority - Disk Only" or "Node and Disk Majority", the cluster service uses the quorum disk as a "vote" - since the disk can only reside at a single node.  If the nodes cannot communicate with each, only a single node will be able to connect to the disk and obtain the required number of votes to achieve quorum.  This prevents a partition where both nodes think they can run the services and applications configured in the cluster.  If no nodes can connect to the quorum disk, none of the nodes will run the cluster services and applications.

Assigning the quorum disk to a Service or Application means the disk will go offline when a failover occurs.  If the disk goes offline, it cannot be used as a vote, and the entire cluster will not operate.

See Windows Server 2008 R2 Cluster Docs and The process of achieving quorum for details


How to select id of father from the child id recursively

That is not Oracle syntax. Below is Oracle syntax:

drop table menu purge;
CREATE TABLE menu
(
  id INT PRIMARY KEY
, name NVARCHAR2(40)
, parent_id INT REFERENCES menu (id)
);

INSERT INTO menu VALUES (1,'Main Product',NULL);
INSERT INTO menu VALUES (2,'Next Main Product',NULL);
INSERT INTO menu VALUES (3,'Addon Product',1);
INSERT INTO menu VALUES (4,'Addon Product',1);
INSERT INTO menu VALUES (5,'Sub-Addon Product',3);
INSERT INTO menu VALUES (6,'Sub-sub-Addon Product',5);
INSERT INTO menu VALUES (7,'Addon Next Main Product',2);
commit;

select listagg(id, ',') within group (order by id) from menu
connect by id = prior parent_id
start with id = 6;

LISTAGG(ID,',')WITHINGROUP(ORDERBYID)
-------------------------------------
1,3,5,6


Oracle 9i import error IMP-00017: following statement failed with ORACLE error 3113

ORA-03113 is a generic error encountered when your database server process was terminated with a critical error. You can find more details in the database alert log. I guess you will see an ORA-07445 error there.

As we do not know these details, all I see is that your error is related to importing statistics. I would try to run this import without importing statistics by specifying STATISTICS=none.

STATISTICS

  NONE

  Do not import or recalculate the database optimizer statistics.

Then after the import you can gather statistics as needed.


Re-installing Oracle 19c on Windows 10: Error in Process: ORACLE_HOME\perl\bin\perl.exe. Error while executing ORACLE_HOME\rdbms\admin\dbmssml.sql

Never delete manually on Windows instead use deinstall.batfrom elevated command prompt from root drive C:\%ORACLE_HOME%\deinstall\deinstall.bat (don't initiate deinstall.bat from inside oracle home)
Remove ORACLE_HOME variable from system/user and ORACLE_HOME/bin from path .
If you removed previous installation and trying to install new database not sure why are you starting with DBCA instead of setup.exe.You mentioned you're trying to install PDB implies you already installed CDB i.e ORCLGLOBAL or am I missing something
Edit:-Deleting registry entries,oracle inventory,services and oracle home directory manually is dirty uninstall if not careful can lead to unintended consequences(current error might be or may not related to previous dirty uninstallation). Oracle provides clean uninstall tools in oracle home directory deinstall.bator gui setup.exe(OUI directory) in oracle home directory (you can check in unzipped folder for directories deinstall and 'oui`).
DBCA should be used only after post install to add new instance/database,new pluggable database or after installing software only.
Since you dirty uninstalled double click setup.exe in oracle home directory (check the image below).

Uninstall tools are shown in red rectangles boxes
Edit:-
On computer one you have conflict in environment from previous installation or leftovers .Verify from command prompt echo %ORACLE_HOME% and where sqlplus if either of the command returns remove those variables alternatively easy way is go to OUI folder as depicted in above picture and next go to bin directory and double click setup.Select all remove everything and go to environment tab select remove everything and start setup again.(I'm assuming you don't have any other Oracle product installations)


How do I get SQL Server agent to show up in SSMS for a non-sysadmin user?

There are several points to consider.

Make sure that person connecting to SQL Server 2012 is using SSMS 2012, I have seen compatibility issues when some features are not showing up when connecting with wrong version.
Remove all roles from the user and grant them again, go over these steps to make sure that you have done it correctly first time.

Script for creating account and assigning role:

USE [master]
CREATE LOGIN SQlSATest WITH PASSWORD = 'test', CHECK_EXPIRATION=OFF, CHECK_POLICY= OFF;
USE [msdb]
CREATE USER [SQLSATest] FROM LOGIN SQLSATest;
EXECUTE sp_addrolemember @rolename = 'SQLAgentOperatorRole', @membername = 'SQLSATest'


This will give you the Schema and Table names along with YES or NO and Creation date:

SELECT s.name As [Schema]
    , t.name as [Table]
    , CASE WHEN c.name IS NULL THEN 'NO' ELSE 'YES' END as 'Exists'
    , t.create_date
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.columns c ON c.object_id = t.object_id AND c.name = 'test'

How the master table works is unclear but it seems that adding such a WHERE clause should work:

WHERE t.name IN (
    SELECT tn.name + CAST(mn.id as varchar(10)) as name
    FROM (values('Logs'), ('Details'), ('Data')) as tn(name)
    CROSS JOIN [master] mn
)

This will mix the list of name with the list of ids from master. Names can be added to the list or it could be replaced by a Names table.

By the way, master is not a proper name for a table since it is also a system database name.

This query adds productname from sample @master table for id < 5:

declare @master table (id int, productname varchar(10))
insert into @master(id, productname) values(1, 'xxx'), (2, 'yyy'), (6, 'yyy')

SELECT s.name As [Schema]
    , t.name as [Table]
    , CASE WHEN c.name IS NULL THEN 'NO' ELSE 'YES' END as 'Exists'
    , t.create_date
    , m.productname
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN (
    SELECT name = tn.name + CAST(mn.id as varchar(10))
        , mn.productname
    FROM (values('Logs'), ('Details'), ('Data')) as tn(name)
    CROSS JOIN @master mn
    WHERE mn.id < 5
) m ON m.name = t.name
LEFT JOIN sys.columns c ON c.object_id = t.object_id AND c.name = 'test'


Do I need to perform any tasks after Postgres goes into recovery mode?

May I suggest upgrading to PostgreSQL 9.3 (freshly out!)? Some write that the new Streaming-Only Remastering capability turns a replica in a set of replicas into the new master for all of the other replicas. Apparently, it's even more useful in combination with the cascading replication you are using in 9.2

In PostgreSQL 9.2, cascading replication really requires file-based WAL archiving to work in case of disaster recovery. PostgreSQL 9.3 will not require that: you can set up large replication clusters of whatever length you want without any WAL archiving.

More information:

What's new in PostgreSQL 9.3
Cascading Replication and Cycles


If you are only inserting into the table and old rows are not deleted or updated and you don't insert rows for old dates, I don't see any harm at all from materializing the query results in another table. Every day, you could be aggregating the previous day's results for each member. Index properly the new table and efficiency should be good.

These are not duplicated data, they are summary results. Since efficiency of calculating them on the spot is not good enough, you are justified to pre-calculate and store
         them in a summary table.

Only if the members.zone changes, you would have to recalculate the results for that member.


Query performance of index scans slower than parallel seq scan on Postgres

All your time is spent doing table I/O, which is to be expected in a test with a cold cache.

The index scan takes longer, but only because it is not parallelized. The sequential scan uses more total time, but since it is run on 5 cores in parallel, it finishes faster.

PostgreSQL v10 can perform parallel index scans, so the remaining riddle is why it doesn't. A side riddle is why the sequential scan uses so many parallel workers.

I suspect that you messed up the configuration parameters. The relevant parameters are:

max_parallel_workers_per_gather: limits how many workers can be used. You must have set this to 4 or more.
min_parallel_table_scan_size: if a table is bigger than that, a parallel worker is planned. If the table size exceeds 3n-1 times that value, n parallel workers are planned. So either your table is very big, or you reduced the parameter. Alternatively:
The storage parameter parallel_workers on the table overrides the calculation based on min_parallel_table_scan_size as described above, so maybe you set that.
Finally, min_parallel_index_scan_size governs when a parallel index scan is considered. Either the index is small, or you lowered the parameter.

I'd expect that the index scan will be faster once you set these parameters to good values.


I would definitely go with a hash of the url and make the hash a unique index. A hash has a fixed length, so you can use CHAR to specify the length of the column, which grants a slight performance boost over VARCHAR or TEXT.

But might I suggest using INSERT IGNORE instead of making two calls to the database? Something like:

INSERT IGNORE INTO urlTable VALUES ('urlHash');

This has the benefit of ignoring any duplicate errors that might arise from attempting to insert a duplicate hash, without first having to do a SELECT COUNT(*) query.


UPDATE a table with just ~2000 rows is slow, would LOCK help?

I hope you are using InnoDB.
Was that the entire query?  So, you are changing all rows of a 2K-row table?
Why?  Might there be another way to design the schema?  Please describe the task further.  There are several things that come to mind -- and they won't involve such a sluggish action.
How big is the table?  Is that 2000 rows out of a million?  Or 2000 out of 3000.
The query must save the old value of 2000 rows, update 2000 rows, update 2000 rows in the index.  Then later it must toss the saved old row copies.
How big is innodb_buffer_pool_size?
If it is a single statement, there is already a LOCK.  So don't bark of that tree.


Accessing an external database through pgAdmin installed on my local machine?

The short answer is choose Add server from the file menu and enter the details of the server. However there are many reason's why it may not work. I'll itemize the most common problems:

postgresl isn't listening
postgresl's pg_hba.conf doesn't allow access from outside
the server's firewall is blocking access
a router is blocking access

If you run into problems, update your question and please include any error messages you get.

BTW When opening up access to postgres from the outside you may want to enforce the use of SSL.


How (and why) does TOP impact an execution plan?

I would have guessed that when a query includes TOP n the database
  engine would run the query ignoring the the TOP clause, and then at
  the end just shrink that result set down to the n number of rows that
  was requested. The graphical execution plan seems to indicate this is
  the case -- TOP is the "last" step. But it appears there is more going
  on.

The way the above is phrased makes me think you may have an incorrect mental picture of how a query executes.  An operator in a query plan is not a step (where the full result set of a previous step is evaluated by the next one.

SQL Server uses a pipelined execution model, where each operator exposes methods like Init(), GetRow(), and Close().  As the GetRow() name suggests, an operator produces one row at a time on demand (as required by its parent operator).  This is documented in the Books Online Logical and Physical Operators reference, with more detail in my blog post Why Query Plans Run Backwards.  This row-at-a-time model is essential in forming a sound intuition for query execution.

  My question is, how (and why) does a TOP n clause impact the execution
  plan of a query?

Some logical operations like TOP, semi joins and the FAST n query hint affect the way the query optimizer costs execution plan alternatives.  The basic idea is that one possible plan shape might return the first n rows more quickly than a different plan that was optimized to return all rows.

For example, indexed nested loops join is often the fastest way to return a small number of rows, though hash or merge join with scans might be more efficient on larger sets.  The way the query optimizer reasons about these choices is by setting a Row Goal at a particular point in the logical tree of operations.

A row goal modifies the way query plan alternatives are costed.  The essence of it is that the optimizer starts by costing each operator as if the full result set were required, sets a row goal at the appropriate point, and then works back down the plan tree estimating the number of rows it expects to need to examine to meet the row goal.

For example, a logical TOP(10) sets a row goal of 10 at a particular point in the logical query tree.  The costs of operators leading up to the row goal are modified to estimate how many rows they need to produce to meet the row goal.  This calculation can become complex, so it is easier to understand all this with a fully worked example and annotated execution plans.  Row goals can affect more than the choice of join type or whether seeks and lookups are preferred to scans.  More details on that here.

As always, an execution plan selected on the basis of a row goal is subject to the optimizer's reasoning abilities and the quality of information provided to it.  Not every plan with a row goal will produce the required number of rows faster in practice, but according to the costing model it will.

Where a row goal plan proves not to be faster, there are usually ways to modify the query or provide better information to the optimizer such that the naturally selected plan is best.  Which option is appropriate in your case depends on the details of course.  The row goal feature is generally very effective (though there is a bug to watch out for when used in parallel execution plans).

Your particular query and plan may not be suitable for detailed analysis here (by all means provide an actual execution plan if you wish) but hopefully the ideas outlined here will allow you to make forward progress.


The answer of eoinbrazil is partly incorrect. A new Node can be in STARTUP2 for a long time. The link the posted says:

  Each member of a replica set enters the STARTUP2 state as soon as mongod finishes loading that member’s configuration, at which time it becomes an active member of the replica set. The member then decides whether or not to undertake an initial sync. If a member begins an initial sync, the member remains in STARTUP2 until all data is copied and all indexes are built. Afterwards, the member transitions to RECOVERING.

I'm administering a 700 GB collection and, when I add a new node the STARTUP2 state remains well over 24 hours. But you can still see if there is something happening, by watching if the database grows. You can see the size of the database on the new node with

show databases

or you can also observe the data directory, to see if it is still growing. (on linux with the commands ls, df, du, iotop, etc ....)


You can have organisation, user, and location all have a reference to a common table representing something that can be an owner to a dog. Then dog can have a single reference to this table. Here is a diagram explaining.

dog.owner_id references can_own_dog.can_own_dog_id, in case that isn't clear. Each of the can_own_dog foreign keys is unique in their table, ideally  unique across all three tables.

I'm not sure if you're familiar with object-oriented programming, but the idea is similar to multiple classes implementing a single interface.

On another note, your diagram says "Users can have multiple locations", and "Dogs can have multiple locations". I think these mean to say "Locations can have many users" and "Locations can have many dogs"?


MAXDOP = 1, Query Hints and Cost Threshold For Parallelism

If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually go parallel?

Simple answer: yes.

Details

There are a couple of separate things going on here, which it is important to separate:

What is the effective maximum degree of parallelism available to a query?

The contributors to this are (broadly in order of importance):

Resource Governor MAX_DOP setting
Query hint MAXDOP setting
The max degree of parallelism instance configuration option

The details are explained in Server’s “Max Degree of Parallelism” setting, Resource Governor’s MAX_DOP and query hint MAXDOP–which one should SQL Server use? by Jack Li, Senior Escalation Engineer for Microsoft SQL Server Customer Service and Support. The table below is reproduced from that link:

Will a query plan use parallelism?

The SQL Server query optimizer always finds a serial plan first*.

Then, if:

Further optimization is justified; and
The cost of the best serial plan exceeds the cost threshold for parallelism configuration value

...the optimizer will try to find a parallel plan.

Then, if:

A parallel plan is found (i.e. is possible); and
The cost of the parallel plan is less than the best serial plan

...a parallel plan will be produced.

Note: the cost threshold for parallelism only affects whether the optimizer looks for a parallel plan. Once a parallel plan is cached, it will execute using parallelism when it is reused (so long as threads are available) regardless of the CTFP setting.

Examples

For both examples, with instance maxdop 1 and query hint maxdop 2, the effective available DOP is 2. If a parallel plan is chosen, it will use DOP 2.

Example 1

Given CTFP of 50 and a cheapest serial plan found cost of 30, SQL Server will not try to find a parallel plan. A serial plan will be produced.

Example 2

Given CTFP of 50 and a cheapest serial plan found cost of 70, SQL Server will try to find a parallel plan. If this plan (if found) has a cost less than 70 (the serial plan cost) then a parallel plan will be produced.

The end result of query optimization is always a single cached plan: serial or parallel. The optimizer finds only a serial plan in search0 (TP) and search1 (QP) phases.

It may then (as described) re-run search1 with a requirement to produce a parallel plan. A choice is then made between serial and parallel based on best whole plan cost so far. That choice is binding in case optimization moves on to search2 (Full Optimization). Each phase of optimization considers many alternatives, but the output from a stage is always a single best plan, which is either serial or parallel.

I wrote about some of this in Myth: SQL Server Caches a Serial Plan with every Parallel Plan


How to make mysqldump use /*!40101 SET character_set_client = utf8mf4 */;?

That special type of comment says "leave this as a comment if running a version older than 4.1.1; else execute it".

utf8mb4 came in in 5.5.3, so the 'correct' version would be

/*!50503 SET character_set_client = utf8mb4*/

If your version of mysqldump and mysql are newer than 5.5.3, no harm is done by leaving it 40101.  If you do try to load it on a mysql older than 5.5.3, the SET will probably complain.

As for why mysqldump does not say 50503 -- I suspect that is a bug.  File it at http://bugs.mysql.com.


Creating non-clustered index on large table fills transaction logs

As far as I know the fact that the underlying table is a columnstore index isn't too relevant here. The linked documentation that you included in the question already gives you the standard fixes for your problem. Below is a list of possible fixes rated in

Increase your log file size. This will help you in the future if you ever need to drop and recreate the index for some reason.
Set the SORT_IN_TEMPDB option to ON. I didn't know this, but that option can help in cases when you have enough free space in tempdb.
Build the index with page compression. This can reduce the amount of data written to the transaction log, but that varchar(4000) column could cause issues.
Create a copy of the table's structure with the indexes that you need and insert data into it in batches. This can be difficult to pull off in production and will overall be inefficient but you can split up your index build into separate transactions this way. If something goes wrong just drop the temporary copy of the table that you made.
If the table is partitioned, you might be able to use partition switching to build the nonclustered index one partition at a time.
Temporarily change your recovery model to bulk-logged. This can cause all sorts of issues so make sure that you fully understand all of the ramifications of doing this.


select * from
(
select SubBrand,Month,Saless,Variable
 from
(select * from DataTable
)as p
unpivot (Saless for Month in(January,February)) as u
) as p
pivot
(
min(Saless) for  Variable in ([Sales],[Promotion])
)as PP
order by SubBrand,Month


SSIS ETL or CDC - real time DWH reporting

Have you considered transactional replication? From what I see that would be the best option with the less administrative overhead.

That way you will have the DB in practically real time and can be relatively easily modified to include more tables in the future.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-2017


If a query triggers a statistics update and times out are the statistics still updated?

That was actually my question at AskSSC. I should have just tested it myself as I accepted an incorrect answer.

With the following test table

CREATE TABLE StatsTest
(
a varchar(max),
b varchar(max)
)

DECLARE @VCM VARCHAR(MAX) = 'A'

INSERT INTO StatsTest
SELECT TOP 20000
       REPLICATE(@VCM,10000),
       REPLICATE(@VCM,10000)
FROM master..spt_values v1,  master..spt_values v2

And the following test code

         SqlConnection connection = new SqlConnection(...);
            connection.Open();

            SqlCommand command = connection.CreateCommand();
            command.CommandTimeout = 12;
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT COUNT(*)
FROM StatsTest
WHERE
      a LIKE '%foo%' OR
      b LIKE '%foo%' ";
            command.ExecuteScalar();

Profiler shows the following

First it creates the stats for column b successfully (the initial SP:StmtStarting /SP:StmtCompleted pair) . Then it starts creating the stats for column a (The selected SP:StmtStarting entry in the screen shot). This entry is followed by an AUTOSTATS event confirming that the statistics on b were created then the timeout kicks in.

It can be seen that the stats creation occurs on the same spid as the query and so this also aborts the creation of stats on column a. At the end of the process only one set of stats exists on the table.

Edit

The above refers to stats creation, to test auto update of the stats I ran the above query without a timeout so both sets of stats were successfully created then updated all columns of all rows so that the stats would be out of date and re-ran the test. The trace for that is pretty similar

Finally just for completeness with SET AUTO_UPDATE_STATISTICS_ASYNC ON the trace looks as follows. It can be seen that system spids are used to perform the operation and they are unaffected by the query timeout as would be expected.


I have a table in mysql, in which searching, insertion, deletion and updation is done quite frequently and I have read that indexes affection insertion and updation performance. so should I use Indexes for faster searches?

Yes, there is an overhead in having indexes when changing data.  As well as updating the base data table, the DBMS also has to update the index structures as well.  That overhead, however, is small.  Remember the all changes are [quickly] written to memory first and only flushed [slowly] to disk some time later.

Indexes exist primarily to improve query performance and can do so by Orders of Magnitude.  Imagine retrieving a single record by its unique ID:

If that field is indexed then the DBMS can quickly skip through the index [blocks], then "hop" across to the data page to get any additional fields.
Without the index the DBMS has to scan each and every record in the table.  If that table has millions of records, that's going to be really, really slow.  Even with a "limit 1" clause, telling the DBMS to stop at the first match, you'll still be reading half the table, on average.

So Indexes are a trade-off.  They can make you [huge] gains on the querying front, while making [small] losses on the update front.  The only person who can definitively tell you whether it's worth your while to index your fields is you, based on your database and your Application.  Test that Application with and without indexes and compare the results.

That said, I strongly suspect you'll come back in favour of using them :-)


Huge Size (still) with Differential Backup

That sounds perfectly expected. If you are updating most of your data and rebuilding all of your indexes: all the affected extents need to be in the differential backup, there is simply no way around that because it must contain every extent that has changed since the last full or differential backup.

If you are not already doing so add the COMPRESSION option to your backups, which can result in considerable savings (and speed up the backup process too by reducing IO).

Also if the size really is a massive issue, this might be one of the few occasions to question if a backup is needed particularly regularly. If you are regenerating all of the data in your warehouse database every day from other sources then you might not need to worry as much about backing it up as often as you do the source data, assuming of course that the source databases have good backup taking and testing processes in place, because you can reconstruct that data from the other sources (or backups thereof) anyway if disaster strikes. Of course this reconstruction process could be significantly slower than restoring a backup, so consult you users and your service restore time SLAs before changing the warehouse's backup plan.

Possible Remediation:

Two possible options to reduce the amount of data that you need to transfer spring to mind:

After each update, make a copy of the data without any non-clustered indexes (backup, restore locally, drop all non-clustered indexes, backup again and transfer this version) and recreate the indexes after restoring at the other end. This avoids transferring all the index data.
Make sure the daily changes are as minimal as possible by only INSERTing new rows and UPDATEing those that need it (leaving identical rows untouched), not rebuilding any indexes (this will mean not disabling them so may slow down the ETL process).
Depending on the complexity of your ETL process refactoring it this way may be a massive job. One way around this may be to add an extra stage to the end of the process: keep a second copy of the warehouse, rebuild the first using the normal process, then update the second using one MERGE statement per table to INSERT/UPDATE/DELETE as needed being very careful to leave any unchanged rows alone. Then take your differential backups from this DB. Keep minimal indexes in the primary instance of the copy, just keep the primary keys and clustered indexes (which you will want for efficiency of the MERGE operations) and rebuild the rest after restore.
This reduces the amount of no-op changes, so will hopefully markedly reduce the size of each differential, and reduce space in the backups devoted to index data.

Both these methods try to reduce the backup size that you are transferring at the expense of adding a fair chunk of extra processing at the receiving end (rebuilding indexes after restore) and sending end (maintaining the second copy at that location) so you are trading one bottleneck for another pair which may or may not be practical.

Depending on your data sources and the complexity of the ETL process you may have better luck with building the warehouse DB at both locations each time, as Chris suggested in the comments.


Why would a long running query remain sleeping and with null wait_info

I'll agree with @Ian for the most part.  Let me expand, though.

It is probably IIS keeping the connection open, but it is by design due to connection pooling, which is almost always used by default these days.

The other point is that the query was not running for over an hour.  Pay very close attention to the awesome documentation that Adam includes in sp_WhoIsActive. Run it with @help =1. On the time attribute you are using to measure run time, Adam writes this:

  For an active request, time the query has been running

  For a sleeping session, time since the last batch completed

And for the sql_text attribute, Adam writes:

  Shows the SQL text for active requests or the last statement
  executed for sleeping sessions, if available in either case.

What's actually happening here is the query completes very quickly and the connection is released back to the pool (in 'sleeping' state) to be picked up when it is demanded again.  The results in your table reflect that the connection that last ran the query is simply going unused for a long period of time. So, nothing to worry about, everything is functioning as designed.

However, you may want to modify your Agent job to run sp_WhoIsActive with @show_sleeping_spids = 1, which will only show sleeping spids if they have an active transaction, so that you don't see these pooled connections anymore.

UPDATE:

In looking at the screenshot again I see you actually do have an open transaction maintained on that spid.  It doesn't really affect my answer, other than the last paragraph, but you do need to find out why that transaction is being held.  Do you have implicit transactions turned on?  Are you failing to commit an explicit transaction in the same batch?


Is it better to use an ID (int) or a Name (varchar) as PK for a table?

As usual, it depends. Let's first consider what a key is, it is something that uniquely identifies something in your business. Even if you choose to use a surrogate key for some reason you should still identify and implement the natural candidate key's.

A key ideally has a number of properties

unique
stable  -- should not change frequently, not necessarily immutable though
irreducible  -- no subset of the attributes qualify as a key
simple  -- few attributes
familiar  -- meaningful to the user

Quite often there is a conflict between these properties and some kind of compromise has to be made. A natural key - such as name in your case - may not be stable enough and therefor is a bad choice for a key. A surrogate key on the other hand is not familiar to the user.

To conclude, there is no easy answer to your question, it depends on the situation. Design your keys, and make the best out of it. I would not recommend blindly choosing one approach over the other.


Nested select: insert values into table with two subqueries


Can I use OUTPUT from an UPDATE in an INSERT?

Here is an example of using OUTPUT...INTO

--demo setup
drop table if exists InsertTest;
Create Table InsertTest (id int);

drop table if exists UpdateTest;
Create Table UpdateTest (id int);
insert into UpdateTest(id) values(1),(2);

--solution
UPDATE UpdateTest
SET id = id + 1
OUTPUT INSERTED.id
INTO InsertTest;

--verify results of insert
select * from InsertTest

ID
2
3


How to measure the number of database writes on SQL Server 2008 R2

I would look at using extended events. Truthfully I have not worekd with these and it's one of those things I have not sat down and tried to learn...yet. So I cannot give you an exact example of how you would impliment with your example.

The advantage to these over trace files is no performance hit. They were specifically designed with performance in mind. So letting it collect information over a long period should not be a problem. You can check out using it for monitoring system activity here. Jonathan Keyhayias did a good month-long series on using extended events. The first day gives a good overview of them here.

He also created a SSMS add-in that makes it a little easier to work with the Extended Events sessions.


As @Scott Hodgin has pointed out on his comments there is some NULL value for person_id in the documents table.

You can easily avoid it by adding where person_id is not null

create table persons (personID int);
create table documents (documentID int, personID int);

insert into persons values(10),(11),(12),(13),(14),(15);
insert into documents values (1,10),(1,11),(1,null),(2,null),(3,11),(3,15);
GO

12 rows affected

select * from persons where personID in (12,13,14);
GO

| personID |
| -------: |
|       12 |
|       13 |
|       14 |

select * from documents where personID in (12,13,14);
GO

documentID | personID
---------: | -------:

select personID from documents
GO

| personID |
| -------: |
|       10 |
|       11 |
|     null |
|     null |
|       11 |
|       15 |

select *
from   persons
where  personID not in (select personID from documents);
GO

| personID |
| -------: |

select *
from   persons
where  personID not in (select personID from documents where personID is not null);
GO

| personID |
| -------: |
|       12 |
|       13 |
|       14 |

dbfiddle here


Converting VARCHAR to TIMESTAMP with milleseconds yields "ERROR: date/time field value out of range"

Handling of date and timestamp input has been made stricter in Postgres 10. Quoting the release notes:

  Make to_timestamp() and to_date() reject out-of-range input
  fields (Artur Zakirov)

  For example, previously to_date('2009-06-40','YYYY-MM-DD') was
  accepted and returned 2009-07-10. It will now generate an error.

Explains what you observed. You'll need to sanitize your input.

Here is the thread where these changes were discussed.


MySQL 5.6 @ AWS RDS read IOPS plateaus lower than write IOPS

Reads are cached.  If your data is smaller than the buffer_pool, everything will be read once, then read IOPs will drop to virtually zero.

There are many writes -- write the data, update the index, the double-buffer, the undo log, the binlog, etc.  Still, there is some caching going on there.  See the "change buffer".

So, I am surprised that the Read IOPs are so high.  And I can't explain its flatness.  Please describe what your app does and how big the data is.

UUIDs

So, the problem stems from having the PRIMARY KEY(id) being a 'random' value, while doing reads in somewhat chronological order, or by station_id.

First, let's try to get rid of id.  Is there another combination of columns that is unique?  If so, it might be a better PK.

Probably, this is the best PK:

PRIMARY KEY(station_id, created_at  -- in this order
            , id   -- include only if needed for uniqueness
           ),
INDEX(created_at) -- for finding today's set of rows for analytics?

I assume there are only hundreds, maybe a few thousand 'stations'?

With PK(id), each INSERT is hitting a random spot in the table.  Given that the buffer_pool is only 10% of the size of the table, this means that 90% of the time there will be a cache miss.  Ditto for SELECTing.

With PK(station_id, created_at), there will be hundreds, maybe thousands of "hot spots", so the INSERTs will be more easily cached.  And even the reads, which need INDEX(created_at) will bounce around only those few spots, not bounce around the entire table.

If a block (16KB in InnoDB) contains 100 rows (a convenient Rule of Thumb), then the inserts and selects discussed so far will be inherently 90 times as fast as before.  (OK, it might be only 5-10 times as fast, but that is still  very good, correct?)

(I discuss the UUID problem here, but the cure in it depends on Type 1; you are using Type 4.  Instead, we cna take advantage of created_at.)

Another issue... I see several long strings in that table.  Do those columns contain the same strings over and over?  If so, you should seriously consider normalizing them.  Then you could replace VARCHARs with, say, 2-byte SMALLINT UNSIGNED (for up to 65K distinct values).  Shrinking the table will help performance, especially when it is bigger than the buffer_pool.

I see several redundant indexes; drop the extras.

INDEX(a), INDEX(a)  -- drop one of them
INDEX(a), INDEX(a,b)  -- drop (a), since the other one can handle its needs

Use NOT NULL when appropriate.

INT takes 4 bytes; use smaller numeric types where appropriate.


SQL Server db user mapped to windows login not in SQL Server logins

The only way a login has rights to a server is if those rights have been granted.  The question is likely:  How did these rights get granted?

If DOMAIN\USER_A is a member of some Windows group, then through the rights granted to the group the DOMAIN\USER_A login exists in the server.  This is true even if there is no individual entry in sys.database_principals or sys.server_principals for that login.

You can look for the permission paths used by a login as follows:

EXEC xp_logininfo 'DOMAIN\USER_A', 'all'

You might find several permission paths for a login.


Will I benefit from SQL Server 2014 In-Memory tables if my tables are already fully cached in-memory?

Some people in our team want to move to SQL Server 2014 to increase performance with In-Memory Tables ...

That entirely depends on:

If more performance is needed
What the current bottleneck is

  however I'm not convinced that doing such will increase performance since our tables are already in memory.

You should be suspect that it may or may not improve performance, however the reason isn't because the data is already in memory. It is mainly due to being lock and latch free and less code execution with native compilation.

Thus, if your bottleneck isn't around locks or latches... the actual gain you receive will be minimal to none. This can be exacerbated if the actual bottleneck is the client.

There is a great article about use cases for IMOLTP, and while we are at it you may want to look at 2016 and the uses of IMOLTP for advanced real time analytics.

  Is there more to the new In-Memory Tables in SQL Server 2014, or in my scenario there is indeed little to gain?

In-Memory objects (in this case, tables) are not just pinning data into memory. They have entirely different memory structures and are lock and latch free.

There are additional differences in how the data is stored, how it affects database startup, etc.


Does SQL92's REPEATABLE-READ preclude Write Skew (A5B)?

Does REPEATABLE READ preclude Write Skew?

Yes - if you buy into the Critique's view that the ANSI definitions were based on locking behaviours, for example:

  Remark 6. The locking isolation levels of Table 2 and the phenomenological definitions of Table 3 are equivalent. Put another way, PO, PI, P2, and P3 are disguised redefinitions of Locking behavior.

From that viewpoint, the history:

r1[x]...r2[y]...w1[y]...w2[x]...(c1 and c2 occur)

...is prevented when long-duration read shared locks are taken, because w1[y] is blocked by the lock taken by r2[y]. Similarly, w2[x] would be blocked by the lock taken by r1[x]. Therefore, this history cannot arise.

No - if you literally interpret the ANSI definitions. These are widely seen to be incomplete, so this outcome is not really all that surprising.


When you have a database with that many tables, what must you remember ???

Use --single-transaction with --no-create-info : This will produce a dump of all table data with the same point-in-time
Setup Replication : You can run STOP SLAVE; and then run your mysqldump, assured that no other activity in going on in the Slave. When dump is finished, that run START SLAVE;.
Don't do any DDL during the dump : Running DDL while dumping from a MySQL Instance can produce some spurious actions (dump reload not completing, wrong structure of table prior to data reload, and things like these)

If your database is all-MyISAM (you start hearing Twilight Zone music) : Don't mysqldump. Shutdown mysql and maker a copy of /var/lib/mysql (LVM Snapshot, scp, rsync, etc). That way, there is no SQL controversy.

You should probably dump data and table structure together, just to be sure.


Preserve capitalization of column names/keys returned by SYS_REFCURSOR in Oracle

Too long for a comment.
When I did a
declare
cursor_number number;
description_table dbms_sql.desc_tab;
l_cursor SYS_REFCURSOR;
column_count number;
begin
OPEN l_cursor for SELECT dummy as "Dummy" from dual;
cursor_number:=dbms_sql.TO_CURSOR_NUMBER(l_cursor);
dbms_sql.describe_columns(cursor_number,column_count,description_table);
for k in 1..column_count loop
    dbms_output.put_line(description_table(k).col_name);
    end loop;
end;
/

the ouput is
Dummy

as expected. So SYS_REFCURSOR behaves correctly.
Also
create table productnew (productid  number)
/
declare
cursor_number number;
description_table dbms_sql.desc_tab;
l_cursor SYS_REFCURSOR;
column_count number;
begin
OPEN l_cursor for SELECT p.productid as "ProductID" from productnew p;
cursor_number:=dbms_sql.TO_CURSOR_NUMBER(l_cursor);
dbms_sql.describe_columns(cursor_number,column_count,description_table);
for k in 1..column_count loop
    dbms_output.put_line(description_table(k).col_name);
    end loop;
end;
/

returns
ProductID

So the problem isn't at the database.


Pivoting data for stacked charts (grouping and creating new columns for each distinct value)?

Another way to do this if the columns are known is using an aggregate and a CASE statement:

SELECT d,
  sum(case when loc = 'Baltimore' then v else 0 end) as Baltimore,
  sum(case when loc = 'Houston' then v else 0 end) as Houston,
  sum(case when loc = 'Chicago' then v else 0 end) as Chicago
FROM test
group by d
order by d;

See SQL Fiddle with Demo

If you want to do this dynamically, then you can create a procedure to generate the SQL statement:

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select d ';

    begin
        for x in (select distinct loc from test order by 1)
        loop
            sql_query := sql_query ||
                ' , sum(case when loc = '''||x.loc||''' then v end) as '||x.loc;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from test group by d order by 1';

        open p_cursor for sql_query;
    end;
/

Then to call it:

variable x refcursor
exec dynamic_pivot(:x)
print x

the result is:

D                                BALTIMORE    CHICAGO    HOUSTON
------------------------------- ---------- ---------- ----------
01-JAN-12 12.00.00.000000 AM            36         55         36
01-FEB-12 12.00.00.000000 AM            49         51         58
01-MAR-12 12.00.00.000000 AM            11         18         13


mongodump sharded collection size is smaller after restoring

If the cluster is healthy, there are no documents with same sharding key at different shards. If there is "duplicates", those are "orphans" what should be cleaned from cluster.

Here more information about orphans and how to remove those.


Is it possible to support multiple snapshots / store a snapshot / restore from a snapshot to a state with another snapshot?

This can't be done, the snapshots are not 'tiered' such that snapshot 2 can revert back to the snapshot 1 state.

In SQL Server, database snapshots preserve the original state of each page only when it is changed in the source database. Snapshot 2 is not preserving changed pages from snapshot 1 but from the original source database.

The documentation highlights this in step 2 under "How to Revert a Database to a Database Snapshot".

Your best bet would be to copy the original database, then take a snapshot of the copied database. Run your "snapshot 2" tests against the snapshot taken of the copied database. You can then easily revert the snapshot for multiple tests and return to your pre-test state and when you're finished, simply drop the copied database. In this fashion, your original database will be unaffected by your testing.