Backup Strategy for Transaction Log. Tail or Standard Log Backup?

The real problem is that you need to know when the application is shut down, and users are out of the database. If there is a communication issue with knowing "when Management pushes the button," then perhaps you could monitor sys.dm_exec_sessions and/or sys.dm_exec_requests to determine when user traffic has subsided. Once you see that users are no longer actively in the system, you can take your final log backup, and then switch the database into SIMPLE recovery.

If you take a tail log backup (eg, BACKUP LOG...WITH NORECOVERY), you'll kick users out of the database when the DB flips into a recovering state. However, you'll still need to recover the database so that you can change it to SIMPLE and run your upgrade. If users are still in the application when you recover the database, you'll have the same possibility of lost transactions.

Once you know that all users are out of the database, a simple transaction log backup should suffice to capture all transactions.

Additionally, you can take a differential backup on the database even when the database is in SIMPLE recovery mode. Differential backups don't give the same point-in-time recovery as a log backup, but if you are truly looking for a recovery point of "immediately before I start my maintenance," then using a differential might be your best option. This option has two main benefits:

It achieves the goal of having an easy restore point that includes all changes to the database up to a point just prior to the maintenance.
Recovering to that restore point requires restoring only the full + differential backups. This will likely reduce the time & effort necessary to restore, compared to full + N logs.

Compared to taking one final log backup, the differential will take longer to run, and will require more disk space on your backup share. The exact time & space will vary depending on how much data has changed since the last full backup, so that is something you will need to evaluate on your own.

Additional information on differential backups from Microsoft:
https://technet.microsoft.com/en-us/library/ms191180(v=sql.105).aspx


Storing undetermined amounts of user information (MySQL)

You can use the same table with another PK(Primary Key) and just one thing field.

Example:

PK          PK
UID         thing_id  thing
71420150404 0000001   First thing
71420150404 0000002   Second thing

You can make a trigger in action BEFORE UPDATE so when you add a new row, the trigger make the job of increment the thing_id field, like SET NEW.thing_id=(SELECT MAX(thing_id)+1 FROM db1.Things WHERE UID=NEW.UID); and that's all.

With this you can add N things for the same UID.

EDIT:

I've added 2 field to your current design, thing_id and thing_date.

TEST:

mysql> # New Table structure
mysql> CREATE TABLE `test`.`Things` (
    ->   `UID` BIGINT NOT NULL,
    ->   `thing_id` INT(7) ZEROFILL UNSIGNED NOT NULL,
    ->   `thing` VARCHAR(255) NULL,
    ->   `thing_date` DATETIME DEFAULT NULL,
    ->   PRIMARY KEY (`UID`, `thing_id`));
Query OK, 0 rows affected (0.01 sec)

mysql> # First SELECT
mysql> SELECT * FROM test.Things;
Empty set (0.00 sec)

mysql> # First message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="Hello!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     IFNULL(MAX(thing_id),0)+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> # SECOND SELECT
mysql> SELECT * FROM test.Things;
+-------------+----------+--------+---------------------+
| UID         | thing_id | thing  | thing_date          |
+-------------+----------+--------+---------------------+
| 71420150404 |  0000001 | Hello! | 2015-10-06 15:00:20 |
+-------------+----------+--------+---------------------+
1 row in set (0.00 sec)

mysql> # Second message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="Hello2!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     MAX(IFNULL(thing_id,0))+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> # Third SELECT
mysql> SELECT * FROM test.Things;
+-------------+----------+---------+---------------------+
| UID         | thing_id | thing   | thing_date          |
+-------------+----------+---------+---------------------+
| 71420150404 |  0000001 | Hello!  | 2015-10-06 15:00:20 |
| 71420150404 |  0000002 | Hello2! | 2015-10-06 15:00:20 |
+-------------+----------+---------+---------------------+
2 rows in set (0.00 sec)

mysql> # Third message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="Did you see there\'s 3 messages?!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     MAX(IFNULL(thing_id,0))+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> # Last SELECT
mysql> SELECT * FROM test.Things;
+-------------+----------+----------------------------------+---------------------+
| UID         | thing_id | thing                            | thing_date          |
+-------------+----------+----------------------------------+---------------------+
| 71420150404 |  0000001 | Hello!                           | 2015-10-06 15:00:20 |
| 71420150404 |  0000002 | Hello2!                          | 2015-10-06 15:00:20 |
| 71420150404 |  0000003 | Did you see there\'s 3 messages?! | 2015-10-06 15:00:20 |
+-------------+----------+----------------------------------+---------------------+
3 rows in set (0.00 sec)

mysql> # fourth message
mysql> SET @UID="71420150404";
Query OK, 0 rows affected (0.00 sec)

mysql> SET @MSG="4th!";
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.Things(UID,thing_id,thing,thing_date)
    -> SELECT
    -> @UID,
    ->     MAX(IFNULL(thing_id,0))+1,
    ->     @MSG,
    ->     NOW()
    -> FROM test.Things
    -> WHERE UID=@UID
    -> ON DUPLICATE KEY UPDATE thing=@MSG;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test.Things;
+-------------+----------+----------------------------------+---------------------+
| UID         | thing_id | thing                            | thing_date          |
+-------------+----------+----------------------------------+---------------------+
| 71420150404 |  0000001 | Hello!                           | 2015-10-06 15:00:20 |
| 71420150404 |  0000002 | Hello2!                          | 2015-10-06 15:00:20 |
| 71420150404 |  0000003 | Did you see there\'s 3 messages?! | 2015-10-06 15:00:20 |
| 71420150404 |  0000004 | 4th!                             | 2015-10-06 15:06:01 |
+-------------+----------+----------------------------------+---------------------+
4 rows in set (0.00 sec)

mysql>

Every @ is a user defined variable, change them for your Php variables and everything work just the same.

YOUR QUERY:

INSERT INTO test.Things(UID,thing_id,thing,thing_date)
SELECT
    :uid,
    MAX(IFNULL(thing_id,0))+1,
    :thing1,
    NOW()
FROM test.Things
WHERE UID=:uid
ON DUPLICATE KEY UPDATE thing=:thing1,thing_date=NOW();


Connecting dimension tables in a multi-fact star schema database warehouse?

Having multiple fact tables in a model is pretty common and is sometimes referred as a Fact Constellation. The shared dimensions are referred as Conformed Dimensions.
As @bbaird said in the comment there shouldn't be a direct one-to-many relationship between dim_a (hotel_rooms) and dim_d (customers) as this relationship should be modeled through a fact table (booking, reservation, stay, ...).
Relationships between dimensions are mainly used to drill-down from one level to another (for instance over a geographical area or as you modeled it for the time dimension). A model with dimensions linked like that is referred as a Snowflake. It is often also possible to flatten these hierarchical levels into a single dimension which might add complexity to maintain but provide better performance on some RDBMS.


MongoDB scripts triggered by cron - how to achieve reasonable password security

First.. It's better to use --eval='command' and not echo a string to mongo... What comes to that password, you could use -p $(cat /etc/secret/password) and files chmod is like 0400


Generally, CSVs are the smallest file size for non-binary (text) data. XML adds a great deal of xml codes that bloat the file size. So CSVs tend to import faster. But XML importers can handle exception cases, etc., with more complexity.

In my career, I've worked with instrument imports at four different kinds of laboratories. Where there was no direct LIMS (Laboratory Information Management System)-to-instrument interface system in place, we found that delimited text files are the fastest to write working import scripts around.

CSV is a comma-delimited text file. If your input data will include commas, then using a different delimiter may make more sense, like a pipe character for example: | A CSV file or pipe-delimited file generally follows the rule that each row/line of text from column 1 to the end-of-line carriage return represents one row of sample data in your database. Then within that row, each column of data in your database is separated from other columns by your delimiter character(s), which could be commas or pipes or anything you want to use.

Text files are great as long as the data is consistent over time.


We do something similar with client id. Yes it can improve performance if you don't need to go through all those intervening tables in every query. However, and it's an important however, this is best done only if you are using a surrogate key that never changes. Otherwise a change of the project id could require a cascade of updates that affect every table and lockup the system. I suspect project_id is as unlikely to change as client Id (client name, now that's another story) and so you might be fine. But please do consider if you will have updates to the field you denormalize, Also it is critical to set up a way to make sure the tables with the denormalized fields cannot get out of synch  with the main table (PK/FK relationships are good for this and you might need cascading updates set (although I personally prefer not to use them if I can help it).)


Where can I find SQL Developer Database examples (scripts) to add data to practice?

The example schemas are now on Github.
https://github.com/oracle/db-sample-schemas


Make sure that you are able to connect to Remote MySQL server (Online server) from a Slave server machine.
You can verify by using telnet command

# telnet <online server IP><space><MySQL port>

Now follow the replication procedures mentioned here:

http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html

For Database synchronization refer existing answer here:

https://stackoverflow.com/questions/7946540/how-do-i-sync-2-mysql-databases


Filling in Dates - Postgres/Redshift SQL

In Postgres (which you explicitly tagged) you can do it like this. But I suspect you don't really use Postgres and the following is not supported by Redshift.
select d.id,
       d.activity_date,
       count(distinct e.user_id) as daily_count
from (
  select distinct e1.id, g.dt::date as activity_date
  from events e1
    cross join generate_series(current_date - 90, current_date - 1, interval '1 day') as g(dt)
  where e1.activity_date >= current_date - 90
    and e1.activity_date <= current_date - 1
) d
  left join events e
    on e.activity_date = d.activity_date
   and e.id = d.id
   and e.user_id <> 0
   and e.name = 'visited site'
group by d.id, d.activity_date

The query first creates a list of all IDs and dates in the desired interval. This is necessary because you also want to group by the id column. Then a left join is used to bring in the events and do the aggregation.
The WHERE clause in the derived table (inner query) means that only IDs are used, that have at least one event in the desired range. If you need all IDs regardless if there was an event or not in that time frame, you either need to pull them from a different table, or remove the WHERE clause of the inner query.


Execution plan contains a 'sort' even though the data is sorted in index

Your query accesses 10 partitions and you are searching a 10 month range so my guess would be that it is partitioned on month of DateRecorded.

I can reproduce your plan with the sort with the below.

CREATE PARTITION FUNCTION pf1 (DATE) AS RANGE RIGHT FOR VALUES (
'2017-01-01',
'2017-02-01',
'2017-03-01',
'2017-04-01',
'2017-05-01',
'2017-06-01',
'2017-07-01',
'2017-08-01',
'2017-09-01',
'2017-10-01',
'2017-11-01' );

CREATE PARTITION SCHEME ps1 AS PARTITION pf1 ALL TO ([Primary]);

CREATE TABLE MachineryReading
  (
     MachineryId  INT,
     DateRecorded DATE,
     Latitude     FLOAT,
     Longitude    FLOAT,
     FixStatus    INT
  )
ON ps1(DateRecorded)

CREATE CLUSTERED INDEX [IX_MachineryId_DateRecorded]
  ON MachineryReading (MachineryId, DateRecorded)

but technically a sort could be avoided if you could get a plan that processed the partitions in order and just concatenated one ordered result to the next.

If you are happy to assume that the partition numbers will be in order of value (I don't know if this is actually guaranteed but it seems to be the case even after partition splits) then adding a leading column to the sort of the partition number achieves this

SELECT DateRecorded,
       Latitude,
       Longitude
FROM   MachineryReading
WHERE  MachineryId = 2127
       AND DateRecorded > '2017-01-10'
       AND DateRecorded < '2017-10-16'
       AND FixStatus >= 2
ORDER  BY $PARTITION.pf1(DateRecorded),
          MachineryId, --Not really needed as this is a constant 2127
          DateRecorded


Has those servers been on that order during their uptime? It looks like that current 2nd secondary has been primary earlier during time what this replica set has been up. Because mongod memory consumption grow (until it reach it's maximum) when it reads data/indexes from disk. (and of course when you insert documents)

At start, mongod process eats only little memory, but during time when it serve clients, it will consume all available free memory, eventually! Because secodary nodes don't (normally) server/receive queries, their memory consumption grows much slower than primary.


Having values or some state in a database

There is already a mechanism in SQL to flag missing information. It is NULL. It should be considered a flag indicating meta data about the corresponding column rather than a value of that column itself.

If NULL is too blunt & you need to distinguish various reasons for data being missing you can add your own flags. These will not have built-in support, of course. You will have to implement them as additional columns within the table and check them explicitly in code. You will have to think through the truth tables for your new flags so out handle them consistently in predicates and logic.

If you have to hold customer-specific prices then you will need a table with a customer key in it. Something like:

PriceID Int, Primary Key, not null
CustomerID Int, Primary Key, not null
AveragePrice Decimal, not null
MinPrice Decimal, not null
MaxPrice Decimal, not null


psql: id SERIAL PRIMARY KEY not starting with 1?

The table should be
create table cali (
    id serial primary key,
    alk_from bigint,
    alk_to bigint,
    and_from int,
    and_to int
);

And do
\COPY cali (alk_from, alk_to, and_from, and_to)
FROM '/home/.../data/output/id_cali.csv'
(FORMAT CSV);

So that it knows to not insert into the id serial column. The serial type is not a true type. From the manual:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases)
Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator


SQL Server 2016 replication between Windows 10 and Windows Server 2012

https://msdn.microsoft.com/en-GB/library/ms143506.aspx shows SQL 2016 Dev as supported on Win10 Home, and I don't know of any explicit feature limitations--though you are right in general that you're limited to what the OS supports.

Have you tried to set up replication using the built-in service logins? I believe the instructions around adding Windows logins are for security best practice, which may not be an issue for a dev environment.


Formatting the output of SQL Server query

I have put your results into one table and then adapted your query. I've taken out the joins and used GROUP BY and PIVOT you will need to put the joins back in to make it work for you.

select name,
   [Projected Status]=max([Projected Status]),
   [Projected Sp Status]=max([Projected Sp Status]),
   [Day 1 Status]=max([Day 1 Status]),
   [Day 1 Sp Status]=max([Day 1 Sp Status]),
   [%Diff]=cast(max([Day 1 Status])as float) / cast(max([Projected Status]) as float)*100
from
(
    select name,
           element,
           value,
           row_number() over(partition by [element] order by [element]) rn
    from tableX
) t
pivot
(
    sum(value)
    for [element] in ([Projected Status], [Projected Sp Status],[Day 1 Status],[Day 1 Sp Status])
) piv

group by name

SQL Fiddle Demo


Store row count or use query to calculate on Primary Key

Redundant information in a database is a no-no.

If you have an index (such as the PRIMARY KEY) on user_id and if there is not thousands of rows for a user, the computing it on the fly is the way to go.


Connecting to a SQL Server database from a Flash program

There are MANY things you will need to consider if you are planning on making your application publicly accessible, some of the common ones are:

If your DB is on your LAN, you need to configure NAT and port forwarding correctly to make it visible on the internet
Permissions need to be set correctly so that your app can access the DB, but random people cannot
Not a very good idea to have an app with its main DB residing on a residential machine
DNS,etc setup since you need to tell the app how to find your computer on the internet


Slow select Queries on Log shipping standby server

Statistics in read only databases.

TL;DR

In Read only databases, regular permanent statistics can be leveraged to satisfy query plans. If sql server needs new statistics for the read only db  or these permanent statistics in the database have become stale, then temporary statistics can be created / updated. These statistics reside in TempDB and are managed by SQL Server (you can only drop them).

There are two kinds of temporary statistics, these that get created due to missing statistics and those that get 'updated'.

The creation of temporary statistics can be removed by adding the statistics manually on the primary database, or generating the estimated execution plans of the reporting queries (also on the primary instance/db). More on that further in this answer.

Permanent statistics can be updated and 'transformed' to temporary statistics on a read only database.

The temporary stat updates can be resolved by either updating your statistics on the primary db more often or disabling the auto stat updates on the read only db after log restores are done.

To disable the auto update stats you could execute this on the read only database: ALTER DATABASE [Database] SET AUTO_UPDATE_STATISTICS OFF; This will stop the updates of temporary statistics on this Read only database.

Another important part regarding Restores to a standby database & temporary statistics is that when applying log backups, the temporary statistics get  updated again, even though they still  exist in sys.stats.

In your case explaining the daily issues with Statman queries (recalculating the temp statistics each day if they need to be created / updated).

Norecovery --> Standby --> Norecovery ... removes temporary statistics

Another interesting part regarding temporary statistics is that they will be gone when the db state changes to restoring using RESTORE DATABASE ... WITH NORECOVERY.

use MASTER
GO
RESTORE DATABASE [ReadOnly2] with NORECOVERY
RESTORE DATABASE [ReadOnly2] WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

Effectively flushing the temporary statistics on all objects

SELECT * From sys.stats where is_temporary = 1;

And recalculating the same 2 stats between each state change + test query running.

Both of these permanent statistics show up in the xml of our execution plan

   <StatisticsInfo Database="[ReadOnly2]" Schema="[dbo]" Table="[Bla]" Statistics="[IX_Bla_indexedval]" ModificationCount="12000000" SamplingPercent="15.8812" LastUpdate="2019-06-12T10:52:32.25" />
      <StatisticsInfo Database="[ReadOnly2]" Schema="[dbo]" Table="[Bla]" Statistics="[PK__Bla__3214EC075017BD54]" ModificationCount="12000000" SamplingPercent="15.2345" LastUpdate="2019-06-12T10:52:35.34" />

With the modificationcount, samplingpercent & lastupdate changed after executing the query again with the temporary statistics 'updated'.

ModificationCount="0" SamplingPercent="5.71018" LastUpdate="2019-06-13T11:32:36.5"

Creation of temporary statistics

Regular, non temporary statistics do not get updated / you cannot update (not even temporary) statistics  on read only databases.

What you are seeing on the reporting instance is the creation / 'updating' of temporary statistics.

These statistics reside in TempDB & SQL Server Creates & Updates them.

Replicating the behaviour

I was able to replicate the behaviour of temporary statistics creation on a table with 100M rows in a read only database (Addendum #1)

With the troublesome StatMan Queries you mentioned.

SELECT StatMan([SC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], step_direction([SC0]) over (order by NULL) AS [SB0000]  FROM (SELECT [NonIndexedVal] AS [SC0] FROM [dbo].[Bla] TABLESAMPLE SYSTEM (7.707678e-001 PERCENT) WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 16)

Maxdop 16 (max cores) due to me having MAXDOP set as 0 on my test machine, YMMV

Restarting the instance

When I restart the instance I see the same behaviour, showing that these are in fact, temporary statistics. Some Q&A on temporary statistics can be found here.

Now what we are seeing is the creation of temporary statistics and they get created before the query runs.

For big tables, they do note that sample rate is still applied to temporary statistics.

  Other point to note is that statistics created as part of auto-stats
  use data sampling so the creation of these statistics is fast and does
  not depend on the size of the table

Source

How could you resolve the creation of the statistics when sql server restarts / the database is restored?

If it is possible to do so, you could script them out and create them on the main 'primary' database.

Finding the temporary statistics

SELECT OBJECT_ID, name, auto_created,
user_created, is_temporary
FROM sys.stats
WHERE is_temporary = 1;

Scripting the statistics

Without T-SQL
Or by using the T-SQL answer on the question How to script statistics in Sql Server? (using T-SQL) By Martin Smith

Which could solve your main issue with the statistics being created time and time again.

Another idea if the issue is due to temp stats being created & you cannot script them out correctly, could be creating the estimated execution plans for the reporting queries on the primary database. This should create the statistics needed when auto create statistics = on.

Temporary statistic updates

The other problem that could arise are stale permanent statistics.
As noted in this aforementioned blog, stale permanent statistics can be updated and set to is_temporary=1.

This means that permanent statistics on a read only database can become temporary statistics until the instance is restarted. When you update the statistics on the primary it should get carried over to the secondary when the log is applied.

Asynchronous temporary statistic updates

We see the asynchronous statistic updates also working for these temporary statistics!

After running addendum #1, we run the next snippet:

   USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_WRITE;

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS OFF

USE [ReadOnly]
GO
INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal)
SELECT TOP(10000000) --10M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS ON

USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_ONLY;
SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

The above SELECT runs instantly.

After running the query, the aftermath is shown.

The temp statistic updates run after query execution.

SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [Indexedval] AS [SC0] FROM [dbo].[Bla] WITH (READUNCOMMITTED)  ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL  OPTION (MAXDOP 1)

Disabling auto update statistics on read only databases

You could disable the updating of permanent statistics to temporary statistics by running the next statement, you can change this setting on a read only database and it will still work.

ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS OFF;

Solution for the temporary stat upgrades.

  Also, will the update stats done on primary (once a week) be restored
  during LS restore?

Updating your stats will be brought over to the secondary, updating them more where possible will result in less stale statistics and less stale permanent statistics being converted to temporary statistics.

Looking into both of these would be my go to in tackling this problem.

Disabling temporary statistics by using a traceflag

When massively enabling traceflags to help a colleague and digging deeper between 2 and 3 thousand, it appears that traceflag 2362 can be used to disable temporary statistics.

You can enable them like this:

DBCC TRACEON(2362,-1);

And all newly temp stats will not be created. Existing temporary statistics will remain until they are removed. For example by setting the db offline and online again.

Applying log backups to a standby database & Temporary statistics

Addendum 2

When running the queries in addendum #2, and applying log backups to the standby database the stats get updated after each restore.

Even after applying an 'empty' log backup.

In addendum 2, between each log backup restore the following query runs:

SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

These trigger temp stat updates each time.

Proof

All this means that applying logs at night will make the temp stat updates run each day, without a restart of the instance.

Resolving this issue

set auto update stats off on the standby db after applying logs. ALTER DATABASE [ReadOnly2] SET AUTO_UPDATE_STATISTICS OFF;
Run your reporting queries when the restores finish to create the temp stats
Update the statistics on your primary database more often before
applying the logs.
Enabling traceflag 2362

When restoring a log backup they still exist:

SELECT name, is_temporary From sys.stats where is_temporary = 1;

name    is_temporary
PK__Bla__3214EC075017BD54   1
IX_Bla_indexedval   1

But they do get recalculated when rerunning the query.

Addendum #1 (Table with 100M records in a read only db.)

CREATE DATABASE [ReadOnly]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'ReadOnly', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL02\MSSQL\DATA\ReadOnly.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'ReadOnly_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL02\MSSQL\DATA\ReadOnly_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [ReadOnly] SET COMPATIBILITY_LEVEL = 140
ALTER DATABASE [ReadOnly] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [ReadOnly] SET AUTO_UPDATE_STATISTICS_ASYNC ON
ALTER DATABASE [ReadOnly] SET  READ_WRITE
ALTER DATABASE [ReadOnly] SET RECOVERY SIMPLE
ALTER DATABASE [ReadOnly] SET  MULTI_USER
ALTER DATABASE [ReadOnly] SET PAGE_VERIFY CHECKSUM

USE [ReadOnly]
GO
CREATE TABLE dbo.Bla(Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, Indexedval INT,NonIndexedVal INT);
CREATE INDEX IX_Bla_indexedval on dbo.Bla(Indexedval);

INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal)
SELECT TOP(10000000) --10M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;
GO 10

USE MASTER
GO
ALTER DATABASE [ReadOnly] SET  READ_ONLY;

USE [ReadOnly]
GO
SELECT NonIndexedVal
FROM dbo.Bla
WHERE  NonIndexedVal = 999999;

#Addendum 2

ALTER DATABASE [ReadOnly] SET  READ_WRITE;

ALTER DATABASE [ReadOnly] SET RECOVERY FULL

BACKUP DATABASE [ReadOnly] to disk = 'D:\temp\ReadOnly.bak'
WITH COMPRESSION, STATS=5

RESTORE FILELISTONLY FROM DISK = 'D:\temp\ReadOnly.bak'

RESTORE DATABASE [ReadOnly2] FROM disk = 'D:\temp\ReadOnly.bak'
WITH MOVE  'ReadOnly' to 'D:\temp\ReadOnly2.mdf'
,MOVE 'ReadOnly_log' to 'F:\temp\ReadOnly_log2.ldf'
, STANDBY = 'D:\temp\ReadOnly_Standby.bak'

USE [ReadOnly2]
GO
ALTER DATABASE [ReadOnly2] SET AUTO_UPDATE_STATISTICS ON

SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

USE [ReadOnly]

INSERT INTO dbo.Bla WITH(TABLOCK)(Indexedval,NonIndexedVal)
SELECT TOP(2000000) --2M
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum1,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum2
FROM master..spt_values spt1
CROSS JOIN master..spt_values spt2
CROSS JOIN master..spt_values spt3;

USE MASTER
GO
BACKUP LOG [ReadOnly] to disk = 'D:\temp\ReadOnlyLog.trn'
WITH COMPRESSION, STATS=5

RESTORE LOG [ReadOnly2] FROM DISK='D:\temp\ReadOnlyLog.trn'
WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

USE [ReadOnly2]
SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);

BACKUP LOG [ReadOnly] to disk = 'D:\temp\ReadOnlyLog2.trn'
WITH COMPRESSION, STATS=5

RESTORE LOG [ReadOnly2] FROM DISK='D:\temp\ReadOnlyLog2.trn'
WITH STANDBY = 'D:\temp\ReadOnly_Standby.bak'

USE [ReadOnly2]
SELECT Indexedval
FROM dbo.Bla
WHERE  Indexedval =999999
AND 1= (SELECT 1);
SELECT * From sys.stats where is_temporary = 1


Just to be clear, you can have multi-value primary keys. But PRIMARY KEY means all values in the composite key are NOT NULL.

CREATE TABLE d ( i int, j int, k int, PRIMARY KEY (i,j,k) );

INSERT INTO d (i,j,k) VALUES (1,2,null);
ERROR:  null value in column "k" violates not-null constraint
DETAIL:  Failing row contains (1, 2, null).

So, you must be unique on (i,j,k). That makes sense. This can provide a linear map to E in your example

  Here D is multi valued attribute. D can be(i,j,k) or (i,j) or any other combination.

That's where you've lost me. That doesn't make sense. If they're all NOT NULL the only combination that matters is (i,j,k)

If you want them such that reordering is also not allowed, for instance. Such that these are rejected..

INSERT INTO d (i,j,k) VALUES (1,2,3), (3,2,1);
INSERT INTO d (i,j,k) VALUES (1,2,3), (2,1,3);

We can do that too using an EXCLUDE constraint and sort with an array.

CREATE TABLE d (
  i int,
  j int,
  k int,
  PRIMARY KEY (i,j,k),
  EXCLUDE (sort(ARRAY[i,j,k]) WITH =)
);

Now we get our error.

INSERT INTO d (i,j,k) VALUES (1,2,3), (3,2,1);
ERROR:  conflicting key value violates exclusion constraint "d_sort_excl"
DETAIL:  Key (sort(ARRAY[i, j, k]))=({1,2,3}) conflicts with existing key (sort(ARRAY[i, j, k]))=({1,2,3}).


Adding filter for a decimal column slow down SELECT query significantly

You didn't actually provide a question to answer, so I will assume that you're looking for a way to improve the performance of your query. People have written books on that topic so it's difficult to give an answer that fits in the site's format when an execution plan cannot be provided. With that said, the strategy that I use most often is to figure out why a query is slow. Comparing the actual plans between a fast and slow version of similar queries can be very helpful. For a query like yours, the issue might boil down to a missing index or a cardinality estimate issue.

If you want an approach that's nearly guaranteed to work, trying using a temp table. You can insert the unfiltered 2k rows into a temp table in a few seconds and it'll take less than a second to apply the filtering to the temp table with a second SELECT query. Using a temp table here avoids diagnosing the root cause of the issue, but there's nothing wrong with using a temp table to aid the performance of a complex query, or really any query.

In the comments, you asked the following question:

  [C]an I instruct SQL to just apply the addition filter on the 2000
  rows selected by the initial query?

This cannot be done directly but you can achieve the desired behavior with careful placement of TOP. Adding a TOP N with an extremely large N won't change the results of the query but it can change optimizer behavior. The optimizer must guarantee correctness in whatever plan it creates. You and I may know that taking the first 987,654,321,987,654,321 rows will never change the results but the optimizer will implement and honor that check because it cannot guarantee that it wouldn't change the results. For a query like the following:

SELECT q.Id, q.Decimals
FROM
(
    SELECT TOP (987654321987654321) target.Id, target.Decimals, target.count
    FROM    dbo.t1
            JOIN dbo.t2 on ...
            JOIN dbo.t3 on ...
            JOIN dbo.t4 on ...
            JOIN dbo.target on ...
    WHERE   t1.Id = ...
            AND t2.CreatedAt >= ''
            AND t2.Type = ''
            AND target.Id NOT IN (
                SELECT  s1.Id
                FROM    dbo.s1
                        JOIN dbo.s2
                WHERE   s1.Type = '' AND s2.Type = ''
            )
) q
WHERE q.count = 0;

The optimizer will not give you a plan that pushes down the count filter into the derived table. That means that it's pretty likely that you'll get the same query plan that finishes in a few seconds for the derived table, and overall performance should be roughly on par with that query. I expect that the slow query plan that runs for longer than a minute would be avoided.


How to see the list of the Postgres databases via the linux terminal?

If you want to execute the command from the shell, you can use the -c option in psql:

psql -c '\l'

Example:

peter@blackrain:~$ sudo su  postgres
postgres@blackrain:/home/peter$ psql -c '\l'
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
------------+----------+----------+-------------+-------------+-----------------------
 blackrain  | peter    | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 redpepper  | peter    | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
            |          |          |             |             | postgres=CTc/postgres
 x          | peter    | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
(6 rows)

postgres@blackrain:/home/peter$


What type of database server/service to use for an increased insert rate?

What makes you think 200,000 inserts a day will be a problem? That is about 140/minute so if they are reasonably sized and spread evenly through the day just about any database will cope on the most modest hardware. If most of them are crammed into one hour than that would be approx 55 per second, so knowing how the pattern of your apps activity is likely to vary is vitally important when trying to predict how it will scale. The size of the inserts makes a difference too: a few hundred bytes per row, or LOB fields with several Mb of data each?

As it stands this question will get closed as "too broad" as it is essentially a "how long is a piece of string" one.

The only thing we can really suggest with this little information is "test it and see what happens".


Store SSRS Report export directly to database

You should be able to write any sort of code in .Net and connect it into SQL Server.  See http://www.codeproject.com/Articles/19954/Execute-NET-Code-under-SQL-Server-2005 for an example.


Slower subquery performance on older MySQL

For further performance, replace

KEY `userid` (`userid`),

With

KEY `ucf` (`userid`, `cis`, `cis_faktury`),

Your Answer has a problem:  It is "wrong" to GROUP BY only some of the non-aggregate columns in the SELECT.  (Newer versions spit at users for doing so.)  And, I can't see how the query would return the same results.

MySQL 5.6 and MariaDB 10.0 added a number of different optimizations.  It is hard to say what exactly lead to the timing difference you see.

Moving to InnoDB is also advised.


Creating a schema for various sizes of varchar

ability to dynamicly add new "fields" i guess you are talking about UDF (user defined fields)
to achieve this you have few methods but most well known is to have a table with 3 fields

Table
-AppName
-ColumnName
-Value

or Just

Table
-ColumnName
-Value

Where basically you store column name and value as rows, because you can easily add and delete rows, this method has allot of disadvantages and its ugly design, but it gets your job done.

you also have the method of adding few extra fields at the end of you table, and keeping them for incase the user decided to use them

e.g

Customers Table
-Name
-Tel
-Country
-....
-UDF1
-UDF2
-UDF3
-UDF4
-UDF5

in this method the user is only limited to a max of 5 user defined fields, but atleast you can make use of the RDBMS structure and table relations etc.
it has its pros and cons as well.

This question is asked on SO and lots of good answers were posted you can check it click here

i also suggest looking at NoSQL , because when it comes to requiring a capability to freely add columns it means you lose the benefit of an RDBMs of SQL and relation ships of tables, so its a good idea to look at the capabilities of NoSQL and its flexibility.

check out

Neo4J
Mongo DB
Couch DB


Expanding parent-child tree with additional tables

Here's an idea: build your "expansion table" with something like this:

expanded_table (id number, parent_src char(1), parent_id number, ...)

The parent_src column should indicate where the parent comes from, e.g. you could use 'O' of original, 'E' for expansion. (Add a primary key on id, of course.)

To handle the querying, use a view defined like this (pseudo-sql):

select 'O' source, o.id, 'O' parent_src, o.parent_id, [...] from original_table
union all
select 'E' source, e.id,   e.parent_src, e.parent_id, [...] from expanded_table

You can do hierarchical queries based on (source,id)/(parent_src,parend_id) pairs.

No problem with duplicate/overlapping ids, the source info disambiguates them.

Now data integrity is going to be very problematic.

You can't have check constraints that reference other tables, and I don't know of a way of building a "conditional foreign key" that would work here.
You could use triggers to make sure DML on the expanded table is coherent, but even with those and triggers on the original table (if you could do that, which doesn't seem to be the case), that would be very tricky to get right.

Having all DML to this "structure" go through PL/SQL that you control could do it, but that doesn't appear to be possible given the information in your post.


Is it possible to add Postgres extensions to AWS Aurora?

Yes, you can create Postgres extensions in AWS Aurora Postgres. Yes, the extension uuid-ossp can be used in Aurora.

To get supported extension list in your AWS Aurora Postgres version, run this query:

SHOW rds.extensions;

or

select * from  pg_available_extensions;

And also check out PostgreSQL Extensions and Modules Supported on Amazon RDS  in the Amazon Relational Database Service User Guide for the list of extensions supported in AWS Aurora Postgres based on Postgres versions.

There is no difference in the extension usage for both community Postgres and Aurora Postgres if extension installed on the server. For uuid-ossp usage, please refer to F.44. uuid-ossp in the PostgreSQL documentation.


After RAM upgrade on 32-bit Oracle server, ORA-04031: unable to allocate x bytes of shared memory

The error just means that the shared_pool ran out of free space. This can be caused by many things, mostly application related. Make sure your shared_pool has a minimum size needed to run your application.
Sometimes it helps to increase the shared_pool_reserved_size so larger objects can be loaded in there.


Should every job, SSIS project, SSRS have its own sql login

I have a bunch of sql servers and every job as well as every
  connection manager for every SSRS report & SSIS project all use one
  login, sysadmin of course.

Since you asked for best practices, by using sysadmin like that you are breaking The Principle of Least Privilege which states that:

  [...] Administrative tasks are broken out using fixed server roles, and the
  use of the sysadmin fixed server role is severely restricted.

  I would like every SSRS, SSIS etc to use their own logins with only the required permissions.

I'd say creating and managing one login for each user, app, job or services would be going to the oposite extremity and that's a problem too, for, as your fellow DBA said, it would make it a really difficult job to manage so many logins, but you can achieve a middle term by using roles and Windows groups for that matter. Check the Principals doc.

  I also would like to know immediately what's causing blocking etc.,    rather than seeing one sysadmin login every time and then checking to
  see what's running the offending query.

Quoting @Dan Guzman's comment: For the purpose of identification, you can specify the application name in the connection string instead of separate logins.

Once you configured the Connection strings and application name you can retrieve it from sys.sysprocesses by using sp_who2, sp_whoisactive or sys.dm_exec_sessions DMV.


Ref:

https://technet.microsoft.com/en-us/library/ms187054(v=sql.105).aspx
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server

  how much extra storage would be required in setting this snapshot
  database.

It will depend on the rate of changes and the age of your snapshot.

Database snapshots operate at the data-page level. Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation. The snapshot stores the original page, preserving the data records as they existed when the snapshot was created. Subsequent updates to records in a modified page do not affect the contents of the snapshot. The same process is repeated for every page that is being modified for the first time. In this way, the snapshot preserves the original pages for all data records that have ever been modified since the snapshot was taken.

If your source database is fairly large and you are concerned about disk space usage, at some point you should replace an old snapshot with a new snapshot. The ideal lifespan of a snapshot depends on its growth rate and the disk space that is available to its sparse files. The disk space required by a snapshot depends on how many different pages in the source database are updated during the life of the snapshot. Therefore, if updates are mostly to a small subset of pages that are updated repeatedly, the growth rate will slow over time and the snapshot space requirements will remain relatively small. In contrast, when all of the original pages are eventually updated at least once, the snapshot will grow to the size of the source database. If the disk begins to fill up, the snapshots compete with each other for disk space. If the disk drive fills up, write operations to all the snapshots will fail.

  Also how would one ensure that a snapshot is in sync with principal
  with acceptable refresh or lag period of 30 minutes?

Snapshot is a point in time state of your whole database. There is no sync with the source.  You have to create a new snapshot every 30 mins if your acceptable lag is 30 minutes.

A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation.

For your case I would highly recommend you look at Always On Availability Groups where you can have read-only copy of your primary database and sync continuously. Details here.


This db block size is causing our DB Buffer Pool to be constantly
  filled up (95%~100% full)

Your DB Buffer Pool should always be full unless you have recently restarted your server - Oracle will not purge blocks from the cache unless it has to in order to make room for other blocks.

This in itself is not an indication that changing the block size will improve anything. You need to know more about the cache misses.

If the OLTP side of your workload can query any part of your 5TB data, optimizing the buffer pool a little for it will probably not make much difference (but may unnecessarily slow down your big sequential scans that it was presumably optimized for)

On the other hand, if there is a small subset of the data hit regularly, and cache misses for the OLTP workloads can be substantially reduced by reducing the block size (which will depend on whether the reads are really scattered across blocks or not), then perhaps the benefits would be significant.

In the end you will have to test and find out :)


How to Add A User To A Database In Standby / Read-Only Mode In Sql Server

Check if the database is part of logshipping. As the database in secondary server will be in Read-only/standby state. ALtering the database directly will not be allowed.

Identify the primary server and add the user there. This will move to your read-only secondary.

Edit : 1

Make sure the logins exist in both the servers.


SQL Insert error Column name or number of supplied values

SRRA has 9 columns, but you're only inserting 8. Specify DEFAULT for your rowguid column.

INSERT INTO srra
    VALUES      (@num_SessIdSession,
                 @tin_SrraCptAnom1,
                 @tin_SrraCptAnom2,
                 @tin_SrraCptAnom3,
                 @tin_SrraCptAnom4,
                 @tin_SrraCptAnom5,
                 @tin_SrraCptAnom6,
                 @tin_SrraCptAnom7,
                 DEFAULT)


MySQL export user with semi-colon ";" on the end

In addition to the fact that there's not any "official" way to do this, there's another potential problem with the approach: there's no mechanism that will ever revoke any privilege for a user.

Today, user 'foo' at host 'bar' has SELECT, UPDATE, and DELETE.

mysql> SHOW GRANTS FOR 'foo'@'bar';
+-------------------------------------------------------------------------------+
| Grants for foo@bar                                                            |
+-------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DELETE ON *.* TO 'foo'@'bar' IDENTIFIED BY PASSWORD '*' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Oops, they shouldn't have delete, so you fix that.  Tomorrow's file contains the correct grant statement:

mysql> GRANT SELECT, UPDATE ON *.* TO 'foo'@'bar';
Query OK, 0 rows affected (0.00 sec)

After that's processed, what privileges does the user have?

mysql> SHOW GRANTS FOR 'foo'@'bar';
+-------------------------------------------------------------------------------+
| Grants for foo@bar                                                            |
+-------------------------------------------------------------------------------+
| GRANT SELECT, UPDATE, DELETE ON *.* TO 'foo'@'bar' IDENTIFIED BY PASSWORD '*' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

The GRANT statement is additive, so it only specifies what privileges should be added if not present.

I would think, then, that a better approach might be what @Mat was (I think) implying... you should script the privilege changes you want to make, then execute that script against all of the servers you want to update, rather than trying to treat one server as permissions master.


SSMS 2016 Job Schedule Wizard Starting At entry field

This only occurs for me when the text size of my screen has been set to Medium or Large, or when the window is misalgined and needs to be minimized and maximized again.


SQL Server 2008: how much RAM memory should SQL Server use in a 8GB RAM server?

Question: Considering scenario, that we have 8GB and we also have IIS hosted in this server (which I believe is a bad idea), in how much RAM should SQL Server use?

The answer to the question "how much memory SQL Server would use" is, as much as it can. For that reason its always advisable to put appropriate value for max server memory so that buffer pool is restricted.

Please don't use task manager to get SQL Server memory utilization it wont give correct value in case when SQL Server service account has locked pages in memory(LPIM) privilege. To find how much RAM SQL Server is using please use below query

select
(physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory

  Where can I configure the amount of RAM that SQL Server should really use without impacting Windows, IIS and any other running services?

You can do that in Sp_configure using below query.

sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'max server memory (MB)' , xxx --value in MB which you need to set
go
reconfigure.

You can fix max server memory using the query but remember, SQL Server can still use memory outside buffer pool for third party DLL's and extended stored procs. This can lead SQL Server to use memory more that what is restricted in buffer pool

Please read this article. It would help you in setting starting value for max server memory. You should use below counters to set correct value for max server memory and monitor SQL Server memory usage.

SQLServer:Buffer Manager--Page Life Expectancy(PLE): PLE shows for how long page remain in buffer pool. The longer it stays the better it is. Its common misconception to take 300 as a baseline for PLE.   But it is not,I read it from Jonathan Kehayias book( troubleshooting SQL Server) that this value was baseline when SQL Server was of 2000 version and max RAM one could see was from 4-6 G. Now with 200G or RAM coming into picture this value is not correct. He also gave the formula( tentative) how to calculate it. Take the base counter value of 300 presented by most resources, and then determine a multiple of this value based on the configured buffer cache size, which is the 'max server memory' sp_ configure option in SQL Server, divided by 4 GB.   So, for a server with 32 GB allocated to the buffer pool, the PLE value should be at least (32/4)*300 = 2400. So far this has done good to me so I would recommend you to use it.

SQLServer:Buffer Manager--CheckpointPages/sec: Checkpoint pages /sec counter is important to know about memory pressure because if buffer cache is low then lots of new pages needs to be brought into and flushed out from buffer pool,  due to load checkpoint's work will increase and will start flushing out dirty pages very frequently. If this counter is high then your SQL Server buffer pool is not able to cope up with requests coming and we need to increase it by increasing buffer pool memory or by increasing physical RAM and then making adequate changes in Buffer pool size. Technically this value should be low if you are looking at line graph in perfmon this value should always touch base for stable system.

SQLServer:Buffer Manager--Freepages: This value should not be less you always want to see high value for it.

SQLServer:Memory Manager--Memory Grants Pending: If you see memory grants pending in buffer pool your server is facing SQL Server memory crunch and increasing memory would be a good idea. For memory grants please read this article

SQLServer:memory Manager--Target Server Memory: This is amount of memory SQL Server is trying to acquire.

SQLServer:memory Manager--Total Server memory This is current memory SQL Server has acquired.


Grouping operator in DBMS (γ) is duplicate-impervious. Why?

In the book, "Garcia-Molina H., Ullman J., Widom J., Database Systems: The Complete Book. Pearson Prentice Hall, 2009", Chapter 5, "Algebraic and Logic Query Languages", on top of page 217 of the 2nd edition, there is a box titled "δ is a Special Case of γ", that explains how the duplicate removal operation δ is redundant, since it can be replaced by the use of γ.

Simply group by all the attributes of the relation, without any aggregation. This will replace each group, consisting only of tuples wich coincide on all the attributes, with a single tuple. And this is exactly the semantics of the duplicate elimination operator.


Updating rows from other rows in a huge table

Recently read an article that might help you, see: https://dzone.com/articles/specifying-target-tables-in-mysql

Still think a self join would be faster than a correlated sub-query. Especially on MySQL 5.5 and less, where these type of sub queries are badly optimized.

Self join like:

UPDATE mytable t1
  JOIN mytable t2
    ON t1.item-id = t2.item-id
SET t1.extra = t2.extra
WHERE t1.type > 0;


I had the same issue with a migration from 9i to 11g.  I decided that I did not want to take a chance that Oracle's character set conversion would do something I did not expect.  Managers don't care about character sets, they just want to know that the data was migrated without any problems. I kept the WE8MSWIN1252 character set and there were no problems.

However, if you had to have a UTF8 character set, and did not want to take any chances this is how I would do it.

Requirements:  a virtual machine in a test environment, to make the install and testing painless

start on the original 10g database in a test environment.
start a new instance of 11g on a virtual machine in a test environment with the old character set
import your data using a data pump or the old imp/exp.
test the web application to see what happens
if successful take a snapshot of the machine so you can revert to this
establish what columns need to be larger. Usually these are LONG or possibly large VARCHAR2 fields
increase the size of the columns so conversion is not required
if you are able set up another Oracle 11 with the new character set and use the data pump to bring the data over.  If this is not feasible then do an export of your data and bring the virtual machine back to before the Oracle install.  Then install Oracle 11 g with the new character set and import the data. Ideally no conversion will occur if you have identified all the tables.
test the web application

Why go to all this extra work?  This solution breaks your project into two issues, the upgrade and the character set. Upgrading databases is not to be considered lightly. Many times it is not the upgrade that is the problem but peripheral problems like interactions with applications or other databases.  Custom PL/SQL code should have no problems but you never know.

If there is a problem people will be looking to you to explain how you were diligent and careful not how you saved a day or two.


In this case, there is an easy workaround to the "explode-implode" problem that happens with JOIN + GROUP BY:

SELECT  UPPER(`2016`.sonum) as sonum,
        SUM(`2016`.lbrhrs) as 'lbrhrs',
        SUM(`2016`.burhrs) as 'burhrs',
        MONTH(`2016`.date) as 'date',
        `2016`.dept as dept,
        ( SELECT MAX(closedate)
              FROM closedates
              WHERE sonum = `2016`.sonum   -- see note below
        ) as 'closed date'   -- Here's the trick
    FROM  `2016`
    WHERE  `2016`.sonum=3470
      and  month(`2016`.`date`)=1
    GROUP BY  UPPER(`2016`.sonum),
              `2016`.dept,
              month(`2016`.date);

If sonum has any ..._ci collation, then it is "Case Insensitive", so the UPPER() is unnecessary for comparing.  Please provide SHOW CREATE TABLE for both tables.

The 'trick' is to avoid the JOIN, thereby avoiding the multiplication of rows before doing the SUMs.


Store fields that are subset of other fields on same table

Generally in a Sales system, Sales Orders and Invoice Orders are two different entities related to each other. That's probably a good way to normalize the columns you're referring to. In general, it's better to normalize your tables to reduce data redundancy and improve query efficiency, as opposed to very wide Tables.
Also generally Orders tables (both Sales and Invoices respectively) are generally broken out into two tables themselves: 1) OrderHeader and OrderLine (you can name these more specifically as appropriate).
The Header tables store general information such as the SalesDate (or InvoicedDate for InvoiceHeader) and customer information. It can also store the totals as well but isn't necessary. The Line tables store individual lines for each item of the Order and the information specific to that item. Each line would have the individual cost of the item (e.g. SalesCost for SalesLine, InvoicedAmount for InvoiceLine).


TSQL Script Execution Through Power shell, Special Characters Rendering Problem

This issue has nothing to do with SQL Server Collations. The problem happens (i.e. the characters get transformed) prior to connecting to SQL Server. If the problem could be resolved by prefixing the string with an upper-case "N", then the script wouldn't work correctly in SSMS (as the OP states it does).

The problem here has to do with the encoding of the file not being known to Invoke-Sqlcmd, thus the bytes making up the file are interpreted incorrectly. This is why it does work correctly when pasting into SSMS (since the encoding of that window is UTF-16LE, like all of .NET / Windows, and is known to SSMS).

I checked the documentation and it does not appear that there is a parameter to set the file encoding like there is with SQLCMD. I assume that the file is being saved as either UTF-16LE ("Unicode" in Microsoft-land) or as UTF-8, but in either case, without a byte-order mark (BOM). I suggest re-saving the file with an encoding that includes the Byte Order Mark ("save with encoding" in some programs like SSMS). Be sure to pick the encoding that specifies either "BOM" or "with signature" (like in MS products). The BOM is 2 - 4 characters (depending on the encoding being used) at the beginning of the file that indicates the encoding being used (i.e. UTF-8, UTF-16LE, UTF-16BE, UTF-32LE, or UTF-32BE). The BOM is a Unicode-only feature and does not exist for non-Unicode encodings. With it being set in the file, any program that opens the file will detect it and know what the encoding is without you needing to specify. Hopefully this holds true for Invoke-Sqlcmd.


Seeking Suggestion for faster DB Insertion Policy

There is no particular policy to faster insertion (loading), which can direct you to your target. Instead, there are some configuration and prerequisites which can help you to improve write (insert) performance.

The performance of any application, installed on OS lies on the hardware of the server. So, first, you should identify how much IOps disk you required, to collect that data you can use SQLIOSim.

If you'll use SAN, then you should choose HDD with low space and high IOps (You can collect details from Vendor about complete hardware). More numbers of disks result in more IOps and better write performance.
Because application writes data to disk, disk performance plays a great role when it comes to OLTP application. So, proper disk configuration is required.

Space on disk (HDD) are divided into 512 bytes sector and 4KB (default) cluster size contains 8 sectors. But the size of SQL Server data pages is 8KB. So, formatting cluster size into 8KB for .mdf and 64KB for .ldf will boost write performance.

You can visit this link for in-depth detail: Disk Partition Alignment Best Practices for SQL Server
Place Transaction Log file (.ldf) on RAID10 or RAID1, tempdb on RAID0 (because restarting SQL Server recreates it) and data file (.mdf) according to your security procedure (on RAID5, 1 or 10). All the data files should be within a separate partition.
Change Recovery Model of the database into BULK_LOGGED.
For OLTP application like yours, it's recommended to create a normal table (according to requirement) with minimum indexes. So, I would suggest creating a simple table with a clustered index on varchar datatype column.
Use BULK INSERT instead of BCP.
Follow this link for a detailed guide about data loading: The Data Loading Performance Guide

If it's normal INSERT through an application, then use ROWLOCK hint with the insert statement. Divide 40K clients into four parts and give them an Id. 10K = 1, 10K=2, 10K=3, 10K=4 (in a table). Now create 4 tables with the same schema (which will use to INSERT records). In Stored Procedure, on the basis of Id of User direct the transaction to respective table. This will reduce locking/blocking on the table.
Make sure you have a healthy network connection between application and database server. There is no DMZ and firewall in between. Routing request through multiple devices will increase total execution time.


What is wrong with this mysql INSERT statement?

There are two way to insert data:

if you have to insert data in Artical table with specific book title then do following

INSERT
INTO Artical (title, price, book_id)
VALUES (
    'Six faces of shadow',
    15,
    (
        SELECT book_id FROM book
        WHERE book.title = "Six faces of shadow"
    )
);

if you want to add all book title with id then do following

INSERT
INTO Artical (title, price, book_id)
SELECT title, price, book_id FROM book

Hope this Help!!!


Full text search on two tsvector columns

In parts similar to my last answer:

SELECT a.id, a.article_title AS title, a.pub_date, a.web_id
       c.city_points, c.city_names
FROM   article a
LEFT   JOIN (
   SELECT ac.article_id AS id
        , string_agg(st_asText(c.geom), ', ') AS city_points
        , string_agg(c.city, ', ') AS city_names
   FROM   article_cities ac
   JOIN   city           c ON c.id = ac.city_id
   GROUP  BY 1
   ) c USING (id)
WHERE a.title_tsv @@ to_tsquery('putin')
OR    EXISTS (
   SELECT 1
   FROM   article_keywords ak
   JOIN   keyword          k ON k.id = ak.keyword_id
   WHERE  ak.article_id = a.id
   AND    k.keyword_tsv @@ to_tsquery('putin')
   );

What's new here: we are only interested in the existence of a matching keyword, we are not even displaying them. (You may want to rethink that: shouldn't it become obvious from the result why the row was found?)

So I am using an EXISTS semi-join as alternative WHERE clause. Related:

Count where previous record exists in either of two tables

Once again we aggregate cities per article_id before joining to article, so we need no aggregate or DISTINCT in the outer SELECT since we do not multiply rows in the join.

And once again use LEFT [OUTER] JOIN to include articles that are not linked to any cities at all.

Be aware that a list of comma-separated tables combined with WHERE conditions to join them is effectively the same as [INNER] JOIN on a join condition. Basic in the manual here.


Setting up filtering for server-side tracing

Does that mean the server-side trace is always only checking the database it's been creating in?

No, the server-side trace has the scope of just that:  the server.  When I create (what I think is) the exact trace definition and export it, I get the following snippet:

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @bigintfilter = 1000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter

exec sp_trace_setfilter @TraceID, 35, 0, 6, N'AdventureWorks'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

I am using SQL Server Profiler 2008 R2 as well.  If you do not filter on the database name, then you will be capturing trace events for all of the instance databases.  As to why your export didn't seem to include that portion of the filter, I can't say for sure.  I know this goes without saying, but I'd check your trace definition within Profiler before you export to ensure the database name is in fact included as a filter (again, I know you've probably double and tripple checked this, but I had to say it).


I agree that the wording is confusing.
A pg_basebackup taken with -X stream can of course be taken as a basis for point-in-time recovery.
I think the assumption here is that you take a pg_basebackup but do not have WAL archiving configured. Then the backup contains just the WAL segments required to recover to the end of the backup and reach a consistent state, but you don't have the WAL required to recover beyond that point.


Postgresql Log message without its statement

If you want to suppress the "STATEMENT" information, then you could set log_min_error_statement = fatal (or panic).

If you want to keep the STATEMENT information but compress it into a single line with the main entry, then you could set log_destination = 'csvlog'.  Technically it will still be multiple lines, but the internal newlines will be escaped by inclusion within double quotes, which any good csv parser will handle for you.


Oracle 11g hierarchical queries with limit, offset and order by to achieve pagination on the root level

With the help of Philᵀᴹ's and Joe Obbish's comments I was able to create a query based on recursive CTEs (Common Table Expressions) that allows pagination on the top level of a hierarchical table.

The SQL statement looks like this:

WITH my_hierarchy (<column1>, <column2>, ...) AS (
  SELECT <column1>, <column2>, ...
  FROM (
    SELECT <column1>, <column2>, ... , row_number() OVER (ORDER BY <order_column> ASC) row_num
    FROM my_table mt
    WHERE mt.<column1> = 'some value'
      AND mt.<column2> = 'other value'
  )
  WHERE row_num between <offset> and <offset+limit> -- pagination on top level
  UNION ALL
  SELECT mt.<column1>, mt.<column2>, ...
    FROM my_table mt
    INNER JOIN my_hierarchy mh   -- recursive self join
        ON mt.parentId = mh.id
)
SELECT *
FROM my_hierarchy


The OLE DB provider "SQLNCLI" for linked server X reported a change in schema version between compile time X and run time X for table [tablename]

Are you using a synonym? The few cases I have seen people complaining of this, this fix worked - http://support.microsoft.com/kb/2498818 - What version/build are you on?


Can't create schema for a db with PostgreSQL

If you create the schema as user mydb_administrator, it will work.
But you messed up your installation by removing the SUPERUSER property from postgres. If that was not deliberate and you have no other superuser, you will have to start PostgreSQL in single-user mode to repair that. See the many existing anwers for details about that.


Extract certain rows from table and import

You can specify your queries per table with the QUERY parameter with Data Pump export. For example:

QUERY=employees:"WHERE department_id > 10 AND salary > 10000"

With the above, only those rows are exported from the employees table, that have department_id > 10 AND salary > 10000.

More information in the official documentation:

http://docs.oracle.com/database/121/SUTIL/dp_export.htm#SUTIL860

Another way to transfer the data: create a database link, and just simply run an INSERT ... SELECT statement.

For example, on the target database:

create database link source connect to username identified by password using 'SOURCE_TNS';
insert into table1 select * from table1@source where ...;


I cannot get my data source to connect to an SQL Server named instance using SSRS Report Builder

I discovered that the named instance does not use default SQL port, but uses port 8000 (which is open on the firewalls). I tried specifying the port number in the connection string like this:

Yes, but the named instance ports are dynamic and managed internally by SQL browser service which use the port number 1434. In your case port# 8000 should not be used in connection string.
However, Please follow any one of following scenario to troubleshoot the issue:
Scenario 1. If you want use named instance in connection string:

make sure SQL Browser service running where SQL named instance installed.
Open 1434 (SQL Browser) port in firewall (where SQL service running)
Make sure you are able to do telnet to the SQL host on port number 1433 and 1434
Your connection string looks like: Data Source=clever-sql1\MSSQLSERVER02;Initial Catalog=cleverdataout

Scenario 2. If you want use port number in connection string:

Change SQL port from configuration manager

2. Restart sql service (named instance)
3. Open the port 1435 (configured in step:1) at windows firewall
4. Make sure you are able to do telnet to the host on custom port 1435 (configured in step:1)
5. Your connection string looks like: Data Source=clever-sql1,1435;Initial Catalog=cleverdataout
In any scenario, verify the user (used in connection string) have appropriate access on SQL server


You can use DBCC CHECKIDENT to reseed the IDENTITY column.

Here is a sample you can run:

SET NOCOUNT ON;
USE tempdb;
GO

CREATE TABLE dbo.foo(ID INT IDENTITY(1,1));
GO

INSERT dbo.foo DEFAULT VALUES;
GO 100

-- note: set it to ([the next value you want] - 1)
DBCC CHECKIDENT(N'dbo.foo', RESEED, 499);
GO

INSERT dbo.foo DEFAULT VALUES;
GO 3

SELECT ID FROM dbo.foo WHERE ID >= 98;

Results:

98
99
100
500
501
502


Why doesn’t this recursive CTE with a parameter use an index when it does with a literal?

Randi Vertongen's answer correctly addresses how you can get the plan you want with the parameterized version of the query. This answer supplements that by addressing the title of the question in case you are interested in the details.

SQL Server rewrites tail-recursive common table expressions (CTEs) as iteration. Everything from the Lazy Index Spool down is the runtime implementation of the iterative translation. I wrote a detailed account of how this section of an execution plan works in answer to Using EXCEPT in a recursive common table expression.

You want to specify a predicate (filter) outside the CTE and have the query optimizer push this filter down inside the recursion (rewritten as iteration) and have it applied to the anchor member. This will mean the recursion starts with only those records that match ParentId = @Id.

This is quite a reasonable expectation, whether a literal value, variable, or parameter is used; however, the optimizer can only do things for which rules have been written. Rules specify how a logical query tree is modified to achieve a particular transformation. They include logic to make sure the end result is safe - i.e. it returns exactly the same data as the original query specification in all possible cases.

The rule responsible for pushing predicates on a recursive CTE is called SelOnIterator - a relational selection (= predicate) on an iterator implementing recursion. More precisely, this rule can copy a selection down to the anchor part of recursive iteration:

Sel(Iter(A,R)) -> Sel(Iter(Sel(A),R))

This rule can be disabled with the undocumented hint OPTION(QUERYRULEOFF SelOnIterator). When this is used, the optimizer can no longer push predicates with a literal value down to the anchor of a recursive CTE. You don't want that, but it illustrates the point.

Originally, this rule was limited to working on predicates with literal values only. It could also be made to work with variables or parameters by specifying OPTION (RECOMPILE), since that hint enables the Parameter Embedding Optimization, whereby the runtime literal value of the variable (or parameter) is used when compiling the plan. The plan is not cached, so the downside of this is a fresh compilation on each execution.

At some point, the SelOnIterator rule was improved to also work with variables and parameters. To avoid unexpected plan changes, this was protected under the 4199 trace flag, database compatibility level, and query optimizer hotfix compatibility level. This is quite a normal pattern for optimizer improvements, which are not always documented. Improvements are normally good for most people, but there is always a chance that any change will introduce a regression for someone.

  I want to put the CTE in a reusable view

You could use an inline table-valued function instead of a view. Provide the value you want to push down as a parameter, and place the predicate in the recursive anchor member.

If you prefer, enabling trace flag 4199 globally is also an option. There are many optimizer changes covered by this flag, so you would need to carefully test your workload with it enabled, and be prepared to handle regressions.


To disable TDE you must first remove the candidate database from the Availability Group - the group itself however does not need to be dropped.

To do this, the following code is needed:

USE [master];
GO

ALTER DATABASE [YourDatabase] SET HADR OFF;
GO

Once this is complete, you may then disable TDE on the PRIMARY replica:

ALTER DATABASE [YourDatabase] SET ENCRYPTION OFF;

The process of decryption is asynchronous, and can be monitored by looking at the percent_complete column in the sys.dm_database_encryption_keys DMV

One thing to note, is that although the database itself is encrypted, if you aren't careful you can end up with part of the log file still encrypted - this is only a problem if you will be dropping the encryption certificates from the offending servers, or trying to take a backup and restore this database to a server that doesn't hold this encryption certificate.


How to return some * data with time interval?


Action_id group by for last 3 days in 3 different columns

PROPOSED QUERY

SET @dt0 = '2014-09-17';
SET @dt1 = @dt0 - INTERVAL 1 DAY;
SET @dt2 = @dt0 - INTERVAL 2 DAY;
SET @sql = 'SELECT A.action ACTION,';
SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt2 AND B.action=A.action,1,0)) "',@dt2,'",');
SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt1 AND B.action=A.action,1,0)) "',@dt1,'",');
SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt0 AND B.action=A.action,1,0)) "',@dt0,'"');
SET @sql = CONCAT(@sql,'FROM (SELECT DISTINCT action FROM mytable ');
SET @sql = CONCAT(@sql,'WHERE date>=@dt2 AND date<=@dt0) A,mytable B GROUP BY A.action');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SAMPLE DATA

mysql> drop database if exists alkb;
Query OK, 1 row affected (0.01 sec)

mysql> create database alkb;
Query OK, 1 row affected (0.00 sec)

mysql> use alkb
Database changed
mysql> create table mytable
    -> (
    ->     id int not null auto_increment primary key,
    ->     date date not null,
    ->     action varchar(10)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytable (date,action) values
    -> ('2014-09-15','ACT1'),('2014-09-16','ACT1'),
    -> ('2014-09-16','ACT2'),('2014-09-17','ACT1'),
    -> ('2014-09-17','ACT2'),('2014-09-17','ACT3');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+----+------------+--------+
| id | date       | action |
+----+------------+--------+
|  1 | 2014-09-15 | ACT1   |
|  2 | 2014-09-16 | ACT1   |
|  3 | 2014-09-16 | ACT2   |
|  4 | 2014-09-17 | ACT1   |
|  5 | 2014-09-17 | ACT2   |
|  6 | 2014-09-17 | ACT3   |
+----+------------+--------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SET @dt0 = '2014-09-17';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @dt1 = @dt0 - INTERVAL 1 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @dt2 = @dt0 - INTERVAL 2 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = 'SELECT A.action ACTION,';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt2 AND B.action=A.action,1,0)) "',@dt2,'",');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt1 AND B.action=A.action,1,0)) "',@dt1,'",');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'SUM(IF(B.date=@dt0 AND B.action=A.action,1,0)) "',@dt0,'"');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'FROM (SELECT DISTINCT action FROM mytable ');
Query OK, 0 rows affected (0.00 sec)

mysql> SET @sql = CONCAT(@sql,'WHERE date>=@dt2 AND date<=@dt0) A,mytable B GROUP BY A.action');
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

+--------+------------+------------+------------+
| ACTION | 2014-09-15 | 2014-09-16 | 2014-09-17 |
+--------+------------+------------+------------+
| ACT1   |          1 |          1 |          1 |
| ACT2   |          0 |          1 |          1 |
| ACT3   |          0 |          0 |          1 |
+--------+------------+------------+------------+
3 rows in set (0.00 sec)

All you have to do is supply the last date and assign it to @dt0;

GIVE IT A TRY !!!


Mysql SHOW OPEN TABLES shows In_use but nothing in SHOW PROCESSLIST

Speculating, since the tables are in MyISAM, perhaps there is a corruption in the table(s). Since it is locking when checking with the server online, try using myisamchk on the stuck table(s) (and perhaps all the tables) after shutting down the server if possible. 51GB is a lot of data so it may take a while. If possible, suggest following commenter's recommendation to convert the tables to InnoDB; adjust server configuration accordingly.

Ref: http://dev.mysql.com/doc/en/myisamchk.html


How long will a temporary MEMORY table persist if I don't drop it (MySQL)

What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure.
Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the question? Let me set the record straight. The fact is: The temp table exists inside and outside of the Stored Procedure, but you can do things with the temporary table only inside the scope of a running Stored Procedure.
According to the Book

Chapter 5 has a subheading Returning Result Sets to Another Stored Procedure.
It says in paragraph 2 on Page 117:

Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution b, and -- because the temporary table has scope throughout the entire session --  it creates many of the same maintainability issues raised by the use of global variables. but if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.

Looking back at the StackOverflow question, I can see someone called the Stored Procedure from the mysql client. Since the mysql client is not a Stored Procedure, the results cannot be manipulated the mysql client level via DML other than doing a SELECT to see the results. Since you calling a recursive stored procedure, you can rest assured the temp table is fully accessible for the duration of the DB Connection.
I hope this answers your question.
UPDATE 2014-01-31 11:26 EST
In your last comment, you said

If we employ persistent connections, will the MEMORY table persist through multiple REQUESTS, and it seems it will, so for performance sake, I'm assuming that using this method will *REQUIRE us to explicitly DROP the temporary MEMORY table. Do I assume correctly?

Yes and No. I say Yes because it is one way to do it. I say no because another way to do it is:
CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;
TRUNCATE TABLE id_list;

Whichever way you choose, the operation is still the same since TRUNCATE TABLE drops and recreates the table. This will not harm other DB Connections since each Connection has its own id_list table.


A really poorly constructed query can take a huge amount of space -- can't really protect from that.

Your maintenance?  Or AWS's maintenance?

If you can 'quickly' upgrade to a bigger instance, would that be an acceptable fix for nearly running out of space?

Would you like a review of the schema for the largest table?  Maybe some datatypes could be shrunk.  Maybe some normalization is feasible.  Maybe unhexing uuids, etc, is advisable.  Etc.

Otherwise, stick with your old rule of thumb.


Create a supertype entity CarBody for subtypes Sedan, SUV, ... and move all general information to that supertype. Your M-M relationship will be between CarBody and Feature. See this topic for subtyping in datamodeling Supertype/Subtype deciding between category: complete disjoint or incomplete overlapping .


Do not have 4100 identical tables.  There are so many issues with such, and it is repeatedly panned in this and other forums.

4100 "stations", each with, say 10 readings, per hour?  41000 is about 12/second (average), which is not very much if you had one row per datapoint per station.  One table with 3 columns: hour (or datetime), station_id, reading.  And batch the 10 rows in a single INSERT statement for a little more efficiency.

How you can fetch the data in a variety of ways -- "just temperatures at 6am on weekends" becomes a simple, and somewhat efficient query.

Alternatively, have one table with 12 columns:  hour/datetime, station_id, and 10 specific readings.  This is likely to be more efficient in all ways.  This also has the advantage that the readings can have specific datatypes for each measurement.  Temperature or humidity:  TINYINT SIGNED is 1 byte and allows -128..127 or DECIMAL(5.2) (3 bytes) for +/-999.99 instead of a generic FLOAT (4 bytes).

The alternatives probably take a similar amount of disk space.  This database will be rather big after a year; you should probably estimate the future size and think ahead about indexing, and other issues.


Backup TO URL WITH NOINIT fails because file already exists

Based on the documentation, it seems that is not supported.

Appending to existing backup blobs is not supported. Backups to an
existing blob can only be overwritten by using the WITH FORMAT option.
However, when using file-snapshot backups (using the WITH
FILE_SNAPSHOT argument), the WITH FORMAT argument is not permitted to
avoid leaving orphaned file-snapshots that were created with the
original file-snapshot backup.


Permission denied while trying to delete even when delete is granted

After a bit of hunting I realized that maybe I am getting this error because I am adding a condition to delete. Which means the database is probably selecting those records. And I hadn't granted select on the table to the user.
Grant select: grant select on table to username
And it worked.


"1292 Incorrect datetime value" when updating an "empty" datetime field

Congratulation on your commitment to clean dates.
Temporary disabling the NO_ZERO_DATE sql_mode will enable this cleanup.
Just to be sure, use 0000-00-00 00:00:00 as the date too.
SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_DATE', '');
UPDATE mytable
SET updated_at=created_at
WHERE (updated_at='' OR updated_at='0000-00-00 00:00:00') and id >0;


MyISAM table row size is bigger than the sum of all the columns size?

That's because you're using a CHAR column type; this is a fixed-width column; regardless of how much data you put in there, it'll always take up exactly that number of characters in the data store.  It is designed for columns where you'll always have exactly that number of characters, because it's more efficient in that specific case.

What you want in your case is VARCHAR; it'll make a note in the column data of how many characters you're storing, and only take up that amount of space in the data store.


I have only seen one instance where this made a difference.  The int was being cast as a string, and then sorted alphabetically.  The zero fill prevented a strange sort order, such as 10 coming before 9.


You can compare both slaves performance using Percona utilities mentioned below:

pt-heartbeat measures replication lag on a MySQL server. You can use it to update a master or monitor a replica. If possible, MySQL connection options are read from your .my.cnf file.
pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master.


Statistical probability of an incomplete transaction committed by Postgres

Speaking out of ignorance, nothing is perfect, so in that vein, how statistically likely is it that Postgres will incompletely commit a transaction and not be able to detect the malfunction?

Barring undetected PostgreSQL or operating system kernel / C library bugs, and in the presence of good quality hardware with ECC memory, nil.

PostgreSQL's write-ahead logging, where it:

Records changes only to to write-ahead log
fsync()s the WAL to ensure it's on disk and persistent
and only then write the changes to the main tables

is extremely robust.

You do need to ensure that your operating system and hardware honours fsync requests properly. The pg_test_fsync program is useful for this, though the results require interpretation. Also, beware of cheap SSDs without proper power-failure protection.

The main risks to data integrity are:

The system administrator
Lack of backups and replication
Operating system and PostgreSQL bugs;
Cheap poor quality memory;
Cheap SSDs without power failure protection;
Faulty/overheating hardware, though on modern x86 you'll usually get MCEs warning you about issues

Anyone who gives you "statistics" or numbers on things like the probability of a future bug causing issues is likely full of it.

There's been lots of work done on transient memory errors; the risk is real but the probability is low. I've never seen a bit-flip error in the wild, but I have colleagues who have. Google has lots of info.

The most important thing is proper backup and admin processes.

I wrote a bit more about this topic a while ago.

BTW, there are a few exceptions to the rule that effects aren't visible until they're committed:

Locking (obviously)
hash indexes (don't use them)
SEQUENCEs (and the SERIAL columns that use them)
... more others I haven't thought of


If you want to find the people "under" CDLEADER = 21, then why do use START WITH US.CDLEADER IS NULL in your CONNECT BY? Just simply use START WITH US.CDLEADER = 21:

WITH ADRECURSIVEUSERLEADER
AS
(
    SELECT US.CDLEADER AS CDLEADER
          ,US.CDUSER AS CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADUSER US
     START WITH US.CDLEADER = 21         -- parent
   CONNECT BY PRIOR US.CDUSER = US.CDLEADER -- child / parent
)
SELECT *
  FROM ADRECURSIVEUSERLEADER T
 WHERE 1 = 1;

SQL Fiddle


you need to remember that in a like statement a _ is ANY single character, and a % is any number (including 0) characters

on your insert for StaticFilesTbl if you use the search strings you wish for in there (and bracket out the underscores) that should i believe get you what you want

INSERT INTO #StaticFilesTbl(StaticFilename)
VALUES('SG[_]%[_]123456'),
        ('TFF[_]x2[_]%[_]x3');

Also you'll nolonger need the + '%' on the end of the LIKE statement as it has everything built in already


Is there a way to trace the DDL and DML activities performed on Microsoft SQL Server manually?

I'm guessing your Audit was turned off long enough ago to explain why you're only seeing recent data (couldn't tell you how it was turned off though).
Other tools built into SQL Server that might help you with auditing is Change Data Capture and/or you could also look into DDL Triggers.


It can be done with a query:

SELECT
    s.*
FROM
  ( SELECT DISTINCT series_id
    FROM series
  ) AS ds
  JOIN
    series AS s
      ON  s.series_id = ds.series_id
      AND s.season =
          ( SELECT si.season
            FROM series AS si
            WHERE si.series_id = ds.series_id
            ORDER BY si.season DESC
            LIMIT 1
          )
      AND s.episode =
          ( SELECT si.episode
            FROM series AS si
            WHERE si.series_id = ds.series_id
            ORDER BY si.season DESC, si.episode DESC
            LIMIT 1
          ) ;

Tested at SQL-Fiddle

With an index on (series_id, season, episode), it should be quite efficient, if the number of series_id is small.

CREATE UNIQUE INDEX uix
  ON series
  (series_id, season, episode) ;

There are several ways to do this query but all get rather complicated because the maximum needed is based on two columns (season and episode). Here's another one, which is the query above, simplified a bit:

SELECT
    s.*
FROM
  ( SELECT series_id, MAX(season) AS season
    FROM series
    GROUP BY series_id
  ) AS ds
  JOIN
    series AS s
      ON  s.series_id = ds.series_id
      AND s.season = ds.season
      AND s.episode =
          ( SELECT si.episode
            FROM series AS si
            WHERE si.series_id = ds.series_id
              AND si.season = ds.season
            ORDER BY si.episode DESC
            LIMIT 1
          ) ;

and another using NOT EXISTS:

SELECT s.*
FROM series AS s
WHERE NOT EXISTS
      ( SELECT 1
        FROM series AS sb
        WHERE sb.series_id = s.series_id
          AND ( sb.season > s.season
             OR sb.season = s.season AND sb.episode > s.episode
              )
      ) ;


Select ONLY Max(date) when there's repeating dates

Akina has already pointed out critical problems with the data... that there is no existing way to determine the proper ordering of the Tracking rows.  It is tempting to just skip this question, but despite the problems, there are some concepts and SQL queries patterns which can help here.

In a traditional relational database, the storage order of records should never be assumed.  Although it may be natural for records to be stored in the order they are added, there is actually no guarantee of this.  A database file can become segmented, then later compacted, etc., and the rows may not be reconstructed in the same order.  Although Access might usually store the records in a particular order, it is a mistake to rely on any such insertion or storage order.

The example [Tracking] data implies a given order by showing [tracking_id] = 7 before [tracking_id] = 4, and comments further indicate that one was inserted before the other.  This mistaken assumption is likely causing some of the confusion.
A related principle of SQL is that query rows should not be assumed to be in a particular order except when an ORDER BY clause is specified.  Once again, it is a mistake to rely on the default order just because it usually matches the insertion order.
Specification of various constraints and indexes can be used to facilitate efficient ordering.  For example, if one specifies a primary key on the table, it is likely that the rows will be stored by that primary key, but they could very well be stored in segmented groups/patterns to facilitate quick searches.  It is still a mistake to expect an SQL query to always return the rows in primary-key order without an explicit GROUP BY clause.

At least within Access, logical order can be implemented by using a long-integer Autonumber column.  Access keeps track of used Autonumber values and it will consistently assign a unique number to new rows.  In this case, it can be trusted that a higher number was inserted after a lower number.  (Note:  This behavior is not universally true, since some database systems by default will reuse numbers of deleted records.)

It is such common practice for tables to use an Autonumber column, that it was natural to assume that [tracking_id] was such a column.  Most users will assume at first read that [tracking_id] = 7 comes AFTER [tracking_id] = 4.  Honestly, the fact that they are not in such an order is perplexing, especially without some other field to indicate the insertion order.
Another alternative is to use a date field, a "timestamp" field.  If the only reason for this field is to indicate insertion/creation order, then I suggest a Date/Time column with Default Value = Now().  (Since Access dates only have resolution down to a second, this is only valid if records will not be inserted faster than each second.)
The "flag" column as you described is discourage and should be unnecessary if one of the other above schema is used.  Manually changing flag values upon insertion and edits can become a maintenance nightmare and without proper constraints (Validation Rules and/or unique indexes), it can lead to contradictory data.

For the following queries, I will assume that [tracking_id] is a unique Autonumber field and that it consistently represents insertion order so that a higher number is the "latest" record with the same [date_action].

I assume that it is possible for each [staffing_id] to have multiple [date_action] values.  The example data shows only one date (albeit multiple rows) for each [staffing_id]. It is tempting to simplify the queries based only on the sample data, but that is a mistake which can return incorrect aggregate values.  The existing two answers make this mistake.

The question already contains all of the various parts of a solution, but never combines them to produce a complete solution.  The mistake is in assuming that the desired results can be achieved with only one aggregate subquery.  In fact, there needs to be two subqueries which find the max date and the max tracking_id separately!  (Honestly, the question text outlines the following three steps using words, but then fails to match the logic in the queries.)

Query 1:  First get the maximum [date_action] for a given [staffing_id],
Query 2:  then get the maximum [tracking_id] for the maximum date and id found in query 1.  (You can't find the maximum [tracking_id] value separately or at the same time--in the same aggregate query--before knowing the maximum date.)
Query 3:  Finally get the record details (other fields) by linking to the found maximum [tracking_id].  (Because the staffing_id and date_action have already been selected in the inner queries AND because tracking_id is considered a unique key, it is only necessary for final selection on that key value.  In other words, it is redundant to include the other fields in the outermost ON clause.)

SELECT t1.*
FROM Tracking AS t1
  INNER JOIN (
    SELECT Max(t2.tracking_id) AS MaxOfTracking_id
    FROM Tracking AS t2
      INNER JOIN (
        SELECT t3.staffing_id, Max(t3.date_action) AS MaxOfdate
        FROM Tracking AS t3
        GROUP BY t3.staffing_id
      ) AS tad
      ON t2.staffing_id = tad.staffing_id AND t2.date_action = tad.MaxOfdate
    GROUP BY t2.staffing_id, t2.date_action
  ) AS tai
  ON t1.tracking_id = tai.MaxOfTracking_id
ORDER BY t1.staffing_id

Returns:

tracking_id   date_action  action_id  staffing_id
2             3/4/2019     3          2
7             10/25/2018   67         3
6             5/5/2019     6          5
8             3/2/2019     55         6


Adding Security to a legacy DB with Schema

That sounds reasonable (from the information provided).

Also, one note - an explicit DENY at any level of the security heirarchy will always override any GRANT. This is different to a REVOKE, which nullifies a GRANT but doesn't do the "Haha, no, you're not getting that. Ever." effect of a DENY. (So you could have a GRANT SELECT at the database level, a REVOKE SELECT at the schema level, and a GRANT SELECT again at the table level - that'd give you select on that one table, and everything else in the database not in that schema (everything else in the schema aside from that table comes under the REVOKE). If you replaced that REVOKE SELECT with a DENY SELECT then you'd lose select access to the entire schema, no exceptions*.)

gbn's answer here is a wonderfully concise intro to the various permissions you can apply to a single database object (note that some of them don't necessarily apply to all objects, the most obvious one being REFERENCES). For an overview of permissions from the domain level down to the individual objects within a schema, see here.

(* I lied, there is one exception: Ownership chaining. Permissions are not checked in the case of an ownership chain. In the simplest case, if Bob grants Jane select access on a view that he owns, and that view only references tables that Bob owns, then Jane's permissions on those tables are not checked, because the permissions are chained from the view to the underlying objects (nb: ownership chaining can get a lot more complex than that). Ownership chaining is incredibly powerful and incredibly useful, but it also has an incredible power to bite you in the ass if you're not careful. If you're running an environment where precisely defined permissions are critical, then always test your permissions before you deploy them.)

(* I lied again, there's one more exception - db_owner [and users aliased to dbo] and sa_role (which is effectively db_owner on every database, plus server-level stuff). Permissions are never checked for these, it's assumed they have full rights to everything.)

Also, as gbn noted, if you're going to use schemas, then you may as well go the whole hog and permission the schemas rather than individual objects within a schema (the primary use case for schemas is for grouping objects with the same permissions together so you can manage permissions on one thing - the schema - rather than a whole bunch of objects).