Since your main issue is the FULLTEXT index, I have two storage engines suggestions
SUGGESTION #1 : MyISAM
The MyISAM storage engine does a full table lock on the following:

ALTER TABLE
DELETE
UPDATE
INSERT

The only exception to the table lock blocking everything would be setting concurrent_insert. (See MySQL Documentation on Concurrent Inserts)
Try setting this in my.cnf
[mysqld]
concurrent_insert = ALWAYS

or
[mysqld]
concurrent_insert = 2

Adding new rows to a MyISAM table bypasses table locking only in this circumstance. You will have to experiment with adding rows to the table while updating fulltext indexed columns in another row of the table.
SUGGESTION # 2 : InnoDB
As of Feb 5, 2013, MySQL 5.6 is not out of beta and is now GA. InnoDB in MySQL 5.6 supports FULLTEXT indexing. Since doing this in an ACID-compliant Database is what you essentially need, look into upgrading. Do no load any preinstalled MySQL 5.6 packages if they predate Feb 5, 2013.


The percentage varies based on a lot of factors; I say "about 20%".

For tiny tables, you would be hard pressed to see any performance difference.

Usually the Optimizer "does the right thing" in deciding between using the index (and bouncing between the index's BTree and the data BTree) versus simply scanning the entire table (and throwing out unwanted rows).

Bottom line:  Don't worry about it.


The order in which individual rows will be locked is undefined and certainly not something you should rely on since it will depend on the query plan.

In general, I would expect that the rows would not be locked in order-- the optimizer would generally expect it to be more efficient to access all the data unordered and then apply the ORDER BY.  If there was a composite index on pk_field, field1, field2, I could see the optimizer potentially choosing to do an ordered scan of the index and then locking each row in the table but that seems unlikely.


If you have a Menu key on your keyboard you can use it to navigate the context menus.  So [Menu],s,a,n will Script as Alter to New Query Editor Window.


SSIS Control Flow: Precedence constraints not working as expected

Your precedent constraint defaults to an AND situation. The preceding task must return success and the expression must be true.

In binary scenarios like this, only one of those two tasks is ever going to happen. By adding the next level of constraint in - both tied to the message box step, the final step is waiting for the "Enable CLR on server" to be successful as well as "CLR Enabled on Server" to be successful and for the value to be equal to 0.

Since only one of those situations will work out, you need to swing the PC to be OR situation

That results in the green dotted lines instead of solid

As an FYI, in your Script task, assuming this runs in an automated fashion (SQL Agent), since you're using a message box, it will fail. There's a System level variable, System::InteractiveMode which is a boolean. It indicates whether you're running in an environment where you can interact with the desktop or not. If that evaluates to false and you attempt any GUI activity, the package will error out.

I find it helpful to add code like the following

public void Main()
{
    // TODO: Add your code here
    bool interactiveMode = (bool) this.Dts.Variables["System::InteractiveMode"].Value;
    bool fireAgain = false;
    string message = "This is some message I likely built out using other SSIS Variable values";

    if (interactiveMode)
    {
        MessageBox.Show(message);
    }

    this.Dts.Events.FireInformation(0, "CLR Check", message, string.Empty, 0, ref fireAgain);

    Dts.TaskResult = (int)ScriptResults.Success;
}

There I have a check against my interactive mode and only attempt to display something if I can. I also go ahead and fire an OnInformation event because that's automatically logged in the 2012+ Project Deployment Model, shows in both the GUI and the Output panel in Visual Studio/SSDT/BIDS and is easily recorded from DTEXEC.exe call by appending I to the reporting parameter i.e. /rep EI


Masking Certain Parts of Specific Records

If you control the T-SQL (like if you're dealing with views or a reporting system), then you're probably looking for a user-defined function. With UDFs, you can code your own logic into something like this:

SELECT dbo.fn_Mask(MySecretField) FROM dbo.tbl_PreciousData

(Having a little fun with the naming geeks there.)

Then, inside fn_Mask, you'd write your own logic to take MySecretField as your input (and you could pass in multiple fields if you like), and even look at the logged-in user's security information to decide whether or not to mask the data.

But danger, Will Robinson: scalar UDFs make your query go serial, as opposed to parallel. Between that, and the fact that people can still directly select MySecretField, this is only a solution for limited scenarios.


MongoDB how to achieve asynchronous geo-replication

To do oneway replication from multiple "Local MongoDB" sources to one "Centralized MongoDB" realtime is possible with mongo-connector product. Just start one mongo-connector per every Local MongoDB.

I have used this product to replicate the one sharded collection (from nine shards) to one single mongodb.


Why are U locks required when using Read Committed Snapshot Isolation

Knowing this, why does sql server need to issue U locks (when using RCSI)? It seems to me that sql server could simply read the rows, and
  request a X lock directly if an update must be performed.

Unlike SI, RCSI does not detect update conflicts. As documented in Books Online, modifying data under RCSI reads currently-committed data, not a possibly out-of date version. (In the absence of update conflict detection, performing updates based on out-of-date data could result in a "lost update".)

Taking update locks is normal behaviour for a non-row-versioning query that updates data. It is a protection against a common cause of conversion deadlock, but it does not guarantee deadlock avoidance in all cases, especially where a different access path (index) is used to qualify rows to change.

You can find more details about the exact behaviour of RCSI when modifying data in my SQLperformance.com article, "Data Modifications Under Read Committed Snapshot Isolation". There is further background on RCSI in general in the article, "Read Committed Snapshot Isolation".

If the updates really are disjoint, you might consider performing the change using Snapshot Isolation rather than RCSI (which admittedly has complex behaviour in this area).


This query will return you all the columns of both tables, you can add your desired column in second query of simple joins without CTE

SELECT *
FROM msdb.dbo.backupset s
     INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
     ORDER BY backup_start_date DESC, backup_finish_date

Query2:

Select  s.database_name,
          m.physical_device_name,
          CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date)
            AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
          CASE s.[type]
           WHEN 'D' THEN 'Full'
           WHEN 'I' THEN 'Differential'
           WHEN 'L' THEN 'Transaction Log'
          END AS BackupType,
          s.server_name,
          s.recovery_model
     FROM msdb.dbo.backupset s
     INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
     ORDER BY backup_start_date DESC, backup_finish_date
     GO

Reference Link


what is better in terms of allocated space in Oracle Tables: NUMBER or BINARY_FLOAT?

It depends on your data (and this assumes that you don't care about the different behaviors of the two data types).  You can put together a test with sample data that is similar to what you're actually storing to find out what approach will use less space.

I'll create a table num_test with two different columns, one declared as a number and one as a binary_float.

create table num_test (
  num_col number,
  flt_col binary_float
);

If I populate it with 100,000 rows of data in increments of 0.001

begin
  for i in 1 .. 100000
  loop
    insert into num_test( num_col, flt_col )
      values( i/1000, i/1000 );
  end loop;
end;

then I can see the minimum, maximum, and average size required to store the values

select min( vsize(num_col) ), max( vsize(num_col) ), avg( vsize(num_col) ),
       min( vsize(flt_col) ), max( vsize(flt_col) ), avg( vsize(flt_col) )
  from num_test;

If you run that, you'll see that the number column is averaging 3.89 bytes/ row (with a min of 2 and a max of 4) while every binary_float required 4 bytes of storage.  So there is a small savings to using number in this case.

But is that the whole story?  Nope.  If we use different sample data, we get very different results.  Let's run the same test but divide everything by 3

begin
  for i in 1 .. 100000
  loop
    insert into num_test( num_col, flt_col )
      values( i/1000/3, i/1000/3 );
  end loop;
end;

Now, if you run the same vsize query, you'll see that the number column takes up to 21 bytes of storage and averages 7.69 bytes for the 200,000 rows.  The flt_col is still using a fixed 4 bytes for every row.

Depending on your data, you may find that one or the other approach uses more space.  Of course, where there are substantial differences in space utilization, there is the potential for more substantial rounding issues when you move to floating point numbers.

Note as well that the results may be version-dependent.  I did my test on an 11.2 database where the documentation indicates that binary_float always takes 4 bytes of storage.  In 10.2, the documentation indicates that binary_float took 5 bytes due to the inclusion of an extra length indicator.  It's not obvious to me why a 32-bit value would ever need a length byte so I'm hard-pressed to understand why an earlier version of Oracle might have required the fifth byte, but the documentation is consistent that it did.


Do stored procedures prevent SQL injection?

No, stored procedures do not prevent SQL injection.  Here's an actual example (from an in-house app someone created where I work) of a stored procedure that unfortunately permits SQL injection:

This sql server code:

CREATE PROCEDURE [dbo].[sp_colunmName2]
    @columnName as nvarchar(30),
    @type as nvarchar(30),
    @searchText as nvarchar(30)
AS
BEGIN
    DECLARE @SQLStatement NVARCHAR(4000)
    BEGIN
        SELECT @SQLStatement = 'select * from Stations where '
            + @columnName + ' ' + @type + ' ' + '''' + @searchText + ''''
        EXEC(@SQLStatement)
    END
END
GO

roughly equivalent to postgres:

CREATE or replace FUNCTION public.sp_colunmName2 (
    columnName  varchar(30),
    type varchar(30),
    searchText  varchar(30) ) RETURNS SETOF stations LANGUAGE plpgsql
AS
$$
DECLARE SQLStatement VARCHAR(4000);
BEGIN
    SQLStatement = 'select * from Stations where '
            || columnName || ' ' || type || ' ' || ''''|| searchText || '''';
    RETURN QUERY EXECUTE  SQLStatement;
END
$$;

The developer's idea was to create a versatile search procedure, but the result is that the WHERE clause can contain anything the user wants, allowing a visit from little Bobby Tables.

Whether you use SQL statements or stored procedure doesn't matter.  What matters is whether your SQL uses parameters or concatenated strings.  Parameters prevent SQL injection; concatenated strings allow SQL injection.


Please, try to analyze your backup strategies.

Also, You can analyze your DATABASE MODE (FULL, Bulk-Logged, Simple)
(https://msdn.microsoft.com/en-us/library/ms189275.aspx)

Also, you can check log space with:

DBCC SQLPERF(LOGSPACE);
GO

check this: https://msdn.microsoft.com/en-us/library/ms190674.aspx


Diagnosing high write throughput on RDS Postgres

It turns out that the vast majority of my write throughput was coming from the stats collector.  Because of the very high number of relations in my database, the stats data is unusually large.  I was able to diagnose the problem by temporarily clearing the stats:

SELECT pg_stat_reset()

Which led to an immediate, dramatic drop in my write throughput.  To solve the issue going forward, I've redirected my stats directory to a ram disk instead of to the hard drive, using this setting: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.RamDisk

The end result is that CloudWatch is now reporting around ~400 KB/s of throughput instead of 40 MB/s.


sp_configure set at code execution - Pro/con's of turning on/off

A) Those options can only be switch by logins with elevated permissions. Therefore to alter them at run-time the application must run with elevated permissions. This is a security risk.

B) Actions performed in this way run in the DBMS's process. Any instability (bug, exception) they cause will affect the stability of SQL Server and all clients it is serving.


Add Primary Key column at the end of an Index

In InnoDB, every "secondary" index (that is, index other than "PRIMARY") automatically adds the columns of the PK.

These are 'identical':

PRIMARY KEY(id),  INDEX(from, id)
PRIMARY KEY(id),  INDEX(from)

You could even do

PRIMARY KEY(from, id),  INDEX(id)

This combo is beneficial when id is AUTO_INCREMENT, but you often do a range on from.  Now the rows are "clustered" together, thereby being more efficient to fetch.  Meanwhile, INDEX(id) keeps AUTO_INCREMENT happy -- since it needs to know where it left off.

I assume you are doing:

SELECT ...
    WHERE `from` = '...'
    ORDER BY id DESC
    LIMIT 20;


For me too many indexes means that it is more expensive to insert, update, or delete rows. Because, if the indexes are of any use they need to be maintained. Find all of the indexes with columns that you either don't query, or don't query very often and drop them. Find all of the indexes where the columns are the same, but in a different order drop all but one version of those. For example a good index would start with low cardinality and get more specific. For example country, state, city, zip code. Another good method would be that if you search by date and other fields lead with the date column to eliminate the largest number of rows first, then choose the values where the other fields match. Try to get the number of indexes down to less than 10 per table.

Although the number of indexes that are useful depends on whether this is OLTP, DSS or data warehouse.


How to set column text when creating a view in SQL?

Simple: use an alias

SELECT ... CASE something WHEN 'somethingelse' ... END AS whatever, ...

The above will have the alias of WHATEVER. You can even get fancy and quote the alias:

... AS "Something Else"

which will have the alias of Something Else.


sp_getapplock not working in multiple Connection

Detailing the answer added to the question, according to the sp_getapplock documentation, (emphasis added):

  The lock resource created by sp_getapplock is created in the current
  database for the session. Each lock resource is identified by the
  combined values of:

  The database ID of the database containing the lock resource.

  The database principle specified in the @DbPrincipal parameter.

  The lock name specified in the @Resource parameter.


SQL Server: test # of partitions == partition_functions.fanout

Not sure why you want to do this, but some of the dmv links are a bit tricky to figure out from BOL. I believe the following meets your requirements:

SELECT t.name, MAX(pf.fanout) fanout, COUNT(*) cnt_partitions
FROM sys.tables t
INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id]
INNER JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
INNER JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE i.[type] IN (0, 1, 5) -- heap, CI, or CCI
GROUP BY t.name;

If I create a few partitioned tables:

CREATE PARTITION FUNCTION functioning_part_function
(BIGINT)
AS RANGE LEFT
FOR VALUES (
  0
, 5000000
, 10000000
, 15000000
, 20000000
, 25000000
, 30000000
, 35000000
, 40000000
, 45000000
, 50000000
);

CREATE PARTITION SCHEME scheming_part_scheme
AS PARTITION functioning_part_function
ALL TO ( [PRIMARY] );

CREATE TABLE dbo.PART_TABLE (
ID BIGINT NOT NULL,
INDEX CCI CLUSTERED (ID)
) ON scheming_part_scheme(ID);

CREATE TABLE dbo.PART_TABLE_2 (
ID BIGINT NOT NULL,
INDEX CCI CLUSTERED (ID)
) ON scheming_part_scheme(ID);

I get the following results:

╔══════════════╦════════╦════════════════╗
║     name     ║ fanout ║ cnt_partitions ║
╠══════════════╬════════╬════════════════╣
║ PART_TABLE   ║     12 ║             12 ║
║ PART_TABLE_2 ║     12 ║             12 ║
╚══════════════╩════════╩════════════════╝


In Postgresql, after pg_dump the access privileges on the database itself are not restored

Why are the access privileges on the database itself not restored?

It's a bug, or a design oversight. Though the responder to that report doesn't think so.

pg_dumpall --globals-only doesn't dump rights on the database. Neither does pg_dump as part of the database dump.

So grants on databases only get included in a full pg_dumpall.

I'll make some noise about it by poking this thread.


Don't mix storage (5 or 9 bytes) and what you get back
In a decimal(19,5) column it is always 9 bytes on disk. So zero will take nine bytes.
Your 10999.99999 is merely a representation of that stored number that is then parsed as 10999.99999 by DATALENGTH
Edit:
DATALENGTH will return the number of bytes needed to store the expression given. It ignores datatype mostly. So 10999.99999 is treated as decimal(10,5) which can be stored in 5 bytes not 9 as Martin pointed out in a comment.
So a decimal(19,5) column is always 9 bytes. DATALENGTH doesn't care. It looks at the expression 10999.99999 and decides it can be stored in 5 bytes. But of course it can't
Personally, I've never used DATALENGTH except to find trailing spaces or such.
Basically, don't use DATALENGTH on non string datatypes.

Returns the number of bytes used to represent any expression.
DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.

Note: LEN tells you how long the string representation is
DECLARE @i decimal(19,5)
SET @i ='10999.99999'
SELECT @i,LEN(@i),DATALENGTH(@i)
SET @i ='-90999.99999'
SELECT @i,LEN(@i),DATALENGTH(@i)


How to optimise MySQL query that uses dependent subquery

Instead of using a sub query you could use a LEFT JOIN.

That way you negate the need for a sub query which is being looked at for each image.

LEFT JOIN
        seen
    ON
        seen.image_id = images.id
    AND
        seen.user_id = $user_id

Then only get rows that haven't been joined.

WHERE
   seen.id IS NULL


How to shard a database like that of SO?

Sharding, or database partitioning, is usually done to allow parallel processing of chunks of data. Groups of records residing in different shards (partitions) can be processed independently of one another, thus effectively multiplying the database server capacity.

The biggest problem to solve when deciding the partitioning scheme is to ensure that data that are usually processed together (e.g. sets of frequently joined records) reside in the same partition. As a result the columns used to join tables (or their subsets) are often used as the partitioning key. If this principle is not followed partitioning usually turns into a big performance issue, because parallel processing of partitions becomes dependent on retrieving related sets of records from other partitions.

Subsequently, for effective partitioning the database needs to be designed from the beginning with this possibility in mind. You can't usually just "slap" partitioning on an existing database, unless the data model and queries are very simple.


MySQL thousands connections after convert MyISAM to InnoDB on fail-over structure

If you are using transactions, please ensure that you use either a COMMIT or ROLLBACK statement before closing the connection.

If this is already the case, you probably need to increase the thread_cache_size value in order to reuse as much as possible the existing connection threads instead of creating new ones from start.

Please note the following values:

mysql> SHOW GLOBAL VARIABLES LIKE 'Connections';
mysql> SHOW GLOBAL VARIABLES LIKE 'Thread_created';
mysql> SHOW GLOBAL VARIABLES LIKE 'Max_used_connections';

If Thread_created/Connections> 0.01, you have to increase the value of thread_cache_size.
The upper limit should be the value of Max_used_connections.


Compare the tablespace concept between Postgres and Oracle

In PostgreSQL, if you don't specify a tablespace in your CREATE TABLE command, the value of parameter default_tablespace will be used.

If default_tablespace is not set, or has an invalid value, the table will be created in the same tablespace as the database.

https://demo.pganalyze.com/databases/6/config/default_tablespace


trigger in MySQL to disallow multiple row deletions at a time on a table

This goes against all that righteous and good in triggers but here we go...
Create a table that keep a count of attempted deletes per connection
CREATE TABLE IF NOT EXISTS trigger_happy_employee_deletes
(
    delete_count INT DEFAULT 0,
    conn_id INT NOT NULL,
    PRIMARY KEY (connection_id)
) ENGINE=MyISAM;

or to reduce disk I/O make it a MEMORY table
CREATE TABLE IF NOT EXISTS trigger_happy_employee_deletes
(
    delete_count INT DEFAULT 0,
    conn_id INT NOT NULL,
    PRIMARY KEY (connection_id)
) ENGINE=MEMORY;

Now get the count from that table and check it
delimiter \\
create trigger not_more_than_2_rows
before delete on  employee
begin
declare count int;
insert ignore into trigger_happy_employee_deletes (conn_id) values (connection_id());
select delete_count into count
    from trigger_happy_employee_deletes
    where conn_id = connection_id()
;
set count = count + 1;
update trigger_happy_employee_deletes
    set delete_count = count
    where conn_id = connection_id()
;
if (count>1) then
    signal sqlstate '12345'
    set message_text = 'cannot delete more than 2 rows at a time'
end if;
end \\
delimiter ;

After your issue a DELETE command, you will have to remember to clear the count like this:
delete from employee where ... ;
update trigger_happy_employee_deletes
    set delete_count = 0
    where conn_id = connection_id()
;

or you count clear it before:
update trigger_happy_employee_deletes
    set delete_count = 0
    where conn_id = connection_id()
;
delete from employee where ... ;

Give it a Try !!!
POTENTIAL DRAWBACKS
Here are the pitfalls of doing this:
Pitfall #1
Using an arbitration method like this (via a MyISAM table) will cause a major bottleneck because a MyISAM performs a full table lock on each INSERT. Using a MEMORY table will not fare much better because although there is far less table access on disk, there is still nominal disk I/O hitting the MEMORY table's .frm file that can cause a slight bottleneck when given enough database traffic.
You cannot make trigger_happy_employee_deletes InnoDB because triggers do not work intelligently with InnoDB:

Jun 21, 2011 : Using MySQL triggers or transactions?
Mar 21, 2011 : Dynamic SQL in MySQL stored routines

Pitfall #2
From a coding perspective, you will have to remember to setup this mechanism across your code. If you forget to do this in all necessary places, you will have undesired deletions.
Pitfall #3
If you want this same mechanism for another table, you would either make another trigger_happy table for deletes or be tempted to merge all delete triggers into a common trigger_happy table, which would make maintenance a total nightmare, not to mention increased bottlenecks by an order of magnitude. Even worse, you may be tempted to merge update and insert triggers into the common trigger_happy table.
Pitfall #4
The trigger would have to communicate with another table outside the triggered table. That's more internal read traffic just to maintain a count.


How to shrink over sized logs in Database Mirroring?

Just do the shrink on the primary:

Can I shrink the transaction log file on a mirror database?

No need to fail over and shrink the secondary.


There is a difference between - Features not supported in NEXT version of sql server vs features not supported in a FUTURE version of sql server.

Mirroring is still available in SQL Server 2012, 2014 and 2016. It has been put into the future deprecated list, but as of now, it is still available.

AlwaysON (as it was with mirroring when it was first introduced in sql server 2005) is an enterprise feature and in sql server 2016 (CTP2.1) it supports upto 2 nodes in standard edition as well.


MySQL server has gone away obstructing import of large dumps

One of the silent killers of MySQL Connections is the MySQL Packet. Even the I/O Thread of MySQL Replication can be victimized by this.

According to the MySQL Documentation

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.
An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

At the very least, you must make sure the packet sizes for both the machine you mysqldump'd from and the machine you are loading are identical.

There may be two(2) approaches you can take:

APPROACH #1 : Perform the mysqldump using --skip-extended-insert

This will make sure the MySQL Packet is not inundated with multiple BLOBs, TEXT fields. That way SQL INSERTs are performed one at a time. The major drawbacks are

the mysqldump is much larger
reloading such a dump takes much longer.

APPROACH #2 : Increase max_allowed_packet

This may be the preferred approach because implementing this is just a mysql restart away. Understanding what the MySQL Packet is may clarify this.

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:

  MySQL network communication code was
  written under the assumption that
  queries are always reasonably short,
  and therefore can be sent to and
  processed by the server in one chunk,
  which is called a packet in MySQL
  terminology. The server allocates the
  memory for a temporary buffer to store
  the packet, and it requests enough to
  fit it entirely. This architecture
  requires a precaution to avoid having
  the server run out of memory---a cap
  on the size of the packet, which this
  option accomplishes.

  The code of interest in relation to
  this option is found in
  sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
  particular attention to
  net_realloc().

  This variable also limits the length
  of a result of many string functons.
  See sql/field.cc and
  sql/intem_strfunc.cc for details.

Given this explanation, making bulk INSERTs will load/unload a MySQL Packet rather quickly. This is especially true when max_allowed_packet is too small for the given load of data coming at it.

CONCLUSION

In most installs of MySQL, I usually set this to 256M or 512M. You should experiement with larger values when data loads produces "MySQL has gone away" errors.


Distinguishing Records with Related Fields

I encourage those more knowledgeable than I to edit this answer or question with additional information if it would be beneficial to those reading this in the future.

After experimentation, I've implemented Option 2, the boolean field for trial version.

I think this solution is

better normalized and
more clear to users

when each attribute is broken down into its own field (rather than having a version field which has to describe multiple attributes like product version and whether or not it is trial).

In some way it could be beneficial if I could just add new version numbers like 7.622 then 7.6221 to get more specific, but this can be confusing to decipher and is also somewhat arbitrary. The alternative--breaking the version/trial into multiple fields--also discourages others from creating new potentially duplicate or unnecessary version numbers which make things harder to find/query.

These additional fields, such as the trial boolean, are related to each Product through the one-to-many relationship between Products and Shipments.


Reusing sql query values for another query in single request

I believe you want something like:

SELECT msg.*
FROM messages msg
JOIN (SELECT chat_id, created_at
  FROM messages
  where id='starting_message_id') nested
ON (msg.chat_id=nested.chat_id and msg.created_at>=nested.created_at)
ORDER BY msg.created_at
LIMIT 6;


Understanding the simple execution plan in PostgreSQL

According to my understanding this first limit the number of records to 3 and then do a sequential scan for all the records. Why does this do a scan for all the records since it has already limited the result to 3 rows ?

That's not (necessarily) what's happening.

Outer nodes consume rows from inner nodes one by one with a demand-pull model.

So in this case, the limit node will result in a maximum of three nodes being consumed from the inner sequential scan node. As a sequential scan node can deliver nodes progressively it will do very little work.

This won't be true of some other node types. For example, if you add an ORDER BY (which you always should with LIMIT otherwise the results can be pretty much random) it will add a Sort or top-N sort node. A Sort node must fully execute the entire subplan before it returns any results, so it might do a lot of work even if there's a limit node on the outside. A top-N sort node can sometimes avoid some of that work, but it depends on the inner plan.

If you use EXPLAIN (ANALYZE, VERBOSE) you can see the actual row counts processed by each node, which is usually informative. In this case the actual rows processed by the inner seqscan will probably be 3.

You will find http://explain.depesz.com/ extremely useful when reading plans. PgAdmin-III also has a graphical explain that some people like, though I don't use it myself.


When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

When a query that used to run fast suddenly starts running slowly in the middle of the night and nothing else is affected except for this one query, how do I troubleshoot it...?

You can start by checking if the execution plan is still in the cache. Check sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_cached_plans. If the bad execution plan is still cached you can analyze it, and you can also check the execution stats. The execution stats will contain information as logical reads, CPU time and execution time. These can give strong indications what the problem is (eg. large scan vs. blocking). See Identifying problem queries for an explanation how to interpret the data.

  Also, this is not a problem with parameter sniffing. I've seen that before, and this is not it, since even when I hard-code the varaibles in SSMS, I still get slow performance.

I'm not convinced. Hard-coding variables in SSMS does not prove that the past bad execution plan was not compiled against a skewed input. Please read Parameter Sniffing, Embedding, and the RECOMPILE Options for a very good article on the topic. Slow in the Application, Fast in SSMS? Understanding Performance Mysteries
is another excellent reference.

  I've concluded (perhaps incorrectly) from these little experiments that the reason for the slow-down is due to how SQL's cached execution plan is set up -- when the query is a little different, it has to create a new execution plan.

This can be easily tested. SET STATISTICS TIME ON will show you the compile vs. execution time. SQL Server:Statistics performance counters will also reveal whether compilation is an issue (frankly, I find it unlikely).

However, there is something similar that you may hit: the query grant gate. Read Understanding SQL server memory grant for details. If your query requests a large grant at a moment no memory is available, it will have to wait, and it will all look as 'slow execution' to the application. Analyzing wait info stats will reveal if this is the case.

For a more general discussion about what to measure and what to look for, see How to analyse SQL Server performance


I have used the Percona PAM Authentication Plugin for MySQL to successfully authenticate individual users via LDAP but so far I haven't managed to the group/proxy part working.

You do need to make sure that there is a PAM profile for MySQL, which is probably the bit that is missing (first question) from the article you linked. I had also read through that along the way.

I found this guide particularly helpful for getting it working (second question):

http://www.arubin.org/blog/2012/07/16/mysql-pam-ldap-authentication-module-configuration/

I'm still in the process of finalising this all and once I'm finished I'll blog about my experience and update this post with more information. Hope this helped!


How to Select the First (or last) entry for a specific day

You can't use an aggregate function in a condition, you need to use a sub-select

SELECT EntryID
FROM AttendanceREcords
WHERE StaffID = 'xxxxx'
AND ArrivalTime = (select min(ArrivalTime)
                   from AttendanceREcords
                   where StaffID = 'xxxxx');

But this can be done more efficiently by using a LIMIT clause:

SELECT EntryID
FROM AttendanceREcords
WHERE StaffID = 'xxxxx'
ORDER BY arrivalTime
LIMIT 1

There is a difference between the two statements: if more than one row has the same minimum arrival time, the first one will return all of them, the second one only one row.

Another alternative that is usually more efficient that a sub-select is using a window function:

SELECT EntryID
FROM (
  SELECT EntryID,
         dense_rank() over (order by arrivalTime) as rnk
  FROM AttendanceREcords
  WHERE StaffID = 'xxxxx'
) t
where rnk = 1;

By changing the order by arrivalTime you can select the first or the last.

If you want the first and last in a single query you can do something like this:

SELECT EntryID,
       arrivalTime
FROM (
  SELECT EntryID,
         min(arrivalTime) as min_time,
         min(arrivalTime) as max_time,
  FROM AttendanceREcords
  WHERE StaffID = 'xxxxx'
) t
where arrival_time = min_time
   or arrival_time = max_time;


How to take pg_dump of a very large postgres database?

Hello you need to change max_locks_per_transaction in the postgresql.conf file typically located in /var/lib/pgsql/data or wherever you installed the data directory for PostgreSQL.

Which was already answered here

You will have to restart the PostgreSQL Service.

The answer above also talks about having to possibly increase shared memory. Here is a link to a PostgreSQL article on that subject.


If it works for you then it's a good practice.  There are basically no hard rules for databases.


Change SQL Server service account without restarting SQL Server

...for security reasons we want to change the password that starts the sql server service everyday

I realize this is probably outside your control, but it's worth mentioning that this is not a good idea for security.  It's a high process overhead for your organization (even if it's generally automated in some way), and it will likely lead to increased chances of some kind of password breach.

Think about it like this: every time you open the bank vault, there is a risk that some malicious person can get inside.  Would you rather open the vault every single day and change the locks, or open it occasionally and keep a really big lock on it (long, secure password).

Accessing the password every day (to change it, and then apply it to the service account) is like this.  You should very a very secure password over changing the password frequently.

If you are required to do this, though - why don't you want to use SQL Server Configuration Manager?  That is the recommended method per the docs:

SQL Server Configuration Manager - Changing the Accounts Used by the Services

  Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as setting permissions in the Windows Registry so that the new account can read the SQL Server settings. Other tools such as the Windows Services Control Manager can change the account name but do not change associated settings. If the service cannot access the SQL Server portion of the registry the service may not start properly.

Emphasis added by me.  So doing this outside of the service can cause reliability issues with your database engine.

Regarding avoiding a restart, using the configuration manager accomplishes this goal as well:

  As an additional benefit, passwords changed using SQL Server Configuration Manager, SMO, or WMI take affect immediately without restarting the service.

Of course, as other answers have mentioned, you should be allowed to restart the service from time to time (Windows Updates, etc).


After update triggers that deal with multi-row updates

Here is how to fix the errors, using proper joins (and if this isn't "fast enough" then look at your indexing):

INSERT dbo.AuditTable
(
  [TableName],
  [Source],
  [RecordId],
  [User],
  [TimeStamp],
  [UpdatedColumn],
  [OldValue],
  [NewValue]
)
SELECT
  N'BookingItem', -- TableName - nvarchar(max)
  ls.CODE,
  INS.Id, -- RecordId - bigint
  u.USERNAME,
  GETDATE(), -- TimeStamp - datetime
  @UpdatedColumn, -- UpdatedColumn - nvarchar(max)
  DEL.Priority, -- OldValue - nvarchar(max)
  INS.Priority  -- NewValue - nvarchar(max)
FROM
  INSERTED AS INS
INNER JOIN
  DELETED AS DEL ON INS.Id = DEL.Id
INNER JOIN
  dbo.TBL_LEG_SOURCE AS ls ON ls.LEG_SOURCE_ID = INS.SourceId
INNER JOIN
  dbo.TBL_USER AS u ON INS.ModifiedById = u.USER_ID
WHERE
(
  (INS.Priority <> DEL.Priority)
  OR (INS.Priority IS NULL AND DEL.Priority IS NOT NULL)
  OR (INS.Priority IS NOT NULL AND DEL.Priority IS NULL)
);

However I think it is quite foolish to run 50+ different inserts of this variety in order to capture every single column change. Why not just create a table with columns for time and table name (you don't need to store username because you can always look that up later), and then whenever there is an update, store the old and new version of the row? You can even use a SEQUENCE to ensure that you can identify the set of rows that were modified together (since timestamp may not be unique enough to do that).

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

CREATE TABLE dbo.AuditData
(
  AuditSequenceID INT,
  TableName SYSNAME,
  [TimeStamp] DATETIME,
  RowState CHAR(1), -- e.g. 'B' = before, 'A' = after
  ... all your 50 columns, including ModifidById ...
);

Now in your trigger:

CREATE TRIGGER dbo.MyTrigger
  ON dbo.BookingItem
  FOR UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @as INT = NEXT VALUE FOR dbo.AuditSequence,
          @now DATETIME = CURRENT_TIMESTAMP;

  INSERT dbo.AuditData(AuditSequenceID, TableName, [TimeStamp], RowState,
    ... the rest of your 50 columns)
  SELECT @as, N'BookingItem', @now, 'B', * FROM deleted;

  INSERT dbo.AuditData(AuditSequenceID, TableName, [TimeStamp], RowState,
    ... the rest of your 50 columns)
  SELECT @as, N'BookingItem', @now, 'A', * FROM inserted;
END
GO

Now, write complicated queries against this much simpler audit structure that are inefficient and try to track exactly which columns have changed and all of that. Much better to pay that price when you're reviewing audit data than to pay that price on every single update operation.


Using SQL*Plus to connect to remote DB from local server, how do I export a backup to my local server?

Datapump uses directory objects in the database, not direct file system paths. It can only export/import from directories visible on the database server itself. Your only possible option would be to share a directory from your local server to the database server and have Oracle write to the shared folder, but that doesn't always work either depending on the share type (plus it would be a pretty big security risk). Using datapump there is no way to export to a client system the way you are trying to do.


64 bit Machine 32 bit Excel Using SQl Server Management Studion V17

You'll need to use a more recent driver since the excel file was created in Excel 2016.

Install the following driver on the machine running the SQL Server Database Engine: Microsoft Access Database Engine 2016 Redistributable (64-bit)

https://www.microsoft.com/en-us/download/details.aspx?id=54920

After the driver is installed, open up SSMS and navigate to:

Linked Servers -> Providers -> right-click Microsoft.ACE.OLEDB.16.0: Enable "Allow In-Process"

From here, you can use the Microsoft.ACE.OLEDB.16.0 in your OPENROWSET command.
I prefer to setup linked servers to these files, as follows:

EXEC sp_addlinkedserver @server='YourLinkedServerName',
                        @srvproduct='Excel',
                        @provider='Microsoft.ACE.OLEDB.16.0',
                        @datasrc='C:\File1.xlsx'

Then you can query the Excel file using the 4-part Linked Server syntax (the DB name and schema part are shorthanded here with ...):

SELECT * FROM [YourLinkedServerName]...[Sheet1$]


Using Oracle Datapump when target table has additional column

Just import your data into a a staging table at the target database. Then write a SQL staement to copy the rows from the staging table into the target table while giving the two new columns an explicit value.
So something like this;

The following syntax should work on 11g

impdp user/password directory=import_directory dumpfile=data.dmp  REMAP_TABLE=schema.source_table_name:staging_table_name

Then, simply do something like this;

INSERT INTO target_table (column1, column2, column3, column4, column5)
SELECT column1, column2, column3, 'New Data', 'New Data2'
FROM staging_table_name

After which you can drop the staging table.


That is not a GROUP BY, but UNION:

select * from cant
union
select * from col
order by id, cant_val;


Using an account that has the ALTER SYSTEM privilege:

alter system flush shared_pool;

followed by:

alter system flush buffer_cache;


Semaphores throttle access to a resource.  If it's not available a process has to wait until it is.  Very long waits can signal that you have a deadlock situation and something needs to be killed to break it up.

https://stackoverflow.com/questions/2332765/lock-mutex-semaphore-whats-the-difference

  A lock allows only one thread to enter the part that's locked and the
  lock is not shared with any other processes.

  A mutex is the same as a lock but it can be system wide (shared by
  multiple processes).

  A semaphore does the same as a mutex but allows x number of threads to
  enter, this can be used for example to limit the number of cpu, io or
  ram intensive tasks running at the same time.


How can I add "in" keyword in this code to accept more than one value in gender and age parameters using SQL Server 2012

I think that you need to split up the male + female values, so you can add the ' and the comma's characters for each gender. If you are on sql 2016 you could use string split:

Example for SQL Server 2016 and up:

DECLARE @gender nvarchar(255)= N'Male,Female';
DECLARE @genderlist nvarchar(1000);
SET @genderlist=(select ''''+ value+''',' from string_split(@Gender,',')
FOR XML PATH(''));
select @genderlist = left(@genderlist,len(@genderlist)-1);
select @genderlist;

SQL Server 2012 Example

declare @gender nvarchar(255)= N'Male,Female'
declare @genderlist nvarchar(1000)
declare @genderlist2 table (genders nvarchar(1000))
declare @temp int
INSERT INTO @genderlist2(genders)
SELECT Split.a.value('.', 'NVARCHAR(MAX)')  as a

FROM
(
    SELECT CAST('<X>'+REPLACE(@Gender, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X')
 AS Split(a)

 set @genderlist=(
select ''''+genders+''',' from @genderlist2
for xml path(''))
select @genderlist = left(@genderlist,len(@genderlist)-1);
select @genderlist

Source

Another issue apart from splitting your string:

One other issue found, is that you need to keep the variables out of the where string created, otherwise the variable will not change to what it is holding. E.G. @p4 would have stayed @p4 instead of parsing to 12,14. I changed it to @Age in my example.

SQL Server 2012 Full procedure

   ALTER Procedure [Evaluation].[SelectPlayerData]

    @MembershipNumber varchar(20),
    @PlayerName varchar(70),
    @PlayerNameEnglish varchar(70),
    @Age varchar(200),
    @Gender varchar(100),
    @Mobile1 varchar(20),
    @Mobile2 varchar(20)
    as
    set nocount on
    begin
     Declare @BaseQuery nvarchar(max)
    ='SELECT        Evaluation.Players.PlayerID, Evaluation.Players.MembershipNumber, Evaluation.Players.PlayerName, Evaluation.Players.PlayerNameEnglish, Evaluation.Players.Age, Evaluation.Gender.GenderEnglish,
                             Evaluation.Players.Mobile1, Evaluation.Players.Mobile2
    FROM            Evaluation.Players INNER JOIN
                             Evaluation.Gender ON Evaluation.Players.GenderID = Evaluation.Gender.GenderID'
        , @ParamList nvarchar(max) = N'@MembershipNumber varchar(20), @PlayerName varchar(70), @PlayerNameEnglish varchar(70),@Age varchar(200), @Gender varchar(100), @Mobile1 varchar(20) , @Mobile2 varchar(20)'
        , @WhereClause nvarchar(max) = ' WHERE 1=1';

    IF @MembershipNumber IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.MembershipNumber = '+@MembershipNumber+'';
        END

    IF @PlayerName IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerName = '+@PlayerName+'';
        END

    IF @PlayerNameEnglish IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerNameEnglish = '+@PlayerNameEnglish+'';
        END

    IF @Age IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Age in ('+@Age+')';
        END

    IF @Gender IS NOT NULL
        BEGIN

        declare @genderlist nvarchar(1000)
        declare @genderlist2 table (genders nvarchar(1000))
        declare @temp int
        INSERT INTO @genderlist2(genders)
        SELECT Split.a.value('.', 'NVARCHAR(MAX)')  as a

        FROM
        (
            SELECT CAST('<X>'+REPLACE(@Gender, ',', '</X><X>')+'</X>' AS XML) AS String
        ) AS A
        CROSS APPLY String.nodes('/X')
         AS Split(a)

         set @genderlist=(
        select ''''+genders+''',' from @genderlist2
        for xml path(''))
        select @genderlist = left(@genderlist,len(@genderlist)-1);

            SET @WhereClause = @WhereClause + ' AND Evaluation.Gender.GenderEnglish in ('+@genderlist+')';
        END

        IF @Mobile1 IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile1 = '+@Mobile1+'';
        END

        IF @Mobile2 IS NOT NULL
        BEGIN
            SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile2 = '+@Mobile2+'';
        END

        SET @BaseQuery = @BaseQuery + @WhereClause;

       EXECUTE sp_executesql @BaseQuery, @ParamList,
         @MembershipNumber = @MembershipNumber,
         @PlayerName = @PlayerName,
         @PlayerNameEnglish = @PlayerNameEnglish,
         @Age = @Age,
         @Gender = @Genderlist,
         @Mobile1 = @Mobile1,
         @Mobile2 = @Mobile2;

    end

SQL Server 2016 Full procedure

     ALTER Procedure [Evaluation].[SelectPlayerData]

@MembershipNumber varchar(20),
@PlayerName varchar(70),
@PlayerNameEnglish varchar(70),
@Age varchar(200),
@Gender varchar(100),
@Mobile1 varchar(20),
@Mobile2 varchar(20)
as
set nocount on
begin
 Declare @BaseQuery nvarchar(max)
='SELECT        Evaluation.Players.PlayerID, Evaluation.Players.MembershipNumber, Evaluation.Players.PlayerName, Evaluation.Players.PlayerNameEnglish, Evaluation.Players.Age, Evaluation.Gender.GenderEnglish,
                         Evaluation.Players.Mobile1, Evaluation.Players.Mobile2
FROM            Evaluation.Players INNER JOIN
                         Evaluation.Gender ON Evaluation.Players.GenderID = Evaluation.Gender.GenderID'
    , @ParamList nvarchar(max) = N'@MembershipNumber varchar(20), @PlayerName varchar(70), @PlayerNameEnglish varchar(70),@Age varchar(200), @Gender varchar(100), @Mobile1 varchar(20) , @Mobile2 varchar(20)'
    , @WhereClause nvarchar(max) = ' WHERE 1=1';

IF @MembershipNumber IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.MembershipNumber = '+@MembershipNumber+'';
    END

IF @PlayerName IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerName = '+@PlayerName+'';
    END

IF @PlayerNameEnglish IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.PlayerNameEnglish = '+@PlayerNameEnglish+'';
    END

IF @Age IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Age in ('+@Age+')';
    END

IF @Gender IS NOT NULL
    BEGIN
    DECLARE @genderlist nvarchar(1000);
    SET @genderlist=(select ''''+ value+''',' from string_split(@Gender,',')
    FOR XML PATH(''));
    SELECT @genderlist = left(@genderlist,len(@genderlist)-1);

        SET @WhereClause = @WhereClause + ' AND Evaluation.Gender.GenderEnglish in ('+@genderlist+')';
    END

    IF @Mobile1 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile1 = '+@Mobile1+'';
    END

    IF @Mobile2 IS NOT NULL
    BEGIN
        SET @WhereClause = @WhereClause + ' AND Evaluation.Players.Mobile2 = '+@Mobile2+'';
    END

    SET @BaseQuery = @BaseQuery + @WhereClause;

   EXECUTE sp_executesql @BaseQuery, @ParamList,
     @MembershipNumber = @MembershipNumber,
     @PlayerName = @PlayerName,
     @PlayerNameEnglish = @PlayerNameEnglish,
     @Age = @Age,
     @Gender = @Genderlist,
     @Mobile1 = @Mobile1,
     @Mobile2 = @Mobile2;

end

Test 1

create schema Evaluation
        create table Evaluation.Players(       PlayerID int,  MembershipNumber int,  PlayerName nvarchar(255),  PlayerNameEnglish nvarchar(255),  Age varchar(200),
                          Mobile1 char(15),Mobile2 char(15) , GenderID tinyint)

        create table Evaluation.Gender(GenderID tinyint, GenderEnglish nvarchar(255))

        insert into Evaluation.players VALUES (1,1,'Randi','Randi',12,123214215215,12421421422,1)
        insert into Evaluation.players VALUES (2,2,'Gandalf','Gandalf',142,123214215215,12421421422,1)
        insert into Evaluation.players VALUES (3,3,'Gandalf','Frodo',14,123214215215,12421421422,1)

        insert into Evaluation.gender VALUES(1,'Male')
        insert into Evaluation.gender VALUES(2,'Female')

DECLARE @return_value int

EXEC    @return_value = Evaluation.[SelectPlayerData]
        @MembershipNumber = NULL,
        @PlayerName = NULL,
        @PlayerNameEnglish = NULL,
        @Age = '12,14',
        @Gender =  NULL,
        @Mobile1 = NULL,
        @Mobile2 = NULL
   SELECT  'Return Value' = @return_value

Result

>  PlayerID MembershipNumber    PlayerName  PlayerNameEnglish   Age GenderEnglish   Mobile1 Mobile2
>     1 1   Randi   Randi   12  Male    123214215215    12421421422
>     3 3   Gandalf Frodo   14  Male    123214215215    12421421422

Test 2

Extra insert

INSERT INTO Evaluation.players VALUES (4,4,'Emma','Watson',12,123214215215,12421421422,2);

Execute proc

 DECLARE @return_value int
EXEC    @return_value = Evaluation.[SelectPlayerData]
        @MembershipNumber = NULL,
        @PlayerName = NULL,
        @PlayerNameEnglish = NULL,
        @Age = '12,14',
        @Gender =  'Male,Female',
        @Mobile1 = NULL,
        @Mobile2 = NULL

Result

PlayerID    MembershipNumber    PlayerName  PlayerNameEnglish   Age GenderEnglish   Mobile1 Mobile2
1   1   Randi   Randi   12  Male    123214215215    12421421422
3   3   Gandalf Frodo   14  Male    123214215215    12421421422
4   4   Emma    Watson  12  Female  123214215215    12421421422


novice-design: Are FOREIGN KEYs always necessary? Joins on columns which are not PK

There is absolutely nothing wrong with joining on columns that are not PKs/FKs. If you are concerned about efficiency then the key is to have appropriate indexes defined to support the join operations you are using. Also, don't assume that the existence of a foreign key implies the existence of an index - some databases automatically create such an index but many do not as it is not required and needlessly takes up space if it would never actually help with the queries you run.

Primary and foreign keys (and unique constrains and other database defined limits) exist for data integrity purposes.

Regarding FKs with ON DELETE CASCADE and people deleting parent rows: it is safer to no define ON DELETE CASCADE and let it error when people try rather than not defining FKs and other data integrity controls due to fear of human error because that is just saving yourself a nasty headache for later. Rows should not be updated by delete-then-reinsert, which is the behaviour that causes this concern, people should instead either perform a check-then-insert-or-update-as-needed or some form of UPSERT operation if the database supports on (MERGE in some like MS SQL Server, INSERT ... ON CONFLICT ... or similar in others, and so forth).


Short answer: No

Longer...

SQL Server doesn't know that it is SSRS sending it a query. So the query from SSRS will run like any other query.

It's more likely be that the query optimiser decides to use a table lock for the SSRS query. of course, it could be a different problem, but that's a different question


Is it a good idea to create an index for each column in a new table?

Would you find it useful to have the phone book, only having it as a list of Surnames with phone numbers, plus a list of Firstnames with phone numbers, and a list of towns with phone numbers? Because essentially, that's what you're getting if you index each column individually.


InnoDB?  Or MyISAM?  Or a mixture?

If MyISAM, why are you not moving to InnoDB?

Look at the arguments to mysqldump.  One says to lock all the MyISAM tables for the duration of the dump.  (This, of course, is nasty for updates/inserts.)  Another says to "lock" InnoDB tables as a --single-transaction.  (This is not so nasty.)

Xtrabackup (see percona.com) is an alternative to mysqldump and an excellent way to backup a live system of InnoDB tables.  So is MySQL Enterprise Backup ($$).


ACID-compliance of MySQL master-master replication?

MySQL replication is not ACID compliant since it is asynchronous.  There are no guarantees that queries executed on one server will be committed on the other.  In fact, the master doesn't even know about the slave except that it's pulling binary logs.  The binary logs themselves "might" be considered ACID compliant when sync_binlog=1 is on, but one server does not know if the other server has committed the change.

If you truly need something close to ACID compliance in your replication, what you want is synchronous replication, which is provided by products like Tungsten Replicator, Galera, and Percona Xtradb Cluster (which is Galera + Percona Server), and MySQL Cluster.  I'm not sure if they fit a strict definition of ACID compliance, but they are certainly a lot closer.  MySQL 5.5 has something called semi-synchronous replication, but don't be fooled...it's still asynchronous.

You can improve the reliability of MySQL replication by doing a few things:

Use Row-based Replication (RBR) by setting binlog_format=ROW in my.cnf.  This will replicate the entire row for every change made instead of replicating the SQL statement that made the change (as with statement-based replication, [SBR]).  That minimizes the chances that a statement executes differently between servers if there are small discrepancies already.
Run pt-table-checksum frequently to verify the consistency between master and slaves.  If there are certain tables that are more important, run it on those tables more frequently.
Set up monitoring that checks the output of pt-table-checksum and alerts you when there are discrepancies.  You can use pmp-check-pt-table-checksum from the Percona MySQL Nagios Plugins if you use nagios.  Otherwise, it's just a simple query to determine this information.
If you have discrepancies, use pt-table-sync to resolve them.  Read the documentation carefully, as you can shoot yourself in the foot relatively easily with this tool.


Find active values of SQLNET.ORA parameters on server

Parameters set in the sqlnet.ora file are not exposed in any data dictionary table.  You can't write a SQL query to find them.

Source:
https://community.oracle.com/thread/2550107?start=0&tstart=0


Optimizing a Query for splitting strings with a known size

Instead of:

SELECT
  Col1 = (SELECT Val from dbo.split(Name, '.') WHERE Id = '1'),
  Col2 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '2'),
  Col3 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '3')
FROM Mains

Use:

SELECT
  s.*
FROM Mains
CROSS APPLY (
    SELECT
        MAX(CASE WHEN Id = 1 THEN Val END) AS Col1,
        MAX(CASE WHEN Id = 2 THEN Val END) AS Col2,
        MAX(CASE WHEN Id = 3 THEN Val END) AS Col3
    FROM dbo.split(Name,'.') s
    ) s

The idea is that you still want exactly one row per row in Mains. And using an aggregate function inside CROSS APPLY will do just that. By using CASE you only need call split() once per row.

There are plenty of questions around here about splitting strings, and SQL DB does already have a string_split() function built in.


Calculate time difference between a datetime and the current date & time

Some other approaches - I started this many hours ago and only just got back to it.

Let's first take a look at how to extract some important date part information from this data:

DECLARE @dt DATETIME = '2014-06-12T12:40:12', @d INT;
SELECT @d = DATEDIFF(MINUTE, @dt, GETDATE());

-- at the time I ran this:
SELECT
  @d, -- 111,027 minutes
  -- or
  @d / 60, -- 1,850 hours
  @d % 60, -- and 27 minutes
  -- or
  @d / 1440, -- 77 days
  @d % 1440, -- and 147 minutes
  -- or
  @d / 1440,      -- 77 Days
  @d % 1440 / 60, -- and 2 hours
  @d % 60;        -- and 27 minutes

Want to discard anything more than 24 hours? Ok, just take the modulo in days (1440 minutes), we just want the remainder (2 hours, 27 minutes).

SELECT LEFT(CONVERT(TIME,DATEADD(MINUTE,@d%1440,0)),5);

-- 02:27

Want all of the hours on the left? ok:

SELECT CONVERT(VARCHAR(11), @d/60) + ':'
  + RIGHT('0' + CONVERT(VARCHAR(11), @d%60), 2);

-- 1850:27

Need the hh: suppressed when it's < 1 hour? ok:

SELECT COALESCE(NULLIF(CONVERT(VARCHAR(11), @d/60),0) + ':','')
  + RIGHT('0' + CONVERT(VARCHAR(11), @d%60) , 2);

-- 1850:27 in this case, but just 27 if input was 27 minutes ago

-- want days broken out separately? ok:

SELECT CONVERT(VARCHAR(11), @d/1440) + ' days, '
  + RIGHT('0' + CONVERT(VARCHAR(11), (@d%1440/60)), 2)
  + ':' + RIGHT('0' + CONVERT(VARCHAR(11), @d%60) , 2);

-- 77 days, 02:27


PostgreSQL 10+

In PostgreSQL 10 the WAL directory was renamed from pg_xlog to the easier to remember pg_wal.

Pg 10: WAL Internals (you can click the top for 9.6 to see the older version before the docs were updated to reflect the change).
New in postgres 10: Renaming of "xlog" to "wal" Globally (and location/lsn) describes the change in more details.


Query Editor Connection Error With Unattended Service Account

I found this post while researching the same error message.
I am using 'SQL Server Data Tools for Visual Studio 2017’ My database is on SQL 2017, Enterprise.
The problem turned out to be that the credentials switched to "Do not use credentials" After I set it to "Use Windows Authentication" when I created the data source.
I changed it back (below) and saved, problem solved.
Data Sources > Rclick > Data Source Properties


Schemas in postgres are used mostly for namespacing and sometimes for security.

Namespacing because two objects can have the same name in different schemas, and are then referenced by schema.object notation - especially useful in conjunction with search_path (many contrib modules do this, eg xml2). Security because you can now do grant ... on all tables in schema.


How to use a table as a reference for another table in MySQL

Change the syntax as per mysql:

CREATE TABLE secondTable
    (
    wID int NULL,
    w1 varchar(50) NULL,
    w1cs varchar(50) NULL,
    L1 varchar(50) NULL,
    c1 varchar(25) NULL
    )


There are many requirements for capturing changed data. I list only three common ones below.

Audits (HIPAA, SAS70, etc.)...one may need to audit SQL Server logins as well as attribute data changes to specific users
EDI...some insurance carriers require one to send to them current and previous data when a policy is altered, say, due to a life event. One may need to send one's previous coverage together with the new coverage. This is a good candidate for using SQL Server Change Data Capture (CDC).
ETL for DWs...require data change capture in order to add new data to facts and dimensions as well as process updates to slowly changing dimensions. Usually, something like SQL Server's Change Tracking would be right for this, since it is a "lighter" tool than CDC.


Is Oracle more demanding environment for a DBA than SQL Server?

Oracle has a significantly higher barrier to entry than SQL Server. Does that make working with Oracle "harder" than SQL Server, well that depends on what is meant by "harder" and where you're setting the bar.

I've very rarely encountered enterprise, Oracle backed systems, that didn't have dedicated Oracle DBAs/developers on the development team. I frequently work with SQL backed systems that have been developed without any DBA expertise whatsoever.

Why?

Microsoft are smart. They've built a database platform that competes with Oracle but made it accessible to anyone that can knock up a database in Access.  The notion of Code First, for anything other than the most noddy of departmental applications, scares me witless. But doesn't it make great commercial sense?

Hide the complexity. Make it easy for anyone to build on your database platform. Later, once they're locked in, they can find all the knobs, levers and switches they'll need to get the best out of the product.

Oracle requires expertise on knobs, levers and switches before you can create your first table. To the novice, that makes Oracle more difficult to get started with. Not technically more challenging, just in comparison to SQL Server, plain difficult.

If you're a developer that just wants to "get things done", which platform are you likely to lean toward?

Is an expert Oracle DBA more expert than his SQL Server equivalent? No.
Is Oracle technology more complex than SQL Server? No.
Is difficult or demanding a good benchmark for anything? Commuting across London on the underground is both but I don't feel superior for doing so :)


@RolandoMySQLDBA's answer is accurate, but I would advise against using the replicate-*-table and replicate-*-db options because of the complexity of how MySQL evaluates replication rules.

When these aren't used, everything is replicated, and I would contend that replicating everything the most reliable configuration.  As a rule, I never do anything else.

If you don't want to replicate the historical data, I would start by setting up a full, standard master/slave replication setup...

...then...

...identify the tables you don't care about on the replica and for each such table, on the slave:

ALTER TABLE table_name ENGINE=BLACKHOLE;

This will discard all of the data in those tables on the slave and convert them to the blackhole storage engine, which accepts inserts (but doesn't store them), returns empty result-sets from selects, and has "0 rows affected" on updates and deletes... so your schemata are still fully compatible but you're not actually storing anything in the tables you're not interested in.

If you're using mysqldump to initially set up the replica, you can also manually edit the dump files and change the table engine on those tables' declarations.

All of the data will still be "replicated to" the slave, but not "saved on" the slave... the data written to these tables will not be stored.  Writing rows to a blackhole table is a very resource-friendly operation -- I've seen my servers handle 40,000+ queries per second when replicating "into" blackhole tables.

If you're concerned about the fact that the data is still transferred to the replica... or, really, even if you're not, just turn on slave_compressed_protocol on the slave and don't think about it -- consider it the cost of a cleaner replication setup.  When the slave connects to the master, it will negotiate a connection using the MySQL compressed client protocol, substantially reducing the actual bytes of data transferred on the wire between the machines, but still sending everything.


Rebuilding an entire Microsoft SQL Server Database

Use SSMS to generate scripts for the entire database DDL.

Use the script to create a new, empty database.

Use BCP to export the data from all tables.  Use BCP to import that data into the new database.

MAKE SURE YOU BACKUP THE ORIGINAL DATABASE before DROPing it (if you in fact intend to drop it at all).  MAKE SURE YOU TEST RESTORE THE BACKUP.


Self-join example review in Books Online

Looks like you are right in all observations and it is a mistake.

They are two separate tables (not views) and a self-join means a table joined to itself (and even a view or derived table or CTE joined to itself). You might even consider a join between a table and a view derived from that table to be a self-join but it is not the case here either.

I suggest you submit a bug report.

Link: list of AdventureWorks tables.


Moving Postgres 10 data dir on Ubuntu 18

Please check the socket file is in the /tmp. If it is there then create a link to /var/run.

OR,
I prefer this method to move the data directory on Ubuntu.

I hope you have the backup copy of the old directory which is in /var/lib/postgresql/10/main.

service postgresql stop
rm -rf /media/chris/Datenspeicher/*
chown -R postgres:postgres /media/chris/Datenspeicher/
chmod 700 /media/chris/Datenspeicher
/usr/lib/postgresql/10/bin/initdb -D /media/chris/Datenspeicher
rm -rf /media/chris/Datenspeicher/*
sudo rsync -av /var/lib/postgresql/10/main/* /media/chris/Datenspeicher
in postgresql.conf file,  data_directory = '/media/chris/Datenspeicher'
service postgresql start


Your INSERT statement is malformed; you may want to try something like:

Insert into usergroup (user_id, group_id, default_group)
Select ug.user_id, 1234 AS group_id, ug.default_group
from [user_group] ug
  join [group] g on ug.group_id = g.id
where g.name = 'someName';

Replace 1234 in the query above with the id of the group you wish to add for each user who is a member of someName.

A fully flushed out test script for this, including the NOT EXISTS clause you referenced:

USE tempdb;

CREATE TABLE dbo.groups
(
    GroupID INT CONSTRAINT PK_groups
        PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , GroupName VARCHAR(255)
);

CREATE TABLE dbo.usergroup
(
    UserID int
    , GroupID int
        CONSTRAINT FK_usergroup_groups__group_id
        FOREIGN KEY REFERENCES dbo.groups(GroupID)
    , DefaultGroup bit
        CONSTRAINT DF_usergroup__default_group DEFAULT ((0))
    , CONSTRAINT PK_usergroup PRIMARY KEY CLUSTERED (UserID, GroupID)
);

INSERT INTO dbo.groups (GroupName)
VALUES ('test 1');

INSERT INTO dbo.groups (GroupName)
VALUES ('test 2');

INSERT INTO dbo.usergroup (UserID, GroupID, DefaultGroup)
VALUES (1, 1, 1);

DECLARE @NewGroupID INT;
DECLARE @ExistingGroupName VARCHAR(255);
SET @ExistingGroupName = 'test 1';
SET @NewGroupID = 2;

INSERT INTO dbo.usergroup (UserID, GroupID, DefaultGroup)
SELECT ug.UserID, @NewGroupID AS GroupID, ug.DefaultGroup
FROM dbo.usergroup ug
  INNER JOIN dbo.groups g ON ug.GroupID = g.GroupID
WHERE g.GroupName = @ExistingGroupName
    AND NOT EXISTS (
        SELECT 1
        FROM dbo.usergroup ug1
        WHERE ug1.UserID = ug.UserID
            AND ug1.GroupID = @NewGroupID);

As you may notice in the above code, I've used two-part naming to include the schema name when referencing table names.  Also, I've added two variables to the INSERT INTO portion of code to simplify making changes.

I've added USE tempdb; - once SQL Server is restarted tempdb is automatically recreated; effectively dropping the tables I create in this code.

I'm also not calling the group table group, since that is a reserved word.  Using it in code will be a recipe for confusing down-the-road.  I've named it groups in my code above, since that more accurately reflects the content of the table.

Also, I've renamed several of your columns to more accurately reflect the content of those columns.  For instance, you have an id column in the group table.  I've renamed it GroupID, and used that nomenclature everywhere.  This simplifies debugging.


How to prevent pg_dump from generating "SET default_table_access_method=heap;"?

No, there is no such way, because downgrading PostgreSQL is not supported.
You'll have to manually edit the dump and remove lines such as that until the dump loads without error. There could be other, more complicated problems than new parameters, see this question for an example in an older version.
If the dump is large, you could split it in three parts:
pg_dump --format=plain  --section=pre-data  --file=dbname-pre.sql  dbname
pg_dump --format=custom --section=data      --file=dbname-data.dmp dbname
pg_dump --format=plain  --section=post-data --file=dbname-post.sql dbname

You will only have to edit the first and last file, which are always small. The "data" section can be dumped in custom format and will be compressed. After editing, load the files in the same order.


Counting number of ids present in one table which is not presented on another for a particular user

You can get it by using NOT EXISTS

First example:

create table table1 (id int, data varchar(100), user_id int);
insert into table1 values (4, 'some data', 12);
insert into table1 values (5, 'another data', 8);
insert into table1 values (6, 'some another data', 6);
create table table2 (id int, tableid int, user_id int);
insert into table2 values (1, 4, 12);
insert into table2 values (2, 5, 14);
insert into table2 values (3, 12, 12);

SELECT count(*) coincidences
FROM   table1
WHERE  user_id = 12
AND    NOT EXISTS (SELECT 1
                   FROM   table2
                   WHERE  tableid = table1.id
                   AND    user_id = table1.user_id);

| coincidences |
| -----------: |
|            0 |

dbfiddle here

Second example:

create table table1 (id int, data varchar(100), user_id int);
insert into table1 values (4, 'some data', 12);
insert into table1 values (5, 'another data', 24);
insert into table1 values (6, 'some another data', 32);
insert into table1 values (7, 'some more data', 12);
create table table2 (id int, tableid int, user_id int);
insert into table2 values (1, 4, 12);
insert into table2 values (2, 5, 14);
insert into table2 values (3, 12, 12);

SELECT count(*) coincidences
FROM   table1
WHERE  user_id = 12
AND    NOT EXISTS (SELECT 1
                   FROM   table2
                   WHERE  tableid = table1.id
                   AND    user_id = table1.user_id)

| coincidences |
| -----------: |
|            1 |

dbfiddle here


What's the cause for an AlwaysOn AG Secondary Replica to have a high Redo Queue Size and Estimated Recovery Time with a good Redo Rate?

So a number of things here... (e.g. this will be a long answer and may go through a number of iterations):

First and foremost did this AG experience a failover?  If not, then checking the ERRORLOGs on the Primary node is where I suggest you start your troubleshooting process; if a failover did occur, check the ERRORLOGs on the node formally known as the Primary to see what happened.  You can use the clunky UI within SSMS or you can use the undocumented extended procedure xp_readerrorlog, which is my recommendation as it's just quicker to filter out noise this way.  For instance, I'd suggest starting out looking for any references to the AG name like the following and digging from there:

xp_readerrorlog 0, 1, N'>>YOUR AG NAME HERE<<', NULL, '2019-11-18 12:00:00.000', '2019-11-19 15:00:00.000', NULL, NULL

Check the link above for syntax surrounding the command.

Based on what you find, expand the search checking your Windows Event Logs (eventvwr.msc), Cluster Logs (cluadmin.msc), Application logs, etc. using error times as a point of reference if necessary.  I highly suspect these logs will identify what caused the synchronization to stop, be it a cluster-related issue, a hiccup due to maintenance, etc.  Based on that, I suggest posting a new question if you are having a hard time interpreting the results.

Second, running Index Maintenance in an AG will cause AG sync delays.  There's no getting around it, regardless the version of SQL Server (SQL 2019 included) though the new versions tend to recover quicker.  If you feel you must run Index Maintenance and you have the ability to take the application offline, I suggest your run offline Index Maintenance.  Execute this as offline REBUILDs with MAXDOP defined, as offline index rebuild operations don't cause as severe of delays within your AG.  Obviously this approach will cause an outage, so it's not something to do lightly.  I do support an environment where we do this as neglecting index maintenance causes unnecessary growth (and in a multi TB system that causes other problems)

To put it plainly, a common misconception in the SQL Server community is that Index Maintenance is super critical to the performance of a database.  This generally just isn't the case as Index fragmentation has only minimal impact on execution plan behavior. Only when average white space per page approaches egregious levels, does fragmentation start to matter. Honestly, in most cases Index maintenance is basically a very expensive stats update operation, meaning be on top of Stats Updates which don't generally cause blocking and also don't back up AGs.

Finally, if you want to see how far behind secondaries are and get an estimate of how long it'll take them to catch up, give this query a try which is a variant of this code from Jonathan Kehayias:

SELECT ar.replica_server_name,
       adc.database_name,
       ag.name AS ag_name,
       drs.is_local,
       drs.synchronization_state_desc,
       drs.synchronization_health_desc,
       drs.last_redone_time,
       drs.redo_queue_size,
       drs.redo_rate,
       (drs.redo_queue_size / drs.redo_rate) / 60.0 AS est_redo_completion_time_min,
       drs.last_commit_lsn,
       drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc
       ON drs.group_id = adc.group_id AND
       drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
       ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar
       ON drs.group_id = ar.group_id AND
       drs.replica_id = ar.replica_id
ORDER BY
       ag.name,
       ar.replica_server_name,
       adc.database_name;


Can MySQL show the respective timestamps in each INSERT output message?


Setting up DBLink between two Sybase Databases

I think it's a terminology problem. SQLA has the concept of a Remote Server - which could be Oracle, MS SQL Server or another SQLA database. Once you have set up a Remote Server, then you can set up remote tables etc.

It doesn't make any difference to the way you use it whether the other SQLA database is on the same database engine, another engine on the same server or a completely different server. Don't forget that SQLA has no concept of a Master database, every database is autonomous.

There are a lot of details for v12.0.1 at this URL : http://dcx.sybase.com/index.html#1201/en/dbusage/ug-accessrd.html

In Sybase Central you will see an entry for Remote Servers in the object tree. The SQL commands are CREATE SERVER and CREATE EXISTING TABLE.

If you have problems it would be worth asking on the dedicated SQLA site: http://sqlanywhere-forum.sap.com/


Unable to update data from federated table automatically

You can create an event as follows for calling stored procedure in every 5 minutes.

CREATE EVENT myEvent
ON SCHEDULE EVERY INTERVAL 5 minute
DO CALL myProcedure();

Here i am creating full process of executing an event with example.

step 1: checking user permissions.user should contain grant permission as     follows.

   show grants for root@'localhost'\G
   Result:`GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION.
step2: creating an database with two tables as you mentioned tab and fed_tab.

step3: Creating and executing  stored procedure.

DELIMITER $$

DROP PROCEDURE IF EXISTS `HOD`.`fed_procedure` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `fed_procedure`()
BEGIN

  declare tab_indx integer(11);

  set tab_indx = (SELECT id FROM tab order by id desc limit 1);
  insert into tab select * FROM fed_tab where id>tab_indx;

END $$

DELIMITER ;

step4: Creating an event.

 CREATE EVENT myEvent
ON SCHEDULE EVERY  5 minute
DO CALL fed_procedure();

it is successfully executing in my machine.the main problem of yours is permission of root user.


UOW's: Relating UOW's listed in MSDTC to SQL Server info

You can get the correlation you're looking for in the sys.dm_tran_active_transactions DMV.
The column is transaction_uow.

http://msdn.microsoft.com/en-us/library/ms174302.aspx


Open source tools to manage Data Quality

DataCleaner is pretty good (spoken like Larry David)

http://datacleaner.org/

Integrates w Pentaho Data Integration (Kettle) too


This is problematic the program is declaring a query whose results will be spooled on the server. The the program will call for one record at a time from the result set. The literal variables (p1,p2,p5,p6,p7) should be related to the configuration of the system stored procedure. The stringed variables (p1-p7) are represented by the last 7 terms of the procedure call.

    exec sp_cursorprepexec
        @p1 output, -- plan handle for statement after its prepared
        @p2 output, -- cursor, the the program can retrieve one row at a time from the result set
        N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int',--parameters for execution
        N'<<query text params here reference the list above>>', --statement, parameters here are the ones form the string list above
        @p5 output, --
        @p6 output, -- configuration terms see the docs
        @p7 output, --
        -- sql server will compile the query with unknown values for @P1 - @P7
        -- these values will be substituted for @P1-@P7 after its compiled
        1, --@P1
        4,
        1,
        3,
        1,
        8,
        1 --@P7

docs for this http://msdn.microsoft.com/en-us/library/Ff848775%28v=sql.105%29.aspx


If you force indexes and tables into RAM by using a ramdisk, all you're likely to be doing is wasting RAM.

PostgreSQL uses the operating system's buffer cache. So when a table/index page is first read, it's cached by the OS, and future reads are returned by the OS cache, not fetched from disk. If the database is bigger than the available memory then the OS will throw oldest, least-used data away and it'll get read from disk next time it's needed.

If you create a ramdisk, you are reducing the amount of memory the OS can use for cache. It'll still cache each block read from the ram disk before returning it to PostgreSQL. If there's not enough space left for efficient memory management of the buffer cache the kernel will do a lot of work trying to free pages for buffered reads, and your ramdisk will actually slow things down.

For writes, the main effect the ramdisk has is to make all disk sync operations effectively no-ops. So it's like running PostgreSQL with fsync=off, where it never tries to flush writes to disk at all. It has the same property of being able to eat your data whenever it feels like it, too.

There's generally not much benefit in running PostgreSQL on a ramdisk vs running it with data durability settings turned off, a very high dirty writeback threshold, etc.

(On a side note, if you create a tablespace on a ramdisk and put anything in it, you're making the whole database cluster - i.e. all databases on that server - at risk of severe data loss. Don't do it. Either initdb the whole database on the ramdisk or, preferably, just tune the server so you don't need to mess about with ramdisks.)


You'd typically use tinyint which is 1 byte too

char(1) will be slightly slower because comparing uses collation
confusion: what is S: SUV or Saloon or Sedan or Sports?
using a letter limits you as you add more types. See last point.
every system I've seen has more then one client eg reporting. The logic of changing V, S into "Van", "SUV" etc will need repeated. Using a lookup table means it is a simple JOIN
extendibility: add one more type ("F" for "Flying car") you can one row to a lookup table or change a lot of code and constraints. And your client code too because this has to know what V, S, F etc are
maintenance: logic is in 3 places: database constraint, database code and client code. With a lookup and foreign key,it can be in one place

On the plus side of using a single letter... er, don't see any

Note: there is a related MySQL question about Enums. The recommendation is to use a lookup table there too.


When might a tsvector field pay for itself?

If you use proximity searching (like "phraseto_tsquery" for example), with a functional index it will have to re-parse each match candidate document to a tsvector and check it for the right sequence and spacing of words.  This can be quite slow, especially if the number of candidates is much higher than the number of final results.  If the tsvector is stored, it can just read it and not reparse the document, this is much faster.  I think other advanced features like "ts_headline" may be in the same situation--but I haven't tested them.

Even if you just use "@@", I think that if the bitmap of the number of results doesn't fit in "work_mem", then it will also need to reparse the documents in order to recheck candidate matching blocks which "overflowed".  Of course in that case increasing "work_mem" would be likely be a better option than adding a column.

For what it is worth, if you use RUM rather than GIN, it will solve the the problem with phraseto_tsquery on functional indexes.


Handle expired SQL login's passwords in SQL Server

Complementing to Julien's answer, you can be proactive to schedule below tsql as a job to alert you on daily basis especially on DaysUntilExpiration of LOGINPROPERTY e.g. get all the logins from your database server that are going to expire in 2 days.

SELECT name as LoginName,
create_date as LoginCreateDate,
modify_date as LoginModifiedDate,
is_policy_checked ,
is_expiration_checked ,
-- returns the number of days until the password expires.
LOGINPROPERTY(name, 'DaysUntilExpiration') DaysUntilExpiration,
LOGINPROPERTY(name, 'PasswordLastSetTime') PasswordLastSetTime,
LOGINPROPERTY(name, 'IsExpired') IsExpired,
LOGINPROPERTY(name, 'IsMustChange') IsMustChange
From sys.sql_logins
--- optional filter
-- where LOGINPROPERTY(name, 'DaysUntilExpiration') >= 2


Why are my Log Ship Jobs Blocking Each Other

What I think you can try here is to change the run times of each of your log shipping jobs.
If you have 5 LS backup jobs running at the same time, change to time to 1 minute/2 minutes apart, the same to the LS restores and copy jobs.


A computed column would solve the issue and probably be better than having triggers:

CREATE TABLE #Moofasa
(
  gotitdate DATE,
  dunitdate DATE,
  delayed AS COALESCE( DATEDIFF(day, gotitdate, dunitdate), 0)
) ;

Tested at: rextester.com:

INSERT INTO #Moofasa
  (gotitdate, dunitdate)
VALUES
  ('2016-07-01', '2016-07-05'),
  ('2016-07-01', '2016-07-04'),
  ('2016-07-04', '2016-07-08'),
  (NULL,         '2016-07-09'),
  ('2016-07-09', NULL),
  (NULL,         NULL)  ;

SELECT *
FROM #Moofasa ;

gives:

gotitdate   dunitdate   delayed
01.07.2016  05.07.2016  4
01.07.2016  04.07.2016  3
04.07.2016  08.07.2016  4
NULL        09.07.2016  0
01.07.2016  NULL        0
NULL        NULL        0


Scaling for MariaDB / Percona / Galera Scaling

Rolando may be right, or I may be right; it depends on the situation...

A single Galera cluster (PXC or MariaDB or hand-rolled) can have more than 3 nodes, but probably should not have a lot more than that.
Writes somewhat scale with Galera.  Especially if you have complex transactions that are heavy on reads.  In that case, the transactions are spread across the nodes, hence the reads are spread around.
Slaves hanging off each node can provide arbitrary read scaling.
In WAN setups, writes are faster in certain situations.  But perhaps you are talking only about LAN?
"low memory" and "disk I/O issues" are pretty much the same with or without Galera.  Maybe you want to address them separately?
Slow reads are usually caused by too many connections, poor indexing, poor SELECT formulation, etc.  Again, this is generally independent of Galera.
Adding a node to a Galera cluster is "trivial".  (Well not quite, but much of the work is automated.)

The only real way to get write scaling is via Sharding.  There is no "automatic" sharding (aside from Spider and Clustrix), and I am suspicious about how "automatic" they can be.


Oracle's transactions are implicit in sqlplus you're already in one and don't have to start it. This means you can just do

select ...
   update ..
   rollback ...
   select...

if you do an exit without doing exit rollback it will commit on exit. begin is not necessary here.


What performance advantages does append-only postgres allow?

If you wish to benefit from index-only scans (IOS), you will still need to vacuum your tables even if they are append-only.  Indeed, autovacuum was never adjusted for the needs of IOS, so it is likely you will need to schedule your own vacuums for IOS to be maximally effective on append only tables.  Certainly don't disable autovac, it will naturally not do stuff when there is nothing for it to do so no good will come from disabling it.

Other than that, I don't think there is much you can do.  This situation will greatly benefit from the freeze map introduced in 9.6, but there is nothing you need to do to activate that, other than use a version at least that high.


Which is better: many join conditions or many where conditions?

If we consider that you use INNER JOIN instead of LEFT JOIN(which appears to be your intent), these two queries are functionally equivalent.  Query optimizers will review and evaluate criteria in your WHERE clause and your FROM clause and consider all of these factors when building query plans in order to reach the most efficient execution plan.  If we do an EXPLAIN on both statements, we get the same result:

Query 1:

EXPLAIN
SELECT
  tableA.ColA
  ,tableA.ColB
  ,tableA.ColC
  ,tableA.ColD
  ,tableA.ColE
FROM tableA
  JOIN tableB ON tableA.ColA=tableB.ColA
WHERE
  tableA.ColB=tableB.ColB
  AND tableA.ColC=tableB.ColC
  AND tableA.ColD=tableB.ColD
  AND tableA.ColE=tableB.ColE

[Results]:

| ID | SELECT_TYPE |  TABLE | TYPE | POSSIBLE_KEYS |    KEY | KEY_LEN |    REF | ROWS |                          EXTRA |
------------------------------------------------------------------------------------------------------------------------
|  1 |      SIMPLE | tableA |  ALL |        (null) | (null) |  (null) | (null) |    1 |                                |
|  1 |      SIMPLE | tableB |  ALL |        (null) | (null) |  (null) | (null) |    1 | Using where; Using join buffer |

Query 2:

EXPLAIN
SELECT
  tableA.ColA
  ,tableA.ColB
  ,tableA.ColC
  ,tableA.ColD
  ,tableA.ColE
FROM tableA
  JOIN tableB ON tableA.ColA=tableB.ColA
  AND tableA.ColB=tableB.ColB
  AND tableA.ColC=tableB.ColC
  AND tableA.ColD=tableB.ColD
WHERE
  tableA.ColE=tableB.ColE

[Results]:

| ID | SELECT_TYPE |  TABLE | TYPE | POSSIBLE_KEYS |    KEY | KEY_LEN |    REF | ROWS |                          EXTRA |
------------------------------------------------------------------------------------------------------------------------
|  1 |      SIMPLE | tableA |  ALL |        (null) | (null) |  (null) | (null) |    1 |                                |
|  1 |      SIMPLE | tableB |  ALL |        (null) | (null) |  (null) | (null) |    1 | Using where; Using join buffer |

You can review the full details with the following links.  I also created a SQL 2008 example so that you can compare how the two engines work (which is the same):

MySQL query example

SQL 2008 query example (Make sure you 'View Execution Plan' for both results)


Unable to delete SQL Server 2008 R2 login

If you have many jobs and do not know which job is owned by this login, this query will tell you the name of jobs owned by them:

SELECT j.name
FROM msdb.dbo.sysjobs AS j
INNER JOIN sys.syslogins AS l ON j.owner_sid = l.sid
WHERE l.name = 'loginYouWantToDelete'

Once you have found the job, you can delete it using: sp_delete_job: (From MSDN)

EXEC msdb.dbo.sp_delete_job
    @job_name = N'JobToDelete';


Central Management Server - Query against host server

I assume that we are indeed talking about Central Management Servers here. And not Master/Target Agent or something else which isn't related to CMS. I see a lot of confusion in this area. Here are a few options:

Skip the CMS. You can register whatever SQL Servers you have in the traditional way (locally) and use a multi server query window (MSQW) for the local server group. Again, I frequently see folks believing that MSQW can only be used with CMS.

Just register the SQL server wherever you want to have it in the CMS tree, but use the IP instead of the machine name. SSMS won't understand that it is the same machine.

Configure an alias on the client, and use that alias name in the server registration. Obvious downside is that all clients where you want to use the CMS list need to have that alias. Sort of defeats the purpose of CMS in the first place.

Perhaps an additional DNS entry pointing to your server? I'm no DNS person, so I can't say if you can have two DNS entries pointing to the same IP.

I'd go for the IP address in the CMS registration. In fact, I did exactly just that a few days ago with a client.


There is no way to limit the abilities of the SYS user. It is a super user account, similar to root on a UNIX operating system.

If a user changes the logging method, this act will be logged. The last OS-level log will show the act of changing it. So your DBA will not be able to disable logging without leaving a record.

Note that the auditing features of Oracle are greatly improved in version 12c. If you can upgrade, you probably should.


There probably is no way to change the partitioning without taking downtime.  If you can block just writes, then this would allow concurrent reads:

disable writes (not via SQL)
CREATE TABLE new ... PARTITION BY RANGE ...
INSERT INTO new
    SELECT * FROM real;   --  probably wise to do in chunks
test new table
RENAME TABLE real TO old, new TO real;  -- Fast and atomic
DROP TABLE old;
enable writes

But -- Before embarking on the rebuild, please show us SHOW CREATE TABLE (both by-hash and by-range) and the important SELECTs.  Also, explain the purpose of using Partitioning.  It may be that Partitioning, even by-range, is not worth doing.  (In my experience by-hash is useless.)

My comments on partitioning (perhaps relevant):  http://mysql.rjweb.org/doc.php/partitionmaint


Find Max Value for each month for the last 3 months, properly

For SQL Server, you could do something like this.

DECLARE @t TABLE (Id INT, DateVal DATETIME, ValueVal DECIMAL(18, 9));

INSERT @t ( Id, DateVal, ValueVal )
SELECT Id, DateVal, ValueVal
FROM (
    VALUES
    (12410,   '01/03/2017 12:17',    0.000178),
    (12410,   '01/10/2017 11:36',    0.000186),
    (12410,   '01/17/2017 11:27',    0.000189),
    (12410,   '01/24/2017 13:09',    0.000182),
    (12410,   '01/31/2017 10:37',    0.000169),
    (12410,   '02/07/2017 11:03',    0.000214),
    (12410,   '02/14/2017 11:52',    0.000176),
    (12410,   '02/21/2017 10:51',    0.000200),
    (12410,   '02/28/2017 12:29',    0.000194),
    (12410,   '03/07/2017 08:39',    0.000206)
) x (Id, DateVal, ValueVal);

SELECT  DATEPART(MONTH, t.DateVal) AS [DateVal],
        MAX(t.ValueVal) AS MaxVal
FROM @t AS t
WHERE t.DateVal >= DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY DATEPART(MONTH, t.DateVal);

Updated WHERE clause:
Flattening dates is weird, and my previous query got you today's date minus three months. To get back to the first of three months ago, you have to turn some tricks.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS [First Of This Month],
       DATEADD(MONTH, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AS [First Of Three Months Ago]

You can either take some time trying to understand this, or keep a cheat sheet of how to do it handy so you don't have to remember awful date math ;)


Position of a race result using database query?

You can use a variable and CROSS JOIN it with your table. You should increment the variable value for each returned record.

Keep in mind you need to set the correct order on your subquery.

order by duration asc

I've set up the next example:

drop table if exists course;
create table if not exists course(id int, duration time);
insert into course values
(1, '01:20:00'),
(2, '01:21:02'),
(3, '01:19:02'),
(4, '01:22:02'),
(5, '01:17:05'),
(6, '01:23:02'),
(7, '01:12:02'),
(8, '01:16:02'),
(9, '01:15:02');

select id, duration, @position := @position +  1 as position
from
       (select @position := 0) x,
       (select id, duration
        from course
        order by duration asc) y;

drop table if exists course;

And this is the result:

| id | duration | position |
|---:|---------:|----------|
| 7  | 01:12:02 | 1        |
| 9  | 01:15:02 | 2        |
| 8  | 01:16:02 | 3        |
| 5  | 01:17:05 | 4        |
| 3  | 01:19:02 | 5        |
| 1  | 01:20:00 | 6        |
| 2  | 01:21:02 | 7        |
| 4  | 01:22:02 | 8        |
| 6  | 01:23:02 | 9        |

Rextester here.


Can I add an Arbiter node in the same server of my Config-Server?

Yes. You can add an Arbiter and a Config Server in the same machine.

Generally, you should avoid setting 2 DB process in the same machine because:

Its harder to provision/estimate the machine resources for 2 process running simultaneously
Its usually cheaper to have 2 low-end machines than 1 high-end (horizontal x vertical scaling).
Some resources can't scale in your environment. E.g: network bandwidth.

However, if you want to share resources only with an Arbiter and Config nodes, since they require much less resources then a Primary node, this configuration is quite good.

The following article shows how you could set up your environment with nice images:
http://www.kchodorow.com/blog/2010/08/09/sharding-and-replica-sets-illustrated/

Setup: 3 replica sets

M = Master
S = Slave
A = Arbiter

Configuration: 3 servers

C = Config Server
No letter = mongos

Since this configuration mixes things, you can achieve high availability and sharding using just 3 servers.


This should be faster:

UPDATE master
  SET "count" = t.cnt
FROM (
   SELECT master_id, COUNT(*) as cnt
   FROM detail
   group by master_id
) as t
where t.detail.master_id = master.id
  and t.cnt <> master."count";

This assumes that master.id is unique.


Mongodb create user with only read and write access

I look like that you haven't enabled authentication (--auth) at the database. Without authentication, there is no authorization. That's why all users can do now whatever they want.


Making an Oracle XE instance network wide available

You should only install server software (i.e. Oracle 18c XE) on the server. You should install client software (i.e. Instant Client or full OCI Client) everywhere else. While the server software includes the client, it is intended for local access, or to allow servers to talk to each other. There is no need to (indeed every reason not to) install the full XE server on every client host.
For your server network configuration files, the defaults associated with an XE setup should be fine. The only thing you'd need to double check is that you don't have a firewall blocking access to the server port (1521 by default) for remote clients.


How to represent a list of entities within a table of the same entity in PostgreSQL?

There's a couple of ways you can go about this but the most relational and normalized way would be to create a second table called UserFriendList with the columns UserId and FriendUserId which would store one row per Friend for each User. This table would be one-to-many from User.Id to UserFriendList.UserId but would also be able to help bridge the join back to the User table on UserFriendList.FriendUserId to User.Id to get all the User attributes of the friends. This kind of table is known as a bridge / junction / linking table.
Example query with this design:
SELECT
    User.Id AS UserId, User.FirstName AS UserFirstName, User.LastName AS UserLastName,
    Friend.Id AS FriendUserId, Friend.FirstName AS FriendFirstName, Friend.LastName AS FriendLastName
FROM User
INNER JOIN UserFriendList
    ON User.Id = UserFriendList.UserId
INNER JOIN User AS Friend
    ON UserFriendList.FriendUserId = Friend.Id

Alternatively you can store the FriendList column directly on the User table as either a comma delimited list or in JSON, but these are both denormalized solutions, which will become harder to maintain changes, potentially lead to data redundancy, and will inflate the size of your User table which could make querying it less efficient.