Each execution of the stored procedure gets a separate copy of the variable, so that is not the risk you need to worry about. The value of the variable is 'private' to your transaction in the sense that no other concurrent transaction can see it or change its value (they have their own variable).

The concurrency problem you need to worry about is that the data in the database may change between statements, or even while a statement is executing.

Using an update locking hint on the investments table only prevents the touched row from being changed by a concurrent session before your transaction completes. It does not necessarily prevent other changes in the database (such as the user that owns that investment changing or the user's id being changed), nor does it prevent other transactions reading that value.

Rather than adding random locking hints that require an expert detailed analysis to assess (and are still easy to get wrong), it is usually simpler, and more sensible, to think about the level of isolation your transaction requires.

If you need the transaction to have persistent effects as if it were the only transaction running on the system (so eliminating worries about concurrent changes), you could run it at the serializable isolation level. If snapshot isolation (not RCSI!) is available to you, and write conflicts are expected to be rare, that could be a suitable isolation level to use as well. Another serialization-type solution would be to use application locks.

For example, using serializable isolation:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

DECLARE @amount decimal(18,2);

UPDATE dbo.investments
SET date_stop = GETUTCDATE(),
    @amount = amount
FROM dbo.investments AS I
JOIN dbo.users AS U
    ON U.userid = I.userid
WHERE
    I.idi = @idi;

IF @amount IS NOT NULL
BEGIN
    UPDATE dbo.users
    SET balance += @amount
    WHERE userid = @userid;
END;

COMMIT TRANSACTION;

You will need to add error handling to detect and resolve any deadlocks or write conflicts that may occur.

For more information, see my series of articles on SQL Server isolation levels:

SQL Server Isolation Levels : A Series


What is the difference between a union and an "isa" disjoint relationship?

A isa usually indicates a subset. e.g. a male cat is a cat is a mammal. A union is the combination of two sets e.g. the union between cats and males is all cats and all males. Union is bidirectional whereas isa goes in one direction.


Windows Server 2012 R2 Essentials and SQL Server 2014

I would ask your vendor why they can't install SQL Server on a domain controller because the documentation seems to indicate otherwise.

See the Installing SQL Server on a Domain Controller section of Hardware and Software Requirements for Installing SQL Server 2014

  For security reasons, we recommend that you do not install SQL Server
  2014 on a domain controller. SQL Server Setup will not block
  installation on a computer that is a domain controller, but the
  following limitations apply:

  You cannot run SQL Server services on a domain controller under a local service account.
  After SQL Server is installed on a computer, you cannot change the computer from a domain member to a domain controller. You must
  uninstall SQL Server before you change the host computer to a domain
  controller.
  After SQL Server is installed on a computer, you cannot change the computer from a domain controller to a domain member. You must
  uninstall SQL Server before you change the host computer to a domain
  member.
  SQL Server failover cluster instances are not supported where cluster nodes are domain controllers.
  SQL Server Setup cannot create security groups or provision SQL Server service accounts on a read-only domain controller. In this
  scenario, Setup will fail.

So there are a few limitations, but I don't think these will apply in your situation. Using a domain account instead of a local service account shouldn't be a problem and I don't think you will use a cluster if you only have one server. Your domain controller probably isn't read only either.

The process is also documented in the Autodesk Vault knowledge base so I suppose it's supported for at least some of their software.

Microvellum's documentation seems to suggest the installer will check and you must not install SQL Server on a domain controller, but in fact that will only generate a warning but still allow you to continue, and it will work as long as you don't try to use a built-in service account.

Windows 2012 R2 essentials is also a supported operating system if you look at the Operating System Requirements on that page as long as it's 64bit.

That being said. If your vendor insists, you don't need to reinstall the operating system to remove the domain controller role from the installed operating system. The steps to demote a domain controller are documented.

Be warned though, if your workstations use a domain account to log in to the network and you remove the domain controller, your users may lose access to their PC's and the network, so you should be very careful when considering that route.

Also note one of the limitations of installing SQL Server on a domain controller is that you can not demote or promote (i.e. remove the domain controller role, or make your server a domain controller) once SQL Server is installed on the machine. In order to do so you must first uninstall SQL Server and then reinstall it afterwards.

As a last note, if you really can't install SQL Server on your machine because it's a domain controller, and you still need the domain controller role for client authentication, you could consider running a virtual machine on your server and run SQL Server inside that VM.

You should always verify licensing with your vendor, but this seems to suggest it could be a viable option without having to purchase a new server or an additional license:Understanding Licensing for Windows Server 2012 R2 Essentials and the Windows Server Essentials Experience role

  The Hyper-V role and Hyper-V Manager console are now included with
  Windows Server 2012 R2 Essentials! The server licensing rights have
  been expanded, allowing you to install an instance of Essentials on
  your physical server to run the Hyper-V role (with none of the other
  roles and features of the Essentials Experience installed), and a
  second instance of Essentials as a virtual machine (VM) on that same
  server with all the Essentials Experience roles and features.


Using X at the end of a SELECT in SQL Server Script?

The "X" is an alias for the following portion:

Select distinct roleid
from UserAccounts
WHERE CustID = @custID

So you'd be able to use it as:

SELECT x.roleid
FROM
(    Select distinct roleid
from UserAccounts
WHERE CustID = @custID) x
where x.roleid >100

In the grand scheme of things, your query would look like:

 (select @invtid = coalesce(@invtid + ',',' ')+rtrim(x.RoleID)
 FROM (
    Select distinct roleid
    from UserAccounts
    WHERE CustID = @custID
     )x
 )

technically you can use anything as your Alias.  "X", usually a more descriptive alias works.  Something like "roles"


Use IN()

WHERE user_id IN ('bla','bla2', 'blaN')


What does "page written failed to compress" indicate?

WiredTiger's general strategy is to only compress pages on-disk where there is a storage benefit.

The "page written failed to compress" counter is incremented when compression was attempted but didn't result in an on-disk storage saving (so a page was stored uncompressed).

Another metric for pages that are stored uncompressed is "page written was too small to compress". In this case, the size of a page was considered too small to attempt compression.

The commonly expected case is that pages can be stored compressed, which increments the "compressed pages written" metric.

  Is it an error I should be concerned about with regards to data integrity and why does it happen?

These storage metrics are informational counters and expected behaviour (i.e. unrelated to data integrity).

There isn't any obvious actionable outcome based on these metrics, but if a collection wasn't getting much overall benefit from compression the counters might provide insight into how often pages aren't compressed (as compared to compressible pages that don't have a significant level of compression).


Should I create a new table for store information of an associated object with object need to create

If the number of questions for each topic on each course varies from one General Exam to another, then yes, you want a structure like this (which is pretty much what you have):

NumberQuestions:
GE_ID int FK
Course_ID int FK
Topic_ID int FK
MaxQuestions int


How to get the SQL-server login name of an integrated security account?

To get the AD groups with exsting logins on the instance that your login DC\Somebody belongs to

xp_logininfo

EXEC xp_logininfo 'DC\Somebody','all';

sys.login_token

SELECT DISTINCT lt.name
FROM sys.login_token lt
INNER JOIN  sys.server_principals  sp
ON lt.principal_id = sp.principal_id AND  sp.type != 'R'
WHERE lt.name NOT IN (SELECT name FROM sys.database_principals WHERE type = 'R' AND name IS NOT NULL)
AND sp.type != 'R';

An example to validate this for other logins with impersonation

EXECUTE AS LOGIN ='DOMAIN\a-rvertongen';

SELECT DISTINCT lt.name
FROM sys.login_token lt
INNER JOIN  sys.server_principals  sp
ON lt.principal_id = sp.principal_id
WHERE lt.name NOT IN (SELECT name FROM sys.database_principals WHERE type = 'R' AND name IS NOT NULL)
AND sp.type != 'R';

REVERT;

Result

name
DOMAIN\a-rvertongen
DOMAIN\SERVER_RO
DOMAIN\SERVER_sysadmin
BUILTIN\Administrators

  Also, what happens if multiple groups are added as sql-server logins,
  and I'm a member of them all, and then I login with integrated
  security.Which login will it take ?

Permissions for multiple AD groups

You will be granted the GRANT / DENY rights of each  AD Group.
DENY take precedence over GRANT permissions.

A more thorough write up in a previous answer by  @Max Vernon can be found here.


SQL Server tracks free space within data files and uses that when additional space is needed. Data files will grow only when there is not enough existing free space to satisfy allocation needs.  Shrinking is not necessary for this space to be reused and should be generally be avoided for the reasons you mentioned.

If you observe occasional file growth even though sufficient free space exists, this may be due to short term space needs. For example, rebuilding a clustered index will require about 120% of the table size as free space during the operation that is freed afterward.


Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box

For logins, this is better IMHO:

http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror

And for jobs, you don't need SSIS. Just highlight the Jobs node in Management Studio, click on Object Explorer Details (F7), in the right pane select all, right-click and script.

I'd do the logins first, in case any of the jobs involve any of the logins.


Tree structure hierarchical query sorting

Efficiently storing, processing and reporting on hierarchical data of mixed/unknown depth in SQL is quite a complex area: whole books (such as Joe Celko's "Trees and Hierarchies in SQL for Smarties") have been written on the subject.

What you have there is often called a "naive tree" (only the parent relationship is noted) which does not allow for easily performing such a sort, or for queries where you want all data from under a particular node. It is common to de-normalise the structure in these cases by adding a path element to each row, so you can perform the sort you are looking for by sorting by theis field (and you can perform "everything under no matter how deep" queries by using filters such as tree_path LIKE '/path/to/target/node/%' (or tree_path LIKE '/path/to/target/node%' if you want to include the node itself). This avoids any need for recursion or an arbitrarily long collection joins, though of course you have the extra work to do maintaining this path when the tree changes. The book I usually recommend to all DB people (devs and admins alike), "SQL Antipatterns", has a chapter on this which covers the basics of this and other alternatives quite clearly (so you don't need to try consume Calko's tome unless you have some more complex requirements!).

If you can't alter the structure then you might be able to produce the path as an output using a recursive CTE (Common Table Expression) - I'll not go into detail here as there are already many good examples out there (in the documentation and StackExchange questions such as https://stackoverflow.com/questions/3307480/postgresql-recursive-with).

If your tree has a fixed depth (all leaf nodes are the same number of levels below root) then a fixed set of joins will do the trick:

SELECT {stuff}
FROM   source_table t1
JOIN   source_table t2 ON t2.id=t1.parent
JOIN   source_table t3 ON t3.id=t2.parent
JOIN   source_table t4 ON t4.id=t3.parent
ORDER BY t1.name, t2.name, t3.name, t4.name

for three levels below your root(s). If your depth is not fixed then this would not work (well, it could be forced to up to an arbitrary depth but the joins and the ordering clause would be pretty hairy to design and inefficient to run) to you need to use the CTE approach or use a modified data structure.


In SQL Server would misc pages be pushed out when an object grows within a mixed extent?

It just jumps to a new, uniform extent.  There is no mechanism to try to convert the first mixed extent into individual uniform extents.

If I understand correctly, when the mixed extent has no data in it any longer, it will switch that extent to being unused.  Then it could be used for either a mixed extent or a uniform extent.


PostgreSQL function thinks table reference exists when it does not

Your issue is that you have defined a recursive function ... the function is calling itself.  The logic that exists in it currently would result in a stack dump with OUT OF MEMORY error (or something along those lines, at least) if it didn't give you the error of "table already exists" to begin with..

CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text)
 RETURNS void AS
$func$
BEGIN

--<snip stuff>

--Now populate `parts1` table with `parts` table's content...
SELECT f_copy_tbl('parts','parts1');  --- THIS IS THE OFFENDING LINE HERE .. REMOVE IT
INSERT INTO parts1 SELECT * FROM parts;

--<snip stuff>

END
$func$ LANGUAGE plpgsql VOLATILE;

Your order of SQL should be as such:

CREATE OR REPLACE FUNCTION my_func(param1 VARCHAR, param2 VARCHAR) RETURNS VOID AS $$
DECLARE
  var1 VARCHAR;
BEGIN
  -- DO STUFFS HERE FOR FUNCTION
END;
$$ LANGUAGE plpgsql;
-- THE ABOVE DEFINES THE FUNCTION

-- THE BELOW EXECUTES THE FUNCTION
SELECT my_func('val1', 'val2'); -- THIS ONE LINE WILL EXECUTE ALL SQL DEFINED INSIDE THE FUNCTION

The function definition is what is between the dollar quoting..


if I correct I understand You - Your problem is single row in result
You can use unnest function for return all values

EDIT Text of query after questions from author:

DO $$

DECLARE xml_string xml;

BEGIN
xml_string := XMLPARSE(DOCUMENT convert_from(pg_read_binary_file('MyData.xml'), 'UTF8'));

DROP TABLE IF EXISTS my;
CREATE TABLE my AS
SELECT
    unnest(xpath('//ID/text()', xml_string)) as ID,
    unnest(xpath('//Name/text()', xml_string)) as Name,
    unnest(xpath('//ID_CUSTOMER/text()', xml_string)) as ID_CUSTOMER;

END$$

SELECT * FROM my;

1   IT  1
1   IT  2
1   IT  3
1   IT  4
1   IT  5


List of open (uncommitted transactions) in DB2 LUW

Assuming you have a newer version of DB2 (10.1 or higher, although I want to say a few of these are in 9.7). You have the Monitor views and functions.

The MON_CURRENT_SQL view and the MON_GET_ACTIVITY table function look like they could possibly be what you are after.


How to understand "NULL NOT IN" when the set is empty?

Lets see what happens with these two conditions:

WHERE NULL IN (SELECT 1 FROM dual WHERE 1 = 0);

WHERE NULL NOT IN (SELECT 1 FROM dual WHERE 1 = 0);

since the subquery yields an empty table, we can write them in pseudocode:

WHERE NULL IN (<empty_table>);

WHERE NULL NOT IN (<empty_table>);

So the first asks whether some (NULL) value is IN an empty table. The result of such condition is always FALSE, independent on whether the value is null or not, since the table is empty.

Applying the same reasoning in the second condition, it is always TRUE, again independent of the value.

It's easy to confirm in Postgres what the result value of these conditions are, as we have a boolean type. See dbfiddle.uk, where the first shows f (FALSE) and the second shows t (TRUE).

As a result of the above, when you run your two queries, they become:

SELECT 1
FROM dual
WHERE FALSE ;

giving an empty result (correct, since the WHERE condition is FALSE)
and

SELECT 1
FROM dual
WHERE TRUE ;

giving one row (correct again).


Unable to find how a table is being populated

Index statistics should tell you when the table was last updated, so maybe it really has been just populated once. Of course you could for example add a trigger to the table to track of someone inserts / updates it, but don't forget to remove it later.


Fault tolerance for Database sharding and Database partitioning

Sharding is for write scaling.  And, yes, each shard has part of the data.  (It might have part of the biggest table.  Meanwhile, smaller tables might be manually kept in sync across the shards.  Or you might have other servers for other things.)
Replication (Master(s) + Replica(s)) has a complete copy of the data on each server.  One Master + one Replica provides some degree of HA, but the "failover" is likely to be manual.
Each shard could have a Replica for HA purposes.  Or you could use a cluster (InnoDB Cluster or Galera) for each shard.  That would give you a combination of read scaling, a little write scaling, and a lot of HA.
When using Master+Replica, all writes go to the Master.  "Critical reads" need to go to the Master, too.  Other reads can go to the Replica.  Since you can have an arbitrary number of replicas, reads can scale "infinitely".  (Booking.com had over 100 replicas a few years ago; I don't know how many today.)
A cluster has at least 3 nodes.  It can be one Master and the rest as replicas, or it can be all Masters.  With 3 (or more) they can vote on what to do if one machine goes dark.  Then those in the majority continue running until you can fix the broken node.
Of course, if you put the Master and all its replicas (or all cluster nodes) in a single datacenter, you run the risk of a failure of all of them.  (Yes, DCs do go down.)
PARTITIONing is rarely of any use.  (I list 4 use cases here:  http://mysql.rjweb.org/doc.php/partitionmaint )
(Please switch from the term "Slave" to "Replica".)


Is long running query always more heavy than short running query

Query can consume more server resources when executing faster.

For example, recreating index with SOTRT_IN_TEMPDB = ON impacts tempdb.

  This option increases the amount of temporary disk space that is used to create an index, the option could reduce the time that is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database.

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


msdb.dbo.sysjobs and last run date and time

The time is not stored as number of milliseconds.  It is a numeric representation of the actual time.  For instance, 12:06:59 is represented as 120659.  1:02:04 pm is presented as 130204.  1:23:45 am would be 12345.

Instead of calculating that all yourself, simply use the dbo.agent_datetime function.

Something like:

SELECT dbo.agent_datetime(last_run_date, last_run_time);

The above function uses this code to create a DATETIME value:

CONVERT(DATETIME,
      CONVERT(NVARCHAR(4),@date / 10000) + N'-' +
      CONVERT(NVARCHAR(2),(@date % 10000)/100)  + N'-' +
      CONVERT(NVARCHAR(2),@date % 100) + N' ' +
      CONVERT(NVARCHAR(2),@time / 10000) + N':' +
      CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +
      CONVERT(NVARCHAR(2),@time % 100),
120)

If you can stand adding a function to the msdb, you can create a table-valued-function that will perform very well, as:

CREATE FUNCTION dbo.get_agent_datetime(@date INT, @time INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
SELECT agent_date_time =
    CONVERT(DATETIME,
          CONVERT(NVARCHAR(4),@date / 10000) + N'-' +
          CONVERT(NVARCHAR(2),(@date % 10000)/100)  + N'-' +
          CONVERT(NVARCHAR(2),@date % 100) + N' ' +
          CONVERT(NVARCHAR(2),@time / 10000) + N':' +
          CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +
          CONVERT(NVARCHAR(2),@time % 100),
    120)
);

To get the last 7 days of Agent Job History, you'd use this:

SELECT JobName = sj.name
    , RunDate = dt.agent_date_time
    , sjh.*
FROM dbo.sysjobs sj
    INNER JOIN dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id
CROSS APPLY dbo.get_agent_datetime(sjh.run_date, sjh.run_time) dt
WHERE dt.agent_date_time >= DATEADD(DAY, -7, GETDATE());

Dates/times presented are stored in the local time of the server.


SQL Anywhere 12 - exporting data to Excel

The OUTPUT USING format of the statement is using a ODBC driver to create/send the data to.

This means you must have a ODBC driver on the server which can create XLS files.

There is a MS Office 2007 ODBC download available from microsoft which provides such a driver.

I don't know if there exist 64-Bit drivers (I think it must match with your dbserver 32/64bit) nor if Office 2010/2013/2016 ODBC drivers exist.

Docu output-statement


postgresql grant user privilages to dynamically created tables

Set your default privileges for the schema using ALTER DEFAULT PRIVILEGES


I was able to find the answer through this thread And this is because MySQL uses an authentication plugin. You can read all the details on the forum.


triggers - using the inserted / deleted tables in Dynamic SQL

Without more insight into the intended goal of this request, it certainly seems as though even with this immediate issue solved, the working code might not provide anything truly useful. Some concerns are:

Depending on how many tables this Trigger will be placed on and/or how frequent the DML operations are, this could cause minor performance issues in the database(s) where this Trigger is creating the tables because creating tables requires a Schema-Lock (I believe) and doing that too frequently might complicate some other operations.
If this Trigger will be placed on more than one Table, how will you distinguish the operations between the different Tables (unless giving them their own prefix for the dynamically created tables)?
Do you have an UpdatedDate or some date field in the table? If not, there is no sense of chronology without looking at the table creation date.
How do you plan to clean up all of these various tables? Maybe it would be best to create a Schema just to hold these tables?
Do you plan on indicating anywhere the DML operation that took place?
If you want to track the "before" and "after" values on an UPDATE, then you need to capture both inserted and deleted tables. But if they have GUID-based names, then you won't be able to correlate between the "inserted" and "deleted" copy tables for a particular UPDATE operation. You would have to re-use the same GUID value and denote the "insert" or "delete" in the table name prefix. If you weren't dynamically creating the table, then you could include a column specifying the DML action, dump both inserted and deleted tables into the already existing table, and just use a GUID or INT from a Sequence to correlate between 2 rows of the same UPDATE operation.
Depending on what version and edition of SQL Server you are using, you might want to look into Change Tracking and Change Data Capture.

However, all of that being said, the issue of interacting with the inserted and deleted tables via Dynamic SQL is an interesting problem. Unfortunately, it can't be done in T-SQL. So now it's also a challenge :-). Fortunately, this can actually be done. How so? With a little help from our friend, Mr SQLCLR.

Now, there doesn't seem to be a lot of situations that really demand, or even benefit from, SQLCLR Triggers. They seem to be the least useful things you can create with SQLCLR. However, here we have a scenario that they are a wonderful fit for. SQL submitted from SQLCLR code is Dynamic SQL. And SQLCLR Triggers have access to the inserted and deleted tables, so it would seem that SQLCLR Triggers can access the inserted and deleted tables in Dynamic SQL. Below is the code that does exactly that to accomplish this request (please note that the DB connection is using the in-process "Context Connection", so the Assembly can be marked with PERMISSION_SET = SAFE; no need for an Asymmetric Key or to set the database to TRUSTWORTHY ON):

Test table for the Trigger to be created on (if using Visual Studio / SSDT, the table definition has to be included in the project):

CREATE TABLE TableThatHasTriggers
(
   TableThatHasTriggersID INT IDENTITY(1, 1) NOT NULL
                          CONSTRAINT [PK_TableThatHasTriggers] PRIMARY KEY,
   InsertTime DATETIME NOT NULL
              CONSTRAINT [DF_TableThatHasTriggers_InsertTime] DEFAULT (GETDATE()),
   SomeValue NVARCHAR(50) COLLATE Latin1_General_100_CI_AS NULL
);

The SQLCLR C# code:

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class Triggers
{
    [SqlTrigger(Target = "TableThatHasTriggers", Event = "FOR INSERT, UPDATE")]
    public static void tr_TableThatHasTriggers_audit()
    {
        string _AuditSQL = @"
                SELECT ins.*
                INTO   dbo.[TMPIns_" + Guid.NewGuid().ToString().Replace("-", "") + @"]
                FROM   INSERTED ins;
";

        SqlConnection _Connection = new SqlConnection("Context Connection = true");
        SqlCommand _Command = _Connection.CreateCommand();
        _Command.CommandText = _AuditSQL;

        // SqlContext.Pipe.Send(_AuditSQL); // display query for debugging purposes ONLY

        try
        {
            _Connection.Open();
            _Command.ExecuteNonQuery();
        }
        finally
        {
            _Command.Dispose();
            _Connection.Dispose();
        }
    }
}

T-SQL wrapper object to place the SQLCLR Trigger onto the Table:

CREATE TRIGGER [dbo].[tr_TableThatHasTriggers_SQLCLRaudit]
  ON [dbo].[TableThatHasTriggers]
  AFTER INSERT, UPDATE
  AS EXTERNAL NAME
             [InsertedTableViaDynamicSQL].[Triggers].[tr_TableThatHasTriggers_SQLCLRaudit];


updatable entities in the design of databases

You can have two entities products_store and products_stock as depicted in your question.

Other Options

  Incase you just need a track of which product is moved

I think of having single Products table with a column name it as In_stock (can be a bit field) where you change it to false, when the product is moved to store.

In_stock value options

1- InStock

0- InStore

  Incase you need a track of DateTime when the product is moved to store.

I think of having single Products table with one column called Move_DateTime (default value NULL) to update the column when the product is moved to store from stock.

Move_DateTime value options

NULL - InStock

DateTimevalue -InStore

In one possible scenario you can go for choosing both the columns to be added in the table.


Update all values in a column with individual values

It should be this simple:

UPDATE u
SET u.P60_Date = DATEADD(DAY, -2, u.P60_Date)
FROM dbo.US_P60 AS u

If you want to be sure, you can do:

SELECT *
INTO dbo.US_P60_bk
FROM dbo.US_P60

Then validate the update is what you want. If it is, drop the _bk table.


SQL Server 2008 ignoring QUERYTRACEON 4136 and ARITHABORT ON

I tried to include most of the details in your other question but specific to this question arithabort and query trace are not solutions just tools to find problems.  You need to implement a fix once you find the problem.  Arithabort may work temporarily because it forces a new plan for a while but it might go right back to what it did before.

Option recompile is an okay bandaid but you are creating an overhead by getting a new plan every single execution and especially if your stored procedure is executed many many times this can create issues.  Setting a local parameter to the parameter ie- Set @name = @lname is an old trick to fix before sql server had an adequate solution and we found this method hit or miss.  Optimize for unknown the microsoft supported hint for parameter sniffing has had the best results and kept company critical processes from having issues.  It is just a hint that basically says to the engine be extra careful about changing plans we found a problem here.


mysql 5.7.9-log set session mode gives warning about NO_AUTO_CREATE_USER is deprecated

"Deprecated" means that the feature works as intended, but it is not recommended anymore because it will probably be disabled in a future realease. MySQL backwards compatibility works usually by deprecating, showing a warning on version X+1 (e.g. 5.7) an an error on the next one (e.g. 5.8). That way you have time to adapt your database and scripts.

In particular, your error is that you are not using NO_AUTO_CREATE_USER as the SQL Mode. In other words, NO_AUTO_CREATE_USER should be enabled by default and you should not change it (disable it) because the option will probably disappear in 5.8, being always enabled.

As a consequence, you cannot any more assume that GRANT creates a user automatically, and instead you should execute explicit CREATE USER statements. This is done because if you run:

CREATE USER root@'10.0.0.11' IDENTIFIED BY 'secret';
GRANT SELECT ON 'wordpress.%' TO root@'10.0.1.1';

You would not have created a user with a password and provided him with access to the wordpress database - you have created a user with a password, and then created another, different user without password with access to that database. That is a security issue so NO_AUTO_CREATE_USER is a very recommended configuration.

I would not recommend you to set the sql mode at session level, but if you do, write:

SET SESSION sql_mode="NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER";

or an ever more strict mode.

The reason why I do not recommend this is double:

You are disabling the stricter defaults (stric_trans_tables, etc.):

mysql> SET SESSION sql_mode=DEFAULT; SELECT @@sql_mode\G

Query OK, 0 rows affected (0.00 sec)

*************************** 1. row ***************************

@@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

1 row in set (0.00 sec)
This is unnecessary if you were able to do it globally (SET GLOBAL or on configuration file). Adding it on session level means you have an extra roundtrip to the database, adding latency. Executing one SET takes very little time, but if you do it on every single query/session, the time adds up. However, if you cannot change it at server level, the session change is the only way to do it.


Postgres to Postgres DBLink with AWS/RDS

You need to configure the remote server so that it demands a password when logging on as foouser to mwu.  Right now it is using one of the other authentication methods.


Will running pg_stat_reset cause performance issues?

pg_stat_reset will not affect query plans (it resets monitoring, not data-distribution, statistics), but it will cause autovacuum to think that no tables have recently been vacuumed, and so autovacuum activity will be elevated for a while. Whether this is a problem depends on the specifics of your data and how it's used.
This is a good article explaining the difference between the statistics used by the planner vs those that pg_stat_reset() affects, as well as how pg_stat_reset interacts with autovacuum:
https://blog.pgaddict.com/posts/the-two-kinds-of-stats-in-postgresql
I'd like to suggest an alternative, however: Just run CREATE TABLE statistics_snapshot_TODAYS_YYYYMMDD AS SELECT * FROM pg_stat_user_indexes (replace pg_stat_user_indexes with whatever you're using if you are using a different pg_stat_* view), and then, in a week, do join it with the view and do a subtraction. This will give you week-over-week deltas, without having to reset anything.
Sketch of the reporting query (not tested, but hopefully you get the idea):
SELECT
    stats_now.schemaname, stats_now.indexrelname,
    stats_now.idx_scan - stats_weekago.idx_scan idx_scan_delta,
    stats_now.idx_tup_read - stats_weekago.idx_tup_read idx_tup_read_delta,
    stats_now.idx_tup_fetch - stats_weekago.idx_tup_fetch idx_tup_fetch_delta
FROM pg_stat_user_indexes stats_now
JOIN statistics_snapshot_20201006 stats_weekago ON
    stats_now.indexrelid=stats_weekago.indexrelid;


How to creare a second login for a second database in Azure? How to deny a user access to one of the databases?

If you don't want the user to have access to the database at all make sure your user does not have system administrator privileges which would allow them access to every database. Baring that, on the database you don't want the 2nd user to have access to, run the following:

USE [1stDatabase]
DROP USER [2ndUser]
GO

To create an exclusive user for the second database:

USE MASTER
CREATE LOGIN [login_name here] WITH PASSWORD = '[password here]'
GO

USE [2ndDatabase]
CREATE USER [login name here] FOR LOGIN [login name here]
GO


How to make sqlplus prompt longer than 50 chars?

Finally, I found an answer!

The trick is, that the 50 char limit is for the string before the variable substitution! The size of the substituted string isn't limited. In my case, shorting _connect_identifier to a temporal variable _ci was enough:

DEFINE _ci=&_connect_identifier
SET SQLPROMPT "(long things)_user'@'_ci'(long things)"


The most likely answer to your question lies in this blog post from Paul White: Properly Persisted Computed Columns

The lack of optimizer cost model support means SQL Server assigns a small fixed cost to scalar computations, regardless of complexity or implementation. As a consequence, the server often decides to recompute a stored computed column value instead of reading the persisted or indexed value directly. This is particularly painful when the computed expression is expensive, for example when it involves calling a scalar user-defined function.

It sounds very much like SQL Server has decided that it would rather recompute the column value rather than read it from disk.
One solution, as mentioned by Paul, is to use trace flag 176 to disable computed column expansion.


SQL Server 2008R2 Database refresh in isolated environment

Just restore them over top.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY;

You may have to use the MOVE option if the folder paths for data/log files don't match, e.g.

RESTORE DATABASE db1 FROM DISK = '\\share\folder\file.bak'
  WITH REPLACE, RECOVERY,
  MOVE 'data_file_name' TO 'D:\SQLData\file.mdf',
  MOVE 'log_file_name'  TO 'E:\SQLLogs\file.ldf';

You can get the file names (and anticipate their sizes, too) from:

RESTORE FILELISTONLY FROM DISK = '\\share\folder\file.bak';


Detecting running pg_dump conflicting with schema changes

Unless somebody has overridden the application_name parameter for pg_dump, you could do this:
SELECT count(*) FROM pg_stat_activity
WHERE application_name = 'pg_dump';

If that gives you 0, no pg_dump is running.


The only way I have seen this work is by adding the double quotes inside the report yourself, but that can be a bit tedious if you have many textboxes and many reports.

Something like this should work though:

=IIF(Globals!RenderFormat.Name = "CSV",  """" & CStr(Fields!MyField.Value) & """", Fields!MyField.Value)

This code checks to see if it's being rendered to csv and adds the quotes if that's the case.


How to create SQL Agent job to automatically restore a database from latest backups?

Solution to the problem is described:
https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/


MySQL query INSERT INTO ... failed - The Table "x" is full

I don't think the issue is InnoDB table size limit at all.

I have posts about InnoDB Tables having a Table is Full Condition in the InnoDB Plumbing

May 31, 2013 : How to solve "The table ... is full" with "innodb_file_per_table"?
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full

My guess is that your InnoDB Log Files (ib_logfile0, ib_logfile1) are probably too small. (See my second post under CAVEAT #2)

Since you are using MySQL 5.1.73 you will have to increase innodb_log_file_size and innodb_log_buffer_size doing some manual labor. Here is how:

STEP 01 : Add these lines to my.cnf under the [mysqld] group header

[mysqld]
innodb_log_file_size=1G
innodb_log_buffer_size=64M

STEP 02: Login as root@localhost and run this command

SET GLOBAL innodb_fast_shutdown = 0;

STEP 03: Shutdown mysql

service mysql stop

STEP 04: Rename the log files

cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.old
mv ib_logfile1 ib_logfile1.old

STEP 05: Start mysql

service mysql start

This will regenerate new log files at 1G each.

Please, give it a try and see if it helps !!!


If the SA is building a new server then you best option is to do a migration not an upgrade (upgrades also do not have a good rollback plan and therefore is not recommended to do in production).

Here is a decent article with migration steps to start with but there may be adjustments for your environment. https://www.mssqltips.com/sqlservertip/1936/sql-server-database-migration-checklist/

Migrations should not be scary but they need everyone's involvement to make sure that if you do run into snags that they get fixed asap and hopefully documented for future reference.


Most efficient way to return multiple aggregates in a single stored proc?

COUNT..CASE is the usual way. For "Sent" though it's bit trickier because it's a different uid filter which requires 2 queries on the Message table

I've used this construct to allow for zero  row for to/from. It avoids an OR and adding a uid filter to each CASE.

Each derived table (can write as a CTE) will return only one row and I've used a MAX on uid to avoid GROUP BY too in case anyone wonders

SELECT
    ISNULL(M.Inbox, 0) AS Inbox,
    ISNULL(M.Unread, 0) AS Unread,
    ISNULL(M.Flagged, 0) AS Flagged,
    ISNULL(M.Drafts, 0) AS Drafts,
    ISNULL(S.Sent, 0) AS Sent,
    ISNULL(M.Archived, 0) AS Archived
FROM
    (
    SELECT
        COUNT(*) AS InBox,
        COUNT(CASE WHEN isread = 0 THEN 1 ELSE NULL END) Unread,
        COUNT(CASE WHEN isFlagged = 1 THEN 1 ELSE NULL END) Flagged,
        COUNT(CASE WHEN isDraft = 1 THEN 1 ELSE NULL END) Drafts,
        S.Sent,
        COUNT(CASE WHEN isArchived = 0 THEN 1 ELSE NULL END) Archived
    FROM
        Message
    WHERE
        [to] = [uid]
    ) M
    FULL OUTER JOIN
    (
    SELECT COUNT(*) AS Sent
    FROM Message
    WHERE [from] = [uid]
    ) S ON 1=1

Edit:

An aggregate without GROUP BY will always return a result (see SO here). I forgot that.

I've updated my SQL


Executing an oracle variable string as I do in SQL Server

In Oracle you use (for dynamic SQL):

BEGIN
    EXECUTE IMMEDIATE

Or

BEGIN
    DBMS_SQL.EXECUTE()

Like the examples here: https://www.guru99.com/dynamic-sql-pl-sql.html


This is documented behavior provided for backwards compatibility. Documentation excerpt:

  Caution A table-level DENY does not take precedence over a
  column-level GRANT. This inconsistency in the permissions hierarchy
  has been preserved for backward compatibility.


Check it here: http://rextester.com/QQZWX40863

CREATE TABLE #t1 (JoinFlag char(1), ReferenceType varchar(20), ReferenceValue varchar(50));
INSERT INTO #t1 VALUES ('A','CARRIER','Sample#######');
INSERT INTO #t1 VALUES ('A','LEASE_NUMBER','17438');
INSERT INTO #t1 VALUES ('A','LEASE_NUMBER','6054');
INSERT INTO #t1 VALUES ('A','STATION','ENERWST');
INSERT INTO #t1 VALUES ('B','LEASE_NUMBER','17704');
INSERT INTO #t1 VALUES ('B','LEASE_NUMBER','17711');
INSERT INTO #t1 VALUES ('B','STATION','RJ');
INSERT INTO #t1 VALUES ('B','STATION','C5');

SELECT DISTINCT
    t2.JoinFlag,
    t2.ReferenceType ,
    (SELECT STUFF((SELECT ',' + ReferenceValue
            FROM #t1
            WHERE #t1.JoinFlag = t2.JoinFlag and #t1.ReferenceType = t2.ReferenceType
            FOR XML PATH('')) ,1,1,''))
FROM
   #t1 AS t2;


The three of them are available with SharePoint standard. Just need to activate the content types (after a restart of SP services).


Trigger to prevent update if existing value is higher than inserted value

You are using INSTEAD OF UPDATE, this means that each update will end in the trigger.

You can add an ELSE statement to do the update if the first IF is not true.

CREATE TRIGGER [dbo].[Prices_InsteadUpdate_Trigger]
ON [dbo].[Prices]
INSTEAD OF UPDATE
AS

SET NOCOUNT ON

IF UPDATE(PRC_Value) AND EXISTS (SELECT * FROM deleted d JOIN inserted i ON d.PRC_PrcId=i.PRC_PrcId WHERE d.PRC_Value>i.PRC_Value)
SELECT NULL
ELSE
UPDATE P
SET PRC_Value = i.PRC_Value
FROM dbo.Prices P
INNER JOIN inserted i
ON i.PRC_PrcId = P.PRC_PrcId;

SET NOCOUNT OFF;

Testing

CREATE TABLE dbo.Prices(PRC_PrcId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                        PRC_Value numeric(5,2));

INSERT INTO dbo.Prices(PRC_Value)
VALUES(1.2)

GO

Lower Price

UPDATE dbo.Prices
SET PRC_Value = 0.5;

Result

(No column name)
NULL

Higher price

UPDATE dbo.Prices
SET PRC_Value = 1.5;

Result

PRC_PrcId   PRC_Value
1   1.50


I couldn't change root user password on mysql with --skip-grant-tables

First of all, when you start your server with the --skip-grant-tables it's recommended that you use also --skip-networking, so no one can connect from the outside.

Then, once you enter to mysql you should first do:

mysql> FLUSH PRIVILEGES;

And to chance your password you should use the ALTER USER instruction:

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('YourPass');

After that, disconnect again and restart your server, that should do it.


Timeout error after SQL Server upgrade from 2008R2 to SQL Server 2014

The Cardinality Estimation logic was updated for SQL Server 2014 and could potentially be a reason. You would have to test the same queries with the old cardinality estimator and compare performance metrics. You could do this by lowering the compatibility level to <120.

I would perform all this testing on a test server and not in production.

https://msdn.microsoft.com/en-us/library/dn600374(v=sql.120).aspx
https://www.brentozar.com/archive/2014/04/sql-2014-cardinality-estimator-eats-bad-tsql-breakfast/


How to UPDATE a column to SET previous row value to NULL cells?

Sample schema and data

create table tbl (
  id int,
  value varchar(10)
  );
insert into tbl select
  1,   'music' union all select
  2,   ''  union all select
  3,   'movie' union all select
  4,   '' union all select
  5,   '' union all select
  6,   'book' union all select
  7,   '' union all select
  8,   '';

Query (SQL Fiddle)

update tbl join (
    select curr.id, (
        select prior.value
          from tbl prior
         where prior.value > ''
           and prior.id < curr.id
      order by prior.id desc
       limit 1) value
      from tbl curr
    where ifnull(curr.value,'') = ''
  ) g on g.id = tbl.id
set tbl.value = g.value;

Results

select * from tbl

| ID | VALUE |
--------------
|  1 | music |
|  2 | music |
|  3 | movie |
|  4 | movie |
|  5 | movie |
|  6 |  book |
|  7 |  book |
|  8 |  book |


Fail to attach database : MSDTC Check status of transaction

This will mark all the MSDTC transactions as failed and allow you to start the database. It's a better idea to use backup/restore to refresh the database though

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'in-doubt xact resolution', 2 -– presume abort
GO
RECONFIGURE


Design with inheritance and two 1 to many relationships to the same table

I think you can (and should) simplify this. Class inheritance doesn't make as much sense in SQL (DDL) as it does in OO programming languages. Instead, you should follow normalization rules.

I'd suggest a solution like this:

CREATE TABLE media (
  media_id int unsigned auto_increment PRIMARY KEY,
  media_type enum('book', 'article', 'podcast', 'video') NOT NULL
  -- Other media columns here
  -- If you need a few columns specific to one media_type, that's OK
);

CREATE TABLE media_comment (
  media_comment_id int unsigned auto_increment PRIMARY KEY,
  media_id int unsigned NOT NULL,
  comment_type enum('question', 'note') NOT NULL,
  marker int unsigned COMMENT 'unit: seconds for video/podcast, page for book/article',
  comment_body TEXT NOT NULL,
  CONSTRAINT media_comment_fk1 FOREIGN KEY (media_id) REFERENCES media(media_id)
);

This has the advantage of significantly simplifying your queries as well as being easier to read / understand.

If you need a few columns specific to certain media types, that's OK. These columns will then be NULL for the other media types. The overhead of NULL values is very small, though varies with database system / storage engine. See e.g. here for an explanation of the InnoDB storage engine in MariaDB and MySQL: NULL in MySQL (Performance & Storage) (StackOverflow)


Restore database with different filename

you can create a table variable(or temp table) and insert all the .bak file metadata in that table and then fetch the row with max value of modified time.

DECLARE @FileMeta table (columns nvarchar(200));

INSERT INTO @FileMeta
EXEC sys.xp_cmdshell 'dir D:\FolderName\*.bak';

SELECT * FROM @FileMeta
WHERE columns not like '%volume%'
AND columns is not null and columns not like '%bytes%';

Now you can get the creation date of the file as shown in this link, and Now from this table @FileMeta You can get the row for Max of creation date that will give you the most recent file.


how to Import MySql Dump file in MySQLWorkBench

I guess you need to extract that particular table and then import in to the workbench.

sh mysqldumpsplitter.sh --source filename --extract TABLE --match_str table-name

This will extract your table in "out" folder and then you can import that to the workbench.

Source: MySQLDumpSplitter


PostgreSQL : Master-Slave replication strategy and solution for 3 servers.

You probably need 5+ servers for "real" high availability:

2x pgpool-II proxies (they send writes to master and reads to anyone)

3x postgresql servers (master, slave, witness)

Writes to master get sent to a slave via streaming replication

Use something like repmgr to do automatic failover

You're going to want to read this thoroughly: http://www.postgresql.org/docs/current/static/high-availability.html


Prepared statement execution with variable number of parameters to be bound

At least in this example, there's an easier approach than this. Remember that the optimizer is always trying to plan the query execution in a way that the path involving least amount of work necessary to retrieve a valid result set will be the path chosen.

SELECT * FROM t1 WHERE (col1 = 'foo') OR (1 = 1);

The server will always return all of the rows, because 1 = 1 contains all the truthiness required to conclude "yes, this row matches the WHERE clause"... and it will not bother scanning the values in col1 to see if they contain 'foo' because the optimizer has found a more obvious, simpler, "lower cost" approach... (any expression) OR (TRUE) is always true... and the shortest path to truth is the one the optimizer is supposed to find and take.  It doesn't need to evaluate whether 1 = 1 row-by-row, since that's a constant expression.

You don't even need a prepared statement, just a query in the procedure:

SELECT id, bar
  FROM table
 WHERE baz = mandatory
   AND (optional IS NULL OR field = optional)
   AND (optional2 IS NULL OR field2 = optional2);

If the program variable "optional" is null, the optimizer realizes that entire expression (in parentheses on that line) will be true for each row, no matter what, since, in the context of the running query, "optional" is a constant value, and optional is NULL is always going to be true, just like 1 = 1 is always true.  The optimizer realizes that this also is a constant expression.  The contents of "field" never need be evaluated, because the OR expression has been determined in advance to be true.  The same goes for optional2.

On the flip side, if "optional" is not null, then optional IS NULL gets optimized away, since it's impossible for that expression to be true, and you are left with AND (field = optional).  Same, again, for "optional2."

The optimizer is left, in each case, with query that can still use indexes as appropriate.

Since the query is in the procedure body, not a prepared statement, SQL injection is impossible since the server has no chance of blurring the distinction between data in variables and the literal query.


Why mongod.service is killed by my system?

Systemd is changing the PIDFile path at startup to /run/mongodb/mongod.pid.  However, the mongod doesn't consult the unit file, it uses its own config, usually /etc/mongod.conf.
Update both the unit file and the mongod.conf to use "/run/mongodb/mongod.pid" as the PIDFile.
Since mongod forks, systemd is expecting to see the pidfile created so that it knows the process has started.  That would use signal 15, it isn't the main problem, though.
Signal 6 occurs when the mongod itself calls abort.
The error calls out line 1598, which is:
Status WiredTigerRecordStore::updateRecord(OperationContext* opCtx,
                                           const RecordId& id,
                                           const char* data,
                                           int len) {
    dassert(opCtx->lockState()->isWriteLocked());
    invariant(opCtx->lockState()->inAWriteUnitOfWork() || opCtx->lockState()->isNoop());

    WiredTigerCursor curwrap(_uri, _tableId, true, opCtx);
    curwrap.assertInActiveTxn();
    WT_CURSOR* c = curwrap.get();
    invariant(c);
    setKey(c, id);
    int ret = wiredTigerPrepareConflictRetry(opCtx, [&] { return c->search(c); });
    invariantWTOK(ret);  <<--- Line 1598

The WiredTiger docs provide an description of WT_NOTFOUND:

#define WT_NOTFOUND
Item not found.
This error indicates an operation did not find a value to return. This includes cursor search and other operations where no record matched the cursor's search key such as WT_CURSOR::update or WT_CURSOR::remove.

The provided 3 log lines unfortunately don't give us any real clues as to what wasn't found.  It could have been a missing or corrupt data file, a damaged entry in the journal, or a truncated record.
What I suspect happened is the PIDFile mismatch led to systemd causing an unclean shutdown that left one or more files corrupt.
To fix this:

fix the PIDFile configuration in both the unit file and the mongod.conf so this doesn't happen again
make a backup of the dbpath
remove all of the files in the dbpath
attempt to start mongodb

If it is able to startup with the corrected PIDFile settings and a clean data directory, you can decide from there whether to restore the backup and attempt to repair the corrupt files, or just start over with an empty db.


At the risk of making an unpopular statement, I think that shrinking might be a good option for you here. Let me explain...

Shrinking a database data file will almost certainly cause fragmentation. This is critical in a production database, where you likely cannot afford the time to defrag the database after the shrink.

However, it sounds like you have some time up your sleeves. Perhaps you could restore the databases onto a non-production server where you will have ample time and resources to shrink and defrag the databases. Then you could migrate these databases to your SSD.

Of course this takes time. You might need to keep your log backups on production so you can roll forward changes, or possibly setup something like replication to keep your data in sync.

Will be interested in what other people think, and what you ultimately choose to do.


Is there any name for common sense / knowledge integrity constraints?

You're going beyond the scope of referential constraints with this (good) question. What you are looking for is more to do with Ontologies and Archetypes than RDBMS constraints. You could also check out this page which is on "Intelligent Databases" and gives as an example, medical records.

Check out the OpenEHR (Open Electronic Health Record here) whereby the system goes beyond data storage (essentially AIUI, your question). It's a fascinating area, touching on machine learning and artifical intelligence. It is far from a trivial problem - and in particular with medical records with all sorts of different regulatory frameworks and privacy concerns. Doctors, surgeons, dentists and pharmacists have different "views" of a patient - how to tease all these out is a major issue in the field. It will come as no surprise that the OpenEHR system is one of many competing in this fascinating and challenging area.

From the OpenEHR (what is page)

  What is openEHR?

  Abstract architecture image openEHR is a virtual community working on
  interoperability and computability in e-health. Its main focus is
  electronic patient records (EHRs) and systems.

  The openEHR Foundation has published a set of specifications defining
  a health information reference model, a language for building
  'clinical models', or archetypes, which are separate from the
  software, and a query language. The architecture is designed to make
  use of external health terminologies, such as SNOMED CT, LOINC and
  ICDx. Components and systems conforming to openEHR are 'open' in terms
  of data (they obey the published openEHR XML Schemas), models (they
  are driven by archetypes, written in the published ADL formalism) and
  APIs. They share the key openEHR innovation of adaptability, due to
  the archetypes being external to the software, and significant parts
  of the software being machine-derived from the archetypes.

  The essential outcome is systems and tools for computing with health
  information at a semantic level, thus enabling true analytic functions
  like decision support, and research querying.


pg_relation_size does not show any difference after VACUUM ANALYZE

VACUUM does not necessarily make unused space available to the filesystem, it merely makes the blocks re-usable for further INSERTs (or UPDATEs).

If I'm not mistaken the only way to also actively reduce the size on the file system would be a VACUUM FULL, but beware that needs an exclusive lock on the table(s).

Do you expect the table to not get any new rows or updates? Because if it will get new rows, then it doesn't really make sense to try to shrink the files phyiscally now if they are going to be increased again later due to new rows arriving.


Give particular privileges for a table to existing role

Use GRANT.

GRANT INSERT, UPDATE, DELETE, TRUNCATE, TRIGGER ON table A TO monitoring;

The role needs privileges for the DB and the schema, too, but since it already has the SELECT privilege, that should be the case. And if you want to keep SELECT and REFERENCES in the mix, you can just:

GRANT ALL ON table A TO monitoring;

But only superuser or the owner of a table can ALTER TABLE. The manual:

  You must own the table to use ALTER TABLE.

You would have to make the role monitoring the owner. (Do you really want that??)

ALTER TABLE A OWNER TO monitoring;

Related:

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?


Why does MySQLTuner only see 4Gb of RAM?

It is not a MySQL thing - MySQLTuner itself cannot see more memory if it is running with 32bit perl interpreter.


Okay to run sp_refreshview against all views on a regular basis?

there's potential for the underlying entities they reference to change

This is the problem, and you should solve it at the source.

If you are updating objects in the database frequently enough that you have to automate frequent metadata corrections, there is probably an opportunity for better processes.

And you shouldn't be updating tables without also updating the views that reference them. So the views should become part of your schema modification workflow.

You can use WITH SCHEMABINDING on the views, which will explicitly enforce  that you can't modify referenced objects without knowingly removing the schema binding. (As an added bonus, your views can't use SELECT *, which is likely part of the problem here).

I wrote about this in the following article:

Benefits of SCHEMABINDING


what files should I exclude/add/copy mysql dbs (without mysqldump)

You cannot exclude the ib_logfile* files, if those aren't present, your data will be at best damaged, unless you are running with innodb_fast_shutdown=0 - which can make stopping the service take a very long time.

The only things that are safe to omit are the relay logs and binary logs. You will have to check what the names of those are set to in your config file.


At the outermost level, you are returning the average values along with pr.pid. If you want averages per pid, then you need to group your rows by pid.

You are doing that in the nested queries but not in the main one. So, after on tr.pid=pr.pid just add group by pr.pid.

But if you want the average value across the entire row set, remove pr.pid from the (main) select list.


Grant permissions on views, deny select on tables

You will have to treat the view as a table. The information_schema already does

If you run

SELECT table_name FROM information_schema.tables
WHERE engine IS NULL;

you get all the views.

Just grant SELECT on the view to the user as follows

GRANT SELECT ON `myDatabase`.`fordibenForYouTable` TO 'thisUser'@'localhost' ;

Once you do this, you should have SELECT access to the table.

To make sure, run SHOW GRANTS FOR 'thisUser'@'localhost';

You should also be able to see what table-level access is granted to 'thisUser'@'localhost'

SELECT * FROM mysql.tables_priv
WHERE user='thisUser' and host='localhost'\G

You can also see what user has table-level access to myDatabase.fordibenForYouTable

SELECT * FROM mysql.tables_priv
WHERE db='myDatabase' and table_name='fordibenForYouTable'\G

GIVE IT A TRY !!!


Two primary keys but not a composite, what is this sort of relationship called?

It's called a composite key.

It doesn't matter how you use a composite key. If two pieces of data uniquely identify a row, it's called a composite key. In fact, the use case you're defining is almost always the use case of a composite key.

They're usually natural keys (having both fields related to the data), rather than surrogate keys (having both fields independent and abstract from the data). A store location, and product ID is the canonical example of a natural composite key. You're just using employees.

If, by chance, you mean to ask if both inputs used in the composite key have a name when they have a UNIQUE property, then no.. You can do it, but even in your case it's a bit awkward right? I mean, what if one employee is managing two spaces?

CREATE TABLE em ( emid serial PRIMARY KEY, name text, position text );
CREATE TABLE space ( spaceid serial PRIMARY KEY, owner text );
CREATE TABLE em_space (
  emid    int REFERENCES em,
  spaceid int REFERENCES space,
  PRIMARY KEY (emid, spaceid)
);


Is it safe to have Mongo replicas on single server?

Replication is a risk mitigation strategy, amongst other purposes. If you understand those risks, and the consequences should they eventuate, and that's all acceptable in your context then by all means run multiple replicas on the same hardware.
For a dev environment I can imagine this would be OK. A hardware failure would affect one person only. Recovery is likely quick. Replication can be tested at minimal cost.
For production, however, it would be much riskier. Hardware failure brings down the whole service. Recovery requires more, and more invasive, intervention. Resuming normal service may take longer and violate RTO.
My reading of the paragraph quoted in the question is that performance will not be affected by having multiple processes on the the same VM host, as long as those VMs are appropriately configured. I don't read it as a recommendation but as "you can do it this way if you like at it won't necessarily break." My point is that an application architect should also consider availability. That is covered in a separate paragraph

For availability, multiple members of the same replica set should not be co-located on the same physical hardware.


Try this:

CREATE OR REPLACE FUNCTION resize_img (i_ID INTEGER)
   RETURN BLOB
IS
   vImageData     BLOB;
   vSizedImage BLOB;

BEGIN

  SELECT img_simbolo_op
         INTO vImageData
         FROM tbl_yourtable
        WHERE my_ID = i_ID;

  DBMS_Lob.createTemporary(vSizedImage, FALSE, DBMS_LOB.CALL);

  ORDSYS.OrdImage.processCopy(vImageData, 'maxScale=75 75', vSizedImage);

  return vSizedImage;

END resize_img;


There is a point where the queries using the index are accessing enough partitions that a local index will be slower than a global index.

For example, if your table is partitioned by date, but you are querying only on a status column.


Accurate PG database size for comparing representations

Just call pg_database_size(dbname) to know the size of the database.

VACUUM (without the FULL clause) does not free any space, it only marks it as reusable, and thus will not change the database's size (except in a rare boundary case, see Routine Vacuuming).

ANALYZE does statistical sampling and would be useful if you needed the row counts, but for the global db size, it's not necessary.


Connections left in a connection pool will show up as "Sleep" in PROCESSLIST.  The "Time" will continue to grow until grabbed by a new "connect".


Find database user name from sysprocesses

Logins (on the server) do not automatically correspond to users (in the database). For instance, "sa" does not have its own user in the database, but uses "dbo". This might get you started.

SELECT s.session_id,
       sp.[type], sp.type_desc,
       sp.[name] AS loginName, dp.[name] AS userName,
       sp.is_disabled
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.database_principals AS dp ON s.security_id=dp.[sid]
LEFT JOIN sys.server_principals AS sp ON s.security_id=sp.[sid];

Note: sysprocesses, sysusers, etc are deprecated and will probably stop working in a future version of SQL Server.

I go into a bit more detail on SQL Server security in my blog post.


Very tricky: Can't update a sequential list SQL-92 ( Filemaker)

Here is another approach, which, however, is likely to perform worse than the other suggestion:

UPDATE
  Beleg
SET
  SortID = (
    SELECT
      COUNT(*)
    FROM
      Beleg AS Beleg2
    WHERE
      Year ( Beleg2.Valuta ) = Year ( Beleg.Valuta )
      AND Beleg2.Ursprungskonto = Beleg.Ursprungskonto
      AND Beleg2.SortID <= Beleg.SortID
  )
WHERE
  Year ( Valuta ) = 2016
  AND Ursprungskonto = 1210
;

On the plus side, not only does this method reset SortID values in a single step, it can also do that for multiple (Year(Valuta), Ursprungskonto) subsets at once, if need be. That is, you can specify multiple values for either attribute in the main WHERE clause, e.g. like this:

WHERE
  Year ( Valuta ) BETWEEN 2011 AND 2016
  AND Ursprungskonto IN (1210, 2101, 1012)

or even just remove the WHERE clause altogether – and each subset will be re-numerated separately from the others, all with the single query.

Important note: this method assumes that SortID values are unique across any single (Year(Valuta), Ursprungskonto) subset.


Separate login for user / administrators using mongodb

You should not have two collections, that will give you headache.

Have a user collection which has admin and regular users.  You can then query users as a whole or look for a given type only. So if you are looking for user id x you don't have two search two collections.

As for the question on duplicate ids with 2 collections, it simply depends how the ids are generated.  You say your ORM does it... so you will need to check the doc on how it does it.


Delete/remove orphaned users and their schema from server/database

To find out the orphan users of a database say "ABC", you need to query below on that database:i.e

USE ABC

sp_change_users_login @Action='Report'

This will list out all the orphan database users for that database. In order to resolve the orphan users, use sp_change_users_login, with query below

USE ABC

sp_change_users_login
    @Action='update_one',
    @UserNamePattern='database_user_name_comes_here',
    @LoginName='login_name_comes_here'

Also, suggested reads: How To Avoid Orphaned Database Users with SQL Server Authentication and Fixing Orphan users


You should change double-quotes by single-quotes.

'.', ':'

CREATE TABLE MyTable(EXP_DATE varchar(26));
INSERT INTO MyTable (EXP_DATE ) VALUES ('2011-05-30-13.06.56.824000');
GO

1 rows affected

SELECT CONVERT(datetime2,
               CONCAT(LEFT (EXP_DATE, 10), ' ',
                      REPLACE (SUBSTRING (EXP_DATE, 12, 8), '.', ':'),
                      RIGHT (EXP_DATE, 7)),
               21)
FROM MyTable;
GO

| (No column name)    |
| :------------------ |
| 30/05/2011 13:06:56 |

dbfiddle here


If you have say a Word or Excel document, then these have documents well-known properties such as Author, Title, Keywords etc which you can associate with a search property list and then a full-text index.  Properties available depending on their IFilter.  You can use the PROPERTY keyword to query these properties.

However with an .xml file, it's unlikely the document has these custom properties, and what you really want to do is search the xml content.  AccountNo sounds like just an attribute in the xml rather than a document property.  You can search your xml using ordinary full-text search methods, however xml markup and attribute names are ignored in full-text indexes.  You can query attribute and element content.  You could also cast the file content to xml and use the built-in xml methods eg .query, .nodes, .value and .exist.  Work through this demo and see if it makes sense:

USE master
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'filestreamDemo' )
BEGIN
    ALTER DATABASE filestreamDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE filestreamDemo
END
GO

CREATE DATABASE filestreamDemo
ON PRIMARY (name = filestreamDemo_File, filename = N'c:\temp\filestreamDemo.mdf' ),
FILEGROUP FG_filestreamDemo CONTAINS FILESTREAM ( NAME = filestreamDemo_FS_File, FILENAME = N'c:\temp\filestreamDemo_FS' )
WITH FILESTREAM (
    non_transacted_access = FULL,
    directory_name = N'filestreamDemo' )
GO

USE filestreamDemo
GO

-- DROP TABLE dbo.AuditTable
IF OBJECT_ID('dbo.AuditTable') IS NULL
CREATE TABLE dbo.AuditTable
(
    rowId           INT IDENTITY CONSTRAINT PK_AuditTable PRIMARY KEY,
    Id              UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
    [message]       VARBINARY(MAX) FILESTREAM NULL,
    fileExt         VARCHAR(5) NOT NULL
)
GO

IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ft' )
CREATE FULLTEXT CATALOG ft AS DEFAULT
GO

CREATE FULLTEXT INDEX ON dbo.AuditTable
    ( [message] TYPE COLUMN fileExt )
    KEY INDEX PK_AuditTable ON ft
    WITH ( CHANGE_TRACKING = MANUAL, STOPLIST = SYSTEM )
GO

INSERT INTO dbo.AuditTable ( Id, [message], fileExt )
SELECT
    NEWID(),
    CAST( '<root AccountNo="123456"/>' AS VARBINARY(MAX) ),
    'xml'

UNION ALL

SELECT
    NEWID(),
    CAST( '<root><child AccountNo="123456"/>elementValue</root>' AS VARBINARY(MAX) ),
    'xml'

UNION ALL

SELECT
    NEWID(),
    CAST( '<dummy/>' AS VARBINARY(MAX) ),
    'xml'
GO

ALTER FULLTEXT INDEX ON dbo.AuditTable START FULL POPULATION;
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

DECLARE @i INT = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN
    WAITFOR DELAY '00:00:05'

    SET @i += 1
    IF @i > 60 BEGIN THROW 50001, 'Too many loops!', 1 BREAK END

END
GO

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO

-- Have a look in the full-text index
SELECT *
FROM sys.dm_fts_index_keywords_by_document(DB_ID(), OBJECT_ID('dbo.AuditTable'))
ORDER BY document_id

-- Search using full-text; WON'T WORK
SELECT [message], CAST( [message] AS VARCHAR(MAX) )
FROM AuditTable
WHERE CONTAINS ( [message], 'AccountNo');

-- will work
SELECT [message], CAST( [message] AS VARCHAR(MAX) )
FROM AuditTable
WHERE CONTAINS ( [message], '123456');

-- will work: numbers are prefixed with nn in full-text indexes
SELECT [message], CAST( [message] AS VARCHAR(MAX) )
FROM AuditTable
WHERE CONTAINS ( [message], 'nn123456');

-- Cast to xml and search using normal xml methods
SELECT *
FROM ( SELECT rowId, ID, CAST( [message] AS XML ) [message] FROM dbo.AuditTable ) a
WHERE a.message.exist('//@AccountNo[.="123456"]') = 1
GO

I also did a test and could not get an xml doc to emit any properties, as suggested by the documentation.  I could however get this to work with a standard Word document.


new Neo4j install: "No authorization header supplied" even after editing config file

You restart Neo4j by calling /path/to/neo/bin/neo4j restart
or by  /etc/init.d/neo4j restart if you installed it as a service.

The neo4j startup script is in the same path as the shell.


performance issues when concurrent requests

Looking at the query plans, the query optimizer is pretty much begging you to add useful indexes to the tables. Until the query plans are robust, forget about looking at locks and I/O.

To make solid suggestions for indexes, you pretty much need to give us a picture of the schema, all the queries, and all existing indexes on the tables. I would not take the missing index suggestions exactly as they are presented. They undoubtedly will help, but they may not be the best thing to do (the suggestions aren't usually... intelligent... for lack of a better word).

The parallelism operators mean that the query used multiple CPUs to process the query in parallel. This is only desirable when you absolutely need SQL Server to do a bunch of work -- in this case, the problem is that you're asking SQL Server to do too much work and it's doing the best it can to satisfy the query in the shortest possible time.

With a small number of rows being returned from each query with what appears to be highly selective predicates, I would expect these queries to fly with useful indexes applied (and no parallelism would be needed).

Just in the visible area, it looks like queries 9 and 10 are virtually the same for ~35% total cost apiece; relatively speaking, the table scan of Transactions should go to pretty much zero. Depending on what you're doing, you may be able to combine those two queries into one.

If that's not enough speed, there are other techniques involving precomputed aggregates, but if you're kinda-sorta-almost okay with 500ms, then you won't need to go this far with just a robust set of base query plans. If all the queries are highly selective, indexes alone should easily get you into the < 5 ms range.

Finally, consider adding useful indexes to your temp tables. A nested loops join with a table scan on the bottom input can be quite expensive depending on the data involved.


Detach SQL Server database without deleting it?

Detaching a DB will remove it's entry from the internal list of DBs managed by Master but will leave the file there to re-attach.  This often could be perhaps not the best course of action to take but to do it follow the MS docs for detach a database.

You could execute the following but it could fail if users are connected to the database in your instance:

EXEC sp_detach_db 'Databasename', 'true';

You could always run a quick sp_who2 to see who is connected to your database or a more thorough:

select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = 'databasename'

As BradC has answered, taking a database 'offline' is also a good option which has it's own benefit and drawback.  Detaching unlocks the file so you could have the file manipulated while it it detached.  Just note having a database 'offline' can cause your scripts or maintenance tasks that scan all your databases to report failures.  It will still 'see' the database, try to access it, but it will not be able to.  Typically most maintenance scripts I write have logic to handle this but many in the wild do not so just note that.


Performance is only one of several benefits that can be gained from partitioning.  The benefits from partitioning are typically realized from partition elimination, where the query engine can rapidly narrow down the data set you're querying against.  This allows SQL Server to read in and operate on just a specific portion of the data, greatly reducing work time and I/O.  However, as discussed in the linked post, this relies heavily on your how your query is written and whether or not the index it is using can leverage the partitioning scheme you have in place.

It's partition elimination that also allows you to leverage the benefits of placing partitions on different disk systems.  The idea is fairly simple: if you can place partitions that are more actively queried against on faster disk, you can help your query out even more because the subset of data it's accessing can be retrieved faster.  At the core, though, the concept remains the same.  The reason you're increasing query performance is because you're helping the engine reduce the amount of data it is working with.

Two other benefits of partitioning include the ability to rapidly move data in and out of table using SWITCH and reducing index rebuild time by rebuilding indexes by partition.  The first is something very specific and can only be used when properly designed, but can be very useful on extremely large data sets.  The second is actually one of the main reasons I will typically leverage partitioning, as maintaining indexes on large tables can be very consuming, both from a resource and time perspective.

That being said, 250 million rows is not typically considered a large data set.  SQL Server can easily handle tables of this size and greater.  When I consider partitioning, I try to think more in terms of data volume.  I don't usually think about partitioning until my table is over 250 GB.  This isn't to say that your table doesn't fit this profile if your rows are very wide, but chances are you're not anywhere near that threshold.  You might want to consider other options to increase your performance.  Usually this means proper indexes.  A quick win, if you're using Enterprise Edition, could be to instead use data compression, which will reduce your I/O to disk and can be a nice boost to performance.


Performance after creating new MySQL server

There are two things you can look into:

Query cache
Buffer pool

Query cache will cache query results (depending on QCache configuration). When the same query is run again, results are returned from the cache instead of MySQL fetching the data directly from the tables. If the Query Cache is full and a new query is executed that needs caching, then the least used Query results are flushed from the Cache to make space for the new query results and so on.

Buffer Pool: InnoDB maintains a storage area called the buffer pool for caching table data and indexes in memory. So if your tables use InnoDB storage engine (or XtraDB for that matter), then the query execution times will improve over a period of time as the Buffer Pool becomes "warm".

Having said that, if your queries are taking too long to run in the first instance, then they may be a candidate for tuning. If you can post results of:

SHOW CREATE table <table name> for the tables involved in the query
the query that is slow

then that will provide a ground for looking into possible improvements to the query and/or indexes that may need to be created/modified.


Insert INTO a table combining * with a column value

You can do it this way
$pr1 = "INSERT INTO B SELECT *, 0 col10 FROM A where A.id > $limitid"

But this code is vulnerable to sql injection  so use only prepared statemenst with parameters see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php


DWs normally go without FKs, since there's normally no DML apart from loading the data into its tables.  You want this to go as fast as possible, while the integrity should be guaranteed by your sources.


SpringData MongoDB - infinite getmore on mongo logs

The lines mean that each [getMore][1] (essentially each iteration on the cursor) is returning 16 documents and that each one is 4MB (the default max batch size limit).  Essentially it is taking a little over 100ms to fetch the documents and return them, and they seem to be around 250KB each.

Note that the queries are yielding the lock, so they are likely not in memory and are being paged off disk (this is not the only reason that it might yield but it is the most likely).

Here's a quick breakdown of the interesting fields:

nreturned:16 = number of documents returned by the getmore
numYields: 4 = number of times that getmore yielded the lock
locks(micros) r:240837 = the time in microseconds that the read lock was held in total (r), which can be greater than the total execution time
reslen:4265572 = the size of the result in bytes (~4MB)
135ms = the time to complete the getmore operation (100ms is the default threshold for logging slow queries)

If I were to hazard a guess I would think that the { $gte: 5, $lte: 10 } matches an awful lot more data than the { $gte: 10, $lte: 1000000 } criteria, and that all of that data is not fitting into memory, but it is hard to be sure with such a limited snapshot of the activity.


PowerBI Service Account granting duplicate execute permissions on stored procedure almost constantly

I have worked out what is happening here.  tl/dr - There was a missing batch separator between a stored procedure and the associated GRANT statement.
If I run:
SELECT *
FROM sys.sql_modules
WHERE definition LIKE '%GRANT EXECUTE ON \[dbo\].\[ExtendEditSessionLifetime\] TO RSExecRole%' ESCAPE '\'

I can see stored procedures that have that grant statement within them.
This returned one record and the definition field begins
CREATE PROC [dbo].[ExtendEditSessionLifetime]      @EditSessionID varchar(32)......

If I navigate to this stored procedure in the object tree and right click and script it out, I can see text of the stored procedure:
CREATE PROC [dbo].[ExtendEditSessionLifetime]
    -- params
AS
BEGIN
    --code
END

GRANT EXECUTE ON [dbo].[ExtendEditSessionLifetime] TO RSExecRole
GO

I wasn't aware of this but it seems that when you create a stored procedure, you can place a GRANT after the END and it becomes part of the definition (I thought it had to be part of the BEGIN .... END construct (when there is one)
Therefore, every time ExtendEditSessionLifetime is run, it is re-granting permissions to RSExecRole.


AG of 2 replicas on 2 nodes in WSFC : is it possible to add 3rd replica to AG and NOT add 3rd machine WSFC?

Is it possible to add a 3rd replica (SQL Server 2017 on Windows Server 2016) to existing availability group, without adding a 3rd machine to existing WSFC ?

No, there are explicit checks to see if the node of an AG is already hosting the same AG. If you want a 3rd replica, add a 3rd note.

I'd also advise against stacking up on multiple instances of SQL Server on the same machine, simply for administrative and troubleshooting purposes.


Deduplicate Data Using Having with Subquery

There are restrictions regarding self-joins in MySQL's implementation of UPDATE and DELETE. The issue is usually solvable by using joins:

DELETE g.*
FROM game AS g
  JOIN
   ( SELECT matchid, ordinal, min(id) AS id
     FROM game
     GROUP BY matchid, ordinal
     -- HAVING count(*) > 1
   ) AS d
   ON  g.matchid = d.matchid
   AND g.ordinal = d.ordinal
   AND g.id > d.id ;

Test at dbfiddle.uk.

Another way would be using a LEFT JOIN / IS NULL construction:

DELETE g.*
FROM game AS g
  LEFT JOIN
   ( SELECT min(id) AS id
     FROM game
     GROUP BY matchid, ordinal
   ) AS d
   ON  g.id = d.id
WHERE d.id IS NULL ;

or an EXISTS subquery rewritten as a JOIN
(delete all games that there exists another game with same matchid, ordinal and smaller id):

DELETE g.*
FROM game AS g
  JOIN
     game AS d
   ON  g.matchid = d.matchid
   AND g.ordinal = d.ordinal
   AND g.id > d.id
 ;


SQL Server - Automatically full backup for new databases which are auto-created with EntityFramework-Migrations

my suggestion is to replace your script with one that is using ola hallengren backup solution with the option ChangeBackupType='Y'
https://ola.hallengren.com/sql-server-backup.html

ChangeBackupType
Blockquote

Change the backup type if a differential or
transaction-log backup cannot be performed.
DatabaseBackup checks differential_base_lsn in
sys.master_files to determine whether a differential backup can be
performed. If a differential backup is not possible, then the database
is skipped by default. Alternatively, you can set ChangeBackupType to
Y to have a full backup performed instead.
DatabaseBackup checks last_log_backup_lsn in
sys.database_recovery_status to determine whether a transaction log
backup in full or bulk-logged recovery model can be performed. If a
transaction log backup is not possible, then the database is skipped
by default. Alternatively, you can set ChangeBackupType to Y to have a
differential or full backup performed instead.

Basically, if you have this set to Y, then when the scheduled tlog for all user_databases runs, then the script will take a full backup instead of just a tlog.


What is the maximum number of parameters I can pass using the SQL IN clause in SQL server 2008 and SQL server 2000?

There isn't a quantity limit on the number of items in the IN clause.  The only limit is that the entire batch cannot be more than the batch size maximum.  In SQL 2008, this is 65,536 * Network Packet Size. In SQL 2000, I'm not sure if the limit was smaller than that.

This link describes these limits for 2008


Postgres deadlock in INSERT VALUES concurrent with ON DELETE CASCADE

I would not worry about the behavior, just retry the transaction if terminated by the deadlock.

The first transaction tries to delete A and B, and acquires sequentially a lock on them, and the second deletes B and A, with the same approach. Upon locking A and B, none of the two transactions can progress, hence one of them is terminated by a deadlock.


Had similar issue, for me it got resolved by installing .NET 3.5 and 4.5


Try switching the index to be:

KEY `new_places_on_wishlist` (`wishlist_id`, `id`, `place_id`)

You are effectively doing the part of order by optimization manual that says it can't use the index because of ordering by non-consecutive parts of the key, although their example is different than your situation.

Basically you are currently doing

SELECT key_part2 FROM foo WHERE key_part1=constant ORDER BY key_part3


Migration of Always On Availability Group from old to new SAN within same data center

Method 4

Depending on the size (and contents) of the data store, if you connect your new SAN to your VMWare infrastructure, you can storage vMotion in addition to moving the guest itself.

If you have large databases in the Availability Group, I'd recommend a modification of Tony Hinkle's answer under Method 1.

Remove secondary replica (call it "B") from the AG
Stop the "B" guest
Storage vMotion "B"'s data store to the new SAN
Bring "B" guest online
Rejoin "B" to Availability Group
Manually failover the Availability Group to "B"
Remove the secondary (former primary, call it "A") from Availability Group
Stop the "A" guest
Storage vMotion "A"'s data store to the new SAN
Bring "A" guest online.
Rejoin "A" to Availability Group
Test with a failover to "A"

Note that depending on how long this takes, you may have to apply any log backups (WITH NORECOVERY, remember) taken during the storage vMotion process on the primary (either time) as part of steps 5 and 11.


Any disadvantages of "SQL_BUFFER_RESULT" for MySQL SELECT?

SQL_BUFFER_RESULT creates a temporary table on the server for every result set.  This is not a temporary table like CREATE TEMPORARY TABLE - it is an implicit temporary table as would be created when using a GROUP BY clause or with a subquery.  As such, all the same rules apply.

First, let's talk the problem that SQL_BUFFER_RESULT is designed to solve:

When the client requests data from the server, until the entire result set has been transmitted to the client the query is still "running" and some locks may still be held.  While the data is being transmitted, it will appear in the Sending data state.  It is library dependent on whether the client just gets all the data at once when the query is executed, or if it trickles in as you select rows, but an example of a problem is as follows:

resultset = conn["SELECT * FROM bigtable"]
resultset.each do |row|
  data[:value] = row[:value]
  sleep 10 # do something expensive here
end

Obviously contrived, but in the case above if there are 1000 rows, the query will still be actively running for 10,000 seconds.  That may seem far fetched, but a lot of applications have "think time" in-between fetching each row because they do some processing.  This is "a very bad thing to do."  Another case where this type of trickle effect could occur is with a large result set over a slow connection.  Ultimately, the problem is the trickling of data to the client causing the query to remain in an active state.  SQL_BUFFER_RESULT resolves this problem by buffering the result into a temporary table first, which makes the query end faster, thus releasing all of its locks (what locks?).  The result set is then fed to the client from the temporary table rather than the query itself.

That sounds great!

But...

Temporary tables take up memory and other resources on the server.  Lots of temporary tables == lots of resources.
Tables that exceed the minimum of tmp_table_size or max_heap_table_size will end up being converted to on-disk temporary tables, which means additional I/O.  It also means that your queries take longer, as once the size of the MEMORY table reaches tmp_table_size, it is then converted to an on-disk MyISAM table.
BLOB/TEXT fields cannot be stored as in-memory temporary tables (the MEMORY storage engine does not support them), thus will always be created on disk.
creating temporary tables is expensive, especially when they are on-disk.
Consider that one of the first things we try to optimize away when looking at an explain plan is "Using temporary."  SQL_BUFFER_RESULT literally makes every query include "Using temporary", (nearly) all the time.  My little bit of testing showed that there were cases where MySQL did not use a temporary table even with this hint, but they were limited (single row lookups on primary key seemed to be the only case).

Here are a few examples to show the effect:

-- unindexed lookup w/out SQL_BUFFER_RESULT
mysql> explain select  * from actor where first_name = 'THORA'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 203
        Extra: Using where
1 row in set (0.00 sec)

-- unindexed lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where first_name = 'THORA'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 203
        Extra: Using where; Using temporary
1 row in set (0.00 sec)

-- indexed lookup w/out SQL_BUFFER_RESULT
mysql> explain select * from actor where last_name = 'TEMPLE'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

-- indexed lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where last_name = 'TEMPLE'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 4
        Extra: Using where; Using temporary
1 row in set (0.00 sec)

-- primary key lookup w/SQL_BUFFER_RESULT
mysql> explain select sql_buffer_result * from actor where actor_id = 200\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: const
         rows: 1
        Extra:
1 row in set (0.00 sec)

What is the problem being solved for?  "locks."  Since this is a hint for SELECT queries, unless you are using FOR UPDATE or LOCK IN SHARED MODE, there are very few locks taken on reads anyhow and they are very fast, so you are mostly solving for a problem that doesn't exist.  Exceptions always exist, but the overhead of creating a temporary table for nearly every query will far outweigh any benefit gained from making locks go away faster.

MySQL's recommendation is to use the SQL_BUFFERED_RESULT hint when retrieving a very large result set over a network connection to the client.  I cannot see any value in using it in another context.

All that being said, you can test and set this "globally" by setting sql_buffer_result=1 at the beginning of every session.  In an environment with any concurrency, I would predict poor results.


My SSMS is bringing wrong object in "Script Stored Procedure as" > "CREATE To" > "New Query Editor Window"

I believe this is what you need. That's a link talking about changing templates in the folder / file location when creating stored procedures. It sounds like someone altered the file.


pgAdmin 4 v1.6 setting PostgreSQL Binary Path to a remote server to clear-up "The system cannot find the path specified." error

You are getting this error because when using EXPLAIN or pg_dump from pgAdmin, the program uses the utilities in its install directory $DIR/../runtime or C:\Program Files (x86)\pgAdmin 4\v1\runtime to execute these commands. You can put this location in the Postgresql binary path and continue with the operation.


Postgresql: Max Connections per server / database

max_connections from postgresql.conf is for the entire server, but CONNECTION LIMIT from CREATE|ALTER DATABASE command is for that specific database, so you have your choice.

You might barely get away with 4500 connections, but only if the vast majority of them don't do anything the vast majority of the time.  In which, case, why have them?  Why does each microservice need a connection pool of 5?

  I've read through numerous articles with performance issues with one database and a schema per tenant so I think that option is ruled out.

There are lots of bad articles out there, or ones based on 15 year old experiences.  A lot has changed in 15 years.  Which articles are they? And did they actually say that a database per tenant was better than a schema per tenant, or just that a schema per tenant was bad with no comparison to the alternative?


Performance of BINARY vs CAST AS BINARY in MySQL

On my tests, I do not see a significant difference (full table scan, 6 rows only, like your example):

$ for i in $(seq 1 10); do for i in $(seq 1 100000); do echo "SELECT * FROM t1 WHERE CAST(c AS BINARY) = 'f';"; done | /usr/bin/time -f "%e" mysql -BN test > /dev/null; done
5.10
4.93
5.00
4.96
4.97
5.21
4.95
5.04
5.00
5.03
$ for i in $(seq 1 10); do for i in $(seq 1 100000); do echo "SELECT * FROM t1 WHERE BINARY c = 'f';"; done | /usr/bin/time -f "%e" mysql -BN test > /dev/null; done
5.10
5.28
4.84
5.00
5.19
5.17
7.14
5.06
4.86
6.24

If the table had more than 6 rows, an index (and therfore, a query plan change) would be helpful, but only if functions are applied to the right side, not to the column name:

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE c = BINARY 'f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: c
          key: c
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE BINARY c = 'f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE CAST(c AS BINARY) = 'f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE c = CAST('f' AS BINARY)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: c
          key: c
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)


Where are some useful SQL puzzles to teach SQL in a workplace?

Joe Celko's books and newsgroup posts are a good source of puzzles (with a variety of answers). They might be a bit more intermediate/advanced and not MySQL-specific, but are easy to follow and generally come from practical real-world situations.

Particularly, hunt down a copy of Joe Celko's SQL Puzzles and Answers (link to Google preview), it's a compilation of some of the best.

The SQLTeam forums occasionally have some interesting problems too, but generally focused on MS SQL Server.

The HackerRank SQL domain has a variety of SQL challenges that can be completed using multiple implementations (e.g.: Oracle, MySql, MS SQL Server, DB2).


This question is similar to: Files - in the database or not?. Click the link to see the answers provided.


SQL 2019 Linux, slow at GUI fast at sqlcmd/Query Analyzer

Auto close was found to be the culprit and it was decided to turn off this option for all databases. Express edition have this feature on by default.


Select all children from infinite parent-child relations

Regarding the "SELECT", i.e. how to recursively get all the descendants of a parent, there a lot of answers in this question:

Find highest level of a hierarchical field: with vs without CTEs

Rolando's method uses variables and a procedure.
a_horse_with_no_name's method uses a recursive CTE. This is not possible in MySQL yet but you could switch to MariaDB 10.2 which has them implemented.

Hierarchical queries in MySQL

The blog post by @Quassnoi has detailed explanation about writing recursive queries in MySQL.

For the recursive DELETE, there is a simple method available in all MySQL versions:

Add a FOERIGN KEY constraint - if you haven't already - with the ON DELETE CASCADE option. Then a simple delete of a node will delete all its descendants.
You could modify the methods in the above links, to do a DELETE instead of SELECT.


It looks like pg_ctl wants a real terminal for output, which is not allocated by ssh when you simply ask it to run a command. According to the Postgres manual

  On Unix-like systems, by default, the server's standard output and standard error are sent to pg_ctl's standard output (not standard error). The standard output of pg_ctl should then be redirected to a file or piped to another process such as a log rotating program like rotatelogs; otherwise postgres will write its output to the controlling terminal (from the background) and will not leave the shell's process group. [...] These default behaviors can be changed by using -l to append the server's output to a log file. Use of either -l or output redirection is recommended.

Therefore, you either need to tell ssh to allocate a terminal for pg_ctl using the -t options (ssh -t somehost "pg_ctl start") or tell pg_ctl to not write to the terminal (ssh somehost "pg_ctl start >/dev/null" or ssh somehost "pg_ctl start -l /tmp/start.log ")


error code 3417 - after installing CU for SQL SERVER 2016 SP1

I have started the sql server service from the command line using the trace flag /T902

FROM TRACE FLAGS

902

Bypasses execution of database upgrade script when installing a Cumulative Update or Service Pack. If you encounter an error during script upgrade mode, it is recommended to contact Microsoft SQL Customer Service and Support (CSS) for further guidance. For more information, see this Microsoft Support article.

WARNING: This trace flag is meant for troubleshooting of failed updates during script upgrade mode, and it is not supported to run it continuously in a production environment. Database upgrade scripts needs to execute successfully for a complete install of Cumulative Updates and Service Packs. Not doing so can cause unexpected issues with your SQL Server instance.

Scope: global only

this is also a good link:
Use Trace Flag 902 to Recover from a Cumulative Update Failure

After that I have looked at my error and it was about applying an update script.

I manually open that file and run it from management studio:

and the real error is:

Msg 15173, Level 16, State 1, Line 63894
Server principal '##MS_AgentSigningCertificate##' has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.
Msg 208, Level 16, State 1, Line 63932
Invalid object name 'dbo.sysssispackagefolders'.

Here is another wonderful contribution by Pinal Dave on the subject:

SQL SERVER – Script level upgrade for database master failed because upgrade step sqlagent100_msdb_upgrade.sql

I have just seen this one:

Failure during install of SQL Server 2016 sp2

after sorting this out,
I will start the sql service again, without the trace flag 902.


MongoDB gives certificate not trusted while openSSL says keys is OK

I changed hostname from mongo to mongo.my_domain.com. Moreover SSL keys are generated for mongo.my_domain.com. Mongo is now up and running with SSL.