This is also not pretty, but it allows for as\many\sub\folders\as\you\may\have.

-- borrowing from Brent:
CREATE TABLE #Files (Parentpath varchar(100), Size int);

INSERT INTO #Files (Parentpath, Size)
  VALUES ('Business\Packets\Data\Archive', 29334),
  ('Coach\Loss\Wharf\Current\Blat\Splunge\More', 7337);

DECLARE @s char(1) = CHAR(92), @sql nvarchar(max) = N'SELECT ParentPath, Size';

SELECT ParentPath, Size, value, rn = ROW_NUMBER() OVER
  (PARTITION BY ParentPath ORDER BY CHARINDEX(value + @s, ParentPath + @s))
  INTO #x FROM #Files AS f CROSS APPLY STRING_SPLIT(f.ParentPath, @s) AS t;

DECLARE @max int = (SELECT MAX(rn) FROM #x);

SELECT @sql += N',  Folder' + RTRIM(rn) + N' = MAX(CASE WHEN rn = '
            +  RTRIM(rn) + N' THEN value END)'
  FROM (SELECT rn = ROW_NUMBER() OVER (ORDER BY name) FROM sys.all_columns) AS x
  WHERE rn <= @max;

SET @sql += N' FROM #x GROUP BY ParentPath, Size ORDER BY Folder1'
         +  CASE WHEN @max > 1 THEN N', Folder2' ELSE N'' END + N';';

EXEC sys.sp_executesql @sql;

DROP TABLE #x, #Files;

With this sample data I get the following results:


I'm not sure how your previous .my.cnf used to work, and I actually have never used these files before (mainly because I didn't know about them). So after a bit of research, I found this link and came up with the following ~/.my.cnf that worked for me:

[clientdbid]
password = mypass
database = dbname
host = server.location.com

and the command that reads it:

mysql --defaults-group-suffix=dbid

A couple things to point out (highlights from the article linked):

group has to be preceded by 'client' to be read by mysql
has to go after any [client] groups, otherwise it will be overridden

I tested this on mysql 5.5 on a Mac, worked great. And now that I know about them, I will use them!

UPDATE
After I set this up, I realized that the command line mysql --defaults-group-suffix=dbid was a little hefty. So as added bonus, assuming you're running Linux/Mac/Etc do this:

echo 'alias mysql_dbid="mysql --defaults-group-suffix=dbid"' >> ~/.profile

Where dbid is the name of your suffix group.


Changing stored procedure parameter results slow stored procedure performance

One of the first things to look at in an actual execution plan, especially on the most expensive operations, is the estimated vs the actual row counts, executions, et cetera.

Often, when the estimated numbers are significantly off from the actual numbers, it indicates that the statistics for the table are out of date. This can cause the query engine to choose a bad plan. Based on what it knows about your data, the plan makes sense; however, enough of the data has changed since your statistics were last updated that the plan's not the best option for actually returning your data.

If you don't have a job that regularly updates statistics, trying updating the statistics on just this one table (or, more likely, the tables involved in this view), and see if the query speed becomes more predictable. I'd recommend setting up a statistics update job to run on a regular basis (probably at least weekly).

Note that, as the stored procedure is recompiled each time through, some time is spent calculating that optimal plan. If the speed of the stored procedure becomes more predictable, you may want to consider trying it without the WITH RECOMPILE; depending on the complexity of the query, the cost of the recompilation might be higher than the cost of using the same plan with different parameters.


Access denied for user 'root'@'localhost' using Password NO

It's due to incorrect permissions on /etc/mysql/debian.cnf file.

Do a long-listing of that file first to check current permissions:

sudo ls -l /etc/mysql/debian.cnf

If group or other fields has write permission enabled, run:

sudo chmod 644 /etc/mysql/debian.cnf

and then restart mysql:

sudo /etc/init.d/mysql restart


How can I control how Pentaho DI formats integers coming from a text file?

Try to convert values in format you need before output, it can be done in "Select values" step, bookmark "Meta-data".


Try sp_BlitzIndex with the @Mode = 2 switch for detailed inventories per database including object counts, row counts, and sizes. You can also use the @GetAllDatabases = 1 parameter for system-wide results. (Disclaimer: I’m one of the open source script’s authors.)


Security update failed : The baseline msi is not installed

Edit: I reviewed this a bit further and another cause of this issue has been the Windows Installer cache.  That seems to create a 'file not found' error when looking for the copy of the msi/msp package that's copied into the Windows directory with the update GUID.  We're not seeing that here, but I think it's because we're not seeing the actual log file for the KB.  Aaron's point was good to bypass Windows Update, but also check to see if everything actually exists where it is supposed to if that doesn't work.
Check here for that solution to this issue.  Again, we will know much better when we can get the actual log.

This has came up a few times in prior versions of MS SQL Server.  This blog shows a good way to actually troubleshoot and resolve the issue. His solution of copying a exe from the other node and replacing it in the binn folder then restarting seems too hacky for me.  I like the SQLGuru suggestion of just reinstalling the prior SP.  That might give you some good results.

This problem can be caused by several issues such as using the wrong patch (x64 vs x32, wrong sp cu, etc.) and other scenarios such as the one listed where the version of the exe for whatever reason isn't what it is expecting, or a entry is not found for that patch in the registry, or God knows what else.  I don't.

The best way to really see what is the root underlying issue would be to review the log files for that particular KB.  It should be in the same section as your other log file you pasted.  From there you should be able to note if the actual patch is the wrong one or out of order, if you need to just reinstall the prior SP and then retry, or if there's another error you'll need to address.  The actual log for those should be named something like sqlversion_KB#.log


If the job_no is unique it could be much faster (adding a unique index to the job_no). but even if its not unique this query could be optimized just adding an index to the job_no

ALTER TABLE jobs
ADD INDEX ind_job_no (job_no) USING BTREE;

adding this index will allow you to update quicker.


Trigger to get next prefixed ID: Is there a better way?

I expect your trigger to throw runtime "Mutating table" error . Unless autonomous transaction is used because you cannot query the table that fires the row-level trigger inside its body.

I'd rather keep it simple - create a sequence, for instance ,  CREATE SEQUENCE SEQ_PROJECT START WITH 6 INCREMENT BY 1.
Also, I'd fire trigger only if project_id is not set (WHEN clause) .

CREATE OR REPLACE TRIGGER "PROJECTS_PROJECT_ID_TRIG" BEFORE INSERT ON PROJECTS
    FOR EACH ROW
    -- Fire trigger only if statement doesn't set project_id
    WHEN (NEW.PROJECT_ID IS NULL)
      BEGIN
       :NEW.PROJECT_ID := 'LC' ||to_char(SEQ_PROJECT.NEXTVAL,'FM000000');  <- Fill Mode
      END;


When you change it to datetime < FROM_UNIXTIME(1549622615) you also need to change the sort order to ORDER BY datetime DESC to get the closest date.


Inherited Permissions: SQL Server 2005 vs 2016

I would not expect the different SQL Server versions to make a difference here. Although the setup might appear identical, there are likely subtle differences between the databases on the 2 servers or the server config.

The first thing I suggest is to verify requirements cross-database ownership chaining are met:

Ensure the 'cross db ownership chaining' configuration option is the same on both servers
Make sure the DB_CHAINING database option is identical
Check that the database owners are the same to maintain an unbroken ownership chain for dbo-owned objects

If the culprit is the 'cross db ownership chaining' server option, it would be better leave that off and instead turn on the more granular DB_CHAINING database option for the SQL 2016 databases in question.

Post the actual error message if you still have problems after the above.


Restore deleted data from a Table which occurred daily

It would be an arduous process, but you might be able to use RESTORE WITH STANDBY to a different instance.  After each log is restored, you can read the data in the database.  There is no guarantee the rows you are looking for haven't been added and deleted within the same transaction log backup.

RESTORE Statements

  STANDBY =standby_file_name Specifies a standby file that allows the
  recovery effects to be undone. The STANDBY option is allowed for
  offline restore (including partial restore). The option is disallowed
  for online restore. Attempting to specify the STANDBY option for an
  online restore operation causes the restore operation to fail. STANDBY
  is also not allowed when a database upgrade is necessary.

  The standby file is used to keep a "copy-on-write" pre-image for pages
  modified during the undo pass of a RESTORE WITH STANDBY. The standby
  file allows a database to be brought up for read-only access between
  transaction log restores and can be used with either warm standby
  server situations or special recovery situations in which it is useful
  to inspect the database between log restores.

On a side note (in reference to your statement about hourly transaction log backups), this situation is one of the reasons I like to Back Up Transaction Logs Every Minute. Yes, Really. for my critical databases.

Here is an example of using WITH STANDBY.

/* Create our test database.  You may need to change the file paths for your environment. */
CREATE DATABASE [StandbyTest] ON  PRIMARY
( NAME = N'StandbyTest', FILENAME = N'C:\StandbyTest\StandbyTest.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'StandbyTest_log', FILENAME = N'C:\StandbyTest\StandbyTest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [StandbyTest] SET RECOVERY FULL
GO
USE [StandbyTest]
GO

/* Create some dummy data */
CREATE TABLE dbo.Customers (CustomerID INT, CustomerName VARCHAR(50))
GO
INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (1, 'Bill Gates')

INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (2, 'Steve Ballmer')

GO

/* Do a full backup of the database */
BACKUP DATABASE [StandbyTest] TO  DISK = N'C:\StandbyTest\StandbyTest.bak'
WITH FORMAT, INIT,
NAME = N'StandbyTest-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

/* Add some more dummy data */
INSERT INTO dbo.Customers (CustomerID, CustomerName)

 VALUES (3, 'Donald Farmer')

/* Do a transaction log backup */
BACKUP LOG [StandbyTest] TO  DISK = N'C:\StandbyTest\StandbyTest.trn'
WITH FORMAT, INIT,
NAME = N'StandbyTest-Transaction Log  Backup',
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

/* Pretend we're a developer */
DROP TABLE dbo.Customers
GO

/* DISASTER!  We need to go back in time! */
USE master
GO
RESTORE DATABASE [StandbyTest]

 FROM  DISK = N'c:\StandbyTest\StandbyTest.bak'
 WITH  FILE = 1,
 REPLACE,
 STANDBY = N'C:\StandbyTest\ROLLBACK_UNDO_StandbyTest.BAK'

GO

/* Test to see if we can read records */
SELECT * FROM StandbyTest.dbo.Customers
/* Notice that we only got 2 customers, because we haven't restored the t-log yet. */

RESTORE LOG [StandbyTest]

 FROM  DISK = N'C:\StandbyTest\StandbyTest.trn' WITH  FILE = 1,  NOUNLOAD,  STATS = 10

GO

/* Test to see if we can read records */
SELECT * FROM StandbyTest.dbo.Customers


Dynamic change to innodb_flush_log_at_trx_commit

While I agree with Rolando's recommendation to change innodb_flush_method, I wasn't 100% clear what you meant by:

  it did not make the actual MySQL setting changed

I want to point out the caveat that making a change to the GLOBAL variable affects any new connections, but does not modify the current session (emphasis mine):

  The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement).

So to check that:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> connect;
Connection id:    6
Current database: *** NONE ***

mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)


Which database should be using for my specific goal

XY Problem
I see too many Red Flags in your post to leave a comment.  In general, you seem to have an XY Problem.
Problem 1

I can constantly hear it grinding away.

STOP! RUN! DO NOT WALK! GET THIS HD REPLACED ASAP!!
If it is really making a grinding sound, then your HD is already toast.
Problem 2

it is averaging about 2MB/s

Modern Hard drives can achieve an IO transfer speed in the 600 MB/s range.  Your observation means you have a non-DB related issue.
Make sure you run your IO benchmark outside of the DB. If your Data Process is not coming close to that measured transfer speed, then the bottleneck is not with the disk IO.
Usually, it is with the way you are processing your data.
Problem 3

averaging to about 200 inserts per second.

Ahh!! Now we are getting to the heart of the problem.
This is usually an indication that you are using a slow-by-slow process method to load data.
You need to load data in Bulk.  When you do that, you should see INSERT rates closer to 1M rows in mere seconds. Your IO transfer rates should then be closer to what you measured outside of the DB.
Problem 4

With the possible mechanical drive as the bottleneck

Use a RAID setup to improve IO/IOPS performance.  This applies to both HDDs and SSDs.
Problem 5

To be able to compare each column in Table 2 to see if the same value exists in column 1 of Table 1 as fast as possible.

Relational Databases are built based on Relational Algebra. If you can change your Business Requirement to be a Relational Algebra question, then the solution becomes a no-brainer
I suggest you Change your Requirements so that

The values in Table_1.Column_1 need to be UNIQUE and NOT NULL
The database is not allowed to accept data for Table_2 that does not comply with the stated requirements.

From there, the answer becomes "Use a FOREIGN KEY on each of the columns in Table_2 that points to the PRIMARY KEY (TABLE_1.COLUMN_1)".
The overhead for the FK check during INSERT should be minimal.
I am unaware of any of the major relational databases that cannot handle a 1 Billion row child table and/or 1 Billion row parent table. So, MySQL should be just fine. (BTW - "List of Software" questions are off topic for this forum.)


SQL Server - Select parent row or child row if no parent

Join the same table twice and use a CASE statement:

select
    case when t2.id is not null then t2.id else t1.id end as id,
    case when t2.id is not null then t2.childid else t1.childid end as childid
from
    tbl t1
left join tbl t2
    on t2.childid = t1.id

id | childid
-: | ------:
 4 |       1
 2 |       3
 2 |       3
 4 |       1
 5 |    null
 6 |    null

db<>fiddle here


SQL Server query scanning more partitions than expected

Reasoning

When comparing the column of the non partitioned table against the partitioned table, sql server will not be able to know what DayOfMonth will hold, even if all of them are 15.

As a result it would not know which partitions to return to satisfy this filtering when joining the two tables.

A different example to give some more insight can be found here.

Testing

I was able to recreate your issue, for further questions please add as much information as possible.
This could be Table definition, indexes, partition scheme, ...

The DDL & DML is at the bottom.

When running the query we can get the same result:

62 partitions returned.

On the seek predicates it tries to filter what it can, this is the two filters on CalendarId that do not have the  + DayOfMonth-1.

This translates to

WHERE ...
    AND (( dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601))
...

When running the query with these filters you will see the same amount of rows returned on accessing the table in the query plan.

Only after getting this data, it can and will be joined to the AccountInfo table on the ID's and the DayOfMonth - 1

To confirm your statement that when we remove the column that only two partitions are scanned:

SELECT
    ai.Name
    ,SUM(dc.Component )
    ,SUM(dc.Value) Value
FROM
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601  - 1)
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270601  - 1)))
GROUP BY            Name

Only two are accessed and used:

Solution

I am going to assume that DayOfMonth only goes up to 31 days.

If you know these boundaries, you can 'hard code' them so that sql server knows what partitions to look for. After this you can add the extra filtering.

E.G.

WITH CTE
AS
(
SELECT
    ai.Name
    ,dc.Component
    ,dc.Value
    ,dc.CalendarId
    ,ai.DayOfMonth
FROM
    Warm.DailyCosts dc
    JOIN Warm.AccountInfo ai
        ON dc.AccountInfoId = ai.Id
WHERE
    ai.CorrelationId = '00000000-0000-0000-0000-000000000000'
    AND ((dc.CalendarId >= (20190601 )
        AND dc.CalendarId < 20190701)
    OR (dc.CalendarId >= 20270601
        AND dc.CalendarId < (20270701)))

)

SELECT Name,Component,SUM(Value)
FROM CTE
WHERE ((CalendarId >= (20190601 + DayOfMonth - 1)
        AND CalendarId < 20190701)
    OR (CalendarId >= 20270601
        AND CalendarId < (20270601 + DayOfMonth - 1)))

GROUP BY
    Name
    ,Component

The sole purpose of the  cte is to let sql server know that it can also filter on dc.CalendarId >= dc.CalendarId >= (20190601 ) OR dc.CalendarId < (20270701)).

Sidenote: Adding a constraint does not work to enforce this.

This query gives us the result we want, with the correct  partition elemination:

Only accessing partitions 19 & 21.

You can probably also use OR / AND logic to get the same result if you prefer. The important part is getting the boundaries known.

Test data

CREATE SCHEMA WARM
GO

CREATE TABLE Warm.DailyCosts(ID INT IDENTITY(1,1) NOT NULL,
                            Component int,
                             Value int,
                             CalendarId INT,
                             AccountInfoId int
                             )

CREATE TABLE Warm.AccountInfo(Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                              Name VARCHAR(25),
                              CorrelationId uniqueidentifier,
                              DayOfMonth int
                              );
USE [master]
GO
ALTER DATABASE [test] ADD FILEGROUP [Partitionfg]
GO
USE [test]
GO
CREATE PARTITION FUNCTION [PF_CostDateByYearMonth](int) AS RANGE RIGHT FOR VALUES (
    20180101,
    20180201,
    20180301,
    20180401,
    20180501,
    20180601,
    20180701,
    20180801,
    20180901,
    20181001,
    20181101,
    20181201,
    20190101,
    20190201,
    20190301,
    20190401,
    20190501,
    20190601,
    20190701,
    20270601,
    20270701,
    20270801,
    20270901,
    20271001,
    20271101,
    20271201,
    20280101,
    20280201,
    20280301,
    20280401,
    20280501,
    20280601,
    20280701,
    20280801,
    20280901,
    20281001,
    20281101,
    20281201,
    20290101,
    20290201,
    20290301,
    20290401,
    20290501,
    20290601,
    20290701,
    20290801,
    20290901,
    20291001,
    20291101,
    20291201,
    20300101,
    20300201,
    20300301,
    20300401,
    20300501,
    20300601,
    20300701,
    20300801,
    20300901,
    20301001,
    20301101,
    20301201)

CREATE PARTITION SCHEME [PS_CostDateByYearMonth]
AS PARTITION [PF_CostDateByYearMonth]
ALL TO ( [Partitionfg] );

CREATE UNIQUE CLUSTERED INDEX ptci ON  Warm.DailyCosts(CalendarId,Id) ON [PS_CostDateByYearMonth](CalendarId) ;
USE [master]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N'TestPartition', FILENAME = N'D:\DATA\TestPartition.ndf' , SIZE = 3072KB , FILEGROWTH = 65536KB ) TO FILEGROUP [Partitionfg]
GO
USE [test]
GO

INSERT INTO  Warm.DailyCosts(Component,
                             Value,
                             CalendarId,
                             AccountInfoId
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20180101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
-- 2018
INSERT INTO  Warm.DailyCosts(Component,
                             Value,
                             CalendarId,
                             AccountInfoId
                             )
SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20190101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
-- 2019
INSERT INTO  Warm.DailyCosts(Component,
                             Value,
                             CalendarId,
                             AccountInfoId
                             )

SELECT TOP(1000000) --1M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
        20280101 +  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) / 1000 % 1000 ,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
-- 2028

INSERT INTO Warm.AccountInfo(
                              Name ,
                              CorrelationId,
                              DayOfMonth
                              )

SELECT TOP(3000000) --3M
        CAST(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS varchar(10)) + 'a',
       '00000000-0000-0000-0000-000000000000',
       15
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2
CREATE NONCLUSTERED INDEX [IX_Component_Value_DailyCosts] ON Warm.DailyCosts
 (Component,Value)
ON [PS_CostDateByYearMonth](CalendarId);
GO


Transactional Data Replication, SQL Server version compatibility

I found a way.

Turns out, if your subscriber is a higher SQL version than the publisher this error will always get produced. A way around this is to add the subscription on the Subscriber instance instead of on the Publisher.


Mathematical equality of two SQL statements

What is the mathematical equality of two SQL statements? For me two queries are equivalent if, when given both the same of any dataset, they return the same result set.

As you pointed out, SQL queries, a superset of relational algebra, can be very complex.  We can mix subqueries, use stored procedures and functions (deterministic or not) which will make you query looks more like real code.  If you are talking about these kinds of queries then it is going to be really hard.  In fact it is probably no different than the "are two algorithms equivalent" problem.

Under those conditions it is probably impossible.

However...

...it might be feasible if the two queries you want to compare are strict set operations.  If so, you can convert the queries to relational algebra and then work it out following equivalence rules.  If you have a selection/restriction with nontrivial boolean conditions then you might end up needing to prove than thoses conditions are also equivalent.  You'll then need to rely on boolean algebra and you'll probably end up doing a truth table.

As you can see this is going to be a lot of work and, as far as I know, nothing exists to compute all of that automatically.  Nevertheless, I found some tools that you might found useful if you want to tackle the task:

RelaX a relational algebra calculator
Truth table generator


Yes, you will have output even if only 1 of the SELECT query has results.

Though by default, you cannot determine if the result was from the 1st or 2nd SELECT query, you can include an additional column in each query to determine where in those SELECT query the result came from. See this example


How to properly restart a MySQL Master server?

You don't need to stop the slave. It will continue once the master restarts. It tries every 60 second or so to connect.
Only stopping the master service/vm is needed.


Linked Server : Syncing two tables on two SQL Servers

Have you considered Merge Replication?  This would seem to be a great solution to me.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication


SQL Loop through Inserts and Update table

You should be able to adjust the dynamic SQL to include the UPDATE for each staging table.

Also, based on your comment about the update only being executed when the insert is successful, I modified the code example to wrap each insert/update into a transaction which should be an all-or-nothing for that staging table.

UPDATE: Added TRY/CATCH logic

--NOTE: the actual EXECUTE sp_executesql @cmd is commented out
Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [RealTable], [StageTble] from MappingTable
),
InsertCommands as
(
select *,
'BEGIN TRY BEGIN TRANSACTION;  insert into ' + [RealTable] + '(' +
STUFF((
        SELECT ',' + [RealCol]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' +
' select ' +
STUFF((
        SELECT ',' + [StageColumn]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ' from ' + [StageTble] + '; update StagingHeader set inserted = 1 where StageTable = ''' + [StageTble] + '' + '; COMMIT; END TRY BEGIN CATCH ROLLBACK; END CATCH;'''  as InsertCommand
 from DistinctTables drt
)
select @cmd =
STUFF((
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

print @cmd

BEGIN TRY BEGIN TRANSACTION;  insert into tblDest1(FldABC,FldZXC,FldQWE) select Field_ABC,Field_ZXC,Field_QWE from tblStage1; update StagingHeader set inserted = 1 where StageTable = 'tblStage1; COMMIT; END TRY BEGIN CATCH ROLLBACK; END CATCH;';
BEGIN TRY BEGIN TRANSACTION;  insert into tblDest2(FldPLM,FldWER,FldYTE) select Field_PLM,Field_WER,Field_YTE from tblStage2; update StagingHeader set inserted = 1 where StageTable = 'tblStage2; COMMIT; END TRY BEGIN CATCH ROLLBACK; END CATCH;';


Optimizing view to read rows as per usage statistics

This sounds like you want to build a materialized view that satisfies your data condition. A materialized view (previously called a snapshot) physically stores a copy of the data rather than a standard view which is simply a query on the underlying tables.
You should get some benefit when querying as the older records will not be in the view and you can define indexes appropriate specifically to viewing.

There are lots of options and restrictions around refreshing of these views and you will need to do some research to establish the right approach in your situation.
https://docs.oracle.com/cd/B28359_01/server.111/b28326/repmview.htm


What is the best practice for backing up a database in Simple mode?

I'd recommend Ola's scripts for SQL Server maintenance - backups, re-indexing, checkDB, house-keeping, everything! It takes care of naming conventions too! They are very clever, and award winning too!!!! :)

Personally, if your databases are small, I'd keep to a full daily backup schedule. I believe Microsoft recommend that as well - simplicity is best approach.

Ola's scripts:
http://ola.hallengren.com/


Multiple left join where not in another table

According to comment you are not interested in users at all, you just want the person to be client or provider (boolean logic,  so it can be both):

WHERE (client.id IS NOT NULL OR provider.id IS NOT NULL)


Suppose you have a auxiliary Numbers table with integer numbers.

DECLARE @s VARCHAR(5);
SET @s = 'ABCDE';

WITH Subsets AS (
SELECT CAST(SUBSTRING(@s, Number, 1) AS VARCHAR(5)) AS Token,
CAST('.'+CAST(Number AS CHAR(1))+'.' AS VARCHAR(11)) AS Permutation,
CAST(1 AS INT) AS Iteration
FROM dbo.Numbers WHERE Number BETWEEN 1 AND 5
UNION ALL
SELECT CAST(Token+SUBSTRING(@s, Number, 1) AS VARCHAR(5)) AS Token,
CAST(Permutation+CAST(Number AS CHAR(1))+'.' AS VARCHAR(11)) AS
Permutation,
s.Iteration + 1 AS Iteration
FROM Subsets s JOIN dbo.Numbers n ON s.Permutation NOT LIKE
'%.'+CAST(Number AS CHAR(1))+'.%' AND s.Iteration < 5 AND Number
BETWEEN 1 AND 5
--AND s.Iteration = (SELECT MAX(Iteration) FROM Subsets)
)
SELECT * FROM Subsets
WHERE Iteration = 5
ORDER BY Permutation

Token Permutation Iteration
----- ----------- -----------
ABCDE .1.2.3.4.5. 5
ABCED .1.2.3.5.4. 5
ABDCE .1.2.4.3.5. 5
(snip)
EDBCA .5.4.2.3.1. 5
EDCAB .5.4.3.1.2. 5
EDCBA .5.4.3.2.1. 5


The key concept here is that you must create more than one query.  Not all results can be obtained by a single query.  It is possible to accomplish this using SQL subqueries (a.k.a. nested queries) so that the overall query can be saved and edited as a single unit, but subqueries are not supported by the Query Designer--they must be edited in SQL View.

Some database systems support aggregate functions which can directly count distinct values in one query.  Access does not support such a function, so you must first write a query which gets distinct values for the Site column.  A query can always refer to another query in the same manner it refers to a table.

Even though the question specifically mentions Design View (Query Designer), it is traditional on Stack Exchange sites to share queries as SQL text.  It is much too tedious to constantly paste screen shots.  Also, SQL text can be copied and pasted directly into SQL view.  At the least you should know/learn how to switch into SQL view so that you can copy and paste there.  For basic SQL statements, just switch back to Design View.

SELECT Company, Site FROM table GROUP BY Company, Site

or

SELECT DISTINCT Company, Site FROM table

The DISTINCT keyword corresponds to the Design View property Unique Values = True.  Save that query as, for example, CompanySites.

Now count values in the saved query:

SELECT Company, Count(Site) As Sites FROM CompanySites

You may want to consider normalizing the database by adding separate [Company] and [Site] tables with appropriate primary and foreign keys.


Rare error lasts only a second "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146)" 146=ECONNREFUSED

Life has many great mysteries. For the MySQL DBA, one of life's greatest mysteries is your question. It's been this way going on 12 years. In fact, I found a bug report that was resolved when the disappearance of mysql.sock was caused by trying to start mysqld with mysqld already running (a.k.a. Shooting mysqld in the Foot).

I have personally addressed workarounds to this situation many times in the StackExchange:

Jul 02, 2013 : MySQL crash. Unknown cause. Signal 11 (ServerFault)
Apr 22, 2013 : /usr/libexec/mysqld: Normal shutdown, but my team doesn't do that?
Mar 06, 2013 : How to properly kill MySQL?
Feb 28, 2013 : mysql restart won't kill child processes on CentOS
Dec 14, 2012 : Percona-server time out on /etc/init.d/mysql start (ServerFault)
May 08, 2012 : How to properly stop MySQL server on Mac OS X?

The mystery has to do with mysql.sock (the MySQL Socket file) disappearing. Once this happens, you cannot connect to mysqld using root@localhost. In fact, no user defined with host='localhost' in the mysql.user table can connect. TCP/IP connections can still be used. The workaround is to connect to mysqld using TCP/IP.

Try create the user root@'127.0.0.1'. From the mysql client you then do this:

mysql -uroot -p -h127.0.0.1 --protocol=tcp

The only way to bring back mysql.sock is to restart mysqld WHEN mysqld IS NOT RUNNING.

You should make sure you explicitly define the mysql.sock file in /etc/my.cnf

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

Of course, go create the folder

mkdir /var/run/mysqld
chmod 777 /var/run/mysqld

Give it a Try !!!

UPDATE 2013-12-06 10:17 EST

Based on your comments...

  I included the log, please let me know if you know how to increase the detail

You are using MySQL 5.0.27, which means you are using a very old mysqld (with a truck load of bugs) and an old InnoDB Storage Engine. There may not be additional detail available. I also noted you are using a Source Distribution rather than RPM installed binaries.

  bring back mysql.sock is to restart mysqld. This does not happen for me. I never have to do anything. The problem lasts a second and goes away.

If the problem remains for only a second or two, I can see what is happening. When you run service mysql start, you are actually running mysqld_safe. At the bottom of mysqld_safe, there is an infinite loop. In the infinite loop, it calls mysqld and waits for a return value. Certain return values from mysqld will make mysqld_safe terminate (normal shutdown,condition where mysqld can't start again). Other return values will cause mysqld_safe to loop and try mysqld again.

SUGGESTION

You should upgrade to MySQL 5.6 to get the latest version of mysqld and the InnoDB Storage Engine. If the problem still persists, at least there will be less baggage due to better quality MySQL binaries. That way, you can rule out mysqld and look for other external factors. Also, you should download RPM binaries rather than compiling source because RPM version usually have more optimizations in place.

UPDATE 2013-12-06 10:39 EST

If you look back at the two links at the top of my answer

LINK#1 to Bug Report
LINK#2 to Bug Report

you will learn that the first link references MySQL 4.0.20, and the second link was from a bug report thread that said the version in question was MySQL 3.22.32 (the final release of MySQL 3 was 3.23.58)

Issues with mysql.sock has been around 13 years now due to shooting yourself in the foot (starting mysqld even though it is running), internal factors (very old version or clunky version of MySQL) or some external condition (lack of RAM or other OS resources).

Then, it looks like you found an internal factor. To verify that mysqld is correcting itself, run SHOW GLOBAL VARIABLES LIKE 'uptime';. If the number continues increasing and never resets, then you can rightly blame the clunky MySQL binary for not being more elaborate. MySQL AB was probably solving some internal issue and may have left some debug statements or trace points in the binary

UPDATE 2013-12-06 11:24 EST

  Just looked inside mysqld_safe. There is code to print mysqld ended and mysqld restarted. If mysqld did indeed terminate, mysqld_safe would have logged it. If mysqld was restarted by mysqld_safe, the fresh/new instance of mysqld would print out the log information including version number. Since the version number was printed only once, it is clear that mysqld was never ended and never restarted. – George Bailey 13 mins ago

  Just FYI, I found out that 146 is ECONNREFUSED, I edited it in to my question. – George Bailey 11 mins ago

Then, it looks like you found an internals issue. The source-compiled MySQL 5.0.27 mysqld may be just printing debug messages after an internal recovery. To verify mysqld is OK in terms of being up, just run SHOW GLOBAL VARIABLES LIKE 'uptime';. If the number does not reset and just keeps increasing, then mysqld is correcting itself from within. Given the old build you are using and the way it was compiled, I would not expect mysqld to be more forthcoming on its own.

You should see if mysqld was started with debug enabled. If not, try setting debug in my.cnfand see if it can get more verbose about its internals. I hope this reveals what the problem may be.


Populate a table on a selective replication slave

Do these two steps while nothing is being added to the table:

Manually copy the table to the Slave(s) (even if empty -- need table definition).
Remove the replacate-ignore setting.


is "create schema" the same way of saying "create database"?

This is for SQLServer, databases and schemas are different in other DBMS like Oracle.

A database and a schema are two different concepts.
A database can contain multiple schemas, for example the default schema 'dbo'

You can address a table by specifiying database.schema.tablename.

Schemas are often used for security reasons, cause you can have one database, multiple schemas, and you can give permissions on the schemas. That way you don't have to specify permissions for each of the objects in the database.
Eg. HR users have select permissions on the entire HR schema, while IT only has select permissions on the IT schema

It also gives you a nice grouping of objects that belong together.

Some more useful information:
http://www.abstractnew.com/2014/11/database-vs-schema-in-database-systems.html
https://www.brentozar.com/archive/2010/05/why-use-schemas/


Knowing everything you have specified, I suggest you to create this model:

/********Characters********
this is the table where all the players exist
*/
CREATE TABLE characters (
    id      <id_type> NOT NULL,
    name    <column_type> ,
    other   <column_type>
);
ALTER TABLE characters ADD CONSTRAINT c_pk PRIMARY KEY ( id );

/********Item Type********
this is the type of item; i.e. poison, sword,...
*/
CREATE TABLE item_type (
    id      <id_type> NOT NULL,
    name    <column_type>,
    other   <column_type>
);
ALTER TABLE item_type ADD CONSTRAINT t_pk PRIMARY KEY ( id );

/********Items********
this is every single and unique existence of any item in the game...
*/
CREATE TABLE items (
    id       <id_type> NOT NULL,
    typeid   <id_type> NOT NULL,
    other    <column_type>
);
ALTER TABLE items ADD CONSTRAINT i_pk PRIMARY KEY ( id );
ALTER TABLE items ADD CONSTRAINT i_t_fk FOREIGN KEY ( typeid ) REFERENCES item_type ( id );

/********Inventory********
this relates all players with all of their items
*/
CREATE TABLE inventory (
    characterid   <id_type> NOT NULL,
    itemid        <id_type> NOT NULL
);
ALTER TABLE inventory ADD CONSTRAINT in_pk PRIMARY KEY ( characterid, itemid );
ALTER TABLE inventory ADD CONSTRAINT in_c_fk FOREIGN KEY ( characterid ) REFERENCES characters ( id );
ALTER TABLE inventory ADD CONSTRAINT in_i_fk FOREIGN KEY ( itemid ) REFERENCES items ( id );

I hope this solution helps you to better approach what you are looking for your game.


Keep in mind that you could get more than 1 row just in case there are two or more records with the same DateGps.

SELECT lp.Id
FROM   LastPosition lp
JOIN   (SELECT  Serial,
                MAX(DateGps) AS maxi
        FROM    LastPosition
        GROUP BY Serial) t1
ON      lp.Serial = t1.Serial
AND     lp.DateGps = t1.maxi;


This is your query:

SELECT id
FROM  `posts`
WHERE user_id =123529745 and source=1 and visible=1
ORDER BY created DESC

You are doing only equality comparisons in the where and then sorting by created.

A single index on:  posts(user_id, source, visible, created, id) should allow the entire query to be satisfied only by the index -- first finding the right rows, then sorting by them, and then fetching the id.


Slave can not handle replication events with the checksum that master is configured to log

Is your slave of older version than master? Say 5.5 or less.
As you say set global binlog_checksum='NONE'; fails on slave => you're on older slave and that is not required on slave as well.


Is it possible that a same INSERT operation performance will suddenly degrade?

It could be blocked by another process, it could have forced an autogrow, something else may have taken the CPU's attention, data may have changed affecting cardinality, the plan could have been flushed, who knows?

Basically, yes, the same query can behave differently at different times due to a variety of environmental factors, both inside SQL Server and external.


Can I replace these left joins with a union or some other optimization?

@Jorge Campos is correct

SELECT o.name, sum(tmp.in_group_a), sum(tmp.in_group_b)
FROM ((
   SELECT a.object_id, 1 as in_group_a, 0 as in_group_b
   FROM group_a a
   ) UNION (
   SELECT b.object_id, 0 as in_group_a, 1 as in_group_b
   FROM group_b b
   )) tmp
INNER JOIN object o ON (tmp.object_id = o.object_id)
GROUP BY o.name;


How to import data from a dump file to a specific schema in postgres?

probably the easiest is to first rename the existing schema_1

restore the dump

rename the new schema_1 to schema_3

restore the original schema_1 to its original name

eg psql:

 alter schema schema_1 rename to schema_1_backup;
 create schema schema_1;
 \i schema_1_dump
 drop schema_3 cascade;
 alter schema schema_1 rename to schema_3;
 alter schema schema_1_backup rename to schema_1;

This is going to annoy other users.

The other way is to change all the SQL commands to say  'schema3' instead of 'schema1'


Why does Oracle store time in DATE column?

Oracle's DATE datatype behaves as both an ANSI DATE and TIME datatype. This was a design decision that only Oracle knows the reasoning for.

I'll add that Oracle isn't the only RDBMS that doesn't follow ANSI SQL standards in this way.

Obviously, you can remove the time portion with TO_CHAR(departure_date,'DD-MM-YYYY').


Next quarter without using Quarter function

mysql> SELECT CONCAT_WS('-', YEAR(NOW()), MONTH(NOW()), '01') + INTERVAL
       3 * QUARTER(NOW()) + 1 - MONTH(NOW())
       MONTH;
--> 2016-07-01

If you don't need the date of the beginning of the next quarter, but need the string '3rd quarter July-September', then use a CASE clause.

Without QUARTER:

SELECT CONCAT_WS('-', YEAR(NOW()), MONTH(NOW()), '01') + INTERVAL
       3 * FLOOR(MONTH(NOW())/3+1) + 1 - MONTH(NOW())
       MONTH;

Because QUARTER(x) is the same as FLOOR(MONTH(x)/3+1).


KISS principle:  Do you have the storage that itself offers you the required availability level (i.e. disk array)? And you do have RMAN backups with a recovery catalog (you need it anyway, for DUPLICATE), don't you? If yes and yes, my version is: no secondary control file at all, one member per each redo group, as there are no secondary datafiles (quite obviously, even for system01.dbf) or no secondary archivelogs.

Otherwise your database is unnecessarily doing stuff that belongs on the storage level. I would just leave storage things on the storage level - the mirror is done there, and it is done effectively. Or, if you want cross-site mirror, do it on ASM level. Database is occupied with database-related processing. The software mirroring on database level only makes sense if you use bare hard disks.

Most people conservatively stick to having three control files, though. Probably Oracle support would also recommend you to use three.

Not only I've never practically benefited from having a second or a third control file; I'm unable to think of a scenario when secondary copy would be justified (i.e. it provides some benefit for  the lack of simplicity). Logical corruption in control file would be propagated to a second control file, just as in case of hardware mirroring. Physical corruption wouldn't be propagated in either case. If storage fails, you lose redo or data - you need to restore via RMAN anyway. You lose control file, you can restore it with RESTORE CONTROLFILE, which only costs extra minute at most. If you've lost redo, the recovery is incomplete. If you didn't lost redo (but lost all control files), the recovery is complete de facto, but Oracle insists on OPEN RESETLOGS (loss-less in such case).


Minimum user rights for connecting MS Sql database remotedly

Check for "CONNECT" rights

SELECT SUSER_NAME(grantee_principal_id), *
FROM sys.server_permissions WHERE type = 'COSQ'
GO
USE MYDB
GO
SELECT USER_NAME(grantee_principal_id), *
FROM sys.database_permissions WHERE type = 'CO'

To fix as needed

USE master
GO
GRANT CONNECT SQL TO myLogin
GO
USE MYDB
GO
GRANT CONNECT TO MyUser
GO


Getting percentages for each day/category

SELECT Request_at AS `Date`,
       SUM(CASE WHEN Status LIKE 'cancelled%'
                THEN Freq
                ELSE 0
                END) / SUM(Freq) AS `% cancel`
FROM source_table
GROUP BY Request_at


How to install oracle 11g XE and use the data files of another instance (like Postgres)?

I see no reason you can't do this.

Typically, you would perform the following steps.

Move the *.dbf files found in %ORACLE_BASE%\oradata\ to a safe place.
Move the files found in %ORACLE_BASE%\fast_recovery_area\ to a safe place.
Move the spfile found in %ORACLE_HOME%\database\spfileXE.ora to a safe place.
Install a new Oracle instance and
Configure it with oracle-xe configure command.
Shut the instance down.
Move the data files, control file, spfile and online redo log files back to their previous locations.
Star the instance.

You would also need to make sure that you specified the same settings during installation as you used with your previous database (ORACLE_HOME, ORACLE_SID, listener port etc.), otherwise your instance won't start. Ideally I would recommend you doing silent installation with response files (described in Oracle Database Express Edition Installation Guide for Microsoft Windows). This would ensure that you've used the same settings across operating system upgrades/reinstall.


Are there best practices or standardized syntax for writing transformation logic?

SQLish

Honestly, your best bet is probably ANSI-compliant SQL, because it can eliminate the ambiguities which you've mentioned.

If there's any trouble with it needing to be written in "natural language", just add in the apprpriate prepositions and such...

Select the maximum paid amount, the customer account number, and the customer phone number
from the 'Invoices' table joined to the 'Customers' table,
joined on the condition that
the 'Invoices'.customer_id column equals the 'Customers'.customer_id column,
grouping by the customer account number and customer phone number.


Tsql Query speed slow due to Or within where clause, causing index scan instead of seek

This is what I call the "kitchen sink" stored procedure - you want one procedure that handles all possible combinations of search criteria that a user might enter. It is virtually impossible to make SQL Server derive a single execution plan that will be optimal and efficient for all of these combinations - I don't care what kind of tricks you might think ISNULL can pull that COALESCE or OR can't.

The solutions I usually attempt, in this order, are:

Add OPTION (RECOMPILE) to the query. Yes, you pay a compilation cost every time, but you will get the right plan given the parameters that are supplied and their values.
Use dynamic SQL. Now you will be able to cache multiple different plans based on the different parameters passed. Combine this with the server-level setting optimize for ad hoc workloads (more info from Kimberly Tripp here and here) so that only versions of the plan that are used more than once are fully cached. Example:

DECLARE
  @PERSON_ID         INT = 0,
  @ITEM_ID           INT = 0,
  @ITEM_VERSION      INT = 1,
  @ITEM_SUB_NAME     VARCHAR(250),
  @ITEM_SUB_SUB_NAME VARCHAR(250);

DECLARE @sql NVARCHAR(MAX) = N'',

SET @sql = N'SELECT ID, PERSON_ID,
   ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION,
   ISNULL(ITEM_SUB_NAME, '''') AS ITEM_SUB_NAME,
   ISNULL(ITEM_SUB_SUB_NAME, '''') AS ITEM_SUB_SUB_NAME,
   ISNULL(ITEM_DATE, ''19000101'') AS ITEM_DATE
FROM dbo.PERSON_TBL AS s WITH (NOLOCK)
WHERE ITEM_VERSION '
+ CASE WHEN @ITEM_VERSION <> 1 THEN
   N' = @ITEM_VERSION' ELSE N' IS NULL' END
+ CASE WHEN @PERSON_ID <> 0 THEN
   N' AND PERSON_ID = @PERSON_ID' ELSE N'' END
+ CASE WHEN @EMPLOYEE_ID <> 0 THEN
   N' AND EMPLOYEE_ID = @EMPLOYEE_ID' ELSE N'' END
+ CASE WHEN @ITEM_SUB_NAME IS NOT NULL THEN
   N' AND ITEM_SUB_NAME = @ITEM_SUB_NAME' ELSE N'' END
+ CASE WHEN @ITEM_SUB_SUB_NAME IS NOT NULL THEN
   N' AND ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME' ELSE N'' END
ORDER BY PERSON_ID, ITEM_SUB_NAME;';

EXEC sys.sp_executesql @sql,
  N'@PERSON_ID INT, @ITEM_ID INT, @ITEM_VERSION INT,
    @ITEM_SUB_NAME VARCHAR(250), @ITEM_SUB_SUB_NAME VARCHAR(250)',
  @PERSON_ID, @ITEM_ID, @ITEM_VERSION,
  @ITEM_SUB_NAME, @ITEM_SUB_SUB_NAME;

If you find that parameter sniffing is still an issue even when you have individual plans for each combination of parameters provided (due to vastly different cardinality based on the actual values), you can add OPTION (RECOMPILE) inside the dynamic SQL, too - you once again pay the compile cost but it should be better when you have a much simpler where clause.

Some resources about this pattern:

The "Kitchen Sink" procedure (video)
#BackToBasics: An Updated Kitchen Sink Example

Paul White has a great post worth reading too:

Parameter Sniffing, Embedding, and the RECOMPILE Options

As an aside:

Stop using single quotes for alias delimiters. It makes them look like strings and is deprecated in some scenarios, never mind that delimiters are not even necessary here. (I talk about this issue here.)
Stop using ambiguous, regional date formats like m/d/yyyy (or is that d/m/yyyy?).
Always use the schema prefix.
Be very careful about using NOLOCK as a magic turbo button.
My thoughts on COALESCE vs. ISNULL.


It would be a large script as a Job consists of data entries in multiple tables/views:

msdb.dbo.sysjobs_view
msdb.dbo.sysjobsteps
msdb.dbo.sysjobschedules
-- you probably dont need these as you are copying to the same server
msdb.dbo.sysschedules
msdb.dbo.syscategories
sys.server_principals
msdb.dbo.sysOperators

Create Jobs using following SPs:

msdb.dbo.sp_add_job
msdb.dbo.sp_add_jobserver
msdb.dbo.sp_add_jobstep
msdb.dbo.sp_add_jobschedule

Look in MSDN to work out what arguments to pass


Is it much more expensive to use procedures that get lists and deriving a count in the application vs. a separate stored procedure for the count?

Not 100% sure I understand your question but - if you are already selecting the data, will also selecting a count be that much more expensive? No, I don't think you'd have an issue doing that. Selecting just a count should incur the same cost as far as the scans/seeks and joins/filtering/aggregating you had to do in your query but also show some time saved in processing/formatting and sending the actual rows as well. If you your select takes 10ms, I'd expect the count to also take 10 or less ms.

Alternatively you can get the rowcount returned by operations as well and return that without even incurring the additional 10 ms.

This SO question deals with the same basic question I think you are driving at and gives a few different answers on technique.


SQL Server 2016 - write output file to mapped Samba share (CentOS 7)

In order to map a network drive under the SQL Server Service Account, you need to login to the server as the service account.  Then use net use /persistent:yes to connect the share.  This should make the share available to the SQL Server Service Account automatically each time it logs into that server.

Having said all that, instead of using a drive letter for the share, simply write directly to the Linux SMB share via the UNC name... so \\linux-machine\share\some-file-name.csv

Alternately, as Aaron Bertrand alluded to in a comment, write a utility to query the data from SQL Server and write directly to the samba share on Linux.


Oracle - Tracking events that lead to a deadlock

Deadlocks are automatically detected by the engine, and one of the statements will be rolled back.  You can find queries by examining alert.log file (it will have a reference to trace file where detailed deadlock information is stored). It's not gonna give you the full stack of queries per sessions that contribute to the deadlock though .

The query you are using shows deadlock until it's resolved by the database.
You can try checking not just v$session.sql_id, but v$session.prev_sql_id (previous query executed within the session context). If you're running Enterprise Edition (and as far as I remember Diagnostic+Tuning Packs) you may examine v$active_session_history view.

If you have an option to modify client applications that use the database, you can use DBMS_APPLICATION_INFO package to identify modules and actions that are being executed.


I created a sample.js file like the sample below:
db=db.getSiblingDB('DbName');

printjson( db.getCollection('CollectionName').find().count() );

then I run this script below on cmd:
mongo --host IP:PORT --username username --password password --authenticationDatabase admin --quiet sample.js > sample.csv


How to restore IBM DB2 database from AIX to Windows?

You cannot restore a DB2 database from AIX into Windows because of the endian. These are not compatible architectures.

For more information, check the InfoCenter: http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.ha.doc/doc/c0005960.html

The only way to retrieve the data between these platforms is via db2look + db2move.

db2look http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002051.html
db2move http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0002079.html


Just change it to INNER JOIN

SELECT u.username,SUM(kort) as total
FROM user as u
INNER JOIN userresult as r
on u.userID = r.userID
WHERE r.date >= '$date'
group by u.userID
order by total DESC;

or put the WHERE clause in the HAVING aggregation

SELECT u.username,SUM(kort) as total
FROM user as u
LEFT JOIN userresult as r
on u.userID = r.userID
WHERE r.date >= '$date'
group by u.userID
HAVING SUM(kort) > 0;

or put the whole thing in a subquery and test the total outside the subquery

SELECT * FROM
(
    SELECT u.username,SUM(kort) as total
    FROM user as u
    LEFT JOIN userresult as r
    on u.userID = r.userID
    WHERE r.date >= '$date'
    group by u.userID
    order by total DESC
) A WHERE total > 0;

The simplest and most sensible is the INNER JOIN


Oracle: guaranteeing unmodified database state with queries?

It's possible for a SELECT statement to modify the database, for example:

select mysequence.nextval increments a sequence
select myfunction executes a PL/SQL function which could have any sort of side effects

The last point is a bit of a corner case since it only works with autonomous transactions, otherwise errors such as

ORA-14551: cannot perform a DML operation inside a query
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

are returned:

SQL> set serveroutput on
SQL> create table t1 (x number);

Table created.

SQL> insert into t1 values (42);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

         X
----------
        42

SQL> create or replace function f1 return varchar2 is
   pragma autonomous_transaction;
begin
   execute immediate 'truncate table t1';
   return 'Table t1 truncated';
end f1;
/
  2    3    4    5    6    7
Function created.

SQL> select f1 from dual;

F1
--------------------------------------------------------------------------------
Table t1 truncated

SQL> select * from t1;

no rows selected


Why are you determining a member's absence through another member's presence?

I guess there must be an events table in your schema. And I would expect it to have a column called something like event_date. It's the event_date column that you should use to determine which events a member could visit (members.registration_date < events.event_date).

After getting the list of members and their respective valid events, you can anti-join it to the attendance table to see which member missed which (or how many) events.

Here's an example query illustrating what I just said. It implements the anti-join using LEFT JOIN + WHERE IS NULL check:

SELECT
  m.member_id,
  e.event_id
FROM       members    m
INNER JOIN events     e ON m.registration_date < e.event_date
LEFT  JOIN attendance a ON m.member_id = a.member_id
                       AND e.event_id  = a.event_id
WHERE a.member_id IS NULL
  AND ... /* some other conditions to filter members and/or events, if necessary */

Or here's another example showing how you could count total events available to a member and those actually attended by him/her:

SELECT
  m.member_id,
  COUNT(e.event_id) AS total_events,
  COUNT(a.event_id) AS attended_events,
FROM       members    m
INNER JOIN events     e ON m.registration_date < e.event_date
LEFT  JOIN attendance a ON m.member_id = a.member_id
                       AND e.event_id  = a.event_id
WHERE /* some conditions as necessary */

Note that in the last example there's no IS NULL check. It's because that query needs to get all the events per member. Missed events are just not counted by the COUNT() function (because the corresponding a.event_id value contains NULL in those cases).

UPDATE

Since, as per your comment, the events are recurring (and thus have no fixed dates), I would suggest adding and using an event_calendar table, populated beforehand either manually or programmatically. Naturally, the events table in the above examples would be replaced with event_calendar.

Alternatively, if maintaining an event calendar is not an option, you could replace events in your queries with

(
  SELECT DISTINCT
    event_id,
    attendance_date AS event_date
    /* or, if they are not purely dates:
    CAST(attendance_date AS date) AS event_date
    */
  FROM attendance
) e

(basically, @ypercube's suggestion with dates thrown in).


How to free space after I drop a column of a table in SQL Server?

You can run a shrink on your database or its individual files, but in general it's not recommended to do because it's a heavy operation and generally wasted efforts because the space will likely be re-used by your database anyway which will result in a growth operation which is also not a lightweight operation.
When you drop the column, the space it was previously using on disk will be recycled and re-used by your database as it needs more space anyway. It isn't just wasted space just because the OS shows it consumed from the disk. SQL Server manages the space it reserves from the disk internally for the very reason of reducing those heavy growth operations I mentioned earlier. So no need to worry about shrinking.
You can also find more good information on why shrinking is a wasteful operation here.
Also, last thing to note is that a VARCHAR(MAX) column doesn't consume the entire ~2 GB it's capable of maxing out at, for each row, unless you were filling it to the max. It's approximately equal to 1 byte per character you're actually using in it, in general (not counting any additional space saving features going on under the hood like compression). So the space savings you might anticipate by dropping it is potentially less than you think.


Get the data, order by C.
Compute a temporary column: if B equals to B in the previous row (rows ordered by C), let that column be 0, otherwise 1. This column is used for the following: when B is the same as B in the previous row, we increase the value in D by 0, otherwise we increase the value by 1. Finally, get A, B, and the SUM of the temporary column up to the current row (this requires the analytic version of SUM), this will give us D.

The above translated to SQL:

with data as
(
  select 1 a, 132 b, 1 c from dual union all
  select 1 a, 155 b, 2 c from dual union all
  select 1 a, 132 b, 3 c from dual union all
  select 1 a, 132 b, 4 c from dual union all
  select 1 a, 155 b, 5 c from dual union all
  select 1 a, 155 b, 6 c from dual union all
  select 1 a, 132 b, 7 c from dual
)
select * from data order by c;

         A          B          C
---------- ---------- ----------
         1        132          1
         1        155          2
         1        132          3
         1        132          4
         1        155          5
         1        155          6
         1        132          7

Get the expected result:

with data as
(
  select 1 a, 132 b, 1 c from dual union all
  select 1 a, 155 b, 2 c from dual union all
  select 1 a, 132 b, 3 c from dual union all
  select 1 a, 132 b, 4 c from dual union all
  select 1 a, 155 b, 5 c from dual union all
  select 1 a, 155 b, 6 c from dual union all
  select 1 a, 132 b, 7 c from dual
)
select
  a, b,
  sum(d) over (order by c rows between unbounded preceding and current row) as d
from
(
select
  a, b, c,
  case when lag(b) over (order by c) = b
    then 0
    else 1
  end as d
from
  data
)
order by c
;

         A          B          D
---------- ---------- ----------
         1        132          1
         1        155          2
         1        132          3
         1        132          3
         1        155          4
         1        155          4
         1        132          5


Failed to insert records into table in different server using sql server agent

I believe you are seeing two different errors on your history report.

You should validate the step output file on the job step(s).
You should validate the linked server permissions & authentication.

Recreating the step output error

Testing with a location that does not exist

Create a jobstep that tries to access a step output folder location that does not exist

Regular T-SQL job steps might report the error but not fail the job, I used a maintenance plan to force the failure here

Start the job

Resulting error:

  Executed as user: AgentServiceAccount. Unable to open Step
  output file.  The step failed.

Testing with insufficient permissions

The same error is returned when no permissions are granted

  Executed as user: AgentServiceAccount. Unable to open Step
  output file.  The step failed.

Recreating the linked server permission error in combination with the step output error

Create a linked server

Authenticate with a non existing login

Create a job and jobstep to select from the newly created linked server

Point to a non existing output file

With the final resulting error on job execution matching yours

  Executed as user: AgentServiceAccount Login failed for user
  'nonexistinglogin'. [SQLSTATE 28000] (Error 18456)Unable to open Step
  output file.  The step failed.

Validate the job step output & the linked server permissions and authentication and you should be good to go.


What is the best way to create a database diagram for SQL Azure without having to install SQL Server?

UPDATE: The database diagrams were added back into SSMS 18.1(link)

UPDATE: the database diagram feature

  Deprecated and removed features in 18.0 (GA)
  ...
  Database Diagrams
  https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms

The feature was also removed from Visual Studio 2019 SSDT.

Original Answer

The latest version of SSMS (as of v16) supports creating database diagrams for Azure SQL databases. You can download it here:

https://msdn.microsoft.com/en-us/library/mt238290.aspx


Why does pg_last_xact_replay_timestamp() sometimes return extremely old timestamps?

Answering this from the future - when I have seen this, it means there have not been any writes to the primary (upstream) database in a while.
If there are no recent transactions on the primary, there are no recent transactions to replay on the follower, so pg_last_xact_replay_timestamp() will just report the time of the last write to primary.


Some DBMS are omnipotent and merciless. They give numerous warnings and errors in all kinds of situations when a user is attempting the slightest deviation from the correct behaviour - defined by the standards and the DBMS documentation.

MySQL is merciful and all-loving. She is trying to help everyone. Even if someone is comparing a string to an integer, she's thinking that the user may know better what he is doing and that maybe he is right. Not wanting to let him go in despair, she is trying - against all consistency and integrity laws - to compare the 2 incomparable things.

And - oh, the miracle - sometimes the 2 things are the same after all! What if one is the number 182 (which comes from an id column, stored in 4 bytes) and the other is the string 182^%^&$$%$%$sjfhjs ^%^&%&*^$^%$^$ which needs 28. They look the same anyway, when the second is truncated from those silly characters!


Is there any advantage to putting some columns in another table, or should I just put them in the same table?

If I were you, I would include the new fields within the location table - while at the same time making considerable use of Declarative Referential Integrity.

I would do something like this (I haven't included all of your new fields - you can check out the fiddle here):

Create and populate two tables, crime and weather:

CREATE TABLE crime (cr_rating VARCHAR (25) PRIMARY KEY);
INSERT INTO crime VALUES ('Low'), ('Medium'), ('High');

and

CREATE TABLE weather (weather_type VARCHAR (50) PRIMARY KEY);
INSERT INTO weather VALUES ('Scorching'), ('Sunny'), ('Mild'), ('Overcast'), ('Drizzle'), ('Rainy'), ('Stormy'), ('Hurricane');

Then create and populate a job table - see discussion below:

CREATE TABLE job (job_id INTEGER PRIMARY KEY, job_type VARCHAR (30) NOT NULL);
INSERT INTO job (job_type) VALUES ('Unknown'), ('Unemployed'), ('Blue collar'), ('White collar'), ('Manager'), ('Self-employed');

No need to use AUTOINCREMENT for reasons outlined here. Developers tend to compulsively use AUTOINCREMENT - possibly a hangover from MySQL standards and possibly some idea that it must somehow be "better" than the simpler code above.

Then create and populate the main location table:

CREATE TABLE the_location
(
  loc_key INTEGER PRIMARY KEY,
  loc_coords VARCHAR (50) NOT NULL, -- not sure how you store these in your system
  loc_status INTEGER CHECK (loc_status IN (0,1)),
  loc_rating INTEGER CHECK (loc_rating BETWEEN 1 AND 10),
  loc_crime VARCHAR (25) NOT NULL,
  -- could use a CHECK CONSTRAINT, but use a lookup table instead
  -- note also that the types should match exactly
  loc_weather VARCHAR (50) NOT NULL,
  -- location_rating_tourism, -- same lookup table idea here (Good, Medium, Poor, Death-zone)
  -- location_rating_income, -- list of social classes table
  -- location_rating_jobs
  CONSTRAINT ct_loc_crime FOREIGN KEY (loc_crime) REFERENCES crime (cr_rating),
  CONSTRAINT ct_loc_weather FOREIGN KEY (loc_weather) REFERENCES weather (weather_type)
);

INSERT INTO the_location
(loc_coords, loc_status, loc_rating, loc_crime, loc_weather)
VALUES
('coord_1', 0, 7, 'Medium', 'Rainy'),
('coord_2', 1, 7, 'High', 'Mild');

I've left some of the other fields out, but if there is one and only one of these things associated with your location, then you can simply have them as a single field in the location table as discussed above.

However, consider the job table - corresponding to the location_rating_jobs record in your location table. Suppose there is a certain percentage of Unknown job_types, a certain percentage of Unemployed &c... per location?

In this case, you can use an Associative Entity (otherwise known as a joining or linking) table to cope with such a relationship. I would do something like the following:

CREATE TABLE location_job
(
  lj_loc INTEGER NOT NULL,
  lj_job      INTEGER NOT NULL,
  lj_percent  INTEGER DEFAULT 0 NOT NULL,
  PRIMARY KEY (lj_loc, lj_job),
  CONSTRAINT ct_lj_loc FOREIGN KEY (lj_loc) REFERENCES the_location (loc_key),
  CONSTRAINT ct_lj_job FOREIGN KEY (lj_job) REFERENCES job(job_id)
);

and then populate it respecting the FOREIGN KEY constraints.

INSERT INTO location_job
VALUES
(1, 1, 10), (1, 2, 10), (1, 3, 10), (1, 4, 10), (1, 5, 10), (1, 6, 50),
(2, 1, 30), (2, 2, 30), (2, 3, 10), (2, 4, 10), (2, 5, 10), (2, 6, 10);

And then, in order to find out the percentage of job_type by area, I would run the following query:

SELECT
  tl.loc_key, tl.loc_coords,
  lj.lj_loc, lj.lj_job, lj.lj_percent,
  j.job_id, j.job_type
FROM the_location tl
JOIN location_job lj ON tl.loc_key = lj.lj_loc
JOIN job j ON lj.lj_job = j.job_id
ORDER BY tl.loc_key, j.job_id;

Result:

loc_key loc_coords  lj_loc  lj_job  lj_percent  job_id  job_type
1   coord_1 1   1   10  1   Unknown
1   coord_1 1   2   10  2   Unemployed
1   coord_1 1   3   10  3   Blue collar
..
.. results snipped for brevity - see fiddle
..

Obviously, in a real query, the duplication of values would be eliminated and you would only have one of each necessary one - I've kept them all here so that you can effectively see the process of joining across tables.

The only area that falls outside the area of standard (current) SQL is the fact that the percentages per area cannot be > 100 (*) (Firebird can have queries in CHECK constraints - but it's about the only server that I know of which can). This is known as ASSERTIONs or "Declarative multi-row constraints" which have not yet been implemented by the major vendors - you'd have to use TRIGGERs for this - or maybe C code in SQLite.

(*) or perhaps your model would allow for those who were both Self-employed and Managers?

If this doesn't answer your question, please let me know. p.s. welcome to the forum! :-)


Conditional Where Statement Involving a NULL value?

Use an OR to check if either the parameter matches the value, or both the parameter and the value are NULL, like this:

WHERE
(
    [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status
    OR
    ([TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL AND @Status IS NULL)
)

You could also use a CASE statement as your IF if you really wanted to, but I think it's easier to read and understand the OR syntax above, so would prefer to use that.

WHERE
(
    CASE WHEN @Status IS NULL AND [TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL THEN 1
       WHEN [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status THEN 1
       ELSE 0 END
) = 1


Export pipe-delimited data with progress db

If you're ok with writing some code:

OUTPUT TO VALUE("filename.d").

FOR EACH table-name:
EXPORT DELIMITER "|" table-name.
END.

OUTPUT CLOSE.

It's possible to write something that's more automated than this if you're interested.


Can queries that users ORDER BY using date to sort data be non deterministic?

If the date you are sorting by has repeat values in it and are not sorting by any other columns the order you get them in will be arbitrary.

Result set 1

email|date
aaa@gmail.com|2019-01-01
bbb@gmail.com|2019-01-01
ccc@gmail.com|2019-01-01

Result set 2

email|date
ccc@gmail.com|2019-01-01
bbb@gmail.com|2019-01-01
aaa@gmail.com|2019-01-01

If you just have an order by on date the database can return either result set as is just ordering by the date field and pulling in records as it gets them regardless of order.

If you need a consistent order in the results you are going to need to add more fields to the order until you can get a more unique set of fields to order by.

For your query I would update it to also order by _id so you will have a consistent result set

SELECT id AS "_id",
       cpf,
       email,
       name,
       agency,
       account,
       "createdDate"
FROM "NextUsers",
     "Users"
WHERE "userId"="Users".id
ORDER BY "createdDate",
         id
OFFSET $1
LIMIT 500;


What to do in times of data inconsistency in Master-Master Replication in MySQL?

First point is that you should only write to one Master at a time, with the other set as the 'backup' option in HAPROXY.

This will prevent you from writing to an out of date master (unless there is a problem with the main one, in which case you probably don't have an option anyway).
By writing to both masters simulataneously you are opening yourself up to a whole load of problems. If you need to write to both you should probably look at Group Replication or Galera Cluster.

Second point, I assume you have taken precautions such as setting the auto_increment_increment and auto_increment_offset values on the two databases? So that in the event you do write to them both (during testing failover etc) you won't get duplicate entries on your auto_increment values.

If you want to test for Seconds behind (and you have Linux servers) you can use a simple script adapted from here: http://sysbible.org/2008/12/04/having-haproxy-check-mysql-status-through-a-xinetd-script/

We use it to check various values to determine if a slave database is up to date and available for use.


Can't backup a Postgres database: FATAL: Peer authentication failed for user "postgres"

In pg_hba.conf, change this line:

local   all             all                                     peer

to:

local   all             all                                     password

Then you can log in locally using a password.


Using postgres_fdw to connect from one read replica to another

You can create replicas using logical decoding.
Check pglogical https://github.com/2ndQuadrant/pglogical

When creating replica using logical decoding it's not read-only.Might help in your case.


An ISO compliant query that will run on all major RDBMS without change - although the performance will not scale to large datasets.

    select a.tournamet, a.board, a.player, a.score,
           (
           (select count(*)
              from data b
             where b.tournamet=a.tournamet
               and b.board=a.board
               and b.score>a.score)
           + 1 + total -
           (select count(*)
              from data c
             where c.tournamet=a.tournamet
               and c.board=a.board
               and c.score<a.score)
           )/2 rank,
           total
      from data a
      join (select tournamet, board, count(score) total
              from data x
          group by tournamet, board) y
                on y.tournamet=a.tournamet
               and y.board=a.board
  order by tournamet, board, rank, player;

For MySQL specifically, subqueries are not allowed in the FROM clause, so the query is slowed down even more by putting the total count into yet another scalar, SELECT-level query:

select a.tournamet, a.board, a.player, a.score,
       (
       (select count(*)
          from data b
         where b.tournamet=a.tournamet
           and b.board=a.board
           and b.score>a.score)
       + 1 +
       (select count(*)
          from data c
         where c.tournamet=a.tournamet
           and c.board=a.board)
       -
       (select count(*)
          from data c
         where c.tournamet=a.tournamet
           and c.board=a.board
           and c.score<a.score)
       )/2 rank
  from data a

And below is a specific MySQL solution that only requires 2 table scans.  Some have mentioned that multiple MySQL variables at the same level doesn't always work, but it's worked everywhere I've tried, so I would be interested to see where it breaks someone has an example.

select d.tournamet, d.board, d.player, d.score, z.rank
from
(
  select tournamet, board, score, avg(rank) rank
  from
  (
    select *,
           @r := if(@t=tournamet and @b=board, @r+1, 1) rank,
           @t := tournamet,
           @b := board
    from (select @t:=null, @b:=null, @r:=0) x, data
    order by tournamet, board, score desc
  ) y
  group by tournamet, board, score
) z
join data d
  on d.tournamet = z.tournamet
 and d.board = z.board
 and d.score = z.score
order by d.tournamet, d.board, d.score desc

The derived table in the middle is a classic ranking query, which
1. resets the rank for each group (tournamet/board); and
2. increases the rank for each record within the group
The ORDER BY in this query causes MySQL to process the records and output the ranking in the right sequence.

The achieve your averaged ranking, we need to take this query and further create averages. Finally, the aggregated data is JOINed back to the main table again to properly attribute the ranking to the original table rows.  The final ORDER BY is added to display the data in a logical sequence.

Structure and some sample data

create table data (
  tournamet int, board int, player int, score int);
insert into data values (
    1      ,1,    2,       990),(
    1      ,1,    4,       980),(
    1      ,1,    6,       480),(
    1      ,1,    7,       450),(
    1      ,1,    9,       980),(
    1      ,1,   10,       510),(
    1      ,2,    2,       140),(
    1      ,2,    4,       110),(
    1      ,2,    6,       140),(
    1      ,2,    7,       110),(
    1      ,2,    9,        50),(
    1      ,2,   10,       120),(
    2      ,1,    3,       150),(
    2      ,1,    5,       150),(
    2      ,1,    7,       150),(
    2      ,1,    8,       130),(
    2      ,1,   10,       100),(
    2      ,1,   11,       400);

SQL Fiddle


Store a formula in a table and use the formula in a function

Prepare

Your formulas look like this:

d*b+(l*4+r)+(i/d)+s

I would replace the variables with $n notation so they can be replaced with values directly in plpgsql EXECUTE (see below):

$1*$5+($3*4+$2)+($6/$1)+$4

You can store your original formulas additionally (for the human eye) or generate this form dynamically with an expression like:

SELECT regexp_replace(regexp_replace(regexp_replace(
       regexp_replace(regexp_replace(regexp_replace(
      'd*b+(l*4+r)+(i/d)+s'
      , '\md\M', '$1', 'g')
      , '\mr\M', '$2', 'g')
      , '\ml\M', '$3', 'g')
      , '\ms\M', '$4', 'g')
      , '\mb\M', '$5', 'g')
      , '\mi\M', '$6', 'g');

Just make sure, you translation is sound. Some explanation for the regexp expressions:

  \m .. matches only at the beginning of a word
  \M .. matches only at the end of a word

4th parameter 'g' .. replace globally

Core function

CREATE OR REPLACE FUNCTION f_calc(
    d int         --  days worked that month
   ,r int         --  new nodes accuired
   ,l int         --  loyalty score
   ,s numeric     --  subagent commission
   ,b numeric     --  base rate
   ,i numeric     --  revenue gained
   ,formula text
   ,OUT result numeric
)  RETURNS numeric AS
$func$
BEGIN
   EXECUTE 'SELECT '|| formula
   INTO   result
   USING  $1, $2, $3, $4, $5, $6;
END
$func$ LANGUAGE plpgsql SECURITY DEFINER IMMUTABLE;

Call:

SELECT f_calc(1, 2, 3, 4.1, 5.2, 6.3, '$1*$5+($3*4+$2)+($6/$1)+$4');

Returns:

29.6000000000000000

Major points

The function takes 6 value parameter and formula text as 7th. I put the formula last, so we can use $1 .. $6 instead of $2 .. $7. Just for the sake of readability.
I assigned data types for the values as I saw fit. Assign proper types (to implement basic sanity checks) or just make them all numeric:
Pass in values for dynamic execution with the USING clause. This avoids casting back and forth and makes everything simpler, safer and faster.
I use an OUT parameter because that's more elegant and makes for shorter clearer syntax. A final RETURN is not needed, the value of the OUT parameter(s) are returned automatically.
Consider the lecture on security by @Chris and the chapter "Writing SECURITY DEFINER Functions Safely" in the manual. In my design, the single point of injection is the formula itself.
You could use defaults for some parameters to further simplify the call.


Two main ways, neither involving a trigger (generally it's better to avoid triggers if you can) -

1) Use an OUTPUT clause with the first INSERT statement to pick out the generated identity value:

DECLARE @NewOrder TABLE (order_id INT);

INSERT INTO Orders
    (customer_id, track_num, pckg_num)
OUTPUT
    inserted.order_id INTO @NewOrder
VALUES
    (@customer_id, @track_num, @pckg_num);

INSERT INTO Items
    (manuf_name, model_name, qty, notes, order_id)
SELECT
    @manuf_name, @model_name, @qty, @notes, order_id
FROM @NewOrder;

2) Rather than declaring Orders.order_id with the identity attribute, create and use a sequence object for it (requires SQL Server 2012 or later):

CREATE SEQUENCE OrderIDSeq AS INT
    START WITH 1;

CREATE TABLE Orders (
    order_id INT
        PRIMARY KEY
        DEFAULT (NEXT VALUE FOR OrderIDSeq),
    //... other columns as before
);

GO

CREATE OR ALTER PROCEDURE AddOrder(
    @customer_id INT, @track_num NVARCHAR(50), @pckg_num NUMERIC,
    @manuf_name NVARCHAR(50), @model_name NVARCHAR(50),
    @qty NUMERIC, @notes NVARCHAR(MAX)) AS
BEGIN
    DECLARE @order_id INT = NEXT VALUE FOR OrderIDSeq;

    INSERT INTO Orders
        (order_id, customer_id, track_num, pckg_num)
    VALUES
        (@order_id, @customer_id, @track_num, @pckg_num);

    INSERT INTO Items
        (order_id, manuf_name, model_name, qty, notes)
    VALUES
        (@order_id, @manuf_name, @model_name, @qty, @notes);
END;


Simple left outer join leaving me with inner join

I think you need a UNION or an OR with EXISTS:

SELECT c.*
FROM change AS c
WHERE c.project_id = 10
   OR c.project_id IS NULL
  AND EXISTS
        ( SELECT *
          FROM change AS x
          WHERE x.project_id = 10
            AND x.object_id = c.object_id
        ) ;


Regressed queries after statistics update

These are the steps I would take:

First of all download Microsoft Migration Assistant and try to spot incompatibilities between compatibility level 2008 and 2012.
Then download Microsoft Assessment and Planning Toolkit and scan for all sort of incompatibilities following a guide.
Spot the incompatible queries using the Query Tuning Assistant .
If everything is alright now move to 2012 compatibility level first and check if all the applications are still working. If the database is old I suppose the applications bight be old too. The applications may contain deprecated command not compatible with later version of SQL Server
If the previous step worked now move to compatibility level 2016 and activate Query Store. Use that to monitor your database for a good week or two and check if everything is alright. At this point 99% of your queries should run faster and 1% should be completely blocked. Query Store is your friend and you will use it to debug blocking and waits.
Now that everything is fixed moving to compatibility level 2019 should be a glass of water as there are not many changes between 2016 and 2019


For setting a good Master- Slave Replication please visit this link What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?

You can read it's a good answer.


Sync User Passwords Across SQL Server AlwaysOn Availability Group

With a user's default database now in an AG, the password change succeeds on the primary but fails on the secondary because the database is inaccessible.

Passwords are stored in the master database, there is no need to connect to that database on the secondary. Unless the database is a contained database, server logins are also stored in the master database. Contained databases take care of themselves as contained users can be "logins" and are "contained" within the database, thus automatically included.

  I've tried changing the user's default database to master temporarily (fails to add it back while in an AG), permanently (some apps expect the default to be their own database), and modifying the CGI to use master as the database when connecting (works on primary, fails on secondary).

If you're using must_change or password policies you'll have an event raised or a message returned about this and can do it programmatically.

Ex: https://technet.microsoft.com/en-us/library/ms131024(v=sql.110).aspx

  Any ideas?

A few come to mind:

Buffer the password (hashed) from the initial password change request. Check to see if login is mapped to any databases that currently reside in any availability group. Get the distinct replica names for each availability group. Connect to each replica and issue the password update.
Run a job on all replicas and check for logins that have been updated recently. This information you can get from sys.server_principals (modify_date) and can use sp_help_revlogin to get the command needed up update the login. This could be run every so often (10 minutes, 15, etc.)
Use event notifications for the ALTER_LOGIN DDL event and have an activated stored procedure that did #2 (the one above) but on demand at the same time rather than on a schedule.

There are other combinations of this using server side traces and/or extended events but it all boils down to about the same process.


Better diagnostics when starting ndb nodes?

You can view what is going on with ndb_mgm -e 'N report eventlog' where N is the data node's nodeid. This won't necessarily give you an estimate of when it will be finished but it can be used to verify that stuff is indeed going on and that the ndbd isn't just spinning indefinitely.

ndb_mgm -e 'all status' is also useful, as it will show you the current phase your data nodes are in. You can read more about phases in Summary of NDB Cluster Start Phases in the MySQL 5.5 Reference Manual.


Extracting entity from attribute in entity-relationship diagram

Unless you have a good reason not to, you should normalize your database schema.  When you normalize your database schema you are avoiding potential issues with data consistency that can occur when you have duplicated data.

Google the terms: insert anomaly, update anomaly, deletion anomaly.  You will see lots of examples of the kinds of problems that an unnormalized database can cause.

Regarding modeling CONTRACT separately from CAR:  I have found that one of the best places to start with a data model is to treat each tangible (or significant intangible) thing that your system cares about as its own table.

The hard reality is that business rules change, but the things that your business cares about don't change - at least not at nearly the same pace.  Therefore, you want your data to reflect the reality of the things your business cares about.

People are not contracts.  Sure, today your boss says one contract per person per car.  What happens when your boss buys (or builds) a new parking garage and that car has a contract for both?  What happens when one person sells a car that has a contract?  The contract is signed by a person, not by the car?  Does the contract go with the car or does it stay with the owner?  What happens when your boss tells you he wants to start tracking future-dated contracts so that a car can have both its current contract and its contract for next year?

You don't have to normalize.  You could try to get away with one big table for everything.  You could just write everything down in a spreadsheet or on a piece of paper.  However, if you want to build a system that is flexible for the future, then start by normalizing.

Regarding IDs for your tables:  Never use anything external as the ID for something, unless you are totally positive that it will never change.  A vehicle VIN is OK as an identifier.  Nothing causes a VIN to change and it is guaranteed to be unique.  A passport number is a terrible ID because (a) it will definitely change as time goes on and (b) you can't be sure that it will be unique.  When primary keys change it causes all kinds of headaches.  This is why a lot of people assign internal, meaningless surrogate keys to their tables.

EDIT: Something Else to Consider:

Something else you should consider is that you should NOT try to model (and maintain data for) things which aren't important to your system.

That is just making work for yourself now and down the road.  Your system will be more complicated to build, maintain, upgrade and use.

Consider the following ERD:

The relationship between CAR and OWNER is not important to your system!  At first this seems a little bit counterintuitive, but think about this:  Does your boss care who actually owns a car?  No!  What they care about is who (PERSON) signed a contract to pay to have which CAR parked in his garage.  Therefore the relationships that are actually important are between CONTRACT and CAR, and CONTRACT and OWNER.  The relationship between CAR and OWNER is not directly significant to your system, unless you have a business rule somewhere that says something about how contracts stay with cars, not owners when people sell their cars.  That is probably dicey from a legal perspective.


Affected rows after update with OpenQuery

Use a modified Linked Server Naming Convention Instead with @@rowcount

UPDATE  [linkedServerName]...[databaseName.TableName]
set status=1 WHERE status=0
select @@rowcount

Note:

Follow these steps to get Microsoft SQL Server linked server naming convention to work with MySQL:

https://stackoverflow.com/questions/31968343/select-from-mysql-linked-server-using-sql-server-without-openquery

  you can use the statement below

  select * from [linkedServerName]...[databaseName.TableName]

  but before executing the code above ,, you have to do some changes ..

  in the SQL Server Management Studio , go to "linked servers" Folder , open Providers Folder , find MSDASQL and gets it's propertythen check "Level Zero Only" press Ok ... then execute above query and Enjoy it !!!


It also depends on what the end result needs to be.

If you want "Balance due per Customer", then you have the right query.

If you want "How much money do we still have to collect overall", then you need to also grab the money where there are no CustomerID, or where the CustomerID is not in the Company table. (Theses can be over-the-counter customers in a small store, for example).

So, try this: add a new row in Orders

INSERT INTO Orders (OrderNumber, CustomerID, BalanceDue) VALUES (1005, 5,999)

Now change your "INNER JOIN" for a "LEFT JOIN". This will get you all the values of the left table, even if there is no corresponding record in the right table.

SELECT Customers.Company, Sum(Orders.BalanceDue) AS Total
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
GROUP BY Customers.Company;

You will see that you have 999$ assigned to the Customer "NULL". But at least you have all the money listed.

As with alot of things SQL and Reporting: it's all a matter of knowing exactly what you are looking for. Keep exploring, and keep asking questions.


SQL Server 2017 on RHEL Linux with IDM for AD authentication

I had similar experience. I believe this was due to a limitation in IDM. There is currently a bugzilla filed for this issue. I believe Red Hat is working on addressing this issue.


Search all table, find PrimaryKeys with Id = int/bigint and enable Identity, autoincrement

the script below will list all the primary keys, that have at least one int or bigint in their columns. You can filter further or tailor the query more but it should be a good starting point and hope it helps.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
    , OBJECT_NAME(p.object_id) AS [Table]
    , i.name AS [Index]
    , p.partition_number
    , p.rows AS [Row Count]
    , i.type_desc AS [Index Type]
    ,K.increment_value as IncrementValue
    ,K.last_value as LastValue
    ,K.seed_value as SeedValue
    ,k.is_nullable
    ,k.is_identity
    ,k.is_filestream
    ,k.is_replicated
    ,k.is_not_for_replication
FROM sys.partitions p

INNER JOIN sys.indexes i
        ON p.object_id = i.object_id
       AND p.index_id = i.index_id

INNER JOIN SYS.TABLES S
         ON S.object_id = P.object_id

LEFT OUTER JOIN sys.identity_columns K
             ON P.object_id = K.object_id

WHERE 1=1

  AND EXISTS ( SELECT 1
                    FROM SYS.COLUMNS C
              INNER JOIN sys.types AS t
                         ON c.user_type_id=t.user_type_id
                   WHERE i.object_id = c.object_id
                   AND T.user_type_id IN (127,56)  -- ONLY BIGINT AND INT
             )

  AND I.is_primary_key = 1

  -- AND i.index_id < 2  -- GET ONLY THE CLUSTERED INDEXES - IF EXISTS ANY
                      -- get heaps too

  --AND k.is_identity = 1 -- GET ONLY THE IDENTITY COLUMNS

ORDER BY [Schema], [Table], [Index]


How can a PostgreSQL standby decide whether its primary is still active or not

For streaming replication, you can check the pg_stat_wal_receiver view. There will be one line here when wal receiver is streaming.
Another question is why you need such information. For monitoring purposes, the amount of replication delay is much more important. Async replica may be significant lagged, but primary and wal receiver still alive (e.g. while reach the network bandwidth limit). Usually on hot standby:
select now() - pg_last_xact_replay_timestamp();

and pg_stat_replication on primary.


Linked Server ERROR while trying to insert some rows into remote table

For linked servers you have to executed like this (specify columns you are inserting into):

insert into [ali\servername].[dbname].[dbo].[mytable]
(col1, col2)
select col1, col2 from mytable;


What program should I use to open .db3 files?

In theory you should know where the file came from and ask the person who gave it to you. A file extension could mean anything. In the old days, .db3 extension used to be for dBase III data files. It also could be for SQLLite binary dump file. See for example Importing SQLite db3 files. If you have problems, it may be attributed to many factors, version compatibility may be one of them. You may also want to perform an integrity check as in here:Pragma Check.


How to UPDATE a SQLITE Column with an INNER JOIN on TWO FIELDS

In order to implement the join on two columns simultaneously, you can use an EXISTS predicate:

UPDATE Table_1
SET Field3 = (SELECT Field3
              FROM Table_2
              WHERE (Table_1.Field1 = Table_2.Field1)
                AND (Table_1.Field2 = Table_2.Field2))
WHERE EXISTS (SELECT *
              FROM Table_2
              WHERE (Table_1.Field1 = Table_2.Field1)
                AND (Table_1.Field2 = Table_2.Field2));

If you are using version 3.15.0 or later, you can also take advantage of row value comparisons:

UPDATE Table_1
SET Field3 =
    (SELECT Field3
     FROM Table_2
     WHERE (Table_1.Field1, Table_1.Field2) = (Table_2.Field1, Table_2.Field2))
WHERE (Field1, Field2) IN (SELECT Field1, Field2 FROM Table_2);


I've built a command-line utility that creates a .sql script file containing T-SQL that will recreate all SQL Server Agent Jobs for the specified SQL Server instance.

The utility takes several command-line parameters, as below, which necessitates running it from the command prompt, cmd.exe.

MVCTSQLJobScripter.exe /server:localhost /outfile:C:\temp\AgentJobs.sql

Be aware that this tool comes with no warranties, and is use at-your-own-risk.  I have used it on my own SQL Servers, and it works for me.

The utility uses the Microsoft.SqlServer.Management.Smo.Agent.Job.Script method to output T-SQL, so the output should be precisely what is needed to recreate jobs.

You can download the binary and prerequisites from http://blog.mvct.com/technology/tool-for-scripting-sql-server-agent-jobs/

There are also many commercially available products that provide various features to move or copy jobs from server to server, such as the Idera SQL Admin Toolset.  I neither work for, nor am I associated with Idera in any way.


Difference in duration of SP:Completed and sum(SP:StmtCompleted)

It's just in how and what they measure. The statement complete measures only that statement. It doesn't measure any wait time while that statement didn't start, etc. The procedure complete measures all sorts of extraneous management, blocks, waits, etc. There's just not going to be a perfect correlation between the two.


On a related note:  There are several ways that auto_inc ids can be "burned".  For example, INSERT IGNORE will allocate as many ids as it could need, but waste any that it fails to use.  Ditto for IODKU.  REPLACE (which is mostly obviated by IODKU) is effectively DELETE + INSERT, hence always burns id(s).

Whereas changing the datatype to a bigger INT will keep you going, it may be better to worry about the "burning" of ids.  That is, find and fix the code.

Normalization tends to burn often.  Here's an approach to avoid wasting ids: http://mysql.rjweb.org/doc.php/staging_table#normalization


Given this table:

CREATE TABLE IQ
(
    XMLDT xml NULL
);

We first need a scalar function to extract the value of interest from the XML:

CREATE FUNCTION dbo.GetRow(@XMLDT AS xml)
RETURNS nvarchar(10)
WITH SCHEMABINDING
AS
BEGIN
    RETURN @XMLDT.value('(./row/text())[1]','nvarchar(10)');
END;

Now we can add a computed column that uses the function:

ALTER TABLE dbo.IQ
ADD RowValue AS dbo.GetRow(XMLDT);

And a constraint to enforce the desired uniqueness:

ALTER TABLE dbo.IQ
ADD CONSTRAINT UQ__IQ_XMLDT_RowValue
UNIQUE (RowValue);

Demo:

-- Succeeds
INSERT INTO IQ (XMLDT)
VALUES('<row>sdsdf</row>');

-- Msg 2627, Level 14, State 1, Line 1
-- Violation of UNIQUE KEY constraint 'UQ__IQ_XMLDT_RowValue'
INSERT INTO IQ (XMLDT)
VALUES('<row>sdsdf</row>');


How to Add Check Constraint between Tables (or change DDL)?

One way is to duplicate the max_length column and enforce that they have identical values in the two tables with a composite foreign key (instead of the simple one):

create table if not exists messages(
    id serial
        primary key,
    max_length smallint not null
        check (max_length > 0),
    default_text text
        check (length(default_text) <= max_length),
    created_on timestamp with time zone not null,
    unique (id, max_length)
);

create table if not exists customized_messages(
    message_id int not null,
    max_length smallint not null,
    message text not null
        check (length(message_text) <= max_length),
    created_on timestamp with time zone not null,
    foreign key (id, max_length)
        references messages (id, max_length)
);

Note (irrelevant to the question):  the message_id column in the 2nd table does not make sense to be serial and at the same time foreign key to the 1st table.


Problem creating my first database , annoying error

The keyword VISIBLE is a MySQL-8.0 syntax. If you are using an earlier version, or MariaDB, omit this word.