If I get it right, you can use window functions:

select event
from (
    select ed.event, ed.start_date
         , row_number() over (partition by ed.event order by ed.start_date) as rn
    from public.eventdate
) as x
where rn = 1
order by start_date
limit 5

In the inner select, the events are given an ordering number per event. In the outer select, the oldest one per event is chosen and the result is ordered by this date.

If this is not what you meant, you can change your query so that the order by becomes deterministic, by adding an aggregate function (I used MIN here):

SELECT ed."event"
FROM public."eventdate" as ed
GROUP BY ed."event"
ORDER BY MIN(ed."start_date") ASC
LIMIT 5
OFFSET 0


dynamically create trigger to audit row changes

SQL Server supports DDL triggers. This would fire when a table is created, for example.

Constructing the table trigger code dynamically in the DDL trigger would, I believe, achieve the desired outcome.


Create a row based on a condition (column value)

You can do this with the below query:

DECLARE @Employees TABLE
(
    EmpNo INT,
    GivenName NVARCHAR(255),
    PreferredName NVARCHAR(255)
)

INSERT INTO  @Employees
VALUES (1, 'Paris', 'Paris'), (2, 'Ashley', 'Ash')

SELECT EmpNo,
    GivenName,
    PreferredName
FROM @Employees
WHERE GivenName = PreferredName
UNION ALL
SELECT EmpNo,
    GivenName,
    NULL AS PreferredName
FROM @Employees
WHERE GivenName <> PreferredName
UNION ALL
SELECT EmpNo,
    NULL AS GivenName,
    PreferredName
FROM @Employees
WHERE GivenName <> PreferredName


Logging Stored Procedure Parameter Values

Yes, we did this type of thing at a company I worked at a few years ago. Even though we did it only for error logging, it was the same general concept. You just need to write the SELECT statement that includes all of the input parameters, use FOR XML, and store that in a variable that can be inserted into your Execution table in a new field: Parameters XML NULL. And then you can scrap the ExecutionParameter table.

Try the following as it shows three ways of generating the XML: Attribute-based, Element-based, and Element-based including NULL elements. The default behavior of FOR XML is to indicate NULL values by not including the attribute or element. But if you are doing Element-based XML and want the parameters to show even if they are NULL, then you need to specify XSINIL in the FOR XML clause. The ability to include an indicator for NULL is not available in Attribute-based XML.

Please note that there is not a huge difference between Attributed-based XML and the default Element-based XML (default = does not include NULL items) in terms of storage size (as shown in the example below). When viewing the XML it does appear that there is a lot more "bloat" with Element-based, and that would be the case if storing the data in an NVARCHAR field or text file. But the XML datatype uses an optimized method of storing the data internally, in a way that cannot be seen, but is clearly indicated in the resulting DATALENGTH output.

There is, however, a slight increase in size when using the XSINIL option for Element-based XML and it needs to keep track of NULL elements which are excluded from the other two types. The increase in size is due to a one-time hit for declaring the xsi namespace in the root element, and then a per-NULL element hit.

DECLARE @Param1 INT = 5,
        @Param2 DATETIME = NULL,
        @Param3 NVARCHAR(50) = N'Test < some & XML chars "',
        @Param4 DATETIME = '2016-04-09';

DECLARE @Parameters XML;

SELECT @Parameters = (
   SELECT @Param1 AS [Param1],
          @Param2 AS [Param2],
          @Param3 AS [Param3],
          @Param4 AS [Param4]
   FOR XML RAW(N'Params') -- Attribute-based XML (NULL attributes are missing)
);

SELECT @Parameters, DATALENGTH(@Parameters); -- 183

SELECT @Parameters = (
   SELECT @Param1 AS [Param1],
          @Param2 AS [Param2],
          @Param3 AS [Param3],
          @Param4 AS [Param4]
   FOR XML RAW(N'Params'), ELEMENTS -- Element-based XML (NULL elements are missing)
);

SELECT @Parameters, DATALENGTH(@Parameters); -- 185

SELECT @Parameters = (
   SELECT @Param1 AS [Param1],
          @Param2 AS [Param2],
          @Param3 AS [Param3],
          @Param4 AS [Param4]
   FOR XML PATH(N'Params'), ELEMENTS XSINIL -- Element-based XML (NULL elements included)
);

SELECT @Parameters, DATALENGTH(@Parameters); -- 434

Returned XML values:

<!-- Attribute-based -->
<Params Param1="5" Param3="Test &lt; some &amp; XML chars &quot;"
 Param4="2016-04-09T00:00:00" />

<!-- Element-based -->
<Params>
  <Param1>5</Param1>
  <Param3>Test &lt; some &amp; XML chars "</Param3>
  <Param4>2016-04-09T00:00:00</Param4>
</Params>

<!-- Element-based including NULL elements -->
<Params xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Param1>5</Param1>
  <Param2 xsi:nil="true" />
  <Param3>Test &lt; some &amp; XML chars "</Param3>
  <Param4>2016-04-09T00:00:00</Param4>
</Params>

Additional notes:

For SchemaName and ProcedureName columns, I would use sysname as the datatype since that is how it is defined in the system tables. And be sure to use lower-case for sysname as it is an alias that lives in master and servers with a default collation that is binary or case-sensitive won't be able to find that alias if it is not all lower-case. Your current server might be case-insensitive, but it is still a good habit to get into when using sysname.
For the ExecutionError table, be sure to include INT columns to capture: ERROR_LINE(), ERROR_NUMBER(), ERROR_STATE(), and ERROR_SEVERITY().
No, you don't want to use SQL_VARIANT as that prevents you from storing the LOB types: VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), and XML.
No, you can't use NVARCHAR(8000) because NVARCHAR has a top size of 4000, unless you use MAX.


mongoDB : Restoring space after db.repairDatabase

I am bit confused about the size before and after the repairDatabase. I was thinking space should get release. I haven't tried compact option

In general you should consider the  repairDatabase  command a last resort to be used when you actually need to salvage or repair a database and don't have a better data source to copy from (eg. a copy of the data from another replica set member of backup). Historically the repair command was often (ab)used to forcibly rebuild databases using the older MMAP storage engine, but this is no longer recommended practice. The repair operation obtains a global write lock and will block all other operations on your MongoDB server until complete.

With MongoDB 3.2.3+ using WiredTiger (the default storage engine), you can use the compact command to release unused disk space to the operating system for a specific collection. Typically this isn't required unless you find space is not being reused effectively over time.

NOTE: while compacting a collection other operations on the same database will be blocked so this is an activity you would only want to perform during scheduled maintainance periods. Depending on the layout of data on disk, compaction may also not result in significant storage space savings. A better operational approach to reclaim all unused space while avoiding downtime would be to resync a mongod as a member of a replica set.

A few points that should help clarify your observations on size numbers with WiredTiger:

Data in WiredTiger is compressed by default and the representation on disk is different from the representation in memory.
There are several potentially interesting measures of size including data size, storageSize, totalIndexSize, and totalSize (which adds storageSize + totalIndexSize). You can inspect these metrics (and more) via db.collection.stats().
Data written to files on disk is grouped into block allocations (in multiples of 4KB) which is why your single document insertion resulted in a 4KB storageSize and 8KB totalSize (the extra 4KB is the initial allocation for the _id index file). Block allocations are variable sized and managed by the WiredTiger storage engine.
The WiredTiger storage engine does not perform in-place updates on data; it uses an MVCC (MultiVersion Concurrency Control) approach with Snapshots and Checkpoints. This can lead to some apparently contradictory observations where storage space may need to grow to accommodate multiple document versions even though the data size is not increasing. Allocations from older document versions will be marked available for reuse as checkpoints complete, and WiredTiger will try to minimize storage fragmentation with a best-fit allocation from existing storage where possible.

  If I observe the numbers produced after above operations, I was under assumption that size should go back to empty, though show dbs shows mc as 0.000GB.

The show dbs command indicates the actual data size (eg. db.mc.stats().size), which may be much larger than the storageSize (due to compression) or smaller than storageSize (due to preallocated/unused space). Your example is the latter: there is storage space available for reuse.
When you deleted documents you used db.collection.remove({}). This syntax deletes matching documents without removing collection metadata (eg. index definitions) or compacting storage space. If you want to more immediately drop an entire collection and free storage space you should instead use db.collection.drop().

For more information, see:

WiredTiger Storage Engine in the MongoDB documentation
A Technical Introduction to WiredTiger presentation from MongoDB World 2015.


How hard would it be to have PostgreSQL run a test to adjust the cost parameters?

It the costs were constant there might be a marginal gain.  The units are necessarily artificial as the various resources are not interchangeable.  Even on the same system the costs can vary at different times.

If the CPU becomes fully loaded, then CPU cycles become more expensive.
On a multi-core system CPU cost may be less than observed by testing one CPU.
If the disks become busy the cost of a disk access increases.
Systems may have different disks with different performance (read cost).  Cost will vary depending on which disk or disk subsystem the data is on.
Different distribution of data across disks may vary the performance.
Different placements of indexes and data may vary the performance.
Placement of data across disks of different performance will result in disk I/O having multiple costs depending on which data is being accessed.
Different types of queries (batch vs OLTP) may have different appropriate cost distributions.

Once the database is created, it may be possible to determine better metrics from the database.  Use of the updated metrics may change loads in ways than make them invalid.  There are margins beyond which behavior becomes relatively un-predicable and likely to seriously degrade.


The client need 'SUPER' privileges to the reports DB in order to create and maintain my Functions

To modify functions and stored procedures, you need the ALTER ROUTINE privilege. If binary logging is enabled, you may also require the SUPER privilege.


History table implementation: "Tuple-versioning" vs Effective Date

Tuple versioning

Will require two updates for each new piece of data - one to set the end date of the old row & one to insert the new row.
The index(es) most likely will contain the interval dates making them wider and marginally less efficient.
Historical queries (AS OF < date >) are simpler.
DELETE can be a logical delete without further code.
The end of the live row must be marked with a "magic" value, typically 9999-12-31, or NULL. The latter will complicate code. The former often morphs into several magic values which become difficult to handle.

Effective date

Will require a MAX(), TOP(1) or similar functionality, on every single read.
Adding a single DATE column to indexes will cause a tiny less bit of bloat.
Logical DELETE is not possible without adding a place-holder "has expired" row to the table.

For both

All writes are INSERTS, never UPDATES.
Primary keys expand to include the date, with implications for referencing tables.
Best to use UTC for the effective dates to avoid time zone and DST issues.

I've tried both. Tuple versioning is easier to support overall.

It is relatively simple to reproduce the functionality of SQL Server's temporal tables by using triggers. Having a separate history table eliminates a lot of the complexities arising from having history and live values co-mingled in one table.


Can geography datatype Lat/Long properties be returned using a view in SQL Server 2008?

Don't reference it as [dbo].[vendor].[location].Lat in the SELECT list.

use simply vendor.location.Lat or location.Lat or define a table alias and use that.

CREATE TABLE dbo.Vendor([location] geography)

GO

/*Works fine*/
CREATE VIEW V1
AS
SELECT
   v.location.Lat AS latitude
FROM
    dbo.vendor v

GO

/*Fails*/
CREATE VIEW V2
AS
SELECT
   dbo.vendor.location.Lat AS latitude
FROM
    dbo.vendor

The use of the column dbo.vendor.location on its own (without the CLR property call) does in fact still work in the column list but the use of "More than two-part column name" is on the list of deprecated TSQL features anyway.


T-SQL How does CHANGETABLE Have a Parameter with the Literal Table Name

This is just special syntax, nothing more. There should be a hint for you in the fact that you are not passing just the table name but also need to put a keyword, either VERSION or CHANGES, before it. However, even without a keyword it would still be just a syntactic issue.

That is because CHANGETABLES is a built-in Transact-SQL function, part of the language. Of course, one might argue that user-defined functions are, in a way, extensions to the language. Even so, there is pre-defined syntax for such extensions and it does not include all the variety of built-in functions' syntax. For instance, you still cannot put an OVER clause after a UDF, as in window functions, or use AS as a delimiter between arguments, as in the CAST function, or enforce the argument to be a single string literal rather than an expression, as in OPENQUERY or OPENROWSET. In short, built-in functions' syntax cannot serve as an example of what you can do in user-defined functions.

The takeaway of the above is, names still cannot be parameterised in Transact-SQL without resorting to dynamic SQL.


MYSQL - Select statement ordered by count of matching rows from another table

as variant:

SET @ID = 1;
SELECT
    ID,
    count(*) as count_matched
FROM TableB
WHERE
    ID != @ID
    AND
    CAT_ID IN (SELECT CAT_ID FROM TableB WHERE ID = @ID )
GROUP BY ID
ORDER BY count(*) DESC
LIMIT 2;

add after comment:

MySQL do not support LIMIT in subqueries, so You can:

Handle returned values in application and use it for prepared statements as parameters
Use temporary tables

SET @ID = 1;
DROP TEMPORARY TABLE IF EXISTS tt;
CREATE TEMPORARY TABLE tt as
SELECT
    ID,
    count(*) as count_matched
FROM TableB
WHERE
    ID != @ID
    AND
    CAT_ID IN (SELECT CAT_ID FROM TableB WHERE ID = @ID )
GROUP BY ID
ORDER BY count(*) DESC
LIMIT 2;
SELECT
    TableA.* FROM TableA WHERE     ID IN (SELECT ID FROM tt);

temporary table is session, so You could use it with same name independent for each session.

But if handle returned values in application - prevent table locks, because CREATE TABLE AS SELECT (and INSERT SELECT) - require lock table, with external code - it just 2 independent selects.


Could not allocate space for object in database because the Filegroup is full

I'm looking for ways to avoid it in the future...

Don't have just a single file in the filegroup that has any free space. Set your files to the same size, grow them at the same time to the same sizes, and let the round robin with proportional fill algorithm do its' thing.

  But, the auto-growth restricted files in filegroups are restricted due to space issues on the drives they reside. So enabling auto-growth for them or increasing their initial size to keep same across all files would be difficult.

Then they should be moved to different drives where there is space, or moved around so that all drives have space, or the drives expanded.

This really comes down to administration. This will be an ever present issue while the data files are vastly different in free space and size. The reason comes down to proportional fill and round robin allocations.

A very quick summary of proportional fill in SQL Server is that  the file in the filegroup with the most free space should be allocated from more often than the ones with less free space. If there is a file with little to no free space, it'll be skipped. This means when you only have one file in your filegroup that really has any space, you're effectively hot-spotting all allocations there. The round robin part is just the allocations going between the files.

Since you're only keeping a single file with unbalanced amounts of free space, this will become a single hot spot for all allocations. This is why I advised the above in equaling your files as much as possible.


how to increase speed of this mysql query?

First retrieve id and the computed scores, then sort and join the result back to news:

SET @line = 0;
SET @last = 25;
SELECT B.*,titlescore,timescore,textscore FROM
(
    SELECT id,titlescore,timescore,textscore,combinedscore,(@line:=@line+1) line
    FROM
    (
        SELECT id,
            MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore`
            (
                CASE
                    WHEN time >=1432173380 THEN 21
                    WHEN time >=1432162580 THEN 18
                    WHEN time >=1432097780 THEN 15
                    WHEN time >=1431903380 THEN 11
                    WHEN time >=1431320180 THEN 7
                    ELSE 0
                END
            ) * 1.75 ) AS `timescore`,
            MATCH (`text`) AGAINST ('tax in work') * 0.65 AS `textscore`,
            MATCH (`title`) AGAINST ('tax in work') * 1.65+
            (
                CASE
                    WHEN time >=1432173380 THEN 21
                    WHEN time >=1432162580 THEN 18
                    WHEN time >=1432097780 THEN 15
                    WHEN time >=1431903380 THEN 11
                    WHEN time >=1431320180 THEN 7
                    ELSE 0
                END
            ) * 1.75 ) + MATCH (`text`) AGAINST ('tax in work') * 0.65
            AS combinedscore
        FROM news
    ) AA WHERE combinedscore >= 35
    ORDER BY combinedscore DESC
) A LEFT JOIN B news USING (id)
WHERE line <= @last;

Give it a Try !!!


Need an Audit Log. Should I create an Audit Log Table or Audit Fields in each table?

Your two scenarios serve different purposes. The former is useful when you application needs to know the history of an entity, e.g. to display the balance sheet as of last December. See also "slowly changing dimensions" and "temporal tables".

The latter is closer to the true audit trail, which should be separate from the data it reports on, and probably even from the database it reports on -- otherwise that same [compromised] application that enters erroneous data can also modify the audit trail to disguise the change. Storing audit data separately from its source entities also benefits performance -- the application doesn't need to sift through days/months/years of historical data to get at the current records.

If you decide to store the audit trail in the database, at least ensure that the authorization ID that modifies the data cannot access the audit trail table, and the audit authorization ID cannot change the source data (also known as separation of duties).


Who changed SQL server max memory configuration?

YES, there is a way to find out Who did it.

On SQL Log file viewer, on left panel select Windows NT to expand and select Application to display.

You can see details of date, User, computer, etc:

message:
Date        8/02/2014
Log     Windows NT (Application)

Source      MSSQLSERVER
Category        (2)
Event       2342
User        ???????   THIS IS WHO  "OFFENDER"
Computer    THIS IS THE HOST where he did it

Message
Configuration option 'min server memory (MB)' changed from 2048 to 512. Run the RECONFIGURE statement to install.

Good luck.


Export Wizard never starts Pre-executing

From your issue, I can see that your choice of tool is a problem.

Below are 2 options that you can use :

Backup on the source server with COMPRESSION and Restore it on the destination server. Make sure you have Instant file initialization enabled. Once Restore is done, you can delete unwanted objects (if you want).
Use BCP OUT in native mode on source server, copy the file to destination (you may zip the file as well) and BULK INSERT into destination table. Make sure to create the table on destination database. I have written this script that will help you.


Well, each log is for a different purpose.

Binary logs - Required for acting as a replication master and useful for auditing or point in time restores from backups
Relay logs - Required for acting as a replication slave. They are only stored until applied to the slave and then removed automatically
General query log - All the queries executed by the server
Slow query log - Queries that are 'slow'. Based on executing time and/or index usage
Innodb logs - These are internal to the innodb engine and not a classic log for you to look at. It stores the data post the current checkpoint and so it can be merged into sequential writes rather then random writes all over the table.
Error logs - Just stderr from the mysqld process

What are you after? Queries run or slow queries or?


Creating a new index, clustered or not, will not break existing foreign key relationships.

Be aware that creating a new unique index will of course fail if duplicate keys exist.


The AWR repository stores the snapshots you see listed in DBA_HIST_SNAPSHOT. The END_TIME is when the Snapshot data was recorded. This is the raw data about performance on your DB instance. They are usually taken every hour and retained for 7 days.

2 snapshots are required to generate a report. They don't really mean much on their own. In this case for the 2 snaps you specified you are getting a report that covers the END time of the 1st snapshot until the END time of the 2nd snapshot.

As far as the times on a generated report not being exactly at :00 I am thinking some rounding might be going on. The snapshots are triggered as close to top of the hour as possible, but it may take a few seconds to actually record the data. If you look at FLUSH_ELAPSED time in DBA_HIST_SNAPSHOT you will see that some data recording operations take a few seconds, some may take a minute or more. This is why you are seeing 10:01 instead of exactly 10:00 (the END of the 1st snap).


Using Postgres to return a selection based on frequency distribution

Get a CTE with the different sums, pick one and return a matching row:
WITH sums AS (
   SELECT DISTINCT dice_sum FROM dice_rolls
)
SELECT * FROM dice_rolls
WHERE dice_sum = (SELECT dice_sum FROM sums ORDER BY random() LIMIT 1)
ORDER BY random() LIMIT 1;


Load data from a Table that has the same structure as Table1 with the only diferrence of an ID not null column?

You could make the identity column IMPLICITLY HIDDEN.
CREATE TABLE T(I INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, A INT)

ALTER TABLE T ALTER COLUMN I SET IMPLICITLY HIDDEN

INSERT INTO T VALUES (42)

SELECT I, A FROM T

will output
I|A
-|------
1|    42


You can set this SESSION variable from the command line:

mysql> show variables like '%strict%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | OFF   |
+--------------------+-------+
1 row in set (0.01 sec)

Then, you do this:

mysql> set innodb_strict_mode = ON;
Query OK, 0 rows affected (0.00 sec)

Then, to check:

mysql> show variables like '%strict%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+

Then, on exiting and reconnecting, and rechecking the variable, it is again OFF - the default. Use SET GLOBAL innodb_strict_mode = ON if you don't want it to reset after logging off. But, setting it in the client will not make the change stick after a reboot of the server.

Edit my.cnf - I've never seen or touched mysqld.cnf.

Put the line(s) below in the [mysqld] section of my.cnf.

sql_mode    = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"
innodb_strict_mode = ON

(I recommend the sql_mode line also - otherwise, for example, certain GROUP BY queries will return (ahem...) anomalous results).

Bounce the server - and relog in.

mysql> show variables like '%strict%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

It's all in the documentation here.

  You can turn innodb_strict_mode ON or OFF on the command line when you
  start mysqld, or in the configuration file my.cnf or my.ini. You can
  also enable or disable innodb_strict_mode at runtime with the
  statement SET [GLOBAL|SESSION] innodb_strict_mode=mode, where mode is
  either ON or OFF. Changing the GLOBAL setting requires the SUPER
  privilege and affects the operation of all clients that subsequently
  connect. Any client can change the SESSION setting for
  innodb_strict_mode, and the setting affects only that client.

Maybe you don't have the SUPER privilege? Log in and run SHOW GRANTS to find out.


Below are some good ways to improve BULK INSERT operations :

Using TABLOCK as query hint.
Dropping Indexes during Bulk Load operation and then once it is completed then recreating them.
Changing the Recovery model of database to be BULK_LOGGED during the load operation.
If the target has Clustered Index then specifying ORDER BY clause in the bulk insert operation will increase the speed of BULK loading.
Using Trace Flag 610 at the beginning of BULK INSERT operation.

The max degree of parallelism should be configured on the server rather than the default. You can refer to my answer on how it configure it here.

Some good reference with interesting stats are given in The Data Loading Performance Guide here
. Have a Look at BATCHSIZE and ROWS_PER_BATCH as well in the guide.


Zoned and hashed sharding of existing, non-empty collection

For me it is working. You only need to create the index { "region": 1, "_id": "hashed" } manually.
use data
db.collection.insertMany([{ region: 'EU' }, { region: 'US' }])

sh.addShardToZone("shard_01", "Europe")
sh.addShardToZone("shard_02", "America")

sh.updateZoneKeyRange(
   "data.collection",
   { "region": "EU", "_id": MinKey },
   { "region": "EU", "_id": MaxKey },
   "Europe"
)

sh.updateZoneKeyRange(
   "data.collection",
   { "region": "US", "_id": MinKey },
   { "region": "US", "_id": MaxKey },
   "America"
)

db.collection.createIndex({ "region": 1, "_id": "hashed" })
sh.shardCollection(
   "data.collection",
   { "region": 1, "_id": "hashed" }
)

Extract from sh.status():
data.collection
  shard key: { "region" : 1, "_id" : "hashed" }
  unique: false
  balancing: true
  chunks:
          shard_01  2
          shard_02  1
          shard_03  1
          shard_04  1
  { "region" : { "$minKey" : 1 }, "_id" : { "$minKey" : 1 } } -->> { "region" : "EU", "_id" : { "$minKey" : 1 } } on : shard_04 Timestamp(3, 0)
  { "region" : "EU", "_id" : { "$minKey" : 1 } } -->> { "region" : "EU", "_id" : { "$maxKey" : 1 } } on : shard_01 Timestamp(4, 1)
  { "region" : "EU", "_id" : { "$maxKey" : 1 } } -->> { "region" : "US", "_id" : { "$minKey" : 1 } } on : shard_03 Timestamp(4, 0)
  { "region" : "US", "_id" : { "$minKey" : 1 } } -->> { "region" : "US", "_id" : { "$maxKey" : 1 } } on : shard_02 Timestamp(2, 0)
  { "region" : "US", "_id" : { "$maxKey" : 1 } } -->> { "region" : { "$maxKey" : 1 }, "_id" : { "$maxKey" : 1 } } on : shard_01 Timestamp(1, 5)
   tag: Europe  { "region" : "EU", "_id" : { "$minKey" : 1 } } -->> { "region" : "EU", "_id" : { "$maxKey" : 1 } }
   tag: America  { "region" : "US", "_id" : { "$minKey" : 1 } } -->> { "region" : "US", "_id" : { "$maxKey" : 1 } }

Please be aware of Balancing failed when moving big collection, my collection is also around 100GB, so you may face the same issue.


How to delete rows from relational database

One simple way to do this is to extract the rows from those tables into temporary tables then truncate the source tables.

Example:

-- Load the top 500 UUT_RESULT records into a temp table
SELECT TOP (500) *
INTO #UUT_RESULT_TEMP
FROM UUT_RESULT
ORDER BY ID

-- Load the related STEP_RESULT records into a separate temp table
SELECT *
INTO #STEP_RESULT_TEMP
FROM STEP_RESULT
WHERE UUT_RESULT IN (SELECT TOP (500) FROM UUT_RESULT ORDER BY ID)

-- Remove the record
TRUNCATE TABLE STEP_RESULT
TRUNCATE TABLE UUT_RESULT

-- Load the records back in
INSERT INTO UUT_RESULT
SELECT * FROM #UUT_RESULT_TEMP

INSERT INTO STEP_RESULT
SELECT * FROM #STEP_RESULT_TEMP

Using TRUNCATE will help speed things up as this is a minimally logged operation.


MySQL IDB File grows huge despite having little data

Some 30 hours now have past and the table in question (i.e. its idb file) has less disk space occupied than expected. The application that uses the table had been offline for a couple of hours, too, and hence, the table has been shrunk again. It still shows its growing behaviour once the application is coming back online again.

It appears now, that there is a purge system within mysql that does not quite keep up with usage of that table - despite the older 5.7 MySQL on much older hardware didn't have that issue.

I suppose that this realization is the answer to my original question and that I will have to tweak the innoDB purge parameters to find optimal settings, or maybe trigger Optimize Table from the Application every once in a while.


Should I install SQL Server 2012 SP3 when I'm already running SQL Server 2014?

Windows Update isn't offering you a Service Pack for SQL Server 2012 to apply to your SQL Server 2014 instance; there is something else on your machine that is triggering it. Could be dozens of things, but most commonly:

The SQL Server 2014 instance isn't the only instance on your machine. You may have SQL Express installed (on its own, or as part of Visual Studio, or as part of some other software), SQLLobalDB, or some other instance that is still at SQL Server 2012 < SP3.
You have other services like SSIS, SSRS, SSAS, etc. that are from 2012 and weren't upgraded as part of the move to 2014.
You have shared 2012 components - e.g. you upgraded your 2012 engine but you didn't upgrade the tools, or the upgrade to the tools failed, or you installed 2012 tools after the upgrade.

Here is what I suggest.

Go download the SQL Server 2012 SP3 standalone installer:

Download Service Pack 3 for Microsoft® SQL Server® 2012

When you run the installer, very early in setup, you'll get to a screen like this, which will tell you all of the components that are eligible for upgrade:

This will give you an indication of what components you need to either patch or remove.

I do not think you should ignore this. If you have other instances on this machine and don't want them there, clean them up! If you do need them, but leave them unpatched, it could be a security vulnerability waiting to happen. There is just no upside at all to ignoring the prompt to apply the service pack - you're getting it for a reason.

As for the assertion error you're getting, that has nothing to do with the SQL Server 2012 Service Pack you're being offered, either. It could be an indication that you are on RTM still and are missing a fix for that problem (in which case, go get the latest SP + CU, or that you've come across a bug that hasn't yet been fixed (in which case contact SQL Server support). Note that a fix may cover your issue even if your exact error message isn't listed explicitly in the knowledge base article.


If you would like to warm your MyISAM Key Buffer you could run this:
SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,' ORDER
BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (SELECT engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (SELECT
B.engine,A.table_schema,A.table_name,A.index_name,A.column_name,A.seq_in_index
FROM information_schema.statistics A INNER JOIN
(SELECT engine,table_schema,table_name
FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema
NOT IN ('information_schema','mysql')) B
USING (table_schema,table_name)
WHERE A.index_type <> 'FULLTEXT'
ORDER BY table_schema,table_name,index_name,seq_in_index) A
GROUP BY table_schema,table_name,index_name) AA
ORDER BY db,tb
;

Here is the output:
+----------------------------------------------------------------------+
| SelectQueryToLoadCache                                               |
+----------------------------------------------------------------------+
| SELECT start_time FROM annarbor.sq_20110722 ORDER     BY start_time; |
| SELECT id FROM junk.category ORDER     BY id;                        |
| SELECT id FROM junk.mytabletolimit ORDER     BY id;                  |
| SELECT parent_id FROM junk.observations ORDER     BY parent_id;      |
| SELECT id FROM junk.observations ORDER     BY id;                    |
| SELECT parent_id FROM junk.pctable ORDER     BY parent_id;           |
| SELECT id FROM junk.pctable ORDER     BY id;                         |
| SELECT id FROM junk.products ORDER     BY id;                        |
| SELECT id FROM junk.subcategory ORDER     BY id;                     |
| SELECT id FROM test.acties ORDER     BY id;                          |
| SELECT id FROM test.deletekeys ORDER     BY id;                      |
| SELECT email FROM test.emailtable ORDER     BY email;                |
| SELECT id FROM test.emailtable ORDER     BY id;                      |
| SELECT id FROM test.ft_test ORDER     BY id;                         |
| SELECT id_key FROM test.id_key_table ORDER     BY id_key;            |
| SELECT id_key FROM test.id_key_table_keys ORDER     BY id_key;       |
| SELECT id FROM test.mytabletodeletefrom ORDER     BY id;             |
| SELECT NGRAM_ID FROM test.ngram_key ORDER     BY NGRAM_ID;           |
| SELECT NGRAM FROM test.ngram_key ORDER     BY NGRAM;                 |
| SELECT NGRAM_ID FROM test.ngram_rec ORDER     BY NGRAM_ID;           |
| SELECT num FROM test.notforeverdata_matches ORDER     BY num;        |
| SELECT id FROM test.pamela ORDER     BY id;                          |
| SELECT id FROM test.rolando ORDER     BY id;                         |
| SELECT num FROM test.rolando2 ORDER     BY num;                      |
| SELECT CourseName FROM test.tab ORDER     BY CourseName;             |
| SELECT CourseName FROM test.tab_to_zap ORDER     BY CourseName;      |
| SELECT academy FROM test.under99color ORDER     BY academy;          |
| SELECT id FROM test.under99color ORDER     BY id;                    |
| SELECT id_key FROM test.weekly_batch ORDER     BY id_key;            |
| SELECT id FROM test.worktable ORDER     BY id;                       |
+----------------------------------------------------------------------+
30 rows in set (0.81 sec)

mysql>

This displays every possible query you could run against MyISAM tables selecting index column fields only. Running those queries will populate the MyISAM Key Buffer.
Here is a similar query to make queries that preload the InnoDB Buffer Pool
SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb
;

Here is a similar query to make queries that preload the InnoDB Buffer Pool and the MyISAM Key Buffer (I posted this back in November 2011)
SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',
    db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (
    SELECT
        engine,table_schema db,table_name tb,index_name,
        GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
    FROM (
        SELECT
            B.engine,A.table_schema,A.table_name,
            A.index_name,A.column_name,A.seq_in_index
        FROM
            information_schema.statistics A INNER JOIN
            (
                SELECT engine,table_schema,table_name
                FROM information_schema.tables
                WHERE engine IN ('InnoDB','MyISAM')
            ) B USING (table_schema,table_name)
        WHERE
            B.table_schema NOT IN ('information_schema','mysql')
            AND A.index_type <> 'FULLTEXT'
        ORDER BY
            table_schema,table_name,index_name,seq_in_index
        ) A
    GROUP BY
        table_schema,table_name,index_name
) AA
ORDER BY
    engine DESC,db,tb
;

If you see the same query appear more than once in a display, this indicates the presence of redundant indexes.
Give it a Try !!!
UPDATE 2012-02-04 21:53 EDT
While one can create caches dedicated to MyISAM via as follows:
SET GLOBAL authentication_cache.key_buffer_size = 1024 * 1024 * 8;
CACHE INDEX mydb.users INTO authentication_cache;
LOAD INDEX INTO CACHE mydb.users;

you must still decide to load all indexes or specific ones. The script I provided simply shows you the list of all possible indexes you can load. You are not obligated to load all indexes, just the ones you know are used the most. If you have the hardware and budget, you load as much as you like into the dedicated MyISAM caches.
Here is something else to consider: All the queries mentioned are actually accessing covering indexes. Running those queries will only access the MyISAM pages of the .MYI file and only the index pages of an InnoDB tables. Table data is never retrieved. In light of this, all or selected queries populated index pages regardless of storage engine.
More Links on the Principles of Covering Indexes

http://www.mysqlperformanceblog.com/2006/11/23/covering-index-and-prefix-indexes/
http://ronaldbradford.com/blog/tag/covering-index/
http://shallop.com/2011/04/covering-indexes-in-mysql/
http://www.mysqlconf.com/mysql2009/public/schedule/detail/6796

UPDATE 2012-02-04 22:05 EDT
Quick note about InnoDB vs MyISAM. You can cache data pages for InnoDB. MyISAM no.


Tabled Value Function returns incorrect or no results

The default value of @CompDate is NULL. I suppose you actually want to find rows where dbo.ACTIVE.RECCLS IS NULL. So you need to modify your function code:

ALTER FUNCTION [dbo].[TVF_Dashboad]
(
-- Add the parameters for the function here
@CompDate datetime = NULL

)
RETURNS TABLE AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT    dbo.ACTIVE.RECORD_ID, dbo.ACTIVE.CMD_ID, dbo.ACTIVE.EQUIPMENT, dbo.ACTIVE.EquipDes,
          dbo.ACTIVE.CASREP,dbo.ACTIVE.[UPDATE], dbo.ACTIVE.TYAST,
          dbo.ACTIVE.STATUS,dbo.ACTIVE.JCN, dbo.COMMANDS.Trigrpah

FROM      dbo.ACTIVE INNER JOIN dbo.COMMANDS ON dbo.ACTIVE.CMD_ID = dbo.COMMANDS.CMD ID

WHERE    (dbo.ACTIVE.RECCLS = @CompDate)
    OR (@CompDate IS NULL AND dbo.ACTIVE.RECCLS IS NULL)
)

Notice: such function may suffer from parameter sniffing issue.


Postgres is returning the “column reference '' is ambiguous” message inside a function even with aliases

The planner cannot understand which my_date you are mentioning there. Either the one in parameters or the one in the table1 (or table2). The correct syntax should be like the one below;

CREATE OR REPLACE FUNCTION test ()
RETURNS TABLE (
  my_date date, gates smallint, area_type character (1),
  area_sizes float[], area_canvases float[]
)
AS $$
BEGIN
    RETURN query
    SELECT
        coalesce(s.my_date, r.my_date) AS my_date,
        coalesce(s.gates, r.gates) AS gates,
        coalesce(s.area_type, r.area_type) AS area_type,
        s.area_sizes,
        r.area_canvases
    FROM (
        SELECT
            t1.my_date,
            t1.gates,
            t1.area_type,
            t1.area_sizes
        FROM
            table1 t1
        WHERE
            t1.area_type = 'X') s
    FULL JOIN (
        SELECT
            t2.my_date,
            t2.gates,
            t2.area_type,
            t2.area_canvases
        FROM
            table2 t2
        WHERE
            t2.area_type = 'Y') r ON r.my_date = s.my_date;
END
$$
LANGUAGE plpgsql;


How to get OUTPUT $action, Inserted.*,Deleted.* records in In SSIS

You can't, ish, is the short answer.

You have 3 ish components in a data flow task that can modify data. OLE DB/ADO.NET Destination, OLE DB Command and a Script Component.

I'm ignoring Script Component for this answer as it's the swiss army knife of components. If you want to do something badly enough, the Script Component will allow you to. Whether cutting down a sequoia tree with the miniature saw is realistic is another matter.

OLE DB Destination is that, a destination. It doesn't push rows out, it only consumes them. Yes, technically it can push rows but that's only through the error output path which still won't solve the problem of getting access to the OUTPUT virtual tables.

OLE DB Command sounds promising. It can add additional columns to your data flow. The big, BIG, BIG, downside is that it is not a set based component. It's going to operate on each individual row that flows through. If there's tens, hundreds, maybe thousands of rows, this singleton behaviour probably isn't going to kill your systems. MERGEing lakhs+ of data is probably going to result in you having a bad time.

If you're deadset on this approach, recognizing the failings with MERGE, the way to make it SSISey, would be to have two data flow components.

The first writes all of your data to a destination table. Take your existing DF and write all the data to a new table.

The second, new Data Flow, uses your MERGE statement with the accompanying virtual tables directed as needed. I talked about a delete first pattern which used the OUTPUT table over on that SO question so the MERGE shouldn't be any different.


Database Size Increase After Index Rebuild.How to get back down?

User Biju jose has challenged the frame of your question in a comment, and I think it's probably the most helpful answer that you could get:

  I believe you should not shrink the database to recover the space since once you do a rebuild after that will cause it to grow again. The point here is your database should also have space to accommodate your maintenance task also!

If you really, desperately need these 8 GBs of space back, you could use DBCC SHRINKFILE on the data file to shrink it down some.  Note, however, that this will likely cause some fragmentation.  This unhelpful cycle (fragmentation -> rebuild -> empty space -> shrink -> fragmentation -> repeat) is described in detail here:

What’s So Bad About Shrinking Databases with DBCC SHRINKDATABASE?

If you want a nice collection of additional reasons not to shrink files, check this out (and all of the articles it links to): Stop Shrinking Your Database Files. Seriously. Now.

Randolph West helpfully points out in the comments that if you do need to reduce fragmentation without growing the file so much, you could do a REORGANIZE of the index.  This requires less working space than the index, but also can only eliminate certain types of fragmentation (it rearranges and compacts the leaf pages of an index, but doesn't drop and recreate the whole thing like a REBUILD does).


You shouldn't have any issues, just be sure that when you plan your maintenance for SQL Server you don't get blocked by the "Restart Computer" rule. E.g. if you have installed Windows Updates in the meantime you might not know that Windows needs to be restarted until you enter your maintenance window...

For some background, I got surprised by this during a SQL 2012 Service Pack + Cumulative Update series recently. It turned out that I fired up the computer, started the Service Pack install, but didn't know that Windows Update had started performing updates in the background. By the time I got to the CU, the reboot flag had been set, and I was very frustrated since it seemed to me like the SP install had forced a restart requirement.

http://www.sqlperformance.com/2012/12/system-configuration/sql-2012-slipstream


SSMS 2008 R2 no longer connects to database

Your first test should be to just try and connect via telnet to port 1433 of the ip in question (assuming it isn't a named instance in which case check the start of your SQL error log to get the dynamic port number).  This will at least prove the server is listening correctly and you dont have any network/firewall issues.


Create temp tables in Amazon RDS PostgreSQL read-replica

As the most easy way which I found to solve this issue - is wrapping all "temporary tables" into separate stored procedures, which return TABLE(...) type, and then use Common Table Expressions (https://www.postgresql.org/docs/9.1/static/queries-with.html).


How recording of every change of a row in a database is generaly stored?

The normal way do do audit logging of this sort is to have a shadow table and log changes with triggers on the base table you are auditing.  The other tables can be placed on a different physical disk if you need to to that for performance, and you can put indexes on them if you need to support quick retrieval of the data.

The tables will have roughly the same structure as your original tables, but will have a datetime column for when the change took place and a marker for whether the row was inserted, changed or deleted.  Sequencing the versions can be done by the time stamp.

The change date can be done by making the datetime column not null with a default of getdate(); an audit user column will capture the user with a not null column defaulted to Suser_Sname().  Assuming the actual user is being impersonated in the session this will capture the identity of the user making the change.

The database has no way to be aware of the IP address connecting to a web server.  The application will have to explicitly capture and log the IP address with the transaction.

If you have a large number of tables you wish to audit, you can use the metadata from the system data dictionary to generate the triggers programmatically.

This solution is by far the best for several reasons:

It captures any changes to the table, not just those made by the application.
The audit tables can be put on a different set of disks to reduce the I/O load on your primary tables.
You can use a view based on a union of the table and audit log table to that will show the entire history including the current version.
You can index the audit log tables as necessary so that the audit users can query them responsively.  As usual, index selection is a tradeoff between query performance and update overhead.


Avoid Sql Conflicts When Inserting data At Same Time

There are many alternatives you could attempt, such as enforcing an exclusive table lock on the SELECT query (thus delaying one entry until after the other).

My personal favorite would be to enforce staging - production tables just like many of the testing apps I manage.

This way, it doesn’t matter the timing or order. Remember the group_ID column is a database column, so it’s best not to get lost in the details over a value whose only purpose is to enforce differentiating groups


Recover Instance after Log File disk error

Disaster Recovery Plan depends on
1.Acceptable Downtime
2.Acceptable Dataloss
Any Disaster recovery  plan you devise will revolve around the above two points only

Either option meant work for the App guys, because all the applications and associated services would need to re pointed
Theoretically, this meant no work for the App team but had a serious time overhead for me (the only DBA).

your Disaster Recovery option doesn't depend on the amount of work involved and who has what share in that..
You will have to sit together with Business Guys and get Clarity on what is the downtime and dataloss they can afford .Based on this you have to devise plans(using High availability options) and test it(Mock Drills) from time to time.


The retention policy is whatever you decide on and then implement.

Putting this under your control is what archive_command is for.  If you don't want to be in control and want PostgreSQL to do it for you, then use replication slots or wal_keep_segments instead.


Creating oracle database physical files from migrated DB

You should use export/import. On your side do:

expdp system/systempawwdord SCHEMAS=YOURSCHEMA

This will produce a file. Take it manually to you customer using CD, DVD, Pendrive. On customer site do:

impdp system/systempassword SCHEMAS=YOURSCHEMA

If the customer is using a different Oracle version, this is not a problem. You probably need to manually re-create tablespaces before importing the DB and add to the impdp command:

REMAP_TABLESPACES=OLDTBS:NEWTBS

For testing you can also do it on your actual instance, export, drop schema and import.


Merge two postgress databases with identical schemas

Unfortunately, there is no simple way to do this. Essentially you will need to write a script to go through one of the databases, extract the appropriate information, and then insert it into the other database, making sure to manage the new id numbers you get and updating the incoming information as needed. The complexity of your schema will determine how difficult this is to manage, but it is doable; pg_upgrade uses a version of this technique to handle re-mapping on-disk files inside the Postgres system catalogs.

On a side note, this is one of the arguments against using surrogate primary keys. Had you used entirely natural unique keys, you would be able to simply dump one database out and load it into the other.


You could create additional statistics for the data. Include a WHERE clause so each new statistics object references only a subset of the table. By bucketing the rows appropriately (big, medium, small?) the filtered statistics will be less bad than the whole-table ones, leading to better plans.

The predicate must match that used in queries. Depending on your settings there may be more system activity for stats maintenance. Of course this assumes the table contains (or could change to contain) some combination of columns on which rows can be meaningfully aggregated.


Text index using text_pattern_ops with utf-8 vs. default operator class with C?

It isn't about encoding, it is about collation.

Consider this:

select * from (values ('1,z'),('1,2j'),('12q'),('13 apples')) foo(x)
  order by x collate "en_US";

gives:

     x
-----------
 1,2j
 12q
 13 apples
 1,z

If you index using that collation, how would you efficiently support a x like '1,%' query?  Not all the things starting with '1,' are adjacent in the index.  This is the problem that text_pattern_ops (or C collation) solves.

You can make a database with UTF8 encoding but C collation.  This mean you don't have to sprinkle text_pattern_ops all over your indexes, and your index builds over text columns will be much faster (and any sort-merge joins you do).  But you can still store non-ASCII characters, although they will sort funny.

Also, setting client_encoding won't make any difference.  Is is the server's encoding and collation that matter, not the client's.


Can anything reveal that 2 independant entries were created or updated at the same time?

Rows inserted at the same time, particularly in the insert-only use case such as yours, will very likely reside next to each other on disk and as a result will have tuple IDs indicating that. They will remain so until you CLUSTER the table using  your unique_random1 column as the index key. You will need to run CLUSTER periodically to shuffle newly inserted rows.

Another bit of information that potentially correlates inserted rows is their transaction ID (XID), which might remain unchanged after the original commit, unless these rows at some later time are updated by separate transactions. I don't believe CLUSTER changes row XIDs.

Basically the same information -- the transaction ID, along with the row data, -- will also be stored in the write-ahead log (WAL). WAL segments will get overwritten at some point, depending on your database's checkpoint frequency.

TL;DR: You probably shouldn't rely on Postgres internals for reliable deidentification of your data.

1 -- Although, what's truly random, cannot have guaranteed uniqueness. You probably meant some sort of a hash.


Is a clustered index on a child table in a parent/child relationship the most optimal index?

You assumption about adjacency is correct.

If we use TPC-H as an example: Clustering the LINEITEMS table on on ORDERID will locate all order lines belonging to the same LINEITEM physically adjacent on disk. This speeds up queries that fetch all order lines for a given ORDERID. Clustering on the foreign key to the parent also allow fast merge joins between the child and parent.

There are a few downsides to the clustering approach:

The entire table must be kept sorted on disk. If you are expecting a
great many inserts with ORDERID not being sequentially generated,
page splits will be more expensive. This is something you can throw
hardware at.
If ORDERID is generated sequentially, you will
create a hotspot at the end of the table. In some database engines
(For example SQL Server) this is a problem at high insert speed. In SQL Server, this typically kicks in around 5K-10K inserts/sec.
The cluster index keys either have to be unique (ex: ORDERID, LINENUMBER) or padded with some
hidden column to make them unique. Since the composite cluster key must be present in all other indexes, this makes the secondary, non-clustered indexes larger.
Storing the table clustered will force a B-tree traversal when you want to locate data via a secondary index (unless the secondary index is covering the query). If you instead kept the table as a heap, all other indexes would only have an 8B overhead and your B-tree traversals are cut in half.

The vast majority of cases, you will want to cluster both the parent and the child on the same leading key. But if you expect the child table to be accessed via many different indexes - it may be worth considering the alternatives.


When you use a replica to backup from so you don't have to turn off the master, is this considered High Availablity?

High Availability (HA) is a very generic term that can be applied to a whole range of different scenarios.

There is no “one size fits all” approach to delivering High Availability (HA). Unique application attributes, business requirements, operational capabilities and legacy infrastructure can all influence HA technology selection. And technology is only one element in delivering HA: people and processes are just as critical as the technology itself.

At it's most basic it simply means ensuring your entire system (of which the database is a part) is available to meet your required SLA, whether that be 99% availability, or 99.999% availability, or somehing else. The above link gives the official definition from MySQL.

Your use of a Slave database is one of many good reasons for using a MySQL slave. And at it's most basic level, if your master fails, the slave could be manually promoted to be the master. Then you would set up the Master as the new slave once it had been recovered.

The below slide presentation gives a good overview of the main MySQL HA offerings.

MySQL HA Solutions


A Query with a Dynamic Case clause or how to avoid Case When

Not sure what reporting question you are trying to solve, but it looks like you can get what you're after by simply referencing the joined column. Here's a couple examples:
/* Count of certifications held by all employees */
SELECT  c.name
        ,COUNT(1) AS certCount
FROM    qualification AS q
        INNER JOIN certification AS c ON c.id = q.certificationid
GROUP BY
        c.name;

/* Count of certifications held, grouped by employee */
SELECT  q.employeeid
        ,c.name
        ,COUNT(1) AS certCount
FROM    qualification q
        INNER JOIN certification AS c ON c.id = q.certificationid
GROUP BY
        q.employee_id
        ,c.name;


Convert a numeric column with number eg.(-1222222) to two float column with two decimal places eg (-12222.22)

Something like this will do:

with data as
(
select -1222222 as foo
union
select 0
union
select -19
union
select 21123021
)
select cast(cast(foo as decimal)/100 as decimal(10,2))
from data;

You can also use format(foo,'N2') and a million other ways.


The microsecond fractional part of your DDL (I.e. The (6)) is only supported from MySQL 5.6 onward. Most likely a compatibility issue I'm afraid.


Create a new column in a view and assign it a data type

You'll want this.

CREATE VIEW VIEW2
AS SELECT [Column1],
cast(NULL as varchar(10)) AS column2
FROM VIEW1;


Primary Key choice on table with unique identifier

There are a few things to consider here:

How is data most often looked up in this table?
How is data most often sorted in this table?
Does this table relate to others as the parent record (i.e. will other tables FK to the PK of this table)?

Also, please keep in mind:

The key fields of a Clustered Index are copied into the non-clustered indexes on the same table
For Clustered Indexes that are not a Primary Key (implied unique) or at least declared as UNIQUE, a hidden "uniqueifier" field is added to rows that would otherwise be duplicates.
NEWSEQUENTIALID() is sequential per each restart of the SQL Server service. It is possible that the starting value after a restart is less than the previous lowest value.

Hence:

If the PK field of this table shows up as a FK field in other tables, there is a definite performance implication of choosing to use a UNIQUEIDENTIFIER instead of an INT as the FKed tables would then have a larger FK field in them.
How many rows do you really expect to have in this table? INT is 4 bytes (compared to the 8 bytes of a BIGINT) and has a max value of 2,147,483,647. If you might have slightly over 2.14 billion items, you can also start the IDENTITY range at the min value of each datatype, which for INT is -2,147,483,648. Starting at the low-end gives you the full 4.294 billion values to use. Compared to the 8 bytes of the DATETIME field if going with the Created field, plus add in the size of another field to make it unique, or the uniqueifier for any duplicate rows.
Since the key field(s) of the Clustered Index are included in Nonclustered indexes, that increases the chances of having a covering index without needing to INCLUDE other columns. Meaning, if you have the Clustered Index on the INT PK and a Nonclustered index on the UNIQUEIDENTIFIER, then JOINing to another table on that INT PK field while specifying the GUID value in a WHERE clause (assuming no other fields from this table are in the query) won't have to go back to the table since the Nonclustered Index will have both of the requiered fields in it. Does the Created field give the same benefit? Likely not.

IF no other tables ever JOIN to this table:

THEN it might be ok to use the Created field as the non-unique Clustered Index and the Id field as the Nonclustered PK.
ELSE it is typically best to add an INT (unless you need more than 4.294 billion values) IDENTITY field, AssetId, as the Clustered PK, and the Id UNIQUEIDENTIFIER field as a Nonclustered Index. Since you likely already have code referencing the UNIQUEIDENTIFIER field as Id, I wouldn't change that name.


SQL insert which updates one table and based on a field value chooses what other table needs updating

What you are looking for is called Trigger.
For example:
https://sqlite.org/lang_createtrigger.html
I've wrote a little sample for sqlite, you could repeat everything on any unix/linux system.
The init.sh:
# cat init.sh

echo "CREATE TABLE 'first_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        type            text DEFAULT 'blue', \
                        blabla          int  DEFAULT 0 \
                        );"

echo "CREATE TABLE 'blue_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        blabla          int  DEFAULT 1 \
                        );"

echo "CREATE TABLE 'red_table' ( id integer primary key, \
                        name            text DEFAULT 'noname', \
                        blabla          int  DEFAULT 2 \
                        );"

echo "CREATE TRIGGER t1 AFTER INSERT ON first_table \
        FOR EACH ROW \
                WHEN NEW.type != 'blue' \
                BEGIN \
                        INSERT INTO blue_table ( name ) VALUES ( NEW.name ); \
                END;"

echo "CREATE TRIGGER t2 AFTER INSERT ON first_table \
        FOR EACH ROW \
                WHEN NEW.type != 'red' \
                BEGIN \
                        INSERT INTO red_table ( name ) VALUES ( NEW.name ); \
                END;"

echo "INSERT INTO first_table ( name, type ) VALUES ( 'ololo1', 'red' ), ( 'ololo2', 'blue' ), ( 'ololo3', 'blue' ), ( 'ololo4', 'red' );"

Firstly, here, you are create 3 tables, thereafter 2 triggers, which are fires according to type field, if type is 'red' - fired 1st trigger, if type is 'blue' - another one.
Usage:
# bash init.sh | sqlite3 tmp.db
# echo "SELECT * FROM red_table;" | sqlite3 tmp.db
1|ololo2|2
2|ololo3|2
# echo "SELECT * FROM blue_table;" | sqlite3 tmp.db
1|ololo1|1
2|ololo4|1


Multi-Subnet Always On with Availability Group for DR

Usually, if you want to use a node for "failover" (high availability), you want to have the AG set for synchronous replication (to enable the auto-failover).

That being said, if you set it to "sync" and it's in another site, you may have performance issue caused by network latency.

Also, if your "read only" node is not part of the AG, then you will have to implement something else to keep the data sync (or close to sync)

This is why we usually see 2 nodes on the same site (for High Availability) and a 3rd node on a remote site (for DR) that usually is an async replica.

I hope it will help you to make the right design.


You don't need to find the ids with no matches when you finally filter for a percentage > 0.

Standard SQL based, but fully untested:

with cte as
 (
   select Id, Type, Value,
      count(*) over (partition by Id) as RowCount -- maximum number of possible matches
   from tab
 )
select t.ID, cte.Id as RelatedID,
   count(*) as countMatches,
   max(cte.RowCount) as Compared,
   100.0 * count(*) / max(cte.RowCount) as PercetageMatched
from tab as t -- or cte if you need the RowCount, too
join cte
  on t.Type  = cte.Type
 and t.Value = cte.Value
where t.id < cte.id
group by t.ID, cte.Id

I don't know about the actuallogic, but you might do the percentage calculation based on the min (or max) of both RowCounts.

Edit:

Of course this will create a huge intermediate result set, mainly based on the count of duplicate values. Following SQL calculates the number of rows before aggregation:

select sum(cnt * (cnt-1) / 2), max(cnt), count(*)
from
 ( select count(*) as cnt
   from tab
   group by type, value
 ) as dt

You might materialize the result of the cte in a table with a clustered index on (type, value, id), maybe this increases performance...


use aliases for your table especially when you self join

SELECT a1.id, a1.display_name, a1.agents_name, a1.estates_name, t.agents_name, t.estates_name
FROM author_table a1
INNER JOIN(
SELECT agents_name.lestates_name
FROM author_table
GROUP BY agents_name and estates_name
HAVING COUNT(agents_name) >1 AND COUNT(estates_name)>1
)temp t ON a1.agents_name = t.agents_name AND a1.estates_name = t.estates_name;


Unable to get a join to pull all records from another table in MySQL

The problem is this:

If r.dateReceived is NULL, the WHERE r.dateReceived > 1587574487 is never satisfied

What you need to do is check for r.dateReceived in the WHERE clause as well

SELECT r.dateReceived as Date,
t.empNumber AS Emp,
r.grpNumber AS Grp,
r.empFName,
r.empLName
FROM empInfo t
LEFT JOIN empreports r
ON t.empNumber = r.empNum
WHERE t.grpNumber = 100
AND t.active = 1
AND (r.dateReceived > 1587574487 OR r.dateReceived IS NULL)
ORDER BY CAST(t.empNumber as UNSIGNED INTEGER) ASC

GIVE IT A TRY !!!


Mongostat shows 2 updates on Primaries when insert data

I haven't confirmed by looking at code but I'd bet they are updates to the local database regarding your secondaries.  The primary keeps track of the current status of the secondaries in the set (including where they have synced to), so that would be an in-place update to the local.slaves collection.  Similarly, there is local.replset.minvalid which will certainly be changing as you do inserts.

Basically, I wouldn't worry about it, it's not going to be a major factor in your performance (by the way, if you are using 2.6+ then you should read this Q&A on performance of that type of loop in the shell).


Last record by date given a foreign table

Ok, I finally got my Sh** together. Was a bit rusty :

SELECT result.* FROM massifs m
JOIN LATERAL (SELECT m.id, m.name, r.record_date, r.some_usefulldata
    FROM records WHERE (m.id=r.massif_id)
    ORDER BY r_record_date DESC LIMIT 1
) result ON true
ORDER BY m.id, result.record_date DESC

To be honest I found the answer on the timescaledb docs. I thought this was a common problem in TSDB, it was !


ola-hallengren running full backups and log backups at the same time-- restore issues?

Could the log backups happening simultaneously during the full backup
cause this situation?

No, simultaneous full and log backups have been possible since SQL Server 2005, as Paul Randal states here in a post on concurrent log / full backups.

Here’s a question that came in – what changed in SQL Server 2005 that
allows concurrent log and full backups?
In SQL Server 2005, the restriction was lifted, but there’s a subtle
twist. You can do concurrent log backups with fulls BUT the log is not
cleared when the log backup ends. The clearing of the inactive portion
of the log is delayed until the full backup completes. This could
cause you to have disk space problems if your log generation rate is
huge and you’re relying on very frequent backups to manage the log
size.

Also stated here regarding the many myths regardings backups.

I used a tool that puts them in order, so that's not my issue

If it is not the tool's wrong doing as you mention then I would try to look if there are any other log backups being taken in your backup history dmv's in the MSDB database.
Double check the restore procedure as well so that you are certain that you have all the log files (for example different paths after failing over the Always On Availability Group if the database is participating in an Availability Group, however Ola's procedure should take care of that).


I'm sorry, I don't have a corrupt pg_conversion table to help perform any verifications. My answers here must, by necessity, be heavily based on speculation.

With the limited information we have, I am guessing that you are attempting to do a full database pg_dump, along the lines of

pg_dump --host 127.0.0.1 --port 5432 --username "postgres" ... "database_name"

Alternative pg_dumps?

If your data tables are still OK, you could try to pg_dump the relevant schema(s)...

pg_dump --host 127.0.0.1 --port 5432 --username "postgres" ... --schema "schema_name" "database_name"

or table(s), and use multiple table switches...

pg_dump --host 127.0.0.1 --port 5432 --username "postgres" ... --table "table_name" "database_name"

With respect to these approaches, I have no idea if this will help, as I've simply never encountered a failing pg_dump for any other reason than a syntax error, or something else relatively benign.

Use COPY to make CSVs

As a last ditch effort, if you aren't able to pg_dump your schema or tables, in a compressed .backup format, you could always extract the data using the COPY command to make comma-separated variable (CSV) files.

COPY table TO '/folder/table_backup.csv' WITH FORMAT CSV;

Good luck, and let us know if you have any success.


select single row from table for a particular column combination

declare @t table(Id int, ColA int, ColB int, Isdefault bit);
insert into @t values
(1, 100, 96, 1),
(2, 100, 96, 0),
(3, 101, 98, 0),
(4, 102, 99, 1);

with cte as
(
select *, row_number() over(partition by ColA, ColB order by Isdefault desc) rn
from @t
)

select Id, ColA, ColB, Isdefault
from cte
where rn = 1;


Enforce unique constraint on 1 of n values

You can create unique partial index:

create unique index on foo (kind) where kind=2;


MongoDB: is order guaranteed deep into the aggregation pipeline?

Your conclusion is correct: internal optimisations or implementation details may alter the order of results depending on the stage.

The $group stage is intentionally not order preserving, so you cannot rely on observational behaviour.

As noted in discussion on SERVER-24799: $group aggregation command should maintain document order in the MongoDB issue tracker:

  The reason the order is not preserved is that we keep track of all groups with a map from the _id to the accumulated value, so that we can quickly look up which group an incoming document belongs to.

As per Aggregation Pipeline Optimizations, some pipeline stages may also be coalesced or reordered to equivalent pipelines with improved performance. You can use the explain:true option to return information about how a pipeline will be executed.


I had a similar conversion of a key column from int to bigint that involved hundreds of tables, some over 100GB.  I used an approach like the one below, which accomplished the migration a couple of hours against the large client databases. If the database is in the FULL recovery model, make sure sufficient transaction log space exists and/or perform log backups during the migration to avoid filling the log.

1) Drop referencing foreign keys on tables to be modified and schemabound objects

2) Optionally, drop all non-clustered indexes (including non-clustered primary key and non-clustered unique constraint indexes) on tables to be modified to reduce disk space requirements during the migration.

3) For each table, create new table using SELECT...INTO with an explicit CAST of the column to bigint, with ISNULL to coerce a NOT NULL column when necessary. Drop original table and rename new table to original name.

4) Recreate clustered indexes/constraints, non-clustered indexes/constraints, followed by other constraints and objects.


What configs influence whether user credentials/grants are written to the binlog in Mysql

You are replicating only my_db.

There are valid security reasons for having the option to not replicate mysql.

The quick solution is to add binlog_do_db=mysql and restart.  However, any GRANTs and CREATE USERs have already been lost.  Either re-issue them and hope they replicate, or replay them on the Slave(s).


Why I don't need to COMMIT in database trigger?

You get an autonomous transaction context for these triggers automatically.

From the CREATE TRIGGER docs:

  One or more particular states of the database that can cause the trigger to fire. You can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. For each of these triggering events, the database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).

What happens if the trigger fails depends on the exact trigger/event. See Exception Handling in Triggers. In particular, a logon trigger that fails with an exception might very well lock non-dba users out - the exception makes the login fail unless the user has specific privileges.


Minimum configurations for mongodb replica set though a java program

Misconceptions

  "It is the best way to ensure data integrity to have MongoDB write to all nodes."

This is entirely wrong. Let's assume you have three members in a replica set. Well, you want the data written to all nodes, so you set your write concern to 3. Now, one of your nodes fails. And while your replica set would work perfectly for reads and writes, with a write concern set to 3, your writes will start to fail. Now you are loosing data, since you can not write any more until all members of your replica set are up, running and resynced.

The write concern REPLICAS_SAFE usually is enough. However, if you have more than 3 data bearing nodes, you absolutely need (relatively ) fast consistency across all nodes and give a damn about write performance, you might want to create a custom write concern this way

private static final int NODES = 5;
private static final int LOOONG_TIMEOUT = 5000;
int paranoidReplicas = Math.floor((NODES/2)+1);

WriteConcern custom = new WriteConcern(paranoidReplicas, LOOONG_TIMEOUT, true);

  "I have to manage the status of the cluster on the application side."

Wrong. All drivers provided by MongoDB are replica set aware. So if your primary fails, your driver will figure out which the new primary is. Transparently.

However, what you could do is to catch according exceptions, check wether there is a primary at the moment (you need the low level driver to do so) and if there isn't one, retry the write after a couple of seconds (the time it takes for electing a new primary - 4 seconds is what I tend to use).

  With a high write concern and a read preference, I can do load balancing without the need of a sharded cluster.

Ok, this is a bit of a stretch from my side, but it smells like that's being the case.

Can you do it? Well, with reservations. First of all, you have to understand that a write concern > 1 does not imply that the change is applied on all nodes simultaneously. It just guarantees that the driver will not return until the change is written to the number of specified nodes. However, until the driver returns, the change may be applied to one node, which will happily send the changed data when a query comes in, while it isn't applied to another node, yet, which will send the unchanged data. To make a long story short: do whatever you want, but consistency among the members of a replica set remains eventual. Please see the replica set introduction on read operations for details.

Another problem is that a higher write concern affects your applications performance. When just using the primary for writes, with the data journaled, your modification will be executed rather fast. With a higher write concern, your modifications will take proportionally longer.

A problem related to the last one is that connections will be returned to your connection pool at a slower pace (and might well block threads of your thread pool, in case you use some sort of servlet engine or application server), allowing fewer concurrent users, although this only applies to loads bordering your hardware's (and configuration's) limits.

Is it a good idea? IMHO, in no way. You will get only a marginal read load balancing with eventual consistency and you pay for it with a rather high impact on your write performance and resource efficiency. If your primary can't handle the load, it is under dimensioned. You either need to scale up or out.

  Asking this question on Stackoverflow will provide me with enough information to run or use a MongoDB cluster properly.

Erm... ...no. You might get a hint, but a short answer can not replace proper training. Or reading the docs thoroughly, for that matter. See above.


Get all field names from all tables in Sybase DB


Get notified when SQL Server 2008 Audit Server logs something?

One consideration would be to query the sys.dm_audit_actions view at regular intervals to see what new information has been logged to the audit log.  For example, create a stored procedure to mail the results of a query from this view to a set of people on a daily basis.  The only drawback would be that it wouldn't be delivered as it happens (asynchronously) unless you wanted to script it in .Net.


MariaDB Master / Slave failover / failback automation

Get a package like "MHA".  It is "free".

See also Galera and Orchestrator.


This is happening because the auto-complete isn't context aware & it's offering you a column name of widget_date as well as the table name you're expecting.


Query to find products with all the given attributes

Your example has syntax errors.  Were you hoping to get just 1001001 from that sample data?

One way:

( SELECT product_id from product_attributes
        WHERE att_val1 = 'x202' AND att_val2 = '2200' )
UNION ALL
( SELECT product_id from product_attributes
        WHERE att_val1 = 'SS120' AND att_val2 = '1080' )
GROUP BY product_id
HAVING COUNT(*) = 2;

The best way:

SELECT product_id
    FROM product_attributes AS a
    JOIN product_attributes AS b  USING(product_id)
    WHERE ( a.att_val1 = 'x202'  AND a.att_val2 = '2200' )
      AND ( b.att_val1 = 'SS120' AND b.att_val2 = '1080' )

Both ways will benefit from INDEX(att_val1, att_val2, product_id).

The schema is called EAV (Entity-Attribute-Value) or Key-Value.  It is a nuisance for writing queries and for performance.  More discussion:   http://mysql.rjweb.org/doc.php/eav


What is difference between tempdb log and database log? In which circumstances these log files uses?

MS SQL Server is an ACID compliant RDBMS.  One of the critical tools involved in providing ACID compliance is a concept of a transaction log.  The transaction log is where the pages modified is saved, so it can revert them and put them back in the previous state if you need to rollback a transaction.  There is much to consider here but you want to ensure you size your VLFs and manage it in advance.

TempDB is a temporary area used by MS SQL Server to store objects like temp tables, join results, some caching, etc.  Unless you have performance issues, you generally don't want to worry about this, don't even back it up.  It get's rebuilt on startup.  It is good practice to create at least 2 or maybe up to 4 physical files for TempDB so you don't get PAGELATCH contention.

Paul Randall (An authority on MS SQL Server) recommends letting your transaction log be at whatever size it grows to after a full weeks activity after reindexing.  Really, unless it grows a lot, don't worry too much about it.  Just make sure to avoid growths.


Your design is good. If you are having a performance problem (which you can't know at design time), you should create an index on column table1.ref_field, in the same order (ASC) as the table2.id column. This will improve performance on joins between those to tables/columns. There is overhead to maintaining any index, so you want to weigh that cost against the benefit of improved performance.

PostgreSQL doesn't automatically create such indexes on foreign key columns that reference other columns, unfortunately, so you have to do it yourself.

Here is a StackOverflow question on the same topic:

Postgres and Indexes on Foreign Keys and Primary Keys

Here is a query to help determine where you might benefit from adding such an index:

Postgresql: Indexes on Foreign Keys


SELECT query: filter unable to distinguish between two different characters

Both of those characters actually match more than just each other. Testing in both SQL Server 2012 SP3 and SQL Server 2016 SP1, and across the following 4 Collations (everything-sensitive) in each of those versions:

Japanese_XJIS_100_CS_AS_KS_WS_SC  (16)
Japanese_90_CS_AS_KS_WS_SC  (12)
Japanese_Bushu_Kakusu_100_CS_AS_KS_WS_SC  (15)
Japanese_Unicode_CS_AS_KS_WS  (13)

I found that each of those 2 characters matched anywhere from 10 - 14 additional characters (numbers in parenthesis in list above is total matches for the group that includes the two characters in the question):

Dec     Hex         Character
8213    0x2015      ―
12293   0x3005      々
12337   0x3031      〱
12338   0x3032      〲
12339   0x3033      〳
12340   0x3034      〴
12341   0x3035      〵
12347   0x303B      〻
12445   0x309D      ゝ
12446   0x309E      ゞ
12448   0x30A0      ゠
12540   0x30FC      ー
12541   0x30FD      ヽ
12542   0x30FE      ヾ
40981   0xA015      ꀕ
65392   0xFF70      ｰ

You can test with something along the lines of:

SELECT NCHAR(0x2015), NCHAR(0x30fc)
WHERE NCHAR(0x2015) = NCHAR(0x30fc) COLLATE Japanese_Unicode_CS_AS_KS_WS;

However, testing via the ICU Collation Demo (be sure to uncheck "diff strength" and check "sort key"), selecting either one of the two Japanese options from the drop-down, and using any combinations of radio-buttons, doesn't ever produce a match for U+2015, though U+30FC does seem to match U+FF70. So, based on this, it would appear that perhaps these two characters shouldn't match. It is always possible that one or more weights was entered incorrectly.

However (again), we must also keep in mind that what we see in that ICU Collation demo and on the Unicode site (the main pages, at least -- not prior versions) isn't the same version of Unicode that SQL Server is using. SQL Server is using either version 4 or 5 of Unicode, while the current stuff we see in those two places online is version 10..

Hence, unfortunately, you might have to stick with occasionally using a binary collation to distinguish between these characters.

Other notes:

Don't use the deprecated _BIN Collations. Just use the newer / current _BIN2 Collations
Just FYI, for binary Collations, the locale doesn't make a difference for NVARCHAR data. And the only difference for VARCHAR is the Code Page that is used (i.e. the character set). Meaning, with NVARCHAR data, there is no difference between Japanese_Unicode_BIN2 and Latin1_General_100_BIN2.
The new "Variation Selector Sensitive" (_VSS) Collations introduced in SQL Server 2017 do not help with this. This is primarily due to Variation Selectors being specific code points in the range of U+FE00 to U+FE0F and supplementary code points U+E0100 to U+E01EF. The characters in question here are not in either of those ranges. Also, Variation Selectors are zero-width, non-printable combining characters that attach to the preceding character.

There was a slight chance that the newer Collations would handle these characters better simple by being newer and hence having the opportunity to have weights added or fixed. However, there was no improvement as discovered using the following test:

;WITH cte AS
(
  SELECT TOP (65535) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [Num]
  FROM [master].[sys].[columns] col1
  CROSS JOIN [master].[sys].[columns] col2
)
SELECT cte.[Num], NCHAR(cte.[Num])
FROM cte
WHERE NCHAR(cte.[Num]) COLLATE Japanese_XJIS_140_CS_AS_KS_WS
     IN (NCHAR(0x2015), NCHAR(0x30fc))

Both Japanese_Bushu_Kakusu_140_CS_AS_KS_WS_VSS and Japanese_XJIS_140_CS_AS_KS_WS_VSS returned the same rows as Japanese_Bushu_Kakusu_100_CS_AS_KS_WS_SC and Japanese_XJIS_100_CS_AS_KS_WS_SC, respectively.


db2: configurations covered by "create db autoconfigure"

You can start by creating the database without using AUTOCONFIGURE. Once it is created use AUTOCONFIGURE with the option APPLY NONE. It will show you what DB2 thinks are better settings.

Many DBAs then use the output of APPLY NONE as input to manually setting the values using update db cfg, it is a common approach.


SQL Server: Slow restore from SQL 2014 DB, but Fast from SQL 2016 DB via SSMS

The restore from 2014 is an upgrade, not just a restore.  You should actually see this in the messages window, assuming you are using T-SQL to do the restore.   You don't mention what Fast and Slow are, or the differences, such as 5 mins vs. 5 hours, so we can't add much to this.


How to find values from one table missing in another?

select author from article where author IS NOT NULL and author <> ''
except
select name   from author

since any comparison the null is false I think below works

select author from article where author <> ''
except
select name   from author


Simply put, as the table (or index) grows, the performance approaches a minimum of one disk hit per lookup.
This is because UUIDs (other than a version 1 UUID with the bits suitably shuffled) are effectively "random".  That is whatever is in the buffer_pool cache is approaches being useless for the next lookup.
You seem to have 3 such indexes; this means that even inserting one row is threatening to require 3 disk hits!  20M new rows per month = 8 rows per second = upwards of 24 disk hits per second just for the insert traffic.  You could tackle this with a huge amount of RAM or by avoiding the 3 UUIDs.
Similarly, any SELECT that uses an INDEX starting with one of those uuids will incur 1 disk hit.
Keep in mind (for InnoDB) that the PRIMARY KEY is a big BTree with all the data, sorted by the PK.  And a secondary index is a smaller BTree with the key column(s) plus the PK's column(s).  So, some things stated about the PK and the seocndary key are interchangeable.
Of note,...  When looking up a row by a secondary key, two BTree lookups are performed, one per BTree involved.  If both are UUIDs that could mean 2 disk hits.  If you have started, and used composite indexes starting with started, and most queries reached for data around the same time, then you get "locality of reference benefits.  That is, everything might be cached and you won't need disk hits.
All of this leads to the beauty of using UUID, version 1, and rearrangin the bits so that the "time" part is in the high order bits.
(For your Q2 and Q3)  Assuming you now have
PRIMARY KEY(started),
INDEX(app_id_one),
INDEX(app_id_two),
INDEX(app_id_thre),

And assuming you are using InnoDB, then you already have started implicitly tacked onto the end of the 3 secondary indexes.  If, instead, you had
PRIMARY KEY(started),
INDEX(app_id_one),
INDEX(started, app_id_two),
INDEX(started, app_id_thre),

then the indexes would take exactly the same amount of space.  (OK building a BTreee leads to some unpredictables variations in "fragmentation", etc.)
Using a DATETIME as the PK is risky unless you are very sure you won't have any duplicates.  Changing the PK to INT or BIGINT (presumably AUTO_INCREMENT) would not change the principles here; just a slight difference in space needed.
pt-online-schema-change or gh-ost or MySQL 8.0 can change the existing table's indexes with minimal interruption.
I don't understand how you can change from the current WHERE app_id_two = ... to WHERE started = ... AND app_id_two = ....  If you currently have started available, then just use it and get rid of the other columns??


Scenario/Method (idea) for transfering database from one server to another

If you are using InnoDB, the "backup" is useless.  ibdata1 is needed.  And there are other issues.

If you are using MyISAM, it will probably work.


Remote query (openquery) to linked server fails after upgrading to SQL Server 2016

The query you're sending to the linked server (via OPENQUERY) shouldn't also contain its own reference to the linked server (via 4-part name), unless the linked server has its own loopback linked server with the same linked server name (and even if it does, I wouldn't try to stack linked servers like that). Try:

SELECT t.Name, t.Disabled
FROM OPENQUERY
(
   [Linked_Server],
   N'select t.Name, t.Disabled
     from [Database-1A].[dbo].[My_tbl] AS t
     where t.ID=232627;'
) AS t;


Oracle function based index is not used in query

You can logically see that the index would narrow the scope of the count, but the database can't make the same determination.  To use a function based index you need to use the function itself in your query.  Assuming the data is such that the index will be useful, something like this should cause it to be used:

SELECT COUNT(val1) WHERE CASE WHEN col1 = 3 THEN val1 ELSE NULL END IS NOT NULL;


Do you know if this are examples of non-identifying or identifying relationships?

Is the user_id necessary for identifying the conference? Without user_id, would you still be able to unambiguously identify one conference from another?

My guess would be yes because I imagine venue_id (which joins to a table of conference centers) and venue_date uniquely identify conferences. If so, then you have a non-identifying relationship between the conferences table and the users table.

For example: in a baseball DB, is team/player an identifying or non-identifying relationship? It may seem that a team would be a necessary component for identifying a player, but think for a moment: what if a player is a free agent between seasons? Then he may not belong to a team for quite a while.

This takes some time to fully contemplate. Try these explanations:

From - https://stackoverflow.com/questions/762937/whats-the-difference-between-identifying-and-non-identifying-relationships

  An identifying relationship is when the existence of a row in a child
  table depends on a row in a parent table....Formally, the "right" way to do this is to make the foreign key part of the child's primary key. But the logical relationship is that the child cannot exist without the parent.

and

  A non-identifying relationship is when the primary key attributes of
  the parent must not become primary key attributes of the child

Here is another example I like: https://www.datanamic.com/support/relationshiptypes.html


Is it possible to index a computed column used in ORDER BY?

There are several things you can do to improve the efficiency of the query.

First, index the tables. OK, it's not trivial to do this. And any indexing should be considered in relation with all the queries you are running in the database. Lets pretend that this is the only query or that it is the most crucial one.

We'll break this task into the 2 tables involved. The first table, uploaded, appears - besides the join - only in the select list (all its columns) and in the 2 WHERE conditions:

AND uploaded.active = 1
AND uploaded.nsfw = 0

Two simple equality conditions, so the immediate thought is to make an index on these two columns or better include the joining column (this is a technicality, as the table is using the MyISAM engine): (active, nsfw, file_name)

ALTER TABLE uploaded
  ADD INDEX active_nsfw_fname_IX
    (active, nsfw, filename) ;

Then we go to the downloaded table. This is more complicated. Its columns do not appear in the select list (only an aggregate result, the count) but they are used in the WHERE, GROUP BY clauses and (the computed count) in the ORDER BY / LIMIT. To complicate matters, one of the conditions is a range condition (>) and not equality:

WHERE downloaded.completed = 1
  AND downloaded.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY downloaded.file_name
ORDER BY downloaded_count DESC LIMIT 30

If both were equalities, I'd blindly add an index on (completed, datetime, file_name) but in this case, I would first try an index on (completed, file_name, datetime), i.e. first the column from the equality check, then the column in the grouping and finally the other.

ALTER TABLE downloaded
  ADD INDEX comp_fname_dt_IX
    (completed, file_name, datetime) ;

Another improvement that is often possible in queries that have ORDER BY and LIMIT is to first do this in a derived table and then join the other tables. We can't really do this exactly here but we can try to put this in the derived table.
Notice that from the uploaded table only the columns in the active_nsfw_fname_IX index are used
And that for the downloaded table the columns of the comp_fname_dt_IX index are aligned with the order that queries are (logically) executed (WHERE - GROUP BY - SELECT):

( SELECT COUNT(CASE WHEN d.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END)
             AS downloaded_count
       , d.file_name
  FROM downloaded AS d JOIN uploaded AS u
    ON d.file_name = u.file_name
  WHERE d.completed = 1
    AND u.active = 1
    AND u.nsfw = 0
  GROUP BY d.file_name
  ORDER BY downloaded_count DESC LIMIT 30
)

The query finally becomes:

SELECT dc.downloaded_count
     , dc.file_name                 -- you can remove this column from the results
     , up.*                         -- as uploaded has a file_name column
FROM
    ( SELECT COUNT(CASE WHEN d.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY) THEN 1 END)
                 AS downloaded_count
           , d.file_name
      FROM downloaded AS d JOIN uploaded AS u
        ON d.file_name = u.file_name
      WHERE d.completed = 1
        AND u.active = 1
        AND u.nsfw = 0
      GROUP BY d.file_name
      ORDER BY downloaded_count DESC LIMIT 30
    ) AS dc
    JOIN uploaded AS up
      ON dc.file_name = up.file_name
ORDER BY downloaded_count DESC ;                 -- no need for LIMIT here

Testing time! I suggest you add these 2 indexes and then time the queries, both your original one and the one above.

And to answer your question, no it is not possible to index the aggregated column. MySQL has neither computed columns nor materialized views which is what is needed in this case (only MariaDB has implemented persistent, computed columns but that wouldn't help you in this query.)

So, the only way is to try by indexing and rewriting the query to reduce as much as possible the number of rows that will be ordered by - and that's what I essentially tried to accomplish above.

You will still see a "filesort" in the EXPLAIN but it's totally different (in terms of performance) to sort 100 than 10k rows.


Dropping unique constraint on PostgreSQL table taking 10+ hours

Step one - go into task manager, have a look and see if anything is actually happening. If you can't see a lot of cpu and/or disk bandwidth then it's not doing anything.

Step two - check what PostgreSQL says is happening. There are two system views useful here: pg_stat_activity and pg_locks. See what's in both. The database/relation numbers in pg_locks are internal OIDs - try SELECT OID,* FROM pg_class to see which tables/indexes are being locked.

My guess is that the system is idle and it's just waiting for an exclusive lock on the table in question and something else is blocking it. The two system views should show you what is happening though.


A question regarding MySQL master-slave replication

For some use-cases, the (usually) small delay before the data appears on the slave isn't a problem.

For "critical" reads, you could send them to the master only.

Another solution would be to use so-called synchronous replication, though actual synchronous replication doesn't really exist. The technology used in Galera Cluster (used by MariaDB Cluster and Percona XtraDB Cluster) is virtually synchronous. The same is true for MySQL's InnoDB Cluster.


Updating multiple mysql rows where column has specific value

create table temp with two fields your have in your csv and

load data infile '/path/your.csv' into table temp FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;

then perform join

update wp_usermeta, temp set wp_usermeta.meta_value=temp.meta_value where wp_usermeta.user_id=temp.user_id;


Select query for returning different data based on creation time

If I guessed the missing pieces correctly, this query using a crosstab() function should do the job (and much faster than the original - except for small cardinalities where not much can be gained to begin with):
It requires that you install the additional module tablefunc first (once per database. Detailed instructions in this related answer on SO:
PostgreSQL Crosstab Query
SELECT name, project_id, positions, created_day
      ,yesterday, last_week, last_month, three_month, six_month, last_year
FROM  (
   SELECT k.name, pr.keyword_id, pr.project_id, pr.created_at::date AS created_day
        , array_agg(DISTINCT pr.position) AS positions
   FROM   project_reports  pr
   JOIN   keywords         k  ON k.id = pr.keyword_id
   GROUP  BY k.id, pr.project_id, created_day -- k.id being the pk
   ) sub
LEFT JOIN crosstab(
    $$SELECT ARRAY[keyword_id, project_id], created_at::date, array_agg(DISTINCT position)
      FROM   project_reports
      WHERE  created_at::date IN (
                    current_date - 1
                  , current_date - 7
                  , (now() - interval '1 mon')::date
                  , (now() - interval '3 mon')::date
                  , (now() - interval '6 mon')::date
                  , (now() - interval '12 mon')::date
                  )
      GROUP  BY 1,2
      ORDER  BY 1,2$$
   ,$$VALUES
       (current_date - 1)
      ,(current_date - 7)
      ,((now() - interval '1 mon')::date)
      ,((now() - interval '3 mon')::date)
      ,((now() - interval '6 mon')::date)
      ,((now() - interval '12 mon')::date)$$
   ) AS t (
        kp int[]
      , yesterday   int[]
      , last_week   int[]
      , last_month  int[]
      , three_month int[]
      , six_month   int[]
      , last_year   int[]
      ) ON t.kp[1] = keyword_id
       AND t.kp[2] = project_id;

To break it down, a simple demo of what I am doing in the crosstab:
SELECT * FROM crosstab(
 $$SELECT *
   FROM  (
      VALUES
        ('{1,2}'::int[], current_date - 1, '{1,2}'::int[])
      , ('{1,3}'::int[], current_date - 1, '{1,2}'::int[])
      , ('{1,2}', current_date - 7, '{5,6,7}'::int[])
      , ('{1,3}', current_date - 7, '{5,6,7}'::int[])
      , ('{1,2}', (now() - interval '1 mon')::date, '{9,10}'::int[])
       -- 3 mon missing
      , ('{1,2}', (now() - interval '6 mon')::date, '{6,6,6}'::int[])
       -- 12 mon missing
      ) sub
    ORDER BY 1,2$$

,$$VALUES
    (current_date - 1)
   ,(current_date - 7)
   ,((now() - interval '1 mon')::date)
   ,((now() - interval '3 mon')::date)
   ,((now() - interval '6 mon')::date)
   ,((now() - interval '12 mon')::date)$$
) AS t (
     kp int[]
   , yesterday   int[]
   , last_week   int[]
   , last_month  int[]
   , three_month int[]
   , six_month   int[]
   , last_year   int[]
   );

Result:
  kp   | yesterday | last_week | last_month | three_month | six_month | last_year
-------+-----------+-----------+------------+-------------+-----------+-----------
 {1,2} | {1,2}     | {5,6,7}   | {9,10}     |             | {6,6,6}   |
 {1,3} | {1,2}     | {5,6,7}   |            |             |           |

The rest should be obvious.
Comments

I de-coupled the two parts and fused the plethora of correlated subqueries into a single subquery, which should be much faster. Then I use cross tabulation pull individual columns from this query. The syntax for crosstab() is the tricky part.

I am not joining to project_keywords, it seems to be just noise. I am suspecting problems in your relational model.

I seems you should include the project_id in the result to make sense. Else you should not GROUP BY it ...

You can just subtract integer from date.