How can I delete rows in an oracle table from xml input parameter?

The problem is your village subquery, it returns a single value of aggregated village ids instead of a table of ids:

SQL> WITH DATA AS
  2   (SELECT XMLTYPE('<root>
  3  <openperiod>
  4  <month></month>
  5  <village>
  6     <villageID>1</villageID>
  7     <villageID>2</villageID>
  8  </village>
  9  </openperiod>
 10  </root>') l_xml
 11      FROM dual)
 12  SELECT extract(l_xml,'//openperiod/village/villageID/text()').getStringVal()
 13    FROM DATA;
EXTRACT(L_XML,'//OPENPERIOD/VI
--------------------------------------------------------------------------------
12

Instead, you should use XMLTABLE (see this other SO question for example):

SQL> WITH DATA AS
  2   (SELECT XMLTYPE('<root>
  3  <openperiod>
  4  <month></month>
  5  <village>
  6     <villageID>1</villageID>
  7     <villageID>2</villageID>
  8  </village>
  9  </openperiod>
 10  </root>') l_xml
 11      FROM dual)
 12  SELECT x.id
 13    FROM DATA d,
 14         XMLTABLE('//openperiod/village/villageID'
 15                  PASSING d.l_xml
 16                  COLUMNS ID VARCHAR2(30) PATH 'text()') x;
ID
------------------------------
1
2

My advice when dealing with complex queries:

start with SQL, then when your query is sound and gives the good result, put it in PL/SQL
start with SELECT, not DML
decompose your query, make sure each subquery returns the appropriate result
build a small example with test tables with 1-2 rows each


Jonathan's blog post describes quite nicely that the tables are striped across the data files in a filegroup and written to in a consistent manner.

Where the files are the same size and starting from empty, you should see the same amount of data for the table across each file.

You should be able to use the same xEvents scripts to see this for your own system.


SQL Server - backup / restore schema only

I haven't used it before but there is an object in SSIS called Transfer SQL Server Objects.  It has an option to copy data or not and you can select copy all objects or just specific types of objects, permissions etc.

You could also look into doing replication but blocking the data portion of it.  In fact if you set up a snapshot replication that just pushed schema for each of your servers you could potentially have an automated process to do the push.

I think the SSIS option is probably your best bet though.


Bringing web service data into SQL server

Does this need to be done using SSIS? If so, you can do pretty much whatever you like in a Script Task (C# or VB.NET). That way you can bring the data directly into SSIS variables and use throughout the package.

Or, you can do this via SQLCLR, in which case you can call it directly from T-SQL. This lets you integrate it rather easily into Stored Procedures, SQL Agent Jobs, etc. You can even call it in a SQL Task in SSIS. BUT, you would need to be a bit more careful using SQLCLR as opposed to using a Script Task in SSIS. I have documented several things to consider across a few answers on Stack Overflow:

How to invoke webservice from SQL Server stored procedure
SQL CLR Web Service Call: Limiting Overhead
Call web service from SQL CLR?

The most important points from those answers are:

Look at the following MSDN pages:

HttpWebRequest
HttpWebResponse
XmlDocument

To escape URLs / Query Strings:

If you are using SQL Server 2005, 2008, or 2008 R2, then use Uri.EscapeDataString as it was available prior to .NET Framework v4.5
If you are using SQL Server 2012, 2014, or newer, then you can use either Uri.EscapeDataString or, if the server has been updated to at least .NET Framework v4.5, then you can alternatively use WebUtility.UrlEncode

If not already done, you will have to enable (one time) "CLR Integration" at the server level: Enabling CLR Integration
SQLCLR is "shared" code. There is a single App Domain for each DB + Owner combination. So all Sessions calling this code will be operating in the same App Domain. If multiple simultaneous requests are made to the same URI, all but the first 2 might be "on hold" since you are bound by the concurrent connection limit imposed by ServicePointManager ( ServicePointManager.DefaultConnectionLimit ). And the default limit is a whopping 2! Meaning, all additional requests to that URI, while there are already 2 active/open HttpWebRequests, will wait inline, patiently. You can increase this by setting the .ServicePoint.ConnectionLimit property of the HttpWebRequest object.
You will need to mark your Assembly as WITH PERMISSION_SET = EXTERNAL_ACCESS. Please don't be lazy and turn TRUSTWORTHY ON. That is an unnecessary security risk. The proper way to make this secure is to do the following:

Sign your Assembly
In the [master] database, create an Asymmetric Key from the DLL of your Assembly.
Also, in [master], create a Login from that Asymmetric Key
Grant your new Login the EXTERNAL ACCESS ASSEMBLY permission
Create your Assembly with a PERMISSION_SET of EXTERNAL_ACCESS, not UNSAFE

If you want to go down the SQLCLR path but don't want to mess with the coding, etc, then one option is to get a pre-built function. The SQL# SQLCLR library (that I wrote) has a function called INET_GetWebPages (not available in the Free version) that calls a URL (can pass in either GET or POST data) and passes back the response (and the response headers as a separate XML field).


Database structure for multiple users sharing one dataset, but also many users with their own dataset

One option to do it is having a many-to-many table, which will hold a calendar id and a user id who can see it. A calendar belonging to two spouses will have two rows in that table.


Find the first gap in an aggregation of integers in SQL Server

You can use ROW_NUMBER to generate what the rank should be

;WITH ranks AS
(
    SELECT
       GroupID, Rank,
       ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY Rank) AS newRank
    FROM
       dbo.GroupAssignment
)
SELECT
   GroupID, MIN(newRank), MIN(Rank)
FROM
   ranks
WHERE
   newRank < Rank -- don't need <> here
GROUP BY
   GroupID;

Personally, I wouldn't re-use this Rank if one of these applies

It can be generated at runtime with ROW_NUMBER etc
It is some kind of ID column: the old value will live in history tables etc and will be ambiguous
It is simply an arbitrary internal key

Also, do you want RANK or DENSE_RANK to deal with "joint 2nd" or "equal values" instead of ROW_NUMBER


Optimising MariaDB for large AWS machines

innodb_buffer_pool_instances will not give you a huge advantage unless you are suffering from a very specific write contention between multiple write threads. I have personally never needed to increase it over the default 8 instances.

I use 500GB servers for MySQL and dedicate around 384 GB to the buffer pool- more would also make sense - I optimize for huge concurrency and even then I am thinking to increase it to an 80% or 85% of the total available memory (but want to leave enough for the filesystem cache).

If that would be an advantage for you or not, it will depend on your access patterns- if you are doing analytics queries, your speed up may be minimal- and of course it has decreasing returns to the point of not being an advantage at all for memory larger than a dataset.

It is very difficult to provide a general advice, but if I had todo it would be this: 1) make sure you are using the right tool (vanilla mysql-InnoDB- may be not a great a great tool for analytics- plugins or external tool may fit better); 2) keep 10-20 percent of your data on memory (most needs follow a 90-10, or 80-rule for frequency of access; and 3) keep your buffer pool with a 75%-80% of your total memory if you only use InnoDB as your main engine. Stress on general advice, your needs will vary and so your configuration. See what is your bottleneck and try to optimize that.


Why do you use this view at all for this query?

The view collects violations and other stuff, but your query does not care about those at all, just the number of items. The view lists all items regardless of these because of the outer joins, so you basically perform a lot of unnecessary extra work to collect violations and other stuff (the NO_MERGE hint makes this even worse), then you throw it away and count the items based on a totally different criteria.

The query should be something like this:

select
  count(*)
from
  item i
  join channel ch on (i.channel_id = ch.id)
  join person p on (i.person_id = p.id)
where
  ch.status = 'A'
  and i.channel_id = 122507833
  and i.item_date >= timestamp'2015-02-01 00:00:00';

Additionally, you don't need materialized views for indexing a column. Just create the index on the column(s) of the base table(s).

On top of the must-have indexes on primary and foreign keys, another possible candidate is item_date column depending on your data distribution.


SQL Server 2005 database corruption. Advise

As you have an old backup with the correct schema, the problem database is online and you've successfully queried several tables, I'd be inclined to try get a dump of the raw data as fast as possible.

Shut down the applications accessing the database.
BCP export the data, table by table to files. As disk corruption could be the source of your woes, export the data to a portable drive or to a fileshare on another machine.
Restore your old backup to a known good server.
Depending on the complexity of the database schema either truncate the data from your restored backup or script out the schema and create a new empty database.
BCP the data back in.

The data may contain inaccuracies, it may be missing rows or contain spurious values. But with any luck you'll end up with more than was in the month old backup.


Access Macro: ApplyFilter w/values from 2 combo boxes

Oh.

Well, I figured it out. I didn't realize that when I use OR, the macro parses it like WHERE (X LIKE Y) OR (X Like Z).

The solution is as follows:

ApplyFilter WHERE Table!Articles!IntCodes LIKE "*"IntCombo1 & "*" & IntCombo2 "*" OR Table!Articles!IntCodes LIKE "*"IntCombo2 & "*" & IntCombo1 "*"

I was hoping to be a little more efficient, but apparently macros don't dig that. :( Any other comments still appreciated!


Creating report based on the sql statement

You can "translate" the appropriate text values to 1 or 0, then add them.

select
  student_id,
  student_name,
  date1,
  date2,
  date3,
  date4,
  date5,
  decode(date1, 'Present', 1, 0) +
  decode(date2, 'Present', 1, 0) +
  decode(date3, 'Present', 1, 0) +
  decode(date4, 'Present', 1, 0) +
  decode(date5, 'Present', 1, 0) as Present,
  decode(date1, 'Late', 1, 0) +
  decode(date2, 'Late', 1, 0) +
  decode(date3, 'Late', 1, 0) +
  decode(date4, 'Late', 1, 0) +
  decode(date5, 'Late', 1, 0) as Late,
  decode(date1, 'Absent', 1, 0) +
  decode(date2, 'Absent', 1, 0) +
  decode(date3, 'Absent', 1, 0) +
  decode(date4, 'Absent', 1, 0) +
  decode(date5, 'Absent', 1, 0) as Present,
  decode(date1, 'Present', 1, 'Late', 1, 0) +
  decode(date2, 'Present', 1, 'Late', 1, 0) +
  decode(date3, 'Present', 1, 'Late', 1, 0) +
  decode(date4, 'Present', 1, 'Late', 1, 0) +
  decode(date5, 'Present', 1, 'Late', 1, 0) as Total_attendance
from
  attendance;

CASE also works, but that is a bit longer:

...
case when date1 = 'Present' then 1 else 0 end +
case when date2 = 'Present' then 1 else 0 end +
case when date3 = 'Present' then 1 else 0 end +
case when date4 = 'Present' then 1 else 0 end +
case when date5 = 'Present' then 1 else 0 end as Present,
...


using Change Tracking functions from another database

CHANGE_TRACKING_CURRENT_VERSION is a built in function not a UDF so Production.dbo.CHANGE_TRACKING_CURRENT_VERSION() isn't allowed.

It accepts no parameters to specify the database.

I would imagine that either of the following would work.

The first calls Production.sys.sp_executesql and executes the function. The second calls sys.sp_executesql in the current database and adds an explicit USE statement to set the context. I haven't set up change tracking and tested explicitly but both approaches work as desired with DB_NAME()

DECLARE @CHANGE_TRACKING_CURRENT_VERSION BIGINT;

EXEC Production.sys.sp_executesql
  N'SELECT @ReturnValue = CHANGE_TRACKING_CURRENT_VERSION()',
  N'@ReturnValue BIGINT OUTPUT',
  @ReturnValue = @CHANGE_TRACKING_CURRENT_VERSION OUTPUT

SELECT @CHANGE_TRACKING_CURRENT_VERSION

DECLARE @CHANGE_TRACKING_CURRENT_VERSION BIGINT;

EXEC sys.sp_executesql
  N'USE Production;
    SELECT @ReturnValue = CHANGE_TRACKING_CURRENT_VERSION()',
  N'@ReturnValue BIGINT OUTPUT',
  @ReturnValue = @CHANGE_TRACKING_CURRENT_VERSION OUTPUT

SELECT @CHANGE_TRACKING_CURRENT_VERSION


How to identify which connection is causing error 17886?

You can do this with an Extended Event Session.  EG
CREATE EVENT SESSION [TrackError_17886] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.username)
    WHERE ([error_number]=(17886)))
ADD TARGET package0.event_file(SET filename=N'TrackError_17886')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
go

Will record the errors in files in your "log" folder.


I've found that this issue seems to be a problem with the number of rows I was attempting to aggregate.  I get errors when aggregating over about 5,000 rows, but don't get errors (well, the same error, anyway) when aggregating less than that.

My solution ended up being to segment and aggregate the rows using ROW_NUMBER, then aggregate those groups, as in the following:

select
    geography::UnionAggregate(GroupGeography)
from
    (
        select
            geography::UnionAggregate(ZC_Geography) as GroupGeography
        from
            (
                select
                    *,
                    ROW_NUMBER() OVER(PARTITION BY 'dummy' ORDER BY ZCG_Zip_Code) / 1000 as ZipGroup
                from
                    tblZipCode
            ) a
        group by ZipGroup
    ) groups


How to resolve duplicate constraint names in Postgres?

My solution was to drop and recreate the constraints, with one of them given a different name. Postgres will let me drop one constraint by name, but I don't know how it chooses which one or whether it's even deterministic, so the safe route from scripting was to drop both and recreate from there. In the above case, that would go like:
alter table t1
    drop constraint oops,
    drop constraint oops,
    add constraint oops foreign key (id) references t2 (id),
    add constraint oops_unique unique (id);

Fortunately, my table was small enough for this to be a practical solution. If it weren't, I'd try updating pg_constraint directly, as Laurenz suggested.


Risks of manual failover in Always On Availability Groups

Do yo have a test environment?  If you don't, get a $200 free Azure credit and set your environment up there (same service pack level) and try it, it is one of the best way to gain confidence and find edge cases.  Nothing can really beat testing a like to like environment except experience.  Also check out the official support docs from Microsoft.

Per the doc you'll want to check out several items, including:

To determine the failover readiness of an secondary replica, query the is_failover_ready column in the sys.dm_hadr_database_cluster_states dynamic management view, or look at the Failover Readiness column of the Always On Group Dashboard.

To answer the question though, AlwaysOn failovers are very fast as the service is running on both instances and the listener then points to the new host.  Ensure the hosts are connecting to the listener instead of the IP of the machine.

Thus, if tested and running properly in your environment it is much faster than FCIs (Failover Cluster Instances) during busy hours.  Note that AlwaysOn only works per database and not per server so you will want to ensure jobs are setup right on the new primary replica, users, permissions/sids, and if you need to maintain databases in sync you will want to ensure that you do that somehow before failing one of them over and not the other.  Also, if you use MSDTC for cross database transactions you'll want to be very careful as it can cause irreparable data corruption in many cases where you use cross db transactions within the same instance.

Perhaps Sean will be able to give you more of the issues you might face if he sees your thread.


After trying multiple times and with the help of the answer here, I was able to get the right query:

SELECT t1.`id`, t1.`BU_ID`, t1.`country_id`,
       t1.`Name`, t1.`Description`, t1.`Length`,
       t1.`Width`, t1.`Height`, t3.min_lwh
FROM boxes AS t1
JOIN (SELECT MIN(t2.Length * t2.Width * t2.Height) AS min_lwh
      FROM boxes AS t2 WHERE (t2.Length * t2.Width * t2.Height) >= 3775
   ) AS t3
ON (t1.Length * t1.Width * t1.Height) = t3.min_lwh;


Oracle related to count(*) syntax alternate options

One option would be to try the first query and catch the no_data_found exception

begin
  SELECT rate
    into StdDefaultValue
    FROM RateTable1L
   where wkstn_oid_sh = p_pf_wkstn_oid_sh
     AND wkstn_oid_lng = p_pf_wkstn_oid_lng
     and exact ='STANDARD DEFAULT';
exception
  when no_data_found
  then
    SELECT rate
      into StdDefaultValue
      FROM RateTable1
     where wkstn_oid_sh = p_pf_wkstn_oid_sh
       AND wkstn_oid_lng = p_pf_wkstn_oid_lng
       and exact ='STANDARD DEFAULT';
end;

or iterate through the tables

begin
  SELECT rate
    into StdDefaultValue
    FROM RateTable1L
   where wkstn_oid_sh = p_pf_wkstn_oid_sh
     AND wkstn_oid_lng = p_pf_wkstn_oid_lng
     and exact ='STANDARD DEFAULT';
exception
  when no_data_found
  then
    StdDefaultValue := null;
end;

if( StdDefaultValue is null )
then
  begin
    SELECT rate
      into StdDefaultValue
      FROM RateTable1
     where wkstn_oid_sh = p_pf_wkstn_oid_sh
       AND wkstn_oid_lng = p_pf_wkstn_oid_lng
       and exact ='STANDARD DEFAULT';
  exception
    when no_data_found
    then
      StdDefaultValue := null;
  end;
end if;

If it is more likely that the value will be found in one of the tables, it would make sense to check that table first.  Depending on how many tables you have to check and how many times this is getting called, exception handling may not be the most efficient approach.  You might be better off with a single UNION ALL query assuming that the value will appear in at most one table.

  SELECT rate
    into StdDefaultValue
    FROM (SELECT rate
            FROM RateTable1L
           where wkstn_oid_sh = p_pf_wkstn_oid_sh
             AND wkstn_oid_lng = p_pf_wkstn_oid_lng
             and exact ='STANDARD DEFAULT'
          UNION ALL
          SELECT rate
            FROM RateTable1
           where wkstn_oid_sh = p_pf_wkstn_oid_sh
             AND wkstn_oid_lng = p_pf_wkstn_oid_lng
             and exact ='STANDARD DEFAULT')

From a data model standpoint, though, having 8 separate tables with the same basic structure and not knowing which table you need to query seems problematic.  It would seem like the sort of thing that is either calling for a parent entity with a type attribute that stores data common to all 8 tables or for creating a single table with 8 partitions. Either approach would make writing code far easier and would make the code more efficient.


Get first and last record for each date and calculate their difference

@Lennart's answer can be simplified to a single Group By:

SELECT LogDate, StartOfDay, EndOfDay,
    DATEDIFF(s, StartOfDay, EndOfDay) AS ElapsedSeconds
FROM
  (
    SELECT
        -- instead of FORMAT better use a Standard SQL CAST:
        CAST(LogDateTime AS DATE) AS LogDate,

        MIN(CASE WHEN EventCode = 1 THEN LogDateTime END) AS StartOfDay,
        MAX(CASE WHEN EventCode = 2 THEN LogDateTime END) AS EndOfDay
    FROM #MyLogs
    WHERE EventCode in (1,2)
    GROUP BY
        CAST(LogDateTime AS DATE)
        -- Pivot in a single stepby removing EventCode
  ) AS dt;

see query


Some resource limitations: https://dev.mysql.com/doc/refman/5.6/en/user-resources.html and https://mariadb.com/kb/en/library/grant/#resource-limit-options
Switch to MariaDB to get "user stats" (connections, bytes, rows, CPU, etc): https://mariadb.com/kb/en/library/information-schema-user_statistics-table/ and  https://mariadb.com/kb/en/library/user-statistics/


Postgres - using aggregate functions to return a different column?

You need to self-join the results of your current query with the base table (twice) on the values of temperature and date. Your query might look something like this:

with agg (day, min_c, avg_c, max_c ) as (
    select date_trunc('day',ts) as ts,
    min("Air Temperature (Actual) (Degrees C)") as "Daily Min Temp (F)",
    avg("Air Temperature (Actual) (Degrees C)"),
    max("Air Temperature (Actual) (Degrees C)")
    from solardb.weatherdata
    group by date_trunc('day',ts)
    order by date_trunc('day',ts)
)
select
  agg.day as ts,
  agg.min_c*9/5 + 32  as "Daily Min Temp (F)",
  data_min.ts as "Time of minimum",
  agg.avg_c*9/5 + 32 as "Daily Avg Temp (F)",
  agg.max_c*9/5 + 32 as "Daily Max Temp (F)",
  data_max.ts as "Time of maximum"
from agg
join solardb.weatherdata data_min
  on agg.min_c = data_min."Air Temperature (Actual) (Degrees C)"
  and agg.day = date_trunc('day',data_min.ts)
join solardb.weatherdata data_max
  on agg.max_c = data_max."Air Temperature (Actual) (Degrees C)"
  and agg.day = date_trunc('day',data_max.ts)

The above is not tested. I used the CTE to make logic more obvious, but one could use a simple subselect too.


Measure the size of a PostgreSQL table row

Q2: way to measure page size
PostgreSQL provides a number of Database Object Size Functions. I packed the most interesting ones in this query and added some Statistics Access Functions at the bottom. (The additional module pgstattuple provides more useful functions, yet.)
This is going to show that different methods to measure the "size of a row" lead to very different results. It all depends on what you want to measure, exactly.
This query requires Postgres 9.3 or later. For older versions see below.
Using a VALUES expression in a LATERAL subquery, to avoid spelling out calculations for every row.
Replace public.tbl with your optionally schema-qualified table name to get a compact view of collected row size statistics. You could wrap this into a plpgsql function for repeated use, hand in the table name as parameter and use EXECUTE ...
SELECT l.metric, l.nr AS bytes
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.tbl t                     -- provide table name *once*
   ) x
CROSS  JOIN LATERAL (
   VALUES
     (true , 'core_relation_size'               , pg_relation_size(tbl))
   , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
   , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
   , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
   , (true , 'indexes_size'                     , pg_indexes_size(tbl))
   , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
   , (true , 'live_rows_in_text_representation' , txt_len)
   , (false, '------------------------------'   , NULL)
   , (false, 'row_count'                        , ct)
   , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
   , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, metric, nr);
Result:
              metric               | bytes    | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 44138496 | 42 MB        |            91
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    32768 | 32 kB        |             0
 table_size_incl_toast             | 44179456 | 42 MB        |            91
 indexes_size                      | 33128448 | 32 MB        |            68
 total_size_incl_toast_and_indexes | 77307904 | 74 MB        |           159
 live_rows_in_text_representation  | 29987360 | 29 MB        |            62
 ------------------------------    |          |              |
 row_count                         |   483424 |              |
 live_tuples                       |   483424 |              |
 dead_tuples                       |     2677 |              |
For older versions (Postgres 9.2 or older):
WITH x AS (
   SELECT count(*)               AS ct
        , sum(length(t::text))   AS txt_len  -- length in characters
        , 'public.tbl'::regclass AS tbl      -- provide table name as string
   FROM   public.tbl t                       -- provide table name as name
   ), y AS (
   SELECT ARRAY [pg_relation_size(tbl)
               , pg_relation_size(tbl, 'vm')
               , pg_relation_size(tbl, 'fsm')
               , pg_table_size(tbl)
               , pg_indexes_size(tbl)
               , pg_total_relation_size(tbl)
               , txt_len
             ] AS val
        , ARRAY ['core_relation_size'
               , 'visibility_map'
               , 'free_space_map'
               , 'table_size_incl_toast'
               , 'indexes_size'
               , 'total_size_incl_toast_and_indexes'
               , 'live_rows_in_text_representation'
             ] AS name
   FROM   x
   )
SELECT unnest(name)                AS metric
     , unnest(val)                 AS bytes
     , pg_size_pretty(unnest(val)) AS bytes_pretty
     , unnest(val) / NULLIF(ct, 0) AS bytes_per_row
FROM   x, y

UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;
Same result.
Q1: anything inefficient?
You could optimize column order to save some bytes per row, currently wasted to alignment padding:
integer                  | not null default nextval('core_page_id_seq'::regclass)
integer                  | not null default 0
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   |
text                     | default '{}'::text
text                     |
timestamp with time zone |
timestamp with time zone |
integer                  |
integer                  |

This saves between 8 and 18 bytes per row. I call it Column Tetris. See:

Configuring PostgreSQL for read performance
Calculating and saving space in PostgreSQL

Also consider:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars


Oracle Tablespace not using 100% of available space

I do not see any issue other than using an unnecessary an incorrect query for monitoring instead of DBA_TABLESPACE_USAGE_METRICS and possibly misunderstanding its output.

PCT_FREE is the ratio of the free space compared to the allocated size.

PCT_ROOM is the ratio of the free space compared to the.

When PCT_FREE reaches 0, that means all your datafiles are full of data up to their allocated size. The autoextensible datafiles in the tablespace can still grow 6% up to maxbytes. At this point, if more data is inserted, PCT_FREE will remain 0 and PCT_ROOM will start decreasing. But keep in mind, 1% here is 490 GB, so it could take a while for PCT_ROOM to move from 6 to 5.

The query is incorrect, because a datafile that is not autoextensible will have its maxbytes value at 0. This part simply does not account for that:

ROUND(SUM(maxbytes) /(1024*1024)) MaxMegBytes

It should be:

ROUND(SUM(decode(maxbytes, 0, bytes, maxbytes)) /(1024*1024)) MaxMegBytes

With non-autoextensible datafiles, the above query returns inaccurate results, it may even return negative values.

If the concept of autoextensible datafiles is confusing, just simply preallocate all your datafiles to the maximum size. That way PCT_ROOM will be 0, and PCT_FREE will represent the actual free space percentage. You can even disable autoextend on the datafiles after doing this, but for that, you need to fix the query as I have point out above.

Or just simply throw all this out and use DBA_TABLESPACE_USAGE_METRICS. Also, apply the latest patches as this view does not work a 100% correctly without them.


First of all, mysql (and other sql databases) are RDMS meaning that they are based in the relational model. This means that the design should be about entities and their relations. In your case:

Entities: locations, types.

Relation: one location can be of one type (if I have understand you correctly). This is a one-to-one relation.

The best way to store this relation is as you propose at the begining:

create table tbl_locationcollections
(
   id int(11) PRIMARY KEY IDENTITY, --(Primary Key),
   name varchar(100) not null,
   cleanname varchar(100) not null,
   typeid int(11) not null (foreign key)
)

create tbl_locationcollectiontypes
(
   id int(11) PRIMARY KEY IDENTITY,  --(Primary Key)
   type varchar(100)
)

but then you propose something strange, some tables to relate each type with their locations. There is no need for this table. The relation is already set by the foreign key constraint. If you want to get all the locations of the same type, for example country, you will do it in a query:

SELECT *
FROM tbl_locationcollections
INNER JOIN tbl_locationcollectiontypes
ON typeid.tbl_locationcollections = id.tbl_locationcollectiontypes
WHERE type.tbl_locationcollectiontypes = 'country'

Benefits of this aproach:

-Readability

-Scalable: you can add more types with out changing the database design

-Logic: you work with relations between entities, wich is the same abstraction our brain makes.

Hope it helps ;)
If you want to know how to store one-to-many or many-to-many relations that is another topic, feel free to ask!


Losing customization capability with managed servers (AWS or Azure)?

For platform-as-a-service (PaaS) databases, you don't get to design the underlying hardware such as CPU, I/O or memory configuration.

You can only choose a service tier that gives you a general idea of the overall performance. On Azure SQL Database, the different service tiers are defined by something Microsoft calls "DTUs", a performance metric that blends disk and CPU performance, but you cannot prioritize one over the other. I suspect AWS RDS works similarly in this regard.

The reason (personal opinion) why you would choose PaaS over IaaS (installing SQL Server on a VM) is that you don't have to maintain and patch any infrastructure. Your colleague is correct in saying that this comes at a price: less freedom.

The upside with PaaS is that you can scale it up and down whenever you need, minute by minute or hour by hour.


I do not use Oracle, but using a STD syntax it should work:

/* aggregated sales by year-month */
SELECT
    T.CALENDAR_YEAR AS Year,
    T.CALENDAR_MONTH_DESC AS Month,
    SUM(S.AMOUNT_SOLD) AS AmountOfMoney
FROM
    SALES S
JOIN
    TIMES T
    ON T.TIME_ID = S.TIME_ID
WHERE S.PROD_ID = (SELECT PROD_ID
                   FROM (
                         SELECT
                            S.PROD_ID,
                            SUM(S.AMOUNT_SOLD) AMOUNT
                         FROM
                            SALES S
                         GROUP BY S.PROD_ID
                         ORDER BY SUM(S.AMOUNT_SOLD) DESC
                        ) BS
                   WHERE ROWNUM = 1
                  )
GROUP BY
    T.CALENDAR_YEAR,
    T.CALENDAR_MONTH_DESC
ORDER BY
    T.CALENDAR_YEAR,
    T.CALENDAR_MONTH_DESC;

db<>fiddle here


SELECT DISTINCT sectionname ,
               sectionid
FROM   section
       LEFT JOIN classsection
              ON classsection.fk_section_sectionid = section.sectionid
       LEFT JOIN classes
              ON classid = classsection.fk_class_classid
       LEFT JOIN course
              ON courseid = fk_course_courseid
WHERE  coursename = 'School'

i just joined section and classses first and then joined courses to see the department name which sections are belong to which classes and it worked kudos


`show procedure status` doesn't list all of the procedures?

Only thing I can think of is that your mysql.proc database might be corrupt. Try with:

ANALYZE TABLE mysql.proc;

If corrupt, you can try to repair it:

REPAIR TABLE mysql.proc;


Can I optimize a left join for a single match?

Wiki answer from a comment on the question by @ypercube:

You can use a filtered index:

CREATE UNIQUE INDEX a_id_uq
ON b (a_id)
WHERE a_is IS NOT NULL;

See Filtered Index Design Guidelines


Why would page level locking be disabled on an Index?

By default, [allow_page_locks] is set to 1, so, someone must have changed it in the past to avoid that level of locking.

  If I go ahead and make changes and allow page level locking, it is not going to break anything, right?

I would test this in a non-prod environment prior to changing.  I would think the outcome would be unexpected locking.

  Is there a script?

You could use this script to identify those indexes and then change it to modify those.  But, I would think there was probably a good reason for the change in the first place.

Select Indx.name,
    Indx.type_desc,
    Indx.is_disabled,
    Indx.allow_page_locks,
    Indx.allow_row_locks
from sys.indexes Indx
left outer join sys.objects OBJ
    on OBJ.object_id=Indx.Object_id
where allow_page_locks = 0


Why does a comparison between CITEXT and TEXT fail?

Do I really need to cast these columns to CITEXT to get comparisons to work?

Yes, that's what you have to do. That's not how the operator works. You're supposed to set this type on the table so you do not have to this. If one says it's case insensitive text, and the other says it's case-sensitive text, the case-sensitive text wins. The reason why the first example works is because

SELECT * FROM citext_test WHERE value = 'One';

is essentially the same as

SELECT * FROM citext_test WHERE value = 'One'::unknown;

Internally, that gets promoted by the operator to citext. You can disambiguate with the cast,

SELECT * FROM citext_test WHERE value = 'One'::citext;

or the functional form,

SELECT * FROM citext_test WHERE citext_eq(value, 'One');

But the apropos solution would be to ALTER TABLE for the table you're joining to and set its type to citext too.


Excel uses compression internally when saving a file, that is the most likely reason why you're seeing a much smaller Excel file.
I would recommend compressing the .bcp file prior to sending it via email.  Use a command-line compressor such as 7-Zip.
Also, I would recommend using the BCPs Native (-n) or Unicode Native format, via the -N option. Be careful using -c mode if you have Unicode (nvarchar/nchar) data types in your export.

Native format maintains the native data types of a database. Native format is intended for high-speed data transfer of data between SQL Server tables. If you use a format file, the source and target tables do not need to be identical. The data transfer involves two steps:

Bulk exporting the data from a source table into a data file
Bulk importing the data from the data file into the target table.

The use of native format between identical tables avoids unnecessary conversion of data types to and from character format, saving time and space. To achieve the optimum transfer rate, however, few checks are performed regarding data formatting. To prevent problems with the loaded data, see the following restrictions list.

From the Books Online for bcp.exe command-line utility:

-n
Performs the bulk-copy operation using the native (database) data types of the data. This option does not prompt for each field; it uses the native values.

For more information, see Use Native Format to Import or Export Data (SQL Server).

-N
Performs the bulk-copy operation using the native (database) data types of the data for noncharacter data, and Unicode characters for character data. This option offers a higher performance alternative to the -w option, and is intended for transferring data from one instance of SQL Server to another using a data file. It does not prompt for each field. Use this option when you are transferring data that contains ANSI extended characters and you want to take advantage of the performance of native mode.

For more information, see Use Unicode Native Format to Import or Export Data (SQL Server).


PL/SQL function to receive a number and return its binary format

This is not an Oracle or PL/SQL issue, but a matter of implementing the proper algorithm.

Here is an example:

https://www.orafaq.com/wiki/Binary

CREATE OR REPLACE FUNCTION dec2bin (N in number) RETURN varchar2 IS
  binval varchar2(64);
  N2     number := N;
BEGIN
  while ( N2 > 0 ) loop
     binval := mod(N2, 2) || binval;
     N2 := trunc( N2 / 2 );
  end loop;
  return binval;
END dec2bin;
/

SQL> SELECT dec2bin(22) FROM dual;
DEC2BIN(22)
----------------
10110

The overhead of LISTAGG + hierarchical query + other SQL functions is bigger than the overhead of a simple PL/SQL function.

SQL> with g as (select * from dual connect by level <= 1000) select count(distinct dec2bin(rownum)) as bincd from g,g;

     BINCD
----------
   1000000

Elapsed: 00:00:13.75

with g as (select * from dual connect by level <= 1000),
g2 as (select rownum as r from g, g)
select count(distinct bin) as bincd from (
select (SELECT LISTAGG(SIGN(BITAND(r, POWER(2,LEVEL-1))),'')
       WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2, LEVEL-1)<=r
) as bin
from g2
);

     BINCD
----------
   1000000

Elapsed: 00:00:35.53

And this is without the UDF Pragma. On 12c and above the usage of the function gets slightly faster with the addition of PRAGMA_UDF.

CREATE OR REPLACE FUNCTION dec2bin (N in number) RETURN varchar2 IS
  PRAGMA UDF; -- <==================================================== MAGIC
  binval varchar2(64);
  N2     number := N;
BEGIN
  while ( N2 > 0 ) loop
     binval := mod(N2, 2) || binval;
     N2 := trunc( N2 / 2 );
  end loop;
  return binval;
END dec2bin;
/

SQL> with g as (select * from dual connect by level <= 1000) select count(distinct dec2bin(rownum)) as bincd from g,g;

     BINCD
----------
   1000000

Elapsed: 00:00:12.01


Log Shipping is a high availability feature of SQL Server where a database copy from a specific point in time is made available to a different server using full backups + scheduled transaction log backups delivered by an internal mechanism of SQL Server.
It's composed of some SQL jobs and some executables and is accessible by a wizard - right click a database -> Properties -> Transaction Log Shipping.

It's no new functionality of SQL Server (if I recall correctly it's at least from SQL 2000).

You can read about it here: Log Shipping Overview.


If you don't like using primary keys that are auto generated, there is another way.

Instead of indexing the userID (which is large and slow to index, because it is a string) you can instead index the hash value of userID. You can then do:

CREATE TABLE Users (
  ID BIGINT
  , Username VARCHAR(255)
)

And you can find the user with:

$user = User::find(hash(user1));

You just need to pick a good hash function. A great candidate is MurmurHash (google it).


Does an empty entity make any sense in an entity-relationship diagram?

This is fine as long as all you need is an index for the class, though it may be very difficult to troubleshoot exactly what is meant by a particular record in either future develompent or by a user for any reason at all.

If you want to be able to define anything about the class, say a name or other defining attribute, you'd just want to add this to the table as well.

For example:

TABLE SchoolClass (classNumber INTEGER PRIMARY KEY, classDescription TEXT)


Update statement errors, parenthesis, failed validation ORACLE

Editor, that higlights matching braces should do the trick.
There are just too many of them. And... You left out the default values in outer nvl() or nvl keyword itself?

...
NVL(
 "nvl here?"(
  (SELECT F.FLI_PRICE FROM FLIGHT F WHERE F.FLI_ID = R.IN_FLIGHT_ID), 0
 )
 * NVL(R.IN_FLIGHT_SEATS_NO,0)
",default value here?") + ...

Go, have a break. This happens to me very often :-)


Cannot connect database as sys as sysdba using sqlplus

If the password contains special characters such as @, enter the password between double quotes:

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 11 15:41:40 2019
Version 19.5.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0

SQL> connect sys/a@abcd as sysdba
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Warning: You are no longer connected to ORACLE.
SQL> connect sys/"a@abcd" as sysdba
Connected.
SQL>


Combine duplicate rows on column1 sum column2

SELECT MIN(id) id,
       ParentId,
       ProductId,
       SUM(Quantity) Quantity
FROM [source_table_name]
GROUP BY ParentId,
         ProductId


MySQL: What exact commands does each privilege level allow?

show and set are commands of the MySQL client; you can't prevent users from using them.

set global is only allowed if the user has the super system privilege. See dev.mysql.com/doc/refman/5.1/en/grant.html#grant-privileges


what is correct way of adding a user in admin with role userAdminAnyDatabase doing upgrade to 2.6

IMPORTANT
  The userAdminAnyDatabase user can grant itself and any other
  user full access to the entire MongoDB instance. The credentials to
  log in as this user should be carefully controlled.

  Users with the userAdmin and userAdminAnyDatabase privileges are not
  the same as the UNIX root superuser in that this role confers no
  additional access beyond user administration. These users cannot
  perform administrative operations or read or write data without first
  conferring themselves with additional permissions.

4 Steps for upgrading MongoDB v2.4 to v2.6

Ensure that at least one user exists in the admin database with the role userAdminAnyDatabase.
Upgrade client libraries,compatible with v2.6
Upgrade all MongoDB process to 2.6
Connect and authenticate to the mongod instance for a single deployment or a mongos for a sharded cluster as an admin database user with the role userAdminAnyDatabase.Use the authSchemaUpgrade command in the admin database to update the user data using the mongo shell.

See Also:

Upgrade MongoDB to 2.6
https://docs.mongodb.org/v2.6/release-notes/2.6-upgrade/

Create a User Administrator
https://docs.mongodb.org/v2.4/tutorial/add-user-administrator/


A snapshot is great for these scenarios where you might have used a database backup in the past:

before doing database schema upgrades
before running adhoc data manipulation queries
maintaining a historical snapshot you might want to analyze later
materializing data on standby servers

In some of the above scenarios, a transaction is a better option to automatically roll back from problems, but there are sometimes cases where the query might succeed but have unintended/unforeseen consequences, so a snapshot is a good standby option to revert a bad SQL script.  Of course, if this is a live system with commits occurring actively, then you wouldn't be able to go back to the snapshot without losing any commits that occurred since the snapshot was made(another reason transactions are better protection when running sql scripts against a live system).

Since the snapshot occupies less disk space, and are faster to create than a backup, they are a better option than backups in these cases.

You would still use traditional database backups to protect against disk array failures, malware, natural disasters, etc.

http://www.simple-talk.com/sql/database-administration/sql-server-2005-snapshots/


Can I rely on reading SQL Server Identity values in order?

When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the clustered index, where an external observer could see a newer Identity value inserted by a concurrent transaction?

Yes.

The allocation of identity values is independent of the containing user transaction. This is one reason that identity values are consumed even if the transaction is rolled back. The increment operation itself is protected by a latch to prevent corruption, but that is the extent of the protections.

In the specific circumstances of your implementation, the identity allocation (a call to CMEDSeqGen::GenerateNewValue) is made before the user transaction for the insert is even made active (and so before any locks are taken).

By running two inserts concurrently with a debugger attached to allow me to freeze one thread just after the identity value is incremented and allocated, I was able to reproduce a scenario where:

Session 1 acquires an identity value (3)
Session 2 acquires an identity value (4)
Session 2 performs its insert and commits (so row 4 is fully visible)
Session 1 performs its insert and commits (row 3)

After step 3, a query using row_number under locking read committed returned the following:

In your implementation, this would result in Checkpoint ID 3 being skipped incorrectly.

The window of misopportunity is relatively small, but it exists. To give a more realistic scenario than having a debugger attached: An executing query thread can yield the scheduler after step 1 above. This allows a second thread to allocate an identity value, insert and commit, before the original thread resumes to perform its insert.

For clarity, there are no locks or other synchronization objects protecting the identity value after it is allocated and before it is used. For example, after step 1 above, a concurrent transaction can see the new identity value using T-SQL functions like IDENT_CURRENT before the row exists in the table (even uncommitted).

Fundamentally, there are no more guarantees around identity values than documented:

Each new value is generated based on the current seed & increment.
Each new value for a particular transaction is different from other concurrent transactions on the table.

That really is it.

If strict transactional FIFO processing is required, you likely have no choice but to serialize manually. If the application has less oneous requirements, you have more options. The question isn't 100% clear in that regard. Nevertheless, you may find some useful information in Remus Rusanu's article Using Tables as Queues.


Just add a WHERE clause to your select that eliminates the duplicates:

INSERT /*+ APPEND */ INTO db1.myTable (
   SELECT * FROM db2.myTable b WHERE NOT EXISTS
      (SELECT 1 FROM db1.myTable a WHERE a.ID = b.ID)
   );

If you don't have a field like ID that is unique across the two tables, then you will need to compare more fields, perhaps all of them.


The best solution depends on your RDBMS, but in general you shouldn't need to manually sanitize the captions because the database will have a "stopword list" or "stoplist" which is used to exclude words from a full-text index (not the same as a regular clustered/non-clustered index). You can modify that list to specify which words are excluded from the full-text index. In most databases, the list would already include prepositions, pronouns and other noise words, meaning a search for birds AND flight would return a caption of "birds in flight" (assuming it exists). Additionally, most systems can stem the indexed terms and give you the ability to search on inflections of a term, e.g. searching for inflections of "fly" will return flying, flew, flown, etc.

If the captions are the keywords, you don't need to parse keywords from them; just add a full-text index to the caption column. If you have or want to add more keywords in addition to the captions, you can index them separately and use them to augment or modify the caption search using the RDBMS' built-in numeric ranking/scoring of results - a hit on a keyword could increase the rank while a miss could do nothing or even decrease it.

If the captions have a 1:1 relationship to images (which they usually do), you don't even need a separate table for those; just store the caption in another column of the images table. Additional keywords could be normalized and stored in a separate table, which would require an intermediate join table with foreign keys from images and keywords. However, this adds complexity and you may find it better to just store a space-delimited list of words in another column in the images table.

The solution you select will depend on several factors including business needs, performance requirements and use cases, i.e. what types of searches you need to support: exact phrase, Boolean AND/OR, fuzzy matches like SOUNDEX, etc. Sometimes full-text indexing isn't even necessary. For instance, if stemming and noise word exclusion aren't important, a search using LIKE with wildcards may suffice.


The dbo.Queue table exists, but does not have the SchemaName column.
This can be due to a custom dbo.Queue table that has nothing to do with Ola's solution. You could rename the table in the maintenance plan or drop the existing tables.

Recreating the error

Step 1: Recreate the maintenance solution Procedure

Step 2 Create the dbo.Queue table without the SchemaName

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Queue]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Queue](
  [QueueID] [int] IDENTITY(1,1) NOT NULL,
  --  [SchemaName] [sysname] NOT NULL,
  [ObjectName] [sysname] NOT NULL,
  [Parameters] [nvarchar](max) NOT NULL,
  [QueueStartTime] [datetime] NULL,
  [SessionID] [smallint] NULL,
  [RequestID] [int] NULL,
  [RequestStartTime] [datetime] NULL,
 CONSTRAINT [PK_Queue] PRIMARY KEY CLUSTERED
(
  [QueueID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
END
GO

Step 3: Create the dbo.QueueDatabase table

Step 4: Try to take a backup

USE [MNGDB]
GO

EXEC  [dbo].[DatabaseBackup]
        @Databases = N'MNGDB',
        @Directory = N'G:\Data',
        @BackupType = N'FULL'

Error:

  Msg 207, Level 16, State 1, Procedure dbo.DatabaseBackup, Line 1545
  [Batch Start Line 5] Invalid column name 'SchemaName'. Msg 207, Level
  16, State 1, Procedure dbo.DatabaseBackup, Line 1555 [Batch Start Line
  5] Invalid column name 'SchemaName'. Msg 207, Level 16, State 1,
  Procedure dbo.DatabaseBackup, Line 1561 [Batch Start Line 5] Invalid
  column name 'SchemaName'.

Why

The query fails because the table exists but the schemaname column does not.
If table does not exist, it succeeds.

Testing the part that causes the error & resolving it

  DECLARE @DatabasesInParallel char(1)  = 'N', @SchemaName varchar(255) = 'test',  @ErrorMessage nvarchar(max)
  IF @DatabasesInParallel = 'Y'
  BEGIN

    BEGIN TRY

      SELECT  QueueID
      FROM dbo.[Queue]
      WHERE SchemaName = @SchemaName

    END TRY

    BEGIN CATCH
      IF XACT_STATE() <> 0
      BEGIN
        ROLLBACK TRANSACTION
      END
      SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') + CHAR(13) + CHAR(10) + ' '
      RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
    END CATCH

  END

Error message

Msg 207, Level 16, State 1, Line 1892
Invalid column name 'SchemaName'.

Dropping the two previously created tables

drop table dbo.[QueueDatabase];
drop table dbo.[Queue];

and rerunning the statement produces no error:

  Commands completed successfully.


Dead Raid Card SQL Server Running in Memory

long story short raid card died so volume is not accessible.

Definitely not a good thing, but is it possible to buy a replacement? If it's on the motherboard directly, can the motherboard be replaced?

  SQL service is still running and applications accessing the databases are still responding.

This will work so long as the data in the files that are now not accessible are not asked for any data. If they are, it'll return an error to the session and write some information in the errorlog. This may currently be happening.

If, at some point, data from this volume was in memory and subsequently changed then you should see errors in the errorlog from checkpoints failing to write to the disk. This leads to a few conclusions but it's hard to say without looking at the database metadata. Generally speaking, if you aren't seeing any failures writing to disk in the eventlog, errorlog, or sessions (if you're looking at that) it's possible to conclude that some of the following may be true:

The database files aren't in use and aren't needed
The database files were in use but no data has changed
The database files aren't in use but will be used at a later point (month end, etc.)
Some data from the database files are in memory but not changed
No data from the database files are in memory
None of the files are log files or they are log files but no VLFs are in use

This list seems to be very contrary but multiples of them can be true at the same time. Again, without seeing the actual metadata it's hard to say.

  The backups aren’t upto date of course.

This is actually the root issue (I know you believe it's the RAID Card) because with backups it could have been restored to a different server, DNS updated, Logins created, and everyone go about their normal day. This isn't a put down, merely an example.

  My question is what does mssql store in memory, as my understanding is it stores everything in ram and then dumps to file?

You can think of it that way, yes. The very oversimplified version is that it's initially read into memory if it existed on disk, changed in memory, and some point later written back to disk if it's a disk based table.

  Is the data recoverable by specialist if I do a raw ram dump?

I'm not sure this would be a suitable way to go to be honest. If the files being offline aren't currently spewing tons of errors into the logs and clients are being returned tons of errors due to the inability to retrieve the data... you may not need (at this point in time) the data from those files. Additionally, swapping out cards should solve the immediate issue.

I believe the question is can you shutdown without losing any data? If so, and you aren't spewing tons of errors as listed above then it's more than likely you won't lose much if anything at all - that's not a guarantee
and YMMV but if you look at all the other logs it should give you an idea. Here is the main point, if it's in memory right now and has been changed then the log record exists whether or not it was successfully flushed to disk at some point later which means upon database startup it should redo the transactions.

Ask your clients to disconnect or stop using the services, stop the services or applications, then deal with the database. The longer this continues to be up and running the higher the risk of something much more catastrophic happening.


How to find which tables were deleted (in Oracle)

If you have the recyclebin turned on in your database you can query the DBA_RECYCLEBIN view to find which objects were dropped within a certain time period.

SELECT owner,
       original_name,
       operation,
       droptime,
       can_undrop
FROM   dba_recyclebin
WHERE  TO_DATE (droptime, 'YYYY-MM-DD:HH24:MI:SS') BETWEEN SYSDATE - 3
                                                       AND SYSDATE

You can issue a FLASHBACK command to restore the table;

FLASHBACK TABLE <you table's original name> TO BEFORE DROP

Alternatives are to flashback to a specific SCN, TIMESTAMP, etc.

See https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9012.htm#SQLRF01802 and http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables011.htm#ADMIN01511 for some more reading (it never hurts to read)


Designing a database structure for companies and stock owners

One of the topics that I commonly touch in my database design answers is that before (a) thinking about the technical aspects of a relational database —e.g., the declaration of tables, columns, etc.— it is highly recommendable to first (b) define all the characteristics of the business context of interest with precision. This implies identifying the entity types, their properties and every significant association that exists among them.
The group of definitions about said elements is commonly referred to as as business rules, which make up the applicable conceptual model.
Also, I esteem that there are other important subjects about (1) data management and (2) relational design and manipulation that (3) may help to clarify some aspects you brought up in the question and via comments as well. I will integrate all these points as the answer goes progressing.
Conceptual level
Let us start reevaluating the relevant conceptual level of abstraction. In this regard, it is important to formulate a series of statements that describe the busines environment. So, in your specific case, keeping the business rules deliberately as simple as possible:

A Company is primarily identified by its Id
A Company is alternately identified by its Name
A Company is established on its FoundingDate
A Person is primarily identified by his or her Id
A Person is alternately identified by the combination of his or her FirstName, LastName, GenderCode, BirthDate and BirthPlace
A Person keeps exactly-one Address
A Person uses exactly-one PhoneNumber
A Company is owned by one-to-many People
A Person owns zero-one-or-many Companies

Illustrative IDEF1X model
Then based on the business rules formulated above, one can create a deliberately and relatively simple IDEF1Xa model like the one shown in Figure 1, in order to have a graphical device that consolidates most of the significant features in a single resource:

a Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in december 1993 by the United States National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the early theoretical works authored by the originator of the relational model, i.e., Dr. E. F. Codd; on (b) the Entity-Relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
As demonstrated, in an IDEF1X model we can start including technical considerations like the indications of properties or attributes that must be constrained via PRIMARY, ALTERNATE and FOREIGN KEY definitions (for brevity: PK, AK and FK, respectively) at the logical level.
The last two of the business rules previously discussed show that there is a many-to-many (M:N) association or relationship between the entity types Company and Person, which reveals the existence of an associative entity type that I called CompanyOwner (which might very well be denominated CompanyShareHolder or something that fitts better the terminology used in the business domain).
One of the properties of paticular importance in the present modeling excercise is the one I named StockShareQuantity (portraying a sum of Shares), because it comes about exclusively in the context of a CompanyOwner association; in this way, it does not belong to a Person nor to a Company, but to the connection that may arise between these two separate entity types.
As specified, each CompanyOwner occurrence or instance is identified by the combination of its CompanyId and OwnerId values, so these properties are highlighted as a composite PK in the entity type depiction. The CompanyOwner.CompanyId property is distinguished with a FK that points to Company.CompanyId, while CompanyOwner.OwnerId is noted with a FK that makes a reference to Person.PersonId.
Aiming to portray a slighlty more elaborate Person entity type, I decomposed Person.Name into FirstName and LastName, and I included the columns GenderCode, BirthDate and BirthPlace. Together, the combined values of all those properties are typically used to identify a Person in certain business scenarios, but you may simply be interested in keeping track of the Person.FullName, if that meets the requirements of your data usage, so you do not have to follow the same approach concerning the particular database under discussion.
Expository logical SQL-DDL structure
Subsequently, it is relatively more easy to declare a logical structure by means of the data definition language supplied by the database management system, in this case PostgreSQL, just like exemplified below:
-- You have to determine which are the most fitting
-- data types and sizes for all the table columns
-- depending on the business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient physical implementation settings; e.g.,
-- a good INDEXing strategy based on query tendencies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE Company ( -- Stands for an independent entity type
    CompanyId       INT       NOT NULL, -- You may like to set it with the SERIAL type to retain system-assigned and system-generated surrogate values.
    Name            TEXT      NOT NULL,
    FoundingDate    DATE      NOT NULL,
    Etcetera        TEXT      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(), -- You may also configure it so that the DEFAULT value is set by the CURRENT_TIMESTAMP function, if appropriate.
    --
    CONSTRAINT Company_PK PRIMARY KEY (CompanyId),
    CONSTRAINT Company_AK UNIQUE      (Name) -- Single-colum ALTERNATE KEY.
);

CREATE TABLE Person ( -- Denotes an independent entity type.
    PersonId        INT       NOT NULL, -- You may like to set it with the SERIAL type to retain system-assigned and system-generated surrogate values.
    FirstName       TEXT      NOT NULL,
    LastName        TEXT      NOT NULL,
    GenderCode      TEXT      NOT NULL,
    BirthDate       DATE      NOT NULL,
    BirthPlace      TEXT      NOT NULL,
    Address         TEXT      NOT NULL,
    PhoneNumber     TEXT      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(), -- You may also configure it so that the DEFAULT value is set by the CURRENT_TIMESTAMP function, if appropriate.
    --
    CONSTRAINT Person_PK PRIMARY KEY (PersonId),
    CONSTRAINT Person_AK UNIQUE      ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate,
        BirthPlace
    )
);

CREATE TABLE CompanyOwner ( -- Represents an associative entity type or M:N association. Attaching an extra column to hold system-generated and system-assigned surrogate values to this table is superfluous.
    CompanyId          INT       NOT NULL,
    OwnerId            INT       NOT NULL,
    StockShareQuantity INT       NOT NULL,
    Etcetera           TEXT      NOT NULL,
    CreatedDateTime    TIMESTAMP NOT NULL DEFAULT NOW(), -- You may also configure it so that the DEFAULT value is set by the CURRENT_TIMESTAMP function, if appropriate.
    --
    CONSTRAINT CompanyOwner_PK          PRIMARY KEY (CompanyId, OwnerId), -- Composite PRIMARY KEY.
    CONSTRAINT CompanyOwnerToCompany_FK FOREIGN KEY (CompanyId)
        REFERENCES Company (CompanyId),
    CONSTRAINT CompanyOwnerToPerson_FK  FOREIGN KEY (OwnerId)
        REFERENCES Person (PersonId),
    CONSTRAINT StockShareQtyIsValid_CK  CHECK       (StockShareQuantity >= 0) -- Appears to be required.
);

As expounded, in this deliberately and relatively simple logical structureb (which is very similar to the one portrayed in your diagram although with a few important differences):

each base table denotes an individual entity type, which prevents ambiguities;
each column stands for a single property of the respective entity type;
a specific data type is fixed for each column in order to ensure that all the values it retains belong to a particular set (that you will have to adapt to adapt to your exact necessities, choosing the most fitting types from the options provided by PostgreSQL), be it INT, TIMESTAMP, TEXT, etc.; and
multiple constraintsc, d are set up (declaratively) in order to guarantee that the rows retained in all the tables comply with the rules determined in the conceptual model.

b I have uploaded a db<>fiddle and a SQL Fiddle, running on PostgreSQL 9.6, that contain the DDL structure and constraints I defined along with sample data so that you can test it “in action”.
c Since (i) the structure of the data —i.e. the tables, columns and types— and (ii) the constraints —e.g., PKs, FKs and CHECKs— imposed on such a structure to ensure that it accepts only valid data are (iii) two diferent —but related— factors of the configuration of a relational database, I suggest (iv) placing the structure declarations apart from the constraint declarations —though the SQL language and the dialect supplied by PostgreSQL permit declaring “in-line” column constraints, if you wish to opt for that option—. In fact, it would be even better to move the constraint definitions outside of the CREATE TABLE … (…); statements, so this point deserves thorough evaluation.
dAn exception that I make regarding the separation of structure from constraints is that I in effect fix NOT NULL in “in-line” column declarations because it is a special kind of constraint. To keep things as brief as possible, permitting NULL marks is an approach  —considered controversial on theoretical grounds— introduced by the SQL language designers to try manage the Missing Information Problem, which is a rather broad subject. NULL marks, in turn, imply dealing with another problem known as Three-Valued Logic. According to the relational theory, a table with one or more columns that enclose NULL marks (1) does not represent a mathematical relation —said marks are indicators about the absence of a value, then they are not domain values—, therefore (2) such a table is not going to “behave” as a relation when it is operated upon. Consequently, while having columns maintaining NULL markers is a possibility, I recommend that you study trustworthy material about these topics so that you can make an informed decision knowing all the implications and methods with respect to the management of Missing Data.
The Person.Address Column
In case that you are interested in manipulating certain encoded parts of the Address column, let us say PostCode, you might like to evaluate decomposing it in several columns and moving the pertinent information to a separate table that, perhaps, would be connected with the Person table via a column with a FK constraint.
Declaring a table for each Company?
You brought up the following thoughts in comments:

As I have stated the design will change because […] there will be multiple companies and people can own multiple companies and therefore show up twice. My original question was how to design this. I thought about making a table for each company which I think would be my last resort but I still have no other guidance or tips on how to do this.

No, you should not create a table for each Company, because that would be quite suboptimal.
In that respect, it can be said that every row kept in each of the previously presented base tables is an assertion about a certain conceptual-level entity that belongs to a certain type, i.e., either a Company or a Person or a CompanyOwner.
Hence, a row about a given Company is held only once in the Company table and a row about a given Person is maintained only once in the Person table. Then, since a Person can own zero, one or more Companies, each row about an exact connection that a Person has with respect to an individual Company is enclosed only once in the CompanyOwner table.
A row in the CompanyOwner table does not contain the whole information about a Person, it only contains (1) one value in the OwnerId column —constrained as a FK— that makes a reference to (2) one value retained in the Person.PersonId column —constrained as the PK—. A certain CompanyOwner.OwnerId value may be repeated multiple times, and so may happen with a CompanyOwner.CompanyId value too, but that is not problematic. As exemplified, the possibility of repeating the same combination of (CompanyId, OwnerId) values is prevented by way of the CompanyOwner composite PK definition.
Derivable information
A set of base tables is definitely not a fixed structure it when it comes to a relational database because, apart from being susceptible to extensions and adaptations, the base tables help in deriving new tables that are not configured at design time.
Let us suposse that you have populated your database tables with the sample data via the following INSERT operations:
INSERT INTO Company
    (CompanyId, Name, FoundingDate, Etcetera)
VALUES
    (1748, 'Database Modeling Inc.', '1985-06-30', 'Foo'),
    (1750, 'Application Programming Co.', '1987-10-14', 'Bar');

INSERT INTO Person
    (PersonId, FirstName, LastName, BirthDate, BirthPlace, GenderCode, Address, PhoneNumber)
VALUES
    (1, 'Edgar', 'Codd', '1923-08-19', 'Fortuneswell, UK', 'M', 'IBM Research Laboratory K01/282, 5600 Cottle Road, San Jose, CA, USA', '01-800-17-50-17-50'),
    (2, 'Alan', 'Turing', '1912-06-23', 'Maida Vale, UK','M', 'National Physical Laboratory, Hampton Road, Teddington, TW11 0LW, England', '01-800-17-48-17-48'),
    (3, 'Grace', 'Hopper', '1906-12-09', 'New York City, USA', 'F', 'Navy’s Office of Information Systems Planning, USA.', '01-800-17-50-17-50'),
    (4, 'Diego', 'Velázquez', '1599-06-06', 'Seville, Spain', 'M', 'Palacio Real, Madrid, Spain', '01-800-17-50-17-50'),
    (5, 'Michelangelo', 'Buonarroti', '1475-03-06', 'Caprese, Italy', 'M', 'Sistine Chapel, Vatican City State', '01-800-17-50-17-50');

INSERT INTO CompanyOwner
    (CompanyId, OwnerId, StockShareQuantity, Etcetera)
VALUES
    (1748, 1, 2500, 'U'),
    (1750, 1, 2500, 'V'),
    (1750, 2, 8000, 'W'),
    (1750, 3, 3580, 'X'),
    (1748, 4, 12899, 'Y'),
    (1750, 5, 12899, 'Z');

After that, if you want to, e.g., produce a table that comprises data about the Owners of all the Companies, you can declare a VIEW like the following one:
CREATE VIEW CompanyAndOwner AS
    SELECT C.CompanyId,
           C.Name AS CompanyName,
           P.PersonId,
           P.FirstName,
           P.LastName,
           P.BirthDate,
           P.BirthPlace,
           P.GenderCode,
           P.Address,
           P.PhoneNumber,
          CO.StockShareQuantity,
           P.CreatedDateTime
        FROM Person P
        JOIN CompanyOwner CO
          ON CO.OwnerId = P.PersonId
        JOIN Company C
          ON C.CompanyId = CO.CompanyId;

Then you can express successive operationsc that SELECT directly FROM that VIEW; e.g.:
SELECT *
  FROM CompanyAndOwner
 WHERE CompanyId = 1750;

and
SELECT CompanyName,
       FirstName AS OwnerFirstName,
       LastName  AS OwnerLastName,
       StockShareQuantity
  FROM CompanyAndOwner
 WHERE CompanyId = 1750;

and
SELECT *
  FROM CompanyAndOwner
 WHERE CompanyId = 1748;

and
SELECT CompanyName,
       FirstName AS OwnerFirstName,
       LastName  AS OwnerLastName,
       StockShareQuantity
  FROM CompanyAndOwner
 WHERE CompanyId = 1748;

Or you can SELECT directly FROM a base table as well:
SELECT COUNT(OwnerId) AS OwnedCompaniesQuantity
   FROM CompanyOwner
  WHERE OwnerId = 1;

etc.
e All the data manipulation operations included here are comprised in the db<>fiddle and the SQL Fiddle previously linked to, so that you can analyze the result sets that they produce.
Company Address(es) and Phone Number(s)
In the series of deliberations that we engaged in via comments I asked you whether you interested in retaining Addresses and PhoneNumbers that belong to Companies, and your response was the following one:

A person can only have one phone and one address. Same goes for the company, because I have made a script that finds the phone based on this information. The way I differentiate between knowing if a entity is a company or person is by using a column called F_Org which will be larger than 4 digits for companies. This is all handled by the script.

I am not sure if that means that the Address(es) and Phone Number(s) of a Company are stored in an application program component (perhaps a file with records and fields, or something similar) but, if you are in fact handling that information, you should make use of the right tool for the job, i.e., include those aspects in the database structure and take advantage of the instruments provided by the database management system (i.e., PostgreSQL) so that you can administer the concerning data in an optimal manner (e.g., by virtue of logical-level operations that are based on the relational algebra and declarative constraints, supported at the physical level by a powerful set processing engine).
Feasible DDL structure extension representing a supertype-subtype relationship
Thus, in case that you determine that, in your business domain

the same Address occurrence can be kept by more than one Person and/or Company instance,

and

the same PhoneNumber occurrence can be used by more than one Person and/or Company instance,

you may like to analyze the diagrams I included in this answer for a question on a scenario that is very similar to the one under consideration (with, among other features, the Party supertype representing either the Organization or Person subtypes, People playing the Role of Owners of Organizations, and Addresses and PhoneNumbers linked with Organizations and People via the supertype), as they can be used as a reference for a tentative extension.


The connection encryption happens at the SQL Server instance level, not at the level of individual databases.

You can have encryption turned on at the instance, in which case every connection to it will be encrypted, or you can set a client to require encryption. Keep in mind every instance such a client connects to will have to have a certificate which the client trusts. Some light reading: https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-microsoft-management-console

So to answer your question directly, yes, if one client connects to an instance with encryption and others don't, that one client will require more processing power than the others. Having said that, unless there's a configuration problem or the server is having processing capacity issues, nobody should notice.


Is this temp table behaviour documented?

This is because while executing the SQL function the entire function body is parsed and executed. But with the PLPGSQL function statement by statement is parsed and executed. Because of this reason the SQL function not able to see the temp table which is created during the function execution.

In other words this applies to other objects, not just temp tables. This is easily demonstrated too:

begin;
--
create function pg_temp.f() returns integer language sql as $$select 1;$$;
create function g() returns integer language sql as $g$
  create or replace function pg_temp.f() returns integer language sql as $$select 2;$$;
  select pg_temp.f();
$g$;
--
select g();
/*
 g
---
 1
*/
select g();
/*
 g
---
 2
 */
--
rollback;

or even:

begin;
create schema stack;
set search_path to stack;
create temp table foo(id) on commit drop as values (1);
select * from foo;
/*
 id
----
  1
*/
savepoint s;
create function f() returns setof integer language sql set search_path = stack, pg_temp as $$
  create table foo(id) as values (2);
  select id from foo;
$$;
select * from f();
/*
 f
---
 1
*/
rollback to s;
create function f() returns setof integer language plpgsql set search_path = stack, pg_temp as $$
begin
  create table foo(id) as values (2);
  return query select id from foo;
end;
$$;
select * from f();
/*
 f
---
 2
*/
rollback;

The suggestion on pgsql-bugs is that the documentation is amended to make this behaviour explicit.

edit:

A change to the documentation has apparently been comitted but isn't yet showing in the 9.4 docs


Here is an example of how you can pick the latest price for a part.

declare @Parts table (PartID int, PartName varchar(50))
declare @PartsLog table (PartID int, TransactionDate Datetime, Price decimal(11,2))

insert into @Parts(PartID, PartName) values
    (1,'Hammer'),
    (2,'Drill')
insert into @PartsLog(PartID,TransactionDate,Price) values
    (1,'2018-01-01',100.00),
    (1,'2018-07-01',200.00),
    (2,'2018-01-01',50.00),
    (2,'2018-03-01',75.00)

SELECT p.PartID
    ,p.PartName
    ,pl.price
FROM @Parts p
JOIN @PartsLog pl
    ON pl.PartID = p.PartID
        AND pl.TransactionDate = (
            SELECT max(TransactionDate)
            FROM @PartsLog
            WHERE PartID = pl.PartID
            )

| PartID | PartName | price  |
|--------|----------|--------|
| 1      | Hammer   | 200.00 |
| 2      | Drill    | 75.00  |


Why does psql open two tcp connections to PostgreSQL?

This double connection depends on whether a password has to be submitted by the client (according to pg_hba.conf) and, if yes, whether the -W option is passed to psql.

In fact avoiding that second connection attempt is the only raison d'être of this option. According to psql manpage:

  -W
  --password

  Force psql to prompt for a password before connecting to a database.

  This option is never essential, since psql will automatically prompt for a password if the server demands password authentication.
  However, psql will waste a connection attempt finding out that the
  server wants a password. In some cases it is worth typing -W to avoid
  the extra connection attempt.

  Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial
  connection attempt.

I think that with this log from iptables, you're just noticing the mentioned "wasted connection attempt".
Note the time elapsed between the two connections:
[598519.458578] and [598520.875428]. Presumably this is the time it took you to enter the password: about 1.42 seconds.

If you try the same test with -W it should ask the password before attempting the connection and make only one connection.


Does Postgres offer a feature like “NEWSEQUENTIALID” in MS SQL Server to make UUID as primary key more efficient

uuid-ossp module
PostgreSQL uses the standardized UUID generation algorithms provided by ITU-T Rec. X.667, ISO/IEC 9834-8:2005, and RFC 4122. From the docs on uuid-ossp,

The uuid-ossp module provides functions to generate universally unique identifiers (UUIDs) using one of several standard algorithms. There are also functions to produce certain special UUID constants.
uuid_generate_v1() This function generates a version 1 UUID. This involves the MAC address of the computer and a time stamp. Note that UUIDs of this kind reveal the identity of the computer that created the identifier and the time at which it did so, which might make it unsuitable for certain security-sensitive applications.

So long as the MAC address does not change, you'll be golden.
That all said, I agree with @a_horse_with_no_name,

From my understanding this is only necessary in SQL Server because tables are stored in a clustered index which makes random insertions slower then with a heap table. Postgres has no such concept, so I don't think that would make a difference in Postgres

In fact, given the chance of fewer collisions and more security, I would take it. And to that I would use uuid_generate_v4()

uuid_generate_v4() This function generates a version 4 UUID, which is derived entirely from random numbers.


How to verify that postgres 9.3 db has no corruption?

If you have initialized your cluster with checksum, you can control this while backuping with pg_backrest.

  PostgreSQL has supported page-level checksums since 9.3. If page checksums are enabled pgBackRest will validate the checksums for every file that is copied during a backup.

You will find more information about cheksums here: https://www.postgresql.org/docs/current/static/app-initdb.html#APP-INITDB-DATA-CHECKSUMS

You can also make a pg_dump to /dev/null (if you don't have enough space to keep a logical dump). This should ensure you that you can read each data block.

PGDG had a discussion this year beacuse some wanted to activate checksum by default. You can find it there: http://www.postgresql-archive.org/Checksums-by-default-td5940158.html


Ora - 00088 error: command cannot be executed by shared server

Try adding (SERVICE=Dedicated) to your TNS entry on the client computer that you are using and then run your query again.

There could be other causes but this is the most common:  you are trying to access data using a shared connection when a dedicated connection is required.

Edit: Are there any other log or trace file entries when this error happens?


Visual studio 2010 missing BI 2010 (SSDT 2010)

Visual Studio 2010 is no longer supported for SQL Server Business Intelligence tools. You will not find a download link unless Google or some other site might have cached it somewhere.

You will need to download Visual Studio 2012 SSDT-BI or 2013, these support BI projects for SQL Server 2012 and 2014 respectively.


Can a trigger catch updates done by a select for update + lob.write()?

Copied comment :

You can't do that , at least up to 12c : "Using OCI functions or the DBMS_LOB package to update LOB values or LOB attributes of object columns does not fire triggers defined on the table that contains the columns or attributes." (docs.oracle.com/database/121/ADDCI/lobs.htm#ADDCI4383 )


Oracle SUM - ORA-00937: not a single-group group function

You should do one of two things:

OPTION #1: Keep only aggregate columns

SELECT SUM(Stages.Distance_Between)
FROM Journey, Journey_Stages, Stages
WHERE Journey.Journey_No=Journey_Stages.Journey_No
AND Journey_Stages.Stage_ID=Stages.Stage_ID;

OPTION #2: Add a GROUP BY clause

SELECT Journey.Journey_No, Stages.Stage_ID, SUM(Stages.Distance_Between)
FROM Journey, Journey_Stages, Stages
WHERE Journey.Journey_No=Journey_Stages.Journey_No
AND Journey_Stages.Stage_ID=Stages.Stage_ID
GROUP BY Journey.Journey_No, Stages.Stage_ID;


How to start my 9.2 data directory using an 8.4 server?

Only the same major version can read the data files.

You need to upgrade properly. To do that you'll have to install 8.4, then use pg_upgrade, or use pg_dump and pg_restore. See the manual for more information.

That said, the error actually has the versions the other way around - it says you're running 8.4 binaries and trying to point them at a datadir from 9.2. Verify this with postgres --version. If you turn out to have 8.4 binaries and a 9.2 datadir then pg_upgrade won't do you any good - you just need to install 9.2 (or use the correct PATH if it's already installed) and run the 9.2 datadir using 9.2 binaries.

Update given extra info in question:

It is very likely that both 8.4 and 9.2 are running fine on your machine, just on different ports.

sudo netstat -ltnp |grep postgres

will show you listening PostgreSQL servers, and

psql -p 5433

(or whatever) will connect to a different port.

The databases appeared to vanish because when you installed the new PostgreSQL it became the default version that's run.

If you find that the 8.4 install is in fact not running, you probably just need to change the port it starts on in its configuration so it runs on a different port, then connect to it as shown above.


I had a file that was around 30 MB, and issued the vacuum full (not the normal VACUUM as it has the same effect that the Postgres behavior) for reducing its size. After the operation the file got sized 22 MB. I'm pasting the stats of each execution, which shows the differences in between (you will see more failures due to size for the VF).

sqlite> vacuum;
Successful lookaside attempts:       3690
Lookaside failures due to size:      913
Lookaside failures due to OOM:       0

sqlite> vacuum full;
Successful lookaside attempts:       6509
Lookaside failures due to size:      1621
Lookaside failures due to OOM:       0

The command .dbinfo will provide some information as the database page size and its count. Database pages are variable as specified in the file format doc page (this link contains all the internal structures).

For investigating the contents you can use sqlite_stats1 (the link contains the specifications of this table) table, which will be created after the ANALYZE:

analyze;
select * from sqlite_stat1;


There is a lot to grasp if you want to fully understand all of the intricacies of locking and isolation levels in SQL Server, and your question is essentially leading you in this direction.

All of the information is available at http://msdn.microsoft.com/en-us/library/ms173763.aspx, although it's not an easy read.  An easier read is available at http://databases.about.com/od/sqlserver/a/isolationmodels.htm - pay particular attention to the "phantom reads" section.

For what you are trying to do you would need BOTH transactions running with
an isolation level of "SERIALIZABLE".  What this basically means is that they will run "as if" one was run after the other - that they were actually serialized.

What is happening is that BOTH the transactions decide that the same record needs to be inserted, and then BOTH try to insert that same record.  The lock that is taken by the test logic is separate from the lock that is taken for the insert, which allows this to happen.  SERIALIZABLE will stop this from happening with it's "Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes" clause.  Another way to think of this is that the read locks from the test need to be held until the whole transaction completes.  This can be specified using a HOLDLOCK query hint; but I have to say that I've not personally played with using HOLDLOCK, but just with the "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" version.


How can I clear buffer for a SQL Server DB Hosted On Azure?

What you're basically trying to do is measure storage speeds in the cloud. Unfortunately, that's not necessarily repeatable or reliable - it can suffer from noisy neighbors, other people doing development on the same database, varies with your storage type, etc.

Rather than clearing the buffers or measuring query runtime, try starting with measuring logical reads. It's the number of 8KB pages a query reads in order to accomplish its goals.

In either SSMS or Operations Studio, run:

SET STATISTICS IO ON;

And then run your query, and look in the output messages for the logical reads on each table - that's the number of 8KB pages it read. (Ignore physical reads - those can change from time to time based on what's in cache.) Generally speaking, reading less pages means a faster query - regardless of whether those pages are read from memory, or from disk.

If your query references a lot of tables, then instead of trying to add up those numbers with a calculator, copy/paste the messages into StatisticsParser.com. Gives you a nice summary of the logical reads overall, even across multiple statements in a batch. (Disclaimer: that open source site was written by one of my employees, but it's not affiliated with my company.)


Postgres knowing which where column returned positive result

Something like:

select t.*, a=1 as "a=1", b=2 as "b=2", c=3 as "c=3"
from table t
where a=1 or b=2 or c=3;


How to find when was partition created in SQL Server

Perhaps you can use the modify_date of sys.partition_functions.  As new partitions for existing tables are created by ALTERing the partition function.

Or mine these out of the default trace.


You may want to consider using the query that will show you free space in the log and free space in the data file. If you add them up you'll get the similar value as the SSMS is showing.

This is an excellent query which does just that:

DECLARE @log_used DECIMAL(19,7);
CREATE TABLE #x(n SYSNAME, s DECIMAL(19,7), u DECIMAL(19,7), b BIT);
INSERT #x EXEC('DBCC SQLPERF(LogSpace);');
SELECT @log_used = u FROM #x WHERE n = DB_NAME();
DROP TABLE #x;

DECLARE @data_used DECIMAL(19,7);
SELECT @data_used = SUM(a.total_pages)*8/1024.0
FROM sys.partitions AS p
INNER JOIN sys.allocation_units AS a
ON p.[partition_id] = a.container_id;

;WITH x(t,s) AS
(
  SELECT [type] = CASE
    WHEN [type] IN (0,2,4) THEN 'data' ELSE 'log' END,
    size*8/1024.0 FROM sys.database_files AS f
)
SELECT
  file_type = t,
  size = s,
  available = s-CASE t WHEN 'data' THEN @data_used ELSE @log_used END
FROM x;

Query Source and answer to similar question

By the way, it is usually good idea to split data file in your tempDB into number of equally sized chunks (number depends on the number of cores). It tends to help performance a lot. Just don't split the log file (it will do more harm than good).


SQL Server 2008 R2 Cluster Service pack 3 fail to failover node

I found the issue after a long time. It was due to 1 million+ files in the \MSSQL\log folder.

After setting up a job that cleared that folder. The failover after SP install worked fine.

The solution was confirmed on both this 2008R2 cluster, and the 2012 cluster we had the same problem on.


Getting an error "ORA-00093: pga_aggregate_target must be between 10M and 4096G-1" on Oracle DB startup

Found an answer at : http://www.databaseskill.com/400704/

But the explanation is not very clear. To summarize :

This property 'pga_aggregate_target' is in a file called an spfile which is present at 'C:\app\dbhome\product\12.1.0\dbhome_1\database' for example.
I edited this value to one fitting the range which in my case I changed to 855638016
Further you require a init file at the same path. If one is not present at the above path you can retrieve a file from 'C:\app\dbhome\admin\orcl\pfile' and rename it to initorcl.ora
After doing the above steps connect to an idle instance by running sqlplus / as sysdba and try the startup; command

Answered my own question for future reference


SQL Server Cache population on server / service startup

There isn't normally any added value in doing this: you may cache data that isn't needed that requires eviction when your real load occurs.

How much time do you think this will save too? Have you done some measurements to see if running a SELECT * on some table improves response after server start up?


Counting Rows based on values in other columns

It will look something like this:

SELECT StudentID, SUM(CASE WHEN INSTR(Classes,:Class) THEN 1 ELSE 0 END)
FROM Classes
GROUP BY StudentID;

This is Oracle syntax, so you will have to translate for Access.


How to Set Recovery Mode in SQL Server Always On Availability Group without downtime?

Availability groups require databases to be in full recovery mode (see Availability Database Prerequisites and Restrictions), so there should be no need to switch it to full prior to running a backup.  The database should not be switched out of full recovery mode if it is in an availability group.

As to where to run the backup, on the primary or secondary, it is a matter of  load distribution, and currency.  If you don't want the load of the backup to run on the primary, then you'll need to run it on the secondary.  If they are in asynchronous replication, the backup may not contain all of the latest data if some of the database replication is backed up in the log send queue.


Help me to better design my query so I can gain more performance

The first this I would try is to add
where doc.datum>=@od_datuma
       and  doc.datum <=@do_datuma
because the having clause. Maybe it will give a hint to the query planner.
Then the big question is: why the outer join with the subquery?
Does the " where .. stari_uazi.ART_ID is null " mean that you want to exclude all the rows returning from the subquery?
I suspect the subquery is the real killer because the search for DOC is not bounded ( DOC.DATUM <@od_datuma ), so it might fetch a huge number of rows, which have not real purpose since the parent query excludes these rows anyway..

My guess is that if you suppress the subquery ( left outer join .. ) you will have something very close from what you want, much faster. If you need to suppress some rows from the result set you can put them in a temporary table and delete them afterwards, this might be faster than the outer join subquery.

Update: so if you want to keep only the rows between @od / @do _datum, remove the left join and add the condition where DOC.DATUM between @od_ and @do_
You will have the same result and it will be much faster.


Why doesn't MariaDB server recognise Server Audit settings in config file?

From the documentation:

  Although the plugin's shared library is distributed with MariaDB by
  default, the plugin is not actually installed by MariaDB by default.

So you need to install the plugin - either execute:

INSTALL SONAME 'server_audit';

Or put it in your option file:

[mariadb]
...
plugin_load_add = server_audit


What's the problem with PostgreSQL temporary files?

Memory is faster than disk.

Anything that can be done only using memory will work faster instead of using the hard disks to store temporary content.

PostgreSQL documentation says:

  work_mem (integer)

  Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files.

So if you find the proper value of it (which depends on total amount of RAM, number of workers, SQL queries done, etc.) then everything should be able to run from RAM only, hence fast.

If this value is exceeded for some operation, temporary files will be created on disk, and hence the operation will be slower.

So tracking amount of temporary files and investigate if it grows is a possible strategy to get better performances (maybe work_mem is just too low, its default value being 4MB; or it is good but it maxes out currently available RAM and then you need a box with more RAM).

Hence the following tools:

1) temp_files in pg_stat_database whose value is "Number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting."

and

2) log_temp_files configurable item whose documentation is:

  Controls logging of temporary file names and sizes. Temporary files can be created for sorts, hashes, and temporary query results. A log entry is made for each temporary file when it is deleted. A value of zero logs all temporary file information, while positive values log only files whose size is greater than or equal to the specified number of kilobytes. The default setting is -1, which disables such logging. Only superusers can change this setting.


SQL Queries Slow in the Upgraded environment

Queries in need of query and index tuning are often those that are most vulnerable to performance regression after a SQL Server upgrade. That may be the best long term solution. It won't hurt to update stats to make sure the optimizer has the best information for informed choices.

If you identify issues where the legacy cardinality estimator performs significantly better after tuning, I suggest the most granular approach to address performance regressions. With a limited number of specific queries, use the FORCE_LEGACY_CARDINALITY_ESTIMATION hint. For a large number of queries within specific database(s), use ALTER DATABASE SCOPED CONFIGURATION. In the case where you experience CE problems with most or all databases, turn on trace flag 9481 at the instance level.

You might first try the ENABLE_QUERY_OPTIMIZER_HOTFIXES query hint or  QUERY_OPTIMIZER_HOTFIXES database scoped configuration so that patches made the CE available in the SP/CUs you've installed are used. These are not used by default due to an overabundance of caution.


Use timestamp(or datetime) as part of primary key (or part of clustered index)

Consider the following:

An ideal index for this query would be KEY (publish, type, Timestamp). By your choice of query I get that filtering by publish and type is common.
If the above is correct, then data inserted into table will not be sequential by Timestamp anyhow
A PRIMARY KEY is unique. Are you certain you are not allowed to have two identical Timestamp values? Remember that resolution is 1 second, so it is very common to have same TIMESTMAP values for different rows.
I usually like an AUTO_INCREMENT columns as PRIMARY KEY (see Reasons to use AUTO_INCREMENT columns on InnoDB). Just use a normal index on (publish, type, Timestamp) is my general opinion.


Mapping similar products in single product in price comparison website

The basics you can do with a simple GROUP BY:

select
 title,
 group_concat(id) as brandIDs
from product1
group by title;

http://sqlfiddle.com/#!9/87532/1

But it will group only products with exactly the same title, no way to simply say in SQL that Samsung Galaxy Ace Nxt G313H Black and Samsung Galaxy Ace Nxt G313H White are the same.

You should normalize your table. You can use a separate table of brands which will save space from the multiplication of the names. And you can add some "hierarchy" of brand->series->product like Samsung->Galaxy Ace Nxt->G313H Black to make these queries easier.

Edit:
I tried to get exactly the results you show with MySQL string functions:

select
 substring_index(title,' ',4),
 group_concat(id) as brandIDs
from product1
group by substring_index(title,' ',4);

http://sqlfiddle.com/#!9/87532/6


createdb doesn't create a new database instance (aka 'cluster', unfortunately, as that term has grown other uses).  It just creates a new database in an already running instance, same as the CREATE DATABASE SQL command does.
To create a new instance use initdb or pg_ctl initdb.
You clearly already have a 9.6 instance created and running.  You should probably figure out why and what is in it, and either shut it down, shut it down and delete it, or upgrade it.  You could leave it running and pick a new port of your new instance, but that is likely to create even more confusion of the same sort you already have.