Yes, as (like you described) rotation replaces the file with the same name, you can find whichever name you like, without any daily changing bits.


Append values to MySQL column and delete old values

First, you can use the VALUES() syntax to update. No need to send parameters twice:

$sql = "
    INSERT INTO multi
        (unitid, value, date_time)
    VALUES
        (?,?,?)
    ON DUPLICATE KEY UPDATE
        presence_value = CONCAT(value, VALUES(value)),
        date_time = VALUES(date_time);
";

To keep only the last three, you can use some string functions:

        presence_value = CONCAT_WS(','                                -- separator
                                   SUBSTRING_INDEX(value, ',', -2),   -- last two
                                   VALUES(value)),                    -- new one

The code becomes:

$sql = "
    INSERT INTO multi
        (unitid, value, date_time)
    VALUES
        (?,?,?)
    ON DUPLICATE KEY UPDATE
        presence_value = CONCAT_WS(','                                -- separator
                                   SUBSTRING_INDEX(value, ',', -2),   -- last two
                                   VALUES(value)),                    -- new one
        date_time = VALUES(date_time);
";
$stmt = $db->prepare($sql);
$stmt->execute([$unit, $value, $time]);

No CRON jobs, no events, no triggers needed.
You need to make sure that that the 3 concatenated values will not exceed the 21 characters combined or increase the size of the column.


Interesting use case...

What makes your problem complex for a DB is your access pattern. Looks like you want to access both by row as well as column. General purpose DBs are generally either row-oriented storage (mostly) or column-oriented storage which will be their most efficient mode of access. They will support the access other way round also (for e.g column based access in a row-oriented storage) but it will not be most efficient for obvious reasons.

If you have a access pattern (row/column) which is way more frequent than the other, you can pick the appropriate DB. If both the access patterns are equally likely, you may consider storing the information in a redundant way. i.e Both the matrix and the transpose of it. As you said the density of the matrix is 0.03%, the overhead may not be too much. You can make the call here.

Coming to the DBs, most of the noSQL DBs offer a flex schema. i.e you do not need to define the schema(columns) upfront and the columns can be optional. For this reason, I think a noSQL DB will be a better fit for this sparse matrix use case. When you query for the row, you will get only the columns which has values in it. You will get the column name along with the result.

The CRS format, while it is great on its own for space saving, it does not fit so well in a DB schema. You will have to handle the access from you application logic. In other words, you will not be really using the row-based access mechanism of DB.

Another option is to use a modified CRS format. For every row, you can store the matrix column values as a series of (column,column value) pairs. You can store this as a single value in a single column of the database. This will avoid the per-column overhead of the DBs. However, you have to do extra processing to decode the matrix columns in your application.

Which DB ? I dont want to pick a name. I would be starting an opinion war. Please do this research separately.


Counting number of occurrences, how to "group by"

Well, I was able to get it working.
If anyone else has some similar scenario, here's what worked:
select casenum,
matcode as casetype,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
(select
  count(sp_name(referred_link,1))
  from cases
  where sp_name(referred_link,1) = referrer
  and date_opened&gt;='##STARTDATE##'
  and date_opened&lt;='##ENDDATE##'
  group by cases.referred_link
) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star,
'repeat client' as reason
from cases
inner join party on cases.casenum=party.case_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on party.party_id=a.names_id
where party.role='Plaintiff'
and party.our_client='Y'
and date_opened&gt;='##STARTDATE##'
and date_opened&lt;='##ENDDATE##'
and cases.case_date_6 is null
and party.party_id IN
  (select
    party_id
    from
      (select
        count(casenum) as casecount,
        party_id
        from cases
        inner join party on cases.casenum=party.case_id
        where party.role='Plaintiff'
        and party.our_client='Y'
        and matcode IN ('PMV','MVA','IMV','GPI','IGP','S%F','ISF')
        group by party_id) multicases
        where multicases.casecount>=2)
group by cases.referred_link, cases.casenum, cases.matcode, date_opened, mailing_list
UNION ALL
select casenum,
matcode,
date_opened,
sp_first_party(casenum) as client,
sp_name(referred_link,1) as referrer,
count(sp_name(referred_link,1)) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star,
'personal referrer' as reason
from cases
inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on names.names_id=a.names_id
left join provider on names.names_id=provider.name_id
left join party on cases.casenum=party.case_id
where names.person='Y'
and date_opened&gt;='##STARTDATE##'
and date_opened&lt;='##ENDDATE##'
and party.our_client='Y'
and party.role='Plaintiff'
and party.party_id != referred_link
and ((code not like '%Attorney%' and code not like '%Established%' and code not like '%Doctor%' and code not like '%Chiropractor%') or provider.code is null)
and matcode in ('PMV','IMV','MVA','S%F', 'ISF', 'GPI', 'IGP')
group by cases.referred_link, casenum, cases.matcode, date_opened, mailing_list
order by num_refs desc, date_opened asc


How can we trim bad data and output clean trimmed data using DQS or MDS?

I think if you can use SQL Server 2017, this new function Trim() Can help you write it better. if you insist on using DQS for this matter, I think this article can help you find your way. Sorry that I couldn't write it for you. Hope it helps.


Does it ever make sense to use RAM Disk to force RAM allocation for tempdb with SQL Server 2008

I'll suggest that moving tempdb to SSDs (which you can do in a cluster, too, as of SQL Server 2012) will:

provide a roughly equivalent benefit (not quite the same, but leaps and bounds better than spinny disks);
be supported (I don't believe Ram Disks are officially supported, but I'll confess it's been a long time since I looked into it); and,
won't use up that valuable RAM for tempdb when tempdb is not using it - at those times your system would benefit more from being able to use it for buffer, query execution, etc.

In general I agree with @Dan - there are probably optimizations you can make within SQL Server that will have much more noticeable impact than moving tempdb into RAM. For example, have you investigated optimizing or even eliminating some of the things that are using tempdb in the first place?


T-SQL DATEDIFF_BIG - 469ms diff with currentmills and documented start-end constraints incorrect?

The documentation for datediff_big is wrong. The limits specified is for datediff.

The millisecond value of 593 that you see is an artefact of the site CurrentMills that get the datetime value of now with millisecond precision and the keeps that value.

Use this link https://currentmillis.com/?now multiple times and navigate to 1970-01-01 and see that you get different rest values each time.


Error using SQL Server 2019 Import and Export Data (64-bit)

Hmm interesting it's calling out SSIS. This is just going out in a limb, but can you use the SQL installer to ensure SSIS is installed and then retry: https://docs.microsoft.com/en-us/sql/integration-services/install-windows/install-integration-services?view=sql-server-ver15
Hopefully that fixes the underlying issue.


I can see how one might think listen_addresses = 'localhost' is somehow related to the authentication method local. But it is not. I quote the manual on listen_addresses:

  listen_addresses (string)

  Specifies the TCP/IP address(es) on which the server is to listen for connections from client applications.

And the chapter The pg_hba.conf File in the manual:

  local

  This record matches connection attempts using Unix-domain sockets. Without a record of this type, Unix-domain socket connections are
  disallowed.

Bold emphasis mine.

For connections via localhost (TCP/IP, local loopback) you actually need an entry for the authentication method host in your pg_hba.conf file (not for local).


Remove Duplicate Entries from DB w/ Unique ID that is NOT Primary Key - Keep one of course!

If more than, say, half the table needs to be removed, it is faster (etc) to

CREATE TABLE new LIKE real;
ADD PRIMARY KEY (...);   -- or UNIQUE
INSERT INTO new
    SELECT DISTINCT ... FROM real;
RENAME TABLE real TO old,
             new TO real;
DROP TABLE old;


PostgreSQL

Go check out the book PostGIS in Action Second Eddition.

PostGIS has everything you're requesting.

  Backend should be able to search photo in area (by radius), search photo by tag and probably suggest tags (kind of autocomplete), all that stuff will be visible thru some simple REST API.

CREATE TABLE photos (
  photoid  serial        PRIMARY KEY,
  path     text,
  tags     text[],
  location geography,
  meta     jsonb
);

INSERT INTO photos(path,tags,location, meta)
VALUES
  (
    '/foo/asdf.jpg',
    ARRAY['person', 'america', 'male'],
    ST_MakePoint(5.493,3.2948),
    '{"CAMERA":"NIKON"}'::jsonb
   );

SELECT *
FROM photos
WHERE tags @> ARRAY['person'];

SELECT *
FROM photos
WHERE ST_DWithin(ST_MakePoint(0,0)::geography, location, 10000);


Capitalize only the first letter of each word of each sentence in SQL Server

declare @a varchar(30);

set @a = 'qWeRtY kEyBoArD TEST<>&''"X';

select stuff((
       select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
       from (select cast(replace((select @a as '*' for xml path('')), ' ', '<X/>') as xml).query('.')) as T1(X)
         cross apply T1.X.nodes('text()') as T2(X)
         cross apply (select T2.X.value('.', 'varchar(30)')) as T3(V)
       for xml path(''), type
       ).value('text()[1]', 'varchar(30)'), 1, 1, '') as [Capitalize first letter only];

This first converts the string to XML by replacing all spaces with the empty tag <X/>. Then it shreds the XML to get one word per row using nodes(). To get the rows back to one value it uses the for xml path trick.


Because SQL Server 2012 isn't out yet, and we're dealing with beta code, this is probably best handled as a support request at Connect.Microsoft.com.  Include all of your logs, and they may be able to diagnose it.

If you do want to pursue it here, include the full build number that you're using, and expect to hear people answer with, "You should try again with the latest release candidate."


Is there any limit to the number of databases you can put on one SQL server?

I've worked on SQL Servers with 8 to 10 thousand databases on a single instance.  It's not pretty.

Restarting the server can take as long as an hour or more.  Think about the recovery process for 10,000 databases.

You cannot use SQL Server Management Studio to reliably locate a database in the Object Explorer.

Backups are a nightmare, since for backups to be worthwhile you need to have a workable disaster recovery solution in place.  Hopefully your team is great at scripting everything.

You start doing things like naming databases with numbers, like M01022, and T9945.  Trying to make sure you're working in the correct database, e.g. M001022 instead of M01022, can be maddening.

Allocating memory for that many databases can be excruciating; SQL Server ends up doing a lot of I/O, which can be a real drag on performance.  Consider a system that records carbon use details across 4 tables for 10,000 companies.  If you do that in one database, you only need 4 tables; if you do that in 10,000 databases, all of sudden you need 40,000 tables in memory.  The overhead of dealing with that number of tables in memory is substantial.  Any query you design that will be ran against those tables will require at least 10,000 plans in the plan cache if there are 10,000 databases in use.

The list above is just a small sampling of problems you'll need to plan for when operating at that kind of scale.

You'll probably run into things like the SQL Server Service taking a very long time to start up, which can cause Service Controller errors.  You can increase the service startup time yourself, create the following registry entry:

Subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control
Name:   ServicesPipeTimeout
Type:   REG_DWORD
Data:   The number of milliseconds before timeout occurs during service startup

For example, to wait 600 seconds (10 minutes) before the service times out, type 600000.

Since writing my answer I've realized the question is talking about Azure.  Perhaps doing this on SQL Database is not so problematic; perhaps it is more problematic.  Personally, I'd probably design a system using a single database, perhaps sharded vertically across multiple servers, but certainly not one-database-per-customer.


How to turn Event Scheduler On permanently in MySQL?

This is quite an easy one really (one of those "Doh"  moments :-) ).

You set it in my.cnf as described here:

[mysqld]
..
.. other_stuff
..
event_scheduler=on
..
.. more_stuff
..

(or my.ini if you're running Windows as pointed out below)

From the MySQL documentation here, you have:

  When the Event Scheduler is ON, the event scheduler thread is listed
  in the output of SHOW PROCESSLIST as a daemon process, and its state
  is represented as shown here:

A daemon process is one that runs in the background as described here. Also known as a service in Windows

So, run:

mysql> SHOW PROCESSLIST\G

and there should be an entry like this:

*************************** 2. row ***************************
     Id: 2
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon      <<<<<====== Note: Daemon!!!
   Time: 3
  State: Waiting for next activation
   Info: NULL
2 rows in set (0.00 sec)


Using a type in place of a column definition list?

Use json_populate_record:

SELECT *
FROM json_populate_record(null::foo, '{"a":1,"b":2,"c":3,"d":4}')

The column matching is done by name, non-existing columns are silently ignored:

create type other_foo as (a int, b int, x int, y int):
SELECT *
FROM json_populate_record(null::other_foo, '{"a":1,"b":2,"c":3,"d":4}');

returns:

a | b
--+--
1 | 2


Are explicit transactions needed in this while loop?

Individual statements -- DML, DDL, etc -- are transactions in themselves. So yes, after each iteration of the loop (technically: after each statement), whatever that UPDATE statement changed has been auto-committed.

Of course, there is always an exception, right? It is possible to enable Implicit Transactions via SET IMPLICIT_TRANSACTIONS, in which case the first UPDATE statement would start a transaction that you would have to COMMIT or ROLLBACK at the end. This is a session level setting that is OFF by default in most cases.

  do we need to add explicit BEGIN TRANSACTION / END TRANSACTION statements so that we can cancel any time?

No. And in fact, given that you want to be able to stop the process and restart, adding an explicit transaction (or enabling Implicit Transactions) would be a bad idea since stopping the process might catch it prior to it doing the COMMIT. In that case you would need to manually issue the COMMIT (if you are in SSMS), or if you are running this from a SQL Agent job, then you do not have that opportunity and might end up with an orphaned transaction.

Also, you might want to set @CHUNK_SIZE to a smaller number. Lock escalation generally happens at 5000 locks acquired on a single object. Depending on the size of the rows and if it is doing Row locks vs Page locks, you might be going over that limit. If the size of a row is such that only 1 or 2 rows fit per each page, then you might always be hitting this even if it is doing Page locks.

If the table is partitioned then you have the option of setting the LOCK_ESCALATION option (introduced in SQL Server 2008) for the table to AUTO so that it locks only the partition and not the entire table upon escalating. Or, for any table you can set that same option to DISABLE, though you would have to be very careful about that. See ALTER TABLE for details.

Here is some documentation that talks about Lock Escalation and the thresholds: Lock Escalation (it says is applies to "SQL Server 2008 R2 and higher versions"). And here is a blog post that deals with detecting and fixing lock escalation: Locking in Microsoft SQL Server (Part 12 – Lock Escalation).

Unrelated to the exact question, but related to the query in the question, there are a few improvements that could be made here (or at least it seems that way just from looking at it):

For your loop, doing WHILE (@@ROWCOUNT = @CHUNK_SIZE) is slightly better since if the number of rows updated on the last iteration is less than the amount requested to UPDATE, then there is no work left to do.
If the deleted field is a BIT datatype, then isn't that value determined by whether or not deletedDate is 2000-01-01? Why do you need both?
If these two fields are new and you added them as NULL so it could be an online / non-blocking operation and are now wanting to update them to their "default" value, then that wasn't necessary. Starting in SQL Server 2012 (Enterprise Edition only), adding NOT NULL columns that have a DEFAULT constraint are non-blocking operations as long as the value of the DEFAULT is a constant. So if you aren't using the fields yet, just drop and re-add as NOT NULL and with a DEFAULT constraint.
If no other process is updating these fields while you are doing this UPDATE, then it would be faster if you queued the records that you wanted to update and then just work off that queue. There is a performance hit in the current method as you have to re-query the table each time to get the set that needs to be changed. Instead, you could do the following which only scans the table once on those two fields and then issues only very targeted UPDATE statements. There is also no penalty from stopping the process at any time and starting it later since the initial population of the queue will simply find the records left to update.

Create a temporary table (#FullSet) that just has the key fields from the clustered index in it.
Create a second temporary table (#CurrentSet) of that same structure.
insert into #FullSet via SELECT TOP(n) KeyField1, KeyField2 FROM [huge-table] where deleted is null or deletedDate is null;

The TOP(n) is in there due to the size of the table. With 100 Million rows in the table, you don't really need to populate the queue table with that entire set of keys, especially if you plan on stopping the process every so often and restarting it later. So maybe set n to 1 million and let that run through to completion. You can always schedule this in a SQL Agent job that runs the set of 1 million (or maybe even less) and then waits for the next scheduled time to pick up again. You can then schedule to run every 20 minutes so there will be some enforced breathing room between sets of n, but it will still finish the entire process unattended. Then just have the job delete itself when there is nothing more to do :-).
in a loop, do:

Populate the current batch via something like DELETE TOP (4995) FROM #FullSet OUTPUT Deleted.KeyField INTO #CurrentSet (KeyField);
IF (@@ROWCOUNT = 0) BREAK;
Do the UPDATE using something like: UPDATE ht SET ht.deleted = 0, ht.deletedDate='2000-01-01' FROM [huge-table] ht INNER JOIN #CurrentSet cs ON cs.KeyField = ht.KeyField;
Clear out the current set: TRUNCATE TABLE #CurrentSet;

In some cases it helps to add a Filtered Index to assist the SELECT that feeds into the #FullSet temp table. Here are some considerations related to adding such an index:

The WHERE condition should match the WHERE condition of your query, hence WHERE deleted is null or deletedDate is null
At the beginning of the process, most rows will match your WHERE condition, so an index isn't that helpful. You might want to wait until somewhere around the 50% mark before adding this. Of course, how much it helps and when it is best to add the index vary due to several factors, so it is a bit of trial and error.
You might have to manually UPDATE STATS and/or REBUILD the index to keep it up to date since the base data is changing quite frequently
Be sure to keep in mind that the index, while helping the SELECT, will hurt the UPDATE since it is another object that must be updated during that operation, hence more I/O. This plays into both using a Filtered Index (which shrinks as you update rows since fewer rows match the filter), and waiting a little while to add the index (if it's not going to be super helpful in the beginning, then no reason to incur the additional I/O).

UPDATE: Please see my answer to a question that is related to this question for the full implementation of what is suggested above, including a mechanism to track status and cancel cleanly: sql server: updating fields on huge table in small chunks: how to get progress/status?


Is possible to reuse a already calculatesd column into a query in MySQL?

You can use user-defined variable.

SELECT @temp := ( /* Complex query */ ) AS myValue,
       IF ( @temp > 10, "OK" , "" )
FROM table;

Maybe server demands preliminary variable definition. If so,

SELECT @temp := ( /* Complex query */ ) AS myValue,
       IF ( @temp > 10, "OK" , "" )
FROM table, (SELECT @temp := 0) dummy;

The documentation do not specified the order of output values calculations, but in practice it always matched the order they are written.


Is there a way to reduce the amount of time spent on a point in time recovery?

Yes, you would have to restore the full backup, at maximum one diff backup, and all necessary log backups to perform a point-in-time recovery in SQL Server. In SQL Server, you cannot roll back already committed transactions to go back in time. You would likely want to use the STOPAT syntax to restore your database to the exact desired point in time.

In fact, because you are doing transaction log backups every 30 minutes, SQL Server is also clearing out the transaction log with each log backup--so to go back to an hour prior, SQL Server wouldn't even have the necessary information to roll back that far, even if SQL Server had such a feature (which it doesn't, sadly).

I can't answer whether it would take a "very long time" because restore time varies quite a bit based on how large the database is, how many changes are contained in the log / diff backups, and how many log backups need to be restored. For a small database that isn't very busy, the restore could be quite fast, but for a very busy multi-terabyte database, it could take a while.

Oracle has a feature called Flashback that will let you do what you describe, but SQL Server doesn't have an equivalent feature.


Disable Journaling in MongoDB + WiredTiger

When running a journal disabled cluster, we observed frequent disk checkouts - once about every 3 seconds. This was confusing, as the docs specifically mention that checkpoints are created every 60 seconds.

After digging around, we found the following comment on mongo's Jira:

  ... it is expected that the primary node executes a checkpoint whenever there are multiple write threads as this checkpoint ensures that writes to the replica set are durable

This means that when journaling is disabled, and there are multiple threads writing to the DB, checkpoints become frequent and slow down the entire instance.

This, of course, was a sufficient reason for us to turn on journaling again.


Rolling Back Stored Procedures in Entity Framework

The rollback will rollback all opening (active) transaction. In case using Entity Framework, EF always auto create an root (outermost) transaction, so that the rollback inside a stored proc will rollback the transaction created by EF as well and it will cause error.

There is a way to get over, using Save point -> see https://msdn.microsoft.com/en-us/library/ms188378.aspx

I've applied the template save point for EF and it works well.


If you are still running RTM (or SP1 or SP2 I guess), SP3 was released recently - give that a try and see if it helps fix the Configuration Manager issue.

(Applying a service pack has helped others in a similar scenario.)


Which one is created first? Database or Instance?

Introduction to the Oracle Database Instance

A database instance is a set of memory structures that manage database
files. A database is a set of physical files on disk created by the
CREATE DATABASE statement. The instance manages its associated data
and serves the users of the database.
Every running Oracle database is associated with at least one Oracle
database instance. Because an instance exists in memory and a database
exists on disk, an instance can exist without a database and a
database can exist without an instance.

A database can exist without an instance, but to use that database, you need to start an instance, then mount and open the database in it.
You can "create" a database by simply copying the files of an existing database. You do not need an instance for that. But to use that database, you need an instance.
If you want to create a database from scratch, you need a running instance first.


Unable to access OEM 11g after upgrading to Firefox ver 37.0.1

Quick workaround: Set Firefox's about:config setting security.tls.version.fallback-limit to '0'.

The correct way to fix this is to get the site to updated.

Source: http://forums.mozillazine.org/viewtopic.php?f=37&t=2925119&start=15


Why does PostgreSQL allow NULLs in domains that prohibit NULL?

Well, for starters look at this,
CREATE DOMAIN mydomain AS int;

CREATE TABLE foo(bar) AS SELECT 42::mydomain;

SELECT f1.bar AS f1, f2.bar AS f2, pg_typeof(f1.bar), pg_typeof(f2.bar)
FROM foo AS f1
LEFT JOIN foo AS f2
  ON false;

That currently returns,
 f1 | f2 | pg_typeof | pg_typeof
----+----+-----------+-----------
 42 |    | mydomain  | mydomain

But what should it return? You're joining two tables and the only type in that table is mydomain which allows NULLs. Logically both columns returned should be of type mydomain. Now if you go back and run this,
CREATE DOMAIN mydomain AS int NOT NULL;

Should that return something different given the above commands? The answer is no -- for the same reason. The resultset is composed of two columns sourced from tables. If this is to return the same types as the source tables, like the above query, then it must return, however counter intuitively, a resultset with NULL. Note this isn't about INSERT it's just showing you that no matter what the type SQL can make it return NULL. That's how you get NULLs into a DOMAIN when the domain itself does not support NULL.
Possible Solutions
Four-valued Logic
How can you solve this the right way? You could graduate from Three-valued_logic (3vl) (which is true/false/null), to 4vl (which would be something like true/false/null/unknown). This would give SQL a primitive not available to the user that EVERYTHING would be parameterized over. It would look like,
// Definitions                              // Value Constructors on

// Two-value
type Bool = True | False;                   // True | False

// Three-value
type NonNullable<T> = T;                    // True | False
type Nullable<T> = Null | NonNullablle<T>;  // Null | True | False

// Four-value!
type Option<T> = Unknown | Nullable<T>;     // So over every column
type Option<T> = Unknown | NonNullable<T>;  // you can store Unknown

And, then every column returned by SQL as a result of a SELECT could be Option-ally Unknown a value the user could not choose and that every type must permit. That would solve this problem. It would also make SQL far harder to teach and grok. Imagine a four-value truth table.
Fatal SELECT
From Paul White another solution would be to make SELECT statements throw a fatal error if the value can't be coerced to or represented by the domain. Some SELECT statements already throw an error, like SELECT 1/0 or SELECT CAST('foo' AS int);

Self-answered to preserve this conversation in chat


How to conditionally create JSON array in postgres with group by clause?

Use conditional aggregation to get the sums depending on is_tag_stock and jsonb_build_object() (or json_build_object()) to create the encapsulating JSON.
SELECT cat_id,
       cat_name,
       jsonb_build_object('piece',
                          sum(CASE
                                WHEN is_tag_stock THEN
                                  piece
                              ELSE
                                0
                              END)) tagged,
       jsonb_build_object('piece',
                          sum(CASE
                                WHEN NOT is_tag_stock THEN
                                  piece
                                ELSE
                                  0
                             END)) untagged
       FROM elbat
       GROUP BY cat_id,
                cat_name;

db<>fiddle


Read ancient Oracle database into current Oracle version

Oracle 8.x.x has been out of support for some time.

Apologies for the bad news, but your only option is to either recover the software from the dead disk, or ask Oracle (obviously with a valid support contract) for a copy of the software. Raise a ticket on My Oracle Support and they will help you.


Disk space is cheap... that's not the point!

Stop thinking in terms of storage space, think instead about buffer pool and storage bandwidth. At the extreme end, CPU cache and memory bus bandwidth. The linked article is part of the series highlighting issues with poor clustered key selection (INT vs GUID vs Sequential GUID) but it highlights the difference bytes can make.

The overriding message is design matters. The difference won't show up in an individual database on an appropriately spec'd server until you hit VLDB territory but if you can save a few bytes, why not do so.

I'm reminded of the environment described in an earlier question. 400+ databases, ranging in size from 50mb-50GB, per SQL instance. Scrubbing a few bytes per record, per table, per database across that environment could make a significant difference.


What happen when a transaction is committed and SNAPSHOT isolation is enabled?

SQL Server will store the "before image" of the values in tempdb. And if you modify the same row several times, then you will have a chain of before-values (a linked list, essentially). The row also has information about a "logical timestamp" (so to speak) for when it is valid. This information is used by SQL Server to determine what data a SELECT should be used (as you might know, the true snapshot isolation gives us repeatable read - so a reader in the same transaction might still need to see the prior version even after the modifier did commit).

Then there is a clean-up process (one per minute, i believe) that sweeps the no longer needed versioned data and removes it from tempdb (aka "version store").

I.e., no need to copy anything at commit time, since it was the "real" data that was modified in the first place.


How to check the memory allocator used by my mysql 5.7.20

You can see which shared libaries mysqld has loaded with:

lsof -p $(pidof mysqld) | grep mem

If its using jemalloc it should be listed.


I cannot say why your daily transaction log backups are large - it is usually down to user activity on the database and SQL Agent maintenance jobs such as ones that use DBCC CHECKDB and re-indexing.
WITH RETAINDAYS will not delete your previous backups, it will prevent overwrites of the backup file when using the INIT option.  You will still need to run a job to clear out the old backup files.
Here's one that Ola Hallengren's scripts add :-
    DECLARE @CleanupDate datetime
    SET @CleanupDate = DATEADD(dd,-28,GETDATE())
    EXECUTE dbo.sp_purge_jobhistory @oldest_date = @CleanupDate


How to run INSERT INTO AS SELECT in parallel on Oracle?

You should use direct path INSERT.
For select you can use PARALLEL hint in the same statement.  you may use PARALLEL without any parameter or use PARALLEL(n) with n number of thread.
Let me know if you need more explanation on this.


Minimizing downtime during MariaDB Replication Setup

Use https://github.com/alestic/ec2-consistent-snapshot of your EBS volume using --mysql-master-status-file FILE option.

Create a new volume from that snapshot and attach to new instance or another instance you can use to copy to a remote location if needed.. The db server will only be locked for a couple seconds. I just performed this operation on a server with a 2TB server with millions of tables.

Start the slave db with data files from other server reset the slave and change your master with the correct log position.


There is a difference between redundant data (bad) and coincidentally repeated data (not bad).  Normalization is a technique which is used to avoid insert, update and delete anomalies.  It is not meant to eliminate every repetition of a piece of data.  Data which is static doesn't benefit from normalization.

A unit of measure, stated as a standardized abbreviation is not the kind of data that you need to normalize out.

Think of it this way:  If you were to normalize out your unit of measure into a separate table, you'd need a foreign key from your nutrients table to your units table.  Is the unit of measure code going to be unique (probably, yes).  Therefore it's a candidate key for your units table.  If it's a candidate key in units you could use it as a foreign key in nutrients.

The end result is that you have your unit of measure code in your nutrients table anyway even if you've normalized out the units.

Here's when you would want to create a units table.  If you have other predicates (columns) that are dependent on the unit code, but not on the nutrient.  For example, a unit_type or a conversion factor to a base unit of the same type (grams for weight, etc.)  This would be a transitive functional dependency in your nutrients table and doesn't belong there for that reason.


Computed columns are your friend.  Use them to capture simple calculations you're going to do anyway, and to guarantee that the computed values are correct.
Persist the results if you want to index or filter on them; don't persist them if you just need to pull the value once in a while.
Capture all data constraints using CHECK constraints.

Here is a pseudo-schema definition:

  id               PRIMARY KEY
, truck_num

, start_date
, end_date         CHECK (end_date > start_date)

, start_miles      CHECK (start_miles >= 0)
, end_miles        CHECK (end_miles > start_miles)

                   -- what if they refill the tank?
, start_gals       CHECK (start_gals >= 0)
, end_gals         CHECK (end_gals < start_gals AND end_gals >= 0)

-- all these should be computed
, miles = end_miles - start_miles
, gals = start_gals - end_gals
, mpg = miles/gals

As for indexing the table, here's what I think:

Cluster on start_date ASC. This will satisfy your WHERE clause. You are always inserting data by increasing start_date, meaning your writes will always be sequential under this clustering scheme.  You are also always querying by start_date so you satisfy your biggest query pattern as well. (3,000 inserts per week is nothing. Because you have such a low volume of inserts, you could even cluster on start_date ASC, end_date ASC.)
Create a non-clustered index on truck_num and INCLUDE mpg. This should satisfy your SELECT, JOIN, and GROUP BY clauses. If you want to ORDER BY mpg, then make mpg part of the index key after truck_num instead of just INCLUDE-ing it.

When you're done, test your setup as follows:

Create a test table
Pump it full of test data
Create the indexes
Update statistics
Run your most common queries
Check their plans and run times


Finding how many times a value is shown on a table - Postgresql

Test

SELECT employee.employee_id,
       COUNT(DISTINCT pr.subscription_type) AS types_count
FROM employee
INNER JOIN person p USING(email)
INNER JOIN order o USING(email)
INNER JOIN product pr ON o.product_id = pr.product_id
-- INNER JOIN subscription s ON pr.subscription_type = s.subscription_type
GROUP BY employee.employee_id
ORDER BY employee_id


What does it mean by '4M blocks' or '4G blocks' in Oracle?

In this case 4G and 4M aren't quite referring to what I think you mean by the unit of digital storage; it isn't referring to 4 gigabytes and 4 megabytes. It's just a number, not a unit.

Oracle has to keep track of the blocks in the tablespace, and appears to be limited to a 32-bit 'address' space (2^32 = 4,294,967,296 = 4G). So it can internally reference up to 4G blocks of data. The distinction between bigfile and smallfile is that bigfile can reference one huge file with all 4G blocks in it, which smallfile can reference up to 1022 files with up to 4M blocks in each. (There seems to be some overhead somewhere, since it's 1022 instead of 1024). The total number of blocks ends up being (almost) the same, they're just organised differently.

So far this has little to do with how much actual data that is. This is where the database block size comes in. If you have a 4KB (4096 byte) block size, a bigfile can be up to 4G x 4096 bytes, or 16TB; while with smallfiles each file could be up to 4M x 4096 bytes, or 16GB. As you can have 1022 files, the total available storage is (almost) the same. If your database blocksize is twice as big, 8KB, the bigfile can also be twice as big, up to 32TB; and each smallfile can similarly be up to 32GB.

The bigfile documentation may help explain it better than I can. (Though confusingly that uses '32K' to mean 32 kilobytes, which is normally clear but when also talking about 4G blocks it might have been better to use '32KB'... I'm not opining on which is 'correct'!)


You have the verbosity set to 0 overall and every individual component is set to -1, which means they will all inherit the parent verbosity setting (of 0). That is as low as the verbosity setting goes, to further reduce the logging you can set systemLog.quiet but that is not recommended for production systems because it can make tracking down issues too difficult and it may not address the root cause in any case.

You mention that the reason you are looking into this is the volume of logs you are seeing, so I would suggest the best thing to do would be to figure out what is causing the excessive logging (slow queries? something else?) at the lowest verbosity level and address that.

Note that if it is slow queries (and you are OK with them being that slow) you may just want to adjust the default 100ms operationProfiling.slowOpThresholdMs threshold upward to reduce that logging specifically.


When a database is detached, it should shut down cleanly. This file has been shadow copied or has been taken from a crashed SQL Server Instance or such. That is, the MDF is not from a cleanly detached, shutdown database.

So, possible solutions..

I don't think this will work thought but please check. Try SP_ATTACH_SINGLE_FILE_DB from a query window. It is clearer than the SSMS GUI attach pop-up. Note, this is deprecated in favour of CREATE DATABASE .. FOR ATTACH_REBUILD_LOG which states as a requirement: A clean shutdown of the database.
But I'm sure that you'll have to go back and state you have a corrupted file.
Better still, ask for a BACKUP which will be smaller and consistent.
If your source says they can attach it, they'll be lying or losing data.
Looking at SO and SF, there appears to one approach that will cause data loss. You can find that for yourself: I don't endorse it.


InnoDB row metadata size (minimum row size)

Each row in InnoDB (let assume COMPACT format) has these headers:

Offsets - one or two byte per variable length type field.
NULL flags - one bit per NULL-able field, aligned to a whole number of bytes.
So called "Extra bytes" - a bunch of flags like is_deleted, pointer to next record ect. Five bytes in COMPACT format, fixed.

Then comes primary key field(s).
Then 6 bytes transaction id and 7 bytes rollback pointer.
Then remaining field values.

For the given row (BIGINT, TINYINT, TINYINT) it's size will be:

0 bytes offsets
0 bytes NULL flags
5 bytes "Extra bytes"
8 bytes BIGINT
6 bytes trx_id
7 bytes roll_ptr
1 byte TINYINT
1 byte TINYINT

So, 28 bytes per record.

But to calculate how much space the table will take you need to take into account:

PAGE header (38+56 bytes)
page trailer
page fill factor
infinum, supremum records
space used by non-leaf pages

Check these slides for more details
http://www.slideshare.net/akuzminsky/data-recovery-talk-on-pluk


High CPU usage by GHOST CLEANUP after DROP FK and DELETE

Why does disabling FK and deleting table's content triggers GHOST
  CLEANUP and just deleting table or truncating table does not?

From your question it looks like you understand ghost records and the ghost cleanup process but I'll summarise for future readers.

When a row is deleted, it isn't physically removed from the page. Instead, it is marked as ghosted and a background task (ghost cleanup) comes along later and removes the records entry from the page slot array (note the data isn't overwritten or zero'd out).

When you TRUNCATE or DROP the table records aren't deleted/ghosted individually, entire pages are de-allocated. See the two excellent answers from @NickChammas and @MartinSmith describing the mechanics of drop and truncate in Why use both TRUNCATE and DROP?

That's why you aren't seeing the ghost cleanup task working over time following a TRUNCATE or DROP.

  Why does cleanup with disabled/dropped FK takes way longer than same
  cleanup with FK enabled?

If you've stated the sequence of events accurately, this process is not what is triggering the ghost cleanup process. The ghost cleanup task doesn't start removing deleted records until the transaction that deleted them commits (it can't as they're locked).

So, either something else is at work or you've encountered a bug. KB978430 could apply, if that doesn't work I'd be inclined to log a support call.


Database Log Reuse Wait shows AVAILABILITY_REPLICA but database is no longer part of an availability group

Take your database offline then back online. This should clear the AVAILABILITY_REPLICA wait, then you can shrink whatever you need to shrink.


How to query closest date and display the specific data

Try this trick

SELECT cte.landing_id,
       cte.percentage,
       cte.date_added,
       cte.address
FROM (  SELECT fish_landing.landing_id,
               percentage.percentage,
               percentage.date_added,
               fish_landing.address,
               (@row_number:=CASE WHEN @landing_id=fish_landing.landing_id THEN @row_number+1 ELSE 1 END) rn,
               (@landing_id:=fish_landing.landing_id)
        FROM (SELECT @landing_id:=0, @row_number:=0) vars,
                  fish_landing
        LEFT JOIN percentage ON percentage.landing_id = fish_landing.landing_id
        LEFT JOIN ( SELECT landing_id, MAX(date_added) AS max_date_added
                    FROM percentage
                    GROUP BY landing_id) AS dt
            ON dt.landing_id = percentage.landing_id
            AND dt.max_date_added = percentage.date_added
        ORDER BY fish_landing.landing_id ASC, percentage.date_added DESC
     ) cte
WHERE cte.rn = 1
ORDER BY cte.landing_id ASC;


Postgresql update table within trigger: duplicate key value violates unique constraint

Thanks @a_horse_with_no_name. The DEFERRED was the trick.

I was able to solve this issue changing the trigger function to:

CREATE OR REPLACE FUNCTION history() RETURNS TRIGGER AS $$
BEGIN
    SET CONSTRAINTS ALL DEFERRED;
    UPDATE machine SET seq = seq-1 where id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;


How to set up SQL Server 2017 as a service

Revoking that one view was never a good way to protect your instance.  The most straightforward way to do this is to make the user the owner of their database, but have no other rights on the instance, even to list the other databases.  EG:

use master
create database a
create login a_user with password='P@sssword*****'
alter authorization on database::a to a_user

revoke view any database to public

Now a_user will be able to connect (with SSMS or otherwise) and will see only one database.


Postgresql: compare record against sibling, but limit search only to active records

Your best (or simplest) choice may be using DISTINCT ON:

WITH latest_prices AS (
  SELECT DISTINCT ON (price_sheet_num, item_id) *
  FROM prices
  ORDER BY price_sheet_num, item_id, created_at DESC
  )
SELECT lpr.*, s.reg_price_sheet_num, s.bulk_price_sheet_num
FROM category_price_sheets s
JOIN latest_prices lpr ON (lpr.price_sheet_num = s.reg_price_sheet_num)
JOIN latest_prices lpb ON (lpb.price_sheet_num = s.bulk_price_sheet_num)
WHERE lpb.item_id = lpr.item_id AND lpb.cost > lpr.cost;

Ensure first there's an index for prices ON (price_sheet_num, item_id, created_at), or better still, prices ON (price_sheet_num, item_id, created_at DESC);

If this doesn't preform well enough, you may benefit from creating a temporary table:

CREATE TEMP TABLE latest_prices (LIKE prices) ON COMMIT DROP;
INSERT INTO latest_prices
SELECT DISTINCT ON (price_sheet_num, item_id) *
FROM prices
ORDER BY price_sheet_num, item_id, created_at DESC;
CREATE UNIQUE INDEX ON latest_prices (price_sheet_num, item_id);

SELECT lpr.*, s.reg_price_sheet_num, s.bulk_price_sheet_num
FROM category_price_sheets s
JOIN latest_prices lpr ON (lpr.price_sheet_num = s.reg_price_sheet_num)
JOIN latest_prices lpb ON (lpb.price_sheet_num = s.bulk_price_sheet_num)
WHERE lpb.item_id = lpr.item_id AND lpb.cost > lpr.cost;


SQL SERVER availability groups and virtualized HA

It depends on what you mean by "lose a VM."  If you have a hardware failure and the VM is effectively powered off, a failover will occur if the failed VM was the primary.  When the failed server is moved and rebooted, it will join the cluster before trying to become primary again, and when it does it will discover that the other node is primary, and it then resolves its own state to secondary.

If there is no power off situation, but the VM disappears from the network for several seconds--this can be bad as it can cause failover to occur, but since the VM that is moving is effectively paused, it does rejoin the cluster to resolve its role--it just picks up where it left off.  The cluster will quickly detect that there are two primaries and the VM that moved will be forced into a secondary role, but if there was any data committed from the transaction logs, you can end up with the databases out of sync.  If this happens, you have to remove the replica database that's out of sync, restore from backup, etc., and then join it back in.  See How to remove a secondary database from an availability group and rejoin it.

Another thing with VMs that can result in this mess is VM snapshot backups, or VMotioning--anything that can cause the server to be paused.  For servers with 100 GB storage--usually not a problem.  But when you start getting into terrabytes, the server can be paused for several seconds to do these operations.  So you may need to adjust your backup strategy to accomodate--see this for more.


Storing in mysql table should be fine. You need to consider how the data is going to be accessed and retrieved when you're designing the table and indexes on it. I'd say that you're likely to be doing queries which return all of the past searches for a particular user (or perhaps even just the most recent by user) so it would probably make sense to cluster the table by UserID and SearchDate. This design should also help reduce contention if lots of inserts are happening at the same time since each user will be inserting into a different part of the table.


What does "*password------------" mean in a profile deadlock report?

It just means that the text of the statement contained the string "password" and SQL Server "helpfully" has masked it as a security feature to prevent you seeing some one else's password.

I was able to reproduce this as follows

CREATE TABLE T(X varchar(1000))

Connection 1

BEGIN TRAN

INSERT INTO T VALUES('password1')

WAITFOR DELAY '00:01:00'

SELECT * FROM T WHERE X = 'password2'

ROLLBACK

Connection 2

BEGIN TRAN

INSERT INTO T VALUES('password2')

WAITFOR DELAY '00:01:00'

SELECT * FROM T WHERE X = 'password1'

ROLLBACK

Then retrieving the graph from the extended events trace


Connectivity to SQL Server on Azure Error: 121 - The semaphore timeout period has expired

try to connect choosing the proper user database here:

Maybe the user you are using is not able to connect to the master database.
Here is how database firewall rules work:
https://docs.microsoft.com/it-it/azure/azure-sql/database/firewall-configure


Securely manage and dynamically create multiple databases

It seems like a limiting factor here might be that we don't want to make a connection string for each client and separate the user used to connect to the DB back end that way.  As a result we are using a SQL Database user that has full access to all the databases, with them really only needing access to 1 DB that being their own.  In the current security model, if a SQL injection attack were to occur, they would be be able to have access to all databases, instead of just the 1 user database they need due to the connection string.

To resolve this issue you're proposing that you create a database to hold customer information such as login ID and password to their SQL Server database user which has access to read/write/execute, and want to know if it provides any benefits over the current model.

You could do as you proposed by having a user that can only accept sanitized and paramterized stored procedure queries with being granted only execute permissions on the master users/login table.  Once you get that value based on your uniqifier (be it ID, company name, etc.), you are mostly protected from a SQL injection attack.  An extra level of defense would be to encrypt the value within the DB and have the app decrypt the username and password before passing it in.  This would fully protect you from someone getting your username/password from SQL Injection even if they did manage to pull out the raw value from the DB engine.  They would have to also get the decrypting private key off the web or key server before they could read the credentials needed to access the databases.

By doing this you are at least protecting your company from having someone make a change on every single DB in the enterprise if they were to break in, and they would be limited to just messing up 1 customer; unless of course they got your private keys or the decrypted password list some other way.

This is of course just a quick review of what you proposed.  There are tons of different ways to do security and this might not be the most ideal in your scenario, I don't know what would be best from the limited info, but it would work to answer your question and give you that extra line of protection of encryption, multiple places they'd have to break in, and separate users owning each DB which is always nice.

SQL 2016 has some great new security enhancements you could utilize too if you want such as always encrypted data and saving your keys elsewhere if you wish to take this to the next step.


Use binary (or some integer as binary) or SET generated encoding column. For example:

CREATE TABLE tablename
( id SERIAL PRIMARY KEY,
  linkfield  VARCHAR(255),
  textfield TEXT,
  datefield DATE,
  lat DECIMAL(10,8),
  long DECIMAL(10,8),
  type ENUM('value1', 'value2', ... , 'valueN'),
  record_type_mask TINYINT UNSIGNED
                   AS ( (linkfield  IS NOT NULL) *  1 +
                        (textfield  IS NOT NULL) *  2 +
                        (datefield  IS NOT NULL) *  4 +
                        (lat + long IS NOT NULL) *  8 +
                        (type       IS NOT NULL) * 16
                      )
);

then use either constant values.

For example, if you want to select

  If a record has a value in date & lat & long it is considered an “event”

you will use

WHERE record_type_mask = 12

because the value 4 marks present lat and long, and the value 8 marks datefield.

If you need records which have datefield set you may use masking:

WHERE record_type_mask & 4
-- or
WHERE record_type_mask IN (4,5,6,7,12,13,14,15,20,21,22,23,28,29,30,31)


Changing database name based on project environment

You can use variables/parameters in your SSIS package to dynamically build your queries, probably the easiest method. It might be tedious to get setup based on how many queries you have used everywhere in your package. Just have something like an "User::Environment" variable that holds either: _DEV, _QA or _PROD. You then join that into your variable holding your query and then as you change that value it would query the appropriate table.

You could base it on the server you are connecting to as well, but would be the same premise of having to configure your queries dynamically in a variable + expression setup. I would probably use above method over validating against server I'm connecting to, just to have more precise control.


Database designers would definitely advise you to use proper data format and built-in date calculation functions


Optimize MySQL insertion - insert multiple records with condition (per record)

IODKU works well with SELECT:
INSERT INTO t
    (unique_key, counter, time)
    ON DUPLICATE KEY UPDATE
        counter = VALUES(counter) + 1,
        time = NOW()
  SELECT unique_key, 1, NOW()
      FROM ...

Notes:

Need unique key (or set of columns)
See how a counter can be updated


Select values from 2 tables with same properties

DECLARE @sql NVARCHAR(MAX) = N'SELECT ';

SELECT @sql += N'
  ' + QUOTENAME(c.name)
  + ' = COALESCE(es.' + QUOTENAME(c.name)
       + ', s.' + QUOTENAME(c.name) + '),'
FROM sys.columns AS c
INNER JOIN sys.columns AS c2
ON c.name = c2.name
AND c.[object_id] = OBJECT_ID('dbo.SETTINGS')
AND c2.[object_id] = OBJECT_ID('dbo.EXTENDED_SETTINGS')
AND c.name NOT IN (N'name', N'ID' /* , ... potentially others ... */);

SET @sql += N' s.name, s.ID';

SELECT @sql += N',
  s.' + QUOTENAME(c.name)
  FROM sys.columns AS c
  WHERE [object_id] = OBJECT_ID('dbo.SETTINGS')
  AND name NOT IN (N'name', N'ID')
  AND NOT EXISTS
  (
    SELECT 1 FROM sys.columns AS c2
      WHERE c2.[object_id] = OBJECT_ID('dbo.EXTENDED_SETTINGS')
      AND c2.name = c.name
  );

SET @sql += N'
  FROM dbo.SETTINGS AS s
  INNER JOIN dbo.EXTENDED_SETTINGS AS es
  ON s.ID = es.SETTINGS_ID
  WHERE es.ID = @extSetId AND s.ID = @settID;';

PRINT @sql;
--EXEC sp_executesql @sql;

Please use table aliases and schema prefixes, and here is why I prefer COALESCE over ISNULL. And if those tables really aren't stored as upper case in a case sensitive collation, do you really need all caps? Also, ID is a horrible and ambiguous name for a column. It should be the same name across the schema; always describing exactly what it is - especially when you're doing lazy dynamic things like this.


How to display name on a column chart itself in SSRS, not on a legends

I believe you cannot do that. But, I came across something similar answered on the forum already. You can achieve a similar effect by following the steps in the link below.
Please go through the below link and check if it might be useful to you.
https://stackoverflow.com/a/9503789/2818351
Credits to the original author who answered the question.


SQL Server stored procedure working when run manually, not running from SQL Server Agent

line 1, character 38, unable to switch the encoding

I ran in to this a while back sql server wants the source string type to match the utf code.

    SELECT CAST('<?xml version = "1.0" encoding = "utf-8"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST('<?xml version = "1.0" encoding = "utf-16"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST(N'<?xml version = "1.0" encoding = "utf-8"?><nobody> </nobody>' AS XML)
    GO
    SELECT CAST(N'<?xml version = "1.0" encoding = "utf-16"?><nobody> </nobody>' AS XML)
    GO

You should see only the first and forth query have results. This is where the xml declared utf-"bits per character" matches the sql datatype.

  line 9, character 9, unexpected end of input

I would run this problem from a different direction.

have the procedure output the received xml
check for permissions issues with the service accounts (difference between sql server and agent services)


Why does Postgresql tell me the database does not exist?

You need to double quote the name. In PostgreSQL we never double quote names. If you follow the convention, PostgreSQL will convert the unquoted identifier to lower case. In your case, that which created the db used double quotes.

Now you have a capital letter in the identifier.

From now on you have to double quote the identifier.

Not,

postgres=# DROP DATABASE RoutingApp_development;

But,

postgres=# DROP DATABASE "RoutingApp_development";


Using IF in T-SQL weakens or breaks execution plan caching?

SQL Server optimises the process of compilation of the query plan for the stored procedure by ignoring the conditional branches inside the stored procedure.  The plan will be generated based on the parameters used for the first execution, this will cause problems if the parameters are different for the branches.

I would place the SQL for each of the branches into their own stored procedure, so that the plan generated is based on the actual usage of parameters for that branch.


Is it Possible to perform Restore on Secondary Replica using Copy_Only Backup that was taken on the same node?

When trying to add a secondary replica to an AG, it needs to be sufficiently close to the primary that whatever transactions are in the active portion of the primary's transaction log are enough to bring the secondary up to current.
Let's say for the sake of argument that your copy only backup is taken once a day at midnight (00:00) and you're starting the process of adding the replica the next day at 09:00 (i.e. nine hours have elapsed since the backup was taken). Let's say for the sake of argument that the restore process was instantaneous (it won't be, but we'll get to that in a moment). Once that restore is complete, you have a database that is consistent with the primary as it was at 00:00. You now need to apply transaction log backups in order to cover the intervening nine hours. Let's say for the sake of argument that those also take no time to restore. Now the primary and secondary are consistent with each other and you should be able to join the secondary replica to the AG.
In the above scenario, all of the restores took zero time. In reality, that's not the case. As you're doing restores on the secondary, your primary database is also taking traffic. It's not a big deal, you just need to apply more transaction log to cover the intervening time. Making the above scenario slightly more realistic, let's say that the restore of the full backup takes 2h. So now it's 11:00 and you start restoring all the transaction logs you have from midnight onward. Let's say that that process takes an hour. Now it's 12:00 and your secondary is restored up to 11:00. You kick off another round of transaction log restores to cover 11:00-12:00. Et cetera until your secondary is sufficiently close to the primary that it can make up the difference with the active portion of the log.
How will you know? Two things. First, if you try to add the secondary and it's not close enough, you'll get the error message you got in re: The remote copy of database "AGTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.'. Second, if there are any t-log backups of the primary that you haven't restored to the secondary, you'll need to restore them.
Clearing up one last thing - the reason you need to do all of the above restores (not just the full backup, the t-log backups as well) using the with norecovery clause is that that's what allows more restores to be done on top of the resulting database. That clause does exactly what it says - it skips the portion of the restore process that runs through so-called crash recovery (which is the process that does roll forwards and roll backs of transactions so that the resulting database is transactionally consistent with the backup).


guideline for calling optimize table after alter table?

No, you should not do OPTIMIZE TABLE because it is the same as

ALTER TABLE tblname ENGINE=WhateverTheStorageEngineIs;
ANALYZE TABLE tblname;

Thus, doing an ALTER TABLE tblname ... ;of any kind followed byOPTIMIZE TABLE tblname;` would create two temp tables.

BTW when you do OPTIMIZE TABLE tblname; on an InnoDB Table you get this

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

So, never use OPTIMIZE TABLE. You could run ANALYZE TABLE tblname; instead.

Changing datatypes of columns ? If and only if there is the possibility of value truncation, you could run ANALYZE TABLE tblname; during off-hours.

Dropping Indexes ? No need to since dropping an index remove all index stats for that index, leaving other indexes alone.


Restore a point between two full backups

You can do a point in time recovery to Oct 5th if you have only full backups and a last transction log backup (called a tail log backup).
Your transaction log backup contains all transactions back to the last log backup. So that should be sufficient to do a point in time recovery.

That you are getting the message "transaction log does not contain the point" indicates one of the following:

There were other transaction log backups taken before your last transaction log backup.
The database was in Simple recovery mode somewhere between the 1st and the 23rd of October.

The reason your transaction log is so big is that it was not backed up regurarly. As transactions happen, the log grows. A transaction log backup truncates your transaction log, and then the empty space can be used for new transactions. The reason can also be that there was a big amount of data modified.

Recommendation: regurarly backup your database (for example every day) and your transaction logs (at least every hour or even more often, depending on the activity in the database). Keep those backups in a safe place. Then you will be able to do proper point in time recovery.


Weak entity and one-one or many-many relationship

If you are running a video rental store, then whether or not your children eat depends very much on a video appearing in as many reservations as possible.

In your example, RESERVATION is a weak entity which relies on both the FK to CUSTOMER and the FK to VIDEO in addition to a further attribute, perhaps date, to comprise its primary key.

It is not possible for a M:N relationship to be supporting for a weak entity (although the intersection table which resolves a M:N relationship can be a weak entity).  This is because a supporting relationship needs to be to a particular entity object.  A set of entity objects can't identify something in relational algebra.

It is possible for a 1:1 relationship to be supporting for a weak entity, although this would be rare "in the wild".  I could imagine it coming up in the context of situations that would typically be 1:M, but where there is a business rule that restricts the child to one at a time.


Methods for right-sizing SQL Server virtualization?

Please try Microsoft Assessment and Planning Toolkit.  This will give you current inventory and workloads.  At least run this tool for 24 hours to collect overall performance.
https://www.microsoft.com/en-us/download/details.aspx?id=7826


First and foremost lets make sure you did due diligence and have determined that is the data file that had grown, not the log. IF the log is growing, read How to shrink the SQL Server log and Factors That Can Delay Log Truncation.

Do you have more that one filegroup, ie aside from PRIMARY, is there another one? SQL Server tables are stored in individual file groups (strictly speaking is partitions of indexes (including index 0 and index 1, better known as the 'table') that are store on individual file groups. If you have more than one filegroup and the table that grows is not in PRIMARY then SQL cannot use the new file for the 'big' table. You need to add the file to the filegroup that has the size problem.

But, before adding a file, I would recommend you investigate how is the space used. Which table has grown and why? Are you wasting space? sp_spaceused will be your friend to investigate this. PErhaps you'll discover that you can reclaim the used space and save you from the troubles of an ever increasing file.


Postgres query to return JSON object keys as array

Sure, with json_object_keys(). This returns a set - unlike the JavaScript function Object.keys(obj) you are referring to, which returns an array. Feed the set to an ARRAY constructor to transform it:

SELECT id, ARRAY(SELECT json_object_keys(obj)) AS keys
FROM   tbl_items;

Or use jsonb_object_keys() for jsonb.

This returns an array of keys per row (not for the whole table).

A more verbose form would be to spell out a LATERAL join instead of the correlated subquery:

SELECT t.id, k.keys
FROM   tbl_items t
LEFT   JOIN LATERAL (SELECT ARRAY(SELECT * FROM json_object_keys(t.obj)) AS keys) k ON true;


How can I figure out when a session opened a transaction to debug a session being blocked by a sleeping session that has an open transaction?

You should be able to use a couple of system views to get you where you want to go:

SELECT [dtat].[transaction_begin_time]
FROM [sys].[dm_tran_active_transactions] AS [dtat]
JOIN [sys].[dm_tran_session_transactions] AS [dtst]
    ON [dtat].[transaction_id] = [dtst].[transaction_id]
WHERE [dtst].[session_id] = @spid


SQL Server 2014: missing replication option

You are likely using the Express Edition.  Note that Express Edition can only act as a Subscriber.  Try using Standard Edition or above to Configure Distribution and Publishing.


SQL Server: Frequent Login timeout expired in Linked server

I am here answering my own question. After so many observations and experiments with searching over the internet, I found solution to this issue. You need to run this simple query

USE Yourdatabase;
GO
EXEC sp_configure 'remote query timeout', 0 ;
GO
RECONFIGURE ;
GO

The setting takes effect immediately without restarting the server.

Please refer Configure the remote query timeout Server Configuration Option and sp_configure.


Can autoshrink permanently damage a database?

No auto shrink can't permanently damage a database. It may lead to highly fragmented tables and indexes which may hurt performance. The fragmentation could be difficult to remove completely, but minor fragmentation itself shouldn't be a concern. You need to assess whether this is really an issue for you worth spending time and effort to fix.


Postgresql trigger update whole table after delete

You can use this simpler / cheaper query for your view or CTE or simply a subquery:

SELECT ser_nr, meas_ser, meas_value
     , row_number() OVER (PARTITION BY ser_nr ORDER BY meas_ser DESC) = 1 AS last_meas
FROM   measurements;

SQL Fiddle.

There are no corner case problems with duplicate or NULL values here while you have that PK constraint you added in the fiddle:

ALTER TABLE measurements ADD CONSTRAINT measurements_pk PRIMARY KEY (ser_nr,meas_ser);

The associated index will also help to make it fast if you only need a small selection of ser_no from a big table.

And yes, this dynamic approach has so much less potential for headache (consistency in the face of concurrent write access!) than trying to keep rows in your base table current.


Confused about load balancing and horizontal scaling in postgresql

First rule of horizontal scaling of a database is to avoid it. At all cost. You should consider it only when no server you can possibly buy can handle your data. And there are servers which can handle enormous amounts of data today.
Horizontal scaling of a database will give you:

at least an order of magnitude more complicated system:

even in a simplest case of sharding you'd have to manage more servers, distribute load evenly, manage schema updates and shared tables in all nodes, make your frontend connect to correct server etc.;
if you use replication instead, then you'd have to manage replication delay, failing queries on a replica, secure transfer of data between primary and replicas, replicas going out of sync, updates have to be run against master etc.;

at least an order of magnitude more bugs, direct consequence of above complications;

at least an order of magnitude harder to debug bugs, direct consequence of above complications;

at least 2 times higher probability of hardware failure - you'd have at least 2 servers, probability of any of them failing is higher than one specific failing;

probably an order of magnitude worse performance than a single server for the same price, as better storage options like battery backed drive controller or SSDs, partitioning your data, smart placement of WAL files, bigger cache etc. would be far more important than buying another, slow server; remember that databases are mostly bottlenecked by storage.

You can scale horizontally clients connecting to a database (frontends are mostly bottlenecked by processor, so this makes sense), you should use replication for failover (so called warm standby server), you can partition your data within the same database (which would get you most of benefits of sharding with far less drawbacks). Just don't do horizontal scaling before you're of a comparable size to Google, Twitter or Facebook. So no 2 small EC2 instances, no.


How to use function parameters in dynamic SQL with EXECUTE?

You are confusing a couple of things. To pass values to EXECUTE, use the USING clause. You don't need format() here.

CREATE OR REPLACE FUNCTION insert_records_for_notification(
        _username text
      , _state text
      , _district text
      , _bloodgroup text
      , _status text
      , _approverejectstatus text
      , _emailsubject text
      , _emailbody text
      , _notificationstatus text)
  RETURNS boolean AS
$func$
DECLARE
   r      moyadev.user%rowtype;
   _where text;
   _sql   text :=
     'INSERT INTO moyadev.notification_email_details (id, youth_enrollment_id, youth_email, email_subject, email_body, status, attempt,sent_date, last_updated_by, last_updated)
      SELECT uuid_generate_v4(), id, email, $7, $8, $9, null, null,$1, now()
      FROM   moyadev.youth_enrollment';
BEGIN
   SELECT * INTO r FROM moyadev.user u WHERE u.user_key = _username;

   _where := concat_ws(' AND '
       , CASE WHEN state               IS NOT NULL THEN 'state = $2'                  END
       , CASE WHEN district            IS NOT NULL THEN 'district = $3'               END
       , CASE WHEN bloodGroup          IS NOT NULL THEN 'bloodgroup = $4'             END
       , CASE WHEN status              IS NOT NULL THEN 'status = $5'                 END
       , CASE WHEN approveRejectStatus IS NOT NULL THEN 'approve_reject_status  = $6' END
       , CASE r.level
            WHEN 'DISTRICT' THEN 'district = $10 AND state = $11 AND fk_id = $12'
            WHEN 'UNIT'     THEN 'district = $10 AND state = $11 AND fk_id = $12'
            WHEN 'STATE'    THEN 'state = $11 AND fk_id = $12'
            WHEN 'NATIONAL' THEN 'fk_id = $12'
         END);

   IF _where <> '' THEN
      _sql := _sql || ' WHERE ' || _where;

      EXECUTE _sql
      USING   $1, $2, $3, $4, $5, $6, $7, $8, $9, r.district, r.state, r.fk_id;
   END IF;

   RAISE NOTICE 'sql: %', _sql;

   RETURN true;  -- boolean!
END
$func$  LANGUAGE plpgsql;

Major points

Do not concatenate parameter values into SQL strings. Very tedious, slow, error-prone and open to SQL injection. Instead pass values to EXECUTE with the USING clause. Related:

SQL injection in Postgres functions vs prepared queries

I removed unused variable id int; and the unused parameter organizationId text. Adapted ordinal reference ($n) accordingly.
Do not confuse the $n notation inside EXECUTE (refer to items in the USING clause) with $n notation in the function body (refer to function parameters)! Related:

Passing array from one plpgsql function to another

Simplified your logic to concatenate the WHERE clause. There were corner case errors: if the initial assignment resulted in empty string, you would start with AND - a syntax error.
Adopt a naming convention that avoids naming conflicts. Parameter names are visible in all statements in the function (but not inside EXECUTE!). Don't use variable names that conflict with column names. A common convention is to prepend parameter and variable names with _.
My advise is to avoid mixed-case identifiers in Postgres, especially when using dynamic SQL.

Related answer on SO:

Test for null in function with varying parameters


Can old collation type impact database performance?

I would like to know whether usage of such "old" collation type may impact overall SQL Server performance?

Well, there shouldn't be a performance difference between SQL_1xCompat_CP850_CI_AS and SQL_Latin1_General_Pref_CP850_CI_AS. Both are the old, simple sort order lists and don't take into account any of the Unicode linguistic rules which the newer Windows Collations (those not starting with SQL_) can do (even for VARCHAR data).

There probably is a slight performance decrease, in typical situations, for using the newer Windows Collations as they are not just simple lists but do tap into the Unicode linguistic rules. However, whatever performance degradation there may be is worth it due to the far better functionality.

One area where there is a performance gain is when comparing NVARCHAR data to an indexed VARCHAR column using a SQL Server Collation (one starting with SQL_) as a conversion needs to take place. An indexed VARCHAR column using a Windows Collation does not get this hit when compared to NVARCHAR data. But this is more of a technical note and not something that should be expected to be an issue for most queries / scenarios.

  Is there any difference between SQL_1xCompat_CP850_CI_AS and SQL_Latin1_General_Pref_CP850_CI_AS?

As denoted in the documentation presented in the question: yes. The sort order is different between them. That should be the only difference as the Code Page and sensitivities are the same between them.

Please note that there are two mistakes in that documentation (but these differences do not change the fact that there are definitely two different sort orders between these two Collations):

Sort Order ID for SQL_Latin1_General_Pref_CP850_CI_AS is 43, not 44
Sort Order Name cannot be the same between both listed Collations as they are different IDs (even if one of the listed IDs is incorrect, the correct ID is still different).

To see the difference please try the following test:

SETUP

CREATE TABLE #CollationTest
(
  [Value] TINYINT NOT NULL PRIMARY KEY,
  [1xCompat] VARCHAR(10) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL,
  [Latin1] VARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
  [Latin1Pref] VARCHAR(10) COLLATE SQL_Latin1_General_Pref_CP850_CI_AS NOT NULL
);

;WITH nums AS
(
  SELECT TOP (256) (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1) AS [num]
  FROM   [master].[sys].[all_columns] ac
)
INSERT INTO #CollationTest ([Value], [1xCompat], [Latin1], [Latin1Pref])
  SELECT [num], CONVERT(VARBINARY(1), [num]), CONVERT(VARBINARY(1), [num]),
         CONVERT(VARBINARY(1), [num])
  FROM nums
  ORDER BY [num] ASC;

TEST 1: Verify that all characters are the same

SELECT * FROM #CollationTest;

TEST 2: Compare the sort ordering between them

;WITH compat AS
(
  SELECT ct.[Value], ct.[1xCompat],
         ROW_NUMBER() OVER (ORDER BY ct.[1xCompat] ASC) AS [ord]
  FROM   #CollationTest ct
), lat AS
(
  SELECT ct.[Value], ct.[Latin1],
         ROW_NUMBER() OVER (ORDER BY ct.[Latin1] ASC) AS [ord]
  FROM   #CollationTest ct
), pref AS
(
  SELECT ct.[Value], ct.[Latin1Pref],
         ROW_NUMBER() OVER (ORDER BY ct.[Latin1Pref] ASC) AS [ord]
  FROM   #CollationTest ct
)
SELECT compat.[ord], compat.[1xCompat], lat.[Latin1], pref.[Latin1Pref]
FROM   compat
INNER JOIN lat
        ON lat.ord = compat.ord
INNER JOIN pref
        ON pref.ord = compat.ord
WHERE  compat.[Value] <> lat.[Value]
OR     compat.[Value] <> pref.[Value]
OR     lat.[Value] <> pref.[Value]
ORDER BY compat.[ord] ASC;

The differences start at row / value 49, and the WHERE clause filters out the first 48 rows where they all match (boring stuff).

It is fairly obvious that there are numerous differences in the sort ordering. One of those differences is that with SQL_1xCompat_CP850_CI_AS the 52 non-accented characters are grouped together with the upper-case always being first, while the accented characters are not only spread out among the rest, but the upper-case and lower-case versions of the same letter aren't even kept together:

m, N, n, O

On the other hand, with SQL_Latin1_General_Pref_CP850_CI_AS both the non-accented and accented characters are all together, grouped by accent:

m, N, n, Ñ, ñ, O

  Will we have any benefits if we will change our collation setting to SQL_Latin1_General_Pref_CP850_CI_AS?

Most likely the only real benefit is probably longevity. I would expect Microsoft to get rid of SQL_1xCompat_CP850_CI_AS at some point.

You should test moving to SQL_Latin1_General_Pref_CP850_CI_AS and if the difference in sort order doesn't adversely affect the app, then probably a good idea to do.

It is probably even better to move to a Windows Collation, but that involves more testing as there are a few other functional differences. Unfortunately, using the following I found that none of the Windows Collations use Code Page 850:

SELECT *
FROM sys.fn_helpcollations()
WHERE  COLLATIONPROPERTY([name], 'CodePage') = 850;

So, while it is still preferred to be using a Windows Collation, changing from your existing Code Page 850 Collation to a newer one (most likely using Code Page 1252) is a much larger testing effort. IF you have the time and resources I say go for it, else just do the change you were asking about :-).


Multiple inserts using select row and variable combination with one SQL statement

You'll need to create a temporary table with the new variables, then cross apply them to the existing row.

I've set up an MCVE1:

CREATE TABLE dbo.rdo
(
    HeaderID int NOT NULL
    , Option2 varchar(10) NOT NULL
    , Option1 varchar(10) NOT NULL
);

INSERT INTO dbo.rdo (HeaderID, Option2, Option1)
VALUES (1, 'test', 'OGGA')

This code will insert the extra 8 variables into the table:

CREATE TABLE #variables
(
    Option1 varchar(10) NOT NULL
);

INSERT INTO #variables (Option1)
VALUES ('OGGA1')
    , ('OGGA2')
    , ('OGGA3')
    , ('OGGA4')
    , ('OGGA5')
    , ('OGGA6')
    , ('OGGA7')
    , ('OGGA8');

INSERT INTO dbo.rdo (HeaderID, Option2, Option1)
SELECT r.HeaderID
    , r.Option2
    , v.Option1
FROM dbo.rdo r
    CROSS APPLY #variables v
WHERE r.Option1 = 'OGGA';

This shows the table after the 8 rows have been added:

SELECT *
FROM dbo.rdo r

Results:

╔══════════╦═════════╦═════════╗
║ HeaderID ║ Option2 ║ Option1 ║
╠══════════╬═════════╬═════════╣
║        1 ║ test    ║ OGGA    ║
║        1 ║ test    ║ OGGA1   ║
║        1 ║ test    ║ OGGA2   ║
║        1 ║ test    ║ OGGA3   ║
║        1 ║ test    ║ OGGA4   ║
║        1 ║ test    ║ OGGA5   ║
║        1 ║ test    ║ OGGA6   ║
║        1 ║ test    ║ OGGA7   ║
║        1 ║ test    ║ OGGA8   ║
╚══════════╩═════════╩═════════╝

1 - I own the referenced website


Use Dynamic SQL To Create View From Cursor

Have you tried modifying the set command like such:

Set @sql = isnull(@sql,'') +
           case when @sql is NULL
                then ' create view dbo.Test as '
                else ' union all '
           end +
           'SELECT ...'

Obviously (?) the print @sql (and exec @sql) is issued after the cursor loop has been completed/closed.

Here's a sample fiddle showing the results:

declare @sql varchar(max), @cr char(1)
set @cr = char(10)

Set @sql = isnull(@sql,'') + @cr +
           case when @sql is NULL
                then 'create view dbo.Test as '
                else 'union all '
           end + @cr +
           'SELECT x from mytable1 ...'

Set @sql = isnull(@sql,'') + @cr +
           case when @sql is NULL
                then 'create view dbo.Test as '
                else 'union all '
           end + @cr +
           'SELECT y from mytable2 ...'

Set @sql = isnull(@sql,'') + @cr +
           case when @sql is NULL
                then 'create view dbo.Test as '
                else 'union all '
           end + @cr +
           'SELECT z from mytable3 ...'

print @sql

And the results of running the above:

create view dbo.Test as
SELECT x from mytable1 ...
union all
SELECT y from mytable2 ...
union all
SELECT z from mytable3 ...


Does INFORMATION_SCHEMA always contain up-to-date metadata?

Yes, the data is up-to-date.  But don't read too much into the numbers.

InnoDB, especially, preallocates space for future rows.  So, it is quite possible to look at the exact size of a table, insert dozens of rows (maybe even thousands), then look at the size again -- and see exactly the same Data_length and Index_length.

When you first create an InnoDB table and insert one small row, the data length will be 16384 bytes (one 16KB block).  As you add more rows, eventually that block will overflow, and another block will be added.  Later (as the table exceeds some threshold), the table will grow in units of (I think) 8MB (an "extent").  This will allow even more rows to be added with no change in disk footprint.

Also, was the table created with innodb_file_per_table ON or OFF?  This controls whether the stuff goes into the communal ibdata1 file or the table's own .ibd file.

And, as @jkavalik points out, DELETE and UPDATE and ALTER can mess with the size, again not necessarily in predictable ways.

Edit

Those are the "disk footprint" size.  Compare to what you can see looking at the filesystem.

A 1-row table with one 4-byte INT takes at least 16384 bytes of disk space.  If you are saying that "4" is the real size, then I have been answering the wrong question.  I say that 16KB is the real size.

There is overhead for each column, for each row, for each block, for BTree structure, for each extent, for indexes, etc.  And InnoDB allocates no less than 16KB at a time.  And room for 'undo' copies of rows.  Hence, "4" turns into "16384".

As a "Rule of Thumb", if you add up the data sizes (4 for INT, etc) of the columns in a row, multiply by the number of rows, then multiply by something between 2 and 3, you are likely to get the "size" of the table.  (The 1-col, 1-row example, of course, is an end-case that exceeds 3x)


As with all open source projects, the internals of the Postgres COPY statement can be easily discovered. As one can read in the source code comments, Postgres attempts to use batch inserts by default, except in the cases where it is not possible:

when there are any BEFORE or INSTEAD OF triggers on the table.
when the target is a partitioned table and there are any statement-level insert triggers.
when the target is a foreign table.
when the target has any volatile DEFAULT expressions.
when there are any volatile expressions in the query WHERE clause.

If none of the above is true, insert will happen in batches of up to 1000 rows (the default can be changed by recompiling the Postgres server).

For partitioned tables the decision to use batch inserts is made for each partition individually.

While most of the COPY processing happens on the server, the client (psql) responsibility is to send data from a local file one 8K buffer at a time, which is evident from the psql \copy command handler source.


Pick the most non-default value from a selection of values

I'm not sure that your MCVE fully represents the problem that you have, but I'll answer the question as given. This question is about performance, so having just a few rows in the table won't cut it. I duplicated your sample data a million times for a total of 6 million rows for FeeTest and 3 million rows for FeeTestClient. Code to do that below:

DROP TABLE IF EXISTS FeeTestClient;

CREATE TABLE FeeTestClient (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [Name] VARCHAR(16));

INSERT INTO FeeTestClient WITH (TABLOCK)
([Name])
SELECT 'ZZZZZZZ' + CAST(RN AS VARCHAR(7))
FROM
(
    SELECT TOP (3000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);

DROP TABLE IF EXISTS FeeTest_source;

CREATE TABLE FeeTest_source (ClientId INT, Fee INT, Val VARCHAR(16), Val2 VARCHAR(16));
INSERT INTO FeeTest_source (ClientId, Fee, Val, Val2)
VALUES (1, 15, 'Default', 'Default'),
       (1, 10, 'Default', 'asdf'),
       (2, 15, 'Default', 'Default'),
       (2, 20, 'Default', 'qwer'),
       (2, 10, 'zxcv', 'asdf'),
       (3, 20, 'Default', 'Default');

SELECT 3 * client.Id - 2 + client.Id % 3 AS ClientId
, src.Fee
, src.Val
, src.Val2 into #t
FROM FeeTest_source src
INNER JOIN FeeTestClient client ON src.ClientId = 1 + client.Id % 3;

DROP TABLE IF EXISTS FeeTest;

CREATE TABLE FeeTest (FeeId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, ClientId INT, Fee INT, Val VARCHAR(16), Val2 VARCHAR(16));

INSERT INTO FeeTest WITH (TABLOCK)
(ClientId, Fee, Val, Val2)
SELECT * FROM #t;

DROP TABLE #t;

Using GROUP BY and only keeping the relevant aggregate could be a good approach depending on the nature of the data in the table and the indexes that are defined against the table. The query below finishes in 2 seconds on my machine:

SELECT Id, Name, MAX(COALESCE(CASE WHEN Val <> 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 <> 'Default' THEN Fee END, CASE WHEN Val = 'Default' AND Val2 = 'Default' THEN Fee END))
FROM FeeTestClient
LEFT OUTER JOIN FeeTest ON FeeTest.ClientId = Id
GROUP BY Id, Name

The plan is as I expect. There's a hash join along with a hash aggregate at the end.

However, you have other options because you have a FeeTestClient table. Another strategy is to calculate what you're looking for per row with OUTER APPLY. One way to do that is below:

SELECT Id, [Name], oa.Fee
FROM FeeTestClient ftc
OUTER APPLY (
    SELECT TOP 1 ft.Fee
    FROM FeeTest ft
    WHERE ft.ClientId = ftc.Id
    ORDER BY
        CASE WHEN Val <> 'Default' THEN 2 ELSE 0 END
        + CASE WHEN Val2 <> 'Default' THEN 1 ELSE 0 END
    DESC, ft.ClientId
) oa;

With APPLY and TOP you almost always want a good index on the inner table. The query optimizer builds a temporary index for us via the spool, and the query takes 14 seconds to run on my machine:

We're searching on ClientId so let's try an index on that:

CREATE INDEX NOT_COVERING ON FeeTest (ClientId);

The index isn't covering so the optimizer doesn't want to use it. Using it would require a lot of key lookups to get the columns that aren't on the index. I can force the index to be used with a lazy, undocumented trick that you should not use in production:

SELECT Id, [Name], oa.Fee
FROM FeeTestClient ftc
OUTER APPLY (
    SELECT TOP 1 ft.Fee
    FROM FeeTest ft
    WHERE ft.ClientId = ftc.Id
    ORDER BY
        CASE WHEN Val <> 'Default' THEN 2 ELSE 0 END
        + CASE WHEN Val2 <> 'Default' THEN 1 ELSE 0 END
    DESC, ft.ClientId
) oa
OPTION (QueryRuleOff BuildSpool);

Now the query runs in 5 seconds. We can see the key lookups in the plan:

Our final attempt with be with a covering index:

CREATE INDEX COVERING ON FeeTest (ClientId) INCLUDE (Val, Val2, Fee);

The query from before now runs in four seconds:

How can you apply this to your giant, anonymized query? You have a lot of key lookups and an index spool. Try defining covering indexes so that you can access the data you need more efficiently. I can't make any kind of guarantee about the overall runtime but it should help to some degree. Please note that I did not have time to take a very careful look at the plans that you posted.

In case anyone is following along at home, there's a bizarre edge case that can cause the covering index to still not be used. Here's one way to work around it:

ALTER TABLE FeeTest
ADD MAGIC_COLUMN AS CASE WHEN Val <> 'Default' THEN 2 ELSE 0 END + CASE WHEN Val2 <> 'Default' THEN 1 ELSE 0 END;

CREATE INDEX COVERING_2 ON FeeTest (ClientId) INCLUDE (MAGIC_COLUMN, Fee);


SQL Server - Why does importing lots of columns with few rows takes way longer than few columns with lots of rows?

At first, I guess the size of the 268 columns can be pretty big. So just look at number of bytes in one row, multiply by 128 and you'll see how much data SQL can cache at one time.

Second, there might be a lot of data type conversions from text to numerics and dates.

At third, there might be foreign keys, indexes, constraints and even triggers on the table you are writing into.

At fourth, the table can be in another database, which is placed on a slower drive.

At fifth, I guess all 268 columns can't get into the same page, and records go to LOB pages.

So, there might be even more reasons. I might suggest you to use clustered Columnstore index on that table, that might improve the loading situation.


Can Oracle Heterogeneous Services be used to connect to a SQL Azure database?

If there is an ODBC driver to connect to it, then the Oracle Transparent Gateway for ODBC should work just fine.


Where are the username and password stored in postgresql?

In pg_authid
By default encrypted, see here
no, not possible (if encrypted)


How to Move All tables from one Database into Different Database?

There are a lot of ways to do this one that go beyond what I listed, but here's a start.

1) You could try the SQL Server Migration Assistant for Oracle.  It's free and usually works well for me for smaller or medium sized datasets.

http://www.microsoft.com/en-us/download/details.aspx?id=28766

2) Create a SSIS package.

3) You could create a linked server in SQL and then select * from Oracle into SQL table (messy and probably not a good idea).

http://support.microsoft.com/kb/280106

4) Export to flat files and import into SQL.  Again, a ton of caveats with this one.


How to take backup of schema postgresql?

You export only schema without data using pg_dump in psql [terminal]:
pg_dump -U postgres -s databasename > backup.sql

or with Pgadmin4

Go to Tool -> Backup and select only schema in the Dump option.


Machine login for Microsoft SQL Server for Linux

I checked in with the WHM+cPanel - the company behind the hosting control panel and they said they store their MySql root user password on a local file on disk as well for doing all the automated maintenance, creation etc.

Excerpt from their docs:
By default, cPanel & WHM's implementation of MySQL stores the MySQL root account password in the /root/.my.cnf configuration file. Do not edit this file manually.

I'm going to end up with doing the same, and storing the file (read only) in the same /root/ folder.


Recreate the dump from MySQL 5.7
Personally, I would recreate the dump without mysql schema.
SQL="SET group_concat_max_len = 1024 * 1024;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name separator ' ')"
SQL="${SQL} FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql','sys')"
DBLIST=`mysql -u... -p... -AN -e"${SQL}"`
mysqldump -u... -p... -B ${DBLIST} > mydbs.sql

Then, you can load mydbs.sql in the MySQL 8.0 instance.
What about the dump you already have ?
First, find out the lines in the dump where each database starts and the number of line in the dump
grep -in "^USE" mydbs.sql
wc -l < mydbs.sql

As an example, I created a dump from a MySQL 5.7 vagrant instance.
root@LAPTOP-57FSQ99U:~# grep -in "^USE" mydbs.sql
24:USE `DMP492`;
122:USE `DMP551`;
190:USE `DMP579`;
228:USE `abeersul`;
262:USE `dbname`;
292:USE `karma`;
300:USE `mysql`;
1212:USE `pch_prod`;
1220:USE `pchcom_prod`;
1286:USE `tryout`;
root@LAPTOP-57FSQ99U:~# wc -l < mydbs.sql
1341
root@LAPTOP-57FSQ99U:~#

Next, create a new dump. Copy everything above and below the mysql schema
In this case, everything before line 300
Then, copy everything from the first DB after mysql schema
In this case, subtract 1212 (first line of the next DB after the mysql schema) from 1341 (the number of lines in the original dump you made) and add 1. Then copy that number of lines from the bottom of the original dump into the new dump:
head -299 mydbs.sql > newdump.sql
(( LINES = 1341 - 1212 + 1 ))
tail -${LINES} mydbs.sql >> newdump.sql

As a demonstration, note how the new dump will not have the mysql schema
root@LAPTOP-57FSQ99U:~# head -299 mydbs.sql > newdump.sql
root@LAPTOP-57FSQ99U:~# (( LINES = 1341 - 1212 + 1 ))
root@LAPTOP-57FSQ99U:~# tail -${LINES} mydbs.sql >> newdump.sql
root@LAPTOP-57FSQ99U:~# grep -in "^USE" newdump.sql
24:USE `DMP492`;
122:USE `DMP551`;
190:USE `DMP579`;
228:USE `abeersul`;
262:USE `dbname`;
292:USE `karma`;
300:USE `pch_prod`;
308:USE `pchcom_prod`;
374:USE `tryout`;
root@LAPTOP-57FSQ99U:~#

Now, just load newdump.sql into MySQL 8.0


Table was deleted during ALTER, how to recover?

Install the same version of MySQL onto another VM. Create the same database. Create that same table; just the schema, no data. Shutdown original MySQL server. Copy the .frm from VM-MySQL to original in proper location. Fix permissions, if necessary. Start original MySQL. This should allow MySQL to see the table.


How RDBMS solve C10K problems, it's also load balance?

This is an extremely broad question, but anyway:
Typically this is solved using connection pooling. To serve tens of thousands (or millions) of concurrent users, you typically need way less concurrent connections on the database ("hundreds"). This is especially true for web-applications.
Web-Applications (where you get that number of concurrent users) are typically also more read intensive than write intensive. So load-balancing read requests already takes away a lot of load.
For "traditional" Client/Server applications, you typically don't have that high number of concurrent database sessions, but for those, this is very often dealt with by scaling vertically: increase number of CPUs, increase RAM, increase number of harddisks to allow more concurrent work to be done (high-end servers with 512 CPUs and more and disk arrays with thousands of disks are not unheard of)
Different databases products provide different scale out strategies. Multi-Master setups (e.g. "shared nothing" architectures) are one way to do that. But the details on how that is implemented are completely dependent on the concrete DBMS product.


Basically yes, all you'd need is to backup and restore the databases. However, there's a bunch of stuff that won't do.

Server level settings (MAXDOP/CTFP, etc.)
Agent Jobs
Maintenance plans
Linked Servers
Users/Logins
Any other customization

It's kind of a pain in the butt to find all this stuff, and even if it's a one time move, scripting it all out can be a hassle. I'd recommend something like the free dba tools to help you with that.


Let me try to summarise the answers as per the commenters above.

First of all, to achieve higher deletion speeds you can do three things:

Delete a higher number of rows at a time
Use partitioning
Run multiple deletes

Ad 1) Since deleting a large number of rows at a time hit the transaction log hard and causes locking - you likely want the number of deletes in a statement to be "small". I find that around 10K rows is a good number (and having table lock escalation turned off with ALTER TABLE Foo SET LOCK_ESCALATION = Disabled

Ad 2) If your table is not already partitioned, this is not an option

Ad 3) DELETE in SQL Server is single threaded, so you need to run more than one at a time to get max speed. To run multiple deletes concurrently, you need a way to partition them so each parallel delete runs on its own set of keys and doesn't block with the others. Typically, you can use the primary key of the table to partition the delete.

For example, if you have an IDENTITY column called key on the table, you can first:

SELECT MAX(key) - MIN(key), MIN(key) FROM Foo WHERE <rows that must be deleted>

Keep the Max - Min and Min somewhere (a table in tempdb) that you can quickly read from into variables @IntervalSize, @MinKey respectively.

Let us say you decide that you can allocate 4 cores for running deletion. You now run 4 queries, either from 4 x SSMS new query or four command prompt via SQLCMD, each doing this:

DECLARE @NumDone INT = 1
WHILE @NumDone > 0 BEGIN
  SET ROWCOUNT 10000

  DELETE FROM Foo WHERE Key BETWEEN @MinKey + @IntervalSize / 4 * @n
     AND @MinKey + @IntervalSize / 4 (@n + 1)
  SET @NumDone = @@ROWCOUNT
END

Pick @n = 0,1,2,3 for each of the concurrently running DELETES.


ERROR 1091 (42000): Can't DROP 'created_date'; check that column/key exists

If you want to drop the index that you created previously, the correct statement would be:

alter table bill_item drop index idx_comp;

created_date is the name of one of the columns in your table bill_item.

Note: To find the index name use
SHOW INDEXES FROM <table> if you used a different naming convention than prepending 'idx_'