Query sometimes hangs (but can be executed in less than 4s when run in parallel) I have a Flask API that has 2 endpoints.
When the user clicks a button, the front-end first calls a /calculate route, which computes an expensive query and appends the results to a table called results.
This table is indexed as follows:
 create index if not exists results_tbl_idx on results
              (customer_id, result_id, team_id, team_name, territory_name, specialty, id);

When /calculate returns, it then gets a handle to the result_id, and then calls a /get_results endpoint, which runs the following query on the results table:
with distinct_users_team_tbl as (
    select team_id,
        count(distinct id) as distinct_users_team,
        count(distinct id) filter ( where targeted_users > 0 ) as targeted_distinct_users_team
    from tam_results_14
    where result_id = 201 and customer_id = '1'
    group by team_id
),
distinct_users_territory_tbl as (
    select team_id, territory_name,
        count(distinct id) as distinct_users_territory,
        count(distinct id) filter ( where targeted_users > 0 ) as targeted_distinct_users_territory
    from tam_results_14
    where result_id = 201 and customer_id = '1'
    group by team_id, territory_name
),
distinct_users_specialty_tbl as (
    select team_id, team_name, territory_name, specialty,
        sum(tam) as tam,
        sum(sam) as sam,
        count(distinct id) as  distinct_users_specialty,
        count(distinct id) filter ( where targeted_users > 0 ) as  targeted_distinct_users_specialty
    from tam_results_14
    where result_id = 201 and customer_id = '1'
    group by team_id, team_name, territory_name, specialty
)

select *
from distinct_users_specialty_tbl
join distinct_users_team_tbl
    on distinct_users_specialty_tbl.team_id = distinct_users_team_tbl.team_id
join distinct_users_territory_tbl
    on distinct_users_specialty_tbl.team_id = distinct_users_territory_tbl.team_id
    and distinct_users_specialty_tbl.territory_name = distinct_users_territory_tbl.territory_name

Sometimes, just after running /calculate, when fetching /get_results, the query hangs forever.
Typically, the /get_results query executes in < 4s. And even while it's hanging, I can paste the exact same query that is hanging into a console and run it in < 4s.
I have checked pg_stat_activity and can see the query running with no wait_event and state = active.
I then checked pg_locks and for that pid, I can see:

My current hunch
Is it that database hasn't finished indexing the new entries to the results table before I query it? But that it has finished adding items to the index by the time I paste the hung query into the terminal and run which can use the index?
Is there some way I can force the table to fully index before running the /get_results query? Is that even possible?
Thanks for any help!!!

Add an explicit
ANALYZE results;

at the end of \calculate so that the other function has accurate table statistics.


How to tell if a backup file contains CDC? Using SQL Server 2008 through to 2014, I know how to backup and restore a database with Change Data Capture enabled (CDC), but what I am looking for is a way to query a full backup file to determine if it contains CDC data.

What would be great would be a way like RESTORE FILELISTONLY that gets the file list from the database, but instead gets the CDC status to determine if the restore will need to use the KEEP_CDC flag?

Is there a way to determine if a backup contains CDC data?

thanks!

(if someone finds a way - which I dont think exists, I will delete my answer :-))

Is there a way to determine if a backup contains CDC data?

I dont think that there is any way to know from a backup, if CDC is enabled or not.
The most you can know if the database was involved in replication (under the hood uses log reader agent that scans the Transaction log) or not by using DatabaseBackupLSN in the result of RESTORE HEADERONLY DatabaseBackupLSN as is the “begin of checkpoint” that is triggered when the backup starts. This LSN will coincide with FirstLSN if the backup is taken when the database is idle and no replication is configured.
I assume the reason that microsoft does not surface whether CDC is enabled or not, might be a security thing as well. CDC is an enterprise level technology that enables tracking of changes all the way down to the column level. This is required by regulations like HIPAA, FISMA, etc.
From Restoring or Attaching a Database Enabled for Change Data Capture

SQL Server uses the following logic to determine if change data capture remains enabled after a database is restored or attached:

If a database is restored to the same server with the same database name, change data capture remains enabled.
If a database is restored to another server, by default change data capture is disabled and all related metadata is deleted.
To retain change data capture, use the KEEP_CDC option when restoring the database. For more information about this option, see RESTORE.
If a database is detached and attached to the same server or another server, change data capture remains enabled.
If a database is attached or restored with the KEEP_CDC option to any edition other than Enterprise, the operation is blocked because change data capture requires SQL Server Enterprise. Error message 932 is displayed:

SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.


Postgresql: How to avoid encoding issues when copying a schema from one server to another? I'm using pg_dump and pg_restore to move a schema from one Postgresql 9.5 server to another. On the destination server:
$ pg_dump -h source.example.com -n my_schema -v --no-owner -F c -f my_schema.dump
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
    LANGUAGE = (unset),
    LC_ALL = (unset),
    LC_CTYPE = "UTF-8",
    LANG = (unset)
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
...
pg_dump: saving encoding = UTF8
(dump completes with no other errors or warnings)

$ pg_restore -h 127.0.0.1 -e -v --no-owner -d my_db my_schema.dump
...
perl: warning: Falling back to the standard locale ("C").
...
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2211; 0 6549333 TABLE DATA mention chicken
pg_restore: [archiver (db)] COPY failed for table "mention": ERROR:  invalid byte sequence for encoding "UTF8": 0xcd 0x2e

Any idea on how to solve this issue? What I want is an exact binary copy of the data. There seems to be some encoding problem which makes me nervous, since what is restored may not be exactly the same as the dump, even if I don't get any errors.

This looks like you are migrating from one OS to another (like Linux / Windows or something like it), because different OSes have different names for locales.

One way I would do it is to find out the exact locale name on the target system which corresponds to the one on the source, then use a text dump (the "normal" output of pg_dump) and on the target system, use psql to log in and set the "client encoding" to whatever the locale name from the first step is, then load the dump with \i.


Schema for dynamic number of attributes I am implementing an application where users can save alerts for different services. Each service can have multiple types of alerts and each alert has X number of attributes to form a predicate. So here's an example:

Services:
- Weather
- Reddit

Weather Alerts:
- Temperature

id
user_id
type
city
condition (above, below)
temperature

- Forecast

id
user_id
type
city
forecast (rainy, sunny)

Reddit Alerts:
- Frontpage

id
user_id
type
number_of_votes

- User comment

id
user_id
type
reddit_user
number_of_votes

I think this gives a good idea of the model. All the alerts are similar but have a different number of attributes. Now my question is: How should I store these alerts in my database? Here are the options I'm considering so far:

1 table with all the possible fields. The first three (id, user_id, and type) would always have some data but most of the others would be empty as the number of alerts type grow. I don't think that's a good option.
1 table per type of alert. This seems like an easy solution but might be tricky for real life scenarios. I'll have to do plenty of joins if I want to get all the alerts for a user since they will be in different tables. The id, user_id and type fields are also always needed so this feels wrong.
1 to many relationship where I have an alert table and an alert has many attributes or conditions. That second table is basically just alert_id, attr_name, value. When I want to use an alert I just fetch all the attributes for that alert_id.
EAV model.

What are your suggestions? Am I missing something here or making all of this too complicated? Database modelling was never one of my strong areas so I'm a bit struggling to make a decision.

If it matters this is for a ruby on rails application.

Option 3 sounds like your best option as it gives you all the flexibility you need, fits into a RDBMS perfectly and follows normalisation.

Option 1: requires schema changes when new alerts are added to the system.

Option 2: More joins = slower queries.

Option 4: This does give you the flexibility you need but if you were going to do it this way then maybe consider using a document store like MongoDB.


Cannot deploy SSIS packages after upgrade to SQL Server 2016 SP2 Build 5201.2 We recently upgraded from SQL 2016 SP1 build 4466 to SP2 build 5201 (CU2 GDR) on an AG.

On one server an analyst was deploying SSIS using Visual Studio Enterprise 2015 (version 14.0.25431.01 Update 3 and MS. NET framework 4.7.02558).

Since the SQL upgrade they get this error message:

  Failed to deploy project. For more information , query the operation_messages view for the operation identifier '777'. (Microsoft SQL Server, Error: 27203)

Querying the operations_messages table in the SSISDB database for 777 I get this message:

  Failed to deploy the project. Fix the problems and try again later.:Cannot use SAVE TRANSACTION within a distributed transaction.

That message appeared as fixed in SP2 CU1 in this KB https://support.microsoft.com/en-us/help/4092554/fix-cannot-use-save-transaction-within-a-distributed-transaction-when

The analyst says she gets the same error if she uses Management Studio and the IS Deployment Wizard.

Seems that changing the AG to not have WITH DTC_SUPPORT = PER_DB  works. Microsoft have been informed. Looking forward to a fix in a future SP2 CU.


Two type users configuration I am developing an app that has two types of user: Customers and employees.

I have planned to make the supertype as user that contains only:

id
username
password

While there would be two subtypes (customer and employee). Each of them has different specific data, namely,

Customer:

id
purchases
name
age

Employee:

id
badge
name
age.

Could someone help me by suggesting a structure with what foreign key to put and implement?

I'm clearly new to making databases.

Make Customer_Id a foreign key referencing User_Id. Employee_Id will also be a foreign key referencing User_Id. As a new user is INSERTed and the User_Id generated use that same value in Customer_Id or Employee_Id (or both) as appropriate.

If name and age are common to both employees and customers, hold them in the super-type (user). Never ever hold age. It changes all the time. Hold Date_of_birth, which never changes, and calculate age when it is required. Even if you need the user's age when the row was written you would be better off with Date_of_birth and Row_created_date.

A customer should be allowed to have many purchases. Storing them all in a single column breaks first normal form. If your implementation is in a document DBMS then that's OK, but should be acknowledged. If you plan to use a relational database you really should pull it into its own entity type early on, during design.

Don't store the password in plain text. It should only ever be the salted, encrypted version that is persisted. There are plenty of articles on the web that explain why and how to implement this.


credentials file for running mysql related jobs via script? Is there a method to point mysql to a credentials file instead of entering account passwords directly in scripts?

Example:

mysqldump --opt --credfile /etc/mysql_creds mydatabase > output.sql

/etc/mysql_creds

username=myuser
password=ToPsEcReT

It would be better to store the credentials in a config file, and load them as an extra-defaults file:

$ mysqldump --defaults-extra-file=mycred.cnf ...

The config file is the same format as /etc/my.cnf or ~/.my.cnf

$ cat mycred.cnf
[mysqldump]
user = myuser
password = xyzzy

MySQL 5.6 also introduces encrypted storage of credentials. You can set up an encrypted file ~/.mylogin.cnf:

$ mysql_config_editor set --login-path=dump --host=localhost --user=root --password
Enter password:

Then you can dump using that login path

$ mysqldump --login-path=dump ...


How to audit all DML operations on a SQL Server 2008 R2 database In order to assess options of implementing indexed views, I want to check the usage of DML operations of the tables.
The database is new to me as well as the relevant business knowledge, so I just have no idea.

Is there a way to quickly collect a statistic within couple of days?

I am using Standard Edition.

If you just want to know number of index updates (regardless of how many rows affected, how wide the indexes are, etc.) and as long as you can be sure the service doesn't get restarted or indexes dropped/re-created in that timeframe, then you can look at sys.dm_db_index_usage_stats now and after a couple of days.

As suggested by Kin, have a look at Nacho's FAQ. Basically:

The DMV can reset if:

the server is restarted
an index is dropped and re-created

The DMV can be empty if you start with -x, -m, or trace flag 2330
If you have a filtered index, updates will increment the usage stats even if the update did not affect the rows in the index
If any column in the clustering key is modified, all non-clustered indexes also increment their usage stats
Errors occurring after a DML attempt is made will still increment counters
Index usage tells you number of operations - it does not have any way to report to you number of rows or pages touched (an update to every row in a very wide table looks the same as an update to a single row in a very skinny table) - if you want more details on actual volume touched, see sys.dm_db_index_operational_stats.

And also Tim Ford's series on snapshotting the DMV:

Retaining historical index usage statistics for SQL Server : Part 1 | Part 2 | Part 3


How to find out those users which have two different access right in postgresql Thanks for your support and help,
Dear the scenario is like this i have 3 tables

1: users
2: users_groups
3: users_groups_rel

users data

id      name
1       zubair
2       ali
3       bob
4       john

users_groups data

   id    name
    1   report
    2   finance
    3   personnel

users_groups_rel data; it is associated table of users and users_groups

 uid    gid
    1   1
    1   2
    1   3
    2   3
    3   2
    4   2
    4   1

  Now i want to have those users which have access just to (Report +
  finance) When i am running this bellow query the result is incorrect,
  it shows those users which have id of (1 , 4) but i don’t need this i
  need that users which mentioned the exact access right for them.

  example:
  i need those users which have access to these access right
  (report and finance)

SELECT r.uid,u.name
   FROM users_groups_rel r left join users u on u.id=r.uid
   GROUP BY r.uid,u.name
   HAVING count(DISTINCT gid in (select id from users_groups where id=2)) = (SELECT count(DISTINCT gid in (select id from users_groups where id=1))

You can aggregate and check if the distinct count per user is equal to the count of distinct groups of the whole table.

Assuming that the user is identified by a column user_id and the group by a column group_name.

SELECT user_id
       FROM users_group
       GROUP BY user_id
       HAVING count(DISTINCT group_name) = (SELECT count(DISTINCT group_name)
                                                   FROM user_group);


How to terminate BCP output with specific string AND newline? I'm trying to terminate each row in BCP output with a specific string along with newline. Neither of these work:

-r"terminator_string\n"';
-r"terminator_string"+\n';

Any suggestions?

For an idea of the overall problem, see https://stackoverflow.com/questions/23329028/outofmemory-exception-when-reading-and-replacing-strings-with-streamreader-and-s

Use the ASCII codes of the characters you want to match (without the 0x prefix).

For example to match A (char hex 41) + LineFeed (char hex 0A) do this:

-r "410A"

This seems to be undocumented. See my blog here for more info: http://kejser.org/databases/bulk-insert-with-linux-line-endings/


Granting privileges to dbms_crypto I'm trying to grant proper privileges for using DBMS_CRYPTO.

Whenever I call it from package it gives me error:
PLS-00201: identifier 'DBMS_CRYPTO' must be declared

User already has privileges:
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
SELECT ANY DICTIONARY

and EXECUTE on DBMS_CRYPTO to package_owner.

any ideas what am i doing wrong?

This solved the issue for me:

grant execute on sys.dbms_crypto to myuser;

I had a similar problem with the random function.


Is it safe to rely on the order of an INSERT's OUTPUT clause? Given this table:

CREATE TABLE dbo.Target (
   TargetId int identity(1, 1) NOT NULL,
   Color varchar(20) NOT NULL,
   Action varchar(10) NOT NULL, -- of course this should be normalized
   Code int NOT NULL,
   CONSTRAINT PK_Target PRIMARY KEY CLUSTERED (TargetId)
);

In two slightly different scenarios I want to insert rows and return the values from the identity column.

Scenario 1

INSERT dbo.Target (Color, Action, Code)
OUTPUT inserted.TargetId
SELECT t.Color, t.Action, t.Code
FROM
   (VALUES
      ('Blue', 'New', 1234),
      ('Blue', 'Cancel', 4567),
      ('Red', 'New', 5678)
   ) t (Color, Action, Code)
;

Scenario 2

CREATE TABLE #Target (
   Color varchar(20) NOT NULL,
   Action varchar(10) NOT NULL,
   Code int NOT NULL,
   PRIMARY KEY CLUSTERED (Color, Action)
);

-- Bulk insert to the table the same three rows as above by any means

INSERT dbo.Target (Color, Action, Code)
OUTPUT inserted.TargetId
SELECT t.Color, t.Action, t.Code
FROM #Target
;

Question

Can I rely on the returned identity values from the dbo.Target table insert to be returned in the order they existed in the 1) VALUES clause and 2) #Target table, so that I can correlate them by their position in the output rowset back to the original input?

For Reference

Here is some trimmed-down C# code that demonstrates what's happening in the application (scenario 1, soon to be converted to use SqlBulkCopy):

public IReadOnlyCollection<Target> InsertTargets(IEnumerable<Target> targets) {
   var targetList = targets.ToList();
   const string insertSql = @"
      INSERT dbo.Target (
         CoreItemId,
         TargetDateTimeUtc,
         TargetTypeId,
      )
      OUTPUT
         Inserted.TargetId
      SELECT
         input.CoreItemId,
         input.TargetDateTimeUtc,
         input.TargetTypeId,
      FROM
         (VALUES
            {0}
         ) input (
            CoreItemId,
            TargetDateTimeUtc,
            TargetTypeId
         );";
   var results = Connection.Query<DbTargetInsertResult>(
      string.Format(
         insertSql,
         string.Join(
            ", ",
            targetList
               .Select(target => $@"({target.CoreItemId
                  }, '{target.TargetDateTimeUtc:yyyy-MM-ddTHH:mm:ss.fff
                  }', {(byte) target.TargetType
                  })";
               )
         )
      )
      .ToList();
   return targetList
      .Zip( // The correlation that relies on the order of the two inputs being the same
         results,
         (inputTarget, insertResult) => new Target(
            insertResult.TargetId, // with the new TargetId to replace null.
            inputTarget.TargetDateTimeUtc,
            inputTarget.CoreItemId,
            inputTarget.TargetType
         )
      )
      .ToList()
      .AsReadOnly();
}

Can I rely on the returned identity values from the dbo.Target table
  insert to be returned in the order they existed in the 1) VALUES
  clause and 2) #Target table, so that I can correlate them by their
  position in the output rowset back to the original input?

No, you can't rely on anything to be guaranteed without an actual documented guarantee. The documentation explicitly states there is no such guarantee.

  SQL Server does not guarantee the order in which rows are processed
  and returned by DML statements using the OUTPUT clause. It is up to
  the application to include an appropriate WHERE clause that can
  guarantee the desired semantics, or understand that when multiple rows
  may qualify for the DML operation, there is no guaranteed order.

This would rely on a lot of undocumented assumptions

The order the rows are output from the constant scan is in the same order as the values clause (I've never seen them differ but AFAIK this is not guaranteed).
The order the rows are inserted will be the same as the order they are output from the constant scan (definitely not always the case).
If using a "wide" (per index) execution plan the values from the output clause will be pulled from the clustered index update operator and not that of any secondary indexes.
That the order is guaranteed to be preserved thereafter - e.g. when packaging rows up for transmission over the network.
That even if the order appears predictable now implementation changes to features such as parallel insert won't change the order in the future (currently if the OUTPUT clause is specified in the INSERT…SELECT statement to return results to the client, then parallel plans are disabled in general, including INSERTs)

An example of point two failing (assuming clustered PK of (Color, Action)) can be seen if you add 600 rows to the VALUES clause. Then the plan has a sort operator before the insert so losing your original order in the VALUES clause.

There is a documented way of achieving your goal though and this is to add a numbering to the source and use MERGE instead of INSERT

MERGE dbo.Target
USING (VALUES (1, 'Blue', 'New', 1234),
              (2, 'Blue', 'Cancel', 4567),
              (3, 'Red', 'New', 5678) ) t (SourceId, Color, Action, Code)
ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (Color,
          Action,
          Code)
  VALUES (Color,
          Action,
          Code)
OUTPUT t.SourceId,
       inserted.TargetId;

@a_horse_with_no_name

  Is the merge really necessary? Couldn't you just do an insert into ...
  select ... from (values (..)) t (...) order by sourceid?

Yes you could. Ordering guarantees in SQL Server… states that

  INSERT queries that use SELECT with ORDER BY to populate rows
  guarantees how identity values are computed but not the order in which
  the rows are inserted

So you could use

INSERT dbo.Target (Color, Action, Code)
OUTPUT inserted.TargetId
SELECT t.Color, t.Action, t.Code
FROM
(VALUES (1, 'Blue', 'New', 1234),
        (2, 'Blue', 'Cancel', 4567),
        (3, 'Red', 'New', 5678) ) t (SourceId, Color, Action, Code)
ORDER BY t.SourceId

This would guarantee that the identity values are assigned in order of t.SourceId but not that they are output in any particular order or that the identity column values assigned have no gaps (e.g. if a concurrent insert is attempted).


How to assign a constant to a value on all rows, if that value is adjacent to some value on any row I have data like this (STATUS, and NAME are the column headers):

STATUS      NAME
ACTIVE      A1
INACTIVE    A1
INACTIVE    B1
INACTIVE    B1

I want to include a CUSTOM field that returns a constant for a particular NAME  value, if that NAME value is adjacent to a specific STATUS value on any row in the data:

CUSTOM      STATUS      NAME
ACTIVE      INACTIVE    A1
ACTIVE      ACTIVE      A1
INACTIVE    INACTIVE    B1
INACTIVE    INACTIVE    B1

Because NAME = A1 had a STATUS of ACTIVE in row 2 above, I want CUSTOM to show ACTIVE for all rows tied to the NAME = A1. I tried this for the CUSTOM field:

case when NAME in (select NAME from SOURCE where STATUS='ACTIVE') then 'ACTIVE' else 'INACTIVE' end CUSTOM

But the above is still reflecting the same as STATUS on any given row.

Reading the explanation in the question, it seems that you don't really care about the ordering but you want to know if for the same name there is at least one row with status = 'ACTIVE'.

This can be done with a window function, using OVER (PARTITION BY name). The CASE expression has a default of NULL so MAX() will return null if there is no such value. The final COALESCE() will convert those nulls to 'INACTIVE':

SELECT
    status, name,
    COALESCE(MAX(CASE WHEN status = 'ACTIVE' THEN status END)
               OVER (PARTITION BY name),
             'INACTIVE') AS custom
FROM
    table_name ;


why there is no incremental backup in sql server How i can change my database backup type from full backup to incremental backup? Is that possible? Why there is no option for incremental backup type.

It seems you are coming to SQL Server from Oracle background and hence looking for similar terminology. Incremental backup in Oracle is similar to that of differential backup in SQL Server.
Below is definition of Incremental backup from Oracle:

An incremental backup copies only those blocks in a data file that
change between backups. A level 0 incremental backup, which copies all
blocks in the data file, is used as a starting point for an
incremental backup strategy.
A level 1 incremental backup copies only images of blocks that have
changed since the previous level 0 or level 1 incremental backup.
Level 1 backups can be cumulative, in which case all blocks changed
since the most recent level 0 backup are included, or differential, in
which case only blocks changed since the most recent level 0 or level
1 incremental backup are included.

And below is definition of Differential backup from Microsoft:

A differential backup is based on the most recent, previous full data
backup. A differential backup captures only the data that has changed
since that full backup. The full backup upon which a differential
backup is based is known as the base of the differential. Full
backups, except for copy-only backups, can serve as the base for a
series of differential backups, including database backups, partial
backups, and file backups. The base backup for a file differential
backup can be contained within a full backup, a file backup, or a
partial backup.

Hope above clears your concern.


MySQL relay log rotates too soon and too often I have a simple master/slave setup where the slave is just a backup. Sometimes I notice that the relay log on the slave rotates too often and too soon, when it is still very small:

4:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860318633
               Relay_Log_File: vps-relay-bin.000350
                Relay_Log_Pos: 922

5:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860324526
               Relay_Log_File: vps-relay-bin.000351
                Relay_Log_Pos: 6176

7:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860331661
               Relay_Log_File: vps-relay-bin.000352
                Relay_Log_Pos: 7418

9:00 PM
              Master_Log_File: mysql-bin.000051
          Read_Master_Log_Pos: 860333988
               Relay_Log_File: vps-relay-bin.000353
                Relay_Log_Pos: 501

Notice that the master log is the same and its position didn't change much. But the relay log rotated several times, and didn't get very big. MySQL .err shows:

2014-03-23 14:35:14 4971 [Note] Slave I/O thread: connected to master '<removed>',replication started in log 'mysql-bin.000051' at position 860316289
2014-03-23 15:59:50 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 16:59:52 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 18:59:56 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 20:35:53 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 21:35:55 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2014-03-23 22:35:57 4971 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

Master's log doesn't show anything.

What could be happening here?

You can verify by yourself as slave server creates (rotates) a new relay log file under the following conditions only:

Each time the I/O thread starts.
When the logs are flushed; for example, with FLUSH LOGS or mysqladmin flush-logs
When the size of the current relay log file becomes “too large,” determined as follows:

If the value of max_relay_log_size is greater than 0, that is the maximum relay log file size.

If the value of max_relay_log_size is 0, max_binlog_size determines the maximum relay log file size.

The SQL thread automatically deletes each relay log file as soon as it has executed all events in the file and no longer needs it. There is no explicit mechanism for deleting relay logs because the SQL thread takes care of doing so. However, FLUSH LOGS rotates relay logs, which influences when the SQL thread deletes them.


SSAS- Dimension Attribute needs to be sorted In one of my SSAS- cube one attribute(inset_date) which is Date datatype and format is yyyy-mm-dd, when I checked in excel pivots it is not coming in order.
I have changed the order by property from name to key but it is not sorted,
Key  -- insert_date(date)
Name -- insert_date(wchar)
Value-- insert_date(date)
Can you please what other properties I have to change to sort the attribute of a dimension?

Thank you for your well asked question.
Ordering attribute hierarchy in SSAS can be done in four different ways

By Key
By name
By Other related attribute key.
By other related attribute name.
By in order to activate ordering we need to set the property AttributeHierarchyOrdered=True.


Change the order of a table according to preference in another table In MS SQL server, I have a table A as below

Name   Food    PreferenceOrder
Jon    Burger  1
Jon    Coke    2
Jon    Pizza   3
Jon    Fries   4
Sam    Pizza   1
Sam    Coke    2

I have another table B that can override the preference order above

Name   Food    PreferredOverFood
Jon    Pizza   Fries
Jon    Coke    Burger
Jon    Fries   Coke
Sam    Coke    Pizza

Basically here, Food should come before PreferredOverFood (Pizza > Fries)

Now, I want to reorder table A according to Table B preferences, so the result should be like

Name   Food    PreferenceOrder
Jon    Burger  4
Jon    Pizza   1
Jon    Fries   3
Jon    Coke    2
Sam    Pizza   2
Sam    Coke    1

I tried by using cursors, so I created a dynamic cursor, with each fetch I am updating Table B with table A preference, but since we are updating things row by row its not considering rows that violate the previous preferences, so I am getting Fries before Pizza (since Fries > Coke is run and it forgot about first preference (Pizza > Fries)).

So dynamic cursor is not working, (its not refreshing the result set after update). Can I use CTE or something to do like above. (Can also have circular dependencies, but not too worried about it for now)

Thanks

It is too long for a comment.

Well, that's the problem, then. I take data from A, then apply overrides from B to it and end up with a cycle. I think that there is a cycle, you don't think that there should be a cycle.

So, there is a bigger problem with understanding how overrides should work.

My logic is the following.

Data from A can be represented in the same format as B:

Name   Food    PreferenceOrder
Jon    Burger  1
Jon    Coke    2
Jon    Pizza   3
Jon    Fries   4

So, for Jon we have this chain originally:

Burger -> Coke -> Pizza -> Fries

OR this set of rules:

a) Burger -> Coke
b) Coke -> Pizza
c) Pizza -> Fries

Then we have overrides from B:

Name   Food    PreferredOverFood
Jon    Pizza   Fries
Jon    Coke    Burger
Jon    Fries   Coke

OR this set of rules:

d) Pizza -> Fries
e) Coke -> Burger
f) Fries -> Coke

Now we merge two sets of rules where second set overrides the first set if there is a conflict.

Rule (d) is the same as (c), so (c) is removed.
Rule (e) overrides (a), so (a) is removed.
Rule (f) is a new rule that doesn't exist in the first set.
If any rules from the first set are still there, they are kept.

Final result of rules:

d) Pizza -> Fries
e) Coke -> Burger
f) Fries -> Coke
b) Coke -> Pizza

This is a directional graph. If it represents a tree, it can be ordered. It there are cycles, you need to decide what to do with them. If there are several disconnected trees, you can order each tree individually and then decide the order between trees.

In your example the graph looks like this (if we start from Coke).

    (b)      (d)      (f)
Coke -> Pizza -> Fries -> Coke -> ... the cycle (bdf) ...
   |
   | (e)
   |
   -> Burger


Handling attributes that are time-variant in a Datamart I have looked through the entire list of sites, and this is I think the best match. This is not really about database administration, more like database design. Please excuse me and point me to the correct site.

I am designing a database for a rudimentary BI system. At this moment I have hit a wall, which is this (explaining using dummy data):

Suppose my fact table contains this information:

John Doe flew from LAX to ATL on 1 Nov in flight AB-123

The dimensions and their attributes are:

Flyer - name, club
Airport - city, code
Date - year, month, date
Flight - code, std, delay, price

Now, from this I can easily generate a report like this:

Airport --> LAX  DFW  ORD  ATL Total
Gold         50   40   10   25   125
Silver      240  300   95  140   775
Bronze     1000 1500  800 1800  5100
Total      1290 1840  905 1965  6000

Using a query like:

select fd.club, ad.code, count(f1.id) from flyer fd, airport ad, fact1 f1
where fd.id = f1.fid and ad.id = f1.aid and month(f1.date) = 10
group by f1.club, ad.code;

But my problem comes from the fact that the "club" status of a flyer is a moving target. A flyer who is in Gold today could have been in Silver in October, so I am counting him in the incorrect group here. Thus, I imagine I need a separate fact table like this:

 John Doe entered Bronze club on 8/15
 John Doe entered Silver club on 10/20
 ...

"Club" drops out as an attribute of the original flyer dimension. Instead, a new club dimension emerges.

And then to generate the report I need, I join these two fact tables.

Am I on the right track? Or is there an alternative, simpler solution to this? One alternative I could think of is to include the club in the original fact table, handling it during the ETL process. So the fact becomes:

John Doe of Silver Club flew from LAX to ATL on 1 Nov in flight AB-123

Please let me know which approach is better, or if there is a third one.

The way to do this is what Kimball called a Type-2 or Type-6 slowly changing dimension..  Essentially, a type-2 SCD has a synthetic dimension key, and a unique key consisting of the natural key of the underlying entity (in this case the flyer) and an 'effective from' date.  The synthetic key is joined against the fact table, so you can attach it with a simple equi-join (i.e. you don't have to filter by date range in the query).

All the attributes (e.g. club in this case) are attributes of the flyer.  If one of these attributes changes, a new row is created on the dimension recording the new state, effective from the date of the change.

The type-6 is like an ordinary type 2, but has a self-join to the current version of the row.  Whenever a new row is created for a given natural key all rows for that natural key are updated with the self-join to the current row.  You may or may not need this functionality.

You can query an as-at status by joining the fact tables against the row that was recorded on them - i.e. the state that was current.  If you have a type-6 the current status can be queried through the self-join, which can also be materialised on the fact table if desired.

This data will also play nicely with ad-hoc reporting tools and cubes, although implementing complex cube hiererchies on a slowly changing dimension is a bit fiddly (you need to keep placeholders for the natural keys of the hierarchy levels and combinations over time).

A good point to start would be a google search on "type 2 slowly changing dimension"


Is there a better way than the ones listed within to join data in an excel spreadsheet to a table in a SQL Server database? Let's say I'm given a list of names and email addresses in an excel spreadsheet. I am asked to find all customers in the database who's name, and email are in that list.

I currently know of two ways to achieve this...

Import the data into a new table (to be removed later) and then use this table to get the data.
Paste the names and addresses into a SQL query.

Is there a 'better' way to do this kind of thing (i.e. one that doesn't require me to place the data in the database before using it, and doesn't require me to create a monster query that has all the data in a 'where x in (data)' part)

Great question! Especially the "monster query" part ;)The answer is this guy here, enjoy: Excel Data Source as Linked Server


How to ensure no duplicate records are added when using multiple Node JS processes We are working on an appointment scheduler. We have a table Appointment that stores an appointment with starttime, endtime, assigneeId and eventId.
We are using Bookshelf.js as an ORM with Node JS. The problem we are facing is if we send parallel thread requests using JMeter for the same combination of starttime, endtime, assigneeId, eventId and our app ends up booking an appointment for both the threads which is incorrect.
Only one of them should succeed and other one should fail. How do we go about solving this problem?
CREATE TABLE `Appointment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned NOT NULL,
  `account_Id` int(10) unsigned NOT NULL,
  `event_Id` int(10) unsigned NOT NULL,
  `assignee_Id` int(10) unsigned NOT NULL,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `book` tinyint(4) DEFAULT '0',
  `code` varchar(20) DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `deleted_By_Id` int(10) unsigned DEFAULT NULL,
  `updated_Appointment_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_Appointment_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `appointments_code_unique` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

What we do is before inserting we see if any overlapping appointments exist in the DB. Iif not, then we add it but with multiple Node JS processes: both of the processes do not find any appointment, and both end up inserting.
First Solution :  INSERT IGNORE but it's difficult in our case to define a Unique key based on starttime and endtime as we need to check for overlapping values as well.
Second Solution: Let both of them insert the record in the Appointment table and after the transaction is committed we check for overlap - if we find an overlap we delete the record which was added later. This will result in a waste of computation as well as ids.
Third Solution: Use an App Level Queue to manage all requests to the Appointment table and only 1 process will resolve all Queue messages. I wonder how this approach will scale.
How do I ensure at the time of insert that only one thread can book an appointment safely? Will a stored procedure be helpful? We are using transactions provided by Bookshelf.js

It sounds like the business key for an Appointment is (assigneeId, eventId, starttime, endtime).  Create a unique constraint on the business key (assigneeId, eventId, starttime, endtime) would fix the issue.  You need to catch unique constraint error returned by the DB on your app.


Is DB2 more reliable and stable compared to PostgreSQL or MySQL? Always i was using MySQL or PostgreSQL for my projects such as Web/Desktop apps.
I had data loss few times and thanks to old backup i was always up and running.

But for a recent project for Airport, i have to use something which never ever can lose its data at-least not database until it has power in the circuit..

So i am thinking DB2 should be the best for such risk less missions? Any idea or hints.

Thanks in advance.

All of the platforms you have mentioned can run close to zero data loss configurations. All of them could be deployed in a configuration that will fail.

Platform choice is one part of the puzzle. It will be your implementation of the platform that determines whether or not your requirements are met.

MySQL Cluster
Oracle RAC
PostgreSQL High Availability
SQL Server High Availability

In reality, high availability and disaster recovery are a trade off between uptime, Recovery Point Objective, Recovery Time Objective and cost.

Zero data loss and zero downtime is expensive, complicated to setup and complicated to maintain. There is no free lunch.


How can you view the IP addresses of active MongoDB Atlas connections? Some posts show you can use db.currentOp(true) to show active connections, but when I do that I get this error

{
        "ok" : 0,
        "errmsg" : "Using $all for currentOp is disallowed in this atlas tier",
        "code" : 8000,
        "codeName" : "AtlasError"
}

Is there another way to view active connections to a MongoDB Atlas Cluster? What tier allows you to use this command?

If I understand correctly, this is about how to view ip addresses that got connected to your mongoDB Atlas cluster, I would say you can see number of connections established to a cluster by just logging into your Atlas mongoDB cloud dashboard (Atlas login). But if you want details on connections established, then you can download logs for that particular cluster as a file, You can navigate to it as given below, but you might only see this option for paid versions, Technically db.currentOp(true) should work, but for some reason it didn't, not sure why, maybe unsupported !! (mongoDB-unsupported-commands) :


DELETE from OPENQUERY alias not working I'm trying to use the following code to execute a DELETE statement on a linked server. I'm trying to delete all records from the remote server where there is a match on a certain ID field in both the table on the linked server and a local table. Any records showing up in the INNER JOIN should be deleted from the linked server table RemoteTable.

The following isn't allowing me to use an alias ("RemoteTable") for my remote table. Am I on the right track here with what I'm trying to accomplish? Thanks!

DELETE FROM OPENQUERY(LINKEDSERVER_MYSQL,'SELECT * from PrimaryInquiry_T2') AS RemoteTable
INNER JOIN Forms.dbo.PrimaryInquiry_TLOCAL AS LocalTable
ON LocalTable.PrimaryInquiry_TLOCAL_EntryID = RemoteTable.PrimaryInquiry_T2_EntryID

You should be able to use SQL Server's proprietary and admittedly awkward self-reference form of delete:

DELETE RemoteTable
  FROM OPENQUERY(...) AS RemoteTable ...


Retrieve All MySQL/MariaDB Table Definition From .frm Files I wonder if it is possible to retrieve the MySQL/MariaDB table definitions from the .frm files?

For example, if I wanted to steal your database and I had access to your MySQL/MariaDB files but didn't have its user account, could I copy all the .frm files from your database, paste them into my own server and then run mysqldump --no-data to retrieve the exact DDL?

Next if the answer is yes:

Does it apply to different server versions and/or OS?
What can I do to prevent or make it difficult for people to do this with my database?

Next if the answer is no:

Is there any dependency to other file? Or would I have to do something?
What exactly is the role of the .frm files?

A 'partial' Answer...

The .frm contains, in an encoded format, the CREATE TABLE for the one table.

Copying a .frm into your copy of MySQL would, in theory, give you the schema definition.  But, other info is needed.  So, now the challenge is to try to re-establish a table where the data is lost, but the schema is still in the .frm.  MyISAM depends on the existence of a .MYD (.MYI can be reconstructed); InnoDB has some hooks in ibdata1, but these may be reconstructable.  (Hence, this Answer is only 'partial'.)

.frm files are probably binary compatible between OS versions, even big-endian vs little-endian.  And across large swaths of MySQL/MariaDB versions.  A table with a JSON column would not be backward compatible.  There have been only a few other incompatible changes in the past decade.

As for hacking into your system.  Which is easier to get into -- The filesystem, or MySQL?  The .frm file is somewhat protected by directory and file permissions.  MySQL is somewhat protected by its password mechanism.  Obviously, you should do whatever you can to prevent access to both.

A common entry point into MySQL is via web arguments (<form>s in HTML) where the values are not properly escaped before INSERTing text into the database.  There are different solutions to that entrypoint, depending on which API is used.  (I've seem a hacker post /etc/passwd on the Internet; he got it from a PHP program that failed to escape simple data entry fields.)  See "SQL Injection".

Is your schema the most valuable thing?  I would expect the data to be more important.  And also the root password for your server.


How to increase column limit of a table in SQLite? SQLite: How can we configure the maximum number of columns allowed in a table?

You would have to obtain the Source Code for SQLLite, change the value of SQLITE_MAX_COLUMN, recompile the code and distrbute the resulting executable.

However, more than 2000 columns in a single table sounds wrong to me.
Do you have lots of repeated fields in each row?  That's poor Relational design.

If necessary, create a second table with the same primary key as the first and add your additional fields into that.  You would have to issue two select statements to retrieve the values, though, because SQLITE_MAX_COLUMNS also applies to select statements, so you can't just join the two tables together.


Is there any point in undersizing VARCHAR columns? Googling around there seems to be mixed reports whether the size of a VARCHAR2 column in Oracle impacts performance or not.

I would like to give the question of VARCHAR size a little twist and hope to get some insight in this:

Given (multiline) free-text fields (not short stuff like names) that you want to store in an (Oracle) database, is there any point (wrt. performance or otherwise) in not maxing out the VARCHAR capacity (VARCHAR2(4000) on Oracle) but choosing a smaller value such as 1024 or 512 because that will likely be sufficient in 98% of the cases anyway.

It does impact memory usage, especially when a client program has to allocate enough memory to receive a dataset.

Bear in mind that a lot of apps (especially web apps) use UTF-8 which is a multi-byte character set. As such, you should really consider characters rather than bytes.

If I was expecting over a thousand characters, then I'd actively consider a CLOB. I'd be thinking about whether it will store plain text or some form of markup (wiki / html ?), usage with non-Euro languages. The Questions and Answers here, for example, would be CLOB, but comments can fit in a VARCHAR.

If you max out a VARCHAR, then in six months someone will want to make it bigger again, and you'd be kicking yourself for not using a CLOB.


Create table from record Is it possible to create a table from a record? For example:

DO $$
DECLARE
  v1 RECORD;
BEGIN
  SELECT 1 AS c INTO v1;
  CREATE TEMP TABLE temp AS
    SELECT v1.*;
END;
$$
LANGUAGE 'plpgsql';

This returns

ERROR:  record type has not been registered
CONTEXT:  SQL statement "CREATE TEMP TABLE temp AS
    SELECT v1.*"
PL/pgSQL function inline_code_block line 6 at SQL statement

I tried more complex solutions such as using hstore to loop through the record keys and create a custom command but then I would not be able to use the same data types.

v1 is untyped and Postgres doesn't know what to do with that.  At some point, either while defining the record or when returning it you have to specify the columns.  Try specifying the column names in the SELECT within an IF for different types.  I put in dummy checks for the IF to be replaced with what you need to decide the row type.

DO
$body$
DECLARE
v1 RECORD;
BEGIN

IF (1=3) THEN--logic to decide which data here
    SELECT 1 AS c1,2 AS c2,3 AS c3 INTO v1;
    CREATE TABLE temp AS SELECT v1.c1, v1.c2, v1.c3;
ELSE
 IF (2=2) THEN--logic to decide which data here
    SELECT 4 AS c1,5 AS c2 INTO v1;
    CREATE TABLE temp AS SELECT v1.c1, v1.c2;
 END IF;
END IF;

END;
$body$
LANGUAGE
'plpgsql'

Alternatively you could also:

DO
$body$
BEGIN

IF (1=3) THEN--logic to decide which data here
    CREATE TABLE temp AS SELECT 1 AS c1,2 AS c2,3 AS c3;
ELSE
 IF (2=2) THEN--logic to decide which data here
    CREATE TABLE temp AS SELECT 4 AS c1,5 AS c2;
 END IF;
END IF;

END;
$body$
LANGUAGE
'plpgsql'


Unexpected slow queries Using LEMP on popular cloud-service virtual server, with MyISAM tables.

The problem is that sometimes once in few hours simple mysql queries (select from where) executing several seconds (5-9) instead of milliseconds (0.002) like in other time.

MySQL slow.log has these records:

  ...
   Query_time: 5.369666 Lock_time: 0.000120 ...

error.log is empty and in other system logs have nothing special too

Such slow queries goes one by one to several databases and tables so its not looks like the table lock and lock_time value in slow.log is also insignificant.

I was enabling general log but there was nothing strange. Just regular queries so I cant find any correlation. I also didnt find out memory or cpu leak. Looks like sometimes something load MySQL a lot and it slowing down.

What can be the reason of such peaks of MySQL slowdown and how can find it out?

"0.002" sounds like the Query cache came into play.

When the QC is turned on exactly the same SELECT is run a second time before any changes happen to the table(s) involved, the resultset is looked up in a Hash (called the Query Cache).  This will take on the order of 1ms regardless of how long the query normally takes.

Or...

Since you are using MyISAM, virtually any operation that touches the same table(s) will 'lock' the table and cause a normally fast query to stall for as long as the other query (or queries) take.  Switching to InnoDB is likely to help.

If you would like to discuss it further, please provide the query, SHOW CREATE TABLE and EXPLAIN SELECT ..., SHOW VARIABLES LIKE 'query%';,   SHOW GLOBAL STATUS LIKE 'Qc%';  The infomation in those may lead to eliminating one or the other of the above explanations.


Postgres heap vs SQL Server clustered index I am transitioning from SQL Server to Postgres, and one of the biggest things for me to digest is the non-existence of the "clustered key" that sorts the data in Postgres.

Can someone share their thoughts on how Postgres avoided the need for an internally sorted dataset and how it works with large heap tables and still supply exceptional performance?

PostgreSQL simply doesn't implement this feature.  There is no trick to not implementing it.  It isn't implemented in the straight forward, uncomplicated way of just not doing it.  To use one bit of jargon, all btree indexes in PostgreSQL are "secondary indexes", not "primary indexes".  Even the primary key's index is a "secondary index".

There are some cases where clustered keys (or index organized tables, as another product calls them) are important, and in those cases PostgreSQL fails to "supply exceptional performance".  You can argue about how common those cases are, of course, but they certainly do exist and it is unfortunate that PostgreSQL doesn't offer a solution for them.  There have proposals to address this, but I don't think any of those efforts are currently active.

In some cases, you can ameliorate the problem by using the CLUSTER command, or by implementing partitioning, or by using covering indexes, but none of these is entirely satisfactory as an alternative to real clustering.


DB2 Load Utility Primary Key vs. Unique Index In short, does the Primary Key double as a Unique Index?
Some background:
I am trying to load a bunch of data into two tables using the DB2 Load utility loading in IXF file types.  The data is separated out by table, and by month per table, so I need to run the load command repetitively.  The actual load of the data doesn't appear to take to long, however, the utility goes and rebuilds the indexes after each load.  This is progressively making my execution time longer and longer with each run as the tables grow.  In the DB2 documentation for the load utility, it shows a indexing mode of deferred option that sounds like it skips that step (recommending that you do a rebuild on the very last load).  When I set this option, I get the following error on execution for both tables...

SQL3214N  The load operation failed because the table is incompatible with the DEFERRED index mode. Reason code: "1".

Now I have gone through and looked at the syscat.indexes table and neither table has a "Unique Index".  Both have primary keys and only one has a unique key, which has prompted my question above.

Documentation says:

It is important to understand that there is no significant difference between a primary key or unique key constraint and a unique index. To implement the concept of primary and unique key constraints, the database manager uses a combination of a unique index and the NOT NULL constraint


How should I design my MySQL database? I recently read about relational database normalization for the first time and I was wondering how best to design my database. If each entry has a url, some text, and a list of probabilities, what is the best way to normalize my database? I was thinking one table with columns id, url, and content, and another with id, probability, and foreign_key_id (referencing the first table), but as an SQL novice I don’t know if this is the best approach, especially since the second table would end up having several times as many rows as the first.

Normalization is only one aspect of the database design process.  For starters, I suggest that you do something like ...

[1] Create an ERD (entity relationship diagram) aka logical diagram.  Start on paper - identify the entities that your system will be dealing with.  Find relevant relationships that connect the entities.  Use the correct terminology: entity, attribute, unique identifier, relationship, cardinality, optionality.  AVOID saying/thinking: tables, primary/foreign keys.  Do NOT mix up instances and entities (each entity represents a multitude of instances).  Remove redundant relationships.  Use modelling software* once the paper version looks okay.
* eg the (freely available) datamodeler:  http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

The ERD stage is probably the hardest one to learn and do.  However, if you get it right, you'll have a reliable foundation for the later stages.  Make sure that nothing in your ERD violates 1NF or 2NF or 3NF. A lot of confusion can arise when "implementation details" are coming into the game too early.  A rather good description of the process can be found at: http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html

[2] Draw a relational/physical model - this is one step closer to the implementation.  Entities (of the ERD) become tables, attributes become columns, unique identifiers become primary keys etc.  Apply the correct data types (depending on the system you are using).  Specify the constraints: primary/unique/foreign/check constraints eg tables on the MANY side of a relationship will need an additional foreign key column (which "references" another pk or unique column).  The relational model is like a roadmap for coding queries later on!

Some modelling software is able to generate SQL(DDL) code from a model, or - vice versa - generate a model from code.  If you cannot resist the temptation to write code straight away, generate a model for documentation purposes.

[3] Only if the diagrams are looking good, write the DDL code (SQL: mainly create table ... and alter table ... statements).  Update the models (ERD and relational) if you make any changes to the initial "layout".

[4] Insert a decent amount of test data into each table (there are online test data generators).

[5] Write the queries, views etc as required.


25M x 25M inner join (postgresql) performance I have a one time need to do an inner join of 25M rows on 25M rows. The box is a Alienware area 51, 4 cores 25GB memory and SATA drive (non system disk). So far it has taken 22 hours. I did btree index the ID (bigint) column which the join is being done on for both tables. Any tips? How long do you think I have to wait?

EXPLAIN SELECT
    public.products_by_location_mv.id,
    public.products_by_location_mv."data_object.unique_id",
    public.products_by_location_mv.location AS outline,
    public.products_by_location_mv.elevation_ft,
    public.products_by_location_mv."geo_product.geo_product_id" AS pid,
    public.products_by_location_mv.cntry_name,
    public.products_by_location_mv.product_name,
    public.products_by_location_mv.product_type,
    public.products_by_location_mv.product_producer,
    public.products_by_location_mv.product_size,
    public.products_by_location_mv.do_location,
    public.products_by_location_mv.product_location,
    public.obj4.uid AS oid,
    public.obj4.size_bytes,
    public.obj4.object_date,
    public.obj4.description,
    public.obj4.location AS path
INTO
    public.inventory0
FROM
    public.obj4
INNER JOIN
    public.products_by_location_mv
ON
    (
        public.obj4.id = public.products_by_location_mv.id) ;

"Hash Join  (cost=3825983.03..12908235.27 rows=24202368 width=1356)"
"  Hash Cond: (products_by_location_mv.id = obj4.id)"
"  ->  Seq Scan on products_by_location_mv  (cost=0.00..1457298.68 rows=24202368 width=721)"
"  ->  Hash  (cost=1414691.68..1414691.68 rows=25507868 width=643)"
"        ->  Seq Scan on obj4  (cost=0.00..1414691.68 rows=25507868 width=643)"

Although it is hard for me to understand the motivation for this kind of query, you cannot expect any kind of efficiency since the query results will not fit in main memory. If you want the results stored somehow, the only way to do it is through the COPY command (http://www.postgresql.org/docs/9.2/static/sql-copy.html). COPY has the advantage that once a row is retrieved from the query it goes directly to secondary storage. This way you actually have a chance for the query to finish after a few hours. Then, if needed you can use COPY again to load those results to another DB table.


Which is faster, SUM(CASE) or CTE PIVOT? There are two types of ways to perform a PIVOT. Before SQL Server 2005, when PIVOT was introduced, most people did this:

SELECT RateID
              SUM(CASE WHEN RateItemTypeID = 1 THEN UnitPrice ELSE 0 END),
              SUM(CASE WHEN RateItemTypeID = 2 THEN UnitPrice ELSE 0 END),
              SUM(CASE WHEN RateItemTypeID = 3 THEN UnitPrice ELSE 0 END)
              FROM rate_item WHERE _WhereClause_
              GROUP BY RateID

Later, when 2005 introduced PIVOT it became this:

   SELECT RateID, [1], [2], [3]
          FROM PertinentRates -- PertinentRates is a CTE with WHERE clause applied
          PIVOT (SUM(UnitPrice) FOR RateItemTypeID IN ([1], [2], [3])) PVT)

Across SQL Server 2005, 2008 R2, 2012 and 2014 (the versions of SQL Server I've worked with that implement PIVOT), in my experience, it has always been faster than SUM(CASE) or in a few cases equally fast. Are there examples where PIVOT is slower?

I can't give the DDL because it is an example from my work. But the table is pretty simple. In the PIVOT example it is drawing from a CTE whereas the SUM(CASE) is drawing directly from the table. But the SUM(CASE) performs the same drawing from the CTE.

In my work example, the PIVOT comes back in 10 seconds while the SUM(CASE) comes back in 14. Clearly it must be doing something different under the covers. The plans are the same, 50% of total each. PIVOT converted to SUM(CASE) in the query analyzer. Yet SUM(CASE) never comes back in less than 13 seconds, and PIVOT never comes back in over 11 seconds.

I've tried running them back and forth, it doesn't matter the order they are run in. If I run them both from a cold cache they both take longer, but PIVOT is still faster, 12 vs 17 seconds. Can't reproduce on a second server, but that one is considerably better; it's 5 seconds each there with minor variations. PIVOT is a little better, but percentage-wise it doesn't have the same edge as on the first server.

The IO stats, like the query plan, is identical between the two. That is odd, I had kind of expected to see different IO stats, even though I've never looked at them for this particular example.

Are there examples where PIVOT is slower?

This is unlikely in simple cases. As Itzik Ben-Gan notes in his SQL Server Pro article, Pivoting Data when looking at the plan for a PIVOT query (emphasis added):

  Figure 3 shows the plan for the PIVOT query. As you can see, this plan is very similar to that of the standard solution—so much so that if you look at the properties of the Aggregate operator, under Defined Values, you’ll find that SQL Server constructed CASE expressions behind the scenes:

  …
  [Expr1022] = Scalar Operator(SUM(CASE WHEN [InsideTSQL2008].[Sales].[Orders].[shipcity]=N'Barcelona' THEN [InsideTSQL2008].[Sales].[Orders].[freight] ELSE NULL END))
  …

  With this in mind, you shouldn’t expect the solution that’s based on the PIVOT operator to perform better than the standard solution. The main benefit in the PIVOT operator at the moment is that it’s less verbose.

For more advanced pivoting requirements that the (non-standard) PIVOT syntax does not directly support, workarounds are needed. These may or may not lead to worse performance compared with CASE, depending on various factors including the skill level of the implementor.

Examples of these problematic cases are covered in Itzik's article, and also well explained in Robert Sheldon's Simple Talk article, Questions About Pivoting Data in SQL Server You Were Too Shy to Ask.

My experience has been that PIVOT and Agg(CASE... generate extremely similar plans with extremely close performance characteristics when both are written optimally. My usual advice is to write queries using whatever syntax feels most natural to you, and to only try rewrites if performance is not acceptable.

Internals

The SQL Server query processor does have a built-in Pivot logical operator (LogOp_Pivot), so it is maybe not quite correct to say that SQL Server rewrites pivots to aggregates and case expressions, at least if we are talking about parsing and compilation activities that take place prior to cost-based optimization (trivial plans are not available for pivot queries).

On the other hand, it is true that the only way the optimizer can implement a query tree containing LogOp_Pivot is via the exploration rule ExpandPivot. This rule expands LogOp_Pivot into a normal grouped aggregate (LogOp_GbAgg) with associated scalar expressions. When this rule is disabled, pivot queries fail to compile.

In practice then, we can say that pivots are always (eventually) 'rewritten' as aggregates and scalar expressions before an executable plan can be produced.

Anyway, the result of the rewrite to LogOp_GbAgg is converted to the physical operators needed for an executable plan by the regular group-by aggregate implementation rules GbAggToHS (hash) or GbAggToStrm (stream).

As a side note, the reason 'manual pivots' (aggregates on case expressions) have an extra Compute Scalar below the aggregate is that the case expressions are pushed toward the leaf level of the query tree during Project Normalization (an early stage of compilation, before cost-based optimization).

Queries that use the PIVOT syntax do not have this because the expressions are not created until ExpandPivot runs during cost-based optimization. At the (earlier) time Project Normalization runs, the query tree still has LogOp_Pivot elements, so there are no projections to push down, and the case expressions typically end up inside the hash or stream aggregate.

There is typically no advantage in avoiding the Compute Scalar, since from SQL Server 2005 onward, expression evaluation is normally deferred until the result is required by a later operator. In this case, evaluation of the case expressions is deferred until the aggregate (hash or stream) requires it.


Date is not sorted in reporting services consecutively and correctly I am trying to generate a report using the SSRS,but the date is not being sorted

as I want when running the query.

It is being shown as follow (consecutive months are mixed):

28/05/2014

28/06/2014

29/05/2014

29/06/2014

What I need is to have it as follow (all consecutive days in the same month) :

28/05/2014

29/05/2014

28/06/2014

29/06/2014

Note that I am using this expression to convert the date :

convert(nvarchar(50),dbo.mytable.manufacturyDate,103)

My default value was a datetime and converting it to text was the problem when

sorting.So I removed the convert in my expression and make sure in the

manufacturyDate text properties that it is a dd/MM/yyyy


Database design considerations for unused columns with every table has same schema I am a web developer so I don't know a lot about databases. The company I joined recently has a very mature Desktop ERP built in .NET and SQL Server, they are providing services to huge corporate clients and there design is working fine. But they didn't develop any web based system. But there database design is quite unusual. Let me explain it and then I will post my questions.

So, Now I joined them to develop them a web based ERP (a replica of there desktop system in web). Since I am building the application from scratch, they have given me liberty to revamp any thing which I think would effect positively.

Now the design is,

They have around 150 tables in database.
Every table has the same schema definition.
They have divided the fields in three categories.

Strings (so they assign 50 varchar(250) fields in database).
DateTime (so they assign 15 smalldatetime fields in database).
Numeric (so they assign 30 Numeric() fields in database).

All columns have names as (these names don't terrify the developers, in a week or two they are accustomed and even remembered many of the fields associations):

Strings (S1, S2, S3, S4 and so on).
DateTime (D1, D2, D3, D4 and so on).
Numerics (N1, N2, N3, N4 and so on).

As I have told you the schema. Every table consist of 95 columns. And only 15-20 columns are actually been used. The remaining 75-80 columns are NULL.
The tables are well normalized and indexes are maintained.
Number of rows in most of the tables are less than 1000. Only the transaction table records touches several hundred thousands.
The precision for numeric columns are by default (1, 0). When any field is selected to be used then the precision is adjusted as per the requirement.
An empty database is of ~4MB.
This design makes there development quite easy. Since they have a number of columns and whenever they need a field. They just select the data type, i.e. String or Numeric or DateTime and the next available column is assigned.

I think this information is quite enough. Now I want to ask

Since, I don't know a lot about SQL. Is this design viable for web environment (web API which will be called from web client as well as from mobile)?
Since, every table has 75-80 NULL columns. Does they cost us a lot of memory in future, when transaction records will touch millions.
What are your suggestions to improve this design?

THANKS.

1) Yes. It's a maintenance/documentation nightmare but technically there's no reason it wouldn't work.

2) In general each null will be one bit of storage. So 80 null fields might be 10 bytes per row. The full answer is that it varies depending on data type but with varchar for the most part it's a good rule of thumb.

Some alternatives where you expect fields to be null, are column sets (see https://msdn.microsoft.com/en-AU/library/cc280521.aspx); where the engine munges those often unused columns into a single XML column. I don't think it's a very good fit for your purposes because it messes up indexing and introduces a bunch of limitations - but it's something to be aware of.

I don't think performance is going to be affected though. It will mean that you'll have additional lookups if queries are doing select * instead of just the columns they need (and so it would be hard to create proper covering indexes). But that's a generic problem and not specific to this design.

3) I think you should probably try to forget refactoring it and just go ahead building your application as best as you can with what you have.

A database developer would probably start by importing one of the existing databases into SSDT (a Visual Studio database project) which will create the schema, then they'd do any minor fixes required to get a build going, and see that it can create a usable empty database for use with the software.

After that they'd go over the source code and start using SSDT's right click refactor functionality to give the tables and columns proper names; which will rename all the procedures/functions etc that use it.

This still leaves any dynamic SQL broken, external reports broken, and anything that lives outside of the database broken (the application, web services, queries from customers with direct access!) This means it's a pretty big and collaborative effort to fix.

I'm almost certain they will run across fields which have multiple meanings - because that's what happens when people build software this way. Luckily if they were doing all of this then they can use that opportunity to split dual-usage fields into separate fields.

But can you do that? I imagine it's outside of your scope, and businesses generally don't like paying for such things when "why would you need to do that, the software works as-is!"


MySQL: Flush Privileges of Single User On Mysql 5.1.72 I created a replication user. Right before running FLUSH PRIVILEGES; this thought passed my mind what if there are any unapplied privilege updates?

How can I flush privileges of a single user?
How can I check the
list of changes about to get applied once FLUSH PRIVILEGES; is
processed?
If things go wrong is there any way to revert the
changes?

This answer applies to all versions of MySQL up to, but maybe not including 8.0.

FLUSH PRIVILEGES syncs the entire set of privileges.  No way to flush just one user's.
No way.
Run the equivalent opposite action -- REVOKE <-> GRANT, etc.

A full dump of certain tables in mysql:  user, db and a few others.  This is the 'source of truth' for the current state of all the GRANTs.  Editing the tables with UPDATE (etc) SQL statements has the equivalent effect to GRANT/REVOKE/FLUSH.


Restoring encrypted DB to SQL vNext I wondered if anyone else may have tried restoring a copy of an encrypted database to an instance of SQL vNext. The instance I'm running has been installed on a CentOS box, but I'm not having any luck restoring this particular database, because I can't get my certs set up. I'm trying to follow the same practice that has allowed me to accomplish this on other instances/servers, to no avail. I've gone so far as to give full access (chmod 777) to most of the folders and files involved, because the error I'm receiving states that it may be a permissions issue. Everything that should be taking place would be in /var/opt/mssql/data, /var/opt/mssql/secrets, /var/opt/mssql/.

If any of you knowledgeable folks could point me in the right direction, I'd greatly appreciate it. I will post more details/specific error message in the morning.

You might be running into a bug in how SQL Server vNext CTP1.2 handles filepaths. To CREATE CERTIFICATE from a backup, you currently need to pre-pend a 'c:' and use backslashes ('\') instead of forward slashes. This will be fixed soon :)

Here's a demo for the scenario you're trying to do (backup an encrypted database and restore it on a SQL vNext on Linux instance). Let me know if it clarifies things for you.

https://github.com/tmullaney/tde-migration-demo


Why does it take so long to view logs on a server? I am just trying to look up more verbose information for a maintenance plan task that failed. I open up the log file viewer and check the box to view the logs for my maintenance plan. There are no active filters besides that in this example. Then I play the waiting game.....

I had it take almost 20 minutes to get the logs to show. So I thought that it might just taking the time to load all logs from the beginning of this servers time. I reduced the filter to about 3 days since that is all I needed anyway. That came up faster but still took 4 minutes to show. Note this is the first time on my computer that I have been trying to view these logs so that might also play a factor. I also tried to look at the logs directly on the server but was getting similar time results.

Is this par for the course? Should I expect viewing the logs to be an experience like this? Is there something that I should be doing or checking? I do plan to check the log age and see if it can be purged but would that still affect looking at logs for only a short X Day period?

Yes, reading logs takes a long time in the log file viewer. A few things to fix it:

Try using xp_readerrorlog with filtered parameters to get just the data you want:

@p1 is the log file you want to look at (0 is current, 1 is the first archive, 2 is second, etc)
@p2 is null for the error log, 2 for Agent
@p3 and @p4 are strings to search for in the output

That way you just get the rows you want.

If you find yourself doing this kind of thing frequently, run a job to cycle the error log periodically (I like weekly) so that you don't have to sift through so much stuff. Plus, make sure you're not logging successful backups or successful logins to the error log.


Impact of changing schema name I have a server with two databases. Each database has a different schema name despite the fact that DB2 is a subset of DB1 (DB2 has a subset of the tables in DB1).

Not withstanding any code changes or environment files that need to be changed as a result, are there any dangers to renaming the schema of a PostgreSQL database? If so, what?  Or will a simple ALTER SCHEMA be ok?

After a pg_dump from DB1 and a restore to DB2 (with psql -f, most likely), ALTER SCHEMA will take care of all internal reverences. Obviously, any code you store outside the database must be updated - you mentioned that already.

What might be less obvious: functions bodies have to be updated manually, if there should be any references to the schema name. Can only include functions you wrote yourself.

There is also less common stuff like external references via dblink, including self-references, that would need to be adapted. You'll know about it if you are using it.

Other than that, the search_path must match your respective setups in the two databases. This concerns general settings in postgresql.conf, but also settings per DATABASE or per ROLE and possibly options supplied with the connection from your apps.

Then you should be good to go.

OR, and that's what I would do, you hack the dump. After you dump from DB1, replace all occurrences of the old schema name with an editor of your choice. Then run the edited SQL script via psql -f to restore to DB2. All done.

Of course, if the name is not unique across the whole dump, you will have to be careful to replace only relevant strings.

If your setup is huge and the schema name is not very selective, you might want to restore to DB2 as is, ALTER SCHEMA, pg_dump and delete from DB2 again, and do the rest in your editor before you restore for good.


Does SQL Server re-create system databases if they are lost/missing? We have a third-party server backup tool running every 24 hours, which backs up everything (files and database in preparation for a bare-metal restore).  After much investigation, it emerged that the tool used non copy_only backups, thus destroying the log chain on both the system databases, and our own.

(I've been researching whether the system databases should be backed up, and the consensus is generally yes because of the data they hold concerning SQL Server in general (e.g. users, agent jobs, etc). This isn't so much about their contents, but what would happen if they were missing after a restore because of the situation I describe).

To stop this crime, I've disabled the tool performing database backups, so now it only does file backups (except the MDF/LDF files).  I then have scheduled SQL Agent to run backups every hour and I then sent them to my private cloud.

In the event of complete server failure, and a bare metal backup was required, I expect that the databases wouldn't be there when SQL started back up, as they weren't backed up/restored by the tool (although I have them safely backed up).

What actually happens at this point?  Does SQL Server re-create the system databases, which I then can restore over?  Or, does it just refuse to start up and I'm heading for trouble?

Answering the initial question

No.

Providing a solution

The procedure of restoring the system databases is explained in the following Microsoft Article:

Reference: Rebuild System Databases (SQL Server 2012)

Prerequisites

Ensure you have a clear documentation of the old system:

SQL Server Instance Configuration
Service Packs and Hotfixes
Current location of database files

Restore steps

Rebuild the system databases:

Insert SQL Server 2012 installation media
Perform a rebuild using the following command:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

Replace the relevant information with the values for your instance.

Verify that the installation was successful by consulting the setup summary file:

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Logs

Restore the most recent backups of the system databases: master, msdb, (model):

When restoring the master database start the SQL Server instance from a DOS prompt with the -m parameter.
Reference: Restore the master Database (Transact-SQL) for more details
When restoring the msdb database start the SQL Server instance with the Trace Flag 3608:

  Prevents SQL Server from automatically starting and recovering any database except the master database. Databases will be started and recovered when accessed. Some features, such as snapshot isolation and read committed snapshot, might not work. Use for Moving System Databases and Moving User Databases. Do not use during normal operation.

Reference: Trace Flags (Transact-SQL)

Rebuild the ressource database:

Launch SQL Server 2012 setup program from installation media
Click Maintenance and then Repair
Select your Instance
On the Ready to Repair summary page click Repair

Restore your User databases

Please read

  Important Notice
  If you never perform a restore, you will never know if you backup (procedure) works. Test and test again.


SQL Server Baseline and Performance Optimization I've been struggling a little bit around Performance Troubleshooting including baseline and troubleshooting of SQL Performance.

Could anyone help with this or point me to where I can possibly get a helpful information around this topic?

Here are some good articles with some practical examples that you can find here:

How to detect SQL Server performance issues using baselines – Part 1 – Introduction

How to detect SQL Server performance issues using baselines – Part 2 – Collecting metrics and reporting

How to detect SQL Server performance issues using baselines – Part 3

While Part 1 will provide you some basic knowledge about what the baseline is, in Part 2 you can find info how to do that on your own using "poor man" method

Part 3 provides some examples on how you can establish baselines and how to use baselines in troubleshooting some issues via ApexSQL Monitor


Separating system databases from MDW SQL Server 2008 R2 We have the Management Datawarehouse feature configure on two of our SQL Servers. Both point to the same central database and upload their results to it.

Everything works fine with the built in reports run from the instance. However, I am taking all the data from the snapshots.diskusage table to help show how our databases have grown and formulate a storage solution for the next chunk of time. For the majority our databases are uniquely named so there is no concern there running:

SELECT *
FROM [RKYV_MDW].[snapshots].[disk_usage]
WHERE database_name = 'tempdb'

The tricky part comes with the system databases, of particular annoyance is tempdb. Seeing as both instances have a tempdb and upload their stats I have double the number of data points and the graphs turn out somewhat meaningless. I can't see anything in the table bar snapshot_id that might help me seperate the two out but I am unsure where this would be unique to a particular instances tempdb.

Does anyone have any thoughts about this? My alternative is to select every other line (from a quick ganders at the data) but feel there must be a more elegant way out there.

Join via snapshots_internal to source_info_internal which has the server instance name, eg

SELECT du.*, sii.instance_name
FROM [snapshots].[disk_usage] du
    INNER JOIN core.[snapshots_internal] si ON du.snapshot_id = si.snapshot_id
        INNER JOIN [core].[source_info_internal] sii ON si.source_id = sii.source_id
WHERE du.database_name = 'tempdb'


How to model a course > section > lesson structure where lessons can belong to either sections or courses? I have three entities:

Course
Section
Lesson

Each "course" is made up of several "lessons".
The "lessons" inside a course, can either be categorized into "sections", or not.
So, the contents of a course could look either like this:
Foo Course:
    Lesson 1
    Lesson 2
    Lesson 3
    Lesson 4

Or like this:
Bar Course:
    Section 1:
        Lesson 1
        Lesson 2
    Section 2:
        Lesson 3
        Lesson 4

So, in other words, a course can either directly have "lessons", or it can have "sections" that in turn have "lessons".
From the other perspective, a "lessons" can either directly belong to a "course", or belong to a "section" that in turn belongs to a "course".
I'm struggling with how to implement this structure in a a relational database.
If every "lesson" had to necessarily belong to a "section", it would be easy, I could just simply have a "Course" table, a "Section" table with a "CourseId" column, and a "Lesson" table with a "SectionId" column.
But my scenario is not as straightforward as that. A "section" can potentially exist as a middleman between a "course" and several "lessons", but it can also be absent, in which case a "course" directly has the lessons and no there are no "sections".
I'd appreciate any suggestions regarding how such a structure can ideally be implemented in the context of relational databases.
Thanks.

Generally when I need to handle this sort of situation in textbooks, I “hide” the full structure of a book from the author. So, for your situation, I would have the following structure:
Course
⇢ Section
  ⇢ Lesson

This way, all courses would have a minimum of one section. This offers a course author a bit of flexibility in the future if they choose to switch to (or from) a Course⇢Lesson to Course⇢Section⇢Lesson model. It also drastically simplifies the SQL queries that will be used, as everything is consistent. Other developers will appreciate this when being introduced to your system.

Disclaimer: I write textbook and online learning software for a living, so my design patterns may be biased as a result of experience working with public and private institutions.


When should I reference the User record or User Profile record I know it is a common paradigm to separate tables when building a User profile. For example, having a user table, and another table called user_profile with a foreign key to the user table.

My understanding is that the user table is better for sensitive user account data or authentication data, such as email, password, user type etc. The Profile table could have additional data of that user like first name, last name, date of birth and more.

But, what about other data that can be related to the user, and can also be modeled with a table. Some examples could be Payments and Transactions. My first guess is to link those to the profile table and that way I don't have to make any joins just to have Transactions and the name of User together (which the user table does not have). Linking to the profile, I have the user info and the Transactions. But then, when is it useful to link to the user table? What are the common paradigms?

Thanks in advance!

If both tables - user and user_profile - have as primary key the column user_id, and there is a one-to-one mapping between rows in these tables, what you have there is a vertical partitioning of a single logical entity.
In the logical data model this should be represented as a single entity type. In the physical data model that single entity type is implemented as two tables. There are reasons why you would want to do this. Security is one, as you have mentioned, though that could be handled just as well with views.
I have not come across this implementation nor seen articles advocating it. I'm not convinced this paradigm is that common.
If there could be many rows in user_profile for each row in user then you have a simple parent-child pattern. Other tables will follow the usual pattern, too.
For other tables which have obvious one-to-many relationships to user your options are not a simple binary. In the logical model the situation is simple - entity type Payments references entity type User. In the physical implementation with vertically partitioned user table you have options. If either of user or user_profile is optional the foreign key must be declared as referencing the mandatory one. If both are required then it simply doesn't matter.
To be explicit, it is OK to join in columns which are not explicitly declared as foreign keys. So you could have the DRI written as .. payments.user_id references user.user_id and legitimately write .. from user_profile up inner join payments p on up.user_id = p.user_id. That is OK; it will work.
If you really wanted to, and your DBMS didn't object, you could even declare two foreign keys for the child table. One FK will link Payment to User. The second will link Payment to User_Profile. My feeling is that this is excessive and adds little, but it is not "wrong".
To answer the stated question, I would follow the principle of least surprise. If table Payment has a column user_id, and there exists a table called User I would expect Payment.user_id to reference User.user_id.


How can I represent discrete ages in a relational database? I am using PostgreSQL to store data on a Person. I need to store whether each Person is capable of teaching a Child of each age year, from 0-17. The ages are discrete values, and a Person can have any number of the 18 (assuming 0-17 years old) assigned to their account. This will be used in a booking system, and also needs to form part of the search results.

Initially I considered creating 18 boolean fields, but this seems inefficient. Is there a better way to do it? I understand that Postgres supports JSONB, so this is an option, but I am unsure of the implications.

Is there a better way to store this data?

The ages are discrete values

OK.

  a Person can have any number of the 18 (assuming 0-17 years old) assigned to their account.

So it's a many-to-many relationship?

If so, you just decompose your data into third normal form as usual, expressing the cardinality by means of one extra relation.

Example follows. The SQL dialect is not necessarily PostgreSQL as I do not have an installation handy right now.

Schema

-- persons relation
CREATE TABLE persons (
    personId INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
    -- Any other attributes
);

-- classes relation
CREATE TABLE pupilClasses (
    age INT NOT NULL PRIMARY KEY,
    size ENUM ('small', 'medium', 'large') NOT NULL
);

-- "Tie" relation that expresses the many-to-many cardinality
-- between persons and classes
CREATE TABLE persons_pupilClasses (
    personId INT NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (personId, age),
    FOREIGN KEY (personId) REFERENCES persons (personId)
        ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (age) REFERENCES pupilClasses (age)
        ON UPDATE CASCADE ON DELETE CASCADE
);

Data

-- Let us populate with some data

-- A few classes
INSERT INTO pupilClasses (age, size) VALUES (0, 'small');
INSERT INTO pupilClasses (age, size) VALUES (1, 'small');
INSERT INTO pupilClasses (age, size) VALUES (2, 'small');
INSERT INTO pupilClasses (age, size) VALUES (3, 'small');
INSERT INTO pupilClasses (age, size) VALUES (4, 'small');
INSERT INTO pupilClasses (age, size) VALUES (5, 'small');
INSERT INTO pupilClasses (age, size) VALUES (6, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (7, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (8, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (9, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (10, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (11, 'medium');
INSERT INTO pupilClasses (age, size) VALUES (12, 'large');
INSERT INTO pupilClasses (age, size) VALUES (13, 'large');
INSERT INTO pupilClasses (age, size) VALUES (14, 'large');
INSERT INTO pupilClasses (age, size) VALUES (15, 'large');
INSERT INTO pupilClasses (age, size) VALUES (16, 'large');
INSERT INTO pupilClasses (age, size) VALUES (17, 'large');

-- A few persons
INSERT INTO persons (personId, name) VALUES (666, 'Alice');
INSERT INTO persons (personId, name) VALUES (667, 'Bertrand');
INSERT INTO persons (personId, name) VALUES (668, 'Carlos');

-- Who can teach to whom

-- Alice to 0−3 and 7−8 classes
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 0);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 1);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 2);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 3);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 7);
INSERT INTO persons_pupilClasses (personId, age) VALUES (666, 8);

-- Bertrand to 7−9, 13, 16−17 classes
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 7);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 8);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 9);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 13);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 16);
INSERT INTO persons_pupilClasses (personId, age) VALUES (667, 17);

-- Carlos can't teach anyone yet ☹

Logic

Let us get a report on those who can teach any classes

SELECT persons.name, GROUP_CONCAT(pupilClasses.age) classes
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    INNER JOIN pupilClasses
        ON pupilClasses.age = persons_pupilClasses.age
GROUP BY persons.personId;

Result:

+----------+----------------+
| name     | classes        |
+----------+----------------+
| Alice    | 0,1,2,3,7,8    |
| Bertrand | 7,8,9,13,16,17 |
+----------+----------------+

Let us get a report of who can teach to whom, if anyone

SELECT persons.name, GROUP_CONCAT(pupilClasses.age) classes
FROM persons
    LEFT JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    LEFT JOIN pupilClasses
        ON pupilClasses.age = persons_pupilClasses.age
GROUP BY persons.personId;

Result:

+----------+----------------+
| name     | classes        |
+----------+----------------+
| Alice    | 0,1,2,3,7,8    |
| Bertrand | 7,8,9,13,16,17 |
| Carlos   | NULL           |
+----------+----------------+

Who teach class 3?

SELECT persons.name
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
WHERE persons_pupilClasses.age = 3;

Result:

+-------+
| name  |
+-------+
| Alice |
+-------+

Who can teach classes 7 or 9?

SELECT DISTINCT persons.name
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
WHERE persons_pupilClasses.age IN (7,9);

Result:

+----------+
| name     |
+----------+
| Alice    |
| Bertrand |
+----------+

Who can teach large sizes?

SELECT DISTINCT persons.name
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    INNER JOIN pupilClasses
        ON pupilClasses.age = persons_pupilClasses.age
WHERE pupilClasses.size = 'large';

Result:

+----------+
| name     |
+----------+
| Bertrand |
+----------+

Although you do not mention it in your description, I'm willing to bet there is at least one more important relation on your actual business scenario: pupils. And they probably move from one class to the next. A simple way to represent that relationship would be:

-- pupils relation
CREATE TABLE pupils (
    pupilId INT NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT NOT NULL,
    FOREIGN KEY (age) REFERENCES pupilClasses (age)
        ON UPDATE RESTRICT ON DELETE RESTRICT
);

-- A few pupils
INSERT INTO pupils (pupilId, name, age) VALUES (1313, 'Donald', 7);
INSERT INTO pupils (pupilId, name, age) VALUES (1314, 'Ernest', 7);
INSERT INTO pupils (pupilId, name, age) VALUES (1315, 'Frank', 9);
INSERT INTO pupils (pupilId, name, age) VALUES (1316, 'Gertrude', 0);
INSERT INTO pupils (pupilId, name, age) VALUES (1321, 'Hans', 13);

And some logic examples:

Who teaches whom?

SELECT persons.name teacher, pupils.name pupil
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    INNER JOIN pupils
        ON pupils.age = persons_pupilClasses.age;

Result:

+----------+----------+
| teacher  | pupil    |
+----------+----------+
| Alice    | Donald   |
| Bertrand | Donald   |
| Alice    | Ernest   |
| Bertrand | Ernest   |
| Bertrand | Frank    |
| Alice    | Gertrude |
| Bertrand | Hans     |
+----------+----------+

Who are each person's pupils?

SELECT persons.name teacher, GROUP_CONCAT(pupils.name) pupils
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    INNER JOIN pupils
        ON pupils.age = persons_pupilClasses.age
GROUP BY persons.personId;

Result:

+----------+--------------------------+
| teacher  | pupils                   |
+----------+--------------------------+
| Alice    | Donald,Ernest,Gertrude   |
| Bertrand | Donald,Ernest,Frank,Hans |
+----------+--------------------------+

Who are each pupil's teachers?

SELECT pupils.name pupil, GROUP_CONCAT(persons.name) teachers
FROM persons
    INNER JOIN persons_pupilClasses
        ON persons.personId = persons_pupilClasses.personId
    INNER JOIN pupils
        ON pupils.age = persons_pupilClasses.age
GROUP BY pupils.pupilId;

Result:

+----------+----------------+
| pupil    | teachers       |
+----------+----------------+
| Donald   | Alice,Bertrand |
| Ernest   | Alice,Bertrand |
| Frank    | Bertrand       |
| Gertrude | Alice          |
| Hans     | Bertrand       |
+----------+----------------+

From this a couple things should be clear:

age may be a bit of a misnomer it may not correspond with the actual natural age of pupils attending those classes.
It's probably a good idea to avoid the temptation of treating it as a continuous range. This is why I quoted your statement that these are discrete values.

For the rest, unless I misunderstood, seems a fairly straightforward normalisation problem.

By the way:

  Initially I considered creating 18 boolean fields,

Where? In the person relation?

  but this seems inefficient.

It's not inefficient. It's a red herring.

Normalise your design first. Once you've gained experience with the data you will be handling you can start thinking about introducing optimisations (which may involve denormalisation). Pre-emptive optimisation is just one of those terrible ideas though.