Error ORA-00918 not raised as expected when using the FETCH command

Do not believe everything you read in Oracle documentation.

Try the below:

CREATE TABLE a (test NUMBER not null, test1 VARCHAR(6) not null, id VARCHAR(5) not null);
CREATE TABLE b ( id VARCHAR(5) not null, lang VARCHAR(5) not null, test1 VARCHAR(6) not null, test2 VARCHAR(5) not null );

insert into a values(1, 'AA',10);
insert into a values(2, 'BB',20);
insert into a values(3, 'CC',30);
commit;

insert into b values(10, 'eng','AA','test2');
insert into b values(20, 'tamil','BB','test2');
commit;

Then:

1) SELECT aa.test, aa.test1, aa.id, bb.test1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ;
2) SELECT aa.test, aa.test1, aa.id, bb.test1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;
3) SELECT aa.test, aa.test1, aa.id, bb.test1 as t1, bb.test2 FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;
4) SELECT * FROM a aa INNER JOIN b bb ON aa.id = bb.id AND bb.lang = 'eng' ORDER BY 1 FETCH FIRST 3 ROWS ONLY;

1), 3) and 4) runs successfully on 12.1.0.2, 2) throws ORA-00918.

On 12.2.0.1, all 4 runs successfully.

The one thing to keep in mind: never rely on implicit behaviour you can not control. Use explicit, unique aliases.


Move DTC from one node to another in Windows 2003 R2 Clustering

You can only move groups, not individual services - if the msdtc has other resources in the same group, then they will need to move as well. To get around this, create a new clister group and include just the msdtc, then you're free to move it without impacting any other cluster resources or services.


Add Decimal Point after First Digit in a Numeric Value

Because all the values inside the convert call are integers it is performing integer division to get an integer result before converting to a decimal. ti.Rate/1000 is 8250/1000 which with integer division becomes 8. CONVERT then changes the type but the extra precision is already lost.

If you add a decimal place to the numeric literal 1000, SQL Server will see it as a decimal/numeric type number instead of an integer, so all you need to do is CONVERT(DECIMAL(10,3),ti.Rate/1000.0) AS Amount. Dividing an integer by a decimal/numeric (or vice-versa) results in a decimal/numeric.

In fact you can probably just do ti.Rate/1000.0 AS Amount unless you specifically need the type to be exactly DECIMAL(10,3) instead of the default (a quick test on SQL2016 shows the default to be NUMERIC(17,6), note that numeric and decimal are functionally identical as one is a synonym for the other). Or if you do specifically need DECIMAL(10,3), you might avoid extra implicit conversions by converting one of the inputs instead of the output: CONVERT(DECIMAL(10,3),ti.Rate)/1000.0 AS Amount (though the performance difference here is likely to be at best infinitesimal).


restore postgres BBDD - message "already exists"

use
-c
--clean

Output commands to clean (drop) database objects prior to outputting the commands for creating them. (Unless --if-exists is also specified, restore might generate some harmless error messages, if any objects were not present in the destination database.)

This option is only meaningful for the plain-text format. For the archive formats, you can specify the option when you call pg_restore.

see manual
so that all elemenst are dropped before you restore the database.
in your case use pg_restore with the same parameter, that has the same effect


Optimize query or model, to get an population overview in time


According to PostgreSQL documentation about NOTIFY:

  The NOTIFY command sends a notification event together with an optional "payload" string to each client application that has previously executed LISTEN channel for the specified channel name in the current database. Notifications are visible to all users.

(emphasis mine)

This means you cannot do what you want just with LISTEN/NOTIFY. However, you can have both a table to store queued messages, LISTEN/NOTIFY to notify external applications that "there are new things in the message queue", and use some extra logic from these external applications so that only one consumes the message.

The strategy depicted in the article What is SKIP LOCKED for in PostgreSQL 9.5? is probably the safest/easiest way to implement a message queue within PostgreSQL. Pay special attention to the "How SKIP LOCKED helps" part. Read also carefully one of their caveats:

  A queue implemented in the RDBMS will never match the performance of a fast dedicated queueing system, even one that makes the same atomicity and durability guarantees as PostgreSQL. Using SKIP LOCKED is better than existing in-database approaches, but you’ll still go faster using a dedicated and highly optimised external queueing engine.

This is specially important if the queue volume is high.


psql asks for password, CREATE USER didn't specify one

A user having a password or not won't affect if you're asked for a password when trying to connect.  That is controlled by the pg_hba.conf file.

If you're being asked for a password that indicates that a password is required.  If you try to connect to an account which doesn't have a password set that will simply fail.  You need to either set a password for the account and use that, use a different type of connection or modify the pg_hba.conf file to use a different authentication method.


Confused about Primary Keys, and Foreign Keys

Teaching yourself database design is indeed very tricky.  You might benefit from finding a good tutorial that would help you master the basics.  If you want suggestions concerning a good learning vehicle, ask.

Here is a very oversimplified summary of some of the basics.

Before you can design a database, you have to analyze the subject matter in some detail.  To quote GregH, "completely understanding how the business works".  One way to do this is to get some reliable source on Museums and how they work in the real world, or whatever approximation to the real world you are willing to settle for.  You may rely on your own memory for this, but it's better to get some input from experts on Museums, your subject matter.  ER modeling can help summarize what you learn.

Another thing you need to spend a little time on is how you intend to use the data.  Your design can be influenced by your intended use.  This can turn out to be a moving target.  You may decide to use the data in more than one way, somewhere in the future.  Think ahead as best you can.

Then, you are ready to design your tables.  Normalisation is one fairly mechanical approach to avoiding certain pitfalls that can creep up on you if you just design at random.  You table design should reflect the relational model of data.  The relational model is slightly different from the ER model, in that foreign keys are used to link tables together.  But the question of which table non key data should go in is nearly impossible to get right unless you have a clear understanding of the subject matter.

good luck.


SQL Server Concurrent Inserts and Deletes

The UPDLOCK, SERIALIZABLE pattern is all about avoiding incorrect results (including false key violation errors) due to race conditions, when performing a particularly common operation known as an UPSERT - update an existing row if it exists; insert a new row otherwise.

  ...while avoiding racing conditions, deadlocks, etc?

You seem to be looking for a magic combination of hints that will allow a database system to perform highly concurrent operations without conflict. In general, there is no such thing. The only way to avoid deadlocks completely is to always modify objects in the same order.

This can be difficult, even impossible, to achieve in a foreign key relationship. Consider that when inserting a new object, you must add the parent row before the child row(s). When deleting an object, you must remove the child(ren) before the parent.

This is not to say that careful locking will not still prevent race conditions, but it cannot guarantee to avoid deadlocks in all situations.

As far as the question goes, yes, using the UPDLOCK, SERIALIZABLE hint will protect from UPSERT race conditions, but no, it will not prevent deadlocks. It may even contribute to increasing their frequency. This is tricky to assess in advance, and even experienced SQL Server database designers can get it wrong.

For more complex requirements involving multiple objects, a common solution is to always explicitly exclusively lock the parent row before modifying the children, even where the natural order is to process the child first.

For example, when deleting an Order:

DECLARE @OrderID bigint = 12345;

BEGIN TRANSACTION;

    -- EXTRA STEP
    -- Dummy update with XLOCK to exclusively lock the parent row
    UPDATE TOP (1) dbo.Orders WITH (XLOCK)
    SET Lastfiledatetime = NULL
    WHERE OrderId = @OrderID;

    -- Remove children
    DELETE dbo.OrderLineDetail
    WHERE OrderId = @OrderID;

    -- Remove parent
    DELETE dbo.Orders
    WHERE OrderId = @OrderID;

COMMIT TRANSACTION;

The extra step of exclusively locking the parent row will help eliminate deadlocks, if all modifications follow the same modification order: parent first, then child(ren). If any process accesses objects in the reverse order, the possibility of a deadlock due to incompatible locks occurs.

Note that can be important to actually modify something in the parent row, since SQL Server may not honour the exclusive lock (XLOCK) in some situations, if it can tell that it is not necessary for the operation being performed.

A second implementation of roughly the same idea is to use application locks, as mentioned in the Q & A Implementing application locks within SQL Server (Distributed Locking Pattern) (see the documentation links there).

This is easier in some respects, but again, you do have to ensure that all code that modifies the protected objects uses the same scheme. As soon as anything can access the underlying object(s) without taking the required application lock(s), the whole scheme breaks down.

An example below shows how we might take an exclusive application lock on a particular order number before processing elements of the order:

BEGIN TRANSACTION;

    -- The order number we want exclusive access to
    DECLARE @OrderID integer = 12345;

    -- Compute the locking resource string
    DECLARE @Resource nvarchar(255) = N'dbo.Order' + CONVERT(nvarchar(11), @OrderID);

    -- Return code from sys.sp_getapplock
    DECLARE @RC integer;

    -- Build dynamic SQL
    DECLARE @SQL nvarchar(max) =
        N'
        EXECUTE @RC = sys.sp_getapplock
            @Resource = ' + QUOTENAME(@Resource) + N',
            @LockMode = Exclusive,
            @LockTimeout = -1;'

    -- Try to acquire the lock
    EXECUTE sys.sp_executesql
        @SQL,
        N'@RC integer OUTPUT',
        @RC = @RC OUTPUT;

    -- Do something with the return code value if necessary
    SELECT @RC;

    --- Sensitive operations go here

    -- Release the application lock early if you can
    -- using sys.sp_releaseapplock

ROLLBACK TRANSACTION;

Concurrency is hard; sorry about that.


Single select from template record oracle

Try this

Select *
from your_table
where height = '6,2'
and weight = 190;

or

Select *
from your_table
where height = (select height from your_table where person = x)
and weight = (select weight from your table where person = x);


Select all data group by 1 column and functioned by another

;with cte as
(
    select
        ID,
        Priority,
        Type,
        Name,
        row_num =
            row_number() over (partition by Type order by Priority desc)
    from dbo.your_table
)
select ID, Priority, Type, Name
from cte
where row_num = 1;


Efficient way to fetch records since a given criteria

To add a bit to what @SQLFox said if ID happens to be the clustered index then it should be blazing fast.  Even if it isn't the clustered index, if it is even included in an index then you are libel to at least get an index scan and go fairly quickly.

If I had to guess I would say it's your ORDER BY that is slowing you down.  With the ORDER BY in place it's going to have to check every row and check to see if it should be part of that top 100.  Without it SQL is just going to check enough rows until it gets 100 of them that match the criteria.


Given the wait types of LATCH_EX and ACCESS_METHODS_DATASET_PARENT I highly doubt AlwaysOn was the culprit.

These two latch types may go hand in hand. You'll want to investigate where the latch_ex waits are actually happening - sys.dm_os_latch_stats may help here where the latch_class is not 'buffer'.

Given that removing AlwaysOn did not help with the situation, I highly doubt AlwaysOn was the problem. However, AlwaysOn may have contributed to some wait time though how much is impossible to say, though I doubt it would be noticeable against your current top waits.

Paul has some information on these:
https://www.sqlskills.com/help/latches/access_methods_dataset_parent/
https://www.sqlskills.com/help/waits/latch_ex/

Additionally this whitepaper on latch contention may be helpful:
http://download.microsoft.com/download/B/9/E/B9EDF2CD-1DBF-4954-B81E-82522880A2DC/SQLServerLatchContention.pdf


Select the students that are enrolled in both the courses

You can approach this problem using conditional aggregation based filtering. Firstly, get all the students who are either enrolled IN(..) course no (1, 3).

Then, you can use HAVING clause to filter out the students having atleast two unique courses subscribed to (which would be 1 and 3 in this case):

SELECT student_name
FROM your_table_name
WHERE course_number IN (1,3)
GROUP BY student_name
HAVING COUNT(DISTINCT course_number) = 2

For good performance, you can define the following Covering Index: (course_number, student_name)


Mysql temp table insert from json can't handle null values

The solution is to ommit the property which you want nulled.

After searching for a while I found this bug report from over a year ago that explains how a json string that contains the value NULL is actually a value and not a null.

{"item":1,"quantity":4} --> item = 1, quantity = 4

{"item":1,"quantity":null} --> item = 1, quantity = 'null' ( Some kind of unfriendly json null )

{"item":1} --> item = 1, quantity = NULL ( Your good old sql null ! )

After seeing this, I thought "there's gotta be a way to cast this unfriendly json null into a nice null". I tried many things which worked, but none of them were insertable into a temporary table.

This was my final attempt which should work, based on what I stated above. I manage to detect if the value is null using an in-line IF statement, then I try to cast an actual SQL NULL into a SIGNED int. I demonstrate how you can select the values but that it's impossible to insert them for some reason.

The code itself :

DROP TABLE IF EXISTS json_temp_table;
CREATE TEMPORARY TABLE json_temp_table (
  item_id int NOT NULL PRIMARY KEY,
  model_number varchar(100),
  quantity int NULL
)
ENGINE = INNODB;

INSERT INTO json_temp_table
SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
SELECT
  json_tb.item_id,
  json_tb.model_number,
  json_tb.quantity,
  if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
FROM JSON_TABLE
(
'[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
, "$[*]"
COLUMNS
(
item_id int PATH "$.item_id",
model_number varchar(100) PATH "$.model_number",
quantity int PATH "$.quantity"
)
) json_tb) x;

Executed on the CLI mysql client

mysql> DROP TABLE IF EXISTS json_temp_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TEMPORARY TABLE json_temp_table (
    ->   item_id int NOT NULL PRIMARY KEY,
    ->   model_number varchar(100),
    ->   quantity int NULL
    -> )
    -> ENGINE = INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
    -> SELECT
    ->   json_tb.item_id,
    ->   json_tb.model_number,
    ->   json_tb.quantity,
    ->   if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
    -> FROM JSON_TABLE
    -> (
    -> '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
    -> , "$[*]"
    -> COLUMNS
    -> (
    -> item_id int PATH "$.item_id",
    -> model_number varchar(100) PATH "$.model_number",
    -> quantity int PATH "$.quantity"
    -> )
    -> ) json_tb) x;
+---------+--------------+----------+
| item_id | model_number | quantity |
+---------+--------------+----------+
|       1 | MFJA53       |        4 |
|       2 | HSRHJN5      |     NULL |
|       3 | FAFAF1       |      345 |
+---------+--------------+----------+
3 rows in set, 1 warning (0.00 sec)

mysql> INSERT INTO json_temp_table
    -> SELECT X.item_id,X.model_number,CAST(IF(x.quantity_is_null=1,NULL, X.quantity) AS SIGNED) 'quantity' FROM (
    -> SELECT
    ->   json_tb.item_id,
    ->   json_tb.model_number,
    ->   json_tb.quantity,
    ->   if(json_type(json_extract(JSON_OBJECT('b',json_tb.quantity),'$.b')) = 'NULL',1 ,0) 'quantity_is_null'
    -> FROM JSON_TABLE
    -> (
    -> '[{"item_id":1,"model_number":"MFJA53","quantity":4},{"item_id":2,"model_number":"HSRHJN5","quantity":null},{"item_id":3,"model_number":"FAFAF1","quantity":345}]'
    -> , "$[*]"
    -> COLUMNS
    -> (
    -> item_id int PATH "$.item_id",
    -> model_number varchar(100) PATH "$.model_number",
    -> quantity int PATH "$.quantity"
    -> )
    -> ) json_tb) x;
ERROR 3156 (22018): Invalid JSON value for CAST to INTEGER from column quantity at row 1
mysql>


How to split a SUM column based on another BIT column?

SELECT sprId,
       COALECSE(SUM(calc*(1-load)), 0),
       COALECSE(SUM(calc*load), 0)
FROM source_table
GROUP BY sprId


Not all members in a Windows group have permissions

Some ideas:

The user has not logged off and back in after being added to the group. The group membership info for a user is stored in that user's login token so it may not show until they reauthenticate to the DC
The user name is different to what you expect. Eg a Woman who got married and the login name changed to match the new surname. I've seen xp_logininfo fail because of this
The user has multiple permission paths and is denied via one of them
What do you mean by "not getting the permissions" is that "no rights to select" or "error when connecting" etc. To test, have the user log in using the ODBC control panel to test basic connectivity before worrying about permission

Edit, more ideas:

The permission path for xp_logininfo only shows how they connected to the SQL Server instance. As per comment, the user in question can connect to the Instance but doesn't have permissions in the database. So, the other members of that group get database permissions from somewhere else.

I wouldn't get hung up on the output of xp_logininfo. I'd check permissions and mapping end to end and not bother with xp_logininfo personally

  If the user is a member of multiple Windows groups of equal privilege level, only the group that was first granted access to SQL Server is reported.


So in Configuration Manager, you configured the SQL Agent service account, correct?  That is the account that will execute SQL Agent jobs, by default.

This query will verify your settings:

SELECT servicename, service_account
FROM   sys.dm_server_services
go

Do you get the error when executing the job via a schedule, when executing manually, or both?

If you are using T-SQL commands to perform the backup, what happens when you paste the commands into a query window and manually execute?  Can you reproduce the error there as well?


Is there an ideal fstab rule for XFS volumes on EC2 for running MongoDB?

No, as far as I know. These "default" settings are just enough. However, I would not add nofail (do not report errors for this device if it does not exist), I want to see an error if MongoDB data directory don't mount.

..In the scenario where the node is part of replica set and it is restarted and the mongod process is starting automatically. Mongod cannot find any data files, so it goes to RECOVERY -state and starts initial sync, what will fill nodes root partition at a fast pace.

But, of course in the situation, where you don't have access to virtual machines console, you must put that nofail, so you don't end up in a situation where you cannot access your machine. You go around the problem by creating a subdirectory (f.ex. mongo) under that /data (/data/mongo) and pointing dbPath to that directory. No mount, no directory --> mongod don't start.


Added .Net assembly to SQL CLR without turning TRUSTWORTHY ON

I executed the same command on SQL Server 2017 CU12 and got the same 15208 error. I then tried the following:
CREATE ASYMMETRIC KEY [MS.NETsnk] FROM EXECUTABLE FILE =
'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll';

and that succeeded! ("snk" in the name is for "Strong Name Key", since technically this isn't a certificate).
From what I can tell, most of the .NET Framework libraries for CLR 2.0 (i.e. .NET Framework versions 2.0, 3.0, and 3.5) are not signed with a certificate. Most, but not all, of the mscor*.dll files, and only one System.*.dll — System.EnterpriseServices.Thunk.dll — are signed with a certificate. The rest are only using a Strong Name Key / are strongly-named.
Starting with CLR 4.0 (i.e. .NET Framework versions 4.*), it appears that all .NET Framework libraries are signed with a certificate.
Hence, the more precise rule is:

For CLR 4.0 (i.e. SQL Server versions 2012 and newer), use CREATE CERTIFICATE

For CLR 2.0 (i.e. SQL Server versions 2005, 2008, and 2008 R2), use CREATE ASYMMETRIC KEY. But, if doing that results in a 15208 error ("The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it"), then switch to CREATE CERTIFICATE.
The reason for this group to start with ASYMMETRIC KEY is the very low probability of ever needing to use CERTIFICATE. I tried to import System.EnterpriseServices.Thunk.dll in both SQL Server 2019 (which used CLR 4.0) and SQL Server 2005 (which used CLR 2.0) and it failed in both — msg 6507 in SQL Server 2005, and msg 6544 in SQL Server 2019 — complaining of a "malformed assembly" (meaning it's a mixed-mode / not-pure-MSIL assembly). I also tried importing some of the mscor* libraries in SQL Server 2005 and they failed with the same 6507 error (not surprising that the core libraries would have unmanaged code in them). So, it's quite likely that there's not even a possibility of using CREATE CERTIFICATE when importing .NET Framework libraries for CLR 2.0.

Now, it appears that System.Messaging has several dependencies:

system.configuration.install
system.runtime.serialization.formatters.soap
system.windows.forms
system.drawing
accessibility
system.directoryservices

Those dependencies get imported automatically because the source for CREATE ASSEMBLY is a file and not a VARBINARY literal / hex bytes. While doing this automatic import of dependencies, the operation fails on system.windows.forms claiming that it is not authorized for UNSAFE, even though we have already created the Asymmetric Key and the other dependencies have imported correctly.
Further investigation reveals that the Strong Name Key used for system.windows.forms is different than what was used for the other libraries. The libraries that import successfully have a Public Key Token of b03f5f7f11d50a3a while the one that fails has a Public Key Token of b77a5c561934e089. As the O.P. discovered, creating an Asymmetric Key from system.windows.forms does not work. Creating a certificate from the library also doesn't work, but that's easier to explain: the library isn't signed with a certificate ;-). The issue with creating the Asymmetric Key, however, is probably due to how system.windows.forms is signed. It uses ECMA signing, which places a standard, place-holder value in the library instead of the public key, and this place-holder value is not a valid public key. For more info, please see the following:

Assemblies and the GAC (Mono project ; there are 2 definitions related to ECMA)
Quite special PublicKey in .NET core assemblies (Stack Overflow answer)

Now, I don't think we can get the public key file to create an Asymmetric Key from. It might be somewhere in the file system as it shouldn't be a security issue, being a "public" key and all, but as of right now I don't know where it is.
So, for the moment we should still be able to get around this using the same basic technique that allows pre-existing, unsigned assemblies in databases upgraded to SQL Server 2017 (or newer) from a pre-2017 version, to work: sign the assembly in-place. Of course, to get the assembly into SQL Server we will need to temporarily enable TRUSTWORTHY, but only for a single command. Then, once the assemblies have been imported, we can:

create a certificate
sign the assembly with it
back up the certificate (both public and private keys) to files (can't get the VARBINARY of the public key via CERTENCODED as that was introduced in SQL Server 2012)
remove the private key from the certificate so that it can't be used to sign anything else
create the same certificate in [master], but only with the public key (so it can't be used for signing)
create a login from that certificate
grant that signature-based login the UNSAFE ASSEMBLY permission

Try the following approach based on the steps outlined above. The example code below assumes that the steps to create the Asymmetric Key, associated signature-based Login, and GRANT on System.Messaging have already been completed.
--CREATE DATABASE [Test];
--ALTER DATABASE [Test] SET RECOVERY SIMPLE;
GO

USE [Test];
ALTER DATABASE [Test] SET TRUSTWORTHY ON;

CREATE ASSEMBLY [System.Messaging]
FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE;

ALTER DATABASE [Test] SET TRUSTWORTHY OFF;

SELECT * FROM sys.assemblies;
-- System.Messaging: publickeytoken=b03f5f7f11d50a3a
-- System.Windows.Forms: publickeytoken=b77a5c561934e089

CREATE CERTIFICATE [WinForms]
  ENCRYPTION BY PASSWORD = 'Use Another Password!'
  WITH SUBJECT = 'For ECMA-signed .NET Framework libraries';

ADD SIGNATURE
  TO ASSEMBLY::[System.Windows.Forms]
  BY CERTIFICATE [WinForms] WITH PASSWORD = 'Use Another Password!';

BACKUP CERTIFICATE [WinForms]
  TO FILE = 'C:\TEMP\WinFormsCert.crt'
  WITH PRIVATE KEY
  (
    FILE = 'C:\TEMP\WinFormsCert.pvk',
    ENCRYPTION BY PASSWORD = 'Backup Password!',
    DECRYPTION BY PASSWORD = 'Use Another Password!'
  );

ALTER CERTIFICATE [WinForms] REMOVE PRIVATE KEY;
GO

USE [master];
CREATE CERTIFICATE [WinForms] FROM FILE = 'C:\TEMP\WinFormsCert.crt';
CREATE LOGIN [WinForms] FROM CERTIFICATE [WinForms];
GRANT UNSAFE ASSEMBLY TO [WinForms];

This work-around shouldn't be necessary when working with CLR 4.0 (i.e. SQL Server 2012 and newer) since all of those assemblies are signed with a certificate.
For more info on working with SQLCLR in general, please visit: SQLCLR Info


PostgreSQL: count datasets / lines containing NULL values

You will need to use dynamic SQL to acheive this, perhaps like this:

testbed:

create role stack;
create schema authorization stack;
set role stack;

create table my_table as
select generate_series(0,9) as id, null::integer as val;

create table my_table2 as
select g as id, case when g<=5 then null::integer else 1 end as val
from generate_series(0,9) g;

function:

create function get_with_nulls_rowcount(p_schema in text, p_table in text)
                returns integer language plpgsql as $$
declare
  n integer;
  s text;
begin
  --
  select array_to_string(array_agg(column_name::text||' is null'), ' or ')
  into s
  from information_schema.columns
  where table_schema=p_schema and table_name=p_table;
  --
  execute 'select count(*) from '||p_table||' where '||s into n;
  return n;
end;$$;

query:

select table_schema, table_name,
       get_with_nulls_rowcount(table_schema, table_name)
from information_schema.tables
where table_schema='stack';

result:

 table_schema | table_name | get_with_nulls_rowcount
--------------+------------+-------------------------
 stack        | my_table   |                      10
 stack        | my_table2  |                       6
(2 rows)


Normalising StudentMaster.Course will help preserve duplicates. Since you've tagged sql-server-2012 sadly string_split() is unavailable. Happily, the internet is rife with pre-2016 workarounds. I'll choose this one arbitrarily since it was the top google hit for me.

Given creation of dbo.fn_split_string_to_column() from the linked SO post, the following should get you what you seek.

with normal_course as (
    select SM.ROLLNO,
           ss.column_id,
           ss.value as course_id
    from dbo.StudentMaster as SM
    cross apply dbo.fn_split_string_to_column(SM.Course,',') as ss
)
select SM.ROLLNO,
       SM.NAME,
       SM.ADDRESS,
       (
       select ','+CM.CourseName
       from normal_course as nc
       join dbo.CourseMaster as CM on CM.CourseId = nc.course_id
       where nc.ROLLNO = SM.ROLLNO
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as Course
from dbo.StudentMaster as SM;

Try it on dbfiddle

Of course, my preference would also be to move the subselect down to a dedicated apply, but that's not strictly required.

Explanation

In your original query, the WHERE clause in your sub-select is fuzzy-matching the CourseId to the comma-aggregated string Course. This "mostly works" as you can see; but one of the (many) downsides of this approach is what you have seen - that each CourseId matches only once and in-line "duplicates" will not be aggregated multiple times.

Splitting out StudentMaster.Course into normalised values occurs in the CTE (with block) at the top of the query. This preserves the duplicates.

    select SM.ROLLNO,
           ss.column_id,
           ss.value as course_id
    from dbo.StudentMaster as SM
    cross apply dbo.fn_split_string_to_column(SM.Course,',') as ss

For the example duplicate row, this shows us...

+--------+-----------+-----------+
| ROLLNO | column_id | course_id |
+--------+-----------+-----------+
| 12345  | 1         | 01        |
| 12345  | 2         | 02        |
| 12345  | 3         | 02        |
+--------+-----------+-----------+

Now that the duplicate course_id is exposed as a separate data record, when you re-aggregate the corresponding CourseNames, the duplicates will be preserved unless you take steps to explicitly remove them.


Suggestion for Large SQL Server Database Design

Here is my opinion:

If you are having very few updates/deletes you can increase the pagefill factor to 95%. This will save on space and reads. Do some testing though.
Partition the table based on a broad category like year.
Put these partitions on different filegroups.


Why does postgres use more space for a time with timezone than a timestamp with timezone?

time with time zone stores microseconds (8 bytes) and the time zone (4 bytes).  timestamp with time zone stores just the microseconds and converts the time zone at display time.  Because of the conceptual weirdness of the time with time zone type, the time zone needs to be stored explicitly.  You don't actually need 8 bytes to store the number of microseconds in a day, but 4 bytes wouldn't be enough.  If you really wanted to, you could probably devise a more compact storage format for time with time zone, but in practice nobody cares.


How to get the data from same table by different conditions in SQL?

If I understand your question correctly, you want all rows with HasAssigned='True'

AND

all rows where HasAssigned='False' where there does not exist a row for that same type that is also HasAssigned='True'

If that is what you want, I think this query will give you that answer:

DECLARE @ColorAssignTable TABLE (
    PKID INT
    ,NAME VARCHAR(30)
    ,Type INT
    ,HasAssigned VARCHAR(10)
    )
insert into @ColorAssignTable(PKID,Name,Type,HasAssigned) values
(11,'Red',1,'True'),
(12,'Blue',2,'False'),
(13,'Yellow',3,'False'),
(14,'Red1',1,'False'),
(15,'Yellow1',3,'True'),
(16,'Blue1',2,'False')

SELECT *
FROM @ColorAssignTable
WHERE HasAssigned = 'True'

UNION ALL

SELECT *
FROM @ColorAssignTable a
WHERE HasAssigned = 'False'
    AND NOT EXISTS (
        SELECT *
        FROM @ColorAssignTable
        WHERE type = a.type
            AND HasAssigned = 'True'
        )
Order by PKID


Can anyone explain to me the concept "incarnation" in Oracle database in an easy-to-understand way?

Following is a short graphic which I will be using to explain what incarnation is used for in the answer:

                                    restore db    +-----+           +-----+     +-----+
                                    recover db    | 2>3 | --------> |  3  | --> |  3  | -->
                                    resetlogs     +-----+           +-----+     +-----+
                                                  ^       Incarn.      3           3
                                                 /        SCN #       400         500
                                                /
                                               /
             restore db    +-----+          +-----+     +-----+
             recover db    | 1>2 | -------> |  2  | --> |  2  | -->  ...
             resetlogs     +-----+          +-----+     +-----+  ^
                           ^       Incarn.     2           2     |    2
                          /        SCN #      300         400    |   500
                         /                                       |
                        /                                        |
        +-----+     +-----+     +-----+                          |
    --> |  1  | --> |  1  | --> |  1  | -->   ...                |
        +-----+     +-----+     +-----+  ^                       |
Incarn.    1           1           1     |     1                 |
SCN #     100         200         300    |    400                |
                                         |                       |
Backup   11:00 ----- 12:00 ----- 13:00 ----- 14:00 ----- 15:00 ----- 16:00 ----- 17:00
                                         |                       |
Restore/                                (1)                     (2)
Recovery

Basics

When an Oracle database is created it receives an initial incarnation number. The incarnation number is stored in the database itself, the control file(s) of the database instance and in the central RMAN catalog database (if you have one).

New Incarnations

Let's take a look at the above graphic. It is a slight modification of the graphic which Oracle uses to explain what incarnations are.
Reference: RMAN Media Recovery (Oracle Docs)

Archive Log Backups

Now let's assume the Archive Log backups are occuring on an hourly basis. Oh, and the datbase is in the ARCHIVE_LOGS mode, which allows us to backup and restore archive logs which enables us to recover the database to a certain point in time. We'll also assume that the database has received a FULL backup at 04:00 (a.m.) the same day. Further, the database has nevery been restored since it was initiailly created and the current INCARNATION# of the database is at 1.

SCN

We'll further assume that 100 transcations occur each hour so that at the time of the backup the SCN# will be a multiple of 100.

Time

The time is only here to increase the readability of the graphic. It plays a role in understanding why Oracle creates a new incarnation for a database that has been restored to a certain point-in-time.

Restoring the Database to Point-in-Time (1)

Somewhere slightly after 13:00 (1pm)  somebody decides that the database has to be restored to 12:00 (12 o'clock midday). The DBA either sets of a bunch of RMAN commands to restore the database to that point-in-time our clicks his way through a fantastic GUI to initiate a restore/recovery from a 3rd-party vendor.

RMAN retrieves the FULL backup of the database and all Archive Log backups from  disk/tape and restores them to the disk. In the recovery phase RMAN will check that all relevant information is available and roll-forward all finished transactions to the Point-in-Time and roll-back all unfinished transactions to the Point-in-Time, to ensure the database is in a consistent state.

Before the database can be opened to the general public, the database has to ensure that all future backups don't conflict with the previous backups. This is when a new incarnation should be created and it happens when you execute the following command to open the database:

ALTER DATABASE OPEN RESETLOGS;

Why?

Ok. If you look at the graphic, the SCN# for 13:00 (1 pm) in the original INCARNATION# 1 path is 300. We never reached the backup at 14:00 (2pm) which would have recorded the SCN# 400 in the backup. During the restore/recovery process, no backup is going to happen and (let's assume) if the restore process takes an hour, then no new data is going to be inserted. When the database is ready to go back online and the next backup occurs at 14:00 (2pm) then the backup will contain the SCN# 300. This is the same SCN# of the database as it was before the restore/recovery was initiated, which was recorded for the backup at 13:00 (1pm).

How Does Oracle Know Which Backup to Use?

This is why the INCARNATION# is set to a new value. It allows Oracle to discern between the Archive Log backup at 13:00 (1pm) with the following information:

INCARNATION#    1
SCN#           300
Time......... 13:00

...and the backup at 14:00 (2pm) with the following information:

INCARNATION#    2
SCN#           300
Time......... 14:00

Restoring the Database to Point-in-Time (2)

Let's assume the database keeps on running after the first restore/recovery action and slightly after 15:00 (3pm) somebody decides there needs to be a new restore/recovery back to 14:00 (2pm) the same day.

RMAN will restore the files, recovery the database and set off an ALTER DATABASE OPEN RESETLOGS to bring the database back online. The INCARNATION# will be now set to 3 and the first backup at 16:00 will contain the information:

INCARNATION#    3
SCN#           400
Time......... 16:00

...which would otherwise conflict with the backup of the database at INCARNATION# 2 and SCN#400 from 15:00 (3pm). But, because of the new INCARNATION# everything is fine.

Summary

That is the (albeit short) explanation of what the incarnations are used for. It allows RMAN (and the heroic DBA) to determine which path to take to restore and recover the database that has previously been backed up and restored more than once.

Next Up

This short explanation of incarnations is by no means complete. There are other factors that result in ORPHANED incarnations and OBSOLETE backups. Some of these topics will be discussed in a follow up answer either in this thread or in a separate Q & A.

For Example...

When looking at incarnations on a larger time-scale, then restoring and recovering may require you to set off a RESET DATABASE TO INCARNATION <number> before you can start recovering the database, because you might be restoring and recovering the database to a point-in-time which is located on the path of the INCARNATION# 1 and which may require you to restore an AUTOBACKUP copy of the control file, before you can start even thinking about restoring and recovering the database.

Stay tuned...


sys.dm_db_index_physical_stats is extremly slow

Dont run the DMV for whole database instead run it for particular table or index . With database being huge in size it is bound to take time.

You must read Paul Randal's Explanation about why this DMV could take more time.

  The idea of the DMV is to display physical attributes of indexes (and
  the special case of a heap) - to do this it has to scan the pages
  comprising the index, calculating statistics as it goes. Many DMVs
  support what's called predicate pushdown, which means if you specify a
  WHERE clause, the DMV takes that into account as it prepares the
  information. This DMV doesn't. If you ask it for only the indexes in
  the database that have logical fragmentation > 30%, it will scan all
  the indexes, and then just tell you about those meeting your criteria.
  It has to do this because it has no way of knowing which ones meet
  your criteria until it analyzes them – so can't support predicate
  pushdown.

Did you tried Ola Hallengren solution for index rebuild.

There are various modes you can try as mentioned in his article. But as you said limited also took 4-5 hours I guess this is what DMV is. Its actually slow and even MS believes the same about it.

Index rebuild is considered as maintenance activity and should be done when load on server is relatively less or during maintenance window.


Restrict Front Ended User access to Form entry only

There's a property on the form called DataEntry
DataEntry property documentation

If you set it to True, it will only allow new additions, it will not show you other records.


Production Database went into restoring mode

(Restoring...) is typically seen under two circumstances.

You're restoring a database, and have specified the WITH NORECOVERY option to allow, typically, log backups to be restored.  The last restore operation would not use the WITH NORECOVERY option, thereby directing SQL Server to run the recovery process on the database, and allow client access to it.
You've taken a backup of the database logfile, and have specified the WITH NORECOVERY option to ensure you have backed up the tail of the log.  This option might be used to ensure no one can access the database in anticipation of migrating the database to a different server.  You may inadvertently use this option when running a transaction log backup via the GUI:

While a database is being restored, it will show up as RESTORING.

You can test the tail log recovery scenario mentioned above in option 2 using this MCVE:

First, we create a database, and set it to FULL recovery:

USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'RestoringModeTest')
BEGIN
    ALTER DATABASE RestoringModeTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE RestoringModeTest;
END
GO
CREATE DATABASE RestoringModeTest;
ALTER DATABASE RestoringModeTest SET RECOVERY FULL;
BACKUP DATABASE RestoringModeTest TO DISK = 'NUL:';
BACKUP LOG RestoringModeTest TO DISK = 'NUL:';
GO

This shows the state of the database:

SELECT d.name
    , d.state_desc
FROM sys.databases d
WHERE d.name = 'RestoringModeTest';

╔═══════════════════╦════════════╗
║       name        ║ state_desc ║
╠═══════════════════╬════════════╣
║ RestoringModeTest ║ ONLINE     ║
╚═══════════════════╩════════════╝

Here we backup the tail of the log.  (NOTE, this is NOT a real backup, since the target is the NUL: device.  DO NOT USE THIS IN PRODUCTION!)

BACKUP LOG RestoringModeTest TO DISK = 'NUL:' WITH NORECOVERY;

Now, when we look at the state of the database:

SELECT d.name
    , d.state_desc
FROM sys.databases d
WHERE d.name = 'RestoringModeTest';

╔═══════════════════╦════════════╗
║       name        ║ state_desc ║
╠═══════════════════╬════════════╣
║ RestoringModeTest ║ RESTORING  ║
╚═══════════════════╩════════════╝

This statement will cause SQL Server to run the recovery process on the database to return it to the ONLINE state:

RESTORE DATABASE RestoringModeTest WITH RECOVERY;


Galera cluster replication stalls when using wsrep_provider_options repl.commit_order=2

(Is your question mostly about the "migration"?  Or about write performance on the resulting Cluster?)

Look at your transactions.  Optimal is some sort of "medium" size.  Too short leads to long latency.  Too long leads to extra risk of deadlocks/failure at COMMIT time.

Look at the network.  You are dependent on how far apart the nodes are.  If there are next to each other, you do not get the HA benefits.  If they are too far apart, you have latency issues.

Look at the write queries.  If they are likely to conflict, but you are sending them to different nodes, then you may be adding to the problem, relative to somehow focusing some sets of queries onto a single node.

Do you have "critical reads"?  How are you handling them?

More Galera tips .


You want to select all customers whose orders have been completely fulfilled. So first you need to know how many parts the customer has ordered in total:

select customerid, sum(total_number_parts) ordered
from orders
group by customerid

You also need to know how many parts have been shipped for each customer. You will need to join orders and dispatched parts to get the customerid associated with the dispatched parts.

select o.customerid, sum(dp.partqty) dispatched
from orders o
left join dispatched_parts dp
   on dp.ordersid = o.id
group by o.customerid

I'm using a left join here, so you also get the total quantity if no parts have been dispatched yet. This is not necessary for your question, but becomes relevant if you want to get all customers who haven't received all their parts yet.

We combine these two, and apply the requested condition in the where-clause:

select a.customerid
from (
    select customerid, sum(total_number_parts) ordered
    from orders
    group by customerid
) a
inner join (
    select o.customerid, sum(dp.partqty) dispatched
    from orders o
    left join dispatched_parts dp
       on dp.ordersid = o.id
    group by o.customerid
) b on b.customerid = a.customerid
where ordered = dispatched

Assuming the corrected orderids (1, 1, 3, 5, 5) I suggested in the comments, this yields

customerid
2
3

Alternatively, you could move the second part entirely into the where-clause by making it a correlated sub-query:

select a.customerid
from (
    select customerid, sum(total_number_parts) ordered
    from orders
    group by customerid
) a
where a.ordered = (
    select sum(dp.partqty)
    from orders o
    left join dispatched_parts dp
       on dp.ordersid = o.id
    where o.customerid = a.customerid
)


Fastest query for distinct IDs in a many-to-many relationship

What would be the most optimal plan for querying all distinct user_id
  for a significant part of the operation_id set (20%-60%).

Use a recursive query:

WITH RECURSIVE cte AS (
   (  -- parentheses are required
   SELECT user_id
   FROM   user_operations
   WHERE  operation_id < 500
   ORDER  BY user_id
   LIMIT  1
   )
   UNION ALL
   SELECT u.user_id
   FROM   cte, LATERAL (
      SELECT user_id
      FROM   user_operations
      WHERE  operation_id < 500
      AND    user_id > cte.user_id  -- lateral reference
      ORDER  BY user_id
      LIMIT  1
      ) u
   )
TABLE cte;

In combination with an index on (user_id, operation_id) - columns in that order. I expect index scans that filter on the second column. Reasonably accurate table statistics are important so Postgres knows it will only have to skip a few rows in the index to find the next user_id. Generally, one might want to increase the statistics target for operation_id in particular:

ALTER TABLE user_operations ALTER operation_id SET STATISTICS 1000;

Since there are only ~1000-2000 different operations, this may not even be necessary, but it's a small price to pay.

Details:

Optimizing queries on a range of timestamps (two columns)

If the predicate operation_id < 500 is stable (always the same), make that a partial index on just (user_id) instead:

CREATE INDEX foo ON user_operations (user_id) WHERE operation_id < 500;

Then statistics on operation_id are not relevant to this query any more.

Even if the predicate is not stable, there may be ways to optimize - depending on the full range of possible conditions and value frequencies.

Performance should be ... delicious.

I optimized the technique in this related answer on SO (with detailed explanation):

Optimize GROUP BY query to retrieve latest record per user

If you have a separate users table and a large part of all users can be found in your sample, even faster query styles are possible. Details in the linked answer.


Best way to store a value which could be multiple types

It would appear that you have already been told that you are straying down the road to the EAV model. Take a look at the image here for why the EAV model should be avoided at virtually all costs.

Bill Karwin, the author responsible for the image above, has written a book "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" and he devotes the first chapter to the EAV anti-pattern. He's also a big hitter on this group and huge on StackOverflow (for database issues).

My advice would be to have a table for each type of result and then to use VIEWs to combine them when necessary.

As an example, you could have

CREATE TABLE char_result
(
  question_id INT,
  user_id INT,
  cresult CHAR,
  result_correct BOOLEAN (or equivalent in your RDBMS)
  ..
  <other stuff>
  ..
);

Do the same for num_result, except substitute nresult INT (FLOAT... whatever) for cresult - same idea for VARCHAR &c.

Then create VIEWs over your different result tables for result_correct (and other fields - number_of_attempts... &c. - whatever your other fields are). In this case, you are comparing like with like and not peforming calculations equivalent to adding population to altitude as per the hilarious image referenced above!


postgresql: how do I dump and restore roles for a cluster?

From the CREATE ROLE documentation:

  Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster.

Since pg_dump dumps a single database, you can't extract roles with that utility.  The pg_dumpall --roles-only command you proposed will do the work - however you may need to filter its output so that only desired roles will be created in the new cluster.

Roles are stored in the pg_authid catalog, which is physically stored in the data/global/ subfolder of a PostgreSQL installation, together with the other cluster-wide tables. You can query the contents of pg_authid through the pg_roles view.

NOTE: you will need superuser rights to dump the roles.  Otherwise, you'd get a permission denied on SELECT on pg_authid - and even when a superuser grants SELECT rights, you'd get the same error.  In this case, however, you can list the roles by querying pg_authid directly, COPY it to a file and roll some magic to create the necessary CREATE ROLE and ALTER ROLE statements.


Reviewing the design of a database about animals of two distinct species

Analyzing the scenario from a pure conceptual perspective, an important entity type that has been missrepresented so far is Animal, which would help to (a) convey all the relevant associations more accurately and to (b) delineate the whole database more clearly at the logical level.
In this way:

Fox and Elephant would then be entity subtypes of Animal, which would in turn be their entity supertype.

Since each particular Animal instance is either a Fox or an Elephant —never both— it means that the subtype instances are mutually exclusive.

Any given Animal occurrence —be it a Fox or an Elephant— may be linked to zero, one, or more Item occurrences, while an Item instance will be linked to exactly one Animal counterpart —be it a Fox or an Elephant—.

Note: Supertype-subtype associations are referred to as superclass/subclass relationships in some conceptual modelling methodologies.

Expository logical DDL design
Consequently, I would set up the corresponding logical-level design along the lines of:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns depending
-- on the applicable business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient physical-level index strategies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE Species (
    SpeciesCode CHAR(1)  NOT NULL,
    Name        CHAR(30) NOT NULL,
    --
    CONSTRAINT Species_PK PRIMARY KEY (SpeciesCode),
    CONSTRAINT Species_AK UNIQUE      (Name)
);

-- “Populating” the Species table:
INSERT INTO Species
    (SpeciesCode, Name)
VALUES
    ('F', 'Fox'),
    ('E', 'Elephant');

CREATE TABLE Animal ( -- Represents the supertype.
    AnimalId        INT       NOT NULL IDENTITY (1,1), -- Column meant to enclosed the Identifiers as established in the business context of relevance.
    SpeciesCode     CHAR(1)   NOT NULL, --Stands for the discriminator property.
    CreatedDateTime DATETIME  NOT NULL,
    --
    CONSTRAINT Animal_PK            PRIMARY KEY (AnimalId),
    CONSTRAINT Animal_to_Species_FK FOREIGN KEY (SpeciesCode)
        REFERENCES Species (SpeciesCode)
);

CREATE TABLE Fox ( -- Conveys one of the subtypes.
    FoxId      INT         NOT NULL, -- No need for system-controlled surrogate values (e.g. those generated with the IDENTITY property) in this column.
    BusinessId VARCHAR(50) NOT NULL, -- This column is supposed to retain the Identifiers as established in the business context of relevance.
    BodyLength INT         NOT NULL,
    FurColour  VARCHAR     NOT NULL,
    --
    CONSTRAINT Fox_PK           PRIMARY KEY (FoxId),
    CONSTRAINT Fox_AK           UNIQUE      (BusinessId),
    CONSTRAINT Fox_to_Animal_FK FOREIGN KEY (FoxId)
        REFERENCES Animal (AnimalId)
);

CREATE TABLE Elephant ( -- Represents the other subtype.
    ElephantId INT         NOT NULL, -- No need for system-controlled surrogate values (e.g. those generated with the IDENTITY property) in this column either.
    BusinessId VARCHAR(50) NOT NULL, -- This column is supposed to retain the Identifiers as established in the business context of relevance.
    Weight     INT         NOT NULL,
    TuskLength INT         NOT NULL,
    --
    CONSTRAINT Elephant_PK           PRIMARY KEY (ElephantId),
    CONSTRAINT Elephant_AK           UNIQUE      (BusinessId),
    CONSTRAINT Elephant_to_Animal_FK FOREIGN KEY (ElephantId)
        REFERENCES Animal (AnimalId)
);

CREATE TABLE Item (
    ItemSeq         INT          NOT NULL IDENTITY (1,1),
    AnimalId        INT          NOT NULL,
    Description     VARCHAR(500) NOT NULL,
    CreatedDateTime DATETIME     NOT NULL,
    --
    CONSTRAINT Item_PK           PRIMARY KEY (ItemSeq),
    CONSTRAINT Item_to_Animal_FK FOREIGN KEY (AnimalId)
        REFERENCES Animal (AnimalId)
);

This db<>fiddle contains the DDL layout shown above so that you can see it in action.

The columns FoxId and ElephantId, constrained as the PKs of the appropriate tables, help in representing the conceptual-level one-to-one association by way of FK constraints that point to the AnimalId column, which is constrained as the PK of the Animal table. This implies that, in an actual “pair”, both the supertype and the subtype rows are identified by the same PK value; thus, it is more than opportune to mention that (a) attaching an extra column to hold system-controlled surrogate values to (b) the tables standing for the subtypes is (c) entirely superfluous.
As you can see I added the Animal.CreatedDateTime because I esteem that it is important to know the exact point in time when a certain Animal row was INSERTed into the database.
In this manner, the association between the Item rows and the Fox or Elephant rows is established “indirectly” via the corresponding Animal rows and the suitable constraints.
The Item.AnimalId column prevents having (1) one column for The Items.FoxId and (2) one column for The Items.ElephantId, with all the ambiguities and needless complexity they involve (e.g., accepting NULL marks with the repercussions they have on data manipulation, adding a CHECK constraint like the one you added to your Items table, etc.).
“Look-up” table for the FurColour data
I am not familiar with FurColours of Foxes (e.g., I do not know how many FurColours are presented by Fox specimens, or how the FurColours are composed, etc.), but you may like to evaluate incorporating a table that serves a “look-up” role for this sort of information, which can in turn be referenced to from a FOREIGN KEY constraint in the Fox table.
Integrity and consistency considerations
It is paramount to mention that, in your business context, you have to (1) ensure that each “supertype” row is at all times complemented by its corresponding “subtype” counterpart, and, in turn, (2) guarantee that said “subtype” row is compatible with the value contained in the “discriminator” column of the “supertype” row.
It would be very elegant to enforce such circumstances in a declarative manner by virtue of ASSERTIONs but, unfortunately, none of the major SQL platforms has suitable support for these powerful instruments (which are the proper tools for this type of job). Therefore, resorting to procedural code within ACID TRANSACTIONs is quite convenient so that these conditions are always met in your database. Other option would be employing TRIGGERs (procedural as well), but they tend to make things untidy.
Creating views to fix useful derivable tables
It seems that one of the purposes served by the columns FoxID and ElephantID of your Items table is that they help to determine the type of Animal row that an Item row is linked to, but that need is better coped with by means of a view (i.e., a derived table) as exemplified as follows:
CREATE VIEW ItemWithAnimal AS
    SELECT I.ItemSeq,
           A.AnimalId,
           A.SpeciesCode,
           I.Description,
           I.CreatedDateTime AS ItemCreatedDateTime,
           A.CreatedDateTime AS AnimalCreatedDateTime
        FROM Item   I
        JOIN Animal A
          ON I.AnimalId = A.AnimalId;

…where the SpeciesCode column values indicate the exact species of the AnimalId value of concern, so the people interpreting the data can see if the Item under consideration is linked to a Fox or an Animal. So when you want to retrieve Item information, you can SELECT directly FROM this derived table or view, instead of the Item base table.
Having views that fetch the “full” Fox or Elephant information seems to be useful too, e.g.:
CREATE VIEW FullFox AS
    SELECT A.AnimalId,
           F.BusinessId,
           F.BodyLength,
           F.FurColor,
           A.CreatedDateTime
        FROM Fox    F
        JOIN Animal A
          ON F.FoxId = A.AnimalId;

Evidently, this view would be more benefitial if, say, the Animal table was comprised of more columns meant to be “shared” by the Fox or Elephant tables, but it is worth to illustrate the benefit of views.

The views discussed above are also included in the db<>fiddle previously linked to.

Review of your Items.ItemID, Items.FoxID and Items.ElephantID columns
It seems that your Items.ItemID column is some sort of derivable column, i.e., the values it contains are calculated in terms of the values contained in two other columns of your table, i.e., Items.FoxID or Items.ElephanID along with Items.ItemSeq (which is unnecessarily involved). I highly advise that you avoid resorting to that kind of course of action for any kind of column, but since Items.ItemID is constrained as the PRIMARY KEY (PK) I put more emphasis to my advise.
In this way, if in your business domain an Item is uniquely diferrentiated by the combination of its ItemSeq and the Identifier of the Animal of relevance (be it a Fox or an Elephant), you can then adapt the logical arrangement I expounded for the Item table by declaring a two-column composite primary key on (AnimalId, ItemSeq) and avoid the work arround with the derivable PK.
Other scenarios involving supertype-subtype associations
In case that you want to see more examples of supertype-subtype relationships, which are typical data structures that come about in business environments of very different natures, you may find of interest, e.g., my answers to the questions entitled:

“Database schema for entities with two possible owner/parent types?”
“Developing a database for a funds transfers business where (a) people and organizations can (b) send and receive money”
“How to model an entity type that can have different sets of attributes?”

You may like to take a look at the rest of posts grouped by the subtypes tag too.


Why only ~half of RAM is used by Oracle on Linux server?

Normally we allocate about half of the systems memory for shared_pool and database buffers, preferably mapped on huge pages. AMM is not compatible with huge pages so when a dba is working with the system he would disable that and manage the memory manually. Define a minimum size for the database buffers and also for the shared pool.
The rest of the memory is available for processing things like sorts and merges.
Maybe the workload of your system is low and could do with a smaller footprint. I also noticed this is a RAC system. RAC likes to have some free memory.
So as a rule of thumb your system is setup pretty much default and could be adjusted to better suit your workload. The exact sizing depends on the usage of the shared_pool and how much caching your need to give a usable performance. At a certain point performance is not improved by giving more memory.


SQL Server distribution database log file grows uncontrollably after full database backup

It sounds like you have a high volume of transactions happening with your replication. If so, that "clean up: distribution" job should probably be running far more often. It sounds to me like it's happening once a day. On my high transaction replication server I have it running every 15 minutes to keep just the latest 36 hours. That way it paces itself and doesn't bog down.

Here's an article that might help - http://www.sqlservergeeks.com/blogs/singhsatnam84/sql-server-bi/563/sql-server-controlling-growth-of-a-distribution-database


I think you should store the data in the database as a normal bytea column.  That way you get all the advantages of a database, and you can process the data using database functions (and even PL/Python, if you want that).  Larger data items will automatically be stored out of line, so there would be no reason for you to introduce another reference indirection.

The main reasons for storing large binary objects outside the database would be if they are too large to be able to store and retrieve them in a satisfactory time, if they bloat the database beyond practicality, or if you need to access the files as files from a separate application.  None of that applies there, as far as I can tell.


There are dozens of objects in msdb that reference the master database.

If msdb were not marked as TRUSTWORTHY, then users would need permission to both the msdb object they are interacting with, plus the master object that is being referenced.

For example, msdb users who get permissions via the SQLAgentUserRole database role are granted execute on msdb.dbo.sp_enum_sqlagent_subsystems. The call stack for that procedure eventually hits master:

* msdb.dbo.sp_enum_sqlagent_subsystems
    * msdb.dbo.sp_enum_sqlagent_subsystems_internal
        * master.dbo.xp_instance_regread

If msdb is not marked as TRUSTWORTHY, then users who are part of the SQLAgentUserRole database role would ALSO need execute permission on master.dbo.xp_instance_regread.

Technically speaking, it is probably possible to remove the TRUSTWORTHY setting in msdb and instead grant specific permissions in master. However, those required permissions are neither documented nor supported.


Oracle: unable to change to noarchivelog

ORA-12560: TNS:protocol adapter error on Windows is typically caused by incorrent ORACLE_HOME and ORACLE_SID environment variables.

Find the database service:

Find the path and name of the instance:

Based on that:

set ORACLE_HOME=O:\oracle\product\12.2.0\dbhome_1
set PATH=%ORACLE_HOME%\bin;%PATH%
set ORACLE_SID=RTG

Then try again:

sqlplus / as sysdba
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;


I suggest you create a calendar table with a couple of years of data. I'll just use one with sufficiently many rows for your example:

CREATE TABLE CALENDAR (d date not null primary key);
INSERT INTO CALENDAR (d) VALUES ('2019-05-01'),...,('2019-08-01');

While we are at it, let's create some usable sample data instead of ascii-art tables:

create table items
( item_id int not null primary key
, startdate date not null
, enddate date not null
, capacity int not null);

insert into items (item_id, startdate, enddate, capacity)
values (1, '2019-05-01', '2019-06-01', 8)
     , (2, '2019-06-21', '2019-07-01', 13)
     , (3, '2019-07-11', '2019-08-01', 4);

To determine the capacity for each date:

select cal.d, i.item_id, coalesce(i.capacity, 0) as capacity
from calendar cal
left join items i
    on cal.d between i.startdate and i.enddate;

Now you can subtract your userdata from that, perhaps easiest to do with a sub-select

select cal.d, i.item_id
     , coalesce(i.capacity, 0) - coalesce((select sum(number)
                                           from userdata u
                                           where u.dt = cal.d
                                             and u.item_id = i.item_id),0) as availible_items
from calendar cal
left join items i
    on cal.d between i.startdate and i.enddate;


How do I export data from Oracle 11.1 to 10.1?

To do this, you'll be wanting to use the 11.1 Data Pump (expdp/impdp) rather than exp/imp. With Data Pump you export using the higher version export utility with the VERSION= parameter.

For example:

expdp scott/tiger version=10.1 directory=DUMPDIR dumpfile=DUMPFILE.dmp logfile=DUMP.log

Don't forget to create the directory (using CREATE DIRECTORY DUMPDIR AS '/path/to/dir';) first.


How to properly monitor the number of PostgreSQL database connections?

Its better to use Workload monitoring tools like pgbadger for checking database connections and overall load. It will help you understand which users are connecting for how much time and what queries are being fired by them. For information on installing and configuring pgbadger, refer this page.

If you just want to check the number of active connections, you can use
 select count(*) from pg_stat_activity where state='active'


SQL Server - how transactions and transaction log work (simplified)

No your theory is wrong.

Dirty pages can be written to disc even if the transaction is not yet committed. However it is ensured that they cannot be written before the last transaction log entry that modified the page has been written to disc.

The transaction log records do contain sufficient information both for redo and undo (except for in tempdb where only undo is necessary). If you decide to rollback the transaction then nothing is deleted from the log. Instead compensation log records are written to the log indicating this.


innodb high i/o , some parameters make mysql crash

Check the logs.  There should be some clue of what caused the crash.  I don't think the settings that you highlighted would cause a crash, but let's change them some:

innodb_buffer_pool_size = 40G        -- good
innodb_buffer_pool_instances = 16    -- 16 is "good enough"; more is "diminishing returns"
innodb_buffer_pool_chunk_size = 128M -- only needed for dynamically changing bp
max_connection = 1000

('instances' has only a small impact on performance.)  If my advice is not good, read the Windows-specific recommendations in https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances

If you have HDD (not SSD), then lower some settings:

innodb_io_capacity = 400
innodb_read_io_threads = 16
innodb_thread_concurrency = 0
innodb_write_io_threads = 16

If you are concerned about performance, turn on the slowlog with long_query_time=1 so you can catch the naughtier queries.

innodb_dedicated_server does not exist until 8.0.3.


Move data from MySQL 5.0 to other server with MySQL 5.7

mysqldump and mysqlimport (as described by the documentation you cited) should be fine for your regular tables, especially since you don't have any foreign keys.

However, due to the differences in MySQL versions, the tables in the mysql database will be different, so I suspect migrating that database probably can't be done with this particular method.

If you need to migrate the mysql database, there are better ways of doing that.


How to Audit 2 or more SQL Logins (server principals) using SQL Server Audit

I believe you are using a part of SQL server audit specification in very first step:

Firstly you just need to create a server audit object using SQL Server Audit. Now in this audit section you are using where clause, which also requires a creation of sql server audit specification:
Check the permissions for altering an audit:

a)To create, alter, or drop a server audit, principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission.

b)Users with the ALTER ANY SERVER AUDIT permission can create server audit specifications and bind them to any audit.

Refer to below eg : from MSDN whihc might help you in understanding the audit using principals in where clause:

  CREATE DATABASE TestDB;
  GO
  USE TestDB;
  GO
  CREATE SCHEMA DataSchema;
  GO
  CREATE TABLE DataSchema.GeneralData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);
  GO
  CREATE TABLE DataSchema.SensitiveData (ID int PRIMARY KEY, DataField varchar(50) NOT NULL);
   GO
    -- Create the server audit in the master database
 USE master;
 GO
  CREATE SERVER AUDIT AuditDataAccess
  TO FILE ( FILEPATH ='C:\SQLAudit\' )
 WHERE object_name = 'SensitiveData' ;
 GO
 ALTER SERVER AUDIT AuditDataAccess WITH (STATE = ON);
  GO
 -- Create the database audit specification in the TestDB database
  USE TestDB;
  GO
CREATE DATABASE AUDIT SPECIFICATION [FilterForSensitiveData]
FOR SERVER AUDIT [AuditDataAccess]
ADD (SELECT ON SCHEMA::[DataSchema] BY [public])
WITH (STATE = ON);
GO
-- Trigger the audit event by selecting from tables
 SELECT ID, DataField FROM DataSchema.GeneralData;
 SELECT ID, DataField FROM DataSchema.SensitiveData;
 GO
 --- Check the audit for the filtered content
  SELECT * FROM fn_get_audit_file('C:\SQLAudit\AuditDataAccess_*.sqlaudit',default,default);
   GO


Yes.

In SQL Server Profiler, you can filter on "Duration"

(This is from the "Standard (default)" template on SQL Server 2012 RC0 Profiler)


How to BCP export three related tables in a transaction consistent manner?

how about creating a database snapshot and then running the BCP against that ?
https://docs.microsoft.com/en-us/sql/relational-databases/databases/create-a-database-snapshot-transact-sql?view=sql-server-ver15


How are derived table implemented in MySQL

This is tricky question, specially since 5.6, in which the optimizer has changed the way subqueries are executed. Specially, having into account that the definition of "derived table" may change person to person

From 4.1 to 5.5, in many cases, either a temporary table would be created or a cross join will be done between tables, filtering later. In some cases, like the infamous SELECT ... IN (SELECT ...) a derived table will not be created, and it will be executed as a correlated subquery (one row at a time). See the examples here: http://forums.mysql.com/read.php?115,576862,576999#msg-576999

On that same thread, Roy Liseng comment the great subquery improvements of 5.6: http://forums.mysql.com/read.php?115,576862,577215#msg-577215 You can see a summary of the improvements on the 5.6 subquery optimization on the manual, including the creation of indexes on materialization, or its conversion into semi-joins. More details on some of the Oracle engineer blogs.

I think that I had to mention this because there are a lot of things going on on the optimizer, and not always a temporary table is created first "as is".

Regarding the other question, MySQL always tries to create internal temporary tables on MEMORY first, but failsback to disk if they are too large (larger than min(tmp_table_size,  max_heap_table_size) or some other restrictions (like having BLOBS).


Is it possible to change the MySQL server-id during a SQL session?

server_id is a dynamic variable, so you can change it while the server is running. To change the global variable, do:

SET GLOBAL server_id=21

More info at http://dev.mysql.com/doc/refman/5.5/en/replication-options.html#option_mysqld_server-id


High page fault, slow query/ insert during background index creation in MongoDB

Index builds can impact the performance of your database. While background index builds will not block read or writes, it is not correct to say that "index creation should not affect [the performance of] read/write".

Building an index will require paging the entire collection through memory; this is cause of the page faults being seen. You will likely want to only build a single index at a time to reduce the impact of this process.


I've updated the SQL Fiddle with the reference answer

1) Referenced answer seems to have a misprint: -- Player 1 (Round 1: 5pts ((10+5)/2), Round 2: 6pts), whereas (10+5)/2 = 7.5 (or 8 while rounding to integer).

2) Look at

SELECT `player`.`name`, `match`.`roundID`, ROUND(AVG(`match_player`.`points`)) AS `points`
FROM player
JOIN `match_player` ON `match_player`.`playerID` = `player`.`id`
JOIN `match` ON `match`.`id` = `match_player`.`matchID`
JOIN `round` ON `round`.`id` = `match`.`roundID`
WHERE `round`.`competitionID` = 1
  AND `round`.`season` = 2018
  AND `match`.`status` = 'finished'
GROUP BY `player`.`name`, `match`.`roundID`
;

It fits your reference answer.


SQL Get Users By One Group But Exclude Others

SELECT
    id
FROM
    users as u
    LEFT JOIN groups
        ON groups.user_id = u.id
WHERE
    groups.name = 'A'
    AND NOT EXISTS (
        SELECT
              1
        FROM
            groups
        WHERE
            user_id = u.id
            and name IN ('B', 'C', 'D')
    )


Do triggers start to degrade in performance (or sometimes fail) on highly transactional tables?

are insert triggers on that table reliable to fire every single time?

Absolutely. Triggers aren't optional, they WILL fire.

Triggers CAN cause a performance impact on highly transactional tables because the trigger fires synchronously to your DML operation. On an insert trigger, if the trigger execution fails, your insert fails. You won't get confirmation that your insert completed successfully until your trigger has also completed successfully.

In this way, your insert trigger WILL make your insert more expensive, because the trigger is adding work to a previously simple INSERT statement. Whether it's going to be acceptable performance will depend on what your trigger is doing.

The same performance problems that can affect any query or procedure can also affect triggers. You'll need to tune them and pay attention to whether that trigger is doing it's job effectively.

  How many transactions per second can SQL Server table triggers handle?

This isn't a straightforward question. It depends on your schema, your data access patterns, your server hardware, etc. Needless to say, some companies are absolutely running SQL Server at massive scale with transactions per second that are mind boggling. But they have fast hardware, large budgets, and very skilled teams building their systems.

For this part of the question to be answerable, you'd need to propose a specific scenario & problem to see if anyone can help resolve that more specific issue.


Order by reduces performance of a grouped result set

Remove LEFT if you don't need it.
The ORDER BY has ISNULL(start), yet the WHERE clause will fail if start isNULL`.  Seems like something could be simplified.
Will the table(s) become so big that they cannot be cached in RAM?  If so, we can talk about the choice of PRIMARY KEY.  (I'll need to see SHOW CREATE TABLE for this discussion.)
The query, as it stands, is destined to take some time...

It needs to look at all index rows with academy_id=3 and day=2, then filter on start.  That is currently about 5 rows; will that increase when there is more data?  Will you end up with a LIMIT?

Will the test on start be a significant factor in the filtering?  If so we can discuss a 'lazy eval' technique.

Here's one thing that may help:  Remove the LEFT JOIN members and replace the GROUP_CONCAT... with:

( SELECT GROUP_CONCAT(...) FROM members AS m
      WHERE m.id = members_attendance.member_id ) AS members,

Also, change

(DATE(members_attendance.checked_in)=CURDATE())

to

members_attendance.checked_in >= CURDATE() AND
members_attendance.checked_in  < CURDATE() + INTERVAL 1 DAY

and add

INDEX(event_id, checked_in)  -- in that order

(unless that table has PRIMARY KEY(event_id) -- it would help if you showed us SHOW CREATE TABLE)

Explanation

Some of the question deals with the slowdown due to ORDER BY.  Let me walk you through some of the steps:

fetch things from the first table, filtering based on the WHERE
fetch things from JOINed tables, again filtering where appropriate
GROUP BY id -- Since the joins created an explosion of rows in a tmp table, but you wanted only one row per id, this shrinks things back down.  It may require a temp table and a sort.  (Note:  My suggestion on GROUP_CONCAT avoids some of the explosion.)
ORDER BY something_else -- If the ORDER BY had been on the same value(s), it could be subsumed into the GROUP BY.  But, since it is not, there needs to be another temp table and sort.

Caveat: "temp table" may or may not be entirely in RAM.  "sort" may or may not be in RAM.  If either of these hits disk, the query will be slower.

Caveat:  If any of the SELECTed columns are TEXT or BLOB, the temp table will be headed for disk.  (The whole story is more complex; let's see SHOW CREATE TABLE.)

It may be possible to get rid of all JOINs, such as my suggestion above.  If so, then you can get rid of the GROUP BY, thereby speeding things up.

Do what you can in the directions I suggest, then let's make another pass at it.  (There may need to be further index changes.)

<my-rant>
    3rd party software tries to help with DB abstraction,
    but that usually gets in the way of performance.
</my-rant>


Weak entity with a many-to-many relationship with its owner

Yes, your entity is OK, but I would recommend this :

Do not use a multiple fields for your date. Instead of Day, Month, Year, you should use DateWorked. Maybe you are already doing that but it doesn't reflect in your explanations. This way, your date will always be valid. You won't have to check if February 30, 2013 is a valid date or not.
I strongly recommend using only 1 field surrogate primary key like HoursID. You can create a unique index on ConID and DateWorked to validate it will not exist more than once.
You may add complexity now or later by using ProjectID and that project could also bind to a ClientID.


How to connect Powershell to the SQL Server named instance?

$ServerName = "[local\s12]";

Remove the square brackets.

Apart from that, the PowerShell code looks fine (and works on my test machine - the code I used is below, copied from my SQL Server test machine SERVER1).

PS C:\Users\Administrator.ADVENTURE-WORKS> [void][reflection.aSSEMBLY]::LoadWithPartialName("Microsoft.SQLServer.Smo" )
PS C:\Users\Administrator.ADVENTURE-WORKS> $ServerName = "server1\sql2012"
PS C:\Users\Administrator.ADVENTURE-WORKS> $ServerName
server1\sql2012
PS C:\Users\Administrator.ADVENTURE-WORKS> $Srv = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server $ServerName

Additionally, is the server actually called "local" or is that just anonymised? If you want to connect to the local server then the alias is a period.

$ServerName = ".\SQL2012"

Otherwise, try to connect to the server local\s12 using SQL Server Management Studio using windows authentication from the same machine that you are running the powershell script. if that fails then there is a connectivity issue. Check firewalls (on the SQL Server machine and on any routers in between the client and server). Check that the TCP/IP network library is enabled on both the client and the server. If TLS is enabled then confirm that the certificates are all correct.


The names you're seeing under "Other Resources" are actually the SQL Server instance names, not the names of the nodes that could be possible owners.

"Possible Owners" are the machines that are eligible to run the Roles configured in the cluster.  For SQL Server, Roles are the SQL Server instance, the SQL Server Agent service, etc.

"Other Resources" are items required to run each role.

The official Microsoft Docs on Failover Clustering with SQL Server is a great resource for understanding how that all ties together.

This MSDN blog talks about preferred and possible owners, and shows how SQL Server manages them automatically.

This Microsoft Docs page talks about the relationship between AlwaysOn and Failover Clustering.  This section of the above page says:

  Do not use the Failover Cluster Manager to manipulate availability groups, for example:

Do not add or remove resources in the clustered service (resource group) for the availability group.
Do not change any availability group properties, such as the possible owners and preferred owners. These properties are set automatically by the availability group.
Do not use the Failover Cluster Manager to move availability groups to different nodes or to fail over availability groups. The Failover Cluster Manager is not aware of the synchronization status of the availability replicas, and doing so can lead to extended downtime. You must use Transact-SQL or SQL Server Management Studio.


How can I verify that my SQL Server edition is Express?

You have to check the SERVERPROPERTY ('Edition') AS ServerEdition to find if your installed instance is an Express edition or standard or enterprise.

You can use below T-SQL :

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion

Another variation that tells you the database size limit based on the version (note that -1 means unlimited)

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build '
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion
    , case  when cast(SERVERPROPERTY ('Edition') as varchar(255)) like '%express%'
                then
                    case
                        when (
                                CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 4) as int) >=10  -- major
                             and CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 3) as int) in (0,50) -- minor
                              ) then 10  -- this is sql server 2008R2 and up
                        when (
                                CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 4) as int) <= 10
                             and CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 3) as int) < 50
                              ) then 4  -- this is sql server 2008 and less
                      end
                        else -1
        end  as EditionLimit

Starting sql server 2008R2, Express edition database limit was increased from 4GB to 10GB.


varchar datatype accepting integer values

Your question relates to implicit type conversions.

I searched the MariaDB documentation but their description is not very detailed except to show examples of this happening (see https://mariadb.com/kb/en/mariadb/type-conversion/). MySQL has a similar page which definitively states it implicitly converts to/from strings as required (see http://dev.mysql.com/doc/refman/5.7/en/type-conversion.html).

  For example, MySQL automatically converts numbers to strings as necessary, and vice versa.

The ISO-92 standard allows for implicit conversions but doesn't define all of them, it's vendor specific. However you can't stop this happening in SQL Server, or Oracle, I suspect it's by convention and can't be avoided.


How to set addtime(current_timestamp, '15:00:00') as MySQL timestamp column default value?

MySQL does not support functions in column default values. CURRENT_TIMESTAMP is the closest thing you'll find to an exception:

  The DEFAULT clause specifies a default value for a column. With one
  exception, the default value must be a constant; it cannot be a
  function or an expression. This means, for example, that you cannot
  set the default for a date column to be the value of a function such
  as NOW() or CURRENT_DATE. The exception is that you can specify
  CURRENT_TIMESTAMP as the default for a TIMESTAMP column.

http://dev.mysql.com/doc/refman/5.5/en/create-table.html#id850368


Postgresql- how to rotate table partitions, trunc oldest every day?

First approach is creating partition every day which name matches date:

DROP TABLE IF EXISTS log_table;

CREATE TABLE log_table (
    logid         SERIAL,
    logdate         date DEFAULT Now()
) PARTITION BY RANGE (logdate);

CREATE TABLE log_table_2020_05_10 PARTITION OF log_table FOR VALUES FROM ('2020-05-10') TO ('2020-05-11');
CREATE TABLE log_table_2020_05_11 PARTITION OF log_table FOR VALUES FROM ('2020-05-11') TO ('2020-05-12');
CREATE TABLE log_table_2020_05_12 PARTITION OF log_table FOR VALUES FROM ('2020-05-12') TO ('2020-05-13');
CREATE TABLE log_table_2020_05_13 PARTITION OF log_table FOR VALUES FROM ('2020-05-13') TO ('2020-05-14');
CREATE TABLE log_table_2020_05_14 PARTITION OF log_table FOR VALUES FROM ('2020-05-14') TO ('2020-05-15');

And then every day you will have to drop the oldest partition and create the new one:

DROP TABLE log_table_2020_05_10;
CREATE TABLE log_table_2020_05_15 PARTITION OF log_table FOR VALUES FROM ('2020-05-15') TO ('2020-05-16');

The second approach with always-the-same-name of partitions will looks like this:

-- initial tables
DROP TABLE IF EXISTS log_table;

CREATE TABLE log_table (
    logid         SERIAL,
    logdate         date DEFAULT Now()
) PARTITION BY RANGE (logdate);

CREATE TABLE log_table_4 PARTITION OF log_table FOR VALUES FROM ('2020-05-10') TO ('2020-05-11');
CREATE TABLE log_table_3 PARTITION OF log_table FOR VALUES FROM ('2020-05-11') TO ('2020-05-12');
CREATE TABLE log_table_2 PARTITION OF log_table FOR VALUES FROM ('2020-05-12') TO ('2020-05-13');
CREATE TABLE log_table_1 PARTITION OF log_table FOR VALUES FROM ('2020-05-13') TO ('2020-05-14');
CREATE TABLE log_table_current PARTITION OF log_table FOR VALUES FROM ('2020-05-14') TO ('2020-05-15');

And then every day you will schedule something like:

-- drop the oldest partition
DROP TABLE log_table_4;

-- rename others
ALTER TABLE log_table_3 RENAME TO log_table_4;
ALTER TABLE log_table_2 RENAME TO log_table_3;
ALTER TABLE log_table_1 RENAME TO log_table_2;
ALTER TABLE log_table_current RENAME TO log_table_1;

-- create new one
CREATE TABLE log_table_current PARTITION OF log_table FOR VALUES FROM (date_trunc('day', Now())) TO (date_trunc('day', Now())+INTERVAL '1 day');


kill 61 with statusonly. SQL tells me that,Status report cannot be obtained. Rollback operation for Process ID 61 is not in progress

It seems Kill With statusonly reports ,only when the session is being killed manually

From Paul Randal :(Emphasis mine)

  KILL WITH STATUSONLY generates a report only if the session ID or UOW is currently being rolled back because of a previous KILL session ID|UOW statement.

So in your case,the operation was not killed..So you will not be able to track status

You can use Sys.dm_Exec_requests to get rollback estimation time

select percent_complete,estimated_completion_time,* from
sys.dm_exec_requests
where status like '%rollback%'

further on resuming part,you can resume If you are using Index Reorg not Rebuild

some more info on reorg vs rebuild (I read about this on MSDN,but not able to find now)

  Reorganize works on page level, so if you happen to stop in the middle, it will roll back that particular page which is 8kb and not the whole thing.

So if you are not using Reorg,only option is to restart index rebuild and it starts from beginning again


Use of table compression in SQL Server 2014

In my experience there are a couple of things to consider:

Sometimes, perhaps due to some maintenance task which doesn't respect compression settings, indexes and tables have compression turned off so carefully consider any routine tasks you have which rebuild indexes.
Space saving can be significant
When a query against a compressed table/index reads data from disk it was quicker (in my experience) than a query reading an uncompressed table due to the I/O limitations of disk.
When the pages were already in the buffer pool, though, the query was slower than when compared to uncompressed data because of the additional CPU involved in uncompressing the data.

So consider your situation in regards to CPU, RAM and storage capacities. Don't forget to consider columnstore indexes which offer both good compression and query performance.


If the PostgreSQL version and architecture is the same on the target so they're binary compatible: Set up streaming replication with WAL archive fallback, so the new host replicates from the old one. Enable hot_standby mode so you can connect to the new host read-only. Test. Then when you're ready to cut over, stop the old server and pg_ctl promote the new server to a read/write master.

If the PostgreSQL version is different on the target: Set up Londiste or Slony-I logical replication from the old DB to the new one. Let it sync up. When ready to switch over, shut down the old server, stop the replication service, switch clients to write to the new server.


Does it make sense to use full recovery if the data and log files are on the same HD?

I can't speak to 1 but for 2, I don't know that your position should be pushing the business toward a specific RPO (recovery point objective). They may not be aware that they'd have to re-enter all the data for a day if things go belly up. Talk to them, find out how much data loss they're willing to tolerate. If they say 24 hours is too much, great, then that indicates the current approach is insufficient for their needs. If that requires hardware purchase to meet the RPO, then they will need to provide funding or accept their current max data loss. Finally, document the outcome in some public place and then test on your restores a recurring basis to ensure you are able to meet that RPO.

That said, there are plenty of other reasons to have data and log (and temp) on separate drives. Some of them documented on this question https://serverfault.com/questions/38511/ms-sql-layout-for-best-performance


login failed for user 'domain\localMachineName$'

This is how the NETWORK SERVICE account from localMachineName presents itself to SQL Server (and file shares, and web sites, etc).

You need to profile "Login Failed" events (or XE Capture or whatever) to identify the program and/or Windows PID


DBCC check on Always On Secondary Replica

Checkdb should be run on both primary and secondary replicas. Normally for big databases and where you have busy system and you cannot afford to run checkdb on both replicas in one go specially on primary replica, people run checkdb on all the secondary replica and failover the primary to secondary making secondary new primary. Now you have new secondary on which you can run checkdb. This allows you to run checkdb on all the nodes without affecting primary workload.

When you run checkdb on secondary primary do gets affected because secondary has to create snapshot and run recovery on it so always run checkdb(even on secondary replica) when load on your complete AG is relatively very less or during downtime.

If you can run checkdb on all the replicas once in a week that would be best thing to do. There are options like physical_only which minimises the impact of checkdb but remember if you use this option the checkdb does not do complete consistency check

Must read:

Checkdb From Every Angle
Minimizing Impact of checkdb


How are auto_increment keys handled in INSERT (SELECT * FROM...)

You can insert into an auto-increment column and specify a value. This is fine; it simply overrides the auto-increment generator.

If you try to insert a value of NULL or 0 or DEFAULT, or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator.

So, it's fine to INSERT INTO table1 SELECT * FROM table2 (by the way, you don't need the parentheses). This means that the id values in table2 will be copied verbatim, and table1 will not generate new values.

If you want table1 to generate new values, you can't do SELECT *. Either you use null or 0 for the id column:

INSERT INTO table1 SELECT 0, col1, col2, col3, ... FROM table2;

Or else you omit the column from both the INSERT statement's column list and the SELECT statement's select-list:

-- No id in either case:
INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3, ... FROM table2;

Before you ask, there is no syntax in SQL for "select * except for one column". You have to spell out the full list of column names you want to insert.


How to Increment duplicate strings in a column

You can use the row number window function for this (http://www.postgresql.org/docs/9.2/static/functions-window.html)

SELECT col || ' ' || row_number() OVER (PARTITION BY col)
FROM test_a;

Output will be:

+------------+
| ?column?   |
|------------|
| bar 1      |
| bar 2      |
| bar 3      |
| bar 4      |
| foo 1      |
| foo 2      |
| foo 3      |
| foo 4      |
+------------+


Restoring postgresql 9.4 crashed database

I finally solve my problem.
I recreate pg_multiaxt/members/0000 file by giving more offset. My first offset was 55344 and the new one is 100192.
The command is :

dd if=/dev/zero of=0000 bs=1 count=100192

After restarting my postgresql service my database is now working correctly.


How to insert (file) data into a PostgreSQL bytea column?

as superuser:

create or replace function bytea_import(p_path text, p_result out bytea)
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function bytea_import(p_path text, p_result out bytea)
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data
             from pg_largeobject
             where loid = l_oid
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

insert into my_table(bytea_data) select bytea_import('/my/file.name');


MySQL Replication : Duplicate entries - SQL_SLAVE_SKIP_COUNTER or delete row?

You don't have to use any trigger, If you want to safely skip duplicate key errors, please add this to my.cnf on slaves

[mysqld]
slave-skip-errors=1062

Then restart mysql

Check if all the slaves have different serverIDs


How to return specific row number of a Hierachial query - Oracle

See this article from Oracle why the rownum approach won't work:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

See this article on StackOverflow for the solution:
https://stackoverflow.com/questions/13351318/table-rownum-returns-0-records-if-rownum-is-tested-for-value-greater-than-100

Edit (for those who don't like reading):
- you want to do something like this

select count(*)
from (select rownum r
        from some_table
        order by r)
where r>=101
and rownum <= 400;


Yes it's the average size of each row in your table.

row size * N rows = table size

Row size = 83 B

Rows = 22,000,001

Row Size times Number of rows:

83 * 22000001 = 1826000083

Convert result in Giga Bytes:

1826000083 / 1024 / 1024 / 1024 = 1.7

Total = 1.7 GiB

Max


Low cardinality estimate in hash match operator

The question of why one cardinality estimation model produces closer results than the other in this case is actually not that interesting. The original CE estimates that not finding a matching row has a very small probability; the new CE calculates that it is almost certain. Both are 'correct', just based on different modelling assumptions. Fundamentally, multi-column semi joins are tricky to evaluate based on single-column statistical information.

It is much more interesting to think about what the query is trying to do, and how we can write it in a way that is more compatible with the statistical information available to SQL Server.

A key observation is that the query will return row(s) with one value per group. In the case of the original query, that is row(s) with the minimum HistoryId value for each Transactionid. In the repro, it is row(s) with the minimum c1 value for each different value of c2. The NOT EXISTS query is just one way of expressing that requirement.

SQL Server has good statistical information about distinct values (density) so all we need to do is write the query in a way that makes it clear we want one value per group. There are many ways to do this, for example (using your repro):

SELECT *
FROM dbo.nat AS N
WHERE N.c1 =
(
    SELECT MIN(N2.c1)
    FROM dbo.nat AS N2
    WHERE N2.c2 = N.c2
);

or, equivalently:

SELECT N.*
FROM dbo.nat AS N
JOIN
(
    SELECT
        N.c2,
        MIN(N.c1) AS c1
    FROM dbo.nat AS N
    GROUP BY
        N.c2
) AS J
    ON J.c2 = N.c2
    AND J.c1 = N.c1;

This produces an exactly correct estimate of 9999 rows in 2008 R2, 2012, and 2014 (both CE models):

With a natural index (which would probably be unique as well):

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

The plan is even simpler:

You may not always be able to get this very simple plan form, depending on indexes, and other factors. The point I am making is that using basic grouping and joining operations often gets better results from the optimizer (and its cardinality estimation component) than more complex alternatives.

Final notes to clear some misconceptions in the question: the 'new CE' was introduced in 2014. TF 4199 enables plan-affecting optimizer fixes. TF 9481 specifies the original ('legacy') CE, and is only effective on 2014 and later versions.


phpMyAdmin how to run an output query? run a batch?

It is actually easiest to simply copy&paste the SQL statements.  Don't bother with trying to find and "eval".

It is even easier in the mysql commandline tool.

Here are my tips on gotchas that sometimes occur in the conversion from MyISAM to InnoDB:  http://mysql.rjweb.org/doc.php/myisam2innodb