MySQL Error 1064 (42000). Insert failure on MySQL table

Please check here to see why posting images of text is not a good idea (can't cut and paste!).
Always put the error code in your question's Title. Easier to search, easier to find!
Also, use the "Code sample" tool for code, not the "Block Quote".
Finally, and to answer your question, your SQL appears to have an error - the name of the table you are querying is "9636354343_ whatsapp_friends" and not "9636354343_whatsapp_friends" - i.e. your code as posted has a space between the first underscore and the "whatsapp" part.  Always give a SHOW CREATE TABLE my_table\G (as text) with table definitions.

In MySQL, you can have 64 characters in identifiers! That_is_more_than_enough_dont_you_think? (=40 characters!). I know what you did was an error, but MySQL (and other servers) do allow you to have spaces in identifiers, but putting the names in backticks (`) or double-quotes (") or whatever.

I cannot stress enough what madness this is - hic sunt leones!. Developers will always be asking "[Is that | Should that be] a space? "Is it a typo"? What happens if you accidentally put in two spaces - can be tricky to read and especially debug! Continue as you are doing, and use underscores to split identifiers to make them more readable!

Also, if you are in a multi-developer shop, pick a few conventions (you can Google best practices) and stick to them! Table names (singular or plural). Upper or Lower case - some servers enforce, others don't (with and/or without escaping).

I (for example, and this is just me) always use singular table names (with the obvious exception of order) and lower case for my identifiers, UPPER case FOR my SQL; - it makes it easy to pick out the language parts from your own objects.

Finally, no matter what you do, you should always use the comments facility available for tables and columns to let other developers know what the table/field is for and if they need to know anything special about the name, if it's not in accord with your convention!

p.s. welcome to the forum! p.p.s you can let people know you've replied to them by using @ in the text of your comment, i.e. for me, it's @Vérace!


Creating a Maintenance Plan for a SQL Azure Database

Instead of creating a maintenance plan on a local SQL Server instance to perform maintenance tasks on an Azure SQL database, consider creating a runbook using Azure Automation. The runbook should run  a PowerShell script like the following:

$AzureSQLServerName = "<ServerName>"
$AzureSQLDatabaseName = "<DatabaseName>"

$AzureSQLServerName = $AzureSQLServerName + ".database.windows.net"
$Cred = Get-AutomationPSCredential -Name "SQLLogin"
$SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName -Query "exec [dbo].[AzureSQLMaintenance] @Operation='all' ,@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1

Write-Output $SQLOutput

Please read detailed information here.


Index Reorgs Blocking Statistics Updates Blocking Inserts

Here's what I'd try:

Reschedule my reorgs to occur outside of data loads
Change the threshold that I reorg to a much higher value than the default for Ola's scripts (5%) to something like 50%. Also crank rebuilds up to 70 or 80% if you're using them.
Stop running reorg and rebuilds all together, and just update stats after a data load

Update:
For auto update stats, you could

Turn them off during data loads, and back on afterwards/after stats updates run from your job
Leave them off and run stats updates jobs at regular intervals during the day

Hope this helps


The PIVOT synxtax is:

WITH query as (
    your query...
)
SELECT Dia_Da_Semana, [00:00], [01:00], [02:00], ...
FROM query
PIVOT (
    MAX(Count_Reps_Abertas)
    FOR Hora IN ([00:00], [01:00], [02:00], ...)
) as piv
;

You can refer to: Using PIVOT and UNPIVOT

Your query goes to the query CTE. This query/CTE must return 3 columns:

row name = [Dia_Da_Semana]
column name = Hora
value = Count_Reps_Abertas

The SELECT and the FOR ... IN(...) must contain a list of all the rows to be pivoted into columns.

Why is there a MAX? Pivot needs an aggregate function such as count, sum, max or min. This is because you could have several rows for each pair of name and month and need to group their values. You have only 1 row for each pair of name and hour but you still have to use it although there is only 1 value to add or max. Therefore you could use either of max, min or sum.


Probably this question is more suitable for StackOverflow ;-)
Anyway: a combination of the two approaches is what I'd use: the User has a small research tool that displays the tags that contains the word (or a part of the word) User is inserting. If a tag already exists the User can select that tag. If a tag doesn't exist, the new tag will be manually approved by a "superuser".
I think this looks like the same approach used in StackOverflow (and in all forums of Stack Exchange)


Assessing statement about third normal form

The exercises says:

  A relation R is in 3NF, if every non-prime attribute of R, is fully functionally dependent on some key of R.

As you have noted, that definition is imprecise. Let’s try to correct it:

  A relation R is in 3NF, if every non-prime attribute of R is fully functionally dependent on some (candidate) key and on nothing else.

Of course such definition should also exclude trivial dependencies or derived dependencies in which the left part contains superflous attributes.


Cannot browse for Backup files using SSMS

Most likely the problem is that the Windows or ADS account the is the service account for the SQL Server Engine doesn't have access to the directory mentioned. Give that user (note that this is not going to be a SQL Server user) access to read/write to the directory and your problem should be resolved.


would like to secure or prevent it from being accessed or copied (tables, views, data etc)

No, this is impossible and anybody telling you otherwise is selling you snake oil. What you want is called DRM and SQL Server cannot do DRM. At best you can prevent accidental media loss (ie. your IP ends up on a stolen laptop or on disk in a flea market and somebody simply reads the precious table and views definition) and that scenario is prevented with on-disk encryption (either TDE or BitLocker will do). But you cannot prevent an administrator from inspecting your database.


To find a version for your pc check with the software publisher install mongodb

Which version did you download and install?  If you have 64-bit Windows 8, you should download and install the build marked with "2008R2+".  Their download page indicates that:

  The Windows 2008+ build uses newer features of Windows to enhance
  performance. Use this build if you are running with 64-bit Windows
  Server 2008 R2, Windows 7, or greater.

If you are using 32-bit Windows 8, you should download the 32-bit Windows version.  Of course the 32-bit version has some limitations, such as a max database size of 2GB.


The Update would benefit from the composite INDEX(column_19, updatedOn).

It is almost never faster to use a client loop to do something that can be done in a single SQL statement.

Since you are talking about updating upwards of a million rows, there are other things to discuss...

One should rarely need to update more than a few rows of a table in a batch.  Perhaps column_20 does not belong in that table; maybe it belongs in a small, maybe one-row, table to avoid this?  Maybe something else.  If you care to describe the semantics of the columns, maybe I can give you more concrete advice.

The reason for the big updating seeming to take "too long" is that it is saving things in case you need to ROLLBACK.

But, if you do need to do a massive UPDATE or DELETE, I recommend doing it in chunks.  There are notes on the impact on Replication in that document.


insert data in multiple tables in one mysql query

Here are two ways to provide the appearance of "should be performed in single query".  What is your goal behind requesting such?
Data integrity  (see "ACID")
There are cases where you 'must' tie two inserts together, else the integrity of the database is in question.  The classic example is moving money from one account to another, but that usually involves a single table.
 BEGIN;
 UPDATE ... take money out of one person's account
 UPDATE ... add that money to another person's account
 COMMIT;

Whether the same table or different tables are involved, the above guarantees that both operations will be done or neither will be done.  The "money" won't vanish even if there is a power failure at any time during the "transaction".
Is that what you need for "inserting into two table at the same time"?  A transaction can, and should, be used for any set of writes to table(s) in database(s) (on a single instance of MySQL) where the writes must be done "at the same time".
Programming
Akina suggests using a Stored Procedure as a way to programmatically wrap the sequence of events, thereby giving the user the appearance of a "single operation":
CALL Transfer(acct1, acct2, amount);


Can I GRANT SELECT on all schemas in a database?

You can wait for PostgsqlSQL v14 and its pg_read_all_data system role.
Other than that, you will have to grant USAGE on all schemas and SELECT on all tables individually.
GRANT SELECT ON ALL TABLES IN SCHEMA will make that job much easier.
My recommendation is to issue all these grants not to the end user role, but to an intermediate role that you then grant to the end user. That way it will be easy to drop qaread.


Why is the default width for a MySQL signed MEDIUMINT 9 instead of 8?

Hmmm... Sounds like a bug.  But since that number is not used for anything (assuming you don't have ZEROFILL), then it is no big deal.

You could, if you like, file a bug report at bugs.mysql.com .


You can run

flush status;
show global variables like 'innodb_ft_cache_size';
show status like 'handler_read_rnd_next';

If it is 2 (or incremented by 2) than you have the variable. If it is 1 (or incremented by 1) you don't.

That should work on every version.


PostgreSQL Reduce Writes on Flash Storage (TBW)

To reduce the number of checkpoints as much as possible, increase max_wal_size and checkpoint_timeout. The price is longer recovery time after a crash.
Make sure that shared_buffers is not too low, so that there is no memory pressure that forces dirty pages out to disk between checkpoints. You could experiment with setting bgwriter_lru_maxpages = 0, so that the background worker does not proactively clean out pages between checkpoints.
Set wal_compression = on so that full page writes are compressed (at the expense of CPU time).


Safe to Restore Database from Untrusted Sources

Is it safe to restore the database onto my machine?

Quite safe if you restore using a non-superuser account. CREATE USER a user for the purpose, CREATE DATABASE ... WITH OWNER ... a db owned by that user, and pg_restore using that user into that database. I'd do it happily enough. The custom format is basically SQL snippets and CSV-like row data in an archive.

To be more careful you can request CSV files and use COPY or psql's \copy to load the CSV files into the database. That's exceedingly safe.

  Can they inject something malicious in there?

Yes, if you restore it using a superuser account like the postgres user. They could do something nefarious like create a plperlu, plpythonu, etc procedure (if you have those languages available in your install) that then runs arbitrary code as the postgres user. It probably can't get outside the postgres operating system user and attack the rest of the system. Probably. But local root exploits aren't as hard to find as they ideally would be.

On Windows you're less likely to have plperl and plpython enabled, but you're likely to run PostgreSQL under NETWORKSERVICE, which is quite privileged. So again, I wouldn't, just in case. They might be able to do something nasty with lo_import and lo_export for example.

In general PostgreSQL tries to make it less than entirely trivial for the database superuser to gain shell access as the user it's running as, at least if no procedural languages other than plpgsql are installed. It's not a strong sandbox though, and it's not at all surprising if you can break out of it and get shell access from the postgres superuser.

Shell access, running code as superuser, etc, from non-superuser accounts should not be possible under any circumstances, though, barring gross misconfiguration like creating a SECURITY DEFINER procedure that runs arbitrary SQL as the superuser.


Is there any way to collect script of running SP with it's input value using DMVs?

Have a look at a possible method here - Getting the Query Plans and Parameters Directly from the Plan Cache


SQL Server Full Text Search - .rtf files incorrectly indexed by rtf tags

I can reproduce this but I think you have a few options:

not worry too much about full-text index bloat.  Not many people are going to be searching for 'rtf1', 'pard' or 'wmetafile0' and I don't think it will affect your performance that much.  You might get the odd clash if someone is searching for 'red' or 'blue' but I'd say this is fairly low risk.
Clean the string up eg with RegEx I copied from here.  Seemed to make a good go of it.  Store it in a separate table with the stream_id ( which is the unique id for the document ) and full-text index that table instead:

-- Convert rtf to plain text
SELECT
    CAST( file_stream AS VARCHAR(MAX) ) original
    , MDS.mdq.RegExReplace(
        CAST( file_stream AS VARCHAR(MAX) ),
        '\{\*?\\[^{}]+}|[{}]|\\\n?[A-Za-z]+\n?(?:-?\d+)?[ ]?', '', 1 )
FROM dbo.Documents

Similar approach with a CLR function I adapted from here:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;
using System.Linq;  // for TakeWhile

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlChars rtfToText( SqlChars inputRtf )
    {
        // RTF function lifted from here and adapted for SQL CLR:
        // https://stackoverflow.com/questions/23277178/richtextbox-throws-outofmemory-on-azure

        bool slash = false; //indicates if backslash followed by the space
        bool figure_opened = false; //indicates if opening figure brace followed by the space
        bool figure_closed = false; //indicates if closing brace followed by the space
        bool first_space = false; //the else spaces are in plain text and must be included to the result

        if (inputRtf.Length < 4) return new SqlChars ( string.Empty );

        int i = 0;
        i = inputRtf.ToString().IndexOf("\\pard");
        if (i < 1) return new SqlChars(string.Empty);

        var builder = new StringBuilder();
        for (int j = i; j < inputRtf.Length - 1; j++)
        {
            char ch = inputRtf[j];
            char nextCh = inputRtf[j + 1];

            if (ch == '\\' && nextCh == 'p') // appends \n if \pard, except for first
            {
                if (j > i && j < inputRtf.Length - 4)
                {
                    string fiveChars = inputRtf.ToString().Substring(j, 5);
                    if (fiveChars.Equals("\\pard"))
                    {
                        builder.Append("\n");
                    }
                }
            }

            if (ch == '\\' && nextCh == 'u') // to deal correctly with special characters
            {
                string fourChars = inputRtf.ToString().Substring(j + 2, 4);
                string digits = new string(fourChars.TakeWhile(char.IsDigit).ToArray());
                char specialChar = (char)int.Parse(digits);
                builder.Append(specialChar);
                j += digits.Length + 5;
                continue;
            }

            if (ch == '\\' && nextCh == '{') // if the text contains symbol '{'
            {
                slash = false;
                figure_opened = false;
                figure_closed = false;
                first_space = false;
                builder.Append('{');
                j++;
                continue;
            }
            else if (ch == '\\' && nextCh == '}') // if the text contains symbol '}'
            {
                slash = false;
                figure_opened = false;
                figure_closed = false;
                first_space = false;
                builder.Append('}');
                j++;
                continue;
            }
            else if (ch == '\\' && nextCh == '\\') // if the text contains symbol '\'
            {
                slash = false;
                figure_opened = false;
                figure_closed = false;
                first_space = false;
                builder.Append('\\');
                j++;
                continue;
            }
            else if (ch == '\\') // we are looking at the backslash
            {
                first_space = true;
                slash = true;
            }
            else if (ch == '{')
            {
                first_space = true;
                figure_opened = true;
            }
            else if (ch == '}')
            {
                first_space = true;
                figure_closed = true;
            }
            else if (ch == ' ')
            {
                slash = false;
                figure_opened = false;
                figure_closed = false;
            }

            if (!slash && !figure_opened && !figure_closed)
            {
                if (!first_space)
                {
                    builder.Append(ch);
                }
                else
                {
                    first_space = false;
                }
            }
        }

        // Return
        return new SqlChars(builder.ToString());
    }
};

I'm sure this isn't the world's most efficient CLR function but it did an ok job on some 100 documents I tested, with a few fails.

Let us know if you hear anything back on the connect item.


How to fix "Access denied for user 'root'@'localhost'" error for MySQL using Mac OS X?

Run this /usr/bin/mysql_upgrade -u root -p
This should reset the rights and grant access with u logged in as 'root'@'localhost'
Thereafter run this /usr/bin/mysql -u root -p... et viola!


How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure

It looks as though the package has been saved with sensitive data protected, meaning that it can only be run by specified users.

To fix it, set the PackageProtectionLevel to DontSaveSensitive and then re-deploy the package to the server.


GRANT ALTER ANY DATABASE TO [somelogin]; or adding the user to the db_creator server-level role, will allow that login to create and/or restore databases, however, that user will not be able to access that database unless the restored database already has a user with the same SID that has the appropriate access.  See my answer here for more details.

There is no server-level role or right that allows a login access to any database without simultaneously allowing that login to modify server-level properties.

The typical setup for creating/restoring databases consists of having a sysadmin member do the restore, and modify access per the requirements for that specific database.

Technet has a link to an excellent poster that describes all the rights and permissions grantable inside SQL Server.


PostgreSQL: restore a record from a backup

On way would be to restore only the relevant table to a new empty dummy database. Then find rows with standard SQL tools. Not sure if you are restricted by Heroku in any way. On the shell, as postgres user (or any user with sufficient privileges) first create an empty staging DB:

createdb -T template0 dummy

If the DB isn't too big and you have plenty of disk space, just restore the whole DB for simplicity. Else, selectively restore tables:

pg_restore --dbname=dummy --schema=public --table=foo my_backup

Details in the manual.

Or if it's just 1 or 3 records I would open the (default plain-text) backup with vim or your editor of choice, search manually, copy selected rows (lines in the backup) to a new file my_copy_file.sql. If you are sure about the data you can COPY into existing table directly, rows will be added.
If you need to manipulate data first COPY to a new, empty table. Create it first if it's not there, yet:

CREATE TABLE staging (Like foo);
COPY staging FROM 'path/to/my_productive_db`

Then proceed from there.


No, SQL Server doesn’t keep a history of session activity, imagine how fast that would grow! Your only hopes for retrieving this information from the past, as your question asks, I think, are:

That the app does something that gets logged in the default trace (and those events haven’t rolled away);
Errors that the app might have generated that clearly indicate the server it connected to ended up in the application machine’s event log; or,
Errors that the app might have generated (like deadlocks, I/O warnings, or failed logins) have been captured in the SQL Server error log or the system_health session.

Now, as mentioned in the comments, you could of course set up something to capture this activity in the future, so you don't have to sit around and wait to repeatedly hammer F5 when the app is running. You can use a variety of things, or even a combination, depending on whether you care only about connections, only about write activity, only about access to specific tables or databases, etc.:

Extended Events
logon triggers
DML triggers
SQL Server Audit
server-side trace
Wireshark
maybe even from the plan cache


You have a two-node setup and no witness, meaning there would be only two votes in the quorum. This is why you cannot use automated failover currently--there aren't enough votes, and as such, the remaining node cannot get quorum when the other node is offline.

There are two problems with this setup.

Split Brain

If a network outage occurs, the two nodes can no longer communicate, so without a third vote (file share or disk witness), neither node has a way of knowing which cluster resources the other node has online.  Accordingly, the resources will be taken offline to avoid a "split brain," which is a term used to describe a situation where multiple nodes have the same resource(s) online.

Quorum

In order for a cluster to be considered healthy and online, over 50% of votes must be cast.  In this scenario the moment one node goes offline, the cluster will go offline.  With only two votes available, each vote is worth 50%, so if one node is down or they cannot communicate, neither node can get more than 50%.

Enter the Witness

For the above reasons, you need to utilise a witness (fileshare or disk) to ensure that over 50% of votes are cast when one node goes down, and the cluster remains healthy and online.

In your specific scenario you would need to utilise a file share witness or Azure witness, due to a lack of shared disks in the cluster.

If you are using Windows Server 2012 R2 and SQL 2012, you could also consider using Dynamic Witness rather than a witness.


Your query can be written without subqueries/joins like that:

UPDATE items
   SET description = replace(description, '#', cast(@num:=@num+1 as char(10)))
 WHERE parent_id = 159133
   AND type='photo'
   AND @num := 1       -- @num initialization by Akina
 ORDER BY name ASC
;


Repmgr : It automatically promotes to new master but other standby stopped

I've posted this response on GitHub:
At this point we haven't made any particular provision for repmgr to run in Docker, so it's possible there may be issues of one kind or another.

I also tried adding this
# service_start_command='pg_ctl -D ${PGDATA} start'
# service_stop_command='pg_ctl -D ${PGDATA} stop -m fast'
# service_reload_command='pg_ctl -D ${PGDATA} reload'
#service_restart_command='pg_ctl -D ${PGDATA} restart -m fast'

but same result.

Did you try adding these items without the leading #? I.e.
service_start_command='pg_ctl -D ${PGDATA} start'
service_stop_command='pg_ctl -D ${PGDATA} stop -m fast'
service_reload_command='pg_ctl -D ${PGDATA} reload'
service_restart_command='pg_ctl -D ${PGDATA} restart -m fast'

By default, when restarting a node for a standby follow operation, repmgr will stop then start the server using pg_ctl, as pg_ctl restart has proven to be problematic in some environments. However the opposite might be the case here. Either way we strongly recommend using the OS level service commands where available to avoid issues like this (not sure if those would be available here).


This isn't generally possible but is in the specific case in your question.

Windowed functions are allowed only in steps 5.1 and 6 in the Logical Query Processing flow chart here (The SELECT and ORDER BY).

SELECT cannot be used to reduce the returned rows. In SQL Server 2008 ORDER BY can only do so in conjunction with TOP (For 2012 OFFSET ... FETCH can filter on the basis of ORDER). As you are only interested in ones where the ROW_NUMBER expression evaluates to 1 in this case you can use

SELECT TOP (1) WITH TIES name,
                         age
FROM   mytable
ORDER  BY row_number() OVER (partition BY name ORDER BY age DESC)

SQL Fiddle

Following discussion in the comments about how this could be extended to get the TOP 2 per partition. This would be possible

SELECT TOP (1) WITH TIES name,
                         age
FROM   mytable
ORDER  BY CASE
            WHEN row_number() OVER (partition BY name ORDER BY age DESC) <= 2 THEN 0
            ELSE 1
          END

I personally wouldn't use either of the queries in this answer though. I would just wrap the query in a table expression and filter using WHERE.


Online clustered index rebuild using more log space than expected

After contacting Paul Randal of SQLSkills he informed me that the reason for the high rates of transaction log usage is that in an ONLINE index re/build each row has it's own log record with all the overhead of a log record. An OFFLINE rebuild logs full 8kb page images, not row by row.

Related link provided by Paweł Tajs:

SQL Q&A: The Lore of Logs (also by Paul Randal)


How the transaction logs are maintained in MSSQL RDS?

RDS is closed source and hidden, but assuming that it is like PostgreSQL in that respect:

The PostgreSQL transaction log is managed differently. It is split into segments of 16MB (by default). As soon as the current segment is full, the next one is used. The full transaction log segment is archived and deleted. These archived logs are not managed by PostgreSQL, and you keep them around as long as you want to be able to restore the database.

So it makes no sense to say that the transaction log gets full or that it gets shrunk, all this is not necessary.


Hidden operation of rebooting a SQL server database

As part of database startup following the reboot, the transaction log for every database is read by SQL Server. Committed transactions are rolled forward, uncommitted transactions are rolled back and the previously active portions of the log would be marked inactive and leave most of the space in the log file free. This process is known as recovery and occurs on database startup, which happens as part of SQL Server startup, AG failover, mirroring failover or after restoring a database from backup.

This may have had an impact on performance, but rebooting the servers does so many things that would affect SQL Server performance. The buffer cache is cleared, as is the plan cache, all open connections are disconnected, any running SQL Agent jobs are cancelled, the list goes on.

The transaction log fullness may have been the problem, or at least part of the problem, but consider a scenario where you have encountered poor plan selection due to parameter sniffing issues. This could easily cause performance issues in SQL Server because the cached plan is not optimal for the number of rows returned by a particular execution of a parameterised query.

This issue has now been masked by a reboot because the plan cache has been cleared. If the first execution of that problem query picked the good plan after the reboot, you may not see this issue for some time until a poor plan is cached for this query again.

Tackling this issue long term (unexpected performance degradation) requires some proactive monitoring:

Gather session and performance statistics using either a paid monitoring tool or free tools like sp_Blitz and sp_whoisactive. Log this information to a table for regular analysis. This also ensures you have information available after your client reboots the server.
Gather your wait stats on a regular basis and log them to a table for analysis.
Ask the client to stop rebooting the server. This wipes many of your valuable statistics from in-memory DMVs and makes identifying and resolving the issue more difficult.
Enable the remote DAC. Next time this occurs, you want resources reserved for an administrator to be able to connect and analyse the server to get to the root cause.
Educate your client - high memory utilisation on a SQL Server is not typically a problem. SQL Server wants memory, it provides best performance when it has access to sufficient memory to store more of the regularly accessed data in cache.
If you have SQL Server 2016 or higher, enable Query Store. This will help you identify unexpected query regressions, such as the one described above. It also helps you implement short-term protections such as forcing known good plans for a query.

Also, take a look at this article which is a great resource for developing a performance troubleshooting methodology in SQL Server, and this article which has some great tips as well.


Ref:

Description of the security update for SQL Server 2014 SP2 GDR: January 16, 2018
Description of the security update for SQL Server 2014 SP2 CU10: January 16, 2018
ADV180002 | Guidance to mitigate speculative execution side-channel
vulnerabilities

On January 16, 2018 ADV180002 | Guidance to mitigate speculative execution side-channel vulnerabilities was released as a standalone patch. Build number 12.00.5214 and file version 2014.120.5214.6. This patch was applicable to servers with service pack 2 and no CU installed. Because there are shops who do not want install cumulative updates but still wants to install the security hotfix.

You can see it here.

One the same day the same patch was released as CU10 for those who wanted all cumulative update since service pack 2. Build number 12.00.5571 and file version    2014.120.5571.0.

You can see it here.

  Do I still need to apply meltdown patch for those servers with build
  2014.120.5532.0 or are they covered for meltdown.

Yes if you want to mitigate mitigate speculative execution side-channel vulnerabilities also know as Meltdown and Spectre.  In that case you will be applying CU10 which will include all updates you from 12.00.5532to 12.00.5571. Latest available update is CU14, build versin 12.00.5600.

As a side not MS-136 was also release with a non-CU and CU version.


Performance tuning of Purging table Data in SQL Server 2016

DDL is one of the main ways of speeding up the deletion of records, but seeing that DDL is not an option and you're facing 'hung states' for delete statements, perhaps I suggest looking into deleting records in batches?
Please see link, and here's a query example based on link:
DECLARE @Batch INT = 1000
WHILE 1 = 1
BEGIN
    DELETE TOP (@Batch)
    FROM MyTable
    WHERE Date < ‘20200401’';
    IF @@ROWCOUNT < @Batch BREAK
END

In this case, when we're deleting by Date, make sure you've got an index on Date so that SQL Server can quickly grab just those rows. You'll want to avoid table-level locking.


From DDL:

`date_created` varchar(250) COLLATE utf8_unicode_ci DEFAULT '',

First question - why? Why not DATETIME?

From query:

AND date_created = '2018-08-23'

Second question - are you sure there are records containing '2018-08-23' value in this field DIRECTLY? without timepart?

Recommendations:

Alter datatype of the field to DATETIME and use proper condition for this datatype.

AND date_created BETWEEN '2018-08-23 00:00:00' AND  '2018-08-23 23:59:59'

If not, replace the condition with

AND date_created LIKE '2018-08-23%'


Unlimited everything?

Sure, buy five different physical boxes at $50,000 each hardware alone, use Core-based Enterprise licensing with SA, patch them all, administer them all, and pay annually for the licensing.

Realistically?  In a way you can justify to accounting?

Use a physical box either directly running Windows and SQL Server, or via a hypervisor,

give it plenty of RAM (enough to fit your entire DBs if you can afford it) - if you're stuck with Standard Edition, 128GB is the max (leave some for the OS)
a couple good, fast CPU's
and put your data on fast, reliable enterprise solid state drives - either local or on the SAN.
I do agree; do not put your actual applications on your SQL Server system/guest.

If they step on each other, look at the Resource Governor or separate VMs.  Watch out for licensing costs on the latter!


What is the default sample size of statistics in SQL Server?

How to identify default sample size of statistics?

I would quote from Old Docs article

Auto Update stats Algorithm:
So the Auto Update stats will fire for every 500 + 20% change in table
rows. Of course, we have an improved algorithm in SQL 2012 which is
SQRT(1000 * Table rows) which is much better.
When it fires it will use the  default sampling rate and here is the
algorithm how it calculates the sampling rate.

 If the table < 8MB then it updates the statistics with a

fullscan.

 If the table > 8MB, it follows an algorithm. It reduces the

sampling rate as the number of rows in the table are increased to make
sure we are not scanning too much data. This is not a fixed value but
is under the control of optimizer. It is not a linear algorithm
either.
Example: if we have 1,000,000 rows it would use a sampling rate of 30%
but when the number of rows increase to 8,000,000 it would reduce the
sampling rate to 10%. These sampling rates are not under the DBAs
control but optimizer decides it.

To understand further I suggest you read

Auto Update Stats Default Sampling test By Joe Sack

Sample Size and Duration of Update Stats By Erin Stellato


SQL Server TRY..CATCH resume on terminating error

I've found a solution, not as elegant as I'd hoped, but it works. If someone has something better I'd like to hear it.

The problem with the masterFailProc is it's wrapping a TRY..CATCH block around the entire script, instead of around individual EXEC calls. If the definition is changed as below, then it will happily log errors on a per-exec basis rather than terminating the entire script:

CREATE OR ALTER PROCEDURE masterFailProc
AS
BEGIN
    SET XACT_ABORT OFF;
    BEGIN TRY
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'start';
        BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH;
        BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH;
        BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH;
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'end';
    END TRY
    BEGIN CATCH
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'fatal error';
    END CATCH
END;
GO


Indices can speed up an aggregation when utilized in a $match or a $sort stage at the beginning of a pipeline.

With your example, no index will be used, since you don't use either. So every document will be accessed.


Assuming that there is an index on the table in question, you could try querying sys.dm_db_index_usage_stats.  It's a view so you will need to specify the database and object_ID of the table you are interested in.  You can query at intervals and store the results to get delta over time.  Look at the MSDN page https://msdn.microsoft.com/en-us/library/ms188755(v=sql.100).aspx for how to interpret the information.


If you use InnoDB or a similar storage engine then it should be ACID compliant (ref: https://en.wikipedia.org/wiki/InnoDB). myISAM, the old default and still very commonly used, most definitely isn't ACID compliant. If you mix the two (you might find simpler table types perform better and are acceptable for volatile data that can and will be reproduced again, such as staging tables for ETL processes) then you solution will not be entirely compliant.

A large caveat with ACID compliance is that for performance reasons most databases use an isolation level that does not guarantee the "I" part - this is within the ANSI SQL specs. To offer proper Isolation you need to guarantee that transactions are serialisable, an isolation level that some DBs don't even support. For example MySQL+InnoDB defaults to "repeatable read", while MS SQL Server defaults to the slightly more strict "read committed", both offer "serialisable" but it is not the default. Why isn't is always supported and usually not the default? Performance: a full isolation requirement can significantly limit concurrency.

There are a few good articles on the subject. For one example http://www.bailis.org/blog/when-is-acid-acid-rarely/ is a short and informative place to start with some interesting discussion in the comments.


Heidi SQL 'Cannot close tab with running query'

Oh, dear, another case of a 3rd party software getting in the way.

Get a command window.
Open the "mysql" commandline tool.
SHOW PROCESSLIST;, identify the pid for the naughty query.
kill 1234; (or whatever the pid number is).

(If you were running the original query from "mysql", a simple CTRL-C would terminate it.  The above stuff would not be needed.)


MySQL query for 3 consecutive integers between records

SELECT DISTINCT
t.id
FROM tablename t
WHERE EXISTS (
    SELECT 1 FROM tablename x
    WHERE x.id = t.id
    AND x.year = t.year+1
)
AND EXISTS (
    SELECT 1 FROM tablename x
    WHERE x.id = t.id
    AND x.year = t.year-1
)


Should the host machine always be restarted after upgrading an SQL Server 2008 instance?

There can be some memory usage downgrade, especially if Sql Server uses Large Page Extensions, otherwise - all the issues are minimal and negligible.


State of the art self healing and self protection in DBMS

SQL Server has Automatic Page Repair During a Database Mirroring Session in 2008+. Is this the sort of thing you're looking for?

A database mirroring partner tries to automatically recover from
corrupted pages on the mirror database by resolving certain types of
errors that prevent reading a data page. The partner that is unable to
read a page requests a fresh copy from the other partner. If this
request succeeds, the unreadable page is replaced by the copy, which
usually resolves the error.

I guess you could include the repair mechanisms of DBCC CHECKDB under the self-healing banner and Page Checksums under self-protection. Clustering, mirroring and the new HADRON features in SQL Server 2012 are arguably relevant topics also.


MSSQL Mirror Configuration Troubleshooting: Witness server forcibly disconnecting every 100 seconds

We reported the issue to Microsoft support and got it fixed. I have also tested SP1 CU1 and the database mirroring error messages have disappeared. So I think that all is good.
https://support.microsoft.com/en-us/help/3210699/fix-error-messages-are-logged-if-database-mirroring-is-configured-by-transact-sql-in-sql-server-2016-and-no-database-activity-occurs-for-more-than-90-seconds


Why are Op Stats all 0's and yet Usage Stats show 29 seeks

Usage statistics come from sys.dm_db_index_usage_stats, which tracks the number of execution plans that include an operator touching that index. It's reset on SQL Server service restart, or when the index is modified.

Operational statistics come from sys.dm_db_index_operational_stats, which track the number of times the index has actually been touched. It's reset on a different schedule - when that object's metadata disappears from cache.


Transpose multiple columns into 2 column and single row to multiple row in sql

There's a T-SQL construct called UNPIVOT which is designed for this. But in my experience, you'll have more luck with APPLY as per my code below:

SELECT *
FROM YourTable t
CROSS APPLY
    (SELECT 'Col1', Column1
    UNION ALL
    SELECT 'Col2', Column2
    UNION ALL
    SELECT 'Col3', Column3
    UNION ALL
    SELECT 'Col4', Column4
   ) u (columnName, Value)
  ;


Replication of specific tables using Postgres 9.4.4 and Amazon Web Services

According to the AWS Documentation, logical replication is possible from RDS Postgres instances, starting with version 9.4.9.  That would solve the RDS -> local direction.

The documentation doesn't make it very clear, but it seems that from version 10.4 an RDS instance can even be the receiver end (the subscriber) of logical replication:

  Beginning with PostgreSQL version 10.4, RDS supports the publication and subscription SQL Syntax for PostgreSQL 10 Logical Replication.


Most Efficient Strategy to Insert Many Records

As a programmer, your time is valuable too. For a daily load process I would start with something as simple as possible and only make it more complicated if you know that you need to improve performance. I don't know your exact requirements, but for a load that happens once per day you probably don't need your code to finish in seconds.

You have six transaction tables, so you can do a small proof of concept for just one table. I'm going to use T_TX_CUST as an example.

Load new data from your customer data source into the customer table
Load new data for T_TX_CUST into a temporary staging table.
Use the staging table to find and insert any missing customers into the customer table.
Load new data from staging into T_TX_CUST.

The advantage of this process is that it's simple and it doesn't matter how much data you already have in the T_TX_CUST table. All that matters is how much data you load per day. Try it out, and if it meets your needs then implement it for all of your tables. One trick to speed things up is to run multiple queries concurrently. For example, you may be able to load multiple staging tables at once or to load data into multiple transaction tables at once.


Does a subscriber node in pglogical "pull", or are updates "pushed"?

The subscribers initiate the TCP connection to the publisher.


How to select name by 2 fields in another 2 tables in mySQL

as correctly mentioned in comments - LEFT JOIN not proper choice in Your case, even if You fix the syntax (JOIN require ON condition)

You need use INNER JOIN:

SELECT
     CONCAT(s1.firstname,s1.lastname) as full_name
FROM students s1
INNER JOIN registration r1 ON s1.student_id = r1.student_id
INNER JOIN courses c1 ON c1.course_code = r1.course_code
WHERE c1.course_name LIKE '%Structure%';

or You need swap tables

SELECT
     CONCAT(s1.firstname,s1.lastname) as full_name
FROM courses c1
LEFT JOIN registration r1 ON c1.course_code = r1.course_code
LEFT JOIN students s1 ON s1.student_id = r1.student_id
WHERE c1.course_name LIKE '%Structure%';

as You can see LIKE - must have % from both ends for found what You are looking for


Can I limit the time a table lock is being held server side?

You can explicitly specify using :

SET LOCK_TIMEOUT timeout_period

  timeout_period

  Is the number of milliseconds that will pass before Microsoft SQL Server returns a locking error. A value of -1 (default) indicates no time-out period (that is, wait forever).
  When a wait for a lock exceeds the time-out value, an error is returned. A value of 0 means to not wait at all and return a message as soon as a lock is encountered.

  At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection.

  The setting of SET LOCK_TIMEOUT is set at execute or run time and not at parse time.

  The READPAST locking hint provides an alternative to this SET option.

  CREATE DATABASE, ALTER DATABASE, and DROP DATABASE statements do not honor the SET LOCK_TIMEOUT setting.

Refer to : Support for SET statements in Azure

UPDATE:

  Is it somehow possible to tell SQL Server that the lock should not be held for more than say one minute so that it is unconditionally released after that time?

No. As I mentioned in the comments section.

THe database wont be able to maintain its ACID properties.

Either the query fails and returns an error or the lock is held by SQL Server  until the transaction is completed and then released.


Can only specific SQL users be forced to use TLS?

The option for forcing encryption from the server side is either all or nothing.  There is no way on the server side to configure it so that it only forces certain logins or connections to encrypt.

Clients, however, can be configured to either encrypt the connection or not.  For example, adding encrypt=true to a Microsoft JDBC connection string, or SSL=require   to a jTDS connection string will configure that client connection to be encrypted.  If you're using an ODBC DSN, the configuration typically has an option for encryption as well.  You should be able to quickly learn how to configure a given SQL client for encryption by doing a bit of searching on the Internet.

One note of caution is that you may need to update SQL Server and the SQL client that is being used if you are forcing TLS 1.2 (see TLS 1.2 support for Microsoft SQL Server).  The latest release of the jTDS driver has a defect that causes it to fail with TLS 1.2, but there is a developer build available in the forums with a fix.  The last official release of jTDS was in 2013, so you'll have to use an unreleased build if you'll be forcing TLS 1.2.


Any Reason to have Regular Failover Clustering in Azure Cloud?

Is there a reason to have Regular SQL Failover Clustering when database is in Azure Cloud?

This completely depends on your use cases, knowledgebase, requirements, and cost.

In some ways it would make sense to use S2D and setup an FCI over an AG, most notably the storage costs for high perf disk can be larger. Since each node in an AG has a whole copy of the database, it may inflate costs or may not be required to have multiple separate copies in a single site.

  People can transition heavy Read queries to the secondary replica (to prevent locking-blocking, tempdb issues), offload maintenance tasks, etc.

Be very careful with this wording, as there can still be redo blocking, ghost record cleanup, version store blowing out tempdb, and no maintenance (except checkdb and backups) can be done on the database. This will also incur extra I/O charges for the reads/writes and also for the base disk usage in Azure whereas the FCI would not.

  Don't Cloud Services inherently offer server level protection by itself? Don't Cloud Services inherently offer server level protection by itself?

Not to argue the point, but as the comments have said there can be issues with the physical location/hardware/connection that the "cloud" is running, so just by having it there doesn't mean you get to walk away without a care in the world. In Azure, for example there are different domains to make sure servers aren't taken down all at the same time for things like patching, which is an example of something that still takes place even in the cloud and isn't indicative of a failure or issue (such as hardware lost/down/etc.).

There are different levels of offerings, some are IaaS (such as this) and some are PaaS (such as AzureDB). In IaaS offerings it is basically you running your own VMs, whether in the cloud or in some datacenter you own the same applies. If this question were to be geared towards a PaaS item, then it would be incumbent upon the PaaS team to do this as you the end user won't have control over this, by choosing IaaS you're taking control over this.

By using IaaS you're responsible for configuring and managing the resources to have uptime and availability, thus it is incumbent upon you for IaaS to choose the correct cloud, OS, and software settings/configurations. Some may prove to be more expensive, tedious, etc., than others.


Does write stalls on separated MDF file affect performance

The occasional disk write of 20ms or more on a volume hosting a database file is not normally a big deal.
The server-level Recovery Interval, or the database-level Target Recovery Time (aka Indirect Checkpoints, and the default for SQL 2016+) can trigger aggressive flushing, which can increase the total write IO cost of a workload by eliminating the write-coalescing behavior of the page cache.
If you set the Target Recovery Time and the "recovery writer" starts to fall behind, it will hijack your schedulers to help it catch up.  This will create "backpressure" to reduce your workload throughput to maintain your Target Recovery Time.  See, eg:

" If recovery writer starts falling behind resulting into long
DPLists, the individual worker threads running on the scheduler starts
playing the role of recovery writer to collect, sort and post write
for dirty pages. This allows recovery writer to scale dynamically on
heavy OLTP systems to ensure target_recovery_time interval set is met
all the time. While multiple recovery writer routine running
aggressively allows us to meet the target_recovery_time for
predictable recovery, the overall throughput of workload may degrade
under these circumstances due to additional overhead caused from each
running worker performing recovery writer activity."

Indirect Checkpoint and tempdb – the good, the bad and the non-yielding scheduler
Also if your workload requires data file reads, these will contend with the writes for IO.


How to design sales model for escrow type payment process

I find with financial transactions you want to have a detailed audit trail of every movement of funds.  This means a write-once (no updates) table containing fund movements using a double entry accounting model.

In a simple version of this, you would have one table containing the list of payees and payers (your senders, administrators and receivers).  Then you would have another table (or better two) that contain the financial transactions.  The tables are roughly as follows:

PARTY: Name, address, bank account, other details about payers/payees.
TRANSACTION: Date, transaction ID, order reference, other general info.
TRANSACTION-DETAIL: FK to Transaction, FK to party, Amount.

When someone makes a simple payment you record one TRANSACTION and two TRANSACTION-DETAIL records.  The first record shows who paid and the second shows who was paid.  The sum of the amounts for the two details must be zero, that means you need to pick a convention for positive and negative Amounts such that one means giving money and the other means getting money.

If the transaction is more complicated, you just add more TRANSACTION-DETAIL records, for example instead of one person getting paid, you get two people being paid (administrator and seller for example).

While the sum of transaction detail amounts for any transaction is zero, the sum of amounts for any given PARTY may be non-zero, meaning you have some of their money in your system.  To clear these amounts, you need a PARTY representing any payment settlement organization or bank that you deal with.  These are the ultimate sources and sinks of funds and their balances don't need to be zero in the long run.

All of this may seem complicated, especially compared to what you were thinking of, but the advantage of it is that it allows you to keep a detailed record of exactly what happened and when it happened.  If you need to make an update, i.e. cancel and order or issue a credit note, just add more transactions and transaction details.  You can always go back and see exactly what went on.  This convention is how people have been handling and accounting for financial transactions for hundreds of years, so if nothing else, people will understand what you're doing.

Here is an example with some data.  In this example, Buyer X pays 100.00 for an order.  The Agency collects 10.00 and pays Seller 1 40.00 and Seller 2 50.00.  They buyer pays with PayPal and the Agency and both Sellers deposit their money in one of two banks.  Note that this example is quite simple insofar as the agency and the sellers clear out their accounts after just one order.  In a real-world scenario, payments for many orders might be batched up before settlement is made.

Note that for OP's scenario where determination of the seller may be delayed, the agency would need to have some kind of escrow account.  This would be an additional party, such as "Outstanding Order Payments".  In such a case the agency could take their fee right away and the balance would be placed in the escrow account.  When the sellers are determined later on, they would be paid out of the escrow account, rather than out of the buyer's account, as is shown in the example, above.

Also note that while each transaction is shown with two details above, it is also legitimate to have three or more details for one transaction if you happen to want to record it that way.  For example, all of the details in 8001, 8002 and 8003 could have been represented as a single transaction if you prefer.


MSSQL 2000 replication, incrementing an integer column on a single row using an update statement

You must either define identity ranges for each server (subscriber), but unsure if this was possible with SQL Server 2000. A more simple approach is to use GUID/uniqueidentifers for IDs, then you avoid conflicts.


Identify the version of a collation from ICU in Postgres

TL;DR

Check out the pg_collation_actual_version() function.

DETAILS

The PostgreSQL documentation for ALTER COLLATION seems to discuss this issue in the "Notes" section:

  When using collations provided by the ICU library, the ICU-specific version of the collator is recorded in the system catalog when the collation object is created. When the collation is used, the current version is checked against the recorded version, and a warning is issued when there is a mismatch...

  A change in collation definitions can lead to corrupt indexes and other problems because the database system relies on stored objects having a certain sort order. ...

  The following query can be used to identify all collations in the current database that need to be refreshed and the objects that depend on them:

SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
       pg_describe_object(classid, objid, objsubid) AS "Object"
  FROM pg_depend d JOIN pg_collation c
       ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
  WHERE c.collversion <> pg_collation_actual_version(c.oid)
  ORDER BY 1, 2;

You will notice that the given query makes use of the pg_collation_actual_version() function, which is defined as:

  pg_collation_actual_version returns the actual version of the collation object as it is currently installed in the operating system. If this is different from the value in pg_collation.collversion, then objects depending on the collation might need to be rebuilt.


num_rows in dba_tables not reflecting the real number of rows in tables Oracle 9i

It's an estimate. See http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2105.htm#i1592091.
If you run the following :

exec dbms_stats.gather_schema_stats('owner');

within Oracle SQL*Plus. You'll get then the exact value of row numbers (until subsequent inserts / deletes change the actual row count so it no longer matches the one gathered).

If you need a precise value you'll need to use SELECT count(*) ...


Have a separate database for data warehousing?

In response to your reply to my comment, I mentioned some of the areas in which PostgreSQL shines for DW work - notably CTEs (Common Table Expressions - AKA the WITH clause) and Window functions (AKA the OVER clause).

Without these in MySQL, you'll end up writing your own hacks to emulate these (and, no disrespect, but it'll take a while for your code to be bug free), whereas with PostgreSQL you get them out of the box. PostgreSQL also has superior GIS support (should you require it) and better JSON - only experimental in MySQL.

Take a look here

  PostgreSQL has traditionally focused on reliability, data-integrity
  and integrated developer-focused features. It has an extremely
  sophisticated query planner, which is capable of joining relatively
  large numbers of tables efficiently.

  MySQL, on the other hand, has traditionally focused on read-mostly web
  apps, usually written in PHP, where the principal concern is with
  optimising simple queries.

DW type apps need far more of the former than the latter.

Also, check this out - a very recent comparion of the two systems. My take on this is that PostgreSQL is the better of the two, esp. for DW work.

You could peruse these pages for PostgreSQL and MySQL features. There is a certain "religious war" element here, and I'm mindful of the fact that you already have MySQL experience which may be a clincher for you. I wish you all the best with your DW project.

[EDIT in response to OP's comment]

I would certainly urge you to have separate databases for your OLTP and your OLAP work - if that is within your budget. OLTP and OLAP are fundamentally different and if you mix them you'll have many conflicts - I know the pain caused by "managers" insisting on running reports against a live system during the day - so if I were you, I would have MySQL as my OLTP system and PostgreSQL as my OLAP.

But whatever configuration you choose, I strongly suggest separate servers.

If I were forced to choose one database and one only, it would be PostgreSQL without hesitation. MySQL was essentially lucky and in the right place at the right time with the right software during the first internet boom. IMHO, PostgreSQL is undoubtedly technically superior.

I bear MySQL no ill-will - I have used it a lot (clients!) and find that if you can live with or workaround its quirks, it can be quite performant and has some interesting features - notably the storage engine choices one can make (which one can't with PostgreSQL).

In the DW sphere for MySQL there are two interesting developments which may bode well for it in this area in the future - these are columnar store engines - Infinidb and Infobright. The problem at the moment is that Infinidb appears to have died - even though the code is still available, the installation guide has disappeared and ICE (Infobright Community Edition) is essentially crippleware. You may wish to evaluate the Entreprise edition.

I urge you to set up a few test cases - imagine what sort of reports you will be running and try them on both systems. Think especially about the complex reports required for OLAP systems - I believe that you will find the far richer PostgreSQL SQL language to be a persuasive argument in its favour.


Azure Managed Instance - Is it possible to send Sql Logs To Log Analytics?

Use the process described in Add Azure Monitor solutions from the Solutions Gallery to add the Azure SQL Analytics (Preview) solution to your Log Analytics workspace.

Once you have created Azure SQL Analytics solution in your workspace, you need to configure the Managed Instance that you want to monitor to stream its diagnostics telemetry to the solution. Follow detailed instructions on this page.

When you add the solution to your workspace, the Azure SQL Analytics tile is added to your workspace, and it appears in Overview. Select View Summary link to load the tile content

Once loaded, the tile shows the number of Azure SQL databases, elastic pools, Managed Instances, and databases in Managed instances that the solution is receiving diagnostics telemetry from.

The solution provides two separate views -- one for monitoring Azure SQL Databases and elastic pools, and the other view for monitoring Managed Instance, and databases in Managed Instances.

To view Azure SQL Analytics monitoring dashboard for Azure SQL Databases and elastic pools, click on the upper part of the tile. To view Azure SQL Analytics monitoring dashboard for Managed Instance, and databases in Managed Instance, click on the lower part of the tile.

Once the Azure SQL Analytics tile for the databases is selected, the monitoring dashboard is shown.


Database index fragmentation and missing rows are not related. Fragmentation is mismatch in ordering of physical pages and clustering key. A fragmentation can never cause rows to go "missing".
No a database without index is NOT prone to corruption. Index just makes things faster period.


Increase AUTO_INCREMENT value in phpMyAdmin

If your auto increment column is of type UNSIGNED SMALLINT, then you'll have the 65535 limit.
Changing the column type to like UNSIGNED INT should allow you up to 4294967295.
You can use  UNSIGNED BIGINT if you need anything bigger.

If this does not solve your problem, can you post your table structure, which you can get with the statement: show create table <table name>


How to get last level of a hierarchical list, group by some field

This code shows how to use a recursive CTE, to return the results you're looking for.

IF OBJECT_ID('dbo.OrdersItems') IS NOT NULL DROP TABLE dbo.OrdersItems;
IF OBJECT_ID('dbo.Containers') IS NOT NULL DROP TABLE dbo.Containers;

CREATE TABLE dbo.Containers
(
    ContainerID int NOT NULL
        CONSTRAINT PK_containers
        PRIMARY KEY CLUSTERED
    , ContainerName text NOT NULL
    , ProductID int NOT NULL
    , Lot int NULL
    , Quantity int NOT NULL
    , ParentContainerID int NULL
        CONSTRAINT FK_Containers_ContainerID
        FOREIGN KEY
        REFERENCES dbo.Containers (ContainerID)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
);

CREATE TABLE dbo.OrdersItems
(
      OrderID INT NOT NULL
    , Position int NOT NULL
    , ContainerID int NULL
        CONSTRAINT FK_OrdersItems_ContainerID
        FOREIGN KEY
        REFERENCES dbo.Containers (ContainerID)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
    , CONSTRAINT PK_OrdersItems
        PRIMARY KEY CLUSTERED (OrderID, Position)
);

Insert the sample data:

INSERT INTO dbo.Containers (ContainerID, ContainerName, ProductID
                             , Lot, Quantity, ParentContainerID)
VALUES
      (23, 'Box 40',  2, null, 40,  null)
    , (16, 'Pallet',  1, null, 120, null)
    , (12, 'Bag 20',  1, null, 20,  14)
    , (13, 'Bag 20',  1, null, 20,  14)
    , (14, 'Box 40',  1, null, 40,  16)
    , (19, 'Bag 20',  2, null, 20,  23)
    , (22, 'Bag 20',  2, null, 20,  23)
    , (5,  'Bag 20',  1, null, 20,  7)
    , (6,  'Bag 20',  1, null, 20,  7)
    , (7,  'Box 40',  1, null, 40,  16)
    , (1,  'Bag 10',  1, 500,  10,  5)
    , (2,  'Bag 10',  1, 501,  10,  5)
    , (3,  'Bag 10',  1, 502,  10,  6)
    , (4,  'Bag 10',  1, 500,  10,  6)
    , (8,  'Bag 10',  1, 600,  10,  12)
    , (9,  'Bag 10',  1, 601,  10,  12)
    , (10, 'Bag 10',  1, 502,  10,  13)
    , (11, 'Bag 10',  1, 501,  10,  13)
    , (15, 'Box 40',  1, 600,  40,  16)
    , (17, 'Bag 10',  2, 700,  10,  19)
    , (18, 'Bag 10',  2, 701,  10,  19)
    , (20, 'Bag 10',  2, 703,  10,  22)
    , (21, 'Bag 10',  2, 701,  10,  22);

INSERT INTO dbo.OrdersItems (OrderID, Position, ContainerID)
VALUES (1, 1, 16)
    , (1, 2, 23);

Here's the recursive CTE:

;WITH RecursiveCTE AS
(
    SELECT c1.ContainerID
        , c1.ContainerName
        , c1.Lot
        , c1.ParentContainerID
        , c1.ProductID
        , c1.Quantity
        , Level = 1
    FROM dbo.Containers c1
    WHERE c1.ParentContainerID IS NULL
    UNION ALL
    SELECT c2.ContainerID
        , c2.ContainerName
        , c2.Lot
        , c2.ParentContainerID
        , c2.ProductID
        , c2.Quantity
        , Level = RecursiveCTE.Level + 1
    FROM dbo.Containers c2
        INNER JOIN RecursiveCTE ON RecursiveCTE.ContainerID = c2.ParentContainerID
)
SELECT r.ProductID
    , r.Lot
    , QuantityTotal = SUM(r.Quantity)
FROM RecursiveCTE r
WHERE r.Lot IS NOT NULL
    AND r.Level > 1
GROUP BY r.ProductID
    , r.Lot;

The results:

ProductID | Lot | QuantityTotal
--------: | --: | ------------:
        1 | 500 |            20
        1 | 501 |            20
        1 | 502 |            20
        1 | 600 |            50
        1 | 601 |            10
        2 | 700 |            10
        2 | 701 |            20
        2 | 703 |            10

As you can tell from the above code, I've renamed some of your columns to more clearly reflect their contents, and have added some minor formatting, along with foreign-key references.

db<>fiddle here


Pass datatable as parameter in PostgreSQL

I would never use these objects. They simply don't seem that useful. That said, PostgreSQL does have a method of passing a table to a function via jsonb. Likely that would be the ideal solution for you if you're looking to get the functionality described in Passing a Table-Valued Parameter to a Stored Procedure. All you'd have to do is map the DataTable to JSON, and then pass the JSON to the function.

To convert a Datatable to JSON you can use JSON.net. See also these Stack Overflow answers.

How to convert datatable to json string using json.net?
Convert datatable to JSON in C#

If the Datatable is too big to serialize to JSON, you may have to create a Foreign Data Wrapper around something that exports the DataRow objects, this would start you down the path mentioned in "Streaming Rows with a DataReader". Though if you don't need a server/client model, you can always dump the DataTable to CSV and read it with a Foreign Data Wrapper: to go down that route check out file-fdw.

I actually tried this. I ran into a ton of problems.

It wasn't even easy figuring out how to install a package with nuGet,
Having installed nuGet, despite being the #1 package on nuGet, it doesn't yet work with .NET Core 2.x
Installing the beta version of nuGet, I got it to work.

Converting a DataTable to JSON with .NET Core 2.x,

using System;
using System.Data;
using Newtonsoft.Json;

class Program
{
  static void Main()
  {
    // Get the DataTable.
    DataTable table = GetTable();
    // ... Use the DataTable here with SQL.
    string json = JsonConvert.SerializeObject(table, Formatting.Indented);
    Console.WriteLine(json);

  }

  static DataTable GetTable()
  {
    // Here we create a DataTable with four columns.
    DataTable table = new DataTable();
    table.Columns.Add("Dosage", typeof(int));
    table.Columns.Add("Drug", typeof(string));
    table.Columns.Add("Patient", typeof(string));
    table.Columns.Add("Date", typeof(DateTime));

    // Here we add five DataRows.
    table.Rows.Add(25, "Indocin", "David", DateTime.Now);
    table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
    table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
    table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
    table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);

    return table;
  }
}

You'd still have to connect to the db and drop the json into an INSERT statement, but that should be easily done.

cmd.CommandText = "SELECT * FROM myfunc(@json)";
cmd.Parameters.AddWithValue("json", json);

Define myfunc(jsonb) on the server, and you should be good: you've passed a DataTable into PostgreSQL.


PostgreSQL - Flatten nested JSONB object array

Set returning functions should be called in the FROM.
SELECT mt.measurement_id,
       p.port ->> 'port_name' as "port_name",
       p.port ->> 'port_description' as "port_description",
       p.port ->> 'port_status_int' as "port_status_int",
       p.port ->> 'port_status' as "port_status",
       p.port ->> 'port_speed_int' as "port_speed_int",
       p.port ->> 'ports_speed' as "ports_speed"
from mytable mt
  cross join jsonb_array_elements(mt.data->'ports') as p(port)
where device_id = 20;

You can simplify this a bit, by using jsonb_to_recordset:
SELECT  mt.measurement_id,
        p.*
from mytable mt
  cross join jsonb_to_recordset(mt.data->'ports') as p(port_name text, port_description text, port_status_int int, port_status text, port_speed_int int, ports_speed text)
where device_id = 20;


From the documentation:

  In the SQL standard, the optional key word AS can be omitted before an
  output column name whenever the new column name is a valid column name
  (that is, not the same as any reserved keyword). PostgreSQL is
  slightly more restrictive: AS is required if the new column name
  matches any keyword at all, reserved or not

cost is a keyword, so as is required.

Also:

  Recommended practice is to use AS or double-quote output column names,
  to prevent any possible conflict against future keyword additions.

... but my preference is to not quote & instead be consistent with using as.


How does partitioning and the lack of a primary key affect ON DUPLICATE KEY UPDATE

When we talk about partitioning tables in relational database platforms is recommended that it is always done on tables where the field is numeric. As a suggestion I would create an additional field that would serve as a surrogate key and this in turn would be used for partitioning the table.


You can create the following tables:

  Player, League, Match(with FK to league), Round(with FK to Match), Shot(with FK to round & Player)

And the following N:N table

  PlayerLeague(a player subscribed to the league)

if the situation exists that a player  can subscribe to a league but doesn't participate in some matches, then another N:N table will be needed so you know to what matches the player has subscribed to

you can then sort the results by a query like

select
 P.PlayerName,
 sum(S.Score),
 avg(S.Score)
from Shot S
inner join Player on P on S.PlayerID=P.PlayerID
group by P.PlayerName
order by sum(S.Score) desc

you can add extra joins/groupby's if you want to have a more detailed result grouped on league/match


You can use the extended stored proc "xp_dirtree".  Store the results in a temp table and get the rowcount.  Zero = not exists.

create table #Files(
subdirectory nvarchar(512),
depth int)

insert into #Files
exec xp_dirtree 'c:\windowsX'

select @@ROWCOUNT

drop table #Files


Can vmware (virtualisation) configuration impact the data integrity of the data file

Your question doesn't contain many details so I'll treat it as a hypothetical question.

Combining data and log files, or any other type of files shouldn't lead to corruption in .vmdk's any more than it would on a normal disk.

You haven't specified which RDBMS or OS you are running, and maybe some obscure systems exist where combining data and log files on the same drive causes the files to become corrupted but I would consider that a bug in the software and it would happen just as well with physical drives.

The VMWare knowledge base contains some documented corruption issues, but none of them have anything to do with combining certain types of files or write operations on the same vmdk.

If you have actual corruption things that come to mind are issues with vmotion or old veaam versions in combination with large backup sizes, but not combining certain files.


Use one big table or multiple small tables for described scenario

You definitely should not have one table per device. That means you'll have to create dynamic SQL to merge the device name into the query, and create a new table every time you add a device. So version 2 is much more appropriate than version 1.

But you don't necessarily need separate tables for voltage and current. You could have a single table device_log, with columns:

id - primary key
sub_device_id - foreign key to sub_devices table
timestamp
type - 'voltage' or 'current'
value

Or if you don't need so much generality, and always read current and voltage at the same times:

id - primary key
sub_device_id - foreign key to sub_devices table
timestamp
voltage
current


How to create function with Return Type as (TABLE student_name string)

Either by using returns table

create function get_students()
 returns table(applicant_id integer, ship_id integer)
as
$$
  select applicant_id, ship_id
  from test.students
$$
language sql;

create function get_student_names()
 returns table(student_name text)
as
$$
  select name
  from test.students
$$
language sql;

Or by using setof

create function get_students
 returns setof students
as
$$
  select *
  from test.students
$$
language sql;


No, there is no way to rename an Extended Events session without dropping and recreating it.  The NAME property is not part of the ALTER EVENT SESSION syntax, nor is it supported by sp_rename.  Simply use SSMS to script and recreate the session easily.  You don't lose anything by doing this.


Script out objects that depend on in SQL Server

In SSMS...

Rt Click on the Database in question, navigate to Tasks and then Generate Scripts...
Select Choose Objects on the left and then click the radio button to Select specific database objects
Select the object you wish to script
In Save to file click the Advanced button
6th option down, set Generate Script for Dependent Objects to true
Set any other options you want and click the OK button
Set the File name and path or Save to Clipboard or Save to new query window
Click the Next buttons to finish.


Have a single User table.  Then if anything changes, such as a phone number, it changes for the lender and the borrower simultaneously without awkward reconciliation between the two tables.

Your Loan table will look something like this

Loan
----
LenderID        -- A foreign key pointing to User
BorrowerID      -- Also a foreign key pointing to User
ItemID

Then create a constraint on the table to ensure LenderID <> BorrowerID.  You don't say which RDBMS you're using but many of the major ones support this.  If anyone tries to lend an item to himself the DBMS will throw an error and refuse to store the data.


Your only recourse would be to activate the slow log and use it as a MyISAM table. By default, the slow log would normally be a text file. However, a general_log table was provided in newer release of MySQL as a CSV table.

mysql> use mysql
Database changed
mysql> show create table slow_log\G
*************************** 1. row ***************************
       Table: slow_log
Create Table: CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  KEY `start_time` (`start_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.03 sec)

mysql>

Make sure you have this in my.cnf and then restart mysql if you had to add this:

[mysqld]
log-output=TABLE
log-slow-queries
long-query-time=0.00001

Next, convert the table to MyISAM and index it on the needed columns:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log ENGINE = MyISAM;
ALTER TABLE mysql.slow_log ADD INDEX starttime_index (start_time);
ALTER TABLE mysql.slow_log ADD INDEX db_starttime_index (db,start_time);
SET GLOBAL slow_query_log = @old_log_state;
SHOW CREATE TABLE mysql.slow_log\G

You can query the table and retrieve the user and host from mysql.slow_log column user_host.

You can crontab some tasks to copy the data from mysql.slow_log where you need it, may a separate database.

CREATE DATABASE IF NOT EXISTS mystatus;

Perhaps, you can run queries like this:

SET @old_log_state = @@global.slow_query_log;
SET GLOBAL slow_query_log = 'OFF';
ALTER TABLE mysql.slow_log RENAME mystats.slow_log;
CREATE TABLE mysql.slow_log LIKE mystats.slow_log;
SET GLOBAL slow_query_log = @old_log_state;

This will move the slow log elsewhere and start with an empty one.

This may not be a full answer, but I hope this is useful to you as to some direction to take.


How to take grade letter from marks range from grade table?

This will do the trick (assuming your marks are in the table Marks):

SELECT      B.grade_letter
FROM        Marks AS A
INNER JOIN  Grade AS B
    ON      A.Mark BETWEEN B.lowerlimit AND B.upperlimit


I assume you have a Basicfile LOB, because shrink is not supported with Securefile LOBs. The segment is not being moved, so the default tablespace and the tablespace of the segment is not relevant now. So a simple test is:

SQL> create table t1(c1 clob) lob (c1) store as basicfile;

Table created.

SQL> insert into t1 select lpad('X', 4000) from dual connect by level<=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes/1024/1024 from user_segments where segment_name =
     (select segment_name from user_lobs where table_name = 'T1' and column_name = 'C1');

BYTES/1024/1024
---------------
             80

SQL> alter table t1 enable row movement;

Table altered.

SQL> delete from t1 where rownum <= 5000;

5000 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table t1 modify lob (c1) (shrink space);

Table altered.

And the LOB segment became half the size after the shrink operation:

SQL> select bytes/1024/1024 from user_segments where segment_name =
     (select segment_name from user_lobs where table_name = 'T1' and column_name = 'C1');

BYTES/1024/1024
---------------
        39.8125


Why query execute faster after first execution

No, you cannot optimize a query to run as fast on the first run as on the second.  This is because during the first run the database caches information in the system.  The second time it is run, if the information is still in the cache, then the database will reuse that info to make the second query faster.


Equivalent of Oracle RR date format in SQL Server

My question:

  Is there a way to express RR in T-SQL?

It can be done, for example, as

-- from date to string
SELECT FORMAT(datetime_field, CASE WHEN YEAR(datetimefield) > 49
                                   THEN 'yyyy-MM-dd'
                                   ELSE 'yy-MM-dd'
                                   END) AS date_formatted_as_RR, ...

-- from string to date
SELECT DATEFROMPARTS(CASE WHEN CAST(SUBSTRING(string_field,7,2) AS INT)>49
                          THEN CAST(SUBSTRING(string_field,7,2) AS INT)+2000
                          ELSE CAST(SUBSTRING(string_field,7,2) AS INT)+2100
                          END,
                     CAST(SUBSTRING(string_field,4,2) AS INT),
                     CAST(SUBSTRING(string_field,1,2) AS INT)) AS date_interpreted_as_RR, ...


How to display True & False for 1 & NULL

SQL Server does not have the Boolean data type. There are no built-in values true and false. One alternative is to use strings 'true' and 'false', but these are strings just like any other string. Often the bit type is used instead of Boolean as it can only have values 1 and 0. Typically 1 is used for "true" and 0 for "false". It is still an integer type, though. Boolean logic will not work. For example, if (CONVERT(bit, 1) and CONVERT(bit, 1))... is not legal syntax.

Likely the best option is to use a CASE and return the requisite strings, as @Akina commented:

CASE
    WHEN TheColumn = 1 THEN 'True'
    WHEN TheColumn IS NULL THEN 'False'
    ELSE 'Oops!'
END


Why would  running ALTER TABLE  on a  column cause it to go from NOT NULL to NULL?

It depends on your setting of SET ANSI_NULL_DFLT_OFF or SET ANSI_NULL_DFLT_ON (read these for other effects too from SET ANSI_DEFAULTS).

Personally, I'd have specified this constraint explicitly if I wanted null or non null columns rather than relying on environment settings.

ALTER TABLE [myTable] ALTER COLUMN [columnA] INT NOT NULL

You can issue this command again and if a column is already at the desired nullability the command is ignored.

However, you also said that you only need to change it where it's part of a primary key. That should have no bearing on whether a column can be nullable or not from the modelling/implementation phase


SQL Server - RangeX-X and RangeI-N locks

I have figured this on my own and I wrote on my blog. For those interested in the solution visit this posts: RangeS-S, RangeS-U, RangeX-X
NB: The links above have been modified to point to archive.org because the site is no longer valid. It's unfortunate. Also, the content of the blogs is quite extensive or I would try to capture some of that data to here. It's just too much for one post. ~ jcolebrand PS: Don't forget to throw a few dollars at the archive.org folks if you follow these links.


Preparing a cluster installation fails at Setup Support Rules Step (Not Clustered or the cluster service is up and online)

So, finally after more investigation I found the right post explaining my problem and the solution.

It turns out the article was the one that dco pointed out. The thing is this: the problem I was experiencing was not solved with the slipstreaming technique (the install with SP2) but with other thing mentioned there further down the road (Issue 2: The "Cluster Service verification" rule fails when you try to install a SQL Server 2008 R2 failover cluster instance).

Turns out I had to enable the "Failover Cluster Automation Server" Feature in my Windows 2012 R2 boxes.

Thanks everybody for your help on this matter, especially dco for pointing me to that website. Since the resolution that dco offered (slipstreaming with SP 3) was not the solution to my problem I did not accepted that one as the answer (so that future searches with other people looking to fix this same problem don't end up on a wild goose chase).


How to set Proxy Settings when installing Oracle 11g on Windows 7 Ultimate Edition?

Your problem is almost certainly that you are entering an email address into the installer to receive information about updates. However this functionality will only work correctly if you also have a valid Oracle Support account.

Since you are in a home environment, I would leave the email address empty. The installer should then proceed without generating any HTTP requests to Oracle's servers.

You will however also have to download win32_11gR2_database_2of2.zip separately and unzip that into the same directory.


Cannot find DReplay.exe for SQL Server 2019

After much more googling and even speaking to Microsoft People, I found out that they have very quietly discontinued it partially.
Which means you do get the "Distributed Replay Controller" and "Distributed Replay Client" but there is no cmd utility "Distributed Replay administration tool" available for SQL Server 2019.
After a lot more research I found the tool which is much simpler and easier to use.
WorkLoadTools

It is a very lightweight application, no installation is needed.
Setup takes 5 minutes as compare to quite a few hours for MS Distributed replay
It uses Extended events to capture only the data that is needed for replaying, therefore the trace file size is much smaller.
You can capture events on Production and replay on client in real-time.
You can also capture a trace on Production and replay on client later.
There is reasonable amount of documentation on it as well.
Plus the guy who wrote this beauty, @spaghettidba is very helpful and will respond to your queries and help you to sort out any issues you may face.

Give it a go and you wont be disappointed.


You can get the required data from DBA_SERVICES (or v$active_services)

You can get the DDL required to create them again using something like this:

select 'DBMS_SERVICE.CREATE_SERVICE(
    SERVICE_NAME => ''' || name || ''',
    NETWORK_NAME => ''' || network_name || ''',
    parameter_array => params
  );' from dba_services;


Striping IO with datafiles on different physical mounts

Your plan of adding three datafiles on different mounts will not work well. Oracle doesn't stripe optimally within tablespaces. It just allocates extents in a reasonably efficient manner. The method you suggested is striped better than one disk, but it's not even close to optimal. Basically, you can't have really good striping without ASM or an external disk management system.

The other problem is that having three independently redundant arrays is either going to waste a lot of disk space or create three seperate points of failure. Think about it - if you are keeping one copy of each data block in each controller, then within that controller you will have multiple copies of the data again. You'll end up with 4, 6 or 9 copies of each block. That's a massive waste of resources. Conversely, without redundancy across controllers you are turning each controller into an SPOF (Single Point Of Failure).

Essentially you have two options:

Let Oracle manage the RAID functionality entirely. Turn off redundancy in the three controllers, set up ASM with normal/high redundancy. Create your diskgroups using an equal number of disks from each controller. Create three failure groups, one for each controller. This will achieve the redundancy and striping that you want.
Manage the redundancy entirely without Oracle. Use LVM or similar system to present a completely redundant, appropriately striped disk to Oracle. This will probably also involve turning off redundancy within each controller, and letting LVM manage the redundancy across controllers. The result is the same.

TL:DR - Your redundancy across disks within each controller is pointless when you have multiple controllers. Disable it and create redundancy across controllers instead.


Large batch INSERTs / UPDATEs and DELETEs Performance problem

Is vacuuming slow because you have inadequate IO throughput, or just because it is throttled too much?

The default throttling of autovacuum is not suitable for very write-intensive servers.  You should probably decrease autovacuum_vacuum_cost_delay or increase vacuum_cost_limit.  And I routinely set vacuum_cost_page_hit and vacuum_cost_page_miss to zero.  Page misses are inherently self-limiting as the vacuum process can't proceed until the page is delivered; so there is little reason to add intentional throttling on top of that.


Sql getting debit, credit and balance issue

The main thing you need is to generate the required order sequence using row_number().

seq = row_number() over
(
  partition by t.CustID
  order by t.InvoiceID,
           t.Date,
           CASE WHEN t.S_Type = 'Receipt Voucher' THEN 1 ELSE 2 END
 )

than use it for calculation of the cumulative balance

;

WITH cte
AS (
    SELECT  CustID,
        [InvoiceID],
        S_Type,
        DATE,
        Debit,
        Credit,
        seq = row_number() OVER (
            PARTITION BY CustID
            ORDER BY InvoiceID,
                DATE,
                CASE
                    WHEN S_Type = 'Receipt Voucher'
                        THEN 1
                    ELSE 2
                    END
            )
    FROM Statement
    )
SELECT c.[InvoiceID],
    c.S_Type AS Type,
    c.DATE,
    .Debit,
    c.Credit,
    b.Balance
FROM cte c
CROSS APPLY (
    SELECT Balance = SUM(Debit) - SUM(Credit)
    FROM cte AS x
    WHERE x.CustID = c.CustID
        AND x.seq <= c.seq
    ) b
WHERE c.CustID = '48'
    AND DATE BETWEEN '2015-01-01'
        AND '2016-01-01'
ORDER BY seq


Optionally exclude certain partitions using OLA's scripts

Having this as an option would be very helpful.

Ola's scripts are now on github and a Feature request is already there.


It is the name of the table that the index is created on.

This:

CREATE INDEX area_index ON rivers (area(geo));

.. creates a functional index on the rivers table, that uses the function area() on the column geo.

In simple terms, Oracle creates an index that pre-calculates the value of area(geo), thus making any lookups faster.

The query you have posted:

SELECT id, geo, area(geo), desc
FROM rivers
WHERE Area(geo) >5000;

... can use the area_index functional index to select the pre-calculated value(s) of area(geo) without having to call the area() function for each row in the table.

Essentially, the query can look like this in pseudo-code:

SELECT id, geo, area_index_value, desc
FROM rivers
WHERE area_index_value >5000;

The Oracle documentation explains this well.


Option to uncheck all or invert selection in SSDT?

For those coming here from Google looking for a solution to this (like me), I found the answer here.

  All you have to do is right click on the section and ta-da, you can Include or Exclude all objects depending on the existing state of the objects.

In this case, section means the Delete, Change, and Add parent folders in the schema compare window.


Sync two MySQL servers data, while changing the Engine in the slave

This should not a problem, provided you do not execute any DDL related to the Storage Engine on the Master.
I have written about this many times

Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
Nov 16, 2011 : Log MySQL DB changing queries and users
Mar 13, 2012 : https://dba.stackexchange.com/questions/14943/setting-up-mysql-across-a-series-of-production-servers/14945#14945
Aug 14, 2012 : Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

NOTE ABOUT MySQL 5.5 to 5.7 Replication
DATETIME in MySQL 5.6+ has been expanded to accommodate microseconds. Please change the binlog_format on both Master and Slave to STATEMENT to avert any issue with replication breaking due to DATETIME value (See my old posts mysql replication master 5.5 slave 5.1 error on create database AND enable log_bin_use_v1_row_events in mysql 5.6)


NULL value returned when concatenating a string in SQL while loop!

If you are on SQL Server 2012+ you can use CONCAT. This treats NULL as an empty string.

declare @o int = 10000000/1000000;
declare @i int = 0;
declare @s nvarchar(200);

while @i < @o
  begin
    set @s = CONCAT(@s, 'hello', cast(@i as nchar(4)));
    set @i += 1;
  end

select @s;


weekly moving average from current_date to the last 52 weeks

SELECT g(f(date)) AS 'week of',
       AVG(...) AS 'moving average'
    FROM tbl
    WHERE date > CURDATE() - INTERVAL 52 WEEK
    GROUP BY f(date)

Where f() is a function that turns a date (or datetime or timestamp) into a number.  Something like FLOOR((TO_DAYS(date) - 3) / 7), where the 3 is chosen based on what day a week "starts" on.  (I doubt if '3' is correct.)  g() is the inverse:  FROM_DAYS((x * 7) + 3).  Test:

mysql> SELECT                            NOW(),
                          FLOOR((TO_DAYS(now()) - 3) / 7) AS 'f(now)',
              FROM_DAYS( (FLOOR((TO_DAYS(now()) - 3) / 7) * 7) + 3) AS 'g(f(now))';
+---------------------+--------+------------+
| NOW()               | f(now) | g(f(now))  |
+---------------------+--------+------------+
| 2016-05-24 17:13:54 | 105210 | 2016-05-24 |
+---------------------+--------+------------+


You query says:

  Pull the most recent order from a set of customers and only if that order has product with type 31.

When you change the < to > it says:

  Pull the oldest order from a set of customers and only if that order has product with type 31.

So it is possible that the modified query to return no results, even the first query, with same parameters returns something. That would happen when the oldest order has product with type different than 31.

If that wasn't the intention of the query but you want the most recent (or oldest) order among the orders that have a product of type 31, you need to modify it, so the LEFT JOIN / IS NULL antijoin considers this restriction.

Some other minor notes:

the LEFT JOIN order_types tdmi works as an INNER join really, due to the tdmi.product_type_id = 31 condition in WHERE.
DISTINCT is not a function. It modifies the whole SELECT and not just one column, so the parentheses are redundant and can only bring confusion.
In a not so minor note, the DISTINCT looks suspicious. It can probably be removed.

The query becomes:

SELECT -- DISTINCT
    td1.product_id,
    td1.customer_id,
    td1.date_test_added,
    tdmi.product_type_id
FROM
    order_history AS td1
    LEFT JOIN order_history td2
        JOIN order_types tdmi2
            ON  tdmi2.product_id = td2.product_id
            AND tdmi2.product_type_id = 31
        ON  td1.customer_id = td2.customer_id
        AND td1.date_test_added < td2.date_test_added
    JOIN order_types tdmi
        ON  tdmi.product_id = td1.product_id
        AND tdmi.product_type_id = 31
WHERE
   td2.date_test_added IS NULL
   AND td1.customer_id IN (91000, 91001, 91002, 91003) ;


How do I do a JOIN in ANSI SQL 89 syntax?

You don't want to use SQL-89 syntax. SQL-89 is just an implicit CROSS JOIN (Cartesian product).

SELECT *            -- NEVER
FROM foo, bar;      -- NEVER EVER DO THIS

Is the same as the more modern

SELECT *
FROM foo
CROSS JOIN bar;

Adding the WHERE clause makes it the equivalent of an INNER JOIN ... ON

SELECT *                         -- DO NOT
FROM foo,bar                     -- DO THIS
WHERE foo.foo_id = bar.foo_id;   -- EVER

You want to use SQL-92 Syntax.

SELECT *
FROM foo
INNER JOIN bar
  ON foo.foo_id = bar.foo_id;

Or even better (if the join column is the same and it's an equijoin =)

SELECT *
FROM foo
INNER JOIN bar
  USING (foo_id);

Note some people don't use INNER. That's fine. It's still SQL-92.

See also

What does [FROM x, y] mean in Postgres?


The procedure sp_AllNightLog uses Ola Hallengren's scripts to do backup. If you don't plan to use sp_AllNightLog, then it is fine. If you do plan to use sp_AllNightLog, then install Ola Hallengren's scripts, which you can find here: https://ola.hallengren.com/
This is documented here: https://www.brentozar.com/sp_allnightlog/


Solved by adjusting the SQL user id to have the appropriate default schema... and tada!


postgresql - trouble to order a list obtained by string_agg

The order by in a string_agg() can absolutely use a CASE expression:

with data (ligne_code) as (
  values
  ('a value'), ('09'), ('12'), ('20'), ('007'),
  ('200'), ('92'), ('11'), ('Foo'), ('Exp3')
)
select string_agg(ligne_code, ',' order by case when ligne_code ~'^[0-9]+$' then ligne_code::int end, ligne_code)
from data;

returns:

007,09,11,12,20,92,200,a value,Exp3,Foo

The expression when ligne_code ~'^[0-9]+$' checks if ligne_code consists of numbers. If that is the case it's converted to an integer which is the first sort criteria. All non-numbers come after that and are sorted in a alphabetic way.


selecting the month day and year from a transaction table

You will want to use the DATE_FORMAT function in MySQL to format the date for each query.

-- query 1
select date_format(date, '%m-%d') `m-d`,
  sum(amount) Total
from mytable
group by date_format(date, '%m-%d');

See SQL Fiddle with Demo. Gives a result:

|   M-D | TOTAL |
-----------------
| 04-08 |    50 |
| 05-01 |   200 |
| 05-03 |   400 |

-- query 2
select date_format(date, '%Y-%m') `Y-m`,
  sum(amount) Total
from mytable
group by date_format(date, '%Y-%m');

See SQL Fiddle with Demo. Gives the result:

|     Y-M | TOTAL |
-------------------
| 2013-04 |    50 |
| 2013-05 |   600 |


How can I display multiple column records in the same row?

You can apply the GROUP_CONCAT function on top of your existing query like so:
SELECT Trader, GROUP_CONCAT(Items, ',') AS Items
FROM
(
    select NPC_Traders.Nickname as Trader, Classes.Class as Items
    from NPC_Traders, Classes
    where Items IN (
               select npc.Tradeables
               from NPC_Traders npc
               where npc.Tradeables LIKE '%1%' OR
                     npc.Tradeables LIKE '%2%' OR
                     npc.Tradeables LIKE '%3%' OR
                                    npc.Tradeables LIKE '%4%')
) AS Subquery


Effect of over allocating shared_buffers

Would over-allocating shared_buffers explain the stability problems? E.g., PostgreSQL tries to allocate more buffer than the OS has left, the OS says "nope" and things go south from there.

When the OS says "nope" it's in the system log, and likely the PostgreSQL error log. Just open it up and look. The process should also not start if a bigger allocation is requested than SHMMAX. But that value may not be set properly -- it may be higher than the physical memory.

  On investigation, I found shared_buffers set abnormally high, actually exceeding physical memory on the machine. I have reset to a more sane level, but would like some more insight into the expected behavior.

shared_buffers doesn't have to fit into RAM. It'll just page. This could be very bad for performance, but I don't know if it would cause the symptoms seen. "Stability problems" are usually hard to define. A bottleneck that reduces memory speed to disk speed can cause all kinds of bad behavior.


Does sql server calculate views every time when someone query the views?

Yes, a view is just a macro (unless indexed). The behaviour is well known.

See Tony Rogerson's article. You can see it yourself in the query plans too. The view doesn't exist on the query plan because it can't exist as a macro

Also note the "Predicate Pushing" phenomenon when you filter or TOP on views


Is the MySQL JSON data type bad for performance for data retrieval?

I think we can safely assume the performance would be worse than using regular columns.

The way to index JSON attributes is to create a virtual generated column for the particular attribute you want (company_id), then index the virtual column, and use that column to filter your query.