How to schedule a weekly backup of a database only if it has been changed since last backup

Welcome to MS SQL :-)

To start with your question:

Yes there is. First you make a full database backup:

BACKUP DATABASE yourdatabasename
TO DISK = "pathtobackupdir\backupfilename.bak"

Now from that moment on a so called differential bitmap is keeping track of all pages changed.

From that moment every time you create a DIFFERENTIAL backup, only the pages that changed since your last FULL backup will be backupped.

 BACKUP DATABASE yourdatabasename
    TO DISK = "pathtobackupdir\backupfilename.bak" WITH DIFFERENTIAL

So every sunday, asuming you had changes every week, the diff backup will be bigger then the previous one. Untill one sunday, it's just as big as a full backup would be. That would imply that all data pages had changed. At that moment you want to do a new full backup. Since that will reset the differential bitmap and your next diff backup will be small again. (depending on the amount of changes)

You could automate the process of making diff backups untill a certain percentage of the data has changed. Have a look at this blog post: http://www.sqlskills.com/BLOGS/PAUL/post/New-script-How-much-of-the-database-has-changed-since-the-last-full-backup.aspx
It explains a way on how to programatically look up the percentage of database changes. That way you could say.. make diff backups if percentage changed < 75% otherwise make a full backup.

Now could you tell me why you want this? Because this isn't a setup I would advise a starter (with exceptions). So please elaborate a bit and we can see if this is the best solution for your setup. How big is your database?  how much changes do you have? is batch based changes? Which version and Edition of SQL do you have do you have?


Show data and disk use breakdown by table

In SSMS, right-click on the database and go to "Reports", "Standard Reports", "Disk Usage by Table". It will tell you the total size, the data size, the index size, and the unused size for each table (as well as the row count).


Unexpected results in query with rownum condition

The pseudocolumn rownum is assigned AFTER all the result rows from the query are retrieved, and so the correct way using rownum to get say 10 rows, is to use <= as pointed out in the comments.  The rownum can never be greater than the number of rows returned.  Example:

SYS@ecs03> select table_name from dba_tables where rownum <= 10;

TABLE_NAME
--------------------------------
CCOL$
FILE$
CLU$
SEG$
ICOL$
OBJ$
TAB$
CON$
UNDO$
TS$

10 rows selected.

SYS@ecs03>

And better yet as Kevin pointed out, use FETCH FIRST *n* ROWS clause, as you can add additional parameters to get deterministic results.

  For consistent results, specify the order_by_clause to ensure a deterministic sort order.

That old code may have worked years ago, but not in the last several years. I got bit on an upgrade from Oracle 9i to 10c when we assumed that group by implied ORDER BY by the same columns in the group by clause.  Wrong!


Master updates Slave but not Master itself

The reason for this is pretty straightforward:

  if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes the whole transaction to the binary log and then commits this transaction into InnoDB

http://dev.mysql.com/doc/refman/5.5/en/binary-log.html

In periods of moderate to low traffic, it's easy for the "last thing that happened" to make it into the binary log and get handed over to the slave, where it's executed, but not persist on the master.  If you're not using sync_binlog = 1 to sync the binary log to disk after every write and innodb_flush_log_at_trx_commit = 1 to flush every InnoDB commit immediately to disk, then you don't have an ACID-compliant installation ... and if the machine itself crashes, then MySQL has no means of realizing that InnoDB and the binary log aren't consistent on crash recovery.

Update: the original question suggests that you reviewed the master's binlogs and found the data that made it to the slave to be missing from there, too.  To clarify, there are two possible explanations, if this is the case:

the event was "written" to the master binary log and sent to the slave, but "writing" to a file and the operating system flushing that file to the hard drive are not the same thing; a file that has been written to does not have to be flushed in order for the unflushed data to be read, since the "written" data exists in the OS cache even if it hasn't been persisted to disk.
there is some language at the link above that suggests that even if the binlog did get persisted to disk, crash recovery could have trimmed those transactions when InnoDB discovered they hadn't been committed:

  after a crash, after doing a rollback of transactions, the MySQL server cuts rolled back InnoDB transactions from the binary log

The justification for this action is stated as:

  This ensures that the binary log reflects the exact data of InnoDB tables, and so, that the slave remains in synchrony with the master (not receiving a statement which has been rolled back).

This statement is not fully true, since there's a small window of time that the event could have gotten sent to the slave.  On the other hand, if this trimming does happen, the binlog would agree with the data that got persisted on the master, which is arguably better than it not agreeing with the data that got persisted to the master.

I'm not wholly convinced that the example, below, from the source code, represents the process described in the documentation, but if you have a message in your error log that looks like this, then that explanation seems to address your question.

sql/binlog.cc:
  sql_print_information("Crashed binlog file %s size is %llu, "
                        "but recovered up to %llu. Binlog trimmed to %llu bytes.",
                        log_name, binlog_size, valid_pos, valid_pos);


Determine if columns in postgres table are inherited

This query uses pg_inherits, which tracks table inheritance:

SELECT a.attname,
       EXISTS (
          SELECT 1
          FROM pg_inherits AS i
             JOIN pg_attribute AS a2
                ON i.inhparent = a2.attrelid
             WHERE i.inhrelid = a.attrelid
               AND a.attname = a2.attname
       ) AS inherited
FROM pg_attribute AS a
WHERE a.attrelid = 'tablename'::regclass
ORDER BY a.attnum;


Replication slows down on massive deletes

First there's no such thing as one way peer to peer (that I know of).  Are you sure you aren't using transactional replication?

Are the deletes done via stored procedures?  If so you can replicate the stored procedures as well as the tables (all in the same publication, this will require a reinit of the subscribers).  This way the stored procedure call will be replicated instead of the actual row by row delete.

With only 700k rows in the table, even if you blew them all away and reloaded them the replication shouldn't get that far behind unless you have a really slow link between the servers (which you didn't specify).


it seems that your log shipping is working fine. Just remove additional log backups from maintenance plans since they can break log shipping.


Best way to get SQL Server databases on small capacity VPS

SQL Server Express Edition is limited to 10GB maximum database size (per database) and 1.4GB of buffer pool memory per instance, so you won't "kill" the server by running it if there's nothing else (or very little) running on it. However, if you're doing a lot of work with the database(s), you'll find yourself starved for memory and reading a lot of data from disk instead of caching in memory.

Bandwidth? That depends greatly on how much traffic you'll have going into & out of the server.

  I wasn't able to find anyone who cost effectively would rent me just some space on a SQL Server database.

Azure SQL Database is very similar to what you describe here. "Cost effective" is in the eye of the beholder.


Random and constant "Log Growths" Alerting, how to make it stop?

This is telling you that a or several transaction logs are growing repeatedly, which means it is set to FULL or BULK LOGGED recovery mode with no or insufficient transaction log backups, or someone has written a really bad or long query that is keeping the transaction log active.

You should look for all FULL recovery model databases on that server and when was the last time a transaction log backup took place first, as that's the most dangerous.  The tlog could grow to where it takes up all the disk stopping all writes to that DB or any other DB with data or logs on that disk assuming there isn't a max size set.  If there isn't, moving fwd, you'll probably want to set a max size so 1 DB doesn't take down all DBs tlogs.

SELECT name, recovery_model_desc
FROM sys.databases

See any that are full?  If so, when was the last transaction log backup? Here is a script to help you on that:

SELECT   d.name,
         d.recovery_model_desc,
         MAX(b.backup_finish_date) AS backup_finish_date
FROM     master.sys.databases d
         LEFT OUTER JOIN msdb..backupset b
         ON       b.database_name = d.name
         AND      b.type          = 'L'
GROUP BY d.name, d.recovery_model_desc
ORDER BY backup_finish_date DES

If that doesn't give you the info you need here is a way to find the longest running top 10 transactions from msdn:

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO

Finally once you see which DB has growing log files you can see what is holding the log file open.  It might even be something like replication setup or other HA solutions that weren't fully removed.  This will tell you:

SELECT [log_reuse_wait_desc]
    FROM [master].[sys].[databases]
--  WHERE [name] = N'DBNAME';
GO


Do certain string patterns affect query performance on a VARCHAR column?

Several points:

1) Store the data in two columns.

2) Normalize 'type' to a separate table, so you'll only put an INT into your index rather than a varchar(255).

3) Switch to UNSIGNED INT for the ids, and maybe TINYINT for the types.

4) Tell the app people to learn about multiple WHERE clauses for their SQL and give them a good index.

3) Once you do those, your question might become, "should my composite index be (id,type) or (type,id)?"


MySQL Trigger On UPDATE "Unknown Column" Error

Get rid of UNIX_TIMESTAMP() everywhere.  DATETIME fields can be compared directly.

Once you have done that, you will probably notice the missing @ that is causing the error.

I think it can be simplified down to only:

CREATE TRIGGER ...
IF COALESCE(OLD.updateDate, OLD.insertDate) < CURRENT_DATE() - interval 3 month
    INSERT INTO three_month_ago_dad_records SELECT OLD.*;
END IF;
END;


SQL Server prevent SA from setting encryption off

Is there a way to prevent any user (including SA) from setting database encryption off …?

No.  If the database is ONLINE then a sysadmin (including SA) can remove TDE.  Sysadmins are the owners of the SQL Instance, and all the databases.


Call function where argument is a (sub)select statement

1. Subquery expression
You can fix it with parentheses like @a_horse commented:
SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1));
But this form is rather error-prone. Nothing in the code guarantees that the sub-select only returns a single row. We don't know whether id is unique and neither does Postgres (unless it looks up system catalogs). We have to rely on it and hope it won't break.
We could add LIMIT 1 to be sure, but then we probably should also add ORDER BY to get deterministic results ...
2. JOIN LATERAL
The modern syntax for this (Postgres 9.3+) would be a LATERAL join:
SELECT f.*
FROM   tableX t, test_function(t.customerid) f
WHERE  t.id = 1;

Which is short syntax for:
SELECT f.*
FROM   tableX t
CROSS  JOIN LATERAL test_function(t.customerid) f
WHERE  t.id = 1;

Exactly the same result as above, except for one subtle but possibly important difference:

If tableX has no row for t.id = 1, the function is never called and the query returns nothing (no row). If, on the other hand, a row is found with customerid IS NULL, the function is called with NULL input and we get whatever the function returns for NULL.
That's how it should be.

The form with the subquery expression cannot distinguish between these two cases. "No row" results in the same NULL value as customerid being NULL. The function is called with NULL input either way.
That's not how it should be.

The LATERAL query also does not break if id is not unique. And you can easily return additional columns from tableX if you want.


How to perform same DML statements on two different servers (Oracle and SQL-Server) simultaneously

I do not have enough reputation to write a comment so I am writing here. It may be an unswer to your question.

I have never tried, but it should work. You can add an Oracle database into your SQL Server as a linked server and then write a trigger in the sql server and perform the insert to Oracle via OpenQuery. This should be the easiest way and should work.


ASPState database locking and growth problems

I suspect you implemented Greg's replacement procedure as pre-emptive optimization. The one-row-at-a-time approach restricts locking to a single row, sure, but it's going to take a lot longer - especially if you force SQL Server to attack rows in a random order.

My suggestion would be to revert to the original procedure:

ALTER PROCEDURE dbo.DeleteExpiredSessions
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @now DATETIME;
  SET @now = GETUTCDATE();

  DELETE ASPState..ASPStateTempSessions
    WHERE Expires < @now;
END
GO

If you find that this becomes a performance issue because of the locks taken, you can split this out and even reduce impact on the logs using something like:

ALTER PROCEDURE dbo.DeleteExpiredSessions
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @now DATETIME, @c INT;
  SELECT @now = GETUTCDATE(), @c = 1;

  BEGIN TRANSACTION;

  WHILE @c <> 0
  BEGIN
    ;WITH x AS
    (
      SELECT TOP (1000) SessionId
        FROM dbo.ASPStateTempSessions
        WHERE Expires < @now
        ORDER BY SessionId
    )
    DELETE x;

    SET @c = @@ROWCOUNT;

    IF @@TRANCOUNT = 1
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
  END

  IF @@TRANCOUNT = 1
  BEGIN
    COMMIT TRANSACTION;
  END
END
GO

This is going to be much better, IMHO, than using a randomized cursor to delete one row at a time. You can tweak the value in TOP (1000) based on actual observation.

A couple of other ideas that might help:

(a) set the ASPState database's recovery model to simple (it defaults to full).

(b) change ASPState_Job_DeleteExpiredSessions to run every 5 or 10 minutes instead of every minute. If this job is taking > 1 minute to run then it is going to always be running. Hopefully it isn't completely linear, meaning that waiting 5 minutes instead of 1 won't queue up more than 5 minutes of work instead of 1. Implementing my suggestions above should help with this.


Get results ordered by a specific column in a looping way

As I've been asked for an example for the SQL Server version, something like:

WITH NumberedWithinCategories AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY NEWID()) AS rownum
  FROM dbo.Words
)
SELECT TOP (80) *
FROM NumberedWithinCategories AS w
JOIN dbo.Categories AS c
  ON c.ID = w.CategoryID
ORDER BY w.rownum, NEWID();

Of course you'd filter the outer query to the columns you want once you've checked the logic...


Since it is a longstanding production SQL Server I can't easily suggest to upgrade versions

The anti semi join cardinality estimation bug is reproducible on all versions of SQL Server from 2005 to 2012 inclusive. All require trace flag 4199 to enable the fix, so upgrading would not solve your problem without activating 4199 (though there are many other good reasons to upgrade from 2005, of course).

...as such I cannot force the traceflag 4199 hint on this specific query.

If it is just one particular query that is affected, you can use OPTION (QUERYTRACEON 4199) to enable the trace flag for just that query. This query hint is documented and supported for use with 4199, and applies from SQL Server 2005 Service Pack 2 onward.
This hint effectively runs DBCC TRACEON (4199) and DBCC TRACEOFF (4199) around the query, and requires sysadmin permission as a result. If that is a problem, add the hint using a plan guide.
You should also look at testing your whole system with 4199 enabled instance-wide. Plan regressions are possible, but overall you may find that the various optimizer fixes enabled by this flag are well worth it. All future plan-affecting query processor fixes require this flag to activate.
All that said...
As mentioned in ypercube's answer, the bug requires two or more join columns to manifest (among many details). The redundancy in your NOT IN clause causes the optimizer to see two column comparisons (though logically there is only one), thereby exposing the bug.
Removing this redundancy will 'solve' the problem for this particular query, though other queries that really do have more than one join predicate will still be vulnerable.
Example
To illustrate, here is an example based on the CSS blog post linked in the question (but with a complete script!):
CREATE TABLE dbo.tst_TAB1
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL
);

CREATE TABLE dbo.tst_TAB2
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 integer NOT NULL
);

CREATE INDEX i ON dbo.tst_TAB1 (c1, c2);
CREATE INDEX i ON dbo.tst_TAB2 (c1, c2);

Sample data:
INSERT dbo.tst_TAB1
    (c1, c2, c3)
SELECT
    number, number, number
FROM master.dbo.spt_values
WHERE
    [type] = N'P'
    AND number BETWEEN 1 AND 2047;

INSERT dbo.tst_TAB2 (c1, c2, c3)
VALUES (1, 1, 1);

Test query using NOT IN with redundant predicate:
SELECT
    T1.c1
FROM tst_TAB1 AS t1
WHERE
    t1.c1 NOT IN
    (
        SELECT
            t2.c1
        FROM tst_TAB2 AS t2
        -- This is redundant!
        WHERE
            t2.c1 = t1.c1
    );

The estimated execution plan shows an estimate of 1 row after the anti semi join:

Side note: In fact this is an example of another (rare) bug. Writing the WHERE clause as t1.c1 = t2.c1 instead of t2.c1 = t1.c1 allows the optimizer to see that the two join predicates are in fact the same, and the bug does not manifest.
The same query with OPTION (QUERYTRACEON 4199):
SELECT
    T1.c1
FROM tst_TAB1 AS t1
WHERE
    t1.c1 NOT IN
    (
        SELECT
            t2.c1
        FROM tst_TAB2 AS t2
        WHERE
            t2.c1 = t1.c1
    )
OPTION (QUERYTRACEON 4199);

The estimated execution plan now shows an estimate of 2046 rows, which is exactly right:

We can also remove the redundant predicate:
SELECT
    T1.c1
FROM tst_TAB1 AS t1
WHERE
    t1.c1 NOT IN
    (
        SELECT
            t2.c1
        FROM tst_TAB2 AS t2
    );

The execution plan happens to use an additional unrelated optimization (the Stream Aggregate), but the important point is that the post-join estimate is correct without having to enable 4199:

Multiple anti semi join columns
It is possible to express an anti semi join over multiple columns using NOT IN syntax. These cases will require 4199. For example, the next query joins on c1 and c2:
SELECT
    T1.c1
FROM tst_TAB1 AS t1
WHERE
    t1.c1 NOT IN
    (
        SELECT
            t2.c1
        FROM tst_TAB2 AS t2
        WHERE
            t2.c2 = t1.c2
    );

The execution plan shows the erroneous 1-row estimate:

With 4199, the issue is resolved:
SELECT
    T1.c1
FROM tst_TAB1 AS t1
WHERE
    t1.c1 NOT IN
    (
        SELECT
            t2.c1
        FROM tst_TAB2 AS t2
        WHERE
            t2.c2 = t1.c2
    )
OPTION (QUERYTRACEON 4199);

Other syntaxes
Using NOT IN in this way is best avoided, not least for the reasons mentioned in Books Online:

That issue with NOT IN and NULLs has been written about many times. There are many alternative syntaxes available, of which NOT EXISTS is my personal preference. Note that changing syntax will not avoid the cardinality estimation bug:
SELECT
    T1.c1
FROM dbo.tst_TAB1 AS t1
WHERE
    NOT EXISTS
    (
        SELECT 1
        FROM dbo.tst_TAB2 AS t2
        WHERE
            t2.c1 = t1.c1
            AND t2.c2 = t1.c2
    );

That two column anti semi join produces the 1-row estimate, and requires 4199 to fix it. The execution plans are exactly the same as seen previously, so I will not repeat them. The NOT EXISTS syntax does avoid the NULLs issue with NOT IN.
Other observations
I agree with ypercube's other observations.

Sprinkling NOLOCK hints over every table in a query is a bad code smell. If the query can truly tolerate READ UNCOMMITTED transaction semantics, set the isolation level explicitly.

TOP without ORDER BY is another sign of poor code. TOP requires an ORDER BY clause to define what TOP means. Never rely on observed behaviour, use an explicit top-level ORDER BY to obtain a guarantee.

INNER LOOP JOIN and join hints in general, imply a FORCE ORDER query hint. This severely limits the optimizer's freedom, and is usually misunderstood and misapplied. Never use hints that you don't fully understand.


Unique Constraint with multiple null columns

You can create a unique filtered index. Here is a simpler example, since I don't know what your goal is. We have a baseball team and some players have been assigned numbers; some haven't chosen yet.

CREATE TABLE dbo.PlayerUniforms
(
  PlayerID INT PRIMARY KEY,  -- FOREIGN KEY etc.
  UniformNumber TINYINT NULL -- CHECK (COALESCE(UniformNumber,0) < 100)
);

CREATE UNIQUE INDEX x ON dbo.PlayerUniforms(UniformNumber)
  WHERE UniformNumber IS NOT NULL;

Now you can enter multiple NULLs in the UniformNumber column like ANSI meant it to be, and still enforce uniqueness among the non-NULL values.


MySQL select complementary pairs on particular values

Try changing your JOIN statement as follows:

     join main b on a.accession < b.accession AND a.folder= b.folder

This should include a.accession = "Z1" and b.accession = "Z2", but exclude a.accession = "Z2" and b.accession = "Z1".


Bad bind variable in audit trail trigger in Oracle 11g R2

If you write :new.colName than you are referencing the new value of a column named "COLNAME" of table tbl_mst_books. But table tbl_mst_books has no column named "COLNAME". Actually there is no possibility to do this this way because dynamic sql does not work with :new and :old bind values. You have to program it explicitly for every column in your table and have to change your code if your table structure changes.

see AskTom


MongoDB 3.2 and earlier require downtime and a coordinated restart of your deployment and application to enable access control: all clients and members of a deployment must use authentication once enabled. There is a localhost exception that allows you to create the first user on the admin database after authentication is enabled.

With planning this can be a relatively quick process, but to minimize potential downtime I strongly recommend first testing in a representative staging environment.

Enabling access control and authentication is an obvious security measure but still leaves you vulnerable to other possible attacks. For example, you should also Configure TLS/SSL to secure your network communication and restrict remote network access via firewall or perhaps a VPN/VPC between your application servers and your MongoDB deployment.

For a full list of security measures and links to relevant tutorials, see the Security Checklist in the MongoDB manual.

There are several steps that you can test in a staging environment to help ensure the transition goes smoothly, including:

Generate a valid keyfile or x.509 certificate for internal authentication between members of your replica set or sharded cluster.
Write a JavaScript file to create the initial user administrator and any required user accounts/roles.
Ensure that you have properly configured authentication by trying to login without any credentials as well as with valid/invalid credentials.
Test external access to your deployment to confirm your firewall configuration.
Make sure the MongoDB driver you are using has been updated to support MongoDB 3.2 and that your application is able to successfully login after you have enabled authentication and any other security measures.

MongoDB 3.4 --transitionToAuth

Another option worth mentioning for future consideration (although generally not the fastest path to enabling authentication on an existing deployment) would be upgrading to MongoDB 3.4.

MongoDB 3.4 includes a new --transitionToAuth option that enables a rolling upgrade to authentication; see: Enforce Keyfile Access Control in a Replica Set without Downtime.


Test Data

CREATE TABLE dbo.Table1( A INT IDENTITY(1,1) PRIMARY KEY NOT NULL
                     ,B varchar(255),C int,D int,E  int);

INSERT  INTO dbo.Table1 WITH(TABLOCK)
(B,C,D,E)

SELECT TOP(1000000) 'Some Value ' + CAST((ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) % 400) as varchar(255))-- 400 different values
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM master..spt_values spt1
CROSS APPLY master..spt_values spt2;

CREATE INDEX IX_B
On dbo.Table1(B);
CREATE INDEX IX_C
On dbo.Table1(C);

Q1

  When we do "select top 100 * from Table1" - whole clustered index
  (table) is being read from disk to memory, even if we need just 100
  rows ? or just 100 rows (their data pages) are read from disk to
  memory ?

Only 100 rows from the clustered index  are read.
As a result only their data pages are cached into  memory.

Example

In my case it reads downward from the clustered index, 1 - 100 values for the column A.

SET STATISTICS IO, TIME ON;
select top 100 * from dbo.Table1

The reads:

Table 'Table1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

4 logical reads = 4 pages

Why are we still seeing a scan count = 1, but only 4 logical reads you might ask?

Because it is executing a range scan:

to satisfy the top operator.

Q2

  Same with nonclustered index, when we do "select top 100 * from Table1
  where column B = 'some value'", whole nonclustered index + clustered
  index gets loaded into memory ? or just 100 rows from nonclustered
  index + 100 rows from clustered index ?

Example 1

SET STATISTICS IO, TIME ON;
select top 100 * from dbo.Table1
WHERE B='Some Value 200'

You might expect for the nonclustered Index to be used here, but in fact the clustered index is still used:

With after clearing the cache 287 logical reads & 2528 read ahead reads.

Table 'Table1'. Scan count 1, logical reads 287, physical reads 1, read-ahead reads 2528, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

We are caching these read ahead reads after reading them from disk to then read 287 pages from memory.

  The read-ahead mechanism is SQL Server’s feature which brings data
  pages into the buffer cache even before the data is requested by the
  query.
  Source

If we checked the cached pages:

cached_pages_count  objectname  indexname   indexid
2536    Table1  PK__Table1__3BD019AE039DA497    1

We see this also shown.

So in this case we are caching some more pages to satisfy the query faster, since we are reading more rows to apply a residual predicate on them:

But we are only reading these pages from the clustered index.

Example 2

You could change the query in the hope of utilizing the nonclustered index:

select top 100 B from dbo.Table1
WHERE B='Some Value 200';

Which it does:

This again gives a small range scan:

Table 'Table1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Only the nonclustered index pages are loaded into memory here.

Example 3

We might force a key lookup to satisfy query from example 1 by using an index hint:

SET STATISTICS IO, TIME ON;
select top 100 *
from dbo.Table1
WITH(INDEX(IX_B))
WHERE B='Some Value 200';

This again results in some read ahead reads and more logical reads than example1:

Table 'Table1'. Scan count 1, logical reads 684, physical reads 3, read-ahead reads 465, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Since we are doing a key lookup from the nonclustered to the clustered index we will be caching pages from both indexes:


unable to change secure_file_priv in my.cnf

I do not know in Linux but in Windows 10/MySQL80 (my.ini instead of my.cnf) the option is spelled secure-file-priv  and not secure_file_priv. Do not forget to restart the service once the change is made.

I sadly lost one evening before finding my error. This is especially confusing as the SQL query to check the variable value is SELECT @@secure_file_priv.


How to get mysql automatically running when I start tomcat apache on Windows?

You are going to love this.

Just install mysqld as a Windows Service. Ready ?

From the Windows DOS shell (ran as Administrator), go to the folder where mysqld.exe is located

Run this

mysqld --install

That's it. You can go to

ControlPanel
Administrative Tools
Service

When the Services Window opens, scroll alphabetically can you will see MySQL as a service

Restart Windows

When Windows comes back up, mysqld.exe should already be running.

Give it a Try !!!

See my other from ServerFault : (Feb 10, 2011 : Installing isolated instance of MySQL on Windows using silent install with .msi)


Getting the exact count of rows in Postgres database

It seems there is currently no built-in way to do what you require in PostgreSQL.

  People are working toward such capabilities.  While nobody can say
  with any certainty when such features will make it into a
  PostgreSQL release, I think it's safe to predict that it will not
  be before late 2017, and most probably later than that.

Meanwhile, you could manually implement a solution using triggers; for example as described in Postgresql General Bits by A. Elein Mustain.

The idea is to maintain an always-current row count in a separate table using triggers. Be aware that this may add significant overhead to data modifications.


HKHostFreezeCkptTrimming on SQL Server 2017

I don't have an answer for what exactly they mean, but I see them on every machine where I have a memory optimized file group.

Since they start with "HK" they seem to be related to In-Memory OLTP (codenamed Hekaton prior to the production release in SQL Server 2014).

I would say that since they are INFO messages, and they are common on instances with memory optimized filegroups, that they are nothing to worry about.

There isn't currently a documented way of disabling those messages, and I hear that there really is no undocumented way of disabling them either.  There is a precedent for disabling backup-related info messages (see trace flag 3226), so you could make a request on user voice: SQL Server Uservoice


Query/Table Hints for the History Table when Using the FOR SYSTEM_TIME Clause

While not a great idea in general, a workaround that seems to work is using the USE PLAN hint

Base query to get the XML from

-- UNION ALL instead of FOR SYSTEM_TIME clause - Table Hints
SELECT  *
FROM    dbo.People WITH (INDEX(IX_Live_People__Name))
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

UNION ALL

SELECT  *
FROM    hist.People WITH (INDEX (IX_Hist_People__Name))
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

Get the XML

Replace ' with ''

Run the query with USE PLAN and the previously generated execution plan

SELECT  *
FROM    dbo.People  FOR SYSTEM_TIME ALL
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'
OPTION(USE PLAN N'...')

Result

Using a plan guide to force the plan

Change ' to '''' in the query plan from the base query

Create the plan guide

EXEC sp_create_plan_guide
    @name = N'TemplateGuide1',
    @stmt = N'SELECT    *
FROM    dbo.People  FOR SYSTEM_TIME ALL
WHERE   LEFT(VolatileData, 2) = ''2A''
    AND Name = ''John''',
    @type = N'SQL',
    @module_or_batch = NULL,
    @hints = N'OPTION(USE PLAN N''Plan Here'')'

Test the query

SELECT  *
FROM    dbo.People  FOR SYSTEM_TIME ALL
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

Result

The plan guide is used, based on the PlanGuideName fragment in the plan's XML

PlanGuideName="TemplateGuide1"

&

UsePlan="true"

As expected, when we add a couple of spaces the plan guide is no longer used

SELECT  *
FROM      dbo.People  FOR SYSTEM_TIME ALL
WHERE   LEFT(VolatileData, 2) = '2A'
    AND Name = 'John'

Resulting in the query without the index hints

(Tested on build version 14.0.3045.24 / SQL Server 2017 CU12)

Notes

While this is a possible workaround it is only meant to be used if it is A) needed and B) there are no other options.

The use of plan guides is most beneficial when using  paramaterized queries (either by design or force), creating a plan guide for each ad hoc query possibility is not feasible.


Is it possible in SQL Server 2008 to have Structures in one file and Rows Data in another file?

As @Denny pointed out, schemas are always in the PRIMARY filegroup. There are potential availability benefits to having just schema in PRIMARY and data elsewhere, if you can make use of Partial Availability & Piecemeal Restore.

The usefullness of this depends on the nature of your data and if you have tables that are suitable for partitioning. For example, if your biggest tables contained Orders and OrderLines which are partitioned by OrderDate across quarterly filegroups and your remaining tables are relatively small by comparison, in the event of failure you would

Restore the PRIMARY filegroup (instantly, as its tiny).
Retore the filegroup containing the smaller supporting tables.
Restore the filegroup allocated to the current quarter order data (again relatively quickly compared to restoring all history).
Set the remaining partitions as offline.
Bring the database online. You can now accept new orders and process existing orders.
Gradually restore the remaining partitions and bring online in turn.

In a similar vain, if you were to suffer corruption to the database metadata (a rare occurance compared to corruption in data structures but not impossible), you would only need to restore the very small primary filegroup to get up and running.

The availability benefits this can bring to a large database are so significant, I try design partial availability in from the outset on new projects. A 500GB database restoring at 50MB/s will result in at least 3 hours of explaining why it's taking so long to the non-techs. With forethought and planning, it could be a fraction of that to get the business up and running again.


MySQL8 Binary installation - getting error msg "-bash: bin/mysql: Permission denied" after upgrading to 8.0.22

Looks like there is a bug reported for this. Hope this helps someone.
https://bugs.mysql.com/bug.php?id=101969


Outer Joins to find records with no matching associated entries

If you want to stick with left join, here is how it should be. The condition you want to exclude is placed on the left join .. on part and you keep the where .. is null check:

select
    foo.*
from
    foo
  left outer join bar
    on  foo.id = bar.foo_id
    and bar.name in ('name1', 'name2')
where
    bar.name is null ;

It can also be done with not exists:

select
    foo.*
from
    foo
where
    not exists
      ( select *
        from bar
        where foo.id = bar.foo_id
          and bar.name in ('name1', 'name2')
      ) ;


Lost persistance in long term in redis without TTL

Preface: keys in Redis do not get deleted, unless explicitly requested or when the server is configured with an eviction policy (and RAM is pressured).

Regrettably, the most common cause for missing keys is security, or rather the lack of it. Script kids, leet haxors and regular bad guys take advantage of unprotected servers and cause all kinds of havoc. The first thing you need to do is ensure that the server is protected properly (i.e. is configured with a password and is not exposed to the world). For more details refer to https://redis.io/topics/security

After ruling out attacks and eviction policy configuration, the only remaining explanation is a rogue piece of code. From the server's side, you can try tracking this behavior using either the MONITOR command or by configuring keyspace events and subscribing to the relevant messages.


How does one create a data base for a survey/questionnaire?


I suspect the indexes are not chosen by the optimizer because they have the column created_at (which is a timestamp?) but the query is converting it to date.

I suggest you either create an index on (project_id, engine_id, type_id, created_at::date) or just keep the index on (project_id, engine_id, type_id, created_at) and change the condition to:

 AND created_at >= now()::date - 14
 AND created_at < now()::date + 1

You can use current_date instead of now()::date.
I can't think of any situation where the (project_id, engine_id, type_id, created_at ASC, created_at DESC) would be useful. Drop this one.
About the two other indexes, (..., created_at ASC) and (..., created_at DESC), either one would be ok for the query (with the changed condition). I suggest you keep only one of them, unless you have other queries that need both.


PostgreSQL get first letter of each word (except...)

Some options:

-- regexp_matches
SELECT string_agg(arr[1], '') AS string
FROM   regexp_matches('The United States of America', '\y(?!(the|of)\y)\w', 'gi') arr;

-- regexp_split_to_table
SELECT string_agg(left(word, 1), '')
FROM   regexp_split_to_table('The United States of America', '\s+') t(word)
WHERE  NOT (word ILIKE ANY ('{the,of}'::text[]));

-- Without regular expression
SELECT string_agg(left(word, 1), '')
FROM   unnest(string_to_array('The United States of America', ' ')) t(word)
WHERE  NOT (word ILIKE ANY ('{the,of}'::text[]));

SQL Fiddle.

Option 2 splits on any amount of white space. Not exactly the same as your regex.
Option 3 splits on a single space character. If you can rely on that (?), the query is cheaper.


If you are merely looking to increment each element of an input array, this simple function is one method of doing it.

CREATE OR REPLACE FUNCTION add1 (int[]) RETURNS int[] AS
$func$
DECLARE
    e       int;
    new_arr int[] := '{}';
BEGIN
    FOREACH e IN ARRAY $1 LOOP
        e := e + 1;
        new_arr := new_arr || e;
    END LOOP;
    RETURN new_arr;
END;
$func$ LANGUAGE plpgsql STABLE;

SELECT add1(array[1,5,8,15]::int[]);
    add1
------------
 {2,6,9,16}

Or queried from your table

select id, array_col, add1(array_col) from yourtable;
 id |      arr      |     add1
----+---------------+---------------
  1 | {1,3,6,9}     | {2,4,7,10}
  2 | {10,20,30,40} | {11,21,31,41}

Plain SQL version of the function:

CREATE OR REPLACE FUNCTION add1(int[]) RETURNS int[] AS
$func$
    SELECT array_agg(vals)
    FROM (select unnest($1) + 1 as vals) AS x;
$func$ LANGUAGE sql STABLE;


Error while restoring database in SQL Server 2008 R2

If SELECT @@VERSION < 10.50.1746.0 then it is possible that the issue you are coming across has been fixed in a specific cumulative update, and can be averted by updating your instance.

The particular issue is described here:

KB #980832 : FIX: "A system assertion check has failed" error message...

The cumulative update is here:

Cumulative Update #4 for SQL Server 2008 R2 RTM

But I recommend applying the most recent service pack and cumulative update available (since they come with a slew of other fixes as well):

SQL Server 2008 R2 Service Pack 2

Cumulative Update #4 for SQL Server 2008 R2 Service Pack 2


I have developed software with similar functionality, the ability to use the MySQL replication stream (binary log, binlog) to capture events in near-real-time in response to inserts/updates/deletes in the database.

Here are some observations I've made regarding performance.  Luckily, the potential hot spots are largely independent of each other.

I will assume, since I was unfamiliar with the Node package you cited and have only just now given their code a cursory review, that they are not actually "tailing" the binlog via polling, but are actually emulating a slave/replica server and connecting to the master and requesting the replication stream.

The first potential bottleneck is the master's ability to write the amount of Binlog data required (I/O throughput is the primary cobstraint).  If your master is already logging in ROW format, then this problem is already solved. If not, then switch your Binlog format, and see. I prefer ROW format, anyway, because it's very useful for data recovery when queries go bad or the application does something to the data it shouldn't have.  It is possible (using 3rd party tools) to capture what happened and reverse it -- in the default configuration, when a delete occurs (for example) the deleted data is actually written to the binary log.

The next point of resource consumption is the slave connection made by such a tool to the master, where the master pushes the data.  A common misconception is that a slave "polls" the master.  In fact, the slave initiates the connection, but the master pushes the data.  This is actually a load on the master that has very little impact on performance when the number of slaves connected is small (say, 5 or fewer).  This load can be eliminated from the master entirely by connecting the "binlog tailer" not to the master, but to an existing slave of the master, with log_slave_updates configured.

Transport of the data from the master to the pseudo-slave can eat significant network bandwidth, so your external utility should support the MySQL client/server compression protocol to reduce this bandwidth.  Enabling this capability can achieve compression ratios of 10:1 depending on the payload.

The final pain point is the external utility itself.  The MySQL Binlog format is a very tightly packed binary format (hence "binary log") that must be parsed and decoded.  The efficiency with which the external utility can unpack and manipulate this data stream will determine how close to real-time the events detected can be emitted, since inefficient code will cause your decided event stream to lag further and further behind the master, though this factor won't have any performance impact on the master server itself.

In short, if your master can handle the workload of generating row-format binlogs for the volume of traffic you expect, the rest of the potential issues are still potential issues, but they should have no meaningful performance implications on the master server, itself.


Long running statement not monitored by Oracle

The statement in question had multiple plans (13 to be precise) which for some reason were not returned by dbms_xplan.display_cursor(). The plan returned by that function was one with less then 300 lines, but other plans had more then 300 lines.

After the hosting partner increased the "_sqlmon_max_planlines" parameter to 500, the statement is now monitored.


I would add both as Adam points out.
I initially thought that views would only be useful as a base for reports but users end up asking for more detail.  The time to add a new column later is much more effort than adding it to start.


What is the data like?  Why does it need updating every second?  Etc.  (Your question is very vague; some of my Plans may not apply, but I can't tell without understanding the problem set.)

Plan A:

If you are SELECTing identical queries more often than you are changing the underlying table(s), the MySQL's "Query cache" may be an excellent solution.

query_cache_type = ON
query_cache_size = 100M  -- No more (unless using Aurora)

The rest is automagic.

Plan B:

Redis or Memcached make better caching tools than another database (eg, MongoDB).

Plan C:

Let's look at your queries and see if they can be sped up.  Better indexes, rearrange the schema, better queries, etc.

Plan D:

Devise some scheme that "knows" whether the data has changed, and avoid re-performing the query.


Does dm_exec_procedure_stats.total_worker_time include signal waits?

Neal Hambly in his post at SQL Server SOS_SCHEDULER_YIELD Wait Type includes this comment (highlights are mine):

If you have long running queries, especially when in conjunction with
high signal wait times, it may be an indication of CPU pressure.
Reviewing the system performance will show if the CPUs are showing
signs of CPU pressure, and reducing workload through performance
tuning or other steps can help reduce the workloads being performed by
SQL Server.

Certainly adding more cores will change the amount of pressure each CPU must face.  So, it seems quite reasonable that more processors will reduce the pressure on each CPU, which in turn provides better CPU throughput.


Find a specific table and column value across all databases

I would not want to do that with T-SQL because it limits me in the amount of servers I could get the results from.

Instead I would create a PowerShell script like this:

$servers = @( 'server1', 'server2')

$query = "SELECT [Database Version] AS DatabaseVersion FROM [dbo].[AWBuildVersion];"

$results = @()
$comment = @()

# Loop through the servers
foreach ($server in $servers) {
    $databases = Get-DbaDatabase -SqlInstance $server | Where-Object {$_.Name -like 'AdventureWorks*'}
    Write-Host "Going through server $server"

    # Loop through the databases
    foreach ($database in $databases) {
        # Reset variables
        $result = @()
        $data = $null
        $comment = $null

        if ($database.Tables.Name -contains 'AWBuildVersion') {
            Write-Host "- Querying database $database"

            $data = Invoke-DbaSqlQuery -SqlInstance $server -Database $database.Name -Query $query
        }
        else {
            $comment += 'Could not find table AWBuildVersion'
        }

        $result = [PSCustomObject]@{
            Server          = $server
            Database        = $database.Name
            DatabaseVersion = $data.DatabaseVersion
            Comment         = $comment -join ', '
        }

        $results += $result
    }
}

$results

You do need the dbatools powershell module to run it.

What it does is:

Iterates through the servers you assign.
Retrieves the databases that have 'AdventureWorks' in the name.
Iterates through the databases and executes the query.
If it cannot find the table it adds a comment to the results that the table could not be found.


SQL Server 2016 Audit Actions, Can DatabaseAuditSpecification capture param values used in statement?

There is no audit action to capture this information, it is a limitation of SQL Server Auditing. Basically it captures the statements after the query optimiser has already parameterised the query.

There are two open requests that I know of to alter this behaviour (here and here) that you could vote for, however, this limitation has been around for some time and doesn't seem likely to change. Note that this is apparently different in Azure SQL Database, however, I haven't tested this to confirm.

Your options for this are triggers, CDC, SQL Trace or third-party auditing tools.


I am receiving the following error - The definition of object x has changed since it was compiled

Modifying my answer based on the comment by @sepupic. Thank you for pointing out my mistake.

This error is generated if stored procedure definition changes while it is running.


MySQL Count Rows with Two Cells the same

What about something like:

select
  height || 'x' || width as dimensions,
  count(*) as number_of_banners
from
  rv_banners
group by
  height, width ;


Full, incremental or differential backup?

Does this mean, that among these backup pieces there is one incremental backup of a data file or control file?

No, the Backup type is D means it's backup of data files or control file but it's not incremental its full backup(denoted by NULL for INCREMENTAL_LEVEL).

Other backups are differential incremental backup of the backup type D which is base for these incremental backups.

  The difference between this and other databases is that most other databases are backed up incremental level=0. So might there be the difference?

Incremental Level 0 and Incremental Level 1 Cumulative is different types of backup so there is difference.

  Incremental backups can be either level 0 or level 1. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.

For more: RMAN Incremental Backups


Does Ordering BY primary key help query performance when joining table to query

In general, specifying an ORDER BY clause is an extra operation that can result (and generally does) in more work for your queries, even queries that JOIN two tables together with an ORDER BY clause that sorts the data to the order of the JOIN predicates.
The query optimizer is (usually) smart enough to know when to internally sort the data to most efficiently JOIN two tables together, and will do so dependent on the indexes of those tables (because indexing already sorts the data) that it's going to use for the query.
So it's theoretically possible that the query optimizer might choose an index that is sorted differently (and/or results in a different sort operation) than the one you specified in your ORDER BY clause which will result in extra work for the query to be processed and the data to be served. And in the best case, generally, you're not adding any benefit by specifying an unneeded ORDER BY clause even if it's not causing any harm.


query returns the same value multiple times

The query returns the same record multiple times.

Variants:

one of source tables (or even both) contains duplicates;
the records are NOT duplicates, there exists at least one field (in a record pair which seems to be duplicates for you) where values differs.

  the table Keys contains multiple equal values of Bnumber, but that is required because 1 Book has multiple keywords.

It is variant 2.

You make a mistake while thinking that the records are duplicates. They differs - at least in Keyword field taken from Keys table.

You may "compress" the output obtaining one book per recodset by grouping and concatenating all keys into one field with keys list separated by comma:

SELECT Books.id,
       Books.Bnumber,
    /* another fields from Books table */
       GROUP_CONCAT(Keys.Keyword) Keys_list
FROM Books
JOIN Keys ON Books.Bnumber = Keys.Bnumber
/* all WHERE conditions which you need */
GROUP BY Books.id,
         Books.Bnumber,
      /* another fields from Books table */


What are Objective Reasons to Prefer SQL Server 2016 over Earlier Versions?

There are a lot of reasons to consider an upgrade to SQL Server 2016. Most of the time, these reasons vary depending on the version of SQL Server one is coming from. This won't be an exhaustive list, but I'll give a few reasons that come to mind. Including one very big reason we just learned about with SQL Server 2016 SP1's release in November of 2016.  Looking forward to see other answers for expansion or features I missed.
Big Reason: Many Features Available in Standard Edition Now
On November 16, 2016 Microsoft announced the release of Service Pack 1 for SQL Server 2016. Along with this announcement, they also announced that many features previously reserved to Enterprise Edition customers would be available in all editions of SQL Server - Standard, Express (with a few exceptions), Web in addition to Enterprise Edition.
This should serve to be a very compelling reason to upgrade for Software Vendors who develop and sell software which have a SQL Server backend – many features can now be available to their customers regardless of their licensed edition of SQL Server. This is also compelling for companies in sectors which must maintain auditing or other security enhancements that have been added to the product over time.
In my humble opinion, in over 17 years of working with SQL Server this makes SQL Server 2016 SP1 quite possibly the most compelling upgrade yet since I've started working with SQL Server in version 6.5. (Don't get me wrong, the 2000, 2005 and 2012 had their pros for their times also!)
Look at the list below, but just as a quick example of some of the features you can now use in Standard Edition, you have these: Compression, Change Data Capture, Fine Grained Auditing, Row-Level Security, Always Encrypted, Partitioning, Database Snapshots, In-Memory OLTP, Columnstore.
Specific Reasons For SQL Server 2016 New Features
I'm trying to keep the reasons here in this section to features introduced in SQL Server 2016 only. I'm also only describing core engine enhancements in this answer, not reporting, analytics, data integration, etc.
Security Related
Row Level Security - I have many clients looking to be able to implement security on a row by row basis. "User x can view data on this row, but not that row". Many of those clients have created functions and/or complicated view based structures to accomplish this. In SQL Server 2016 there are built in predicates and security policies available to aid here. You can read more about RLS here.
Dynamic Data Masking - This one sounds great on paper, but I wonder if it will prove to be less useful than the other two changes in the security area. Basically, you can dynamically mask, or obfuscate, data at query time based on the permissions of the caller. The amount of data to be obfuscated is something that is programmable. For instance, in the example Microsoft uses, you could mask all but the final four digits of a Credit Card number so a call taker in a call center could verify those final four digits. You can read more about that here.
Always Encrypted - Perhaps you have heard about some of the flaws or concerns over Transparent Data Encryption. Perhaps you don't want to use TDE for fear that someone with SA access can definitely decrypt if they wanted. Always Encrypted was released as a feature that puts the decrypt/encrypt ability in the hands of the application calling to SQL Server. Data at rest in SQL Server is encrypted. Data in flight heading to SQL Server is encrypted as a result of this approach also. You can read more about Always Encrypted here.
HA/DR Features
A few features came out here. One is the ability to use Always On Availability Groups in SQL Server Standard - the Basic Availability Group.  This was necessary as Mirroring is deprecated and had no replacement. The functionality is limited to the number of replicas and the approach for synchronization. You can read more here.
That isn't the only change to Availability Groups (which, by the way, were introduced in SQL Server 2012). There are also a few other changes (you can see the exhaustive list with links to other changes here):

Group Managed Service Accounts are supported.
Distributed Transactions are supported (with some caveats).
Read intent connections can be load balanced among read only replicas.
Three replicas can be involved in automatic failovers now.
Encrypted databases can now participate in an Always On Availability Group.
Performance Improvements - part of the theme of SQL Server 2016 is "it's just faster" - that's the Microsoft Marketing spin, but it is proving out in the marketplace. Several improvements were made to Availability Group performance.

Installer Fixes Some Common Mistakes
The SQL Server installer starting in SQL Server 2016 fixes some common mistakes made when configuring TempDB. The setup GUI guides you through the best practice configuration now.
Query Store
A favorite among SQL Server MVPs and tuners. This feature is a tool to give you insights into how queries are executed by the engine. Especially useful in tracking performance issues caused by changes in query plans over time. This can be a tool to view, but also a tool to fix. Helps simplify a few performance tuning concepts. You can read more about that here.
Temporal Tables
I often see a need to ask a question like "What did this data look like as of such and such a date". Temporal Tables are one method for doing that. Definitely have some warehousing use cases. See more here.
Columnstore Improvements
Three big to me improvements here among a couple others. One is updateable nonclustered columnstore indexes. SQL Server 2014 gave us updateable clustered, but now we have updateable nonclusted. SQL Server 2016 also allows columnstore indexes on In-Memory OLTP tables. And the ability to place a traditional B-tree nonclustered index on a clustered columnstore index now exists in SQL Server 2016.
Additional Feature Improvements in In-Memory OLTP
Several of the features that weren't supported inside of In-Memory tables or in natively compiled procedures are now available.  Some additions (but see this article for more): UNIQUE Constraints, Check Constraints, OUTPUT clause in DML, Outer Join, Subqueries in the select list, SELECT DISTINCT, UNION/UNION ALL, and more.
General Performance Improvements
SQL Server 2014 made some improvements to performance in several key areas as each release does. SQL Server 2016 made additional key enhancements throughout the product. That includes eliminating for many customers some of the pain sometimes encountered when upgrading to SQL Server 2014 due to the cardinality estimator changes. Several changes up and down the engine were made to improve everyday performance. Microsoft's CSS team has been blogging about this on their blog. You can just search for "SQL Server 2016 it just runs faster". An example blog post for this series is here.
I've seen this played out in clients who have upgraded, and expect to see it more and more.
Some Cons
There are cons obviously to any upgrade. It could be because I do this for a living as a consultant, but I think the cons are all incredibly manageable and really just consider them "count the cost first" items to be aware of and manage around.
Upgrades In General - In general upgrades mean you are introducing new functionality. You need to be able to test them. Between the versions of SQL Server various features are deprecated. This doesn't mean they go away right away but it means they can in future releases, often three versions later. For instance the TEXT data type, or doing the outer joins in the where clause with the *= or =* syntax are not supported if you are coming from a database running in SQL Server 2000 compatibility mode. Speaking of compatibility modes - they only go back three versions as well. So if you have a database running in 80 (SQL Server 2000) or 90 (SQL Server 2005) compatibility modes, they can't go over into SQL Server 2016 like that. So if you've been avoiding doing that test and sneaking compatibility levels, it's time for a reckoning.
Again. That is easy stuff. There are tools out there to help capture and test data to see what breaks. There are perfmon counters that can help look for deprecated features.
Licensing - Two things here:

Core Based - if you are used to pre SQL Server 2012 licensing terms, CPU licenses were just that - CPU licenses. Since SQL Server 2012, CPU based licenses have been based on the core. So there is an adjustment period there. Also Enterprise for new licenses (with some exceptions that were made when 2012 first rolled out) must be licensed at the core level - no server + CAL for enterprise. The shining silver lining on this cloud here is what I said about SP1 allowing Enterprise features in non-enterprise SKUs. If you need more than 128GB of RAM. If you need the advanced scans performance improvements. If you need more than 32GB of RAM for a columnstore or In-Memory then you are looking at Enterprise. If you don't? And you don't need online index rebuilds? Standard may just be for you, and it may be more for you now with those features newly available to you.
Software Assurance (SA) - if you didn't buy SA, you have to buy new. So if you are running SQL Server 2005 and you are worried that you are out of mainstream support, not terribly far away from the end of extended support and wish you had the features now available, you can't just upgrade like a Service Pack. If you never purchased SA, then your upgrade is a new purchase. That's okay, it really is worth it. But it is something that has to hit the budget.

Summary
There are many more reasons, like JSON support, introduction of R, etc. But these are just some of the reasons I would consider an upgrade for. The most important item, again, being the ability to use many previously Enterprise Edition-only features in Standard, or even Express. I have seen great success stories among my clients and I'm expecting to see more.
And if you are using SQL Server 2005 or 2008, the list is even larger. AlwaysOn Availability Groups came out in 2012, Columnstore Indexes in SQL Server 2012, In-Memory OLTP in SQL Server 2014, etc.
Not to mention if you are still on SQL Server 2005 or 2008, you are out of mainstream support.


Implementing a simple ERD in MySQL: is the implementation below 100% compliant with the ERD?

For the simple case shown, with a business rule something like "Each company can be contacted at zero to many email addresses; each email address directs to exactly one company" it would be more usual to have the company id as a foreign key in the email table.  What you have shown will work, but there will be a one-to-one relationship between Email and CompanyEmail, which is redundant.

For your linked question, where you actually have a Company, a Branch and an Employee, all of which can have many email addresses and any one email address could relate to any or all of Company, Branch and Employee, things are slightly different.  In the logical data model I would be inclined to show Email and the intersection entity types explicitly to document the rules fully.  In the physical table design, however, I would consider omiting the Email table.  This is because its only columns are e_addr and e_id, so e_id is really just a synonym for e_addr and e_addr can replace e_id in the intersection tables CustomerEmail, BranchEmail and EmployeeEmail.  You can still answer all the questions about one email address's re-use but won't have the actual table to maintain.  It would mean an email address disappeared from your system when it stopped being used.  That's probably OK in practice.


AlwaysOn database not synchronizing/recovery pending due to cluster loss quorum

This issue solved after click on resume data movement in node 2 . Hope it helps others.


How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY

Test

WITH cte AS ( SELECT origin,
                     provider,
                     price,
                     departure_date,
                     ROW_NUMBER() OVER (PARTITION BY /* origin, */ provider
                                        ORDER BY departure_date) rn
              FROM flights
              WHERE origin = 'LHR'
                AND destination IN ('FCO', 'CDG', 'AMS')
                AND cabin_type = 'ECONOMY' )
SELECT origin,
       provider,
       GROUP_CONCAT(price
                    ORDER BY departure_date
                    SEPARATOR ',')
FROM cte
WHERE rn <= 10
GROUP BY origin,
         provider;

UPDATE: (draft code, see comments)

WITH
cte1 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn
          FROM flights
          WHERE origin = 'LHR'
            AND destination ='FCO'
            AND cabin_type = 'ECONOMY' ),
cte2 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte1
          WHERE rn <= 10),
cte3 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn
          FROM flights
          WHERE origin = 'LHR'
            AND destination = 'CDG'
            AND cabin_type = 'ECONOMY' ),
cte4 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte3
          WHERE rn <= 10),
cte5 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                    ORDER BY departure_date) rn
          FROM flights
          WHERE origin = 'LHR'
            AND destination = 'AMS'
            AND cabin_type = 'ECONOMY' ),
cte6 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          from cte5
          WHERE rn <= 10),
cte7 AS ( SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte2
          UNION ALL
          SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte4
          UNION ALL
          SELECT origin,
                 provider,
                 price,
                 departure_date
          FROM cte6 )
cte8 as ( SELECT origin,
                 provider,
                 price,
                 departure_date,
                 ROW_NUMBER() OVER (PARTITION BY provider
                                        ORDER BY departure_date) rn
           FROM cte7)
SELECT origin,
       provider,
       GROUP_CONCAT(price
                    ORDER BY departure_date
                    SEPARATOR ',')
FROM cte8
WHERE rn <= 10
GROUP BY origin,
         provider;


Should we periodically 'refresh' the mirrored copy of a SQL Server database?

I don't trust mirroring 100%, but I do trust it about 99%, same with everything else. It never hurts to periodically check your mirror database and DR scheme. We test our DR plan across the board several times a year, and it would not hurt your scenario to set aside a DR test time and simulate a failure of your primary database to fully test the mirror if you have concerns.


Inline edit SQL Server database rows from Visual Studio


Best way to store few rows of data every minute without creating 1440 columns a day?

I would argue for 1 row per data event (so 1440 rows per day) with one static column per data point. This will be easiest to query against any of the fields.


You can use the output clause to throw the inserted rows into a table variable. Then you'll be able to see all of the identity values.

Link for the output clause:
http://technet.microsoft.com/en-us/library/ms177564.aspx


Split data into X days' Cohort (SQL Server)

Your desired outcome is that you can change one value (the cohort width) to have all rows re-allocated to the new groupings.

I'd use a separate table to hold the upper and lower bounds for each cohort. These values can be generated a run-time according to the width parameter. There are two special cases - the day-zero cohort which only ever contains a single value and the last cohort which has an unlimited upper bound. There will have to be a second parameter to say how many "normal" cohorts there must be. To generate this I will use a "numbers" table.

create table #Cohorts
(
    Label       varchar(20) not NULL,
    LowerBound  int         not NULL,
    UpperBound  int         not NULL
);

declare @Width int = 10;
declare @Depth int = 5;

insert #Cohorts
(
    Label,
    LowerBound,
    UpperBound
)
select
    'day_' + CONVERT(varchar(20), ((n + 1) * @Width)),
    1 + (n * @Width),
    ((n + 1) * @Width)
from Numbers
where n <= @Depth

union all

select 'day_0', 0, 0        -- the special case for day_0

union all

select
    'day_' + CONVERT(varchar(20), ((@Depth + 1) * @Width)) + 'plus',  -- a special label for the last cohort
    1 + ((@Depth + 1) * @Width),    -- the upper limit of the dynamic cohorts
    2147483647                      -- the maximum value of an int; pseudo-infinity

For the values given in the question this returns

Label       LowerBound  UpperBound
----------  ----------  ----------
day_0       0           0
day_10      1           10
day_20      11          20
day_30      21          30
day_40      31          40
day_50      41          50
day_60      51          60
day_60plus  61          2147483647

In your original question you have used (open, close] bounds. For simplicity I have used [closed, closed]. It doesn't matter since the bounds are integers and the cohorts are contiguous. It just means your >  and <= becomes my >= and <=. With tinkering your convention could be used equally well.

We assign each data row to a cohort by joining to this temporary table. The counting is done using GROUP BY.

select
    c.Label, COUNT(*)
from your_data_table as d
inner join #Cohorts as c
     on c.LowerBound <= d.by_day
     and d.by_day <= c.UpperBound
group by c.Label;

This will give one row per cohort. To get a single row with a column per cohort requires a PIVOT. There is a T-SQL statement to do this. There are other methods, too, of which your sum(case..) is one.


Grant select to dbo view without select on non dbo tables

You have to understand the problem before proceeding with the solution.
When you select from a view the SQL Server system would check permissions twice:
Once when you select from the view, and once when the view makes reference to the underlying table.
Obviously the second check would fail if the user has no permission on the underlying table.

Microsoft have solved this with "Ownership Chaining" (known as OC).
OC will bypass the permission check that would be done when the view is referencing the table,
Only if the owner of the view is the same as the owner of the table.
The thing is, OC is bypassing permission checks completely, which means that it can bypass denies as well...
For example, if "JoeBlogs" has the create view permission, he can create a new view which has access to the entire table.
I suggest you read about ownership chaining before you decide your course of action.

books online
msdn blog
detailed example, using a stored procedure instead of view

Having said that, the first Intuitive solution would be to create the view under the same non-dbo schema as the underlying tables (or a schema owned by the same user as the non-dbo schema).
However, if you find that solution to be too risky there is another (and maybe even better) option:
You can always use a  function (Multistatement Table-valued Function) with an EXECUTE AS clause:
Create function syntax
execute as clause
This method will allow you to select from the function (execute permission on the function) while the function belongs to dbo. The user specified in the execute as clause must have permissions on the underlying tables.


Command Line Creation of a new 12g Oracle Database

I have not manually created a database since 7.3.4. You can use dbca in silent mode without x-windows.

Installing and Configuring Oracle Database Using Response Files


Implementing voting system like reddit/StackExchange?

What you have seems pretty solid.  How about

create table vote (
   user_id int not null,
   content_id int not null,
   time_recorded datetime not null,
   vote int not null,
   primary key (user_id, content_id),
   foreign key (user_id) references `user`(user_id)
      on delete cascade,
   foreign key (content_id) references content(content_id)
      on delete cascade,
   check (vote = 1 or vote = -1)
);

A composite primary key with those two keys makes perfect sense.  They absolutely should be foreign keys as well.  I threw in to cascade a delete from the parent tables; if a user deletes, the "like" will be removed from the content (which, IIRC, is what happens in StackExchange), and if the content is removed, there is no real point in keeping the votes on it.  I also implemented the vote column as an integer which must be either 1 or -1, rather than a boolean.  That way you can get an overall tally of up and down votes with a simple sum(vote), rather than two select count(*).


Does MariaDB support schema changes inside a transaction?

No.

"DDL statements (CREATE, ALTER, DROP) [...] cause an implicit COMMIT"

Thanks horse with no name.


SQL Database structure to store calculations

I would just have a single table to store the relationships, just add a ScriptType and then you have the classic Entity Attribute Value table.  Properly indexed (an example is below) the performance should be pretty good.  Then adding additional types is straightforward.  Not sure if it makes more sense for you to have the ScriptType attribute as a component of the CalculationScript table or not, but I would definitely go with a single table.

CREATE TABLE CalculationScript(
ScriptId int NOT NULL PRIMARY KEY,
Script VARCHAR(8000) NOT NULL
);

CREATE TABLE Calculation(
CalculationId int NOT NULL PRIMARY KEY,
Name nvarchar(100) NOT NULL
);

CREATE TABLE ScriptType(
ScriptTypeID INT NOT NULL PRIMARY KEY,
[Name] NVARCHAR(100) NOT NULL
);

CREATE TABLE Script_EAV(
Script_EAVID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
CalculationId int FOREIGN KEY REFERENCES Calculation(CalculationId),
ScriptTypeID INT FOREIGN KEY REFERENCES ScriptType(ScriptTypeID),
ScriptId int FOREIGN KEY REFERENCES CalculationScript(ScriptId)
)

CREATE NONCLUSTERED INDEX IDX_Script_EAV_CalclationID_ScriptTypeID ON dbo.Script_EAV
    (CalculationID, ScriptTypeID)
    WITH (FILLFACTOR=70, SORT_IN_TEMPDB=ON)


Your answer

  more than one attribute with same name (Color for example),

So then create a different attribute with more specificity.

  I am also required to store the image photo url.

This is just another attribute...

Product Attribute
-----------------
2 | photo_exterior

Product Attribute Value
-----------------------
id: 1
attribute_id: 2
value: 01/a0/01a0sdadasfsdfsdfasdfasfdfsafs.jpg

All of that said, I wouldn't be doing this unless I had to. I would be using JSONB.

  What is the best way to fetch products with all attributes?

I'm unsure what kind of resultset you want. Are you asking how do you query an EAV? If you can make it, you can certainly do the joins to get the result set out of it.

  Is there any way to fetch each product with all attributes with one query (one product -> one row)?

Yes, if you don't know how many attributes you have, you'll likely end up using hstore or jsonb though. See jsonb_agg()

X-Y problem?

Examining speed in order to get you to reasses JSONB and save your life. Create a million products, rename the attribute using jsonb.

CREATE TABLE products(id,name,data)
AS
        SELECT x, 'foo'::text, jsonb_build_object('color', 'blue') AS data
        FROM generate_series(1,1e6) AS gs(x);

UPDATE products
SET data = data-'color' || jsonb_build_object('colour', data->'color');
Time: 5063.010 ms

On my x230 laptop, with an SSD. That's 197 rows/ms? You recreating Amazon that you have to update faster than that?


MySQL cluster: Failover between data node

The management node is not essential for normal mysql operations, except on:

Installation and other cluster-wide maintenance operations
It is by default the arbitrator node, which means that it holds the responsibility to decide if a particular partition continues running in the case of a split brain.

Split brain is an important concept in clustering, which happens when apparently, a group of nodes identify some other group of nodes as down (a group can be a single node). However, you cannot be sure if they are really down or the communication among them is down. In order to maintain consistency, an algorithm must decide what to do. If not, your separate partitions of the cluster could end up with different states/data sets. Typically, and that is what happens, if your partition has more than 50% of the nodes, you continue, if not, you stop yourself.

In your particular case, without an arbitrator, as 1 node is only 50% of the data nodes, it decides to "kill themself" -it is not a failure, it is a design decision. With the arbitrator, we know that the other node will not continue by himself, as the arbitrator cannot see it, so it decides to continue with 50% of the nodes (1, that has all the data, as the default is noofreplicas = 2) + arbitrator. In general, you want to run with an odd number of nodes to minimize the possibility of a full cluster shutdown.

Typically, on an NDB setup, your SQL nodes are also a primary and a secondary arbitrator node (you can modify its weights). You normally have more than one API node, to avoid a single point of failure. So the most typical setup is 2 API/SQL/arbitrator nodes and 2 NDB nodes. That way, in most cases, 2 nodes have to go down for the whole cluster to fail.


(You don't say which DBMS, but inferring SQL Server from your temporary table name..)

If you're on a recent enough version (>=2008 ?) you can achieve the fine level of rollup control you're after with GROUPING SETS. For your 2nd query, the equivalent would be:

SELECT a,b,c, COUNT(*)
FROM #TEMP
GROUP BY GROUPING SETS ((a,b,c),())


There are certain situations where a GUID might be needed, but in general I'd advise against it.   The main reason is that GUIDs are, by their nature, random.  So using them as an identity column essentially means you're storing your rows randomly.  With a typical incremental primary key, the data is sequential.   Table scans are usually more efficient, etc.

In the end, there are tradeoffs with either situation.   Brent Ozar has an older article on the topic:  https://www.brentozar.com/archive/2014/08/generating-identities/


Grand read privilege to the user but is not allowed to grant the same privilege to another user?

the solution was to add

    WITH GRANT OPTION;

at the end of grant command line.
e.g.
if i wanted to grant select privilege (+ allow user to grant the same privilege to another user), to a user called user1 for table EMPLOYEE. Here would a an appropriate command

GRANT SELECT ON DATABASENAME.EMPLOYEE TO USER1  WITH GRANT OPTION;


I would model this using a single table for the Professions, and a related table for Specializations.

USE tempdb;

DROP TABLE IF EXISTS dbo.Users;
DROP TABLE IF EXISTS dbo.Specializations;
DROP TABLE IF EXISTS dbo.Professions;
GO

CREATE TABLE dbo.Professions
(
    ProfessionID int NOT NULL
        CONSTRAINT PK_Professions
        PRIMARY KEY CLUSTERED
    , ProfessionName  varchar(30) NOT NULL
);

CREATE TABLE dbo.Specializations
(
    ProfessionID int NOT NULL
        CONSTRAINT FK_Specializations_ProfessionID
            FOREIGN KEY
            REFERENCES dbo.Professions(ProfessionID)
    , SpecializationID int NOT NULL
    ,   CONSTRAINT PK_Specializations
        PRIMARY KEY CLUSTERED
        (ProfessionID, SpecializationID)
    , SpecializationName varchar(30) NOT NULL
);

Some sample data:

INSERT INTO dbo.Professions (ProfessionID, ProfessionName)
VALUES (1, 'Computer Scientist')
    , (2, 'Electrician')
    , (3, 'Janitor')
    , (4, 'Architect');

INSERT INTO dbo.Specializations (SpecializationID, SpecializationName, ProfessionID)
VALUES (1, 'Application Development', 1)
    , (2, 'System Engineering', 1)
    , (3, 'Support', 1)
    , (4, 'Small Buildings', 4)
    , (5, 'Tall Buildings', 4);

The professions, with their associated specializations:

SELECT p.ProfessionName
    , sp.SpecializationName
FROM dbo.Professions p
    LEFT JOIN dbo.Specializations sp ON p.ProfessionID = sp.ProfessionID

Results:

╔════════════════════╦═════════════════════════╗
║   ProfessionName   ║   SpecializationName    ║
╠════════════════════╬═════════════════════════╣
║ Computer Scientist ║ Application Development ║
║ Computer Scientist ║ System Engineering      ║
║ Computer Scientist ║ Support                 ║
║ Electrician        ║ NULL                    ║
║ Janitor            ║ NULL                    ║
║ Architect          ║ Small Buildings         ║
║ Architect          ║ Tall Buildings          ║
╚════════════════════╩═════════════════════════╝

If your users can only have a single profession, I would create a Users table like this:

CREATE TABLE dbo.Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY
        CLUSTERED
    , UserName varchar(30) NOT NULL
    , ProfessionID int NOT NULL
        CONSTRAINT FK_Users_Profession
        FOREIGN KEY
        REFERENCES dbo.Professions(ProfessionID)
    , SpecProfID int NULL
    , SpecializationID int NULL
    , CONSTRAINT FK_Users_Specialization
        FOREIGN KEY (SpecProfID, SpecializationID)
        REFERENCES dbo.Specializations(ProfessionID, SpecializationID)
    , CONSTRAINT CK_Users_ProfSpec
        CHECK (
            (SpecProfID IS NULL AND SpecializationID IS NULL)
            OR (COALESCE(SpecProfID, 0) = COALESCE(ProfessionID, 0))
            )
);

The check constraints ensure only valid professions and specializations can be added at the expense of requiring a "duplicate" ProfessionID column for specializations.

Inserting users then looks like this:

INSERT INTO dbo.Users (UserID, UserName, ProfessionID, SpecProfID, SpecializationID)
VALUES (1, 'Little Johhny', 1, 1, 2)
    , (2, 'Mary Quite Contrary', 2, NULL, NULL);

Seeing the users with their professions and specializations looks like:

SELECT u.UserName
    , p.ProfessionName
    , sp.SpecializationName
FROM dbo.Users u
    INNER JOIN dbo.Professions p ON u.ProfessionID = p.ProfessionID
    LEFT JOIN dbo.Specializations sp ON p.ProfessionID = sp.ProfessionID
                   AND u.SpecializationID = sp.SpecializationID;

╔═════════════════════╦════════════════════╦════════════════════╗
║      UserName       ║   ProfessionName   ║ SpecializationName ║
╠═════════════════════╬════════════════════╬════════════════════╣
║ Little Johhny       ║ Computer Scientist ║ System Engineering ║
║ Mary Quite Contrary ║ Electrician        ║ NULL               ║
╚═════════════════════╩════════════════════╩════════════════════╝

Attempting to insert invalid data looks like:

INSERT INTO dbo.Users (UserID, UserName, ProfessionID, SpecializationID)
VALUES (3, 'Peter Pumpkin Eater', 3, 2);

  Msg 547, Level 16, State 0, Line 83
  The INSERT statement conflicted with the CHECK constraint "CK_Users_ProfSpec". The conflict occurred in database "tempdb", table "dbo.Users".

If I needed to support users with multiple professions, I'd use a cross-reference table, like this:

DROP TABLE IF EXISTS dbo.UsersProfessions;
DROP TABLE IF EXISTS dbo.Users;
GO

CREATE TABLE dbo.Users
(
    UserID int NOT NULL
        CONSTRAINT PK_Users
        PRIMARY KEY
        CLUSTERED
    , UserName varchar(30) NOT NULL
);

CREATE TABLE dbo.UsersProfessions
(
    UserID int NOT NULL
    , ProfessionID int NOT NULL
    , SpecProfID int NULL
    , SpecializationID int NULL
    , CONSTRAINT PK_UsersProfessions
        PRIMARY KEY CLUSTERED
        (UserID, ProfessionID)
    , CONSTRAINT FK_UsersProfessions_ProfessionID
        FOREIGN KEY (ProfessionID)
        REFERENCES dbo.Professions(ProfessionID)
    , CONSTRAINT FK_UsersProfessions_Specialization
        FOREIGN KEY (SpecProfID, SpecializationID)
        REFERENCES dbo.Specializations (ProfessionID, SpecializationID)
    , CONSTRAINT CK_UsersProfessions_Specialization
        CHECK (
            (SpecProfID IS NULL AND SpecializationID IS NULL)
            OR (COALESCE(ProfessionID, 0) = COALESCE(SpecProfID, 0))
            )
);

Insert data:

INSERT INTO dbo.Users (UserID, UserName)
VALUES (1, 'Little Johnny')
    , (2, 'Mary Quite Contrary')
    , (3, 'Peter Pumpkin Eater');

INSERT INTO dbo.UsersProfessions (UserID, ProfessionID, SpecProfID, SpecializationID)
VALUES (1, 1, 1, 1)
    , (1, 4, 4, 5)
    , (2, 2, NULL, NULL)
    , (2, 3, NULL, NULL);

Query the data:

SELECT u.UserName
    , p.ProfessionName
    , sp.SpecializationName
FROM dbo.Users u
    LEFT JOIN dbo.UsersProfessions up ON u.UserID = up.UserID
    LEFT JOIN dbo.Professions p ON up.ProfessionID = p.ProfessionID
    LEFT JOIN dbo.Specializations sp ON up.SpecProfID = sp.ProfessionID AND up.SpecializationID = sp.SpecializationID

Results:

╔═════════════════════╦════════════════════╦═════════════════════════╗
║      UserName       ║   ProfessionName   ║   SpecializationName    ║
╠═════════════════════╬════════════════════╬═════════════════════════╣
║ Little Johnny       ║ Computer Scientist ║ Application Development ║
║ Little Johnny       ║ Architect          ║ Tall Buildings          ║
║ Mary Quite Contrary ║ Electrician        ║ NULL                    ║
║ Mary Quite Contrary ║ Janitor            ║ NULL                    ║
║ Peter Pumpkin Eater ║ NULL               ║ NULL                    ║
╚═════════════════════╩════════════════════╩═════════════════════════╝

Once again, the constraints in place prevent invalid profession/specialization combinations:

INSERT INTO dbo.UsersProfessions (UserID, ProfessionID, SpecProfID, SpecializationID)
VALUES (3, 3, 3, 2);

  Msg 547, Level 16, State 0, Line 149
  The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UsersProfessions_Specialization". The conflict occurred in database "tempdb", table "dbo.Specializations".

This will also support users with a profession, that have not specialized:

INSERT INTO dbo.Users (UserID, UserName)
VALUES (4, 'Max');

INSERT INTO dbo.UsersProfessions (UserID, ProfessionID)
VALUES (4, 1);

SELECT u.UserName
    , p.ProfessionName
    , sp.SpecializationName
FROM dbo.Users u
    LEFT JOIN dbo.UsersProfessions up ON u.UserID = up.UserID
    LEFT JOIN dbo.Professions p ON up.ProfessionID = p.ProfessionID
    LEFT JOIN dbo.Specializations sp ON up.SpecProfID = sp.ProfessionID AND up.SpecializationID = sp.SpecializationID

╔══════════╦════════════════════╦════════════════════╗
║ UserName ║   ProfessionName   ║ SpecializationName ║
╠══════════╬════════════════════╬════════════════════╣
║ Max      ║ Computer Scientist ║ NULL               ║
╚══════════╩════════════════════╩════════════════════╝


Update dynamic column names using keys from jsonb argument

Your case is too dynamic for json_populate_record(): it takes a record type which you don't know (nor have) at the time of calling.

PROCEDURE calling UPDATE for each JSON key

Proof of concept to show what did not work in your attempt.

CREATE PROCEDURE record_event (
   foo_arg int,
   name_arg text,
   data_arg jsonb,
   occurred_at_arg timestamptz
) AS
$proc$
DECLARE
   column_name text;
BEGIN
   INSERT INTO foo_event
        (foo    , name    , data    , occurred_at)
   VALUES(foo_arg, name_arg, data_arg, occurred_at_arg);

   -- works, but inefficiently:
   FOR column_name IN
      SELECT * FROM jsonb_object_keys(data_arg)
   LOOP
      EXECUTE format('UPDATE foo SET %I = $1 WHERE id = $2', column_name)
      USING data_arg->>column_name, foo_arg;
   END LOOP;
END
$proc$  LANGUAGE plpgsql;

Call:

CALL record_event(1, 'name_arg', '{"col1":"val1","CoL2":"val2"}', now());

Column names cannot be dynamic, so format the query (with format() for convenience) and use EXECUTE. But values are better provided with the USING clause.

Note the format specifier %I, but the parameters $1 and $2 refer to values provided by the USING clause (not to function parameters!).

But I would not use it. Multiple UPDATE commands are pretty inefficient. Instead use a ...

Function with a single UPDATE

Should be much more efficient.

CREATE OR REPLACE FUNCTION record_event(
   foo_arg int,
   name_arg text,
   data_arg jsonb,
   occurred_at_arg timestamptz
)
  RETURNS void AS
$func$
DECLARE
   _sql text;
BEGIN
   INSERT INTO foo_event
          (foo    , name    , data    , occurred_at)
   VALUES (foo_arg, name_arg, data_arg, occurred_at_arg);

   SELECT INTO _sql
          'UPDATE foo SET '
       || string_agg(format('%I = %L', key, value), ', ')
       || ' WHERE id = ' || foo_arg
   FROM   jsonb_each_text(data_arg);

   IF _sql IS NOT NULL THEN
      -- RAISE NOTICE '%', _sql; -- uncomment instead of EXECUTE to debug
      EXECUTE _sql;
   END IF;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT record_event(1, 'name_arg', '{"col1":"val1","CoL2":"val2"}', now());

We could pass values with another USING clause. But simply concatenate the whole command for convenience.

Using a FUNCTION as there is nothing that would require a PROCEDURE (new since Postgres 11). Would work as procedure too, though.

As always, properly quote identifiers and values to defend against possible SQL injection. I only dare to concatenate foo_arg directly, as an integer value is safe in this regard and works as unquoted numeric literal.

Note that column names are treated as case-sensitive.

Are PostgreSQL column names case-sensitive?

Related:

SQL injection in Postgres functions vs prepared queries
When to use stored procedure / user-defined function?
How to create a table dynamically based on json data?


Optimize table on large table online DDL Feature

OPTIMIZE TABLE is almost useless for InnoDB.  Why are you running it?
Some people like to break up the disk into partitions.  But that leads to overflowing one partition when there is spare room in another.  You could move tmpdir to a bigger partition?
OPTIMIZE TABLE table must copy the table over.  Ditto for flavors of ALTER TABLE.  Hence, you must have enough spare disp space, preferrable in the same FS, as the table.
MySQL 8.0 has some "inline" or "in place" ALTERs; maybe it would help here.
Percona's pt-online-schema-change avoids blocking writes during schema changes.  Ditto for gh-ost.  These probably need the extas disk space, so may not be relevant.

Moral of this story...  Don't let the disk get so full that you can't make a full copy of the biggest table.


There are two ways

ALTER TABLE qwerty RENAME qwerty_2;

or

RENAME TABLE qwerty TO qwerty_2;


In my opinion, the RAM given over to a RAM disk would be better utilized for other caching.  If InnoDB, give it to innodb_buffer_pool_size.  For MyISAM, give a little to key_buffer_size, leave most for data caching, which the OS does.


Fail to login on Sql server with DNS but not with IP

I've also create an Alias on SQL Server

An alias is a client-side concept.  To be effective you must configure the alias on every client computer connecting to SQL Server.  The alternative is to create a Hostname Alias to enable that name to be resolved by all clients on the network.

There are four easy steps to creating a Hostname Alias, but they are poorly documented and often misunderstood. All you have to do is:

1) Create a DNS Record pointing to the IP address of the target server

2) Configure SQL Server to listen on port 1433 on that IP address

3) Add SPNs to enable the SQL Server Service account to use Kerberos Authentication (optional if you don't use Kerberos).

4) Add BackConnectionHostNames entries to enable NTLM Authentication

https://blogs.msdn.microsoft.com/dbrowne/2012/05/21/how-to-add-a-hostname-alias-for-a-sql-server-instance/


Entering Exact Values into a Table Using SQL

Just do not use string interpolation or concatenation to get values into SQL queries in PHP. That's error prone and might make your program vulnerable to SQL injection attacks.
Use parameterized queries. See "How to include a PHP variable inside a MySQL statement" and "How can I prevent SQL injection in PHP?".
Using parameterized queries will also ensure that the data is automatically escaped in the necessary way. So this also solves your problem. No manual escaping is necessary.


What's the difference between equality (=) and inequality () on BIT field in SQL Server?

They seem logically equivalent.

They are.  Even with ANSI_NULLS OFF "a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL."
I found an old feedback item for this here, you can go vote for it.


Normally, you'd simply alias the subquery (which I thought was required in SQL Server)

SELECT DISTINCT ssn, job, identifier, startdate, enddate, salary, ceasing
  FROM oldcontracts
       JOIN (SELECT DISTINCT name, identifier
               FROM jobs) mysubquery
         ON oldcontracts.job = mysubquery.name

I would strongly suggest that you always alias any tables in your query and that you use those aliases for every column in the select list.  That makes it much easier to see where the different data elements are coming from and makes it easier when you add additional joins in the future.


Microsoft Azure completely ignores IPs of any internal network on Azure, if you want any computers to connect to a listener in an AlwaysOn availability group make sure 100% sure you specify the VIP, Virtual IP, of the Cloud Service and not the internal IP of the cluster. You will not be able to connect to the cluster or to the listener.

When on the Dashboard view, note the address shown under Public Virtual IP (VIP) Address.
Completely ignore the internal IP Address for listener configuration.


Which one of these two contact management database designs is correct or best practice?

In general. Recommended structure (assuming one user can fit into several categories):

users (user_id,
       name,
       -- etc.,
       primary key (user_id))

categories (cat_id,
            category_type, -- shipper, supplier, etc.
            name,
            -- etc.,
            primary key (cat_id))

users_to_categories (user_id,
                     cat_id,
                     -- etc.,
                     primary key (user_id, cat_id),
                     foreign key (user_id) references users (user_id),
                     foreign key (cat_id) references categories (cat_id))

If some user fits in more than one category there is more than one record in the last table with the same user_id and different cat_id.

  A user can only choose one category for example, supplier. If he wants to change to customer, he must delete his account first.

If so:

users (user_id,
       cat_id,
       name,
       -- etc.,
       primary key (user_id),
       foreign key (cat_id) references categories (cat_id))

categories (cat_id,
            category_type, -- shipper, supplier, etc., ENUM
            name,
            -- etc.,
            primary key (cat_id))


Update column based on sum of rows from another table by join

One approach:

CREATE TEMPORARY TABLE x
    SELECT id, SUM(c) AS TheSum
        FROM table2 ...;

UPDATE table1 JOIN x ON (table1.id = x.id)
    SET table1.a = x.TheSum
    WHERE table1.b = "something" AND table1.a IS NULL