Use the next query

SELECT
B.Company,
COUNT(DISTINCT A.Ticket) AS QTY
FROM Table1 A
INNER JOIN Table2 B
ON A.CompanySk = B.EDWCompanySK
WHERE A.StartedDate >= '20171128'
GROUP BY B.Company
ORDER BY QTY DESC


SSIS Package execution fails when started by user logging in with FreeTDS

SSIS catalog executions impersonate the connected login, so probably correct that it's an auth issue.  Easy workaround is to provision a SQL Agent Proxy for the identity you want to run the package, and configure an Agent Job to run the package, invoking the job with sp_start_job.

This is similar to, but more secure than, using xp_cmdshell to run dtexec directly, or call the package execution procedures from powershell.  In both cases a local identity is used to execute the package.


Restore PostgreSQL database with same locale settings as source database

These are the exact steps I take to import a copy of a PostgreSQL 9.5 database (exported on Linux using en_US.UTF-8 encoding) into PostgreSQL 9.3 on Windows 7 or PostgreSQL 9.5 on Windows 8.1. You need to create the database with the appropriate encoding prior to loading the SQL file, otherwise the encoding from Linux (en_US.UTF-8) will prompt Windows to use a default encoding.

REM WARNING: Console code page (65001) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details.
chcp 1252

C:\PostgreSQL\9.5\bin\psql.exe -U postgres -d postgres_password -c "CREATE DATABASE my_db WITH TEMPLATE=template0 ENCODING='UTF-8' LC_COLLATE='american_usa' LC_CTYPE='american_usa'"

REM Grant privileges to the appropriate database user.
C:\PostgreSQL\9.5\bin\psql.exe -U postgres -d postgres_password -c "GRANT ALL PRIVILEGES ON DATABASE my_db TO myuser"

REM Import my_db.sql. Ignore the following error (it comes from a line in the SQL file, but we have already created the database with the correct locale, so we are ok):
REM psql:my_db.sql:22: ERROR:  invalid locale name: "en_US.UTF-8"

C:\PostgreSQL\9.5\bin\psql.exe -o nul --quiet -U postgres -d postgres_password -f my_db.sql

As discussed here, the locale on Windows should be american_usa (on Windows 7). On Windows 8.1, either american_usa or en-US (not en_US as with Unix) will work.


Understanding Plan Choice When Query Store Doesn't Respect Forced Plan

Could anyone help me understand why the plan wasn't forced

Looking through the plan XML, plan id 6194 was compiled on version 15.0.700.539 of SQL Server, while plan id 13449 was compiled on version 15.0.900.210.

The only differences of note (other than cardinality estimates) between the two plans are these:

The compute scalar was affected by a row goal (due to the implicit TOP from OFFSET / FETCH):

EstimateRowsWithoutRowGoal="170.008"

And the NL join was optimized with undordered prefetch:

<NestedLoops Optimized="true" WithUnorderedPrefetch="true">

It seems that these slight differences resulted in the problem described in Andrew Kelly's article, which you linked to.  The plan is essentially the same, with minor implementation differences.  Grant Fritchey describes this as follows:

  There is a relatively obscure situation where a "morally-equivalent" plan, a plan that is identical in all the core essentials, but not necessarily perfectly identical, can be used instead of the precise plan you define. However, this isn't common.

From page 473 of his free e-book about execution plans.

  and why the one in use was so much more expensive?

The really expensive plan in use has 4 extra columns, so I think that one is not really comparing apples to apples.  The higher costs appear to mostly be due to the parameters used (and thus higher cardinality estimates).  Incidentally, it's on another completely different build: 15.0.1300.566.  Things sure do move fast in the cloud =P

These are all "new" columns coming out of the SELECT *:

<ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="delete_requested_date_utc" />
<ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="retention_policy_applied_date_utc" />
<ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="retention_policy_type" />
<ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="id_portal_user" />

The combined test you provided were all inline with this latest version of things.


Something wrong with the viewer (looks like the bug in SSMS), but you always can open ERRORLOG in any other viewer, such as notepad and get exactly the same info


Sync MySql Replication from Master2 to Master1 after failover

As Master2 is in read-only;
Master2: SHOW MASTER STATUS
Master1:
CHANGE MASTER TO
MASTER_HOST = 'host_name'
MASTER_USER = 'user_name'
MASTER_PASSWORD = 'password'
MASTER_LOG_FILE = 'master_log_name'
MASTER_LOG_POS = master_log_pos;

START SLAVE;

Where the master log file name/pos are from the Master2 SHOW MASTER STATUS
The rapidly changing master status isn't an issue as its binary logs are from Master1 anyway. Master1 won't write its own binary log message back again when received via replication.


Should a support incident be opened for every stack dump, or just ones that cause an outage?

It's fairly cut and dry: If you want to actually work on the issue, collect data, uninstall/reinstall, isolate, test, potentially provide more dumps, potentially use things like appverifier and debugdiag or have a valid repro, then by all means open a ticket so it can be looked into and worked on.

If you just want to ship off a stack dump, with no other data, and have someone look at it in a vacuum, then I would not open a ticket as it'll have no traction and go nowhere. Neither party will be happy.

That's the short and sweet version.


Performance issues after moving a database to SQL Server 2014

Ok, at this point I would be considering "manually" replacing statistics to tide me over. I haven't done this myself, but it should work in theory...

You can confirm that this would work as follows:
1.  Restore the pre-upgrade database to your LIVE system and confirm that the query is fast in that database, and delete that database once done. (This test is to eliminate any additional environmental differences as the cause. If the query is slow, then the rest of this proposal may be useless.)
1a. Restore the pre-upgrade database to your test system and confirm that the query is fast.
2. Update statistics, and confirm that the query is slow (cancel after a couple minutes of course)
3. Set compat to 120 and confirm that the query is still slow
4. Set compat back to 100 and confirm that the query is still slow
5. Restore another copy of the pre-upgrade database (I'll refer to this as Rest2, and the earlier as Rest1)
6. Extract all statistics from Rest2 using the techniques at https://thomaslarock.com/2015/07/how-to-recreate-sql-server-statistics-in-a-different-environment/ for all tables involved in the problematic queries (or all tables if that's simpler)
7. Apply the statistics to Rest1, and see if query is now fast (you may need to dbcc freeproccache first).

If it works, then it's almost certainly safe to apply the statistics to your live database--just make sure you have ONLY statistics scripted. And also set its compat level to 100. You should then see the queries run fast (though you may need to dbcc freeproccache first--but consider the possible effect on live performance).

Note I'm assuming here (based on your original post) that you do not have statistics autoupdate turned on, and that your data changes slowly enough that your old statistics will do until you figure out how to get your workload working with compat 120 and/or updated statistics (and you may as well sort both at this point).


Query taking long on table with low record count

If the query plan involves a full table scan, Oracle has to read every block from the table up to its high water mark (HWM).  If there are 800 MB of blocks below the HWM, it would make perfect sense that it would take 30 seconds to read all that data.  The number of blocks that actually have data in them, in this case, is irrelevant.

If you truncated the table, however, you should have reset the HWM unless you specified REUSE STORAGE which is not the default.  The fact that the table is still 800 MB implies that you either explicitly had a REUSE STORAGE in your TRUNCATE command or that you didn't actually truncate the table-- perhaps you just did a DELETE to remove all the data, for example.  A DELETE will not reset the HWM.


Backup failure for a DB part of Always-On Availability group - SQL 2012 Ent Edition

MS discovered that this is an issue with 2012 CU3. We ran into this bug: https://support.microsoft.com/en-us/help/3033492/alwayson-availability-groups-may-be-reported-as-not-synchronizing-afte

This problem is resolved in SQL 2012 SP4.

In the interim - the solution is a reboot to get the DBs off the bad state.


Entity Relationship Diagram - Entity depending on attribute

Here's one idea:

CREATE TABLE Employees
( empno ... not null primary key
, skilllevel ... not null
);

CREATE TABLE Supervising
( supervisor_empno ... not null references Employees (empno)
, empno ... not null references Employees (empno)
, primary key (supervisor_empno, empno)
);

If you want to guarantee the skilllevel in supervising:

CREATE TABLE Employees
( empno ... not null primary key
, skilllevel ... not null
, unique (skilllevel, empno)
, check (skillevel in ('High', 'Low'))
);

CREATE TABLE Supervising
( supervisor_empno ... not null
, supervisor_skillevel ... not null
,    foreign key (supervisor_skillevel, supervisor_empno)
     references Employees (skillevel, empno)
, empno ... not null references Employees (empno)
, empno_skillevel ... not null
,    foreign key (skillevel, empno)
     references Employees (skillevel, empno)
, check (supervisor_skillevel = 'High')
, check (skillevel = 'Low')
, primary key (supervisor_empno, empno)
);


Avoiding "Waiting for table metadata lock" when `ALTER TABLE DROP PARTITION`?

What you are asking for is impossible. Regardless of storage engine, DDL of any kind will lock a table. If you must remove a partition from a table that is active, you should:

Setup MySQL Replication (if you haven't done so already)
Perform all SELECTs involving v3_cam_date against the Slave
STOP SLAVE; on the Slave
Perform ALTER TABLE ... DROP PARTITION on the Master.
Perform all SELECTs involving v3_cam_date against the Master
START SLAVE; on the Slave (Replicates ALTER TABLE ... DROP PARTITION to the Slave)

This is probably your only recourse. The only other recourse is to simply wait out the ALTER TABLE ... DROP PARTITION.
This situation requires some intervention in the application. Within your application, you would have to create a Write DBVIP on the Master and use the Read DBVIP with one of the following three(3) options:
OPTION #1

Keep the Read DBVIP on the Slave

OPTION #2

Keep the Read DBVIP on the Master
When you must do an ALTER TABLE, move the Read DBVIP to the Slave
When ALTER TABLE has been completed, move the Read DBVIP to the Master

OPTION #3

Setup Read DBVIP on a LoadBalancer
Remove Master from LoadBalanced DBVIP on demand

Option #1 seems to be the simplest way in the long run.


How to return multiple columns (each a composite type) in plpythonu

The simple way to do that would be to call your bar() function like this:

postgres@[local]:5432:postgres:=# SELECT * FROM bar();
 a | b
---+---
 1 | 2
 3 | 4
(2 rows)

Time: 0.622 ms
postgres@[local]:5432:postgres:=#

Which returns as a record type in this context, with two rows and two columns.

Hope that helps. =)


SQL Server 2005 Tools makes SQL Server 2008 Express install fail

Open Run Menu and type regedit.
It will open Registry Editor
Now go to following location.

HKEY_LOCAL_MACHINE > Software > Wow6432Node > Microsoft > Microsoft SQL Server > 90

Right click on 90 and click on Delete.
Press Yes to confirm deletion.

Now try to install SQL Server 2008 and it would work this time.


Which CLR Stored Proc is causing memory pressure leading to sqlservr.exe terminating unexpectedly?

SAFE CLR procedures do not crash SQL Server, so looking at the managed memory usage of CLR is unlikely to lead you to the root cause.

It's more likely that you have unsafe CLR code interacting with native code that has a memory leak.  So look at any the CLR projects for any use of 3rd party native components (like OleDB or ODBC drivers), or any use of P/Invoke or COM Interop.


Integer comparison in MySQL. does it cause lots of overhead?

My answer assumes that you are describing a simple query like this:

SELECT `Number` FROM `Table` WHERE `Number` <= 12040670 ORDER BY `Number` DESC

From your question, it's not clear how you're storing the numbers.  If the column isn't an integer data type, you should make one (probably INT UNSIGNED).  That will help the performance whether the column is indexed or not.

There probably should be an index on the numbers column; it will significantly increase the speed of the query.  If the numbers column is the PRIMARY key, then it's already indexed.

The overhead of integer comparison isn't really relevant here; if the numbers are INTs, the comparison is very simple and efficient and if they aren't, the data types of the data and test value would be cast to the same type, which doesn't cost much at all.  The real concern would be having to read the entire table if there isn't an index available.

1,000,000 rows is nothing to MySQL.  If this query uses an index, there will be practically no load from it.  If there is no index and it has to read the entire table, there may be some load and the server may need to hit the disk if the table isn't all in memory, but this query is simple and it probably wouldn't cause significant load.  It may take up to a second to complete, but unless you would be running the query with a different reference number frequently, you probably wouldn't notice the query.


Using AMMM or Huge Pages for Oracle 11g R2 Database

This is what Oracle says about it:

About HugePages

  If you have a system with more than 16GB of memory running Oracle
  databases with a total System Global Area (SGA) larger than 8GB, you
  should enable the HugePages feature to improve database performance.


MySQL: Filter results by joining a vertical table


Is a MERGE with OUTPUT better practice than a conditional INSERT and SELECT?

Because you are using a Sequence, you can use the same NEXT VALUE FOR function -- that you already have in a Default Constraint on the Id Primary Key field -- to generate a new Id value ahead of time. Generating the value first means that you don't need to worry about not having SCOPE_IDENTITY, which then means that you don't need either the OUTPUT clause or doing an additional SELECT to get the new value; you will have the value before you do the INSERT, and you don't even need to mess with SET IDENTITY INSERT ON / OFF :-)

So that takes care of part of the overall situation. The other part is handling the concurrency issue of two processes, at the exact same time, not finding an existing row for the exact same string, and proceeding with the INSERT. The concern is about avoiding the Unique Constraint violation that would occur.

One way to handle these types of concurrency issues is to force this particular operation to be single threaded. The way to do that is by using application locks (which work across sessions). While effective, they can be a bit heavy-handed for a situation like this where the frequency of collisions is probably fairly low.

The other way to deal with the collisions is to accept that they will sometimes occur and handle them rather than try to avoid them. Using the TRY...CATCH construct, you can effectively trap a specific error (in this case: "unique constraint violation", Msg 2601) and re-execute the SELECT to get the Id value since we know that it now exists due to being in the CATCH block with that particular error. Other errors can be handled in the typical RAISERROR / RETURN or THROW manner.

Test Setup: Sequence, Table, and Unique Index

USE [tempdb];

CREATE SEQUENCE dbo.MagicNumber
  AS INT
  START WITH 1
  INCREMENT BY 1;

CREATE TABLE dbo.NameLookup
(
  [Id] INT NOT NULL
         CONSTRAINT [PK_NameLookup] PRIMARY KEY CLUSTERED
        CONSTRAINT [DF_NameLookup_Id] DEFAULT (NEXT VALUE FOR dbo.MagicNumber),
  [ItemName] NVARCHAR(50) NOT NULL
);

CREATE UNIQUE NONCLUSTERED INDEX [UIX_NameLookup_ItemName]
  ON dbo.NameLookup ([ItemName]);
GO

Test Setup: Stored Procedure

CREATE PROCEDURE dbo.GetOrInsertName
(
  @SomeName NVARCHAR(50),
  @ID INT OUTPUT,
  @TestRaceCondition BIT = 0
)
AS
SET NOCOUNT ON;

BEGIN TRY
  SELECT @ID = nl.[Id]
  FROM   dbo.NameLookup nl
  WHERE  nl.[ItemName] = @SomeName
  AND    @TestRaceCondition = 0;

  IF (@ID IS NULL)
  BEGIN
    SET @ID = NEXT VALUE FOR dbo.MagicNumber;

    INSERT INTO dbo.NameLookup ([Id], [ItemName])
    VALUES (@ID, @SomeName);
  END;
END TRY
BEGIN CATCH
  IF (ERROR_NUMBER() = 2601) -- "Cannot insert duplicate key row in object"
  BEGIN
    SELECT @ID = nl.[Id]
    FROM   dbo.NameLookup nl
    WHERE  nl.[ItemName] = @SomeName;
  END;
  ELSE
  BEGIN
    ;THROW; -- SQL Server 2012 or newer
    /*
    DECLARE @ErrorNumber INT = ERROR_NUMBER(),
            @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();

    RAISERROR(N'Msg %d: %s', 16, 1, @ErrorNumber, @ErrorMessage);
    RETURN;
    */
  END;

END CATCH;
GO

The Test

DECLARE @ItemID INT;
EXEC dbo.GetOrInsertName
  @SomeName = N'test1',
  @ID = @ItemID OUTPUT;
SELECT @ItemID AS [ItemID];
GO

DECLARE @ItemID INT;
EXEC dbo.GetOrInsertName
  @SomeName = N'test1',
  @ID = @ItemID OUTPUT,
  @TestRaceCondition = 1;
SELECT @ItemID AS [ItemID];
GO

Question from O.P.

  Why is this better than the MERGE? Won't I get the same functionality without the TRY by using the WHERE NOT EXISTS clause?

MERGE has various "issues" (several references are linked in @SqlZim's answer so no need to duplicate that info here). And, there is no additional locking in this approach (less contention), so it should be better on concurrency. In this approach you'll never get a Unique Constraint violation, all without any HOLDLOCK, etc. It's pretty much guaranteed to work.

The reasoning behind this approach is:

If you have enough executions of this procedure such that you need to worry about collisions, then you don't want to:

take any more steps than are necessary
hold locks on any resources for longer than necessary

Since collisions can only happen upon new entries (new entries submitted at the exact same time), the frequency of falling into the CATCH block in the first place will be pretty low. It makes more sense to optimize the code that will run 99% of the time instead of the code that will run 1% of the time (unless there is no cost to optimizing both, but that is not the case here).

Comment from @SqlZim's answer (emphasis added)

  I personally prefer to try and tailor a solution to avoid doing that when possible. In this case, I don't feel that using the locks from serializable is a heavy handed approach, and I would be confident it would handle high concurrency well.

I would agree with this first sentence if it were amended to state "and _when prudent". Just because something is technically possible does not mean that the situation (i.e. intended use-case) would be benefited by it.

The issue I see with this approach is that it locks more than what is being suggested. It is important to re-read the quoted documentation on "serializable", specifically the following (emphasis added):

  Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Now, here is the comment in the example code:

SELECT [Id]
FROM   dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */

The operative word there is "range". The lock being taken is not just on the value in @vName, but more accurately a range starting at the location where this new value should go (i.e. between the existing key values on either side of where the new value fits), but not the value itself. Meaning, other processes will be blocked from inserting new values, depending on the value(s) currently being looked up. If the lookup is being done at the top of the range, then inserting anything that could occupy that same position will be blocked. For example, if values "a", "b", and "d" exist, then if one process is doing the SELECT on "f", then it will not be possible to insert values "g" or even "e" (since any one of those will come immediately after "d"). But, inserting a value of "c" will be possible since it wouldn't be placed in the "reserved" range.

The following example should illustrate this behavior:

(In query tab (i.e. Session) #1)

INSERT INTO dbo.NameLookup ([ItemName]) VALUES (N'test5');

BEGIN TRAN;

SELECT [Id]
FROM   dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */
WHERE  ItemName = N'test8';

--ROLLBACK;

(In query tab (i.e. Session) #2)

EXEC dbo.NameLookup_getset_byName @vName = N'test4';
-- works just fine

EXEC dbo.NameLookup_getset_byName @vName = N'test9';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

EXEC dbo.NameLookup_getset_byName @vName = N'test7';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

EXEC dbo.NameLookup_getset_byName @vName = N's';
-- works just fine

EXEC dbo.NameLookup_getset_byName @vName = N'u';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

Likewise, if value "C" exists, and value "A" is being selected (and hence locked), then you can insert a value of "D", but not a value of "B":

(In query tab (i.e. Session) #1)

INSERT INTO dbo.NameLookup ([ItemName]) VALUES (N'testC');

BEGIN TRAN

SELECT [Id]
FROM   dbo.NameLookup WITH (SERIALIZABLE) /* hold that key range for @vName */
WHERE  ItemName = N'testA';

--ROLLBACK;

(In query tab (i.e. Session) #2)

EXEC dbo.NameLookup_getset_byName @vName = N'testD';
-- works just fine

EXEC dbo.NameLookup_getset_byName @vName = N'testB';
-- hangs until you either hit "cancel" in this query tab,
-- OR issue a COMMIT or ROLLBACK in query tab #1

To be fair, in my suggested approach, when there is an exception, there will be 4 entries in the Transaction Log that won't be happening in this "serializable transaction" approach. BUT, as I said above, if the exception happens 1% (or even 5%) of the time, that is far less impacting than the far more likely case of the initial SELECT temporarily blocking INSERT operations.

Another, albeit minor, issue with this "serializable transaction + OUTPUT clause" approach is that the OUTPUT clause (in its present usage) sends the data back as a result set. A result set requires more overhead (probably on both sides: in SQL Server to manage the internal cursor, and in the app layer to manage the DataReader object) than a simple OUTPUT parameter. Given that we are only dealing with a single scalar value, and that the assumption is a high frequency of executions, that extra overhead of the result set probably adds up.

While the OUTPUT clause could be used in such a way as to return an OUTPUT parameter, that would require additional steps to create a temporary table or table variable, and then to select the value out of that temp table / table variable into the OUTPUT parameter.

Further Clarification: Response to @SqlZim's Response (updated answer) to my Response to @SqlZim's Response (in the original answer) to my statement regarding concurrency and performance ;-)

Sorry if this part is a wee-bit long, but at this point we are just down to the nuances of the two approaches.

  I believe the way the information is presented could lead to false assumptions about the amount of locking one could expect to encounter when using serializable in the scenario as presented in the original question.

Yes, I will admit that I am biased, though to be fair:

It is impossible for a human to not be biased, at least to some small degree, and I do try to keep it at a minimum,
The example given was simplistic, but that was for illustrative purposes to convey the behavior without over-complicating it. Implying excessive frequency was not intended, though I do understand that I also didn't explicitly state otherwise and it could be read as to imply a larger problem than actually exists. I will try to clarify that below.
I did also include an example of locking a range between two existing keys (the second set of "Query tab 1" and "Query tab 2" blocks).
I did find (and volunteer) the "hidden cost" of my approach, that being the four extra Tran Log entries each time the INSERT fails due to a Unique Constraint violation. I have not seen that mentioned in any of the other answers / posts.

Regarding @gbn's "JFDI" approach, Michael J. Swart's "Ugly Pragmatism For The Win" post, and Aaron Bertrand's comment on Michael's post (regarding his tests showing what scenarios have decreased performance), and your comment on your "adapation of Michael J. Stewart's adaptation of @gbn's Try Catch JFDI procedure" stating:

  If you are inserting new values more often than selecting existing values, this may be more performant than @srutzky's version. Otherwise I would prefer @srutzky's version over this one.

With respect to that gbn / Michael / Aaron discussion related to the "JFDI" approach, it would be incorrect to equate my suggestion to gbn's "JFDI" approach. Due to the nature of the "Get or Insert" operation, there is an explicit need to do the SELECT to get the ID value for existing records. This SELECT acts as the IF EXISTS check, which makes this approach more so equate to the "CheckTryCatch" variation of Aaron's tests. Michael's re-written code (and your final adaptation of Michael's adaptation) also includes a WHERE NOT EXISTS to do that same check first. Hence, my suggestion (along with Michael's final code and your adaptation of his final code) won't actually hit the CATCH block all that often. It could only be situations where two sessions, given the same non-existent ItemName, and running the INSERT...SELECT at the exact same moment such that both sessions receive a "true" for the WHERE NOT EXISTS at the exact same moment and thus both attempt to do the INSERT at the exact same moment. That very specific scenario happens much less often than either selecting an existing ItemName or inserting a new ItemName when no other process is attempting to do so at the exact same moment.

WITH ALL OF THE ABOVE IN MIND: Why do I prefer my approach?

First, let's look at what locking takes place in the "serializable" approach. As mentioned above, the "range" that gets locked depends on the existing key values on either side of where the new key value would fit. The beginning or ending of the range could also be the beginning or ending of the index, respectively, if there is no existing key value in that direction. Assume we have the following index and keys (^ represents the beginning of the index while $ represents the end of it):

Range #:    |--- 1 ---|--- 2 ---|--- 3 ---|--- 4 ---|
Key Value:  ^         C         F         J         $

If session 55 attempts to insert a key value of:

A, then range # 1 (from ^ to C) is locked: session 56 cannot insert a value of B, even if unique and valid (yet). But session 56 can insert values of D, G, and M.
D, then range # 2 (from C to F) is locked: session 56 cannot insert a value of E (yet). But session 56 can insert values of A, G, and M.
M, then range # 4 (from J to $) is locked: session 56 cannot insert a value of X (yet). But session 56 can insert values of A, D, and G.

As more key values are added, the ranges between key values becomes narrower, hence reducing the probability / frequency of multiple values being inserted at the same time fighting over the same range. Admittedly, this is not a major problem, and fortunately it appears to be a problem that actually decreases over time.

The issue with my approach was described above: it only happens when two sessions attempt to insert the same key value at the same time. In this respect it comes down to what has the higher probability of happening: two different, yet close, key values are attempted at the same time, or the same key value is attempted at the same time? I suppose the answer lies in the structure of the app doing the inserts, but generally speaking I would assume it to be more likely that two different values that just happen to share the same range are being inserted. But the only way to really know would be to test both on the O.P.s system.

Next, let's consider two scenarios and how each approach handles them:

All requests being for unique key values:

In this case, the CATCH block in my suggestion is never entered, hence no "issue" (i.e. 4 tran log entries and the time it takes to do that). But, in the "serializable" approach, even with all inserts being unique, there will always be some potential for blocking other inserts in the same range (albeit not for very long).
High-frequency of requests for same key value at the same time:

In this case -- a very low degree of uniqueness in terms of incoming requests for non-existent key values -- the CATCH block in my suggestion will be entered regularly. The effect of this will be that each failed insert will need to auto-rollback and write the 4 entries to the Transaction Log, which is a slight performance hit each time. But the overall operation should never fail (at least not due to this).

(There was an issue with the previous version of the "updated" approach that allowed it to suffer from deadlocks. An updlock hint was added to address this and it no longer gets deadlocks.) BUT, in the "serializable" approach (even the updated, optimized version), the operation will deadlock. Why? Because the serializable behavior only prevents INSERT operations in the range that has been read and hence locked; it doesn't prevent SELECT operations on that range.

The serializable approach, in this case, would seem to have no additional overhead, and might perform slightly better than what I am suggesting.

As with many / most discussions regarding performance, due to there being so many factors that can affect the outcome, the only way to really have a sense of how something will perform is to try it out in the target environment where it will run. At that point it won't be a matter of opinion :).


Add to your WHERE clause: AND DATEDIFF(DAY,'2019-02-09',TheDate) % 3 = 0

This will evaluate the number of days between your initial date and the current value of TheDate. The modulus (%) operator will return 0 for every 3 days in the timeline. Note that performance may take a hit evaluating this expression for every row, depending on the number of members in the dimension.


Adding a new primary key ID column to an existing table

You can add your GUID ID column with the following.

ALTER TABLE [yourSchema].[yourTable] add [ID] UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL

To do this for multiple tables you could use something like this:

SELECT 'ALTER TABLE '+QUOTENAME(s.name)+'.'+QUOTENAME(t.name)+' add [ID] UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL'

FROM sys.schemas s
JOIN sys.tables t ON t.schema_id = s.schema_id

WHERE (     (s.name = 'Schema1' AND t.name IN ('Schema1_TableList'))
            OR
            (s.name = 'Schema2' AND t.name IN ('Schema2_TableList'))
            ... -- Add more schemas and their tables as needed
        )

This will give you all the ALTER TABLE statements to create an ID UNIQUEIDENTIFER coulmn with the ID values pre populated.


What should be the schema of the database if the parent table is being referenced in more than one child table?

My approach in a model:
-- Categories and subcategories table
CREATE TABLE category (id INT PRIMARY KEY,
                       name VARCHAR(255),
                       parent_id INT,
                       FOREIGN KEY (parent_id) REFERENCES category (id));

-- Products table
CREATE TABLE product (id INT PRIMARY KEY,
                      name VARCHAR(255),
                      category_id INT,
                      FOREIGN KEY (category_id) REFERENCES category (id));

How it works: DEMO (MySQL used).
The queries are converted to procedures - only for simplifying their execution call.
If some section is empty we don't display it.
If we want to get the categories list with the info does they have subcategories we simply execute
SELECT *, EXISTS ( SELECT 1
                   FROM category t2
                   WHERE t1.id = t2.parent_id ) has_subcategory
FROM category t1;


I had to finally call Microsoft support and just a simple command called
    DBCC LOG INFO
on publisher revealed a possible root cause. I saw more than 8600 VLFs! and this was the cause of that latency. Also, our log file is preallocated to 538GB.

Replication was out of sync by almost 19 hours by the time I got follow up call from Microsoft help 4:00pm next day after I opened case with Microsoft. Steps to be taken were really simple. Backup publisher db log couple of time and try to shrink log file. Set increment factor for log file by 8GB or 12GB instead of percentage or 500MB. So, that next time log file grows, it will create 16 VLFs per 8GB or 12GB depending on your increment factor.

After I backed up log, I was able to shrink log file to 350GB and total VLFs to around 5300. Still higher. Latency didn't come down though. It went upto 22 hours. I started wondering whether number of VLFs was just one of the causes. However, around 11:30pm, latency reduced to around 7h30m and I freed up more space around that time, reducing VLFs to 2001. By 2am, replication was in sync. I hurried up and backed up log twice and shrink the log file to 10GB and grew it back to around 248GB. Total number VLFs as of now are 184 and replication is in sync since then. Whew! Log file is almost empty.

Let me know if you have any questions about this. I am glad to help. Hopefully someone else doesn't have to call Microsoft for this issue.


While restoring copy of SQL Server 2008 backup file in SQL Server 2008 R2 degraded performance

The odds are that on the restore, it is appropriate to update statistics on the db. In many cases, this step alone will help.

If it does not help, it may be that index fragmentation crept up and became an issue now, though my $ is on statstics...


Incremental Database/Data Change Restore

editted based on feedback in comments

If you could stay readonly than a good alternative to replication
could be log shipping. The vendor would create transaction log
backups. And send those backup to you. You then apply those
backups.You can't restore differential only, you would still need to
reply a full first. Another benefit of (delayed) logshipping is that, since the source is at the vendor (where you have no control over what they do with it, you might want to build in some buffer (in time) that when they make a huge mistake, you simply pause the logshipping redo, so you still have a good copy)
For restoring filegroups/files you need transaction log restores as well. It can work but it highly depends on the growth rate vs the transaction log rate if I would prefer this above option 1, simply log shipping. Furhtermore, partitioning your database and table just for the sole purpose of solving your replica problem wouldn't be the route I would pick first but I have some questions for you later.
Replication sounds like a very good option if you think the restoring of transaction log backups becomes to much work.
What prohibits sql 2000 from doing Bulk Inserts? However, if you think logshipping is tedious, keeping track of updating every single object sounds like a potentially more troublesome strategy.
I have no experience with cdc. somebody else has to help out on that.

Couple of questions for you:

How big is the initial database size?
What will be the daily, monthly growth rate?
What will be  the daily growth rate of the transaction log?
is it possible to have both locations in the same domain?
Is it possible to build a secure connection between the two locations?
What is the bandwidth between the to locations? what is the latency?

If we have some numbers, it's easier to provide you with a better advise.


Dynamic paths for postgres file_fdw (csv)

My question: Is there a way to point the base of the file path to a non-static value to abstract that change? Or would I be better off writing a script to collect the list of foreign tables, drop them, and rebuild the DDL with new filepath arguments?

You can point to a symlink. However, I wouldn't. I would keep things running just the way you're doing it now. What's the reason to do that? I imagine under any reasonable load the DDL is not an issue. But, it may be worth experimenting and reporting back.


Why can't I insert an email on a table that has one more dot than an existing one?

rdms usually don't make mistakes
So run a query
SELECT * FROM users WHERE lower(email::text) = 'fake.email@domain.com'

and see where it gets you a result, with upper letters


Without an ORDER BY clause, you have no guarantees as to the order the rows are presented to you, even if they 'look' ordered when you run a select.  Bullet 1 of this link

https://msdn.microsoft.com/en-us/library/ms188385.aspx


Why can't I create a new schema with the owner set to dbo?

The db_datareader, db_datawriter, and db_ddladmin permissions will not give the necessary privilege for this action.

db_datareader:    Members of the db_datareader fixed database role can read all data from all user tables.
db_datawriter:    Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
db_ddladmin:  Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

As you can see, these permissions will give you privileges to create and manipulate some database structure. However, they will not give you the privileges to alter other user or create schema.
Check this image from Microsoft of database level roles and permissions

Note that permission db_ddladmin does not grant you the privilege to create SCHEMA. To do this you need set your test user as a membership of db_accessadmin. This role will grant privileges to create schema and alter any user. The alter any user privileges will be necessary to set dbo user as a schema owner:
USE [your_database]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [teste]
GO

After this, you will can authorize DBO in the new schema like:
USE [your_database]
GO
CREATE SCHEMA [test] AUTHORIZATION [dbo]
GO

Another role will grant permissions to do this is the db_owner, but is "some powerful" permission to this. That is why the correct role is db_accessadmin. You can read more about each permission at this link
Remember to use the commands in the correct database with:
USE [your_database]


Restore / Create a database with a data file (MDF) from backup the same but with two data files (MDF and NDF)

You need to include the .NDF file in your restore script.

 RESTORE DATABASE mydb
    FROM  DISK='\\150.10.10.12\backup\mydb.bak'
    WITH
        REPLACE
      , RECOVERY
      , MOVE 'IMAGENES_Data' TO 'F:\MSSQL\Data\Mydb.mdf'
      , MOVE 'IMAGENES'      TO 'F:\MSSQL\Data\Mydb.ndf'
      , MOVE 'IMAGENES_Log'  TO 'G:\MSSQL\LOG\Mydb_LOG.ldf';


Backup compression was introduced in SQL 2008 Enterprise, and in SQL2008R2 and later, added to Standard Edition.

When creating a backup, you can specify the WITH COMPRESSION keyword, which will ensure that the database backup size is compressed to approximately a similar size as a zipped 'normal' backup file.

For SQL2005 or older, the best way really (other than using a specific tool like RedGate) is to ensure that xp_cmdshell is enabled on the instances, and then use a command line to compact using for e.g. WinRar.

I use a cmd file that looks something like this:

@echo off
Set "winrarPath=C:\Program Files\WinRAR"
"%winrarPath%\winrar.exe" a -r "ZippedBackup.zip" "BackupFile.BAK"

You can then execute this cmd file from your 2000 / 2005 instances.  You can also play around with passing %1-type variables to the cmd file, if your filenames are not generic.


Measure Performance for INSERT, UPDATE and DELETE commands on DB2

I guess it's a locking issue.

While such inserts take so long use admin view below. The column HLD_CURRENT_STMT_TEXT shows the blocker statement (what cause the insert took so long):

db2 select * from SYSIBMADM.MON_LOCKWAITS


There should be a table for every box, ie entity type/class, and every diamond, ie relationship type/class. There should be a column for every oval, ie attribute/property. Each line from a diamond to a box is indicates a foreign key. Relationship PKs are composite and consist of FK columns to associated/participant/referenced entity types.


After upgrade to 2016, can't view SQL Server Logs

Windows patching and majid's solution is what worked for us too. Just adding my 2c that may help others. We have a cluster with 2008, 2014 SP2 and 2016 CU2. After upgrading an instance to 2016, we had the following errors:

2014 and 2016: syspolicy_purge_history job failed on step 3
2016: SQL agent would start then stop
2016: Couldn't view SQL error logs

Windows patching, which primarily applied .net security patches fixed the syspolicy_purge_history job in 2014. The new ODBC driver fixed remaining issues in 2016.

Note: installing 2016 SP1 before installing this driver (https://www.microsoft.com/en-us/download/details.aspx?id=53339), will install a newer ODBC driver, that appears to have the same issues when running on a cluster. We attempted this on a test environment, but had to uninstall SP1 and the new ODBC 13.1 driver, then install CU2 and the other ODBC 13.1 driver. Haven't attempted to install SP1 again yet


Auto Increment Key Pair with Start Digit at 200000 SQL SERVER 2008

Here is one way, note that this effectively limits concurrency, so don't expect stellar performance at scale.

INSERT dbo.TableName(col1, col2)
  SELECT @ClientName, COALESCE(
    (SELECT MAX(col2) + 1
     FROM dbo.TableName WITH (UPDLOCK, HOLDLOCK)
     WHERE col1 = @ClientName), 200000);

Of course if anyone ever deletes any rows from this table, you'll be missing values. And if an insert gets rolled back, that rolled back number will be reused, so be careful if you reveal that assigned value anywhere prior to the rollback.


Problem with InnoDB "per table" file sizes

Please keep in mind that TRUNCATE TABLE is a DDL command that tells the InnoDB storage engine to pretend there are no records.

According to the MySQL Documentation on TRUNCATE TABLE :

  When fast truncation is used, it resets any AUTO_INCREMENT counter to
  zero. From MySQL 5.0.13 on, the AUTO_INCREMENT counter is reset to
  zero by TRUNCATE TABLE, regardless of whether there is a foreign key
  constraint.)

However, doing TRUNCATE TABLE does nothing for space already allocated.

To quickly drop the space down to zero rows, you can do the following:

#
# Algorithm 1
#
CREATE TABLE watchdog_new LIKE watchdog;
ALTER TABLE watchdog RENAME watchdog_zap;
ALTER TABLE watchdog_new RENAME watchdog;
DROP TABLE watchdog_zap;

Since TRUNCATE TABLE tells mysql that the table is considered empty, you can do this:

#
# Algorithm 2
#
TRUNCATE TABLE watchdog;
ALTER TABLE watchdog ENGINE=InnoDB;

These two steps behave the same way as Algorithm 1.

These steps also can be done with

#
# Algorithm 3
#
TRUNCATE TABLE watchdog;
OPTIMIZE TABLE watchdog;

Internally, OPTIMIZE TABLE would perform Algorithm 1 and then perform ANALYZE TABLE. For InnoDB, the ANALYZE TABLE phase of OPTIMIZE TABLE is bypassed. In fact, it is unnecessary because index statistics are recomputed on each query executed against an InnoDB table.


Query to obtain indexes information - why does it produce duplicated values?

If you check where the different column values come from (PAGE_COUNT and AVG_FRAGMENTATION_IN_PERCENT), you will see they are from the system function SYS.DM_DB_INDEX_PHYSICAL_STATS.

Just before joining with SYS.DM_DB_INDEX_PHYSICAL_STATS you have joined SYS.INDEXES with SYS.DM_DB_PARTITION_STATS, so your row is detailed by object_id, index_id, partition_number. Try joining by these 3 against SYS.DM_DB_INDEX_PHYSICAL_STATS (since the function result is actually detailed by these 3):

INNER JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) AS IXS ON
    IXS.OBJECT_ID = T.OBJECT_ID AND
    IXS.index_id = I.index_id AND
    IXS.partition_number = PS.partition_number


Are you attempting to insert a JSON array with two objects, Test1 and Test2?

If so, then you need to insert the data as an array:

JSON array:

[{
  "Test1": {
    "Val1": "37",
    "Val2": "25"
  }
}, {
  "Test2": {
    "Val1": "25",
    "Val2": "27"
  }
}]

Your INSERT statement would look similar to the following (note the wrapping brackets around the JSON objects):

INSERT INTO zz_TEST_ObsJSON (jsonData) values ('[{"Test1":{"Val1":"37", "Val2":"25"}},{"Test2":{"Val1":"25", "Val2":"27"}}]');


Using count() and sum() in a calculation in a select

GROUP BY and the HAVING clause are what you are looking for. GROUP BY allows you to use aggregate functions without having to only have 1 row of data as you currently do. Here, we're going to compute the count of all the users and to total bandwidth for all the users at that company. I don't quite get your tipping point so the logic there may be off. I guess I'm reading it as "we're sharing 1GB of data across all these users"

The HAVING is similar to our WHERE but operates on the GROUPed BY data. Here I've specified that I only want rows where the total bandwidth is over 1GB.

Unless you need something from the Company table, there's no real reason to add them to the query since all of your data is on the User record.

SELECT
    U.CompanyId
,   COUNT(*) as numUsers
,   SUM(U.BandwidthThisMonth) as totalBandwidth
FROM
    Company C
    INNER JOIN
        [User] U
        ON U.CompanyId = C.CompanyId
GROUP BY
    U.CompanyId
HAVING
    -- this assumes storage vendor math and not true GB
    -- As a consumer, I'd expect 1GB of bandwidth to be 1 073 741 824
    SUM(U.BandwidthThisMonth) > 1000000000;


It's not documented in the official docs (as of now - Feb 2018), but PgPool's memory footprint is quite heavy.

In my recent testing PgPool II version 3.6 needs as much as 140 MB RAM per child process. (the number of child processes is defined in num_init_children). This is private process memory - not shared.

This means approximately 8GB for each 50 clients.

Compared to PostgreSQL, this is 5-10 times more (PostgreSQL can easily handle 250 sessions on 8 GB RAM). Plus, PostgreSQL uses shared buffer cache which is much more cost effective.


Improving indexing of jsonb data when the json structure is well defined

If category were a regular column and not an expression, PostgreSQL would choose an index-only scan, which would be much faster if the table has been vacuumed recently. But index-only scans are not supported on expression indexes like that, see the documentation:

In principle, index-only scans can be used with expression indexes. For example, given an index on f(x) where x is a table column, it should be possible to execute
SELECT f(x) FROM tab WHERE f(x) < 1;

as an index-only scan; and this is very attractive if f() is an expensive-to-compute function. However, PostgreSQL's planner is currently not very smart about such cases. It considers a query to be potentially executable by index-only scan only when all columns needed by the query are available from the index. In this example, x is not needed except in the context f(x), but the planner does not notice that and concludes that an index-only scan is not possible. If an index-only scan seems sufficiently worthwhile, this can be worked around by adding x as an included column, for example
CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);

An additional caveat, if the goal is to avoid recalculating f(x), is that the planner won't necessarily match uses of f(x) that aren't in indexable WHERE clauses to the index column. It will usually get this right in simple queries such as shown above, but not in queries that involve joins. These deficiencies may be remedied in future versions of PostgreSQL.

So you would jave to include the JSON column in the index to get an index-only scan. This may be impossible, if the size of the JSON is too big. Otherwise, it may still improve the speed of the query, even though such a large index will we much less efficient.
Essentially, you are suffering from a bad data model. Given that the structure of the JSONs is the same for all rows, that should certainly have been designed using normal table columns and avoiding jsonb. Then the problem would not exist.


So our sysadmin just told me:
"we use VMWare virtualization, and the system is fully optimized as a VM (virtio, etc.).You are trying to do the read and the write to an NFS mounted disk (my home directory)"
I tried the command in /tmp and it worked there.


BULK INSERT continue on PRIMARY KEY error

I think a good approch would be to have a staging table (without PK constraint).
You will load your data from your bulk command into this table and then, you should be able to script down the logic you want to implement into a store procedure that you can run after
Ex: Check for duplicate, log them to another table or send a mail, delete the duplicate base on some logic and then insert the remaining rows into the target table.


Write to table with transaction without locking

Is it possible to write to a database table using a transaction
  without locking it?

Depends on what you mean by "it"?  locks can be at different resource level meaning: row, page, table, non clustered index pages etc.

If you mean all of the above than the answer is no. At the same time you can avoid locks on some of the resources depending on the scope of your transaction and how many records you are updating.

I suggest you read this article :

Why do we need UPDATE Locks in SQL Server? by Klaus Aschenbrenner

Now let's look at an example using adventureworks database. To simulate what I am doing you can download this database from here.

 USE [AdventureWorks]
 GO

--Making sure we are only updating one row
SELECT COUNT(0) FROM [Production].[Product]
WHERE ProductId=318

BEGIN TRAN
UPDATE [Production].[Product]
    SET [Name]='ML Crankarm_modified'
    WHERE ProductId=318
--ROLLBACK TRAN

Since we are using BEGIN TRAN (explicit transaction) we can check what locks this session is holding.

SELECT
  resource_type,
    resource_database_id,
    resource_associated_entity_id,
    request_mode,
    request_status,
    request_session_id
FROM sys.dm_tran_locks
WHERE request_session_id=59

Looking at these locks and lock compatibility chart you should be able to figure out what select statements you can run and which one will get blocked. Keep in mind it will also be dictated by your transaction isolation level.

For example you have Intent Exclusive lock on object id 482100758 which is production.product table itself and this lock is not compatible with Shared lock. Therefore you cannot select all records from this table.  But if you want to select one or more records that does not have locks incompatible with Shared lock you should be able to select data.


Select query - group by and sub group by

If I have figured correctly, what you are after:

; WITH cte AS
  ( SELECT pid, uid, event, timeStamp,
           maxts = MAX(timeStamp) OVER (PARTITION BY pid, uid)
    FROM tableX
  )
SELECT pid, cnt = COUNT(*)
FROM cte
WHERE maxts = timeStamp
  AND event = 200
GROUP BY pid ;

If there is a chance that you have 2 rows with identical pid, uid and timestamp, you'll need to replace COUNT(*) with COUNT(DISTINCT uid).


It turned out that I was creating the database and the user correctly. What I was doing wrong was not connecting to my database correctly through the Knexfile.

I looked at the docs from http://knexjs.org/

Once I adjusted the object in my knexfile to specify the database, I was able to run my migrations successfully.


How to prevent null foreign key during database normalization?

NOTE

Many-to-Many relationships are represented by a linking table.

You are missing the link between Student(s) and Class(es)

create class_enrollment (
  student_id int not null references Student( id ),
  class_id   int not null references Class( id ),
  constraint class_enrollment_pk primary key ( student_id, class_id )
);

Adjustment

Your Grades table can then be modified to reference the PK of class_enrollment.

create table GRADES (
  student_id  int,
  class_id    int,
  category_id int,
  grade_val    number,
  constraint grade_pk primary key
     (student_id, class_id, category_id), -- a surrogate key is recommended
  constraint grade_fk1 foreign key (student_id, class_id)
     references class_enrollment(student_id, class_id)
);

Overall Grade

If you can always calculate a Summary value, You can hide the calculation in a VIEW (or a MATERIALIZED VIEW).

But, since teachers can override the calculated value, you'll want to store the Summary in the "parent table".

In your case, the "parent table" to Grades would be Class_Enrolement.

alter table class_enrollment add ( overall_grade  number );


Unique identifiers for the same type of object in the same request

Normalizing this will result in three tables with an many:many relation, the columns in parens () indicate the logical Primary Key:

groups: (group_id)

ingredients: (ingredient_id), name

ingredients_per_group: (group_id, ingredient_id), position

This is the junction table between groups and ingredients, the position is just an attribute of the ingredient in this specific group.

If an ingredient could be used many times in the same recipe but in different positions, (say use some butter in step 2 but also in step 17), then the PK of ingredients_per_group should be (group_id, ingredient_id, position).

It's like an ingredient for a receipt, the same ingredient can/will be used in multiple receipts, but in different amounts or in a different order.


Does Microsoft SQL Server offer an Advisory Locks feature like Postgres?

Application locks. They are arbitrary locks held by the engine on behalf of the app. Acquired with sp_getapplock and released with sp_releaselock (if needed). Shared, Update, Exclusive and intent modes with the usual compatibility matrix. Can have transaction or session scope. The behavior matches the PG advisory locks in every aspect, but is a stored procedure, not a function (so it cannot be used inline in a SELECT, for instance).


Create view using tables from differing databases on a server

You'll need to hard-code the view on each server.  But you can write a stored procedure to recreate the view on-demand.


You might notice the problem is :

ENGINE=MyISAM

Try changing that to:

ENGINE=InnoDB


Can I combine SELECT TOP() with the IN operator?

One method is with CROSS APPLY:

SELECT top_50.ColumnName
FROM (VALUES(1),(2),(3)) AS id_list(ID)
CROSS APPLY(SELECT TOP(50) t.ColumnName
    FROM TableName AS t
    WHERE t.ID = id_list.ID
    ORDER BY t.ColumnName) AS top_50;

To avoid returning random rows with TOP, ORDER BY is needed and the ordered by columns should be unique. An index with a composite  key on ID and ColumnName will be useful in this example query for efficiency.


does last_insert_id() returns correct result when writes from multiple sessions?

Here's what the MySQL 5.5 documentation says:

  The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

Unless you fall into the other caveats noted in the question you link to, the last_insert_id() function will return what you expect, and will not be affected by other sessions.


Where can I find guidelines for building a SQL Server machine specifically for DBCC CHECKDB work?

You want disks that are as fast as you can afford.  DBCC CHECKDB is going to be limited by physical IO speed and pretty much nothing else.

That said, you do still need to run DBCC CHECKDB on your production server as you aren't doing physical IO checks on the production server at this point.


Help performance tuning master/detail (email like inbox) SQL query

A few thoughts:

Your WHERE clause needs a supporting index

WHERE MT.ThreadSentTo=@UserId OR MT.ThreadStartedBy=@UserId really needs two indexes to be efficient - one on the ThreadSentTo field, and one on the ThreadStartedBy field. Otherwise, the SQL engine will be performing a full table scan to retrieve the correct threads.

Use OFFSET... NEXT N ROWS ONLY instead of ROW_NUMBER()

Starting in SQL 2012, a new construct was added to SQL Server for handling paging. This works like this:

DECLARE @PageNumber int = 20
DECLARE @RowsPerPage int = 15

SELECT *
FROM MyTable T
INNER JOIN MyDetailTable D
    ON T.MyTableID = D.MyTableID
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

In this case, the query will skip the first 285 ((20-1)*15) rows, and retrieve the next 15 rows. This is a faster paging method than the older RowNumber() filter for normal paging.


In my experience, the question around putting enums and their descriptions into code or the database, really depends on the complexity and target audience of the app you're writing.
To be more precise, I tend to store descriptions of enum values in the database when there is a chance that I'll be localizing the database and need to share that localization with reporting solutions or 3rd party systems that won't accept localization through some other more traditional method.
Having said that, I wouldn't suggest extrapolating enums and their descriptions into an entity-attribute-value system.  Typically, I might create an enum table for each data or fact table in my database.  So, if I have a table for storing Invoices, and that table has a column for InvoiceStatus, I might have a table called InvoicesStatusDescriptions with a couple of rows that describe the various invoice statii.  Then, if I want to quickly localize the app, I can simply change the description value for each enum value.
An example may help:
CREATE TABLE dbo.Invoices
(
    InvoiceID int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT Invoices_pk
        PRIMARY KEY
        CLUSTERED
    , InvoiceStatusID tinyint NOT NULL
        CONSTRAINT Invoices_status_fk
        FOREIGN KEY
        REFERENCES dbo.InvoiceStatusDescription(InvoiceStatusID)
);

The InvoiceStatusID column references the following table:
CREATE TABLE dbo.InvoiceStatusDescription
(
    InvoiceStatusID tinyint NOT NULL
        CONSTRAINT InvoiceStatusDescription_fk
        PRIMARY KEY
        CLUSTERED
    , InvoiceStatusDescription nvarchar(30) NOT NULL

);

Some sample data:
INSERT INTO dbo.InvoiceStatusDescription (InvoiceStatusID, InvoiceStatusDescription)
VALUES (1, N'Started')
    , (2, N'Payment Requested')
    , (3, N'Payment Received')
    , (4, N'Closed');

INSERT INTO dbo.Invoices (InvoiceStatusID)
VALUES (1)
    , (3);

Then you can provide a query like this to get localized versions of the status of each invoice:
SELECT i.InvoiceID
    , id.InvoiceStatusDescription
FROM dbo.Invoices i
    INNER JOIN dbo.InvoiceStatusDescription id ON i.InvoiceStatusID = id.InvoiceStatusID

With the sample data, output looks like this:

InvoiceID
InvoiceStatusDescription

1
Started

2
Payment Received

You can even add a column to the InvoiceStatusDescription table indicating the locale, but in my experience it would be easier to simply replace the description values in each enum table when the user wants to switch to a different locale.
Having said all that, your code is still going to need to understand the meaning of the enum values, so you'll still likely create discrete enums in your code.  Keeping the two systems in sync can be a challenging problem space in large systems.


SQL query to return a column per value in value range?

SELECT CAST(YEAR(date) AS VARCHAR(4)) + '-' + CAST(MONTH(date) AS VARCHAR(2)) AS Date
    , [0-19000] = COUNT(CASE WHEN Value<=19000 THEN 1 END)
    , [19000-20000] = COUNT(CASE WHEN Value > 19000 and Value<=20000 THEN 1 END)
    , [20000-21000] = COUNT(CASE WHEN Value > 20000 and Value<=21000 THEN 1 END)
    , [21000-22000] = COUNT(CASE WHEN Value > 21000 and Value<=22000 THEN 1 END)
    , [22000-23000] = COUNT(CASE WHEN Value > 22000 and Value<=23000 THEN 1 END)
    , [23000-24000] = COUNT(CASE WHEN Value > 23000 and Value<=24000 THEN 1 END)
FROM [SomeTable]
GROUP BY CAST(YEAR(date) AS VARCHAR(4)) + '-' + CAST(MONTH(date) AS VARCHAR(2))


Update staging table records in SQL Server 2017 with INSERT trigger and table-valued function, using INSERTED records

With Scott Hodgin's help (see comment thread above), I was able to figure out the issue:

First issue:  Since I certainly was using an OLE DB destination, I needed to add FIRE_TRIGGERS in the Advanced Editor (Microsoft, make this easier to find)

Second issue: once the INSERT trigger was firing, it was updating every record in the table, not just the ones where the function returned a value.  Of course it was; there was no WHERE clause or INNER JOIN to limit what needed to be update.  I modified my INSERT trigger to include the fields from the INSERTED table, and added a WHERE clause as follows:

CREATE TRIGGER [Staging].[INSERT_New_Pricing_tg]
    ON [Staging].[OrderDetail01_tb]
    AFTER INSERT
    AS
    BEGIN
        SET NOCOUNT ON

        UPDATE Staging.OrderDetail01_tb
            SET NewListCost = A.CustomerPrice,
                NewPriceVersion = A.NewPriceVersion
            FROM (SELECT I.Company,
                            I.CustomerNumber,
                            I.UPC,
                            LU.CustomerPrice,
                            LU.NewPriceVersion
                    FROM inserted I
                    CROSS APPLY (SELECT * FROM dbo.Find_New_Price_fn(I.Company, I.CustomerNumber, I.UPC)) LU) A
            WHERE Company = A.Company
                AND CustomerNumber = A.CustomerNumber
                AND UPC = A.UPC
    END

Thank you very much Scott for starting me on the right path.  Without your help and that link, I never would have solved this.


Count up number of rows matching criteria in same table

Using GROUP BY should avoid to use the subquery.

SELECT link_id_fk,
       COUNT(*) AS total
FROM   lsql_Flex_Values flex
WHERE  flex_type = 'address'
GROUP BY link_id_fk;


How to troubleshoot how resource governed query is being throttled in SQL 2012?

An update - I still don't know how best to detect what type of throttling has gone on, but I did find the culprit in my case. It does seem that the CPU CAP to 75% was causing a much higher decrease in performance than a 25% drop should suggest. Raising the CPU Cap to 100% fixed the problem.

A connect article seems to validate this might be a problem with the CPU Cap implementation itself.

Its not the best answer to this question, and if someone still wants to answer the original "how to tell what throttling is going on..." question. I'd be amenable.

If you are struggling to understand why your queries are being throttled so severely, this might be a solution worth trying for it.

http://connect.microsoft.com/SQLServer/feedback/details/781335/resource-governor-feature-cap-cpu-percent-does-not-work-as-expected


How to verify postgresql db restore from pg_dump file

What is the exact problem you are trying to solve? pg_dump and pg_restore are official supported PostgreSQL tools, so you should just assume they are correctly implemented. You have a few options on checking databases are identical though, depending on how certain you need to be they are identical.

Your most basic check would be to check that both databases have the same tables. You can find a list of tables via the information schema. For a loose check that the data is identical, you could look at the amount rows in these tables, and the upper and lower bound of primary keys (assuming you have primary keys where ordering makes sense).

If you really need to guarantee the data is identical, then you will need to produce some sort of hash and compare that, and that's essentially what you are doing with sorted dumps. It's worth to remember though that the database is more than just tables, you need to check sequences too.


A tablespace is a logical entity in Oracle database which consists of physical data files stored on the underlying filesystem.

We provide a quota of a certain size on the tablespace to a user. Which allows the user to create objects on it. And the user can explicitly defines on which tablespace he/she wants to create the object.

Here is a simple example which represents(as far as I understand) your scenario.

-- Create two tablespaces
SQL> create tablespace meta_data datafile size 1m;

Tablespace created.

SQL> create tablespace data datafile size 1m;

Tablespace created.

--Create a user and assign quota on one of the tablespaces
--or you can define a tablespace a default tablespace
--on which user can create an object by default.

SQL> create user user1 identified by user1 quota unlimited on meta_data
  2  ;

User created.

--Assign quotas on another tablespace
SQL> alter user user1 identified by user1 quota unlimited on data;

User altered.

--Now, the user can create objects on both of the tablespaces.
--In order to make a connection, he or she needs to have
--`create session` system privilege

SQL> grant create session to user1;

Grant succeeded.

SQL> conn user1@orclpdb
Enter password:
Connected.

--Let's create a table on meta_data tablespace.

SQL> create table t1(col1 number) tablespace meta_data;
create table t1(col1 number) tablespace meta_data
*
ERROR at line 1:
ORA-01031: insufficient privileges

--Oops, lack of create table privilege

SQL> conn sys@orclpdb as sysdba
Enter password:
Connected.
SQL> grant create table to user1;

Grant succeeded.

SQL> conn user1@orclpdb
Enter password:
Connected.
SQL>  create table t1(col1 number) tablespace meta_data;

Table created.

SQL> create table t2(col1 number) tablespace data;

Table created.


Does the SQL Server Backup Database Command also backup views from a Database?

The backup command backs up views, stored procedures, user-defined functions and so on.To test:

Create a database
Create a table
Insert something in it
Create a view on this table
Restore the database with another name; or
Drop the database and restore the previous .bak


low memory < 1G+- available, so the goal is not to exceed 512mb of total mysql usage

I'll assume 0.5GB.

max_connections=1501 #we only use up to 200, setting this to avoid too many conn error

That will chew up a lot of RAM that you do not have.  I would set it to no more than 50 and then work on making sure the applications throttle back how many connections they ask for.

If you have more than, say, 10 active threads, you could encounter them stumbling over each other -- this leads to performance issues of its own.

innodb_buffer_pool_size=128M

This is probably all you can afford in that tiny RAM.  More than that will lead to swapping; less than that could lead to cases where it simply can't get the job done.

innodb_log_file_size=64M

That's a disk file limit; it has no impact on RAM allocation.

innodb_log_buffer_size  16777216

is in RAM.  Cut that back to 4M.

The 80% rule is fine if you have more than 4GB of RAM.  A better rule is "80% of available RAM".  The issue is that you need the OS, the mysql code, and many other caches, buffers, etc.

max_allowed_packet=2G #due to large queries during data migration

Duh?  That is allocated in RAM, but you don't have that much RAM??  Imagine how much swapping will happen!  If you are loading a dump from mysqldump, rebuild the dump so that it does not build huge INSERT statements.  If you have 2GB BLOBs, you may be in big trouble.  Then cut that back to 32M (I guess).

innodb_buffer_pool_chunk_size is relevant only if you dynamically resize the buffer_pool.  (You are not likely to do this.)

Toss the query cache:  query_cache_size = 0 and query_cache_type = OFF.

tmp_table_size and max_heap_table_size -- Set to 1M.  These may be allocated once per connection or even more often (in the case of a complex SELECT).  MySQL has a fallback if these are "too small", so you won't lose functionality.

sort_buffer_size -- 4M

There may be more.


Create Linked Server to AlwaysOn Availability Group Listener

Finally found a tutorial here: http://www.derekseaman.com/2014/10/sql-2014-always-ag-pt-12-kerberos.html

The problem for me was that I needed to register the SPN for the Availability Group against the Managed Service accounts that are used to run the SQL Database Engine (step 8 in the link). Once I did that and restarted all 3 servers in the cluster everything works.


SQL Server 2014 and SQL Server 2014 Management Studio

You have to run the main installer, and choose to add the shared feature. The fact that it's available as a separate download as well is a bonus for people who don't want to have to download such a large ISO, but the SSMS installer is definitely part of the main installer - you just have to choose it from the "shared features" section.


Clustered Table Scan Because of "SELECT *"

If you need columns in the output that aren't covered by the index, the optimizer has to make a choice:

Perform a table / clustered index scan (therefore all columns are there)
Perform a seek, then perform lookups to retrieve the columns not covered

Which way it will choose depends on a variety of things, including how narrow the index is, how many rows match the predicate, etc. You can force a seek with the FORCESEEK hint, but I suspect it will end up performing the same or worse than the scan SQL Server has chosen in your case.

Some options:

Change the app to run a proper query. I listed this first for a reason.
Create a view that selects only the columns you need:

CREATE VIEW dbo.myview
WITH SCHEMABINDING
AS
  SELECT col1, col2, col3 FROM dbo.tablename;

Then you can change the app to SELECT * from this view. Or you can get even more creative and rename the original table, and change the name of this view to what the name of the table used to be. Breaking change, obviously; proceed with caution.
Add all of the other columns to the key or INCLUDE list for the index. If these are hard-coded values and always the ones used, you may consider a filtered index.


Expression index on a citext column ignored, why?

Please edit your question, rather than posting answers to it that don't answer it.

If you create an index on the expression substring(old_value,1,1024), then that index can only get used if you query involves substring(old_value,1,1024).

While it is theoretically possible to prove that old_value='foo' implies that substring(old_value,1,1024)='foo' (and thus the contrapositive to that) if you have enough insight into the internals of substring, PostgreSQL makes no attempt to prove that.  You need to write the query in a way that no such proof is needed.


Are there any benefits in disabling mongodb journaling besides performance gain?

The only other marginal benefit is if you are using a 32-bit build (which is also not recommended for production use). Since 32-bit builds are limited to ~2Gb of addressable data for memory mapped files, the journal is off by default to allow for more data (with journal enabled, the 32-bit data limit is halved to ~1Gb). In all other cases, the journal is enabled by default. For 64-bit systems, the addressable virtual memory is not a limiting factor for data size.

Any performance gain from disabling the journal is a tradeoff for data & operational risk. I would strongly advise against disabling the journal unless your data is ephemeral (i.e. you don't care about losing it or can easily recreate it from another source of truth).

If you are concerned about possible contention or overhead from disk I/O of journaling, you could always move the journal to a separate volume. NOTE: moving the journal to a separate volume may also change your backup strategy, particularly if you were relying on filesystem snapshots to take a comparatively quick backup without having to fSyncLock() and quiesce your database.

  I know journaling provides write durability and crash resiliency. But I can tune syncdelay parameter to make data files flush more frequently, but mongodb docs recommends against this.

Writes to the journal are fast append-only operations. As you've noted, these provide durability and crash resiliency. If MongoDB stops unexpectedly, it can recover from the journal and data will be in a consistent state.

Without the journal, your data is in an unknown state after an unexpected shutdown and you need to validate it by running repair or (in the case of a replica set node) resync from a known-good copy of the data.

  What effect would it have on my mongodb setup and why should I not set syncdelay on production setups?

The syncdelay setting controls the frequency where the in-memory view is synced with the on-disk view of data (aka the "background flush interval").

By default, the journalCommitInterval is 100ms and the syncdelay (background flush) happens every 60 seconds.

If you adjust the syncdelay too low, you can end up creating a lot of additional I/O (and reduced performance) because writes to dirty pages can no longer be effectively batched together and the same page will be re-written multiple times. If you adjust the syncdelay higher, you can create I/O spikes (and reduced performance) with a large volume of changes being committed in a single batch.

The syncdelay and journalCommitInterval parameters are available as tuneable settings because in some cases it may make sense to adjust these (for example, to try to temporarily help an under-provisioned I/O system by reducing write spikes). In a healthy production system, it would be best to leave these settings as-is.

For more information, How MongoDB’s Journaling Works is a helpful read.


Can SQL Server 2008 R2 extended events execute stored procedure or query?

Out of the box, no.

You'd need to hook into the event stream for extended events and then take action based on that. Tom Stringer has a good overview and sample code to do this!

See also Introducing the Extended Events Reader by Mike Wachal.

Jonathan Kehayias also describes it in detail in his Pluralsight course.


Why are InnoDB's index keys limited to 767 bytes?

This seems to be linked to the size of the InnoDB pages:

  If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.

Thanks to XL on the #mysql IRC chat for the pointer to page sizes. I will update this answer when I find out more about the InnoDB page format and its limitations.

UPDATE
The quote above applies to the total index key length for a row, whereas my original question was about the index key length for a single-column index. My guess at this point is that the 767-byte limitation is legacy from the time when all pages were 4KB in size, and thus both single-column and multi-column indexes were limited to a total length of 767 (+1?) bytes.
I will update this question again when I have more information about:

how the page size influences the index key length,
why there is a 1-byte difference in maximum key length between single-column indexes and multi-column indexes, and
why the legacy value of 767 bytes (if it is indeed a legacy value) was kept for single-column index key lengths.


Can I install Oracle 12c and NOT use pluggable databases?

Yes it is possible to use single tenant..
here you go --> Single Tenant Configuration


Why does 'plpgsql' not appear in the pg_extension table?

On PostgreSQL 9.1, it is possible to get to this state without directly manipulating the catalogs.

I started from having plpgsql as an extension, and did the following:

# DROP EXTENSION plpgsql CASCADE; -- do this only if you are prepared to lose your plpgsql functions, too
# \dx
                                     List of installed extensions
     Name      │ Version │ Schema │                            Description
───────────────┼─────────┼────────┼────────────────────────────────────────────────────────────────────
 btree_gin     │ 1.0     │ public │ support for indexing common datatypes in GIN
 btree_gist    │ 1.0     │ public │ support for indexing common datatypes in GiST
 dblink        │ 1.0     │ public │ connect to other PostgreSQL databases from within a database
 fuzzystrmatch │ 1.0     │ public │ determine similarities and distance between strings
 hstore        │ 1.0     │ public │ data type for storing sets of (key, value) pairs
 intarray      │ 1.0     │ public │ functions, operators, and index support for 1-D arrays of integers
 pgcrypto      │ 1.0     │ public │ cryptographic functions

# SELECT lanname FROM pg_language;
  lanname
───────────
 internal
 c
 sql
 plpythonu

CREATE PROCEDURAL LANGUAGE plpgsql; -- this won't work in 9.3, for example

After this, I have no plpgsql as an extension, but it is listed among the languages.  You can define functions in this language as usual.


Why the value changes when I assign it to a variable?

Maybe this quote from http://dev.mysql.com/doc/refman/5.6/en/bit-functions.html will help clarify:

Bit functions and operators comprise BIT_COUNT(), BIT_AND(), BIT_OR(), BIT_XOR(), &, |, ^, ~, <<, and >>.
Bit functions and operators require BIGINT (64-bit integer) arguments and return BIGINT values, so they have a maximum range of 64 bits. Arguments of other types (such as the BINARY and VARBINARY binary string types) are converted to BIGINT and truncation might occur.

(The 5.7 man page hints that some future version will extend BIT beyond 64 bits.)


Creating a case-insensitive and accent/diacritics insensitive search on a field

First, we need to clarify a few things.

Pattern Ops and Trigram

This type of query can work on varchar/text pattern_ops,

foo LIKE 'bar%'

Queries with Un-anchored search patterns reqire trigram.

foo LIKE '%bar%'

Both of them can work on function expressions, so long as you maintain the same sequence in the query that you have in the index.

Full text search is something completely different. It transforms your text into lexemes by stubbing to the root words, lowercasing, removing stop words, and a slew of other tricks. Keep reading:

Unaccent by itself (wouldn't suggest it)

Unfortunately though unaccent is only "stable" not "immutable." And so if you try to create an index on unaccent(text) you'll get an error, functions in index expression must be marked IMMUTABLE. I assume this is why you created the wrapper f_accent having found that on Erwins answer on Stack Overflow. Which looks like this,

CREATE EXTENSION unaccent;
CREATE OR REPLACE FUNCTION f_unaccent(text)
  RETURNS text AS
$func$
SELECT public.unaccent('public.unaccent', $1)  -- schema-qualify function and dictionary
$func$
LANGUAGE sql
IMMUTABLE;

CREATE TABLE foo AS
  SELECT 'asdf'::text AS mytext;
CREATE INDEX ON foo (lower(f_unaccent(mytext)) text_pattern_ops);

Or, for trigram,

CREATE INDEX ON foo
USING gin(lower(f_unaccent(mytext)) gin_trgm_ops);

Now remember you have to carry that long sequence lower(f_unaccent(mytext))  through to make it work..

SELECT *
FROM foo
WHERE lower(f_unaccent(mytext)) LIKE '%whatever%';

Unaccent with a FTS Dictionary

Instead of that method, I would create a dictionary that uses unaccent and use FTS. With custom dictionaries though it will likely do what you want. FTS converts the words to tokens which makes searching very simple and fast.

CREATE EXTENSION unaccent; -- still required it plugs into FTS.

CREATE TEXT SEARCH CONFIGURATION mydict ( COPY = simple );
ALTER TEXT SEARCH CONFIGURATION mydict
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, simple;

Now you should be able to

Removing any varchar_pattern_ops or trigram index
if desired, create a GIST or GIN index on the tsvector

CREATE INDEX ON foo USING to_tsvector('mydict', mytext);

You can see the tokens and what it's doing here,

select to_tsvector(
  'mydict',
  'Zoë and Renée are elected to the council of Cheese Eating Surrender Monkeys'
);
                                                             to_tsvector
-------------------------------------------------------------------------------------------------------------------------------------
 'and':2 'are':4 'cheese':10 'council':8 'eating':11 'elected':5 'monkeys':13 'of':9 'renee':3 'surrender':12 'the':7 'to':6 'zoe':1
(1 row)

And you can query the table simply like this,

SELECT *
FROM foo
WHERE to_tsvector('mydict', mytext) @@ 'cheese & council';

You don't have to use simple either. If the column contains statements in just one language you can use one of the other dictionaries to simplify things and remove stop words.


Would adding indexes to my foreign keys improve performance on this MySQL query?

Yes, performance may be better if you add those indexes. However, with such a small number of rows, it's quite possible that full table scan is more efficient and optimizer choses not to use any indexes.
After adding indexes your execution plan will be different, to get a rough estimation of how effective the indexes are you can multiply "Rows" column for each line of output of explain
In general, indexes on fields which participate in filtering/join conditions/order/group by improve performance. You also need to take into account selectivity (how many distinct values you have) of the column; if it's too low , the engine will not use it except if it's covering index for a query.
Foreign key is a constraint; the main purpose of any constraint to enforce some restriction (referential integrity in case of FK). Thus, if you care about integrity of you data, you should add foreign constraint.

The fact that Mysql implicitly creates an index on FK column means better read performance, and bit worse insert/update/delete performance (because index itself has to be updated).

Finally,

  My thinking is that since I'm selecting from locations first, ....

is not absolutely correct. Physical processing is not the same as logical; optimizer decides in which order it will process tables involved (as you can see in your output, the engine first accesses tickets table) and what access method to use. You can control it to some extent with hints though...

*Side note. The way your WHERE clause written :

WHERE `tickets`.`client_id` = '20'
  AND
  (
    `customers`.`name` LIKE '%Mahoney%'
    OR `customers`.`email` LIKE '%Mahoney%'
    OR `locations`.`address` LIKE '%Mahoney%'
  )

makes your LEFT JOIN customers behave as INNER JOIN.*

Update
Never mind my side note, I didn't pay attention you have ORs with multiple tables.

I hope it was helpful.


Why do I get incorrect characters when decoding a Base64 string to NVARCHAR in SQL Server?

The problem is that you encoded a UTF-8 encoded string into Base64. So, decoding the Base64 gives you back the original UTF-8 sequence of bytes. SQL Server uses UTF-16 Little Endian only for NVARCHAR data, and even for XML. Hence, Ã¹ is the 8-bit version of the two-byte UTF-8 sequence for ù (0xC3 and  0xB9).

Fortunately, it is possible to convert a UTF-8 encoded string into UTF-16, or even into a non-Unicode Code Page (IF the Code Page supports all characters being converted). The trick is to convert the Base64 decoded bytes, in their text representation (even with incorrectly converted characters) into XML. The trick to this trick is that you need to add the <?xml ...> declaration (typically omitted) and specify the source encoding:

DECLARE @Base64Value NVARCHAR(500) = N'T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=';

DECLARE @BinaryValue VARBINARY(500) =
    CONVERT(XML, @Base64Value).value('.','varbinary(max)');

DECLARE @IntermediaryValue VARCHAR(500) = CONVERT(VARCHAR(500), @BinaryValue);

SELECT @BinaryValue, @IntermediaryValue;
-- 0x4FC3B920657374206C6520636166C3A9206C6520706C75732070726F6368653F
-- Oֳ¹ est le cafֳ© le plus proche?

-- This is to NVARCHAR, which will always work:
SELECT CONVERT(NVARCHAR(500),
    CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>' +  @IntermediaryValue)
              );
-- Où est le café le plus proche?

-- This is to VARCHAR, but "success" will depend on the Code Page
-- specified by the default Collation of the current Database:
SELECT CONVERT(VARCHAR(500),
    CONVERT(XML, '<?xml version="1.0" encoding="UTF-8"?>' +  @IntermediaryValue)
              );

 -- In a DB with a Latin1_General Collation it works:
 -- Où est le café le plus proche?

 -- In a DB with a Hebrew Collation, it gets the following error:
 /*
   Msg 6355, Level 16, State 1, Line XXXXX
   Conversion of one or more characters from XML to target collation impossible
 */

Please note that this trick only works for converting from various source encodings into UTF-16 Little Endian (as that is how the XML datatype in SQL Server stores strings internally). This method cannot be used to convert UTF-16 into UTF-8 or some other non-SQL Server-supported encoding.

Below is an Inline-TVF encapsulating the steps shown above:

GO
CREATE FUNCTION dbo.ConvertBase64EncodedUTF8ToUTF16LE
(
  @Base64EncodedUTF8String VARCHAR(8000)
)
RETURNS TABLE
AS RETURN

    SELECT
        CONVERT(NVARCHAR(500),
                CONVERT(XML,
                        '<?xml version="1.0" encoding="UTF-8"?>' +
                        CONVERT(VARCHAR(500),
                                CONVERT(XML, @Base64EncodedUTF8String)
                                  .value('.','varbinary(max)')
                               )
                       )
               ) AS [DecodedValue];

GO

And then test:

SELECT *
FROM   dbo.ConvertBase64EncodedUTF8ToUTF16LE(
          'T8O5IGVzdCBsZSBjYWbDqSBsZSBwbHVzIHByb2NoZT8=');
-- Où est le café le plus proche?

Just use with CROSS APPLY if doing a set-based operation.


MySQL: How think about Null insert to foreign key column

If the relationship for child B against A isn't mandatory then just allow NULL on the foreign key column. It's very common to do this.

Don't use a default record (like ID = 0) to match against when there shouldn't really be one, it will bring you more trouble that solutions (what data will this record have? what happens with NOT NULL columns?)


Add missing rows to table via checking relations between other tables

INSERT INTO product_m_tender (tender_id, product_fix_id)
SELECT tender_id, pairs.child_product_id
FROM product_m_tender
JOIN (SELECT parent_product_id, child_product_id FROM products_m_products
      UNION
      SELECT child_product_id, parent_product_id FROM products_m_products) pairs
  ON product_m_tender.product_fix_id = pairs.parent_product_id
ON CONFLICT (tender_id, product_fix_id) DO NOTHING;

fiddle (nextvals absence fixed by a constant of zero).

Pay attention that

CONSTRAINT tender_id_product_fix_id UNIQUE (tender_id, product_fix_id)

is added to product_m_tender table.

If possible products chain can be longer than 2 records, than execute this query until zero records affected, or move pairs subquery to recursive CTE.


Developer's query performing very poorly in custom database

varchar(max) columns with delimited lists is an anti-pattern, particularly if you split those lists out.  Properly designed relational tables provide much better performance.  However, that is likely secondary to your performance problem.
I'd bet money on the problem being blocking.  From Microsoft:

Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. On SQL Server, blocking occurs when one SPID holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource.

Blocking is probably occurring when the Tableau queries take a long time to complete, blocking the updates from occurring, and creating the impression of slowness.
Brent Ozar has an excellent set of tools that can help understand performance problems.  Specifically around blocking, this page contains a load of great resources.
By the way, it looks to me like that update statement in your question looks perfectly well-formed to me.  I see nothing wrong with it as shown.  If you provide query plans for the queries and update statements that are problematic, I'll provide more definitive advice.
If you do find that blocking is a significant problem, you may want to look into SQL Server's multi-version-concurrency-control option that can be enabled at the database level, READ COMMITTED SNAPSHOT.  From that document:

When the READ_COMMITTED_SNAPSHOT database option is set ON, read committed isolation uses row versioning to provide statement-level read consistency. Read operations require only SCH-S table level locks and no page or row locks. That is, the SQL Server Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions. A user-defined function can return data that was committed after the time the statement containing the UDF began.

Be aware, read committed snapshot isolation (RCSI) is not the silver bullet it can appear to be at first glance.  Like most things, it has positive and negative effects.  By design, it changes the concurrency profile of the database, such that you can no longer rely on locking to enforce select-then-update semantics, since the select will no longer block other transactions from accessing the row that will soon be updated.  Kendra Little has a [great article(https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/) talking about RCSI.


Can database objects be made case insensitive while keeping strings case sensitive?

Mostly "yes", depending on how "automagic" you want everything to work.

First: Database identifier Collation (i.e. object names, column names, index names, etc) is taken from the Database's default Collation. That will determine the Collation of columns such as sys.objects.name, etc. So to do this, you can create or alter the Database to have a case-Insensitive Collation, such as Latin1_General_100_CI_AS_SC. This will allow you to have a table TEST that will be valid in queries such as select * from test;.

Server-scoped identifier Collation (e.g. Database names) is handled by the Instance's default Collation.

Second: If a Database's default Collation is case-Insensitive, then expressions that are entirely string literals and/or variables and/or output-parameters and/or UDF return values will be treated as case-Insensitive. For these expressions you would need to override that default behavior, per expression, using the COLLATE clause.

For example:

/* take database default collation behavior */
IF ('test' = 'TEST')
BEGIN
  PRINT 'Case-Insensitive';
END;
ELSE
BEGIN
  PRINT 'Case-Sensitive';
END;
-- ? result depends on the default collation of the "current" database ?

/* force case-sensitive */
IF ('test' = 'TEST' COLLATE Latin1_General_100_CS_AS_SC)
BEGIN
  PRINT 'Case-Insensitive';
END;
ELSE
BEGIN
  PRINT 'Case-Sensitive';
END;
-- Case-Sensitive

/* force case-insensitive */
IF ('test' = 'TEST' COLLATE Latin1_General_100_CI_AS_SC)
BEGIN
  PRINT 'Case-Insensitive';
END;
ELSE
BEGIN
  PRINT 'Case-Sensitive';
END;
-- Case-Insensitive

Expressions between string columns and either string literals or variables will use the Collation of the string column, and if the Collation for the column is case-sensitive, then the expression will be evaluated as case-sensitive without needing to use the COLLATE clause.

By default, newly created columns that do not specify the COLLATE clause will inherit the Database's default Collation. In this case, you will need to specify something like COLLATE Latin1_General_100_CS_AS_KS_WS_SC for all VARCHAR / NVARCHAR columns that you want to be sensitive across all 4 categories (plus support Supplementary Characters).

PLEASE NOTE: Be very cautious about using a binary Collation (i.e. Latin1_General_100_BIN2) to get an accent/case/kana/width sensitive Collation; binary Collations are not truly case-sensitive.


There's a (standard SQL) way of getting the guarantees you want, but I'm afraid mySQL 5.7 is not able to handle it. My example has been tested on PostgreSQL.

You can define your users table this way:

-- Schema for everything
CREATE SCHEMA teams ;

-- Users
CREATE TABLE teams.users
(
  user_id integer NOT NULL PRIMARY KEY,
  team_id integer,
  CONSTRAINT users_team_id_user_id_key UNIQUE (user_id, team_id)
);

Then, have your teams have be defined in the following fashion:

-- Teams have one team_lead
CREATE TABLE teams.teams
(
  team_id integer NOT NULL PRIMARY KEY,
  team_lead_id integer NOT NULL,

  -- This constraint ensures that 'team_lead_id' is a 'user_id', and
  -- this user is actually a member of the team
  CONSTRAINT teams_team_lead_id_fkey FOREIGN KEY (team_lead_id, team_id)
      REFERENCES teams.users (user_id, team_id) MATCH FULL
      ON UPDATE RESTRICT ON DELETE RESTRICT
      DEFERRABLE INITIALLY DEFERRED
);

By using a composite foreign key constraint you actually make sure that the user you are checking for, is actually a member of the team.

After this second table has been defined, you can now add the foreign key constraint users.team_id -> teams.team_id:

-- The 'team_id' on users is a
ALTER TABLE teams.users
  ADD CONSTRAINT users_team_id_fkey FOREIGN KEY (team_id)
      REFERENCES teams.teams (team_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
      DEFERRABLE INITIALLY DEFERRED ;

Note that both tables are referrrig to each other. You have circular references.

Being this the case: the constraints must be checked at the end of a transaction, not just at the end of a statement. In the case of PostgreSQL, they need to be defined DEFERRABLE and INITIALLY DEFERRED (this is what mySQL cannot handle). You need to enter data for the first team (table teams) and its team leader (table users) all in one transaction, or, otherwise, there's no way to satisfy both FOREIGN KEY constraints:

Let's assume these are 'user 1' and 'team 5':

BEGIN TRANSACTION ;
INSERT INTO teams.users (user_id, team_id)      VALUES (1, 5) ;
INSERT INTO teams.teams (team_id, team_lead_id) VALUES (5, 1) ;
COMMIT TRANSACTION ;

Unfortunately, mySQL 5.7 documentation about foreign key constraints states:

  [...] InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things are impossible, such as deleting a record that refers to itself using a foreign key.

MariaDB documentation about FKs doesn't have mentions about deferrability.


Is it safe to use an output (to_char) date format string of more than 22 characters (ORACLE 11)

Just because you can jump off a cliff, doesn't mean you should.

Use REPLACE() with a token:

select REPLACE('SOMESTRING_##DATE##_SOME LOTS MORE STRINGS OF A FILENAME PREFIX',
               '##DATE##',
               TO_CHAR(sysdate,'YYYYMMDD'))
from DUAL;

You could take this a step further to allow different format strings:

with mytable as (
select 'SOMESTRING_##YYYYMMDD##_SOME LOTS MORE STRINGS OF A FILENAME PREFIX' as format_string
from DUAL
)
select REGEXP_REPLACE(format_string,'##(.*)##',TO_CHAR(sysdate,replace(
                    regexp_substr( format_string, '##(.*)##', 1, 1),'##','')))
  from mytable;

Note that there have been parsing bugs with long TO_CHAR identifiers that have caused ORA-600 errors & session crashes.


replace

->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',
IFNULL( `downloads`.`is_download` , 0 ) )

with

->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',
DB:Raw(' IFNULL( `downloads`.`is_download` , 0 )') )

and add an alias

->select('subjects.subjectID', 'subjects.subjectName', 'subjects.price',
DB:Raw(' IFNULL( `downloads`.`is_download` , 0 ) as is_download') )


Data types
The data type character(n) is almost always the wrong choice. It's a "legacy" type I wouldn't use any more. It exhibits surprising behavior and does nothing text / varchar / varchar(n) couldn't do better.

Any downsides of using data type “text” for storing strings?

And you are mixing type date in the table with timestamp literals in the query. While this works, you should at least provide explicit type declaration to defend against surprising results. Better yet, provide actual dates in the query or cast input explicitly. Like
report_date >= timestamp '2017-02-01T17:29:49.661Z'

Or:
report_date >= date '2017-02-02'

The manual about constants and type casts.
Index
Your index indx_own_inst_detail_id_report_date_desc on (id, report_date DESC NULLS LAST) looks good for the query, and the query plan you provided makes good use of it. Execution time: 7.801 ms does not seem too bad either.
If your "uncached" query takes 4 seconds, then you may have to work on your hardware or server configuration or both. Slow storage and not enough RAM for cache? More work_mem is not the cure for this, may even make it worse by taking away RAM from cache memory. Related:

Increasing work_mem and shared_buffers on Postgres 9.2 significantly slows down queries

If you have enough RAM and proper memory settings this might burn down to an issue of cold cache: Only the first invocation is slow (or the first few invocations). If that's a problem, consider pg_prewarm. See:

PostgreSQL: Force data into memory

If your table is vacuumed enough (or mostly read-only) you may profit from index-only scans if you append the one additional column entity_id in your SELECT list to the index:
CREATE INDEX ON own_inst_detail (id, report_date DESC NULLS LAST, entity_id)

That might be particularly useful in your situation since Postgres only accesses the index in this case and does not need to visit the table at all. (Eliminating the Bitmap Heap Scan completely.) May help with your disk / cold cache bottleneck.
Related:

Can Postgres use an index-only scan for this query with joined tables?