Unexpected scans during delete operation using WHERE IN

"I'm more wondering why the query optimizer would ever use the plan it currently does."

To put it another way, the question is why the following plan looks cheapest to the optimizer, compared with the alternatives (of which there are many).

The inner side of the join is essentially running a query of the following form for each correlated value of BrowserID:

DECLARE @BrowserID smallint;

SELECT
    tfsph.BrowserID
FROM dbo.tblFEStatsPaperHits AS tfsph
WHERE
    tfsph.BrowserID = @BrowserID
OPTION (MAXDOP 1);

Note that the estimated number of rows is 185,220 (not 289,013) since the equality comparison implicitly excludes NULL (unless ANSI_NULLS is OFF).  The estimated cost of the above plan is 206.8 units.

Now let's add a TOP (1) clause:

DECLARE @BrowserID smallint;

SELECT TOP (1)
    tfsph.BrowserID
FROM dbo.tblFEStatsPaperHits AS tfsph
WHERE
    tfsph.BrowserID = @BrowserID
OPTION (MAXDOP 1);

The estimated cost is now 0.00452 units.  The addition of the Top physical operator sets a row goal of 1 row at the Top operator.  The question then becomes how to derive a 'row goal' for the Clustered Index Scan; that is, how many rows should the scan expect to process before one row matches the BrowserID predicate?

The statistical information available shows 166 distinct BrowserID values (1/[All Density] = 1/0.006024096 = 166).  Costing assumes that the distinct values are distributed uniformly over the physical rows, so the row goal on the Clustered Index Scan is set to 166.302 (accounting for the change in table cardinality since the sampled statistics were gathered).

The estimated cost of scanning the expected 166 rows is not very large (even executed 339 times, once for each change of BrowserID) - the Clustered Index Scan shows an estimated cost of 1.3219 units, showing the scaling effect of the row goal.  The unscaled operator costs for I/O and CPU are shown as 153.931, and 52.8698 respectively:

In practice, it is very unlikely that the first 166 rows scanned from the index (in whatever order they happen to be returned) will contain one each of the possible BrowserID values.  Nevertheless, the DELETE plan is costed at 1.40921 units total, and is selected by the optimizer for that reason.  Bart Duncan shows another example of this type in a recent post titled Row Goals Gone Rogue.

It is also interesting to note that the Top operator in the execution plan is not associated with the Anti Semi Join (in particular the 'short-circuiting' Martin mentions).  We can start to see where the Top comes from by first disabling an exploration rule called GbAggToConstScanOrTop:

DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers
WHERE BrowserID NOT IN
(
    SELECT DISTINCT BrowserID
    FROM tblFEStatsPaperHits WITH (NOLOCK)
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');

That plan has an estimated cost of 364.912, and shows that the Top replaced a Group By Aggregate (grouping by the correlated column BrowserID).  The aggregate is not due to the redundant DISTINCT in the query text: it is an optimization that can be introduced by two exploration rules, LASJNtoLASJNonDist and LASJOnLclDist.  Disabling those two as well produces this plan:

DBCC RULEOFF ('LASJNtoLASJNonDist');
DBCC RULEOFF ('LASJOnLclDist');
DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers
WHERE BrowserID NOT IN
(
    SELECT DISTINCT BrowserID
    FROM tblFEStatsPaperHits WITH (NOLOCK)
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, LOOP JOIN, RECOMPILE);
GO
DBCC RULEON ('LASJNtoLASJNonDist');
DBCC RULEON ('LASJOnLclDist');
DBCC RULEON ('GbAggToConstScanOrTop');

That plan has an estimated cost of 40729.3 units.

Without the transformation from Group By to Top, the optimizer 'naturally' chooses a hash join plan with BrowserID aggregation before the anti semi join:

DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers
WHERE BrowserID NOT IN
(
    SELECT DISTINCT BrowserID
    FROM tblFEStatsPaperHits WITH (NOLOCK)
    WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1, RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');

And without the MAXDOP 1 restriction, a parallel plan:

Another way to 'fix' the original query would be to create the missing index on BrowserID that the execution plan reports.  Nested loops work best with when the inner side is indexed.  Estimating cardinality for semi joins is challenging at the best of times.  Not having proper indexing (the large table doesn't even have a unique key!) will not help at all.

I wrote more about this in Row Goals, Part 4: The Anti Join Anti Pattern.


Please explain SYSCS_UTIL.SYSCS_CREATE_USER() function

Read the secderby.pdf, page 41, "Configuring NATIVE authentication":

Use the SYSCS_UTIL.SYSCS_CREATE_USER system procedure to add credentials
for the Database Owner. Remember that the Database Owner is the user who
created the database.

I assume you created your DB without username/password or you tried to create a different user then the one when you created the DB.

Working example:
CONNECT 'jdbc:derby://localhost:1527/myDB;create=true;user=admin;password=admin123';
call SYSCS_UTIL.SYSCS_CREATE_USER('admin', 'admin123' );
You see in the above example the DBO (database owner) is 'admin' and for the very first SYSCS_CREATE_USER call, the user must be 'admin' too.

Not working example:
CONNECT 'jdbc:derby://localhost:1527/myBadDB;create=true;';
call SYSCS_UTIL.SYSCS_CREATE_USER('admin', 'admin123' );


Transaction Log file size is increasing in my Azure (IAAS) SQL Server 2014 database

Do you ever do a full database backup?  That will zap the log, which is what you're looking for.  See T-SQL BACKUP command for syntax.  Not sure what backups look like on Azure, but that should help.


Will a primary key be added as a clustered index?

Yes, SQL Server will create the primary key as clustered by default, but you don't have to accept the defaults.

ALTER TABLE dbo.foo
  ADD CONSTRAINT pk PRIMARY KEY (bar); -- clustered

ALTER TABLE dbo.foo
  ADD CONSTRAINT pk PRIMARY KEY CLUSTERED (bar); -- clustered

ALTER TABLE dbo.foo
  ADD CONSTRAINT pk PRIMARY KEY NONCLUSTERED (bar); -- non-clustered

And yes, you will see some I/O activity here, so if it is a busy system, best to save this for quieter hours or a maintenance period.


Simply get MAX value of Towns and Cities grouped by Country.

SELECT Country,
       MAX(town1) Town1, MAX(City1) City1,
       MAX(town2) Town2, MAX(City2) City2,
       MAX(town3) Town3, MAX(City3) City3,
       MAX(town4) Town4, MAX(City4) City4,
       MAX(town5) Town5, MAX(City5) City5
FROM (
      SELECT Country,
             City,
             'City' + CAST(ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Country) AS varchar(10)) ColumnSequence,
             Town,
             'Town'+ CAST(ROW_NUMBER() OVER (PARTITION BY Country ORDER BY Country) AS varchar(10)) ColumnSequence2
      FROM Countries
     ) Temp
PIVOT (MAX(City) FOR ColumnSequence IN (City1, City2, City3, City4, City5)) Piv
PIVOT (MAX(Town) FOR ColumnSequence2 IN (town1, town2, town3, town4, town5)) Piv
GROUP BY Country
GO

Country | Town1     | City1     | Town2      | City2      | Town3  | City3      | Town4  | City4      | Town5 | City5
:------ | :-------- | :-------- | :--------- | :--------- | :----- | :--------- | :----- | :--------- | :---- | :----
India   | hyder     | Hyderabad | Bang       | Bangalore  | Del    | New Delhi  | townin | India Town | null  | null
UK      | chelsea   | London    | sunderland | Birmingham | United | Manchester | null   | null       | null  | null
USA     | New Jersy | New York  | houst      | Houston    | Dally  | Dallas     | null   | null       | null  | null

Warning: Null value is eliminated by an aggregate or other SET operation.

dbfiddle here


Output a 2 column table to a three column query

In SQL Server 2012 you can use the new IIF() function:

SELECT EmployeeName,
  Shift1 = COUNT(IIF(Shift=1,1,NULL)),
  Shift2 = COUNT(IIF(Shift=2,1,NULL))
FROM dbo.tablename
GROUP BY EmployeeName;

This is just shorthand for a CASE expression, so on older versions you could do it this way:

SELECT EmployeeName,
  Shift1 = COUNT(CASE WHEN Shift = 1 THEN 1 END),
  Shift2 = COUNT(CASE WHEN Shift = 2 THEN 1 END)
FROM dbo.tablename
GROUP BY EmployeeName;

You can also use PIVOT:

SELECT EmployeeName, Shift1 = [1], Shift2 = [2]
FROM dbo.TableName AS t
PIVOT (COUNT(Shift) FOR Shift IN ([1],[2])) AS p;

Now, none of these seems to produce the results in the same order you've defined in the question, and I don't know it's possible to produce that order reliably given the sample data you've shown.

# SQLfiddle demo #


How do you enable SQL Server 2019's result set caching?

Currently, in CTP 2.3, in so far as I can tell - you cannot enable this, even under DAC.


Same query uses 2 different plans in 2 sessions

You could create an optimizer extension to handle statistics for your collection, so the optimizer doesn't have to guess the value of its cardinality, selectivity and cost.

This can be done with the SQL statement ASSOCIATE STATISTICS:
http://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL339

In order to implement the statistics collection interface, please have a look at:
http://docs.oracle.com/database/121/ADDCI/ext_optimizer.htm#ADDCI2110

Another option is to use the undocumented CARDINALITY hint, which I would personally not recommend.

For an example of statistics association, please check the code below:

-- Creates a collection type
CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
/

-- Creates a function wrapper to the collection
CREATE FUNCTION collection_wrapper(
                p_collection IN varchar2_ntt
                ) RETURN varchar2_ntt IS
BEGIN
  RETURN p_collection;
END collection_wrapper;
/

-- Create an object type specification to handle statistics collection
-- on the function wrapper
CREATE TYPE collection_wrapper_ot AS OBJECT (
  dummy_attribute NUMBER,

  STATIC FUNCTION ODCIGetInterfaces (
                  p_interfaces OUT SYS.ODCIObjectList
                  ) RETURN NUMBER,

  STATIC FUNCTION ODCIStatsTableFunction (
                  p_function   IN  SYS.ODCIFuncInfo,
                  p_stats      OUT SYS.ODCITabFuncStats,
                  p_args       IN  SYS.ODCIArgDescList,
                  p_collection IN varchar2_ntt
                  ) RETURN NUMBER

);
/

-- Statistics interface implementation
-- Basically returns the count on the collection as its cardinality
CREATE TYPE BODY collection_wrapper_ot AS

   STATIC FUNCTION ODCIGetInterfaces (
                   p_interfaces OUT SYS.ODCIObjectList
                   ) RETURN NUMBER IS
   BEGIN
       p_interfaces := SYS.ODCIObjectList(
                           SYS.ODCIObject ('SYS', 'ODCISTATS2')
                           );
       RETURN ODCIConst.success;
   END ODCIGetInterfaces;

   STATIC FUNCTION ODCIStatsTableFunction (
                   p_function   IN  SYS.ODCIFuncInfo,
                   p_stats      OUT SYS.ODCITabFuncStats,
                   p_args       IN  SYS.ODCIArgDescList,
                   p_collection IN  varchar2_ntt
                   ) RETURN NUMBER IS
   BEGIN
      p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);
      RETURN ODCIConst.success;
   END ODCIStatsTableFunction;

END;
/

-- Associate the statistics type with the function wrapper
ASSOCIATE STATISTICS WITH FUNCTIONS collection_wrapper USING collection_wrapper_ot;

Now you can check the effects on the execution plan:

SQL> set autotrace traceonly explain

SQL> SELECT *
  2  FROM   TABLE(varchar2_ntt('A','B','C'));

Execution Plan
----------------------------------------------------------
Plan hash value: 1748000095

--------------------------------------------------------------
| Id  | Operation                             | Name | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                      |      |  8168 |
|   1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH|      |       |
--------------------------------------------------------------

SQL> SELECT *
  2  FROM   TABLE(
  3            collection_wrapper(
  4               varchar2_ntt('A','B','C')));

Execution Plan
----------------------------------------------------------
Plan hash value: 4261576954

------------------------------------------------------------------------
| Id  | Operation                         | Name               | Rows  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |     3 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| COLLECTION_WRAPPER |       |
------------------------------------------------------------------------

PS.: The code above was adapted from http://www.oracle-developer.net/display.php?id=427.


Restore SQL Server database using Windows Powershell 3.0

You're going to have to get into that exception to see what the problem is. Powershell keeps track of errors for you in a system variable called $Error, which is a heap (i.e. index 0 is the most recent error). I usually do something like this:

$e = $error[0]
$e.Exception
$e.Exception.InnerException
$e.Exception.InnerException.InnerException
...

Until I find the real error. It could be a problem with the actual restore (i.e. database with that name already exists, bad paths for the physical files, etc). But until you get to the root of that exception you won't know!


Analyze needed after a refresh materialized view?

Table statistics are not updated automatically.

They remain unchanged if refreshed with the CONCURRENTLY option.
Without CONCURRENTLY, a new file is written for the table, and the basic counts relpages and reltuples in the system catalog pg_class are consequently reset to 0. Table statistics (per column) in pg_statistic still remain unchanged, though.

I ran a quick test in Postgres 9.6 to confirm.

In some cases (where query plans don't depend on current statistics much) you may want to save the time and not run ANALYZE at all. In all other cases you'll want / need to schedule a manual ANALYZE myview; right after the REFRESH. Or you wait until autovacuum kicks in, which only happens if enough rows were changed (CONCURRENTLY actually runs DDL commands on the MV to only affect changed rows.)


Can't run digest() on PosrgreSQL 9.3 on RDS

The question was answered at the AWS forum by Shawn@AWS - The rds.extension list shows available, rather than installed, modules.

Thank you for using RDS Postgres. The "rds.extension" parameter shows you what extensions are available in RDS Postgres. You still need to execute the "create extension" command.
Using your test case:

postgres=> show rds.extensions;
... ,pgcrypto, ...
postgres=> create extension pgcrypto;
CREATE EXTENSION

postgres=> SELECT digest('blah', 'sha1');
digest
\x5bf1fd927dfb8679496a2e6cf00cbe50c1c87145
(1 row)

Addendum
pg_available_extensions shows which extensions are available and which are already installed:
postgres=> SELECT * FROM pg_available_extensions;

          name          | default_version | installed_version |                               comment
------------------------+-----------------+-------------------+---------------------------------------------------------------------
 chkpass                | 1.0             |                   | data type for auto-encrypted passwords
 xml2                   | 1.0             |                   | XPath querying and XSLT
 plpgsql                | 1.0             | 1.0               | PL/pgSQL procedural language
 pgcrypto               | 1.0             |                   | cryptographic functions
 postgres_fdw           | 1.0             |                   | foreign-data wrapper for remote PostgreSQL servers
...


In the end the only resolution that we had was to abandon the new connection type that uses "M" and go back to the legacy connection as explained here. So far it has not hung.


How to fix "The OLE DB provider "MSOLAP" has not been registered"?

The SQL Server Analysis Services drivers aren't actually installed as a part of the normal SQL Server installation unless you install SSAS on the server.

You can download the MSOLAP driver from the SQL Server feature pack and install it.  Then you can create the linked server.  Don't forget to install the 64bit install if you have a 64bit SQL Server or the 32bit if you have a 32bit SQL Server.


Why is Postgres CTE slower than subquery?

CTE's in PostgreSQL are an optimization fence. That means the query planner doesn't push optimizations across a CTE boundary.

Blog entry about this

I think a lot of this is silly though you can just write it like this.. Here we use CROSS JOIN LATERAL rather than the complex wrapping and NOT EXISTS rather than NOT IN

SELECT word,
  _created_at
FROM nps_reports
CROSS JOIN LATERAL unnest(regexp_split_to_array(
  nps_reports.comment,
  '[^a-zA-Z0-9]+'
)) AS word
WHERE nps_reports.comment <> 'undefined'
  AND nps_reports.comment IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM stop_words
    WHERE stop_words.stop_word = word
  )
ORDER BY _created_at DESC;

All of that said, whatever you're doing seems to be reinventing FTS. So that's also a bad idea.


Read: READ.

Not SELECT. Forget SELECT. SELECT privilege allows users to run SELECT .. FOR UPDATE, which modifies the metadata in the database blocks (not the user data) and locks the affected rows.

READ privilege is available starting with 12c.

Write: INSERT, UPDATE, DELETE.

Execute: EXECUTE

For the rest (Object Privileges), most people do not even know about them and can happily live without them.


Msg 2627 Violation of PRIMARY KEY constraint

tblShowteam has primary key on teams column  (

  create table tblShowteam(
  SetId           int,
  datum           date,
  teams           int,
  primary key (teams));

You are inserting rows with the same primary key (1) multiple time :

insert into tblShowteam values (1, '2014-06-28', 1);
--the same as  "insert into tblShowteam (SetId,datum,teams) VALUES (...,1);
insert into tblShowteam values (2, '2014-06-05', 1) ; --it's gonna fail , record with
-- teams = 1 already exists

I guess setId was supposed to be primary key, not teams.


SQL on a VM. Backup to Network location or just backup to VM

You should still backup away from the VM, or at least copy the backups off, preferably to a redundant location (either another SAN, or tape, whatever).

VMs can come back from a lot of things, but if you suffer a catastrophic failure, you may not be able to access the VM storage easily.

C: drive fills up
Windows dies a horrible death
A patch goes wrong at some level (VM, Windows, etc.)

Backing up to a VM-agnostic location gives you some breathing room. While the SAN may still be a single point of failure (remember the redundant location thing?), if you need to stand up a new VM in an emergency, you want the backups to be as easy to restore as possible.


SQL, sum one column where another column is the same and display as the third column

You didn't specify your RDBMS, but here is a solution using SQL Server

Declare @T table (a int, b int)
INSERT INTO @T
    (a, b)
VALUES
    (1, 23),
    (1, 14),
    (1, 3),
    (2, 45),
    (2, 16)
;

select
    a,b,sum(b) over (partition by a)  as c
from @T

| a | b  | c  |
|---|----|----|
| 1 | 23 | 40 |
| 1 | 14 | 40 |
| 1 | 3  | 40 |
| 2 | 45 | 61 |
| 2 | 16 | 61 |

If your RDBMS doesn't support Window Functions, you could use a common table expression to do the sum/group and then join that cte to the original table.  The results are the same.

;WITH _cte
AS (
    SELECT a
        ,sum(b) AS c
    FROM @T
    GROUP BY a
    )
SELECT t.a
    ,t.b
    ,c.c
FROM @T t
JOIN _cte c ON c.a = t.a


I think your problem is (as a_horse.. says) that your foreign key is not deferrable. Maybe creating your foreign key according the to code below solves your issue.

    ALTER TABLE documents
      ADD FOREIGN KEY (typ_document_id)
      REFERENCES typ_documents(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
      DEFERRABLE
      INITIALLY DEFERRED;


Logging info to different tables is not possible without rewriting parts of Ola's solution and creating new objects.

By default, you can log the output to the dbo.CommandLog table by using @LogToTable = 'Y'

You can then query the recent outputs for the integrity checks with this command:

SELECT TOP (100) [ID]
      ,[DatabaseName]
      ,[SchemaName]
      ,[ObjectName]
      ,[ObjectType]
      ,[IndexName]
      ,[IndexType]
      ,[StatisticsName]
      ,[PartitionNumber]
      ,[ExtendedInfo]
      ,[Command]
      ,[CommandType]
      ,[StartTime]
      ,[EndTime]
      ,[ErrorNumber]
      ,[ErrorMessage]
  FROM [MNGDB].[dbo].[CommandLog]
  WHERE CommandType = 'DBCC_CHECKDB'
  ORDER BY EndTime desc;

You can then change the filter to check the index rebuilds / reorgs by changing the filter to WHERE CommandType = 'ALTER_INDEX'.

If you really want to change the tables, you could create a copy of the dbo.CommandLog table and create dbo.CommandLogIndex for example.

CREATE TABLE [dbo].[CommandLogIndex](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [DatabaseName] [sysname] NULL,
    [SchemaName] [sysname] NULL,
    [ObjectName] [sysname] NULL,
    [ObjectType] [char](2) NULL,
    [IndexName] [sysname] NULL,
    [IndexType] [tinyint] NULL,
    [StatisticsName] [sysname] NULL,
    [PartitionNumber] [int] NULL,
    [ExtendedInfo] [xml] NULL,
    [Command] [nvarchar](max) NOT NULL,
    [CommandType] [nvarchar](60) NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NULL,
    [ErrorNumber] [int] NULL,
    [ErrorMessage] [nvarchar](max) NULL,
 CONSTRAINT [PK_CommandLog2] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Afterwards you have to create a new procedure, a copy of the dbo.CommandExecute procedure to use the dbo.CommandLogIndex table. I use Replace All (ALT + A) in SSMS to change the procedure calls.

As a last step, you could create a copy of the procedure dbo.IndexOptimize with the name  [dbo].[IndexOptimizeLogToTable] and change the dbo.CommandExecute procedure calls to dbo.CommandExecuteIndex.

Afterwards you run the procedure with @logtotable='Y'

  EXEC [dbo].[IndexOptimizeLogToTable] @databases = 'Test',@logtotable='Y'

And you should see the results in the new dbo.CommandLogIndex table.


How to find unused columns in MariaDB tables

If your table has columns that are defined to have NULL values, run this:
SELECT COUNT(given_column) FROM mytable;

This gives you the numbers of rows in mytable whose given_column is not NULL. If the result comes back as zero(0), that column is not populated with anything. You could drop that column with ALTER TABLE mytable DROP COLUMN given_column;
If your table has columns define as DEFAULT <some-value>, run this
SELECT COUNT(DISTINCT given_column) DistinctCount FROM mytable;

If this comes back as 1, you could most likely drop this column as well.
If the DistinctCount > 1 and you want to see that breakdown, run this
SELECT IFNULL(given_column,'Total Rows') ColumnsValues,COUNT(1) rowcount
FROM mytable GROUP BY given_column WITH ROLLUP;

These queries should give you a good idea which can be dropped.
CAVEAT : Please make sure your given_column is never referenced in your source code before dropping the column


Replicate a database to one secondary replica, but not the other secondary replica using Availability Group

You can setup 2 different availability groups and add databases accordingly, so that way you can manage it separately.


Display a row for each month in a range defined by two columns

I would suggest creating a dates table and cross-joining that table to your parts table, something like the following example.

Do this in tempdb to not step on anything else.

USE tempdb;

Create a Months table, with the Year, Month, and a starting date for each:

CREATE TABLE dbo.Months
(
    iYear int NOT NULL
    , iMonth int NOT NULL
    , dDate date NOT NULL
    , CONSTRAINT pk_Months
        PRIMARY KEY CLUSTERED
        (iYear, iMonth)
);

Use a CTE to populate rows into the table above.  This example creates years/months in the range of 1900-01-01 to 2173-10-01:

;WITH cte AS
(
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, cteDates AS
(
    SELECT dDate = DATEADD(DAY, 0, ROW_NUMBER() OVER (ORDER BY c1.Num))
    FROM cte c1
        CROSS APPLY cte c2
        CROSS APPLY cte c3
        CROSS APPLY cte c4
        CROSS APPLY cte c5
)
, cteMonths AS
(
    SELECT iYear = DATEPART(YEAR, cteDates.dDate)
        , iMonth = DATEPART(MONTH, cteDates.dDate)
    FROM cteDates
)
INSERT INTO dbo.Months(iYear, iMonth, dDate)
SELECT cteMonths.iYear
    , cteMonths.iMonth
    , DATEFROMPARTS(cteMonths.iYear, cteMonths.iMonth, 1)
FROM cteMonths
GROUP BY cteMonths.iYear
    , cteMonths.iMonth
ORDER BY cteMonths.iYear
    , cteMonths.iMonth;

Create the table with the sample "Parts" data:

CREATE TABLE dbo.PartCosts
(
    PartNum varchar(10) NOT NULL
    , StartDate date NOT NULL
    , EndDate date NOT NULL
    , Cost decimal(10,2) NOT NULL
);

INSERT INTO dbo.PartCosts(PartNum, StartDate, EndDate, Cost)
VALUES ('ABCD1', '2014-01-01', '2014-04-01', 50.99)
    , ('BDE2', '2014-01-01', '2014-03-01', 14.59)
    , ('ABCD1', '2014-04-01', '2014-05-01', 13.99);

Run the cross-join to get the results you're after:

SELECT pc.PartNum
    , pc.StartDate
    , pc.EndDate
    , pc.Cost
    , Period = CONVERT(varchar(7), m.dDate, 111)
FROM dbo.PartCosts pc
    CROSS JOIN dbo.Months m
WHERE pc.StartDate <= m.dDate
    AND pc.EndDate > m.dDate;

The results:

+---------+------------+------------+-------+---------+
| PartNum | StartDate  |  EndDate   | Cost  | Period  |
+---------+------------+------------+-------+---------+
| ABCD1   | 2014-01-01 | 2014-04-01 | 50.99 | 2014/01 |
| ABCD1   | 2014-01-01 | 2014-04-01 | 50.99 | 2014/02 |
| ABCD1   | 2014-01-01 | 2014-04-01 | 50.99 | 2014/03 |
| BDE2    | 2014-01-01 | 2014-03-01 | 14.59 | 2014/01 |
| BDE2    | 2014-01-01 | 2014-03-01 | 14.59 | 2014/02 |
| ABCD1   | 2014-04-01 | 2014-05-01 | 13.99 | 2014/04 |
+---------+------------+------------+-------+---------+


Postgres: flatten Key-Value (K-V) store with jsonb_agg

You need a two step aggregation:

select bar_id,
       jsonb_object_agg(externalidentifier, internals) as mappings
from (
  SELECT foos.bar_id,
         externalidentifier,
         jsonb_agg(internalIdentifier) as internals
  FROM foos
  GROUP BY bar_id, externalidentifier
) t
group by bar_id;

Your updated fiddle: https://www.db-fiddle.com/f/pKbFeRedD59s4f3VTnuyih/0


How to temporarily disable foreign keys in Amazon RDS PostgreSQL?

session_replication_role

I found an alternative way of disabling foreign keys -- https://stackoverflow.com/a/18709987

set session_replication_role = replica;

And re-enabling them with

set session_replication_role = default;

This works on RDS but still requires unusual privileges (i.e. not granted by default).

dropping and recreating FKs

Alternative solution is, as suggested in comments, to temporarily drop the FKs. This brings additional advantage that data is verified when FKs are re-enabled.

Dropping

create table if not exists dropped_foreign_keys (
        seq bigserial primary key,
        sql text
);

do $$ declare t record;
begin
    for t in select conrelid::regclass::varchar table_name, conname constraint_name,
            pg_catalog.pg_get_constraintdef(r.oid, true) constraint_definition
            from pg_catalog.pg_constraint r
            where r.contype = 'f'
            -- current schema only:
            and r.connamespace = (select n.oid from pg_namespace n where n.nspname = current_schema())
        loop

        insert into dropped_foreign_keys (sql) values (
            format('alter table %s add constraint %s %s',
                quote_ident(t.table_name), quote_ident(t.constraint_name), t.constraint_definition));

        execute format('alter table %s drop constraint %s', quote_ident(t.table_name), quote_ident(t.constraint_name));

    end loop;
end $$;

Recreating

do $$ declare t record;
begin
    -- order by seq for easier troubleshooting when data does not satisfy FKs
    for t in select * from dropped_foreign_keys order by seq loop
        execute t.sql;
        delete from dropped_foreign_keys where seq = t.seq;
    end loop;
end $$;


Logs are written to sequentially, so only one would ever be used at a time.  So, no, there is no benefit from having two on the same database.

EDIT: I looked over the part about different drives.  I suppose there could be a need in having two log files if you are working with limited drive space and your original log file would outgrow it's drive and you cannot move it to a larger drive for whatever reason.


Upload database records to AWS using TSQL?

perhaps you can share what exactly the software need, so without assuming things , I would describe approaches I use at my current work.

I normally use powershell to extract the data from dbs, I use SMO [link] http://sqlmag.com/powershell/using-sql-server-management-objects-powershell

and then you can upload the output to an s3 bucket using write-s3object powershell command , this can be done from any customer environment which has access to internet, and with using the awspowershell module which you can download [link] https://aws.amazon.com/powershell/ and from a central EC2 in your aws you can read this data from s3-bucket , you can use read-s3object to get the data from the s3 bucket


Suggest better design pattern for reverse DNS lookups

Do not do it in SQL. From the few things that can be categorized as 'stupid use of SQLCLR', making expensive lengthy network calls ranks as #1. At the very very very very least, make sure the CLR code calls Thread.BeginThreadAffinity() before it goes into waiting for the intertubez to respond (DNS lookup and reverse lookup included).

The proper way to handle this is to use an external process, place the IPs to resolve in a queue, dequeue in batches and resolve several (tens) of IPs in parallel using asynchronous I/O, eg. the non-obsolete Dns.BeginGetHostEntry().


ERROR: please confirm the keyfile is match and keyring plugin is loaded

I have solved my issue:

Make sure that the backup taken by xtrabackup and the keyring file in /var/lib/mysql-keyring/keyring are from the same server.

Also after copying the keyring file to the destination server, make sure to chown mysql:mysql to it.

The Keyring file should be copied as is not the contents of the file.

To verify if things are working, you can create a new DB and some new tables, also insert some data, that are encrypted then run the following

SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS
FROM INFORMATION_SCHEMA.TABLES
WHERE CREATE_OPTIONS LIKE '%ENCRYPTION="Y"%';

You need to get the result tables. Now try to select from those tables, you should see the data inserted.


MariaDB 5.5 -- how to customize password rules

A couple of comments:

Re: "the standard linux-user-name-matches-the-mysql-account-name authentication" - I assume you mean using the unix_socket plugin for authentication. This only works on localhost, i.e. the user has to be logged in on the database server.
There is no MariaDB 5.7, but there is however a MySQL 5.7.
There is no need to build your own RPM for MariaDB 10.x on RHEL7. You can just add the relevant MariaDB 10.x .repo file to your /etc/yum.repos.d/ directory. You can use the repo config tool on mariadb.org.

It's up to you to decide whether it's worth the effort to upgrade to MariaDB 10.1+ and thereby be able to use the password checking plugins - simple_password_check and cracklib_password_check. (10.4 is now GA, though at the moment this is still relatively new, so you may want to consider 10.3 or older as a more stable release.)


MySQL - Get counts of `car models` by `car make`

You have to GROUP BY both make and model, to get the count
SELECT `make`,`model`, COUNT(*) as `countmakemodel`
FROM `cars_new`
WHERE `make` IN ('Chrysler', 'Ferrari', 'Citroen', 'Dodge')
GROUP BY `make`,`model`


Extended Entity-Relationship Diagram: Do the sub classes have foreign keys or primary keys?

It's not an either / or situation. The answer for your particular system may exists as a combination of these two options. Let me explain.

If the only attribute in common between the Programmer, Engineer and Admin tables are the Emp# then I'd likely implement them as separate tables. Each would have Emp# as the primary key. I would not implement the Employee table at all since it added no additional value to the run-time system.

If the three sub-types had many columns in common, say Name, HireDate, Department .. HolidayBalance, ContactNumber etc., and only a few sub-type specific values (as you've annotated on your diagram) you could put all columns in Employee and make the inapplicable ones (e.g. a Programmer's EType) NULL. A view can be defined for each sub-type if excluding the NULLs becomes tedious.

Likely the truth lies somewhere between these two - there are enough specific attributes in each sub-type to warrant its own table, but enough common ones to justify an Employee table, too. In this case I'd make Emp# the primary key of Employee and also each sub-type would have the primary key of Emp#, too. Further I'd define Programmer.Emp# to be a foreign key referencing Employee.Emp#, and the same for the other sub-types. In this way uniqueness is maintained within each sub-type and so is integrity between the sub-type and super-type. In other words, I'd make a one-to-one relationship between Employee and each sub-type.

I would not implement a surrogate key in each of the sub-types (for example ProgrammerId) as it adds nothing that Emp# can't already achieve.

Note that it is difficult to declaratively ensure mutual exclusion between the sub-types. This will be enforced in the application or through triggers.


You can only return single row results so grouping in the way you are envisioning is not all that natural. There is a way, however, to use aggregate functions to combine groups and get the exact JSON result you are looking for.

SELECT
  row_to_json(all_shows)
FROM (
  SELECT
    array_agg(bands) bands,
    shows.*
  FROM shows
  JOIN (
    SELECT show_id, array_agg(artists.name)
    FROM artist_shows
    JOIN artists ON (artist_shows.artist_id = artists.id)
    GROUP BY show_id
  ) bands ON bands.show_id = shows.id
  WHERE date >= now()
) all_shows

Here we are using a subquery (bands) to group all artists names based on the show id and then join the aggregate array into the show row. Lastly you can combine all rows and return a JSON representation of the data. If you don't care to have Postgres wrap everything up into JSON then you can just use what is in the initial FROM clause.


Best way to create a daily snapshotted read-only db in RDS?

What's wrong with bullet 3? Schedule a Lambda function written in your preferred language to run at midnight that calls the RDS API to perform a snapshot.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.html

You want this part near the bottom:

https://rds.us-east-1.amazonaws.com/
    ?Action=CreateDBSnapshot
    &DBInstanceIdentifier=mydbinstance
    &DBSnapshotIdentifier=mydbsnapshot
    &SignatureMethod=HmacSHA256
    &SignatureVersion=4
    &Version=2013-09-09
    &X-Amz-Algorithm=AWS4-HMAC-SHA256
    &X-Amz-Credential=AKIADQKE4SARGYLE/20140423/us-east-1/rds/aws4_request
    &X-Amz-Date=20140423T161105Z
    &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
    &X-Amz-Signature=e9649af6edcfbab4016f04d72e1b7fc16d8734c37477afcf25b3def625484ed2


Check your configuration.  Do SHOW VARIABLES; on each machine, then diff the output.  It smells like one of these is different:
innodb_buffer_pool_size
sync_binlog
innodb_flush_binlog_on_trx_commit
or maybe something else.

Now to be hostile...  RAID-10  (or -5) without a battery-backed-write-cache is hardly worth having.  Sure, you gain recovery from a disk crash (a rare event), but meanwhile, you don't get the write speedup of the write cache.


POSTGRES Compare a json arrays against another array

If you mean an exact string match then you can do:

SELECT * FROM table where items::text = '[{"id":"","name":""},{"id":"","name":""}]'

Note that JSONB does not necessarily keep the order in the objects the same so it might not work all the time because the order of items might change on the output.

You can lookup the Postgres docs for more advanced JSON operations where you can actually look inside the values.


Can't connect to/from SQL Server 2008 from my Windows 2008 server

I'd check Services to ensure both are running - the name in Services for both SQL Servers will also confirm the instance name. On my laptop, I have MSSQLSERVER, so I can connect to that with either the machine name (LAPTOP), or with .    My other instance is called SQLTEST2, so I can connect to LAPTOP\SQLTEST2.

If that fails, go looking in both sets of binaries in Program Files. Each instance will have ERRORLOGs in the the respective log folders - that might have a clue in there, such as a login error.

Incidentally, since they are both on the same machine, ensure Shared Memory is enabled for both.


How To Exclude Queries Returned From mysqldumpslow Command

OK - you should check out the page here,

    (Due to a limitation in the parser, a space character must precede
and follow the SQL_NO_CACHE keyword; a nonspace such as a newline
causes the server to check the query cache to see whether
the result is already cached.)

Now, it is possible that the syntax that you are using doesn't correspond to the bit about "newlines" above - I'm not sure. Maybe you could try running your queries directly from the command line?

[EDIT] in response to OP's comment.

mysqlslowdump -g '!(SQL_NO_CACHE)' /path/to/my/log
mysqlslowdump -g '^!(SQL_NO_CACHE)$' /path/to/my/log

I've never come across this problem, but is it possible that rewriting your code with spaces surrounding "SQL_NO_CACHE" could have some effect?


In MySQL can I drop a database by moving the directory containing the database?

SHOW DATABASES;

I suspect it will not show the database that you "moved".

Moving (or deleting) the directory would be unsafe it it contained any InnoDB tables.  It is safe for MyISAM tables, and probably MERGE and Archive.

ibdata1 contains information about InnoDB tables.  Non-InnoDB table types are discovered by looking in the file system.


First make sure you have created a Cluster and a Node definition file, then re-run the command .This should fix this error.


What happens when MySQL/InnoDB buffer pool size is small and a large transaction is run causing the buffer pool overflow?

I just encountered a question 5 days ago : InnoDB errors and crashes with MyISAM database?

When troubleshooting the issue in a live chat session, I discovered that dirty pages from  Temporary InnoDB Tables were occupying the InnoDB Buffer Pool. The file ibtmp was 482MB with 75% of the buffer pool being dirty since innodb_max_dirty_pages_pct was 75. The Buffer Pool was only 128M. So, then, what happens ?

The Buffer Pool has to flush very frequently when the Buffer Pool is too small (which is what I encountered with a virtually empty database).

If you run SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';, you will see something like this:

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%';
+---------------------------------------+--------------------------------------------------+
| Variable_name                         | Value                                            |
+---------------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status        | Dumping of buffer pool not started               |
| Innodb_buffer_pool_load_status        | Buffer pool(s) load completed at 190925  9:50:58 |
| Innodb_buffer_pool_resize_status      |                                                  |
| Innodb_buffer_pool_pages_data         | 247                                              |
| Innodb_buffer_pool_bytes_data         | 4046848                                          |
| Innodb_buffer_pool_pages_dirty        | 0                                                |
| Innodb_buffer_pool_bytes_dirty        | 0                                                |
| Innodb_buffer_pool_pages_flushed      | 36                                               |
| Innodb_buffer_pool_pages_free         | 7945                                             |
| Innodb_buffer_pool_pages_misc         | 0                                                |
| Innodb_buffer_pool_pages_total        | 8192                                             |
| Innodb_buffer_pool_read_ahead_rnd     | 0                                                |
| Innodb_buffer_pool_read_ahead         | 0                                                |
| Innodb_buffer_pool_read_ahead_evicted | 0                                                |
| Innodb_buffer_pool_read_requests      | 1090                                             |
| Innodb_buffer_pool_reads              | 214                                              |
| Innodb_buffer_pool_wait_free          | 0                                                |
| Innodb_buffer_pool_write_requests     | 325                                              |
+---------------------------------------+--------------------------------------------------+
18 rows in set (0.01 sec)

mysql>

You can monitor Innodb_buffer_pool_pages_dirty with

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_dirty%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| Innodb_buffer_pool_pages_dirty | 0     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql>

This number may fluctuate with pages being flushed and pages coming in, but this will definitely bottlenck InnoDB. Your solution is just to increase the Buffer Pool size.

I talked about other ill effects before (See my post what happens when InnoDB logspace is too small)


In addition to what Justin Cave wrote, since PostgreSQL 9.1 you can speed up any search with LIKE (~~) or ILIKE (~~*), and basic regular expression matches, too (~). Use the operator classes provided by the module pg_trgm with a GIN or GiST index to speed up LIKE expressions that are not left-anchored. To install the extension, run once per database:

CREATE EXTENSION pg_trgm;

Create an index of the form

CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops);

Or:

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

Creating and maintaining a GIN or GiST index carries a cost, but if your table is not heavily written, this is a great feature for you.

Depesz has written an excellent article in his blog about the new feature.

GIN or GiST?

These two quotes from the manual should provide some guidance

  The choice between GiST and GIN indexing depends on the relative
  performance characteristics of GiST and GIN, which are discussed
  elsewhere. As a rule of thumb, a GIN index is faster to search than a
  GiST index, but slower to build or update; so GIN is better suited for
  static data and GiST for often-updated data.

But for "nearest neighbour" type of queries with the using the distance operator <->:

  This can be implemented quite efficiently by GiST indexes, but not by
  GIN indexes.


Create result set with all records from LEFT table, and from the RIGHT table only bring records that do no exist in left already

Assuming all the columns are compatible:

SELECT * from table1
UNION ALL
SELECT table2.* from table2 left join table1 using (customer_id, date)
    where table1.customer_id is null

If not all the columns are compatible, you will have to do a bit of tedious manipulation of the select lists, rather than using *.


Unless you have cross-database ownership chaining enabled (off by default) and have the same User in both databases, then giving access to an object in one database does not imply anything permission-wise to other databases. Of course, I am not recommending that cross-database ownership chaining be enabled, and neither is Microsoft.

Instead, you should create a certificate that can be used to sign modules and act as a proxy for permissions between the databases. The catch is that they work on stored procedures, function (non-Inline TVFs), assemblies, and triggers. But this method allows for those Users that should not have any access to the [HR] database to actually not have any access themselves: it is the code (i.e. the module) that has the access.

The basic concept:

Create certificate in [Reporting]
Create stored procedure in [Reporting] (that selects directly from tables in [HR])
Use ADD SIGNATURE to sign the new stored procedure using the certificate
Backup the certificate to a file
Create certificate in [HR] FROM the backup file you just created (the certificate needs to be identical in both places!)
Create user in [HR] FROM the certificate
GRANT SELECT permission on appropriates tables in [HR] (i.e. the ones references in the new stored procedures in [Reporting]) to the new certificate-based user
Go home and enjoy the rest of the day :)

Some resources:

Module Signing Info
Ownership Chains
ADD SIGNATURE
Signing Stored Procedures in SQL Server
Tutorial: Signing Stored Procedures with a Certificate
Tutorial: Ownership Chains and Context Switching

OR, if you prefer to stick with the simplicity of using views, then your views are in the wrong database. You need to instead:

Add the appropriate User(s) to the [HR] database so that there is basic access, but do not grant any permissions to any tables (and hopefully no group or role membership will imply such permissions)
Create the desired Views in the [HR] database
GRANT SELECT only to these Views, and not to anything else, to these Users


storing data from different sources in a single column?

It sounds like magento customers, shoppify customers, etc. are subclasses (subtypes) of the customer class.  This is the terminology in object modeling.

In ER modeling, the terminology is usually generalization/specialization.

There are two techniques often used when designing tables to fit this situation.

One is single table inheritance.  Store all the customers in one table, with a customer_type field, and all the fields for all the types.  Leave fields NULL when they don't apply to a particular customer.

Another is class table inheritance.  Have one table for customers with all the common data, and only that data.  Have separate tables for each subclass (subtype) of customer, with data that is specialized to that subtype.  In the subclass tables, don't generate a distinct id for the rows, because you don't need to.  Use a copy of the customer id as both the PK for the specialized table and an FK to join with the correct row in the generalized table.

Joining specialized data and generalized data is pretty fast, and fairly simple.

With either technique, the problem you originally posed goes away.  You can make an FK reference to the customer table, and access both the generalized data and the specialized data.

If you search on all the buzzwords I've given in here, you'll get lots of useful articles on the web that treat the subject in more depth.  You can also explore the subtypes tag right here.


1&2 - Every update query must update the data for the table, wherever it may appear - in the clustered index (always), in any index that has the column in its definition, and in any index which just includes this column.

3 - Removing records may or may not help, it depends on 4:

4 - Probably, because sometimes it will not use the index, but will instead choose to scan the entire table. If it's scanning the entire table, then reducing the size of the table (via (3) above) will help. It will scan the entire table if, so far as the optimizer is concerned, that will be more efficient than using the index, then accessing the clustered index to complete the operation. It may choose to do this because it's actually true that the index will not help, or if statistics are out of date, or just sometimes it gets this wrong. Examine execution plans to determine if this has happened.

4a - If there's a lot of activity on the table, then other operation may have locks that prevent your query from processing as fast as it could if it was the only activity occurring in the database. Waiting for locks can take seconds, minutes, or hours - it depends on how timely the other operations are

5 - Solution to what?

824392 isn't that many rows, but it may make for a big table - it depends on how wide those rows are, really.


Oracle index with REGEXP_REPLACE is it a good idea?

I don't have direct experience of an index with this kind of REGEX so I would avoid it by creating a 'standardised' telephone number column.

Create a process to go through the telephone numbers and store the clean, formatted version in the new column.

Perform the 'standardising' process on the user input and use that as a search against the new telephone column - which you will have indexed.

The other option is to go through a data clean up process to standardise all the telephone numbers and ensure that any on-going input method applies the same process/validation.

EDIT:

The virtual column option mentioned above may be of use to you as well.

ALTER TABLE contacts ADD(search_telephone AS (REGEXP_REPLACE(tel_no, 'pattern', 'replace')).


How to pull rows only from the weekends?

Use the DATENAME() function and specify the datepart as weekday.

select ID, Name, Salary, Date
from dbo.yourTable
where datename(weekday, Date) in ('Saturday', 'Sunday');

As Aaron pointed out, this relies on the language being set to English.  Likewise, you could use the DATEPART() function with weekday and test for Saturday and Sunday values.


Well, usually I use the following approach: instead of altering a live column type (if it's not something really trivial), I add another column of a new type, then set all the values in it (more on it below), then delete the old column after doing all that (and checking that all works ok), using its name as a name for a new column.

Perhaps this approach can still be used. To convert old values use something like this:

UPDATE my_table
   SET datetime_column = STR_TO_DATE(varchar_column, '%d-%m-%Y')

Please take note that actual format literal might be different in your case; consult the manual and test it for some exact value to be sure.


SQL Server 2005, I'm seeing about 100 'jobs' in the SQL Agent>jobs node in SSMS. They appear as hexadecimal strings not with their real names:

They are from reporting service subscriptions.

Below SQL will give you list of the job names against the actual reports

select ScheduleID
    ,Path
    ,name
    ,s.Description
from ReportServer.dbo.catalog c
inner join ReportServer.dbo.Subscriptions s on c.ItemID = s.Report_OID
inner join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID

Even better (got it from this link):

  A SQL Server Agent job is created to control the scheduled execution of the report, and this is stored in the sysjobs and sysjobsteps of the MSDB database. The agent job name is a guid value that represents the ScheduleId (yes, in case you've not yet noticed, this makes your Agent Job list messy with a bunch of guids that make it hard to work with your non-subscription jobs, we are hearing you:).

select 'Report' = c.Path
    ,'Subscription' = s.Description
    ,'SubscriptionOwner' = uo.UserName
    ,'SubscriptionModBy' = um.UserName
    ,'SubscriptionModDate' = s.ModifiedDate
    ,'ProcessStart' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessStart)
    ,'NotificationEntered' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.NotificationEntered)
    ,'ProcessAfter' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessAfter)
    ,n.Attempt
    ,'SubscriptionLastRunTime' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.SubscriptionLastRunTime)
    ,n.IsDataDriven
    ,'ProcessHeartbeat' = dateadd(hh, DATEDIFF(hh, Getutcdate(), Getdate()), n.ProcessHeartbeat)
    ,n.Version
    ,n.SubscriptionID
from Notifications n
inner join Subscriptions s on n.SubscriptionID = s.SubscriptionID
inner join catalog c on c.ItemID = n.ReportID
inner join Users uo on uo.UserID = s.OwnerID
inner join Users um on um.UserID = s.ModifiedByID


The Logical Type of The Document in CouchBase

I talked about this recently in my blog post on data modeling.

It really depends on how your application will be interacting with your documents. If you're going to use a lot of N1QL, it probably makes sense to use the 'type' field like you are doing. (I don't know what tooling is available for Go, but Linq2Couchbase can help you manage this 'type' field).

However, if you plan to use direct key access a lot (because it's faster or it makes more sense to do so from your application(s)), then you might want to use a meaningful key to convey relationships or other information. E.g. if I have a "product::123", then I can infer that "product::123::locations" is a related document. I don't think there is a way to get "product" from that type of key without some kind of string parsing overhead (SPLIT or SUBSTR N1QL functions). Though it may be possible to design an index for that? You may want to ask the wizards in our N1QL forum.

Or it might make sense to do a combination of both. In general, there isn't a right or wrong way to design your keys. It really depends on the data access patterns you want to use.


The subqueries in your query are al counting all rows of the table. They should probably only be counting for the "shyft" from the main table; something like:

SELECT shyft,
(SELECT COUNT(tkno) FROM Table1 WHERE shyft=x.shyft AND dete = DATE(now())) AS 'Todays Tickets',
(SELECT COUNT(tkno) FROM Table1 WHERE shyft=x.shyft AND dete = DATE_SUB(DATE(now()), INTERVAL 1 DAY)) AS '1 Day Ago',
(SELECT COUNT(tkno) FROM Table1 WHERE shyft=x.shyft AND dete = DATE_SUB(DATE(now()), INTERVAL 2 DAY)) AS '2 Days Ago'
FROM Table1 x
GROUP BY shyft;


MySQL Database inside a VM on a high availablity cluster

I would suggest separate instances for your critical production databases and consolidation to a single instance only for the small and less important MySQL databases.

In a consolidated MySQL environment, a single instance with n databases, any database could potentially impact on the others.

Additionally, it would be better to configure appropriately at least the critical databases and that will be easier when you're using separate instances.

Another important point is backup: different storage engines need different backup methods: if you use only transactional storage engines, you could avoid locking during the backup (xtrabackup, MEB, --single-transaction for mysqldump), non-transactional storage engines will require table lock during the backup. You could manage this by using flexible backup procedures, but if you need to generate a consistent backup for replication slave provisioning, for example, it would be more complicated if you have mixed engines across the databases.


Delete publications from the Local Publications folder in Microsoft SQL Server Management Studio.

To delete a publication

1.Connect to the Publisher in Management Studio, and then expand the server node.

2.Expand the Replication folder, and then expand the Local Publications folder.

3.Right-click the publication you want to delete, and then click Delete.


What are the differences between leaf and non-leaf pages?

Think about it like this: leaf level pages are the end of the road for the data search through the B-tree structure of an index. They contain the data defined in the index definition (or in the case of a clustered index, all of the table's data) and a row locator to the data row (in the case of a nonclustered index).

Non-leaf level pages contain the "road map" to the leaf level pages/data by including the key value as well as a pointer to either another non-leaf level page (depending on the B-tree depth and location of the intermediate page) or the resulting leaf level page (index page for a nonclustered index, and data page for a clustered index).

Edit:  Here's a good image to put a picture to the idea.


MySQL on SSD - what are the disadvantages?

Modern SSDs are a lot more reliable than people tend to think in these discussions if you buy decent drives. If drive failure is a concern then RAID the drives as you would with spinning metal based drives.

One concern with SSDs is write lifetime, but for all but pathological cases if you have good drives the reliability shouldn't be much (if any) worse than traditional drives. The other issue is random write performance which can be a problem on some drives under heavy write loads (this is mitigated somewhat with drives that have a larger "over allocation" factor, and you'll not find it slower than spinning metal manages). Neither of these issues is going to be significant unless you have write-heavy loads.

"20% of operations are inserts or updates" isn't particularly meaningful as they could be affecting one row each or many millions. From a performance PoV the best thing to do is test your app and run realistic benchmarks for it. From a reliability PoV with good drives you should be as safe as with spinning metal, though of course use RAID1 (or 10) and backups to cover all the bases as you would normally.

Any recommendation more specific than that is going to be very opinion rather than fact based. For my own PoV: these days I put SSDs anywhere where I can afford the larger $/Gb and cope with the smaller individual device sizes, but I don't trust a single SSD any more than a single spinning-metal drive so anything important gets both RAIDed and (more importantly) regularly backed up and those backups regularly tested (a step that too often gets skipped).


How to restore the privileges of the user postgres in PostgreSQL?

If you edit your pg_hba.conf to allow login as one of the other superusers, then you can login as one of those and then alter role postgres superuser.


On the first "use" command after login, MySQL scans database, tables and columns name for auto completion. If you have many db, tables it could take a while.

To avoid that, launch your client with the -A option (or --no-auto-rehash)

mysql -uroot -p -A

You could also add the disable_auto_rehash variable in your my.cnf (in the [mysql] section) if you want to disable it completely. This change does not require a reboot (it is a client, not server, variable).


MySQL copy all views from one database to another in stored procedure

You need to call SHOW CREATE VIEW to get the view code.  However, that will have the database name in the code.  You would need to parse the output, replace that database name, then execute dynamic statements with PREPARE and EXECUTE.

Stored procedures may be the most difficult way to manage this. For example, you can mysqldump can dump/load the views from one database to another in one line : https://stackoverflow.com/questions/1658204/backing-up-views-with-mysql-dump

Other tools like Flyway and Liquibase can help with schema management as well.


Using IF and other conditions in T-SQL (trying to import data from Oracle using SQL Server SSIS)

assuming you can see the oracle table from your SQL Server

INSERT INTO SQLTable( ID, field2 )
SELECT ID,
       field2
FROM OracleTable o
WHERE NOT EXISTS( SELECT NULL FROM SQLTable s WHERE s.ID = o.ID )

this is just the simple SQL for inserts. I have not used SSIS so YMMV


Full GROUP BY in MySQL 5.7 breaks de-dupe queries

SELECT DISTINCT dedups on all columns. Sounds like that is what you want. GROUP BY has other purposes.

But I smell another problem here...  A common pattern is

SELECT
    ... some columns, some aggregates
    FROM a
    JOIN b  ON ...
    WHERE ...
    GROUP BY some columns

This is rather inefficient (even when you don't run into the "only group by" restriction).  First the JOIN is done.  This inflates the number of rows, and builds a big tmp table.  Then the GROUP BY is used to deflate it, often back to exactly the number of rows that are in one of the tables.

There are a couple of workarounds.  But the depend on the details of the query (please show us the query).

If all the aggregates are on columns in, say, b, then use a derived table to first compute all the aggregates from b, then join the result of that to a to get the rest of the columns.

If there is only one aggregate, and even if the JOIN is actually LEFT JOIN, then

SELECT some columns,
       ( SELECT ... FROM b WHERE ... GROUP BY ...) -- correlated subquery
    FROM a
    -- no GROUP BY needed here, so no inflate-deflate inefficiency


Is it worth checking value with select before updating

Well, "13.2.11 UPDATE Syntax" suggests, that no writing is done, when the value doesn't actually change.

  If you set a column to the value it currently has, MySQL notices this and does not update it.

So the extended WHERE clause isn't needed to prevent unnecessary writes.

But you might get a tiny performance benefit from the extended WHERE clause as if not record to update was found, the logic to check, if the row actually needs to be changed won't run. But really, that's tiny.

And the effort for retrieval is there in either way. You can only try to support it with indexes (on id and lock (compound) for the extended version and on id for the other).


What is different bettween Cassandra NoSQL data model and SQL data model

Cassandra is a Columnar Store NoSQL Database, Which goes beyond the Key Value pairs.

A Column family in cassandra is a container for rows, like table in any RDBMS.

And These are Stored and represented in Json Formats.

Below are some links where you can get more info:

http://wiki.apache.org/cassandra/DataModel

http://www.datastax.com/docs/0.8/ddl/index

http://www.javageneration.com/?p=70


When using XE Profiler in SSMS 17.3, the 'Database Name' column shows as either NULL or an empty string

I saw the same thing you saw. I went into the actual Properties of the Extended Event and noticed that 'collect database_name' was not checked.  I checked that box and restarted the extended event session and the database_name showed up. (I had to go into each 'selected event' and check the box for 'collect database name')

Note:
The XE Profiler extended events are actually under the regular Extended Events node.  They are prefixed with QuickSession - That's where you change the properties. Apparently, the nodes under XE Profiler are simple shortcuts.


Can I use SQLite to share data among different applications?

Yes.

From the FAQ:

  Can multiple applications or multiple instances of the same application access a single database file at the same time?

  Multiple processes can have the same database open at the same time.
  Multiple processes can be doing a SELECT at the same time. But only
  one process can be making changes to the database at any moment in
  time, however.

  SQLite uses reader/writer locks to control access to the database.
  (Under Win95/98/ME which lacks support for reader/writer locks, a
  probabilistic simulation is used instead.) But use caution: this
  locking mechanism might not work correctly if the database file is
  kept on an NFS filesystem. This is because fcntl() file locking is
  broken on many NFS implementations. You should avoid putting SQLite
  database files on NFS if multiple processes might try to access the
  file at the same time. On Windows, Microsoft's documentation says that
  locking may not work under FAT filesystems if you are not running the
  Share.exe daemon. People who have a lot of experience with Windows
  tell me that file locking of network files is very buggy and is not
  dependable. If what they say is true, sharing an SQLite database
  between two or more Windows machines might cause unexpected problems.

  We are aware of no other embedded SQL database engine that supports as
  much concurrency as SQLite. SQLite allows multiple processes to have
  the database file open at once, and for multiple processes to read the
  database at once. When any process wants to write, it must lock the
  entire database file for the duration of its update. But that normally
  only takes a few milliseconds. Other processes just wait on the writer
  to finish then continue about their business. Other embedded SQL
  database engines typically only allow a single process to connect to
  the database at once.

  However, client/server database engines (such as PostgreSQL, MySQL, or
  Oracle) usually support a higher level of concurrency and allow
  multiple processes to be writing to the same database at the same
  time. This is possible in a client/server database because there is
  always a single well-controlled server process available to coordinate
  access. If your application has a need for a lot of concurrency, then
  you should consider using a client/server database. But experience
  suggests that most applications need much less concurrency than their
  designers imagine.

  When SQLite tries to access a file that is locked by another process,
  the default behavior is to return SQLITE_BUSY. You can adjust this
  behavior from C code using the sqlite3_busy_handler() or
  sqlite3_busy_timeout() API functions.


I removed one of the subqueries from yours but fundamentally as mysql doesn't support count over partition etc, it will need more than one pass through the table:

Select test.mid, test.pid, A.cnt as midCount, count(*) as pidCount
from test join
(Select mid, count(*) as cnt from test group by mid) A
on test.mid =  A.mid
Group by mid, pid


SQL Server 2012 Install error "The instance selected for installation is already installed and clustered on computer..."

Installing Windows Server from scratch would remove any issues from the failed install and ensure that there are no problems going forward when patching or changing the SQL Server instance.

If this is going to be a production server removing any chance for downtime in the future should be the biggest concern.


Does it make sense to use SQL Server's bracket notation in hand written code?

Standard SQL uses the double quote " for quoted identifiers. SQL Server supports this using the QUOTED_IDENTIFIER option (ANSI_QUOTES in mySQL). Standard SQL improves portability in general  and in this case would port to Oracle. Similarly I'd change SQL keywords to upper case (Intermediate SQL-92 requirement) and expand int to INTEGER (entry level SQL-92 requirement).

Needlessly using quoted identifiers, whatever the flavour, should be avoided, IMO.


How to prompt sql if there is no exception

Using

EXCEPTION
when value_invalid then
null;

is definitively a bad idea, you should not silently suppress the error message.

Try this:

DECLARE
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM SET sga_max_size=12g';
END;
/

Note, inside a PL/SQL Block you have only direct privileges. Privileges granted by role (e.g. DBA role) do not apply inside the block.


Exit sqlplus.

Make sure your os user is part of the DBA group (ORA_DBA in Windows).

Make sure ORACLE_SID is set to the SID of the database and unset TWO_TASK:

Windows:

set ORACLE_SID=ORCL
set TWO_TASK=

or

Unix/Linux

ORACLE_SID=ORCL
TWO_TASK=
export TWO_TASK

Now, connect as sysdba:

sqlplus / as sysdba

Now, startup the database:

SQL> startup mount;

SQL> alter database open;


Postgresql: Get pg_stat_statments.calls but per PID

pg_stat_statement cannot produce this kind of information.

You'd have to set log_statement = all, then you get all these statements (and all others) in the log. Make sure that you have %p in your log_line_prefix.

Be careful: logging all statements can bring a busy server down. In PostgreSQL v12, you can set log_transaction_sample_rate to log only a certain sample of transactions, then you can estimate the total count.


SQL Server authentication failed after server promote

Found it!
I was able to create a privileged user while putting the DB in single-user mode.
Here is the link: http://v-consult.be/2011/05/26/recover-sa-password-microsoft-sql-server-2008-r2/


Assigning a logical name to a sql instance

What I can think of is a server alias.

In SQL Server Configuration Manager, expand SQL Native Client Configuration, right-click Aliases, and then click New Alias.


Is there anyway that I can manually create a PID for the MySQL instance?

No, you do not want to go there. Why ?

The mysqld_safe script is responsible for creating and destroying the PID file

You can see it when you grep for it

$ MYSQLD_SAFE_SCRIPT=`which mysqld_safe`
$ grep -n pid_file < ${MYSQLD_SAFE_SCRIPT}
26:pid_file=
198:      --pid-file=*) pid_file="$val" ;;
638:if test -z "$pid_file"
640:  pid_file="$DATADIR/`hostname`.pid"
642:  case "$pid_file" in
644:    * )  pid_file="$DATADIR/$pid_file" ;;
647:append_arg_to_args "--pid-file=$pid_file"
720:if test -f "$pid_file"
722:  PID=`cat "$pid_file"`
731:  rm -f "$pid_file"
732:  if test -f "$pid_file"
735:$pid_file
782:  rm -f $safe_mysql_unix_port "$pid_file"   # Some extra safety
796:  if test ! -f "$pid_file"      # This is removed if normal shutdown
834:    numofproces=`ps xaww | grep -v "grep" | grep "$ledir/$MYSQLD\>" | grep -c "pid-file=$pid_file"`
840:      PROC=`ps xaww | grep "$ledir/$MYSQLD\>" | grep -v "grep" | grep "pid-file=$pid_file" | sed -n '$p'`
859:log_notice "mysqld from pid file $pid_file ended"
$

What you should do is

service mysql stop
... delete the pid file if it is still exists after shutdown
service mysql start

If you have to restore backups from MySQL Enterprise, make sure there is no PID file.

Preferably, you could setup MySQL Replication and do the backups on the Slave. Then, when it is time for backup, run

STOP SLAVE;
SET GLOBAL innodb_fast_shutdown=0;
service mysql stop (The pid file should get deleted by mysqld_safe)
Make your backup
service mysql start (The pid file should get created by mysqld_safe)

CAVEAT

If you ever have a problem with service mysql stop taking forever and still not shutting down mysqld, this quickly indicates that the socket file is missing. PID file will be out of whack as well.

In that happens, do a TCP/IP shutdown of MySQL

First do this

mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp ping

If this echoes

mysqld is alive

then you can run

mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp shutdown

GIVE IT A TRY !!!


in place updates causes forwarded records

This is due to compression.

Compressed table has no more FixedVar row format, so even if you use fixed-length columns they aren't stored in Fixedvar way, no more fixed space reserved for (even NULL) values.

When you use page compression, frist ROW COMPRESSION is applied.
And here what we have:Row Compression Implementation

  It stores fixed character strings by using variable-length format by
  not storing the blank characters. Note

  NULL and 0 values across all data types are optimized and take no
  bytes.

Here is my repro where I use dbo.Nums, table of 1000000 natural numbers, to fill my two tables: dbo.test_comp with page compression enabled and dbo.test_no_comp with no compression:

CREATE TABLE dbo.test_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
)
WITH(DATA_COMPRESSION=PAGE);

insert into dbo.test_comp(id,  LoadTime)
select n, getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 0   11,7117117117117    18519

UPDATE dbo.test_comp SET LoadEndTime = LoadTime;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 54949   11,1783696529459    19688

CREATE TABLE dbo.test_no_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
)

insert into dbo.test_no_comp(id,  LoadTime)
select n, getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_no_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_no_comp  0   6,22905027932961    28572

UPDATE dbo.test_no_comp SET LoadEndTime = LoadTime;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_no_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_no_comp  0   6,22905027932961    28572

UPDATE

  Even with compression the fixed and variable representation of
  datetime '1900-01-01 00:00:00' should be 8 bytes

This is completely untrue, because you use PAGE compression that creates a dictionary and substitute repeating values:

  Dictionary compression

  After prefix compression is applied to every
  column individually, the second phase of page compression looks at
  all values on the page to find duplicates in any column of any row,
  even if they have  been encoded to reflect prefix usage. The
  process of  detecting duplicate values is data type–agnostic, so
  values in completely different columns could  be the same in their
  binary representation. For example, a 1-byte character is represented
  in hex as  0x54, and it would be seen as a duplicate of the 1-byte
  integer 84, which is also represented in hex  as 0x54. The dictionary
  is stored as a set of symbols, each of which corresponds to a
  duplicated value  on the data page. After the symbols and data values
  are determined, each occurrence of one of the  duplicated values is
  replaced by the symbol. SQL Server recognizes that the value actually
  stored in  the column is a symbol and not a data value by examining
  the encoding in the CD array. Values which  have been replaced by
  symbols have a CD array value of 0xc.

But I wanted point out another thing.
Did you test if your PAGE compression make sense at all in your case?
You have a heap and it's not static, i.e. you update it.

  Compression of heap pages

  Pages in a heap are checked for possible compression only during
  rebuild and shrink operations. Also, if you drop a clustered index on
  a table so that it becomes a heap, SQL Server runs compression
  analysis on any full pages. To make sure that the RowID values stay
  the same, heaps aren’t recompressed during typical data modification
  operations. Although the Page-ModCount value is maintained, SQL Server
  never tries to recompress a page based on the PageMod-Count value

So in your case even if you can achieve compression on INSERT (only if you use bulk insert with tablock), any update will only destroy your compression.

Look at my new test.

I realized that in my first test when i did INSERT without tablock, only row compression was applied, I inserted 1000000 rows and I get 19688 pages.
Now I insert with tablock into the same table:

CREATE TABLE dbo.test_comp (
 id int,
 LoadTime DATETIME NOT NULL,
 LoadEndTime DATETIME not NULL,
filler char(200) default 'qwertyuiopasdfghjklzxcvbnmnbvcxzlkjhgfdsapoiuytrewyyyyyyyykshdgfgsklghkfdjglfdvlaepeoèrehjblgjbltdjgljreglrelgretgregrtegregreqw'
)
WITH(DATA_COMPRESSION=PAGE);

insert into dbo.test_comp with(tablock) (id,  LoadTime, LoadEndTime)
select n, getdate(), getdate()
from dbo.nums;

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 0   0   1387

Now I have only 1387 pages.
1387 vs 19688 only because in first case page compression, even if defined, was not applied since my INSERT was without tablock

And now I update my nice compressed table:

UPDATE dbo.test_comp SET LoadEndTime = getdate();

SELECT OBJECT_NAME(object_id) AS table_name, forwarded_record_count, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.test_comp'), DEFAULT, DEFAULT, 'DETAILED');

--table_name    forwarded_record_count  avg_fragmentation_in_percent    page_count
--test_comp 990247  8,43971631205674    22442

Wow, after update I have 22422 pages vs 1387 original pages!

After this test I really NEVER will use page compression on a heap that is not static. The final table size is not much less compared with its non-compressed analog but as a bonus I have 990247 forwarded records out of 1000000


Cannot connect to mysql server error for master replication

Using this mysql -h XXX -u -p you cannot connect to mysql remotely using root, if you want to connect with root remotely you have to configure it. For replication you need to create replication user on master and give it replication priviliges.

example;

mysql> CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';

mysql> GRANT REPLICATION SLAVE ON . TO 'repl'@'%.example.com';

start your slave and check what is the error on IO thread also check slave log for any errors.


How can I filter an extended event by database in SQL Server 2008 R2

Two things that are the problem.

There is no database_name action in SQL Server 2008 R2 (it was introduced in SQL Server 2012):

select
    action_name =
        p.name + '.' + o.name
from sys.dm_xe_objects o
inner join sys.dm_xe_packages p
on o.package_guid = p.guid
where o.object_type = 'action'
and o.name like '%database%';

The output from that is:

action_name
-----------
sqlserver.database_id
sqlserver.database_context

But there is sqlserver.database_id.  The second problem is that your attempt with database_id didn't work because you have a typo (you have "sqlsever", missing an "r").


How to model data which requires index on all columns?

You can model your table as a dictionary with two columns:

UID
ID_VALUE

Both would be indexed with dedicated index.

You can then add as many ID_VALUE rows for any UID and search for an UID given ID_VALUE.

Queries would then look like

SELECT UID FROM table WHERE ID_VALUE=x;
SELECT ID_VALUE FROM table WHERE UID=xyz;

Second query would return more than one row.

EDIT

  I need to lookup all the id's given any single id

As long as you have all IDs bound by a common column, in this case UID, this should be possible. Consider following query:

SELECT t2.id_value
  FROM table t1
      ,table t2
 WHERE t1.uid = t2.uid
   AND t1.id_value = x

This will return all id's given a single id X.

It really doesn't matter what are the values of ID_VALUE. They may be other UIDs pointing to this one or any other arbitrary number. If you want to search of any ID_VALUE including the UID value, you can include this UID value into the set too so you will have one row where UID = ID_VALUE.

EXAMPLE

Let's say you have following data in your model:

UID   ID1   ID2  ID3
--------------------
  1     2     3    4
  2     1     4
  3     4
  4     2     3

You can model this data in a dictionary table like so:

UID   ID_VALUE
--------------
  1          2
  1          3
  1          4
  2          1
  2          4
  3          4
  4          2
  4          3

If you want to include the UID in the list so the last query in my example would return ALL of IDs including the UID, you would then have:

UID   ID_VALUE
--------------
  1          2
  1          3
  1          4
  1          1 <---
  2          1
  2          4
  2          2 <---
  3          4
  3          3 <---
  4          2
  4          3
  4          4 <---

I've marked the rows added to satisfy this requirement.
Executing the query:

SELECT t2.uid
      ,t2.id_value
  FROM table t1
      ,table t2
 WHERE t1.uid = t2.uid
   AND t1.id_value = 1

Will result in:

UID   ID_VALUE
--------------
  1          2
  1          3
  1          4
  1          1
  2          1
  2          4
  2          2

This will return all IDs given any single id as per requirement. Note that ID_VALUE of 1 can be found in both sets with UID = 1 and UID = 2, both are returned.

This would be equivalent to executing this query on your flat design:

SELECT * FROM table WHERE UID=1 OR ID1=1 OR ID2=1 OR ID3=1


Could you monitor all changes made within a postgresql database by somehow subscribing to the replication log?

As I understand it, your options depend a bit on a key requirement: Do you need the changes recorded in exact transactional order or not?

If you need them in exact order, then a change table or replication decoding are the two workable options I've heard of. Postgres does not implement incrementally updated materialized views, so that's out.

If you do not need the changes in precise commit order, then you can use an alternative strategy. For tables of interest, include a column that takes an ever-incrementing number. This is first assigned on INSERT (easy) and then you have to manage updates on UPDATE (harder.) From there, you have a number line that you can select from. If you track the last number you processed, you can then grab everything > than that, and how you have your unprocessed rows. The primary advantage of such a solution is that you can use that same number line to track different rollups, etc.

Logical decoding is probably going to be simplest.


Insert dependent records in multiple tables, MSSERVER 2012

But it will only inserts the last inserted ID from the Query before.

This is current as documented in https://docs.microsoft.com/en-us/sql/t-sql/functions/ident-current-transact-sql:

  Returns the last identity value generated for a specified table or view.
  The last identity value generated can be for any session and any scope.

You can get all the identities generated in an insert using the OUTPUT clause as documented at https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

  The Source file is a simple txt file i imported with the SQL-Server Assistant, so i could easily add keys or indexes, or use custom imports.

Assuming that you don't need to deduplicate anything at this point (i.e. you won't have two companies with the same address), and you do not need to keep ID values unique between the tables, you could:

Add an ID column to the source table of the right type (INT, UNIQUEIDENTIFIER, ...) which is populated by default (i.e. IDENTITY(1,1) or DEFAULT NEWID())
Use that for the IDs of all the inserts coming from it (if you are using IDENTITY columns you will need to temporarily set IDENTITY_INSERT off for them as this is done, see https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql

No need for OUTPUT or extra lookups.

  I can change the Database if necassary or redesign if you think im doing something stupid ;)

You'll need to more thoroughly describe what you are trying to model before we can help at all with that. It does seem strange to me that a company record would have a reference to contact details rather than the other way around: if is more likely that a company would have several contact methods than that one contact method could be relevant to more then one company.


Azure Failover Clustering vs On Premise Failover Clustering

Azure has two storage for 2 Vms; Traditional On-Premise contains 1 Shared storage.

Did you read The link you shared in the question, BTW I cannot see the diagram you posted in the question when I referred the link. I will quote from the same link.

  This article explains how to create a SQL Server Failover Cluster Instance (FCI) on Azure virtual machines in Resource Manager model. This solution uses Windows Server 2016 Datacenter edition Storage Spaces Direct (S2D) as a software-based virtual SAN that synchronizes the storage (data disks) between the nodes (Azure VMs) in a Windows Cluster. S2D is new in Windows Server 2016.

So the concept here is the two machines are using 2 different disk which is made as one disk and presented to FCI as single storage via Storage Direct Spaces technology.. Azure does not have concept of shared storage so you need some tool to present disks as shared storage to FCI. What it does is

S2D synchronizes the data on the data disk and presents the synchronized storage as a storage pool.
The storage pool presents a cluster shared volume (CSV) to the failover cluster.
The SQL Server FCI cluster role uses the CSV for the data drives.
An Azure load balancer to hold the IP address for the SQL Server FCI.
An Azure availability set holds all the resources.


I figured it out and got it confirmed by the lecture, hopefully this will help someone else:

  i)

  The key is {A,B,E}.

  This means that FD1 and FD3 are both partial — but note that FD2 is
  not, because {D,E} is not a partial key. The decomposition creates one
  table for each of the FDs, and one for the PK FD:

  R1(A,B,E) R2(A,B,C) R3(B,D)

-

  ii) Using the above we can create the following tables:

        `R1                R2              R3`
| A  | B  | E  |   | A  | B  | C  |    | B  | D  |
| a1 | b1 | e1 |   | a1 | b1 | c1 |    | b1 | d1 |
| a2 | b1 | e2 |   | a2 | b1 | c2 |    | b2 | d3 |
| a2 | b2 | e2 |   | a2 | b2 | c1 |

  Joining R1 and R2 gives:

        R1R2                  R3
| A  | B  | E  | C  |    | B  | D  |
| a1 | b1 | e1 | c1 |    | b1 | d1 |
| a2 | b1 | e2 | c2 |    | b2 | d3 |
| a2 | b2 | e2 | c1 |

  Joining R1R2 with R3:

| A  | B  | E  | C  | D  |
| a1 | b1 | e1 | c1 | d1 |
| a2 | b1 | e2 | c2 | d1 |
| a2 | b2 | e2 | c1 | d3 |

  which is equal to:

| A  | B  | C  | D  | E  |
| a1 | b1 | c1 | d1 | e1 |
| a2 | b1 | c2 | d1 | e2 |
| a2 | b2 | c1 | d3 | e2 |

  Hence decomposition is lossles with respect to joins.


Grant permissions to only specific tables in a Schema

In the standard SQL there is no way to explicitly exclude some privileges.
Some RDBMS have the DENY command (MS SQL Server as an example) to do that.


Is it possible to convert mysql binlog from statement format to row format?

You cannot convert a binlog to another version, but you import its SQL as long as have mysqlbinlog for version 4.1.

Simply do the following:

Run MySQL 4.1 version of mysqlbinlog against the MySQL 4.1 binlog, saving output to stmts.sql
Install MySQL 5.x with these in my.cnf

log-bin=mysql-bin
binlog-format=ROW

Login to MySQL 5.1 and run source stmts.sql
Run SHOW BINARY LOGS;

When done, the binlogs you see should be row-based.

Give it a Try !!!

Why can't you just convert ? The BINLOG Magic Number gets displaced with later versions.

I wrote about before

Nov 30, 2012 : How can you monitor if MySQL binlog files get corrupted?
Feb 04, 2011 : MySQL master binlog corruption

Every time you restart mysql, the next empty binlog is created. Each version of MySQL has a different size:

MySQL 5.6 : 120
MySQL 5.5 : 107
MySQL 5.1 : 106
MySQL 5.0 : 98

In light of this, generating binlogs from scratch is the best way to go.

UPDATE : Thanks to Michael's Comment, you have to preload the data the binlog came from into the MySQL 5.1 Instance.


According to your current explanation indexes are not going to help much (if at all) with your current query.

  So there are 3.25M rows with a project key.

That's also the total number of rows, so this predicate is true for (almost) every row ... and not selective at all. But there is no useful index for the jsonb column "references". Including it in the btree index on ("createdAt", "references", type) is just pointless.

Even if you had a generally more useful GIN index on "reference" like:

CREATE INDEX stats_references_gix ON stats USING gin ("references");

... Postgres would still have no useful statistics about individual keys inside the jsonb column.

  There are only 5 distinct values for the type

Your query selects all of one type and an unknown fraction of another type. That's an estimated 20 - 40 % of all rows. A sequential scan is most certainly going to be fastest plan. Indexes start to make sense for around 5 % of all rows or less.

To test, you can force a possible index by setting for debugging purposes in your session:

SET enable_seqscan = off;

Reset with:

RESET enable_seqscan;

You'll see slower queries ...

You group by project values:

  GROUP  BY "references"->> 'project'

And:

  There are exactly 400,000 distinct values for the project reference.

That's 8 rows per project on average. Depending on value frequencies we still have to retrieve an estimated 3 - 20 % of all rows if we only picked min and max per project in a LATERAL subquery ...

Try this index, it makes more sense than what you have now:

CREATE INDEX stats_special_idx ON stats (type, ("references" ->> 'project'), "createdAt")
WHERE "references" ? 'project';

Postgres might still fall back to a sequential scan ...

More might be done with a normalized schema / more selective criteria / a smarter query that only picks min and max "createdAt" ...

Query

I would write your query like this:

SELECT EXTRACT(EPOCH FROM (MAX("createdAt") - MIN("createdAt")))
FROM   stats
WHERE  "references" ? 'project'
AND   (type = 'event1' OR
       type = 'event2'
   AND "createdAt" >= '2015-11-02 00:00:00+08:00'  -- I guess you want this
   AND "createdAt" <  '2015-12-04 00:00:00+08:00'
      )
GROUP  BY "references"->> 'project';  -- don't cast

Notes

Don't cast here:

stats."references"::jsonb ? 'project'

The column is jsonb already, you gain nothing. If the predicate was selective, index usage might be prohibited by the cast.
Your predicates on "createdAt" are probably incorrect at lower and upper bound. To include whole days consider my suggested alternative.
references is a reserved word, so you have to always double-quote it. Don't use it as identifier. Similar for double-quoted CaMeL-case names like "createdAt" either. Allowed, but error prone, needless complication.
type

  type character varying(255) NOT NULL,

  The type is a simple string no longer than 50 characters.

  There are only 5 distinct values for the type field, this would probably be no more than a few hundred in production.

None of this seems to make sense.

varchar(255) in itself hardly ever makes any sense. 255 characters is an arbitrary limit without significance in Postgres.
If it's no longer than 50 chars, then the limit of 255 makes even less sense.
In a properly normalized design you would have a small integer column type_id (referencing a small type table) which occupies only 4 bytes per row and makes indexes smaller and faster.

Ideally, you would have a project table, listing all projects and another small integer FK column project_id in stats. Would make any such query faster. And for selective criteria, much faster queries would be possible - even without the suggested normalization. Along these lines:
Optimize GROUP BY query to retrieve latest record per user


How to check SQL database files for physical fragmentation?

To answer your questions in order:

You can check for the physical fragmentation of the disk by going to the drive, right clicking on it and selecting tools, then defragment.  Or close to that, depending on which version of the OS you are using your specific steps may be different.  But you will only be able to see the fragmentation on a volume level, not specific files.
For the most part, no, you don't need to worry about physical fragmentation of your files if you are using a SAN (unless the SAN is dedicated to this one purpose).  Due to how most SANs are used, your IO is going to be random; which is fine since you should have LOTS of spindles to spread the load out.
Larger physical database files would prevent fragmentation, but again, you shouldn't worry about that if you are on a SAN.  The more important thing to make sure of is that when the file grows, it grows at a set size (rather than percentage) and that size is sufficient to handle a good amount of growth.  You would want to grow the file to what you estimate the required size will be in X days and THEN defragment the volume.

This link - Brent Ozar goes into more detail and should include some more information you can use to either prove or disprove that the SAN is the reason for your bottleneck.


Solution:
the two different machanisms need to be added in the configuration. Like:

authenticationMechanisms=PLAIN,MONGODB-CR

After that, it's necessary to create the users which should be authenticated via LDAP as followed:

db.getSiblingDB("$external").createUser({ user : "syranno", roles: [ { role: "readWrite", db: "testdb" } ] } )

By adding the user to $external, MongoDb knows to authenticate this user against LDAP.


Which system databases should I checkdb and defrag indices?

All system and user databases should be check using CHECKDB. The only exception will be tempdb as you cannot generate a snapshot of tempdb and checkdb behind the hood generates a DB Snapshot.

The tricky part is for tempdb (When Tempdb is corrupted) as we ALL know that it gets recreated when a SQL Server restart is done, but Tempdb files are not deleted when you restart SQL Server and are also not 0 (zero) initialized so the corruption can persist.

Tempdb corruption (which is rare .. and most likely will be due to a bug in SQL Server) is described here.


This can be solved with the dotall-modifier (?s):

 select regexp_replace(entry, '(?s)LINE2.*LINE4\r\n', '') from test

More modifiers can be found here: https://mariadb.com/kb/en/pcre/#option-setting


Creating a listener creates (permissions must allow, otherwise the entry will be made by the network team) a DNS entry, and has a dependency to an IP address. It logically follows that each listener needs its own IP address, because how else will traffic know where to be directed?

See Failover Cluster Manager>[Cluster Name]>Services and applications> [Availability Group name]. Under Server Name section, the listener name is displayed and shows the IP Address underneath, which is reflected as a Dependencies entry if you open the properties of the listener name.

If you find a way around it, I'd sure like to know, because it doesn't make sense to me that it would work. Good luck!


ON CONFLICT ON CONSTRAINT fail postgresql

This:

EMAIL       VARCHAR(50)     NOT NULL UNIQUE,
USERNAME    VARCHAR(30)     NOT NULL UNIQUE,

makes both EMAIL and USERNAME individually unique, in addition to your other constraint. If only a combination of EMAIL and USERNAME is to be unique, remove UNIQUE from the column definitions.


Server has antivirus. Do I need to enable external locking on MySQL?

No, external locking is (was) only needed for MyISAM when you tried to execute several processes on the same data directory, such as two server instances, or an instance and a file repair.

Sincerely, I would be surprised if that page manual was accurate and was still fully supported. In any case, it is mostly impossible to do that now, as InnoDB (default engine) disallows 2 instances on the same server at the same time.

In summary, an antivirus scan is not a problem for MySQL, unless it changes the files or moves them away; and if the resources it uses do not interfere with the database execution. I wouldn't run, however, an antivirus software on a production server, unless it was for scanning the network (emails or desktop clients).


Searching for a Data Dictionary for SQL or Oracle

I've Community Wiki'd this

Oracle

Storage can hold multiple databases
Databases can be opened by multiple instances
Server can run run multiple instances
A server can only run a single instace for any given database
Data is stored in tablespaces
A tablespace may be made up of multiple data files
Tables/indexes/Materialized Views can be partitioned across multiple segments
A segment lives on a tablespace
A database object (table, index, stored program unit...) is owned by a user
A table may be be referenced (through a foreign key constraint) by another table, including a table owned by another user
A table may have at most one primary key constraint
A table may have zero or more unique constraints
A primary key or unique constraint is logically enforced by exactly one index (though you could have a multi-column unique constraint which is backed up by multiple unique indexes with the columns in a differing order)
An index may be unique or non-unique. A unique constraint may be enforced by a unique or non-unique index.
A table must have at least one column
Each column has a datatype (and optionally a length or precision/scale)
A trigger may relate to at most a single table. (Some triggers, such as SERVERERROR, don't relate to a table).