How does the actual restoration works in SQL Server internally?

I think this is the core methodology of database backup and restoration and the whole purpose of taking backup is to be able to restore it to point in time in case of any untoward incident like disk failure, server failure or database crash etc.

Lets understand them one by one.

Full Backup

A full backup, as the name implies, backs up everything. It is the foundation of any kind of backup. This is a complete copy, which stores all the objects of the database: Tables, procedures, functions, views, indexes etc. Having a full backup, you will be able to easily restore a database in exactly the same form as it was at the time of the backup.

A full backup creates a complete backup of the database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one single backup.

A full backup must be done at least once before any of the other types of backups can be run—this is the foundation for every other kind of backup.

Differential Backup

A differential database backup is the superset of the last full backup and contains all changes that have been made since the last full backup. So, if there are very few transactions that have happened recently, a differential backup might be small in size, but if you have made a large number of transactions, the differential backup could be very large in size.

As a differential backup doesn’t back up everything, the backup usually runs quicker than a full backup. A differential database backup captures the state of the changed extents at the time that backup was created. If you create a series of differential backups, a frequently-updated database is likely to contain different data in each differential. As the differential backups increase in size, restoring a differential backup can significantly increase the time that is required to restore a database. Therefore, it is recommended to take a new full backup, at set intervals, to establish a new differential base for the data.

Differential backups save storage space and the time it takes for a backup. However, as data changes over time, the differential backup size also increases. The longer the age of a differential backup and larger the size and at some point in time it may reach the size of the full backup. A large differential backup loses the advantages of a faster and smaller backup as it requires the full backup to be restored before restoring the recent differential backup. Typically, we would restore the most recent full backup followed by the most recent differential backup that is based on that full backup.

Transaction Log Backup

The log backup, as its name implies, backs up the transaction logs. This backup type is possible only with full or bulk-logged recovery models. A transaction log file stores a series of the logs that provide the history of every modification of data, in a database. A transaction log backup contains all log records that have not been included in the last transaction log backup.

It allows the database to be recovered to a specific point in time. This means that the transaction log backups are incremental and differential backups are cumulative in nature. If you want to restore the database to a specific point in time, you need restore a full, recent differential, and all the corresponding transaction log records which are necessary to build the database up to that specific point, or to a point very close to the desired point in time, just before the occurrence of the accident that resulted in the data loss. This series of modifications is contained and maintained using LSN (Log Sequence Number) in the log chain. A log backup chain is an unbroken series of logs that contain all the transaction log records necessary to recover a database to a point in time. A log chain always starts with a full database backup and continues until for reason it breaks the chain (for example, changing the recovery model of database to simple, or taking an extra full backup), thus by preventing log backups from being taken on the database until another full (or differential) backup is initiated for that database.

For having more clarity, you can refer below image:

Below image will show how to use SSMS for point in time restoration once you are done restoring Full, Differential and prior log backups:

Log shipping works on same concept however Full and differential backup restoration is part of initial set up whereas log backup and restoration works on the LSN part. Each log backup at primary servers maintains last LSN, same is compared at Secondary server and applied accordingly.

As far as internals of database restoration is concerned, you may find more details of it at these links:

Basically they go through three phases in case of any failure or non-clean shutdown:

Analysis Phase analyzes the transaction log to determine what is the last checkpoint, and creates the Dirty Page Table (DPT) and the Active Transaction Table (ATT). The DPT contains records of pages that were dirty at the time the database was shut down. The ATT contains records of transactions that were active at the time the database was not cleanly shut down.
Redo Phase rolls forwards every modification recorded in the log that may not have been written to the data files at the time the database was shut down. The minimum log sequence number (minLSN) required for a successful database-wide recovery is found in the DPT, and marks the start of the redo operations needed on all dirty pages. At this phase, the SQL Server Database Engine writes to disk all dirty pages belonging to committed transactions.
Undo Phase rolls back incomplete transactions found in the ATT to make sure the integrity of the database is preserved. After rollback, the database goes online, and no more transaction log backups can be applied to the database.

When we are restoring database from Full and Differential backup and Log backup, without using recovery command i.e. with NORECOVERY 2nd phase will be performed which is redo upto the last committed transactions. When you issue command for recovery i.e. in the last log backup restoration or point in time restoration, undo will be performed and no more log backups can be applied afterwards.

First phase i.e. analysis phase is when database comes up from the failure or unclean shutdown and won't be applicable in case of backup restoration.

Hope above helps.


I'm gonna lose the theory for a minute and remind you that you're lacking a table. Bands don't have labels; record(ing)s do. A band can, throughout its career, publish recordings on many labels.

If, when Metallica left Megaforce to sign with Elektra, you simply changed the value in the 'label' foreign key column, you'd have no record (no pun intended) of the fact that they were ever with Megaforce. Actually, the albums Ride The Lightning and Kill 'Em All are what should be associated with the label Megaforce, while Master Of Puppets is associated with Elektra.

BandID:
  pk in tblBand
  fk in tblAlbum

LabelID:
  pk in tblLabel
  fk in tblAlbum


Fixed it I just noticed the SQL Management Studio did something crazy and assigned the foreign key to itself

I corrected with this

ALTER TABLE [dbo].[REQUEST]  WITH CHECK ADD  CONSTRAINT [FK_REQUEST_REPORT] FOREIGN KEY([RQ_RP_ID])
REFERENCES [dbo].[REPORT] ([RP_ID])
GO


Max redo speed on replica side in Availability Group

"Heard about"? From where? As far as I know this is dependent on local factors such as raw disk subsystem capability, controller capacity, concurrency, type of data being logged, etc. etc.

Redoing large index maintenance operations on AGs is definitely a known issue. This post certainly admits as much and suggests ways to mitigate the issues:

Recommendations for Index Maintenance with AlwaysOn Availability Groups

But I don't think you can expect any predictable x MB/sec never mind a guarantee, you will have to observe what your system is capable of, and troubleshoot issues when it falls below your threshold range.


Can I "Execute T-SQL Statement Task" against all user databases?

This doesn't address your primary question but I can't help but propose a different approach than the one you're currently taking. You should be backing up the transaction log frequently enough that, in the event of a disaster, you won't lose any more data than your SLA allows. If you can afford to lose up to 30 minutes of data, then you should probably be backing up the transaction log every 15 minutes. You can certainly do this through the maintenance plan wizard (including picking which databases to run on, and one option is definitely "All user databases"):

Your current approach of switching to simple, running a shrinkfile, then switching back to full, is quite self-defeating. You're in full recovery model but you're not getting any of the benefits from it. Backing up the log throughout the day will keep your t-log size down as backing up the log allows space within the file to be re-used. If you don't need point-in-time recovery, then switch to simple recovery. This allows the space within the log file to be re-used automatically. In either case you shouldn't have to manually shrink the file anymore - which is very expensive process to repeat every night (never mind one that impacts users every time it auto-grows during the day).


Compare a fragmented distributed database with a replicated distributed database.  A replicated database would contain all columns and all records on more than one database instance; whereas, the separate instances of a fragmented distributed database do not all contain the same columns and/or records.

Vertical Fragmentation:
A vertically-fragmented distributed database would consist of separate instances which do not share all columns / attributes.  For example, the database for the sales department might have a column for a customer's interests; however, the database in the accounting department would not need this column.

Horizontal Fragmentation:
A horizontally-fragmented distributed database would exist when separate database instances have a similar schema but contain different types of records.  For example, a company with two locations East and West might have two similar databases; however, the one instance would only contain customers in the East, the other only customers in the West.


I had the same - somehow a windows or SQL Server update changed something and a previously successful build started failing.
In my case it was access permissions to the folder containing the dat files that was failing.
I could run the bcp from the command line and it worked but it failed from SQL Server.
The SQL Service needs access to the folder, basically.
To test this, try running

EXEC master..xp_cmdshell 'DIR <your-dat-folder>'

and if that fails check your access permissions (and grant as needed).


Where to find the installer for SQL Server Native Client 11.0.5058?

SQL Server version 11.00.5058 is SQL 2012 SP2, so you need to download Microsoft SQL Server 2012 SP2 Feature Pack

There are several places where you can look up the exact version of a build number. Personally I use sqlserverbuilds but there are others.

Once you find the name of the build you can use that to google and it may lead you to the correct version of the installer or feature pack you need.

If you click download on that page it takes you to a list of packages you can select that looks like this:


Creating a GroupBy query to include a result for when no results match

The usual way to get a series in postgres is with generate_series. This function produces a series of integers or timestamps - you can use either but assuming your 'dates' are really timestamptz, here's how you might go about it if you are on 8.4 or above:

testbed:

create table sales(sales_date timestamptz, sales_amount numeric);
insert into sales(sales_date, sales_amount) values('2011-01-15 12:00', 100);
insert into sales(sales_date, sales_amount) values('2011-02-15 12:00', 240);
insert into sales(sales_date, sales_amount) values('2011-04-15 12:00', 400);
insert into sales(sales_date, sales_amount) values('2011-04-16 12:00', 30);

query:

with w as ( select month, sum(sales_amount) as total
            from (select date_trunc('month',sales_date) as month, sales_amount from sales) z
            group by month )
select to_char(month, 'fmMon') as month, coalesce(total, 0) as total
from (select generate_series(min(month), max(month), '1 month'::interval) as month from w) m
     left outer join w using(month);

result:

 month | total
-------+-------
 Jan   |   100
 Feb   |   240
 Mar   |     0
 Apr   |   430

--edit: a bit of extra detail on the query:

produce a summary of sales by month (but no month present if no sales):

with w as ( select month, sum(sales_amount) as total
            from ( select date_trunc('month',sales_date) as month, sales_amount
                   from sales ) z
            group by month )

which could alternatively be written as:

with w as ( select date_trunc('month',sales_date) as month, sum(sales_amount) as total
            from sales
            group by date_trunc('month',sales_date) )

produce an unbroken series of months (without sales) from the minimum to the maximum:

select generate_series(min(month), max(month), '1 month'::interval) as month from w

outer join the unbroken series to the summary of sales by month to produce an unbroken series with sales (or null sales if no sales present):

left outer join w using(month)

for the months with null sales, change the null to a 0:

coalesce(total, 0)


ora-12705 cannot access NLS data files or invalid environment specified

Thanks everyone,

finally after trying many steps on changing registry and also environment variable in windows property, without any luck I ignored database creation during installation and proceed with installation without creating database.

after installation is done I manually created database by launching DBCA and all went well without any error in NLS_LANG.

hope this is helpful to someone else

regards


Never heard of a waterfall join, but based on your description:

SELECT A.*, COALESCE(B.col3, C.col3) AS col3 ...
  FROM A
  LEFT JOIN B
    ON A. col1 = B.col1
    AND A.col2 = B.col2
  LEFT JOIN B AS C
    ON A.col1 = C.col1
    AND B.col1 IS NULL;


How to verify (CHECKSUM or MD5) dataset you received over a network?

Why?

If there is a hardware error in the machine, all sorts of things will be dying, not just your one query.

If you are concerned about the network, see all the checksums, etc, involved in TCP/IP and Ethernet.  You don't need a layer of checks on top of that.

If you are asking about consistency between Master and Slave in replication, then say so; that gets into a different area of things.

Bottom line:  Don't worry.  Resultsets are corrupted about as often as you get hit by a meteorite.


Primary Key and Clustered Index are really separate concepts and, although many tables put both of these attributes on the same constraint or index, this is not a requirement.

I do not see how changing the Clustered Index to the make it also the Primary Key will particularly help your performance.

It sounds like you have a Primary Key (BIGINT) already and that it is physically implemented through a unique non-clustered index.  This should be good for fairly quick lookups of the Primary Key since it would be a relatively narrow index.

As you have it defined now, the skewed Clustered Index is mostly the same special non-NULL value, with about 17.5K rows.

Since you identify the Clustered Index as a likely pain point, you might look into whether a Filtered Index or an INDEXED VIEW would provide the needed performance improvement.

Sample of a Filtered View based on MSDN, like this:

CREATE NONCLUSTERED INDEX YourNonClusteredFilteredIndex
    ON dbo.YourTable (ClusterKeyCol)
    WHERE ClusterKeyCol IS NOT NULL; -- Or <> {special value}
GO

Sample of an Index View based on MSDN, something like this:

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE VIEW dbo.YourClusteredView
WITH SCHEMABINDING
AS
    SELECT ClusterKeyCol, PrimaryKeyCol
    FROM dbo.YourTable
    WHERE PrimaryKeyCol <> {special value};
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX YourClusteredIndex
    ON dbo.YourClusteredView (ClusterKeyCol);
GO

If you have a more detailed problem description, as John M asked, then perhaps some more targeted help can be provided.


Things to keep in mind while editing Mysql "my.cnf" file

There are a lot of inter-dependencies.  Casual use of the Query cache can speed up some queries.  Busy systems tend to be slowed down by the QC.

Overcommitting RAM is deadly for performance.  Here is a discussion of that, and some of the settings.

32bit system with 8GB of RAM ! -- Is that a 32-bit OS?  Or a 32-bit MySQL on a 64-bit OS?  If the former, then 4GB is totally idle.  In either case, MySQL is constrained as if it has only 4GB.

What kinds of things slowed down when you changed what settings?  We can probably explain what happened if you give us some details.


Generally, for the case where NULL can only mean that the column should not be updated, the pattern is as follows:

UPDATE dbo.table
  SET col1 = CASE WHEN @param1 IS NOT NULL THEN @param1 ELSE col1 END,
      col2 = CASE WHEN @param1 IS NOT NULL THEN @param1 ELSE col2 END,
  WHERE key = @key;

As Erik points out, there may be considerations for whether it is worth testing if the value has changed before invoking an UPDATE:

The Impact of Non-Updating Updates

In my testing, I have found that it is typically not worth checking first, but I did not take into account all of the little details Paul mentioned (I ignored LOB columns, for example):

Checking if a non-LOB column needs to be updated

And you don't need a transaction around a single UPDATE. It is by definition a transaction - it will either succeed or fail.

If you want to allow the user to override an existing value with NULL, then for those columns you could potentially pass a token value that would never appear in the data naturally, like a poop emoji. For example:

UPDATE dbo.table
  SET col1 = CASE WHEN @param1 = N'' THEN NULL
                  WHEN @param1 IS NOT NULL THEN @param1
                  ELSE @param1 END,
  ...

Or you could be more complex and literal, adding an additional parameter per nullable column, like

...
@param1 nvarchar(32) = NULL,
@ForceParam1Null bit = 0,
...
  UPDATE dbo.table
    SET col1 = CASE WHEN @param1 IS NULL
                     AND @ForceParam1Null = 1 THEN NULL
                  WHEN @param1 IS NOT NULL THEN @param1
                  ELSE @param1 END,
    ...


Best practice guidelines for a application table archive in Oracle 12C

You can use Flashback Data Archive to keep track of previous versions of a table. With that feature you can query the table as it looked at any point in time (within your retention period). That does sound like a more flexible approach than the one you are building (re-inventing the wheel).

1 and 2 does sound very straightforward good ideas. 3 depends on the amount of data you are loading. unusable indexes will hurt your application during the load.
I would consider putting each load into a separate partition. Then it is very easy to get rid of data after a year - just drop the entire partition. And I assume that you often will look at data at a specific point in time - in which case the optimizer can use partition pruning if you construct the SQL correctly.


creating a multi table "identity" column (faux identity)

If the version is at least 2012 use a SEQUENCE


Yes you can use MyISAM for storing the geo location as they support the spatial data types.For more information please look Saptial Datatypes in MySQL

And You can also use the mix of engines there is no issues on that..


MSQL state "closing tables" taking double the time of a process

Closing tables this thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, you should verify that you do not have a full disk and that the disk is not in very heavy use.

This bug report can be related as well.

Further,

MySQL closes an unused table and removes it from the table cache under the following circumstances:

When the cache is full and a thread tries to open a table that is not in the cache.
When the cache contains more than table_cache entries and a table in the cache is no longer being used by any threads.
When a table flushing operation occurs. This happens when someone issues a FLUSH TABLES statement or executes a mysqladmin flush-tables or mysqladmin refresh command.

You can determine whether your table cache is too small by checking the mysqld status variable Opened_tables, which indicates the number of table-opening operations since the server started:

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, you should increase the table cache size
For more information refer how MySQL opens and closes a tables here:

http://dev.mysql.com/doc/refman/5.0/en/table-cache.html


Which collation should I use for biblical Hebrew?

First: There is no distinction, collation-wise, between biblical Hebrew and modern Hebrew. We are just dealing with Hebrew.

Second: Regardless of anything else, you want to use the newest set of collations, which are the _100_ series as they have newer / more complete sort weights and linguistic rules than the older series with no version number in the name (technically the are version 80). Normally I highly recommend using the newest version of whatever collation you need, but at least in this one instance there is good reason to use the version with no version number in the name. The version 100 (or newer) collations are far more complete, and can distinguish between supplementary characters (or even fully support them if using an SC or 140 collation), but assuming that you are not dealing with supplementary characters, then the version 80 (no version number in name) collations do a better job of handling Hebrew (see item "Sixth" below).

Third: There is no concept of "Kana" (or Kana-type) in Hebrew, so you can ignore any of the collation variations with _KS in their names (as that is a sensitivity that you will never use).

Forth: The collations ending in _SC support supplementary characters (i.e. full UTF-16) so usually best to pick one of those, if available (meaning: if you are using SQL Server 2012 or newer).

Fifth: You don't want a binary collation (_BIN or _BIN2) as those can't distinguish between Hebrew letters with both vowels and cantillation marks that are the same but have the combining characters in different orders, nor can they ignore vowels and other marks to equate things like א and אֽ.

For example (vowel and cantillation mark combining characters in opposite order):

SELECT NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8),
       NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C)
WHERE  NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8) =
       NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C) COLLATE Hebrew_100_CS_AS_SC;
-- אָ֜  אָ֜

SELECT NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8),
       NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C)
WHERE  NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8) =
       NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C) COLLATE Hebrew_100_BIN2;
-- no rows

Sixth: It depends on how you will be interacting with the string values. Hebrew does not have upper-case/lower-case letters, yet there are a few code points that are affected by case-sensitivity. There are even a few code points that are width-sensitive. Accent-sensitive / insensitive affects diacritics used for vowels, pronunciation, and cantillation marks (i.e. trope).

Do you need to distinguish between final and non-final forms of the same letter? There are five letters in Hebrew that look different when used as the final letter of a word. SQL Server handles this via case-sensitivity / _CS collations (though, unfortunately, it appears to be broken in the newer, and usually better, version 100 and newer collations):

SELECT NCHAR(0x05DE) AS [Mem],
       NCHAR(0x05DD) AS [Final Mem]
WHERE  NCHAR(0x05DE) = NCHAR(0x05DD) COLLATE Hebrew_CI_AS_KS_WS;
-- 1 row (expected; all sensitive except case)
-- Mem  Final Mem
-- מ    ם

SELECT NCHAR(0x05DE) AS [Mem],
       NCHAR(0x05DD) AS [Final Mem]
WHERE  NCHAR(0x05DE) = NCHAR(0x05DD) COLLATE Hebrew_CS_AI;
-- no rows (expected; all insensitive except case)

SELECT NCHAR(0x05DE) AS [Mem],
       NCHAR(0x05DD) AS [Final Mem]
WHERE  NCHAR(0x05DE) = NCHAR(0x05DD) COLLATE Hebrew_100_CI_AI;
-- no rows (expected 1 row; all insensitive)

Do you need to distinguish between pronunciation marks, vowels, and cantillation marks? SQL Server handles this via accent-sensitivity / _AS collations (though, unfortunately, it appears to be broken in the newer, and usually better, version 100 and newer collations). Please note that all three of these are grouped together under accent-sensitivity and cannot be controlled separately (i.e. you can't do vowel sensitive but cantillation mark insensitive).

Pronunciation marks

There are several letters that have two different sounds. Sometimes the only indicator for which sound to use is the context of what word the letter is in (and sometimes even the surrounding words), such as in the actual Torah (where there are no pronunciation marks or vowels). But, that same text in other forms, as well as other text, will have dots placed either inside the letter, or for the letter Shin, above the letter. The letter Shin can have either an "sh" or "s" sound. To indicate the "sh" sound (i.e. letter "shin"), there is a dot above on the right side, while a dot above on the left side denotes the "s" sound (letter "sin"):

SELECT NCHAR(0x05E9) AS [Shin],                            -- ש
       NCHAR(0x05E9) + NCHAR(0x05C1) AS [Shin + Shin Dot], -- שׁ
       NCHAR(0x05E9) + NCHAR(0x05C2) AS [Shin + Sin Dot]   -- שׂ
WHERE  NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C1) COLLATE Hebrew_CS_AI_KS_WS
AND    NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C2) COLLATE Hebrew_CS_AI_KS_WS;
-- 1 row (expected; all sensitive except accent)

SELECT NCHAR(0x05E9) AS [Shin],                            -- ש
       NCHAR(0x05E9) + NCHAR(0x05C1) AS [Shin + Shin Dot], -- שׁ
       NCHAR(0x05E9) + NCHAR(0x05C2) AS [Shin + Sin Dot]   -- שׂ
WHERE  NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C1) COLLATE Hebrew_CI_AS
OR     NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C2) COLLATE Hebrew_CI_AS;
-- no rows (expected; all insensitive except accent)

SELECT NCHAR(0x05E9) AS [Shin],                            -- ש
       NCHAR(0x05E9) + NCHAR(0x05C1) AS [Shin + Shin Dot], -- שׁ
       NCHAR(0x05E9) + NCHAR(0x05C2) AS [Shin + Sin Dot]   -- שׂ
WHERE  NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C1) COLLATE Hebrew_100_CI_AI_SC
OR     NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C2) COLLATE Hebrew_100_CI_AI_SC;
-- no rows (expected 1 row; all insensitive)

Vowels

SELECT NCHAR(0x05D0) AS [Aleph],                           -- א
       NCHAR(0x05D0) + NCHAR(0x05B8) AS [Aleph with vowel] -- אָ
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05B8) COLLATE Hebrew_CS_AI_KS_WS;
-- 1 row (expected; all sensitive except accent)

SELECT NCHAR(0x05D0) AS [Aleph],                           -- א
       NCHAR(0x05D0) + NCHAR(0x05B8) AS [Aleph with vowel] -- אָ
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05B8) COLLATE Hebrew_CI_AS;
-- no rows (expected; all insensitive except accent)

SELECT NCHAR(0x05D0) AS [Aleph],                           -- א
       NCHAR(0x05D0) + NCHAR(0x05B8) AS [Aleph with vowel] -- אָ
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05B8) COLLATE Hebrew_100_CI_AI_SC;
-- no rows (expected 1 row; all insensitive)

Cantilation marks

Technically, according to official Unicode data, Hebrew cantillation marks are ignorable and should only register as a difference here when using a binary collation. However, SQL Server treats them the same as accents (unfortunately) and cannot ignore them separately from pronunciation marks or vowels.

SELECT NCHAR(0x05D0) AS [Aleph],                                       -- א
       NCHAR(0x05D0) + NCHAR(0x05A8) AS [Aleph with cantillation mark] -- א֨
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05A8) COLLATE Hebrew_CS_AI_KS_WS;
-- 1 row (expected; all sensitive except accent)

SELECT NCHAR(0x05D0) AS [Aleph],                                       -- א
       NCHAR(0x05D0) + NCHAR(0x05A8) AS [Aleph with cantillation mark] -- א֨
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05A8) COLLATE Hebrew_CI_AS;
-- no rows (expected; all insensitive except accent)

SELECT NCHAR(0x05D0) AS [Aleph],                                       -- א
       NCHAR(0x05D0) + NCHAR(0x05A8) AS [Aleph with cantillation mark] -- א֨
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05A8) COLLATE Hebrew_100_CI_AI_SC;
-- no rows (expected 1 row; all insensitive)

Do you need to distinguish between wide and non-wide forms of the same letter? There are eight letters in Hebrew that are stretched (wide) but only for the purpose of using in Torah scrolls (either hand-written / real or printed) to keep the fully-justified columnar format (which is actually how it appears in Torah scrolls). SQL Server handles this via width-sensitivity / _WS collations (interestingly-enough, it appears to be the only sensitivity that works correctly in the newer version 100 and newer collations, though sadly, it's the least likely to be used):

SELECT NCHAR(0x05DC) AS [Lamed],
       NCHAR(0xFB25) AS [Wide Lamed]
WHERE  NCHAR(0x05DC) = NCHAR(0xFB25) COLLATE Hebrew_CI_AI;
-- no rows (expected 1 row; all insensitive)

SELECT NCHAR(0x05DC) AS [Lamed],
       NCHAR(0xFB25) AS [Wide Lamed]
WHERE  NCHAR(0x05DC) = NCHAR(0xFB25) COLLATE Hebrew_100_CS_AS_KS_SC;
-- 1 row (expected; all sensitive except width)
-- Lamed    Wide Lamed
-- ל        ﬥ

SELECT NCHAR(0x05DC) AS [Lamed],
       NCHAR(0xFB25) AS [Wide Lamed]
WHERE  NCHAR(0x05DC) = NCHAR(0xFB25) COLLATE Hebrew_100_CI_AI_WS_SC;
-- no rows (expected; all insensitive except width)

So, perhaps Hebrew_CI_AI for the columns, and you can override per expression / predicate via the COLLATE statement if you need to use a variation, such as COLLATE Hebrew_CS_AI or Hebrew_CI_AS or Hebrew_CS_AS.

Additional Notes

You will need to store the data in NVARCHAR columns / variables. You could accomplish most of this in regular 8-bit VARCHAR using the Windows-1255 code page (which is what all Hebrew_* collations use), including combining characters for vowels and pronunciation dots:

;WITH Hebrew AS
(
    SELECT NCHAR(0x05E9) + NCHAR(0x05C1) + NCHAR(0x05B8)
           COLLATE Hebrew_100_CS_AS AS [Shin]
)
SELECT
  Hebrew.[Shin] AS [Unicode],
  CONVERT(VARCHAR(20), Hebrew.[Shin]) AS [CodePage1255],
  CONVERT(VARBINARY(10), CONVERT(VARCHAR(20), Hebrew.[Shin])) AS [CodePage1255_bytes]
FROM   Hebrew;
-- Unicode  CodePage1255    CodePage1255_bytes
-- שָׁ        שָׁ               F9D1C8

However, only the Unicode Hebrew block contains the cantillation marks (i.e. trope; code points U+0591 through U+05AF) plus a few extras (code points U+05C4 through U+05C7), and the Alphabetic Presentation Forms block contains the wide variant of several letters plus some other stuff.
Please see the official Unicode Standard (Version 12.1), Chapter 9: Middle East-I, Section 9.1: Hebrew, pages 361-366 (389-394 of the PDF) for a more detailed description of how Unicode handles Hebrew text.
According to the official Unicode CLDR (locale-specific tailorings) rules for the Hebrew ("he" and "he-IL") culture, U+05F3 HEBREW PUNCTUATION GERESH should either match or come before, U+0027 APOSTROPHE. Normally, U+05F3 sorts after apostrophe. This behavior is indeed seen when using the ICU collation demo and switching between the "root" / standard sort order (used by US English / "en-US") and "he". However, this behavior does not seem to be available in either .NET or SQL Server:

SELECT NCHAR(0x05F3)
WHERE  NCHAR(0x05F3) <= N'''' COLLATE Hebrew_100_CS_AS_KS_WS;
-- no rows

SELECT NCHAR(0x05F3)
WHERE  NCHAR(0x05F3) <= N'''' COLLATE Hebrew_CS_AS_KS_WS;
-- no rows

While an unfortunate omission, it does make sense given that I don't see any "he" or "he-IL" -specific tailorings in the Windows Sorting Weight Table files. This could very well mean that there is no actual difference between the Hebrew_* and Latin1_General_* collations outside of the associated code page, which is only used for VARCHAR data, and that does not apply here.
Please see related question and answer: How To Strip Hebrew Accent Marks

O.P. replied with:

  Yes, I need to distinguish between: 1) final and non-final forms of the same letter 2) pronunciation marks 3) vowels, and 4) cantillation marks.

In that case, since you don't need to ignore differences between those properties, you can use the 100 level collations. The example below shows a Hebrew letter (Sin) with a pronunciation mark, a cantillation mark, and a vowel. There are six versions so that each possible combination of ordering of the combining characters could be represented. There is a seventh entry that uses the other dot to create the letter Shin with the same base letter, vowel, and cantillation mark. The query shows that only the six "Sin" entries match each other (even with different byte orders), but not the "Shin".

I included the use of Latin1_General and Japanese_XJIS_140 collations to show that the rules work with those as well in case you need to make use of them (the 140 collations, only in Japanese, have more upper-case / lower-case mappings than older versions). But in general, probably best to stick with the Hebrew collations, and use a non-100 version if you need to ignore differences in vowels, marks, dots, and final vs non-final forms.

DECLARE @Shin   NVARCHAR(5) = NCHAR(0x05E9), -- base Hebrew letter
        @Dot    NVARCHAR(5) = NCHAR(0x05C2), -- Sin Dot
        @Mark   NVARCHAR(5) = NCHAR(0x05A8), -- Cantillation Mark (i.e. trope)
        @Vowel  NVARCHAR(5) = NCHAR(0x05B8); -- Vowel

DECLARE @Dot_Mark_Vowel NVARCHAR(20) = @Shin + @Dot + @Mark + @Vowel,
        @Dot_Vowel_Mark NVARCHAR(20) = @Shin + @Dot + @Vowel + @Mark,
        @Vowel_Dot_Mark NVARCHAR(20) = @Shin + @Vowel + @Dot + @Mark,
        @Vowel_Mark_Dot NVARCHAR(20) = @Shin + @Vowel + @Mark + @Dot,
        @Mark_Vowel_Dot NVARCHAR(20) = @Shin + @Mark + @Vowel + @Dot,
        @Mark_Dot_Vowel NVARCHAR(20) = @Shin + @Mark + @Dot + @Vowel,
        @ShinDot_Mark_Vowel NVARCHAR(20) = @Shin + NCHAR(0x05C1) + @Mark + @Vowel;

SELECT @Dot_Mark_Vowel AS [Sin], @ShinDot_Mark_Vowel AS [Shin];

;WITH chr AS
(
  SELECT *
  FROM   (VALUES
            (@Dot_Mark_Vowel, 'Dot + Mark + Vowel'),
            (@Dot_Vowel_Mark, 'Dot + Vowel + Mark'),
            (@Vowel_Dot_Mark, 'Vowel + Dot + Mark'),
            (@Vowel_Mark_Dot, 'Vowel + Mark + Dot'),
            (@Mark_Vowel_Dot, 'Mark + Vowel + Dot'),
            (@Mark_Dot_Vowel, 'Mark + Dot + Vowel'),
            (@ShinDot_Mark_Vowel, 'ShinDot + Mark + Vowel')
         ) tmp([Hebrew], [Description])
) SELECT chr1.[Hebrew],
         '--' AS [---],
         chr1.[Description] AS [Description_1],
         CONVERT(VARBINARY(20), RIGHT(chr1.[Hebrew], 3)) AS [Bytes_1],
         '--' AS [---],
         chr2.[Description] AS [Description_2],
         CONVERT(VARBINARY(20), RIGHT(chr2.[Hebrew], 3)) AS [Bytes_2]
  FROM   chr chr1
  CROSS JOIN chr chr2
  WHERE  chr1.[Description] <> chr2.[Description] -- do not compare item to itself
  AND    chr1.[Hebrew] = chr2.[Hebrew] COLLATE Hebrew_100_CS_AS_SC
  AND    chr1.[Hebrew] = chr2.[Hebrew] COLLATE Latin1_General_100_CS_AS_SC
  AND    chr1.[Hebrew] = chr2.[Hebrew] COLLATE Japanese_XJIS_140_CS_AS;

-- this query returns 30 rows


Recover accidentally deleted rows from a table

If your database is in full recovery mode you can also try third party tools such as ApexSQL Log or SQL Log Rescue.

These tools will attempt to read your transaction log and reconstruct statements.

You can also try reading transaction log manually using fn_dblog function but it’s going to be complex since this is not a well-documented function.


Should a privileged port (<1024) be used to deploy Postgresql in production?

Using a privileged port for Postgres would require the postgres daemon to run with root privileges, which in itself is a security vulnerability. So, no, you should not use a privileged port for Postgres.


Would it be sensible to create a MySQL user for each user account of a web application?

What type of logging would you be looking at gaining? What I do in my application is an audit table that tracks application user, application command, and result (among other things). This, along with another audit table that tracks individual table changes, provides all the logging we need per user.

You could also set up a connection pool in your application to control connections per application.

At first blush, I think trying to manage so many users is a burden that outweighs any possible benefits.


NVMe+bcache as huge storage cache for vertica database

Very interesting !

Not sure how this would work around Tuple Mover internal crunching ? Block change all the time.

my take

I guess a UDT to extract data from Vertica and flush it into memcahe or similar would also be a solution


Properly test queries that are slow on first run

Restarting Sql Server blows away the plan cache so the first time the query is executed, a new execution plan has to be built.  Check out Eight Different Ways to Clear the SQL Server Plan Cache.

Alternatively, you could try altering the text of your query by adding (or removing) some extra spaces or blank lines which should force a new plan to be built.

However, I'd be quite surprised if it took 35 to 51 seconds to build a new plan.

You can verify whether building a new execution plan is part of your extended execution time by issuing set statistics time on in a query session before you execute your query against an empty plan cache (or where you've made a slight change to the query text such that a new execution plan is required).  In the SSMS messages window, look for something like this:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

Additionally, have a look at Why is My Query Faster the Second Time it Runs?


Query missing one date; only happened today

I don't see how this query could ever have returned the current day.  ROWNUM starts with 1 so TRUNC(sysdate - rownum) will never return the current day and neither will TRUNC(sysdate + rownum).  Both sides of your UNION return exactly 32 rows so the entire query should always return 64 rows.  If you want 32 days before today, 32 days after today, and today in your result set, it would need to be 65 rows.

This query should return 65 rows and should include today.

SELECT TRUNC(SYSDATE - ROWNUM) DATEITEM
FROM   DUAL
CONNECT BY ROWNUM <= 32
UNION
SELECT TRUNC(SYSDATE + ROWNUM - 1) DATEITEM
FROM   DUAL
CONNECT BY ROWNUM <= 33
order by dateitem desc;

You can also write it without any UNION

SELECT TRUNC(SYSDATE - ROWNUM -33) DATEITEM
FROM   DUAL
CONNECT BY ROWNUM <= 65
order by dateitem desc;

From a performance standpoint, it is unlikely that there will be a meaningful difference between using one, two, or three subqueries.  If you are going to UNION multiple sets together, since you know there are no overlaps, you would want to use a UNION ALL rather than a UNION to avoid an unnecessary sort and DISTINCT (UNION has to remove duplicates, UNION ALL does not).


Using Galera Cluster(Percona XtraDB Cluster, MariaDB Cluster) for setting up multi master

it does not so far. I suggested it to Codership (the creator of Galera) to port it to Windows but somehow I did not have luck with my suggestion.

But we (fromdual.com) would be happy to help you setting it up and operating it on Linux?

Regards,
Oli


Does export in schema mode also includes tablespaces?

Yes it does.
https://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007519

Tablespace Mode
A tablespace export is specified using the TABLESPACES
parameter. In tablespace mode, only the tables contained in a
specified set of tablespaces are unloaded. If a table is unloaded,
then its dependent objects are also unloaded. Both object metadata and
data are unloaded. In tablespace mode, if any part of a table resides
in the specified set, then that table and all of its dependent objects
are exported.

e.g.
expdp hr DIRECTORY=pump DUMPFILE=tbs456.dmp TABLESPACES=tbs_4, tbs_5, tbs_6


SQLCMD -L Returns Bizarre Response Instead of Server List

This can happen with a computer, probably a laptop, that is not joined to the domain.


Simple Query optimization using ORDER BY

You should add one of these two indexes:

ALTER TABLE asset
ADD INDEX product_id_status_expiry_date(product_id, status, expiry_date);

This will help with the filtering.

ALTER TABLE asset
ADD INDEX expiry_date_serial_number(expiry_date, serial_number);

This will help with the filesort.

Only one of them can be used at a time, so add only the most advantageous.


psycopg2 connect error: cannot pass more than 100 arguments to a function?

The argument to callproc needs to be a list of parameters. Therefore, if you pass in
abc=json.dumps(stringObject)
cursor.callproc('insert_usr_details_in_all_tables', abc)

It will split the string. Instead use:
cursor.callproc('insert_usr_details_in_all_tables', [abc,])


I can explain why the first query is slow, but not why the second one is fast. :)

My theory is that ft_min_word_len is longer than "2" on your system (default is 4) so the "word" '96' is not actually making it into the full-text index... so you're doing a full table scan or at least you're having to find all of the rows containing 'hannover' in the index and then discarding the rows not matching the final criteria.  Unfortunately, EXPLAIN SELECT isn't as valuable as usual when it comes to queries involving a fulltext index.  It just tells you whether that index is going to be attempted, apparently.

Take a look at these:

https://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_ft_min_word_len

If you aren't, already, then you may want to use SELECT SQL_NO_CACHE ... when benchmarking queries... or you may be getting a response from the query cache that is deceptively speedy.


Upgrade in-place with low disk space on C: drive

Can you do it? Yes, you can. Should you do it? No, no shouldn't.

You're playing with fire, less than 10% free space on C drive is an urgent/critical flag in any decent company.

If you spot anything worth deleting, go for it. But in any case, since this is a physical server and you said Wintel team can't help you, present the scenario as an issue and a possible emergency in the future.


General ETL principle - ETL from unknown number of tables?

You're asking about an ETL process which accepts data from new tables without having to be adjusted?  In principal, sure, all things are possible.  In practice, writing this would be a challenge.

There are two main approaches to combining data from two or more tables: joins and unions.  These correspond to your two examples, where A, B, and C become one row (they're being joined) or three rows (they're being unioned).

Either way, your ETL process would need to have some kind of pattern with which to identify the sources.  For example, it could look for tables named "Orders" and a number.  You could write code to create dynamic SQL based on such a pattern, but having multiple nearly-identical tables differing only by suffix is generally a bad design.  There are some similar concepts, such as partitioning.

Alternatively, if your tables are substantially different, you're essentially talking about writing an artificial intelligence to recognize and combine related data.  This would be a huge project; suitable for a thesis, or DARPA maybe, but probably not a business with deadlines to meet.  Any solution it created would need to be vetted and tweaked by hand to be accurate, so why not just update the code yourself?

Is there a business goal you're trying to meet, or is this a matter of curiosity?


How to design table to allow marking records as related to each other?

A bridge table
related_id  Related_TO_ID
     13           2
     13          12

If 1-->2  is equal to 2-->1 you need one row else you need two.
Now You can get all ids that are related to 13
And if you want to exclude some ration ships you can add a column that indicates that 13 is never related to 42
SELECT Related_TO_ID FROM brdgetable WHERE related_id   = 13

Why you never should save data in delimitedcolumns you can read here


Collations are database objects, so their name is an identifier.

You cannot use a parameter for an identifier, you'd have to use dynamic SQL in a PL/pgSQL function:

RETURN QUERY EXECUTE
   format(
      'SELECT ... COLLATE %I ...'
      text_collation
   );


Updating statistics, what about existing execution plans

If you update statistics on a table that didn't have any modifications since the previous statistics update, your execution plans will not be invalidated.

If you update statistics AND data has been changed between this and previous statistics update then it will be invalidated if the database has the AUTO_UPDATE_STATISTICS database option set to ON. Note that affected plans are not immediately recompiled; each plan will recompile when it is next retrieved from cache for execution.

See this post by Kendra Little: Does Updating Statistics Cause a Recompile if No Data Has Changed?

  Finding: Statistics Update Alone Didn’t Cause a Recompile

  SQL Server was smart enough to check if the data had changed. Updating
  statistics alone doesn’t always invalidate execution plans.

And this post by Kimberly Tripp referencing a no longer available connect item:

  It’s NOT a bug, it’s BY DESIGN. And, it actually makes sense.

  If the plan should NOT be invalidated (directly due to statistics
  because the data has NOT changed) then it won’t. But… If the plan
  should be evaluated (statistics have been updated AND data changed)
  then it will.

Also see this post by Erin Stellato: Statistics and Recompilations which references this MSDN documentation page

  When the AUTO_UPDATE_STATISTICS database option is set to ON, queries
  are recompiled when they target tables or indexed views whose
  statistics have been updated or whose cardinalities have changed
  significantly since the last execution. This behavior applies to
  standard user-defined tables, temporary tables, and the inserted and
  deleted tables created by DML triggers. If query performance is
  affected by excessive recompilations, consider changing this setting
  to OFF. When the AUTO_UPDATE_STATISTICS database option is set to OFF,
  no recompilations occur based on statistics or cardinality changes,
  with the exception of the inserted and deleted tables that are created
  by DML INSTEAD OF triggers. Because these tables are created in
  tempdb, the recompilation of queries that access them depends on the
  setting of AUTO_UPDATE_STATISTICS in tempdb. Note that in SQL Server
  2000, queries continue to recompile based on cardinality changes to
  the DML trigger inserted and deleted tables, even when this setting is
  OFF.

So if you want to be absolutely sure your plans for those tables are recompiled you will have to recompile them your self using

sp_recompile 'tablename'


How to coordinate two sessions writing the same row to a MySQL table

Here's the best solution I've been able to come up with, using a write-read approach:

INSERT using ON DUPLICATE KEY UPDATE; (without first checking to see if the row exists)
if the value returned != 0, then that's the PK of the new row;
else a row must already exist with that value, so SELECT it;
if the SELECT can't find the row, then throw an exception because this is unrecoverable.

This has the following advantages:

it doesn't require any transactionality or a different isolation level, because the insert-if-not-exists statement is already an atomic operation;
it executes at most 2 SQL statements (insert-select) instead of 3 (select-insert-select), making the calling code simpler;
since we're not using INSERT IGNORE, if the INSERT statement throws an exception it will be for a legit reason - e.g. auto_increment has reached the max value of id column - and thus we don't have to worry about needlessly logging duplicate key exceptions or ignoring actual database problems.


How to handle the junk characters upon conversion of the data from image datatype to varchar?

If you are converting data from one format to another, then there is no such thing as "junk": it is all data, even if it doesn't "look" right. So, if you are saying that the conversion is not producing the expected results, then you need to be clearer about what the expect results should look like. You do mention that:

  col1 starting with "0x7B5C727" is working fine

Well, doing that conversion

SELECT CONVERT(VARCHAR(MAX), 0x7B5C72);

Returns:

{\r

Why is "{\r" considered "working"? If the data in col1 is actual binary data, then you shouldn't be converting it to a string type in the first place. If "{\r" is somehow the expected result, then the fact that

SELECT CONVERT(VARCHAR(MAX), 0xD0CF11E0);

returns:

ÐÏà

would need to also be considered "correct".

Now, there is a minor difference between the two: the "working" input of "0x7B5C727" shows characters that all fall within the standard ASCII set of values 0 - 127 (i.e. 0x00 - 0x7F). But the "not working" input of "0xD0CF11E0" shows characters that mostly fall within the Extended ASCII range of 128 - 255 (i.e. 0x80 - 0xFF) which is determined by the Code Page associated with the default Collation of the database that the query is running in. And, the one character in the standard range -- 0x11 -- is a control character (hence the box). But, while the characters returned for the "not working" set can change per each Code Page, I tried several and they tend to mostly map to the same characters.


Copy every entry from TableA to TableB where the ID is in a textfile containing a list of IDs

The easiest way I know of is creating a table in tempdb with a single column (id for instance), then expand the tempdb node in SSMS and find the table. Right click and select "Edit top 200 rows".

A grid will appear. Select the whole first row with the row header on the left, then paste the whole contents of the file.

BCP is an alternative way to load the file contents to the destination table. In this case I don't find it any way easier than copying and pasting the file contents in SSMS.

You could also use OPENROWSET or other techniques to reference the file directly in your query, but that requires copying the file to the server or messing with permissions: too much hassle for a one-off task.


Varchar index - will hashing value make it faster?

What you are asking is a little daunting. Here is why:

Would it be faster to store a hash of the value as well and instead index and search on that?

Creating a hash column and indexing sounds like a great idea. I have suggested that back on March 03, 2013 : Possible INDEX on a VARCHAR field in MySql (See Suggestion #3)

Does that even make sense if the values are not guaranteed to be unique?

This would depend on the cardinality of that hash column. Since you said you will have millions of rows, let me express this in numerical terms:
Run SELECT COUNT(DISTINCT hashcolumn) ... against the table. For a one million row table, this count should be greater that 20. In other words, each distinct value should have no more that 50,000 rows (5% of the table rows). Any value that has more that 50,000 rows will cause the MySQL Query Optimizer to dismiss the index from being used and make a full table scan the preferred method for that hash value.

If hashing the values gives them a consistent length, would indexing that make queries faster?

I would say Yes and Perhaps at the same time. Why two answers? Indexing and using a hash column in place of a long column sounds brilliant against a MyISAM Table. You said you are using InnoDB.
When it comes to using Fixed vs Variable text, I would go with MyISAM over InnoDB

Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options
May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Mar 25, 2011 : Performance implications of MySQL VARCHAR sizes

EPILOGUE
If the table is fairly-to-heavily used in Transactions, the table must stay InnoDB. You can take better advantage of your idea in MyISAM. You can go forward with the hash idea. Please make sure the PRIMARY KEY is single integer column (BIGINT if you know you will exceed 2 billion rows. Otherwise, INT). I would do a major RAM upgrade and increase the InnoDB Buffer Pool size accordingly.


No, they don't need to have the same name/number/type of columns (although for the columns you are comparing on in the "join" i.e. the on-predicate, they need to be compatible to compare).

The MERGE statement created according to your example syntax will specify which fields to match on (merge predicate), or insert/update/delete by. If you want to match on CustomerName = Customer, or insert a OrderDate into the Date field, that would be fine.

Think of it as if you are doing an INSERT based on a join, and an UPDATE based on a join, within the single statement. Some people refer to this process as an "upsert"!


SQL error: Unknown column in 'where clause'

The value for $_GET['q'], which must be Wien, should be in quotes

$sql = "SELECT * FROM bundesland WHERE bkuerzel = '" . $_GET['q'] . "'";
$result = mysql_query($sql, $con);

Without putting quotes around $_GET['q'], mysqld thinks the value of $_GET['q'] is a field.


Table size increasing drastically in sql server

I bet it is a heap table. SQL server can be lousy at re-using space for deleted data in a heap. Try having it as a clustered table instead.


Why none of the sql_mode(s) are recognized by MySQL?

Your problem is very simple

You have the following

[mysql]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

sql_mode is not an option of the mysql client. That's why the error message is unknown variable.

Note how sql_mode and sql-mode do not appear in the mysql client options:

$ mysql --help | grep mode
                      Automatically switch to vertical output mode if the
                      work in batch mode. Disable with --disable-pager. This
  --ssl-mode=name     SSL connection mode.
  --ssl               Deprecated. Use --ssl-mode instead.
                      Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
                      also. Does not work in batch mode. Disable with
  --binary-mode       By default, ASCII '\0' is disallowed and '\r\n' is
                      \C and DELIMITER, in non-interactive mode (for input
                      expired password sandbox mode.
binary-mode                       FALSE

SOLUTION

sql_mode is an option for mysqld.

Simply change the group header to [mysqld]

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

You don't have to restart mysql. Just login and run

mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

GIVE IT A TRY !!!


Trigger to compare inserted value with value from another table

According to your question the sample tables are follows:

  Table:ITEMS

Field's Name      Type
------------      ------
Serialno          Number (PK)
Qty               Number

Table:ORDERS

Field's Name      Type
------------      ------
Orderno           Number (PK)
Serialno          Number (PK)(FK)
Qty               Number

The following trigger may solve your problem.

CREATE OR REPLACE TRIGGER check_qty
AFTER INSERT OR UPDATE ON ORDERS
FOR EACH ROW
DECLARE
 item_qty ITEMS.qty%type;
BEGIN
 SELECT qty
 INTO item_qty
 FROM ITEMS
 WHERE serialno = :NEW.serialno;

IF(:NEW.qty>item_qty)THEN
 RAISE_APPLICATION_ERROR(-20250,'QTY is greater than stock qty!');
END IF;
END;


Maintenance in MYSQL when innodb_file_per_table disabled

ANALYZE TABLE will read index pages for a table, compute statistics, and store the results in INFORMATION_SCHEMA.STATISTICS. No writes to ibdata1 whatsoever.

Notwithstanding, anything DDL-related such as

ALTER TABLE ... ADD INDEX ...
ALTER TABLE ... DROP INDEX ...
ALTER TABLE ... MODIFY COLUMN ...
OPTIMIZE TABLE ...

performed against an InnoDB table or its indexes with innodb_file_per_table disabled will make ibdata1 mercilessly grow.

There are two things you could try to minimally control (or at least monitor) ibdata1's growth

ALTERNATIVE #1 : Place a limit on ibdata1 on creation

Perhaps create a large ibdata1

ibdata1:innodb_data_file_path = ibdata1:100G

or set to large initial size and a larger max filesize

ibdata1:innodb_data_file_path = ibdata1:50G:autoextend:max:100G

ALTERNATIVE #2 : Use a Large Raw Data Device

According to MySQL 5.0 Certification Study Guide, Page 428

  Any raw partitions in the configuration must exist but must have the modifier newraw listed after the size of the file specification. newraw tells InnoDB to initialize the partition when the server starts up. New partitions are treated as read-only after initialization. After InnoDB initializes the tablespace, stop the server change newraw to raw in the partition specfication, and restart the server. For example, to use a 10GB Unix partition named /dev/hdc6, begin with a configuration like this:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Gnewraw

  Start the server and let InnoDB initialize the tablespace. Then stop the server and change the configuration from newraw to raw:

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdc6:10Graw

  After changing the configuration, restart the server.

CAVEAT

With either alternate you stop worrying about growth until there is no more room. Otherwise, you must eventually deal with any imposed limits on ibdata1's size.

Apr 15, 2012 : What happens when InnoDB hits its tablespace autoextend max?
Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?


Add new columns on existing or auxiliary table?

You haven't specified a database platform, but my experience with MS SQL server is that you should put those fields in the same table, unless you need them separate for other reasons (like if a single store can have multiple addresses).

Any performance concerns you have can likely be resolved with proper indexing, based on how you believe the table will be queried.


after deleting 100 millions of records in postgresql free space doesn't change

The documentation describes that well:

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.

For an application where you have to delete lots of rows regularly, you might consider partitioning. If you can partition the table in a way so that you can drop partitions rather than running DELETE, you won't have no bloat problem any more.


Using Window Function instead of Select sub-query for counting

I think that this would provide the best performance while still avoiding the sub-select.  I guessed that there was a column called OrderID in the Orders table, may need to adjust.

;WITH CTE_Orders AS
    (
    SELECT O.EmployeeID
        , COUNT(O.OrderID) AS AllOrders
        , SUM(CASE WHEN CONVERT(DATE, O.RequiredDate) <= CONVERT(DATE, O.ShippedDate) THEN 1 ELSE 0 END) AS LateOrders
    FROM dbo.Orders AS O
    GROUP BY O.EmployeeID
    )
SELECT E.EmployeeID
    , E.LastName
    , C.AllOrders
    , C.LateOrders
FROM CTE_Orders AS C
    INNER JOIN dbo.Employees AS E ON E.EmployeeID = C.EmployeeID
ORDER BY C.EmployeeID


How to prepend an element to a JSON array?

Burns down to a simple solution:

_y := to_jsonb(ARRAY[_x]) || _y;

This is concatenating two jsonb arrays with the || operator.. You need to wrap the simple text value _x into a jsonb array for this. There are various way, to_jsonb(ARRAY[_x]) is one.

Or even just:

_y := to_jsonb(_x) || _y;

The manual:

  The || operator concatenates the elements at the top level of each of
  its operands. [...]

So this happens to work for element-to-array concatenation as well.

You could unnest the array and reconstruct it (like you were trying), but that's more expensive. And might be tricky since a JSON array can hold elements of various types, unlike a Postgres array. (Which would not be a problem for your JSONB array of text.)

Your attempt failed because (as commented at the related answer), jsonb_build_array() expects a list of values, which can be replaced with a single array following the keyword VARIADIC - but not with a single element, followed by VARIADIC array.


APEX Installation ORA-01435 while running @apxconf

Oracle support doc 1970925.1 suggests that this is because you're trying to use the wrong version of script with the installed APEX version (e.g. 5.0 APEX installation but 5.1 scripts).

Ensure you're using the right version of scripts with the right APEX version.

Another reason this could happen, is if APEX isn't even installed.  Note that with 12.2 onwards, APEX isn't installed in the database by default.


Efficiently get first 10 results and total number of results

If your ORDER BY and WHERE clauses are complicated, then proposed solution should cut time in half:
1. Create a new table for intermittent results (it can be a temp table, table variable or memory optimized table with session id marker and schema_only setting).
2. Insert all rows from source query into this table, including order by expressions as additional columns.
3. Run your count and top 10 query from this new table.


Does PostgreSQL work well as a workstation-based DBMS?

(this is just too long for a comment)

Postgres works very well on a workstation - it also works very well on the server.

You should be aware that Postgres, being a full blown server DBMS does require some kind of installation - and it requires to start the server process in order to use it. You don't really need a Windows service and you can start and stop the server from within your application (by calling pg_ctl).

For our development team I have prepared a little batch file that gets a Postgres server up and running from the ZIP distribution without any installation hassles. Something like that should be suitable for your needs as well). If your are interested, I can add it to this answer.

Although I really like Postgres (and would chose it over SQL Server any time), I agree with dezso that an embedded engine like SQLite (or even SQL Server CE) might be more suitable, due to smaller installation requirements (and thus less possible problems). But they are less powerful as well.


How can I fix the Oracle syntax of this update by the result of a select in Oracle 10g?

The documentation for the Update statement allows this syntax even in 10g, so perhaps this is a bug.  I looked for one on My Oracle Support, but couldn't find one.  Perhaps you should open a SR with Oracle to see what they say.

As a workaround if you re-arrange the statement to have the literal first it will work on both versions.

update test set col1 = 1 + (select 1 from DUAL );

Another workaround is to wrap the function in to_number.  I don't see why either should be necessary.  Updating a column to the results of a compound expression that contains a scalar subquery expression added to a value is supported by the syntax and should work.

Similar behavior found here also with no resolution.


ERROR: Failed to convert parameter value from a String to a DateTime

You need to convert date into nvarchar first through your query:

Convert(varchar,[date_attribute],106)


Is there any fastest way to shrink LOB data in SQL Server

Shrinking a database is not fast. In fact, it tends to be incredibly slow, and there's nothing you can do to speed it up, other than to have less data to shrink.

How does shrinking work?

When you shrink a data file, SQL Server takes the 8k pages from the end of the file, and moves them to a (random) spot at the beginning of the file. It moves the pages one page at a time. In a way, the page-by-page movement of data is similar to the way a REORGANIZE works, except in reverse. Because the pages are moved individually, to a (random) new spot, it will "disorganize" pages by making previously-contiguous pages non-contiguous, creating fragmentation.

The page-by-page movement is inherently slow, which is why it is faster to REBUILD highly-fragmented indexes than to REORGANIZE them.

REBUILD instead

Normally, I'd suggest that you just REBUILD into a new filegroup, then remove or shrink the old (empty) filegroup. The REBUILD into the new filegroup will be faster than shrinking, because REBUILD if faster then REORGANIZE (and "backwards REORGANIZE" aka shrink). Then, with the old filegroup empty, it will be quick to remove or shrink when it is empty or almost empty, because there are no/few pages to remove.

And because shrinking creates fragmentation, you are going to need to do a REORGANIZE after you shrink anyway.

LOBing a curveball

In your scenario, LOB data represents 2458GB out of 3108GB total data (80%). When you do a REBUILD it will move the B-Tree pages, but it will not move the LOB data. So if you rebuild into a new filegroup, your old filegroup will still be 60+% full.

For the table(s) that have a significant amount of LOB data, you would need to take a different approach. You could create a new table in the new filegroup, migrate data to that new table, then swap table names and drop the old table. You could think of this process as a "manual rebuild"--except doing it this way would allow you to copy LOB data to the new filegroup. I describe this method in a blog post on changing data types, but the exact same method would work in this case--except you would be changing filegroups, rather than changing data types.

Shrinking (nearly) empty filegroups is fast

Shrinking is slow because it has to move allocated pages in order to create contiguous free space at the end of the data file. If the filegroup is empty, the end of the file can be truncated without moving pages and will be very fast.

If the filegroup you are shrinking is PRIMARY then you won't be able to get it completely empty--PRIMARY contains all the system tables & metadata. By moving all your user tables out of PRIMARY, the filegroup will be almost empty, and there will be a very small number of pages to move. Having just a few pages to move will also be pretty fast, particularly compared to moving 2TB of pages.


Warning SQL01271: The unnamed foreign key between tables [dbo].[Match] and [dbo].[Team] will be checked by checking the table

Try the more verbose syntax

...
Team1_ID int not null CONSTRAINT FK_Match_Team1 FOREIGN KEY (TeamID) REFERENCES Team (TeamID),
Team1_ID int not null CONSTRAINT FK_Match_Team2 FOREIGN KEY (TeamID) REFERENCES Team (TeamID),
...

I'd suggest the lack of explicit constraint name is confusing VS because you have 2 match-team constraints. That is, it is a Visual Studio error not a SQL Server error. You won't get it in SSMS


MongoDB 3.6 crashed due to invariant() failure

It looks like the problem is not with your physical memory.

What is the RAM size you are using? Are there any other applications that are consuming more RAM memory?

Refer the below thread for more details

Invariant failure: seekRet resulted in status


SQL Error: 1038: An object or column name is missing or empty

Are you trying to create empty login?

CREATE LOGIN [] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

If we run above in the SSMS query window, we get below error message:

  Msg 1038, Level 15, State 4, Line 1 An object or column name is
  missing or empty. For SELECT INTO statements, verify each column has a
  name. For other statements, look for empty alias names. Aliases
  defined as “” or [] are not allowed. Change the alias to a valid name.

Now the error makes sense;

  Because login is created as [] and error clearly says that “…[] are
  not allowed…”

Solution:

  The issue was caused because of the empty login name. If we provide name, then the issue will be resolved.

For more info check  CREATE LOGIN


How do you perform a MYSQL After Update Trigger Insert Loop?

You can restructure your query

INSERT INTO raw_materials_stock
  (batch_id,raw_material_id,use_date,quantity)
    SELECT
       new.batch_id,
       b.raw_material_id,
       now(),
       ((new.volume/100)*b.percent)*-1
    from base_formula b where b.base_id=new.base_id;


Percona Xtrabackup fails to backup InnoDB table with fulltext index

Do I have to do the --export run right after I create the backup ? Or
  can I delay that until I actually need to restore a backup ?

Not necessarily, as long as the backup completes with completed Ok then it is assumed that the backup is successful. It is still best practice to test your backups regularly.

  What can I do about the error for the FTS files ? Is that normal ?
  Will Mysql re-create the fulltext index automatically or is it even
  included in the main tables .idb file ?

This is expected if in your test.txt file the table with FULLTEXT indexes is not included. Basically you are not backing up that particular table so it will be skipped during the backup operation and during the prepare operation

Easily reproduced if you create test1 and test2 tables and then on the test1 table make sure you create a FULLTEXT index. On the backup stage:

181215 10:31:22 [01] Skipping ./test/FTS_000000000000002e_0000000000000033_INDEX_1.ibd.

On the prepare stage:

InnoDB: Failed to find tablespace for table `test`.`FTS_000000000000002e_0000000000000033_INDEX_1` in the cache. Attempting to load the tablespace with space id 30

One more note, it is recommended to use xtrabackup binary instead of innobackupex because starting in version PXB 8.0 innobackupex is removed.


Oracle: Inserting into table error - either duplicate column name or invalid datatype

External table name is construct_ext
CREATE TABLE construct_ext
  (    construct_id NUMBER (10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      mw_kda NUMBER (7,3)
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input

    ACCESS PARAMETERS
  (
        RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
        TERRITORY AMERICA
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
        LOGFILE bad_files:'logflie_insert_into_construct_from_construct.log'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL
        )
    LOCATION ('CONSTRUCT.CSV')
   ) REJECT LIMIT UNLIMITED;

INSERT /*+ ignore_row_on_dupkey_index ( consruct ( construct_id ) ) */
INTO construct
    (construct_id,
    n_term,
    enz_name,
    c_term,
    cpp,
    mutations,
    mw_kda)
SELECT
      construct_id,
      n_term ,
      enz_name,
      c_term ,
      cpp ,
      mutations ,
      mw_kda
FROM
      construct_ext ext

WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
);

Edit:-After creating external table verify if you're able to query the table
Edit:- As per @a_horse_with_no_name you can access external table without creating using inline external table for Database version 18c and above
The problem arises when you try to insert target table without creating external table(hope someone can shed light on this) work around is to create staging table then insert into target table
    CREATE TABLE construct_stg
    AS
    -- select part works without creating external table
    SELECT *
        FROM EXTERNAL (
        (
          construct_id NUMBER(10),
          n_term VARCHAR2 (50),
          enz_name VARCHAR2 (50),
          c_term VARCHAR2 (50),
          cpp VARCHAR2 (50),
          mutations VARCHAR2 (50),
          mw_kda NUMBER (7,3)
          )
        TYPE ORACLE_LOADER
        DEFAULT DIRECTORY data_to_import
        ACCESS PARAMETERS (
            RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
            TERRITORY AMERICA
            SKIP 1
            BADFILE bad_files:'badflie_insert_into_construct_from_construct.bad'
            --LOGFILE bad_files:'logflie_insert_into_construct_from_construct.log'--create different directory for log file or skip this line
            FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
            MISSING FIELD VALUES ARE NULL
            (
              construct_id,
              n_term ,
              enz_name,
              c_term ,
              cpp ,
              mutations ,
              mw_kda
              )
            )

        LOCATION ('CONSTRUCT.CSV')
        REJECT LIMIT UNLIMITED)ext_tab;

   CREATE INDEX idx_construct_id ON construct_stg(construct_id);

   ALTER TABLE construct_stg ADD CONSTRAINT pk_construct_id_stg PRIMARY KEY (construct_id) USING INDEX;  -- to enforce unique or skip this

    INSERT /*+ ignore_row_on_dupkey_index ( consruct,idx_construct_id) */
    INTO construct
    (construct_id,
    n_term,
    enz_name,
    c_term,
    cpp,
    mutations,
    mw_kda)
SELECT
      construct_id,
      n_term ,
      enz_name,
      c_term ,
      cpp ,
      mutations ,
      mw_kda
FROM
      construct_stg ext

WHERE NOT EXISTS (
        SELECT * FROM construct c
        WHERE c.n_term = ext.n_term
        AND c.enz_name = ext.enz_name
        AND c.c_term = ext.c_term
        AND c.cpp = ext.cpp
        AND c.mutations = ext.mutations
);


Want to match two date columns of two tables both have diff formats in mysql

Found it... it will be date_format(date , '%Y- %m- %d')


How to reinstall SQL Server Integration Services I've deinstalled from a Production server by mistake

Run the installer again.

Installing/Configuring SSIS and SSRS to existing SQL Server 2008 database services

    Run SQL Server setup program and on the “Feature Selection” page click the checkboxes associated with the features you would like to have installed. When finish, reapply any service pack the instance have applied before.


What is best strategy for backup on streaming replication(hot standby) environment

As of >= 1.3.1 Barman supports backup from a standby replica (concurrent_backup). Barman config, e.g. /etc/barman.d/standby.conf looks like this:

[standby]
description = "Replica of main PostgreSQL DB"
ssh_command = ssh postgres@db02
conninfo = host=db02 user=postgres
backup_options = concurrent_backup
streaming_conninfo = host=db02 user=postgres
streaming_archiver = on

If your master is running on PostgreSQL <= 9.5 you'd have to install pgespresso extension (there are binary packages e.g. for Debian from PGDG APT repos). PostgreSQL 9.6 introduced native streaming API, there's no need for special extension.

On standby server make sure to configure archive_command:

wal_level = hot_standby
archive_mode = on
archive_command = 'rsync -a %p barman@backup:/var/lib/barman/standby/incoming/%f'

the incoming directory should match

barman:~$ barman diagnose | grep incoming_wals_directory

Also on standby server update pg_hba.conf (where 10.0.0.3 is ipaddress of barman server):

host   all             postgres            10.0.0.3/32        trust

And enable WAL files streaming:

barman~$ barman receive-wal standby

You can check your configuration using:

barman:~$ barman check standby

Server standby:
        PostgreSQL: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 1 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        pgespresso extension: OK
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK

Then you should be ready to run full backup:

   barman:~$ barman backup standby


In SSMS you can easily create diagrams showing the relationships between tables. The basic steps are:

Connect to the database server
Expand the Databases folder
Expand the folder for the database you're investigating.
Right click on the Database Diagrams folder.

a. Select Install Diagram Support if needed
Select New Database Diagram
In the wizard add any tables you're interested in.
Take the appropriate next step:

a. Curse the previous DBA/Developer that generated this mess.

b. Thank your deity of choice for the blessing of a well structured/modeled database.

c. Shrug your shoulders and tell yourself this is manageable.


Database structure, relational or data warehouse?

It seems you want to aggregate location based statistics over time for rainfall.  A database structure like the one below would let you do that.  The 'data source' could be just a filename, or some indication as to where it came from.

create table DimDataSource (
       DataSourceID      int identity (1,1) not null
       DataSourceDesc    nvarchar (100)  -- May need unicode for file names
)
go

alter table DimDataSource
  add constraint PK_DataSource
      primary key clustered (DataSourceID)
go

create table DimLocation (
       LocationID        int identity (1,1) not null
       LocationDesc      varchar (50)
)
go

alter table DimLocation
  add constraint PK_Location
      primary key clusterd (LocationID)
go

create table DimDate (
       DateID           smalldatetime not null  -- 'Date' is a reserved word
      ,MonthID          int not null
      ,MonthDesc        varchar (15)
      ,QuarterID        int not null
      ,QuarterDesc      varchar (15)
      ,YearID
)
go

alter table DimDate
  add constraint PK_Date
      primary key clustered (DateID)
go

create table DimTime (
       TimeID           time not null  -- 'Time' is a reserved word
      ,Hour             int not null
)
go

alter table DimTime
  add constraint PK_Time
      primary key clustered (TimeID)
go

-- If the table is <50GB, don't bother with partitioning, but put a clustered
-- index on DateID or LocationID and DateID, depending on how you normally expect
-- to query the data.

create table FactRainfall (
       RainfallID        int identity (1,1) not null -- May need a wider type if >4B rows.
                                                     -- SSAS likes an identity column for
                                                     -- incremental loads
      ,DataSourceID      int not null
      ,LocationID        int not null
      ,DateID            smalldatetime not null
      ,TimeID            time not null
      ,Rainfall          float
)
go

-- Add foreign keys as necessary

Populate the dimensions with the appropriate list of locations, date ranges, time of day to the right grain and one data source record per file.  This table will also allow you to put a cube over the top, or can be flattened with a view, which will help people using tools like Excel or stats packages to get and use the data.


MariaDB 10.2 Encrypted at rest log files not being written

Ok after a bit of trial and error I've got all logs writing! I think the problem must have had to do with permissions as even though the log files were chown to mysql in the separate folder I planned to use - removing file designation from the config allowed logs to get written in the default folders. Sigh - thanks for your help!


Migrate logins tagged to specific databases users

I have a procedure called sp_SrvPermissions that has a @DBName parameter specifically for that.  You can do something like this:

EXEC sp_SrvPermissions @DBName = '<DBName>', @Output = 'CreateOnly'

This will give you not just the script to create the LOGIN associated with just that database but any server level roles they are a member of and any server specific permissions they might have as well.


EXPLAIN tells you the difference.

If it shows DEPENDENT SUBQUERY it means that the subquery in IN() is executed once-per-row for the table1, that may be really many times if the table is big.

Different MySQL versions may apply different optimizations to the first query to get rid of the DEPENDENT SUBQUERY or at least minimize its performance impact:

use subquery cache - may be good if there are many duplicates in col1 values
change NOT IN into NOT EXISTS
materialize the subquery into a temporary table and (left)join to it
rewrite the query to left-join directly

If none of these is applied by your optimizer, use the second query because MySQL optimizes joins better (more so in older versions). Having a plan with DEPENDENT SUBQUERY in it is not effective in most cases.

IIRC the LEFT JOIN ... IS NULL may be evaluated as NOT EXISTS with specific optimization for that plan.

For both queries you should have an index on table2.col1 to allow fast execution of the subquery or fast detection of (non) existence of the row.


For anyone looking for a workaround, limiting pg_restore to a specific schema has helped me get around this bug. See https://stackoverflow.com/a/11776053/11819


No, an indexed view is maintained immediately as the base table is changed - there is no delay possible (unless the reader is using nolock and the original transaction hasn't committed yet). There must be something else going on, but you'll need to provide more details than "they said."


You have to look in my.cnf for the variable  innodb_parallel_doublewrite_path
You should also set this
[mysqld]
innodb_doublewrite=0

Then, restart mysql
After restarting mysql, you can then delete /test/mysql/xb_doublewrite
CAVEAT
What is the purpose of this feature ?
According to Percona Server 5.7 performance improvements

Parallel doublewrite buffer. For ages, MySQL has had only one
doublewrite buffer for flushing data pages. So even if you had several
threads for flushing you couldn’t efficiently use them – doublewrite
quickly became a bottleneck. We’ve changed that by attaching two
doublewrite buffers to each buffer pool instance: one for each type of
page flushing (LRU and flush list). This completely avoids any
doublewrite contention, regardless of the flusher thread count. We’ve
also moved the doublewrite buffer out of the system tablespace so you
can now configure its location.

So, if you do want it gone, remove innodb_parallel_doublewrite_path if it is there. Although not recommnded, you must add innodb_doublewrite=0 to disable the InnoDB Double Write Buffer altogether.


PostgreSQL failover: Who is responsible for handling it? How do clients connect to the new leader?

https://www.postgresql.org/docs/12/warm-standby-failover.html says:

  PostgreSQL does not provide the system software required to identify a
  failure on the primary and notify the standby database server. Many
  such tools exist and are well integrated with the operating system
  facilities required for successful failover, such as IP address
  migration.

Yes you have to setup something.

PostgreSQL does not provide automatic procedure for clients.


How can I reset the counters from pg_stat_database view?

The solution is simple, store previous values and substract.
No need to reset anything in Postgres.
In this way meaningful stats can be obtained:

(newValue-oldValue)/numberOfSeconds


Leading zeros are truncated when I spool to CSV

String are written between "" in the CSV. So your datatype is not string type, or the tool you use for viewing the CSV automatically converts and trims it.

[oracle@o71 ~]$ sqlplus -S bp/bp
set markup csv on
set echo off
spool 1.csv
select '04589' as phone from dual
union all select '02586' from dual;

"PHONE"
"04589"
"02586"

exit
[oracle@o71 ~]$ cat 1.csv

"PHONE"
"04589"
"02586"

[oracle@o71 ~]$


Can I use mysql to serve 100's of millions of small image files?

I wonder why any kind of DB is needed. It sounds like you'd like it if these were just files, but that the number of them and their small size are problematic.

There are two strategies that can help here, one is to use a few levels of directories to help partition the files into smaller clusters.

Now, some file systems may have inode issues or become very wasteful of space if they are tiny files and large block sizes.  Indeed, if you're on Linux it may be worth spending some time looking at the alternative file systems that are available and see if one meets your use case well.

A 'simply' way to avoid the issue is, for the last layer, rather than having files in a leaf directory, just have files in a zip file with zero compression.  This pretty much works almost like a directory with files, but can circumvent the above two issues.  It effectively gives you what you've been doing by hand, but with a more standard tool-chain.

From what you've described so far, I personally wouldn't be putting this anywhere near any kind of DB, NoSQL or traditional.

If you really want a DB solution, you could look at some form of partitioning across multiple tables to avoid any file size limits, but it wouldn't be my first option!


Thanks to Amacvar on reddit for pointing me to the answer:

max_standby_archive_delay and max_standby_streaming_delay have to be set for longer period than the longest query you want to run on the hot-standy.

From https://www.postgresql.org/docs/current/static/hot-standby.html

  It is important that the administrator select appropriate settings for max_standby_archive_delay and max_standby_streaming_delay. The best choices vary depending on business priorities. For example if the server is primarily tasked as a High Availability server, then you will want low delay settings, perhaps even zero, though that is a very aggressive setting. If the standby server is tasked as an additional server for decision support queries then it might be acceptable to set the maximum delay values to many hours, or even -1 which means wait forever for queries to complete.


Number of TempDB files if we can't use parallelism

Individual queries don't use parallelism? So what? If you have two completely different users running different queries at the same time, they too can make use of different tempdb files.

Of course it doesn't help all that much if you have four tempdb files all on the same volume - the real gain is when you can spread it out into multiple I/O paths.

Hard to speculate on "any slowdowns you're seeing" without more details.

Shouldn't be any issues with changing the file size. Just make sure they are all exactly the same size, have the exact same autogrow settings, and check to see if you should run trace flags 1117 and 1118.


Best way to get last identity inserted in a table

Use SCOPE_IDENTITY() if you are inserting a single row and want to retrieve the ID that was generated.

CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1));

INSERT #a(x) VALUES('a');

SELECT SCOPE_IDENTITY();

Result:

----
1

Use the OUTPUT clause if you are inserting multiple rows and need to retrieve the set of IDs that were generated.

INSERT #a(x)
  OUTPUT inserted.identity_column
  VALUES('b'),('c');

Result:

----
2
3

  and why this is best faster option?

Performance aside, these are the only ones that are guaranteed to be correct in the default isolation level and/or with multiple users. Even if you ignore the correctness aspect, SQL Server holds the inserted value in SCOPE_IDENTITY() in memory, so naturally this will be faster than going and running your own isolated query against the table or against system tables.

Ignoring the correctness aspect is like telling the mailman he did a good job delivering today's mail - he finished his route 10 minutes faster than his average time, the problem is, none of the mail was delivered to the right house.

Do not use any of the following:

@@IDENTITY - since this can't be used in all scenarios, for example when a table with an identity column has a trigger that also inserts into another table with its own identity column - you will get the wrong value back.
IDENT_CURRENT() - I go into detail about this here, and the comments are useful reading as well, but essentially, under concurrency, you will often get the wrong answer.
MAX() or TOP 1 - you would have to protect the two statements with serializable isolation in order to ensure that the MAX() you get isn't someone else's. This is much more expensive than just using SCOPE_IDENTITY().

These functions also fail whenever you insert two or more rows, and need all the identity values generated - your only option there is the OUTPUT clause.


Newdatabase option is not available in sql server management studio 2014

For some reason, SSMS doesn't provide this New Database menu option for SSAS Tabular. But you can easily create a new empty or shell database with the following XMLA script. Just click the XMLA button in the toolbar and then paste in the following. Edit the ID and Name property to the desired database name. And edit the DOMAIN\GroupName to set the group that has admin permission. This will allow members of that group to deploy over this database without being SSAS admins for the whole instance.

The following script is for SSAS 2012 SP1 or 2014 Tabular models:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ObjectDefinition>
        <Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400">
            <ID>ShellDatabase</ID>
            <Name>ShellDatabase</Name>
            <ddl200:CompatibilityLevel>1103</ddl200:CompatibilityLevel>
            <ddl200_200:StorageEngineUsed>InMemory</ddl200_200:StorageEngineUsed>
            <Language>1033</Language>
            <Collation>Latin1_General_CI_AS</Collation>
            <DataSourceImpersonationInfo>
                <ImpersonationMode>Default</ImpersonationMode>
            </DataSourceImpersonationInfo>
            <Roles>
                <Role>
                    <ID>Role</ID>
                    <Name>Administrators</Name>
                    <Members>
                        <Member>
                            <Name>DOMAIN\GroupName</Name>
                        </Member>
                    </Members>
                </Role>
            </Roles>
            <DatabasePermissions>
                <DatabasePermission>
                    <ID>DatabasePermission</ID>
                    <Name>DatabasePermission</Name>
                    <RoleID>Role</RoleID>
                    <Process>true</Process>
                    <Read>Allowed</Read>
                    <Administer>true</Administer>
                </DatabasePermission>
            </DatabasePermissions>
        </Database>
    </ObjectDefinition>
</Create>

And for SQL 2016 Tabular models using the latest 1200 compatibility level, use the following script. Just change the name to the desired database name and the DOMAIN\GroupName to your administrators group.

{
  "create": {
    "database": {
      "name": "ShellDatabase",
      "compatibilityLevel": 1200,
      "model": {
        "culture": "en-US",
        "roles": [
          {
            "name": "Admins",
            "modelPermission": "administrator",
            "members": [
              {
                "memberName": "DOMAIN\\GroupName"
              }
            ]
          }
        ]
      }
    }
  }
}


Recompile not working for DELETE statement

why it expects for 1 row (in Clustered index seek), when I specify
  recompile

That is fine.

Somewhat confusingly the estimated rows on the inside of a nested loops join are per execution of the operator.

A seek into a primary key will indeed return 1 row (or 0 if the value doesn't exists at all).

In your case you have 2,000 seeks all returning 1 row and the actual rows reported is 2,000 so the estimated rows per execution was correct. You need to multiply estimated rows by estimated numbers of executions and compare that to the actual rows to see if there is any discrepancy.

Your first plan shows that the estimated number of executions of the inner side will in fact be under estimated though (at 45 not 2,000) as it estimates the stream aggregate on TcpInfoId will return 45 distinct values. This is because there are no column statistics on table variables that tell it that in fact the values are unique. OPTION (RECOMPILE) just allows it to take into account of the number of rows in the table variable it doesn't provide any information on column density.

If you were to change the column definition of TcpInfoId to [TcpInfoId] [BIGINT] NOT NULL PRIMARY KEY or [TcpInfoId] [BIGINT] NULL UNIQUE CLUSTERED (difference being the second one will allow a single NULL) then this will provide uniqueness information that can be used.

  Each column has X unique values and Y NULLS. But in practice one of
  them is 0 (we have all nulls or we have all unique non-null values).

If you are on 2016 you can use a unique filtered index

 CREATE TYPE [AddressInfoParts] AS TABLE
 (
 [TcpInfoId] [BIGINT] NULL INDEX IXTcpInfoId UNIQUE WHERE [TcpInfoId] IS NOT NULL,
 [EmailInfoId] [BIGINT] NULL INDEX IXEmailInfoId NONCLUSTERED,
 [PhoneInfoId] [BIGINT] NULL INDEX IXPhoneInfoId NONCLUSTERED
 )

If you are on a version that doesn't support filtered indexes in table types perhaps you can use a #Temporary table instead (as they can get column statistics created).

Or you can stop trying to use a single table type for these two very different cases.

If I understand your question correctly this is what the second plan represents. The thing you point out as a problem there is not in fact a problem as explained at the beginning of this answer.


You should probably try with OR like:

select *
  from myTable join otherTable
on (setting = 'A' and value = column1) or
   (setting = 'B' and value = column2)

Also UNION ALL may work for you

select * from myTable join otherTable on setting = 'A' and value = column1
union all
select * from myTable join otherTable on setting = 'B' and value = column2


It is not advisable to move files before the export job is finished. The reason is that when exporting, the utility "builds and maintains the master table for the duration of the job. At the end of an export job, the content of the master table is written to a file in the dump file set." (see Oracle's documentation at the following address: https://docs.oracle.com/database/121/SUTIL/GUID-6BDC1CC8-8596-402D-B016-602985B97AB6.htm#SUTIL814) If you move a dump file before the job is done, you risk corrupting that process and making the job fail.

Is there a reason you need to move the files before the data pump job is finished? If not, simply wait for the job to complete. While being able to move files as soon as they are not in use would be very useful for large export/import jobs, you will save yourself a lot of time by letting the export finish before manipulating the dump files.


Is where x = 0 or x is null to be replaced by where isnull(x, 0) = 0 resp. where NVL(x, 0) = 0

If you're looking at it purely from just what's better from a performance perspective ... I'd just test both of your methods and see what's faster for your data.

... but as you only have three states anyway, and this is going in a where clause, it might be even faster to just check that it's not the third case, and then you don't need the NVL test:

column <> 1

Or, if there really is no difference (and NULL isn't a case of 'we don't know, so we're going to treat it for all practical purposes that it's 0, but we occassionally need to know when it's unknown'), just update the table and set it to 0 where it's currently NULL.  (and adjust whatever's feeding in the NULLs)


Make sure that the HBA rules that PostgreSQL is actually using are the ones you expect:
select *
from pg_hba_file_rules
where type = 'host'
order by line_number ;

Check the error column for anything amiss with the file syntax.