Different manifestations of the same logic yield very different execution times

Since you are not allowed to share any more information on the queries such as sample data + table definition, these tests will be on own fabricated data and with or without indexes. Test data below.

YMMV.

The inner join + group by query

The 'bad' query puts everything in an index spool, and subsequentially executes a nested loop join on the exponential 8M rows

Whole plan

Index Spool properties.

This index spool creates over 8M records as a result of the <= join.
The nested loop operator over 8M rows into a stream aggregate will also decrease performance.

The derived table

The 'better' plan

The stream aggregate is used before the nested loop operator is used. Resulting in a much smaller join.
There is also a difference in stream agg operators, this one supports the MAX() function while the other one (above) supports the GROUP BY statement + The partialagg of MAX().

TL;DR

My guess is that the huge nested loops operator used in the plan of the inner join query factors in for the excessive time.
The difference with the good plan being that the Stream aggregate happens before the nested loops operator.
Also as @ypercubeᵀᴹ mentioned, they are not logically similar.

Adding indexes

CREATE INDEX IX_widgets_population_serial_ID on widgets_population(serial_ID);
CREATE INDEX IX_widgets_tested_serial_ID on widgets_tested(serial_ID);

The 'good' query gets improves a lot after an index, using 15ms cpu time

The reason that this change is so drastic, is that the TOP operator is used now.

This top operator is getting the first value that matches the <= condition by using the index's sort order, for each value in widgets_tested.

The 'bad' query, not so much

Executing the join before executing GROUP BY & MAX(), where the MAX() is calculated before the join in the good plan.

Data used for this example

CREATE TABLE  widgets_population(serial_ID int)

CREATE TABLE  widgets_tested(serial_ID int)

SET NOCOUNT ON
DECLARE @I INT = 1;
WHILE @I  <= 1000
BEGIN
INSERT INTO  widgets_population(serial_ID)
VALUES(@I)
INSERT INTO  widgets_tested(serial_ID)
VALUES(@I)
SET @I+=1
END

INSERT INTO  widgets_population(serial_ID)
select serial_ID+1000 from widgets_population

INSERT INTO  widgets_tested(serial_ID)
select serial_ID+1000 from widgets_tested

INSERT INTO  widgets_population(serial_ID)
select serial_ID+2000 from widgets_population

INSERT INTO  widgets_tested(serial_ID)
select serial_ID+2000 from widgets_tested


Modelling Relationships in an E-R Diagram, Table or Foreign Key?

Your first option works best when there is the possibility that a lecture can have multiple tutors or when a tutor can teach multiple classes. Your second option works best when you can guarantee that a class will only ever have a single tutor at any one time (otherwise you would need multiple entries in your class table to accommodate extra tutors). The third one will only work if you can guarantee that a tutor will only ever teach one class (the opposite of the second scenario).

If you want/need maximum flexibility then go with option 1. However, you should choose the option that best suits your needs based on the descriptions of the types I have given above.

You can indeed over-normalize a database, and in some cases a de-normalized structure may suit the requirement better - but I don't think this is one of those times.

I hope this helps you.


Assuming that you don't want multiple tnsnames.ora files, the simplest option would be to set the TNS_ADMIN environment variable to point at the one tnsnames.ora file you wanted to maintain on the machine.  All the Oracle Homes on the machine would then use the tnsnames.ora file in the directory specified by the TNS_ADMIN setting before looking for an entry in the local Oracle Home's tnsnames.ora file.


Can the current search_path be used in a query?

pg_settings is a system view containing all the configuration options and their values available in the current context:

  The view pg_settings provides access to run-time parameters of the server. It is essentially an alternative interface to the SHOW and SET commands. It also provides access to some facts about each parameter that are not directly available from SHOW, such as minimum and maximum values.

This view you can query just like any other, so you can get the value of search_path like this:

SELECT setting FROM pg_settings WHERE name = 'search_path';

The returned value is a text, with the delimiter being ', '.  Some tinkering is needed so that you can compare table_schema to this:

SELECT *
  FROM information_schema.tables
 WHERE table_schema = ANY (SELECT unnest(string_to_array(setting, ', '))
                             FROM pg_settings
                            WHERE name = 'search_path');

This is still only half perfect, as "$user" can be in the search_path.  One way to solve this is to replace it with the current user like

... replace(setting, '"$user"', CURRENT_USER) ...

See all this at work on DBFiddle.


Assuming that you want to implement a supertype/subtype relationship between the two tables (an Address is a Contact), you can, with a column having a fixed value of 'A' and use it for the foreign key:

CREATE TABLE Contact
  ( ID INT NOT NULL IDENTITY,
    Type VARCHAR(10) NOT NULL,
    PRIMARY KEY (ID, Type)
  ) ;

and

CREATE TABLE Address
  ( ID INT NOT NULL,
    Type  VARCHAR(10) NOT NULL DEFAULT 'A',
    CHECK (Type = 'A'),
    PRIMARY KEY (ID, Type),
    FOREIGN KEY (ID, Type)
      REFERENCES Contact (ID, Type)
  ) ;

Alternatively we could declare Type in Address as a persisted computed column (the check constraint would not be needed then):
Type AS CAST('A' AS varchar(10)) PERSISTED

CREATE TABLE Address
  ( ID INT NOT NULL,
    Type AS CAST('A' AS varchar(10)) PERSISTED NOT NULL,
    PRIMARY KEY (ID, Type),
    FOREIGN KEY (ID, Type)
      REFERENCES Contact (ID, Type)
  ) ;


How do I limit a SQL stored procedure to be run by one person at a time?

To add to @Tibor-Karaszi's answer, setting a lock timeout doesn't actually produce an error (I've submitted a PR against the docs).  sp_getapplock just returns -1, so you have to check the return value.  So like this:

create or alter procedure there_can_be_only_one
as
begin
begin transaction

  declare @rv int
  exec @rv = sp_getapplock 'only_one','exclusive','Transaction',0
  if @rv < 0
   begin
      throw 50001, 'There is already an instance of this procedure running.', 10
   end

  --do stuff
  waitfor delay '00:00:20'

commit transaction
end


This seems a strange request but it is possible.

GRANT SELECT ON YourTable TO FOO;
GRANT INSERT ON YourTable TO FOO;
GRANT UPDATE ON YourTable TO FOO;
GRANT DELETE ON YourTable TO FOO;
DENY VIEW DEFINITION ON YourTable TO FOO;

They will of course be able to see the names of the columns by issuing a SELECT * and be able to get the datatypes with a bit more effort by select into a new (possibly #temp) table. They will lose intellisense on that table.

You also say

  Since there were already Stored Procedures (SPs) for saving, updating, selecting and deleting records, that programmer will just use this SPs in accessing/manipulating records.

In that case probably they don't need any permissions at all on the table. If the table and stored procedures have the same owner you just need to grant exec permissions on the procedure and the access to the table in the procedure will still succeed through ownership chaining.


MongoDB Primary Member of Replica Set High Memory Usage

Introduction

The oplog has nothing very little to do with memory consumption. It is a capped collection used as sort of a write-ahead log for operations to be replicated to other replica set members.

In general, MongoDB uses up to around 85% (give and take) of memory unless told otherwise. This memory is used to keep the indices and the "working set" (copies of the most recently used documents) in RAM to ensure optimal performance. While it is technically possible to limit the RAM used by MongoDB, it is a Very Bad Idea™ to do so, as you severely limit MongoDB's performance and make it basically impossible to detect when to scale out or up because of insufficient RAM.

TL;DR: If you have to ask how to limit the RAM utilised by MongoDB, you probably should not limit it, as you are unable to judge the side effects this step will introduce.

Limiting MongoDBs memory consumption

You basically have three options: Limit the cache size for the WiredTigers storage engine, use cgroups to limit the memory mongod can request from the OS or use Docker to do so (which makes it a bit easier, but under the hood Docker uses cgroups as well, iirc).

Option 1: Limit WiredTigers cache size

Add the following option to your configuration file (I assume it is in YAML format):

storage:
 wiredTiger:
  engineConfig:
     cacheSizeGB: <number>

where <number> is the maximum amount of RAM MongoDB is allowed to use for WiredTiger's cache. Note that fiddling with this parameter can severly impact performance (on the other hand, limiting MongoDB's memory consumption always will). Please also note that this does not limit the memory used by mongod itself (for example, each connection gets a small stack assigned).

Option2: Using cgroups to limit the overall memory consumption of mongod

As a root user, first ensure that cgroups are enabled:

$ lscgroup
cpuset:/
cpu:/
cpuacct:/
memory:/
devices:/
freezer:/
net_cls:/
blkio:/

Assuming cgroups are available, you can now configure a control group for MongoDB's memory consumption in /etc/cgconfig.conf:

group mongodb{
    memory {
        memory.limit_in_bytes = 512m;
    }
}

After you have done so, you need to restart the cgconfig service. Do not simply copy and paste the config above: With 512 MB, MongoDB will bearly run (if at all). Adjust the memory limit to your needs, with at least 2GB of RAM.

Next, you need to assign mongod to the control group you just created. To do so, you need to edit /etc/cgrules.conf:

*:mongod memory mongodb/

where * denotes that this rule applies regardless who started mongod, the limit will be applied to RAM according to the rules of the control group mongod/. As a last step, you now need to restart the cgred and MongoDB services. The mongod now should use only the specified amount of RAM, for the better or worse.

Option 3: Use Docker to limit mongod's overall memory consumption:

Identify which version of MongoDB you are running currently

$ mongod -version
db version v3.4.10
git version: 078f28920cb24de0dd479b5ea6c66c644f6326e9
OpenSSL version: OpenSSL 1.0.2n  7 Dec 2017
allocator: system
modules: none
build environment:
   distarch: x86_64
   target_arch: x86_64

Your output may be different, but we only need the db version, namely the minor version. In this example, it is "3.4".
Pull a suitable docker image

$ docker pull mongo:3.4

You should pull the docker image for the version you determined earlier and use the pulled image in the next step.
Run the docker image with the appropriate parameters

 $ docker run -d --name mongod --memory=512m \
 > --mount type=bind,src=/path/to/your/datafiles,dst=/data/db \
 > --mount type=bind,src=/file/somewhere/,dst=/key.file,readonly
 > mongod <yourOptions>

A few things to note here: The first mount makes your existing datafiles accessible from inside the container, while the second mount does the same for your keyfile. You need to adjust your mongod options accordingly, namely the keyFile option to point to the destination you mounted your keyfile to.
See the docker documentation and the README of the mongo docker image for details.

Conclusion

You have a sharded cluster and you want to limit the memory consumption of the individual shard members.

In case we are talking of a production system, this is a Very Bad Idea™: Either you have other services running on the machine running your mongods (which would make the two services compete for resources in case of heavy load) or artificially limit the performance MongoDB will provide (by using the methods described above). This is bad systems design. Why did you shard the first place? Sharding is MongoDB's method of load balancing and scaling out (in case a limiting factor, say RAM, can not be scaled up any more because the bang you get for the buck is insufficient). I have a mantra which I repeat to customers (and to be honest occasionally to myself):

  MongoDB instances bearing production data should run on dedicated machines. No exceptions!

Depending on the reasons you sharded the first place and how many shards you have, it may well be that you didn't even need to shard if you ran your cluster on dedicated machines. Do the math.

And even if it was a good idea to have your cluster nodes running other services, they are obviously under provisioned. Given the price of RAM compared with reduced performance, it is basically a no-brainer to scale your machines up with a decent amount of RAM rather than limiting it artificially to enforce a system design which is bad in the first place.

My advice for you is to not follow any of the above approaches. Instead, run your data bearing MongoDB instances on dedicated machines. Scale them up as long as you get an according bang for the buck RAM and IO-wise (CPU is rarely an issue) before you shard. As of the time of this writing that would be between 128 and 256GB RAM and a RAID 0 (in case you have a replica set, which you do have, don't you?) or RAID 10 ( in case your shards are not replica sets - shame on you ;) ) with SSDs. Shard only if

you have too many IOPS for a single machine to handle
you need more RAM than you could fit into your replica set members with a good bang for the buck
you have more data than a single machine can persist.

hth

PS Do not blame it on me or MongoDB if your performance goes south after you limited the RAM for the mongods.


How to give an existing server login access to an attached/migrated database

Finally worked after changing the database owner.  The database was reattached by a different user so the owner was something else.

use [database name];
exec sp_changedbowner @loginame = 'loginX';

After that the user loginX was able to access the database.  It still does not show under Security -> Users but the database is mapped for the loginX login on the server.


Difference Between Sign In and Login (Users and Groups)

I think arguing about login vs sign-in is sematics.

Sql server allows you to log in via 2 modes.

SQL authentication: means using a user id and password which is not the same  as your domain credentials.

Or.

Windows authentication: This can be granted to an individual domain user or a domain group.

Domain groups are a nice way of granting access without having to grant access to individual users each time. For example if a department or team should all have access to a system (e.g HR or Payroll) you can grant access to the team as a whole. As users are added to the team in AD they automatically inherit access to the db via their membership of the group.

This can be overridden by denying individuals access if required. Eg all of HR have access except user xyz.

If SQL is configured to use both modes this referred to as "mixed mode" authentication.

A login gives you access to the SQL  instance. You also need to be granted access to individual dbs. Once you have access to sql the method becomes irrelevant. Once you're in, you're in.

Technically there is third method related to contained databases. But in my experience this is rare.


How to restrict SSMS to list the database of a particular user (Windows 2012 + Plesk)

Using at least SQL Server Management Studio -16.3, you can right click on the Databases node in SSMS and add a filter to restrict displaying databases owned by that user.


SQL perf issue - configure server correctly first or troubleshoot issues one by one?

There are a couple of things to say about this, Dynamics AX performance is not necessarily always caused by SQL Server slowness, especially if you say "everything is slow".

When faced with performance issues in Dynamics AX I tend to use the following approach:

First start with reviewing resources on all servers involved (cpu pressure, memory pressure, page life expectancy,...)

Every AOS, Client & Citrix need at least 4 cores
AX is very sensitive to CPU latency, make sure your CPU's are set to high performance in the BIOS. If you say everything is slow, even simple forms my gut feeling says this isn't the case.
Perform the standard  checks such as disk latency and the likes, not only on the SQL Server but on all components involved.

Then verify if all the SQL Server settings are correct and according to best practices

Correct trace flags
Correct index/stats maintenance
MaxDOP
max memory settings
...

Review all the relevant database properties if they are set according to best practices

auto update statistics

Review the database design by reviewing missing indexes and slow queries

use known DMV queries (I tend to like the Glenn Berry scripts)
log dmv data using DynamicsPerf If you contact support this will be the first tool they'll have you install
log slow queries to the SQL Statement trace log so you can relate them to the call stack in code

review known issues in the application set up (number sequences, database log, ...)

look at the top 10 issues discovered by the Premier Field Engineers

Once you have a setup that works acceptably and some processes remain slow, use the Trace Parser to have exact timings on every piece of code and query that's being run during the process. This should help you either solve or at least explain why the remaining process is slow.


How to effectively filter between several tables in SQL

You can use dynamic SQL to generate a query based on varied requirements. This way, you can exclude joins if you don't need them, like so:

DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'SELECT * FROM myTable AS t ';
IF @param1 IS NOT NULL
    SET @sql = @sql + 'JOIN mySecondTable AS s ON s.Id = t.mySecondTableId ';
IF @param2 IS NOT NULL
    SET @sql = @sql + 'JOIN myThirdTable AS x ON x.Id = t.myThirdTableId';
-- repeat as required to build a valid query

DECLARE @filters NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX) = '@filterParam1 INT, @filterParam2 NVARCHAR(128);'

IF @filterParam1 IS NOT NULL
BEGIN
   SET @filters = '[column] = @filterParam1';
END

IF @filterParam2 IS NOT NULL
BEGIN
  SET @filters = CASE WHEN @filters IS NULL
                      THEN ''
                      ELSE ','
                  END + '[column2] = @filterParam2';
END

DECLARE @where NVARCHAR(MAX);
IF @filters IS NOT NULL
BEGIN
   SET @where = 'WHERE ' + @filters;
   SET @sql = @sql + @where;
END

EXEC sp_ExecuteSql @stmt = @sql
                 , @params = @params
                 , @filterParam1 = @filterParam1
                 , @filterParam2 = @filterParam2;

Assuming that @param1 is null and @filterParam1 is also null, it would generate the following query:

SELECT *
  FROM myTable AS t
  JOIN myThirdTable AS x on x.Id = t.myThirdTableId
 WHERE [column2] = @filterParam2;

Notice how the join to mySecondTable has not been included and the unnecessary filter has not been included either. You can find more examples of how to use sp_executeSql on Microsoft Docs.


PostgreSQL: alternative ways to connect to database

A poor man's solution might be simply installing pgAdminIII, setting up the connection to the database and using it's built in filtering and data view/modification functionality.

I admit this does not give you a very good user experience (about 8 clicks to get to the table you want to view/edit), but it is very easy to set up.

One important thing: you may want to restrict access to the tables that are really editable by your users.  A good approach might be:

CREATE ROLE limited_editor;

GRANT USAGE ON SCHEMA your_schema TO limited_editor;
GRANT SELECT, INSERT, UPDATE ON your_table1, your_table2, ... TO limited_editor;
REVOKE ALL ON ALL TABLES IN SCHEMA your_schema FROM public;

GRANT limited_editor TO alice, bob, ...;

When doing this, revoking access from public might have some unwanted effects on other users' privileges.  Fix them accordingly (for example, by setting up a more powerful editor role, that can be granted to other users).


Migration of oracle database from rhel5 to rhel6

The database[s] and software are on SAN/NAS storage. In that case you can remount them on the new server with the same path's. Because the OS version is different issue a relink for the Oracle binaries. Don't forget to also copy /etc/oratab, /etc/oracle and /usr/local/bin files etc.

Backup and restore are not bad at all but assuming there is some pre production environment with similar setup, you can use that to verify the procedure before the production migration. This is the quickest option (remounting).


You can shred the XML and rebuild it again using XQuery.

declare @X xml = '
<Root attr1="val1" attr2="val2">
    <Elements>
        <Element Code="1" Value="aaa" ExtraData="extra" />
        <Element Code="2" Value="" ExtraData="extra" />
        <Element Code="3" ExtraData="extra" />
    </Elements>
    <ExtraData>
       <!-- Some XML is here -->
    </ExtraData>
</Root>';

select @X.query('
  (: Create element Root :)
  element Root
    {
      (: Add all attributes from Root to Root :)
      /Root/@*,
      (: create element Elements under Root :)
      element Elements
        {
          (: For each Element element in /Root/Elements :)
          for $e in /Root/Elements/Element
          return
            (: Add element Element :)
            element Element
              {
                (: Add all attributes except Value to Element :)
                $e/@*[local-name() != "Value"],

                (: Check if Attribute Value exist :)
                if (data($e/@Value) != "")
                then
                  (: Create a Value element under Element :)
                  element Value
                  {
                    (: Add attribute Value as data to the element Element :)
                    data($e/@Value)
                  }
                else () (: Empty element :)
              }
          },
      (: Add all childelements to Root except the Elements element :)
      /Root/*[local-name() != "Elements"]
    }');

Result:

<Root attr1="val1" attr2="val2">
  <Elements>
    <Element Code="1" ExtraData="extra">
      <Value>aaa</Value>
    </Element>
    <Element Code="2" ExtraData="extra" />
    <Element Code="3" ExtraData="extra" />
  </Elements>
  <ExtraData>
    <!-- Some XML is here -->
  </ExtraData>
</Root>

If Elements is not the first element under Root the query need to be modified to add all elements before Elements first and all elements after Elements after.


Multi Master replication vs Single-Master performance

Every write is performed on every machine, whether it is a Master or a Slave.  The corollary to that is that there is a limit to write scaling with traditional Replication.  Even Galera and Group Replication are just variants on Master(s) and Slave(s).  Each server has all the data, and it is kept as up-to-date as is practical.

Hence, Sharding is the only way to get write scaling.

OK, let me try to answer your question anyway.

With RBR (Row-Based-Replication), the Slave can usually do a write (INSERT/UPDATE/DELETE/etc) with less effort than the Master.

That implies that having multiple Masters will spread the "Master effort" out somewhat.  Hence, having more Masters helps in scaling, and moving more reads off to Slaves help, too.

Some configurations, and my comments:

M <-> M "Dual Master" -- If you are writing to both (as implied above), then it is a somewhat fragile system.

M1 -> M2 -> M3 -> M1 "Circular Masters" -- very fragile.  If one server goes down, it is a nightmare to repair.

3+ Masters, each talking to each other "Galera Cluster" -- This solves the above fragilities, but suffers if there is large latency between Masters.  (latency to get geographic separation to get HA.)  This is about the best config for some write scaling today.

"Group Replication" -- a competitor of Galera when it comes to HA.  I'm not sure about write scaling.

In all of the above, Slave(s) can hang off any or all Masters to provide read scaling.

For write scaling (and not HA), I would go with 3 Galera nodes in the same server room.  If there is enough reads to worry about, then tack some readonly Slaves on.

Note:  Going past 3 nodes in Galera (or maybe it is 5), you risk some degraded performance.  This is due to the fact that each node must talk to each other node during each COMMIT.


How to deal with large offsets in select?

Why not try a where clause? If you index j.data->'key', a where will only be a log(n).

Another reason may be order by j.data->'key' is a slow computation. Indexing this can also boost your order by (but the benefit will be far less noticeable than the benefit due to the where clause itself).

This article may help better answer your question:
https://use-the-index-luke.com/sql/partial-results/fetch-next-page


Sync two mysql database one in intranet and the other on internet

MySQL has replication cabapilities, sure you has revised the MySQL documentation.

This means that MySQL copies data from master to slave, not both directions.

You proposed "just click a button", if you don't want a "do it yourself" solution maybe can try with a database tool like MySQL workbench.

In any case it is dangerous to synchronize two databases with files: data inconsistencies, primary keys collisions, ...


Sql Server - Running a Job on Multiple servers

To answer the specific question of running a SQL Agent job on multiple servers (definitely a good question) and, for the moment, ignoring the particular thing that you want the job to do (which could be another question):

SQL Server / SQL Server Agent actually comes with a facility that makes this type of thing very easy: multiserver administration. You set up a Master (MSX) and several Target (TSX) servers. You then create the job only on the Master and then select which of the Target servers it should run on.

Please see the section of MSDN pages, starting with Automated Administration Across an Enterprise.

It is actually quite nice. I worked at a place with 50 (at least) instances in Product, plus 5 other environments (Dev, QA, etc) with something like 12 - 30 instance per environment. Lots of SQL Agent jobs, mostly managed via MSX / TSX.

You can still create jobs local to any particular instance. For example, when migrating data from a large table to a new structure (a pre-release task for minimizing downtime so that during the release the tables just need to be swapped), about 1 week prior to the release I would create the new tables and local jobs to migrate batches of 1k - 4k rows, every few minutes. Creating them as local instead of MSX allowed for:

Setting different batch sizes and schedules per node: We had 20 nodes and some were either quite a bit larger than the rest or maybe quite a bit smaller. Different batch sizes worked better for different data volumes. Along those same lines, as the jobs working on the nodes with larger tables got to maybe 30% complete or something, they started to slow down, requiring minor changes to batch size and/or schedule frequency. It is not possible with MSX (without a large CASE @@SERVERNAME statement) to tailor job specifics per Target / TSX server.
Auto-clean up: The very nature of these jobs was temporary, and once all of the data on a node had been migrated, there was no more purpose in that partcular job as new rows can be handled via the INSERT, UPDATE, DELETE trigger that kept the already migrated rows in sync. Hence, once the job detected that 0 rows were moved, it looked up its own JobID and called [msdb].dbo.sp_delete_job to delete itself. Doing this meant that we didn't have jobs running that weren't doing any work, and we didn't have to worry about job cleanup during or after the release. I do not think this is possible with MSX / TSX as I do not think a TSX server can delist one of its MSX jobs. Of course, the documentation for sp_delete_job doesn't say that it can't and I haven't tried.

And Linked Servers aren't required for any of the MSX / TSX communication. In fact, you should be careful when restoring a backup of an MSX server to another server for the purposes of testing. I did that one time with our Product MSX server and started removing some of the Target server meta-data as it was cluttering up what I was trying to test. A few hours later the lead DBA sent out an urgent message asking who was messing with the Jobs in Production as quite a few jobs were being mysteriously delisted from some / most / all of their TSX servers. I just happened to be that the server I was using for testing was sitting inside the Production network for obsolete technical reasons and was allowed to communicate with the TSX nodes. Oops ;-). Needless to say, Mr Lead DBA was something quite-the-opposite-of-happy with me (and the junior DBA who did the restore for me).

To be clear about the behavior, when you select a Target server, the job and its related data (steps, schedule(s), etc) are replicated to that TSX server. So the job will live on the Target server and run via SQL Agent on that Target server; the jobs are not running on the Master server.

If you have ever wondered what the [originating_server_id] field was in [msdb].dbo.sysjobs, it refers to the Master/MSX server (I believe a value of 0 means a "local" job).

SELECT * FROM [msdb].dbo.sysjobs -- originating_server_id

Also:

-- map of jobs and what Target/TSX servers they are on
SELECT * FROM [msdb].dbo.sysjobservers

-- registered TSX servers
SELECT * FROM [msdb].dbo.systargetservers

Here is the full list of SQL Server Agent Tables


I would say there is a strong correlation between your out of space event and the missing trace. Note that the sp_configure option merely tells you that the default trace is enabled, but that does not mean that it is running or that it even exists. Note that sys.traces is not a table but a view:

create view sys.traces as select * from OpenRowset(TABLE SYSTRACES)

What does the TABLE SYSTRACES rowset provide? How does it work? How are its results filtered? Your guess is as good as mine. It is possible that the trace is still there, but in a state that prevents it from being exposed by this view. And it may be in a state that still prevents it from being started even after restarting the service.

First, make sure the location of the default trace has sufficient space, the SQL Server service account still has adequate permissions to write to it, you aren't subject to any space quotas, etc. You can get the location from the registry:

HKEY_LOCAL_MACHINE\Software\Microsoft\...YourInstance...\Setup\SQLDataRoot\

Once you are sure that SQL Server should be able to write to this folder, then you can disable and re-enable the default trace:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default trace enabled', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'default trace enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;

You shouldn't need to restart the SQL Server service at this point, but may be a final kick in SQL Server's pants if you still don't see a row in sys.traces. Note that the trace_id you get is not guaranteed to stay at 1.


How to succesfully run a batch file in an SQL Agent job?

It looks like you need to configure the second step of your job to run as a domain account with permissions to write to the \KWS2-WEB-SERVER\Share\Reports\Uur share as well as read access to the C:\Reports\Energie\Uur folder.

You first will need to add a credential to SQL Server. From the Security > Credentials folder you will need to right click and choose New Credential... Fill in that information and click OK.

Once that is complete you will need to create a Proxy that uses this credential. From the SQL Server Agent > Proxies > Operating System (CmdExec) folder you will need to right click and choose New Proxy... Use the credential you created earlier.

Now you can configure your second job step to use the Proxy you just created using Run As: drop down of the job step page.


Create a new computed column on the underlying table that is an RTRIM of the column in question. Add an index on this column. Adjust the view to use this computed column.


Half of the partitions are empty on a KEY partition

You have 100 partitions. So it's likely that your hash values modulus 100 are all even numbers.
This is a general risk of hashing. If the hashing function produces only values with a pattern like this, you can get imbalanced partitions.
One way to fix this is to use a prime number for the number of partitions. For example 101 is a prime number close to your desired number of partitions.
This helps because if the hash values modulus 100 produced a pattern, that pattern will be offset by 1 per window of 100 values. Thus it will be spread more evenly over the partitions.


EMStagePatches_$ORACLE_SID is using up space

Just to make sure this is not used by your OEM
- rename the folder to $ORACLE_HOME/EMStagePatches2_$ORACLE_SID and restart your OEM if nothing bad happens i think is fine ti assume that is just a staging folder for the uploaded patches


Equivalent Linux commands for Windows ones to create user and database

On a 32-bit openSUSE Tumbleweed with PostgreSQL 13 installed. Just run:
> sudo su postgres
> createuser --username=postgres --superuser --pwprompt saleor
> createdb --username=postgres --owner=saleor saleor

If the user already exists, it can be deleted by:
> sudo su postgres
> dropuser --username=postgres saleor

To see a list of users and databases, just run:
> sudo su postgres
> psql
# \du
# \l
# \q


In PostgreSQL, is there a type-safe first() aggregate function?

DISTINCT ON()

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

  SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget ORDER BY

Also, while I didn't bold it then I will now

  Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)
FROM foo
GROUP BY a,b;


First I ran
Use ReportServer
select * from ExecutionLog3 order by TimeStart DESC

This gave me a list of events that I matched with the scheduled times of the jobs:

So I went to the portal and checked the dataset. Turns out caching was enabled:


Installation of Oracle 11gR2 in Oracle Linux 7

This is caused by the missing libstdc++ package.

Linking using g++ fails searching for -lstdc++

In your previous question, on your screenshot, you have multiple missing packages, including libstdc++.


Difference between COUNT(ColName) vs COUNT(ALL(ColName))

There is no difference.  ALL is the default keyword, as opposed to DISTINCT.


You can try with:

CREATE TABLE a
(
  a1 INTEGER,
  a2 INTEGER,
  a3 INTEGER,
  a4 INTEGER,
  -- other columns
  FOREIGN KEY (a1, a2, a3, a4) REFERENCES b MATCH FULL
);

From the manual:

  A value inserted into the referencing column(s) is matched against the values of
  the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table.


Reason to not use nullable number in Oracle?

Realistically, the requirement is crazy.  Like all great crazy ideas, however, it is probably based on a nugget of potential reasonableness taken far out of context by people that have no understanding of the underlying rationale.

It can be reasonable to design a database schema such that no NULL values are allowed.  If you do that, however, you are committing to a level of normalization where every non-required element is broken out into an separate table with an appropriate foreign-key reference back to the parent.  It's not often done in practice but in cases where it makes sense to do, there can be benefits.

If you are going to design a database schema such that no NULL values are allowed, it makes no sense to allow let alone require magic values to indicate that something is unknown.  That introduces all the problems that allowing NULL values has plus it adds additional code to check for the magic values that has to get repeated all over the place.  It makes no sense to develop an API that requires magic values to be passed in regardless of the database design-- if you're going to hobble your code with checks for magic values, you really ought not let that insanity propagate out to other systems.


How can I find the source of postgresql per-connection memory leaks?

If you were using community PostgreSQL, the definitive answer to figuring out where the memory is going on Linux is to connect to a bloated backend with gdb, and do p MemoryContextStats(TopMemoryContext) and then inspect the output from the server log file.  But since you are not using community PostgreSQL, I guess the definitive answer would be to contact your paid support and ask them to figure it out for you.

The most likely cause for this is that PostgreSQL keeps a per-connection cache of metadata about all the database objects (tables, indexes, etc.) it has touched during the connection lifetime.  There is no upper limit on how big this cache can get, and no mechanism for expiring it.  So if you have hundreds of thousands or millions of these things, and a long-lived connection will eventually touch each one of them, then their memory usage will continuously grow.  The usual solution to this (assuming you can't reduce the number of objects in your database) is to set up your connection pooler so that each connection has a maximum time to live before being closed rather than recycled.


Working example of OPENDATASOURCE command using SQL Authentication

To run it with a SQL login, just need to specify User ID and Password in the connection string (called "init string" in BOL)

SELECT
    * FROM
OPENDATASOURCE (
   'SQLNCLI', -- or SQLNCLI
   'Data Source=OtherServer\InstanceName;Catalog=RemoteDB;User ID=SQLLogin;Password=Secret;').RemoteDB.dbo.SomeTable


Setting PostgreSQL data_directory in postgresql.conf not working

You are not doing anything wrong per se, and the documentation is not wrong. But the initscript from CentOS doesn't support you doing things this way, so you have to change the initscripts to not check for these files, since PostgreSQL will check for it itself and just try to start the database even if these files are not there.

So to test it out, comment out the if, then and the whole else block and just let it run the ... part of the script. PostgreSQL should read the settings correctly and start itself without a problem. And you might of course inform the people responsible for the CentOS package that they should do it differently. If it doesn't run, you should get a reasonable error message from PostgreSQL itself.


If you need this for moving data among databases, as you indicate in your comments, I'd suggest using psql and its \copy command.

This will definitely work, as it writes the files onto the local machine (the one the client is running on, eg. your laptop), and if you can connect using pgAdmin 4, you can also connect using psql.

The process would look like

psql -h dbhost -U whatever -d yourdb
psql> \copy table_name TO ~/table_name.csv WITH (FORMAT csv)
psql> \q

psql -h other-dbhost -U whatever -d your_other_db
psql> \copy other_table FROM ~/table_name.csv WITH (FORMAT csv)

The file location syntax assumes you are on Linux.


JOIN 2 Tables on 1 Table and Select their separate quantity

First, let's sum up a few assumptions:

It appears that a given item cannot be both a consumable and a shipment unit, unless item_code is not a unique value in item.
The shipment_unit table is a simple list of all "unit" items in all shipment boxes; shipment_consumable is (similarly) a list of all "consumables" in all shipment boxes.
Both shipment_unit and shipment_consumable have no quantity column of their own; so, if a given shipment box was supposed to have (say) 1 Windows phone and two wired headsets, you would have to have two rows in shipment_consumable for the box, with the same shipment_box_id and item_id.
You are looking for the items and quantities in 1 instance of a shipment_box, rather than the total items and quantities in the total quantity of the shipment_box. That is to say, if shipment_box.quantity is 17, and the box contains 2 Windows phones, you want 2 as the count, not 34 (2 * 17).

First - I must say that there appears to be absolutely no reason to have both shipment_unit and shipment_consumable. The tables appear to have the exact same structure, and foreign keys to the exact same other tables (shipment_box and item). You know whether a given item is consumable or a "unit" form the item table, so there's no good reason I can see to separate the two things from each other. I feel obligated to point this out, as I suspect your design would be much simpler with just one table (shipment_content, perhaps).

Similarly, having a quantity field for each item in a shipment_unit (or _consumable, or _content) seems like an obvious plus; barring different values for date_added, there's no good reason to have:

 shipment_unit_id | shipment_box_id | item_id |   date_added
------------------+-----------------+---------+------------------
         7        |        4        |    1    | 2017-10-11 00:00
         8        |        4        |    1    | 2017-10-11 00:00

in order to get two Windows phone in the same shipment_box. This may not seem likely at this point, but to me it seems like a trivially predictable need for the future. That said, some would say if you don't need it as part of the current plans, leave it out rather than cluttering your design with what could become a bunch of unused fields.

That said, we don't always have the option to choose certain elements of the design - you may be stuck with this table layout, through no fault of you own.

So, if this is how it all works, and if it's how it must work, how do you get the info you need? I think your draft query would work accurately, and your JOIN query would not (except for degenerate cases, where there's 0 or 1 rows for a given shipment_box_id in each of the other two tables).

As written, there's one potential issue with your JOIN query - it may not be relevant, but it should be noted. You are performing an INNER JOIN to both ship_unit and ship_consumables. If a shipment_box only had "unit"-type items in it, then you would get back no data for it - because as written, there must be at least one "unit" and at least one consumable for a shipment_box to be displayed.

To avoid this, we should make the joins LEFT JOINs, so we get a shipment_box even if it's only got units or consumables, but not both (Note that by doing this, we'll also get a shipment_box that has no items attached. If necessary, we can filter those out later.)

The next thing to understand is how joins work. Each row in the underlying result set will have the fields from shipment_unit, and the fields from shipment_consumable. If you'll always have at most 1 row from each table, things will work as you expect; otherwise, they won't.

Let's posit a shipment_box (shipment_box_id = 5, box_code = 'BX-255', quantity = 10) with the following contents:

1 Windows phone (unit)
1 wired headset (consumable)
1 wireless headset (consumable)

So, shipment_unit has:

 shipment_unit_id | shipment_box_id | item_id |   date_added
------------------+-----------------+---------+------------------
         10       |        5        |    2    | 2017-10-12 00:00

and shipment_consumable has:

 shipment_consumable_id | shipment_box_id | item_id |   date_added
------------------------+-----------------+---------+------------------
           14           |        5        |    3    | 2017-10-12 00:00
           15           |        5        |    4    | 2017-10-12 00:00

If we join shipment_box, shipment_unit, and shipment_consumable as you indicate, we get the following (I've left out the shipment_box columns, and the shipment_box_id and date_added from the other two tables, for readability):

 shipment_unit_id | su_item_id | shipment_consumable_id | sc_item_id
------------------+------------+------------------------+------------
         10       |     2      |           14           |     3
         10       |     2      |           15           |     4

Basically, since there's no criteria to tie records from shipment_unit and shipment_consumable together, we get the cross-product of the records from each of those table that match our current shipment-box.

When we go to match item rows to what weve already got, we see that each row will match two items - the shipment_unit item, and the shipment_consumable item. So, with item_code attached, we've got this:

 shipment_unit_id | su_item_id | shipment_consumable_id | sc_item_id | item_code
------------------+------------+------------------------+------------+-----------
         10       |     2      |           14           |     3      |  ITM-002
         10       |     2      |           14           |     3      |  ITM-003
         10       |     2      |           15           |     4      |  ITM-002
         10       |     2      |           15           |     4      |  ITM-004

So, when we group by item_code there are two rows with 'ITM-002', and we'll get a count of 2 for that item (even though only one is in the shipment_box).

There are a couple of ways to work around this.

Since, as I noted above, there's really no reason to have separate shipment_unit and shipment_consumable table, you can use a union of the two tables within your existing query, instead of getting the UNION of two completely different queries:

SELECT
    sb.box_code,
    i.item_code,
    COUNT(*) `Qty` -- Display quantity of items
FROM
    shipment_box sb
JOIN
    (SELECT shipment_box_id, item_id FROM shipment_unit
     UNION ALL
     SELECT shipment_box_id, item_id FROM shipment_consumable
    ) AS box_item
ON
    sb.shipment_box_id = box_item.shipment_box_id
JOIN
    item i
ON
    box_item.item_id = i.item_id
GROUP BY
    sb.box_code,
    i.item_code
ORDER BY
    box_code,
    item_code
;

This way, in our example, the subquery would generate three rows - 1 from shipment_unit, and 2 from shipment_consumable.

Of course, that solution still includes a UNION. So, if you don't like that, then we can ignore counting the items from our result set altogether, and make simple separate queries to get the counts:

SELECT
    sb.box_code,
    i.item_code, -- Display item_code from joined item table
    (SELECT COUNT(*)
       FROM shipment_unit su2 INNER JOIN item i2 ON (su2.item_id = i2.item_id)
      WHERE su2.shipment_box_id = sb.shipment_box_id
        AND i2.item_code = i.item_code
    ) +
    (SELECT COUNT(*)
       FROM shipment_consumable sc2 INNER JOIN item i2 ON (sc2.item_id = i2.item_id)
      WHERE sc2.shipment_box_id = sb.shipment_box_id
        AND i2.item_code = i.item_code
    ) as `Qty`
FROM
    shipment_box sb
LEFT JOIN
    shipment_unit su
ON
    su.shipment_box_id = sb.shipment_box_id
LEFT JOIN
    shipment_consumable sc
ON
    sc.shipment_box_id = sb.shipment_box_id
LEFT JOIN
    item i
ON
    (
        i.item_id = su.item_id
    OR
        i.item_id = sc.item_id
    )
GROUP BY
    sb.box_code,
    sb.shipment_box_id,
    i.item_code
ORDER BY
    box_code,
    item_code
;

Note that we have to add sb.shipment_box_id to the GROUP BY, since we're using that for the SELECT COUNT(*) subqueries.

This is making a number of allowances:

It will work even if item_code is not unique (for instance, if you have item_id 27 that's a unit, and item_id 31 that's a consumable, but (since they're actually the same thing) both rows have item_code 'ITM-027')
It should work if the above situation is true, and (for some bizarre reason) you've got a box with item_id 27 in shipment_unit, AND item_id 31 in shipment_consumable. Since it's counting the occurences in both tables regardless, it would correctly generate a count of 2 for item_code 'ITM-027'

If (as I would tend to assume) every item_code has one and only one item_id, We can avoid the extra SELECT COUNT(*) queries altogether.

SELECT
    sb.box_code,
    i.item_code, -- Display item_code from joined item table
    CASE i.item_type
      WHEN 1
        THEN COUNT(DISTINCT su.shipment_unit_id)
      WHEN 2
        THEN COUNT(DISTINCT sc.shipment_consumable_id)
      ELSE NULL
    END as `Qty`
FROM
    shipment_box sb
LEFT JOIN
    shipment_unit su
ON
    su.shipment_box_id = sb.shipment_box_id
LEFT JOIN
    shipment_consumable sc
ON
    sc.shipment_box_id = sb.shipment_box_id
LEFT JOIN
    item i
ON
    (
        i.item_id = su.item_id
    OR
        i.item_id = sc.item_id
    )
GROUP BY
    sb.box_code,
    i.item_code,
    i.item_type
ORDER BY
    box_code,
    item_code
;

Here, we use i.item_type' to determine what value toCOUNT, so we need that in theGROUP BY`.

We're back to basing our COUNT on our result set. As noted before, we could see our incidence of unit or consumable items multiplied through the JOIN. However, we're not counting the rows directly; we're counting the number of distinct IDs from shipment_unit or shipment_consumable. This factors those multiple rows right back out again, and we'll get correct counts.

Finally, here's a DB-Fiddle link showing your two queries (tweaked slightly so the work), and my three, in action.

I'd recommend my 2nd query (COUNT via sub-queries) if item_code is not unique, and my 3rd (COUNT DISTINCT) is it is unique.


According to MySQL Documentation on SELECT ... INTO OUTFILE

Any file created by INTO OUTFILE or INTO DUMPFILE is writable by all
users on the server host. The reason for this is that the MySQL server
cannot create a file that is owned by anyone other than the user under
whose account it is running. (You should never run mysqld as root for
this and other reasons.) The file thus must be world-writable so that
you can manipulate its contents.

You should output the SELECT INTO OUTFILE to /var/lib/mysql as follows
SELECT * FROM data INTO OUTFILE 'data.csv';

Of course, you need to make sure you have FILE permission on gs@localhost.
There are two ways to have this permission given
METHOD #1
GRANT FILE ON *.* TO 'gs'@'localhost';

METHOD #2
UPDATE mysql.user SET File_priv = 'Y' WHERE user='gs' AND host='localhost';
FLUSH PRIVILEGES;

UPDATE 2012-05-01 07:09 EDT
To give yourself FILE privilege, do the following:
service mysql restart --skip-networking --skip-grant-tables
mysql <hit enter>
UPDATE mysql.user SET File_priv = 'Y' WHERE user='gs' AND host='localhost';
exit
service mysql restart


Is there a way to set an order to the way files are available in Azure? I want the latest backup first

You can't change how the files are stored in Azure but I guess you don't want to, you just want so change the sort order of the view in the Azure portal?

In that case I recommend using Microsoft Azure Storage Explorer instead.


3 Node mongo db authentication setup fails

As shown in your question you are now connected with the secondary node. Since secondary may contain stale data, by default MongoDB won't allow you to read any data from secondary.
There are two options to read your data

Connect primary node (use the correct port number )
From secondary, run rs.slaveOk(). This will allow you to read data from the secondary.

Note: run rs.status() or rs.isMaster() to find out which one is primary.


Is SQL Server 2008 going to be obsolete "soon"?

Here's everything you need to know about Microsoft Support Lifecycle Policy and from here you can search for specific products lifecycle information. Specific for 2008 family here.

Interestingly enough it was announced on the 18th of July this year that SQL Server 2008 Service Pack 4 update is to be supported until 7/9/2019.
Source


How do you remove a mirrored database SQL Server 2012

The operation cannot be performed on database "test0224aEd62" because it is involved in a database mirroring session or an availability group.

The error is self explanatory .. you cannot drop a database if it is part of DB mirroring, AlwaysON or replication.

I normally avoid using GUI when I have option of doing same thing using TSQL ...

For future readers, you can break database mirroring using below TSQL command (run it on the secondary server)

ALTER DATABASE <<DB NAME>> SET PARTNER OFF

Once mirroring is broken, then you can drop that database using

alter database <<db name>> set single_user with rollback immediate
go
drop database <<db name>>
go


How to grant access permision for multiple database and multiple tables to a user in MySQL?

GRANT ... ON dbname.tablename TO user@... IDENTIFIED BY ...;

You can grant ON *.*, ON dbname.*, ON db.tbl, or even on column(s).

You will need multiple GRANTs for multiple tables.


Matching a value with every element of a set of values - Postgresql

This kind of query is called a relational division, where you "divide" film_country by country to obtain a "quotient" that represents a number of films, and then filter out on this number.

The canonical way to write it would be:

SELECT name FROM film JOIN
  (SELECT id_film FROM film_country
   GROUP BY id_film
   HAVING count(*)=(select count(*) from country)
  ) s
ON (film.id_film=s.id_film);


Confused by GRANT ALL PRIVILEGES ON *.* (...) WITH GRANT OPTION

I really had to think about this one. Error 1442 affecting GRANT commands ?

Note the MySQL Documentation

  Error: 1442 SQLSTATE: HY000 (ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG)

  Message: Can't update table '%s' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

If you are using managed hosting, some are known to use triggers and stored procedures to deliberately prevent running GRANT commands.

EXAMPLE : Amazon RDS allows the following:

  Q: What privileges are granted to the master user for my DB Instance?

  For MySQL, the default privileges for the master user include: create, drop, references, event, alter, delete, index, insert, select, update, create temporary tables, lock tables, trigger, create view, show view, alter routine, create routine, execute, trigger, create user, process, show databases, grant option.

What grants are disallowed ?

SUPER
FILE
SHUTDOWN (See my post what could cause a mysql process to close (clean))
REPLICATION SLAVE
REPLICATION CLIENT
See MySQL Documentation on all the GRANTS

If you are seeing Error 1442, then your hosting company must be using some Proxy code written as a Stored Procedure or has created triggers against the mysql schema to throw regular GRANT command under the bus. From a business point-of-view, they are right in doing this.

If Amazon RDS allowed all grants, can you imagine the havoc being wreaked ?

If you had SHUTDOWN, you could shutdown MySQL RDS instance without Amazon's API
If you had REPLICATION SLAVE and REPLICATION CLIENT, you could setup Slaves outside of Amazon (with the right security group defined).
If had SUPER, you can kill connections, hose binary logs, increase connection limits, etc.
If you had FILE, you could create files with SELECT ... INTO OUTFILE right inside the MySQL RDS Instance and LOAD DATA ... INFILE from external sources (again, with the right security group defined).

Therefore, you need to read the hosting company's documentation on the limits they place on MySQL grants. If Amazon does this with RDS, I am sure other companies would have the same protocols in place. The way around this for an Amazon RDS user would be to ditch Amazon RDS and switch to Amazon EC2.

Contact your hosting company and see if this is indeed the case.


How to use wal_level in Postgres to set the recovery mode to simple for a database like how Microsoft SQL Server does it?

PostgreSQL works quite differently. Transaction log files will be removed with all wal_levels (as soon as the file is full and archived and a checkpoint has completed).
If you want to live without WAL archives, and all you need is crash recovery, you can set wal_level to minimal.


mysqldump file restore while sql is still being dumped

You could do things to speed up this whole process

STEP 1 : Disable the double write buffer / increase log buffer size

Go to the target server. Login o mysql as root and run

mysql> SET GLOBAL innodb_fast_shutdown=0;

then restart mysqld like this

service mysql restart --innodb-doublewrite=OFF --innodb-log_buffer-size=268435456

This disables the double write buffer and make the log buffer 256M

Running SET GLOBAL innodb_fast_shutdown=0; beforehand does a complete flush of InnoDB.

STEP 2 : Pipe mysqldump from source server to target server

mysqldump ... | mysql ...

This will not save the dump to a file. This send the commands straight to the target server

STEP 3 : Restart mysqld on the target server (restore original options)

service mysql restart

SUGGESTED READING

I have suggested disabling the double write buffer before

May 03, 2014 : Optimizing a large number of INSERT ... SELECT statements
Dec 19, 2014 : When is it safe to disable InnoDB doublewrite buffering?
Sep 29, 2016 : MySQL settings useful to speed up a mysqldump import

I have also suggested piping mysqldump to mysql client before

Jan 13, 2012 : MySQL - MySqlDump uses \n instead of null
Mar 02, 2012 : How to log verbose output from mysqldump?
Nov 11, 2016 : Large MYSQL DB on single file
Nov 23, 2016 : Moving large databases


Return row with most matching parameters

;WITH c AS
(
  SELECT RowID,
    c = CASE WHEN DocumentID = @DocumentID THEN 1 ELSE 0 END
      + CASE WHEN EmployeeID = @EmployeeID THEN 1 ELSE 0 END
      + CASE WHEN CompanyID  = @CompanyID  THEN 1 ELSE 0 END
  FROM dbo.TableName
)
SELECT TOP (1) RowID FROM c ORDER BY c DESC;


List table sizes for all tables on all databases

If you wanted to get this across all of your environment, for all of your databases...and you don't mind using PowerShell... You will need run this from a machine that at least has SQL Server 2008 Management Studio installed.

# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

function Get-TableSize ([string[]]$server) {
    foreach ($srv in $server) {
        $s = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $srv

        $s.Databases.Tables |
            ? {-Not $_.IsSystemObject} |
                Select @{Label="Server";Expression={$srv}},
                    @{Label="DatabaseName";Expression={$_.Parent}},
                    @{Label="TableName";Expression={$_.Name}},
                    @{Label="SizeKB";Expression={$_.DataSpaceUsed}}
    }
}

As labeled the DataSpaceUsed SMO object outputs in "KB", you can modify this to be the measurement of your choice by just putting the abbreviated reference for it. So if I wanted "MB": $_.DataSpaceUsed/1MB.

In the function ([string[]]$server), the brackets "[]" mean the parameter accepts an array of objects. So if you have your servers listed in a file you can call the function like so:

$list = get-content .\ServerList.txt
Get-TableSize -server $list | Out-GridView

I prefer using Out-GridView initially to review the output, and it copies easily straight into Excel for me. You can also output this to the other supported formats of PowerShell if desired.

Example with screenshot, you can also just list the servers out:


Should images be separated from posts in MongoDb?

You feel like they should be separated because that makes perfect sense in the relational world.
But as you said in the NoSql world and in MongoDB in particular, you want to group like-items together.  I've not done extensive research on Mongo, but have spent some time with the online classes and I believe the answer is to store them together.  You probably realize there is no such thing as a join in Mongo and therefore if you wanted to get 100 rows and get their corresponding images, you'd have to get the IDs for the 100 rows and then get 100 rows by their identifier (object_id or whatever).  In other words, you have to do manual joins.


First, have your storage administrator increase the size of your LUN dynamically to the desired size up to 2TB, which is as much as ASM will recognize in a single disk.
When this is completed, you will need to get your OS to recognize the change. If you are using udev rules to present your LUNs, then use the udevadm command to update the device metadata:
udevadm trigger --action=change --verbose [device path]

If desired you can tailor the udevadm command to focus specifically on your LUN device using the device path or the --subsystem-match options. Use the --dry-run option along with --verbose to confirm which devices will be checked for changes before running it for real on a production system.
Once the changes are recognized by the operating system, you must propagate them them in ASM. Login to your ASM instance with SYSASM privileges and execute the following command:
ALTER DISKGROUP [DG NAME] RESIZE;

This will resize your diskgroup to consume the entire LUN in its new capacity.
I have performed these commands on live, running production systems without issue many, many times. It is much more efficient when using an external SAN for storage to resize LUNs this way than to worry about adding separate disks. Avoid that until you hit the 2TB limit, and then make every LUN in the group the same size (2TB).


(I'm assuming SQL Server in this answer, please clarify which RDBMS you're using if it's not SQL Server)

The index you want is:

CREATE INDEX [IX_Parent_Id]
ON [sch].[Parent] (Id) INCLUDE (Name)

You use INCLUDE when a column isn't being filtered on (isn't in the WHERE clause) but is being selected. INCLUDE means that the column will only be included at the leaf level of the index (it's a small efficiency saving, essentially). Also as noted, if you have a SELECT statement without a where clause, indexes will never be used (faster to just scan the table rather than muck about with an index). Index seek is only used when you're returning a comparatively small number of rows, for your sample query if most of your ParentsChild and Parents rows are marked as Active then the optimiser may well decide to index scan instead of seek. A query that would be more likely to get an index seek instead of scan would be something like:

SELECT
     b.Id AS 'ParentsChildId'
    , b.[Name] AS 'ParentsChildName'
    , a.Id AS 'ParentId'
    , a.[Name] AS 'ParentName'
FROM
    [sch].[ParentsChild] b
INNER JOIN
    [sch].[Parent] a
ON  a.Id = b.Id
WHERE
    a.Active = 1 AND
    b.Active = 1 AND
    a.Id = 2

(getting the children of Parent ID 2)


how to get total count of partial counts

Try adding a column name for COUNT(attendance) - such as CountAttendance.

SELECT COUNT(*) FROM
(
SELECT student_id,
       COUNT(attendance) as CountAttendance
FROM   student_attendance
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = (SELECT max_allowed
                            FROM   [configuration])
) a ;

Removing COUNT(attendance) from the SELECT clause of the subquery should also work (as you found out).

  But why a non-aliased COUNT column causes issue?

Because all columns in a subquery should be named/aliased. Some DBMS (and some versions) don't require this, as they provide arbitrary names themselves. Others, like SQL Server, are more restrictive.

You could even have a single column with SELECT 1 AS c in the subquery or SELECT NULL AS c. The outer query needs only to count rows with COUNT(*) so the column list in the subquery is completely irrelevant. What matter are the WHERE and HAVING clauses.


The problem with the query is not to do with the colon, but rather the quote characters you're using. Instead of ‘, use '.


You can kill the task on the SQL Server that is verifying the log files, then you will be able to click Next to get to the next screen in the Wizard.

Just use the query below to find the task running under smsql, and use the kill command to remove the SPID.

The wizard will show a popup error, but will still allow you to continue to the next screen.

Here is a query to help find the SPID to kill:

DECLARE @who2 TABLE(
    [SPID] INT,
    [Status] VARCHAR(200),
    [Login] VARCHAR(200),
    [HostName] VARCHAR(200),
    [BlkBy] VARCHAR(20),
    [DBName] VARCHAR(200),
    [Command] VARCHAR(200),
    [CPUTime] BIGINT,
    [DiskIO] BIGINT,
    [LastBatch] VARCHAR(20),
    [ProgramName] VARCHAR(200),
    [SPID2] INT,
    [RequestID] INT
)

INSERT @who2 EXEC sp_who2

select SPID, [Login], HostName, DBName, Command, LastBatch, [@who2].ProgramName from @who2
where [LOGIN] like '%smsql%'


mysql cannot start because of init.d folder?

In the first one it is looking for a DB in /var/lib/mysql (the default) and in the second, it is looking in /data/mysql, where you have moved to.


SQL Server 2012 Table Partitioning - Have I done it correctly?

Unfortunately, you are correct the suspect that the partitioning you created can get you in trouble.

First of all, partition elimination only happens if the partitioning key is in the query you execute. In other words, any query that does NOT contain AcctYear will do (at least) 31 seeks. SQL Server has no way to know which partition to search if you don't specify it.

Second, there are several issue with the optimiser and partitioning that prevents elimination even when it is theoretically possible to achieve it (like in your  example). In SQL Server, you can think of a partition as a hidden index column. In your case, if you were to index in such a way that job number was leading the index, your query with both AcctYear and job number might do only a single seek.

If you add the most common queries you expect to your question, it would be possible to advise you on the best partitioning strategy that achieves your goal.


VMs are fine for testing, performance overhead exists but it is at most 30% under high load, not orders of magnitude.

If you want to run multiple database instances on the same machine, what you are looking for is mysqld_multi. You should probably avoid it, though, it is a terrible, messy solution. VMs are a better approach. Or containers if you really feel you have to avoid VMs at all cost.


Unable to browse to folder on SSRS 2012 through report url

You will have to call /pages/folder.aspx with an ItemPath parameter as absolute path /Test which url encoded will look like this.

  http://192.168.8.1/My_ReportSever/Pages/Folder.aspx?ItemPath=%2fTest

You can of course always copy/paste the url when you browse the server.


SQL Pull replication stops on unintialized subscription

If  you want to Pull from a SQL Agent, you'll have to schedule and execute the replication job with a custom program or script.  Here's an old sample of doing it in PowerShell:
$asm = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
$asm = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Replication")
$asm = [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.RMO")

function Do-Replication
{

<# .SYNOPSIS Initiate Merge Replication Sync
   .DESCRIPTION This Function kicks of a Transactional Replication Synchronization
   .EXAMPLE Do-Replication -subscriber "DBROWNE0" "DBROWNE0" "inventory" "subscriberTest" "replTest" $false 1 4;
   .PARAMETER subscriber The SQL Instance Name name of the Publication, EG localhost
   .PARAMETER spublisher The SQL Instance Name name of the Publisher, eg MyPublisher
   .PARAMETER publication The name of the publication
   .PARAMETER subscriptionDatabase The name of the Subscriber Database
   .PARAMETER publicationDatabase The name of the publisher database
   .PARAMETER forceReInit $true to force a ReInitialization of the subscription, $false otherwise
   .PARAMETER verboseLevel Logging verbosity level
   .PARAMETER retries Number of times to retry the sync in case of a failure #>

  param([string][Parameter(Mandatory = $true,position = 0)] $subscriber,
    [string][Parameter(Mandatory = $true,position = 1)] $publisher,
    [string][Parameter(Mandatory = $true,position = 2)] $publication,
    [string][Parameter(Mandatory = $true,position = 3)] $subscriptionDatabase,
    [string][Parameter(Mandatory = $true,position = 4)] $publicationDatabase,
    [Boolean][Parameter(Mandatory = $true,position = 5)] $forceReInit,
    [int32][Parameter(Mandatory = $true,position = 6)] $verboseLevel,
    [int32][Parameter(Mandatory = $true,position = 7)] $retries
  )

  "Subscriber: $subscriber";
  "Publisher: $publisher";
  "Publication: $publication";
  "Publication Database: $publicationDatabase";
  "Subscription Database: $subscriptionDatabase";
  "ForceReInit: $forceReinit";
  "VerboseLevel: $verboseLevel";
  "Retries: $retries";

  for ($counter = 1; $counter -le $retries; $counter++)
  {
    "Subscriber $subscriber";
    $serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $subscriber;
    try
    {
      $serverConnection.Connect();
      $transPullSubscription = New-Object Microsoft.SqlServer.Replication.TransPullSubscription;
      $transPullSubscription.ConnectionContext = $serverConnection;
      $transPullSubscription.DatabaseName = $subscriptionDatabase;
      $transPullSubscription.PublisherName = $publisher;
      $transPullSubscription.PublicationDBName = $publicationDatabase;
      $transPullSubscription.PublicationName = $publication;
      if ($true -ne $transPullSubscription.LoadProperties())
      {
        throw New-Object System.ApplicationException "A subscription to [$publication] does not exist on [$subscriber]"
      }
      if ($null -eq $transPullSubscription.PublisherSecurity)
      {
        throw New-Object System.ApplicationException "There is insufficent metadata to synchronize the subscription. Recreate the subscription with the agent job or supply the required agent properties at run time.";
      }
      $transPullSubscription.SynchronizationAgent.Output = "c:\temp\ReplicationLog.txt";
      $transPullSubscription.SynchronizationAgent.OutputVerboseLevel = $verboseLevel;

      if ($forceReInit -eq $true)
      {
        $transPullSubscription.Reinitialize();
      }

      $transPullSubscription.SynchronizationAgent.Synchronize();
      "Sync Complete";
      return;
    }
    catch [Exception]
    {
      if ($counter -lt $retries)
      {
        $_.Exception.Message + ": " + $_.Exception.InnerException;
        "Retry $counter";
        continue;
      }
      else
      {
        return $_.Exception.Message + ": " + $_.Exception.InnerException }
    }
  }
}


How to remove spaces between words and replace with single underscore using 'REPLACE','INSTR' OR 'SUBSTR' oracle function

Something like this might work for you, at least if replace does the same on all your platforms as in SQL Server.

Replace all spaces with space+underscore
Replace underscore+space with an empty string
Replace space with an empty string

replace(replace(replace('my name is            xyz', ' ', '_ '), ' _', ''), ' ', '');


What do I have to do in order to be able to receive notifications?

I'd say that's a pretty way to go about it.  You can create a new user-defined error message using sys.sp_addmessage, which will in turn store it in sys.messages.  And then when you add your alert, you can specify to alert on a specific @message_id.  This can be seen through the definition of sp_add_alert.

Then when you have this alert in place and the error message is raised, you should get a notification provided that side is all set up correctly.


Store Mysql Index in separate .Ibd file?

In theory it's possible. What you call a table in InnoDB is a B+tree index named PRIMARY. The key in the PRIMARY index is the primary key field(s).

All secondary indexes are B+tree indexes, too. The key would be the secondary index field(s). The value is the primary key.
(See my slides to get more details http://www.slideshare.net/akuzminsky/indexes-in-my-sql-aleksandr-kuzminsky-https-twindbcom?ref=https://twindb.com/efficient-use-of-indexes-in-mysql/)

Although logically the PRIMARY and secondary indexes are separate they're still stored in one .ibd file (that's assuming innodb_file_per_table=ON). There is no way to store them in separate files.


Nested set model: retrieve sub-tree containing nodes that match condition

The nested set model makes this kind of queries, for unlimited levels, easy to write:

SELECT *
FROM sr_projects
WHERE name LIKE '%x%'

UNION ALL

SELECT p.*                                 -- the parents
FROM sr_projects AS p
  JOIN
    ( SELECT lft
      FROM sr_projects
      WHERE name LIKE '%x%'
    ) AS x
    ON p.lft < x.lft AND x.lft < p.rght

UNION ALL

SELECT ch.*                                -- the children
FROM sr_projects AS ch
  JOIN
    ( SELECT lft, rght
      FROM sr_projects
      WHERE name LIKE '%x%'
    ) AS x
    ON x.lft < ch.lft AND ch.lft < x.rght ;

Test at SQLfiddle-1

It might be more efficient if you use a temp table, because the subquery with the unsargable condition (name LIKE '%x%') will only be run once. But that depends on the version of MySQL you are using. Some newer versions are capable of subquery optimizations (and can actually cache and reuse the subquery results and even automatically index them):

CREATE TEMPORARY TABLE temp_x AS
SELECT *
FROM sr_projects
WHERE name LIKE '%x%' ;

and then:

SELECT *
FROM temp_x

UNION ALL

SELECT p.*                                 -- the parents
FROM sr_projects AS p
  JOIN
    temp_x AS x
    ON p.lft < x.lft AND x.lft < p.rght

UNION ALL

SELECT ch.*                                -- the children
FROM sr_projects AS ch
  JOIN
    temp_x AS x
    ON x.lft < ch.lft AND ch.lft < x.rght ;

DROP TABLE temp_x ;

Test at SQLfiddle-2


SQLAnywhere: Mobilink upload_fetch_column_conflict seems not to be executed

upload_fetch_column_conflict is only getting called, if there is a line updated. Therefore an insert will not trigger that function.

If inserting a new line which might conflict with a recent change on consolidated database, the conflict needs to be caught during upload workflow -- so e.g. inside processing of upload_insert.


Initializing Transactional Replication From two Different Database Backup

The replication topology that you are describing is known as the Central Subscriber Model which is comprised of two or more Publishers replicating to one Subscriber.  The dilemma you are having is how to go about initializing the Central Subscriber, which is a common conundrum.

I have talked about this in detail in my post Central Subscriber Model Explained and have found through experience that the best way to initialize a Central Subscriber is with a snapshot, utilizing horizontal partitioning, static row filters, and setting the Action if name is in use article properties to Delete data. If article has a row filter, delete only data that matches the filter.

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


Performance Improvement went wrong in Production, worked fine in Test

Your rewrite does not have the same semantics as the original query, so different results are not surprising.

This is true without worrying about the NOLOCK hint, and even if it is (somehow) guaranteed that the highest ref_clnt value is associated with the row having the highest RefNo value. See this db<>fiddle example.

Precision is important when dealing with computers, so you need to think carefully about data types and edge cases. The maximum RefNo calculation will use string sorting semantics, so '999' sorts higher than '1000'. There are several other important differences between the queries. I'm not going to list all of them, but NULL handling is another example.

There are also numerous bugs in both versions of the code. The original will fail if any ref_clnt value of -1000000000 or lower is returned, because that will not fit in varchar(10). The sign makes the length 11.

The easiest way to safely improve the original version of the code is to add an index on a computed column:

ALTER TABLE dbo.table_name
ADD ref_cc AS
    ISNULL(CAST(ref_clnt AS integer), 0);

CREATE NONCLUSTERED INDEX i
ON dbo.table_name (S_Mode, ref_cc);

db<>fiddle demo

The execution plan can then seek directly to the highest ref_clnt row (sorted as integer) for the given S_Mode value:

The vendor's original SQL may still be of debatable quality, but at least it will run faster and produce the same results.

The vendor's new suggestion:

SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'

...is still problematic, at least in theory, because ISNULL uses the data type of the first parameter, so the integer literal 0 is implicitly cast to varchar(50).

  The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.

The MAX still operates on a string, which may produce unexpected results. In any case the expression is still not seekable without a (different) computed column index.


You didn't specify your RDBMS or version, but I looked at another question you asked here and noticed you were using at least SQL Server 2016.  The following should work on SQL 2012 and later.  I added a few more rows to your sample data to show another month of scans (December) so I could make sure I only presented the subtraction of a dropped mailbox one time.

You'll note that ObjectSID 333 did not have scan data for November or December.  We reflect the drop during the November scan.

Take a look at my solution and let me know if I have misunderstood your requirements or you see an error in the results.

--demo setup
drop table if exists dbo.mailbox
CREATE TABLE [dbo].[Mailbox](
[ObjectSID] [nvarchar](184) NULL, --ObjectSID of Mailbox
[Database] [nvarchar](64) NULL, --Exchange Database
[PrimarySMTP] [nvarchar](254) NULL, -- eMailAddress
[ItemCount] [int] NULL, -- SUM of eMails
[Itemsize_MB] [int] NULL, -- size of Mails
[Itemsize_del_MB] [int] NULL, -- size of deleted mails
[Arch_Database] [nvarchar](64) NULL, -- Name of the Archive Exchange Database
[Arch_ItemCount] [int] NULL, -- Sum of all archived mails
[Arch_Itemsize_MB] [int] NULL, -- Size of archived mails
[Arch_Itemsize_del_MB] [int] NULL,  --Size of deleted archived mails
[ScanTime] [date] NULL  --Date of the last SCAN
) ON [PRIMARY]
GO
Insert into Mailbox Values
 (111,N'Database1',N'Sample.User1@domain.com',63913,16535,1,N'ARCH1',0,0,0,'2018-10-22')
,(111,N'Database1',N'Sample.User1@domain.com',63958,16540,2,N'ARCH1',0,0,0,'2018-10-24')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-11-19')
,(111,N'Database1',N'Sample.User1@domain.com',64533,16664,2,N'ARCH1',0,0,0,'2018-12-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-11-19')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-24')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-10-22')
,(222,N'Database2',N'Sample.User2@domain.com',296,11,0,N'ARCH2',39139,10867,0,'2018-12-19')
,(333,N'Database1',N'Sample.User3@domain.com',55292,12723,23,N'ARCH1',37302,7128,0,'2018-10-22')
,(333,N'Database1',N'Sample.User3@domain.com',55532,12855,25,N'ARCH1',37306,7128,0,'2018-10-24')

My solution

;WITH MailBoxAgg
AS (
    --Select all distinct ScanTimes from Mailbox and join
    --against Mailbox where the ScanTime <= one of the distict ScanTimes
    --If the Mailbox ScanTime <> Distinct ScanTime, that means the mailbox was dropped
    --so force 0 into [Mailbox in MB]
    SELECT [database]
        ,ObjectSID
        ,CASE
            WHEN mb.ScanTime <> st.ScanTime
                THEN 0
            ELSE sum(Itemsize_MB + Itemsize_del_MB + Arch_Itemsize_MB + Arch_Itemsize_del_MB)
            END AS [Mailbox in MB]
        ,mb.ScanTime
        ,st.ScanTime AS stscan
    FROM (
        SELECT DISTINCT ScanTime
        FROM Mailbox
        ) st
    JOIN Mailbox mb
        ON mb.ScanTime = (
                SELECT max(ScanTime)
                FROM Mailbox
                WHERE [Database] = mb.[Database]
                    AND ObjectSID = mb.ObjectSID
                    AND ScanTime <= st.ScanTime
                )
    GROUP BY ObjectSID
        ,[Database]
        ,mb.ScanTime
        ,st.ScanTime
    )
SELECT [database]
    ,objectsid
    ,[Mailbox in MB]
    ,CASE
        --if the Mailbox ScanTime <> DistinctScanTime, get the
        --previous row value and negate it to represent negative
        --growth for a dropped mailbox
        WHEN scantime <> stscan
            THEN lag([mailbox in MB]) OVER (
                    PARTITION BY [database]
                    ,objectsid ORDER BY scantime
                    ) * - 1
        --if the previous row [mailbox in MB] is null,
        --this is the first row for the mailbox, so force current MB as growth
        WHEN lag([mailbox in MB]) OVER (
                PARTITION BY [database]
                ,objectsid ORDER BY scantime
                ) IS NULL
            THEN [mailbox in MB]
        --subtract the previous mailbox mb from the current to reflect growth
        ELSE [mailbox in MB] - lag([mailbox in MB]) OVER (
                PARTITION BY [database]
                ,objectsid ORDER BY scantime
                )
        END AS Growth
    ,ScanTime
    ,stscan
FROM MailBoxAgg

The results

| database  | objectsid | Mailbox in MB | Growth | ScanTime   | stscan     |
|-----------|-----------|---------------|--------|------------|------------|
| Database1 | 111       | 16536         | 16536  | 2018-10-22 | 2018-10-22 |
| Database1 | 111       | 16542         | 6      | 2018-10-24 | 2018-10-24 |
| Database1 | 111       | 16666         | 124    | 2018-11-19 | 2018-11-19 |
| Database1 | 111       | 16666         | 0      | 2018-12-19 | 2018-12-19 |
| Database1 | 333       | 19874         | 19874  | 2018-10-22 | 2018-10-22 |
| Database1 | 333       | 20008         | 134    | 2018-10-24 | 2018-10-24 |
| Database1 | 333       | 0             | -20008 | 2018-10-24 | 2018-11-19 |
| Database1 | 333       | 0             | 0      | 2018-10-24 | 2018-12-19 |
| Database2 | 222       | 10878         | 10878  | 2018-10-22 | 2018-10-22 |
| Database2 | 222       | 10878         | 0      | 2018-10-24 | 2018-10-24 |
| Database2 | 222       | 10878         | 0      | 2018-11-19 | 2018-11-19 |
| Database2 | 222       | 10878         | 0      | 2018-12-19 | 2018-12-19 |


Indexed range comparison of array key's value in row inside JSONB

You want to test a given key for a range of numeric values (not just for equality), but existing jsonb operators do not provide such functionality, even less with index support.

Makes the task difficult to speed up, but there are still options. The best solution depends on data distribution, value frequencies, typical queries, avg. column size and other undisclosed details.

Assuming the general case that you want to test for arbitrary names and values and return the whole row.

SELECT *
FROM   tbl t
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements(t.jdata->'array') j
   WHERE  j->>'name' = 'test1'                           -- !
   AND   (j->>'value')::numeric > 4.5
   )
AND t.jdata->'array' @> '[{"name": "test1"}]';

The outer predicate AND t.jdata->'array' @> '[{"name": "test1"}]' is logically redundant and only makes sense if you expect a small percentage of rows to even have 'test1', in which case the query can make good use of this jsonb_path_ops index:

CREATE INDEX tbl_jdata_arr_test1_idx ON tbl USING gin ((jdata->'array') jsonb_path_ops);

Don't index the whole jsonb column for this purpose, would just makes the index bigger and slower. Only put the nested array into the index and make sure the WHERE condition of the query matches.

If you're only ever interested in 'test1', make it a partial index instead:

CREATE INDEX ...
WHERE  jdata->'array' @> '[{"name": "test1"}]';

We must repeat the test j->>'name' = 'test1' in the EXISTS expression to assert the object with "name":"test1" provides the value > 4.5 - and not just any object in the array.

Also, I would rather cast to numeric, not float. The implicit rounding of floating point numbers might make your test fail in extreme cases.

Related:

Speed up range test for key values nested in jsonb array of objects

More specialized, smaller and faster indexes are possible, for instance by extracting a Postgres array of names and only indexing that. Related:

Search for nested values in jsonb array with greater operator
Find rows containing a key in a JSONB array of records (my answer to your previous related question)
Index for finding an element in a JSON array
Index for finding combinations of attributes in JSON array


I would start by determining the stats at run-time. Only if that turns out to be too expensive, I would denormalize the statistics:

select f1.fighter_id
     , count(case when f2.is_draw = 0 and f1.fighter_id = f2.winnner_id then 1 end) as #wins
     , count(case when f2.is_draw = 0 and f1.fighter_id = f2.looser_id then 1 end) as #loss
     , count(case when f2.is_draw = 1 and f1.fighter_id in (f2.winner_id, f2.looser_id) then 1 end) as #draws
     , ...
from fighter f1
join fights  f2
    on f1.fighter_id in (f2.winnner_id, f2.looser_id)
group by f1.fighter_id;


I don't think it's a matter of performance but of management.

With separate file per table, you can store different databases in different storage devices for example.

You can deal with the case of very large databases in file systems that can't handle big files (at least postpone the problem until one table reaches the file size limit).

You don't have uncontrolled tablespace growth. If you have some big tables that you drop, the ibdata file stays small.

One aspect that may have some effect on performance is the fragmentation of table data and indexes, which will be limited per table. But that needs testing to be confirmed.


What are you trying to "sync" exactly?  A subset of tables, the entire database?  Replication was designed for syncing data between databases and it offers a number of different flavors and features.  It's pretty verbose and should likely tackle just about any "sync" scenario you're looking to accomplish.  My main issue with Replication is that people often take it too far and try to use it as a fully fledged HA/DR solution when there are better approaches instead.

If you don't want to setup Replication (as it does have it's fair share of caveats and problems) and want to tap into Change Tracking or even CDC, then you'll likely be creating your own sync jobs/routines.  I'm not aware of any 3rd party tools that prefer Change Tracking or CDC over replication in this domain.


Replicate it!  Else you won't be able to failover to a Slave if the Master dies.  Deal with access control in some of the following ways:

Use GRANT to restrict what each user can see.
Use VIEWs to avoid showing the sensitive columns.
Segregate the sensitive columns into other tables (vertical partitioning), so that the GRANT applies to tables instead of columns and VIEWs aren't needed.


Create a months table:

CREATE TABLE Months (
  MonthDate date
);

and populate it with dates like these:

2016-07-01
2016-08-01
2016-09-01
...

Then you can get your results with a query like this:

SELECT
  m.MonthDate,
  SUM(TIMESTAMPDIFF(HOUR,
                    GREATEST(m.MonthDate                    , t.StartDateTime ),
                    LEAST   (m.MonthDate + INTERVAL 1 MONTHS, t.FinishDateTime)
                   )) AS Duration
FROM
  YourTable AS t
  INNER JOIN Months AS m ON t.StartDateTime  < m.MonthDate + INTERVAL 1 MONTHS
                        AND t.FinishDateTime > m.MonthDate
GROUP BY
  m.MonthDate
;

The output would be in the form:

MonthDate   Duration
----------  --------
2016-08-01  10
2016-09-01  9

Changing the dates to month names would, in my view, constitute a presentation issue but, if necessary, MySQL offers built-in functions like MONTHNAME to do the transformation directly in SQL.

Additional information can be found in the MySQL manual:

TIMESTAMPDIFF()
MONTHNAME()
GREATEST()
LEAST()


You want faster?  Add INDEX(floor_id, user_id).


What would the relational diagram look like in this situation?

I am with your teacher here. I cannot see any difference in degree of Normalization between the two solutions, but you are embedding aspects of a particular implementation presentation (Two views of what is often referred to as the External or Logical Schema.) into the core data structures. When performing the initial database design (of the Conceptual Schema) it is preferred to hide as much as possible both of the external presentations and of the physical table design.

Once the Conceptual Schema has been designed (and fully normalized), then one would determine the best way to present the Logical views of that structure to external applications, and the optimal physical assignment of columns to tables (the Physical Schema) to maximize performance.

Your proposal is pushing subtle choices of Physical and External design into the Conceptual Schema inappropriately.

It appears to me that your teacher is having difficulty explaining that your proposal might be a valid physical database design, later, given certain assumptions, and accurately reflects the External views as currently implemented, but fails to meet the needs of a Conceptual Schema that maximizes normalization with minimal clutter.

A key observations is that whenever two tables (entities) have the same Primary Key, then even if the normalization is the same the clutter has been increased. There will always be many ways to clutter a clean (Conceptual Schema) design, but this clutter can always be eliminated from it. In a real world database design with hundreds or even thousands of tables minimizing clutter is an essential design attribute.


Postgres not returning data on array_agg query as below

A LEFT JOIN can solve it, like Laurenz provided.
But I suggest an ARRAY constructor in a LATERAL subquery instead:
SELECT l.name, b.book_list
FROM   library l
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT title
      FROM   books
      WHERE  library = l.id
      )
   ) b(book_list)
WHERE  l.id = :library_no;

This way, you don't need to aggregate in the outer query level and don't need to GROUP BY there.
You also don't need COALESCE, since the ARRAY constructor over an empty result already produces an empty array ({}).
And it should be faster for a small selection in library - obviously the query gets the result for a single given library.
Aside, you only need the variable :library_no in a single place like demonstrated.
About LATERAL joins:

What is the difference between LATERAL and a subquery in PostgreSQL?

About the ARRAY constructor:

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Basic about joining tables in the manual.


The table starting with #sql is just a temporary table and, if it's not in use, can safely be deleted. MySQL creates this temporary table to perform the various ALTER TABLE operations that you request.

If your original operation never completed, then your data should still be safely in its original table. If not, you can always rename the temporary table yourself and you'll get back whatever is in it.


Efficiently store large list structure in RocksDB so that the data can be retrieved in pages

you need to split your "large list" into a set of nodes of a "Segment B-Tree" and then store each node one as a RocksDB record.

Each node will need to store it's Height in the "Segment B-Tree" and it's left child ID.

This have a number of benefit

This will let you find if one entry is inside the list without
    having to scan the whole list.
Scanning all the leaf node would be fast because leaf node have
contiguous ID
This let you easily fetch any subset of the list using offset.

See : https://www.sqlite.org/fts3.html for more detail.


SQL Licensing on Azure, Standard D4 Windows 2012 Server 8 core, is it 1 phsysical core?

At the risk of stating the obvious, nothing is physical in Azure as far as you are concerned.  The best thing you can do is go to the Azure Pricing Calculator, find the 'SQL Server' section, select the 'Standard' section, find D-Series, select 'D4' and slide the slider to 1.  That's what you will be paying, eg

SQL Server will treat that box as having 8 cores, so should you.  You can also compare and contrast costs, eg try Enterprise edition.  As far as running coreinfo on Azure goes, don't bother unless you've got some time to kill.  By signing up to IaaS you have resolved to care much less about that kind of thing, freeing you up to concentrate on the software.

If you are concerned about performance you should cast your eye over this superb article:

Performance Guidance for SQL Server in Azure Virtual Machines
https://msdn.microsoft.com/en-us/library/azure/dn248436.aspx