MySQL 5.6 "temporal columns" upgrade - datetime etc.

There are two things here, updating the table structure to 5.6 format and handling the new features.

For the upgrade, run the mysql_upgrade script to check for format incompatibilities. For those tables that it tells you to REPAIR them, run REPAIR TABLE your_table if they are MyISAM and ALTER TABLE your_table ENGINE=InnoDB if they are InnoDB. That will reconstruct those tables (they will be locked while that happens) to avoid any incompatibility issues.

Once you have done that, in relation to temporal SQL changes, the idea is that a TIMESTAMP (and DATETIME, TIME) is now really a TIMESTAMP(0). You can also create TIMESTAMP(n), where n is the number of decimals. That shouldn't tamper any uniqueness of time fields, but you must understand that functions like seconds() can return now a decimal, not an integer.

The other change, making TIMESTAMPs not "DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" are not enabled by default (they mimic the old behaviour). But it is deprecated and can be disabled with the variable explicit_defaults_for_timestamp.

Relaying on now() for creating unique keys is and was already a broken design, because you can insert two rows on the same second. You shouldn't do that.


Average of last 4 runs of a product on SQL Server 2008

with cte as
( select ID, cost, ddate
       , row_number() over (partition by ID order by ddate) as rn
    from table
)
select cte1.ID, cte1.ddate, avg(cte2.cost) as avg, count(cte2.cost) as count
from cte as cte1
join cte as cte2
      on cte2.ID  = cte1.ID
     and cte2.rn >= cte1.rn
     and cte2.rn <= cte1.rn + 3
group by cte1.ID, cte1.ddate
order by cte1.ID, cte1.ddate


Slow running query using date range on non-clustered index column

You should look at the actual plan, not to the estimated one that you posted here.

Your plan uses NL with lookups in the clustered index + sorts, so it's clear the server estimated small number of rows for your filters. When you look at the actual plan and instead of 100-1000 rows see there 500.000 rows go out from your first index seek, there is a problem. It means that full scan should be used, and hash join instead of merge.

So update your post with the actual plan please


MySQL select values based on a sub query

select
  t2.app_status,
  count(*) as status_count
from
  tbl_appointment t2
INNER JOIN
  tbl_notification_flow t1 ON t1.notification_type = t2.app_status
WHERE t1.notification_type != 'Other'

group by t2.app_status ;

with result:

Booked      13
Defaulted   1
Missed      4
Notified    1


Your logic problem occurs in the way you are ORing i.PracticeId=0 into the criteria. That row is ORed so that it's always true. Effectively, you're using OR when you want it to act like ELSE (that's not how OR works).

I believe you want to add in this AND @PracticeId<>1000 to get the desired results:

declare @PracticeId bigint =1000
select * from insurance i
WHERE ISNULL(I.DELETED,0)=0
    and (
          (  @PracticeId =1000 and(I.PracticeId=@PracticeId and I.PracticeId<>0) )
        OR( ((i.PracticeId=0 AND @PracticeId<>1000) OR I.PracticeId=@PracticeId) and I.PracticeId<>1000 )
    )

Note: this query will probably perform terribly and be very slow due to the way the criteria are ORed together. Solving this with dynamic SQL or an IF/ELSE block would likely simplify your logic and perform much better.


Using window function to carry forward first non-null value in a partition

The following query achieves the desired result:

select *, first_value(somevalue) over w as carryforward_somevalue
from (
  select *, sum(case when somevalue is null then 0 else 1 end) over (partition by person order by id ) as value_partition
  from test1

) as q
window w as (partition by person, value_partition order by id);

Note the null case statement - if IGNORE_NULL was supported by postgres window functions this wouldnt be needed (as mentioned by @ypercubeᵀᴹ)


You have to choose a direction when using the recursive CTE. You could add another recursive CTE to account for the descending reference_id's

with recursive
cte as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on t.id = cte.reference_id
     ),
 cte2 as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on cte.id = t.reference_id
     )
SELECT *
FROM cte
UNION
SELECT *
FROM cte2
ORDER BY id ASC;

Result

| id  | unique_id | reference_id |
| --- | --------- | ------------ |
| 43  | 55544     |              |
| 45  | 45454     | 43           |
| 66  | 55655     | 45           |
| 78  | 88877     | 66           |

DB-Fiddle

EDIT:

added version with UNION ALL

with recursive
cte as (
      select t.*
      from mytable t
      where t.id = 66
      union all
      select t.*
      from cte join
           mytable t
           on t.id = cte.reference_id
     ),
 cte2 as (
      select t.*
      from mytable t
      where t.reference_id = 66
      union all
      select t.*
      from cte2 join
           mytable t
           on cte2.id = t.reference_id
     )
SELECT *
FROM cte
UNION ALL
SELECT  *
FROM cte2
ORDER BY id ASC;

DB-Fiddle


Can standby database of a physical database be pluggable or vice versa?

Can standby database of a non-container database be pluggable or vice versa?

In order to plugin a non-CDB standby database into a CDB, the non-CDB standby database needs to be of version 12c as well. So existing non-CDB databases will need to be upgraded to 12c before they can be part of a 12c CDB.

  Suppose there are 2 CDBs on 2 different server. And one has 3 primary PDBs, the other has 3 standby PDBs of the primary with dataguard configuration. If the switchover executed on one primary, will it execute switchover on other 2 PDBs?

The dataguard switchover or failover operations are all performed at the CDB level. When the Data Guard is configured with the CDB database, all PDBs are also replicated to the target. Same way when a failover or switchover operation is performed all the PDBs also change role along with the CDB container.

As migration is a serious task I would recommend to take look at Oracle Support Knowledge based documents.(MOS account required)
Step by Step Examples of Migrating non-CDBs and PDBs Using ASM for File Storage (Doc ID 1576755.1)


How to mark a table as pre-populated with postgresql logical replication

I was able to get this fixed using a bit of a hack. Basically, drop the table from the publication, refresh the publication from the subscriber, add the table to the publication again, then refresh the publication again, but with copy_data=false:

drop the table from the publication.

alter publication my-publication drop table only my-table ;

Refresh the subscription.

ALTER SUBSCRIPTION my-subscription REFRESH PUBLICATION;

The log lines seem to stop at this point. Good!
Add the table to the publication.

alter publication my-publication add table only my-table ;

Refresh the subscription with copy_data (false)

 ALTER SUBSCRIPTION my-subscription REFRESH PUBLICATION with (copy_data = false);

This seems to work, though it seems like it'd be problematic on a table that was changing rapidly.

It'd be nice to put this in a transaction somehow, but the commands are being run alternately on the publisher and the subscriber, so I don't know how that'd work.


What is the infile SAS equivalent in SQL Server?

As per MSSQLTIPS documentation Transferring data from SAS to SQL Server and back SQL Server data typing is much richer than SAS, translating SQL Server values for use with SAS can be challenging. Even the topic of representing and processing missing data values is substantially different between SQL Server and SAS. SQL Server represents missing data values with NULL values, but SAS has a minimum of two ways of representing missing values: a period (.) for numeric missing values and a blank space (' ') for character missing values.

As DSD (delimiter-sensitive data) tells SAS to treats two
consecutive delimiters as a missing value and removes quotation marks
from character values.
Whereas SQL Server represents missing data
values with NULL values.

For further your ref here & here


Clustered Index Update on Nonclustered Columns

When you have a clustered index on a table, the clustered index IS the table!

Mentally  you can substitute "table" for "clustered index" in this instance and it will make sense.

The data for every field in every row is in your clustered index.  The clustered index just sets the order of the physical pages in the database to be organized by your clustering key(s).

You can always fall back on the phone book analogy for these things, too:  in  your classic phone book, the data is clustered on Last Name, First Name.  Each entry still has PhoneNum, Address at the leaf level but you don't order by that.  The pages are in physical order by the the keys.

I can't advise on optimizing the query unless you show us the table and query you are running, but basically this cost will be paid one way or another.  If you don't update the clustered index it will be a table update and a table scan.


What is 'Log Memory' in Query Store 2017

If we look at the documentation for the underlying object, sys.query_store_runtime_stats, we'll see it has the following descriptions:

avg_log_bytes_used - Average number of bytes in the database log used by the query plan, within the aggregation interval.Note: Azure SQL Data Warehouse will always return zero (0).
last_log_bytes_used - Number of bytes in the database log used by the last execution of the query plan, within the aggregation interval. Note: Azure SQL Data Warehouse will always return zero (0).
min_log_bytes_used - Minimum number of bytes in the database log used by the query plan, within the aggregation interval.  Note: Azure SQL Data Warehouse will always return zero (0).
max_log_bytes_used - Maximum number of bytes in the database log used by the query plan, within the aggregation interval.Note: Azure SQL Data Warehouse will always return zero (0).
stdev_log_bytes_used - Standard deviation of the number of bytes in the database log used by a query plan, within the aggregation interval.Note: Azure SQL Data Warehouse will always return zero (0).


Splitting a String and ORDER BY DESC its matching Values

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

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

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

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

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

Test in fiddle.

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

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


First of all , The stored procedures you have are totally different. So the performance are not really compare-able.

Thought the final output you receive is the same , in the first SP you are doing a simple insert where as in second SP you are inserting and then doing an update.
So it is pretty obvious that the second procedure is doing more work


How to configure global charset on MySQL

You need to incorporate the following into your my.cnf file

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
default-character-set = utf8
collation-server = utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

You can find more information on the following manual pages

http://dev.mysql.com/doc/refman/5.6/en/charset-configuration.html
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_system
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_character_set_server

In response to Mark's answer, if you need to convert existing tables/databases to the correct character set you will need a suitable ALTER query. Something similar to the below:

ALTER TABLE `table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Alternatively there's a great PHP script here which does it for you.


You design is basic and cover

1 user -> many items
1 item -> many comments
1 user -> many comments

The question is would you like the user to be able to comment on the same item multiple of times?
if yes, you do have a good design else we would need to work on it.


Cannot reclaim Index Unused Memory in In-Memory OLTP

I read somewhere that the garbage collector doesn't free up space until it faces memory pressure but it seems weird that it would hold so much unused memory.

Is it weird, though? Why do garbage collection now when it can be put off until later :)
From the documentation, a confirmation of your thought (emphasis mine):

After a user transaction commits, it identifies all queued items associated with the scheduler it ran on and then releases the memory. If the garbage collection queue on the scheduler is empty, it searches for any non-empty queue in the current NUMA node. If there is low transactional activity and there is memory pressure, the main garbage-collection thread can access garbage collect rows from any queue. If there is no transactional activity after (for example) deleting a large number of rows and there is no memory pressure, the deleted rows will not be garbage collected until the transactional activity resumes or there is memory pressure.

Your test workload seems quite small and I wouldn't be surprised if it isn't enough to trigger any collection, especially with how robust that system's memory resources are.
With 2TB of memory, I wouldn't sweat < 500MB of memory usage for this purpose, nor would I worry about this until it became an actual problem.


How to script data in deterministic order

I personally would use a tool (as indeed I already use) , such as are sold by Apex, Red Gate, Toad, and others.  These could be used to script only the rows that have changed.  That would make your looking for changes very simple indeed.

If you are looking for a free tool you might examine http://opendbiff.codeplex.com/.  I have not personally used it, but some people find it useful.  (There are doubtless other such tools in the public domain.)


Updating Database table from another machine

There is an Oracle Support Note ORA-29913: Error in Executing ODCIEXTTABLEOPEN Callout (Doc ID 458620.1)  related to your errormessages. You have an external table (ORGANIZATION EXTERNAL) with data in the file 'testfile.txt'  in the Oracle directory "TEST_9F86D0818".

The support note says that this will happen if your os directories that contain the file testfile.txt does not have the appropriate permissions.

So let's check if this file does not exist or if there are problems with the os permissions on the file or the os directories where it resides.

Find out the path of the Oracle directory "TEST_9F86D0818"

select directory_path
from all_directories
where directory_name='TEST_9F86D0818';

Assume the directory_path retrieve in 1 is '/u01/app/oracle/data' or 'C:\u01\app\oracle\data' if you are on a Windows system. Then login to the database server and check if '/u01/app/oracle/data/testfile.txt' (C:\u01\app\oracle\data\testfile.txt) exists.
Let's assume the user that runs your Oracle Software on your database server is oracle. Usually this is the case on a Linux server. Then check if this user can read testfile.txt:

head /u01/app/oracle/data/testfile.txt > /dev/null

or

type C:\u01\app\oracle\data\testfile.txt > NUL

if you are on a Windows system.
If this generates an error message then this is the reason for your problem that you have to eliminate.
With a user that has access to directory and can execute th UTIL_FILE package (e.g. a dba user) connect to the databae with sqlplus and execute the following
command:

DECLARE
  V1 VARCHAR2(32767);
  F1 UTL_FILE.FILE_TYPE;
BEGIN
  F1 := UTL_FILE.FOPEN('TEST_9F86D0818','testfile.txt','R');
  UTL_FILE.GET_LINE(F1,V1);
  UTL_FILE.FCLOSE(F1);
exception
  when others then
    dbms_output.put_line('ERROR: ' || SQLCODE
      || ' - ' || SQLERRM);
    raise;
END;
/

If an error is rased, this is the reason for your problem that you have to eliminate. If no error is raised show us the create statement of your external table.


Is it possible to increase query performance on a narrow table with millions of rows?

Why the the optimiser doesn't go for your your first index:

CREATE NONCLUSTERED INDEX [CommonQueryIndex] ON [dbo].[Heartbeats]
(
    [DateEntered] ASC,
    [DeviceID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Is a matter of selectivity of the [DateEntered] Column.

You told us that your table has 44 million rows. the row size is:

4 bytes, for the ID, 4 bytes for the Device ID, 8 bytes for the date, and 1 byte for the 4 bit columns. that's 17 bytes + 7 bytes overhead for (tags, Null bitmap, var col offset,,col count) totals 24 Bytes per row.

That would rougly translate to 140k pages. To store those 44 million rows.

Now the optimiser can do two things:

It could scan the table (clustered index scan)
Or it could use your index. For every row in your index, it would
then need to do a bookmark lookup in the clustered index.

Now at a certain point it just becomes more expensive to do all these single lookups in the clustered index for every index entry found in your non clustered index. The threshold for that is generally the total count of lookups should exceed 25% tot 33% of the total table page count.

So in this case: 140k/25%=35000 rows 140k/33%=46666 rows.

(@RBarryYoung, 35k is 0.08% of the total rows and 46666 is 0.10 %, so I think that is where the confusion was)

So if your where clause will result in somewhere between 35000 and 46666 rows.(this is underneath the top clause!) It's very likely that your non clustered will not be used and that the clustered index scan will be used.

The only two ways to change this are:

Make your where clause more selective. (if possible)
Drop the * and select only a few columns so you can use a covering
index.

now sure you could create a covering index even when you use a select *. Hoever that just creates a massive overhead for your inserts/updates/deletes. We would have to know more about your work load (read vs write) to make sure if that's the best solution.

Changing from datetime to smalldatetime is a 16% reducion in size on clustered index and a 24% reduction in size on your non clustered index.


MySQL: Why is inserting data with transactions faster then without them

Without explicit transaction, all statements are in auto-commit mode - so each insert is separate transaction. And that comes with some overhead for syncing etc.

If you insert all rows without commiting in between, mysql can do lot of it in memory/cache and sync it to disk only once (simplified, caches will probably be flushed multiple times by filesystem and OS).

In case of no explicit transaction each insert has to wait for disk to confirm writing all changes before returning and letting the loop continue. This can be sometimes relaxed a bit by flushing periodically instead after each transaction, but you might lose some data on failure IIRC.

On the other side - if you insert/update too many rows in one transaction, log with information for possible rollback can grow a lot and slow everything down. So transaction size should be reasonable (not millions of rows).

Some tips: http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-transaction-management.html


I was able to do this and it was a very smooth process at the end.

Just took a snapshot of the current 12c RDS instance and restore it. Restoration creates a new 12c RDS instance, but have to be careful about the instance type.

After all I upgraded it to 18c with a one click as they say in the documents.

The schema, users, passwords were all there in the new RDS as the old one. Did not require any kind of Oracle administrator skill.


ADD PERIOD FOR SYSTEM_TIME on table failed

I think I have found how to fix my issue, but I am not going to accept this as answer as I am not able to explain what is causing the problem and guarantee this will work anytime. It's fix found after a lot of testing and I will be glad if someone can bring more light here.

I have never used datetime2 with precision. So, I went back to the source of this format datetime2(0) - Alter Non-Temporal Table to be System-Versioned Temporal Table. The only difference with the script I have been using was the date time function. I used GETUTCDATE() as I do not need to be so precised with datetime(0)(2018-03-15 07:21:02 for example) and in the example it is  SYSUTCDATETIME(). So, I have changed it.

I have create a script which is dropping a database if exists, restoring a database from backup and then executing my code in a loop (as I said I get the error sometimes and it was very hard to reproduce it).

I have run a the script a lot of times and I was getting different number of fails (sometimes 70%, sometimes 50%, sometimes below):

I have found this Why is GETUTCDATE earlier than SYSDATETIMEOFFSET? discussion about differences between old and new date time functions. Then build the following query:

DECLARE @UTC DATETIME2(0) = GETUTCDATE();
DECLARE @SYSUTC DATETIME2(0) = SYSUTCDATETIME();

WHILE DATEPART(SECOND, @UTC) = DATEPART(SECOND, @SYSUTC)
BEGIN;
    SET @UTC  = GETUTCDATE();
    SET @SYSUTC  = SYSUTCDATETIME();
END;

SELECT @UTC AS [UTC]
      ,@SYSUTC AS [SYS UTC]
      ,DATEPART(SECOND, @UTC) AS [UTC sec]
      ,DATEPART(SECOND, @SYSUTC) AS [SYS UTC sec]
      ,CASE WHEN @UTC < @SYSUTC THEN 1 ELSE 0 END AS [TimeTravelPossible]
      ,CONVERT(DATETIME2(0), @UTC) AS [UTC date]
      ,CONVERT(DATETIME2(0), @SYSUTC) AS [SYS UTC date]
      ,IIF(CONVERT(DATETIME2(0), @UTC) = CONVERT(DATETIME2(0), @SYSUTC), 1, 0) AS [Are The Same];

I just wanted to check if I can get different datetime2(0) dates using sys and not sys date time function. And of course it is possible.

Maybe, the checks that the engine is making is doing something like this, comparing its current date with my newer date and this is causing the error - open records with start of period set to a value in the future.

I have change the script like this and executed 1 000 times last night - no errors were generated. So, I believe I have fixed this particular issue, but I can't be sure.

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
ADD [SysStartTime] DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysStart DEFAULT SYSUTCDATETIME()
   ,[SysEndTime] DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_AnalysisCustomRollupsV2JoinGroups_SysEnd DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime]);

ALTER TABLE [dbo].[AnalysisCustomRollupsV2JoinGroups]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AnalysisCustomRollupsV2JoinGroupsChanges));


Simplifying the "repetitive" code of a query involving UNION ALL, WHERE and LIMIT

You can use the ROW_NUMBER() analytical function to generate row numbers for each partition of (points, type):

SELECT
  *,
  ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY rand ()) AS rownum
FROM
  questions

And then you can compare rownum to a certain value that depends on points and type:

WHERE
  rownum <= some_expression

Of course, you will need to use nesting in order to be able to reference rownum like that:

SELECT
  ...
FROM
  (
    SELECT
      ...
      ROW_NUMBER() ... AS rownum
    FROM
      ...
  ) AS derived
WHERE
  rownum <= ...

And you need to keep in mind that because of the nesting the rownum column becomes part of the derived table's column set – so, specifying * as the main SELECT's column list will include the rownum column as well. If you want it out of the result, you will have to list each questions column explicitly:

SELECT
  question_id,  /* or whatever the PK column is going to be called */
  points,
  type,
  ...  /* other "questions" columns */
FROM
  (
    ...

Taking all of the above points into account (the last one in particular), I am unsure if the resulting query can be called a simplification of the original, but here is my attempt anyway:

SELECT
  question_id,
  points,
  type,
  ...
FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY rand ()) AS rownum
    FROM
      questions
  ) AS derived
WHERE
  rownum <= CASE
              WHEN points = 3 AND type = 1 THEN 6
              WHEN points = 2 AND type = 1 THEN 4
              WHEN points = 3 AND type = 1 THEN 2
              WHEN points = 3 AND type = 0 THEN 10
              WHEN points = 2 AND type = 0 THEN 6
              WHEN points = 1 AND type = 0 THEN 4
            END
;

The CASE expression returns a row limit for each required partition and a NULL for any other, thus effectively providing a filter to eliminate all irrelevant rows (the rows where points and type have values other than those mentioned in the conditions). This kind of filter, however, is not pushed down to the underlying questions table and, therefore, does not prevent the query from generating row numbers for all combinations of (points, type) before filtering out those that are not needed. You can avoid the redundant work by adding an explicit filter to the questions table like this:

  ...
FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY rand ()) AS rownum
    FROM
      questions
    WHERE
      points IN (1, 2, 3)
      AND type IN (0, 1)
  ) AS derived
WHERE
  ...

Alternative

Alternatively you can rewrite the entire query in a different way, such that rows are filtered and row limits assigned at the same time. If you use a virtual table like this:

(
  SELECT 3, 1,  6 UNION ALL
  SELECT 2, 1,  4 UNION ALL
  SELECT 1, 1,  2 UNION ALL
  SELECT 3, 0, 10 UNION ALL
  SELECT 2, 0,  6 UNION ALL
  SELECT 1, 0,  4
) AS limits (points, type, rowlimit)

you can join questions to it and thus have both a filter and a way to assign row limits to each partition. The full query will then look like this:

SELECT
  question_id,
  points,
  type,
  ...
FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY rand ()) AS rownum
    FROM
      questions AS q
      INNER JOIN
      (
        SELECT 3, 1,  6 UNION ALL
        SELECT 2, 1,  4 UNION ALL
        SELECT 1, 1,  2 UNION ALL
        SELECT 3, 0, 10 UNION ALL
        SELECT 2, 0,  6 UNION ALL
        SELECT 1, 0,  4
      ) AS limits (points, type, rowlimit)
        USING (points, type)
  ) AS derived
WHERE
  rownum <= rowlimit
;

You can then go further and make the limits dataset an actual table in your database. You have mentioned that requirements are not going to alter, but perhaps knowing that you can configure the requirements in the table will change that perspective.

Product-related notes

You mention that you have not decided in which SQL product this is going to be used. Here are some product-related notes to consider.

Both variations of the solution will work in MySQL 8.0 or newer version. Version 8.0 is the minimum because that is where support for ROW_NUMBER() was first introduced in MySQL.
The other product you mentioned, SQL Server, has supported ROW_NUMBER() since version 2005 but other aspects of the solution will require changes in order to make it work in SQL Server.

2.1. You are using ORDER BY RAND() to randomise the rows selected. In SQL Server, the RAND() function is a runtime constant†, therefore you will need to use a different method; ORDER BY NEWID() could be one:

  ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY NEWID()) AS rownum

2.2. The JOIN ... USING syntax is not supported in SQL Server. You will need to replace it with the JOIN ... ON one. Because of this switch, you will also need to rewrite the nested SELECT column list. The reason is, USING automatically suppresses duplication of the same-name columns used for joining, points and type, when you are using * in the SELECT clause. The ON operator has no such effect, therefore you will need to rewrite the derived query like this:

  (
    SELECT
      q.*,
      limits.rowlimit,
      ROW_NUMBER() OVER (PARTITION BY points, type ORDER BY NEWID()) AS rownum
    FROM
      questions AS q
      INNER JOIN ( ... ) AS limits (points, type, rowlimit)
        ON q.points = limits.points AND q.type = limits.type
  ) AS derived

2.3. While not a required change per se, you can take advantage of SQL Server's support of the VALUES constructor as a means of defining a derived table to make the limits definition more compact:

  (
    VALUES
      (3, 1,  6),
      (2, 1,  4),
      (1, 1,  2),
      (3, 0, 10),
      (2, 0,  6),
      (1, 0,  4)
  ) AS limits (points, type, rowlimit)

†The result of the function will not change for the duration of the SELECT statement's execution.


Any queries that read the table WITH (NOLOCK) or with READ UNCOMMITTED isolation level will proceed despite your WITH (TABLOCKX).

More info about lock modes can be found here:
https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx

Personally I'd say that if you have queries reading from the table WITH (NOLOCK) then they should fully anticipate that they will get bad results so blocking those reads wouldn't be a priority. If your those queries need to return data that is actually correct then they should not use NOLOCK. https://sqlstudies.com/2015/03/18/why-not-nolock/


SQL Server Error - The connection is broken and recovery is not possible

It would appear that the cause of this was that the server was stuck in a perpetual reboot loop and was losing and regaining connectivity. Power cycling the server resolved the issue.


Is there any way to get the estimate cost of executing a query in MySQL?

In short

Run your SELECT ... query
SHOW STATUS LIKE 'last_query_cost'
if the answer was 0 rerun the query with
select  SQL_NO_CACHE ... and do step 2 above again


Handling delayed key info in real time data warehouse

One possible solution is to create missing dimension members solely based on what is available in the fact data, and then merge (aka upsert) the full dimension data as it arrives later.

For this to work, the attributes of your dimensions will need to be able to accommodate ‘unknown’.


SQL Server Management Studio: how to parse stored procedure code and check if table columns exist?

I don't think there's any way to do this with a parse, but you could try running the script inside a transaction then rolling back that transaction at the end. If there's an error, it should throw it, but even if there isn't, there won't be any lasting changes.


Algorithm used to perform offset/limit over a union all result set

When limiting clauses like OFFSET are applied depends on the query, particularly what the result is ordered by.

If you are ordering by columns that are also being used to filter the results (in a WHERE clause for instance) and are indexed then the limitation will often be applied early. The query planner will try to find just the first rows that match the search condition. Otherwise the limit is usually applied after everything else, so the server is likely to spool the results into a temporary store then sort and apply your OFFSET/LIMIT filter.

For a UNION query on its own, it depends on the brains in the query planner so you might have to look at its plan output to know (I'm not a postgres expert), and again it may depend on the rest of the query details. However the work is done (which will affect performance) the output will be the same though: the limitation will be applied to the complete result.

In the example you give the UNION query is actually providing a derived table, named rs. The limitation will definitely be applied to the whole result here so it is functionally the same as if you had done SELECT * FROM <some_table> OFFSET 100000 LIMIT 10. Because they are the same you could rewrite your example as simply:

SELECT * FROM rs1
UNION ALL
SELECT * FROM rs2
OFFSET 100000 LIMIT 10;

and the results would be the same.

One important note: you have not specified any ordering clause here. This may be because you are giving a minimal example so forgive me if I'm telling you something you already know, but without an ordering clause you should assume the output order to be arbitrary, possibly random, and likely to change between runs of the same query. Sometimes this is fine (you just want the data and you don't care about the order) but as soon as you are using things like OFFSET/LIMIT/TOP/... you do care about the order because the query after OFFSET 10000 LIMIT 10 is likely to be OFFSET 10010 LIMIT 10 and you want that to definitely want ten different rows by some ordering (not an arbitrary ten that could in theory end up being the same ten as last time). In practise the ordering of output is usually more stable than this, but it isn't always by any means so you should always provide an ordering clause if a sudden change in output order would cause undesirable behaviour.


Listing the Microsoft SQL Server configuration with the values set by mssql-conf?

There is no command line option to view what the values are actually set too, for now you'll have to check the file yourself

cat /var/opt/mssql/mssql.conf

However, it does not show the defaults. Stock, it has two options. One for sqlagent.enabled and one for EULA.accepteula. If you change one of those values as in the case of disabling CEIP,

$ sudo /opt/mssql/bin/mssql-conf set telemetry.customerfeedback false

Your conf file will look like this,

$ cat /var/opt/mssql/mssql.conf
[EULA]
accepteula = Y

[sqlagent]
enabled = false

[telemetry]
customerfeedback = false


Would it not be safe to assume that your products might have more than one restriction?

In that case, don't you need an intersection between product and restriction (type)?

Consider this ERD:

Note that restrictions are shown using an entity-sub-typing model.  You can keep distinct restriction value types appropriate to the kind of data that you require.  At the same time, the individual specific restriction types share a key space with the restriction header table.  That makes the intersection: PRODUCT_RESTRICTION easier to define and use.

There are different ways of doing it, but you'd probably want to consider including a partitioning attribute in the RESTRICTION table.

Walter Mitty suggests:

  If you want to model restrictions as a case of subtyping, there are
  design patterns for this case.   There are two tags over in
  StackOverflow where the info tab might help you:
  single-table-inheritance class-table-inheritance

Joel Brown responds:

The restriction model I have illustrated is one common way of implementing subtyping.  There are other variations.


Limiting the CPU/Memory utilization for a Query in SQL server 2008R2

The short answer is 'Yes'.
You can use RG to limit the CPU consumption of that process.
Note that RG is only available in Enterprise and Data Center editions of SQL Server 2008R2. Use the Microsoft documentation excellent walk-through to learn about using RG: Resource Governor How-to Topics

Longer answer - RG does not address the root cause, it's a band aid to help with the symptoms, and make the disease less disturbing to the other users.
If I were in your place, before resorting to RG solutions, I would invest some time trying to optimize the process itself.
Although 'accessing 80 million rows' doesn't mean much, and just accessing data rows should not consume a lot of CPU.
My experience has proven again and again that many processes that take hours, can be reduced to minutes (and in some cases, to seconds), and be made to consume orders of magnitude less resources than the original.
Optimization effort should include not only the technicalities, such as proper indexing, schema tweaks, query syntax, and plan investigation, but also higher level evaluation of the process architecture and flow, does it do a full refresh or incremental update? how many times do you access the same tables? are there many temp tables involved? too many steps? Perhaps some redundant work being done?
If you provide some more details about the process, and why it takes so much CPU, people here will be happy to help you.

Of course, there is no guarantee that you will succeed, and eventually you may end up resorting to use RG, but IMHO the effort is very worth while.


I should have written this long ago, but better late than never.

How did I resolve the issue? Each backup attempt created a dump file which I analyzed using WinDbg. These files indicated that the error originated in the sqlmin.dll assembly. I did a search for hotfixes for that assembly, found some, applied them, and after a reboot, the backups started working again.

For anyone who comes across weird SQL Server bugs like this, try looking at the dump files for clues.


Not sure I understood it correctly, but if you really want to match "first order item with first available inventory" etc. until you get out of "available" units, you have to give each row its "rank" - the first/second/third/.. designation. It is possible to do using user variables (and probably by some other tricks).

http://sqlfiddle.com/#!9/cd6ef/4

select
 order_item_id, order_item_product, order_item_rn,
 inventory_id, inventory_unit, inventory_product, inventory_rn
from
(select
 `id` order_item_id,
 `product` order_item_product,
 @r:=CASE WHEN @p <> `product` THEN 1 ELSE @r+1 END AS order_item_rn,
 @p:=product AS pset
from
 (SELECT @r:= 0) s,
  (SELECT @p:= '') c,
  (SELECT *
   FROM order_item
   group by id
   ORDER BY product, id
  ) t1) t3
join
(select
 `id` inventory_id,
 inventory inventory_unit,
 `product` inventory_product,
 @r:=CASE WHEN @p <> `product` THEN 1 ELSE @r+1 END AS inventory_rn,
 @p:=product AS pset
from
 (SELECT @r:= 0) s,
  (SELECT @p:= '') c,
  (SELECT *
   FROM inventory
   group by id
   ORDER BY product, id
  ) t2)t4
ON
 t3.order_item_product = t4.inventory_product
 AND t3.order_item_rn = t4.inventory_rn;

But you should probably do it at the application level and not in SQL at all. For tables containing lot of rows I expect this solution to be quite slow.


Hi disk I/O and crash when turning on sync_binlog in MySQL

Please keep in mind what MySQL Documentation says on sync_binlog:

If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after sync_binlog commit groups are written to the binary log. The default value of sync_binlog is 0, which does no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log's contents from time to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

Apparently, you are writing lots of commit groups in one write operation to the binary log.

Setting sync_binlog to 0 lets the OS flush commit groups to disk (on its own time)
Setting sync_binlog to 1 lets mysqld flush commit groups to disk on each write

You could set sync_binlog > 1 to throttle mysqld's flush of the binlog.
You should expect setting sync_binlog to create more disk I/O, not less.
If your DB Server is a bare-metal server, you may need to check your disk controller and either change the batteries or get a better disk controller.
UPDATE 2015-04-02 10:57 EST
You just mentioned in your comment that you are using SSDs. I wrote a post over a year ago (MySQL on SSD - what are the disadvantages?) stating that SSDs are not all that good for sequential writes. In your case, the sequential writes are happening on the binlogs. SSD write performance is the same regardless of sequential writes and random writes. I would consider moving your binary logs to HDD rather than on SSD.


A join aggregate behaviour I can't understand

The WHERE clause is checking s.student_type='instate'.

In a LEFT JOIN, everything on the right side is NULL, so the WHERE clause would not evaluate to anything.

If I may, I'd like to suggest a slight rewrite of your query:

Instead of this

SELECT c.class_id,
       SUM
       (
        CASE
         WHEN s.student_id IS NULL THEN 0
         ELSE 1
        END
       ) as total_students
  FROM classes c LEFT JOIN students s ON c.class_id = s.class_id
GROUP BY c.class_id;

write it more compact like this

SELECT c.class_id, SUM(1-ISNULL(s.student_id)) as total_students
FROM classes c LEFT JOIN students s USING (class_id)
GROUP BY c.class_id;

Now, if you would like to filter on student_type and have the zeros to come back, you must rewrite the query like this:

SELECT
    A.class_id,
    IFNULL(B.total_students,0) total_students
FROM classes A LEFT JOIN
(
    SELECT c.class_id, COUNT(s.student_id) as total_students
    FROM classes c INNER JOIN students s USING (class_id)
    WHERE s.student_type='instate'
    GROUP BY c.class_id
) B USING (class_id);

UPDATE 2012-03-08 13:11 EST

Now I am curious

This is your query

SELECT class_id,
(
    SELECT count(*) FROM students a
    WHERE a.class_id=e.class_id
    AND a.student_type='instate'
) FROM classes e WHERE class_account='92';

Please Try This One

SELECT
    COUNT(DISTINCT e.class_id)
FROM
    (SELECT class_id FROM classes WHERE class_account='92') e
    INNER JOIN
    (SELECT class_id FROM students WHERE student_type='instate') a
    USING (class_id)
;

Make sure you have the following indexes before running this new query:

ALTER TABLE classes ADD INDEX (class_account,class_id);
ALTER TABLE students ADD INDEX (student_type,class_id);


How can I create a bool column for a view based on the result of a statement?

"a boolean column stating whether or not a group has any users"
Use EXISTS:
CREATE VIEW group_info AS
SELECT g.name, NOT EXISTS (SELECT 1 FROM users u WHERE u.group = g.id) AS empty
FROM   groups g;

This returns 1 row per group, no matter whether there are users or not - not one row per user like you had, but probably didn't want - so we don't need DISTINCT.
It's largely irrelevant whether you write SELECT 1 or SELECT * or SELECT 'foo' in the EXISTS subquery. Only the bare existence of at least one row matters.
With number of users
CREATE VIEW group_info AS
SELECT g.name, count(u.group) AS ct_users
FROM   groups     g
LEFT   JOIN users u ON u.group = g.id
GROUP  BY g.name;

LEFT JOIN is crucial to retain groups without users.


Are there differences in performance of getting data between FullText Search and indexed columns

In large part it's going to depend on if @filter is a word or a group of words.  Full text indexes essentially break down the contents of the column into the individual words and let you search for a word (group of words) or a synonym etc.  150k rows really isn't all that much to be searching on as these things go, but you may very well see some performance increase, again depending on what @filter is.  I would also double check the rest of your indexes.  For example one on art.id and one on lager.art_id and lager.skl_id.  Also given how few columns you are returning (assuming that is the case in the real query not just this example) you might consider making them covering indexes by "including" (look at the key word INCLUDE in CREATE INDEX) the extra fields in your indexes.  That lets the query just look at the index and not have to go back to the original table.


Is there a shorthand way to 'Auto_Fix' all Orphaned Users in an SQL Server 2008 R2 database?

Ted Krueger (@onpnt on twitter) wrote a great script that does this. It adds logins for any user without a login and runs the auto_fix. He even wrote one that includes fixing Windows logins:

http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users

Of course if you want to test it first (or merely perform an audit) you can comment out the action lines (EXEC) and just print out the results.


Migration to databases Domain Users do not have access

Users are tied to logins with SIDS. If you moved your DBs, you may need to recreate that mapping.

You can either create another login for DOMAIN\DOMAIN USERS and go through and manually remap, or
run sp_revlogins (link) and sp_change_users_login 'Report' (link)

If sp_change_users_login 'Report' finds orphans, run sp_change_users_login 'Auto_Fix', 'user'.


Database design: Two 1 to many relationships to the same table

Relational databases are not built to handle this situation perfectly.  You have to decide what is most important to you and then make your trade-offs.  You have several goals:

Maintain third normal form
Maintain referential integrity
Maintain the constraint that each account belongs to either a corporation or a natural person.
Preserve the ability to retrieve data simply and directly

The problem is that some of these goals compete with one another.

Sub-Typing Solution
You could choose a sub-typing solution where you create a super-type that incorporates both corporations and persons.  This super-type would probably have a compound key of the natural key of the sub-type plus a partitioning attribute (e.g. customer_type).  This is fine as far as normalization goes and it allows you to enforce referential integrity as well as the constraint that corporations and persons are mutually exclusive.  The problem is that this makes data retrieval more difficult, because you always have to branch based on customer_type when you join account to the account holder.  This probably means using UNION and having a lot of repetitive SQL in your query.

Two Foreign Keys Solution
You could choose a solution where you keep two foreign keys in your account table, one to corporation and one to person.  This solution also allows you to maintain referential integrity, normalization and mutual exclusivity.  It also has the same data retrieval drawback as the sub-typing solution.  In fact, this solution is just like the sub-typing solution except that you get to the problem of branching your joining logic "sooner".

Nevertheless, a lot of data modellers would consider this solution inferior to the sub-typing solution because of the way that the mutual exclusivity constraint is enforced.  In the sub-typing solution you use keys to enforce the mutual exclusivity.  In the two foreign key solution you use a CHECK constraint.  I know some people who have an unjustified bias against check constraints.  These people would prefer the solution that keeps the constraints in the keys.

"Denormalized" Partitioning Attribute Solution
There is another option where you keep a single foreign key column on the chequing account table and use another column to tell you how to interpret the foreign key column (RoKa's OwnerTypeID column).  This essentially eliminates the super-type table in the sub-typing solution by denormalizing the partitioning attribute to the child table.  (Note that this is not strictly "denormalization" according to the formal definition, because the partitioning attribute is part of a primary key.)  This solution seems quite simple since it avoids having an extra table to do more or less the same thing and it cuts the number of foreign key columns down to one.  The problem with this solution is that it doesn't avoid the branching of retrieval logic and what's more, it doesn't allow you to maintain declarative referential integrity.  SQL databases don't have the ability to manage a single foreign key column being for one of multiple parent tables.

Shared Primary Key Domain Solution
One way that people sometimes deal with this issue is to use a single pool of IDs so that there is no confusion for any given ID whether it belongs to one sub-type or another.  This would probably work pretty naturally in a banking scenario, since you aren't going to issue the same bank account number to both a corporation and a natural person.  This has the advantage of avoiding the need for a partitioning attribute.  You could do this with or without a super-type table.  Using a super-type table allows you to use declarative constraints to enforce uniqueness.  Otherwise this would have to be enforced procedurally.  This solution is normalized but it won't allow you to maintain declarative referential integrity unless you keep the super-type table.  It still does nothing to avoid complex retrieval logic.

You can see therefore that it isn't really possible to have a clean design that follows all of the rules, while at the same time keeping your data retrieval simple.  You have to decide where your trade-offs are going to be.


SQL Server 2008: use AVG() and an inner join

SELECT Patients.LastName,
AVG(PatientVisit.Pulse) as "avg Pulse",
AVG(PatientVisit.DepressionLevel) as "avg Deplevel"
from Patients
inner join
PatientVisit
on Patients.PatientKey = PatientVisit.PatientKey
GROUP BY Patients.PatientKey, Patients.LastName
order by Patients.LastName;

Important to group by PatientKey as well, so you don't have a single record for Smiths and Nguyens. It also allows extra optimisations, as it (should be) marked as unique (being the Primary Key).


This is a big "It Depends..." but generally speaking you don't want to have statistics updating asynchronously for most workloads because it can mean that you generate a less-than ideal execution plan based on the existing out-of-date statistics.

If you have a scenario where the auto update stats execution takes an excessive amount of time and causes timeouts, you might consider going the route of letting stats update asynchronously so that it doesn't create that problem.  The few seconds it typically takes to auto update statistics can easily result in minutes of difference in execution times from a better execution plan from the new statistics.


Please note while @Kondybas' answer will produce expected results on correct data sets it will NOT restrict the number of iterations.
All rows will still be read but only 2 of them will be returned. That means:

Drop in performance
You will NOT avoid infinite loops that way!

If there is a loop in the tree of pid-id links in the table data due to some mistake such query can last forever (which might event corrupt the entire server by flooding the disk with endless temporary result data - checked in MariaDB 10.3.15.)
So you have to put a restricting condition inside the WITH clause as follows:
WITH RECURSIVE cte AS
  ( SELECT 1 AS lvl -- UDV initialization
         , A.id
         , A.MK
         , A.pid
         , A.AFI -- initial portion
      FROM M AS A
     WHERE A.pid = 1

     UNION

    SELECT cte.lvl + 1 AS lvl -- UDV increment on each iteration
         , z.id
         , z.MK
         , z.pid
         , z.AFI -- recursive portion
      FROM cte -- referring to the already gathered recursion
      JOIN M AS z ON z.pid = cte.id
      WHERE cte.lvl < 2        -- *** the correct condition ***
  )
SELECT *
  FROM cte
;

P.S. I guess it is also a good idea to set max_recursive_iterations to some reachable value like 10000 to prevent MariaDB from getting unresponsive.


Mongodb - Cluster one host keeps syncing

Answer generated from question comments by Stennie:

By "database is still syncing", I assume you are referring to the syncingTo message in the rs.status() output. This just informational and shows which member a secondary is syncing from.

All of your replica set members currently have the same optime so there is no replication lag and this is a normal state. For more detail see my answer to a similar question on Stack Overflow:

MongoDB replica set - always shows “syncingTo”

I would also note that MongoDB 2.6 reached End of Life as of last month (Oct, 2016) which means there will be no further maintenance releases. I would highly recommend upgrading to a supported release, particularly if this is a new deployment. The latest production release series is currently MongoDB 3.2.


in postgres, can one optimize a table across partitions?

the question is whether there is any way to optimize at the global level. for example, would it be possible to create an index on global.usr_id that would span all partitions?

Sadly the answer is "No" - global indexes are not currently supported


DELETE statement conflicted with the REFERENCE constraint

That is the whole point of foreign key constraints: they stop you deleting data that is referred to elsewhere in order to maintain referential integrity.

There are two options:

Delete the rows from INVENTORY_ITEMS first, then the rows from STOCK_ARTICLES.
Use ON DELETE CASCADE for the in the key definition.

1: Deleting In Correct Order

The most efficient way to do this varies depending on the complexity of the query that decides which rows to delete. A general pattern might be:

BEGIN TRANSACTION
SET XACT_ABORT ON
DELETE INVENTORY_ITEMS WHERE STOCK_ARTICLE IN (<select statement that returns stock_article.id for the rows you are about to delete>)
DELETE STOCK_ARTICLES WHERE <the rest of your current delete statement>
COMMIT TRANSACTION

This is fine for simple queries or for deleting a single stock item, but given your delete statement contains a WHERE NOT EXISTS clause nesting that within WHERE IN might produce a very inefficient plan so test with a realistic data set size and rearrange the query if needed.

Also note the transaction statements: you want to make sure both the deletes complete or neither of them do. If the operation is already happening within a transaction you will obviously need to alter this to match your current transaction and error handling process.

2: Use ON DELETE CASCADE

If you add the cascade option to your foreign key then SQL Server will automatically do this for you, removing rows from INVENTORY_ITEMS to satisfy the constraint that nothing should refer to the rows you are deleting. Just add ON DELETE CASCADE to the FK definition like so:

ALTER TABLE <child_table> WITH CHECK
ADD CONSTRAINT <fk_name> FOREIGN KEY(<column(s)>)
REFERENCES <parent_table> (<column(s)>)
ON DELETE CASCADE

An advantage here is that the delete is one atomic statement reducing (though, as usual, not 100% removing) the need to worry about transaction and lock settings. The cascade can even operate over multiple parent/child/grand-child/... levels if there is only one path between parent and all the descendants (search for "multiple cascade paths" for examples of where this might not work).

NOTE: I, and many others, consider cascaded deletes to be dangerous so if you use this option be very careful to properly document it in your database design so you and other developers don't trip over the danger later. I avoid cascading deletes wherever possible for this reason.

A common problem caused with cascaded deletes is when someone updates data by dropping and recreating rows instead of using UPDATE or MERGE. This is often seen where "update the rows that already exist, insert those that don't" (sometimes called an UPSERT operation) is needed and people unaware of the MERGE statement find it easier to do:

DELETE <all rows that match IDs in the new data>
INSERT <all rows from the new data>

than

-- updates
UPDATE target
SET    <col1> = source.<col1>
  ,    <col2> = source.<col2>
       ...
  ,    <colN> = source.<colN>
FROM   <target_table> AS target JOIN <source_table_or_view_or_statement> AS source ON source.ID = target.ID
-- inserts
INSERT  <target_table>
SELECT  *
FROM    <source_table_or_other> AS source
LEFT OUTER JOIN
        <target_table> AS target
        ON target.ID = source.ID
WHERE   target.ID IS NULL

The problem here is that the delete statement will cascade to child rows, and the insert statement won't recreate them, so while updating the parent table you accidentally lose data from the child table(s).

Summary

Yes, you have to delete the child rows first.

There is another option: ON DELETE CASCADE.

But ON DELETE CASCADE can be dangerous, so use with care.

Side note: use MERGE (or UPDATE-and-INSERT where MERGE is not available) when you need an UPSERT operation, not DELETE-then-replace-with-INSERT to avoid falling into traps laid by other people using ON DELETE CASCADE.


How to get the relationship between backup set?

From SQL:

SELECT p.start_time, p.handle, p.tag, s.backup_type,
       s.incremental_level, controlfile_included
FROM   v$backup_piece p, v$backup_set s
WHERE  p.set_stamp = s.set_stamp
AND    p.set_count = s.set_count
ORDER BY tag;

For the backup type:

L: Archived Redo Logs
D: Datafile Full Backup (or control file backup or autobackup)
I: Incremental Backup

You can see which files are in each backup by the TAG column.

Keep in mind that this view may report backups are don't actually exist on disk unless you:

RMAN> crosscheck backup;
RMAN> delete expired backup;

Refer to the Oracle Database 12c Documentation (or whatever version of Oracle you are using) for more information:

v$backup_set

v$backup_piece


Combine WHERE NOT EXISTS with ON DUPLICATE KEY UPDATE

The problem in your query is this part:

ON DUPLICATE KEY UPDATE
    --
    paramvalue = t.extension ;

The alias t is not visible in that part of the query.

However, assuming that the two values, the one to be inserted in the params.extension column (12345) and the one checked against the params_extensions column (WHERE t.extension = '12345')  are the same, then you you can use the VALUES (extension) in the UPDATE part:

Several other improvements can be made:

no need for a derived table (tmp), you can use the internal dual table.
the UPDATE subtype = subtype is redundant
the inetger values (eg. 12345) need not to be passed quoted. They are not string literals, they are integer values.

The statement becomes:

INSERT INTO params
  (extension, type, subtype, paramvalue)
SELECT 1234567, 'TEST', 'TEST2', '24268'
FROM dual
WHERE NOT EXISTS
  ( SELECT 1
    FROM params_exceptions t
    WHERE extension = 1234567
      AND type = 'FRNR1'
      AND subtype = 'FORWARD'
  )
ON DUPLICATE KEY UPDATE
  paramvalue = VALUES (extension) ;


How to Duplicate Column in same table in SQL

Depending on other requirements, you might be able to get away with using a computed column. This assumes you don't need to manipulate the data in the duplicated column.
ALTER TABLE dbo.t
    ADD NewColumn AS OldColumn;


Different queries result in big difference in performance

The use of the pure SQL statement in the second loop makes the database engine compile the whole select 'a'|| v_i::character varying into  v_md5; on each loop iteration before evaluating it whereas the variable assignment is parsed easily into low level instructions once.


Converting SQL Server recovery model simple to full

BOL says about recovery model that

  Recovery models are designed to control transaction log
  maintenance. A recovery model is a database property that controls how
  transactions are logged, whether the transaction log requires (and
  allows) backing up, and what kinds of restore operations are
  available.

Please note that recovery model controls amount of logging in transaction log file and in case of disaster how much data you can recover. It has no relation to amount of inserts or updates or number of column in you table.

You can keep simple recovery model if you do not require point in time recovery. You can take full and differential backup in simple recovery model. If you want to change recovery model to full then make sure you take regular transaction log backups after changing recovery model. Changing recovery model should depends on how much data loss you can afford or agreed between you and client basically the RPO and RTO.


RESTORE VERIFYONLY locks file in Azure Blob Storage

This seems very similar to:

FIX: Cannot remove backup file after you restore the database from Windows Azure in SQL Server 2014 or SQL Server 2012

From that Microsoft Support article:

  Symptoms

  Assume that you use the RESTORE HEADERONLY or RESTORE FILELISTONLY command to restore a database from Windows Azure Blob storage service in Microsoft SQL Server 2014 or SQL Server 2012. After the command is completed, you cannot remove the backup file.

  Cause

  The issue occurs because there is an infinite lease on the backup file. Some race conditions between internal threads cause the lease to be not released in some cases.

This is marked as first fixed in Cumulative Update 4 for SQL Server 2012 SP2. While the fix does not mention RESTORE VERIFYONLY explicitly, you should definitely check this out before reporting it as a bug.

The latest Service Pack for SQL Server 2012 at the time of writing is SP3, which will also include the fix mentioned above.

Finally, just to document something GregGalloway originally left in a comment on the question, you can remove active leases using a script in the following MSDN article:

Deleting Backup Blob Files with Active Leases


Unable to login to SQL Server using SSMS and SQL agent failing

Fix for my this question was to apply

.NET framework 4.6.1
ADO.NET - SqlClient (.NET Framework 3.5/.NET
Framework 2.0 SP2) from this link
https://support.microsoft.com/en-us/kb/3135244.

These fixed the local login and SQL agent service issues. Then the new issue surfaced i.e. unable to SSMS this server from other servers.

To fix this I had to enable ‘KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA’ in registry. After enabling it remote connection started working fine.


MySQL replication: most important config parameters for performance on slave server?

If your concern is about performance and you don't care about losing a small chunck of data(usually 1 second) in case of server crashes, I would change the follow variables:

innodb_buffer_pool_size - try to use 80% of your total ram (in this case 3.2Gb)
innodb_log_file_size - chose a good value in here to optimize the i/o in your slave
innodb_flush_log_at_trx_commit - set it to 2 or 0
innodb_doublewrite - disable innodb doublewrite
innodb_flush_method - change the flush method to O_DIRECT
sync_binlog - if you have binary log enable on slave, disable sync_binlog
make sure you have sync slave variables disabled sync_master_info sync_relay_log and sync_relay_log_info

A good option is to read this article, it's a general overview to how optimize innodb, it will work for your master and also to your slave.

If you are planning to upgrade to the percona server which implement features from MySQL 5.6, I would recommend that you increase the number of slave sql threads slave-parallel-workers


You could consider Availability Groups for your databases. Since you are using them for reporting/read-only already then you already have licenses for your secondary instances.

This will allow better access to your secondaries as it does not need to take the database offline for updates - your data will also be more up to date as it is a constant flow of transaction log updates rather than batched backups.


Algebra of joins, is there ever a need to repeat a table in a series of joins?

Dependent on context (chain) addressing to the same table can mean different sets of rows. So you have to point them by two different aliases to avoid ambiguity.

Let's imagine we want to build the list of customers and their suppliers both with cities where they reside:

SELECT w.name AS customer,
       s.city AS c_city,
       z.name AS supplier,
       r.city AS s_city
  FROM customers      AS w
  LEFT JOIN suppliers AS z ON z.id = w.supplier_id
  LEFT JOIN cities    AS s ON s.id = w.city_id
  LEFT JOIN cities    AS r ON r.id = z.city_id
ORDER BY w.name ASC;

Here same table is joined twice because each join performed on it's own condition and produce different resulting city names - one for customer and one for supplier. And there is no way to achieve the desired result without joining the same table twice.

P.S.

Let's imagine we have the list of cities visited by tourist:

-------------------------------------
| ID | DATE | CITY_ID | PREVIOUS_ID |
-------------------------------------

Here PREVIOUS_ID is the reference to the row in the same table and it mean " the city  tourist come from". And now we want to build the route containing last four visited cities to the some point:

SELECT *
   FROM visited      AS w
   JOIN cities       AS z ON z.id = w.city_id
   LEFT JOIN visited AS r ON r.id = w.previous_id
   LEFT JOIN cities  AS s ON s.id = r.city_id
   LEFT JOIN visited AS q ON q.id = r.previous_id
   LEFT JOIN cities  AS d ON d.id = q.city_id
   LEFT JOIN visited AS x ON x.id = q.previous_id
   LEFT JOIN cities  AS f ON f.id = x.city_id
 WHERE w.id = 12345; -- last position

As you can see we can easely join again and again the same tables.


fulltext_stoplists to database_principals issue

The sys.fulltext_stoplists doc says the column principal_id is the ID of the database principal that owns the stoplist. You can specify it as one of the Arguments of the CREATE FULLTEXT STOPLIST command:

AUTHORIZATION owner_name Specifies the name of a database principal to
own of the stoplist. owner_name must either be the name of a principal
of which the current user is a member, or the current user must have
IMPERSONATE permission on owner_name. If not specified, ownership is
given to the current user.

It appears the owner of the object no longer exists. You can assign a new owner using the ALTER AUTHORIZATION like this:
ALTER AUTHORIZATION ON FULLTEXT STOPLIST::CustomStopword TO SomeExistingUser;

After that, run the DBCC check again to see if everything is ok.


Both replication masters stop by themselves

The provided error Error 'Duplicate entry '411465' for key 1' on query means that the slave read and attempted to execute a binary log event to insert a row that already existed, ie. the same value 411465 for your primary key.

The most likely cause of this is the the insert was executed on the slave. To diagnose the query, you would use mysqlbinlog and use the binary log coordinates from SHOW SLAVE STATUS. This will give you the server-id that the query originated from, which will match either your main master or 'passive' master.

Once you determine the query, you can identify the row on the server that is throwing the slave error to determine next steps. You can choose to :

skip the entry using SET GLOBAL sql_slave_skip_counter=1 to proceed to the next binary log statement
Delete the specific row on the server and start slave to have the statement run from replication.

However, you need to take steps to understand how the mismatch occurred, or you're going to run into this again. This will require some more detective work from your end using mysqlbinlog.

If, as you say, only one master is writeable at a time, you should ensure the following:

passive master is read_only=1 and your failover solution is able to modify read_only.
the user that your application runs as (or any other non-trusted user) does not have the SUPER privilege. Any user with SUPER privilege can execute statements even on read_only=1 servers.
setup pt-table-checksum to ensure the data is in sync on both servers.


Retrieving row count without using Count function

If you want to get the exact count of rows in an efficient manner, then COUNT(*) is it. The ANSI standard (look for "Scalar expressions   125") states that COUNT(*) give the row count of a table: it is intended to be optimised from the start.

  If COUNT(*) is specified, then the result is the cardinality of T.

A ROW_NUMBER() function isn't a practical option: it isn't a counting function (it's  "ROW_NUMBER") and it will run badly as you add rows: a few 1000 will show how bad this it
SUM(1) may be optimised to COUNT(*) internally but I'd never use it
@@ROWCOUNT will require all rows to be returned by the first SELECT, which is a huge unnecessary overhead.

If you can live with approximate for SQL Server then use sys.dm_db_partition_stats. Marian's answer is out of date now since SQL Server 2005 added dmvs

SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'Mytable' AND (index_id < 2)

See this on SO too for some more info: https://stackoverflow.com/questions/6069237/fastest-way-to-count-exact-number-of-rows-in-a-very-large-table/6069288#6069288

In summary, there is exactly one useful way of getting the number of rows in a table. COUNT(*)


My solution does only 1 scan of #TempBucket but it adds an additional sort, I'm not sure it's more efficient, you should test it on volumes of data:

;with cte as
(
select t.*, tb.id as BucketId,
       row_number() over(partition by t.id order by tb.FromVal) as rn
from #TempData t
     join #TempBucket tb
         on (t.value = tb.filter)
         or (tb.Fromval < t.value AND t.value <= tb.ToVal)
)
select count(id) as cnt, BucketId
from cte
where rn = 1
group by BucketId;

To give a priority to the row in priority bucket it sorts by tb.FromVal which is null in case of priority bucket, alternatively you can sort on tb.Filter desc, I just don't know the exact criteria of priority bucket whether the FromVal,ToVal are null or Filter = 0


Question about 'user process' in the context of Oracle

an user process is a piece of software
  that can connect to an oracle server.
  You (the user) can start a piece of
  this kind of software, then connect to
  oracle.

Not quite. A User Process in Oracle is different than a client, which is what I think you are referring to. The user starts a client program (SQL*Plus, Pro*C, etc.), which runs on the client system. This program contacts the Oracle database server, typically either via TNS or IPC. It is the Oracle database that spawns the User Process. It's a bit confusing terminology in that a User Process from the database perspective is really just another process that Oracle starts, but it is specific to a user's connection to the database - it is a server process. It is outside of the user's direct control.


Storing multi-select data with single-select data

Specific answers
First of all, here is my personal approach to your specific questions:

Do I store the multi-select data in a single field that is comma delimited?

No, I do not recommend you to proceed that way. Since you have the chance to make use of a relational database management system (RDBMS), I personally consider that it would be highly convenient and reasonable to design and implement your database in a relational fashion. I am convinced that there is no need to use a format as limited as CSV when one can take advantage of the robustness of relational tools. If you opt for the CSV method, you would be adding unnecessary complexity to your project.

Pros. CSV format has its use in certain contexts, e. g., supplying information interchange between applications that handle data in incompatible formats. Another feature of CSV is that it is easily modified by spreadsheet aplications such as MS Excel or LibreOffice Calc. Anyway, due to the fact that you are going to use a RDBMS, I do not think any of these facts is relevant in terms of database design or storage in a relational platform. If you come across the need to deliver some of your data in CSV format in the future, then you can export your structured data to a comma delimited file without undermining your database soundness.

Cons. As you may already know, by storing CSV data in a single column, you would be breaking First Normal Form. This way, having unnormalized data (nonsimple or multivalued columns), you lose many of the essential technical advantages that are provided by RDBMS which are firmly grounded in relational theory. Since many people have rightly enumerated the abundant problems that arise by storing data in CSV format in an RDBMS, I will only mention that you would be putting at risk the data integrity (which is widely considered one of the most important properties of a database), the performance of your system would be substantially diminished and you will end up re-implementing in your own application many of the capabilities that RDBMS offer natively, which have been vastly optimized over the years.

Do I store it in a separate table with a UserID?

Yes, to me that is the best approach and can be easily implemented in a relational system by means of normalization.
As I understand your specifications, MatchGender represents the gender of potential partners with which a given User is interested in getting in touch. So, the assertions a User seeks one-to-many MatchGenders and a MatchGender is sought by one-to-many Users are business rules of particular importance in this specific context.
If the above is correct, then there is a many-to-many relationship between User and MatchGender, and this kind of scenarios are very common in relational database development. I will model this aspect and offer an implementation example below, in my suggestion.

Pros. You can expect predictable results since your database will be built on relational principles. You can take full advantage of RDBMS capabilities, such as aggregate functions, JOINs, referential integrity, etc. You can prevent data redundancy and full table scans. Your database structure will be more flexible for future changes or extensions. You can deliver database performance that largely meets the needs of your clients. Your data retrieval will be straightforward.

Cons. At first glance, the relational approach may seem a bit cumbersome, since it requires the database designers and programmers to be more disciplined but, as the project grows, that discipline will pay off.

Other relevant aspects of your scenario
I want to describe some other points about my understanding of your situation. This way, I think that the statements a User pursues one-to-many Hobbies and a Hobby is pursued by one-to-many Users are important business rules as well. So, there is also a many-to-many relationship between the entities User and Hobby, and this also has to be modelled and implemented.
I assume that you will have a set of Hobbies that will be inserted into the database by some sort of system administrator. If so, a HobbyCode that uniquely identifies a given occurence of a Hobby may be defined and you can set it as the meaningful PRIMARY KEY of such table.
Modeling and implementation suggestion
Therefore, to deal with your situation, I propose the following IDEF1X data model:

And then, the next DDL can be derived and physically implemented with ease from such data model:
CREATE TABLE Gender
(
    GenderCode      CHAR(1)  NOT NULL,
    Name            CHAR(11) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Gender PRIMARY KEY CLUSTERED
    (
        GenderCode ASC
    ),
    CONSTRAINT UIX_Gender_Name UNIQUE
    (
        Name
    )
)

CREATE TABLE Hobby
(
    HobbyCode       CHAR(2)  NOT NULL,
    Name            CHAR(30) NOT NULL,
    Descriptionn    CHAR(90) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_Hobby PRIMARY KEY CLUSTERED
    (
        HobbyCode ASC
    ),
    CONSTRAINT UIX_Hobby_Name UNIQUE
    (
        Name
    )
)

CREATE TABLE UserProfile
(
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATETIME NOT NULL,
    GenderCode      CHAR(1)  NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserProfile PRIMARY KEY CLUSTERED
    (
        UserId ASC
    ),
    CONSTRAINT UIX_UserProfile_FirstName_LastName_and_BirthDate UNIQUE
    (
        FirstName ASC,
        LastName  ASC,
        BirthDate ASC
    ),
    CONSTRAINT FK_UserProfile_Gender FOREIGN KEY
    (
        GenderCode
    )
    REFERENCES Gender (GenderCode)
)

CREATE TABLE UserMatchGender
(
    UserId          INT      NOT NULL,
    GenderCode      CHAR(1)  NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserMatchGender PRIMARY KEY CLUSTERED
    (
        UserId     ASC,
        GenderCode ASC
    ),
    CONSTRAINT FK_UserMatchGender_UserProfile FOREIGN KEY
    (
        UserId
    )
    REFERENCES UserProfile (UserId),
    CONSTRAINT FK_UserMatchGender_Gender FOREIGN KEY
    (
        GenderCode
    )
    REFERENCES Gender (GenderCode)
)

CREATE TABLE UserHobby
(
    UserId          INT      NOT NULL,
    HobbyCode       CHAR(2)  NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    CONSTRAINT PK_UserHobby PRIMARY KEY CLUSTERED
    (
        UserId    ASC,
        HobbyCode ASC
    ),
    CONSTRAINT FK_UserHobby_UserProfile FOREIGN KEY
    (
        UserId
    )
    REFERENCES UserProfile (UserId),
    CONSTRAINT FK_UserHobby_Hobby FOREIGN KEY
    (
        HobbyCode
    )
    REFERENCES Hobby (HobbyCode)
)


If you don't want event at 12:10:01, then answer is very simple - use LEAD or LAG to compare timestamps of the two consecutive rows. Just be aware that if you have a long sequence of events in which each pair of events is less than 10 minutes apart, then the query will return only the first event of this sequence.

In other words, there has to be gap of more than 10 minutes between two events to add a row to the result set.

Sample data

DECLARE @T TABLE(EventID int, RecordTime datetime2(0));
INSERT INTO @T (EventID, RecordTime) VALUES
( 1, '2017-04-01 12:00:00'),
( 2, '2017-04-01 12:00:10'),
( 3, '2017-04-01 12:00:20'),
( 4, '2017-04-01 12:00:32'),
( 5, '2017-04-01 12:05:42'),
( 6, '2017-04-01 12:09:00'),
( 7, '2017-04-01 12:24:12'),
( 8, '2017-04-01 12:36:46'),
( 9, '2017-04-01 12:36:57'),
(10, '2017-04-01 15:00:00');

Query

WITH
CTE
AS
(
    SELECT
        EventID
        ,RecordTime
        ,LAG(RecordTime) OVER (ORDER BY EventID) AS PrevRecordTime
    FROM @T
)
SELECT
    EventID
    ,RecordTime
FROM
    CTE
    CROSS APPLY
    (
        SELECT DATEDIFF(second, PrevRecordTime, RecordTime) AS RecordDiffSeconds
    ) AS CA
WHERE
    RecordDiffSeconds IS NULL
    OR RecordDiffSeconds > 600
ORDER BY EventID;

Result

+---------+---------------------+
| EventID |     RecordTime      |
+---------+---------------------+
|       1 | 2017-04-01 12:00:00 |
|       7 | 2017-04-01 12:24:12 |
|       8 | 2017-04-01 12:36:46 |
|      10 | 2017-04-01 15:00:00 |
+---------+---------------------+


what is the difference between mysql community server and mysql cluster

MySQL cluster is a storage engine for MySQL, which distributes and replicates data in an in-memory cluster (NDB data nodes) and can be queried via MySQL servers (SQL nodes).

Where it gets confusing, is that MySQL Cluster also has a native API where you can avoid the MySQL storage engine system and communicate directly to the data nodes with the NDB API, which is kind of NoSQL-like.  I'm not aware of any other storage engines that have this ability, and there are many large customers preferring this method of usage.

MySQL Cluster also has it's own release cycle and versioning system, and is currently at version 7.3.


SQL - Add column if match else, on aggregate data

You can add the number of gifts with either:

SELECT ...,
    COUNT(CASE WHEN GiftOrder='gift' THEN 1 END) AS number_of_gifts

or the similar (works in MySQL only):

SELECT ...,
    SUM(GiftOrder='gift') AS number_of_gifts

If you want a simple (yes/no) as in your question, if the customer has ever done one or more gifts vs. none at all, then:

SELECT ...,
    CASE WHEN COUNT(CASE WHEN GiftOrder='gift' THEN 1 END) > 0
        THEN 'Gift'
        ELSE 'Not Gift'
    END AS gifts


This answer applies to all versions of MySQL up to, but maybe not including 8.0.

FLUSH PRIVILEGES syncs the entire set of privileges.  No way to flush just one user's.
No way.
Run the equivalent opposite action -- REVOKE <-> GRANT, etc.

A full dump of certain tables in mysql:  user, db and a few others.  This is the 'source of truth' for the current state of all the GRANTs.  Editing the tables with UPDATE (etc) SQL statements has the equivalent effect to GRANT/REVOKE/FLUSH.


Connecting to SQL Server 2008 on windows server 2008

You will be using the servername (DNS) or the IP address of the server.  But if you have a named instance (as opposed to the default instance), your servername will be followed by \instanceName.

So, in other words unless you changed any SQL Server Express install defaults, you should connect to the server like this:

yourServerName\sqlexpress

SQL Server Express has a default named instance name of SQLEXPRESS.  Also, since SQL Server Express is a named instance, you'll need to ensure that your SQL Server Browser service is running.

Another one.  If you are trying to connect to SQL Server from a separate machine, you'll need to ensure that the TCP/IP protocol is enabled for SQL Server.

The above two points can be verified and configured in SQL Server Configuration Manager.


Is an Oracle SID UPPERCASE or lowercase?

Case sensitive on Linux/UNIX and not case sensitive on Windows.


You can do that with the help of the additional module dblink. Install once per database (the one where the trigger is executed):

CREATE EXTENSION dblink;

You need superuser privileges (in the database cluster where the trigger runs) for some of the involved functionality.

Since you want to write to the external database, you will probably want to use dblink_exec(). The manual has code examples.

Or try a search here for more dblink examples.

Note that you cannot ROLLBACK what you have written with dblink to an external database. A local exception after the external write does not undo what's written in the foreign database.


How to insert several csv files into Elasticsearch?

I just had a look at the data in the Higher Education Statistics Agency (HESA) zipped file and the files are all different.

This means you will either have to create an individual .config file for each import or create a single .config file using conditions as described in the following article:

Reference: How to use multiple csv files in logstash (Elastic Discuss Forum)

Expanding on your first .config by one level:

input{
    file{
        path =>"Users/mike/Data/ACCREDITATION.csv"
        start_position => "begining"
        sincedb_path => "/dev/null"
    }
    file{
        path =>"Users/mike/Data/ACCREDITATION.csv"
        start_position => "begining"
        sincedb_path => "/dev/null"
    }

}

filter{
    # added condition for first file
    if [path] == "Users/mike/Data/ACCREDITATION.csv"{
        csv{
            separator => ","
            columns => ['PUBUKPRN', 'UKPRN', 'KISCOURSEID', 'KISMODE', 'ACCTYPE', 'ACCDEPEND', 'ACCDEPENDURL', 'ACCDEPENDURLW']

        }
        mutate{convert => ["PUBUKPRN","integer"]}
        mutate{convert => ["UKPRN","integer"]}
        mutate{convert => ["KISMODE","integer"]}
        mutate{convert => ["ACCTYPE","integer"]}
        mutate{convert => ["ACCDEPEND","integer"]}
    }
    # added condition for second file
    else if [path] == "Users/mike/Data/AccreditationByHep.csv"{
        csv{
            separator => ","
            columns => ['AccreditingBodyName', 'AccreditionType', 'HEP', 'KisCourseTitle', 'KiscourseID']
        }
    # ommitted mutations for second file
    }

}

output{
    # added condition for first file
    if [path] == "Users/mike/Data/ACCREDITATION.csv"{
        elasticsearch{
            hosts =>"localhost"
            index =>"accreditation"
            document_type =>"accreditaiton keys"
        }
    }
    # added condition for second file
    else if [path] == "Users/mike/Data/AccreditationByHep.csv"{
        elasticsearch{
            hosts =>"localhost"
            index =>"accreditationByHep"
            document_type =>"accreditaitonbyhep keys"
        }
    }
    stdout{}
}

document_type is a deprecated configuration option

You should be able to expand on this example on your own.


SSRS Express will only connect to its own SQL Server instance as a data source. You can’t use it in the way you’re describing it. Sorry.


deleted and seen get in the way of optimizations
GROUP BY GREATEST(m.to_id, m.from_id), LEAST(m.to_id, m.from_id) seems strange.  Wouldn't this suffice:  GROUP BY m.to_id, m.from_id?
Since the user is either from_id or to_id, half the info is already known?
What is m.id < 999999999 for?
IN ( SELECT ... ) often optimizes poorly.
You say "pagination", yet I don't see the inefficient OFFSET not the better "remember where I left off".
Use realistic limits, not blindly VARCHAR(255).
Each table has 3 timestamps.  You will never use some of them.
Don't use SELECT *; instead, spell out the columns that you really need.
You will need more indexes, but let's wait to see what more of the SELECTs look like.
LEFT JOIN account assumes that the desired account might be missing.  If so, there a data integrity problem.  So switch to JOIN; this may aid in optimizations.
LIMIT 100 -- Isn't that too much to put on a UI page?
AND (message.to_id = 1 AND message.from_id = 2) OR (message.to_id = 2 AND message.from_id = 1) is hard to optimize.  Use UNION of two SELECTs instead of OR, and add (tentatively) INDEX(from_id, to_id).  The reason is that the Optimizer has almost no techniques for handling OR; it simply ignores indexes and falls back on a table scan.

This might work:

AND (message.to_id, message.from_id) IN ((1, 2), (2, 1))

Here's the Changelog entry that hints at such:

----- 2013-12-03 5.7.3 Milestone 13 -- Functionality Added or Changed -- -----

The optimizer now is able to apply the range scan access method to queries of this form:

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

Previously, for range scans to be used it was necessary for the query to be written as:

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
                      OR ( col_1 = 'c' AND col_2 = 'd' );

For the optimizer to use a range scan, queries must satisfy these conditions:

Only IN predicates can be used, not NOT IN.

There may only be column references in the row constructor on the IN predicate's left hand side.

There must be more than one row constructor on the IN predicate's right hand side.

Row constructors on the IN predicate's right hand side must contain only runtime constants, which are either literals or local column references that are bound to constants during execution.

EXPLAIN output for applicable queries will change from full table or index scan to range scan. Changes are also visible by checking the values of the Handler_read_first, Handler_read_key, and Handler_read_next status variables.


How to get the parameter values of a stored procedure that's in the middle of execution?

For SQL Server 2016 you need to have the query profiling infrastructure enabled in advance with trace flag 7412 or an extended events session capturing query_thread_profile (and be on at least  SQL Server 2016 SP1) but then can use

WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select query_plan.query('//ParameterList')
from sys.dm_exec_query_statistics_xml(@session_id)

and see the ParameterRuntimeValue and ParameterCompiledValue from the execution plan of the statement being executed.

For SQL Server 2019+ the infrastructure is enabled by default so the above will just work.

This might not be all the parameters of the stored proc however as it will only include params used by the execution plan.


If you have a really large number of fields in the SELECT and/or the fields are wide, you should make sure the JOIN fields are your clustered indexes as well.

Pulling a few dozen fields via a key lookup can be the most expensive part of a query like this.  Clustering on your JOIN keys forgoes that step.

The only real way to know is to run the query, check the execution plans, and see where your bottlenecks are.  For the WHERE clause you could make a filtered index that just includes the cluster key of that table.


How to keep history of MySQL/Aurora stored procedure revisions?

Here's a good StackOverflow post with various answers that might help you: Put MySQL database under version control?
Specifically, Liquibase seems to withstood the test of time, and it seems to be supported by AWS (seeing as you mentioned Aurora): Deploy, track, and roll back RDS database code changes using open source tools Liquibase and Jenkins
(Note this leverages the Amazon service CodeCommit.)


Trick to remember which instance I am working with

You can colour code the status bar in SQL Server Management Studio
Easiest if you use the Registered Servers or Central Management Servers feature and then allocate colours at a group level i.e. Dev (Green) / Test (Yellow) / Prod (Red)
https://documentation.red-gate.com/sp/ssms-tab-management/working-with-ssms-tab-management-quick-ref
btw You may need to spell it color to get a match


Determining maximum table size, type of engine and type of indexes supported

SQL Server has B-Tree indexes only. Other types are hardly used day to day.
All maximum capacity specs for 2008 are here: there is a link to SQL Server 2005. Note SQL Server does not have "limits" apart of disk space for size of the DB or a table.
Express differs in other ways: see editions
Like Sybase, Oracle, Postgres, SQL Server is a single engine. As an approximation, InnoDB is MySQL's closest in concept

Note: if you come from MySQL, do not make assumptions about SQL Server. It (like other RDBMS) are more complex and mature then MySQL in many respects


How to encode a known date but unknown year?

The only thing that I can think of is to split the date into day/month and year - having one or the other as NULL as necessary. This is one of the only times I consider a split year/month/day structure, with separate fields, to be OK.


Here is the query you need:
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
(select
    @inc      := IF(@groupon=0,1,0)           inc,
    @groupnum := @groupnum + @inc             grpn,
    @s_tag    := IF(diary='start',1,0)        stag,
    @e_tag    := IF(diary='end',  1,0)        etag,
    @groupon  := IF(diary='start',1,@groupon) g1,
    @groupon  := IF(diary='end',  0,@groupon) g2,
    (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    id,diary from diary
) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;

First, here is your sample data
mysql> use all_db
Database changed
mysql> drop table if exists diary;
Query OK, 0 rows affected (0.03 sec)

mysql> create table diary
    -> (
    ->     id int not null auto_increment,
    ->     diary varchar(25),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into diary (diary) values ('breakfast'),('walk'),('start'),
    -> ('office works'),('office projects'),('end'),('taxi'),('start'),
    -> ('preparing for meeting'),('doing the meeting'),('end'),('night');
Query OK, 12 rows affected (0.06 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> select * from diary;
+----+-----------------------+
| id | diary                 |
+----+-----------------------+
|  1 | breakfast             |
|  2 | walk                  |
|  3 | start                 |
|  4 | office works          |
|  5 | office projects       |
|  6 | end                   |
|  7 | taxi                  |
|  8 | start                 |
|  9 | preparing for meeting |
| 10 | doing the meeting     |
| 11 | end                   |
| 12 | night                 |
+----+-----------------------+
12 rows in set (0.00 sec)

mysql>

Using iterative variable manipulation
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select * from
(select
    @inc      := IF(@groupon=0,1,0)           inc,
    @groupnum := @groupnum + @inc             grpn,
    @s_tag    := IF(diary='start',1,0)        stag,
    @e_tag    := IF(diary='end',  1,0)        etag,
    @groupon  := IF(diary='start',1,@groupon) g1,
    @groupon  := IF(diary='end',  0,@groupon) g2,
    (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    id,diary from diary
) A;

Here is what it generates:
mysql> set @groupnum = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @groupon = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @inc = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from
    -> (select
    ->     @inc      := IF(@groupon=0,1,0)           inc,
    ->     @groupnum := @groupnum + @inc             grpn,
    ->     @s_tag    := IF(diary='start',1,0)        stag,
    ->     @e_tag    := IF(diary='end',  1,0)        etag,
    ->     @groupon  := IF(diary='start',1,@groupon) g1,
    ->     @groupon  := IF(diary='end',  0,@groupon) g2,
    ->     (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    ->     id,diary from diary
    -> ) A;
+-----+------+------+------+------+------+---------+----+-----------------------+
| inc | grpn | stag | etag | g1   | g2   | ord     | id | diary                 |
+-----+------+------+------+------+------+---------+----+-----------------------+
|   1 |    1 |    0 |    0 |    0 |    0 |       0 |  1 | breakfast             |
|   1 |    2 |    0 |    0 |    0 |    0 |       0 |  2 | walk                  |
|   1 |    3 |    1 |    0 |    1 |    1 |       1 |  3 | start                 |
|   0 |    3 |    0 |    0 |    1 |    1 |       0 |  4 | office works          |
|   0 |    3 |    0 |    0 |    1 |    1 |       0 |  5 | office projects       |
|   0 |    3 |    0 |    1 |    1 |    0 | 1000000 |  6 | end                   |
|   1 |    4 |    0 |    0 |    0 |    0 |       0 |  7 | taxi                  |
|   1 |    5 |    1 |    0 |    1 |    1 |       1 |  8 | start                 |
|   0 |    5 |    0 |    0 |    1 |    1 |       0 |  9 | preparing for meeting |
|   0 |    5 |    0 |    0 |    1 |    1 |       0 | 10 | doing the meeting     |
|   0 |    5 |    0 |    1 |    1 |    0 | 1000000 | 11 | end                   |
|   1 |    6 |    0 |    0 |    0 |    0 |       0 | 12 | night                 |
+-----+------+------+------+------+------+---------+----+-----------------------+
12 rows in set (0.00 sec)

mysql>

With a few refinements

ignoring ord=1 and ord=1000000
GROUP BY on grpn

Here is the final query first mentioned
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
(select
    @inc      := IF(@groupon=0,1,0)           inc,
    @groupnum := @groupnum + @inc             grpn,
    @s_tag    := IF(diary='start',1,0)        stag,
    @e_tag    := IF(diary='end',  1,0)        etag,
    @groupon  := IF(diary='start',1,@groupon) g1,
    @groupon  := IF(diary='end',  0,@groupon) g2,
    (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    id,diary from diary
) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;

Here it is executed:
mysql> set @groupnum = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @groupon = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set @inc = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
    -> (select
    ->     @inc      := IF(@groupon=0,1,0)           inc,
    ->     @groupnum := @groupnum + @inc             grpn,
    ->     @s_tag    := IF(diary='start',1,0)        stag,
    ->     @e_tag    := IF(diary='end',  1,0)        etag,
    ->     @groupon  := IF(diary='start',1,@groupon) g1,
    ->     @groupon  := IF(diary='end',  0,@groupon) g2,
    ->     (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    ->     id,diary from diary
    -> ) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;
+------+-----------------------------------------+
| grpn | itinerary_step                          |
+------+-----------------------------------------+
|    1 | breakfast                               |
|    2 | walk                                    |
|    3 | office works,office projects            |
|    4 | taxi                                    |
|    5 | preparing for meeting,doing the meeting |
|    6 | night                                   |
+------+-----------------------------------------+
6 rows in set (0.00 sec)

mysql>

Give it a Try !!!
CAVEAT
I tried out @a1ex07's query against my sample data
mysql> select d.id1,
    -> CASE
    -> WHEN COUNT(*) >1 THEN 'concatenated'
    -> ELSE NULL
    -> END AS `type`,
    -> GROUP_CONCAT(
    -> case
    -> when diary = 'start' or diary = 'end' then null
    -> else diary
    -> end
    -> order by id
    -> ) as diary
    -> FROM
    -> (
    -> SELECT x.id, x.diary,
    -> CASE WHEN start_id IS NULL OR end_id IS NULL OR start_id > end_id THEN id
    -> ELSE start_id
    -> END as id1,
    -> end_id
    -> FROM
    -> (
    ->     select a.*,
    ->     (select max(id) from diary b where b.diary ='start' and b.id <=a.id) as start_id,
    ->     (select min(id) from diary b where b.diary ='end' and b.id >=a.id) as end_id
    ->
    ->      from diary a
    ->  )x
    -> )d
    -> group by id1,end_id;
+------+--------------+-----------------------------------------+
| id1  | type         | diary                                   |
+------+--------------+-----------------------------------------+
|    1 | NULL         | breakfast                               |
|    2 | NULL         | walk                                    |
|    3 | concatenated | office works,office projects            |
|    3 | NULL         | taxi                                    |
|    8 | concatenated | preparing for meeting,doing the meeting |
|   12 | NULL         | night                                   |
+------+--------------+-----------------------------------------+
6 rows in set (0.00 sec)

mysql>

By George, it works !!! He get's +1 for getting his answer in first.