Why is a functional dependency described as a "one-to-many" relationship? As far as I know, the definition of a functional dependency reads that, given the attributes X and Y, "each X value is associated with precisely one Y value". But then the author of the article that I read called this a "one-to-many" relationship. Isn't this really "one-to-one"..?

I think that the FD can't be described as "One-to-one" relationships. In One-to-one relationships, the X value identifies a Y value and vice-versa. In FD this doesn't happen. In fact, the Y values can be shared from different tuples with the same X value.

Think to a simple table with only cities, states and nations (And don't care about the redundancy). We know that the state identifies one and one nation, but a single nations doesn't identify a single state.

So, generally  X implies Y but Y doesn't imply X.

A last observation: an one-to-one relationship need that the tables involved in the relationship must have the same number of rows, in order to associate to every a one b and to associate to every b one a. In the textbook you'll find a lot of FD in which this doesn't happen.

So we can describe a FD as an association of a Y value to many (only sometimes one) X values. So it's called an one-to-many relationship.


Advance primary key after mass insert I have a table with a few rows in it. It has a primary. I insert a large number of rows, including all fields including the primary key. Now when I try to an insert without specifying the primary key it fails because it's trying to use a number that was already used as a primary key in the previously inserted rows. How can I tell the table to create new primary keys at numbers higher than the existing keys?

For example, say I have rows with primary keys 1-8 in the table and I do a mass insert of rows with primary keys 9 through 64. Then I try to do a single insert without a primary key and it fails because it's trying to use 9. The next try fails because it's trying to use 10. And so on. I want to tell the table to start counting new primary keys at 65. How can I do that?

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl;

Table name and column name are two separate parameters to the pg_get_serial_sequence() function.
And you don't need a subselect.
Details:

How to reset postgres' primary key sequence when it falls out of sync?
Reset the sequence of a serial PK with dynamic SQL


Is there a way to calculate yesterday / today of last month / year in SQL? Is there a way to calculate today (and yesterday) of last month (and last year) in SQL?

Assuming today is 26-May-2015 then how to find?

Today of last year i.e. 26-May-2014?
Today of last month i.e. 26-April-2015?
Yesterday of last year i.e. 25-May-2014?
Yesterday of last month i.e. 25-April-2015?

Shouldn't really need any hacks to handle leap years, but it depends on what results you expect. Typically you just subtract the larger component first, so subtract a month before you subtract a day, instead of the other way around.

Do any of these produce results you don't expect? If so, which ones are "wrong" to you, and why?

DECLARE @d TABLE(d DATE);

INSERT @d(d) VALUES('20150330'),('20150331'),
      ('20150430'),('20150531'),('20150526'),

      ('20160229'), -- Leap day: test "Today of last year" and "Yesterday of last year"

      -- Month after leap day: test "Today of last month" and "Yesterday of last month"
      ('20160329'),('20160330'),('20160331'),

      -- March 1st in possible leap year: test "Yesterday"
      ('20160301'), -- test regular leap year
      ('20000301'), -- test century divisible by 400
      ('19000301'), -- test century not divisible by 400 (not a leap year)

      -- March 1st in year AFTER possible leap year: test "Yesterday of last year"
      ('20170301'), -- test regular leap year
      ('20010301'), -- test century divisible by 400
      ('19010301'); -- test century not divisible by 400 (not a leap year)

SELECT

  [Today] = d,
  [Today of last year] = DATEADD(YEAR, -1, d),
  [Today of last month] = DATEADD(MONTH, -1, d),

  [Yesterday] = DATEADD(DAY, -1, d),
  [Yesterday of last year]  = DATEADD(DAY, -1, DATEADD(YEAR, -1, d)),
  [Yesterday of last month] = DATEADD(DAY, -1, DATEADD(MONTH, -1, d))

FROM @d;

You could also consider using a calendar table if you want to handle leap years, holidays, weekends, etc. differently. What you really need to do is fully understand and explain your requirement. With many months having 31 days, and with leap years especially, each possible combination of "today or yesterday last month or year" can be open to multiple interpretations.


Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)? There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for instance, I can do this..

WITH w AS (
  SELECT *
  FROM ( VALUES (1) ) AS t(x)
)
SELECT *
INTO foo
FROM w;

But, I can't do this..

WITH w AS (
  SELECT *
  FROM ( VALUES (1) ) AS t(x)
)
CREATE TABLE foo AS
SELECT * FROM w;

Or, I get

ERROR:  syntax error at or near "CREATE"
LINE 5: CREATE TABLE foo AS

How would I do that using the standardized CTAS syntax.

It may be awkward, but you have to move the WITH clause from the top into the query. It's a part of the statement to generate the table, and that statement comes after the CREATE TABLE, so you would use this syntax.

CREATE TABLE foo AS
WITH w AS (
  SELECT *
  FROM ( VALUES (1) ) AS t(x)
)
SELECT * FROM w;

Also worth noting that it's not explicit in the official docs, it just falls under query

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]

The docs mention two other use cases for the WITH keyword,

To set storage_options
To set NO DATA

You can see both of them in use like this,

CREATE TABLE foo
WITH (fillfactor = 100)
AS
  WITH w AS (
    SELECT *
    FROM ( VALUES (1) ) AS t(x)
  )
  SELECT * FROM w
WITH NO DATA;

Now you've got a table with nothing.

TABLE foo;
 x
---
(0 rows)


Is it possible to backup with dump or rman from Oracle to MS Sql Server? We need to move some tables and schemas from an Oracle database to an MS SQL Server database. We have only Oracle dump files. We import them into an Oracle database. Then we need to use a replication tool to move the data from Oracle to the SQL Server. Is it possible to bypass the replication tool for the moving task? Is it possible to move data from Oracle to the SQL Server using Oracle RMAN or DUMP files? Thanks...

No, I don't think that this is possible. Both tools use Oracle specific file formats and can work only with Oracle databases. So the way to move data from an Oracle dump file created by the Oracle exp or expdp tool is to import the dump in an Oracle database and move it from this database to an SQL Server database.

Note 1: Toad from Quest is able to read Oracle export dumps and I cannot be 100% sure that there exists a tool that hacked the Oracle dump format to read the data and write it to SQL server.

Note 2: It is possible to migrate data from an Oracle database  to a Microsoft SQL Server database. Evidently Oracle ist not much interested in such migrations but Microsoft is, so you should search the Microsoft pages for tools, e.g. here is one for Oracle 12c and SQL Server 2017. HR is a sample schema in provided by Oracle. Oracle 9i is a rather old version, so I am not sure if this will work for Oracle 9i.

Note 3: I think it will make sense that you describe the whole problem more detailed. Why do you want to copy the data to your system, what are the requirements and constraints. What is the size of the data, what type of objects do you want to migrate,... Maybe it make sense to open a new question that describes the whole scenario. Maybe the question that you put here doesn't really help you to solve your original problem.


Postgressql text search: pg_trgm index with order by going very slowly I have a really slow query(~30 seconds) on a large text search DB that I could really use some help with.

I'm searching for ordered text, in a long file path string. So the path could be: folder1/cat 123/dog 234

I'm using ilike '%search_term_1% %search_term_2%' to do searches. So cat dog would return the result above.

I have a table with two columns:

create table file (
  id bigserial primary key,
  path varchar(2048) not null,
  peers integer);

I want to do a text search on path, and order by peers desc nulls last

I've created the following two indices to speed up the queries:

create index idx_file_path_tri on file using gin (path gin_trgm_ops);
create index idx_file_peers on file(peers nulls last);

Here's the explain anaylze results:

explain analyze select * from file where path ilike '%cat%' order by peers desc nulls last limit 15;

It looks like I need a compound index with the gin, but it won't let me build one based on peers desc nulls last...

Limit  (cost=7512.09..7512.13 rows=15 width=126) (actual time=342729.147..342729.153 rows=15 loops=1)
   ->  Sort  (cost=7512.09..7516.48 rows=1753 width=126) (actual time=342729.144..342729.145 rows=15 loops=1)
         Sort Key: peers DESC NULLS LAST
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Bitmap Heap Scan on file  (cost=809.59..7469.09 rows=1753 width=126) (actual time=2143.088..342610.395 rows=219
580 loops=1)
               Recheck Cond: ((path)::text ~~* '%cat%'::text)
               Heap Blocks: exact=38190
               ->  Bitmap Index Scan on idx_file_path_tri  (cost=0.00..809.15 rows=1753 width=0) (actual time=2108.286..2108
.286 rows=223590 loops=1)
                     Index Cond: ((path)::text ~~* '%cat%'::text)
 Planning time: 0.328 ms
 Execution time: 342729.330 ms
(11 rows)

Note: the queries go extremely quickly without the order by.

The core performance problem is the selectivity estimation for the predicate path ilike '%cat%', which is off by a factor of 130:

  (cost=0.00..809.15 rows=1753 width=0) (actual time=2108.286..2108
  .286 rows=223590 loops=1)

Probably leading to a sub-optimal query plan.

In this particular case, Postgres identifies a quarter million rows, just to filter the top 15. A giant waste of time. Depending on missing information, some other query plan (some other query) will be much more efficient. Here is a related question with some generic techniques in the answers that apply in similar fashion to your case:

Can spatial index help a "range - order by - limit" query

Selectivity estimation for pattern matching is hard, but there have been major improvements in Postgres 9.6. Detailed explanation in this closely related answer:

Trigram index for for ILIKE patterns not working as expected

Your best course of action may be to install a current version of Postgres.

Of course, there may be all kinds of additional problems, your question does not reveal details.

Or you may be using the wrong tool altogether, you did not define what you are trying to search exactly.

Concerning:

  It looks like I need a compound index with the gin, but it won't let me build one based on peers desc nulls last ...

Again: essential details like your CREATE INDEX command and the resulting error msg are missing. My educated guess: You need to install the additional module btree_gin first. You probably know that by now, since you just commented on this related answer explaining as much:

Best index for similarity function

But don't bother, it probably won't help with your query.


Improving database design, is this a valid case for entity attribute values? Well currently we are not using eav, however our database seems a bit finicky and one suggestion was something along the lines of entity attribute values.
What we have is a table which acts as "translations". It has multiple rows with each line describing a translation. With multiple column, each column being a language.
CREATE TABLE table_name (
    id serial,
    organisation int,
    key text,
    en_gb text,
    nl_nl text,
   ....
);

This works fine (omitted constraints). However one feature is that users need to supply their own translations for data they inserted. In whatever language they wish. (Given the language needs to be formally described in iso 639-1)
This creates the weird situation where upon inserting not only a new row might be added. But the database schema also might be modified: a check is performed if the language column exists, if it is not it is added.
Which also adds a lot of columns (with nulls) for all fields - a minor problem though.
One solution would be to instead of having a column for each language use a json "translations" so that not each field/row needs to have a value for everything. This was dismissed on grounds that the far majority of the time we modify a single field, in an already existing column: apart from 6 or so languages where we operate we do not expect many more to be inserted.
Another solution was to use EAV, with instead of using a single row for all translations from a single field there is a new row for each language/translation of a certain field.
CREATE TABLE table_name (
    id serial,
    organisation int,
    key text,
    language text,
    translation text,
   ....
);

This seems a good case for entity attribute values right? Apart from that the database now is quite convoluted and isn't easily visualized as a neat table. So is it?
The ability to visualize as a neat table I personally like since insertion happens most often by providing a csv, so the visualization mirrors that.

For a given entity and language, could there be more than one translation provided? If so, how would they be distinguished from each other?

The user just inserts translations for organisations. A translation would be:
{
    organisation: 1,
    key: 'orgname',
    'en-us': 'somename',
    'nl-nl': 'dutchname',
    'fr-fr': 'french-organisation-name'
}

Uniqueness is given by (organisation, key) (or when transformed to multiple lines (organisation, key, language)). When multiple users edit, given they have the right to do so, they translate "for everyone", there is no merit of who did the translation, or for whom the translation is.

I think that your proposed approach with a table row per language is fine, but I would definitely have language be a foreign key to a lookup table of languages.
Otherwise you'll end up with languages like English, en, EN or sprfz that people enter on a whim, and you will get complaints why the program does not display the proper English version of the text. Prefill that lookup table with entries for all conceivable languages.


backup to NAS using sqlcmd I've been searching for possible duplicates before asking but can't find anything. The closest was Backup SQL Server To Nas but not exactly my case.

We have a brand new Synology NAS configured, due to some security needs is configured to use a dedicated user and pwd not included on the active directory or alike. Already tested and is ok, can login, see the files and all rights are ok.

On the other side, we have backups running using Ola Hallengren useful scripts, by running slqcmd.

Question: how I can provide user/pwd credentials to access the NAS? Have tried using -U -P but got following error. Then tried with -S but also error. What I'm doing wrong here? I guess should be something silly whatever I'm missing, but can't possible find it.

Sqlcmd: The -E and the -U/-P options are mutually exclusive.  Process Exit Code 1.  The step failed.

[soapbox]

  I would tell them "for security reasons" you should provide Windows
  Authentication access to that storage. Ask them if they really want
  authentication information in plain text saved to access a box that stores all the data for the organization and would give a user full access to their data (unless you are encrypting your backups)?

[/soapbox]

For your specific issue the parameters in sqlcmd for authentication are to access a SQL Server instance, not to access external storage or other resource. You will need to adjust the scripts to more or less map the drive with the required credentials and then use that path within your backup script.

If you are on SQL Server 2014 I would suggest using PowerShell script or SQL Agent step as this contains easier methods of working with the file system and mapping drives. You can quickly check access to the path before running the T-SQL code to ensure it does not fail prematurely.


Concatenate two functions in a result I'm trying to add this as one of my columns:

REPLACE(REPLACE(REPLACE(dbo.new_truck.new_Description| CHAR(13)| '
')| CHAR(10)| '
')| '"'| 'in')+'Location: '+IF(dbo.new_truck.ditl_Inventory=True,'Vancouver','Edmonton')

But after executing the SELECT, SQL Server changes it to this:
REPLACE(REPLACE(REPLACE(dbo.new_truck.new_Description| CHAR(13)| '
')| CHAR(10)| '
')| '"'| 'in') + 'Location: ' + 'IF(dbo.new_truck.ditl_Inventory=True,[Edmonton],[Vancouver])'

It appears that I cannot concatenate a function with a string, with another function. If I take everything after "Location:" out, of course, it works. But without the location :)

Any other way to accomplish this? I could write this into the PHP code that handles the output of this query, but thought I'd try this first

Thanks for your help!

I have instead edited my PHP code that handles this data:

//Show the location in the truck description
if ($mainrow[24] == "0") {
$mainrow[16] = $mainrow[16] . " Location: Edmonton";
} else if ($mainrow[24]=="1") {
$mainrow[16] = $mainrow[16] . " Location: Vancouver";
}

where $mainrow[24] is the "Inventory" column, and $mainrow[16] is the Description column.


Does the order of columns in a table's definition matter? When defining a table, it's helpful to order the columns in logical groups and the groups themselves by purpose. The logical ordering of columns in a table conveys meaning to the developer and is an element of good style.

That is clear.

What is not clear, however, is whether the logical ordering of columns in a table has any impact on their physical ordering at the storage layer, or if it has any other impact that one might care about.

Apart from the impact on style, does column order ever matter?

There is a question on Stack Overflow about this, but it lacks an authoritative answer.

Does the logical ordering of columns in a table have any impact on their physical order at the storage layer? Yes.

Whether it matters or not is a different issue that I can't answer (yet).

In a similar manner to that described in the frequently linked article from Paul Randal on the anatomy of a record, let's look at a simple two column table with DBCC IND:

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

USE master;
GO

IF DATABASEPROPERTY (N'RowStructure', 'Version') > 0 DROP DATABASE RowStructure;
GO

CREATE DATABASE RowStructure;
GO

USE RowStructure;
GO

CREATE TABLE FixedLengthOrder
(
    c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , c2 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL
    , c3 CHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL
);
GO

INSERT FixedLengthOrder DEFAULT VALUES;
GO

DBCC IND ('RowStructure', 'FixedLengthOrder', 1);
GO

The output above shows that we need to look at page 89:

DBCC TRACEON (3604);
GO
DBCC PAGE ('RowStructure', 1, 89, 3);
GO

In the output from DBCC PAGE we see c1 stuffed with the character 'A' before c2's 'B':

Memory Dump @0x000000000D25A060

0000000000000000:   10001c00 01000000 41414141 41414141 †........AAAAAAAA
0000000000000010:   41414242 42424242 42424242 030000††††AABBBBBBBBBB...

And just because, lets bust open RowStructure.mdf with a hex editor and confirm the 'A' string preceeds the 'B' string:

Now repeat the test but reverse the order of the strings, placing the 'B' characters in c1 and the 'A' characters in c2:

CREATE TABLE FixedLengthOrder
(
    c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , c2 CHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL
    , c3 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL
);
GO

This time our DBCC PAGE output is different and the 'B' string appears first:

Memory Dump @0x000000000FC2A060

0000000000000000:   10001c00 01000000 42424242 42424242 †........BBBBBBBB
0000000000000010:   42424141 41414141 41414141 030000††††BBAAAAAAAAAA...

Again, just for giggles, lets check the hex dump of the data file:

As Anatomy of a Record explains, the fixed and variable length columns of a record are stored in distinct blocks. Logically interleaving fixed and variable column types has no bearing on the physical record. However, within each block the order of your columns does map to the order of bytes in the data file.

CREATE TABLE FixedAndVariableColumns
(
    c1 INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , c2 CHAR(10) DEFAULT REPLICATE('A', 10) NOT NULL
    , c3 VARCHAR(10) DEFAULT REPLICATE('B', 10) NOT NULL
    , c4 CHAR(10) DEFAULT REPLICATE('C', 10) NOT NULL
    , c5 VARCHAR(10) DEFAULT REPLICATE('D', 10) NOT NULL
    , c6 CHAR(10) DEFAULT REPLICATE('E', 10) NOT NULL
);
GO

Memory Dump @0x000000000E07C060

0000000000000000:   30002600 01000000 41414141 41414141 †0.&.....AAAAAAAA
0000000000000010:   41414343 43434343 43434343 45454545 †AACCCCCCCCCCEEEE
0000000000000020:   45454545 45450600 00020039 00430042 †EEEEEE.....9.C.B
0000000000000030:   42424242 42424242 42444444 44444444 †BBBBBBBBBDDDDDDD
0000000000000040:   444444†††††††††††††††††††††††††††††††DDD

See also:

Column order doesn’t matter… generally, but – IT DEPENDS!


Multiple left join hangs server I have a problem with left join query. It connects 3 tables and calculates sales value for categories.

Query:

SELECT
    categories.name,
    categories.id,
    SUM((orders_products.product_price_gross + orders_products.option_price)*(1 - (orders_products.rebate/100))  * orders_products.product_qty) AS suma
FROM product_categories
LEFT JOIN orders_products
    ON product_categories.product_id = orders_products.product_id
LEFT JOIN categories
    ON product_categories.category_id = categories.id
GROUP BY categories.name, categories.id
ORDER BY suma DESC

    categories
id  int(11) NO  PRI     NULL    auto_increment
parent_categories   int(11) NO      0
name    varchar(250)    NO

orders_products
id  int(11) NO  PRI     NULL    auto_increment
order_id    int(11) NO  PRI     0
product_id  int(11) NO      0
product_name    varchar(255)    NO
product_price_gross     double(8,2) NO      0.00
product_qty     int(3)  NO      0
option_price    decimal(8,2)    NO      NULL
discount    decimal(8,2)    NO      NULL
rebate  int(11) NO      NULL

product_categories
product_id  int(11) NO  MUL     0
category_id     int(11) NO      0
ord     int(11) NO      0
id  int(11) NO  PRI     NULL    auto_increment

Tables are big - categories - 2808 rows, product_categories - 84 000, orders_products - 47500

It should display list of categories and sales values for each one. Instead of that it hangs the server totally and restart is needed. I used many different queries from StackOverflow but any of them hangs server. I have no idea why.

MySQL's query optimizer is not very smart, anything you can do to help it will go a long way to improving your query performance.

Couple of issues I see at a glance without being able to see an execution plan (please edit your question with the output of explain select * from table...:

Your first join is going from an indexed column on your left table (product_categories.product_id) to a non-indexed column of your right table (orders_products.product_id). With no where clause, that's going to result in a very heavy nested loop through every record on the orders_product for each row in your product_categories table. To speed it up you could add an index to orders_products.product_id, ideally you would also limit the left table using some sort of criteria in a where clause.
Your second join is doing a similar mistake. Your left table column is not indexed while your right column is a PK. I think you're probably signing up for another long queue of nested loops through each of the tables.

The fact that you're left joining also means that regardless of what's in your right table, you're going to have to inspect every element in your left table. Inner joins can be more performant, but without indexing your columns properly and without the use of some kind of where clause criteria to help limit the rowsets you have to inspect, this is going to be a heavy query.

In summary:

index your join columns
use inner joins where possible
use limiting criteria in your where clause if possible ti reduce the amount of index scanning needed.


Need to Sync postgresql DB to remote I have two PostgreSQL standalone servers A and B. From server A I need to sync only one database and table to server B. How should I proceed?

As answered before, you have loads of different ways of doing this. Here are a few:

External logical replication: projects like Slony, Londiste, Bucardo can replicate the needed objects through triggers;
Extensions like pglogical;
In-core logical replication which is available since Postgres 10
Foreign Data Wrappers: you could use postgres_fdw to make your table readable from a remote server. This means connecting to the other table, not replicating it.
Backup and restore: you could cron a pg_dump and restore of your table from server A to B.
Physical Replication: you could replicate the whole A cluster on cluster B using streaming replication. This could give you a nice disaster recovery solution. On the B server, you could then issue one of the solutions above to a second instance on the server B.
and probably several other ways :-).


mysql optimize table crash When I try OPTIMIZE TABLE  `table` (MyISAM) on a table which is about 300MB, then it is crashed and must be repaired. What could cause this problem? The same problem occurs on other tables over 300MB.

MYISAM tables are very fragile and get damaged/crash every now and then. First advice is to move to Innodb engine it is simply performed by using alter table command.

Now, to fix crashed MYISAM tables the proper way is to bring the MYSQL down and once it is down and there is no traffic you can fix them using this command line statement:

myisamchk --silent --force --fast  /path/table-name.MYI

Trying to repair/optimise MYISAM tables on a live server usually does not work. From MYSQL CLI the command should be

mysql> repair table tablename;

instead of optimize table if the table was crashed.


Using an RDBMS for querying tens of Terabytes of time-series data? I stumbled over an algorithmic / data-structures question over on SO, which I'll short quote:

  (...) opinions regarding best of
  breed data structures to be used for indexing time-series (aka
  column-wise data, aka flat linear).

  Queries that will be required:

All values in the time range [t0,t1]

All values in the time range [t0,t1] that are greater/less than v0

All values in the time range [t0,t1] that are in the value range[v0,v1]

  The data sets consist of summarized time-series (...)
  The
  data set(s) in question are about 15-20TB in size, hence processing is
  performed in a distributed manner - because some of the queries
  described above will result in datasets larger than the physical
  amount of memory available on any one system.

  Distributed processing in this context also means dispatching the
  required data specific computation along with the time-series query,
  so that the computation can occur as close to the data as is possible
  - so as to reduce node to node communications (somewhat similar to map/reduce paradigm) - in short proximity of computation and data is
  very critical.

I'll readily admit that problems of this scale are way over my head, but my first hunch (even with the data sizes mentioned) given this problem would have been to ask whether they checked if large-scale RDBMS (well, I guess Oracle, or Oracle, right?) could handle this in a sane way.

So here's the question: Can a (enterprise?) RDBMS handle this kind of problem today with acceptable performance vs. a "hand-coded" solution.

Note: Hope this isn't too vague, and feel free to re-tag as appropriate :-)

Answering this from one point of view: SQL Server 2012 columnstores could easily handle this. I've seen it working and once segment elimination and batch processing kick in, those TBs are reduced to very few actual IOs an the results get churned in few ms (ie. most data is eliminated upfront and there is simply no need to scan 100s of TBs). The queries you asked about are exactly the kind columnstores are designed for. This is such an efficient storage/processing paradigm that there's simply no need for distributed computing, even at hundreds of TB of data.


ORA-03135 connection lost contact First i connect to oracle remotely, then open pl/sqldeveloper create one table. Then i am trying to import csv file to PL/sql developer. Importing process starts normally, but every day midnight we face

  ORA-03135 connection lost contact.

The file is almost 3 GB containing 20 million records.

the importing process stop after 10 million records.

I think check your network connection.
In between your network connection interrupted.
So In your sqlnet.ora and also on sqlnet.ora on your server try to change inbound connection or outbound connection and also try to set connection timeout parameter in sqlnet.ora file.


Finding the median of column values per row I've tried the below query, but the median value shown is wrong.

Query

select
(select cast(Avg(TotAvg * 1.0 )as decimal(6,2))
 from (values (convert(decimal(6,2), a)),(convert(decimal(6,2), b))
             ,(convert(decimal(6,2), c)),(convert(decimal(6,2), d))
             ,(convert(decimal(6,2), e)),(convert(decimal(6,2), f))
             ,(convert(decimal(6,2), g)),(convert(decimal(6,2), h))
             ,(convert(decimal(6,2), i)),(convert(decimal(6,2), j))
             ,(convert(decimal(6,2), k)),(convert(decimal(6,2), l))
             ,(convert(decimal(6,2), m)),(convert(decimal(6,2), n))
             ,(convert(decimal(6,2), o)),(convert(decimal(6,2), p))
             ,(convert(decimal(6,2), q)),(convert(decimal(6,2), r))
             ,(convert(decimal(6,2), s)),(convert(decimal(6,2), t))
             ,(convert(decimal(6,2), u))
    ) as Totalavg(TotAvg)
) as Median
from temp

Column values are a to u in a row.

Table Values

First row - 1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8
Second row - 1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8

Create table script

create table temp
(
    id int identity(1,1),
    a decimal(6,2),
    b decimal(6,2),
    c decimal(6,2),
    d decimal(6,2),
    e decimal(6,2),
    f decimal(6,2),
    g decimal(6,2),
    h decimal(6,2),
    i decimal(6,2),
    j decimal(6,2),
    k decimal(6,2),
    l decimal(6,2),
    m decimal(6,2),
    n decimal(6,2),
    o decimal(6,2),
    p decimal(6,2),
    q decimal(6,2),
    r decimal(6,2),
    s decimal(6,2),
    t decimal(6,2),
    u decimal(6,2)
)

Insert Table Script

insert into temp
    (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
values
    (1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8)

insert into temp
    (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
values
    (1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8)

First step is to unpivot columns into rows. Then you can enumerate the values using row_number and pick the middlest value from there:

select id, avg(val)
from (
    select id, val
         , count(*) over (partition by id) as c
         , row_number() over (partition by id order by val) as rn
    from temp unpivot (
             val for col in (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
         ) as x
) as y
where rn IN ((c + 1)/2, (c + 2)/2)
group by id;


Fail to execute SQL command asynchronously I want to invoke a SQL command INSERT INTO for 3,000,000 times to insert 3,000,000 records via ADO.

Other than the BULK INSERT command, I also want to utilize the ADO.Command object to execute the SQL command asynchronously.

So I do as follows:

  CString strSQLChange;

  _ConnectionPtr pConnection = NULL;
  _CommandPtr pCmdChange = NULL;

  // Open connection.
  pConnection.CreateInstance(__uuidof(Connection));
  pConnection->Open (strCnn, "", "", adConnectUnspecified);

  // Create command object.
  pCmdChange.CreateInstance(__uuidof(Command));
  pCmdChange->ActiveConnection = pConnection;

  for (UINT uIndex = 0; uIndex < 3000000, uIndex ++)
  {
     strSQLChange.Format(_T("INSERT INTO MyTable VALUES (%u, %u, %u);"), uIndex, uIndex *2, uIndex *3);
     pCmdChange->CommandText = _bstr_t(strSQLChange);
     pCmdChange->Execute(NULL, NULL, adCmdText | adAsyncExecute | adExecuteNoRecords);
  }

The first execution is OK. But the second one will cause the following error

  Operation cannot be performed while connecting asynchronously

Does that means the ADO command object cannot execute the SQL command asynchronously?

I am also thinking about using BULK INSERT command, but since there are multiple tables, I also need to execute BULK INSERT command multiple times. It is better to execute them asynchronously so executing SQL commands asynchronously is also a solution I need to seek.

You need create a separate pCmdChange object for each INSERT:

for (UINT uIndex = 0; uIndex < 3000000, uIndex ++)
{
     // Create command object.
     pCmdChange.CreateInstance(__uuidof(Command));
     pCmdChange->ActiveConnection = pConnection;
     pCmdChange->CommandText = strSQLChange;

     pCmdChange->Execute(NULL, NULL, adCmdText | adAsyncExecute | adExecuteNoRecords);
}

Keep in mind that your server probably won't like 3,000,000 near-concurrenct commands. You may run into SQL server dead-locks and or other connectivity issues.


In Subselect, using GetDate instead of a @variable leads to worse execution plan Execution plans

This is a simplified version of the real-life problem. When using GetDate in subselect, the plan is worse and different. the 2 queries:

select * , (select count(1)
            from hamsfa.customer as sub
            where sub.tstamp_nosync > @old
              and sub.cus_id > main.cus_id) as x
from hamsfa.customer as main ;

select * , (select count(1)
            from hamsfa.customer as sub
            where sub.tstamp_nosync > dateadd(year, -100, getdate())
              and sub.cus_id > main.cus_id) as x
from hamsfa.customer as main ;

What I don't fully understand is why.

The variable should be by my understanding be worse.

There is another query where this leads to a difference of almost 700% (1s vs 7s).

So the question is: Why does a variable produce better plan even through it should be worse by my experience?

EDIT: Statistics are up to date and correct

When you use the variable, SQL Server makes a guess at the selectivity of the  tstamp_nosync predicate. For the > operator, the guess is 30%. The table contains 3052 rows, so estimation guesses that 30% of 3052 = 915.6 rows will pass that test.

When you use the dateadd(year, -100, getdate()) expression, SQL Server can produce a much more accurate estimate of the number of rows that will qualify for that predicate.

Your build of SQL Server 2012 does not have the Actual Rows Read property, so the number of rows that actually match is not available in your second plan. That feature was added in Service Pack 3 of SQL Server 2012 (you are still on Service Pack 1, for some reason).

From what I can tell, the better estimate for the dateadd expression is that all rows will qualify. This is apparent from the output of the Eager Index Spool in the first plan. The 3052 rows are tested 3052 times (once per iteration of the nested loop join) with the tstamp_nosync predicate, and all pass. The result is 3052 * 3052 = 9,314,704 rows in total. This is shown as the actual number of rows from the spool:

The general expectation that using variables will result in a worse execution plan is sound, because specific estimations are usually more accurate than complete guesses; however, the other predicate sub.cus_id > main.cus_id in the query will always result in a 30% guess.

For the variable query, both predicates are estimated with a 30% guess. The combined estimate is 3052 * 30% * 30% = 274.68 rows (seen at the input to the Stream Aggregate):

For the dateadd query, there is one 30% guess (seek predicate) and a 100% selectivity estimate (residual predicate), giving a combined estimate of 915.6 rows as noted previously. This estimate is shown at the output of the Clustered Index Seek:

The difference in estimates explains the optimizer's different plan selections.

With fewer rows estimated for the variable case, the estimated cost is lower. The better estimate is costed higher, for similar reasons. Nevertheless, both plans are based on inaccurate estimations because of the guess involved in the cus_id predicate.

In terms of observed performance, which plan performs better rather depends on the details of your local environment. The variable plan scans the source table once and builds an indexed temporary table in tempdb. The non-variable plan simply accesses the base table each time with a seek (guessed 30% selectivity) and residual predicate (computed 100% selectivity). Which performs better in practice depends on multiple factors.

Given correct information for both predicates, the optimizer would be more likely to generate the plan that is better in practice.

It often pays to be very conscious of data types. There is an implicit conversion to datetime2(4) in your execution plan because that is the type of the column, and the variable is something else (datetime perhaps). The result of the dateadd expression is also datetime, and also requires implicit conversion. This conversion does not appear to be important in this specific case, but it often is. For example, see:

Performance Surprises and Assumptions : DATEADD() by Aaron Bertrand.


Get unmatched rows for foreign key Consider two tables:

CREATE TABLE Users(
  id INT PRIMARY KEY,
  name VARCHAR(128)
) Engine=InnoDB

CREATE TABLE Tags(
  id INT PRIMARY KEY,
  user REFERENCES Users(id),
  tag VARCHAR(128)
) Engine=InnoDB

I would like to know which Users do not have any Tags assigned to them. I could do a LEFT JOIN and then add a WHERE clause for no Tags, but as there are tens of thousands of Users and each could have a few tens of Tags, I think that would be impractical. Is there a better way?

This is running on MySQL 5.5 and 5.6 on Ubuntu Server 12.04 running in Amazon EC2.

A NOT EXISTS can help in this case:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Users(Id INT PRIMARY KEY);
CREATE TABLE Tags(Id INT PRIMARY KEY,User INT REFERENCES User(Id));

INSERT INTO Users(Id) VALUES(1);
INSERT INTO Users(Id) VALUES(2);

INSERT INTO Tags(Id,User)VALUES(1,1);

Query 1:

SELECT * FROM Users AS U WHERE NOT EXISTS(SELECT 1 FROM Tags AS T WHERE T.user = U.id);

Results:

| ID |
|----|
|  2 |

(Thanks, @dotancohen for putting this into a SQLFiddle)


Transaction log back up Another accidental DBA question. We have a DB instance that host 10 databases. This DB server doesn't have any maintenance plan set up. I want to set up a decent maintenance plan that will do a full database back up everyday and do an hourly transaction log back up. I tried clicking on new maintenance plan button from SSMS and it looks like the SQL server agent is not configured. I plan to enable this by running the following

 sp_configure 'show advanced options', 1;
 GO
 RECONFIGURE;
 GO
 sp_configure 'Agent XPs', 1;
 GO
 RECONFIGURE
 GO

Then I plan to use the Maintainence plan wizard and set up my plan. Am I following the right route>? Do I have to watch for something while setting up the plan? Your advise is appreciated. Thanks

You can use Ola Hallengren's - SQL Server Maintenance Solution

This is much flexible than maintenance plans.

We have 300+ servers and we are using it for all our backups. It just works flawless.

To get you started :

Automate and Improve Your Database Maintenance Using Ola Hallengren's Free Script


MS-Access: How do I go about building a database with inconsistent data sources? I'm working on building a database to aggregate various data sources and feed a set of Tableau dashboards.

The data is all employee data, the problem is, I only have one file of seven with a unique Employee ID, and the format of Employee_Name is inconsistent across about half the files, so I can't join on Employee_Name (capitalization, commas, and initials are all inconsistent). Do I need to write macros to create consistency in Employee_Name so I can write the lookups to create the proper primary/foreign key relationships, or is there a more succinct way to do this?

My first choice is to go back to the client, and ask for new data exports with a common Employee_ID field across all of them, but I don't know if or when that's feasible.

I should also note, I already wrote a bunch of code in R to solve this problem and perform the joins, but the client needs a solution that doesn't rely on me running an R script every time they want to update their data.

I'm also all ears if there's a better solution with SQLite or something like that.

You are building a basic BI/DW environment. You need to get:

Cleaner data, as it is generally more efficient to fix the problem upstream.
A real database. I would not use Access for anything important like that. It gets corrupt and can only grow to a certain size. Ask the client if they can host a DB to hold this data or can spin something up in the cloud.
Use some form of tool like ETL to extract and load the data into your database.

If you want to go really cheap, Microsoft's Tableau competitor (Power BI) has basic ETL functionality built into it, can source directly from flat files, can call R scripts for clean-up before presenting data, and can store a limited amount of data internally.


Directly acessing the NULL bitmap of a row in SQL Server I was reading the question Checking a wide table for nulls and I'm wondering if it's possible to somehow directly access the NULL bitmap of a row to fast check if a row contains NULL values. Would that be a reliable technique?

(From my reading, there might be issues with the newly introduced SPARSE columns since SQL Server 2008, but can't tell for sure.)

No, there isn't. Besides, this is a very very very misguided effort. Since you've already loaded the page in memory, you have already paid the price for any row and any column on that page. This is a given for any row oriented storage. The price for fetching the page overwhelms the price of looking up values in the page.

If you want to optimize for retrieving the value (including whether it is NULL) for only a specific column then what you are asking for is columnar oriented storage, which is an entirely different game.


update the timestamp on column when data is filled in another I am new to Postgresql and SQL in general. I have a table that looks like this

id (primary key)|temperature|temperature_timestamp|humidity|humidity_timestamp|...|parameter|parameter_timestamp

I want the timestamp of a particular "parameter"_timestamp to be automatically updated when I do an 'UPDATE' on a particular parameter.

Eg: when I update the temperature in a particular row the corresponding temperature_timestamp on that row should update

I am following this link currently https://www.revsys.com/tidbits/automatically-updating-a-timestamp-column-in-postgresql/ and am able to update one column how do I extend this to my entire table ?

MY Trigger funtion looks like this :

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.temp_lastupdated = now();
RETURN NEW;
END;
$$ language 'plpgsql';

And my trigger looks like this

CREATE TRIGGER update_timestamp
BEFORE UPDATE
ON sensor_data
FOR EACH ROW EXECUTE PROCEDURE  update_timestamp();

For my preference, a trigger is the wrong tool for the job here. Trying to outguess the application in terms of whether it really meant to update a certain value or not is a losing game. Let the business logic do what it is told and if it updates value "a", then you as the administrator require that it also update "a_timestamp" part and parcel of the same transaction.

By way of example: if you're limited in your ability to modify the app code but you can require a json payload, you can use a model like the following (full dbfiddle here).

Given a table of the form...

create table foo (
    id serial primary key,
    a int,
    b int,
    c int,
    a_tstz timestamptz not null default now(),
    b_tstz timestamptz not null default now(),
    c_tstz timestamptz not null default now()
);
insert into foo (a,b,c)
values
(1,2,3),
(4,5,6);

...and a proc of the form...

create procedure bar (payload jsonb)
language sql
as $$
    with j as (
       select payload as p
    ), cte as (
        select
            (p->>'id')::int as id,
            case when p ? 'a' then (p->>'a')::int else f.a end as a,
            case when p ? 'b' then (p->>'b')::int else f.b end as b,
            case when p ? 'c' then (p->>'c')::int else f.c end as c,
            case when p ? 'a' then now()::timestamptz else f.a_tstz end as a_tstz,
            case when p ? 'b' then now()::timestamptz else f.b_tstz end as b_tstz,
            case when p ? 'c' then now()::timestamptz else f.c_tstz end as c_tstz
        from foo f
        join j on (j.p->>'id')::int = f.id
    )
    update foo as f set
        a = c.a,
        b = c.b,
        c = c.c,
        a_tstz = c.a_tstz,
        b_tstz = c.b_tstz,
        c_tstz = c.c_tstz
    from cte as c
    where c.id = f.id;
$$

...you can call bar('{"id":1,"a":1,"c":null}'::jsonb);. This will demonstrate to you that attributes which are specified in the json payload are updated alongside their paired timestamps while unspecified attributes are not updated. This is true even if specified attributes

Are updated to the same value as they were before
Are moved to an unknown NULL state

Updates that occur outside usage of the procedure with a valid jsonb payload will still require that you update the paired timestamp "manually". The proc demonstrates only that you can infer which timestamps need to be updated. It's worth noting that this approach as-written here likely has a bunch of edge case bugs you'd want to QA for. Off the top of my head...

malformed payload - no id supplied, a supplied multiple times, invalid data supplied, etc...
multiple payload - I only tested a single valid payload. If you supply multiple payloads at the same time what do you want to happen.
upsert logic - the proc as given only updates values. How will you detect no update has happened and insert as needed?

Possibly also worth noting is that requiring yourself to update the data/timestamp pairs via the procedural code allows you to do out-of-band updates to data in the table that don't futz the timestamp without disabling the trigger. While you may say now "I always want the timestamp updated.", in my experience it's a questionable choice to paint yourself into a corner like that. To put it another way: "It's not a bug, it's a feature."


what is the most simple and easiest way to backup and restore on oracle 11g? Im new on oracle 11g i come from SQL Server administration and the process to
 back up and restore its very easy and intuitive.

I was searching how to make backup and restore on oracle 11g in many cases devolpers talk about RMAN but i want to know if there is a more simple and easy solution to restore all elements of my DW
I dont need to make a programmed back up at the moment
mainly i want a programtically solution to operate in a windows SO

Shut down the database instance.
Copy all files to backup location.
Start up the database instance.


Handle no more connections or out of shared memory I have a golang program that does computation on data in multiple threads at once, all pulling their data from Postgres. The number of threads depends on a previous result. There can therefore be hundreds of threads trying to pull data from Postgres at the same time.

The golang sql library allows one to specify a connection limit, which prevents postgres from running out of shared memory or free connections.

If I hardcode the maximum number of connections I will run out of connections when something else is connected. On the other hand if I hardcode a too low number of allowed connections from the golang program performance will be unnecessary limited.

What would be the best way to allow the go program to use as many as possible connections, without running into the limits. I imagine this number to be a variable one depending on the number of other services that are connected to the database at that time.

I am thinking of running PgBouncer between the database and the golang program, hopefully accepting all the connections from the golang program, allowing as many as possible through, but blocking the rest until connections become free. I am however unsure if PgBouncer does this, but I will be testing this next.

Is there perhaps another method of having a connections pool that will block connections when no real connections are free? Blocking, not refusing, as refusing a connection will mean I have to add retry logic to my golang program.

The number of threads depends on a previous result.

Could you rewrite this to have fewer queries returning more rows each?  If you launch one thread per row to be returned, you are probably introducing more overhead than you are removing.

  If I hardcode the maximum number of connections I will run out of connections when something else is connected.

Unless that "something else" is another golang program doing much the same as the first one, or something equivalent, then you can make max_connections only be slightly larger than the max pool size in golang, and you shouldn't have a problem.  It does require you to have a fairly good idea of what the other things are.

  On the other hand if I hardcode a too low number of allowed connections from the golang program performance will be unnecessary limited.

Have you verified this?  Having 300 connections fighting over 16 CPUs and 4 hard drives is not going to enhance productivity.  There is only so much hardware, and now you have to unproductively fight over spinlocks (for example) as well as do the real work.

  I am thinking of running PgBouncer between the database and the golang program, hopefully accepting all the connections from the golang program, allowing as many as possible through, but blocking the rest until connections become free.

It can do this, but why would it be better than having golang itself do it?  It could make sense if you can push your "other connections" through pgbouncer, but can't push them through golang.  Then you can have one pooler that sees everything.  On the other hand, it introduce another layer of latency on every network round trip.  Plus another layer of complexity.


What's the correct syntax for this Postgres query in MySQL? I have a  query which works well in our PostgreSQL db, but we now need to run our app on a MySQL DB as well.

The query takes an array of IDs and positions, and updates each record with given ID with a new value for the respective position.

  UPDATE items
    SET position = i_vals.position
    FROM (VALUES (1,12), (2, 13), (3,11)) AS i_vals(id, position)
    WHERE i_vals.id = items.id;

How can I rewrite this query to be MySQL and SQLite3 friendly? As a bonus, I'd like to be able to also run in PostgreSQL with the same query, but appreciate that might not be possible

It can be

UPDATE items, ( SELECT 1 id, 12 position UNION ALL
                SELECT 2   , 13          UNION ALL
                SELECT 3   , 11                    ) i_vals
SET items.position = i_vals.position
WHERE i_vals.id = items.id;

or:

UPDATE items
       JOIN   ( SELECT 1 id, 12 position UNION ALL
                SELECT 2   , 13          UNION ALL
                SELECT 3   , 11                    ) i_vals
       ON  i_vals.id = items.id;
SET items.position = i_vals.position ;


Most efficient way to insert data into archiving table without influencing production? For example lets say we have table A:

create table if not exists T(
column1 int,
column2 varchar,
column3 date
);

and archiving table TArchive:

create table if not exists TArchive(
column1 int,
column2 varchar,
column3 date
);

What would be the best approach to inserting data older than x date into TArchive without locking down table T in production? Under the assumption that table T has a big amount of rows.

I've been researching this for hours. In SQL Server you have different approaches like: https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows-from-a-really-big-table/
But in PostgreSQL I can barely find anything.

Should you just directly take the data from the T table and import it into TArchive?

Should you import your data into a temp table first, then import that into the archive table? And if so, why would this approach be better when you're doing 2x the inserts for the same data.

How many functions should you make? One function to rule them all? Or a function for archiving and another for deleting the old data?

Are there any other approaches?

You probably cannot find any articles on the topic because it is so simple:

WITH old_rows AS (
   DELETE FROM t
   WHERE ...
   RETURNING *
)
INSERT INTO tarchive
SELECT * FROM old_rows;

This won't lock table t very much — concurrent SELECTs and data modifications won't be affected (unless they are trying to modify one of the deleted rows).

Your main problems are:

This statement may take a long time, holding locks and blocking autovacuum as it runs.
Afterwards, the table will not be smaller, but emptier. This hurts sequential scans and wastes cache space.

Particularly the second problem hurts, and the remedies all require making the table unavailable for a time:

VACUUM (FULL) will reorganize the table, but blocks any access to the table while it runs.
Replacing the table with a different one will block data modifications for a while:

BEGIN;
LOCK TABLE bloated IN SHARE ROW EXCLUSIVE MODE;
CREATE TABLE bloated_copy (LIKE bloated INCLUDING ALL);
INSERT INTO bloated_copy SELECT * FROM bloated;
DROP TABLE bloated;
ALTER TABLE bloated_copy RENAME TO bloated;
COMMIT;

Because of all that, it is a good idea to plan how you want to get rid of old data right when you design your system, but in my experience this is almost always forgotten. The least painful way to get rid of old data is to partition the tables by time.


SQLAgent executing command on Mirror database I have a mirrored database DB1, ServerA as primary and ServerB is mirror.

I have a job running on ServerB  (mirror), configured to run against DB1 database.

I was expecting the job to fail but the job connects to ServerA, completes successfully.
I am not able to reproduce the scenario on a different mirrored instance.

I am curious to know when this happens (SQL Agent connects to a different Server if the database is in mirror/restoring mode).

Note: If I re-configure the job to run against master database and access DB1 database using db1.schema.table it fails!

The easiest way to prevent the job from running on the mirror (remotely executes on the principal) is add as a first step a USE statement for YourDBName  e.g.

The Job Step will Fail on the Mirror and the Job History Log Entry will resemble the following:

The database "YourDBName" cannot be opened. It is acting as a mirror database. [SQLSTATE 42000] (Error 954).  The step failed.

I am using SQL server 2012 SP4 Build No 11.0.7469.6

I understand that initially the database jobs will run on the Principal Only, however after a Mirror Failover they also appear to execute on the Mirror also, but actually will be remotely executing on the Principal, even though this does not appear to make much sense.


A way to reference other DB without hardcoding its name My customer uses pair of databases (SQL Server) where one references the other. Let's say DataDb and ConfigDb. ConfigDb contains hundreds of SPs that references DataDb like [DataDb].[dbo].[the_object].

This pair of databases is deployed to their customers together with some applications.

For development and testing I need to have locally pairs for several customers at once and run the applications against the right pair just by changing the connection string.

When I restore the customer's databases I use suffix of the customer, like DataDb_Cust1, ConfigDb_Cust1. The problem is that stored procs from ConfigDb have hardcoded the name of the referenced database (there's always [DataDb].[dbo].[the_object] which I need to change to [DataDb_Cust1].[dbo].[the_object]).

I could go through all the object (SPs, functions, views) and rename the referenced DB name. I already have some scripts to automate that. But is there a better way (e.g. just use some alias defined on SQL server level or something like that) so that the referenced DB name does not have to be hardcoded in SPs?

I looked at synonyms, but it turns out they are not supported by Microsoft Entity Framework - ORM used by the application. I'd need a solution that would work with EF6. However I can verify it by myself. Any other solution?

The size of DB varies, but typically the DataDb is between 1 and 8GB, the ConfigDb is quite small. No Enterprise features, but it uses SQLCLR assemblies, for some customers even linked servers to access other DBs in distributed transactions (using MSDTC) but it is rare case. I have now 6 sets and expect at most 20 in near future.

The DataDb uses FILESTREAM, which is not supported by LocalDb.

If the databases are under 10 GB, then it might be easier to use SQL Server Express Edition and create a separate Instance for each customer. This would allow for full separation between the paired Databases (a more accurate testing environment) and means that you wouldn't need to change any of the DB names. If the app and SQL Server are on the same server for dev and testing, then you might get away with using SQL Server Express LocalDB (a.k.a. just "LocalDB") which is really quick and easy to create new Instances and to start and stop them. But LocalDB does not allow for TCP/IP connections, or any remote connections; it is local only.

Even if you need Standard or Enterprise Edition features, it might still be easier to create an Instance per customer rather than modifying objects to reference a different Database name, especially since any modifications introduces the risk of changing some incorrectly and then breaking something that you aren't even working on.

And, if you have a standard naming convention, such as DEV_{customer_code}, then it would be fairly easy to write a command-shell script (.cmd file) to start and stop the Instances simply by passing in the {customer_code}, and then you could create desktop short-cuts for those and/or call the script via automated testing, etc.

Don't forget, SQL Server 2016 Developer Edition is now free. You don't need the Enterprise features, but you can't beat that price :-).


How to make a replica slave member to be primary member? I have setup a shard cluster but there is some errors on my config server replica. Below is my configure server replica status:

cnf-serv:SECONDARY> rs.status()
{
    "set" : "cnf-serv",
    "date" : ISODate("2017-08-21T01:28:00.926Z"),
    "myState" : 2,
    "term" : NumberLong(1),
    "configsvr" : true,
    "heartbeatIntervalMillis" : NumberLong(2000),
    "optimes" : {
        "lastCommittedOpTime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "appliedOpTime" : {
            "ts" : Timestamp(1502320369, 1),
            "t" : NumberLong(1)
        },
        "durableOpTime" : {
            "ts" : Timestamp(1502320369, 1),
            "t" : NumberLong(1)
        }
    },
    "members" : [
        {
            "_id" : 0,
            "name" : "172.19.0.13:27017",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                "ts" : Timestamp(0, 0),
                "t" : NumberLong(-1)
            },
            "optimeDurable" : {
                "ts" : Timestamp(0, 0),
                "t" : NumberLong(-1)
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "optimeDurableDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2017-08-21T01:28:00.831Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : NumberLong(0),
            "lastHeartbeatMessage" : "Connection refused",
            "configVersion" : -1
        },
        {
            "_id" : 1,
            "name" : "172.19.0.4:27017",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : {
                "ts" : Timestamp(0, 0),
                "t" : NumberLong(-1)
            },
            "optimeDurable" : {
                "ts" : Timestamp(0, 0),
                "t" : NumberLong(-1)
            },
            "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
            "optimeDurableDate" : ISODate("1970-01-01T00:00:00Z"),
            "lastHeartbeat" : ISODate("2017-08-21T01:28:00.831Z"),
            "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
            "pingMs" : NumberLong(0),
            "lastHeartbeatMessage" : "Connection refused",
            "configVersion" : -1
        },
        {
            "_id" : 2,
            "name" : "172.19.0.7:27017",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 119601,
            "optime" : {
                "ts" : Timestamp(1502320369, 1),
                "t" : NumberLong(1)
            },
            "optimeDate" : ISODate("2017-08-09T23:12:49Z"),
            "configVersion" : 185982,
            "self" : true
        }
    ],
    "ok" : 1
}

I can see from above command two replica members are not reachable and the only reachable member is a secondary. So I need to make the secondary member to be primary in order to make the config replica works. I tried below command in secondary but not works:

cnf-serv:SECONDARY> rs.slaveOk()
cnf-serv:SECONDARY> var cfg = rs.config();
cnf-serv:SECONDARY> cfg.members[0].priority=0.5
0.5
cnf-serv:SECONDARY> cfg.members[1].priority=0.5
0.5
cnf-serv:SECONDARY> cfg.members[2].priority=1
1
cnf-serv:SECONDARY> rs.reconfig(cfg, {force: true})
{ "ok" : 1 }

after making this change the secondary is still secondary. It didn't become the primary. How can I fix this replica set issue?

Your problem is that one node of three cannot be "majority". If you remove those two nodes from setup, that one can become primary. Altho better solution is to get those two other nodes to response.

That error message tells that this node (where you gave rs.status() -command) cannot connect to other two nodes. I guess that there is a firewall between and there is no hole for port 27017.

As always, the best source for the problem solving is mongodb.log file, where program tell you what is wrong with setup.


Redundant mirroring of two mongodb servers I have built a laravel web application for a client of mine which runs on a dedicated server.

The system is a booking system, and the client needs access to this at any time during the work day. So he came to me with "What happends when I loose my internet connection?".

The solution I am going to implement for my client is that I will set up a Raspberry Pi that will run the same web application locally at their shop. So if the loose their internet connection, they can access the web application served from the Raspberry instead.

However, what I now need, is that the Raspberry at all times when it has internet connection, syncs up with the latest information about booked appointments from the live server. And when the internet connection goes down and they use the local web application to book new appointments, then I need the raspberry to tell the live application about those new hours that are booked when the internet connection comes back on.

I have been looking at replication-sets, but it looks like I need a third server to be a judge here for the system to work in such a way that the slave gets writing capabilities to its mongdb server.

Can anyone help me out with any ideas here?

Thanks.

You'll need to find a different approach as there are a few major assumptions that won't work.

  The solution I am going to implement for my client is that I will set up a Raspberry Pi that will run the same web application locally at their shop

Raspberry Pi 1/2 (with 32-bit ARM CPU) is not an officially supported platform for MongoDB, nor is it likely to be. The MMAP storage engine (which is the only 32-bit storage engine available for MongoDB) also has a limitation of ~2GB of data on 32-bit deployments as per the underlying MMAP API. There are some community builds which might be passable for development use, but I wouldn't recommend for client/production work.

There is progress toward experimental 64-bit ARM support in the MongoDB 3.3 development branch, so support for Raspberry Pi 3 or newer is an eventual possibility. If you want to run a full-featured web application with minimal compatibility hassle, a small form factor 64-bit x86 computer is a better bet for current deployments.

  So basically, I need the Pi to be a slave, which becomes master when no inet, and writes to the master when the inet is back up

This isn't a supported replication model as at MongoDB 3.2; a replica set can only have a single primary which accepts writes. An offline/disconnected secondary would be read-only.

  "What happens when I lose my internet connection?".

Some other approaches to consider:

Set up a backup internet connection. This will minimise complexity since you don't have to worry about conflicting versions of data or applications.
Look into a different local storage solution for offline writes which is also more suited for embedded / low power devices (for example, SQLite). You'll have to work out how to resolve data editing conflicts between local & web data, though.


Is it possible to create a function for transactions that use more than one database? As an example, below is a transaction. Would it be possible to write a function defining each database/table, so that in the future I could simply plug in the fields and corresponding values and have them go to the correct database as part of that single call?

BEGIN;

  INSERT INTO ENTITY.entity (`first_name`, `last_name`, `create_time`, `update_time`, `hashID`)
           VALUES ('Lola', 'High', DEFAULT, DEFAULT, DEFAULT);
  INSERT INTO NUMID.numID_history (`id_status`, `id`, `criminal_status`, `charge`, `plea`, `sentence`, `create_time`, `update_time`, `hashID`)
           VALUES (000, 412056888, 0, NULL, NULL, NULL, DEFAULT, DEFAULT, LAST_INSERT_ID());
  INSERT INTO DOB.dob_state (`day`, `month`, `year`, `state_ID`, `stateID_status`, `create_time`, `update_time`, `hashID`)
           VALUES (1, 1, 1985, 34506028, 0, DEFAULT, DEFAULT, LAST_INSERT_ID());

COMMIT;

This might entail, for example, a function that inserts a value to a field only IF that field exists. For example, if the field "create_time" exists in all database.tables, then the value would be inserted in each of them. On the other hand, if "last_name" exists in only one of them, then it would be inserted only in that database.table.

I want a function to store which databases have which tables and use a set of fields that entails a union of all databases. Then when I insert values to fields, without me dictating which database gets what, the function should do that automatically.

Could I create such a function? How would I store it?

I believe I don't quite get what your question is about. The following function would insert those values, with the same values on fields like create_time, within a transaction, on multiple databases. Is this what you're looking for?

delimiter //
CREATE PROCEDURE yourdb.yourprocname (_first_name varchar(100), _last_name varchar(100), _create_time datetime, _update_time datetime,  _hash_id varchar(32),
  _id_status int, _id int, _criminal_status int, _day int, _month int, _year int, _state_id int, _state_id_status int)
begin
  START TRANSACTION;

  INSERT INTO ENTITY.entity (`first_name`, `last_name`, `create_time`, `update_time`, `hashID`)
    VALUES (_first_name, _last_name, _create_time, _update_time, _hash_id);

  INSERT INTO NUMID.numID_history (`id_status`, `id`, `criminal_status`, `create_time`, `update_time`, `hashID`)
    VALUES (_id_status, _id, _criminal_status, _create_time, _update_time, LAST_INSERT_ID());

  INSERT INTO DOB.dob_state (`day`, `month`, `year`, `state_ID`, `stateID_status`, `create_time`, `update_time`, `hashID`)
    VALUES (_day, _month, _year, _state_id, _state_id_status, _create_time, _update_time, LAST_INSERT_ID());

  COMMIT;
end//
delimiter ;

Example call:

call yourdb.yourprocname ('Lola', 'High', CURRENT_TIMESTAMP, ... etc)


SQL Query to compare (HL7) value at certain position in string based on delimiters (not position) This is complicated by the fact that I don't know what DBMS the backend uses.  I know it's Cache, but I don't seem to be able to do anything but the most basic commands.  I cannot seem to SET or DECLARE variables.

Here is an example:

  MSH|^~\&|unimportant|This is the substring I want^123123123^ABC|unimportant|...

So let's call that specific substring SendingFacility.  What is the simplest, most compatible way, to write something like this:

SELECT * WHERE SendingFacility = 'This is the substring I want'

I tried SELECT WHERE SendingFacility REGEXP '<some regex>' but I get the following error:

[Error Code: 14, SQL State: 37000]  [SQLCODE: <-14>:<A comparison operator is required here>]

If this looks like HL7 that's because it is :)

This works

SELECT *
FROM
       TABLE
WHERE
       $PIECE($PIECE(HL7Message,'|',4),'^',1) = 'This is the substring I want'

or to get Sendingfacilities based on other criteria

SELECT $PIECE($PIECE(HL7Message,'|',4),'^',1) as SendingFacility
FROM TABLE
WHERE <whatever>


Error 18456 error severity 14 state 5 SQL Server 2012 SCCM2012 SP1 We had a primary site for SCCM 2012 SP1 running fine. I then installed CAS for SCCM on another server. The prerequisites required me to change the collation on the existing server. I did this and ran a rebuild database command at the same time. The SCCM database for the site dropped, along with a reporting database and temp reporting database. I had to re-add some users to the instance and marry up with the reattached databases (the ones that had dropped). I am now getting a SQL Server error as follows:

  Error: 18456, Severity: 14, State: 5.
  2013-11-20 16:28:44.73 Logon
  Login failed for user 'DOMAIN\MACHINENAME$'. Reason: Could not find a
  login matching the name provided. [CLIENT: ]

SCCM user auto discovery isn't working as new users in AD are not being written into the database, probably due to this. I have recreated the local machine user and checked NT AUTHORITY\SYSTEM, NT AUTHORITY\NETWORK SERVICE, and DOMAIN\MACHINENAME$ users match between the databases and the server and the SIDs all match. To do this, I used:

select * from sys.database_principals
select * from sys.server_principals

All connections are local and using windows authentication. The owner of the SCCM database is 'sa' and the SID matches. TCP/IP and named pipes are enabled, the ports for connection are static. The 3 users above are set as sysadmin on the server.

Any ideas?

When you say you did a database rebuild command, do you mean you rebuilt the system databases for sql server & changed the default collation?  Doing this means you've effectively created a new database server.  I found these instructions for moving the SCCM db from one server to another.  Looks like they're using a db backup/restore & you've attached the db, but everything else should be the same.  There is a step in the ConfigMgr setup that will fix up the database permissions for you, have you tried that?


Import CSV file into Mysql with Multiple Delimiters/Field Separators I'm trying to import a large csv file into Mysql.  Unfortunately, the data within the file is separated both by spaces and tabs.

As a result, whenever I load the data into my table, I end up with countless empty cells (because Mysql only recognizes one field separator). Modifying the data before importing it is not an option, as I'm working with something like 400 million rows.

Here is an example of the data:

# 1574    1 1 1
$ 1587    6 6 2
$115 1878    8 9 23

(Where the second and third value of every row are separated by a tab)

Any ideas?

If you're on *nix - check out the tools sed, awk, grep and split and related (or even vi). As mentioned perl could do it, so could python or PHP (or C or Java or ...) This looks more like a task for programming tools rather than a database. That's not to say you couldn't do this using PL/SQL or T-SQL or , but sometimes the most suitable tool is not within the database server (and in this case, certainly not within MySQL).


No way to set maximum retrieved rows in pgadmin query tool I'm using version 1.16.1, and I can't find a "max(imum) rows" param to tweak.  Any help would be appreciated.

I don't think there is a global parameter to tweak in pgAdmin 1.16.*

As an alternative you can open tables with:

Tools -> View Data -> View Top 100 rows

This is also available from the context menu of a table in the object browser.
Once the data grid is open, a "Limit Bar" is available, where you can set a maximum for returned rows.

I would consider it a reasonable feature request to make this "Limit" element available in the "View data options" you get when you open a table with a filter (grid icon with funnel). This could also remember the last setting.

A global setting is a bit of a foot gun. People forget and then wonder why they don't get more rows.


Automatic decimal rounding issue The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals regardless of any cast / converts.

For example, let's do a simple division as 1234/1233. A calculator will produce 1,00081103000811. But when I do this on SQL Server, we get the following:

-- Result: rounded at 1.000811000... with trailing zeroes up until the 37 precision
SELECT CAST(CAST(1234 AS DEC(38,34))/CAST(1233 AS DEC(38,34)) AS DEC(38,37))

-- Result: rounded at 1.000811
SELECT CONVERT(DECIMAL(38,32), 1234)/CONVERT(DECIMAL(38,32),1233)

-- Correct result at 1,00081103000811
-- But this requires the zeroes to be put in manually when you don't
-- even know the precision of the end result
SELECT 1234.0/1233.00000000000000

Why does this automatic rounding occur? And what's the best way to calculate insanely long decimal values when you can't be sure how big a number (the int or dec part) will be, since the table can contain various different values?

Thanks!

tl;dr

Don't do calculations in SQL language

Longer

The result scale and precision is well defined here on MSDN. It isn't intuitive, really.
However, in simple terms, precision is lost when the input scales are high because the result scales need to be dropped to 38 with a matching precision drop.

To confirm things

Your extra CAST in the first example simply add zeroes
The truncation happens as per my MSDN link (2nd example)
The 3rd example with constants has implied decimal values that are just enough (5,1) and 18,14).This means the result scale and precision have no truncation (see blow)

More on the 1st and 3rd cases..

The result scale for a division is max(6, s1 + p2 + 1):

First example, this is 77 which is dropped to 38. Precision is forced down similarly, subject to a minimum of 6 (see this)
Third example, this is 24 so precision does not need adjusted

You have some options

calculate in the client code eg .net
use CLR functions to do .net calculations
live with the loss of accuracy
use float and live with 15 significant figures as best

FInally, see this on SO https://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy/424052#424052


Is there any (hidden) built-in function on MS-SQL to unquote object names? Sometimes I store object names (identifiers) in some of our databases, for example in some parameter tables. Because I select records from these tables using the '=' or 'LIKE' comparison operators, I must take care to store these names always with or without brackets.

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = '[TABLE_NAME]';

or

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = 'TABLE_NAME';

However MS-SQL has some functions where you can use object names with or without brackets, for example the OBJECT_ID() function. I've set up a minimal example on dbfiddle.uk.

CREATE TABLE TEST
(
    ID     INT IDENTITY(1,1) PRIMARY KEY,
    OBJECT sysname NOT NULL
);
GO

INSERT INTO TEST VALUES ('[obj1]'),('obj2'),('obj3'),('[obj4]');
GO

Now I can use OBJECT_ID() to check if the table TEST exists in this way:

IF OBJECT_ID('TEST') IS NOT NULL
BEGIN
    SELECT 'TEST EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID    |
| :----------- |
| TEST EXISTS. |

IF OBJECT_ID('[TEST]') IS NOT NULL
BEGIN
    SELECT '[TEST] EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID      |
| :------------- |
| [TEST] EXISTS. |

It doesn't matter if I pass the identifier TEST with or without brackets, parser is smart enough to remove the brackets.

Well, I can simulate this by adding a scalar function that remove brackets from one string:

CREATE FUNCTION UNQUOTENAME(@TXT NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
    BEGIN
        RETURN IIF(LEFT(@TXT, 1) = N'[' AND RIGHT(@TXT, 1) = N']',
                   SUBSTRING(@TXT, 2, LEN(@TXT) -  2),
                   @TXT);
    END;
GO

And then use it in this way:

SELECT dbo.UNQUOTENAME (N'[FIELD]') NAME1, N'FIELD' NAME2;
GO

NAME1 | NAME2
:---- | :----
FIELD | FIELD

SELECT ID, OBJECT
FROM   TEST
WHERE OBJECT LIKE 'obj%';
GO

ID | OBJECT
-: | :-----
 2 | obj2
 3 | obj3

SELECT ID, dbo.UNQUOTENAME(OBJECT)
FROM   TEST
WHERE  dbo.UNQUOTENAME(OBJECT) LIKE 'obj%';
GO

ID | (No column name)
-: | :---------------
 1 | obj1
 2 | obj2
 3 | obj3
 4 | obj4

But my question is:

Is there any hidden built-in function that removes brackets using T-SQL?

dbfiddle here

Sometimes I store objects names in some of our databases
I must take care to store this names always with or without brackets.

An "object name" is technically called an identifier.  In some contexts an identifier will be appear TSQL code surrounded by [ and ] or " and ".  These characters are not part of the identifier, and you should never store them.
Instead store the identifier as a nvarchar(128) (or sysname), and add the delimiters at runtime using the QUOTENAME function.
The inverse of QUOTENAME is PARSENAME, which has the additional ability to navigate multi-part names.
Note that QUOTENAME has an optional second parameter, and if you specify a single quote character for that parameter QUOTENAME does not create a valid delimited identifier expression.  It emits a varchar literal expression.


Strange blocking on readable secondary after reboot We are running an SQL Server 2014 Availability Group with 3 replicas, one synchronous (SQL2 for this matter) and one asynchronous secondary replica. We also configured read-only routing to the synchronous secondary replica.

Last night SQL2 rebooted from automatic windows update installation. The server went back online, SQL Server service started (delayed start) and the database went into recovery. After a while the event viewer showed the database integrity check succeeded and the database was ready for use.

The database showed synchronized state in SQL Management studio. The AG state was healthy, but no queries were getting results from the database.

The queries were blocked by the wait type: HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING.

Sometimes the wait type changed to ‘lck_m_s’ wait type and the blocked by a pid that was a process performing a DB Startup command. I know this has to do with the Fast Recovery option that comes with SQL Server Enterprise, but I don’t understand why a simple select was blocked forever.

The main question is: How can SQL Server show the AG database is healthy but actually it isn’t? Do you recognize this problem?

To fix this, we removed the secondary from the AG and joined the database back again to the AG and now everything is working again.

It sounds like you experienced the behavior that's described in this post on the PFE blog:

AlwaysOn Availability Groups unable to query against readable secondary replica database: Wait Type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING

Essentially, there happened to be a long-running transaction on the primary when the secondary was made readable, and thus queries will be blocked in the secondary until the snapshot-related row versions are available.  I imagine removing and re-adding the database was coincidental with the long-running transaction finally completing.

So this behavior, as described in that blog post, is by design.

However, if there was not a long-running transaction, then this could be a bug.  There is a comment on that blog that indicates others have had this problem:

  I am facing the issue after Secondary SQL Server reboot after OS patching. Do not see any open transactions in primary prior to the reboot. There are two databases in the AG having this issue. And we have been waiting for more than 15 hours but still readable replica is not able to process any select query for those databases.

If you are able to repeat the behavior, it would be good to report it on the feedback site and / or engage Microsoft support if you have a support agreement.


In pgPool-II 3.7.5 configuration pgpool.conf, is recovery_user a role or a system user? I'm having trouble configuing pgPool-II 3.7.5 for online recovery. In pgpool.conf, there is a parameter called recovery_user along with a recovery_password that are somehow used to run recovery_1st_stage_command.

The pgPool docs say of recovery_user:

  Specifies the PostgreSQL user name to perform online recovery.

To me, this indicates very clearly that recovery_user is the name of a PostgreSQL user (role in the database). However, in a previous version of the docs, it also says

  You need to setup ssh so that recovery_user can login from the primary to the standby without being asked for a password.

which clearly indicates that recovery_user should be a system user. Granted this is documentation from pretty different versions but it's hard for me to believe that the implementation of this feature changed in that way.

Which is it, is recovery_user a database role or a server user?

Bonus: Please explain how pgPool-II orchestrates running recovery_1st_stage_command

I don't use pgpool, but it must be the OS user who runs the database server.  It cannot be a user within the PostgreSQL database, as a failed database cannot accept connections until after it has been recovered.


Database design for articles with localized prices I'm designing a database for a shop for having some fun using Postgresql (but the database should not be really important here, right?).
I'd like to design for each article in the catalog multiple prices (e.g. divided by country). I was thinking about the best solution from the point of view of performance.
I see three options.

Option 1

The classical approach with junction tables, i.e. three tables:

article
price
article_price

Where price is like

CREATE TABLE price (
  id bigserial PRIMARY KEY,
  amount numeric(6,2) NOT NULL
  country  money_country NOT NULL,
  currency money_currency NOT NULL
)

and money_country and money_currency are two enums. One could say that currency and country should be a table. I believe, but maybe I'm wrong, that I can use some constraint to validate money_country and money_currency pairs, e.g. defining an SQL function instead of having additional tables.

Option 2

I define a different approach which will sacrifice some data integrity by using arrays, e.g.:

CREATE TABLE article (
  id bigserial PRIMARY KEY
  prices bigint[]
)

where prices is an array of id of the rows in price to be linked to the row in article.

Option 3

Probably more "wild", I could use types, e.g.

CREATE TYPE my_money AS (
  --something here to define money
)

CREATE TABLE article (
  id bigserial PRIMARY KEY
  prices my_money[]
)

What's the best approach? Am I missing some?

I would have tables for:

Supplier

Product

Inventory

Price

Currency

Sales

Country

I would follow tradional Entity Relationship Diagram and model. The more you normalise the design the more efficient it will be because it will be doing less IO. Sometimes you can take shortcuts which might save you a bit of time. But eventually it'll catch up with you and you'll wish you did it properly the first time.
A product could have many prices. Could be sold in many countries. Some countries could have multiple currencies. Prices change over time.
If you expect there will be more than 1 country/currency/price etc then design it to handle multiple countries/currencies/prices etc from the beggining.
So when you define a price you'd want to be able to define a price for this product, in this country, in this currency for this time frame.
Yes its possible to combine all of these things into a compound key, but experience has taught me you dont want keys to have any business meaning.
"Performance" is relative. RDBMs' are extremely good at crunching transactional data. With good design this model will scale up to corporate levels. Reporting and analytics might need a rethink.


With MySQL 5.6, Is there any way to keep a replication slave X seconds behind_the_master? It'd be really nice for disaster recovery to be able to set this up.  We are doing snapshots, but this seems like it'd be a really nice backup to have.  For example, I'd like to always keep a replicated slave 30 minutes behind our master, so that we could have a sort of live backup all the time.

This is a secondary replicated server, as ypercube points out -- if you only have a single replicated server, running it 30 minutes behind would mean you'd potentially lose 30 minutes of data in the case of the master crashing.

Although I suspect the relevant binlogs for those 30 minutes of binlog events would actually be stored on the slave (and just not processed until it was 30 minutes behind), so I'm thinking that I wouldn't actually, even if this were the only slave.

I'll be doing some testing to confirm this behavior, as it is relevant for some potential use-cases for us.  I'll update this later.

For any version pt-slave-delay  Is what you're looking for.  Note this is a 3rd party tool that continuously runs with stop/start slave, to keep it around the delay you want.

If you have 5.6 or above you can use the native built in slave delay


Missing step in backup (DR) plan Unfortunately, I am assuming like most folks in this role, the title DBA came by default rather than design.

I am trying to manage our farm of 5 SQL Server 2016 servers, each having a warm stand-by for our Disaster Recovery.

We have queried our user base and have come to the decision that some downtown is acceptable as long as we can minimize data loss.  We do not need point in time recoveries, only to ensure that we have the most current data possible based on the users data loss acceptability.

What we have decide for our model is each database on each of the primaries will be backed up and restored on the appropriate stand-by (with the database left in Stand-By/Read Only mode) and then a log shipping job created to ship the log to the standby server every 30 seconds to 1 hour depending on the data loss sensitivity.  Then, on each stand by server, once every 15 minutes to 1 hour, a restore job runs to restore all the transactions shipped from the primary into the stand by database.  Once the transactions have been shipped to the stand by and restored successfully, I no longer care about those transactions.

This works great as I can query the stand by databases and see the updated data as expected.

Where I am lacking information in is, my transaction log is growing larger than my DB file on my primary.  I ASSUMED that the log shipping would mark the log as backed up, but I am apparently wrong.

I am hesitant to create a job that takes full backups as I do not want to break the log shipping chain so here is where I need some advise on how to control my log sizes.

I have queried the internet for answers, but I get about as many different answers as there are results, so I am turning here for advise.

Thanks in advance,
Keith

I am hesitant to create a job that takes full backups as I do not want to break the log shipping chain so here is where I need some advise on how to control my log sizes.

This is a misconception/myth. A fullbackup will never break the log backup chain. Only an adhoc NON COPY_ONLY backup will break the log backup chain.

In full recovery, only a full backup will truncate the log.

  ran a log_reuse_wait_desc and there is nothing holding the log

Check your autogrowth settings. Make sure it is not set to Percent autogrowth and the MB autogrowth is sensible.

You can check my script to check.

Also, you should check : Why Does the Transaction Log Keep Growing or Run Out of Space?


Server default collation change or not change The default collation of our new server is SQL_Latin1_General_CP1_CI_AS. However the collation of the other databases which are used as the source to migrate data into the target databases on this server are Latin1_General_CI_AS.

To avoid getting the error while we load data from database 1 (collation:Latin1_General_CI_AS) to database 2 (collation:SQL_Latin1_General_CP1_CI_AS), I changed the collation of database 2 (the target database) to Latin1_General_CI_AS.

However I'm wondering if it might any side-affect?
What is the reason that the server default collation is SQL_Latin1_General_CP1_CI_AS? Is it any better than other collation, in terms of performance, etc?

Thanks for your help.

I worked out this issue and share it here, in case someone needs it in future.

The default collation setting is determined by the server locale. For most English speaking countries is it : Latin1_General_CI_AS

If the server locale setting has been left as default which is English (United States), the collation at time of MS SQL Management Studio would be set to SQL_Latin1_General_CP1_CI_AS.

You can check the collation for each local in the following url:

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


Distinguishing Records with Related Fields I'm a newer user (obviously) and would greatly appreciate question writing suggestions as well as answer suggestions:
I have a lot of interrelated tables which largely depend on my Products table, which is the master list of a certain product. Much of the information for any given product is determined by traits from the Shipments table. There is a one-to-many relationship between Shipments and Products. Each shipment has a Product Version field, but in addition to having a unique version, any shipment may be a trial shipment.
Which is the better design?
There are two workable designs which come to mind.
1. Current Design
The Product Version field contains values for each possibility. For example, a product of version 7.6 would have the value 7.6, but a 7.6 product which is a trial would be 7.61. To select all 7.6 products, I would use a Like "7.6*" statement. This keeps all of the versions in one table and I have this documented for the next user. The irritating thing is that I have to have a field containing the necessary SQL to query that specific version. This SQL is located in a ProductVersion table.
2. Adding a Field
I could have a True/False field for each shipment which indicated if it was a trial... (I also have to take into account the ease with which new users will be able to use this without me, but as that's a bit subjective, that's not the core question.)
I don't know if this makes a difference but
Part of the fun with my current setup is that only some records in Products have associated Shipments, since for sometime the shipments were not recorded. The shipments are also imported into the database in a different process, sometimes after the associated product.
So:
Is it a better design to add a boolean field for trial products?

I encourage those more knowledgeable than I to edit this answer or question with additional information if it would be beneficial to those reading this in the future.

After experimentation, I've implemented Option 2, the boolean field for trial version.

I think this solution is

better normalized and
more clear to users

when each attribute is broken down into its own field (rather than having a version field which has to describe multiple attributes like product version and whether or not it is trial).

In some way it could be beneficial if I could just add new version numbers like 7.622 then 7.6221 to get more specific, but this can be confusing to decipher and is also somewhat arbitrary. The alternative--breaking the version/trial into multiple fields--also discourages others from creating new potentially duplicate or unnecessary version numbers which make things harder to find/query.

These additional fields, such as the trial boolean, are related to each Product through the one-to-many relationship between Products and Shipments.


Unique code based on category - a sort of auto_increment I have to create a unique code to every user registering. The thing is that the unique code needs to be generated based on which city the user is from.
So the tables I have are:

CREATE TABLE `microreg`.`users` ( `user_id` INT NOT NULL
AUTO_INCREMENT , `user_code` CHAR(10) NOT NULL , PRIMARY KEY (`user_id`),
UNIQUE (`user_code`));

user_id is for unique identification of users in DB.
 user_code is
   the readable code generated for the use of users. They access their
   account with that unique code.

CREATE TABLE `microreg`.`districts` ( `district_id` INT NOT NULL
AUTO_INCREMENT , `district_name` VARCHAR(50) NOT NULL , `district_code`
CHAR(4) NOT NULL , PRIMARY KEY (`district_id`), UNIQUE (`district_code`));`

This contains as about 20K districts with unique code assigned.

say,

Trichy      ITRY
Coimbatore  ICBE
Kolkata     IKOL
Delhi       IDEL
...
New York    UNYK
Washington  UWDC

So, when user registers with Trichy as district, he will be generated with

user_id   : 1
user_code : ITRY000001

second user from same city

user_id   : 2
user_code : ITRY000002

another one from New York

user_id   : 3
user_code : UNYK000001

user_code is unique with respect to district. [User with id 1 and 3 are not same]

So far, I fetched the maximum value of the selected district of the user

SELECT RIGHT(MAX(user_code),6) FROM `users` WHERE user_code LIKE 'ITRY%';

and then increment it by 1 and then add it back with district_code appended to it.

INSERT INTO `microreg`.`users` (`user_id`, `user_code`) VALUES (NULL, 'ITRY000003');

This method works perfectly when there is only one user updating at a time. But this started creating problem when multiple users concurrently started registering.
Since unique is set, two users with same user_code was generated and creates overhead.

Previously, for another application, I created unique tables for every code (like district here) and added the user to that table and then appended back to original table (like users here).
In that case I had only 4 types of code (like district here). But for this app, I have got about 20K districts. I couldn't get a spark for this. (For information, I use PHP with mysqli for this application) Thanks.

Assuming you are using an ACID compliant engine (i.e. InnoDB) the "get max, add one, then insert" method should work if wrapped in a transaction.

I would query the requirement for the user code to be a specific item though given you have a user ID which is presumably an auto-incrementing integer. If there is no need for codes to be sequential within a region (i.e. it is OK to have ITRY000003 and ITRY000042 but nothing in between because users 4 through 41 are in other places) then you could just have the user code be a derived value constructed from [district_code]+PadWithZeros([user_id]). THis menas you don;t have to worry about generating the extra code (which I assume is needed for display purposes mainly?). You may still need to store it if the users could move between districts but you'd like to keep their original code if they do. If it isn't required that the code be static if the user moves districts then you don't need to store it: it can be derived when needed.