It's not an easy task to plan the dimensions of shards, and there is no fool or errorproof way of doing it.

What I tend to do is to create a certain fraction of the expected data per shard as dummy data. If I have a shard handy, I fill it completely.

In the next step, I generate the same fraction of the expected load on a matching number of front-end servers.

Now, I analyse the memory consumption of the indices and overall. Knowing the index size, you roughly know the minimum RAM you need. In order to have enough warning time and a decent amount of RAM for connections, the  working set and other processes, I round up to the next sensible value for RAM sizes. This way you make sure your RAM does not get filled up easily, you have a decent warning time when you have too scale out because of insufficient RAM and your application won't be throttled by your database.

Let me illustrate that. Given my average object size is slightly under 1kB and I plan to have a 1TB partition, but I only have a 256GB partition for tests, I'd create somewhere in the order of 250 million dummy documents. Now, let's say we plan to have 8 front-end servers for the application, I'd set up two of them.

Next, I'd use a load testing tool (there are many, and they are out of scope of this answer) to generate load on those two front-end servers.

After you have done that, you should have a quite accurate indication of overall memory consumption, index size and working set size.

Let's say we find out that our indices are 20GB in size and 300 connections are made. So, let's just right say 21GB. So our shard should have four times this RAM, rounded up to the next sensible value (keep in mind cost efficiency!) So, let's assume that would be 96GB.

Now, you should find out wether it makes more sense to have two shards instead of one, each with 48GB of RAM and 500GB of disk space for MongoDB, respectively. This may be the case, because bigger SSDs can be disproportionately expensive.

I think you get the picture: There is no easy formula, you simply have to do your homework ;) My shortish might or might not work for you. If it doesn't, you have to work one out yourself.

Oh, and whatever you do when planning a shard: plan with SSDs. It's worth it.


Grouping and counting rows by existence of matching criteria in any of several columns

SELECT COUNT(a.*) as Total, "Fail" as Status
FROM MyTable as a
WHERE a.Q1 = "NO" or a.Q2 = "NO" or a.Q3 = "NO" or
    a.Q4 = "NO" or a.Q5 = "NO" or a.Q6 = "NO" or a.Q7 = "NO"
GROUP BY "Fail"
UNION ALL
SELECT COUNT(b.*) as Total, "Pass" as Status
FROM MyTable as b
WHERE b.Q1 <> "NO" and b.Q2 <> "NO" and b.Q3 <> "NO" and
    b.Q4 <> "NO" and b.Q5 <> "NO" and b.Q6 <> "NO" and b.Q7 <> "NO";
GROUP BY "Pass"

The syntax may have some problems, but I think this will do it for you. If you have lots more columns to add, then it isn't too difficult to write them out once and then copy, paste, and replace in notepad.

You may need to wrap all the column comparisons in a NZ() to protect against null values. Example: WHERE Nz(b.Q1,"") <> "NO"


Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

If you ONLY want to optimise for this query. This is the best index:

ALTER TABLE items ADD INDEX (category, created_at, user_id)

This optimises the value of the filters, which reduces the total amount of data you touch. By adding user_id, item_id at the end of the query, you make the index covering and it saves you a lookup into the primary index.

We can assume that item_id is NOT NULL (as it is the PRIMARY index).

However, because the MySQL optimiser is pretty stupid, you may need to rewrite like this:

SELECT  user_id, SUM(count) AS count
FROM
(
  SELECT category, created_at, user_id, COUNT(*) as count
  FROM items
  WHERE category = 'magazine'
  AND created_at > 1384754400
  GROUP BY category, created_at, user_id
) AS d
GROUP BY user_id
ORDER BY count DESC
LIMIT 100


With a full backup you can only restore to the point in time where the backup was taken.

With a incremental backup you can only restore to the point in time where the backup was taken, but you also need the full backup on which the incremental is based upon.

If you want a true point in time recovery, you have to enable the binary log (as described here).
In a restore scenario, you would recover a full/incremental backup and then rollforward to your point in time usinge the 'mysqlbinlog' command as described here.


One option is to drop the type and then re-create it. You will have to modify all the modules that use it first, so a better and less disruptive option might be to create a new table type with the right data type, modify each of the modules to point at the new type, then drop the old one.

The syntax is simply:

DROP TYPE dbo.TableValue_Test;

However this will fail while modules still reference the type. If the TVP is only referenced in client code, you might have less to worry about, but you should still update the app code to use the right data type from end to end.


Why does COALESCE function not work for this query?

To answer your question

COALESCE would be utterly pointless where you placed it to begin with. The aggregate function count() never returns NULL. And logic dictates that num_60_79 can never be lower than 1 in the subquery. Related:

Query optimization or missing indexes?

But that's irrelevant, because if a subquery returns no row, then it cannot return any values at all.

To be precise, in your case the subquery may return any number of rows but, when joining to second_table, if there is no row with matching values in (assignment_id, num_60_79) then all columns from the subquery b are filled with NULL values instead. Related:

Multiple correlated subqueries with different conditions to same table

NATURAL FULL JOIN is a very exotic way to join tables. Especially for somebody still learning how to use COALESCE. Quoting the manual:

  NATURAL is shorthand for a USING list that mentions all columns in
  the two tables that have the same names.

and:

  FULL OUTER JOIN returns all the joined rows, plus one row for each
  unmatched left-hand row (extended with nulls on the right), plus one
  row for each unmatched right-hand row (extended with nulls on the left).

I think it's safe to assume there is no column named num_60_79 in second_table and you surely wouldn't want to involve it in the join conditions if there was one. You only want to join on the column assignment_id, and that's what you should put in the query instead of the NATURAL key word.

Also, while FULL JOIN is theoretically possible here, we would typically see a LEFT JOIN.

You also don't need two layers of subqueries and some other noise in the query.

Proper query

All things considered, while you are ...

  trying to count the number of columns that is retrieved from the subquery

... my educated guess is you want this query instead:

SELECT s.*, COALESCE(b.num_60_79, 0) AS num_60_79
FROM   second_table s
LEFT   JOIN (
   SELECT assignment_id, count(*) AS num_60_79
   FROM   avg_required_table
   WHERE  mark_as_percent >= 60
   AND    mark_as_percent <  80
   GROUP  BY assignment_id
   ) b USING (assignment_id);


How to handle big data update based on excel spreadsheet

A Fast Method

A probably faster way to do this is the following:

Provide the data in a database table
Execute a MERGE statement that merges the data from this table into the source table

To provide the data in a database table you can use sql loader or [external tables].

http://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606

create table big_tab(
  id number primary key,
  value1 number,
  value2 number,
  value3 number)
;

create table mod_tab(
  id number,
  value1 number,
  value2 number,
  value3 number)
;

Now assume you have the following data in big_tab.

SQL> select * from big_tab;

    ID     VALUE1     VALUE2     VALUE3

     1         11         21         31
     2         12         22         32
     3         13         23         33
     4         14         24         34
     5         15         25         35

you have a csv file mod_data.csv with the following values

1,111,121,131
3,113,123,133

you can load this into the table with sqlldr load.ctl DIRECT=true with the sql loader control file sql.ldr containing

LOAD DATA
INFILE "mod_data.csv"
TRUNCATE
INTO TABLE mod_tab
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  id char,
  value1 char,
  value2 char,
  value3 char
)

Using the option "DIRECT=true" will be faster thean loading without this option.
Now you have

SQL> select * from mod_tab;

        ID     VALUE1     VALUE2     VALUE3
---------- ---------- ---------- ----------
         1        111        121        131
         3        113        123        133

you can merge this into big_tab with the following statement

MERGE INTO big_tab
using mod_tab on (big_tab.id=mod_tab.id)
when matched then update
  set
    value1=mod_tab.value1,
    value2=mod_tab.value2,
    value3=mod_tab.value3
;

and get

SQL> select * from big_tab;

        ID     VALUE1     VALUE2     VALUE3
---------- ---------- ---------- ----------
         1        111        121        131
         2         12         22         32
         3        113        123        133
         4         14         24         34
         5         15         25         35

If the processing was ok you can cleanup

    truncate table mod_tab;

An Even Faster Method

If you have a lot of data to modify, there is a way that is even better (at least if your io system is fast):

create a auxiliary table with the same structure as big_tab table
insert the modify rows in this auxiliary table
insert the rows from big_table that will not be modified in the auxiliary table
drop the big_tab table
rename the auxiliary table to big_tab.
add constraints and indexes to the new big_tab table

This can be don by the following script

create table aux_tab as select * from big_tab where 1=0;
alter table mod_tab add  primary key (id);
insert /*+ APPEND */ into aux_tab select  big_tab.id id,
    decode(mod_tab.id,null,big_tab.value1,mod_tab.value1) value1,
    decode(mod_tab.id,null,big_tab.value2,mod_tab.value2) value1,
    decode(mod_tab.id,null,big_tab.value3,mod_tab.value3) value3
  from big_tab, mod_tab
  where big_tab.id=mod_tab.id(+)
;
drop table big_tab;
rename aux_tab to big_tab;
alter table big_tab add  primary key (id);
alter table mod_tab drop  primary key;
truncate table mod_tab;

And Even Faster

You should always do your load and insert in direct path mod. Therefore I added and APPEND hint to the insert statement and the DIRECT=true option to the SQL*Loader command. But you can try to parallelize some actions to get even faster. Parallel statement work optimal on partitioned tables so it may make sense to partition big_tab and also mod_tab and aux_tab.


Transactional Replication hangs when huge transactions flow

I've experienced a similar problem. It turned out to be an issue with when the "Agent history clean up: distribution" job kicks-off on the distributor. This job would hang for a long time and bring replication to a stand-still when there were a lot of transactions in the queue. Check the History for the Job to see if it's stalling at the same time you have your problems.

The solution we implemented was to push through transactions in Batches with a WAIT between batches. we had to experiment with the batch size and Wait time to get the sweet-spot that didn't cripple the distributor.


Using RDBMS with pseudo foreign keys VS using NoSQL solutions

TL;DR. Unless you have a compelling niche case, then make  full use of the capabilities of your RDBMS!

Hmmm... you say:

some database administrators for our org

What do the others say?
You also write:

it is preferred to simply represent this with a pseudo foreign-key ID column

What, exactly, is a "pseudo foreign-key ID column"?
The whole point (and greatest benefit) you obtain from using FOREIGN KEYs (FKs) is that you have automatic enforcement of Declarative Referential Integrity (DRI) - see below. This functionality of the database system is deliberately inflexible when changing data. This is for your developers' benefit, your DBAs' benefit, your company's benefit and most importantly of all, for your customers' benefit!
The modern RDBMS (most of them anyway) is a complex programming environment in its own right that you can leverage to maintain maximum control of your data as close as possible to your data.
You can effectively have fine-grained control of the data next to the data which is the best place for it. Your database is the last bastion of defence against rogue applications (honest errors for the most part) and/or DBAs/Sysadmins (again, mostly honest) modifying your data in a way that will render it inconsistent.
Inconsistent data can be worse than useless - if you know your data is corrupt, you can restore from backup, but if it's inconsistent, mission-critical decisions could be made on the basis of erroneous premises!
Your DBAs say:

that inserts and deletes (especially cascading ones) become prohibitively expensive as the database scales out

Yet, you appear to be implementing your home-baked RI scheme using pseudo foreign-key ID columns? This adds its own costs - e.g. for an INSERT into a child table, you have to (in the app - and all within one transaction) query the database (presumably checking for the existence of parents?) and then do your (consistent) INSERT and then COMMIT?
For a DELETE and/or an UPDATE on a parent, you have to check for the existence of children, then DELETE or UPDATE the child according to a rule (CASCADing) and then DELETE/UPDATE the parent all within one transaction. Setting DRI in the database removes all that overhead! See here and the rest of that thread.
In my opinion what becomes far more prohibitively costly is lost data, orphaned records, childless parents and late-night calls from irate customers threatening to pull the plug on your company and its software!
Yes, there is a certain price to be paid for enforcing DRI. Your DBAs are suggesting that you enforce this in the app - yet this is also costly and it's far more expensive, far far more error-prone and far far more time-consuming to do it in the app than it is to do it using the RDBMS (see example above).

Apps come and go.
Languages come and go (apart maybe from C).
Frameworks come and go.
It was XML 10 - 20 years ago, now it's all JSON...
Fortran/Cobol -> C -> C++ -> Java -> Scala -> Clojure -> Go -> Rust -> whatever-you're-having-yourself...
Data is permanent (or at least, very long-lived in comparison to the above)!

The youngest of the mainstream RDBMS products, MySQL, was first released 23 years ago and it was only meant to be a lightweight copy of systems such as Oracle (1979), Ingres (mid-eighties or earlier - was important at the time), Sybase/MS SQL Server (1984), PostgreSQL (also mid-eighties or earlier) and Interbase/Firebird (1984 - also important at the time).
So, these systems have been around a lot longer than your app.
What your DBAs are proposing is to reinvet the wheel - a wheel which works very well for millions of users every day in banks, supermarkets, factories, offices and warehouses (non-exhaustive list) all over the world.
There are a few main issues here.

Who knows? Maybe you have an excellent team of hot-shot programmers who can implement mainstream RDBMS capabilities in, say, less than 3 years?
Ehh... why am I not buying that scenario? Well, the "cheapest" (but by far the better IMHO) of the two Open Source systems above - PostgreSQL - cost $15M and 269 work-years of effort and MySQL cost $63.5M and 1155 work-years. Do you think your team can match that?

You have your database software - by trying to circumvent/rewrite its DRI capabilities, you are literally throwing away the years of programming effort and testing by them and (effective) testing by their (literally) millions of end-users.

Suppose, one bright, sunny morning, your boss/customer/tax-authorities/whoever decides that you need to write a new application to work with your database? You'll have to implement all of your DRI all over again! And probably in a different language because your boss has read in some in-flight magazine that X is the language du jour. It'll be like déjà vu meets Groundhog Day all over again!

You then hire a contract whizz-kid programmer who wrote a system in X for his undergrad thesis. He doesn't know that column_A in table_W is meant to be the child of column_B in table_Z, so he happily produces hundreds of lines of code a day, that work swimmingly and quickly on your test data.
Then it lands with the customer and they start to notice that records are going missing - sums/record-counts are incorrect. Your contractor has moved on to a startup (Cowboys 'R Us) and you're left holding the baby! (This scenario could also pan-out in your current set-up).
Check out these three articles

I can't wait for NoSQL to die - an admittedly jaundiced view of NoSQL, but not so much if you consider the following articles,
what happens when the rubber meets the road and (ahem...) advantage was taken of NoSQL's eventual consistency (BASE) model,
Finally, how NoSQL systems are now finding that SQL and consistency weren't really that bad after all - come back ACID/SQL, all is forgiven!

I'm not saying that NoSQL systems don't have a "niche", it's just that a niche is precisely what they do have (much in the same vein as above). Unless you have very specific needs which are clearly aligned with a NoSQL use-case, then they are best avoided  (IMHO).
If the above hasn't convinced you, then consider these final points:

Read this - point no. 2 from Jonathan Lewis. He wrote a book (530 pages on Oracle optimiser fundamentals), so he knows a thing or two about databases!

More war stories, for fans of Chapter 8! "Now prepare yourself to
read all about 'The World's Worst Oracle Project.'" - Jonathan Lewis.
This chapter describes some of the most common mistakes in development
Oracle database applications. You'll certainly recognise some of them,
because so many people stubbornly cling to certain beliefs. I know I
like to bring up several of his points when I get into common
arguments like these:

We want our application to be "Database Independent."
We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.

NOTE - point 2. - that's exactly what you are proposing to do!

Look at Brian Aker's amusing talk on NoSQL here - he makes the point that NoSQL is OK for ad-hoc queries where you're dealing with stale-ish data, it's just that SQL is great at slicing and dicing data also.

The key advantage though with SQL is that you haven't to write a new Map-Reduce job or whatever to analyse your data, you can just write a query, et voilà, you're done! If you're not using FOREIGN KEYs in your RDBMS, you're effectively in a half-way house between SQL and NoSQL!

There are old-wives' tales about FKs being unnecessary overhead. This notion was kept alive by the fact that (for good, but probably now legacy, notably space, reasons) some RDBMSs did not/don't automatically create indexes on Foreign Key fields (this should be done in the majority of cases). This led to the mistaken view that Foreign Keys were overhead without benefit which is not true for the vast bulk of use-cases!
You say:

if so, why would you even want to use an RBDMS?

This is a very good question. If you're not using the RDBMS as an RDBMS, then why have it at all? Just put the data into Notepad/vi!
You should have a serious sit-down with management and explain why using the RDBMS' capabilities is a good idea. Furthermore, I would ask your (No-FK) people to justify not using FOREIGN KEYs. Unless your company has a real and pressing NoSQL niche, then point them to the works of Micheal Stonebraker and/or Jonathan Lewis on this.
Finally, my advice to you, if your company/management insists on pursuing this madness, is to run very very far, very very fast! You will end up in a situation where you are constantly firefighting, being woken from your bed at 03:30 and worse still, you won't get a chance to improve your skill set or learn anything of value.


Which database should I look at for analyzing ~30gb, 700M records dataset?

(Too long for a comment...)

MySQL...

SHOW VARIABLES LIKE '%buffer%'; -- the main configuraton is the cache allocation.  But it depends on which Engine you are using and how much RAM you have.

  pretty normalized, specifically joining, grouping, slicing and dicing the 3 tables

Sorry, but we need to check the details.  Please provide SHOW CREATE TABLE, some slow queries plus their EXPLAINs.

Are you CPU-bound?  Then the queries and/or indexes need attending to.

Are you I/O-bound?  Then probably indexes.

Edit 1

Glancing at the "schema", I see something naughty:  diagnosis_code is NUMBER in one table and VARCHAR(30) in another.  This makes it impossible to use an index when JOINing.

So, I repeat, let's see the MySQL version of CREATE TABLE and the queries.


Restoring Database, creating new copy and use existing transaction logs for the original backup database to restore the NEW database to be current?

That won't work. As soon as you recover the database after restore, that begins a new timeline for the database.

If this is an on-going process, one possible solution is to get the team generating the backups to take a differential every day which will have fairly low impact on effort and system load. This is in addition to what they currently have. However, it will require some careful management of the files to ensure you have the right sequence of full backup, differential and tlogs. The benefit is, you will not have more than the 20 tlog files to apply if you ever need to restore the database to the latest version. You also don't lose existing PITR based on the 15-20 tlog backups taken daily (I'm assuming there is some PITR requirement given the number of log backups).

If you want to further reduce the number of log files to apply, you can increase the number of differential backups per day but I'd wager the added complexity in managing the files far outweighs the benefits. If you script all your tasks (sounds like you are), execution and scrutinizing for issues is fairly easy to do. If you're using the SSMS GUI, well, you really shouldn't.

** To make it clear, this assumes you already reset the backup chain with a full database backup at some interval. Having just a single full DB backup and only taking differentials and tlog backups from there on is not a viable solution. In most situations, the differential will eventually grow to a large size, possibly bigger than the base (full DB) backup if you have lots of write activities. An example of a basic backup cycle might look like:
 - Full DB backup - every Sunday 2am
 - Differential DB backup - every day
 - 8pm Transaction log backup - every 15minutes
This cycle has potential data loss of up to 15 minutes if there are no other protections (e.g. AlwaysOn) in place.

If this is just a 1-time thing, i.e. you won't get any more tlog backups then you could reduce your recovery time when the goal is to get to the latest point-in-time available. Just restore the DB, differential and apply all the logs. Once done, take a backup of that and keep it along with all the other backup files you have. This buys you rapid recovery when you need to get to the latest version available since you only need to do a single DB restore. However, don't delete all the other backup files in case you need to restore to a specific point-in-time other than the most recent.


In Oracle how do I save a sequence.nextval in a variable to be reused in multiple inserts?

I think it goes like this

DECLARE
    ENABLED_USER_ID PLS_INTEGER;
    DISABLED_USER_ID PLS_INTEGER;
BEGIN
    ENABLED_USER_ID := SEQ.NEXTVAL;
    DISABLED_USER_ID := SEQ.NEXTVAL;

    INSERT INTO USERS (ID, USR_NAME)
    VALUES  (ENABLED_USER_ID, 'ANDREW');

    INSERT INTO CAR (CAR_ID, CAR_NAME, USR_ID)
    VALUES (CARSEQ.NEXTVAL, 'FORD', ENABLED_USER_ID);

    INSERT INTO USERS (ID, USR_NAME)
    VALUES  (DISABLED_USER_ID, 'ANDREW');

    INSERT INTO CAR (CAR_ID, CAR_NAME, USR_ID)
    VALUES (CARSEQ.NEXTVAL, 'FORD', DISABLED_USER_ID);
END;
/


Postgresql Update Table row values by Grouped Column

Simple answer - as the dummy value in api_id doesn't matter: use the call_id:

update the_table
    set api_id = call_id::text
where api_id is null;

Complicated answer:

Assuming you have some way of generating a random string, you could do something like this (untested):

update the_table
   set api_id = t.random_string
from (
  select distinct on (call_id) call_id, random_string() as random_string
  from the_table
  where api_id is null
  order by call_id
) t
where t.call_id = the_table.call_id
 and the_table.api_id is null;

This generates one row with per call_id in the inner query and generates a random value for each of that. This result is then used to update the target table.

The above assumes that if there is already value for a specific call_id there is one for all rows. It won't "fill" in missing api_id for other rows (e.g. if there was a row with call_id = 123 but no api_id)


In SQL Server 2016, what is the difference between Always Encrypted and Transparent Data Encryption?

Downsides of Transparent Data Encryption compared to Always Encrypted:

Only protects data at rest - backups and data files are "safe" but data in motion or in memory is vulnerable
Whole database only
All data is encrypted the same way
Backup compression can take longer and be counter-productive

Well, actually, there are some improvements here in SQL Server 2016 that defy what we've typically known about trying to compress encrypted data - it's much better than previous versions, but presumably still worse than only encrypting a handful of columns (untested)

tempdb also inherits encryption – stays even after disabling TDE
Requires Enterprise Edition
Data always accessible to sysadmin

Always Encrypted addresses all of these issues in part or in full:

Data is protected at rest, in motion, and in memory - much more control over certs, keys, and exactly who can decrypt data
Can be just a single column
Encryption type is a choice:

Can use deterministic encryption to support indexes and point lookups (say, SSN)
Can use random encryption for higher protection (say, credit card number)

Since it's not database-wide, backup compression isn't necessarily affected - of course the more columns you encrypt, the worse luck you'll have
tempdb is uninvolved
As of SQL Server 2016 Service Pack 1, Always Encrypted now works in all editions
Data can be protected from sysadmin (but not sysadmin AND Windows security/cert/key admins, in other words you can separate responsibility as long as those two groups don't collude)

There is a limitation, though, and that is that not all drivers and applications can deal with the encrypted data directly, so in some cases this will require updating/changing drivers and/or modifying code.


Oracle shrinking / reclaiming free tablespace space

I agree with EdStevens' comment, but here are some other ideas:
The AskTom website  has a script that will list exactly how much you can shrink the existing data files.
If that doesn't yield much savings, you can find the biggest indexes on that tablespace and rebuild them; that is a temporary solution and the indexes would eventually get big again, but if budget doesn't allow more disk space now, try this query:
select segment_name, sum(bytes)/1024/1024 as mb
from dba_segments
where tablespace_name='TB1' and segment_type like 'INDEX%'
group by segment_name
order by mb;

Then if you find an index named  ind_big_stuff is huge, then
alter index ind_big_stuff rebuild online;

Also, if your application is a commercial application, there may be several tables with zero rows.  Or if you have partitioned tables, you may have some partitions with zero rows.  You can drop space allocated to them with
exec dbms_space_admin.drop_empty_segments(schema_name=>'HR');

You can use the segment creation deferred clause on an alter table statement for things like partitioned tables, or tables you know will not have any rows.


Restrict user to only take COPY_ONLY backups

No there is no option or restriction which we can enforce so that a user can take copy_only backup when ever he tries to take any backup but what you can do is create a procedure which backups database with copy only option and grant execute on the procedure to various users and then remove backup database privilege from the user.

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. So move accordingly. Its really not a good practice to allow general users to backup database as per there mood but I guess requirements vary as per different firms.For more details read Security section in This Link

  Is there a way to force users to only take a full backup with COPY_ONLY so they don't interfere with diff backups

Let me tell you there is no concept of copy only differential backup. It only applies to full backup and transaction log backup.

The other option is through social engineering by educating the developers about possible issues which can occur with normal full and log backup may be about if they take log backup it might break LSN chain and Log shipping would fail and they would be responsible. There is also a simple option to ask developers to use inbuilt script kept at some shared location(hope you have one in your firm) and make sure you have copy only clause in that scripts. I guess everybody would find it helpful to use already written script instead of writing by own or taking by GUI.


Oracle partitioning scenario with primary key absolutely required

Yes, reference partitioning requires a primary key on the parent table.

  Reference partitioning enables the partitioning of two tables that are
  related to one another by referential constraints.

  The partitioning key is resolved through an existing parent-child
  relationship, enforced by enabled and active primary key and foreign
  key constraints.

If you try to do this with a unique key instead of a primary key, you will get this error:

ORA-02268: referenced table does not have a primary key


What is a secure strategy for managing login/passwords used in a powershell script

Your best option would be to create the passwords in a file (or table) using ConvertTo-SecureString and ConcertFrom-SecureString. You will need to utilize the "-Key" or "-SecureKey" parameters to allow the service account with SQL Server Agent to read the password into your script.

The best write-up I've come across for this process is found here and here. Which the code snippets of interest from the articles:

#Encrypting password on machine 1
$File = "\\Machine1\SharedPath\Password.txt"
[Byte[]] $key = (1..16)
$Password = "P@ssword1" | ConvertTo-SecureString -AsPlainText -Force
$Password | ConvertFrom-SecureString -key $key | Out-File $File

#Accessing the password on machine 2
$File = "\\Machine1\SharedPath\Password.txt"
[Byte[]] $key = (1..16)
Get-Content $File | ConvertTo-SecureString -Key $key

The general premise being that you can use the first process to save your passwords to a file, or table. Then in your SQL Agent job read the password back in a semi-secure manner. It is not the most secure because if anyone finds the key, they can obviously read the password as well.

As well, in the end you are likely going to be passing it as plain text to the destination, unless it accepts use of a PSCredential object.


How to add a step in all existing jobs in sql server

I have created a stored procedure which will add a new first step to any existing agent job.

It checks if running on the AG primary replica and quits with success otherwise:

use master
go

-- Adds a first step to specified job, which checks whether running on Primary replica

create procedure AddAGPrimaryCheckStepToAgentJob
    @jobname nvarchar(128)
as

set nocount on;

-- Do nothing if No AG groups defined
IF SERVERPROPERTY ('IsHadrEnabled') = 1
begin
    declare @jobid uniqueidentifier = (select sj.job_id from msdb.dbo.sysjobs sj where sj.name = @jobname)

    if not exists(select * from msdb.dbo.sysjobsteps where job_id = @jobid and step_name = 'Check If AG Primary' )
    begin
        -- Add new first step: on success go to next step, on failure quit reporting success
        exec msdb.dbo.sp_add_jobstep
          @job_id = @jobid
        , @step_id = 1
        , @cmdexec_success_code = 0
        , @step_name = 'Check If AG Primary'
        , @on_success_action = 3  -- On success, go to Next Step
        , @on_success_step_id = 2
        , @on_fail_action = 1     -- On failure, Quit with Success
        , @on_fail_step_id = 0
        , @retry_attempts = 0
        , @retry_interval = 0
        , @os_run_priority = 0
        , @subsystem = N'TSQL'
        , @command=N'IF (SELECT ars.role_desc
        FROM sys.dm_hadr_availability_replica_states ars
        JOIN sys.availability_groups ag ON ars.group_id = ag.group_id AND ars.is_local = 1) <> ''Primary''
    BEGIN
       -- Secondary node, throw an error
       raiserror (''Not the AG primary'', 2, 1)
    END'
        , @database_name=N'master'
        , @flags=0
    end
end
GO

Blog post is here.

Note: For SQL Server 2014 onwards you can use the builtin function sys.fn_hadr_is_primary_replica('dbname'):

If sys.fn_hadr_is_primary_replica (@dbname) <> 1
BEGIN
    -- This is not the primary replica, exit without error.
END
-- This is the primary replica, continue to run the job...


Seemingly equivalent queries take vastly different execution times

SELECT Data.timestamp, Labels.label, Data.value
FROM Data
JOIN DataItems
      on DataItems.dataItemId = Data.dataItemId
     and Data.assetId = 134
     AND Data.timestamp between '2016-09-27T12:00:00Z' AND '2016-09-27T14:00:00Z'
JOIN Labels
      on DataItems.labelId = Labels.labelId
     AND Label.label IN ('Eng_RPM','Eng_Load','Eng_OilTemp')

maybe add a non-clustered index on Label.label


Actually I do not think this is something you would want to change under nearly any circumstances. You should not fix the symptom, but should find out the problem.

Does the mysql log contains information about aborted connections? Does the syslog of that server contain information which might be relevant? Do you run a more or less decent version of mysql? (At least the most current minor version.) Have you checked if the database server runs out of resources (e.g. RAM, because swapping is really bad for any DB server)? Have you compared the number of connections your webserver maximally issues simultaneously to the db server and the number of connections your db server is processing in parallel from its configuration file?

Have you tried pinging the database server from the web server for a long period of time? Does the ping slows down (or gets lost) too, when the error message pops up in your application?

You should check all that before you change anything in timeouts.


generating all substrings (n-grams) for bit-strings

You can solve the error by using the bit varying datatype. That being said, I don't actually know if this is the behavior you're looking for - it looks weird to me and doesn't match what the text version does.

testdb=# CREATE OR REPLACE FUNCTION public.ngrams(in input bit varying, in len integer)
 RETURNS SETOF bit
 LANGUAGE sql
AS $function$
SELECT cast((input << alpha) as bit varying)
FROM GENERATE_SERIES(0, LENGTH($1)-($2+1), 1) alpha;
$function$
;
CREATE FUNCTION
testdb=# CREATE OR REPLACE FUNCTION ngrams(varchar, integer) RETURNS SETOF TEXT AS $$
SELECT SUBSTRING($1 FROM n FOR $2)::TEXT
FROM GENERATE_SERIES(1, LENGTH($1)-($2-1), 1) n;
$$ LANGUAGE SQL;
CREATE FUNCTION
testdb=# with inputs as (select '10111' as x, c as y from generate_series(2, 3) c)
select x, y, ngrams(cast(x as text), y) ngrams_txt from inputs;
   x   | y | ngrams_txt
-------+---+------------
 10111 | 2 | 10
 10111 | 2 | 01
 10111 | 2 | 11
 10111 | 2 | 11
 10111 | 3 | 101
 10111 | 3 | 011
 10111 | 3 | 111
(7 rows)

testdb=# with inputs as (select '10111' as x, c as y from generate_series(2, 3) c)
select x, y, ngrams(cast(x as bit varying), y) ngrams_bit from inputs;
   x   | y | ngrams_bit
-------+---+------------
 10111 | 2 | 10111
 10111 | 2 | 01110
 10111 | 2 | 11100
 10111 | 3 | 10111
 10111 | 3 | 01110
(5 rows)


Using COUNT() implicitly for making a view over game match data?

This select first all individual player_IDs and uses them to get the numbers

CREATE TABLE `game_match` (
  `ID` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID of the match,',
  `ACCOUNT_ONE_ID` INT(10) NOT NULL COMMENT 'Foreign key for Player 1.',
  `ACCOUNT_TWO_ID` INT(10) NOT NULL COMMENT 'Foreign key for Player 2.',
  `WINNER_ID` INT(10) NOT NULL COMMENT 'Foreign key for which of the two players won.',
  INDEX(ACCOUNT_ONE_ID, ACCOUNT_TWO_ID, WINNER_ID) ,
  PRIMARY KEY (`ID`) USING BTREE
);

INSERT INTO game_match
  (ACCOUNT_ONE_ID, ACCOUNT_TWO_ID, WINNER_ID)
VALUES
  (0, 1, 0),
    (1, 2, 2),
    (1, 2, 1),
    (0, 1, 0),
    (1, 0, 0),
    (2, 1, 2),
    (0, 2, 0),
    (0, 1, 1);

SELECT
DISTINCT
PlayerID,
(SELECT COUNT(*) FROM game_match WHERE (ACCOUNT_ONE_ID = t1.PlayerID OR ACCOUNT_TWO_ID = t1.PlayerID) AND WINNER_ID =  t1.PlayerID) MATCHES_WON,
(SELECT COUNT(*) FROM game_match WHERE (ACCOUNT_ONE_ID = t1.PlayerID OR ACCOUNT_TWO_ID = t1.PlayerID) AND WINNER_ID <> t1.PlayerID) MATCHES_LOST
FROM (SELECT DISTINCT ACCOUNT_ONE_ID  AS PlayerID FROM game_match
UNION
SELECT DISTINCT ACCOUNT_TWO_ID FROM game_match) t1

PlayerID | MATCHES_WON | MATCHES_LOST
-------: | ----------: | -----------:
       0 |           4 |            1
       1 |           2 |            5
       2 |           2 |            2

db<>fiddle here


You are hitting the limit on the GROUP_CONCAT() results length - group_concat_max_len as can be seen in http://sqlfiddle.com/#!9/bb7a66/16

You can raise it temporarily for your query:

SET SESSION group_concat_max_len = 10240;

http://sqlfiddle.com/#!9/bb7a66/19


Recommended Permissions for SQL Account that will be creating new SQL Accounts

You could assign the server role securityadmin to the Admin_Main account.

The securityadmin server role is documented as:

  Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE database-level permissions if they have access to a database. Additionally, they can reset passwords for SQL Server logins.

  IMPORTANT: The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role.

Reference: Server-Level Roles (Microsoft | Docs)

Let's give that a try and see how far we can get...

Creating SQL Server Login Admin_Main

We'll create a new SQL Server Login and assign that user the securityadmin server role:

USE [master]
GO
CREATE LOGIN [Admin_Main] WITH PASSWORD=N'MyShineyNewP@sswerd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [securityadmin] ADD MEMBER [Admin_Main]
GO

This is all done with an account that has the sysadmin server role and the result is:

Command(s) completed successfully.

Login With Admin_Main

You can then open up a new database query window using the Admin_Main SQL Serer Login and the corresponding password. Once you have done that...

Assigning Higher Privileges

...your new user is certainly going to try and increase his/her privileges by adding himself/herself to the sysadmin server role:

ALTER SERVER ROLE [sysadmin] ADD member [Admin_Main]

This results in the following:

Msg 15151, Level 16, State 1, Line 3
Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

So the SQL Server Login can't assign himself higher privileges.

Creating a New User (Or: How Far Can I Get?)

Seeing as the Admin_Main is now allowed to create new SQL Server Logins, let's create a new user while still logged in as Admin_Main and add some server level roles. We'll do this in multiple steps to find out when assigning SQL Server roles will fail:

Create Login

Input

USE [master]
GO
CREATE LOGIN [New_User] WITH PASSWORD=N'MyShineyNewP@sswerd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

Output

Command(s) completed successfully.

Assign securityadmin Role

Input

ALTER SERVER ROLE [securityadmin] ADD MEMBER [New_User]
GO

Output

Command(s) completed successfully.

Assign sysadmin SQL Server Role

Input

ALTER SERVER ROLE [sysadmin] ADD MEMBER [New_User]
GO

Output

Msg 15151, Level 16, State 1, Line 13
Cannot alter the server role 'sysadmin', because it does not exist or you do not have permission.

Conclusion

You will have to assign the SQL Server role securityadmin to the Admin_Main SQL Server Login to allow this account to create new SQL Server Logins and/or Windows Authenticated SQL Server Logins.

...and you can't assign permissions/privileges to other accounts that your initial accounts doesn't already possess.


Error using view to limit data access between databases in SQL Server

A user must have a security context to access the ReplData database.  That can be their own account, an AD group of which they are a member, or the guest user (if the guest user is enabled in the ReplData database).

No permission need be granted on the ReplData.dbo.refData table as long as the ownership chain is unbroken.  Since the refData table is owned by dbo, the implication is the login that owns the ReplData database must be a database user in the Reporting database and that user must own the SourceA schema.  Alternatively, the Reporting could be owned by the same login that owns the ReplData database and the SourceA schema owned by dbo.

You can change the schema or database owner with ALTER AUTHORIZATION, if needed:

ALTER AUTHORIZATION ON SCHEMA::SourceA TO <ReplData-database-owner-user>;
ALTER AUTHORIZATION ON DATABASE::Reporting TO <ReplData-database-owner-login>;

Also, the DB_CHAINING database option must be on in both the ReplData and Reporting databases.

In the case of stored procedures, you can get around the need for a user context by signing the module with a certificate that exists on both databases.  That's not an option with a view, though.


Scripting SQL Server into multiple SQL scripts

As @vonPryz said, I have the same feeling that you're trying to open a huge sql file (and SSMS's generated files are not the best in the world, as in they're pretty verbose by default).

As you most probably saw already, SSMS is not properly working with big files. It fails to properly parse them. I would suggest that you don't generate script files for your data, because this isn't MySQL's dump. Use full backups or, if you really want only one table, use bcp exports.

Here's the link to the documentation of the bcp tool. It's easy to use and you shouldn't have any problem to import it on another server.


Connecting to ReadOnly Secondary Replica of SQL Server via the Listener by Default

The Availability Group Listener is (per the documentation), simply "a Domain Name System (DNS) listener name, listener port designation, and one or more IP addresses." It becomes a resource in the Windows Server Failover Cluster (WSFC), and is used for routing traffic to the appropriate Availability Group (AG) node. However, it is ultimately just a DNS Name and IP.

When you connect to the Listener, it will always route to the primary (read/write) replica. The only way to have traffic routed to a secondary (read-only) replica is to explicitly specify ApplicationIntent=ReadOnly. There is no way to change the default behavior on the Listener to route traffic to a read-only secondary without specifying ApplicationIntent.

If you want users to connect to a read-only replica without explicitly specifying the ApplicationIntent, then those users would need to connect directly to the secondary server.
Note that if the user connects directly to the secondary server, a failover could result in that server becoming primary, and thus, no longer read-only.

In your scenario, if you have automatic failover between DB01 & DB02, and DB03 will "never" be primary, and you also want users to always connect to DB03, then you should simply direct users to connect directly to DB03.

However, if you want to take advantage of Read-Only routing so that the server names are obscured, and the specific secondary node to which a user is connected is transparent to them, then your only choice is to connect to the AG Listener, and specify ApplicationIntent=ReadOnly.


Removing SQL Server from failover cluster - you have to run the setup.exe and in maintenance you have to select remove node.

Then removing the node from windows cluster, I would suggest you use PowerShell

Remove-ClusterNode –Name node4 #change your node name here

  The Remove-ClusterNode cmdlet removes a node from a failover cluster. After the node is removed, the node no longer functions as part of the cluster unless the node is added back to the cluster. Removing a node is also called evicting a node from the cluster.

The above commmand will ask your confirmation if you want to really evict the node or not -- unless you use use /force switch.


Can i view per Database memory usage on SQL Server?

Just be aware that the suggested queries focuses on the Buffer Pool (cached data).
A database can have other types of memory usage as well, like exec plans (when database bound), working memory for queries, locks, etc. I don't know of a way to summarize those since many are, sort of indirect consequences of doing things inside a SQL Server, and some are not necessarily bound to a certain database.


SQL Server : Memory pressure,Spiked user connections and CPU usage

The threshold for parallelism should be higher than 20 for very large database.

Memory pressure can be avoided by tuning query/index, rebuilding fragmented index, enabling TF 2371 at database level to fire auto update statistics frequently.

As a DBA you can add multiple file group to your very large database, each containing separate mdf file (located on different disk drive) and move larger table on newly added file group to get higher disk IO.


sp_blitzCache against one stored procedure

Rephrased your questions a little:

Q: Why multiple rows for the same plan handle?

A: Because a plan can include multiple statements.

Q: Why does a proc have multiple lines in sp_BlitzCache?

Similar to above: because a stored proc can have multiple statements in it. You'll see one line for the entire proc's metrics, and a line for each statement in it.

Q: When I see "CREATE PROC" does that mean the proc was created?

No, that's just how SQL Server stores the queries in a stored procedure. It shows you the contents of the proc.

Q: Why is the memory granted different from memory used?

Because the grant is calculated before the query starts using memory. It's kinda like you asking for money to go to the store - you may not use all of the memory if they don't have all the stuff you wanted at the store. You may end up only using some of your money.


Ora 01017 Invalid Username Password; with correct data

I reinstalled oracle and now it works fine.
My guess with tnsnames.ora was totally wrong and also think there was the error!


How to select records by nth minimum/maximum value of date column in postgresql?

Another way:

SELECT *
FROM x
WHERE work = (SELECT DISTINCT work FROM x ORDER BY 1 OFFSET 1 LIMIT 1)
ORDER BY id
LIMIT 1;

Or more simpliest:

SELECT DISTINCT on (work)
       work, id
FROM x
ORDER BY 1, 2
OFFSET 1
LIMIT 1;

You can choose num of element by changing offset.


Unable to install 64-bit Oracle 10g Release 2 ODAC 10.2.0.3 for Windows x64 on 64 bit Windows Server 2008 R2

I think That version of ODAC is not certified on Windows 2008 R2. Please use the regular Oracle DB Client that supports Windows 2008 R2. If you need to use ODP.NET 10.2, then go here:
http://www.oracle.com/technetwork/database/10204-winx64-vista-win2k8-082253.html

It can be very helpful to You.


How to avoid multiple joins on the same table (it has 200 columns to hold IDs. Another table has a mapping of IDs to values)

Take a look at my answer here to understand why your original design is a very bad idea from a relational database point of view.

Basically, you have "squashed" two entities into one! Your scenario is very similar to that question! You need to normalise your data - this will greatly assist your query formulation and speed!

It appears that there are two entities here (IMHO) a checklist entity and a question entity.

Checklists are identified by a checklist_id (chk_id) and questions are uniquely identifed by a checklist_id and a question_id. Perhaps "quiz" might be a better name? You could have history quizzes, sports quizzes &c. with different chk_id's.

So, in order to tackle the underlying problem, you should create a question table (see SQLFiddle here):

CREATE TABLE question
(
  chk_id INTEGER NOT NULL,
  q_id INTEGER NOT NULL,
  q_comment VARCHAR (255) NOT NULL,
  q_checked BIT NOT NULL,
  CONSTRAINT question_pk PRIMARY KEY (chk_id, q_id)
);

You could implement my strategy in VIEWs if you can't change the underlying table structure - that will be a bit clumsy, but still better than your current scenario!

Sample checklist table:

A sample of the first 3 questions extracted from your checklist table and sample data:

CREATE TABLE checklist -- you have this already!
(
  chk_id INTEGER PRIMARY KEY,
  q1_id  INTEGER NOT NULL,
  q1_comment VARCHAR (255) NOT NULL, -- or whatever size suits.
  q1_checked BIT NOT NULL,

  q2_id INTEGER NOT NULL,
  q2_comment VARCHAR (255),
  q2_checked BIT NOT NULL,

  q3_id INTEGER NOT NULL,
  q3_comment VARCHAR (255),
  q3_checked BIT NOT NULL

  -- &c. 197 times.
);

INSERT INTO checklist
VALUES
(233, 1, 'Good question!', 1, 2, 'Crap', 0, 3, 'Better', 1),
(234, 1, 'An OK question', 0,  2, 'Bleuch...', 1,  3, 'Brilliant!!', 0);

If the "comment" field is the question itself - i.e. "prompt", substitute text of your choice - my comments are opinions about the question... not really sure. Easily modifiable!

You will then need to construct a series of 200 INSERTs like this:

INSERT INTO question
SELECT
  chk_id,
  q1_id,
  q1_comment,
  q1_checked
FROM checklist;

You will have to do this 200 times (q2... q200). Obviously, this will require some work (either manual typing or scripting) to get your data out of your original entity, but it will be worth it in the long run!

Here are the three question entries (by two checklists - history or sport):

SELECT * FROM question;

Result:

chk_id  q_id          q_comment     q_checked
   233     1     Good question!          true
   233     2               Crap         false
   233     3             Better          true
   234     1     An OK question         false
   234     2          Bleuch...          true
   234     3        Brilliant!!         false

Your tables will be much more performant and much easier to manipulate!


The warning

WARNING:  no privileges were granted for "serial"

indicates that the granting role doesn't have the necessary privilege to grant privileges on the object in question.

Either execute the GRANT command as the role owning the sequence.
Or do the same as superuser (default superuser is postgres).
Or GRANT the necessary privilege to the role dev so that this role can in turn grant it to others:

GRANT ALL ON SEQUENCE serial TO dev WITH GRANT OPTION;

The name serial for a sequence isn't particularly wise, but since serial is not a name of a base type and not a reserved word, it wouldn't cause any immediate trouble.

Aside:
Unfortunately the manual uses the name serial in the CREATE SEQUENCE and ALTER SEQUENCE examples. I feel like a suggestion to pgsql-docs@postgresql.org might be in order ...


What does GRANT CONTROL on a schema do in MSSQL?

To see what CONTROL on schema brings to you, create a test user without any permission, grant CONTROL on schema to it and check what permissions he has after it:
create user test;
grant control on schema::dbo to test;

execute as user = 'test';
select *
from sys.fn_my_permissions('dbo', 'schema');
revert;

Here is what you'll get:

When you want to CREATE TABLE you should open BOL article CREATE TABLE (Transact-SQL) and scroll down to PERMISSIONS:

Now you see that even if your table is not supposed to have columns of CLR user-defined type, you still need CREATE TABLE permission to grant to your user to make him be able to create a table.
Test user has already ALTER SCHEMA implied by CONTROL on schema, but CREATE TABLE should be granted (as well as REFERENCE permission in case of CLR user-defined type).


Why does OBJECT_ID return NULL in a computed column?

A metadata function mentioned in a computed column in a #temp table is going to reference tempdb.sys.objects, not yourdatabase.sys.objects, since the table is created in tempdb. Try adding the database prefix to the insert, and that will make OBJECT_ID go find it in the right place:

CREATE TABLE #procs (
  name SYSNAME,
  [object_id] AS OBJECT_ID(name)
);

INSERT INTO #procs (name) VALUES ('dbo.sp_send_dbmail');
INSERT INTO #procs (name) VALUES ('msdb.dbo.sp_send_dbmail');

SELECT name, [object_id] FROM #procs;

Results:

dbo.sp_send_dbmail          NULL
msdb.dbo.sp_send_dbmail     283148054


Oracle Active Data Guard - OPEN_MODE STATUS

When working with DataGuard, I would strongly advise using the DataGuard Broker (dgmgrl).

It greatly simplifies the cluster management process and is the tool of choice for interrogating and diagnosing problems with the cluster.

dgmgrl /
DGMGRL> show configuration
. . .
DGMGRL> show database PRIMARY_DB
. . .
DGMGRL> show database STANDBY_DB
. . .


Very light queries are in slow-query-log ! Why?

I noticed you are searching for an IP address

You could add an additional column with a numeric value for the IP

First, create the column to hold the numeric value of the IP and index it

ALTER TABLE gl_visiteurs ADD COLUMN IPVALUE INT UNSIGNED DEFAULT 0;

Next populate that new column and index it

UPDATE gl_visiteurs SET IPVALUE = INET_ATON(IP);
ALTER TABLE gl_visiteurs ADD INDEX (IPVALUE);

From there, you can do deletes via numeric lookups like this

delete from gl_visiteurs
where IPVALUE = INET_ATON('68.180.230.33');

and do inserts like this

INSERT INTO gl_visiteurs (IP, IPVALUE, user_id)
VALUES ('68.180.230.33',INET_ATON('68.180.230.33'),0);

UPDATE 2017-01-09 17:57 EST

Based on your comment, you can drop the IP column and keep the IPVALUE column

Then your inserts would be

INSERT INTO gl_visiteurs (IPVALUE, user_id)
VALUES (INET_ATON('68.180.230.33'),0);

but to see the IP your selects must be something like

SELECT INET_NTOA(IPVALUE) as IP,... FROM gl_visiteurs


MSSQL Select all parent rows for which exists a combination of children rows with desired value

Below is one method.  In the outer WHERE clause, specify the number of types included in the IN clause.  An composite index on the Childrens table ParentId and TypeId will help optimize the query.

SELECT  *
FROM    Parents p
WHERE   2 = ( SELECT    COUNT(DISTINCT c.TypeId)
              FROM      Childrens c
              WHERE     p.Id = c.ParentId
                        AND c.TypeId IN ( 1, 2 )
            );

SELECT  *
FROM    Parents p
WHERE   3 = ( SELECT    COUNT(DISTINCT c.TypeId)
              FROM      Childrens c
              WHERE     p.Id = c.ParentId
                        AND c.TypeId IN ( 4, 5, 6 )
            );

EDIT:
The original DDL in the question didn't include constraints or indexes.  These aren't relevant for query functionality but are important to compare performance of alternative solutions.

Assuming the IDENTITY columns in both tables are also the primary keys and the tables are related, I added the constraints below.

ALTER TABLE Parents
    ADD CONSTRAINT PK_Parents PRIMARY KEY CLUSTERED(Id);

ALTER TABLE Childrens
    ADD CONSTRAINT PK_Childrens PRIMARY KEY CLUSTERED(Id);

ALTER TABLE Childrens
    ADD CONSTRAINT FK_Childrens_Parents FOREIGN KEY (ParentId) REFERENCES Parents(Id);

I added candidate indexes on columns specified on JOIN or WHERE clauses and specified UNIQUE for those indexes that include the primary key column.  Indexes not deemed to be used can be dropped afterward.

CREATE UNIQUE INDEX idx1 ON Parents (Id, GroupId);
CREATE INDEX idx1 ON Childrens (ParentId, TypeId);
CREATE INDEX idx2 ON Childrens (TypeId, ParentId);

Finally, I ran the original query in my answer in SSMS along with the query below which Tpsamw1 proposed in a comment (but without ORDER BY to level the playing field). SET STATISTICS IO ON and including the actual execution plan:

SELECT  p.Id
      , p.GroupId
FROM    Parents p
        INNER JOIN Childrens c ON c.ParentId = p.Id
WHERE   c.TypeId IN ( 4, 5, 6 )
GROUP BY p.Id
      , p.GroupId
HAVING  COUNT(DISTINCT c.TypeId) = 3;

SSMS reported comparable performance (50% of batch cost for each query) but that doesn't tell the whole story.  The STATISTICS IO showed a higher number of logical reads for my original query, which I believe a better indicator of performance.  This suggests Tpsamw1's query performs better.  Below are the stats:

My original query:

Table 'Childrens'. Scan count 15, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Parents'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Tpsamw1 query:

Table 'Parents'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Childrens'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I then loaded additional data by copying the original test data and update stats:

INSERT INTO Parents SELECT NEWID() FROM Parents WHERE Id = 1;
INSERT INTO Childrens SELECT SCOPE_IDENTITY(), TypeId FROM Childrens WHERE ParentId = 1;
INSERT INTO Parents SELECT NEWID() FROM Parents WHERE Id = 2;
INSERT INTO Childrens SELECT SCOPE_IDENTITY(), TypeId FROM Childrens WHERE ParentId = 2;
INSERT INTO Parents SELECT NEWID() FROM Parents WHERE Id = 3;
INSERT INTO Childrens SELECT SCOPE_IDENTITY(), TypeId FROM Childrens WHERE ParentId = 3;
INSERT INTO Parents SELECT NEWID() FROM Parents WHERE Id = 4;
INSERT INTO Childrens SELECT SCOPE_IDENTITY(), TypeId FROM Childrens WHERE ParentId = 4;
INSERT INTO Parents SELECT NEWID() FROM Parents WHERE Id = 5;
INSERT INTO Childrens SELECT SCOPE_IDENTITY(), TypeId FROM Childrens WHERE ParentId = 5;
GO 1000

UPDATE STATISTICS dbo.Childrens WITH FULLSCAN;
UPDATE STATISTICS dbo.Parents WITH FULLSCAN;
GO

Running the queries again shows execution plan changes to both queries.  Both queries yielded the same number of logical IOs, indicating performance is be about the same.  The only execution plan difference was an inner merge join versus a right outer merge join.

Table 'Parents'. Scan count 1, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Childrens'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


How to recover deleted records by mistake in Mysql

If it's MyISAM - no chance to recover w/o a backup.

If InnoDB - it depends.
InnoDB flags a record as deleted and keeps it in a page for a while. When a tree is rebalanced the deleted records are purged.

So whether you can undelete records depends on how much writes were done to the table after the delete.

Open InnoDB tablespace in a hexeditor and try to find the deleted records:

hexdump -c table.ibd

If table is relatively small I prefer bvi (it allows search by a string or a binary patter)

bvi table.ibd

So if you see the deleted records - it's recoverable.

Other place to look for deleted records is the binlog. If it's in a row format - the delete event will contain old and new values.

InnoDB redo log stores the changes, too. But definitely it won't keep them for a month.

To retrieve deleted records from an InnoDB page you can use a MySQL data recovery tool . Recovery of deleted records is similar to recovery from corrupted tablespace. But in that case c_parser must be called with -D option that will dump deleted records only.

DISLAIMER: I'm an author of the tool


You could set up a job to rebuild or reorganize them weekly/daily.

I recommend checking out http://sqlfool.com/2011/06/index-defrag-script-v4-1/

This index rebuilder is highly customizable.


Create a result for a line chart with dates

It's not pretty but if you have a calendar table (there are plenty of scripts online to create one), you can run the following. I joined calendar to your table and replaced the NULL's (which in your case would not have a scan date) with the last risk number.

Here's a SQL Fiddle

select caldate, scandate,
(select risk from yourTable where scandate = (select max(scandate) from yourTable where
  scandate is not null and scandate <= caldate))
from calendar a
left join yourTable b on a.caldate = b.ScanDate
where caldate between '2014-04-20' and '2014-04-28'


Why SQL Server Standard 2017 is using more memory than limit described in documentation?

I believe you missed the documentation. Please see the highlighted points in image below

The memory used by memory optimized tables is 32 GB PER DATABASE

So let us say you are having SQL Server standard edition having columnstore indexes and having memory optimized tables in 3 databases.

The maximum memory utilized can be

128+32+(3*32)= 256 GB.

Let me know for how many databases you are using memory optimized tables ?. The 128 GB limit is only for buffer pool


Hello Senthil Prabhu,

When we are working with SQL Profiler we must keep in mind that we should not

OVERLOAD THE TRACES
There is no easy way to decide which events or data columns you need to capture as part of a trace. It
depends on the problem at hand, and it requires a good understanding of what events are available and
what each one of them does.

As the SQL Profiler is Most powerful yet complicate to analyst tool

When you connect to SQL Profiler you can create a template of T-SQL Duration. and more in Event Selection Tab you can customize the object name and other filter so you will find only desire details.

TSQL_DURATION
The TSQL_Duration template is used to capture all T-SQL statements submitted to SQL Server by
clients and their execution time. It groups the results by execution time duration.

Hope this will help you.

Thank you,
Sam


ODBC Data Source SQL Server Connection - Login Failed For User

My answer... From my comments:

The issue was that the server was set to "Windows Authentication Mode" only. To fix this I

Right click the server - > Properties
Click "Security" in the left side of the "Server Properties" dialog
Changed server Authentication to "SQL Server and Windows Authentication mode"
Clicked "OK"
Restarted Associated services. At first I forgot to restart the services, so I was still getting the error, but now I am able to connect without an issue. This was very helpful: http://support.microsoft.com/kb/555332


SQL Server schema intact but every record from every table is gone

This happened at my company.

Someone re-ran the schema creation scripts from within the application's admin interface--something like an "initialize database" command. These scripts included a series of IF EXISTS()...DROP...CREATE statements to drop and recreate each table and constraint. Since they were designed to wipe a database even if it was already populated, they were carefully structured to account for foreign key relationships. Thus, the database schema was there and looked great, but all of the data was gone.

To prevent this, revoke the appropriate DDL permissions from the app's database login after it has created its own schema, or make sure any "initialize database" functionality your application has includes several safety checks and presents appropriate warning to the user regarding what's about to happen.


How to copy data into same table based on existing data in sql?

One more solution is to run sub query for two time with different updated Date_column value. It will select data from same table and updated with different date_value.

INSERT into TABLE_NAME (Column1, Column2, Date_column)( SELECT (Column1, Column2, '201840') FROM TABLE_NAME where Date_column = '201839');

INSERT into TABLE_NAME (Column1, Column2, Date_column)( SELECT (Column1, Column2, '201841') FROM TABLE_NAME where Date_column = '201839');


Implementing json_object_agg() in Postgres 9.3

I was able to emulate json_object_agg using string_agg (which is available in 9.3).

Your example would be:

select user, ('{' || string_agg('"' || offer || '": ' || clicks, ',') || '}')::json as activity
from click_activity
group by user


MySQL : Stored Procedure from Trigger giving duplicate results

The reason is simple - inaccuracy. Variable name and column name interference. You type WHERE TRIGGER_NAME = trigger_name. Both TRIGGER_NAME and trigger_name are treated as input variable name, and this condition is always true. You have 2 triggers matched the rest condition TRIGGER_SCHEMA = SCHEMA(), so query returns 2 rows.

Simply rename input variables. Or add table alias and allow the server to distinguish the column name and variable name: WHERE INFORMATION_SCHEMA.TRIGGERS.TRIGGER_NAME = trigger_name AND TRIGGER_SCHEMA = SCHEMA();.


Where does Oracle Enterprise Manager derive the "User Logon Time" metrics from?

"Logon Per Sec" and "Logon Per Tx" can be found in OEM REPOSITORY (e.g. look in sysman.mgmt$metrics_daily view, the metric_name = 'instance_throughput' and metric_column like 'logons%'). The other metric will also be there, these are maintained in OEM REPO not in individual targets.


Transactional Replication failing between SQL Server 2012 and SQL Server 2016 versions

The other half of the version requirements is that your distributor be the highest SQL Server version in your Replication topography.

In your case, you are replicating from SQL Server 2012 to SQL Server 2016, which works only if your distributor is also SQL Server 2016.

If you are running your distribution database on your 2012 publisher, configuration will fail when you add the subscriber because a subscriber is at a higher version than the distributor. You would need to either run a dedicated distributor (recommended), or use your 2016 subscriber as the distributor server.

The error message you posted is deceptive because it's not a subscriber/publisher version issue, but rather a subscriber/distributor version issue.


SQL Server 2005 Analysis Services won't start

Are you doing a service restart or a Stop and start in SQL Server Config Manager? I had a similar issue on an old 2005 box and if I did a restart of SQL services in SQL server config manager the issue would not be resolved until I do a Stop and start or reboot the server.


Dimensional Modeling of a Loan Approval Process and Fact Grain(s)

Dimensions that are 1-1 with facts are not unheard of. The problem with such dimensions is their size, which won't get any smaller just because you introduce a fact table that's even more detailed. Whether you truly need a dimension at the grain of an individual opportunity is a question I'd think about.

You don't mention measures in your question at all, and they're rather important for identifying the fact tables. For instance I'd expect the "Opportunity" (though I'm accustomed to calling it a Loan application) to have the amounts requested and approved. How would you calculate the total amount approved and the number of applications approved/total in the model you're thinking of?

I find it weird that you're attemting to convince the potential users that they need more functionality than they think they do; in my experience it's usually the other way around. How much domain experience do you have? If you're sure the use-cases you've come up with are real, you should be able to find a stakeholder who agrees with you. If you can't, you probably should postpone implementation until someone needs it.

(Unless your architecture is such that adding a more detailed fact table will require unreasonably large effort if not done upfront, e.g. you're planning to ETL source data directly to the dimensional data mart, throwing away all the data that doesn't fit  - but that's an argument against that architecture, not against the "Opportunity" fact table, IMO.)

An "accumulating snapshot" fact table is commonly mentioned when modelling a process in a dimensional paradigm (1, 2).


MySQL 5.7: Scope of SET TRANSACTION ISOLATION LEVEL for SELECT

(To answer question in comment)

There are three ways to build a transaction:

(1)

BEGIN;
any number of statements  -- all in one transaction
COMMIT;

(2)

SET AUTOCOMMIT = ON;
...
statement  -- This is a transaction
...
statement  -- This is a different transaction

(3)

SET AUTOCOMMIT = OFF;
...
statement
statement
COMMIT;  -- 2 statements in a transaction

statement
statement
COMMIT;  -- another 2 statements in a different transaction


Would a sequence provide any performance gains over SELECT MAX(id)?

SELECT MAX(id)+1; is incorrect, will not work under even mild concurrency as multiple processes will find the same MAX and then attempt to insert the same next value.

A SEQUENCE is correct, so by definition better, but not necessarily the best. It should be used as a DEFAULT constraint on the column.

By far simplest solution is to simply use an IDENTITY column.


backup the difference with sql server express between 2 timestamps

My guess is that you ran this:

BACKUP DATABASE foo TO DISK = 'c:\backup\foo.bak';

... wait a week ...

BACKUP DATABASE foo TO DISK = 'c:\backup\foo.bak';

The result of this is that your backup file contains two backups, so the file itself will increase in size, whether that delay is a week or a minute.

To avoid this, you can choose one of two options:

You can reinitialize the file to remove any old backups and replace them with the new one:

BACKUP DATABASE foo TO DISK = 'c:\backup\foo.bak' WITH INIT;

This may or may not be desirable, since you only ever have a single backup, and if something should go wrong in between reinitialization and backup completion, you have zero backups.
You can write to a new file each time, typically people put a timestamp on the file or write to a folder made for that day.

BACKUP DATABASE foo TO DISK = 'c:\backup\foo201811010843.bak';

You can use a variable for the destination filename, so you can construct that based on GETDATE() as opposed to hard-coding it.

DECLARE @fn varchar(32) = 'c:\backup\' + CONVERT(char(8), GETDATE(), 112)
  + REPLACE(CONVERT(char(5), GETDATE(), 108),':','') + '.bak';

BACKUP DATABASE foo TO DISK = @fn;

The upside is that you don't have to be tied to a single backup file (and hence single point of failure). The downside is that you will have multiple files to manage... you may want some kind of cleanup task that automatically deletes everything except, say, the last two backups.

Now, there are a couple of issues that are tangential that I have to mention:

Are you only taking full backups? If so, then realize you will never be able to restore your database to a specific point in time, only to the point in time the last successful backup started. If that is a problem, you should use full recovery, not simple recovery, and determine a proper log backup schedule that meets your business requirements for how much data you can lose (RPO) and how long you can wait to get back up and running (RTO) in the event of a disaster.
If you are going to continue using simple recovery, you do have an option to take a backup of only deltas since the last backup, e.g.

BACKUP DATABASE foo TO DISK = 'c:\backup\foodiffwhatever.bak' WITH DIFFERENTIAL;

You'll need to manage files here, too, because all the differentials in the world are useless without the original full backup they reference. For more info, see Differential Backups.
If you are worried about your database growing to 35 GB but not having enough room to store a backup of that database, stop what you're doing right now and go find more disk space. You should not be making a decision between having data and being able to back it up and, frankly, you should not be storing your backups on the same disk as the database anyway (that kind of defeats the purpose). It's like keeping your car key in a jacket pocket and keeping the extra key (you know, in case you lose your jacket) in the other jacket pocket.


created and deleted a database on mysql slave host by mistake

That depends. If the test database on the Master was never in use, then the slave won't care. As long as the slave is not a master to another server, then you are OK.

If the slave has binary logging enabled, the database will just get created and dropped and a record of it would be in the binary logs of the slave.

Besides a test database would not be in use in Production.


Time Out issue, can I set the Ping Property?

You are probably looking for the KeepSessionAlive property of the ReportViewer control. To quote the linked MSDN page:

  ReportViewer.KeepSessionAlive Property

  Property Value
  true to keep the user session from expiring; otherwise, false. The default is true.

  Setting this property to true causes the ReportViewer control to keep the ASP.NET session from expiring if session is enabled in your ASP.NET application. Also, in remote processing mode, it causes the ReportViewer control to keep the report server session from expiring.

You should be able to set this property to false to prevent the keep-alive calls.


MariaDB Galera Cluster - first node won't reconnect after restart... Ever

With not so much detail in the question, I can't give a detailed answer, but I'd recommend starting with MariaDB's documentation about Galera Cluster since you're using MariaDB. See e.g. Getting Started with MariaDB Galera Cluster. The DigitalOcean documentation you referred to is for MySQL 5.6, so there may be details in there that works slightly differently on MariaDB.

Maybe the most crucial question about your specific problem is: What error message are you getting? And I mean the actual error message in the MariaDB error log, which is not necessarily what systemctl will show you in the shell when you run the start/restart/status commands. Once you have this, try your favourite search engine to find the answer. If that doesn't work, come back to DBA.SE and ask a more specific question which includes the error message(s).

Galera Cluster is an excellent technology, but if you don't have much experience with it yet, make sure to read carefully about its limitations, and make sure they're not in conflict with your particular use-case. There is a lot to learn, so be prepared to invest sufficient time and effort.


You're getting close, you're looking at the cached plans which says what has been executed. That doesn't tell you what's being executed right now, or yesterday, or in ten minutes.

Try this to see what's running right now.

SELECT  s.loginame, db_name(s.dbid) name, s.hostname,
 s.program_name,
 s.sql_handle, s.stmt_start, s.stmt_end,
 s.spid, CONVERT(smallint, s.waittype) waittype,
 s.lastwaittype, s.ecid, s.waittime , s.blocked,
 r.plan_handle,  r.statement_start_offset, r.statement_end_offset, r.query_plan_hash, r.start_time,
 q.plan_generation_num
FROM master..sysprocesses AS s WITH(NOLOCK)
LEFT OUTER JOIN sys.dm_exec_requests AS r WITH(NOLOCK)
   ON (s.spid = r.session_id)
LEFT OUTER JOIN sys.dm_exec_query_stats q
  ON q.plan_handle = r.plan_handle
  AND q.statement_start_offset = r.statement_start_offset
  AND q.statement_end_offset = r.statement_end_offset
WHERE (s.cmd<>'AWAITING COMMAND'
                AND s.cmd NOT LIKE '%BACKUP%'
                AND s.cmd NOT LIKE '%RESTORE%'
                AND s.cmd NOT LIKE 'FG MONITOR%'
                AND s.hostprocess > ''
                AND s.spid>50
                AND s.spid<>@@SPID)
AND lastwaittype NOT IN ('WAITFOR', 'SLEEP_TASK')
ORDER BY s.spid, s.ecid ASC

I would use SP_Whoisactive and log into a table for monitoring and seeing what's executing. Note, this is not the best way. This only captures what is running in real time when the query runs.

The next best way is to apply CDC / CDT / Trigger notifications on the databases / running SQL Profiler.


PDO count(*) does not work with LIMIT and OFFSET

Within the query itself, trying separating the COUNT(*) from the retrieval by rewriting the query like this:

SELECT COUNT(*) from (SELECT myl_news_comments WHERE news_id=:news_id LIMIT :ajax_read_how_many_comments OFFSET :items_already_loaded) A

Your code should look like this now:

        $query_list_records = "SELECT COUNT(*) from (SELECT myl_news_comments WHERE news_id=:news_id LIMIT :ajax_read_how_many_comments OFFSET :items_already_loaded) A";
        $result_list_records = $db->prepare($query_list_records);
        $result_list_records->bindValue(':news_id', $_POST['news_id'] , PDO::PARAM_INT);
        $result_list_records->bindValue(':ajax_read_how_many_comments', (int)$ajax_read_how_many_comments , PDO::PARAM_INT);
        $result_list_records->bindValue(':items_already_loaded', (int)$_POST['items_already_loaded'] , PDO::PARAM_INT);
        $result_list_records->execute();
        $total_records = $result_list_records->fetchColumn();

echo $total_records;

Give it a Try !!!


Making the Oracle optimizer use a virtual column to find out about a partition

I think you have a problem with your virtual column definition. For your special values 2012-02-01 (in YYYY-MM-DD format) and 120.23452 the value of the virtual column is

2012*10000+2*100+1*1000+180+120.23452 = 20120000+200+1000+300.23452 =20121500.23452

and not

20120201300.23452

as you expected.

Also check if your virtual column is the column your table is partitioned by.

From the VLDB and Partitioning Guide:

  Virtual column-based partitioned tables benefit from partition pruning
  for statements that use the virtual column-defining expression in the
  SQL statement.

So I think select-statement

select * from mytable where start_date > todate('02-01-2012', 'MM-DD-YYYY') and    start_value > 120.23452

should be something like

select * from mytable where (
  extract(YEAR FROM START_DATE) * 10000 +
  extract(MONTH FROM START_DATE)*100 +
  extract(DAY FROM START_DATE))*power(10,3) +
  (START_VALUE+180)
)  > 20121500.23452

for partition pruning to take place.

The

todate

function you use in your select statement does not exist in oracle sql. The name of the function is

to_date

.


Query the Differential Changed Maps in SQL Server

SQL Server 2019 contains a new DMV sys.sys.dm_db_page_info() (docs).

  The function returns one row that contains the header information from the page.

This header information includes

diff_status         bit             Bit to indicate if diff status is changed
diff_status_desc    nvarchar(64)    Description of the diff status bit

The bit column is 0 for clean pages and 1 for dirty pages. The description is NOT CHANGED or CHANGED respectively.

Let's say we create a new table and add a row.

use Sandpit;
create table t(c int);
insert t(c) values(1);

We can find page ids in the traditional ways or using another DMV

select t.c, pr.*
from t
cross apply sys.fn_PageResCracker(%%physloc%%) as pr;

For me this happens to return file 1, page 240. Plugging this into the DMV we get

select diff_status, diff_status_desc
from sys.dm_db_page_info(db_id(N'Sandpit'), 1, 240, N'DETAILED');

Which returns

diff_status diff_status_desc
----------- ----------------
1           CHANGED

A full backup should reset these flags and, sure enough, it does

backup database Sandpit to disk = N'C:\Dummy\Sandpit.bak'

select diff_status, diff_status_desc
from sys.dm_db_page_info(db_id(N'Sandpit'), 1, 240, N'DETAILED');

diff_status diff_status_desc
----------- ----------------
0           NOT CHANGED


Managed to solve it quite simply. Stopped the db, commented the options in postgresql.conf and restarted. PostgreSQL took care of deleting everything automatically.


What are the advantages of a database design with single table for MtM relationships?

I don't know if this design has a name, but it is:

horrible to retrieve data with,
horrible to modify,
impossible to guarantee referential integrity.

Can't think of any advantage, but if I could, I'm sure none would trump these three.

Multiple many-to-many tables would be much better compared to this design, although I won't insist that would be the only alternative.


Dump single rows (with blobs) to single files

There are three(3) things you need to do

Provide a list to tables that you want to dump row by row
Use --skip-extended-insert, which forces each INSERT to be one row
Use --hex-blob

Here is such a script

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-h127.0.0.1 -P3306 --protocol=tcp -u${MYSQL_USER} -p${MYSQL_PASS}"

DB_TO_DUMP=mydb
TBLISTFILE=/tmp/tables_in_${DB_TO_DUMP}.txt

TABLES_TO_SPLIT_BY_ROW="tb1 tb2 tb3"

SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${TBLISTFILE}

TBLIST=""
for TB in `cat ${TBLISTFILE}` ; do TBLIST="${TBLIST} ${TB}" ; done

DT=`date +"%Y%m%d%H%M%S"`
for TB1 in `echo "${TBLIST}"`
do
    ROW_BY_ROW=0
    for TB2 in `echo "${TABLES_TO_SPLIT_BY_ROW}"`
    do
        if [ "${TB1}" == "${TB2}" ] ; then ROW_BY_ROW=1 ; fi
    done
    MYSQLDUMP_OPTIONS=""
    if [ ${ROW_BY_ROW} -eq 1 ]
    then
        MYSQLDUMP_OPTIONS="--hex-blob --skip-extended-insert"
    fi
    DUMPFILE=${TB1}_${DT}.sql
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB_TO_DUMP} ${TB1} > ${DUMPFILE}
done

Just supply the name of the database in the DB_TO_DUMP along with the tables whose rows are to be one by one in the TABLES_TO_SPLIT_BY_ROW variable.

Please note how the dumpfile name has a datetime appended to the table name so you can do some versioning based on the datetime. You can change that to be versioned any way you wish.

You did ask that each row be in its own file. I will leave that up to you to script.


SQL log directory has too much dump file

With Reference sql server 2008 automatically generate stack dump

Set 'msdb' to single user mode and run the following command

  DBCC CHECKDB('msdb', REPAIR_REBUILD)

Reset 'msdb' to multiuser mode the same process for your error database.
This resolved our problem.


Is there a way to make an OOo form for PostgreSQL updateable view?

According to this bug report, there is currently no way to insert data into updatable views through SDBC in LibreOffice Base (I assume it's the same about OpenOffice Base). I tested JDBC and ODBC as well, and no luck. There is no way how to point the Base to a (fake) primary key in the Base, and Base won't insert data into tables without primary key. The author of the report promised to solve this issue himself, but this project looks dead now.

However, the post pointed me to a workaround: when you add a serial-like column to the view (like ROW_NUMBER() OVER (ORDER BY table.id ASC) AS oid), you can assing it as the primary key while importing the view to MS Access through ODBC. This works with MS Access 2003 for me - the data can be inserted into database, with only minor bugs. Access is as good at making forms as OpenOffice base, so this is a good workaround for anyone who have the Access.


Adding ReadOnly Domain-less replica to existing AG with two replicas on two nodes WSFC

Since you got SQL 2017 on Windows Server 2016, you can achieve that (what your looking for) with anyone of following options:

Domain-independent Availability Group
For this to happen, you probably have to re-build WSFC as Active Directory-Detached Cluster since your existing WSFC would expect all nodes to be part of same domain. Consider it's bit complex setup compare to LS and required additional maintenance of master keys and certificates as AG Replica's end-points communication completely depends on those.
Log-shipping
Configure the log shipping pointing Listener name (existing AG) as Primary server and new node as Secondary server in Log Shipping topology


Just keep adding to your list of LEFT JOINs, like this:

Select
  Atr.Ssn_Tx
 ,Names.First_Na
 ,Names.Middle_Na
 ,Names.Last_Na
 ,Reff.Ref_Cd_Desc_Tx Suffix_Na
 ,MBR.PRSN_MBRSH_ID
 ,(CASE WHEN Tax.FILLING_STATUS_CD IN (2061,2062,2063) OR  (Tax.FILLING_STATUS_CD=2063 AND Tax.PRIM_TX_FILER_IN ='Y') THEN 'PRIMARY'
             WHEN Tax.FILLING_STATUS_CD=2063 AND Tax.PRIM_TX_FILER_IN ='N' THEN 'SPOUSE'
             WHEN Tax.FILLING_STATUS_CD IN (2066, 2068) THEN 'DEPENDENT'
             ELSE 'Not sent to FDSH'
             END) AS FILTER_TYPE_CD
 From Hix.T_Enrt En Inner Join Hix.T_Prsn_Enrt Pe On En.Enrt_Id = Pe.Enrt_Id And En.Enrt_Status_Cd In (428,439, 430 ) And Pe.Active_In = 'Y' And (Pe.Retro_In Is Null Or Pe.Retro_In = 'N')
Left Outer Join Hix.T_Aptc Ap On En.Enrt_Id = Ap.Enrt_Id
Left Outer Join Hix.T_Prsn_Addl_Attr Atr On Pe.Prsn_Mbrsh_Id = Atr.Prsn_Mbrsh_Id
Left Outer Join
    (
                    Select nAc.Prsn_mbrsh_Id,  First_Na , Middle_Na , Last_Na, Suffix_Cd From Hix.T_Prsn_Name Na, Hix.t_prsn_mbrsh_prsn_name_ac nAc
                                                   where na.prsn_name_id = nAc.prsn_Name_Id
    ) Names On Pe.prsn_mbrsh_id = Names.prsn_mbrsh_id
Left Outer Join Hix.T_Ref_Data Reff On Names.Suffix_Cd = Reff.Ref_Data_Id
Left Outer Join Hix.T_Ref_Data Re On Atr.GENDER_CD = Reff.Ref_Data_Id
Left Outer Join Hix.T_PRSN_MBRSH MBR on MBR.PRSN_MBRSH_ID =  Pe.PRSN_MBRSH_ID
LEFT OUTER JOIN HIX.T_PRSN_MBRSH_TAX Tax ON Tax.PRSN_MBRSH_ID = Pe.PRSN_MBRSH_ID AND tax.TAX_FL_YR_NB=YEAR(CURRENT DATE)-2

Assumption: your HIX.T_PRSN_MBRSH_TAX table has a PRSN_MBRSH_ID column (most likely this is the case)


MySQL Bug reference in release notes, but where?

Those big-numbered bugs are internal to Oracle.  We mere mortals are not allowed to see them.

The visible bugs are numbered under about 82000 (so far), and visible at bugs.mysql.com.


Bring second instance up, restore data to that instance. Then mongoexport data to json files what you can mongoimport to current mongodb with "--mode upsert --upsertFields '_id'" (using _id as a key)


Preserving timezones explicitly in postgres

My use case is a simple end-user-facing application accessed from only folks in the 'Europe/Berlin' timezone. [...]
My intuition is now to store the time as a timestamp without time zone because [...]
Later I wouldn't be able to refer to the actual timezone that post was published in.

That indicates a misunderstanding stemming from the unfortunate data type names:

timestamp without time zone (= timestamp)
timestamp with time zone ( = timestamptz)

Neither data type stores any time zone information whatsoever. timestamptz just adds input and output logic to take the timezone setting of the current session into account, and projects from / to UTC time accordingly.
Don't be too embarrassed if that had you confused, it has happened to the best of us:

Time zone storage in data type “timestamp with time zone”

In your particular case
If your statement is reliable:

accessed from only folks in the 'Europe/Berlin' timezone.

... then you might as well just use timestamp. You know the accompanying time zone. And there is no conversion between input and output. You can convert to timestamptz on the fly with:
my_column AT TIME ZONE 'Europe/Berlin'

If your app should outgrow it's current scope and you need to switch to timestamptz to easily facilitate input from multiple time zones, convert with:
ALTER TABLE tbl ALTER col TYPE timestamptz
   USING my_column AT TIME ZONE 'Europe/Berlin'

Be sure to use the time zone name 'Europe/Berlin', not a hard-coded offset that would fail to adjust for DST variations, nor time zone abbreviation, which is just a name for a hard-coded offset - another one of those very common misunderstandings.
Or use timestmaptz right away to cover all eventualities.
Either way, to preserve timezones like you asked, you then need to store them explicitly (in an additional column), as that is not saved otherwise - and you can no longer just assume 'Europe/Berlin'.
Related:

Ignoring time zones altogether in Rails and PostgreSQL
Oddities with AT TIME ZONE and UTC offsets
Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?


Would existing constraints and indexes applied to an old table, apply to a new table renamed like the old one after it's dropped?

No.

Ok, it depends on if you create them on the copy.


Is there a configuration setting for how many pages ANALYZE will scan?

Each column of a table has an attstattarget property (stored in pg_attribute) that tells how much data should be stored for it from the statistical sample gathered by ANALYZE.

It defaults to default_statistics_target, which itself defaults to 100.

In Statistics Used by the Planner, the doc says:

  The amount of information stored in pg_statistic by ANALYZE, in
  particular the maximum number of entries in the most_common_vals and
  histogram_bounds arrays for each column, can be set on a
  column-by-column basis using the ALTER TABLE SET STATISTICS command,
  or globally by setting the default_statistics_target configuration
  variable

The reason behind the 30,000 pages and rows is that the sample size in rows considered by ANALYZE is 300 times the maximum value of attstattarget for the sampled table, which would be the default 100.

The 300 comes from a statistical formula mentioned in the source code
src/backend/commands/analyze.c:

    /*--------------------
     * The following choice of minrows is based on the paper
     * "Random sampling for histogram construction: how much is enough?"
     * by Surajit Chaudhuri, Rajeev Motwani and Vivek Narasayya, in
     * Proceedings of ACM SIGMOD International Conference on Management
     * of Data, 1998, Pages 436-447.  Their Corollary 1 to Theorem 5
     * says that for table size n, histogram size k, maximum relative
     * error in bin size f, and error probability gamma, the minimum
     * random sample size is
     *      r = 4 * k * ln(2*n/gamma) / f^2
     * Taking f = 0.5, gamma = 0.01, n = 10^6 rows, we obtain
     *      r = 305.82 * k
     * Note that because of the log function, the dependence on n is
     * quite weak; even at n = 10^12, a 300*k sample gives <= 0.66
     * bin size error with probability 0.99.  So there's no real need to
     * scale for n, which is a good thing because we don't necessarily
     * know it at this point.
     *--------------------
     */
    stats->minrows = 300 * attr->attstattarget;

As for the number of pages, since rows don't span across pages, at most N pages are going to be read to get N rows. I believe that ANALYZE intentionally aims at fetching this maximum of pages to get the best sample. It makes sense since rows stored in a same page are more likely to be correlated.


SQL Server Agent initiating self-termination

Since you recently upgraded a few months ago from SQL Server 2005 EE to 2012 SE, you should save off and clear your event logs because SqlAgent may crash when it sees Event Log IDs from an older build of SQL Server. For example, the Agent may crash because it reads an event logged by a SQL Server build earlier than the current build, which only contains data with ONE insertion string and the agent will try to format it using the latest resource information that requires TWO strings.  This would lead to a brief memory leak just before it shuts down since a buffer is allocated for the string that it is trying to print into when the crash occurs.

As far as the agent service not listed in configuration manager, there are multiple possibilities:
https://support.microsoft.com/en-us/kb/941823

Ensure you are running 2012 Configuration Manager

You can try rebuilding the WMI Repository:
https://blogs.technet.microsoft.com/askperf/2009/04/13/wmi-rebuilding-the-wmi-repository/


Can I Configure Enterprise Manager Cloud Control on Standard Edition

Is it possible to configure enterprise manager cloud control on Oracle 12c Standard edition?

Infrastructure Repository Databases

  The infrastructure repositories for RMAN, Oracle Enterprise Manager
  Cloud Control, AWR Warehouse, and Global Data Services Catalog can be
  shared in one database, or deployed in separate databases. Enterprise
  Edition must be used for the infrastructure repository database(s).

Will it administer multiple databases (Non-CDB)?

Yes, that is the point of a central monitoring environment.

What about administering databases on different servers?

Yes, same as above.


Is it okay to have a fill factor of 100 on the clustered index that is on an ID column?

In most cases you do want a FILLFACTOR of 100. Only in special cases would you want to lower that value. Updates only cause page-splits if they increase the size of the row such that it won't still fit on the same data page. Deletes here and there shouldn't matter in either case. In all cases, you need to do regular index maintenance and REBUILD the clustered index when fragmentation is over a certain limit. Rebuild will re-sort all of the data to be in the ideal order, so it will fill in voids left by DELETE operations and UPDATE operations that caused page splits where there is still available space on the old and/or new data pages.

A lot of deletes can leave empty pages and hence some fragmentation, but using a lower FILLFACTOR isn't going to help there. And in fact, depending on what rows are being deleted, you could be increasing empty page fragmentation since fewer rows will be on each data page in the first place.

But keep in mind:

Using a FILLFACTOR of 100 doesn't mean that 100% of the space is used. Rows are fit into the data page as long as there is space for the entire row. If you have 100 bytes left over and have a 105 byte row to add, it can't fit there. But there is still space for an update of a variable length column to increase by 50 bytes.
When you set FILLFACTOR to less than 100, you are reserving space on all data pages, not just the ones that will be getting updated. Unless you have a usage pattern that evenly distributes updates across the entire range of the table/index, why reserve space on data pages that contain "older" rows that aren't being updated? That just slows down queries across the entire index / table in the hopes that some of the newer data pages (i.e. rows more likely to be updated) won't page split as early as they would when using a FILLFACTOR of 100.
Consider the size of your rows and how many will fit onto a data page when FILLFACTOR is 100, and how many will fit onto a data page with a lower FILLFACTOR such as 90, or even 80. If you have wide rows of 500 - 1000 bytes, the difference between various FILLFACTOR values might be just one or a few rows. What types of updates are happening? If updates are to fixed-length columns, this isn't even an issue. For variable-length columns, can they be increasing in size by 100 - 500 bytes?

There are several factors to consider here to be truly pragmatic: if, in the course of a day or week (how long between REBUILD operations?) successive UPDATE operations on rows that sort close together are going to expand in size and cause multiple page splits, then is postponing that page split by several hours or a day really worth the impact of reserving that space across the entire table / index such that it takes up more disk space, space in memory (i.e. Buffer Pool), makes backups larger, takes longer to read from disk into the Buffer Pool, take longer to do the index maintenance, etc?
Fragmentation adversely affects range queries, not singleton queries. If you are fetching individual rows (or finding rows to be updated or deleted), you likely won't see the impact of fragmentation, at least not until it is really bad, but maybe not at all.

As with many / most technical decisions, there are various technicalities that influence what is the "best" or "optimal" approach. So you really need to consider the max size of the row, if the updates will happen to variable-length columns, if the update activity is going to be mostly spread across the table / index or confined to a more "recent" segment of it, etc. I have worked in places where the policy was for all tables to have a FILLFACTOR of 80 and it was an insane (and irresponsible) practice given that we were using IDENTITY columns for the clustered indexes (usually PKs) and "older" data rarely ever got updated. So we wasted tons of expensive SAN space on many tables -- often containing a million rows -- that then bloated the backups (and made both backup operations and restore operations take longer!) and chewed up more memory, which either force cached plans to get evicted or cached data pages (i.e. Page Life Expectancy was quite low).

ERGO: Start with 100 and experiment with lowering in small amounts IF / WHEN you think an optimization might be warranted (i.e. if you find that fragmentation on a particular table is growing to higher levels between rebuilds AND that is also causing performance degradation!).


Found_it is null when declared. If no row matches the query that looks for album and userid found_it is not updated, i.e. it is still null. The test

found_it!=1 (use found_it <> 1 instead)

therefor evaluates to NULL ( NULL <> 1 => NULL ). You can prevent this by a default constraint in your declaration:

DECLARE found_it INT DEFAULT 0;

Now the test will evaluate to FALSE.


It's a case of relational-division.
We have assembled an arsenal of query techniques in this related answer on SO:

How to filter SQL results in a has-many-through relation

For your case this query will be among the fastest possible solutions:

SELECT v.*
FROM   params p1
JOIN   params p3 USING (visit_id)
JOIN   visits v  ON v.id = p3.visit_id
WHERE  p1.a_key = 'key1' AND p1.a_value = 'value1'
AND    p3.a_key = 'key3' AND p3.a_value = 'value3';

If you just need the visit_id you don't even need to join to table visits.

db<>fiddle here
Old sqlfiddle.