Actual rows read in a table scan is multiplied by the number of threads used for the scan

Parallel heap scan

You might be expecting distribution among parallel threads as in the following toy example:

SELECT TOP (5 * 1000 * 1000)
    n = ROW_NUMBER() OVER (ORDER BY @@SPID)
INTO #n
FROM sys.columns AS C
CROSS JOIN sys.columns AS C2
CROSS JOIN sys.columns AS C3;

SELECT COUNT_BIG(*)
FROM #n AS N
GROUP BY N.n % 10
OPTION (USE HINT ('ENABLE_PARALLEL_PLAN_PREFERENCE'));

In that plan, the heap table is indeed parallel scanned, with all threads coordinating to share the work of reading the whole table:

or in SSMS view:

Your case

But this is not the arrangement in your uploaded plan:

The heap scan is on the inner side of a nested loops join*, so each thread runs a serial copy of the inner side, meaning the there are DOP (degree of parallelism) independent copies of the Table Spool and Table Scan.

At DOP four, this means there are four spools and four scans, as evidenced by the Number of Executions = 4 on the table scan. Indeed, the heap table is fully scanned four times (once per thread), giving 250,000 * 4 = 1,000,000 rows. The lazy spool caches the result of the scan per thread.

So the difference is that your parallel scan is four serial scans in parallel, rather than four threads cooperating to parallel scan the heap once (as in the toy example above).

It can be challenging to conceptualize the difference, but it is crucial. Once you see the branch between the two exchanges as DOP separate serial plans, it becomes easier to decode.

Of course the plan is wildly inefficient, and the spool adds little value. Notice the join predicate is stuck at the nested loops join, rather than being pushed down the inner side (making the join an apply). This is due to the complex join conditions involving ISNULL.

You might get a slightly better-performing plan by making your nc_assignedTouch index clustered rather than nonclustered, but the bulk of the work would still be happening at the join, and the improvement would almost certainly be minimal. A query rewrite is probably necessary here. Ask a follow-up question if you want assistance expressing the query in a more execution-friendly way.

For more background on the parallelism aspects, see my article Understanding and Using Parallelism in SQL Server.

Footnotes

* There is one general exception to this, where one can see a true co-operative parallel scan (and exchanges) on the inner side of a nested loops join: The outer input must be guaranteed to produce at most one row, and the loop join cannot have any correlated parameters (outer references). Under these conditions, the execution engine will allow parallelism on the inner side because it will always produce correct results.

You may also notice that operators below an Eager Spool on the inner side of a parallel nested loops join also execute only once. There are still DOP copies of these operators, but the runtime behaviour is that only one thread builds a shared indexed structure, which is then used by all instances of the Eager Spool. I do apologise that all this is so complicated.


I got following results

SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL;
-- 5276

SELECT * FROM gv$session where SID = SYS_CONTEXT('USERENV','SID');
-- No result

SELECT * FROM gv$session where SID =5276;
-- 4 results for 4 node RAC
-- 1 result matches my USERNAME,OSUSER, MACHINE and PROGRAM
-- 3 results are different from my USERNAME,OSUSER, MACHINE and PROGRAM

SELECT * FROM v$session where SID = SYS_CONTEXT('USERENV','SID');
-- 1 result

I cannot explain but following query in 4 node RAC gives interesting output.

SELECT '1' AS QUERY_NUMBER,S.* FROM gv$session S where SID = SYS_CONTEXT('USERENV','SID')
UNION ALL
SELECT '2',S.* FROM gv$session S where SID = ( SELECT  SYS_CONTEXT('USERENV','SID') FROM DUAL)
UNION ALL
SELECT '3',-1,S.* FROM v$session S where SID = SYS_CONTEXT('USERENV','SID');

QUERY_NUMBER = 1, no rows

QUERY_NUMBER = 2, 4 rows (4 node RAC)

QUERY_NUMBER = 3, 1 row


Optimised way to search over 2 milllion poi data in mysql

You can use Geohashing (Geohasing)

You have to create a geohash for every POI in your table.

And then based on the precision that you choose you can use
LEFT(your_geohash_column,'c1','c3','c4','c5','c6',c7','c8')
Where the c's are the 8 neighbors from the center calculated by your desired precision.

Keep in mind that you are limiting the results with the precision which you can consider it a radius.


That's perfectly acceptable, it basically states:

  You have to provide a value for this, but I have no clue what that would be.

If you define a default value, you claim that this is a sensible value for any row that doesn't supply a value - which is not really feasible for most attributes.

Think about a column named first_name - you want to make sure a value is supplied but there is no way you can come up with a sensible default value for that. Or a column named salary in an employee table.


If a query contains two identical subqueries, will they both run?

Identical subqueries are normally only performed once. However, the way you write a query isn't neccessarily the actual execution order, so there are no guarantees.
To make sure, view your estimated execution plan with one or both of the subqueries, and compare them.

In your example, you could place the subquery in a JOIN instead, which would make it both reusable and more easy to read.

SELECT g.*, p.Amt AS 'TotalCost', p.Amt * 0.1 AS 'GST'
FROM Group g
LEFT JOIN (SELECT GroupID, SUM(Price) AS Amt
    FROM Product
    GROUP BY GroupID) AS p ON p.GroupID = g.GroupID


I can answer the first question, the rest of your description is too confused for me to follow.

The first 1000 rows were inserted successfully into the existing partition, but inserting the row with id = 1000 failed. This error caused the whole transaction to be rolled back, including the previously inserted rows.

One thing that may also confuse you is that with range partitioning, the upper end of the range is excluded. So if you create a partition

FOR VALUES FROM (1) to (1000)

it can only accommodate values up to 999. This looks surprising with discrete data types like integer, but makes life much easier when using other data types:

FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2020-01-01 00:00:00')

is so much nicer than having to use an upper limit of 2019-12-31 23:59:59.999999.


Change Permissions of MSSQL Server generated files

By default .mdf file permissions are granted to Administrators Group and SQL Service Account when a database is restored.


CPU Time Slower in SQL Server 2014 than in SQL Server 2008

If you have not done so, I would highly recommend:

1.> Installing the right version of Integration Services for Hyper-V on the host and guest(like VMWare Tools). This alone can be a performance boost.

2.> Within Hyper V manager, disable compatibility mode on the processors.

3.> Consider using a SCSI controller for data volumes.

4.> Consider disk sizes that are fixed rather than expanding.

Should you implement any of these techniques, I'd be interested in knowing if any performance improvement was gained.


SQL Server avoid repeating the collation type multiple times in a query

You can once create a view using column definitions like this:

FirstName COLLATE SQL_Latin1_General_CI_AS, ...

Then anytime you'd like to use User tables, you can use this view instead.


Scheduling duplication of two tables across instances

As long as all the changes happen only on your first instance, transactional replication is what you are looking for. It was designed for this purpose and usually operates close to realtime.

You can find an introduction to replication here: http://www.sqlservercentral.com/stairway/72401/

If you need to be able to apply changes on both machines, merge replication might be the better option, but is was not designed for realtime operations. The above link has an introduction to merge replication too.


How to use Limit in query on my index column without scan all rows?

EXPLAIN is not as smart as one would like.  It provided 42415 without noticing your LIMIT 3.

The important clue that says that it will use the index is the Key column lists the index name.

That query, with that index (I assume ID is a numeric datatype) will touch only 3 rows of the index, then 3 rows of the data.

Two ways to get more info:

EXPLAIN FORMAT=JSON SELECT ...;

or

FLUSH STATUS;
SELECT ...
SHOW SESSION STATUS LIKE 'Handler%';

I would expect to see 2 (3-1) in one or two rows, and not see a number that matches the table size.


How to handle null value number DataWarehouse

If you don't have a thorough understanding on the business meaning of the data point, you should NEVER put in a dummy or made-up value.

If this is a fact table, you will need to work with the business teams to understand the scenarios in which the NULL can occur.

If the column represents a monetary value, using zero works in most cases, but again, understanding the data point is necessary here too.

If you determine with the business teams that NULL can/should be represented by something else e.g. $0, or -1, etc., I recommend working with the folks responsible for the source data, and trying to affect the change there if possible. Then the appropriate business values flow through to the DWH in your ETL.

If the business determines a field/data point can legitimately have no value, then let there be nulls!

If the column is an identifier e.g. a primary key to another dimension or reference table, then it is a different story. In your dimension or reference table, there would typically be a row representing 'no value', which would have an id of it's own... This key would go in your fct table.

Regardless of what you work out with your business teams... ALWAYS document it all in your data dictionary, so that users of the data (and your ETL/DWH developers) have a reference back to what it was done that way.

Side Note: I hear lots of arguments for not allowing nulls which relate to "oh, what if users writing queries don't know how to properly account for NULLs in joins or where clauses, etc... This is never a good reason to support a design decision at the data model/etl level. This issue is best addressed at the data presentation or reporting layer. E.G. some BI tools have built in handling for this or abstract it away, etc.


Is index performance on a TIMESTAMP(6) column impacted when converting it to a number

If you have a simple b-tree index on mycolumn, then yes, you would need to avoid calling functions on that column in order to be able to use the index to filter rows.  In this case, it would seem to make much more sense to convert your numeric literals to timestamps than to do the reverse

 SELECT * FROM MYTABLE
 WHERE my_column > to_timestamp( to_char(20110622105738), 'YYYYMMDDHH24MISS' )
   AND my_column <= to_timestamp( to_char(20150622105738), 'YYYYMMDDHH24MISS' )
 ORDER BY MYCOLUMN

Now, you could also create a function-based index

create index idx_my_fbi
    on mytable( TO_NUMBER(TO_CHAR(MYCOLUMN, 'YYYYMMDDHH24MISS'), '99999999999999') );

which your original query could use.

Of course, in either case, whether Oracle actually uses the index will depend on how selective it expects the predicate to be.  Since you are returning 4 years worth of data, it seems unlikely that an index would be beneficial even if it was eligible to be used.  Unless your table has hundreds of years worth of data in it, a table scan (or a full scan of one or more partitions if the table is partitioned) would seem like a more appropriate query plan.


Michael Green is right: the devs are trying to thwart parameter sniffing, which happens when SQL Server compiles a plan that is great for one set of parameter values, but horrible for others.

You'll want to use OPTION (RECOMPILE) on the statement(s) with issues, not WITH RECOMPILE on the procedure. And I wouldn't recommend the local variables "trick" - it just makes the code messier; better to use OPTIMIZE FOR UNKNOWN on modern versions if that's the method that works best in your scenario. (For a whole lot more on this topic, see this great post by Paul White.)

Also, if many parameters are optional (so the query has things like WHERE col = @param or @param IS NULL), this is what I call "the kitchen sink" - sometimes dynamic SQL can be a much more effective solution. You didn't show the rest of your code, only that you were already using the local variables trick, but it will essentially look like this:

DECLARE @sql NVARCHAR(MAX) = N'SELECT ... FROM ... WHERE 1 = 1';

IF @cas_name IS NOT NULL
  SET @sql += N' AND cas_name = @cas_name';

IF @instance_name IS NOT NULL
  SET @sql += N' AND instance_name = @instance_name';

IF @verified_id IS NOT NULL
  SET @sql += N' AND verifier_id = @verifier_id';

...

SET @sql = @sql + N' OPTION (RECOMPILE);';
PRINT @sql;

EXEC sys.sp_executesql @sql,
  N'@cas_name VARCHAR(20), @instance_name VARCHAR(50), @verifier_id INT, ...',
  @cas_name, @instance_name, @verifier_id, ...;

This approach of only adding clauses for parameters that are actually supplied protects you from caching plans based on different sets of parameters (for example, if I supply @FirstName on first execution, the seek plan on that column that gets cached isn't going to help when I ask for @LastName LIKE N'%s%'). The OPTION (RECOMPILE); at the end protects you from plans that can vary greatly based on the values of the same parameters from execution to execution (for example, WHERE name LIKE N'%s%' should yield a different plan shape than WHERE name LIKE N'Q%').

This typically works best with the server setting optimize for ad hoc workloads, which you can read about here and here. Essentially what this does is prevents your plan cache from filling up with all these slight plan variations, unless they are used more than once. (Yes, with OPTION (RECOMPILE), the point is moot; however, the server setting can't hurt for the rest of your ad hoc query workload, and I've never come across a downside to having it on.)

This is pretty safe from SQL injection, since you don't have to worry about concatenating user input into SQL strings (all parameters are strongly typed), but it can't hurt to read these topics on dynamic SQL:

Protecting Yourself From SQL Injection - Part 1
Protecting Yourself From SQL Injection - Part 2
The Curse and Blessings of Dynamic SQL (Erland Sommarskog)
Dynamic Search Conditions in T-SQL (Erland also)

I have videos about my solution to "the kitchen sink" here and here as well as a blog post about it.


Can I minimize maintenance of duplicated Windows + SQL Auth logins?

I would suggest moving all clients to the domain and removing the SQL logins as the AD route is much more secure. If this is not an option then I would probably remove the AD logins and have RDP users use SQL logins as having two logins per user doesn't make much sense.

If for some reason you decide you have to go the two login route I would write a stored procedure to replicate the settings from AD users to your SQL logins. This could be triggered with a DDL trigger. This way you would only have to update one set of users.

I would also be careful in using AD groups if you are not the only person responsible for the administration of the domain. It's not clear from the database who has the permissions specified for the group as users be dropped in and out of the AD group without your knowledge.


Postgres Minor Version Upgrade from 11.4 to 11.7

The documentation advises:

  Minor releases never change the internal storage format and are always compatible with earlier and later minor releases of the same major version number. For example, version 10.1 is compatible with version 10.0 and version 10.6. Similarly, for example, 9.5.3 is compatible with 9.5.0, 9.5.1, and 9.5.6. To update between compatible versions, you simply replace the executables while the server is down and restart the server. The data directory remains unchanged — minor upgrades are that simple.

In addition, you should check the release notes for additional requirements.
In your case, additional activities are necessary if you are using the intarray contrib module.


We now have a table with over two billion rows and 700GB of data. It's
  a small number of columns, one being a 256 char varchar used to store
  serialised blob data. This is now of a size that when people try to
  insert, delete and update to it (via the app) users occasionally lock
  each other out. This can only get worse as time goes on.

Why do users lock each other out?  Is it that they are all accessing the same rows?  Perhaps the real solution to improve performance is query and index tuning so that only the needed data are touched regardless of table size.

Archiving read-only historical data will improve performance of full scans, improve manageability of current data, and perhaps reduce I/O due to a the smaller working set of active data.  You could simply move old data to an archive table in the same database during a scheduled daily process.  Rather than have users invoke a separate process to copy the data back, consider using a UNION ALL view of current and archived data when users need to report on both current and read-only historical data.


Can a full DBCC CHECKDB operation be safely offloaded to a restored backup on a different server?

SQL Server expert Paul Randal discusses this option in his post CHECKDB From Every Angle: Consistency Checking Options for a VLDB

Use a separate system
This alternative is relatively simple – restore your backup (you are
taking regular backups, right?) on another system and run a full DBCC
CHECKDB on the restored database. This offloads the consistency
checking burden from the production system and also allows you to
check that your backups are valid. There are some drawbacks to this
however:

You need to have sufficient disk space on the spare system to be able to restore the backup onto. If the production database is
several
TB, you need the same several TB on the spare box. This equates to a
non-trivial amount of money – initial capital investment plus ongoing
storage management costs. (Hopefully a future release will alleviate
this – while at Microsoft I invented and patented a mechanism for
consistency checking a database in a backup without restoring it.)

If the consistency checks find an error, you don’t know for sure that the database is corrupt on the production system. It could be a
problem with the spare box that’s caused the corruption. The only way
to know for sure is to run a consistency check on the production
system. This is a small price to pay though, because most of the time
the consistency checks on the spare system will be ok, so you know the
production database was clean at the time the backup was taken.

Other than the drawbacks that Paul mentions, I'm not aware of any problems with offloading the consistency checks to another system.
I provided an answer to a question - How to conduct integrity test on SQL Server database backup file? where I described the process we use at my shop.

At my shop, we have a 'play' server that we use to automate restores
of the latest FULL/DIFF of the databases we deem 'important'. We use
Windows Task Scheduler to kick off a bat file which has several SQLCMD
steps. After successful restores, we run full DBCC CHECKDB WITH
ALL_ERRORMSGS,NO_INFOMSGS and output the result to a txt file. Then we
email the output txt files to the database group for evaluation.
This process tests TWO things

Can I restore my backups?
Is the data in the backup structurally intact (DBCC)

One additional note:  Corruption can happen anytime.  Immediately after a good DBCC CHECKDB or successful backup, so make attempts to run DBCC consistency checks often.


It's LOCATE (in DB2) which is equivalent to CHARINDEX in SQL Server.

Here is the complete description of it with example.

Thanks!


Getting error when using Oracle imp command to import dmp file

Basically, your first step should be to try specifying a larger buffer (one large enough to hold your LOB).  And, so you're not surprised, this operation can take many, many hours (or even days) depending on the size of the import.
And, IIRC, this error can also be thrown if your import version is less than your export version... so check that, if moar buffer doesn't fix this.
If that fails, you'll need to provide moar details.

ORA-22993: specified input amount is greater than actual source amount

*Cause:  (1) For LOB write, the amount of data received is different from the amount that was indicated would be sent.  (2) For LOB copy and loadfromfile, the end of the source LOB/FILE value was reached before the specified input amount was copied/loaded.
*Action: (1) will happen when using OCI's piecewise mechanism with polling or with a callback function.  Modify the code either to send the amount specified or to pass 0 as the input amount so that any amount of data can be sent.  (2) will happen if the specified input amount is too large for the source LOB/FILE given the starting source offset.  Either decrease the starting source offset, or decrease the amount to copy/load.


barman + backup and restore + how to enable auto deletion from backup files

You should configure retention_policy.

Here's the syntax :

retention_policy = {REDUNDANCY value | RECOVERY WINDOW OF value {DAYS | WEEKS | MONTHS}}

You will find documentation here


How do I guarantee using Microsoft ODBC Driver 13 for SQL Server in a linked server?

It's not possible to use just ODBC. Linked servers use Distributed Queries which is dependent on the OLEDB interface. This is not an optional or interchangeable API. Below that API can be any OLEDB, ODBC or even JDBC provider so long as its implementation supports the OLEDB interface. For example, there is an OLEDB provider for ODBC which is one of the most common ways to run DQ against non-SQL Server data sources. SQL Server natively supports OLEDB so there's no need for the extra layer. Most/all of this information can be found in Books Online or MSDN.

Why do you need to use only ODBC? If want it for Always Encrypted, suggest you log a feature request via https://connect.microsoft.com/SQLServer/feedback/. I don't know if the scenario is even supported where you run AE through DQ. The "calling SQL Server" essentially becomes the client to the "target SQL Server" but you don't fully control the client since DQ isn't something you can modify.

If it's for some other reason, share details here so folks help come up with alternate solutions.


which time period covers date string over datetime column

A sargable exact alternative to DATE(ColumnName) = DATE'yyyy-mm-dd' is

ColumnName >= TIMESTAMP'yyyy-mm-dd' AND
ColumnName < TIMESTAMP'yyyy-mm-dd' + INTERVAL '1' DAY

Something like

ColumnName >= 'yyyy-mm-dd 00:00:00' AND
ColumnName <= 'yyyy-mm-dd 23:59:59'

is not only more complicated (and bug prone), but also leaves out any values between 'yyyy-mm-dd 23:59:59.000001' and 'yyyy-mm-dd 23:59:59.999999'.


Good explanation.  Good idea.  I'll add my 2-cents...

"Sharding" would have a few merchants on one server (plus its replicas); a few other merchants on another shard, etc.

You are suggesting that it is practical to have all the data everywhere, but "shard" the accesses to minimize RAM requirements.

First let me make a strong suggestion.  Have all PRIMARY KEYs be composite, starting with the merchant_id.  This will cluster all the rows for a given merchant together.  This is in addition to having most of the indexes start with that same column.

If you currently have

PRIMARY KEY(id),   -- auto_increment
INDEX(merchant_id, ...)   -- a few of these

then change to

PRIMARY KEY(merchant_id, id)  -- for m_id for clustering; id for uniqueness
INDEX(id)   -- to keep auto_increment happy
INDEX(merchant_id, ...)   -- fewer of these

Reconsider the secondary indexes; you may not need as many, since the rows are already clustered by merchant_id.

Granted, inserting not-for-this-shard merchant rows will need to some blocks; but they will be transient and not cost too much room in innodb_buffer_pool_size.

If you are pseudo-sharding the slaves, then if a slave dies, all the other slaves are ready to take over for any Merchant's requests.  The only problem is that queries will be slow due to the lack of cached data.  I see the 'instant' failover as being more of a benefit than the 'slower' is a detriment.  Anyway, the caching will catch up pretty soon.

How much write traffic is there, versus read traffic?

A lot of write:  Then you need to shard the Masters (and Slaves).  But would this mean that you are still doing all the writes to all Masters?  This is clumsy?
Mostly read traffic:  (That's what I was commenting on above.)

Keep in mind that every Slave performs every write that happened on its Master.  Corollary:  You can't get write scaling without Sharding the Masters.

PK example

Using your specifics (from Comment):  You currently have an 8-byte id BIGINT?

PRIMARY KEY(id),   -- auto_increment, 0 extra bytes (clustered with data)
INDEX(account_id, timestamp)   -- INT + TIMESTAMP or DATETIME : 4+5 + 8 bytes

-->

PRIMARY KEY(account_id, timestamp, id),   -- 0 extra bytes
INDEX(id)   -- 8 + 4+5 bytes

That is the disk footprint will be virtually identical.  To elaborate:

The PK is "clustered" with the data; this makes the data ordered according to the PK.  Hence, the PK takes zero extra bytes on top of the data.  (OK, there is a small overhead for the non-leaf nodes of the BTree.)
Any secondary index has a copy of the PK in it.  (This is how it gets to the actual row.)
So, in this pair, the secondary index has exactly the same fields, with exactly the same total space, just rearranged.
Here's the feature I am after:  When looking up rows for one account, perhaps over a timestamp range, the data PK will now be used instead of using the secondary key, then jumping through id.  And all the desired rows will be adjacent to each other.  The result:  Such SELECTs will be faster (maybe 2x, but there are a lot of variables).

Back to the "old wives tale" that you should keep the PK "small".  Yeah, that is a general rule.  But I have just explained an exception.

If you have several more secondary keys on the same table, then the extra secondary keys will be bulkier.  But you want to skip on RAM, and don't need to worry about disk.  Again, what I suggest fits well.


Take a look at this list of mine :

Employee/ Student Information
Account Receivable/Payable
Attendance Record
Inventory
Class/Task Records

Hope this list helps. For further reference, you can try to look at this : http://www.techyv.com/article/most-used-database


Why is my index on the join keys not used in a very simple hash join case?

Providing information on the actual query plan used would help.

Speaking in general, the DB engine would have to use the two indexes to identify rows in both tables that have matching aid values; then, look up all the actual rows in the daily_feeds table to see if menu is 1, and then look up the rows in animal to get the aname values.

If there was a large number of rows to be returned, and if those are your complete tables, then it may well have decided it would be faster to simply load the tables in the first place, and scan them to find the matching rows and do the menu check (at which point aname is already there and available).

Let me note here that I'm most familiar with the way SQL Server uses indexes. It's possible that some of the following may not directly apply to PostgreSQL. However, most of the basics seem to match up, and I'm not going to delve into specifics enough to be likely to go too far wrong.

The optimizer has three ways to find rows in daily_feeds. It can simply look through the entire table; it can use the aid index to seek to a particular value or range of values of aid, then use the index information to go to the correct pages in the table to get the rows; or, it can treat the index as if it was a table, and scan that (possibly looking up the rows in the full table, if all the data it needs isn't in the index).

There are many consideration an optimizer must make in figuring out how to get the data it needs. One factor is the amount of pages it might have to load to get the data. Loading pages form disk is one of the most time-consuming tasks for a database. So, if it can limit the number of pages it may need, then that should speed up the query.

For this query, we need to find two columns in animal (aid and aname), and three in daily_feeds (aid, menu, shift).

It has two ways to identify the rows it's interested in: rows where an aid in animal matches an aid in daily_feeds, and rows where menu is 1. I'll guess that statistics would indicate (certainly a foreign key relationship would, if it actually existed) that all or almost all rows would be returned form both tables based on the aid = aid check. That doesn't do a good job of reducing the number of rows we're looking for. However, menu = 1 knocks out a lot of rows. Since menu isn't in an index, we have to go to the table to filter it by that.

Once we've got the daily_feeds rows we want, we need to find the matching rows in animal to pick up aname. We can expect to be looking for around 500 rows, and must assume (worst case) they're evenly distributed through the animal data. I'm make another assumption here; that (even with a text column) the rows in the animal table aren't terribly wide. If the basic table is stored in 500 pages (ignoring the text data; it may be stored off-row, but we don't need it for this query), then the odds are that all the pages will have to be loaded to get the rows we need.

Now, there are two paths to proceed:

Use the aaid index on animal to find the 500 aid values we're looking for; then, using the index, go to the pages where the data lives and find them in each page.
Load the animal table directly, and scan it for the 500 aid values we're looking for directly.

The optimizer decided to use option 2. Looking at it from this perspective, that doesn't necessarily seem that odd.

Note: the engine may be deciding this entire plan before it takes its first step. This can lead to bad plans (if, for instance, it turned out that menu = 1 only brought back 5 rows, then using the index might be faster) on occasion. SQL Server can maintain statistics on columns that help it determine cardinality (how well a known value for column X will limit the target rows), to help it make these decisions. PostgreSQL has some sort of statistic facility as well - not sure if it's directly comparable or not.


Is SQL Server's fill strategy the same for multi-file tempdb as multi-file (non-tempdb) filegroups?

Does this same fill strategy apply to tempdb?

Yes it does.

To prove it to yourself you can do a test similar to one described here:

SQL Server Proportional Fill Algorithm Example

i.e. populate temp table with some amount of data and check space used for every tempdb file.

I cite here only the conclusion:

  File groups use proportional fill strategy across all the files within
  each file group and uses a round robin algorithm to write a
  proportional amount to the free space in the file and to each file
  within the file group.

  Understanding the proportional fill and round robin behavior in SQL
  Server is important not only for user databases, but especially in the
  case of tempdb as well. The SQL Server best practice is to have
  multiple tempdb data files to alleviate tempdb PFS and SGAM
  contention. It is easy to imagine when the tempdb data files sizes are
  uneven, then the extent allocations would be heavier on the bigger
  sized data files. This configuration would again cause a hotspot and
  not alleviate tempdb contention.

And here is a further reading:
Correctly adding data files to tempdb by Paul Randal

Among other things, Paul describes how to add files correctly to match existing file sizes, and the reason of doing so is proportional fill algorithm.


Does SQL Server route connections to a secondary replica which has commit mode as synchronous in case of an AG with multiple secondary nodes?

It will depend on your routing list.

SQL Server 2104 and SQL 2012 read-only routing directed traffic to the first available replica in the routing list, unless it was not accessible, and then it would direct the connection to the next replica in the routing list. When you have multiple secondary replicas available for read, it is not possible to spread the read load across those replicas.

In SQL Server 2016 you can configure load-balancing across a set of read-only replicas.

Details Here: https://blogs.msdn.microsoft.com/alwaysonpro/2016/05/02/sql-server-2016-alwayson-availability-group-enhancements-load-balance-read-only-routing/


What i understand from your question is you need to group your result on the basis of user_id for that you can try this one

SELECT user_id,GROUP_CONCAT(id),GROUP_CONCAT(content) FROM tbl WHERE sth='sth' GROUP BY user_id LIMIT 10;

If you need anything else please comment and update in your question.


Forced serialization on inline table valued function

Simply, your query doesn't have a high enough estimated cost.

SQL Server estimates how much effort a query will take, and reveals this unitless measure as estimated cost. If it's below your Cost Threshold for Parallelism, then SQL Server won't bother with a parallel plan. Your plan has an estimated cost of .129, which, since it is less than 5, means no parallelism.

Also in your plan XML is the detail that this query only took 1 millisecond. You have no need to go parallel. There's not enough work to justify the overhead of distributing effort across multiple threads.

As an aside for an earlier version of this question, even queries that would be TRIVIAL will go parallel if their estimated cost is high enough, and all the other conditions for parallelism are met.

A way to prove that something is preventing parallelism is  NonParallelPlanReason="CouldNotGenerateValidParallelPlan" in the plan XML.

Another way to test (I'm assuming you're on SQL Server 2014, because 2016 has an actual hint to use) is to add OPTION(QUERYTRACEON 8649) to your query. Please just use that for testing though, because adding undocumented trace flags to production code is really stupid not a good thing.


If replication is using Row Based Replication (RBR), then, say, an UPDATE statement is faster on the Slave than on the Master.  This is because all that is sent to the Slave is the changed record.  The Slave does not have to think, does not have to search for the row, does not get the rows that don't need to change.

With Statement Based Replication, the Slave is essentially repeating whatever complex processing was done on the Master -- with an exception:  If there are a bunch of SELECTs happening to figure out what to UPDATE/INSERT/DELETE, then those happen only on the Master.

If any server has "high" CPU usage, crank up the slow log; let's see what is causing it.  Almost always, performance can be improved by a better index, a reformulation of a SELECT, etc.


You need to first isolate the rows with 'ABIERTO' and 'CERRADO' and then do a FULL JOIN between them. Since MySQL has not implemented FULL join, there are various ways to achieve such a join. We will need the distinct values of (key, point_name) pairs, so the easiest way seems to be to get first these values and then do two LEFT joins, one to 'ABIERTO' and another to 'CERRADO' values:

SELECT
    d.point_name AS equipo,
    a.time       AS abierto,
    b.time       AS cerrado
FROM
    ( SELECT DISTINCT `key`, point_name
      FROM alarms_9_2015
    ) AS d
  LEFT JOIN
    alarms_9_2015 AS a
      ON  a.value = 'ABIERTO'
      AND a.key = d.key
      AND a.point_name = d.point_name
  LEFT JOIN
    alarms_9_2015 AS b
      ON  b.value = 'CERRADO'
      AND b.key = d.key
      AND b.point_name = d.point_name
ORDER BY
    COALESCE(a.time, b.time) ;

A composite index on (key, point_name, value) would improve efficiency.


How can I export a subset of tabledata from a prodution database into my local testing database?

I haven't used this tool, but Jailer promises to do just that

  Database Subsetting and Browsing Tool. Exports consistent, referentially intact row-sets from relational databases (JDBC)

http://sourceforge.net/projects/jailer/


How to do a restore of a large postgresql database?

Of course the best option would be to remember how exactly you took that backup...

If you want to restore the backup on a hosted database, it had better be a logical backup created with pg_dump. So make sure it is not a physical backup:

Run tar -tzvf 20200204_data_tar.gz and make sure it does not contain files like postgresql.auto.conf or PG_VERSION.

Once you have ascertained that, unpack the archive to a new directory and check what you have got:

mkdir x && cd x
tar -xzvf ../20200204_data_tar.gz

Then check out which dump format the file is and restore it accordingly:

“plain” format:

The archive contains a single text file with SQL statements, and somebody was weird enough to pack a single file into a tar archive.

Such a dump is restored with

psql -f <SQL-file> -h <hostname> -p <port> -U <user> -d <target-database>

“custom” format:

The archive contains a single binary file, and somebody was weird enough to pack a single file into a tar archive.

Such a dump is restored with

pg_restore -h <hostname> -p <port> -U <user> -d <target-database> <dump-file>

“directory” format:

The archive contains a file toc.dat and many files of the form number.dat.gz.

Such a dump is restored with

pg_restore -h <hostname> -p <port> -U <user> -d <target-database> <directory-containing-toc.dat>

“tar” format:

The archive contains the files toc.dat and restore.sql and many files of the form number.dat.

Such a dump can be restored just like the directory format above.


Any reasons at all *not* to use MySQL in this scenario

Here are the cons that I see, this being a somewhat subjective question:

There are political reasons for and against this setup.

I don't know if I can be any more clear than that.

I would think this would be a huge benefit, so I would use this as my main selling point, because otherwise I don't see why not use the existing infrastructure to do what you need:

No need to migrate export data from MySQL to a new DB

Note that that's really going to turn into a con for you pretty quickly, cos everyone will say "just put a stub in that converts the MySQL requests into MSSQL" and that's going to be the negative all over again.


How to create rolling backups for SQL Server 2008 R2

Ola Hallengren's excellent scripts provide solutions for database maintenance including backups - the scripts have won some awards in the last year, and are increasingly referenced by SQL Server luminaries. I've been using them for about a year - I love them because they are highly configurable - for backups, you define the retention period in the job in hours. The retention period only kicks in if you actually have refresh backups.

http://ola.hallengren.com/


Calling SELECT DISTINCT on multiple columns

How does this work exactly?

It gives you distinct combinations of all the expression in the SELECT list.

SELECT DISTINCT col1, col2, ...
FROM table_name ;

is also equivalent to:

SELECT col1, col2, ...
FROM table_name
GROUP BY  col1, col2, ... ;

Another way to look at how it works - probably more accurate - is that it acts as the common bare SELECT (ALL) and then removes any duplicate rows. See Postgres documentation about SELECT: DISTINCT clause.

  Should we ever do this?

Of course. If you need it, you can use it.


ERROR 1813 (HY000) at line x: Tablespace '`mydb`.`mytable`' exists

So apparently there were two copies of mytable in my DB dump. There was mytable (all lowercase) and MyTable (camel case). On Linux that's not an issue but I guess on Mac OS X it is an issue.


Find the functional dependencies that hold in a relation

For convenience, define the (implied) numbering of your question:

A->D
BD->E
AC->E
DE->B

Then substituting 1 and 3 into 4 gives
(A)(AC) -> B
which reduces to just
 5. AC -> B.


What database platform better at scaling?

Any of the big RDBMS systems or NoSQL systems can handle that sort of data load.  Back in 2002 I had a database table with 1B records in it that got more than 100k records a day, and that was running SQL Server 2000.  The systems today are much faster (both the hardware and the software) and should be able to handle that load going in with no problem.  The big problem becomes getting the data out, and having the data indexed properly to find it based on how you want to search.  And having enough memory to load the data into cache to find it faster.


Is it possible to configure backup filename conventions for Maintenance Plan Wizard in SSMS

I would recommend using Ola's scripts.

https://ola.hallengren.com/sql-server-backup.html

Not only are they awesome but they will give you the naming convention that you are looking for.


SQL Server '12 Using RAM equal to 2x size of the only user database

SQL Server will never ever ever give back any memory that it uses unless pressure is exerted on the service by the operating system. It's designed that way intentionally to do as much processing in memory as possible to prevent disk I/O.

In the comments above there's a linked article to A Sysadmin’s Guide to Microsoft SQL Server Memory which contains some good information explaining the specific use cases and behavior that you can expect. Generally though it's best to remember that the structure of data held in the SQL buffer cache is based on performance, not resource conservation. Make sure your maximum memory is set appropriately for your environment and needs.

If you are running SQL Server in a virtual environment where you are trying to reduce the amount of memory on an ongoing basis then you will need to use a balloon driver or other utility to exert memory pressure on the instance.

For performance purposes if you are seeing a sudden flood of complaints it's possible that the SQL instance is running out of memory and is paging to disk, which will result in a very significant performance hit. There are multiple recommendations, but my personal preference is to not use a page file at all which will result in certain crash dumps not being returned in the event of a windows fault.


Views are implemented as table plus rewrite rule in Postgres. Details in the manual, chapter Views and the Rule System.

The tricky part: not the view itself depends on involved functions, only the rewrite rule does. So this query should be your solution:

SELECT r.ev_class::regclass AS view, d.refobjid::regprocedure AS function
FROM   pg_rewrite r
JOIN   pg_depend  d ON d.objid = r.oid
                   AND d.refclassid = 'pg_proc'::regclass  -- only functions
WHERE  r.ev_class = 'v123'::regclass;  -- name of view here (optionally schema-qualified)

Returns all user-defined functions (excluding built-in functions).

The query assumes you did not define any additional rules for the view. Else filter the one with rulename = '_RETURN', which is the default name for the rewrite rules of a view.

The cast to regprocedure (not just regproc) returns the function with argument types, uniquely identifying it. The name is schema-qualified automatically if the current search_path would not resolve differently otherwise.


Redshift is missing in target database list of AWS SCT Schema Convertion Tool

OK, Amazon Redshift is only valid as a target database when you select OLAP or dataware house project option. If the project is created for an OLTP database Redshift is not an option as a target data platform.
Please refer to https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html for detailed list


Delete duplicates with specific columns as identifier

--=======================================================
-- delete the duplicate records from table @t
-- keeping a single unit of each
-- marcelo miorelli 24-nov-2014
--=======================================================

--=======================================================
--create a table variable and insert records in it
-- just for this example
--=======================================================
declare @t table ([num] int, com varbinary(512) )

insert into @t select 1, convert(varbinary(512), '6778981' )
insert into @t select 1, convert(varbinary(512), '6778981' )
insert into @t select 1, convert(varbinary(512), '6778982' )
insert into @t select 2, convert(varbinary(512), '6778982' )
insert into @t select 2, convert(varbinary(512), '6778982' )
insert into @t select 3, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778982' )
insert into @t select 4, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778983' )
insert into @t select 5, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778984' )
insert into @t select 6, convert(varbinary(512), '6778985' )
insert into @t select 7, convert(varbinary(512), '6778985' )
insert into @t select 7, convert(varbinary(512), '6778985' )
insert into @t select 8, convert(varbinary(512), '6778985' )
insert into @t select 8, convert(varbinary(512), '6778985' )
insert into @t select 9, convert(varbinary(512), '6778985' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )
insert into @t select 9, convert(varbinary(512), '6778986' )

--=======================================================
-- this is going to show the table before the deletes
--=======================================================
select * from @t

--=======================================================
-- this deletes the duplicates - leaving only 1 of each
--=======================================================
; with cte as (
    select *
        , row_number() over (partition by [num],com  order by [num],com ) as Picker
    from @t
    )
delete cte
where Picker > 1

--=======================================================
-- this shows the table after the deletion - without the deletes
--=======================================================
select * from @t


To rotate the logs you just need to execute

EXEC master.sys.sp_cycle_errorlog;

You can schedule the execution daily or as often as it makes sense in your scenario with a SQL Server agent job.


Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search

Switch to InnoDB, and

Set innodb_buffer_pool_size to at least 250M
Show us the tentative SELECTs
SHOW CREATE TABLE (DESC is inadequate)


Performing join on 3 tables & retrieving selected columns

You could try the following restructured query which uses joins instead of the sub-selects;

SELECT pirhd.GTIN,
       pirhd.INFORMATION_PROVIDER,
       pirhd.RETAILER,
       pirhd.TARGET_MARKET,
       MESSAGE_XML,
       pub.another_column
FROM   PI_RESUBMIT_HIERARCHY_DETAIL pirhd
       INNER JOIN Pi_Item Item
          ON     item.GTIN = pirhd.GTIN
             AND item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER
             AND item.TARGET_MARKET = pirhd.TARGET_MARKET
             AND item.RETAILER = pirhd.RETAILER
       INNER JOIN PI_PUBLICATION pub ON item.obj_id = PUB.OBJ_ID
WHERE  RESUBMIT_ITEM_DETAIL_ID = '88956457'
ORDER BY pirhd.GTIN

Another couple of possibilities;

WITH bi_pub AS
(SELECT DISTINCT obj_id , another_column
   FROM PI_PUBLICATION )
SELECT pirhd.GTIN,
       pirhd.INFORMATION_PROVIDER,
       pirhd.RETAILER,
       pirhd.TARGET_MARKET,
       MESSAGE_XML,
       pub.another_column
FROM   PI_RESUBMIT_HIERARCHY_DETAIL pirhd
       INNER JOIN Pi_Item Item
          ON     item.GTIN = pirhd.GTIN
             AND item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER
             AND item.TARGET_MARKET = pirhd.TARGET_MARKET
             AND item.RETAILER = pirhd.RETAILER
       INNER JOIN BI_PUB pub
          ON item.obj_id = PUB.OBJ_ID
WHERE  RESUBMIT_ITEM_DETAIL_ID = '88956457'
ORDER BY pirhd.GTIN

OR

SELECT pirhd.GTIN,
       pirhd.INFORMATION_PROVIDER,
       pirhd.RETAILER,
       pirhd.TARGET_MARKET,
       MESSAGE_XML,
       (SELECT pub.another_column
          FROM PI_PUBLICATION pub
         WHERE pub.obj_id = item.obj_id
           AND ROWNUM = 1)
FROM   PI_RESUBMIT_HIERARCHY_DETAIL pirhd
       INNER JOIN Pi_Item Item
          ON     item.GTIN = pirhd.GTIN
             AND item.INFORMATION_PROVIDER = pirhd.INFORMATION_PROVIDER
             AND item.TARGET_MARKET = pirhd.TARGET_MARKET
             AND item.RETAILER = pirhd.RETAILER
       INNER JOIN PI_PUBLICATION pub ON item.obj_id = PUB.OBJ_ID
WHERE  RESUBMIT_ITEM_DETAIL_ID = '88956457'
ORDER BY pirhd.GTIN

This really depends on your data and requirements and only you can answer that.


How to list LOGIN + ROLE in SQL Server 2008?

No, don't use sys.syslogins - this is a deprecated compatibility view that is only there so people have time to change their existing code based on SQL Server 2000. Also, I'm completely unclear how you think a CASE expression will help you. You need to join to sys.server_role_members to find out which logins belong to which roles, and then back to sys.server_principals again to get the name of each role.

SELECT p.name AS Login_Name, p.[type_desc] AS Account_Type,r.name AS [Role]
FROM sys.server_principals AS p
LEFT OUTER JOIN sys.server_role_members AS rm
ON p.principal_id = rm.member_principal_id
LEFT OUTER JOIN sys.server_principals AS r
ON rm.role_principal_id = r.principal_id
AND r.[type] = 'R'
WHERE p.[type] IN ('U', 'S', 'G')
and p.name not like '%##%'
ORDER BY p.name, p.[type_desc];

(I'm not sure what value you expect from the second ORDER BY column here.)

If you want only a single row per login:

SELECT p.name AS Login_Name, p.[type_desc] AS Account_Type,
  Roles = STUFF((SELECT N',' + r.name
    FROM sys.server_role_members AS rm
    INNER JOIN sys.server_principals AS r
    ON rm.role_principal_id = r.principal_id
    WHERE rm.member_principal_id = p.principal_id
    ORDER BY r.name
    FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,N'')
FROM sys.server_principals AS p
WHERE p.[type] IN ('U', 'S', 'G')
and p.name not like '%##%'
ORDER BY p.name;


big db for managing user files and settings

The first path is certainly the more conventional of the two, absent a compelling reason to break the system down into multiple schemas.  Something along these lines:

CREATE TABLE user (
  user_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_name VARCHAR(50) NOT NULL,
  password VARCHAR(32) NOT NULL,
  status_code INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE user_file (
  file_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL ,
  user_id INT NOT NULL,
  path VARCHAR(2000) NOT NULL,
  INDEX (user_id),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;

CREATE TABLE user_settings (
  user_id INT PRIMARY KEY NOT NULL,
  email VARCHAR(150),
  color VARCHAR(10),
  etc VARCHAR(255),
  CONSTRAINT FOREIGN KEY (user_id)
    REFERENCES user(user_id)
) ENGINE=InnoDB;


How can I setup users/groups in PostgreSQL so that each user has privileges on objects created by other users in the same group?

Assuming you already have

GRANT employees TO john;

Just do

ALTER USER john SET ROLE TO employees;

Now when john connects he will automatically have role employees and when he creates an object it will be owned by employees;


If a heap-organized table has no indexes, then the database must perform a full table scan to find a value.

Following link provides information on indexes in detail
http://docs.oracle.com/cd/E11882_01/server.112/e25789/indexiot.htm#CNCPT721
here you will find details of various kinds of indexes Oracle supports

If you have query like

Find a,b,c where emp_name = 'Adam Smith'

In case you have an index on column emp_name than instead of searching the entire table the data can be fetched directly. In case the column is not unique multiple rows that have emp_name Adam Smith will be fetched.

On large tables Indexes can improve the performance of queries even 100 times.

Performance improvement depends on the selectivity. In million values in a column if 100% are unique you get maximum selectivity and performance improvement is maximum, but if you have only 2 different values of a column say like 0 or 1 or yes or no you may get some improvement or no improvement or even degradation.

There are different types of Indexes like B-Tree, Bitmap, Bitmap join etc.
Most commonly used indexes are B-Tree.

B-tree is a tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time. Indexes are stored as B-Tree on disk

You can find details of B-Tree indexes on the following link
http://en.wikipedia.org/wiki/B_tree_indexing


Why does join syntax with stacked on clauses work?

The ON clause location determines join order. You can do this with parentheses, which makes it much clearer:

SELECT  *
FROM    MainTable P
        INNER JOIN
          ( SubTable L
            INNER JOIN
            SubTableType LT
            ON  L.Type = LT.Type
          )
        ON  P.ID = L.ID

You can now just omit the parentheses, and you come up with your second query which forces L JOIN LT to be performed first, followed by the join to P.

The error you are getting is because your 3rd query doesn't preserve the Chiastic Order. Look at it with the parentheses, and it will become clear:

SELECT  *
FROM    MainTable P
        INNER JOIN
        ( SubTable L
          INNER JOIN
           SubTableType LT
           ON  P.ID = L.ID --<<< P is not a valid identifier here
         )
        ON  L.Type = LT.Type

My recommendation - if you need to force join order, always use the parentheses, and proper indentation of the code, which will make this immediately visible and easy to understand.

Since you are only using INNER JOINs, order doesn't matter, and the engine is free to choose to perform the join in any order it sees fit. When OUTER JOINs are involved, the order does matter, so you need to be able to force which tables you want joined first.


You have two options:

1) The safest is to use on your driver the "secondary" option as described on the below article:

http://docs.mongodb.org/manual/reference/read-preference/

2) Another option is to tag your secondaries as described below and target your queries against the tag, most drivers offer that option.

Disadvantage is that you have to ensure that these nodes can't become primaries else you will target primaries. Advantage is that you can target certain operations to certain nodes.

http://docs.mongodb.org/manual/tutorial/configure-replica-set-tag-sets/#add-tag-sets-to-a-replica-set

I would say to stick with first option if you just starting using mongo.


Delete records from one table using join with another table?

Remove the star! The syntax (see the DELETE page at MSDN), can be either:

DELETE
FROM Employees
...

or

DELETE Employees
FROM Employees
...

or

DELETE e
FROM Employees AS e
...

You probably don't need the stars in the '*sales*' string either. If you mean to find the rows with 'sales' string anywhere in that column, the wild card is % (see the LIKE page):

DELETE e
FROM dbo.Employees AS e
WHERE e.DeptNo IN
  (SELECT d.DeptNo
  FROM dbo.Departments AS d
  WHERE d.DeptName LIKE '%sales%') ;

I removed the LCase() as well. That is an Access function, not an SQL-Server one (thnx @Aaron). Add back (the equivalent LOWER()) if your database (or table) uses a case sensitive collation (the default is case insensitive so usually there is no need for such functions.)


How to order rows randomly but keep groups together?

You only need a random order for the GroupID values, so first get these distinct values and a random number for each, in a derived table:

SELECT t.*
FROM tablename AS t
  JOIN
    ( SELECT GroupID,
             NEWID() AS rnd   -- or another random function
      FROM tablename
      GROUP BY GroupID
    ) AS r
    ON r.GroupID = t.GroupID
ORDER BY
    r.rnd,
    t.SomeValue ;

If you already have a Groups table which holds the GroupID values, you can replace that FROM tablename GROUP BY GroupID with FROM Groups.


Copying 500 GB SQL Server database from Production to Dev instance

There are two options you can do that will not cause any downtime to your production environment:

1) If you only want to grab the database structure itself (you don't need the data) you can simply script out the production database. Then simply run that on your dev server to create the database.

2) If you need the database and the data simply grab the latest backup of that database and restore it to your dev server. You will find that this is the most common method used to keep the development servers matched up to production data.


MySQL replication problem: slave has become out of sync

A bit more information would be useful - did you update a single table, or multiple tables? Was it a structure change or a pure data change?

It may be possible to get the original table from the master, dump it and restore to a new table on the slave, rebuild the data on the slave by matching the records in the slave data to that of the master table and updating data fields as necessary using some update queries with joins between the two tables on the primary key.

Otherwise, can you go back to a recent backup and restore that to the slave, including the master and slave locations for that backup, so that you can then play-forward through the relay / master logs on the slave in order to catch up with the master??


Official PostgreSQL Capitalization conventions

I'm going to basically mirror Verace's comments and state this, making it semi-official:

There is no one best practice that will cover every circumstance. What follows makes the following assumptions (and what to do if you haven't done this):

You have already discussed this with your team (people working by themselves often just have to make up their mind)
There is no formalized style definition for SQL already on your team (Otherwise you wouldn't be asking us)
There is no formalized style definition for any code (Follow the same basic conventions already established for other languages and formalize a style)

So the rest of this is somewhat opinionated but based on experience

When it comes to table names

You should go for singular entity names (this makes documentation easier)
You should use Pascal Case here

When it comes to field names

Use camelCase on your field names
Use short singular names unless the definition definitely makes sense as a plural (it almost never does)

When it comes to your own function or stored procedure names

Use underscore_separation
Use field naming for the parameterization

When it comes to built in database functions or language names (eg SELECT)

Unless there's a requirement for it to be capitalized a certain way, use ALL CAPS
Know the APIs for your language to know what's reasonable or required

When it comes to spacing

A lot of folks use column alignment for keywords and indentation for things which are not keywords
A lot of folks use commas at the beginning of the row when the fields are separated on each line (This makes it easier to comment out a specific field from a selection list)
Never use spaces as part of the names of things, not even for return value headers.

When it comes to punctuation

Parentheses - USE THEM. They're FREE. I promise.
Semicolons - USE THEM. They're not going to break you. They force you to think out your code. And they're good hygiene.
Carriage Returns - Once again, they're free ;-) And make your code readable.

You should also recognize that while I'm trying to help you apply a generic style guide, that the community for Postgres generally doesn't use camelCase or PascalCase but instead uses underscore_separation. The really important bit is to ensure that you establish and use a specific style everywhere to be consistent.


Try creating a couple of temporary tables, indexing them, analyzing them, and then join to them..

CREATE TEMPORARY TABLE luid_lookup AS
  SELECT id, find_user(id) AS fuid FROM legacy_table;

CREATE TEMPORARY TABLE eoid_lookup AS
  SELECT external_object_id, find_external_object(external_object_id) AS feid FROM legacy_table;

CREATE INDEX luid_lookup_tmp_ix1 ON luid_lookup (id, fuid);
CREATE INDEX eoid_lookup_tmp_ix1 ON eoid_lookup (external_object_id, feid);

ANALYZE luid_lookup;
ANALYZE eoid_lookup;

And then

INSERT INTO interactions(
    user_id, external_object_id, origin, interaction_type,
    interaction_sub_type, published_at, origin_id, created_at, updated_at)

SELECT b.fuid, c.feid, 'facebook' as origin,
    a.interaction_type, a.interaction_sub_type, a.published_at, a.origin_id,
    a.current_date, a.current_date
from legacy_table a
join luid_lookup b on a.id = b.id
join eoid_lookup c on a.external_object_id = c.external_object_id

If the creation of the temporary tables takes a long time, then your find_user() and your find_external_object() methods need to be tuned (as opposed to the actual insert query).

One additional note .. if the id or external_object_id are NOT pkeys/unique of their tables, you will want to make sure that you're only getting unique values..  (If you don't, then you'll get duplicate rows when you join)  In that case, make sure you do a subselect and get the distinct result (example below).

CREATE TEMPORARY TABLE luid_lookup AS
  SELECT id, find_user(id) AS fuid FROM (SELECT DISTINCT id FROM legacy_table) l_tab;


list of items or menu in mysql workbench

Use a collection of primitive types appropriate to the type stored in the database. If you have more than one column from a table then use a collection of a class that you define that will hold all of the primitive types as properties of that class--if appropriate.  Bind your menu UI to the class/collection.


Why are my SQL Server 2008R2 databases not mirroring?

After all that one of the servers didn't have the SQL Server service running under the same user. I'm an idiot.


Create table from stored procedure result

If you're really committed to this type of activity then there is a lot of dynamic sql in your future.  Everything you describe is possible.  But tread very lightly as dynamic sql opens some doors that you may wish later on had stayed closed.

One of the most classic articles in the history of dynamic sql can be found here.  It's called the blessing and curse of dynamic sql.  You give that a thorough review and you should understand your options better.

http://www.sommarskog.se/dynamic_sql.html

This specific section is very pertinent to your question.

http://www.sommarskog.se/dynamic_sql.html#Cre_tbl

Note that the author identifies a key point.  If you're doing this your violating some fundamentals of relational database design.  Now, I'm all for breaking the rules, as long as you understand the tradeoff and the purpose of the rules being broken.  But, the question is valid.  What are you really trying to accomplish?


Postgres how to update first row in the child table for all parent entries

Possible question interpretation:
UPDATE workflow_task
SET task_Type = CASE WHEN workflow_task.id = first_row.id
                     THEN 'POC'
                     ELSE 'Appr'
                     END
FROM ( SELECT MIN(id) id, workflow_id
       FROM workflow_task
       GROUP BY workflow_id ) first_row
WHERE workflow_task.workflow_id = first_row.workflow_id;

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=08c60035f735ae557100dde8a7623008
workflow table not needed for this operation.


securely exposing sql server 2008 for windows authentication

The words "securely", "exposing", "outside" and "database" do not belong together.

Under no circumstance should you ever expose a SQL server to the internet.  It's a very bad idea.

So your question really should be: How do we give access to our SQL server to remote locations?

The answer: VPN.  The remote users should establish a VPN connection to your network.  From there you can give them the appropriate access to the database.


DB architecture: What if a table is part of another table

Two ways to do it:

Option 1, as you suggest, is to make a new LeadId FK column in the [Reference] table, and make it nullable. This will work, it'll be easy enough to query, but you'll have a column with lots of nulls.
Option 2 is the 3NF (third normal form) way, with a third table that links [Reference] to [Lead]. That would contain a ReferenceId FK and a LeadId FK. You would need to join to this table in queries, but you wouldn't be stuck with a lot of nulls.


How to create a multiple mysql replication

The question is a bit confusing ("into"? master; "master-server"?; "client" vs "slave"?; etc); I'll answer some possibly related things.

You can have multiple MySQL 'instances' (master or slave) on a single server (piece of hardware).  They must have separate data trees and my.cnf.  The implementation could be:

Separate Ports (not all 3306)
Separate VMs
Separate Docker instances

Those choices may involve separate or the same copy of the MySQL binary.

If by "client" you mean "web server", then you need to figure out how the "users" will hit the same or different web servers in order to get to different mysql instances.

Once you have multiple instances running, they can connect Master and Slave in the standard way -- namely that you need to know th host and port.  Ditto for Master-Master.

Are you talking about 4 "customers"?  Or 4 Slaves?  Either way, I have mostly covered both.

Setting up "Master-Master" (alias "multi-Master" replication) on a single server does not make much sense.  It does not give you much performance, HA, or any other reason to have M-M.


Can I parse a text value from a specific column in a query (MySQL)?

WITHOUT INDEXING

SELECT * FROM actor_info
WHERE film_info LIKE '%Animation%' OR film_info LIKE '%Documentary%';

or

SELECT * FROM actor_info
WHERE LOCATE('Animation',film_info)>0
OR LOCATE('Documentary',film_info)>0;

WITH INDEXING

Create a FULLTEXT index on film_info

ALTER TABLE actor_info ADD FULLTEXT (film_info);

Query using the FULLTEXT index

SELECT * FROM actor_info
WHERE MATCH (film_info) AGAINST ('+Animation*' IN BOOLEAN MODE)
UNION
SELECT * FROM actor_info
WHERE MATCH (film_info) AGAINST ('+Documentary*' IN BOOLEAN MODE);


Two diferent time mysql databases with same uniqueid autoincrement values need to be merged

INSERT INTO new_table
    (id, ...)
    SELECT id + 1000, ...
        FROM old_table;

This assumes the target table has ids no larger than 1000; adjust if needed.


I did that kind of migration and I never want to do it again. But if you really want to do it, it is feasable. Here's my recommandations:

Make sure you have the exact same version on both instance.

select SERVERPROPERTY('ProductVersion')
Migration will be way easier if the root folder is in the same path. If you can't have that, it's much more complicated and I strongly suggest taking another path. BTW I did that with SQL 2000. Now I think you can copy over the ROOT folder in one shot. Some files like FullText will be failing if the catalogs are not migrated and replication snapshot folder and etc... Take note of all the datafiles path with:

select * from master..sysaltfiles
If you can't have the user databases at the same location, no big deal. You'll attach them later.
Start the service and pray.

Now back to restoring MSDB and MODEL.
In your particular problem. Your last restore was done without recovery. The MSDB is inconsistant and can't be recovered. I suggest you restart SQL Server in Single User Mode (using -m startup parameter) then DROP MSDB and restore it again.


Transaction and Try-catch in SQL Server Job

I would rather recommend a pattern like the one from Exception Handling and Nested Transactions:

create procedure [usp_my_procedure_name]
as
begin
    set nocount on;
    declare @trancount int;
    set @trancount = @@trancount;
    begin try
        if @trancount = 0
            begin transaction
        else
            save transaction usp_my_procedure_name;

        -- Do the actual work here

lbexit:
        if @trancount = 0
            commit;
    end try
    begin catch
        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @trancount = 0
            rollback
        if @xstate = 1 and @trancount > 0
            rollback transaction usp_my_procedure_name;

        raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
    end catch
end

This pattern checks the XACT_STATE() in the catch block to guard against uncommittable transactions:

  Uncommittable Transactions and XACT_STATE
  If an error generated in a
  TRY block causes the state of the current transaction to be
  invalidated, the transaction is classified as an uncommittable
  transaction. An error that ordinarily ends a transaction outside a TRY
  block causes a transaction to enter an uncommittable state when the
  error occurs inside a TRY block. An uncommittable transaction can only
  perform read operations or a ROLLBACK TRANSACTION. The transaction
  cannot execute any Transact-SQL statements that would generate a write
  operation or a COMMIT TRANSACTION. The XACT_STATE function returns a
  value of -1 if a transaction has been classified as an uncommittable
  transaction. When a batch finishes, the Database Engine rolls back any
  active uncommittable transactions. If no error message was sent when
  the transaction entered an uncommittable state, when the batch
  finishes, an error message will be sent to the client application.
  This indicates that an uncommittable transaction was detected and
  rolled back.

Your code is checking for @@TRANCOUNT in places where it cannot be 0, it uses a mixture of informational PRINT messages and SELECT result sets for communicating success, it does not handle errors that are recoverable. Ideally the exceptions should propagate to the client, in this case to the Agent job (ie.  your catch should re-raise).


Just run pg_dump.  The documentation says:

  pg_dump is a utility for backing up a PostgreSQL database. It makes
  consistent backups even if the database is being used concurrently.
  pg_dump does not block other users accessing the database (readers
  or writers).

In turn, the state of the database that you see on the slave is also consistent.  If there is a considerable delay in replication, the result may be slightly different than a dump from the master.


NOT NULL if value is true in another column

This is a rather simple CHECK constraint:

CREATE TABLE my_table
(
id serial,
attribute boolean,
number integer,
CONSTRAINT if_attribute_then_number_is_not_null
   CHECK ( (NOT attribute) OR (number IS NOT NULL) )
) ;

The logic behind the code is that the logical restriction if a then b is written in boolean logic as (not a) or (b). May seem counter-intuitive at first look but if you write the possible boolean combinations it works out.

The constraint could also be written as CHECK ( NOT (attribute AND number IS NULL) ) which may seem a bit more explanatory ("do not allow attribute to be false and number to be null at the same time"). Pick whatever is more readable to you.


How is it possible we have rows in the database that violate a unique constraint?

My thought is that the constraints were disabled during the load. This allows for faster insert of data but can cause issues like this.

If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

For big tables, this saves a lot of disk I/O because InnoDB can use its change buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys.


Usually FULLTEXT searches need more than a single letter, there is actually a setting that prevents them from being used if less than X number of characters are used.

  ft_min_word_length: The minimum length of the word to be included in a
  FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing
  this variable

My guess is that you are not using your index when you search for a single character like that.


The problem is that you make several wrong assumptions. Below you will find some corrections

MongoDB is not optimized for small resources

MongoDB was specifically designed to take a lot of data (recording clickstreams were the first application, iirc). As far as I can read, you have your django app on the same server as MongoDB. The problem here is that a lot of users for your django app would translate in a lot of queries/aggregations/write operations done on the MongoDB side. So django and MongoDB would have a race for resources especially during high load times. Since django is the first in the stack, it will almost always "win", for example requesting RAM which MongoDB now can not request. So it might well happen that MongoDB refuses something because of the lack of resources, the request is cancelled and your system appears to do nothing while really the two parts of your application did their best to answer the request, but failed to do so for the lack of resources.

To be honest: Running MongoDB on an 1GB instance alone would imho be not reasonable. Let alone with a django application. Imho, with this setup, you should at the very least have 4GB of RAM, and it might work until you put real load on it. For comparison: I usually suggest between 32 and 128GB of RAM per node (depending on the data, indices and a few other factors) for machines using SSDs as storage technology. Mind you, that is for MongoDB only – at an according scale of data, of course.

"Working set" does not (only) mean cache

  Disclaimer: brutally simplified and terminology might be off

MMAPv1 uses memory mapped files. All the details put aside, this means that a file is treated as an addressable range of memory. So if MongoDB wants to read a certain doc, it uses a memory address and a range it wants to read. That memory address might either be already in RAM or it has to be read from disk. Or, and here is the misconception, from the OSes filesystem cache, which – you guessed it – resides in RAM, though just a different part. (Iirc, what happens in this situation is that the address a pointer refers to is changed). So, not only would MongoDB have the working set in RAM, but it would be the direct cause of quite some part of the filesystem cache. So, we have another part of MongoDB requiring even more RAM than the working set only.

The working set is not the only thing consuming memory

Actually, the way journaling works, it doubles the RAM required by MongoDB.
Each connection (and remember each driver basically opens a connection pool) gets 1MB of RAM allocated.
Operations need some memory. Lets take aggregations as an example. They are capped to 100MB memory consumption – that alone would be 10% of your RAM, 5% of your allocatable memory.

However since you use MMAPv1: Do NOT turn off journaling! It is vital for crash recovery in MMAPv1.

Conclusion

Your machine is vastly underprovisioned in terms of RAM. Even if you have a tight budget, I can not stress the need of putting more RAM into that machine enough. I'd at least put 4GB into that machine (physical, that is, not swap) and see how it goes.

Be aware though, that with this setup, you'll always have django and MongoDB compete for resources the most when you need it the least: when your application has comparatively many concurrent users.


Microsoft access to Sql Server Data Migration

I would use the SQL Server import wizard, but make sure to use staging tables to import the data from Access, so you can perform data cleansing before inserting on the definitive tables.

A staging table is a temporary table used to load the data "as is" in the destination system (SQL Server in your case) so you can make sure the datatypes, collation, any custom format is according to your needs. This is made to avoid key/constraint violations, errors due to incompatible datypes, etc...

So for this example, you can import your access tables and ask the wizard to create the new tables during the import, after your are ok with your data, you can insert them in your definitive tables.


Trying to query AD on a linked server error message is 7321

At a minimum, in the outer query you want SamAccountName which you are using within the openquery as a filter but do not have in your select list there.

Whether that's the root issue or just a byproduct of you doctoring this up for the site remains to be seen.

Select Name, EmployeeID, Mail, SamAccountName
FROM OPENQUERY(ADSI, 'SELECT Name, EmployeeID, Mail, SamAccountName
FROM ''LDAP://DC=mydomain,DC=com''
WHERE  objectClass=''user'' AND EmployeeID > 0 and SamAccountName = ''f.bower''')


In Microsoft SQL Server's linked server configuration, what does the "RPC" setting do?

One allows A=>B the other allows B=>A.  In other words just because server A can call a proc on B doesn't mean B can call a proc on A.

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

RPC
   Enables RPC from the specified server.

RPC Out
   Enables RPC to the specified server.


How do I know that I am connected to a config server

I found one way to do it.

On config server:

> db.serverCmdLineOpts().parsed.sharding.clusterRole
configsvr

On shard server:

> db.serverCmdLineOpts().parsed.sharding.clusterRole
shardsvr

Have to be careful as db.serverCmdLineOpts().parsed.sharding.clusterRole is not always defined.


Inner procedure is rolled back but outer procedure is still committed

The problem you're facing is down to how the TRY...CATCH construct handles errors - it consumes them, eats them, and spits nothing back out except for a 0 return code. This is evidenced by a very innocuous line in the docs on TRY...CATCH that says

  Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.

This line however appears to be out of date and doesn't give any information as to the THROW statement.

To resolve this problem, you can use the throw statement to re-throw the offending error after your rollback event, as seen in this fiddle (link) or in the abbreviated sample below:

BEGIN TRY
    BEGIN TRANSACTION

    --Your code goes here

    COMMIT TRANSACTION
END TRY
BEGIN CATCH

    --Some other error handling code here

    ROLLBACK TRANSACTION;

    THROW;

END CATCH

The key is knowing what the CATCH block is doing, given the fact that when an error is raised in the TRY block, it is consumed (IE not passed back to the caller) allowing you to handle the error with your own code.

In your case, the CATCH block simply performs a rollback, meaning the error is never raised back to the calling procedure - adding THROW resolves that by manually throwing the error back to the caller (in your case the outer procedure)

If you don't want to use throw, you can also use RAISERROR for some more granular control over the error that is raised.


Best practice for other Table Data in an Order

It would be helpful to see your actual SQL scripts; an English description can be ambiguous.  It sounds like you have all the fields from your Address, Customer, and Product tables in your Orders table, to capture a snapshot of the data as it was when the order was placed.  Is that right?  If so, that's a valid approach, but it will take up a lot of space.  Space is cheap, but performance is not: keep your tables lean.

First, do you not separate Orders and OrderDetails?  Or is that what OrderProduct is meant to do?  You should only need customer and address information in the header table.  Your detail table would then typically be at a level of specific line items, with quantities and prices.  In some cases, a logical order might be shipped to multiple locations, or at separate times, and you'd need Address, DateShipped, DateReceived, DateReturned, etc., at the line item level.

Second, changing values for customers and addresses.  It's good that you're giving this some thought.  The topic you need to read up on is "slowly-changing dimensions:" today, customer #123, Mr. Smith, lives on 101 Main Street, next year she's Ms. Smith and she lives on 1st Avenue, but she's still customer #123.  There are many approaches to handling this problem.  Typically, rather than recording this information in the Orders table, you'd record each version of the customer as a record in the Customers table, with a persistent customer number (123) and a version number (1 for Main Street, 2 for 1st Avenue), something like this:

 CustomerSK CustomerID VersionID FirstName LastName Address DateEffective DateExpired IsCurrent
 ---------- ---------- --------- --------- -------- ------- ------------- ------------ ---------
 42942      123        1         Robert    Smith    10001   2018-12-07    2019-12-22  0
 42943      123        2         Roberta   Smith    10002   2019-12-23    NULL        1

 OrderID CustomerSK DatePlaced
 ------- ---------- ----------
 103101  42942      2018-07-19
 103102  42943      2019-12-24

You may have a calculated field which concatenates customer ID and version ID to make 123001 and 123002, or create a new auto-increment field for the table, with no relation to the customer IDs.  This is known as a "synthetic key", and is labeled CustomerSK in the sample above.

Your Orders table would either have two fields, CustomerID and VersionID, or a single CustomerSK field.  You use this field, or pair of fields, to join to the Customers table and get the name and address as of when the order was placed.  You could join to Customers again, on C1.CustomerID = C2.CustomerID AND C2.IsCurrent = 1, to get the customer's name and address right now.

IsCurrent can be a calculated field on DateExpires IS NULL.  Alternatively, you may prefer a far future value like 2100-12-31 for customer records which have not yet expired.  Be sure to have a unique constraint on { CustomerID, VersionID } and on { CustomerID } WHERE IsCurrent = 1.

In a real example, you'd have multiple addresses for each customer.  The same principle holds: when an address changes, record the new value, mark the old value as expired, and mark the new value as current.

Third, concatenating a text prefix to a numeric code is not going to be ideal for IDs.  You should distinguish between people-friendly IDs and database IDs.  The former may be alphanumeric, to help people recognize them, but data integrity and joins should be based on the latter, for best performance.  It's easy enough to display a prefix before the numeric ID, if necessary, in GUIs.  Consider dates: you always store them using a DATE data type, never as a number or text, but you can format them however the user likes when displaying data.

I'm not sure what distinction you're drawing between customers and people.  Pedantically, it may seem strange to put prospects in a Customers table if they've never actually ordered anything, so you may prefer to call it People or Contacts, but what information would you store for a customer that you wouldn't for anyone else?  LastOrderID or DateLastOrder, perhaps?  Those may be sparse columns in a table which includes prospective customers, but NULLs don't take up much space.


Yes , it should work considering 3rd node is part of same windows failover cluster.
I believe with 2 nodes you are trying to achieve HA and if 3rd node is in different data center it's ideal for DR.
.
Also I am not exactly sure when you say taking DC down, if both nodes down, then test the case for quorum as I guess you might be on windows 2012 and things are tricky there for odd/even majority of votes


For a 1TB DB ... Is weekly rebuild overkill?

I found it hard to believe that your OLTP system goes over the course of a single week and reshuffles 1TB of data so bad as to require a rebuild. Very few use cases would ever call for something so dramatic. Unless you have an explanation why do you need such high frequency I will say no, one week is way way too often.

  are the rule of thumbs for re-indexing frequency, % of database affected, # indexes that should be rebuilt

Yes, the same rule of thumb that applies to almost every performance related action: you measure impact. You establish a performance baseline, you measure the deviation from it over time, and you measure the impact of reindex actions. W/o basic measurements is always going to be a shot in the dark.

As for the original question: how do you reduce the amount of log generated during reindex? There are only two viable solutions:

use minimally logged operations
rebuild less, which require partitioning

As you write causes mirroring backlogs/delays it means option one is off the table as minimally logging and mirroring don't mix. That leaves only the option to rebuild less. Partition rebuild can come in handy, but only offline partition rebuild is supported. You can minimize the offline time by using fast partition switch (ie. you rebuild a copy of the data and then you switch the optimized rebuilt data).

Ultimately, for 1TB, you should never rebuild it. Your old data, which never changes, should lay at rest, compacted and archived. Current data is subject to churn and changes and you should only have to rebuild what has changed.


Using IMPORT FOREIGN SCHEMA with Greenplum database

No, that cannot be done. You have to use a less ancient fork of PostgreSQL that has heard of REPEATABLE READ.

To make that work, you'd have to modify the PostgreSQL source code, but if you do you should use SERIALIZABLE only with Greenplum.


Materialized view for the last 24 hour period

A fast-refreshable materialized view cannot contain a non-deterministic function like current_timestamp.  So if you want to materialize the data from the last 24 hours in a materialized view, the materialized view would need to do a complete refresh every time.

Do you need a materialized view?  Could you maintain your own staging table and create a custom job that runs every few minutes, deletes the data that is now more than 24 hours old, and inserts the new data (either by directly querying the table or creating a trigger that writes the new data to a different table)?  That's likely a non-starter if you need to use the query rewrite functionality of a materialized view but if you just want the benefit of having a smaller table to query, custom code may be more efficient.

Or could you partition the table by day so that queries for the past 24 hours always just have to hit the two most recent partitions?


You need to have a subquery (q1) to count how many answers of every user coincide with those of user 10 (number_of_coinciding_answers). Then, have a second subquery (q2) that counts the number_of_answers.

Then compute 100.0 * number_of_coinciding_answers / number_of_answers:

Everything put together just looks like:

SELECT
    q1.user,
    100.0 * q1.number_of_coinciding_answers / q2.number_of_answers AS percent
FROM
    (SELECT
        t.user,
        count(good.answer) AS number_of_coinciding_answers
    FROM
        t
        JOIN t AS good ON good.question = t.question
                      AND good.user = 10
                      AND good.answer = t.answer
    WHERE
        t.user <> 10
    GROUP BY
        t.user
    ) AS q1
    CROSS JOIN
    (SELECT
        count(*) AS number_of_answers
    FROM
        t
    WHERE
        t.user = 10
    ) AS q2
ORDER BY
    q1.user ;

You'll get

user |  percent
---: | -------:
  11 | 50.00000
  12 | 50.00000

Note that the JOIN condition is taking care of just finding matching answers between the current user and the reference user (user 10).

Note also that the numbers retrieived are 50% and 50%, which are the correct ones, as pointed out by Rick James.

You can write it in a slightly more compact fashion (that MySQL actually seems to handle better) by using:

SELECT
    q1.user,
    100.0 * q1.number_of_coinciding_answers /
          (SELECT count(*) FROM  t WHERE  t.user = 10) AS percent
FROM
    (SELECT
        t.user,
        count(good.answer) AS number_of_coinciding_answers
    FROM
        t
        JOIN t AS good ON good.question = t.question
                      AND good.user = 10
                      AND good.answer = t.answer
    WHERE
        t.user <> 10
    GROUP BY
        t.user
    ) AS q1
ORDER BY
    q1.user ;

All these queries are helped by having a multi-column covering index on columns (user, question, answer). That is, in MySQL, you'd define your table

CREATE INDEX idx_t_uqa
    ON t (user, question, answer) ;

You can see a more step-by-step approach and all the settings at dbfiddle here


Can I split lines into Elements with For XML?

Use a split string function of your choice and split on CRLF.

select E.ID,
       (
       select S.Item as Line
       from dbo.SplitStrings(E.Body, char(13)+char(10)) as S
       for xml path(''), type
       )
from dbo.Example as E
for xml path('row');

Have a look at the blog post Split strings the right way – or the next best way by Aaron Bertrand for a couple of versions to choose from.


What is the difference between covering and overlapping constraints use in DBMS?

In reference with below Employee Entity Relational Model

Overlap Constraints : Can "Karthik" (employee) be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/Disallowed) - In this scenario the hourly employee karthik cannot be a contract employee - Disallowed - This explains the Overlap constraints.
Covering Constraints : Is every hourly employee and every contract employee an employee of this organization? Answer: Yes, all the contract/hourly employees belong to the master employees entity - This explains the Covering constraints.


Meaning of various user account privileges in phpMyAdmin

Your second question was well covered by joanolo.
I would like to address your first question only.
On Apr 17, 2014, I answered this question : How to grant super privilege to the user?. In that post, I explained how the SUPER privilege was impossible to grant to a database-specific user. I clarified the difference between global privileges and database-specific privileges by comparing the table structure of mysql.user and mysql.db.
To show the difference between them in terms of grants side-by-side, please run this query:
SELECT
REPLACE(glb,'_priv','') global_privilege,
IFNULL(REPLACE(dbs,'_priv',''),'NOT ALLOWED') database_privilege
FROM (SELECT A.column_name glb,B.column_name dbs FROM
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user'
AND column_name LIKE '%priv') A
LEFT JOIN
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='db'
AND column_name LIKE '%priv') B
ON A.column_name=B.column_name) AA;

When you run this in MySQL 5.7.12, you get the following output:
+-------------------+--------------------+
| global_privilege  | database_privilege |
+-------------------+--------------------+
| Select            | Select             |
| Insert            | Insert             |
| Update            | Update             |
| Delete            | Delete             |
| Create            | Create             |
| Drop              | Drop               |
| Grant             | Grant              |
| References        | References         |
| Index             | Index              |
| Alter             | Alter              |
| Create_tmp_table  | Create_tmp_table   |
| Lock_tables       | Lock_tables        |
| Create_view       | Create_view        |
| Show_view         | Show_view          |
| Create_routine    | Create_routine     |
| Alter_routine     | Alter_routine      |
| Execute           | Execute            |
| Event             | Event              |
| Trigger           | Trigger            |
| Reload            | NOT ALLOWED        |
| Shutdown          | NOT ALLOWED        |
| Process           | NOT ALLOWED        |
| File              | NOT ALLOWED        |
| Show_db           | NOT ALLOWED        |
| Super             | NOT ALLOWED        |
| Repl_slave        | NOT ALLOWED        |
| Repl_client       | NOT ALLOWED        |
| Create_user       | NOT ALLOWED        |
| Create_tablespace | NOT ALLOWED        |
+-------------------+--------------------+
29 rows in set (0.04 sec)

Given this display, it is impossible to grant RELOAD, SHUTDOWN, PROCESS, FILE, SHOW DATABASES, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER, and CREATE TABLESPACE to a database-specific user.
For example, you don't want a database-specific user to

Shutdown the Database
See the processlist of other users
Setup Replication on their own
Kill DB Connections
Create a New User
Create Giant Tablespace Files
Load an External File into the Database
Create External Files in the OS using SELECT ... INTO OUTFILE
and so forth

SUPPLEMENTAL INFO
There are also table-specific grants in mysql.tables_priv and column-specific grants in mysql.columns_priv. These grants are stored in ENUM columns.
Just run DESC mysql.tables_priv; and DESC mysql.columns_priv; and see.


Mysql - Load data infile query for Particular Values

IMHO there is no a perfect solution for such scenario but you can utilize IGNORE clause.

For example if you were to have an auto_increment id column in your table and you already have a row in it with a known id then your query to import only rows for february might look like this

LOAD DATA local INFILE  '/path/to/your/file.txt' IGNORE
INTO TABLE slsdata
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(@sldate)
SET id = IF(MID(@sldate, 6, 2) = '02', NULL, 1), sldate = @sldate

What it does it reads values of sldate field in @slfield variable then in SET clause it checks whether it's february and if it is then assigns NULL to id column and if not then assigned predefined existing value 1 violating a UNIQUE constraint and IGNORE clause does exactly that it skips a row in such a case.

That being said I'd recommend another approach:

create a temporary staging table without any constraints
load whole file in it
do all necessary cleansing, validating against ref tables, transformations etc. on required subset of data (e.g. for particular month) utilizing all SQL capabilities
insert required subset of data from staging table into your factual table
truncate or drop staging table


Mysql update table from another table with condition

you should join the tables, so  that all rows are joint together.
Before you start this use a select with the inner join to see if it fits.
UPDATE wp_postmeta_temp wpmt
INNER JOIN wp_postmeta_import wpi ON wpi.post_id=wpmt.post_id AND wpmt.meta_key = '_variation_description'
   SET wpmt.meta_value= wpi.meta_value


Referring to DBCC UPDATEUSAGE (Transact-SQL):

Update the stats - sysviews are out of sync.

It seems the system views are not always accurate, and sp_spaceused is notorious for this. A form of caching is applied to non-critical, but heavy process data collection functions - like measuring partitions (like what you just created another of) and allocations.

Examining sp_spaceused (Transact-SQL), we see this:

  When updateusage is specified, the SQL Server Database Engine scans the data pages in the database and makes any required corrections to the sys.allocation_units and sys.partitions catalog views regarding the storage space used by each table. There are some situations, for example, after an index is dropped, when the space information for the table may not be current. updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. If preferred, DBCC UPDATEUSAGE can be run separately.

Try running:

EXEC sp_spaceused @updateusage = N'TRUE';


Column doesn't exist when using CASE expression in WHERE clause

The WHERE clause is evaluated before aliases in the SELECT clause. WHERE is used to locate rows from the base table which are the input to all expressions in the SELECT. Only after locating the rows your CASE can be evaluated with real values and the final_price alias is assigned its value.

There are multiple ways to solve that:

repeat the same expression in the WHERE clause - that way it will be used to locate the rows
wrap your query as a subquery in another query and move the WHERE to the outer query - as you select from a derived table, the alias becomes a "real" column that way
(this would work in MySQL, but not in PostgreSQL) use the HAVING clause instead of WHERE - HAVING (in MySQL) works after the SELECT is computed to do a "second wave" of filtering. In standard SQL (and in Postgres), SELECT is logically processed after HAVING, so this won't work.

create a generated/virtual column from your CASE expression, that way it becomes a part of your base table and you might even be able to index it

Warning: your query will need to do fulltable/index scan unless you can add some "simpler" WHERE conditions or be able to use the indexed virtual column.