When the primary key is also the foreign key, in Postgres

Primary key & Foreign key

  Can I designate that one column of a child to be both a foreign key and also a primary key?

Yes absolutely:

create table photo
(
  id integer primary key,
  ... other columns ...
);

create table thumbnail
(
  id integer primary key references photo,
  ... other columns ...
);

TOAST

bytea columns are stored outside of the normal column data (in so called "toast tables") and are not retrieved unless you include them in the SELECT list.

Quote from the manual

  The big values of TOASTed attributes will only be pulled out (if selected at all) at the time the result set is sent to the client.

This only means that even if a query is forced to do a Seq Scan, the bytea columns are not retrieved until the rows are identified that need to be sent back. So if your query does a seq scan on 1 million rows but only 1 row is returned, only one bytea value is read and sent to the client.

Note that the thumbnails might actually be stored inline (not in the toast table) if they are small enough (TOASTing is only triggered for sizes below approx. 2k)


Total, but remember that is only a rule of thumb. Use common sense when setting the value. InnoDB does like as much memory as possible, but there are a couple things to consider:

1) Make sure there is enough RAM left over for everything else that needs to run on the server

2) Remember that the point of the InnoDB buffer pool is to handle the normal working set of your data.  How large is your database total, including some growth factor?  There's no real need to set the buffer pool much more than that, even if you have the RAM.  But your working set of data (what is used in day-to-day activities) is often lower than the total size of the DB.  How much does it really need to keep in RAM for best performance?


You should always use ashift=12 today unless you have an overwhelmingly good reason not to.

relatime=off might save you a vanishingly small amount. Don't expect it to be measurable.

xattr=sa is important if you use extended attributes (e.g. SELinux) and you have a lot of tables and file opening/closing operations.

logbias=throughput should reduce your disk I/O volume, and it will help with SSD storage where there is no huge disconnect between bandwidth and latency like there is on spinning media.

recordsize=8k will ensure you don't have any RMW overheads. You'll have to decide whether the extra compression ratio achievable with larger recordsize is a net victory in your use case.

You may find the slides from a lecture I gave on the subject useful.


To get the second highest distinct value in the table you can use

SELECT MIN(value)
FROM   (SELECT DISTINCT TOP (2) value
        FROM   tablename
        ORDER  BY value DESC)T
/*If only one distinct value return nothing. */
HAVING MIN(value) <> MAX(value);


Sparx Enterprise Architect is a UML tool with a database modelling function.  It's not terribly expensive and pretty UML centric.  It also has some pretty good extensibility features if you're that way inclined.


You might have an issue with your credentials. I recently used the following command successfully to back up to SQL Azure:

BACKUP DATABASE [MyDatabaseName] TO URL = N'https://mystorageaccount.blob.core.windows.net/mycontainer/MyDatabaseName_backup_2014_12_19_124300_8473463.bak'
WITH CREDENTIAL = N'MyCredentialName',
    NOFORMAT, NOINIT,
    NAME = N'MyDatabaseName_backup_2014_12_19_124300_8473463',
    SKIP, REWIND, NOUNLOAD,
    COMPRESSION, STATS = 10;

The credential was set up with MyCredentialName as the 'Name', the storage account name as the 'Identity', and primary or secondary access key as the 'Password'.

I ran into problems with the credential originally because I was trying to use the credential format supported for using Azure storage as the file location for the database (i.e. url prefix for 'Name', "SHARED ACCESS SIGNATURE" for Identity, and everything after the "?" from shared access signature url for the password), and that apparently does not work with the BACKUP command.


MySQL Replication: Slave with Different Column From Master

WOW, I remember your last question !!!

If you kept the s_relations_old on the slave, just switch back and start replication.

STOP SLAVE;
ALTER TABLE s_relations RENAME s_relations_bad;
ALTER TABLE s_relations_old RENAME s_relations;
START SLAVE;

You should perform the conversion of the temp_col on the master so that you let that ALTER TABLE replicate to the slave.

You will have to bite the bullet on this one and have some downtime.


Cost of indexed view versus filtered index?

That design is terrible.  You should have separate tables and real foreign keys.  For a quick fix, use the indexed views.  If there are perf problems, fix the design.


You are duplicating the common data on multiple rows which can lead to consistency issues. Look up "normal form" for a discussion about the standard ways to deal with this and help ensure your data is consistent and efficient. Read around database normalisation for more about this.

To break that duplicated data out you could do something like:

                    Message             MessageData
Client              ==============      ================
==============      id_msg    (PK) <--- id_msg (FK) (PK)
id_client (PK) <--- id_client (FK)      key         (PK)
client_name         time_stamp          value
contact_no

You potentially have a second problem though: the message data table presented above is still a property bag or Entity-Attribute-Value (EAV) model which while flexible can be problematical in a number of ways. You'll find many discussions online (including here on DBA.SE) and in print (Bill Karwin's excellent "SQL Anitpatterns" has a relevant chapter) about the pros and cons of EAV structures so I'll not delve into more detail here.

If those array keys in your JSON example are fixed then a better layout might be:

                    Message
Client              ==============
==============      id_msg    (PK)
id_client (PK) <--- id_client (FK)
client_name         time_stamp
contact_no          temperature
                    humidity
                    battery
                    cnt1

Or if those keys are common in the data but there are potentially others that and you need to allow any key/value pair, you can use a hybrid of fixed columns and property bag:

                    Message             OtherMessageData
Client              ==============      ================
==============      id_msg    (PK) <--- id_msg (FK) (PK)
id_client (PK) <--- id_client (FK)      key         (PK)
client_name         time_stamp          value
contact_no          temperature
                    humidity
                    battery

  7.300.000 rows per year

With appropriate table and index design 7.3 million rows is not a lot at all.


Oracle Full Client / Database Client package locations

When it comes to software downloads for production use, the Oracle sites, thankfully, are a little less of a maze - provided that you are familiar with the entrance.

You can find the latest media packs for production use on the Oracle Software Delivery Cloud.

After logging in and accepting terms and export restrictions, select Oracle Database and Linux x86-64. After hitting Go, you should be able to find Oracle Database 11g Release 2 (11.2.0) Media Pack for Linux x86-64 and within; the Oracle Database 11g Release 2 Client (11.2.0.1.0) for Linux x86-64.

You can find Oracle Database 12c Release 1 (12.1.0.2.0) Media Pack for Linux x86-64 and the associated Oracle Database 12c Release 1 Client (12.1.0.2.0) for Linux x86-64 (64-bit) in much the same way.

The patch from 11.2.0.1.0 to 11.2.0.2.0 should be downloaded from Metalink / My Oracle Support.

Starting with 11.2.0.2.0, any subsequent updates can be downloaded from with the installer itself, either as an integrated part of the installation process, or in advance (useful when destined server is restricted from accessing the Internet) by running the installer with the option -downloadUpdates.

I guess you have already considered just running the datapump tools from the actual Oracle home of the database in question?

These days, you may also be offered to try out a new and enhanced version of the software delivery cloud. Look for a big link on the landing page/login page.

On a final note, lots of additional information can be found in the Client Installation Guides available in the Oracle Database Online Documentation 11g Release 2 (11.2) or Oracle Database Online Documentation 12c Release 1 (12.1).


Is there a better alternative to lookup tables to deduplicate text columns in PostgreSQL?

Lots of joins is exactly the usual approach.

You can, however, write a non-STRICT LANGUAGE sql function like lookup_mytext(id) that does a SELECT from the lookup table by id. PostgreSQL will inline it as a subquery, then flatten the subquery to a join. So the effect is the same, but it's notationally simpler to write

SELECT
  x,
  lookup_mytext(y)
FROM t;

than

SELECT
  t.x, tt.val
FROM
  t INNER JOIN tt ON (t.y = tt.id);

Most of the time I prefer to avoid obfuscation and keep the explicit join. But it can become beneficial if this is a real pattern across the app.

See the documentation for rules on function inlining. Check with EXPLAIN.


After formatting the query and especially the expression, it's pretty obvious that the expression can be split/rewqitten in 2 parts:

select SUBSTR( string,
               1,
               POSITION('_' in string) - 1
             )   AS PRD_NAM
from PRD_MWE_GPRSDATPART_PERM_S

where string is:

       SUBSTR( PART_SEQ_NBR,
               POSITION('_' in PART_SEQ_NBR ) + 1,
               CHAR_LEGTH(PART_SEQ_NBR)
             )

The 2 expressions - the main and the (string) sub-expression - are very similar as well. If it's still not obvious what they do, one has only to try the query, putting a few underscore _ characters in the data of the column.


Is this a bug with SQL Server 2016?

Yes. Definitely this is not correct behaviour. I have reported it here and is fixed in SQL Server 2016 SP2 CU9.

As Mikael Eriksson says in the comments sys.database_scoped_configurations and sys.dm_exec_sessions are implemented as views in the format

SELECT ...
FROM OpenRowset(TABLE xxxx)

However comparing the two plans below there is an obvious difference.

DBCC TRACEON(3604);

DECLARE @database_scoped_configurations TABLE(x INT);

INSERT INTO @database_scoped_configurations
SELECT configuration_id
FROM   sys.database_scoped_configurations
OPTION (QUERYTRACEON 8608, QUERYTRACEON 8615, QUERYTRACEON 8619, QUERYTRACEON 8620 );

DECLARE @dm_exec_sessions TABLE(x INT);

INSERT INTO @dm_exec_sessions
SELECT session_id
FROM   sys.dm_exec_sessions
OPTION (QUERYTRACEON 8608, QUERYTRACEON 8615, QUERYTRACEON 8619, QUERYTRACEON 8620 );

Trace flag 8619 output for both of these queries shows

  Apply Rule: EnforceHPandAccCard - x0-> Spool or Top (x0)

SQL Server is apparently not able to ascertain that the source for the TVF isn't also the insert target so it requires Halloween protection.

In the sessions case this was implemented as a spool that captures all rows first. In the database_scoped_configurations by adding a TOP 1 to the plan.
The use of TOP for Halloween protection is discussed in this article. The article also mentions an undocumented trace flag to force a spool rather than TOP that works as expected.

DECLARE @database_scoped_configurations TABLE(x INT);

INSERT INTO @database_scoped_configurations
SELECT configuration_id
FROM   sys.database_scoped_configurations
OPTION (QUERYTRACEON 8692)

An obvious problem with using TOP 1 rather than a spool is that it will arbitrarily limit the number of rows inserted. So this would only be valid if the number of rows returned by the function was <=1.

The initial memo looks like this

Compare this with the initial memo for query 2

If I understand the above correctly it thinks that the first TVF can return a maximum of one row and so applies an incorrect optimisation. The Max for the second query is set to 1.34078E+154 (2^512).

I've no idea where this maximum rowcount is derived from. Perhaps metadata supplied by the author of the DMV? It's also odd that the TOP(50) workaround doesn't get rewritten to TOP(1) because TOP(50) wouldn't prevent the Halloween issue from occurring (though would stop it continuing indefinitely)


DLATK: Resolving (1045, "Access denied for user 'root'@'localhost' (using password: NO)")

This project is written around a passwordless user (and MyISAM as a default engine) indicating databases's aren't the projects strong suite.
The username is fixed as the current logged in user getpass.user
So recommends:
create a non-root user the same as your unix user (replace XXX below with your local user name).
create user XXX identified by '';
grant all on DATABASE.* to XXX@localhost;

DATABASE is the name of the database where the samples where loaded which is probably dla_tutorialby your command line.


mysql slave replication doesn't work due to error 1236

Sounds like this sequence happened:

The slave went down, but you did not notice it.
A few days(?) went by.
The binlogs (on the Master) were automatically purged (either by a script you use, or by expire_logs_days)
Now you noticed the Slave was stopped, but it is too late.

If this is not the only slave, stop another slave and clone it to rebuild this one.

If it is the only slave, you are stuck with rebuilding the slave from the master.  Not fun.

But... You said the Master died and was repaired??  Well, look at the timeframes, and other things that I suggest above.  How many days (#2); how fast is the purging (#3), etc?


Slow remote SELECT statement due to long "client processing time", but fast locally

This issue is now resolved.

It was a network problem, and the SQL box was using a 100 MB/s NIC card, instead of a 10 GB/s NIC card...

A network configuration change to use the correct network card has fixed the problem.  Now we are getting similar performance for all queries from the Production SQL box and from other boxes on the network.

Thanks everyone for your help.


If you are dumping tables t1, t2, and t3 from mydb
mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql

If you have a ton of tables in mydb  and you want to dump everything except t1, t2, and t3, do this:
DBTODUMP=mydb
SQL="SET group_concat_max_len = 10240;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('t1','t2','t3')"
TBLIST=`mysql -u... -p... -AN -e"${SQL}"`
mysqldump -u... -p... ${DBTODUMP} ${TBLIST} > mydb_tables.sql

Give it a Try !!!
UPDATE 2014-03-06 10:15 EST
@RoryDonohue pointed out to me that the GROUP_CONCAT function needs to have its max length extended. I added the session variable group_concat_max_len to my answer with a length max of 10K. Thanks, @RoryDonohue.


ORA-01017: invalid username/password; cannot login with other users except sys user, Oracle Database 12c?

Why was SEC_CASE_SENSITIVE_LOGON set to false? That is deprecated in 12c. This is a known issue that I encountered many times.
Case-Insensitive Passwords and ORA-1017 Invalid Username or Password

The Oracle Database 12c release 2 (12.2) default authentication
protocol is 12 (Exclusive Mode). This protocol requires case-sensitive
passwords for authentication. Review your options if you have earlier
release password versions.
Starting with Oracle Database 12c release 2 (12.2), the default value
for the SQLNET.ORA parameter ALLOWED_LOGON_VERSION_SERVER is changed
to 12. This parameter refers to the logon authentication protocol used
for the server, not the Oracle Database release.
By default, Oracle no longer supports case-insensitive password-based
authentication; only the new password versions (11G and 12C) are
allowed. The case-insensitive 10G password version is no longer
generated.
...
If the following conditions are true, then you may have accounts that
are prevented from logging into the database after upgrading to 12.2:
You are upgrading a server that has user accounts created in an
earlier Oracle Database release.
User accounts created in the earlier release use a case-insensitive
password version from an earlier release authentication protocol, such
as the 10G password version.
Earlier release user accounts have not reset passwords.
The server has been configured with SEC_CASE_SENSITIVE_LOGON set to
FALSE, so that it can only authenticate users who have a 10G
case-insensitive password version.
...
Option for Servers with Accounts Using Only 10G Password Version
After you upgrade to Oracle Database 12c release 2 (12.2), complete
the following procedure to enable accounts using the 10G password
version:
Log in as an administrator.
Edit the SQLNET.ORA file to change the
SQLNET.ALLOWED_LOGON_VERSION_SERVER setting from the default, 12, to
11 or lower. For example:
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
After you change to a more permissive
SQLNET.ALLOWED_LOGON_VERSION_SERVER setting, expire users’ passwords
to require them to change their passwords. For detailed information,
refer to Oracle Database Security Guide.


Can SQL Server Transactional Replication do this?

I believe your DBA is referring to Publishing Stored Procedure Execution in Transactional Replication.

With Transactional Replication you have the option to published the definition of the stored procedure or the execution of the stored procedure.  Publishing stored procedure execution can provide significantly better performance for large batch operations since only the procedure execution is replicated, bypassing the operation being sent to Subscribers as a large, multi-step transaction.

If you have anymore questions please let me know. I hope this helps.


SQL Server 2008 R2 Lockup for 10 minutes

We had another mysterious SQL crash yesterday afternoon (no logs for that one either--just a SQL error message). We failed over to the mirror and ran some burn-in tests on this system and it turns out this server had a memory module that must have recently developed issues. I believe this is likely the cause of these issues. Thanks to all who looked into this!


Is there a way to insert multiple rows into a table with default values for all columns?

Using generate_series() and ctes. Tested in rextester.com:

create table t
( tid serial primary key,
  i int default 0,
  name text default 'Jack'
) ;

with ins as
  (insert into t (i, name)               -- all the columns except any serial
   values (default, default)
   returning i, name
  )
insert into t
  (i, name)
select
  ins.i, ins.name
from
  ins cross join generate_series(1, 9);  -- one less than you need

For the case when there is only one column and it's a serial, I see no way to use the default. Using the generate_series is straight-forward:

insert into course
  (course_id)
select
  nextval('course_course_id_seq')
from
  generate_series(1, 10);

If there are other, more "peculiar" default values, like a UUID function or the non-standard clock_timestamp(), the statement will have to be adjusted accordingly, like the serial case.


log ship from older version to newer one

As the error message mentions, you can't use WITH STANDBY when restoring to a newer version of SQL Server.  The database upgrade process has to run in that case, and it can't do that in standby mode because the database is essentially read-only.  This is documented in RESTORE Statements - Arguments (Transact-SQL):

  STANDBY is also not allowed when a database upgrade is necessary.

You can use NORECOVERY rather than STANDBY for the restores instead.  You won't be able to use the new server as a readable copy of the database, which might be a dealbreaker.  But it supports the scenario of migrating to the newer server (if that's what your end goal is).

I would double check your configuration. Specifically, check that you selected "No recovery mode" for the "Database state when restoring backups" option on the Restore tab for the target (secondary) database.  It's step 17 in Configure Log Shipping (SQL Server) - Using SQL Server Management Studio

  On the Restore tab, under Database state when restoring backups, choose the No recovery mode or Standby mode option.

You can review the settings by right-clicking the database, choosing properties, and then looking at the "transaction log shipping" tab.

Clarifying a few questions and statements from the comments:

  It seems that at the time of database open on 2016, it actually applies/restores transaction log files. Please confirm!"

No, that's not how it works. The transaction logs are applied during the RESTORE LOG operation. So the deleted source transaction logs aren't a problem. If you're getting that error about missing transaction logs, then it sounds like some of the restores failed, or they were restored out of order, etc.

  However, when we check the timestamp of data and log files of the configured databases on 2016, it shows the timestamp when this was configured

You can't use Windows/NTFS timestamps as a valid indication of the SQL Server data files being used, and thus it can't be used as evidence that the transaction logs are not being applied when you restore them.


Reading SQL Performance Counters using PowerShell script

Try this:

Get-Counter "$($instance):Buffer Manager\Buffer cache hit ratio"

What happens is that your $instance variable isn't isolated, so therefore you need to explicitly state what the variable is and where it ends.  You can do that with the above notation (dollar sign and parenthesis around the variable name).

Take a look at the different outputs of the following:

Write-Host "$instance:Buffer Manager\Buffer cache hit ratio"

The output is:

 Manager\Buffer cache hit ratio

Now with this correct notation:

Write-Host "$($instance):Buffer Manager\Buffer cache hit ratio"

The output is this:

\\MyMachineName\MSSQL$InstanceName:Buffer Manager\Buffer cache hit ratio

A common way to troubleshoot these sorts of problems is to use the above methodology, by seeing exactly what you're passing into other cmdlets/functions/etc.


Is it safe to disable sync_binlogs, innodb_flush_log_at_trx_commit and innodb_doublewrite on a site-redundant galera cluster?

I've done some research on this now, so I'll answer on my own question.

Summary first

If the performance problems are observed only on one node, it's perfectly safe to turn off innodb_doublewrite and innodb_flush_log_at_trx_commit on the affected node - just remember to use one of the other nodes for bootstrapping should the whole cluster fail.

# echo "[server]" >> /etc/mysql/conf.d/temp_perfomance_hax.cnf
# echo "innodb_doublewrite = 0" >> /etc/mysql/conf.d/temp_performance_hax.cnf
# echo "innodb_flush_log_at_trx_commit = 2" >> /etc/mysql/conf.d/temp_performance_hax.cnf
# systemctl restart mysql # innodb_doublewrite cannot be set run-time

Also, in a true site-redundant galera cluster, it should be reasonably safe to put innodb_flush_log_at_trx_commit to 2 and sync_binlog to 0 on any number of nodes.  If one is experiencing performance problems, if uptime and performance is important, and if one lost transaction probably isn't a matter of life and death - then no hesitations needed, just go ahead and do it.

set global sync_binlog = '0';
set global innodb_flush_log_at_trx_commit = 2;

and one can probably go ahead and turn off double write as well if performance is more important than ACID-compliance.  The probability of disasters is pretty low.

I can see two reasons for sticking to the "safe" settings:

Preparedness for the worst-case-scenario: what if the whole cluster melts down simultaneously?
More robustness on the nodes

Node robustness: keep innodb_doublewrite turned on

innodb_doublewrite can be safely turned off at one node without losing ACID-complience, but it may still be better to have it turned on.  According to When is it safe to disable InnoDB doublewrite buffering? turning off innodb-doublewrite may cause corrupted data, in that case it may be needed to wipe the data partition on the downed node and start it up again.  This comes with three costs:

manual sysadmin intervention
elevated levels of IO on the cluster while the corrupted node is performing a SST
the cluster will have to run with reduced redundancy until the node is fully operative again.

(As far as I can understand, a missing flush on log files shouldn't cause problems starting a db node - i.e. for innodb_flush_log_at_trx_commit the manual explicitly states that  "InnoDB's crash recovery works regardless of the value")

Reasons why the whole cluster may burn down at once

Hard explosions

Consider innodb_flush_log_at_trx_commit to be set to 2, binlog_sync to be set to 0 and innodb-doublewrite-buffering set to 1, this may give performance gains, and as far as I can understand the only failure mode it won't cover is if a quorum of nodes are suffering a power-loss or kernel panic.  For a true multi-site-setup, those seems rare enough that one may want to neglect them.

If the nodes are located nearby, a local problem, i.e. power outage, flooding, fire, etc can knock all of the nodes out in the same time.  I've multiple times witnessed a whole server site going down for the weirdest reasons even though everything was supposed to be redundant.
Could solar storms affect the whole cluster simultaneously?  Especially if the cluster is located in the same city and in polar regions?  I've seen two laptops crashing simultaneously, being in the polar region during a solar storm, and with no other plausible reasons (different OS'es, it wasn't under a leap second, etc).
If all nodes runs the same OS, some OS kernel problem could potentially cause all nodes to crash hard with a kernel panic in exactly the same time.

Software crashes

I believe that if innodb_flush_log_at_trx_commit is set to 0 or innodb_flush_log_at_trx_commit is set to 0, then a mysql crash affecting all nodes simultaneously could cause data loss.

Has anyone ever observed galera crashing in such a way?

A system admin or buggy script doing "kill -9" simultaneously on mysql on all the nodes too ... but if the system admin wants to cause data loss, he will probably find a way regardless of those settings.

Cluster going down for other reasons

In those cases, those settings shouldn't matter:

Sudden and total network isolation between all three nodes, or leaving the cluster fragmented so no quorum can be found.  One would need to bootstrap the cluster after such an incident, and if one really want to be 100% sure that no transactions are lost, one needs to manually investigate binlogs (see more on that below) - but since neither mysql nor the OS has crashed, all state will eventually be synced, no matter the sync settings.
Bad timing - i.e. one node going down and while it's down another node could go down or there could be some network isolation.  Afterwards, the cluster needs to be bootstrapped.  However, everything that the nodes have received will be written to disk, so the above mentioned settings will not affect anything.  (We've had this coming over us once).

Manual intervention is needed on cluster failures

No matter the settings, to be 100% sure that no transaction is lost after a catastrophic cluster failure, it's actually needed to manually inspect/merge binlogs.  We need to bootstrap the cluster using only one node, but we don't know if this node got all the changesets, this info is only available by investigating the binlogs from all nodes (with log_slave_updates turned on, one can probably do with binlogs from a quorum of the nodes).

This means that you need to make a decision in advance - what should be the priority if we encounter a catastrophic cluster meltdown?  Is the priority to get the cluster up and running again as fast as possible, or is it to make 100% sure no transactions have been lost before bootstrapping the cluster?

Your mileage may vary

If some downtime is acceptable under extreme circumstances (a full catastrophic db cluster meltdown is extreme) and if it's paramount to ensure all transactions are in place before returning to production mode, then one needs to make 100% sure that the binlogs are synced and kept, and that the backup regime is good.  Syncing innodb logs and double write is then non-critical and can be turned off if that gives performance benefits.

If "getting the cluster up and running again as fast as possible" is important, then it's important to set innodb_flush_log_at_trx_commit = 1 and set innodb_doublewrite = 1 at a quorum of the nodes; if the cluster should go down then it can be quickly bootstrapped from any of the servers with those settings turned on.  (Some consideration may be needed.  On our previous meltdown node C fell down and got up again.  Network glitch between node A and node B before node C got properly joined into the cluster, resulting in a shutdown.  Obviously, we can bootstrap from A or B without any data loss, but not from C).


Can I use PHP function strtotime() in SQL queries?

UPDATE table
SET lasttime=TIME_FORMAT(SEC_TO_TIME(((SUBSTRING_INDEX(time,':',1)*60)+SUBSTRING_INDEX(time,':',-1))-480),'%i:%s');

Do the calculation in seconds then back to time,no need for php

FIDDLE


Can I retrieve all database objects owned by a particular database?

SQL Server doesn't aggregate this type of information for you in any way, so you're going to use some kind of loop to get this information in a single resultset. One way is to concatenate a set of UNION ALL queries, but I think an explicit cursor populating a #temp table one row at a time is an easier way to digest what is happening, especially as the source query gets more complex:

First, create a #temp table:

CREATE TABLE #objs
(
  db        sysname PRIMARY KEY,
  functions int,
  procs     int,
  triggers  int,
  views     int
);

Then:

DECLARE @sql nvarchar(max) = N'SELECT DB_NAME(),
  SUM(CASE WHEN CHARINDEX(N''FUNCTION'', o.type_desc) > 0 THEN 1 ELSE 0 END) [functions],
  SUM(CASE WHEN CHARINDEX(N''PROCEDURE'', o.type_desc) > 0 THEN 1 ELSE 0 END) [procs],
  SUM(CASE WHEN CHARINDEX(N''TRIGGER'', o.type_desc) > 0 THEN 1 ELSE 0 END) [triggers],
  SUM(CASE WHEN CHARINDEX(N''VIEW'', o.type_desc) > 0 THEN 1 ELSE 0 END) [views]
FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id;';

DECLARE @n sysname, @e nvarchar(512);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
  SELECT QUOTENAME(name)
  FROM sys.databases
  WHERE database_id > 4
  AND state = 0 -- online
  ORDER BY name;

OPEN c;
FETCH NEXT FROM c INTO @n;

WHILE @@FETCH_STATUS <> -1
BEGIN
  SET @e = @n + N'.sys.sp_executesql ';
  INSERT #objs EXEC @e @sql;
  FETCH NEXT FROM c INTO @n;
END
CLOSE c;
DEALLOCATE c;

SELECT * FROM #objs;

Note that for any database that doesn't have any of these objects you will get a row of NULL values instead of 0s. If you want to change that, just fix your pattern from:

SUM(CASE WHEN ... THEN 1 ELSE 0 END)

To:

COUNT(CASE WHEN ... THEN 1 END)

Clean up:

DROP TABLE #objs;

You can also take a look at sp_foreachdb.

For completeness, here is an example using dynamic SQL:

DECLARE @query nvarchar(max) = N'
 UNION ALL SELECT N''$dbs$'',
  SUM(CASE WHEN CHARINDEX(N''FUNCTION'', o.type_desc) > 0 THEN 1 ELSE 0 END) [functions],
  SUM(CASE WHEN CHARINDEX(N''PROCEDURE'', o.type_desc) > 0 THEN 1 ELSE 0 END) [procs],
  SUM(CASE WHEN CHARINDEX(N''TRIGGER'', o.type_desc) > 0 THEN 1 ELSE 0 END) [triggers],
  SUM(CASE WHEN CHARINDEX(N''VIEW'', o.type_desc) > 0 THEN 1 ELSE 0 END) [views]
FROM $dbv$.sys.sql_modules m INNER JOIN $dbv$.sys.objects o ON m.object_id = o.object_id';

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += REPLACE(REPLACE(@query,
    N'$dbs$', REPLACE(name,'''','''''')),
    N'$dbv$',QUOTENAME(name))
  FROM sys.databases
  WHERE database_id > 4
  AND state = 0 -- online
  ORDER BY name;

SET @sql = STUFF(@sql, 1, 12, N'') + N';';

EXEC sys.sp_executesql @sql;


COUNT beginning of, mid of & end per month for many years

One thing I can think of, is to generate a date range from the input date that splits the month into three parts. It's quite easy to keep the first two parts constant and have the third one vary:

create function get_first(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date, date_trunc('month', p_input)::date + 10, '[]')
$$
language sql
immutable;

create function get_second(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date + 11, date_trunc('month', p_input)::date + 20, '[]')
$$
language sql
immutable;

create function get_third(p_input date)
  returns daterange
as
$$
  select daterange(date_trunc('month', p_input)::date + 21, (date_trunc('month', p_input) + interval '1 month')::date, '[)')
$$
language sql
immutable;

With that you can do the following aggregation:

select animal,
       count(*) filter (where get_first(from_date) @> from_date) as start,
       count(*) filter (where get_second(from_date) @> from_date) as mid,
       count(*) filter (where get_third(from_date) @> from_date) as "end"
from occurrence
group by animal;

The three functions can be enhanced/changed to deal with the variable length of a month, but that would require much more complex calculations. It would boil down to making the +1 or +20 a percentage of the number of days in the month.

If you need to find the highest of those three parts, you can unpivot the values and then using distinct on() to get it:

select distinct on (x.animal) x.*
from (
  select animal,
         to_char(from_date, 'yyyy-mm') as month,
         count(*) filter (where get_first(from_date) @> from_date) as start,
         count(*) filter (where get_second(from_date) @> from_date) as mid,
         count(*) filter (where get_third(from_date) @> from_date) as "end"
  from occurrence
  group by animal, month
) t
  cross join lateral (
    values (animal, start, month, 'start'), (animal, mid, month, 'mid'), (animal, "end", month, 'end')
  ) x (animal, num, month, section) --on true
order by x.animal, num desc

The cross join lateral essentially turns the three columns into rows and then distinct on () is used to pick the highest value of the three for each animal

Online example


Prevent execution of heavy-loading and / or runaway queries in postgres

You can use statement_timeout to limit execution time:
https://www.postgresql.org/docs/current/runtime-config-client.html


SQL Server experiencing THREADPOOL waits while still having plenty of available workers

If you have the max degree of parallelism set to zero, which is the default, then queries that might benefit from parallelism will consume 32 threads.  With the number of workers set to 960, again the default for your setup, you could only run 30 simultaneous parallel queries.

I'd recommend setting max degree of parallelism to something sane, like 8.


Using SELECT to call a function in a jdbc migration

You can avoid this by using the DO command like so:

DO $$
BEGIN
    PERFORM AddGeometryColumn('parks', 'park_geom', 128, 'MULTIPOLYGON', 2 );
END;
$$

Note the BEGIN and END are necessary as it has to be a valid plpgsql block.


Failed allocate pages: FAIL_PAGE_ALLOCATION 1

The output of errorlog had dbcc memorystatus dump and what I noticed was

Process/System Counts                         Value(in Bytes)
---------------------------------------- ----------
Available Physical Memory                1217605632---1.1 G
Available Virtual Memory                 140627167866880
Available Paging File                    5656502272
Working Set                               305238016
Percent of Committed Memory in WS                99
Page Faults                                27923310
System physical memory high                       0
System physical memory low                        0
Process physical memory low                       1--Memory Low
Process virtual memory low                        0
2016-06-14 04:28:27.41 Server

Please note the available physical memory is very low. There was almost no memory in buffer pool

Regarding clerk which is consuming more memory

MEMORYCLERK_SQLQERESERVATIONS (node 0)           KB
---------------------------------------- ----------
VM Reserved                                       0
VM Committed                                      0
Locked Pages Allocated                            0
SM Reserved                                       0
SM Committed                                      0
Pages Allocated                            22599824  --21.5 G

Page Life Expectancy                             64

Now on server where max server memory is 28 G if MEMORYCLERK_SQLQERESERVATIONS is taking 21.5 G that is definitely a problem. This is what causing the OOM condition.

What is MEMORYCLERK_SQLQERESERVATIONS

This is a memory clerk in SQL Server which tracks memory allocated to query which involves Sort or hash operations during execution. These operators can be the largest memory consumers for a query.

Why OOM error due to this

When query involving sort and hash operations is executed  it will make a reservation request based on the original query plan which contained a sort or a hash operator. Then as the query executes, it requests the memory and SQL Server will grant that request partially or fully depending on memory availability. There is a memory clerk (accountant) named ‘MEMORYCLERK_SQLQERESERVATIONS’ which tracks memory allocation to such requests . Now in your scenario following could be happening

Query is requesting so much memory grant that SQL Server is only able to provide it a limited amount, this limited amount is called "Required Memory", so that it starts executing and while executing the query, because memory requirement was large and SQL Server cannot provide it as there was no memory in resource pool, the query fails with OOM error. The memory required when query is running is called "Additional Memory"
There was Bug fixed in SQL Server 2012 Sp1 CU4 where query requested huge amount of memory grant causing it to be drastically slow or subsequently failing with OOM error. The possibility that bug resurfaced cannot be ruled out considering fact that QEReservations hogged all of the buffer pool
Since the clerk has already taken 90 % of memory. Required Memory for new query is not available and query fails with OOM error.
Your tables and indexes has skewed statistics which is forcing optimizer to build sub optimal plan causing it to request much more memory grant than actually required and in turn creating issues.
Lastly the queries running on SQL Server requires some serious tuning.

As per This Blogs.msdn article

  What Can a Developer Actually Do about Sort/Hash Operations?

  Speaking of re-writing queries, here are some things to look for in a
  query that may lead to large memory grants.

  Reasons why a query would use a SORT operator (not all inclusive
  list):

ORDER BY (T-SQL)

GROUP BY (T-SQL)

DISTINCT (T-SQL)

Merge Join operator selected by the optimizer and one of the inputs of the Merge join has to be sorted because a clustered index is

  not available on that column.

  Reasons why a query would use a Hash Match operator (not all inclusive
  list):

JOIN (T-SQL) – if SQL ends up performing a Hash Join. Typically, lack of good indexes may lead to the most expensive of join operators

  – Hash Join. Look at query plan.

DISTINCT (T-SQL) – a Hash Aggregate could be used to perform the distinct. Look at query plan.

SUM/AVG/MAX/MIN (T-SQL)– any aggregate operation could potentially be performed as a Hash Aggregate . Look at query plan.

UNION – a Hash Aggregate could be used to remove the duplicates.

To further understand the problem I would require you to add output of below queries into your question. I would also like you to add output of Paul Randal Wait stats query. The source of query is This Blog, I suggest you to read the blog.

SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

--Find who uses the most query memory grant:

SELECT mg.granted_memory_kb, mg.session_id, t.text, qp.query_plan
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
ORDER BY 1 DESC OPTION (MAXDOP 1)

--Search cache for queries with memory grants:

SELECT t.text, cp.objtype,qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
JOIN sys.dm_exec_query_stats AS qs ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qp.query_plan.exist(‘declare namespace n=”http://schemas.microsoft.com/sqlserver/2004/07/showplan“; //n:MemoryFractions’) = 1

There are few other things I would like you to check for queries running on system.

Select granted_query_memory,session_id,command from sys.dm_exec_requests

This will show you how much memory is granted to queries running on the system.

If you can see XML actual execution plan you have MemoryGrant=xxxxx can you collect this value for costly queries.

All the above will show us if there is problem in query or some other issue as to why it is requesting so much memory for execution.

EDIT

From various query outputs you pasted.

You can see the requested_memory_kb for large number of queries are approx 5G, this is large memory grant, ideally it should be few MB's. Do note that required_memory_kb is just around 5 MB and granted_query_memory is NULL this is because due to memory pressure SQL Server is just able to provide minimum memory to start the query but not able to provide additional memory for query execution resulting query to fail with OOM error.

The query costs for queries requesting huge memory is also high which leads me to believe that either statistics are skewed or queries are written poorly. Other possibility would be query not supported by proper index. Number of queries requesting such a huge memory grant is good in number.

For above queries see granted_query_memory it is all in GB. The first 3 queries running used approx 15 G of memory which almost used 50 % of memory. In SQL Server millions of process run which require memory in some way so you can see if 3 queries are using 50% of available memory OOM issue is bound to occur.

Solution

You should seriously consider tuning the first 4 queries in above screenshot

Make sure you run index rebuild and stats update at least weekly so that skewed stats does not force optimizer to produce bad plan.

Use resource governor and create a resource pool and workload group and run queries which are requesting large memory grant in this pool. You can limit the memory request with parameter request_max_memory_grant_percentage. An example is shown in this Blog. This is just alternate method till you tune all your queries.


Mysql : Maximum number of connections on a per user basis - Possible?

You can set this per-user on a per-hour basis.
Under the WITH option for the MySQL GRANT command yu gave the following:
with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

You can set

queries per hour
updates per hour
inserts per hour
connection per hour

This can give you finer per-hour granularity not only in terms of the number of connections made, but what can be executed.
The columns for these user grants are locate in mysql.user:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select column_name from information_schema.columns
    -> where table_schema='mysql'
    -> and table_name='user'
    -> and column_name like 'max%'
    -> order by ordinal_position;
+----------------------+
| column_name          |
+----------------------+
| max_questions        |
| max_updates          |
| max_connections      |
| max_user_connections |
+----------------------+
4 rows in set (0.01 sec)

mysql>

I wrote about these things before

May 24, 2011 : What are user connections - when are the created and destroyed?
Jun 16, 2011 : Would it be sensible to create a MySQL user for each user account of a web application?
Mar 22, 2012 : How can I limit MySQL connections?

Your Original Question
As shown above, you can set user per-hour user limits. However, I came to notice that you want just DB Connections open for any given user.
You may require some special customized authentication that counts how many connections a user has. In MySQL 5.1+ you could possibly script this:
SELECT COUNT(1) ConnectionCount,user
FROM information_schema.processlist
WHERE user <> 'system user'
GROUP BY user;

This would allow you to know the counts for any connected user.
You may want to look into connection pooling and persistent connections to regulate the number of users connected.


Inherit INNER JOIN fields in MySql SELECT QUERY

you can join on table_C

SELECT nameC, nameA, nameB
FROM table_Ref r
INNER JOIN table_A a ON r.idA=a.idA
INNER JOIN table_B b ON r.idB=b.idB
INNER JOIN table_C c ON a.idC=c.idC;


If your pluggable database is not open, then you will not be able to connect to it in regular fashion. You need to check the connection descriptor that you application uses in order to check where it connects.

When you create a PDB, it is automatically added in the listener as a service. You can check using

lsnrctl status

To connect to a specific pluggable database, you need to use its service name.
You can start a pluggable database by connecting to the container and issuing:

alter pluggable database <PDB_NAME> open;

To switch your context to a specific pluggable database when connected to the container, use:

alter session set container = <PDB_NAME>;

After that all commands will be ran against that pluggable database.

References:

Introduction to the Multitenant Architecture

Connecting to Container Databases (CDB) and Pluggable Databases (PDB)


Trying to map postal codes to their respective cities, but running into some issues

Your question is not very clear.

You say you are trying to create a database of cities and zip codes, but then you say you are using Geonames, which is a database of cities and zip codes. Why are you trying to re-create it?

Places are not hierarchical. You need to think about the institution responsible for the place's boundaries. State boundaries are determined by the US Constitution. City/county boundaries are determined by city and/or state governments. Zip boundaries are determined by USPS.

Some towns contain zips. Some zips contain towns. Some zips cross state boundaries.

A mailing address needs a zip AND some other info. Usually it is a city OR a "surveyed area" such as a street address (which in turn may belong to a city).

A university also likely has more than one address ;)

I would use a little table inheritance and give country, state/prov, city and surveyed area all the same supertype, that way you can point a single foreign key at any of them.


PostgreSQL - Out of memory on count group by query

Well you can use set enable_hashagg = false to disable the HashAggregate plan type, which should force it so choose an alternate plan.

You should probably also make sure you have analyzed the table, as it may that it is a lack of statistics that is stopping postgres realising it won't have enough memory for the result, which is going to be pretty massive.


linked server be made using the login's current security context

From BOL:

Be made using the login's current security context

Specify that a connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.

http://technet.microsoft.com/en-us/library/ms188477(v=sql.105).aspx

Using the login's current security context will use whatever user is logged in to the local machine.  Be it Windows authentication or SQL authentication.

So, in your example above, if JOEsmith logs into server A and runs a query against server B, it will execute the query on server B using JOEsmith, as long as JOEsmith is a valid logon for server B.


GETDATE() uniqueness when used on INSERT

GETDATE() is not guaranteed to be unique, no. Especially if it is a datetime, where milliseconds are rounded up or down, and of course when not all data is coming from the same user, you are almost guaranteed to have collisions.

Of course, a clustered index does not need to be unique, since SQL Server will make it so if it isn't (but only when it needs to). If you need to identify a specific row yourself (as opposed to just the uniquifier for SQL Server's internal usage), and there are no other candidate key columns (this is possible with things like event logging tables), you could add a non-clustered primary key that is, say, an IDENTITY column. Or if you really want web scale - and care more about insert performance than storage or any subsequent use of the data - you could use a uniqueidentifier column populated with NEWID().

Example

Let's take a look at an example, and see the differences.

USE tempdb;
GO

-- rely on uniqifier

CREATE TABLE dbo.Test1
(
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test1(g);

-- use an IDENTITY column

CREATE TABLE dbo.Test2
(
  i INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test2(g);

-- use a GUID

CREATE TABLE dbo.Test3
(
  n UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()
    PRIMARY KEY NONCLUSTERED,
  g DATETIME
);
CREATE CLUSTERED INDEX x ON dbo.Test3(g);
GO

Insert speed

I populated all three tables with about 500,000 rows, using the following script:

SET NOCOUNT ON;
GO
SELECT SYSDATETIME();
GO
INSERT dbo.Test1(g)
  SELECT DATEADD(SECOND, ABS([object_id])/1000, GETDATE())
  FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();
GO
INSERT dbo.Test2(g)
  SELECT DATEADD(SECOND, ABS([object_id])/1000,
  GETDATE()) FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();
GO
INSERT dbo.Test3(g)
  SELECT DATEADD(SECOND, ABS([object_id])/1000,
  GETDATE()) FROM sys.all_columns;
GO 100

SELECT SYSDATETIME();

Results:

Uniquifier:    2.26 seconds
IDENTITY:      3.89 seconds
GUID:          5.06 seconds

Scan Speed

SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test1;
SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test2;
SELECT SYSDATETIME();
DBCC DROPCLEANBUFFERS;
SELECT * FROM dbo.Test3;
SELECT SYSDATETIME();

Results:

Uniquifier:  3.89 seconds
IDENTITY:    4.21 seconds
GUID:        4.85 seconds

Space used

Looking at simple results from sp_spaceused:

EXEC sp_spaceused N'dbo.Test1';
EXEC sp_spaceused N'dbo.Test2';
EXEC sp_spaceused N'dbo.Test3';

Results:

                      Rows      Reserved    Data      Index size
Test1 - Uniquifier    513300    14024 KB    13880 KB       96 KB
Test2 - IDENTITY      513300    29072 KB    15792 KB    13200 KB
Test3 - GUID          513300    49936 KB    21960 KB    27928 KB

The uniquifier takes up less space than the IDENTITY column (and obviously both take less than the GUID), since it is only needed for collisions (and probably other optimizations I'm not aware of, such as compression).

We can also look at the index pages for the clustered index on the datetime column (index id = 1) and the non-clustered primary key (index id = 2):

DBCC TRACEON(3604,-1);

-- Uniquifier
DBCC IND('tempdb', 'dbo.Test1', 1); -- 1,747 index pages
-- no second index for this table

-- IDENTITY
DBCC IND('tempdb', 'dbo.Test2', 1); -- 1,987 index pages
DBCC IND('tempdb', 'dbo.Test2', 2); -- 1,637 index pages

-- GUID
DBCC IND('tempdb', 'dbo.Test3', 1); -- 2,764 index pages
DBCC IND('tempdb', 'dbo.Test3', 2); -- 3,472 index pages

Page/Row Contents

And finally, we can take a look at a specific page to see what is stored in a row. I simply took the first row from each of the clustered index DBCC IND results from above (your page id values will almost certainly be different):

DBCC PAGE('tempdb',1, 153, 1);
DBCC PAGE('tempdb',1, 199, 1);
DBCC PAGE('tempdb',1, 217, 1);

Uniquifier - pay particular attention to length/record size:

Slot 0, Offset 0x60, Length 15, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Record Size = 15
--------------^^

Memory Dump @0x000000000F7EA060

0000000000000000:   10000c00 0333ba00 fba20000 020000††††.....3º.û¢.....

Slot 1, Offset 0x6f, Length 23, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 23
--------------^^
Memory Dump @0x000000000F7EA06F

0000000000000000:   30000c00 0333ba00 fba20000 02000001 †0....3º.û¢......
0000000000000010:   00170001 000000††††††††††††††††††††††.......

IDENTITY seems to have 4 extra bytes in the clustered index:

Slot 0, Offset 0x60, Length 19, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Record Size = 19
--------------^^

Memory Dump @0x0000000011DAA060

0000000000000000:   10001000 a735ba00 fba20000 03020000 †....§5º.û¢......
0000000000000010:   030000†††††††††††††††††††††††††††††††...

Slot 1, Offset 0x73, Length 27, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 27
--------------^^
Memory Dump @0x0000000011DAA073

0000000000000000:   30001000 a735ba00 fba20000 04020000 †0...§5º.û¢......
0000000000000010:   03000001 001b0001 000000†††††††††††††...........

GUID has an extra 16 bytes in the clustered index:

Slot 0, Offset 0x60, Length 31, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP
Record Size = 31
--------------^^

Memory Dump @0x0000000011DAA060

0000000000000000:   10001c00 393aba00 fba20000 f3233e73 †....9:º.û¢..ó#>s
0000000000000010:   a36e114b b1229a80 a5cb090a 030000††††£n.K±".¥Ë  ....

Slot 1, Offset 0x7f, Length 39, DumpStyle BYTE
----------------------------^^

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 39
--------------^^
Memory Dump @0x0000000011DAA07F

0000000000000000:   30001c00 393aba00 fba20000 c7bb2544 †0...9:º.û¢..Ç»%D
0000000000000010:   4ad3574f a2c2029f e4abc9d7 03000001 †JÓWO¢Â.ä«É×....
0000000000000020:   00270001 000000††††††††††††††††††††††.'.....

Conclusion

Seems to me, by all accounts, you are better off letting the uniquifier do its own thing (assuming you don't expect to be able to differentiate between two rows with the exact same date/time value). The only time you can get in trouble with this, if any single value is duplicated 2,147,483,648 times, at which point you will overflow the uniquifier range of integer.


With Snapshot or RCSI isolation you get optimistic locking model where Readers don't block Writers and Writers don't block Readers. Beware that with optimistic locking query results for Readers may differ from pessimistic locking, since Readers don't wait for Writers to complete

Would Snapshot or RCSI isolation help when performing the insert/update ops in Step 3?

Writers still block Writers since Exclusive locks are held, but it of course depends which rows / pages / tables are locked. So no, I would say RSCI or Snapshot won't help for step 3
In fact, Snapshot isolation may potentially even make things worse, since it operates on transaction level while RCSI operates on statement level

What are the risks of introducing update conflicts?

According to the table below, you may get the update conflicts in Snapshot isolation (not RCSI)


SQL Server 2008 R2 Express with management tools installation failure

I found the cause of the problem. Recently I'd set my default program files folder to my hdd, because my C: is a 60 GB SSD. The key is located in the register at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion. The keys "ProgramFilesDir" and "ProgramFilesDir (x86)" are the ones that need to be default ("C:\Program Files" and "C:\Program Files (x86)") in order for it to work. Sorry for the unnecessary post.


Like Gaius said, review the answers at How-to implement an entity with an unknown maximum number of attributes?

Rewriting for MySQL Syntax from the answer by Eric Humphrey at above link:

You could do this. It would allow good performance for normal duration games, while allowing you to also store long running games.

CREATE TABLE InningRuns (
    GameId INT NOT NULL AUTO_INCREMENT,
    Team CHAR(4) NOT NULL, --'Home','Away'
    Inning1 TINYINT, --Seeing how more than 255 runs are not really possible in an inning
    Inning2 TINYINT,
    [...],
    Inning9 TINYINT,
    ExtraInnings VARCHAR(2000), --Use to hold any runs in extra innings, store multiple values using some delimiter like comma (,), pipe (|), semicolon (;), or anything that is not part of the data.
    PRIMARY KEY (GameId, Team)
)

You could further normalize and have a row for each unique combination of game, team, & inning. This would allow you as many innings as the InningId datatype would allow.

CREATE TABLE InningRuns (
    InningRunId INT NOT NULL AUTO_INCREMENT,
    GameId INT NOT NULL,
    Team CHAR(4) NOT NULL, --'Home','Away'
    InningId TINYINT, --Seeing how more than 255 innings might be excessive
    Runs TINYINT,
    PRIMARY KEY (InningRunId),
    UNIQUE (GameId, Team, InningId)
)


File growth and location change for system health extended events

I see system health file location by default goes to log folder, can this be changed as its very hard to find for 200+ servers and thus making it a standard location across x servers

Yes, you'll have to edit the extended event definition:

ALTER EVENT SESSION [system_health] ON SERVER
DROP TARGET package0.event_file
ALTER EVENT SESSION [system_health] ON SERVER
ADD TARGET package0.event_file(SET filename=N'F:\MyLocation\system_health.xel',max_file_size=(50),max_rollover_files=(40))
GO

  a) Why don't we see additional 5 files yet listed once we change number of files to 10?

Because it writes to the files in a rolling fashion, like SQL Server Errorlogs... so you can set it to keep 99 errorlogs but it won't just go out and make 99. When the data rolls over, a restart occurs, etc., then the new files will be created if needed.

  b) How are the files filling up the space as I see there is no sequential filing say oldest files [ 3 days old] are still 3 MB while some of latest ones [ consider today] have already filled with 5 MB and going back to old ones to record data.

They are used as needed but never "go back" and fill the files up that were previously used. The old ones will eventually be aged out and deleted with a new file created in place of the old deleted one. So yes, everything you're seeing is by design. It means the 3MB one had 3MB of data written to it before something happened (most like a restart of the service) and then the next one had 5MB written to it and rolled over to a new file.

  How is this writing mechanism done?

If it's set for a delay or synchronous it'll wait that long before flushing, otherwise it flushes the buffer out to the file. Not sure what you're looking for with this question.

  I need to understand this to figure out how many files and how big can they be created before making a change

It's going to be different for each instance, though most instances I would hope stay up and running a long time between restarts... if so, most should fall in line with an average amount of testing to keep whatever data you're looking at collecting for however long you're looking to keep it.


these are the standard permission:

for this mapped user settings:

you have in place some kind of deny for your users.


This is one of those where the correct answer is: it depends. It all depends on your environment, on your data. What you can and should do, is test how frequently you need to run them in lower environments. Play with them under load/stress. Figure out which options you may need to use.

But to give you something to start with, and not knowing the distribution of your data, I would go with RUNSTATS daily. Maybe REORG once a week. Now, depending on when your LOAD occurs, I would consider running it after the LOAD. LOAD bypasses a lot of things. You want to know the number of rows and the distribution of the data in the table(s) in order to have efficiently running queries. REORG is to clean up lost space and improve I/O. Perhaps less important after LOAD (depending on table fragmentation).

Both RUNSTATS and REORG acquire locks of sorts, so again, that is where you want to test in lower environments. If you can, run them after hours for zero impact. If you need to run them during the day, play with the options to see what you can do. The systems I happened to work on allowed for full RUNSTATS and full offline REORGs during the day. But we didn't have the volume of transactions others have.

Hopefully I've given you some thoughts to consider and a place to start with. The (final) answer differs for everyone.


char or smallint for a column with numeric codes?

The standard clearly says "four-digit code", which in my view means it should always contain 4 numeric characters, which in turn means the appropriate data type is char(4).
If you want to ensure the code value adheres to ISO 18245, store it as char(4) in the database along with a check constraint.


(Partially) clone a huge DB for DEV purposes

Answers originally left in comments

A test environment should probably have a full copy and could be populated form the backups (and it double checks they are working right). – danblack

  My ideas was to use mysqldump to set up only the structure (no data), then use a long list of insert into … select ... statements to fill the tables 1 by 1.

Use SELECT .. INTO OUTFILE and LOAD DATA INFILE instead of building SELECTs. Plus processing the tables in the right order of course. – Akina


The connection pool will automatically kill connections which haven't been used in a while.  There's no way to identify these from the SQL Server side with 100% accuracy without running the risk of the client application throwing error messages when it attempts to use the connection from the pool which it things should be open put is really closed.

What problem are you trying to solve for?


Please tell me my data isn't gone... lie to me even

I'm not sure I understand your question, or even what you did, but let's walk this through.

If all you did was to issue a cp -R /var/lib/mysql /path/to/backup, then this does not harm your existing data.

It is also an incorrect backup method. Your backup itself is probably useless (unless all tables are MyISAM and you issued a FLUSH TABLES WITH READ LOCK).

What's further unclear is your description of .opt and .frm files. Is that all you got? That's strange, because you should have also got some *.MYD files, and some *.ibd files or an ibdata1 file.

Which storage engines are you using for your tables? What is the output of

mysql> select @@datadir;
mysql> show global variables like 'innodb_data_file_path' ;
mysql> show global variables like 'innodb_data_home_dir' ;

?

Last, I don't understand from your question whether data has been deleted from your webserver (containing your MySQL data).


How to specify the exact .ndf file on a filegroup to save a table in?

No, you can't specify the exact data file you want the data to go into.

What you can, though, is specify the filegroup at the table creation. If you want to fill in a big table on a specific disk drive, then you have to create a different filegroup, and create the table on that filegroup.


Enforcing Password Policy on an Instance

You can script it like this for every login, which is already there.

USE [master]
GO

SELECT 'ALTER LOGIN ['+name+'] WITH CHECK_POLICY=ON;'
,* FROM sys.sql_logins
WHERE is_policy_checked = 0
GO

Copy from the results what you want, and execute it in another query window.


Database logs growing during maintenance plan

What I am trying to understand is why this is occurring, and how to prevent it.

Maintenance Plans do not have much logic that you can implement when dealing with index fragmentation and once you configure it, they run regardless if you really need a reorg or rebuild on your indexes.

You are better off implementing Ola's backup solution as well as Index maintenance  solution .

Also, refer to Why Does the Transaction Log Keep Growing or Run Out of Space? - especially answers from @MikeWalsh and @AaronBertrand


Apply the select privilege to all users in mysql

Try this:

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| eroom            | localhost |
| musr             | localhost |
| musr             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> select concat('GRANT SELECT ON *.* to "',user,'"@"',host,'";') into outfile '/tmp/select_script.sql' FROM mysql.user;
Query OK, 4 rows affected (0.00 sec)

mysql> source /tmp/select_script.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


Stored Procedure Create Table from Variable with Variable Constraint

Your logic seems fine, you are getting a syntax error because you aren't delimiting your strings correctly.

[PeriodClosedUID] [varchar](3) NULL,)' <-- problem

Also it's very helpful to be liberal with carriage returns and indenting, so problems like this aren't hidden 4 screens over and only accessible via horizontal scrollbar.

CREATE PROCEDURE dbo.procFinancialPeriodTable -- always use schema prefix
    @TABLENAME SYSNAME
AS
BEGIN -- use body wrappers around whole body
  SET NOCOUNT ON; -- put this at the beginning - no point in setting it at the end

  DECLARE @SQL NVARCHAR(MAX); -- dynamic SQL should always be Unicode

  SELECT @SQL = 'CREATE TABLE dbo.' + QUOTENAME(@TABLENAME) + '('
    + '[ID] [int] IDENTITY(1,1) NOT NULL,[FinPeriodNr] [int] NOT NULL,
       [FinCurrentPeriod] [bit] NULL,[FinStart] [date] NULL,
       [FinEnd] [date] NULL,[FinStatusOpen] [bit] NOT NULL,
       [PeriodClosedTS] [smalldatetime] NULL,[PeriodClosedUID] [varchar](3) NULL,
       CONSTRAINT [PK_FinancialPeriod_' + @TABLENAME + '_1] PRIMARY KEY CLUSTERED(
         [FinPeriodNr]));'; -- all those options you specified were verbose defaults

  EXEC sp_executesql @sql; -- instead of EXEC(@sql)
END
GO

Some links:

My Stored Procedure "Best Practices" checklist
Bad habits to kick : Avoiding the schema prefix
Bad habits to kick : Using EXEC() instead of sp_executesql


Cannot change the Delete Rule of a self referencing foreign-key-relationship to cascade delete

To further clarify this question, I've created a sample schema as such:

USE TempDB;

CREATE TABLE [dbo].[myObj]
(
    [ID] [int] NOT NULL CONSTRAINT PK_myObj PRIMARY KEY IDENTITY(1,1),
    [fk_ParentID] [int] NULL CONSTRAINT FK_myObj_ID FOREIGN KEY
                    REFERENCES myObj (ID) ON DELETE CASCADE
);

When you attempt to execute this SQL, the following error is returned by SQL Server:

  Msg 1785, Level 16, State 0, Line 3
  Introducing FOREIGN KEY constraint 'FK_myObj_ID' on table 'myObj' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

  Msg 1750, Level 16, State 0, Line 3
  Could not create constraint. See previous errors.

The error message indicates that SQL Server is declining to create the foreign key cascade relationship since deleting a single row could result in all the rows being deleted (or perhaps just multiple rows, not all, depending on data in the table!).

To illustrate this, consider the following data:

INSERT INTO myObj (fk_ParentID) VALUES (NULL);
INSERT INTO myObj (fk_ParentID) VALUES (1);
INSERT INTO myObj (fk_ParentID) VALUES (2);
INSERT INTO myObj (fk_ParentID) VALUES (3);
INSERT INTO myObj (fk_ParentID) VALUES (4);
INSERT INTO myObj (fk_ParentID) VALUES (5);
INSERT INTO myObj (fk_ParentID) VALUES (6);
INSERT INTO myObj (fk_ParentID) VALUES (7);

Which looks like:

If I could define the ON DELETE CASCADE action on this table, executing the following code would delete all rows in the table:

DELETE FROM myObj WHERE ID = 1;

Deleting the row with ID=1 would cascade through the row with fk_ParentID=1 which would cascade delete through fk_ParentID 2, etc, etc.  Most likely this is not the effect you want.


The only way other users have the ability to change the password of the SA account are other members of the sysdamin role and members of the security admin role. Are your users part of the sysadmin or security admin role?

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-2017


Which method is better to drop all user connections before a restore Change to single user mode or Take offline the database?

I would set it offline. I say this because SINGLE_USER mode is generally used to allow an administrator to perform some kind on maintenance on a database without users attempting to access and possibly interfering with the maintenance.

It doesn't really matter either way for a restore as your users will experience the same thing which is they wont be able to connect. One possible annoyance with the SINGLE_USER approach would be if a user stole the single session before you and blocked you from performing your restore.


The short answer: This is not possible.

The long answer: Problems like this usually arise from a non-optimal database design. In your case you have two types of tests. They are different, but both represent tests. Therefore you should have one table dbo.tests that contains the common attributes (columns) of all tests.

Each test type has specific attributes. that are not shared with other test types. So for each test type you should have an additional table that contains the primary key of dbo.tests with a declared FK relationship, followed by the test type specific attributes.

With that structure you can easily declare a FK relationship between tests and other tables. If the relationship needs to include all test types, the FK points to the dbo.test table. If the relationship needs to include only a specific test type, the FK points to the specific table for that test type.

To make the transition to the new table layout easier, you could create a view for each test type that contains the common columns and the specific columns. Those views then look similar to the tables you currently have. On those views you can create triggers to handle insert, updates and deletes directing the data between the common and the specific tables for each type. That way you could use the view the same way you are using your current tables.


What are the performance implications of polymodels versus normal Bigtable models in Google App Engine?

There probably is a performance difference, but it's probably pretty small.

Here's what I found (all based on the the Google Docs for Python).

BigTable does not support PolyModel information natively.  Instead, it's implemented using a 'class' property. So, when you try to perform a search, for example, to find a base class, you will be searching on this 'class' property.
All queries that use the PolyModel class have an extra filter applied that filters by the given class (using the 'class' property).
Any indexes created for a PolyModel class, must take into account the extra 'class' column.

Essentially, the PolyModel class takes care of the 'class' property on its own, inserting it into queries and utilizing for other queries.  Other than that, it works identical to standard BigTable usage.

So, the difference is really just adding and maintaining one extra column.

Is there a performance difference?  Yes, probably so.  Every column you add to any database system is going to have a performance impact.  But, is it significant?  Probably not.


Setting DataGuardSyncLatency for Oracle Data Guard

On the primary and standby server I set:

ALTER SYSTEM SET DATA_GUARD_SYNC_LATENCY=0 scope=both sid='*';

Not sure if it should be 0.


What you are describing is a data warehouse.  The live, normalized, read-write system is OLTP (online transaction processing) and the denormalized read-only snapshot is a data warehouse.  The structure of the data warehouse could be a Star Schema, especially if it's highly denormalized.  Data warehouses often have summarization in addition to denormalization.  There can be many copies of the same data summarized over various dimensions and/or timeframes.

The disadvantages of this technique are that the snapshot is generally not 100% up to date and you have to be very careful about how your snapshot is taken or you could actually introduce discrepancies other than timeliness into your data warehouse.  Another possible issue is that you may have difficulty doing some kinds of reporting out of a data warehouse because of the choices you made when rolling up details into you summary tables.  Also, if your data warehouse has multiple summaries over different timeframes, for example, you have to be careful to keep these consistent with one-another.

The timeliness issue in particular is one you have to be careful about.  I've seen users make a change to their online system and then get angry that it didn't show up right away in a report that is run against the data warehouse.  Users tend not to know or care about the vagaries of reporting systems.


What are all the ways an open transaction will rollback?

To add to what @RLF provided in his answer; the following will also result in transaction rollback.

ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE; will rollback any open transactions.
Disconnection by the client, or server, or any bit of infrastructure in between.
If the underlying disk subsystem goes away, all open connections to the database will be terminated. Uncommitted Transactions will be rolled-back when the database is subsequently opened.
If the server is shutdown, or quits for any reason, uncommitted transactions will be rolled back when it starts up and opens the associated database.
If SET XACT_ABORT ON is set, any T-SQL statement that raises a run-time error will cause an open transaction to roll back.
Batch-scoped transactions, applicable to multiple active result sets (MARS), that have not been committed will roll back when a batch completes.
For completeness, ROLLBACK TRANSACTION will, rather obviously, roll back a transaction.
If the transaction log becomes full while a transaction is open, and the database is subsequently closed for any reason, roll-back will occur when the database is brought back online.
If SET IMPLICIT_TRANSACTIONS ON; is set, including by ANSI default, uncommitted transactions will be automatically rolled back when the client disconnects.


How to perform a while loop on the query result

Oke, I found my own answer after reading the internet en doing some trail and error.

I created a stored procedure which I will execute on the update trigger.

The trigger

CREATE TRIGGER `order_gereed` AFTER UPDATE ON `oc_order`
 FOR EACH ROW IF new.order_status_id = "15"
THEN
CALL VooraadUpdate(new.order_id, new.store_url);
UPDATE push_voorraad SET actie = '1';
END IF

The procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `VooraadUpdate`(IN `orderID` INT(11), IN `storeURL` VARCHAR(255))
    NO SQL
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE a, b, c INT;
    DECLARE cur1 CURSOR FOR SELECT
                              oc_order_product.quantity,
                              oc_order_option.option_value_id,
                              product.id
                            FROM oc_order_product
                              JOIN oc_order_option
                                ON oc_order_option.order_id = oc_order_product.order_id
                              JOIN product
                                ON product.oc_product_id = oc_order_product.model
                            WHERE oc_order_product.order_id = orderID AND oc_order_product.store_url = storeURL;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur1;
    read_loop: LOOP
      FETCH cur1 INTO a, b, c;
        IF done THEN
          LEAVE read_loop;
        END IF;

      UPDATE product_option_value SET quantity = quantity - a WHERE product_id=c AND option_value_id=b;

    END LOOP;

    CLOSE cur1;
END


Could someone explain bizarre behavior executing millions of UPDATES?

Short version
Your algorithm looks O(n*m) on first glance, but effectively grows O(n * m^2), because all rows have the same ID. Instead of 5M rows, you are getting >1.25G rows
Long version
Your function is inside an implicit transaction. That's why you see no data after cancelling your query, and also why it needs to maintain distinct versions of the updated/inserted tuples for both loops.
Additionally, I suspect you have a bug in your logic or underestimating the number of updates made.
First iteration of the outer loop - current_id starts at 1, inserts 1 row, then the inner loop performs an update 10000 times for the same row, finalizing with the only row showing an ID of 10001, and current_id with a value of 10001. 10001 versions of the row are still kept, as the transaction is not finished.
Second iteration of the outer loop - as current_id is 10001, a new row is inserted with ID 10001. Now you have 2 rows with the same "ID", and 10003 versions in total of both rows (10002 of the first one, 1 of the second one). Then the inner loop updates 10000 times BOTH rows, creating 20000 new versions, getting to 30003 tuples so far...
Third iteration of the outer loop: current ID is 20001, a new row is inserted with ID 20001. You have 3 rows, all with same "ID" 20001, 30006 row/tuples versions so far. Then you perform 10000 updates of 3 rows, creating 30000 new versions, now 60006...
...
(If your space had allowed) - 500th iteration of the outer loop, creates 5M updates of 500 rows, just in this iteration
As you see, instead of your expected 5M updates, you got 1000 + 2000 + 3000 + ... + 4990000 + 5000000 updates (plus change), which would be 10000 * (1+2+3+...+499+500), over 1.25G updates. And of course a row is not just the size of your int, it needs some additional structure, so your table and index gets over ten gigabytes size.
Related Q & A:

set isolation level for postgresql stored procedures
Making sense of Postgres row sizes


sql server active active configuration with same database names

You have two different clusters sharing the nodes, both clusters in having one active and one passive node. The names and resources on each cluster are completely independent from the names and resources on the other cluster. So you can safely add Dnb_E and each cluster. On failover the node running now the active node of both clusters will start OK, since the Db_E on the cluster A is different from Db_E on cluster B. Notice that I intentionally avoided using the name 'instance'.

Using the misleading 'active-active' nomenclature just leads down the thinking that you somehow share resources between the two clusters. Calling this configuration 'active-active' is strongly discouraged.


Assuming that you alias the inline view to Q, that syntax should be perfectly valid in Oracle.  I'm assuming that you actually have the alias in the SQL Server version as well.

SQL> ed
Wrote file afiedt.buf

  1  select q.a, sum(q.b)
  2    from (select deptno a, sal b from emp
  3          union all
  4          select 40, 10000 from dual
  5         ) q
  6*  group by q.a
SQL>
SQL> /

         A   SUM(Q.B)
---------- ----------
        30       9406
        20      10880
        40      10000
        10       8753


Can a table have Both a Clustered Columnstore Index and Nonclustered Columnstore Index?

No, you can't:

CREATE TABLE dbo.cs_test (id INT NOT NULL)

CREATE CLUSTERED COLUMNSTORE INDEX cs_whatever ON dbo.cs_test

CREATE NONCLUSTERED COLUMNSTORE INDEX nc_whatever ON dbo.cs_test(id);

Returns the error message:

  Msg 35339, Level 16, State 1, Line 7 Multiple columnstore indexes are
  not supported.

The same result for multiple nonclustered columnstore indexes, as well.


Non clustered index not used for nullable column

This is because your nonclustered index covers predicates for "EntityId" and "AggregateEntityId" or only "EntityId" based on the order that the columns are listed on the index.
This is because the B-Tree that stores the nonclustered index data first sorts on EntityId then by AggregateEntityId.
Since your second query doesn't use EntityId in the WHERE clause, the nonclustered index is invalid in this case because it's unable to efficiently locate AggregateEntityId without being able to first filter on EntityId.
You would need another nonclustered index that starts with AggregateEntityId (or only contains AggregateEntityId) under the Indexed Columns.


Sybase query: How can I sort the extraction (by well formed columns)

Try adding-w parameter, this would dictate size of window. You may use -w99999 and output can be copied to a text editor. You could see that in the correct or desired format.

This seems to be working and hence adding it as an answer.


You could try the following to achieve the result. Your date format string is invalid and the column alias should be placed after the END clause of CASE expression.

SQL> desc info
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 START_DATE                                         DATE
 END_DATE                                           DATE
 AMOUNT                                             NUMBER

SQL> select * from info;

START_DAT END_DATE      AMOUNT
--------- --------- ----------
30-JUN-16 14-SEP-16       1200
14-SEP-16 30-JUN-16       1300
30-MAY-16 30-JUN-16       1300
14-SEP-16 20-JUL-16       1400

SQL> select start_date,
case
when end_date > to_date('2016-06-30', 'yyyy-mm-dd') then to_date('06-30-2016', 'MM/DD/YYYY') end as end_date, amount from info
order by end_date asc;  2    3    4

START_DAT END_DATE      AMOUNT
--------- --------- ----------
30-JUN-16 30-JUN-16       1200
14-SEP-16 30-JUN-16       1400
30-MAY-16                 1300
14-SEP-16                 1300

OR

SQL> select start_date,
case
when end_date > to_date('2016-06-30', 'yyyy-mm-dd') then to_date('06/30/2016', 'MM/DD/YYYY') else end_date end as end_date, amount from info
order by end_date asc;  2    3    4

START_DAT END_DATE      AMOUNT
--------- --------- ----------
30-JUN-16 30-JUN-16       1200
14-SEP-16 30-JUN-16       1400
30-MAY-16 30-JUN-16       1300
14-SEP-16 30-JUN-16       1300

SQL>


Filling Empty Values when Calculating Standard Deviation

I was able to resolve the issue through the use of a CROSS JOIN.

  The SQL CROSS JOIN produces a result set which is the number of rows
  in the first table multiplied by the number of rows in the second
  table if no WHERE clause is used along with CROSS JOIN.This kind of
  result is called as Cartesian Product.

I used this in a sub-query to generate a list of dates with every Part ID. This was joined to my other table, allowing me to substitute a value of 0 where null. The standard dev. then calculates correctly.


In Oracle 12c trying to create table with columns greater than 4000

Those are not the correct steps to enable the extended data type. You have set max_string_size to EXTENDED in the spfile (scope=spfile) so it had no effect while running the scripts.

Try again.

Non-CDB:

Enabling the New Extended Data Type Capability

CONNNECT SYS / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER SYSTEM SET max_string_size=extended;
START $ORACLE_HOME/rdbms/admin/utl32k.sql
SHUTDOWN IMMEDIATE;
STARTUP;

Or:

MAX_STRING_SIZE


Lost DMK encryption password for master database

My company has TDE encryption enabled for some of our databases. The
  password that the master key was encrypted by for one of our servers
  is lost. We do have the backup .key file, however, the password it was
  encrypted with is lost too.

  We also have the certificate backed up, but those passwords are lost
  as well.

So you know the backup cert/DMK files are now useless. I would IMMEDIATELY take new backups and save the passwords. This way you can restore older backup files should anything happen. I would also backup the SMK as that's the only means you have to decrypting the DMK/Cert at this moment.

  The original backup files (master key, cert) are still in existence
  and those encryption passwords are available for previous servers
  these DBs lived on.

I'm assuming the keys haven't been rotated per your comment. If this is the case you are most likely able to restore these to a test server and restore your TDE enabled databases to said test server without issue. If this is the case, you could re-use these keys assuming nothing else is relying on the DMK/Cert in your current instance.

Since TDE works through the SMK (and not by passwords) you're currently "at risk". Sure, it's running but who knows when or if something will happen. I stated before, take new backups.

The most straight forward approach would be to remove TDE from the databases on that instance, remove the cert, and remove the DMK (After backing them up first). Then proceed to create the DMK/Cert/TDE enabled. It's going to be disk and cpu intensive for a bit (depending on the size of database and hardware/disk/etc). Backup the new DMK/Cert with a guarded password.


Able to connect with connection manager but SSIS package does not authenticate

We where able to solve the Connection Problems with the Package Parameter Tab. Here you are able to "Save" for each connection the Username and Password (In clear Text ^^?!). And then the Package can connect.


Most efficient way to insert rows into a temp table in a stored procedure

Since this seems to be a data warehouse, is / can the recovery model be bulk logged (or simple)? That in addition with using TABLOCK hint on table_b could help a lot since the inserts could be minimally logged. I found a blog post by Itzik Ben-Gan on minimally logged inserts that could help.


PostgreSQL - query first on indexed column?

If you look carefully at your query plan, you will find that the Seq Scan expects to find just one row (rows=1 width=840) in the listings table.

This is what happens if you have a situation such as this:

 CREATE TABLE users
 (
   id text PRIMARY KEY,
   internal_id serial,
   something_else char(800)
 ) ;

 CREATE TABLE listings
 (
   id bigserial PRIMARY KEY,
   created_at timestamptz default current_timestamp,
   posted_by text REFERENCES users ON UPDATE CASCADE ON DELETE CASCADE
 ) ;

We add a few users, but just ONE listing:

 INSERT INTO users (id) VALUES('1009280753550922') ;
 INSERT INTO users (id) SELECT generate_series(1, 1000)::text ;

 INSERT INTO listings (posted_by) VALUES('1009280753550922') ;

Have the tables analyzed, so that the query planner has got good statistics:

 ANALYZE users;
 ANALYZE listings;

And make it explain the SELECT:

 EXPLAIN
 SELECT
     *, users.internal_id
 FROM
     listings
     JOIN users ON listings.posted_by = users.id
 WHERE
     listings.id = 2
     and posted_by = '1009280753550922';

 | QUERY PLAN                                                                      |
 | :------------------------------------------------------------------------------ |
 | Nested Loop  (cost=0.28..9.32 rows=1 width=3248)                                |
 |   ->  Seq Scan on listings  (cost=0.00..1.01 rows=1 width=33)                   |
 |         Filter: ((posted_by = '1009280753550922'::text) AND (id = 2))           |
 |   ->  Index Scan using users_pkey on users  (cost=0.28..8.29 rows=1 width=3211) |
 |         Index Cond: (id = '1009280753550922'::text)                             |

dbfiddle here

The reason why PostgreSQL decides to perform a Sequential Scan of the listings table is because there is just one row. In that case, reading the whole table takes less time than using an index.

If you just add more data to the listings table, PostgreSQL changes plans, because then, reading the whole table is no longer the cheapest alternative:

 INSERT INTO listings (posted_by) SELECT generate_series(1, 1000)::text ;
 INSERT INTO listings (posted_by) SELECT generate_series(1, 1000)::text ;
 INSERT INTO listings (posted_by) SELECT generate_series(1, 1000)::text ;

 EXPLAIN
 SELECT
     *, users.internal_id
 FROM
     listings
     JOIN users ON listings.posted_by = users.id
 WHERE
     listings.id = 2
     and posted_by = '1009280753550922';

 | QUERY PLAN                                                                          |
 | :---------------------------------------------------------------------------------- |
 | Nested Loop  (cost=0.56..16.60 rows=1 width=3234)                                   |
 |   ->  Index Scan using listings_pkey on listings  (cost=0.28..8.30 rows=1 width=19) |
 |         Index Cond: (id = 2)                                                        |
 |         Filter: (posted_by = '1009280753550922'::text)                              |
 |   ->  Index Scan using users_pkey on users  (cost=0.28..8.29 rows=1 width=3211)     |
 |         Index Cond: (id = '1009280753550922'::text)                                 |

dbfiddle here

PostgreSQL (and most other databases) has a Query Planner that chooses an execution plan based on a cost-estimate of all available alternatives. The cost-estimate is based on the amount and statistical distribution of data within the different columns of the different tables, and takes into consideration the availability of indexes, but will choose to use them only if it is the cheapest alternative (or you force it to use them via settings).

With a query as simple as the one in this example, just joining two tables using an equality JOIN condition, you shouldn't need to outsmart PostgreSQL query planner. Let the planner plan. It will nearly always choose the best plan.

You may need to play some tricks when JOINs are much more complicated: many more tables, conditions which aren't necessarily just equalities, function calls, correlations between tables that the planner doesn't take into account, etc. ... and the planner ends up making bad cost-estimates, and the estimated cheapest plan is not really the cheapest.


Method for Finding Top Record with WHERE conditions in Child Tables

I wouldn't consider this a hack.  Windowing functions, including ROW_NUMBER are fairly efficient and generally perform equal to or better than alternatives.  On new instances of SQL Server, I assume that FIRST_VALUE would be more performant, but have not specifically confirmed this.

Note: I've seen LAST_VALUE produce incorrect results, and would recommend staying clear of it, opting instead to use FIRST_VALUE with the opposite sort.

A word of caution, however.  If your ordering criteria are not specific enough to produce unique a single "best" record (e.g. suppose you omitted OrderDtlID, any of the detail records for that order could be first), then which item is chosen is functionally random (or, more accurately, based upon hidden variables that you can neither easily discover nor control in many cases).  If you change from ROW_NUMBER to RANK and your candinality changes (you start getting a different number of results) this is a good indicator of a problem.  Also worth pointing out this can be a problem with other means of picking the latest/best record, not just ROW_NUMBER.

Even if you are indifferent to which would be selected, the results of such a query may not be repeatable, though you may no see it right away (the hidden variables don't general change after each query).  It can cause problems troubleshooting issues later and, if you run any sort of parallel environment (dev/qa), make accurate comparison between the two impossible.


Postgres: using existing index to support GROUP BY query on custom function

I'm not yet sure if this is doable in pure SQL (probably - yes), but here is the brute force solution using cursors. This is just a (working) draft, with some twiddling and dynamic SQL you could add more parameterers, like function name.

create or replace function top_f(_limit int)
returns table (f text, sum int)
language plpgsql
AS $$
declare
    _a text;
    _fa text;
    _asum int  := 0;
    _fasum int :=0;
    _cnt int   := 0;
    _cur cursor for SELECT a, sum(b) FROM t GROUP BY a ORDER BY a;
begin
    open _cur;
    LOOP
        fetch _cur into _a, _asum;
        if upper(_a) <> _fa then
            return query select _fa, _fasum;
            _fasum := 0;
            _cnt   := _cnt + 1;
        end if;
        exit when _cnt >= 10;
        _fasum := _fasum + _asum;
        _fa := upper(_a);
    END LOOP;
end;
$$;

And here is how it works. Please note run times which are the point here.

(filip@[local]:5432) filip=# SELECT a, sum(b) FROM t group by a order by a limit 12;
     a     | sum
-----------+-----
 a         |  11
 A         |  11
 Aachen    |  11
 Aachens   |  11
 Aaliyah   |  11
 Aaliyahs  |  11
 aardvark  |  11
 aardvarks |  22
 Aaron     |  11
 Aarons    |  11
 aback     |  11
 abacus    |  11
(12 rows)

Time: 0.838 ms

(filip@[local]:5432) filip=# SELECT * from top_f(10);
     f     | sum
-----------+-----
 A         |  22
 AACHEN    |  11
 AACHENS   |  11
 AALIYAH   |  11
 AALIYAHS  |  11
 AARDVARK  |  11
 AARDVARKS |  22
 AARON     |  11
 AARONS    |  11
 ABACK     |  11
(10 rows)

Time: 1.524 ms

(filip@[local]:5432) filip=# SELECT upper(a), sum(b) FROM t GROUP BY upper(a) ORDER BY upper(a) LIMIT 10;
   upper   | sum
-----------+-----
 A         |  22
 AACHEN    |  11
 AACHENS   |  11
 AALIYAH   |  11
 AALIYAHS  |  11
 AARDVARK  |  11
 AARDVARKS |  22
 AARON     |  11
 AARONS    |  11
 ABACK     |  11
(10 rows)

Time: 2061.320 ms (00:02.061)

Update: here is the same with parametrized function name:

CREATE OR REPLACE FUNCTION top_f(_function name, _limit int)
RETURNS TABLE (f text, sum int)
LANGUAGE plpgsql
AS $$
DECLARE
    _a text;
    _fa text;
    _newfa text;
    _asum int  := 0;
    _fasum int :=0;
    _cnt int   := 0;
    _cur cursor for SELECT a, sum(b) FROM t GROUP BY a ORDER BY a;
BEGIN
    OPEN _cur;
    LOOP
        FETCH _cur INTO _a, _asum;
        EXECUTE 'SELECT ' || quote_ident(_function) || '($1)' INTO _newfa USING _a;
        IF _newfa < _fa then
            raise exception 'Function is not monotonic: f(%) = % < %', _a, _newfa, _fa;
        ELSIF _newfa > _fa then
            return query select _fa, _fasum;
            _fasum := 0;
            _cnt   := _cnt + 1;
            exit when _cnt >= _limit;
        END IF;
        _fasum := _fasum + _asum;
        _fa := _newfa;
    END LOOP;
END;
$$;


Two physical servers with sql server on same network

Solved that by changing SQL port on SQL Server Configuration Manager, but you'll always have to specify the port for every connections that is different from the standard 1433 port. If you have an APP, you'll also have to change the connection string method to set up a different port or work the code to get it from an external file.


dbstart will only start databases that have the third parameter in the oratab file set to Y. Check the value in that file.

You also don't need to pass the ORACLE_HOME as an argument; not sure if you actually meant to pass the SID, but anyway, the home is taken from the oratab too.

The script itself says that it should only be executed as part of the system boot procedure, e.g. from a script run during boot from an /etc/rc*.d directory. The $ORACLE_HOME parameter that's passed in is only used to start the listener; the home for each database is taken from oratab.

According to the documentation, dbstart is deprecated in favour of Oracle Restart (in non-RAC environments) in 11g Release 2; I guess that means you should use crsctl start has to start the listener and database from then. That also uses the third oratab value to decide whether to start the database.


Selecting rows with condition on two columns together

then u can use this one

SELECT * FROM tablex WHERE Number<>123 OR VAT<>15


MySql-Workbench: Can't connect to MySQL server on '127.0.0.1' (111)

The error message says clearly the MySQL server could not be reached, hence check everything that could block access to it. This is not user related.

Do you have a running network adaptor on the machine you run Workbench on? Does it allow TCP/IP connections?
Can you reach the target machine your MySQL runs on?
Does MySQL run on that target machine?
Can you create an SSH tunnel to the target machine?
Can you reach the MySQL server when you are logged in on the target machine (e.g. by means of an SSH)?

If you can answer all that with yes there should be no problem connecting to the MySQL server from MySQL Workbench. Point 6 can be a bit more complicated, if your MySQL server is not on the same machine as where the SSH endpoint is or if it has networking disabled or if it uses a different port than what you tried.

However, the general idea should be clear now. Try to isolate the actual problem (is it network related or a config problem?). Maybe this video can also help you: https://www.youtube.com/watch?v=DCgRF4KOYIY&t=76s


Editing the data in that table is not the correct way to make this change, as it is a system table. In fact, I don't think it is even possible, although some system tables outside of master can be updated.

If you want to do this via script, instead of manually editing your backup jobs, try this:

SELECT 'EXEC msdb..sp_update_jobstep @job_name = ''' + j.[name] + ''', @command = ''' + REPLACE(js.[command], '\\OldBackup\Share', '\\NewBackup\Share') + ''';'
FROM msdb..sysjobsteps js
INNER JOIN msdb..sysjobs j ON j.job_id = js.job_id
WHERE j.[name] = 'Backup Job Name'

This will dynamically generate a sp_update_jobstep command and replace the old backup share info with the new backup share info. Copy the generated results into a new query window, validate the job that's being updated is correct and the command looks correct, then you can execute.

Obviously, you should test this first and don't just execute it a production system.

More info:
sp_update_jobstep


Update files or a table every 5 seconds or for every update to a table whichever is the less frequent

This worked for me.

create event event_name on schedule every 5 second starts now()
do
begin
if (select update_time+5>=now() from information_schema.TABLES
where table_name='Table_name')
then call procedure_name();
end if;
end//

Here the event checks every 5 seconds if any update happened in the last 5 seconds on the table. If yes it simply calls the procedure update_file or something.