MySQL replication to slave having VPN connection to Master

Let's start with a sample display of SHOW SLAVE STATUS\G

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000262
        Read_Master_Log_Pos: 803779735
             Relay_Log_File: relay-bin.067799
              Relay_Log_Pos: 402744
      Relay_Master_Log_File: mysql-bin.000262
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 803779735
            Relay_Log_Space: 402598
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

As long as Slave_IO_Running is Yes, MySQL Replication has the needed connectivity. According to your question, when MySQL loses connectivity and reconnects, will it lose data? In a perfect world, I would say no as long as the relay log (Relay_Log_File) is written in a clean format. Once in a long while, when the network intermittency is significantly long, the relay log may get corrupted.

There is one sure fire way to correct this (please follow along):

Take note of these two values from the SHOW SLAVE STATUS\G

Relay_Master_Log_File : This represents the binary log on the Master whose entry was last executed on the Slave
Exec_Master_Log_Pos : This represents the position of binary log on the Master whose entry was last executed on the Slave

To properly connect to a safe place on Master, do the following

Select the Relay_Master_Log_File and use the value : mysql-bin.000262

Select the Exec_Master_Log_Pos and use the value : 803779735

Once you have those values, then run these commands using those values:

STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000262',master_log_pos=803779735;
START SLAVE;

The CHANGE MASTER TO command will erase all relay logs on the Slave and start with a fresh relay log.

This is most likely the best way because you can only be sure about the last binary log entry from the Master that successfully reached the Slave and was executed on the Slave. Any entries from the Master's binary log that was written to a current relay log but was not closed properly or correctly written due to an intermittency problem with the IO Thread could result in a malformed relay log.

The only way this method would not work is if the binary log on the Master is itself corrupt. I have seen this happen when a DB Server crashed leaving the last binary log not closed properly. Running mysqlbinlog against the Master's last binary log had shown a difference between the filesize and last legal position. Trying to reestablish replication from a Master's corrupt binary log is a headache. You must then fanagle replication to work by running RESET MASTER on the Master, connecting replication to the first binary log on the Master, and perform due diligence of syncing the Slave using pt-table-checksum and pt-table-sync.

UPDATE 2012-02-17 08:13 EDT

@vinny : If you are thinking of creating an additional Slave, here are other posts I have made in the past on this and similar subjects:

https://dba.stackexchange.com/a/1409/877
https://dba.stackexchange.com/a/2734/877
https://dba.stackexchange.com/a/12352/877
https://serverfault.com/questions/257394/create-a-mysql-slave-from-another-slave-but-point-it-at-the-master/257426#257426

I hope they help.


Cannot install Oracle Grid on Oracle Linux 6.4

The "inappropriate ioctl for device" error probably means it doesn't like either the filesystem or the options with which is mounted. Check your ASM setup and that it actually is using ASM.


Mysql - Problem speed of access on query (Low latency query sometime)

You may switch the table from innodb to memory, but ensure the size of the table isn't that huge so that it doesn't eat up entire of its memory. Yes, you may safe guard or recover the table during system crash. Have master/slave replication setup and once the table is created on master with memory as engine type and change the table engine type to innodb (on slave only). So that even at the instance of master crash/restart you will get the data from slave.


Just put a delete/insert/update trigger on every table and log every operation to a central location. Something like this (untested):

CREATE TRIGGER dbo.auditTableName
ON dbo.TableName
FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS (SELECT 1 FROM inserted UNION ALL SELECT 1 FROM deleted)
    INSERT CentralDB.dbo.AuditTable(TableName, EventTime, Action)
      SELECT N'dbo.TableName', GETDATE(), CASE
        WHEN EXISTS (SELECT 1 FROM inserted) THEN
          WHEN EXISTS (SELECT 1 FROM deleted) THEN
            'Update' ELSE 'Insert' END ELSE 'Delete' END;
END
GO

You can generate this code easily, like this, assuming all objects are under dbo:

SELECT 'CREATE TRIGGER dbo.audit' + name + '
 ON dbo.' + name + '
 FOR INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS (SELECT 1 FROM inserted UNION ALL SELECT 1 FROM deleted)
    INSERT CentralDB.dbo.AuditTable(TableName, EventTime, Action)
      SELECT N''dbo.' + name + ''', GETDATE(), CASE
        WHEN EXISTS (SELECT 1 FROM inserted) THEN
          WHEN EXISTS (SELECT 1 FROM deleted) THEN
            ''Update'' ELSE ''Insert'' END ELSE ''Delete'' END;
END
GO' FROM sysobjects WHERE type = 'U';

Again, that's just a general idea, untested as I don't have SQL Server 2000 handy. Use results to text and you should get the appropriate trigger for each table.


How can we demo Lost update, Uncommitted data, Unrepeatable data, Phantom on MySQL

The different problems are prevented at different isolation levels. So you need to first set an isolation level that allows a problem to happen before you can demonstrate it.

Here's a table that explains which isolation level allows which problem to occur (taken from my post over at stackoverflow which also briefly explains the problems the isolation levels are solving).

                 | Lost updates | Dirty Read | Non-Repeatable Read | Phantom Read
---------------------------------------------------------------------------------
Read Uncommitted | possible     | possible   | possible            | possible
Read Committed   | impossible   | impossible | possible            | possible
Repeatable Read  | impossible   | impossible | impossible          | possible
Serializable     | impossible   | impossible | impossible          | impossible

So, for the first example, the lost update problem, it would work like this:

session 1> start transaction;
Query OK, 0 rows affected (0.00 sec)

session1 > select * from t;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

session 1> update t set a = 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

The session is not yet committed, but in session 2 you can already see the value, when you set the isolation accordingly.

session2 > set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

session2 > start transaction;
Query OK, 0 rows affected (0.00 sec)

session2 > select * from t;
+------+------+
| a    | b    |
+------+------+
|    2 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

session2 > update t set a=3 where a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

As you can see the Changed value for session 2 is 0, because it read uncommitted values from session 1. When you commit both sessions now, no matter which one first, the values in column a will be 2. The update statement of session 2 got lost.

I think you get the idea, of how to demonstrate the problems. I got some work to do now. When you still have problems, I will update this post when I find the time.


In SQL Server, what's the impact of a checkpoint to log records in log buffer?

We could implement a DBMS such that every thread that needs to write a log record writes it straight to disk immediately and waits for acknowledgement. This would be inefficient since there would be many small IOs and many threads waiting. It is more efficient for each thread to write to an in-memory buffer and have that buffer written to disk as a single large IO. All threads that write log records add to this one buffer and when it is written to disk (i.e. "flushed" or "hardened") all log records from all threads are written.
So when must that buffer be flushed? There are three mandatory circumstances

When a transaction commits.
When a checkpoint is taken.
When the buffer is full.

The system can additionally write the buffer any time it chooses. The
Log records must be flushed before a commit acknowledgement is sent to the client to satisfy the durability guarantee of ACID.
Checkpoints flush dirty pages from the bufferpool to disk, whether the transaction which dirtied those pages has committed or not. On restart after failure the DBMS needs a way to figure out which changes to those dirty pages should be retained and which removed. SQL Server follows the ARIES recovery protocol for recovery and ARIES requires the write ahead log to be flushed before the dirty pages are flushed.
Naturally the buffer must be of finite size and so the system must be able to empty it at any point. The log contains many types of record other than user data changes. ARIES protocol takes care of the various combinations of flushed log, flushed or unflushed bufferpool pages, and committed, uncommitted and rolled back transactions.
The authors you quote are saying the same thing - that log records must be flushed before the corresponding dirty pages can be flushed. This is an echo of the documentation linked previously

SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written.


MS SQL Server Error 18456 after database move

First, the error seems something wrong with login (as per following query)

select * from sys.messages
where language_id = 1033 and message_id = 18456

When you told to simply rename DB Name in connectionstring, did you verify which username was used, and the same username exists as login with appropriate permissions on SQL server, you can verify this via SSMS.


AlwaysOn Manual failback not working correctly

I resolved this in the end. It was my own fault. I had connected using the name of the cluster "SQL-CLUSTER" rather than creating a listener. Originally I had tried to create a listener but was getting errors so stupidly I'd just ignored them. The errors were because I'd not set up the network properly (I'm a DBA not a network technician :) )

Anyway, after setting up the listener properly it works like a charm. Long story short... don't connect to the cluster, connect to the listener.


Settings

There is a pecking order of what config files are used.  I hesitate to say exactly what Aurora does, since it is offshoot of MySQL.  First will be /etc/my.chf of /etc/my.ini; last will be .my.cnf in your home directory.  (Note the leading dot.)  Later values will override earlier values.

Be sure to put things in the [mysqld] group in each file.

Be aware that the various settings become visible at different times -- after restart; after re-login; etc.  Also, note that some are "global", some are "session" (and initialized to the global value); etc.  Also note that some can be changed dynamically with a SET command; but, again, there are various variants due to global/session.

Buffer_pool

The main use of memory is for the buffer_pool.

Bigger = faster
Too big --> swapping, and slowdown, or even crash

The default for Aurora is probably optimal for the amount of RAM in the server.  Do not change it.  In the absence of a decent default, set it to about 70% of available RAM for MySQL.


How do I distribute data from the *source database* to the *destination database* in `SQL`?

Replication is the right solution here, despite the comment above.  Simply copy the published table into a separate staging table at each subscriber.  Then, on a schedule of your choosing, copy the data from the staging table to wherever it needs to go in the subscriber database.

You can use SQL Server Transactional Replication for this, or just copy the tables on a schedule with SSIS or over Linked Server connections.

If you use SSIS, your SSIS package you can use a Foreach loop to load multiple databases, or you can configure a separate SQL Agent job or job step for each target database, which would enable you to schedule them differently, and run just one.  A SQL Agent job step can specify the target connection string.


Data design issue. Need help with normalization

One of these is not like the others - expanse

Entities:
groupID, ID, name

composite PK on groupID, ID


PostgreSQL 9.1, how to restore a Hot Standby Slave w/o any Wal Files?

If the WAL files don't exist anymore anywhere, you need to rebuild the standby from scratch (new base backup).  If you still have the WAL files on the master or in an archive somewhere, just change the restore_command in recovery.conf to fetch them from wherever they are.


ibdata1 back to it's original size after deleting it

Did you have innodb_file_per_table = ON?

Regardless of the above setting, ibdata1 will have some stuff in it.  Without more specifics (size of that file, the above setting, number of tables, number of .ibd files, etc.), I cannot be more specific.

In my opinion, "tiny" tables may as well be built with file_per_table = OFF.


Selecting multiple values from a column and put them into separate columns

The simple way (assuming you already know all the party users) would be to use pivot() and row_number() statements.

select  MainUser,
        [1] as PartyUser1,
        [2] as PartyUser2,
        [3] as PartyUser3,
        [4] as PartyUser4,
        [5] as PartyUser5,
        [6] as PartyUser6
from (
    select *, row_number() over(partition by MainUser order by PartyUser) as rn
    from party_table
    ) t
PIVOT (max(PartyUser) for rn in ([1], [2], [3], [4], [5], [6])) as u


SQL Server 2005/2008 logging connections

No, you're not going about this in the right way.

  I have application that be able to take all RAM to the SQL Engine

SQL Server will use as much as it needs unless you limit it. Read Brent Ozar's A Sysadmin’s Guide to Microsoft SQL Server Memory.

  transaction logs raising to the unbelievable size on HDD during
  one-two days

9 times out of 10 this is due to the database being in FULL recovery and no log backups being taken. See Paul Randall's article Running Out of Transaction Log Space.

If you want to monitor the items you listed:

Using SQL Server Profiler
sp_whoisactive
SQL Server Perfmon Best Practices


Mysql output to csv putting column names on the bottom

The records order is defined by your ORDER BY clause expression. All id field values are converted to string type due to the type of the base (first) subquery field type. Of course the digits are first and letters last.

Replace it with

ORDER BY id='id' DESC, id


Detect if transaction request for a certain database Query has come from a Virtual Machine?

There is no specific, built-in, method for determining if a client connection is coming from a virtual machine.  You'll need to identify the client connections via some other method, such as the name of the client machine.

For instance, if the VMs are named like "VM01", "VM02", etc, you could use a server-level logon trigger to deny connections from machines not named like "VM%".   Be aware, if the logon trigger doesn't work correctly you run the risk of preventing all client connections.  If the trigger doesn't accept connections from the server machine, SQL Server Agent won't be able to connect.  In short, there are a lot of "gotchas" using a logon trigger.


It sounds like what you're looking for is a "non-relational database," often called a document or NoSQL database.  Amazon defines that here:

What is a document database

  A document database is a type of nonrelational database that is designed to store semistructured data as documents.

Microsoft has a cloud based product called Azure Cosmos DB, which you can read about here:

Introduction to Azure Cosmos DB

  Keeping database schema and indexes in-sync with an application’s schema is especially painful for globally distributed apps. However, with Cosmos DB, you do not need to deal with schemas or indexes. The database engine is fully schema-agnostic.

Microsoft doesn't offer an on-premises version of this product.

SQL Server also has json and XML data types. You could serialize objects with this loosely structured format and store it in a column of this type, but the preferred approach would be to use Cosmos DB or another document database like mongodb or ravendb.


Using MySQL Workbench you can reverse engineer a database into an EER model. See the documentation for details.

  For a visual representation (EER diagram) of this schema, select
  Model, Create Diagram from Catalog Objects to create the EER Diagram
  for the model.

There are binaries available or a PPA depending on your version of ubuntu. See here for details on the apt repository or install from source.


Granting a user read-only access on [dbo] but full access on their own schema

The correct grants are something like:

create user joe without login
go
create schema joe_schema
go

grant select on schema::dbo to joe;
alter authorization on schema::joe_schema to joe;

grant create table,
      create procedure,
      create view,
      create function,
      create type
      to joe;

It's pretty important that you don't grant the user the ability to alter a schema owned by another user, especially one owned by dbo.  If you do ownership chains will allow the user to skip permissions checks on the schema owner's objects in all schemas.


Reading 1124 blocks to get 1904 rows is unfortunate, but normal.
What is not normal is the I/O time: almost 20 ms for a single 8kB block. Either your I/O system is really overloaded, or the disk is amazingly slow.


Using desktop computers to manage an SQLite database on a fileserver?

The issue with this is that as you scale SQLite, you run into the fundamental problem that it doesn't have row-level locks, only table locks. The "thing" you run out of first isn't a raw resource like CPU or IO or memory - it's that your app will get bogged down in lock management. You can have lots and lots of users sharing an SQLite database, but only your readers can tolerate waiting for your writers. If this database is going to be on a a shared drive, then that will only make your scalability problems worse as it introduces additional latency, making the lock acquire/release cycle longer!

Also, in a business, there is no such thing as "non-critical data" unless you are literally talking about people MP3 or recipe collections! If people use it to get their work done and for whatever reason it "goes away" then they will wonder why, and perhaps escalate it up their reporting line. I totally sympathize with how long it takes to get a "proper" database server (be aware that the DBA feels exactly the same way, when he's waiting for the procurement department to buy the servers!) but unless you are prepared to support something forever, don't deploy it. Or if you do, develop it with something like the free versions of Oracle or SQL Server, so that when the time comes, it can easily be migrated into the datacentre.


Where can I find exact rules of a collation?

Unfortunately no, no such documentation exists. The most likely reason for this is that it would be much more difficult to document than most people are aware of, due to the actual complexity of the rules. While there is a default weight to each character (even if that weight is 0), how that weight is used is determined by several layers of rules. Some of those rules are dependent on the Collation (e.g. case, accent, Kana, and width sensitivity) and some are simply hard-coded (e.g. sorting lower-case before upper-case in most, but not all, cases). But those types of rules are just some of the types of rules that are processed.

Yes, there are simple mappings of æ = ae, but there are more complex mappings involving combining characters (characters that are not displayed on their own, but are super-imposed over base characters). For example:

SELECT NCHAR(252), NCHAR(0x0308), N'u' + NCHAR(0x0308)
-- ü    ̈   ü

IF (NCHAR(252) = N'u' + NCHAR(0x0308)) SELECT 'Same!' ELSE SELECT 'Nope.';
-- Same!

IF (NCHAR(252) = N'u') SELECT 'Same!' ELSE SELECT 'Nope.';
-- Nope.

IF (NCHAR(252) = N'u' COLLATE Latin1_General_100_CI_AI) SELECT 'Same!' ELSE SELECT 'Nope.';
-- Same!

Of course, some "characters" are comprised of a base character and multiple combining characters.

Also, some weights depend on context (i.e. where the character is placed in relation to other characters). For example, a hyphen (i.e. == minus sign == - ) by itself sorts before letters. A simple test shows this behavior:

SELECT * FROM (VALUES ('a'), ('c'), ('-')) t(c)
ORDER BY t.c COLLATE SQL_Latin1_General_CP1_CI_AS ASC;

SELECT * FROM (VALUES ('a'), ('c'), ('-')) t(c)
ORDER BY t.c COLLATE Latin1_General_CI_AS ASC;

Both Collations have the same ordering here:

-
a
c

Now let's place the hyphen between some letters. First we will test with a SQL Server Collation (which uses the older, simpler "string sort"):

SELECT * FROM (VALUES ('a'), ('c'), ('-'), ('aaa'), ('aca'), ('a-b'), ('a-d')) t(c)
ORDER BY t.c COLLATE SQL_Latin1_General_CP1_CI_AS ASC;

That returns:

-
a
a-b
a-d
aaa
aca
c

Here there is no difference in that the - still comes before the letters, which is why both a- rows came before both the aa and ac rows. Now use a Windows Collation:

SELECT * FROM (VALUES ('a'), ('c'), ('-'), ('aaa'), ('aca'), ('a-b'), ('a-d')) t(c)
ORDER BY t.c COLLATE Latin1_General_CI_AS ASC;

That returns:

-
a
aaa
a-b
aca
a-d
c

What you are seeing here is that the hyphen, due to being between letters, is ignored (and yes, it is ignored only when sorting; when doing a comparison, it is not ignored, which can be seeing by running: SELECT 1 WHERE 'a-b' = 'ab' COLLATE Latin1_General_CI_AS;). This is why a-b sorts between aaa and aca as it is seen as being ab, and a-d comes after aca as it is seen as being ad.

Two other complicating factors are:

What versions of the Unicode Character Database (UCD) and Common Locale Data Repository (CLDR) are being used. It is not as straight-forward as one (or many) would hope, and the older versions of the Unicode standard either do not seem to have all of the base info, or at least they certainly don't make it easy to find
How precise is Microsoft's implementation of the Unicode standard? After all, it is just a standard and can be implemented differently by different vendors.

All that being said, you can still probably get pretty close to what you are looking for by examining some of the base data files from Unicode.org. But first, we will need to know which locale-specific customization file to look at. We can find this by getting the LCID of the Collation in question:

SELECT COLLATIONPROPERTY('Finnish_Swedish_CI_AS', 'LCID'),
       CONVERT(VARBINARY(3), COLLATIONPROPERTY('Finnish_Swedish_CI_AS', 'LCID'));
-- 1035       0x00040B

Now we can look for 040B on the following page: LCID Structure.

We see that 0x040B refers to fi-FI, which means that we will be looking for a file having either fi-FI or just fi in its name.

Explanations (some "light" reading ;-):

Unicode Collation Algorithm (UCA)
Unicode Locale Data Markup Language (LMDL): Part 2: General (Transforms)
Unicode Locale Data Markup Language (LMDL): Part 5: Collation

Data:

Transformations (æ → ae, etc.)
Remapped Unicode Element Table for use with CLDR
Root collation order
Finnish customizations (based on LCID of 1035 / 0x040B meaning "fi")


Tools for working with xtrabackup files?

There are tools to extract data from XtraBackup data files. But don't forget about a redo log. Without it the Xtrabackup copy is incomplete. To make the backup usable you have to apply the redo log which means you need to restore the backup.

I would look at mydumper or mysqldump with -T option and then upload the dumps to hive.


Probably easiest to solve with DCL:

DENY SELECT ON data1.table (column3) TO user1;

Other ways is to create a view where the critical columns is not exposed, and grant select on that view to the user. Needless to say you should revoke any privileges on the table from that user


Character Conversion Issue Importing From CSV File

there are various words that are "written" to the database incorrectly.

No, the characters are being read incorrectly. They are being written correctly. Or, another way to look at this is: the characters are being written incorrectly to the data file. Either way, SQL Server is doing exactly what it is being asked to do.

This is a simple encoding issue. The data was originally exported as UTF-8, but then that UTF-8 encoded file was read into SQL Server as if it were an Extended ASCII file using Code Page 1252. There are three clues which indicate that this is the issue:

Most characters come through correctly. The ones that don't are the accented characters.
The characters that are incorrect are coming through as two characters instead of one.
You mentioned that exporting to an "ASCII" file worked.

UTF-8 is a multi-byte encoding: it uses a different number of bytes depending on the character being encoded. The first 128 Code Points (U+0000 - U+007F), which contain the US English alphabet, all use 1 byte. Code Points above that range take 2 - 4 bytes. This is why some characters transferred between systems as expected: N (upper-case Latin "N") is 0x4E in UTF-8 as well as in Code Page 1252 (in fact, it's 0x4E across all 8-bit Code Pages supported by SQL Server). This is one of the reasons that UTF-8 is so popular. However, the accented characters are not 1 byte in UTF-8:

The á ( U+00E1 ) is encoded as two bytes in UTF-8: 0xC3 and 0xA1. When those two bytes are read by something expecting Code Page 1252, they are interpreted as being Ã ( 0xC3 on Code Page 1252 ) and ¡ ( 0xA1 on Code Page 1252 ). Then, either you or your import process, lower-cased the Ã (probably because it was in the middle of a word) which is how you ended up with: Usuã¡rio.
The ú ( U+00FA ) is encoded as two bytes in UTF-8: 0xC3 and 0xBA. When those two bytes are read by something expecting Code Page 1252, they are interpreted as being Ã ( 0xC3 on Code Page 1252 ) and º ( 0xBA on Code Page 1252 ). Then, either you or your import process, lower-cased the Ã (probably because it was in the middle of a word) which is how you ended up with: Nãºmero.

Your choices are:

Encode the file as (Extended) ASCII using Code Page 1252 (as it is exported), and do not change how it is being read into SQL Server. (it sounds like you have already done this).
Continue exporting the file using the UTF-8 encoding, but change how the file is being read into SQL Server by specifying that it is encoded as UTF-8. Please note that for anyone using BCP.exe, BULK INSERT, or OPENROWSET(BULK...), this option only became available starting in SQL Server 2016. The Code Page to use is 65001 (which usually means UTF-8 with Byte Order Mark, but I am not sure if SQL Server requires the Byte Order Mark in these cases).


Get list of orders that have not been closed that are older than 60 days

SELECT
    o1.order_id
FROM tblOrders o1
WHERE
    o1.dsc_id = 1
    AND o1.te_date < DATE_ADD(NOW(), INTERVAL -60 DAY)
    AND NOT EXISTS (SELECT order_id FROM tblOrders o2 WHERE o2.order_id = o1.order_id and o2.dsc_id = 8)
    AND EXISTS (SELECT order_id FROM tblOrders o2 WHERE o2.order_id = o1.order_id and o2.dsc_id != 1)
GROUP BY o1.order_id


SSMS command to disconnect after query has finished

Easy one ...
Think about why do you want to do it ? Any specific reasons ?
From BOL :
To change the options for the current queries, click Query Options on the Query menu or the shortcut menu of the SQL Server Query window. Under Execution, click Advanced.

Disconnect after the query executes
When this check box is selected, the connection to SQL Server is terminated after the query completes. This check box is cleared by default.


For your second option, you could put a filtered unique index on:

CREATE UNIQUE INDEX ix_DefaultLocation ON dbo.Locations(EventID, IsDefault)
WHERE IsDefault = 1

This will allow unlimited non-default locations, but only one default location per event id.


Efficient PostgreSQL Updates Using a Temporary Table

Things that can be done, although whether they help or not... is another story:

Given that your UPDATE is quite simple, my first guess is your triggers are hurting your performance. Processing triggers is normally time-consuming, specially if they're written in any interpreted language (which means, more or less, they're not written in C). If you have possibility of checking with a development machine, test disabling all the triggers, and see what effect it has (time your queries!). Then reenable them one by one, and see which effect each one has on timings. You can find a few triggers hurting (a lot). If there are a few that consume a lot of time, have someone qualified revise them, optimize them, and even if necessary, rewrite them using C. My experience is that any kind of logging or auditing your inserts might make the process slower by (easily) a factor of 10. Take into account that, on top of your 14 triggers, the database might have added some more to make sure all constraints are met (CHECK, REFERENCES, UNIQUE, ...). It's not normally a good idea to try to disable those (and doing it is not straightforward, if possible at all).
Try to find out whether you really need all the indexes in your setup. Check the explanations about Unused Indexes on PostgreSQL wiki. The way your query is working (only updating the column detail), if detail is not part of any index, this wouldn't have much of an influence. PostgreSQL should be able to perform a Heap Only Tuple (HOT) update, and the indexes wouldn't have any big effect.
For HOT updates to succeed, you need some free space in your tables. So, make sure your tables have a fillfactor less than 100. From the docs on CREATE TABLE:

  fillfactor (integer)

  The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. This parameter cannot be set for TOAST tables.

(emphasis mine)
Consider having a covering index on your temporary table. That is:

CREATE INDEX tmp_products_idx
ON tmp_products
USING BTREE
(product_id, detail);

ANALYZE tmp_products;

This makes sense only if the length of detail is moderate. I don't think this will make much of a difference... because this might allow for an Index Only Scan for the source part of your update, but you won't be certain unless you try it.

Some more information about your execution plans will be necessary to provide finer advice.


Best practice is to use a domain account for SQL Services, preferably a different one for SQL Server and SQL Server Agent. These accounts should not be local admins (and do not need to be), you should use a principle of least privilege. You need to set these up in advance so you can enter the account credentials during SQL Server setup.

One question, why on earth are you doing a new project (it is 2016) on an 8 year old version of SQL Server? SQL Server 2016 RTMs [releases to manufacturing] on 1.6.2016, i.e. in just over 4 weeks. I'd recommend using that.


Oracle 11g not allowing to connect to. Error ORA-01122: database file 1 failed verification check

Did you do anything like renaming datafiles or moving them around in the filesytem?
The ORA-01122 indicates that the information in the controlfile does not match your environment.

So if you do a shutdown/restart of the database and still get the error and moved files around you should move/rename the files to their original places/names and try again.

If all this does not work, have a look at MOS 1041424.6 for details.


How to order by COUNT(*) of unique entries in GROUP BY?

I answer this question because @ypercubeᵀᴹ was reluctant to do so, and I thought it might help others.
The tricky part is to use COUNT(DISTINCT WordID) instead of COUNT(*), as the latter does not consider the repetition.
As suggested by @ypercubeᵀᴹ, the ultimate solution is to use COUNT(DISTINCT WordID) and COUNT(*) together to have a perfect order considering the number of unique words and total occurrence.
The query looks like
SELECT ArticleID,COUNT(*) AS C, COUNT(DISTINCT WordID) AS D FROM WordMap
WHERE WordID IN(
SELECT WordID FROM Words WHERE Word IN ('word1', 'word2')
)
GROUP BY ArticleID
ORDER BY D DESC, C DESC


No.

Preferably card numbers and similar information should be stored in a Vault, where access can be controlled and no single person have full access.

If that is not possible, and the card numbers needs to be stored in the database, then they should always be stored encrypted.
Many databases have functions that can do the encryption on the database side, although it is best to do it in the application so that the numbers are encrypted before sent over the network.

For instance:

Postgres: pgcrypto module (pgp/raw encryption)
SQL Server: ENCRYPTBY* functions
Oracle: DBMS_CRYPTO package


It appears that something like 95% of your wait time is on CPU.  Even if you completely eliminated the direct path reads, you'd eliminate less than 2% of your total wait time.  It seems unlikely that a 2% reduction in your wait time would even register as an improvement.

Taking a look at what is consuming all that CPU-- you have a handful of very CPU intensive queries. 0dp0c8wux0jnt and 3g2kw67v3v0qs are consuming 60% of the CPU your system is using. Have you looked at optimizing those queries so that they use less CPU? 3g2kw67v3v0qs in particular takes nearly 5 seconds per execution.

What else do you have running on this machine?  A total SGA & PGA that is 6% of the memory available on the machine implies that you probably have a number of other things running (perhaps many database instances).


MySQL does not feature recursive queries. In fact, Bill Karwin just wrote a blog post about how MySQL is the only major RDBMS without recursive queries. Notwithstanding, what you need is a Stored Procedure that performs the query as a preorder tree traversal using a queue.
YOUR SAMPLE DATA
mysql> drop database if exists ayman;
Query OK, 2 rows affected (0.03 sec)

mysql> create database ayman;
Query OK, 1 row affected (0.00 sec)

mysql> use ayman
Database changed
mysql> create table topics
    -> (id int not null primary key,
    -> parent int default null
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into topics (id) values (1),(5);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into topics (id,parent) values (2,1),(3,2),(4,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table articles
    -> (id int not null,
    -> topic int not null,
    -> featured int not null
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into articles values
    -> (1,1,1),(2,2,0),(3,3,1),(4,4,1),(5,5,1);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Here it is loaded
mysql> select * from articles order by id;
+----+-------+----------+
| id | topic | featured |
+----+-------+----------+
|  1 |     1 |        1 |
|  2 |     2 |        0 |
|  3 |     3 |        1 |
|  4 |     4 |        1 |
|  5 |     5 |        1 |
+----+-------+----------+
5 rows in set (0.00 sec)

mysql> select * from topics order by id;
+----+--------+
| id | parent |
+----+--------+
|  1 |   NULL |
|  2 |      1 |
|  3 |      2 |
|  4 |      2 |
|  5 |   NULL |
+----+--------+
5 rows in set (0.00 sec)

mysql>

STORED PROCEDURE
DELIMITER $$

DROP FUNCTION IF EXISTS `ayman`.`GetFeaturedArticles` $$
CREATE FUNCTION `ayman`.`GetFeaturedArticles` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN

    DECLARE rv,rvlist,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';
    SET queue = GivenID;
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        SET front_id = FORMAT(queue,0);
        IF queue_length = 1 THEN
            SET queue = '';
        ELSE
            SET pos = LOCATE(',',queue) + 1;
            SET q = SUBSTR(queue,pos);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;

        SELECT IFNULL(qc,'') INTO queue_children FROM
        (
            SELECT GROUP_CONCAT(AA.id) qc
            FROM articles AA INNER JOIN topics BB
            ON AA.topic = BB.id
            WHERE BB.parent = front_id
        ) A;
        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_children;
            ELSE
                SET rv = CONCAT(rv,',',queue_children);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    SET rvlist = CONCAT(GivenID,',',rv);
    SET rv = '';
    SET @comma = '';

    #
    # Strip Out Non-Featured Articles
    #
    SET @searching = 1;
    WHILE @searching  = 1 DO
        SET @searching = (LOCATE(',',rvlist) > 1);
        SET front_id = FORMAT(rvlist,0);
        SELECT COUNT(1) INTO @ItsFeatured FROM articles WHERE id = front_id AND featured = 1;
        IF @ItsFeatured = 1 THEN
            SET rv = CONCAT(rv,@comma,front_id);
            SET @comma = ',';
        END IF;
        SET pos = LOCATE(',',rvlist) + 1;
        SET q = SUBSTR(rvlist,pos);
        SET rvlist = q;
    END WHILE;

    RETURN rv;

END $$

DELIMITER ;

Here is the execution of it
mysql> select * from articles;
+----+-------+----------+
| id | topic | featured |
+----+-------+----------+
|  1 |     1 |        1 |
|  2 |     2 |        0 |
|  3 |     3 |        1 |
|  4 |     4 |        1 |
|  5 |     5 |        1 |
+----+-------+----------+
5 rows in set (0.00 sec)

mysql> select * from topics;
+----+--------+
| id | parent |
+----+--------+
|  1 |   NULL |
|  5 |   NULL |
|  2 |      1 |
|  3 |      2 |
|  4 |      2 |
+----+--------+
5 rows in set (0.00 sec)

mysql> select GetFeaturedArticles(1);
+------------------------+
| GetFeaturedArticles(1) |
+------------------------+
| 1,3,4                  |
+------------------------+
1 row in set (0.03 sec)

mysql>

I borrowed this code from my earlier post : Find highest level of a hierarchical field: with vs without CTEs
The Stored Procedure first collects all the descendants of a given article's topic hierarchy. Then, it iterates through the return list and strips non-featured articles.
Give it a Try !!!


Improve Performance -Update query with OR in WHERE

The best option if you really want to squeeze out the most is to parallelise the query.

Instead of a single UPDATE, run multiple copies. Each of them operate on a subset of the data in the temporary table. Keep adding more copies and more subsets until you run out of cores of CPU (whatever happens first).

If you don't have the ability to parallelise. Run two UPDATES, one that works on the first part of the OR, another on the second. If possible, add indexes on t_h_bg_pc_it to support both queries. Like this:

Declare @date datetime = '09-Dec-2013'
update  #tmp_sp_abc
set     test = rtrim(xyz_test)
from    #tmp_sp_abc t1
join    t_h_bg_pc_it t2
  on    t2.id_i = t1.i
where   t1.r_type = 1
and     t2.[date] = @date

update  #tmp_sp_abc
set     test = rtrim(xyz_test)
from    #tmp_sp_abc t1
join    t_h_bg_pc_it t2
  on    tt2.id_s1 = t1.s
where   t1.r_type = 1
and     t2.[date] = @date

You can now index t_h_bg_pc_it on both s, date, xyz_test and id_id, date, xyz_test (two indexes). This should speed things up. It may also be useful to index #tmp_sp_abc on r_type. If possible, do both the parallelism and the indexing/rewrite.

Finally: Fire whoever it is that comes up with naming conventions for your tables. Life is too short for this type of cryptic naming, SQL Server does auto-completion.


How to Retrieve value from a User-Defined Table Type (UDTT)

You need to create the type inside the database where you will declare it - data types can not be reached across databases or servers. This is no different from creating a local variable using an alias type - it has to exist in the context of the current database (or your code has to execute in the context of the database where the type exists, e.g.

EXEC dbname.sys.sp_executesql N'DECLARE @t dbo.type;
  INSERT @t(cols) SELECT whatever...;
  -- do other stuff but it has to be in this dynamic SQL block';


Separate table for 'scheduled payments' and 'completed payments'?

There is one entity - financial transaction, that can be in a various states. Completed, scheduled, postponed, rolled back etc. State is the property of the entity, only part of the whole, like the field is a part of the row.

When the state is just a field of the table it is possible to create DB structure and code that are invariant regardless of how many states are possible. In the opposite case each new state produce the new table and lot of code should be rewritten to reflect the changes.


How to most efficiently find out if a record has child records?

Don't forget that Postgres has a boolean datatype. The following is the most succinct way to express the query:

select
  parent_id,
  name,
  exists (select from child where parent_id = p.parent_id) as has_children
from parent p;

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=86748ba18ba8c0f31f1b77a74230f67b


Alias Names and Two Table Query Problems

Oracle does not support using the AS keyword for defining a table alias, only for column aliases. Just rewrite the query by placing a space between the table name and the alias and see if it runs that way.

In particular rewrite the FROM clause like this:

 FROM anc_test_codes ats,
   anc_results_seqs ars


Postgres Query to retrieve locations within a given distance

You can repeat the expression in the filter condition, or you can avoid that by using a subquery:

SELECT *
FROM (SELECT *,
             3959 * acos(
                       cos(
                          radians('13.1')
                       ) * cos(
                          radians(lat)
                       ) * cos(
                          radians(lng) - radians('53.1')
                       ) + sin(
                          radians('13.1')
                       ) * sin(radians(lat))
                    ) AS distance
      FROM person) AS q
WHERE distance < 10
ORDER BY distance LIMIT 20;


I don't believe this info appears in any v$ or dba views. Using auditing is the only method.


Is it possible to scale-out with Microsoft SQL Server?

You've kinda got a bunch of questions in here:

It is possible to scale out READS in Azure SQL DB? Yes.

Depending on the service you use, like Azure SQL DB's Active Secondary Replicas, your read workload can automatically be scaled out across multiple servers without big changes to your application code. However, you do still need to add a separate connection string in your application specifying ApplicationIntent=ReadOnly so that they know it's safe to move your read query over to a readable replica.

Is it possible to scale out WRITES in Azure SQL DB? Yes, if you change code.

The document you linked to, Data Partitioning, is a set of design guidelines that you can use when you code your application. However, those are indeed code changes, not a feature of Azure SQL DB that you just flip on the same way you flip on secondary replicas.

It's up to you to design a layer of code so that when your app needs to run a query, it connects to the appropriate location.

Think of it like the World Wide Web: at Stack Overflow, write workloads are separated out across StackOverflow.com, DBA.StackExchange.com, ServerFault.com, and other sites. However you, the user, have to know which site has the data you want to write to - you can't post an answer to this question over at ServerFault.com. That logic is built into your brain - and the same logic needs to be built into your app if you want to scale out writes across multiple servers.

There was a brief moment in time where Microsoft thought they'd do this work for you with Azure SQL DB Federations, but that was quickly deprecated.

Are the same things possible with SQL Server? Yes.

Scaling out reads is as easy as:

Buying more SQL Servers and building them into an Availability Group
Adding another connection string in your app specifying ApplicationIntent=ReadOnly
Profit!

Scaling out writes isn't that easy, and requires code changes just like it does in Azure SQL DB.

Does either of those do true load balancing? No.

Neither Azure SQL DB nor SQL Server Always On Availability Groups truly balance load across multiple secondaries. You can get into a situation where one replica is running a handful of awful queries, and the rest are sitting around idle. And in that situation, you can still end up getting new queries assigned equally to both the underworked and overworked servers.

Doing true load balancing - keeping the work requirements similar across multiple replicas - is left as an exercise for the reader.


That warning is quite real and correct. Why??

The warning is meant to have DBA and Developer pick off anachronisms.

What's an anachronism ? Think of these analogies:

Having a cellphone in the year 1930
Using in a LapTop in 1976
Having a Coin with the Year 5 B.C. (would probably be fake)

In essence, it is having an object that exists before its time.

Look at the query. The first part of the INSERT to be evaluated would be the

(SELECT id FROM Role
  WHERE `Code` = 'CUSTOMER')

In a high write DB environment, if the id was inserted into the Role table in one DB connection and you executed the INSERT in another DB Connection, you could get a race condition. How?

If the Customer Role was INSERTed into the Role table first, you get a numeric value for the id
If the Customer Role was INSERTed into the Role table last, you get a NULL for the id

This situation, governed by the order the entries were made, would yield inconsistent results should you ever try to recover from the binary logs. This situation is even more evident when doing Master/Slave Replication because the Slave could theoretically be inconsistent for this reason.

In your particular case, you can breathe a sigh of relief because you, the Developer/DBA, already predefined the role CUSTOMER in the Role table. You need not worry, right ???

Therefore, that warning is just a reminder for everyone to streamline their input of data.

You could ignore that particular warning.

However, for the sake of clarity, or if you want the error log to stop growing due to the warning, you should do this:

STEP 01

Make the Stored Procedure as follows:

DELIMITER $$

USE `MobiFit_Dev` $$

DROP PROCEDURE IF EXISTS `User_Signup` $$

CREATE DEFINER = `root` @`localhost` PROCEDURE `User_Signup` (
  email VARCHAR (250),
  hashedPassword BINARY(60),
  User_Role INT,
  firstName VARCHAR (100),
  lastName VARCHAR (100),
  gender ENUM ('Male', 'Female'),
  dateOfBirth DATE,
  height DECIMAL (3, 2),
  currentWeight DECIMAL (4, 1)
)
BEGIN
  INSERT INTO USER (
    Email,
    HashedPassword,
    RoleId,
    FirstName,
    LastName,
    Gender,
    DateOfBirth,
    Height,
    CurrentWeight,
    CreatedAt
  )
  VALUES
    (
      email,
      hashedPassword,
      User_Role,
      firstName,
      lastName,
      gender,
      dateOfBirth,
      height,
      currentWeight,
      UTC_TIMESTAMP()
    ) ;
END $$

STEP 02

When calling the Stored Procedure, retrieve the role_id and pass it into the Stored Procedure:

SELECT id INTO @GivenRoleID FROM Role  WHERE `Code` = 'CUSTOMER';
User_Signup('lee3@gmail.com', 'password', @GivenRoleID, 'Lee', 'Brooks',
null, null, null, null);

Give it a Try !!!


Mysql table crash, what do I need to do to fix this

This is a MyISAM table. It is easy to fix. Login to mysql and run this

mysql> REPAIR TABLE mysql.proc;

and you should be good to go.

BTW this is where stored procedure are stored.


Configure Oracle user in Enterprise Manager to CREATE VIEW on any of his tables

To query data from the tables owned by an user, the user does not need any additinoal privileges. Your script fails because the user does not have the privilege to create a view.

GRANT CREATE VIEW TO user;

Note: privileges granted through a role have no effect in a PL/SQL script.


Best way to model the relationship of unique pairs

This may not be needed and I am not totally sure I have the best answer.  I was not able to come up with anything that utilized some kind of hash or other type of comparison mechanism.  I am also running in SQL Server so I don't have the ability to use a least() and greatest() function.  See this DBA Stack Exchange Question.
I have however done some performance testing with a few different methods.  What data I captured is below:
+---------------------------------------------------------+-----------+--------------------+--------------------+--------------+----------------+-----------------------------+
|                                                         |           | Instead of Trigger | Instead of Trigger |              | After Trigger  | Sum and Absolute Difference |
|                          Event                          | Baseline  |  Case Statements   |     Not Exists     | Indexed View | Only Completed | Computed Persisted Columns  |
+---------------------------------------------------------+-----------+--------------------+--------------------+--------------+----------------+-----------------------------+
| Execution Time                                          | 07:06.510 | 13:46.490          | 08:47.594          | 22:18.911    | 30:38.267      | 11:24:38                    |
| Query Profile Statistics                                |           |                    |                    |              |                |                             |
|   Number of INSERT, DELETE and UPDATE statements        | 125250    | 249500             | 499000             | 250000       |                | 249999                      |
|   Rows affected by INSERT, DELETE, or UPDATE statements | 124750    | 249500             | 374250             | 124750       |                | 124750                      |
|   Number of SELECT statements                           | 0         | 0                  | 0                  | 0            |                | 0                           |
|   Rows returned by SELECT statements                    | 0         | 0                  | 0                  | 0            |                | 0                           |
|   Number of transactions                                | 125250    | 249500             | 499000             | 250000       |                | 249999                      |
| Network Statistics                                      |           |                    |                    |              |                |                             |
|   Number of server roundtrips                           | 1         | 250000             | 250000             | 250000       |                | 250000                      |
|   TDS packets sent from client                          | 6075      | 250000             | 250000             | 250000       |                | 250000                      |
|   TDS packets received from server                      | 462       | 250000             | 250000             | 250000       |                | 250000                      |
|   Bytes sent from client                                | 24882190  | 62068000           | 62568000           | 59568000     |                | 61567990                    |
|   Bytes received from server                            | 1888946   | 76910970           | 8782500            | 67527710     |                | 69783720                    |
| Time Statistics                                         |           |                    |                    |              |                |                             |
|   Client processing time                                | 420901    | 269564             | 18202              | 240341       |                | 238190                      |
|   Total execution time                                  | 424682    | 811028             | 512726             | 1325281      |                | 665491                      |
|   Wait time on server replies                           | 3781      | 541464             | 494524             | 1084940      |                | 427301                      |
+---------------------------------------------------------+-----------+--------------------+--------------------+--------------+----------------+-----------------------------+

This data would suggest one of 3 options:

Assuming you are ok with the use of an IDENTITY column as the Primary Key, then the best performing method appears to be the INSTEAD OF Trigger - NOT EXISTS.
Assuming you are not ok with the use of an IDENTITY column as the Primary Key, and you are ok with the existence of 2 other Persistent Computed Columns then the best performing method appears to be the Persistent Computed Columns.
Assuming you are not ok with the use of an IDENTITY column as the Primary Key, and you are not ok with the existence of 2 other Persistent Computed Columns then the best performing method appears to be the INDEXED VIEW

Background on the overall testing method
I created a thing table with the same schema as you provided, and populated it with each INT from 1-500.  Then I scripted out a bunch of single INSERT statements for each combination of thing (a GO was utilized so that when a given entry failed for either of the checks that the rest of the script would run and we could collect the statistics for the entire process).
INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(1, 1, 1 * 1)
GO

INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(1, 2, 1 * 2)
GO

INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(1, 3, 1 * 3)
GO
...
INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(500, 498, 500 * 498)
GO

INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(500, 499, 500 * 499)
GO

INSERT INTO relationship (thing_one, thing_two, relationship) VALUES(500, 500, 500 * 500)
GO

Done correctly this results in 124,750 total records added from 250,000 total attempts.
This process was repeated for 4 different methods to see what performance was like.  I also ran a "baseline" query which only had the INSERT statements for the unique combinations.  That way we had some idea on how fast it could possibly get in the framework that the test is setup.
Details on Each Method

INSTEAD Trigger - Case expressions to sort input values for thing_one and thing_two.

With this implementation we were going to take the provided data, ensure that the smaller value of thing_one and thing_two was ultimately placed into the thing_one column and the other (the larger of the two) was placed into the thing_two column.  From there the Primary Key would ensure only the unique values would remain.
NOTE: Because with this implementation we are doing uniqueness on the Primary Key (which I think is the correct table structure), it is difficult to handle UPDATE statements via an INSTEAD Trigger.  This was asked in a previous Stack Overflow Question and the basic outcome is that you either need a different method, or an Identity column.  I don't personally think it is a great idea to add an Identity column if there is a natural primary key as it sounds like you have here.
The Trigger Code:
CREATE TRIGGER InsteadOfInsertTrigger on [dbo].[relationship]
INSTEAD OF INSERT
AS
INSERT INTO [dbo].[relationship]
(
    thing_one,
    thing_two,
    relationship
)
SELECT
CASE
    WHEN I.thing_one <= I.thing_two
        THEN I.thing_one
    ELSE
        I.thing_two
    END
,CASE
    WHEN I.thing_one <= I.thing_two
        THEN I.thing_two
    ELSE
        I.thing_one
    END
,I.relationship
FROM inserted I
GO

INSTEAD Trigger - NOT EXISTS check

This is a trigger that stopped the INSERT if the relationship already existed.  The values going into thing_one and thing_two are not sorted, but hopefully that isn't a problem.  Just like with the previous Trigger this still has the same pitfall when it comes to UPDATES.
The Trigger Code:
CREATE TRIGGER InsteadOfInsertTrigger on [dbo].[relationship]
INSTEAD OF INSERT
AS
INSERT INTO [dbo].[relationship]
(
    thing_one,
    thing_two,
    relationship
)
SELECT
I.thing_one
,I.thing_two
,I.relationship
FROM inserted I
WHERE NOT EXISTS
(
    SELECT 1
    FROM [dbo].[relationship] t
    WHERE (t.thing_one = i.thing_two AND t.thing_two = i.thing_one)
    --This one shouldn't be needed because of the Primary Key
    --AND (t.thing_one = i.thing_one AND t.thing_two = i.thing_two)
)
GO

Unique Indexed View

With this method, we created a View and put a Unique Clustered Index on it.  If a duplicate record is added then it fails this check and the change is rolled back.  I saw 2 ways of doing this, either with a CASE expression like I have below, or some kind of UNION.  In my testing the CASE performed much better.
The View and associated Index Code:
CREATE VIEW dbo.relationship_indexedview_view
WITH SCHEMABINDING
AS
    SELECT
    CASE
        WHEN thing_one <= thing_two
            THEN thing_one
        ELSE
            thing_two
        END as thing_one_sorted,
    CASE
        WHEN thing_one <= thing_two
            THEN thing_two
        ELSE
            thing_one
        END as thing_two_sorted
    FROM [dbo].[relationship_indexedview]
GO

CREATE UNIQUE CLUSTERED INDEX relationship_indexedview_view_unique
    ON dbo.relationship_indexedview_view (thing_one_sorted, thing_two_sorted)
GO

AFTER INSERT and UPDATE Trigger

Here we have another TRIGGER implementation that will handle both INSERT and UPDATES.  After the INSERT or UPDATE has finished, it checks to see if a duplicate value has been added, and performs a ROLLBACK if it finds one.
NOTE: This method performed super poorly.  I stopped it after ~30 minutes of running and it only added 51,378 of the expected 124,750 rows (~24% of the INSERT commands executed).
Trigger Code:
CREATE TRIGGER AfterTrigger ON [dbo].[relationship]
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS
    (
        SELECT 1
        FROM [dbo].[relationship] T1
            INNER JOIN [dbo].[relationship] T2
                ON T1.thing_one = T2.thing_two
                AND T1.thing_two = T2.thing_one
    )
    BEGIN
        RAISERROR ('Duplicate Relationship Value Added', 16, 1);
        ROLLBACK TRANSACTION; --stops the Insert/Update
    END
END
GO

Sum and Absolute Difference Comparison using Physical Computed Columns

After getting confirmation from this Math Stack Exchange Question.  We know that a given relationship (thing_one, thing_two) or (thing_two, thing_one) can be tested as unique by looking at the sum and the absolute value of their difference.  We can create 2 Computed Persisted Columns and create a Unique Constraint.
With a small modification to the table schema we can ensure the uniqueness without having to modify the INSERT script.
The only downside is having to maintain 2 more columns on the table. As long as that is ok, this appears to be one of the smallest amounts of overhead and does not have the same pitfalls of having to deal with the Primary Key changes, as found with the TRIGGER based methods.
This presumably could be pushed to a separate table, or some other indexed view, but I have not done any testing with that.
CREATE TABLE relationships (
    thing_one INT REFERENCES thing(id),
    thing_two INT REFERENCES thing(id),
    thing_one_thing_two_sum AS thing_one + thing_two PERSISTED,
    thing_one_thing_two_absolute_difference AS ABS(thing_one - thing_two) PERSISTED,
    relationship INT NOT NULL,
    PRIMARY KEY (thing_one, thing_two),
    CHECK (thing_one != thing_two),
    UNIQUE(thing_one_thing_two_sum, thing_one_thing_two_absolute_difference)
);

Hopefully this helps with the design decision or is at least an interesting read.


Internal query on CDEF$ and CON$ very CPU bound

Browse My Oracle Support for the possible causes. For example quick search revealed a bug about your Oracle internal SQL. Workaround for it is:

Set optimizer_mode=rule for the affected DMLs

The bug is registered against version 11g but of course it is possible that it exists also in 10g.


There is no such a thing as "best option". If page would always be superior, why bother offering row as sub-par alternative or vice versa? It really depends on a lot of things, so further analysis is needed. Start by reading some materials about data compression. MS has published a technical article Data Compression: Strategy, Capacity Planning and Best Practices that's a bit old, but fits your Sql Server version.

By the way, Sql Server 2008 has been out of mainstream support since Q2 2014 (and this is assuming you are running the latest SP4 patchlevel). You really should consider upgrading into more recent a version of Sql Server.


Cannot retrieve values from LEFT JOIN (2 tables)

a_horse_with_no_name already provided with the answer.

But to disable this "one of those stupid MySQL "features" that no one can really understand", you can set sql_mode to be more strict about it. Consider the following setup in your my.cnf config file:

[mysqld]
sql_mode = ONLY_FULL_GROUP_BY

See documentation. This will not allow you to reference non-grouped columns unless with an aggregation function.


Ola Hallengren's maintenance solution has several parts:

Database backups - you don't usually want to use these in Managed Instances because you can't restore the backups to another location, like on-premises. The only use for database backups is to restore them into another managed instance - like if you're doing it for disaster recovery, backing them up to blob storage, and then syncing that blob to a different Azure account (in case you're worried about a malicious actor closing your Azure account, and leaving you with no backups.) If you do want to use backups for that purpose, you can use the @Url parameter for the DatabaseBackup proc, and back up to a URL.

Integrity checks - these work the same way they work for normal SQL Server - no change for Ola's scripts here.

Index and statistics maintenance - these also work the same way they'd work on-premises, but be aware that with the limited transaction log throughput of Azure SQL DB and its relatives, you probably want to use the @Delay parameter when you rebuild/reorg indexes so that you slow down the amount of transaction log throughput you're generating, and leave some DTUs for user transactions.

Parameters common to all procs - don't try to log them to the file system, because you simply don't have access to the file system.


How can I select data and the total to cursors?

An alternative to msi77's solution is the use of analytical functions. This might be a bit more efficient but you should run an explain plan to find out.

SELECT id,
       name,
       count(*) over () as total_count
FROM your_table
WHERE name = 'AA'


Is MySql's LAST_INSERT_ID() function guaranteed to be correct?

A couple caveats I'd like to point out when using LAST_INSERT_ID:

I know you mentioned single-row inserts. But when doing multiple-row inserts, LAST_INSERT_ID() will return the value of the first row inserted (not the last).
If the insert failed, LAST_INSERT_ID() would be undefined. The same is true for automatic rollbacks of transactions (due to errors).
If you do an insert in a transaction that succeeds, and you still issue a ROLLBACK, LAST_INSERT_ID() would be left as it was prior to the rollback.
There are a couple caveats when using AUTO_INCREMENT and LAST_INSERT_ID in statement-based replication. The first being when used in a trigger or function. The second being the less-common scenario where your auto_increment column is part of a composite primary key and is not the first column in the key.


How to speed up an insertion from a huge table with postgres?

.. a new table that contains all columns of all tables ..

Really?  That's unlikely to be useful.

  select .. from t1 join t2 join t3 join t4 ...

Without "on" clauses to tell postgres how to put the tables together, those 'join's will link every row in t1 to every row in t2 and then every row of that to every row in t3 and then every row of that to every row in t4 ...

That's an awful lot of "rows".

As always, build a select statement that returns the data that you want, then just add the "insert into" bit on the front of it.

If your tables are all the same structure, I'd suspect you'll be better served by 'union [all]'s instead of 'join's but YMMV; we can't tell because you haven't given us the structure of your tables or your intended result.


How do you know how long a room is booked for?  Your model indicates when a booking starts, but when does it end?

Also, your ERD shows foreign keys from BOOKING to USER and ROOM but these columns aren't acknowledged in your BOOKING table.  Some other answers have interpreted that as the columns being missing.  You should be explicit in your diagram to avoid confusion (or add the FK columns if you missed them!)


How to prevent trigger from overwriting values that were explicitly set?

No way to determine does some field was set in UPDATE query text. You may operate with NEW.updated and OLD.updated values only.

Actions table:

OLD.updated    NEW.updated    Action
------------------------------------
NULL           NULL           nothing
NULL           now()          nothing
NULL           NOT now()      nothing
now()          NULL           NEW.updated=OLD.updated
now()          now()          nothing
now()          NOT now()      ?
NOT now()      NULL           NEW.updated=OLD.updated
NOT now()      now()          NEW.updated=OLD.updated
NOT now()      NOT now()      NEW.updated=OLD.updated

And compacted form:

OLD.updated    NEW.updated    Action
------------------------------------
NULL           any value      nothing
NOT now()      any value      NEW.updated=OLD.updated
now()          NULL           NEW.updated=OLD.updated
now()          now()          nothing
now()          NOT now()      ?

Define the action unknown to me. Then convert this table to proper If-ElseIf-Else form...

PS. now() means that the difference between field value and current timestamp is negligible small.

--

UPDATE

The final expression will be like:

IF   (OLD.updated != NOW())
  OR (     (OLD.updated = NOW())
       AND (NEW.updated IS NULL)
     )
THEN SET NEW.updated=OLD.updated
ELSEIF     (OLD.updated = NOW())
       AND (NEW.updated != NOW())
THEN ?????
END IF;


Can't start system_health extended events session

First, identify the folder that is the problem. In SSMS, with results set to grid, run the following query:

SELECT target_data = CONVERT(xml, target_data)
  FROM sys.dm_xe_session_targets
  WHERE target_package_guid = '5B2DA06D-898A-43C8-9309-39BBBE93EBBD'
  AND target_name = N'event_file';

Should return one row, and you should be able to click on it from the grid result. This will open a new tab with an XML document, which should look something like this:

<EventFileTarget truncated="0">
  <Buffers logged="0" dropped="0" />
  <File name="C:\...path_to_xe_files...\system_health_0_131278486394000000.xel" />
</EventFileTarget>

If the file name does not include a path component, the Extended Events default file location can be determined by looking in the system registry.  This code should do that:

EXEC master.sys.xp_instance_regread
       @rootkey      = N'HKEY_LOCAL_MACHINE'
       , @key          = N'SOFTWARE\Microsoft\MSSQLServer\CPE'
       , @value_name   = N'ErrorDumpDir';

Once you have the path where the Extended Events files are being saved, try to do something like this:

BACKUP DATABASE model TO DISK = 'C:\...path_to_xe_files...\model_trash.bak'
  WITH INIT;

This should give you a better idea of what the problem is - could be permissions, could be that the folder doesn't exist (maybe the configuration for the instance was moved), could be a whole slew of things. But this should point you in the right direction and get you a better error message.


How does MOVE COMPRESS affect future data?

Your DBA was kind of correct, for the typical scenario.

MOVE COMPRESS compresses existing data with Basic compression.
Any new data inserted with regular insert will be uncompressed. New data inserted with direct-path insert will be compressed.

If you want all new data to be compressed, then you can use OLTP compression. Then syntax for that is COMPRESS FOR OLTP. OLTP compression requires extra license as it is part of the Advanced Compression option.


For multi-million row tables, you really need to consider normalizing columns wherever practical.  For example, there may be only a few thousand distinct user_agent values, you you are saving dozens or hundreds of bytes for multiple copies.  using a SMALLINT UNSIGNED linked to another table would shrink that to 2 bytes.

Why worry about space?  It has some impact on speed.  It forestalls running out of disk space.

Be consistent, and use reasonable datatypes:  If you won't have more than, say, 64K users, declare user_id in both tables as SMALLINT UNSIGNED.

What language needs 255 characters to describe?  Consider using an ENUM.

IP addresses, even IPv6, can be compressed into BINARY(16) (16 bytes) instead of the up-to-39 you currently have.  (And 255 is overkill.)

20 INSERTs per second is no problem for MySQL.
What is the API hitting?  A web server?  REST?  Java?  Can that handle 20/second?

What will the PRIMARY KEY be?  What SELECTs will be applied to the dataset?  These need to be discussed to talk about indexes, and whether you will have performance problems when reading.

Max number of records?  You will run out of disk space before you hit any database limit.  I think there is a limit of 64TB.  I've seen a billion-row table in action.

Do the math.  When will you run out of disk space?  Will you drop "old" data after some period of time?  If so, let's talk about an efficient way to do that -- else you will come back with a problem of a huge DELETE.


As Shanky already commented, running scheduled shrinks is wrong. There is ton of info online about it. Find the root cause of that growth and solve it. I would get rid of it immediately.

As for the error, seems related to db ownership. If you run a search with the error text your will find several related questions here, here and more.

As a recommendation, I would suggest switching to Ola Hallengren maintenance solution for administrative tasks as DBCC, index maintenance, backups, etc.


Weird issue with the order of clauses seeming to matter

OK, just to follow up my correct (yay!) comment answer - in my experience almost everything along the lines of "weird issue" involving data selection, involves a rogue, unparenthesised "OR" somewhere in the where clause. It's always the first place I'd look.

Typically you write some code along the lines of:

WHERE a
AND b OR c
AND d

and then when you look at the code your mind kind of fills in the parenthesis where you think it ought to be. Whereas order or precedence rules actually perform it thus:

WHERE (a AND b) OR (c AND d)

A good trick to use when coding, which I tend to do automatically now, is as soon as you use an "OR" anywhere in your code, then add some parenthesis to make it totally clear.

Even if your where clause is simply:

WHERE a OR b

Then explicitly change it to

WHERE ( a OR b )

since, you're future-proofing against someone adding to the code later and accidentally writing something like:

WHERE a OR b
AND c


Oracle 11gR2 - How To Audit SYS.USER$ Table

In Oracle 11g this cannot be audited. There are ways of doing it in 12c and up. That said, only users with the "select any dictionary" privilege should be able to select from that table, and anyone with that privilege should be trusted.


updating values in one table if depending table row is affected

Not sure if this will fit in your situation, but a mysql VIEW should work for you, and make things easier.

You could create a VIEW called product_table, based on a select over subscriber_table, like this:

CREATE VIEW product_table AS SELECT item_id, product_id, COUNT(*) AS
count_subscriber  FROM subscriber_table GROUP BY item_id, product_id

Using this, you will not need to update manually product_table data on every change made over subscriber_table.


Database Entity Design for Enumeration Table

If there was any sort of choice to be made by your designers or users in the selection of these values I would go for the surrogate ID approach. What has been decided can be un-decided and the change will be easier with surrogates in place.

Since you are adamant the values are fixed by a Law Of Nature (or, at least, some external authority) I would be OK with using the single-column approach. In these circumstance the chances of change, and the wider impact, mean you'll likely have sufficient notice to effect the application update.


How can I use Count() or Sum() with advanced criteria such as matching the most recent rows where a condition is true?

Using a common table expression for Max(RecordDate) might help.

Not sure if this code is just what you need, but you can modify. The script works on the sample table that I created from your question.

DECLARE @StartDate datetime = '2019-05-10 08:00:00';
DECLARE @EndDate datetime = '2019-05-24 09:00:00';
DECLARE @StageID int = 6;

WITH MyRecentSerialNumber AS
    (SELECT ID, MAX(RecordDate) AS MaxRecordDate,   SerialNumber,   ProductID,  StageID,        Result
    FROM t1
    WHERE Result = 1
    GROUP BY ID, SerialNumber, ProductID, StageID, Result)
SELECT ProductID, COUNT (*) 'Total',
    SUM (CASE WHEN Result = 1 THEN 1 ELSE 0 END) 'Output'
FROM MyRecentSerialNumber
WHERE MaxRecordDate BETWEEN @StartDate AND @EndDate AND StageID = @StageID
GROUP BY ProductID;

Output is the same with the sample data, but you can test and adjust with data that should be excluded.


Isolating access to tenant data in a multi-tenant database

I would probably provision "tenant support identity" (user or role) and have process where a support engineer is provisioned access to a single tenant, through temporary membership in the role, or by giving the support person the password to a temporarily-enabled user.

The developers would add stored procedures that manage the tenant support identity, along with perhaps some auditing.

Then the only difference between a single-tenant and multi-tenant database is that you have to implement RLS for the tenant support identity.


Getting the syntax right for MySQL select with inner join

Yes, problem one is that you are mixing explicit (SQL-92) JOIN syntax with implicit join, , (SQL-89) comma syntax. While this is allowed and with some care it may even work correctly, it's very easy to get this type of errors.

The FROM part of your query:

from courses as co , course_type as ct
left join course_leaders as cl
    on cl.course_leaders_id = co.course_leaders_id1
left join course_links_to_innstyle as cli
    on cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id

is parsed as:

from
    courses as co
  ,                        -- <-- that acts as a CROSS JOIN
    (
        (
            course_type as ct
        left join
            course_leaders as cl
        on
            cl.course_leaders_id = co.course_leaders_id1
        )
    left join
        course_links_to_innstyle as cli
    on
        cli.course_links_to_innstyle_id = co.course_links_to_innstyle_id
    )

Now it's clear that inside the parentheses, co is not defined.

Problem two is that there is no joining condition for ct. You have 4 tables but only 2 (and not 3) joining conditions. This is either something you missed or the intention of the query is to do a CROSS JOIN.

Solution:

It's better to use explicit JOIN syntax everywhere and not mix the two (syntaxes).
Fix the code by either adding the missing condition or use CROSS JOIN if that is the intention of the query.


Does logical reads in RAM appears in wait stats or where?

A worker goes on a waiter list when it needs to wait for some kind of resource. There is no resource to wait on for logical reads. Assuming that your buffer pool hasn't paged out the data is already in memory managed by SQL Server. Queries that do lots of logical reads are slow because they spend a lot of CPU doing those logical reads.

If you want to get an idea of what internal operations SQL Server is doing when performing logical reads then you could do ETW tracing using perfview or some other tool while running a query which is bottlenecked by logical reads. ETW tracing might be a good step if you believe that your workload has issues with memory throughput. Please note that this is probably not the case and the following demo is almost certainly the wrong way to approach a query performance issue.

First I'll hack together a query which I expect to spend most of its time doing logical reads:

DROP TABLE IF EXISTS #215016;

SELECT 1 ID, REPLICATE('Z', 1000) FILLER INTO #215016
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

SELECT t1.ID, t2.ID
FROM #215016 t1
INNER JOIN #215016 t2 ON t1.ID < t2.ID
OPTION (MAXDOP 1, QueryRuleOff BuildSpool);

SQL Server used a full core of CPU throughout the query's execution. Here are perfview results taken from a ten second sample:

Perhaps the circled method name is what you're interested in. For this query SQL Server spends about 5% of its CPU time on sqlmin!BPool::Get, which I assume has to do with actually performing the logical read. In defense of that idea, that method is what calls sqlmin!BUF::AcquireLatch most of the time:

and the number of shared latches taken during the query is about the same as the number of logical reads. But this is just a guess because method names aren't documented by Microsoft and I don't know the full scope of a "logical read".


"Fire" a trigger to populate columns that are NOT NULL?

If you want to set values for NULLable columns, then just set those values, like this:

CREATE OR REPLACE TRIGGER enrollment_bi
BEFORE INSERT ON ENROLLMENT
FOR EACH ROW
BEGIN
  IF :NEW.STUDENT_ID    IS NULL THEN
    :NEW.STUDENT_ID    := 9;
  END IF;
  IF :NEW.SECTION_ID    IS NULL THEN
    :NEW.SECTION_ID    := 9;
  END IF;
  IF :NEW.ENROLL_DATE   IS NULL THEN
    :NEW.ENROLL_DATE   := TO_DATE('30-JAN-99','DD-MON-YY');
  END IF;
  IF :NEW.FINAL_GRADE   IS NULL THEN
    :NEW.FINAL_GRADE   := 9;
  END IF;
  IF :NEW.CREATED_BY    IS NULL THEN
    :NEW.CREATED_BY    := '9';
  END IF;
  IF :NEW.CREATED_DATE  IS NULL THEN
    :NEW.CREATED_DATE  := TO_DATE('09-SEP-99','DD-MON-YY');
  END IF;
  IF :NEW.MODIFIED_BY   IS NULL THEN
    :NEW.MODIFIED_BY   := '9';
  END IF;
  IF :NEW.MODIFIED_DATE IS NULL THEN
    :NEW.MODIFIED_DATE := TO_DATE('01-JAN-00','DD-MON-YY');
  END IF;
END;
/

There is no need to read the table before (certainly not in a FOR loop) - and that is actually forbidden. The trigger executes for each row modified by any SQL statement, and gives you the context of the operation it is invoked on: variable :OLD contains the previous values of the columns (for UPDATE and DELETES) and :NEW the new values (for UPDATE and INSERT). So all you need is to change the values in :NEW if any of them is NULL.

Then again, you do not need a trigger at all: just specify the values you want as defaults when you create the table:

CREATE TABLE ENROLLMENTS (
  STUDENT_ID    NUMBER        DEFAULT 9,
  SECTION_ID    NUMBER        DEFAULT 9,
  ENROLL_DATE   DATE          DEFAULT TO_DATE('30-JAN-99','DD-MON-YY'),
  FINAL_GRADE   NUMBER        DEFAULT 9,
  CREATED_BY    VARCHAR2(20)  DEFAULT '9',
  CREATED_DATE  DATE          DEFAULT TO_DATE('09-SEP-99','DD-MON-YY'),
  MODIFIED_BY   VARCHAR2(20)  DEFAULT '9',
  MODIFIED_DATE DATE          DEFAULT TO_DATE('01-JAN-00','DD-MON-YY')
);

I am guessing the datatypes here - you may have different ones.


Identify sequence of events based on certain rules

This query gives the correct output:

With ord as (
    Select first, last
        , EventNr = ROW_NUMBER() Over(order by first)
    From (
        Select first = t1.EventTime, last = t2.EventTime
            , rn = ROW_NUMBER() Over(partition by t1.ID order by t2.eventTime)
        From #T t1
        Inner Join #T t2 On t2.EventTime > t1.EventTime  and t2.TypeID = @LastTypeID
        Where t1.TypeID = @FirstTypeID
    ) ids
    Where rn = 1
)
Select t.ID, t.EventTime, t.TypeID, o.EventNr From #T t
Inner Join ord o On t.EventTime >= o.first and t.EventTime <= o.last
Order By o.EventNr, t.EventTime

I didn't use Column ID for ordering because it seems to be specific to your samplte.


Removing superfluous tables with a synchronization?

No, there's no way to do this using native functionality as replication doesn't know about the additional objects so it can't remove them.


Does MySQL store point datatypes as LAT LNG or LNG LAT?

Constructor Syntax

Looking up the spatial function reference, you'll see

Point(x, y)
Constructs a Point using its coordinates

This isn't entirely correct. All GIS implementations must do (x,y) for projected coordinates which is  (long,lat). But, on geodidic cordinate systems there is some disagreement about what to do. MySQL (and SQL Server) do (lat,long) but PostGIS maintains (long,lat) everywhere.

This is touched on in the spec OpenGIS®
Implementation Standard for Geographic
information - Simple feature access - Part 2: SQL option,

  For geodetic CRSs having an ellipsoidal 2-D coordinate system, the two-dimensional ellipsoidal coordinate system axes are geodetic latitude, positive northwards, and geodetic longitude, positive eastwards. Axis direction shall be ‘north’ and ‘east’ respectively.
  For geodetic CRSs having a three-dimensional ellipsoidal coordinate system, the name and abbreviation of the horizontal axes in a WKT string shall follow the requirements in (ii). The vertical axis name shall be ‘ellipsoidal height’; the vertical axis abbreviation shall be ‘h’ and should be included when abbreviations for the horizontal axes are included.

Note the above words are found verbatim in Geographic information - Well-known text representation of coordinate reference systems

Even as far back as the 1.1 spec,

  A spatial reference system, also referred to as a coordinate system, is a geographic (latitude-longitude), a projected (X,Y), or a geocentric (X,Y,Z) coordinate system.

That said, it seems PostGIS and Oracle and many third party libraries maintain (x,y,[z]) for all types of points. This is in violation of the spec for WKT, but is a pretty common convention. For example GeoJSON also does this,

  Point coordinates are in x, y order (easting, northing for projected
     coordinates, longitude, and latitude for geographic coordinates)

And, (lat,long) ordering is explicitly defined by EPSG for SRSID 4326,.

  A 2D or 3D coordinate system in which position is specified by geodetic latitude, geodetic longitude and (in the three-dimensional case) ellipsoidal height, used in Geographic CRSs.

You may also consider checking out Paul Ramsey's (Captain PostGIS) blog on this subject entitled, "Let's Call the Whole Thing Off"

Nicities

As a special note, MySQL brings two new functions to the mix,

ST_Longitude()
ST_Latitude()

These functions stand aside from ST_X(), and ST_Y() and require a geographic SRS or they throw an exception and error.

Storage

From the docs

  Internally, MySQL stores geometry values in a format that is not identical to either WKT or WKB format. (Internal format is like WKB but with an initial 4 bytes to indicate the SRID.)

Storing is defined here.

Table 11.1 WKB Components Example

Component       Size    Value
Byte order      1 byte  01
WKB type        4 bytes 01000000
X coordinate    8 bytes 000000000000F03F
Y coordinate    8 bytes 000000000000F0BF


How can I move data from one filegroup to another?

The following code can be used on an ad-hoc basis to move data from one filegroup to another:

CREATE PROCEDURE dbo.MoveDataToCustomFilegroups
(
    @IndexType int
    , @OldFileGroupName sysname
    , @NewFileGroupName sysname
    , @DebugOnly bit = 0
)
AS
BEGIN
    /*
        Description:    MOVE ALL INDEXES of specified @IndexType from @OldFileGroupName
                        to @NewFileGroupName
        By:             Max Vernon
        NOTE:           Should not be used on partitioned tables.
    */
    SET NOCOUNT ON;
    DECLARE @OnlineOption bit;
    DECLARE @msg nvarchar(255);
    DECLARE @cmd nvarchar(max);
    /*
    @IndexType can be
        0 = Heap
        1 = Clustered
        2 = Nonclustered
        3 = XML
        4 = Spatial
    */
    IF NOT EXISTS (SELECT 1 FROM (VALUES (0), (1), (2), (3), (4))v(n) WHERE v.n = @IndexType )
    BEGIN
        SET @msg = N'Invalid @IndexType specified.  @IndexType can be
        0 = Heap
        1 = Clustered
        2 = Nonclustered
        3 = XML
        4 = Spatial
';
        THROW 50000, @msg, 1;
    END
    IF @DebugOnly = 1
    BEGIN
        SET @msg = N'Running in DEBUG mode.  No statements will be executed.';
        RAISERROR (@msg, 10, 1);
    END
    SET @cmd = N'';
    SET @msg = N'';
    SET @OnlineOption = 0;
    IF EXISTS (SELECT name FROM sys.data_spaces WHERE name = @OldFileGroupName)
    BEGIN
        IF EXISTS (SELECT name FROM sys.data_spaces WHERE name = @NewFileGroupName)
        BEGIN
            IF @IndexType = 0
            BEGIN
                /*
                    Heaps require building a clustered index on the target filegroup,
                    then dropping the clustered index.
                */
                SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE CHAR(13) + CHAR(10) END +
                N'CREATE CLUSTERED INDEX [CX_' + o.name + N'_' + (SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id) + N'] ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N' (' + QUOTENAME((SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id)) + N')
    WITH (FILLFACTOR=100, DATA_COMPRESSION=PAGE) ON ' + QUOTENAME(@NewFileGroupName) + N';
    DROP INDEX [CX_' + o.name + N'_' + (SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id) + N'] ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) + N';
    '
                FROM sys.indexes i
                    INNER JOIN sys.objects o on i.object_id = o.object_id
                    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
                    INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
                    INNER JOIN sys.partitions p ON o.object_id = p.object_id AND i.index_id = p.index_id
                WHERE i.type = 0 /* HEAP */
                    AND o.type = 'U' /* USER_TABLE */
                    AND o.is_ms_shipped = 0
                    AND ds.name = @OldFileGroupName;
            END
            ELSE
            BEGIN
                SELECT @cmd = @cmd + CASE WHEN @cmd = N'' THEN N'' ELSE CHAR(13) + CHAR(10) END +

                    CASE WHEN i.is_primary_key = 1 AND i.type = 2
                    THEN N'
    BEGIN TRANSACTION
    BEGIN TRY
        ALTER TABLE ' + QUOTENAME(s.name) + N'.'+ QUOTENAME(o.name) + N'
        DROP CONSTRAINT ' + QUOTENAME(i.name) + N';
        ALTER TABLE ' + QUOTENAME(s.name) + N'.'+ QUOTENAME(o.name) + N'
        ADD CONSTRAINT ' + QUOTENAME(i.name) + N' PRIMARY KEY NONCLUSTERED ('
                    ELSE
                        N'CREATE ' + CASE WHEN i.is_unique = 1 THEN N'UNIQUE ' ELSE N'' END + i.type_desc + N' INDEX ' + QUOTENAME(i.name) + N' ON ' + QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) +
                        N'('
                    END +
                        (
                        SELECT STUFF((
                            SELECT N', ' + QUOTENAME(col_c.name) + CASE WHEN col_ic.is_descending_key = 1 THEN N' DESC' ELSE N'' END
                            FROM sys.indexes col_i
                                INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
                                INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
                            WHERE col_ic.is_included_column = 0
                                AND col_i.object_id = i.object_id
                                AND col_i.index_id = i.index_id
                            ORDER BY col_ic.key_ordinal
                            FOR XML PATH('')
                            ),1,2,'')
                        )
                    + N')' + CHAR(13) + CHAR(10) +
                    CASE WHEN (
                            SELECT COUNT(1)
                            FROM sys.indexes col_i
                                INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
                                INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
                            WHERE col_ic.is_included_column = 1
                                AND col_i.object_id = i.object_id
                                AND col_i.index_id = i.index_id
                            ) > 0
                    THEN
                        N' INCLUDE (' +
                        (SELECT STUFF((
                            SELECT N', ' + QUOTENAME(col_c.name)
                            FROM sys.indexes col_i
                                INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
                                INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
                            WHERE col_ic.is_included_column = 1
                                AND col_i.object_id = i.object_id
                                AND col_i.index_id = i.index_id
                            ORDER BY col_ic.key_ordinal
                            FOR XML PATH(N'')
                            ),1,2,N'')
                        ) + N')'
                    ELSE N''
                    END +
                    CASE WHEN i.has_filter = 1 THEN N' WHERE ' + i.filter_definition ELSE N'' END +
                    N'    WITH (' +
                    CASE WHEN NOT(i.is_primary_key = 1 AND i.type = 2) THEN N'DROP_EXISTING = ON, ' ELSE N'' END +
                    CASE WHEN i.fill_factor > 0 AND i.fill_factor < 100 THEN N'FILLFACTOR = ' + CONVERT(NVARCHAR(3), i.fill_factor) + N', ' ELSE N'' END +
                    N'PAD_INDEX = ' + CASE WHEN i.is_padded = 1 THEN N'ON' ELSE N'OFF' END + N', ' +
                    N'IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 1 THEN N'ON' ELSE N'OFF' END + N', ' +
                    CASE WHEN NOT(i.is_primary_key = 1 AND i.type = 2) THEN N'ONLINE = ' + CASE WHEN @OnlineOption = 1 THEN N'ON' ELSE N'OFF' END + N', ' ELSE N'' END +
                    N'ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 1 THEN N'ON' ELSE N'OFF' END + N', ' +
                    N'ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 1 THEN N'ON' ELSE N'OFF' END + N', ' +
                    N'DATA_COMPRESSION = ' + p.data_compression_desc +
                    N') ON ' +
                    N'[' + @NewFileGroupName + N'];'

                    + CASE WHEN i.is_primary_key = 1 AND i.type = 2
                    THEN N'
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
    END CATCH'
                ELSE
                    N'
    '
                    END
                FROM sys.indexes i
                    INNER JOIN sys.objects o ON i.object_id = o.object_id
                    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
                    INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
                    INNER JOIN sys.partitions p ON o.object_id = p.object_id AND i.index_id = p.index_id
                WHERE o.type = 'U' /* USER_TABLE */
                    AND i.type = @IndexType
                    AND o.is_ms_shipped = 0
                    AND ds.name = @OldFileGroupName
                    --AND i.name = 'AcisC5ModuleLaunc3'
                ORDER BY o.name, i.name;
            END
            IF @cmd = N''
            BEGIN
                SET @msg = N'Warning: no indexes found on ' + @OldFileGroupName + N'!';
                THROW 50000, @msg, 3;
            END
            ELSE
            BEGIN
                RAISERROR (@cmd, 0, 1) WITH NOWAIT;
                IF @DebugOnly = 0
                BEGIN
                    EXEC sp_executesql @cmd;
                END
            END
        END
        ELSE
        BEGIN
            SET @msg = N'ERROR: ' + @NewFileGroupName + N' does not exist!';
            THROW 50000, @msg, 2;
        END
    END
    ELSE
    BEGIN
        SET @msg = 'ERROR: ' + @OldFileGroupName + N' does not exist!';
        THROW 50000, @msg, 2;
    END
END
GO

I wrote a blog post featuring this code at SQLServerScience.com.

The script doesn't automatically move data for new tables.  I wouldn't recommend it, but you could automate that by running the script via a SQL Server Agent job on a schedule.  However, you'd be much better off to use the ON [filegroup] clause in the CREATE TABLE statement to avoid needlessly moving data around.  Check the Microsoft Docs for CREATE TABLE for the details on the ON <filegroup> clause.

An example of a correctly formed CREATE TABLE statement with an ON clause is:

CREATE TABLE [schema].[table]
(
    [column_name] int NOT NULL
) ON [filegroup];

If you define a clustered primary key inline, you could use this syntax:

CREATE TABLE [schema].[table]
(
    [primary_key_column_name] int NOT NULL
         CONSTRAINT [table_pk]
         PRIMARY KEY CLUSTERED
         ON [filegroup]
);


SQL Server Agent/Properties/Alert System/Mail Session/Test is disabled

Test button referred in the question is only available for older version of SQL Servers that supported SQL Mail. Below picture is from a SQL2008 SP4 instance where you can use the test button.

From books online:

  SQL Mail is not installed by default in SQL Server. For more
  information, see Understanding Surface Area Configuration. SQL Mail is
  not supported on 64-bit versions of SQL Server. SQL Mail stored
  procedures cannot be installed on 64-bit versions.

For Database mail you can use this feature for testing.

Using SSMS, Instnace-->Management-->Database Mail-->Right click-->Sent test Email.


How to stop Log Reader Agent from SQL/Command Line

The Log Reader Agent is typically run via a SQL Agent job (unless you have some funky process that kicks it off from the command line). Given this you should be able to just query msdb.sysjobs for the job, and execute msdb.dbo.sp_stop_job @job_name = (your agent job).


Indexing - Uniqueidentifier Foreign Key or Intermediary mapping table?

Ok, I am making a lot of assumptions (INT instead of VARCHAR(50) being one of them) with this answer, so feel free to correct me if needed.  The problem with option B is that it introduces a new join to relate Users to Alerts without any real added benefit.  If joining on the UserID, it is best to index the UserID, so you can utilize seeks for your joins.

For Option A, UserID will be the clustering key (index key for the clustered index) on the Users table.  UserID will be a nonclustered index key on Alerts table.  This will cost 16 bytes per Alert.

For Option B, UserID will be the clustering key on the Users table.  UserId will probably be the clustering key in UserMap too, to make joining more efficient.  UserKey (assuming this is an INT) would then be a nonclustered index key on the Alerts table. This will cost 4 bytes per Alert.  And 20 bytes per UserMap.

Looking at the big picture, one relationship, for Option A, costs 16 bytes of storage, and involves 1 join operation.  Whereas, one relationship, for Option B, costs 24 bytes of storage, and involves 2 join operations.

Furthermore, there are a possibility of  340,282,366,920,938,000,000,000,000,000,000,000,000 uniqueidentifiers and only  4,294,967,296 INTs.  Implementing a uniqueidentifier to INT map for a this type of relationship could cause unexpected results when you start reusing INTs.

The only reason for creating this type map table, is if you plan on creating a Many to Many relationship between Users and Alerts.

Taking all of this into consideration, I would recommend Option A.

I hope this helps,

Matt