How to represent foreign key in an Extended ER Model in Chen notation?

I think I found the answer, it is more or less not possible and anyway not intended in an ERM. There is a small workaround left in using double underlining.
There is simply no FK to mark in an official ERM. See http://faculty.juniata.edu/rhodes/dbms/ermodel.htm:

E-R Model is not SQL-based. It's not tied to any particular logical implementation of a DBMS. It is a conceptual and semantic model, which attempts to capture meanings rather than an actual implementation.

DO NOT THINK OR START WITH TABLES--YOU WILL BE MISGUIDED ON RELATIONSHIPS AND SOME ATTRIBUTES.

...

Foreign key: term used in relational databases (but not in the E-R model) for an attribute that is the primary key of another table and is used to establish a relationship with that table where it appears as an attribute also.

And in https://en.m.wikipedia.org/wiki/Weak_entity:

In a relational database, a weak entity is an entity that cannot be uniquely identified by its attributes alone; therefore, it must use a foreign key in conjunction with its attributes to create a primary key. The foreign key is typically a primary key of an entity it is related to.

...

An identifying relationship is one where the primary key is populated to the child weak entity as a primary key in that entity.

Thus, the weak key attribute is not equal to a foreign key, it is simply a primary key of another entity needed to reach an entity’s uniqueness (a weak entity is effectively the interim entity that you need to dissolve an M to N relation, using the PKs of “both sides” as attributes; this is out of my course script). Thus the dotted line is not the format for FKs.
Result:
Then, only double underlining remains as an unofficial convention which is up to now used at the university. And mind that showing the FKs is not needed anyway, a named relation implies it and should not explain it, an ERM is not about FKs; do not think in tables.


Why does the plan cache contain estimated and not actual execution plans?

Think about what "actual" means. It's what actually happens for the execution of that plan.

Another common name for the actual execution plan is the "post execution plan".  As a real world example to correlate this scenario, say you plan to go on a cross country trip, so you plot out the roads you're going to take and how long you think it'll take. But because of road work and detours, it doesn't happen quite like you planned. That's the actual execution plan:  The actual route that you took to get to where you're going.  The estimated plan would have been what you thought you'd be doing, and what route you thought you should take.

You can't store post execution plans because they are just that....post execution. They contain metrics of what really happened, not what SQL Server thinks should happen (estimated execution plan).

The plan cache is really just a repository of "maps" that it can reuse without having to spend the expensive time to draw up a new one. Actual execution plans are after the fact, and the plan cache is to handle before the fact.


Monitoring DB activity in a large server environment

A couple months ago I wrote this script that can execute through SQLCMD your dynamic SQL on a comma-delimited-list of servers.

Note that you have to copy/paste the result into a new window in SSMS, then change it to SQLCMD mode, remove any non-SQL lines, and then execute.

I've used this a few times and it works. It's not a shrink-wrapped, commercial deployment tool, but it will work.

My first use case that inspired this script is that I inherited a bunch of servers from another DBA, but their email address was still in some of the Agent Operators. Instead of searching through every server for that person's email address and updating it to my own, I wrote this script to automate the process. The dynamic SQL can be adapted to any purpose.

Be sure to test this very carefully.

/*
    This script will create a TSQL script that you can run separately in SQLCMD mode.

    Steps:
    1. Skip this:  Create the function SplitString with the commented-out script below.
    2. Edit @Servers with a comma delimted list of your servers.
    3. Edit @SearchVariable1 and @SearchVariable2 with the search variables.
        If you want to search for only 1 string, then make both variables that string.
        For example 'kanjana', which is part of the name of the DBA who had this set of servers before me.
        You can add more SearchVariableN variables. Edit the dynamic SQL statement.
4.  Edit the dynamic SQL to do whatever you need.
5. Copy and paste the result into a new window, specify Query | SQLCMD mode, and execute.
    Remove any non-TSQL lines, such as '(1 row affected)'
6. If logical, you can re-run the script to check the results.

*/

USE MSDB
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString
-- SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
GO

CREATE FUNCTION [dbo].[SplitString]
(
      @Input NVARCHAR(MAX),
      @Character CHAR(1)=','
)
RETURNS @Output TABLE (
      Item NVARCHAR(1000)
)
AS
BEGIN
      DECLARE @StartIndex INT, @EndIndex INT

      SET @StartIndex = 1
      IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
      BEGIN
            SET @Input = @Input + @Character
      END

      WHILE CHARINDEX(@Character, @Input) > 0
      BEGIN
            SET @EndIndex = CHARINDEX(@Character, @Input)

            INSERT INTO @Output(Item)
            SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

            SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
      END

      RETURN
END
GO

-- User configuration variables:
DECLARE @Servers VARCHAR(MAX) = ''
DECLARE @SearchVariable1 VARCHAR(MAX) = ''
DECLARE @SearchVariable2 VARCHAR(MAX) = ''

-- ************ Edit these variables:
SET @Servers = @Servers +
    'server1,server2,server3,server4'

SET @SearchVariable1 = 'person1'
SET @SearchVariable2 = 'person2'
-- ************

-- other variables:
DECLARE @ThisServer VARCHAR(100)
DECLARE @SQL VARCHAR(MAX) = ''
DECLARE @B CHAR(2) = CHAR(13) + CHAR(10)

DECLARE MyCursor CURSOR
    FOR SELECT * FROM dbo.SplitString(@Servers,',')
OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @ThisServer

WHILE @@FETCH_STATUS = 0
BEGIN
    -- PRINT @ThisServer
    SET @SQL = @SQL + ':CONNECT ' + @ThisServer + @B
    SET @SQL = @SQL + 'SELECT ''' + @ThisServer + ''' AS [Server] ' + @B
    SET @SQL = @SQL + 'SELECT * FROM msdb.dbo.sysoperators ' + @B +
        'WHERE email_address LIKE ''%' + @SearchVariable1 + '%''' + @B +
        'OR email_address LIKE ''%' + @SearchVariable2 + '%'''
    SET @SQL = @SQL + @B + 'GO' + @B + @B

    -- Use this to update the operator. This only works when there is only one email address for the operator.
    -- ToDo: adapt this to replace the email address within a list.
    --SET @SQL = @SQL +
    --  'USE [msdb] ' + @B +
    --  'GO ' + @B +
    --  'EXEC msdb.dbo.sp_update_operator @name=N''DFK_Team'', @enabled=1, @email_address=N''me@myemail.com''' + @B  +
    --  'GO '
    --SET @SQL = @SQL + @B + @B

    FETCH NEXT FROM MyCursor INTO @ThisServer
END

PRINT @SQL

CLOSE MyCursor
DEALLOCATE MyCursor

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'SplitString')
    AND xtype IN (N'TF'))
DROP FUNCTION SplitString


MongoDB error: self signed certificate in certificate chain

You have the --sslCAFile pointing at the server.pem file rather than the CA file which seems to be in /etc/mongo/ssl/ca/ca.crt. As a result the cert you have generated for your client connection is not considered valid.

You have a couple of options, you can allow cert-less connections by clients (they are still SSL encrypted, just no cert to validate the client side). This is done by specifying the allowConnectionsWithoutCertificates option on the server and is functionally equivalent to how HTTPS works on the web.

The other option of course is to fix the sslCAFile path to point at the CA cert rather than the server cert.


When is space be allocated in databases?

(This Answer addresses MySQL with ENGINE=InnoDB.)

Your specific question:  Use TINYINT UNSIGNED instead of INT to save 3 bytes per row (on disk and in cache).  Do it.

Basic guidelines (and TMI):

All numeric types (at least for NOT NULL) are fixed length, regardless of the value stored in them:

TINYINT - 1 byte
INT - 4 bytes
DOUBLE - 8 bytes
DECIMAL(m,n) about m/2 bytes.  (The exact number of bytes is complicated.)

SIGNED vs. UNSIGNED has not impact on space.  Instead, it impacts the range for *INT types.  Eg, TINYINT SIGNED is -128..127; TINYINT SIGNED is 0..255.

NULL vs NOT NULL -- the space consumed is complex due to MVCC, potential for ROLLBACK, the storage in BTree structure, etc.  It is not worth thinking of NULL as a way to save space.  Decide between NULL vs NOT NULL based on business logic, not space.  OK, you could let 0 or '' be used instead of NULL.  But, again, the space difference is so minuscule and complex to compute that it is not worth it.
 I think that NULL vs NOT NULL either matters or does not, depending on the ROW_FORMAT.

Make the decision between a 4-byte INT and a 1-byte TINYINT (or other sizes) when you originally create the table; it is costly to change it later.  Be conservative; maybe there are only 10K thingies today, but if the landscape changes, might you exceed the 64K limit of SMALLINT UNSIGNED tomorrow?

Most database data can be UNSIGNED.

String types (VARCHAR, TEXT, BLOB, etc) are typically variable length, with a 1- or 2-byte length prepended to the space.

CHAR(10) CHARACTER SET utf8mb4 NOT NULL takes 10 to 40 bytes, plus at least 1 byte overhead.  VARCHAR(10) CHARACTER SET utf8mb4 (NULL or NOT NULL) takes 0 to 40 bytes, plus at least 1 byte overhead.  (There may be ROW_FORMAT dependencies here, too.)  TEXT, VARBINARY, and BLOB act similar to VARCHAR.

Priority #1:  Business logic
Priority #2:  Space


Mysql Order By affecting a Query with column alias, group by, and having

The HAVING tipo = '1' should in no way allow output to include tipo=0, regardless of GROUP BY issues, etc.

File a bug report at http://bugs.mysql.com .


How to identify who made changes in SQL server management?

You have to log the data in some way manually.  My favorite is to include CREATE and UPDATE columns on each table as such:

ALTER TABLE TableName ADD [CREATE_TIMESTAMP] datetime NULL DEFAULT (getdate())
ALTER TABLE TableName ADD [CREATE_USERID] varchar(30) NULL DEFAULT (suser_sname())
ALTER TABLE TableName ADD [UPDATE_TIMESTAMP] datetime NULL DEFAULT (getdate())
ALTER TABLE TableName ADD [UPDATE_USERID] varchar(30) NULL DEFAULT (suser_sname())

Then add a trigger to force the update of the UPDATE columns.
After that you can tell who inserted or updated a row.  Still can't tell a delete though. You would need a log table for that.  Basically create a table TableName_Log that has the same structure as TableName then a DELETE trigger that moves the deleted rows (updated too if you want) into the log table.


find sold subscriptions from table 1 and store name in table 2, joined by store id. (oracle)

select
t2.store_name
,t1.store_code
,sum (t1.Amount) -- if Amount can be >1
,count (t1.*)  -- if counting rows is enough and we want to show stores without subscribtions
from Table1 t1
right join Table2 t2 on t1.store_code=t2.store_code
where
t1.subs = 'twin' /* type of subscription */
and t1.date > '01.05.13'
group by t2.store_name, t1.store_code

It is a good practice to use LEFT JOIN rather then RIGHT JOIN, as the later one is harder to imagine and understand.

select
t1.store_name
,t2.store_code
,sum (t2.Amount) -- if Amount can be >1
,count (t2.*)  -- if counting rows is enough and we want to show stores without subscribtions
from Table2 t1
left join Table1 t2 on t1.store_code=t2.store_code
where
t2.subs = 'twin' /* type of subscription */
and t2.date > '01.05.13'
group by t1.store_name, t2.store_code


Yes it can.  The multi sourced transactions will go into the mutli source slave's binlog with the same domain id as it receives them.  To further slaves, it just looks like a single server, the same as any other server.


Problem Getting Simple Replication to Work in SQL Server 2008/2012

This is a permissions issue and you can find more details in the verbose agent log.

Verify that your replication agent process accounts have the necessary permissions listed in the section Permissions That Are Required by Agents in Replication Agent Security Model.

Note that Microsoft recommends that you use a Windows accounts for replication agent process accounts.  Do not impersonate the SQL Server Agent account when you create the subscription, use a Windows account.

The Distribution Agent process account for a pull subscription requires:

Member of db_owner in the subscription database.

The account that is used to connect to the Distributor requires:

Be a member of the PAL.
Have read permissions on the snapshot share.


Clustered index compression vs table compression - are they the same thing?

If I set compression (either page or row) on the clustered index of a table, is that the same as setting compression on the table?

Yes.

From MSDN:

  Use the REBUILD WITH syntax to rebuild an entire table including all the partitions in a partitioned table. If the table has a clustered index, the REBUILD option rebuilds the clustered index.

  SQL Server provides options for doing both, which suggests that they are different

The reason why both syntaxes exist is because a table doesn't necessarily have a clustered index. In other words, ALTER INDEX ALL ON ... REBUILD does not affect a heap (though it will touch all nonclustereds), so the other route serves that purpose.

Also, while the documentation doesn't explicitly say so, the ALTER TABLE ... REBUILD syntax does not enable/disable compression of all nonclustered indexes on the table. It only affects the heap or the clustered index.

Finally, if you're testing this through SSMS, be aware that you may get errors when disabling compression -- script things out so you can see what's really going on.


This is a very simple INSERT .. SELECT query.

The id is not included in the column list as it has the AUTO_INCREMENT property and will get values automatically. You only need to replace 'aaa' with the menucardhash value that you want to duplicate from and 'qqq'  with the new value:

INSERT INTO menuship3
    (headhash, menucardhash, menucathash, producthash)
SELECT
    headhash, 'qqq', menucathash, producthash
FROM
    menuship3
WHERE
    menucardhash = 'aaa' ;


Import Data from 48 GB csv File to SQL Server

You can use powershell to fast import large CSV into sql server. This script High-Performance Techniques for Importing CSV to SQL Server using PowerShell - by Chrissy LeMaire (author of dbatools)
Below is the benchmark achieved :

5.35 million rows a minute for non-indexed tables and
4.35 million rows a minute for tables with clustered indexes.

The script even batches your import into 50K rows so that during import, it does not hog the memory.
Edit: ON SQL Server side -

Make sure you have proper recovery model set .. for such load, bulk recovery is best setting.
Autogrowth is set to proper MB and not Percent.
Both data and log files are provisioned big enough so there is minimal autogrowth events.
Do the import during non peak hours.
Follow best practices described in Data loading perf guide and or my answer.
Instant File Initialization is enabled and power setting is set to high perf.


SQLcl: set color in sqlprompt (ANSI/VT100 Control sequences)

From above link I created a Java Script in sqlcli bin directory(on windows 10)

C:\sqlcl-19.2.1.246.1707\sqlcl\bin>type PromptColor.js
//file name PromptColor.js save in sqlcli bin dir
//login sqlcli and run script PromptColor.js
var dbUser = util.executeReturnOneCol('select user from sys.dual');
if ( dbUser == 'HR' )
  {
   sqlcl.setStmt('set sqlprompt "@|red _USER|@@@|red _CONNECT_IDENTIFIER|@@|red >|@"');
  }
else if ( dbUser == 'SOE')
  {
    sqlcl.setStmt('set sqlprompt "@|green _USER|@@@|green _CONNECT_IDENTIFIER|@@|green >|@"');
  }

else
  {
    sqlcl.setStmt('set sqlprompt "@|blue _USER|@@@|blue _CONNECT_IDENTIFIER|@@|blue >|@"');
  }

sqlcl.run();

login with default prompt

C:\sqlcl-19.2.1.246.1707\sqlcl\bin>sql soe/soe@localhost:1521/orclpdb.localdomain -- pdb database

SQLcl: Release 19.2.1 Production on Tue Nov 12 12:17:13 2019

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>script PromptColor.js

Next is to figure how to call javascript from login.sql

Edit:- create a sql file with single line script PromptColor.js place in SQLPATH directory and add that sqlfile name in login.sql it automatically changes color based on user logged in


PostgreSQL: Multiple Inheritance tables (one child to two parents)

I advise never using INHERITS, even in simple single-inheritance cases
Not sure why you think this would not be possible.

I would certainly never do this butttt.... you have Multiple Inheritance too if you're really masochistic. From the docs on Inheritance

  A table can inherit from more than one parent table, in which case it has the union of the columns defined by the parent tables. Any columns declared in the child table's definition are added to these. If the same column name appears in multiple parent tables, or in both a parent table and the child's definition, then these columns are "merged" so that there is only one such column in the child table. To be merged, columns must have the same data types, else an error is raised. The merged column will have copies of all the check constraints coming from any one of the column definitions it came from, and will be marked not-null if any of them are.

Example

CREATE TABLE vehicle ( name text );
CREATE TABLE boat () INHERITS (vehicle);
CREATE TABLE car () INHERITS (vehicle);

--ewww,
CREATE TABLE boatcar () INHERITS (boat,car);
NOTICE:  merging multiple inherited definitions of column "name"

INSERT INTO boatcar (name) VALUES ('amphibious car');

TABLE boatcar ;
      name
----------------
 amphibious car
(1 row)

TABLE boat;
      name
----------------
 amphibious car
(1 row)

TABLE car;
      name
----------------
 amphibious car
(1 row)

TABLE vehicle ;
      name
----------------
 amphibious car
(1 row)


What permissions does the SQL Agent's user need to execute Powershell scripts?

Don't use the PowerShell job step type. It doesn't behave the same as "regular" PowerShell, it's stuck at a specific (old) version of PowerShell, and it's got a host of other limitations. TL;DR: You won't be happy with the results.

Instead, use a CmdExec job step and have it execute powershell.exe. This will let you run the latest PowerShell, load any module, and have total flexibility in how things execute. Full details, including using a Proxy, are available at https://dbatools.io/agent/


Oracle database 19C moving back and forth with time without resetlogs

I would say that this is an "X-Y Question" (Asking for one thing, "X", whilst actually wanting to achieve something else, "Y").
Mucking about, spinning the database back and forth "through time" is inherently dangerous and intrusive and, most likely, will not achieve what the client wants.
Being able to Flash-back and Flash-forward the database relies in all the changes involved being in the Database FlashBack Logs.  As soon as you make changes to the "earlier" database, you cannot Flash it "forward" again without losing the changes you made "back then".
It's like every Time-Travel movie you've ever seen, where the villain goes back in time and changes something in the Past, thereby re-writing the Present.  The only way to "rescue" the situation is to undo whenever they did (and that's basically what Flash-"forward" does).
Restate the [actual] problem.
What does the client actually want to achieve?
Databases accumulate data over time so if the client wants to change some "earlier" data, then they need to identify which data, what they want to do to it and then come and talk to you about the best way to do so (which shouldn't involve Database Recovery techniques, just regular insert/update/delete-type stuff).
And if, they've already tried that and something "in" the database actively prevented them from doing so, then there's probably a very Good Reason that it did so ...


Cast to date is sargable but is it a good idea?

The mechanism behind the sargability of casting to date is called dynamic seek.

SQL Server calls an internal function GetRangeThroughConvert to get the start and end of the range.

Somewhat surprisingly this is not the same range as your literal values.

Creating a table with a row per page and 1440 rows per day

CREATE TABLE T
  (
     DateTimeCol DATETIME PRIMARY KEY,
     Filler      CHAR(8000) DEFAULT 'X'
  );

WITH Nums(Num)
     AS (SELECT number
         FROM   spt_values
         WHERE  type = 'P'
                AND number BETWEEN 1 AND 1440),
     Dates(Date)
     AS (SELECT {d '2012-12-30'} UNION ALL
         SELECT {d '2012-12-31'} UNION ALL
         SELECT {d '2013-01-01'} UNION ALL
         SELECT {d '2013-01-02'} UNION ALL
         SELECT {d '2013-01-03'})
INSERT INTO T
            (DateTimeCol)
SELECT DISTINCT DATEADD(MINUTE, Num, Date)
FROM   Nums,
       Dates

Then running

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT *
FROM   T
WHERE  DateTimeCol >= '20130101'
       AND DateTimeCol < '20130102'

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101';

The first query has 1443 reads and the second 2883 so it is reading an entire additional day then discarding it against a residual predicate.

The plan shows the seek predicate is

Seek Keys[1]: Start: DateTimeCol > Scalar Operator([Expr1006]),
               End: DateTimeCol < Scalar Operator([Expr1007])

So instead of >= '20130101' ... < '20130102' it reads > '20121231' ... < '20130102' then discards all the 2012-12-31 rows.

Another disadvantage of relying on it is that the cardinality estimates may not be as accurate as with the traditional range query. This can be seen in an amended version of your SQL Fiddle.

All 100 rows in the table now match the predicate (with datetimes 1 minute apart all on the same day).

The second (range) query correctly estimates that 100 will match and uses a clustered index scan. The CAST( AS DATE) query incorrectly estimates that only one row will match and produces a plan with key lookups.

The statistics aren't ignored completely. If all rows in the table have the same datetime and it matches the predicate (e.g. 20130101 00:00:00 or 20130101 01:00:00) then the plan shows a clustered index scan with an estimated 31.6228 rows.

100 ^ 0.75 = 31.6228

So in that case it appears the estimate is derived from this formula:

  The following table shows the number of conjuncts guessed and the resultant selectivity as a function of input table cardinality of N:

  | Conjuncts | Cardinality | Selectivity |
|-----------|-------------|-------------|
| 1         | N^(3/4)     | N^(-1/4)    |
| 2         | N^(11/16)   | N^(-5/16)   |
| 3         | N^(43/64)   | N^(-21/64)  |
| 4         | N^(171/256) | N^(-85/256) |
| 5         | N^(170/256) | N^(-86/256) |
| 6         | N^(169/256) | N^(-87/256) |
| 7         | N^(168/256) | N^(-88/256) |
| ...       |             |             |
| 175       | N^(0/256)   | N^(-1)      |

If all rows in the table have the same datetime and it doesn't match the predicate (e.g. 20130102 01:00:00) then it falls back to the estimated row count of 1 and the plan with lookups.

For the cases where the table has more than one DISTINCT value the estimated rows seems to be the same as if the query was looking for exactly 20130101 00:00:00.

If the statistics histogram happens to have a step at 2013-01-01 00:00:00.000 then the estimate will be based on the EQ_ROWS (i.e. not taking into account other times on that date). Otherwise if there is no step it looks as though it uses the AVG_RANGE_ROWS from the surrounding steps.

As datetime has a precision of approx 3ms in many systems there will be very few actual duplicate values and this number will be 1.


Here is a working FOR XML EXPLICIT example.  They are a bit harder to code, but I tend to build them up, section by section so they're not so bad:

USE tempdb
GO

SET NOCOUNT ON
GO

IF OBJECT_ID('dbo.Borrowers') IS NOT NULL DROP TABLE dbo.Borrowers
CREATE TABLE dbo.Borrowers
(
    ID                      INT IDENTITY PRIMARY KEY,
    FirstName               VARCHAR(30),
    MiddleName              VARCHAR(30),
    LastName                VARCHAR(30),
    Title                   VARCHAR(30),
    DepartureDate           DATE,
    LocationOfficeNumber    VARCHAR(30),
    Location                VARCHAR(30),
    LocationSection         VARCHAR(30),
    PracticeGroup           VARCHAR(30),
    Email                   VARCHAR(30)
)
GO

INSERT INTO dbo.Borrowers( FirstName, MiddleName, LastName, Title, DepartureDate, LocationOfficeNumber, Location, LocationSection, PracticeGroup, Email )
VALUES
    ( 'Alvaro', 'X', 'Costa', 'Mr', GETDATE(), '000-000', 'Location 1', 'Location Section 2', 'Practice Group 3', 'alvaro@nospam.com' ),
    ( 'w', '', 'Bob', 'Mr', GETDATE(), '000-007', 'Location 2', 'Location Section 3', 'Practice Group 4', 'wBob@nospam.com' )
GO

SELECT
      1             AS Tag
    , NULL          AS Parent
    , NULL          AS [import!1]
    , NULL          AS [template!2!id]
    , NULL          AS [template!2!name]
    , NULL          AS [record!3!searchfield]
    , NULL          AS [record!3!searchvalue]
    , NULL          AS [record!3!sort!HIDE]

    , NULL          AS [field!4!id]         -- NewID
    , NULL          AS [field!4!!CDATA]

    , NULL          AS [field!4!id]         -- Name
    , NULL          AS [field!4!!CDATA]

    , NULL          AS [field!4!id]         -- ProfTitle
    , NULL          AS [field!4!!CDATA]

    , NULL          AS [field!4!id]         -- BorDepart
    , NULL          AS [field!4!!CDATA]

    , NULL          AS [field!4!id]         -- OfficePhone
    , NULL          AS [field!4!!CDATA]

    , NULL          AS [link!5!id]          -- Location
    , NULL          AS [link!5!linkfield]
    , NULL          AS [link!5!!CDATA]

    , NULL          AS [link!5!id]          -- Sec
    , NULL          AS [link!5!linkfield]
    , NULL          AS [link!5!!CDATA]

    , NULL          AS [link!5!id]          -- PracticeTL
    , NULL          AS [link!5!linkfield]
    , NULL          AS [link!5!!CDATA]

    , NULL          AS [field!4!id]         -- Email
    , NULL          AS [field!4!!CDATA]

UNION ALL

SELECT
      2
    , 1
    , NULL
    , 'Borrower'
    , 'Borrowers'
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

UNION ALL

SELECT
      3
    , 2
    , NULL
    , NULL
    , NULL
    , 'NewID'
    , 'NewID'
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , 'NewID'
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , 'Name'
    , LastName
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , 'ProfTitle'
    , Title
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'BorDepart'
    , CONVERT( CHAR(10), DepartureDate, 111 )
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'OfficePhone'
    , LocationOfficeNumber
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      5
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'Location'
    , 'Code'
    , Location
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      5
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'Sec'
    , 'Term'
    , LocationSection
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      5
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'PracticeTL'
    , 'Term'
    , PracticeGroup
    , NULL
    , NULL

FROM dbo.Borrowers

UNION ALL

SELECT
      4
    , 3
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , ID
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , NULL
    , 'Email'
    , Email

FROM dbo.Borrowers
ORDER BY [record!3!sort!HIDE]
FOR XML EXPLICIT, ROOT('sydney')

HTH


An execution plan was not included, but the typical issue with queries like this (sorting aside) is the optimizer choosing a nested loops anti semi join without a good supporting index. It can also be a rogue top (1), or a poorly-performing transformation to a semi-join with nested start-up filters and an anti-semi join.
Regardless, there are two usual workarounds:

Rewrite the OR manually as a UNION (or, if guaranteed disjoint, as a UNION ALL).
Rewrite the NOT EXISTS as a left join filtering the preserved side for NULL.

The following incorporates both:
DECLARE @thisTable table
(
    id integer PRIMARY KEY,
    refid integer NULL,
    refbool bit NOT NULL,
    col varchar(10) NOT NULL
);

INSERT @thisTable
    (id, refid, refbool, col)
VALUES
    (1, NULL, 0, 'val'),
    (2, NULL, 0, 'val'),
    (3, NULL, 0, 'val'),
    (4,  2  , 1, 'val'),
    (5, NULL, 0, 'val'),
    (6,  1  , 1, 'val');

SELECT
    U.id
FROM
(
    -- T.refbool = 1
    SELECT T.id
    FROM @thisTable AS T
    WHERE
        T.col = 'val'
        AND T.refbool = 1

    -- Or (disjoint)
    UNION ALL

    -- T.refbool = 0 and not exists
    SELECT T.id
    FROM @thisTable AS T
    LEFT JOIN @thisTable AS T2
        ON T2.refid = T.id
    WHERE
        T.col = 'val'
        AND T.refbool = 0
        AND T2.id IS NULL
) AS U
ORDER BY
    CHECKSUM(NEWID());

db<>fiddle online demo
For more alternatives on random ordering see the existing Q & A:

What is the best way to get a random ordering?.

Don't just try the top answer.


Make primary key and unique id as foriegn key

The first table, user_profile has two unique constraints, the PRIMARY KEY on (id) and the UNIQUE on the non-nullable (user_id).

That makes it possible to reference either of them in FOREIGN KEY constraints from other tables. You can use what you have, to reference the PK:

CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER_PROFILE (id)

or, to reference the UNIQUE column:

CONSTRAINT FOREIGN KEY (user_id) REFERENCES USER_PROFILE (user_id)

There is no need to reference both of them.


You can't select the column you're pivoting on.

Change your query to SELECT PvtTbl.*, like this:

SELECT PvtTbl.*

FROM    (
        SELECT [au_lname], [state], [au_id]
        FROM [authors]
        WHERE STATE IN('CA','OR','IN')
        ) AS Data

PIVOT   (
    COUNT([au_id])
    FOR au_lname IN(
        [CA], [OR], [IN])
    ) as PvtTbl;

The other columns from the source table, authors, are automatically added to the columns returned by the PIVOT clause, so output contains the state column, as well as [CA], [OR], [IN].

I'm attempting to infer your desired output.  It looks like you want a count of each au_lname for each state.  The most likely "correct" pivot would be:

USE tempdb;
IF OBJECT_ID(N'dbo.authors', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.authors;
END
GO
CREATE TABLE dbo.authors
(
    au_lname sysname
    , [state] sysname
    , [au_id] int
);

INSERT INTO dbo.authors (au_lname, [state], au_id)
VALUES ('Vernon', 'CA', 1)
    , ('Darling', 'OR', 2)
    , ('Obbish', 'IN', 3)
    , ('White', 'CA', 4)
    , ('Vernon', 'OR', 5)
    , ('Vernon', 'OR', 6);

SELECT  *

FROM    (
        SELECT [au_lname], [state], [au_id]
        FROM [authors]
        WHERE STATE IN('CA','OR','IN')
        ) AS Data

PIVOT   (
    COUNT([au_id])
    FOR [state] IN(
        [CA], [OR], [IN])
    ) as PvtTbl;

The output looks like:

╔══════════╦════╦════╦════╗
║ au_lname ║ CA ║ OR ║ IN ║
╠══════════╬════╬════╬════╣
║ Darling  ║  0 ║  1 ║  0 ║
║ Obbish   ║  0 ║  0 ║  1 ║
║ Vernon   ║  1 ║  2 ║  0 ║
║ White    ║  1 ║  0 ║  0 ║
╚══════════╩════╩════╩════╝


Pull data from one DB Server to another DB server

You have a few options.

Copy data in the correct order. Find the parent child relationships and insert data to the parent tables first.
Drop or disable the constraints, insert the data, then recreate or re-enable the constraints.
Use a tool like Redgate SQL Data Compare, which will identify the heirachy and copy data in the correct order for you.

If you want to know more about how to find foreign key constraints there is a great post here


Mongodb tree structure questions and answers

If the whole survey is not too big and if it will not grow, I would consider putting all the questions and answers inside one document.

So one document of that collection would look like

{
  "survey": {
    "Question A": [
      {
        "Ans A": [
          {
            "Question C": [...]
          }
        ]
      },
      {
        "Ans B": [
          {
            "Question D": [...],
            "Question E": [...]
          }
        ]
      }
    ]
  }
}

If it is big, and could grow in future, I would consider putting answer and question ids as key-value pair like following.

{
  "title": "Question A",
  "answers": [
    {
      "Ans A": [
        "Question C's Id"
      ]
    },
    {
      "Ans B": [
        "Question D's Id",
        "Question E's Id"
      ]
    }
  ]
}

I wouldn't normalize to have both question and answer collection, because it would take too much time to join each other.


SQL agent job step's connection managers

Given that SSIS tasks are XML files, I would have thought that it would be possible to write a T-SQL XML query to find the connection objects.

You would need to search the XML for instances of

<DTS:ConnectionManager>

Here's a sample from a project I have:

<DTS:ConnectionManager>
  <DTS:Property DTS:Name="DelayValidation">0</DTS:Property>
  <DTS:Property DTS:Name="ObjectName">SQLOverview</DTS:Property>
  <DTS:Property DTS:Name="DTSID">{1e0a262f-476a-49d8-873b-acf7e3d7d46e}</DTS:Property>
  <DTS:Property DTS:Name="Description">
</DTS:Property>
  <DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property>
  <DTS:ObjectData>
    <DTS:ConnectionManager>
      <DTS:Property DTS:Name="Retain">0</DTS:Property>
      <DTS:Property DTS:Name="ConnectionString">Data Source=MyPC\SQL2008R2Dev;Initial Catalog=SQLOverview;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SQLOverview;</DTS:Property>
    </DTS:ConnectionManager>
  </DTS:ObjectData>
</DTS:ConnectionManager>

A couple of things to note:

The top node is <DTS:Executable>.  These <DTS:ConnectionManager> items are the next level down.
The above snip shows a static / fixed connection.  If you use an expression / dynamic connection, the XML code is a bit different:

  0
  MultiServer
  {02150dee-336e-4750-9159-3c4d3f0c62c3}

  OLEDB
  @[User::DynamicServerName]

      0
      Data Source=MyPC\SQL2008R2;Initial Catalog=master;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SQLOverview;

Please also note that all the code here has been formatted using an on-line XML formatter to aid readability.  In reality, each of these ConnectionManager XML snippets is on a single line of the .DTSX file.


You want 3 different searches and the results combined. I'd use UNION:

SELECT
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel
FROM `user` AS u
  LEFT JOIN user_media AS umt
         JOIN media AS mt ON  mt.id = umt.media_id
                          AND mt.type = 'T'
    ON u.id = umt.user_id
  LEFT JOIN user_media AS umm
         JOIN media AS mm ON  mm.id = umm.media_id
                          AND mm.type = 'M'
    ON u.id = umm.user_id
WHERE
  u.name LIKE CONCAT('%', ?, '%')

UNION ALL

SELECT
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel
FROM media AS mt
  LEFT JOIN user_media AS umt ON mt.id = umt.media_id
  LEFT JOIN `user` AS u ON u.id = umt.user_id
  LEFT JOIN user_media AS umm
        JOIN media as mm ON  mm.id = umm.media_id
                         AND mm.type = 'M'
    ON umt.user_id  = umm.user_id
WHERE
  mt.type = 'T'
  AND mt.value LIKE CONCAT('%', ?, '%')

UNION ALL

SELECT
  u.id     AS user_id,
  u.name   AS name,
  mm.value AS mail,
  mt.value AS tel
FROM media AS mm
  LEFT JOIN user_media AS umm ON mm.id = umm.media_id
  LEFT JOIN `user` AS u ON u.id = umm.user_id
  LEFT JOIN user_media AS umt
         JOIN media AS mt ON  mt.id = umt.media_id
                          AND mt.type = 'T'
    ON umm.user_id = umt.user_id
WHERE
  mm.type = 'M'
  AND mm.value LIKE CONCAT('%', ?, '%') ;

Tested at SQLfiddle. (replace the ? with the value you want to search for.)


How to set up bidirectional replication with Postgresql

You don't need bidirectional replication and its additional complexity.

Simply use standard replication, and setup B as a standby for A.

If A fails, let B take over as master, and then reconfigure A as standby for B, possibly using pg_rewind to do this.

Repeat as necessary should either node fail.


How to use GROUP BY in an efficient way without losing attributes?

You can use a window function:

SELECT player_id , ... FROM (
    SELECT player_id, year_world, ...
         , COUNT(*) over (PARTITION BY player_id) AS player_id_count
    FROM <table-a>
) AS T
WHERE player_id_count > 3

over (partition by player_id) defines the window where count(*) should be applied. In this case, we want the window to be all rows for the player in question. This means that you will get the count for player_id on all rows with player_id.


If the tables in question are not using a clustered index, they are what is referred to as "heaps".  Heaps do not automatically release the space consumed by deleted rows.

To determine if your tables actually have clustered indexes, you can right-click the table name from SQL Server Management Studio, then click "Script table ... -> CREATE to -> New Query Editor Window".  If you see the word "CLUSTERED" in the definition produced, then the table has a clustered index.

If your tables are not using clustered indexes, I would recommend adding clustered indexes on appropriate key columns (probably the id column), since rows deleted from clustered indexes are automatically freed by the ghost cleanup process referred to in the answer posted by @RLF.  Clearly, you're going to do this in the development environment, NOT production, correct?

For further info, I recommend reading Paul Randall's excellent post on the ghost cleanup process.  One salient sentence from that post is:

  do ghost records occur in heaps? The answer is no, not during normal processing.


Status fields: ALTER TYPE vs. INSERT INTO?

What are the cons of using ALTER TYPE instead of creating a new table and using JOIN? I really don't want to add a JOIN to my queries just because of a status field.

This is your typical pro/con enum vs normalization question.

ENUM type

Great if the range is fixed or only requires modification by the DBA.
Get a really nice query syntax which optimized well (bottom assumes status is an ENUM)

SELECT * FROM foo WHERE status = 'INVALID';

Sorts well and permits easy range within the type. All enums are sorted and have ordinality
Store in an 4 byte-size
Can eliminate a JOIN
PostgreSQL has ENUM types, not macros (like MySQL). This means you don't take a hit in maintenance. If you have five tables using your ENUM type you can still manage just that one type and not five tables.

Normalized / JOIN method

Always preferred if the user needs to be able to modify the domain/data
Always preferred if the domain/data needs to shrink (remove values)
Will provide easier modification with an ORM
More traditional
Can be smaller than ENUM (for instance smallint is two bytes, enum is always four bytes).


Only you can answer that question.

The option will make the index rebuild operation an online operation. See Perform Index Operations Online. There are certain limitations and restrictions, I recommend you read the link above and Guidelines for Online Index Operations as well. To call out a few issues, online operations have licensing edition restrictions, they consume more disk space, they take longer, and they pin the log from shrinking. However, they do allow for the application to continue to function online while the rebuild is ongoing. So, as you see, only you can answer what you need. Take the time, study, and make an educated decision.

Also, is very unlikely that you need to rebuild indexes for maintenance. Have a look at SQL Server Index and Statistics Maintenance.


Get all the queries executed in the last week in SQL Server

I don't expect you to be on 2016 yet as it's been released only very recently (at the time of writing) but if you have a 2016 server and you have Query Store enabled for your database, you can review your queries there.

Have a look at the documentation for Query Store which lists the following example use cases:

  Determine the number of times a query was executed in a given time window, assisting a DBA in troubleshooting performance resource
  problems.
  Identify top n queries (by execution time, memory consumption, etc.) in the past x hours.

If you want to have a log of your queries for future review you could look into Extended Events or server side traces, depending on your server version.

If you are running SQL 2005+ you can log queries using this article explaining server side trace.

If you are running a version more recent than 2008 you can use Extended Events which would be recommended over server side traces.

Look at the query posted here by Paul Randal showing you how to log expensive queries using Extended events. You could modify the WHERE from that query to log all queries.

That being said I don't think it's a good idea to log ALL of your queries. If you have a fairly active production system that would really slow your system down. Have at least a few filters, and log as few data as possible.

If you don't have any logging set up there is no way you can retroactively and reliably get that information.

The closest you can get is querying the plan cache with something like:

SELECT
sql_text.text,
st.last_execution_time,
DB_NAME(qp.dbid) as databasename
FROM sys.dm_exec_query_stats st
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) AS sql_text
INNER JOIN sys.dm_exec_cached_plans cp
ON cp.plan_handle = st.plan_handle
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) as qp
WHERE st.last_execution_time >= DATEADD(week, -1, getdate())
ORDER BY last_execution_time DESC;

That would list you all queries executed in the last week that still have the plan in cache along with the last execution time and database name (unless it's prepared statement).

But you need to realize this is no guaranteed way to get you ALL of your queries as there are multiple reasons a query could have been executed but never or no longer have a plan in cache.

For example there are ways to drop the entire plan cache or evict a single plan

If optimize for ad hoc workloads is active, a query which has only been executed once will not be retrieved by this query and plans that were once stored in cache can be evicted from the cache by anything that cause a plan recompile such as updated statistics, DDL statements, index rebuilds etc. Read through this whitepaper listing all circumstances that cause plan recompilation.


Am I using the correct data types on SQL Server side to call an Oracle 10g function?

Your datatypes should be okay, however you need to change your call slightly and add OUTPUT after your @price.  I've tested this against Oracle11g on INTEGER -> INT, VARCHAR2 -> VARCHAR, DATE -> Datetime using your getTest function

exec ( 'BEGIN ? := db.pkg.getPrice(?,?,?,?); END;', @price OUTPUT, @id1, @id2, @curr, @ed ) AT ORASRV


How to add a fixed string to a mongoDB result like we do we in SQL Server?

You can use MongoDB's Aggregation Pipeline
to add calculated or constant fields to a query.

The $addFields
stage appends new fields to existing result documents if you don't want to specify
a list of all fields. However, since your example projects a subset of fields you
can also add new fields
using expressions in a
$project stage.

Your find() query would become a $match stage in aggregation. Here's an example showing a few different options (with comments) in the $project stage:

db.product.aggregate([

    // The initial $match stage can take advantage of available indexes
    { $match: {
        "company.cad": {
            $in: [" ", "14940"]
        },
        "tradeItemMeasurements.netContent.measurementUnitCode": {
            $ne: null
        },
    }},

    // Project the desired result fields
    { $project: {
        // Fields that already exist
        "_id": 1,
        "brandNameInformation.brandName": 1,
        "childTradeItems.gtin": 1,
        "company.cad": 1,
        "placeOfProductActivity.countryOfOrigin.countryCode": 1,
        "referencedFileInformations.uniformResourceIdentifier": 1,
        "tradeItemClassification.gpcCategoryCode": 1,

        // constant values
        "ACTIVE": true,
        "language.code": "PT-BR",

        // calculated values (in this case, size of an array)
        "itemCount": { $size: "$tradeItems" },

        // literal values (that might otherwise be interpreted as expressions)
        "batch.batchNumber": { $literal: 1 },
    }}

])


PostgreSQL: Administration as Non-Superuser

That can easily be done if you make your administrative user a member of the users you create:
CREATE ROLE newuser LOGIN;
GRANT newuser TO admin;

Then you inherit all rights from that user and can ALTER and DROP the objects it owns. You can even become that user:
SET ROLE newuser;

The only problem is that newuser can revoke that privilege.


Performance when using truncated VARCHAR as index in MySQL

You are only partially right. MySQL query optimization engine will still try to use that index, as long as:

Query has exact match (a = 'b', a <> 'b', IN clause etc. - not "LIKE" clause),
Storage engine is InnoDB, XtraDB (Percona), or possibly some other fork of InnoDB.
Server is recent enough (at least 5.0 or 5.1 - I'm not sure now, but in 4.x varchar indexes worked a bit differently).


What is the best way to replace a SQL Server database with an updated one?

Restore new database from backup (or attach) as yourDatabaseNew
Rename old database to yourDatabaseOld
Rename new database to yourDatabase
Drop the old database

To fix the logins problem make sure you created all logins and users on your temporary VPS.
Export logins from the existing server with sp_help_revlogin


MYISAM tables are very fragile and get damaged/crash every now and then. First advice is to move to Innodb engine it is simply performed by using alter table command.

Now, to fix crashed MYISAM tables the proper way is to bring the MYSQL down and once it is down and there is no traffic you can fix them using this command line statement:

myisamchk --silent --force --fast  /path/table-name.MYI

Trying to repair/optimise MYISAM tables on a live server usually does not work. From MYSQL CLI the command should be

mysql> repair table tablename;

instead of optimize table if the table was crashed.


PostgreSQL window functions very slow on big table

If we can assume a device table holding all devices of interest. Example:

CREATE TABLE device (device_id int, device text);
INSERT INTO device (device_id, device) VALUES
  (100, 'a')
, (101, 'b')
, (102, 'c');

The query can be very simple:

SELECT d.device_id, g.point, g.dt_edit
FROM   device d
,      LATERAL (
   SELECT point, dt_edit
   FROM   gps_data
   WHERE  device_id = d.device_id
   ORDER  BY dt_edit DESC NULLS LAST -- more items?
   LIMIT  2
   ) g;

Assuming a much smaller cardinality in table device than in table gps_data, i.e. many rows per device in the latter.

If dt_edit is not UNIQUE, add more items to ORDER BY to disambiguate the sort order.

All you need for this to be fast is a multicolumn index;

CREATE INDEX gps_data_foo_idx
ON gps_data (device_id, dt_edit DESC NULLS LAST); -- more items?

The NULLS LAST modifier is useful if the column can be NULL, but does not hurt in any case. Either way, the query must match the index.

PostgreSQL sort by datetime asc, null first?

If you don't have a device table, I would suggest to create one (and keep it current).
But there are ways to make this fast even without device table ...

Details:

Optimize GROUP BY query to retrieve latest record per user
How do I efficiently get "the most recent corresponding row"?
SELECT DISTINCT on multiple columns


Best practice for a Table with private columns

A slight variation on Rolandos answer.  Rather than a TEXT column, you could use an INT column as a mask value.  Using a bitwise OR to test the mask would give you way to determine if the field is shown.

select id,
    case when 1 | mask = mask then col1 end col1,
    case when 2 | mask = mask then col2 end col2,
    case when 4 | mask = mask then col3 end col3,
    case when 8 | mask = mask then col4 end col4,
    ...
from table

So if you want to show columns 1 and 3 then the mask would be 5.
Editing the mask is just a matter of adding and subtracting the appropriate values.
For example if you want to make column 3 public for a user

update table
set mask = mask + 4
where id = 1
    and 4 | mask <> mask -- Make sure it isn't already set

Edit By @RolandoMySQLDBA

Perhaps to apply IF function to your idea

SELECT
    IF(POWER(2,0) | mask = mask,fld1,'Private') fld1,
    IF(POWER(2,1) | mask = mask,fld2,'Private') fld2,
    IF(POWER(2,2) | mask = mask,fld3,'Private') fld3,
    IF(POWER(2,3) | mask = mask,fld4,'Private') fld4,
    ...
    IF(POWER(2,n) | mask = mask,fldn,'Private') fldn
FROM user_table;

Maybe, this might work too

SET @xp = -1;
SELECT
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld1,'Private') fld1,
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld2,'Private') fld2,
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld3,'Private') fld3,
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fld4,'Private') fld4,
    ...
    IF(POWER(2,@xp:=(@xp+1)) | mask = mask,fldn,'Private') fldn
FROM user_table;

so you don't have to hardcode the powers of 2,

This would only work for n <= 32. Don't know what would happen if there would be 33+ columns.


how is synchronization maintained between different write and read database

In SQL Server 2012 you can have read-only secondaries, which allow you to use Availability Groups to mirror a database or set of databases to another server, and perform read-only queries against them even as they are being mirrored.

The downside: this requires Enterprise Edition on both nodes, which can only be licensed per core (not CAL), and the underlying OS needs to be WSFC, so the OS needs to be Enterprise Edition as well (this is less of a big deal since the additional cost of Enterprise at the OS level is laughable compared to the jump in SQL Server license costs).

In previous versions you could do this with log shipping (subject to the annoying limitations @datagod already mentioned) or with mirroring + snapshots (which also needs Enterprise Edition). I haven't seen any of our customers using replication for this specific requirement but I suppose that is possible as well.


Database Stuck 'In Recovery' with command XTP_DB_RECOVERY

It's in a restoring state so you need to create and log a backup. (see below)

    BACKUP DATABASE [earnings]
TO DISK = N'c:\sql\earnings.bak'
    WITH NOFORMAT, NOINIT, NAME = N'earnings-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
    GO

    BACKUP LOG [earnings]
TO DISK = N'C:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak'
    WITH NOFORMAT, NOINIT, NAME = N'earnings_LogBackup_2018-06-02_12-42-07',
SKIP, NOREWIND, NOUNLOAD, STATS = 10

Once we have the backups, we will restore the backups.

In order to restore the full and log backup we need to use the NORECOVERY option for the full restore. So, if we just restore the full backup as follows:

RESTORE DATABASE [earnings]
FROM DISK = N'c:\sql\earnings.bak' WITH NORECOVERY, NOUNLOAD, STATS = 10

The database will now be in a restoring state.  If we forget to restore additional backups, the database will be stuck in this mode.

object explorer
To finalize the restore and access the database we need to restore the log backup as follows:

RESTORE LOG [earnings]
FROM DISK = N'c:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak'
SQL Server database in RESTORING state after doing backup log with NORECOVERY

Another reason your database can be in restoring state is when you backup the tail of the log using the NORECOVERY option as shown below.

    BACKUP DATABASE [earnings]
TO DISK = N'c:\sql\earnings.bak'
    WITH NOFORMAT, NOINIT,  NAME = N'earnings-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
    GO

    BACKUP LOG [earnings]
TO DISK = N'C:\sql\earnings_LogBackup_2018-06-02_12-42-07.bak'
    WITH NOFORMAT, NOINIT, NAME = N'earnings_LogBackup_2018-06-02_12-42-07',
SKIP, NOREWIND, NOUNLOAD, NORECOVERY, STATS = 10

This will cause the database to change to a restoring state.

To fix this you can restore the database backups as shown above.

Make a SQL Server database in RESTORING state accessible without restoring backups
If the database is stuck in the restoring state and you don't have additional backups to restore, you can recover the database using the following command:

RESTORE DATABASE [earnings] WITH RECOVERY

Once you issue this command, the database will be useable, but you won't be able to restore any additional backups for this database without starting all over again with the full backup.

Read up on "SQL Server Database Mirroring" also.


postgresql custom TEXT SEARCH CONFIGURATION with tsvector_update_trigger

When creating the new configuration with

CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french );

it does not appear in pg_catalog, but in the current schema (public by default).

This is made apparent by the \dF command in psql:

test=# \dF
               List of text search configurations
   Schema   |    Name    |              Description
------------+------------+---------------------------------------
 pg_catalog | danish     | configuration for danish language
 pg_catalog | dutch      | configuration for dutch language
 pg_catalog | english    | configuration for english language
 pg_catalog | finnish    | configuration for finnish language
 pg_catalog | french     | configuration for french language
 pg_catalog | german     | configuration for german language
 pg_catalog | hungarian  | configuration for hungarian language
 pg_catalog | italian    | configuration for italian language
 pg_catalog | norwegian  | configuration for norwegian language
 pg_catalog | portuguese | configuration for portuguese language
 pg_catalog | romanian   | configuration for romanian language
 pg_catalog | russian    | configuration for russian language
 pg_catalog | simple     | simple configuration
 pg_catalog | spanish    | configuration for spanish language
 pg_catalog | swedish    | configuration for swedish language
 pg_catalog | turkish    | configuration for turkish language
 public     | fr         |
(17 rows)

In that case, the trigger should be created with:

CREATE TRIGGER TS_tsv BEFORE INSERT OR UPDATE ON books
  FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(tsv, 'public.fr', title, authors, descriptionText);


How to create a new column in SELECT CASE when string contains Arabic words?

Since you are using Unicode / UTF-16 (i.e. NVARCHAR), you do not need either the CAST or even an Arabic_* collation. The only requirement for string literals is to prefix them with a capital-"N":
SELECT 'قدوم' COLLATE Arabic_CI_AS, '-o-' AS [-o-], 'رجوع' COLLATE Arabic_CI_AS;
-- ???? -o- ????

SELECT N'قدوم' COLLATE French_CI_AS, '-o-' AS [-o-], N'رجوع' COLLATE French_CI_AS;
-- قدوم -o- رجوع

(the -o- field is used as a separator to prevent the Bi-Direction formatting, inherent with Arabic and Hebrew code points, from displaying the second column on the left in the T-SQL comment below the query when rendered in HTML)
When using NVARCHAR (including NCHAR and the should-never-be-used NTEXT), the encoding is always UTF-16, hence the character set never changes (it's always Unicode, which is a single character set). In this case, the collation mainly affects sorting and comparisons (which are not being done here).
If you were using VARCHAR, then an Arabic collation would be needed in order to set the code page in order to support the Arabic characters (Windows Code Page 1256).
The reason why your initial 'قدوم' returned ???? is that the string literal was not prefixed with N, making it a VARCHAR string, in which case it was converted to the code page associated with the default collation of the current database you were using, which clearly was not an Arabic collation. This conversion happens as the query is initially parsed, which means that your string was already ???? before the cast operation converted it to NVARCHAR. If you had been using a database that had an Arabic default collation, then your string literal would have worked even without the N prefix, and the CAST to NVARCHAR would have been just as unnecessary.

For more information on working with strings / encodings / Unicode / collations, please visit my site: Collations Info

P.S. I would recommend that you not be doing old-style JOINs such as FROM TableA, TableB WHERE TableA.JoinColumn = TableB.JoinColumn. While that syntax has not been deprecated, the related outer join syntax — *= and =* — was actually removed in SQL Server 2005. So, for consistency (and personally, I would also throw in readability), you should instead use the INNER JOIN clause. Also, it's always best to prefix object names with their Schema name. The end result is:
FROM dbo.TableA
INNER JOIN dbo.TableB
        ON TableB.JoinColumn = TableA.JoinColumn


Performance: MySQL id = vs id IN (multiple values)

I found a similar post here.

I've done some benchmark on all 3 queries on a table with 500,000 records with index on id. I fetched 10,000 rows. I did the same tests on MyISAM and Innodb, all same results. I made sure that for each query I used different set of numbers, so the query is not cached by MySQL. I did the tests multiple times, and I got similar numbers every time.

Benchmark results (average):

Query 1, fetch by = OR =: 3.700 seconds
Query 2, fetch by IN: 0.037 seconds
Query 3, fetch by >= && <=: 0.005 seconds
Query 4, fetch by between: same as >= && <=


Why doesn't INSERT AFTER Trigger add all results?

On most platforms your insert would throw an error if your subselect returns more than one row, rather than just insert the 'first' returned row (and 'first' is undefined without an order by clause).

What you are probably after is an insert like this:

INSERT Into Likes
SELECT New.ID, ID
FROM Highschooler
WHERE Grade = New.Grade and ID<>New.ID;


MySQL "SLEEP" command throws syntax error

You have to use

select sleep(5);

because it is similar to other mysql functions.


First, to answer the question you asked about the behavior of this command:

 psql -h localhost -p 5432 postgres testdb

  Also, why does the prompt say password authentication failed for user "testdb". From what I have understood, testdb is the name of the database and not the name of the user. The user name is postgres.

Because you have given two trailing options to this psql command: postgres and testdb. Since neither the -d nor -U option were given, psql assumes the database name should be the first argument and the username should be the second argument, per the documented Synopsis of the psql command.

Second, to answer your other question:

  When I connect to testdb from within the postgres prompt, I am not required to enter the password. ... However, when I try to connect to this database from the terminal using [... psql, and getting a password prompt ... ]

psql has some logic by which it determines whether it is safe to keep and reuse the password you entered initially, see the logic about keep_password in command.c. Presumably when you are able to use the \c meta-command to reconnect from within an existing psql session, psql is preserving your initial password and reusing it -- either that, or your pg_hba.conf rules allow a connection without a password with the given user/database, e.g. a trust or ident rule.


Downgrade database from SQL Server 2012 to 2008

One option is third party tools: Red Gate's SQL Compare and SQL Data compare are very good for this sort of thing, and what we use internally. They will let you handle the data table by table, so that might help keep size down. Not sure if they any upper limits on size.


ypercube already gave the right answer, but here's another alternative:

Instead of GROUPing by all columns (leading to a bloated GROUP BY this, that, that, and_that_too), the following can be used:

select
    sales.stor_id,
    MIN(stores.stor_name) AS stor_name
from
    sales
join
    stores
on  sales.stor_id =stores.stor_id
group by
    sales.stor_id
having
    (count(sales.stor_id)>=5)

Though I confess we just move the bloating around... But this at least clarifies the mechanism of the query, by simplifying the GROUP BY.


I would consider taking the union queries and make a view using them.  You can then treat the union as if it was a table.


Introducing surrogate key in may-to-many table?

I would suggest that brand_product_id is redundant.
Yes, every table row must be uniquely identifiable.
Nobody ever said that had to be accomplished with a single field.
Logically, this is a "Weak Entity".  No record can exist in this table without corresponding entries in the other two tables.
It is highly unlikely that you would ever want to query this table without reference to one [or both] of the other two tables, i.e. you would always want to query it for a brand or a product.   Therefore, the [unique] combination of brand_id and product_id is sufficient for a Primary Key.
I would suggest that your biggest decision (if it's a decision at all) is whether to have the Primary Key on (brand_id, product_id) with an index on (product_id, brand_id) for the "reverse" look-ups, or the other way around.  YMMV.


How to ensure security with SQL Server Restore

I think your best approach would be to supply each user with his or her personal database engine process.
With Linux, consider using Docker or some other container framework which further enhances your security by sandboxing the SQL server process.

One of the problems that may arise from your approach, clifton_h already mentioned some other things to watch for in his comment:
Any user that is member of the "public" Server Role will be able to see at least the names of all databases on the instance when connecting with SQL Server Management Studio - but if you set the securable "View any database" to "Deny", the user won't even be able to browse databases he is allowed to connect to using the Object Explorer (USE [databasename], however, remains possible). This will sure cause some headscratching among your users and desparate calls on your help line :)

At the very minimum do not allow using contained databases (see https://docs.microsoft.com/en-us/sql/relational-databases/databases/security-best-practices-with-contained-databases?view=sql-server-2017), the already mentioned "View any database" permission and the items that are part of the Surface Area Configuration Policy (https://docs.microsoft.com/en-us/sql/relational-databases/security/surface-area-configuration?view=sql-server-2017):


AWS Postgres RDS "storage-full" and not responding. How to bring back online?

Turns out in this "storage-full" status you cannot connect to the instance. Only thing you can do is to increase storage.
https://aws.amazon.com/premiumsupport/knowledge-center/rds-autoscaling-low-free-storage/


Return ID from partitioned table in postgres?

Assuming a parent's table like this:

CREATE TABLE parent AS (
  id not null default nextval('parent_id_seq'::regclass)
  ... other columns ...
);

Whether you're using a rule or a trigger to divert the INSERTs into the child tables, immediately after the INSERT you may use:

SELECT currval('parent_id_seq'::regclass);

to get the last id inserted by your session, independently of concurrent INSERTs, each session having its own copy of the last sequence value it has obtained.


Sum of points and rank according to points

Try this query

select a.*,b.points from table3 a
join(
select Sum(PointsUp-PointsDown) as points,t1.id
from table1 t1
  join table2 t2 on t2.Id = t1.id
where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01'
Group By t1.Id)b on a.id=b.id
order by b.points desc


What is the difference between lock_timeout and deadlock_timeout?

The difference between a live lock (handled by lock_timeout) and a deadlock is that the latter is guaranteed never to end, because two (or more) sessions are waiting for each other.

A live lock can always end – it will end as soon as the session that locks the resource ends the database transaction. With a deadlock, that is not possible, because all involved transactions are blocked and cannot continue.

By default, the database resolves deadlocks by killing one of the involved transactions, but it does not interrupt live locks.


Database MySQL - Transpoing Table from one form to other

Please post question correctly. I am not getting your schema & your second query is showing @sql variable value to same because of last record value in first query stored to @sql due to last write. Post schema of table with some dummy data.

I am trying to understand & conclude that you need column sub-query. I am setting example which could help you.

select (select name from demo where id=outer.id limit 1) as 'subquery-1' , (select age from demo where id=outer.id limit 1) as 'subquery-2' from outer;

Now for your query would be like-

SELECT country_location, time_of_day,
                          (select MAX(IF(FUNCTION_NAME = 'Dance program',budget, NULL) from country_report dr JOIN function_master am ON dr.function_name = am.func_NAME and  WHERE am.func_CATEGORY = 'FUN' and dr.id=outer.id ) as 'Dance Program',
                          (select MAX(IF(FUNCTION_NAME = 'singing',budget, NULL) from country_report dr JOIN function_master am ON dr.function_name = am.func_NAME and  WHERE am.func_CATEGORY = 'FUN' and dr.id=outer.id) as 'Singing'
       FROM country_report outer
       WHERE daily_param = 1 AND country_location in ('hong kong','tokyo')
       GROUP BY country_location, time_of_day;

May be inner subquery return more than 1 record & show error. So please set conditions as your need.


Can I run a mongos without any config servers?

You cannot run a mongos without a config server, but you shouldn't have to in order to "scale up" your connections in any case - all you would be doing is adding an unnecessary layer between the driver you are using and the replica set.  Unless you are using sharding, having that layer is not necessary, and can introduce performance issues in some cases (such as unbatching delays).

Your driver (which you don't mention) will have connection pool settings (the Java version can be found here) and perhaps multiplier options to determine how many connections you will open to your replica set, and how many threads will block and wait for a connection from the pool.

Some general advice:

If you do go down the route of having a single shard (which is what your replica set would effectively become with config servers and mongos processes), then do not run with a single config server - always run with 3 config servers in production, period.
Be careful with connections - each connection on the MongoDB side will require 1MB of memory (as will each connection on a mongos).  If you ramp up connection levels you will be using memory for connection heap rather than data - this can be significant (10GB of memory for 10,000 concurrent connections)
If you do ramp up connections, make sure your ulimit settings can handle it

Finally, this use of mongos as a general proxy has been proposed before.  In theory it would simplify some of the driver code needed if there was always a mongos in the connection path, but it is not as simple as just allowing it.  The relevant feature request can be found (and voted for, watched etc.) here:

https://jira.mongodb.org/browse/SERVER-1594


Be very careful not to conflate table partitioning with (and?) database sharding. This specific article doesn't do itself any favors by having such a generic and overloaded title, and you're not the first person to start referring to sharding as partitioning.

In fact, there is already a pull request to change the title to Horizontal, vertical, and functional data partitioning.

The minimize requirements for referential integrity bit implies that you shouldn't be sharding data that you commonly join. If your question is "when would handling joins in the application be beneficial?" it would be a narrow use case, probably where latency between the application and a shard is better than the latency between shards (think distributed app and geo-replicated data), and when all of the data is being cached closer to the application anyway (e.g. Redis), as most good applications will plan for at the outset these days. Make sure you have done proper research to determine that (a) you need sharding at all, and (b) that you should be separating data you plan to join a lot. There are almost certainly going to be separation tactics that minimize the need to join across shards or in the application.

Sharding works best when each node that you're writing to contains data that is largely independent and can live without any of the other nodes. When they depend on each other and you're joining between them, you've largely defeated the purpose of separating them in the first place. A good use case is when each customer/tenant has its own shard (or its own database on one of the shards). Most activity for any given tenant is focused on that shard. When central data is needed, it is often replicated to each of the shards, rather than joined across shards. This is the one of the models APS and Azure SQL Data Warehouse use.

  would like to get to the bottom of why MS encourages application-side join instead of a db join

They are talking about aggregate queries there, and I think they are implying that you are concatenating the results, not joining them. For example if customer A is on shard 1 and customer B is on shard 2, and you want to show total sales by customer, the app can (even asynchronously) run an aggregate query on shard 1 for customer A, and an aggregate query on shard 2 for customer B, do that in parallel, and then just union those results together. No joining across shards. I think you are reading way too much into it by saying they are encouraging it or suggesting it is necessary.

And if you want an answer to why Microsoft says something in a very specific way, you're going to have to ask Microsoft. We can help you interpret what they probably mean, and even suggest scenarios where it makes sense, but we can't "get to the bottom" of anything.


How to change job output log location, when using Ola Hallengren's scripts

I’m going to reword your questions a little:

Q: Should I log Agent jobs directly to a remote file server? No, because if you lose network connectivity or the file share goes down, your Agent job could fail. Your Agent job might not require network connectivity, like an update stats job, so that failure would suck.

Q: If I want to centralize Agent job history, how should I do it? Consider logging to a table (and Ola’s scripts support this too.) That way you can centralize the data in whatever method you like, like replication or log shipping. I like logging to a DBA utility database, and then restoring that from all my servers to one central server daily. Then I use union all views to combine the data from all my servers. It’s not up to the second, though.


Which database is best for deeply embedded database or through C DLL?

Have a look at PostgreSQL, it has unlimited database size, what appears to be many options for encryption, support for full text search and interfaces for several languages (C as well via libpg). It is also open source. See the about-page for PostgreSQL for more info.


Recommended Way to Store Logical Expressions

This could be implemented as a set of tables. There will be one column for each logical operator against each source column. As this can lead to a great many combinations I'd suggest deploying only the columns needed at the time. The design allows for new columns to be included later.

The decision table:

Referrer
PlanIdNotEqual
GameIdIn              -- this is just a flag to say whether this condition applies to this referrer
MinAgeLimitGreaterThan
AmountGreaterThan
...
further columns to meet additional conditions

Since there are multiple GameId values for one Referr's conditions it gets a separate table:

DecisionGameIdIn
    Referrer
    GameId

The query then becomes (in pseudo-SQL)

select
  <columns>
from Game
inner join Decision
on  (Decision.PlanIdNotEqual is NULL
     or Decision.PlanIdNotEqual <> Game.PlanId)
and (Decision.GameIdIn is NULL
     or Game.GameId in (select GameId from DecisionGameIdIn where Referrer = <current value>))
and (Decision.MinAgeLimitGreaterThan is NULL
     or Game.MinAgeLimit > Decision.MinAgeLimitGreaterThan)
and (Decision.AmountGreaterThan is NULL
     or Game.Amount > Decision.AmountGreaterThan)
... continue for further conditions
where Decision.Referrer = <the current value>

This can rapidly become out-of-control. Keeping Decision's columns aligned with the query logic is one problem. The combinatorial explosion is another. The plethora of Boolean conditions can overwhelm any indexing strategy resulting in table scans.


From MSDN:

  Replication does not automatically replicate data from a TDE-enabled
  database in an encrypted form. You must separately enable TDE if you
  want to protect the distribution and subscriber databases. Snapshot
  replication, as well as the initial distribution of data for
  transactional and merge replication, can store data in unencrypted
  intermediate files; for example, the bcp files.  During transactional
  or merge replication, encryption can be enabled to protect the
  communication channel.

Also, a suggested read here.

Yes, you can use TDE with replication as explained here.

TDE is file level encryption. This meaning, TDE encrypts and decrypts at the disk level where the data and log files are retained. Replication is managed in a combination of the system databases and the user database level and in reality, has no awareness of the file storage. This is the same when we would discuss any type of replication, such as Peer-to-peer, Transactional or Merge replication.


Select only those rows based on specific condition of user id

SELECT DISTINCT t1.*
FROM `table` t1, `table` t2
WHERE t1.id = t2.id
  AND t1.timestamp > t2.timestamp
  AND t2.activity = 'Tv';


ORA-03114 not connected to ORACLE
Cause: A call to Oracle was attempted when no connection was established. Usually this happens because a user-written program has not logged on. It may also happen if communication trouble causes a disconnection.
Common causes for the ORA-03114 error include:

Listener process is not running
DB is shutting down or session has been forcibly disconnected
Missing entry to the tnsnames.ora
Network issues such as timeouts
Problems with connection pooling (shared servers, MTS)

If - as you seem to suggest - your tnsnames.ora was modified recently, an incorrect configuration could be one possible reason for the error, especially if other clients are not experiencing any problems. Compare the tnsnames.ora and sqlnet.ora files between the client that fails and the clients that work. Also check the network path for routers or firewalls that could be enforcing idle timeouts, if that is applicable in your environment.
See here for further explanation:

https://www.tekstream.com/resource-center/ora-03114-not-connected-to-oracle/
http://www.dba-oracle.com/t_ora_03114.htm


MySQL/MariaDB: Scaling 10 millions database in cluster

10 million databases is too many to have in one cluster and will likely hit a bunch of internal limits and be unable to balance workloads. You should look into containerizing the databases so you have one small DB associated with each client.


'23-OCT-1994' is a string, not a DATE, so TO_CHAR on a character is pointless.

First you have to convert your string into a DATE value. You should specify the date format plus the language which is used:

TO_DATE('23-OCT-1994', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American')

Then you can compare with your weekday. Note, 'DAY' returns the weekday but padded with spaces up to the longest name. Try '"'||TO_CHAR(DATE '2018-10-23', 'DAY')||'"' to see the effect. You should also specify the language.

You can use one of the following:

TRIM(TO_CHAR(..., 'DAY', 'NLS_DATE_LANGUAGE = American')) = 'FRIDAY'
TO_CHAR(..., 'fmDAY', 'NLS_DATE_LANGUAGE = American') = 'FRIDAY'
TO_CHAR(..., 'DY', 'NLS_DATE_LANGUAGE = American') = 'FRI'

for example

begin
   if TO_CHAR(TO_DATE('23-OCT-1994', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American'),'fmDAY', 'NLS_DATE_LANGUAGE = American') = 'FRIDAY' THEN
      DBMS_OUTPUT.PUT_LINE('FRIDAY');
   else
      DBMS_OUTPUT.PUT_LINE('Not Friday');
   end if;
end;


How to search entire database to find tables that contain 2 columns?

To include views:
SELECT TABLE_NAME, 'user_id' 'oganization_id'
    FROM
    INFORMATION_SCHEMA.COLUMNS
    where COLUMN_NAME IN ('user_id', 'oganization_id') AND TABLE_CATALOG = '<your database name>'
    GROUP BY TABLE_NAME
    HAVING COUNT(COLUMN_NAME) =2

To only include tables:
SELECT c.TABLE_NAME, 'user_id', 'oganization_id'
    FROM
    INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t on t.TABLE_NAME = c.TABLE_NAME
    where c.COLUMN_NAME IN ('user_id', 'organization_id') AND t.TABLE_TYPE = 'BASE TABLE' AND c.TABLE_CATALOG = '<your database name>'
    GROUP BY c.TABLE_NAME
    HAVING COUNT(COLUMN_NAME) =2


Per Team - Count Number of Losses in Last N Results

This should do the trick:

select team_id, count(*) from (
    select *, case when @team = team_id then @i:=@i+1 else @i:=0 END as n, @team:=team_id
    from team_results, (select @i:=0, @team_id:=0) init
    order by team_id, result_date desc
) a
where a.n < 10 and won = 0
group by team_id;

You use the inner query to enumerate the results by date. Here you probably want to apply some heuristic where condition to filter the results to optimize your query. For example if they in average play every second day then you can limit for the last one month or so.

Then you can count the lost games with the given conditions per team.

@update to show teams with 0 loss

select team_id, sum(1 - won) -- instead of count rows sum losses (1-won) = loss
from (
    select *, case when @team = team_id then @i:=@i+1 else @i:=0 END as n, @team:=team_id
    from team_results, (select @i:=0, @team_id:=0) init
    order by team_id, result_date desc
) a
where a.n < 10  -- remove the won=0 filter
group by team_id;


Multiple SPIDs in sp_who2, Deadlocks and Parallelism

Answering this question:

  Am I correct in saying this is a result of parallelism?

Yes. Activity Monitor and sp_who2 display one row for each thread (execution context) involved. It is unnecessarily verbose and confusing, but Microsoft have not responded to requests to improve Activity Monitor. It is even less likely that the (technically undocumented sp_who2) will be updated.

Adam Machanic's sp_WhoIsActive is a much better alternative.

There is not enough information in the question to address your other points, but it does seem you might be running with an excessive degree of parallelism, possibly the default of zero (= 24 for your configuration).

Setting max degree of parallelism to 8 might be about right. See MAXDOP setting algorithm for SQL Server for detailed advice.

And, as Dan Guzman said:

  Parallel queries in a pure OLTP workload are often due to inattention to query and index tuning detail. In addition to reducing maxdop and cost threshold, view the execution plans for optimization opportunities.


How to compare timestamps with different time zones?

This just works:

SELECT *, ts AT TIME ZONE '-01'  -- see below about '-1' vs '+1'
FROM  (
   VALUES
   (1, timestamptz '2018-04-05 06:00:00 +01')
 , (2,             '2018-04-05 06:00:00 +00')
 , (3,             '2018-04-05 06:00:00 -01')
 , (4,             '2018-04-05 04:00:00 -01')
   ) t(id, ts)
WHERE  ts BETWEEN '2018-04-05 06:00:00+01'  -- coerced to timestamptz
              AND '2018-04-05 07:00:00+01'; -- derived from context!

But note several pitfalls and potential misconceptions here!

The SQL construct BETWEEN ... AND (not a function, strictly speaking; more like an operator in practice) is not concerned with timezones. That would be a misconception of how things work.
Be aware of the two distinct data types timestamp (timestamp without time zone) and timestamptz (timestamp with time zone). See:

Ignoring time zones altogether in Rails and PostgreSQL

In particular, the time zone offset in timestamptz literals only serves as input / output modifier to the value and is not stored at all. Only the according UTC time is stored internally. See:

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

Be aware of the odd syntax difference between timestamp literals and time zone specifiers (flipped sign) due to disagreement between POSIX and SQL standards:

Why does PostgreSQL interpret numeric UTC offset as POSIX and not ISO-8601?

The output you show (with different time zone offsets) cannot be achieved  by simply returning timestamptz values:

2018-04-05 06:00:00 +01
2018-04-05 04:00:00 -01

timestamptz values are always displayed according to the timezone setting of the session. To get the strings you display (for varying time zones), you would have to preserve the time zone offset of the input and use it to format the output. (Or store complete input literals as text)
Finally, note that '2018-04-05 04:00:00 -01' also passes the test, since it's exactly the same timestamptz value as '2018-04-05 06:00:00+01', just formatted differently (same point in time, displayed for different time zones).
So three rows pass the test in your example, not just two.

If your head is spinning right now, consider this demo:

WITH tbl AS (
   SELECT *
        , split_part(tstz_string, ' ', 3) AS tz_string
        , tstz_string::timestamptz AS tstz
   FROM  (
      VALUES
      (1, '2018-04-05 06:00:00 +01')
    , (2, '2018-04-05 06:00:00 +00')
    , (3, '2018-04-05 06:00:00 -01')
    , (4, '2018-04-05 04:00:00 -01')
      ) t(id, tstz_string)
   )
SELECT *
     , to_char(tz_string::numeric * -1, 'SG00')                                       AS tz_posix
     ,  tstz AT TIME ZONE (tz_string::numeric * -1)::text AS ts_at_org_tz
     , (tstz AT TIME ZONE (tz_string::numeric * -1)::text)::text || ' ' ||  tz_string AS tstz_org
     , (tstz AT TIME ZONE (tz_string::numeric * -1)::text)::text || ' ' ||  tz_string
      = tstz_string                                                                   AS strings_equal
FROM   tbl;

id | tstz_string             | tz_string | tstz                   | tz_posix | ts_at_org_tz        | tstz_org                | strings_equal
-: | :---------------------- | :-------- | :--------------------- | :------- | :------------------ | :---------------------- | :------------
 1 | 2018-04-05 06:00:00 +01 | +01       | 2018-04-05 06:00:00+01 | -01      | 2018-04-05 06:00:00 | 2018-04-05 06:00:00 +01 | t
 2 | 2018-04-05 06:00:00 +00 | +00       | 2018-04-05 07:00:00+01 | +00      | 2018-04-05 06:00:00 | 2018-04-05 06:00:00 +00 | t
 3 | 2018-04-05 06:00:00 -01 | -01       | 2018-04-05 08:00:00+01 | +01      | 2018-04-05 06:00:00 | 2018-04-05 06:00:00 -01 | t
 4 | 2018-04-05 04:00:00 -01 | -01       | 2018-04-05 06:00:00+01 | +01      | 2018-04-05 04:00:00 | 2018-04-05 04:00:00 -01 | t

db<>fiddle here


The LEFT OUTER JOIN ON version is the standard way to write this logic, so I would highly recommend using this version rather than fiddling around with OR for performance reasons.

However, as ypercube has helpfully pointed out, the queries are not quite equivalent if the data and sharding methodology means that a Table1.Id value can end up in a different ShardKey than the ShardKey of the corresponding Table1.table2 value. If that happens, then the OR t2.ShardKey IS NULL portion of the 2nd query will be triggered and retain a row with matching Ids even if the ShardKey does not match.

In general, I suspect that they will typically result in similar query plans.  Given two equivalent queries (which yours may or may not be, depending on the sharding methodology), by formulating the query in the standard way you are more likely to be working with the query optimizer rather than forcing it to recognize that the two queries can be expressed in similar ways and apply the appropriate simplifications.

If you want to be even more explicit about applying @shardkey to each table in the join, you can do that directly in the LEFT OUTER JOIN. However, SQL Server will be able to easily recognize that your original query is equivalent to this one.

SELECT t1.id, t2.ShardKey
FROM Table1 t1
LEFT OUTER JOIN Table2 t2 on t1.table2 = t2.id and t2.ShardKey = @shardkey
WHERE t1.id = @id and t1.ShardKey = @shardkey

An example of different query plans

On my SQL 2014 instance, the queries have (slightly) different query plans and the query plan for the LEFT OUTER JOIN ON version (top) is superior to the ORM-generated LEFT OUTER JOIN WHERE OR IS NULL version (bottom).

In particular, the seek into Table2 should be a seek on both id and ShardKey, but in the LEFT OUTER JOIN WHERE OR IS NULL version it is a seek only on id and a subsequent Filter operator tests all matching rows in order to apply the appropriate ShardKey restriction.

This difference may or may not be meaningful in your specific case (especially if Id is unique without adding in the ShardKey). However, if your real version actually uses table partitioning by ShardKey (and this just isn't represented in the simple sample query), you will likely be losing the benefit of partition elimination for the seek into Table2.


Log files

Error log -- very little activity; don't worry about it.
redo/undo -- Mandatory; you cannot avoid their use (for InnoDB).
binlog -- for replication and/or recovery.  Clearly state in your benchmark results the setting of log_bin.
general log -- This should normally be turned off.  It is a disk hog.  Leave it off for benchmarking.
slowlog -- It is reasonable to leave it off for benchmarking.  (And so-state.)  For production systems, I recommend leaving it on.  It does not hurt much, even with long_query_time = 1.  (That is "seconds".)
Double-buffer -- Keep on unless the disk subsystem handles it.  (Cf "torn page")
innodb_flush_log_at_commit -- =1 for banking-like security; =2 for more speed, at the expense of very rare loss of data.

The disk subsystem makes a big difference in certain write-intensive apps:

Plain HDD -- slow
SSD -- noticeably faster, but non-zero write time.
RAID with Battery Backed Write Cache -- virtually eliminates I/O time for writes.  The BBWC avoids data loss even during power failure.

Rant
Benchmarks are a popular way to lie about how great your thing is.  What is your goal for the benchmark?
It is somewhat reasonable to compare two hardware setups or versions via comparing benchmarks.  However, TPC (etc) benchmarks only test a few focused things.  Keep in mind that your app may not be really like any of the TPCs.
Also, any 'real' system has multiple things going on.  Benchmarks (like drug trials) mostly punt when it comes to combinations.
Some benchmarks focus on "how many connections can be run before the system keels over?"  If you are anywhere near that limit, you are already in trouble, and latency may make the system a failure.


removing sql logins from one server, after migrating to another server, but all the databases were not migra

Instead of migrating or transferring all the users from server A to server B, you should have identified the orphan users for the migrated databases.

Say from server 1 , as you said you moved database A and B:

1) Find orphan users on database A- Once you have found them , mapped the orphan users with help of script you used earlier to find the logins on server A

2) Find orphan users on database B- Once you have found them , mapped the orphan users with help of script you used earlier to find the logins on server A.

For more on how to find orphan and map them read this article on Orphan users


How to aggregate transactional records by month year but based on subscription period not calendar months?

First it calculates month according to next formula:

Month = If day < 20 then, if month = 1 then month = 12 else previous month

Obviously you can change day 20 by no matter which other day.

  NOTE: As far as I understand, this date 2010-06-18 20:10:00.000000 should
  returns Month=5 due Day=18

with calcMonth as
(
    SELECT extract(YEAR FROM date)  AS year,
           case when extract(day from date) < 20
                then (case when extract(MONTH FROM date) = 1
                           then 12
                           else extract(MONTH FROM date) - 1
                      end)
                else extract(MONTH FROM date)
                end AS month,
            amount,
            cust_id
    FROM  events
)
SELECT year,
       month,
       sum(amount) AS amount
FROM   calcMonth
WHERE  cust_id = 211
GROUP BY 1, 2
ORDER BY year, month;

year | month | amount
:--- | :---- | -----:
2010 | 5     |     20
2010 | 6     |     35
2010 | 7     |     25
2011 | 1     |   5000
2011 | 2     |     44

dbfiddle here

These are values returned by CTE:

    SELECT date as Date,
           extract(YEAR FROM date)  AS Year,
           extract(day from date) AS Day,
           extract(MONTH FROM date) AS Month,
           case when extract(day from date) < 20
                then (case when extract(MONTH FROM date) = 1
                           then 12
                           else extract(MONTH FROM date) - 1
                      end)
                else extract(MONTH FROM date)
                end AS CalMonth,
            Amount
    FROM  events

date                | year | day | month | calmonth | amount
:------------------ | :--- | :-- | :---- | :------- | -----:
2010-06-18 20:10:00 | 2010 | 18  | 6     | 5        |     20
2010-07-18 05:53:00 | 2010 | 18  | 7     | 6        |     35
2010-07-27 05:45:00 | 2010 | 27  | 7     | 7        |     25
2011-02-14 20:07:00 | 2011 | 14  | 2     | 1        |   5000
2011-02-21 20:29:25 | 2011 | 21  | 2     | 2        |     22
2011-03-14 20:48:26 | 2011 | 14  | 3     | 2        |     22

dbfiddle here


dbcc checkdb differences between physical_only, full and data_purity

Do I understand it correctly that if the database was created before SQL2005 it will have dbi_dbccFlags set to 0 and full checkdb will skip column-value checks, but once such database will have at least one successfull data_purity checks the flag will be set to 2 and column-value checks will be done during every full checkdb? And if the database is created in SQL2005+ it will have dbi_dbccFlags set to 2 by default and then full checkdb with no option and with data_purity will be the same checks?

Yes, that's right. You only need to enable the DATA_PURITY option running CHECKDB on a pre-2005 created database once, if the result is clean then subsequent CHECKDBs without the option will automatically include the check. And it's part of the standard CHECKDB process in 2005 and above-created databases.

Also estimates seem to suggest that for SQL2005+ there's no distinction between these two checks, at least if it comes to space needed in TempDB

That's because it's the same check process - the option wouldn't have any effect in a SQL2005+ created database because CHECKDB does that check as standard.


MySql service on Windows won't start after changing datadir

If You open Windows Services and check the service properties, You will find something like this:

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57

not enough to change datadir in my.ini file, need also point windows to run service with proper my.ini file


First, you have a scan count of 17. I.e., it re-reads the same set of data over and over again, and the total for those are 4616999 logical reads. Assuming that the exact same set of data is re-read 17 times, you would divide your 30 GB with 17 to get the amount of data read from disk. So, at first pass it reads som 1.8 GB data, and then it re-reads this same set of data 16 times more. 1.8 GB data is no problem to fit in memory.

Second, even if you did have a scan count of 1, and the data wouldn't fit in memory, then where would the problem be? As it read some data, that data can later removed from memory during the read as it get further along the read operation.

Finally, don't get fooled by "physical reads" from statistics IO. You can also have physical I/O done as read-ahead, blob reads etc. Make sure you read the whole line and all numbers after the logical reads should be summed to get the total physical reads. Also, the perfmon buffer cache hit ratio counter don't include read-aheads, meaning it is very misleading.


SQL Server 2012 Import Wizard appending new data only

The Import/Export wizard builds out an SSIS package behind the scenes to perform EL (Extract and Load). It really doesn't have a Transform capability since it's a streamlined tool.

What you're trying to do is conditionally load data based on existence. Which is fine, that's a common pattern.

You're attempting to solve it by a non-working query.

select col1, col2, col3 from A where col1 not in (select col1 from

DatabaseB.dbo.B)

This assumes that DatabaseB is on the same instance as A. If you established a Linked Server between A and B, then your query would work - whether it performs at acceptable levels is a different matter.

select col1, col2, col3 from A where col1 not in (select col1 from

Server2.DatabaseB.dbo.B)

A more SSIS native way of doing this would be to add a Lookup between the Source and Destination that matches based on col1 but that cannot be done using the Import Export wizard. You'd have to edit the package using Visual Studio/SQL Server Data Tools (SSDT).

If you absolutely cannot edit the package for reasons, then you have to build 2 packages. The first will export all the Col1 IDs from Destination back to Source. You can then modify the source query to reference the local cache of ids instead of trying to cross servers.

Good all purpose read -> Stairway to Integration Services I think step 3 is an incremental load which is what you're looking for


When you perform a transaction - like deleting 600K records all at once - SQL Server has to log what it's doing in the transaction log file as it works.

Say the process takes 5 minutes, and 4 minutes into it, the server reboots for some reason. SQL Server starts up, opens the log file, and then needs to undo all that work (because the transaction never committed.

To avoid having a log file that large, you can:

Do your work in smaller transactions - like working in batches
Put your database in simple recovery model (so that transaction log space is available for reuse as soon as your transaction is committed), or
If you're in full recovery model, back up your transaction log frequently, maybe even every minute - because the space is only marked as available for reuse after it's been backed up