Is it safe to rely on transactions in Firebird 2.5 Embedded DB in case of power outage?

That is usually safe. Firebird uses an MVCC (Multi-Version Concurrency Control) architecture. Updates (and deletes) are written as a back version and a new version. The new version overwrites the previous version, and the back version is a delta to recreate the previous record version from the new record version. Deletes are written as a 'stub' record version that marks the record as deleted.
Each version is marked with the transaction that originally created the record version. If that transaction is not committed, that version of the record will not be visible. Firebird will follow the chain of back version pointers of the record to find the version that is visible to the transaction and reconstruct that version.
After a crash or other abrupt termination, Firebird will at some point detect the transaction is not active, and the transaction will be marked as rolled back. At that time, or at some later point during garbage collection, Firebird will rewrite the record versions to make the latest committed version the 'new' version, and eliminate unnecessary back versions.
The fact that you have a cascading delete in your example isn't really relevant, as long as the consistency of individual records is OK.

How can a transaction go from Active to Rolled Back if it exits abnormally?
This can happen in one of two ways.

When a transaction starts, it takes out a lock on its own transaction id. If a transaction (B) attempts to update or delete a
record and finds that the most recent version of the record was
created by a transaction (A) whose TIP state is ACTIVE, transaction B
tries to get a conflicting lock on A's transaction id. A live
transaction maintains an exclusive lock on its own id, and the lock
manager can probe a lock to see if the owner is still alive. If the
lock is granted, then B knows that A died and changes A's TIP state
from Active to Rolled Back.
When a transaction starts, it checks to see if it can get an exclusive lock on the database - if it can no other transactions are
active. Every active transaction has a shared lock on the database. If
it gets an exclusive lock, it converts all Active TIP entries to
Rolled Back.

(from: Firebird for the Database Expert: Episode 4 - OAT, OIT, & Sweep)
To prevent dataloss, Firebird employs what is called a "careful write" strategy to ensure on-disk consistency, by ensuring data is written in an order that maintains the dependencies between data pages (and versions).
The new record is written in the same place as the old record, while the back version is written to reconstruct the old record. There are basically two scenarios:

The new version and its back version fit on the same data page.
Both new and back version are written to the in-memory image of the page, and the page is written to disk.
In this situation, the only real 'failure' mode would be that the disk write itself is only partially done when the power fails. This problem can be addressed by using disk controllers with a backup battery unit (BBU) or similar solution.

The back version doesn't fit on the same page, and has to go on another data page.
The back version and new version are written to the in-memory image of their respective pages. As the new version depends on the back version (it points to the back version), the page with the back version is written to disk first, then the page with the new version is written to disk.
If the process terminates after the back version is written, the back version is orphaned, but the previous version of the record is still intact. It also has the same failure mode as the previous one.

So, in case of a power outage, or other type of termination of the Firebird process, this means that you possibly have orphaned pages (pages allocated but not yet registered, or pages released but not yet marked as available), or orphaned records (a back version that is written, but not yet correctly linked in a version chain of records). These orphaned pages or back versions waste space, but do not affect consistency. It might be necessary to use gfix to fix and reclaim space wasted that way.
That said, besides having failures mid-write and no BBU, I believe there are scenarios where a power outage leaves the database in a state where it first needs to be repaired using gfix, but I haven't encountered those scenarios myself and I can't really think of one right now.
This all assumes that you have synchronous writes (forced writes) enabled on your Firebird database (the default). If you disable synchronous writes, then writing pages to disk is left to your OS, and this might result in data-loss as pages might be written in a different order, for example the new version has been written to disk, but the back version not yet at the time of the outage, then you have essentially lost the committed version of the row, orphaned previous back versions, and the back version pointer of the new version points to garbage.
Further reading:

Firebird for the Database Expert: Episode 3 - On disk consistency
Firebird for the Database Expert: Episode 4 - OAT, OIT, & Sweep
A not-so-very technical discussion of Multi Version Concurrency Control
Understanding Firebird Transactions


query_cache_size: 512000000 plus query_cache_type: 1 is really bad for performance.

If the QC is on, then every modification to a table will cause a purge of all rows for that table in the QC.  The bigger the QC is, the longer it takes.  I recommend only 50M for the size.  Or turn the QC off.  All

"the" is a poor test case since it is (assuming English) so very common. Pick some word that occurs only a few times.  Or, maybe a word that occurs once, a word that occurs about 10 times, and a word that occurs 100 times.

max_heap_table_size: 512000000 and tmp_table_size: 512000000 are also bad because they are 12% of RAM; lower to 1% of RAM.  In complex queries, those limits come into play.  If there are several complex queries running at once, you could blow out RAM.  Swapping is really bad for performance.

If you can't permanently change those settings, complain to the provider.  If they won't listen, then change providers.  That's how bad I think the settings are.  No change to "memory, cpus, disk: ssd" can fix the query_cache_size problem.


How to pass a table-valued parameter to sp_execute_external_script?

Focusing on Table-Valued Parameters (TVPs) is a bit misleading since the question isn't really concerned with them. The example code, in fact, isn't even using the TVP as a TVP, and would be the same question even if presented as asking about table variables. Meaning, the following adaptation of the OP's example code is really the same question since sp_execute_external_script is being passed a string containing a query and not the @inputTvp variable:

DECLARE @inputTableVariable TABLE
(
  [ID] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  [SomeValue] NVARCHAR(50) NOT NULL
);

DECLARE @tSqlString NVARCHAR(100) = N'SELECT * FROM @inputTableVariable;';

EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @input_data_1 = @tSqlString; -- this doesn't work

Keep in mind that TVPs are meant to send structured data into SQL Server, not out of it. It's also a variable, hence it would only be available to the local context (i.e. not Dynamic SQL or any other sub-process). Still, just to be sure, I tried the following (in SQL Server 2017 so I couldn't specify "Java" as the language):

EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @params = N'@inputTableVariable dbo.[testTVP] READONLY';

and received:

  Msg 39018, Level 16, State 2, Line XXXXX
  Parameter '@inputTableVariable' uses a data type that is not supported by the runtime for 'R' script. Unsupported types are timestamp, datetime2, datetimeoffset, time, text, ntext, image, hierarchyid, xml, sql_variant and user-defined type.

That being said, you can pass in structured data (avoiding the use of temporary tables), you just need to serialize it as NVARCHAR(MAX) and pass it as a regular parameter (i.e. not an @input_data_1 query). For serialization, you can use either XML or JSON:

Via XML

DECLARE @XmlString NVARCHAR(MAX);

SET @XmlString = (SELECT *
                  FROM   @inputTvp
                  FOR XML AUTO, ROOT('data'));

PRINT @XmlString; -- DEBUG (else comment out)

EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @params = N'@inputData NVARCHAR(MAX)',
    @inputData = @XmlString;

Via JSON

DECLARE @JsonString NVARCHAR(MAX);

SET @JsonString = (SELECT *
                   FROM   @inputTvp
                   FOR JSON AUTO, ROOT('data'));

PRINT @JsonString; -- DEBUG (else comment out)

EXEC sp_execute_external_script
    @language = N'Java',
    @script = N'com.example.TestTvp',
    @params = N'@inputData NVARCHAR(MAX)',
    @inputData = @JsonString;


I can not see your script but I am assuming you get orphaned users on the database after the failover because the users are linked to SQL Server local logins with different SIDs on the different instances.
The best way would be to recreate the login with the same SID in both instances CREATE USER SID=<sid> so you do not have to fix orphaned users every time you failover.
If this solution doesn't suit your needs, perhaps you can set up an alert for event 1480 - AG Role Change, and run the script as the response action.


Elegant Code to find continuous time period and generate id

You just need to apply DENSE_RANK to the result of the first CTE - Demo

WITH T
     AS (SELECT *,
                [time] - row_number() OVER (ORDER BY [time]) AS group_num
         FROM   #test1
         WHERE  value > 0)
SELECT *,
       DENSE_RANK() OVER (ORDER BY group_num) AS group_id
FROM   T


After processing complete, SSAS DB slowly consumes all available space on disk, w. high memory use also

I found the answer eventually ....

There was a job set up to incrementally process the cube. Incremental cube processing requires a query to be defined to select new fact data to merge into the cube. In my case, this query was defined badly and was selecting the entire set of fact data, i.e. all the data that was already in the cube. Although fully processing the cube did not consume excessive disk space, for some reason incremental processing consumed all the available space. As a result the process never completed successfully. Rebooting the server caused the files created by the unfinished process to be deleted, but then minutes later the processing job would start again.


City
----
CityID
CityName
PRIMARY KEY (CityID)

Hotel
-----
HotelID
HotelName
CityID
PRIMARY KEY (HotelID)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package
-------
PackageID
PackageName
CityID
PRIMARY KEY (PackageID)
UNIQUE KEY (PackageName)
FOREIGN KEY (CityID)
  REFERENCES City (CityID)

Package versions:

PackageVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID, VersionNumber)
UNIQUE KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID)
  REFERENCES Package (PackageID)

and subtypes (of versions):

LandPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'L'
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)

FlightPackageVersion
------------------
PackageID
VersionNumber
PackageType  DEFAULT 'F'
OriginID
PRIMARY KEY (PackageID, VersionNumber, PackageType)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)
FOREIGN KEY (OriginID)
  REFERENCES City (CityID)

and default Package Version:

PackageDefaultVersion
--------------
PackageID
VersionNumber
PackageType
PRIMARY KEY (PackageID)
FOREIGN KEY (PackageID, VersionNumber, PackageType)
  REFERENCES PackageVersion (PackageID, VersionNumber, PackageType)


There are some general ISO/ANSI standards that should act as guidelines for all RDBMS that use the SQL language. The standards define features of the language, such as data types. The current standard is SQL 2011, which added a standard for temporal support.

Ideally, all RBDMS vendors that use SQL should develop language functionality according to the standards, making porting databases and user skills between the various implementations much easier.

Unfortunately, this is not really the case. There are many features that RBMS vendors are slow to implement (e.g. SQL Server not having DATE until 2008, despite it being in the SQL:92 standards) and many things that the standards do not cover (e.g. indexing)

The full standards are not available freely online (I don't think), however you can read part one here SQL Part 1


How to pause the subscriber end of transactional replication

Replication runs using "Agents"--essentially external executables that do the work. For transactional Replication, you have three agents:

Snapshot Agent - this is the process who does the snapshot to initialize Replication (assuming you aren't initializing via another method. Most people use snapshots). There is one per publication. It lives on either the Publisher or the Distributor.
Log Reader Agent - this is the process that reads the transaction log on the publisher, identifies changes, and writes those changes to the distribution database on your distributor. There is one per publication. It lives on either the Publisher or the Distributor.
Distribution Agent - this is the process that takes changes from your distribution database and pushes it to the subscriber. There is one per subscriber. It lives on either the Subscriber or the Distributor.

The standard setup is that the agents run as SQL Agent jobs. If you want to stop distributing commands to a subscriber, simply stop the jobs. You can find distribution agents based on the job category of REPL-Distribution. The job name will be composed to include publisher/subscriber info to help you identify the correct job to disable. Those values may be truncated when making the job name, so if you have multiple distributors, you may need to look at the distribution job step itself to be 100% sure you have identified the correct job.

Note you can also confirm the correct job by querying the distribution database--but I generally find it quicker and easier to look at the job itself.


Dump All Databases in MySQL - Fails on mysql and performance_schema

Sounds like you are using an older version of the mysqldump client than the database you're taking the dump from.

Do a "which mysqldump", and see if it is in the same bin directory as your running mysqld. Up to date versions of mysqldump ignore performance_schema.


How to succesfully set up sp_AllNightLog to create a Reporting Server?

Like any transaction log restore, sp_AllNightLog needs exclusive access to a database in order to do the restore.

This means you'll run into trouble when:

A user starts a very long-running query
sp_AllNightLog tries to start a log restore, and is blocked
Other users try to start queries, but are blocked by sp_AllNightLog, leading to timeouts and angry users

I wouldn't use sp_AllNightLog for this purpose (and that's not what it's designed for, so that's why you're not finding tutorials on how to do it.) If you DID insist on doing it, you would need to write code to:

Check to see if there are any user queries running
If yes, then bail out gracefully without trying to start a restore (but probably check to see if it's been a long time since a restore has been done - because you might be in a position where you need to kill the reporting query to get the data caught up)
If no, then start the restore process

That is left as an exercise for the reader.


From the RAISERROR documentation (emphasis mine):

  Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.

It's highly likely the principal you're executing the script as does not meet these criteria.

There's nothing wrong with using RAISERROR; you're just using a severity level that's excessive. I use level 16 as a default for an error that is raised and the sequence will be terminated. If you want to be more accurate, you can follow the levels given by Microsoft itself:

Now, having said all that, depending on the context of the script, using RAISERROR may not be enough, as it doesn't "exit" the script by itself (using normal severity levels).

For example:

RAISERROR(N'Test', 16, 1);

SELECT 1;   /* Executed! */

This will both raise an error and return a result set.

To terminate the script immediately, I prefer to use RETURN (using GOTO-type constructs are generally discouraged in most programming circles where alternatives exist):

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */

Or handle the error using TRY/CATCH, which will cause execution to jump to the CATCH block if the severity is 11 or higher:

BEGIN TRY
    RAISERROR(N'Test', 16, 1);
    SELECT 1;   /* Not executed */
END TRY
BEGIN CATCH
    SELECT 2;   /* Executed */
END CATCH

BEGIN TRY
    RAISERROR(N'Test', 10, 1);
    SELECT 1;   /* Executed */
END TRY
BEGIN CATCH
    SELECT 2;   /* Not executed */
END CATCH

A separate problem is if the script spans multiple batches -- RETURN will only exit the batch:

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */
GO

SELECT 2;   /* Executed! */

To fix this, you can check @@ERROR at the start of every batch:

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */
GO

IF (@@ERROR != 0)
    RETURN;

SELECT 2;   /* Not executed */

Edit: As Martin Smith correctly points out in the comments, this only works for 2 batches. To extend to 3 or more batches, you can cascade raising errors like so (note: the GOTO method does not solve this problem as the target label must be defined within the batch):

RAISERROR(N'Test', 16, 1);
RETURN;

SELECT 1;   /* Not executed */
GO

IF (@@ERROR != 0)
BEGIN
    RAISERROR(N'Error already raised. See previous errors.', 16, 1);
    RETURN;
END

SELECT 2;   /* Not executed */
GO

IF (@@ERROR != 0)
BEGIN
    RAISERROR(N'Error already raised. See previous errors.', 16, 1);
    RETURN;
END

SELECT 3;   /* Not executed */

Or, as he also points out, you can use the SQLCMD method if that's appropriate for your environment.


How to import a single table in Windows environment

This issue has been dealt with here. The approach described therein assumes that you have already separated your schema dump by filename.

However, if I've understood you correctly, you have a schema dump from which you wish to extract a single file? I have written a small tool which extracts individual table data separately from a schema dump file.

It's called MySQLDumpSplitter. If it doesn't suit your needs, there are two others with the same name also on Github. A Google of "mysqldump file split" or similar will lead you to other tools.

Once you have your Table_name.sql file, just follow the steps outlined in the first link.

DISCLAIMER: I wrote the linked tool. Its limitations are described on the page - you may find others which work better for you.


Database Normalization - Can one field point to another?

Even if SQL were to support a "pointer" like you describe, the pointer itself would take some memory. Ironically, the pointer might even be larger than the integer value it points to.

You tagged this question with MySQL, so I'll answer with respect to MySQL: there is no support for this pointer concept. In a relational database, columns contain values, not pointers.

Constraints are part of the table's metadata, and serve to make sure the value in the foreign key column does not get out of sync with the key column it references. Either it block changes, or duplicates changes if you declare the foreign key constraint with cascading effects.

Other implementations of RDBMS might have this feature, but not MySQL.


Simple avg query on large table much slower in PostgreSQL than SQL Server

In PostgreSQL 9.6 Date constructors were fixed.

  Improve speed of the output functions for timestamp, time, and date data types (David Rowley, Andres Freund)

You can see the commit fest for this here. It's roughly 20x faster. In addition 9.6 supports parallel sequential scans. So this may be 20x faster on a single core. And, the whole scan may be able to use all of the cores on your system.

Download 9.6
Try again.
Get back with the results.


How to deal with very large transaction logs in SQL Server

I would take the first transaction log backup by hand (not as a scheduled task) and during hours when a performance hit will be acceptable to your users.

Then take a second immediately so that the working area of the log file moves back to the start of the file.

Then shrink the log file.

Then start your scheduled transaction log backups.


PostgreSQL - is it possible to use a plpgsql function in a bash script?

As you were hinted in comments above, your RBAR approach might be very inefficient.  Consider the suggestions there.

Also, I am not going into details about the different approaches of UPSERT, as it is a very broad topic, especially when one wants to do concurrency-safe.  PostgreSQL 9.5 helps a lot in this regard.

So, to your actual question: it is not directly possible writing PL/pgSQL 'commands' interspersed with bash, but the task is definitely solvable.

For this, you need to define a function like this (left out the complexity to show my point more concisely):

CREATE OR REPLACE FUNCTION dynamic_upsert(
    table_to_upsert name,
    key_column name,
    value_column name,
    key integer,
    value text
)
RETURNS void
LANGUAGE plpgsql
AS $BODY$
BEGIN
    ...
    EXECUTE $$ UPDATE $$ || quote_ident(table_to_upsert) ||
            $$ SET $$ || quote_ident(value_column) || $$ = $2
               WHERE $$ || quote_ident(key_column) || $$  = $1 $$
        USING key, value;
    ...
END;
$BODY$;

Please note that this is fully dynamic in that you can choose any table, match against any column of it and modify any (other) column.  The type of the key (integer) and the value (text) are fixed, though.

Now you can call this from a shell script like

psql -h $DBHOST -U $DBUSER -d $DBNAME \
     -c "SELECT dynamic_upsert($TABLE, $KEY_COL, $VAL_COL, $key, $value)"

If you are still RBARing, this will produce an additional overhead of connecting to the database for every single change, so be patient (or set up a connection pool).

Notes:

using concatenation, one has to be absolutely sure to quote the object names properly.  This is here done using quote_ident(), which is the way to go if you are concatenating the dynamic statement.  In newer versions (from 9.1), one would use the format() function to avoid the mess:

EXECUTE format($$ UPDATE %I SET %I = $2 WHERE %I = $1$$,
               table_to_upsert, value_column, key_column)
USING key, value;

here I am using dollar quoting when building the dynamic query.  This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example).  This way most editors will highlight the statements nicely.


How to re-enable read ahead (prefetch) feature in SQL Server after disabling it?

All you need do is turn the trace flag off globally:

DBCC TRACEOFF (652, -1);

A service restart is not required.

To control read-ahead for the current session only, simply omit the -1.

DBCC TRACEON (652);
...
...tests
....
DBCC TRACEOFF (652);

This trace flag is only minimally documented, so for anything more than educational purposes on an isolated test system, it makes sense to contact Microsoft Support for advice.

Note: QUERYTRACEON is not effective for trace flag 652.


'Cleanse' a SQL Server database file created with Instant File Initialization enabled?

A SQL Server BACKUP only backs up extents that are being used to hold data.  The unused extents are left behind by the backup.  When a page is used for data it will be formatted for use as needed, so that page would be free of old data.

Therefore, all you should need to do is backup the database and restore it elsewhere.  The restored files will be of the same size as the original database, but the unused extents will be created using the capabilities of the target server.  This may be initialized fully or instantly initialized using the blocks of disk on the target server.

However, because extents are the level at which backups happen the unused pages in the extent could still have potential to expose some data when restored on another server.  Not as much as could be exposed on the source server, since the unused extents are not restored.


Is it worth to optimize queries with EarlyTermination Timeout?

I wonder if this imbalance of the number of rows maybe a result of the Timeout

Almost without exception, no. An initial cardinality estimation is performed before optimization begins. Subsequent optimizer transformations may require new estimates to be computed. There is no general rule to say which of the estimates will be more "accurate".

See this related question for additional information about the Timeout reason:

Queries without Good Enough Plan Found

  I thought it should be appropriate after the index & statistics rebuilding?

You have "a very complex ugly query". It is unreasonable to expect the cardinality estimator to work well in such cases. Try computing estimates yourself from the single-column (possibly sampled) statistics histograms and single-value density information to get a feeling for this.

As query complexity builds, so the quality of estimates tends to go down. This is inevitable, if you consider that the input to one estimation is usually the output of another. Thus, errors tend to accumulate. This is made much worse by the use of "ugly" (read: non-relational or otherwise hard-to-estimate) conditions.

  Does optimizing queries that end with ReasonForEarlyTermination=Timeout make any sense at all, if it's impossible to change the query itself?

Sure (but no differently than for any other problematic plan). In general, one can break the query up, express it using different syntax, or any one of a hundred different "tricks" may be used.

Even where the query itself cannot be changed, if a good plan can be found, it can be forced with a plan guide. More generally, adding new statistics and/or indexes can provide better information to the optimizer and cardinality estimator. This can also affect the code path taken through the optimizer in complex ways. I will not attempt to list all the things that can be done outside of changing the query text.

  ...the next query plan creation will certainly timeout again and so might be different, with different results?

The point is that by providing better information or data access methods, you will generally improve your chances of getting a good plan. The optimizer is not random, so for a given state of the database, the same plan will be selected, regardless of timeout, or other termination reason.

The real key to success is to avoid "very complex ugly" queries, of course.


Summary:

Can I use snapshot isolation for all write transactions?

Yes, but depending on usage read committed snapshot might be better.

Does snapshot isolation have flaws?

Yes. It needs to store row versions of all active transactions, which requires disk/memory.

What isolation level is better for readonly transactions?

snapshot isolation, or read committed snapshot depending if there are several statements depending on each other and the size of the transaction. read committed if there are many updates and disk space on tempdb is a concern.
Deeper information
Which isolation level to use depends on your use-case and how you use your data within the transactions. So let's start with a deeper comparison of the levels.
The SQL Server maintains consistency by utilizing two different techniques: Locking and Row versioning.
Locking isolation levels
Locking works by the SQL Server issues a shared lock on the tables/rows that it reads, blocking other transactions from updating the data. The same goes for updates, which are issued an exclusive lock, blocking other transactions from reading the data. Locking occurrs in different parts of the database, which I will not go into, but for instance tables, rows, indexes.
READ COMMITTED is using locking to ensure that it only reads committed data. And that no other transactions are updating the data while it's being read.
This means that an update statement on rows that another transaction is currently selecting is being blocked. And, a select statement on rows that another transaction is updating is being blocked until that data is committed.
READ UNCOMMITTED ignores everything, and doesn't take any locks at all. This means that another transaction can perform an update on rows that the transaction is currently reading without being blocked, but it also have the effect that the transaction will receive data that the other transaction might not yet have committed.
SERIALIZABLE do the opposite, and locks everything. While READ COMMITTED releases the locks when it read the row, or when the statement is completed depending on the lock, SERIALIZABLE releases the locks once the transaction is committed. This means that another transaction that wants to update data that the transaction have read at least once, or another transaction wants to read data that the transaction has updated will be blocked until the transaction is committed.
Row versioned isolation levels
READ COMMITTED SNAPSHOT and SNAPSHOT ISOLATION uses row versioning instead.
Row versioning means that every time a row is modified, the SQL Server stores a version of the row, ensuring that it will stay the same when read by another transaction.
SNAPSHOT ISOLATION works in the way that when a read is done on a table, it retrieves the last version of the rows that were committed at the time the transaction started. This provides a consistent snapshot of the data within the transaction. Data modified after the transaction started will not be visible, but at the same time the transaction will not be blocked. To protect against lost updates, if the transaction wants to update some rows that have been changed by another transaction after the transaction begun, it will terminate the transactions due to conflicting data.
READ COMMITTED SNAPSHOT works in the same way as snapshot isolation, but instead of keeping the snapshot the entire transaction, it only keeps it for the duration of the statement. This means that two read statements within one transaction could receive different results.
However, when the transaction is doing an update, it is using the actual row instead of a previous row version and does not track if the row has been changed.
Since the SQL Server needs to keep each modified row that could be used by active transactions available, it stores them in tempdb. Due to this, tempdb needs to be large enough to incorporate all the changes. There is a background thread that checks which rows are still needed, and removes the rest, but if there is a long running transaction, it will prevent those rows to be removed. If tempdb will run out of space, no new row versions will be created, and any transactions attempting to access those (non-existing) rows will terminate.
Comments
READ COMMITTED SNAPSHOT is apart from READ UNCOMMITTED the most permissive when it comes to concurrency. It doesn't block any other DML statements, and keep a consistent view of the data within each statement.

SNAPSHOT ISOLATION is permissive and maintains better consistency than READ COMMITTED SNAPSHOT, with the drawback that due to its conflict resolution may fail when doing updates. Multiple statements within the same transaction are guaranteed to be consistent with each other.

READ COMMITTED is permissive when it comes to concurrent reads, not so much for updates. Due to locking, it is also slower than the row versioning levels.

READ UNCOMMITTED is not recommended to use due to it will read dirty data, if that is no concern then it is best due to no locking and no need to keep old row versions.

SERIALIZABLE is not recommended since it keeps locks on everything and therefore is slow and not good with concurrency.


Connection to database causes SSPI context error

Try using Microsoft Kerberos Configuration Manager for SQL Server to automatically detect and fix such SPN Issues:

https://blogs.msdn.microsoft.com/farukcelik/2013/05/21/new-tool-microsoft-kerberos-configuration-manager-for-sql-server-is-ready-to-resolve-your-kerberosconnectivity-issues/


SQL Server Reporting Services: Generate Scripts Programmatically

I'm not sure how well powershell would handle the guids for the reports when you only transfer the jobs, but you could use rs.exe to execute a script file and call the webservice to download/copy/deploy Reporting Services Objects.

On the Sample Reporting Services rs.exe Script to Copy Content between Report Servers it is stated that Shared Schedules are supported for both native and sharepoint integrated instances.

There used to be a tool called "rsscripter" that provided a gui to create the scripts but I'm not sure how well it will work with more recent versions. It's included in the SQL Server fine build and looks like this:

The original article on that tool can still be found on archive.org Download Reporting Services Scripter


Do we have to run auto update statistics after index rebuild?

You should not update statistics after you rebuilt the index as rebuilding it gives you up-to-date statistics. See Does rebuild index update statistics? by Jack Li for more info.

You get updated stats for the specific index you just rebuilt. Other stats for other indexes on the same table (and statistics not associated with an index at all) do not get updated. You may or may not choose to update those stats separately.

Auto-update is recommended, though I would also recommend a scheduled job too as the threshold for when auto-update kicks in can be too high for many systems. I usually have an update stats job run daily or even more frequently, but it depends on the system and its needs.


createIndex := 'CREATE INDEX idx_'
    || Fulltable_name
    || '_properties_crosspr_gist ON public.'
    || Fulltable_name
    || '
    USING gist (properties)
    WHERE properties @> $$"block_level_0"=>"cerber-head"$$::hstore
      AND properties @> $$"block_level_1"=>"head"$$::hstore
      AND properties @> $$"block_level_2"=>"cross-news"$$::hstore
      AND properties ? $$block_level_5$$::text
      AND NOT properties ? $$block_level_6$$::text';


How to improve this query performance by indexing properly?

Since you have a foreign key from CHILDEVENTS to CHILDINFORMATION and you don't select any column from the child information table, you can skip that table. One less join will certainly not hurt:

SELECT CHE.CHILDID, COUNT(*) AS CURRENTWEEKPOTTYBREAKS
FROM  BKA.CHILDEVENTS AS CHE
WHERE CHE.TYPE ='POTTYBREAK'
  AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend
GROUP BY CHE.CHILDID ;

As for indexing, the existing index on (Adddate,Type) INCLUDE (ChildId, EventId) does not look very good for this query. I would try replacing it (in fact removing both those AddDate-Type indexes) with an index on:

(Type, AddDate) INCLUDE (ChildId)


Huge Data Migration in Oracle and space concerns

No, you can't pre-fail the process unless it's something you write yourself.  However, you can use Oracle's Resumable Space Allocation to allow your session(s) to suspend for a given amount of time if it runs out of space, extents, or quota.  Then once the space problem's been resolved, the transaction/session automagically keeps working from where it was suspended.


Mysql replication problem after export/import on already working replication

I would check the export process.

If you use mysqldump, create this script on the Slave and execute it on the Slave:

#
# Dump the Master
# Use the coordinates of the Master at the point the mysqldump starts
#
DUMPFILE=/path/to/dumpfile.sql
MYSQL_USER=masterroot
MYSQL_PASS=masterrootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
SQL="SELECT GROUP_CONCAT(schema_name) FROM information_schema.schemata"
SQL="${SQL} WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}" | sed 's/,/ /g'`
echo "STOP SLAVE;" > ${DUMPFILE}
MYSQLDUMP_OPTIONS="--master_data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --add-drop-database"
mysqldump -h ipaddrofmaster ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} >> ${DUMPFILE}
echo "START SLAVE;" >> ${DUMPFILE}
#
# Load the Slave
#
MYSQL_USER=slaveroot
MYSQL_PASS=slaverootpassword
MYSQL_CONN="-u${MYSQL_USER} -p ${MYSQL_PASS}"
mysql -h localhost ${MYSQL_CONN} < ${DUMPFILE}

This script will move all user-defined database. It will not import the mysql schema from the Master. This is designed so that the Master can be from an earlier version of MySQL than the Slave and will still import the data. All grants should be left in tact.

You need to make sure of the following:

the Master and Slave are using the same binlog_format (Being Cautious)
the Master and Slave are using two different values for server_id

If Slave_IO_Running is Yes, Slave_SQL_Running is Yes, you are definitely writing on the Master, and the Relay_Log_Space is not moving, then one of the following might be happening

the Slave ran out of diskspace because MySQL will freeze solid with no diskspace
The I/O has silently stopped heartbeating (Derek Downey mentioned this in hist old post Can we capture only Slave_IO_Running in SHOW SLAVE STATUS in MySQL)


What's better: a temporary column or a temporary table?

As per my experience, adding new table (Option 2) referencing index column would be you best choice. And will be less messy and easy to use.

Also, going further you may be able to add columns to the newly created table.

Hope this help!


Here is how you find the sum of the lengths the LONGTEXT column of all rows

SELECT SUM(field_length) TotalFieldLength FROM
(SELECT LENGTH(LongTextColumnName) field_length FROM mydb.mytable) A;

Once you delete the rows you want, just compress the file

You can do it in a single command

OPTIMIZE TABLE mydb.mytable;

or in stages

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
ALTER TABLE mydb.mytablenew DISABLE KEYS;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytablenew ENABLE KEYS;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;
ANALYZE TABLE mydb.mytable;

Give it a Try !!!


UTL_FILE directory name changes oracle 19c

Starting with 18c, the UTL_FILE_DIR parameter no longer exists, thus the usage of directory paths instead of directory objects in UTL_FILE is no longer possible.
Desupport of UTL_FILE_DIR Initialization Parameter

Starting in Oracle Database 18c, the UTL_FILE_DIR parameter is no
longer supported. Instead, specify the name of a directory object.

Even in earlier releases, the parameter and usage of paths existed only for backward compatibility.


Create table partitions on different filegroups, the first boundary on a Memory Optimised filegroup

Partitioning memory-optimized tables directly is not yet supported. See SQL Server Features Not Supported for In-Memory OLTP.

A workaround is described in the documentation Application Pattern for Partitioning Memory-Optimized Tables

  You can emulate partitioned tables with memory-optimized tables by maintaining a partitioned table and a memory-optimized table with a common schema. Current data would be inserted and updated in the memory-optimized table, while less-frequently accessed data would be maintained in the traditional partitioned table.

Alternatively, you could consider other technology solutions like Columnstore for real time operational analytics, which combines in-memory OLTP with columnstore (as [optionally filtered] secondary storage).


How to open a file in MySQL event scheduler?

You cannot or should not manipulate files within a store procedure (which is basically the same as an event, but with a schedule) due to scope and security reasons.

MySQL events and stored procedures are supposed to only modify or read data using SQL, and not go outside of the database server responsibilities. If MySQL server could read or execute arbitrary files, that would be considered a vulnerability and would be patched (with the exception of LOAD DATA or very few other very specific commands). While certain plugins allow accessing or executing arbitrary data, that is considered an exception, and not the normal way of using MySQL.

Normally PHP applications access MySQL as a resource, not the other way round. You may want to setup a UNIX cron or Window task scheduler at your OS to do what you are trying to do (executing PHP code) instead, rather than using the database.


How to Migrate Oracle 11g R2 (Windows) database to Oracle 12c R2 (Linux) db

Source:

RMAN> shutdown immediate
RMAN> startup mount
RMAN> backup database ...

Destination:

RMAN> startup nomount
RMAN> restore controlfile from ...
RMAN> alter database mount;
RMAN> restore database;
RMAN> alter database open resetlogs upgrade;

Then manually upgrade the database to 12.2:

Example of Manual Upgrade of Windows Non-CDB Oracle Database 11.2.0.3


Powershell script while executing mysqldump script

I would guess that there's one or more characters in the password that are "confusing" the shell.
There are standard techniques for supplying passwords to mysql utilities that avoids having to have them coded into scripts or visible to any process list run on the machine.
See this answer.


You are over-thinking this.  Stopping the slave doesn't cause you to lose your binlog position, and changing the replication username and password doesn't, either, as long as that is all you change.

On the slave:

mysql> STOP SLAVE;

This won't stop in the middle of a binlog read.

Create the new account in the master, then reconfigure the slave to use them:

mysql> CHANGE MASTER TO MASTER_USER='foo', MASTER_PASSWORD='bar';
mysql> START SLAVE;

Unless you change other parameters with CHANGE MASTER TO, the slave will reconnect with the new credentials and resume where it left off.  You do not need to specify host, port, file, position, or anything else, and you should not specify them, unless they are changing too... in which case, yes, you'll need to restart with new coordinates.

https://dev.mysql.com/doc/refman/5.6/en/change-master-to.html


MariaDB 5.5-56 not reading the my.cnf file and taking default values

On a Linux OS, make sure the file /etc/my.cnf is owned by root.


Thanks to everyone for your help.

After pouring through some execution plans, it turns out there is a JOIN that is being processed differently based on the amount of RAM available. With less RAM it evaluates it with a Hash; with more RAM, it uses a series of Merge Joins.

So basically it came down to poorly written T-SQL, which I am presently refactoring.


What does @ signify in a query expression?

@ is the "absolute value" operator.

SELECT @ -1;
 ?column?
----------
        1
(1 row)

It returns the same value for positive numbers.


Fill Database With Recursive Factorial Function

DECLARE
    COUNTER INTEGER := 0;
    CURSOR C_NUMBERS IS SELECT  NUMBERS FROM FACTORIALS;
    TYPE N_LIST IS VARRAY(7) OF FACTORIALS.NUMBERS%TYPE;
    TYPE F_LIST IS VARRAY(7) OF FACTORIALS.FACTORIALS%TYPE;
    NUMBER_LIST N_LIST := N_LIST();
    FACTORIAL_LIST F_LIST := F_LIST();
BEGIN
    FOR N IN C_NUMBERS LOOP
          COUNTER := COUNTER + 1;
          NUMBER_LIST.EXTEND;
          NUMBER_LIST(COUNTER) := N.NUMBERS;
     END LOOP;

    FOR I IN 1 .. NUMBER_LIST.COUNT LOOP
          FACTORIAL_LIST.EXTEND;
          FACTORIAL_LIST(I) := F_FACT_NUMBER(NUMBER_LIST(I));
    END LOOP;
/***************************FIRST SOLUTION***************************/
    FOR K IN 1..NUMBER_LIST.COUNT LOOP
          UPDATE FACTORIALS
          SET FACTORIALS = FACTORIAL_LIST(K)
          WHERE NUMBERS = NUMBER_LIST(K);
    END LOOP;
/***************************SECOND SOLUTION**************************/
    FORALL INDX IN NUMBER_LIST.FIRST..NUMBER_LIST.LAST
                UPDATE FACTORIALS SET FACTORIALS = FACTORIAL_LIST(INDX)
                WHERE NUMBERS = NUMBER_LIST(INDX);
END;
/

Problem is solved. I have two solutions which are showed in the PL/SQL code. When i execute this code and then the FACTORIALS table like as below:

* FACTORIALS TABLE AFTER THE SP IS RAN
------------------------------
| NUMBERS | FACTORIALS       |
|----------------------------|
| 3       |    6             |
| 5       |    120           |
| 6       |    720           |
| 9       |    362880        |
| 7       |    5040          |
| 2       |    2             |
| 1       |    1             |
------------------------------


I need query to provide distinct number of choices

If by distinct combinations you mean distinct sets of numbers, regardless of their order (in fact, sets are unordered, according to the algebra of sets), then the only fix your script requires is to replace each <> with either > or <, by which I mean that it should be either all < or all >.

After you switch the comparison symbol, some comparisons will become redundant, and so your query can be simplified. Here is one way to rewrite it:

SELECT
  t1.ID AS Number_1,
  t2.ID AS Number_2,
  t3.ID AS Number_3,
  t4.ID AS Number_4,
  t5.ID AS Number_5,
  t6.ID AS Number_6
FROM
  cTop8 AS t1,
  cTop8 AS t2,
  cTop8 AS t3,
  cTop8 AS t4,
  cTop8 AS t5,
  cTop8 AS t6
WHERE t1.ID < t2.ID
  AND t2.ID < t3.ID
  AND t3.ID < t4.ID
  AND t4.ID < t5.ID
  AND t5.ID < t6.ID
;

And the output would be exactly 28 rows:

Number_1  Number_2  Number_3  Number_4  Number_5  Number_6
--------  --------  --------  --------  --------  --------
1         2         3         4         5         6
1         2         3         4         5         7
1         2         3         4         5         8
1         2         3         4         6         7
1         2         3         4         6         8
1         2         3         4         7         8
1         2         3         5         6         7
1         2         3         5         6         8
1         2         3         5         7         8
1         2         3         6         7         8
1         2         4         5         6         7
1         2         4         5         6         8
1         2         4         5         7         8
1         2         4         6         7         8
1         2         5         6         7         8
1         3         4         5         6         7
1         3         4         5         6         8
1         3         4         5         7         8
1         3         4         6         7         8
1         3         5         6         7         8
1         4         5         6         7         8
2         3         4         5         6         7
2         3         4         5         6         8
2         3         4         5         7         8
2         3         4         6         7         8
2         3         5         6         7         8
2         4         5         6         7         8
3         4         5         6         7         8

Feel free to keep your ORDER BY RAND() if the row order needs to be randomised.


osm2pgsql: select relation member by role

If we can make the assumption that the unnest() operation will always return a set where a member "id" is listed followed by its role in a single column of text entries, then I've written up a quick solution for you.

I made a quick data set as:

CREATE TABLE mylist (entry text);

INSERT INTO mylist VALUES
('n240045827'),
('admin_centre'),
('w185481610'),
('outer'),
('w191987461'),
('outer'),
('w166286278'),
('outer'),
('w166290573'),
('outer'),
('w65481875'),
('outer'),
('w166278211'),
('outer'),
('w166278224'),
('outer'),
('w166278225'),
('outer'),
('w166278227'),
('outer'),
('w185148605'),
('outer');

Assuming we can return this, then the script

WITH numbered AS(
  SELECT row_number() OVER() AS row, entry
  FROM mylist)
  SELECT a.entry
  FROM numbered AS a JOIN numbered AS b
  ON a.row = b.row-1 AND b.entry = 'admin_centre';

will return only n240045827.

How does it work?  Well, I just took a straightforward approach to assigning order to the returned list by using row_number() OVER().  In this example, I've used a CTE WITH... to add the row after the fact, but in your case you could add it into your query like so:

...
SELECT ltrim(member, 'n')::bigint AS osm_id
    FROM (
    SELECT row_number() OVER() a AS row, unnest(members) AS member
    FROM planet_osm_rels
...

Finally, I simply join this result to itself, comparing one row to the next by its row number, and looking for the 'admin_centre' text entry.

Here's a SQL Fiddle for testing.

For your perusal:

Common Table Expressions

Window Functions

Edit

If you want to return all rows as column pairs, then use the following approach:

WITH numbered AS(
  SELECT row_number() OVER() AS row, entry
  FROM mylist)
  SELECT a.entry, b.entry
  FROM numbered AS a JOIN numbered AS b
  ON a.row = b.row-1 AND a.row%2 = 1;

Also, here's a SQL Fiddle #2 for testing.


It's easy with a numbers table. Since also qty cannot be more than 10, we only need a very small numbers table:

CREATE TABLE numbers
  ( i int NOT NULL PRIMARY KEY ) ;

INSERT INTO numbers (i)
VALUES (1), (3), (5), (7), (9) ;

We need only the odd numbers because the numbers of rows wanted in the result is half of qty (or about half).

The query:

SELECT
    t.id, t.name,
    qty = CASE WHEN n.i = 1 AND t.qty % 2 > 0 THEN t.qty % 2 ELSE 2 END
FROM
    tableX AS t
  JOIN
    numbers AS n
  ON
    n.i <= t.qty ;

Of course we could also do it without any auxiliary table:

SELECT
    t.id, t.name,
    qty = CASE WHEN n.i = 1 AND t.qty % 2 > 0 THEN t.qty % 2 ELSE 2 END
FROM
    tableX AS t
  JOIN
    (VALUES (1), (3), (5), (7), (9)) AS n (i)
  ON
    n.i <= t.qty ;

Tested at dbfiddle.uk.


How to design a partitioning strategy for a VLDB OLTP solution (SQL Server 2016, >100TB init. size)

I would suggest you to go through the article describes some important prerequisites and suggestion for OLTP databases.

http://nerdtechies.com/2016/12/05/improve-write-performance-sql-server-database/

For the loading process use BULK INSERT and for normal insert user WITH(ROWLOCK).

https://technet.microsoft.com/en-us/library/dd425070.aspx

Partitioning

What you need to know.

What would be retention of online data?
What is growth/day?
On what basis reports users pull maximum (daily/weekly/Monthly or Yearly)?
Archiving Policy.

--I have experience on 2 TB table with 50GB growth/day, one month data on production rest on WH. So suggesting accordingly.

If 70-80 % Usage of daily basis analytical reports. I would suggest to go for daily basis partition as there would be huge amount of data.
It will perform faster but to generate weekly, monthly and yearly report you'll have lengthy query.

If there is 50-50 ratio between daily,weekly and monthly analytical then go for monthly partitioning. In this case Daily and Weekly basis reports will perform slower than day basis partitioning, because there would be lot of records to filter from the month. But you'll have quite simple query.

Partitioning by considering retention of online data make Archiving Policy easier.

Index

As table will be partitioned you should create partitioned indexes on the table.To create partitioned index you need to include partition base column in the indexes. Until you don't create partitioned indexes on the partitioned table you wouldn't get performance benefits.

Creating Indexes on separate filegroups will result in good performance for reports. So create separate Partition scheme,function, on separate filegroupes for indexes same as created for table.

Better to go for Column Stored Index on (Base_Partition_Column,customer name, account numbers, transaction types, Financial Column) on Index_Partition_Scheme.

Create indexes with FILLFACTOR=80

Creating partitioned indexes make index maintenance easier. Instead of rebuilding or reorganizing complete index you can perform maintenance task for particular partition of the index which minimize maintenance duration for big tables.

To do it you can track index fragmentation and row count of the partitions. It will help you to find out indexes on which partition should be rebuild.

Maintenance schedule depends on data size, how much off duration you have to perform the maintenance activities and how long SQL Server takes to finish the task.
It would be better to test you maintenance plan on test environment first with same amount of data then move for production if it finish within the off hour you have.

Thanks


Unable to start a nested transaction for OLE DB provider "SQLNCLI11" for linked server

As the error message suggests, add the following to your stored procedure:

SET XACT_ABORT ON;

See Distributed Queries and Distributed Transactions


SQL Trying to sum and group by distinct values

Following confirmation in the comments it seems that you need this

WITH Dist
     AS (SELECT DISTINCT LineName,
                         Material,
                         ProdQty
         FROM   [table])
SELECT LineName,
       COUNT(Material) AS Cmat,
       SUM(ProdQty)    AS ProdQty
FROM   Dist
GROUP  BY LineName


Galera cluster: change the size of InnoDB Redo log files online?

The InnoDB redo log files are independent of galera.  Because of this, you should be able to change the redo log configuration by doing a rolling restart and following the procedure in the link you referenced.


How to generate create table script without primary and foreign key in Oracle

You could try the following:

set pagesize 0
set long 90000

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);

SELECT DBMS_METADATA.GET_DDL( 'TABLE','table_name','schema') FROM DUAL;

This should result in the DDL without any indexes and foreign keys.

Reference:

docs.oracle.com
dba-oracle.com


Error on SELECT - Primary filegroup is full

The quickest fix would be to restart the SQL Server and Tempdb will be recreated with default size and empty files.

But if it's a production server you can't really restart it when you want. A real fix would be to add a new file on a different drive and run your queries.

An example would be (new file of starting size 1 MB, increase 100 MB, limit 500 MB):

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'newtempfile', FILENAME = N'e:\newtempfile.ndf' , SIZE = 100MB, MAXSIZE = 500MB , FILEGROWTH = 100MB )
GO

And then, when you have time, check what's using Tempdb so much. But likely you'll still need space for TempDB, so you'd better plan space for this and assign enough space for this db, as it's very important for a healthy system (you can simply look at it as a RAM part).

PS1: check if you don't have a Cartesian product anywhere in your select statements, because that number seems a bit high.

PS2: If you have enough free space on the TempDB drive, check if the files didn't get to their limit and autogrowth is disabled. If yes, enable autogrowth (not by percent, but by some specific size you feel comfortable with).

PS3: a good solution would be to break that ETL process into smaller transactions. Instead of removing 1 Bil records at once, do it by 1000 mill .. or play with batch sizes until you feel safe with the length of the process and the size of the files. You'll likely obtain the result faster and not have huge increases in space at once.


Why is searching by one varchar column not working correctly on replica?

As you say the column is indexed, try dropping and creating the index again. It might have gotten corrupted during replication.


Replace value of strongly typed xml element in SQL Server with XQuery

I have not found a simple way to just modify the replace value of statement to work with anonymous simple type definitions.

Simple repro of what you have:

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid">
                    <xs:simpleType>
                        <xs:restriction base="xs:string">
                            <xs:maxLength value="30"/>
                        </xs:restriction>
                    </xs:simpleType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';

set @X.modify('replace value of /root/xid  with "2"');

Result:

  Msg 2247, Level 16, State 1, Line 25 XQuery [modify()]: The value is
  of type "xs:string", which is not a subtype of the expected type
  "<anonymous>".

One workaround is to modify your schema to use a named simple type xidType and cast the new value.

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid" type="xidType"/>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
    <xs:simpleType name="xidType">
        <xs:restriction base="xs:string">
            <xs:maxLength value="30"/>
        </xs:restriction>
    </xs:simpleType>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';

set @X.modify('replace value of /root/xid  with "2" cast as xidType?');

Another way is to extract the XML to an untyped XML variable, modify the variable and put it back to the table.

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid">
                    <xs:simpleType>
                        <xs:restriction base="xs:string">
                            <xs:maxLength value="30"/>
                        </xs:restriction>
                    </xs:simpleType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';
declare @X2 xml = @X;

set @X2.modify('replace value of (/root/xid/text())[1]  with "2"');
set @X = @X2;


Is there any query to fetch the a single row data in chunks?

I worked for a while on your request dude... your problem is that it's not really adapted for relational database (POOR PERFORMANCES), you will be obliged to use some scripting language, but there is some interesting results:

I've created a tale with one line:

CREATE TABLE mytable (mycolumn VARCHAR(1000));

Inserted your example in it:

INSERT INTO mytable VALUES ("Phone Number:--- xxxxxxxxxxx Call Type:--- MISSED Call Date:--- 2013-06-13 Call duration in sec :--- 0 Phone Number:--- xxxxxxxxxxx Call Type:--- OUTGOING Call Date:--- 2013-06-14 Call duration in sec :--- 21 Phone Number:--- xxxxxxxxxxx Call Type:--- INCOMING Call Date:--- 2013-06-14 Call duration in sec :--- 79");

Now with my query you can retrieve one line in your dataset. This query will split the result and return one line, you just have to modify the @i variable to choose your line (0 is the first line) :

SET @i = 0;
SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 3),' ', -1) AS "Phone Number",
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 6),' ', -1) AS "Phone Type",
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 9),' ', -1) AS "Call Date",
SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 15),' ', -1) AS "Call Duration in sec"
FROM mytable;

Example with the third line:

[11:42:02] [DEV\MFO] mysql> SET @i=2;
Query OK, 0 rows affected (0.00 sec)

[11:42:08] [DEV\MFO] mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 3),' ', -1) AS "Phone Number", SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 6),' ', -1) AS "Phone Type", SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 9),' ', -1) AS "Call Date",  SUBSTRING_INDEX(SUBSTRING_INDEX(mycolumn, ' ', (@i * 15) + 15),' ', -1) AS "Call Duration in sec" FROM mytable;
+--------------+------------+------------+----------------------+
| Phone Number | Phone Type | Call Date  | Call Duration in sec |
+--------------+------------+------------+----------------------+
| xxxxxxxxxxx  | INCOMING   | 2013-06-14 | 79                   |
+--------------+------------+------------+----------------------+
1 row in set (0.00 sec)

Note that if you want to know how many lines you have in your dataset you can use:

SELECT (LENGTH(mycolumn) - LENGTH(REPLACE(mycolumn, ' ', '')) + 1) / 15 FROM mytable;

Let me know...

Max.


How to select the rows affected by an update

This sounds like a job for SELECT FOR UPDATE. Please see MySQL Docs on this
I have discussed this over the years

Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
Jan 02, 2012 : LOCK IN SHARE MODE
Mar 18, 2012 : select for update gives error on indexed column
May 09, 2012 : Transaction Lock Timeouts When Updating a Row
May 13, 2012 : Cannot update certain rows in innodb tables
Aug 10, 2012 ; Similar function NOWAIT in MySQL
Feb 12, 2014 : row locking within ACID transaction innodb


In Oracle, one of the fundamental concepts for the CBO (Cost Based Optimizer) is that of the bucket.

When collecting statistics about the distribution of data in a table (v. important for optimising the query plan) Oracle can split the data into buckets (or bands or segments) for the purpose of constructing histograms to determine the distribution of the data.

It can't really be better explained than here:

  Data skew in a column can make it difficult for the optimizer to
  accurately estimate the cardinality of an operation. Without a
  histogram it will assume an even distribution amongst the distinct
  values for the column.

  Take the case of a Yes/No flag for active
  records in a table. There may be 1 million rows in the table with only
  100 being marked as active. The optimizer would assume half are marked
  as "Yes" and half as "No", which would be a really bad assumption in
  this case. Histograms describe the distribution of data in a column,
  which helps identify data skew and allows the optimizer to make a
  better decision.

Checkout the links on the same page - oracle-base is a super site for all things Oracle by Tim Hall who really knows his stuff!

See here for a bit more detail, or Jonathan Lewis' site for in-depth information. Lewis is the CBO man - he literally "wrote the book" (Cost-based Oracle Fundamentals) about this (not bedtime reading! :-) ).


Galera - single database backup & restore

There are multiple ways to backup and restore but as always this depends on your needs and varies among setups, here is just an idea that could help minimize the recovery on time point in case need to restore.

Before, is important to understand that in a MariaDB Galera cluster, all the nodes are "masters" and by using pc.weight=0 you are just modifying the quorum check Weighted Quorum for a Master and Multiple Slaves Scenario, from the docs:

node1: pc.weight = 1
node2: pc.weight = 0

  Under this pattern, if node1 dies, all remaining nodes end up as non-primary components. If any other node dies, the Primary Component is preserved. In the case of network partitioning, node1 always remains as the Primary Component.

But this can't be used as a backup strategy since once your data has being modified it will be replicated among all the nodes.

Regarding the backup and restore scenario in case of a human error, hacked DB or hardware malfunction, etc, it will depend on how periodically you were backing up, your last on time restore point will be the latest date of your backup. You could create backups daily or even every 12 hours, a heavy time-consuming process depending on your DB size.

As an easy but more "expensive" alternative to this method, you could attach one or more slaves to your cluster, One to be in sync (no delayed) and a second one using a delayed replication in where you could define a delay for example 1 hour. (you could set the delay to your "reaction" time for example)

However, is important to monitor the state of the cluster(masters) so in case something unexpected happened you could stop the replication (at least on the delayed slave) since you would like to use it as your earlies restore on time point.


Combining multiple left join for one single line

Use the GROUP_CONCAT function

SELECT header,GROUP_CONCAT(eMessage) ErrorMessages FROM
(
    SELECT tblSData.header,IFNULL(tblEAlert.eMessage,'') eMessage
    FROM tblSData
    LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID
    Where tblSData.aID=".$aID." Order By tblSData.dateTimer  Asc
) A
GROUP BY header;

or

SELECT tblSData.header,GROUP_CONCAT(IFNULL(tblEAlert.eMessage,'')) ErrorMessages
FROM tblSData
LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID
Where tblSData.aID=".$aID." Order By tblSData.dateTimer  Asc
GROUP BY header;

A good index to create for this would be

ALTER TABLE tblSData ADD INDEX (aID,dateTimer);

What good will this index do ?

This will get everything for a specific aID
All aID rows are  ordered by dateTimer
The sDataID column will be available for use from the index rather than the table because the Clustered Index will include the PRIMARY KEY anyway.

You should also create this one

ALTER TABLE tblEAlert ADD INDEX (sDataID);

Give it a Try !!!


Why can i not call COUNT on a varray column in a select statement using oracle

10.2 documentation: Using Collection Methods

  The following apply to collection methods:

  Collection methods cannot be called from SQL statements.

  ...

11.2 documentation: Collection Methods

  A collection method invocation can appear anywhere that an invocation
  of a PL/SQL subprogram of its type (function or procedure) can appear,
  except in a SQL statement.

Versions before 12c:

SQL> CREATE OR REPLACE TYPE "LIST_OF_NAMES_T" IS  VARRAY(10) OF VARCHAR2 (100);
  2  /

Type created.

SQL> create table table1(c1 LIST_OF_NAMES_T);

Table created.

SQL> insert into table1 values(LIST_OF_NAMES_T('A', 'B'));

1 row created.

SQL> commit;

SQL> create function f1(p_list_of_names list_of_names_t) return number as
  2  begin
  3    return p_list_of_names.count;
  4  end;
  5  /

Function created.

SQL> select f1(items) from table1;

 F1(ITEMS)
----------
         2

In 12c and later, you can define functions on the fly, so this also works:

SQL> with function f2(p_list_of_names list_of_names_t) return number as
  2  begin
  3   return p_list_of_names.count;
  4  end;
  5  select f2(items) from table1;
  6  /

 F2(ITEMS)
----------
         2


Because the table has InnoDB engine and only 2 columns, the one in the index and the PRIMARY KEY column, the index is a "covering" index for the query, so it is used. It's not used as effectively as a B-tree would be used but it still covers the query. Note that any secondary index of an InnoDB table carries the PRIMARY KEY columns data as well, so in this case it's actually (name, id) internally.

Consider that the optimizer had 2 options, either scan the whole table or scan the whole index. It chose the index because it is less wide (InnoDB tables have a extra "baggage" of 13 bytes per row. For the secondary indexes this baggage is smaller.)

You can check by doing some tests:

Test A: add a third column in the table, without adding any indexes and check the execution plan. It would probably do a table scan and not use the index. The index carries the name and id (primary key) data but not any other column data, so it would not be covering the SELECT * query.
Test B: convert the table to use MyISAM engine and check the execution plan again. Indexes for MyISAM tables do not carry the primary columns, so the plan should again probably be a table scan and not use the index.
Test C/D: same as A or B (add a 3rd column or convert the table to MyISAM) but run the query SELECT name FROM test WHERE name LIKE 'sun%';. The index should be used again as it is covering the query and is cheaper to use than a table scan.


What is the largest whole number in Oracle SQL table that can be stored without rounding errors?

Since ypercube suggested that the max_value << 10^100 I did a binary search.
 Oracle supports 39 digit precision for whole numbers.


Remove multiple joins to same table with where

Append to your query GROUP BY user_id. This has the effect that multiple user_ids become one row entry.

Additionally append HAVING COUNT(user_id)=2. As you've GROUP BY user_id the count of them represents that the number of field matches.


When does PostgreSQL move a WAL file to archive?

Who is writing to the replica's archive directory, the master (via scp or something like that) or the replica?

The replica only archives if archive_mode=always. If it just set to 'on', then only the master archives.

  What is the criteria by which the system decides that a WAL file is eligible to be moved from pg_xlog to the archive?

They are never moved.  They are copied to the archive.  And then at some later point (after at least a checkpoint), they are deleted/overwritten from pg_xlog/pg_wal.  The criteria for deletion are all of: a successful checkpoint/restartpoint, wal_keep_segments satisfied, all replication slots (if any) satisfied, archive_command (if in use) returns success.

  The backup/restore model is predicated around filesystem snapshots - so a restore operation would rely on crash recovery.

Who are you trying to restore here, the master or the replica?  I just recreate a replica if it gets lost, but some people find that too slow and want to be able to restore it.  And some people wouldn't recovery the master at all, they would just promote the replica.

  I infer from this that I should only need the artchived WAL files since the last file-system snapshot to be available (although keeping at least 2 generations would be a better idea).

Unless your file-system snapshot has itself been backed up somewhere, I would assume it is no longer available to be used after a major crash.  So you need all the WAL since the beginning of the most recent snapshot which has itself been backed up offsite.  If you want to retain the ability to do PITR, you need more than that.


How to UPDATE a column by COUNT of FK from another table?

You can do an UPDATE JOIN of table1 against a subquery that aggregates id counts in table2

UPDATE
    table1 A INNER JOIN
    (SELECT id,COUNT(1) idcount
    FROM table2 GROUP BY id) B
    USING (id)
SET A.number = B.idcount;

This query will not get every id. Why? If there is an id in table1 that is missing in table2, that does not write a zero in the numbers column.

To cover for id values missing in table2, run this one:

UPDATE
    table1 A LEFT JOIN
    (SELECT id,COUNT(1) idcount
    FROM table2 GROUP BY id) B
    USING (id)
SET A.number = IFNULL(B.idcount,0);

Give it a Try !!!


mySQL Group Replication Error "ERROR 3092 (HY000)"

Turns out the 2 servers had the same server UUID since one was cloned from the other. Deleting auto.cnf on one server and restarting fixed the problem.
Newbie error for sure!


How to migrate data from Google AppEngine Datastore

I think approcket can help you. It syncs data from GAE datastore to mysql. Is this what you ask for?


Is there a name for the class of characters represented by [] in SQL Server?

"I think" this is what you are referring to:

Delimited Identifiers

https://technet.microsoft.com/en-us/library/ms176027(v=sql.105).aspx

T-SQL Naming Conventions

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql


What port is used by SQL Server to write backups to a remote server?

Remote file access occurs over SMB. Remote backups are just another form of remote file access. See File and Sharing Protocol Stack.

If you use SMB over TCP directly (ie. disable NetBIOS) then the port used is 445, both UDP and TCP. If you use NetBIOS then it requires 137 both UDP and TCP, 138 UDP and 139 TCP. See Directly Hosting SMB over TCP/IP:

  NetBIOS over TCP traditionally uses the following ports:

nbname            137/UDP
nbname            137/TCP
nbdatagram        138/UDP
nbsession         139/TCP

  Direct hosted "NetBIOS-less" SMB traffic uses port 445 (TCP and UDP).


Having trouble with a query. DBMS Assignment

In SQL, you can join a table to itself.
Treat the two sides of the join as two separate tables and you're on your way to solving it.


Setting up a SQL Server cluster for the first time, some questions!

The network name is a virtual client access point for the instance of SQL Server on the failover cluster, and it needs to be unique on the domain.  This will be how you will connect to the instance.

The Cluster resource group is the name by which everything is grouped in the Windows Failover Cluster Manager, you will typically want this named similarly to what you named the instance.

The IP you provide will be tied to the network name you provided and needs to be unique as well.


I got the sollution,

1:extract your new mysql file :-
tar -zxf mysql-5.1.58.tar.gz

2:Rename the old errmsg.sys file which is in error file showing
mv /usr/share/mysql/english/errmsg.sys /usr/share/mysql/english/errmsg.sys.old

3:copy the new errmsg.sys file to exsting place
cp mysql-5.1.58/sql/share/english/errmsg.sys /usr/share/mysql/english/errmsg.sys

4:start your server.
 service mysql start

for Detail explanation kindly refer this link

http://toastergremlin.com/?p=447


Inserting data to a table which is being cleaned up by SQL job at the same time

If CreatedOn is not the clustered index (and especially if it is not indexed at all), and you are inserting new data sequentially (e.g. new rows have a newer CreatedOn), you should consider making that the clustered index. Then you shouldn't have any contention when you are deleting old data and inserting new data, since it will be on a completely different set of pages. Unless for some reason your inserts or the delete are escalating locks. If that is the case then instead of one big transaction:

DELETE [massive amount of rows]
  WHERE CreatedOn < [somedate];

You should consider breaking that up into chunks, as you suggested; but, just doing that in a loop and not adding any other changes won't have that much of an effect, since that may still be operating as a single transaction (and your problem might be exasperated by transaction log writes, and perhaps poor autogrowth settings for the transaction log). My method is typically as follows (with no "outer" transaction at play) - commit each set in its own transaction, and checkpoint or backup the log in between transactions. This lets user queries get in between your transactions and also reduces the impact on the log. Note that I picked 1000 arbitrarily; that might not be the right number for your scenario, but I'm pretty sure 10 is not.

DECLARE @somedate DATE = DATEADD(DAY, -1, GETUTCDATE());

BEGIN TRANSACTION;

SELECT 1;

WHILE @@ROWCOUNT > 0
BEGIN
  COMMIT TRANSACTION;

  -- if in simple recovery: CHECKPOINT;
  -- otherwise: BACKUP LOG ...;

  BEGIN TRANSACTION;

  DELETE TOP (1000) dbo.MyTable
    WHERE CreatedOn < @SomeDate;
END

But again, this will help your process most if CreatedOn is clustered, or at least indexed (having no context I don't know if your overall workload would be better off with that as clustered, but I do know that this query will work much better if it is).


POWA analyzer displays error 500 after logging in

There are additional requirements for database objects documented at https://powa.readthedocs.io/en/latest/security.html.
Let me know if there are any problems after applying those.


Extract four digits from a column by regexp

If the four digits you need to extract are either exactly at the end of the string or are followed only by spaces, it would probably be easiest to extract them using the RIGHT() function after removing the trailing spaces using the RTRIM() function:

SELECT
  RIGHT(RTRIM(YourColumnName), 4) AS SomeCode
FROM
  YourTableName
WHERE
  ...
;


Should I use DynamoDB or stick with MySQL via webservice for my mobile app syncs

I don't have experience with DynamoDB so the best I can do is offer some pointers on optimizations to look for if you stay in the hosted MySQL world.

Memory allocation.  If you're using innodb be sure you have adequate buffer pool size with two log files as the lesser of 25% buffer pool size or 1 Gig.  In a perfect world your buffer pool would be as large as all the .ibd files (aka sum(data_length+index_length) from information_schema.tables where engine='innodb').  Understandably this is not always possible, in which case try to allocate 60-70% of your system memory to buffer pool while leaving the rest available for disk cache.  (This of course assumes this is a dedicated MySQL server not hosting apache or running your php scripts).

If you're running MyISAM tables then at least make sure your key_buffer_size can accommodate the index_length (or size of your .MYI files).

Disks.  If you're needing to fall back to disk you want those to be as fast as possible.  In a perfect world you'd have PCI-E based SSD cards.  I imagine this is out of your budget if you're worried about charging after switching to amazon, so maybe see if your hosting provider has any options above what you currently have.  SSD based SAS drives > 15k hdd sas > nl-sas.
Service layer between mysql and clients.  There is memory overhead to each connection to the database that could be used toward buffer pool or disk cache.  Try to have a service layer that maintains a bounded connection pool and providing application layer caching where possible.
Distribute the work load.  This is tantamount to going to the "cloud" just that you're managing all machines yourself.  If your app is mostly read heavy and not always required to be up to the microsecond up to date you can setup replication between many physical machines and load balance the reads across them.

Look toward getting slaves setup at different physical datacenters across the country/world/what ever's applicable to your demographic.  Load balance connections to the nearest one.  (This assumes traffic is first routed to your front end in these locations otherwise the problem might just be worsened).

Partitioning.  This is especially true if you're using myisam and dealing with table level locks.  It's also particularly useful if you have a table that has patterns of pruning old rows periodically (you can just drop partitions instead of running individual delete queries which fragment the data).


Unable to connect to specific instance of Oracle RAC

If your TNS entry is incorrect for your current configuration, for example you specify (SERVER = SHARED) in your connection descriptor, but you have not configured shared servers, you will receive this error.

But since you said you had not changed anything and this error had occurred suddenly, I am going to assume your configuration is correct. In that case, the database instance may have reached the limit for the processes it can start. You should check the alert log for ORA-00020 errors, and query the highest ever (since the last restart) process utilization of your instance:

select max_utilization, limit_value from gv$resource_limit where inst_id = 1 and resource_name = 'processes';

If you find ORA-00020 errors, or max_utilization equals to limit_value in the output of the above query, the instance reached the limit at some point, and you may need to increase the value of the processes parameter or investigate the reason for having such number of processes.


Postgresql extension pageinspect - mean bt_metap

src/include/access/nbtree.h:

/*
 * The Meta page is always the first page in the btree index.
 * Its primary purpose is to point to the location of the btree root page.
 * We also point to the "fast" root, which is the current effective root;
 * see README for discussion.
 */

typedef struct BTMetaPageData
{
    uint32      btm_magic;      /* should contain BTREE_MAGIC */
    uint32      btm_version;    /* should contain BTREE_VERSION */
    BlockNumber btm_root;       /* current root location */
    uint32      btm_level;      /* tree level of the root page */
    BlockNumber btm_fastroot;   /* current "fast" root location */
    uint32      btm_fastlevel;  /* tree level of the "fast" root page */
} BTMetaPageData;

So I suggest reading src/backend/access/nbtree/README


Is it possible to disable the remember password checkbox in SSMS?

No, this is not possible, unless you reverse engineer SSMS and either hide the checkbox or make it a no-op, then recompile and deploy. And repeat every time you upgrade, apply a service pack, etc. (To be clear: this is not what I recommend.)

I have a better solution, though:

Why don't your 4-5 employees use different profiles? This takes a tiny bit more disk space, sure, but each profile gets its own SqlStudio.bin - then you could use Windows Authentication, too, which means they don't have to type their password or check a box, and it simplifies auditing, too, in case this isn't the most egregious thing they do.


Select data divided in groups evenly distributed by value

Here's a stab at an algorithm. It's not perfect, and depending on how much time you want to spend refining it, there are probably some further small gains to be made.

Let's assume you have a table of tasks to be performed by four queues. You know the amount of work associated with performing each task, and you want all four queues to get an almost equal amount of work to do, so all queues will complete at about the same time.

First off, I'd partition the tasks using a modulous, ordered by their size, from small to large.

SELECT [time], ROW_NUMBER() OVER (ORDER BY [time])%4 AS grp, 0

The ROW_NUMBER() orders every row by size, then assigns a row number, starting at 1. This row number is assigned a "group" (the grp column) on a round-robin basis. First row is group 1, second row is group 2, then 3, the fourth gets group 0, and so on.

time ROW_NUMBER() grp
---- ------------ ---
   1            1   1
  10            2   2
  12            3   3
  15            4   0
  19            5   1
  22            6   2
...

For ease of use, I'm storing the time and grp columns in a table variable called @work.

Now, we can perform a few calculations on this data:

WITH cte AS (
    SELECT *, SUM([time]) OVER (PARTITION BY grp)
             -SUM([time]) OVER (PARTITION BY (SELECT NULL))/4 AS _grpoffset
    FROM @work)
...

The column _grpoffset is how much the total time per grp differs from the "ideal" average. If the total time of the all tasks is 1000 and there are four groups, there should ideally be a total of 250 in each group. If a group contains a total of 268, that group's _grpoffset=18.

The idea is to identify the two best rows, one in a "positive" group (with too much work) and one in a "negative" group (with too little work). If we can swap groups on those two rows, we could reduce the absolute _grpoffset of both groups.

Example:

time grp total _grpoffset
---- --- ----- ----------
   3   1   222         40
  46   1   222         40
  73   1   222         40
 100   1   222         40
   6   2   134        -48
  52   2   134        -48
  76   2   134        -48
  11   3   163        -21
  66   3   163        -21
  86   3   163        -21
  45   0   208         24
  71   0   208         24
  92   0   208         24
----
=727

With a grand total of 727, each group should have a score of about 182 for the distribution to be perfect. The difference between the group's score and 182 is what we're putting in the _grpoffset column.

As you can see now, in the best of worlds, we should move about 40 points worth of rows from group 1 to group 2 and about 24 points from group 3 to group 0.

Here's the code to identify those candidate rows:

    SELECT TOP 1 pos._row AS _pos_row, pos.grp AS _pos_grp,
                 neg._row AS _neg_row, neg.grp AS _neg_grp
    FROM cte AS pos
    INNER JOIN cte AS neg ON
        pos._grpoffset>0 AND
        neg._grpoffset<0 AND
        --- To prevent infinite recursion:
        pos.moved<4 AND
        neg.moved<4
    WHERE --- must improve positive side's offset:
          ABS(pos._grpoffset-pos.[time]+neg.[time])<=pos._grpoffset AND
          --- must improve negative side's offset:
          ABS(neg._grpoffset-neg.[time]+pos.[time])<=ABS(neg._grpoffset)
    --- Largest changes first:
    ORDER BY ABS(pos.[time]-neg.[time]) DESC
    ) AS x ON w._row IN (x._pos_row, x._neg_row);

I'm self-joining the common table expression that we created before, cte: On one side, groups with a positive _grpoffset, on the other side groups with negative ones. To further filter out which rows are supposed to match each other, the swap of the positive and negative sides' rows must improve _grpoffset, i.e. get it closer to 0.

The TOP 1 and ORDER BY selects the "best" match to swap first.

Now, all we need to to is add an UPDATE, and loop it until there's no more optimization to be found.

TL;DR - here's the query

Here's the complete code:

DECLARE @work TABLE (
    _row    int IDENTITY(1, 1) NOT NULL,
    [time]  int NOT NULL,
    grp     int NOT NULL,
    moved   tinyint NOT NULL,
    PRIMARY KEY CLUSTERED ([time], _row)
);

WITH cte AS (
    SELECT 0 AS n, CAST(1+100*RAND(CHECKSUM(NEWID())) AS int) AS [time]
    UNION ALL
    SELECT n+1,    CAST(1+100*RAND(CHECKSUM(NEWID())) AS int) AS [time]
    FROM cte WHERE n<100)

INSERT INTO @work ([time], grp, moved)
SELECT [time], ROW_NUMBER() OVER (ORDER BY [time])%4 AS grp, 0
FROM cte;

WHILE (@@ROWCOUNT!=0)
    WITH cte AS (
        SELECT *, SUM([time]) OVER (PARTITION BY grp)
                 -SUM([time]) OVER (PARTITION BY (SELECT NULL))/4 AS _grpoffset
        FROM @work)

    UPDATE w
    SET w.grp=(CASE w._row
               WHEN x._pos_row THEN x._neg_grp
               ELSE x._pos_grp END),
        w.moved=w.moved+1
    FROM @work AS w
    INNER JOIN (
        SELECT TOP 1 pos._row AS _pos_row, pos.grp AS _pos_grp,
                     neg._row AS _neg_row, neg.grp AS _neg_grp
        FROM cte AS pos
        INNER JOIN cte AS neg ON
            pos._grpoffset>0 AND
            neg._grpoffset<0 AND
            --- To prevent infinite recursion:
            pos.moved<4 AND
            neg.moved<4
        WHERE --- must improve positive side's offset:
              ABS(pos._grpoffset-pos.[time]+neg.[time])<=pos._grpoffset AND
              --- must improve negative side's offset:
              ABS(neg._grpoffset-neg.[time]+pos.[time])<=ABS(neg._grpoffset)
        --- Largest changes first:
        ORDER BY ABS(pos.[time]-neg.[time]) DESC
        ) AS x ON w._row IN (x._pos_row, x._neg_row);


Azuredb The database 'tempdb' has reached its size quota

Limits depend from your service trier, in this sitiation it is no less than 250 GB for database, but it is only 32 MB for tempdb attached to this database. You can check other available resources here Azure SQL Database resource limits and information about Azure tempdb here Tempdb database in SQL Database.

There are several options which allow you to investigate what is going on with your tempdb. It is hidden, but you can still query sys objects to retrive information about this kind of databases. Try to check following resources:

Lesson Learned #10: Monitoring TempDB usage (useful answer in
comment, one tempdb per database)
How to identify which query is
filling up the tempdb transaction log?
How to Monitor tempdb use

When you find out what is consuming tempdb resources, you should be able to fix it or extend your question with additional information.

As you said, your file is 85 MB and we can see that it exceed max tempdb data size. Try to work with smaller chunks of data at a time. You should also consider BULK insert in this scenario.


Why get queryplans recompiled every minute

After some extended research I found the cause of my problem.
So to calculate the cache pressure limit SQL Server uses following formula:

  75% of visible target memory from 0-4GB + 10% of visible target memory from 4GB-64GB + 5% of visible target memory >64GB

Since SQL Server 2012 and above, memory pressure is triggered when a cache store grows to 62.5% of the plan cache pressure limit.
When this happens, plans are removed from the cache using an algoritm called eviction policy, which is based on plan cost.
So at certain moments I see a drop in the size of my plan cache and this scenario happens several times in a day, causing every time the compilation of new plans.


Query result in a daily date from a date range selection Postgres 9.3

All that you have to add is the day (be it the start or the end date).  This can be obtained using a cast to date, for example.  When you add it to the query, you'll need a GROUP BY clause, too, on this expression - that will produce you the daily aggregates.
The whole query will look like
SELECT created_at::date AS start_date,
       created_at::date + 1 AS end_date,
       COUNT(case when type = 'Completed Sale' then id end) AS complete,
       COUNT(case when disposition = 'Partial Sale' then id end) AS partial,
       SUM(gross) AS revenue
FROM sales
WHERE created_at >= '2015-09-20 00:43:32' AND created_at <= '2015-09-24 18:43:32'
GROUP BY created_at::date;

Notes:

using start_date and end_date like this can be slightly confusing - in normal life one expects the end date to be inclusive.  With the way you use it might not be clear which data belong to which day - I would simply omit the end date.
in the GROUP BY clause it's enough to mention the cast expression only once - adding one day in another columns does not result in a different grouping.


replicate data from one db to another db server using automated process

Do not try to make it harder than it actually is. Replication mechanism build-in mySQL will be the best for that. There is a ton of tutorials in google, ex.:

https://fromdual.com/how_to_setup_mysql_master-slave_replication

Basically, You need master-slave configuration.


What is a better way to keep "user online"?

The fastest and easiest to scale query is the query you don't run.

Move your session state to a cache tier like memcached or Redis.
Update the cached value on every user interaction, not the database value.
Persist to the database via a background task that periodically scans the cache for updates.


The obvious solution is to have your tool generate the output in the proper format.

If that is not possible, just use dos2unix or unix2dos on the file before processing, depending on the direction you need. Both utilites are provided as part of the dos2unix package that can be installed from the default Linux repositories, or downloaded seperately.

Other alternatives involve perl, sed or tr: https://en.wikipedia.org/wiki/Unix2dos

unix2dos:

perl -i -p -e 's|[\r\n]+|\r\n|g' file

  or

sed -i -e 's/\r*$/\r/' file

dos2unix:

tr -d '\r' < file > file2 # For ASCII and other files which do not contain multibyte characters (Not utf-8 safe).

  or

perl -i -p -e 's/\r//g' file

  or

sed -i -e 's/\r//g' file


Delete query from SQL Server 2008 to linked Postgres

DELETE data on linked server if user rights maped properly
run the same as normal SQL DELETE command, with full object names:

DELETE FROM [PGSQL].[demodata].[dbo].[tbl_Master]
SELECT * FROM [PGSQL].[demodata].[dbo].[tbl_Master]

  [Name of Linked Server].[database].[schema].[table]


Execution time of triggers fired when a procedure is executed in postgresql

You can use pg_stat_statements extension to collect stats over all queries in your db. You can indirectly see to total runtime of INSERT/UPDATE OR DELETE for needed table.


Why is part of transaction committed when stored procedure faults?

Transactions don't automatically roll back on error--that's not what they are designed to do. They are designed to give you the ability to rollback. However, you still need to do something to make that happen.
As you mention, you can make that happen through TRY...CATCH, which gives you the most control over if and how you can rollback.
It sounds like you are expecting the behavior of SET XACT_ABORT ON, which you can set in your stored procedure, but is not the default behavior. The description of setting XACT_ABORT on vs off from the docs is:

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.

When SET XACT_ABORT is ON, calling a faulting procedure returns messages in SSMS like:
Msg 8152, Level 16, State 14, Procedure spProcName, Line 298 [Batch Start Line 5]
String or binary data would be truncated.

Completion time: 2021-04-26T10:51:00.8420902+10:00

The first line is in red in SSMS.
When SET XACT_ABORT is OFF, calling a faulting procedure includes one additional message:
Msg 8152, Level 16, State 14, Procedure spProcName, Line 298 [Batch Start Line 5]
String or binary data would be truncated.
The statement has been terminated.

Completion time: 2021-04-26T10:53:52.5951780+10:00

In particular, when you see the following message, it means statements before the faulting statement within the procedure have been committed:
The statement has been terminated.


Sharing certificates encryped by password between DBs and instances

You only specify the password to decrypt the private key file with. You need to add a password to store the certificate with:

CREATE CERTIFICATE ERecruitStatsGatheringCert
ENCRYPTION BY PASSWORD = 'S3creT!
FROM FILE = 'd:\DavesCert.cer'
WITH PRIVATE KEY (
    FILE = 'd:\DavesCert.pvk',
    DECRYPTION BY PASSWORD = 'S3creT!');

But your Background information makes the whole exercise futile. You're doing it wrong. The correct sequence of actions is:

Create a cert in the user DB
Add the signature to the store procedure(s)
Drop the private key of the certificate
Backup/restore that cert to master (public key only!)
Create login in master, assigned that permission, etc.

Notice that not only the private key never leaves the database, is in fact explicitly dropped right after signing the procedure. This is required in order to prevent further use of this certificate to sign other procedures and abuse the login permissions created at step 5. You repeat these steps on each database and use a different certificate on each database. You repeat these steps each time you modify any of the signed procedure and generate a new certificate each time.

As a general rule, any signature/encryption public/private key (RSA) scheme that requires a copy of the private key is broken. This is why is called private and this is what gives value to the signature: the knowledge that there exists only one instance of this private key in the world, therefore anything signed by it is proof that it came from the one and only owner/holder of the unique private key.

I know there are some who shun my recommendation of dropping the private key right after is used to sign the procedure. I stand by my recommendation, but the important thing for your problem is that it is not required to copy the private key into [master] in order to leverage the the code signing permission you desire. You can (and should) create the login derived from the certificate using just the public key.

Regarding automation: As I see it, basically it is a tool (script, app) that takes two inputs: a securable (procedure, function, module) and a permission (VIEW SERVER STATE). Everything else is an automated process (create a one-time-use throw-away cert, sign, export cert, import cert in [master], create login, grant permission). The names implied (cert name, login name) can all be generated.

Proliferation of names is a valid concern. An alternative would be to use only one certificate and associated private key, and the signature tool could add the private key when needed (when signing the procedure), then remove it after the signing. Is really a matter of how you run your shop and how important is the asset you're protecting. But the important thing is that you do not need to import the private key in [master].


How can I get the measures of the last month with MDX?

Without sample data or structure it would be hard to provide the exact code, but here goes.

You need to look into the PARALLELPERIOD function. That function takes a Level from a hierarchy and navigates back a number of steps in that level.

Also, I'm using the assumption here that you want to use the TAIL() function to get the last month in your dimension with an actual value in your measure.

I whipped up an example in the AdventureWorks database that I think is getting you what you want (which still isn't entirely clear)

WITH MEMBER [Measures].[LastmonhtValue]
as
sum(PARALLELPERIOD([Date].[Calendar].[Month],1),[Measures].[Customer Count])

SELECT
    [Measures].[LastmonhtValue] ON 0,
 TAIL(NonEmptyCrossJoin([Date].[Calendar].[Month].Members,[Measures].[Customer Count],1),1) on 1
  FROM [Adventure Works]

The way this works:
- the parallelperiod function moves back one month
- the tail function uses the regular measure to get you the last nonempty month
- the result is 1 month with previous month's data

This is how the data looks when you browse the cube using 'month' and 'customer count'

This is the result of the code:

Barring any additional information from your side I'm afraid that is the best I can do.

Be sure to design your attribute relationships in your time dimension to make sure this code has acceptable performance