MySQL not responding, Writing to net status

Your problem is quite straightforward.

You mentioned that the table is using the MyISAM storage engine.
The processlist shows

Process ID 3629079 is running SELECT * FROM web.files order by file_name_eng desc
SELECT has been running 2324 seconds (39 min 44 sec)
Process ID 3649490 is running UPDATE query
SELECT has blocking the UPDATE for 1967 sec (32 min 47 sec)

NOTE : Any INSERT, UPDATE, DELETE on a MyISAM table requires a full table lock each time.

NOTE : If a series of 50 UPDATEs is issued on one table, each UPDATE requires a full table lock.

SUGGESTION #1

If web.files has no FULLTEXT indexes, you should convert web.files to InnoDB with

ALTER TABLE web.files ENGINE=InnoDB;

I suggested this because InnoDB will allow UPDATEs, DELETEs, and INSERTs to harmoniously coexist with SELECTs via MVCC and Transaction Isolation.

If you are not the Project Manager/Lead Developer, please consult with your Project Manager/Lead Developer before doing this.

SUGGESTION #2

SELECT * FROM web.files order by file_name_eng desc

From the EXPLAIN plan, I can see that the query

Query must do a full table scan because there is no WHERE clause
There is no index on file_name_eng column, so there is no assistance in order the table (a filesort would happen, but happen fast)

Therefore, you should run

ALTER TABLE web.files ADD INDEX (file_name_eng);

Rhetorial Questions

Do you need SELECT * ?
Could you list only the columns you really need ?


IBM DB2 Data Studio Web Console Installer

It's part of the full Data Studio installation image. However, Web Console is deprecated. Use Data Server Manager instead.


Get dates of the MSSQL Database Transaction Log records

Your question is not very detailed in exactly what you want, but How to read and interpret the SQL Server log has some very good information about interpreting the SQL Server log.

Specifically relating to finding the start of a transaction (from the above post):

  The first think I will do will be to look at the LOP_BEGIN_XACT
  operations. This log record marks the beginning of a transaction and
  is very important in log analysis because is the only log record that
  contains the date and time when the transaction started, and also
  contains the SID of the user that had issued the statement. Here is
  how I query for these operations:

select [Current LSN], [Operation], [Transaction ID], [Parent Transaction ID],
    [Begin Time], [Transaction Name], [Transaction SID]
from fn_dblog(null, null)
where [Operation] = 'LOP_BEGIN_XACT'


It's clear that the issue is related to stdin. If you have control over the dump process, a possible solution is to do not execute the .sql file from psql. To do so, you simply have to run the following two queries:

Execute this from the original database: COPY (SELECT * FROM mytable WHERE ...) TO '/storage/mytable.dump'
Execute this from the destination database (the one where you have the out of memory issue): CREATE TABLE mynewtable ( ... ); COPY mynewtable FROM '/storage/mytable.dump';.

You can mess around with the various parameters available in COPY. For example, you can export a gzipped file or a CSV with headers and custom separator: https://www.postgresql.org/docs/current/static/sql-copy.html .

You can also issue a pg_dumpall > /storage/db.out to export the entire original database and run psql -f /storage/db.out postgres to recreate it on the new database: https://www.postgresql.org/docs/current/static/app-pg-dumpall.html . This should work.. I've just dumped an output file of several tens of GB on OS X (linux-like) without any problem.

Se also pg_dump for more selective database dump options: https://www.postgresql.org/docs/current/static/app-pgdump.html


MongoDB "select x from y where id in" another table

You should use $lookup for subquery.
Check out the document.
There is an example from the document.
db.collection1.aggregate([
{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}


Property DefaultLanguageName is not available Error on saving db settings

For a contained database, set the default language with ALTER DATABASE. For example:

ALTER DATABASE [TMS-API555] SET DEFAULT_LANGUAGE = english;


update all rows after insertion in mysql

Updating all rows in a table is seriously non-scalable.

Consider tossing active and updated_at.  Change created_at to be DEFAULT CURRENT_TIMESTAMP (or something like that).  And have INDEX(created_at).  Then,

SELECT ...
    ORDER BY created_at DESC  LIMIT 1

Will get the "latest" row (which seems to be your "active").

If there is a risk of inserting two rows in the same second, there are workarounds.


How to SELECT most common (viral) from a table and LIMIT results for pagination?

ORDER BY likes DESC should be used to get the highest likes first.

The second page is going to have LIMIT 10,10.

With a likes table as it is, its going to be hard to get quick performance out of this. Getting the number of likes into the main articles table would help get a quicker results of this if they are used often. If you need to use both perhaps an INSERT trigger on the likes table.

Once you have a likes column in articles, it will need an index on it.

Then your query becomes simply:

SELECT *
FROM articles
ORDER BY likes DESC
LIMIT 10

The second page way is:

SELECT *
FROM articles
ORDER BY likes DESC
LIMIT 10,10

If you got too many pages, you'll eventually start to be slow down as getting 10 pages at an offset needs to walk down to that offset first.


How do I find out *why* a user has certain effective permissions?

You can find some good information regarding security from the article below.

Reviewing SQL Server Permissions | TechRepublic http://tek.io/KfzEyp

Except:

  The following query uses the sys.database_permissions system view to
  indicate which users had specific permissions inside the current
  database.

SELECT
        dp.class_desc
       ,dp.permission_name
       ,dp.state_desc
       ,ObjectName = OBJECT_NAME(major_id)
       ,GranteeName = grantee.name
       ,GrantorName = grantor.name
    FROM
        sys.database_permissions dp
        JOIN sys.database_principals grantee
        ON dp.grantee_principal_id = grantee.principal_id
        JOIN sys.database_principals grantor
        ON dp.grantor_principal_id = grantor.principal_id


How to create a view that refresh automatically

Use WITH SCHEMABINDING in the view

CREATE VIEW ExampleDBaseII
WITH SCHEMABINDING
AS
SELECT T.ID, Cast(T.Name AS Varchar) as Name, Cast(T.City AS Varchar) as City,
FROM Team T
GO

This will disallow any changes to the underling tables that could affect the view

It also requires the use of qualifiers (schema, alias) and disallows the use of SELECT *. Which is a good thing (SO link)


Delete rule on view does not execute every statement

Quote from the manual

  Caution

  In many cases, tasks that could be performed by rules on INSERT/UPDATE/DELETE are better done with triggers. Triggers are notationally a bit more complicated, but their semantics are much simpler to understand.

(Emphasis mine)

I think this is one of the many cases where this is better done with a trigger:

create or replace function delete_reach()
  returns trigger
as
$$
begin
  DELETE FROM reach
  WHERE reach.id = old.id;

  DELETE FROM rp
  WHERE rp.id = old.rp;
  return old;
end;
$$
language plpgsql;

create trigger delete_reach_trigger
   instead of delete
   on vw_reach
   for each row
   execute procedure delete_reach();

Online example: https://rextester.com/KMEDTV61929


Postgres (not Postgre) is absolutely free. You can do anything you want with it.

For MySQL you will most probably need a commercial license if you plan to redistribute your application with a bundled database. Not sure if this is required if your application just needs MySQL but I think this also requires a commercial licenses because it is considered a derivative work

But I'm not a lawyer. So if you want to be 100% sure, talk to Oracle and a lawyer.


Replication problems after server upgrade

Could any operations be altering the values of the primary key columns of the table?

That error can be generated when a replicated update or delete statement cannot find a specific PK value.


Non-RAC single instance with ASM startup query

Check if autostart for Oracle Restart is enabled:

$ cat /etc/oracle/scls_scr/$HOSTNAME/oracle/ohasdstr
enable

If it is not enabled, then enable it:

crsctl enable has

Check if ASM autostart is enabled:

crsctl stat res ora.asm

If ASM is not registered in GI, add it with:

srvctl add asm ...

Check if used diskgroups are registered:

srvctl status diskgroup -g DATA
crsctl stat res ora.DATA.dg

If they are not registered, add them with:

srvctl add diskgroup ...

Check if the database is registered and autostart is enabled:

srvctl config database -d ORCL

If it is not, register and enable:

srvctl add database ...
srvctl enable database ...

Finally make sure you define the used ASM diskgroups as dependencies:

srvctl modify database -d ORCL -diskgroup "DATA,FRA"

You can also check the listener:

srvctl config listener

If it does not exist, you can add with:

srvctl add listener ...

You do not need to start or stop anything manually with sqlplus, lsnrctl. Oracle Restart takes care of that based on the defined start/stop options and dependencies.


Rebuilding large columnstore indexed tables - am I doing it right?

What I plan to do: •drop the columnstore index, create row-store index
  (this is to align the data in logtime) •then drop the row store index,
  convert the table into column store index (better compression and
  queries later)

You can use CREATE CLUSTERED INDEX...WITH (DROP_EXISTING=ON) to change the existing columnstore clustered index to a b-tree and then CREATE CLUSTERED COLUMNSTORE INDEX...WITH (DROP_EXISTING=ON) to change back to columnstore. This will eliminate the drop index step that changes the columnstore to a heap.

I still expect this will take quite some time with 60 billions rows. Although source data might not have been loaded in C6 order, data overall are typically loaded in roughly chronological order so there will still be a temporal relationship for data in the same proximity. I suggest you review the min and max values in sys.column_store_segments to see if this effort is justified, considering that segment elimination is done after partition elimination. The performance benefit might not be a great as you think.


Where is documented that no other transaction can update a locked row?

You say:

  if there is any LOCK on a row, [then] no transaction (even without LOCK) can update the row

On the page you have linked to you can read that

  An exclusive (X) lock permits the transaction that holds the lock to update or delete a row

which means that before a transaction can update a row it must acquire an X lock on it, which is impossible1 if there is already an S or X lock on that row placed by a locking read in another transaction.

1 -- because X, which as we know stands for "exclusive",  is incompatible with any other lock.


There isn't enough information here to know if the "sync target falling more than 30s behind" was the case, but it seems likely if you are pushing through a lot of activity with all of your replica set nodes on the same machine.

There should be some log entries confirming the change; if you're still on 2.6 you should be able to grep for:

 changing sync target because current sync target's most recent OpTime is

One thing to note is that rs.syncFrom() is a temporary change. By default the sync targets are automatically chosen by MongoDB, and will be reset if you restart instances or lose connectivity.

There's a helpful open source tool called Edda which you can use to process MongoDB log files and visualise changes in replica set history. NOTE: The github repository has changed since that blog post was published; the current repo is 10gen-labs/edda.


Oracle full expdp/impdp but skip data in some tables

No, it is not a good approach, there are a lot of problems, e.g.

Doing a data import after the indexes are created will slow down the import considerable.
the import will fire all insert triggers. That may slow down the import, too, and it may have unwanted side effects
The current values of the sequences do not match to data.

You should use the QUERY parameter. So if you want to exclude the LOG and MESSAGE table of the APP schema add

QUERY=APP.LOG:"where 0=1"
QUERY=APP.MESSAGE:"where 0=1"

to the parameter file of your export.

Another way to accomplish this task is to split this into two export. This is a little bit more complex but avoids reading table data.

export the schema and exclude the large tables. his task is independent from the size of the two large tables
export the structure of the two large tables. The fime for this task is also independent of the size of the two tables

first export (schema export with data)

DUMPFILE=schema.dmp
SCHEMAS=APP
EXCLUDE=TABLE:"in ('LOG', 'MESSAGES')
CONTENT=ALL  # this is the default, so it must not be specified

second export (table exports without data)

DUMPFILE=table.dmp
TABLES=APP.LOG,APP.MESSAGE
CONTENT=METADATA_ONLY

At first you import the schema dump, then you import the table dump. For both imports you can use the FULL=Yparameter.

schema import:

DUMPFILE=schema.dmp
FULL=Y

table import:

DUMPFILE=table.dmp
FULL=Y


First, find the person who put that job in place and have him/her tied to a chair listening to cats scratching a chalk board while you go for lunch.

When done with that, compare your resource utilization and performance data for extended periods before and after the job runs. I suspect you'll see perf dips and a number of resources getting really busy after the job executes. You can temporarily suspend the job and monitor closely for a week to see what happens. I doubt you'll run into problems but as with all changes to a prod system, still keep a close eye for a while and make sure you capture perf, resource usage and waits data.

These sprocs are typically run in a test environment, especially for performance testing, to see how specific queries or the overall workload behaves on a cold-start equivalent. It can also be used to see how specific parts of the system handles unexpected load spikes especially storage.

We have encountered a single support engineer whose standard perf tuning method involves doing these things on a production system; we couldn't get rid of him fast enough. Running all of them on a production system regularly is pretty close to rebooting your server daily to keep it healthy and doing it in the middle of a busy day for best results - really, really shouldn't.


Do NDF files move with Alter Database automatically

ALTER DATABASE can be used to do many things. In order to move the file you'll want to use MODIFY FILE to move your files to the specified location. This goes for mdf, ndf, ldf. Erik Darling blogged about this a while back and gave some examples.

ALTER DATABASE [Sample] MODIFY FILE ( NAME = Sample, FILENAME = 'C:\Whatever\Sample.mdf' );

From the docs:

  MODIFY FILE Specifies the file that should be modified. Only one
   property can be changed at a time. NAME must always be
  specified in the  to identify the file to be modified. If
  SIZE is specified, the new size must be larger than the current file
  size.

  To move a data file or log file to a new location, specify the current
  logical file name in the NAME clause and specify the new path and
  operating system file name in the FILENAME clause. For example:

  To move a data file or log file to a new location, specify the current
  logical file name in the NAME clause and specify the new path and
  operating system file name in the FILENAME clause. For example:


SQL redundant databases both sets being modified independently yet staying concurrent

SQL Server has various flavours of replication with different capabilities.

It sounds like peer-to-peer transactional replication best fits your needs.


How to make a line/string to multiple records

Just to be clear:

Each degree has:

Position 1) Degree Type
Position 2) Year
Position 3) Univerity / College

Item 4) There can be more than 1 degree or only 1 degree.

Other Criteria:  Each entire section is separated by a ';' (semicolon) each subsection is separated by a comma.
No other commas are in the string?

Here's the code.

Declare @TestName as varchar(500)
Declare @Pos1 as int
Declare @Pos2 as int
Declare @Pos3 as int
Declare @Pos4 as int

Set @TestName = 'B.A, 1983, University of China; M.B.A, 1999, University of Taiwan; Ph.D, 1999, University of Southern California,'

---Start loop
--Get the position of the first 'comma'
Select @Pos1= PATINDEX('%,%',@TestName)

--Get the position of the second 'comma'
Select @Pos2= PATINDEX('%,%',Substring(@TestName, (@Pos1+1), (Len(@TestName)-(@Pos1+1))))

--Get the position of the end section.
Select @Pos3= PATINDEX('%;%',Substring(@TestName, (@Pos2+@Pos1+1), (Len(@TestName) )))

Select
SUBSTRING(@TestName, 1, @Pos1-1) as DegreeType,
lTRIM(SUBSTRING(@TestName, @Pos1+1, @Pos2-1)) as GradYear,
SUBSTRING(@TestName, @Pos1+@Pos2+1, @Pos3-1) as University

-- Reset the value of @TestName so you can loop start over.
Set @TestName = SUBSTRING(@TestName,@Pos1+@Pos2+@Pos3+1, Len(@TestName) - @Pos3+1)

--Shows you what is left.
Select @TestName
--Loop End

You'll still need to build loop and the above code is in SQL Server but it should translate across if you are using another database system.


Difference between Aurora Global database and Aurora regional database with multiple read replicas?

Aurora uses MySQL replication to replicate to a secondary data
while Aurora Global database uses underlying hardware to replicate your data from another region which doesn't affect your database performance compared to using read replicas.


SQL Server Management Studio highlights a word that does not appear to be a reserved word

SSMS uses color syntax highlighting for keywords that are not necessarily reserved keywords, but are keywords in the context of certain statements.  For example POOL is a keyword as in CREATE RESOURCE POOL.  SSMS will show the word in a different color even when not in the context of a CREATE or ALTER resource pool.


How to update multiple columns of multiple rows in one SQL statement

You could supply the new values as a table (with the help of the VALUES row constructor), so that you could join it with the target table and use the join in the UPDATE statement, like this:

UPDATE
  tgt
SET
  Column1 = src.Column1,
  Column2 = src.Column2,
  Column3 = src.Column3,
  ...
FROM
  dbo.TargetTable AS tgt
  INNER JOIN
  (
    VALUES
    (1, 'a', 'k', 'x', ...),
    (2, 'b', 'l', 'y', ...),
    (3, 'c', 'm', 'z', ...)
  ) AS src (ID, Column1, Column2, Column3, ...)
    ON tgt.ID = src.ID
;


Do values in `sys.dm_os_wait_stats` reset to 0 or stop accumulating when they hit the max value?

According to Docs:

  In SQL Server the wait-time counters are bigint values and therefore
  are not as prone to counter rollover as the equivalent counters in
  earlier versions of SQL Server.

I've seen this with earlier version of SQL Server where rollover happened and you'd get negative numbers because the values are signed.

I took a look at the source code and indeed these are 8-byte signed values. Since this is all C++, I created a simple repro:

#include "stdint.h"
#include <iostream>

using namespace std;

int main()
{
    int64_t i8 = 0;
    cout << "Size of integer: " << sizeof(i8) << " bytes." << endl;
    cout << "Max Value: " << INT64_MAX << endl;
    i8 = INT64_MAX + 1;
    cout << "Value of MAX + 1: " << i8 << endl;
    cin;
    return 0;
}

The output is similar to what I experienced in previous versions of SQL Server, the signed value is rolled over. Note that if you compile this, you should get a compiler warning that there will be rollover - I received compiler warning 4307.

Output of above:

Size of integer: 8 bytes.
Max Value: 9223372036854775807
Value of MAX + 1: -9223372036854775808

I'm NOT stating this is exactly how SQL Server works in all and every case as there are many nuances... but this should be a similar approximation.

What does this all really mean?

I highly doubt, as Aaron has said, that you'll ever encounter rollover on the 64-bit values in the current hardware climate. If you did, it would be easy to spot and really be more of reporting inaccuracy than any real issue in my opinion.


How to increment identity without add a row and get the skipped value?

You could use DBCC CHECKIDENT (RESEED) for this purpose, but keep in mind you need special permissions, and I'm not sure it worth.

  Permissions
  Caller must own the schema that contains the table, or be
  a member of the sysadmin fixed server role, the db_owner fixed
  database role, or the db_ddladmin fixed database role.

CREATE TABLE tbl (foo int IDENTITY, bar int);

INSERT INTO tbl (bar) VALUES (10),(15),(20);

SELECT * FROM tbl;

foo         bar
----------- -----------
1           10
2           15
3           20

/* save current identity value */
DECLARE @CURRENT_ID int;
SELECT @CURRENT_ID = IDENT_CURRENT('tbl');
PRINT 'Current ID = ' + CAST(@CURRENT_ID as varchar(10));

Current ID = 3

/* get next identity value */
DECLARE @NEXT_ID int;
SET @NEXT_ID = @CURRENT_ID + 1;

/* reseed */
DBCC CHECKIDENT (tbl, RESEED, @NEXT_ID);

SELECT * FROM tbl;

foo         bar
----------- -----------
1           10
2           15
3           20
5           30
6           35
7           40

DROP TABLE tbl;


Display table names in titles in SQL Server Management Studio

No, there is no way to make Management Studio do that. You've opened a query window, and you can customize what is displayed there, but table name is not one of the choices. After all, a small number of queries involve only one table. And think about these questions:

If you add another query to that window, what should the title become?
If you change the query it produced to be a join, what then?
If you've saved the query as some file, should the table name override the name you gave?

These questions are rhetorical, since the functionality is not possible anyway, but there are many other technical considerations that go far beyond right-clicking a table and saying SELECT TOP...

SSMSBoost seems to give you some control over these things, but I don't see table name as being an option here either (probably for the same technical challenges I raised in my bullets).


How convert database from SQL Server 2012 TO SQL Server 2005

If you can't install SQL Server on your PC the only choice you have (if the database is already 2012) is to script out the databases objects, change anything that isn't compatible with 2005, and then run the scripts for your 2005 instance.

Because there are many changes between 2005 and 2012, datatypes among other database objects may not align properly. This means the application(s) being used with the database if put into 2005 from 2012 may not function correctly or at all.

Your best option is to get a waiver to install 2012 developer edition or some other test system for your 2012 needs.


While Postgres doesn't allow a partially unique constraint, it does support a partial unique index:
create unique index unique_row on myTable(content_id, brand_id) where not is_archived;

See Partial Indexes in the Postgres documentation.
This is effectively pretty much the same as a unique constraint, because such constraints are implemented with unique indexes anyway.


Optimize MySQL FULLTEXT searches to use another index

Give this a try (and let us know if it helps):

SELECT  p.*, ft.relevance
    FROM  (
        SELECT id,
               MATCH (brand, name, variation) AGAINST('something' IN BOOLEAN MODE) AS relevance
            FROM Product
            WHERE MATCH (brand, name, variation) AGAINST('something' IN BOOLEAN MODE)
          ) AS ft
    JOIN Product AS p USING(id)
    WHERE p.side_id = 7
    ORDER BY  ft.relevance DESC
    LIMIT  10;

Also, provide EXPLAIN FOMAT=JSON SELECT ....


mongodb cluster Data distribution is not uniform

As at MongoDB 3.4, collection balancing is based on chunk counts and a migration threshold which is the delta between the shard with the most chunks for a collection and the shard with the least. For example, with your deployment having more than 80 chunks the migration threshold to trigger a balancing round would be a difference of 8 chunks. Your sample sh.status() output includes 15 shards with 72 or 73 chunks each, so the flanker.bill collection would be considered balanced.

A chunk represents a range of shard key values containing documents up to the configured chunkSize which is 64MB by default. Individual chunks are not expected to have similar document counts or data size, and some use cases (eg. highly variable document sizes, frequent data deletion, lack of cardinality in shard keys) may lead to more significant differences in the sum of documents represented by a chunk. The usual outcome is that on average chunk ranges provide a reasonable proxy for balancing data in a sharded collection while requiring minimal accounting/coordination between shards.

With a hashed shard key on a field with good cardinality you should expect reasonably random distribution of shard key values which helps distributes inserts across shards and reduces the need for rebalancing.

Since you appear to have a significant imbalance of data between shards, I would investigate further by calculating more details on actual data stored on the shards as per: How to Determine Chunk Distribution (data and number of docs) in a Sharded MongoDB Cluster. NOTE: calculating exact document totals (vs estimates) can be resource intensive, so exercise caution if this is your production environment and start with estimates.

It is likely that you have a number of chunk ranges which contain few (or in extreme cases, no) documents due to data deletion and more of these happen to have ended up on the same shard. If so, there is a manual administrative procedure to Merge Empty Chunks which may be relevant. Empty or sparsely populated chunks are not merged automatically as the assumption is that new documents will still be inserted into these chunk ranges in future.


Paul Randal is probably the undisputed expert in Sql Server corruption.

According to him, 99.8% of all cases of corruption that he has seen were caused by the IO subsystem.  You might find some interesting information here (Myths around causing corruption).

He also talks about doing root-cause analysis here (My SQL Server Database is Corrupt - Now What?!)

The first thing I'd be doing is asking my storage guys for another disk drive and moving all of my logs there.  This 'might' solve the problem if your current log disk drive is having problems, but it wouldn't be a guarantee since there are other possibilities that might be causing your problem.


The inner part you can create like this

DECLARE @table TABLE(Lookup_item_id INT, code VARCHAR(100),lookup_item_title VARCHAR(100));
INSERT INTO @table  VALUES
 (220,'ad_flat.installations','Town gas')
,(221,'ad_flat.installations','City water')
,(224,'ad_flat.heating_type','gas')
,(226,'ad_flat.heating_type','wood');

SELECT t.code AS [@code]
      ,t.Lookup_item_id AS [*]
FROM @table AS t
FOR XML PATH('lookup_item'),ROOT('lookup_list');

The result

<lookup_list>
  <lookup_item code="ad_flat.installations">220</lookup_item>
  <lookup_item code="ad_flat.installations">221</lookup_item>
  <lookup_item code="ad_flat.heating_type">224</lookup_item>
  <lookup_item code="ad_flat.heating_type">226</lookup_item>
</lookup_list>

This you can integrate into your select within paranthesis like a column (scalar sub-select).


I don't know fully details of your environment, i.e. if the tables in question are mostly used for writing or reading.

How often you do this delete?

what is the primary key and clustered index of [sko].[stage_närvaro]?

If I wanted to optimise this delete there are a few things I would consider:

1) an index on the underlying tables of the view  ext.v_imp_närvaro with the columns used in the select (person_id, termin_fakta,[läsår_fakta]) you want an index seek there most likely (no need to include any columns because you are just going there for the EXISTS)

2) I have been using a lot filtered indexes and I would consider the following:

CREATE NONCLUSTERED INDEX IDXF_STAGE_NARVARO_FORSYSTEMS_EXTENS
ON [sko].[stage_närvaro] ([försystem])
INCLUDE ([person_id],[termin_fakta],[läsår_fakta])
WHERE [försystem] = 'Extens'

3) I can see some LCK_M_S going on there, not sure if related to this query in particular but nevertheless I try to always use ROWCOUNT and do big deletes and updates in batches, something like the following:

    USE DATABASENAME
    GO

    DECLARE @RC INT
    SELECT @RC = 0
    SET ROWCOUNT  5000

    WHILE (1 = 1)
      BEGIN
        BEGIN TRANSACTION

                        DELETE  sko.stage_närvaro
                        FROM    sko.stage_närvaro e
                        WHERE   försystem = 'Extens'
                        AND EXISTS (
                            SELECT  *
                            FROM    ext.v_imp_närvaro v
                            WHERE   e.person_id = v.person_id
                            AND     e.termin_fakta = v.termin_fakta
                            AND     e.läsår_fakta = v.läsår_fakta
                        )

                     SELECT @RC = @@ROWCOUNT

                     print CAST ( DB_NAME()  AS VARCHAR(500) ) +
' -- ' + CAST ( @RC  AS VARCHAR(10) ) + ' -->  ' +
CAST( GETDATE() AS VARCHAR(25))

                     WAITFOR DELAY '00:00:01';

        IF @RC = 0
          BEGIN
            COMMIT TRANSACTION

            BREAK
          END

        COMMIT TRANSACTION
      END

    SET ROWCOUNT  0

 --==============================================================
 --SET ROWCOUNT 10000 -- define maximum updated rows at once

-- DO THE UPDATE

-- don't forget about bellow
-- after everything is updated
--SET ROWCOUNT 0

-- Setting ROWCOUNT to 0 turn off limits - don't forget about it.
--===============================================================

This may not be a comprehensive solution, because there are bits missing on the question too, however, it will surely give you some ideas as possible ways to improve big delete operations.


Datetime columns in sys.dm_db_mirroring_connections are an hour behind

Since it is presently summer time in the Northern hemisphere, it's likely that you're comparing winter time to summer time, which would explain the 1 hour difference.


Postgres "session variables" kludge on AWS RDS

AWS Support confirmed that it is, indeed, impossible to set defaults for custom session variables on RDS PostgreSQL.

The next best solution was to trap SQLSTATE 42704 (undefined_object) errors in all the trigger functions with

  EXCEPTION WHEN undefined_object THEN SET kludge.param TO 'default_value';

followed by replicated trigger code, so that the trigger gets properly executed even in the error case.


MySQL query returns different result on second execution

The order of columns is important, if you are going to use mysql variables this way. You are using @revenue_share before it is calculated. So, the first time the query runs, NULL is used for the calculation of @marketing_spend. The second time the query runs, the variable has a value saved from the 1st run.

Additionally, the two subqueries that have FROM kpi_data can be combined in one. And you don't really need to use variables. You can get the same results using derived tables and one parameter only (@discount_code), used three times:

SELECT
    @discount_code AS discount_code,
    sales_revenue,
    rsp_float,
    marketing_spend
    sales_revenue - marketing_spend AS roi_dollars,
    revenue_share,
    CAST( ((sales_revenue - marketing_spend) / marketing_spend) AS DECIMAL (5, 2) )
        AS roi,
    CAST( (sales_revenue / marketing_spend) AS DECIMAL (5, 2) )
        AS mroi
FROM
    (   SELECT
            sr.sales_revenue,
            kd.revenue_share_percentage / 100  AS rsp_float,
            kd.fixed_media_cost,
            sr.sales_revenue * kd.rsp_float AS revenue_share,
            kd.fixed_media_cost + (sr.sales_revenue * kd.rsp_float)
                AS marketing_spend
        FROM
            (   SELECT
                    CAST(SUM(total_price) AS UNSIGNED) AS sales_revenue
                FROM
                    shopify_orders
                WHERE
                    discount_code = @discount_code
            ) AS sr
          CROSS JOIN
            kpi_data AS kd
        WHERE
            kd.CODE = @discount_code
    ) AS t ;


Designing a database structure for a permissions system

"... the yellow tables are arrays so that every user can have multiple ..." -- Good.

Restricted access should be in the application layer.  No user should be able to write arbitrary SQL.

Dates should not be separated -- it will make inefficient to filter on a range of dates.  Anyway, where do you need "multiple dates"?

That's a lot of sensitive data; rethink storing all of it.


Consider having a row in the userstat table for each attempt that the user makes at the test. This means, then, that you would have to make the composite primary key of the table to be users_userid and attemptid (which can be an identity column).

I would maybe think that the questions table should be a many-to-many relationship between userstat and questions rather than between users and questions, assuming that each time a user attempts the test they may get different questions. Either way the primary key on this table needs to include more than the single foreign key, it should include both foreign keys.


Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?

The reason for the behavior is that SQL Server can't determine how many rows will match to ForeignKey, since there is no index with RowKey as the leading column (it can deduce this from statistics on the #temp table, but those don't exist for table variables/UDTTs), so it makes an estimate of 100,000 rows, which is better handled with a scan than a seek+lookup. By the time SQL Server realizes there is only one row, it's too late.

You might be able to construct your UDTT differently; in more modern versions of SQL Server you can create secondary indexes on table variables, but this syntax is not available in 2008 R2.

BTW you can get the seek behavior (at least in my limited trials) if you try to avoid the bitmap/probe by hinting a nested loops join:

DECLARE @Keys TABLE (RowKey INT PRIMARY KEY); -- can't hurt

INSERT @Keys (RowKey) VALUES (10);

SELECT
     t.RowKey
    ,t.SecondColumn
FROM
    dbo.Test t
INNER JOIN
    @Keys k
ON
    t.ForeignKey = k.RowKey
    OPTION (LOOP JOIN);

I learned this trick from Paul White several years ago. Of course, you should be careful about putting any kind of join hints in production code - this can fail if people make changes to the underlying objects and that specific type of join is no longer possible or no longer most optimal.

For more complex queries, and when you move to SQL Server 2012 or above, it's possible that trace flag 2453 could help. That flag didn't help with this simple join, though. And the same disclaimers would apply - this is just an alternative thing you shouldn't generally do without a ton of documentation and rigorous regression testing procedures in place.

Also, Service Pack 1 is long out of support, you should get on Service Pack 3 + MS15-058.


Delayed durability for reporting database

My question is that will changing the setting to delayed durability have an effect on the speed of the SP?

If the stored procedure makes many small updates without a transaction then delayed durability might speed it up.  But batching the inserts into a larger transaction would be a better idea.


((100G is not the same as 100M.  typo was fixed))

The line you gave is what you do -- in my.cnf.  (Double check the syntax.)

That configuration was very important 15 years ago -- when some operating systems could not create a file bigger than 2GB (or 4GB).  Now is is just an artifact.  Most people simply have autoextend on the last entry and forget about it.

The config says, as you say, "fill up one before moving on to the next".  The number (eg, "100M") is the pre-allocated size.

Why do you want this config?  I am curious as to whether you have a use case for current machines.

It is usually better to have innodb_file_per_table=ON.  Each table that you subsequently CREATE or ALTER is put into it's own "tablespace", which is identified by a file: <tablename>.ibd.  At that point, you don't need a huge, growing, set of ibdata* files.  (Leave autoextend on the last one, just in case.)

Limitations... There is no way to have that config automatically add another ibdata* file name, only autoextend the last one.  Once created, you can't get rid of any of the files without dumping and reloading.

EDIT. "but we have many dynamically created tables per second" -- Ouch!  How many tables do you have?  How tiny are they?  In general, thousands of tables is bad.  Might it make sense to combine tables in some way?  Would you care to elaborate on the design that 'needs' lots of tables?

Lots of tiny tables are better off in ibdata* (file_per_table = OFF).  The OS overhead for more files is non-zero.  (For big tables, the maintenance benefit of .ibd wins.)  Regardless of the setting, there will be a .frm file per table.

Yes, lots of ibdata* files would let you migrate some to new drives.  Use "symbolic links" (if unix) or the equivalent if Windows (Junctions?).


what is the difference between data file and data block in oracle

The Logical Storage Structures section in the Oracle Concepts Guide has a good explanation of both data blocks and data files (pages 259 - 296).  There is quite a bit of good information including illustrations, but here are some excerpts:

  data block
  Smallest logical unit of data storage in Oracle Database.
  Other names for data blocks include Oracle blocks or pages. One data
  block corresponds to a specific number of bytes of physical space on
  disk.

  data file
  A physical file on disk that was created by Oracle Database
  and contains the data for a database. The data files can be located
  either in an operating system file system or Oracle ASM disk group.


IDE Access to Hive without user password?

I could not test the below described procedure as I don't have the same infrastructure as you, but I suggest you to try this and come back with further details if any of the steps fails.
DBeaver should be able to do this trick for you:

Install DBeaver, either as an Eclipse plug-in or as a stand-alone application
Create a new connection, for example by clicking on the  icon
Select Apache Hive as connection type, and then click "next":

In Generic JDBC connection setting, go first to "SSH" tab and setup your SSH tunnel:

Still in Generic JDBC connection setting, come back to "General" tab and click on "Connection details (name, type, ... )" at the bottom right of the window
On the new screen, click on "Shell commands":

Then I am less sure, but either in "Before connect" or "After connect", add your command sudo su - hive_user. I would rather expect "Before connect", but it depends in which sequence DBeaver connects to SSH tunnel and execute this command. If the first solution doesn't work then I would test the other one:

Once done, click "Back" and fill in the "General" tab with your Hive user. You click "Test connection ..." to ensure everything works fine. Once it works, you can click on "Finish".


There is some misunderstanding here.

Your alert log shows that you were trying to use Data Guard features, this is clearly not a manual standby.

A manual standby is a copy of the "primary" database continuously rolled forward manually with the archive logs from the "primary" database.
You transfer the archivelogs with OS commands, e.g. scp or rsync. Then you periodically register the archivelogs and recover the database with RMAN or SQL*Plus user managed recovery, but definitely not the Data Guard Media Recovery Process.
Data Guard is not involved at all, so there is no need for a standby controlfile, just a regular controlfile.


SQL - Trigger to update a column as NULL when another column has a fixed value

I agree with Andriy that a constraint should be used, but to answer your question if this is possible with a trigger, the answer is yes.

Here is the trigger:

CREATE TRIGGER i_Seller
ON Seller
AFTER INSERT
AS
IF EXISTS (SELECT NULL FROM inserted i WHERE Type = 'Org')
BEGIN;
    UPDATE s
    SET MidName = NULL
    FROM Seller s
    JOIN inserted i ON s.SellerID = i.SellerID
    WHERE s.Type = 'Org';
END;


Row estimate more accurate for primary key column than for others?

The "magic" you observed lies in this detail of the query planner. Quoting the manual:

In any case, the planner will scale the values it finds in pg_class to
match the current physical table size, thus obtaining a closer approximation.

The values it  in pg_class are reltuples and relpages - the number of live rows and data pages on disk.
Since the physical size will have approximately doubled, Postgres will expect approximately that many rows, which explains the pretty accurate estimate for the sequential scan.
Value frequencies in pg_statistic are outdated and cannot be scaled as trivially as the row count.  You'd need to run ANALYZE for that. Explains the estimate for the index scan.
I don't see how the PK would play any special role in this.


Is LocalDB installed independently of SQL Server?

LocalDB is an optional feature of SQL Server Express edition.  It may be installed by:

1) Selecting the LocalDB feature during installation of SQL Server Express

2) Selecting the .NET desktop development feature during installation of VS 2017. According to this thread, SSDT does not automatically install LocalDB.


how to get only those records that have more than one values?

You need to group and join to the aggregation

select t1.*
from the_table t1
  join (
    select id
    from the_table
    group by id
    having count(distinct matches) > 1
  ) t2 on t1.id = t2.id


Best lock strategy to prevent deadlock on concurrent updates

The explicit locking doc says acquire in a consistent order, so perhaps always do the lower id first.

For explicit locking perhaps row level SELECT 1 FROM customer WHERE id IN (1,2) FOR UPDATE.

You should handle the deadlock at the query level, the schematics of a deadlock means its not obvious the the database how it should continue and leaves it for a application designer to implement. Exit and watchdog seems rather extreme.


Is it possible to put mdf and ldf to NAS (Synology)

You should be able to store the files on the NAS.
A bigger problem will be moving between PCs. Each machine will be its own instance of SQL Server. It will not be possible for both instances to own the database files at the same time. You will have to attach the file to one instance, work on that machine, detach from that machine, go to the other and attach the files to that instance before you can work on the DB.
A simpler approach would would be to have one of your boxes (the more powerful one) own the files permanently. Use the other box to remotely communicate with the owning box. Use a remote desktop session, or simply have SSMS (or other client tool) resolve to the host\instance name.


Normalization is hard :/

Guys I had a go, but I doubt it's correct - please be gentle. For example we can SUM to get current stock, but the joins seem kind of unnecessary instead of using a figure stored somewhere. I would love to see the thought process of someone experienced designing this. Anyway:

Product
---
product ID
product name
category

1, aspirin, tablets
2, toxazon, injections
3, ibuprof, tablets

Batch
---
batch ID
product ID
expiry date

asp-001, 1, 2015-03-01
tox-001, 2, 2015-02-10
tox-002, 2, 2015-02-28

Transaction
---
batch ID
transaction date
amount

asp-001, 2015-01-01, 200
tox-001, 2015-01-30, 200
asp-001, 2015-02-01, -100
tox-002, 2015-02-07, 500


How to update a field in a nested JSON document in Mongo?

As per MongoDB documentaion here The update method can modify specific fields of an existing document or documents or replace an existing document entirely, depending on the update parameter.

By default, the update() method updates a single document. Set the Multi Parameter to update all documents that match the query criteria.

The update() method has the following form:

Changed in version 3.6.

db.collection.update(
   <query>,
   <update>,
   {
     upsert: <boolean>,
     multi: <boolean>,
     writeConcern: <document>,
     collation: <document>,
     arrayFilters: [ <filterdocument1>, ... ]
   }
)

For example to update the JSON documents through update command in mongo shell, which you have been mention.

Here I have created the test database & also users collection based on your mention documents.

> use test
switched to db test
>db.createCollection("users");
{ "ok" : 1 }
> db.users.insert({
...   "_id" : '123',
...     "friends": [
...       {name: 'allen', emails: [
...         {
...           video: {
...           name:'abc',
...           subType:'video',
...           status:'active'
...               }
...         },
...         {
...           "video": {
...           "name":'abc',
...           "subType":'video',
...           "status":'active'
...               }
...         }
...         ]}
...     ]
...  })
WriteResult({ "nInserted" : 1 })

Now I want to see the inserted documents in users collections in mongo shell through the command such as

> db.users.find().pretty();
{
        "_id" : "123",
        "friends" : [
                {
                        "name" : "allen",
                        "emails" : [
                                {
                                        "video" : {
                                                "name" : "abc",
                                                "subType" : "video",
                                                "status" : "active"
                                        }
                                },
                                {
                                        "video" : {
                                                "name" : "abc",
                                                "subType" : "video",
                                                "status" : "active"
                                        }
                                }
                        ]
                }
        ]
}

As you want to update the status : Inactive , which comes under the metadata of video.
The update statement command will be as follows

> db.users.update ({"_id": '123'}, {$set: {"friends.0.emails.0.video.status" : 'Inactive'} });
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

Again I will check through the find method , it has been modified or not.

> db.users.find().pretty();
{
        "_id" : "123",
        "friends" : [
                {
                        "name" : "allen",
                        "emails" : [
                                {
                                        "video" : {
                                                "name" : "abc",
                                                "subType" : "video",
                                                "status" : "Inactive"
                                        }
                                },
                                {
                                        "video" : {
                                                "name" : "abc",
                                                "subType" : "video",
                                                "status" : "active"
                                        }
                                }
                        ]
                }
        ]
}


Running Check Check Constraint and still have untrusted foreign keys (not a replication issue)

So, turns out the Application Code uses BULK INSERTS which makes the FK's become un-trusted.  To resolve this, I need to get the Developers to add in WITH ( CHECK_CONSTRAINTS ) to the BULK INSERT to resolve the issue and then re-run the WITH CHECK CHECK CONSTRAINTS.  Thanks to Dan Guzman for the prompt.


user "sa" cannot connect to SQL Server Express 2008 R2

I had to do two things to solve it.

1.) I didn't have mixed mode turned on.  I had to right click the server node in the Object Explorer and go to Server Properties.

https://stackoverflow.com/questions/2474839/unable-to-login-to-sql-server-sql-server-authentication-error-18456

2.) restarted Windows Service for SQL Server.


$ ssh svuser@dbexample.com
$ dokku mongo:export database > backup.tar
$ tar -xfv backup.tar
tar: This does not look like a tar archive
tar: Skipping to next header
tar: Exiting with failure status due to previous errors

As per github dukku-mongodb documentation by @josegonzalez here I don't think the export is actually a tar. The readme we have is wrong, but if you run dokku mongo:help export, you'll see the correct usage.

Here's what dokku mongo:help export says:

export a dump of the MongoDB service database

arguments:

service   service to run command against

examples:
by default, datastore output is exported to stdout

dokku mongo:export lolipop

you can redirect this output to a file

dokku mongo:export lolipop > lolipop.dump

export a dump of the MongoDB service database
arguments:

service   service to run command against

examples:
by default, datastore output is exported to stdout

dokku mongo:export lolipop

you can redirect this output to a file

dokku mongo:export lolipop > lolipop.dump

For further your ref here


SQL Server: Create New GUID Values for Existing Index

You may be able to reduce index rebuild/reorg times by moving to NEWSEQUENTIALID().

You are currently inserting around 4,500 rows into the table per day.  The table has around 6.3 million rows at present.  That's around 0.7% of the table per day.  Assuming every insert on the table results in an index-page split, this will result in 9,000 pages per day written to the storage system.  This will create some unnecessary I/O pressure both for inserts into the index, and for rebuild/reorgs.  Switching the Key field to sequential ID generation will vastly reduce this I/O pattern.

If you switch to NEWSEQUENTIALID(), the code that performs INSERTs on the table will need to be sensitive the possibility that it will attempt to insert a duplicate Key.  This is a remote possibility, however it is still a possibility, and is the same for both NEWID() and NEWSEQUENTIALID() - since nothing is truly random that is generated by a computer.  Queries against the index could gain some benefit by making the index unique, as in:

CREATE UNIQUE NONCLUSTERED INDEX [idx_Robert] ON [dbo].[Aleksander]
(
    [Key] ASC
) WITH (
    PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , DROP_EXISTING = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , FILLFACTOR = 80
    ) ON [PRIMARY];

This change would make index scans less likely.  Paul White has an excellent article about this at https://sqlkiwi.blogspot.com/2011/02/seeking-without-indexes.html

Since you haven't given any indication of the type of queries you run against this table, I will assume you are at some points scanning the entire index nothing about your situation.  If your data is truly stored on a single spinning disk, then having your index non-fragmented should reduce the time needed for queries to return information.

Having said that, you could likely get a whole lot more for your money (time is money) by improving the IO subsystem so index rebuild/reorgs are unnecessary.  See http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/ .

If you are interested in measuring the effects of the index settings you are using for the Key column, you could monitor page free percentage (among other things) for pages used by the index by using the system dmv, sys.dm_exec_index_physical_stats.  For example:

SELECT o.name, ps.avg_page_space_used_in_percent, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('KeyTable','Table'), NULL, NULL, 'DETAILED') ps
    INNER JOIN sys.objects o ON ps.object_id = o.object_id;

(You may want to adjust the parameters passed to only show the index on the Key column.)

Once the avg_page_space_used_in_percent crosses some boundary value such as 95% (or more), you would then rebuild the index, which both defragments it, and with an appropriate FILL_FACTOR setting, leaves some space free on each page for new rows with randomly generated NEWID() values.

If you did decide to use NEWSEQUENTIALID() your index could use a FILL_FACTOR of 100% since index pages would not longer be split on a frequent basis.  This has the benefit of reducing the number of pages required to hold the index by 20% (against your current settings), thereby reducing the IO required to rebuild the index when that does become necessary.

Since you are only inserting new rows at a rate of about 0.07% of the total table per day, it may take a very long time before the system automatically triggers a histogram update on the statistics for the table involved.  You could use the STATS_DATE() function to ensure the statistics are never more than x days old.  Up-to-date statistics are vital for the query engine to produce optimal query plans.


The linked paper has a definition:

  In the relational model, a query Q1 is said to be contained in the query Q2 if Q1 produces a subset of the answers of Q2, for any given database.


How to change MySQL InnoDB cluster from single primary mode to multi primary mode?

Any reason why you're not using MySQL Shell and its AdminAPI to set up and manage your InnoDB cluster?

You can create a multi-primary cluster very easily using the AdminAPI. Example:

var cluster = dba.createCluster("myCluster", {multiPrimary: true})

Also, if you already have a running Group Replication group that you'd like to manage using the Shell you can simply "adopt" it:

var cluster = dba.createCluster("myCluster", {adoptFromGR: true})

And since 8.0.14, you can change a running InnoDB cluster from single-primary mode to multi-primary mode "live". The AdminAPI provides a function for that operation:

<Cluster.>switchToMultiPrimaryMode()>

Also, it is possible to switch back a running cluster to single-primary mode:

<Cluster.>switchToSinglePrimaryMode([instance])

And even to elect a member of the cluster to be the new primary:

<Cluster.>setPrimaryInstance(instance)

Please take a look at the blog post that announced those new features:

https://mysqlserverteam.com/mysql-innodb-cluster-changing-cluster-topology-modes-live/

And for further information check the official userguide.

Cheers,

Miguel


How Ola Hallengrens Index maintenance scripts handles columnstore indexes?

Ola Hallengren's solution doesn't support Columnstore indexes for now.

There is an outstanding feature request on his Github that hasn't been closed as wontfix so you might want to keep an eye on that: ColumnStore Support for Index Maintenance #71


Query sys.columns and sys.types like this:

select object_name(c.object_id) TableName, *
FROM sys.columns c
join sys.types t ON c.system_type_id = t.system_type_id
where t.name = 'datetime'


How do delete duplicate key row in mssql server?

Cannot insert duplicate key row in object 'dbo.posALL' with unique index 'PK_tposALL'

Breaking down this error message a little:

You have a table named dbo.posALL
There is a unique index named PK_tposALL defined on this table
The program you are using tries to insert a record with values for the key columns defined in PK_tposALL that already exist in the table

To understand which columns constitute a duplicate, you can go to the table in SSMS, expand the "Keys" folder, and then choose "script key as."  Check out this screenshot:

ALTER TABLE [dbo].[Badges]
ADD CONSTRAINT [PK_Badges__Id] PRIMARY KEY CLUSTERED
(
    [Id] ASC
);

Note that in this case, the unique index is defined using a primary key constraint.  I imagine this is the case in your scenarios as well.  Yours may or may not be CLUSTERED, and that part doesn't matter for this situation.

In any case, my dbo.Badges requires the Id column to be unique in the whole table.  Your column list will be different.

To find the duplicate data, you'll write a query that specifies each column from the unique index in the WHERE clause:

SELECT b.*
FROM dbo.Badges b
WHERE
    b.Id = 128;

In your case, it will look something like this:

SELECT *
FROM dbo.posALL a
WHERE
    a.ColumnA = 123
    AND a.ColumnB = 456
    AND a.ColumnC = 'abc';

Where,

ColumnA, ColumnB, and ColumnC are the columns in PK_tposALL
123, 456, and 'abc' are the values your program was trying to insert into the table

In the end you'll have to decide what to do from there, possibly either deleting the existing row (so that you can insert the new row) or updating the existing row with the values from the new row.


NOT IN statement or what to use in this case?

DELETE FROM fps WHERE fps.id NOT IN
    (SELECT DISTINCT fps_id FROM fmmp)

The sub query should find a list of fps ids that are being referred to, so you can delete any fps entries that are not in that list.


SQL Server - global temporary table vs normal table

A normal table will cause logging in your database, consume space, and require log flush on every commit.  So for temporary data, you should use a temporary table.

But you would normally use a regular (#) temporary table here, not a global (##) temporary table.

Global temporary tables are useful in the (very rare) scenario where multiple, separate sessions need access to the same temporary table.  Another scenario where a global temporary table can be useful is when the temp table is created in dynamic SQL, but referenced in the calling batch,  eg:

exec ('select * into ##foo from sys.objects')
select * from ##foo

But a global temporary table is not necessary to create a temp table in a stored procedure and access it in dynamic SQL, eg:

create table #foo(id int, data varchar(200))
exec ('insert into #foo(id,data) values (1,''hello'')')
select * from #foo

And when you create a regular temporary table in a stored procedure it will automatically be destroyed when the stored procedure ends, and you can run multiple instances of the same procedure without interfering with each other.


Pitfalls of renaming tables in sql server

Another option is to use partition switching once you have both tables set up create a switch out table.

Then do a simple switch command like this:

ALTER TABLE [Table Name] SWITCH
   TO [Table Name]_SwitchOut

ALTER TABLE [Table Name]_SwitchIn SWITCH
   TO [Table Name];

Then simply drop the in and out tables.


If you want to run packaged in the project deployment mode (SSISDB), you need to install SQL Server DB engine on the SSIS box.
If you want to execute packages from files system or other stores, you can skip DB engine.
In both cases you need a license for the SSIS box.

In SSISDB mode you will manage packages using SSMS connection to the DB engine.

Steps how to install SSISDB:
1. Open SQL Server Management Studio.
2. Connect to the SQL Server Database Engine.
3. In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
4.Click Enable CLR Integration. The catalog uses CLR stored procedures.

From:
https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017


MySQL : Extract SQL statements from binlog

--base64-output=DECODE-ROWS --verbose

This is the format you need to be using if you are reviewing the binlog manually... it extracts the maximum amount of useful data from the log... but it will help if you understand how binary logging works in MySQL, first.
RDS (at least for MySQL 5.5 and 5.6) uses binlog_format = MIXED.  This means the server may choose, on a query-by-query basis, to log either the actual SQL statement that was executed ("statement" log), or the actual rows changed by the query ("row" log) but not both.
The "commented SQL" that you are seeing is, as explained in the documentation, a human-readable pseudo-sql reconstruction of the actual data that was changed on the master by your query, for events logged using the ROW format.  The body of the query that made the change is not preserved, because it is not needed for replication -- slave servers can replicate the exact same changes to their tables as occurred on the master, based on these before ("where") and after ("set") row images.
You can't "get" the original SQL from these events because it's not being saved to the binary log when events are written like this.  In the example you posted, it's telling you that the value of the 3rd column changed.
###   @3=1373178370 # old value

###   @3=1378479295 # new value

This "pseudo-SQL" is commented out because it isn't actual valid SQL that a server could execute.  It's eyeball-compatible-only.  (For a server to replay these row events, it needs to see the BINLOG blocks, which are a base64-encoded version of the information that mysqlbinlog is decoding for you, here).
The column names are also not preserved in the binary log for row-based events, because since they should be the same on master and slave, that information is not needed.
(Edit: you mentioned RDS but re-reading your question, it seems that you may be trying to apply these logs to RDS rather than having retrieved them from RDS; retrieving logs from RDS is only possible with RDS/MySQL 5.6.  The conclusion, then, is that the server that originated these logs is either logging in MIXED or ROW mode.)

Update: I see what you are doing now.  To summarize the link you posted, you're trying to (1) take a point-in-time snapshot of an existing server with mysqldump, (2) restore that snapshot to an RDS instance, then (3) use mysqlbinlog to play the transactions forward from the binlog to sync the RDS server to your existing production server, and finally (4) migrate your application to RDS.
Bottom line: You are overthinking the nature of the problem and are concerned without cause.  MySQL Server knows how to interpret the output of mysqlbinlog without either the --base64-output or --verbose flags and apply the changes it sees, whether or not the original query SQL is logged because when the query isn't logged, the actual row changes are logged.
When you run mysqlbinlog you see what looks almost like noise.  Example:
#080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F
...
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;

This is a base-64-encoded version of very tightly-packed binary "row images" containing the actual change that needs to be made to the database.  A MySQL server knows how to decode and apply this change.   Conversely, when you decode-rows and verbose, you're asking mysqlbinlog to decode the binary log for human eyeballs.
Leave your server's binlog_format setting as it is.  The binary logs should work without a problem.

But now, forget everything you were planning because literally in the last 24 hours, Amazon has announced a much better way: they have opened up the ability to temporarily connect an RDS instance, as a slave, to a non-RDS master, to allow the RDS instance to be brought into real-time automatically prior to being promoted to master.
This is exactly the solution to what you are trying to accomplish and is much easier and less error prone.

To import data from a MySQL database that is not in Amazon RDS, you can configure replication from that database to an instance of MySQL running in Amazon RDS. The MySQL database you are migrating from can be running either on-premise in your data center, or on an Amazon EC2 instance. The MySQL instance in Amazon RDS must be running either version 5.5.33 or 5.6.13. Using replication to migrate the data reduces downtime.
— http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html

No kidding.
As a DBA, I've been very reluctant to use RDS in my own production networks, preferring to retain full control over all aspects of the server... and by "reluctant," I mean "we didn't."  I finally relented when Amazon made it possible in RDS for MySQL 5.6 to use the RDS instance as master to a non-RDS slave ... and, now, using an RDS master as slave to a legacy server to simplify migration seems like another positive sign for the viability of RDS.


Select any variant of

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c, m
    WHERE c.type = 1
      AND m.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m ON m.type = 1
    WHERE c.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m ON c.type = 1
    WHERE m.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m ON m.type = 1
          AND c.type = 1

    INSERT INTO c (c, m)
    SELECT c.id, m.id
    FROM c
    JOIN m /* ON 1 = 1 */
    WHERE c.type = 1
      AND m.type = 1


Whenever a client disconnects from SQL Server, the database engine rolls-back any open transactions prior to the spid terminating.

This applies to all clients, even a "client" Agent Job running via a linked server.

If a stored procedure running via a linked server times out, the connection to the target server will disconnect, causing all open transactions in that session to roll back.

This answer shows many of the ways an open transaction can be rolled back.


For the same reason a carpenter does not use one tool for his job, companies should not rely on one tool for development of systems. There is nothing wrong with PL/SQL when used for the correct jobs. We use it for ETL and reporting on our warehouses, together with other BI tools. We also use C++ if we need to manipulate huge amounts of data in memory (on the server) and then persist changes or write the results as text or html reports.

For OLTP (you say "production") we use java together with frameworks such as Spring and Hibernate (and JDBC) to make communication with Oracle easier.  You should then investigate your middle tier because you need efficient connection pooling for production - we find that Weblogic is stable for this.  Your architect is correct to mention inserts and simple selects if your OLTP consists of mainly transactional tables, but nowadays "OLTP" systems are complex and hybrid and Oracle has to manage a wide range of DML, concurrency and locking strategies.  If you have the luxury of designing OLTP from scratch, keep it simple and clean.

You can find a full list of development tools here.


What is the significance of the trailing dollar sign `$` on identifiers in SQL Server?

Even if only by convention, what is the rule here regarding this?

There is nothing stopping you from using a $ in your code... generally, though, using reserved values is frowned upon. Most internal items you'll see will use a $, a % or %%,  # or ##. I'd stay away from using all of those in my user code.

  Should I be using this in my own code?

See above.

  Dumping some internal views [...]

There are various other items you'll see such as OPENROWSET or OPENTABLE. I'll save you from another question, no there isn't a way to see that definition.

You can think of Dynamic Management Views (DMVs) as taking a memory structure and making it a rowset... and literally that's what is being done. There is a difference between an internal base table (which holds metadata about the database objects) and DMVs (which are in memory structures).

DMVs will be helpful to you, internal base tables will not be very helpful as you won't find much (if any) documentation on the table itself or the values inside of it.

  Can you describe the convention amongst $, a % or %%, # or ## that is used internally?

No, I would also not worry about it as it makes no actual difference to you.

  When you say "no there isn't a way to see that definition" you mean the definition for sys.sysschobjs$

Depends, do you see OPENTABLE or OPENROWSET in that definition as I stated above? If not, then you can probably see the base tables over a DAC or DAC + Single User Mode connection.

  What would you call sysschobjs$ you say "internal item" is that the most specific name in the Microsoft parlance?

I would call it an internal base metadata table. You could call it whatever you like, just how "automobile" is also called a "car".

  You described DMVs (which I believe I understand), and Base Tables which I also believe I understand, but not internal items that end in $ or how they relate DMVs and Base Tables.

Some will end in $ some won't, some will have #, some won't. Like I said above, you'll find a variety of items used. There isn't a hard and fast rule. Example being you see that table end in $ but there are functions that also use $. Again, at the end of the day it's a name and nothing else.

I believe you're reading into this waaaaaaaaay too much. There is no secret code or handshake or society.


Why is an empty rowset not equivalent to null when assigning a query result to a variable and how best to work around?

This says to me a subquery (which always returns a single value) represents an empty rowset as null in that return value, ...

A subquery used in a context that expects a single, scalar value needs to return a single, scalar value. Such contexts are when used as an expression or with operators that expect a single, scalar value. When no scalar value exists, it returns NULL for precisely the reason you thought: it means "unknown". Think of a subquery in these contexts as being a replacement for a scalar UDF. It will always return a single something (T-SQL does not allow for a return type of void). So, what you have is effectively: select @testB = dbo.Function(6); assuming that "dbo.Function" is defined as:

CREATE FUNCTION dbo.Function (@Input INT)
RETURNS INT
AS
BEGIN
  DECLARE @Temp INT;
  SELECT @Temp = TestTable.[id]
  FROM   (VALUES (5)) TestTable([id])
  WHERE  TestTable.[id] = @Input;

  RETURN @Temp;
END;

More examples:

SELECT 1 AS [one], (SELECT 2 WHERE 1 = 0) AS [nothing];
-- 1   NULL

and:

DECLARE @MoreThanOneRow INT;

SELECT @MoreThanOneRow = (SELECT [object_id] FROM sys.objects);
/*
Msg 512, Level 16, State 1, Line XXXXX
Subquery returned more than 1 value. This is not permitted when the subquery
  follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
*/

and:

DECLARE @MoreThanOneColumn INT;

SELECT @MoreThanOneColumn = (SELECT TOP (1) [object_id], [schema_id] FROM sys.objects);
/*
Msg 116, Level 16, State 1, Line XXXXX
Only one expression can be specified in the select list when the
  subquery is not introduced with EXISTS.
*/

  ... but attempting to select a single column from an empty rowset does not return null. Rather, it kind of says "I have no value, so there is nothing to assign".

I wouldn't think of an empty result set in terms of "I have no value, so there is nothing to assign". Instead, think of it in terms of: an assignment will be performed for every row returned, and no rows returned means that there is no action to take. It might be a subtle difference, but the focus really needs to be on "rows = actions" more so than "returned value is something that can be assigned".

This distinction is not so subtle when:

You are executing a T-SQL scalar UDF in a query and passing in a column. Assuming we are not dealing with Scalar UDF inlining (starting in SQL Server 2019), then scalar UDFs are executed per each row, hence the performance issues associated with them and the impetus for the new feature in SQL Server 2019 to inline UDFs (if they meet the requirements).
You are setting a value like you are showing for select @testA = id from .... The assignment will happen per each and every row returned. And the final value of the variable will be the last row returned. That can be predictable if you use an ORDER BY, or you can live life on the edge and go for unpredictable results by not using an ORDER BY. For example:

DECLARE @Temp INT;

SELECT @Temp = tab.col
FROM   (VALUES (1), (3), (4), (2)) tab(col);

SELECT @Temp AS [NotOrdered];
-- 2

SELECT @Temp = tab.col
FROM   (VALUES (1), (3), (4), (2)) tab(col)
ORDER BY tab.col;

SELECT @Temp AS [Ordered];
-- 4

Another way to look at this is that you are using a result set as a loop:

-- pseudo-code
while (ResultSet.GetNextRow())
{
    set @testA = ResultSet.GetColumnValue("id");
}

If the first call to ResultSet.GetNextRow() returns false (due to no rows in the result set), then the loop is never entered and the assignment never happens.
If there are multiple rows in the result set, then the assignment happens for each and every row returned, and in the order that they are returned.

In fact, this is how string concatenation works when done via result set (please note: as Martin Smith pointed out in this Stack Overflow answer, string concatenation done in this manner is not guaranteed to produce the expected result, even if most of the time it does; however, that does not take away from this method proving my point here):

DECLARE @List NVARCHAR(MAX) = N'**';

SELECT @List = @List + N', ' + QUOTENAME([name])
FROM   sys.schemas
WHERE  1 = 0
ORDER BY [name];

SELECT @List AS [NoChange];
-- **

SELECT @List = @List + N', ' + QUOTENAME([name])
FROM   sys.schemas
ORDER BY [name];

SELECT TRIM(N'*, ' FROM @List) AS [SQL2017orNewer];
--SELECT SUBSTRING(@List, 5, 80000) AS [PreSQL2017];

I even made use of this behavior to generate a complex T-SQL script:

Uppercase All String Columns (and in a Single Query!)


Wrong estimated number of rows using TOP condition

13.83 is the number of rows it thinks it needs to satisfy the 15 rows you're asking for. It guesses that after that it can stop.

Paul White goes into it a lot more in his post Inside Optimizer Row Goals in Depth.

Since he spends a lot of time at this site you'll probably see an extensive answer, going into a lot of detail about why it's 13.83 and not 13.84. ;)

I suggest you to read below blogs

Why Top1 Killed My Performance you'll also see some reasons why having row goals can hurt.
SSIS Tuning tips That Everyone Misses


Thank You for additional information
If it standard and default settings, it not optimal for huge data, so You can start from:

locate my.cnf file (it could be few different locations, depending
from installation)
in mysql run SHOW VARIABLES LIKE 'innodb_file_per_table'; if it OFF -
good to drop ibdata file and re-create it

adjust for start :

innodb_buffer_pool_size = 4G (or 8G)
innodb_log_file_size = 1G
innodb_file_per_table= ON (if it OFF)
innodb_flush_log_at_trx_commit = 0 (because it development machine)
innodb_log_buffer_size = 64M
max_allowed_packet = 128M

It must increase speed of work

if innodb_file_per_table= OFF it mean all You databases in single files and mysql will not release space when You delete and/or optimise tables

with innodb_file_per_table= ON, effect from TRUNCATE/OPTIMISE available momentary and this is important for dev machine.

All above work always and if You decide re-load all dump, and if will go other way.

For already restored:

check in MySQL "SHOW DATABASES", by default it dump and restore in
alphabetical order, so question will be for last in list
for last database, compare table by table with original (if available), but better re-load it completely

I not sure - what inside Your huge dump, so can not give universal recommendations - how to properly split it.


Case insensitive search of CLOBs in Oracle 12

Searching without any kind of index seems to be not a good idea. And an analysis without further details is difficult. But you can add resources to improve your speed.
It seems that the bottleneck of your query is CPU and not the disks. If disk speed is the limitation there would not be a difference between regexp and LIKE.

You can try to parallelize your query. If the work is done by 4 processes instead of one, it is possible that the query response tim is a quarter of the original time.
you can add an additional clob field that contains the content of dataClobfield  in uppercase. this field is used for searching the records you are interested in


Selecting consecutive rows with same values and one that differs

If you are on version 8.x (or above), you can take advantage of window functions like LAG/LEAD. This functions return a value of previous or next next row inside a partition, following an specific order.

Using your data:

SELECT
    userID, tStamp, status,
    CASE WHEN COALESCE(LAG(status) OVER (PARTITION BY userID ORDER BY tStamp), '') = status
         THEN 0 ELSE 1 END AS rst
FROM
   t;

returns:

userID | tStamp              | status | rst
:----- | :------------------ | :----- | --:
Brown  | 2017-10-18 03:20:28 | idle   |   1
Brown  | 2017-10-18 03:25:28 | idle   |   0
Brown  | 2017-10-18 04:00:28 | active |   1
Brown  | 2017-10-18 04:10:28 | active |   0
Brown  | 2017-10-18 04:35:28 | idle   |   1
Brown  | 2017-10-18 04:45:28 | idle   |   0
Jason  | 2017-10-18 03:20:00 | idle   |   1

Notice rst flagged each status change. Now using this news values you can easily find time differences between those rows where rst=1.

WITH ct AS
(
    SELECT
        userID, tStamp, status,
        CASE WHEN COALESCE(LAG(status) OVER (PARTITION BY userID ORDER BY tStamp), '') = status
             THEN 0 ELSE 1 END AS rst
    FROM
       t
)
SELECT
    userID, CONCAT("From ", status, " to ",
                   LEAD(status) OVER (PARTITION BY userID ORDER BY tStamp)) status,
    timestampdiff(MINUTE, tStamp,
                  LEAD(tStamp) OVER (PARTITION BY userID ORDER BY tStamp)) tDiff
FROM
    ct
WHERE
    rst = 1;

userID | status              | tDiff
:----- | :------------------ | ----:
Brown  | From idle to active |    40
Brown  | From active to idle |    35
Brown  | null                |  null
Jason  | null                |  null

db<>fiddle here


Why is this SQL update query returning rows?

This is due to an UPDATE trigger that has a SELECT in it. Modify the trigger so that it doesn't return any rows.


To be honest, for such need, I would not use pivot, I would simply:

select XPK, 'Total_count', First_count + Second_count
from my_table
union
select XPK, 'Total_amount', First_amount + Second_amount
from my_table

If you really want to use pivot you can try:

select XPK, kpi, total
from (
select XPK,  First_count + Second_count as Total_count, First_amount + Second_amount as Total_amount
from my_table
) t
unpivot (total for kpi in(Total_count, Total_amount)) tt

But maybe your problem is more complex than what you described....


If you just want to get the job done and not worry about why it works on test but not on production then the old export/import methods will work. You don't mention an operating system so I assume Windows because that's what I work on.  Something like:

REM from a DOS .bat script located on the server

SET oracle_sid=YourDatabaseName

REM  export the schema
C:\app\YourOraclePath\bin\exp SchemaOwner/SchemaPass@SchemaName \
   file=(C:\Dump\SchemaName01,C:\Dump\SchemaName02,C:\dump\SchemaName03) \
   filesize=1GB log=C:\dump\Logs\SchemaName.log

REM drop and recreate the new user if they have existing objects
REM otherwise comment out this line

sqlplus /nolog @C:\Dump\DropRecreateUsers.sql

imp NewSchemaOwner/NewSchemaOwnerPassword \
  LOG=C:\dump\logs\importNewSchemaOwner.log file=SchemaOwner01.dmp \
  fromuser=SchemaOwner touser=NewSchemaOwner

This does not address your error using the data pump which is likely to be connected to the difference between test and production.  Nor does it address the wisdom of importing data sets into production from production.  If this operation fails what are the consequences?


How to use merge hints to isolate complex queries in SQL Server

If you use a multi-statement UDF, then your inner select is executed exactly once for each outer row. The multi-statement UDF is treated as a black box: the execution plan will now show access to the objects used in your complex view.

On the other hand, a subquery and/or an inline UDF is flattened out by the optimizer. When this is the case, the execution plan will include access to the objects used in your complex view.


What is the best way to store large lists of user statistics?

As others have mentioned, I'd look to normalize the data in your UserStatistics table. You could do something stupid simple as a generic Likes table and a Follows table (don't take my poor names, I'm sure you can come up with better ones :).
The Likes table would have its own primary key, and a foreign key field referencing user_id, and it could have a column called like_id and a column called like_type. The like_id would store one row per ID from the user_song_likes, user_album_likes, and user_artist_likes columns of your previous schema, and the like_type would represent if it's of type song, album, or artist.
You could then implement the same idea for your Follows table (e.g. store one row per ID of each follower and following in a generic follow_id column with a follow_type column to determine which is which). Using a more normalized schema (even though this one is generic to keep things simple) is a more relational form of storing your data and will be more efficient for querying.


Please enlighten me about FILESTREAM and BLOB files

Based on the whitepaper written by Paul Randal -

BLOBs smaller than 256 kilobytes (KB) (such as widget icons) are better stored inside a database.
BLOBs larger than 1 megabyte (MB) are best stored outside the database.
For those sized between 256 KB and 1 MB, the more efficient storage solution depends on the read vs. write ratio of the data, and the rate of “overwrite”. Storing BLOB data solely within the database (e.g., using the varbinary (max) data type) is limited to 2 gigabytes (GB) per BLOB.

Answering your questions :

  What's the difference between inserting the .PDF (for example) using just a VARBINARY(MAX) column in the database, and storing this .PDF in a FILESTREAM database. I know with the BLOB varbinary(max) the .PDF is INSIDE the database. If I physically delete the .PDF, can I still restore it using the value stored inside the varbinary(MAX) column right?

Once you insert the pdf into the database as SINGLE_BLOB, you dont need the original .pdf file. You can export out if you need it at later time.

  When I insert this .PDF file in a filestream, I can see it inside the folder (the one created for the filegroup), if I insert 100 files, I can see 100 files inside the filestream folder, but when I run DELETE FROM and delete 100 rows in the database, I can still see these files in the filestream folder. Why does this happen? I thought that, to maintain consistency, these files would be deleted too.

File Streams uses a garbage collection process to clean up files that are not longer needed.   A system task wakes up periodically (~10 seconds or so) and checks for file stream garbage collection needs. Read "Why don't all the files get collected?"

  DANGER:  Deleting a file directly from the file stream container is considered database corruption and dbcc checkdb will report corruption errors.  These files are linked to the database in a transactional way and the deletion of a file is the same as a damaged page within the database.

-

  With both cases, after inserting the .PDF file inside the BLOB VARBINARY(MAX) or FILESTREAM, do I still need the original file?

For BLOB Varbinary(max) --> You can delete the original file. For Filestream, do not delete the file from the filestream folder. If the original file is not in the same folder than the filestream folder, then you can delete it.

References :

Excellent whitepaper (pdf) : To BLOB or Not To BLOB:
Large Object Storage in a Database or a Filesystem?
Free book from redgate : The Art of
SQL Server FILESTREAM


Make index work on join to table variable

Assuming your variables are always NOT NULL

WHERE  ( ReceiveDateTime < @DocOldestTreshold
          OR ( ReceiveDateTime < @DocNewestTreshold
               AND ReceiveDateTime < t.Treshold ) )

Is equivalent to

WHERE  ReceiveDateTime < @DocOldestTreshold
        OR ReceiveDateTime < LEAST(@DocNewestTreshold, t.Treshold)

Which is equivalent to

WHERE  ReceiveDateTime < GREATEST(@DocOldestTreshold, LEAST(@DocNewestTreshold, t.Treshold))

So if you have this index

CREATE INDEX ix
  ON [DocumentInfo](StatusId, [ReceiveDateTime])

And the following query

SELECT di.DocumentId
FROM   @Tresholds T
       CROSS APPLY (SELECT IIF(@DocNewestTreshold < t.Treshold, @DocNewestTreshold, t.Treshold)) CA1(Least)
       CROSS APPLY (SELECT IIF(@DocOldestTreshold > CA1.Least, @DocOldestTreshold, CA1.Least)) CA2(Target)
       JOIN [DocumentInfo] di
         ON di.StatusId = t.DocumentStatusId
            AND di.ReceiveDateTime < CA2.Target
            AND NOT EXISTS (SELECT *
                            FROM   [doc].[document] d (NOLOCK)
                            WHERE  d.ParentId = di.DocumentId)

It can do an equality seek on StatusId and a range seek on ReceiveDateTime


Is it Okay for mysql to hit 59% without no query running in my site?

With nothing running, mysqld will use 0% of CPU, therefore you must have something running. Enable general log, that will show you exactly what it's doing.

And 1GB isn't a particularly big database.


What are the possible 'types' available in SQL Server as mentioned in the 'type' column in the sys.master_files view?

You shouldn't mix file extensions with file types. You have to differentiate between file extensions and what the files actually contain.

Create Database (non-standard)

It is possible to create a database that has *.txt as the file extension for the Data files ("ROWS Data" type) which are otherwise created with an *.mdf or *.ndf extension.

It is equally possible to create a database that has a *.txt extension for the TransactionLog file ("Log" type) which is otherwise created with an *.ldf file extension.

Script to create a non-standard database

CREATE DATABASE [File_Types]
    CONTAINMENT = NONE
    ON  PRIMARY
   ( NAME = N'File_Types', FILENAME = N'C:\SQL\SQL_DATA\File_Types.txt' , SIZE = 5120KB , FILEGROWTH = 1024KB ), -- normally *.mdf
   ( NAME = N'File_Types_01', FILENAME = N'C:\SQL\SQL_DATA\File_Types_01.txt' , SIZE = 5120KB , FILEGROWTH = 1024KB ), -- normally *.ndf
    FILEGROUP [File_Types_FS_FG] CONTAINS FILESTREAM
   ( NAME = N'File_Types_FS', FILENAME = N'C:\SQL\FILESTREAM\File_Types_FS' ) -- no extension; is a directory where BLOBs are stored
   LOG ON
   ( NAME = N'File_Types_log', FILENAME = N'C:\SQL\SQL_LOGS\File_Types_Log.txt' , SIZE = 3072KB , FILEGROWTH = 10%) -- normally *.ldf
GO
USE [File_Types]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [File_Types] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

This will create a database with three files which all have the *.txt extension.

Query sys.master_files

SELECT database_id, FILE_ID, TYPE, type_desc, NAME, mf.physical_name
  FROM sys.master_files AS mf WHERE database_id = 13

Output

database_id FILE_ID type_desc   NAME            physical_name
13          1       ROWS        File_Types      C:\SQL\SQL_DATA\File_Types.txt
13          2       LOG         File_Types_log  C:\SQL\SQL_LOGS\File_Types_Log.txt
13          3       ROWS        File_Types_01   C:\SQL\SQL_DATA\File_Types_01.txt
13      65537   2   FILESTREAM  File_Types_FS   C:\SQL\FILESTREAM\File_Types_FS

To answer your questions

"DATA Rows" will always be type 1. A file with an *.mdf extension can be of any type.
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data. (see: FILESTREAM Overview)
Tables are stored in pages and extents inside the "Data ROWS" database files. An explanation of how data is stored can be found here: Understanding Pages and Extents and Managing Space Used by Objects.


Google Datastore model User and Messages efficently

Your second approach seem to be the best. Having a Message entity with key relations to sender/reciever.

As for you deleting a user dilema, you should consider not removing the entity when a user is deleted as this would break your db consistency. You should probably just have a logic deletion (eg a boolean value changed when a user is deleted), that way, messages sent to/from that user will stll make sense for other users.


All queries are so slow on mysql NDB cluster

Redundant index. A PRIMARY KEY is an index, so there is not need for the redundant KEY no(no).  (DROPping this will speed up things a little.)

Agent. Where's the agent in the schema?  Is it a varchar?  or enum?  or normalized SMALLINT UNSIGNED.  The last would save space, hence improve speed, but would complicate the insert.

Better index. Replace INDEX(ip) with INDEX(ip, agent).  This will speed up the given SELECT some.  (But I suspect the agent is usually the same for a given ip.)

Country code There are very standard 2-letter country codes; use them instead of the bulky varchar(50).

Batching. For now for the real speedup...  Batch the inserts.  Collect them in a table, swap tables, INSERT..SELECT, etc.  Details here.  That technique is self-adjusting; so if things get slower, they actions also get more efficient.  That link also explains how to efficiently normalize.

Tuning. IndexMemory=128M seems unreasonably small.

Back to InnoDB?

  The select query also disappointed me, it takes almost 100 times longer than normal InnoDB.

The techniques I have mentioned are well tuned for InnoDB; I have less experience with NDB Cluster.  Maybe you should go back to InnoDB and we can work on making that better?  innodb_buffer_pool_size should be about 70% of RAM.

IPv6. A side note:  IPv4 needs only VARCHAR(15); IPv6 needs (39).  And ip addresses may as well be CHARACTER SET ascii, not utf8.  (No significant performance benefit here.)


I am not exactly sure what you are asking for in this post.

Here is what I came up with

CREATE TABLE device_table
(
    id INT PRIMARY KEY NOT NULL auto_increment
  , defice_id VARCHAR(20)
  , used_at DATETIME
  , app_id VARCHAR(30)
  , duration INT
);

INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'chrome', 30);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-19', 'chrome', 1);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-2', '2018-07-19', 'chrome', 2);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-2', '2018-07-20', 'facebook', 100);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'chrome', 20);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-3', '2018-07-20', 'chrome', 10);
INSERT INTO device_table(device_id, used_at, app_id, duration) VALUES('device-1', '2018-07-20', 'facebook', 5);

SELECT device_id
     , app_id
     , CONCAT('duration (', CAST(SUM(duration) AS CHAR), ')')
FROM device_table
-- remove filter to include everything else
WHERE device_id = 'device-1'
-- if you want top ten for "device-1" then comment out the date filter
AND   used_at >= '2018-07-18'
GROUP BY device_id
       , app_id
-- and uncomment the following line
--LIMIT 10
;

Could you explain your question a little more so the rest of us can understand what you are trying to accomplish here?

If you want the top 2 of any and all devices then use this:

SELECT device_id
     , app_id
     , CONCAT('duration (', CAST(sum(duration) AS CHAR), ')')
FROM device_table
GROUP BY device_id
       , app_id
ORDER BY duration DESC
LIMIT 2 -- change this to how many you want
;


Is it possible to store Always Encrypted master key an on-premises key vault?

It's always safe to store the Keys in Centralize key vault rather than any on-premises.

For your case, you can try to create your own Custom Keystore provider


The most important thing when doing a large update or delete is to avoid the transaction log growing out of control. To allow log reuse & avoid bloating to Tlog (Transaction log truncation)

In full recovery mode - take log backups
In Simple recovery mode - do checkpoints.

Since you are using Full recovery, your delete logic should be

begin
 begin tran
   -- delete using batches
   -- carefully choose batch size. SQL Server will do a lock escalation
   -- for batch greater than 5000. (https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx)
 commit tran / rollback tran
  -- since you are using Full recovery, take log backups
  -- with CHECKPOINT SQL Server still cannot reuse those portions of the log until you perform a log backup.
end

I use waitfor delay to esp. avoid prolonged blocking.

Refer to this gold mine blog post - Break large delete operations into chunks by Aaron Bertrand. Also, Take Care When Scripting Batches is very useful blog post.


What is the instance name of the box I'm logged into?

If the DB is running, you can look for the pmon process:

ps auxww | grep pmon | grep -v grep

You'll see one pmon process for each database that is up. For example ora_pmon_YOURDB, where YOURDB is the instance name.

You can also ask the listener which services it is serving connections for, with:

lsnrctl status


Database schema - performance v correctness information

This is a "materialized view" and triggers are used often to implement them. It is duplication of data, and an opportunity for data inconsistency, but if needed and well implemented, they can improve performance a lot.

But I don't see what are you asking.


Couldn't install SQL Server 2012 on machine with Windows 7 SP1, VS 2010 SP1

Here's the troubleshooting options I'd try:

Disable antivirus. Man, I hate saying this, but I've seen issues where antivirus took too long to scan a file, and it caused installation problems.
Re-download the installation media. I know you've seen other users run this same setup and it's worked, but I've seen enough corrupt downloads that I would try this anyway. It doesn't cost you anything.
If D is a network share (a mapped drive), copy the installation files locally before you start.


I think log shipping is probably a good approach but I'm concerned I won't be able to compress the tlogs

Since you are using logshipping on SQL Server 2012, you can compress your transaction log backups

  (they'll be big).

Take frequent log backup, so that the size of the T-logs is manageable and you are using backup compression (see above screenshot). The minimum is 1 min.

  My question is, can I manually set up log-shipping, essentially leaving the primary untouched and just restore 24 hours worth of transactions on the secondary each day - whilst still being able to read from the secondary during the day?

Yes you can do what you are saying. You can delay the restoration of log backups on the secondary server. So, just have to copy job to copy the log backups from primary to secondary and depending on your need and business requirement, you can delay the log restoration.

Note that when T-logs are being restored, users will be disconnected.

EDIT:

You can put inplace of allowing only COPY_ONLY log backups so that it does not break your log backup chain. I have written it in my answer here. Also, remember that a full backup will never break a log chain. It can mess up a differential backup though. So, its a good practice to have COPY_ONLY backups!


Creating plan guide for query called through sp_executesql

For those stumbling upon this question trying to create a plan guide, the syntax I had originally is correct. The reason it wasn't working (I suspect - I can't find any confirmation in the documentation) is that the view uses a CTE for recursion. Evidently this disqualifies it from plan guide use.

My original problem was that the recursive view performed poorly when a SELECT statement was issued via sp_executesql with parameters (the client is an Access database).

I finally came across this older question on Stack Overflow where somebody is having basically the same problem:

https://stackoverflow.com/questions/4226035/why-does-a-query-slow-down-drastically-if-in-the-where-clause-a-constant-is-repl

I was kind of beginning to wonder if I would need to trick/help the query optimizer by pushing the recursion out to a user-defined function, and this confirmed the suspicion. I moved all the CTEs from the view into an inline UDF that uses the @person_id parameter directly in the anchor of the recursion, and now it's pleasantly fast even with sp_executesql.

So, not the solution I originally thought I was going to need, but I'll take it. (It's probably more straight-forward this way, too. I don't have to worry about attaching a plan guide to every subtle variation of the query that Access might construct.)


Freeing disk space after dropped database

Try sudo lsof| grep deleted and check if any PostgreSQL process appears. This command looks for files which have been deleted but its file descriptors are still open by any process. Another side effect is that df -h and du -sh / differs. This is because du looks at the file system and sum up the size of all files, and df looks at the physical device.

I have just had an issue with a database which didn't free any space after a DROP table and that was the cause.

The only solution I know is to restart the database. Maybe you can try to send a reload (SIGHUP).


First, yes it is possible to make sure changing a column in one table gets updated in all related tables. The concept is defined as a Foreign Key. In MySQL, the tables must be using the InnoDB storage engine. Here is the link to the 5.5 documentation on implementing foreign keys.

The basic gist is your child table (the table that relies on a foreign table's column value) needs to have this done to it:

ALTER TABLE bar ADD CONSTRAINT fk_foo FOREIGN KEY foo_id
 REFERENCES foo (foo_id) ON DELETE CASCADE ON UPDATE CASCADE

Where foo_id exists in both the child table bar and the parent table foo. The foo_id column must be defined exactly the same in both tables (same size, type and unsigned/signed status).

Secondly, I would highly suggest not basing your order of visits on the auto-increment value.  Auto-increment columns should have no meaning to your data.

If you have a date column, you should be able to get the order based on that column + the visitor, even if you add it manually at a later date. This would remove the 'need' of updating the autoincrement value.