How to efficiently recompute the data in a rather large X table?

The amount of data we are talking about here is not nearly enough to be this big of a problem. Odds are, a bit of optimization will do you quite nicely.
So, opening your query plan, as I go along:

Fix your WHERE clauses. Your current query forces table scans just about everywhere because you use functions. IsNull(COALESCE(linkedlot.IsPrivate, c.IsPrivate), 0) = 0 should be ((ll.IsPrivate = 0) Or (ll.IsPrivate Is Null And c.IsPrivate = 0) Or (ll.IsPrivate Is Null And c.IsPrivate Is Null)). Side note: are these fields actually nullable? Why? These queries would be MUCH simpler if they were not.

Change UNION to UNION ALL. UNION does an implicit DISTINCT, but you're doing one in the main query already. Depending on your data, this may help a lot or hurt a lot.

Make sure your temporary tables have indexes/primary keys.

If the query actually takes a while, load it into a copy of the table, then MERGE it into the live copy afterwards.

Wait, never mind... very simply, your CROSS JOINs are killing you.
Look through your query plan (or rather, save it and open it up in SQL Server Management Studio), and hover over some of those thick arrows. You're exploding Contract times ApplicationUserRole five times (8 million rows every time), Contract times ApplicationUser twice (6 million rows every time), then slapping all those together (on several levels) to get distinct values (sorting 8-16 million rows every time).
But "explode all possible combinations for every possible way rights can be affected, whether they exist or not, then combine, then sort, then sort again" is not what you want. You want users versus documents, and look up their rights.
So let's do that.
Your query should look like this:
Select
  UserId = U.UserId,
  ContractId = C.ContractId,
  -- If there are explicit denials, MAX should be MIN
  CanRead = Max(Case When <<highest priority condition>>
                     When <<down the list...>>
                     Else 0 End),
  -- If there are explicit denials, MAX should be MIN
  CanWrite = Max(Case When <<highest priority condition>>
                      When <<down the list...>>
                      Else 0 End)
From
  dbo.User As U
  Cross Join dbo.Contract As C
  Left Outer Join (<<Look up first thing...>>)
  Left Outer Join (<<down the list...>>)
Group By
  U.UserId, C.ContractId

So, grabbing a few from your plan:
Select
  UserId = U.UserId,
  ContractId = C.ContractId,
  CanRead = Max(Case When ContractOwner.UserId Is Not Null Then 1
                     When <<down the list...>>
                     Else 0 End),
  CanWrite = Max(Case When ContractOwner.UserId Is Not Null Then 1
                      When <<down the list...>>
                      Else 0 End)
From
  dbo.User As U
  Cross Join dbo.Contract As C
  Left Outer Join dbo.LotSupplier As LotSupplier
    On LotSupplier.ContractId = C.Id
  Left Outer Join dbo.ContractBuyer As ContractBuyer
    On ContractBuyer.ContractId = C.Id
  Left Outer Join dbo.Lot As LinkedLot
    On LotSupplier.LotId = LinkedLot.Id
  Left Outer Join dbo.ApplicationUserRole As ContractOwner
    On ContractOwner.UserId = U.UserId
    And ContractOwner.BUid = C.SignatoryBUId
    And ((C.IsPrivate Is Null) Or (C.IsPrivate = Cast(0 As Bit)))  -- NO ISNULL!!
    And ContractOwner.RoleId In (16, 19, 20)
  Left Outer Join dbo.ContractBuyer As ContractBuyer
    On ContractBuyer.ContractId = C.Id
  Left Outer Join dbo.ApplicationUserRole As Buyer
    On ((C.IsPrivate = Cast(1 As Bit)
        Or (LinkedLot.IsPrivate = Cast(1 As Bit))
    And ContractBuyer.BuyerId = Buyer.UserId
    And ...on and on...
  Left Outer Join (<<down the list...>>)
Group By
  U.UserId, C.ContractId

Unless you're running this on a brutally underspecced server, the entire query can be made to run in a few seconds, trust me.
Since you're also obviously working in what I call a many-hats model (a person can be a user, buyer, owner, et cetera, I highly recommend you consider normalizing out a Person (or Party, or whatever tickles your fancy) table.

Information would have to be updated in one place only
You would not have to do that awful join on e-mail
That join would not fail on updating info in only one place :-)
You would be much better set up for sub-contracting, multi-owner contracting, contract transfers and all that fun

It looks like you're kind of using ApplicationUser for this purpose, but I think your question alone shows one of the downsides to that.


mysql.sock missing: php connection works but cant connect from terminal

First you need to check your MySQL config file my.cnf and make sure it contains socket parameter

[client]
socket=/var/mysql/mysql.sock

[mysqld]
socket=/var/mysql/mysql.sock

if those parameters not presented then you need to add them and start MySQL service.

after that you need to change MySQL folder permission sudo chmod -R 755 /var/lib/mysql/ and make sure it is owned by MySQL user.

finally start MySQL service.

Note: you need to make sure ports are opened for MySQL inside firewall


Difference between pg_column_size(table.*) and pg_column_size(table.col1) + pg_column_size (table.col2)

The 24 byte/row difference rings a bell: it is the same size as the row header in tables and indexes.  What I did not realize up until your question is that the same header size applies apparently to every row you return in a query:

SELECT pg_column_size((1::integer, 2::smallint));
 pg_column_size
────────────────
             30

but

SELECT pg_column_size(1::integer) + pg_column_size(1::smallint) AS pg_column_size;
 pg_column_size
────────────────
              6

When passing a row to pg_column_size(), it calculates the size of the whole row, including the header.

Now playing a bit further with this, there are some interesting facts you can find.

For example, one can check the difference caused by padding the columns up to the nearest 4 bytes (where k is a positive integer):

SELECT pg_column_size((1::smallint, 2::integer));
 pg_column_size
────────────────
             32

Here we got two more bytes than in the first example, the remaining 2 bytes after the smallint got 'wasted'.  This may be important when choosing the order of columns in huge tables - for example, we can squeeze something into that 'empty' space:

SELECT pg_column_size((1::smallint, FALSE, FALSE, 2::integer));
 pg_column_size
────────────────
             32

The other important thing is that columns that have NULL as a value have zero size here:

SELECT pg_column_size((NULL::smallint, 2::integer));
 pg_column_size
────────────────
             28

In contrast, you'll get NULL as a result when trying to add up the column individually:

SELECT pg_column_size(NULL::smallint) +  pg_column_size(2::integer) AS pg_column_size;
 pg_column_size
────────────────
           NULL


Oracle SQL Developer Textcursor disappearing

Adding AddVMOption -Dsun.java2d.opengl=true to ~/.sqldeveloper/<version>/product.conf fixed it for me.


MySQL - Exclude a result if even one foreign key doesn't meet criteria (date availability checker)

You need a DISTINCT because of the n:m relationships
But the date selection i would write differently, so that all holtel.id should be excluded, which have a reservation on that day, so that it can't be choosen anymore
Schema (MySQL v5.7)
CREATE TABLE `reservations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hotel_id` int(10) unsigned DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert  into `reservations`(`id`,`hotel_id`,`start_date`,`end_date`) values
    (1,1,'2020-08-26','2020-09-02'),
    (2,1,'2020-12-06','2020-12-13');

CREATE TABLE `regions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `region_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert  into `regions`(`id`,`region_name`) values
    (1,'Region 1'),
    (2,'Region 2'),
    (3,'Region 3'),
    (4,'Region 4'),
    (5,'Region 5');

CREATE TABLE `hotels` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `region_id` int(10) unsigned DEFAULT NULL,
  `hotel_name` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

insert  into `hotels`(`id`,`region_id`,`hotel_name`) values
    (1,1,'Hotel 1'),
    (2,3,'Hotel 2'),
    (3,5,'Hotel 3'),
    (4,5,'Hotel 4'),
    (5,4,'Hotel 5');

Query #1
SELECT
    DISTINCT hotels.hotel_name, regions.region_name
FROM
    hotels
        LEFT JOIN
    reservations ON hotels.id = reservations.hotel_id
        LEFT JOIN
    regions ON hotels.region_id = regions.id
WHERE
(reservations.start_date is NULL OR reservations.end_date is NULL)

        OR hotels.id NOT IN
        (SELECT h.id
FROM hotels h
    INNER JOIN
    reservations r ON h.id = r.hotel_id
WHERE
   CAST('2020-09-02' AS DATE) BETWEEN r.start_date AND r.end_date)
;

hotel_name
region_name

Hotel 2
Region 3

Hotel 3
Region 5

Hotel 4
Region 5

Hotel 5
Region 4

View on DB Fiddle


How does InnoDB store tables in ibdata file?

InnoDB Architecture

Percona created this picture a long time ago. As you can see, ibdata1 contains many classes of data structures. There are as follows:

Table Data Pages (if innodb_file_per_table disabled)
Table Index Pages (if innodb_file_per_table disabled)
Data Dictionary (Tablespace IDs, Logical-to-Physical Mapping to Tables)
Double Write Buffer (Handles Data Redundancy to Support Crash Recovery)
Insert Buffer (Handles Updates to Secondary Indexes)
Rollback Segments (Manages Reverse Engineering of Uncommitted Transactions)
Undo Space (Containers of Information for Rollback Operations)

BTREEs in InnoDB
If you are concerned how indexes are stored, please read my earlier posts about InnoDB indexes

Apr 04, 2012 : How does innodb_stats_on_metadata relate to query execution plan stability?
Jun 28, 2012 : Benefits of BTREE in MySQL
Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions?
Jan 09, 2013 : Is ANALYZE useful immediately after creating an InnoDB index?
Mar 27, 2013 : Are there implicit indexes in InnoDB like MyISAM?
Jun 14, 2013 : Does the size of the primary key contribute to table size?


Groups and Members database: Which of these two options is a good design?

The first one is probably the best option.

Members of a group is a kind of data that could increase some day and if you keep the first option you will only need to add a new row in the Members table.
Also you can remove or add users from a group without messing with any possible Groups indexes and without needing to update which is a more expensive action.


Create MS SQL login for support engineer to access only vendor database

I - To create a login with access to specific database only:

Use SSMS and connect to the SQL server instance
Open Security -> right click Logins -> New Login (e.g. login named SE)

3 Enter the login details. In Server Roles, make sure only 'public' is checked

4 In User Mapping, make sure only the vendor database is selected.

If public role for the vendor database is not enough, grant the necessary permissions but only on the vendor database

Another solution is to deny access to all databases and then grant just to the vendor database

DENY VIEW ANY DATABASE TO [SE]

See more info here: http://msdn.microsoft.com/en-us/library/ms177518.aspx

II- How to monitor his activity on database (what query he runs, is he try to took any backup and when)

I suggest using some third party tools. Transaction logs don't capture info about executed queries

III-Deny Any backup and restore task (I want to keep this only to sysadmin)

Having  public role will prevent the user to backup or restore databases

he'll get the following messages when he tries to backup or restore a database

T-SQL statement to deny creating backups is:

DENY BACKUP ANY DATABASE to [SE]


Design - duplicating properties vs not allowing delete

There are a few issues that come to mind.

A normalized approach (jobs in one table, addresses in another, related only by a foreign key) is desirable from a purely database design perspective. It makes both storage and reporting more efficient.
With this normalized schema, however, you'll want to make sure that an address isn't entered in duplicate (for instance when creating or changing a job). This could typically be the task of a "create job" stored procedure, but you'd have to build some sort of matching logic to merge addresses like 101 First Street with 101 East 1st St, etc. This logic may well be in the GUI, in the form of an "auto-complete" dropdown or similar.
I'm guessing that there could be legal aspects as well: there are juristictions where a person (like a customer) can request that you remove any personally identifiable information about them after your relation ends. If that information includes that you've done business at their street address, then you'd probably want to ask your legal beagles about the legality of retaining a record of addresses even though you've deleted or masked the job data.


SQL server: create local copy of remote table

You may want to consider transactional replication, which would allow you to have an initial snapshot of the table copied from the source system to the target system. After this initial snapshot, you can then configure transactional replication to push the data changes to the target system.

The requirement for transactional replication is that the table has a primary key, however, it is a feature that I have used for the purpose you described on many occasions.

Bear in mind that replication would need to be activated on the source system and requires some administration to ensure it runs correctly. However, for a single table syncronization setup as described, it should cover everything you need.


Arithmetic overflow error varchar to numeric

You get the error because essentially at the end you're dividing an 8 character long string by a numeric value which is has precision as 3 like this example below

DECLARE @num VARCHAR(8) = '12356456'
DECLARE @den DECIMAL(3,0) = 121

SELECT @num / @den

SQL Server internally tries to convert VARCHAR(8) to DECIMAL(3,0) and fails with your error.

CAST([Column2] as numeric(8)) works because now VARCHAR(8) can be converted to numeric(8).

You don't really need to convert DATEDIFF(week, CONVERT(date,convert(varchar(8),Column1)), GETDATE()) to VARCHAR(8)


Changes to the storage layer of a database, that is also a Publisher in a replication topology are not replicated to Subscribers. The only thing that can be replicated are those objects defined as Articles.

For example, you can change the autogrowth setting of a given database data file at the Publisher without concern for the Subscribers. You can think of the database storage layer as an abstraction. It has to be because you may be using different storage sub system at the Publisher and Subscriber, requiring different data file placement etc.

This particular type of change can be applied without the need to reinitialize replication.

(For interests sake: If you were using Database Mirroring, then DDL changes of this specific nature would make it to the Mirror. Worth noting because Publishers are also often Mirrored in some environments.)


Our replica set config is invalid or we are not a member of it - MongoDB

As per error it seems like you have not configured properly the replica set. How to setup MongoDB Replica Set , you find here.

For example I am writing the steps here to setup MongoDB Replica Set

1) Start a mongod instance.

2) Start another mongod instance.

3) Start Replication.

After launcing the all mongod node instances, connect to any one node and start the mongo shell then type in command prompt

  rs.initiate()

For example

Now the mongo shell prompt would be changed to yournodeinstancename:PRIMARY>

You may also check the status using rs.status() method.

  rs.status()

for more info about rs.status() see the MongoDB documentation here and here

4) Add a MongoDB instance to the Replica Set.

  rs.add();

5) Check the Status.

You may check the status of the Replica Set by running the following command

  rs.status();

6) Getting an overview of the replica set and if current node is master

  rs.isMaster()

For further your ref here , here , here


How to edit current query without executing it?

Don't write a trailing semicolon. If you go through the history, remove the trailing semicolon. That will put the query in the buffer, and you can edit it with \e.


Is this something that will always need to be handled in an exception routine?

The system generated start and end times are keyed to the server's system time at the moment the BEGIN TRANSACTION is executed.  If you have long-running transactions, you should:

Build in exception handling that can retry the transaction.
Rework the offending code so it executes more quickly.  This may sound like a lot of work, but it will be necessary to do some level of rework to handle temporal-based exceptions correctly.  If you're going to do rework, you may as well determine why the transaction is taking so long, and mitigate that behavior.

  Or is Microsoft considering to change that behavior?

There is no way to know what Microsoft will do in future versions of their products unless they have made a public announcement that is not covered by a nondisclosure agreement.  They might modify the temporal table functionality at some point in the future such that the start and end dates are reflective of the current system time at the point of insert/update, however that is not currently how it works.


MySQL - Delete with IN Clause taking more time

WHERE .. IN is slow in most cases.

Use multi-table delete:

DELETE tbl.*
FROM tbl, DELETE_ID
WHERE tbl.id = DELETE_ID.id


Delete a variable number of records by a chronological order

You can use TOP with a variable. You just need to put it in parentheses.

WITH T
     AS (SELECT TOP (@to_delete) *
         FROM   usr_pwd_hist
         WHERE  usr_id = @usr_id
         ORDER  BY history_nr ASC)
DELETE FROM T;


Tables with engines of "MEMORY" does not get dropped when server is restarted

Every table created with CREATE TABLE will create a .frm file followed by other files (such as .MYD and .MYI for MyISAM or .ibd for InnoDB).

When it comes to a table created with ENGINE=MEMORY will create only the .frm file and actual data and index storage will occur in RAM.

If mysqld crashes, the data will be lost, but the .frm will remain.

In your case, you ran CREATE TABLE MY_TEMPORARY_TABLE. This will create a file called MY_TEMPORARY_TABLE.frm.

Had you created the table using CREATE TEMPORARY TABLE MY_TEMPORARY_TABLE, then the table itself would disappear upon mysqld crash or shutdown.


in what situations the dynamic port number in sql server is used?

Dynamic ports are used when a SQL Server instance is configured to use dynamic ports on the specified interface(s). By default, only named instances use dynamic ports as described in the documentation. Below is an excerpt from the doc:

  At startup, when an instance of SQL Server is configured to listen on
  dynamic ports, it checks with the operating system for an available
  port, and opens an endpoint for that port. Incoming connections must
  specify that port number to connect. Since the port number can change
  each time SQL Server starts, SQL Server provides the SQL Server
  Browser Service, to monitor the ports, and direct incoming connections
  to the current port for that instance. Using dynamic ports complicates
  connecting SQL Server through a firewall because the port number may
  change when SQL Server is restarted, requiring changes to the firewall
  settings. To avoid connection problems through a firewall, configure
  SQL Server to use a static port.

The SQL Server error log will also include messages indicating the listening interface IP addresses and ports.

EDIT:

As mentioned by @TiborKaraszi, SQL Server remembers that last used dynamic port and will continue to use that as the going forward unless there's a conflict, in which case a new one is used.


(Some of this is off-topic.)

Fast ALTER

The handling of ADD/DROP INDEX is different for MyISAM and InnoDB.
Oracle is focusing only on InnoDB.
InnoDB can allow writes while an index is being built -- by keeping track of what needs to be added to the index.  It already has a "Change Buffer" to optimize such.  MyISAM would have to invent a similar mechanism, and has no 'transaction' hooks to help.

Therefore, I conclude that all(?) benefits from ALTER TABLE ... INLINE/INPLACE are handled inside the InnoDB engine, leaving MyISAM out in the cold.

Long ago, one flavor of online ALTER TABLE, namely adding a item to an ENUM (with restrictions), was implemented.  But that only required modifying the .frm file.

OPTIMIZE

In one situation in a hundred, OPTIMIZE TABLE is useful in MyISAM.  The need for OPTIMIZE in InnoDB is much less frequent.

One example (applies to MyISAM only):  The table has a lot of churn -- either from UPDATEs that change the lengths of rows or from DELETE+INSERT.  MyISAM's INSERT gives preference to filling in holes in the .MYD, which can lead to individual rows scattered across multiple disk blocks.  This slows down SELECT, even for 'point queries'.

Time Series

Are you are deleting "old" rows and adding "new" rows?  If so,

Your MyISAM may need OPTIMIZE, as explained above.  But, with InnoDB, OPTIMIZE would be mostly a waste.
DELETEing 40M rows locks a MyISAM table for a long time.
There are other techniques to deal with big deletes.

Partitioning for time series

PARTITIONing, even with MyISAM (before MySQL 8.0), is possible.  It might let you turn the time consuming DELETE into an instantaneous DROP PARTITION.  More
Using PARTITIONing would probably obviate the need for OPTIMIZE, even for MyISAM.
Caveat:  Usually when adding partitioning, all the indexes need rethinking.


Turns out I was connecting to a Load Balancer which redirects me to either one of 2 sql servers which are synchronized ( for better performance) the SQLServer Agent was turned on on one of the servers but it was off on the other server. I needed to create my job on the main server (which has the agent service running). I didn't know we had 2 SQL servers that were identical and that I was connecting to a different one every other time. Sorry about that, but thanks again for the helpful comments, I found some interesting things in the error logs that helped me figure this all out. Thanks again!


As seen in the PDF, there's no clear winner when we compare MySql performance between different Kernel.

Since all databases are different, you will have to do benchmark to be sure. I think the hardware spec will affect the performance more than the OS.


Configure mongo for one shot full db read

Starting in MongoDB 3.4, the default WiredTiger internal cache size is the larger of either:

50% of (RAM - 1 GB),
or 256 MB.

For example, In your case from the 8GB of RAM the WiredTiger cache will use 3.5GB of RAM (0.5 * (8 GB - 1 GB) = 3.5 GB).

This can be reduced to for example to 1GB by using the following admin command only when the mongorestore operation happens.

db.adminCommand( { "setParameter": 1, "wiredTigerEngineRuntimeConfig": "cache_size=1G"})

and revert this back to 3.5 GB once the restoration is completed.

Note:

By reducing the wired tiger cache size will increase the time to restore the data.


Finding two most recent payments for every sale

A nice windowing function will do the job.

with phillovesCTEs as (
  select ARTRANSID, SALENO, PAIDDATE, DUEDATE, ACCTNO,
         ROW_NUMBER() over (
                            group by SALENO
                            order by PAIDDATE desc
                           ) as RN
  from ARTRANS
)
select *
from phillovesCTEs
where RN <=2;


When upgrading config servers to use WireTiger, the running config server with wiredtiger does not authenticate the existing pass and user?

It looks like the current MongoDB 3.0 upgrade instructions are missing mention of two important parameters for backing up and restoring users and roles:

mongodump --dumpDbUsersAndRoles (see also: Required Access to Backup User Data).
mongorestore --restoreDbUsersAndRoles (see also: Required Access to Restore User Data)

I can think of several approaches to fix:

If you don't have many user accounts on the config servers, recreate the administrator & user accounts. This isn't ideal, but is probably the fastest approach.
Export the users from your mmap database. This is more involved, but saves you recreating the users & roles. I've described steps for this below.
Redo the config server migration with the user & role information included. I expect this is the least desirable option.

Exporting the users

Assuming you have already upgraded all of your config servers to WiredTiger, here are some steps to add the user information:

Disable the balancer
Stop the last config server listed in your mongos' configDB setting (will call that config3 for the purpose of these steps). This will ensure your sharded cluster metadata remains read-only for the following steps.
Re-start config2 using the mmap data directory

At this stage you should have:

config1 (running WiredTiger)
config2 (running mmap with user/role data)
config3 (stopped)

Export the data from config2:

  mongodump --db config --dumpDbUsersAndRoles --username .. --password ..

Add any other parameters needed, eg --authenticationDatabase .. if you need to auth against another database.
If you have users in the admin database on your config server, you will also want to dump that as well.
(optional) Remove files from your dump except for the user/role information. If you are certain nothing has changed since you did the original migration from mmap to WiredTiger you could skip this step, however it would be safer to not overwrite any existing data.

Preview the files to remove:

  find ./dump -type f -not -name "\$admin.system*"

WARNING: removing files, make sure you have previewed to confirm:

  find ./dump -type f -not -name "\$admin.system*" | xargs rm

Re-start config2 using the wiredTiger storage engine
Run: mongorestore --db config --restoreDbUsersAndRoles dump/config/

You should see messages about restoring users & roles, for example:

  2015-03-18T02:41:34.887+1100  restoring users from dump/config/$admin.system.users.bson

  2015-03-18T02:41:34.887+1100  restoring roles from dump/config/$admin.system.roles.bson

Login to config2 and confirm the users are correctly setup (i.e. auth with admin account, use db.getUsers() to check).

At this stage you should have:

config1 (running WiredTiger)
config2 (running WiredTiger with user/role data)
config3 (stopped)

Copy the dump directory to config1 and repeat the mongorestore step.
Shutdown config2 (to keep the sharded cluster metadata readonly for the next step).

At this stage you should have:

config1 (running WiredTiger with user/role data)
config2 (stopped)
config3 (stopped)

Start config3. Copy the dump directory to config3, and repeat the mongorestore step.

At this stage you should have:

config1 (running WiredTiger with user/role data)
config2 (stopped)
config3 (running WiredTiger with user/role data)

Start config2. At this point all config servers should be online with the user information.
Re-enable the balancer so normal balancing activity & chunk migration can resume.


Here is an example using a calendar table (which you really should have). This example just populates 2014 but you can stuff it with as many years as you like...

CREATE TABLE dbo.Calendar(d DATE PRIMARY KEY);

INSERT dbo.Calendar(d) SELECT TOP (365)
 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, '20140101')
 FROM [master].dbo.spt_values
 WHERE [type] = N'P' ORDER BY number;

Now the query is simple:

DECLARE @s DATE = '20140518', @e DATE = '20140527';

SELECT c.d, Frequency = COALESCE(s.Frequency,0)
  FROM dbo.Calendar AS c
  LEFT OUTER JOIN dbo.splunge AS s
  ON c.d = s.[date]
  WHERE c.d >= @s
    AND c.d < DATEADD(DAY, 1, @e);

SQLfiddle example

If you can't create a calendar table (and don't have a numbers table handy either), then you can just put it inline:

DECLARE @s DATE = '20140518', @e DATE = '20140527';

SELECT c.d, Frequency = COALESCE(s.Frequency,0)
  FROM
(
   SELECT TOP (DATEDIFF(DAY, @s, @e)+1)
 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY number)-1, @s)
 FROM [master].dbo.spt_values
 WHERE [type] = N'P' ORDER BY number
) AS c(d)
  LEFT OUTER JOIN dbo.splunge2 AS s
  ON c.d = s.[date]
  WHERE c.d >= @s
    AND c.d < DATEADD(DAY, 1, @e);

SQLfiddle example

For more on generating sets (of dates, numbers, etc) see this series:

http://sqlperformance.com/generate-a-set-1
http://sqlperformance.com/generate-a-set-2
http://sqlperformance.com/generate-a-set-3


Help with Recursive CTE query with INSERT, ON CONFLICT and RETURNING

You don't need a recursive CTE, you can use nextval() in the result from the unnest() directly and then use lag() to get the value from the previous row. To deal with existing rows, I would do this using a join between the result of the unnest() and the path table, detecting new and existing rows.

For the new rows, a new ID will be generated and only those will be inserted:

insert into path (id, name, parent_id)
select id, name, parent_id
from (
  select *,
         case
           when is_new
             then lag(id) over (order by level)
             else existing_parent
         end as parent_id
  from (
    SELECT case
              when p.id is null then nextval('path_id_seq')
              else p.id
           end as id,
           x.name, x.level, p.parent_id as existing_parent,
           p.id is null as is_new
    FROM unnest(string_to_array('Assets/images/232.png', '/')) with ordinality as x(name, level)
      LEFT JOIN path p on p.name = x.name
    order by level
  ) t1
) t2
where is_new;

Live example: http://rextester.com/YGMT19565

The only "drawback" if you will is that the sequence values are not necessarily assigned in the "order" you'd expect. But as the only purpose of a generated primary key is to be unique (and nothing else) this shouldn't be a problem.


What do these statements mean in the MS β exam 70-461 "skills measured" list?

DML - refers to data manipulation (update, insert, delete). A DML trigger is a trigger created to happen on update, insert or delete of table data.

The update function is the part of a DML trigger where you test if a required update has occured.

For Example:

CREATE TRIGGER myupdate_trigger
ON mytable
FOR UPDATE AS
if UPDATE(column_1)
BEGIN
-- Do something
END

This will trigger a further action every time column column_1 is updated in table mytable. The update function is UPDATE(column_1).


Why are my statistics getting out of date so quickly?

Time spent automatically updating statistics is included in compile time.  The stats update takes 10 minutes, and the compile time for a query that triggers an auto stats update includes that time - which is why the query takes over 10 minutes every now and then.

The modification threshold for an automatic stats update, per the documentation, is

sqrt(table cardinality * 1000)

With your numbers (300,000,000 rows), you hit that threshold at 547,722 modifications.

Since 1,000,000 rows are being added every day, you're bound to hit an auto stats update around twice a day.

Since your query's estimates are good before and after the stats update, one possible solution would be to enable the database level AUTO_UPDATE_STATISTICS_ASYNC setting.  This still let's SQL Server update stats automatically, but it's done asynchronously (so queries don't have to wait on it to happen):

ALTER DATABASE [Live]
SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Note that this affects the entire database, not just that table / statistic.


Oracle 11.1.0 Autobackup going to wrong location

Figured out the problem. Simply changing the Autobackup Format back to the default '%F' is not viewed as the default. In order to truly have the default value you must use the clear command.

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

RMAN behaivor returned to normal. Thanks to all who viewed my question!


Get all of the rows involved between tables relationships to delete them

According to your table schema where there is a rule ON DELETE CASCADE in FK_AggregationChildren_Aggregation foreign key, you can delete only affected records of 'Aggregation' table.

Use the same recursive query to fetch rows but adding AggregationID of childrens, then delete distinct AggregationId codes.

DECLARE @Serial VARCHAR(20) = 'Code1';

;WITH rc AS
(
    SELECT AggregationId Id, null as Code
    FROM   dbo.Aggregation
    WHERE  AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial)
    UNION ALL
    SELECT ac.AggregationChildrenId Id, AggregationID as Code
    FROM   dbo.AggregationChildren ac
    JOIN   rc
    ON     ac.AggregationId = rc.Id
)
DELETE FROM dbo.Aggregation
WHERE AggregationID IN (SELECT DISTINCT Code FROM rc);

This is the result:

SELECT * FROM dbo.Aggregation;
GO

| AggregationId |
| ------------: |
|             9 |

SELECT * FROM dbo.AggregationChildren;
GO

AggregationChildrenId | AggregationId | Position
--------------------: | ------------: | -------:
                   10 |             9 |        1
                   11 |             9 |        2

db<>fiddle here


SQL Server : Is it possible to have a linked server link to another linked server?

I found my answer at MS SQL Server Forum: SQL Server 2005 X64 Linked Server error

Turnes out I had "Level zero only" checked

So, it goes something like this: x64 Query > Linked Server to x86 > View > Linked Server to ADS
So, something like this now works...

SELECT * FROM OPENQUERY([HAL2011\SQLEXPRESS], 'SELECT PartNum, OnHand FROM [master].[dbo].[vHPQoH]')

Where [HAL2011\SQLEXPRESS] is the x86 Server with a view [vHPQoH] to a lined server pointing to the Sybase ADS.

But thanks to everyone for your help.  I still learned a lot!  This place is great!


Distributed Availability Group Failover Issue

I believe you should run the following command on the primary of the secondary group.

ALTER AVAILABILITY GROUP <AG name> FORCE_FAILOVER_ALLOW_DATA_LOSS;


You can do this all in one statement pretty easily.

SELECT string_agg(statement, E'\nUNION ALL ') || ';'
FROM (
  SELECT FORMAT(
    $$SELECT '%s' AS table_catalog, '%s' AS table_schema, '%s' AS table_name, id FROM %I.%I.%I WHERE %I BETWEEN 1 AND 1000$$,
    table_catalog,
    table_schema,
    table_name,
    table_catalog,
    table_schema,
    table_name,
    column_name
  )
  FROM information_schema.columns
  WHERE
    table_catalog = CURRENT_CATALOG
    AND table_schema = 'public'
    AND column_name = 'id'
) AS t(statement);

That will generate a single statement. In psql, just run \gexec after that.


is it possible to use utl_file_dir = '*' scope = spfile in 12c

Deprecated does not mean it can not be used, so yes, you can use it:

[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Sep 26 15:29:05 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter system set utl_file_dir='*' scope=spfile;

System altered.

SQL>

But keep in mind, it is desupported starting with 18c: Desupport of UTL_FILE_DIR Initialization Parameter, the parameter does not exist anymore.

[oracle@o73 ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Sep 26 15:28:50 2018
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> alter system set utl_file_dir='*' scope=spfile;
alter system set utl_file_dir='*' scope=spfile
                                  *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

12.2 example:

[oracle@o71 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Sep 27 11:01:50 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      *

SQL> !ls -l /home/oracle/hello.txt
ls: cannot access /home/oracle/hello.txt: No such file or directory
SQL> DECLARE
  L_HANDLER UTL_FILE.FILE_TYPE;
BEGIN
  L_HANDLER := UTL_FILE.FOPEN('/home/oracle', 'hello.txt', 'W');
  UTL_FILE.PUTF(L_HANDLER, 'Hello World!');
  UTL_FILE.FCLOSE(L_HANDLER);
END;
/  2    3    4    5    6    7    8

PL/SQL procedure successfully completed.

SQL> !cat /home/oracle/hello.txt
Hello World!


Single table or multiple tables for custom transfer queue

There are a lot of options when it comes to data synchronization in SQL and there is no single best option. It all depends on what are the priorities for the solution, what is the workload, maintainability and so on.. there are also build in features like SQL Server Replication that you could use.

If you are concerned with efficiency of your solution, then I would suggest to perform tests to see how many transactions are you getting and is it a satisfactory result.
You have to bear in mind that triggers are not the fastest things in the world, so if this becomes a bottleneck - you might want to look for other solutions.

An option for replacing triggers might be adding a timestamp or last insert/update datetime column in your data that you would reference in the synchronization procedure processing only the ones changed since last sync would improve performance. This would mean you would have to synchronize whole rows unless you implemented some more advanced logic.

You should also design your conflict handling process in case the same information gets updated on both servers between synchronizations.


Difference between MongoDB's find and findone calls

Based on my own benchmarks, find().limit(1) is orders of magnitude faster than findOne().

There is either an error in the MongoDB documentation or a bug in findOne(). findOne() performs more like find().limit(N) where N is the number of documents the query would return. I figured this out while trying to figure out why my simple queries were so slow!

update: response from a 10gen (MongoDB) engineer:

  The two queries you are executing are very different. A find query
  returns a cursor, this is essentially a no-operation scenario, as no
  actual data is returned (only the cursor information). If you call
  findOne, then you are actually returning the data and closing the
  cursor. The docs should definitely be clearer :-)

Update: Indeed, if the find().limit(1) document is retrieved, the orders of magnitude speed difference seems to disappear. Also, I could not reproduce the major speed difference with the MongoDB JavaScript driver. I originally benchmarked using the MongoDB Java driver.


select data from table which dont have row in another table

What you want is called "anti-join" (or "anti-semijoin"). There are 3 main ways to do this kind of query in MySQL (and some more in other DBMS, that have implemented EXCEPT operator):

NOT IN subquery (no need for DISTINCT here but you may need to add the check AND pov.product_id IS NOT NULL if that column is nullable):

select p.id
from product as p
where p.id not in
  ( select pov.product_id
    from product_has_option_value as pov
    where pov.option_value_id = 543
      and pov.product_id is not null
  ) ;

LEFT JOIN with IS NULL check:

select p.id
from product as p
    left join  product_has_option_value as pov
      on  pov.option_value_id = 543
      and pov.product_id = p.id
where pov.product_id is null ;

NOT EXISTS correlated subquery:

select p.id
from product as p
where not exists
  ( select 1
    from product_has_option_value as pov
    where pov.option_value_id = 543
      and pov.product_id = p.id
  ) ;


Better way of handling incorrect date format in a column with "char(10)" data type / TRY_CONVERT equivalent in PLSQL

TRY_CONVERT is a function that is called for every record, how is that different?
Below works since 12.2:
SQL> alter session set nls_date_format='YYYY/MM/DD';

Session altered.

SQL> select cast('2021/03/07' as date default date'2012-01-01' on conversion error) from dual;

CAST('2021
----------
2021/03/07

SQL> select cast('X021/zz/99' as date default date'2012-01-01' on conversion error) from dual;

CAST('2021
----------
2012/01/01

SQL> select to_date('X021/zz/99' default date'2012-01-01' on conversion error, 'YYYY/MM/DD') from dual;

TO_DATE('X
----------
2012/01/01

SQL>

On your version, you can use your function.


Efficient configuration for a purely temporary, small Oracle installation

You can either "drop user test_user cascade;", then create the user again with all needed grants and objects, or drop the database and restore a backup of the database while it is empty. Depending on the number of tables, indexes and constraints one of the two methods will be faster. The more tables, indexes and constraints, the more likely just dropping the database and restoring a backup of the empty database will be faster.


Referencing the post Backup to multiple files for faster and smaller SQL Server files

Problem

Have you ever wished you could get your backups to run faster? Well there may be a way, by writing to multiple files. Creating SQL Server backups is pretty simple to do; you can use the SQL Server management tools or you can use T-SQL commands to issue the backup. But sometimes with large databases it takes a long time to run and it is difficult to copy this very large file across the network or even to your backup tapes.

Solution

Write your database backup to multiple files. In addition to writing your database backup to one file you have the ability to write to multiple files at the same time and therefore split up the workload. The advantage to doing this is that the backup process can run using multiple threads and therefore finish faster as well as having much smaller files that can be moved across the network or copied to a CD or DVD. Another advantage to writing to multiple files is if you have multiple disk arrays you can write your backup files to different arrays and therefore get better I/O throughput. One thing to note is that to maintain the I/O throughput you should keep the writing of your backup files and the reading of the database file on different disk arrays.

Backup to multiple files using T-SQL

BACKUP DATABASE [Northwind] TO
DISK = 'C:\Northwind_file1.bak',
DISK = 'D:\Northwind_file2.bak',
DISK = 'E:\Northwind_file3.bak',
DISK = 'F:\Northwind_file4.bak'
WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT

A 200gb database is not really that large and (in my opinion) shouldn't take anywhere near 20 hours.  I would also do some testing on your backup target latency without even dealing with SQL Server.


The Transactional replication is not a "DR Solution" you may face a lot of issues with this, (Not all tables replicated, Different data in each DB) even that it worked in the past, There's no warranty it will work in the present. The suggestion will be using some supported tool like log shipping or always on.

Even with that if you decided to go with this, as @Queue Mann said, you will need to point everything to the new server, There's no automated option on this, (but there IS in Always on)
For your questions: A) There's no "official" fail over, you just use the replicated DB and point all your applications to the DB.

B) In the case of fail over you will need to set up everything again (In Always on , you won't as long it was done in Synchronous mode).

To be honest I don't know why keep using replication instead of the official DR solutions, you will save a lot of configuration (and troubles) going in the right way.


How can I search for all points with a specific range (radius) of another point?

Can the queries themselves be improved?

...
dbo.myPOI2 ON myTable.geog.STDistance(myPOI2.geog) <= POI2.Point_Radius

Yes. That's going to be slow. You could massively improve that query by buffering the point and finding the intersects:

ON myTable.geog.STIntersects(myPOI.geog.STBuffer(PP.Point_Radius)) = 1

You can make this even better in your case because the radius is provided on the row, rather than in the query. You can store the geometery returned by STBuffer on the table instead of the point. If you ever want to to find the center point (which you'll probably never need), you can use the GIS function STCentroid.

Other implementations

As a side note, this type of query is done with ST_DWithin in PostGIS and will run on the index.


Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB?

Percona's Xtradb has some improvements -- mostly in scalability.  They changed the Mutexes, which held back effective use of multiple cores.


How well, if at all, does Oracle NoSQL database integrate with Oracle RDBMS?

Oracle NoSQL Database integrates well with the Oracle database.  You can run an SQL query in Oracle Database that can reference data in the Oracle database as well as NoSQL Database.

Please see the Oracle NoSQL Database documentation for more details on external tables support.

You can also get an overview of the latest release here:
http://www.youtube.com/watch?v=VT2n1QhIprQ

I'd suggest that you also post your questions relating to Oracle NoSQL Database to the OTN forum: https://forums.oracle.com/forums/forum.jspa?forumID=1388.  We monitor the forums regularly...

Happy Holidays.

Ashok Joshi
Senior Director, Oracle NoSQL Database development


create table t1 (x varchar2(2));

insert into t1 (x) values ('1');
insert into t1 (x) values ('2');
insert into t1 (x) values ('3');
insert into t1 (x) values ('5');
insert into t1 (x) values ('6');
insert into t1 (x) values ('8');
insert into t1 (x) values ('9');
insert into t1 (x) values ('10');
insert into t1 (x) values ('11');
commit;

SQL>  select level as x
  2     from dual
  3  connect by level <= 9
  4    minus
  5   select to_number(x)
  6     from t1;

         X
----------
         4
         7

2 rows selected.


Error when modifying Notify Operator task in maintenance plan

From the message you posted I can see MSDB is not set for Mail Host Status.

  Database.IsMailHost Property -- Gets a value that specifies whether
  the database is configured as a mail host.

To find whether the Database is a Mail Host or not, use the below PowerShell command. I tested it on SQL Server 2014.

Use the below code in Powershell ISE to check: (For Default Instance Only)

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("MSDB")
Write-Host "Database is a mail host :" $db.IsMailHost

The MSDB database is the Database Mail host database. This database contains the stored procedures and messaging objects for Database Mail.

By default, Database Mail is not active.

You must explicitly enable Database Mail by using either

The Database Mail Configuration Wizard

or

The Database Mail XPs Option of the sp_configure system stored procedure.

The following example enables the Database Mail extended stored procedures.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO


The way I would do this is with a Derived Column Transformation.  Take a look at this example (hitting the sample database, AdventureWorks2012):

As you can see on this screenshot, what I'm doing is taking the Name column from AdventureWorks2012.HumanResources.Department (this would work with your version of SQL Server/SSIS as well, I believe.  Although you would know pretty quickly if it doesn't) and using a conditional statement for my Expression:

Name == "Sales" ? "New Sales Name" : Name

What that is, is a short-hand conditional statement.  It takes the Boolean result of Name == "Sales".  If that's true (i.e. the Name is "Sales") then return "New Sales Name", or whatever string you'd want to substitute.  If that's false (the Name is not "Sales") then just return the Name as it originally was.


View SQL Code Behind Flat File Import Wizard?

The "Import Flat File to SQL Wizard" is actually just an abbreviated version of the more complex "Import & Export Wizard." It uses the exact same mechanism, but streamlines the process by skipping unnecessary options & picking appropriate defaults for you. Ultimately, the import process uses the same mechanism though.
One of the "hidden" options that is only available in the Import & Export Wizard, is to generate an SSIS Package, rather than executing immediately. The import actually happens via SSIS as a mechanism, rather than pure T-SQL or PowerShell code. Because it is not executing T-SQL or PowerShell directly, it can only output the thing it generates & runs behind the scenes--an SSIS Package.
To access the Import & Export Wizard, right-click on a database, and select `Tasks-->Import Data..." (this is right below "Import Flat File..." on the context menu). Here, you can choose a flat file source, and SQL Server destination.
On the last page of the Import & Export Wizard, you can select to Save as an SSIS Package, or run immediately. If you save as an SSIS package, you will be able to see exactly what SQL Server is doing.


Imagine the following.

On 2019-08-19 18:00:00 you started a full online database backup that took 5 minutes. This backup included all the datafiles listed above.

Backuppieces created from this operation:

arch1.bkp - backup of archivelogs that existed before starting the full database backup + the last archivelog that was created as a result of the implicit logswitch
db1.bkp - backup of datafiles
arch2.bkp - backup of archivelogs that were created during the full database backup + the last archivelog that was created as a result of the implicit logswitch
c1.bkp - controlfile autobackup created at the end of the backup process

Then at 2019-08-19 18:10:00 someone dropped the TRISCTALTB tablespace.
At 2019-08-19 18:15:00 a scheduled archivelog backup was started, which took 1 minute, and created the following backuppieces:

arch3.bkp - backup of archivelogs created since the last backup + the last archivelog that was created as a result of the implicit logswitch
c2.bkp - controlfile autobackup created at the end of the backup process

At 2019-08-19 18:20:00 you realize that TRISCALTB tablespace is missing. You check the alert log, and see the tablespace was dropped at 2019-08-19 18:10:00. You decide to do an incomplete database recovery and specify the point of time as 2019-08-19 18:09:59, right before the  tablespace was dropped.

You then stop the instance, delete all files, check the backups, start the instance in nomount mode, and restore the controlfile from the latest controlfile backup: c2.bkp. The problem is, that controlfile backup was taken after the TRISCALTB was dropped, so there is no datafile 13 in the list of datafiles.

You perform the restore and recovery until 2019-08-19 18:09:59. This restores all datafiles except datafile 13 from db1.bkp, restore and applies the archivelogs from arch2.bkp and arch3.bkp. When the recovery is complete, you then open the database with alter database open resetlogs;.

That is the point where such datafile entries, with MISSING in their name are created. Datafile 13 does not exist in the restored controlfile. Datafile 13 exists in the dictionary in SYSTEM tablespace at the point of time it was restored and recovered to. When you open the database, it recognizes the mismatch between the controlfile and the dictionary, and creates an offline datafile entry in the controlfile with the name of MISSING and the datafile number (00013) under the default location ($ORACLE_HOME/dbs). No, RMAN did not restore datafile 13 at all, it is not in the filesystem, datafile 13 is just a controlfile entry at this point.

Even though you had no errors during the restore and recovery, it is not yet complete. If you see these MISSING datafiles, you need to restore and recover them.

At this point you can do the following in RMAN:

Restore the datafile:

run
{
  set newname for datafile 13 to '+DATA';
  restore datafile 13;
}

Switch to the restored copy:

switch datafile 13 to copy;

Recover the restored datafile:

recover datafile 13;

Finally bring it online:

alter database datafile 13 online;


Oracle: Resetting an identity column vs resetting a sequence

Given that identity column internally uses a sequence, why is it not possible to do the same with a sequence i.e. change its start value to a specific number with a single alter statement?

Oracle Official way of resetting a sequence is dropping and recreating the sequence, of course, you need to take care about the privileges granted.

  You can also use this statement to restart a sequence by dropping and then re-creating it. For example, if you have a sequence with a current value of 150 and you would like to restart the sequence with a value of 27, then you can drop the sequence and then re-create it with the same name and a START WITH value of 27.

Oracle 12c Docs

However, there is an undocumented way to reset the sequence which works for Oracle 12c.

SQL> select serial.nextval from dual;

   NEXTVAL
----------
         4

SQL> alter sequence serial restart start with 1;

Sequence altered.

SQL>  select serial.nextval from dual;

   NEXTVAL
----------
         1


JDBC Connection on Current Primary Database After Switchover: ORA-01033 encountered

And that is why we use role-based services instead of the default service derived from instance_name and db_unique_name.

If your primary database is called PROD_DB (db_unique_name), there will be  service with that name at the primary site, and that service will be available even after a failover or switchover. The listener receives your request, sees that you are requesting to connect using the service PROD_DB, and forwards your request to the instance(s) that provide that service. The instance is in MOUNT stage, and you receive ORA-01033.

Create the role based service at both sites (replace -db and -service values with your current ones):

srvctl add service -db ORCL -service myservice -role primary
srvctl start service -db ORCL -service myservice -role primary

The start step will fail at the standby site, and the service remains in INTERMEDIATE state, but that is normal.

With this, the service is automatically relocated (stopped at old primary and started at new primary) when you perform a role transition with Data Guard broker.

And use this service for connecting:

(DESCRIPTION_LIST=
    (LOAD_BALANCE=off)
    (FAILOVER=on)

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=PROD_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice))
    )

    (DESCRIPTION=
        (ADDRESS_LIST=
            (LOAD_BALANCE=on)
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_1)(PORT=1521))
            (ADDRESS=(PROTOCOL=tcp)(HOST=DR_DB_2)(PORT=1521))
        )
        (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice))
    )

)

When you try to connect using the service myservice, and your client tries the first entry (old primary, currently standby), the listener refuses the connection as there will be no myservice service running at that site, and your client tries the next available address.


When a transaction truncate a global temporary table, is that logged in transaction log or error log?

You mean a ##temp table? It isn't logged in error log. But it must be logged in transaction log since you can rollback a truncate table command.


How can we automatically replace a table with a view for a specific user?

You can check these things in sys.dm_exec_sessions, but you have to do it in your (let's hope) stored procedures (you can't redirect a query that specifies a specific table name to a different table based on these variables):

ALTER PROCEDURE dbo.report
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS
  (
    SELECT 1 FROM sys.dm_exec_sessions
      WHERE program_name = 'program'
      AND login_name = 'user'
      AND session_id = @@SPID
  )
  BEGIN
    -- do it their way
    RETURN;
  END

-- do it the normal way
END
GO

This is a bad solution though. If one user wants custom ordering, what happens when another user decides they, too, want different ordering? It will happen. Now all your reports are going to have three forks? Why not make ORDER BY a part of the interface?

I'm wondering if you could do something here with schemas, for example if this guy has a different default schema you could write the query such that it doesn't reference a schema. Everyone else will get dbo.viewname and he would get newschema.viewname. But this is messy, the opposite of self-documenting, and if you're going to write code that handles his case, you may as well fix the code the right way.

Plus, as I suggested in my comment, simply referencing a different view or table name doesn't guarantee you'll get a different ordering by doing so. SQL Server still only has to obey a specific order if you've actually stated one in the outer ORDER BY.


Replicate SQL Data Inside Firewall to Outside Firewall - Suggestions

The method I'm familiar with would be to to only have an internal database. The firewall separating your internal and DMZ networks can be configured to only allow appropriate communications through, in your case 1443 (or your SQL port) between the web server and database.

However, you can't simply rely on your firewall for security. Proper SQL permissions would be necessary to only allow access to your web data. And of course you should be validating user input to protect against SQL injection.

If you do want to go the replication route, check out the MS offerings such as SQL Server Transactional Replication.


mysql - ALTER IGNORE ADD INDEX removed in mysql 5.7.4 - What to use instead?

This may help:

Rename the existing table and create a new table.
Use insert ignore to copy data from old table to new table.


Optimizer failing to load statistic when creating plan in SQL Server 2017 "Calculator failed. Replanning."

For a calculator to fail does not necessarily mean that loading of stats failed.

See Joe Sack's post with the query_optimizer_estimate_cardinality event indicating CSelCalcColumnInInterval CalculatorFailed:

The CSelCalcAscendingKeyFilter Calculator

Regarding the default for any values not in the histogram, Itzik Ben-Gan has a great summary in Seek and You Shall Scan Part II: Ascending Keys, but focuses on numeric data types.


Execute UNION and after LEFT JOIN in MySQL

You are on the right way but just messed up with the syntax.

SELECT t1.*
  FROM ( SELECT CLAVE_ART
              , DESCRIPCION_ART
              , EXISTENCIA_ART
              , MARCA_ART
              , exi_num_alm
           FROM cdc_vw_Marbetes_Catalogo_Exis
          WHERE EXISTENCIA_ART > 0
            AND exi_num_alm = 25

          UNION

         SELECT clave
              , descripcion
              , existencia
              , marca
              , almacen
           FROM tblMarbetesAdicionales
          WHERE almacen = 25
       ) AS t1
  LEFT JOIN tblmarbetescongeladas AS t2 ON t1.CLAVE_ART = t2.clave
 WHERE t2.clave IS NULL
;


SQL Server High Availability restore copy_only backups from secondary replica problem

First things first, get rid of the differential backup. If you are performing full, copy only backups off of a secondary these will do nothing for you.

The differential backup works to backup changes from a full backup (not copy only). When a normal full backup runs it sets all of the pages in the database as being backed up. Going forward from that point any change to a page gets marked, and then the differential would come along and backup any page marked as changed. As time goes along the differential will get larger and larger. A copy only backup does not reset those flags, so things will just get worse.

At this point decide how you want to proceed with your backup strategy. If you want to continue to take differential backups, then also take full backups on your primary. If you no longer want to take the differentials for the database that are in the AG then set the copy_only flag to N in the Ola script. This will allow the differential job to run and take backups of databases not in the AG, but will ignore those that are in the AG (so long as the backup preference marks a secondary replica as the machine to backup from).

As regards the benefit of offloading backups to a secondary, this can help reduce resource contention on your primary replica, which is where most of your traffic can be. It can also be useful as a way to take database backups in a DR site, where you may have a secondary replica or two.


Is the subscription of merge replication only for SQL Server Compact Edition?

This only means that if you are using SQL Server Compact edition, that this edition can only be a subscriber, not a publisher.

Here is more information on setting up merge replication between Microsoft SQL Server and SQL Server Compact 3.5.

Here is an example of a different subscriber than SQL Server Compact edition, a blogpost which sets up a Merge replication using SQL Server 2012 as the publisher and SQL Server 2008 R2 as the subscriber:

Merge Replication Step By Step


What I am about to say would be rather crazy but entirely possible. Here it goes...

CRAZY SUGGESTION #1

Download the source code for the mysql client
Remove the commands

DESC "table"
SHOW TABLES
SHOW CREATE TABLE "table"

Compile the source
Give your developers access to this special compiled version of the mysql client

CRAZY SUGGESTION #2

Download MySQL Proxy
Program the LUA scripts to ignore

DESC "table"
SHOW TABLES
SHOW CREATE TABLE "table"

Have your developers access DB using MySQL-Proxy-based IP Addresses

EPILOGUE

I would say Give it a Try, but I have never done anything like this before.

Notwithstanding, it would be a brave C/C++ or LUA soul indeed to engage in such an undertaking...


Take a look at using the EXCEPT clause.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-ver15

SELECT WAREHOUSE, PRODUCT, MODEL, QUANTITY FROM Table1
EXCEPT
SELECT WAREHOUSE, PRODUCT, MODEL, QUANTITY FROM Table2


Give alias / column definition list to ROWS FROM()

You need a table alias too
SELECT T.u[1], t.l[1]
FROM ROWS FROM (
  regexp_matches('fooBarBaz', '[[:upper:]]', 'g'),
  regexp_matches('fooBarBaz', '[[:lower:]]', 'g')
) AS t (u, l);


MySQL InnoDB + high table creation-destruction rate

What kind of memory and HD space leak problems can this structure give
  us in MySQL-InnoDB?

Given your description it's impossible to give any meaningful value to memory or storage requirements.  You say "16 million per year" but what is that?  16 millions rows/tables?  With no schema example it's still impossible to estimate.   For tables you do have you can run

select table_name, data_length, index_length, data_free from information_schema.tables where table_schema='mydatabase';

To get a feel for current storage requirements.   What you'll be more interested in is growth rate over time though to ensure you're not going to run out of space in 3months down the road.  MySQL doesn't give a built in growth rate metric so that's something you'll need to periodically poll and record yourself.

For memory requirements, in a perfect world your innodb_buffer_pool would be big enough to accommodate all your data and indexes in memory.  It's never a perfect world though and can become impractical or cost prohibitive to have such a machine.  At the same time, strive to make the buffer pool as large as you can to keep most frequently accessed data in memory.

If you are straight up dropping tables the storage space will be reclaimed to the file system if and only if you are configured with innodb_file_per_table = true.  Other wise all your data is stored along with metadata and transaction history in ibdata1.

  Is this a good practice or should we consider a structure change?

It's not unheard of to offline old historical data.  However your description makes it seems like this becoming active is some what common.  I can't imagine any on demand process where you have to rebuild a table before being able to service the request.

Rebuilding a table and it's indexes is much slower then a raw copy of a file of similar size to the final table.

You say it's being "packed" and "unpacked" indicating it's still being stored somewhere.  If it's going to be needed to be accessible by an application just keep it in the database and let old pages fall out of the buffer pool.  When requested again the information needs to be read from disk versus having a table completely rebuilt.


Foreign Key NULL vs Foreign Key to an empty string

The answer lies in the ambiguity of your question:

However if I want a unique index on medicine name & brand_id ...

You do not actually want "a unique index". You want to enforce certain rules. A unique index is a means to an end; a tool. The question is: What exactly do you want?
Do you want that there can only be a single instance of a name with an unknown brand? Or can there be multiple instances? That depends on what "unknown" or "missing" or "empty" or NULL is supposed to mean for you.
The canonical meaning of NULL is "unknown". We simply do not know, what goes here, it could be anything, including nothing (empty ('') in string types, 0 in numeric types).
If you want to allow one generic brand, include an entry in brand, call it "generic" or "ACME" or whatever. I like to use the id value 0 for such a catch-all entry.
Then your unique index or constraint is doing what it's supposed to do: Allow only one instance of (name, brand_id) in the table medicine. But multiple entries with (name, NULL) are still possible. Set brand_id NOT NULL, too, if you don't want that.
The best solution also depends on typical queries and details of your setup. Be sure to document exactly what NULL / empty / 0 is supposed to mean and why you chose to design it like that. It may seem crystal clear at the moment you implement it, but may be confusing later.
I added another link to the answer you referenced:

PostgreSQL multi-column unique constraint and NULL values
Create unique constraint with null columns


How use raise notice in nested and sub-function?

Yes it is possible. I have tested the following with PG 12.2 and with psql CLI:

show client_min_messages;
client_min_messages
---------------------
notice
(1 row)
create or replace function f2 () returns text
as
$$
declare
begin
 raise notice 'Function 2 is now running';
 return 'f2: OK';
end;
$$
language plpgsql;
CREATE FUNCTION
create or replace function f1 () returns text
as
$$
declare
begin
 raise notice 'Function 1 executing';
 perform f2();
 return 'f1: OK';
end;
$$
language plpgsql;
CREATE FUNCTION
select f1();
psql:t2f.sql:27: NOTICE:  Function 1 executing
psql:t2f.sql:27: NOTICE:  Function 2 is now running
   f1
--------
 f1: OK
(1 row)


What happens as a database grows to the HDD's lmit?

When the hdd fills the DB could no longer write and it will crash.
But that is the easy thing to solve & monitor- whenever you reach 85% capacity just add more or clear old data.

The harder/more important part is to ensure performance is not impacted by the growth in data (more rows to scan)
Usually this is handled by partitions and cleanup processes which somewhat mitigate the first issue as well.

Regards
Jony


MySQL: JOIN empty tables with a constant value column, COUNT(*) returns a 0, but adding a GROUP BY on the constant value returns nothing?

It does not matter what you group by, try for example TableA.id. The reason is that MAX and COUNT returns a scalar even for the empty set (your first query). If you add a group by (no matter what attribute you use), you get one scalar per group. But since there are no groups, you get an empty set.

It may be illustrative to add an outer query that count from your second query:

SELECT COUNT(*)
FROM (
    SELECT 'Combined', COUNT(*), MAX(Timestamp)
    FROM TableA
    JOIN TableB USING (Id)
    GROUP BY 1
) AS T


Is there a way to have MySQL GROUP BY utilize trailing whitespace?

char values are space-padded when they are stored, so all trailing whitespace is assumed to be padding and removed when retrieving values.  varchar fields will retain trailing whitespace, but by default it is removed for comparisons.  (see The CHAR and VARCHAR types)

To retain the trailing whitespace for varchar values, use the binary operator.  Something like SELECT foo, CHAR_LENGTH(foo) FROM myTable
WHERE foo='bar'
GROUP BY BINARY foo should do what you want.


SSRS email Subscription Not Working but local smtp is working

It was seriously LOL.

I gave NetworkService user full control to mailroot folder residing in  C:\inetpub\ and Vola email was sent from localsmtp.

I bet if someone has faced this problem and has find any solution like same.

Reason OF Solution - login context/User that is rendering report on ReportServer and creating an email template to throw it in pickup directory residing in C:\inetpub\mailroot  should be "Network Service"

Question/Doubt Left
Now the confusion left is can we change this user as my service account user i.e. local Administrator was having  full control on  mailroot folder but it was not working till i opted above mentioned solution.


WITH
cte1 AS ( SELECT b.team, SUM(a.total) total
          FROM data1 a
          INNER JOIN team b ON a.teamID = b.teamID
          WHERE a.month = 1 and a.year = 2019
          GROUP by b.team ),
cte2 AS ( SELECT b.team, SUM(c.total) total
          FROM data2 c
          INNER JOIN team b ON c.teamID = b.teamID
          WHERE c.Date BETWEEN '2019-01-01' AND '2019-01-31'
          GROUP by b.team ),
SELECT COALESCE(cte1.team, cte2.team) team,
       COALESCE(cte1.total, 0) - COALESCE(cte2.total, 0) total
FROM cte1
FULL OUTER JOIN cte2 ON cte1.team = cte2.team
WHERE COALESCE(cte1.total, 0) != COALESCE(cte2.total, 0)

Of course it can be simplified - but it will decrease its clearness.


Try this.

--set up demo data
declare @T table (id int, name varchar(10), NumberId int)
insert into @T(id, name, NumberId) values
(1,'Abc',1),
(2,'bcd',1),
(3,'cde',1),
(4,'def',2),
(5,'efg',2),
(6,'fgh',2),
(7,'ghij',2),
(8,'defg',1),
(9,'efgj',1),
(10,'fghi',1),
(11,'ghij',1 )
;

I used a common table expression to create groupings of consecutive NumberId's

--Create a grouping for consecutive values called grp

;WITH _cte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY Numberid ORDER BY [id]
                ), [id]) AS grp
    FROM @T
    )
    select * from _cte

| id | name | NumberId | grp                     |
|----|------|----------|-------------------------|
| 1  | Abc  | 1        | 1900-01-01 00:00:00.000 |
| 2  | bcd  | 1        | 1900-01-01 00:00:00.000 |
| 3  | cde  | 1        | 1900-01-01 00:00:00.000 |
| 4  | def  | 2        | 1900-01-04 00:00:00.000 |
| 5  | efg  | 2        | 1900-01-04 00:00:00.000 |
| 6  | fgh  | 2        | 1900-01-04 00:00:00.000 |
| 7  | ghij | 2        | 1900-01-04 00:00:00.000 |
| 8  | defg | 1        | 1900-01-05 00:00:00.000 |
| 9  | efgj | 1        | 1900-01-05 00:00:00.000 |
| 10 | fghi | 1        | 1900-01-05 00:00:00.000 |
| 11 | ghij | 1        | 1900-01-05 00:00:00.000 |

Then, I used another common table expression to add a row_number to each row in the above result

;WITH _cte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY Numberid ORDER BY [id]
                ), [id]) AS grp
    FROM @T
    )
    select * from _cte order by id
    ,AddedRn    --add a row number for each entry in the group
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY grp ORDER BY id
            ) AS rn
    FROM _cte
    )

| id | name | NumberId | grp                     | rn |
|----|------|----------|-------------------------|----|
| 1  | Abc  | 1        | 1900-01-01 00:00:00.000 | 1  |
| 2  | bcd  | 1        | 1900-01-01 00:00:00.000 | 2  |
| 3  | cde  | 1        | 1900-01-01 00:00:00.000 | 3  |
| 4  | def  | 2        | 1900-01-04 00:00:00.000 | 1  |
| 5  | efg  | 2        | 1900-01-04 00:00:00.000 | 2  |
| 6  | fgh  | 2        | 1900-01-04 00:00:00.000 | 3  |
| 7  | ghij | 2        | 1900-01-04 00:00:00.000 | 4  |
| 8  | defg | 1        | 1900-01-05 00:00:00.000 | 1  |
| 9  | efgj | 1        | 1900-01-05 00:00:00.000 | 2  |
| 10 | fghi | 1        | 1900-01-05 00:00:00.000 | 3  |
| 11 | ghij | 1        | 1900-01-05 00:00:00.000 | 4  |

Now put it all together and only select rows where rn in (1,2)

--set up demo data
declare @T table (id int, name varchar(10), NumberId int)
insert into @T(id, name, NumberId) values
(1,'Abc',1),
(2,'bcd',1),
(3,'cde',1),
(4,'def',2),
(5,'efg',2),
(6,'fgh',2),
(7,'ghij',2),
(8,'defg',1),
(9,'efgj',1),
(10,'fghi',1),
(11,'ghij',1 )
;

--Create a grouping for consecutive values called grp
WITH _cte
AS (
    SELECT *
        ,DATEADD(DAY, - ROW_NUMBER() OVER (
                PARTITION BY Numberid ORDER BY [id]
                ), [id]) AS grp
    FROM @T
    )
    ,AddedRn    --add a row number for each entry in the group
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY grp ORDER BY id
            ) AS rn
    FROM _cte
    )
--Select only the rows with rn in (1,2)
SELECT *
FROM AddedRn
WHERE rn IN (1,2)
ORDER BY id

| id | name | NumberId | grp                     | rn |
|----|------|----------|-------------------------|----|
| 1  | Abc  | 1        | 1900-01-01 00:00:00.000 | 1  |
| 2  | bcd  | 1        | 1900-01-01 00:00:00.000 | 2  |
| 4  | def  | 2        | 1900-01-04 00:00:00.000 | 1  |
| 5  | efg  | 2        | 1900-01-04 00:00:00.000 | 2  |
| 8  | defg | 1        | 1900-01-05 00:00:00.000 | 1  |
| 9  | efgj | 1        | 1900-01-05 00:00:00.000 | 2  |


DB2 LUW V10.5 on Windows. How to Manually rotate the Diaglog using scripts

The easiest way to rotate the db2diag.log file is to use the db2diag utility with the -A ("archive") option.  It will rename the current db2diag.log file (appending the current timestamp to the file name).

db2diag is also quite useful for searching through huge db2diag.log files (instead of trying to open them in a text editor).  It has many options to filter the file based on time, content of messages, etc.  You can read more in the documentation.


Which user does Windows Authentication of a maintenance plan authenticate? before whom, why and for what?

The Manage Connections dialog is for a connection to the Database or Instance you are performing maintenance on.

So if you type in a sql authenticated user called 'bob' there, the user bob will be the one that connects to the instance to do the maintenance tasks you are specifying.

You can prove that out by running SQL Server Trace/Profiler (in a dev environment) and seeing the connection being made.

That is basically the user that is connecting to the objects within SQL Server.

The connection to the folders and external resources will be, in this case, whatever your SQL Server Service Account is running as.

It's a bit confusing but it sort of works like this:

1.) Your SQL Server Agent Service Account (or the proxy account used) will do all of the executing. It will call out to the maintenance plan, it will handle the job logging for job history/etc.

2.) When the maintenance plan is run - any of the tasks inside of it will either use whatever permissions the agent account has, or whatever you have explicitly defined in the maintenance plan connection manager -as you have done.

3.) For any access to resources outside of SQL Server - the SQL Server Service account will actually be the account interacting. So to write a backup to a folder, your SQL Server Service account will need that permission. The agent tells the server to run the maintenance plan which tells the server to do a backup. The service account of the SQL Server service then performs what it was requested and it writes out the backup.

Also

You didn't ask here - but I'll mention it again like I did in the last answer. I don't get anything for recommending a free resource - but the Ola Hallengren maintenance solution I mentioned on a related question from you is a really good resource. It will intelligently do your index rebuilds/reorganizes so you only touch what needs to be, it will add more control and intelligence to your statistics updates, it does a great job with backups, etc. But the nicest thing going for it? It gets rid of the black box of maintenance plans and takes a variable out of play for you to have to worry about seeing inside of with some of the more advanced security questions you are trying to get answered.


What happens if transaction rollback fails?

I think your question hits the core of the database reliability properties; the ACID properties. They are there in order to guarantee the database's reliability. The transaction log plays a vital role here. You probably know the ACID properties, so Iam not diving into that.
If a rollback fails, then you would have a serious problem. The reliability of the database cannot be guaranteed. In other words; you probably have some sort of corruption in your transaction log and will end up with an inconsistent database. I am not sure how it works with PostgreSQL, but for MS SQL Server it will set the DB offline and restarts it, which will trigger a recovery action and it will try to rollback the transaction again. If that doesn't work you will probably have to restore the database from an earlier backup (log, differential or full).


Ideally you can shutdown using

/etc/init.d/mysqld stop

I cannot add comments so requesting to post what do you see in error log when you do

mysqladmin shutdown

About kill commands please note that mysqld_safe is wrapper script which will start mysqld upon killing... so you should kill mysqld_safe first followed by mysqld. ( not a suggested way though).

Refer: how to stop mysql on mac


Mongodb Standalone Performs better than Replica Set

I would like to know what could be the real reason for the replica sets to function slower compared to the standalone db.

Replica sets provide high availability and data redundancy, but these benefits are best realized using separate server resources.

With multiple data-bearing replica set members on a single server you are adding contention for the underlying hardware resources (CPU, RAM, disk):

Every write has to be replicated and applied by each of the replica set members (which each have their own journal, oplog, and data files), so there will be significantly more I/O as compared to a standalone server.
A replica set on a single server will be using RAM to store multiple copies of the same data; a standalone can have more of your data & indexes in the same amount of RAM.

If the reason for your performance challenge is a lack of resources, you should be adding dedicated resources (such as more RAM or faster storage) rather than sharing existing resources.

Note that even with replica set members on multiple servers, secondary reads are not a panacea. For some considerations, see: Can I use more replica nodes to scale?.

Before adding additional server resources, I recommend starting with optimising indexes to support your common queries and aggregations. See: Index Strategies in the MongoDB documentation.


Microsoft SQL Server - Permission to create a Snapshot

Its not possible to do straight away as mentioned on MS documentation:

  Permissions Any user who can create a database can create a database
  snapshot; however, to create a snapshot of a mirror database, you must
  be a member of the sysadmin fixed server role.

What I understand is - if you could have alternative in place for DB creator role, this purpose can be achieved and this has been explained very well by one of MSSQL SME Mr. Solomon Rutzky

You can achieve this by using module signing which has been explained by Mr. Tibor Nagy on this link --> https://www.mssqltips.com/sqlservertip/3336/how-to-use-module-signing-for-sql-server-security/

You can also refer the question and answer thread for better understanding of module signing at the same forum and how can this be used:

Granting a user a permission to restore a database

Execute Permissions for a Store Procedure that creates databases

https://sqlquantumleap.com/2018/02/15/safely-and-easily-use-high-level-permissions-without-granting-them-to-anyone-server-level/

More about Module signing at Microsoft:

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms345102(v=sql.100)

Hope this leads you into right direction.


I think the article is useful in a very limited scenario:

You are on a local or otherwise isolated machine.
You need to do this for a very short burst of activity.
You have no access to Profiler (not my first choice for tracing anyway), no idea how to construct a server-side trace, and no access to Books Online so you can learn the syntax (you don't need Profiler to set up a trace).
You have control over, or at least cursory knowledge of, the set of statements that might be executed against your instance for the duration of your "trace."
You have a good idea of how much space is on your C drive...

Without those disclaimers in place, I'm actually quite nervous that someone is going to skim the article, not read the comments, put this into production, and forget about it until - well, you can imagine the handful of scenarios that could result.


Slow Trigger performance when big batches (take2)

Instead of doing all that work in the update trigger, you could just capture the DELETED rows and worry about what actually changed, and formatting a pretty message later.  EG

create table price_history (SKU varchar(13), PriceGroup varchar(5), PriceLevel int, Price float, Qty float, ManyOtherColumns Varchar(100), historyDate datetime, ChangedColumns varchar(Max))
CREATE unique clustered INDEX price_history_nc1 ON price_history
(
    SKU ASC,
    PriceGroup ASC,
    PriceLevel ASC,
    HistoryDate ASC
)

go
Create or alter TRIGGER PriceHist_trig_U ON MyPriceTable FOR UPDATE
AS
INSERT INTO price_history (SKU, PriceGroup, PriceLevel, price, Qty, ManyOtherColumns, HistoryDate)
            SELECT DEL.SKU,DEL.PriceGroup,DEL.PriceLevel,DEL.Price,DEL.Qty,DEL.ManyOtherColumns, getdate()
FROM DELETED DEL

Or try skipping the trigger and using Change Tracking, Change Data Capture, or Temporal Tables instead.


How can I find the user who performed a database transaction?

Yes you can find what you are looking for in mysql log files.
Binary Log keeps track of the transactions that describe database changes such as table creation operations or changes to table data.

Query Log keeps track of all operations including Select and Show statements.

Both log files describe the action with the user who performed it.

This link is a tutorial for how to read binary log file.


Benefits of reorging a new table with loaded data?

Db2 for LUW
REORG of compressed tables with RESETDICTIONARY might be beneficial after the initial load, because during the load the table-level compression dictionary is built using a sample of incoming data, which means that if your data patterns are unevenly distributed across the input file the dictionary might be suboptimal. REORG will use a larger sample (if not all of the data) to build the dictionary.
Other than that, REORG after LOAD is unneccesary.
Db2 for z/OS is different because in it every table would have a clustering index (implicitly defined if not declared explicitly), and if your input file is not ordered according to the target table's clustering index, clustering factor after the load will be poor.


Recent patches for vulnerability attacks for SQL Server 2014 SP2 version

You need both SQL and Windows patches, as well as CPU microcode updates, to be fully** protected.

GDR is meant to be security-only patches, whereas Cumulative Updates are bug fixes as well as security patches. Once you go from the GDR path on to the CU path, you're stuck there, i.e. you can't go back to GDR only.

(Confusingly, this latest Meltdown / Spectre update shows up on certain SQL Server versions as a GDR and CU update.)

If you are already on a CU, then stick with CU update.

** Note that Microsoft is rolling back the Meltdown / Spectre updates on Windows Update because there are some issues with certain CPUs, so it's hard to tell whether you were one of the lucky ones to get them while they were up. See the warning below.

Use this link to check if you're fully patched, using a PowerShell script from Microsoft.

The link includes the following statement:

  Customers must take the following actions to help protect against the
  vulnerabilities:

  Verify that you are running a supported antivirus application before
  you install operating system or firmware updates. Contact the
  antivirus software vendor for compatibility information.
  Apply all
  available Windows operating system updates, including the January 2018
  Windows security updates.
  Apply the applicable firmware update that is
  provided by the device manufacturer.

And this warning:

  Customers who only install the Windows January 2018 security updates will not receive the benefit of all known protections against the vulnerabilities. In addition to installing the January security updates, a processor microcode, or firmware, update is required. This should be available through your device manufacturer.


SQL Server - register Service Principle Name without service restart?

Quick answer

Question:

  I have configured everything correctly, but after these changes I want to ensure that SQL Server can register the SPN's successfully- preferably without a service restart as this is a live environment.

  Is there a way of doing this?

Answer:

No.

Explanation

From the official documentation Register a Service Principal Name for Kerberos Connections

  A Service Principal Name (SPN) must be registered with Active Directory, which assumes the role of the Key Distribution Center in a Windows domain. The SPN, after it is registered, maps to the Windows account that started the SQL Server instance service. If the SPN registration has not been performed or fails, the Windows security layer cannot determine the account associated with the SPN, and Kerberos authentication will not be used.

and

  If the server cannot automatically register the SPN, the SPN must be registered manually. See Manual SPN Registration.

and further down in the section Automatic SPN Registration you can find

  When an instance of the SQL Server Database Engine starts, SQL Server tries to register the SPN for the SQL Server service. When the instance is stopped, SQL Server tries to unregister the SPN. For a TCP/IP connection the SPN is registered in the format MSSQLSvc/:.Both named instances and the default instance are registered as MSSQLSvc, relying on the  value to differentiate the instances.

Summary

The SQL Server Service essentially regsisters the SPN on service start automatically. If you cannot start the SQL Server Service then you will have to perform this task manually.

Considerations

Ensure the service account that start the SQL Server Service is allowed to read and write SPNs.

  To do this, you must grant the following access control settings for the SQL Server service account in the Active Directory directory service:

  Read servicePrincipalName
  Write servicePrincipalName

Reference: How to use Kerberos authentication in SQL Server (Microsoft Support)


MySQL least privileges to get database/tables size

This is a little confusing but the minimum privileges you need to check a table size is to have the privileges to SELECT that table. So if you want to know the whole DB size you need privileges to SELECT every single table in that DB.

This is why:

There is no way to read a DB size without reading its tables' sizes
The command to read a table size (no querying information_schema) is SHOW TABLE STATUS table_name
SHOW TABLE STATUS derives from SHOW TABLES, so the privileges and rules are similar.
Finally, the official documentation about SHOW TABLE:
https://dev.mysql.com/doc/refman/8.0/en/show-tables.html

  If you have no privileges for a base table or view, it does not show
  up in the output from SHOW TABLES or mysqlshow db_name.

So the minimum access to check DB sizes is pretty invasive because you need to grant SELECT privileges to all tables.


How to combine duplicate parent rows in a relational MySQL table

You'll need to change the people_meta.person_id values before deleting the row from the people table, because the engine wouldn't know how to replace the values with the duplicates.

update people_meta set person_id = 1 where person_id = 0;
delete from people where id = 0;


is there a work around for when you want to put an OR inside a filtered index?

Unfortunately, there seems to be no way to create a negative filter for an index, without resorting to creating a materialized view.  If it were possible to create a negative filter such as the one you'd like, it would be quite difficult for the query-optimizer to "pick" the index for use, drastically increasing the time required to find a good plan.

Depending on the query patterns for this table, you could simply create two indexes; one for less than 9 and one for greater than 14.  Either of these indexes may be chosen by the query optimizer for simple WHERE clauses such as WHERE StatusID = 6

CREATE TABLE dbo.TestNegativeFilter
(
    TestNegativeFilter INT NOT NULL
        CONSTRAINT PK_TestNegativeFilter
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , StatusID INT NOT NULL
);
GO

CREATE INDEX IX_TestNagativeFilter_LessThan9
ON dbo.TestNegativeFilter(StatusID)
WHERE (StatusID < 9);

CREATE INDEX IX_TestNagativeFilter_GreaterThan14
ON dbo.TestNegativeFilter(StatusID)
WHERE (StatusID > 14);

Another way to accomplish this might be:

CREATE INDEX IX_TestNegativeFilter_9_to_14
ON dbo.TestNegativeFilter(StatusID)
WHERE (StatusID IN (9, 10, 11, 12, 13, 14));

SELECT *
FROM dbo.TestNegativeFilter tnf
EXCEPT
SELECT *
FROM dbo.TestNegativeFilter tnf
WHERE tnf.StatusID IN (9, 10, 11, 12, 13, 14);

This uses the index filtered on 9 to 14 to exclude rows.

On my test rig, a simple covering index returns rows by far the quickest:

CREATE NONCLUSTERED INDEX IX_TestNegativeFilter_StatusID
ON dbo.TestNegativeFilter(StatusID)
INCLUDE (TestNegativeFilter);

SELECT *
FROM dbo.TestNegativeFilter tnf
WHERE tnf.StatusID NOT IN (9, 10, 11, 12, 13, 14);

Alternatively, using a variation on the approach used in your own answer:

CREATE INDEX [IX dbo.TestNegativeFilter StatusID not 9-14]
ON dbo.TestNegativeFilter (StatusID)
WHERE StatusID <> 9
AND StatusID <> 10
AND StatusID <> 11
AND StatusID <> 12
AND StatusID <> 13
AND StatusID <> 14;

Despite the filter being written as conjunctions, it supports queries written in any of the following ways (the first being slightly more efficient):

StatusID NOT IN (9, 10, 11, 12, 13, 14)
StatusID < 9 OR StatusID > 14
StatusID NOT BETWEEN 9 AND 14


This is the correct form of the if clause

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
     CONCAT(
       'IF(q.label = "',
       label,
       '", 1, 2) AS ',
       label
    )
   ) INTO @sql
    FROM question;
SET @sql = CONCAT('SELECT a.id, ', @sql, ', q.id
       FROM answer a
       INNER JOIN question q ON questionId = q.id
       GROUP BY a.id');
PREPARE stmt FROM @sql;

But still you should always write the result of
    SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
     CONCAT(
       'IF(q.label = "',
       label,
       '", 1, 2) AS ',
       label
    )
   ) INTO @sql
    FROM question;
 SELECT @sql;


100 million rows in a table does not make a database "huge" by today's standards

This is also not MySql related in most ways - because you leave pretty fat the server software level and talk hardware.

The standard answer to this is that it all depends on your workload profile. Is it transactional, reporting, OLAP?

You mentioned that your largest table is 100 million rows. How does this compare to the other tables? Are they much smaller, or about the same size?

You may want:

As much RAM as you can. It is nice that servers these days should be able to load this size table in memory, isn't it?
As much disk performance as you can get.

This is where special hardware gets into the game fast. The last project I did in this area had 3x768gb flash disks with write back cache and 2x96gb with 48 cores each. However, this was an Oracle RAC and probably way beyond what MySQL could handle.

Anyhow, your best bet would be to go SSD for the IO side, memory for the buffer side, and with something in the 100 million row range you should be able to keep a significant part of that in memory. Using a 64gb server and 48gb buffer for that table I get  480 bytes per row (table, index). That wont be exact, but it gives you an idea that you can easily (64gb servers are quite cheap) fit the data in memory.

Here is a hint: Databases are not large when the fit in memory. They are large when you can not fit them into decent server hardware.


Backing up multiple databases to the same point in time

I am assuming that your task is to create a set of backups where you can restore all of them to a single static (no open transactions) point (all in sync).  Similar to what a quiesce point will do for you in DB2.  Unfortunately there is nothing in SQL Server that does exactly that.  However, assuming your databases are in FULL recovery mode, you can do something similar.

You can create a single MARKED transaction across all of the databases at once.  Then you take a transaction log backup of each database.  Assuming you are keeping up with your log backups (which you should be anyway) log backups don't take very long.  And there is no need to stop anyone from being in the system (except maybe long enough for you to create your marked transaction).

At this point if you need to do a restore then you do a RESTORE STOPBEFOREMARK on each of your databases.  This will restore all of the databases to the same point (based on your transaction).  Note: There is also a STOPATMARK if you want to include that transaction.

If you want an example I've got one in the link above along with quite a bit of additional reading.

I realize this doesn't exactly answer your question but hopefully it solves your problem.


How to SUM previous sum, e.g N = (N-1) + (N-2) + ... + 1?

This looks like a candidate for window functions.

First calculate the per-id sums, then do a running sum ordered by ID to get the desired final result.

with t_report_code_temp(id, t_code) as
(
select id, sum(no)
from table_a
group by id
)
SELECT
  id,
  sum(t_code) OVER (ORDER BY id ASC)
FROM t_report_code_temp;


Safe to start Mirroring on Production Server?

Assuming you have everything properly configured, starting mirroring simply means the log rows for transactions not already at the mirror will be sent to the mirror database; once that has occurred the only further overhead would be writing to the mirror database.  If the mirror is not very far behind the principal, then this step is generally very quick.

If you have mirroring configured in Synchronous (high safety) mode, that overhead might be enough to show delays in commits. Generally, transactions would be slightly slower since data must be written to both the primary log, and the mirror log before control is returned to the user.

If you have a maintenance window, it would be preferable to start mirroring during that window.


What is the equivalent of a "Certificate of Destruction" when using a cloud-hosted database?

1) When we issue the ActiveResource::Base#destroy command on a record,
  is the data truly destroyed?

Sure not.

  We keep backup snapshots with Heroku's PG
  Backups, so even if the original command truly destroys the data, its
  probably still in the backups. Without deleting the backups, is it
  even possible to destroy completely the client's data?

Sure not.

  2) Is there an equivalent to a Certificate of Destruction for this
  purpose?

No. Ask your cloud provider but 99.99% = no.

  3) If there is not an equivalent, how have other people mitigated or
  otherwise assuaged similar client concerns?

Full data layer encryption.  Tricky but possible.


String comparison without CASE statements?

I'm not sure that you'd call it shorter, but you could use the Logical Function IIF. From the docs,

  IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation.

Per the example,

SELECT c, iif(c='foo',1,0)
FROM ( VALUES ('foo'),('bar') ) AS t(c);


Max Price Using Multiple Tables and Columns

I'm guessing when you're adding the other columns, you are changing the GROUP BY as the columns in the SELECT must be included. That's going to change your results.
Instead, I do the joining and grouping necessary to get the results I wanted, in your case the average, in a sub query and join to that.
Try this:
SELECT
    Max_AvgPrice,
    te.a__IDPK_TreatmentEvent,
    te.Code_Insurance_1
FROM
    (
        SELECT
            Max_AvgPrice = MAX(pav.AvgPrice),
            te.a_ID_Treatment
        FROM
            dbo.TreatmentEvent           te
        LEFT JOIN
            dbo.dim_Procedure_Codes      pco ON te.Code_Insurance_1 = pco.a_IDPK_ProcedureCode
        LEFT JOIN
            dbo.dim_Procedure_Category   pca ON pco.a_ID_ProcedureCategory = pca.a_IDPK_ProcedureCategory
        LEFT JOIN
            dbo.dim_Procedure_AvgPrice   pav  ON pco.a_ID_ProcedureGrouping = pav.a_ID_ProcedureGrouping
        GROUP BY
            te.a_ID_Treatment,
            YEAR(te.TreatmentEventStartDate),
            pca.a_IDPK_ProcedureCategory,
            te.a_ID_TreatmentEventStatus
        HAVING
            (
                YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE())
                OR YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE()) - 1
            )
            AND (pca.a_IDPK_ProcedureCategory = 1)
            AND (te.a_ID_TreatmentEventStatus = 3)
    ) AS subquery
    INNER JOIN dbo.TreatmentEvent AS te ON subquery.a_ID_Treatment = te.a_ID_Treatment