JOIN 2 Tables on 1 Table and Select their separate quantity Is there any other way i can display the quantity field i have from 2 different tables where their ID's are referenced on 1 table.

Example:

shipment_box table, columns: shipment_box_id, box_code, quantity, date_created

shipment_consumable table, columns: shipment_consumable_id, shipment_box_id, item_id, date_added

shipment_unit table, columns: shipment_unit_id, shipment_box_id, item_id, date_added

The requirements would be:

The user must be able to create a shipment box.
The created shipment box must be able to accept a mix of unit and consumable items.
The report generated must display all shipment box's contents and quantity per item_code.

Below would be a visual representation of my database tables:

my initial idea was to create a separate SELECT Query for shipment_unit and shipment_consumable joining to shipment_box but if there's a way i can do this around without having to UNION these two queries, it would be more ideal.

below is my draft query (not tested yet):

SELECT
    sb.box_code,
    i.item_code,
    COUNT(su.shipment_unit_id) 'Qty'
FROM
    shipment_box sb
JOIN
    shipment_unit su
ON
    su.shipment_box_id = sb.shipment_box_id
JOIN
    item i
ON
    i.item_id = su.item_id
GROUP BY
    sb.box_code,
    i.item_code

UNION

SELECT
    sb.box_code,
    i.item_code,
    COUNT(sc.shipment_unit_id) 'Qty'
FROM
    shipment_box sb
JOIN
    shipment_consumble sc
ON
    sc.shipment_box_id = sb.shipment_box_id
JOIN
    item i
ON
    i.item_id = sc.item_id
GROUP BY
    sb.box_code,
    i.item_code

i was thinking of something similar to a hypothetical query like below:

SELECT
    sb.box_code,
    i.item_code, -- Display item_code from joined item table
    IF(COUNT(su.shipment_unit_id) = 0, COUNT(sc.shipment_consumable_id)) 'Qty' -- Display quantity of shipment_unit and shipment_consumable items
FROM
    shipment_box sb
JOIN
    shipment_unit su
ON
    su.shipment_box_id = sb.shipment_box_id
JOIN
    shipment_consumble sc
ON
    sc.shipment_box_id = sb.shipment_box_id
JOIN
    item i
ON
    (
        i.item_id = su.item_id
    OR
        i.item_id = sc.item_id
    )
GROUP BY
    sb.box_code,
    i.item_code

i am open with a lot of ideas that can help me please. Thank you.

First, let's sum up a few assumptions:

It appears that a given item cannot be both a consumable and a shipment unit, unless item_code is not a unique value in item.
The shipment_unit table is a simple list of all "unit" items in all shipment boxes; shipment_consumable is (similarly) a list of all "consumables" in all shipment boxes.
Both shipment_unit and shipment_consumable have no quantity column of their own; so, if a given shipment box was supposed to have (say) 1 Windows phone and two wired headsets, you would have to have two rows in shipment_consumable for the box, with the same shipment_box_id and item_id.
You are looking for the items and quantities in 1 instance of a shipment_box, rather than the total items and quantities in the total quantity of the shipment_box. That is to say, if shipment_box.quantity is 17, and the box contains 2 Windows phones, you want 2 as the count, not 34 (2 * 17).

First - I must say that there appears to be absolutely no reason to have both shipment_unit and shipment_consumable. The tables appear to have the exact same structure, and foreign keys to the exact same other tables (shipment_box and item). You know whether a given item is consumable or a "unit" form the item table, so there's no good reason I can see to separate the two things from each other. I feel obligated to point this out, as I suspect your design would be much simpler with just one table (shipment_content, perhaps).

Similarly, having a quantity field for each item in a shipment_unit (or _consumable, or _content) seems like an obvious plus; barring different values for date_added, there's no good reason to have:

 shipment_unit_id | shipment_box_id | item_id |   date_added
------------------+-----------------+---------+------------------
         7        |        4        |    1    | 2017-10-11 00:00
         8        |        4        |    1    | 2017-10-11 00:00

in order to get two Windows phone in the same shipment_box. This may not seem likely at this point, but to me it seems like a trivially predictable need for the future. That said, some would say if you don't need it as part of the current plans, leave it out rather than cluttering your design with what could become a bunch of unused fields.

That said, we don't always have the option to choose certain elements of the design - you may be stuck with this table layout, through no fault of you own.

So, if this is how it all works, and if it's how it must work, how do you get the info you need? I think your draft query would work accurately, and your JOIN query would not (except for degenerate cases, where there's 0 or 1 rows for a given shipment_box_id in each of the other two tables).

As written, there's one potential issue with your JOIN query - it may not be relevant, but it should be noted. You are performing an INNER JOIN to both ship_unit and ship_consumables. If a shipment_box only had "unit"-type items in it, then you would get back no data for it - because as written, there must be at least one "unit" and at least one consumable for a shipment_box to be displayed.

To avoid this, we should make the joins LEFT JOINs, so we get a shipment_box even if it's only got units or consumables, but not both (Note that by doing this, we'll also get a shipment_box that has no items attached. If necessary, we can filter those out later.)

The next thing to understand is how joins work. Each row in the underlying result set will have the fields from shipment_unit, and the fields from shipment_consumable. If you'll always have at most 1 row from each table, things will work as you expect; otherwise, they won't.

Let's posit a shipment_box (shipment_box_id = 5, box_code = 'BX-255', quantity = 10) with the following contents:

1 Windows phone (unit)
1 wired headset (consumable)
1 wireless headset (consumable)

So, shipment_unit has:

 shipment_unit_id | shipment_box_id | item_id |   date_added
------------------+-----------------+---------+------------------
         10       |        5        |    2    | 2017-10-12 00:00

and shipment_consumable has:

 shipment_consumable_id | shipment_box_id | item_id |   date_added
------------------------+-----------------+---------+------------------
           14           |        5        |    3    | 2017-10-12 00:00
           15           |        5        |    4    | 2017-10-12 00:00

If we join shipment_box, shipment_unit, and shipment_consumable as you indicate, we get the following (I've left out the shipment_box columns, and the shipment_box_id and date_added from the other two tables, for readability):

 shipment_unit_id | su_item_id | shipment_consumable_id | sc_item_id
------------------+------------+------------------------+------------
         10       |     2      |           14           |     3
         10       |     2      |           15           |     4

Basically, since there's no criteria to tie records from shipment_unit and shipment_consumable together, we get the cross-product of the records from each of those table that match our current shipment-box.

When we go to match item rows to what weve already got, we see that each row will match two items - the shipment_unit item, and the shipment_consumable item. So, with item_code attached, we've got this:

 shipment_unit_id | su_item_id | shipment_consumable_id | sc_item_id | item_code
------------------+------------+------------------------+------------+-----------
         10       |     2      |           14           |     3      |  ITM-002
         10       |     2      |           14           |     3      |  ITM-003
         10       |     2      |           15           |     4      |  ITM-002
         10       |     2      |           15           |     4      |  ITM-004

So, when we group by item_code there are two rows with 'ITM-002', and we'll get a count of 2 for that item (even though only one is in the shipment_box).

There are a couple of ways to work around this.

Since, as I noted above, there's really no reason to have separate shipment_unit and shipment_consumable table, you can use a union of the two tables within your existing query, instead of getting the UNION of two completely different queries:

SELECT
    sb.box_code,
    i.item_code,
    COUNT(*) `Qty` -- Display quantity of items
FROM
    shipment_box sb
JOIN
    (SELECT shipment_box_id, item_id FROM shipment_unit
     UNION ALL
     SELECT shipment_box_id, item_id FROM shipment_consumable
    ) AS box_item
ON
    sb.shipment_box_id = box_item.shipment_box_id
JOIN
    item i
ON
    box_item.item_id = i.item_id
GROUP BY
    sb.box_code,
    i.item_code
ORDER BY
    box_code,
    item_code
;

This way, in our example, the subquery would generate three rows - 1 from shipment_unit, and 2 from shipment_consumable.

Of course, that solution still includes a UNION. So, if you don't like that, then we can ignore counting the items from our result set altogether, and make simple separate queries to get the counts:

SELECT
    sb.box_code,
    i.item_code, -- Display item_code from joined item table
    (SELECT COUNT(*)
       FROM shipment_unit su2 INNER JOIN item i2 ON (su2.item_id = i2.item_id)
      WHERE su2.shipment_box_id = sb.shipment_box_id
        AND i2.item_code = i.item_code
    ) +
    (SELECT COUNT(*)
       FROM shipment_consumable sc2 INNER JOIN item i2 ON (sc2.item_id = i2.item_id)
      WHERE sc2.shipment_box_id = sb.shipment_box_id
        AND i2.item_code = i.item_code
    ) as `Qty`
FROM
    shipment_box sb
LEFT JOIN
    shipment_unit su
ON
    su.shipment_box_id = sb.shipment_box_id
LEFT JOIN
    shipment_consumable sc
ON
    sc.shipment_box_id = sb.shipment_box_id
LEFT JOIN
    item i
ON
    (
        i.item_id = su.item_id
    OR
        i.item_id = sc.item_id
    )
GROUP BY
    sb.box_code,
    sb.shipment_box_id,
    i.item_code
ORDER BY
    box_code,
    item_code
;

Note that we have to add sb.shipment_box_id to the GROUP BY, since we're using that for the SELECT COUNT(*) subqueries.

This is making a number of allowances:

It will work even if item_code is not unique (for instance, if you have item_id 27 that's a unit, and item_id 31 that's a consumable, but (since they're actually the same thing) both rows have item_code 'ITM-027')
It should work if the above situation is true, and (for some bizarre reason) you've got a box with item_id 27 in shipment_unit, AND item_id 31 in shipment_consumable. Since it's counting the occurences in both tables regardless, it would correctly generate a count of 2 for item_code 'ITM-027'

If (as I would tend to assume) every item_code has one and only one item_id, We can avoid the extra SELECT COUNT(*) queries altogether.

SELECT
    sb.box_code,
    i.item_code, -- Display item_code from joined item table
    CASE i.item_type
      WHEN 1
        THEN COUNT(DISTINCT su.shipment_unit_id)
      WHEN 2
        THEN COUNT(DISTINCT sc.shipment_consumable_id)
      ELSE NULL
    END as `Qty`
FROM
    shipment_box sb
LEFT JOIN
    shipment_unit su
ON
    su.shipment_box_id = sb.shipment_box_id
LEFT JOIN
    shipment_consumable sc
ON
    sc.shipment_box_id = sb.shipment_box_id
LEFT JOIN
    item i
ON
    (
        i.item_id = su.item_id
    OR
        i.item_id = sc.item_id
    )
GROUP BY
    sb.box_code,
    i.item_code,
    i.item_type
ORDER BY
    box_code,
    item_code
;

Here, we use i.item_type' to determine what value toCOUNT, so we need that in theGROUP BY`.

We're back to basing our COUNT on our result set. As noted before, we could see our incidence of unit or consumable items multiplied through the JOIN. However, we're not counting the rows directly; we're counting the number of distinct IDs from shipment_unit or shipment_consumable. This factors those multiple rows right back out again, and we'll get correct counts.

Finally, here's a DB-Fiddle link showing your two queries (tweaked slightly so the work), and my three, in action.

I'd recommend my 2nd query (COUNT via sub-queries) if item_code is not unique, and my 3rd (COUNT DISTINCT) is it is unique.


full text phrase-search with stop word I would like SQL Server to search a phrase "centos 8" for example. The exact match.
But since number "8" is considered a stop word, it only searches for "centos".
Even if I use quotes
 select [KEY] FROM CONTAINSTABLE(myTable, myColumn, '"centos 8"')

How can I make it search for a phrase without disabling the stop words feature completely? (very useful in other cases)

You don't need to disable the STOPWORDS / STOPLIST feature completely, rather you can just remove that specific word "8" from it if you want the search to always be able to match it.
Microsoft's Docs ALTER FULLTEXT STOPLIST explain how to remove words from the default STOPLIST. And for general information on managing STOPLISTS you can read there docs Configure and Manage Stopwords and Stoplists for Full-Text Search as well.


Why have both a postmaster and postgres executable? It's a little confusing how you have several executables to start postgresql, e.g. postmaster, postgres, pg_ctl.

postmaster -D /usr/local/pgsql/data
postgres -D /usr/local/pgsql/data
pg_ctl -D /usr/local/pgsql/data

I know that pg_ctl is designed to simplify the process of start postgres, but postmaster and postgres appear to be the same exact binary. It appears that postmaster is a symlink to postgres. What benefit comes from doing this?

The postmaster symlink appears to be historical. I didn't even know it existed until you pointed it out.

The other two are a typical pattern for UNIX daemon applications. postgres provides the server functionality, and pg_ctl provides control client for it.

It would be possible to bundle pg_ctl's functionality into postgres, but doing so would mean postgres would have to run in two modes: as a DB server, or as a client to connect to a running DB server. That's a little bit unpleasant architecturally, and would add what's essentially "dead code" (unreachable and unusable) to running PostgreSQL server instances.

Similarly, it'd be possible to remove the ability to launch a PostgreSQL server from the postgres executable and require you to do it via pg_ctl, but that'd be cumbersome, and would really annoy people who manage distribution init scripts. They usually want to bypass application startup programs and launch the daemon directly, so they can prevent it from detaching to run in the background, trap exit codes, capture stderr, etc.

Essentially: postgres is the low level server program. pg_ctl is a user tool for starting/stopping the server and other management actions. postmaster is an obsolete alias for postgres.

In general you use distribution service management (service, systemctl, update-rc.d, etc) if you installed PostgreSQL from packages, or pg_ctl if you installed from source or 3rd party binary installers that don't integrate with the operating system services system.


Continuous jobs on Redis I want to setup a script that sums up a bunch of values in a list and then puts down the result in total-count and then removes the entire list, And this all should be repeated once a minute or as soon as the script completes. Is it possible to make a script that does this?

Redis doesn't do scheduling of jobs/scripts - you'll have to use something (e.g. cron) to trigger the script.

The script itself sounds simple enough - LRANGE should be used to iterate the list's elements, sum them up, SET total-count to the result and DEL the list. Lua would be perfect for this but you can also .sh with redis-cli for example.

What bothers me, however, is that LRANGE would be O(N) and you want to run this all the time... that means your Redis will be busy doing this and all that just for an aggregate? Perhaps you should consider an alternative that will save all the work (from Redis) and the need to schedule a script. One possible alternative, for example, would be to add the list's members directly to the total-count key, possibly even without storing them in the list in the first place - unless I missed something, this appears a simpler solution to you request.


Generate test data distrubution in Oracle DB What I want to do is to generate test data as follows

table
t1 (id number, colA date, colB number).
Where
"id" is unique,
"colA" is a date incremented every 15 minutes,
"colB" is a number

Condition: value from colA should be repeated random n times.
This is what I've managed to come up with so far:

    SELECT rownum id, TO_DATE('01-MAY-2015') + ROWNUM/24/4 - 1 AS colA, trunc(100*exp(dbms_random.normal)) AS colB
    FROM (
        SELECT ROWNUM FROM (
            SELECT 1 FROM DUAL
            CONNECT BY LEVEL <= (TO_DATE('01-SEP-2015') - TO_DATE('01-MAY-2015'))*24*4
        )
    );

And how it should look like

Example:

ID      colA                    colB
=====   ====================    =====
11089   23-AUG-2015 12:15:00    75667
11090   23-AUG-2015 12:15:00    64552
11091   23-AUG-2015 12:15:00    26633
11092   23-AUG-2015 12:15:00    86463
11093   23-AUG-2015 12:30:00    77364
11094   23-AUG-2015 12:30:00    2233
11095   23-AUG-2015 12:30:00    32213
11096   23-AUG-2015 12:45:00    73112
11097   23-AUG-2015 13:00:00    1238
11098   23-AUG-2015 13:00:00    8222


Building MySQL InnoDB clustered indexes Assuming that you have a table like

CREATE TABLE `t1` (
    `search_id` int(11) NOT NULL,
    `provider_id` bigint(20) unsigned NOT NULL,
    `status` text NOT NULL,
    `imported_at` int(11) NOT NULL,
    `created_at` int(11) NOT NULL,
    `latitude` double DEFAULT NULL,
    `longitude` double DEFAULT NULL,
    `moderation` tinyint(3) NOT NULL DEFAULT '0',
    PRIMARY KEY (`search_id`,`provider_id`),
    KEY `k_search_imported` (`search_id`,`imported_at`),
    KEY `k_scm` (`search_id`,`created_at`,`moderation`),
    KEY `k_sc` (`search_id`,`created_at`)
) ENGINE=InnoDB;

What I'm unsure about is why

SELECT COUNT(1) FROM t1;

Takes 19 minutes (I've populated the table with 1.2 million rows).

MariaDB [test] 10:53:58> show indexes from t1;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY           |            1 | search_id   | A         |       13478 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          0 | PRIMARY           |            2 | provider_id | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            1 | search_id   | A         |          30 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_search_imported |            2 | imported_at | A         |       46213 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            1 | search_id   | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_scm             |            3 | moderation  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            1 | search_id   | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | k_sc              |            2 | created_at  | A         |      646982 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0.00 sec)

I wonder if the problem here is the cardinality. My question is should InnoDB indexes be ordered by the higher cardinality columns closer to the the start of the index, i.e. PRIMARY KEY (provider_id,search_id); or does it even matter?

So as @RickJames, suggested in one of the comments, cardinality has little to do with order of indexes. Therefore if your query is similar to

SELECT id, search_id, provider_id FROM t1 WHERE search_id=123 AND provider_id=567

Then the index should be ordered search_id,provider_id. If however you are running a more range based query.

  For efficiency, the "=constant" column should come before the "range"
  column in the INDEX

That means whenever you have a query as follows.

SELECT id, search_id, provider_id FROM t1 WHERE search_id>123 AND
provider_id<567 AND columnX=1

Then your index should be created as follows columnX,search_id,provider_id.


What is a common way to save 'debit' and 'credit' information? I'm working on an accounting system, and for each transaction I need to save if this is either debit or credit. I can think of two ways (MySQL database):

METHOD 1

Amount (decimal)
Type (enum, debit/credit)

METHOD 2

Debit (decimal)
Credit

In the first setup, I save the type of transaction, but in the second way I rather save the amounts in the debit or credit column. Pros of this method are that I can more easily sum both debit and credit totals than in method 1. But I am wondering if there is a common way to do this?

Another way to do this (no idea if this is common or not!):

create table `transactions` (
  ... some columns ...
  amount decimal(10,2),
  ttype int,
  foreign key (ttype) references transactiontypes (id)
  ...
);

create table transactiontypes (
  id int primary key,
  description varchar(30),
  multiplier int
);

And then in transactiontypes you have:

select * from transactiontypes;

+----+---------------+------------+
| id | description   | multiplier |
+----+---------------+------------+
|  1 | deposit       |          1 |
|  2 | withdrawal    |         -1 |
+----+---------------+------------+

And possibly more rows.

Then all amounts in transactions will be positive, and a join to transactiontypes + multiplication by multiplier gets the "real" (positive/negative) amount.


Create INSERT INTO from SELECT in PostgreSQL How to create INSERT INTO statements, using SELECT statement? I need it because I want to delete 10k of records from a table, but if someday some of that record need to be restored I want to do that easily. I don't want to backup the whole table, because I just need to restore some of deleted rows.

You can use pg_dump with the --inserts option. You should also check the manual of your SQL client. Some can export data as INSERT statements.

Another option is to just copy the rows that you delete into a new table:

create table _backup
as
with deleted as (
  delete from the_table
  where .... --<< put your condition here
  returning *
)
select  *
from deleted;

The above will delete rows from the_table and all rows that are deleted are copied into the table _backup


DB2 Query Error SQL0204N Even With The Schema Defined I'm using Python to access tables in DB2 10.1.0

I have a login account named foobar and a schema with the same name. I have a table named users under the schema.

When I'm logged in as foobar, I can run the following query successfully from the command line:

select * from users

I have a small Python script that I'm using to connect to the database.  The script is:

#!/usr/bin/python

import pyodbc

if  __name__ == "__main__":

    accessString ="DRIVER={DB2};DATABASE=MYDATABASE;SERVER=localhost;UID=foobar; PWD=foobarish1;CURRENTSCHEMA=FOOBAR"
    print accessString
    cnxn = pyodbc.connect(accessString , autocommit=True)
    cursor = cnxn.cursor()
    query = "SELECT * FROM USERS"
    cursor.execute(query)
    rows = cursor.fetchall()
    for row in rows:
        print 'Row data'
        print row[0]
    cursor.close()
    cnxn.close()

When I run the script, I get the following error:

  ('42S02', '[42S02] [IBM][CLI Driver][DB2/LINUXX8664] SQL0204N  "FOOBAR.USERS" is an undefined name.  SQLSTATE=42704\n (-204) (SQLExecDirectW)')

This usually means that the schema isn't defined.  However, if I change the query in the script to:

VALUES CURRENT SCHEMA

the script runs successfully and it returns

FOOBAR

I've also tried adding the schema directly to the table name, making the query

SELECT * FROM FOOBAR.USERS

and I still get the same error.

Does anyone know how to fix this so I can query the user table?  Your assistance and insight is appreciated.

Maybe there is a problem with upper and lower case. I created a table using the following statement:

CREATE TABLE "FOOBAR"."Users"  (col1 INTEGER)

If I try try to run on of the following statements at the CLP I get an error SQL -204

db2 "select * from db2root1.Users"

db2 "select * from db2root1.USERS"

db2 "select * from db2root1.users"

Always the error message  SQL0204N  "DB2ROOT1.USERS" is an undefined name.  SQLSTATE=42704 is returned.

I would suggest to run the following statement and check the spelling of the table name.

select t.tabname from syscat.tables t where lower(t.tabname) like 'user%'

Some tools are creating the tables with a case sensitive table name. This may result in an SQL -204 in some scenarios.

In this case drop and recreate the table without any quotation marks.


Maintenance plan to backup differentials hourly each to a new file name in SQL Server 2012 In addition to a nightly full backup, I'd like to set up an hourly differential backup maintenance plan in SQL Server 2012, where each differential backup file should receive a different name. It seems this isn't possible. Am I overlooking something simple?

In the GUI interface, my choices are either to append to the target differential backup file, or to overwrite it. Is there a way to append  _{YEAR}{MO}{DY}{HH} to the differential filename?

You should use Ola's backup solution. Brad talks about it in more details on simple talk.

The solution is very simple to implement and its tested by many organizations.

  Value  Description

  FULL   Full backup

  DIFF   Differential backup

  LOG        Transaction log backup


How to create oracle linked server in oracle server itself? Is it possible to create oracle linked server in other oracle server? I actually wanted to do this with sql server but in sql server I couldn't run long query (8000 limit).

  I have access to remote database but only read only

  I can't create views and stored procedure.

  I want to create linked server and with it's help create views in my
  local oracle database.
  Is this feasible and is the speed of query
   execution comparable?

The concept you are looking for on the Oracle side is a Database Link.  See this introductory information in the Concepts Guide.  Then follow the link at the end of the section to the Administrators Guide where you can learn more about it.  You will also want to look at the Database Gateway documentation for the OS you are on, specifically the section on Configuring Oracle Database Gateway for ODBC which will explain steps need to prepare the system for non-Oracle Database Links.

To answer your concerns...

Read only access is fine as long as you don't want to change the remote database.
Not being able to create remote views or stored procedures won't be a problem.  If you create Materialized Views then a remote Materialized View Log may be useful, but not necessary.
Using database links on the Oracle side you can create views of remote tables or even join local data with remote data.  Yes, the speed is comparable. It won't be as fast since the data has to flow over the network, and sometimes the query plan won't be ideal, but these may not be issues depending on your situation.

Here are the steps you will need to perform:

Create a system DSN.
Create an appropriate ORACLE_HOME\hs\admin\ .ora file.
Add an appropriate ORACLE_HOME\network\admin\listener.ora entry.
Add an appropriate ORACLE\network\admin\tnsnames.ora entry.
Reload the listener.
Create the database link.


SQL Server: How to create a table that fills one 8 KB page? Is there a way to calculate the amount of data that fills one 8 KB page in SQL Server and create a table that fills one page entirely? This kind of table is presented in in this blog post.

A row is maximum 8060 bytes but there is row overhead included.

CREATE TABLE dbo.FullPage1 (
    ID int NOT NULL,
    filler1 char(8000) NOT NULL,
    filler2 char(49) NOT NULL)
GO

If you change filler2 to char(50) it breaks with

  Creating or altering table 'FullPage1' failed because the minimum row size would be 8061, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

The 8060 is over all columns. There is no column overhead except the NULL bitmap (see later)

CREATE TABLE dbo.FullPage3 (
    ID int NOT NULL,
    filler1 char(2683) NOT NULL,
    filler2 char(2683) NOT NULL,
    filler3 char(2683) NOT NULL)
GO

Here, data is 8053 bytes with overhead of 2 bytes record type) + 2 bytes NULL bitmap pointer + 3 bytes NULL bitmap.

Even though we have no NULLable columns, the NULL bitmap exists. When we add a NULLable column, we don't increase the row size.

CREATE TABLE dbo.FullPage3 (
    ID int NOT NULL,
    filler1 char(8000) NOT NULL,
    filler2 char(49) NOT NULL,
    filler3 varchar(8000) NULL)
GO

This gives a warning of course

  Warning: The table "FullPage3" has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes.

The NULL bitmap is "one bit per column". Not just NULLable columns. Uncomment filler6v and it fails with "... including 8 bytes of internal overhead ..."

CREATE TABLE dbo.FullPage4 (
    ID int NOT NULL,
    filler1 char(8000) NOT NULL,
    filler2 char(49) NOT NULL,
    filler1v varchar(10) NULL,
    filler2v varchar(10) NULL,
    filler3v varchar(10) NULL,
    filler4v varchar(10) NULL,
    --filler6v varchar(10) NULL,
    filler5v varchar(10) NULL
    )
GO

For more info about on-disk structures, see Inside the Storage Engine: Anatomy of a record. Interestingly, where is the 4 byte row header? This appears excluded from the 8060 bytes.

Tested on SQL Server 2008 SP1 and SQL Server 2005 SP3


Why does dropping decimal columns not free up any space? I have several tables, each of which had a few columns of data type Decimal. I decided to drop these columns to free up some space.
I used the ALTER TABLE DROP COLUMN col1 command for the same.
However, I can not view any space being freed up. Why should it be so, since Decimal is not a variable length column and should free up space instantly?

PS. This is the query I'm using to track the amount of space available:

SELECT
b.groupname AS 'File Group',
Name,
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2)) [Currently Allocated Space (MB)],
CONVERT (Decimal(15,2),ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2)) AS [Space Used (MB)],
CONVERT (Decimal(15,2),ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS [Available Space (MB)],
'DBCC SHRINKFILE ('''+Name+''','+CAST(CONVERT (Decimal(15,2),ROUND((FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2)) AS VARCHAR(10))+')' QUERY
FROM dbo.SYSFILES a (NOLOCK)
JOIN SYSFILEGROUPS b (NOLOCK)
ON a.groupid = b.groupid
ORDER BY b.groupname

For a rowstore table you should not expect a drop column operation to free up any space. Data is stored on pages in row format. I'm oversimplifying a bit, but for most rowstore tables you'll end up with many rows stored on a single page.
Consider what would happen to the pages if you removed a column. Would you end up with any completely empty pages that could be returned to SQL Server? No, instead you'd get a bunch of small "holes" in the pages. SQL Server won't give you back any space unless you take a maintenance action on the table. For example, rebuilding the table will copy over all of the data into new pages. That should reduce the space needed by the table after dropping a column.
Rowstore demo
Here's a simple demo for rowstore in which dropping a column only frees up space after a REBUILD is completed on the table:
CREATE TABLE [dbo].[X_TBL_159269](
       [NUM] [int] NOT NULL,
       [COL1] decimal(18,0) NULL,
       [COL2] decimal(18,0) NULL,
       [COL3] decimal(18,0) NULL,
CONSTRAINT [PK_X_TBL_159269] PRIMARY KEY CLUSTERED
(
       [NUM] ASC
)
);

-- insert 1000000 rows
WITH
       L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
       L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
       L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
       L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
       L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
       L5 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L4 B),
       NUMS AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NUM FROM L5)
       INSERT INTO [X_TBL_159269] WITH (TABLOCK)
       SELECT TOP (1000000) NUM, NUM AS [COL1], NUM AS [COL2],  NUM AS [COL3]
       FROM NUMS
       ORDER BY NUM;

sp_spaceused 'X_TBL_159269';

/*
reserved    data    index_size  unused
39752 KB    39608 KB    80 KB   64 KB
*/

ALTER TABLE [X_TBL_159269] DROP COLUMN COL2;

sp_spaceused 'X_TBL_159269';

/*
reserved    data    index_size  unused
39752 KB    39608 KB    80 KB   64 KB
*/

-- rebuild clustered index to free up space
ALTER TABLE [X_TBL_159269] REBUILD;

sp_spaceused 'X_TBL_159269';

/*
reserved    data    index_size  unused
30728 KB    30656 KB    72 KB   0 KB
*/

Columnstore demo
For columnstore tables, the data is stored in column format. Dropping a column will immediately free up space:
DROP TABLE IF EXISTS [dbo].[X_TBL_159269];

CREATE TABLE [dbo].[X_TBL_159269](
       [NUM] [int] NOT NULL,
       [COL1] decimal(18,0) NULL,
       [COL2] decimal(18,0) NULL,
       [COL3] decimal(18,0) NULL,
       INDEX cci CLUSTERED COLUMNSTORE
);

-- insert 1000000 rows
WITH
       L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
       L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B),
       L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B),
       L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
       L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B),
       L5 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L4 B),
       NUMS AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NUM FROM L5)
       INSERT INTO [X_TBL_159269] WITH (TABLOCK)
       SELECT TOP (1000000) NUM, NUM AS [COL1], NUM AS [COL2],  NUM AS [COL3]
       FROM NUMS
       ORDER BY NUM
       OPTION (MAXDOP 1);

sp_spaceused 'X_TBL_159269';

/*
reserved  data
10760 KB  10672 KB
*/

ALTER TABLE [X_TBL_159269] DROP COLUMN COL2;

sp_spaceused 'X_TBL_159269';

/*
reserved  data
8200 KB   8008 KB
*/


SQL Server linked server from on premises to SQL Server instance on Windows in Amazon EC2 I am trying to create a SQL Server linked server from on premises to SQL Server instance on Windows in Amazon EC2 (2 different domains) and getting:

  Login timeout expired.

I am using sa account and password. It is the same on both instances.
I was wondering if it's even possible do to so? If yes, what could be the issue here?

Trying to find docs online but not able to do so.
Also, can the linked server be created from AWS EC2 to on premises and vice versa?

When you're using EC2, don't think of it as "a special version of SQL Server" but rather "the same SQL Server I'm used to, running in a special data center." There are no feature restrictions in SQL Server--you're limited only by the infrastructure that you build in the AWS cloud/data center.

You can absolutely create linked servers from on-prem to EC2, and vice-versa. You just have the same challenges as any time you create linked servers across data centers.

Authentication

You mention that AWS & on-Prem are in different domains. This will require that you either have a trust between the domains, or that you use SQL Server authentication.

You've already said you're solving this with SQL Auth, and the configuration of the linked server & auth is a whole separate question, so I'll not dive into those details here.

Networking

Ultimately, your servers need to be able to talk to each other. It sounds like that isn't happening. You mentioned that the error you are seeing is "Login timeout expired." This is a somewhat generic error, but I'm guessing the more verbose version of it is this:

  Login timeout expired.

  A network-related or instance-specific error
  has occurred while establishing a connection to SQL Server. Server is
  not found or not accessible. Check if instance name is correct and if
  SQL Server is configured to allow remote connections.

This full error indicates that your client (in this case, your client is your on-prem instance that is establishing the connection to the AWS instance) cannot reach the server. Presumably, both SQL Instances are online, healthy, and accepting connections. The issue could be a problem resolving the server name, or it could be that a firewall is blocking the communication, or some other network issue.

You will need to establish a (secure!!!) network route between your on-prem server and your EC2 instance.

From your on-prem server, open a PowerShell prompt, and use the Test-NetConnection cmdlet to confirm if your server can communicate with the EC2 instance on the SQL Server port.

In this example, I'm testing a connection to the machine blep.am2.co which is running a SQL Server instance running on the default port 1433:

PS C:\> Test-NetConnection blep.am2.co -port 1433
WARNING: TCP connect to (192.168.86.100 : 1433) failed

ComputerName           : blep.am2.co
RemoteAddress          : 192.168.86.100
RemotePort             : 1433
InterfaceAlias         : Wi-Fi
SourceAddress          : 192.168.86.226
PingSucceeded          : True
PingReplyDetails (RTT) : 20 ms
TcpTestSucceeded       : False

The test shows that the name was correctly resolved to an IP, that a ping test was successful (PingSucceeded: True), but that it was unable to connect on the specified TCP port (TcpTestSucceeded: False).

A successful ping indicates that there is a successful route to the server (that's good!); a failed ping could indicate that the server is not accessible at all (it is also possible to disable ping responses, so a failed ping is valuable data, but depending on your network/security configuration may not be conclusive).

The TCP Test failure indicates that the specific connection on port 1433 is failing. The combination of a successful ping, and failure on port 1433 indicates that the most likely issue is a firewall blocking the connection. You would need to check all firewalls along the network path between servers to identify which one is blocking the traffic. At minimum, you would likely need to check:

Outgoing connections on the Windows firewall for your on-prem server
Outgoing connections on the firewall for your on-prem data center
Incoming connections on your AWS network
Incoming connections on the Windows firewall for your EC2 server

Once you resolve the networking connectivity, and the Test-NetConnection is successful on your SQL Server port, you will be able to successfully create your linked server.

⚠Warning⚠

If you aren't familiar with networking & network security, I would suggest proceeding carefully when making any configuration changes to allow your on-prem & AWS servers to communicate. Misconfiguring the connections could create a vulnerability where either you allow network connections from unauthorized sources, or that you transmit data unencrypted across the internet, where it could be intercepted.


MySQL non-sequential primary key of certain length what's the best way to create a non-sequential primary key of a certain length in MySQL?

I'm looking to use the primary key as a 'Pin' number which real actual human people can use to refer to the object in the database to join a game. I'd like them to be non-sequential so it's not quite so easy to find other games which they aren't necessarily supposed to access.

I've added the criteria that they are a certain length, but I guess I really want them to be at least a certain length.

Could anyone point me in the right direction? I've looked at http://seventhfloor.whirlycott.com/2008/05/27/generating-primary-keys/ but it seems a bit over the top, and I wondered if there were a more straight forward way.

I would suggest that the "right direction" would be to go a different direction entirely.

A sufficiently random and difficult-to-guess distribution of keys, assuming that is even an achievable objective, is likely to be otherwise sub-optimal for space and performance.  But that's the second problem.

The first problem is that exposing the internals of your database in such a fashion is intrinsically unsafe.

Instead, create a separate column for this "PIN" -- of whatever desired length and data type, with a unique key constraint on the column so no more than one record can possibly have the same PIN associated with it.

You can then populate this value using whatever algorithm you want, and you can subsequently change the algorithm, change the min/max lengths of the value, etc., without any other impact on the rest of your application or database as a result of such changes.


How do I copy a schema for an instance of Oracle running in a Docker container? Pursuant to this question:
Is this package missing and if so, how do I fix?

...now I actually want to perform the copy of a schema using this package. ...except I can't figure out how to do that. I've seen this: How to create copy of full schema on same database in oracle but I can't seem to execute the commands. As mentioned in the previous question, we are running Oracle in a docker container, so this might be complicating matters. Again, I'm using DataGrip to connect. Since it's in a container, all of our connections are from remote. ...am I going to have to get into the container somehow? Docker is also something that is outside of my expertise.

I've tried executing:

expdp fromUser/fromPword schemas=sourceSchema directory=DUMPDIR dumpfile=dump.dmp logfile=explog.txt

...as well as...

exec expdp fromUser/fromPword schemas=sourceSchema directory=DUMPDIR dumpfile=dump.dmp logfile=explog.txt

I've also created the directory object DUMPDIR and granted read/write to it.

expdp is a command line utility that interacts with the DBMS_DATAPUMP API. You run it on the host OS, not from within another client.

https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_datpmp.htm#BABHBEHH

  See Also:
  Oracle Database Utilities for more information on the concepts behind the DBMS_DATAPUMP API, how it works, and how it is implemented in the Data Pump Export and Import utilities

https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_overview.htm#i1009203


How can I 'further' improve my table and queries This question is an extension of this question. Where Rick James kindly provided a list of things I should change in my table and query.

I also changed my application so that unused filters weren't included in the query at all.

Now my table looks like this:

CREATE TABLE `transaction_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txn_id` varchar(32) NOT NULL,
  `nationality_id` smallint(6) NOT NULL,
  `tdate` date NOT NULL,
  `destination` varchar(80) NOT NULL,
  `sku` int(11) NOT NULL,
  `sales` decimal(8,2) NOT NULL,
  `units` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tdate` (`tdate`),
  KEY `datenat` (`tdate`,`nationality_id`),
  KEY `dest` (`destination`),
  KEY `txnid` (`txn_id`),
  KEY `cov` (`sku`,`nationality_id`,`tdate`)
) ENGINE=InnoDB AUTO_INCREMENT=3236519 DEFAULT CHARSET=utf8;

Unforutnately, this actually increased the query time quite vastly (it took 100 seconds) and here is the explain. Note I have also removed the join on nationalities in the subquery as it was redundant.

Query

SELECT `products`.`brand`, COUNT(DISTINCT transaction_data.txn_id) AS numOrders
                                FROM `products`
                                INNER JOIN `transaction_data` ON `transaction_data`.sku=`products`.sku
                                INNER JOIN `nationalities` ON `transaction_data`.`nationality_id`=`nationalities`.`nationality_id`
                                INNER JOIN (SELECT DISTINCT `transaction_data`.`txn_id`
                                FROM `transaction_data`
                                INNER JOIN `products` USING (sku)
                                WHERE brand = '<brand>') AS tmp_txns ON tmp_txns.txn_id=`transaction_data`.txn_id
                                WHERE brand <> '<brand>' AND
                                 tdate >= '2014-01-01'
                                 AND tdate < '2014-01-01' + INTERVAL 1 YEAR
                                GROUP BY brand
                                ORDER BY numOrders DESC, brand ASC
                                LIMIT 10

I don't understand how it's using less rows but taking way longer.

I have also set my innodb_buffer_pool_size to a low "60M" (as per RickJames suggestion for a t2.micro amazon RDS).

Also, to add to previous.  Unfortunately setting up summary tables isn't a great idea. While it is correct that they re suitable for my use.  I can't guarantee that a user in the future won't try to add data either non-sequentially or even try to swap data out.

Edit

Further to Rick James' answer below

W.O.W - Hit the nail on the head with the date range in the inner query. I was basically running the wrong query the whole time (too many other =brand results).  It now runs in 2.4 seconds!

id = auto incremement, integer
txn_id = non-unique  'basket' identifier.

'1', '7662_20120101_PC  672_1_12:25:00',
'2', '7662_20120101_PC  672_1_12:25:00',
'3', '7662_20120101_PC  672_1_12:25:00',
'4', '7660_20120101_KL  867_1_12:23:00',
'5', '7662_20120101_PC  672_1_12:25:00',
'6', '7660_20120101_KL  867_1_12:23:00',
'7', '7658_20120101_KL  1211_1_12:12:0', // only sku in basket

You don't need the nationality in the outer query.  Remove it, then:

INDEX(tnx_id, sku)  -- instead of just INDEX(txn_id)

That will let both queries be 'covered', as to be indicated by Using index in the EXPLAIN.

Won't it be 'correct' to include the date range in the inner query, too?  That would beg for adding INDEX(sku, tdate)

I don't see why the outer join to products failed to use the index.  Please change suitable fields to NOT NULL and be consistent about datatypes.  Shrink the INTs where practical, and add UNSIGNED where appropriate.  But make sure they are consistent across JOINs.

The 60M was to get you away from "swapping".  Can you see how much RAM is still free, and that you are doing no swapping?  If the is still some RAM, give part of it to the buffer_pool.

Is there a 1:1 relationship between id and trx_id?  If so, there are several more changes to suggest.


OSQL RTM on SQL Server 2012 SP2? I have a situation that I've never seen.  OSQL crashed in a scheduled job which has always worked.  Windows event viewer shows an exception code: 0xc0000005.
the version of OSQL.exe also showing in the same Windows Event Viewer error message is 2011.110.2100.60. -->  this corresponds to the SQL Server 2012 RTM.

When running @@version of the SQL Server it states that I have SP2 installed.

Is this normal? do I need to upgrade OSQL to SP2? If so, how to upgrade OSQL to SP2?

OSQL Windows Event Viewer output:

    Faulting application name: OSQL.EXE, version: 2011.110.2100.60, time stamp: 0x4f35eaf3
Faulting module name: ntdll.dll, version: 6.3.9600.18696, time stamp: 0x59153753
Exception code: 0xc0000005
Fault offset: 0x00000000000546fb
Faulting process id: 0x1bdc
Faulting application start time: 0x01d31d465269cf58
Faulting application path: \\SFSMSSQL\c$\Program Files\Microsoft SQL Server\110\Tools\Binn\OSQL.EXE
Faulting module path: C:\Windows\SYSTEM32\ntdll.dll
Report Id: 5bb47a06-895c-11e7-80eb-005056b410f0
Faulting package full name:
Faulting package-relative application ID:

@@Version output:

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
                May 14 2014 18:34:29
                Copyright (c) Microsoft Corporation
                Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Added Note: I am aware that we should be using SQLCMD instead OSQL which will soon be depricated... it is in our road map. .

My research hasn't been fruitful.

Thank you

Faulting application path: \SFSMSSQL\c$\Program Files\Microsoft SQL Server\110\Tools\Binn\OSQL.EXE
Faulting module path: C:\Windows\SYSTEM32\ntdll.dll

You appear to be running osql.exe from another server via the c$ share.  If so, the local ntdll.dll is probably a different version to the osql.exe on the remote server.
Change the path
\\SFSMSSQL\c$\Program Files\Microsoft SQL Server\110\Tools\Binn\OSQL.EXE

to something like:
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\OSQL.EXE


Where does SYSTIMESTAMP come from in multi-server Oracle RAC environment? Where does SYSTIMESTAMP come from in multi-server Oracle RAC environment?

I'm hoping that the SYSTIMESTAMP derives from a common location (master node?) as opposed to the server that is currently servicing the request.

Additional Detail:

We are using a timestamp to ensure that entries in a log table are recorded sequentially. Say, for example, we have two nodes in our RAC and they are 1 second off. Our app server inserts two log entries within, say, 0.03 seconds of each other. The first insert is serviced by Node1 and the second by Node2. If there is one second difference between the two nodes, the timestamp could potentially show the log entries occurring in the wrong order! (I would just use a sequence, but our sequences are cached for performance reasons... )

Up to 11g I know Oracle used the local OS time as the system time in a RAC environment, and I never heard that it changed so I'm comfortable saying it's still in place.

You would sync all the servers together typically using NTP or another solution to ensure the time sync.  Usually if your servers local time is out of sync even by 30 seconds or so, you will tend to see other issues such as Kerberos auth issues so it's unlikely they are far off from each other.


Auto Increment in MS SQL letter and number I have one question, how to achieve auto increment in MS SQL. the one way i know so far is to perfix in the table eg

CREATE TABLE Customer (
    CUSId INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
    ,CUSKey AS 'Cus' + RIGHT('000' + CONVERT(VARCHAR(5), CUSId), 6) PERSISTED
    ,CusName VARCHAR(50)
    ,mobileno INT
    ,Gender VARCHAR(10)
    )

i will get something like

Cus0001
Cus0002

what after Cus9999 ?

some how can i achive auto increment like this

CUSAB000001
CUSAB000002
CUSAB000003
CUSAB000004
CUSAB000005
CUSAB000006
CUSAB000007
CUSAB000008
CUSAB999999
CUSCD000001
CUSYZ999999.

In order to increment groups starting at AA and ending at ZZ, you need to convert the left portion of the number (whatever is above the number of digits you want to keep as integers) into Base 26. You start by truncating the right portion of the number so that you have only the left portion, then you divide by 10 ^ IntegerDigits (e.g. 3 integer digits == 10 ^ 3 == 1000) to get the A_ side, then use modulo on the same 10 ^ IntegerDigits to get the _A side. Those values will get us the offset from the ASCII value for A. For example:

SELECT (123142 / 1000) AS [Truncated],
       (123142 / 1000) / 26 AS [SetsOf26],
       (123142 / 1000) % 26 AS [Remaining],
       CHAR(65) AS [A];

-- Truncated = 123
-- SetsOf26  = 4
-- Remaining = 19
-- A         = A

We can put all of that together in an Inline TVF (for demo purposes, not for using in a computed column in a table) as follows:

CREATE FUNCTION dbo.Base26 (@Base10 INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  WITH truncated AS
  (
    SELECT (@Base10 / 1000) AS [Value]
  )
  SELECT @Base10 AS [Actual],
    tr.Value AS [Truncated],
    CHAR(65 + (tr.Value / 26)) AS [1stChar],
    CHAR(65 + (tr.Value % 26)) AS [2ndChar],
    CHAR(65 + (tr.Value / 26))
      + CHAR(65 + (tr.Value % 26))
      + RIGHT('00' + CONVERT(VARCHAR(20), @Base10 % 1000), 3) AS [EndResult]
  FROM truncated tr;
GO

Then we can test with:

SELECT * FROM dbo.Base26(142);
SELECT * FROM dbo.Base26(3142);
SELECT * FROM dbo.Base26(123142);
SELECT * FROM dbo.Base26(123999);

which returns:

Actual   Truncated   1stChar   2ndChar   EndResult
142      0           A         A         AA142

3142     3           A         D         AD142

123142   123         E         T         ET142

123999   123         E         T         ET999

HOWEVER, I see absolutely no reason at all to implement this in your situation. There is no benefit in having a string "CusXXXXXX" value where "XXXXXX" is really just the IDENTITY value. If you were going to obfuscate the "XXXXXX" via a hash or Modular Multiplicative Inverse, then it might be ok, though I am still not sure you would want to store the "Cus" string portion of it. But in its current form, you get no benefit whatsoever from doing this. You are just wasting space in the DB.

Other notes:

You would be better off not abbreviating "Customer" to be "CUS" as a prefix for columns in this table. Just use the full table name, especially for the ID column: CustomerID, and CustomerName.
You should not use a numeric type (i.e. INT) to store phone numbers. Numbers that you do not do mathematical operations on should be stored as strings. This also applies to postal codes, Social Security Numbers (SSNs), etc. In the case of phone numbers, they often have extensions or other non-numeric options, such as prefixing with + if they are outside of the base country.
You should not use a string type (i.e. VARCHAR(10)) to store a repeated code / label such as "gender" (assuming that the full words "male" and "female" or similar are being stored, and that a non-binary / _BIN2 Collation is being used). You should have a "Gender" lookup table with a GenderID TINYINT column as the Primary Key (but not an IDENTITY). Then in the Customer table you would also have GenderID TINYINT but it would be a Foreign Key referencing dbo.Gender (GenderID).

OR:

You could use a CHAR(1) COLLATE Latin1_General_100_BIN2 NOT NULL column with a CHECK Constraint enforcing that only M and F are acceptable. This gets you a "code" that is human readable while still being as efficient with storage, memory, and comparisons as a TINYINT would be.


Returning only the row with maximum value from related rows In the example below how do I get the rows where the swap id is the maximum value in relation to the reservationId?   I am currently joining multiple tables to get customer, reservation, room etc information.  But I don't want rows with a swapnumber of 1 if a value is higher then that.

    DECLARE @RoomExchange TABLE (ID INT IDENTITY(1,1), reservationID int,
                     swapNumber int, oldRoom int, newRoom int)

INSERT INTO @RoomExchange (reservationID, swapNumber, oldRoom, newRoom)
SELECT 12, 1, 1,2   UNION ALL
SELECT 13, 1, 3,4   UNION ALL
SELECT 14, 1, 5,6   UNION ALL
SELECT 14, 2, 6,5   UNION ALL
SELECT 15, 1, 7,8   UNION ALL
SELECT 15, 2, 8,9   UNION ALL
SELECT 15, 3, 9,7   UNION ALL
SELECT 16, 1, 10,11

So above i only want rows where the ID would be

1, 2, 4, 7 and 8.

You can use window functions:

select id, reservationID swapNumber, oldRoom, newRoom
from (
    select id, reservationID swapNumber, oldRoom, newRoom
         , row_number() over (partition by reservationId
                              order by swapNumber desc) as rn
    from @RoomExchange
) as T
where rn = 1;

By reversing the sort order we can pick the first one for each partition.


SQL Server Management Studio cannot connect because of common algorithm I just installed SQL Server Management Studio, and I'm trying to connect to SQL Server 2014. It shows an error message "A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)"

My co-worker's SSMS connects with no issues, so I'm sure the server is working.

From what I've found so far, it's because of the TLS version issue, and I should upgrade SQL Server. Unfortunately, I don't control the server, just the client, and I have no idea when the DBA will get around to updating. I can't just wait around for them to find time to update.

I tried both SSMS 2016 version 13.0.16106.4 and SSMS v17.9.1 version 14.0.17289.0.

What can I do on my machine to make sure it connects to the server?

One issue that can cause this is if TLS 1.0 is disabled on the client as part of a security hardening process.  Since the version of SQL Server you are trying to connect to doesn't support a version of TLS that is enabled on your system, the connection fails.
If you can't upgrade SQL Server, the only solution is to enable TLS 1.0 on the system where SSMS is running.  This can be done by modifying the appropriate registry settings specified in Transport Layer Security (TLS) registry settings

Registry path: HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols
To enable the TLS 1.0 protocol, create an Enabled entry in either the Client or Server subkey as described in the following table. This entry does not exist in the registry by default. After you have created the entry, change the DWORD value to 1.


Filter on join condition and performance I have a query of the following type:
SELECT *
FROM CUSTOMER C
JOIN USERTABLE U on C.AccountmanagerID = US.ID

The user table also has external users which never can be the accountmanager. This table has about 30k records of which only about 500 users can be the accountmanager. I can filter them based on the length of their username. Which gives only 1k records back.
SELECT *
FROM CUSTOMER C
JOIN USERTABLE U on C.AccountmanagerID = US.ID
AND len(U.USERNAME) = 3

When i run the query plans the second one is way more efficient, which i can imagine, but I can't find any complete reasoning behind this. Can someone enlighten me and give me some background information. I am also wondering if this is always the case or is this just coincidence.
Link to the queryplan:
https://www.brentozar.com/pastetheplan/?id=HypDy3MnP
Thanks in advance

The difference is your second Execution Plan is using an Index Seek (which is typically a more performant operation) on your Customer table:

This is because your additional filter results in a cardinality that returns much less records and makes an Index Seek operation more applicable as opposed to an Index Scan. Notice the difference in thicknesses between the arrows in your first Execution Plan and the second one. There's a lot less rows being returned in the second plan because of your second query's JOIN predicate helping filter down the data upfront.
This also results in a more efficient operation being used to JOIN the two Tables together, a Nested Loops operator instead of Merge Join.
To answer your last question, this is neither a coincidence nor always guaranteed to be the case, Execution Plans and what the SQL Engine does under the hood is fairly complex, and therefor testing and comparing plans and results is the best way to understand what's going because speculating for a given scenario is difficult and varies from one scenario to the next.
In this case it just worked out that the filter you chose reduced enough records from the User table upfront, that the INNER JOIN to the Customer table was a lot smaller and allowed it to use an Index Seek operation which is more performant (and again also resulted in the JOIN operation to be something more efficient too, a Nested Loops operation).
You can see the reduction in records to JOIN to in your Customer table by looking at the "Estimated Number of Rows" (in this case, you should use the Actual Execution Plan instead of the Estimated Execution Plan when possible) property when you highlight over the Index Scan operation on "OSUSR_6VB_MSS_CUSTOMER" in the first Execution Plan vs the Index Seek operation in the second Execution Plan, went from 31,877 rows to approximately 472 rows:


With InnoDB, any secondary index (such as INDEX(created_id)) implicitly includes a copy of the PRIMARY KEY (id).  Hence, the first query is "covering" as indicated by "Using index".
When you need to look at more than about 20% of a table, it is actually faster to ignore the index, and do a full table scan -- rather than bouncing between the index and the data.  (The index and the data are separate BTree structures, connected by that PK copy.)
ORDER BY causes a filesort unless it can be consumed by the index it is using.  (But it is not using it, as the above point points out.)
MySQL rarely uses more than one index in a SELECT.  (Because using multiple indexes would be more complex, and almost always slower.)

That is just a few of the possible things that can go on (other than the items I just mentioned).

See also my Index Cookbook .


SQL 2008 R2 performance problems - mixed signals on disk performance I'm having no end of trouble with one of our production SQL boxes - users are experiencing massive drop offs in performance and this is having severe impacts on the business. I've been investigating mainly from a SQL/dba perspective and using sp_BlitzFirst I've been seeing intermittent reports of very slow read/write times (typically ~100-300ms but at points as high as 1000ms+).

The real head scratcher is that after getting our infrastructure guys involved they can't see anything amiss from their end.

The server is a virtual machine (Windows Server 2008 R2, SQL 2008 R2 Standard with 12 cores and 32Gb of RAM providing the databases for a Dynamics CRM 4.0 instance and another web application that is integrated with CRM) running under vmware with storage being provided by a Dell EqualLogic SAN and they are seeing times more in the 30-40ms range. Now it's entirely possible that the samples taken by BlitzFirst and vmware are simply not coinciding and causing the disparity but if not could there be any other explanation? I've used perfmon on the server and while the averages for the disk read/write are pretty reasonable I see definite spikes that are much higher then vmware is reporting - much closer to the figures that BlitzFirst is reporting. Is there anything else I should be looking at?

I'm reasonably certain that the problems BlitzFirst is reporting are real as they always occur at the same points the users experience major problems. To be honest the application performance of this CRM has always been pretty crap but even with a pretty low bar it's still falling well short.

Not wanting to be one of those OPs who just disappears without providing closure here's an update..

Despite receiving multiple assurances from infra that the vhost was in no way overloaded and that contention for physical resources couldn't possibly be the problem they eventually moved multiple VMs off the host to another one and the symptoms disappeared once that was complete. So either the problems were caused by resource contention on the host bottlenecking the VM's performance or the symptoms that just coincidentally looked like resource contention just so happened to coincidentally disappear at the same time that load on the vhost was reduced.

So many thanks to everyone for the helpful comments/pointers!


Copy data from multiple databases to a central database for reporting and analysis I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting and analysis purposes (a one way transfer).
Table structures are the same in all databases. Note that all my databases reside on the same server, except the central database, which is on a separate server. I have evaluated a couple of options. My current thinking and related questions are below:

SSIS
With SSIS I can do data transfer from the source to the destination databases; however, there is no change tracking. I have to track the changes myself and then extract and push the data manually. Apart from this I will end up creating a number of control flows and related data flows as the data sources are different which would be hard to manage in future.

Replication
Replication has change tracking, so the data transfer would be quick enough. However, I have to create those many publishers against each database. I am afraid server performance would be impacted due to this. I am also not sure which replication type to use, or if any performance issues could be handled with a better configuration. I am okay with a nightly update and data transfer need not be real time.

How should I proceed from here?

I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting and analysis purposes (a one way transfer).
Table structures are the same in all databases.

REPLICATION : If the objects (tables) you are replicating are not undergoing frequent schema changes then you can use many-to-one replication using T-Rep (this is a very thorough answer that I wrote with scripts and end result), provided you can slightly modify your table schema. This will give you a near-to-realtime data transfer to your central server.
SSIS : If you dont need a near-to-realtime data transfer, then you can look into SSIS - Incremental load using Lookup transform as described in Updating Rows in Incremental Loads – Level 4 of the Stairway to Integration Services. ALso, refer to this excellent series : Stairway to Integration Services for more details. Alternatively, you can test out  Change Data Capture (SSIS) (as @srutzky suggested).


Maximum memory setting in SQL Server I'm running SQL Server 2008 and a web based application, on a single dedicated server, with only 2Gb of memory available.

As is noted elsewhere, SQL Server regularly takes up to 98% of physical memory, which appears to slow down the web application running on the server.

In Server Properties in SSMS, under Memory, Maximum Server Memory (in Mb) is set to: 2147483647

My question is, what would be the recommended number to put in the maximum server memory box, given the amount of memory I have available, and that the same server is also running the web application?

Additionally, is it safe to make a change to this number, while SQL Server is running?

Thank you for your advice.

I will answer the last question first: Yes, you can change it while the server is running without issue. If you want to change the value via SQL you can do it with the following query

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

See this page for more details about setting memory on SQL server.

Your first question, unfortunately the answer is: I can't tell you, I'm not there.

There is a 1,000,000 things you need to factor in when allocating memory. How big are result sets from the queries, how often are they run, would a query that used to take 20 ms be ok to now take 200 ms?

Sql's defaults assumes that it is the only thing running on the server, so it just sets the memory to MAX_VALUE and it stops growing when all available memory is in use (and on dedicated hardware that is fairly close to what you want to happen(see Aarons comment for a possible caveat)). Normally any web server or other software interacting with the database would be on different hardware communicating to it over the network.

You really just need to just set it to a value you think is sane, and if your webserver is still memory choked lower it. If SQL is not giving you enough performance after you give the webserver the memory it needs you will need to either buy more ram or move the SQL to dedicated hardware.


Sql server/azure connection string parameter for readonly access? Is there a specific parameter that can be used in a SQL server/azure connection string to specify that the connection should be readonly?

In other words, only SELECT queries should work but INSERT/UPDATE/DELETE and DDL statements should return an error.

I'm aware of the ApplicationIntent=ReadOnly parameter, but my understanding is that its purpose is to connect to read replicas.
In my case, the target server might not have any read replica. In this case, would ApplicationIntent=ReadOnly be enough to make the conneciton read only even though it connects to the primary?

No, there is unfortunately no such connection string parameter.  The "ApplicationIntent=ReadOnly" option is just used by the SQL driver to attempt to route your connection to a read only replica.

I'd suggest that, if there are users or processes that should only have read access to this database, you accomplish this using the built-in SQL Server permissions.  In other words, only add those users / processes to the db_datareader database role if they are accessing tables and view directly.

If they are accessing data through stored procedures, be careful to only grant them EXECUTE permissions on the read procedures.


Postgres reduce by key if key is not null I need to select N rows with seq > X which reduce_key is null and for each row group with the same reduce_key select the one with the biggest seq. Result should be strictly sequential (but with rows with the same reduce_key filtered except the one with biggest seq) and limited with N.

Here is a query which does that:

explain analyze SELECT
    x2."user_id",
    x2."seq",
    x2."timestamp",
    x2."reduce_key",
    x2."mapping"
FROM
    "user_sequence" x2
WHERE
    (
        (x2."user_id" = 860862404)
        AND (x2."seq" > 3562974)
    )
AND (
    (x2."reduce_key" IS NULL)
    OR (
        x2."seq" IN (
            SELECT
                MAX ("seq")
            FROM
                "user_sequence"
            WHERE
                ("user_id" = 860862404)
            AND (
                ("seq" >= x2."seq")
                AND ("reduce_key" = x2."reduce_key")
            )
            GROUP BY
                "reduce_key"
        )
    )
)
ORDER BY
    x2."seq"
LIMIT 1000

The problem is that it's being executed ~70ms on table with tens millions of rows. Is there any more efficient way to reduce rows by key leaving rows with null key?

Table structure:

actor=# \d user_sequence;
   Table "public.user_sequence"
   Column   |  Type   | Modifiers
------------+---------+-----------
 user_id    | integer | not null
 seq        | integer | not null
 timestamp  | bigint  | not null
 mapping    | bytea   | not null
 reduce_key | text    |
Indexes:
    "user_sequence_pkey" PRIMARY KEY, btree (user_id, seq)
    "user_sequence_user_id_reduce_key_seq_idx" btree (user_id, reduce_key, seq)

EXPLAIN ANALYZE:

 Limit  (cost=0.57..231389.06 rows=1000 width=103) (actual time=0.408..74.754 rows=308 loops=1)
   ->  Index Scan using user_sequence_pkey on user_sequence x2  (cost=0.57..24444343.74 rows=105642 width=103) (act
ual time=0.406..74.645 rows=308 loops=1)
         Index Cond: ((user_id = 860862404) AND (seq > 3562974))
         Filter: ((reduce_key IS NULL) OR (SubPlan 1))
         Rows Removed by Filter: 692
         SubPlan 1
           ->  GroupAggregate  (cost=0.57..447.59 rows=1 width=33) (actual time=0.096..0.097 rows=1 loops=730)
                 Group Key: user_sequence.reduce_key
                 ->  Index Only Scan using user_sequence_user_id_reduce_key_seq_idx on user_sequence  (cost=0.57..4
47.03 rows=110 width=33) (actual time=0.036..0.081 rows=68 loops=730)
                       Index Cond: ((user_id = 860862404) AND (reduce_key = x2.reduce_key) AND (seq >= x2.seq))
                       Heap Fetches: 49371
 Planning time: 0.808 ms
 Execution time: 74.890 ms
(13 rows)

I think that problem is with filter but how to make it use index?

I am using PostgreSQL 9.4.

Assuming you want what your current query does (which seems different from what your description says).

You ask:

  I think that problem is with filter but how to make it use index?

Your query plan shows that Postgres is already using indexes for every step of the way. The added FILTER step only filters rows according to your additional predicates.

I posted a different idea at first that was missing ORDER BY seq. This query using a simple NOT EXISTS anti-semi-join should be a better equivalent:

SELECT user_id
     , seq
     , timestamp
     , reduce_key
     , mapping
FROM   user_sequence u
WHERE  user_id = 860862404
AND    seq > 3562974
AND    NOT EXISTS (
   SELECT 1
   FROM   user_sequence
   WHERE  user_id = u.user_id
   AND    reduce_key = u.reduce_key
   AND    seq > u.seq
   )
ORDER  BY seq
LIMIT  1000;

Your existing indexes look good for it. The second one might be a bit more efficient with descending sort order for the last item:

user_sequence_user_id_reduce_key_seq_idx btree (user_id, reduce_key, seq DESC)

Normally, there would be a simpler alternative with DISTINCT ON, but that would fold all rows with reduce_key IS NULL into a single row while you want them all.

Related answer with more explanation:

How do I efficiently get "the most recent corresponding row"?

Aside: You have a lot of unnecessary parentheses and double-quotes. Maybe an inefficient ORM?


ANSI_NULLS and QUOTED_IDENTIFIERS mismatch When I export the entire list of stored procedures/functions, I noticed there is a mismatch between ANSI_NULLS/QUOTED_IDENTIFIERS settings.  Some have them on, others off.  I'm not explicitly setting this for the stored procedures or functions, so how does SQL determine whether to define on or off for these settings by default?

so how does SQL determine whether to define on or off for the ansi nulls by default?

BOL quotes - For stored procedures, SQL Server uses the SET ANSI_NULLS setting value from the initial creation time of the stored procedure. Whenever the stored procedure is subsequently executed, the setting of SET ANSI_NULLS is restored to its originally used value and takes effect. When invoked inside a stored procedure, the setting of SET ANSI_NULLS is not changed.

You can use the sys.sql_modules to check  the stored procedure or function uses uses_ansi_nulls and uses_quoted_identifier settings 1 (ON) or 0 (OFF)

SELECT OBJECT_NAME([object_id]) as ObjectName,
       uses_ansi_nulls,
       uses_quoted_identifier
FROM sys.sql_modules
--WHERE OBJECT_NAME([object_id]) = 'any_sp/function_name_your_Want_to_Check'
ORDER BY object_id

Both ANSI_NULLS and QUOTED_IDENTIFIERS should be ON so that you dont see any unexpected surprises. Also, in newer versions of SQL Server, this two settings are defaulted to ON.


what are the costs of enabling extended events? I have been dealing with deadlocks, specifically getting their information, as you can see on this post here.

On this same post Shanky recommended "You must now rely on extended events trace to capture deadlock information"

My questions would be:

1) is there any increase of CPU by using extended events?

2) how much memory, disk space and I/O does it use?

any link to related documentation will be a bonus.

Of course there will be an increase on CPU, as any other process on the server. But Extended Events are recommended precisely due to the low resource needs of running them. Using Extended Events to capture information takes much fewer resources than using the long old known profiler tool for example. Use it wisely of course, don't setup and run thousands of sessions to capture tons of data, then you can have a problem. We have used Extended Events on our own servers for auditing different processes and from our experience, we almost didn't saw a measurable increase on CPU activity. Yes, it takes some disc space to record all data it gathers, but again, think ahead and plan were to save it in order to affect as less as possible production environment.

As for 2nd question: it depends, as usual. Each system, platform and configuration are different, none is exactly to the other. So, if not possible to tell you how much RAM, CPU, disk space or IO will gonna take. But certainly will not be so much. Again, use common sense, don't start hundreds of sessions capturing gazillion of data, because then obviously you will have a penalty on performance.

For more detailed info check here, here here and here.


Consistent mysqldump of mixed InnoDB and MyISAM tables from RDS instance I have MySQL db on dedicated server. I would like it to be an external master for an RDS instance. And I want to make backups of RDS. (Currently we have mix of both InnoDB and MyISAM but we are slowly transitioning to full InnoDB).

I did flush tables with read lock on our master(on dedicated server) and took a dump. Restored that dump on a RDS instance and configured external master. The RDS replication caught up. I am trying to create second slave in RDS from the first slave we have up to date with master.
This is where I run into trouble. I can not get consistent copy to run replication. I tried doing snapshot and building replication from the restored snapshot - but replication gets errors about duplicate entires and foreign constraints. If I skip some errors - replications runs for few minutes and errors again. The tables in the error are not MyISAM.

I tried doing dump. Tried locking tables. Flushing tables.

Because you can not do flush tables with read lock on RDS - I tried doing what is said in this AWS article: (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.BackingUpAndRestoringAmazonRDSInstances.html).

Because the errors are not connected to MyISAM tables I though it is not a problem, but still tried by:

use my_db;
flush tables [list of MyISAM tables];
lock each MyISAM table for read: `lock table <MyISAM table> read`

But the problem is still the same.

Database is around 100G so I dump it table by table with shell one liner.

Any ideas what and where I am doing wrong ?

Last month, I answered this question : Synchronizing a RDS instance with another RDS instance

From researching and answering that post, I recently learned that you can set read_only off on a MySQL RDS Slave. What can this mean for you ? A very unique opportunity. What opportunity ?

CONVERT ALL YOUR MyISAM TABLES TO InnoDB !!!

Once you have made the RDS Slave writable by setting read_only to 0, you can change the storage engine of all the tables in the RDS Slave to InnoDB. Don't worry, it is possible for a Master to have MyISAM tables and the Slave to have InnoDB. I have suggested Master and Slave having different storage engines before (Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?)

Once you have converted all that tables in the RDS Slave to InnoDB, you make read replicas of that new Slave without the MyISAM headaches of backing up data.

NOTE : Here is a sample script to convert all your MyISAM tables to InnoDB

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
CONVERT_SCRIPT=/tmp/ConvertMyISAMToInnoDB.sql
echo "SET SQL_LOG_BIN = 0;" > ${CONVERT_SCRIPT}
SQL="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;')"
SQL="${SQL} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQL="${SQL} table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${CONVERT_SCRIPT}
less ${CONVERT_SCRIPT}

GIVE IT A TRY !!!


Find rows containing a key in a JSONB array of records I'm trying to query for a key present in an array of objects. This structure:
column jdata
{"name": "Somedata",
 "array": [ {"name":"bla1", "attr": "somevalue"},
            {"name":"bla2", "otherdata": "somevalue2"},
            {"name":"bla3", "otherdata": "somevalue"}
           ],
"otherstuff": "stuff"
}

Now I do btree's on jdata->'name' or (jdata->'datetime')::cast and that works great.
I also do json_path_ops where jdata->'array' @> '[{"name":"bla3"}]' works a real charm.
My problem is the attr key can be in any of the objects in the array and I care about the record if the key is present, however the value can be nearly anything. Is there a way to query for this? Is there a way it can be indexed? I want to do jdata->'array' @> '[{"attr": ?}]' Or maybe the ? 'attr' can be used inside an array somehow?
Currently I am thinking a trigger that scans for the key and then moves it to a header with a true or false or whatever, and then a normal btree will work. Is there a better way? I need to edit around 500k records at the average site to add this values.
Please point me in a direction.

Postgres 12 or later: with SQL/JSON path expression
SELECT *
FROM   tbl
WHERE  jdata->'array' @? '$ ? (exists (@."attr"))';

You can look for keys or values, only in the outer nesting level or recurse, abstract arrays away (unnest them) in lax mode or not (strict mode). I added query variations in the fiddle to demonstrate:
db<>fiddle here
@? is jsonpath existence operator
jsonpath value '$ ? (exists (@."attr"))' explained:
$ ... look at each value found in left operand (in default "lax" mode)
? ... run the following test
(exists (@."attr")) ... Does a key with name 'attr' exist?
Can use an index, either with default jsonb_ops:
CREATE INDEX tbl_jdata_array_idx ON tbl USING GIN ((jdata->'array'));

Or with jsonb_path_ops:
CREATE INDEX tbl_jdata_array_path_ops_idx ON tbl USING GIN ((jdata->'array') jsonb_path_ops);

See:

https://www.postgresql.org/docs/current/gin-builtin-opclasses.html
https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING

Related:

How to sum the values of a json column filtered with regex?

Postgres 11 or older (no SQL/JSON, yet)
That particular use case is not covered by plain indexes for built-in operators.
Simple query without index support
SELECT *
FROM   tbl
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements(jdata->'array') elem
   WHERE  elem ? 'attr'
   );

EXISTS because we want each qualifying row once, even if multiple array elements can contain the key. And it's faster.
But this query cannot use an index.
Expression index
You can generate a text array of unique keys in the given jsonb array of records - and wrap the expression into a simple IMMUTABLE function:
CREATE OR REPLACE FUNCTION jsonb_arr_record_keys(jsonb)
  RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY (
   SELECT DISTINCT k
   FROM   jsonb_array_elements($1) elem, jsonb_object_keys(elem) k
   )';

COMMENT ON FUNCTION jsonb_arr_record_keys(jsonb) IS '
   Generates text array of unique keys in jsonb array of records.
   Fails if any array element is not a record!';

Then create a GIN expression index based on this function:
CREATE INDEX tbl_special_idx ON tbl USING gin (jsonb_arr_record_keys(jdata->'array'));

Query like this, using the generic array contains operator @>:
SELECT *
FROM   tbl
WHERE  jsonb_arr_record_keys(jdata->'array') @> '{attr}';

Now the index can be used efficiently.
Provide the key name nested in an array ('{attr}'). (You can conveniently check for multiple keys this way ('{attr1, attr2}') or similar ... )
dbfiddle here
Related:

Index for finding an element in a JSON array
How to get particular object from jsonb array in PostgreSQL?
Conditionally replace single value per row in jsonb column


Oracle DBMS Scheduler - what if database fails? We are moving some Oracle overnight jobs off a cron server and running them via Oracle DBMS Scheduler instead. It is my understanding that if the database fails overnight then the job email notification will also fail.

Has anyone got a method of emailing users to let them know their specific overnight jobs failed in this instance (without letting them know the entire database dropped)?  It seems a slight limitation of using dbms scheduler over a cron server.

I was thinking if there was a way to monitor a gmail mailbox and if a job success email from the scheduler is not received to then send a job fail message to the users?  Couldn't see a way to do this either unfortunately.

Does anyone have any ideas for this scenario?  Thanks very much.

If your scheduler database fails, this should raise an event in your monitoring application. This is about the similar situation as compared to cron, when the server fails. In that case cron also has no way to tell it failed.

If you want to use dbms_scheduler as an enterprise scheduling tool, don't forget the reporting. Reporting should help explaining what ran and why not.


Mysql fulltext boolean ignore phrase I'm trying to come up with a mysql boolean search that matches "monkey" but ignores / doesn't take into account any matches of "monkey business".

Something like the following is the general idea but it negates the value. I need it to not match rows with "monkey business" unless they also have "monkey" without "business" after it elsewhere in the text.

SELECT * FROM table
MATCH (title) against ('+monkey ~"monkey business"' IN BOOLEAN MODE);

Is it possible? Thanks in advance for any help you can give! I realise this could be done with better search engines, at the moment fulltext is all I can use.

I tend to be a little conservative and cautious when using FULLTEXT indexes because FULLTEXT indexes has a nasty habit of playing head games with the MySQL Query Optimizer.

Try getiing all monkey rows first in a subquery, and then eliminate "monkey business":

SELECT * FROM
(
    SELECT id,title FROM table
    WHERE MATCH (title) against ('+monkey' IN BOOLEAN MODE)
) A
WHERE LOCATE('monkey business',title) = 0;

You could also do the reverse by getting all rows that do not have "monkey business" and then scan for any rows with monkey:

SELECT * FROM
(
    SELECT id,title FROM table
    WHERE MATCH (title) against ('-"monkey business"' IN BOOLEAN MODE)
) A
WHERE LOCATE('monkey',title) > 0;

The second query would make the temp table from the subquery much larger and would take longer to scan for the outside WHERE clause. Go with the first query.

Give it a Try !!!


Postgres on Engineyard space at 100%, pg_xlog filled up with files I have a database that is growing rapidly. VERY rapidly. My /db directory is at 100% now, and there are a bunch of new files in pg_xlog.

My background is in Oracle and MySQL. I do not know what is going on with this database. I know that I need to hire a Postgres DBA quickly.

Is there anything in the immediate term that I can do to fix my issue? I notice that deleting data does nothing to mitigate my space issue.

Output from pg_settings is:

archive_command             /bin/true                       configuration file
archive_mode                on                              configuration file
archive_timeout             1min                            configuration file
checkpoint_segments         100                             configuration file
checkpoint_timeout          5min                            configuration file
client_encoding             UTF8                            session
DateStyle                   ISO, MDY                        configuration file
default_statistics_target   100                             configuration file
default_text_search_config  pg_catalog.english              configuration file
effective_cache_size        1358MB                          configuration file
hot_standby                 on                              configuration file
hot_standby_feedback        on                              configuration file
lc_messages                 C                               configuration file
lc_monetary                 C                               configuration file
lc_numeric                  C                               configuration file
lc_time                     C                               configuration file
listen_addresses            *                               configuration file
log_destination             csvlog                          configuration file
log_line_prefix             %m:                             proc=%p,user=%u,db=%d,host=%r   configuration file
log_min_duration_statement  2s                              configuration file
log_rotation_age            1d                              configuration file
log_rotation_size           100MB                           configuration file
logging_collector           on                              configuration file
maintenance_work_mem        128MB                           configuration file
max_connections             512                             configuration file
max_files_per_process       65535                           configuration file
max_stack_depth             6MB                             configuration file
max_standby_streaming_delay -1                              configuration file
max_wal_senders             5                               configuration file
port                        5432                            configuration file
search_path                 public, "$user", public         session
shared_buffers              424MB                           configuration file
temp_tablespaces                                            configuration file
wal_buffers                 8MB                             configuration file
wal_keep_segments           128                             configuration file
wal_level                   hot_standby                     configuration file
wal_writer_delay            200ms                           configuration file
work_mem                    32MB                            configuration file

You can revise the checkpoint_segments setting in your postgresql.conf and issue SELECT pg_reload_conf(); as the postgres (super)user (in any database) to make that change live. What the change to that setting does is reduce the number of WAL segments that remain resident in your pg_xlog directory. If you have a lot of data churn in your db you will want to keep the checkpoint_segments setting at 32 minimum (see http://www.postgresql.org/docs/current/static/runtime-config-wal.html#GUC-CHECKPOINT-SEGMENTS for more details).

Some general tips:

Ensure your autovacuum settings are pretty aggressive.
Move the pg_xlog directory to a different volume than the $PGDATA directory. I have measured performance increases from 10% - 30% from that change alone.
Set up a monitoring tool to keep an eye on table/index/database/server size increases. Eg. Nagios, New Relic,


Ephemeral disk on Azure LS series I am little confused about the ephemeral disk on LS series Azure VM.I want to suggest  L32s_v2 series vm for a customer due to high iops and throughput that the nvme disks provide but there is a catch. if vm is stopped from portal all the data on nvme disk will be erased. this is a serious risk because i cant use it for sql server database files


temp tables in tempdb are not cleaned up by the system My question is similar to the one asked here: SQL Server tempdb rogue temporary tables

Since I don't have enough points to comment and the answer there doesn't address my concern, I can only ask a new question here.

Using SQL Search Tool in Visual Studio 2017 I can see the columns in the table and  confirm that the temp table named #BBC835DE is indeed from a table variable, which is related to a stored procedure. I re-run the procedure without any problem, but this table still hangs on. How do I drop tables like this and clean up the tempdb? Thanks.

PS. Based on the result from SQL Search Tool, it seems that not all the tables named like #BBC835DE are from table variables, some are from the cursor declared in stored procedures too.

I believe this is to do with the plan cache. Just ran a little test on my end and I can replicate it.

To remove the table from tempdb DBCC FREEPROCCACHE works, obviously target the plan handle for the stored procedure.

You could do this but it would just come back again and freeing the cache all the time generally isn't the best idea.

If you're concerned about it taking up space you can run this query I wrote for a monitoring tool that should work. On my tests it always shows 0KB after the SP has finished. This leads me to believe it just keeps the shell of the table there for some internal use. So it seems harmless to me.

USE tempdb;
WITH KB
AS (SELECT t.name,
           SUM(a.used_pages) * 8 AS KBUsed,
           p.rows AS [Rows]
    FROM sys.tables t
        INNER JOIN sys.partitions p
            ON t.object_id = p.object_id
        INNER JOIN sys.allocation_units a
            ON a.container_id = p.partition_id
    WHERE t.is_ms_shipped = 0
    GROUP BY t.name,
             p.rows
   ) SELECT * FROM KB


Does MERGE prevent deadlocks and server blocking? I have started looking at MERGE as an option that I would use in my application to process UPSERT transactions. Seems that a lot of SQL Server experts are recommending this approach. However, while exploring this method I found some common problems that MERGE can cause, for example in
Use Caution with SQL Server's MERGE Statement by Aaron Bertrand.
My system is state wide and I use ColdFusion on the back end. It is multi-thread and we expect a huge volume of requests to our database. Each of these INSERT/UPDATE transactions will handle a single row transaction. That means the user is inserting or updating one row at the time. I use transaction with rollback in ColdFusion when I call stored procedure. Is that enough or would be better to have that in SQL instead?
Here is an example of my MERGE statement used in a stored procedure:
CREATE PROCEDURE [dbo].[SaveMaster]
   @RecordID INT = NULL, -- Auto increment ID
   @Status BIT = NULL,
   @Name VARCHAR(50) = NULL,
   @Code CHAR(2) = NULL, --Primary Key
   @ActionDt DATETIME = NULL,
   @ActionID UNIQUEIDENTIFIER = NULL
AS
   MERGE dbo.Master WITH (HOLDLOCK) AS Target
   USING (SELECT @RecordID,@Status,@Name,@Code,@ActionDt,@ActionID)
   AS Source (RecordID,Status,Name,Code,ActionDt,ActionID)
      ON Target.RecID = Source.RecordID
   WHEN MATCHED THEN
      UPDATE
    SET Target.Status = Source.Status,
        Target.Name = Source.Name,
        Target.Code = Source.Code,
        Target.ActionDt = Source.ActionDt,
        Target.ActionID = Source.ActionID
   WHEN NOT MATCHED THEN
    INSERT(
        Status,Name,Code,ActionDt,ActionID
    )VALUES(
        Source.Status,
        Source.Name,
        Source.Code,
        Source.ActionDt,
        Source.ActionID
    )
OUTPUT inserted.RecID,$action as Action;

You will see that I use WITH(HOLDLOCK) to prevent primary key violations and deadlocks. After I read the article that is attached in my post, it seems that even having HOLDLOCK still you can end up with deadlocks. I'm not sure if that applies to my code above as well.
I use a @RecordID parameter to check if the row exists, and based on that MERGE will execute an insert or update. This column is an auto-incremented id that I pass back and forth in order to make sure I always update the correct row in the table. This approach is used in more than one transaction in my system.
I'm wondering if this is good enough or I should consider using Primary Key (Code is PK) instead? Or maybe have both RecordID and Code column included? Would that help in performance at all?
I'm exploring the options and the most important trying to prevent deadlocks and server blocking that seems like a big issue with these transactions in SQL world. If anyone have opinion or recommendation for my code please let me know.
Additional considerations
I have received some feedback that insert and update should be separate, and I should try to avoid MERGE for various reasons. Here is another approach that I found. I'm wondering if any of these two would be a good solution and if there is any potential problem with primary key violation or deadlocks:
begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran
Or

2)

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

I know that my SQL Server 2008 needs an upgrade but that is something that I do not control unfortunately.

In the example above you will see that I use WITH(HOLDLOCK) to prevent
  primary key violation and deadlocks.

That is necessary and (normally) sufficient to prevent PK violations with concurrent MERGE.  Deadlocks depend on the other locks in your transaction, but if you have a simple clustered index key lock for your MERGE you should be fine.

  I use @RecordID parameter to check if record exist and based on that
  MERGE will execute Insert or Update. This field is auto incremented id
  that I pass back and forth in order to make sure I always update
  correct rows in the table.

That is quite strange and probably bad.  You should use the Primary Key to "make sure you always update the correct rows".  I don't see why you have that column at all, let alone using it in your MERGE.  Just use the PK.