Splitting a String and ORDER BY DESC its matching Values From a Query like:

SELECT id
FROM table
ORDER BY
        column = 'A B C D E F G' DESC,
        column = 'A B C D E F' DESC,
        column = 'A B C D E' DESC,
        column = 'A B C D' DESC,
        column = 'A B C' DESC,
        column = 'A B' DESC,
        column = 'A' DESC
GROUP BY id

And the Answer I got was to use

SELECT id
FROM table
ORDER BY
        SUBSTRING_INDEX(column, ' ', 1),
        LENGTH(column) DESC
GROUP BY id

The problem that it seems that I can't define a specific string to it And it orders the full-records only, What I want is something to order the matches of the ORDER BY at top by its highest-matching value as the ladder of defined string.

In all cases, Is there any performance difference between my first Query of ladder column = . . . . DESC,. . . And any other solution? And what is the other solution for such a Query to select element in a specific string parts order.

The table is

[id  -  column       ]
[1   -  A B C D E    ]
[2   -  A B C        ]
[3   -  A B C D E F G]
[4   -  X Y Z        ]
[5   -  A            ]
[6   -  B            ]
[7   -  A B C        ]
[8   -  B A C D      ]
[9   -  A B G C D E F]
[10  -  A B C D E F G H I J K]
[11  -  Z A B C D E]

And I have this string A B C D E F G that I want to get all the rows matching its parts, So I want to order like ORDER BY PARTSOF('A B C D E F G') DESC

to get the result of

[id - column]
[3  - A B C D E F G]
[1  - A B C D E]
[2  - A B C]
[7  - A B C]
[5  - A]
. . . EVERYTHING AFTER IS BY ID . . .
[4  - X Y Z        ]
[6  - B            ]
[8  - B A C D      ]
[9  - A B G C D E F]
[10 - A B C D E F G H I J K]
[11 - Z A B C D E  ]

Juse like this SQLFiddle http://sqlfiddle.com/#!9/9f4c815/2/0

It seems like you want in the result first the substrings of a parameter string ('A B C D E F G'). In this case the LIKE should be reversed - and it only needs the wild character in the end, not on both sides:

 'A B C D E F G' LIKE CONCAT(col, '%')

If this goes to the WHERE clause, it will affect only what results you get of course, not the order. If you want to affect the ordering, (first the rows that match the above, then the rest), then it should be in the ORDER BY:

SELECT id, col
FROM t
ORDER BY
 -- CASE WHEN 'A B C D E F G' LIKE CONCAT(col, '%') THEN 0 ELSE 1 END,
    CASE WHEN 'A B C D E F G' LIKE CONCAT(col, '%') THEN col END DESC,
    id ;

Since MySQL orders nulls first when ascending and last when descending, we can remove the first CASE expression from the ORDER BY (commented above).

Test in fiddle.

You may also want to try this variation that separates the two parts and then UNIONs them. It has an additional (redundant) clause in the first WHERE, to make the index use more efficient for the first part. The second part will have to do a table or index scan anyway:

( SELECT id, col
  FROM t
  WHERE col >= LEFT('A B C D E F G', 1)
    AND col <= 'A B C D E F G'
    AND 'A B C D E F G' LIKE CONCAT(col, '%')
  ORDER BY col DESC
)
UNION ALL
( SELECT id, col
  FROM t
  WHERE NOT ('A B C D E F G' LIKE CONCAT(col, '%'))
  ORDER BY id
) ;


Validation of views even though auto_reval is set to DEFERRED According to http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/r0054951.html?lang=en it should be possible to create stored procedures and views referencing not existing tables if the setting AUTO_REVAL is set to DEFERRED.

On my system however the following commands (creating a view referencing an invalid table) still result in an error:

db2 get database configuration | grep REVAL

  Auto-Revalidation                          (AUTO_REVAL) = DEFERRED

db2 "create view v1 as select * from not_existing_table"

  DB21034E  The command was processed as an SQL statement because it was not a
  valid Command Line Processor command.  During SQL processing it returned:
  SQL0204N  "DB2ADMIN.NOT_EXISTING_TABLE" is an undefined name.  SQLSTATE=42704

DB2 does not create the view, also not as an invalid object.

Am I missing anything? Are there other options I need to verify? I'm using DB2 LUW 10.5 (fixpack 4).

I think the DEFERRED setting only affects CREATE OR REPLACE statements. If you want simple CREATEs to behave in this manner, set AUTO_REVAL to DEFERRED_FORCE.


Top clause slows down query I already received an answer on similar question, but it's not applicable in the case below.

In last time, I just rewrited my query on two distinct, first was fetching first N rows from one table and then it was joined (if and only if it contains sufficient amount of data). But here there is enough of data, but query still be very slow. Here is simplified example of slow query:

DECLARE @Top int = 1000

SELECT TOP (@Top) *
FROM (
    SELECT [t0].[DateTimeUtc] AS [value], [t2].[SystemName], [t2].[Name], [t0].[Id], [t1].[Discriminator], [t1].[ParentActionTemplateId], [t0].[DateTimeUtc], [t1].[Id] AS [Id2]
    FROM [directcrm].[CustomerActions] AS [t0]
    INNER JOIN [directcrm].[ActionTemplates] AS [t1] ON [t1].[Id] = [t0].[ActionTemplateId]
    LEFT OUTER JOIN [directcrm].[ActionTemplates] AS [t2] ON [t2].[Id] = [t1].[ParentActionTemplateId]
    ) AS [t7]
WHERE (([t7].[Discriminator] = @p9) OR ([t7].[Discriminator] = @p10) OR ([t7].[Discriminator] = @p11) OR ([t7].[Discriminator] = @p12)) AND ([t7].[ParentActionTemplateId] IS NOT NULL) AND (([t7].[DateTimeUtc]) <= @p13) AND (([t7].[DateTimeUtc]) > @p14) AND ([t7].[Id] > @p15)
ORDER BY [t7].[Id]

But if we hack optimizer:

DECLARE @Top int = 1000

SELECT TOP (@Top) *
FROM (
    SELECT [t0].[DateTimeUtc] AS [value], [t2].[SystemName], [t2].[Name], [t0].[Id], [t1].[Discriminator], [t1].[ParentActionTemplateId], [t0].[DateTimeUtc], [t1].[Id] AS [Id2]
    FROM [directcrm].[CustomerActions] AS [t0]
    INNER JOIN [directcrm].[ActionTemplates] AS [t1] ON [t1].[Id] = [t0].[ActionTemplateId]
    LEFT OUTER JOIN [directcrm].[ActionTemplates] AS [t2] ON [t2].[Id] = [t1].[ParentActionTemplateId]
    ) AS [t7]
WHERE (([t7].[Discriminator] = @p9) OR ([t7].[Discriminator] = @p10) OR ([t7].[Discriminator] = @p11) OR ([t7].[Discriminator] = @p12)) AND ([t7].[ParentActionTemplateId] IS NOT NULL) AND (([t7].[DateTimeUtc]) <= @p13) AND (([t7].[DateTimeUtc]) > @p14) AND ([t7].[Id] > @p15)
ORDER BY [t7].[Id]

OPTION (OPTIMIZE FOR (@TOP = 100000000)) -- < here

We get an instant answer, because in first case it uses loop join which is slow as hell, in second - hash join.

Of course I rebuilded all statistics and all indices for all tables in database.

I don't know what can I do with this query, because it's just a simple join without any magic. I tried build some indices, rewrite query and so on, but optimizer always chooses the same plan and runs very slow. I'm unable to use hints because of ORM and because they seems to be really hacky. I use them to determine the bottleneck and then fix it by using DDL modifications. It usually works, but today it failed.

When possible you should update actual execution plans to Paste The Plan. Without the XML we have to make guesses about the operators shown in the images. For the rest of this answer I'm going to assume that the clustered key of all of the tables is id.

As far as I can tell the problem isn't with the nested loop join but with the table access method on the [directcrm].[CustomerActions] table. It looks like SQL Server does a clustered index seek in order starting with [t7].[Id] > @p15. However, it's possible that the storage engine will need to seek many rows until it finds enough that match the filter requirements against the [DateTimeUtc] column. If you're on SQL Server 2016 you can check this by looking at "Number of Rows Read" in the actual query plan for the clustered index seek. It's theoretically possible for the clustered index seek on [directcrm].[ActionTemplates] AS [t1] to also be a problem. However, it looks like the filtering against that table only removes 30% of the rows so I doubt that's an issue.

Based on just how the query is written the biggest problem is that SQL Server is not aware of the values of the local variables. As a result the query optimizer makes cardinality estimates based on hardcoded results. For example, whenever you have an unknown expression in TOP the query optimizer uses a hardcoded guess of 100 for that value. I understand that you are constrained by your ORM but you're essentially fighting with the query optimizer.

I have a few ideas on how to improve performance listed below in rough order of preference. It's likely that you won't be able to implement some of them due to ORM restrictions but I thought I should include them all for completeness.

A good test is try is the RECOMPILE hint. That may be enough to get SQL Server to pick a better plan. I understand that you can't use that one but perhaps you could replace some of the variables with hardcoded values? A TOP statement with a variable is going to be really bad because the query plan will never change as you change the value of the variable. The optimizer will always set a row goal of 100 when creating the plan (based on testing I did tonight).

Since you got better performance when not using the clustered index on [directcrm].[CustomerActions] as the driving index you could try to make the clustered seek more expensive or unavailable. One way to make it more expensive is to introduce some doubt about the order of the rows. Right now you order by t0.id which means that plans that use that clustered index can avoid a sort. If you did something like the following that might be enough:

ORDER BY CASE WHEN @sort = 1 THEN [Discriminator] ELSE [Id] END

Just set @sort always equal to 0 so you always get the sort that you want when the query is executed.

Another trick is to add superfluous operators to parts of the WHERE clause to prevent index use. In some cases just adding 0 won't be optimized away and an index seek will be unavailable. You can strategically add things like that to the WHERE clause to encourage the index usage that you want. For example, if you don't want a clustered index seek on [directcrm].[CustomerActions] you can start with this and make it more complicated if that isn't sufficient:

[t7].[Id] + 0 > @p15

You may be able to create a covering index against [directcrm].[ActionTemplates] to encourage that table to be used as the driving table. Of course you could always drop the clustered index on [directcrm].[CustomerActions] as well but I imagine that will have side effects that you can't ignore.

The nuclear option is to use a plan guide. There are many ways to create plan guides. It's possible to force a query to use a certain plan without changing the query's text. For example, if you can get a good query plan in the cache (such as with your row goal trick) then you can use sp_create_plan_guide_from_handle to force a plan guide to the query. Keep in mind that your query text needs to exactly match what is stored in the plan guide. If you're on SQL Server 2016 you could also try freezing a query plan using the Query Store.


Do I need to enable Trace Flag 1117 for equally sized data files? I was reading about fill proportional algorithm in SQL Server and then I recalled TF1117. BOL states:

  When a file in the filegroup meets the autogrow threshold, all files
  in the filegroup grow. This trace flag affects all databases and is
  recommended only if every database is safe to be grow all files in a
  filegroup by the same amount.

What I can't understand is if data files are filling proportionally, won't they auto-grow proportionally either? In that case we can't omit using TF1117.

EDIT

Here is the version of SQL Server

What I can't understand is if data files are filling proportionally, won't they auto-grow proportionally either? In that case we can't omit using TF1117.

The proportional fill algorithm states that if you have multiple files in a file group and all have different free space, SQL Server will fill the space in the files such that it would try to fill more in the file which has larger space as compared to one having lower space. Example if you have 2 files in FG with 10 and 20 GB size respectively, with proportional fill for every 1 extent allocated to 10GB file 2 extent will be allocated to 20 GB file.

While TF 1117 says that if I am enabled and autogrowth happens I would force all files to expand equivalent to amount of data entered. For example if 100 MB of data is entered all files in a file group with TF 1117 enables will increase by 100MB this will not happen with proportional fill as described above.

More about Proportional Fill Algorithm

In most cases adding TF 1117 helps in reducing contention in tempdb but please note once enabled it applies to all the databases running on the system.

PS: If you have SQL Server 2016 and above you do not need to turn on TF 1117 and 1118.


DB out of sync secondary node HAG I have a question about HAG / DB synchronization in MS SQL server.
I have a HAG and in the secondary node I had to remove one DB on Saturday from the HAG. I added it back on Sunday and it's been out of sync since then. This secondary node it's read-only and more like a backup so it's not impacting anything.
I want to know what would be the best approach to put this DB back in sync. Right now the DB it's in the available databases but not synchronizing.
If I join this database to the HAG, it will automatically start seeding from the primary or is it better to restore the last full backup and t-logs and then join to the HAG?
The DB it's quite large. I tried restoring it in another VM and it took approximately two days and a half so I guess that I should stop the full backup for that DB in specific and just restore the last full backup and all t-logs until up to date right?
Thanks!
EDIT: I ended up restoring the full last backup with the t-logs in norecovery in the secondary node and then just joining the DB into the AG. Worked like a charm.
Thanks for the responses!

Imho, it's worth to try the following:
Simulate the same scenario but with a tiny test db. Don't forget to do full/log backups which happened (?) with your big db on primary since Sunday.
It will take ~10-15 mins to reproduce and you will know for sure will it go or not.
Regarding the question:  I believe it will not start auto-seeding because your DB was recovered and readable before you removed it from HAG. Also, if there was a full backup on primary, it also affects the log chain.


Database structures for similar profiles Please bare me if my question is too blunt

I am working on a social network where I need to show similar profiles to users

Every profiles has 40 parameters to match with other profiles.

I want to fetch results in a manner that users with maximum similarity with current logged-in user i.e if user has exact same values to other users then they must be shown at top.

After that users with lesser similarities should be shown i.e 39 similarities

I am planning to use 2 tables

[user][table] ->  id, name, email, password, username etc

[user_values][table] -> It will have 40 columns for all 40 parameters and user_id

Can this be done using single query ?

So we will have 40 values of current login user. Whenever they reach a matches page we should be able to hit a query which will fetch users in order to descending match of columns.

I can change database design/structure if needed.

I have limited to 10 user_values for this example. Replace the two instances of "1" with the user_id of the user that you are matching profiles to.

SELECT
    user_id,
    @score :=
        (user_values.value1 = cur_user_values.value1) +
        (user_values.value2 = cur_user_values.value2) +
        (user_values.value3 = cur_user_values.value3) +
        (user_values.value4 = cur_user_values.value4) +
        (user_values.value5 = cur_user_values.value5) +
        (user_values.value6 = cur_user_values.value6) +
        (user_values.value7 = cur_user_values.value7) +
        (user_values.value8 = cur_user_values.value8) +
        (user_values.value9 = cur_user_values.value9) +
        (user_values.value10 = cur_user_values.value10)
        AS score
FROM
    user_values JOIN (
        SELECT
            value1,
            value2,
            value3,
            value4,
            value5,
            value6,
            value7,
            value8,
            value9,
            value10
        FROM
            user_values
        WHERE
            user_values.user_id = 1
    ) cur_user_values
WHERE
    user_id != 1
ORDER BY
    score DESC;

This results in a response like so (I was testing with only 5 users):

In my case, score will be a number between 0 and 10. In your case it will be a number between 0 and 40.


Long running SELECT query fails as deadlock victim to a DELETE on SQL SERVER - Why, and how to avoid this? Firstly 1 thread selecting a huge amount of data from a table.

Then, before the SELECT query is not yet finished, another thread deletes data from that table.

It causes SELECT query to failed with error errorCode=1205 sqlState=40001 and message that deadlock happened.

I thought that second transaction must wait until first is finished, or first transaction must return data without the changes that were made by the second transaction. How can I avoid this?

I can't change the order of transactions or control them in some way (there is many users of app, and they can do whatever they want in any order at any time). Why does it happen?

There is how queries looks like (i obfuscated them a little):

SELECT distinct P.pID, (LastNAME+' '+FirstName+' '+SurName) as NAME, 'person' as Type, CA.OrgName, PC.personemail as Email
FROM Persons P
INNER JOIN WORK PW ON (P.mainwork = PW.pwId)
INNER JOIN CONTACT PC ON (PC.pID = P.pID)
LEFT OUTER JOIN ORG CA ON (CA.orgId = PW.orgId)
WHERE (P.Status = ?)

and

DELETE FROM persons WHERE id = 1234

I thought that second transaction must wait until first is finished

Basically, this is not true. Your (or anyone's) database wouldn't be very efficient if only one person could do something with it at a time.

  Why does it happen?

Simply:

A SELECT query is taking shared locks on any rows it's looking at, so although it's not going to conflict with another SELECT query, it definitely will conflict with exclusive locks from a DELETE. As the SELECT query is not updating anything, it's usually going to be the one to be the deadlock victim, as it'll likely be the least expensive to rollback.

Why it happens specifically in the case you're talking about is not possible to say without further information. I'll add some links to deadlock detection at the end but I suspect this isn't the main purpose of the question so...

What to do?

It's difficult to know what the best approach is going to be for you (without a lot more information). It is possible that you should have a look at your isolation levels.

You could use READ_COMMITTED_SNAPSHOT, but this could result in further overheads, or use READ_UNCOMMITED/use a NOLOCK hint - I'm not a fan of those options usually as the data returned might not be actually what you want, but they may be suitable in your case. It's hard to say exactly what would be best, as it'll depend on a lot of different variables about your system and it's use.

Best thing you can do in that regard though is read up about isolation levels, these are some similar posts/sources that will start you off and go into things in more detail.

MSDN - How can a select statement result in being chosen as deadlock victim?, SQL Server central ,  MSDN - Understanding Isolation Levels

But...

Don't rush to make a change just so you can get your queries to finish. A more appropriate avenue to explore may be your queries themselves.

Why is the SELECT statement taking so long to run? Why are things being deleted from the table so frequently? Maybe some simple query tuning will get them all to complete quicker and reduce the chance of a deadlock. Perhaps you need to do some general performance tuning on your database server (I won't go into detail on this here though as it's getting way off topic).

You've got to make sure your queries are fit for business purposes, rather than queries just 'completing at all costs', databases are a business tool first a foremost and need to work around that (however much we might not like that)!

Just going ahead and changing the isolation level, or even just using NOLOCK, can have big impacts on your database and also what exact results queries return - which could have business impacts.

Also...

To aid in deadlock detection, have a look at this post on StackOverflow and check out the Deadlock Information Tools  on the MSDN site which will explain how to use trace flags to provide more information about your deadlocks.

Finally...

More specific advice can be offered if you put the SQL Server version in your post, and some more details about what exactly the SELECT query is (to see if there's anything that may be causing it to take so long and be open for being locked).


Want to order on two columns but not the usual way I have a table as below

ID  userID  Date
1   2273    22/08/2011
2   2274    24/08/2011
3   2275    26/08/2011
4   2273    26/08/2011
5   2273    26/08/2011
6   2271    26/08/2011

And want result as below.  Need mysql query help to acheive this. Basically i want to order by date but same user must all be together. And i am trying to achieve this in single query.

ID  userID  Date
1   2273    22/08/2011
4   2273    26/08/2011
5   2273    26/08/2011
2   2274    24/08/2011
3   2275    26/08/2011
6   2271    26/08/2011

I tries select * from mytable order by userId,Date and will result in following

ID  userID  Date
6   2271    26/08/2011
1   2273    22/08/2011
4   2273    26/08/2011
5   2273    26/08/2011
2   2274    24/08/2011
3   2275    26/08/2011

Which is not i want i want user with date ascending should come first and same user records in table should follow his first record...

SELECT *
FROM Mytable
ORDER BY
   userID, Date

I assume Date is really a date/time type and not varchar...

Edit, after clarification:

Untested

SELECT
    M.*
FROM
    ( --one row for each user
    SELECT MIN(Date) AS FirstUserDate, userID
    FROM MyTable
    GROUP BY userID
    ) foo
    JOIN
    MyTable M ON foo.userID = M.userID
ORDER BY
    foo.FirstUserDate, M.userID, M.Date;


Log Shipping - Secondary Database(s) - Which recovery model? In Log shipping configuration - the primary database must be either in Full or Bulk-Logged recovery model. However, is there any requirement of recovery model of the secondary database?

Example: Assuming both primary and secondary databases are in full recovery model, if there is a failover and the secondary becomes the new primary, we could change the db recovery model.

Also refer to: https://www.microsoftpressstore.com/articles/article.aspx?p=2832586&seqNum=3 and search for "Log shipped databases have to use the full recovery model." - is this correct?

The recovery model for the secondary will be the same as the primary database, because you're backing up the primary, restoring it on the secondary node using NORECOVERY/STANDBY, and then restoring subsequent log files to it.

In a log shipping configuration, you cannot make changes to the secondary database. If you could, then the databases would be out of sync, defeating the purpose of log shipping as a DR solution.

Edit: The Microsoft documentation is fairly comprehensive regarding the basics of log shipping and the requirements for it.


Error #1093 you-cant-specify-target-table-table-name-for-update-in-from-clause I'm trying the following sql code:

UPDATE ticketsorders
SET amount = $val
WHERE id = $ticketsorder->id
  AND ((SELECT COALESCE(SUM(amount), 0)
        FROM ticketsorders
        WHERE tickettype_id = $tickettype->id) + $val )
      <= (SELECT maxtickets
          from tickettypes
          where id = $tickettype->id)
  AND ((SELECT COALESCE(SUM(amount), 0)
        FROM ticketsorders
        WHERE event_id = $eventid) + $val )
      <= (SELECT maxtickets from events where id = $eventid)

$val = the number of tickets
there is a maximum number defined in maxtickets for an event and for a tickettype thats what the 2 ANDS are for. I have a similair insert statement which works.

But I keep getting:

  #1093 - You can't specify target table 'ticketorders' for update in FROM clause.

I have placed alot of as x as y etc to try and make it work like in examples https://stackoverflow.com/questions/8333376/you-cant-specify-target-table-table-name-for-update-in-from-clause

The wheres are there to make sure the update only succeeds when the new sum is below a set maxtickets on both event and tickettype.

Can anyone enlighten me?

I do not like the method provided in the linked question, due to the way UPDATE is processed in MySQL.

Especially in your case, where you want to allow (or restrict) the update based on values from several rows of the table (and not just the row to be updated), I suggest you use 2 statements, one to get the max and sum values from the tickettypes, events and ticketsorders tables and then update accordingly or use JOIN if you want to do this in one statement. The CROSS JOIN are not a problem here as the three first subqueries produce one row each:

UPDATE
    ( SELECT maxtickets
      FROM tickettypes
      WHERE id = $tickettype->id
    ) AS tt
  CROSS JOIN
    ( SELECT maxtickets
      FROM events
      WHERE id = $eventid
    ) AS ev
  CROSS JOIN
    ( SELECT COALESCE(SUM(amount), 0) AS sum_amount
      FROM ticketsorders
      WHERE tickettype_id = $tickettype->id
    ) AS ts
  JOIN
    ticketsorders AS t
      ON  ts.sum_amount +$val <= tt.maxtickets
      AND ts.sum_amount +$val <= ev.maxtickets
SET
    t.amount = $val
WHERE
    t.id = $ticketsorder->id ;


Create multi-key JSON object with integers or floats as values I have a table of text and double precision type. From it, I want to build a JSON object that looks like this: {"a": 1.0, "b": 1.2, "c": 3.4} (edit: to clarify, the keys are the text column in my table, and the values are the double precision column). Note that there are no quotes around the values because they are floats, not strings, which the JSON standard supports.

Using Postgres 9.5. There's the json_build_object function that takes two parallel text arrays and builds a JSON object with text keys and text values... also works with NULL values, turning them into null (no quotes) in JSON. So I'd get {"a": "1.0", "b": "1.2", "c": "3.4"}. But what if I want integer or float values? It won't take any numeric type arrays.

I got around it for now by making my own function to handle this in Python! Is there a way to do this without defining a new function?

CREATE OR REPLACE FUNCTION json_build_object_float_values(keys TEXT[], vals DOUBLE PRECISION[]) RETURNS JSONB AS
$$
    assert len(keys) == len(vals)
    import json
    d = {}
    for i in range(len(keys)):
        d[keys[i]] = float(vals[i])
    return json.dumps(d)
$$
LANGUAGE 'plpython3u' IMMUTABLE;

(note if you use this, it returns JSONB, but you could change it to JSON)

Edit, here's test input and expected output:

CREATE TABLE test (str text, num double precision);
INSERT INTO test(str, num) VALUES ('a', 1.0);
INSERT INTO test(str, num) VALUES ('b', 2.3);
-- Here's my solution using my custom function...
SELECT json_build_object_float_values(array_agg(str), array_agg(num)) FROM test;
-- expected output:
--  json_build_object_float_values
-- --------------------------------
--  {"a": 1.0, "b": 2.3}
-- (1 row)

Simpler one:

SELECT json_build_object_float_values(ARRAY['the', 'quick'], ARRAY[1, 3]);

 json_build_object_float_values
--------------------------------
 {"the": 1.0, "quick": 3.0}
(1 row)

Update

According to your sample data and desired result you can get it using json_object_agg Postgres function.

CREATE TABLE test (str text, num double precision);
INSERT INTO test(str, num) VALUES ('a', 1.0);
INSERT INTO test(str, num) VALUES ('b', 2.3);

select json_object_agg(str, num)
from test;

| json_object_agg        |
| :--------------------- |
| { "a" : 1, "b" : 2.3 } |

dbfiddle here

You can use row_to_json Postgres function.

create table foo(f1 int, f2 varchar(10), f3 float, f4 decimal(18,2));
insert into foo values (2, 'name', 2.345, 23.23);

select row_to_json(f)
from   (select f1,f2,f3,f4 from foo) f;

| row_to_json                                |
| :----------------------------------------- |
| {"f1":2,"f2":"name","f3":2.345,"f4":23.23} |

dbfiddle here


Inserting date from string: CAST vs CONVERT Consider the following two ways to convert a datetime varchar string into a date field:

SELECT convert(date, '2012-12-21 21:12:00', 20) -- Only date is needed
SELECT cast('2012-12-21 21:12:00' as date) -- Only date is needed

Both return what I expect: The date excluding the time, in as a date datatype.

My question is: Is there any pro and cons of doing either way?

The (formerly) accepted answer iswas incorrect as it iswas a bad and misleading test. The two queries being compared do not do the same thing due to a simple typo that causes them to not be an apples-to-apples comparison. The test in the accepted answer is unfairly biased in favor of the CAST operation. The issue is that the CONVERT operation is being done with convert(date, GETDATE()+num,20) -- a value to convert that changes per row -- while the CAST operation is being done with a simple cast(GETDATE() as date) -- a value to convert that is consistent across all rows and is replaced in the execution plan as a constant. And in fact, looking at the XML execution plan even shows the actual operation performed as being CONVERT(date,getdate(),0) !!

Insofar as my testing shows (after making them equal via using cast(GETDATE()+num as date)), the times varry with them being mostly the same (which makes sense if they are both reduced to being CONVERT anyway) or the CONVERT winning:

SET STATISTICS IO, TIME ON;
    ;with t as (
               select convert(date, GETDATE(),20) as fecha , 0 as num
             union all
             select convert(date, GETDATE()+num,20) as fecha, num+1 from t where num<1000000)
    select max(fecha)
      from t
    option (maxrecursion  0);
SET STATISTICS IO, TIME OFF;

-- 4754-07-23
--Table 'Worktable'. Scan count 2, logical reads 6000008, physical reads 0, read-ahead reads 0

-- SQL Server Execution Times:
--   CPU time = 9031 ms,  elapsed time = 9377 ms.

-- VS

SET STATISTICS IO, TIME ON;
    ;with t as (
               select cast(GETDATE() as date) as fecha , 0 as num
             union all
             select cast(GETDATE() as date) as fecha, num+1 from t where num<1000000)
    select max(fecha)
      from t
    option (maxrecursion  0);
SET STATISTICS IO, TIME OFF;

--2016-08-26
--Table 'Worktable'. Scan count 2, logical reads 6000008, physical reads 0, read-ahead reads 0

-- SQL Server Execution Times:
--   CPU time = 8969 ms,  elapsed time = 9302 ms.

SET STATISTICS IO, TIME ON;
    ;with t as (
               select cast(GETDATE() as date) as fecha , 0 as num
             union all
             select cast(GETDATE()+num as date) as fecha, num+1 from t where num<1000000)
    select max(fecha)
      from t
    option (maxrecursion  0);
SET STATISTICS IO, TIME OFF;

-- 4754-07-23
--Table 'Worktable'. Scan count 2, logical reads 6000008, physical reads 0, read-ahead reads 0

-- SQL Server Execution Times:
--   CPU time = 9438 ms,  elapsed time = 9878 ms.

The main difference between CAST and CONVERT is that CONVERT allows for the "style" to be specified. The "style" not only allows for tailoring the output when converting a non-string to a string, but also allows for specifying the input format when converting a string to a non-string:

SELECT CONVERT(DATE, '5/10/2016', 101); -- 101 = mm/dd/yyyy
-- 2016-05-10

SELECT CONVERT(DATE, '5/10/2016', 103); -- 103 = dd/mm/yyyy
-- 2016-10-05

Now compare that functionally with CAST:

SELECT CAST('13/5/2016' AS DATE);
-- Msg 241, Level 16, State 1, Line 71
-- Conversion failed when converting date and/or time from character string.

SELECT CONVERT(DATE, '13/5/2016', 101); -- 101 = mm/dd/yyyy
-- Msg 241, Level 16, State 1, Line 76
-- Conversion failed when converting date and/or time from character string.

SELECT CONVERT(DATE, '13/5/2016', 103); -- 103 = dd/mm/yyyy
-- 2016-05-13

One additional thing to mention about CAST: because it does not have the "style" parameter, the format of the date string passed in is assumed to be that of the current culture (a session property). The current culture is denoted by the @@LANGID and @@LANGUAGE system variables. This means that the CAST statement that failed in the test directly above could succeed for a different culture / language. The following tests shows this behavior and how that same date string does work with CAST when the current language is "French" (and would work with several others, based on the values in the dateformat column in sys.syslanguages):

IF (@@LANGID <> 0) -- us_english
BEGIN
    PRINT 'Changing LANGUAGE to English...';
    SET LANGUAGE ENGLISH;
    SELECT @@LANGUAGE AS [CurrentLanguage], @@LANGID AS [LangID];
END;

SELECT @@LANGUAGE, CAST('13/5/2016' AS DATE) AS [Test 1];
-- Msg 241, Level 16, State 1, Line 71
-- Conversion failed when converting date and/or time from character string.
GO

SELECT @@LANGUAGE, CONVERT(DATE, '13/5/2016', 103) AS [Test 2]; -- 103 = dd/mm/yyyy
-- us_english   2016-05-13
GO

IF (@@LANGID <> 2) -- Français
BEGIN
    PRINT 'Changing LANGUAGE to French...';
    SET LANGUAGE FRENCH;
    SELECT @@LANGUAGE AS [CurrentLanguage], @@LANGID AS [LangID];
END;

SELECT @@LANGUAGE, CAST('13/5/2016' AS DATE) AS [Test 3];
-- 2016-05-13
GO

SELECT @@LANGUAGE, CONVERT(DATE, '13/5/2016', 103) AS [Test 4]; -- 103 = dd/mm/yyyy
-- Français 2016-05-13
GO

-- Reset current language, if necessary.
IF (@@LANGID <> @@DEFAULT_LANGID)
BEGIN
    DECLARE @Language sysname;

    SELECT @Language = sl.[alias]
    FROM   sys.syslanguages sl
    WHERE  sl.[langid] = @@DEFAULT_LANGID;

    PRINT N'Changing LANGUAGE back to default: ' + @Language + N'...';

    SET LANGUAGE @Language;
    SELECT @@LANGUAGE AS [CurrentLanguage], @@LANGID AS [LangID];
END;


How to store user activity that supports undo? I can track user activity on posts, so that users can see for example "2 updates!". The essence of the strategy I’m using leverages 2 tables:

post_activity:
post_id | user_id | time_created

post_users:
post_id | user_id | time_created | time_last_seen

With these 2 tables, I can query all records from post_activity where the post_user.time_last_seen is less than the post_activity.time_created for a count of all new items.

The problem is that post_activity is essentially an anonymous log, with no associated rows or notion of the type of action that caused it. What I can’t solve is for example this scenario:

User A creates new post (1 new activity stored)
User B comments on new post (1 new activity stored)
User C sees "2 new!" but...
User A or B deletes their contribution.

As far as the system is concerned, there are 2 unseen items but the user may see nothing!

Can anyone provide a simple demonstration of how I might have a stronger relationship between activity and user actions here? Particularly one that supports unseen deleted activity?

Create a TRIGGER on the table in which deletes are recorded and update related tables as required.

Relevant postgresql documentation

Info about triggers: http://www.postgresql.org/docs/9.5/static/triggers.html
How to create a trigger: http://www.postgresql.org/docs/9.5/static/sql-createtrigger.html


MySQL / MariaDB Get last 7 days summarized and with avarages calculated currently, I have this table:

CREATE TABLE `plant_data` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `plant_id` BIGINT(20) UNSIGNED NOT NULL,
    `temperature` DECIMAL(5,1) UNSIGNED NOT NULL,
    `light` SMALLINT(5) UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
)

with the following example data:

INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1623, 14, 22.2, 35, '2020-02-16 09:00:06', '2020-02-16 09:00:06');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1622, 5, 22.8, 33, '2020-02-16 09:00:06', '2020-02-16 09:00:06');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1621, 14, 22.8, 36, '2020-02-16 08:00:07', '2020-02-16 08:00:07');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1620, 5, 23.3, 33, '2020-02-16 08:00:07', '2020-02-16 08:00:07');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1619, 14, 23.1, 36, '2020-02-15 07:00:11', '2020-02-15 07:00:11');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1618, 5, 23.8, 34, '2020-02-15 07:00:11', '2020-02-15 07:00:11');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1617, 14, 24.4, 38, '2020-02-15 06:00:09', '2020-02-15 06:00:09');
INSERT INTO `plant_data` (`id`, `plant_id`, `temperature`, `light`, `created_at`, `updated_at`) VALUES (1616, 5, 24.6, 34, '2020-02-15 06:00:09', '2020-02-15 06:00:09');

I want to get the average values of the last X days as one row for each day. I could do it with X querys for each day like

SELECT plant_id, avg(temperature) FROM plant_data WHERE created_at >= '2020-02-16 00:00:00' AND created_at <= '2020-02-17 00:00:00' GROUP BY plant_id;

But I want to know, if it is possible to get the data with one query to get a result like this:

+----------+------------------+------------+
| plant_id | avg(temperature) | day        |
+----------+------------------+------------+
|        5 |         24.58000 | 2020-02-16 |
|       14 |         24.42000 | 2020-02-16 |
|        5 |         23.58000 | 2020-02-15 |
|       14 |         23.42000 | 2020-02-15 |
+----------+------------------+------------+

Would be nice if someone has a good idea for doing this to save me query time.

First, a very nice first post. Both ddl and sample data makes it easy to recreate the setup. Second, all you have to do with your query is to add the date dimension:

SELECT plant_id, date(created_at) as day, avg(temperature)
FROM plant_data
WHERE created_at >= '2020-02-15 00:00:00'
  AND created_at <= '2020-02-17 00:00:00'
GROUP BY plant_id, date(created_at);

You might also be interested in the BETWEEN predicate:

SELECT plant_id, date(created_at) as day, avg(temperature)
FROM plant_data
WHERE created_at BETWEEN '2020-02-15 00:00:00'
                     AND '2020-02-17 00:00:00'
GROUP BY plant_id, date(created_at);

I haven't checked, but there is a risk that day is a reserved word. In addition I prefer to add more meaning to my identifiers, so I would possibly choose:

SELECT plant_id, date(created_at) as created_date, avg(temperature)
FROM plant_data
WHERE created_at BETWEEN '2020-02-15 00:00:00'
                     AND '2020-02-17 00:00:00'
GROUP BY plant_id, date(created_at);

As a complement to posting ddl and insert data you can also use - for example - Fiddle. There are more sites like this, and I here choosed this one for no particular reason.


Validate data between two large tables I have this scenario. Data files are transferred from multiple stores (transactions) to the head office as they happen. For each store the table in question is pretty big in size. At the head office, that same table contains the same info from all stores which makes it huge. The data is transferred through files as there isn't any direct connections between the HO and the stores.

I'm trying to find an efficient way to make sure that the data from each store has made it safe a sound to the head office table. To do this I have tried to do a checksum_AGG of a checksum for the fields that should match between the store and the HO for a specific Day for a specific store. But the performance was poor. I've also tried the sum of specific numeric columns and do the same at host for a specific Date and store to compare but the performance seems to be poor also.

I've tried to use a persisted computed column to calculate a row's hashbytes and maybe use that but unfortunately, I couldn't, since the table was created with ansi_nulls off, which is a restriction of the persisted computed columns. And it's unthinkable to fix that issue on a very large scale of stores and HOs.  I also thought of creating a trigger to fill any type of checksum per row, but the reality is that I will need to do this on multiple tables (maybe 50 tables) and to add triggers on every table will be very heavy, especially in OLTP system at the stores.

I have verified the fragmentation of the indexes and all is good.

Ideally it would be great if I transfer all the data from the store line by line and compare it against the HO but it's unthinkable since it's way too big.

I'm looking for ideas that I can explore to see if performance is acceptable. The idea is to get some type of checksum of a table per date (store/date) from a store and compare it with the same operation at the HO. This means that I would only need to transfer a checksum value per store and date to the HO.
The solution must be compatible from SQL Server 2008 up until the latest SQL Server version.

More info as requested
The sample table that I've been working with is one from a HO for my tests...
Size: 160Gb for 105 stores and 633 days (200 columns... I know... not ideal but it's what it is and practically impossible to change for all stores and HOs)

Example of queries tried to create a daily checksum per store:

select [trsDate],[store],CHECKSUM_AGG(checksum(amt,qty,total))
from BigTable
where store='111'
AND trsDate BETWEEN '2016-01-01' and '2016-01-30'
group by [trsDate],[store]
order by [trsDate],[store]

SELECT trsDate, store,'BigTable',
amt=SUM(ISNULL(amt,0)),
qty=SUM(ISNULL(qty,0)),
total=SUM(ISNULL(total,0))
FROM BigTable
WHERE trsDate BETWEEN '2016-01-01' and '2016-01-30'
AND store='111'
GROUP BY trsDate,store

select [trsDate],[store],CHECKSUM_AGG(checksum([trsDate],[store],terminal,trsNumb,linenumb))
from BigTable
where store='111'
AND trsDate BETWEEN '2016-01-01' and '2016-01-30'
group by [trsDate],[store]
order by [trsDate],[store]

SELECT [trsDate],[store], HASHBYTES('SHA2_512', concat(COUNT(*),CHAR(26), SUM(amt+qty+total))
from BigTable
where store='111'
AND trsDate BETWEEN '2016-01-01' and '2016-01-30'
group by [trsDate],[store]
order by [trsDate],[store]

Execution plan shows an index Seek 100%... Adding an index to Store\Date and include amt,qty and total has helped drastically but I have to investigate the impact of this index for the thousands of transaction processed and transfer.
Ideally I would love to have a checksum on all columns but the more I dig the more I realise that it will practically be impossible (otherwise I would need to double the size of each table by putting all columns in the indexe's include)

Transactional Replication.
As one idea, this sounds like a reasonable candidate for transactional replication. Checksums, cyclical redundancy checks (CRC), and row counts are used to validate source data with the destination data.
From Validate Replicated Data, Books Online:

How Data Validation Works
SQL Server validates data by calculating a row count or a checksum at the Publisher and then comparing those values to the row count or checksum calculated at the Subscriber. One value is calculated for the entire publication table and one value is calculated for the entire subscription table, but data in text, ntext, or image columns is not included in the calculations.
While the calculations are performed, shared locks are placed temporarily on tables for which row counts or checksums are being run, but the calculations are completed quickly and the shared locks removed, usually in a matter of seconds.
When binary checksums are used, 32-bit redundancy check (CRC) occurs on a column-by-column basis rather than a CRC on the physical row on the data page. This allows the columns with the table to be in any order physically on the data page, but still compute to the same CRC for the row. Binary checksum validation can be used when there are row or column filters on the publication.


Modify TNSNAMES.ORA file to connect to an Oracle Database I would like to modify TNSNAMES.ORA file to connect to an ORACLE Database and I noticed that on my PC there are 3 different files with name TNSNAMES.ORA files. Which of these three should I modify in order to make connection

D:\app\XXX\product\11.2.0\client_32\network\admin
E:\app\XXX\product\11.2.0\client_64\network\admin
D:\app\XXX\product\11.2.0\client_1\network\admin

You may start command prompt using windows commanline cmd and use below:

tnsping any_random_name

as you can see in the image, it uses parameter file where sqlnet.ora exists. This is the folder where your current oracle installation points to. You should make change in that folder's tnsnames.ora.


Separate Schemas by Industry or by Application functionality? I currently have a small retail/Point of Sale and an Education application running as on-premise custom solutions for different customers. I am planning to rewrite the app and host as a SaaS/web based.

I might use Postgresql, I would go for a Single DB and shared schema. (The data volume is low and the mo. of concurrent users will be 400 max.).

The retail app addresses billing, inventory etc.,(backoffice/mini-ERP). The education app covers student enrollment, management and student prospecting etc.,(mostly Frontoffice - Student management, Knowledge Management, Leads, some Marketing etc.,)

The initial thought was to have a common public Schema (with addresses, contacts and other common table objects) and have specific Schemas for Retail and Education. i.e., Industry vertical -wise. As I add more customers in different businesses than existing I will end up adding additional schema. This could be one approach.

Or I was thinking should I go for a public schema and separate schema for ERP type functionality, CRM - like functionality etc., This way the schema will be Horizontal i.e., based on functionality rather than Industry. Tomorrow I can add a Chemical Retail or Medical contact management client or a Restaurant client who need billing and fit them easily into existing data models and can handle the variations in application code rather than creating new schemas for each business line.

Confused on which approach will be better in the long run?

EDIT: Or should I just not bother and stick with the public schema and handle variations in code? or are there any compelling reasons for the above approaches or any other better approaches than this?

I understand that you want to go with single database (as it is good from management & maintenance point of view), but maybe it's too much integration.

I am assuming that:

you will have separate application for each industry,
they have not very much in common (they really cover different business aspects)

I think that one of acceptable solutions in this case would be:

a separate database for each industry
in every database, single shared public schema for shared data, including common dictionaries, all non-customer-dependent data.
one separate database user account + private schema for each customer. You might use postgres feature of table inheritance to conform to some public table.

Issues that Simon puts in his comment are really important. So you will have to enforce a very strict policy regarding GRANTs in your database.

In PostgreSQL, it is possible to achieve (as well in many other RDBMS). The key to achieving multi-tenant solution would be intelligent usage of schemas, roles, search_path setting. See http://www.postgresql.org/docs/current/static/ddl-schemas.html. Actually what I propose will in a way emulate what Oracle does. If you need help on details please ask.


how to dump a case-sensitive table within a specific schema I'm trying to dump a case-sensitive table named app_auth.User by executing the following command

pg_dump --schema=app_auth -t '"User"' -U em3local -h 127.0.0.1 -Fc -a -f <path> <dbname>

but what I'm returned is:

pg_dump: No matching tables were found

If I actually query my DB, the table does exist:

<dbname>=# SELECT * FROM app_auth."User";
 username | email | password | name | surname | lang | resettoken | userinfo_id | state | tos_accepted | id
----------+-------+----------+------+---------+------+------------+-------------+-------+--------------+----

any hint?

When you specifies the schema name to unload it does not mean that it will be default schema to search objects:
nd@postgres=# create schema foobar;
CREATE SCHEMA
nd@postgres=# create table foobar."Foo"();
CREATE TABLE
nd@postgres=# \! pg_dump --schema=foobar -t "Foo"
pg_dump: no matching tables were found

So you should to fully qualify the table name:
nd@postgres=# \! pg_dump --schema=foobar -t 'foobar."Foo"'
--
-- PostgreSQL database dump
--
... etc ...

Excerpt from the documentation (cursive and bold is mine):

The -n and -N (--schema) switches have no effect when -t is used, because tables selected by -t will be dumped regardless of those switches, and non-table objects will not be dumped.
Formerly, writing -t tab would dump all tables named tab, but now it just dumps whichever one is visible in your default search path.


How to "normalize" external data before insertion into "Data Warehouse" like database? I want to be database agnostic but let's say we're inserting data into SQL Server which is to be used as a data warehouse.
There are MILLIONS of email records from an email platform that wasn't too well though out technically. It's data is not normalized.
So for instance one import might be 7 million records where one important field is a subject line (for testing purposes) and that subject line is "Happy Mother's Day One and All, we love Long Texts!" for all 7 million records.
Now add that "7 million of the exact same long text" to about maybe 50 different subject lines. The benefits of normalization couldn't be clearer with this example. Convert the highly redundant long texts to integers, and then store a small dimensional table translating them back to text. Right?
I'm just wondering the exact implementation of this.
I'm going to use an ETL tool (one or another) but I suppose there are multiple ways of doing it. I guess I would attempt to translate the "subject" into an int based on the small dimensional table, and if no match is found, I would take the distinct new records and append them with an auto-incrementing table? Just wondering if this is a common task.
Normalizing smelly data before insertion into a data warehouse. I don't mean cleaning, auditing, profiling --- I mean literal database normalization to shrink the data volume.

The whole process you described is implemented in the database engine itself for the columnstore compression.
DB engine will scan data for repeating text, find duplicates and create dictionary tables. In the row data it will keep only minimal references to dictionary.
Be sure to keep data batches big enough to trigger this compression process during insert - follow this guidance: Columnstore indexes - Data loading guidance


Copy PostgreSQL database from a remote server When I was using MySQL, I could run a command that would SSH into my server and copy the database to my local machine.
ssh -t remoteserver 'mysqldump --compress -u dbuser --password="password" db_name' | /usr/local/mysql/bin/mysql -u root --password="password" local_db_name

How can I do the same thing with PostgreSQL?

If the DB server takes database connections from your host (usually on port 5432), then you don't need to use SSH.  In this case, you need to do the following:

pg_dump -h your_db_host -U db_user -C db_name | psql -h localhost -d local_db_name -U local_db_user

If you can only connect to your database from the host itself, you have to add the SSH logic from your MySQL example.

With -C, you will get a CREATE DATABASE command in the beginning of the dump so you don't have to create it yourself.  The above command will ask for a password twice unless you store these in a .pgpass file (assuming you are on linux/Unix).  Also, the local connection may be different if your DB doesn't listen on TCP (in this case just omit the -h localhost part).

For further details and options, see pg_dump documentation.


Will moving the MATCH...AGAINST part in different part of the query (e.g. before evaluation of JOIN) will improve the performance? Basically, I have the following query:

select `t`.*
from `tracking` `t`
where exists
(
    select `ti`.`id` from `tracking_items` `ti`
    inner join `cars` `c` on (`c`.`car_id` = `ti`.`tracking_object_id`)
    where `t`.`id` = `ti`.`tracking_id` and match(`c`.`car_text`) against('+bulgaria +turkey' in boolean mode)
    limit 1
) and ... limit 5

The problem I have?

There's FULLTEXT index for c.car_text column, but however the query is running a bit slowly (especially for records that rarely occurs, e.g. searching for non-existing keyword).

I haven't published the result of EXPLAIN, as I'd like to know the theory behind my question, e.g. for different possible cases.

I use MySQL 5.6 with InnoDB storage engine, also it's worth to mention that cars table have like ~160m records.

I am glad you came here. I have always suggested using FULLTEXT indexes to collect keys first and then join it to other tables.

Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jul 18, 2012 : Why full-text-search returns less rows than LIKE
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Jan 29, 2012 : Mysql fulltext search my.cnf optimization
Aug 01, 2016 : How to make a FULLTEXT search with ORDER BY fast?

In your particular case, I would suggest rewriting your query as an all-out JOIN:

SELECT t.* FROM tracking t
INNER JOIN tracking_items ti ON t.id = ti.tracking_id
INNER JOIN (SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
ON ti.tracking_object_id = c.car_id LIMIT 5;

or maybe

SELECT t.* FROM
(SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
INNER JOIN tracking_items ti ON c.car_id = ti.tracking_object_id
INNER JOIN tracking t ON ti.tracking_id = t.id  LIMIT 5;

If these queries give you an idea, please incorporate it if you still want to use WHERE EXISTS.

Please run the explain plan on these queries and your and see the differences

EXPLAIN select `t`.*
from `tracking` `t`
where exists
(
    select `ti`.`id` from `tracking_items` `ti`
    inner join `cars` `c` on (`c`.`car_id` = `ti`.`tracking_object_id`)
    where `t`.`id` = `ti`.`tracking_id` and match(`c`.`car_text`)
    against('+bulgaria +turkey' in boolean mode)
    limit 1
) and ... limit 5;

EXPLAIN SELECT t.* FROM
(SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
INNER JOIN tracking_items ti ON c.car_id = ti.tracking_object_id
INNER JOIN tracking t ON ti.tracking_id = t.id LIMIT 5;

EXPLAIN SELECT t.* FROM tracking t
INNER JOIN tracking_items ti ON t.id = ti.tracking_id
INNER JOIN (SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
ON ti.tracking_object_id = c.car_id LIMIT 5;

Give it a try, and let us know what happens !!!


What are best practices for storing many iterations of a product in an RDBMS? We are building a tool to track the prices of products over time, and using Postgres as our RDBMS. It is important that product attributes can be changed, and that the history of an product's attributes be preserved forever. Here is a schema we designed based on OpenStreetMap's internal schema:

We have a 'products' table on the left storing every version of every product, and a 'current_products' table on the right storing only the most recent version of each product. Every time we want to change a store, we:

create an entry in changesets
read the latest entry of the product in 'products', increment version by one, and create another entry with the changes
delete the corresponding entry in 'current_products' and create a new one with the changes and the latest version number from 'products'

We want to enforce as many business rules in the database engine as possible rather than relying on our software to keep things consistent, and this schema feels pretty "off", so we welcome any suggestions. Thanks in advance!

Edit:
Revised the schema based a response from @bbaird . Also decided to include versioning of stores and users. Tied products, stores, and users together with price table.

It should work. But looking on your diagram I think you could merge products with product_changeset as you could have for every product version info who and when changed that. You could also consider to avoid to have dedicated table current_product and instead use some kind of flag (active or timestamp on main products table). There is lot of possibilities, but best solution needs to think about:

how many products do you expect to have?
how often such changes can be made?
do you want to change any attribute or only to specific ones?
what is usage of this product history? Do you need to be able to revert to some point of history? Or do you want to show it somewhere to the user? Or is just it should be in db just for some audits, just as kind of logs?

added as a response to comment, it doesn't allow for such long ones :)
So I see two solutions here:

your original one with that change that current_products should provide unique product id which would be then referenced by products directly. I think you could change relation of product_changesets, and connect that
to primary key of products. This seems like less used entity.

+ better queries for only current versions of product - which seems to be major of your needs
- more complicated when new version is provided
- more complicated when you need to revert to some previous version
- risk of desynchronize tables - you can have different actual versions in both tables; which one should be a source of truth?

alternatively you could have one table with immutable values (like product id or similar) which would be main table in fact for products. Then there could be kind of product_history or product_versions which would
have relation to main product table and would have all attributes plus some flag (like active) which would declaratively say that this only row should be considered as valid one.

+ easier than previously to add new version - you need only to have some trigger or similar to update flag of previous version, and some default on this flag, so modifying version would be only adding new rows in fact
+ no risk that you will have different values for current version in separate tables
- slightly more complicated standard queries - you need to take product by, say, its id and this active column - you can have view for that
- there is risk that after many years for many changes, this table will be quite big; so maybe you could store only previous version, and previous ones somehow remove?

In any case, you have just to connect this price_entries tables like you have already on diagram - using main unique id of product. Then, again, you have figure out how to say that specific entry is the current one - similarly like above you can
try to reflect that by some current_price entity, but it seems like overcomplicating. There some active columns seems like doing a job. Do you need to connect specific price entry with specific version of product?
And please make sure that products attributes are kind of lookups (with dedicated tables), not free text fields, as it easily brings a mess to db.


Disaster Recovery through cloning drives in sql server 2014 enterprise I am a DBA noob and recently my work has considered to have a disaster recovery plan for their production DB lets say A (sql server 2014 enterprise). The production server A and Disaster recovery server B are in different geographical locations. We have just 10 databases in the production A and each DB size does not exceed 4GB. During the installation, I have changed the default locations to D, E and F respectively and the system databases and tempdb files are stored in D and E drive as well. However I left the root directory as default in C drive in both the servers A and B. I have followed the similar path configuration in Disaster Recovery (DR) server as well.

So my management plan is to make clone copies in Disaster Recovery (B) server of all LUNs that are replicated each hour from production server A. Once this copies ( D and E drive from A) are completed, they are planning to mount these in DR server.

So my question, is this appropriate way to follow Disaster Recovery ? Also, what issues I might face ? I am little worried whether databases will be up and running once this mounting is completed in DR server ? If not what steps should I consider ? Any suggestions highly appreciated.

  Data : D:\MSSQL\Data\
  Log: E:\MSSQL\Data\
  Backup: F:\MSSQL\Backup

Firstly, you need to determine what the acceptable Recovery Point Objective (total data loss) and Recovery Time Objective (time taken to restore the system) is for each database on your production server.
If the amount of data loss your business is willing to accept is less than the interval that your disks are replicated to your DR site then this won't work as a strategy. If it takes longer to clone and provision the disks for your DR server than the RTO, then this solution won't work.
Download Brent Ozar's First Responder Kit, which includes a High Availability and Disaster Recovery worksheet to help you determine and design a suitable DR solution for your databases.
There are many options within SQL Server itself for providing DR capabilities that might be better suited than SAN replication (although that is always an option). Log Shipping, for example, is a simple, low-cost solution that might achieve lower RPO and RTO than hourly SAN replication and can be simpler than having to clone LUNs and attach them to the SQL Server.


mysql dummy where check to utilize multi column index I have multi-column index on col1,col2. Both columns are unsigned int with values 1 or greater. I want WHERE col2=100. I write WHERE col1>0 AND col2=100. The query should now use my index.

Does this work and what are the ramifications. How does it get optimized etc.

It is doubtful that the optimizer would choose to use this index, and if it did, MySQL would need to do a complete index scan in order to match the second entry. This would be less efficient than a table scan.

Add an index on col2.

EDIT 7/11/2012

It occurs to me that an analogy might help you understand why this can't possibly work.

Consider that an index is sort of like a phone book from ye olde times.  Phone books have a compound index on (lastname, firstname).

This is a phone book:

CREATE TABLE phonebook (
  lastname varchar(255) NOT NULL,
  firstname varchar(255) NOT NULL,
  phone varchar(11) NOT NULL,
  PRIMARY KEY (lastname,firstname)
)

If you want to find my number in the book, you would find Brown, Aaron in the book which would then have the data that you want (my number).  If you want to find all people with the last name 'Brown', it's also easy because they are organized by lastname - you just find the first 'Brown' entry and keep going until you are no longer looking at "Brown's."  So, this works:

SELECT phone FROM phonebook WHERE lastname = 'Brown';

or even

SELECT phone FROM phonebook WHERE lastname LIKE 'B%';

What if you want to find everyone named "Aaron?"  You can't use the index that the phone book provides - you have to look at every single entry in the book in order to get all the Aarons.

-- this will still have to scan every record in the table
SELECT phone FROM phonebook WHERE lastname >= 'A' AND firstname = 'Aaron';

In your example, col1 is lastname and col2 is firstname.  You are trying to trick MySQL into using a phone book index by saying "Give me all the people with the first name of Aaron that also have a last name that starts with a letter."  Knowing that the last name starts with a letter doesn't help you find all the Aarons, just like knowing that col1 > 0 doesn't help.  You still have to look at every entry in the table.


in Mariadb - Can you cache the indexes and data for Aria Storage engine tables? I have a table that is a read-only table (real-time view), I used Aria based on the recommendation for read-heavy workload.
Based on this What are the main differences between InnoDB and MyISAM?
When it comes to cache is Aria like MyISAM or like InnoDB? meaning, Can I cache only indexes or can I also cache the data? (under the assumption I have large enough RAM)

As for now
We plan to also add CACHE INDEX for Aria tables if there is a need for this.

And as you can see that it is written in https://mariadb.com/kb/en/cache-index/ so that it would be more like the MyISAM, but as development and theory grows in time, we will see and wait, what they actually want to do qand implement.


I'm not certain.  I tried to start out be reproducing your problem but the alter worked fine for me.

test > CREATE TABLE `bar` (  `content` text ) ENGINE=MyISAM DEFAULT CHARSET=latin1;  INSERT INTO bar VALUES (0x8081828384858687898A8B8C8D8E8F909192939495969798999A9B9C9D9E9F);
Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

test > ALTER TABLE bar CHANGE content content TEXT CHARACTER SET UTF8;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

test > select * from bar;
+---------------------------------+
| content                         |
+---------------------------------+
| ����������������������������� |
+---------------------------------+
1 row in set (0.00 sec)

test > set names utf8;
Query OK, 0 rows affected (0.00 sec)

test > select * from bar;
+---------------------------------------------------------------------------------+
| content                                                                         |
+---------------------------------------------------------------------------------+
| €‚ƒ„…†‡‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Here's my related char settings

test > show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Edit

My char settings before running set names utf8

test > show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

Version

test > select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.1.41-3ubuntu12.10-log |
+-------------------------+
1 row in set (0.00 sec)


Filter based on values after join operation - MongoDB I have two collections in the following format -

collection1:

{
    "_id": "col1id1",
    "name": "col1doc1",
    "properties": [ "<_id1>", "<_id2>", "<_id3>"]
}

collection2:

{
    "_id": "<_id1>",
    "name": "doc1",
    "boolean_field": false
},
{
    "_id": "<_id2>",
    "name": "doc2",
    "boolean_field": true
},
{
    "_id": "<_id3>",
    "name": "doc3",
    "boolean_field" : false
}

The desired output is:

{
    "_id": "col1id1",
    "name": "col1doc1",
    "property_names": ["doc1", "doc3"]
}

The field properties of document in collection1 has three IDs of documents in collection2 but the output after join operation should contain only those which have the boolean_field value as false.

How can I perform this filter with a join operation in MongoDB?

In MongoDB 3.6+ you can add a pipeline to your $lookup stage to include additional join conditions.

A solution was less obvious than I expected since your source lookup field is an array, but the following should return the expected outcome:

db.collection1.aggregate([
    { $match: {
        _id: "col1id1"
    }},

    // Filtered lookup based on agg pipeline
    { $lookup: {
        from: "collection2",
        // let references fields in the original collection
        let: { property: "$properties" },
        pipeline: [
            { $match:
                // use $expr to compare fields using let variables
                { $expr:
                    { $and: [
                        { $in: [ "$_id", "$$property" ] },
                        { $eq: [ "$boolean_field", false ] }
                    ]}
                }
            },
            { $project: { "_id": 0, "name": 1 }}
        ],
        as: "property_names"
    }},

    // Project desired output fields
    { $project: {
        "name" : "$name",
        "property_names": {
            $map: {
                input: "$property_names",
                in: "$$this.name"
            }
    }
    }
}
]).pretty()

Sample output:

{
    "_id" : "col1id1",
    "name" : "col1doc1",
    "property_names" : [
        "doc1",
        "doc3"
    ]
}


Problem using pivot to display data I currently have a table called sample, the structure of my attendance table is:

create table sample
(
  Student_ID VARCHAR2 (10),
  Late_Attendance number (10) Default 3,
  Attendance_Date Date,
  Attendance_ID VARCHAR2 (10)
);

Sample data:

insert into sample
values ('SCM-026020', default, To_Date ('4/4/2016', 'DD/MM/YYYY'), 'LATE');
insert into sample
values ('SCM-026021', default, To_Date ('4/4/2016', 'DD/MM/YYYY'), 'ABSENT');
insert into sample
values ('SCM-026022', default, To_Date ('4/4/2016', 'DD/MM/YYYY'), 'PRESENT');
insert into sample
values ('SCM-026020', default, To_Date ('5/4/2016', 'DD/MM/YYYY'), 'ABSENT');
insert into sample
values ('SCM-026021', default, To_Date ('5/4/2016', 'DD/MM/YYYY'), 'ABSENT');
insert into sample
values ('SCM-026022', default, To_Date ('5/4/2016', 'DD/MM/YYYY'), 'PRESENT');

This is the SQL I used based on pivot:

select *
from  (
        select student_id, attendance_Date, attendance_id,
        sum (DECODE (Attendance_id, 'ABSENT', 1, 0)) absent,
        SUM (DECODE (Attendance_ID, 'LATE', 1, 0)) LATE,
        SUM (DECODE (Attendance_ID, 'PRESENT', 1, 0)) PRESENT
        from sample
        group by student_id, attendance_id, attendance_date
      )

pivot (
          MAX (Attendance_ID)
          for Attendance_Date IN (To_Date ('4/4/2016', 'DD/MM/YYYY') "4/4/2016",
                                  To_Date ('5/4/2016', 'DD/MM/YYYY') "5/4/2016")
      )

Why I am getting such weird output?

STUDENT_ID     ABSENT       LATE    PRESENT 4/4/2016   5/4/2016
---------- ---------- ---------- ---------- ---------- ----------
SCM-026021          1          0          0 ABSENT     ABSENT
SCM-026022          0          0          1 PRESENT    PRESENT
SCM-026020          1          0          0            ABSENT
SCM-026020          0          1          0 LATE

The Student_ID scm-026020 is appearing twice which should only appear once and the sum such as absent for scm-026021 should be 2 but displaying as 1.

My expected output would be:

STUDENT_ID     ABSENT       LATE    PRESENT 4/4/2016   5/4/2016
---------- ---------- ---------- ---------- ---------- ----------
SCM-026021          2          0          0 ABSENT     ABSENT
SCM-026022          0          0          2 PRESENT    PRESENT
SCM-026020          1          1          0 LATE       ABSENT

There should always be one row per student and day.

If I remove the sum (DECODE (Attendance_id, 'ABSENT', 1, 0)) absent part, it gives the correct output, but I need to put this since I need to calculate at the end the number of attendance days they are late, absent, and present.

The columns ABSENT, LATE and PRESENT in your output represent totals per student, whereas the date columns represent details per student, and details are the pivoted columns. In order to get such output with PIVOT, your source must provide totals on the same row with details.

The source in your query is not providing that kind of information. It is grouping by student_id, attendance_Date, attendance_id, and that grouping does not have any effect since there is always one row per student and date.

In order to have both details and totals on the same row, you can use window aggregation, like this:

SELECT
  student_id,
  attendance_Date,
  attendance_id,
  SUM (DECODE (Attendance_id, 'ABSENT',  1, 0)) OVER (PARTITION BY student_id) AS ABSENT,
  SUM (DECODE (Attendance_ID, 'LATE',    1, 0)) OVER (PARTITION BY student_id) AS LATE,
  SUM (DECODE (Attendance_ID, 'PRESENT', 1, 0)) OVER (PARTITION BY student_id) AS PRESENT
FROM
  sample

Note absence of GROUP BY. The OVER clause makes SUM a window aggregate function. With a window function you can return aggregate data along with detail (non-aggregate) data. The query above returns output like this:

STUDENT_ID  ATTENDANCE_DATE  ATTENDANCE_ID  ABSENT  LATE  PRESENT
----------  ---------------  -------------  ------  ----  -------
SCM-026020  4/4/2016         LATE           1       1     0
SCM-026020  5/4/2016         ABSENT         1       1     0
SCM-026021  5/4/2016         ABSENT         2       0     0
SCM-026021  4/4/2016         ABSENT         2       0     0
SCM-026022  4/4/2016         PRESENT        0       0     2
SCM-026022  5/4/2016         PRESENT        0       0     2

That is, it returns details per date and student along with totals per student. Using that source, the PIVOT clause provides the expected result:

STUDENT_ID  ABSENT  LATE  PRESENT  4/4/2016  5/4/2016
----------  ------  ----  -------  --------  --------
SCM-026020  1       1     0        LATE      ABSENT
SCM-026021  2       0     0        ABSENT    ABSENT
SCM-026022  0       0     2        PRESENT   PRESENT


Restoring schemas with the sequences This evening a developer asked to restore only schemas from a database into another database.
A colleague of mine did this, but after that the developer came to me saying that the sequences are missing from the restored schema.
I think this was the command my colleague used to get a dump of the schema only :
/usr/local/pgsql915/bin/pg_dump -U backupuser --schema-only myegrtx -p 5432 -t tb_roadtax_kioskinfo -t tb_rtx_expdelivery_postcode -t tb_roadtax_inquiryinfo -t roadtax_tx_history -t tb_roadtax_payment_data_info -t tb_roadtax_renewalinfo -t tb_roadtax_kioskusers -t tb_roadtax_kioskuser_trans -t tb_roadtax_delivaryinfo -t tb_roadtax_asinfo  -t tb_roadtax_disk_print_info > myegrtxbike-schema.sql

And this was the command to restore it to the new db :
/usr/local/pgsql934/bin/psql -U postgres -d myegrtxbike -p 6434 -f srikanth/myegrtxbike-schema.sql

How do I now get the backup of and restore the sequences that have been reported missing?
I need some guide on this, as googling the commands is not a success (The commands do not work or I do not get the proper commands, especially for the sequence part).

Simply add -t <sequencename> for all the sequences you want to dump.


Relation between multiple hierarchical taxonomies linked to a single entity I am working on an online test application which supports multiple choice questions.
I have created schema for taxonomy (classification) system to be used with questions.
taxonomy(id, name, slug, description)
taxonomy_terms(id, taxonomy_id, name, slug, parent, weight)

question_taxonomy(id, question_id, taxonomy_term_id)

A question is classified under various categories (taxonomies).
At the top rests Exam taxonomy which has Subject taxonomy as children. Each Subject consists of various Topic taxonomy which may further be divided into Sub Topics by adapting Sub Topic taxonomy.
To create relationships between various taxonomy terms(linked across taxonomies), I have created another table as
taxonomy_term_relations(id, parent_id, child_id)

e.g,
MAT is a taxonomy term for Exam Taxonomy
MAT may have two subjects viz Reasoning & General Knowledge which are taxonomy terms for Subject taxonomy
Reasoning subject will have 10 different topics with taxonomy terms falling under Topic taxonomy, say Reasoning will have Topic A, Topic B ....
Each Question will belong to a Topic which in turn will belong to Subject which again will belong to Exam.
A Subject may belong to multiple Exams or a Topic may belong to multiple Subjects.
Is this the correct approach to create db schema for this system.
UPDATE
@MirNazim suggested another approach to this problem with Exams, Subjects and Topics treated as separate entities rather than taxonomy, which will reduce the complexity of whole thing. So the new structure may become
exams(id, name, description...)
subjects(id, name, description ...)
exam_subjects(id, exam_id, subject_id)
topics(id, name, parent, weight....)
subject_topics(id, subject_id, topic_id)

Any suggestions on this approach will be appreciated.

Taxonomy -> TaxonomyTerm
TaxonomyTerm -> Subject
Subject -> Topic
Topic -> Question

Each of these lines is a 1 to many relationship, e.g. A single Taxonomy can have 1 or more TaxonomyTerms, and these terms can be related to multiple Taxonomies. I think you mostly have it but I'm not seeing a need to link one TaxonomyTerm directly with another.


The invoice number is characters and numbers combined but I want to change these to numerical only in SQL The invoice number is characters and numbers combined but I want to change these to numerical only in SQL.

I want like this CC/DDD/1, CC/DDD/2 .... n

To answer your question, I did the following (all of the steps shown below are in the fiddle here).

First part - if you wish to keep the old invoice numbers:

Construct and populate the table:

CREATE TABLE invoice
(
  invoice_id VARCHAR (100) NOT NULL
);

INSERT INTO invoice
VALUES
('CC/DDD/2018-2019/3'),
('CC/DDD/2018-2019/6'),
('CC/DDD/2018-2019/7'),
('CC/DDD/2018-2019/9'),
('CC/DDD/2018-2019/12'),
('CC/DDD/2018-2019/23'),
('CC/DDD/2018-2019/27'),
('CC/DDD/2018-2019/122222'),  -- The large numbers are for testing of sorting,
('CC/DDD/2018-2019/244444');  -- thanks to @DougDeden for pointing out problems in this area

I'll step through the logic I used.

SELECT CHARINDEX('/', REVERSE(invoice_id)) AS cutoff FROM invoice;

Result:

cutoff
2
2
2
2
3
3
3
7
7

Get the REVERSE of the string along with the CHARINDEX of / and then obtain the position of the first slash (/) which tells us where the number of interest lies.

Then, I ran:

SELECT
  LEFT(REVERSE(invoice_id), CHARINDEX('/', REVERSE(invoice_id)) - 1) AS the_num
FROM invoice;

Result:

the_num
3
6
7
9
21
32
72
222221
444442 -- note original is 24444

The LEFT function gives us the actual number to be suffixed (added) to the invoice.

Finally, I ran:

SELECT SUBSTRING(invoice_id, 1, 7)
        + REVERSE(LEFT(REVERSE(invoice_id),
          CHARINDEX('/', REVERSE(invoice_id)) - 1)) AS new_invoice_id
FROM invoice
ORDER BY -- CAST(new_invoice_id AS INTEGER);  -- try this more elegant but it doesn't work!
  CAST(REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/',
                    REVERSE(invoice_id)) - 1)) AS INTEGER);

Result:

new_invoice_id
CC/DDD/3
CC/DDD/6
CC/DDD/7
CC/DDD/9
CC/DDD/12
CC/DDD/23
CC/DDD/27
CC/DDD/122222
CC/DDD/244444

Note that in order to obtain the correct result, it is necessary to REVERSE back the original one, not very elegant, but these functions are quite performant. Thanks to @DougDeden for pointing out an error in the original answer!

As mentioned above, all of this is in the fiddle here.

Second part - if you wish to renumber the invoices:

@Lennart proposes a simultaneous renumbering, so for completeness, I'm adding a similar solution to my answer. The first steps are the same as above - the SQL does become a bit cumbersome, but it can be done as follows - first, to obtain the new numbers:

SELECT
  REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/', REVERSE(invoice_id)) - 1)) AS the_num,
  ROW_NUMBER()
    OVER (ORDER BY CAST(REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/', REVERSE(invoice_id)) - 1)) AS INTEGER))
    AS new_number
FROM invoice
ORDER BY
  CAST(REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/',
            REVERSE(invoice_id)) - 1)) AS INTEGER);

Result:

the_num new_number
3       1
6       2
7       3
9       4
12      5
23      6
27      7
122222  8
244444  9

Be very careful when sorting - if you sort INTEGERs as VARCHARs, the results are anomalous - basically, sorting INTs as strings means that the "numbers" are sorted by the first digit, so 32, 332 and 45 will sort as 32, 332 and 45 and not 32, 45, 332.

So, now we have our renumbering - a final step is necessary:

SELECT
  SUBSTRING(invoice_id, 1, 7) +
  CAST(ROW_NUMBER()
    OVER (ORDER BY CAST(REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/',
                        REVERSE(invoice_id)) - 1)) AS INTEGER)) AS VARCHAR)
  AS invoice_no
FROM invoice
ORDER BY
  CAST(REVERSE(LEFT(REVERSE(invoice_id), CHARINDEX('/',
           REVERSE(invoice_id)) - 1)) AS INTEGER);

Notice the CAST of the ROW_NUMBER() - if this is not done, an error is thrown Msg 8114 Level 16 State 5 Line 1 Error converting data type varchar to bigint. You can't "add" an INTEGER to a string!

Result:

invoice_no
CC/DDD/1
CC/DDD/2
CC/DDD/3
CC/DDD/4
CC/DDD/5
CC/DDD/6
CC/DDD/7
CC/DDD/8
CC/DDD/9

As mentioned in the comments, this renumbering is probably not a good idea as it will require the maintenance of a correspondance between the old invoice numbers and the new invoice numbers. All of these steps above are shown in the fiddle here.


Greenplum DB changing table column length I am new to using Greenplum DB, was working on Oracle DB. I understand conceptually Greenplum in columnar DB and has different workings then Oracle.

The question I have is regarding altering table column length. I created a table with a column with say data type character varying(50). On top of this table I created a view which is summarizing some information. Later I wanted to change the length of column to character varying(100). It is not allowed in GP.

I had to drop view, change the column length and then re-create the view. I do not believe that this was the case on Oracle. Not sure why GP has to to this.

Does someone has understanding as to how to avoid this?

It's not even about the type or subtype. Changing a column on a table requires you to drop and recreate all dependencies that reference it. You can do this in a single transaction.

CREATE TABLE foo ( a varchar(10) );
CREATE VIEW bar AS TABLE foo;

BEGIN;
  DROP VIEW bar;
  ALTER TABLE foo
    ALTER COLUMN a
    SET DATA TYPE varchar(100);
  CREATE VIEW bar AS TABLE foo;
COMMIT;


How do I declare and use variables in Oracle? My main skills are with SQL Server, but I have been asked to do some tuning of an Oracle query. I have written the following SQL:

declare @startDate int
select @startDate = 20110501

And I get this error:

declare @startDate int
select @startDate = 20110501
Error at line 1
ORA-06550: line 1, column 9:
PLS-00103: Encountered the symbol "@" when expecting one of the following:

   begin function package pragma procedure subtype type use
   <an identifier> <a double-quoted delimited-identifier> form
   current cursor

How do I declare and use variables in Oracle?

Inside pl/sql block:

declare
 startdate number;
begin
  select 20110501 into startdate from dual;
end;
/

using a bind variable:

var startdate number;
begin
  select 20110501 into :startdate from dual;
end;
/

PL/SQL procedure successfully completed.

SQL> print startdate

 STARTDATE
----------
  20110501

in a query:

select object_name
from user_objects
where created > to_date (:startdate,'yyyymmdd');  /*prefix the bind variable wïth ":" */


Report an error in merge's when-not-matched-then clause Is it possible to report an error (raise exception or anything) in merge's when-not-matched-then clause? I'm aiming for something like

WHEN NOT MATCHED THEN RAISE NO_DATA_FOUND;

Or anything that will tell the caller that script failed.

You can do this using a trigger.

Note: This is HORRIBLE, but I can't think of another way of achieving this.

Test tables:

create table testmerge
(
col1 number primary key ,
col2 number
);

insert into testmerge values ( 1, 1 );

create table testmerge2
(
col1 number primary key,
col2 number
);

insert into testmerge2 values ( 2, 2 );

Trigger on merge table:

create or replace trigger testmergeerror_btrig
  before insert on testmerge
  for each row
begin
if :new.col1 = -99999999999 and :new.col2 = -99999999999
then
    raise_application_error(-20000,'Your error message');
end if;
end;
/

Merge:

MERGE INTO testmerge t1
    USING testmerge2 t2
    ON (t1.col1 = t2.col1)
  WHEN MATCHED THEN
    UPDATE SET t1.col2 = t2.col2
WHEN NOT MATCHED THEN
insert(col1,col2) values (-99999999999,-99999999999);

Basically, when a row doesn't match, we set some values (that your application will never use, yes I know it is a horrible way of doing it!) in the INSERT that the trigger looks for, then fires an error.

Test case:

SQL> MERGE INTO testmerge t1
  2  USING testmerge2 t2
  3  ON (t1.col1 = t2.col1)
  4  WHEN MATCHED THEN
  5  UPDATE SET t1.col2 = t2.col2
  6  WHEN NOT MATCHED THEN
  7  insert(col1,col2) values (-99999999999,-99999999999);
USING testmerge2 t2
      *
ERROR at line 2:
ORA-20000: Your error message
ORA-06512: at "PHIL.TESTMERGEERROR_BTRIG", line 4
ORA-04088: error during execution of trigger 'PHIL.TESTMERGEERROR_BTRIG'

SQL>


According to the SQL Standard, numeric values may only be compared with numeric values e.g. an INTEGER value can be compared with a NUMERIC value by coercing both to NUMERIC values.

It sounds like MySQL is not compliant with Standards in this regard. To be fair, most SQL product exhibit similar implicit type coercions and it is encumbent on users to avoid them (in contrast to a strongly type 3GL whose users might expect a compile error!)


Efficient manual cleanup of foreign keys in MySQL I have an all-InnoDB database in MySQL (Percona) 5.6 that was previously using FK constraints. I had to give up on them as I kept running into locking issues, so I removed the constraints and added some manual cleanup maintenance scripts instead. These queries are causing problems as they appear to be very inefficient, so I'm wondering how they can be made to work more efficiently.
A typical arrangement is this:
t1
id, field1
t2
id, t1_id, field1
t1_id in t2 is a foreign key pointing at the id field of t1. Each table has id set as its primary key, and t2 has a regular index on t1_id.
So, instead of a foreign key constraint dealing with cascade deleting records in t2 when a parent record in t1 is deleted, I have a cleanup query like this:
DELETE t2.* FROM t2 LEFT JOIN t1 ON t1.id = t2.t1_id WHERE t2.t1_id IS NOT NULL AND t1.id IS NULL;

in other words, delete any record in t2 that points at a non-existent record in t1.
That works, but it's really slow. Here's the explain for it:
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows    | filtered | Extra                                |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+
|  1 | SIMPLE      | t2    | range  | t1_id         | t1_id   | 5       | NULL          | 2392097 |   100.00 | Using where                          |
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY       | PRIMARY | 4       | mydb.t2.t1_id |       1 |   100.00 | Using where; Not exists; Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------+---------+----------+--------------------------------------+

The obvious problem here is that the row count is very high, and other questions I've seen on this subject suggest the range match type is at the root of the problem. If I omit the check for NOT NULL on t1_id, it matches the whole of the t2 table, making the row count even higher.
Because this is a single query, it tends to lock everything in t2 until it's complete, holding up other queries. I tried replacing it with separate select and delete, but it doesn't help much.
How can this be done better?

Try batching the deletes, so that you are only checking a small set of records at a time, and so are locking a smaller set of records.  You can play around with increase or decreasing the size of @batchSize until you find the "magic number" that performs best for you.  Note: I don't normally work in MySQL so my while loop syntax may not be 100% correct, but the general idea is there.

DECLARE @i int;
DECLARE @batchSize int;
DECLARE @maxKey int;

SET @i = 0;
SET @batchSize = 2000;
SET @maxKey = (select max(id) from t2 where t2.tl_id IS NOT NULL);

WHILE (@i < @maxKey) DO
BEGIN
    DELETE t2
    FROM t2
    LEFT JOIN t1 ON t2.t1_id = t1.id
    WHERE t2.id >= @i and t2.id <= @i + @batchSize
        AND t2.t1_id is not null
        AND t1.ID is null
    ;

    SET @i = @i + @batchSize;
END WHILE


What's the differences between utf8_general_ci and utf8_unicode_ci and utf8_binary collation in MySQL? I'm can't find the documentation of MySQL on this topic. Anyone can give some explanations please?

According to the MySQL Documentation

  Collations have these general characteristics:

  Two different character sets cannot have the same collation.

  Each character set has one collation that is the default collation.
  For example, the default collation for latin1 is latin1_swedish_ci.
  The output for SHOW CHARACTER SET indicates which collation is the
  default for each displayed character set.

  There is a convention for collation names: They start with the name of
  the character set with which they are associated, they usually include
  a language name, and they end with _ci (case insensitive), _cs (case
  sensitive), or _bin (binary).

  In cases where a character set has multiple collations, it might not
  be clear which collation is most suitable for a given application. To
  avoid choosing the wrong collation, it can be helpful to perform some
  comparisons with representative data values to make sure that a given
  collation sorts values the way you expect.

StackOverflow has a list of questions tagged utf-8 and collation

ServerFault only has one tagged utf-8 and collation

There is a website called efreedom.com that has links all around StackOverflow concerning utf8 : http://efreedom.com/Question/1-4784168/Change-Collation-Utf8-Bin-One-Go

Here is another site about collations as its place in the MySQL World : http://www.collation-charts.org/

Here is a link explaining binary collations : http://dev.mysql.com/doc/refman/5.0/en/charset-binary-collations.html


Get trigger query using query analyzer I have a server in which there is no SSMS installation, and only query analyzer is available. And all I need to do is find the trigger information (basically trying to find out what that trigger does).

I tried using object explorer in query analyzer, to use "Alter To->New window" command. But that command failed saying "Use SSMS to alter the query".

Is there a way, I can write a simple sql query, that gives me the trigger information?

Never mind. Found it.

sp_helptext 'TriggerName'

worked like a charm.


same field in bothe "where" and "order by" - how to index for this query? The query:

EXPLAIN  EXTENDED
SELECT *, u.user_id
FROM xgate_geoloc g
  JOIN tig_users u
  ON g.uid = u.uid
WHERE
         -- corners selection:
          ( ( (lat BETWEEN COALESCE(?, lat) and COALESCE(?, lat))
          AND (lat NOT BETWEEN COALESCE(?, lat) and COALESCE(?, lat))
            )
         OR ( (lon BETWEEN COALESCE(?, lon) and COALESCE(?, lon))
          AND (lon NOT BETWEEN COALESCE(?, lon) and COALESCE(?, lon))
            )
          )
     AND sex = COALESCE(?, sex)
     AND language = COALESCE(?, language)
     AND city = COALESCE(?, city)
     AND province = COALESCE(?, province)
     AND country = COALESCE(?, country)
     AND time BETWEEN COALESCE(?, time) and COALESCE(?, time)
ORDER BY time DESC

does that "OR" in the where expression have any effect on te index I should be using ?
'time' field is used in both where and order by. Any way to forge an index in this scenario ?
order by time desc - descending order; yet another bummer

Thanks for the input!

The OR will most likely affect the index usage, because it hinders index seeking. Generally speaking "OR" is 'bad' for index usage in where clauses.

The time field, it would be sensible to have it in an index sorted in the DESC order when you use it DESC;

Other than that - we'll need more information to give more exact/concrete advice


How can I connect my other table using the mysqli_insert_id? I've got a problem like this , I cannot get the id of the parent table .

table_name : transaction_tbl

- transaction_id
-file_name
-file_path
-description

table_name : transaction_details
-details_id
-transaction_id
- details

here is my code to insert :

 $sql = "INSERT INTO transaction_tbl (`file_name`,`file_path`,`description`)           VALUE('$file_name','$file_path','$description') " ;

                    $query = $conn->query($sql);
                    $transaction_id = mysqli_insert_id($conn);
                if ($query === True ){
                    $sql = "INSERT INTO transaction_details (`transaction_id`,`details`) VALUES ($transaction_id,$details) ";

                }else {
                    trigger_error('Wrong SQL: ' . $sql . 'Error: ' . $conn->error, E_USER_ERROR);
                }

Now I can insert in my transaction_tbl but not in transaction_details. what should I do? Can somebody help me?

There is an error in line:

$sql = "INSERT INTO transaction_details (`edi_transaction_id`,`details`) VALUES ($edi_transaction_id,$details) ";

This should be:

$sql = "INSERT INTO transaction_details (`transaction_id`,`details`) VALUES ($edi_transaction_id,$details) ";

Finally in your code you assign the text of the command to the variable $sql, but do not execute it.


What determines the maximum number of concurrent connection to SQL Server 2016 Express After some googling, there seems to not have a straight forward answer to the question. MSDN Forum mentions it depends on CPU, Memory usage, database size etc. Could someone help explain or perhaps suggest an example to show why there are so many factors limiting the number of concurrent connection. Also how could one ever find out the maximum connection of an application?

So are so many factors because there are so many different types of loads.  When it get loaded the only defense mechanism is to deny connections.

I you are looking for max connection you are looking for the wrong thing.

Design your application to be scalable - acquire the connection as late as possible and release the connection as soon as possible.  A well designed application could have 100 active users and never have more than 10 active connections.