Is this ok ?

The db_datareader role gives implicit access to SELECT against all tables and views in a database.

If that's ok, then it's ok

You might be interested in the information in this post about db_datareader

You can conditionally check to see if the user already exists

use myDB
GO

if not exists(select * from sys.database_principals where name = 'foo')
-- create your database user

When restoring or attaching databases where the user already exists, you might have to 'remap' any Sql logins to the restored database.  This is what I use as part of normal 'refresh' scripts

use <YourDatabase>
go
set nocount on
declare @cmd varchar(max)=''
select
    @Cmd=@cmd + 'ALTER USER ' + dp.name + ' WITH LOGIN = ' + dp.name + ';' + char(10)
from
    sys.database_principals dp left join
    master.sys.server_principals sp on
        sp.name=dp.name
where
    dp.type='s' and
    dp.default_schema_name<>dp.name and
    sp.name is not null
exec (@cmd)


Use an indexed view instead:

CREATE VIEW AfricanRatingsAVG
WITH SCHEMABINDING
AS
SELECT RecipeID,
       COUNT_BIG(*) AS cnt,
       AVG(Rating) as rating
FROM   dbo.AfricanRatings
GROUP  BY RecipeID;
GO

CREATE UNIQUE CLUSTERED INDEX cdxAfricanRatingsAVG on AfricanRatingsAVG(RecipeID);

The engine will maintain the cnt and avg for you. Use the NOEXPAND hint when querying the view:

  When NOEXPAND is specified for a view, the query optimizer considers
  using any indexes defined on the view.


MySQL can't import a custom library for udf

The issue has been solved by:

removing the -c option from the gcc command line and adding the -fPIC option at the end, turning the command line into:

gcc -Wall -I/usr/local/include -shared -o libcompress_sequence.so compress_sequence.c -fPIC

The -c option removed the linking, which is not wished here since the library is compiled in one step. So removing the -c option tells to gcc to make the linking with the libraries included in the C file. But then gcc didn't compile anymore, and complained about the option -fPIC missing. I added it and gcc compiled.

Then MySQL complained about not being able to detect the function COMPRESS_PROTEIN_SEQUENCE in my library. So I put it in small letters: compress_protein_sequence, as the function is named into my C code, then MySQL imported the library. The actual MySQL command is the following:

CREATE FUNCTION compress_protein_sequence RETURNS STRING SONAME 'libcompress_sequence.so';

I am now able to use the MySQL function COMPRESS_PROTEIN_SEQUENCE.


SQL Server 2016 Database Backup and Restore

Note: to get the best help on this question, please include your actual RESTORE statement, and the specific error message that you're getting.

When using In-Memory OLTP, SQL Server has to create a new folder named "xtp" in the root of the default file location for the SQL Server instance.  This folder contains the DLLs for compiled stored procedures and other in-memory objects.  You can find more details about that here:

In-Memory OLTP files –what are they and how can I relocate them?

If you've changed the location for your data files, you may need to update SQL Server's access to the file system there:

Configure File System Permissions for Database Engine Access

As a test / workaround, you could manually create the "xtp" folder, and then try the restore again.


Postgresql: very slow results with "group by"

In my hands this query, on a simulated data set, is ten times faster than you observed, even on pretty crummy hardware. It has the same EXPLAIN plan and similar estimated costs, just much faster execution.  So either something very strange is going on, or your server is seriously overloaded.  Your best bet for investigating that might be outside of PostgreSQL, for example using top, perf, vmstat, etc.

As far as an index, the one best suited for this query would be:

create index on reports (run_id , detection_status, bug_id);

This allows it to use an index only scan to complete the query, and in my hands is 4 to 5 times faster.  But I don't know how that would translate to your server, as it seems to have an undiagnosed performance pathology.  Also, you need to keep reports well vacuumed for this to be effective, otherwise the index-only nature of the scan loses its benefit.  You might need to modify the tables autovac parameters to get it to be vacuumed enough, or set up your own manual vacuum scripts.


TL;DR; The transaction log contains all information necessary to re-create or undo each transaction, depending on whether it contains a commit record for that transaction or not.

In SQL databases transaction atomicity is implemented most frequently using write-ahead logging (meaning that the transaction log entries are written before the actual tables and indexes are updated).

Queries in the strict sense of the word, that is, SELECT statements and other read operations that do not change the database state, are not logged as there is nothing to commit or roll back, and as such the concept of atomicity does not really apply here, at least at the DBMS level. The application consuming query results might need to enforce atomicity by discarding results of previous queries if one of the subsequent queries fail and cause the rollback.

When DML statements (INSERT, UPDATE, DELETE, MERGE) and other statements that change the database state get executed, the changes they make are first written into the write-ahead log (WAL). If all statements succeeds and a commit (explicit or implicit) is issued, this fact is also recorded in the WAL, and the log is persisted. This makes the changes durable. They may be written out to the actual table and index segments at some later time, during a checkpoint or log replay.

However, if one of the statements fails, there would be no commit record in the log for this transaction, and the DBMS will undo table and index changes made so far using the preceding log records of that transaction.

Similarly, if the DBMS process crashes, or the power goes out, there won't be a commit record for the in-flight transaction either, and the table or index changes that may have already been persisted will be undone during log replay.


How to Handle Database for Application which Starts from Scratch Annually

Honestly, I'd go with #1 with a twist in all but a rare circumstance.  The rare circumstance that would make me go with separate databases (or, more likely, one database with separate schemas) is if there are fundamental data type changes between years, and the data structure changes so often and completely that it really is a new database altogether.  That's probably not the case, though, because you're talking about a single application.  And because it's a single app, there's probably some common reference data that will remain static (or slowly-changing) through the years, and probably some functionality that they'll want, like the ability to replicate old users (so they don't have to keep typing in the values each year).

So instead, I'd go with one database, one set of tables, and an indicator telling to which year that data belongs.  Your example of a User seems a little odd to me, though.  If you're talking about application users, I'd only put them in once and if you need to break it down by year, have a User-Access table that has your user ID and the year as the two columns.  Then, your application knows only to allow that user access to the relevant year(s), without having to repeat data.

Here's the twist I mentioned above:  in addition to marking yearly data with a year indicator, you might have a second schema for archived data.  Partition each of the relevant tables by year and, as the year ends and you're getting ready for the next year, roll them off into the Archive schema.  The data would remain for historical and reporting purposes, while leaving your application free to handle the current year as though nothing had happened.

Archiving would be optional in this scenario, however.  If you have a lot of data and people rarely go back to look at it, it might make sense to move those off to archive tables, but even if not, you could still stick the old data on slower disks and with table partitioning by year, rarely touch those partitions even if they are in the same table.


This is not a technical problem, it's a contract management problem.  Don't frig with the turnkey application.  It gives the vendor a get-out-of-jail-free card that allows them to ignore their service level agreement.

Either,

Throw hardware at the problem (SSDs, bigger server, more RAM etc.), or
Talk to whomever manages the relationship with your vendor.  Push the responsibility for system performance back on to the vendor, and/or
Investigate the business case for moving to a competitor's product.

Messing with the application is the last thing you should do, particularly if relations with the vendor are getting tense.

You need to make sure your management knows that the performance issue is down to the architecture of the system.  Get that in writing somehow; write up a report describing the issue and the reasons you can't fix it unilaterally.  Make sure it's emailed to all of your internal stakeholders and you have a record of it being sent.  Arrange a meeting to follow that up and explain it.  That covers your arse - this is quite important if the vendor (or the party that authorised the purchase) tries to get political.

Now, escalate the issue with your I.T. management function, making sure it's well documented.  As you understand the root cause of the problem, this shouldn't be an issue.  The vendor has sold you a system that is not fit for purpose.  It's now a management issue.  Light the blue touch paper and stand back.

If you've already done this, then you've done everything that you can do without interfering with the application itself.  Again, I can't say this strongly enough: do not do anything to the application that could void the SLA.


Is there a MySQL engine or trick to avoid writing so many temp tables to disk?

You need to properly tune all connection-based settings as you pointed out from that link.
If you are looking for something quick and dirty, I have just the thing:
Back on Nov 30, 2012, I wrote a nice post about mapping temp tables to RAM, even disk-based temp tables : Is it bad to create many mysql temporary tables simultaneously?
Give it a Try !!!
UPDATE 2013-01-03 22:14 EDT
In terms of the BLOBs, my condolences. Notwithstanding, I have good news. I have a very quick-and-dirty stunt you can do once you set up the RAM disk.
Ready ???
Set the following values in
tmp_table_size = 1K
max_heap_table_size = 1K

That's not a misprint. That's not a typo. I said set tmp_table_size and max_heap_table_size to 1K. You are probably saying "Why set those values so low?"
Those values represent the largest in-memory temp table allowed before transferring the temp table to disk. If you have a RAM disk, then temp tables leap right into the RAM disk as quickly as possible. As the MySQL Documentation says on max_heap_table_size:

If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.


Single-row INSERT...SELECT much slower than separate SELECT

SQL Server chooses to scan the heap tables on the inner side of the loops joins using row-level locks. A full scan would normally choose page-level locking, but a combination of the size of the table and the predicate means the storage engine chooses row locks, since that appears to be the cheapest strategy.

The cardinality misestimation deliberately introduced by the OPTIMIZE FOR means that the heaps are scanned many more times than the optimizer expects, and it does not introduce a spool as it normally would.

This combination of factors means that performance is very sensitive to the number of locks required at runtime.

The SELECT statement benefits from an optimization that allows row-level shared locks to be skipped (taking only intent-shared page-level locks) when there is no danger of reading uncommitted data, and there is no off-row data.

The INSERT...SELECT statement does not benefit from this optimization, so millions of RID locks are taken and released each second in the second case, along with the intent-shared page-level locks.

The enormous amount of locking activity accounts for the extra CPU and elapsed time.

The most natural workaround is to ensure the optimizer (and storage engine) get decent cardinality estimates so they can make good choices.

If that is not practical in the real use case, the INSERT and SELECT statements could be separated, with the result of the SELECT held in a variable. This will allow the SELECT statement to benefit from the lock-skipping optimization.

Changing the isolation level can also be made to work, either by not taking shared locks, or by ensuring that lock escalation takes places quickly.

As a final point of interest, the query can be made to run even faster than the optimized SELECT case by forcing the use of spools using undocumented trace flag 8691.


SQL Server backup failing. Error: 3041, Severity: 16, State: 1

As Max pointed out, the sa login error doesn't corrolate to the backup time as it happens several hours before, however the 2nd one does.  Have you looked into:
http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx

http://www.sqlservercentral.com/Forums/Topic377116-146-1.aspx#bm380502

Do any of the items in the MSDN blog play a factor in your environment?  Most common one would be the AUTO_CLOSE ON.  Let us know, thanks.


Deleting multiple rows based on the first instance of the value

If this was SQL Server 2012 or later version, you could use the FIRST_VALUE window function:

WITH cte AS
  (
    SELECT
      FirstColC = FIRST_VALUE(ColC) OVER (PARTITION BY ColA ORDER BY ColB ASC)
    FROM
      dbo.Table1
  )
DELETE FROM
  cte
WHERE
  FirstColC % 2 = 0
;

In SQL Server 2005 FIRST_VALUE can be replaced with a correlated TOP (1) + ORDER BY subquery:

WITH cte AS
  (
    SELECT
      FirstColC = (
        SELECT TOP (1)
          ColC
        FROM
          dbo.Table1 AS toprow
        WHERE
          toprow.ColA = main.ColA
        ORDER BY
          ColB ASC
      )
    FROM
      dbo.Table1 AS main
  )
DELETE FROM
  cte
WHERE
  FirstColC % 2 = 0
;


How to take Backup of BackUp DB in SQL Server 2012

Changing the IP of the secondary server shouldn't be an issue as long as you used DNS names in the log shipping configuration.

To answer your questions:

No you can't take a backup of a standby database, at least not with the native functionality in SQL Server.
As long as all the setting stay the same, there shouldn't be any impact. You can disable the copy and restore job before the change though.
There shouldn't be any impact as long as you used DNS names in your log shipping configuration. The only thing that I can think of is that you need to refresh the DNS cache of the primary server to get the new IP address.


How to store one-byte integer in PostgreSQL?

No, there is no 1-byte integer in the standard distribution of Postgres. All built-in numeric types of standard Postgres occupy 2 or more bytes.
Extension pguint
But yes, there is the extension pguint, maintained by Peter Eisentraut, one of the Postgres core developers. It's not part of the standard distribution:
In addition to various unsigned integer types, it also provides the 1-byte integer you are looking for:
int1 (signed 8-bit integer)
uint1 (unsigned 8-bit integer)
uint2 (unsigned 16-bit integer)
uint4 (unsigned 32-bit integer)
uint8 (unsigned 64-bit integer)

Be sure to read the chapter "Discussion" at the linked site, explaining possible complications. You need to exercise care with type casts and numeric literals when introducing more integer types ...
Workaround
A possible, simple workaround would be to encode 1-byte integer values as "char" (with double-quotes!), an "internal" simplistic 1-character type, which actually uses a single byte of storage, byte values of a signed 1-byte integer, the upper half represented as ASCII characters.
You can encode values in the range of -128 to 127. Demo:
SELECT i
     , i::"char"
     , i::"char"::int
FROM   generate_series(-128,127) i;

There are several characters not meant for display. So encode before you store and decode before you display ...
Remember: "char" is an "internal" type intended for simple and cheap enumeration. Not officially designed for what we are doing here, and not portable to other RDBMS. There are no guarantees by the Postgres project. But since "char" is used all over the system catalogs, the type is not going to change.
Initially, I had assumed unsigned 1-byte integer range (0 to 255) and used text as stepping stone. But that only works for numbers 1 - 127. Instead, use signed integer range (-128 to 127) and cast between "char" and integer directly.


SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
  AND TABLE_NAME IN ('TABLES', 'SCHEMATA');

or, for example (6 tables will be shown)

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
  AND (    TABLE_NAME LIKE 'TABLE%'
        OR TABLE_NAME LIKE 'SCHEMA%' );


MySQL: Very slow query, not using index, no joins involved

You may want the following indexes

ALTER TABLE tran.member ADD INDEX cardnbr_mbr_nbr_ndx (cardnbr,mbr_nbr);
ALTER TABLE tran.member ADD INDEX mbr_nbr_cardnbr_ndx (mbr_nbr,cardnbr);

Then, you could queries like these

Each Member and All Cards for That Member

SELECT mbr_nbr MemberNumber,GROUP_CONCAT(cardnbr) Cards
FROM tran.member GROUP BY mbr_nbr;

Each Card and All Members Associated with that Card

SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.member GROUP BY cardnbr;

So, that the size of tran.member does not slow things down, try creating a temp table for the purpose of gathering these two columns

DROP TABLE IF EXISTS tran.adhoc_members_cards;
CREATE TABLE tran.adhoc_members_cards
SELECT mbr_nbr,cardnbr FROM tran.member WHERE 1=2;
ALTER TABLE tran.adhoc_members_cards ENGINE=MyISAM;
ALTER TABLE tran.adhoc_members_cards ADD INDEX cardnbr_mbr_nbr_ndx (cardnbr,mbr_nbr);
ALTER TABLE tran.adhoc_members_cards ADD INDEX mbr_nbr_cardnbr_ndx (mbr_nbr,cardnbr);
ALTER TABLE tran.adhoc_members_cards DISABLE KEYS;
INSERT INTO tran.adhoc_members_cards
SELECT mbr_nbr,cardnbr FROM tran.memberl
SELECT mbr_nbr,cardnbr FROM tran.member WHERE 1=2;
ALTER TABLE tran.adhoc_members_cards ENABLE KEYS;

Now, you can run the two queries I mentioned like this:

SELECT mbr_nbr MemberNumber,GROUP_CONCAT(cardnbr) Cards
FROM tran.adhoc_members_cards GROUP BY mbr_nbr;
SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.adhoc_members_cards GROUP BY cardnbr;

Search for the cardmember you want as well:

SELECT cardnbr CardNumber,GROUP_CONCAT(mbr_nbr) Members
FROM tran.adhoc_members_cards WHERE cardnbr = 000099999930;


Is DBCC SHOWFILESTATS an accurate way to see unused space?

You can use sys.dm_db_index_physical_stats to identify the size of your partitions and how much space is being used.  Use the page_count field to see the size of your partition.  If you perform a SAMPLED or DETAILED scan, you will also get a value for avg_page_space_used_in_percent, which will allow you to estimate how much of the space in the partition is actually being used.

SELECT
    object_name(di.object_id) [table_name]
    ,i.name
    ,di.index_id
    ,di.partition_number
    ,di.record_count
    ,di.page_count/128.0 [partition_size_MB]
    ,di.avg_page_space_used_in_percent
    ,(di.page_count/128.0) * di.avg_page_space_used_in_percent * .01 [space_used_MB]
FROM
    sys.indexes i
    join sys.dm_db_index_physical_stats(DB_ID(),object_id('<Table Name'>), NULL, NULL, 'SAMPLED') di
      on i.object_id = di.object_id and i.index_id = di.index_id

If you want to check the free space in the actual files themselves, I recommend using a query from Glenn Berry's DMV script.

SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);

This should give you a rough idea of whether or not you have any space to reclaim.  As for actually reclaiming it, it depends on what you need to do.  If your files are simply over provisioned, you can shrink them using DBCC SHRINKFILE (this is not something that should be done regularly).  If, however, the files are mostly full but you have free space within the partitions, try rebuilding the indexes to reclaim some of the space (likely lost due to fragmentation).  If the disk is full and there isn't space in the partitions, consider archiving data off to another disk system or to offline storage.

That all being said, 100 million rows really isn't that much data and I'm curious as to why partitioning was even chosen as a solution.  It's a lot of overhead without a lot of benefit to you.


Can I setup database triggers to run on a defined secondary set of servers only?

This currently isn't practical. Writes may not be done on a downstream replica, whether it replays WAL via streaming or archives.

However, the logical decoding feature in PostgreSQL 9.4 sets the foundations for making this sort of thing possible. You could have a logical decoding plugin that reads the change-stream from the master server, then writes audit information about it to a separate PostgreSQL master. If you want to query the audit server from the main production server you could use a foreign data wrapper like postgres_fdw.

The team I'm working with is building an asynchronous multi-master system for PostgreSQL named Bi-Directional Replication for PostgreSQL (BDR). At the moment it can't do what you want, but it's built on top of logical decoding, and the sort of thing you describe is something we're looking at adding in a later release of BDR, or its one-way single-master replication variant UDR.

Alternately, you may wish to look at adopting a system like Londiste. Combined with PostgreSQL's ENABLE REPLICA triggers (which only fire on replication writes via session_replication_role = 'replica', not for local writes) you might be able to rig up something like what you want. Note that Londiste doubles the write load on the master, though, as it has to write a replication queue entry as well as the original write. This might be almost as expensive as updating your activity log directly.

Logical decoding avoids this write amplification, which is why I think it'll be the long term solution for what you want to do. Right now using it would involve some non-trivial software development, though.


How can I check the integrity of disabled foreign key relationships for each individual table? (SQL Server 2008)

These DBCC CHECKCONSTRAINTS commands can be run as follows:

DBCC CHECKCONSTRAINTS (TableName) - checks an individual table
DBCC CHECKCONSTRAINTS (ConstraintName) - checks an individual
constraint
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS - checks all constraints
in the database
DBCC CHECKCONSTRAINTS WITH ALL_ERRORMSGS - returns all rows that
violate constraints
DBCC CHECKCONSTRAINTS WITH NO_INFOMSGS - suppress messages when query
runs
DBCC CHECKCONSTRAINTS check only enabled constraints

Keep in mind that SQL Server needs to read through all of your data to check for the constraints, so be careful not to run this across the board for your large or very busy databases. This should be something that is run off hours and also for very large databases you should run this either at a table level or a constraint level.


After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column

ORDER BY within a view was only ever a broken "feature" (albeit one that enterprise manager seemed to encourage).

Views are meant, for the most part, to be indistinguishable from Tables. And Tables have no inherent order.

This means that the only place that an ORDER BY is guaranteed to affect the order in which results are returned is in the outermost SELECT - i.e. the one that references this view (assuming that that SELECT, in turn, is not within another view or UDF)

Within a view, UDF, etc, ORDER BY was only ever meant to work for its other/overloaded purpose - to make TOP make sense - i.e. without an ORDER BY, asking for the TOP 10 would be poorly defined, because, again, tables have no inherent order.

Unfortunately, there is a common mis-perception that this works because the Graphical view designers in Enterprise Manager and Management Studio actually encourage it - there are "sort" features included there, and if you use them, the tool inserts TOP 100 PERCENT and ORDER BY - despite this having been something that doesn't work since at least 2005.

Having just re-read the question - it was relying on the order of a GROUP BY? That's compounding one issue with another. There's no guarantee that the output from a statement involving a GROUP BY will have any particular order.


There would not be any noticeable performance benefit if you move to higher version of windows server. Normally people move to higher version keeping support in perspective and the host of new features they get in newer version of windows. Refer Compare features in Windows Server versions to see list of new features you get in various versions of Windows Server.


MySQL Deadlock - accessing different primary key values also creating deadlock

So, I will answer the question "why it is creating problem with whole query":

In MySQL's REPEATABLE-READ transaction isolation mode, no phantom rows appear. In order to do that, MySQL locks, not only the rows, but also the gaps between them (also know as next-key locking).

As MySQL happens to be executing the read on the first table using an index scan, what MySQL considers a gap is probably larger than expected, as it uses the primary key to check that no newer rows are inserted that could affect the query with that particular scan type. As a result, a large part of the table may be affected.

Reducing the transaction-isolation mode should avoid the gap-locking and only lock the specific row being updated and improve the general concurrency while avoiding deadlocks as only one row is affected for that particular query.

Update: It seems that the lower isolation level doesn't fix the locking for the update + subselect. Rewriting it as a JOIN does actually do the trick? (Maybe the UPDATE has a different optimization than the SELECT?). The idea of the JOIN rewrite is doing something like this:

UPDATE M_SAMP M
JOIN MM_RVW_SAMP MM
ON M.M_ID = MM. M_ID
SET M.MM_Q_IND = 1
WHERE MM.TARGET_M_ID = 19;


How do you get a count for an aggregated row?

SELECT  sp.advcode AS Advertiser,
        ii.*
    FROM
      ( SELECT  count(*) AS Created,
                COUNT(i.sent) AS Sent,
                sum((i.active = 1) AND (i.sent IS NULL)) AS ActiveUnsent,
                count(i.inquiryId) AS Total,
                COUNT(i.campaignLeadId) AS CampaignLeads,
                sum(i.active = 0) AS Inactive
            FROM  inquiry i
            WHERE  i.dateCreated >= '2016-02-02'
              AND  i.dateCreated  < '2016-02-02' + INTERVAL 1 MONTH
            GROUP BY  i.schoolProfileId
      ) AS ii
    LEFT JOIN  schoolProfile sp ON sp.schoolProfileId = ii.schoolProfileId ;
    WHERE  sp.advcode LIKE 'al007'

Notes:

By not having a JOIN, the counts are more likely to be correct.
COUNT(nullable-column) simply count how many non-nulls there are
Boolean expressions evaluate in SUM() as 0 or 1, thereby obviating the CASE.
In your BETWEEN, you probably included an extra day.
i needs INDEX(dateCreated)
I assume you are using InnoDB and schoopProfileId is the PRIMARY KEY of schoolProfile?
If you really need LEFT, then you might get NULL for Advertiser?
If you don't need LEFT, then ...

The following might be faster:

SELECT
      ( SELECT  advcode
            FROM  schoolProfile
            WHERE  schoolProfileId = i.schoolProfileId
              AND  advcode LIKE 'al007'
      ) AS advertiser,
        count(*) AS Created,
        COUNT(i.sent) AS Sent,
        sum((i.active = 1) AND (i.sent IS NULL)) AS ActiveUnsent,
        count(i.inquiryId) AS Total,
        COUNT(i.campaignLeadId) AS CampaignLeads,
        sum(i.active = 0) AS Inactive
    FROM  inquiry i
    WHERE  i.dateCreated >= '2016-02-02'
      AND  i.dateCreated  < '2016-02-02' + INTERVAL 1 MONTH
      AND  EXISTS
      ( SELECT  *
            FROM  schoolProfile
            WHERE  schoolProfileId = i.schoolProfileId
              AND  advcode LIKE 'al007'
      )
    GROUP BY  i.schoolProfileId;


Errors using expdp with `flashback_time=systimestamp`

Just had a look at the documentation.

It says:

  You can specify the time in any format that the
  DBMS_FLASHBACK.ENABLE_AT_TIME procedure accepts

So SYSTIMESTAMP should actually work... Though the docs suggest you need to enclose the parameter in quotation marks. Try:

FLASHBACK_TIME="SYSTIMESTAMP"


Color formatting Pie Charts in SSRS 2008 R2

Open the Properties pane.
On the design surface, click the chart. The properties for the chart object are displayed in the Properties pane.
The object name (Chart1 by default) appears in the drop-down list at the top of the Properties pane.
In the Chart section, for the Palette property, select a new palette from the drop-down list.


Count where previous record exists in either of two tables

Inner join on counting_table.bigint-1 = other_table.bigint and counting_table.char = other_table.char

SELECT *
FROM counting_table t1
INNER JOIN other_table t2
    ON t1.bigint_column-1 = t2.bigint_column
    AND t1.character_column=t2.character_column


MySQL 5.6 on OS X 10.8.2 disconnects on user creation?

Alright another instance of "read your damn logs" :)

tail /usr/local/var/mysql/username.local.err told me:

2013-02-25 11:29:50 1616 [ERROR] Native table 'performance_schema'.'accounts' has the wrong structure
2013-02-25 11:29:50 1616 [ERROR] Native table 'performance_schema'.'hosts' has the wrong structure
2013-02-25 11:29:50 1616 [ERROR] Native table 'performance_schema'.'socket_instances' has the wrong structure
2013-02-25 11:29:50 1616 [ERROR] Native table 'performance_schema'.'socket_summary_by_instance' has the wrong structure
...

So a quick google turned up the answer: sudo mysql_upgrade -u root -ppass


Why and how and where to use user without login

Imagine that you have somebody running an app, and this requires certain privileges. You do want to be able to log who is doing what and see who are logged in to your SQL Server. So each person need their own login (this can possible be one login created for a domain group).
You don't feel comfortable having these people connecting using SSMS and play around in the database. Since the app might need DELETE permissions on the Customers table (say), then the end-user now can do this from SSMS.
So you create a user without login and have the app use EXECUTE AS as this user. I.e., without EXECUTE AS, the end-user have no privileges at all in the database (i.e., the potential AD group you created a login for and its subsequent user in the database). But they do have privileges when they've done EXECUTE AS.
Is this read security? No, of course not. But perhaps better than either having no traceability or having the end-users being able to play around in the database "directly".
What about application roles? Yes, that can be tighter since they are password protected.
Another usage for this is when you are testing things, using EXECUTE AS USER. You want to play around with something permissions related and it is easier to assign the permissions to a user without login and then do your tests then having to create a login and a user for whatever you want to test.


Error "the system cannot find the file specified" when opening a large SQL Server file

If no code page conversions are necessary (i.e. either not changing code pages or changing code pages but no characters being used are different between the code pages; and this only pertains VARCHAR data), then there is an undocumented option that might be much quicker, both in terms of time spent working out the solution, and time spent moving data. In fact, this undocumented approach is incredibly fast because it does not move any data at all. It merely updates the internal collation_id of each string column in the metadata (for system tables that should be updated and all user tables). The only real time spent is in the rebuilding of all affected indexes. But there is no data export and import. And it bypasses all of the restrictions that prevent updating a databases collation (in many, but not all, cases). Plus it changes the system DBs and the instance-level collation.
The major issue is that by changing the collation_id, any existing VARCHAR characters in the range of 128 - 255 might be misinterpreted in the new collation. However, this is a non-issue if the code page isn't changing. Also, there are two areas that are currently not updated by this approach, though I have requested that they be added, so please vote for this suggestion: Update Collation of Partition Functions and User-Defined Table Types via SQLSERVR -Q.
The method I speak of is running sqlservr.exe with the -q switch. You need to stop the SQL Server engine service, and run that command passing in the new collation and a couple of specific trace flags. I have a detailed description of this approach in the following post (please be sure to read all of the details about what this method does and does not do):
Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?


SSIS Merge component performing a UNION instead of merging

The issue is that you're using the wrong transformation.

"The Merge transformation is similar to the Union All transformations." -MSDN Merge Transformation

You can use the Merge Join transformation and specify your join type, then select the input column used for each output.

Then you can get your desired output:

If your source(s) is an RDBMS system it's better to sort with an ORDER BY as opposed to using a Sort task, since it is a blocking component and the DB can likely perform the sort faster.


1 .

select E.* from emp E where id in (
select A.eid from (select distinct eid from dep where name = 'Tom')A
left join (select distinct eid from dep where name = 'Paul')B
on A.eid = B.eid where B.eid is null

)

2 .

select E.* from emp E where id not in (
select distinct eid from dep where name = 'Tom'
)


Create integer id columns from existing string columns (integer coding?)

I guess you can use the ASCII function:

SELECT ID_STRING_A,ID_STRING_B
     , ASCII(ID_INT_A) - 64 AS ID_INT_A
     , ASCII(ID_INT_B) - 64 AS ID_INT_B
FROM ...

Perhaps the intention's more clear using:

     , ASCII(ID_INT_A) - ASCII('A') + 1 AS ID_INT_A

EDIT, since the question where changed something like this is possible:

WITH T (n, s) as (
    SELECT row_number() over (order by s), s
    FROM (
        SELECT ID_STRING_A FROM mytable
        UNION
        SELECT ID_STRING_B FROM mytable
    ) as X (s)
)
SELECT m.ID_STRING_A, m.ID_STRING_B, T1.n, T2.n
FROM mytable as m
JOIN T as T1
    ON m.ID_STRING_A = T1.s
JOIN T as T2
    ON m.ID_STRING_B = T2.s

EDIT, updating table

I have a gut feeling that this can be done in a simpler way, but I cross joined the cte with itself and filtered with WHERE to update both columns at once:

ALTER TABLE mytable
    ADD ID_INT_A INT;

ALTER TABLE mytable
    ADD ID_INT_B INT;

WITH cte (n, s) as (
    SELECT row_number() over (order by s), s
    FROM (
        SELECT ID_STRING_A FROM mytable
        UNION
        SELECT ID_STRING_B FROM mytable
    ) as X (s)
), cte2 (n1,s1,n2,s2) as (
    SELECT c1.n, c1.s, c2.n, c2.s
    FROM cte c1
    CROSS JOIN cte c2
)
UPDATE mytable
    SET ID_INT_A = cte2.n1
      , ID_INT_B = cte2.n2
FROM cte2
WHERE mytable.ID_STRING_A = cte2.s1
  AND mytable.ID_STRING_B = cte2.s2
;

It should be noted that this is a 1-time operation. If you decide to add AABB later on, the enumeration will be wrong


The program which performs log shipping runs under a specific UserID. Assuming you are using SQL Server Agent to perform log shipping on a schedule, log shipping will happen using SQL Server Agent's credentials (but see below for proxy accounts). You can find this by starting SQL Server Configuration, highlighting SQL Server Services and right-clicking on SQL Server Agent. The account listed there must have write permission on NAS device. Importantly, the source Agent and the destination device must share a source of authentication. If they are on different network domains, say, you may have to get your LAN administrators involved to establish the trust between the two devices.

Proxies: it is possible to establish additional credentials which SQL Agent will use to run particular jobs. See here for information. If your site is using proxies to run log shipping then it is the proxy account which will need access to the NAS.

To test, I would suggest you create a new job with a single step that copies a small text file from the DB server to the NAS. Use the same credentials for this as you do for log shipping. Run this and adjust permissions until it works. Then duplicate those permission for the actual log shipping job and re-test.


You can create an index on the column. However casting strings to dates for an index isn't straightforward, as the conversion isn't recognized as "immutable" by Postgres (although it is).
With a little workaround, this can be done though:
create function dob(p_value jsonb)
returns date
as
$$
  select (p_value ->> 'dob')::date;
$$
language sql
immutable;

Then you can create an index:
create index on the_table ( (dob(obj)) );

And use the function in the WHERE clause:
SELECT *
FROM the_table
WHERE dob(obj) BETWEEN DATE '2019-01-01' AND DATE '2020-01-01'


How to get all records where a joined column contains some value (at least once)

you can use a subquery in your where clause.

e.g.

SELECT sheet,
       entry,
       approver
FROM timesheet
WHERE sheet =
(
    SELECT sheet FROM timesheet WHERE approver = 3
);


What impact does using backup preference on Availability Groups have?

No, the availability group does not do anything to control where backups occur.  The backup preference settings on an availability group are there so that the backup software can reference it to determine where to run the backup.  If the backup software in use doesn't have any code to reference these settings, they will have no impact on that backup software and it will just run the backups on the server it connects to.

Most backup software that supports availability groups will reference the backup preference configuration and take backups accordingly.  Maintenance Plan backups will adhere to the backup preference configured on the availability group.

One option I would recommend for the copy-only backup setup--create your SQL Agent job to run a copy-only backup.  The first step in the job would be to check if the server is the primary, and if so, end the job.  If it is not the primary, then run the copy-only backup.

The first step of the job can use the fn_hadr_is_primary_replica function to determine if the server is the primary of a given database:

sys.fn_hadr_is_primary_replica ( 'dbname' )

See sys.fn_hadr_is_primary_replica (Transact-SQL).


How do I use RMAN to create a test database from a full recovery backup dataset?

First place to check is Oracle official documentation.
For example http://docs.oracle.com/cd/E36909_01/backup.1111/e10642/rcmdupdb.htm#autoId0
Depending of your database version(10g, 11g, 12c...).
Just search documentation for duplicate database.
I am not going to explain all questions from your script because it could only confuse you a little more.

Depending of prod db size you could use datapump export and import for example, or transportable tablespaces or like you tried RMAN.
Your rman commands should be something like

-- scripts for restore using RMAN,
-- This script is usable when the oracle versions are the same,
-- or a direct upgrade is supported
--
-- prep database for rman import
rman target /
shutdown immediate
startup nomount
exit

-- duplicate the database
rman auxiliary /
duplicate database to newdb
  backup location 'E:\Backup\oracle\flash_recovery_area\olddb\BACKUPSET\2016_07_29' nofilenamecheck
  DB_FILE_NAME_CONVERT ('E:\app\oracle\oradata\olddb','E:\Oracle\oradata\newdb')
  LOGFILE
    'E:\Oracle\oradata\newdb\redo01.log' SIZE 2G,
    'E:\Oracle\oradata\newdb\redo02.log' SIZE 2G,
    'E:\Oracle\oradata\newdb\redo03.log' SIZE 2G;

duplicate database to testdb spfile backup location 'backup_path' nofilenamecheck;

When the databases are of different versions, use expdp/impdp:

-- Alternate procedure using using expdp / impdp
--
-- Export on source database
sqlplus: create or replace directory data_pump_directory as 'f:\dp';

cmd: expdp user/password@olddb schemas=SchemaToMigrate directory=data_pump_directory dumpfile=dbname.dmp logfile=expdp_dbname.log

-- generate script for generating user from old database
-- see http://www.oracle-scripts.net/generate-user-ddl/
--
-- copy output files from source system to destination system
--
-- Prep destination database for the import
-- use script generated above to create user/schema on destination database
sqlplus: create or replace directory data_pump_directory as 'f:\dp';

-- From bitter experience, this will not necessarily work with the user/password@newdb
-- on the command line.
-- If impdp chokes with ORA-39087:
-- 1. Confirm database is open (alter databaase open)
-- 2. Confirm the directory reference is created in the database
-- 3. Confirm the system and schema users are granted read and write on the directory reference
-- 4. Confirm the oracle service (OS) user has authority on the OS directory
-- 5. then try not supplying credentials until impdp asks for them (yeah,  a possible bug somewhere)
cmd: impdp user/password@newdb schemas=SchemaToMigrate directory=data_pump_directory dumpfile=dbname.dmp logfile=impdp_dbname.log table_exists_action=replace  remap_tablespace=oldts1:newts1,oldts2:newts2

This script can be written in many ways, depending if you want to recover to specific date and time, specific SCN, is test db the same structure(the same path for data and other files) or not, the same operating system or not, do you need to change some initialization parameters or not etc.


The column order of composite clustered primary key on a partitioned table?

Which of the following primary keys (the column order is different) is preferred?

Like all indexing decisions, much depends on how the table will be queried.

All partitioned indexes (for SQL Server 2008 and later) have the partition ID (not partitioning key value) as a hidden leading key column in each partitioned index, so the effective competing definitions are:

PartitionID, K1, K2
vs.
PartitionID, K2, K1

This affects the utility of each index for different types of queries, as one would expect. The main extra consideration is that inequality seeks on the first real key (K1 or K2) are still supported, regardless of any inequality seek and/or partition elimination operations on the PartitionID column.

For example, the (K1, K2) index specification can seek to a range of partitions, and a range of K1 values simultaneously:

SELECT T1.*
FROM dbo.T1 AS T1
WHERE 1 = 1
AND T1.K1 >= CONVERT(date, '20080711', 112)
AND T1.K1 <= CONVERT(date, '20100711', 112);

The same query on a table with (K2, K1) as the clustered index key could seek find the range of partitions, but it would have to fully scan each qualifying partition to locate rows that match the K1 predicates exactly. To be clear, the test of K1 values would be applied as a residual predicate, not as a seeking operation.

This will show as a Clustered Index Scan in showplan, with a partition-eliminating seek, and residual predicate on K1 values:

A subtle point when using the date data type as the partitioning key is that you must be careful to use explicit date types in your queries if you expect partition elimination to occur reliably. Using other types, such as datetime, is easily done (by accident) but will often prevent elimination, where it would logically be expected.

For example, this query will touch all partitions:

DECLARE @dt datetime = '20080711';

SELECT *
FROM dbo.T1 AS T1
WHERE T1.K1 = @dt;

Whereas this query will touch only a single partition:

DECLARE @dt date = '20080711';

SELECT *
FROM dbo.T1 AS T1
WHERE T1.K1 = @dt;

Both queries look superficially identical in graphical showplan (a clustered index seek). You need to check the operator properties in detail to check if static or dynamic partition elimination is being applied.

For the example join query given in the question: Both indexing strategies include the K2 column, but neither can generally provide rows in K2 order without a sort. As a result, either index is equally good for a hash or nested loops join, but neither can provide the input order required for a merge join on K2.

This might seem counter-intuitive for the (K2, K1) index, but remember the leading PartitionID key. Each partition has rows in (K2, K1) order. Unless exactly one partition is specified in the query, a sort will be necessary to return rows in K2 order. The (K1, K2) index could only return rows in K2 order for a single partition and a single given value of K1.

The proposed clustered primary key (K1, K2) has the potential advantage of minimizing base table page splits if appended data is actually sorted by the clustering key during the insert operation. For the (K1, K2) index, this would mean rows sorted by (PartitionID, K1, K2). For (K2, K1), it would be (PartitionID, K2, K1).

Related reading: Query Processing Enhancements on Partitioned Tables and Indexes


This is straightforward (a simple GROUP BY):

SELECT Client, thickness, material, SUM(amount) as amount
FROM your_table
GROUP BY Client, thickness, material;

Change your_table for the name of your table. If only 1 row exists of one combination of Client, thickness and material, it will select it as is.

Check the GROUP BY MySQL page for more information.


Join query result of 2 different tables based on common column

You can do it with:

SELECT   u.id, u.given_name, u.family_name, u.email, COUNT(1) as num
FROM     users u JOIN tasks t
          ON (u.id=t.user_id)
WHERE    t.created_at BETWEEN %s and %s
GROUP BY u.id, u.given_name, u.family_name, u.email, (DAYS_AGO_7_CONFIG,YESTERDAY))

To address the question in the comments, you can add a HAVING clause after GROUP BY to limit the results to those where num is greater than 5:

HAVING   COUNT(1)>5


Replication : Delete from Master Database

set sql_log_bin=0;
<delete_query>;
set sql_log_bin=1;

That should take care with the delete on master and will not replicate to the slave.

I'd recommend against this though. I think it's better to transfer data older than 5 years to a separate table then drop the table from the master side and retain the one on the slave.


One way of doing it would be to use LEAD.

WITH T AS
(
SELECT  StudentID,
        GradeIssued AS g1,
        LEAD(GradeIssued, 1) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g2,
        LEAD(GradeIssued, 2) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g3,
        LEAD(GradeIssued, 3) OVER (PARTITION BY StudentID ORDER BY WhenIssued) AS g4
FROM StudentGrades
)
SELECT DISTINCT StudentID
FROM   T
WHERE  g1 = 'D'
       AND g2 = 'C'
       AND g3 = 'C'
       AND g4 = 'A'

Another way would be to use XML PATH to concatenate all grades in order and LIKE.

WITH sg1 AS
(
SELECT DISTINCT StudentID
FROM StudentGrades
)
SELECT *
FROM sg1
CROSS APPLY
(
    SELECT GradeIssued + ''
    FROM StudentGrades sg2
    WHERE sg2.StudentID = sg1.StudentID
    ORDER BY WhenIssued
    FOR XML PATH('')
) CA(grades)
WHERE grades LIKE '%DCCA%'


I made a connection using pgadmin, and then dropped the trigger. The problem was I can only use ssh to connect to the server, and I didn't know how to make that connection at first. I've just found a way to tunnel the connection via ssh.

ssh -fNg -L 5555:localhost:5432 {username}@{domain.com}


Query Error: Error: ER_NON_UNIQ_ERROR: Column 'candidate_id' in field list is ambiguous

Your query is right, only

    SELECT user_name, can.candidate_id
FROM user AS usr INNER JOIN candidate as can
ON usr.user_id = can.entered_by
INNER JOIN candidate_joborder as canjo
ON can.candidate_id = canjo.candidate_id
WHERE usr.user_id = 1


How do I get the count for time periods?

This is what worked for me in the end.

SELECT
     [StartTime].[PossibleTime] AS [StartTime]
    ,[EndTime].[PossibleTime] AS [EndTime]
    ,(SELECT COUNT(1)
       FROM [Log] AS [L]
       WHERE [StartTime].[PossibleTime] >= [L].[loginTime]
       AND [EndTime].[PossibleTime] <= [L].[logoutTime]
     ) AS [UserCount]
SELECT ROW_NUMBER() OVER (ORDER BY [PossibleTime]) AS [ROWNUM]
FROM (
    SELECT [loginTime] AS [PossibleTime]
    FROM [Log]
    UNION ALL
    SELECT [logoutTime] AS [PossibleTime]
    FROM [Log]
   ) AS [EndTime]
INNER JOIN
SELECT ROW_NUMBER() OVER (ORDER BY [PossibleTime]) AS [ROWNUM]
FROM (
    SELECT [loginTime] AS [PossibleTime]
    FROM [Log]
    UNION ALL
    SELECT [logoutTime] AS [PossibleTime]
    FROM [Log]
) AS [StartTime]
ON [StartTime].[ROWNUM] = [EndTime].[ROWNUM]
ORDER BY [StartTime].[PossibleTime], [EndTime].[PossibleTime]


pgAdmin 4, no built-in SSH tunnel config?

UPDATE:

SSH Tunneling is now available as of version 3.1 - https://www.pgadmin.org/docs/pgadmin4/development/release_notes_3_1.html

Original answer:

Looks like they plan on having it for v2.0, which could be up to a year away, though they say that they are open to having it sooner.

  I hope we'll be able to add SSH support sooner rather
  than later.

https://www.mail-archive.com/pgadmin-support@postgresql.org/msg15175.html


What index to use with lots of duplicate values?

BTree

  My issue here is that the BTree index will be huge since afaict it will store duplicate values (it has too, since it can't assume the table is physically sorted). If the BTree is huge I end up having to read both the index and the parts of the table that the index points too...

Not necessarily — Having a btree index that is 'covering' will be the fastest read time, and if that is all you want (ie if you can afford the extra storage), then it is your best bet.

  BRIN

  My understanding is that I can have a small index here at the expense of reading useless pages. Using a small pages_per_range means that the index is bigger (which is a problem with BRIN since I need to read the whole index), having a big pages_per_range means that I'll read a lot of useless pages.

If you can't afford the storage overhead of a covering btree index, BRIN is ideal for you, because you have clustering already in place (this is crucial for BRIN to be useful). BRIN indexes are tiny, so all the pages are likely to be in memory if you choose a suitable value of pages_per_range.

  Is there a magic formula to find a good value of pages_per_range that takes into account those trade offs?

No magic formula, but start with pages_per_range somewhat less than the average size (in pages) occupied by the average a value. You are probably trying to minimize: (number of BRIN pages scanned)+(number of heap pages scanned) for a typical query. Look for Heap Blocks: lossy=n in the execution plan for pages_per_range=1 and compare with other values for pages_per_range — i.e. see how many unnecessary heap blocks are being scanned.

  GIN/GiST

  Not sure those are relevant here since they're mostly used for full text search, but I also hear that they're good at dealing with duplicate keys. Would either a GIN/GiST index help here?

GIN may be worth considering, but probably not GiST — however if the natural clustering really is good, then BRIN will probably be a better bet.

Here is a sample comparison between the different index types for dummy data a bit like yours:

table and indexes:

create table foo(a,b,c) as
select *, lpad('',20)
from (select chr(g) a from generate_series(97,122) g) a
     cross join (select generate_series(1,100000) b) b
order by a;
create index foo_btree_covering on foo(a,b);
create index foo_btree on foo(a);
create index foo_gin on foo using gin(a);
create index foo_brin_2 on foo using brin(a) with (pages_per_range=2);
create index foo_brin_4 on foo using brin(a) with (pages_per_range=4);
vacuum analyze;

relation sizes:

select relname "name", pg_size_pretty(siz) "size", siz/8192 pages, (select count(*) from foo)*8192/siz "rows/page"
from( select relname, pg_relation_size(C.oid) siz
      from pg_class c join pg_namespace n on n.oid = c.relnamespace
      where nspname = current_schema ) z;

name               | size    | pages | rows/page
:----------------- | :------ | ----: | --------:
foo                | 149 MB  | 19118 |       135
foo_btree_covering | 56 MB   |  7132 |       364
foo_btree          | 56 MB   |  7132 |       364
foo_gin            | 2928 kB |   366 |      7103
foo_brin_2         | 264 kB  |    33 |     78787
foo_brin_4         | 136 kB  |    17 |    152941

covering btree:

explain analyze select sum(b) from foo where a='a';

| QUERY PLAN                                                                                |
| :---------------------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=3282.57..3282.58 rows=1 width=8) (actual time=45.942..45.942 rows=1 loops=1)                                                   |
|   ->  Index Only Scan using foo_btree_covering on foo  (cost=0.43..3017.80 rows=105907 width=4) (actual time=0.038..27.286 rows=100000 loops=1) |
|         Index Cond: (a = 'a'::text)                                                                                |
|         Heap Fetches: 0                                                                                |
| Planning time: 0.099 ms                                                                                |
| Execution time: 45.968 ms                                                                                |

plain btree:

drop index foo_btree_covering;
explain analyze select sum(b) from foo where a='a';

| QUERY PLAN                                                                                |
| :-------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=4064.57..4064.58 rows=1 width=8) (actual time=54.242..54.242 rows=1 loops=1)                                     |
|   ->  Index Scan using foo_btree on foo  (cost=0.43..3799.80 rows=105907 width=4) (actual time=0.037..33.084 rows=100000 loops=1) |
|         Index Cond: (a = 'a'::text)                                                                                |
| Planning time: 0.135 ms                                                                                |
| Execution time: 54.280 ms                                                                                |

BRIN pages_per_range=4:

drop index foo_btree;
explain analyze select sum(b) from foo where a='a';

| QUERY PLAN                                                                                |
| :-------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=21595.38..21595.39 rows=1 width=8) (actual time=52.455..52.455 rows=1 loops=1)                                   |
|   ->  Bitmap Heap Scan on foo  (cost=888.78..21330.61 rows=105907 width=4) (actual time=2.738..31.967 rows=100000 loops=1)        |
|         Recheck Cond: (a = 'a'::text)                                                                                |
|         Rows Removed by Index Recheck: 96                                                                                |
|         Heap Blocks: lossy=736                                                                                |
|         ->  Bitmap Index Scan on foo_brin_4  (cost=0.00..862.30 rows=105907 width=0) (actual time=2.720..2.720 rows=7360 loops=1) |
|               Index Cond: (a = 'a'::text)                                                                                |
| Planning time: 0.101 ms                                                                                |
| Execution time: 52.501 ms                                                                                |

BRIN pages_per_range=2:

drop index foo_brin_4;
explain analyze select sum(b) from foo where a='a';

| QUERY PLAN                                                                                |
| :-------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=21659.38..21659.39 rows=1 width=8) (actual time=53.971..53.971 rows=1 loops=1)                                   |
|   ->  Bitmap Heap Scan on foo  (cost=952.78..21394.61 rows=105907 width=4) (actual time=5.286..33.492 rows=100000 loops=1)        |
|         Recheck Cond: (a = 'a'::text)                                                                                |
|         Rows Removed by Index Recheck: 96                                                                                |
|         Heap Blocks: lossy=736                                                                                |
|         ->  Bitmap Index Scan on foo_brin_2  (cost=0.00..926.30 rows=105907 width=0) (actual time=5.275..5.275 rows=7360 loops=1) |
|               Index Cond: (a = 'a'::text)                                                                                |
| Planning time: 0.095 ms                                                                                |
| Execution time: 54.016 ms                                                                                |

GIN:

drop index foo_brin_2;
explain analyze select sum(b) from foo where a='a';

| QUERY PLAN                                                                                |
| :--------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate  (cost=21687.38..21687.39 rows=1 width=8) (actual time=55.331..55.331 rows=1 loops=1)                                    |
|   ->  Bitmap Heap Scan on foo  (cost=980.78..21422.61 rows=105907 width=4) (actual time=12.377..33.956 rows=100000 loops=1)        |
|         Recheck Cond: (a = 'a'::text)                                                                                |
|         Heap Blocks: exact=736                                                                                |
|         ->  Bitmap Index Scan on foo_gin  (cost=0.00..954.30 rows=105907 width=0) (actual time=12.271..12.271 rows=100000 loops=1) |
|               Index Cond: (a = 'a'::text)                                                                                |
| Planning time: 0.118 ms                                                                                |
| Execution time: 55.366 ms                                                                                |

dbfiddle here


Handling attributes that are time-variant in a Datamart

The way to do this is what Kimball called a Type-2 or Type-6 slowly changing dimension..  Essentially, a type-2 SCD has a synthetic dimension key, and a unique key consisting of the natural key of the underlying entity (in this case the flyer) and an 'effective from' date.  The synthetic key is joined against the fact table, so you can attach it with a simple equi-join (i.e. you don't have to filter by date range in the query).

All the attributes (e.g. club in this case) are attributes of the flyer.  If one of these attributes changes, a new row is created on the dimension recording the new state, effective from the date of the change.

The type-6 is like an ordinary type 2, but has a self-join to the current version of the row.  Whenever a new row is created for a given natural key all rows for that natural key are updated with the self-join to the current row.  You may or may not need this functionality.

You can query an as-at status by joining the fact tables against the row that was recorded on them - i.e. the state that was current.  If you have a type-6 the current status can be queried through the self-join, which can also be materialised on the fact table if desired.

This data will also play nicely with ad-hoc reporting tools and cubes, although implementing complex cube hiererchies on a slowly changing dimension is a bit fiddly (you need to keep placeholders for the natural keys of the hierarchy levels and combinations over time).

A good point to start would be a google search on "type 2 slowly changing dimension"


From your log, it looks like you were successful starting streaming replication.

  LOG:  unexpected pageaddr 0/24000000 in log segment 000000010000000000000029, offset 0

  LOG:  started streaming WAL from primary at 0/29000000 on timeline 1

To see more in your log, want to change the logging parameters:

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql/9.4'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0

Adding hot_standby = on to your postgresql.conf also makes it easier for debugging as well.

Though, what you really should be doing to see if replication is working in your setup is something like this:

Create a table and generate traffic:

CREATE TABLE t1 (a int, b int);

INSERT INTO t1 (a,b)
    SELECT random()*100000, random()*100000
             FROM generate_series(1,1000000);

Check the status on the primary and secondary with these queries:

Primary:

SELECT * FROM pg_current_xlog_location();

Hot Standby:

SELECT * FROM pg_last_xlog_replay_location();

If the locations are exactly the same, or very close together, then everything is working just fine.

Hope that helps. =)


Remote queries at SNAPSHOT isolation level

If you dig into the internals of how snapshot isolation is implemented in SQL Server, the reason why remote access is not supported becomes clear.

When snapshot isolation is enabled, up to 14 bytes of versioning information are appended to each row that is modified. The additional bytes contain the transaction sequence number of the transaction that modified the row, along with a pointer to the versioned row in tempdb.

A transaction running at snapshot isolation will see the version of a row that existed at the point it started (that exists at the time the first statement is executed, rather than BEGIN TRAN). It is the transaction sequence number that is used to identify the correct version in the version store. As there is no correlation between the sequence numbers on your local and remote server, it's impossible for the remote server to retrieve the correct version.

There are undoubtedly ways a SQL Server to SQL Server connection could manage this first issue, should the SQL development team decide it was worth doing. But there is a second problem, how would you deal with removing versions from the version store?

The background process responsible for cleanup again uses the transaction sequence number to determine if a row version is still required. This requires nothing more than identifying the oldest running transaction sequence number and removing any versions that are older than this. With a remote connection... sounds ugly, which is why it isn't supported.

Could a different approach be taken, perhaps enforcing REPEATABLE READ or SERIALIZABLE on the remote connection? No, not without compromising consistency as the row at the remote server could not be locked at the time your local transaction started. You could not rely on the behaviour, compromising ACID compliance.

Row Versioning Resource Usage and SQL Server 2005 Row Versioning-Based Transaction Isolation contain most of the salient detail.

How best to workaround this will depend on what you're trying to achieve. If you can expand on your specific scenario we can explore the alternative approaches.


Can I skip major versions in pg_upgradecluster?

You should be able to do that with pg_upgradecluster either way, using -m dump which uses pg_dump and pg_restore or -m upgrade which uses pg_upgrade according to the manpage here. Using -m upgrade should be much faster than the dump and restore method that -m dump uses.


Postgres speed up index creation for large table

Before I posted this question, I had two tests running but I wasn't sure if they would ever finish. I was hoping to get some insight into how to speed it up if they never finished. Thankfully, one of the tests did finish. I also got good results by trying the suggestions that jjanes posted.

Using Postgres 9, I ran two tests:

Inserting the 2 billion entries with indexes
Building indexes for a table that already had the 2 billion entries inserted

The first test has been running for a week and has only inserted 20% of the 2 billion entries. Inserting with index is definitely not the way to go.

The second test took 45 hours. Without knowing how long index creation was going to take, I prematurely cut off some of the previous tests and never got to see them complete. It takes a long time but for now, I'm happy that it doesn't run on forever.

Taking jjanes suggestions, I upgraded to Postgres 11, set the parallel workers to 8 and set the maintenance work memory to 7GB. I followed this guide to pick the parameters: https://www.cybertec-postgresql.com/en/postgresql-parallel-create-index-for-better-performance/.
This sped up the index creation and it only took 35 hours. Shaving off 10 hours was a nice surprise. Thanks!


Re-writing a sub-select as a condition on a column generated with 'over (partition by ...)'

Yes but not with row_number(). You can use either the window aggregate min():

select id, code, descriptor
from
  ( select id, code, descriptor,
           min_code = min(code) over (partition by id)
    from @t
  ) as t
where code = min_code ;

Or the rank() window function (or the dense_rank() one, they'll both work the same for the rnk = 1 check):

select id, code, descriptor
from
  ( select id, code, descriptor,
           rnk = rank() over (partition by id order by code)
    from @t
  ) as t
where rnk = 1 ;

I was about to comment that there is no way to do this without subqueries of any kind (correlated, derived tables, ctes) but alas, there is. Not in 2008R2 but in 2012 version using one more window function.

Note: Do not use this. The efficiency will probably be the worse. The following query might be good for an obfuscation contest but not for production.

Instead, check the nice execution plan of the above 2nd query, when there is a suitable index.

Just for fun - and horror of the future maintainer of the code:

select distinct
    id,
    code = min(code) over (partition by id),
    descriptor =
        case when code = min(code) over (partition by id)
            then descriptor
            else first_value(descriptor) over
                    (partition by id order by code, descriptor)
        end
from @t ;

Tested at SQLFiddle


Modify all array entries in all records of a table at once

You can update all the array entries like this:

UPDATE ambient_properties ap
SET recording_time =
    (
        SELECT ARRAY(
            SELECT unnest(ap.recording_time) - interval '1 hours'
        )
    );

The same strategy should also work for values of other types.

SQLFiddle


Availability Group - Deleting old backup files

Since you mentioned this is in an AG, are you sure you're running the command and checking the backup folder on the same server?

If you are connecting to the Availability Group primary through the AG listener, then running the xp_delete_file command will run on whichever physical server happens to be the primary replica at that time.

Try running SELECT @@SERVERNAME to verify which server you're connected to when you run the backup deletion command, then check that server's F:\ drive to see if backups were deleted or not.

You should also verify that you are getting a 0 as the return code from the extended stored procedure:

DECLARE @ReturnCode int;
EXECUTE @ReturnCode = [master].[dbo].xp_delete_file 0, @BackupDir, 'bak', @Date, 1;
IF @ReturnCode <> 0 PRINT @ReturnCode;

Note that the example call to xp_delete_file you provided is targeting files with a .bak extension.  The example call to Ola's backup script is targeting log backups, which will have an extension of .trn by default.  This may be contributing to the discrepancy you're seeing.

By the way, I would strongly recommend that you take backups to somewhere other than the local drive.  Not only does it represent a single point of failure for your SQL Server instance and critical database backups, but it can actually be pretty confusing with an AG when doing restores.

In particular, if your transaction log backups are being taken to two different locations (local folders on each replica), restoring to a point in time over a period that includes an AG failover will be tough.  You'll have to get the log files from both servers and make sure they get restored in the right order.  The process is much simpler if they all go to a network location like \\servername\backups\MyAgListenerName\DatabaseName\LOG.


I located the error: My joins were incorrect and the operation cannot be performed in Design View. I had to rewrite the joins in SQL view.

The original code:

SELECT OldNames.ID, Names.NewID, Catalog.Specimen#,
FROM Catalog INNER JOIN (OldNames INNER JOIN Names ON OldNames.ID = Names.AllID)
ON (Catalog.ID = Names.NewID) AND (Catalog.ID = Names.AllID)

Replace 'AND' with 'OR' to have the code function properly (although it can only be executed from SQL view)

SELECT OldNames.ID, Names.NewID, Catalog.Specimen#,
FROM Catalog INNER JOIN (OldNames INNER JOIN Names ON OldNames.ID = Names.AllID)
(Catalog.ID = Names.NewID) OR (Catalog.ID = Names.AllID)


You'll have to drop the replication slot that is associated with the replication. Check the recovery configuration for the name.

Note that replication will break soon after you do that.


SQL Server LCK_M_X lock from .NET application

It turned out we had a bug that sometimes caused a connection with a still active transaction to leak out of the pool without being properly disposed. It was this transaction that caused locks down the road. After fixing the leak, the problem is now gone!

The READ_COMMITTED_SNAPSHOT change was a red herring.


Select rows from T such that sum(T.quantity) is greater than an argument

If you don't need the "optimal fill", you could use a window function and something like this:

select *
from (
  select id, name, price, category,
         sum(price) over (order by price, date_created) as so_far
  from my_table
  where price > 40
    and category = 'buy'
) t
where so_far <= 12;

If you are mixing "groups" of orders, you might need a partition by clause as well.
Change the order by inside the window function to meet your ordering criteria.


Are availability groups required for AlwaysOn

If you have an underlying Windows Cluster, and shared storage you could look at using Failover Cluster Instances - this would work at an instance level. Standard edition supports this for two nodes.


will a record set derived from jsonb_to_recordset always maintain the order of the items in a JSON array

Here's another solution using another of Postgres' json functions: jsonb_array_elements.

  SELECT id, (value->>'benefit')::varchar as benefit, ordinality as rank
  FROM products
  CROSS JOIN LATERAL jsonb_array_elements(benefits) with ordinality

"with ordinality" gives you the order of each array item and allows you to do an explicit order by on that field to ensure the order remains the same.

This stackoverflow post was very helpful


Selecting data from a mapping table using a foreign key

It is the same
SELECT gl.Title, gl.Genre, gl.Plattform, gl.ReleaseDate, gl.Price
FROM GameList gl
INNER JOIN UserGame ug ON gl.GL_ID = ug.GL_ID
WHERE ug.USR_ID = 10;

As long you have the User_id you don't need more, but if you also want information from the users table
SELECT gl.Title, gl.Genre, gl.Plattform, gl.ReleaseDate, gl.Price
FROM GameList gl
INNER JOIN UserGame ug ON gl.GL_ID = ug.GL_ID
INNER JOIN Users u ON ug.USR_ID=u.USR_ID
WHERE ug.USR_ID = 10;


What is an "operational master" with respect to entity-relationship diagrams?

You piqued my curiosity here. Cursory search on the internets mostly shows that this question haunts students since at least 2014, with no apparent relief. However, there seems to be one page that  explains what an "operational master entity" is:

  The key-only parent entity (otherwise known as an operational master entity, a collection entity, or a categorising entity, or perhaps a domain entity) stores the valid or actual range of values for the attribute.

A domain entity commonly translates to a dictionary, lookup, or dimension table. For example, for a Game entity a Publisher might be one operational master entity. The Publisher entity would have two attributes, a surrogate Id and Name.

P.S. It appears Google Books have a few more occurrences, mostly from the last century.


Instead of object_id and object_type columns, why not use a primary key for each object type to be commented on, but maintain the object_type (for partitioning later) so that you have question_id, comment_id, and answer_id. For each row of data, two of the columns will be null, but it is easy on your ORM (single table inheritance with a type qualifier).

Your queries will also join to the correct FK, I am not sure of the space usage for this, but I expect it to be very performant since the joins do not need additional filters on the where clause for different types as well as complex "SQL join acrobatics"


Can not drop a PostgreSQL database using dropdb?

What you see there with psql \l only means that the Postgres role admin has been granted a couple of privileges by the Postgres role postgres - which is the default superuser and probably the actual owner of the database.

Quoting the manual:

  If a superuser chooses to issue a GRANT or REVOKE command, the command
  is performed as though it were issued by the owner of the affected
  object. In particular, privileges granted via such a command will
  appear to have been granted by the object owner.

The privileges granted are:

C -- CREATE
c -- CONNECT
T -- TEMPORARY

CONNECT and TEMPORARYon databases are also default default privileges for PUBLIC, but what we see here has been granted explicitly.

The privilege to drop the DB is not given, it cannot be. Only the owner of the database can drop it. (Or a superuser.) Hence the error message:

  ERROR:  must be owner of database

Typically, each shell user connects with a Postgres role of the same name. So, given you have the necessary sudo rights, this should do it:

sudo -u postgres dropdb my_database_name_here

Or switch to the  shell user postgres like demonstrated here.

But you may have to disconnect other users first. See:

Force drop db while others may be connected


Installing Oracle Express Database 18c (XE) On Oracle Cloud Free Tier Fails With ORA-00821: Specified value of sga_target | minimum requirements

How to Install Oracle Database 18c Express Edition on Oracle Cloud Free Tier (for life) | Centos 7

Credits :
1
2

 STEP 1 - Download Oracle Required Software

oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
oracle-database-xe-18c-1.0-1.x86_64.rpm

1. mkdir Downloads
2. cd /home/opc/Downloads
3. curl -L -O https://download.oracle.com/otn-pub/otn_software/db-express/oracle-database-xe-18c-1.0-1.x86_64.rpm
3. curl -L -O https://yum.oracle.com/repo/OracleLinux/OL7/latest/x86_64/getPackage/oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm
4. sudo yum localinstall oracle-database-preinstall-18c-1.0-1.el7.x86_64.rpm (Pre-Requistis only)

 STEP 2 - Change RPM To Avoid Issue 1 :

( Issue 1 : This system has 971 MB of RAM and does not meet minimum requirements )

Install & run rpmrebuild (if you don't have it already)

1. sudo yum install rpmrebuild
2. cd /home/opc
3. rpmrebuild -e -p /home/opc/oracle-database-xe-18c-1.0-1.x86_64.rpm
   ( this might take few mins )

Once rpmrebuild is opened :

1. Scroll down ( to bottom ) -> search for this line : [ $PhyMem -lt 1024 ]
2. Change to : [ $PhyMem -lt 102 ]
3. Press Esc -> type : wq!
4. Wait for RPM to be re-build ( it's auto and take few mins ).

Do the rpmrebuild modification :

1. Scroll down ( to bottom ) -> search for this line : [ $PhyMem -lt 1024 ]
2. Change to : [ $PhyMem -lt 102 ]
3. Press Esc -> type : wq!
4. Wait for RPM to be re-build ( it's auto and take few mins ).

Install Database from new RPM  :

1. cd /home/opc/rpmbuild/RPMS/x86_64/
2. sudo yum localinstall oracle-database-xe-18c-1.0-1.x86_64.rpm

 STEP 3 - Change Config File Before Database Configuration To Avoid Issue 2 :

( Issue 2 : sga_target 296M is too small )

Once step 2 completed - Edit File : /etc/init.d/oracle-xe-18c

1. sudo -s
2. cp /etc/sysconfig/oracle-xe-18c.conf /etc/sysconfig/oracle-xe-18c.orig
3. vi /etc/sysconfig/oracle-xe-18c.conf
4. search for "sga_target=" (/sga_target= in vi)
5. change from : MEMORY_CONSTRUCT="-initParams sga_target=${sga}M,pga_aggregate_target=${pga}M"
          To   : MEMORY_CONSTRUCT="-initParams sga_target=500M,pga_aggregate_target=${pga}M"
6. Save file.

 STEP 4 - Run Database Configuration :

( This may take up to 30 mins )

1. /etc/init.d/oracle-xe-18c configure


how to generate scripts for database objects specific to an application?

Even though you did not say it explicitly, I guess you removed the unnecessary objects from the restored database and now want to migrate the structure, and maybe some of the data, from the new (restored plus modifications) database to Production.

If this is correct, then I would recommend Red Gate’s SQL Compare (Structure) and SQL Data Compare (Data, of course) products.  Both of these work very well and are able to generate change scripts which you may modify further or let the products execute the changes directly.  Both products work by comparing the "From" and "To" databases to determine the needed changes.  I am not sure if both products are aware of version differences (Never had a downgrade issue) so I would check the generated scripts to see if they add anything which is not compatible with SQL Server 2000.  Also, both products are available on a trial basis.

If you are a user of Visual Studio and have the appropriate version level (With VS 2010, I believe it is Pro and higher), you might also consider using Visual Studio for Database Professionals.


How should I structure my time based index?

If a single column is used by a query for filtering, it can use an index if the that column is the left-most column in the index.

So assuming the TimeSheetDate is the left-most column in your index, you don't need to create another index for it.

Whether or not you should create another index in a reversed order is dependent on the selectivity of the UserCustomerKey column and whether queries filter using it.


SQL Server: What is the difference when backing up via .bak, BACPAC or just copying .mdf files?

the .bak file is far larger than the actual database.

The problem might be that you are just appending the backups to one single file. The default is to appended to the device. Take your backups with WITH COMPRESSION, INIT - compress your backup and all backup sets should be overwritten.

A .bacpac file is not a substitute for a full backup.. Also, using MDF/LDF files as backup is not a good habit.


Your Question is much too broad to provide any specific table design suggestions. Just this, "Which one has Employee", could mean any of dozens of things as mentioned in the Answer by Neil McGuigan: the person who first took customer inquiry phone call, the sales person who responded, the sales assistant who filled out Sales Order, the production manager who acknowledged receipt of the Sales Order, the workers who produced the items on the work order, and so on.

But I'll give you a simplified overview that might provide some of the insight you requested.

Business Transaction Life-Cycle

Using the definitions from Wikipedia, the usual work flow of vendor-customer is shown in this diagram. Colors mean:

Pink items = customer-vendor contact/interaction without documents
Blue items = documents from/to the customer
Yellow items = documents internal to the vendor.

After consulting with a sales person (optional), the customer submits a Purchase Order to a vendor, describing exactly what service/product they have decided to buy. If accepted by the vendor, a legally-binding contract is established.

The Sales Order is produced by the sales people of the vendor, reflecting the content of the Purchase Order but using the format and lingo that makes sense to the internal staff of the vendor.

The vendor's internal staff, the production-related managers (senior artisans, manufacturing managers, warehouse supervisors, etc.) create one or more Work Order documents directing their staff on what to do to render the services/products desired by the customer.

When any part or whole of the Purchase Item has been rendered to the customer, an Invoice is prepared by the vendor and delivered to the customer to demand payment. The Invoice should reference the Purchase Order number/id.

Lastly, the customer makes payment along with some kind of Remittance Advice referencing the Invoice number/id. This step completes the transaction (ignoring returns, Change Orders, or other issues).

The diagram above is not a table or entity diagram. For example, you may well have multiple work orders for one sales order. You may have multiple invoices if partial deliveries are made to the customer. Or, imagine if the manufacturing staff only builds two units of WonderWidget at a time to avoid wastage. So we must wait for another customer to order a second widget, while waiting we do not yet produce a Work Order. Meanwhile we can start on other items on the sales order, and we produce Work Orders for these early items. In this scenario we have a Many-to-Many relationship where any Sales Order can have more than one Work Order (customer wants several items with some of those items being produced while waiting for WonderWidget production to begin) while each Work Order can have more than one Sales Order attached (a pair of Sales Orders from different customers in case of double-WonderWidget production).

In-Person Retail

In-person retail is much simpler, with an work flow of three instantaneous steps, only two of which would be documents (or database entities).

A customer grabs some stuff from the store shelf, presents at the checkout counter, a register tallies up the items (akin to a sales order), payment is executed, and a sales receipt is produced. The Sales Receipt is basically the combination of the register's tally (sales order) plus the payment details (partial credit card number, date-time, and so on).

Again, this is simplified because we ignore returns and such issues.

Again this diagram is not a table or entity diagram.

Web/Phone Retail

With online web or telephone ordering, the work flow is probably a combination of the two above. If the customer pays immediately, then we don't need the Invoice & Remittance Advice. If it's a small shop, the Sales Order and Work Order may be the same single entity, whereas if this is an Amazon.com style business with multiple warehouses, then we will have multiple work orders.

Over-Simplified Partial Table Diagram

Just to give you a flavor as a newbie to table design, here is a grossly over-simplified design. Partial design, just for the sales order. This diagram uses simple crows-foot notation and Postgres data types. Abbreviations: pk means primary key, and fk means foreign key.


SELECT records based on return of COUNT query

This can be done using a window function:

select name, city, cnt
from (
  select name, city, count(*) over (partition by name) as cnt
  from names
) t
where cnt > 1;


SQL Always On COPY ONLY backups - what's the point if I cant restore the AG from these backups?

Copy only backups are generally used for situations where you need to restore the data for an unusual reason without disrupting the current backup chain (assuming there is one). Typical reasons are restores to dev boxes to test code or look at an irregularity in the data. You get the idea.

As a side note, taking backups from AG secondaries is kind of a donkey. If the AG is behind at all, your backups are behind. If you're not already using the secondary as a readable replica, they make it an expensive behind-backup device since now you have to fully license it (assuming you have software assurance, and all that).


How do I 'hint' the cardinality of a recursive CTE?

I've worked round the issue like this, but I'm hoping there is a less kludgy way:

explain analyze
with recursive w(n) as ( select 1 union all select n+1 from w where n<5 )
select * from w limit (select count(*) from w);
/*
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Limit  (cost=3.66..3.72 rows=3 width=4) (actual time=0.032..0.034 rows=5 loops=1)
   CTE w
     ->  Recursive Union  (cost=0.00..2.95 rows=31 width=4) (actual time=0.003..0.019 rows=5 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)
           ->  WorkTable Scan on w w_1  (cost=0.00..0.23 rows=3 width=4) (actual time=0.002..0.002 rows=1 loops=5)
                 Filter: (n < 5)
                 Rows Removed by Filter: 0
   InitPlan 2 (returns $2)
     ->  Aggregate  (cost=0.70..0.71 rows=1 width=0) (actual time=0.029..0.030 rows=1 loops=1)
           ->  CTE Scan on w w_2  (cost=0.00..0.62 rows=31 width=0) (actual time=0.005..0.025 rows=5 loops=1)
   ->  CTE Scan on w  (cost=0.00..0.62 rows=31 width=4) (actual time=0.000..0.002 rows=5 loops=1)
*/


The last two LEFT joins can be rewritten as INNER joins (due to the WHERE condition on the application_category table.

If application (name) is indeed unique, there is no difference if you group by that column or by application (id) or even by applicant (application_id) - the last one provided there is a FOREIGN KEY constraint from applicant towards application.

So, you could add an index on applicant (application_calendar_id, application_id)

and rewrite the query as:

SELECT
    COUNT(*) AS applicantCount,
    application.name,
    applicant.application_id,
    application.id
FROM  applicant
  LEFT JOIN application
    ON applicant.application_id = application.id
  JOIN calendar
    ON applicant.application_calendar_id = calendar.id
  JOIN application_category
    ON application_category.id = calendar.category_id
WHERE application_category.id = '886760e6d0b6ab306b0914a60ac4265e'
GROUP BY applicant.application_id ;


To locate the rows you want to see, you must do two things:

change the way you create the table
change the way you load data into the table

Why Change the Way You Create the Table ???
When you did this
create table tgt as select * from src;

You create the tgt table without any indexes. You can verify this by running
show create table src \G
show create table tgt \G

and you will see src with its indexes and tgt without them.
Consequently, with no indexes around, the table load would be the fastest. If you at least had a primary key. you could navigate the table by some numeric id column.
So, to create table with the indexes in place, do this:
create table tgt like src;

You could then run
show create table src \G
show create table tgt \G

and see that they are the same in structure.
Why Change the Way You Load Data into the Table ???
You could just start loading like this
insert into tgt select * from src;

When the load is done, you could run
SELECT * FROM tgt WHERE id = 265975;
SELECT * FROM tgt WHERE id = 265976;

You could get an glimpse at when the row looks like but the created_timestamp may not be populated. If it is populated correctly, then the problem is essentially solved.
Another Way to See Which Row Without Loading Data
You could isolate which rows have an issue with the created_timestamp by doing something else
# mysqldump --skip-extended-insert mydb src > dumpfile.txt
# head -265976 dumpfile.txt | tail -2

This will show you the rows that would have been inserted.
This is all the help I can suggest since I do not know what the table structures are.


SQL Server expensive nested loops join and lazy table spool

I would try removing the OR clause in the join between document and processingentitymapping

You could do that with UNION

SELECT distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
FROM datagatheringruntime dgr
INNER JOIN processentitymapping pem on pem.entityid = dgr.entityid
INNER JOIN document d on d.entityid = pem.entityid
LEFT JOIN PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
WHERE rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
UNION
SELECT distinct d.documentpath as path, d.documentname as name, d.datecreated as created, pc.DateProcessed
FROM datagatheringruntime dgr
INNER JOIN processentitymapping pem on pem.entityid = dgr.entityid
INNER JOIN document d on d.unitofworkid = pem.processid
LEFT JOIN PendingCorrespondence pc on pc.PendingCorrespondenceId = d.PendingCorrespondenceId
WHERE rootid = @P0 and dgr.name in('cust_pn', 'case_pn')
OPTION(RECOMPILE);

The reason being that the table spool is feeding the NESTED LOOPS operator

And on this nested loops operator is the OR predicate.

Filtering out until we have 9 rows remaining.

Changing the OR to a UNION should remove the spool, you might have to look into indexing after removing the OR.

Indexes that could improve performance after rewriting with UNION

CREATE INDEX IX_EntityId
on document(EntityId)
INCLUDE(DocumentPath, DocumentName, DateCreated, PendingCorrespondenceId);

CREATE INDEX IX_UnitOfWorkId
on document(UnitOfWorkId)
INCLUDE(DocumentPath, DocumentName, DateCreated, PendingCorrespondenceId);

See here for another example on this


StartsWith query using LIKE is returning the wrong results on a non ascii column

SQL Collations are provided only for backward compatibility.

This has been the case for over 10 years.

Use a more modern collation such as Romanian_100_CI_AS.

This does not produce that behaviour.


Get parent/child or parent/siblings depending on the ID passed

Your first query can be simplified to:

SELECT a2.id, a2.title
FROM article a2
WHERE a2.parentid = 61

To combine this with your second query you can use union:

SELECT a2.id, a2.title
FROM article a
JOIN article a2
    ON COALESCE(a2.parent_id, a2.id) = a.parent_id
WHERE a.id = 61 AND a2.id <> 61

UNION

SELECT a2.id, a2.title
FROM article a2
WHERE a2.parentid = 61;


How is the "Adjust memory quotas for a process" user right assignment used in SQL Server 2019?

Can anyone confirm why this is required and what it's function is within the SQL Engine

This privilege when granted to user account will give ability to the account to adjust maximum memory that other process can use. See the official document

This privilege determines who can change the maximum memory that can be consumed by a process. This privilege is useful for system tuning on a group or user basis.
This user right is defined in the Default Domain Controller Group Policy Object (GPO) and in the local security policy of workstations and servers.

The SQL Server, SQL Server agent. SSAS service account should have this privilege to adjust the quota if DB engine deems necessary. But also note that if this is given to user account which is used to run certain queries it can also lead to Denial of Service attack( DoS) by changing the quota.


Rsync'ed postgres databases won't allow log-on, gets "cache lookup failed for relation" error

Make sure that postgres is killed on the side you are rsyncing from as well as the side you rsynced to (you goofus).

Smart Peter: After you ran rsync what files did it copy?
Goofus Peter: Um almost everything, except the network stuff, I needed that
Smart Peter: so,  everything like all the pid's and cron and everything
Goofus Peter:  I guess so, but postgres was off on the machine I was copying from
Smart Peter: Well, was postgres on on the machine you were copying too?
Goofus Peter: Oh crap

... Later ...

Goofus Peter: Hey, it still didn't work, and now I can't even start postgres!
Smart Peter: Is it running
Goofus Peter: How could it be running if I couldn't even start it?
Smart Peter: Because it was running all along!
Goofus Peter: (gets cut off)
Smart Peter: Because you have a cron job that restarts it
Goofus Peter: But I killed cron
Smart Peter: Did you kill it, or did you stop the service
Goofus Peter: Stopped the service...
Smart Peter: So basically, you left the bodies and moved the headstones?
Goofus Peter:  OK I'll make sure everything is dead and call you back

... Time passes ...

Goofus Peter: OK everything worked
Smart Peter: Great, I'll go tell stackexchange!


Personally I would schule it using the Windows Scheduler. You might want to get robocopy if you are copying your backup files to another server. Having an email client that you can send emails from DOS can be helpful. But otherwise you would just write .BAT files and or a Powershell file. You want a process that creates a backup log and checks that log for errors. Then you can send out emails when there are errors. There should be something already written online somewhere.


Nearest neighbours (KNN) from a selected point

With a SELF JOIN, in the form of an inner join if you know the distance. Or a cross if you don't.

SELECT spatial_id, geom
FROM rhp_tvp.spatial AS rt1
JOIN rhp_tvp.spatial AS rt2
  USING ST_DWithin( rt1.geom, rt2.geom, MAX_DISTANCE )
WHERE rt1 ... -- SELECT POINT.
ORDER BY rt1.geom <-> rt2.geom
LIMIT 10

If you don't know the max distance of one point to another,

SELECT spatial_id, geom
FROM rhp_tvp.spatial AS rt1
CROSS JOIN rhp_tvp.spatial AS rt2
WHERE rt1 ..  -- SELECT POINT
ORDER BY rt1.geom <-> rt2.geom
LIMIT 10;


SQL Server 2016 in-place upgrade time for large databases

I'd really recommend not doing an in-place upgrade; certainly not for a very large system like yours.  The reasons for this recommendation are many, and have been covered extensively by Mike Walsh in his answer on a separate question.

If you really need to do an in-place upgrade, the only real way to determine the amount of time required is to do a dry run on separate, identical, hardware.  This would involve installing SQL Server, and using backup-and-restore to copy all 1,000+ databases to the test server, then performing an in-place upgrade.  I'd recommend doing this in the very minimum since it allows you to test SQL Server in your environment prior to committing to it in production.


Design problem with placing proper constrains

Lets start first with the part of the design that seems OK. You have Member and Donor and PeopleInNeed. It's all right to have 3 tables, one for each entity:

Donor
    DonorID      PK
    Name
    Age

Member
    MemberID     PK
    Name

PeopleInNeed
    PeopleID     PK
    Name

The problem is in the Action and Donation tables. You say:

Member can create many action for collecting donations and people in need can have many actions for them.

I take that to mean that Actions are created by a member (so a 1-to-many relationship between Member and Action).

Also that PeopleInNeed may be related to multiple Actions and an action may be related to multiple people in need (so a many-to-many relationship between PeopleInNeed and Actions.

Actually, one particular action may be related to only one people in need. I meant that one people in need can have more that one different actions for them. That is way I put both MemberID and PeopleID as PK's in Action table.

The Action table becomes:

Action
    ActionID     PK
    MemberID          FK1 -> Members
    PeopleID          FK2 -> PeopleInNeed
    ActionCreatedDate

Then we have Donations. You say:

Now the key thing is that donor can donate only once for a particular action.

That tells me that Donation is just a many-to-many table between Donor and Action.

Donation
    DonorID      PK   FK1 -> Donors
    ActionID     PK   FK2 -> Actions
    DonationDate
    Amount

You could make the (DonorID, ActionID) a UNIQUE constraint instead, if you absolutely need a surrogate DonationID PK as well.


You are missing a few points:

there could be a GiST index supporting an exclusion constraint

an index on an expression can help the optimizer get better estimates for that expression, even if it isn't scanned

I recommend the query from my blog:
SELECT s.schemaname,
       s.relname AS tablename,
       s.indexrelname AS indexname,
       pg_relation_size(s.indexrelid) AS index_size
FROM pg_catalog.pg_stat_user_indexes s
   JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE s.idx_scan = 0      -- has never been scanned
  AND 0 <>ALL (i.indkey)  -- no index column is an expression
  AND NOT i.indisunique   -- is not a UNIQUE index
  AND NOT EXISTS          -- does not enforce a constraint
         (SELECT 1 FROM pg_catalog.pg_constraint c
          WHERE c.conindid = s.indexrelid)
ORDER BY pg_relation_size(s.indexrelid) DESC;


Could not connect to postgres after installing 9.4 and uninstalling 9.3

I resolve the problem changin the port from 5433 to 5432


How to retrieve stored procedures from raw data

Stored Procedures are physically stored in the mysql.proc table as a MyISAM table

Simply copy

/var/lib/mysql/mysql/proc.frm
/var/lib/mysql/mysql/proc.MYD
/var/lib/mysql/mysql/proc.MYI

If the new installation is the same major version of mysql, copying should do it for you

It would be safer to go back to the old setup, start mysql, and dump the stored procedures.

See my old post Dump only the Stored Procedures in MySQL