Are Delete operations written to AppendOnlyFile in Redis?

Yes. Every modification (also delete) of data gets written to AOF. See Redis persistence demystified blog posting.

  Append only file The Append Only File, usually called simply AOF, is
  the main Redis persistence option. The way it works is extremely
  simple: every time a write operation that modifies the dataset in
  memory is performed, the operation gets logged. The log is produced
  exactly in the same format used by clients to communicate with Redis,
  so the AOF can be even piped via netcat to another instance, or easily
  parsed if needed. At restart Redis re-plays all the operations to
  reconstruct the dataset.

  To show how the AOF works in practice I'll do a simple experiment,
  setting up a new Redis 2.6 instance with append only file enabled:

./redis-server --appendonly yes

  Now it's time to send a few write commands to the instance:

redis 127.0.0.1:6379> set key1 Hello
OK
redis 127.0.0.1:6379> append key1 " World!"
(integer) 12
redis 127.0.0.1:6379> del key1
(integer) 1
redis 127.0.0.1:6379> del non_existing_key
(integer) 0

  The first three operations actually modified the dataset, the fourth
  did not, because there was no key with the specified name. This is how
  our append only file looks like:

$ cat appendonly.aof
*2
$6
SELECT
$1
0
*3
$3
set
$4
key1
$5
Hello
*3
$6
append
$4
key1
$7
 World!
*2
$3
del
$4
key1

  As you can see the final DEL is missing, because it did not produced
  any modification to the dataset.


pgAgent doesn't run on another database than "postgres"

The solution is that the maintenance DB's server (when you right click on server --> properties --> the 5th row) is postgres and it might be the other database where you want to run pgagent jobs.


Clustered Index fragmentation vs Index with Included columns fragmentation

Your questions:
Doesn't an Index with included columns have the exact same problem?
Yes.
Is a table with included columns not just the same as a "shadow table" with the same fragmentation problems?
Yes
Should I migrate to use UserId, TipIndex as a ClusteredIndex instead of Id?
I would, yes.
How to prevent fragmentation?
There are a couple of different types of fragmentation to consider. One is when you only have part of your pages being used because you’ve had page splits. If you have a lot of inserts, this will happen. Don’t stress too much. The other is when you have pages where the subsequent page is in a different extent. Again, I wouldn’t worry too much. If your data is mostly in the buffer cache, it doesn’t really matter if it moves across extents.
So... don’t worry about it too much. But don’t bother having a complete copy of the data in a way that you won’t actually be querying it.


Intermittent "Login timeout expired" after migrating to VmWare SQL Server

I am here answering my own question to help those who are experiencing the same behavior. After many observations and experiments with searching over the internet, I found the solution to this issue following the next steps.
Make sure you test everything before going to production.

Disable TCP Chimney features on the both the SQL Server & Client application server:
Disable TCPChimney and Offload features on the system level registries.

You can do it by running the command  "Netsh int ip set chimney DISABLED" from command prompt. But you can do it manually.

Go to the registry "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters"

a.    Double-click the EnableTCPChimney registry entry. If any entry is not present, create it by right clicking on the blank space and click on Create DWORD (32 bit).
b.    In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
c.    Double-click the EnableRSS registry entry.
d.    In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
e.    Double-click the EnableTCPA registry entry.
f.    In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
g.    Create the registry key “SynAttackProtect” and – In the Edit DWORD Value dialog box, type 0 in the Value data box, and then click OK.
h.    Restart the server.

Disable TCPChimney and Offload features in the properties of NIC card.  Below are the instructions.

a.    Click Start, click Run, type ncpa.cpl, and then click OK.
b.    Right-click a network adapter object, and then click Properties.
c.    Click Configure, and then click the Advanced tab.
d.    In the Property list, click Receive Side Scaling, click Disable in the Value list, and then click OK.
e.    In the Property list, click TCP/IP Offload, click Disable in the Value list, and then click OK.
f.    Repeat steps 2 through 5 for each network adapter object.

Check if “IPv4 Checksum Offload” & “IPv4 Large Send Offload” OR “Checksum Offload” & “Large Send Offload” are enabled under Advanced Settings of NIC.  If they are, disable them. These options may vary with the model and make of the NIC.   The other properties to look for and disable are:

i.    Offload Receive IP Checksum
ii.   Offload Receive TCP Checksum
iii.  Offload TCP Segmentation
iv.   Offload Transmit IP Checksum
v.    Offload Transmit TCP Checksum

Related articles:

Information about the TCP Chimney Offload, Receive Side Scaling, and Network Direct Memory Access features in Windows Server 2008:    http://support.microsoft.com/kb/951037
Error message when an application connects to SQL Server on a server that is running Windows Server 2003: "General Network error," "Communication link failure," or "A transport-level error":         http://support.microsoft.com/kb/942861

Regarding the performance impact:

TCP Chimney Offload – Possible Performance and Concurrency Impacts to SQL Server Workloads:        http://blogs.msdn.com/b/psssql/archive/2008/10/01/windows-scalable-networking-pack-possible-performance-and-concurrency-impacts-to-sql-server-workloads.aspx

  Note: This would trigger the network breaking for a couple of seconds,
  so schedule this accordingly. Any registry changes would come into
  effect only after a reboot of the machine is done. Also if were
  disabling any NIC card property, the network will go down for a couple
  of seconds for the changes to reflect before it can resume back. So if
  this is a PRODUCTION box, schedule this during your downtime or
  Maintenance window

TCP TIME-WAIT Delay & MaxUserPort :
The following changes needs to be done on machines from where we connect to the SQL Server from the client applications. Add this on your application / web servers universally.

5. TCP TIME-WAIT Delay:
Set the TcpTimedWaitDelay registry key that is available in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters to 30 secs.
If the registry key is not present, create them and set it to the value that we had suggested above.

6.       MaxUserPort:
Set the MaxUserPort registry key that is available in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters to 65,000.
Again if the registry key is not present, create them and set it to the value that we had suggested above.

Refer the following article for more information:
TCP TIME-WAIT Delay: https://technet.microsoft.com/en-us/library/cc938217.aspx
MaxUserPort: https://technet.microsoft.com/en-us/library/cc938196.aspx


SQL Server service account Windows privileges and rights

I quite often have to setup MS SQL Server and wondered if anyone can
  provide advice on configuring the accounts the services should run as.
  IMO this has been vaguely documented by Microsoft, while they point
  you in the right direction I have never been able to find any concrete
  examples.

It's actually documented quite thoroughly: http://msdn.microsoft.com/en-us/library/ms143504.aspx

Is there a part of that you're not sure about?

  For simple deployments\development environments it is OK to use the
  virtual account defaults the installer uses: e.g. NT
  SERVICE\MSSQLSERVER

This is going to depend on the environment. I, personally, hate finding a server someone setup using a local account and asking to get access to network resources some time in the future, among other issues.

  For production and in domain environments it's recommended to use
  either a Managed Service Account, or create a domain user account(not
  an admin) for each service.

Again, depends, but generally I would agree (a counter example would be availability groups where it makes sense to use a single domain account across all instances).

  Allegedly if you use a domain account at installation time the
  installer will set any required permissions for you.

Unless there is a failure, etc, it will do so. I'm not sure why the "Allegedly" part.

  If changing the service account on an existing install from a virtual
  account to a domain account the recommendation is to use the SQL
  Server configuration manager to set the new service accounts.
  Allegedly this will set any required permissions for you.

When changing any of the services for SQL Server, always use SSCM. Always. Period. It will set the permissions for the new account to the basics. If before the local system account was used and unrestricted permission to everything on the system was had, I would expect something to fail permissions after the change due to tighter controlled security. That's not a SQL Server SSCM fault, that's an admin fault of not granting proper EXTRA permissions (such as accessing a network share, restricted folders, items outside of the SQL Server install purview, etc.)

  I just tried changing the service account in an existing install to a
  domain account and it would give me a logon failure until I granted
  the account 'log on as service' permission, which contradicts the part
  where the SQL Server configuration manager will set any required
  permissions. (Although im not sure if a GPO may have interfered with
  setting this local security policy)

Sounds like a GPO is causing an issue (IMHO). Wouldn't be the first time :)

  So my question is, if you create a new domain user account for each of
  the SQL Server processes what permissions should be set for each
  account?

I would explicitly set any permissions outside those stated in the msdn link I have above (also given by @joeqwerty and in your OP). For example, on a "backup" folder on a network share, on a new drive added to hold new databases (where setup was already run but the drive didn't exist), etc.

  But it's not clear to me if that is something I should be doing
  manually for the user I create to run the service as, or whether using
  the SQL config manager should automatically set these permissions.

Unless something is extremely broken with the server, these shouldn't have to be manually given.


Is necessary to create a new table in this context?

If the same ProjectDescription can be used by multiple different Templates then you'll need a third table, as a linking table, for the many-to-many relationship between the two entities. Otherwise you'd be forced to store duplicate rows in ProjectDescription which is bad design from a Normalization standpoint.
The linking table would be simple and just store template_id and projectdescription_id, and the primary key would be a composite key on both those fields.


Is there a difference between text_pattern_ops and COLLATE "C"?

The manual:

Note that you should also create an index with the default operator
class if you want queries involving ordinary <, <=, >, or >=
comparisons to use an index. Such queries cannot use the
xxx_pattern_ops operator classes. (Ordinary equality comparisons can
use these operator classes, however.) It is possible to create
multiple indexes on the same column with different operator classes.
If you do use the C locale, you do not need the xxx_pattern_ops
operator classes, because an index with the default operator class is
usable for pattern-matching queries in the C locale.

So, the index with COLLATE "C" does everything a text_pattern_ops would do,  plus more which the latter cannot. Like support this query:
SELECT * FROM t
WHERE name >= 'z' COLLATE "C";

COLLATE "C" must be specified explicitly in the query to make the COLLATE "C" index applicable (unless you operate with "C" collation to begin with, but then why the index?)
The above query requires the feature of "per-column collation support" (as does your index). The feature was added with Postgres 9.1. The operator class text_pattern_ops is much older and made a lot of sense before Postgres 9.1.
Since Postgres 9.1 the COLLATE "C" variant is superior as it is more versatile. The only reason I could think of would be to intentionally not support additional use cases for some odd reason. The manual might actually add a deprecation notice for the operator classes xxx_pattern_ops.


No, the SPID (Server Process ID) is assigned the moment an application establishes a connection/session to the database and is retained until the connection/session ends.


Unfortunately you can't use a trigger to update the table that called the trigger: from the documentation:

  A stored function or trigger cannot modify a table that is already
  being used (for reading or writing) by the statement that invoked the
  function or trigger.

Could you move the admin field to a separate table which just holds the current admin id (or list of), and then you could update that with the trigger.

(of course if your admin is always id:1000, then you probably don't need the trigger to start with)

You would also need to consider whether to fire this AFTER INSERT and/or UPDATE.

An example trigger would be:

DELIMITER $$
CREATE TRIGGER update_user_admin
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF new.id = 1000 THEN
        UPDATE userAdmin SET id = new.id;
    END IF;
END;
$$
DELIMITER ;


How to make inter-database queries with dynamic SQL Server database name?

Not sure I fully understand all of the variables but I suspect you would be able to use synonyms that are different between the two environments which would allow the code to be the same. It is a layer of abstraction that requires documentation and/or an extra layer of the onion to peel, but can definitely simplify the code you write. Here is the CREATE SYNONYM topic... I have used them extensively so please let me know if you have further questions about them.


Migrate a schema structure from one server to another MySQL

If You already use MySQL Workbench, You can use menu:
Database -> Synchronise with any Source

Before apply changes it show You script, which You can copy and if need edit
You can test it on copy of databases

Also there are many other tools:

Navicat
SQLyog
dbSchema
and etc

example of script:

ALTER TABLE `test_db`.`company1_name`
ADD FULLTEXT INDEX `Company1_name` (`Company1_name` ASC)


Disk usage spikes: good, bad or indifferent?

Indifferent.

SQL Server batches updates - and especially dirty writes with so called checkpoints. This is pretty much normal behavior and only a problem when it turns into a problem (i.e. too large, slowing down the system etc.)


There are two extensions of RAISERROR that may help:

To write it to the error log:

DECLARE @msg NVARCHAR(1000) = N''
RAISERROR(@msg, 16, 1) WITH LOG;

To display it in the messages tab in "real time":

DECLARE @msg NVARCHAR(1000) = N''
RAISERROR(@msg, 16, 1) WITH NOWAIT;


How to dispose of large tables with the least impact to log shipping?

If you DROP the tables, it'll cause minimal logs to be generated because it doesn't immediately force garbage collection to occur on the used space, it merely basically just removes the reference. You similarly can accomplish the same outcome with TRUNCATE (though I think on large tables there could be more overhead than DROP, I'll have to research this and update my answer accordingly).
This is a really good article that details why TRUNCATE and DROP operations are seemlessly quick and generate minimal logs, which is due to a feature of SQL Server called deferred drop.
Either way, avoid DELETE otherwise that will generate a ton of logs that will need to get shipped and reapplied at the target database.
This StackOverflow Question has multiple answers with good information about efficiently removing data from a large table (some maybe not specificly related to your question, but good for future reference or other users with similar problems).


slow index creation with gist and gist__int_ops

I have also had this problem, and have never found a solution, other than "don't use gist__int_ops".  The picksplit or penalty function seems to go pathologically wrong on large tables.  I don't know if this is a bug someone could fix with enough effort, or if it is an inherent problem.

  (since it should work better on frequently updated /inserts table)

I don't know how good this advice actually is (or ever was).  Note that it was removed from the docs in version 9.5.  But if you can never get the build to finish anyway, then surely it can't be good advice for your situation.


How do I add a composite check constraint that references a composite key?

A check constraint that references another table like that is called a FOREIGN KEY. You can do what you want with MATCH FULL

As a special note the types must be the same, so in your example tasks.users_id is text. It must be int (the underlying type of users.id).

CREATE TABLE users (
  id               SERIAL,
  name             TEXT NOT NULL,
  email            TEXT NOT NULL,
  organization_id  int,
  PRIMARY KEY (organization_id, id),
  UNIQUE (name, email)
);

CREATE TABLE tasks (
  id               SERIAL PRIMARY KEY,
  organization_id  int  NOT NULL,
  users_id         int  NOT NULL,
  description      TEXT NOT NULL,
  FOREIGN KEY (organization_id, users_id)
    REFERENCES users (organization_id, id) MATCH FULL
);

You can add the constriant after the fact like this..

ALTER TABLE tasks
  ADD CONSTRAINT asdf
  FOREIGN KEY (organization_id, users_id)
  REFERENCES users (organization_id, id)
  MATCH FULL;


How to create Select query function in the Postgresql?

Function Execute like this And get my Answer

select * from functionname();


postgres how to drop all the table in a schema through command

You want to use the CASCADE option of DROP SCHEMA. From the documentation:

  CASCADE  - Automatically drop objects (tables, functions, etc.) that are
  contained in the schema, and in turn all objects that depend on those
  objects

BE CAREFUL - emphasis above mine.

Obviously you'll need to recreate the schema afterwards.

To just drop all tables in the current schema, you can use this script:

DO $$ DECLARE
    tabname RECORD;
BEGIN
    FOR tabname IN (SELECT tablename
                    FROM pg_tables
                    WHERE schemaname = current_schema())
LOOP
    EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(tabname.tablename) || ' CASCADE';
END LOOP;
END $$;

Change WHERE schemaname = current_schema() if you want to drop all of the tables in a different schema.


Is it possible to determine what part of a stored procedure is currently executing?

You can follow along with either a SQL Server Trace or Extended Events.

You can watch completed statements and/or batches, and that should give you the granularity you are looking for.

SP:StmtCompleted Event Class


Is MongoDB index creation single threaded?

There is an OPEN issue already created in MongoDB JIRA for the same.

As per the issue, MongoDB index creation is not Multi-Threaded.

use multiple cores for index sort-phase

Multi-threaded index creation

UPDATE

Try increasing the maxIndexBuildMemoryUsageMegabytes parameter value.

The default value is 500 MB.

What does it do?

Limits the amount of memory that the simultaneous foreground index builds on one collection may consume for the duration of the builds.

So by increasing this limit, may increase the performance of index creation. For Example:

db.adminCommand( { setParameter: 1, maxIndexBuildMemoryUsageMegabytes: 70000 } )


DBMS_METADATA.GET_DDL not displaying complete DDL for big tables

Just set the "long" in the sql sheet window big enough to hold the desired ddl.

Example:

set long 1000
select dbms_metadata.get_ddl('TABLE', 'XP','OPS$RM74384') from dual;

-- Then just hit Run Script button or press f5.


The documentation says:

  ⓘ Note

  FOR SYSTEM_TIME filters out rows that have period of validity with zero duration (SysStartTime = SysEndTime). Those rows will be
  generated if you perform multiple updates on the same primary key
  within the same transaction. In that case, temporal querying surfaces
  only row versions before the transactions and ones that became actual
  after the transactions. If you need to include those rows in the
  analysis, query the history table directly.

You have typed SysStartTime and SysEndTime as datetime2 with no fractional seconds. This increases the chance they will compare equal, giving a "period of validity with zero duration".

You can see the history rows exist by looking in the history table directly:

SELECT * FROM [dbo].[MyTable_History];

The only issue is that the FOR SYSTEM_TIME query does not show rows where SysStartTime and SysEndTime are equal.

db<>fiddle demo


SQL Conditional Join - remove duplication from Stored Procedure

Your code looks ok. Conditional join would lead you to parameter sniffing issue and you used one of two most popular solutions to avoid it.
Also you may want to use the second approach - dynamic sql, something like this:

DECLARE @sql NVARCHAR(max);

SET @sql = N'
WITH Catalog_Search_Results(FIM_NO,Rnk)
AS
(
    SELECT I.FIM_NO, 0
    FROM IMF AS I
    '
IF @Search IS NOT NULL
   SET @sql = CONCAT(@sql, 'INNER JOIN dbo.Catalog_ItemSearch(@Search) funcItemSearch ON I.FIM_NO = funcItemSearch.FIM_NO');

SET @sql = CONCAT(@sql, ')
SELECT FIM_NO, Rnk
    FROM Catalog_Search_Results
    ORDER BY Rnk desc')

EXEC sp_executesql @sql, N'@Search varchar(100)', @Search


How can I get a single row on a inner join not to double my sum

You could use multiple SELECT Statements, like this:

SELECT CHT.ID,
   (SELECT SUM(TOTAL_E + TOTAL_F + TOTAL_STOC)
   FROM DEP_DEZ WHERE FK_ID_CHESTIONAR = cht.ID),
   (SELECT SUM(MASA_TOTALA) FROM CAR_EXP WHERE FK_ID_CHESTIONAR = CHT.ID)
   FROM CHEST cht
GROUP BY CHT.ID


Select multiple rows from one row based on column values

UNION does make sense.

You can put the UNION in a subquery which will neaten it up some:

DECLARE @prod TABLE (id int, abr int, ubr int)
INSERT INTO @prod
VALUES
(1 , 1 , 1),
(2 , 1 , 0),
(3 , 0 , 1),
(4 , 1 , 1),
(5 , 1 , 1)

SELECT id, 'ABR' as Ed
FROM @prod
WHERE abr = 1
UNION ALL
SELECT id, 'UBR' as Ed
FROM @prod
WHERE ubr = 1
ORDER BY id

You could also do an UNPIVOT I think but for this simple use case a UNION seems most efficient.


MySQL user created via command line can't access PHPMyAdmin

You have to run the FLUSH PRIVILEGES; command.


How to do a select on a table based on a index-by table?

This can be broken out into different steps if you want (i.e. variable for the count from the characteristics table).  But something with this logic should work.  Assuming unique constraint on (ID_SALA, NOME_CARATERISTICA) in getSalas, and unique data/constraint on (CARATERISTICA) in tabc_valor or whatever source data you have to compare to.  If those aren't necessarily unique, then you will only have one extra step to group by those columns first and sum the counts.

In one query:

with matches as (
    select gs.ID_SALA, count(1) ct
    from getSalas gs
    join tabc_valor tv on tv.CARATERISTICA = gs.NOME_CARATERISTICA
        and tv.VALOR <= gs.VALOR_CARATERISTICA
    group by gs.ID_SALA
), targetCount as (
    select count(1) ct
    from tabc_valor
)
select m.ID_SALA
from matches m
join targetCount tc on tc.ct = m.ct;


SQL Developer 3.x had a "theme" option, but that's been removed in 4.0, so no luck there.

You could try changing the "Look and Feel" option to Windows (I don't know what you get on other OSes).

Settings here:

Result should match your OS color scheme. I get this for example, which is a bit more readable than the default white on blue:


By default, XtraDB is used as the InnoDB replacement.

If you login to MariaDB and run SHOW ENGINES;, look for the InnoDB.

According to the Documentation for SHOW ENGINES;:

  Note that both MySQL's InnoDB and Percona's XtraDB replacement are labeled as InnoDB. However, if XtraDB is in use, it will be specified in the COMMENT field. See XtraDB and InnoDB. The same applies to FederatedX.

Based on your SHOW ENGINES; output, you are using XtraDB now.

In case you need to revert back to the original InnoDB, see Using InnoDB Instead of XtraDB. It discusses flipping the plugins and restart MariaDB.

As for XtraDB, you can read the Documentation.

I can tell you one thing that I wish MySQL's InnoDB had: MORE INFORMATION_SCHEMA

Check out the 5.5 and 5.6 INFORMATION_SCHEMA extensions for Percona and see how they compare to those in MariaDB.

To see which parts of XtraDB can be tuned and monitored (which InnoDB cannot do), look at this Pictorial Representation of InnoDB (originated by Vadim Tkachenko).


'Locking' will occur when I create more than 5-6 non-clustered indexes?

It's hard to give you a complete answer without knowing what you mean by "locking problem".  All queries take locks of one type or another and this is normal and correct.  I'm going to assume you mean that queries are getting blocked?  Where you are running one query and another will have to wait for resources?

I can tell you the following:

While you create the indexes you might (depending on how you create it) see some blocking.
Every index you add creates overhead for write operations.  This means that your writes will take longer and could increase the amount of blocking that you see.

In and of themselves no, creating 5-6 indexes shouldn't be causing you a noticeable problem.  Now if you had 50-60 indexes we might expect one.  If you are seeing a problem you will have to give us quite a bit more detail (table structures, indexes, queries that are having a problem, and most importantly what the actual problem is) before we can give you a more specific answer.


If you want to mask data but still store it unmasked, and you don't want to use the Oracle provided tools (chargeable extra!) then you need to develop an API that does the masking for you.

You store the data as normal in the database.

Clients don't get any access to the tables directly.  When they request data, use a "GET" call, which masks out the data to your spec, and when they insert data they use a "SET" call.

This all relies on access to the data being through this API though, so you need to carefully design that side of things.  This means using one schema for storage of data and another user as the API owner/data access path.

There's lots of things to take into consideration here and it's not a small job.


Logins are stored in master database. Since system databases cannot be part of AG (before sql server 2019 - From PASS summit 2018, MS announced that system dbs can be part of AG), they are not replicated by default.

The only way to do is to sync logins using sp_help_revlogin or use Late Robert Davis's script - Transferring Logins or use dbatools - Copy-DbaLogin (You can use -Force option, so that it will sync (drop and create) all the logins from source server to your destination server).


I see a constraint in my table thats not in INFORMATION SCHEMA?

Please stop using INFORMATION_SCHEMA and deprecated backward compatibility views like sysobjects. This is just another in the long list of things that INFORMATION_SCHEMA is missing in order to provide a complete picture of your metadata. See this post for a more exhaustive list:

The case against INFORMATION_SCHEMA views

To find the name of the default constraints for any table, use the catalog view sys.default_constraints:

SELECT [constraint] = df.name, [column] = c.name, df.[definition]
  FROM sys.default_constraints AS df
  INNER JOIN sys.tables AS t
    ON df.parent_object_id = t.[object_id]
  INNER JOIN sys.columns AS c
    ON t.[object_id] = c.[object_id]
    AND df.parent_column_id = c.column_id
  INNER JOIN sys.schemas AS s
    ON s.[schema_id] = t.[schema_id]
  WHERE s.name = N'dbo'
  AND t.name = N'DocumentDistribution';


Rebuild index in SQL Server does not reduce fragmentation

From this connect item Index Rebuild Doesn't Affect Fragmentation

  For small tables, usually performance impact on fragmentation is
  undectable. The first 8 page allocation would be from mixed extents
  and mixed extents could be anywhere in database files. Rebuilding
  indexes would not change this nature.

  If you have a small table, those mixed pages weight a lot during
  fragmentation calculation; therefore, rebuilding index may not reduce
  fragmentation. (As matter of fact, I could easily construct a case
  that fragmentation increases after rebuild.) Those fragmentation would
  not be a pain for your query performance; so basically you can ingore
  them.

  When page counts of an index reaches to certain big size (for example,
  1000 pages), then fragmentation may start to impact performance.
  Rebuilding index should reduce fragmentation.

Therefore probable reason rebuilding does not lower the fragmentation value due to fact that after rebuild the pages which are allocated to index is from Mixed extent.

Also, @Shanky has written a great article on same with in-depth analysis showing this behavior with a test scenario in What can Cause Index to be Still Fragmented After Rebuild


Oracle Equivalent of SQL Server's Bitwise Operators

Have you checked the manual?, it should help with your question to a large extent.


SQL Server logins and users -- assign multiple logins to a single user?

Since you are using SQL Server local logins, they cannot be grouped.  Each login is independent and each login needs a user in the database.

Where you can get some saving in specifying rights is to create roles, to which you grant, revoke, or deny rights.  (Note that deny rights override grant rights.)  Then use these roles to give rights to the users.

This way a role contains the specifications of permissions, rather than applying those to every user individually.

After this you can add the users to the roles that user needs.  For example:

Allen - ReadOnly role
Sally - ReadOnly role, MaintainAccounts role
Zenubia - UpdateNonSecureData Role
etc.

This preserves your security settings at the role level, so you should not need to repeat those grants.  Just add or remove a user from a role, when needed.


How to get average of lat/long from cities by county that they're in

My intention is to get the latitude/longitude of the counties using the coordinates of their respective cities.

This is insane and you should never do this. Counties aren't geographic and this isn't how GIS works. Moreover the cities inside the counties are totally useless for determining where the county is. Cities are near rivers, costs, railroads, and roads. Counties are just political hacks to disempower minorities, and make sure rich ranches in unincorporated territories have enough police to keep out ex-slaves.

So if you have to use counties, what you want you to do is use them as they are and not try to make sense of them.

Download the Cenus Shape Files for your state. They're distributed in the TIGER data set by state, and they have one or more polygons that represents each state's counties!
Run a query using ST_Within to determine what county your points are in.

There is no need to find any specific point in that polygon because it makes no sense.


Export the PATH variable. You have missed that.

PATH=$ORACLE_HOME/bin:$PATH
export PATH


Is my shopping list database schema good enough?

Your schema has a circular reference and redundant columns/foreign keys.

Consider the following normalization of your data structure:

Put itens as a reference table, no Foreign Keys.
Stocklist references Items
Users have a FK to Household, no use on including the reverse relation because you can always find the User records for a given Household with a table Inner Join.


The redo logs are required by the InnoDB storage engine. The redo logs provide the 'D' in ACID - durability. You can't set the innodb_log_file_size to 0 as the smallest allowed value is 1MB. Also, the smallest value for innodb_log_files_in_group is 1, so you can't use that either.

You can shut down the MariaDB server and disable InnoDB - use these settings in your .cnf file:

[mysqld]
skip_innodb
default_storage_engine=Aria

Then delete the InnoDB redo log files (by default: ib_logfile0 and ib_logfile1 in your datadir - by default /var/lib/mysql/) and start the MariaDB server again.


MySQL unknown column 'password_last_changed'

From the 5.7.6 release notes:

  If you upgrade to this release of MySQL from an earlier version, you
  must run mysql_upgrade (and restart the server) to incorporate the
  changes to the mysql database. mysql_upgrade moves Password column
  values to the authentication_string column and removes the Password
  column. For nonupgraded installations that have no account_locked
  column, the server treats all accounts as unlocked, and attempts to
  lock or unlock and account produce an error.

Although you will find similar issues in every upgrade, so running this tool is a must. password_last_change was added in 5.7.4, so you must update the table definitions.

Please note that at the time, upgrades from 5.6 are supported, but not from 5.5 at the moment of writing these lines, so if you have upgraded directly from 5.5 or below you will likely find problems.


How to get the dependent entities (referencing entities) for an User Defined Function (UDF)

You can find out the referencing entity dependencies for your function or any other database entities by using DMV dm_sql_referencing_entities. An example statement is as below.

Select
referencing_schema_name, referencing_entity_name,
referencing_id, referencing_class_desc, is_caller_dependent
from sys.dm_sql_referencing_entities ('dbo.ufn_MyFunction', 'OBJECT');


Get timing on individual queries in Anonymous code block

Save sysdate or systimestamp into a pl/sql variable just before the query then use dbms_output.put_line to output the difference afterwards.


How do I insert records into database with foreign keys into SQL Server?

Since Department.Mgr_ssn is nullable, I would insert the departments without Mgr_ssn.

Then insert the employees as you did before. Start with the employee having no Super_ssn (the big boss, I guess). Then the emps whose super is the big boss, etc.

Finally update the departments to set the Mgr_ssn.

UPDATE Department
SET Mgr_ssn = '333445555'
WHERE Dnumber = 5

The rule is to first populate tables with a lookup character, i.e. tables containing constants. Departments are kind of constant, where as employees can change.


Have you tried something like this (syntax is for SQL Server):

SELECT TOP(1)
    DATA_KEY_ID
    , TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE"
    , DATA_KEY_REF
    , DATA_KEY_ID
FROM TableName
WHERE SCREEN_REF = 'TEST'
    and FIELD_REF = (
            select REF
            from REF_TABLE
            where SCREEN_REF = 'TEST'
                    and ID = 'EFFDTE'
            )

The TOP(1) clause just after the SELECT tells the database engine to return a single row.  In order for this to return the same row each time it is executed, you should also include an ORDER BY clause at the end such as:

ORDER BY DATA_KEY_ID;

If you are using MySQL, the syntax would be something like:

SELECT
    DATA_KEY_ID
    , TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE"
    , DATA_KEY_REF
    , DATA_KEY_ID
FROM TableName
WHERE SCREEN_REF = 'TEST'
    and FIELD_REF = (
            select REF
            from REF_TABLE
            where SCREEN_REF = 'TEST'
                    and ID = 'EFFDTE'
            )
ORDER BY DATA_KEY_ID
LIMIT 0,1;

If you specify the type of RDBMS you are using, that would help identify the actual syntax you should use.

For Oracle, which seems like the platform you may be using, you need to add a reference to ROWNUM to the WHERE clause, like:

SELECT *
FROM
  ( SELECT
        DATA_KEY_ID
        , TO_DATE('1799/12/31', 'YYYY/MM/DD') + TO_NUMBER(RTRIM(DATA_VALUE)) as "EFFDATE"
        , DATA_KEY_REF
        , DATA_KEY_ID
    FROM TableName
    WHERE SCREEN_REF = 'TEST'
        and FIELD_REF = (
                select REF
                from REF_TABLE
                where SCREEN_REF = 'TEST'
                        and ID = 'EFFDTE'
                )
    ORDER BY DATA_KEY_ID
  ) x
WHERE ROWNUM = 1;


PostgresSQL date/time field out of range on seconds-since-epoch

Seconds since epoch timestamps

Your "timestamp" is in seconds-since-epoch. Per dezso, use to_timestamp(). I missed that when I checked \dfS+

Bad complex idea, and dragons.

Check the input formats. They cover casts from strings. That's what COPY is doing under the hood. The only method that even remotely looks like a long number is ISO 8601. If you look at that example though you'll see it's not a seconds-since-epoch

Example    | Description
19990108   | ISO 8601; January 8, 1999 in any mode

This is basically the same as another example on that chart.

Example    | Description
1999-01-08 | ISO 8601; January 8, 1999 in any mode

Converting to timestamp with abstime as an intermediary format

So if you want to convert from seconds-since-epoch, you can cheat by using the internal abstime since there is no available cast directly to timestamp from a string of seconds-since-epoch.

SELECT 1421088300::abstime::timestamp;
      timestamp
---------------------
 2015-01-12 12:45:00
(1 row)

What's happening here is that abstime is binary coercable with integer. You can see that in \dC+. I checked \dfS+ for functions to get from integer to timestamp and found none. There is a cast though from integer to abstime (which is stored as an integer), and from abstime to timestamp.

If this is a new table you could actually type that column as abstime. It should load perfectly fine. And then you can ALTER TABLE. here is an example, except I'm not running COPY (but it should work all the same).

CREATE TABLE foo(bar)
AS VALUES
  (1421088300::abstime);

TABLE foo;
          bar
------------------------
 2015-01-12 12:45:00-06
(1 row)

ALTER TABLE foo
  ALTER bar
  TYPE timestamp;

TABLE foo;
         bar
---------------------
 2015-01-12 12:45:00
(1 row)

\d foo;
                Table "public.foo"
 Column |            Type             | Modifiers
--------+-----------------------------+-----------
 bar    | timestamp without time zone |


What happens if we copy neo4j's data folder while the server is running?

The copied store might be corrupted, but also might be recoverable.

There's a good reason neo4j has a backup tool: this method is not safe.

If you need to snapshot the file system, cleanly shut down the DB first.


Best practice - Log shipping / replication for archive / reporting database

I would recommend to use log shipping but only if you do not want to prevent delete operations. Log shipping keeps the target database the same as the source database.
Log shipping is a relatively simple mechanism, it just backups and copies over the transaction log and applies it to the target database. It is a bit simpler to setup and to maintain than Replication.

Replication also keeps the source database completely in sync with the target database. With transactional or snapshot replication there is no possibility to exclude delete operations.
The only exception is that in merge replication exclusion of DELETE statements is still possible. However, this possibility will be removed in future versions of SQL Server (see the first note in https://msdn.microsoft.com/en-us/library/ms146947.aspx). So that is nr. 1 of your worries I think. Some more considerations can be found here: https://technet.microsoft.com/en-us/library/ms151206(v=sql.105).aspx.

Performancewise there also can be a big impact. This depends a lot on the database and the operations on it. So test it in a test environment to see if the performance loss is acceptable.
Personally, I'm not a huge fan of merge replication as I have experienced many problems with it.

Summarizing, if you want to prevent delete operations I would recommend to write your own synchronization mechanism. If you don't want to do that, and can live with the issues mentioned above, merge replication will also be usable.


What determines if a bytea has 1 or 4 extra bytes?

I believe the answer is in this header comment of heaptuple.c:

* Before Postgres 8.3 varlenas always had a 4-byte length header, and
* therefore always needed 4-byte alignment (at least).  This wasted space
* for short varlenas, for example CHAR(1) took 5 bytes and could need up to
* 3 additional padding bytes for alignment.
*
* Now, a short varlena (up to 126 data bytes) is reduced to a 1-byte header
* and we don't align it.  To hide this from datatype-specific functions that
* don't want to deal with it, such a datum is considered "toasted" and will
* be expanded back to the normal 4-byte-header format by pg_detoast_datum.


MySQL: error 1118 (42000) row size too large

Sounds like the import forced the Engine to be InnoDB??

If so, then let's see the my.cnf and figure out what to fix.

If not, then let's see the CREATE TABLE that is leading to the problem.

Or, maybe, a CHARACTER SET default changed.

Show us the first part of the dump and the CREATE TABLE just before the naughty table.


SAN Storage & Server for Oracle Replication

If you have already got an EMC then SRDF is the obvious choice, tho' Veritas Volume Replicator may be an option. SAN is not something you usually buy "off the shelf" the first time, the vendor will work with you to establish your requirements, both for performance and resilience. Other vendors you may which to consider are HDS, 3Par and NetApp.

However my recommendation is to not use the storage for this, use Data Guard because a) it's free once you already have paid for an oracle license and b) it enables Active Standby, something that I am very excited about myself.


Speed up slow postgres query with indices (~5 minutes)

WHERE user_id = 1
ORDER BY game_id DESC

You probably need an index on bets (user_id, game_id).  That would let it tackle the above in a very efficient way, rather than the very inefficient it is doing it now:

"Filter": "(bets.user_id = 1)",
"Rows Removed by Filter": 106898896,

If that doesn't do it for you, please provide a complete text-formatted "explain (analyze, buffers)".  Neither of the ways you presented it is very effective.  Especially since we don't even know what tool generated the image one, and the two things seem to have come from different executions plans.


Is there a way to avoid the password prompt in pg_basebackup?

Create a .pgpass in the home directory for the postgres user, or whatever user you run pg_basebackup as.

Link to the manual: http://www.postgresql.org/docs/current/static/libpq-pgpass.html


Extract Data Between Delimeter In NVarchar Field

This seems to work:

declare @name varchar(20) = 'name_Test_1'

select SUBSTRING(@name,CHARINDEX('_',@name,0)+1, (CHARINDEX('_',@name,CHARINDEX('_',@name,0)+1)) - CHARINDEX('_',@name,0)-1)


Display query which SSMS executes from Interface/WYSIWYG edits

When you make the changes, there's a menu option "Table Designer".  Under there is the option to Generate a Change Script.

There's an option there to automatically generate the script on future changes too.


How large is your database? If you expect that your database will not grow to larger than 10 GB, and you are familiar with SQL Server, then you can use SQL Server Express Edition. If you don't need any of the enterprise features (page/row compression, HA/DR, encryption, table partitioning, etc), it's a great way to manage smaller amounts of data...and its free!

I've never personally used a set-up with Access ADP as a front-end, but I've heard it works fine for straight-forward data management. If you have the time/resources, creating a simple web-based front end for management (or buying something out of the box that sits on SQL Server) might allow you to reduce the complexity even further.


Outside of what you've already noted this should be a low impact change (but hey, anything could happen).

In fact, even if you change the value the checksum won't be calculated and written until the pages and written to disk - which could mean it could take days or weeks to get all of the pages actually changed in a natural lifecycle way (as in you didn't just go around rebuilding indexes to have this immediately take effect).

However, company policy is company policy. Even if there is -5% impact, if anything were to happen then the typical finger pointers will immediately throw out any relevant evidence and logic and start the blame game.

TL;DR: Little to no impact, I'd still put in the change control and "after hours" change due to company policy and CYA.


how to convert this varchar to datetime format?

There is too much precision in the varchar to be converted into datetime.

One option(better in my opinion) would be to change the target column to datetime2(7). Then you can convert like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(@dt as datetime2(7));

If changing the column is not an option the you can do the conversion like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(cast(@dt as datetime2(7))as datetime)


A Query in the Query Store shows high Logical Reads

Remove the OPTION(FORCE ORDER). This greatly limits the optimizer's freedom to find a good plan.
Modify the Index Seek indexes to include the columns referenced in the following Key Lookup operators (Output List and Predicate)
Capture actual (post-execution) plans for representative executions.
Consider adding OPTION (RECOMPILE) if different executions require different plan shapes to run well, or OPTIMIZE FOR hints for the main cases.

If nothing else, attempt to capture a post-execution plan for a problematic case, as the basis for your tuning efforts.


Oracle XE installation aborts with no error explanation

System Requirements

  Oracle Database XE for Windows x64 is supported on the following
  operating system versions:

  Windows 7 x64 - Professional, Enterprise, and Ultimate editions
  Windows 8.1 x64 - Pro and Enterprise editions
  Windows 10 x64 - Pro, Enterprise, and Education editions
  Windows Server 2012 x64 - Standard, Datacenter, Essentials, and Foundation editions
  Windows Server 2012 R2 x64 - Standard, Datacenter, Essentials, and Foundation editions
  Windows Server 2016 x64 - Standard, Datacenter, and Essentials editions

Home edition is not supported for Oracle databases and is known to have issues, like here:

Oracle 11g setup closes without errors on pre-requisite check


Why is % forbidden char in THROW message?

It appears that they left the printf functionality (or at least part of it) in the THROW command but did not include a way to pass in a parameter to it.  I tried each of the characters listed in BOL for RAISEERROR as possible "types" %(d, i, o, s, x, X, or u) and each of them gave back a blank.  Any other character after the % and you get back a valid string.  My guess is that they are replacing the value with a NULL which of course causes any string concatenation to return a NULL and when printed looks blank.

Unfortunately without the code behind it (or at least a MS Developer) to confirm there is no way to be certain.


How to Block normal user access to mysql

Question need more info.

Unless we set .my.cnf (mysql clinet) on the server, No one can access by mysql simply by hitting mysql, Else you need do mysql_secure_installation to set mysql root password if you haven't done it before.


Command for setting Inline Length has no effect

The reason for this behavior is that DB2 must have enough space for the largest possible LOB locator in the column for the row in the table.

INLINE LENGTH is not like a VARCHAR where space is used only according to the amount of data.  It's like CHAR – the size you specify is reserved in every single row of the table.

From the documentation (scroll down to description of INLINE LENGTH integer):

  For a column of data type LOB, the default inline length is set to be the maximum size of the LOB descriptor if the clause is not specified. Any explicit INLINE LENGTH must be at least the maximum LOB descriptor size.

There's a table that shows the minimum explicit inline length for various LOB lengths, and for CLOB(2G), the maximum LOB Locator size is 312 bytes.

Therefore, for your table, the inline length must be at least 312 bytes to ensure that the largest possible LOB Locator can be stored.


Exhaustive SQL stored proc validation make insert slow

Insert all the data into a staging table without verifying anything.  This should go quickly, but your mileage may vary.  This also assumes that you are only ever going to be doing one insert at a time.  Multiple inserts may require multiple staging tables, but you might be able to use a temp table for it or have a session identifier in the staging table to indicate discrete insert operations.

In any case, once the data is loaded into the staging table, perform your validation checks.  Something like this (I don't know your database structure so this is just an example).

UPDATE StagingTable
SET ErrorDesc = ISNULL(ErrorDesc,'') + 'MemberClassName mismatch'
WHERE NOT(MemberClassName IN (SELECT MemberClassName FROM MemberClass))

Repeat this for all your validation rules.  This should go much faster than your row by row check.

Once done, move all rows that had no validation errors into the final tables, then output all the errors for whatever you want to do with them.


I see nothing wrong with PAYMENT referring to BOOKING. Starting with BOOKING, why is check_out part of the P.K.? I don't see how you can check out of the room several times, for a check-in. Given:

CREATE TABLE BOOKING
( guest_ID varchar NOT NULL --  FK to Guest
, room_ID varchar NOT NULL -- FK to Room
, check_in date NOT NULL
,   PRIMARY KEY (guest_ID, room_ID, check_in)
, ... );

For PAYMENT you need to identify what BOOKING it relates to, so it makes sense to include check_in:

CREATE TABLE PAYMENT
( guest_ID varchar NOT NULL
, room_ID varchar NOT NULL
, check_in date NOT NULL
,     FOREIGN KEY (guest_ID, room_ID, check_in) REFERENCES BOOKING (...)
, date_paid timestamp NOT NULL
--    , amount numeric NOT NULL
);

I deliberately left out the P.K. for PAYMENT, if full payment is required, first 3 columns are sufficient, if partial payment is possible, date_paid can be included.

Other considerations, what is the agreed price of the booking? the price for a room may vary over the season, campaigns etc. If we assume that the price belongs to the booking somehow, the amount paid is only needed in case of partial payments, otherwise, a bill is either paid or not

For evaluation, is there a reason to include hotel instead of room? You can deduce the hotel from the room, and that would also make it possible to relate an evaluation to a booking as with payments.


How can I print the contents of a table to the console?

You should handle this all in the C# app code. Exporting data from T-SQL is not handled natively and requires SQLCLR, which is fine when needed, but it is not necessary here since you are already working within app code that can handle it quite cleanly.

You should have the app code do a SELECT (or grab the contents via EF if you say it can do that easily) and log it to a file after each step.

You should also have a command-line option/flag, or a check-box in the UI before clicking "Start", for "Debug mode" that will additionally display what is being written to disk along with an option to "Continue" or "Cancel".


show grants for someuser not working for root

I'm only guessing here, but could it be that you have defined different host values for someuser on server1 and server2?

When you execute SHOW GRANTS FOR someuser; it really executes SHOW GRANTS FOR someuser@'%'; - that is, if you don't specify the host part, the it will default to using the wildcard (%) for the host part.

So if you have someuser@'%' on server1 and e.g. someuser@'localhost' on server2, then you will get the result you have observed.

You can see the host part of the users by executing SELECT user, host FROM mysql.user;

And then you can view the grants for the different users by executing SHOW GRANTS FOR someuser@'localhost'; or similar for users with other host values in their mysql.user rows. Note that each row in the table is a different user - it doesn't matter that some of them might have the same user names as long as they have different host values.


So as a matter of fact, starts with is not so hard to write as it would first seem. Given the initial key of 0x000000100004, obviously equals won't work and there is no starts with operation, but we can write ObjectId >= 0x000000100004 AND ObjectId < 0x000000100005.
By forcing the string of long values to VARBINARY conversion to be big-endian the natural form actually works correctly. We want >= (value0 & value1 & ... & lastvalue) AND < (value0 & value1 & ... & (lastvalue + 1)). As far as I know, the conversion to VARBINARY has to be written in application code, which is fine for me.


Any pointers on reducing my privileges in production but not making my job excessively difficult

At first, I suggest you do all the privilege play in a development or QA environment where there is no problem if the access is removed for some time. You will need to see if the applications won't have any issues with security.

I'll tell you our internal approach:

all the applications use a single domain user that is granted necessary permissions on a database (usually db_owner database role).
for occasional data read we use a SQL login. For that user we assign the database role - db_datareader. This is where it ends the access for developers on the main database cluster. For any other idea they would have, they will use the reporting server databases which are copies (done using Log Shipping) of the main server databases done at midnight. In order not to kill the reporting server with killer ad hoc queries we use the resource groups allocations for memory and cpu.
for the DBA team we have a domain group which has all privileges on the machine and the server (admin on the windows machine and sysadmin on the sql server)
for installations we have a SQL user that is db_owner on the databases that we use when running the updates - we use DDL triggers to monitor schema changes and we should see which changes were done during installation or as separate change
there are some occasional exceptions for experienced developers, but after their need is fulfilled we remove their access - they receive permissions based on their domain login, so we can monitor connections in traces/ddl views and any possible changes with the ddl triggers.

As for the way of doing all that work with the logins and users - in Management Studio in the server security folder you create all the needed logins, and then, you associate them with your databases and give them the roles they need. If you script the action you will see that initially it will be created a server login, then a database user connected to that login, then assigned a database role for that user. You can keep the script in your script set, so you can verify every time which users should be live and kicking and which should not be.


Adding database-specific privs for user gives table 'db' is full, MariaDB, *tiny* toy database

This isn't really an answer, in that I still have no idea why this was failing. It just provides a workaround. I noticed that the db table used the "new" storage engine "aria", so I converted it to use the traditional MyIsam storage engine, like so:
alter table db engine=myisam

After doing this, the problems posted disappeared.


How is the 2 Node Cluster Limit Enforced for SQL Server Standard Edition?

Of course licensing is a necessary part of Microsoft's business, so it is not surprising that the installation would enforce the limits that the purchaser is granted.

Setting up a SQL Server Failover cluster for SQL Server 2012 (Standard as well as Enterprise editions) you must first:

Create a New SQL Server Failover Cluster (Setup)

Option 1: Integration Installation with Add Node (i.e. only 2 nodes permitted.)

Create and configure a single-node SQL Server failover cluster instance. ...
On each node to be added to the SQL Server failover cluster, run Setup with Add Node functionality to add that node.

A notice says: For more information on the different possible scenarios during Add Node operations, see Add or Remove Nodes in a SQL Server Failover Cluster (Setup)

Option 2: Advanced/Enterprise Installation

Add or Remove Nodes in a SQL Server Failover Cluster (Setup)

This option continues on to link to other pages that provide more detailed installation instructions for Advanced/Enterprise Installations. The warning is not explicitly called out for these installations.

This suggests that (1) the Add or Remove Nodes process and (2) the license or, if you prefer, the Standard edition, provides the tools to protect both the purchaser and the provider by controlling, in this case, how many failover nodes can be installed.

Looking forward to SQL Server 2016 SP1 which will bring some good benefits on Standard Edition, by allowing many features to run on Standard.  But it will not allow for multi-node SQL Server failover cluster installations.

Clarification: For SQL Server Standard Edition (from 2000-2016) the failover cluster can only support 2 nodes per instance.  If you are using SQL Server Enterprise Edition you can support more nodes both synchronous and asynchronous, the exact numbers depending on the release you are using.

True, it is also possible to install another instance of SQL Server, even a failover cluster, but it would not be part of the first cluster. And if this was installed on the same physical server, there would likely be some resource contention.

If you want a Third Server in your landscape you can install another server and implement log shipping from the active Cluster Node to the Third Server.  Since this has some latency issues the Third Server will not always be fully up-to-date with the data being shipped to it.


presentation of non-ascii characters (ñ)

Is mysql_json_data.py input or output or code or a dump?

'MA\xd1ANA... has Ñ encoded in latin1 (and perhaps escaped for display?)
'Ma\xc3\xb1ana... has ñ encoded in utf8 (and perhaps escaped for display?)

If you have one file with both in it, you have a mess.

The former will probably be truncated as you attempt to put it into your MySQL table.  The latter will probably insert correctly.

See my notes on Python.


In my personal opinion, it seems as all of those queries have very separate requirements and output.  I would personally keep them separate to the end.  In other words, what are you going to gain out of filling a temporary object with a key/value pair of the output of 5 unrelated queries?

I'd wrap them each in a stored procedure, parameterize what I can to make code reusable, and then just make individual calls to each whenever I needed to.  I like to follow the principle that a logical piece of code (stored proc, method, etc.) should do just one thing.

So the approach I would take is something like this:

create proc dbo.GetDetail
as
    SELECT Type, Description, Country, Quantity
    FROM UserHoldings
    WHERE (Type = 'Detail')
    ORDER BY Quantity DESC;
go

create proc dbo.GetCurrent
    @Quantity int = 0
as
    SELECT Type, Description, Country, Quantity
    FROM UserHoldings
    WHERE (Type = 'Current')
    AND Quantity >= @Quantity
    ORDER BY Quantity DESC;
go

So on and so forth...

That way when you needed the data, you'd make your calls:

exec dbo.GetDetail;
exec dbo.GetCurrent;
exec dbo.GetCurrent @Quantity = 10;

Why this particular approach?  Because I think this scales well.  It's the way I typically approach database development.  I'm completely fine with having a bunch of small pieces of executable code.  It beats having a few big ones, and having to modify accordingly.  That doesn't grow.

This is my opinion, and I'm looking forward to seeing what others would do in this scenario.


Why there is a start (*) after Nodegroup:0 in MySQL cluster? What does it means?

It shows which node is master. It is internal thing and probably the only visible effect of it is when exactly half of the nodes goes down. Then if one of the remaining nodes is master - cluster keeps working. If not - cluster aborts.

http://dev.mysql.com/doc/refman/5.1/en/faqs-mysql-cluster.html#qandaitem-B-10-1-8

Updated link:
http://dev.mysql.com/doc/refman/5.1/en/faqs-mysql-cluster.html#faq-cluster-what-show-nodegroup-star


du of directories in a diskgroup in Oracle ASM

du of asmcmd doesn't support this, just put it in a loop.

$ dg=fra; for dir in $(asmcmd ls $dg); do echo $dg/$dir; asmcmd du $dg/$dir; done


SQL Server Always On or Mirroring & Log Shipping

If you were referring to Database Mirroring when you say mirroring (as opposed to SAN mirroring), then don't. It has been deprecated since 2012 now so it is a potential candidate for complete removal in the future releases.

Go with Always On Availability Groups. The difference in DBA overhead should be mostly the same. What is different, IMHO, is the learning curve. Log shipping is dead simple to learn, implement and operate. That's largely because it was originally created by MSIT DBAs (decades ago) as a simple, flexible DR solution which was subsequently productized. DB Mirroring applies the same concepts with a few powerful enhancements (e.g. shipping blocks instead of backups gives a sizeable perf boost).

Always On AG extends these concepts but added dependencies that users now need to learn to use and manage (e.g. WSFC). It's not rocket science but may take some effort to pickup if you're not already familiar with them. However, once you figure it out, the benefits are pretty significant (grouping resources, readable secondaries, much higher transfer perf are among the top ones).


inner joins with where clause for no values?

The "where" clause excludes everything in Table1

Provide sample data in the question if you would like an SQL query example


MYSQL Replication _ Master port is 3306 and slave port is 3307

Master mysql port = 3306
master server id = 1
mysql vesrion 5.5.50

Slave mysql port = 3307
slave server id =2
mysql version 5.5.50

I have tested the above scenario with Master port 3306 and slave 3307. And it's working fine. I have followed the steps you have given in the question.

As you have mentioned in the comment.I think it compatibility issue.You can check with the similar version.

 mysql master version = 5.7
 mysql slave version = 5.6

  Statement-based replication depends on compatibility at the SQL level between the master and slave. In others, successful SBR requires that any SQL features used be supported by both the master and the slave servers. For example, if you use a feature on the master server that is available only in MySQL 5.7 (or later), you cannot replicate to a slave that uses MySQL 5.6 (or earlier).

http://dev.mysql.com/doc/refman/5.7/en/replication-features.html
http://dev.mysql.com/doc/refman/5.7/en/replication-compatibility.html


What you are looking for is to actually do the INSERT into warehouse_maine after Warehouse

DELIMITER $$
DROP TRIGGER IF EXISTS Warehouse_InsertAfter $$
CREATE TRIGGER Warehouse_InsertAfter AFTER INSERT ON Warehouse
FOR EACH ROW
BEGIN
    IF NEW.state = 'maine' THEN
        INSERT IGNORE INTO warehouse_maine (shipperid, name, state)
        VALUES (NEW.shipperid, NEW.name, NEW.state);
    END IF;
END;
DELIMITER ;

This trigger will do the job. If you are doing a bulk INSERT of rows into the Warehouse table, this trigger will slow things down a bit. If you plan to do such bulk INSERTs, you are better off doing that in a single INSERT:

INSERT IGNORE INTO warehouse_maine
SELECT * FROM Warehouse WHERE state='maine';


Soft-deleting change-tracked user content

Alright, so my solution is to cascade soft-deletions in the after update triggers. Here's an example:

create trigger `tr_user_content_au` after update
    on `user_content` for each row
begin
    if new.`deleted_at` <> null then
        -- The row has been soft-deleted. We assume that no
        -- other fields have been modified.

        -- Soft-delete related rows.
        update `user_content_derived` set `deleted_at` = now();
    else if
        -- Check if any other fields have been modified.
        old.`col_1` <> new.`col_1` or
        old.`col_2` <> new.`col_2` then
        -- Copy modified fields to the change table.
        insert into `user_content_change` (
            `user_content_id`,
            `col_1`,
            `col_2`
        )
        values (
            new.`id`,
            if (old.`col_1` = new.`col_1`, null, old.`col_1`),
            if (old.`col_2` = new.`col_2`, null, old.`col_2`)
        );
    end if;
end;

Also, I use views to filter out already deleted rows:

create view `vw_user_content_visible` as
    select
        `col_1`,
        `col_2`
      from `user_content`
     where `deleted_at` = null;

And finally, I turn even the simplest insert, select or update statements into stored procedures. That's how I mask whether an entry is physically deleted or just soft-deleted. Apart from this, I suppose that people working on application code are less likely to forget about soft-deletions if they are forced to look into the database code to alter queries.


Can an article in Sql Server replication be based upon a query?

Yes its definately possible. We use it all the time for replicating warehouse data to 53 different warehouses. The query you are talking about is called a replication filter.

Now listen up!!!
Do not put your query in the filter. Use a view instead.

That means you can easily alter the view at a later date as filters cannot be changed.
So you filter will be "select cols from vwUserFilter"

If you put the query in the filter and want to change it, you'd have to drop the publication and re-create it which is very painful.

Also see this link


ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

You mentioned a problem with creating an INDEX, but I see several other problems.  Many of them, if fixed, could avoid the index problem.

Huge OFFSET

  LIMIT 100 OFFSET 214000

This sounds like a crawler is scraping your site.  Block the crawler.

Huge number of "pages"

If it is not a crawler, then please explain who would would have the patience t0 step through to page 2141 !!

More than that, why do you have that many pages??  That is not what WP is designed for.

Can't fully index

Because of this "range", it is not possible to build an truly efficient index for the query:

AND wp_posts.post_date != '0000-00-00 00:00:00'

Do you really have 'zero' dates in your table?

Engine

MyISAM is deprecated and going away; switch to InnoDB.  InnoDB has different limitations on indexes, so it may make your index problem go away.

1000

Do you really have long passwords?  SELECT MAX(CHAR_LENGTH(post_password)) FROM wp_posts;  If it is significantly less than 255, shrink the column definition.

Do you need utf8mb4 for the password?  Is it a "plaintext" password; if so, then that charset is probably valid.  If it has been hashed, consider ascii, which won't have the 4x issue.


Postgresql - how to do backup/restore on a database level?

Except pg_dump which a logical backup tool, all other tools are physical backup tools and copy all database files from the cluster and transaction logs (WAL). AFAIK currently there is no way to make a physical backup of a single database.

If you need to restore one single database from a physical backup you need to restore the whole cluster-wide backup to a temporary instance and use pg_dump/pg_restore to restore the single database from the temporary cluster into the original cluster.


Simple SQL query but huge table - how to optimize?

The optimal index for that query is

INDEX(goal_id, year, target)

in that order.  And regardless of the cardinality of any of the columns.

Start with the = test(s)
Add on at most one range (the two year tests are effectively one "range")
Add on all the other columns found anywhere in the query; this makes the index "covering" for another boost.  Now the entire query is performed in the index's BTree, without also having to bounce over to the data's BTree.

(That is a simplification.  For more details and varied cases: http://mysql.rjweb.org/doc.php/index_cookbook_mysql )

But...  That query looks like it might return lots and lots of rows.  If so, you may be network-bound.  If so, then think about whether you can do more processing in SQL without shoveling a million rows to the client.


How does the MySQL Query Optimizer react to a SELECT COUNT sentence?

The two queries have a very big difference:

----- query 1
SELECT COUNT(*)
FROM customers
WHERE ID > 10000
  AND country = 'US' ;

----- query 2
SELECT *
FROM customers
WHERE ID > 10000
  AND country = 'US' ;

While the second query returns all rows that match the WHERE conditions, the first one has an aggregate function (COUNT()) in the SELECT list, so it does an aggregation, a collapsing of rows that match the conditions into one row and returns only one number, the number of rows that match the conditions.

So, for the first query, there is no sensible reason to have an ORDER BY. The result is one row only. Even more, it should produce an error as the rows (that have been collapsed into one) may have different values in the country and created_at columns. So, which one should be used for the ordering (say in a case where you had a GROUP BY and the result set was more than one rows)?

You can test at SQL-Fiddle that SQL-Server, when you add ORDER BY country, created_at, it produces the error:

  Column "customers.country" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

An error is produced in Postgres, too.

But even in MySQL that may allow such non-standard syntax, to add ORDER BY in the first query, the optimizer is smart enough to not take that into account for the execution plan. There is nothing to order. One row will be returned anyway. You can check that by viewing the execution plans with EXPLAIN. Simple test at SQL-Fiddle: Mysql-test

Oracle (version 11g2) seems to allow such nonsense too. You can see the execution plan here: Oracle-test. Not sure how the plan should be interpreted but it seems that Oracle at least knows that it's one row only so the "sorting" operation is not costly.


postgresql crash in master node with streaming replication

If that crash is repeatable, you probably have data corruption in your table. If the crash is not repeatable, the cause could be faulty memory.
At any rate, the odds are good that your hardware has problems. That would not surprise me, if you are as negligent with hardware maintenance as you are with software maintenance (9.4 is out of support).
Use reliable server grade hardware and keep your software versions reasonably recent.


How to bring data from two tables that could/could not be present in one of them

Try changing your GROUP BY YEAR(PurchaseDate)...
to GROUP BY ALL YEAR(PurchaseDate)...

  GROUP BY ALL -> "Specifies to include all groups in the results regardless of whether
  they meet the search criteria in the WHERE clause. Groups that don't
  meet the search criteria have NULL for the aggregation."

For some reason, MS decided to deprecate the feature, but it still works.
See the GROUP BY topic in the documentation.


To track object use with the SQL Server Audit feature, it’s necessary to set up the auditing. In order to do so, an audit object must be created first.

This can be done using SQL Server Management Studio or T-SQL.

The next step is to set up the auditing in the particular database on specific objects. This is where availability of the SQL Server Audit feature is required on the database level (the database level auditing is available in SQL Server Enterprise and Developer editions only).

To continue setting up the auditing, it’s required to create a database level audit specification. Such database level audit specification will belong to the audit object previously created.

Although SQL Server provides a built-in feature (the View Audit Logs context menu option of an audit object) to view captured information, this is not a convenient way for creating comprehensive reports, and it provides basic filtering only.

So, in order to provide tracked information for any deeper analysis or documenting, use the fn_get_file_audit SQL Server function to read repository .sqlaudit files used by the audit object.


Find percentage of NULL storage in Oracle table

NULL value

  Absence of a value in a column of a row. Nulls indicate missing,
  unknown, or inapplicable data. A null should not be used to imply any
  other value, such as zero.

There are no alternate definitions of NULL in Oracle.  A column can't take less space than NULL unless the column doesn't exist at all.  Also, if a null value is in the last column of the row, the row will take less space than the same null column as an earlier column.  Here is the wording in the documentation:

  In general, try to place columns that frequently contain nulls last so
  that rows take less space. Note, though, that if the table you are
  creating includes a LONG column as well, then the benefits of placing
  frequently null columns last are lost.

Since in your example data, none of the rows with nulls have them last, we can ignore this space optimization.  We can also not compare the lack of a column to a column with nulls as that would be the true "space" of a null, which according to some block dumps I did is one byte per null per row.

In this case we are actually comparing the space of a null vs. the space of a value.  Because any value will by definition use more space than a null, my answer is that, a null value will decrease not increase the amount of space consumed, therefore creating a percentage compared to the size of the table is meaningless.

You could do a block dump of a row containing a value and then set the value to null and dump the block again to see how much additional space is available, but that number and the resulting ratio you could create with it and the size of the table would be a measure of space used by values rather than space used by NULLs.


PostgreSQL: date/time field value out of range

Follow the hint and adjust your datestyle setting appropriately.

The parameter consists of two parts: the output style and the expected ordering of day, month and year.

With US settings, parsing 30.06.2020 causes an error:

SET datestyle = US, MDY;

SELECT '30.06.2020'::date;

ERROR:  date/time field value out of range: "30.06.2020"
LINE 1: SELECT '30.06.2020'::date;
               ^
HINT:  Perhaps you need a different "datestyle" setting.

This is because there are only 12 months in a year.

You should probably use this setting:

SET datestyle = GERMAN, DMY;

SELECT '30.06.2020'::date;

    date
------------
 30.06.2020
(1 row)

You can use the options connection parameter to set datestyle when you connect.


Binlog commits and flushes on SQL Server (compared to MySQL)

Being more 'click-and-go', does SQL server set everything to safe values?

It's baked in to Availability Groups (physical) and Transactional Replciation (logical) that a replica cannot commit changes ahead of the primary.
In AGs transaction log records are streamed to the replicas as they are hardened on the primary, but on a failover, any transaction that failed to commit on the primary will be rolled back on the replicas.
In Transactional Replication changes are harvested from the transaction log on the primary (called the "Publisher"), but only committed transactions are sent to the Distribution database.

What are equivalent settings, or settings one must know when dealing with primary/secondary consistency, and their performance impact?

Since consistency is always guaranteed, the main thing is to understand the difference between Sync and  Async replicas.


What risks are there if we enable read committed snapshot in sql-server?

Summary

If you have locking problems then you have a problem with your code: it isn't the database engine
It isn't a magic bullet
You may add more problems

Load

It will also increase load on your tempdb and CPU. Also see:

"Performance Impact: The Potential Cost of Read_Committed_Snapshot" (Linchi Shea)

Safety

Most important, snapshot isolations are not safe in many cases by default. Read "Snapshot isolation" (Wikipedia) for more on write-skew anomalies. The next section is "Making Snapshot Isolation Serializable" to get around this.

  In general, therefore, snapshot isolation puts some of the problem of maintaining non-trivial constraints onto the user, who may not appreciate either the potential pitfalls or the possible solutions. The upside to this transfer is better performance.

Also see:

"The Potential Dangers of the Read Committed Snapshot Isolation Level" (JimMcLeod, disputed in comments by Alex Kuznetsov)
Deadlocked!: "read committed snapshot" Explained (Nick Berardi)
Serializable vs. Snapshot Isolation Level, the Marble problem (Craig Freedman)
Reads involving UDFs under READ_COMMITTED_SNAPSHOT may seem inconsistent (Alex Kuznetsov)


PostgreSQL How do I convert struct text to plain *char in C functions?

The data pointed to by the text structure isn't null-terminated, so you can't use it with C "string" functions directly.

To determine the length of the text, you can use VARSIZE(your_text)-VARHDRSZ.

In your example, you could use fwrite instead of printf to output the string (passing in the length), or copy the text to a char* and terminate it manually if you intend to use C string functions.

Here's a sample of how either could be done:

#include <stdio.h>
#include <string.h>
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(fun);

size_t log_pgtext(FILE *to, text *what)
{
    return fwrite(VARDATA(what), 1, VARSIZE(what)-VARHDRSZ, to);
}

char *dup_pgtext(text *what)
{
    size_t len = VARSIZE(what)-VARHDRSZ;
    char *dup = palloc(len+1);
    memcpy(dup, VARDATA(what), len);
    dup[len] = 0;
    return dup;
}

Datum fun(PG_FUNCTION_ARGS)
{
    text    *str1, *str2;

    str1 = PG_GETARG_TEXT_P(0);
    str2 = PG_GETARG_TEXT_P(1);

    FILE *fp = fopen("/tmp/fun.log", "w");

    log_pgtext(fp, str1);
    fprintf(fp, "\n");

    char *str = dup_pgtext(str2);
    fprintf(fp, "%s\n", str);
    pfree(str);

    fclose(fp);

    PG_RETURN_INT32(0);
}

Reference documentation for PostgreSQL 9.2.


Firstly, I'd encourage you to read the blog post Be Careful When Mixing INNER and OUTER Joins.

  If you use Derived Tables (or nested joins) the way you'd use
  parenthesis in a math or boolean equation to express your logical
  order of precedence, you can make your code cleaner and more readable
  and you can ensure that you get back the exact results you intended.

I don't know if this will solve your problem, as you did not post any table definitions or sample data, but I'd try changing your query to encapsulate the inner join into a derived table and then join the derived table using the FULL OUTER JOIN.  Here is an example (not tested):

SELECT DISTINCT [ij].[Artikelnummer]
    ,[ij].[Bezeichnung1]
    ,[ij].[AuspraegungID]
    ,[ij].[USER_SkuDe] AS [Parent SKU]
    ,[AV].[USER_SkuDe] AS [Varianten SKU]
FROM (
    SELECT [A].[Artikelnummer]
        ,[A].[Bezeichnung1]
        ,[A].[USER_SkuDe]
        ,[S].[AuspraegungID]
    FROM [dbo].[KHKArtikel] [A]
    INNER JOIN [dbo].[LBSysStock] [S]
        ON [S].[Artikelnummer] = [A].[Artikelnummer]
    ) ij --inner join
FULL OUTER JOIN [dbo].[LBSysArtikelVarianten] [AV]
    ON [ij].[AuspraegungID] = [AV].[AuspraegungID]
        AND [AV].[Artikelnummer] = [ij].[Artikelnummer]


How can I include more query in one query?

Wouldn't this be sufficient for your update, since the subquery providing the update value evaluates to 0 if there aren't any answers for your question.

UPDATE tbl_questions q
SET q.answercount = (SELECT COUNT(*) FROM tbl_answers a WHERE a.qid = q.id)
WHERE q.answercount = 0


How to get SQL Server Compact 4.0 Database content query?

You can use my ExportSqlCe command line tools to export the schema and data, and then use sqlcmd to run the resulting script against a SQL Server 2014 database https://exportsqlce.codeplex.com/wikipage?title=Command%20line%20utilities&referringTitle=Documentation


Check the history of the agent job for the Snapshot agent on Whatever server is running as the Distributor. Drill down into the history of the agent job and you should find an error. (The error is often listed in the step preceeding the one that is retrying). Will probably be lack of space, etc.

If this does not reveal anything useful then you may need to switch on verbose logging to try and get a better error of the root cause. Its a combination of changing the agent profile and configuring a logging location external to SQL Server. These posts should get you there :-

https://stackoverflow.com/questions/1724322/where-is-the-log-file-located-when-verbose-history-logging-is-turned-on-for-sql

http://blogs.msdn.com/b/repltalk/archive/2010/03/17/troubleshooting-snapshot-agent-hang.aspx


Optimization for aggregation and grouping with MySQL

Using temporary and filesort are not as evil as everyone makes them out to be.

Do not split a DATE into year, month, and day columns; it will cause more pain than it is worth.

I prefer (in your specific case) to do GROUP BY LEFT(c.date, 7), although it may not speed up anything.

"Summary Tables" is the way to go for that type of query, assuming you have large tables.  More discussion in my blog.  You would add one (maybe more) row to the summary table each night, then a query against that table could summarize any day range, including your month.

Do not store AVG(...) in a Summary table; it is probably more 'correct' to store SUM(foo) AS sum_foo and COUNT(*) AS foo_ct, then have the report

SUM(sum_foo) / SUM(foo_ct) AS 'Average foo'

Probably the optimal summary table would have

PRIMARY KEY(goal_id, date)

and have sum_conversions calculated from SUM(c.conversions), plus anything else relevant.  Often, one summary table can handle a small set of standard 'reports'.


Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB

Check that if you have indexes (clustered/nonclustered, full text).
Use sp_spaceused 'your_table_name' to check that you can actually discard indexes.
Check what type of table is used.
In your example of 0.2KB=205 Bytes you will have 38 rows per data page if your table is heap and 39 rows per data page if it is clustered table.

Please see below the example:

IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages
(
KeyField SMALLINT --IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null
)
GO

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 39

-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
SELECT 'This is Heap. Note Data Space'
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO

IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages
(
KeyField SMALLINT IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null
)
GO

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 39

-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
SELECT 'This is Clustered Index. Note Data Space'
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO

Check for random inserts/updates/deletes in your table. This may be an issue as free space is not reclaimed back. The free space may be wasted during page splits too.

Please see below the example with average row size 205 Bytes and 200 rows (just like in your case). Table data size is 1.57 MB:

IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages
(
KeyField SMALLINT IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null
)
GO

Enter the data

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 8700))
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 200

DELETE FROM sparse_pages
WHERE LEN(Filler)>300
GO
-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO


Filter based on values after join operation - MongoDB

In MongoDB 3.6+ you can add a pipeline to your $lookup stage to include additional join conditions.

A solution was less obvious than I expected since your source lookup field is an array, but the following should return the expected outcome:

db.collection1.aggregate([
    { $match: {
        _id: "col1id1"
    }},

    // Filtered lookup based on agg pipeline
    { $lookup: {
        from: "collection2",
        // let references fields in the original collection
        let: { property: "$properties" },
        pipeline: [
            { $match:
                // use $expr to compare fields using let variables
                { $expr:
                    { $and: [
                        { $in: [ "$_id", "$$property" ] },
                        { $eq: [ "$boolean_field", false ] }
                    ]}
                }
            },
            { $project: { "_id": 0, "name": 1 }}
        ],
        as: "property_names"
    }},

    // Project desired output fields
    { $project: {
        "name" : "$name",
        "property_names": {
            $map: {
                input: "$property_names",
                in: "$$this.name"
            }
    }
    }
}
]).pretty()

Sample output:

{
    "_id" : "col1id1",
    "name" : "col1doc1",
    "property_names" : [
        "doc1",
        "doc3"
    ]
}


PostGIS window function not picking up index?

A query reading the whole table will not profit from the index. It's cheaper to read the whole table sequentially.

The spatial GiST index you display can be useful for other types of queries filtering a small percentage from the table, like:

SELECT * FROM point_data WHERE ST_DWithin($input_geometry, geom, 5000);

Understanding the decisions of the Postgres query planner requires intimate knowledge of Postgres. Start by reading this chapter of the manual and follow links from there:

How the Planner Uses Statistics

And more particularly, about spatial indexes in the PostGIS manual:

Taking Advantage of Indexes


Are there any SQL Server or MySQL versions that allow MULTIPLE NULL for a UNIQUE Constraint?

All versions of MySQL and derivatives (MariaDB, Drizzle, Galera, etc.) ignore  nulls in UNIQUE constraints. This allows for any number of rows that have nulls in the columns of a unique constraint. Note that this is the behaviour defined in the SQL standard.

On the opposite, all SQL Server versions, at least from 2000 till now, restrict to only one NULL in unique constraints. I think there was a Connect request to have this changed but since it would break backwards compatibility (in lots of existing databases that have been using the feature as it is for more than 20 years), I highly doubt this will ever be changed.

If you want to have the standard behaviour (multiple nulls) in SQL Server, you can use a filtered index in versions 2008+. Example:

CREATE UNIQUE INDEX Nulls_ignored_UQ
  ON tableX
    (columnA)
  WHERE (columnA IS NOT NULL) ;

One disadvantage is that you can't use this column as a target for a FOREIGN KEY constraint (only PRIMARY KEY and UNIQUE constraints can be used for that, not unique indexes.)

On the opposite issue, if you want to allow only a single null in MySQL, you can use a computed column in recent versions (MariaDB 5.2+, MySQL 5.7+) and then add a second UNIQUE constraint:

ALTER TABLE tableX
  ADD columnA_single_null int AS (CASE WHEN columnA IS NULL THEN 1 END),
  ADD CONSTRAINT Single_null_UQ
    UNIQUE (columnA_single_null) ;

Test in dbfiddle.uk


Accessing nested array in json blob in PostgreSQL

Your example data and your example query do not fit together.  There is no JSONB field named 'MMC' in the data, but there is in the query, for example.

Assuming the query is the thing that is correct, then something like this would work:

SELECT
  id,
  (
    SELECT
      max((value->1) :: int) AS _max_
    FROM
      jsonb_array_elements(data -> 'MMC')
  ) as MMCPow
FROM
  comp

Don't use "jsonb_array_elements_text" or ":: text" when you don't want them converted to text, use the -> operator to index into a JSONB array to get the 2nd element (JSONB arrays start counting from 0), and use "max" to take the max.


In MySQL why is the first batch executed through client-side prepared statement slower?

Probably you have an ordinary "cold cache" case.

When you INSERT 10,000 rows into a table, the rows need to be added to the appropriate place(s) in the "data".  Also, 10,000 entries need to be added to each index's BTree.  (How many indexes do you have?  Please provide SHOW CREATE TABLE.)

If you have AUTO_INCREMENT, then the rows will be "appended" to the table; this is not a caching issue.  If, on the other hand, you have a UUID index, or some other 'random' index, then the "inserts" into the index will be random -- involving read-modify-write.  The read and the write are cached.  But, if the system were "cold" when you did the first 10,000, a lot of reads were probably necessary.  Multiply that by how many indexes you have.

How big is the table?  If it is small enough to fit into innodb_buffer_pool_size (if InnoDB) or small enough for the indexes to fit into key_buffer_size (if MyISAM), then soon all the index blocks will be cached, and the inserts will speed up.

If the table is too big for the cache, then the 'random' indexes will continue to hit the disk (and be slow).  AUTO_INCREMENT will continue to be fast.

5 seconds is about 500 reads from a commodity spinning drive.  So, I would guess that you have a modest sized table.  Since the next 10K rows go in <1sec (<100 disk hits), I will guess that it fits in cache so far.

I often recommend 100-1000 for chunk size.  This is partially because 5 sec can be a problem.  You may as well COMMIT after each chunk, since the benefit of delaying it is minor.  Furthermore, if you wait "too long" to commit, the 'transaction' could overflow the log_file, leading to an inefficiency.

A chunk size of 100 is about 10% slower than the theoretical maximum.  1000 is about 1% slower than max.

If you have replication, keep in mind that the 5 seconds will interfere with anything else being replicated.  (Another argument against 10K.)


Mp3 file directly in a Database Without using BLOB

There is probably no way to have a binary piece of data (file) stored in a database (either relational or non-relational, NoSQL, that is) as "not blob" and without any need to decode it at the same time.

If you don't want to decode it, store it as a BLOB.
If you don't want to store it as a BLOB, store it as a CLOB and then decode it.
If you want neither of the above, consider another means of storage
Accessing a database directly from "outer world" is generally a bad idea.


What does IDLE state denotes in a row of pg_stat_activity?

This has been answered in postgres mailing list

  Each row in pg_stat_activity represents an established connection to
  the server from a client. "idle" means the client is not currently
  executing a query nor in a transaction.

  If query_start_date is 2 days old, that just means the last query to
  be executed on that connection was two days ago.

  Not really a reason for worry, unless you have so many connections
  open that it's consuming more RAM than you can afford. It's generally
  desirable for a connection pool to have a few idle connections so
  queries don't suffer the latency of establishing a new connection.


Dictate edition during unattended install?

Per my answer here, the installation media contains every edition, and it's the licence key that dictates which licenced edition is available. The ISO you get from MSDN comes with a key for the edition specified on MSDN, but the key can be replaced with any valid key, as it's in a configuration file.

If you need to deploy an arbitrary licenced edition, you'll need to collect a set of licence keys for each edition you want to deploy, then use conditional logic in your scripts to use whichever one is appropriate.

The main piece you're missing, though, is to perform an edition upgrade. This will take the default install from Enterprise Evaluation to the edition defined by the licence key provided.

Here's the relevant part of the script I used in such a deployment, that uses the licence key that was provided with the installation media (presumably, you'll want to modify that part):

"C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe"
    /ACTION="Upgrade" /QS /CONFIGURATIONFILE="%installMediaPath%\x64\DefaultSetup.ini"
    /INSTALLMEDIAPATH="%installMediaPath%\x64\Setup" /INSTANCEID="MSSQLSERVER"
    /INSTANCENAME="MSSQLSERVER" /IACCEPTSQLSERVERLICENSETERMS

Note the presence of the user-defined variable %installMediaPath%, which I assume you'll have in your script. This script was written for 2008 R2, but I think it should work with 2012 (untested).


How do I encrypt data to be decrypted on a linked server? SQL

It's possible to do what you are suggesting. You can't create the same asymmetric key directly using parameters, but you can backup the database containing the asymmetric key and backup the database master key on the data server. Restore them on the Interface server and drop the private key on the data server using ALTER ASYMMETRIC KEY remove private key. That way the data server can only encrypt and the Interface server can decrypt using the same asymmetric key.

Or, you can create a symmetric key encrypted by the asymmetric key using the same key_source and identity_value on each server and transfer the data in it's encrypted stated, then decrypt it on the Interface server. It does not matter, in the latter case, if the asymmetric keys are different because each protects keys that are identical on each server. If your goal is to encrypt data in transit, then the second one will be easier to recover if there are any issues.


Extract return type of functions from metadata

Based on a quick and simple test:

ALL_ARGUMENTS

select * from
all_arguments
where
position = 0
and data_level = 0
and argument_name is null
and data_type = 'REF CURSOR';

position 0 is the first argument of a program unit. If it does not have a name/level (data_level = 0 and argument_name is null), then it is the return argument of the function, and you are looking for sys_refcursor type, which is displayed as REF CURSOR in this view.


Apply in a CSV file to multiple databases

Load the CSV file into a "staging" table. From there you can easily do the UPDATE/INSERT/DELETE in a single statement for each one

Something along the lines:

update real_table
  set ...
where exists (select 1 from staging_table where ...)

insert into ...
select
from stage_table
  left join real_table
where real_table.some_col is NULL

delete real_table
where not exists (select 1 from staging_table)

Then delete the data from the staging table.

Probably the fastest way to do it. The only drawback is that you have the data for one table twice in the database (until it is deleted from the staging table).


Accept remote TCP/IP request from aLL IPs in SQL server

I found the reason of problem.

I don't know for what SQL or other services had changed the value type of TcpPort fields in registry (HKEY_LOCAL_Machine\Software\Microsoft\Microsoft SQL Server\Instance ID\MSSQLServer\SuperSocketNetLib\TCP\IPALL\TCPPort ).

It's converted to Binary or DWORD type.

but the solution : I removed TcpPort value and then create a new string value and named it TcpPort and set it to 1433 and then service started successfully.

there is no need to say that I checked that field over and over and it doesn't has any whitespaces.


Mysql upgrade to 5.7: Data too long for column

Presumably the problem is that your sql_mode variable now has STRICT in it, which means that inserts/updates with varchar values that are too long for the defined length of the columns now lead to errors, whereas previously the values were truncated without any errors.

To go back to the old behaviour, you can edit your /etc/my.cnf file, look for the sql-mode variable, and remove the STRICT keyword from it. Then either restart mysqld or just log in and set sql_mode dynamically:

SET GLOBAL sql_mode='the same value as in the .cnf file here';


Could not find proper my.cnf, so had to find the preferred location where mysql is searching for my.cnf by using the command:

mysql --help | grep cnf

which displays

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

So I created a file  /etc/my.cnf with the required settings, restarted the server, and everything worked like a charm.


mysql join 3 table retain order of data order by used in subquery

This kludge allegedly works:  Tack LIMIT 999999999 onto the subquery.  That forces the optimizer to use the ORDER BY.  Normally (in newer versions of MySQL), the Optimizer simply throws away the ORDER BY in certain subqueries -- because the SQL Standard says that a subquery delivers an unordered set.  A reference.

(Not relevant in your case.)  Meanwhile, explicitly add ORDER BY in the subquery; don't depend on GROUP BY providing an ordering.  That MySQL 'extension' is in flux.  (If the GROUP BY and ORDER BY are the same, the Optimizer is smart enough to do them together.)

Because of the OR, the only practical way to do the subquery is with a full table scan of the 2M rows.

Optimization tip:  Smaller --> faster, especially if the table is too big to be cached.  So...

Don't use BIGINT (8 bytes) when something smaller would suffice.
Don't use INT (4 bytes) for a 'flag'.  TINYINT is 1 byte.
Don't use TEXT when VARCHAR(255 or less) would suffice.  (There is an extra savings when a tmp table is needed.)


ALTER AUTHORIZATION ON test_user.table1 TO dbo

OR

ALTER SCHEMA dbo TRANSFER test_user.table1

The older sp_changeobjectowner is deprecated

Some FYI: SQL: transfer database schema


Replication of databases with specific prefix on master

Dynamically, no.

You could, however, write a SELECT against information_schema.TABLES to create the replicate-do-db list for subsequent manual (or programmatic) placement into the config file.

Are you really adding more databases frequently?

Would it be better to, instead, do replicate-ignore-db on the rest of the dbs?

Also, consider using binlog-... so that the filtering is done sooner (on the Master instead of on the Slave).  (Yes, there are valid reasons to delay the filtering until the Slave.)