Cluster command locking read only queries on replica

A problem with letting queries operate against the table while it is being CLUSTERED (or VACUUM FULL) is that even queries which are select-only from the user's perspective can still write to the table in non-user-visible ways, to set hint bits and clean up after rolled-back transactions, for example.

You could set up two replicas, one which has replay paused just before the table cluster operation starts and so is open for readers during the CLUSTER, and another which is allowed to replay normally.  Then once the CLUSTER is done, everyone switches from the paused replica to the other one, so that the paused one can catch up.  Whether you can tolerate using a replica which has fallen behind by hours is another question, though.

There are projects that will do online clustering of tables, such as pg_reorg and pg_repack. They are not part of the PostgreSQL core, but they are probably worth a look.  I have not used them in production, but mostly because I don't have a production need for them.

Yet another option would be to partition the tables.  Then each partition can be reclustered separately, and you might not have to do clustering at all if the partition constraints accomplish the same thing as clustering does--generating more efficient IO patterns for partial scans.


The less than and greater than are not inclusive, there is no number in the list you gave that is more than 2814 AND less than 2815.

Try this instead:

DELETE
FROM titolo
WHERE idtitolo BETWEEN 2814 AND 2815


Sync oracle DBs using archive log when not connected to each other

First you need to make sure that the copied database is a standby database. That is not the case in your case. Use duplicate to do that is the easiest way.

Is it possble at all using a not connected database .... yes, but why? To me it looks like a waste of money if there is no decent connectivity.

Restore/open failed because your copy database feels it has the PRIMARY role.
(select database_role from v$database; to check)

There hardly is a simpler way to do this than using rman duplicate. This can be done using a backup, no problem at all. Maybe hiring a real dba can save you lot's of time, money and in the end a disaster.


If i understood correctly, then you want to join your two tables and if there is no entry in retention for a given cust_id and date the intensity should return 0.

So you might be looking for something like this:

DROP TABLE IF EXISTS customer_date_dimension;
CREATE TABLE customer_date_dimension(
 cust_id INT,
 cust_date DATE
);

INSERT INTO customer_date_dimension(cust_id, cust_date) VALUES
(1, '2018-01-01'),
(1, '2018-01-02'),
(2, '2018-01-01'),
(2, '2018-01-02');

DROP TABLE IF EXISTS retention;
CREATE TABLE retention(
  cust_id INT,
  cust_date DATE,
  intensity INT
);

INSERT INTO retention(cust_id, cust_date, intensity) VALUES
(1, '2018-01-02', 6),
(2, '2018-01-02', 10);

SELECT c.cust_id AS customer_id, c.cust_date AS "date",
      (CASE WHEN r.intensity IS NULL THEN 0 ELSE r.intensity END) AS intensity
FROM customer_date_dimension AS c
LEFT JOIN retention AS r ON
      c.cust_id = r.cust_id
  AND c.cust_date = r.cust_date;


Are WHERE clauses applied in the order they are written?

To elaborate on @alci's answer:

PostgreSQL doesn't care what order you write things in

PostgreSQL doesn't care at all about the order of entries in a WHERE clause, and chooses indexes and execution order based on cost and selectivity estimation alone.
The order in which joins are written is also ignored up to the configured join_collapse_limit; if there are more joins than that, it'll execute them in the order they're written.
Subqueries can be executed before or after the query that contains them, depending on what's fastest, so long as the subquery is executed before the outer query actually needs the information. Often in reality the subquery gets executed kind-of in the middle, or interleaved with the outer query.
There's no guarantee PostgreSQL will actually execute parts of the query at all. They can be completely optimized away. This is important if you call functions with side-effects.

PostgreSQL will transform your query

PostgreSQL will heavily transform queries while retaining the exact same effects, in order to make them run faster while not changing the results.

Terms outside a subquery can get pushed down into the subquery so they execute as part of the subquery not where you wrote them in the outer query
Terms in the subquery can be pulled up to the outer query so their execution is done as part of the outer query, not where you wrote them in the subquery
The subquery can, and often is, flattened into a join on the outer table. The same is true of things like EXISTS and NOT EXISTS queries.
Views get flattened into the query that uses the view
SQL functions often get inlined into the calling query
... and there are numerous other transformations made to queries, such as constant expression pre-evaluation, de-correlation of some subqueries, and all sorts of other planner/optimizer tricks.

In general PostgreSQL can massively transform and rewrite your query, to the point where each of these queries:

select my_table.*
from my_table
left join other_table on (my_table.id = other_table.my_table_id)
where other_table.id is null;

select *
from my_table
where not exists (
  select 1
  from other_table
  where other_table.my_table_id = my_table.id
);

select *
from my_table
where my_table.id not in (
  select my_table_id
  from other_table
  where my_table_id is not null
);

will usually all produce exactly the same query plan. (Assuming I didn't make any dumb mistakes in the above anyway).

It's not uncommon to try to optimize a query only to find that the query planner has already figured out the tricks you're trying and applied them automatically, so the hand-optimized version is no better than the original.

Limitations

The planner/optimizer is far from omnicient, and is limited by the requirement to be absolutely certain it can't change the effects of the query, the available data to make decisions with, the rules that've been implemented, and the CPU time it can afford to spend pondering the optimizations. For example:

The planner relies on statistics kept by ANALYZE (usually via autovacuum). If these are outdated, the plan choice can be bad.
The statistics are only a sample, so they can be misleading due to sampling effects, especially if too small a sample is taken. Bad plan choices can result.
The statistics don't keep track of some kinds of data about the table, like correlations between columns. This can lead the planner to make bad decisions when it assumes things are independent when they aren't.
The planner relies on cost parameters like random_page_cost to tell it the relative speed of various operations on the particular system it's installed on. These are only guides. If they're badly wrong they can lead to poor plan choices.
Any subquery with a LIMIT or OFFSET cannot be flattened or be subject to pullup / pushdown. This doesn't mean it'll execute before all parts of the outer query, though, or even that it'll execute at all.
CTE terms (the clauses in a WITH query) are always executed in their entirety, if they're executed at all. They can't be flattened, and terms can't be pushed up or pulled down across the CTE term barrier. CTE terms are always executed before the final query. This is non-SQL-standard behaviour, but it's documented as how PostgreSQL does things.
PostgreSQL has a limited ability to optimize across queries on foreign tables, security_barrier views, and certain other special kinds of relation
PostgreSQL won't inline a function written in anything except plain SQL, nor do pullup/pushdown between it and the outer query.
The planner/optimizer is really dumb about selecting expression indexes, and about trivial data type differences between index and expression.

Tons more, too.

Your query

In the case of your query:

select 1
from workdays day
where day.date_day >= '2014-10-01'
    and day.date_day <= '2015-09-30'
    and day.offer_id in (
        select offer.offer_day
        from offer
        inner join province on offer.id_province = province.id_province
        inner join center cr on cr.id_cr = province.id_cr
        where upper(offer.code_status) <> 'A'
            and province.id_region in ('10' ,'15' ,'21' ,'26' ,'31' , ...,'557')
            and province.id_cr in ('9' ,'14' ,'20' ,'25' ,'30' ,'35' ,'37')
    )

nothing stops it from being flattened into a simpler query with an extra set of joins, and it very likely will be.

It'll probably turn out something like (untested, obviously):

select 1
from workdays day
inner join offer on day.offer_id = offer.offer_day
inner join province on offer.id_province = province.id_province
inner join center cr on cr.id_cr = province.id_cr
where upper(offer.code_status) <> 'A'
   and province.id_region in ('10' ,'15' ,'21' ,'26' ,'31' , ...,'557')
   and province.id_cr in ('9' ,'14' ,'20' ,'25' ,'30' ,'35' ,'37')
   and day.date_day >= '2014-10-01'
   and day.date_day <= '2015-09-30';

PostgreSQL will then optimise the join order and join methods based on its selectivity and row count estimates and the available indexes. If these reasonably reflect reality then it'll do the joins and run the where clause entries in whatever order is best - often mixing them together, so it does a bit of this, then a bit of that, then comes back to the first part, etc.

How to see what the optimizer did

You can't see the SQL that PostgreSQL optimizes your query into, because it converts the SQL to an internal query tree representation then modifies that. You can dump the query plan and compare it to other queries.

There's no way to "deparse" that query plan or the internal plan tree back to SQL.

http://explain.depesz.com/ has a decent query plan helper. If you're totally new to query plans etc (in which case I'm amazed you made it this far through this post) then PgAdmin has a graphical query plan viewer that provides much less information but is simpler.

Related reading:

Optimizer
Query planning parameters
EXPLAIN
CTEs (WITH queries)
My blog on CTEs as optimisation fences

Pushdown/pullup and flattening capabilities continue to improve in each release. PostgreSQL is usually right about pull-up/push-down/flattening decisions, but not always, so occasionally you have to (ab)use a CTE or the OFFSET 0 hack. If you find such a case, report a query planner bug.

If you're really, really keen you can also use the debug_print_plans option to see the raw query plan, but I promise you don't want to read that. Really.


Get incremental counts of an aggregated value in a joined table

Using functions or expressions in JOIN conditions is usually a bad idea, I say usually because some optimisers can handle it fairly well and utilize indexes anyhow. I would suggest creating a table for the weights. Something like:

CREATE TABLE weights
( weight int not null primary key
);

INSERT INTO weights (weight) VALUES (0),(10),(20),...(1270);

Make sure you have indexes on posts_reasons:

CREATE UNIQUE INDEX ... ON posts_reasons (reason_id, post_id);

A query like:

SELECT w.weight
     , COUNT(1) as post_count
FROM weights w
JOIN ( SELECT pr.post_id, SUM(r.weight) as sum_weight
       FROM reasons r
       JOIN posts_reasons pr
             ON r.id = pr.reason_id
       GROUP BY pr.post_id
     ) as x
    ON w.weight > x.sum_weight
GROUP BY w.weight;

My machine at home is probably 5-6 years old, it has an Intel(R) Core(TM) i5-3470 CPU @ 3.20GHz and 8Gb of ram.

uname -a
Linux dustbite 4.16.6-302.fc28.x86_64 #1 SMP Wed May 2 00:07:06 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

I tested against:

https://drive.google.com/open?id=1q3HZXW_qIZ01gU-Krms7qMJW3GCsOUP5

MariaDB [test3]> select @@version;
+-----------------+
| @@version       |
+-----------------+
| 10.2.14-MariaDB |
+-----------------+
1 row in set (0.00 sec)

SELECT w.weight
     , COUNT(1) as post_count
FROM weights w
JOIN ( SELECT pr.post_id, SUM(r.weight) as sum_weight
       FROM reasons r
       JOIN posts_reasons pr
             ON r.id = pr.reason_id
       GROUP BY pr.post_id
     ) as x
    ON w.weight > x.sum_weight
GROUP BY w.weight;

+--------+------------+
| weight | post_count |
+--------+------------+
|      0 |          1 |
|     10 |       2591 |
|     20 |       4264 |
|     30 |       4386 |
|     40 |       5415 |
|     50 |       7499 |
[...]
|   1270 |     119283 |
|   1320 |     119286 |
|   1330 |     119286 |
[...]
|   2590 |     119286 |
+--------+------------+
256 rows in set (9.89 sec)

If performance is critical and nothing else helps you could create a summary table for:

SELECT pr.post_id, SUM(r.weight) as sum_weight
FROM reasons r
JOIN posts_reasons pr
    ON r.id = pr.reason_id
GROUP BY pr.post_id

You can maintain this table via triggers

Since there is a certain amount of work that needs to be done for each weight in weights, it may be beneficial to limit this table.

    ON w.weight > x.sum_weight
WHERE w.weight <= (select MAX(sum_weights)
                   from (SELECT SUM(weight) as sum_weights
                   FROM reasons r
                   JOIN posts_reasons pr
                       ON r.id = pr.reason_id
                   GROUP BY pr.post_id) a
                  )
GROUP BY w.weight

Since I had a lot of unnecesary rows in my weights table (max 2590), the restriction above cut the execution time from 9 down to 4 seconds.


http://social.msdn.microsoft.com/Forums/en-US/sqlreplication/thread/0b8bac44-da87-4115-90f6-b8bf1e1d0bd8

Both cmd values relate to the number of commands written. Since last stats - cmds are all cmds written since the last entry and stats- cmd are an accumulation of all cmds written since the last comment with a state of 1.


UPDATE column to MIN of a SELECT result set - what went wrong?

Your problem is because it thinks that own_resource_uuid in your sub-query is res.own_resource_uuid, not asset_z_hardware.own_resource_uuid. If you specify it completely, it should be unambiguous and SQL will know what you intended.

Before you introduced res there was nothing else it could be. SQL will assume you mean a column in the local sub-query if there’s one there.


Consolidating data changes in SQL Server by team

We also use source control for managing schema changes. When it comes to deployment of new versions, I find it irreplaceable. It saves a lot of time and saves oneself from headaches

Red Gate is a good choice, though we went for ApexSQL Diff  and ApexSQL Version

But none of these can track actual data changes. And data changes are not stored in source control

The tool that we use for monitoring data changes is ApexSQL Log, so we can see what data changed (records added, deleted or modified), who made a change and when. It also shows the schema changes - a table modified, a store procedure dropped, etc. Its main request is a database in Full recovery model, which requires regular backups and hard disk space - but on the other hand, you'll be ready if it comes to a disaster recovery ;)


INSERT ON CONFLICT DO NOTHING and RAISE unique_violation

Is there a way to catch a unique_violation via ON CONFLICT?

ON CONFLICT itself is a unique_violation catching mechanism. From the docs

  ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error. (See ON CONFLICT Clause below.)

If you don't want the behavior just don't use ON CONFLICT then you catch the unique_violation yourself and the choices are endless.

That said, one of the drawbacks of partitioning from Limitations,

  Using the ON CONFLICT clause with partitioned tables will cause an error, because unique or exclusion constraints can only be created on individual partitions. There is no support for enforcing uniqueness (or an exclusion constraint) across an entire partitioning hierarchy.

That's because when you partition you don't have a single index that represents uniqueness and so you either have to lock all the indexes during the check and before the insertion, or forgo that functionality.


Unable to connect oracle 12 database remotely while local connection works

Env:

[root@o71 ~]# tail -5 /home/oracle/.bash_profile

export ORACLE_SID=MIN18
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/18.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH

Local:

[root@o71 ~]# su - oracle -c "sqlplus / as sysdba"

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 15 15:54:16 2019
Version 18.4.0.0.0

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

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL>

Remote:

[root@o72 ~]# ssh root@o71 su - oracle -c "sqlplus / as sysdba "

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 15 15:55:15 2019
Version 18.4.0.0.0

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

Enter user-name:

Remote with better syntax:

[root@o72 ~]# ssh root@o71 'su - oracle -c "sqlplus / as sysdba "'

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 15 15:55:39 2019
Version 18.4.0.0.0

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

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL>

Instead of:

ssh root@node2 su - oraabc -c "/oracle/ABC/12102/bin/sqlplus / as sysdba"

Use this:

ssh root@node2 'su - oraabc -c "/oracle/ABC/12102/bin/sqlplus / as sysdba"'

Also I find it really bad practice to set these variables in profile by default. Just use oraenv.

Example:

[root@o72 ~]# ssh root@o71 'su - oracle -c "export ORAENV_ASK=NO; export ORACLE_SID=MIN18; . oraenv; sqlplus / as sysdba "'
The Oracle base remains unchanged with value /u01/app/oracle

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 15 15:56:51 2019
Version 18.4.0.0.0

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

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL>


MySQL auto Increment id has taken but seems like not written to disk

Please provide the schemas involved, plus the SQL statements.

A wild guess is that you are using OFFSET (or LIMIT nnn,mmm).  I explain here why OFFSET can skip or duplicate rows.


Add following line to sqlnet.ora:

adr_base=off

You should be able to find your traces then.
Without it, all client traces go to %userprofile%\oracle\oradiag_%username% folder.


Remove accidental duplicates in PK table, cascade changes to FK table

This solution requires first that there aren't any conflicting or cascading information in plant_name_mapping; to work, the following query must return no records:

select *
from plant_name_mapping o
join plant_name_mapping n ON ((n.newgenus, n.newspecies) = (o.oldgenus, o.oldspecies));

The queries to update your tables are:

create temp table plantschanged as
with
  renamed as (
    select o.*, n.newgenus, n.newspecies, n.newspecies is not null as namechanged
    from plants o
    left join plant_name_mapping n on ((n.oldgenus, n.oldspecies) = (o.genus, o.species))
      ),
  newplants as (
    select id, coalesce(newgenus, genus) as genus, coalesce(newspecies, species) as species, namechanged
    from renamed
    ),
  plantsnewid as (
    select *, min(id) over(partition by genus, species) as new_id
    from newplants
    )
select *
from plantsnewid
where namechanged or new_id <> id;

with
  plantsrenamed as (
    update plants p set genus = pc.genus, species = pc.species
    from plantschanged pc
    where p.id = pc.id and pc.id = pc.new_id
    returning p.id
    )
update samples s set tree = pc.new_id
from plantschanged pc
where s.tree = pc.id and pc.id not in (select * from plantsrenamed);

delete from plants
where id in (select id from plantschanged where new_id <> id);

The table plants may have more dependencies than just the table samples. You may check through the following query if you have any further tables that would require updating (perform the same query as update samples with each of these, prior to delete from plants):

select conrelid::regclass, conname
from pg_constraint
where confrelid = 'plants'::regclass and contype = 'f';


Assuming you have a valid instance of the SMO Server object defined as $ServerObject. The following code will allow you pull the names of all schemas in the database UserDatabase01:

$Database = $ServerObject.Databases | ? { $_.Name -eq "UserDatabase01" }
$Database.Schemas | Select Name

For more information on schema properties available through SMO, see the documentation for the Schema class. Unfortunately, the version of SMO for SQL 2005 is missing a lot of properties for this class. Newer versions are backwards compatible, you may want to look into installing a newer version:

SQL 2012 Feature Pack
SQL 2014 Feature Pack


restarting database while shrink [db] is still running

Restarting SQL Server is rarelly a good way to fix issue.

You can try to log as a dedicated admin (DAC connection) and then, you will see what is running and you may be able to kill some session.

Be aware that by killing a session, SQL will have to roll it back (which is single threaded) and there is no way to escape from this rollback. Sometime, the best solution may be to wait for it to complete. (You can kill the select as the rollback should be pretty quick)


High PageIOLatch_SH Waits with High Drive Idle times

Is there memory pressure on your server? The buffer pool may be filling up causing a lot of paging out to disk.

SELECT cntr_value AS [Page Life Expectancy]
FROM sys.dm_os_performance_counters
WHERE [object_name] = N'SQLServer:Buffer Manager'
AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);

-- Is this a NUMA system?
SELECT
  CASE COUNT(DISTINCT parent_node_id)
     WHEN 1
         THEN 'NUMA disabled'
         ELSE 'NUMA enabled'
  END
FROM sys.dm_os_schedulers
WHERE parent_node_id NOT IN (32, 64);

Also, check the number of rows returned and the hierarchy in your where statements. You may be returning more rows than you require.


MySql ORDER BY slow with join, fast as 2 queries

Investigate the plan. MySQL alters the order of the tables scanning.
Add STRAIGHT_JOIN. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=592b1fb77c5c383abb8d93f9d9e13559


Can I rename the View Report button in SSRS

Nope. That button can't be edited.

Up until SSRS 2016, the amount of customization possible in SSRS outside of the report canvas was close to zero. With 2016, there have been some improvements, but I believe that button is still not customizable.


Why would adding an index on a MySQL table slow it down significantly but ok on SQL Server and PostgreSQL

MyISAM?  InnoDB?  What is the cache size?  (SHOW VARIABLES LIKE '%buffer%';)  Did you run the query a second time (to cancel out the affect of caching)?  Do you have TEXT or BLOB columns?  (Please provide SHOW CREATE TABLE.)  How much RAM?  How big (GB) is the table (SHOW TABLE STATUS).

Probably...  Definitely... The cache for the data was not large enough to hold the products in question.  And, since the products are mostly "random", there is a lot of I/O.

Here's what happens in any(?) database vendor for that query:

Locate the 'row' for 1500 in the index.
Scan forward through the index until 2000.  (Due to BTree organization in MySQL, this is quite efficient.)
For each row, reach over into the "data" to get all the columns (SELECT *).  (This is where vendors vary.  And "Engines" in MySQL will vary.)  Since the data rows are in one order, but the index rows are in a different order, MySQL will be effectively doing 'random I/O' to get the rows.  (MySQL fetches the rows as needed.  Other vendors may sort the row-addresses first -- perhaps a benefit, perhaps a cost.)
For MySQL with InnoDB and large TEXT/BLOB columns, those columns may be stored elsewhere, thereby necessitating an extra I/O.  (Hence the admonition not to use *, but to spell out only the necessary columns.)

But...

If "too much" of the table needs to be fetched -- in particular more than about 20% of the table has product in the desired range -- MySQL will do a table scan instead of using the index.  (I don't know about non-MySQL vendors.)  This optimization is usually beneficial, but sometimes is a mistake.  The EXPLAIN SELECT .. would tell us which it did.

InnoDB "reaches into the data" via the PRIMARY KEY which is "clustered" with the data.  So it is a BTree-probe to find each row.  MyISAM has a byte address into the data file, so it is a fseek.  Other vendors work differently.

So... Is the comparison "fair"?  Do you have the PK "clustered" with the data on all vendor tests?  (Etc)

And I did not get into the caching details.  This is probably a major component of the slowdown.

I'll fill in more details after you provide more details.

After details

You are using InnoDB, not MyISAM (good).  innodb_buffer_pool_size = 8M is much  too small.  For 16GB of RAM, recommend 11G.  But even 1G would show a significant speedup since the table is smaller than that.

You say you are running MySQL 5.7, yet the 8M default contradicts that.  Did you override the default?  Please provide the version (SELECT @@version;).

It seems that there are no big TEXT or BLOB columns, so my comments on such do not apply.

Bottom line:  To get a 'fair' comparison, increase the buffer_pool setting for MySQL.  This is the most important tunable for performance.  It is not automatically set because it depends on the amount of available RAM.


I can't answer for all versions, but for SQL Server 2012 up until  SQL Server 2017 I am certain that they are supported

LocalDB has the same programmability features as SQL Server Express.

  SQL Server Express LocalDB, a lightweight version of Express that has
  all of its programmability features, yet runs in user mode and has a
  fast, zero-configuration installation and a short list of
  prerequisites.

Source

And then,  building on the previous point, for the SQL Server express 2012 T-SQL Syntax

  Express supports the same T-SQL language elements you find in any
  edition of SQL Server. Not only can you issue data manipulation
  language queries against the database, but you can also run data
  definition language statements to create such objects as views,
  triggers, cursors and stored procedures

Source

Testing (SQL Server 2017)

USE testdb
GO
CREATE TABLE #temp (id int , value nvarchar(255));

INSERT INTO #temp( id ,value)
SELECT 5, 'bla';

SELECT * FROM #temp;

result

id  value
5   bla

The temporary table also works when changing the db's compatibility mode to 100 (2008).


select rid,rating,uid from ratings group by uid;


Should DBAs also be acting as Security Administrators?

I have functioned as all the roles in the past. Small place, one server, 3 databases and I was the only IT. So I did it all; DBA, cabling, hardware, software, programming. It should be separated out, but if you don't have the manpower or the money, then combined roles is the only way to cover them all.


Grouping Based on Column Consecutive Values

SELECT (@prev = Title) AS same,
       Title,
       `Date`,
       @prev := Title AS t
    FROM ( SELECT @prev := '' ) AS Init
    JOIN ( SELECT `Date`, Title FROM tbl ORDER BY `Date` LIMIT 999999 ) AS x
    HAVING same = 0

See if that gets the right info, plus some junk.  Then get rid of the junk:

SELECT `Date`, Title
    FROM ( the entire query above ) AS y
    ORDER BY `Date`


How can I see if the data in a SQL Server table is page-compressed?

To see whether or not data pages are indeed currently "PAGE" compressed, you can use the undocumented DMF sys.dm_db_database_page_allocations(). The is_page_compressed field contains the info you are looking for. You will need to use the DETAILED mode (i.e. 5th parameter) or else the values in that field will all be NULL.

To be clear (based on the wording of the question being, "what can I do to find out if the data in that table is page-compressed?"), this is not an all-or-none issue: page compression is applied per each data page, hence you can have none that are compressed, all being compressed, or any combination in between. So, you need to look at all of the pages. And no, you cannot necessarily assume that a single non-page compressed page indicates that you need to REBUILD because a non-filled page will not compress.

For example:

SELECT [is_page_compressed]
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED');

The following shows that the data pages are not initially page compressed, but they are after the REBUILD operation:

USE [tempdb];

-- DROP TABLE dbo.CompressedHeap;
CREATE TABLE dbo.CompressedHeap
(
  ID INT IDENTITY(1, 1) NOT NULL,
  String sysname,
  [MaxLength] SMALLINT,
  [Type] VARCHAR(5)
) WITH (DATA_COMPRESSION = PAGE);

INSERT INTO dbo.CompressedHeap ([String], [MaxLength], [Type])
  SELECT col.[name], col.[max_length], obj.[type]
  FROM   master.sys.columns col
  CROSS JOIN master.sys.objects obj;

SELECT [is_page_compressed], *
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED')
WHERE  [is_iam_page] = 0
AND    [is_allocated] = 1;
-- 394 pages

ALTER TABLE dbo.CompressedHeap REBUILD;

SELECT [is_page_compressed], *
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED')
WHERE  [is_iam_page] = 0
AND    [is_allocated] = 1;
-- 179 pages


If the school provided email is always going to be available and everyone is required to have one then you could certainly use that as the primary key.  If not then it sounds like you don't have a good natural key.  You might consider going with a surrogate key.  Create an additional column that is auto incrementing and put your primary key on it.  Then create indexes on the email and student id.

Lot's of people will argue that you should always use a natural key, and other's will argue that you should always use a surrogate key.  In my personal opinion you should use a natural key where it makes sense and a surrogate when it doesn't.  A natural key makes sense when it is reasonably short, consistent and always available.

I'm not a big fan of large concatenated primary keys.  If nothing else when you link the table to another one you may have to include all of those columns to create a good join and that makes your system heavily de-normalized and your queries slower.  At that point a simple integer surrogate key will let you create simple fast joins.


MySQL cluster fast inserts but slow selects

It seems that for solving your problem you chose the wrong tool.

MySQL Cluster is good when you do mostly key based lookups from multiple threads from memory based tables. Disk based tables may be useful when you have rarely read data. Or your work dataset is small portion of the table which should fit into memory cache whose size is defined by DiskPageBufferMemory config variable.

If your queries need many range or full scans - MySQL Cluster is slow even on physical machines. That is because such queries need a lot of data exchanges between data nodes. Try pinging between your data nodes. And for range scan data nodes may need to exchange hundreds and thousands of such messages.

Also MySQL previously stated that for data nodes your should use physical machines and have good interconnect for data node traffic. I doubt that this recommendation is no more valid nowadays.

And I think you should try cleaning up your config. For testing most of those things hardly changes anything and some setting may be slowing down things. Try such simplified NDBD_DEFAULT section:

[NDBD DEFAULT]
NoOfReplicas=1
DataMemory=1536M
IndexMemory=2560M

LockPagesInMainMemory=2
ODirect=1


Storing logical WAL in the same database

setup a trigger on UPDATE DELETE and INSERT that writes the info you want to a logging table.


Calculating average with AVG for a list of numbers

The best solution depends on where and in what form the values come from.

unnest()

Use the set returning function unnest() with an array in the FROM clause. That's shortest for long lists, but it's a Postgres specific solution.

SELECT avg(n) FROM unnest('{2,3,4}'::int[]) n;

Which is short syntax for:

SELECT avg(n) FROM unnest('{2,3,4}'::int[]) AS n(n);

VALUES

Alternatively, use a VALUES expression as subquery. This is standard SQL.

SELECT avg(n)
FROM  (VALUES (2),(3),(4)) t(n);

UNION ALL

You can do the same with a much more verbose chain of SELECT ... UNION ALL SELECT .... Also standard SQL.

SELECT avg(n)
FROM  (
   SELECT 2
   UNION ALL
   SELECT 3
   UNION ALL
   SELECT 4
   ) t(n);

Use UNION ALL, not just UNION, which would fold duplicates (which you did not specify!) and is therefore also considerably more expensive.

SQL Fiddle.

Note that all numeric literals in the example are cast to integer automatically (just digits). For other data types you may have to add explicit type casts. avg() returns (per documentation):

  numeric for any integer-type argument, double precision for a
  floating-point argument, otherwise the same as the argument data type


Do a lot of error checks affect query performance and is it bad practice?

I'm hard-pressed to see how an IF statement before the "meat" of the procedure that is just doing simple tests on the parameters would produce a bad plan.

Unless something really odd is going on, I wouldn't expect those checks to have a meaningful impact on performance unless the actual work the procedure is doing is very minimal.  Even a few hundred equality/ inequality checks should take way less than a millisecond which is almost certainly too small to be meaningful to you.

If there were 100 checks, that would make me suspect that something was off in the procedure and that it was trying to do too much/ take too many parameters and that it was in desperate need of refactoring into multiple smaller procedures.

The one caveat would be if this was a table-valued function (TVF) rather than a procedure.  I would absolutely avoid adding these sorts of checks if that would transform a single-statement TVF into a multi-statement TVF because that's going to prevent the optimizer from merging the query into the rest of the statement that it is being called from.  If you have a multi-statement TVF, that logic is potentially getting called many thousands of times in a single SQL statement in which case you might start to see some performance impact.  Even there, though, if your checks are only 1% of the overall runtime of the object, you probably don't have too much to optimize for.


How to use the COUNT function to count up to the current row for each row?

This will do it

select UserName,
       EventDate,
       row_number over (partition by UserName order by EventDate asc) as EventNumber
  from AllEvents


Problem with data insertion in Oracle table from SQL Server table

SQL Server inserts across linked servers use row-by-row insert statements.  If you run this:

INSERT INTO [oracledb].database.schema.table
SELECT *
FROM dbo.LocalTable

SQL Server will use a cursor to fetch each individual row from dbo.LocalTable and run an INSERT INTO ... statement against the linked server for each row.

Essentially, the insert gets turned into this:

DECLARE cur CURSOR
FOR
SELECT *
FROM dbo.LocalTable;
OPEN cur;
FETCH NEXT FROM cur INTO @vars...
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO [oracledb].database.schema.table (cols...)
    VALUES (@vals);
    FETCH NEXT FROM cur INTO @vars...
END
CLOSE cur;
DEALLOCATE cur;

You'll find that as the number of rows increases, the time required to run the insert will increase linearly.  This increases the chance that blocking on the target side may interfere with the inserts.  The OLEDB wait type indicates SQL Server is waiting for code in the OleDB provider to complete.  It occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.

The "best practice" for loading data from SQL Server to a link-server target is to run that either from the target side, or via a bulk-load app designed for that purpose.

You mentioned in comments that you do not have access to the Oracle server.  Without being able to see what's happening at the Oracle server side, it will be difficult to diagnose where the problem lies.


If the query for database too complicated, does it means the design of Database is bad?

The answer is: divide to conquer! Each sub-query should become a query (or a view) of its own. Use sub-queries sparingly. Use JOINs instead.

You would need at least one query (using JOINs) for:

AUTHOR
CUSTOMER
BOOK
...

Then you can test your queries or views independently (very useful if you are having trouble).


Lost postgreSQL password in Windows installation

Having previousley tried to delete PostgreSQL and reinstall it I realise I had neglected to delete the files the uninstall leaves behind.  In a Windows XP environment these are found in C:/Program Files/PostgreSQL/9.2  Deleting the remaining files in the directory 9.2 and then deleting the directory PostgreSQL allowed me to perform a completely new install of PostgreSQL without any failures.  Of course the new installation process requires input of new passwords, this time I made a note of them :)


According to ISO 8601 T is the time designator.
You need to use a constant "T" in your date format mask.

And your time stamp contains timezone so you need to use TO_TIMESTAMP_TZ function to achieve this.

SQL> select to_timestamp_tz('2017-02-14T18:04:34.988+00:00',' YYYY-MM-DD"T"HH24:MI:SS.FF TZH:TZM') from dual;

TO_TIMESTAMP_TZ('2017-02-14T18:04:34.988+00:00','YYYY-MM-DD"T"HH24:MI:SS.FF
---------------------------------------------------------------------------
14-FEB-17 06.04.34.988000000 PM +00:00

Documentation:TO_TIMESTAMP_TZ


How do I replace a substring within a string, but only if substring is matched within a regex group?

I would use two nested regexp_replace() calls:

first to enclose all keys with double quotes
then to enclose all values with double quotes

with data (input) as (
  values ('{receipt_type:Production,adam_id:1233,app_item_id:1233,more:[{h:234}]}')
)
select regexp_replace(
          regexp_replace(input, '(\w+)(:)', '"\1"\2', 'g'),
          '(:)(\w+)(,|})', '\1"\2"\3', 'g')
from data;

returns:
{"receipt_type":"Production","adam_id":"1233","app_item_id":"1233","more":[{"h":"234"}]}

If you want have the integers without quotes, you could run a third regexp_replace that removes the quotes from integers with quotes:
regexp_replace(..., '"([0-9]+)"', '\1', 'g')


CASE in WHERE clause - retuns a single-row result instead of 4 rows?

The WHERE clause is used to filter data. You aren't trying to filter out the records, instead you want to include them but alter their displayed value based on their actual value. This is why the CASE statement in the SELECT statement gives you the desired results.

Specifically, the reason your query omits 3 results is not because the CASE statement isn't working, but rather that the first 2 WHEN statements will likely always evaluate false. Effectively, when Projects.StartDate >= '2005/01/01' then Projects.[Name] would have to equal NULL for the records to be selected. If employee 24 had any projects started on or after 01/01/2005 and a NULL project name, those records would show up.

To break it down even further, for each record in your query, it will do the following.

Checks if Projects.StartDate = '2005-01-01 00:00:00.000'. If so, does Projects.[Name] = NULL. If so, return record.
If the check in step 1 evaluates false, then check if Projects.StartDate > '2005-01-01 00:00:00.000'. If so, does Projects.[Name] = NULL. If so, return record.
If the checks in both step 1 and step 2 evaluate false, then does Projects.[Name] = Projects.[Name] then return record. Hint: it always will.

Because 3 of the records associated with employee 24 were on or after '2005-01-01 00:00:00.000' and did not have NULL project names, they were excluded from the results.


I am currently investigating an occurrence of the same timeout exception with the same key wording:

  This failure occured while attempting to connect to the Principle server.

The environment is SQL Server 2012 with mirroring, so I suspect that the problem was not directly related to a long-running query, but rather to a temporary network/sync issue, either between the application and the SQL servers or between the SQL servers themselves. Stack trace:

System.Data.SqlClient.SqlException (0x80131904): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.  This failure occured while attempting to connect to the Principle server. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.DynamicUpdate(TrackedObject item)
   at System.Data.Linq.ChangeDirector.StandardChangeDirector.Update(TrackedObject item)
   at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode)
   at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)
   ...

Update: evidently, this wording is included in the exception message whenever a command times out against a mirrored/failover partner environment. It does indeed reflect a command that exceeded its timeout, and has nothing to do with a failure to attempting to connect. You can verify this by running a simple WAITFOR DELAY '00:00:31' command, which exceeds the default 30-second SqlCommand timeout, and triggers the same exception message.

I have opened a Connect bug to ask Microsoft to correct the misleading wording in the message.


If you know the exact IP of the incoming MySQL users, you can do the following

Suppose the user is called datacustomer and the incoming user's IP is 10.200.210.252. From what you are saying, there should be a MySQL user within mysql.user called datacustomer@'%'. You cansee that user by running this query

SELECT * FROM mysql.user
WHERE user='datacustomer' AND host='%';

If one row appears, you can change the host of that user

UPDATE mysql.user
SET host='10.200.210.252'
WHERE user='datacustomer' AND host='%';
FLUSH PRIVILEGES;

You'll have to repeat these steps with every MySQL user.


If you know start _id and end _id then you can retrieve the bulk you inserted using query like this:

db.yourCollection.find({_id:{$gt:ObjectId("56e13d5aed63a8477a7e2e78"), $lt: ObjectId("570f5a27b2d1a50996d3a0f0")}})

Replace the default mongodb ObjectIds with your _id fields.

Also, if the _id you are using is default mongodb ObjectId then you can easily compare them using above query since mongodb ObjectIds are monotonically increasing.


CPU utilization dangerously high due to COUNT queries

You can save some cpu cycles by using the same datatype as the column in your date range. So instead of:

WHERE (DATE(COL3) BETWEEN '2016-08-01' AND '2016-08-14')

assuming col3 is a timestamp, use something like:

where col3 >= timestamp('2016-08-01 00:00:00')
  and col3  < timestamp('2016-08-15 00:00:00')

You might need to use the unix_timestamp instead of timestamp conversion so that 'between' will work, but you can always do a >= and a < instead of using between.

There's also no need to include Col2 in your select, since col2 will always have the same value in each execution.

So, the query would become:

SELECT COUNT(DISTINCT col1)
FROM conversion_Details c
WHERE col3 >= timestamp('2016-08-01 00:00:00')
  AND col3  < timestamp('2016-08-15 00:00:00')
  AND col4 IN (0, 17)
  AND col5 = 5
  AND col2 = 'SOMETHING' ;

A covering index would help to improve efficiency further. Try this one (order matters):

ALTER TABLE conversion_Details
  ADD INDEX covering_index_52431
    (col5, col2, col4, col3, col1) ;


SQL Update Efficiency and Blocking Concern

Table locking is a good thing, it's done so your data doesn't get corrupted.

I wouldn't recommend using NOLOCK when writing data, you should have a look at the answers to Is the NOLOCK (Sql Server hint) bad practice?

An article linked from the question (SQL Server NOLOCK Hint & other poor ideas) has as it's first recommendation:

  Do not use any HINTs until your testing proves that you have an issue
  that can’t be solved any other way than by using a HINT.

As ypercube commented, I would work out why your SELECT query is slow before applying lock hints to you query.


MySQL Dumper creating multiple files - spamming email confirmations

Do not OPTIMIZE tables before backups.  That consumes a lot of time, thereby making there by more 1-minute dumps.  And OPTIMIZE rarely does anything useful.

You have "max file size" = 0 KB -- does that mean unlimited, or small?  I see that the files tend to be a few hundred KB.


Field/datatype for searchable address components?

UPDATE: see this answer for a working example of the below

How would I store it? What you've got is

User data (formatted_address)
Normalized address breakdown (address_components)

I would store only the formatted_address or whatever the user provided. Why?

I don't trust Django or whatever you're integrating with
I want the ability to re-normalize addresses when new data comes out.

PostGIS is a PostgreSQL extension that provides address_standardizer. You can install standardize_address like this,

CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;

From there you can provide the formatted address to standardize_address

SELECT standardize_address( formatted_address )

This will return a composite type stdaddr. I may cache this against the version of the extension and the timestamp of the call to standardize_address.

The stdaddr composite type will also be much easier to geocode which you always seem to need at some point if you're going down this path.

All of that said, Django appears to be using Google Maps to normalize their addresses. If that works for you, you can run with it. I'm sure that's not a bad source of address normalization. How you store that, will depend entirely on how you want to query it. If you're even querying it at all and not just presenting a profile. If you're not JOINing based on any of that I think a new column django_google_maps jsonb is a great starting point.


Guidance for using composite keys to identify rows

After thinking about this, I would revise your table structure just slightly.
First, revise your sheet-size table:

Sheet_size
===========
Id
Length
Width
Thickness

Second, create a sheet-size/type relationship table:

Sheet_size_type
================
Sheet_Type_Id
Sheet_Size_Id

Then, create the following constraints:

The primary key (and index) of Sheet_size should be the id column
There should be some sort of unique key (and index) enforced on the dimensions in Sheet_size.  Consider, are two sheets of dimensions (48, 96, .5) and (96, 48, .5) equal (that is, does the direction of the dimensions matter)?  This sort of problem may be difficult to enforce if through the use of the columns as part of the primary key, but becomes more manageable when using constraints and stored procedures.
The primary key (and index) of Sheet_size_type should use both of the foreign keys, starting with the one with the lower cardinality (probably sheet_type, given your example).  You may want an additional index in the other direction, but it might not be necessary.

This revision will save you database space (as a ratio of the number of sheet types using the same size), and shouldn't impact overhead too much.

There are other potential concerns about equality/uniqueness if you are using a float datatype, as the imprecision may trip you up unexpectedly.  You should consider whether or not a fixed-point type, to some given precision, would be more appropriate.


Is there such a thing as an OLAP cube that can change the number of levels in a hierarchy without having to be rebuilt?

You will need a parent-child dimension, where each member also keeps the id of its parent; something like

Id; name; parent

1; world;null

2; europe;1

3; north america;1

(...)

123; france; 2

And so on. Whether your particular OLAP solution supports this or not?... that’s a different battle.


How can I create a view with self referencing table and a cyclic condition?

SELECT *
    FROM tbl a
    WHERE deleted IS NULL
      AND EXISTS ( SELECT * FROM tbl
                       WHERE id = a.parent_id
                         AND deleted IS NULL );

See if that works, then turn it into a VIEW.


Postgres: strange long request: A is fast, B is fast, A WHERE IN B is slow

Put together, Request A WHERE IN Request B is very long. Is that
  normal?

Yes, that's fairly common when elaborating queries. In the first case, the IN(...) clause has only a handful of constants. The planner knows exactly how many values and how frequent they are in the table, so it has a good chance to find an optimal execution plan (in this case, an index scan).

When the inside of this IN (...) clause is replaced by a subquery, the planner may mis-estimate the number of values that this subquery will produce, or their frequency, or both, especially if the subquery is complex, and so it may choose a non-optimal plan compared to when there is a list of constants inside IN (...).


How to get all connected values as a single column

this is my solution. As I mentioned in a previous comment, it's not the prettiest solution stylistically, but it works.

DECLARE @Id int = 1092

DECLARE @i                      INT = 0,
    @IdToProcess    INT = 0

DECLARE @t TABLE    (   Id  INT,
                    Processed   BIT
                )

INSERT INTO @t (Id, Processed)
VALUES (@Id, 0)

WHILE   (1 = 1 OR @i <= 100)
BEGIN
 SET @i = @i +1
SET @IdToProcess = 0

SELECT  @IdToProcess = MIN(Id)
FROM    @t
WHERE   Processed = 0

SET @IdToProcess = ISNULL(@IdToProcess, 0)

IF @IdToProcess <= 0
BEGIN
    BREAK
END

INSERT INTO @t (Id, Processed)
SELECT  AL.Id2, 0
FROM    dbo.AL AL
WHERE   AL.Id1 = @IdToProcess
        AND NOT EXISTS (SELECT 1 FROM @t T WHERE T.Id = AL.Id2)
UNION
SELECT  AL.Id1, 0
FROM    dbo.AL AL
WHERE   AL.Id2 = @IdToProcess
        AND NOT EXISTS (SELECT 1 FROM @t T WHERE T.Id = AL.Id1)

UPDATE  @t
SET     Processed = 1
WHERE   Id = @IdToProcess
END

SELECT * FROM @T


Running a CTE query in a loop using PL/pgSQL

The DO command has no facility to actually return data (except with RAISE, or you could write to a (temp) table .. ).

You need to create a PL/pgSQL function that can define a return type with RETURNS and call it.

You could return the result with RETURN QUERY EXECUTE. But I suspect the whole operation can be simplified ...

Rewrite as single SQL query

You probably don't need plpgsql or loops at all. Consider this plain SQL query instead:

WITH v AS (
   SELECT '2011-02-13 11:55:11'::timestamp AS _from -- provide times once
         ,'2012-02-13 01:02:21'::timestamp AS _to
   )
, q AS (
   SELECT c.coordinates_id
        , date_trunc('hour', t.calltime) AS stamp
        , count(*) AS zcount
   FROM   v
   JOIN   mytable t ON  t.calltime BETWEEN v._from AND v._to
                   AND (t.calltime::time >= v._from::time OR
                        t.calltime::time <= v._to::time)
   JOIN   coordinates c ON (t.lat, t.lon)
                   BETWEEN (c.bottomrightlat, c.topleftlon)
                       AND (c.topleftlat, c.bottomrightlon)
   GROUP  BY c.coordinates_id, date_trunc('hour', t.calltime)
   )
, cal AS (
   SELECT generate_series(GREATEST('2011-02-02 00:00:00'::timestamp, v._from)
                        , LEAST('2012-04-01 05:00:00'::timestamp, v._to)
                        , '1 hour'::interval) AS stamp
   FROM v
   )
SELECT q.coordinates_id, cal.stamp, COALESCE (q.zcount, 0) AS zcount
FROM   v, cal
LEFT   JOIN q USING (stamp)
WHERE (cal.stamp::time >= v._from::time OR
       cal.stamp::time <= v._to::time)
ORDER  BY q.coordinates_id, stamp;

Instead of looping through rows in table coordinates, join to the CTE and produce the whole result in one go.
As you aggregate per row of coordinates we need the primary key of this table (or any other unique set of columns) I assume a pk named coordinates_id.
I added the CTE v (for "values") on top to provide _from and _to timestamps once only.
I use _from and _to to limit the time range of the calender right away, instead of adding WHERE clauses to trim the surplus in the final SELECT.

GREATEST('2011-02-02 00:00:00'::timestamp, v._from)
LEAST('2012-04-01 05:00:00'::timestamp, v._to)

I use "ad-hoc rows" like demonstrated in this related answer by @kgrittn for a much simpler JOIN condition:

     ON (t.lat, t.lon)
BETWEEN (c.bottomrightlat, c.topleftlon)
    AND (c.topleftlat, c.bottomrightlon)

I cast to time (::time) instead of using extract ('hour' ..), because it's simpler and faster.

I am not 100 % sure this is exactly what you are after, but it should be very close.


How to avoid typecasting leading to full table update?

If you want you can use small trick

update urtable set  `VendorID` = 0 where `OrderID` = 0 and
(select count(*) from urtable)<>(select count(*) from urtable where `OrderID` = 0)


When executing this:

INSERT INTO table VALUES(point(48.277274, -116.5547745));

the two decimal constants are implicitly casted to double precision numbers,
since it corresponds to the closest matching types for the point() function:

test=> \df point
                                  List of functions
   Schema   | Name  | Result data type |        Argument data types         |  Type
------------+-------+------------------+------------------------------------+--------
 pg_catalog | point | point            | box                                | normal
 pg_catalog | point | point            | circle                             | normal
 pg_catalog | point | point            | double precision, double precision | normal
 pg_catalog | point | point            | lseg                               | normal
 pg_catalog | point | point            | path                               | normal
 pg_catalog | point | point            | polygon                            | normal
(6 rows)

On the other hand, when executing this:

SELECT myfun(48.277274, -116.5547745)

the two decimal constants are implicitly casted to real as the type,
because float(n) is equivalent to real when 1<=n<=24, as explained in the
documentation on datatypes:

  PostgreSQL also supports the SQL-standard notations float and
  float(p) for specifying inexact numeric types. Here, p specifies the
  minimum acceptable precision in binary digits. PostgreSQL accepts
  float(1) to float(24) as selecting the real type, while float(25) to
  float(53) select double precision. Values of p outside the allowed
  range draw an error. float with no precision specified is taken to
  mean double precision

This downcast comes with a loss of precision, because, from the same doc page:

  On most platforms, the real type has a range of at least 1E-37 to
  1E+37 with a precision of at least 6 decimal digits. The double
  precision type typically has a range of around 1E-307 to 1E+308 with a
  precision of at least 15 digits

Conclusion: use double precision instead of float(18), which in fact is a synonym for the less-precise real.


SELECT * FROM INFORMATION SCHEMA on V5R3M0

You are experiencing the difference between "system naming convention" and "SQL naming convention" modes.  Originally, system naming mode required a slash character / as the delimiter when qualifying with a schema name, while SQL naming mode used a period . delimiter.  An update to v7.1 now allows either delimiter when using system naming.  V5R3 is no longer supported, nor updated.  On a green screen session you can specify STRSQL NAMING(*SYS).  Or specify system naming convention on your JDBC/ODBC connection string settings.

There are other functional differences between the two modes.  See here and here. Chief among them is that system naming mode allows use of the library list, as long as you don't specify a CURRENT SCHEMA.  But you can specify a current library, with the CHGCURLIB command in CL, or in SQL with CALL QCMDEXC('CHGCURLIB MYLIB',15) (where 15 is the length of this sample command)

DB2 for i catalog views may be found in QSYS2.

SELECT COLNO
      ,SYSTEM_COLUMN_NAME
      ,DATA_TYPE
      ,coalesce(PRECISION,LENGTH)        as len
      ,smallint(SCALE)                   as dp
      ,STORAGE                           as bytes
      ,varchar(COLUMN_TEXT,50)           as fldtext
      ,varchar(substr(LABEL, 1,20),20)   as hdg1
      ,varchar(substr(LABEL,21,20),20)   as hdg2
      ,varchar(substr(LABEL,41,20),20)   as hdg2
      ,CCSID
      ,IS_NULLABLE
FROM QSYS2/SYSCOLUMNS
WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB'
  AND SYSTEM_TABLE_NAME   = 'SOMEPF'

Most IBM i shops used 10-character names (operating system rules) and typically did not use 128-character names allowed by DB2, so generally SYSTEM_COLUMN_NAME, SYSTEM_TABLE_NAME, and SYSTEM_SCHEMA_NAME will be sufficient.

The V5R3 SQL Reference  is available as a PDF, as Dave Jones mentioned.


Galera replication is not synchronous, only what they call 'virtually synchronous'. It's synchronous only up to the point where the write set is to be applied.

  There might be a small period of time when a slave is out of sync.
  This happens because the master may apply events faster than the
  slave. And if you do read from the slave, you may read the data that
  has not changed yet.

  However, this behavior can be changed by setting the
  wsrep_causal_reads=ON variable. In this case, the read on the slave
  will wait until the event is applied (this will obviously increase the
  response time of the read). The gap between the slave and the master
  is the reason why this replication is called virtually synchronous
  replication, and not real synchronous replication.

Source: Multi-Master Replication (Percona XtraDB Cluster - this page also has a diagram which explains the problem).

Another approach is to define one of the nodes as the 'master', and then write only to that node. Critical reads should then also be directed at the 'master' node, whereas less-critical reads can go to any node.

PS: Note that if you plan to write to all the nodes, you might find the following article useful: Galera’s big gotcha for MySQL users.

Edit:

Note that wsrep_causal_reads=ON has been deprecated by wsrep_sync_wait=1.


Question about the UNION operator and its implicit data type conversions

For one, you can certainly simplify your query, for example (I've added schema prefixes, which you should always use):

Select A, B, C
  From dbo.DocumentInfo As Di INNER JOIN dbo.TransmittalSheet TS
  ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS)
  Where TransmittalNumber = 'TS- 0001'
UNION
Select A, B, C
  From dbo.EquipmentJobPlan As EQ dbo.INNER JOIN TransmittalSheet TS
  ON TS.TransmittalID IN (EQ.JobPlanTransmittal_IMP, EQ.JobPlanTransmittal_OBS)
  Where TransmittalNumber = 'TS- 0001';

Or perhaps even:

SELECT A, B, C
  FROM dbo.TransmittalSheet TS
  LEFT OUTER JOIN dbo.DocumentInfo As Di
  ON TS.TransmittalID IN (Di.DocumentTransmittal_IMP, Di.DocumentTransmittal_OBS)
  LEFT OUTER JOIN dbo.EquipmentJobPlan As EQ
  ON TS.TransmittalID IN (EQ.JobPlanTransmittal_IMP, EQ.JobPlanTransmittal_OBS)
WHERE TS.TransmittalNumber = 'TS- 0001'
AND COALESCE(Di.KeyColumn, EQ.KeyColumn) IS NOT NULL
GROUP BY A, B, C;

(I don't know if the GROUP BY is necessary, because I don't know if you are using UNION on purpose for the intention of removing duplicates. Though I do recommend putting table aliases on all column references, so readers have a clue which columns come from which tables. You may need to use COALESCE here and choose which table takes precedence for any of the output columns if A, B or C don't come from TransmittalSheet.)

Now, you say that when you use a UNION you get a conversion error, because in one of the tables column C is a varchar and in another it is a smalldatetime. Your options are:

To fix that. If you are considering those columns to be the same and compatible, they should be of the same data type and contain like data. Which means changing the data type after you remove any data that can't be directly converted to smalldatetime.
If the varchar column needs to have different data that is not compatible with smalldatetime, then perhaps it is best to present that as a different output column in your UNION query.
After that, you can apply an explicit cast on the smalldatetime type so that it can be converted to varchar, instead of the way SQL Server will do it (which is to try to convert the string to a date). If you want to only present valid smalldatetime data, then you can CAST the other way, but use a CASE expression so that the conversion is only attempted on valid data. The implicit conversion can follow the rules of data type precedence, but if the varchar column contains any value that can't be converted to smalldatetime, it fails with Msg 295, as you've discovered. This can happen even if those rows don't appear in the output - the conversion can occur before the rows are filtered out (I've written about this here, here, here and here and it was reported as a bug on Connect (RIP). And because you're using UNION instead of UNION ALL, you may be more likely to hit invalid values because the additional DISTINCT SORT operations this (usually) adds, which can change the way SQL Server will optimize the overall plan.

So, if you can't benefit from the re-writes above (or if they produce different results for some reason), and you can't do 1. or 2., then I would re-write the UNION version either like this:

-- against the table where C is smalldatetime:
SELECT A, B, CONVERT(CHAR(10), C, 120)
FROM ...

-- or against the table where C is varchar:
SELECT A, B, CONVERT(SMALLDATETIME, CASE WHEN ISDATE(C) = 1 THEN C END)
FROM ...

A more localized example you can play with:

CREATE TABLE dbo.foo(A INT, B INT, C VARCHAR(75));
CREATE TABLE dbo.bar(A INT, B INT, C SMALLDATETIME);
INSERT dbo.foo(A,B,C) VALUES(1,2,'three'),(2,4,'20150101');
INSERT dbo.bar(A,B,C) VALUES(1,2,'20050101'),(2,4,'20050101');
GO

-- these both work of course:
SELECT A,B,C FROM dbo.foo WHERE ISDATE(C) = 1;
SELECT A,B,CONVERT(SMALLDATETIME,C) FROM dbo.foo WHERE ISDATE(C) = 1;
GO

-- fails:
SELECT A,B,C FROM dbo.foo
UNION
SELECT A,B,C FROM dbo.bar;
GO

-- succeeds, but may not always:
SELECT A,B,C FROM dbo.foo WHERE ISDATE(C) = 1
UNION
SELECT A,B,C FROM dbo.bar;
GO

-- succeeds:
SELECT A, B, C = CONVERT(SMALLDATETIME, CASE WHEN ISDATE(C) = 1 THEN C END)
FROM dbo.foo
UNION
SELECT A, B, C FROM dbo.bar;

-- succeeds:
SELECT A, B, C FROM dbo.foo
UNION
SELECT A, B, C = CONVERT(VARCHAR(10), C, 120) FROM dbo.bar;
GO

DROP TABLE dbo.foo, dbo.bar;

Finally, please don't CAST( AS Char) with no length, or any other variable type.


Database design that requires an index over two tables

If you have an index on product_categories (category_id), as well as the one you already have on orders (customer_id) then this type of query should be very fast.  You can do a highly specific index scan on each table separately, then hash join the results.

https://explain.depesz.com/s/JEpZ

If that isn't fast enough for you, or you can't get it to use such a plan even when you have indexes in place, then I'm afraid you will have to give us a lot more info, like the actual query plan including timing, and what time you hope to achieve.


Exporting database from SSMS to Azure is sitting on "enabling indexes" without finishing

It is always important to use the latest SSMS for better experience with SQL Azure. Intellisense is now supported and many bugs related to SQL Azure have been fixed. Please download the latest version from here.

For importing a database to an empty SQL Azure database, please use the Data Migration Assistant. Download it from here.


Oracle 11g varchar2 datatype with date and time as YYYY/MM/DD-HH24:MI:SS:FF3

First you need to convert it to TIMESTAMP datatype (because DATE type does not handle fractions of second). Then you can truncate it to date for example:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';

with data as (select '2012/09/06-21:17:30:019' as lastupdate from dual)
select
  trunc(to_timestamp(lastupdate, 'YYYY/MM/DD-HH24:MI:SS:FF3')) date_only,
  trunc(to_timestamp(lastupdate, 'YYYY/MM/DD-HH24:MI:SS:FF3')) + 30 date_only_30
from data;

DATE_ONLY           DATE_ONLY_30
------------------- -------------------
2012-09-06 00:00:00 2012-10-06 00:00:00


Extract from DB2 to SQL Server - where is the data in between the two servers?

It depends where the SSIS Package is running, but yes the machine or server it's running on is a passthrough between your DB2 server and your SQL server. So the data is loaded into memory of the machine where the SSIS Package is running on, and if you're using certain data transformations and variables, those consume additional memory on the SSIS server also.
This article goes a little more in depth and has good information on the memory consumption of SSIS.


The triggered event is invoked for each row.

The syntax is

CREATE TRIGGER c_view_delete_trg INSTEAD OF DELETE
   ON c_view
   FOR EACH ROW
   EXECUTE PROCEDURE c_view_delete();

This means that the function is invoked once for each row that is being deleted. this is why it prints each time one row.


If you are trying to decide whether to build a dynamic or CASE expression, omitting columns whose values haven't changed, I'd say that it depends on your index structure, and you will need to test. In this article, I found that with a single, simple, clustered primary key, the blind update was always faster; this changed in some scenarios when multiple indexes were added.

http://www.sqlperformance.com/2012/10/t-sql-queries/conditional-updates

Where the breaking point is, I'm not sure; nor did I test with dynamic SQL.


Subquery gives no error for a non-existing column with the same name as in the outer query

I run this query:

select * from t1 where c1 in (select c1 from t2);

  The above query should give an error as c1 is not present in t2.
  Instead, it returns all the rows from t1.

No, the query should not give an error. It's a common mistake (thinking that the c1 in (select c1 from t2) refers to t2. It doesn't due to scope resolution, i.e. how column names are resolved (how it is found which table they are referring to). The query:

select * from t1 where c1 in (select c1 from t2);

Can resolve to three different options:

when t2 has a column named c1, it runs as:

select * from t1 where c1 in (select t2.c1 from t2);

when t2 does not have a column named c1, but t1 has, it runs as:
(this is your case!)

select * from t1 where c1 in (select t1.c1 from t2);

and when neither t2 nor t1 have a column named c1, it will throw an error:

select * from t1 where c1 in (select c1 from t2);

-- Error is thrown ("Unknown column c1" or something like that)

  Another version of the
  above query with delete which can be much more disastrous:

delete from t1 where c1 in (select c1 from t2);

  The above query deletes all the rows from t1 when it is just supposed
  to give an error.

For the same reasons, no. The query resolves to and runs as:

delete from t1 where c1 in (select t1.c1 from t2);

so it will delete all rows from t1 as long as t2 table is not empty.

How to avoid these problems?

Always prefix column references with their table names. By doing this, you will always have the result you want or get an error if the column doesn't appear in the table you are prefixing it with.

Your queries should be:

select t1.* from t1 where t1.c1 in (select t2.c1 from t2);

delete from t1 where t1.c1 in (select t2.c1 from t2);

Both of them will throw an error if there is no column c1 in table t2.


Oracle: Get the row count in a cursor when using a OPEN-FOR-USING statement

Do the ROWCOUNT check after looping through the set.  If ROWCOUNT will remain 0 if there were no data in the cursor.  You could use a FOR LOOP construct like:

OPEN ....

FOR ... IN mycursor
LOOP
    /* Process data here */
END LOOP:

IF mycursor%ROWCOUNT = 0 ...

CLOSE mycursor;


In theory no, in practice yes.

After testing for several months on an instance running SQL 2014 Enterprise edition, against 20+ replication instances running MySQL, I found:

distributor_admin does not need SA for day to day operations.

If there is a sync issue (like reboot during patching) if distributor_admin does not have SA it will not self recover.  When distributor_admin does have SA, replication has always self recovered after patching.


Is Scale out deployment for SSRS needed in an Availability group?

Configuring a Scale-Out SSRS instance on Always On is fairly straightforward. You follow the normal Scale-Out deployment steps but when you connect your first and second SSRS instances to the ReportServer database you simply point to the Always On listener.

There is a great blog article here that goes through the steps from start to finish.

You do not specify applicationintent = readonly in the connection string because you want both SSRS instances to connect to the primary ReportServer database. Use that connection string option in the connection strings for your report data sources (if they are in an AG) to redirect report workload to the secondary replica.

Lastly, using the Virtual Account is likely causing your issues as it won't have access to the RS databases. Use a domain service account instead.


Part 1: Proper XML Struture

In order to get anything other than a flat XML layout, you need to use either FOR XML EXPLICIT mode, or nested FOR XML AUTO queries.  Please see the following MSDN sections for complete details, including examples:

Use EXPLICIT Mode with FOR XML
Use Nested FOR XML Queries

Also, you do not need to generate the <SubParentBranchID> element as it is entirely superfluous. One of the nice things about XML is being able to get the parent node from your current location :).

Part 2: Exporting to a File

This can be done rather easily with a simple SQLCLR function to save the contents of a variable (or query) into a text file.

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = false, IsPrecise = true)]
public static SqlString SaveXmlToFile([SqlFacet(MaxSize = 4000)] SqlString FilePath,
    SqlXml XmlData)
{
    try
    {
        File.WriteAllText(FilePath.Value, XmlData.Value, Encoding.Unicode);
    }
    catch (Exception __Exception)
    {
        return __Exception.Message;
    }

    return String.Empty;
}

And there is no need to do any NULL checking of the input parameters via .IsNull() since I am using the RETURNS NULL ON NULL INPUT option:

CREATE FUNCTION [dbo].[SaveXmlToFile](@FilePath NVARCHAR(4000), @XmlData XML)
RETURNS NVARCHAR(4000)
WITH EXECUTE AS CALLER,
     RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [SomeAssemblyName].[FileUtils].[SaveXmlToFile];

Then you can use like this:

DECLARE @Output XML;

SET @Output = (
   SELECT ...
   FOR XML ...;
);

DECLARE @ErrorMessage NVARCHAR(4000);
SET @ErrorMessage = dbo.SaveXmlToFile(N'path/to/file.xml', @Output);

A few easy steps to get the above SQLCLR function working (and pretty much any Assembly you create that needs EXTERNAL_ACCESS or UNSAFE):

The assembly needs to be signed. In Visual Studio, go to Project Properties -> SQLCLR tab -> Signing... button.
"CLR Integration" needs to be enabled:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Create an Asymmetric Key in [master] from the DLL:

USE [master];
CREATE ASYMMETRIC KEY [KeyName]
FROM EXECUTABLE FILE = 'Path\to\SomeAssemblyName.dll';

Create a Login [master] from the DLL:

CREATE LOGIN [SomeLoginName]
FROM ASYMMETRIC KEY [KeyName];

Grant the Key-based Login the appropriate permission:

GRANT EXTERNAL ACCESS ASSEMBLY TO [SomeLoginName];

Please notice how none of those steps was to turn the database property of TRUSTWORTHY to ON!!!

An alternate means of getting this SQLCLR function without doing any coding, compiling, creating of Asymmetric Keys or Logins, etc, is to get a pre-done library that you just install. The SQL# library contains several File System functions and bypasses all of the steps shown above. Please note that I am the author of SQL#, and while there is a Free version, the File System functions are only available in the Full version.


Oracle Wallet Error in Red Hat High Availability Resource

In case anyone is interested in the solution that was identified:

The sqlnet.ora in question contained the following:

ENCRYPTION_WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
       (METHOD_DATA =
        (DIRECTORY = ${ORACLE_BASE}/admin/${ORACLE_SID}/wallet)
        )
   )

Which worked fine with the oracle user profile on the server.  However, when running inside the HA resource, the environment variables were overridden, and ORACLE_BASE was not defined.  Thus when oracle started, the system attempted to locate the "/admin/${ORACLE_SID}/wallet" directory, and upon not finding it, defaulted to a "wallet closed" state.  Queries about the wallet when the database is in this state returned a null record.

When we attempted to troubleshoot the issue, we logged into the host server as oracle, got the user profile from the shell, and thus when we started sqlplus and ran queries it was able to resolve the ENCRYPTION_WALLET_LOCATION and populate the record in the table.  Thus all local queries worked, and suddenly all remote queries "started working" again with little indication in the logs of what had taken place behind the scenes.


How do I join on a timestamp and include NULL entries?

I have managed to get this, by removing the join altogether from the main query and creating a CASE WHEN subquery to look first if a match is made, and assign 0 if not. Then, to sum the invoices on conditions met.

If there is a better method to handle this than the query I now have below, please let me know. Since I am working with a CASE WHEN and subqueries within, it reads to me that this part can be better streamlined somehow. But if it works, it works and does not seem terribly expensive.

Also, please note: above I make a reference to a.account_id in error, on line 18 (second from the bottom). That happened when I modified the exact tables being used in my situation to a more simple set for understanding the SQL being used. To be sure of the fields in my accounting table representation:

a.account_id = IDs representing Chart of Accounts
a.invoice_id = IDs representing the Sales Group on the invoice (this above is in error at a.account_id is 20, and should read a.invoice_id = 20)
a.credit = Double Ledger Accounting Credits
a.debit = Double Ledger Accounting Debits

WITH RECURSIVE t(days,budget) AS (
SELECT 1 as days,b.amount/30 AS budget
FROM budgets b
WHERE b.id = 10
UNION ALL
SELECT t.days + 1,t.budget
FROM t WHERE t.days < 30)
SELECT
t.days AS "Day",
SUM ( CASE WHEN
( SELECT a.date FROM accounting a
LEFT JOIN t ON date_part('day',a.date) = t.days
WHERE a.date >= '2020-04-01'
AND a.date <= '2020-04-30'
AND a.invoice_id = 20
LIMIT 1) IS NULL
THEN 0 ELSE
( SELECT SUM
( CASE WHEN a.account_id IN (17,43,44)
THEN a.credit - a.debit ELSE 0 END)
FROM accounting a
WHERE date_part('day',a.date) = t.days
AND a.date >= '2020-04-01'
AND a.date <= '2020-04-30'
AND a.invoice_id = 20)
END ) AS "Invoices",
t.budget AS "Budget"
FROM t
GROUP BY 1,3
ORDER BY 1;

I appreciate those who took a peek at this query, and considered an approach to solve it.


Enable Replication/CDC from High Availability Group

Transactional replication should be able to do your requirement of replicate some specific tables, as long as they have primary key.

For CDC I have not used and can't say if will work or not.

Regarding the scripts you should be able to generate them when creating the replication setup.


regexp_replace should be enough:
regexp_replace('R.C. Bray.', '([A-Z])(\.)(\w)', '\1\2 \3', 'g')

This replaces a single upper case letter followed by a . directly followed by a "word character" by inserting a space between the dot and the character. It leaves dots that have a whitespace after them untouched.


postgresql - declaring variables to be used for start_time - end_time range

This is what my final solution looked like, and worked for me.
DO $$
DECLARE

--Specify start date - end date in YYYY-MM-DD format.
start_date timestamp := '2020-10-1';
end_date timestamp := '2020-10-5';

BEGIN
    CREATE TEMP TABLE temp_output ON COMMIT DROP AS
    select distinct on (account.id, menu.name, kitchen_item.name)
    account.id as "Account ID",
    account.firstname as "Seller First Name",
    account.lastname as "Seller Last Name",
    account.email as "Seller Email",
    account.phone as "Seller Phone",
    address.address as "Seller Address (Street)",
    address.address_2 as "Seller Address 2",
    account.zip_code as "Seller Zip",
    address.neighborhood as "Seller Neighborhood",
    menu.name as "Name of active menu",
    kitchen_item.name as "Dishes",
    kitchen_item.price as "Price",
    kitchen_item.daily_max_orders as "Quantity",
    menu.pickup_start_time as "Start time",
    menu.pickup_end_time as "End time",
    menu.repeat_mon as "Monday",
    menu.repeat_tues as "Tuesday",
    menu.repeat_wed as "Wednesday",
    menu.repeat_thurs as "Thursday",
    menu.repeat_fri as "Friday",
    menu.repeat_sat as "Saturday",
    menu.repeat_sun as "Sunday",
    order_item.created as "Date of last sale"
    from account
    left join store on account.id = store.account_id
    left join menu on store.id = menu.store_id
    left join menu_item on menu.id = menu_item.menu_id
    left join kitchen_item on (menu_item.kitchen_item_id = kitchen_item.id and store.id = kitchen_item.store_id)
    left join orders on (orders.store_id = store.id)
    left join order_item on (order_item.order_id = orders.id)
    join store_address on store.id = store_address.store_id
    join address on store_address.address_id = address.id
    where orders.placed >= start_date AND orders.placed <= end_date
    order by account.id asc, menu.name, kitchen_item.name asc, order_item.created desc;
END $$;

SELECT * FROM temp_output;


Inside a stored procedure body you have to use a semi-colon to delimit statements. But semi-colons are also the default for delimiting statements in most clients so you end up needing to change your client's delimiter to something else (I use @). You end up with something like:

create procedure cleanup()
begin
    delete from mytable;
    delete from myothertable;
end @


In general, a GRANT is revoked with its opposite command having REVOKE instead of GRANT and FROM instead of TO:

REVOKE ALL ON SCHEMA open_waters FROM test;


Sql, get category tree for values from one table, names and ids

Example.

Structure and data:

CREATE TABLE tree (id INT, parent INT, name VARCHAR);

INSERT INTO tree VALUES
(1,0,'root'),
(2,1,'branch 1'),
(3,1,'branch 2'),
(4,2,'branch 3'),
(5,2,'branch 4'),
(6,4,'branch 5'),
(7,6,'branch 6'),
(8,3,'leaf 1'),
(9,5,'leaf 2'),
(10,7,'leaf 3');

Query:

WITH RECURSIVE cte AS ( SELECT t1.id, t1.parent, t1.name
                        FROM tree t1
                        LEFT JOIN tree t2 ON t1.id = t2.parent
                        WHERE t2.parent is null
                        UNION ALL
                        SELECT t1.id, t1.parent, CONCAT(t1.name, ' > ', t2.name)
                        FROM tree t1
                        JOIN cte t2 ON t1.id = t2.parent
                      )
SELECT name
FROM cte
WHERE parent = 0;

Output:

                    name
---------------------------------------------------------
root > branch 2 > leaf 1
root > branch 1 > branch 4 > leaf 2
root > branch 1 > branch 3 > branch 5 > branch 6 > leaf 3

fiddle

UPDATE:

WITH recursive cte AS ( SELECT t1.category_id, t1.category_parent,    t1.category_name::text
                    FROM s_category t1
                    LEFT JOIN s_category t2 ON t1.category_id = t2.category_parent
                    WHERE t2.category_parent is null
                    UNION ALL
                    SELECT t1.category_id, t1.category_parent,     CONCAT(t1.category_name, ' > ', t2.category_name)
                    FROM s_category t1
                    JOIN cte t2 ON t1.category_id = t2.category_parent
                  )
select

cte.category_name AS category,
s_product.product_id as itemID,
s_product.product_id as itemGroupID,
product_shop_id as productCode,
product_ean as ean,
product_name as title,
product_short_label as description,
product_url as link,
filename as image,
price_tax as price,
price_rec as priceOriginal,
producer_name as brand,
-- as category,
s_category.category_id as categoryID
-- as hierarchy,
-- as params,
-- as name,
-- as value,
-- as labels

from

public.s_product
left join s_product_image on s_product.product_id =             s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join cte on s_product.category_id = cte.category_id
left join s_producer on s_product.producer_id = s_producer.producer_id

where

s_product.product_id='133471'
and s_pricelist_generate

UPDATE2:

Alternative CTE - producing URL for each node.

WITH RECURSIVE
cte AS ( SELECT id, parent, name
         FROM tree
         WHERE parent = 0
       UNION ALL
         SELECT tree.id, tree.parent, CONCAT(cte.name, ' > ', tree.name)
         FROM tree, cte
         WHERE cte.id = tree.parent
       )


Validate deployment failed deny policy in SQL managed instance Azure

Figured out the solution:

Go to the resource group you want to create the managed instance in > Policies > Assignments > Allowed resource types (with the scope subscription/resourcegroupname)   parameters > then from the allowed resource types dropdown select Microsoft.Sql.managedInstances

Sign out and into your account, then managed instance will deploy.

When I got the 'Validate Deployment - 'deny' Policy action' error I went to the Activity log, clicked the Validate Deployment error dropown, clicked the 'deny' Policy action, then looked at the JSON tab - if I looked sequntially to see where the error originated I found this section

"category": {
    "value": "Policy",
    "localizedValue": "Policy"
},
"eventTimestamp": "2019-10-30T13:04:54.8516173Z",
"id": "/subscriptions/XXXXXXX/resourceGroups/XXXXXXXXXXXXX/providers/Microsoft.Sql/managedInstances/minamehere/events/77999a0f-d551-45e0-9b3b-1156b246b50d/ticks/637080374948516173",

"resourceId": "/subscriptions/XXXXXXXXX/resourceGroups/XXXXXXX/providers/Microsoft.Sql/managedInstances/minamehere",
    "status": {
        "value": "Failed",
        "localizedValue": "Failed"

Which is what led me to policies and allowed me to troubleshoot and fix the issue - somewhere I knew I needed to add 'Microsoft.Sql/managedInstances' to the Policy category, which fits with the deny policy error I was experiencing.


Procedure completes prematurely after converting comma-delimited value strings to XML

It turns out that the problem was not with the procedure itself but with the PHP function calling it. Prior to execution, I had to include

        mssql_query("SET ANSI_WARNINGS ON");
        mssql_query("SET ANSI_PADDING ON");
        mssql_query("SET CONCAT_NULL_YIELDS_NULL ON");

in the function. Setting these in the procedure itself had no effect.

My question, directed at the MS SQL Server community was misguided but I'll leave this here in case anyone else has this problem.


How to query against exact values in XML column set

Specifying the text() node before the predicate will be more efficient than having text() in the predicate.

select *
from #ColumnSet as C
where AllValues.exist('*/text()[. = "POSITIVE"]') = 1

Query plan with text in the predicate AllValues.exist('*[text() = "POSITIVE"]') = 1


Only SQL Server Enterprise Edition supports compression - how to work around that?

After checking this link about sql server version check and this one below as well:

How do you find SQL Server version/edition without SSMS installed?

I have just got this script to check what he have in the current server:

  SELECT
    CASE
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'
       --WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019'
       ELSE 'Unknown on April 2019'
    END AS MajorVersion,
      SERVERPROPERTY('productversion') as 'Product Version',
      SERVERPROPERTY('productlevel') as 'Product Level',
      SERVERPROPERTY('edition') as 'Product Edition',
      SERVERPROPERTY('buildclrversion') as 'CLR Version',
      SERVERPROPERTY('collation') as 'Default Collation',
      SERVERPROPERTY('instancename') as 'Instance',
      SERVERPROPERTY('lcid') as 'LCID',
      SERVERPROPERTY('servername') as 'Server Name',
    SERVERPROPERTY('EditionID') AS 'EditionID'

and used it in my create index script down below, after the create table:

        IF object_id('TEMPDB..#RADHE_sp_getsubscriptions') IS NOT NULL
            DROP TABLE #RADHE_sp_getsubscriptions

            create table #RADHE_sp_getsubscriptions (
                publisher sysname NOT NULL,
                publisher_db sysname NOT NULL,
                publication sysname NOT NULL,
                replication_type int,
                subscription_type int,
                last_updated datetime,
                subscriberd_db sysname,
                update_mode int,
                last_sync_status int,
                last_sync_summary nvarchar(4000),
                last_sync_time datetime)

           IF ( SELECT RADHE = CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('edition')) LIKE '%Enterprise%' THEN 1 ELSE 0 END ) = 1

                   CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions
                                       ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication)
                                     WITH (   PAD_INDEX = OFF
                                            , FILLFACTOR = 100
                                            , SORT_IN_TEMPDB = ON
                                            , IGNORE_DUP_KEY = OFF
                                            , STATISTICS_NORECOMPUTE = OFF
                                            , ONLINE = OFF
                                            , DATA_COMPRESSION=PAGE
                                            , ALLOW_ROW_LOCKS = ON
                                            , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

           ELSE

                   CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions
                                       ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication)
                                     WITH (   PAD_INDEX = OFF
                                            , FILLFACTOR = 100
                                            , SORT_IN_TEMPDB = ON
                                            , IGNORE_DUP_KEY = OFF
                                            , STATISTICS_NORECOMPUTE = OFF
                                            , ONLINE = OFF
                                            , DATA_COMPRESSION=NONE
                                            , ALLOW_ROW_LOCKS = ON
                                            , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

--checking the index creation script
-- EXEC SP_COUNT 'TEMPDB..#RADHE_sp_getsubscriptions'

--Results:
 -- SERVER 1 - CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions  ON [dbo].[#RADHE_sp_getsubscriptions__________________________________________________________________________________________0000000A9250] (  [publisher] ASC  , [publisher_db] ASC  , [publication] ASC  )  WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = ON, DATA_COMPRESSION=NONE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]  GO
 -- SERVER 2 - CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions  ON [dbo].[#RADHE_sp_getsubscriptions__________________________________________________________________________________________00000001B274] (  [publisher] ASC  , [publisher_db] ASC  , [publication] ASC  )  WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = ON, DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]  GO

and it is all working fine now.


foreign key violation within trigger on insert of 'new' id in other table as foreign key

That should be simply achieved by setting the foreign key constraints to be checked at the end of the transaction. This is achieved by means of a SET CONSTRAINTS ALL DEFERRED (or, at least, SET CONSTRAINTS name_of_fk_constraint DEFERRED.

As per PostgreSQL (9.3) documentation about SET CONSTRAINTS:

  SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode.

If you want to use this feature, you need to create your constraints with the DEFERRABLE option. If you intend to use them mostly in deferred mode, it is a good idea to make them INTIALLY DEFERRED. By default, constraints are NOT DEFERRABLE.

It is advisable to make constraint names unique (PostgreSQL doesn't force you to; they only need to be unique per table), and to create the names explictily. This way, you can control its deferrability with guarantee that you always specify the right one.

If you want a specific FK constraint to behave already as DEFERRED, you can declare it with:

ALTER TABLE table_a
    ADD CONSTRAINT table_a_col_name_table_b_fk FOREIGN KEY (col_name)
    REFERENCES table_b (col_name)
    ON UPDATE NO ACTION   /* or whichever action is deemed appropriate */
    ON DELETE NO ACTION   /* or whichever action is deemed appropriate */
    DEFERRABLE INITIALLY DEFERRED;

This way, the FK constraint is checked, by default, at the end of the transaction, and the SET CONSTRAINT isn't needed.


How SQL server generates a Query plan with Auto Create Statistics set to OFF

Table variables is another story, what the server uses when there are no statistics is estimation for unknown input:

So in your case the estimation will be select power(1000000, 0.75) = 31622
( = predicate with non unique column prior to 2014)

Related book: T-SQL Querying by Itzik Ben-Gan


VARCHAR missing index not showing up in missing index dmv or query plan suggestions

Almost certainly because it is seen as a trivial plan and those do not activate the missing index feature, there´s a workaround in this blog by Mr Darling
https://www.erikdarlingdata.com/sql-server/whats-the-point-of-1-select-1/


Consider using this for the yes/no:

IF(EXISTS( SELECT 1 FROM A WHERE product_no = B.product_no ), 'Yes', 'No') AS NewCustomer


From your question, I couldnot clearly understand what is the required business scenario, but with the sample data provided I have given an pseudo code and the respective mongodb implementation of that pseudo code.

if invited field is "0800123456"
   if event type is 3 and event status is 11
     select matching documents
or if event type is 4 and event status is 11 and primary owner exists
     select matching documents

Mongo DB Query

db.collection.find({
  $or: [
    {
      $and: [
        {
          "invited": "0800123456"
        },
        {
          "eventtype": {
            $eq: "3"
          }
        },
        {
          "eventstatus": {
            $eq: 11
          }
        }
      ]
    },
    {
      $and: [
        {
          "eventtype": {
            $eq: "4"
          }
        },
        {
          "eventstatus": {
            $eq: 11
          }
        },
        {
          primaryowner: {
            $exists: true
          }
        }
      ]
    }
  ]
})

If this is not matching your scenario, then write your own query with the help of this sample code.

Hope it Helps!


Returning columns based on various conditions in Group By

Let's start by putting your data into a temp table. For future questions you'll want to do this in the question itself so people that want to help you don't have to copy it by hand from a screenshot. I made some guesses about data types:

CREATE TABLE #input (
    Symbol VARCHAR(1) NOT NULL,
    Epoch INT NOT NULL,
    [Open] NUMERIC(9, 1) NOT NULL,
    [Close] NUMERIC(9, 1) NOT NULL,
    [High] NUMERIC(9, 1) NOT NULL,
    [Low] NUMERIC(9, 1) NOT NULL,
    CuVol INT NOT NULL,
    Dt10Min DATETIME NOT NULL
);

INSERT INTO #input
VALUES
('A', 127, 23.6, 24, 23.9, 22.8, 1600, '20100822')
, ('A', 128, 24.6, 24.1, 24.8, 23.6, 3200, '20100822')
, ('A', 129, 23.7, 24.6, 23.9, 23.5, 4800, '20100822');

Now let's write separate queries for all of the information that you want and combine them at the end. Getting the minimum value from a table is very straightforward:

SELECT MIN([Low])
FROM #input;

As is getting the maximum value from a table:

SELECT MAX([High])
FROM #input;

Carrying along column values based on the minimum or maximum of another column is more complex. Around here we call that a "greatest n per group" problem. If you just need a single row then you can use the TOP operator. SQL Server will do a TOP 1 sort which will require any extremely low memory grant. This can be helpful if your table is very large. The query below returns the row with the lowest Epoch:

SELECT TOP 1 [Open]
FROM #input
ORDER BY Epoch ASC;

We can write a very similar query to return data associated with the largest Epoch. It's also easy to select as many columns as needed:

SELECT TOP 1 Symbol, [Epoch], [CuVol], [Dt10Min], [Close]
FROM #input
ORDER BY Epoch DESC;

Now we need to combine all of the queries together to get a single result set. Each query is guaranteed to return a single row, so we might as well use CROSS JOIN. Here's the complete query:

SELECT
  highest_epoch.symbol
, highest_epoch.Epoch
, lowest_epoch.[Open]
, highest_epoch.[Close]
, min_max.High
, min_max.Low
, highest_epoch.CuVol
, highest_epoch.Dt10Min
FROM
(
    SELECT
      MIN(i.[Low]) [Low]
    , MAX(i.[High]) [High]
    FROM #input i
) min_max
CROSS JOIN
(
    SELECT TOP 1 Symbol, [Epoch], [CuVol], [Dt10Min], [Close]
    FROM #input
    ORDER BY Epoch DESC
) highest_epoch
CROSS JOIN
(
    SELECT TOP 1 [Open]
    FROM #input
    ORDER BY Epoch ASC
) lowest_epoch;

This query returns the results that you're looking for:

╔════════╦═══════╦══════╦═══════╦══════╦══════╦═══════╦═════════════════════════╗
║ symbol ║ Epoch ║ Open ║ Close ║ High ║ Low  ║ CuVol ║         Dt10Min         ║
╠════════╬═══════╬══════╬═══════╬══════╬══════╬═══════╬═════════════════════════╣
║ A      ║   129 ║ 23.6 ║ 24.6  ║ 24.8 ║ 22.8 ║  4800 ║ 2010-08-22 00:00:00.000 ║
╚════════╩═══════╩══════╩═══════╩══════╩══════╩═══════╩═════════════════════════╝

Without any indexes, it'll do three scans of the table but it shouldn't require sorting. That could be okay depending on the size of the table and your response time requirements. However, if you need to speed the query up you could consider adding covering indexes for each derived table or you could use a more advanced technique to do the "greatest n per group" calculations. For example, it's possible to use GROUP BY to carry along extra columns as I showed in this answer.


Why is array_agg() slower than the non-aggregate ARRAY() constructor?

There is nothing "old school" or "outdated" about an ARRAY constructor (That's what ARRAY(SELECT x FROM foobar) is). It's modern as ever. Use it for simple array aggregation.

The manual:

  It is also possible to construct an array from the results of a
  subquery. In this form, the array constructor is written with the key
  word ARRAY followed by a parenthesized (not bracketed) subquery.

The aggregate function array_agg() is more versatile in that it can be integrated in a SELECT list with more columns, possibly more aggregations in the same SELECT, and arbitrary groups can be formed with GROUP BY. While an ARRAY constructor can only return a single array from a SELECT returning a single column.

I did not study the source code, but it would seem obvious that a much more versatile tool is also more expensive.

One notable difference: the ARRAY constructor returns an empty array ({}) if no rows qualify. array_agg() returns NULL for the same.